Re: Libpq support for precision and scale

Tom Lane wrote:
>
> Fernando Nasser <fnasser(at)cygnus(dot)com> writes:
> > This is a patch that was posted some time ago to pgsql-patches and
> > no one has commented on it.
> > It adds something that JDBC has that is not present in libpq (see
below).
> > Is it OK for inclusion?
>
> Here are some comments ...
>

Thanks.

> > int PQfprecision(const PGresult *res, int field_num);
> > int PQfscale(const PGresult *res, int field_num);
>
> > Return Scale and Precision of the type respectively.
>
> These seem okay, but I don't like the API detail that "0 is returned if
> information is not available". 0 is a valid result, at least for
> PQfscale. I would recommend returning -1. If you really want to
> distinguish bad parameters from non-numeric datatype, then return -1
> and -2 for those two cases.
>

This seems to be the libpq convention. On calls such as PQfsize and
PQfmod, for instance, zero is a valid result and is also returned if
the information is not available.

Please note that we did not make this convention -- our original version
did return -1. But we decided that following a different rule for these
two routines was even more confusing. And change the return convention
for the whole set of functions at this point seems out of the question.

P.S.: Maybe whoever originally designed the libpq interface was trying
to accomplish some sort of "soft fail" by returning zero. Just a guess
of course.

> > Most programs won't need this information and may not be willing
> > to pay the overhead for metadata retrieval. Thus, we added
> > an alternative function to be used instead of PQexec if one
> > wishes extra metadata to be retrieved along with the query
> > results:
>
> > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query);
>
> This strikes me as very ugly, and unnecessary, and inefficient since
> it retrieves metadata for all columns even though the client might
> only need to know about some of them.

This part I would not worry about. The new routines are for result sets
(not arbitrary columns) so the fields present in it have already been
pre-selected. Also, this kind of information is useful for tools as
they don't know beforehand what the fields will be. In all cases
we can think of, the tool will always want metadata about all the
fields.

> An even worse problem is that
> it'll fail entirely with a multi-query query string.
>

This is a bummer. But I see no solution for this besides documenting
the restriction in the manual. If I am not mistaken we already have
the limitation of returning just the last result anyway (we just
collect the error messages).

> What I think would be cleaner would be to do the metadata queries
> on-the-fly as needed. With the caching that you already have in there,
> on-the-fly queries wouldn't be any less efficient.
>
> But to do a metadata query we must have access to the connection.
> We could handle it two ways:
>
> 1. Add a PGconn parameter to the querying functions.
>

The problem is that results may be kept longer than connections
(see below). The current solution did not require the connection
as the metadata is for the result set, not tables.

The PGconn parameter would be reasonable for retrieving metadata
about table columns, for instance.

> 2. Make use of the PGconn link that's stored in PGresults, and
> specify that these functions can only be used on PGresults that
> came from a still-open connection.
>

That field has been deprecated (see comments in the source code)
because a result may be kept even after the connection is closed.

> I think I prefer the first, since it makes it more visible to the
> programmer that queries may get executed. But it's a judgment call
> probably; I could see an argument for the second as well. Any comments,
> anyone?
>

It would have to be the former (to avoid the stale pointer problem).

But requiring a connection adds a restriction to the use of this info
and makes it have a different life span than the object it refers to
(a PGresult), which is very weird.

> > The PQftypename function returns the internal PostgreSQL type name.
> > As some programs may prefer something more user friendly than the
> > internal type names, we've thrown in a conversion routine as well:
> > char *PQtypeint2ext(const char *intname);
> > This routine converts from the internal type name to a more user
> > friendly type name convention.
>
> This seems poorly designed. Pass it the type OID and typmod, both of
> which are readily available from a PQresult without extra computation.
> That will let you call the backend's format_type ... of course you'll
> need a PGconn too for that.
>

Requiring the PGconn is bad. But we still could have a PQFtypeExt()
returning the "external" type if people prefer it that way.
We thought that this should be kept as an explicit conversion
operation to make clear the distinction of what the backend knows
about and this outside world view of things.

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9