Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
>> bryn(at)yugabyte(dot)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:

https://www.postgresql.org/message-id/F0A23614-749D-4A89-84C5-119D4000F9FE@yugabyte.com

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?