Re: [HACKERS] autonomous transactions

[Michael Paquier]

On Sat, Jan 7, 2017 at 5:30 PM, Andrey Borodin  wrote:
> The new status of this patch is: Ready for Committer

I don't think that this thread has reached a conclusion yet. From what
I can see the last patch does not apply, so I have moved the patch to
next CF with "waiting on author".
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Andrey Borodin]

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:   tested, passed
Spec compliant:   tested, passed
Documentation:tested, passed

Here’s review of Background Sessions v2 patch.
===Purpose===
Patch provides API for controlling other backend. Also, patch contains Python 
API support for calling C API.

===Overall status===
Patch applies to current HEAD clearly.
Contains new test and passes existing tests.
Contains sufficient documentation.
Contains 2 TODO items. Not sure it's OK to it leave so.
Another patch from this commitfest (pg_background) is based on this patch.

===Suggestions===
I haven’t found a way to safely acquire status of session (without possibility 
of ereport(ERROR)).
I do not see how to pass massive data, except by one long char* SQL. All the 
values have to be formatted as text.
BackgroundSessionStart() result do not contain PID. This functionality is 
expected by pg_background (though, can be added separately by pg_background). I 
suppose, this is done to prevent API users from accessing internals of 
BackgroundSession structure. But some information have to be public, anyway.
bgsession.c code contains very little comments.
I do not think that switch inside a switch (see bgsession_worker_main()) is 
easy to follow.

===Conclusion===
There’s always something to improve, but I think that this patch is ready for 
committer.

PS. I’ve read the db_link patches, but this review does not apply to them. I 
suppose db_link refactoring would be useful and functionality is added, so I 
think these patches deserve separate commitfest entry.

Best regards, Andrey Borodin.

The new status of this patch is: Ready for Committer

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Petr Jelinek]

On 11/10/16 21:54, Merlin Moncure wrote:
> On Tue, Oct 11, 2016 at 10:06 AM, Petr Jelinek  wrote:
>> On 10/10/16 16:44, Merlin Moncure wrote:
>>> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs  wrote:
 On 6 October 2016 at 21:27, Robert Haas  wrote:
> I think we should implement background transactions and call them
> background transactions.  That allows us to expose additional
> functionality which is useful, like the ability to kick something off
> and check back later for the results.  There's no reason to call it
> background transactions and also call it autonomous transactions: one
> feature doesn't need two names.

 I'm happy to also invoke it via an alternate mechanism or API, so that
 it can continue to be used even if the above mechanism changes.

 We have no need to wait for the perfect solution, even assuming we
 would ever agree that just one exists.
>>>
>>> -1 on implementing both autonomous and background transactions.  This
>>> will confuse everyone.
>>
>> I personally care much more about having background transactions than
>> autonomous ones (as I only ever had use-cases for the background ones)
>> so don't agree there.
> 
> All right.  But would you agree then that AT should at least emulate
> competing implementations? A major advantage of bgworkers is possibly
> supporting concurrent activity and maybe the syntax could be more
> directed to possibly moving in that direction other than copying
> oracle style (PRAGMA etc), particularly if the locking rules are
> substantially different.
> 

Yes, I am just saying we should have both. I don't feel like I can judge
if background transactions solve autonomous transactions use-cases so I
am not expressing opinion there.

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Pavel Stehule]

2016-10-11 21:54 GMT+02:00 Merlin Moncure :

> On Tue, Oct 11, 2016 at 10:06 AM, Petr Jelinek 
> wrote:
> > On 10/10/16 16:44, Merlin Moncure wrote:
> >> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs 
> wrote:
> >>> On 6 October 2016 at 21:27, Robert Haas  wrote:
>  I think we should implement background transactions and call them
>  background transactions.  That allows us to expose additional
>  functionality which is useful, like the ability to kick something off
>  and check back later for the results.  There's no reason to call it
>  background transactions and also call it autonomous transactions: one
>  feature doesn't need two names.
> >>>
> >>> I'm happy to also invoke it via an alternate mechanism or API, so that
> >>> it can continue to be used even if the above mechanism changes.
> >>>
> >>> We have no need to wait for the perfect solution, even assuming we
> >>> would ever agree that just one exists.
> >>
> >> -1 on implementing both autonomous and background transactions.  This
> >> will confuse everyone.
> >
> > I personally care much more about having background transactions than
> > autonomous ones (as I only ever had use-cases for the background ones)
> > so don't agree there.
>
> All right.  But would you agree then that AT should at least emulate
> competing implementations? A major advantage of bgworkers is possibly
> supporting concurrent activity and maybe the syntax could be more
> directed to possibly moving in that direction other than copying
> oracle style (PRAGMA etc), particularly if the locking rules are
> substantially different.
>

There is a big trap - AT is usually used for writing to log tables. When BT
fails on maximum active workers then, then you cannot do any expected
operation.

Regards

Pavel


>
> merlin
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] autonomous transactions

[Merlin Moncure]

On Tue, Oct 11, 2016 at 10:06 AM, Petr Jelinek  wrote:
> On 10/10/16 16:44, Merlin Moncure wrote:
>> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs  wrote:
>>> On 6 October 2016 at 21:27, Robert Haas  wrote:
 I think we should implement background transactions and call them
 background transactions.  That allows us to expose additional
 functionality which is useful, like the ability to kick something off
 and check back later for the results.  There's no reason to call it
 background transactions and also call it autonomous transactions: one
 feature doesn't need two names.
>>>
>>> I'm happy to also invoke it via an alternate mechanism or API, so that
>>> it can continue to be used even if the above mechanism changes.
>>>
>>> We have no need to wait for the perfect solution, even assuming we
>>> would ever agree that just one exists.
>>
>> -1 on implementing both autonomous and background transactions.  This
>> will confuse everyone.
>
> I personally care much more about having background transactions than
> autonomous ones (as I only ever had use-cases for the background ones)
> so don't agree there.

All right.  But would you agree then that AT should at least emulate
competing implementations? A major advantage of bgworkers is possibly
supporting concurrent activity and maybe the syntax could be more
directed to possibly moving in that direction other than copying
oracle style (PRAGMA etc), particularly if the locking rules are
substantially different.

merlin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Pavel Stehule]

2016-10-11 17:06 GMT+02:00 Petr Jelinek :

> On 10/10/16 16:44, Merlin Moncure wrote:
> > On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs 
> wrote:
> >> On 6 October 2016 at 21:27, Robert Haas  wrote:
> >>> I think we should implement background transactions and call them
> >>> background transactions.  That allows us to expose additional
> >>> functionality which is useful, like the ability to kick something off
> >>> and check back later for the results.  There's no reason to call it
> >>> background transactions and also call it autonomous transactions: one
> >>> feature doesn't need two names.
> >>
> >> I'm happy to also invoke it via an alternate mechanism or API, so that
> >> it can continue to be used even if the above mechanism changes.
> >>
> >> We have no need to wait for the perfect solution, even assuming we
> >> would ever agree that just one exists.
> >
> > -1 on implementing both autonomous and background transactions.  This
> > will confuse everyone.
> >
>
> I personally care much more about having background transactions than
> autonomous ones (as I only ever had use-cases for the background ones)
> so don't agree there.
>

we can, we should to have both - background can be used for paralelism,
autonomous for logging.

they are not 100% replaceable.

Regards

Pavel


>
> --
>   Petr Jelinek  http://www.2ndQuadrant.com/
>   PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] autonomous transactions

[Petr Jelinek]

On 10/10/16 16:44, Merlin Moncure wrote:
> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs  wrote:
>> On 6 October 2016 at 21:27, Robert Haas  wrote:
>>> I think we should implement background transactions and call them
>>> background transactions.  That allows us to expose additional
>>> functionality which is useful, like the ability to kick something off
>>> and check back later for the results.  There's no reason to call it
>>> background transactions and also call it autonomous transactions: one
>>> feature doesn't need two names.
>>
>> I'm happy to also invoke it via an alternate mechanism or API, so that
>> it can continue to be used even if the above mechanism changes.
>>
>> We have no need to wait for the perfect solution, even assuming we
>> would ever agree that just one exists.
> 
> -1 on implementing both autonomous and background transactions.  This
> will confuse everyone.
> 

I personally care much more about having background transactions than
autonomous ones (as I only ever had use-cases for the background ones)
so don't agree there.

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Merlin Moncure]

On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs  wrote:
> On 6 October 2016 at 21:27, Robert Haas  wrote:
>> I think we should implement background transactions and call them
>> background transactions.  That allows us to expose additional
>> functionality which is useful, like the ability to kick something off
>> and check back later for the results.  There's no reason to call it
>> background transactions and also call it autonomous transactions: one
>> feature doesn't need two names.
>
> I'm happy to also invoke it via an alternate mechanism or API, so that
> it can continue to be used even if the above mechanism changes.
>
> We have no need to wait for the perfect solution, even assuming we
> would ever agree that just one exists.

-1 on implementing both autonomous and background transactions.  This
will confuse everyone.

The lingo here is no so important, I think.  What *is* important is
defining how the locking and execution rules should work and the
implementation should flow from that.  Those rules should be estimated
from competing implementations and how well they work.  +1 for any
solution that makes migration from other solutions to postgres easier.

bgworkers should be considered if you want things to run in parallel.
Reading the proposal (note, I may have missed it) it isn't clear to me
if you can have the parent and AT run a query at the same time.
Should this (parallel execution) be a design goal, then that's the end
of the story.

However I don't think it is TBH.  ISTM the expectation is single
threaded behavior with finer grained control of commits.   If we're
not 100% clear on this point one way or the other then things are a
bit preemptive.  Maybe we are clear and I missed something?

One major advantage non-bgworker serilized execution approach is that
certain classes of deadlock are easier to detect or do not exist since
there is only one execution state; AIUI it's impossible for two
transaction states to be simultaneously waiting assuming the pl/pgsql
instuctions are not run in parallel with one exception, and that is
the AT trying to acquire a lock exclusively held by the master.  If
the AT blocks on the parent it ought to be O(1) and instant to detect
that and roll it back with right supporting infrastructure in the lock
manager.  It also makes sharing execution state much easier,
especially the parts that look like, "I'm waiting here until the other
guy finishes" since there's only one "guy".

How will advisory locks work? I think they'd block with bgworkers and
not block with non-bgworkers.  What about other session based stuff
like prepared statements?  Expectations around those cases out to
clarify the implementation.

merlin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Andres Freund]

On 2016-10-06 10:56:34 +0100, Simon Riggs wrote:
> On 7 September 2016 at 20:46, Robert Haas  wrote:
> > On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs  wrote:
> > True.  I believe this issue has been discussed before, and I think
> > it's a solvable issue.  I believe that an autonomous transaction could
> > be made to appear to the rest of the system (outside the affected
> > backend) as if it were a subtransaction, but then get committed before
> > the containing transaction gets committed.  This would avoid needing
> > more PGPROCs (but getting deadlock detection to work is hard).
> 
> Just to point out that I've actually written this approach already.
> The patch is available, Autonomous Subtransactions.
> We discussed it in Ottawa and it was rejected. (I thought Robert was
> there, but Serge and Tom definitely were).

Hm, maybe I'm missing something, but don't pretty all of the objections
to that approach also apply to this one, baring some additional changes
required for lock conflict detection?


> We have various approaches... summarised in chronological order of
> their suggestion
> 
> 1. Use additional PGXACTs - rejected because it wouldn't provide enough room

Doesn't this approach precisely requires additional PGXACTs in the form
of additional background workers? Plus a full process overhead?


> 2. Use Autonomous SubTransactions - rejected because the semantics are
> different to what we might expect from ATs

Which semantics are fundamentally different?  Snapshot visibility seems
fairly trivial to adapt, and so seems locking. In even seems slightly
easier to give a good error message about deadlocking against the main
transaction in this approach.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Thu, Oct 6, 2016 at 4:53 PM, Simon Riggs  wrote:
> For myself, I don't care what you call it.
>
> I just want to be able to invoke it by saying PRAGMA AUTONOMOUS_TRANSACTION;
> and I know many others do also.

To me, those statements are rather contradictory, and I don't support
the latter proposal.  However, I don't have the only vote here,
either.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Simon Riggs]

On 6 October 2016 at 21:27, Robert Haas  wrote:

>> * The labelling "Autonomous Transaction" is a simple coat of paint,
>> which can easily be transferred to a better implementation if one
>> comes. If one doesn't then its better to have something than nothing.
>> So I suggest we commit Background Transactions first and then in a
>> fairly thin commit, implement Autonomous Transactions on top of it for
>> now and if we get a better one, switch it over.
>
> I think we should implement background transactions and call them
> background transactions.  That allows us to expose additional
> functionality which is useful, like the ability to kick something off
> and check back later for the results.  There's no reason to call it
> background transactions and also call it autonomous transactions: one
> feature doesn't need two names.

For myself, I don't care what you call it.

I just want to be able to invoke it by saying PRAGMA AUTONOMOUS_TRANSACTION;
and I know many others do also.
If a better implementation emerges I would happily replace this one
with another.

I'm happy to also invoke it via an alternate mechanism or API, so that
it can continue to be used even if the above mechanism changes.

We have no need to wait for the perfect solution, even assuming we
would ever agree that just one exists.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Thu, Oct 6, 2016 at 5:56 AM, Simon Riggs  wrote:
> Just to point out that I've actually written this approach already.
> The patch is available, Autonomous Subtransactions.
> We discussed it in Ottawa and it was rejected. (I thought Robert was
> there, but Serge and Tom definitely were).

Where is the patch?

> See other posts in this thread by Serge and Craig to explain why.

I don't think the posts on Craig and Serge explain why that approach
was rejected or would be a bad idea.

> We have various approaches... summarised in chronological order of
> their suggestion
>
> 1. Use additional PGXACTs - rejected because it wouldn't provide enough room

Of course, a background worker uses a PGXACT too and a lot more, so if
you think extra PGXACTs are bad, you should *really* think background
workers are bad.

> 2. Use Autonomous SubTransactions - rejected because the semantics are
> different to what we might expect from ATs

In what way?  I think the questions of how you implement it and what
the semantics are are largely orthogonal questions.  To which proposal
is this referring?

> 3. Use background transactions (this thread)

Sure.

> 4. Use pause and resume so we don't use up too many PGXACTs

I don't know what "pause and resume" means.

> * The labelling "Autonomous Transaction" is a simple coat of paint,
> which can easily be transferred to a better implementation if one
> comes. If one doesn't then its better to have something than nothing.
> So I suggest we commit Background Transactions first and then in a
> fairly thin commit, implement Autonomous Transactions on top of it for
> now and if we get a better one, switch it over.

I think we should implement background transactions and call them
background transactions.  That allows us to expose additional
functionality which is useful, like the ability to kick something off
and check back later for the results.  There's no reason to call it
background transactions and also call it autonomous transactions: one
feature doesn't need two names.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Petr Jelinek]

On 06/10/16 11:56, Simon Riggs wrote:
> 
> * The labelling "Autonomous Transaction" is a simple coat of paint,
> which can easily be transferred to a better implementation if one
> comes. If one doesn't then its better to have something than nothing.
> So I suggest we commit Background Transactions first and then in a
> fairly thin commit, implement Autonomous Transactions on top of it for
> now and if we get a better one, switch it over.
> 

+1 to this

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Simon Riggs]

On 7 September 2016 at 20:46, Robert Haas  wrote:
> On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs  wrote:
>> On 2 September 2016 at 09:45, Robert Haas  wrote:
>>> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
>>>  wrote:
 I would like to propose the attached patch implementing autonomous
 transactions for discussion and review.
>>>
>>> I'm pretty skeptical of this approach.  Like Greg Stark, Serge Rielau,
>>> and Constantin Pan, I had expected that autonomous transactions should
>>> be implemented inside of a single backend, without relying on workers.
>>
>> The problem is that we have limits on the number of concurrent
>> transactions, which is currently limited by the number of procs.
>
> True.  I believe this issue has been discussed before, and I think
> it's a solvable issue.  I believe that an autonomous transaction could
> be made to appear to the rest of the system (outside the affected
> backend) as if it were a subtransaction, but then get committed before
> the containing transaction gets committed.  This would avoid needing
> more PGPROCs (but getting deadlock detection to work is hard).

Just to point out that I've actually written this approach already.
The patch is available, Autonomous Subtransactions.
We discussed it in Ottawa and it was rejected. (I thought Robert was
there, but Serge and Tom definitely were).

See other posts in this thread by Serge and Craig to explain why.


I take your point that startup time may be not as good as it can be.
But what Peter has works and is useful, whatever we call it.

We have various approaches... summarised in chronological order of
their suggestion

1. Use additional PGXACTs - rejected because it wouldn't provide enough room
2. Use Autonomous SubTransactions - rejected because the semantics are
different to what we might expect from ATs
3. Use background transactions (this thread)
4. Use pause and resume so we don't use up too many PGXACTs

What I see is that there are some valid and useful features here and
we should be developing them further because they have other benefits.

* Autonomous Subtransactions might not work for Autonomous
Transactions, but they are useful for incremental loading of large
data, which then allows easier logical decoding. So that sounds like
we should do that anyway, even if they are not ATs. They can also be
used within parallel tasks.

* Background Transactions are useful and we should have them.

* The labelling "Autonomous Transaction" is a simple coat of paint,
which can easily be transferred to a better implementation if one
comes. If one doesn't then its better to have something than nothing.
So I suggest we commit Background Transactions first and then in a
fairly thin commit, implement Autonomous Transactions on top of it for
now and if we get a better one, switch it over.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Peter Eisentraut]

On 9/3/16 9:08 AM, Serge Rielau wrote:
> Interestingly, despite being supported in PL/SQL on nested BEGIN END blocks, 
> we nearly exclusively see AT’s covering the entire function or trigger.

Oracle only supports an entire PL/SQL function to be declared
autonomous.  But it was pretty easy in my implementation to allow that
for arbitrary blocks.  In fact, it would have been harder not to do that.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Peter Eisentraut]

On 8/31/16 9:11 AM, Craig Ringer wrote:
> Peter, you mention "Oracle-style autonomous transaction blocks".
> 
> What are the semantics to be expected of those with regards to:
> 
> - Accessing objects exclusively locked by the outer xact or where the
> requested lockmode conflicts with a lock held by the outer xact
> 
> - Visibility of data written by the outer xact

The semantics are that the autonomous session is completely isolated
from the parent session.  It has no special knowledge about transactions
happening on the parent.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Peter Eisentraut]

On 8/31/16 8:46 AM, Greg Stark wrote:
> I'm surprised by the background worker. I had envisioned autonomous
> transactions being implemented by allowing a process to reserve a
> second entry in PGPROC with the same pid. Or perhaps save its existing
> information in a separate pgproc slot (or stack of slots) and
> restoring it after the autonomous transaction commits.

There is quite likely room for a feature like that too, but it's not
this one.

> Using a background worker mean that the autonomous transaction can't
> access any state from the process memory.

That is intentional.

Autonomous transactions is actually a misnomer.  It's autonomous
sessions.  But Oracle names it wrong.  Autonomous sessions (or whatever
you want to call them) have their own session state, configuration
parameters, potentially different plugins loaded, different
authorization state, and so on.

> What happens if a
> statement timeout occurs during an autonomous transaction?

Right now not much. ;-)  But I think the behavior should be that the
autonomous session is aborted.

> What
> happens if you use a pl language in the autonomous transaction and if
> it tries to use non-transactional information such as prepared
> statements?

The same thing that happens if you open a new unrelated database
connection and try to do that.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Peter Eisentraut]

On 8/31/16 12:38 AM, Jaime Casanova wrote:
> On 30 August 2016 at 20:50, Peter Eisentraut
>  wrote:
>>
>> - Patches to PL/pgSQL to implement Oracle-style autonomous transaction
>> blocks:
>>
>> AS $$
>> DECLARE
>>   PRAGMA AUTONOMOUS_TRANSACTION;
>> BEGIN
>>   FOR i IN 0..9 LOOP
>> START TRANSACTION;
>> INSERT INTO test1 VALUES (i);
>> IF i % 2 = 0 THEN
>> COMMIT;
>> ELSE
>> ROLLBACK;
>> END IF;
>>   END LOOP;
>>
>>   RETURN 42;
>> END;
>> $$;
>>
> 
> this is the syntax it will use?

That is the syntax that Oracle uses.  We could make up our own.

> i just compiled this in head and created a function based on this one.
> The main difference is that the column in test1 it's a pk so i used
> INSERT ON CONFLICT DO NOTHING
> 
> and i'm getting this error
> 
> postgres=# select foo();
> LOG:  namespace item variable itemno 1, name val
> CONTEXT:  PL/pgSQL function foo() line 7 at SQL statement
> STATEMENT:  select foo();
> ERROR:  null value in column "i" violates not-null constraint
> DETAIL:  Failing row contains (null).
> STATEMENT:  INSERT INTO test1 VALUES (val) ON CONFLICT DO NOTHING
> ERROR:  null value in column "i" violates not-null constraint
> DETAIL:  Failing row contains (null).
> CONTEXT:  PL/pgSQL function foo() line 7 at SQL statement
> STATEMENT:  select foo();
> ERROR:  null value in column "i" violates not-null constraint
> DETAIL:  Failing row contains (null).
> CONTEXT:  PL/pgSQL function foo() line 7 at SQL statement
> 
> this happens even everytime i use the PRAGMA even if no START
> TRANSACTION, COMMIT or ROLLBACK are used

The PL/pgSQL part doesn't work well yet.  If you want to play around,
use the PL/Python integration.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Tue, Sep 13, 2016 at 5:06 PM, Petr Jelinek  wrote:
> I mostly agree. I think if this was called something like background
> transactions it might be better. It's definitely useful functionality but
> the naming is clearly contentious. It won't stop people using it for same
> use-cases as autonomous transactions though (which is fine).

Quite right.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Petr Jelinek]

On 13/09/16 22:24, Robert Haas wrote:

On Wed, Sep 7, 2016 at 9:14 PM, Craig Ringer
 wrote:

That's probably going to be one of the smaller costs. Doing this with
bgworkers won't be cheap, but you need to consider the alternative.
Factoring out all transaction-specific data currently stored in or
pointed to by globals into a transaction state struct that can be
swapped out. Making PgXact and PGPROC capable of representing
multiple/suspended transactions. Lots more. Much of which would have a
performance impact on all day to day operations whether or not
autononomous xacts are actually in use.

I haven't looked into it in detail. Peter can probably explain more
and better. I'm just pointing out that I doubt there's any way to do
this without a cost somewhere, and having that cost limited to actual
uses of autonomous xacts would be nice.


I don't really believe this line of argument.  I mean, sure, it's nice
to limit the cost of features to the people who are using those
features.  Totally agreed.  But if the cost really wouldn't be that
high anyway, which I suspect is the case here, then that argument
loses its force.  And if that separation increases the performance
cost of the feature by two or three orders of magnitude in realistic
use cases, then you really have to wonder if you've picked the right
approach.

Again, I'm not saying that having ways to run commands in the
background in a worker isn't useful.  If I thought that wasn't useful,
I would not have written pg_background and developed it as far as I
did.  But I still don't think that's the right way to implement an
autonomous transaction facility.



I mostly agree. I think if this was called something like background 
transactions it might be better. It's definitely useful functionality 
but the naming is clearly contentious. It won't stop people using it for 
same use-cases as autonomous transactions though (which is fine).


--
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Wed, Sep 7, 2016 at 9:14 PM, Craig Ringer
 wrote:
> That's probably going to be one of the smaller costs. Doing this with
> bgworkers won't be cheap, but you need to consider the alternative.
> Factoring out all transaction-specific data currently stored in or
> pointed to by globals into a transaction state struct that can be
> swapped out. Making PgXact and PGPROC capable of representing
> multiple/suspended transactions. Lots more. Much of which would have a
> performance impact on all day to day operations whether or not
> autononomous xacts are actually in use.
>
> I haven't looked into it in detail. Peter can probably explain more
> and better. I'm just pointing out that I doubt there's any way to do
> this without a cost somewhere, and having that cost limited to actual
> uses of autonomous xacts would be nice.

I don't really believe this line of argument.  I mean, sure, it's nice
to limit the cost of features to the people who are using those
features.  Totally agreed.  But if the cost really wouldn't be that
high anyway, which I suspect is the case here, then that argument
loses its force.  And if that separation increases the performance
cost of the feature by two or three orders of magnitude in realistic
use cases, then you really have to wonder if you've picked the right
approach.

Again, I'm not saying that having ways to run commands in the
background in a worker isn't useful.  If I thought that wasn't useful,
I would not have written pg_background and developed it as far as I
did.  But I still don't think that's the right way to implement an
autonomous transaction facility.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Craig Ringer]

On 8 Sep. 2016 1:38 pm, "Andres Freund"  wrote:
>
> I kind of dislike this approach for a different reason than already
> mentioned in this thread: Namely it's not re-usable for implementing
> streaming logical replication of large transaction, i.e. allow to decode
> & apply un-committed changes.  The issue there is that one really needs
> to have multiple transactions active in the same connection, which this
> approach does not allow.

I've been thinking about this recently with an eye to handling the majority
of transactions on a typical system that neither perform DDL nor are
concurrent with it.

The following might be fairly nonsensical if I've misunderstood the problem
as I've not had a chance to more than glance at it, but:

I wonder if some kind of speculative concurrent decoding+output is possible
without being able to have multiple xacts on a backend. We could use a
shared xact and snapshot for all concurrent xacts. If any of them do
anything that requires invalidations etc we dump the speculatively
processed ones and fall back to reorder buffering and serial output at
commit time until we pass the xact that caused an invalidation and don't
have more pending. Add a callback to the output plugin to tell it that
speculative decoding of an xact has been aborted.

Or maybe even just put that speculstive decoding on hold and pick up where
we left off partway through the reorder buffer when we get around to
processing it serially. That way we don't have to discard work already
done. More usefully we could avoid having to enqueue stuff into the reorder
buffer just in case we have to switch to fallback serial decoding.

Failing that:

Since logical decoding only needs read only xacts that roll back, it only
needs multiple backend private virtualxacts. They don't need
SERIALIZABLE/SSI which I think (?) means other backends don't need to be
able to wait on them. That seems simpler than what autonomous xacts would
need since there we need them exposed in PgXact, waitable-on for txid
locks, etc. Right? In the same way that historical snapshots are cut-down
maybe logical decoding xacts can be too.

I suspect that waiting for full in-process multiple xact support to do
interleaved decoding/replay will mean a long wait indeed.

Re: [HACKERS] autonomous transactions

[Andres Freund]

Hi,

On 2016-08-30 21:50:05 -0400, Peter Eisentraut wrote:
> I would like to propose the attached patch implementing autonomous
> transactions for discussion and review.
> 
> This work was mostly inspired by the discussion about pg_background a
> while back [0].  It seemed that most people liked the idea of having
> something like that, but couldn't perhaps agree on the final interface.
> Most if not all of the preliminary patches in that thread were
> committed, but the user interface portions were then abandoned in favor
> of other work.  (I'm aware that rebased versions of pg_background
> existing.  I have one, too.)

I kind of dislike this approach for a different reason than already
mentioned in this thread: Namely it's not re-usable for implementing
streaming logical replication of large transaction, i.e. allow to decode
& apply un-committed changes.  The issue there is that one really needs
to have multiple transactions active in the same connection, which this
approach does not allow.

That's not necessarily a fatal objection, but I did want to throw that
as a design goal out there.

- Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Craig Ringer]

On 8 September 2016 at 08:18, Tsunakawa, Takayuki
 wrote:
> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
>> Of course, if we could decrease the startup cost of a bgworker
>
>> For this use in autonomous tx's we could probably pool workers. Or at least
>> lazily terminate them so that the loop cases work better by re-using an
>> existing bgworker.
>
> I’m not sure whether to be nervous about the context switch cost in the use
> cases of autonomous transactions.

That's probably going to be one of the smaller costs. Doing this with
bgworkers won't be cheap, but you need to consider the alternative.
Factoring out all transaction-specific data currently stored in or
pointed to by globals into a transaction state struct that can be
swapped out. Making PgXact and PGPROC capable of representing
multiple/suspended transactions. Lots more. Much of which would have a
performance impact on all day to day operations whether or not
autononomous xacts are actually in use.

I haven't looked into it in detail. Peter can probably explain more
and better. I'm just pointing out that I doubt there's any way to do
this without a cost somewhere, and having that cost limited to actual
uses of autonomous xacts would be nice.

(BTW, could you please set your reply style so that your mail client
quotes text and it's possible to see what you wrote vs what is
quoted?)

-- 
-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
> Of course, if we could decrease the startup cost of a bgworker

For this use in autonomous tx's we could probably pool workers. Or at least 
lazily terminate them so that the loop cases work better by re-using an 
existing bgworker.



Though I may say something odd, isn’t the bgworker approach going to increase 
context switches?  I thought PostgreSQL has made efforts to decrease context 
switches, e.g.



* Each backend itself writes WAL to disk unlike Oracle requests LGWR process to 
write REDO to disk.



* Releasing and re-acquiring a lwlock appears to try to avoid context switches.



   /*

   * Loop here to try to acquire lock after each time we are signaled by

   * LWLockRelease.

   *

   * NOTE: it might seem better to have LWLockRelease actually grant us 
the

   * lock, rather than retrying and possibly having to go back to 
sleep. But

   * in practice that is no good because it means a process swap for 
every

   * lock acquisition when two or more processes are contending for the 
same

   * lock.  Since LWLocks are normally used to protect not-very-long

   * sections of computation, a process needs to be able to acquire and

   * release the same lock many times during a single CPU time slice, 
even

   * in the presence of contention.  The efficiency of being able to do 
that

   * outweighs the inefficiency of sometimes wasting a process dispatch

   * cycle because the lock is not free when a released waiter finally 
gets

   * to run.  See pgsql-hackers archives for 29-Dec-01.

*/



I’m not sure whether to be nervous about the context switch cost in the use 
cases of autonomous transactions.



Regards

Takayuki Tsunakawa

Re: [HACKERS] autonomous transactions

[Craig Ringer]

On 8 Sep. 2016 3:47 am, "Robert Haas"  wrote:
>

> Of course, if we could decrease the startup cost of a bgworker

For this use in autonomous tx's we could probably pool workers. Or at least
lazily terminate them so that the loop cases work better by re-using an
existing bgworker.

I'm pretty sure we're going to need a bgworker pool sooner or later anyway.

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs  wrote:
> On 2 September 2016 at 09:45, Robert Haas  wrote:
>> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
>>  wrote:
>>> I would like to propose the attached patch implementing autonomous
>>> transactions for discussion and review.
>>
>> I'm pretty skeptical of this approach.  Like Greg Stark, Serge Rielau,
>> and Constantin Pan, I had expected that autonomous transactions should
>> be implemented inside of a single backend, without relying on workers.
>
> The problem is that we have limits on the number of concurrent
> transactions, which is currently limited by the number of procs.

True.  I believe this issue has been discussed before, and I think
it's a solvable issue.  I believe that an autonomous transaction could
be made to appear to the rest of the system (outside the affected
backend) as if it were a subtransaction, but then get committed before
the containing transaction gets committed.  This would avoid needing
more PGPROCs (but getting deadlock detection to work is hard).

> So doing autonomous transactions inside a single backend doesn't gain
> you very much, yet it is an enormously invasive patch to do it that
> way, not least because it requires you to rewrite locking and
> deadlocks to make them work correctly when proc is not 1:1 with xid.
> And as Serge points out it introduces other restrictions that we know
> about now, perhaps more as well.

Yes, but I think that if we want to really meet the needs of Oracle
users who are used to being able to slap an autonomous transaction
pragma on any function that they like, we're going to need a solution
that is far lighter-weight than starting up a new backend.  Any
approach based on background workers is going to make the cost of a
function call something like 4ms, which I suspect is going to make it
useless for a pretty significant percentage of the cases where users
want to use autonomous transactions.  For example, if you want to log
every attempt to access an object, this is a phenomenally expensive
way to get there.  Calling a per-row trigger is already pretty
expensive; calling a per-row trigger that has to *launch a process for
every row* is going to be insanely bad.

>> That approach would be much less likely to run afoul of limits on the
>> number of background workers
>
> That would also be an argument against using them for parallelism, yet
> we used background workers there.

I guess that's sort of true, but parallel query intrinsically requires
multiple processes or threads, whereas autonomous transactions only
require that if you pick an implementation that requires that.  Also,
the parallel query facility is designed to only apply to operations
that are already pretty expensive, namely long-running queries, but
there are lots of use cases where an autonomous transaction gets
spawned to do a very small amount of work, and not infrequently in a
loop.  So the startup cost is a significantly more serious problem for
this use case.

Of course, if we could decrease the startup cost of a bgworker, that'd
be good for parallel query, too, because then we could deploy it on
shorter queries.  But the point remains that for parallel query the
planner can always decide to use a non-parallel plan if the query is
cheap enough that the startup cost of a worker will be a problem.
That isn't the case here; if the worker startup cost is a problem,
then you'll just end up with really slow SQL code.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Craig Ringer]

On 3 Sep. 2016 20:27, "Greg Stark"  wrote:
>

> Well using a separate process also requires rewriting locking and
> deadlock detection since a reasonable user might expect that second
> process to have access to data locked in their current transaction.

The  user is going to hit some confusing issues.

Xact1: alter table... Add column...
Xact2: inserts data with new format
Xact2: autonomous commits leaving xact1 running
Xact2: rollback

There are similar issues with FKs and and sorts. It isn't limited to schema
changes.

I don't see how autonomous xacts that inherit parent xact's uncommitted
data, locks, etc can ever really be safe. Useful, but use at your own
(considerable) risk and unlikely to be worth the issues they introduce.

If they inherit locks but don't see uncommitted data it'd be even worse.

See the autonomous commit subxact thread for more on these issues.

>
> Parallel query is currently restricted to read-only queries however.
> Autonomous transactions will certainly need to be read-write so the
> question then is what problems led to the restriction in parallel
> query and are they any more tractable with autonomous transactions?

Parallel query is fairly different because it doesn't have it's own xact.
This should mean no need to be able to wait on a separate virtualxid or
xid, no need to have their own TransactionID allocated, no complex
considerations of visibility and most importantly can't commit separately.

Re: [HACKERS] autonomous transactions

[Serge Rielau]

> On Sep 3, 2016, at 5:25 AM, Greg Stark  wrote:
> 
> On Sat, Sep 3, 2016 at 12:09 PM, Simon Riggs  wrote:
>> So doing autonomous transactions inside a single backend doesn't gain
>> you very much, yet it is an enormously invasive patch to do it that
>> way, not least because it requires you to rewrite locking and
>> deadlocks to make them work correctly when proc is not 1:1 with xid.
>> And as Serge points out it introduces other restrictions that we know
>> about now, perhaps more as well.
> 
> Well using a separate process also requires rewriting locking and
> deadlock detection since a reasonable user might expect that second
> process to have access to data locked in their current transaction.The
> plus side is that we're already facing that issue with parallel query
> so at least it's something that only has to be solved once instead of
> a new problem.
I can’t speak for reasonable users, (or persons in fact ;-)
But… previous implementations of ATs do fully expect them to deadlock on their 
parents and not see uncommitted changes.
So if one’s goal is to merely match competitors’ behavior then that part is a 
non issue.
I do not recall the single backend approach having been particularly invasive.
We managed to do a the 9.4 -> 9.5 merge with little problem despite it.

IMHO, solving the problem of passing variables to and from an AT is required 
for viability of the feature.
How else would the AT know what it’s supposed to do?
Starting an AT within a DDL transaction seems a much more narrow use case to me.

Interestingly, despite being supported in PL/SQL on nested BEGIN END blocks, 
we nearly exclusively see AT’s covering the entire function or trigger.
This usage property can be used to narrow the scope of variable passing to 
function parameters.

Cheers
Serge Rielau
salesforce.com

  

 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Greg Stark]

On Sat, Sep 3, 2016 at 12:09 PM, Simon Riggs  wrote:
> So doing autonomous transactions inside a single backend doesn't gain
> you very much, yet it is an enormously invasive patch to do it that
> way, not least because it requires you to rewrite locking and
> deadlocks to make them work correctly when proc is not 1:1 with xid.
> And as Serge points out it introduces other restrictions that we know
> about now, perhaps more as well.

Well using a separate process also requires rewriting locking and
deadlock detection since a reasonable user might expect that second
process to have access to data locked in their current transaction.The
plus side is that we're already facing that issue with parallel query
so at least it's something that only has to be solved once instead of
a new problem.

Parallel query is currently restricted to read-only queries however.
Autonomous transactions will certainly need to be read-write so the
question then is what problems led to the restriction in parallel
query and are they any more tractable with autonomous transactions?

-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Simon Riggs]

On 2 September 2016 at 09:45, Robert Haas  wrote:
> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
>  wrote:
>> I would like to propose the attached patch implementing autonomous
>> transactions for discussion and review.
>
> I'm pretty skeptical of this approach.  Like Greg Stark, Serge Rielau,
> and Constantin Pan, I had expected that autonomous transactions should
> be implemented inside of a single backend, without relying on workers.

The problem is that we have limits on the number of concurrent
transactions, which is currently limited by the number of procs.

We could expand that but given the current way we do snapshots there
will be realistically low limits however we do this.

So doing autonomous transactions inside a single backend doesn't gain
you very much, yet it is an enormously invasive patch to do it that
way, not least because it requires you to rewrite locking and
deadlocks to make them work correctly when proc is not 1:1 with xid.
And as Serge points out it introduces other restrictions that we know
about now, perhaps more as well.

Given that I've already looked into the "single backend" approach in
detail, it looks to me that Peter's approach is viable and robust.

> That approach would be much less likely to run afoul of limits on the
> number of background workers

That would also be an argument against using them for parallelism, yet
we used background workers there.

> , and it will probably perform
> considerably better too, especially when the autonomous transaction
> does only a small amount of work, like inserting a log message
> someplace.  That is not to say that providing an interface to some
> pg_background-like functionality is a bad idea; there's been enough
> interest in that from various quarters to suggest that it's actually
> quite useful, and I don't even think that it's a bad plan to integrate
> that with the PLs in some way.  However, I think that it's a different
> feature than autonomous transactions.  As others have also noted, it
> can be used to fire-and-forget a command, or to run a command while
> foreground processing continues, both of which would be out of scope
> for an autonomous transaction facility per se.  So my suggestion is
> that you pursue the work but change the name.

We agree that we like the infrastructure Peter has provided is useful,
so that part can go ahead.

If that infrastructure can be used in a simple syntactic way to
provide Autonomous Transactions, then I say let that happen. Peter,
please comment on how much infrastructure is required to implement ATs
this way?

If somebody believes there is a better way for ATs, that is just an
optimization of the limits and can occur later, if the people who
believe there is a better way can prove that is the case and come up
with a patch. It's OK for features to go in now and have better
infrastructure later, e.g. LISTEN/NOTIFY.

I would very much like to see ATs in v10 and this is a viable approach
with working code.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Jim Nasby]

On 9/2/16 3:45 AM, Robert Haas wrote:

So my suggestion is
that you pursue the work but change the name.


That might make the plpgsql issues significantly easier to deal with as 
well, by making it very explicit that you're doing something with a 
completely separate connection. That would make requiring special 
handling for passing plpgsql variables to a query much less confusing.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
 wrote:
> I would like to propose the attached patch implementing autonomous
> transactions for discussion and review.

I'm pretty skeptical of this approach.  Like Greg Stark, Serge Rielau,
and Constantin Pan, I had expected that autonomous transactions should
be implemented inside of a single backend, without relying on workers.
That approach would be much less likely to run afoul of limits on the
number of background workers, and it will probably perform
considerably better too, especially when the autonomous transaction
does only a small amount of work, like inserting a log message
someplace.  That is not to say that providing an interface to some
pg_background-like functionality is a bad idea; there's been enough
interest in that from various quarters to suggest that it's actually
quite useful, and I don't even think that it's a bad plan to integrate
that with the PLs in some way.  However, I think that it's a different
feature than autonomous transactions.  As others have also noted, it
can be used to fire-and-forget a command, or to run a command while
foreground processing continues, both of which would be out of scope
for an autonomous transaction facility per se.  So my suggestion is
that you pursue the work but change the name.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Joel Jacobson]

On Thu, Sep 1, 2016 at 8:38 PM, Andres Freund  wrote:
>
> I'm not convinced this makes much sense. All FDWs, dblink etc. already
> allow you do stuff outside of a transaction.

You as a DBA can prevent FDWs from being used and dblink is an
extension that you don't have to install.
So if preventing side-effects is necessary in your application, that
can be achieved by simply not installing dblink and preventing FDWs.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Andres Freund]

On 2016-08-31 06:10:31 +0200, Joel Jacobson wrote:
> This is important if you as a caller function want to be sure none of
> the work made by anything called down the stack gets committed.
> That is, if you as a caller decide to rollback, e.g. by raising an
> exception, and you want to be sure *everything* gets rollbacked,
> including all work by functions you've called.

> If the caller can't control this, then the author of the caller
> function would need to inspect the source code of all function being
> called, to be sure there are no code using autonomous transactions.

I'm not convinced this makes much sense. All FDWs, dblink etc. already
allow you do stuff outside of a transaction.

Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Joel Jacobson]

On Wed, Aug 31, 2016 at 6:22 PM, Simon Riggs  wrote:
> On 31 August 2016 at 14:09, Joel Jacobson  wrote:
>> My idea on how to deal with this would be to mark the function to be
>> "AUTONOMOUS" similar to how a function is marked to be "PARALLEL
>> SAFE",
>> and to throw an error if a caller that has blocked autonomous
>> transactions tries to call a function that is marked to be autonomous.
>>
>> That way none of the code that needs to be audited would ever get executed.
>
> Not sure I see why you would want to turn off execution for only some 
> functions.
>
> What happens if your function calls some other function with
> side-effects?

I'm not sure I understand your questions. All volatile functions modifying data
have side-effects. What I meant was if they are allowed to commit it
even if the caller doesn't want to.

However, I'll try to clarify the two scenarios I envision:

1. If a function is declared AUTONOMOUS and it gets called,
then that means nothing in the txn has blocked autonomous yet
and the function and any other function will be able to do autonomous txns
from that here on, so if some function would try to block autonomous that
would throw an error.

2. If a function has blocked autonomous and something later on
tries to call a function declared AUTONOMOUS then that would throw an error.

Basically, we start with a NULL state where autonomous is neither blocked
or explicitly allowed. Whatever happens first decides if autonomous transactions
will explicitly be blocked or allowed during the txn.

So we can go from NULL -> AUTONOMOUS ALLOWED
or NULL -> AUTONOMOUS BLOCKED,
but that's the only two state transitions possible.

Once set, it cannot be changed.

If nothing in an application cares about autonomous transactions,
they don't have to do anything special, they don't need to modify any
of their code.

But if it for some reason is important to block autonomous transactions
because the application is written in a way where it is expected
a RAISE EXCEPTION always rollbacks everything,
then the author of such an application (e.g. me) can just block
autonomous transactions
and continue to live happily ever after without having to dream nightmares about
developers misusing the feature, and only use it when appropriate.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Joel Jacobson]

On Thu, Sep 1, 2016 at 12:12 AM, Vik Fearing  wrote:
> Part of what people want this for is to audit what people *try* to do.
> We can already audit what they've actually done.
>
> With your solution, we still wouldn't know when an unauthorized attempt
> to do something happened.

The unauthorized attempt to execute the function will still be logged
to the PostgreSQL log file
since it would throw an error, just like trying to connect with e.g.
an invalid username would be logged to the log files.
I think that's enough for that use-case, since it's arguably not an
application layer problem,
since no part of the code was ever executed.

But if someone tries to execute a function where one of the input params
is a password and the function raises an exception if the password
is incorrect and wants to log the unauthorized attempt, then that
would be a good example of when you could use and would need to use
autonomous transactions to log the invalid password attempt.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Constantin S. Pan]

On Wed, 31 Aug 2016 14:46:30 +0100
Greg Stark  wrote:

> On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut
>  wrote:
> > - A API interface to open a "connection" to a background worker, run
> > queries, get results: AutonomousSessionStart(),
> > AutonomousSessionEnd(), AutonomousSessionExecute(), etc.  The
> > communication happens using the client/server protocol.
> 
> I'm surprised by the background worker. I had envisioned autonomous
> transactions being implemented by allowing a process to reserve a
> second entry in PGPROC with the same pid. Or perhaps save its existing
> information in a separate pgproc slot (or stack of slots) and
> restoring it after the autonomous transaction commits.
> 
> Using a background worker mean that the autonomous transaction can't
> access any state from the process memory. Parameters in plpgsql are a
> symptom of this but I suspect there will be others. What happens if a
> statement timeout occurs during an autonomous transaction? What
> happens if you use a pl language in the autonomous transaction and if
> it tries to use non-transactional information such as prepared
> statements?

I am trying to implement autonomous transactions that way. I
have already implemented suspending and restoring the parent
transaction state, at least some of it. The next thing on
the plan is the procarray/snapshot stuff. I think we should
reuse the same PGPROC for the autonomous transaction, and
allocate a stack of PGXACTs for the case of nested
autonomous transactions.

Solving the more general problem, running multiple
concurrent transactions with a single backend, may also be
interesting for some users. Autonomous transactions would
then be just a use case for that feature.

Regards,
Constantin Pan
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

Re: [HACKERS] autonomous transactions

[Vik Fearing]

On 08/31/2016 03:09 PM, Joel Jacobson wrote:
> On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova
>  wrote:
>>
>> On 30 August 2016 at 23:10, Joel Jacobson  wrote:
>>>
>>> There should be a way to within the session and/or txn permanently
>>> block autonomous transactions.
>>>
>>
>> This will defeat one of the use cases of autonomous transactions: auditing
> 
> My idea on how to deal with this would be to mark the function to be
> "AUTONOMOUS" similar to how a function is marked to be "PARALLEL
> SAFE",
> and to throw an error if a caller that has blocked autonomous
> transactions tries to call a function that is marked to be autonomous.
> 
> That way none of the code that needs to be audited would ever get executed.

Part of what people want this for is to audit what people *try* to do.
We can already audit what they've actually done.

With your solution, we still wouldn't know when an unauthorized attempt
to do something happened.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Serge Rielau]

> On Aug 31, 2016, at 6:46 AM, Greg Stark  wrote:
> 
> Using a background worker mean that the autonomous transaction can't
> access any state from the process memory. Parameters in plpgsql are a
> symptom of this but I suspect there will be others. What happens if a
> statement timeout occurs during an autonomous transaction? What
> happens if you use a pl language in the autonomous transaction and if
> it tries to use non-transactional information such as prepared
> statements?
> 
+1 on this.
The proposed solution loosely matches what was done in DB2 9.7 and it runs into 
the same 
complexity. Passing local variable or session level variables back and forth 
became a source of grief.

At SFDC PG we have taken a different tack:
1. Gather up all the transaction state that is scattered across global 
variables into a struct
2. backup/restore transaction state when an autonomous transaction is invoked.

This allows full access to all non-transactional state.

The downside is that full access also includes uncommitted DDL (shared 
recache). 
So we had to restrict DDL in the parent transaction prior to the spawning of 
the child.

If there is interest in exploring this kind of solution as an alternative I can 
elaborate.

Cheers
Serge
 



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Greg Stark]

On Wed, Aug 31, 2016 at 3:11 PM, Craig Ringer  wrote:
>
> I suspect that there'll be way too much code that relies on stashing
> xact-scoped stuff in globals for that to be viable. Caches alone.
> Peter will be able to explain more, I'm sure.
>
> We'd probably need a new transaction data object that everything
> xact-scoped hangs off, so we can pass it everywhere or swap it out of
> some global. The mechanical refactoring alone would be pretty scary,
> not to mention the complexity of actually identifying all the less
> obvious places that need changing.

Well this is the converse of the same problem. Today process state and
transaction are tied together. One way or another you're trying to
split that -- either by having two processes share state or by having
one process manage two transactions.

I suppose we already have the infrastructure for parallel query so
there's at least some shared problem space there.

-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Simon Riggs]

On 31 August 2016 at 14:09, Joel Jacobson  wrote:
> On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova
>  wrote:
>>
>> On 30 August 2016 at 23:10, Joel Jacobson  wrote:
>> >
>> > There should be a way to within the session and/or txn permanently
>> > block autonomous transactions.
>> >
>>
>> This will defeat one of the use cases of autonomous transactions: auditing
>
> My idea on how to deal with this would be to mark the function to be
> "AUTONOMOUS" similar to how a function is marked to be "PARALLEL
> SAFE",
> and to throw an error if a caller that has blocked autonomous
> transactions tries to call a function that is marked to be autonomous.
>
> That way none of the code that needs to be audited would ever get executed.

Not sure I see why you would want to turn off execution for only some functions.

What happens if your function calls some other function with
side-effects? How would you roll that back? How would you mark
functions for the general case?

Functions with side effects can't be tested with simple unit tests;
that has nothing to do with autonomous transactions.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Petr Jelinek]

On 31/08/16 16:11, Craig Ringer wrote:

On 31 August 2016 at 21:46, Greg Stark  wrote:

On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut
 wrote:

- A API interface to open a "connection" to a background worker, run
queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(),
AutonomousSessionExecute(), etc.  The communication happens using the
client/server protocol.


Peter, you mention "Oracle-style autonomous transaction blocks".

What are the semantics to be expected of those with regards to:

- Accessing objects exclusively locked by the outer xact or where the
requested lockmode conflicts with a lock held by the outer xact

- Visibility of data written by the outer xact



That would be my question as well.



Also, is it intended (outside the plpgsql interface) that the
autonomous xact can proceed concurrently/interleaved with a local
backend xact? i.e. the local backend xact isn't suspended and you're
allowed to do things on the local backend as well? If so, what
handling do you have in mind for deadlocks between the local backend
xact and the bgworker with the autonomous xact? I'd expect the local
backend to always win, killing the autonomous xact every time.



I would expect that in PLs it's handled by them, if you misuse this on C 
level that's your problem?



I'm surprised by the background worker. I had envisioned autonomous
transactions being implemented by allowing a process to reserve a
second entry in PGPROC with the same pid. Or perhaps save its existing
information in a separate pgproc slot (or stack of slots) and
restoring it after the autonomous transaction commits.


I suspect that there'll be way too much code that relies on stashing
xact-scoped stuff in globals for that to be viable. Caches alone.
Peter will be able to explain more, I'm sure.



I can also see some advantages in bgworker approach. For example it 
could be used for "fire and forget" type of interface in the future, 
where you return as soon as you send exec and don't care about waiting 
for result.


--
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Craig Ringer]

On 31 August 2016 at 21:46, Greg Stark  wrote:
> On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut
>  wrote:
>> - A API interface to open a "connection" to a background worker, run
>> queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(),
>> AutonomousSessionExecute(), etc.  The communication happens using the
>> client/server protocol.

Peter, you mention "Oracle-style autonomous transaction blocks".

What are the semantics to be expected of those with regards to:

- Accessing objects exclusively locked by the outer xact or where the
requested lockmode conflicts with a lock held by the outer xact

- Visibility of data written by the outer xact

?

Also, is it intended (outside the plpgsql interface) that the
autonomous xact can proceed concurrently/interleaved with a local
backend xact? i.e. the local backend xact isn't suspended and you're
allowed to do things on the local backend as well? If so, what
handling do you have in mind for deadlocks between the local backend
xact and the bgworker with the autonomous xact? I'd expect the local
backend to always win, killing the autonomous xact every time.

> I'm surprised by the background worker. I had envisioned autonomous
> transactions being implemented by allowing a process to reserve a
> second entry in PGPROC with the same pid. Or perhaps save its existing
> information in a separate pgproc slot (or stack of slots) and
> restoring it after the autonomous transaction commits.

I suspect that there'll be way too much code that relies on stashing
xact-scoped stuff in globals for that to be viable. Caches alone.
Peter will be able to explain more, I'm sure.

We'd probably need a new transaction data object that everything
xact-scoped hangs off, so we can pass it everywhere or swap it out of
some global. The mechanical refactoring alone would be pretty scary,
not to mention the complexity of actually identifying all the less
obvious places that need changing.

Consider invalidation callbacks. They're always "fun", and so simple
to get right

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Greg Stark]

On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut
 wrote:
> - A API interface to open a "connection" to a background worker, run
> queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(),
> AutonomousSessionExecute(), etc.  The communication happens using the
> client/server protocol.
>


I'm surprised by the background worker. I had envisioned autonomous
transactions being implemented by allowing a process to reserve a
second entry in PGPROC with the same pid. Or perhaps save its existing
information in a separate pgproc slot (or stack of slots) and
restoring it after the autonomous transaction commits.

Using a background worker mean that the autonomous transaction can't
access any state from the process memory. Parameters in plpgsql are a
symptom of this but I suspect there will be others. What happens if a
statement timeout occurs during an autonomous transaction? What
happens if you use a pl language in the autonomous transaction and if
it tries to use non-transactional information such as prepared
statements?


-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Pavel Stehule]

2016-08-31 15:09 GMT+02:00 Joel Jacobson :

> On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova
>  wrote:
> >
> > On 30 August 2016 at 23:10, Joel Jacobson  wrote:
> > >
> > > There should be a way to within the session and/or txn permanently
> > > block autonomous transactions.
> > >
> >
> > This will defeat one of the use cases of autonomous transactions:
> auditing
>
> My idea on how to deal with this would be to mark the function to be
> "AUTONOMOUS" similar to how a function is marked to be "PARALLEL
> SAFE",
> and to throw an error if a caller that has blocked autonomous
> transactions tries to call a function that is marked to be autonomous.
>
> That way none of the code that needs to be audited would ever get executed.
>

I like this idea - it allows better (cleaner) snapshot isolation.

Regards

Pavel


>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] autonomous transactions

[Joel Jacobson]

On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova
 wrote:
>
> On 30 August 2016 at 23:10, Joel Jacobson  wrote:
> >
> > There should be a way to within the session and/or txn permanently
> > block autonomous transactions.
> >
>
> This will defeat one of the use cases of autonomous transactions: auditing

My idea on how to deal with this would be to mark the function to be
"AUTONOMOUS" similar to how a function is marked to be "PARALLEL
SAFE",
and to throw an error if a caller that has blocked autonomous
transactions tries to call a function that is marked to be autonomous.

That way none of the code that needs to be audited would ever get executed.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Jaime Casanova]

On 30 August 2016 at 20:50, Peter Eisentraut
 wrote:
>
> - Patches to PL/pgSQL to implement Oracle-style autonomous transaction
> blocks:
>
> AS $$
> DECLARE
>   PRAGMA AUTONOMOUS_TRANSACTION;
> BEGIN
>   FOR i IN 0..9 LOOP
> START TRANSACTION;
> INSERT INTO test1 VALUES (i);
> IF i % 2 = 0 THEN
> COMMIT;
> ELSE
> ROLLBACK;
> END IF;
>   END LOOP;
>
>   RETURN 42;
> END;
> $$;
>

this is the syntax it will use?
i just compiled this in head and created a function based on this one.
The main difference is that the column in test1 it's a pk so i used
INSERT ON CONFLICT DO NOTHING

and i'm getting this error

postgres=# select foo();
LOG:  namespace item variable itemno 1, name val
CONTEXT:  PL/pgSQL function foo() line 7 at SQL statement
STATEMENT:  select foo();
ERROR:  null value in column "i" violates not-null constraint
DETAIL:  Failing row contains (null).
STATEMENT:  INSERT INTO test1 VALUES (val) ON CONFLICT DO NOTHING
ERROR:  null value in column "i" violates not-null constraint
DETAIL:  Failing row contains (null).
CONTEXT:  PL/pgSQL function foo() line 7 at SQL statement
STATEMENT:  select foo();
ERROR:  null value in column "i" violates not-null constraint
DETAIL:  Failing row contains (null).
CONTEXT:  PL/pgSQL function foo() line 7 at SQL statement

this happens even everytime i use the PRAGMA even if no START
TRANSACTION, COMMIT or ROLLBACK are used

-- 
Jaime Casanova  www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Jaime Casanova]

On 30 August 2016 at 23:10, Joel Jacobson  wrote:
>
> There should be a way to within the session and/or txn permanently
> block autonomous transactions.
>

This will defeat one of the use cases of autonomous transactions: auditing

>
> Coding conventions, rules and discipline are all good and will help
> against misuse of the feature, but some day someone will make a
> mistake and wrongly use the autonomous transaction and cause unwanted
> unknown side-effect I as a caller function didn't expect or know
> about.
>

well, if the feature is not guilty why do you want to put it in jail?

-- 
Jaime Casanova  www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Joel Jacobson]

I would love to see autonomous transactions in core.

I just have one major concern, but thankfully it's easily addressed.

There should be a way to within the session and/or txn permanently
block autonomous transactions.

This is important if you as a caller function want to be sure none of
the work made by anything called down the stack gets committed.
That is, if you as a caller decide to rollback, e.g. by raising an
exception, and you want to be sure *everything* gets rollbacked,
including all work by functions you've called.

If the caller can't control this, then the author of the caller
function would need to inspect the source code of all function being
called, to be sure there are no code using autonomous transactions.

Coding conventions, rules and discipline are all good and will help
against misuse of the feature, but some day someone will make a
mistake and wrongly use the autonomous transaction and cause unwanted
unknown side-effect I as a caller function didn't expect or know
about.

Once you have blocked autonomous transactions in a session or txn,
then any function called must not be able to unblock it (in the
session or txn), otherwise it defeats the purpose.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] autonomous transactions

[Peter Eisentraut]

I would like to propose the attached patch implementing autonomous
transactions for discussion and review.

This work was mostly inspired by the discussion about pg_background a
while back [0].  It seemed that most people liked the idea of having
something like that, but couldn't perhaps agree on the final interface.
Most if not all of the preliminary patches in that thread were
committed, but the user interface portions were then abandoned in favor
of other work.  (I'm aware that rebased versions of pg_background
existing.  I have one, too.)

The main use case, in a nutshell, is to be able to commit certain things
independently without having it affected by what happens later to the
current transaction, for example for audit logging.

My patch consists of three major pieces.  (I didn't make them three
separate patches because it will be clear where the boundaries are.)

- A API interface to open a "connection" to a background worker, run
queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(),
AutonomousSessionExecute(), etc.  The communication happens using the
client/server protocol.

- Patches to PL/pgSQL to implement Oracle-style autonomous transaction
blocks:

AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 0..9 LOOP
START TRANSACTION;
INSERT INTO test1 VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
  END LOOP;

  RETURN 42;
END;
$$;

This is very incomplete and has some open technical issues that I will
discuss below.  But those are all issues of PL/pgSQL, not really issues
of how autonomous sessions work.

Basically, a block that is declared with that pragma uses the autonomous
C API instead of SPI to do its things.

- Patches to PL/Python to implement a context manager for autonomous
sessions (similar to how subtransactions work there):

with plpy.autonomous() as a:
for i in range(0, 10):
a.execute("BEGIN")
a.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
if i % 2 == 0:
a.execute("COMMIT")
else:
a.execute("ROLLBACK")

This works quite well, except perhaps some tuning with memory management
and some caching and some refactoring.

While the PL/pgSQL work is more of a top-level goal, I added the
PL/Python implementation because it is easier to map the C API straight
out to something more accessible, so testing it out is much easier.


The main technical problem I had with PL/pgSQL is how to parse named
parameters.  If you're in PL/Python, say, you do

plan = a.prepare("INSERT INTO test1 (a, b) VALUES ($1, $2)",
 ["int4", "text"])

and that works fine, because it maps straight to the client/server
protocol.  But in PL/pgSQL, you will want something like

DECLARE
  x, y ...
BEGIN
  INSERT INTO test1 (a, b) VALUES (x, y)

When running in-process (SPI), we install parser hooks that allow the
parser to check back into PL/pgSQL about whether x, y are variables and
what they mean.  When we run in an autonomous session, we don't have
that available.  So my idea was to extend the protocol Parse message to
allow sending a symbol table instead of parameter types.  So instead of
saying, there are two parameters and here are their types, I would send
a list of symbols and types, and the server would respond to the Parse
message with some kind of information about which symbols it found.  I
think that would work, but I got lost in the weeds and didn't get very
far.  But you can see some of that in the code.  If anyone has other
ideas, I'd be very interested.


Other than that, I think there are also other bits and pieces that are
worth looking at, and perhaps have some overlap with other efforts, such as:

- Refining the internal APIs for running queries, with more flexibility
than SPI.  There have recently been discussions about that.  I just used
whatever was in tcop/postgres.c directly, like pg_background does, and
that seems mostly fine, but if there are other ideas, they would be
useful for this, too.

- An exception to the "mostly fine" is that the desirable handling of
log_statement, log_duration, log_min_duration_statement for
non-top-level execution is unclear.

- The autonomous session API could also be useful for other things, such
as perhaps implementing a variant of pg_background on top of them, or
doing other asynchronous or background execution schemes.  So input on
that is welcome.

- There is some overlap with the protocol handling for parallel query,
including things like error propagation, notify handling, encoding
handling.  I suspect that other background workers will need similar
facilities, so we could simplify some of that.

- Client encoding in particular was recently discussed for parallel
query.  The problem with the existing solution is that it makes
assign_client_encoding() require hardcoded knowledge of all relevant
background worker types.  So I tried a more general solution, with a hook.

- I added new 

Re: [HACKERS] autonomous transactions

[Dimitri Fontaine]

Robert Haas robertmh...@gmail.com writes:
 One thing that strikes me (maybe this is obvious) is that the
 execution of the main transaction and the autonomous transaction are
 not interleaved: it's a stack.  So in terms of globals and stuff,
 assuming you knew which things needed to be updated, you could push
 all that stuff off to the side, do whatever with the new transaction,
 and then restore all the context afterwards.

I think they call that dynamic scope, in advanced programming
language. I guess that's calling for a quote of Greenspun's Tenth Rule:

  Any sufficiently complicated C or Fortran program contains an ad hoc
  informally-specified bug-ridden slow implementation of half of Common
  Lisp.

So the name of the game could be to find out a way to implement (a
limited form of) dynamic scoping in PostgreSQL, in C, then find out all
and any backend local variable that needs that to support autonomous
transactions, then make it happen… Right?

Regards,
-- 
dim

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Robert Haas]

On Thu, Sep 16, 2010 at 5:19 AM, Dimitri Fontaine
dfonta...@hi-media.com wrote:
 Robert Haas robertmh...@gmail.com writes:
 One thing that strikes me (maybe this is obvious) is that the
 execution of the main transaction and the autonomous transaction are
 not interleaved: it's a stack.  So in terms of globals and stuff,
 assuming you knew which things needed to be updated, you could push
 all that stuff off to the side, do whatever with the new transaction,
 and then restore all the context afterwards.

 I think they call that dynamic scope, in advanced programming
 language. I guess that's calling for a quote of Greenspun's Tenth Rule:

  Any sufficiently complicated C or Fortran program contains an ad hoc
  informally-specified bug-ridden slow implementation of half of Common
  Lisp.

 So the name of the game could be to find out a way to implement (a
 limited form of) dynamic scoping in PostgreSQL, in C, then find out all
 and any backend local variable that needs that to support autonomous
 transactions, then make it happen… Right?

Interestingly, PostgreSQL was originally written in LISP, and there
are remnants of that in the code today; for example, our heavy use of
List nodes.  But I don't think that has much to do with this project.
I plan to reserve judgment on the best way of managing the relevant
state until such time as someone has gone to the trouble of
identifying what state that is.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise 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

Re: [HACKERS] autonomous transactions

[Tom Lane]

Robert Haas robertmh...@gmail.com writes:
 I plan to reserve judgment on the best way of managing the relevant
 state until such time as someone has gone to the trouble of
 identifying what state that is.

The really fundamental problem here is that you never will be able to
identify all such state.  Even assuming that you successfully completed
the herculean task of fixing the core backend, what of add-on code?

(This is also why I'm quite unimpressed with the idea of trying to
get backends to switch to a different database after startup.)

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Darren Duncan]

Robert Haas wrote:

On Thu, Sep 16, 2010 at 5:19 AM, Dimitri Fontaine dfonta...@hi-media.com 
wrote:

I think they call that dynamic scope, in advanced programming
language. I guess that's calling for a quote of Greenspun's Tenth Rule:

 Any sufficiently complicated C or Fortran program contains an ad hoc
 informally-specified bug-ridden slow implementation of half of Common
 Lisp.

So the name of the game could be to find out a way to implement (a
limited form of) dynamic scoping in PostgreSQL, in C, then find out all
and any backend local variable that needs that to support autonomous
transactions, then make it happen… Right?


Interestingly, PostgreSQL was originally written in LISP, and there
are remnants of that in the code today; for example, our heavy use of
List nodes.  But I don't think that has much to do with this project.
I plan to reserve judgment on the best way of managing the relevant
state until such time as someone has gone to the trouble of
identifying what state that is.


It would probably do Pg some good to try and recapture its functional language 
roots where reasonably possible.  I believe that, design-wise, functional 
languages really are the best way to do object-relational databases, given that 
pure functions and immutable data structures are typically the best way to 
express anything one would do with them. -- Darren Duncan


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] autonomous transactions (was Re: TODO note)

[Darren Duncan]

Robert Haas wrote:

On Wed, Sep 15, 2010 at 3:37 AM, Colin 't Hart colinth...@gmail.com wrote:

I note that the implementation of tab completion for SET TRANSACTION in PSQL
could benefit from the implementation of autonomous transactions (also
TODO).


I think it's safe to say that if we ever manage to get autonomous
transactions working, there are a GREAT MANY things which will benefit
from that.  There's probably an easier way to get at that Todo item,
though, if someone feels like beating on it.

One problem with autonomous transactions is that you have to figure
out where to store all the state associated with the autonomous
transaction and its subtransactions.  Another is that you have to
avoid an unacceptable slowdown in the tuple-visibility checks in the
process.


As I understand it, in many ways, autonomous transactions are like distinct 
database client sessions, but that the client in this case is another database 
session, especially if the autonomous transaction can make a commit that 
persists even if the initial session afterwards does a rollback.


Similarly, using autonomous transactions is akin to multi-processing.  Normal 
distinct database client sessions are like distinct processes, but usually are 
started externally to the DBMS, but autonomous transactions are like processes 
started within the DBMS.


Also, under the assumption that everything in a DBMS session should be subject 
to transactions, so that both data-manipulation and data-definition can be 
rolled back, autonomous transactions are like a generalization of supporting 
sequence generators that remember their incremented state even when the action 
that incremented it is rolled back; the sequence generator update is effectively 
an autonomous transaction, in that case.


The point being, the answer to how to implement autonomous transactions could be 
as simple as, do the same thing as how you manage multiple concurrent client 
sessions, more or less.  If each client gets its own Postgres OS process, then 
an autonomous transaction just farms out to another one of those which does the 
work.  Or maybe there could be a lighter weight version of this.


Does this design principle seem reasonable?

If autonomous transactions could be used a lot, then maybe the other process 
could be kept connected and be fed other subsequent autonomous actions, such as 
if it is being used to implement an activity log, so some kind of IPC would be 
going on.


-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions (was Re: TODO note)

[Robert Haas]

On Wed, Sep 15, 2010 at 2:32 PM, Darren Duncan dar...@darrenduncan.net wrote:
 The point being, the answer to how to implement autonomous transactions
 could be as simple as, do the same thing as how you manage multiple
 concurrent client sessions, more or less.  If each client gets its own
 Postgres OS process, then an autonomous transaction just farms out to
 another one of those which does the work.  Or maybe there could be a lighter
 weight version of this.

 Does this design principle seem reasonable?

I guess so, but the devil is in the details.  I suspect that we don't
actually want to fork a new backend for every autonomous transactions.
 That would be pretty expensive, and we already have an expensive way
of emulating this functionality using dblink.  Finding all of the bits
that think there's only one top-level transaction per backend and
generalizing them to support multiple top-level transactions per
backend doesn't sound easy, though, especially since you must do it
without losing performance.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise 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

Re: [HACKERS] autonomous transactions (was Re: TODO note)

[Darren Duncan]

Robert Haas wrote:

On Wed, Sep 15, 2010 at 2:32 PM, Darren Duncan dar...@darrenduncan.net wrote:

The point being, the answer to how to implement autonomous transactions
could be as simple as, do the same thing as how you manage multiple
concurrent client sessions, more or less.  If each client gets its own
Postgres OS process, then an autonomous transaction just farms out to
another one of those which does the work.  Or maybe there could be a lighter
weight version of this.

Does this design principle seem reasonable?


I guess so, but the devil is in the details.  I suspect that we don't
actually want to fork a new backend for every autonomous transactions.
 That would be pretty expensive, and we already have an expensive way
of emulating this functionality using dblink.  Finding all of the bits
that think there's only one top-level transaction per backend and
generalizing them to support multiple top-level transactions per
backend doesn't sound easy, though, especially since you must do it
without losing performance.


As you say, the devil is in the details, but I see this as mainly being an 
implementation issue, where an essentially same task could abstract different 
possible implementations, some more light or heavyweight.


This is loosely how I look at the issue conceptually, meaning like the illusion 
that the DBMS presents to the user:


The DBMS is a multi-process virtual machine, the database being worked on is the 
file system or disk, and uncommitted transactions are data structures in memory 
that may have multiple versions.  Each autonomous transaction is associated with 
a single process.  A process can either be started by the user (client 
connection) or by another process (autonomous transaction).  Regardless of how a 
process is started, the way to manage multiple autonomous tasks is that each has 
its own process.  Tasks that are not mutually autonomous would be within the 
same process.  Child transactions or savepoints have the same process as their 
parent when the parent can rollback their commits.


Whether the DBMS uses multiple OS threads or multiple OS processes or uses 
coroutines or whatever is an implementation detail.


A point here being that over time Postgres can evolve to use either multiple OS 
processes or multiple threads or a coroutine system within a single 
thread/process, to provide the illusion of each autonomous transaction being an 
independent process, and the data structures and algorithms for managing 
autonomous transactions can be similar to or the same as multiple client 
connections, since conceptually they are alike.


-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions (was Re: TODO note)

[Alvaro Herrera]

Excerpts from Robert Haas's message of mié sep 15 14:57:29 -0400 2010:

 I guess so, but the devil is in the details.  I suspect that we don't
 actually want to fork a new backend for every autonomous transactions.
  That would be pretty expensive, and we already have an expensive way
 of emulating this functionality using dblink.  Finding all of the bits
 that think there's only one top-level transaction per backend and
 generalizing them to support multiple top-level transactions per
 backend doesn't sound easy, though,

Yeah, and the transaction handling code is already pretty complex.

 especially since you must do it without losing performance.

Presumably we'd have fast paths for the main transaction, and
any autonomous transactions beside that one would incur in some
slowdown.

I think the complex parts are, first, figuring out what to do with
global variables that currently represent a transaction (they are
sprinkled all over the place); and second, how to represent the
autonomous transactions in shared memory without requiring the PGPROC
array to be arbitrarily resizable.

The other alternative would be to bolt the autonomous transaction
somehow in the current subtransaction stack thing and marking it in some
different way so that we can reuse the games we play with push/pop
there.  That still leaves us with the PGPROC problem.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions (was Re: TODO note)

[Robert Haas]

On Wed, Sep 15, 2010 at 6:21 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Robert Haas's message of mié sep 15 14:57:29 -0400 2010:

 I guess so, but the devil is in the details.  I suspect that we don't
 actually want to fork a new backend for every autonomous transactions.
  That would be pretty expensive, and we already have an expensive way
 of emulating this functionality using dblink.  Finding all of the bits
 that think there's only one top-level transaction per backend and
 generalizing them to support multiple top-level transactions per
 backend doesn't sound easy, though,

 Yeah, and the transaction handling code is already pretty complex.

Yep.

 especially since you must do it without losing performance.

 Presumably we'd have fast paths for the main transaction, and
 any autonomous transactions beside that one would incur in some
 slowdown.

 I think the complex parts are, first, figuring out what to do with
 global variables that currently represent a transaction (they are
 sprinkled all over the place); and second, how to represent the
 autonomous transactions in shared memory without requiring the PGPROC
 array to be arbitrarily resizable.

 The other alternative would be to bolt the autonomous transaction
 somehow in the current subtransaction stack thing and marking it in some
 different way so that we can reuse the games we play with push/pop
 there.  That still leaves us with the PGPROC problem.

I wonder if we could use/generalize pg_subtrans in some way to handle
the PGPROC problem.  I haven't thought about it much, though.

One thing that strikes me (maybe this is obvious) is that the
execution of the main transaction and the autonomous transaction are
not interleaved: it's a stack.  So in terms of globals and stuff,
assuming you knew which things needed to be updated, you could push
all that stuff off to the side, do whatever with the new transaction,
and then restore all the context afterwards.  That doesn't help in
terms of PGPROC, of course, but for backend-local state it seems
workable.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise 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

Re: [HACKERS] autonomous transactions

[Josh Berkus]

All,



Added to TODO:

* Add anonymous transactions

  http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php



IMHO, autonomous transactions should be part of a package with a 
spec-compliant CREATE PROCEDURE statement.That is, the difference 
between PROCEDURES and FUNCTIONS would be that:


-- PROCs have autonomous transactions
-- PROCs have to be excuted with CALL, and can't go in a query
-- PROCs don't necessarily return a result

--Josh Berkus

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] autonomous transactions

[Alvaro Herrera]

Josh Berkus escribió:
 All,


 Added to TODO:

 * Add anonymous transactions

   http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php

 IMHO, autonomous transactions should be part of a package with a  
 spec-compliant CREATE PROCEDURE statement.

IMHO we should try to get both things separately, otherwise we will
never get either.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] autonomous transactions

[Bruce Momjian]

Simon Riggs wrote:
 On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
  
   Agreed.  I think Pavel Stehule was doing some experiments with them, I
   don't know if he got anywhere.
  
  
  I did only first research. Any hack is possible - you can stack
  current transaction, but real implementation needs similar work like
  nested transaction :( and it is too low level for me. And some code
  cleaning is necessary. There are global variables.
  
  And there is most important question about data visibility - is
  autonomous transaction independent on main transaction (isolation)?
  You have to thing about deadlock, about reference integrity, etc. This
  task isn't simple.
 
 Yes, I think autonomous transactions should be on the TODO. They're
 useful for
 - error logging
 - auditing
 - creating new partitions automatically
 
 Plus I think we'd be able to improve the code for CREATE INDEX under
 HOT, and probably a few other wrinkly bits of code.

Added to TODO:

* Add anonymous transactions

  http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php


-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] autonomous transactions

[Bruce Momjian]

Bruce Momjian wrote:
  Plus I think we'd be able to improve the code for CREATE INDEX under
  HOT, and probably a few other wrinkly bits of code.
 
 Added to TODO:
 
 * Add anonymous transactions
 
   http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php

Sorry, updated to Add _autonomous_ transactions.  (The one time I
don't cut/paste and I get it wrong.)

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] autonomous transactions

[Decibel!]

On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  From looking at how Oracle does them, autonomous transactions are
  completely independent of the transaction that originates them -- they
  take a new database snapshot. This means that uncommitted changes in the
  originating transaction are not visible to the autonomous transaction.
 
  Oh! Recursion depth would need to be tested for as well. Nasty.
 
 Seems like the cloning-a-session idea would be a possible implementation
 path for these too.

Oracle has a feature where you can effectively save a session and return
to it. For example, if filling out a multi-page web form, you could save
state in the database between those calls. I'm assuming that they use
that capability for their autonomous transactions; save the current
session to the stack, clone it, run the autonomous transaction, then
restore the saved one.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828


pgpKD3eTOJmEA.pgp
Description: PGP signature

Re: [HACKERS] autonomous transactions

[Roberts, Jon]

 On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote:
  Simon Riggs [EMAIL PROTECTED] writes:
   From looking at how Oracle does them, autonomous transactions are
   completely independent of the transaction that originates them --
 they
   take a new database snapshot. This means that uncommitted changes
in
 the
   originating transaction are not visible to the autonomous
 transaction.
 
   Oh! Recursion depth would need to be tested for as well. Nasty.
 
  Seems like the cloning-a-session idea would be a possible
implementation
  path for these too.
 
 Oracle has a feature where you can effectively save a session and
return
 to it. For example, if filling out a multi-page web form, you could
save
 state in the database between those calls. I'm assuming that they use
 that capability for their autonomous transactions; save the current
 session to the stack, clone it, run the autonomous transaction, then
 restore the saved one.
 --

You are describing an uncommitted transaction and not an autonomous
transaction.  Transactions in Oracle are not automatically committed
like they are in PostgreSQL.

Here is a basic example of an autonomous transaction: 

create or replace procedure pr_log_error (p_error_message
errorlog.message%type) is 
  pragma autonomous_transaction;
begin
  insert
into errorlog
 (log_user,
  log_time,
  error_message)
  values (user,
  sysdate(),
  p_error_message);
  commit;
exception
  when others then
rollback;
raise;
end;
  

And then you can call it from a procedure like this:

create or replace procedure pr_example is
begin
  null;--do some work
  commit;  --commit the work
exception
  when others
pr_log_error(p_error_message = sqlerrm);
rollback;
raise;
end;

The autonomous transaction allows me to insert and commit a record in
different transaction than the calling procedure so the calling
procedure can rollback or commit.

You can also remove the commit/rollback from pr_example and instead do
it from the anonymous block that calls it.  I just added it to make it
clear that it is a different transaction than the error logging
transaction.



Jon

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] autonomous transactions

[Hans-Juergen Schoenig]

On Jan 25, 2008, at 7:27 AM, Decibel! wrote:


On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote:

Simon Riggs [EMAIL PROTECTED] writes:

From looking at how Oracle does them, autonomous transactions are
completely independent of the transaction that originates them  
-- they
take a new database snapshot. This means that uncommitted  
changes in the
originating transaction are not visible to the autonomous  
transaction.



Oh! Recursion depth would need to be tested for as well. Nasty.


Seems like the cloning-a-session idea would be a possible  
implementation

path for these too.


Oracle has a feature where you can effectively save a session and  
return
to it. For example, if filling out a multi-page web form, you could  
save

state in the database between those calls. I'm assuming that they use
that capability for their autonomous transactions; save the current
session to the stack, clone it, run the autonomous transaction, then
restore the saved one.



If you want to use it for webforms you cannot just put it on the  
stack - you had to put it in shared memory because you don't know if  
you will ever get the same database connection back from the pool.
personally i like marko's idea. if a snapshot was identified by a key  
it would be perfect. we could present the snapshots saved as a nice  
nice superuser-readable system view (similar to what we do for 2PC)


the only thing i would do is to give those snapshots some sort of  
timeout (configurable). otherwise we will get countless VACUUM  
related reports.

this sounds like a very cool feature - definitely useful.

many thanks,

hans

--
Cybertec Schönig  Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

Re: [HACKERS] autonomous transactions

[Robert Treat]

On Tuesday 22 January 2008 11:02, Roberts, Jon wrote:
 I really needed this functionality in PostgreSQL.  A common use for
 autonomous transactions is error logging.  I want to log sqlerrm in a
 function and raise an exception so the calling application knows there is
 an error and I have it logged to a table.



 I figured out a way to hack an autonomous transaction by using a dblink
 in a function and here is a simple example:




This is an enhanced version of the hack, maybe it will be of some help... 
https://labs.omniti.com/trac/pgsoltools/browser/trunk/autonomous_logging_tool

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] autonomous transactions

[Simon Riggs]

On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
 
  Agreed.  I think Pavel Stehule was doing some experiments with them, I
  don't know if he got anywhere.
 
 
 I did only first research. Any hack is possible - you can stack
 current transaction, but real implementation needs similar work like
 nested transaction :( and it is too low level for me. And some code
 cleaning is necessary. There are global variables.
 
 And there is most important question about data visibility - is
 autonomous transaction independent on main transaction (isolation)?
 You have to thing about deadlock, about reference integrity, etc. This
 task isn't simple.

Yes, I think autonomous transactions should be on the TODO. They're
useful for
- error logging
- auditing
- creating new partitions automatically

Plus I think we'd be able to improve the code for CREATE INDEX under
HOT, and probably a few other wrinkly bits of code.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] autonomous transactions

[Neil Conway]

On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
 And there is most important question about data visibility - is
 autonomous transaction independent on main transaction (isolation)?

From looking at how Oracle does them, autonomous transactions are
completely independent of the transaction that originates them -- they
take a new database snapshot. This means that uncommitted changes in the
originating transaction are not visible to the autonomous transaction.

On Wed, 2008-01-23 at 08:13 +, Simon Riggs wrote:
 Yes, I think autonomous transactions should be on the TODO. They're
 useful for
 - error logging
 - auditing
 - creating new partitions automatically

I think they would also be useful to implement procedures that perform
DDL operations or COMMITs / ROLLBACKs.

-Neil



---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] autonomous transactions

[Pavel Stehule]

On 23/01/2008, Simon Riggs [EMAIL PROTECTED] wrote:
 On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
  
   Agreed.  I think Pavel Stehule was doing some experiments with them, I
   don't know if he got anywhere.
  
 
  I did only first research. Any hack is possible - you can stack
  current transaction, but real implementation needs similar work like
  nested transaction :( and it is too low level for me. And some code
  cleaning is necessary. There are global variables.
 
  And there is most important question about data visibility - is
  autonomous transaction independent on main transaction (isolation)?
  You have to thing about deadlock, about reference integrity, etc. This
  task isn't simple.

 Yes, I think autonomous transactions should be on the TODO. They're
 useful for
 - error logging
 - auditing
 - creating new partitions automatically


I worked on workflow implementation only in stored procedures. Without
autonomous transaction you cannot implement some models. And it's
usable for AQ.

 Plus I think we'd be able to improve the code for CREATE INDEX under
 HOT, and probably a few other wrinkly bits of code.

 --
   Simon Riggs
   2ndQuadrant  http://www.2ndQuadrant.com



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] autonomous transactions

[Gregory Stark]

Neil Conway [EMAIL PROTECTED] writes:

 On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
 And there is most important question about data visibility - is
 autonomous transaction independent on main transaction (isolation)?

From looking at how Oracle does them, autonomous transactions are
 completely independent of the transaction that originates them -- they
 take a new database snapshot. This means that uncommitted changes in the
 originating transaction are not visible to the autonomous transaction.

I think the hard part would be error handling. You have to be able to catch
any errors and resume the outer transaction.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] autonomous transactions

[Gokulakannan Somasundaram]

 I think the hard part would be error handling. You have to be able to
 catch
 any errors and resume the outer transaction.


I think this is not right. Autonomous transactions are used as soon as you
catch a error in order to log them. It can be used even for auditing. But
resuming the outer transaction etc should not be on the plate of autonomous
transactions. I am making an example here ...

Suppose you want to write a code which captures the attempt to change the
sensitive information, and also fails the change made to sensitive
information. In order to fail the change, we might need to rollback the
transaction, which would prevent the attempt being logged. So if we have
autonomous audit transaction, it will commit irrespective of the rollback
which happened to the original transaction

The Audit transaction, which is a autonomous transaction need not catch any
error and resume the outer transaction.

Thanks,
Gokul.

Re: [HACKERS] autonomous transactions

[Simon Riggs]

On Wed, 2008-01-23 at 00:26 -0800, Neil Conway wrote:
 On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
  And there is most important question about data visibility - is
  autonomous transaction independent on main transaction (isolation)?
 
 From looking at how Oracle does them, autonomous transactions are
 completely independent of the transaction that originates them -- they
 take a new database snapshot. This means that uncommitted changes in the
 originating transaction are not visible to the autonomous transaction.

Oh! Recursion depth would need to be tested for as well. Nasty.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] autonomous transactions

[Alvaro Herrera]

Gokulakannan Somasundaram escribió:

 The Audit transaction, which is a autonomous transaction need not catch any
 error and resume the outer transaction.

What if the logging fails, say because you forgot to create the audit
table?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] autonomous transactions

[Neil Conway]

On Wed, 2008-01-23 at 09:30 +, Gregory Stark wrote:
 I think the hard part would be error handling. You have to be able to catch
 any errors and resume the outer transaction.

I agree that you'd need to do this, but I don't follow why it would be
particularly difficult. You essentially have a stack of active
transactions (since one autonomous transaction can start another
autonomous transaction, and so forth). If you encounter an error in the
current transaction, you abort it as normal, pop the stack, and resume
execution of the originating transaction.

I think the hard part is fixing the parts of the backend that assume
that a single process can only have a single top-level transaction in
progress at a given time.

-Neil



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] autonomous transactions

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 From looking at how Oracle does them, autonomous transactions are
 completely independent of the transaction that originates them -- they
 take a new database snapshot. This means that uncommitted changes in the
 originating transaction are not visible to the autonomous transaction.

 Oh! Recursion depth would need to be tested for as well. Nasty.

Seems like the cloning-a-session idea would be a possible implementation
path for these too.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] autonomous transactions

[Gokulakannan Somasundaram]

On Jan 24, 2008 2:46 AM, Alvaro Herrera [EMAIL PROTECTED] wrote:

 Gokulakannan Somasundaram escribió:

  The Audit transaction, which is a autonomous transaction need not catch
 any
  error and resume the outer transaction.

 What if the logging fails, say because you forgot to create the audit
 table?

I get it now...


 --
 Alvaro Herrera
 http://www.CommandPrompt.com/
 The PostgreSQL Company - Command Prompt, Inc.

Re: [HACKERS] autonomous transactions

[Jonah H. Harris]

On Jan 23, 2008 10:06 PM, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote:
 On Jan 24, 2008 2:46 AM, Alvaro Herrera [EMAIL PROTECTED] wrote:
   The Audit transaction, which is a autonomous transaction need not catch
 any
   error and resume the outer transaction.
 
  What if the logging fails, say because you forgot to create the audit
  table?
 
 I get it now...

Autonomous transactions are, umm, autonomous.  The calling transaction
doesn't know about or care whether the autonomous transaction succeeds
or fails for any reason.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation| fax: 732.331.1301
499 Thornall Street, 2nd Floor  | [EMAIL PROTECTED]
Edison, NJ 08837| http://www.enterprisedb.com/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[HACKERS] autonomous transactions

[Roberts, Jon]

I really needed this functionality in PostgreSQL.  A common use for
autonomous transactions is error logging.  I want to log sqlerrm in a
function and raise an exception so the calling application knows there is an
error and I have it logged to a table.  

 

I figured out a way to hack an autonomous transaction by using a dblink in
a function and here is a simple example:

 

create or replace function fn_log_error(p_function varchar, p_location int,
p_error varchar) returns void as

$$

declare

  v_sql varchar;

  v_return varchar;

  v_error varchar;

begin

  perform dblink_connect('connection_name', 'dbname=...');

  

  v_sql := 'insert into error_log (function_name, location, error_message,
error_time) values (''' || p_function_name || ''', ' || 

   p_location || ', ''' || p_error || ''', clock_timestamp())';

  

  select * from dblink_exec('connection_name', v_sql, false) into v_return;

 

  --get the error message

  select * from dblink_error_message('connection_name') into v_error;

 

  if position('ERROR' in v_error)  0 or position('WARNING' in v_error)  0
then

raise exception '%', v_error;

  end if;

 

  perform dblink_disconnect('connection_name');

 

exception

  when others then

perform dblink_disconnect('connection_name');

raise exception '(%)', sqlerrm;

end;

$$

language 'plpgsql' security definer;

 

I thought I would share and it works rather well.  Maybe someone could
enhance this concept to include it with the core database to provide
autonomous transactions.

 

 

Jon

Re: [HACKERS] autonomous transactions

[Neil Conway]

On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote:
 Maybe someone could enhance this concept to include it with the core
 database to provide autonomous transactions.

I agree that autonomous transactions would be useful, but doing them via
dblink is a kludge. If we're going to include anything in the core
database, it should be done properly (i.e. as an extension to the
existing transaction system).

-Neil



---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] autonomous transactions

[Alvaro Herrera]

Neil Conway wrote:
 On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote:
  Maybe someone could enhance this concept to include it with the core
  database to provide autonomous transactions.
 
 I agree that autonomous transactions would be useful, but doing them via
 dblink is a kludge. If we're going to include anything in the core
 database, it should be done properly (i.e. as an extension to the
 existing transaction system).

Agreed.  I think Pavel Stehule was doing some experiments with them, I
don't know if he got anywhere.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] autonomous transactions

[Roberts, Jon]

 On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote:
  Maybe someone could enhance this concept to include it with the core
  database to provide autonomous transactions.
 
 I agree that autonomous transactions would be useful, but doing them via
 dblink is a kludge. 

Kludge or hack but I agree!

 If we're going to include anything in the core
 database, it should be done properly (i.e. as an extension to the
 existing transaction system).

I agree!  That is why I said someone could enhance this concept to include
it with the core database.  




Jon

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] autonomous transactions

[Pavel Stehule]

 Agreed.  I think Pavel Stehule was doing some experiments with them, I
 don't know if he got anywhere.


I did only first research. Any hack is possible - you can stack
current transaction, but real implementation needs similar work like
nested transaction :( and it is too low level for me. And some code
cleaning is necessary. There are global variables.

And there is most important question about data visibility - is
autonomous transaction independent on main transaction (isolation)?
You have to thing about deadlock, about reference integrity, etc. This
task isn't simple.

Pavel



 --
 Alvaro Herrerahttp://www.CommandPrompt.com/
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support

 ---(end of broadcast)---
 TIP 7: You can help support the PostgreSQL project by donating at

 http://www.postgresql.org/about/donate


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster