Re: [HACKERS] autonomous transactions
On Sat, Jan 7, 2017 at 5:30 PM, Andrey Borodin wrote: > The new status of this patch is: Ready for Committer I don't think that this thread has reached a conclusion yet. From what I can see the last patch does not apply, so I have moved the patch to next CF with "waiting on author". -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: tested, passed Documentation:tested, passed Here’s review of Background Sessions v2 patch. ===Purpose=== Patch provides API for controlling other backend. Also, patch contains Python API support for calling C API. ===Overall status=== Patch applies to current HEAD clearly. Contains new test and passes existing tests. Contains sufficient documentation. Contains 2 TODO items. Not sure it's OK to it leave so. Another patch from this commitfest (pg_background) is based on this patch. ===Suggestions=== I haven’t found a way to safely acquire status of session (without possibility of ereport(ERROR)). I do not see how to pass massive data, except by one long char* SQL. All the values have to be formatted as text. BackgroundSessionStart() result do not contain PID. This functionality is expected by pg_background (though, can be added separately by pg_background). I suppose, this is done to prevent API users from accessing internals of BackgroundSession structure. But some information have to be public, anyway. bgsession.c code contains very little comments. I do not think that switch inside a switch (see bgsession_worker_main()) is easy to follow. ===Conclusion=== There’s always something to improve, but I think that this patch is ready for committer. PS. I’ve read the db_link patches, but this review does not apply to them. I suppose db_link refactoring would be useful and functionality is added, so I think these patches deserve separate commitfest entry. Best regards, Andrey Borodin. The new status of this patch is: Ready for Committer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 11/10/16 21:54, Merlin Moncure wrote: > On Tue, Oct 11, 2016 at 10:06 AM, Petr Jelinek wrote: >> On 10/10/16 16:44, Merlin Moncure wrote: >>> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs wrote: On 6 October 2016 at 21:27, Robert Haas wrote: > I think we should implement background transactions and call them > background transactions. That allows us to expose additional > functionality which is useful, like the ability to kick something off > and check back later for the results. There's no reason to call it > background transactions and also call it autonomous transactions: one > feature doesn't need two names. I'm happy to also invoke it via an alternate mechanism or API, so that it can continue to be used even if the above mechanism changes. We have no need to wait for the perfect solution, even assuming we would ever agree that just one exists. >>> >>> -1 on implementing both autonomous and background transactions. This >>> will confuse everyone. >> >> I personally care much more about having background transactions than >> autonomous ones (as I only ever had use-cases for the background ones) >> so don't agree there. > > All right. But would you agree then that AT should at least emulate > competing implementations? A major advantage of bgworkers is possibly > supporting concurrent activity and maybe the syntax could be more > directed to possibly moving in that direction other than copying > oracle style (PRAGMA etc), particularly if the locking rules are > substantially different. > Yes, I am just saying we should have both. I don't feel like I can judge if background transactions solve autonomous transactions use-cases so I am not expressing opinion there. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
2016-10-11 21:54 GMT+02:00 Merlin Moncure : > On Tue, Oct 11, 2016 at 10:06 AM, Petr Jelinek > wrote: > > On 10/10/16 16:44, Merlin Moncure wrote: > >> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs > wrote: > >>> On 6 October 2016 at 21:27, Robert Haas wrote: > I think we should implement background transactions and call them > background transactions. That allows us to expose additional > functionality which is useful, like the ability to kick something off > and check back later for the results. There's no reason to call it > background transactions and also call it autonomous transactions: one > feature doesn't need two names. > >>> > >>> I'm happy to also invoke it via an alternate mechanism or API, so that > >>> it can continue to be used even if the above mechanism changes. > >>> > >>> We have no need to wait for the perfect solution, even assuming we > >>> would ever agree that just one exists. > >> > >> -1 on implementing both autonomous and background transactions. This > >> will confuse everyone. > > > > I personally care much more about having background transactions than > > autonomous ones (as I only ever had use-cases for the background ones) > > so don't agree there. > > All right. But would you agree then that AT should at least emulate > competing implementations? A major advantage of bgworkers is possibly > supporting concurrent activity and maybe the syntax could be more > directed to possibly moving in that direction other than copying > oracle style (PRAGMA etc), particularly if the locking rules are > substantially different. > There is a big trap - AT is usually used for writing to log tables. When BT fails on maximum active workers then, then you cannot do any expected operation. Regards Pavel > > merlin > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] autonomous transactions
On Tue, Oct 11, 2016 at 10:06 AM, Petr Jelinek wrote: > On 10/10/16 16:44, Merlin Moncure wrote: >> On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs wrote: >>> On 6 October 2016 at 21:27, Robert Haas wrote: I think we should implement background transactions and call them background transactions. That allows us to expose additional functionality which is useful, like the ability to kick something off and check back later for the results. There's no reason to call it background transactions and also call it autonomous transactions: one feature doesn't need two names. >>> >>> I'm happy to also invoke it via an alternate mechanism or API, so that >>> it can continue to be used even if the above mechanism changes. >>> >>> We have no need to wait for the perfect solution, even assuming we >>> would ever agree that just one exists. >> >> -1 on implementing both autonomous and background transactions. This >> will confuse everyone. > > I personally care much more about having background transactions than > autonomous ones (as I only ever had use-cases for the background ones) > so don't agree there. All right. But would you agree then that AT should at least emulate competing implementations? A major advantage of bgworkers is possibly supporting concurrent activity and maybe the syntax could be more directed to possibly moving in that direction other than copying oracle style (PRAGMA etc), particularly if the locking rules are substantially different. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
2016-10-11 17:06 GMT+02:00 Petr Jelinek : > On 10/10/16 16:44, Merlin Moncure wrote: > > On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs > wrote: > >> On 6 October 2016 at 21:27, Robert Haas wrote: > >>> I think we should implement background transactions and call them > >>> background transactions. That allows us to expose additional > >>> functionality which is useful, like the ability to kick something off > >>> and check back later for the results. There's no reason to call it > >>> background transactions and also call it autonomous transactions: one > >>> feature doesn't need two names. > >> > >> I'm happy to also invoke it via an alternate mechanism or API, so that > >> it can continue to be used even if the above mechanism changes. > >> > >> We have no need to wait for the perfect solution, even assuming we > >> would ever agree that just one exists. > > > > -1 on implementing both autonomous and background transactions. This > > will confuse everyone. > > > > I personally care much more about having background transactions than > autonomous ones (as I only ever had use-cases for the background ones) > so don't agree there. > we can, we should to have both - background can be used for paralelism, autonomous for logging. they are not 100% replaceable. Regards Pavel > > -- > Petr Jelinek http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] autonomous transactions
On 10/10/16 16:44, Merlin Moncure wrote: > On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs wrote: >> On 6 October 2016 at 21:27, Robert Haas wrote: >>> I think we should implement background transactions and call them >>> background transactions. That allows us to expose additional >>> functionality which is useful, like the ability to kick something off >>> and check back later for the results. There's no reason to call it >>> background transactions and also call it autonomous transactions: one >>> feature doesn't need two names. >> >> I'm happy to also invoke it via an alternate mechanism or API, so that >> it can continue to be used even if the above mechanism changes. >> >> We have no need to wait for the perfect solution, even assuming we >> would ever agree that just one exists. > > -1 on implementing both autonomous and background transactions. This > will confuse everyone. > I personally care much more about having background transactions than autonomous ones (as I only ever had use-cases for the background ones) so don't agree there. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Thu, Oct 6, 2016 at 3:53 PM, Simon Riggs wrote: > On 6 October 2016 at 21:27, Robert Haas wrote: >> I think we should implement background transactions and call them >> background transactions. That allows us to expose additional >> functionality which is useful, like the ability to kick something off >> and check back later for the results. There's no reason to call it >> background transactions and also call it autonomous transactions: one >> feature doesn't need two names. > > I'm happy to also invoke it via an alternate mechanism or API, so that > it can continue to be used even if the above mechanism changes. > > We have no need to wait for the perfect solution, even assuming we > would ever agree that just one exists. -1 on implementing both autonomous and background transactions. This will confuse everyone. The lingo here is no so important, I think. What *is* important is defining how the locking and execution rules should work and the implementation should flow from that. Those rules should be estimated from competing implementations and how well they work. +1 for any solution that makes migration from other solutions to postgres easier. bgworkers should be considered if you want things to run in parallel. Reading the proposal (note, I may have missed it) it isn't clear to me if you can have the parent and AT run a query at the same time. Should this (parallel execution) be a design goal, then that's the end of the story. However I don't think it is TBH. ISTM the expectation is single threaded behavior with finer grained control of commits. If we're not 100% clear on this point one way or the other then things are a bit preemptive. Maybe we are clear and I missed something? One major advantage non-bgworker serilized execution approach is that certain classes of deadlock are easier to detect or do not exist since there is only one execution state; AIUI it's impossible for two transaction states to be simultaneously waiting assuming the pl/pgsql instuctions are not run in parallel with one exception, and that is the AT trying to acquire a lock exclusively held by the master. If the AT blocks on the parent it ought to be O(1) and instant to detect that and roll it back with right supporting infrastructure in the lock manager. It also makes sharing execution state much easier, especially the parts that look like, "I'm waiting here until the other guy finishes" since there's only one "guy". How will advisory locks work? I think they'd block with bgworkers and not block with non-bgworkers. What about other session based stuff like prepared statements? Expectations around those cases out to clarify the implementation. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 2016-10-06 10:56:34 +0100, Simon Riggs wrote: > On 7 September 2016 at 20:46, Robert Haas wrote: > > On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs wrote: > > True. I believe this issue has been discussed before, and I think > > it's a solvable issue. I believe that an autonomous transaction could > > be made to appear to the rest of the system (outside the affected > > backend) as if it were a subtransaction, but then get committed before > > the containing transaction gets committed. This would avoid needing > > more PGPROCs (but getting deadlock detection to work is hard). > > Just to point out that I've actually written this approach already. > The patch is available, Autonomous Subtransactions. > We discussed it in Ottawa and it was rejected. (I thought Robert was > there, but Serge and Tom definitely were). Hm, maybe I'm missing something, but don't pretty all of the objections to that approach also apply to this one, baring some additional changes required for lock conflict detection? > We have various approaches... summarised in chronological order of > their suggestion > > 1. Use additional PGXACTs - rejected because it wouldn't provide enough room Doesn't this approach precisely requires additional PGXACTs in the form of additional background workers? Plus a full process overhead? > 2. Use Autonomous SubTransactions - rejected because the semantics are > different to what we might expect from ATs Which semantics are fundamentally different? Snapshot visibility seems fairly trivial to adapt, and so seems locking. In even seems slightly easier to give a good error message about deadlocking against the main transaction in this approach. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Thu, Oct 6, 2016 at 4:53 PM, Simon Riggs wrote: > For myself, I don't care what you call it. > > I just want to be able to invoke it by saying PRAGMA AUTONOMOUS_TRANSACTION; > and I know many others do also. To me, those statements are rather contradictory, and I don't support the latter proposal. However, I don't have the only vote here, either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 6 October 2016 at 21:27, Robert Haas wrote: >> * The labelling "Autonomous Transaction" is a simple coat of paint, >> which can easily be transferred to a better implementation if one >> comes. If one doesn't then its better to have something than nothing. >> So I suggest we commit Background Transactions first and then in a >> fairly thin commit, implement Autonomous Transactions on top of it for >> now and if we get a better one, switch it over. > > I think we should implement background transactions and call them > background transactions. That allows us to expose additional > functionality which is useful, like the ability to kick something off > and check back later for the results. There's no reason to call it > background transactions and also call it autonomous transactions: one > feature doesn't need two names. For myself, I don't care what you call it. I just want to be able to invoke it by saying PRAGMA AUTONOMOUS_TRANSACTION; and I know many others do also. If a better implementation emerges I would happily replace this one with another. I'm happy to also invoke it via an alternate mechanism or API, so that it can continue to be used even if the above mechanism changes. We have no need to wait for the perfect solution, even assuming we would ever agree that just one exists. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Thu, Oct 6, 2016 at 5:56 AM, Simon Riggs wrote: > Just to point out that I've actually written this approach already. > The patch is available, Autonomous Subtransactions. > We discussed it in Ottawa and it was rejected. (I thought Robert was > there, but Serge and Tom definitely were). Where is the patch? > See other posts in this thread by Serge and Craig to explain why. I don't think the posts on Craig and Serge explain why that approach was rejected or would be a bad idea. > We have various approaches... summarised in chronological order of > their suggestion > > 1. Use additional PGXACTs - rejected because it wouldn't provide enough room Of course, a background worker uses a PGXACT too and a lot more, so if you think extra PGXACTs are bad, you should *really* think background workers are bad. > 2. Use Autonomous SubTransactions - rejected because the semantics are > different to what we might expect from ATs In what way? I think the questions of how you implement it and what the semantics are are largely orthogonal questions. To which proposal is this referring? > 3. Use background transactions (this thread) Sure. > 4. Use pause and resume so we don't use up too many PGXACTs I don't know what "pause and resume" means. > * The labelling "Autonomous Transaction" is a simple coat of paint, > which can easily be transferred to a better implementation if one > comes. If one doesn't then its better to have something than nothing. > So I suggest we commit Background Transactions first and then in a > fairly thin commit, implement Autonomous Transactions on top of it for > now and if we get a better one, switch it over. I think we should implement background transactions and call them background transactions. That allows us to expose additional functionality which is useful, like the ability to kick something off and check back later for the results. There's no reason to call it background transactions and also call it autonomous transactions: one feature doesn't need two names. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 06/10/16 11:56, Simon Riggs wrote: > > * The labelling "Autonomous Transaction" is a simple coat of paint, > which can easily be transferred to a better implementation if one > comes. If one doesn't then its better to have something than nothing. > So I suggest we commit Background Transactions first and then in a > fairly thin commit, implement Autonomous Transactions on top of it for > now and if we get a better one, switch it over. > +1 to this -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 7 September 2016 at 20:46, Robert Haas wrote: > On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs wrote: >> On 2 September 2016 at 09:45, Robert Haas wrote: >>> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut >>> wrote: I would like to propose the attached patch implementing autonomous transactions for discussion and review. >>> >>> I'm pretty skeptical of this approach. Like Greg Stark, Serge Rielau, >>> and Constantin Pan, I had expected that autonomous transactions should >>> be implemented inside of a single backend, without relying on workers. >> >> The problem is that we have limits on the number of concurrent >> transactions, which is currently limited by the number of procs. > > True. I believe this issue has been discussed before, and I think > it's a solvable issue. I believe that an autonomous transaction could > be made to appear to the rest of the system (outside the affected > backend) as if it were a subtransaction, but then get committed before > the containing transaction gets committed. This would avoid needing > more PGPROCs (but getting deadlock detection to work is hard). Just to point out that I've actually written this approach already. The patch is available, Autonomous Subtransactions. We discussed it in Ottawa and it was rejected. (I thought Robert was there, but Serge and Tom definitely were). See other posts in this thread by Serge and Craig to explain why. I take your point that startup time may be not as good as it can be. But what Peter has works and is useful, whatever we call it. We have various approaches... summarised in chronological order of their suggestion 1. Use additional PGXACTs - rejected because it wouldn't provide enough room 2. Use Autonomous SubTransactions - rejected because the semantics are different to what we might expect from ATs 3. Use background transactions (this thread) 4. Use pause and resume so we don't use up too many PGXACTs What I see is that there are some valid and useful features here and we should be developing them further because they have other benefits. * Autonomous Subtransactions might not work for Autonomous Transactions, but they are useful for incremental loading of large data, which then allows easier logical decoding. So that sounds like we should do that anyway, even if they are not ATs. They can also be used within parallel tasks. * Background Transactions are useful and we should have them. * The labelling "Autonomous Transaction" is a simple coat of paint, which can easily be transferred to a better implementation if one comes. If one doesn't then its better to have something than nothing. So I suggest we commit Background Transactions first and then in a fairly thin commit, implement Autonomous Transactions on top of it for now and if we get a better one, switch it over. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 9/3/16 9:08 AM, Serge Rielau wrote: > Interestingly, despite being supported in PL/SQL on nested BEGIN END blocks, > we nearly exclusively see AT’s covering the entire function or trigger. Oracle only supports an entire PL/SQL function to be declared autonomous. But it was pretty easy in my implementation to allow that for arbitrary blocks. In fact, it would have been harder not to do that. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 8/31/16 9:11 AM, Craig Ringer wrote: > Peter, you mention "Oracle-style autonomous transaction blocks". > > What are the semantics to be expected of those with regards to: > > - Accessing objects exclusively locked by the outer xact or where the > requested lockmode conflicts with a lock held by the outer xact > > - Visibility of data written by the outer xact The semantics are that the autonomous session is completely isolated from the parent session. It has no special knowledge about transactions happening on the parent. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 8/31/16 8:46 AM, Greg Stark wrote: > I'm surprised by the background worker. I had envisioned autonomous > transactions being implemented by allowing a process to reserve a > second entry in PGPROC with the same pid. Or perhaps save its existing > information in a separate pgproc slot (or stack of slots) and > restoring it after the autonomous transaction commits. There is quite likely room for a feature like that too, but it's not this one. > Using a background worker mean that the autonomous transaction can't > access any state from the process memory. That is intentional. Autonomous transactions is actually a misnomer. It's autonomous sessions. But Oracle names it wrong. Autonomous sessions (or whatever you want to call them) have their own session state, configuration parameters, potentially different plugins loaded, different authorization state, and so on. > What happens if a > statement timeout occurs during an autonomous transaction? Right now not much. ;-) But I think the behavior should be that the autonomous session is aborted. > What > happens if you use a pl language in the autonomous transaction and if > it tries to use non-transactional information such as prepared > statements? The same thing that happens if you open a new unrelated database connection and try to do that. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 8/31/16 12:38 AM, Jaime Casanova wrote: > On 30 August 2016 at 20:50, Peter Eisentraut > wrote: >> >> - Patches to PL/pgSQL to implement Oracle-style autonomous transaction >> blocks: >> >> AS $$ >> DECLARE >> PRAGMA AUTONOMOUS_TRANSACTION; >> BEGIN >> FOR i IN 0..9 LOOP >> START TRANSACTION; >> INSERT INTO test1 VALUES (i); >> IF i % 2 = 0 THEN >> COMMIT; >> ELSE >> ROLLBACK; >> END IF; >> END LOOP; >> >> RETURN 42; >> END; >> $$; >> > > this is the syntax it will use? That is the syntax that Oracle uses. We could make up our own. > i just compiled this in head and created a function based on this one. > The main difference is that the column in test1 it's a pk so i used > INSERT ON CONFLICT DO NOTHING > > and i'm getting this error > > postgres=# select foo(); > LOG: namespace item variable itemno 1, name val > CONTEXT: PL/pgSQL function foo() line 7 at SQL statement > STATEMENT: select foo(); > ERROR: null value in column "i" violates not-null constraint > DETAIL: Failing row contains (null). > STATEMENT: INSERT INTO test1 VALUES (val) ON CONFLICT DO NOTHING > ERROR: null value in column "i" violates not-null constraint > DETAIL: Failing row contains (null). > CONTEXT: PL/pgSQL function foo() line 7 at SQL statement > STATEMENT: select foo(); > ERROR: null value in column "i" violates not-null constraint > DETAIL: Failing row contains (null). > CONTEXT: PL/pgSQL function foo() line 7 at SQL statement > > this happens even everytime i use the PRAGMA even if no START > TRANSACTION, COMMIT or ROLLBACK are used The PL/pgSQL part doesn't work well yet. If you want to play around, use the PL/Python integration. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Tue, Sep 13, 2016 at 5:06 PM, Petr Jelinek wrote: > I mostly agree. I think if this was called something like background > transactions it might be better. It's definitely useful functionality but > the naming is clearly contentious. It won't stop people using it for same > use-cases as autonomous transactions though (which is fine). Quite right. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 13/09/16 22:24, Robert Haas wrote: On Wed, Sep 7, 2016 at 9:14 PM, Craig Ringer wrote: That's probably going to be one of the smaller costs. Doing this with bgworkers won't be cheap, but you need to consider the alternative. Factoring out all transaction-specific data currently stored in or pointed to by globals into a transaction state struct that can be swapped out. Making PgXact and PGPROC capable of representing multiple/suspended transactions. Lots more. Much of which would have a performance impact on all day to day operations whether or not autononomous xacts are actually in use. I haven't looked into it in detail. Peter can probably explain more and better. I'm just pointing out that I doubt there's any way to do this without a cost somewhere, and having that cost limited to actual uses of autonomous xacts would be nice. I don't really believe this line of argument. I mean, sure, it's nice to limit the cost of features to the people who are using those features. Totally agreed. But if the cost really wouldn't be that high anyway, which I suspect is the case here, then that argument loses its force. And if that separation increases the performance cost of the feature by two or three orders of magnitude in realistic use cases, then you really have to wonder if you've picked the right approach. Again, I'm not saying that having ways to run commands in the background in a worker isn't useful. If I thought that wasn't useful, I would not have written pg_background and developed it as far as I did. But I still don't think that's the right way to implement an autonomous transaction facility. I mostly agree. I think if this was called something like background transactions it might be better. It's definitely useful functionality but the naming is clearly contentious. It won't stop people using it for same use-cases as autonomous transactions though (which is fine). -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Wed, Sep 7, 2016 at 9:14 PM, Craig Ringer wrote: > That's probably going to be one of the smaller costs. Doing this with > bgworkers won't be cheap, but you need to consider the alternative. > Factoring out all transaction-specific data currently stored in or > pointed to by globals into a transaction state struct that can be > swapped out. Making PgXact and PGPROC capable of representing > multiple/suspended transactions. Lots more. Much of which would have a > performance impact on all day to day operations whether or not > autononomous xacts are actually in use. > > I haven't looked into it in detail. Peter can probably explain more > and better. I'm just pointing out that I doubt there's any way to do > this without a cost somewhere, and having that cost limited to actual > uses of autonomous xacts would be nice. I don't really believe this line of argument. I mean, sure, it's nice to limit the cost of features to the people who are using those features. Totally agreed. But if the cost really wouldn't be that high anyway, which I suspect is the case here, then that argument loses its force. And if that separation increases the performance cost of the feature by two or three orders of magnitude in realistic use cases, then you really have to wonder if you've picked the right approach. Again, I'm not saying that having ways to run commands in the background in a worker isn't useful. If I thought that wasn't useful, I would not have written pg_background and developed it as far as I did. But I still don't think that's the right way to implement an autonomous transaction facility. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 8 Sep. 2016 1:38 pm, "Andres Freund" wrote: > > I kind of dislike this approach for a different reason than already > mentioned in this thread: Namely it's not re-usable for implementing > streaming logical replication of large transaction, i.e. allow to decode > & apply un-committed changes. The issue there is that one really needs > to have multiple transactions active in the same connection, which this > approach does not allow. I've been thinking about this recently with an eye to handling the majority of transactions on a typical system that neither perform DDL nor are concurrent with it. The following might be fairly nonsensical if I've misunderstood the problem as I've not had a chance to more than glance at it, but: I wonder if some kind of speculative concurrent decoding+output is possible without being able to have multiple xacts on a backend. We could use a shared xact and snapshot for all concurrent xacts. If any of them do anything that requires invalidations etc we dump the speculatively processed ones and fall back to reorder buffering and serial output at commit time until we pass the xact that caused an invalidation and don't have more pending. Add a callback to the output plugin to tell it that speculative decoding of an xact has been aborted. Or maybe even just put that speculstive decoding on hold and pick up where we left off partway through the reorder buffer when we get around to processing it serially. That way we don't have to discard work already done. More usefully we could avoid having to enqueue stuff into the reorder buffer just in case we have to switch to fallback serial decoding. Failing that: Since logical decoding only needs read only xacts that roll back, it only needs multiple backend private virtualxacts. They don't need SERIALIZABLE/SSI which I think (?) means other backends don't need to be able to wait on them. That seems simpler than what autonomous xacts would need since there we need them exposed in PgXact, waitable-on for txid locks, etc. Right? In the same way that historical snapshots are cut-down maybe logical decoding xacts can be too. I suspect that waiting for full in-process multiple xact support to do interleaved decoding/replay will mean a long wait indeed.
Re: [HACKERS] autonomous transactions
Hi, On 2016-08-30 21:50:05 -0400, Peter Eisentraut wrote: > I would like to propose the attached patch implementing autonomous > transactions for discussion and review. > > This work was mostly inspired by the discussion about pg_background a > while back [0]. It seemed that most people liked the idea of having > something like that, but couldn't perhaps agree on the final interface. > Most if not all of the preliminary patches in that thread were > committed, but the user interface portions were then abandoned in favor > of other work. (I'm aware that rebased versions of pg_background > existing. I have one, too.) I kind of dislike this approach for a different reason than already mentioned in this thread: Namely it's not re-usable for implementing streaming logical replication of large transaction, i.e. allow to decode & apply un-committed changes. The issue there is that one really needs to have multiple transactions active in the same connection, which this approach does not allow. That's not necessarily a fatal objection, but I did want to throw that as a design goal out there. - Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 8 September 2016 at 08:18, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer >> Of course, if we could decrease the startup cost of a bgworker > >> For this use in autonomous tx's we could probably pool workers. Or at least >> lazily terminate them so that the loop cases work better by re-using an >> existing bgworker. > > I’m not sure whether to be nervous about the context switch cost in the use > cases of autonomous transactions. That's probably going to be one of the smaller costs. Doing this with bgworkers won't be cheap, but you need to consider the alternative. Factoring out all transaction-specific data currently stored in or pointed to by globals into a transaction state struct that can be swapped out. Making PgXact and PGPROC capable of representing multiple/suspended transactions. Lots more. Much of which would have a performance impact on all day to day operations whether or not autononomous xacts are actually in use. I haven't looked into it in detail. Peter can probably explain more and better. I'm just pointing out that I doubt there's any way to do this without a cost somewhere, and having that cost limited to actual uses of autonomous xacts would be nice. (BTW, could you please set your reply style so that your mail client quotes text and it's possible to see what you wrote vs what is quoted?) -- -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer > Of course, if we could decrease the startup cost of a bgworker For this use in autonomous tx's we could probably pool workers. Or at least lazily terminate them so that the loop cases work better by re-using an existing bgworker. Though I may say something odd, isn’t the bgworker approach going to increase context switches? I thought PostgreSQL has made efforts to decrease context switches, e.g. * Each backend itself writes WAL to disk unlike Oracle requests LGWR process to write REDO to disk. * Releasing and re-acquiring a lwlock appears to try to avoid context switches. /* * Loop here to try to acquire lock after each time we are signaled by * LWLockRelease. * * NOTE: it might seem better to have LWLockRelease actually grant us the * lock, rather than retrying and possibly having to go back to sleep. But * in practice that is no good because it means a process swap for every * lock acquisition when two or more processes are contending for the same * lock. Since LWLocks are normally used to protect not-very-long * sections of computation, a process needs to be able to acquire and * release the same lock many times during a single CPU time slice, even * in the presence of contention. The efficiency of being able to do that * outweighs the inefficiency of sometimes wasting a process dispatch * cycle because the lock is not free when a released waiter finally gets * to run. See pgsql-hackers archives for 29-Dec-01. */ I’m not sure whether to be nervous about the context switch cost in the use cases of autonomous transactions. Regards Takayuki Tsunakawa
Re: [HACKERS] autonomous transactions
On 8 Sep. 2016 3:47 am, "Robert Haas" wrote: > > Of course, if we could decrease the startup cost of a bgworker For this use in autonomous tx's we could probably pool workers. Or at least lazily terminate them so that the loop cases work better by re-using an existing bgworker. I'm pretty sure we're going to need a bgworker pool sooner or later anyway.
Re: [HACKERS] autonomous transactions
On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs wrote: > On 2 September 2016 at 09:45, Robert Haas wrote: >> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut >> wrote: >>> I would like to propose the attached patch implementing autonomous >>> transactions for discussion and review. >> >> I'm pretty skeptical of this approach. Like Greg Stark, Serge Rielau, >> and Constantin Pan, I had expected that autonomous transactions should >> be implemented inside of a single backend, without relying on workers. > > The problem is that we have limits on the number of concurrent > transactions, which is currently limited by the number of procs. True. I believe this issue has been discussed before, and I think it's a solvable issue. I believe that an autonomous transaction could be made to appear to the rest of the system (outside the affected backend) as if it were a subtransaction, but then get committed before the containing transaction gets committed. This would avoid needing more PGPROCs (but getting deadlock detection to work is hard). > So doing autonomous transactions inside a single backend doesn't gain > you very much, yet it is an enormously invasive patch to do it that > way, not least because it requires you to rewrite locking and > deadlocks to make them work correctly when proc is not 1:1 with xid. > And as Serge points out it introduces other restrictions that we know > about now, perhaps more as well. Yes, but I think that if we want to really meet the needs of Oracle users who are used to being able to slap an autonomous transaction pragma on any function that they like, we're going to need a solution that is far lighter-weight than starting up a new backend. Any approach based on background workers is going to make the cost of a function call something like 4ms, which I suspect is going to make it useless for a pretty significant percentage of the cases where users want to use autonomous transactions. For example, if you want to log every attempt to access an object, this is a phenomenally expensive way to get there. Calling a per-row trigger is already pretty expensive; calling a per-row trigger that has to *launch a process for every row* is going to be insanely bad. >> That approach would be much less likely to run afoul of limits on the >> number of background workers > > That would also be an argument against using them for parallelism, yet > we used background workers there. I guess that's sort of true, but parallel query intrinsically requires multiple processes or threads, whereas autonomous transactions only require that if you pick an implementation that requires that. Also, the parallel query facility is designed to only apply to operations that are already pretty expensive, namely long-running queries, but there are lots of use cases where an autonomous transaction gets spawned to do a very small amount of work, and not infrequently in a loop. So the startup cost is a significantly more serious problem for this use case. Of course, if we could decrease the startup cost of a bgworker, that'd be good for parallel query, too, because then we could deploy it on shorter queries. But the point remains that for parallel query the planner can always decide to use a non-parallel plan if the query is cheap enough that the startup cost of a worker will be a problem. That isn't the case here; if the worker startup cost is a problem, then you'll just end up with really slow SQL code. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 3 Sep. 2016 20:27, "Greg Stark" wrote: > > Well using a separate process also requires rewriting locking and > deadlock detection since a reasonable user might expect that second > process to have access to data locked in their current transaction. The user is going to hit some confusing issues. Xact1: alter table... Add column... Xact2: inserts data with new format Xact2: autonomous commits leaving xact1 running Xact2: rollback There are similar issues with FKs and and sorts. It isn't limited to schema changes. I don't see how autonomous xacts that inherit parent xact's uncommitted data, locks, etc can ever really be safe. Useful, but use at your own (considerable) risk and unlikely to be worth the issues they introduce. If they inherit locks but don't see uncommitted data it'd be even worse. See the autonomous commit subxact thread for more on these issues. > > Parallel query is currently restricted to read-only queries however. > Autonomous transactions will certainly need to be read-write so the > question then is what problems led to the restriction in parallel > query and are they any more tractable with autonomous transactions? Parallel query is fairly different because it doesn't have it's own xact. This should mean no need to be able to wait on a separate virtualxid or xid, no need to have their own TransactionID allocated, no complex considerations of visibility and most importantly can't commit separately.
Re: [HACKERS] autonomous transactions
> On Sep 3, 2016, at 5:25 AM, Greg Stark wrote: > > On Sat, Sep 3, 2016 at 12:09 PM, Simon Riggs wrote: >> So doing autonomous transactions inside a single backend doesn't gain >> you very much, yet it is an enormously invasive patch to do it that >> way, not least because it requires you to rewrite locking and >> deadlocks to make them work correctly when proc is not 1:1 with xid. >> And as Serge points out it introduces other restrictions that we know >> about now, perhaps more as well. > > Well using a separate process also requires rewriting locking and > deadlock detection since a reasonable user might expect that second > process to have access to data locked in their current transaction.The > plus side is that we're already facing that issue with parallel query > so at least it's something that only has to be solved once instead of > a new problem. I can’t speak for reasonable users, (or persons in fact ;-) But… previous implementations of ATs do fully expect them to deadlock on their parents and not see uncommitted changes. So if one’s goal is to merely match competitors’ behavior then that part is a non issue. I do not recall the single backend approach having been particularly invasive. We managed to do a the 9.4 -> 9.5 merge with little problem despite it. IMHO, solving the problem of passing variables to and from an AT is required for viability of the feature. How else would the AT know what it’s supposed to do? Starting an AT within a DDL transaction seems a much more narrow use case to me. Interestingly, despite being supported in PL/SQL on nested BEGIN END blocks, we nearly exclusively see AT’s covering the entire function or trigger. This usage property can be used to narrow the scope of variable passing to function parameters. Cheers Serge Rielau salesforce.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Sat, Sep 3, 2016 at 12:09 PM, Simon Riggs wrote: > So doing autonomous transactions inside a single backend doesn't gain > you very much, yet it is an enormously invasive patch to do it that > way, not least because it requires you to rewrite locking and > deadlocks to make them work correctly when proc is not 1:1 with xid. > And as Serge points out it introduces other restrictions that we know > about now, perhaps more as well. Well using a separate process also requires rewriting locking and deadlock detection since a reasonable user might expect that second process to have access to data locked in their current transaction.The plus side is that we're already facing that issue with parallel query so at least it's something that only has to be solved once instead of a new problem. Parallel query is currently restricted to read-only queries however. Autonomous transactions will certainly need to be read-write so the question then is what problems led to the restriction in parallel query and are they any more tractable with autonomous transactions? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 2 September 2016 at 09:45, Robert Haas wrote: > On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut > wrote: >> I would like to propose the attached patch implementing autonomous >> transactions for discussion and review. > > I'm pretty skeptical of this approach. Like Greg Stark, Serge Rielau, > and Constantin Pan, I had expected that autonomous transactions should > be implemented inside of a single backend, without relying on workers. The problem is that we have limits on the number of concurrent transactions, which is currently limited by the number of procs. We could expand that but given the current way we do snapshots there will be realistically low limits however we do this. So doing autonomous transactions inside a single backend doesn't gain you very much, yet it is an enormously invasive patch to do it that way, not least because it requires you to rewrite locking and deadlocks to make them work correctly when proc is not 1:1 with xid. And as Serge points out it introduces other restrictions that we know about now, perhaps more as well. Given that I've already looked into the "single backend" approach in detail, it looks to me that Peter's approach is viable and robust. > That approach would be much less likely to run afoul of limits on the > number of background workers That would also be an argument against using them for parallelism, yet we used background workers there. > , and it will probably perform > considerably better too, especially when the autonomous transaction > does only a small amount of work, like inserting a log message > someplace. That is not to say that providing an interface to some > pg_background-like functionality is a bad idea; there's been enough > interest in that from various quarters to suggest that it's actually > quite useful, and I don't even think that it's a bad plan to integrate > that with the PLs in some way. However, I think that it's a different > feature than autonomous transactions. As others have also noted, it > can be used to fire-and-forget a command, or to run a command while > foreground processing continues, both of which would be out of scope > for an autonomous transaction facility per se. So my suggestion is > that you pursue the work but change the name. We agree that we like the infrastructure Peter has provided is useful, so that part can go ahead. If that infrastructure can be used in a simple syntactic way to provide Autonomous Transactions, then I say let that happen. Peter, please comment on how much infrastructure is required to implement ATs this way? If somebody believes there is a better way for ATs, that is just an optimization of the limits and can occur later, if the people who believe there is a better way can prove that is the case and come up with a patch. It's OK for features to go in now and have better infrastructure later, e.g. LISTEN/NOTIFY. I would very much like to see ATs in v10 and this is a viable approach with working code. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 9/2/16 3:45 AM, Robert Haas wrote: So my suggestion is that you pursue the work but change the name. That might make the plpgsql issues significantly easier to deal with as well, by making it very explicit that you're doing something with a completely separate connection. That would make requiring special handling for passing plpgsql variables to a query much less confusing. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut wrote: > I would like to propose the attached patch implementing autonomous > transactions for discussion and review. I'm pretty skeptical of this approach. Like Greg Stark, Serge Rielau, and Constantin Pan, I had expected that autonomous transactions should be implemented inside of a single backend, without relying on workers. That approach would be much less likely to run afoul of limits on the number of background workers, and it will probably perform considerably better too, especially when the autonomous transaction does only a small amount of work, like inserting a log message someplace. That is not to say that providing an interface to some pg_background-like functionality is a bad idea; there's been enough interest in that from various quarters to suggest that it's actually quite useful, and I don't even think that it's a bad plan to integrate that with the PLs in some way. However, I think that it's a different feature than autonomous transactions. As others have also noted, it can be used to fire-and-forget a command, or to run a command while foreground processing continues, both of which would be out of scope for an autonomous transaction facility per se. So my suggestion is that you pursue the work but change the name. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Thu, Sep 1, 2016 at 8:38 PM, Andres Freund wrote: > > I'm not convinced this makes much sense. All FDWs, dblink etc. already > allow you do stuff outside of a transaction. You as a DBA can prevent FDWs from being used and dblink is an extension that you don't have to install. So if preventing side-effects is necessary in your application, that can be achieved by simply not installing dblink and preventing FDWs. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 2016-08-31 06:10:31 +0200, Joel Jacobson wrote: > This is important if you as a caller function want to be sure none of > the work made by anything called down the stack gets committed. > That is, if you as a caller decide to rollback, e.g. by raising an > exception, and you want to be sure *everything* gets rollbacked, > including all work by functions you've called. > If the caller can't control this, then the author of the caller > function would need to inspect the source code of all function being > called, to be sure there are no code using autonomous transactions. I'm not convinced this makes much sense. All FDWs, dblink etc. already allow you do stuff outside of a transaction. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Wed, Aug 31, 2016 at 6:22 PM, Simon Riggs wrote: > On 31 August 2016 at 14:09, Joel Jacobson wrote: >> My idea on how to deal with this would be to mark the function to be >> "AUTONOMOUS" similar to how a function is marked to be "PARALLEL >> SAFE", >> and to throw an error if a caller that has blocked autonomous >> transactions tries to call a function that is marked to be autonomous. >> >> That way none of the code that needs to be audited would ever get executed. > > Not sure I see why you would want to turn off execution for only some > functions. > > What happens if your function calls some other function with > side-effects? I'm not sure I understand your questions. All volatile functions modifying data have side-effects. What I meant was if they are allowed to commit it even if the caller doesn't want to. However, I'll try to clarify the two scenarios I envision: 1. If a function is declared AUTONOMOUS and it gets called, then that means nothing in the txn has blocked autonomous yet and the function and any other function will be able to do autonomous txns from that here on, so if some function would try to block autonomous that would throw an error. 2. If a function has blocked autonomous and something later on tries to call a function declared AUTONOMOUS then that would throw an error. Basically, we start with a NULL state where autonomous is neither blocked or explicitly allowed. Whatever happens first decides if autonomous transactions will explicitly be blocked or allowed during the txn. So we can go from NULL -> AUTONOMOUS ALLOWED or NULL -> AUTONOMOUS BLOCKED, but that's the only two state transitions possible. Once set, it cannot be changed. If nothing in an application cares about autonomous transactions, they don't have to do anything special, they don't need to modify any of their code. But if it for some reason is important to block autonomous transactions because the application is written in a way where it is expected a RAISE EXCEPTION always rollbacks everything, then the author of such an application (e.g. me) can just block autonomous transactions and continue to live happily ever after without having to dream nightmares about developers misusing the feature, and only use it when appropriate. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Thu, Sep 1, 2016 at 12:12 AM, Vik Fearing wrote: > Part of what people want this for is to audit what people *try* to do. > We can already audit what they've actually done. > > With your solution, we still wouldn't know when an unauthorized attempt > to do something happened. The unauthorized attempt to execute the function will still be logged to the PostgreSQL log file since it would throw an error, just like trying to connect with e.g. an invalid username would be logged to the log files. I think that's enough for that use-case, since it's arguably not an application layer problem, since no part of the code was ever executed. But if someone tries to execute a function where one of the input params is a password and the function raises an exception if the password is incorrect and wants to log the unauthorized attempt, then that would be a good example of when you could use and would need to use autonomous transactions to log the invalid password attempt. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Wed, 31 Aug 2016 14:46:30 +0100 Greg Stark wrote: > On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut > wrote: > > - A API interface to open a "connection" to a background worker, run > > queries, get results: AutonomousSessionStart(), > > AutonomousSessionEnd(), AutonomousSessionExecute(), etc. The > > communication happens using the client/server protocol. > > I'm surprised by the background worker. I had envisioned autonomous > transactions being implemented by allowing a process to reserve a > second entry in PGPROC with the same pid. Or perhaps save its existing > information in a separate pgproc slot (or stack of slots) and > restoring it after the autonomous transaction commits. > > Using a background worker mean that the autonomous transaction can't > access any state from the process memory. Parameters in plpgsql are a > symptom of this but I suspect there will be others. What happens if a > statement timeout occurs during an autonomous transaction? What > happens if you use a pl language in the autonomous transaction and if > it tries to use non-transactional information such as prepared > statements? I am trying to implement autonomous transactions that way. I have already implemented suspending and restoring the parent transaction state, at least some of it. The next thing on the plan is the procarray/snapshot stuff. I think we should reuse the same PGPROC for the autonomous transaction, and allocate a stack of PGXACTs for the case of nested autonomous transactions. Solving the more general problem, running multiple concurrent transactions with a single backend, may also be interesting for some users. Autonomous transactions would then be just a use case for that feature. Regards, Constantin Pan Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 08/31/2016 03:09 PM, Joel Jacobson wrote: > On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova > wrote: >> >> On 30 August 2016 at 23:10, Joel Jacobson wrote: >>> >>> There should be a way to within the session and/or txn permanently >>> block autonomous transactions. >>> >> >> This will defeat one of the use cases of autonomous transactions: auditing > > My idea on how to deal with this would be to mark the function to be > "AUTONOMOUS" similar to how a function is marked to be "PARALLEL > SAFE", > and to throw an error if a caller that has blocked autonomous > transactions tries to call a function that is marked to be autonomous. > > That way none of the code that needs to be audited would ever get executed. Part of what people want this for is to audit what people *try* to do. We can already audit what they've actually done. With your solution, we still wouldn't know when an unauthorized attempt to do something happened. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
> On Aug 31, 2016, at 6:46 AM, Greg Stark wrote: > > Using a background worker mean that the autonomous transaction can't > access any state from the process memory. Parameters in plpgsql are a > symptom of this but I suspect there will be others. What happens if a > statement timeout occurs during an autonomous transaction? What > happens if you use a pl language in the autonomous transaction and if > it tries to use non-transactional information such as prepared > statements? > +1 on this. The proposed solution loosely matches what was done in DB2 9.7 and it runs into the same complexity. Passing local variable or session level variables back and forth became a source of grief. At SFDC PG we have taken a different tack: 1. Gather up all the transaction state that is scattered across global variables into a struct 2. backup/restore transaction state when an autonomous transaction is invoked. This allows full access to all non-transactional state. The downside is that full access also includes uncommitted DDL (shared recache). So we had to restrict DDL in the parent transaction prior to the spawning of the child. If there is interest in exploring this kind of solution as an alternative I can elaborate. Cheers Serge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Wed, Aug 31, 2016 at 3:11 PM, Craig Ringer wrote: > > I suspect that there'll be way too much code that relies on stashing > xact-scoped stuff in globals for that to be viable. Caches alone. > Peter will be able to explain more, I'm sure. > > We'd probably need a new transaction data object that everything > xact-scoped hangs off, so we can pass it everywhere or swap it out of > some global. The mechanical refactoring alone would be pretty scary, > not to mention the complexity of actually identifying all the less > obvious places that need changing. Well this is the converse of the same problem. Today process state and transaction are tied together. One way or another you're trying to split that -- either by having two processes share state or by having one process manage two transactions. I suppose we already have the infrastructure for parallel query so there's at least some shared problem space there. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 31 August 2016 at 14:09, Joel Jacobson wrote: > On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova > wrote: >> >> On 30 August 2016 at 23:10, Joel Jacobson wrote: >> > >> > There should be a way to within the session and/or txn permanently >> > block autonomous transactions. >> > >> >> This will defeat one of the use cases of autonomous transactions: auditing > > My idea on how to deal with this would be to mark the function to be > "AUTONOMOUS" similar to how a function is marked to be "PARALLEL > SAFE", > and to throw an error if a caller that has blocked autonomous > transactions tries to call a function that is marked to be autonomous. > > That way none of the code that needs to be audited would ever get executed. Not sure I see why you would want to turn off execution for only some functions. What happens if your function calls some other function with side-effects? How would you roll that back? How would you mark functions for the general case? Functions with side effects can't be tested with simple unit tests; that has nothing to do with autonomous transactions. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 31/08/16 16:11, Craig Ringer wrote: On 31 August 2016 at 21:46, Greg Stark wrote: On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut wrote: - A API interface to open a "connection" to a background worker, run queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(), AutonomousSessionExecute(), etc. The communication happens using the client/server protocol. Peter, you mention "Oracle-style autonomous transaction blocks". What are the semantics to be expected of those with regards to: - Accessing objects exclusively locked by the outer xact or where the requested lockmode conflicts with a lock held by the outer xact - Visibility of data written by the outer xact That would be my question as well. Also, is it intended (outside the plpgsql interface) that the autonomous xact can proceed concurrently/interleaved with a local backend xact? i.e. the local backend xact isn't suspended and you're allowed to do things on the local backend as well? If so, what handling do you have in mind for deadlocks between the local backend xact and the bgworker with the autonomous xact? I'd expect the local backend to always win, killing the autonomous xact every time. I would expect that in PLs it's handled by them, if you misuse this on C level that's your problem? I'm surprised by the background worker. I had envisioned autonomous transactions being implemented by allowing a process to reserve a second entry in PGPROC with the same pid. Or perhaps save its existing information in a separate pgproc slot (or stack of slots) and restoring it after the autonomous transaction commits. I suspect that there'll be way too much code that relies on stashing xact-scoped stuff in globals for that to be viable. Caches alone. Peter will be able to explain more, I'm sure. I can also see some advantages in bgworker approach. For example it could be used for "fire and forget" type of interface in the future, where you return as soon as you send exec and don't care about waiting for result. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 31 August 2016 at 21:46, Greg Stark wrote: > On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut > wrote: >> - A API interface to open a "connection" to a background worker, run >> queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(), >> AutonomousSessionExecute(), etc. The communication happens using the >> client/server protocol. Peter, you mention "Oracle-style autonomous transaction blocks". What are the semantics to be expected of those with regards to: - Accessing objects exclusively locked by the outer xact or where the requested lockmode conflicts with a lock held by the outer xact - Visibility of data written by the outer xact ? Also, is it intended (outside the plpgsql interface) that the autonomous xact can proceed concurrently/interleaved with a local backend xact? i.e. the local backend xact isn't suspended and you're allowed to do things on the local backend as well? If so, what handling do you have in mind for deadlocks between the local backend xact and the bgworker with the autonomous xact? I'd expect the local backend to always win, killing the autonomous xact every time. > I'm surprised by the background worker. I had envisioned autonomous > transactions being implemented by allowing a process to reserve a > second entry in PGPROC with the same pid. Or perhaps save its existing > information in a separate pgproc slot (or stack of slots) and > restoring it after the autonomous transaction commits. I suspect that there'll be way too much code that relies on stashing xact-scoped stuff in globals for that to be viable. Caches alone. Peter will be able to explain more, I'm sure. We'd probably need a new transaction data object that everything xact-scoped hangs off, so we can pass it everywhere or swap it out of some global. The mechanical refactoring alone would be pretty scary, not to mention the complexity of actually identifying all the less obvious places that need changing. Consider invalidation callbacks. They're always "fun", and so simple to get right -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut wrote: > - A API interface to open a "connection" to a background worker, run > queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(), > AutonomousSessionExecute(), etc. The communication happens using the > client/server protocol. > I'm surprised by the background worker. I had envisioned autonomous transactions being implemented by allowing a process to reserve a second entry in PGPROC with the same pid. Or perhaps save its existing information in a separate pgproc slot (or stack of slots) and restoring it after the autonomous transaction commits. Using a background worker mean that the autonomous transaction can't access any state from the process memory. Parameters in plpgsql are a symptom of this but I suspect there will be others. What happens if a statement timeout occurs during an autonomous transaction? What happens if you use a pl language in the autonomous transaction and if it tries to use non-transactional information such as prepared statements? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
2016-08-31 15:09 GMT+02:00 Joel Jacobson : > On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova > wrote: > > > > On 30 August 2016 at 23:10, Joel Jacobson wrote: > > > > > > There should be a way to within the session and/or txn permanently > > > block autonomous transactions. > > > > > > > This will defeat one of the use cases of autonomous transactions: > auditing > > My idea on how to deal with this would be to mark the function to be > "AUTONOMOUS" similar to how a function is marked to be "PARALLEL > SAFE", > and to throw an error if a caller that has blocked autonomous > transactions tries to call a function that is marked to be autonomous. > > That way none of the code that needs to be audited would ever get executed. > I like this idea - it allows better (cleaner) snapshot isolation. Regards Pavel > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] autonomous transactions
On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova wrote: > > On 30 August 2016 at 23:10, Joel Jacobson wrote: > > > > There should be a way to within the session and/or txn permanently > > block autonomous transactions. > > > > This will defeat one of the use cases of autonomous transactions: auditing My idea on how to deal with this would be to mark the function to be "AUTONOMOUS" similar to how a function is marked to be "PARALLEL SAFE", and to throw an error if a caller that has blocked autonomous transactions tries to call a function that is marked to be autonomous. That way none of the code that needs to be audited would ever get executed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 30 August 2016 at 20:50, Peter Eisentraut wrote: > > - Patches to PL/pgSQL to implement Oracle-style autonomous transaction > blocks: > > AS $$ > DECLARE > PRAGMA AUTONOMOUS_TRANSACTION; > BEGIN > FOR i IN 0..9 LOOP > START TRANSACTION; > INSERT INTO test1 VALUES (i); > IF i % 2 = 0 THEN > COMMIT; > ELSE > ROLLBACK; > END IF; > END LOOP; > > RETURN 42; > END; > $$; > this is the syntax it will use? i just compiled this in head and created a function based on this one. The main difference is that the column in test1 it's a pk so i used INSERT ON CONFLICT DO NOTHING and i'm getting this error postgres=# select foo(); LOG: namespace item variable itemno 1, name val CONTEXT: PL/pgSQL function foo() line 7 at SQL statement STATEMENT: select foo(); ERROR: null value in column "i" violates not-null constraint DETAIL: Failing row contains (null). STATEMENT: INSERT INTO test1 VALUES (val) ON CONFLICT DO NOTHING ERROR: null value in column "i" violates not-null constraint DETAIL: Failing row contains (null). CONTEXT: PL/pgSQL function foo() line 7 at SQL statement STATEMENT: select foo(); ERROR: null value in column "i" violates not-null constraint DETAIL: Failing row contains (null). CONTEXT: PL/pgSQL function foo() line 7 at SQL statement this happens even everytime i use the PRAGMA even if no START TRANSACTION, COMMIT or ROLLBACK are used -- Jaime Casanova www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On 30 August 2016 at 23:10, Joel Jacobson wrote: > > There should be a way to within the session and/or txn permanently > block autonomous transactions. > This will defeat one of the use cases of autonomous transactions: auditing > > Coding conventions, rules and discipline are all good and will help > against misuse of the feature, but some day someone will make a > mistake and wrongly use the autonomous transaction and cause unwanted > unknown side-effect I as a caller function didn't expect or know > about. > well, if the feature is not guilty why do you want to put it in jail? -- Jaime Casanova www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
I would love to see autonomous transactions in core. I just have one major concern, but thankfully it's easily addressed. There should be a way to within the session and/or txn permanently block autonomous transactions. This is important if you as a caller function want to be sure none of the work made by anything called down the stack gets committed. That is, if you as a caller decide to rollback, e.g. by raising an exception, and you want to be sure *everything* gets rollbacked, including all work by functions you've called. If the caller can't control this, then the author of the caller function would need to inspect the source code of all function being called, to be sure there are no code using autonomous transactions. Coding conventions, rules and discipline are all good and will help against misuse of the feature, but some day someone will make a mistake and wrongly use the autonomous transaction and cause unwanted unknown side-effect I as a caller function didn't expect or know about. Once you have blocked autonomous transactions in a session or txn, then any function called must not be able to unblock it (in the session or txn), otherwise it defeats the purpose. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
Robert Haas wrote: On Thu, Sep 16, 2010 at 5:19 AM, Dimitri Fontaine wrote: I think they call that dynamic scope, in advanced programming language. I guess that's calling for a quote of Greenspun's Tenth Rule: Any sufficiently complicated C or Fortran program contains an ad hoc informally-specified bug-ridden slow implementation of half of Common Lisp. So the name of the game could be to find out a way to implement (a limited form of) dynamic scoping in PostgreSQL, in C, then find out all and any backend local variable that needs that to support autonomous transactions, then make it happen… Right? Interestingly, PostgreSQL was originally written in LISP, and there are remnants of that in the code today; for example, our heavy use of List nodes. But I don't think that has much to do with this project. I plan to reserve judgment on the best way of managing the relevant state until such time as someone has gone to the trouble of identifying what state that is. It would probably do Pg some good to try and recapture its functional language roots where reasonably possible. I believe that, design-wise, functional languages really are the best way to do object-relational databases, given that pure functions and immutable data structures are typically the best way to express anything one would do with them. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
Robert Haas writes: > I plan to reserve judgment on the best way of managing the relevant > state until such time as someone has gone to the trouble of > identifying what state that is. The really fundamental problem here is that you never will be able to identify all such state. Even assuming that you successfully completed the herculean task of fixing the core backend, what of add-on code? (This is also why I'm quite unimpressed with the idea of trying to get backends to switch to a different database after startup.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
On Thu, Sep 16, 2010 at 5:19 AM, Dimitri Fontaine wrote: > Robert Haas writes: >> One thing that strikes me (maybe this is obvious) is that the >> execution of the main transaction and the autonomous transaction are >> not interleaved: it's a stack. So in terms of globals and stuff, >> assuming you knew which things needed to be updated, you could push >> all that stuff off to the side, do whatever with the new transaction, >> and then restore all the context afterwards. > > I think they call that dynamic scope, in advanced programming > language. I guess that's calling for a quote of Greenspun's Tenth Rule: > > Any sufficiently complicated C or Fortran program contains an ad hoc > informally-specified bug-ridden slow implementation of half of Common > Lisp. > > So the name of the game could be to find out a way to implement (a > limited form of) dynamic scoping in PostgreSQL, in C, then find out all > and any backend local variable that needs that to support autonomous > transactions, then make it happen… Right? Interestingly, PostgreSQL was originally written in LISP, and there are remnants of that in the code today; for example, our heavy use of List nodes. But I don't think that has much to do with this project. I plan to reserve judgment on the best way of managing the relevant state until such time as someone has gone to the trouble of identifying what state that is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
Robert Haas writes: > One thing that strikes me (maybe this is obvious) is that the > execution of the main transaction and the autonomous transaction are > not interleaved: it's a stack. So in terms of globals and stuff, > assuming you knew which things needed to be updated, you could push > all that stuff off to the side, do whatever with the new transaction, > and then restore all the context afterwards. I think they call that dynamic scope, in advanced programming language. I guess that's calling for a quote of Greenspun's Tenth Rule: Any sufficiently complicated C or Fortran program contains an ad hoc informally-specified bug-ridden slow implementation of half of Common Lisp. So the name of the game could be to find out a way to implement (a limited form of) dynamic scoping in PostgreSQL, in C, then find out all and any backend local variable that needs that to support autonomous transactions, then make it happen… Right? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions (was Re: TODO note)
On Wed, Sep 15, 2010 at 6:21 PM, Alvaro Herrera wrote: > Excerpts from Robert Haas's message of mié sep 15 14:57:29 -0400 2010: > >> I guess so, but the devil is in the details. I suspect that we don't >> actually want to fork a new backend for every autonomous transactions. >> That would be pretty expensive, and we already have an expensive way >> of emulating this functionality using dblink. Finding all of the bits >> that think there's only one top-level transaction per backend and >> generalizing them to support multiple top-level transactions per >> backend doesn't sound easy, though, > > Yeah, and the transaction handling code is already pretty complex. Yep. >> especially since you must do it without losing performance. > > Presumably we'd have fast paths for the main transaction, and > any autonomous transactions beside that one would incur in some > slowdown. > > I think the complex parts are, first, figuring out what to do with > global variables that currently represent a transaction (they are > sprinkled all over the place); and second, how to represent the > autonomous transactions in shared memory without requiring the PGPROC > array to be arbitrarily resizable. > > The other alternative would be to bolt the autonomous transaction > somehow in the current subtransaction stack thing and marking it in some > different way so that we can reuse the games we play with "push/pop" > there. That still leaves us with the PGPROC problem. I wonder if we could use/generalize pg_subtrans in some way to handle the PGPROC problem. I haven't thought about it much, though. One thing that strikes me (maybe this is obvious) is that the execution of the main transaction and the autonomous transaction are not interleaved: it's a stack. So in terms of globals and stuff, assuming you knew which things needed to be updated, you could push all that stuff off to the side, do whatever with the new transaction, and then restore all the context afterwards. That doesn't help in terms of PGPROC, of course, but for backend-local state it seems workable. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions (was Re: TODO note)
Excerpts from Robert Haas's message of mié sep 15 14:57:29 -0400 2010: > I guess so, but the devil is in the details. I suspect that we don't > actually want to fork a new backend for every autonomous transactions. > That would be pretty expensive, and we already have an expensive way > of emulating this functionality using dblink. Finding all of the bits > that think there's only one top-level transaction per backend and > generalizing them to support multiple top-level transactions per > backend doesn't sound easy, though, Yeah, and the transaction handling code is already pretty complex. > especially since you must do it without losing performance. Presumably we'd have fast paths for the main transaction, and any autonomous transactions beside that one would incur in some slowdown. I think the complex parts are, first, figuring out what to do with global variables that currently represent a transaction (they are sprinkled all over the place); and second, how to represent the autonomous transactions in shared memory without requiring the PGPROC array to be arbitrarily resizable. The other alternative would be to bolt the autonomous transaction somehow in the current subtransaction stack thing and marking it in some different way so that we can reuse the games we play with "push/pop" there. That still leaves us with the PGPROC problem. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions (was Re: TODO note)
Robert Haas wrote: On Wed, Sep 15, 2010 at 2:32 PM, Darren Duncan wrote: The point being, the answer to how to implement autonomous transactions could be as simple as, do the same thing as how you manage multiple concurrent client sessions, more or less. If each client gets its own Postgres OS process, then an autonomous transaction just farms out to another one of those which does the work. Or maybe there could be a lighter weight version of this. Does this design principle seem reasonable? I guess so, but the devil is in the details. I suspect that we don't actually want to fork a new backend for every autonomous transactions. That would be pretty expensive, and we already have an expensive way of emulating this functionality using dblink. Finding all of the bits that think there's only one top-level transaction per backend and generalizing them to support multiple top-level transactions per backend doesn't sound easy, though, especially since you must do it without losing performance. As you say, the devil is in the details, but I see this as mainly being an implementation issue, where an essentially same task could abstract different possible implementations, some more light or heavyweight. This is loosely how I look at the issue conceptually, meaning like the illusion that the DBMS presents to the user: The DBMS is a multi-process virtual machine, the database being worked on is the file system or disk, and uncommitted transactions are data structures in memory that may have multiple versions. Each autonomous transaction is associated with a single process. A process can either be started by the user (client connection) or by another process (autonomous transaction). Regardless of how a process is started, the way to manage multiple autonomous tasks is that each has its own process. Tasks that are not mutually autonomous would be within the same process. Child transactions or savepoints have the same process as their parent when the parent can rollback their commits. Whether the DBMS uses multiple OS threads or multiple OS processes or uses coroutines or whatever is an implementation detail. A point here being that over time Postgres can evolve to use either multiple OS processes or multiple threads or a coroutine system within a single thread/process, to provide the illusion of each autonomous transaction being an independent process, and the data structures and algorithms for managing autonomous transactions can be similar to or the same as multiple client connections, since conceptually they are alike. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions (was Re: TODO note)
On Wed, Sep 15, 2010 at 2:32 PM, Darren Duncan wrote: > The point being, the answer to how to implement autonomous transactions > could be as simple as, do the same thing as how you manage multiple > concurrent client sessions, more or less. If each client gets its own > Postgres OS process, then an autonomous transaction just farms out to > another one of those which does the work. Or maybe there could be a lighter > weight version of this. > > Does this design principle seem reasonable? I guess so, but the devil is in the details. I suspect that we don't actually want to fork a new backend for every autonomous transactions. That would be pretty expensive, and we already have an expensive way of emulating this functionality using dblink. Finding all of the bits that think there's only one top-level transaction per backend and generalizing them to support multiple top-level transactions per backend doesn't sound easy, though, especially since you must do it without losing performance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autonomous transactions
Josh Berkus escribió: > All, > >> >> Added to TODO: >> >> * Add anonymous transactions >> >> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php > > IMHO, autonomous transactions should be part of a package with a > spec-compliant CREATE PROCEDURE statement. IMHO we should try to get both things separately, otherwise we will never get either. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] autonomous transactions
All, Added to TODO: * Add anonymous transactions http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php IMHO, autonomous transactions should be part of a package with a spec-compliant CREATE PROCEDURE statement.That is, the difference between PROCEDURES and FUNCTIONS would be that: -- PROCs have autonomous transactions -- PROCs have to be excuted with CALL, and can't go in a query -- PROCs don't necessarily return a result --Josh Berkus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] autonomous transactions
Bruce Momjian wrote: > > Plus I think we'd be able to improve the code for CREATE INDEX under > > HOT, and probably a few other wrinkly bits of code. > > Added to TODO: > > * Add anonymous transactions > > http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php Sorry, updated to "Add _autonomous_ transactions". (The one time I don't cut/paste and I get it wrong.) -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] autonomous transactions
Simon Riggs wrote: > On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote: > > > > > > Agreed. I think Pavel Stehule was doing some experiments with them, I > > > don't know if he got anywhere. > > > > > > > I did only first research. Any hack is possible - you can stack > > current transaction, but real implementation needs similar work like > > nested transaction :( and it is too low level for me. And some code > > cleaning is necessary. There are global variables. > > > > And there is most important question about data visibility - is > > autonomous transaction independent on main transaction (isolation)? > > You have to thing about deadlock, about reference integrity, etc. This > > task isn't simple. > > Yes, I think autonomous transactions should be on the TODO. They're > useful for > - error logging > - auditing > - creating new partitions automatically > > Plus I think we'd be able to improve the code for CREATE INDEX under > HOT, and probably a few other wrinkly bits of code. Added to TODO: * Add anonymous transactions http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] autonomous transactions
On Jan 25, 2008, at 7:27 AM, Decibel! wrote: On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote: Simon Riggs <[EMAIL PROTECTED]> writes: From looking at how Oracle does them, autonomous transactions are completely independent of the transaction that originates them -- they take a new database snapshot. This means that uncommitted changes in the originating transaction are not visible to the autonomous transaction. Oh! Recursion depth would need to be tested for as well. Nasty. Seems like the cloning-a-session idea would be a possible implementation path for these too. Oracle has a feature where you can effectively save a session and return to it. For example, if filling out a multi-page web form, you could save state in the database between those calls. I'm assuming that they use that capability for their autonomous transactions; save the current session to the stack, clone it, run the autonomous transaction, then restore the saved one. If you want to use it for webforms you cannot just put it on the stack - you had to put it in shared memory because you don't know if you will ever get the same database connection back from the pool. personally i like marko's idea. if a snapshot was identified by a key it would be perfect. we could present the snapshots saved as a nice nice superuser-readable system view (similar to what we do for 2PC) the only thing i would do is to give those snapshots some sort of timeout (configurable). otherwise we will get countless VACUUM related reports. this sounds like a very cool feature - definitely useful. many thanks, hans -- Cybertec Schönig & Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, 2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at
Re: [HACKERS] autonomous transactions
> On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote: > > Simon Riggs <[EMAIL PROTECTED]> writes: > > >> From looking at how Oracle does them, autonomous transactions are > > >> completely independent of the transaction that originates them -- > they > > >> take a new database snapshot. This means that uncommitted changes in > the > > >> originating transaction are not visible to the autonomous > transaction. > > > > > Oh! Recursion depth would need to be tested for as well. Nasty. > > > > Seems like the cloning-a-session idea would be a possible implementation > > path for these too. > > Oracle has a feature where you can effectively save a session and return > to it. For example, if filling out a multi-page web form, you could save > state in the database between those calls. I'm assuming that they use > that capability for their autonomous transactions; save the current > session to the stack, clone it, run the autonomous transaction, then > restore the saved one. > -- You are describing an uncommitted transaction and not an autonomous transaction. Transactions in Oracle are not automatically committed like they are in PostgreSQL. Here is a basic example of an autonomous transaction: create or replace procedure pr_log_error (p_error_message errorlog.message%type) is pragma autonomous_transaction; begin insert into errorlog (log_user, log_time, error_message) values (user, sysdate(), p_error_message); commit; exception when others then rollback; raise; end; And then you can call it from a procedure like this: create or replace procedure pr_example is begin null;--do some work commit; --commit the work exception when others pr_log_error(p_error_message => sqlerrm); rollback; raise; end; The autonomous transaction allows me to insert and commit a record in different transaction than the calling procedure so the calling procedure can rollback or commit. You can also remove the commit/rollback from pr_example and instead do it from the anonymous block that calls it. I just added it to make it clear that it is a different transaction than the error logging transaction. Jon ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] autonomous transactions
On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > >> From looking at how Oracle does them, autonomous transactions are > >> completely independent of the transaction that originates them -- they > >> take a new database snapshot. This means that uncommitted changes in the > >> originating transaction are not visible to the autonomous transaction. > > > Oh! Recursion depth would need to be tested for as well. Nasty. > > Seems like the cloning-a-session idea would be a possible implementation > path for these too. Oracle has a feature where you can effectively save a session and return to it. For example, if filling out a multi-page web form, you could save state in the database between those calls. I'm assuming that they use that capability for their autonomous transactions; save the current session to the stack, clone it, run the autonomous transaction, then restore the saved one. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpKD3eTOJmEA.pgp Description: PGP signature
Re: [HACKERS] autonomous transactions
On Tuesday 22 January 2008 11:02, Roberts, Jon wrote: > I really needed this functionality in PostgreSQL. A common use for > autonomous transactions is error logging. I want to log sqlerrm in a > function and raise an exception so the calling application knows there is > an error and I have it logged to a table. > > > > I figured out a way to "hack" an autonomous transaction by using a dblink > in a function and here is a simple example: > > > This is an enhanced version of the "hack", maybe it will be of some help... https://labs.omniti.com/trac/pgsoltools/browser/trunk/autonomous_logging_tool -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] autonomous transactions
On Jan 23, 2008 10:06 PM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> wrote: > On Jan 24, 2008 2:46 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > The Audit transaction, which is a autonomous transaction need not catch > any > > > error and resume the outer transaction. > > > > What if the logging fails, say because you forgot to create the audit > > table? > > > I get it now... Autonomous transactions are, umm, autonomous. The calling transaction doesn't know about or care whether the autonomous transaction succeeds or fails for any reason. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] autonomous transactions
On Jan 24, 2008 2:46 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Gokulakannan Somasundaram escribió: > > > The Audit transaction, which is a autonomous transaction need not catch > any > > error and resume the outer transaction. > > What if the logging fails, say because you forgot to create the audit > table? > I get it now... > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
Re: [HACKERS] autonomous transactions
Simon Riggs <[EMAIL PROTECTED]> writes: >> From looking at how Oracle does them, autonomous transactions are >> completely independent of the transaction that originates them -- they >> take a new database snapshot. This means that uncommitted changes in the >> originating transaction are not visible to the autonomous transaction. > Oh! Recursion depth would need to be tested for as well. Nasty. Seems like the cloning-a-session idea would be a possible implementation path for these too. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] autonomous transactions
On Wed, 2008-01-23 at 09:30 +, Gregory Stark wrote: > I think the hard part would be error handling. You have to be able to catch > any errors and resume the outer transaction. I agree that you'd need to do this, but I don't follow why it would be particularly difficult. You essentially have a stack of active transactions (since one autonomous transaction can start another autonomous transaction, and so forth). If you encounter an error in the current transaction, you abort it as normal, pop the stack, and resume execution of the originating transaction. I think the hard part is fixing the parts of the backend that assume that a single process can only have a single top-level transaction in progress at a given time. -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] autonomous transactions
Gokulakannan Somasundaram escribió: > The Audit transaction, which is a autonomous transaction need not catch any > error and resume the outer transaction. What if the logging fails, say because you forgot to create the audit table? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] autonomous transactions
On Wed, 2008-01-23 at 00:26 -0800, Neil Conway wrote: > On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote: > > And there is most important question about data visibility - is > > autonomous transaction independent on main transaction (isolation)? > > >From looking at how Oracle does them, autonomous transactions are > completely independent of the transaction that originates them -- they > take a new database snapshot. This means that uncommitted changes in the > originating transaction are not visible to the autonomous transaction. Oh! Recursion depth would need to be tested for as well. Nasty. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] autonomous transactions
> > > > I think the hard part would be error handling. You have to be able to > catch > any errors and resume the outer transaction. > I think this is not right. Autonomous transactions are used as soon as you catch a error in order to log them. It can be used even for auditing. But resuming the outer transaction etc should not be on the plate of autonomous transactions. I am making an example here ... Suppose you want to write a code which captures the attempt to change the sensitive information, and also fails the change made to sensitive information. In order to fail the change, we might need to rollback the transaction, which would prevent the attempt being logged. So if we have autonomous audit transaction, it will commit irrespective of the rollback which happened to the original transaction The Audit transaction, which is a autonomous transaction need not catch any error and resume the outer transaction. Thanks, Gokul.
Re: [HACKERS] autonomous transactions
"Neil Conway" <[EMAIL PROTECTED]> writes: > On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote: >> And there is most important question about data visibility - is >> autonomous transaction independent on main transaction (isolation)? > >>From looking at how Oracle does them, autonomous transactions are > completely independent of the transaction that originates them -- they > take a new database snapshot. This means that uncommitted changes in the > originating transaction are not visible to the autonomous transaction. I think the hard part would be error handling. You have to be able to catch any errors and resume the outer transaction. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] autonomous transactions
On 23/01/2008, Simon Riggs <[EMAIL PROTECTED]> wrote: > On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote: > > > > > > Agreed. I think Pavel Stehule was doing some experiments with them, I > > > don't know if he got anywhere. > > > > > > > I did only first research. Any hack is possible - you can stack > > current transaction, but real implementation needs similar work like > > nested transaction :( and it is too low level for me. And some code > > cleaning is necessary. There are global variables. > > > > And there is most important question about data visibility - is > > autonomous transaction independent on main transaction (isolation)? > > You have to thing about deadlock, about reference integrity, etc. This > > task isn't simple. > > Yes, I think autonomous transactions should be on the TODO. They're > useful for > - error logging > - auditing > - creating new partitions automatically > I worked on workflow implementation only in stored procedures. Without autonomous transaction you cannot implement some models. And it's usable for AQ. > Plus I think we'd be able to improve the code for CREATE INDEX under > HOT, and probably a few other wrinkly bits of code. > > -- > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] autonomous transactions
On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote: > And there is most important question about data visibility - is > autonomous transaction independent on main transaction (isolation)? >From looking at how Oracle does them, autonomous transactions are completely independent of the transaction that originates them -- they take a new database snapshot. This means that uncommitted changes in the originating transaction are not visible to the autonomous transaction. On Wed, 2008-01-23 at 08:13 +, Simon Riggs wrote: > Yes, I think autonomous transactions should be on the TODO. They're > useful for > - error logging > - auditing > - creating new partitions automatically I think they would also be useful to implement procedures that perform DDL operations or COMMITs / ROLLBACKs. -Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] autonomous transactions
On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote: > > > > Agreed. I think Pavel Stehule was doing some experiments with them, I > > don't know if he got anywhere. > > > > I did only first research. Any hack is possible - you can stack > current transaction, but real implementation needs similar work like > nested transaction :( and it is too low level for me. And some code > cleaning is necessary. There are global variables. > > And there is most important question about data visibility - is > autonomous transaction independent on main transaction (isolation)? > You have to thing about deadlock, about reference integrity, etc. This > task isn't simple. Yes, I think autonomous transactions should be on the TODO. They're useful for - error logging - auditing - creating new partitions automatically Plus I think we'd be able to improve the code for CREATE INDEX under HOT, and probably a few other wrinkly bits of code. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] autonomous transactions
> > Agreed. I think Pavel Stehule was doing some experiments with them, I > don't know if he got anywhere. > I did only first research. Any hack is possible - you can stack current transaction, but real implementation needs similar work like nested transaction :( and it is too low level for me. And some code cleaning is necessary. There are global variables. And there is most important question about data visibility - is autonomous transaction independent on main transaction (isolation)? You have to thing about deadlock, about reference integrity, etc. This task isn't simple. Pavel > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] autonomous transactions
> On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote: > > Maybe someone could enhance this concept to include it with the core > > database to provide autonomous transactions. > > I agree that autonomous transactions would be useful, but doing them via > dblink is a kludge. Kludge or hack but I agree! > If we're going to include anything in the core > database, it should be done properly (i.e. as an extension to the > existing transaction system). I agree! That is why I said "someone could enhance this concept to include it with the core database". Jon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] autonomous transactions
Neil Conway wrote: > On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote: > > Maybe someone could enhance this concept to include it with the core > > database to provide autonomous transactions. > > I agree that autonomous transactions would be useful, but doing them via > dblink is a kludge. If we're going to include anything in the core > database, it should be done properly (i.e. as an extension to the > existing transaction system). Agreed. I think Pavel Stehule was doing some experiments with them, I don't know if he got anywhere. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] autonomous transactions
On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote: > Maybe someone could enhance this concept to include it with the core > database to provide autonomous transactions. I agree that autonomous transactions would be useful, but doing them via dblink is a kludge. If we're going to include anything in the core database, it should be done properly (i.e. as an extension to the existing transaction system). -Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate