server side prepare breaks view inserts

Kovacs Baldvin <[email protected]> Sat, 13 May 2006 03:08:45 +0200

Newsgroups	gmane.comp.db.postgresql.dbdpg
Message-ID	<[email protected]>

Hello,

it seems to be that when using { pg_server_prepare => 1 }
then I can't get back the result table of the insert function triggered
by the insert rule of a view.

I cooked a minimal example, see the attached files. For trying it
out, you need to have a database with the plpgsql language present.
Please adjust the DBI->connect(...) call to match the parameters
of your database to run the program. Before starting the program,
you need to create the schema with "psql < schema.sql".

I am using debian, and
postgresql     7.5.8
libdbd-pg-perl 1.45-2

My results are:

------------------------------
Running with { pg_server_prepare => 0 }
Fetching the new id...
The new id is: 9
------------------------------
Running with { pg_server_prepare => 1 }
Fetching the new id...
DBD::Pg::st fetchrow_array failed: no statement executing
------------------------------
Running with { pg_server_prepare => 2 }
Fetching the new id...
The new id is: 11
------------------------------

Please somebody try to explain this phenomena...
(pg_server_prepare => 1 seems to be the default for my
installation...)

Best regards,
Baldvin Kovacs

_______________________________________________
Dbdpg-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/dbdpg-general

drop.sql (text/plain, 128 B)

drop rule apple_insert_rule on apple_view;
drop function apple_view_insert(text);
drop view apple_view;
drop table apple_table;

schema.sql (text/plain, 723 B)

-----------------------------
create table apple_table (id serial, color text);
-----------------------------
create view 
    apple_view as
select * from apple_table;
-----------------------------
create function 
    apple_view_insert(text) 
    returns integer as '
declare
    new_color text;
    new_id int;
    new_oid int;
begin
    insert into apple_table(color) values (new_color);
    get diagnostics new_oid = result_oid;
    select into new_id id from apple_table where oid = new_oid;
    return new_id;
end;
' language plpgsql;
-----------------------------
create rule 
    apple_insert_rule as on insert to apple_view
    do instead
        select apple_view_insert(NEW.color);
-----------------------------

test.pl (text/x-perl, 988 B)

#!/usr/bin/perl -w

use DBI;

$| = 1;
my $server_side_prepares = ($ARGV[0] || "") ne "-n";

my $dbh = DBI->connect("dbi:Pg:dbname=$ENV{USER}", "", "", {AutoCommit => 1});
die "could not connect" unless $dbh;

my $query_string = "INSERT INTO apple_view (color) values (?);";

my $sth;

foreach my $pg_server_prepare (0,1,2) {

    print "-"x30, "\nRunning with ",
    "{ pg_server_prepare => $pg_server_prepare }\n";
    
    $sth = $dbh->prepare($query_string, 
                         { pg_server_prepare => $pg_server_prepare });
    die unless $sth;
    $sth->execute('red')        or die DBI::errstr;
    get_the_new_id($sth);

}

print "-"x30,"\n";

sub get_the_new_id {
    my $sth = shift;

    if ( $sth->{'pg_cmd_status'} =~ /^INSERT 0 0/ ) {
        print "Fetching the new id...\n";
        my ($id) = ($sth->fetchrow_array);
        print "The new id is: $id\n"
            unless $sth->err();
    } else {
        die "We do not have the expected cmd status\n";
    }
}

1;