Hi,
This new thread is a follow-up of [1] and [2].
Problem description:
We have occasionally observed objects having an orphaned dependency, the
most common case we have seen is functions not linked to any namespaces.
Examples to produce such orphaned dependencies:
Scenario 1:
session 1: begin; drop schema schem;
session 2: create a function in the schema schem
session 1: commit;
With the above, the function created in session 2 would be linked to a non
existing schema.
Scenario 2:
session 1: begin; create a function in the schema schem
session 2: drop schema schem;
session 1: commit;
With the above, the function created in session 1 would be linked to a non
existing schema.
A patch has been initially proposed to fix this particular
(function-to-namespace) dependency (see [1]), but there could be much
more scenarios (like the function-to-datatype one highlighted by Gilles
in [1] that could lead to a function having an invalid parameter datatype).
As Tom said there are dozens more cases that would need to be
considered, and a global approach to avoid those race conditions should
be considered instead.
A first global approach attempt has been proposed in [2] making use of a dirty
snapshot when recording the dependency. But this approach appeared to be "scary"
and it was still failing to close some race conditions (see [2] for details).
Then, Tom proposed another approach in [2] which is that "creation DDL will have
to take a lock on each referenced object that'd conflict with a lock taken by
DROP".
This is what the attached patch is trying to achieve.
It does the following:
1) A new lock (that conflicts with a lock taken by DROP) has been put in place
when the dependencies are being recorded.
Thanks to it, the drop schema in scenario 2 would be locked (resulting in an
error should session 1 committs).
2) After locking the object while recording the dependency, the patch checks
that the object still exists.
Thanks to it, session 2 in scenario 1 would be locked and would report an error
once session 1 committs (that would not be the case should session 1 abort the
transaction).
The patch also adds a few tests for some dependency cases (that would currently
produce orphaned objects):
- schema and function (as the above scenarios)
- function and type
- table and type (which is I think problematic enough, as involving a table into
the game, to fix this stuff as a whole).
[1]:
https://www.postgresql.org/message-id/flat/a4f55089-7cbd-fe5d-a9bb-19adc6418...@darold.net#9af5cdaa9e80879beb1def3604c976e8
[2]:
https://www.postgresql.org/message-id/flat/8369ff70-0e31-f194-2954-787f4d9e21dd%40amazon.com
Please note that I'm not used to with this area of the code so that the patch
might not take the option proposed by Tom the "right" way.
Adding the patch to the July CF.
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From 6798e85b0679dfccfa665007b06d9f1a0e39e0c6 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 29 Mar 2024 15:43:26 +0000
Subject: [PATCH v1] Avoid orphaned objects dependencies
It's currently possible to create orphaned objects dependencies, for example:
Scenario 1:
session 1: begin; drop schema schem;
session 2: create a function in the schema schem
session 1: commit;
With the above, the function created in session 2 would be linked to a non
existing schema.
Scenario 2:
session 1: begin; create a function in the schema schem
session 2: drop schema schem;
session 1: commit;
With the above, the function created in session 1 would be linked to a non
existing schema.
To avoid those scenarios, a new lock (that conflicts with a lock taken by DROP)
has been put in place when the dependencies are being recorded. With this in
place, the drop schema in scenario 2 would be locked.
Also, after locking the object, the patch checks that the object still exists:
with this in place session 2 in scenario 1 would be locked and would report an
error once session 1 committs (that would not be the case should session 1 abort
the transaction).
The patch adds a few tests for some dependency cases (that would currently produce
orphaned objects):
- schema and function (as the above scenarios)
- function and type
- table and type
---
src/backend/catalog/dependency.c | 42 ++++++++++++++
src/backend/catalog/objectaddress.c | 57 +++++++++++++++++++
src/backend/catalog/pg_depend.c | 6 ++
src/include/catalog/dependency.h | 1 +
src/include/catalog/objectaddress.h | 1 +
src/test/modules/Makefile | 1 +
src/test/modules/meson.build | 1 +
.../test_dependencies_locks/.gitignore | 3 +
.../modules/test_dependencies_locks/Makefile | 14 +++++
.../expected/test_dependencies_locks.out | 49 ++++++++++++++++
.../test_dependencies_locks/meson.build | 12 ++++
.../specs/test_dependencies_locks.spec | 39 +++++++++++++
12 files changed, 226 insertions(+)
34.6% src/backend/catalog/
32.7% src/test/modules/test_dependencies_locks/expected/
20.7% src/test/modules/test_dependencies_locks/specs/
9.2% src/test/modules/test_dependencies_locks/
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index d4b5b2ade1..2251145e3b 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1517,6 +1517,48 @@ AcquireDeletionLock(const ObjectAddress *object, int flags)
}
}
+/*
+ * depLockAndCheckObject
+ *
+ * Lock the object that we are about to record a dependency on.
+ * After it's locked, verify that it hasn't been dropped while we
+ * weren't looking. If the object has been dropped, this function
+ * does not return!
+ */
+void
+depLockAndCheckObject(const ObjectAddress *object)
+{
+ char *object_description;
+
+ /*
+ * Those don't rely on LockDatabaseObject() when being dropped (see
+ * AcquireDeletionLock()). Also it looks like they can not produce
+ * orphaned dependent objects when being dropped.
+ */
+ if (object->classId == RelationRelationId || object->classId == AuthMemRelationId)
+ return;
+
+ object_description = getObjectDescription(object, true);
+
+ /*
+ * If we don't get a description then there is no need to worry about this
+ * object as it is certainly not in the progress of being dropped.
+ */
+ if (!object_description)
+ return;
+
+ /* assume we should lock the whole object not a sub-object */
+ LockDatabaseObject(object->classId, object->objectId, 0, AccessShareLock);
+
+ /* check if object still exists */
+ if (!ObjectByIdExist(object))
+ ereport(ERROR, errmsg("%s does not exist", object_description));
+
+ pfree(object_description);
+
+ return;
+}
+
/*
* ReleaseDeletionLock - release an object deletion lock
*
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..8cb1adae89 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2590,6 +2590,63 @@ get_object_namespace(const ObjectAddress *address)
return oid;
}
+/*
+ * ObjectByIdExist
+ *
+ * Return whether the given object exists.
+ *
+ * Works for most catalogs, if no special processing is needed.
+ */
+bool
+ObjectByIdExist(const ObjectAddress *address)
+{
+ int cache;
+ HeapTuple tuple;
+ const ObjectPropertyType *property;
+
+ property = get_object_property_data(address->classId);
+
+ cache = property->oid_catcache_id;
+
+ if (cache >= 0)
+ {
+ /* Fetch tuple from syscache. */
+ tuple = SearchSysCache1(cache, ObjectIdGetDatum(address->objectId));
+
+ if (!HeapTupleIsValid(tuple))
+ {
+ return false;
+ }
+
+ ReleaseSysCache(tuple);
+
+ return true;
+ }
+ else
+ {
+ Relation rel;
+ ScanKeyData skey[1];
+ SysScanDesc scan;
+
+ rel = table_open(address->classId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ get_object_attnum_oid(address->classId),
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(address->objectId));
+
+ scan = systable_beginscan(rel, get_object_oid_index(address->classId), true,
+ NULL, 1, skey);
+
+ /* we expect exactly one match */
+ tuple = systable_getnext(scan);
+ systable_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ return (HeapTupleIsValid(tuple));
+ }
+}
+
/*
* Return ObjectType for the given object type as given by
* getObjectTypeDescription; if no valid ObjectType code exists, but it's a
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index f85a898de8..f7b0ad3a42 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -106,6 +106,12 @@ recordMultipleDependencies(const ObjectAddress *depender,
if (isObjectPinned(referenced))
continue;
+ /*
+ * Acquire a lock and check object still exists while recording the
+ * dependency.
+ */
+ depLockAndCheckObject(referenced);
+
if (slot_init_count < max_slots)
{
slot[slot_stored_count] = MakeSingleTupleTableSlot(RelationGetDescr(dependDesc),
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index ec654010d4..8915548711 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -94,6 +94,7 @@ typedef struct ObjectAddresses ObjectAddresses;
/* in dependency.c */
extern void AcquireDeletionLock(const ObjectAddress *object, int flags);
+extern void depLockAndCheckObject(const ObjectAddress *object);
extern void ReleaseDeletionLock(const ObjectAddress *object);
diff --git a/src/include/catalog/objectaddress.h b/src/include/catalog/objectaddress.h
index 3a70d80e32..56f746264b 100644
--- a/src/include/catalog/objectaddress.h
+++ b/src/include/catalog/objectaddress.h
@@ -53,6 +53,7 @@ extern void check_object_ownership(Oid roleid,
Node *object, Relation relation);
extern Oid get_object_namespace(const ObjectAddress *address);
+extern bool ObjectByIdExist(const ObjectAddress *address);
extern bool is_objectclass_supported(Oid class_id);
extern const char *get_object_class_descr(Oid class_id);
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 256799f520..75f357100f 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -17,6 +17,7 @@ SUBDIRS = \
test_copy_callbacks \
test_custom_rmgrs \
test_ddl_deparse \
+ test_dependencies_locks \
test_dsa \
test_dsm_registry \
test_extensions \
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index d8fe059d23..60305dcccd 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -16,6 +16,7 @@ subdir('test_bloomfilter')
subdir('test_copy_callbacks')
subdir('test_custom_rmgrs')
subdir('test_ddl_deparse')
+subdir('test_dependencies_locks')
subdir('test_dsa')
subdir('test_dsm_registry')
subdir('test_extensions')
diff --git a/src/test/modules/test_dependencies_locks/.gitignore b/src/test/modules/test_dependencies_locks/.gitignore
new file mode 100644
index 0000000000..bf000faac4
--- /dev/null
+++ b/src/test/modules/test_dependencies_locks/.gitignore
@@ -0,0 +1,3 @@
+# Generated subdirectories
+/log/
+/output_iso
diff --git a/src/test/modules/test_dependencies_locks/Makefile b/src/test/modules/test_dependencies_locks/Makefile
new file mode 100644
index 0000000000..7491048380
--- /dev/null
+++ b/src/test/modules/test_dependencies_locks/Makefile
@@ -0,0 +1,14 @@
+# src/test/modules/test_dependencies_locks/Makefile
+
+ISOLATION = test_dependencies_locks
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_dependencies_locks
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_dependencies_locks/expected/test_dependencies_locks.out b/src/test/modules/test_dependencies_locks/expected/test_dependencies_locks.out
new file mode 100644
index 0000000000..d0980f77d5
--- /dev/null
+++ b/src/test/modules/test_dependencies_locks/expected/test_dependencies_locks.out
@@ -0,0 +1,49 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_begin s1_create_function_in_schema s2_drop_schema s1_commit
+step s1_begin: BEGIN;
+step s1_create_function_in_schema: CREATE FUNCTION testschema.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+step s2_drop_schema: DROP SCHEMA testschema; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_schema: <... completed>
+ERROR: cannot drop schema testschema because other objects depend on it
+
+starting permutation: s2_begin s2_drop_schema s1_create_function_in_schema s2_commit
+step s2_begin: BEGIN;
+step s2_drop_schema: DROP SCHEMA testschema;
+step s1_create_function_in_schema: CREATE FUNCTION testschema.foo() RETURNS int AS 'select 1' LANGUAGE sql; <waiting ...>
+step s2_commit: COMMIT;
+step s1_create_function_in_schema: <... completed>
+ERROR: schema testschema does not exist
+
+starting permutation: s1_begin s1_create_function_with_type s2_drop_foo_type s1_commit
+step s1_begin: BEGIN;
+step s1_create_function_with_type: CREATE FUNCTION footype(num foo) RETURNS int AS 'select 1' LANGUAGE sql;
+step s2_drop_foo_type: DROP TYPE public.foo; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_foo_type: <... completed>
+ERROR: cannot drop type foo because other objects depend on it
+
+starting permutation: s2_begin s2_drop_foo_type s1_create_function_with_type s2_commit
+step s2_begin: BEGIN;
+step s2_drop_foo_type: DROP TYPE public.foo;
+step s1_create_function_with_type: CREATE FUNCTION footype(num foo) RETURNS int AS 'select 1' LANGUAGE sql; <waiting ...>
+step s2_commit: COMMIT;
+step s1_create_function_with_type: <... completed>
+ERROR: type foo does not exist
+
+starting permutation: s1_begin s1_create_table_with_type s2_drop_footab_type s1_commit
+step s1_begin: BEGIN;
+step s1_create_table_with_type: CREATE TABLE tabtype(a footab);
+step s2_drop_footab_type: DROP TYPE public.footab; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_footab_type: <... completed>
+ERROR: cannot drop type footab because other objects depend on it
+
+starting permutation: s2_begin s2_drop_footab_type s1_create_table_with_type s2_commit
+step s2_begin: BEGIN;
+step s2_drop_footab_type: DROP TYPE public.footab;
+step s1_create_table_with_type: CREATE TABLE tabtype(a footab); <waiting ...>
+step s2_commit: COMMIT;
+step s1_create_table_with_type: <... completed>
+ERROR: type footab does not exist
diff --git a/src/test/modules/test_dependencies_locks/meson.build b/src/test/modules/test_dependencies_locks/meson.build
new file mode 100644
index 0000000000..92a978ab93
--- /dev/null
+++ b/src/test/modules/test_dependencies_locks/meson.build
@@ -0,0 +1,12 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+tests += {
+ 'name': 'test_dependencies_locks',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'isolation': {
+ 'specs': [
+ 'test_dependencies_locks',
+ ],
+ },
+}
diff --git a/src/test/modules/test_dependencies_locks/specs/test_dependencies_locks.spec b/src/test/modules/test_dependencies_locks/specs/test_dependencies_locks.spec
new file mode 100644
index 0000000000..fd15bd2a78
--- /dev/null
+++ b/src/test/modules/test_dependencies_locks/specs/test_dependencies_locks.spec
@@ -0,0 +1,39 @@
+setup
+{
+ CREATE SCHEMA testschema;
+ CREATE TYPE public.foo as enum ('one', 'two');
+ CREATE TYPE public.footab as enum ('three', 'four');
+}
+
+teardown
+{
+ DROP FUNCTION IF EXISTS testschema.foo();
+ DROP FUNCTION IF EXISTS footype(num foo);
+ DROP TABLE IF EXISTS tabtype;
+ DROP SCHEMA IF EXISTS testschema;
+ DROP TYPE IF EXISTS public.foo;
+ DROP TYPE IF EXISTS public.footab;
+}
+
+session "s1"
+
+step "s1_begin" { BEGIN; }
+step "s1_create_function_in_schema" { CREATE FUNCTION testschema.foo() RETURNS int AS 'select 1' LANGUAGE sql; }
+step "s1_create_function_with_type" { CREATE FUNCTION footype(num foo) RETURNS int AS 'select 1' LANGUAGE sql; }
+step "s1_create_table_with_type" { CREATE TABLE tabtype(a footab); }
+step "s1_commit" { COMMIT; }
+
+session "s2"
+
+step "s2_begin" { BEGIN; }
+step "s2_drop_schema" { DROP SCHEMA testschema; }
+step "s2_drop_foo_type" { DROP TYPE public.foo; }
+step "s2_drop_footab_type" { DROP TYPE public.footab; }
+step "s2_commit" { COMMIT; }
+
+permutation "s1_begin" "s1_create_function_in_schema" "s2_drop_schema" "s1_commit"
+permutation "s2_begin" "s2_drop_schema" "s1_create_function_in_schema" "s2_commit"
+permutation "s1_begin" "s1_create_function_with_type" "s2_drop_foo_type" "s1_commit"
+permutation "s2_begin" "s2_drop_foo_type" "s1_create_function_with_type" "s2_commit"
+permutation "s1_begin" "s1_create_table_with_type" "s2_drop_footab_type" "s1_commit"
+permutation "s2_begin" "s2_drop_footab_type" "s1_create_table_with_type" "s2_commit"
--
2.34.1
