Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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, which are supposed to allow incremental addition of features to the protocol? What other costs do you have in mind? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 tracked this trough all expressions. There really weren't that many situations where we had to punt. Sounds cool. What was your use-case for doing that? Portability. That approach is what made the conversion to PostgreSQL from the commercial product we were using quick and painless. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 major and minor protocol version numbers, which are supposed to allow incremental addition of features to the protocol? Well, that's a good question. I seem to recall that the last time it was discussed, questions were raised about whether a minor-number version bump would really work as desired. In particular, if the client connects asking for 3.1 and the server doesn't know anything later than 3.0, you end up having to do another connection cycle, which is rather inefficient and has got unpleasant failure cases too. This could be avoided if there were a way to have the server allow the connection but only at 3.0 level, but (1) there is no way to report that in 3.0 protocol, and (2) requiring clients to support 3.0 as well as 3.1 could be burdensome. Basically, it's uncharted territory, because we've never actually done it before. It wouldn't be a bad idea to put make sure upgrading to a 4.1 protocol version will actually work smoothly into our list of goals for protocol 4.0 ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 analysis on 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 first blush, 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 references to nullable columns are nullable C) All result columns which are simple references to column from the nullable side of an outer join are nullable (i.e., columns from the right side of a LEFT JOIN, left side of a RIGHT JOIN, or any side of a FULL OUTER JOIN) D) All others are nullable (i.e. simple column references to non-nullable columns 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 DESCRIBE message is currently extensible, so we'd probably need to add a new version of the message. That might be a rather tough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has provided so 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 to feature enough to put in the word. I certainly won't, because I don't really see the benefit. But since most of our competitors seem 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 situations where we had to punt. D) All others are nullable I think you meant All others are not nullable. As I see it, the hardest part of this feature is getting the information to the client. Ay, there's the rub. 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. I've been thinking that the next *big* project I look at here might be a new version of the protocol, since I see mentions of protocol limitations preventing things people want with some regularity. We should be keeping a list, and this should be on it. That might be a rather tough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has provided so far. 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 metadata. That seems marginal. the question is simply whether one values to feature enough to put in the word. ... or fund the work. There are people for hire in the community. I certainly won't, because I don't really see the benefit. 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 someone enough bother to want to devote the resources to changing it. I really think it's that simple. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 expressions. There really weren't that many situations where we had to punt. Sounds cool. What was your use-case for doing that? D) All others are nullable I think you meant All others are not nullable. Ups, yeah, right, that was supposed to read *non*-nullable. That might be a rather tough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has provided so far. 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 metadata. That seems marginal. Well, there is one other, namely SQL standards compliance. It does mandate that CREATE TABLE ... AS SELECT creates NOT NULL constraints on non-nullable columns I think (I didn't re-check, though). I'm not sure I see the value in that either, but, hey, standards compliance ought to be a value it in itself, right? the question is simply whether one values to feature enough to put in the word. ... or fund the work. There are people for hire in the community. And that was, of course, supposed to read put in the *work*. Alas, just putting in the *word* is probably not going to be enough ;-) best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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. That might be a rather tough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has provided so far. 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 metadata. That seems marginal. Yes. We need a pretty convincing use-case to seriously consider such a thing. 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 someone enough bother to want to devote the resources to changing it. 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. So even if someone stepped up with a patch, it'd likely get rejected outright, unless there's significant community buy-in to the need for it. I agree with Kevin's comment that the right thing to be doing now would be to be keeping a list of things we might want to change the protocol for. It's just about certain that no single element on that list will be sufficient reason to change, but once there are enough of them maybe we'll have critical mass to do them all together. (Actually, isn't there such a page on the wiki already? Or a subsection of the TODO list?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 metadata. That seems marginal. Well, there is one other, namely SQL standards compliance. It does mandate that CREATE TABLE ... AS SELECT creates NOT NULL constraints on non-nullable columns I think (I didn't re-check, though). I'm not sure I see the value in that either, but, hey, standards compliance ought to be a value it in itself, right? Um ... but that case has nothing to do with protocol changes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 infer table attributes from the result set metadata. That seems marginal. Well, there is one other, namely SQL standards compliance. It does mandate that CREATE TABLE ... AS SELECT creates NOT NULL constraints on non-nullable columns I think (I didn't re-check, though). I'm not sure I see the value in that either, but, hey, standards compliance ought to be a value it in itself, right? Um ... but that case has nothing to do with protocol changes. No, that was meant as a use-case for the deduction of nullability, not for it's transmission to the client. While those are obviously two distinct things, I figured we'd probably tackle them at the same time (if ever). It'd be strange to infer NOT NULL constraints for CREATE TABLE ... AS SELECT, yet provide no way for clients to obtain that information for simple SELECT statements. And you're right, the Wiki already contains a wish list for the next protocol version, and that wish list includes an entry for extending Describe to report the nullability of columns. The entry, BTW, was added by one Tom Lane ;-) The wish list can be found on http://wiki.postgresql.org/wiki/Todo under Wire Protocol Changes. The referenced thread on -hackers includes a rather interesting use-case. The idea presented there is to infer the type of a statement's result columns at application compile-time, and inject the result into the compiler's type checking and deduction algorithm. Since most statically types languages don't have a general concept of undefined (i.e., there's no special undefined value included in the domain of every type), there's a lot of value in knowing that a columns cannot be null. It allows you to map the column directly to a string, int or whatever on the client side, instead of going through some intermediate type which adds undefined to the list of possible values. (That intermediate type is the Maybe monad in Haskell, in C++ it'd be boost::optional or something similar) best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 someone enough bother to want to devote the resources to changing it. 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. So even if someone stepped up with a patch, it'd likely get rejected outright, unless there's significant community buy-in to the need for it. I agree with Kevin's comment that the right thing to be doing now would be to be keeping a list of things we might want to change the protocol for. It's just about certain that no single element on that list will be sufficient reason to change, but once there are enough of them maybe we'll have critical mass to do them all together. (Actually, isn't there such a page on the wiki already? Or a subsection of the TODO list?) There is. Currently section 27.3 (seems not to have an anchor to link, and might change numbers when other things change, but that's what it's called now). Heading wire protocol changes. And I think this is on there already? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 -- goncharov.a...@gmail.com -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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, however... 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. That makes me think that guessing which attributes of a query may be null seems like a pretty futile exercise. At first blush, 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. I'd not be keen on people putting much effort into futile exercises ; better to work on things that are less futile.
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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. ,--- 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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 -- alex-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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+Moncurem=1l=NULLd=365s=rp=44 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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+Moncurem=1l=NULLd=365s=rp=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 -- alex-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 -- alex-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 | 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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 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 -- alex-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
,--- 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-goncha...@comcast.net -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
