Re: [HACKERS] Autonomous Transaction is back

[Noah Misch]

On Fri, Sep 11, 2015 at 02:30:53PM -0500, Merlin Moncure wrote:
> On Thu, Sep 10, 2015 at 8:39 PM, Noah Misch  wrote:
> > It's an exceptionally-challenging development project, agreed.  So much code
> > assumes the 1:1 relationship between backends and top-level transactions.
> 
> I guess I'm being obtuse, but can you explain why that assumption must
> be revisited?

It's not imperative, but the proposal on the table does so.  Robert described
doing so as "a key design goal for this feature"[1].  I have a general picture
of why the proposers chose that, but I will defer to them for any elaboration.

[1] 
http://www.postgresql.org/message-id/ca+tgmoze__kohfuyvzpxvtjsb85xm34kc_t41-oxhtb_111...@mail.gmail.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 Transaction is back

[Merlin Moncure]

On Thu, Sep 10, 2015 at 8:39 PM, Noah Misch  wrote:
> On Wed, Sep 09, 2015 at 10:04:01AM -0400, Robert Haas wrote:
>> On Sun, Sep 6, 2015 at 1:56 AM, Noah Misch  wrote:
>> > What design principle(s) have you been using to decide how autonomous
>> > transactions should behave?
>>
>> I have to admit to a complete lack of principle.  I'm quite frightened
>> of what this is going to need from the lock manager, and I'm trying to
>> wriggle out of having to do things there that are going to be nastily
>> hard.  My wriggling isn't going very well, though.
>
> It's an exceptionally-challenging development project, agreed.  So much code
> assumes the 1:1 relationship between backends and top-level transactions.

I guess I'm being obtuse, but can you explain why that assumption must
be revisited?  I don't see why it has to be...I must be missing
something.

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 Transaction is back

[Noah Misch]

On Wed, Sep 09, 2015 at 10:04:01AM -0400, Robert Haas wrote:
> On Sun, Sep 6, 2015 at 1:56 AM, Noah Misch  wrote:
> > What design principle(s) have you been using to decide how autonomous
> > transactions should behave?
> 
> I have to admit to a complete lack of principle.  I'm quite frightened
> of what this is going to need from the lock manager, and I'm trying to
> wriggle out of having to do things there that are going to be nastily
> hard.  My wriggling isn't going very well, though.

It's an exceptionally-challenging development project, agreed.  So much code
assumes the 1:1 relationship between backends and top-level transactions.


-- 
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 Transaction is back

[Merlin Moncure]

On Wed, Sep 9, 2015 at 9:04 AM, Robert Haas  wrote:
> On Sun, Sep 6, 2015 at 1:56 AM, Noah Misch  wrote:
>> What design principle(s) have you been using to decide how autonomous
>> transactions should behave?
>
> I have to admit to a complete lack of principle.  I'm quite frightened
> of what this is going to need from the lock manager, and I'm trying to
> wriggle out of having to do things there that are going to be nastily
> hard.  My wriggling isn't going very well, though.

Hm.  Here is current dblink behavior:

postgres=# create table l (id int);
CREATE TABLE
postgres=# insert into l values(1);
INSERT 0 1
postgres=# update l set id =2 where id = 1;
UPDATE 1
Time: 0.595 ms
postgres=# select dblink('', 'update l set id = 3 where id = 1');


Does the lock manager really needs to be extended to address this
case?  pg_locks pretty clearly explains what's happening, via:
postgres=# select locktype, transactionid, pid, granted from pg_locks
where not granted;
   locktype│ transactionid │  pid  │ granted
───┼───┼───┼─
 transactionid │ 88380 │ 20543 │ f

and

postgres=# select locktype, transactionid, pid, granted from pg_locks
where transactionid = 88380;
   locktype│ transactionid │  pid  │ granted
───┼───┼───┼─
 transactionid │ 88380 │ 20543 │ f
 transactionid │ 88380 │ 19022 │ t

If pg_locks and/or pg_stat_activity were extended with a 'parent_pid'
column, a userland query could terminate affected backends with a join
against that column where any ungranted.  Naturally there's a lot more
to it than that; you'd want to issue an appropriate cancellation
message and various other things.  I suppose I'm probably missing
something, but I'm not clear on why the lock manager needs to be
overhauled to deal with this case when we can just scan current
strictures assuming we can a) manage child pid independently of parent
pid and b) easily figure out who is parent of who.

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 Transaction is back

[Robert Haas]

On Sun, Sep 6, 2015 at 1:56 AM, Noah Misch  wrote:
> What design principle(s) have you been using to decide how autonomous
> transactions should behave?

I have to admit to a complete lack of principle.  I'm quite frightened
of what this is going to need from the lock manager, and I'm trying to
wriggle out of having to do things there that are going to be nastily
hard.  My wriggling isn't going very well, though.

-- 
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 Transaction is back

[Merlin Moncure]

On Sun, Sep 6, 2015 at 12:56 AM, Noah Misch  wrote:
> My comments have flowed out of a principle that autonomous transactions shall
> have precisely the same semantics as using another backend via dblink.

That's what I thought, too.  AT is syntax sugar for dblink approach.
Syntax and performance aside, I think the only way dblink style AT
could be improved would be to have:

1) better cancel handling, especially cancel child when parent cancels
(this is a major headache with dblink)
2) automatic rejection of acquire attempts by child on exclusive
assets held by parent

#2 is what we've been discussing, but I'm pretty confused.  Most
especially I'm not clear on whether parent and children share a pid or
have unique pid.  If pids are unique as in dblink, #2 could be done
via a query assuming you have a way of identifying child transactions
in pg_stat_activity.  The discussion has suggested that the pid is
shared, which I found odd but took on faith.

I'm also wondering if we need syntax to handle synchronous vs
asynchronous execution of the AT (the latter of which is only possible
with a separate PID, I think).

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 Transaction is back

[Noah Misch]

On Thu, Sep 03, 2015 at 04:21:55PM -0400, Robert Haas wrote:
> On Sat, Aug 22, 2015 at 2:23 AM, Noah Misch  wrote:
> >> > Can you get away with only looking at tuples though?  For example,
> >> > what about advisory locks?  Table locks?
> >>
> >> Well, that's an interesting question.  Can we get away with regarding
> >> those things as non-conflicting, as between the parent and child
> >> transactions?
> >
> > For system lock types, no.  While one could define advisory locks to work
> > differently, we should assume that today's advisory lockers have 
> > expectations
> > like those of system lockers.  An autonomous transaction should not bypass 
> > any
> > lock that a transaction of another backend could not bypass.
> 
> Why?
> 
> Suppose you do this:
> 
> BEGIN;
> DECLARE CURSOR foo FOR SELECT * FROM foo;
> BEGIN AUTONOMOUS TRANSACTION;
> ALTER TABLE foo ALTER bar TYPE int;
> 
> This has got to fail for safety reasons, but CheckTableNotInUse() is
> on it.  Suppose you do this:
>
> BEGIN;
> LOCK foo;
> BEGIN AUTONOMOUS TRANSACTION;
> INSERT INTO foo VALUES ('spelunk');
> 
> How will making this fail improve anything?

Core PostgreSQL doesn't care.  This is a user interface design decision to be
made in light of current SQL expectations and future SQL author wishes.  The
LOCK reference page, our contract with users, says nothing to constrain the
choice.  LOCK is exceptional in that we never get much insight into the
caller's expectations.  I think LOCK should follow closely the built-in
commands that take the same locks.  This variation of your examples must fail
in order to avoid a paradox if the first transaction aborts:

BEGIN;
ALTER TABLE foo ALTER bar TYPE frob;
BEGIN AUTONOMOUS TRANSACTION;
INSERT INTO foo VALUES ('spelunk');

If we made that fail and made your second example succeed, that would imply
"LOCK foo" acquires a special kind of AccessExclusiveLock differing from what
ALTER TABLE acquires.  That's incompatible with my sense of the LOCK command's
role in the system.  An ability to procure an option to acquire, without
waiting, a lock and delegate that option to another transaction would have
applications.  It's a different feature calling for distinct syntax.


My comments have flowed out of a principle that autonomous transactions shall
have precisely the same semantics as using another backend via dblink.  They
should have less overhead.  They may give different error messages.  They
shall not permit sequences of commands that fail in a dblink implementation of
the same multi-transaction sequence.  I chose this principle because it fits
my intuitive notion of transaction "autonomy" and because everything I've
heard about other implementations suggests that they work in that way.  If
some RDBMS implementation does otherwise, I would find that persuasive.

What design principle(s) have you been using to decide how autonomous
transactions should behave?


-- 
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 Transaction is back

[Robert Haas]

On Sat, Aug 22, 2015 at 2:23 AM, Noah Misch  wrote:
>> > Can you get away with only looking at tuples though?  For example,
>> > what about advisory locks?  Table locks?
>>
>> Well, that's an interesting question.  Can we get away with regarding
>> those things as non-conflicting, as between the parent and child
>> transactions?
>
> For system lock types, no.  While one could define advisory locks to work
> differently, we should assume that today's advisory lockers have expectations
> like those of system lockers.  An autonomous transaction should not bypass any
> lock that a transaction of another backend could not bypass.

Why?

Suppose you do this:

BEGIN;
DECLARE CURSOR foo FOR SELECT * FROM foo;
BEGIN AUTONOMOUS TRANSACTION;
ALTER TABLE foo ALTER bar TYPE int;

This has got to fail for safety reasons, but CheckTableNotInUse() is
on it.  Suppose you do this:

BEGIN;
LOCK foo;
BEGIN AUTONOMOUS TRANSACTION;
INSERT INTO foo VALUES ('spelunk');

How will making this fail improve anything?

-- 
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 Transaction is back

[Noah Misch]

On Fri, Aug 21, 2015 at 10:06:44AM -0400, Robert Haas wrote:
> On Tue, Aug 18, 2015 at 3:06 PM, Merlin Moncure  wrote:
> > On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas  wrote:
> >> On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch  wrote:
> >>> [1] That's not to say it must use the shmem lock structures and deadlock
> >>> detector.
> >>
> >> This footnote goes to my point.

> >> So, I agree that this scenario should be an error.  What I don't agree
> >> with is the idea that it should be the deadlock detector's job to
> >> throw that error.

I couldn't gather from your earlier messages that this scenario should get an
error, so I'm glad to have that clarified.

> > Can you get away with only looking at tuples though?  For example,
> > what about advisory locks?  Table locks?
> 
> Well, that's an interesting question.  Can we get away with regarding
> those things as non-conflicting, as between the parent and child
> transactions?

For system lock types, no.  While one could define advisory locks to work
differently, we should assume that today's advisory lockers have expectations
like those of system lockers.  An autonomous transaction should not bypass any
lock that a transaction of another backend could not bypass.


-- 
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 Transaction is back

[Robert Haas]

On Tue, Aug 18, 2015 at 3:06 PM, Merlin Moncure  wrote:
> On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas  wrote:
>> On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch  wrote:
>>> CREATE TABLE t (c) AS SELECT 1;
>>> BEGIN;
>>> UPDATE t SET c = 2 WHERE c = 1;
>>> BEGIN_AUTONOMOUS;
>>> UPDATE t SET c = 3 WHERE c = 1;
>>> UPDATE t SET c = 4 WHERE c = 2;
>>> COMMIT_AUTONOMOUS;
>>> ROLLBACK;
>>>
>>> If you replace the autonomous transaction with a savepoint, the c=3 update
>>> finds no rows, and the c=4 update changes one row.  When the outer 
>>> transaction
>>> aborts, only the original c=1 row remains live.  If you replace the 
>>> autonomous
>>> transaction with a dblink/pg_background call, the c=3 update waits
>>> indefinitely for c=2 to commit or abort, an undetected deadlock.
>>>
>>> Suppose you make the autonomous transaction see tuples like in the savepoint
>>> case.  The c=3 update finds no rows to update, and the c=4 update changes 
>>> one
>>> row.  When the outer transaction aborts, you have two live rows (c=1 and 
>>> c=4).
>>> Suppose you instead make the autonomous transaction see tuples like in the
>>> dblink case, yet let c=3 ignore the lock and change a row.  If both the
>>> autonomous transaction and the outer transaction were to commit, then you 
>>> get
>>> two live rows (c=2 and c=3).  Neither of those outcomes is acceptable, of
>>> course.  In today's tuple update rules, c=3 must deadlock[1].  Other 
>>> credible
>>> tuple update rules may not have this problem, but nothing jumps to mind.
>>>
>>> [1] That's not to say it must use the shmem lock structures and deadlock
>>> detector.
>>
>> This footnote goes to my point.
>>
>> It seems clear to me that having the autonomous transaction "see" the
>> effects of the outer uncommitted transaction is a recipe for trouble.
>> If the autonomous transaction updates a row and commits, and the outer
>> transaction later aborts, the resulting state is inconsistent with any
>> serial history.  I'm fairly certain that's going to leave us in an
>> unhappy place.
>>
>> Even more obviously, ending up with two committed row versions that
>> are both updates of a single ancestor version is no good.
>>
>> So, I agree that this scenario should be an error.  What I don't agree
>> with is the idea that it should be the deadlock detector's job to
>> throw that error.  Rather, I think that when we examine the xmax of
>> the tuple we can see - which is the original one, not the one updated
>> by the outer transaction - we should check whether that XID belongs to
>> an outer transaction.
>
> Hm: do you mean 'an' outer transaction (meaning, basically, any in
> progress transaction) or the outer transaction of the AT.  I think you
> mean outer transaction of the AT, which makes a lot of sense and
> should be easy and fast to test.  It's like an implied NOWAIT if the
> locker is the AT and the lockee is the parent.
>
> Can you get away with only looking at tuples though?  For example,
> what about advisory locks?  Table locks?

Well, that's an interesting question.  Can we get away with regarding
those things as non-conflicting, as between the parent and child
transactions?

If we want to insist that they conflict, the easy case is easy: when
acquiring a lock, we can consult the local lock table hash and see if
an outer transaction already holds such a lock.  If so, we error out.
The problem is when somebody else is waiting for the lock we hold.
That gets complicated, because the deadlock detector can't work out
that we're deadlocking with ourselves unless there are two separate
PGPROCs, one for the inner transaction and one for the outer
transaction, and even then it might think it can rearrange the lock
queue so that the outer transaction goes first, which in fact won't
work here.

I'm not exactly sure how to make this work out without involving the
deadlock detector, but I think we should try hard to get there.

-- 
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 Transaction is back

[Rajeev rastogi]

On 18 August 2015 21:18, Robert Haas Wrote:

>This footnote goes to my point.
>
>It seems clear to me that having the autonomous transaction "see" the
>effects of the outer uncommitted transaction is a recipe for trouble.
>If the autonomous transaction updates a row and commits, and the outer
>transaction later aborts, the resulting state is inconsistent with any
>serial history.  I'm fairly certain that's going to leave us in an
>unhappy place.
>
>Even more obviously, ending up with two committed row versions that are
>both updates of a single ancestor version is no good.
>
>So, I agree that this scenario should be an error.  What I don't agree
>with is the idea that it should be the deadlock detector's job to throw
>that error.  Rather, I think that when we examine the xmax of the tuple
>we can see - which is the original one, not the one updated by the outer
>transaction - we should check whether that XID belongs to an outer
>transaction.  If it does, we should throw an error instead of trying to
>lock it.  That way (1) the error message will be clear and specific to
>the situation and (2) we don't need a separate PGPROC for each
>autonomous transaction.  The first of those benefits is agreeable; the
>second one is, in my opinion, a key design goal for this feature.

Yes I agree with this. I was in favor of error all the time without involving 
deadlock detector. 

Thanks and Regards,
Kumar Rajeev Rastogi


-- 
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 Transaction is back

[Merlin Moncure]

On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas  wrote:
> On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch  wrote:
>> CREATE TABLE t (c) AS SELECT 1;
>> BEGIN;
>> UPDATE t SET c = 2 WHERE c = 1;
>> BEGIN_AUTONOMOUS;
>> UPDATE t SET c = 3 WHERE c = 1;
>> UPDATE t SET c = 4 WHERE c = 2;
>> COMMIT_AUTONOMOUS;
>> ROLLBACK;
>>
>> If you replace the autonomous transaction with a savepoint, the c=3 update
>> finds no rows, and the c=4 update changes one row.  When the outer 
>> transaction
>> aborts, only the original c=1 row remains live.  If you replace the 
>> autonomous
>> transaction with a dblink/pg_background call, the c=3 update waits
>> indefinitely for c=2 to commit or abort, an undetected deadlock.
>>
>> Suppose you make the autonomous transaction see tuples like in the savepoint
>> case.  The c=3 update finds no rows to update, and the c=4 update changes one
>> row.  When the outer transaction aborts, you have two live rows (c=1 and 
>> c=4).
>> Suppose you instead make the autonomous transaction see tuples like in the
>> dblink case, yet let c=3 ignore the lock and change a row.  If both the
>> autonomous transaction and the outer transaction were to commit, then you get
>> two live rows (c=2 and c=3).  Neither of those outcomes is acceptable, of
>> course.  In today's tuple update rules, c=3 must deadlock[1].  Other credible
>> tuple update rules may not have this problem, but nothing jumps to mind.
>>
>> [1] That's not to say it must use the shmem lock structures and deadlock
>> detector.
>
> This footnote goes to my point.
>
> It seems clear to me that having the autonomous transaction "see" the
> effects of the outer uncommitted transaction is a recipe for trouble.
> If the autonomous transaction updates a row and commits, and the outer
> transaction later aborts, the resulting state is inconsistent with any
> serial history.  I'm fairly certain that's going to leave us in an
> unhappy place.
>
> Even more obviously, ending up with two committed row versions that
> are both updates of a single ancestor version is no good.
>
> So, I agree that this scenario should be an error.  What I don't agree
> with is the idea that it should be the deadlock detector's job to
> throw that error.  Rather, I think that when we examine the xmax of
> the tuple we can see - which is the original one, not the one updated
> by the outer transaction - we should check whether that XID belongs to
> an outer transaction.

Hm: do you mean 'an' outer transaction (meaning, basically, any in
progress transaction) or the outer transaction of the AT.  I think you
mean outer transaction of the AT, which makes a lot of sense and
should be easy and fast to test.  It's like an implied NOWAIT if the
locker is the AT and the lockee is the parent.

Can you get away with only looking at tuples though?  For example,
what about advisory locks?  Table locks?


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 Transaction is back

[Robert Haas]

On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch  wrote:
> CREATE TABLE t (c) AS SELECT 1;
> BEGIN;
> UPDATE t SET c = 2 WHERE c = 1;
> BEGIN_AUTONOMOUS;
> UPDATE t SET c = 3 WHERE c = 1;
> UPDATE t SET c = 4 WHERE c = 2;
> COMMIT_AUTONOMOUS;
> ROLLBACK;
>
> If you replace the autonomous transaction with a savepoint, the c=3 update
> finds no rows, and the c=4 update changes one row.  When the outer transaction
> aborts, only the original c=1 row remains live.  If you replace the autonomous
> transaction with a dblink/pg_background call, the c=3 update waits
> indefinitely for c=2 to commit or abort, an undetected deadlock.
>
> Suppose you make the autonomous transaction see tuples like in the savepoint
> case.  The c=3 update finds no rows to update, and the c=4 update changes one
> row.  When the outer transaction aborts, you have two live rows (c=1 and c=4).
> Suppose you instead make the autonomous transaction see tuples like in the
> dblink case, yet let c=3 ignore the lock and change a row.  If both the
> autonomous transaction and the outer transaction were to commit, then you get
> two live rows (c=2 and c=3).  Neither of those outcomes is acceptable, of
> course.  In today's tuple update rules, c=3 must deadlock[1].  Other credible
> tuple update rules may not have this problem, but nothing jumps to mind.
>
> [1] That's not to say it must use the shmem lock structures and deadlock
> detector.

This footnote goes to my point.

It seems clear to me that having the autonomous transaction "see" the
effects of the outer uncommitted transaction is a recipe for trouble.
If the autonomous transaction updates a row and commits, and the outer
transaction later aborts, the resulting state is inconsistent with any
serial history.  I'm fairly certain that's going to leave us in an
unhappy place.

Even more obviously, ending up with two committed row versions that
are both updates of a single ancestor version is no good.

So, I agree that this scenario should be an error.  What I don't agree
with is the idea that it should be the deadlock detector's job to
throw that error.  Rather, I think that when we examine the xmax of
the tuple we can see - which is the original one, not the one updated
by the outer transaction - we should check whether that XID belongs to
an outer transaction.  If it does, we should throw an error instead of
trying to lock it.  That way (1) the error message will be clear and
specific to the situation and (2) we don't need a separate PGPROC for
each autonomous transaction.  The first of those benefits is
agreeable; the second one is, in my opinion, a key design goal for
this feature.

-- 
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 Transaction is back

[Albe Laurenz]

Noah Misch wrote:
> > > If the autonomous transaction can interact with uncommitted
> > > work in a way that other backends could not, crazy things happen when the
> > > autonomous transaction commits and the suspended transaction aborts:
> > >
> > > CREATE TABLE t (c) AS SELECT 1;
> > > BEGIN;
> > > UPDATE t SET c = 2 WHERE c = 1;
> > > BEGIN_AUTONOMOUS;
> > > UPDATE t SET c = 3 WHERE c = 1;
> > > UPDATE t SET c = 4 WHERE c = 2;
> > > COMMIT_AUTONOMOUS;
> > > ROLLBACK;
> > >
> > > If you replace the autonomous transaction with a savepoint, the c=3 update
> > > finds no rows, and the c=4 update changes one row.  When the outer 
> > > transaction
> > > aborts, only the original c=1 row remains live.  If you replace the 
> > > autonomous
> > > transaction with a dblink/pg_background call, the c=3 update waits
> > > indefinitely for c=2 to commit or abort, an undetected deadlock.

> My starting expectation is that the semantics of an autonomous transaction
> will be exactly those of dblink/pg_background.  (I said that during the
> unconference session.)  The application would need to read data from tables
> before switching to the autonomous section.  Autonomous transactions are then
> a performance and syntactic help, not a source of new semantics.  Does any
> database have autonomous transactions that do otherwise?

Oracle behaves like that, i.e. it deadlocks with your example:

SQL> SELECT * FROM t;

 C
--
 1

SQL> CREATE PROCEDURE proc2 IS
  2  PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4  UPDATE t SET c = 3 WHERE c = 1;
  5  UPDATE t SET c = 4 WHERE c = 2;
  6  COMMIT;
  7  END;
  8  /

Procedure created.

SQL> CREATE PROCEDURE proc1 IS
  2  BEGIN
  3  UPDATE t SET c = 2 WHERE c = 1;
  4  proc2;
  5  ROLLBACK;
  6  END;
  7  /

Procedure created.

SQL> CALL proc1();
CALL proc1()
 *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "LAURENZ.PROC2", line 4
ORA-06512: at "LAURENZ.PROC1", line 4

Yours,
Laurenz Albe

-- 
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 Transaction is back

[Noah Misch]

On Sat, Aug 15, 2015 at 10:20:55PM -0300, Alvaro Herrera wrote:
> Noah Misch wrote:
> 
> > In today's scenarios, the later query cannot commit unless the suspended 
> > query
> > also commits.  (Changing that is the raison d'être of autonomous
> > transactions.)  If the autonomous transaction can interact with uncommitted
> > work in a way that other backends could not, crazy things happen when the
> > autonomous transaction commits and the suspended transaction aborts:
> > 
> > CREATE TABLE t (c) AS SELECT 1;
> > BEGIN;
> > UPDATE t SET c = 2 WHERE c = 1;
> > BEGIN_AUTONOMOUS;
> > UPDATE t SET c = 3 WHERE c = 1;
> > UPDATE t SET c = 4 WHERE c = 2;
> > COMMIT_AUTONOMOUS;
> > ROLLBACK;
> > 
> > If you replace the autonomous transaction with a savepoint, the c=3 update
> > finds no rows, and the c=4 update changes one row.  When the outer 
> > transaction
> > aborts, only the original c=1 row remains live.  If you replace the 
> > autonomous
> > transaction with a dblink/pg_background call, the c=3 update waits
> > indefinitely for c=2 to commit or abort, an undetected deadlock.
> 
> Maybe what we need to solve this is to restrict what the autonomous
> transaction can do; for instance, make it so that the autonomous
> transaction can see all rows of the outer transaction as if the outer
> transaction were committed, but trying to update any such row raises an
> error.  As far as I can see, this closes this particular problem.  (We
> likely need additional rules to close all holes, but hopefully you get
> the idea.)
> 
> Perhaps there exists a set of rules strong enough to eliminate all
> problematic visibility scenarios, but which still enables behavior
> useful enough to cover the proposed use cases.  The audit scenario is
> covered because the audit trail doesn't need to modify the audited
> tuples themselves, only read them.

My starting expectation is that the semantics of an autonomous transaction
will be exactly those of dblink/pg_background.  (I said that during the
unconference session.)  The application would need to read data from tables
before switching to the autonomous section.  Autonomous transactions are then
a performance and syntactic help, not a source of new semantics.  Does any
database have autonomous transactions that do otherwise?


-- 
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 Transaction is back

[Alvaro Herrera]

Noah Misch wrote:

> In today's scenarios, the later query cannot commit unless the suspended query
> also commits.  (Changing that is the raison d'être of autonomous
> transactions.)  If the autonomous transaction can interact with uncommitted
> work in a way that other backends could not, crazy things happen when the
> autonomous transaction commits and the suspended transaction aborts:
> 
> CREATE TABLE t (c) AS SELECT 1;
> BEGIN;
> UPDATE t SET c = 2 WHERE c = 1;
> BEGIN_AUTONOMOUS;
> UPDATE t SET c = 3 WHERE c = 1;
> UPDATE t SET c = 4 WHERE c = 2;
> COMMIT_AUTONOMOUS;
> ROLLBACK;
> 
> If you replace the autonomous transaction with a savepoint, the c=3 update
> finds no rows, and the c=4 update changes one row.  When the outer transaction
> aborts, only the original c=1 row remains live.  If you replace the autonomous
> transaction with a dblink/pg_background call, the c=3 update waits
> indefinitely for c=2 to commit or abort, an undetected deadlock.

Maybe what we need to solve this is to restrict what the autonomous
transaction can do; for instance, make it so that the autonomous
transaction can see all rows of the outer transaction as if the outer
transaction were committed, but trying to update any such row raises an
error.  As far as I can see, this closes this particular problem.  (We
likely need additional rules to close all holes, but hopefully you get
the idea.)

Perhaps there exists a set of rules strong enough to eliminate all
problematic visibility scenarios, but which still enables behavior
useful enough to cover the proposed use cases.  The audit scenario is
covered because the audit trail doesn't need to modify the audited
tuples themselves, only read them.

-- 
Álvaro Herrerahttp://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 Transaction is back

[Noah Misch]

On Fri, Aug 07, 2015 at 11:26:08AM -0400, Robert Haas wrote:
> On Thu, Aug 6, 2015 at 11:04 PM, Merlin Moncure  wrote:
> > I don't necessarily disagree with what you're saying, but it's not
> > clear to me what the proposed behavior is.  Since the AT can commit
> > before the outer, ISTM *any* ungranted lock requested by the AT but
> > held by the outer leads to either A: functional deadlock (regardless
> > of implementation details) or B: special behavior.
> 
> I don't accept that.  We've already GOT cases where a query can be
> suspended and other queries can be running in the same backend.  You
> can do that via cursors.  Those cases work fine, and the deadlock
> detector doesn't know anything about them.  How is this any different?

In today's scenarios, the later query cannot commit unless the suspended query
also commits.  (Changing that is the raison d'être of autonomous
transactions.)  If the autonomous transaction can interact with uncommitted
work in a way that other backends could not, crazy things happen when the
autonomous transaction commits and the suspended transaction aborts:

CREATE TABLE t (c) AS SELECT 1;
BEGIN;
UPDATE t SET c = 2 WHERE c = 1;
BEGIN_AUTONOMOUS;
UPDATE t SET c = 3 WHERE c = 1;
UPDATE t SET c = 4 WHERE c = 2;
COMMIT_AUTONOMOUS;
ROLLBACK;

If you replace the autonomous transaction with a savepoint, the c=3 update
finds no rows, and the c=4 update changes one row.  When the outer transaction
aborts, only the original c=1 row remains live.  If you replace the autonomous
transaction with a dblink/pg_background call, the c=3 update waits
indefinitely for c=2 to commit or abort, an undetected deadlock.

Suppose you make the autonomous transaction see tuples like in the savepoint
case.  The c=3 update finds no rows to update, and the c=4 update changes one
row.  When the outer transaction aborts, you have two live rows (c=1 and c=4).
Suppose you instead make the autonomous transaction see tuples like in the
dblink case, yet let c=3 ignore the lock and change a row.  If both the
autonomous transaction and the outer transaction were to commit, then you get
two live rows (c=2 and c=3).  Neither of those outcomes is acceptable, of
course.  In today's tuple update rules, c=3 must deadlock[1].  Other credible
tuple update rules may not have this problem, but nothing jumps to mind.


[1] That's not to say it must use the shmem lock structures and deadlock
detector.


-- 
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 Transaction is back

[Robert Haas]

On Thu, Aug 6, 2015 at 11:04 PM, Merlin Moncure  wrote:
> I don't necessarily disagree with what you're saying, but it's not
> clear to me what the proposed behavior is.  Since the AT can commit
> before the outer, ISTM *any* ungranted lock requested by the AT but
> held by the outer leads to either A: functional deadlock (regardless
> of implementation details) or B: special behavior.

I don't accept that.  We've already GOT cases where a query can be
suspended and other queries can be running in the same backend.  You
can do that via cursors.  Those cases work fine, and the deadlock
detector doesn't know anything about them.  How is this any different?

-- 
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 Transaction is back

[Merlin Moncure]

On Thu, Aug 6, 2015 at 4:15 PM, Robert Haas  wrote:
> On Mon, Aug 3, 2015 at 9:09 AM, Merlin Moncure  wrote:
>> hm.  OK, what's the behavior of:
>>
>> BEGIN
>>   UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>
>>   BEGIN WITH AUTONOMOUS TRANSACTION
>> UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>   END;
>>
>>   RAISE EXCEPTION ...;
>> EXCEPTION ...
>>
>> END;
>
> Sure, so that case might need a little bit of special handling.  That
> doesn't mean it's a good idea for heavyweight locks to conflict in
> general.  I think you're going to find that implementing the latter is
> an extremely unrewarding task, and that the benefits are seriously
> negative.  For example, consider:
>
> BEGIN
> UPDATE foo SET x = x + 1 WHERE foo_id = 1;
> BEGIN WITH AUTONOMOUS TRANSACTION
> UPDATE foo SET x = x + 1 WHERE foo_id = 2;
> END;
> END;
>
> Now, suppose that a concurrent session does LOCK TABLE foo after the
> first UPDATE and before the second one.  That's now a soft deadlock.
> But the only way the deadlock detector can see that is if the main
> transaction and the autonomous transaction have separate PGPROC
> entries, which is a design we explicitly rejected because it puts a
> tight limit on the number of ATs that can be in progress and the level
> to which those ATs can be nested.  But let's say you don't care, so we
> go back to that design.  The deadlock detector will have to be taught
> that the outer transaction can't help but wait for the inner
> transaction, so we teach it that.  Now it can see that the only way to
> resolve the deadlock without aborting any transactions is to reorder
> the lock request from the autonomous transaction ahead of the
> concurrent session that is seeking a full table lock.  So the
> autonomous transaction acquires the lock without blocking after all.
> You have exactly the same result that you would have had anyway but
> with a phenomenal amount of additional code and complexity.
>
> And for what?  In the original example, the way the deadlock is going
> to be reported is like this:
>
> ERROR: deadlock detected
> DETAIL: Process 12345 waits for ShareLock on transaction 1000; blocked
> by process 12345.
> Process 12345 waits for ShareLock on transaction 1001; blocked by process 
> 12345.
>
> That is not a model of clarity.  On the other hand, if you just make a
> rule that attempting to update or delete a tuple that an outer
> transaction has already updated throws a bespoke error, you can do
> something like this:
>
> ERROR: tuple to be updated was already modified by a suspended outer 
> transaction
>
> ...which has precedent in an existing message in trigger.c.
> Similarly, if you try to drop a table that the outer transaction has
> locked, the natural thing is for CheckTableNotInUse() to catch that
> and report it this way:
>
> ERROR: cannot DROP TABLE "foo" because it is being used by active
> queries in this session
>
> If you work hard enough, you can instead make that generate a deadlock
> error message, but you're going to have to work pretty hard, and the
> result is worse.
>
> I'd really like to hear some more *specific* scenarios where it's
> valuable for locks to conflict between the outer transaction and the
> AT.  I grant that tuple updates are a case where the conflict has to
> be detected somehow, but I don't accept that the lock manager is the
> best way to do that, and I don't accept that there are a large number
> of other cases that will need similar handling.

I don't necessarily disagree with what you're saying, but it's not
clear to me what the proposed behavior is.  Since the AT can commit
before the outer, ISTM *any* ungranted lock requested by the AT but
held by the outer leads to either A: functional deadlock (regardless
of implementation details) or B: special behavior.  Deadlocks would
certainly require some acrobatics to detect and resolve due to the
fact that one party to the lock is not in fact blocked on a lock but
on the outer's execution state.  So maybe the right thing to do is to
simply ignore the problem and hang both transactions until timeout or
cancel; this isn't really much different vs. ghetto dblink style AT
that is done today in my experience.

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 Transaction is back

[Robert Haas]

On Mon, Aug 3, 2015 at 9:09 AM, Merlin Moncure  wrote:
> hm.  OK, what's the behavior of:
>
> BEGIN
>   UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>
>   BEGIN WITH AUTONOMOUS TRANSACTION
> UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>   END;
>
>   RAISE EXCEPTION ...;
> EXCEPTION ...
>
> END;

Sure, so that case might need a little bit of special handling.  That
doesn't mean it's a good idea for heavyweight locks to conflict in
general.  I think you're going to find that implementing the latter is
an extremely unrewarding task, and that the benefits are seriously
negative.  For example, consider:

BEGIN
UPDATE foo SET x = x + 1 WHERE foo_id = 1;
BEGIN WITH AUTONOMOUS TRANSACTION
UPDATE foo SET x = x + 1 WHERE foo_id = 2;
END;
END;

Now, suppose that a concurrent session does LOCK TABLE foo after the
first UPDATE and before the second one.  That's now a soft deadlock.
But the only way the deadlock detector can see that is if the main
transaction and the autonomous transaction have separate PGPROC
entries, which is a design we explicitly rejected because it puts a
tight limit on the number of ATs that can be in progress and the level
to which those ATs can be nested.  But let's say you don't care, so we
go back to that design.  The deadlock detector will have to be taught
that the outer transaction can't help but wait for the inner
transaction, so we teach it that.  Now it can see that the only way to
resolve the deadlock without aborting any transactions is to reorder
the lock request from the autonomous transaction ahead of the
concurrent session that is seeking a full table lock.  So the
autonomous transaction acquires the lock without blocking after all.
You have exactly the same result that you would have had anyway but
with a phenomenal amount of additional code and complexity.

And for what?  In the original example, the way the deadlock is going
to be reported is like this:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 1000; blocked
by process 12345.
Process 12345 waits for ShareLock on transaction 1001; blocked by process 12345.

That is not a model of clarity.  On the other hand, if you just make a
rule that attempting to update or delete a tuple that an outer
transaction has already updated throws a bespoke error, you can do
something like this:

ERROR: tuple to be updated was already modified by a suspended outer transaction

...which has precedent in an existing message in trigger.c.
Similarly, if you try to drop a table that the outer transaction has
locked, the natural thing is for CheckTableNotInUse() to catch that
and report it this way:

ERROR: cannot DROP TABLE "foo" because it is being used by active
queries in this session

If you work hard enough, you can instead make that generate a deadlock
error message, but you're going to have to work pretty hard, and the
result is worse.

I'd really like to hear some more *specific* scenarios where it's
valuable for locks to conflict between the outer transaction and the
AT.  I grant that tuple updates are a case where the conflict has to
be detected somehow, but I don't accept that the lock manager is the
best way to do that, and I don't accept that there are a large number
of other cases that will need similar handling.

-- 
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 Transaction is back

[Merlin Moncure]

On Tue, Aug 4, 2015 at 4:12 AM, Rajeev rastogi
 wrote:
> On 03 August 2015 18:40, Merlin Moncure [mailto:mmonc...@gmail.com] Wrote:
>>On Sun, Aug 2, 2015 at 11:37 PM, Rajeev rastogi
>> wrote:
>>> On 31 July 2015 23:10, Robert Haas Wrote:
I think we're going entirely down the wrong path here.  Why is it ever
>>useful for a backend's lock requests to conflict with themselves, even
>>with autonomous transactions?  That seems like an artifact of somebody
>>else's implementation that we should be happy we don't need to copy.
>>>
>>> IMHO, since most of the locking are managed at transaction level not
>>backend level and we consider main & autonomous transaction to be
>>independent transaction, then practically they may conflict right.
>>> It is also right as you said that there is no as such useful use-cases
>>where autonomous transaction conflicts with main (parent) transaction.
>>But we cannot take it for granted as user might make a mistake. So at-
>>least we should have some mechanism to handle this rare case, for which
>>as of now I think throwing error from autonomous transaction as one of
>>the solution. Once error thrown from autonomous transaction, main
>>transaction may continue as it is (or abort main transaction also??).
>>
>>hm.  OK, what's the behavior of:
>>
>>BEGIN
>>  UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>
>>  BEGIN WITH AUTONOMOUS TRANSACTION
>>UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>  END;
>>
>>  RAISE EXCEPTION ...;
>>EXCEPTION ...
>>
>>END;
>
> It should throw an error (or something equivalent) as the second update will 
> wait for record lock to get released, which in this case will not happen till 
> second update finishes. So catch 22.

Yeah. Point being, from my point of view autonomous transactions have
to conflict with the master transaction (or any transaction really).
I agree the right course of action is to error out immediately...what
else could you do?  There isn't even a deadlock in the classic sense
and allowing control to continue would result in indeterminate
behavior FWICT.

>>Also,
>>*) What do the other candidate implementations do?  IMO, 
>>compatibility>>should be the underlying design principle.
>
> Oracle throws error in such case. But we can decide on what behavior we want 
> to keep.

gotcha.  makes sense.

>>*) What will the "SQL only" feature look like?
>
> Similar to PL as mentioned in your example, we can provide the "SQL only" 
> feature also.
>
>>*) Is the SPI interface going to be extended to expose AT?
>
> I don’t think at this point that there is any need of exposing SPI interface 
> for this.

Ok, how do AT work in a non-plpgsql ("SQL only") scenario?  Are you
going to similarly extend BEGIN TRANSACTION?

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 Transaction is back

[Rajeev rastogi]

On 03 August 2015 18:40, Merlin Moncure [mailto:mmonc...@gmail.com] Wrote:
>On Sun, Aug 2, 2015 at 11:37 PM, Rajeev rastogi
> wrote:
>> On 31 July 2015 23:10, Robert Haas Wrote:
>>>I think we're going entirely down the wrong path here.  Why is it ever
>useful for a backend's lock requests to conflict with themselves, even
>with autonomous transactions?  That seems like an artifact of somebody
>else's implementation that we should be happy we don't need to copy.
>>
>> IMHO, since most of the locking are managed at transaction level not
>backend level and we consider main & autonomous transaction to be
>independent transaction, then practically they may conflict right.
>> It is also right as you said that there is no as such useful use-cases
>where autonomous transaction conflicts with main (parent) transaction.
>But we cannot take it for granted as user might make a mistake. So at-
>least we should have some mechanism to handle this rare case, for which
>as of now I think throwing error from autonomous transaction as one of
>the solution. Once error thrown from autonomous transaction, main
>transaction may continue as it is (or abort main transaction also??).
>
>hm.  OK, what's the behavior of:
>
>BEGIN
>  UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>
>  BEGIN WITH AUTONOMOUS TRANSACTION
>UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>  END;
>
>  RAISE EXCEPTION ...;
>EXCEPTION ...
>
>END;

It should throw an error (or something equivalent) as the second update will 
wait for record lock to get released, which in this case will not happen till 
second update finishes. So catch 22.

>Also,
>*) What do the other candidate implementations do?  IMO, compatibility
>should be the underlying design principle.


Oracle throws error in such case. But we can decide on what behavior we want to 
keep.

>*) What will the "SQL only" feature look like?

Similar to PL as mentioned in your example, we can provide the "SQL only" 
feature also.

>*) Is the SPI interface going to be extended to expose AT?

I don’t think at this point that there is any need of exposing SPI interface 
for this.

Thanks and Regards,
Kumar Rajeev Rastogi

-- 
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 Transaction is back

[Merlin Moncure]

On Sun, Aug 2, 2015 at 11:37 PM, Rajeev rastogi
 wrote:
> On 31 July 2015 23:10, Robert Haas Wrote:
>>I think we're going entirely down the wrong path here.  Why is it ever useful 
>>for a backend's lock requests to conflict with themselves, even with 
>>autonomous transactions?  That seems like an artifact of somebody else's 
>>implementation that we should be happy we don't need to copy.
>
> IMHO, since most of the locking are managed at transaction level not backend 
> level and we consider main & autonomous transaction to be independent 
> transaction, then practically they may conflict right.
> It is also right as you said that there is no as such useful use-cases where 
> autonomous transaction conflicts with main (parent) transaction. But we 
> cannot take it for granted as user might make a mistake. So at-least we 
> should have some mechanism to handle this rare case, for which as of now I 
> think throwing error from autonomous transaction as one of the solution. Once 
> error thrown from autonomous transaction, main transaction may continue as it 
> is (or abort main transaction also??).

hm.  OK, what's the behavior of:

BEGIN
  UPDATE foo SET x = x + 1 WHERE foo_id = 1;

  BEGIN WITH AUTONOMOUS TRANSACTION
UPDATE foo SET x = x + 1 WHERE foo_id = 1;
  END;

  RAISE EXCEPTION ...;
EXCEPTION ...

END;

Also,
*) What do the other candidate implementations do?  IMO, compatibility
should be the underlying design principle.

*) What will the "SQL only" feature look like?

*) Is the SPI interface going to be extended to expose AT?

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 Transaction is back

[Rajeev rastogi]

On 31 July 2015 23:10, Robert Haas Wrote: 
On Tue, Jul 28, 2015 at 6:01 AM, Craig Ringer  wrote:
>> That should be practical to special-case by maintaining a list of 
>> parent transaction (virtual?) transaction IDs. Attempts to wait on a 
>> lock held by any of those should fail immediately. There's no point 
>> waiting for the deadlock detector since the outer tx can never 
>> progress and commit/rollback to release locks, and it might not be 
>> able to see the parent/child relationship from outside the backend 
>> doing the nested tx anyway.

>I think we're going entirely down the wrong path here.  Why is it ever useful 
>for a backend's lock requests to conflict with themselves, even with 
>autonomous transactions?  That seems like an artifact of somebody else's 
>implementation that we should be happy we don't need to copy.

IMHO, since most of the locking are managed at transaction level not backend 
level and we consider main & autonomous transaction to be independent 
transaction, then practically they may conflict right.   
It is also right as you said that there is no as such useful use-cases where 
autonomous transaction conflicts with main (parent) transaction. But we cannot 
take it for granted as user might make a mistake. So at-least we should have 
some mechanism to handle this rare case, for which as of now I think throwing 
error from autonomous transaction as one of the solution. Once error thrown 
from autonomous transaction, main transaction may continue as it is (or abort 
main transaction also??). 

Any other suggestion to handle this will be really helpful.

Thanks and Regards,
Kumar Rajeev Rastogi




-- 
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 Transaction is back

[Robert Haas]

On Tue, Jul 28, 2015 at 6:01 AM, Craig Ringer  wrote:
> That should be practical to special-case by maintaining a list of
> parent transaction (virtual?) transaction IDs. Attempts to wait on a
> lock held by any of those should fail immediately. There's no point
> waiting for the deadlock detector since the outer tx can never
> progress and commit/rollback to release locks, and it might not be
> able to see the parent/child relationship from outside the backend
> doing the nested tx anyway.

I think we're going entirely down the wrong path here.  Why is it ever
useful for a backend's lock requests to conflict with themselves, even
with autonomous transactions?  That seems like an artifact of somebody
else's implementation that we should be happy we don't need to copy.

-- 
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 Transaction is back

[Rajeev rastogi]

On 28 July 2015 03:21, Josh Berkus Wrote:
 
On 07/27/2015 02:47 AM, Rajeev rastogi wrote:
>>> Why have any fixed maximum?
>> Since we are planning to have nested autonomous transaction, so it is 
>> required to have limit on this so that resources can be controlled.

>Is there a particular reason why this limit wouldn't just be max_stack_depth?

We will require to allocate some initial resources in order to handle all 
nested autonomous transaction. 
So I think it is better to have some different configuration parameter.

Thanks and Regards,
Kumar Rajeev Rastogi

-- 
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 Transaction is back

[Rajeev rastogi]

On 28 July 2015 15:31, Craig Ringer Wrote:


>> 2.It should be allowed to deadlock with master transaction.
>We
>> need to work-out a solution to avoid deadlock.
>
>The deadlock case in autonomous tx's is a bit different.
>
>Assuming you don't intend to allow interleaving, where you can switch
>between transactions at will rather than just at begin/commit, the only
>way a deadlock can happen is when the outer tx holds a lock that the
>inner tx tries to acquire.
>
>That should be practical to special-case by maintaining a list of parent
>transaction (virtual?) transaction IDs. Attempts to wait on a lock held
>by any of those should fail immediately. There's no point waiting for
>the deadlock detector since the outer tx can never progress and
>commit/rollback to release locks, and it might not be able to see the
>parent/child relationship from outside the backend doing the nested tx
>anyway.

Thanks, sounds to be a good idea. I shall evaluate the same.

Thanks and Regards,
Kumar Rajeev Rastogi

-- 
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 Transaction is back

[Craig Ringer]

On 23 July 2015 at 13:31, Rajeev rastogi  wrote:

> 1.The autonomous transaction treated as a completely different
> transaction from the master transaction.

Personally I think that's a lot more useful than having the inner tx
able to see the outer tx's uncommitted changes.

> 2.It should be allowed to deadlock with master transaction. We
> need to work-out a solution to avoid deadlock.

The deadlock case in autonomous tx's is a bit different.

Assuming you don't intend to allow interleaving, where you can switch
between transactions at will rather than just at begin/commit, the
only way a deadlock can happen is when the outer tx holds a lock that
the inner tx tries to acquire.

That should be practical to special-case by maintaining a list of
parent transaction (virtual?) transaction IDs. Attempts to wait on a
lock held by any of those should fail immediately. There's no point
waiting for the deadlock detector since the outer tx can never
progress and commit/rollback to release locks, and it might not be
able to see the parent/child relationship from outside the backend
doing the nested tx anyway.

There's no need to check the parent list until we actually try to wait
on a lock, though I don't know whether it's practical to delay until
then.

-- 
 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 Transaction is back

[Joel Jacobson]

On Tue, Jul 28, 2015 at 12:56 AM, Josh Berkus  wrote:

> Ah, ok.  The goal of the project is that the writer of X() *cannot*
> prevent Y() from writing its data (B1) and committing it.
>

> One of the primary use cases for ATX is audit triggers.  If a function
> writer could override ATX and prevent the audit triggers from
> committing, then that use case would be violated.
>
> Can you explain what use case you have where simply telling the staff
> "if you use ATX without clearing it, you'll be fired" is not sufficient?
>  Possibly there's something we failed to account for in the unconference
> discussion.
>

I fully understand and agree why you want to prevent X() from letting Y()
commit, if the use-case is e.g. auditing.

I'll try to explain where I'm coming from by providing a bit of background
and context.

One of the greatest strengths with writing an entire application using only
sql and plpgsql functions,
is you don't have to worry about side effects when calling functions, since
you are always in total control to
rollback all your writes at your stack-depth and deeper down the stack, and
the caller can likewise be certain
that if the function it called threw an exception, all of its work is
rollbacked.

However, if e.g. making use of plperlu functions, it's possible those
functions might have written files to disk
or made network connections to the outside world,
i.e. it's possible they have caused side-effects that naturally cannot be
rollbacked by postgres.

Currently, in our codebase at Trustly, we have already quite a few plperlu
functions.
In each one of them, we need to think carefully about side-effects,
it's usually fine since most of them are immutable.

But if we had to worry about all plpgsql functions being able to write
things without consent from the caller,
then we would have a completely different situation with increased
complexity and risk for failures.

>"if you use ATX without clearing it, you'll be fired"
We trust each other so we don't have that kind of problem.
But still, even when trusting your own and others code, I would say quite
often you make use of a function
for which there are small details you have forgotten about or never knew in
the first place,
and if that little detail would be something written in an ATX, that could
be a problem
if you the caller wouldn't want whatever was written to be written.

Don't get me wrong, ATX is something I would absoltely love, since then you
could
for instance in function doing password validation, update the
FailedLoginAttempts column
in an ATX and then still raise an exception to rollback the operation and
return an error to the client.

However, the need for ATXs is at least for us a special need you won't need
in most functions,
and since the risk and complexity increases with it, I would prefer if it
can be enaled/disabled
by default globally and explicitly enabled/disabled per function.

If the global default is "disabled" or if it's "disabled" for a specific
function, like for X() in your example,
and if it's enabled for Y(), then X() tries to call Y() you should get an
error even before Y() is executed.

That way we can still do auditing, since X() couldn't execute Y() since it
was declared as AT,
and that would be the same thing as if X() wouldn't have the line of code
in it that executs Y(),
something which X() is in power of as if X() calls Y() or not, is
ultimately X()'s decision.

If we declare entire functions as AT, then we only have to check before
executing the function
if AT is allowed or not in the context, determined by the global default or
if the caller function is defined AT or NOT AT.

Use cases:

1. X[AT] -> Y[AT]
OK, since caller X() is declared AT i.e. allows AT in itself and in callees.

2. X[AT] -> Y[NOT AT]
OK, since caller X() is declared AT i.e. allows AT in itself and in callees,
and since Y() is NOT AT, i.e. not making use of AT and not allowing it in
callees
that is not in violation with anything.

3: X[NOT AT] -> Y[AT]
Invalid, since caller X() is declared NOT AT, i.e. disallows AT in itself
and in callees,
and since Y() is declared AT it cannot be executed since it's declared AT.

4: X[NOT AT] -> Y[NOT AT]
OK, since caller X() is declared NOT AT, i.e. disallows AT in itself and in
callees,
and since Y() is also declared NOT AT, it can be executed.

Re: [HACKERS] Autonomous Transaction is back

[Ashutosh Bapat]

> Ah, you're missing how commits in ATX are expected to work.  Let me
> illustrate:
>
> X (
>Data write A1
>call Y(
> Start ATX
> Data write B1
> Commit ATX
>)
>Data write A2
>Exception
> )
>
> In this workflow, B1 would be committed and persistent. Neither A1 nor
> A2 would be committed, or visible to other users.  Depending on what
> implementation we end up with, A1 might not even be visible to Y().
>
>
A1 should never be visible to Y(), else we will end up with
inconsistencies. E.g.

A1 is a primary key and B1 writes a foreign key referencing A1. Commit ATX,
will not complain as it sees A1, but in case X rolls back, we may have B1
referencing nothing.

Am I missing something?

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [HACKERS] Autonomous Transaction is back

[Pavel Stehule]

2015-07-27 23:59 GMT+02:00 Merlin Moncure :

> On Mon, Jul 27, 2015 at 4:41 PM, Joel Jacobson  wrote:
> > On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure 
> wrote:
> >>
> >> On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus  wrote:
> >> > Batch Jobs: large data-manipulation tasks which need to be broken up
> >> > into segments, with each segment committing separately.  Example:
> >> > updating 1 million records in batches of 1000.
> >>
> >> Autonomous transactions are not a good fit for this case; stored
> >> procedures are a better way to go for any scenario where you don't
> >> want be be in a snapshot (for example, suppose you want to change
> >> isolation level on the fly).
> >
> >
> > Hm, you mean we need real "stored procedures" in PostgreSQL and not just
> > "functions"?
>
> Yes, exactly.
>
> Autonomous transactions aren't really set up for cases where the
> function runs for a very long time or indefinitely.  This is the
> 'advancing xmin' problem as Josh puts it but I think the problem is
> much bigger than that.  Anyways, this is mostly irrelevant to
> autonomous transactions as long as the design isn't extended to try
> and cover that case.
>
> Is the Autonomous Transaction feature only going to be exposed through
> pl/pgsql?
>

I hope not.

The integration with plpgsql can be secondary question. In this case I
prefer a relation to block statement without possibility to explicit
COMMIT. Minimally in functions.

some like

BEGIN
  BEGIN AUTONOMOUS
   ...
  END;
END;

This is consistent with current subtransaction support, and disallow some
corner cases like forgotten COMMIT.

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 Transaction is back

[Jim Nasby]

On 7/27/15 6:40 PM, Jim Nasby wrote:

On 7/27/15 5:12 PM, Joel Jacobson wrote:

Right now, when writing a function, if you raise an exception, you can
be sure all writes you have made will be rollbacked, but your caller
function might caught the exception and decide to carry on and commit
work made before your function was called, but at least you can be
confident your writes won't be committed as long as you don't caught the
exception you raised in your own function. If I understand it correctly,
that would change with the addition of Autonomous Transaction, unless
given a way to prevent a function you call from starting and commiting
a Autonomous Transaction. Wrong? If so, then please show how to prevent
Y() from commiting the "Data write B1" in your example, I don't get it.


What's being described here doesn't make sense in either use case ([1] &
[2]), but I do understand the concern about what 3rd party software is
doing. It would be nice to have the ability to disallow and/or disable
autonomous transactions, but I don't see a practical way of doing that
other than introducing a new GUC. I'm not sure if it's worth that effort.


It just occurred to me that another option would be to have an event 
trigger for beginning an autonomous transaction.



[1] the "batch process" use case: batches that still hold their own
transaction open don't gain anything.

[2] the "audit logging" case. If you didn't care about auditing
surviving regardless of a rollback then you wouldn't go to the extra
work of an autonomous transaction to begin with.



--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.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 Transaction is back

[Jim Nasby]

On 7/27/15 5:56 PM, Josh Berkus wrote:

Can you explain what use case you have where simply telling the staff
"if you use ATX without clearing it, you'll be fired" is not sufficient?
  Possibly there's something we failed to account for in the unconference
discussion.


That there's no way to enforce that, short of hand-auditing code? 
There's already enough things that are difficult/impossible to enforce, 
I'd rather not add another one.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.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 Transaction is back

[Jim Nasby]

On 7/27/15 5:12 PM, Joel Jacobson wrote:

Right now, when writing a function, if you raise an exception, you can
be sure all writes you have made will be rollbacked, but your caller
function might caught the exception and decide to carry on and commit
work made before your function was called, but at least you can be
confident your writes won't be committed as long as you don't caught the
exception you raised in your own function. If I understand it correctly,
that would change with the addition of Autonomous Transaction, unless
given a way to prevent a function you call from starting and commiting
a Autonomous Transaction. Wrong? If so, then please show how to prevent
Y() from commiting the "Data write B1" in your example, I don't get it.


What's being described here doesn't make sense in either use case ([1] & 
[2]), but I do understand the concern about what 3rd party software is 
doing. It would be nice to have the ability to disallow and/or disable 
autonomous transactions, but I don't see a practical way of doing that 
other than introducing a new GUC. I'm not sure if it's worth that effort.


[1] the "batch process" use case: batches that still hold their own 
transaction open don't gain anything.


[2] the "audit logging" case. If you didn't care about auditing 
surviving regardless of a rollback then you wouldn't go to the extra 
work of an autonomous transaction to begin with.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.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 Transaction is back

[Josh Berkus]

On 07/27/2015 03:12 PM, Joel Jacobson wrote:
> On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus  > wrote:
> 
> Ah, you're missing how commits in ATX are expected to work.  Let me
> illustrate:
> 
> X (
>Data write A1
>call Y(
> Start ATX
> Data write B1
> Commit ATX
>)
>Data write A2
>Exception
> )
> 
> In this workflow, B1 would be committed and persistent. Neither A1 nor
> A2 would be committed, or visible to other users.  Depending on what
> implementation we end up with, A1 might not even be visible to Y().
> 
> So that solves your use case without any need to "block" ATXs in called
> functions.  However, it leads to some interesting cases involving
> self-deadlocks; see the original post on this thread.
> 
> 
> I don't follow. In your example above, if I'm X(), how do I ensure Y()
> won't have committed anyting at all when I later at "Exception" decide
> to rollback everything from "Data write A1" to "Data write A2" including
> any writes made by Y() (in the example "Data write B1")?

Ah, ok.  The goal of the project is that the writer of X() *cannot*
prevent Y() from writing its data (B1) and committing it.

One of the primary use cases for ATX is audit triggers.  If a function
writer could override ATX and prevent the audit triggers from
committing, then that use case would be violated.

Can you explain what use case you have where simply telling the staff
"if you use ATX without clearing it, you'll be fired" is not sufficient?
 Possibly there's something we failed to account for in the unconference
discussion.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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 Transaction is back

[Joel Jacobson]

On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus  wrote:

> Ah, you're missing how commits in ATX are expected to work.  Let me
> illustrate:
>
> X (
>Data write A1
>call Y(
> Start ATX
> Data write B1
> Commit ATX
>)
>Data write A2
>Exception
> )
>
> In this workflow, B1 would be committed and persistent. Neither A1 nor
> A2 would be committed, or visible to other users.  Depending on what
> implementation we end up with, A1 might not even be visible to Y().
>
> So that solves your use case without any need to "block" ATXs in called
> functions.  However, it leads to some interesting cases involving
> self-deadlocks; see the original post on this thread.
>
>
I don't follow. In your example above, if I'm X(), how do I ensure Y()
won't have committed anyting at all when I later at "Exception" decide to
rollback everything from "Data write A1" to "Data write A2" including any
writes made by Y() (in the example "Data write B1")?

I understand the "Exception" will take care of rollbacking my (X's) writes,
but that's not sufficient if you want to make sure you rollback
*everything*, including any writes made by functions you call.

Right now, when writing a function, if you raise an exception, you can be
sure all writes you have made will be rollbacked, but your caller function
might caught the exception and decide to carry on and commit work made
before your function was called, but at least you can be confident your
writes won't be committed as long as you don't caught the exception you
raised in your own function. If I understand it correctly, that would
change with the addition of Autonomous Transaction, unless given a way to
prevent a function you call from starting and commiting a Autonomous
Transaction. Wrong? If so, then please show how to prevent Y() from
commiting the "Data write B1" in your example, I don't get it.

Re: [HACKERS] Autonomous Transaction is back

[Merlin Moncure]

On Mon, Jul 27, 2015 at 4:41 PM, Joel Jacobson  wrote:
> On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure  wrote:
>>
>> On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus  wrote:
>> > Batch Jobs: large data-manipulation tasks which need to be broken up
>> > into segments, with each segment committing separately.  Example:
>> > updating 1 million records in batches of 1000.
>>
>> Autonomous transactions are not a good fit for this case; stored
>> procedures are a better way to go for any scenario where you don't
>> want be be in a snapshot (for example, suppose you want to change
>> isolation level on the fly).
>
>
> Hm, you mean we need real "stored procedures" in PostgreSQL and not just
> "functions"?

Yes, exactly.

Autonomous transactions aren't really set up for cases where the
function runs for a very long time or indefinitely.  This is the
'advancing xmin' problem as Josh puts it but I think the problem is
much bigger than that.  Anyways, this is mostly irrelevant to
autonomous transactions as long as the design isn't extended to try
and cover that case.

Is the Autonomous Transaction feature only going to be exposed through pl/pgsql?

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 Transaction is back

[Josh Berkus]

On 07/27/2015 02:47 AM, Rajeev rastogi wrote:
>> Why have any fixed maximum?
> Since we are planning to have nested autonomous transaction, so it is 
> required to have limit on this so that resources can be controlled.

Is there a particular reason why this limit wouldn't just be
max_stack_depth?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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 Transaction is back

[Josh Berkus]

On 07/27/2015 02:41 PM, Joel Jacobson wrote:
> However, we should also add a way for the caller to protect against an
> Autonomous Transaction in a function called by the caller. Imagine if
> you're the author of function X() and within X() make use of some other
> function Y() which has been written by some other author, and within
> your function X(), it's very important either all of your work or none
> at all gets committed, then you need to make sure none of the changes
> you made before calling Y() gets committed, and thus we need a way to
> prevent Y() from starting and committing an Autonomous Transaction,
> otherwise we would increase the risk and complexity of working with
> functions and plpgsql in PostgreSQL as you would then need to be sure
> none of the functions you are using within a function will start and
> commit an ATX.

Ah, you're missing how commits in ATX are expected to work.  Let me
illustrate:

X (
   Data write A1
   call Y(
Start ATX
Data write B1
Commit ATX
   )
   Data write A2
   Exception
)

In this workflow, B1 would be committed and persistent. Neither A1 nor
A2 would be committed, or visible to other users.  Depending on what
implementation we end up with, A1 might not even be visible to Y().

So that solves your use case without any need to "block" ATXs in called
functions.  However, it leads to some interesting cases involving
self-deadlocks; see the original post on this thread.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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 Transaction is back

[Joel Jacobson]

On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure  wrote:

> On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus  wrote:
> > Batch Jobs: large data-manipulation tasks which need to be broken up
> > into segments, with each segment committing separately.  Example:
> > updating 1 million records in batches of 1000.
>
> Autonomous transactions are not a good fit for this case; stored
> procedures are a better way to go for any scenario where you don't
> want be be in a snapshot (for example, suppose you want to change
> isolation level on the fly).


Hm, you mean we need real "stored procedures" in PostgreSQL and not just
"functions"?

If not, I think it would be sufficient to add Autonomous Transaction
support to the type of functions we already have in pg to allow writing a
batch job function which would commit after X numbers of modified rows,
instead of having to write a script in an external language such as Perl to
call the function in a while-loop and commit in between each function call.

However, we should also add a way for the caller to protect against an
Autonomous Transaction in a function called by the caller. Imagine if
you're the author of function X() and within X() make use of some other
function Y() which has been written by some other author, and within your
function X(), it's very important either all of your work or none at all
gets committed, then you need to make sure none of the changes you made
before calling Y() gets committed, and thus we need a way to prevent Y()
from starting and committing an Autonomous Transaction, otherwise we would
increase the risk and complexity of working with functions and plpgsql in
PostgreSQL as you would then need to be sure none of the functions you are
using within a function will start and commit an ATX.

Re: [HACKERS] Autonomous Transaction is back

[Rajeev rastogi]

On 23 July 2015 21:04, Robert Haas Wrote:
On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi  
wrote:
>> 2.It should be allowed to deadlock with master transaction. We
>> need to work-out a solution to avoid deadlock.

>This sentence seems to contradict itself.  I thought the consensus was that 
>the transaction should NOT conflict with the master transaction.

Since we are saying transaction is autonomous to parent transaction, we cannot 
guarantee that it does not take any conflicting lock unless otherwise designed 
so by the application.
But yes, we should have mechanism to deal with the possible deadlock.

>> 3.It can support multiple level of nesting based on the
>> configuration (may be max as 70).

>Why have any fixed maximum?

Since we are planning to have nested autonomous transaction, so it is required 
to have limit on this so that resources can be controlled.

>> 2. The above commands can be issued either inside the procedure to 
>> make few statements of procedure inside autonomous transaction or even 
>> in stand-alone query execution.

>I think inside a procedure the autonomous transaction will need to be 
>lexically scoped.  You won't be able to do this, for example:

>BEGIN AUTONOMOUS TRANSACTION;
>FOR x IN SELECT ... LOOP
>COMMIT;
>BEGIN AUTONOMOUS TRANSACTION;
>END LOOP;

I am not sure, how we will be able to control this. IMHO user should be able to 
control this, especially since it does not have any meaning from user 
perspective.
Please let me know if I am missing something here.

>Rather you'd have to do something like this:

>FOR x IN SELECT .. LOOP
>   BEGIN WITH AUTONOMOUS TRANSACTION
>   do stuff
>   END;
>END LOOP;


Thanks and Regards,
Kumar Rajeev Rastogi


-- 
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 Transaction is back

[Merlin Moncure]

On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus  wrote:
> Batch Jobs: large data-manipulation tasks which need to be broken up
> into segments, with each segment committing separately.  Example:
> updating 1 million records in batches of 1000.

Autonomous transactions are not a good fit for this case; stored
procedures are a better way to go for any scenario where you don't
want be be in a snapshot (for example, suppose you want to change
isolation level on the fly).

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 Transaction is back

[Ashutosh Bapat]

On Thu, Jul 23, 2015 at 11:01 AM, Rajeev rastogi 
wrote:

>  After few failed attempt to propose Autonomous transaction earlier. I
> along with Simon Riggs would like to propose again but completely different
> in approach.
>
>
>
> We also had discussion about this feature in last PGCon2015 Unconference
> Day, those who missed this discussion, please refer
>
>
> https://wiki.postgresql.org/wiki/AutonomousTransactionsUnconference2015
>
>
>
>
>
> Before jumping into the design and code proposal for this feature, me
> along with Simon Riggs wanted to propose its behavior and usage to keep
> everyone in the same boat.
>
> So we have summarized the behavior and usage of the Autonomous Transaction
> based on the discussion with community members in last PGCon2015
> Unconference Day:
>
>
>
> *Behavior of Autonomous Transaction*:
>
> 1.The autonomous transaction treated as a completely different
> transaction from the master transaction.
>
> 2.It should be allowed to deadlock with master transaction. We
> need to work-out a solution to avoid deadlock.
>

For an autonomous transaction not to conflict with the master, either it
shouldn't take conflicting resources or when it does so, they should be
shared by the master transaction. The first needs to be implemented in an
application, the later will need careful design of how to share such
resources (hierarchial resource sharing?) and how to release the resources
when the ATX ends. Given that the transactions are autonomous as per point
1, they should be allowed to conflict and the deadlock detection would take
care of such deadlocks.


>  3.It can support multiple level of nesting based on the
> configuration (may be max as 70).
>


This might just be an implementation artifact in other RDBMSes, and may not
necessarily apply to the implementation done here. So, we should not list
it as "necessary" behaviour of ATX. Just as an example (no way suggesting
that the design should be this way), if we implement autonomous
transactions using background worker processes or additional backends like
prepared transactions, we will have a limit on the number of simultaneously
open ATX. This may translate to nesting limit if we implement 4, but not if
we implement parallel autonomous transactions.


>  4.Outer (i.e. main or upper autonomous) transaction to be
> suspended while the inner autonomous transaction is running.
>
> 5.Outer transaction should not see data of inner till inner is
> committed (serializable upper transaction should not see even after inner
> transaction commit).
>

To me only 1 and 5 seem to be the real behaviour we should define, may be
4, but that restricts parallelism. Although parallel autonomous
transactions will have its own problems to solve like sharing code. But the
design shouldn't restrict it from being a future possibility, I guess.


>
>
> *How to Use Autonomous Transaction*:
>
> 1. We can issue explicit command to start an Autonomous transaction as
> below:
>
> BEGIN AUTONOMOUS TRANSACTION  (Don’t worry about keywords
> at this point.)
>
> Do you work.
>
> COMMIT/ROLLBACK   (Will commit/rollback the autonomous
> transaction and will return to main transaction or upper autonomous
> transaction).
>
>
>
> 2. The above commands can be issued either inside the procedure to make
> few statements of procedure inside autonomous transaction or even in
> stand-alone query execution.
>
> 3. We can make whole procedure itself as autonomous, which will be similar
> to start autonomous transaction in the beginning of the procedure and
> commit/rollback at the end of the procedure.
>
>
>
> There was another discussion in Unconference Day to decide whether to
> implement COMMIT/ROLLBACK inside the procedure or autonomous transaction.
> So our opinion about this is that
>
> COMMIT/ROLLBACK inside procedure will be somewhat
> different from Autonomous Transaction as incase of first, once we commit
> inside the procedure,
>
> it commits everything done before call of procedure. This is the behavior
> of Oracle.
>
> So in this case user required to be very careful to not do any operation
> before call of procedure, which is not yet intended to be committed inside
> procedure.
>
>
>
> So we can prefer to implement Autonomous Transaction, which will not only
> be compatible with Oracle but also gives really strong required features.
>
>
>
> I have not put the use-cases here as already we agree about its strong
> use-cases.
>
>
>
> Requesting for everyone's opinion regarding this based on which we can
> proceed to enhance/tune/re-write our design.
>
>
>
> *Thanks and Regards,*
>
> *Kumar Rajeev Rastogi *
>
>
>



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [HACKERS] Autonomous Transaction is back

[Josh Berkus]

On 07/23/2015 12:40 PM, Robert Haas wrote:
> That's a problem, but I think it is rather unfair to say that it has
> anything to do with autonomous transactions.  "Run a procedure without
> needing to hold a snapshot" is a completely separate feature request
> from "allow autonomous transactions", and it's probably neither easy
> nor uncontroversial.

That's more-or-less a summary of what I just posted.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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 Transaction is back

[Robert Haas]

On Thu, Jul 23, 2015 at 2:49 PM, Josh Berkus  wrote:
> On 07/23/2015 11:39 AM, Robert Haas wrote:
>> On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus  wrote:
 Requesting for everyone's opinion regarding this based on which we can
 proceed to enhance/tune/re-write our design.
>>>
>>> So, one of the things which came up during the discussion was advancing
>>> XMIN, which is not important to the audit logging use case, but is very
>>> important for the batch job use case.  What have you concluded regarding
>>> this item?
>>
>> Could you explain more specifically what you are talking about here?
>>
> Yeah, my notes are kinda incoherent, no?
>
> There's two core use-cases for Autonomous Transactions (hereafter ATX):
>
> * audit logging
> * batch jobs
>
> Audit Logging: triggers or other statements which should leave a record
> even when a transaction aborts.  While audit logging is the main example
> of this use case, any kind of logging of failures is applicable.
>
> Batch Jobs: large data-manipulation tasks which need to be broken up
> into segments, with each segment committing separately.  Example:
> updating 1 million records in batches of 1000.
>
> Unlike the Audit Logging case, Batch Jobs generally want XMIN to advance
> so that updated/imported/deleted rows can be vacuumed or HOT updated.
> Thus the need to allow XMIN to advance.
>
> One of the things we kind of concluded from our discussion was that the
> two core use-cases are probably different features:
>
> Audit Logging:
> * requires 2-way data interaction with outer transaction
> * no parallelism
> * XMIN does not need to advance
> * master transaction should still commit/fail
> * needs to support nesting
>
> Batch Jobs:
> * 1-way data interaction sufficient (master-->child)
> * parallelism desired
> * XMIN should advance
> * master process could be transactionless
> * does not need to support nesting
>
> Of these two, the Audit Logging case is the more important one to
> implement because there is no real workaround for it.  Batch Jobs can,
> and are, handled by external scripting, and having ATX for them is more
> of a convenience than anything else.

You're still not really explaining what you mean by "xmin should
advance".  If the parent transaction holds a snapshot, or for as long
as it does, xmin can't be advanced safely.  If it doesn't, you'll be
fine.  I suppose the situation you're worried about is where we
execute a stored procedure that repeatedly spawns autonomous
transactions.  Since the parent transaction will always have a
snapshot, you won't advance xmin until the entire stored procedure
finishes.

That's a problem, but I think it is rather unfair to say that it has
anything to do with autonomous transactions.  "Run a procedure without
needing to hold a snapshot" is a completely separate feature request
from "allow autonomous transactions", and it's probably neither easy
nor uncontroversial.

-- 
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 Transaction is back

[Josh Berkus]

On 07/23/2015 11:39 AM, Robert Haas wrote:
> On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus  wrote:
>>> Requesting for everyone's opinion regarding this based on which we can
>>> proceed to enhance/tune/re-write our design.
>>
>> So, one of the things which came up during the discussion was advancing
>> XMIN, which is not important to the audit logging use case, but is very
>> important for the batch job use case.  What have you concluded regarding
>> this item?
> 
> Could you explain more specifically what you are talking about here?
> 

Yeah, my notes are kinda incoherent, no?

There's two core use-cases for Autonomous Transactions (hereafter ATX):

* audit logging
* batch jobs

Audit Logging: triggers or other statements which should leave a record
even when a transaction aborts.  While audit logging is the main example
of this use case, any kind of logging of failures is applicable.

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately.  Example:
updating 1 million records in batches of 1000.

Unlike the Audit Logging case, Batch Jobs generally want XMIN to advance
so that updated/imported/deleted rows can be vacuumed or HOT updated.
Thus the need to allow XMIN to advance.

One of the things we kind of concluded from our discussion was that the
two core use-cases are probably different features:

Audit Logging:
* requires 2-way data interaction with outer transaction
* no parallelism
* XMIN does not need to advance
* master transaction should still commit/fail
* needs to support nesting

Batch Jobs:
* 1-way data interaction sufficient (master-->child)
* parallelism desired
* XMIN should advance
* master process could be transactionless
* does not need to support nesting

Of these two, the Audit Logging case is the more important one to
implement because there is no real workaround for it.  Batch Jobs can,
and are, handled by external scripting, and having ATX for them is more
of a convenience than anything else.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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 Transaction is back

[Robert Haas]

On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus  wrote:
>> Requesting for everyone's opinion regarding this based on which we can
>> proceed to enhance/tune/re-write our design.
>
> So, one of the things which came up during the discussion was advancing
> XMIN, which is not important to the audit logging use case, but is very
> important for the batch job use case.  What have you concluded regarding
> this item?

Could you explain more specifically what you are talking about here?

-- 
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 Transaction is back

[Josh Berkus]

> Requesting for everyone's opinion regarding this based on which we can
> proceed to enhance/tune/re-write our design.   

So, one of the things which came up during the discussion was advancing
XMIN, which is not important to the audit logging use case, but is very
important for the batch job use case.  What have you concluded regarding
this item?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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 Transaction is back

[Robert Haas]

On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi
 wrote:
> 2.It should be allowed to deadlock with master transaction. We
> need to work-out a solution to avoid deadlock.

This sentence seems to contradict itself.  I thought the consensus was
that the transaction should NOT conflict with the master transaction.

> 3.It can support multiple level of nesting based on the
> configuration (may be max as 70).

Why have any fixed maximum?

> 2. The above commands can be issued either inside the procedure to make few
> statements of procedure inside autonomous transaction or even in stand-alone
> query execution.

I think inside a procedure the autonomous transaction will need to be
lexically scoped.  You won't be able to do this, for example:

BEGIN AUTONOMOUS TRANSACTION;
FOR x IN SELECT ... LOOP
COMMIT;
BEGIN AUTONOMOUS TRANSACTION;
END LOOP;

Rather you'd have to do something like this:

FOR x IN SELECT .. LOOP
   BEGIN WITH AUTONOMOUS TRANSACTION
   do stuff
   END;
END LOOP;

In that way it would work like an EXCEPTION block, which can be
attached to a begin block to establish a subtransaction.  There's no
way in PL/pgsql to persistently enter a subtransaction; but you can
have one that applies to a particular lexical scope (the BEGIN block
to which EXCEPTION is attached).

-- 
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 Transaction (WIP)

[Amit Kapila]

On Thu, Jul 3, 2014 at 12:03 PM, Rajeev rastogi 
wrote:
> On 01 July 2014 12:00, Amit Kapila Wrote:
> >Simon has mentioned that exactly this idea has been rejected at
>
> >PGCon 2 years back. Please refer that in below mail:
>
> >
http://www.postgresql.org/message-id/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@szxeml508-mbx.china.huawei.com
>
>
>
> >As far as I can see, you never came back with the different solution.
>
>
>
> Yeah right. So for this I tried to search archived mails to get the
details about the discussion but I could not find anything regarding design.
> So I am not sure how shall I make my solution different from earlier as
earlier solution is not accessible to me.

I haven't read your idea/patch in any detail, so can't comment
on whether it is good or bad.  However I think if one of the
Committers has already mentioned that the same idea has been
rejected previously, then it makes little sense to further review
or update the patch unless you know the reason of rejection and
handle it in an acceptable way.

Now as far as I can understand, the problem seems to be in a way
you have defined Autonomous Transaction Storage which can lead
to consumption of additional client slots, this is just what I could make
sense from above mail but I am not completely sure on this matter.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 01 July 2014 12:00, Amit Kapila Wrote:

>On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi 
>mailto:rajeev.rast...@huawei.com>> wrote:
>> On 30 June 2014 22:50, Pavel Stehule Wrote:
>> >I didn't find a related message.
>> >?
>>
>> I think there have been some confusion, the design idea were never rejected 
>> but yes there were few feedback/ concern, which I had clarified. Also some 
>> of the other concerns are already fixed in latest patch.

>Simon has mentioned that exactly this idea has been rejected at
>PGCon 2 years back. Please refer that in below mail:
>http://www.postgresql.org/message-id/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@szxeml508-mbx.china.huawei.com

>As far as I can see, you never came back with the different solution.

Yeah right. So for this I tried to search archived mails to get the details 
about the discussion but I could not find anything regarding design.
So I am not sure how shall I make my solution different from earlier as earlier 
solution is not accessible to me. Any help regarding this will be really great 
help to me.

Also from the current Autonomous transaction discussion thread (including 
ca+u5nmkeum4abrqbndlyt5ledektae8rbiyw3977yhmeowq...@mail.gmail.com),
I have summarized all important feedbacks as mentioned below along with the 
resolution suggested:


1.  Pavel Stehule (07-04-2014): -1 for Oracle syntax - it is hardly 
inconsistent with Postgres

Changed the syntax to “START AUTONOMOUS TRANSACTION”

2.  Pavan (10-04-2014): Making autonomous transaction properties 
independent of main transaction.
Made all properties of autonomous transaction (including read-only) independent 
from main transaction except isolation level, which I did not find very useful 
to keep different. But others opinion is different then we can make this 
property also independent.

3.  Alvaro Herrarta (09-04-2014): Autonomous transaction to have their own 
separate proc entry.
This was concluded to not have separate proc entry for autonomous transaction 
and same concluded.

4.  Tom Lane (09-04-2014): The point being that you need to change both 
pg_subtrans and pg_clog to make that state transition.
This is handled for autonomous transaction.

5.  Robert Haas (09-04-2014): Not in favour of current design related to 
"maintaining lockmask for autonomous transaction".

I had replied for this mail regarding why this design is kept but still if 
design for this part is not acceptable, then I can rework to make it better. In 
order to do so I would be very happy to have more discussion to get concrete 
feedback and direction to improve this.

6.  Tom Lane (09-04-2014): no justification for distinguishing normal and 
autonomous transactions at this level (locking level).
I had replied this also earlier. Reason for distinguishing at this level is to 
handle any kind of deadlock possibility between main and autonomous 
transaction. Deadlock handling between main and autonomous transaction was one 
of the requirement discussed at PGCon 2012 as part of autonomous transaction 
discussion.  Please let me know if I am missing something in this.

All of the above mentioned changes are included in latest patch shared.
Please let me know if I have missed any other important points from the earlier 
discussion, I would like to address that also.
>Have you checked the discussion in Developer meeting notes. Please
>check the same at below link:
>http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions

From the discussion, I am able to make out two important points:

1.  Main transaction and autonomous transaction should be independent and 
can conflict.

This is already included in our latest patch.

2.  Utility commands like VACUUM and CREATE INDEX CONCURRENTLY should be 
able to work from autonomous transaction.

Both of the above mentioned utility commands are not supported even inside the 
main transaction. So it is not working within autonomous transaction.

Any opinion about this?
Please let me know if I have missed any points from the link given.


>> So I wanted to have this patch in commitfest application, so that we can 
>> have a healthy discussion and rectify all the issues.
>> But now I see that this patch has already been moved to rejected category, 
>> which will put break on further review.
>I believe ideally this patch should have been marked as
>"Returned with feedback" as you already got a feedback long
>back and never come up with solution for same.

Since this patch is very big and complex, it is better we continue discussing 
from the first CommitFest itself so that we can get ample time to share 
everyone’s opinion and then address all possible issue.

Any Opinions/Suggestions are welcome. Also let me know if I have missed 
something.


Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-07-01 10:38 GMT+02:00 Rajeev rastogi :

>  On 01 July 2014 12:26, Pavel Stehule Wrote:
>
>
>
> >>Have you checked the discussion in Developer meeting notes. Please
>
> >>check the same at below link:
>
> >>
> http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions
>
>
>
> >Are these notes still valid?
>
> >* Why autonomous transaction should be close to  functions? We can
> implement AT as first step and next step can be implementation of
> integration AT to stored procedures.
>
> We have implemented AT on the line of sub-transaction. Also we have
> integrated AT with stored procedure i.e. we can create an autonomous
> transaction inside the store procedure, which can be also committed.
>
> >* When autonomous transaction is independent on parent transaction, then
> locks parent and autonomous transaction should be in conflict
>
> Yes our implementation makes the autonomous transaction independent of
> main transaction and hence as per our design parent (main) transaction and
> autonomous may get conflicted.  For which we have implemented deadlock
> detection mechanism between autonomous transaction and its parent
> transaction.
>
> > I though about integration to PL/pgSQL and I don't think so close
> integration between autonomous transaction and procedure is optimal. More
> practical is design so autonomous transaction is similar to subtransaction.
>
> Yes as mentioned above, our implementation of autonomous transaction is on
> track of subtransaction.
>

ok

>  >Then we can simply wrote some code like
>
> >  BEGIN
>
> >.. some code
>
> >  WHEN OTHERS THEN
>
> >.. I would to write permanently to log
>
> >BEGIN AUTONOMOUS
>
> >  INSERT INTO log VALUES(..);
>
> >WHEN OTHERS
>
> >  RAISE WARNING 'Cannot to write to log ..';
>
> >  RAISE EXCEPTION ' ...' forward up exception from autonomous
> transaction to parent transaction
>
> >END
>
> >  END;
>
> >Now I am thinking so PL/SQL design of autonomous transactions is
> relatively limited and is not best to follow it.
>
> With our approach, we can use autonomous transaction in procedure as given
> below:
>
>   BEGIN
>
> .. some code
>
>   WHEN OTHERS THEN
>
> .. I would to write permanently to log
>
> *START AUTONOMOUS TRANSACTION*
>
>   INSERT INTO log VALUES(..);
>
>*COMMIT:  *
>
> WHEN OTHERS
>
>   RAISE WARNING 'Cannot to write to log ..';
>
>   RAISE EXCEPTION ' ...' forward up exception from autonomous
> transaction to parent transaction
>
> END
>
>   END;
>
I don't like this design (really) - it can be used in later implementation
of procedures - but I don't like a explicit transaction manipulation in
functions. It is Oracleism (and this part I don't want to follow, because
Oracle design is not lucky)  - and it is one reason, why Oracle SP are
significantly complex than PostgreSQL SP. After all I am thinking so
PostgreSQL relation between transactions and procedures are better, simply
for usage, simply for learning.  But it is little bit different topic.

Regards

Pavel





>  Please let me know if I have missed to answer any of your queries.
>
> Thanks and Regards,
>
> Kumar Rajeev Rastogi
>
>
>
>
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 01 July 2014 12:26, Pavel Stehule Wrote:

>>Have you checked the discussion in Developer meeting notes. Please
>>check the same at below link:
>>http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions

>Are these notes still valid?
>* Why autonomous transaction should be close to  functions? We can implement 
>AT as first step and next step can be implementation of integration AT to 
>stored procedures.
We have implemented AT on the line of sub-transaction. Also we have integrated 
AT with stored procedure i.e. we can create an autonomous transaction inside 
the store procedure, which can be also committed.
>* When autonomous transaction is independent on parent transaction, then locks 
>parent and autonomous transaction should be in conflict
Yes our implementation makes the autonomous transaction independent of main 
transaction and hence as per our design parent (main) transaction and 
autonomous may get conflicted.  For which we have implemented deadlock 
detection mechanism between autonomous transaction and its parent transaction.
> I though about integration to PL/pgSQL and I don't think so close integration 
> between autonomous transaction and procedure is optimal. More practical is 
> design so autonomous transaction is similar to subtransaction.
Yes as mentioned above, our implementation of autonomous transaction is on 
track of subtransaction.
>Then we can simply wrote some code like
>  BEGIN
>.. some code
>  WHEN OTHERS THEN
>.. I would to write permanently to log
>BEGIN AUTONOMOUS
>  INSERT INTO log VALUES(..);
>WHEN OTHERS
>  RAISE WARNING 'Cannot to write to log ..';
>  RAISE EXCEPTION ' ...' forward up exception from autonomous transaction 
> to parent transaction
>END
>  END;
>Now I am thinking so PL/SQL design of autonomous transactions is relatively 
>limited and is not best to follow it.
With our approach, we can use autonomous transaction in procedure as given 
below:
  BEGIN
.. some code
  WHEN OTHERS THEN
.. I would to write permanently to log
START AUTONOMOUS TRANSACTION
  INSERT INTO log VALUES(..);
   COMMIT:
WHEN OTHERS
  RAISE WARNING 'Cannot to write to log ..';
  RAISE EXCEPTION ' ...' forward up exception from autonomous transaction 
to parent transaction
END
  END;
Please let me know if I have missed to answer any of your queries.
Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-07-01 8:29 GMT+02:00 Amit Kapila :

> On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi 
> wrote:
> > On 30 June 2014 22:50, Pavel Stehule Wrote:
> >
> > >I didn't find a related message.
> > >?
> >
> > I think there have been some confusion, the design idea were never
> rejected but yes there were few feedback/ concern, which I had clarified.
> Also some of the other concerns are already fixed in latest patch.
>
> Simon has mentioned that exactly this idea has been rejected at
> PGCon 2 years back. Please refer that in below mail:
>
> http://www.postgresql.org/message-id/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@szxeml508-mbx.china.huawei.com
>
> As far as I can see, you never came back with the different solution.
>
> Have you checked the discussion in Developer meeting notes. Please
> check the same at below link:
>
> http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions
>

Are these notes still valid?

* Why autonomous transaction should be close to  functions? We can
implement AT as first step and next step can be implementation of
integration AT to stored procedures.

* When autonomous transaction is independent on parent transaction, then
locks parent and autonomous transaction should be in conflict

I though about integration to PL/pgSQL and I don't think so close
integration between autonomous transaction and procedure is optimal. More
practical is design so autonomous transaction is similar to subtransaction.

Then we can simply wrote some code like

  BEGIN
.. some code
  WHEN OTHERS THEN
.. I would to write permanently to log
BEGIN AUTONOMOUS
  INSERT INTO log VALUES(..);
WHEN OTHERS
  RAISE WARNING 'Cannot to write to log ..';
  RAISE EXCEPTION ' ...' forward up exception from autonomous
transaction to parent transaction
END
  END;

Now I am thinking so PL/SQL design of autonomous transactions is relatively
limited and is not best to follow it.

Regards

Pavel


>
> > So I wanted to have this patch in commitfest application, so that we can
> have a healthy discussion and rectify all the issues.
> > But now I see that this patch has already been moved to rejected
> category, which will put break on further review.
>
> I believe ideally this patch should have been marked as
> "Returned with feedback" as you already got a feedback long
> back and never come up with solution for same.
>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Amit Kapila]

On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi 
wrote:
> On 30 June 2014 22:50, Pavel Stehule Wrote:
>
> >I didn't find a related message.
> >?
>
> I think there have been some confusion, the design idea were never
rejected but yes there were few feedback/ concern, which I had clarified.
Also some of the other concerns are already fixed in latest patch.

Simon has mentioned that exactly this idea has been rejected at
PGCon 2 years back. Please refer that in below mail:
http://www.postgresql.org/message-id/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@szxeml508-mbx.china.huawei.com

As far as I can see, you never came back with the different solution.

Have you checked the discussion in Developer meeting notes. Please
check the same at below link:
http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions


> So I wanted to have this patch in commitfest application, so that we can
have a healthy discussion and rectify all the issues.
> But now I see that this patch has already been moved to rejected
category, which will put break on further review.

I believe ideally this patch should have been marked as
"Returned with feedback" as you already got a feedback long
back and never come up with solution for same.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-07-01 8:16 GMT+02:00 Rajeev rastogi :

>  On 30 June 2014 22:50, Pavel Stehule Wrote:
>
> 2014-06-30 12:38 GMT+02:00 Abhijit Menon-Sen :
>
> >>If I understand correctly, the design of this patch has already been
> >>considered earlier and rejected. So I guess the patch should also be
> >>marked rejected?
>
>
>
> >I didn't find a related message.
> >?
>
> I think there have been some confusion, the design idea were never
> rejected but yes there were few feedback/ concern, which I had clarified.
> Also some of the other concerns are already fixed in latest patch.
>
> So I wanted to have this patch in commitfest application, so that we can
> have a healthy discussion and rectify all the issues.
>
> But now I see that this patch has already been moved to rejected category,
> which will put break on further review.
>
> So is there any way to bring back and continue reviewing this patch.
>
> Please let me know if any issue or I am missing something.
>

I didn't watch a discuss about internal implementation, but now, when I am
testing this feature - it works well.

Surely - this feature has important but with relatively large impact and
should be extremely well tested. Now there are no any special test.
Probably we can reuse a tests for nested transactions.

I prefer this feature will be part of first commitfest due high complexity.

Regards

Pavel


>
>
> *Thanks and Regards,*
>
> *Kumar Rajeev Rastogi*
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 30 June 2014 22:50, Pavel Stehule Wrote:
2014-06-30 12:38 GMT+02:00 Abhijit Menon-Sen 
mailto:a...@2ndquadrant.com>>:
>>If I understand correctly, the design of this patch has already been
>>considered earlier and rejected. So I guess the patch should also be
>>marked rejected?

>I didn't find a related message.
>?
I think there have been some confusion, the design idea were never rejected but 
yes there were few feedback/ concern, which I had clarified. Also some of the 
other concerns are already fixed in latest patch.
So I wanted to have this patch in commitfest application, so that we can have a 
healthy discussion and rectify all the issues.
But now I see that this patch has already been moved to rejected category, 
which will put break on further review.
So is there any way to bring back and continue reviewing this patch.
Please let me know if any issue or I am missing something.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-06-30 12:38 GMT+02:00 Abhijit Menon-Sen :

> If I understand correctly, the design of this patch has already been
> considered earlier and rejected. So I guess the patch should also be
> marked rejected?
>

I didn't find a related message.

?

Regards

Pavel


>
> -- Abhijit
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Abhijit Menon-Sen]

If I understand correctly, the design of this patch has already been
considered earlier and rejected. So I guess the patch should also be
marked rejected?

-- Abhijit


-- 
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 Transaction (WIP)

[Pavel Stehule]

Hello

There are lot of unnecessary block over one statement in code

+   if ((inAutoX) && (chunk == events->head) && ((char *)event
< afterTriggers->events_stack[my_level].tailfree))
+   {
+   continue;
+   }
+


and there a few too long lines

Regards

Pavel


2014-06-24 18:40 GMT+02:00 Pavel Stehule :

> postgres=# select version();
>
> version
>
> -
>  PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.8.2 20131212 (Red Hat 4.8.2-7), 64-bit
> (1 row)
>
>
>
> 2014-06-24 18:39 GMT+02:00 Pavel Stehule :
>
> Hello
>>
>> regress tests fails:
>>
>>  plancache... ok
>>  limit... ok
>>  plpgsql  ... ok
>>  copy2... ok
>>  temp ... FAILED
>>  domain   ... ok
>>  rangefuncs   ... ok
>>  prepare  ... ok
>>  without_oid  ... ok
>>  conversion   ... ok
>>  truncate ... ok
>>  alter_table  ... ok
>>  sequence ... ok
>>
>> I did some small tests and it works well. When I looked to code, I was
>> surprised by hardcoded max nesting level of autonomous transactions
>>
>> #define MAX_AUTOX_NESTING_LEVEL   3
>>
>> why? Is not it too restrictive?
>>
>> I am missing a regress tests.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>
>>
>> 2014-06-18 11:19 GMT+02:00 Rajeev rastogi :
>>
>>> On 17 June 2014 02:01, Alvaro Herrera Wrote:
>>>
>>> > What's the status of this patch?
>>>
>>> I have completed work on this and some more changes are done on top of
>>> earlier patch shared:
>>> 1. Fixed all of the issues observed.
>>> 2. Addressed some of the feedback from community like
>>> a. Change the syntax to
>>> START AUTONOMOUS TRANSACTION [READ ONLY | READ
>>> WRITE]
>>> b. As Pavan had pointed, I have made transaction behavior (only
>>> read-only properties) of main and autonomous transaction independent.
>>> 3. Added documentation for this feature.
>>> 4. Rebased to latest git code.
>>>
>>> Please find the attached latest patch and provide opinion.
>>>
>>> Thanks and Regards,
>>> Kumar Rajeev Rastogi
>>>
>>>
>>>
>>> --
>>> 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 Transaction (WIP)

[Pavel Stehule]

postgres=# select version();

version
-
 PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.8.2 20131212 (Red Hat 4.8.2-7), 64-bit
(1 row)



2014-06-24 18:39 GMT+02:00 Pavel Stehule :

> Hello
>
> regress tests fails:
>
>  plancache... ok
>  limit... ok
>  plpgsql  ... ok
>  copy2... ok
>  temp ... FAILED
>  domain   ... ok
>  rangefuncs   ... ok
>  prepare  ... ok
>  without_oid  ... ok
>  conversion   ... ok
>  truncate ... ok
>  alter_table  ... ok
>  sequence ... ok
>
> I did some small tests and it works well. When I looked to code, I was
> surprised by hardcoded max nesting level of autonomous transactions
>
> #define MAX_AUTOX_NESTING_LEVEL   3
>
> why? Is not it too restrictive?
>
> I am missing a regress tests.
>
> Regards
>
> Pavel
>
>
>
>
>
>
> 2014-06-18 11:19 GMT+02:00 Rajeev rastogi :
>
>> On 17 June 2014 02:01, Alvaro Herrera Wrote:
>>
>> > What's the status of this patch?
>>
>> I have completed work on this and some more changes are done on top of
>> earlier patch shared:
>> 1. Fixed all of the issues observed.
>> 2. Addressed some of the feedback from community like
>> a. Change the syntax to
>> START AUTONOMOUS TRANSACTION [READ ONLY | READ
>> WRITE]
>> b. As Pavan had pointed, I have made transaction behavior (only
>> read-only properties) of main and autonomous transaction independent.
>> 3. Added documentation for this feature.
>> 4. Rebased to latest git code.
>>
>> Please find the attached latest patch and provide opinion.
>>
>> Thanks and Regards,
>> Kumar Rajeev Rastogi
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>>
>


temp.out
Description: Binary data

-- 
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 Transaction (WIP)

[Pavel Stehule]

Hello

regress tests fails:

 plancache... ok
 limit... ok
 plpgsql  ... ok
 copy2... ok
 temp ... FAILED
 domain   ... ok
 rangefuncs   ... ok
 prepare  ... ok
 without_oid  ... ok
 conversion   ... ok
 truncate ... ok
 alter_table  ... ok
 sequence ... ok

I did some small tests and it works well. When I looked to code, I was
surprised by hardcoded max nesting level of autonomous transactions

#define MAX_AUTOX_NESTING_LEVEL   3

why? Is not it too restrictive?

I am missing a regress tests.

Regards

Pavel






2014-06-18 11:19 GMT+02:00 Rajeev rastogi :

> On 17 June 2014 02:01, Alvaro Herrera Wrote:
>
> > What's the status of this patch?
>
> I have completed work on this and some more changes are done on top of
> earlier patch shared:
> 1. Fixed all of the issues observed.
> 2. Addressed some of the feedback from community like
> a. Change the syntax to
> START AUTONOMOUS TRANSACTION [READ ONLY | READ
> WRITE]
> b. As Pavan had pointed, I have made transaction behavior (only
> read-only properties) of main and autonomous transaction independent.
> 3. Added documentation for this feature.
> 4. Rebased to latest git code.
>
> Please find the attached latest patch and provide opinion.
>
> Thanks and Regards,
> Kumar Rajeev Rastogi
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>


regression.diffs
Description: Binary data

-- 
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 Transaction (WIP)

[Alvaro Herrera]

What's the status of this patch?

-- 
Álvaro Herrerahttp://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 Transaction (WIP)

[Rajeev rastogi]

On 14 April 2014 20:10, Simon Riggs wrote:

>>Autonomous Transaction Storage:
>>As for main transaction, structure PGXACT is used to store main transactions, 
>>which are created in shared memory of size:
>>   (Number of process)*sizeof(struct PGXACT)
>>Similarly a new structure will be defined to store autonomous transaction:
>>   Struct PGAutonomousXACT

Oh...I had already added this patch for 2014-June CommitFest, thinking that 
everyone is busy with  work to wrap up 9.4.

> I already proposed exactly this design two years ago and it was rejected at 
> the PgCon hackers meeting.
> I have a better design worked out now and will likely be working on it for 9.5

Can we work together to take this feature to final goal.
May be you can go through my complete patch and see whatever part of the patch 
and related design can be re-used along with your new design.
Also if possible you can share your design (even rough is OK), I will see if I 
can contribute to that in some-way.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Simon Riggs]

On 7 April 2014 05:06, Rajeev rastogi  wrote:


> *Autonomous Transaction Storage:*
>
> As for main transaction, structure PGXACT is used to store main
> transactions, which are created in shared memory of size:
>
> (Number of process)*sizeof(struct PGXACT)
>
> Similarly a new structure will be defined to store autonomous transaction:
>
> *Struct PGAutonomousXACT*
>

I already proposed exactly this design two years ago and it was rejected at
the PgCon hackers meeting.

I have a better design worked out now and will likely be working on it for
9.5

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

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 21:25, Robert Haas Wrote:

> >> > Deadlock Detection:
> >> I'm not sure how this would work out internally
> > In order to resolve deadlock, two member variable will be created in
> the structure PROLOCK:
> > Bitmask for lock types currently held by autonomous
> transaction.
> > LOCKMASKholdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
> > Bitmask for lock types currently held by main transaction.
> > LOCKMASKholdMaskByNormalTx
> >
> > Now when we grant the lock to particular transaction, depending on
> > type of transaction, bit Mask will be set for either holdMaskByAutoTx
> or holdMaskByNormalTx.
> > Similar when lock is ungranted, corresponding bitmask will be reset.
> 
> That sounds pretty ugly, not to mention the fact that it will cause a
> substantial increase in the amount of memory required to store
> PROCLOCKs.  It will probably slow things down, too.

Actually I followed above design to keep it align with the existing design. As 
I understand, currently also
all lock conflict is checked based on the corresponding lock bit mask. 

This is good catch that shared memory required will increase but isn't it 
justified from user perspective
since we are allowing more transactions per session and hence memory required 
to store various kind of resources 
will increase.

Since we are just additionally setting the bitmask for each lock (in-case there 
is autonomous transaction, then there will
be one more additional bit mask setting and deadlock check), I don't think it 
should slow down the overall operation. 

Also We can keep number of autonomous transaction configurable(default-0), to 
keep it less impacting incase it is not configured.

An autonomous transaction can also conflict with main transaction, so in order 
to check conflict between them, 
I am distinguishing at this level.

Please correct me If I am wrong anywhere and also please provide your thought 
on this and on overall design.

Thanks and Regards,
Kumar Rajeev Rastogi


-- 
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 Transaction (WIP)

[Rajeev rastogi]

On 10 April 2014 11:18, Pavan Deolasee Wrote:
>>I could think of few  global variables like transaction properties 
>>related(i.e. read-only mode, isolation level etc). As I plan to keep 
>>transaction properties of autonomous transaction same as main transaction, so 
>>there is no need to have these global variables separately.
>>Apart from this there are global variables like with-in transaction counters, 
>>GUC, xactStartTimeStamp. I think there is no need to maintain these variables 
>>also separately. They can continue from previous value for autonomous 
>>transaction also similar to as sub->>transaction does.

>Hmm. Is that in line with what other databases do ? I would have preferred AT 
>to run like a standalone transaction without any influence of the starting 
>transaction, managing its own resources/locks/visibility/triggers etc.

To me it seems it is not very useful to keep the transaction properties 
separate except the read-only properties (though oracle does not share any 
transaction properties).

So we can have restriction that isolation and deferrable properties of main 
transaction will be inherited by autonomous transaction but read-only 
properties can be defined independently by autonomous transaction. Which looks 
to be fair restriction according to me.

In order to keep read-only properties separate, there is already infrastructure 
in PG. Inside the structure TransactionStateData, there is variable 
prevXactReadOnly (entry-time xact r/o state), which can keep the parent 
transaction read only properties and XactReadOnly can be changed to current 
transaction properties.
Moreover we can take this (transaction 
properties) as a feature enhancement also once a basic infrastructure is 
established, if acceptable to everyone.

Autonomous transaction will not share resource/lock/visibility etc with main 
transaction. This has been already taken care in WIP patch.
>>In-case of autonomous transaction, only specific global variables initialized 
>>are related to resources (similar to sub-transaction), which anyway  gets 
>>stored in current transaction state.
>>Please let me know if I am missing something or if you have some specific 
>>global variables related issue.
>No, I don't have any specific issues in mind. Mostly all such global state is 
>managed through various AtStart/AtEOX and related routines. So a careful 
>examination of all those routines will give a good idea what needs to be 
>handled. You probably will require to write
>AtATStart/AtATEOX and similar routines to manage the state at AT 
>start/commit/rollback. Sorry, I haven't looked at your WIP patch yet.

For some of the resources, I have already written AtATStart/AtATEOX kind of 
routines in WIP patch.

Comments/feedbacks/doubts are welcome.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavan Deolasee]

On Thu, Apr 10, 2014 at 10:44 AM, Rajeev rastogi
wrote:

>  On 09 April 2014 12:14, Pavan Deolasee Wrote:
>
>  >Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT will
> have same characteristics as a top level transaction, I
>
> >wonder how do you plan to separate those global state variables ? Sure,
> we can group them in a structure and put them on a stack when an AT starts
> and pop them off when the original top transaction becomes active again,
> finding all such global state variables is
>
> >going to be tricky.
>
>
>
> I could think of few  global variables like transaction properties
> related(i.e. read-only mode, isolation level etc). As I plan to keep
> transaction properties of autonomous transaction same as main transaction,
> so there is no need to have these global variables separately.
>
> Apart from this there are global variables like with-in transaction
> counters, GUC, xactStartTimeStamp. I think there is no need to maintain
> these variables also separately. They can continue from previous value for
> autonomous transaction also similar to as sub-transaction does.
>
>
>

Hmm. Is that in line with what other databases do ? I would have preferred
AT to run like a standalone transaction without any influence of the
starting transaction, managing its own resources/locks/visibility/triggers
etc.


>  In-case of autonomous transaction, only specific global variables
> initialized are related to resources (similar to sub-transaction), which
> anyway  gets stored in current transaction state.
>
>
>
> Please let me know if I am missing something or if you have some specific
> global variables related issue.
>
>
>

No, I don't have any specific issues in mind. Mostly all such global state
is managed through various AtStart/AtEOX and related routines. So a careful
examination of all those routines will give a good idea what needs to be
handled. You probably will require to write AtATStart/AtATEOX and similar
routines to manage the state at AT start/commit/rollback. Sorry, I haven't
looked at your WIP patch yet.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 12:14, Pavan Deolasee Wrote:

>Whenever I was asked to have a look at implementing this feature, I always 
>wondered about the great amount of global state that a backend maintains which 
>is normally tied to a single top transaction. Since AT will have same 
>characteristics as a top level transaction, I
>wonder how do you plan to separate those global state variables ? Sure, we can 
>group them in a structure and put them on a stack when an AT starts and pop 
>them off when the original top transaction becomes active again, finding all 
>such global state variables is
>going to be tricky.

I could think of few  global variables like transaction properties related(i.e. 
read-only mode, isolation level etc). As I plan to keep transaction properties 
of autonomous transaction same as main transaction, so there is no need to have 
these global variables separately.
Apart from this there are global variables like with-in transaction counters, 
GUC, xactStartTimeStamp. I think there is no need to maintain these variables 
also separately. They can continue from previous value for autonomous 
transaction also similar to as sub-transaction does.

In-case of autonomous transaction, only specific global variables initialized 
are related to resources (similar to sub-transaction), which anyway  gets 
stored in current transaction state.

Please let me know if I am missing something or if you have some specific 
global variables related issue.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Tom Lane]

Robert Haas  writes:
> On Wed, Apr 9, 2014 at 12:24 AM, Rajeev rastogi
>  wrote:
>> Now when we grant the lock to particular transaction, depending on type of 
>> transaction, bit
>> Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
>> Similar when lock is ungranted, corresponding bitmask will be reset.

> That sounds pretty ugly, not to mention the fact that it will cause a
> substantial increase in the amount of memory required to store
> PROCLOCKs.  It will probably slow things down, too.

More to the point, why isn't it a flat-out bad idea?  I can see no
justification for distinguishing normal and autonomous transactions
at this level.

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 Transaction (WIP)

[Robert Haas]

On Wed, Apr 9, 2014 at 12:24 AM, Rajeev rastogi
 wrote:
>> > Deadlock Detection:
>> I'm not sure how this would work out internally
> In order to resolve deadlock, two member variable will be created in the 
> structure PROLOCK:
> Bitmask for lock types currently held by autonomous transaction.
> LOCKMASKholdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
> Bitmask for lock types currently held by main transaction.
> LOCKMASKholdMaskByNormalTx
>
> Now when we grant the lock to particular transaction, depending on type of 
> transaction, bit
> Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
> Similar when lock is ungranted, corresponding bitmask will be reset.

That sounds pretty ugly, not to mention the fact that it will cause a
substantial increase in the amount of memory required to store
PROCLOCKs.  It will probably slow things down, too.

-- 
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 Transaction (WIP)

[Heikki Linnakangas]

On 04/09/2014 09:55 AM, Hannu Krosing wrote:

This would be greatly simplified if we can accept the restriction that
there is only single
snapshot per backend (not per transaction). To me this seems a
completely sensible restriction.


Huh? In Read committed mode, every query within a transaction gets a 
different snapshot.


- Heikki


--
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 Transaction (WIP)

[Craig Ringer]

On 04/09/2014 02:44 PM, Pavan Deolasee wrote:
> On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
> mailto:rajeev.rast...@huawei.com>> wrote:
> 
> 
> Though autonomous transaction uses mixed approach of sub-transaction
> as well as main
> transaction, transaction state of autonomous transaction is handled
> independently.
> 
> 
> Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT
> will have same characteristics as a top level transaction, I wonder how
> do you plan to separate those global state variables ? Sure, we can
> group them in a structure and put them on a stack when an AT starts and
> pop them off when the original top transaction becomes active again,
> finding all such global state variables is going to be tricky.

... not to mention the fact that extensions may rely on having their own
global state.

-- 
 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 Transaction (WIP)

[Hannu Krosing]

On 04/09/2014 08:44 AM, Pavan Deolasee wrote:
> On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
> mailto:rajeev.rast...@huawei.com>> wrote:
>
>
> Though autonomous transaction uses mixed approach of
> sub-transaction as well as main
> transaction, transaction state of autonomous transaction is
> handled independently.
>
>
> Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT
> will have same characteristics as a top level transaction, I wonder
> how do you plan to separate those global state variables ? Sure, we
> can group them in a structure and put them on a stack when an AT
> starts and pop them off when the original top transaction becomes
> active again, finding all such global state variables is going to be
> tricky.
I would hope most of this to be solved by having one (read only) virtual
transaction and
then juggling the ATs in a way similar to current subtransaction machinery.

The main differences would be that:

 A) the top level transaction stays virtual

and

 B) ATs are committed independantly

This would be greatly simplified if we can accept the restriction that
there is only single
snapshot per backend (not per transaction). To me this seems a
completely sensible restriction.

Re syntax, I think we need a way to name the transactions so we can have
a way
to switch between multiple parallel active autonomous transactions.

-
BEGIN TRANSACTION myfirsttransaction;

do something in myfirsttransaction;

BEGIN TRANSACTION anothertransaction;

do something in anothertransaction;

SET TRANSACTION myfirsttransaction;

more work in myfirsttransaction;

ROLLBACK anothertransaction;

COMMIT; -- or COMMIT myfirsttransaction;


Cheers
Hannu


>
> Thanks,
> Pavan
>
> -- 
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavan Deolasee]

On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
wrote:

>
> Though autonomous transaction uses mixed approach of sub-transaction as
> well as main
> transaction, transaction state of autonomous transaction is handled
> independently.
>
>
Whenever I was asked to have a look at implementing this feature, I always
wondered about the great amount of global state that a backend maintains
which is normally tied to a single top transaction. Since AT will have same
characteristics as a top level transaction, I wonder how do you plan to
separate those global state variables ? Sure, we can group them in a
structure and put them on a stack when an AT starts and pop them off when
the original top transaction becomes active again, finding all such global
state variables is going to be tricky.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 01:43, Tom Lane Wrote:

> > I'm also pretty unconvinced that multiple PGPROCs is the right way to
> > go.  First, PGPROCs have a bunch of state in them that is assumed to
> > exist once per backend.  We might find pretty substantial code churn
> > there if we try to go change that.  Second, why do other backends
> > really need to know about our ATs?  As far as I can see, if other
> > backends see the AT as a subtransaction of our top-level transaction
> > up until it actually commits, that ought to be just fine.
> 
> If we can make it work like that, sure.  I'm a bit worried about how
> you'd decouple a subtransaction and commit it atomically ... or if
> that's not atomic, will it create any problems?  

Though autonomous transaction uses mixed approach of sub-transaction as well as 
main
transaction, transaction state of autonomous transaction is handled 
independently.
So depending on the transaction state of autonomous transaction (for commit 
TBLOCK_AUTOCOMMIT), 
this transaction will be committed. While committing:
1.  Commit of record and logging the corresponding WAL happens in the same 
way as main transaction (except the way autonomous transaction and their 
sub-transaction accessed).
This will take care automatically of updating pg_clog also for 
autonomous transaction.
2.  Also it marks the autonomous transaction finish by setting appropriate 
fields of MyPgAutonomousXact in similar manner as done for main transaction.
3.  Freeing of all resource and popping out of parent transaction happens 
in the same way as sub-transaction.

> The point being that
> you need to change both pg_subtrans and pg_clog to make that state
> transition.

Yes I am changing both. But no specific changes were required. During commit 
and assignment of autonomous transaction, it is automatically taken care. 

Any comment/feedback/doubt are welcome?

Thanks and Regards,
Kumar Rajeev Rastogi








-- 
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 Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 01:09, Rover Haas Wrote:
 
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go.  First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend.  We might find pretty substantial code churn
> there if we try to go change that.  

Yes you right. That is why I am not creating a separate procarray entry to 
maintain autonomous transaction. Please find details in previous reply sent 
today sometime back.

> Second, why do other backends 
> really need to know about our ATs?  As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction up
> until it actually commits, that ought to be just fine.  Maybe the
> backend needs to internally frob visibility rules, but that's not a
> matter for shared memory.

In order to get snapshot from other session, it will be required by other 
session to access autonomous transaction and their sub-transactions.

During snapshot creation, autonomous transaction is considered as main
transaction and list of all running autonomous transaction and their 
sub-transaction
gets stored in snapshot data.

e.g. Suppose below processes are running with given transactions:

Proc-1: 100
Proc-2: 101, 102 (Auto Tx1), 103 (Auto Tx2), 104 (Sub-tx of Auto Tx2)
Proc-3: 105, 106 (Auto Tx2), 107 (Auto Tx2)

Suppose latest completed transaction is 108.

Then Snapshot data for autonomous transaction 107 will be as below:
Xmin: 100
Xmax: 109
Snapshot->xip[]:  100, 101, 102, 103, 105, 106  

Snapshot->subxip[]:   104

Thanks and Regards,
Kumar Rajeev Rastogi




-- 
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 Transaction (WIP)

[Andres Freund]

On 2014-04-08 16:13:21 -0400, Tom Lane wrote:
> Robert Haas  writes:
> > I'm also pretty unconvinced that multiple PGPROCs is the right way to
> > go.  First, PGPROCs have a bunch of state in them that is assumed to
> > exist once per backend.  We might find pretty substantial code churn
> > there if we try to go change that.  Second, why do other backends
> > really need to know about our ATs?  As far as I can see, if other
> > backends see the AT as a subtransaction of our top-level transaction
> > up until it actually commits, that ought to be just fine.
> 
> If we can make it work like that, sure.  I'm a bit worried about how you'd
> decouple a subtransaction and commit it atomically ... or if that's not
> atomic, will it create any problems?  The point being that you need to
> change both pg_subtrans and pg_clog to make that state transition.

I think it can be made work sensibly - while those states are changed it
will still appear to be running via the procarray. There's some fun
around suboverflowed entries, but I think that can be handled by
reserving an entry for autonomous transactions.

Greetings,

Andres Freund

-- 
 Andres Freund 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 Transaction (WIP)

[Tom Lane]

Robert Haas  writes:
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go.  First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend.  We might find pretty substantial code churn
> there if we try to go change that.  Second, why do other backends
> really need to know about our ATs?  As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction
> up until it actually commits, that ought to be just fine.

If we can make it work like that, sure.  I'm a bit worried about how you'd
decouple a subtransaction and commit it atomically ... or if that's not
atomic, will it create any problems?  The point being that you need to
change both pg_subtrans and pg_clog to make that state transition.

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 Transaction (WIP)

[Andres Freund]

On 2014-04-08 15:39:18 -0400, Robert Haas wrote:
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go.  First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend.  We might find pretty substantial code churn
> there if we try to go change that.  Second, why do other backends
> really need to know about our ATs?  As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction
> up until it actually commits, that ought to be just fine.  Maybe the
> backend needs to internally frob visibility rules, but that's not a
> matter for shared memory.

Agreed. That's also how I imagined things to work.

I think except the visibility semantics, there's really not that much to
do if we were to reuse the subtransaction framework. There's some
complications with Hot Standby, but I think those can be solved.

Greetings,

Andres Freund

-- 
 Andres Freund 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 Transaction (WIP)

[Robert Haas]

On Tue, Apr 8, 2014 at 2:43 PM, Alvaro Herrera  wrote:
> Greg Stark wrote:
>> If the autonomous transaction is actually a separate procarray entry
>> (which I suspect it would have to be, much like prepared transactions
>> and the dblink connections which are commonly used to kludge
>> autonomous transactions) then this should be fairly painless. If you
>> implement some kind of saving and restoring procarray data then it
>> probably wouldn't work out.
>
> I don't have time to digest this proposal ATM, but in previous occasion
> when we have discussed autonomous transactions (ATs), we have always
> considered natural that they have their own procarray entries; there are
> too many strange issues otherwise.
>
> Since the number of procarray entries is fixed at startup time, one
> natural consequence of this is that the number of ATs in flight at any
> moment is also fixed.  Normally we consider allocating a single AT per
> session to be sufficient.  So you can't have one AT start another AT,
> for instance -- that seems a reasonable restriction.

It depends.  A lot of Oracle users are used to having autonomous
transactions be very cheap, so you can just mark random procedures as
running in an autonomous transaction and forget about it.  If the call
stack is several levels deep, then you could easily have one such
procedure call another such procedure.  Of course, you may feel that's
bad practice or that we shouldn't emulate what $COMPETITOR does, and I
agree we don't have to necessarily do it that way just because they do
it that way, but I'm not sure it's accurate to say that nobody will
care.

I'm also pretty unconvinced that multiple PGPROCs is the right way to
go.  First, PGPROCs have a bunch of state in them that is assumed to
exist once per backend.  We might find pretty substantial code churn
there if we try to go change that.  Second, why do other backends
really need to know about our ATs?  As far as I can see, if other
backends see the AT as a subtransaction of our top-level transaction
up until it actually commits, that ought to be just fine.  Maybe the
backend needs to internally frob visibility rules, but that's not a
matter for shared memory.

-- 
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 Transaction (WIP)

[Alvaro Herrera]

Greg Stark wrote:

> If the autonomous transaction is actually a separate procarray entry
> (which I suspect it would have to be, much like prepared transactions
> and the dblink connections which are commonly used to kludge
> autonomous transactions) then this should be fairly painless. If you
> implement some kind of saving and restoring procarray data then it
> probably wouldn't work out.

I don't have time to digest this proposal ATM, but in previous occasion
when we have discussed autonomous transactions (ATs), we have always
considered natural that they have their own procarray entries; there are
too many strange issues otherwise.

Since the number of procarray entries is fixed at startup time, one
natural consequence of this is that the number of ATs in flight at any
moment is also fixed.  Normally we consider allocating a single AT per
session to be sufficient.  So you can't have one AT start another AT,
for instance -- that seems a reasonable restriction.

-- 
Álvaro Herrerahttp://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 Transaction (WIP)

[Greg Stark]

On Mon, Apr 7, 2014 at 12:06 AM, Rajeev rastogi
 wrote:
>
> Deadlock Detection:
>
> It is possible that the main or upper autonomous transaction has taken a lock 
> on some resource, which might be required by lower autonomous transaction. If 
> it happens so then deadlock will occur. So in order to solve this issue, each 
> main and autonomous transaction will hold list of all locks acquired in 
> PROLOCK based on which deadlock will be resolved.


I'm not sure how this would work out internally -- it would depend on
how you plan to allocate the new transaction in the internal data
structures -- but the natural way to prevent/detect deadlocks would be
to have the parent transaction immediately take a lock on the
autonomous transaction as soon as it's started. That would cause any
lock in the autonomous transaction which caused it to wait on the
parent transaction to be detected as a deadlock. It would also cause
any monitoring tool to correctly show the parent transaction as
waiting on the autonomous transaction to finish.

If the autonomous transaction is actually a separate procarray entry
(which I suspect it would have to be, much like prepared transactions
and the dblink connections which are commonly used to kludge
autonomous transactions) then this should be fairly painless. If you
implement some kind of saving and restoring procarray data then it
probably wouldn't work out.


-- 
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 Transaction (WIP)

[Pavel Stehule]

2014-04-07 12:16 GMT+02:00 Atri Sharma :

>
>
>
> On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule wrote:
>
>>
>>
>>
>> 2014-04-07 11:59 GMT+02:00 Rajeev rastogi :
>>
>>   On 07 April 2014 12:12, Pavel Stehule wrote:
>>>
>>>  >+1 for feature
>>>
>>> Thanks
>>>
>>>
>>>
>>> >-1 for Oracle syntax - it is hardly inconsistent with Postgres
>>>
>>> We can discuss and come out with the syntax based on everyone agreement.
>>>
>>> >Autonomous transactions should be used everywhere - not only in plpgsql
>>>
>>>
>>>
>>> Yes you are right. I am not planning to support only using plpgsql.
>>> Initially we can support this
>>>
>>> Using the standalone SQL-commands and then later we can enhance based on
>>> this infrastructure
>>>
>>> to be used using plpgsql, triggers.
>>>
>>
>> ok
>>
>> long time I though about this feature.
>>
>> I am thinking so this should be fully isolated transaction - it should
>> not be subtransaction, because then you can break database consistency - RI
>>
>>
>>
> I am missing something here, but how does making it a subtransaction break
> consistency? Isnt that what should actually be happening so that the
> autonomous transaction's changes are actually visible till the parent
> transaction commits?
>

commit of autonomous transaction doesn't depends on outer transaction. So
anything what you can do, should be independent on outer transaction.

Pavel



>
> What am I missing here?
>
> Regards,
>
> Atri
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Andres Freund]

On 2014-04-07 15:46:42 +0530, Atri Sharma wrote:
> On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule wrote:
> I am missing something here, but how does making it a subtransaction break
> consistency? Isnt that what should actually be happening so that the
> autonomous transaction's changes are actually visible till the parent
> transaction commits?
> 
> What am I missing here?

START TRANSACTION;
INSERT INTO referenced_to_table ... id = 1;
START AUTONOMOUS SUBTRANSACTION;
INSERT INTO referencing_table id = 1 ...;
COMMIT AUTONOMOUS SUBTRANSACTION;
ROLLBACK;

Greetings,

Andres Freund

-- 
 Andres Freund 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 Transaction (WIP)

[Atri Sharma]

On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule wrote:

>
>
>
> 2014-04-07 11:59 GMT+02:00 Rajeev rastogi :
>
>   On 07 April 2014 12:12, Pavel Stehule wrote:
>>
>>  >+1 for feature
>>
>> Thanks
>>
>>
>>
>> >-1 for Oracle syntax - it is hardly inconsistent with Postgres
>>
>> We can discuss and come out with the syntax based on everyone agreement.
>>
>> >Autonomous transactions should be used everywhere - not only in plpgsql
>>
>>
>>
>> Yes you are right. I am not planning to support only using plpgsql.
>> Initially we can support this
>>
>> Using the standalone SQL-commands and then later we can enhance based on
>> this infrastructure
>>
>> to be used using plpgsql, triggers.
>>
>
> ok
>
> long time I though about this feature.
>
> I am thinking so this should be fully isolated transaction - it should not
> be subtransaction, because then you can break database consistency - RI
>
>
>
I am missing something here, but how does making it a subtransaction break
consistency? Isnt that what should actually be happening so that the
autonomous transaction's changes are actually visible till the parent
transaction commits?

What am I missing here?

Regards,

Atri

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-04-07 11:59 GMT+02:00 Rajeev rastogi :

>  On 07 April 2014 12:12, Pavel Stehule wrote:
>
>  >+1 for feature
>
> Thanks
>
>
>
> >-1 for Oracle syntax - it is hardly inconsistent with Postgres
>
> We can discuss and come out with the syntax based on everyone agreement.
>
> >Autonomous transactions should be used everywhere - not only in plpgsql
>
>
>
> Yes you are right. I am not planning to support only using plpgsql.
> Initially we can support this
>
> Using the standalone SQL-commands and then later we can enhance based on
> this infrastructure
>
> to be used using plpgsql, triggers.
>

ok

long time I though about this feature.

I am thinking so this should be fully isolated transaction - it should not
be subtransaction, because then you can break database consistency - RI

I am happy so someone does this job

Regards

Pavel


>
>
> *Thanks and Regards,*
>
> *Kumar Rajeev Rastogi *
>
>
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 07 April 2014 12:12, Pavel Stehule wrote:

>+1 for feature
Thanks

>-1 for Oracle syntax - it is hardly inconsistent with Postgres
We can discuss and come out with the syntax based on everyone agreement.
>Autonomous transactions should be used everywhere - not only in plpgsql

Yes you are right. I am not planning to support only using plpgsql.  Initially 
we can support this
Using the standalone SQL-commands and then later we can enhance based on this 
infrastructure
to be used using plpgsql, triggers.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 07 April 2014 12:20, Craig Ringer
> 
> > Syntax to create autonomous transaction can be as:
> >
> > */PRAGMA AUTONOMOUS TRANSACTION;/*
> 
> Wouldn't you want to use SET TRANSACTION for this?
> 
> Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?
> 
> What's the logic behind introducing "PRAGMA" ?
> 
> 
> If you wanted to use that syntax for Oracle compatibility you'd need to
> use:
> 
> PRAGMA AUTONOMOUS_TRANSACTION;
> 
> (note underscore). But really, this would no be a pragma at all,
> PostgreSQL doesn't really have the concept. Calling it that would just
> be misleading.

Actually it is same as oracle (i.e. PRAGMA AUTONOMOUS_TRANSACTION), it was just 
typo mistake in previous mail.
But if this is also not accepted then we can discuss and come out with a syntax 
based on everyone agreement.

> 
> > *_Starting of Autonomous  Transaction:_*
> >
> > Starting of autonomous transaction will be exactly same as starting
> > sub-transaction.
> 
> If you don't want it to dirty read data from the parent tx, or inherit
> parent locks, then it cannot be the same at all.

While starting sub-transaction, it is just initializing the resources required 
and
links the same to the parent transaction, which we require for autonomous 
transaction also.
I am not able to notice any issue as you mentioned above with this.
Please let me know if I am missing something or misunderstood your concern.

> > 2.  Freeing of all resource and popping of previous transaction
> > happens in the same way as sub-transaction.
> 
> I'm not sure what you mean here.

It means, during commit of autonomous transaction, freeing of all resource are 
done in the same way as done for sub-transaction.
Also current autonomous transaction gets popped out and points to the parent 
transaction in the similar way as done for sub-transaction.
 
> Overall, this looks like a HUGE job to make work well. I know some
> others have been doing work along the same lines, so hopefully you'll
> be able to collaborate and share ideas.

Yes it is huge works, so I have proposed in the beginning of 9.5 so that we can 
have multiple round of discussion and hence address
all concerns.
Also I have proposed to finish this feature in multiple rounds i.e. first 
patch, we can try to support autonomous transaction from
standalone SQL-command only, which will set-up infrastructure for future work 
in this area.

Using the WIP patch sent, I have done basic testing and it works fine.

Any comments?

Thanks and Regards,
Kumar Rajeev Rastogi





-- 
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 Transaction (WIP)

[Ian Barwick]

On 07/04/14 15:50, Craig Ringer wrote:

On 04/07/2014 12:06 PM, Rajeev rastogi wrote:



Syntax to create autonomous transaction can be as:

 */PRAGMA AUTONOMOUS TRANSACTION;/*


Wouldn't you want to use SET TRANSACTION for this?

Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?

What's the logic behind introducing "PRAGMA" ?


If you wanted to use that syntax for Oracle compatibility you'd need to use:

 PRAGMA AUTONOMOUS_TRANSACTION;

(note underscore).


FWIW the implementation in the patch uses "PRAGMA 
AUTONOMOUS_TRANSACTION", the space is presumably a typo.



Regards

Ian Barwick


--
 Ian Barwick   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 Transaction (WIP)

[Craig Ringer]

On 04/07/2014 12:06 PM, Rajeev rastogi wrote:


> Syntax to create autonomous transaction can be as:
> 
> */PRAGMA AUTONOMOUS TRANSACTION;/*

Wouldn't you want to use SET TRANSACTION for this?

Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?

What's the logic behind introducing "PRAGMA" ?


If you wanted to use that syntax for Oracle compatibility you'd need to use:

PRAGMA AUTONOMOUS_TRANSACTION;

(note underscore). But really, this would no be a pragma at all,
PostgreSQL doesn't really have the concept. Calling it that would just
be misleading.




> *_Starting of Autonomous  Transaction:_*
> 
> Starting of autonomous transaction will be exactly same as starting
> sub-transaction.

If you don't want it to dirty read data from the parent tx, or inherit
parent locks, then it cannot be the same at all.

> 2.  Freeing of all resource and popping of previous transaction
> happens in the same way as sub-transaction.

I'm not sure what you mean here.


Overall, this looks like a HUGE job to make work well. I know some
others have been doing work along the same lines, so hopefully you'll be
able to collaborate and share ideas.

-- 
 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 Transaction (WIP)

[Pavel Stehule]

Hello

+1 for feature
-1 for Oracle syntax - it is hardly inconsistent with Postgres

Autonomous transactions should be used everywhere - not only in plpgsql

Regards

Pavel


2014-04-07 6:06 GMT+02:00 Rajeev rastogi :

>  I would like to propose “Autonomous Transaction” feature for 9.5.
> Details for the same are mentioned below:
>
>
>
> *What is Autonomous Transaction?*
>
> An autonomous transaction has its own COMMIT and ROLLBACK scope to ensure
> that its outcome does not affect the caller’s uncommitted changes.
> Additionally, the COMMITs and ROLLBACK in the calling transaction should
> not affect the changes that were finalized on the completion of autonomous
> transaction itself. Below are properties of autonomous transaction:
>
> 1.  The autonomous transaction does not see uncommitted changes made
> by the main transaction and does not share locks or resources with main
> transaction.
>
> 2.  Changes in autonomous transactions are visible to other
> transactions upon commit of the autonomous transactions. Thus, users can
> access the updated information without having to wait for the main
> transaction to commit.
>
> 3.  Autonomous transactions can start other autonomous transaction.
> There are no limit, other than resource limits, on how many levels of
> autonomous transaction can be started.
>
>
>
> *Use-case:*
>
> There are many use-case for this feature. One of the use-case is
> illustrated below
>
> Say a procedure is defined, which does some operation on the
> database and incase of any failure in operation on main table, it maintains
> the failure information in a separate relation. But because of current
> transaction behavior, once main table operation fails, it will rollback
> whole transaction and hence error logged in error relation will be also
> lost, which might have been required for future analysis.
>
>In order to solve this issue, we can use autonomous transaction as
> shown below:
>
> *CREATE OR REPLACE function operation(err_msg IN VARCHAR) returns void AS
> $$*
>
> *BEGIN*
>
> * INSERT INTO at_test(id, description) VALUES (998,
> ‘Description for 998’);*
>
> * INSERT INTO at_test(id, description) VALUES (999, NULL);*
>
> *EXCEPTION*
>
> * WHEN OTHER THEN*
>
> * PRAGMA AUTONOMOUS TRANSACTION;*
>
> * INSERT INTO error_logs(id, timestamp,
> err_msg) VALUES(nextval(‘errno’), timenow(), err_msg);*
>
> * COMMIT;*
>
> * RAISE not_null_violation;*
>
> *END;*
>
> *$$ LANGUAGE plpgsql;*
>
> So once we execute above procedure, second INSERT will fails and then
> within exception handling it will start autonomous transaction and log the
> error information in a separate table and then gets committed. So though
> operation to table at_test will fail and rollback, error information will
> persist in the error_logs table. After execution of procedure, record in
> two tables will be as below:
>
> *Postgres=# select * from error_logs;*
>
> *id |  log_time   | err_msg*
>
> *+-+-*
>
> *  5 | 2014-01-17 19:57:11 | error*
>
> *postgres=# select * from at_test;*
>
> *id | decsription*
>
> *+-*
>
> *(0 rows)*
>
>
>
> *Syntax:*
>
> Syntax to create autonomous transaction can be as:
>
> *PRAGMA AUTONOMOUS TRANSACTION;*
>
> This can be used with independent SQL commands, from procedure, triggers.
>
>
>
> *Implementation:*
>
> Implementation of autonomous transaction is based on the existing
> sub-transaction and main transaction. Most of the implementations are
> re-used for autonomous transaction also. Below are the brief details about
> the same:
>
>
>
> *Autonomous Transaction Storage:*
>
> As for main transaction, structure PGXACT is used to store main
> transactions, which are created in shared memory of size:
>
> (Number of process)*sizeof(struct PGXACT)
>
> Similarly a new structure will be defined to store autonomous transaction:
>
> *Struct PGAutonomousXACT*
>
> *{*
>
> *   TransactionId  xid;*
>
> *   TransactionId  xmin;*
>
> *   /* Store the level below main transaction as stored for
> sub-transaction*/*
>
> *   intnestingLevel;*
>
> *   struct XidCache   subxids;*
>
> *   bool overflowed;*
>
> *   bool delaychkpt;*
>
> *   uint  nxids;*
>
> *} PGAutonomousXACT;*
>
> All structure members of PGAutonomousXACT are same as used in PGXACT
> except nestingLevel as marked in bold color to store the level of
> transaction.
>
> Similar to main transaction, the memory allocated to store autonomous
> transaction will be:
>
> *(Number of process) * sizeof (struct PGAutonomousXACT)*MAX_AUTO_TX_LEVEL*
>
> Where MAX_AUTO_TX_LEVEL is maximum number of nested autonomous transaction
> level.
>
> Unlike main transaction, autonomous transaction cannot be a

Re: [HACKERS] Autonomous transaction

[Robert Haas]

On Tue, Apr 13, 2010 at 8:24 PM, Bruce Momjian  wrote:
>> I think it would be really useful, though, for users and maybe even
>> for system internals.  Working out the semantics is a challenge, but
>> not an insurmountable one, I think.
>
> Yea, it is going to feel like a ship with a leaky hull, so we are going
> to have to re-think a bunch of stuff, like how do we handle visibility,
> cleanout of old rows from UPDATE, etc.

Yeah, agreed.

...Robert

-- 
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 transaction

[Bruce Momjian]

Robert Haas wrote:
> On Tue, Apr 13, 2010 at 8:01 PM, Bruce Momjian  wrote:
> > Yea, having some things in our system be non-transactional is odd and
> > hard to understand. ?Just thinking about it, it seems it would introduce
> > all sorts of odd behaviors.
> 
> I think it would be really useful, though, for users and maybe even
> for system internals.  Working out the semantics is a challenge, but
> not an insurmountable one, I think.

Yea, it is going to feel like a ship with a leaky hull, so we are going
to have to re-think a bunch of stuff, like how do we handle visibility,
cleanout of old rows from UPDATE, etc.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.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 transaction

[Robert Haas]

On Tue, Apr 13, 2010 at 8:01 PM, Bruce Momjian  wrote:
> Yea, having some things in our system be non-transactional is odd and
> hard to understand.  Just thinking about it, it seems it would introduce
> all sorts of odd behaviors.

I think it would be really useful, though, for users and maybe even
for system internals.  Working out the semantics is a challenge, but
not an insurmountable one, I think.

...Robert

-- 
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 transaction

[Bruce Momjian]

p...@thetdh.com wrote:
>  It would be useful to have a relation such that all dirtied
>  buffers got written out even for failed transactions (barring
>  a crash) and such that read-any-undeleted were easy to do,
>  despite the non-ACIDity. The overhead of a side transaction
>  seems overkill for such things as logs or advisory relations,
>  and non-DB files would be harder to tie in efficiently to DB
>  activity. A side transaction would still have to be committed
>  in order to be useful; either you're committing frequently
>  (ouch!), or you risk failing to commit just as you would the
>  main transaction.

Yea, having some things in our system be non-transactional is odd and
hard to understand.  Just thinking about it, it seems it would introduce
all sorts of odd behaviors.

--
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.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 transaction

[pg]

 It would be useful to have a relation such that all dirtied buffers got 
written out even for failed transactions (barring a crash) and such that 
read-any-undeleted were easy to do, despite the non-ACIDity. The overhead of a 
side transaction seems overkill for such things as logs or advisory relations, 
and non-DB files would be harder to tie in efficiently to DB activity. A side 
transaction would still have to be committed in order to be useful; either 
you're committing frequently (ouch!), or you risk failing to commit just as you 
would the main transaction.

David Hudson

-Original Message-
From: Loïc Vaumerel [mailto:she...@gmail.com]
Sent: Sunday, April 4, 2010 10:26 AM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] Autonomous transaction

Hi,



I have an application project based on a database.
I am really interested in using PostgreSQL.


I have only one issue, I want to use autonomous transactions to put in place a 
debug / logging functionality.
To do so, I insert messages in a "debug" table.
The problem is, if the main transaction / process rollback, my debug message 
insert will be rolled back too.
This is not the behavior I wish.


I need a functionality with the same behavior than the Oracle "PRAGMA 
AUTONOMOUS_TRANSACTION" one.
I have searched for it in the documentation and on the net, unfortunately 
nothing. (maybe I missed something)


I just found some posts regarding this :
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php
https://labs.omniti.com/trac/pgtreats/browser/trunk/autonomous_logging_tool
... and some others ...


All solutions I found are working the same way : they use dblink.
I consider these solution more as handiwork than a clean solution.
I am a little bit concerned about side effects as dblink were not intially 
designed for this.


So my questions :
Is there a way to use real and clean autonomous transactions in PostgreSQL yet ?
If no, is it planned to do so ? When ?


Thanks in advance


Best regards


Shefla

Re: [HACKERS] Autonomous transaction

[Dimitri Fontaine]

Loïc Vaumerel  writes:
> All solutions I found are working the same way : they use dblink.
> I consider these solution more as handiwork than a clean solution.
> I am a little bit concerned about side effects as dblink were not
> intially designed for this.

See plproxy which is designed for this kind of work. Or about…

> Is there a way to use real and clean autonomous transactions in
> PostgreSQL yet ?

None that I know of.

> If no, is it planned to do so ? When ?

We get demands quite often, it seems it's one of the "big tickets" we're
still missing. I don't remember any development effort proposal, though.

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 transaction

[Jaime Casanova]

On Sun, Apr 4, 2010 at 10:26 AM, Loïc Vaumerel  wrote:
[...]
> All solutions I found are working the same way : they use dblink.
> I consider these solution more as handiwork than a clean solution.
> I am a little bit concerned about side effects as dblink were not intially
> designed for this.
>

the only side effect i can think of is that you will use another
connection slot (that's because dblink will stablish a new connection)

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
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 transaction

[Robert Haas]

On Sun, Apr 4, 2010 at 10:26 AM, Loïc Vaumerel  wrote:
> Is there a way to use real and clean autonomous transactions in PostgreSQL
> yet ?

No.

> If no, is it planned to do so ? When ?

To my knowledge, no one is working on this.

...Robert

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