Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
OK, what would people like done with this patch? Our TODO list has: * -Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of temporary tables, removing any NOTIFYs, cursors, open transactions, prepared queries, currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. The difficult of this features is allowing RESET ALL to not affect changes made by the interface driver for its internal use. One idea is for this to be a protocol-only feature. Another approach is to notify the protocol when a RESET CONNECTION command is used. This patch does everything except reset currval(), but the big missing item is that it doesn't handle the protocol issues outlined in the TODO item. However, there also has been very little discussion on exactly how the protocol stuff would work. Should we add it for 8.2 and see if we get any problem reports? --- I have backed out the patch until there is more discussion. I now see that the CC list had an incorrect entry for the patches list, so I am unsure if others have seen this patch thoroughly. --- Hans-J?rgen Sch?nig wrote: We have implemented a patch which can be used by connection pools for instance. RESECT CONNECTION cleans up a backend so that it can be reused. Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open transactions, prepared statements and GUCs are cleaned up. I hope we have not missed important per-backend information. test=# BEGIN; BEGIN test=# RESET CONNECTION; RESET test=# COMMIT; WARNING: there is no transaction in progress COMMIT test=# PREPARE myplan(int, int) AS SELECT $1 + $2; PREPARE test=# RESET CONNECTION; RESET test=# EXECUTE myplan(1, 2); ERROR: prepared statement myplan does not exist test=# test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class; DECLARE CURSOR test=# FETCH NEXT FROM mycur; relname - views (1 row) test=# RESET CONNECTION; RESET test=# FETCH NEXT FROM mycur; ERROR: cursor mycur does not exist test=# CREATE TEMP TABLE mytmp (id int4); CREATE TABLE test=# RESET CONNECTION; RESET test=# INSERT INTO mytmp VALUES (10); ERROR: relation mytmp does not exist All regression tests passed. It would be nice if we had this in 8.1. Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
To complete the connection pooling for multiple users, it would be great to have a protocol level option to change roles semi-permanently (to reduce permissions). RESET SESSION AUTHORIZATION would then bounce back to that (new, set) role until another protocol-level role rollback. This would allow completely reusable connections per database while maintaining a real sandbox for each connection. On Tue, April 25, 2006 10:19 am, Bruce Momjian wrote: OK, what would people like done with this patch? Our TODO list has: * -Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of temporary tables, removing any NOTIFYs, cursors, open transactions, prepared queries, currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. The difficult of this features is allowing RESET ALL to not affect changes made by the interface driver for its internal use. One idea is for this to be a protocol-only feature. Another approach is to notify the protocol when a RESET CONNECTION command is used. This patch does everything except reset currval(), but the big missing item is that it doesn't handle the protocol issues outlined in the TODO item. However, there also has been very little discussion on exactly how the protocol stuff would work. Should we add it for 8.2 and see if we get any problem reports? ---(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] [PATCHES] Implementing RESET CONNECTION ...
Bruce Momjian pgman@candle.pha.pa.us writes: Should we add it for 8.2 and see if we get any problem reports? No. I don't believe this can work without a far more invasive patch than this is. To point out just one problem, what of cached plans in plpgsql functions? Those can't be carried across a genuine connection reset (permissions and search path are two reasons why not). And the protocol issues are not something you can just ignore, because the command does break reasonable driver-level expectations. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
Christopher Kings-Lynne wrote: What would be absolutely ideal is a reset connection command, plus some way of knowing via the protocol if it's needed or not. And a way of notifying the client that a reset has happened. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
What did we decide on RESET CONNECTION. Do we want an SQL command or something only the protocol can do? --- Oliver Jowett wrote: (cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
What would be absolutely ideal is a reset connection command, plus some way of knowing via the protocol if it's needed or not. Chris Bruce Momjian wrote: What did we decide on RESET CONNECTION. Do we want an SQL command or something only the protocol can do? --- Oliver Jowett wrote: (cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
(cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
