diff -durpN postgresql.1/doc/src/sgml/config.sgml postgresql.2/doc/src/sgml/config.sgml --- postgresql.1/doc/src/sgml/config.sgml 2012-07-05 06:51:30.540155082 +0200 +++ postgresql.2/doc/src/sgml/config.sgml 2012-07-22 21:39:00.856924933 +0200 @@ -4953,7 +4953,10 @@ COPY postgres_log FROM '/full/path/to/lo milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be - logged. A value of zero (the default) turns this off. + logged. The timeout may happen any time, i.e. while waiting for locks + on database objects or in case of a large result set, during data + retrieval from the server after all locks were successfully acquired. + A value of zero (the default) turns this off. @@ -4964,6 +4967,60 @@ COPY postgres_log FROM '/full/path/to/lo + + lock_timeout (integer) + + lock_timeout configuration parameter + + + + Abort any statement that tries to acquire a heavy-weight lock on rows, + pages, tables, indices or other objects and the lock(s) has to wait + more than the specified number of milliseconds. As opposed to + statement_timeout, this timeout (and the error) may only + occur while waiting for locks. If log_min_error_statement + is set to ERROR or lower, the statement that timed out will + also be logged. A value of zero (the default) turns this off. + + + + Setting lock_timeout in + postgresql.conf is not recommended because it + affects all sessions. + + + + + + lock_timeout_option (enum) + + lock_timeout_option configuration parameter + + + + Control the behaviour of lock_timeout. Possible values are + 'per_lock' and 'per_statement'. + + + + With 'per_lock' in effect and if the statement involves more than one + lock, the timeout applies to every one of them individually, starting + from the time the server attempts to lock an object. This makes the + statement possibly wait for up to N * lock_timeout time in + the worst case where N is the number of locks attempted. This is the + default. + + + + With 'per_statement' in effect, lock_timeout behaves like + statement_timeout: the specified timeout applies to all + the locks in a cumulative way, starting from the time the command + arrives at the server from the client. + + + + + vacuum_freeze_table_age (integer) diff -durpN postgresql.1/doc/src/sgml/ref/lock.sgml postgresql.2/doc/src/sgml/ref/lock.sgml --- postgresql.1/doc/src/sgml/ref/lock.sgml 2012-04-16 19:57:22.229913063 +0200 +++ postgresql.2/doc/src/sgml/ref/lock.sgml 2012-07-22 21:39:00.857924939 +0200 @@ -39,8 +39,11 @@ LOCK [ TABLE ] [ ONLY ] NOWAIT is specified, LOCK TABLE does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an - error is emitted. Once obtained, the lock is held for the - remainder of the current transaction. (There is no UNLOCK + error is emitted. If lock_timeout is set to a value + higher than 0, and the lock cannot be acquired under the specified + timeout value in milliseconds, the command is aborted and an error + is emitted. Once obtained, the lock is held for the remainder of + the current transaction. (There is no UNLOCK TABLE command; locks are always released at transaction end.) diff -durpN postgresql.1/doc/src/sgml/ref/select.sgml postgresql.2/doc/src/sgml/ref/select.sgml --- postgresql.1/doc/src/sgml/ref/select.sgml 2012-04-16 19:57:22.233913109 +0200 +++ postgresql.2/doc/src/sgml/ref/select.sgml 2012-07-22 21:39:00.859924951 +0200 @@ -1199,6 +1199,14 @@ FOR SHARE [ OF 0) + { + switch (LockTimeoutOption) + { + case LOCK_TIMEOUT_PER_LOCK: + enable_timeout_after(LOCK_TIMEOUT, LockTimeout); + break; + case LOCK_TIMEOUT_PER_STMT: + enable_timeout_at(LOCK_TIMEOUT, + TimestampTzPlusMilliseconds( + GetCurrentStatementStartTimestamp(), + LockTimeout)); + break; + default: + elog(ERROR, "unhandled lock_timeout_option value: \"%s\"", + GetConfigOptionByName("lock_timeout_option", NULL)); + break; + } + } +} + + +/* + * Disable LOCK_TIMEOUT + */ +static void +DisableLockTimeout(void) +{ + disable_timeout(LOCK_TIMEOUT, false); +} + + +/* + * Indicator for LOCK_TIMEOUT. + */ +static bool +LockTimeoutCondition(void) +{ + return get_timeout_indicator(LOCK_TIMEOUT); +} + + +/* + * Error reporting for LOCK_TIMEOUT + */ +static void +LockTimeoutError(void) +{ + if (get_timeout_indicator(LOCK_TIMEOUT)) + ereport(ERROR, + (errcode(ERRCODE_QUERY_CANCELED), + errmsg("canceling statement due to lock timeout"))); +} + + /* * STATEMENT_TIMEOUT handler: trigger a query-cancel interrupt. */ diff -durpN postgresql.1/src/backend/utils/misc/guc.c postgresql.2/src/backend/utils/misc/guc.c --- postgresql.1/src/backend/utils/misc/guc.c 2012-07-03 13:50:11.725151648 +0200 +++ postgresql.2/src/backend/utils/misc/guc.c 2012-07-22 21:39:00.865924989 +0200 @@ -389,6 +389,17 @@ static const struct config_enum_entry sy }; /* + * Control behaviour of lock_timeout: + * - timeout applied per lock from the time the lock is attempted to be taken + * - timeout applied per statement from the time the statements has started + */ +static const struct config_enum_entry lock_timeout_options[] = { + {"per_lock", LOCK_TIMEOUT_PER_LOCK, false}, + {"per_statement", LOCK_TIMEOUT_PER_STMT, false}, + {NULL, 0, false} +}; + +/* * Options for enum values stored in other modules */ extern const struct config_enum_entry wal_level_options[]; @@ -1861,6 +1872,17 @@ static struct config_int ConfigureNamesI }, { + {"lock_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Sets the maximum allowed timeout for any lock taken by a statement."), + gettext_noop("A value of 0 turns off the timeout."), + GUC_UNIT_MS + }, + &LockTimeout, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"vacuum_freeze_min_age", PGC_USERSET, CLIENT_CONN_STATEMENT, gettext_noop("Minimum age at which VACUUM should freeze a table row."), NULL @@ -3141,6 +3163,16 @@ static struct config_enum ConfigureNames NULL, NULL, NULL }, + { + {"lock_timeout_option", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Sets the lock_timeout behaviour."), + NULL + }, + &LockTimeoutOption, + LOCK_TIMEOUT_PER_LOCK, lock_timeout_options, + NULL, NULL, NULL + }, + { {"log_error_verbosity", PGC_SUSET, LOGGING_WHAT, gettext_noop("Sets the verbosity of logged messages."), diff -durpN postgresql.1/src/backend/utils/misc/postgresql.conf.sample postgresql.2/src/backend/utils/misc/postgresql.conf.sample --- postgresql.1/src/backend/utils/misc/postgresql.conf.sample 2012-05-14 08:20:56.298830662 +0200 +++ postgresql.2/src/backend/utils/misc/postgresql.conf.sample 2012-07-22 21:39:00.866924995 +0200 @@ -528,6 +528,11 @@ #------------------------------------------------------------------------------ #deadlock_timeout = 1s +#lock_timeout = 0 # timeout value for heavy-weight locks + # taken by statements. 0 disables timeout + # unit in milliseconds, default is 0 +#lock_timeout_option = 'per_lock' # behaviour of lock_timeout. possible + # values are: 'per_lock' or 'per_statement' #max_locks_per_transaction = 64 # min 10 # (change requires restart) # Note: Each lock table slot uses ~270 bytes of shared memory, and there are diff -durpN postgresql.1/src/include/storage/proc.h postgresql.2/src/include/storage/proc.h --- postgresql.1/src/include/storage/proc.h 2012-07-22 16:48:48.548857900 +0200 +++ postgresql.2/src/include/storage/proc.h 2012-07-22 21:39:00.867925001 +0200 @@ -219,8 +219,15 @@ extern PGPROC *PreparedXactProcs; /* configurable options */ extern int DeadlockTimeout; extern int StatementTimeout; +extern int LockTimeout; +extern int LockTimeoutOption; extern bool log_lock_waits; +typedef enum LockTimeoutOptions { + LOCK_TIMEOUT_PER_LOCK, + LOCK_TIMEOUT_PER_STMT +} LockTimeoutOptions; + /* * Function Prototypes diff -durpN postgresql.1/src/include/utils/timeout.h postgresql.2/src/include/utils/timeout.h --- postgresql.1/src/include/utils/timeout.h 2012-07-22 21:34:50.480375707 +0200 +++ postgresql.2/src/include/utils/timeout.h 2012-07-22 21:39:00.867925001 +0200 @@ -25,6 +25,7 @@ typedef enum TimeoutId /* Predefined timeout reasons */ STARTUP_PACKET_TIMEOUT, DEADLOCK_TIMEOUT, + LOCK_TIMEOUT, STATEMENT_TIMEOUT, STANDBY_DEADLOCK_TIMEOUT, STANDBY_TIMEOUT, diff -durpN postgresql.1/src/test/regress/expected/prepared_xacts.out postgresql.2/src/test/regress/expected/prepared_xacts.out --- postgresql.1/src/test/regress/expected/prepared_xacts.out 2012-04-16 19:57:22.776919413 +0200 +++ postgresql.2/src/test/regress/expected/prepared_xacts.out 2012-07-22 21:39:00.868925007 +0200 @@ -198,6 +198,22 @@ set statement_timeout to 2000; SELECT * FROM pxtest3; ERROR: canceling statement due to statement timeout reset statement_timeout; +-- pxtest3 should be locked because of the pending DROP +set lock_timeout to 2000; +SELECT * FROM pxtest3; +ERROR: canceling statement due to lock timeout +reset lock_timeout; +-- Test lock_timeout_option = 'per_statement' and see that lock_timeout +-- triggers instead of statement_timeout if both are set. +-- pxtest3 should be locked because of the pending DROP +set statement_timeout to 2000; +set lock_timeout to 2000; +set lock_timeout_option to 'per_statement'; +SELECT * FROM pxtest3; +ERROR: canceling statement due to lock timeout +reset lock_timeout; +reset statement_timeout; +reset lock_timeout_option; -- Disconnect, we will continue testing in a different backend \c - -- There should still be two prepared transactions @@ -213,6 +229,11 @@ set statement_timeout to 2000; SELECT * FROM pxtest3; ERROR: canceling statement due to statement timeout reset statement_timeout; +-- pxtest3 should be locked because of the pending DROP +set lock_timeout to 2000; +SELECT * FROM pxtest3; +ERROR: canceling statement due to lock timeout +reset lock_timeout; -- Commit table creation COMMIT PREPARED 'regress-one'; \d pxtest2 diff -durpN postgresql.1/src/test/regress/sql/prepared_xacts.sql postgresql.2/src/test/regress/sql/prepared_xacts.sql --- postgresql.1/src/test/regress/sql/prepared_xacts.sql 2012-04-16 19:57:22.796919644 +0200 +++ postgresql.2/src/test/regress/sql/prepared_xacts.sql 2012-07-22 21:39:00.868925007 +0200 @@ -126,6 +126,22 @@ set statement_timeout to 2000; SELECT * FROM pxtest3; reset statement_timeout; +-- pxtest3 should be locked because of the pending DROP +set lock_timeout to 2000; +SELECT * FROM pxtest3; +reset lock_timeout; + +-- Test lock_timeout_option = 'per_statement' and see that lock_timeout +-- triggers instead of statement_timeout if both are set. +-- pxtest3 should be locked because of the pending DROP +set statement_timeout to 2000; +set lock_timeout to 2000; +set lock_timeout_option to 'per_statement'; +SELECT * FROM pxtest3; +reset lock_timeout; +reset statement_timeout; +reset lock_timeout_option; + -- Disconnect, we will continue testing in a different backend \c - @@ -137,6 +153,11 @@ set statement_timeout to 2000; SELECT * FROM pxtest3; reset statement_timeout; +-- pxtest3 should be locked because of the pending DROP +set lock_timeout to 2000; +SELECT * FROM pxtest3; +reset lock_timeout; + -- Commit table creation COMMIT PREPARED 'regress-one'; \d pxtest2