Re: Bug #866 related problem (ATTN Tom Lane)

Tom Lane wrote:
>
> Florian Wunderlich <fwunderlich(at)devbrain(dot)de> writes:
> > So can I be sure that every non-aggregate SELECT on tables joined with
> > unique indexes works, independent of the WHERE or ORDER BY?
>
> I would think that backwards scan on a join mostly doesn't work, but
> haven't tried it in any detail.

From what I've tried, this seems to be correct.

> A plan with a SORT node at the top *will* work, no matter what's under
> the SORT, so ORDER BY might mask problems in some cases.
>
> > Is anybody working on implementing this functionality?
>
> Not me... although I have thought about at least adding enough code to
> report an error in the cases that will give wrong answers.

What is the right solution for this problem then?

Creating a table in a transaction, declaring a cursor on this table, and
performing updates, deletes and inserts on the temporary table and at
the same time on the original table?

Aside from triggers that are now only fired on the original tables and
modify their content, but not the content of the temporary tables and
other ugly things, what about server performance when creating these
temporary tables? Am I correct to suspect that the server performance is
considerably worse if multiple large tables need to be created that
cannot be held in memory simultaneously than when using the same number
of CURSORs?

Is there another solution that does not use SELECT with LIMIT/OFFSET?

Why haven't more people had this problem - even the simplest web
application that breaks a large query over multiple pages with "previous
page" and "next page" should have this problem? Probably everyone uses a
simple SELECT and transfers all data to the application at once, at
least the PostgreSQL JDBC driver does this, though CURSOR support is
already specified in the java.sql.ResultSet interface. And people wonder
why applications perform badly even with a fast SQL server.

Regards,
Florian Wunderlich