Thread: Re: [HACKERS] PQfnumber and quoted identifiers

[ pgsql-interfaces added to cc list ]

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Peter Eisentraut wrote:
>> There was a discussion on -interfaces that might need more consideration.
>> 
>> http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00026.php
>> 
>> Apparently, it has so far been an undocumented feature of libpq's function
>> PGfnumber (return column number from column name) that the column name
>> needs to be double-quoted if it contains upper-case letters.  That, is you
>> need to write
>> 
>> PQfnumber(res, "\"Bar\"")
>> 
>> I think this is completely bizarre and pointless.  This is a C interface
>> and not SQL.  Other libpq functions that accept names of SQL objects don't
>> do this.  Also, PQfname and PQfnumber ought to be inverses.
>> 
>> Since this behavior was undocumented and no one had noticed it in the last
>> 10 years, I think we can away with removing it.

> I don't agree; you'll certainly break all libpq apps that contact 
> databases with columns that have uppercase or special chars, and the 
> failure might be very subtle because in most cases you wouldn't expect 
> that this function call fails after you successfully created a rowset. 

That was pretty much the argument that carried the day in the earlier
thread.  However, I'm not sure how many people really use PQfnumber
(as opposed to hard-wiring assumptions about returned column numbers),
and it would seem that the intersection of those people with people who
use mixed-case column names may be nearly the empty set.  If a lot of
people did this, the behavior would have been discussed and documented
(or changed) long ago.  So I'm not convinced that we'd really break
very many apps by changing to the behavior that everyone seems to agree
is more sensible.

A data point is that we did make comparable changes to the handling of
database names a couple releases ago, and we got few if any gripes.

Another data point is that the original Berkeley coding of PQfnumber
did not have the case folding/dequoting behavior.  The history seems
to be:

Original code: straight strcmp() of argument against returned column name

1997-05-19 23:38: replace strcmp() with strcasecmp() (no dequoting
logic, pretty obviously a broken idea in hindsight)

1997-11-10 00:10: attempted to implement the current behavior of
dequoting+downcasing, but due to a typo, the actual effect was to revert
the behavior to exact match

1999-02-03 15:19: fix typo, installing the current behavior

So other than the shortlived 6.2 release, releases before 6.5 had the
behavior Peter wants.  I find it interesting that it took more than a
year for anyone to notice that the putative dequoting+downcasing logic
installed for 6.3 didn't work.
        regards, tom lane

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> If you change it, please give me a chance to code it version-aware so 
> quoting/non-quoting can be performed dependent on libpq in use.

If you want a run-time test, the most reliable way would be to directly
test what PQfnumber does --- for instance, make a query known to
contain the column name "foo", and see what PQfnumber(res, "\"foo\"")
returns.  I'm not sure there is any other solution, since we can't
retroactively install a version identifier in old libpq releases.

For a compile-time test, you could perhaps look to see if
PG_DIAG_SQLSTATE or one of the other new macros in postgres_ext.h
is defined.  Not sure you really want a compile-time test though;
it'd break very easily if you get linked against some other version
of the library.

As for the more general question of whether to offer libpq version
identification going forward, I have no strong opinion on whether
it's really useful or not.  If it's wanted, I'm tempted to suggest
that PQparameterStatus() could be extended to recognize "libpq_version"
paralleling "server_version".  Not sure about a clean way to expose
the version at compile time.
        regards, tom lane

"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Is there a reason why it can't accecpt both formats?

Does it downcase FOO or not?  You can't have it both ways.
        regards, tom lane

Tom Lane wrote:

>That was pretty much the argument that carried the day in the earlier
>thread.  However, I'm not sure how many people really use PQfnumber
>(as opposed to hard-wiring assumptions about returned column numbers),
>and it would seem that the intersection of those people with people who
>use mixed-case column names may be nearly the empty set.  If a lot of
>people did this, the behavior would have been discussed and documented
>(or changed) long ago.  So I'm not convinced that we'd really break
>very many apps by changing to the behavior that everyone seems to agree
>is more sensible.
>  
>
...

>So other than the shortlived 6.2 release, releases before 6.5 had the
>behavior Peter wants.  I find it interesting that it took more than a
>year for anyone to notice that the putative dequoting+downcasing logic
>installed for 6.3 didn't work.
>
pgAdmin3 beta testers found it...

If you change it, please give me a chance to code it version-aware so 
quoting/non-quoting can be performed dependent on libpq in use.


Regards,
Andreas

On Sun, 2003-10-05 at 13:43, Andreas Pflug wrote:
> Tom Lane wrote:
> >So other than the shortlived 6.2 release, releases before 6.5 had the
> >behavior Peter wants.  I find it interesting that it took more than a
> >year for anyone to notice that the putative dequoting+downcasing logic
> >installed for 6.3 didn't work.
> >
> pgAdmin3 beta testers found it...
> 
> If you change it, please give me a chance to code it version-aware so 
> quoting/non-quoting can be performed dependent on libpq in use.

Is there a reason why it can't accecpt both formats?  That way PQfname
and PQfnumber could be inverses of each other.  The only wart is that
PQfnumber would also accecpt the "\"Bar\"" format also.