Thread: example of really weird caching (or whatever) problem
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
Brandon Metcalf wrote:
> Here is an example of the caching problem I described yesterday in a
> post. I have the following tables:
> 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";
> }
>
> $$;
Umm - you've got a named closure inside your funciton here - "sub
do_delete".
It's warning isn't using the "my $table" variable, it's using a
localised copy of that variable. That gets defined when the sub is
defined, which will be on the first call (my $table=foo2) and still
exists, unchanged on the second call (my $table=foo1).
Warning - can't remember if I'm using the right terminology on the
above, although I think it's the right diagnosis.
--
Richard Huxton
Archonet Ltd
d == dev@archonet.com writes:
d> Brandon Metcalf wrote:
d> > Here is an example of the caching problem I described yesterday in a
d> > post. I have the following tables:
d> > And here is the SQL for the function and trigger definitions:
d> >
d> > CREATE OR REPLACE FUNCTION bmetcalf.foo_func()
d> > RETURNS TRIGGER
d> > LANGUAGE plperlu
d> > AS $$
d> > require 5.8.0;
d> >
d> > my $table = $_TD->{relname};
d> > warn "table name is $table";
d> > warn "BWM before call: table name is $table";
d> >
d> > do_delete();
d> >
d> > return 'SKIP';
d> >
d> > sub do_delete {
d> > warn "BWM in call: table name is $table";
d> > }
d> >
d> > $$;
d> Umm - you've got a named closure inside your funciton here - "sub
d> do_delete".
d> It's warning isn't using the "my $table" variable, it's using a
d> localised copy of that variable. That gets defined when the sub is
d> defined, which will be on the first call (my $table=foo2) and still
d> exists, unchanged on the second call (my $table=foo1).
Yep, it seems that's the problem. If I pass in $table and use a
lexical variable defined inside do_delete(), the problem goes away.
So, this is where my understanding of how triggers work lacks. For a
given session, each execution of a trigger isn't completely
independent?
--
Brandon
Brandon Metcalf wrote:
> Yep, it seems that's the problem. If I pass in $table and use a
> lexical variable defined inside do_delete(), the problem goes away.
> So, this is where my understanding of how triggers work lacks. For a
> given session, each execution of a trigger isn't completely
> independent?
Nothing to do with triggers - it's all to do with your Perl code.
#!/usr/bin/perl
sub foo {
my $x = shift;
print "foo x = $x\n";
bar();
return;
sub bar {
print "bar x = $x\n";
}
}
foo(1);
foo(2);
exit;
$ ./perl_example.pl
foo x = 1
bar x = 1
foo x = 2
bar x = 1
If you use warnings it'll tell you about it too with this example.
--
Richard Huxton
Archonet Ltd
d == dev@archonet.com writes:
d> Brandon Metcalf wrote:
d> > Yep, it seems that's the problem. If I pass in $table and use a
d> > lexical variable defined inside do_delete(), the problem goes away.
d> > So, this is where my understanding of how triggers work lacks. For a
d> > given session, each execution of a trigger isn't completely
d> > independent?
d> Nothing to do with triggers - it's all to do with your Perl code.
I respectfully disagree because if I don't execute a DELETE on foo2 as
shown in my original email, the problem doesn't occur. Somewhere in
the trigger execution it's remembering the first table on which the
trigger fired. So, the information about foo2 is coming from
somewhere and it's in the Perl code. In other words, I performing two
different DELETEs which cause two different invocations of the same
trigger.
d> #!/usr/bin/perl
d> sub foo {
d> my $x = shift;
d> print "foo x = $x\n";
d> bar();
d> return;
d> sub bar {
d> print "bar x = $x\n";
d> }
d> }
d> foo(1);
d> foo(2);
d> exit;
d> $ ./perl_example.pl
d> foo x = 1
d> bar x = 1
d> foo x = 2
d> bar x = 1
d> If you use warnings it'll tell you about it too with this example.
--
Brandon
b == bmetcalf@cash.us.nortel.com writes:
b> d == dev@archonet.com writes:
b> d> Brandon Metcalf wrote:
b> d> > Yep, it seems that's the problem. If I pass in $table and use a
b> d> > lexical variable defined inside do_delete(), the problem goes away.
b> d> > So, this is where my understanding of how triggers work lacks. For a
b> d> > given session, each execution of a trigger isn't completely
b> d> > independent?
b> d> Nothing to do with triggers - it's all to do with your Perl code.
b> I respectfully disagree because if I don't execute a DELETE on foo2 as
b> shown in my original email, the problem doesn't occur. Somewhere in
b> the trigger execution it's remembering the first table on which the
b> trigger fired. So, the information about foo2 is coming from
b> somewhere and it's in the Perl code. In other words, I performing two
That should read it's _not_ in the Perl code.
b> different DELETEs which cause two different invocations of the same
b> trigger.
b> d> #!/usr/bin/perl
b> d> sub foo {
b> d> my $x = shift;
b> d> print "foo x = $x\n";
b> d> bar();
b> d> return;
b> d> sub bar {
b> d> print "bar x = $x\n";
b> d> }
b> d> }
b> d> foo(1);
b> d> foo(2);
b> d> exit;
b> d> $ ./perl_example.pl
b> d> foo x = 1
b> d> bar x = 1
b> d> foo x = 2
b> d> bar x = 1
b> d> If you use warnings it'll tell you about it too with this example.
--
Brandon
Brandon Metcalf wrote:
> d == dev@archonet.com writes:
>
> d> Brandon Metcalf wrote:
> d> > Yep, it seems that's the problem. If I pass in $table and use a
> d> > lexical variable defined inside do_delete(), the problem goes away.
> d> > So, this is where my understanding of how triggers work lacks. For a
> d> > given session, each execution of a trigger isn't completely
> d> > independent?
>
> d> Nothing to do with triggers - it's all to do with your Perl code.
>
>
> I respectfully disagree because if I don't execute a DELETE on foo2 as
> shown in my original email, the problem doesn't occur.
Of course not.
> Somewhere in
> the trigger execution it's remembering the first table on which the
> trigger fired.
Yes. in your "sub do_delete" with it's local variable.
> So, the information about foo2 is coming from
> somewhere and it's in the Perl code.
Yes, your local copy of $table in do_delete.
> In other words, I performing two
> different DELETEs which cause two different invocations of the same
> trigger.
You've written your code such that do_delete has a local copy of $table.
In fact, the way it actually works iirc is that when you exit the
trigger function "my $table" goes out of scope and vanishes, but the
"$table" in do_delete doesn't vanish and persists from call to call. You
might call this a static variable in C terms.
> d> #!/usr/bin/perl
>
> d> sub foo {
> d> my $x = shift;
> d> print "foo x = $x\n";
> d> bar();
> d> return;
>
> d> sub bar {
> d> print "bar x = $x\n";
> d> }
> d> }
>
> d> foo(1);
> d> foo(2);
> d> exit;
This code mirrors _exactly_ what is happening with your trigger. On the
first call to foo $x is set to 1, on the second it's set to 2. That
doesn't affect "sub bar" though because its copy of $x is still the one
from the first call.
Maybe the following makes it clearer:
#!/usr/bin/perl
sub foo {
my $x = shift;
print "foo x = $x, ";
bar();
return;
sub bar {
print "bar x = $x\n";
$x--;
}
}
for my $i (1..5) { foo($i); }
exit;
$ ./perl_example.pl
foo x = 1, bar x = 1
foo x = 2, bar x = 0
foo x = 3, bar x = -1
foo x = 4, bar x = -2
foo x = 5, bar x = -3
The two $x variables go their separate ways and the one in "bar" doesn't
go out of scope at the end of the function.
--
Richard Huxton
Archonet Ltd
d == dev@archonet.com writes:
d> Brandon Metcalf wrote:
d> > d == dev@archonet.com writes:
d> >
d> > d> Brandon Metcalf wrote:
d> > d> > Yep, it seems that's the problem. If I pass in $table and use a
d> > d> > lexical variable defined inside do_delete(), the problem goes away.
d> > d> > So, this is where my understanding of how triggers work lacks. For a
d> > d> > given session, each execution of a trigger isn't completely
d> > d> > independent?
d> >
d> > d> Nothing to do with triggers - it's all to do with your Perl code.
d> >
d> >
d> > I respectfully disagree because if I don't execute a DELETE on foo2 as
d> > shown in my original email, the problem doesn't occur.
d> Of course not.
d> > Somewhere in
d> > the trigger execution it's remembering the first table on which the
d> > trigger fired.
d> Yes. in your "sub do_delete" with it's local variable.
d> > So, the information about foo2 is coming from
d> > somewhere and it's in the Perl code.
d> Yes, your local copy of $table in do_delete.
d> > In other words, I performing two
d> > different DELETEs which cause two different invocations of the same
d> > trigger.
d> You've written your code such that do_delete has a local copy of $table.
d> In fact, the way it actually works iirc is that when you exit the
d> trigger function "my $table" goes out of scope and vanishes, but the
d> "$table" in do_delete doesn't vanish and persists from call to call. You
d> might call this a static variable in C terms.
OK. I understand the Perl part of what is going on. What I don't
understand is why $table in do_delete() hangs around. It seems this
is more a characteristic of how triggers work in pgsql. At any rate,
I appreciate the input since it provides me with a fix.
d> > d> #!/usr/bin/perl
d> >
d> > d> sub foo {
d> > d> my $x = shift;
d> > d> print "foo x = $x\n";
d> > d> bar();
d> > d> return;
d> >
d> > d> sub bar {
d> > d> print "bar x = $x\n";
d> > d> }
d> > d> }
d> >
d> > d> foo(1);
d> > d> foo(2);
d> > d> exit;
d> This code mirrors _exactly_ what is happening with your trigger. On the
d> first call to foo $x is set to 1, on the second it's set to 2. That
d> doesn't affect "sub bar" though because its copy of $x is still the one
d> from the first call.
d> Maybe the following makes it clearer:
d> #!/usr/bin/perl
d> sub foo {
d> my $x = shift;
d> print "foo x = $x, ";
d> bar();
d> return;
d> sub bar {
d> print "bar x = $x\n";
d> $x--;
d> }
d> }
d> for my $i (1..5) { foo($i); }
d> exit;
d> $ ./perl_example.pl
d> foo x = 1, bar x = 1
d> foo x = 2, bar x = 0
d> foo x = 3, bar x = -1
d> foo x = 4, bar x = -2
d> foo x = 5, bar x = -3
d> The two $x variables go their separate ways and the one in "bar" doesn't
d> go out of scope at the end of the function.
--
Brandon
Brandon Metcalf wrote: > > OK. I understand the Perl part of what is going on. What I don't > understand is why $table in do_delete() hangs around. It seems this > is more a characteristic of how triggers work in pgsql. At any rate, > I appreciate the input since it provides me with a fix. Because it will persist until either: 1. "sub do_delete" expires 2. the whole perl interpreter expires (which then implies #1) Your trigger function will be "compiled"* just before it is first called and will then exist until #2 above happens (when you disconnect). Since your trigger function exists, that implies do_delete continues to exist which means its version of $table continues to exist. It really is exactly like that example script I attached, where the "exit" equals database end-of-session. * ok, perl is technically interpreted, except of course it isn't really, and I don't know what a better term than compiled would be. -- Richard Huxton Archonet Ltd
d == dev@archonet.com writes: d> Brandon Metcalf wrote: d> > d> > OK. I understand the Perl part of what is going on. What I don't d> > understand is why $table in do_delete() hangs around. It seems this d> > is more a characteristic of how triggers work in pgsql. At any rate, d> > I appreciate the input since it provides me with a fix. d> Because it will persist until either: d> 1. "sub do_delete" expires d> 2. the whole perl interpreter expires (which then implies #1) d> Your trigger function will be "compiled"* just before it is first called d> and will then exist until #2 above happens (when you disconnect). Since d> your trigger function exists, that implies do_delete continues to exist d> which means its version of $table continues to exist. It really is d> exactly like that example script I attached, where the "exit" equals d> database end-of-session. OK, that makes sense. d> * ok, perl is technically interpreted, except of course it isn't really, d> and I don't know what a better term than compiled would be. No, you're right. It is compiled before being executed, but not in the sense that C code is compiled. -- Brandon