Re: [PATCHES] Implementing RESET CONNECTION ...

[Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>]

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
  EnterpriseDB    http://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
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +