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
