Patch to avoid orphaned dependencies

Hi,

This new thread is a follow-up of [1].

Problem description:

We have occasionally observed objects having an orphaned dependency, the most common case we have seen (if not the only one) is functions not linked to any namespaces.
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.

The attached patch is avoiding those race conditions globally by changing the dependency mechanism: we are using a dirty snapshot any time we’re about to create a pg_depend or pg_shdepend entry.
That way we can check if there is in-flight transactions that are affecting the dependency: if that’s the case, an error is being reported.

This approach has been chosen over another one that would have make use of the locking machinery.
The reason for this choice is to avoid possible slow down of typical DDL command, risk of deadlock, number of locks taken by transaction...

Implementation overview:

• A new catalog snapshot is added: DirtyCatalogSnapshot.
• This catalog snapshot is a dirty one to be able to look for in-flight dependencies.
• Its usage is controlled by a new UseDirtyCatalogSnapshot variable.
• Any time this variable is being set to true, then the next call to GetNonHistoricCatalogSnapshot() is returning the dirty snapshot.
• This snapshot is being used to check for in-flight dependencies and also to get the objects description to generate the error messages.

Testing:

Test 1

Session1:

postgres=# create schema tobeorph;
CREATE SCHEMA
postgres=# create table tobeorph.bdt (a int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# CREATE OR REPLACE FUNCTION tobeorph.bdttime() RETURNS TIMESTAMP AS $$
DECLARE   outTS TIMESTAMP;
BEGIN   perform pg_sleep(10);   RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql volatile;
CREATE FUNCTION

Session 1 does not commit, then session 2:

postgres=# drop schema tobeorph;
ERROR:  cannot drop schema tobeorph because other objects depend on it
DETAIL:  table tobeorph.bdt depends on schema tobeorph
function tobeorph.bdttime() (not yet committed) depends on schema tobeorph
HINT:  DROP and DROP CASCADE won't work when there are uncommitted dependencies.

Test 2

Session 1:

postgres=# create schema toinsert;
CREATE SCHEMA
postgres=# begin;
BEGIN
postgres=*# drop schema toinsert;
DROP SCHEMA

Session 1 does not commit, then session 2:

postgres=# CREATE OR REPLACE FUNCTION toinsert.bdttime() RETURNS TIMESTAMP AS $$
DECLARE   outTS TIMESTAMP;
BEGIN   perform pg_sleep(10);   RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql volatile;
ERROR:  cannot create function toinsert.bdttime() because it depends of other objects uncommitted dependencies
DETAIL:  function toinsert.bdttime() depends on schema toinsert (dependency not yet committed)
HINT:  CREATE won't work as long as there is uncommitted dependencies.

Test3

Session1:
psql -U toorph postgres
psql (14devel)
Type "help" for help.

postgres=> begin;
BEGIN
postgres=*> CREATE OR REPLACE FUNCTION bdttime() RETURNS TIMESTAMP AS $$
DECLARE   outTS TIMESTAMP;
BEGIN   perform pg_sleep(10);   RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql volatile;
CREATE FUNCTION

Session 1 does not commit, then session 2:

postgres=# drop owned by toorph;
ERROR:  cannot drop objects owned by role toorph because other uncommitted objects depend on it
DETAIL:  function public.bdttime() (not yet committed) depends on role toorph
HINT:  Commit or rollback function public.bdttime() creation.

I'm creating a new commitfest entry for this patch.

Thanks

Bertrand

[1]: https://www.postgresql.org/message-id/flat/a4f55089-7cbd-fe5d-a9bb-19adc6418ae9%40darold.net#9af5cdaa9e80879beb1def3604c976e8