[HACKERS] Connection Pools and DISCARD ALL
There's been a lively discussion on JDBC list recently about how we handle connection pooling. This has connected a few thoughts in my head. That's made me think about the PHP interface, which issues a BEGIN; ROLLBACK; pair every time somebody connects to the pool. We should have a single/consistent way of starting a new connection to PostgreSQL when using a session pool. As committed, DISCARD ALL does everything but cannot be issued inside a transaction block. I'd like to propose that DISCARD ALL also issue a ROLLBACK command if it is issued from within a transaction block. That way whenever we reassign a session pool connection to another agent we can just issue a single command from all interfaces, without needing to test what the state of the connection is beforehand. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: As committed, DISCARD ALL does everything but cannot be issued inside a transaction block. I'd like to propose that DISCARD ALL also issue a ROLLBACK command if it is issued from within a transaction block. That was *intentional* to prevent mistakes. I understand; I'm challenging that intention. Neil's original commit message said that was intended to catch programmer mistakes and that such use is probably unintended. If the developer has attempted to issue it in the wrong place, he's probably also forgot to handle errors correctly, i.e. ROLLBACK then reissue. If we care about helping the developer we should make the command end the transaction block if one exists then issue it. Less code for the developer, less mistakes. Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. http://svr5.postgresql.org/pgsql-interfaces/2001-02/msg00116.php -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
Simon Riggs [EMAIL PROTECTED] writes: As committed, DISCARD ALL does everything but cannot be issued inside a transaction block. I'd like to propose that DISCARD ALL also issue a ROLLBACK command if it is issued from within a transaction block. That was *intentional* to prevent mistakes. Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Connection Pools and DISCARD ALL
On Thu, 2007-10-04 at 13:03 -0700, Neil Conway wrote: On Thu, 2007-10-04 at 15:50 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. Well, it's a WARNING, but your point is taken. Can't a clueful interface just check what the transaction status of the connection is, rather than unconditionally issuing a ROLLBACK? I think it can, but can't a clueful server do this and avoid the problem of non-clueful interfaces? This is making me think that we should just embed the session pool inside the server as well and have done with it. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
On Thu, 2007-10-04 at 15:50 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. Well, it's a WARNING, but your point is taken. Can't a clueful interface just check what the transaction status of the connection is, rather than unconditionally issuing a ROLLBACK? -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
Simon Riggs wrote: On Thu, 2007-10-04 at 13:03 -0700, Neil Conway wrote: On Thu, 2007-10-04 at 15:50 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. Well, it's a WARNING, but your point is taken. Can't a clueful interface just check what the transaction status of the connection is, rather than unconditionally issuing a ROLLBACK? I think it can, but can't a clueful server do this and avoid the problem of non-clueful interfaces? This is making me think that we should just embed the session pool inside the server as well and have done with it. Could we maybe have some flavor of ROLLBACK that doesn't issue a warning if no transaction is in progress? There is precedent for this sort of facility - DROP ... IF EXISTS. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
Simon Riggs wrote: This is making me think that we should just embed the session pool inside the server as well and have done with it. You mean prefork? That would be neat. I don't think it's all that impossible. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Connection Pools and DISCARD ALL
Andrew Dunstan [EMAIL PROTECTED] writes: Could we maybe have some flavor of ROLLBACK that doesn't issue a warning if no transaction is in progress? There is precedent for this sort of facility - DROP ... IF EXISTS. Something that would actually be doable for 8.3 would be to downgrade this particular WARNING to a NOTICE. A DBA who hasn't got log_min_messages set higher than NOTICE hasn't really got a lot of room to whine about bulky logs. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
