Re: TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 02.06.2015 16:20, Melvin Davidson wrote:
> You can use the following to list the triggers and see what functions
> they call. Then you can check pg_proc to see how TRUNCATE is used in
> prosrc.
>
>
> SELECT c.relname,
> t.tgname,
> p.proname AS function_called,
> t.tgconstraint AS is_constraint,
> CASE WHEN t.tgconstrrelid > 0
> THEN (SELECT relname
> FROM pg_class
> WHERE oid = t.tgconstrrelid)
> ELSE ''
> END AS constr_tbl,
> t.tgenabled
> FROM pg_trigger t
> INNER JOIN pg_proc p ON ( p.oid = t.tgfoid)
> INNER JOIN pg_class c ON (c.oid = t.tgrelid)
> WHERE tgname NOT LIKE 'pg_%'
> AND tgname NOT LIKE 'RI_%' -- < comment out to see constraints
> -- AND t.tgenabled = FALSE
> ORDER BY 1;
>
>
> On Tue, Jun 2, 2015 at 5:31 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at
> <mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at>> wrote:
>
> Andreas Ulbrich wrote:
> > I'm in a handle for a trigger for TRUNCATE. Is it possible to
> find out
> > whether the TRUNCATE TABLE ist called with CASCADE?
>
> I don't think there is.
>
> But you can find out the table where the trigger is defined and
> examine
> if any foreign key constraints are referring to it.
>
> If yes, then the trigger was called with CASCADE.
> If no, it might have been called either way, but the effect would
> be the same.
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
I think, I must explain the problem deeper:

I have two (or more) tables
CREATE TABLE a (id ... UNIQUE -- maby the PRIMARY KEY,...);
CREATE TABLE b (...) INHERIT (a);

But the id has to be unique over the inheritance. So one solution of the
problem is:
CREATE key_table (id ... UNIQUE, table REGCLASS);
By trigger every INSERT/UPDATE/DELETE in Table a,b,... changes the
key_table.
This works.

Now I have a table reference to the id of table a*. This is not
possible, but reference to key_table(id) works fine.
CREATE TABLE r (..., a_id /* REFERENCES a*(id) */ REFERENCES
key_tabel(id),..);

And now the problem: Can I support TRUNCATE TABLE?
DELETE is not a problem: for DELETE FROM a the trigger deletes the entry
in the key_table and if the reference action on delete is CASCADE, the
entries in r will be deletet.

But TRUNCATE TABLE a! In a TRUNCATE TRIGGER I can delete the entries in
the key_table "WHERE table = a" (O.K. the performance) -- it is actual
not a TRUNCATE TABLE but a TRUNCATE PARTITION.
And if I not specified ONLY, there is also a TRUNCATE TABLE b and the
trigger ist fired too.

But what is with table r? If I do the delete in the key_table, the
delete action will be used. But there is not a truncate action, cascaded
truncation is controlled by execute TRUNCATE. And so, I must delete the
entries in r if there is a CASCADE in the TRUNCATE or raise an exception
if the TRUNCATE is RESTRICTED.

Now the Question? How to find out in the trigger function for truncate
whether is there a CASCADE or not.

regards, Andreas
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.