Re: libpq questions

On Wed, Feb 01, 2006 at 09:47:12AM +1100, James Harper wrote:
> Now, given the current where clause, the client isn't going to see any
> NULL values, but a change in the where clause might suddenly make the
> calculated f field nullable. Maybe this doesn't matter and the client
> application should itself be aware of the database schema anyway...
> maybe I'm just worrying too much :)
>
> 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?

Umm, the database doesn't keep track of whether a column is nullable or
not. Nor would I expect it to. For example in the above case, just
because the column doesn't contain nulls, doesn't mean it shouldn't be
able to. The "NOT NULL" declaration is a constraint and PostgreSQL
won't add it for you.

> I guess I need to do some testing unless you know off the top of your
> head?

Is suppose this would be the different between SELECT * INTO and CREATE
TABLE ... LIKE.

> 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...
>
> Is this a limitation of libpq or of the underlying database engine?

If you use cursors you can get any rows whenever you want. By default
if you ask for all the rows you get all the rows...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.