Postgres Pro
Downloads
Home   >   mailing lists

Thread: Bogus NULL object_name from pg_event_trigger_dropped_objects()

Bogus NULL object_name from pg_event_trigger_dropped_objects()

From
Tom Lane
Date:
I chanced to experiment with the event trigger function shown in
9.29.2. Processing Objects Dropped by a DDL Command [1].
I got

et=# create table t1(f1 text primary key);
CREATE TABLE
et=# drop table t1;
NOTICE:  DROP TABLE dropped object: table public.t1 public.t1
NOTICE:  DROP TABLE dropped object: type public.t1 public.t1
NOTICE:  DROP TABLE dropped object: type public._t1 public.t1[]
NOTICE:  DROP TABLE dropped object: toast table pg_toast.pg_toast_38092 pg_toast.pg_toast_38092
NOTICE:  DROP TABLE dropped object: index pg_toast.pg_toast_38092_index pg_toast.pg_toast_38092_index
NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> t1_pkey on public.t1
NOTICE:  DROP TABLE dropped object: index public.t1_pkey public.t1_pkey
DROP TABLE
et=#

Note the faulty output for the pkey constraint.  I've not looked
for the cause.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/functions-event-triggers.html#PG-EVENT-TRIGGER-SQL-DROP-FUNCTIONS

Re: Bogus NULL object_name from pg_event_trigger_dropped_objects()

From
Alvaro Herrera
Date:
On 2021-Nov-15, Tom Lane wrote:

> I chanced to experiment with the event trigger function shown in
> 9.29.2. Processing Objects Dropped by a DDL Command [1].
> I got
> 
> et=# create table t1(f1 text primary key);
> CREATE TABLE
> et=# drop table t1;
> NOTICE:  DROP TABLE dropped object: table public.t1 public.t1
> NOTICE:  DROP TABLE dropped object: type public.t1 public.t1
> NOTICE:  DROP TABLE dropped object: type public._t1 public.t1[]
> NOTICE:  DROP TABLE dropped object: toast table pg_toast.pg_toast_38092 pg_toast.pg_toast_38092
> NOTICE:  DROP TABLE dropped object: index pg_toast.pg_toast_38092_index pg_toast.pg_toast_38092_index
> NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> t1_pkey on public.t1
> NOTICE:  DROP TABLE dropped object: index public.t1_pkey public.t1_pkey
> DROP TABLE
> et=# 
> 
> Note the faulty output for the pkey constraint.  I've not looked
> for the cause.

Egad.

Looking ...

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Re: Bogus NULL object_name from pg_event_trigger_dropped_objects()

From
Alvaro Herrera
Date:
On 2021-Nov-15, Tom Lane wrote:

> NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> t1_pkey on public.t1
> NOTICE:  DROP TABLE dropped object: index public.t1_pkey public.t1_pkey
> DROP TABLE

> 
> Note the faulty output for the pkey constraint.  I've not looked
> for the cause.

Ah yeah.  For objects which have a "schema/name" combination that is
unique (is_nsp_name_unique in ObjectPropertyType), we supply a name,
otherwise we don't.  Constraints don't have unique names in a schema
(this also applies to other objects that live under such as triggers and
rules).

Strangely, the schema is also null for triggers, but not for
constraints; I don't remember what the reason is for constraints to have
namespaces, but triggers don't have them.

Anyway, although this is documented, I'm not 100% sure that it was the
best possible design decision.  As I recall, the idea was that the user
should be able to construct an equivalent DROP command for the object in
a replica server, and the schema/name combination could not be used for
that purpose for things like constraints, so we decided to leave the
name null.  

The code for this is in EventTriggerSQLDropAddObject, which could
perhaps stand some additional commentary.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"How strange it is to find the words "Perl" and "saner" in such close
proximity, with no apparent sense of irony. I doubt that Larry himself
could have managed it."         (ncm, http://lwn.net/Articles/174769/)