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.a...@wien.gv.at
<mailto:laurenz.a...@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
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
