Re: SQL procedures

On 08.11.2017 17:23, Merlin Moncure wrote:
> On Tue, Oct 31, 2017 at 12:23 PM, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> - Transaction control in procedure bodies
> This feature is really key, since it enables via SQL lots of things
> that are not possible without external coding, including:
> *) very long running processes in a single routine
> *) transaction isolation control inside the procedure (currently
> client app has to declare this)
> *) certain error handling cases that require client side support
> *) simple in-database threading
> *) simple construction of daemon scripts (yeah, you can use bgworker
> for this, but pure sql daemon with a cron heartbeat hook is hard to
> beat for simplicity)
>
> I do wonder how transaction control could be added later.
>
> The last time I (lightly) looked at this, I was starting to think that
> working transaction control into the SPI interface was the wrong
> approach; pl/pgsql would have to adopt a very different set of
> behaviors if it was called in a function or a proc. If you restricted
> language choice to purely SQL, you could work around this problem; SPI
> languages would be totally abstracted from those sets of
> considerations and you could always call an arbitrary language
> function if you needed to. SQL has no flow control but I'm not too
> concerned about that.
>
> merlin
>
>
I am also very interested in answer on this question: how you are going
to implement transaction control inside procedure?
Right now in PostgresPRO EE supports autonomous transactions. Them are
supported both for SQL and plpgsql/plpython APIs.
Them are implemented by saving/restoring transaction context, so unlike
most of other ATX implementations, in pgpro autonomous
transaction is executed by the same backend. But it is not so easy to
do: in Postgres almost any module have its own static variables which
keeps transaction specific data.
So we have to provide a dozen of suspend/resume functions:
SuspendSnapshot(),  SuspendPredicate(), SuspendStorage(),
SuspendInvalidationInfo(), SuspendPgXact(), PgStatSuspend(),
TriggerSuspend(), SuspendSPI()... and properly handle local cache
invalidation. Patch consists of more than 5 thousand lines.

So my question is whether you are going to implement something similar
or use completely different approach?
In first case it will be good to somehow unite our efforts... For
example we can publish our ATX patch for Postgres 10.
We have not done it yet, because there seems to be no chances to push
this patch to community.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company