Skip to content

Commit a4e986e

Browse files
michaelpqmichaelpq
committed
Add more tests for utility commands in pipelines
This commit checks interactions with pipelines and implicit transaction blocks for the following commands that have their own behaviors when used in pipelines depending on their order in a pipeline and sync requests: - SET LOCAL - REINDEX CONCURRENTLY - VACUUM - Subtransactions (SAVEPOINT, ROLLBACK TO SAVEPOINT) These scenarios could be tested only with pgbench previously. The meta-commands of psql controlling pipelines make these easier to implement, debug, and they can be run in a SQL script. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Discussion: https://postgr.es/m/CAO6_XqroE7JuMEm1sWz55rp9fAYX2JwmcP_3m_v51vnOFdsLiQ@mail.gmail.com
1 parent f98765f commit a4e986e

File tree

2 files changed

+184
-0
lines changed

2 files changed

+184
-0
lines changed

‎src/test/regress/expected/psql_pipeline.out

Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -585,5 +585,117 @@ PQsendQuery not allowed in pipeline mode
585585
1
586586
(1 row)
587587

588+
--
589+
-- Pipelines and transaction blocks
590+
--
591+
-- SET LOCAL will issue a warning when modifying a GUC outside of a
592+
-- transaction block. The change will still be valid as a pipeline
593+
-- runs within an implicit transaction block. Sending a sync will
594+
-- commit the implicit transaction block. The first command after a
595+
-- sync will not be seen as belonging to a pipeline.
596+
\startpipeline
597+
SET LOCAL statement_timeout='1h' \bind \g
598+
SHOW statement_timeout \bind \g
599+
\syncpipeline
600+
SHOW statement_timeout \bind \g
601+
SET LOCAL statement_timeout='2h' \bind \g
602+
SHOW statement_timeout \bind \g
603+
\endpipeline
604+
WARNING: SET LOCAL can only be used in transaction blocks
605+
statement_timeout
606+
-------------------
607+
1h
608+
(1 row)
609+
610+
statement_timeout
611+
-------------------
612+
0
613+
(1 row)
614+
615+
statement_timeout
616+
-------------------
617+
2h
618+
(1 row)
619+
620+
-- REINDEX CONCURRENTLY fails if not the first command in a pipeline.
621+
\startpipeline
622+
SELECT $1 \bind 1 \g
623+
REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
624+
SELECT $1 \bind 2 \g
625+
\endpipeline
626+
?column?
627+
----------
628+
1
629+
(1 row)
630+
631+
ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block
632+
-- REINDEX CONCURRENTLY works if it is the first command in a pipeline.
633+
\startpipeline
634+
REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
635+
SELECT $1 \bind 2 \g
636+
\endpipeline
637+
?column?
638+
----------
639+
2
640+
(1 row)
641+
642+
-- Subtransactions are not allowed in a pipeline.
643+
\startpipeline
644+
SAVEPOINT a \bind \g
645+
SELECT $1 \bind 1 \g
646+
ROLLBACK TO SAVEPOINT a \bind \g
647+
SELECT $1 \bind 2 \g
648+
\endpipeline
649+
ERROR: SAVEPOINT can only be used in transaction blocks
650+
-- LOCK fails as the first command in a pipeline, as not seen in an
651+
-- implicit transaction block.
652+
\startpipeline
653+
LOCK psql_pipeline \bind \g
654+
SELECT $1 \bind 2 \g
655+
\endpipeline
656+
ERROR: LOCK TABLE can only be used in transaction blocks
657+
-- LOCK succeeds as it is not the first command in a pipeline,
658+
-- seen in an implicit transaction block.
659+
\startpipeline
660+
SELECT $1 \bind 1 \g
661+
LOCK psql_pipeline \bind \g
662+
SELECT $1 \bind 2 \g
663+
\endpipeline
664+
?column?
665+
----------
666+
1
667+
(1 row)
668+
669+
?column?
670+
----------
671+
2
672+
(1 row)
673+
674+
-- VACUUM works as the first command in a pipeline.
675+
\startpipeline
676+
VACUUM psql_pipeline \bind \g
677+
\endpipeline
678+
-- VACUUM fails when not the first command in a pipeline.
679+
\startpipeline
680+
SELECT 1 \bind \g
681+
VACUUM psql_pipeline \bind \g
682+
\endpipeline
683+
?column?
684+
----------
685+
1
686+
(1 row)
687+
688+
ERROR: VACUUM cannot run inside a transaction block
689+
-- VACUUM works after a \syncpipeline.
690+
\startpipeline
691+
SELECT 1 \bind \g
692+
\syncpipeline
693+
VACUUM psql_pipeline \bind \g
694+
\endpipeline
695+
?column?
696+
----------
697+
1
698+
(1 row)
699+
588700
-- Clean up
589701
DROP TABLE psql_pipeline;

‎src/test/regress/sql/psql_pipeline.sql

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -350,5 +350,77 @@ SELECT 1;
350350
SELECT 1;
351351
\endpipeline
352352

353+
--
354+
-- Pipelines and transaction blocks
355+
--
356+
357+
-- SET LOCAL will issue a warning when modifying a GUC outside of a
358+
-- transaction block. The change will still be valid as a pipeline
359+
-- runs within an implicit transaction block. Sending a sync will
360+
-- commit the implicit transaction block. The first command after a
361+
-- sync will not be seen as belonging to a pipeline.
362+
\startpipeline
363+
SET LOCAL statement_timeout='1h' \bind \g
364+
SHOW statement_timeout \bind \g
365+
\syncpipeline
366+
SHOW statement_timeout \bind \g
367+
SET LOCAL statement_timeout='2h' \bind \g
368+
SHOW statement_timeout \bind \g
369+
\endpipeline
370+
371+
-- REINDEX CONCURRENTLY fails if not the first command in a pipeline.
372+
\startpipeline
373+
SELECT $1 \bind 1 \g
374+
REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
375+
SELECT $1 \bind 2 \g
376+
\endpipeline
377+
378+
-- REINDEX CONCURRENTLY works if it is the first command in a pipeline.
379+
\startpipeline
380+
REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
381+
SELECT $1 \bind 2 \g
382+
\endpipeline
383+
384+
-- Subtransactions are not allowed in a pipeline.
385+
\startpipeline
386+
SAVEPOINT a \bind \g
387+
SELECT $1 \bind 1 \g
388+
ROLLBACK TO SAVEPOINT a \bind \g
389+
SELECT $1 \bind 2 \g
390+
\endpipeline
391+
392+
-- LOCK fails as the first command in a pipeline, as not seen in an
393+
-- implicit transaction block.
394+
\startpipeline
395+
LOCK psql_pipeline \bind \g
396+
SELECT $1 \bind 2 \g
397+
\endpipeline
398+
399+
-- LOCK succeeds as it is not the first command in a pipeline,
400+
-- seen in an implicit transaction block.
401+
\startpipeline
402+
SELECT $1 \bind 1 \g
403+
LOCK psql_pipeline \bind \g
404+
SELECT $1 \bind 2 \g
405+
\endpipeline
406+
407+
-- VACUUM works as the first command in a pipeline.
408+
\startpipeline
409+
VACUUM psql_pipeline \bind \g
410+
\endpipeline
411+
412+
-- VACUUM fails when not the first command in a pipeline.
413+
\startpipeline
414+
SELECT 1 \bind \g
415+
VACUUM psql_pipeline \bind \g
416+
\endpipeline
417+
418+
-- VACUUM works after a \syncpipeline.
419+
\startpipeline
420+
SELECT 1 \bind \g
421+
\syncpipeline
422+
VACUUM psql_pipeline \bind \g
423+
\endpipeline
424+
353425
-- Clean up
354426
DROP TABLE psql_pipeline;

0 commit comments

Comments
 (0)