Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-10 Thread Peter Eisentraut
On sön, 2011-10-09 at 11:51 -0400, Tom Lane wrote: The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. Why do we have major and minor protocol version numbers,

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-10 Thread Kevin Grittner
Florian Pflug wrote: On Oct9, 2011, at 14:20 , Kevin Grittner wrote: Florian Pflug wrote: Coming up with a reasonable algorithm isn't *that* hard. Agreed. Our shop has used a home-grown framework for over a decade where we parse queries using ANTLR ( http://www.antlr.org/ ) and we

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-10 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On sön, 2011-10-09 at 11:51 -0400, Tom Lane wrote: The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. Why do we have

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Florian Pflug
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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Kevin Grittner
Florian Pflug wrote: Coming up with a reasonable algorithm isn't *that* hard. Agreed. Our shop has used a home-grown framework for over a decade where we parse queries using ANTLR ( http://www.antlr.org/ ) and we tracked this trough all expressions. There really weren't that many

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Florian Pflug
On Oct9, 2011, at 14:20 , Kevin Grittner wrote: Florian Pflug wrote: Coming up with a reasonable algorithm isn't *that* hard. Agreed. Our shop has used a home-grown framework for over a decade where we parse queries using ANTLR ( http://www.antlr.org/ ) and we tracked this trough all

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Florian Pflug wrote: I don't think the reply to a DESCRIBE message is currently extensible, so we'd probably need to add a new version of the message. Or a new protocol version. Exactly --- this *would* require a protocol version bump.

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Tom Lane
Florian Pflug f...@phlo.org writes: On Oct9, 2011, at 14:20 , Kevin Grittner wrote: Yeah. It would be nice to see at least one use case. The only comment I recall is a vague suggestion that that people might want to select data from a table and infer table attributes from the result set

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Florian Pflug
On Oct9, 2011, at 17:56 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Oct9, 2011, at 14:20 , Kevin Grittner wrote: Yeah. It would be nice to see at least one use case. The only comment I recall is a vague suggestion that that people might want to select data from a table and

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-09 Thread Magnus Hagander
On Sun, Oct 9, 2011 at 17:51, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Yeah, it wouldn't be hard to produce a long list of things which would take about the same effort which seem more beneficial to me. It's a matter of whether this is causing

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-08 Thread Alex Goncharov
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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-08 Thread Christopher Browne
I'll point to rather different reasoning... Libpq is not returning tables, or relations, for that matter, but rather the results of queries. It is reasonable to expect to know which attributes of a table are or are not nullable, and that is commonly available as an attribute of pg_attribute,

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-07 Thread Peter Eisentraut
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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-07 Thread Merlin Moncure
On Thu, Oct 6, 2011 at 5:02 PM, Alex Goncharov alex-goncha...@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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-07 Thread Alex Goncharov
,--- 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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-07 Thread Alex Goncharov
,--- 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,

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Merlin Moncure
On Thu, Oct 6, 2011 at 1:02 PM, Alex Goncharov alex-goncha...@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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- 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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Merlin Moncure
On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov alex-goncha...@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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Florian Pflug
On Oct6, 2011, at 22:38 , Merlin Moncure wrote: On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov alex-goncha...@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. ,---

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Merlin Moncure
On Thu, Oct 6, 2011 at 4:16 PM, Florian Pflug f...@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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- 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) * |

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread 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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Florian Pflug
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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Bruce Momjian
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.

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- 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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- 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: | |

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Andres Freund
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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- 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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread anara...@anarazel.de
Alex Goncharov alex-goncha...@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 |

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- You/anara...@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

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Florian Pflug
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,

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-06 Thread Alex Goncharov
,--- 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