Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uxBBP-00Bfvk-EF for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Sep 2025 21:19:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uxBBN-0042E2-3y for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Sep 2025 21:19:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uxBBM-0042Dt-No for pgsql-hackers@lists.postgresql.org; Fri, 12 Sep 2025 21:19:49 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uxBBJ-000UJg-17 for pgsql-hackers@postgresql.org; Fri, 12 Sep 2025 21:19:49 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 58CLJSlm2472722; Fri, 12 Sep 2025 17:19:29 -0400 From: Tom Lane To: Sergey Shinderuk cc: =?UTF-8?B?0JDQvdGC0YPQsNC9INCS0LjQvtC70LjQvQ==?= , PostgreSQL Hackers Subject: Re: Error with DEFAULT VALUE in temp table In-reply-to: References: <1325838.1753287294@sss.pgh.pa.us> Comments: In-reply-to Sergey Shinderuk message dated "Fri, 12 Sep 2025 14:01:42 +0300" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2456697.1757711921.0@sss.pgh.pa.us> Date: Fri, 12 Sep 2025 17:19:28 -0400 Message-ID: <2472716.1757711968@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2456697.1757711921.1@sss.pgh.pa.us> Sergey Shinderuk writes: > Thank you for working on this. I see you've fixed the patch and > committed it as a0b99fc1220. I tested it a bit and see some side effects > which may be unintentional. Many thanks for double-checking! > 1. SCHEMA lost object_name. Ugh. I was misled first by assuming that EventTriggerSQLDropAddObject didn't have *other* pre-existing bugs, and second by overestimating the test coverage for this function. In particular I thought that this coding pattern: if (is_objectclass_supported(object->classId)) { ... } else { if (object->classId == NamespaceRelationId && isTempNamespace(object->objectId)) obj->istemp = true; } meant that is_objectclass_supported() doesn't return true for NamespaceRelationId --- a conclusion I should have realized was silly, I guess. So that "else" action was unreachable, and the code failed to set "istemp" true for its own temp schema. But I took it on faith and supposed that we weren't filling objname for schemas. I would have spotted the behavior change if event_trigger.sql made any attempt to verify more than a few of the outputs of pg_event_trigger_dropped_objects(), but it didn't. So the attached patch fixes that test script to print all the expected-to-be-stable outputs. > 2. DEFAULT VALUE now has schema_name and object_name. Setting schema_name is expected I think: you can hardly opine that an object is temp unless it's associated with a temp schema. You're right that setting object_name to the table name is the wrong thing, and again I blame that on poor test coverage. > Currently schema_name is null for triggers and policy objects, but that > may be accidental. Double ugh. Triggers and policy objects have this exact same bug. Fixed (and tested) in the attached. I'm tempted to wonder if the objectaddress.c ObjectProperty infrastructure should grow some support for cases like these, but right now I think it'd be about a wash in terms of the amount of code added. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v1-fix-oversights-in-commit-a0b99fc12.patch"; charset="us-ascii" Content-ID: <2456697.1757711921.2@sss.pgh.pa.us> Content-Description: v1-fix-oversights-in-commit-a0b99fc12.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/e= vent_trigger.c index fcdcba009d4..f34868da5ab 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -30,6 +30,7 @@ #include "catalog/pg_opclass.h" #include "catalog/pg_opfamily.h" #include "catalog/pg_parameter_acl.h" +#include "catalog/pg_policy.h" #include "catalog/pg_proc.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_trigger.h" @@ -1302,6 +1303,7 @@ EventTriggerSQLDropAddObject(const ObjectAddress *ob= ject, bool original, bool no MemoryContextSwitchTo(oldcxt); return; } + obj->objname =3D get_namespace_name(object->objectId); } else if (object->classId =3D=3D AttrDefaultRelationId) { @@ -1311,7 +1313,6 @@ EventTriggerSQLDropAddObject(const ObjectAddress *ob= ject, bool original, bool no colobject =3D GetAttrDefaultColumnAddress(object->objectId); if (OidIsValid(colobject.objectId)) { - colobject.objectSubId =3D 0; /* convert to table reference */ if (!obtain_object_name_namespace(&colobject, obj)) { pfree(obj); @@ -1320,6 +1321,90 @@ EventTriggerSQLDropAddObject(const ObjectAddress *o= bject, bool original, bool no } } } + else if (object->classId =3D=3D TriggerRelationId) + { + /* Similarly, a trigger is temp if its table is temp */ + /* Sadly, there's no lsyscache.c support for trigger objects */ + Relation pg_trigger_rel; + ScanKeyData skey[1]; + SysScanDesc sscan; + HeapTuple tuple; + Oid relid; + + /* Fetch the trigger's table OID the hard way */ + pg_trigger_rel =3D table_open(TriggerRelationId, AccessShareLock); + ScanKeyInit(&skey[0], + Anum_pg_trigger_oid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(object->objectId)); + sscan =3D systable_beginscan(pg_trigger_rel, TriggerOidIndexId, true, + NULL, 1, skey); + tuple =3D systable_getnext(sscan); + if (HeapTupleIsValid(tuple)) + relid =3D ((Form_pg_trigger) GETSTRUCT(tuple))->tgrelid; + else + relid =3D InvalidOid; /* shouldn't happen */ + systable_endscan(sscan); + table_close(pg_trigger_rel, AccessShareLock); + /* Do nothing if we didn't find the trigger */ + if (OidIsValid(relid)) + { + ObjectAddress relobject; + + relobject.classId =3D RelationRelationId; + relobject.objectId =3D relid; + /* Arbitrarily set objectSubId nonzero so as not to fill objname */ + relobject.objectSubId =3D 1; + if (!obtain_object_name_namespace(&relobject, obj)) + { + pfree(obj); + MemoryContextSwitchTo(oldcxt); + return; + } + } + } + else if (object->classId =3D=3D PolicyRelationId) + { + /* Similarly, a policy is temp if its table is temp */ + /* Sadly, there's no lsyscache.c support for policy objects */ + Relation pg_policy_rel; + ScanKeyData skey[1]; + SysScanDesc sscan; + HeapTuple tuple; + Oid relid; + + /* Fetch the policy's table OID the hard way */ + pg_policy_rel =3D table_open(PolicyRelationId, AccessShareLock); + ScanKeyInit(&skey[0], + Anum_pg_policy_oid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(object->objectId)); + sscan =3D systable_beginscan(pg_policy_rel, PolicyOidIndexId, true, + NULL, 1, skey); + tuple =3D systable_getnext(sscan); + if (HeapTupleIsValid(tuple)) + relid =3D ((Form_pg_policy) GETSTRUCT(tuple))->polrelid; + else + relid =3D InvalidOid; /* shouldn't happen */ + systable_endscan(sscan); + table_close(pg_policy_rel, AccessShareLock); + /* Do nothing if we didn't find the policy */ + if (OidIsValid(relid)) + { + ObjectAddress relobject; + + relobject.classId =3D RelationRelationId; + relobject.objectId =3D relid; + /* Arbitrarily set objectSubId nonzero so as not to fill objname */ + relobject.objectSubId =3D 1; + if (!obtain_object_name_namespace(&relobject, obj)) + { + pfree(obj); + MemoryContextSwitchTo(oldcxt); + return; + } + } + } else { /* Generic handling for all other object classes */ diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regres= s/expected/event_trigger.out index 0e090cbc375..16e4530708c 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -228,9 +228,15 @@ INSERT INTO undroppable_objs VALUES ('table', 'schema_one.table_three'), ('table', 'audit_tbls.schema_two_table_three'); CREATE TABLE dropped_objects ( - type text, - schema text, - object text + object_type text, + schema_name text, + object_name text, + object_identity text, + address_names text[], + address_args text[], + is_temporary bool, + original bool, + normal bool ); -- This tests errors raised within event triggers; the one in audit_tbls -- uses 2nd-level recursive invocation via test_evtrig_dropped_objects(). @@ -268,8 +274,12 @@ BEGIN END IF; = INSERT INTO dropped_objects - (type, schema, object) VALUES - (obj.object_type, obj.schema_name, obj.object_identity); + (object_type, schema_name, object_name, + object_identity, address_names, address_args, + is_temporary, original, normal) VALUES + (obj.object_type, obj.schema_name, obj.object_name, + obj.object_identity, obj.address_names, obj.address_args, + obj.is_temporary, obj.original, obj.normal); END LOOP; END $$; @@ -325,42 +335,44 @@ NOTICE: table "audit_tbls_schema_two_table_three" d= oes not exist, skipping NOTICE: table "schema_one_table_one" does not exist, skipping NOTICE: table "schema_one_table two" does not exist, skipping NOTICE: table "schema_one_table_three" does not exist, skipping -SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast= '; - type | schema | object = ---------------+------------+------------------------------------- - table column | schema_one | schema_one.table_one.a - schema | | schema_two - table | schema_two | schema_two.table_two - type | schema_two | schema_two.table_two - type | schema_two | schema_two.table_two[] - table | audit_tbls | audit_tbls.schema_two_table_three - type | audit_tbls | audit_tbls.schema_two_table_three - type | audit_tbls | audit_tbls.schema_two_table_three[] - table | schema_two | schema_two.table_three - type | schema_two | schema_two.table_three - type | schema_two | schema_two.table_three[] - function | schema_two | schema_two.add(integer,integer) - aggregate | schema_two | schema_two.newton(integer) - schema | | schema_one - table | schema_one | schema_one.table_one - type | schema_one | schema_one.table_one - type | schema_one | schema_one.table_one[] - table | schema_one | schema_one."table two" - type | schema_one | schema_one."table two" - type | schema_one | schema_one."table two"[] - table | schema_one | schema_one.table_three - type | schema_one | schema_one.table_three - type | schema_one | schema_one.table_three[] +-- exclude TOAST objects because they have unstable names +SELECT * FROM dropped_objects + WHERE schema_name IS NULL OR schema_name <> 'pg_toast'; + object_type | schema_name | object_name | object_= identity | address_names | address_arg= s | is_temporary | original | normal = +--------------+-------------+-------------------------+------------------= -------------------+---------------------------------------+--------------= -----+--------------+----------+-------- + table column | schema_one | | schema_one.table_= one.a | {schema_one,table_one,a} | {} = | f | t | f + schema | | schema_two | schema_two = | {schema_two} | {} = | f | t | f + table | schema_two | table_two | schema_two.table_= two | {schema_two,table_two} | {} = | f | f | t + type | schema_two | table_two | schema_two.table_= two | {schema_two.table_two} | {} = | f | f | f + type | schema_two | _table_two | schema_two.table_= two[] | {schema_two.table_two[]} | {} = | f | f | f + table | audit_tbls | schema_two_table_three | audit_tbls.schema= _two_table_three | {audit_tbls,schema_two_table_three} | {} = | f | t | f + type | audit_tbls | schema_two_table_three | audit_tbls.schema= _two_table_three | {audit_tbls.schema_two_table_three} | {} = | f | f | f + type | audit_tbls | _schema_two_table_three | audit_tbls.schema= _two_table_three[] | {audit_tbls.schema_two_table_three[]} | {} = | f | f | f + table | schema_two | table_three | schema_two.table_= three | {schema_two,table_three} | {} = | f | f | t + type | schema_two | table_three | schema_two.table_= three | {schema_two.table_three} | {} = | f | f | f + type | schema_two | _table_three | schema_two.table_= three[] | {schema_two.table_three[]} | {} = | f | f | f + function | schema_two | | schema_two.add(in= teger,integer) | {schema_two,add} | {integer,inte= ger} | f | f | t + aggregate | schema_two | | schema_two.newton= (integer) | {schema_two,newton} | {integer} = | f | f | t + schema | | schema_one | schema_one = | {schema_one} | {} = | f | t | f + table | schema_one | table_one | schema_one.table_= one | {schema_one,table_one} | {} = | f | f | t + type | schema_one | table_one | schema_one.table_= one | {schema_one.table_one} | {} = | f | f | f + type | schema_one | _table_one | schema_one.table_= one[] | {schema_one.table_one[]} | {} = | f | f | f + table | schema_one | table two | schema_one."table= two" | {schema_one,"table two"} | {} = | f | f | t + type | schema_one | table two | schema_one."table= two" | {"schema_one.\"table two\""} | {} = | f | f | f + type | schema_one | _table two | schema_one."table= two"[] | {"schema_one.\"table two\"[]"} | {} = | f | f | f + table | schema_one | table_three | schema_one.table_= three | {schema_one,table_three} | {} = | f | f | t + type | schema_one | table_three | schema_one.table_= three | {schema_one.table_three} | {} = | f | f | f + type | schema_one | _table_three | schema_one.table_= three[] | {schema_one.table_three[]} | {} = | f | f | f (23 rows) = DROP OWNED BY regress_evt_user; NOTICE: schema "audit_tbls" does not exist, skipping -SELECT * FROM dropped_objects WHERE type =3D 'schema'; - type | schema | object = ---------+--------+------------ - schema | | schema_two - schema | | schema_one - schema | | audit_tbls +SELECT * FROM dropped_objects WHERE object_type =3D 'schema'; + object_type | schema_name | object_name | object_identity | address_name= s | address_args | is_temporary | original | normal = +-------------+-------------+-------------+-----------------+-------------= --+--------------+--------------+----------+-------- + schema | | schema_two | schema_two | {schema_two}= | {} | f | t | f + schema | | schema_one | schema_one | {schema_one}= | {} | f | t | f + schema | | audit_tbls | audit_tbls | {audit_tbls}= | {} | f | t | f (3 rows) = DROP ROLE regress_evt_user; @@ -378,9 +390,10 @@ BEGIN IF NOT r.normal AND NOT r.original THEN CONTINUE; END IF; - RAISE NOTICE 'NORMAL: orig=3D% normal=3D% istemp=3D% type=3D% identit= y=3D% name=3D% args=3D%', + RAISE NOTICE 'NORMAL: orig=3D% normal=3D% istemp=3D% type=3D% identit= y=3D% schema=3D% name=3D% addr=3D% args=3D%', r.original, r.normal, r.is_temporary, r.object_type, - r.object_identity, r.address_names, r.address_args; + r.object_identity, r.schema_name, r.object_name, + r.address_names, r.address_args; END LOOP; END; $$; CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop @@ -436,18 +449,18 @@ CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig= .part_10_20 (id) FOR VALUES FROM (15) TO (20); NOTICE: END: command_tag=3DCREATE TABLE type=3Dtable identity=3Devttrig.= part_15_20 ALTER TABLE evttrig.two DROP COLUMN col_c; -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dtable column ident= ity=3Devttrig.two.col_c name=3D{evttrig,two,col_c} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable constraint i= dentity=3Dtwo_col_c_check on evttrig.two name=3D{evttrig,two,two_col_c_che= ck} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dtable column ident= ity=3Devttrig.two.col_c schema=3Devttrig name=3D addr=3D{evttrig,two= ,col_c} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable constraint i= dentity=3Dtwo_col_c_check on evttrig.two schema=3Devttrig name=3D ad= dr=3D{evttrig,two,two_col_c_check} args=3D{} NOTICE: END: command_tag=3DALTER TABLE type=3Dtable identity=3Devttrig.t= wo ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT; -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Ddefault value iden= tity=3Dfor evttrig.one.col_b name=3D{evttrig,one,col_b} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Ddefault value iden= tity=3Dfor evttrig.one.col_b schema=3Devttrig name=3D addr=3D{evttri= g,one,col_b} args=3D{} NOTICE: END: command_tag=3DALTER TABLE type=3Dtable identity=3Devttrig.o= ne ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey; -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dtable constraint i= dentity=3Done_pkey on evttrig.one name=3D{evttrig,one,one_pkey} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dtable constraint i= dentity=3Done_pkey on evttrig.one schema=3Devttrig name=3D addr=3D{e= vttrig,one,one_pkey} args=3D{} NOTICE: END: command_tag=3DALTER TABLE type=3Dtable identity=3Devttrig.o= ne ALTER TABLE evttrig.one DROP COLUMN col_c; -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dtable column ident= ity=3Devttrig.one.col_c name=3D{evttrig,one,col_c} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Ddefault value iden= tity=3Dfor evttrig.one.col_c name=3D{evttrig,one,col_c} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dtable column ident= ity=3Devttrig.one.col_c schema=3Devttrig name=3D addr=3D{evttrig,one= ,col_c} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Ddefault value iden= tity=3Dfor evttrig.one.col_c schema=3Devttrig name=3D addr=3D{evttri= g,one,col_c} args=3D{} NOTICE: END: command_tag=3DALTER TABLE type=3Dtable identity=3Devttrig.o= ne ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint; NOTICE: END: command_tag=3DALTER SEQUENCE type=3Dsequence identity=3Devt= trig.id_col_d_seq @@ -456,26 +469,26 @@ ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENT= ITY, ALTER COLUMN col_d SET DATA TYPE int; NOTICE: END: command_tag=3DALTER TABLE type=3Dtable identity=3Devttrig.i= d DROP INDEX evttrig.one_idx; -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dindex identity=3De= vttrig.one_idx name=3D{evttrig,one_idx} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dindex identity=3De= vttrig.one_idx schema=3Devttrig name=3Done_idx addr=3D{evttrig,one_idx} ar= gs=3D{} DROP SCHEMA evttrig CASCADE; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table evttrig.one drop cascades to table evttrig.two drop cascades to table evttrig.id drop cascades to table evttrig.parted -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dschema identity=3D= evttrig name=3D{evttrig} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.one name=3D{evttrig,one} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dsequence identity=3D= evttrig.one_col_a_seq name=3D{evttrig,one_col_a_seq} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Ddefault value iden= tity=3Dfor evttrig.one.col_a name=3D{evttrig,one,col_a} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.two name=3D{evttrig,two} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.id name=3D{evttrig,id} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.parted name=3D{evttrig,parted} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_1_10 name=3D{evttrig,part_1_10} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_10_20 name=3D{evttrig,part_10_20} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_10_15 name=3D{evttrig,part_10_15} args=3D{} -NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_15_20 name=3D{evttrig,part_15_20} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dschema identity=3D= evttrig schema=3D name=3Devttrig addr=3D{evttrig} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.one schema=3Devttrig name=3Done addr=3D{evttrig,one} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dsequence identity=3D= evttrig.one_col_a_seq schema=3Devttrig name=3Done_col_a_seq addr=3D{evttri= g,one_col_a_seq} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Ddefault value iden= tity=3Dfor evttrig.one.col_a schema=3Devttrig name=3D addr=3D{evttri= g,one,col_a} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.two schema=3Devttrig name=3Dtwo addr=3D{evttrig,two} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.id schema=3Devttrig name=3Did addr=3D{evttrig,id} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.parted schema=3Devttrig name=3Dparted addr=3D{evttrig,parted} args=3D= {} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_1_10 schema=3Devttrig name=3Dpart_1_10 addr=3D{evttrig,part_1_= 10} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_10_20 schema=3Devttrig name=3Dpart_10_20 addr=3D{evttrig,part_= 10_20} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_10_15 schema=3Devttrig name=3Dpart_10_15 addr=3D{evttrig,part_= 10_15} args=3D{} +NOTICE: NORMAL: orig=3Df normal=3Dt istemp=3Df type=3Dtable identity=3De= vttrig.part_15_20 schema=3Devttrig name=3Dpart_15_20 addr=3D{evttrig,part_= 15_20} args=3D{} DROP TABLE a_temp_tbl; -NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Dt type=3Dtable identity=3Dp= g_temp.a_temp_tbl name=3D{pg_temp,a_temp_tbl} args=3D{} +NOTICE: NORMAL: orig=3Dt normal=3Df istemp=3Dt type=3Dtable identity=3Dp= g_temp.a_temp_tbl schema=3Dpg_temp name=3Da_temp_tbl addr=3D{pg_temp,a_tem= p_tbl} args=3D{} -- check unfiltered results, too CREATE OR REPLACE FUNCTION event_trigger_report_dropped() RETURNS event_trigger @@ -485,34 +498,61 @@ DECLARE r record; BEGIN FOR r IN SELECT * from pg_event_trigger_dropped_objects() LOOP - RAISE NOTICE 'DROP: orig=3D% normal=3D% istemp=3D% type=3D% identity=3D= % name=3D% args=3D%', + RAISE NOTICE 'DROP: orig=3D% normal=3D% istemp=3D% type=3D% identity=3D= % schema=3D% name=3D% addr=3D% args=3D%', r.original, r.normal, r.is_temporary, r.object_type, - r.object_identity, r.address_names, r.address_args; + r.object_identity, r.schema_name, r.object_name, + r.address_names, r.address_args; END LOOP; END; $$; NOTICE: END: command_tag=3DCREATE FUNCTION type=3Dfunction identity=3Dpu= blic.event_trigger_report_dropped() +CREATE FUNCTION event_trigger_dummy_trigger() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +BEGIN + RETURN new; +END; $$; +NOTICE: END: command_tag=3DCREATE FUNCTION type=3Dfunction identity=3Dpu= blic.event_trigger_dummy_trigger() CREATE TABLE evtrg_nontemp_table (f1 int primary key, f2 int default 42); NOTICE: END: command_tag=3DCREATE TABLE type=3Dtable identity=3Dpublic.e= vtrg_nontemp_table NOTICE: END: command_tag=3DCREATE INDEX type=3Dindex identity=3Dpublic.e= vtrg_nontemp_table_pkey +CREATE TRIGGER evtrg_nontemp_trig + BEFORE INSERT ON evtrg_nontemp_table + EXECUTE FUNCTION event_trigger_dummy_trigger(); +NOTICE: END: command_tag=3DCREATE TRIGGER type=3Dtrigger identity=3Devtr= g_nontemp_trig on public.evtrg_nontemp_table +CREATE POLICY evtrg_nontemp_pol ON evtrg_nontemp_table USING (f2 > 0); +NOTICE: END: command_tag=3DCREATE POLICY type=3Dpolicy identity=3Devtrg_= nontemp_pol on public.evtrg_nontemp_table DROP TABLE evtrg_nontemp_table; -NOTICE: DROP: orig=3Dt normal=3Df istemp=3Df type=3Dtable identity=3Dpub= lic.evtrg_nontemp_table name=3D{public,evtrg_nontemp_table} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtype identity=3Dpubl= ic.evtrg_nontemp_table name=3D{public.evtrg_nontemp_table} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtype identity=3Dpubl= ic.evtrg_nontemp_table[] name=3D{public.evtrg_nontemp_table[]} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Ddefault value identi= ty=3Dfor public.evtrg_nontemp_table.f2 name=3D{public,evtrg_nontemp_table,= f2} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtable constraint ide= ntity=3Devtrg_nontemp_table_f1_not_null on public.evtrg_nontemp_table name= =3D{public,evtrg_nontemp_table,evtrg_nontemp_table_f1_not_null} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtable constraint ide= ntity=3Devtrg_nontemp_table_pkey on public.evtrg_nontemp_table name=3D{pub= lic,evtrg_nontemp_table,evtrg_nontemp_table_pkey} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dindex identity=3Dpub= lic.evtrg_nontemp_table_pkey name=3D{public,evtrg_nontemp_table_pkey} args= =3D{} +NOTICE: DROP: orig=3Dt normal=3Df istemp=3Df type=3Dtable identity=3Dpub= lic.evtrg_nontemp_table schema=3Dpublic name=3Devtrg_nontemp_table addr=3D= {public,evtrg_nontemp_table} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtype identity=3Dpubl= ic.evtrg_nontemp_table schema=3Dpublic name=3Devtrg_nontemp_table addr=3D{= public.evtrg_nontemp_table} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtype identity=3Dpubl= ic.evtrg_nontemp_table[] schema=3Dpublic name=3D_evtrg_nontemp_table addr=3D= {public.evtrg_nontemp_table[]} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Ddefault value identi= ty=3Dfor public.evtrg_nontemp_table.f2 schema=3Dpublic name=3D addr=3D= {public,evtrg_nontemp_table,f2} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtable constraint ide= ntity=3Devtrg_nontemp_table_f1_not_null on public.evtrg_nontemp_table sche= ma=3Dpublic name=3D addr=3D{public,evtrg_nontemp_table,evtrg_nontemp= _table_f1_not_null} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtable constraint ide= ntity=3Devtrg_nontemp_table_pkey on public.evtrg_nontemp_table schema=3Dpu= blic name=3D addr=3D{public,evtrg_nontemp_table,evtrg_nontemp_table_= pkey} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dindex identity=3Dpub= lic.evtrg_nontemp_table_pkey schema=3Dpublic name=3Devtrg_nontemp_table_pk= ey addr=3D{public,evtrg_nontemp_table_pkey} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Df type=3Dtrigger identity=3De= vtrg_nontemp_trig on public.evtrg_nontemp_table schema=3Dpublic name=3D addr=3D{public,evtrg_nontemp_table,evtrg_nontemp_trig} args=3D{} +NOTICE: DROP: orig=3Df normal=3Dt istemp=3Df type=3Dpolicy identity=3Dev= trg_nontemp_pol on public.evtrg_nontemp_table schema=3Dpublic name=3D addr=3D{public,evtrg_nontemp_table,evtrg_nontemp_pol} args=3D{} CREATE TEMP TABLE a_temp_tbl (f1 int primary key, f2 int default 42); NOTICE: END: command_tag=3DCREATE TABLE type=3Dtable identity=3Dpg_temp.= a_temp_tbl NOTICE: END: command_tag=3DCREATE INDEX type=3Dindex identity=3Dpg_temp.= a_temp_tbl_pkey +CREATE TRIGGER a_temp_trig + BEFORE INSERT ON a_temp_tbl + EXECUTE FUNCTION event_trigger_dummy_trigger(); +NOTICE: END: command_tag=3DCREATE TRIGGER type=3Dtrigger identity=3Da_te= mp_trig on pg_temp.a_temp_tbl +CREATE POLICY a_temp_pol ON a_temp_tbl USING (f2 > 0); +NOTICE: END: command_tag=3DCREATE POLICY type=3Dpolicy identity=3Da_temp= _pol on pg_temp.a_temp_tbl DROP TABLE a_temp_tbl; -NOTICE: DROP: orig=3Dt normal=3Df istemp=3Dt type=3Dtable identity=3Dpg_= temp.a_temp_tbl name=3D{pg_temp,a_temp_tbl} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtype identity=3Dpg_t= emp.a_temp_tbl name=3D{pg_temp.a_temp_tbl} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtype identity=3Dpg_t= emp.a_temp_tbl[] name=3D{pg_temp.a_temp_tbl[]} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Ddefault value identi= ty=3Dfor pg_temp.a_temp_tbl.f2 name=3D{pg_temp,a_temp_tbl,f2} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtable constraint ide= ntity=3Da_temp_tbl_f1_not_null on pg_temp.a_temp_tbl name=3D{pg_temp,a_tem= p_tbl,a_temp_tbl_f1_not_null} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtable constraint ide= ntity=3Da_temp_tbl_pkey on pg_temp.a_temp_tbl name=3D{pg_temp,a_temp_tbl,a= _temp_tbl_pkey} args=3D{} -NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dindex identity=3Dpg_= temp.a_temp_tbl_pkey name=3D{pg_temp,a_temp_tbl_pkey} args=3D{} +NOTICE: DROP: orig=3Dt normal=3Df istemp=3Dt type=3Dtable identity=3Dpg_= temp.a_temp_tbl schema=3Dpg_temp name=3Da_temp_tbl addr=3D{pg_temp,a_temp_= tbl} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtype identity=3Dpg_t= emp.a_temp_tbl schema=3Dpg_temp name=3Da_temp_tbl addr=3D{pg_temp.a_temp_t= bl} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtype identity=3Dpg_t= emp.a_temp_tbl[] schema=3Dpg_temp name=3D_a_temp_tbl addr=3D{pg_temp.a_tem= p_tbl[]} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Ddefault value identi= ty=3Dfor pg_temp.a_temp_tbl.f2 schema=3Dpg_temp name=3D addr=3D{pg_t= emp,a_temp_tbl,f2} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtable constraint ide= ntity=3Da_temp_tbl_f1_not_null on pg_temp.a_temp_tbl schema=3Dpg_temp name= =3D addr=3D{pg_temp,a_temp_tbl,a_temp_tbl_f1_not_null} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtable constraint ide= ntity=3Da_temp_tbl_pkey on pg_temp.a_temp_tbl schema=3Dpg_temp name=3D addr=3D{pg_temp,a_temp_tbl,a_temp_tbl_pkey} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dindex identity=3Dpg_= temp.a_temp_tbl_pkey schema=3Dpg_temp name=3Da_temp_tbl_pkey addr=3D{pg_te= mp,a_temp_tbl_pkey} args=3D{} +NOTICE: DROP: orig=3Df normal=3Df istemp=3Dt type=3Dtrigger identity=3Da= _temp_trig on pg_temp.a_temp_tbl schema=3Dpg_temp name=3D addr=3D{pg= _temp,a_temp_tbl,a_temp_trig} args=3D{} +NOTICE: DROP: orig=3Df normal=3Dt istemp=3Dt type=3Dpolicy identity=3Da_= temp_pol on pg_temp.a_temp_tbl schema=3Dpg_temp name=3D addr=3D{pg_t= emp,a_temp_tbl,a_temp_pol} args=3D{} +DROP FUNCTION event_trigger_dummy_trigger(); +NOTICE: DROP: orig=3Dt normal=3Df istemp=3Df type=3Dfunction identity=3D= public.event_trigger_dummy_trigger() schema=3Dpublic name=3D addr=3D= {public,event_trigger_dummy_trigger} args=3D{} -- CREATE OPERATOR CLASS without FAMILY clause should report -- both CREATE OPERATOR FAMILY and CREATE OPERATOR CLASS CREATE OPERATOR CLASS evttrigopclass FOR TYPE int USING btree AS STORAGE = int; diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql= /event_trigger.sql index ef5978b9697..c613c0cfd43 100644 --- a/src/test/regress/sql/event_trigger.sql +++ b/src/test/regress/sql/event_trigger.sql @@ -202,9 +202,15 @@ INSERT INTO undroppable_objs VALUES ('table', 'audit_tbls.schema_two_table_three'); = CREATE TABLE dropped_objects ( - type text, - schema text, - object text + object_type text, + schema_name text, + object_name text, + object_identity text, + address_names text[], + address_args text[], + is_temporary bool, + original bool, + normal bool ); = -- This tests errors raised within event triggers; the one in audit_tbls @@ -245,8 +251,12 @@ BEGIN END IF; = INSERT INTO dropped_objects - (type, schema, object) VALUES - (obj.object_type, obj.schema_name, obj.object_identity); + (object_type, schema_name, object_name, + object_identity, address_names, address_args, + is_temporary, original, normal) VALUES + (obj.object_type, obj.schema_name, obj.object_name, + obj.object_identity, obj.address_names, obj.address_args, + obj.is_temporary, obj.original, obj.normal); END LOOP; END $$; @@ -263,10 +273,12 @@ DROP SCHEMA schema_one, schema_two CASCADE; DELETE FROM undroppable_objs WHERE object_identity =3D 'schema_one.table_= three'; DROP SCHEMA schema_one, schema_two CASCADE; = -SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast= '; +-- exclude TOAST objects because they have unstable names +SELECT * FROM dropped_objects + WHERE schema_name IS NULL OR schema_name <> 'pg_toast'; = DROP OWNED BY regress_evt_user; -SELECT * FROM dropped_objects WHERE type =3D 'schema'; +SELECT * FROM dropped_objects WHERE object_type =3D 'schema'; = DROP ROLE regress_evt_user; = @@ -285,9 +297,10 @@ BEGIN IF NOT r.normal AND NOT r.original THEN CONTINUE; END IF; - RAISE NOTICE 'NORMAL: orig=3D% normal=3D% istemp=3D% type=3D% identit= y=3D% name=3D% args=3D%', + RAISE NOTICE 'NORMAL: orig=3D% normal=3D% istemp=3D% type=3D% identit= y=3D% schema=3D% name=3D% addr=3D% args=3D%', r.original, r.normal, r.is_temporary, r.object_type, - r.object_identity, r.address_names, r.address_args; + r.object_identity, r.schema_name, r.object_name, + r.address_names, r.address_args; END LOOP; END; $$; CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop @@ -346,17 +359,37 @@ DECLARE r record; BEGIN FOR r IN SELECT * from pg_event_trigger_dropped_objects() LOOP - RAISE NOTICE 'DROP: orig=3D% normal=3D% istemp=3D% type=3D% identity=3D= % name=3D% args=3D%', + RAISE NOTICE 'DROP: orig=3D% normal=3D% istemp=3D% type=3D% identity=3D= % schema=3D% name=3D% addr=3D% args=3D%', r.original, r.normal, r.is_temporary, r.object_type, - r.object_identity, r.address_names, r.address_args; + r.object_identity, r.schema_name, r.object_name, + r.address_names, r.address_args; END LOOP; END; $$; = +CREATE FUNCTION event_trigger_dummy_trigger() + RETURNS trigger + LANGUAGE plpgsql +AS $$ +BEGIN + RETURN new; +END; $$; + CREATE TABLE evtrg_nontemp_table (f1 int primary key, f2 int default 42); +CREATE TRIGGER evtrg_nontemp_trig + BEFORE INSERT ON evtrg_nontemp_table + EXECUTE FUNCTION event_trigger_dummy_trigger(); +CREATE POLICY evtrg_nontemp_pol ON evtrg_nontemp_table USING (f2 > 0); DROP TABLE evtrg_nontemp_table; + CREATE TEMP TABLE a_temp_tbl (f1 int primary key, f2 int default 42); +CREATE TRIGGER a_temp_trig + BEFORE INSERT ON a_temp_tbl + EXECUTE FUNCTION event_trigger_dummy_trigger(); +CREATE POLICY a_temp_pol ON a_temp_tbl USING (f2 > 0); DROP TABLE a_temp_tbl; = +DROP FUNCTION event_trigger_dummy_trigger(); + -- CREATE OPERATOR CLASS without FAMILY clause should report -- both CREATE OPERATOR FAMILY and CREATE OPERATOR CLASS CREATE OPERATOR CLASS evttrigopclass FOR TYPE int USING btree AS STORAGE = int; ------- =_aaaaaaaaaa0--