From 13752743bf7010f7cbf07bdf2a0087c3960121c5 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Wed, 1 Feb 2017 17:52:35 -0500 Subject: [PATCH] Don't count background workers against a user's connection limit. Doing so doesn't seem to be within the purpose of the per user connection limits, and has particularly unfortunate effects in conjunction with parallel queries. Backpatch to 9.6 where parallel queries were introduced. David Rowley, reviewed by Robert Haas and Albe Laurenz. --- doc/src/sgml/ref/create_database.sgml | 3 ++- doc/src/sgml/ref/create_role.sgml | 5 +++- src/backend/access/transam/twophase.c | 1 + src/backend/storage/ipc/procarray.c | 34 +++++++++++++++++++++++++++ src/backend/storage/lmgr/proc.c | 2 ++ src/backend/utils/init/postinit.c | 2 +- src/include/storage/proc.h | 2 ++ src/include/storage/procarray.h | 1 + 8 files changed, 47 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index cf33746c1e..cf0d53b301 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -258,7 +258,8 @@ CREATE DATABASE name The CONNECTION LIMIT option is only enforced approximately; if two new sessions start at about the same time when just one connection slot remains for the database, it is possible that - both will fail. Also, the limit is not enforced against superusers. + both will fail. Also, the limit is not enforced against superusers or + background worker processes. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index a3b8ed9ab4..2ae576ede6 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -198,7 +198,10 @@ CREATE ROLE name [ [ WITH ] If role can log in, this specifies how many concurrent connections - the role can make. -1 (the default) means no limit. + the role can make. -1 (the default) means no limit. Note that only + normal connections are counted towards this limit. Neither prepared + transactions nor background worker connections are counted towards + this limit. diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c index 5415604993..ea7bfa0578 100644 --- a/src/backend/access/transam/twophase.c +++ b/src/backend/access/transam/twophase.c @@ -420,6 +420,7 @@ MarkAsPreparing(TransactionId xid, const char *gid, proc->backendId = InvalidBackendId; proc->databaseId = databaseid; proc->roleId = owner; + proc->isBackgroundWorker = false; proc->lwWaiting = false; proc->lwWaitMode = 0; proc->waitLock = NULL; diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 4adb286d5b..651c561dfd 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -2751,6 +2751,38 @@ CountDBBackends(Oid databaseid) return count; } +/* + * CountDBConnections --- counts database backends ignoring any background + * worker processes + */ +int +CountDBConnections(Oid databaseid) +{ + ProcArrayStruct *arrayP = procArray; + int count = 0; + int index; + + LWLockAcquire(ProcArrayLock, LW_SHARED); + + for (index = 0; index < arrayP->numProcs; index++) + { + int pgprocno = arrayP->pgprocnos[index]; + volatile PGPROC *proc = &allProcs[pgprocno]; + + if (proc->pid == 0) + continue; /* do not count prepared xacts */ + if (proc->isBackgroundWorker) + continue; /* do not count background workers */ + if (!OidIsValid(databaseid) || + proc->databaseId == databaseid) + count++; + } + + LWLockRelease(ProcArrayLock); + + return count; +} + /* * CancelDBBackends --- cancel backends that are using specified database */ @@ -2810,6 +2842,8 @@ CountUserBackends(Oid roleid) if (proc->pid == 0) continue; /* do not count prepared xacts */ + if (proc->isBackgroundWorker) + continue; /* do not count background workers */ if (proc->roleId == roleid) count++; } diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 9a758bd916..cb6e1e3e5b 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -372,6 +372,7 @@ InitProcess(void) MyProc->backendId = InvalidBackendId; MyProc->databaseId = InvalidOid; MyProc->roleId = InvalidOid; + MyProc->isBackgroundWorker = IsBackgroundWorker; MyPgXact->delayChkpt = false; MyPgXact->vacuumFlags = 0; /* NB -- autovac launcher intentionally does not set IS_AUTOVACUUM */ @@ -544,6 +545,7 @@ InitAuxiliaryProcess(void) MyProc->backendId = InvalidBackendId; MyProc->databaseId = InvalidOid; MyProc->roleId = InvalidOid; + MyProc->isBackgroundWorker = IsBackgroundWorker; MyPgXact->delayChkpt = false; MyPgXact->vacuumFlags = 0; MyProc->lwWaiting = false; diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 824d5abf11..cde4a798b6 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -350,7 +350,7 @@ CheckMyDatabase(const char *name, bool am_superuser) */ if (dbform->datconnlimit >= 0 && !am_superuser && - CountDBBackends(MyDatabaseId) > dbform->datconnlimit) + CountDBConnections(MyDatabaseId) > dbform->datconnlimit) ereport(FATAL, (errcode(ERRCODE_TOO_MANY_CONNECTIONS), errmsg("too many connections for database \"%s\"", diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 775c66a197..c44d82b90c 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -102,6 +102,8 @@ struct PGPROC Oid databaseId; /* OID of database this backend is using */ Oid roleId; /* OID of role using this backend */ + bool isBackgroundWorker; /* true if background worker. */ + /* * While in hot standby mode, shows that a conflict signal has been sent * for the current transaction. Set/cleared while holding ProcArrayLock, diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h index dd37c0cb07..ecc3c4a4f6 100644 --- a/src/include/storage/procarray.h +++ b/src/include/storage/procarray.h @@ -73,6 +73,7 @@ extern pid_t CancelVirtualTransaction(VirtualTransactionId vxid, ProcSignalReaso extern bool MinimumActiveBackends(int min); extern int CountDBBackends(Oid databaseid); +extern int CountDBConnections(Oid databaseid); extern void CancelDBBackends(Oid databaseid, ProcSignalReason sigmode, bool conflictPending); extern int CountUserBackends(Oid roleid); extern bool CountOtherDBBackends(Oid databaseId, -- 2.40.0