Обсуждение: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

My understanding is that libpq does not allow one to find if a result
set column is nullable.

Is this right?

(I know how to get a table column nullability information from
pg_attribute.attnotnull, but when coding around the libpq API:
 * Is, OMG, ugly.
 * Doesn't cover the arbitrary SELECT statements.
)

Thanks,

-- Alex -- alex-goncharov@comcast.net --

On Thu, Oct 6, 2011 at 1:02 PM, Alex Goncharov
<alex-goncharov@comcast.net> wrote:
> My understanding is that libpq does not allow one to find if a result
> set column is nullable.
>
> Is this right?
>
> (I know how to get a table column nullability information from
> pg_attribute.attnotnull, but when coding around the libpq API:
>
>  * Is, OMG, ugly.
>
>  * Doesn't cover the arbitrary SELECT statements.

why aren't you using PQgetisnull()?

merlin

,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
| My understanding is that libpq does not allow one to find if a result
| set column is nullable.
,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
| why aren't you using PQgetisnull()?

This function is not about the nullability of a column but rather
about the value in a result set cell:
 PQgetisnull: Tests a field for a null value.      int PQgetisnull(const PGresult *res, int row_number, int
column_number);

Notice the 'row_number'.     

-- Alex -- alex-goncharov@comcast.net --

On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov
<alex-goncharov@comcast.net> wrote:
> ,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
> | My understanding is that libpq does not allow one to find if a result
> | set column is nullable.
> ,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
> | why aren't you using PQgetisnull()?
>
> This function is not about the nullability of a column but rather
> about the value in a result set cell:
>
>  PQgetisnull: Tests a field for a null value.
>
>     int PQgetisnull(const PGresult *res, int row_number, int column_number);
>
> Notice the 'row_number'.


right -- get it.  well, your question is doesn't make sense then --
any column can be transformed in ad hoc query, so it only makes sense
to test individual values post query..    btw, if you don't like
querying system catalogs, check out information_schema.columns.

merlin

On Oct6, 2011, at 22:38 , Merlin Moncure wrote:
> On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov
> <alex-goncharov@comcast.net> wrote:
>> ,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
>> | My understanding is that libpq does not allow one to find if a result
>> | set column is nullable.

>> ,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
>> | why aren't you using PQgetisnull()?
>>
>> This function is not about the nullability of a column but rather
>> about the value in a result set cell:
>>
>>  PQgetisnull: Tests a field for a null value.
>>
>>     int PQgetisnull(const PGresult *res, int row_number, int column_number);
>>
>> Notice the 'row_number'.
>
> right -- get it.  well, your question is doesn't make sense then --
> any column can be transformed in ad hoc query, so it only makes sense
> to test individual values post query..    btw, if you don't like
> querying system catalogs, check out information_schema.columns.

Sure, but there are still a lot of cases where the database could deduce
(quite easily) that a result column cannot be null. Other databases do
that - for example, I believe to remember that Microsoft SQL Server preserves
NOT NULL constraints if you do
 CREATE TABLE bar AS SELECT * from foo;

So the question makes perfect sense, and the answer is: No, postgres currently
doesn't support that, i.e. doesn't deduce the nullability of result columns,
not even in the simplest cases.

best regards,
Florian Pflug

On Thu, Oct 6, 2011 at 4:16 PM, Florian Pflug <fgp@phlo.org> wrote:
> Sure, but there are still a lot of cases where the database could deduce
> (quite easily) that a result column cannot be null. Other databases do
> that - for example, I believe to remember that Microsoft SQL Server preserves
> NOT NULL constraints if you do
>
>  CREATE TABLE bar AS SELECT * from foo;
>
> So the question makes perfect sense, and the answer is: No, postgres currently
> doesn't support that, i.e. doesn't deduce the nullability of result columns,
> not even in the simplest cases.

hm, good point.  not sure how it's useful though.  I suppose an
application could leverage that for validation purposes, but that's a
stretch I think.

merlin

,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
| My understanding is that libpq does not allow one to find if a result
| set column is nullable.
,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
| why aren't you using PQgetisnull()?
,--- I/Alex (Thu, 06 Oct 2011 16:22:28 -0400) ----*
| This function is not about the nullability of a column but rather
| about the value in a result set cell:
|      int PQgetisnull(const PGresult *res, int row_number, int column_number);
| Notice the 'row_number'.     
,--- Merlin Moncure (Thu, 6 Oct 2011 15:38:59 -0500) ----*
| right -- get it.  well, your question is doesn't make sense then --

What?..

* It makes complete logical sense to ask a question if a result set column may ever have a NULL cell.

* It can be done for a table using pg_attribute.attnotnull.

* It can be done, at the C API level, in a wide variety of other databases, including the two most often mentioned in
thisaudience: Oracle (through and OCI call) and MySQL (at least through ODBC.)
 

| any column can be transformed in ad hoc query, so it only makes sense
| to test individual values post query..

What query?

Look at the subject line: it mentioned PQdescribePrepared.

I execute PQprepare, and then PQdescribePrepared -- I never fetch the
data.  When the statement is described, plenty information can be
obtained about the columns -- but not its nullability (what I wanted
to be confirmed or denied -- for libpq API.)

| btw, if you don't like querying system catalogs, check out
| information_schema.columns.

Than was not my question, right?  (What difference is there between
using pg_X tables of information_schema?)

,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
| Sure, but there are still a lot of cases where the database could deduce
| (quite easily) that a result column cannot be null.

Right. Of course.  I can do it in 'psql'.

| Other databases do that - for example, I believe to remember that
| Microsoft SQL Server preserves NOT NULL constraints if you do
| 
|   CREATE TABLE bar AS SELECT * from foo;

I don't know a database where this would not be true.

| So the question makes perfect sense, and the answer is: No, postgres currently
| doesn't support that, i.e. doesn't deduce the nullability of result columns,
| not even in the simplest cases.

You are wrong: as in my original mail, use pg_attribute.attnotnull to
see why I say this.

,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
| hm, good point.  not sure how it's useful though.  I suppose an
| application could leverage that for validation purposes, but that's a
| stretch I think.
`--------------------------------------------------------*

Thanks for sharing your knowledge of applications.

(Look, I appreciate anybody's reply and readiness to help, but if you
have a limited expertise in the subject area, why bother replying?)

-- Alex -- alex-goncharov@comcast.net --

On 10/06/2011 06:02 PM, Alex Goncharov wrote:
>
> (Look, I appreciate anybody's reply and readiness to help, but if you
> have a limited expertise in the subject area, why bother replying?)
>
>

People are trying to help you. Please be a little less sensitive. 
Sneering at Merlin is not likely to win you friends. He's well known 
around here as being quite knowledgeable.

cheers

andrew

On Oct7, 2011, at 00:02 , Alex Goncharov wrote:
> ,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
> | Sure, but there are still a lot of cases where the database could deduce
> | (quite easily) that a result column cannot be null.
>
> Right. Of course.  I can do it in 'psql'.

For the result of an *arbitrary* query?

I think what you are missing is that there is *huge* difference between
tables (as created by CREATE TABLE) and result sets produced by SELECT
statements.

The former can carry all sorts of constraints like NOT NULL, CHECK,
REFERENCES, ..., and their structure as well as the constraints they carry
are stored in the catalog tables in the schema pg_catalog.

The latter cannot carry any constraints, and their meta-data thus consist
simply of a list of column names and types. Their meta-data is also
transient in nature, since it differs for every SELECT you issue.

Views are a kind of mixture between the two - their meta-data isn't any
richer than that of a SELECT statement, but since VIEWs aren't transient
objects like statements, their meta-data *is* reflected in the catalog.

> | Other databases do that - for example, I believe to remember that
> | Microsoft SQL Server preserves NOT NULL constraints if you do
> |
> |   CREATE TABLE bar AS SELECT * from foo;
>
> I don't know a database where this would not be true.

Ähm... postgres would be one where the resulting table doesn't have any
NOT NULL columns. Ever.

> | So the question makes perfect sense, and the answer is: No, postgres currently
> | doesn't support that, i.e. doesn't deduce the nullability of result columns,
> | not even in the simplest cases.
>
> You are wrong: as in my original mail, use pg_attribute.attnotnull to
> see why I say this.

Nope, you miss-understood what I said. I said "result columns", meaning the
columns resulting from a SELECT statement. Postgres doesn't deduce the nullability
of these columns. The fact that postgres supports NOT NULL constraints on tables
(which is what pg_attribute.attnotnull is for) really has nothing to do with that.

best regards,
Florian Pflug

Alex Goncharov wrote:
> ,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
> | hm, good point.  not sure how it's useful though.  I suppose an
> | application could leverage that for validation purposes, but that's a
> | stretch I think.
> `--------------------------------------------------------*
> 
> Thanks for sharing your knowledge of applications.
> 
> (Look, I appreciate anybody's reply and readiness to help, but if you
> have a limited expertise in the subject area, why bother replying?)

FYI, I see 867 Postgres posts mentioning Merlin Moncure in the past
year:
http://search.postgresql.org/search?q=Merlin+Moncure&m=1&l=NULL&d=365&s=r&p=44

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

,--- You/Florian (Fri, 7 Oct 2011 01:00:40 +0200) ----*
| On Oct7, 2011, at 00:02 , Alex Goncharov wrote:
| > ,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
| > | Sure, but there are still a lot of cases where the database could deduce
| > | (quite easily) that a result column cannot be null.
| > 
| > Right. Of course.  I can do it in 'psql'.
| 
| For the result of an *arbitrary* query?

In 'psql', no: I was commenting specifically, and confirming what you
said, on your
a lot of cases where the database could deduce (quite easily) that aresult column cannot be null

| I think what you are missing is that there is *huge* difference between
| tables (as created by CREATE TABLE) and result sets produced by SELECT
| statements.

Actually, no, I am not missing the huge difference -- again, I was
just agreeing with you.  Agreeing that there is a lot of cases where
the nullability can be trivially deduced, even in 'psql'. (That also
meant disagreeing with the message posted before yours.)
| The former can carry all sorts of constraints like NOT NULL, CHECK,
| REFERENCES, ..., and their structure as well as the constraints they carry
| are stored in the catalog tables in the schema pg_catalog.

Yes.

| The latter cannot carry any constraints, and their meta-data thus consist
| simply of a list of column names and types. Their meta-data is also
| transient in nature, since it differs for every SELECT you issue.

Right: but for (most?) every SELECT, one can logically deduce whether
it can be guaranteed that a given column will never have a NULL value.
Since in a given SELECT, the result column are a combination of either
other columns, or expressions, including literals.

Now, I am not even wondering about a 100% percent reliable
determination by a hypothetical 'PQfisnullable(PQresult *r, int idx)'.

But if libpq can tell me about column names, types and sizes (PQfname,
PQftype, PQfmod), why would it be impossible to have 'PQfisnullable'?

Today I tested that it is done in: Oracle, DB2, MySQL, Teradata,
Informix, Netezza and Vertica (in many of these via ODBC.)

This is conceptually feasible.

And in PostgreSQL, this could be done by combining
 (1)   Oid PQftable(const PGresult *res, int column_number); (2)   int PQftablecol(const PGresult *res, int
column_number);(3)   a SQL query of pg_attribute,attnotnull
 

I have not tried this yet, hesitating to walk into a monstrosity and
hoping that there is some hidden way to get the information through
one of
 int PQfmod(const PGresult *res, int column_number); int PQgetisnull(const PGresult *res, int row_number, int
column_number);

(the latter with an odd 'row_number'; I actually tried row_number= 0
and -1, after preparing a statement. No luck.)  

| Views are a kind of mixture between the two - their meta-data isn't any
| richer than that of a SELECT statement, but since VIEWs aren't transient
| objects like statements, their meta-data *is* reflected in the
| catalog.

Again, combining (1), (2) and (3) above should give a good answer here.

| > | Other databases do that - for example, I believe to remember that
| > | Microsoft SQL Server preserves NOT NULL constraints if you do
| > | 
| > |   CREATE TABLE bar AS SELECT * from foo;
| > 
| > I don't know a database where this would not be true.
| 
| Ähm... postgres would be one where the resulting table doesn't have any
| NOT NULL columns. Ever.

Not sure what you mean here:

--------------------------------------------------
http://www.postgresql.org/docs/8.4/interactive/ddl-constraints.html#AEN2290:

A not-null constraint simply specifies that a column must not assume
the null value. 

CREATE TABLE products (   product_no integer NOT NULL,   name text NOT NULL,   price numeric
);

The NOT NULL constraint has an inverse: the NULL constraint.

CREATE TABLE products (   product_no integer NULL,   name text NULL,   price numeric NULL
);
--------------------------------------------------

| 
| > | So the question makes perfect sense, and the answer is: No, postgres currently
| > | doesn't support that, i.e. doesn't deduce the nullability of result columns,
| > | not even in the simplest cases.
| > 
| > You are wrong: as in my original mail, use pg_attribute.attnotnull to
| > see why I say this.
| 
| Nope, you miss-understood what I said.

You said, "not even in the simplest cases" -- and this is what caused
my statement.

| I said "result columns", meaning the columns resulting from a SELECT
| statement.

Then I misunderstood you, indeed -- I thought you included an inquiry
about a table.  Sorry for the misunderstanding then.

| Postgres doesn't deduce the nullability of these columns. The fact
| that postgres supports NOT NULL constraints on tables (which is what
| pg_attribute.attnotnull is for) really has nothing to do with that.
 create table t1(nn1 char(1) not null, yn1 char(1) null); create table t2(nn2 char(1) not null, yn2 char(1) null);
 (may use pg_attribute.attnotnull on t1, t2, is I didn't see the 'create's.
 Now, for this statement, I can easily identify non-nullable columns.  selectt1.nn1, -- guaranteed: not nullt1.ny1, --
nullablet2.nn2,-- guaranteed: not nullt2.ny2  -- nullable from t1, t1;    
 
| best regards,
| Florian Pflug

Thank you -- I appreciate the conversation!

-- Alex -- alex-goncharov@comcast.net --

,--- You/Bruce (Thu, 6 Oct 2011 19:09:16 -0400 (EDT)) ----*
| > (Look, I appreciate anybody's reply and readiness to help, but if you
| > have a limited expertise in the subject area, why bother replying?)
| 
| FYI, I see 867 Postgres posts mentioning Merlin Moncure in the past
| year:
| 
|     http://search.postgresql.org/search?q=Merlin+Moncure&m=1&l=NULL&d=365&s=r&p=44

I watch most of the PostgreSQL technical lists all the time and know
who is who.

I didn't mean to be disparaging (and said, "Look, I appreciate
anybody's reply and readiness to help").

But really, before replying, one should think about the posted
question, and resist opinionating on the topics little thought about
and worked with.

To this:

,--- Merlin Moncure (Thu, 6 Oct 2011 15:16:18 -0500) ----*
| why aren't you using PQgetisnull()?
`--------------------------------------------------------*

I replied politely:

,--- I/Alex (Thu, 06 Oct 2011 16:22:28 -0400) ----*
| This function is not about the nullability of a column but rather
| about the value in a result set cell:
| 
|   PQgetisnull: Tests a field for a null value. 
|   
|      int PQgetisnull(const PGresult *res, int row_number, int column_number);
| 
| Notice the 'row_number'.     
`-------------------------------------------------*

To this:

,--- Merlin Moncure (Thu, 6 Oct 2011 15:38:59 -0500) ----*
| right -- get it.  well, your question is doesn't make sense then --
|
| btw, if you don't like querying system catalogs, check out
| information_schema.columns.
|
`--------------------------------------------------------*

it was harder; still, I stayed in the technical area:

,--- I/Alex (Thu, 06 Oct 2011 18:02:41 -0400) ----*
|
| What?..
| 
| * It makes complete logical sense to ask a question if a result set
|   column may ever have a NULL cell.
| 
| * It can be done for a table using pg_attribute.attnotnull.
| 
| * It can be done, at the C API level, in a wide variety of other
|   databases, including the two most often mentioned in this audience:
|   Oracle (through and OCI call) and MySQL (at least through ODBC.)
|
`-------------------------------------------------*

To this:

,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
| hm, good point.  not sure how it's useful though.  I suppose an
| application could leverage that for validation purposes, but that's a
| stretch I think.
`--------------------------------------------------------*

it was plain hard -- the expressed opinion didn't relate to the
original question, and was, besides, quite unfounded.

,--- Andrew Dunstan (Thu, 06 Oct 2011 18:30:44 -0400) ----*
| People are trying to help you. Please be a little less sensitive. 
| Sneering at Merlin is not likely to win you friends. 
`---------------------------------------------------------*

I know.

I wouldn't have been sensitive about an opinion on a side topic ("not
sure how it's useful though") (did anybody asked about that?), had
Merlin also offered sound and relevant technical points.  He hadn't.

On the technical point now:

It's clear enough for me at this point, that I had not overlooked
anything in libpq and it doesn't support finding a result set column
nullability (no hypothetical PQfisnullable function or a hidden way to
use other PQf* functions for this purpose.)

I will resort to the ugly method I outlined in my previous message,
combining:

,--- I/Alex (Thu, 06 Oct 2011 19:42:13 -0400) ----*
|
|   (1)   Oid PQftable(const PGresult *res, int column_number);
|   (2)   int PQftablecol(const PGresult *res, int column_number);
|   (3)   a SQL query of pg_attribute,attnotnull
|
`-------------------------------------------------*

Thanks everybody who replied!

P.S. And on the odd chance that somebody thinks that this    functionality would be possible and helpful to add to
libpq,and    the problem is in the lack of human resources: I would be more    then happy to dig into some PostgreSQL
(theproduct) development    under somebody's coaching, to start with.  This topic or other.    I just wouldn't know
whereto start myself.
 

-- Alex -- alex-goncharov@comcast.net --

On Friday, October 07, 2011 01:42:13 AM Alex Goncharov wrote:
> ,--- You/Florian (Fri, 7 Oct 2011 01:00:40 +0200) ----*
> 
> | On Oct7, 2011, at 00:02 , Alex Goncharov wrote:
> | > ,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
> | > 
> | > | Sure, but there are still a lot of cases where the database could
> | > | deduce (quite easily) that a result column cannot be null.
> | >
> | > 
> | >
> | > Right. Of course.  I can do it in 'psql'.
> |
> | 
> |
> | For the result of an arbitrary query?
> 
> In 'psql', no: I was commenting specifically, and confirming what you
> said, on your
> 
>  a lot of cases where the database could deduce (quite easily) that a
>  result column cannot be null
Could you quickly explain what exactly you want that information for? Just 
because it has been done before doesn't necessarily mean its a good idea...


Thanks,

Andres

The obvious typos (sorry if this is a duplicate message, I sent the
first one from a wrong address):

,--- I/Alex (Thu, 06 Oct 2011 19:42:13 -0400) ----*
|   (may use pg_attribute.attnotnull on t1, t2, is I didn't see the 'create's.   (may use pg_attribute.attnotnull on
t1,t2, if I didn't see the 'create's.
 
|   Now, for this statement, I can easily identify non-nullable columns.   Now, for this statement, I can easily
identifythe non-nullable columns:
 

-- Alex -- alex-goncharov@comcast.net --

,--- You/Andres (Fri, 7 Oct 2011 02:28:30 +0200) ----*
| >  a lot of cases where the database could deduce (quite easily) that a
| >  result column cannot be null
| Could you quickly explain what exactly you want that information for? Just 
| because it has been done before doesn't necessarily mean its a good idea...

I am not writing a database application here (i.e. I am not storing
the data).  I am responding to a client requirement, basically:
 Given a SELECT (or possibly, simpler, a table name), tell me which columns are non-nullable?

I can give the answer about the tables trivially in 'psql' (using
pg_attribute.attnotnull).  But it has to be done inside the C code I
wrote a couple of years ago, already using libpq, preparing and
describing arbitrary statements...  If I could get the required
information through some use of PQ* functions...

But, oh well, I'll "PQexec(a-fancy-select-from-pg_attribute)".

Ugly :(

-- Alex -- alex-goncharov@comcast.net --

Alex Goncharov <alex-goncharov@comcast.net> schrieb:

>,--- You/Andres (Fri, 7 Oct 2011 02:28:30 +0200) ----*
>| >  a lot of cases where the database could deduce (quite easily) that
>a
>| >  result column cannot be null
>| Could you quickly explain what exactly you want that information for?
>Just 
>| because it has been done before doesn't necessarily mean its a good
>idea...
>
>I am not writing a database application here (i.e. I am not storing
>the data).  I am responding to a client requirement, basically:
>
>  Given a SELECT (or possibly, simpler, a table name), tell me which
>  columns are non-nullable?
That doesnt explain why it's  needed. To get community buyin into a feature the community - or at least parts of it -
needto understand why its needed.
 

Greetings, Andres

,--- You/anarazel@anarazel.de (Fri, 07 Oct 2011 02:54:39 +0200) ----*
|
| >  Given a SELECT (or possibly, simpler, a table name), tell me which
| >  columns are non-nullable?
| That doesnt explain why it's  needed.

It's  needed for some meta analysis. That's as much as I can say.

| To get community buyin into a feature the community - or at least
| parts of it - need to understand why its needed.

Take a look at these APIs:
 http://download.oracle.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#isNullable(int)    int isNullable(int
column)throws SQLException   Indicates the nullability of values in the designated column.
 
 http://msdn.microsoft.com/en-us/library/ms716289(v=VS.85).aspx    NullablePtr [Output] Pointer to a buffer in which to
returna   value that indicates whether the column allows NULL values.
 

A common and natural question to be answered about result sets.

-- Alex -- alex-goncharov@comcast.net --

On Oct7, 2011, at 01:42 , Alex Goncharov wrote:
> Right: but for (most?) every SELECT, one can logically deduce whether
> it can be guaranteed that a given column will never have a NULL value.
> Since in a given SELECT, the result column are a combination of either
> other columns, or expressions, including literals.

Sure. Deducing nullability isn't a hard problem, at least not if it's
OK to simply say "nullable" if things get too complex.

> And in PostgreSQL, this could be done by combining
>
>  (1)   Oid PQftable(const PGresult *res, int column_number);
>  (2)   int PQftablecol(const PGresult *res, int column_number);
>  (3)   a SQL query of pg_attribute,attnotnull

That won't work. I'm pretty sure that you'll get the wrong answer
for queries involving OUTER joins, e.g.
 SELECT * FROM foo LEFT JOIN bar ON bar.foo_id = foo.foo_id

> I have not tried this yet, hesitating to walk into a monstrosity and
> hoping that there is some hidden way to get the information through
> one of
>
>  int PQfmod(const PGresult *res, int column_number);
>  int PQgetisnull(const PGresult *res, int row_number, int column_number);

Let me assure you that there's no "hidden way". The feature is simply
unsupported.

> Now, for this statement, I can easily identify non-nullable columns.
>
>  select
>     t1.nn1, -- guaranteed: not null
>     t1.ny1, -- nullable
>     t2.nn2, -- guaranteed: not null
>     t2.ny2  -- nullable
>  from t1, t1;

Sure. So can I. But postgres can't, since nobody's implemented the necessary
algorithm so far. You're very welcome to produce a patch, though. Should you
decide to do that, I recommend that you discuss the design of this *before*
starting work (in a separate thread). Otherwise, you might discover objections
to the general approach, or even to the whole feature, only after you put
considerable effort into this.

best regards,
Florian Pflug

,--- You/Florian (Fri, 7 Oct 2011 03:21:23 +0200) ----*
| Sure. Deducing nullability isn't a hard problem, at least not if it's
| OK to simply say "nullable" if things get too complex.

Yes.

| > And in PostgreSQL, this could be done by combining
| > 
| >  (1)   Oid PQftable(const PGresult *res, int column_number);
| >  (2)   int PQftablecol(const PGresult *res, int column_number);
| >  (3)   a SQL query of pg_attribute,attnotnull
| 
| That won't work. I'm pretty sure that you'll get the wrong answer
| for queries involving OUTER joins, e.g.
| 
|   SELECT * FROM foo LEFT JOIN bar ON bar.foo_id = foo.foo_id

That's a good point.  But I'll do with what I manage to get.  I am
pretty sure that in my client's use, this is not going to be an issue.

And OTOH, I am not sure that other databases will give me a good
answer.  I'll play with them soon, out of technical curiosity.

| > I have not tried this yet, hesitating to walk into a monstrosity and
| > hoping that there is some hidden way to get the information through
| > one of
| > 
| >  int PQfmod(const PGresult *res, int column_number);
| >  int PQgetisnull(const PGresult *res, int row_number, int column_number);
| 
| Let me assure you that there's no "hidden way". The feature is simply
| unsupported.

Oh, great -- that's the second best answer I hoped for: just didn't
want to go down the expensive and not fool-proof way by mistake.  Had
to ask this list.

| > Now, for this statement, I can easily identify non-nullable columns.
| > 
| >  select
| >     t1.nn1, -- guaranteed: not null
| >     t1.ny1, -- nullable
| >     t2.nn2, -- guaranteed: not null
| >     t2.ny2  -- nullable
| >  from t1, t1;    
| 
| Sure. So can I. But postgres can't, since nobody's implemented the necessary
| algorithm so far. You're very welcome to produce a patch, though.

I've looked into the 'src/interfaces/libpq' and other parts of 'src'
more than once and suspect that I won't be able to find where to plug
this in correctly, even if I figure out a meaningful algorithm.

| Should you decide to do that,

Unlikely: in a couple of days I hope to have my implementation as I
described before, then there will be no need for our application to
wait for the desired PQfnullable function.  Besides, our application
has to work with any libpq.so.5, so no new PQ* function can be called.

I'd only venture to do it for the personal goal of contributing to
PostgreSQL.  Who knows, but unlikely -- a too high barrier to entry.

| I recommend that you discuss the design of this *before* starting
| work (in a separate thread). Otherwise, you might discover
| objections to the general approach, or even to the whole feature,
| only after you put considerable effort into this.
| 
| best regards,
| Florian Pflug

Thank you: this is all very valuable,

-- Alex -- alex-goncharov@comcast.net --

On tor, 2011-10-06 at 20:15 -0400, Alex Goncharov wrote:
> P.S. And on the odd chance that somebody thinks that this
>      functionality would be possible and helpful to add to libpq, and
>      the problem is in the lack of human resources: I would be more
>      then happy to dig into some PostgreSQL (the product) development
>      under somebody's coaching, to start with.  This topic or other.
>      I just wouldn't know where to start myself. 

I had some some research on this particular topic/feature recently.  My
notes currently say, it's better to not tackle this before the not-null
cataloging patch (see entry in current commitfest) is finished.  Because
that patch would presumably already implement much of the logic
necessary to determine whether a give expression implies nullability or
not and catalog this in a simpler fashion.  Based on that you will then
have to drag this information around and put it on the wire so that the
client APIs can process it.

On Thu, Oct 6, 2011 at 5:02 PM, Alex Goncharov
<alex-goncharov@comcast.net> wrote:
> ,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
> | My understanding is that libpq does not allow one to find if a result
> | set column is nullable.
> ,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
> | why aren't you using PQgetisnull()?
> ,--- I/Alex (Thu, 06 Oct 2011 16:22:28 -0400) ----*
> | This function is not about the nullability of a column but rather
> | about the value in a result set cell:
> |      int PQgetisnull(const PGresult *res, int row_number, int column_number);
> | Notice the 'row_number'.
> ,--- Merlin Moncure (Thu, 6 Oct 2011 15:38:59 -0500) ----*
> | right -- get it.  well, your question is doesn't make sense then --
>
> What?..
>
> * It makes complete logical sense to ask a question if a result set
>  column may ever have a NULL cell.
>
> * It can be done for a table using pg_attribute.attnotnull.
>
> * It can be done, at the C API level, in a wide variety of other
>  databases, including the two most often mentioned in this audience:
>  Oracle (through and OCI call) and MySQL (at least through ODBC.)
>
> | any column can be transformed in ad hoc query, so it only makes sense
> | to test individual values post query..
>
> What query?
>
> Look at the subject line: it mentioned PQdescribePrepared.
>
> I execute PQprepare, and then PQdescribePrepared -- I never fetch the
> data.  When the statement is described, plenty information can be
> obtained about the columns -- but not its nullability (what I wanted
> to be confirmed or denied -- for libpq API.)
>
> | btw, if you don't like querying system catalogs, check out
> | information_schema.columns.
>
> Than was not my question, right?  (What difference is there between
> using pg_X tables of information_schema?)
>
> ,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
> | Sure, but there are still a lot of cases where the database could deduce
> | (quite easily) that a result column cannot be null.
>
> Right. Of course.  I can do it in 'psql'.
>
> | Other databases do that - for example, I believe to remember that
> | Microsoft SQL Server preserves NOT NULL constraints if you do
> |
> |   CREATE TABLE bar AS SELECT * from foo;
>
> I don't know a database where this would not be true.
>
> | So the question makes perfect sense, and the answer is: No, postgres currently
> | doesn't support that, i.e. doesn't deduce the nullability of result columns,
> | not even in the simplest cases.
>
> You are wrong: as in my original mail, use pg_attribute.attnotnull to
> see why I say this.
>
> ,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
> | hm, good point.  not sure how it's useful though.  I suppose an
> | application could leverage that for validation purposes, but that's a
> | stretch I think.
> `--------------------------------------------------------*
>
> Thanks for sharing your knowledge of applications.
>
> (Look, I appreciate anybody's reply and readiness to help, but if you
> have a limited expertise in the subject area, why bother replying?)


Well, admittedly, perhaps my response was hastily written.  But try to
understand the zen of things around here: often if you
propose/gripe/suggest something, you'll get a challenge back which is
really fishing for more detail.  It's not personal.  By the way, you
still haven't explained use cases.  You can always talk
hypotheticals...'other people do it' is not a standard for inclusion
of a feature (although it can be).  I've been coding against libpq for
years and years and have never needed to test for nullability, so
that's where my skepticism comes from.

merlin

,--- Peter Eisentraut (Fri, 07 Oct 2011 11:14:09 +0300) ----*
| On tor, 2011-10-06 at 20:15 -0400, Alex Goncharov wrote:
| > P.S. And on the odd chance that somebody thinks that this
| >      functionality would be possible and helpful to add to libpq, and
| >      the problem is in the lack of human resources: I would be more
| >      then happy to dig into some PostgreSQL (the product) development
| >      under somebody's coaching, to start with.  This topic or other.
| >      I just wouldn't know where to start myself. 
| 
| I had some some research on this particular topic/feature recently.  My
| notes currently say, it's better to not tackle this before the not-null
| cataloging patch (see entry in current commitfest) is finished.

Peter,

Thank you -- this is very helpful: I was not aware of a commitfest
list.  Will try to check it out within a few days.

| Because that patch would presumably already implement much of the
| logic necessary to determine whether a give expression implies
| nullability or not and catalog this in a simpler fashion.  Based on
| that you will then have to drag this information around and put it
| on the wire so that the client APIs can process it.
`-----------------------------------------------------------*

Good to hear that I am not alone in the expressed wish. Thank you
again for all the technical details!

-- Alex -- alex-goncharov@comcast.net --

,--- You/Merlin (Fri, 7 Oct 2011 07:39:57 -0500) ----*
| On Thu, Oct 6, 2011 at 5:02 PM, Alex Goncharov
| > ,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
| > | hm, good point.  not sure how it's useful though.  I suppose an
| > | application could leverage that for validation purposes, but that's a
| > | stretch I think.
| > `--------------------------------------------------------*
| >
| > Thanks for sharing your knowledge of applications.
| >
| > (Look, I appreciate anybody's reply and readiness to help, but if you
| > have a limited expertise in the subject area, why bother replying?)
| Well, admittedly, perhaps my response was hastily written.  But try
| to understand the zen of things around here: often if you
| propose/gripe/suggest something, you'll get a challenge back which
| is really fishing for more detail.  It's not personal.

Merlin,

I appreciate the spirit of the PostgreSQL technical lists: I am
permanently subscribed to PERFORM, and, occasionally, to HACKERS.  I
regularly unsubscribe from the latter because it quickly overloads me
with the flood of messages I have no time even to read, not to say,
digest.  HACKERS would be one of the most useful technical reads, if
it were not so bloody floody.
 (On GENERAL, take a look at this reply to a question similar to mine:
   http://archives.postgresql.org/pgsql-general/2005-08/msg01152.php
 What's the value of this kind of advice?)

| By the way, you still haven't explained use cases.

As I said yesterday, it is for my client to find various meta data.

Also note that I posted the references to common APIs (JDBC and ODBC),
where this interface is available, because "nullability" is a natural
thing to ask about.  You can also find how this kind of functionality
is supported, e.g. in Oracle OCI.

Plus, now you have seen, from Peter Eisentraut's message that I just
replied to, and from the mail archive link I posted a dozen of lines
above here, that I am not the first person interested in this kind of
functionality in the PostgreSQL land.

| You can always talk hypotheticals...'other people do it' is not a
| standard for inclusion of a feature (although it can be).

I didn't ask anybody to include anything in PostgreSQL; my question,
now unambiguously answered (thank you, the list!) was:

,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
|
| My understanding is that libpq does not allow one to find if a result
| set column is nullable.
| 
| Is this right?
|
`-------------------------------------------------*

Compare this with what you have tried to write about.

| I've been coding against libpq for years and years and have never
| needed to test for nullability,

It's not a serious argument, in my opinion.

| so that's where my skepticism comes from.
`-------------------------------------------------*

But, sincerely, I do appreciate your readiness to help and continuing
the conversation this morning.

Thank you,

-- Alex -- alex-goncharov@comcast.net --

The obvious typos:

,--- I/Alex (Thu, 06 Oct 2011 19:42:13 -0400) ----*
|   (may use pg_attribute.attnotnull on t1, t2, is I didn't see the 'create's.   (may use pg_attribute.attnotnull on
t1,t2, if I didn't see the 'create's.
 
|   Now, for this statement, I can easily identify non-nullable columns.   Now, for this statement, I can easily
identifythe non-nullable columns:
 

-- Alex -- goncharov.alex@gmail.com --

<p>I'll point to rather different reasoning...<p>Libpq is not returning tables, or relations, for that matter, but
ratherthe results of queries.<p>It is reasonable to expect to know which attributes of a table are or are not nullable,
andthat is commonly available as an attribute of pg_attribute, however...<p>General purpose queries are nowhere near so
predetermined. Indeed, whether a column is nullable may not be at all visible, as the value of a column may be computed
bya function and thereby be quite opaque to static analysis.<p>That makes me think that guessing which attributes of a
querymay be null seems like a pretty futile exercise.  At first blush, we could simplify to PQnullable() always
returningtrue, but that's not terribly revealing.  However, often, there mayn't be a much better solution that isn't
reallytough to implement.<p>I'd not be keen on people putting much effort into futile exercises ; better to work on
thingsthat are "less futile." 

On Oct8, 2011, at 23:07 , Christopher Browne wrote:
> General purpose queries are nowhere near so predetermined.  Indeed, whether a column is nullable may not be at all
visible,as the value of a column may be computed by a function and thereby be quite opaque to static analysis. 

I don't agree. To me, nullability is part of a column's type, just as the type's OID and TYPMOD are. We do static
analysison the TYPMOD, so I don't see why we shouldn't or couldn't do that on nullability. 

> That makes me think that guessing which attributes of a query may be null seems like a pretty futile exercise.  At
firstblush, we could simplify to PQnullable() always returning true, but that's not terribly revealing.  However,
often,there mayn't be a much better solution that isn't really tough to implement. 

Coming up with a reasonable algorithm isn't *that* hard. Here's what I think would be reasonable
 A) All result columns which are not simple column references are nullable B) All result columns which are simple
referencesto nullable columns are nullable C) All result columns which are simple references to column from the
nullableside of an outer join are nullable    (i.e., columns from the "right" side of a LEFT JOIN, "left" side of a
RIGHTJOIN, or any side of a FULL OUTER JOIN) D) All others are nullable    (i.e. simple column references to
non-nullablecolumns from the non-nullable side of a join) 

If someone cared enough, (A) could be improved upon further. CASE constructs are an obvious candidate for deeper
inspection(i.e., a CASE construct is non-nullable if all WHEN branches are non-nullable and a non-nullalbe ELSE branch
exists),as is COALESCE (similar rule). 

This is mostly how it works for typmod I think - we do some analysis, but at some point we give up and just return
"-1".

As I see it, the hardest part of this feature is getting the information to the client. I don't think the reply to a
DESCRIBEmessage is currently extensible, so we'd probably need to add a new version of the message. That might be a
rathertough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has
providedso far. 

> I'd not be keen on people putting much effort into futile exercises ; better to work on things that are "less
futile."

Again, I think "futile" is the wrong word here. This is all perfectly doable, the question is simply whether one values
tofeature enough to put in the word. I certainly won't, because I don't really see the benefit. But since most of our
competitorsseem to support this, and since Sun even put this into the JDBC spec, I guess a whole lot of people
disagree.

best regards,
Florian Pflug