Re: TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 02.06.2015 22:12, Melvin Davidson wrote:
> 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.
No, someone can do:
INSERT INTO A VALUES (2,3);
TABLE A;
shows:
p_col | acol
-------+------
1 | 1
2 | 2
2 | 3
p_col is not unique!

>
> On Tue, Jun 2, 2015 at 3:45 PM, Andreas Ulbrich
> <andreas(dot)ulbrich(at)matheversum(dot)de
> <mailto: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 <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.
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.