Re: TRIGGER TRUNCATE -- CASCADE or RESTRICT

Your problem is in your design.

If you do it like this:

CREATE TABLE A
(
p_col serial PRIMARY KEY,
acol integer
);

CREATE TABLE B() INHERITS (A);

INSERT INTO A(acol) VALUES (1);
INSERT INTO B(acol) VALUES (2);

SELECT * FROM A;
SELECT * FROM B;

Then the sequence (p_col) will be UNIQUE across all tables and can be
referenced.
No need for a key table.

On Tue, Jun 2, 2015 at 3:45 PM, Andreas Ulbrich <
andreas(dot)ulbrich(at)matheversum(dot)de> wrote:

> 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>
> 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)
>> 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.
>
>
>

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