Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit473ab40

Browse files
committed
Add sql_drop event for event triggers
This event takes place just before ddl_command_end, and is fired if andonly if at least one object has been dropped by the command. (Forinstance, DROP TABLE IF EXISTS of a table that does not in fact existwill not lead to such a trigger firing). Commands that drop multipleobjects (such as DROP SCHEMA or DROP OWNED BY) will cause a single eventto fire. Some firings might be surprising, such asALTER TABLE DROP COLUMN.The trigger is fired after the drop has taken place, because that hasbeen deemed the safest design, to avoid exposing possibly-inconsistentinternal state (system catalogs as well as current transaction) to theuser function code. This means that careful tracking of objectidentification is required during the object removal phase.Like other currently existing events, there is support for tagfiltering.To support the new event, add a new pg_event_trigger_dropped_objects()set-returning function, which returns a set of rows comprising theobjects affected by the command. This is to be used within the userfunction code, and is mostly modelled after the recently introducedpg_identify_object() function.Catalog version bumped due to the new function.Dimitri Fontaine and Álvaro HerreraReview by Robert Haas, Tom Lane
1 parent593c39d commit473ab40

File tree

15 files changed

+1083
-115
lines changed

15 files changed

+1083
-115
lines changed

‎doc/src/sgml/event-trigger.sgml

Lines changed: 105 additions & 5 deletions
Large diffs are not rendered by default.

‎doc/src/sgml/func.sgml

Lines changed: 113 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15980,4 +15980,117 @@ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
1598015980
<xref linkend="SQL-CREATETRIGGER">.
1598115981
</para>
1598215982
</sect1>
15983+
15984+
<sect1 id="functions-event-triggers">
15985+
<title>Event Trigger Functions</title>
15986+
15987+
<indexterm>
15988+
<primary>pg_event_trigger_dropped_objects</primary>
15989+
</indexterm>
15990+
15991+
<para>
15992+
Currently <productname>PostgreSQL</> provides one built-in event trigger
15993+
helper function, <function>pg_event_trigger_dropped_objects</>.
15994+
</para>
15995+
15996+
<para>
15997+
<function>pg_event_trigger_dropped_objects</> returns a list of all object
15998+
dropped by the command in whose <literal>sql_drop</> event it is called.
15999+
If called in any other context,
16000+
<function>pg_event_trigger_dropped_objects</> raises an error.
16001+
<function>pg_event_trigger_dropped_objects</> returns the following columns:
16002+
16003+
<informaltable>
16004+
<tgroup cols="3">
16005+
<thead>
16006+
<row>
16007+
<entry>Name</entry>
16008+
<entry>Type</entry>
16009+
<entry>Description</entry>
16010+
</row>
16011+
</thead>
16012+
16013+
<tbody>
16014+
<row>
16015+
<entry><literal>classid</literal></entry>
16016+
<entry><type>Oid</type></entry>
16017+
<entry>OID of catalog the object belonged in</entry>
16018+
</row>
16019+
<row>
16020+
<entry><literal>objid</literal></entry>
16021+
<entry><type>Oid</type></entry>
16022+
<entry>OID the object had within the catalog</entry>
16023+
</row>
16024+
<row>
16025+
<entry><literal>objsubid</literal></entry>
16026+
<entry><type>int32</type></entry>
16027+
<entry>Object sub-id (e.g. attribute number for columns)</entry>
16028+
</row>
16029+
<row>
16030+
<entry><literal>object_type</literal></entry>
16031+
<entry><type>text</type></entry>
16032+
<entry>Type of the object</entry>
16033+
</row>
16034+
<row>
16035+
<entry><literal>schema_name</literal></entry>
16036+
<entry><type>text</type></entry>
16037+
<entry>
16038+
Name of the schema the object belonged in, if any; otherwise <literal>NULL</>.
16039+
No quoting is applied.
16040+
</entry>
16041+
</row>
16042+
<row>
16043+
<entry><literal>object_name</literal></entry>
16044+
<entry><type>text</type></entry>
16045+
<entry>
16046+
Name of the object, if the combination of schema and name can be
16047+
used as an unique identifier for the object; otherwise <literal>NULL</>.
16048+
No quoting is applied, and name is never schema-qualified.
16049+
</entry>
16050+
</row>
16051+
<row>
16052+
<entry><literal>object_identity</literal></entry>
16053+
<entry><type>text</type></entry>
16054+
<entry>
16055+
Text rendering of the object identity, schema-qualified. Each and every
16056+
identifier present in the identity is quoted if necessary.
16057+
</entry>
16058+
</row>
16059+
</tbody>
16060+
</tgroup>
16061+
</informaltable>
16062+
</para>
16063+
16064+
<para>
16065+
The <function>pg_event_trigger_dropped_objects</> function can be used
16066+
in an event trigger like this:
16067+
<programlisting>
16068+
CREATE FUNCTION test_event_trigger_for_drops()
16069+
RETURNS event_trigger LANGUAGE plpgsql AS $$
16070+
DECLARE
16071+
obj record;
16072+
BEGIN
16073+
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
16074+
LOOP
16075+
RAISE NOTICE '% dropped object: % %.% %',
16076+
tg_tag,
16077+
obj.object_type,
16078+
obj.schema_name,
16079+
obj.object_name,
16080+
obj.object_identity;
16081+
END LOOP;
16082+
END
16083+
$$;
16084+
CREATE EVENT TRIGGER test_event_trigger_for_drops
16085+
ON sql_drop
16086+
EXECUTE PROCEDURE test_event_trigger_for_drops();
16087+
</programlisting>
16088+
</para>
16089+
16090+
<para>
16091+
For more information about event triggers,
16092+
see <xref linkend="event-triggers">.
16093+
</para>
16094+
</sect1>
16095+
1598316096
</chapter>

‎src/backend/catalog/dependency.c

Lines changed: 46 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -190,6 +190,44 @@ static bool stack_address_present_add_flags(const ObjectAddress *object,
190190
ObjectAddressStack*stack);
191191

192192

193+
/*
194+
* Go through the objects given running the final actions on them, and execute
195+
* the actual deletion.
196+
*/
197+
staticvoid
198+
deleteObjectsInList(ObjectAddresses*targetObjects,Relation*depRel,
199+
intflags)
200+
{
201+
inti;
202+
203+
/*
204+
* Keep track of objects for event triggers, if necessary.
205+
*/
206+
if (trackDroppedObjectsNeeded())
207+
{
208+
for (i=0;i<targetObjects->numrefs;i++)
209+
{
210+
ObjectAddress*thisobj=targetObjects->refs+i;
211+
212+
if ((!(flags&PERFORM_DELETION_INTERNAL))&&
213+
EventTriggerSupportsObjectType(getObjectClass(thisobj)))
214+
{
215+
EventTriggerSQLDropAddObject(thisobj);
216+
}
217+
}
218+
}
219+
220+
/*
221+
* Delete all the objects in the proper order.
222+
*/
223+
for (i=0;i<targetObjects->numrefs;i++)
224+
{
225+
ObjectAddress*thisobj=targetObjects->refs+i;
226+
227+
deleteOneObject(thisobj,depRel,flags);
228+
}
229+
}
230+
193231
/*
194232
* performDeletion: attempt to drop the specified object. If CASCADE
195233
* behavior is specified, also drop any dependent objects (recursively).
@@ -215,7 +253,6 @@ performDeletion(const ObjectAddress *object,
215253
{
216254
RelationdepRel;
217255
ObjectAddresses*targetObjects;
218-
inti;
219256

220257
/*
221258
* We save some cycles by opening pg_depend just once and passing the
@@ -250,15 +287,8 @@ performDeletion(const ObjectAddress *object,
250287
NOTICE,
251288
object);
252289

253-
/*
254-
* Delete all the objects in the proper order.
255-
*/
256-
for (i=0;i<targetObjects->numrefs;i++)
257-
{
258-
ObjectAddress*thisobj=targetObjects->refs+i;
259-
260-
deleteOneObject(thisobj,&depRel,flags);
261-
}
290+
/* do the deed */
291+
deleteObjectsInList(targetObjects,&depRel,flags);
262292

263293
/* And clean up */
264294
free_object_addresses(targetObjects);
@@ -332,15 +362,8 @@ performMultipleDeletions(const ObjectAddresses *objects,
332362
NOTICE,
333363
(objects->numrefs==1 ?objects->refs :NULL));
334364

335-
/*
336-
* Delete all the objects in the proper order.
337-
*/
338-
for (i=0;i<targetObjects->numrefs;i++)
339-
{
340-
ObjectAddress*thisobj=targetObjects->refs+i;
341-
342-
deleteOneObject(thisobj,&depRel,flags);
343-
}
365+
/* do the deed */
366+
deleteObjectsInList(targetObjects,&depRel,flags);
344367

345368
/* And clean up */
346369
free_object_addresses(targetObjects);
@@ -356,6 +379,10 @@ performMultipleDeletions(const ObjectAddresses *objects,
356379
* This is currently used only to clean out the contents of a schema
357380
* (namespace): the passed object is a namespace. We normally want this
358381
* to be done silently, so there's an option to suppress NOTICE messages.
382+
*
383+
* Note we don't fire object drop event triggers here; it would be wrong to do
384+
* so for the current only use of this function, but if more callers are added
385+
* this might need to be reconsidered.
359386
*/
360387
void
361388
deleteWhatDependsOn(constObjectAddress*object,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp