tcl-tdbc

[Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[<to...@tu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Since Postgresql about 8.3 (maybe its 8.2, I'm not quite sure), type
guessing has become more stringent [1]. This has a nasty effect on prepared
statements as TDBC usually does them:

 |  tomas@floh:~$ tclsh8.6
 |  % package require tdbc
 => 1.0b14
 |  % package require tdbc::postgres
 => 1.0b14
 |  % set dbconn [tdbc::postgres::connection new -database husch -host localhost -user husch -password liuwah3U]
 => ::oo::Obj9
 |  % set stmt [$dbconn prepare {select id, b_name from besitzer where b_name = :foo}]
 => could not determine data type of parameter $1

What happens is that PostgreSQL sets the type of the parameter to
"unspecified" and has not enough hints to make better guesses. With psql
you can see something similar:

 |  tomas@floh:~$ psql husch
 => psql (8.4.5)
 => Type "help" for help.
 |  
 |  husch=# prepare foo as select id, b_name from besitzer where b_name = $1;
 => ERROR:  could not determine data type of parameter $1

But this, for example, works:

 |  husch=# prepare foo as select id, b_name from besitzer where b_name = '$1';
 |  PREPARE

I have an ugly work-around for that at the moment (concatenating strings
with '' as in SELECT ... WHERE b_name = :foo || ''. Traditional
PostgreSQL casting (à la :foo::VARCHAR) is even uglier and dies a
horrible death because the driver tries to make another parameter out of
that).

I've read about paramtype, but I'm stuck on how to use that *before*
I've got a statement object. Any hints?

Thanks
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFNoqRCBcgs9XrR2kYRAlHLAJ43QTXMk678Prr3+ldbGOvV3INeZgCeL8FQ
rONBBI5esDXFYTO3m5QOC4Q=
=aJpL
-----END PGP SIGNATURE-----

Re: [Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[Kevin K. <kk...@ny...>]

On 04/11/2011 02:48 AM, to...@tu... wrote:
> What happens is that PostgreSQL sets the type of the parameter to
> "unspecified" and has not enough hints to make better guesses. With psql
> you can see something similar:
>
>   |  tomas@floh:~$ psql husch
>   =>  psql (8.4.5)
>   =>  Type "help" for help.
>   |
>   |  husch=# prepare foo as select id, b_name from besitzer where b_name = $1;
>   =>  ERROR:  could not determine data type of parameter $1
>
> But this, for example, works:
>
>   |  husch=# prepare foo as select id, b_name from besitzer where b_name = '$1';
>   |  PREPARE
>
> I have an ugly work-around for that at the moment (concatenating strings
> with '' as in SELECT ... WHERE b_name = :foo || ''. Traditional
> PostgreSQL casting (à la :foo::VARCHAR) is even uglier and dies a
> horrible death because the driver tries to make another parameter out of
> that).

Can you give me the schema for the 'besitzer' table?

I'm not seeing the same phenomenon that you are.  I *do* get it
for a statement like:

prepare foo as select $1

where there is actually no information about $1's data type.  (Please
log a bug at tdbc.tcl.tk about the cast syntax :foo::VARCHAR -- that's
an easy change.)

But I don't get the error for statements like

select id from people where name = $1

In that case, it correctly infers the 'text' type for 'name' and
transfers it successfully.

For what it's worth, my PostgreSQL server reports 8.4.7 and not 8.4.5,
so maybe there was a bug fixed along the way?

Are you set up to build tdbc? If I give you source patches, can you
test them? I have a few ideas for potential fixes.

-- 
73 de ke9tv/2, Kevin

Re: [Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[<to...@tu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Apr 11, 2011 at 10:48:09PM -0400, Kevin Kenny wrote:
> On 04/11/2011 02:48 AM, to...@tu... wrote:
>> What happens is that PostgreSQL sets the type of the parameter to
>> "unspecified" [...]

> Can you give me the schema for the 'besitzer' table?

Yes, sorry. Here it is:

    CREATE TABLE besitzer (
      id SERIAL   PRIMARY KEY,
      b_name      VARCHAR
    );

...just cut down to bare minimum (it's wht I used in preparing this
example, though)-

> I'm not seeing the same phenomenon that you are.  I *do* get it
> for a statement like:
>
> prepare foo as select $1
>
> where there is actually no information about $1's data type.  (Please
> log a bug at tdbc.tcl.tk about the cast syntax :foo::VARCHAR -- that's
> an easy change.)
>
> But I don't get the error for statements like
>
> select id from people where name = $1
>
> In that case, it correctly infers the 'text' type for 'name' and
> transfers it successfully.

Aha. So it seems it'd work if b_name above were TEXT instead of VARCHAR
[runs away for testing...]

HAH. That's it. Declaring the colum as TEXT instead of VARCHAR does the
trick. This one doesn't make trouble :-)

So a workaround would be to use TEXT instead of VARCHAR. They are the
same for PostgreSQL anyway.

> For what it's worth, my PostgreSQL server reports 8.4.7 and not 8.4.5,
> so maybe there was a bug fixed along the way?

Hm. I don't know. What's interesting is that psql doesn't complain in
this case either:

 |  tomas@floh:~$ psql foo
 => psql (8.4.5)
 => Type "help" for help.
 |  
 |  foo=# prepare muh as select id, b_name from blub where b_name = $1;
 => PREPARE

(sorry for the name changes :-/

> Are you set up to build tdbc? If I give you source patches, can you
> test them? I have a few ideas for potential fixes.

I'd glad to help in any way, I'm a big fan of Tdbc. On the other hand,
it's strange VARCHAR and TEXT behave so differently, so the right thing
might be to take it to the PostgreSQL folks. I'll keep you in the loop.

Thanks for looking into it
- -- tomás
>
> -- 
> 73 de ke9tv/2, Kevin
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFNo+u/Bcgs9XrR2kYRAg51AJ9/N/kqIKBuKAYV5qpPf6XkzErtMACUDIA+
p+yJptjlnCjktLho7CJ49Q==
=S3WK
-----END PGP SIGNATURE-----

Re: [Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[Kevin K. <kk...@ny...>]

On 04/12/2011 02:05 AM, to...@tu... wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Mon, Apr 11, 2011 at 10:48:09PM -0400, Kevin Kenny wrote:
>> On 04/11/2011 02:48 AM, to...@tu... wrote:
>>> What happens is that PostgreSQL sets the type of the parameter to
>>> "unspecified" [...]
>
>> Can you give me the schema for the 'besitzer' table?
>
> Yes, sorry. Here it is:
>
>      CREATE TABLE besitzer (
>        id SERIAL   PRIMARY KEY,
>        b_name      VARCHAR
>      );
>
> ...just cut down to bare minimum (it's wht I used in preparing this
> example, though)-

This is feeling more and more like a PostgreSQL bug.  Here is what I'm
doing from the command line, and I'm not seeing your issue. Are you
doing something different?

$ psql tdbc_test
psql (8.4.7)
Type "help" for help.

tdbc_test=# create table example(id serial primary key, name varchar);
NOTICE:  CREATE TABLE will create implicit sequence "example_id_seq" for 
serial column "example.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"example_pkey" for table "example"
CREATE TABLE
tdbc_test=# prepare foo as select id, name from example where name = $1;
PREPARE
tdbc_test=# insert into example(name) values('fred');
INSERT 0 1
tdbc_test=# execute foo('fred');
  id | name
----+------
   1 | fred
(1 row)

tdbc_test=# drop table example;
DROP TABLE
tdbc_test=# \q


-- 
73 de ke9tv/2, Kevin

Re: [Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[<to...@tu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Apr 12, 2011 at 08:50:35AM -0400, Kevin Kenny wrote:
> On 04/12/2011 02:05 AM, to...@tu... wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On Mon, Apr 11, 2011 at 10:48:09PM -0400, Kevin Kenny wrote:
>>> On 04/11/2011 02:48 AM, to...@tu... wrote:
>>>> What happens is that PostgreSQL sets the type of the parameter to
>>>> "unspecified" [...]
>>
>>> Can you give me the schema for the 'besitzer' table?
>>
>> Yes, sorry. Here it is:
>>
>>      CREATE TABLE besitzer (
>>        id SERIAL   PRIMARY KEY,
>>        b_name      VARCHAR
>>      );
>>
>> ...just cut down to bare minimum (it's wht I used in preparing this
>> example, though)-
>
> This is feeling more and more like a PostgreSQL bug.  Here is what I'm
> doing from the command line, and I'm not seeing your issue. Are you
> doing something different?

I guess the difference is in the PostgreSQL version. I took the question
to the PostgreSQL general mailing list, in any case (I'd have to install
a newer PostgreSQL here to check my guess and are a bit tight on time
the next couple of days, but I'll do eventually, for sure).

> $ psql tdbc_test
> psql (8.4.7)
> Type "help" for help.
>
> tdbc_test=# create table example(id serial primary key, name varchar);
> NOTICE:  CREATE TABLE will create implicit sequence "example_id_seq" for 
> serial column "example.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
> "example_pkey" for table "example"
> CREATE TABLE
> tdbc_test=# prepare foo as select id, name from example where name = $1;
> PREPARE
> tdbc_test=# insert into example(name) values('fred');
> INSERT 0 1
> tdbc_test=# execute foo('fred');
>  id | name
> ----+------
>   1 | fred
> (1 row)
>
> tdbc_test=# drop table example;
> DROP TABLE
> tdbc_test=# \q

No, that's exactly the example which fails on my version (8.4.5). Since
it's reproducible with psql alone and no tdbc, I'd say it's a missing
entry in pg_cast which got added between 8.4.5 and 8.4.7. Tdbc is
innocent :-)

Thanks, and I'll report back any insights.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFNpFHiBcgs9XrR2kYRApg/AJ46Cjxi8D8KuOq8RuymQUGxwkRRdACcCLe6
RePbE/bUCQgiM4dsmOtvBnE=
=24XX
-----END PGP SIGNATURE-----

Re: [Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[<to...@tu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Apr 12, 2011 at 09:16:00AM -0400, Kevin Kenny wrote:
> On 04/12/2011 09:21 AM, to...@tu... wrote:

[...]

>> Thanks, and I'll report back any insights.

Turns out "something" corrupted my template1 (this "something" being
most probably myself :-/

Thus, there were some bogus entries in pg_cast in all databases I was
using to do the tests.

I'm very sorry for the noise.

Thanks and regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFNpUTlBcgs9XrR2kYRAlp0AJ9VuRm+dbx4P95hUNeG5SMofLqqKgCdGlrx
cEok6CSqGGj4AKJIZU3yTzs=
=Qx12
-----END PGP SIGNATURE-----