example of really weird caching (or whatever) problem

["Brandon Metcalf" <bmetcalf@xxxxxxxxxx>]

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

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