Обсуждение: BUG #1215: Call sql function from plpgsql results vary.

The following bug has been logged online:

Bug reference:      1215
Logged by:          Bob Henkel

Email address:      bob@teamhenkel.com

PostgreSQL version: 8.0 Beta

Operating system:   Windows XP Home SP1

Description:        Call sql function from plpgsql results vary.

Details:

I was playing around seeing what new things I could do in stored procedures.
Here is the statment I'm using to get the issue.
select * from f_trap_error();
I expect the above statement to alway return 1 which it does.
The issue is I expect the trapped_error table to contain a seq id and than a
999 899
so the table should look like this if I ran select * from f_trap_error();
twice.
1 999
2 899
3 999
4 899
What I'm seeing is the first time I run select * from f_trap_error(); is
1 999
2 899
The second time I run select * from f_trap_error(); the table looks like
this
1 999
2 899
3 999
As you can see the 4th record never got inserted. This would be the insert
that is running in my sql stored procedure.


Here are the database objects I'm using to get this issue.
CREATE TABLE trapped_error
(
  trapped_error_id serial NOT NULL,
  error_code int8
)
WITHOUT OIDS;
-------------------------------
CREATE TABLE dual
(
  x int2
)
WITHOUT OIDS;
---------------------------
insert into dual values(1);
---------------------------
CREATE OR REPLACE FUNCTION f_trap_error()
  RETURNS int8 AS
$BODY$DECLARE
x integer;
BEGIN
    x := 4;

    x := x / 1;

    insert into trapped_error(error_code) values(999);
   select * from f_test_sql() into x;

    RETURN 1;
EXCEPTION
        WHEN division_by_zero THEN

            RAISE NOTICE 'caught division_by_zero';
  insert into trapped_error(error_code) values(2);
RETURN 2;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
-----------------------------------
CREATE OR REPLACE FUNCTION f_test_sql()
  RETURNS int2 AS
$BODY$
--SAVEPOINT my_savepoint;

insert into trapped_error(error_code) VALUES(899);
--ROLLBACK TO my_savepoint;


select * from dual;
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

Let me know if I didn't explain something and if you can't reproduce this. I
will leave the database I created this untouched incase it can help shed
some light on something.
Thanks
Bob

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> I was playing around seeing what new things I could do in stored procedures.
> Here is the statment I'm using to get the issue.
> select * from f_trap_error();
> I expect the above statement to alway return 1 which it does.
> The issue is I expect the trapped_error table to contain a seq id and than a
> 999 899

I think the problem is you declared f_test_sql as IMMUTABLE, which
entitles the planner to execute it once and bind the result as a
constant.  Functions with side-effects should *never* be marked
immutable (nor stable for that matter).

            regards, tom lane

That fixed it, it works now as I had hoped. Thanks again


>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: "Bob Henkel" <bob@teamhenkel.com>
>CC: pgsql-bugs@postgresql.org
>Subject: Re: [BUGS] BUG #1215: Call sql function from plpgsql results vary.
>Date: Wed, 11 Aug 2004 23:06:09 -0400
>
>"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> > I was playing around seeing what new things I could do in stored
>procedures.
> > Here is the statment I'm using to get the issue.
> > select * from f_trap_error();
> > I expect the above statement to alway return 1 which it does.
> > The issue is I expect the trapped_error table to contain a seq id and
>than a
> > 999 899
>
>I think the problem is you declared f_test_sql as IMMUTABLE, which
>entitles the planner to execute it once and bind the result as a
>constant.  Functions with side-effects should *never* be marked
>immutable (nor stable for that matter).
>
>            regards, tom lane