Horton

Thread

Commits

  1. Fix misevaluation of STABLE parameters in CALL within plpgsql.

  1. BUG #17199: Calling stored procedure with stable function as argument results in wrong result

    The Post Office <noreply@postgresql.org> — 2021-09-21T12:52:14Z

    The following bug has been logged on the website:

Bug reference:      17199
Logged by:          Alexander Nawratil
Email address:      notegihu@confused.at
PostgreSQL version: 13.4
Operating system:   Linux
Description:        

Hello,

we are facing some inconsistencies in one of our unit tests since the last
upgrade of PostgreSQL to 11.13 and 13.4.
When a function that returns just a row count of a table is marked as STABLE
and is called from a stored procedure as argument, the result of the
function is different than when the function is called beforehand and stored
to a local variable.
It's working fine when the function is marked as VOLATILE, or on PostgreSQL
11.12 / 13.3.

Steps to reproduce:

1. Create an empty table
2. Create a function that is marked as STABLE and returns the row count of
the table (RETURN SELECT COUNT(*) FROM table)
3. Create a procedure that takes an integer parameter and simply outputs it,
e.g. RAISE NOTICE
4. In a single DO block: INSERT a row into the table, then CALL
proc(func()); /* --> output: 0, instead of 1 */
5. Storing the result of func() to a variable x, then CALL proc(x); /* -->
output: 1, as expected */

Example SQL:

CREATE TABLE tbl (col INT4);

CREATE OR REPLACE FUNCTION stable_func()
    RETURNS INT4
    STABLE AS
$code$
BEGIN
    RETURN (SELECT COUNT(1) FROM tbl);
END
$code$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION volatile_func()
    RETURNS INT4
    VOLATILE AS
$code$
BEGIN
    RETURN (SELECT COUNT(1) FROM tbl);
END
$code$ LANGUAGE plpgsql;


CREATE OR REPLACE PROCEDURE call_proc(t TEXT, cnt INT4)
AS
$code$
BEGIN
    RAISE NOTICE '%', (t || ' -- count: ' || cnt::TEXT);
END
$code$ LANGUAGE plpgsql;

DO
$$
DECLARE
    x INT4;
BEGIN
    RAISE NOTICE '%', (VERSION());
    INSERT INTO tbl(col) VALUES (1);

    x := volatile_func();
    CALL call_proc('from_var_volatile_func', x);
    CALL call_proc('inline_volatile_func', volatile_func());

    x := stable_func();
    CALL call_proc('from_var_stable_func', x);
    CALL call_proc('inline_stable_func', stable_func());
END
$$;

I would expect that all calls output the same row count of 1. However, the
actual value differs for the last call since the upgrade.

Output on Postgres 13.3:
PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
from_var_volatile_func -- count: 1
inline_volatile_func -- count: 1
from_var_stable_func -- count: 1
inline_stable_func -- count: 1

Output on Postgres 13.4:
PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
from_var_volatile_func -- count: 1
inline_volatile_func -- count: 1
from_var_stable_func -- count: 1
inline_stable_func -- count: 0

The same change in behavior seems to be introduced in 12.8, not being
present in 12.7. I ran the code above using official Docker Hub images.

Best regards,
Alexander Nawratil

  2. Re: BUG #17199: Calling stored procedure with stable function as argument results in wrong result

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-09-21T15:01:11Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> When a function that returns just a row count of a table is marked as STABLE
> and is called from a stored procedure as argument, the result of the
> function is different than when the function is called beforehand and stored
> to a local variable.

Ugh.  Looks like I broke this in 84f5c2908, by not thinking about the
possibility that a CALL's argument expressions would need an up-to-date
snapshot.

Will fix, thanks for the report!

			regards, tom lane

  3. Re: BUG #17199: Calling stored procedure with stable function as argument results in wrong result

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-09-21T15:49:56Z

    I wrote:
> Ugh.  Looks like I broke this in 84f5c2908, by not thinking about the
> possibility that a CALL's argument expressions would need an up-to-date
> snapshot.

Concretely, the attached seems to take care of it.  I'll refrain
from pushing this till later, though, since v14 is frozen for rc1
for a few hours more.

			regards, tom lane