Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita4e986e

Browse files
committed
Add more tests for utility commands in pipelines
This commit checks interactions with pipelines and implicit transactionblocks for the following commands that have their own behaviors whenused in pipelines depending on their order in a pipeline and syncrequests:- SET LOCAL- REINDEX CONCURRENTLY- VACUUM- Subtransactions (SAVEPOINT, ROLLBACK TO SAVEPOINT)These scenarios could be tested only with pgbench previously. Themeta-commands of psql controlling pipelines make these easier toimplement, 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 parentf98765f commita4e986e

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
SELECT1;
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 \bind1 \g
374+
REINDEX TABLE CONCURRENTLY psql_pipeline \bind \g
375+
SELECT $1 \bind2 \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 \bind2 \g
382+
\endpipeline
383+
384+
-- Subtransactions are not allowed in a pipeline.
385+
\startpipeline
386+
SAVEPOINT a \bind \g
387+
SELECT $1 \bind1 \g
388+
ROLLBACK TO SAVEPOINT a \bind \g
389+
SELECT $1 \bind2 \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 \bind2 \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 \bind1 \g
403+
LOCK psql_pipeline \bind \g
404+
SELECT $1 \bind2 \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+
SELECT1 \bind \g
415+
VACUUM psql_pipeline \bind \g
416+
\endpipeline
417+
418+
-- VACUUM works after a \syncpipeline.
419+
\startpipeline
420+
SELECT1 \bind \g
421+
\syncpipeline
422+
VACUUM psql_pipeline \bind \g
423+
\endpipeline
424+
353425
-- Clean up
354426
DROPTABLE psql_pipeline;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp