Skip to content

Commit 2366211

Browse files
michaelpqmichaelpq
committed
Handle better implicit transaction state of pipeline mode
When using a pipeline, a transaction starts from the first command and is committed with a Sync message or when the pipeline ends. Functions like IsInTransactionBlock() or PreventInTransactionBlock() were already able to understand a pipeline as being in a transaction block, but it was not the case of CheckTransactionBlock(). This function is called for example to generate a WARNING for SET LOCAL, complaining that it is used outside of a transaction block. The current state of the code caused multiple problems, like: - SET LOCAL executed at any stage of a pipeline issued a WARNING, even if the command was at least second in line where the pipeline is in a transaction state. - LOCK TABLE failed when invoked at any step of a pipeline, even if it should be able to work within a transaction block. The pipeline protocol assumes that the first command of a pipeline is not part of a transaction block, and that any follow-up commands is considered as within a transaction block. This commit changes the backend so as an implicit transaction block is started each time the first Execute message of a pipeline has finished processing, with this implicit transaction block ended once a sync is processed. The checks based on XACT_FLAGS_PIPELINING in the routines checking if we are in a transaction block are not necessary: it is enough to rely on the existing ones. Some tests are added to pgbench, that can be backpatched down to v17 when \syncpipeline is involved and down to v14 where \startpipeline and \endpipeline are available. This is unfortunately limited regarding the error patterns that can be checked, but it provides coverage for various pipeline combinations to check if these succeed or fail. These tests are able to capture the case of SET LOCAL's WARNING. The author has proposed a different feature to improve the coverage by adding similar meta-commands to psql where error messages could be checked, something more useful for the cases where commands cannot be used in transaction blocks, like REINDEX CONCURRENTLY or VACUUM. This is considered as future work for v18~. Author: Anthonin Bonnefoy Reviewed-by: Jelte Fennema-Nio, Michael Paquier Discussion: https://postgr.es/m/CAO6_XqrWO8uNBQrSu5r6jh+vTGi5Oiyk4y8yXDORdE2jbzw8xw@mail.gmail.com Backpatch-through: 13
1 parent a116885 commit 2366211

File tree

4 files changed

+184
-23
lines changed

4 files changed

+184
-23
lines changed

‎doc/src/sgml/protocol.sgml

+11-10
Original file line numberDiff line numberDiff line change
@@ -1088,16 +1088,17 @@ SELCT 1/0;<!-- this typo is intentional -->
10881088

10891089
<para>
10901090
If the client has not issued an explicit <command>BEGIN</command>,
1091-
then each Sync ordinarily causes an implicit <command>COMMIT</command>
1092-
if the preceding step(s) succeeded, or an
1093-
implicit <command>ROLLBACK</command> if they failed. However, there
1094-
are a few DDL commands (such as <command>CREATE DATABASE</command>)
1095-
that cannot be executed inside a transaction block. If one of
1096-
these is executed in a pipeline, it will fail unless it is the first
1097-
command in the pipeline. Furthermore, upon success it will force an
1098-
immediate commit to preserve database consistency. Thus a Sync
1099-
immediately following one of these commands has no effect except to
1100-
respond with ReadyForQuery.
1091+
then an implicit transaction block is started and each Sync ordinarily
1092+
causes an implicit <command>COMMIT</command> if the preceding step(s)
1093+
succeeded, or an implicit <command>ROLLBACK</command> if they failed.
1094+
This implicit transaction block will only be detected by the server
1095+
when the first command ends without a sync. There are a few DDL
1096+
commands (such as <command>CREATE DATABASE</command>) that cannot be
1097+
executed inside a transaction block. If one of these is executed in a
1098+
pipeline, it will fail unless it is the first command after a Sync.
1099+
Furthermore, upon success it will force an immediate commit to preserve
1100+
database consistency. Thus a Sync immediately following one of these
1101+
commands has no effect except to respond with ReadyForQuery.
11011102
</para>
11021103

11031104
<para>

‎src/backend/access/transam/xact.c

-13
Original file line numberDiff line numberDiff line change
@@ -3426,16 +3426,6 @@ PreventInTransactionBlock(bool isTopLevel, const char *stmtType)
34263426
errmsg("%s cannot run inside a subtransaction",
34273427
stmtType)));
34283428

3429-
/*
3430-
* inside a pipeline that has started an implicit transaction?
3431-
*/
3432-
if (MyXactFlags & XACT_FLAGS_PIPELINING)
3433-
ereport(ERROR,
3434-
(errcode(ERRCODE_ACTIVE_SQL_TRANSACTION),
3435-
/* translator: %s represents an SQL statement name */
3436-
errmsg("%s cannot be executed within a pipeline",
3437-
stmtType)));
3438-
34393429
/*
34403430
* inside a function call?
34413431
*/
@@ -3547,9 +3537,6 @@ IsInTransactionBlock(bool isTopLevel)
35473537
if (IsSubTransaction())
35483538
return true;
35493539

3550-
if (MyXactFlags & XACT_FLAGS_PIPELINING)
3551-
return true;
3552-
35533540
if (!isTopLevel)
35543541
return true;
35553542

‎src/backend/tcop/postgres.c

+18
Original file line numberDiff line numberDiff line change
@@ -2732,6 +2732,17 @@ start_xact_command(void)
27322732

27332733
xact_started = true;
27342734
}
2735+
else if (MyXactFlags & XACT_FLAGS_PIPELINING)
2736+
{
2737+
/*
2738+
* When the first Execute message is completed, following commands
2739+
* will be done in an implicit transaction block created via
2740+
* pipelining. The transaction state needs to be updated to an
2741+
* implicit block if we're not already in a transaction block (like
2742+
* one started by an explicit BEGIN).
2743+
*/
2744+
BeginImplicitTransactionBlock();
2745+
}
27352746

27362747
/*
27372748
* Start statement timeout if necessary. Note that this'll intentionally
@@ -4780,6 +4791,13 @@ PostgresMain(int argc, char *argv[],
47804791

47814792
case 'S': /* sync */
47824793
pq_getmsgend(&input_message);
4794+
4795+
/*
4796+
* If pipelining was used, we may be in an implicit
4797+
* transaction block. Close it before calling
4798+
* finish_xact_command.
4799+
*/
4800+
EndImplicitTransactionBlock();
47834801
finish_xact_command();
47844802
send_ready_for_query = true;
47854803
break;

‎src/bin/pgbench/t/001_pgbench_with_server.pl

+155
Original file line numberDiff line numberDiff line change
@@ -884,6 +884,161 @@
884884
}
885885
});
886886

887+
# Try SET LOCAL as first pipeline command. This succeeds and the first
888+
# command is not executed inside an implicit transaction block, causing
889+
# a WARNING.
890+
$node->pgbench(
891+
'-t 1 -n -M extended',
892+
0,
893+
[],
894+
[qr{WARNING: SET LOCAL can only be used in transaction blocks}],
895+
'SET LOCAL outside implicit transaction block of pipeline',
896+
{
897+
'001_pgbench_pipeline_set_local_1' => q{
898+
\startpipeline
899+
SET LOCAL statement_timeout='1h';
900+
\endpipeline
901+
}
902+
});
903+
904+
# Try SET LOCAL as second pipeline command. This succeeds and the second
905+
# command does not cause a WARNING to be generated.
906+
$node->pgbench(
907+
'-t 1 -n -M extended',
908+
0,
909+
[],
910+
[qr{^$}],
911+
'SET LOCAL inside implicit transaction block of pipeline',
912+
{
913+
'001_pgbench_pipeline_set_local_2' => q{
914+
\startpipeline
915+
SELECT 1;
916+
SET LOCAL statement_timeout='1h';
917+
\endpipeline
918+
}
919+
});
920+
921+
# Try REINDEX CONCURRENTLY as first pipeline command. This succeeds
922+
# as the first command is outside the implicit transaction block of
923+
# a pipeline.
924+
$node->pgbench(
925+
'-t 1 -n -M extended',
926+
0,
927+
[],
928+
[],
929+
'REINDEX CONCURRENTLY outside implicit transaction block of pipeline',
930+
{
931+
'001_pgbench_pipeline_reindex_1' => q{
932+
\startpipeline
933+
REINDEX TABLE CONCURRENTLY pgbench_accounts;
934+
SELECT 1;
935+
\endpipeline
936+
}
937+
});
938+
939+
# Try REINDEX CONCURRENTLY as second pipeline command. This fails
940+
# as the second command is inside an implicit transaction block.
941+
$node->pgbench(
942+
'-t 1 -n -M extended',
943+
2,
944+
[],
945+
[],
946+
'error: REINDEX CONCURRENTLY inside implicit transaction block of pipeline',
947+
{
948+
'001_pgbench_pipeline_reindex_2' => q{
949+
\startpipeline
950+
SELECT 1;
951+
REINDEX TABLE CONCURRENTLY pgbench_accounts;
952+
\endpipeline
953+
}
954+
});
955+
956+
# Try VACUUM as first pipeline command. Like REINDEX CONCURRENTLY, this
957+
# succeeds as this is outside the implicit transaction block of a pipeline.
958+
$node->pgbench(
959+
'-t 1 -n -M extended',
960+
0,
961+
[],
962+
[],
963+
'VACUUM outside implicit transaction block of pipeline',
964+
{
965+
'001_pgbench_pipeline_vacuum_1' => q{
966+
\startpipeline
967+
VACUUM pgbench_accounts;
968+
\endpipeline
969+
}
970+
});
971+
972+
# Try VACUUM as second pipeline command. This fails, as the second command
973+
# of a pipeline is inside an implicit transaction block.
974+
$node->pgbench(
975+
'-t 1 -n -M extended',
976+
2,
977+
[],
978+
[],
979+
'error: VACUUM inside implicit transaction block of pipeline',
980+
{
981+
'001_pgbench_pipeline_vacuum_2' => q{
982+
\startpipeline
983+
SELECT 1;
984+
VACUUM pgbench_accounts;
985+
\endpipeline
986+
}
987+
});
988+
989+
# Try subtransactions in a pipeline. These are forbidden in implicit
990+
# transaction blocks.
991+
$node->pgbench(
992+
'-t 1 -n -M extended',
993+
2,
994+
[],
995+
[],
996+
'error: subtransactions not allowed in pipeline',
997+
{
998+
'001_pgbench_pipeline_subtrans' => q{
999+
\startpipeline
1000+
SAVEPOINT a;
1001+
SELECT 1;
1002+
ROLLBACK TO SAVEPOINT a;
1003+
SELECT 2;
1004+
\endpipeline
1005+
}
1006+
});
1007+
1008+
# Try LOCK TABLE as first pipeline command. This fails as LOCK is outside
1009+
# an implicit transaction block.
1010+
$node->pgbench(
1011+
'-t 1 -n -M extended',
1012+
2,
1013+
[],
1014+
[],
1015+
'error: LOCK TABLE outside implicit transaction block of pipeline',
1016+
{
1017+
'001_pgbench_pipeline_lock_1' => q{
1018+
\startpipeline
1019+
LOCK pgbench_accounts;
1020+
SELECT 1;
1021+
\endpipeline
1022+
}
1023+
});
1024+
1025+
# Try LOCK TABLE as second pipeline command. This succeeds as LOCK is inside
1026+
# an implicit transaction block.
1027+
$node->pgbench(
1028+
'-t 1 -n -M extended',
1029+
0,
1030+
[],
1031+
[],
1032+
'LOCK TABLE inside implicit transaction block of pipeline',
1033+
{
1034+
'001_pgbench_pipeline_lock_2' => q{
1035+
\startpipeline
1036+
SELECT 1;
1037+
LOCK pgbench_accounts;
1038+
\endpipeline
1039+
}
1040+
});
1041+
8871042
# Working \startpipeline in prepared query mode with serializable
8881043
$node->pgbench(
8891044
'-c4 -t 10 -n -M prepared',

0 commit comments

Comments
 (0)