On 08.11.2017 17:23, Merlin Moncure wrote:
On Tue, Oct 31, 2017 at 12:23 PM, Peter Eisentraut
<peter.eisentr...@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
