From 7f7e8cc3f2cc95098154a722a6d5c6f61190a043 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 12 Aug 2004 21:00:34 +0000 Subject: [PATCH] Allow commas in BEGIN, START TRANSACTION, and SET TRANSACTION, as required by the SQL standard. For backwards compatibility, however, continue to accept the syntax without. Minor editorialization in the reference pages for these commands, too. --- doc/src/sgml/ref/begin.sgml | 33 +++++----- doc/src/sgml/ref/set_transaction.sgml | 83 +++++++++++++++++-------- doc/src/sgml/ref/start_transaction.sgml | 36 ++++++++--- src/backend/parser/gram.y | 54 +++++++--------- src/backend/tcop/utility.c | 30 +++++---- 5 files changed, 140 insertions(+), 96 deletions(-) diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml index bf1195f527..741d8aa997 100644 --- a/doc/src/sgml/ref/begin.sgml +++ b/doc/src/sgml/ref/begin.sgml @@ -1,5 +1,5 @@ @@ -20,9 +20,12 @@ PostgreSQL documentation -BEGIN [ WORK | TRANSACTION ] - [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ] - [ READ WRITE | READ ONLY ] +BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] + +where transaction_mode is one of: + + ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } + READ WRITE | READ ONLY @@ -101,8 +104,13 @@ BEGIN [ WORK | TRANSACTION ] Issuing BEGIN when already inside a transaction block will provoke a warning message. The state of the transaction is not affected. To nest transactions within a transaction block, use savepoints - (See - for more information). + (see ). + + + + For reasons of backwards compatibility, the commas between successive + transaction_modes may be + omitted. @@ -123,15 +131,10 @@ BEGIN; BEGIN is a PostgreSQL - language extension. There is no explicit BEGIN - command in the SQL standard; transaction initiation is - always implicit and it terminates either with a - COMMIT or ROLLBACK statement. - - - - Other relational database systems may offer an autocommit feature - as a convenience. + language extension. It is equivalent to the SQL-standard command + , which see for additional + compatibility information. diff --git a/doc/src/sgml/ref/set_transaction.sgml b/doc/src/sgml/ref/set_transaction.sgml index ef6dea1ac1..50a8908825 100644 --- a/doc/src/sgml/ref/set_transaction.sgml +++ b/doc/src/sgml/ref/set_transaction.sgml @@ -1,4 +1,4 @@ - + SET TRANSACTION @@ -16,13 +16,13 @@ -SET TRANSACTION - [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ] - [ READ WRITE | READ ONLY ] +SET TRANSACTION transaction_mode [, ...] +SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...] -SET SESSION CHARACTERISTICS AS TRANSACTION - [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ] - [ READ WRITE | READ ONLY ] +where transaction_mode is one of: + + ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } + READ WRITE | READ ONLY @@ -34,7 +34,7 @@ SET SESSION CHARACTERISTICS AS TRANSACTION characteristics of the current transaction. It has no effect on any subsequent transactions. SET SESSION CHARACTERISTICS sets the default transaction - characteristics for each transaction of a session. SET + characteristics for subsequent transactions of a session. SET TRANSACTION can override it for an individual transaction. @@ -47,7 +47,7 @@ SET SESSION CHARACTERISTICS AS TRANSACTION The isolation level of a transaction determines what data the - transaction can see when other transactions are running concurrently. + transaction can see when other transactions are running concurrently: @@ -64,28 +64,35 @@ SET SESSION CHARACTERISTICS AS TRANSACTION SERIALIZABLE - The current transaction can only see rows committed before - first query or data-modification statement was executed in this transaction. + All statements of the current transaction can only see rows committed + before the first query or data-modification statement was executed in + this transaction. Intuitively, serializable means that two concurrent transactions will leave the database in the same state as if - the two has been executed strictly after one another in either - order. + the two had been executed strictly one after the other (in one + order or the other). - The level READ UNCOMMITTED is mapped to - READ COMMITTED, the level REPEATABLE - READ is mapped to SERIALIZABLE, The - transaction isolation level cannot be set after the first query or + The SQL standard defines two additional levels, READ + UNCOMMITTED and REPEATABLE READ. + In PostgreSQL READ + UNCOMMITTED is treated as + READ COMMITTED, while REPEATABLE + READ is treated as SERIALIZABLE. + + + + The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, - UPDATE, FETCH, + UPDATE, FETCH, or COPY) of a transaction has been executed. See for more information about transaction isolation and concurrency control. @@ -112,13 +119,27 @@ SET SESSION CHARACTERISTICS AS TRANSACTION Notes - The session default transaction isolation level can also be set - with the command - -SET default_transaction_isolation = 'value' - - and in the configuration file. Consult for more - information. + If SET TRANSACTION is executed without a prior + START TRANSACTION or BEGIN, + it will appear to have no effect, since the transaction will immediately + end. + + + + It is possible to dispense with SET TRANSACTION by + instead specifying the desired transaction_modes in + START TRANSACTION. + + + + The session default transaction modes can also be set by setting the + configuration parameters + and . + (In fact SET SESSION CHARACTERISTICS is just a + verbose equivalent for setting these variables with SET.) + This allows them to be set in the configuration file. Consult for more information. @@ -131,7 +152,7 @@ SET default_transaction_isolation = 'value' isolation level in the standard; in PostgreSQL the default is ordinarily READ COMMITTED, but you can change it as - described above. Because of multiversion concurrency control, the + mentioned above. Because of multiversion concurrency control, the SERIALIZABLE level is not truly serializable. See for details. @@ -139,7 +160,15 @@ SET default_transaction_isolation = 'value' In the SQL standard, there is one other transaction characteristic that can be set with these commands: the size of the diagnostics - area. This concept is only for use in embedded SQL. + area. This concept is specific to embedded SQL, and therefore is + not implemented in the PostgreSQL server. + + + + The SQL standard requires commas between successive transaction_modes, but for historical + reasons PostgreSQL allows the commas to be + omitted. diff --git a/doc/src/sgml/ref/start_transaction.sgml b/doc/src/sgml/ref/start_transaction.sgml index 1a7bc363ce..823bd99195 100644 --- a/doc/src/sgml/ref/start_transaction.sgml +++ b/doc/src/sgml/ref/start_transaction.sgml @@ -1,5 +1,5 @@ @@ -20,9 +20,12 @@ PostgreSQL documentation -START TRANSACTION - [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ] - [ READ WRITE | READ ONLY ] +START TRANSACTION [ transaction_mode [, ...] ] + +where transaction_mode is one of: + + ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } + READ WRITE | READ ONLY @@ -30,10 +33,10 @@ START TRANSACTION Description - This command begins a new transaction. If the isolation level or + This command begins a new transaction block. If the isolation level or read/write mode is specified, the new transaction has those characteristics, as if was executed. It is the same + endterm="sql-set-transaction-title"> was executed. This is the same as the command. @@ -52,8 +55,25 @@ START TRANSACTION Compatibility - This command conforms to the SQL standard; but see also the - compatibility section of START TRANSACTION + to start a transaction block: any SQL command implicitly begins a block. + PostgreSQL's behavior can be seen as implicitly + issuing a COMMIT after each command that does not + follow START TRANSACTION (or BEGIN), + and it is therefore often called autocommit. + Other relational database systems may offer an autocommit feature + as a convenience. + + + + The SQL standard requires commas between successive transaction_modes, but for historical + reasons PostgreSQL allows the commas to be + omitted. + + + + See also the compatibility section of . diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6a27714b05..e8bc3cf5ad 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.470 2004/08/12 19:12:21 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.471 2004/08/12 21:00:28 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -161,11 +161,11 @@ static void doNegateFloat(Value *v); %type opt_drop_behavior -%type createdb_opt_list copy_opt_list -%type createdb_opt_item copy_opt_item +%type createdb_opt_list copy_opt_list transaction_mode_list +%type createdb_opt_item copy_opt_item transaction_mode_item %type opt_lock lock_type cast_context -%type opt_force opt_or_replace transaction_access_mode +%type opt_force opt_or_replace opt_grant_grant_option opt_revoke_grant_option opt_nowait @@ -222,7 +222,7 @@ static void doNegateFloat(Value *v); target_list update_target_list insert_column_list insert_target_list def_list indirection opt_indirection group_clause TriggerFuncArgs select_limit - opt_select_limit opclass_item_list transaction_mode_list + opt_select_limit opclass_item_list transaction_mode_list_or_empty TableFuncElementList prep_type_clause prep_type_list @@ -4021,27 +4021,26 @@ opt_transaction: WORK {} | /*EMPTY*/ {} ; -transaction_mode_list: +transaction_mode_item: ISOLATION LEVEL iso_level - { $$ = list_make1(makeDefElem("transaction_isolation", - makeStringConst($3, NULL))); } - | transaction_access_mode - { $$ = list_make1(makeDefElem("transaction_read_only", - makeIntConst($1))); } - | ISOLATION LEVEL iso_level transaction_access_mode - { - $$ = list_make2(makeDefElem("transaction_isolation", - makeStringConst($3, NULL)), - makeDefElem("transaction_read_only", - makeIntConst($4))); - } - | transaction_access_mode ISOLATION LEVEL iso_level - { - $$ = list_make2(makeDefElem("transaction_read_only", - makeIntConst($1)), - makeDefElem("transaction_isolation", - makeStringConst($4, NULL))); - } + { $$ = makeDefElem("transaction_isolation", + makeStringConst($3, NULL)); } + | READ ONLY + { $$ = makeDefElem("transaction_read_only", + makeIntConst(TRUE)); } + | READ WRITE + { $$ = makeDefElem("transaction_read_only", + makeIntConst(FALSE)); } + ; + +/* Syntax with commas is SQL-spec, without commas is Postgres historical */ +transaction_mode_list: + transaction_mode_item + { $$ = list_make1($1); } + | transaction_mode_list ',' transaction_mode_item + { $$ = lappend($1, $3); } + | transaction_mode_list transaction_mode_item + { $$ = lappend($1, $2); } ; transaction_mode_list_or_empty: @@ -4050,11 +4049,6 @@ transaction_mode_list_or_empty: { $$ = NIL; } ; -transaction_access_mode: - READ ONLY { $$ = TRUE; } - | READ WRITE { $$ = FALSE; } - ; - /***************************************************************************** * diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 89ac4843ba..0fff253a61 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.224 2004/08/12 19:12:21 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.225 2004/08/12 21:00:34 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -333,23 +333,21 @@ ProcessUtility(Node *parsetree, case TRANS_STMT_BEGIN: case TRANS_STMT_START: { - BeginTransactionBlock(); + ListCell *lc; - if (stmt->options) + BeginTransactionBlock(); + foreach(lc, stmt->options) { - ListCell *head; - - foreach(head, stmt->options) - { - DefElem *item = (DefElem *) lfirst(head); - - if (strcmp(item->defname, "transaction_isolation") == 0) - SetPGVariable("transaction_isolation", - list_make1(item->arg), false); - else if (strcmp(item->defname, "transaction_read_only") == 0) - SetPGVariable("transaction_read_only", - list_make1(item->arg), false); - } + DefElem *item = (DefElem *) lfirst(lc); + + if (strcmp(item->defname, "transaction_isolation") == 0) + SetPGVariable("transaction_isolation", + list_make1(item->arg), + false); + else if (strcmp(item->defname, "transaction_read_only") == 0) + SetPGVariable("transaction_read_only", + list_make1(item->arg), + false); } } break; -- 2.39.5