From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: panso8@gmail.com
Subject: BUG #18656: "STABLE" function sometimes does not see changes
Date: Tue, 15 Oct 2024 09:58:35 +0000
Message-ID: <18656-cade1780866ef66c@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18656
Logged by: Alexander Alehin
Email address: panso8@gmail.com
PostgreSQL version: 16.4
Operating system: Debian 11
Description:
CREATE TABLE stbl_states(
id int primary key,
state text
);
CREATE TABLE stbl_logs(
ts timestamp default clock_timestamp(),
log text
);
CREATE or replace FUNCTION stbl_get_state(p_id int) RETURNS text
LANGUAGE sql STABLE AS
$$
select coalesce(k.state, 'off') from stbl_states k where k.id = p_id;
$$;
CREATE or replace PROCEDURE stbl_log(p_message text)
LANGUAGE plpgsql AS
$$
begin
insert into stbl_logs(log) values (p_message);
end;
$$;
CREATE or replace PROCEDURE stbl_update_state()
LANGUAGE plpgsql AS
$$
declare
s text;
begin
update stbl_states set state='on' where id=1;
s := stbl_get_state(1);
call stbl_log(s);
call stbl_log(stbl_get_state(1));
insert into stbl_logs(log) values (stbl_get_state(1));
exception when others then
s := SQLERRM;
call stbl_log(s);
end;
$$;
insert into stbl_states(id) values(1);
call stbl_update_state();
select log from stbl_logs order by ts;
---------------------------------------------------------------
log
-----
on
off
on
(3 rows)
But expected:
on
on
on
If "STABLE" or "exception ..." is removed, it will work as expected.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, panso8@gmail.com
Subject: Re: BUG #18656: "STABLE" function sometimes does not see changes
In-Reply-To: <18656-cade1780866ef66c@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox