TRIGGER TRUNCATE -- CASCADE or RESTRICT

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

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

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

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
<mailto: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
<mailto: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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general