tgl@sss.pgh.pa.us wrote:
bryn@yugabyte.com writes:
get stacked diagnostics msg = message_text;
if msg != 'relation "pg_temp.flag" does not exist' then
This is pretty fragile --- eg, it will fall over with translated messages. I think you could presume that if the error condition name is undefined_table then you know what to do.
Mea culpa. I should have stressed that my code was meant to be a sketch rather than the real thing. So my use of "on commit delete rows" suits the "hard shell paradigm" that I described here:
where the client code does:
check out connection
call a user-defined API subprogram
release connection
and where I therefore want automatic check-out-duration session state.
In a different use case, I'd want session-duration session state. There. I'd use "on commit preserve rows".
About testing what "message_text" from "get stacked diagnostics msg" returns, yes… of course its sensitivity to the current choice of national language is a non-starter. I don't like to assume more than I have to. So I might say this:
if msg !~ '"pg_temp.flag"' then
But, then again, I might decide that it's just too fussy.
I've seen this pattern in use:
create temp table if not exists pg_temp.flag(val boolean not null) on commit delete rows;
insert into pg_temp.flag(val) values(true);
But doing a DDL before every use of the session-state representation felt heavier than assuming that it's there and creating the table only if it isn't. But I haven't done any timing tests. Is the "create… if not exists" so lightweight when the to-be-created object does exist that I'm fussing over nothing?