As I read the docs, a temp table doesn't solve our problem, as it does not persist between sessions. With a web page there is no guarentee that you will receive the same connection between requests, so a temp table doesn't solve the problem. It looks like you either have to create a real table (which is undesirable becuase it has to be physicaly synced, and TTFB will be very poor) or create an application tier in between the web tier and the database tier to allow data to persist between requests tied to a unique session id.
Looks like the solutions to this problem is not RDBMS IMHO. Alex Turner NetEconomist On Wed, 26 Jan 2005 12:11:49 +0200, Andrei Bintintan <[EMAIL PROTECTED]> wrote: > The problems still stays open. > > The thing is that I have about 20 - 30 clients that are using that SQL query > where the offset and limit are involved. So, I cannot create a temp table, > because that means that I'll have to make a temp table for each session... > which is a very bad ideea. Cursors somehow the same. In my application the > Where conditions can be very different for each user(session) apart. > > The only solution that I see in the moment is to work at the query, or to > write a more complex where function to limit the results output. So no > replace for Offset/Limit. > > Best regards, > Andy. > > > ----- Original Message ----- > From: "Greg Stark" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: "Richard Huxton" <email@example.com>; "Andrei Bintintan" > <[EMAIL PROTECTED]>; <firstname.lastname@example.org>; > <email@example.com> > Sent: Tuesday, January 25, 2005 8:28 PM > Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? > > > > > > Alex Turner <[EMAIL PROTECTED]> writes: > > > >> I am also very interesting in this very question.. Is there any way to > >> declare a persistant cursor that remains open between pg sessions? > >> This would be better than a temp table because you would not have to > >> do the initial select and insert into a fresh table and incur those IO > >> costs, which are often very heavy, and the reason why one would want > >> to use a cursor. > > > > TANSTAAFL. How would such a persistent cursor be implemented if not by > > building a temporary table somewhere behind the scenes? > > > > There could be some advantage if the data were stored in a temporary table > > marked as not having to be WAL logged. Instead it could be automatically > > cleared on every database start. > > > > -- > > greg > > > > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])