Here is an example of the caching problem I described yesterday in a
post. I have the following tables:
db=> \d bmetcalf.foo1;
Table "bmetcalf.foo1"
Column | Type | Modifiers
-----------+------+-----------
country | text |
replicaID | text |
host | text |
replica | text |
Triggers:
foo_trig BEFORE DELETE ON foo1 FOR EACH ROW EXECUTE PROCEDURE foo_func()
db=> \d bmetcalf.foo2;
Table "bmetcalf.foo2"
Column | Type | Modifiers
-----------+------+-----------
vobID | text |
replicaID | text |
Triggers:
foo_trig BEFORE DELETE ON foo2 FOR EACH ROW EXECUTE PROCEDURE foo_func()
And here is the SQL for the function and trigger definitions:
CREATE OR REPLACE FUNCTION bmetcalf.foo_func()
RETURNS TRIGGER
LANGUAGE plperlu
AS $$
require 5.8.0;
my $table = $_TD->{relname};
warn "table name is $table";
warn "BWM before call: table name is $table";
do_delete();
return 'SKIP';
sub do_delete {
warn "BWM in call: table name is $table";
}
$$;
DROP TRIGGER foo_trig ON bmetcalf.foo1;
DROP TRIGGER foo_trig ON bmetcalf.foo2;
CREATE TRIGGER foo_trig BEFORE DELETE ON bmetcalf.foo1 FOR EACH ROW
EXECUTE PROCEDURE bmetcalf.foo_func();
CREATE TRIGGER foo_trig BEFORE DELETE ON bmetcalf.foo2 FOR EACH ROW
EXECUTE PROCEDURE bmetcalf.foo_func();
Now, watch what happens when I execute a DELETE on bmetcalf.foo1 after
a DELETE on bmetcalf.foo2 . The table name when this trigger fires
for bmetcalf.foo1 changes:
db=# delete from bmetcalf.foo1 where "replicaID" = '11';
...
db=# delete from bmetcalf.foo1 where "replicaID" = '11';
NOTICE: table name is foo1 at line 6.
NOTICE: BWM before call: table name is foo1 at line 8.
NOTICE: BWM in call: table name is foo2 at line 15.
DELETE 0
If I close my session and reconnect and only run the DELETE against
bmetcalf.foo1, the problem doesn't appear:
db=# delete from bmetcalf.foo1 where "replicaID" = '11';
NOTICE: table name is foo1 at line 6.
NOTICE: BWM before call: table name is foo1 at line 8.
NOTICE: BWM in call: table name is foo1 at line 15.
DELETE 0
Any ideas?
--
Brandon