Re: [HACKERS] Trailing comma support in SELECT statements
On Tue, Oct 21, 2014 at 10:16 AM, Tom Lane t...@sss.pgh.pa.us wrote: (Of course, I'm not for the feature w.r.t. SQL either. But breaking data compatibility is just adding an entire new dimension of trouble. Another dimension of the trouble is breaking the operation of the tools that parse SQL statements for various purposes, e.g. for dependency analysis. This is a misfeature for the benefit of edit-lazy users only. -- Alex
Re: [HACKERS] libpq: PQcmdStatus, PQcmdTuples signatures can be painlessly improved
,--- You/Peter (Tue, 10 Jan 2012 19:13:42 +0200) * | On tis, 2011-12-13 at 07:55 -0500, Alex Goncharov wrote: |char *PQcmdStatus(PGresult *res); |char *PQcmdTuples(PGresult *res); | | Unreasonable: | |a. What, these two can modify 'res' I pass in?.. | |b. Oh, yes, because they return 'char *' pointing to | 'res-cmdStatus+n', so, a libpq user may write: | | char *s = PQcmdStatus(res); | *s = 'x'; | | and have 'res' modified. (Would be the user's fault, of course.) | | Note that const PGresult * would only warn against changing the | fields It would not warn, it would err (the compilation should fail). | of the PGresult struct. It doesn't do anything about changing the data | pointed to by pointers in the PGresult struct. So what you are saying | doesn't follow. By this logic, passing 'const struct foo *' doesn't have any point and value, for any function. But we know that this is done (and thank you for that) in many cases -- a good style, self-documentation and some protection. E.g. here: ,--- I/Alex (Tue, 13 Dec 2011 07:55:45 -0500) * | Compare: | | int PQntuples(const PGresult *res) | | Reasonable: doesn't modify 'res'. `-* BTW, I have not submitted the context differences, as suggested, only because of extreme overload at work and the need to do a careful caller and documentation analysis. I still hope to be able to do it in a reasonably near future. -- 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
[HACKERS] libpq: PQcmdStatus, PQcmdTuples signatures can be painlessly improved
Compare: int PQntuples(const PGresult *res) Reasonable: doesn't modify 'res'. With: char *PQcmdStatus(PGresult *res); char *PQcmdTuples(PGresult *res); Unreasonable: a. What, these two can modify 'res' I pass in?.. b. Oh, yes, because they return 'char *' pointing to 'res-cmdStatus+n', so, a libpq user may write: char *s = PQcmdStatus(res); *s = 'x'; and have 'res' modified. (Would be the user's fault, of course.) The non-const-ness of 'PGresult *' for these two functions seems to stand out among the functions covered in the 30.3.2. Retrieving Query Result Information manual section and inhibits writing the strict client code. I would suggest to change the signatures by applying this trivial patch (and changing the documentation): == diff orig/postgresql-9.1.1/src/interfaces/libpq/libpq-fe.h ./postgresql-9.1.1/src/interfaces/libpq/libpq-fe.h 450c450 extern char *PQcmdStatus(PGresult *res); --- extern const char *PQcmdStatus(const PGresult *res); 453c453 extern char *PQcmdTuples(PGresult *res); --- extern const char *PQcmdTuples(const PGresult *res); == diff orig/postgresql-9.1.1/src/interfaces/libpq/fe-exec.c ./postgresql-9.1.1/src/interfaces/libpq/fe-exec.c 2665,2666c2665,2666 char * PQcmdStatus(PGresult *res) --- const char * PQcmdStatus(const PGresult *res) 2736,2737c2736,2737 char * PQcmdTuples(PGresult *res) --- const char * PQcmdTuples(const PGresult *res) 2739,2740c2739 char *p, *c; --- const char *p, *c; (The above was obtained in 9.1.1; the subsequent build with GCC 4.1.2 succeeds without warnings.) If the above change causes a warning in a client code, so much the better: the client code is doing something unreasonable like the *s assignment in my example above. -- 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: PQcmdStatus, PQcmdTuples signatures can be painlessly improved
,--- I/Alex (Tue, 13 Dec 2011 07:55:45 -0500) * | If the above change causes a warning in a client code, so much the | better: the client code is doing something unreasonable like the *s | assignment in my example above. ,--- Robert Haas (Tue, 13 Dec 2011 10:51:54 -0500) * | Or they just haven't bothered to decorate their entire code-base with | const declarations. They don't have to, for the conceptually correct code. I.e. one can write (with the old and new code): /* no: const */ PGresult *res; const char *readout; readout = PQxxx(res,...); /* no: *readout = 'x'; */ all right and have no compilation warnings. But one can also (reasonably) const-qualify the 'res' above (const-correct and const-consistent code is a good thing.) | If you want this patch to be considered for application, you should | post an updated patch which includes the necessary doc changes and add | a link to it here: | | https://commitfest.postgresql.org/action/commitfest_view/open OK, I could do it... ,--- Alvaro Herrera (Tue, 13 Dec 2011 13:01:13 -0300) * | Do we really need a 100% complete patch just to discuss whether we're | open to doing it? IMHO it makes sense to see a WIP patch and then | accept or reject based on that; if we accept the general idea, then a | complete patch would presumably be submitted. `-* ... but I like this more. I.e., can one tell me to bother or not with the complete patch, based on the general idea, which wouldn't change for the complete patch? -- 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] What is known about PostgreSQL HP-UX support?
Thank you all who replied! -- Alex -- alex-goncha...@comcast.net -- /* * They're only trying to make me LOOK paranoid! */ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What is known about PostgreSQL HP-UX support?
[ Thanks all for the very productive discussion in the thread libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable which I originated. Very useful. Now on something different. ] About two years ago, I had to research some PostgreSQL failures on HP-UX on a lame PA-RISC box. Looking at the PostgreSQL source code then, I got an impression that running PostgreSQL on HP-UX was an open question -- HP-UX didn't seem like a seriously targeted platform. Was I wrong in my assessment? Does anybody have a good experience running PostgreSQL on HP-UX? What version of both? PA-RISC? IA64? Thanks, -- 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] What is known about PostgreSQL HP-UX support?
Thanks... ,--- You/Tom (Sun, 09 Oct 2011 22:29:19 -0400) * | Well, HP hasn't exactly been forthcoming with support on their own end, | but we do have an HPUX 11.31 IA64 machine in the buildfarm, Should I read the above as: 1. The PostgreSQL server will build on HPUX 11.31 IA64. 2. The server will run all right (speaking on the test cases you personally covered only) on HPUX 11.31 IA64. 3. Both PostgreSQL v. 8.4 and 9.1. | and I still routinely test on a personal 10.20 HPPA box, so it's not | like the platform doesn't get coverage at all. and the same three items on HPPA 10.20. Or anything of the items 1 to 3 is not true or not certain? Thanks again! -- 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
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
,--- 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
[HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
My understanding is that libpq does not allow one to find if a result set column is nullable. Is this right? (I know how to get a table column nullability information from pg_attribute.attnotnull, but when coding around the libpq API: * Is, OMG, ugly. * Doesn't cover the arbitrary SELECT statements. ) Thanks, -- Alex -- alex-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
,--- 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
,--- 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
,--- 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
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
,--- 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
,--- 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
Re: [HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT
,--- I/Alex (Mon, 24 May 2010 12:25:18 -0400) * | No equivalent of FETCH_COUNT is available at the libpq level, so I | assume that the interface I am using is smart enough not to send | gigabytes of data to FE. | | Where does the result set (GBs of data) reside after I call | PQexecPrepared? On BE, I hope? Sorry for asking again... No sarcasm meant: is there no straightforward answer here? Or nobody is certain? Or a wrong list? Thanks, -- 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, PQexecPrepared, data size sent to FE vs. FETCH_COUNT
,--- Abhijit Menon-Sen (Tue, 25 May 2010 17:26:18 +0530) * | Unless you explicitly declare and fetch from an SQL-level cursor, your | many GBs of data are going to be transmitted to libpq, which will eat | lots of memory. (The wire protocol does have something like cursors, | but libpq does not use them, it retrieves the entire result set.) ,--- Yeb Havinga (Tue, 25 May 2010 14:08:51 +0200) * | The GBs of data are gathered at the site of the libpq client (pgresult | object gathered/allocated while consuming result input from backend). `--* Thank you very much! -- 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
[HACKERS] libpq, PQexecPrepared, data size sent to FE vs. FETCH_COUNT
I have some libpq-using application code, in which fetching the data
follows this logic (after a statement has been prepared):
PQexecPrepared(pg_result, pg_conn, pg_statement_name, input_param_cnt,
param_values, param_lengths, param_formats, result_format);
PQntuples(rows_in_result, pg_result);
/* The application provides storage so that I can pass a certain number of
rows
* (rows_to_pass_up) to the caller, and I repeat the following loop until
* many rows_to_pass_up cover all the rows_in_result (pg_row_num_base keeps
the track
* of where I am in the process. */
for (int row_idx = 0; row_idx rows_to_pass_up; ++row_idx) {
const int pg_row_number = row_idx + pg_row_num_base;
for (int pg_column_number = 0; pg_column_number result_column_cnt_
++pg_column_number) {
PQgetvalue(value, pg_result, pg_row_number, pg_column_number);
PQgetlength(length, pg_result, pg_row_number, pg_column_number);
}
}
My question is: am I doing the right thing from the data size being
passed from BE to FE perspective?
The code in `bin/psql' relies on the value of the FETCH_COUNT
parameter to build an appropriate
fetch forward FETCH_COUNT from _psql_cursor
command.
No equivalent of FETCH_COUNT is available at the libpq level, so I
assume that the interface I am using is smart enough not to send
gigabytes of data to FE.
Is that right? Is the logic I am using safe and good?
Where does the result set (GBs of data) reside after I call
PQexecPrepared? On BE, I hope?
Thanks,
-- 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
