public inbox for [email protected]
help / color / mirror / Atom feedFrom: Heikki Linnakangas <[email protected]>
To: [email protected]
Subject: pgsql: Avoid orphaned objects dependencies
Date: Wed, 27 May 2026 15:50:25 +0000
Message-ID: <[email protected]> (raw)
Avoid orphaned objects dependencies
Concurrent DDL can leave behind objects referencing other objects that
no longer exist. This can happen if an object is dropped, while a new
object that depends on it is created concurrently. For example:
session 1: BEGIN; CREATE FUNCTION myschema.myfunc() ...;
session 2: DROP SCHEMA myschema;
session 1: COMMIT;
DROP SCHEMA does check that there are no objects dependending on the
schema being dropped, but it does not see objects being concurrently
created by other sessions. Even if it did, this scenario would still
fail:
session 1: BEGIN: DROP SCHEMA myschema;
session 2: CREATE FUNCTION myschema.myfunc() ...;
session 1: COMMIT;
When the DROP SCHEMA runs, the schema was empty, but the new function
is created in it before the dropping transaction completes. The CREATE
FUNCTION does not see that the schema is concurrently being dropped.
In both of these scenarios, the function is left behind in the schema
that no longer exists.
To fix, acquire AccessShareLock on all referenced objects when
recording dependencies. This conflicts with the AccessExclusiveLock
taken by DROP, preventing the race. After acquiring the lock, verify
that the object still exists, and if it was dropped concurrently,
report an error. We already had such a mechanism for shared
dependencies, but for some reason we didn't do it for in-database
dependendies.
Ideally the locks would be acquired much earlier when creating a new
object, but that will require modifying a lot of callers. This check
while recording the dependency is a nice wholesale protection, and
even if we change all the CREATE commands to acquire locks earlier,
it's still good to have this as a backstop to catch any cases where we
forgot to do so.
The patch adds a few tests for some cases that left behind orphaned
objects before this. It also adds a test for roles, which already had
such protection, although that test is partially disabled because the
error message includes an OID which is not predictable.
Author: Bertrand Drouvot <[email protected]>
Reviewed-by: Heikki Linnakangas <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 14
Branch
------
REL_18_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/c8cd3d6976f7af2eceff8421ec2fd0d2bdc8dc84
Modified Files
--------------
src/backend/catalog/pg_depend.c | 131 ++++++++++++++++++++
.../isolation/expected/ddl-dependency-locking.out | 137 +++++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../isolation/specs/ddl-dependency-locking.spec | 104 ++++++++++++++++
src/test/regress/expected/alter_table.out | 11 +-
5 files changed, 379 insertions(+), 5 deletions(-)
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pgsql: Avoid orphaned objects dependencies
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox