public inbox for [email protected]help / color / mirror / Atom feed
pgsql: Avoid orphaned objects dependencies 6+ messages / 1 participants [nested] [flat]
* pgsql: Avoid orphaned objects dependencies @ 2026-05-27 15:50 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Heikki Linnakangas @ 2026-05-27 15:50 UTC (permalink / raw) To: [email protected] 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_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/5100bdbd3ba20caf00c8074b61326f80d9255a65 Modified Files -------------- src/backend/catalog/pg_depend.c | 136 ++++++++++++++++++++ .../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, 384 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid orphaned objects dependencies @ 2026-05-27 15:50 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Heikki Linnakangas @ 2026-05-27 15:50 UTC (permalink / raw) To: [email protected] 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 ------ master Details ------- https://git.postgresql.org/pg/commitdiff/2fbb21170e9053720c2c374b21eb650a22b8aaea 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid orphaned objects dependencies @ 2026-05-27 15:50 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Heikki Linnakangas @ 2026-05-27 15:50 UTC (permalink / raw) To: [email protected] 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid orphaned objects dependencies @ 2026-05-27 15:50 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Heikki Linnakangas @ 2026-05-27 15:50 UTC (permalink / raw) To: [email protected] 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_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/3a9909eda20775658903889b6150b756e5a8a57d Modified Files -------------- src/backend/catalog/pg_depend.c | 133 +++++++++++++++++++- .../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, 380 insertions(+), 6 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid orphaned objects dependencies @ 2026-05-27 15:50 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Heikki Linnakangas @ 2026-05-27 15:50 UTC (permalink / raw) To: [email protected] 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_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d9bc0d96c247971973f297ffe59a30c01e0cd06a 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Avoid orphaned objects dependencies @ 2026-05-27 15:50 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Heikki Linnakangas @ 2026-05-27 15:50 UTC (permalink / raw) To: [email protected] 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_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/5fa137727db05d5084987362a5ecfb0111afca27 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-05-27 15:50 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-27 15:50 pgsql: Avoid orphaned objects dependencies Heikki Linnakangas <[email protected]> 2026-05-27 15:50 pgsql: Avoid orphaned objects dependencies Heikki Linnakangas <[email protected]> 2026-05-27 15:50 pgsql: Avoid orphaned objects dependencies Heikki Linnakangas <[email protected]> 2026-05-27 15:50 pgsql: Avoid orphaned objects dependencies Heikki Linnakangas <[email protected]> 2026-05-27 15:50 pgsql: Avoid orphaned objects dependencies Heikki Linnakangas <[email protected]> 2026-05-27 15:50 pgsql: Avoid orphaned objects dependencies Heikki Linnakangas <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox