From: PFC <lists@peufeu.com>
To: pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Big IN() clauses etc : feature proposal
Date: Wed, 10 May 2006 21:23:59 +0200
Message-ID: <op.s9cn5908cigqcu@apollo13> (raw)
In-Reply-To: <20060510190011.GO99570@pervasive.com>
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>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Big IN() clauses etc : feature proposal
In-Reply-To: <op.s9cn5908cigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox