Date: Wed, 10 May 2006 21:23:59 +0200
To: pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Big IN() clauses etc : feature proposal
References: <op.s89zl30wcigqcu@apollo13>
	<20060509105214.P90693@vesihiisi.cksoft.de>
	<op.s8916gm6cigqcu@apollo13>
	<20060509113337.M90693@vesihiisi.cksoft.de>
	<op.s893vzcpcigqcu@apollo13> <20060509103632.GD29652@svana.org>
	<op.s897j60qcigqcu@apollo13> <1147190285.32304.43.camel@firebolt>
	<op.s9alu1vhcigqcu@apollo13> <16680.1147201981@sss.pgh.pa.us>
	<20060510190011.GO99570@pervasive.com>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s9cn5908cigqcu@apollo13>
In-Reply-To: <20060510190011.GO99570@pervasive.com>
User-Agent: Opera M2/8.51 (Linux, build 1462)


> Speaking of which, if a temp table is defined as ON COMMIT DROP or
> DELETE ROWS, there shouldn't be any need to store xmin/xmax, only
> cmin/cmax, correct?

	Yes, that's that type of table I was thinking about...
	You can't ROLLBACK a transaction on such a table.
	You can however rollback a savepoint and use "INSERT INTO tmp SELECT FROM  
tmp" which implies MVCC (I think ?)

	I was suggesting to be able to use FETCH (from a cursor) in the same way  
as SELECT, effectively using a named cursor (DECLARE...) as a simpler,  
faster version of a temporary table, but there is another (better ?)  
option :

	If rowcount estimates for functions are implemented, then a set-returning  
function can be written, which takes as argument a named cursor, and  
returns its rows.
	It would have accurate rowcount estimation (if the cursor is WITH SCROLL,  
which is the case here, rows are stored, so we know their number).

	Then you could do :

DECLARE my_cursor ... AS (query that we only want to do once)
SELECT ... FROM table1 JOIN fetch_cursor( my_cursor ) ON ...
SELECT ... FROM table2 JOIN fetch_cursor( my_cursor ) ON ...
SELECT ... FROM table3 JOIN fetch_cursor( my_cursor ) ON ...

	No need to redefine the FETCH keyword.
	An interesting functionalyty with minimal hassle.