Perl+Postgresql: a function does not return a value if RAISE NOTICE is present

Question

Hi,

I noticed that when I call a PL/PgSQL or PL/Perl function from a Perl script using DBI, it does not return a value if a RAISE NOTICE or elog(NOTICE) is used in the function. To illustrate:

A simple table:

CREATE TABLE "public"."table1" (
  "fld" INTEGER
) WITHOUT OIDS;

A simple function:

CREATE OR REPLACE FUNCTION "public"."function1" () RETURNS integer AS
$body$
DECLARE
  myvar INTEGER;
BEGIN
  SELECT INTO myvar fld FROM table1 LIMIT 1;
  RETURN myvar;
END;
$body$
LANGUAGE 'plpgsql'

A piece of Perl script:

use DBI;
...
my $ref = $dbh->selectcol_arrayref('SELECT function1()');
print $$ref[0];

As it is, it prints the value from the table.

But I get no result if I add RAISE NOTICE as follows:

SELECT INTO myvar fld FROM table1 LIMIT 1;
RAISE NOTICE 'Testing';
RETURN myvar;

Am I missing something or such behavior is by design?

Answer 1

I can't reproduce this, using Debian's Perl 5.10, DBI 1.605 and DBD::Pg 2.8.7 against PostgreSQL 8.3.7. I get the notice printed out as expected.

steve@steve@[local] =# create or replace function public.function1() returns integer language 'plpgsql' as $$ declare myvar integer; begin select into myvar fld from table1 limit 1; raise notice 'Testing'; return myvar; end; $$;
CREATE FUNCTION
steve@steve@[local] =#
[1]+  Stopped                 psql --cluster 8.3/steve
steve@arise:~$ DBI_TRACE=1 perl -MData::Dumper -MDBI -e '$dbh = DBI->connect(qw|dbi:Pg:dbname=steve;port=5433;host=/tmp steve steve|, {RaiseError=>1,PrintError=>0}); print Data::Dumper->new([$dbh->selectcol_arrayref("SELECT function1()")], [qw|result|])->Dump'
    DBI 1.605-ithread default trace level set to 0x0/1 (pid 5739) at DBI.pm line 273 via -e line 0
    Note: perl is running without the recommended perl -w option
    -> DBI->connect(dbi:Pg:dbname=steve;port=5433;host=/tmp, steve, ****, HASH(0x1c9ddf0))
    -> DBI->install_driver(Pg) for linux perl=5.010000 pid=5739 ruid=1000 euid=1000
       install_driver: DBD::Pg version 2.8.7 loaded from /usr/lib/perl5/DBD/Pg.pm
    <- install_driver= DBI::dr=HASH(0x1e06a68)
    !! warn: 0 CLEARED by call to connect method
    <- connect('dbname=steve;port=5433;host=/tmp', 'steve', ...)= DBI::db=HASH(0x1fd8e08) at DBI.pm line 638
    <- STORE('RaiseError', 1)= 1 at DBI.pm line 690
    <- STORE('PrintError', 0)= 1 at DBI.pm line 690
    <- STORE('AutoCommit', 1)= 1 at DBI.pm line 690
    <- STORE('Username', 'steve')= 1 at DBI.pm line 693
    <> FETCH('Username')= 'steve' ('Username' from cache) at DBI.pm line 693
    <- connected('dbi:Pg:dbname=steve;port=5433;host=/tmp', 'steve', ...)= undef at DBI.pm line 699
    <- connect= DBI::db=HASH(0x1fd8e08)
    <- STORE('dbi_connect_closure', CODE(0x1da2280))= 1 at DBI.pm line 708
NOTICE:  Testing
    <- selectcol_arrayref('SELECT function1()')= ( [ '2' ] ) [1 items] at -e line 1
$result = [
            '2'
          ];

I suggest isolating your problem to a small script (like above) and running it with DBI_TRACE set fairly high any seeing what differences you see. Maybe also looking at the release notes for DBD::Pg and seeing if they mention it maybe having been confused by these in the past. With DBI_TRACE=10 I see this:

PQexec
Begin pg_warn (message: NOTICE:  Testing
 DBIc_WARN: 1 PrintWarn: 1)
NOTICE:  Testing
End pg_warn
Begin _sqlstate

So you should be looking for something like that in your own output.

Answer 2

Check the client_min_messages setting in your database server's postgresql.conf file. From the PostgreSQL 8.3 docs:

client_min_messages (string)

Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default is NOTICE. Note that LOG has a different rank here than in log_min_messages.

Answer 3

I have the problem Dennys describes above- with RAISE NOTICE on in an postgres function i dont get a value from DBI.
In the Postgres manual (1) I found this:
"Emit a log or error message. Possible levels are DEBUG, LOG, INFO, NOTICE, WARNING, and ERROR. ERROR raises an error condition; if this is not trapped by the surrounding Perl code, the error propagates out to the calling query, causing the current transaction or subtransaction to be aborted."

I am not an Perl expert- perhaps anyone can help me with the perl code that traps the postrgres notice???

Thanks Marcus