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

Is this right?

(I know how to get a table column nullability information from
pg_attribute.attnotnull, but when coding around the libpq API:

* Is, OMG, ugly.

* Doesn't cover the arbitrary SELECT statements.
)

Thanks,

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

Search Discussions

  • Merlin Moncure at Oct 6, 2011 at 8:16 pm

    On Thu, Oct 6, 2011 at 1:02 PM, Alex Goncharov 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
  • Alex Goncharov at Oct 6, 2011 at 8:22 pm
    ,--- I/Alex (Thu, 06 Oct 2011 14:02:14 -0400) ----*
    My understanding is that libpq does not allow one to find if a result
    set column is nullable.
    ,--- You/Merlin (Thu, 6 Oct 2011 15:16:18 -0500) ----*
    why aren't you using PQgetisnull()?
    This function is not about the nullability of a column but rather
    about the value in a result set cell:

    PQgetisnull: Tests a field for a null value.

    int PQgetisnull(const PGresult *res, int row_number, int column_number);

    Notice the 'row_number'.

    -- Alex -- alex-goncharov@comcast.net --
  • Merlin Moncure at Oct 6, 2011 at 8:39 pm

    On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov 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
  • Florian Pflug at Oct 6, 2011 at 9:17 pm

    On Oct6, 2011, at 22:38 , Merlin Moncure wrote:
    On Thu, Oct 6, 2011 at 3:22 PM, Alex Goncharov
    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
  • Merlin Moncure at Oct 6, 2011 at 9:29 pm

    On Thu, Oct 6, 2011 at 4:16 PM, Florian Pflug 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
  • Alex Goncharov at Oct 6, 2011 at 10:11 pm
    ,--- 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-goncharov@comcast.net --
  • Andrew Dunstan at Oct 6, 2011 at 10:30 pm

    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
  • Florian Pflug at Oct 6, 2011 at 11:00 pm

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

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

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

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

    Views are a kind of mixture between the two - their meta-data isn't any
    richer than that of a SELECT statement, but since VIEWs aren't transient
    objects like statements, their meta-data *is* reflected in the catalog.
    Other databases do that - for example, I believe to remember that
    Microsoft SQL Server preserves NOT NULL constraints if you do

    CREATE TABLE bar AS SELECT * from foo;
    I don't know a database where this would not be true.
    Ähm... postgres would be one where the resulting table doesn't have any
    NOT NULL columns. Ever.
    So the question makes perfect sense, and the answer is: No, postgres currently
    doesn't support that, i.e. doesn't deduce the nullability of result columns,
    not even in the simplest cases.
    You are wrong: as in my original mail, use pg_attribute.attnotnull to
    see why I say this.
    Nope, you miss-understood what I said. I said "result columns", meaning the
    columns resulting from a SELECT statement. Postgres doesn't deduce the nullability
    of these columns. The fact that postgres supports NOT NULL constraints on tables
    (which is what pg_attribute.attnotnull is for) really has nothing to do with that.

    best regards,
    Florian Pflug
  • Alex Goncharov at Oct 6, 2011 at 11:42 pm
    ,--- You/Florian (Fri, 7 Oct 2011 01:00:40 +0200) ----*
    On Oct7, 2011, at 00:02 , Alex Goncharov wrote:
    ,--- Florian Pflug (Thu, 6 Oct 2011 23:16:53 +0200) ----*
    Sure, but there are still a lot of cases where the database could deduce
    (quite easily) that a result column cannot be null.
    Right. Of course. I can do it in 'psql'.
    For the result of an *arbitrary* query?
    In 'psql', no: I was commenting specifically, and confirming what you
    said, on your

    a lot of cases where the database could deduce (quite easily) that a
    result column cannot be null
    I think what you are missing is that there is *huge* difference between
    tables (as created by CREATE TABLE) and result sets produced by SELECT
    statements.
    Actually, no, I am not missing the huge difference -- again, I was
    just agreeing with you. Agreeing that there is a lot of cases where
    the nullability can be trivially deduced, even in 'psql'. (That also
    meant disagreeing with the message posted before yours.)
    The former can carry all sorts of constraints like NOT NULL, CHECK,
    REFERENCES, ..., and their structure as well as the constraints they carry
    are stored in the catalog tables in the schema pg_catalog. Yes.
    The latter cannot carry any constraints, and their meta-data thus consist
    simply of a list of column names and types. Their meta-data is also
    transient in nature, since it differs for every SELECT you issue.
    Right: but for (most?) every SELECT, one can logically deduce whether
    it can be guaranteed that a given column will never have a NULL value.
    Since in a given SELECT, the result column are a combination of either
    other columns, or expressions, including literals.

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

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

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

    This is conceptually feasible.

    And in PostgreSQL, this could be done by combining

    (1) Oid PQftable(const PGresult *res, int column_number);
    (2) int PQftablecol(const PGresult *res, int column_number);
    (3) a SQL query of pg_attribute,attnotnull

    I have not tried this yet, hesitating to walk into a monstrosity and
    hoping that there is some hidden way to get the information through
    one of

    int PQfmod(const PGresult *res, int column_number);
    int PQgetisnull(const PGresult *res, int row_number, int column_number);

    (the latter with an odd 'row_number'; I actually tried row_number= 0
    and -1, after preparing a statement. No luck.)
    Views are a kind of mixture between the two - their meta-data isn't any
    richer than that of a SELECT statement, but since VIEWs aren't transient
    objects like statements, their meta-data *is* reflected in the
    catalog.
    Again, combining (1), (2) and (3) above should give a good answer here.
    Other databases do that - for example, I believe to remember that
    Microsoft SQL Server preserves NOT NULL constraints if you do

    CREATE TABLE bar AS SELECT * from foo;
    I don't know a database where this would not be true.
    Ähm... postgres would be one where the resulting table doesn't have any
    NOT NULL columns. Ever.
    Not sure what you mean here:

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

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

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

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

    CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
    );
    --------------------------------------------------
    So the question makes perfect sense, and the answer is: No, postgres currently
    doesn't support that, i.e. doesn't deduce the nullability of result columns,
    not even in the simplest cases.
    You are wrong: as in my original mail, use pg_attribute.attnotnull to
    see why I say this.
    Nope, you miss-understood what I said.
    You said, "not even in the simplest cases" -- and this is what caused
    my statement.
    I said "result columns", meaning the columns resulting from a SELECT
    statement.
    Then I misunderstood you, indeed -- I thought you included an inquiry
    about a table. Sorry for the misunderstanding then.
    Postgres doesn't deduce the nullability of these columns. The fact
    that postgres supports NOT NULL constraints on tables (which is what
    pg_attribute.attnotnull is for) really has nothing to do with that.
    create table t1(nn1 char(1) not null, yn1 char(1) null);
    create table t2(nn2 char(1) not null, yn2 char(1) null);

    (may use pg_attribute.attnotnull on t1, t2, is I didn't see the 'create's.

    Now, for this statement, I can easily identify non-nullable columns.

    select
    t1.nn1, -- guaranteed: not null
    t1.ny1, -- nullable
    t2.nn2, -- guaranteed: not null
    t2.ny2 -- nullable
    from t1, t1;
    best regards,
    Florian Pflug
    Thank you -- I appreciate the conversation!

    -- Alex -- alex-goncharov@comcast.net --
  • Alex Goncharov at Oct 7, 2011 at 12:20 am
    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.alex@gmail.com --
  • Andres Freund at Oct 7, 2011 at 12:31 am

    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
  • Alex Goncharov at Oct 7, 2011 at 12:47 am
    ,--- You/Andres (Fri, 7 Oct 2011 02:28:30 +0200) ----*
    a lot of cases where the database could deduce (quite easily) that a
    result column cannot be null
    Could you quickly explain what exactly you want that information for? Just
    because it has been done before doesn't necessarily mean its a good idea...
    I am not writing a database application here (i.e. I am not storing
    the data). I am responding to a client requirement, basically:

    Given a SELECT (or possibly, simpler, a table name), tell me which
    columns are non-nullable?

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

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

    Ugly :(

    -- Alex -- alex-goncharov@comcast.net --
  • Anarazel at Oct 7, 2011 at 12:54 am

    Alex Goncharov 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
  • Alex Goncharov at Oct 7, 2011 at 1:06 am
    ,--- You/anarazel@anarazel.de (Fri, 07 Oct 2011 02:54:39 +0200) ----*
    Given a SELECT (or possibly, simpler, a table name), tell me which
    columns are non-nullable?
    That doesnt explain why it's needed.
    It's needed for some meta analysis. That's as much as I can say.
    To get community buyin into a feature the community - or at least
    parts of it - need to understand why its needed.
    Take a look at these APIs:

    http://download.oracle.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#isNullable(int)

    int isNullable(int column) throws SQLException
    Indicates the nullability of values in the designated column.

    http://msdn.microsoft.com/en-us/library/ms716289(v=VS.85).aspx

    NullablePtr [Output] Pointer to a buffer in which to return a
    value that indicates whether the column allows NULL values.

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

    -- Alex -- alex-goncharov@comcast.net --
  • Alex Goncharov at Oct 7, 2011 at 12:34 am
    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-goncharov@comcast.net --
  • Florian Pflug at Oct 7, 2011 at 1:21 am

    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
  • Alex Goncharov at Oct 7, 2011 at 1:45 am
    ,--- You/Florian (Fri, 7 Oct 2011 03:21:23 +0200) ----*
    Sure. Deducing nullability isn't a hard problem, at least not if it's
    OK to simply say "nullable" if things get too complex.
    Yes.
    And in PostgreSQL, this could be done by combining

    (1) Oid PQftable(const PGresult *res, int column_number);
    (2) int PQftablecol(const PGresult *res, int column_number);
    (3) a SQL query of pg_attribute,attnotnull
    That won't work. I'm pretty sure that you'll get the wrong answer
    for queries involving OUTER joins, e.g.

    SELECT * FROM foo LEFT JOIN bar ON bar.foo_id = foo.foo_id
    That's a good point. But I'll do with what I manage to get. I am
    pretty sure that in my client's use, this is not going to be an issue.

    And OTOH, I am not sure that other databases will give me a good
    answer. I'll play with them soon, out of technical curiosity.
    I have not tried this yet, hesitating to walk into a monstrosity and
    hoping that there is some hidden way to get the information through
    one of

    int PQfmod(const PGresult *res, int column_number);
    int PQgetisnull(const PGresult *res, int row_number, int column_number);
    Let me assure you that there's no "hidden way". The feature is simply
    unsupported.
    Oh, great -- that's the second best answer I hoped for: just didn't
    want to go down the expensive and not fool-proof way by mistake. Had
    to ask this list.
    Now, for this statement, I can easily identify non-nullable columns.

    select
    t1.nn1, -- guaranteed: not null
    t1.ny1, -- nullable
    t2.nn2, -- guaranteed: not null
    t2.ny2 -- nullable
    from t1, t1;
    Sure. So can I. But postgres can't, since nobody's implemented the necessary
    algorithm so far. You're very welcome to produce a patch, though.
    I've looked into the 'src/interfaces/libpq' and other parts of 'src'
    more than once and suspect that I won't be able to find where to plug
    this in correctly, even if I figure out a meaningful algorithm.
    Should you decide to do that,
    Unlikely: in a couple of days I hope to have my implementation as I
    described before, then there will be no need for our application to
    wait for the desired PQfnullable function. Besides, our application
    has to work with any libpq.so.5, so no new PQ* function can be called.

    I'd only venture to do it for the personal goal of contributing to
    PostgreSQL. Who knows, but unlikely -- a too high barrier to entry.
    I recommend that you discuss the design of this *before* starting
    work (in a separate thread). Otherwise, you might discover
    objections to the general approach, or even to the whole feature,
    only after you put considerable effort into this.

    best regards,
    Florian Pflug
    Thank you: this is all very valuable,

    -- Alex -- alex-goncharov@comcast.net --
  • Bruce Momjian at Oct 6, 2011 at 11:09 pm

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

    Thanks for sharing your knowledge of applications.

    (Look, I appreciate anybody's reply and readiness to help, but if you
    have a limited expertise in the subject area, why bother replying?)
    FYI, I see 867 Postgres posts mentioning Merlin Moncure in the past
    year:

    http://search.postgresql.org/search?q=Merlin+Moncure&m=1&l=NULL&d=365&s=r&p=44

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Alex Goncharov at Oct 7, 2011 at 12:16 am
    ,--- You/Bruce (Thu, 6 Oct 2011 19:09:16 -0400 (EDT)) ----*
    (Look, I appreciate anybody's reply and readiness to help, but if you
    have a limited expertise in the subject area, why bother replying?)
    FYI, I see 867 Postgres posts mentioning Merlin Moncure in the past
    year:

    http://search.postgresql.org/search?q=Merlin+Moncure&m=1&l=NULL&d=365&s=r&p=44
    I watch most of the PostgreSQL technical lists all the time and know
    who is who.

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

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

    To this:

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

    I replied politely:

    ,--- I/Alex (Thu, 06 Oct 2011 16:22:28 -0400) ----*
    This function is not about the nullability of a column but rather
    about the value in a result set cell:

    PQgetisnull: Tests a field for a null value.

    int PQgetisnull(const PGresult *res, int row_number, int column_number);

    Notice the 'row_number'.
    `-------------------------------------------------*

    To this:

    ,--- Merlin Moncure (Thu, 6 Oct 2011 15:38:59 -0500) ----*
    right -- get it. well, your question is doesn't make sense then --

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

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

    ,--- I/Alex (Thu, 06 Oct 2011 18:02:41 -0400) ----*
    What?..

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

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

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

    To this:

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

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

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

    I know.

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

    On the technical point now:

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

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

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

    Thanks everybody who replied!

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

    -- Alex -- alex-goncharov@comcast.net --
  • Peter Eisentraut at Oct 7, 2011 at 8:14 am

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

    Thank you -- this is very helpful: I was not aware of a commitfest
    list. Will try to check it out within a few days.
    Because that patch would presumably already implement much of the
    logic necessary to determine whether a give expression implies
    nullability or not and catalog this in a simpler fashion. Based on
    that you will then have to drag this information around and put it
    on the wire so that the client APIs can process it.
    `-----------------------------------------------------------*

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

    -- Alex -- alex-goncharov@comcast.net --
  • Merlin Moncure at Oct 7, 2011 at 12:40 pm

    On Thu, Oct 6, 2011 at 5:02 PM, Alex Goncharov 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
  • Alex Goncharov at Oct 7, 2011 at 1:47 pm
    ,--- You/Merlin (Fri, 7 Oct 2011 07:39:57 -0500) ----*
    On Thu, Oct 6, 2011 at 5:02 PM, Alex Goncharov
    ,--- Merlin Moncure (Thu, 6 Oct 2011 16:28:56 -0500) ----*
    hm, good point.  not sure how it's useful though.  I suppose an
    application could leverage that for validation purposes, but that's a
    stretch I think.
    `--------------------------------------------------------*

    Thanks for sharing your knowledge of applications.

    (Look, I appreciate anybody's reply and readiness to help, but if you
    have a limited expertise in the subject area, why bother replying?)
    Well, admittedly, perhaps my response was hastily written. But try
    to understand the zen of things around here: often if you
    propose/gripe/suggest something, you'll get a challenge back which
    is really fishing for more detail. It's not personal.
    Merlin,

    I appreciate the spirit of the PostgreSQL technical lists: I am
    permanently subscribed to PERFORM, and, occasionally, to HACKERS. I
    regularly unsubscribe from the latter because it quickly overloads me
    with the flood of messages I have no time even to read, not to say,
    digest. HACKERS would be one of the most useful technical reads, if
    it were not so bloody floody.

    (On GENERAL, take a look at this reply to a question similar to mine:

    http://archives.postgresql.org/pgsql-general/2005-08/msg01152.php

    What's the value of this kind of advice?)
    By the way, you still haven't explained use cases.
    As I said yesterday, it is for my client to find various meta data.

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

    Plus, now you have seen, from Peter Eisentraut's message that I just
    replied to, and from the mail archive link I posted a dozen of lines
    above here, that I am not the first person interested in this kind of
    functionality in the PostgreSQL land.
    You can always talk hypotheticals...'other people do it' is not a
    standard for inclusion of a feature (although it can be).
    I didn't ask anybody to include anything in PostgreSQL; my question,
    now unambiguously answered (thank you, the list!) was:

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

    Is this right?
    `-------------------------------------------------*

    Compare this with what you have tried to write about.
    I've been coding against libpq for years and years and have never
    needed to test for nullability,
    It's not a serious argument, in my opinion.
    so that's where my skepticism comes from.
    `-------------------------------------------------*

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

    Thank you,

    -- Alex -- alex-goncharov@comcast.net --
  • Christopher Browne at Oct 8, 2011 at 9:37 pm
    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."
  • Florian Pflug at Oct 9, 2011 at 10:45 am

    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
  • Kevin Grittner at Oct 9, 2011 at 12:20 pm

    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
  • Florian Pflug at Oct 9, 2011 at 2:56 pm

    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
  • Tom Lane at Oct 9, 2011 at 3:57 pm

    Florian Pflug 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
  • Florian Pflug at Oct 9, 2011 at 5:10 pm

    On Oct9, 2011, at 17:56 , Tom Lane wrote:
    Florian Pflug <fgp@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
  • Tom Lane at Oct 9, 2011 at 3:52 pm

    "Kevin Grittner" <Kevin.Grittner@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
  • Magnus Hagander at Oct 9, 2011 at 6:40 pm

    On Sun, Oct 9, 2011 at 17:51, Tom Lane wrote:
    "Kevin Grittner" <Kevin.Grittner@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?
  • Peter Eisentraut at Oct 10, 2011 at 8:30 am

    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?
  • Tom Lane at Oct 10, 2011 at 1:57 pm

    Peter Eisentraut 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
  • Kevin Grittner at Oct 10, 2011 at 11:25 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 6, '11 at 6:02p
activeOct 10, '11 at 1:57p
posts35
users12
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase