Обсуждение: TRIGGER TRUNCATE -- CASCADE or RESTRICT

Salvete!

I'm in a handle for a trigger for TRUNCATE. Is it possible to find out
whether the TRUNCATE TABLE ist called with CASCADE?

regads andreas

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

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.albe@wien.gv.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@postgresql.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.

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.ulbrich@matheversum.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.albe@wien.gv.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@postgresql.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.

On 03/06/15 08:40, Andreas Ulbrich wrote:
> 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!
Curious, I tried to investigate, to get a better understanding and ran
into a problem...

    $ psql
    psql (9.4.1)
    Type "help" for help.

    gavin=> CREATE TABLE A
    gavin-> (
    gavin(> p_col serial PRIMARY KEY,
    gavin(> acol  integer
    gavin(> );
    CREATE TABLE
    gavin=> CREATE TABLE B() INHERITS (A);
    CREATE TABLE
    gavin=> INSERT INTO A(acol) VALUES (1);
    ERROR:  column "acol" of relation "a" does not exist
    LINE 1: INSERT INTO A(acol) VALUES (1);
    ^
    gavin=> \d+ a
    Table "public.a"
      Column | Type   |
    Modifiers                     | Storage | Stats target | Description
    --------+---------+---------------------------------------------------+---------+--------------+-------------
      p_col  | integer | not null default
    nextval('a_p_col_seq'::regclass) | plain   |              |
      acol   | integer
    |                                                   | plain
    |              |
    Indexes:
         "a_pkey" PRIMARY KEY, btree (p_col)
    Child tables: b

    gavin=> \d b
    Table "public.b"
      Column | Type   |                     Modifiers
    --------+---------+---------------------------------------------------
      p_col  | integer | not null default nextval('a_p_col_seq'::regclass)
      acol   | integer |
    Inherits: a

    gavin=>




[...]


Cheers,
Gavin