Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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,

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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.

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Fri, Aug 21, 2015 at 10:06:44AM -0400, Robert Haas wrote: On Tue, Aug 18, 2015 at 3:06 PM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch n...@leadboat.com wrote: [1] That's

Re: [HACKERS] Autonomous Transaction is back

On Tue, Aug 18, 2015 at 3:06 PM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch n...@leadboat.com wrote: CREATE TABLE t (c) AS SELECT 1; BEGIN; UPDATE t SET c = 2 WHERE c = 1;

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch n...@leadboat.com 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

Re: [HACKERS] Autonomous Transaction is back

On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch n...@leadboat.com 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

Re: [HACKERS] Autonomous Transaction is back

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 =

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Fri, Aug 07, 2015 at 11:26:08AM -0400, Robert Haas wrote: On Thu, Aug 6, 2015 at 11:04 PM, Merlin Moncure mmonc...@gmail.com 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,

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Thu, Aug 6, 2015 at 11:04 PM, Merlin Moncure mmonc...@gmail.com 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

Re: [HACKERS] Autonomous Transaction is back

On Mon, Aug 3, 2015 at 9:09 AM, Merlin Moncure mmonc...@gmail.com 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 ...

Re: [HACKERS] Autonomous Transaction is back

On Thu, Aug 6, 2015 at 4:15 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Aug 3, 2015 at 9:09 AM, Merlin Moncure mmonc...@gmail.com 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

Re: [HACKERS] Autonomous Transaction is back

On 03 August 2015 18:40, Merlin Moncure [mailto:mmonc...@gmail.com] Wrote: On Sun, Aug 2, 2015 at 11:37 PM, Rajeev rastogi rajeev.rast...@huawei.com 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

Re: [HACKERS] Autonomous Transaction is back

On Tue, Aug 4, 2015 at 4:12 AM, Rajeev rastogi rajeev.rast...@huawei.com 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 rajeev.rast...@huawei.com wrote: On 31 July 2015 23:10, Robert Haas Wrote: I think we're

Re: [HACKERS] Autonomous Transaction is back

On Sun, Aug 2, 2015 at 11:37 PM, Rajeev rastogi rajeev.rast...@huawei.com 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?

Re: [HACKERS] Autonomous Transaction is back

On 31 July 2015 23:10, Robert Haas Wrote: On Tue, Jul 28, 2015 at 6:01 AM, Craig Ringer cr...@2ndquadrant.com 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

Re: [HACKERS] Autonomous Transaction is back

On Tue, Jul 28, 2015 at 6:01 AM, Craig Ringer cr...@2ndquadrant.com 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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On 23 July 2015 at 13:31, Rajeev rastogi rajeev.rast...@huawei.com 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

Re: [HACKERS] Autonomous Transaction is back

On Tue, Jul 28, 2015 at 12:56 AM, Josh Berkus j...@agliodbs.com 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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus j...@agliodbs.com wrote: Batch Jobs: large data-manipulation tasks which need to be broken up into segments, with each segment committing separately. Example: updating 1

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Mon, Jul 27, 2015 at 4:41 PM, Joel Jacobson j...@trustly.com wrote: On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus j...@agliodbs.com wrote: Batch Jobs: large data-manipulation tasks which need to be broken up into

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus j...@agliodbs.com 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

Re: [HACKERS] Autonomous Transaction is back

On 07/27/2015 03:12 PM, Joel Jacobson wrote: On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus j...@agliodbs.com mailto:j...@agliodbs.com wrote: Ah, you're missing how commits in ATX are expected to work. Let me illustrate: X ( Data write A1 call Y(

Re: [HACKERS] Autonomous Transaction is back

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,

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

2015-07-27 23:59 GMT+02:00 Merlin Moncure mmonc...@gmail.com: On Mon, Jul 27, 2015 at 4:41 PM, Joel Jacobson j...@trustly.com wrote: On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus j...@agliodbs.com wrote: Batch

Re: [HACKERS] Autonomous Transaction is back

On 23 July 2015 21:04, Robert Haas Wrote: On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi rajeev.rast...@huawei.com 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

Re: [HACKERS] Autonomous Transaction is back

On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus j...@agliodbs.com 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

Re: [HACKERS] Autonomous Transaction is back

On Thu, Jul 23, 2015 at 11:01 AM, Rajeev rastogi rajeev.rast...@huawei.com 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

Re: [HACKERS] Autonomous Transaction is back

On Thu, Jul 23, 2015 at 2:49 PM, Josh Berkus j...@agliodbs.com wrote: On 07/23/2015 11:39 AM, Robert Haas wrote: On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus j...@agliodbs.com wrote: Requesting for everyone's opinion regarding this based on which we can proceed to enhance/tune/re-write our

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi rajeev.rast...@huawei.com 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

Re: [HACKERS] Autonomous Transaction is back

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

Re: [HACKERS] Autonomous Transaction is back

On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus j...@agliodbs.com 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

Re: [HACKERS] Autonomous Transaction is back

On 07/23/2015 11:39 AM, Robert Haas wrote: On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus j...@agliodbs.com 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

[HACKERS] Autonomous Transaction is back

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