-- -- Tests using psql pipelining -- CREATE TABLE psql_pipeline(a INTEGER PRIMARY KEY, s TEXT); -- Single query \startpipeline SELECT $1 \bind 'val1' \sendpipeline \endpipeline ?column? ---------- val1 (1 row) \startpipeline SELECT 'val1'; \endpipeline ?column? ---------- val1 (1 row) -- Multiple queries \startpipeline SELECT $1 \bind 'val1' \sendpipeline SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline SELECT 'val4'; SELECT 'val5', 'val6'; \endpipeline ?column? ---------- val1 (1 row) ?column? | ?column? ----------+---------- val2 | val3 (1 row) ?column? | ?column? ----------+---------- val2 | val3 (1 row) ?column? ---------- val4 (1 row) ?column? | ?column? ----------+---------- val5 | val6 (1 row) -- Multiple queries in single line, separated by semicolons \startpipeline SELECT 1; SELECT 2; SELECT 3 ; \echo :PIPELINE_COMMAND_COUNT 3 \endpipeline ?column? ---------- 1 (1 row) ?column? ---------- 2 (1 row) ?column? ---------- 3 (1 row) -- Test \flush \startpipeline \flush SELECT $1 \bind 'val1' \sendpipeline \flush SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline \flush SELECT 'val4'; SELECT 'val5', 'val6'; \endpipeline ?column? ---------- val1 (1 row) ?column? | ?column? ----------+---------- val2 | val3 (1 row) ?column? | ?column? ----------+---------- val2 | val3 (1 row) ?column? ---------- val4 (1 row) ?column? | ?column? ----------+---------- val5 | val6 (1 row) -- Send multiple syncs \startpipeline \echo :PIPELINE_COMMAND_COUNT 0 \echo :PIPELINE_SYNC_COUNT 0 \echo :PIPELINE_RESULT_COUNT 0 SELECT $1 \bind 'val1' \sendpipeline \syncpipeline \syncpipeline SELECT $1, $2 \bind 'val2' 'val3' \sendpipeline \syncpipeline SELECT $1, $2 \bind 'val4' 'val5' \sendpipeline \echo :PIPELINE_COMMAND_COUNT 1 \echo :PIPELINE_SYNC_COUNT 3 \echo :PIPELINE_RESULT_COUNT 2 SELECT 'val7'; \syncpipeline \syncpipeline SELECT 'val8'; \syncpipeline SELECT 'val9'; \echo :PIPELINE_COMMAND_COUNT 1 \echo :PIPELINE_SYNC_COUNT 6 \echo :PIPELINE_RESULT_COUNT 5 \endpipeline ?column? ---------- val1 (1 row) ?column? | ?column? ----------+---------- val2 | val3 (1 row) ?column? | ?column? ----------+---------- val4 | val5 (1 row) ?column? ---------- val7 (1 row) ?column? ---------- val8 (1 row) ?column? ---------- val9 (1 row) -- Query terminated with a semicolon replaces an unnamed prepared -- statement. \startpipeline SELECT $1 \parse '' SELECT 1; \bind_named '' \endpipeline ?column? ---------- 1 (1 row) -- Extended query is appended to pipeline by a semicolon after a -- newline. \startpipeline SELECT $1 \bind 1 ; SELECT 2; \endpipeline ?column? ---------- 1 (1 row) ?column? ---------- 2 (1 row) -- \startpipeline should not have any effect if already in a pipeline. \startpipeline \startpipeline SELECT $1 \bind 'val1' \sendpipeline \endpipeline ?column? ---------- val1 (1 row) -- Convert an implicit transaction block to an explicit transaction block. \startpipeline INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline BEGIN \bind \sendpipeline INSERT INTO psql_pipeline VALUES ($1) \bind 2 \sendpipeline ROLLBACK \bind \sendpipeline \endpipeline -- Multiple explicit transactions \startpipeline BEGIN \bind \sendpipeline INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline ROLLBACK \bind \sendpipeline BEGIN \bind \sendpipeline INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline COMMIT \bind \sendpipeline \endpipeline -- COPY FROM STDIN -- with \sendpipeline and \bind \startpipeline SELECT $1 \bind 'val1' \sendpipeline COPY psql_pipeline FROM STDIN \bind \sendpipeline \endpipeline ?column? ---------- val1 (1 row) -- with semicolon \startpipeline SELECT 'val1'; COPY psql_pipeline FROM STDIN; \endpipeline ?column? ---------- val1 (1 row) -- COPY FROM STDIN with \flushrequest + \getresults -- with \sendpipeline and \bind \startpipeline SELECT $1 \bind 'val1' \sendpipeline COPY psql_pipeline FROM STDIN \bind \sendpipeline \flushrequest \getresults ?column? ---------- val1 (1 row) message type 0x5a arrived from server while idle \endpipeline -- with semicolon \startpipeline SELECT 'val1'; COPY psql_pipeline FROM STDIN; \flushrequest \getresults ?column? ---------- val1 (1 row) message type 0x5a arrived from server while idle \endpipeline -- COPY FROM STDIN with \syncpipeline + \getresults -- with \bind and \sendpipeline \startpipeline SELECT $1 \bind 'val1' \sendpipeline COPY psql_pipeline FROM STDIN \bind \sendpipeline \syncpipeline \getresults ?column? ---------- val1 (1 row) \endpipeline -- with semicolon \startpipeline SELECT 'val1'; COPY psql_pipeline FROM STDIN; \syncpipeline \getresults ?column? ---------- val1 (1 row) \endpipeline -- COPY TO STDOUT -- with \bind and \sendpipeline \startpipeline SELECT $1 \bind 'val1' \sendpipeline copy psql_pipeline TO STDOUT \bind \sendpipeline \endpipeline ?column? ---------- val1 (1 row) 1 \N 2 test2 20 test2 3 test3 30 test3 4 test4 40 test4 -- with semicolon \startpipeline SELECT 'val1'; copy psql_pipeline TO STDOUT; \endpipeline ?column? ---------- val1 (1 row) 1 \N 2 test2 20 test2 3 test3 30 test3 4 test4 40 test4 -- COPY TO STDOUT with \flushrequest + \getresults -- with \bind and \sendpipeline \startpipeline SELECT $1 \bind 'val1' \sendpipeline copy psql_pipeline TO STDOUT \bind \sendpipeline \flushrequest \getresults ?column? ---------- val1 (1 row) 1 \N 2 test2 20 test2 3 test3 30 test3 4 test4 40 test4 \endpipeline -- with semicolon \startpipeline SELECT 'val1'; copy psql_pipeline TO STDOUT; \flushrequest \getresults ?column? ---------- val1 (1 row) 1 \N 2 test2 20 test2 3 test3 30 test3 4 test4 40 test4 \endpipeline -- COPY TO STDOUT with \syncpipeline + \getresults -- with \bind and \sendpipeline \startpipeline SELECT $1 \bind 'val1' \sendpipeline copy psql_pipeline TO STDOUT \bind \sendpipeline \syncpipeline \getresults ?column? ---------- val1 (1 row) 1 \N 2 test2 20 test2 3 test3 30 test3 4 test4 40 test4 \endpipeline -- with semicolon \startpipeline SELECT 'val1'; copy psql_pipeline TO STDOUT; \syncpipeline \getresults ?column? ---------- val1 (1 row) 1 \N 2 test2 20 test2 3 test3 30 test3 4 test4 40 test4 \endpipeline -- Use \parse and \bind_named \startpipeline SELECT $1 \parse '' SELECT $1, $2 \parse '' SELECT $2 \parse pipeline_1 \bind_named '' 1 2 \sendpipeline \bind_named pipeline_1 2 \sendpipeline \endpipeline ERROR: could not determine data type of parameter $1 -- \getresults displays all results preceding a \flushrequest. \startpipeline SELECT $1 \bind 1 \sendpipeline SELECT $1 \bind 2 \sendpipeline \flushrequest \getresults ?column? ---------- 1 (1 row) ?column? ---------- 2 (1 row) \endpipeline -- \getresults displays all results preceding a \syncpipeline. \startpipeline SELECT $1 \bind 1 \sendpipeline SELECT $1 \bind 2 \sendpipeline \syncpipeline \getresults ?column? ---------- 1 (1 row) ?column? ---------- 2 (1 row) \endpipeline -- \getresults immediately returns if there is no result to fetch. \startpipeline \getresults No pending results to get SELECT $1 \bind 2 \sendpipeline \getresults No pending results to get \flushrequest \endpipeline ?column? ---------- 2 (1 row) \getresults No pending results to get -- \getresults only fetches results preceding a \flushrequest. \startpipeline SELECT $1 \bind 2 \sendpipeline \flushrequest SELECT $1 \bind 2 \sendpipeline \getresults ?column? ---------- 2 (1 row) \endpipeline ?column? ---------- 2 (1 row) -- \getresults only fetches results preceding a \syncpipeline. \startpipeline SELECT $1 \bind 2 \sendpipeline \syncpipeline SELECT $1 \bind 2 \sendpipeline \getresults ?column? ---------- 2 (1 row) \endpipeline ?column? ---------- 2 (1 row) -- Use pipeline with chunked results for both \getresults and \endpipeline. \startpipeline \set FETCH_COUNT 10 SELECT $1 \bind 2 \sendpipeline \flushrequest \getresults ?column? ---------- 2 (1 row) SELECT $1 \bind 2 \sendpipeline \endpipeline ?column? ---------- 2 (1 row) \unset FETCH_COUNT -- \getresults with specific number of requested results. \startpipeline SELECT $1 \bind 1 \sendpipeline SELECT $1 \bind 2 \sendpipeline SELECT $1 \bind 3 \sendpipeline \echo :PIPELINE_SYNC_COUNT 0 \syncpipeline \echo :PIPELINE_SYNC_COUNT 1 \echo :PIPELINE_RESULT_COUNT 3 \getresults 1 ?column? ---------- 1 (1 row) \echo :PIPELINE_RESULT_COUNT 2 SELECT $1 \bind 4 \sendpipeline \getresults 3 ?column? ---------- 2 (1 row) ?column? ---------- 3 (1 row) \echo :PIPELINE_RESULT_COUNT 0 \endpipeline ?column? ---------- 4 (1 row) -- \syncpipeline count as one command to fetch for \getresults. \startpipeline \syncpipeline \syncpipeline SELECT $1 \bind 1 \sendpipeline \flushrequest \getresults 2 \getresults 1 ?column? ---------- 1 (1 row) \endpipeline -- \getresults 0 should get all the results. \startpipeline SELECT $1 \bind 1 \sendpipeline SELECT $1 \bind 2 \sendpipeline SELECT $1 \bind 3 \sendpipeline \syncpipeline \getresults 0 ?column? ---------- 1 (1 row) ?column? ---------- 2 (1 row) ?column? ---------- 3 (1 row) \endpipeline -- -- Pipeline errors -- -- \endpipeline outside of pipeline should fail \endpipeline cannot send pipeline when not in pipeline mode -- After an aborted pipeline, commands after a \syncpipeline should be -- displayed. \startpipeline SELECT $1 \bind \sendpipeline \syncpipeline SELECT $1 \bind 1 \sendpipeline \endpipeline ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 ?column? ---------- 1 (1 row) -- For an incorrect number of parameters, the pipeline is aborted and -- the following queries will not be executed. \startpipeline SELECT \bind 'val1' \sendpipeline SELECT $1 \bind 'val1' \sendpipeline \endpipeline ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0 -- Using a semicolon with a parameter triggers an error and aborts -- the pipeline. \startpipeline SELECT $1; SELECT 1; \endpipeline ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 -- An explicit transaction with an error needs to be rollbacked after -- the pipeline. \startpipeline BEGIN \bind \sendpipeline INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline ROLLBACK \bind \sendpipeline \endpipeline ERROR: duplicate key value violates unique constraint "psql_pipeline_pkey" DETAIL: Key (a)=(1) already exists. ROLLBACK; -- \watch is not allowed in a pipeline. \startpipeline SELECT \bind \sendpipeline \watch 1 \watch not allowed in pipeline mode \endpipeline -- (1 row) -- \gdesc should fail as synchronous commands are not allowed in a pipeline, -- and the pipeline should still be usable. \startpipeline SELECT $1 \bind 1 \gdesc synchronous command execution functions are not allowed in pipeline mode SELECT $1 \bind 1 \sendpipeline \endpipeline ?column? ---------- 1 (1 row) -- \gset is not allowed in a pipeline, pipeline should still be usable. \startpipeline SELECT $1 as i, $2 as j \parse '' SELECT $1 as k, $2 as l \parse 'second' \bind_named '' 1 2 \gset \gset not allowed in pipeline mode \bind_named second 1 2 \gset pref02_ \echo :pref02_i :pref02_j \gset not allowed in pipeline mode \bind_named '' 1 2 \sendpipeline \endpipeline i | j ---+--- 1 | 2 (1 row) -- \g and \gx are not allowed, pipeline should still be usable. \startpipeline SELECT $1 \bind 1 \g \g not allowed in pipeline mode SELECT $1 \bind 1 \g (format=unaligned tuples_only=on) \g not allowed in pipeline mode SELECT $1 \bind 1 \gx \gx not allowed in pipeline mode SELECT $1 \bind 1 \gx (format=unaligned tuples_only=on) \gx not allowed in pipeline mode \reset SELECT $1 \bind 1 \sendpipeline \endpipeline ?column? ---------- 1 (1 row) -- \g and \gx warnings should be emitted in an aborted pipeline, with -- pipeline still usable. \startpipeline SELECT $1 \bind \sendpipeline \flushrequest \getresults ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 SELECT $1 \bind 1 \g \g not allowed in pipeline mode SELECT $1 \bind 1 \gx \gx not allowed in pipeline mode \endpipeline -- \sendpipeline is not allowed outside of a pipeline \sendpipeline \sendpipeline not allowed outside of pipeline mode SELECT $1 \bind 1 \sendpipeline \sendpipeline not allowed outside of pipeline mode \reset -- \sendpipeline is not allowed if not preceded by \bind or \bind_named \startpipeline \sendpipeline \sendpipeline must be used after \bind or \bind_named SELECT 1 \sendpipeline \sendpipeline must be used after \bind or \bind_named \endpipeline -- \gexec is not allowed, pipeline should still be usable. \startpipeline SELECT 'INSERT INTO psql_pipeline(a) SELECT generate_series(1, 10)' \parse 'insert_stmt' \bind_named insert_stmt \gexec \gexec not allowed in pipeline mode \bind_named insert_stmt \sendpipeline SELECT COUNT(*) FROM psql_pipeline \bind \sendpipeline \endpipeline ?column? ------------------------------------------------------------ INSERT INTO psql_pipeline(a) SELECT generate_series(1, 10) (1 row) count ------- 7 (1 row) -- After an error, pipeline is aborted and requires \syncpipeline to be -- reusable. \startpipeline SELECT $1 \bind \sendpipeline SELECT $1 \bind 1 \sendpipeline SELECT $1 \parse a \bind_named a 1 \sendpipeline \close a \flushrequest \getresults ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 -- Pipeline is aborted. SELECT $1 \bind 1 \sendpipeline SELECT $1 \parse a \bind_named a 1 \sendpipeline \close a -- Sync allows pipeline to recover. \syncpipeline \getresults Pipeline aborted, command did not run SELECT $1 \bind 1 \sendpipeline SELECT $1 \parse a \bind_named a 1 \sendpipeline \close a \flushrequest \getresults ?column? ---------- 1 (1 row) ?column? ---------- 1 (1 row) \endpipeline -- In an aborted pipeline, \getresults 1 aborts commands one at a time. \startpipeline SELECT $1 \bind \sendpipeline SELECT $1 \bind 1 \sendpipeline SELECT $1 \parse a \bind_named a 1 \sendpipeline \syncpipeline \getresults 1 ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 \getresults 1 Pipeline aborted, command did not run \getresults 1 Pipeline aborted, command did not run \getresults 1 Pipeline aborted, command did not run \getresults 1 \endpipeline -- Test chunked results with an aborted pipeline. \startpipeline \set FETCH_COUNT 10 SELECT $1 \bind \sendpipeline \flushrequest \getresults ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 SELECT $1 \bind \sendpipeline \endpipeline fetching results in chunked mode failed Pipeline aborted, command did not run \unset FETCH_COUNT -- \getresults returns an error when an incorrect number is provided. \startpipeline \getresults -1 \getresults: invalid number of requested results \endpipeline -- \getresults when there is no result should not impact the next -- query executed. \getresults 1 No pending results to get select 1; ?column? ---------- 1 (1 row) -- Error messages accumulate and are repeated. \startpipeline SELECT 1 \bind \sendpipeline \gdesc synchronous command execution functions are not allowed in pipeline mode \gdesc synchronous command execution functions are not allowed in pipeline mode synchronous command execution functions are not allowed in pipeline mode \endpipeline ?column? ---------- 1 (1 row) -- -- Pipelines and transaction blocks -- -- SET LOCAL will issue a warning when modifying a GUC outside of a -- transaction block. The change will still be valid as a pipeline -- runs within an implicit transaction block. Sending a sync will -- commit the implicit transaction block. The first command after a -- sync will not be seen as belonging to a pipeline. \startpipeline SET LOCAL statement_timeout='1h' \bind \sendpipeline SHOW statement_timeout \bind \sendpipeline \syncpipeline SHOW statement_timeout \bind \sendpipeline SET LOCAL statement_timeout='2h' \bind \sendpipeline SHOW statement_timeout \bind \sendpipeline \endpipeline WARNING: SET LOCAL can only be used in transaction blocks statement_timeout ------------------- 1h (1 row) statement_timeout ------------------- 0 (1 row) statement_timeout ------------------- 2h (1 row) -- REINDEX CONCURRENTLY fails if not the first command in a pipeline. \startpipeline SELECT $1 \bind 1 \sendpipeline REINDEX TABLE CONCURRENTLY psql_pipeline \bind \sendpipeline SELECT $1 \bind 2 \sendpipeline \endpipeline ?column? ---------- 1 (1 row) ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block -- REINDEX CONCURRENTLY works if it is the first command in a pipeline. \startpipeline REINDEX TABLE CONCURRENTLY psql_pipeline \bind \sendpipeline SELECT $1 \bind 2 \sendpipeline \endpipeline ?column? ---------- 2 (1 row) -- Subtransactions are not allowed in a pipeline. \startpipeline SAVEPOINT a \bind \sendpipeline SELECT $1 \bind 1 \sendpipeline ROLLBACK TO SAVEPOINT a \bind \sendpipeline SELECT $1 \bind 2 \sendpipeline \endpipeline ERROR: SAVEPOINT can only be used in transaction blocks -- LOCK fails as the first command in a pipeline, as not seen in an -- implicit transaction block. \startpipeline LOCK psql_pipeline \bind \sendpipeline SELECT $1 \bind 2 \sendpipeline \endpipeline ERROR: LOCK TABLE can only be used in transaction blocks -- LOCK succeeds as it is not the first command in a pipeline, -- seen in an implicit transaction block. \startpipeline SELECT $1 \bind 1 \sendpipeline LOCK psql_pipeline \bind \sendpipeline SELECT $1 \bind 2 \sendpipeline \endpipeline ?column? ---------- 1 (1 row) ?column? ---------- 2 (1 row) -- VACUUM works as the first command in a pipeline. \startpipeline VACUUM psql_pipeline \bind \sendpipeline \endpipeline -- VACUUM fails when not the first command in a pipeline. \startpipeline SELECT 1 \bind \sendpipeline VACUUM psql_pipeline \bind \sendpipeline \endpipeline ?column? ---------- 1 (1 row) ERROR: VACUUM cannot run inside a transaction block -- VACUUM works after a \syncpipeline. \startpipeline SELECT 1 \bind \sendpipeline \syncpipeline VACUUM psql_pipeline \bind \sendpipeline \endpipeline ?column? ---------- 1 (1 row) -- Clean up DROP TABLE psql_pipeline;