Re: [GENERAL] Extended query protocol and exact types matches.
2010/12/10 Merlin Moncure > On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin > 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.
Re: [GENERAL] Extended query protocol and exact types matches.
On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended query protocol and exact types matches.
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 > On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin > 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.
Re: [GENERAL] Extended query protocol and exact types matches.
On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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.