Re: missing "SPI_finish" that isn't missing

On 12/24/15 16:37, Tom Lane wrote:

> to make this coding pattern work is to set up a subtransaction, and either
> commit it in the success path or roll it back upon catching an error.
> (This is not terribly well documented, but the exception-block handling
> in plpgsql provides a working model to follow.)

Ok, I've just had a look at that in plpgsql, and I see what you mean about
the path of least resistance ... though it's not completely obvious to me
how many pieces of that are there for other plpgsql purposes, and how many
are inherently necessary just to be able to catch and recover from an
error thrown from SPI.

BeginInternalSubTransaction? CreateExecutorState? CreateExprContext?
xact callback? subxact callback?

> 99% of the time, the path of least resistance at the C-code level is to
> avoid expected error conditions in the first place, rather than try to
> catch and clean up. In this example, there are plenty of ways you might
> test whether "mytable" exists before risking the SPI_execute call.

I can think of:

- another SPI query
"select 1 from pg_catalog.pg_class join pg_catalog.pg_namespace n
on relnamespace = n.oid
where nspname = 'myschema' and relname = 'mytable'"

- InvalidOid != get_relname_relid("mytable",
GetSysCacheOid1(NAMESPACENAME, CStringGetDatum("myschema")))

- ... other variations on those ...

The first one strikes me funny because it's an even heavier SQL query
to plan and execute just to find out if I can execute the original
trivial one. (Which doesn't really matter, admittedly, since I need to
do it exactly once.) The second one strikes me funny for the mixture of API
levels, undocumented low level to check existence followed by documented
SPI level for the original query. Almost tempts me to ditch SPI entirely
and learn how to use heap_* methods for the single trivial row retrieval
I need, only by the time I've figured out all that, I'll have forgotten
what I was trying to do in the first place.

Is there a particular, reasonably tidy idiom that has emerged as the usual,
customary approach to a task like this?

Thanks,
-Chap