Hello,
I found that using "BEGIN ISOLATINO LEVEL SERIALIZABLE" in a pipline with
prepared statement makes pgbench abort.
$ cat pipeline.sql
\startpipeline
begin isolation level repeatable read;
select 1;
end;
\endpipeline
$ pgbench -f pipeline.sql -M prepared -t 1
pgbench (15devel)
starting vacuum...end.
pgbench: error: client 0 script 0 aborted in command 4 query 0:
transaction type: pipeline.sql
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 0/1
pgbench: fatal: Run was aborted; the above results are incomplete.
The error that occured in the backend was
"ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query".
After investigating this, now I've got the cause as below.
1. The commands in the script are executed in the order. First, pipeline
mode starts at \startpipeline.
2. Parse messages for all SQL commands in the script are sent to the backend
because it is first time to execute them.
3. An implicit transaction starts, and this is not committed yet because Sync
message is not sent at that time in pipeline mode.
4. All prepared statements are sent to the backend.
5. After processing \endpipeline, Sync is issued and all sent commands are
executed.
6. However, the BEGIN doesn't start new transaction because the implicit
transaction has already started. The error above occurs because the snapshot
was already created before the BEGIN command.
We can also see the similar error when using "BEGIN DEFERRABLE".
One way to avoid these errors is to send Parse messages before pipeline mode
starts. I attached a patch to fix to prepare commands at starting of a script
instead of at the first execution of the command.
Or, we can also avoid these errors by placing \startpipeline after the BEGIN,
so it might be enogh just to note in the documentation.
Actually, we also get an error just when there is another SQL command before the
BEGIN in a pipelne, as below, regardless to using prepared statement or not,
because this command cause an implicit transaction.
\startpipeline
select 0;
begin isolation level repeatable read;
select 1;
end;
\endpipeline
I think it is hard to prevent this error from pgbench without analysing command
strings. Therefore, noting in the documentation that the first command in a pipeline
starts an implicit transaction might be useful for users.
What do you think?
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
