Fwd: Extended query protocol and exact types matches.

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 ?

PS.

I know, that queries like SELECT $1 does not work without
specifying OID or without rewriting it to e.g. SELECT $1::text.

Thanks.

--
// Dmitriy.

Hey sql@,

---------- Forwarded message ----------
From: Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
Date: 2010/12/9
Subject: Extended query protocol and exact types matches.
To: pgsql-general(at)postgresql(dot)org

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 ?

PS.

I know, that queries like SELECT $1 does not work without
specifying OID or without rewriting it to e.g. SELECT $1::text.

Thanks.

--
// Dmitriy.

--
// Dmitriy.

---------- Forwarded message ----------
From: Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
Date: 2010/12/10
Subject: Fwd: Extended query protocol and exact types matches.
To: postgres list <pgsql-sql(at)postgresql(dot)org>

Hey sql@,

---------- Forwarded message ----------
From: Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
Date: 2010/12/9
Subject: Extended query protocol and exact types matches.
To: pgsql-general(at)postgresql(dot)org

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 ?

PS.

I know, that queries like SELECT $1 does not work without
specifying OID or without rewriting it to e.g. SELECT $1::text.

Thanks.

--
// Dmitriy.

--
// Dmitriy.

--
// Dmitriy.

On Fri, Dec 10, 2010 at 2:53 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> [ message that was forwarded to three mailing lists in an 12 hour period ]

Come on, give me a break! How quickly do you expect people to answer
your questions? It's reasonable to follow up if you haven't heart
anything in a few days, but this is ridiculous. And you might want to
follow up to the original list first, rather than a different one.
Like this: "I see that no one responded to this email... can anyone
help me out on this? If there's additional information that I should
provide, please let me know what would be helpful. Thanks!"

If you want people to give a higher priority to your questions than
they're willing to do for free, you need a commercial support
contract. If you want answers to routine questions within a handful
of hours, you probably need an *expensive* commercial support
contract.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hey Robert,

2010/12/10 Robert Haas <robertmhaas(at)gmail(dot)com>

> On Fri, Dec 10, 2010 at 2:53 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> > [ message that was forwarded to three mailing lists in an 12 hour period
> ]
>
> Come on, give me a break!

Please sorry ! But I don't mail to you personally.

> How quickly do you expect people to answer
> your questions? It's reasonable to follow up if you haven't heart
> anything in a few days, but this is ridiculous.

Where is it written how many time I should wait before reask/forward ?
But sorry for this anyway !

> And you might want to
> follow up to the original list first, rather than a different one.
>
Where is it written ?

> Like this: "I see that no one responded to this email... can anyone
> help me out on this? If there's additional information that I should
> provide, please let me know what would be helpful. Thanks!"
>
Thanks for the advice !

>
> If you want people to give a higher priority to your questions than
> they're willing to do for free, you need a commercial support
> contract. If you want answers to routine questions within a handful
> of hours, you probably need an *expensive* commercial support
> contract.
>
Thanks for the offer ! As soon as there will be money without fail we
will think over it.

It would be quicker to answer my question and help than to teach me
the alphabet of communication. Although, thank you, and for that :-)

--
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--
// Dmitriy.

On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)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

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?

2010/12/10 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)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
>

--
// Dmitriy.

On Fri, Dec 10, 2010 at 11:46 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> It would be quicker to answer my question and help than to teach me
> the alphabet of communication. Although, thank you, and for that :-)

It would be quicker still to ignore your email altogether, but I'm
guessing you're not going to recommend that I take that approach. I
did look briefly at whether I could also answer the question while I
was replying, but found that I wasn't really sure what you were asking
(which may be why no one else responded either). To the best of my
ability to determine what you were actually asking, it was something
along the lines of "What will happen if I tell libpq that a parameter
is a bigint when the server is expecting an int?". I don't happen to
know the answer to that question without writing a 25-line program,
compiling it, and testing it. Which wouldn't be very hard, but on the
other hand it wouldn't be very hard for you to do it either. Best
guess without testing? It'll work if the value is within the range
that can be represented by an int and throw an error otherwise.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2010/12/10 Robert Haas <robertmhaas(at)gmail(dot)com>

> On Fri, Dec 10, 2010 at 11:46 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> > It would be quicker to answer my question and help than to teach me
> > the alphabet of communication. Although, thank you, and for that :-)
>
> It would be quicker still to ignore your email altogether, but I'm
> guessing you're not going to recommend that I take that approach.

Sure not !

> I
> did look briefly at whether I could also answer the question while I
> was replying, but found that I wasn't really sure what you were asking
> (which may be why no one else responded either).

I've got one useful respond before this post in general(at)(dot)

> To the best of my
> ability to determine what you were actually asking, it was something
> along the lines of "What will happen if I tell libpq that a parameter
> is a bigint when the server is expecting an int?". I don't happen to
> know the answer to that question without writing a 25-line program,
> compiling it, and testing it. Which wouldn't be very hard, but on the
> other hand it wouldn't be very hard for you to do it either. Best
> guess without testing? It'll work if the value is within the range
> that can be represented by an int and throw an error otherwise.
>
Yes, I've already investigated it, compiled and tested. The first line of
my initial post says: "To be assured and just for calmness.". :-)

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--
// Dmitriy.

On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)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

2010/12/10 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)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,

Me ? :-) !true ! I just pretend not to bloat libpq and keep it clean...

> 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 :-).
>
I've implemented client side prepare too! :-) So, I am on right way and
not alone! :-)

>
> merlin
>

Thank you very much ! You help me a lot!

--
// Dmitriy.