Thread: Extended query protocol and exact types matches.

On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Hey general@,
>
> To be assured and just for calmness.
>
> Problem:
>
> 1. CREATE TABLE test_tab (id integer, dat varchar(64));
>
> 2. INSERT INTO test_tab VALUES($1, $2) via PQexecParams,
>  where paramTypes[0] == OID of bigint,
>            paramTypes[1] == OID of text.
>
> Questions:
>
> Whether this case falls to
> http://www.postgresql.org/docs/9.0/static/typeconv-query.html ?
>
> Is such cases safe or it is recommended (best) to specify a
> OIDs which are exact matches ?

Anyways, here's the deal:

The oid vector passed to the database in these functions is for
describing the data you are passing. If left NULL, you leave it up to
the database to try and guess what you are sending based on the
context of the query.  This has pros and cons.  With the text
protocol, it's somewhat ok to leave off the oid vector: this isn't
much different from sending uncasted unknown strings into psql.  It's
basically there to protect you from sending bogus data to the server
and reduce chance of type confusion.  If you are using binary
protocol, the oid vector is absolutely essential -- it's insane to
have the server 'guess' what you are passing in since a wrong guess
could be interpreted improperly vs a formatting error that text
casting raises.  If you are wrapping libpq with a higher level
library, sending the correct oids always would be a pretty good idea.
Meaning, you should try and coerce your application/language types
into a type the database understands and pass a corresponding oid.

merlin

On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Hey Merlin,
>
> Thank you for explanation !
>
> Yes, I understand that specifying NULL instead real OID will provoke
> the parser attempts to infer the data types in the same way as it would
> do for untyped literal string constants.
> But there are three string types: text, varchar(n) and character(n) which
> has a different OIDs but they are all in the same type category. So, is it
> worth it to implement some Varchar and Character types (which actually
> wraps Text) at the library level or specifying the OID of text for contexts
> where these parameters actually varchar or char (i.e. types of same
> category) are safe?

not really, at the end of the day, you are coming in from C char*, so
just send TEXTOID and let the server worry about what to do if say you
are passing into varchar or (more rarely char(n)).  libpqtypes, the
library you are pretending doesn't exist, does this
(http://libpqtypes.esilo.com/man3/pqt-specs.html).  PGtext is
typedef'd char* and the only format string for character types is
%text.

IMNSHO, If you wanted to attack this problem in an actually novel and
useful way in C++ style, I would consider taking the libpqtypes
library, rip out all the format string stuff, and rig variadic
templates so you could leverage variadic queries.  Maybe this could be
integrated into libpqxx, not sure.

printf : cout :: : PQexecf  : query

query(conn, "select $1 + $2", 3, 7);

'query' is hypothetical function that uses template type inference,
mapping/marshaling data and building the data structure that
PQexecParams points to (in libpqtypes, the PGparam).  Parsing the type
format string is expensive enough that we had to implement a client
side prepare to reduce the cost of searching type handlers over and
over.  Of course, cout is not really faster than printf, but that's
another topic :-).

merlin