From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | neuhauser(at)sigpipe(dot)cz, PgSQL General <pgsql-general(at)postgresql(dot)org>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Subject: | Re: more anti-postgresql FUD |
Date: | 2006-10-16 09:05:33 |
Message-ID: | 45334B5D.4030602@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Merlin Moncure wrote:
> On 10/13/06, Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> wrote:
>> SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N;
>
> using offset to walk a table is extremely poor form because of:
> * poor performance
> * single user mentality
> * flat file mentality
>
> databases are lousy at this becuase they inheritly do not support
> abolute addressing of data -- nore should they, beause this is not
> what sql is all about. in short, 'offset' is a hack, albeit a useful
> one in some cases, but dont gripe when it doesn't deliver the goods.
>
> for server side browsing use cursors or a hybrid pl/pgqsl loop. for
> client side, browse fetching relative to the last key:
>
> select * from foo where p > p1 order by p limit k;
This does require some way for the client to keep a single transaction
open. If this kind of query is performed by a web application (as is
often the case), the "client" is the server side web script engine, and
not all of those beasts are capable of keeping a transaction open across
pages (PHP comes to mind).
This combined with expensive (complex) queries is regularly a pain.
The alternative solution of storing the query results in a temporary
table suffers from the same problem (the transaction is gone after the
first page).
I believe, as a result of this, it is not uncommon to pass the primary
key id's of all results on in a hidden field, so they are available for
quick querying on proceeding pages.
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2006-10-16 09:41:25 | Re: exploiting features of pg to obtain polymorphism |
Previous Message | Alban Hertroys | 2006-10-16 08:20:12 | Re: A query planner that learns |
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2006-10-16 09:45:40 | Re: more anti-postgresql FUD |
Previous Message | Shane Ambler | 2006-10-16 08:29:05 | Re: Postgresql Caching |