Quantcast

libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

classic Classic list List threaded Threaded
35 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
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 -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Merlin Moncure-2
On Thu, Oct 6, 2011 at 1:02 PM, Alex Goncharov
<[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
,--- 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 -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Merlin Moncure-2
On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov
<[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Florian Pflug-2
On Oct6, 2011, at 22:38 , Merlin Moncure wrote:
> On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov
> <[hidden email]> 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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Merlin Moncure-2
On Thu, Oct 6, 2011 at 4:16 PM, Florian Pflug <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
In reply to this post by Alex Goncharov-4
,--- 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?)

-- Alex -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Andrew Dunstan


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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Florian Pflug-2
In reply to this post by Alex Goncharov-4
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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Bruce Momjian
In reply to this post by Alex Goncharov-4
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  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
In reply to this post by Florian Pflug-2
,--- 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

| 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.
 
  select
        t1.nn1, -- guaranteed: not null
        t1.ny1, -- nullable
        t2.nn2, -- guaranteed: not null
        t2.ny2  -- nullable
  from t1, t1;
 
| best regards,
| Florian Pflug

Thank you -- I appreciate the conversation!

-- Alex -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
In reply to this post by Bruce Momjian
,--- 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 (the product) development
     under somebody's coaching, to start with.  This topic or other.
     I just wouldn't know where to start myself.

-- Alex -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov
In reply to this post by Alex Goncharov-4
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 identify the non-nullable columns:

-- Alex -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Andres Freund
In reply to this post by Alex Goncharov-4
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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
In reply to this post by Alex Goncharov-4
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 identify the non-nullable columns:

-- Alex -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
In reply to this post by Andres Freund
,--- 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 -- [hidden email] --


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Andres Freund


Alex Goncharov <[hidden email]> 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 - need to understand why its needed.

Greetings, Andres



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
,--- You/[hidden email] (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 return a
    value that indicates whether the column allows NULL values.

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

-- Alex -- [hidden email] --

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Florian Pflug-2
In reply to this post by Alex Goncharov-4
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



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

Alex Goncharov-4
,--- 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 -- [hidden email] --


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
12
Loading...