Re: Queries run twice

erne_dev @ Bellsouth.net wrote:
> Running version 8.0 server under WinXP, client is Builder/ADO application
> connecting through ODBC. Found a reference to exactly the same problem on
> this msg http://archives.postgresql.org/pgsql-odbc/2005-02/msg00033.php. Is
> this a PostgreSQL bug? Or is it ODBC driver?

Well, if the reply to that message is right, I think it's an unfortunate
interaction between ADO/PostgreSQL.

> My application also works with
> MySQL and this doesn't happen when connecting to a MySQL server so I don't
> think is has to do with the ADO components.

Could you turn query-logging on in MySQL and just check? That would help
to pin things down precisely.

> Like the poster of that message
> my query gets executed twice, one as I specified the second time without the
> WHERE clause, so the whole Table is returned causing a lot of network
> traffic and slowing things down...What is the 'parse statement' mentioned on
> that message? Is that a PostgreSQL setting?

There is a "parse statements" option in the ODBC driver's settings
http://gborg.postgresql.org/project/psqlodbc/genpage.php?doc-config

What I think is happening, is that for some reason the ADO code wants to
know the types of the columns (even though it presumably already has
this information in the first query). So - it issues the query without
the WHERE and then doesn't actually fetch any rows, just checks what
types are returned.

Now, one difference between PostgreSQL and many other databases is that
if you ask for 1 million rows, we return all those straight away. Many
others keep the results server-side and only pass them to the client
when asked for.

Why return them all at once?
1. It frees up resources server-side for other clients
2. If the client asked for 1 million rows, then it presumably wants
all of them, otherwise it would have said so.

The problem occurs when you have application-generated code which is
thinking in more of a dbf/file oriented way. It should really do "SELECT
... LIMIT 0"

Anyway, that's only speculation - if you turn on MySQL statement logging
and it still shows the second query, that's the problem. If it doesn't
that suggests it's something in the ODBC driver.

--
Richard Huxton
Archonet Ltd