On Wed, Feb 01, 2006 at 09:47:12AM +1100, James Harper wrote:
> > Be careful what you infer from such a scan: not finding any NULLs
> > doesn't necessarily mean a column isn't nullable, it just means the
> > result set didn't contain any NULLs.
>
> I understand that limitation, but haven't figured out if it matters in
> my situation. The only time it might is if the client wants to infer a
> schema as a result of a query, eg 'SELECT * FROM TableX WHERE 0 = 1'.
Even if such a query did return a "nullable" flag, plenty of other
metadata would be absent that might be just as interesting from a
schema-viewing standpoint (CHECK, PRIMARY KEY, etc.). A better way
to view the schema is to query the system catalogs or the Information
Schema.
> In the above example, does the database engine assign internally a
> 'nullability' flag? I guess it must do... because how would the
> following be evaluated:
>
> SELECT f1 + f2 AS f INTO TableY FROM TableX WHERE f1 < 30
>
> Would the column f in the created table be nullable or not?
>
> I guess I need to do some testing unless you know off the top of your
> head?
I'm not familiar enough with PostgreSQL internals to comment on
what's happening underneath, but I could tell you from experience
what the above query would do. But with a quick test you could
figure it out for yourself :-)
> Hmmm... so a select statement with result set of a million rows is going
> to stall for a while before the results are usefully available to the
> client, and is then going to use a significant amount of memory on the
> client...
Correct.
> Is this a limitation of libpq or of the underlying database engine?
The "Incremental results from libpq" thread from a few months ago
might answer your questions:
http://archives.postgresql.org/pgsql-interfaces/2005-11/msg00010.php
> Are there any alternative (but native - eg not ODBC) interfaces to
> postgresql?
What problem do you want the alternative to solve? If it's just
the libpq-fetches-all-rows problem then you could use a cursor.
--
Michael Fuhr
