On Wed, Mar 12, 2008 at 1:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Hunsaker" <badalex@gmail.com> writes:
>
> > Perhaps my simple updates are not enough for analyze to
> > invalidate the query plan? Should I be doing inserts/deletes or just
> > more updates?
>
> No, AFAICS the plan inval will happen after a vacuum regardless of whether
> anything actually changed. I'm thinking that there's some property
> of the query or the invoked function that's relevant, but it's not
> clear what. (We'll know soon enough once we can reproduce it easily...)
>
> regards, tom lane
>
Ok I got it... have not been able to reproduce it in pure pgsql yet
so... maybe its a DBD::Pg bug? (CC'd Greg Sabino Mullane)
This crashes everytime for me. and just about every part is required. That is
If I take out the function call it works
If I take out the junk = ? it works
If I take out the nextval('junk_seq') it works
If I take out the begin_work it works
....
#!/usr/bin/perl
use strict;
use warnings;
use DBI();
use DBD::Pg();
my $db = DBI->connect('dbi:Pg:dbname=test;', {'pg_server_prepare'=>1,
'pg_prepare_now'=>1}) || die "could not connect: $!";
$db->do('create table junk (junk text, junk_id int);');
$db->do('create sequence junk_seq;');
$db->do("create or replace function junk_func(int) returns integer as
'select junk_id from junk where junk_id = \$1;' language 'sql' stable
strict;");
my $sth = $db->prepare('select * from junk where junk = ? and junk_id
= junk_func(1) limit 1;');
$db->do('vacuum junk;');
$db->begin_work();
$db->do('select nextval(\'junk_seq\');');
$sth->execute('test') || die "failed: $!";
$sth->fetchall_arrayref();
$db->disconnect();