path: root/src/test/regress/expected/psql_pipeline.out

--
-- 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;