Thread: update and tcl/tk

Bruno LEVEQUE (bruno.leveque@libertysurf.fr) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
update and tcl/tk

Long Description
Sometime, when I want update data I cannot with TCL/tk but I can directly.

I use Linux Slackware 7.1 kernel  2.2.16 and postgresql 7.0.2

Sample Code
A example :

set datestyle to 'postgres, european'; select * from cheque where num_cpte=8 and date='09-11-2000' and ok='' and
cred=0.0;

reading /tmp/psql.edit.100.14034

 num_cpte |    date    | nb_cheq |  lib   | ok | deb | cred
----------+------------+---------+--------+----+-----+------
        8 | 09-11-2000 | 4919351 | gynéco |    | 200 |    0
(1 row)

Now I do an update with TCL/TK

global Hote

set conn [pg_connect banque -host $Hote -port "5432"]

set res [pg_exec $conn "set datestyle to 'postgres, european'; update cheque set ok='x' where num_cpte=8 and
date='09-11-2000'and nb_cheq=4919351 and lib like 'gynéco' and ok='' and deb=200.00 and cred=0.0"] 

puts stdout "[pg_result $res -status -error]"

PGRES_COMMAND_OK

set datestyle to 'postgres, european'; select * from cheque where num_cpte=8 and date='09-11-2000' and ok='' and
cred=0.0;

reading /tmp/psql.edit.100.14034

 num_cpte |    date    | nb_cheq |  lib   | ok | deb | cred
----------+------------+---------+--------+----+-----+------
        8 | 09-11-2000 | 4919351 | gynéco |    | 200 |    0
(1 row)


If I update directly

set datestyle to 'postgres, european'; update cheque set ok='x' where num_cpte=8 and date='09-11-2000' and
nb_cheq=4919351and lib like 'gynéco' and ok='' and deb=200.00 and cred=0.0 


It's well.

I can update some other line with my Tcl/Tk request.

Is it a bug ?

No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> [ via libpgtcl, the given query updates no rows ]
> If I update directly
> set datestyle to 'postgres, european'; update cheque set ok='x' where num_cpte=8 and date='09-11-2000' and
nb_cheq=4919351and lib like 'gynéco' and ok='' and deb=200.00 and cred=0.0 
> It's well.

I am guessing that the critical point here is the presence of a
non-ASCII character in the clause "lib like 'gynéco'".  Does the
query work from Tcl as long as you specify only regular ASCII characters
in the LIKE phrase?

Assuming that it does, I think that you might be looking at a
mistranslation of Tcl's internal UTF-8 character set into the character
set used by the Postgres backend.  We had a recent report that that
translation didn't seem to be working right with recent Tcl releases,
but I have no details nor a fix at the moment.

What Tcl version are you using, anyway?

            regards, tom lane

Bruno LEVEQUE <bruno.leveque@libertysurf.fr> wrote:
> [ via libpgtcl, the given query updates no rows ]
> If I update directly
> set datestyle to 'postgres, european'; update cheque set ok='x' where num_cpte=8 and date='09-11-2000' and
nb_cheq=4919351and lib like 'gynéco' and ok='' and deb=200.00 and cred=0.0 
> It's well.

I initially guessed that libpgtcl was causing a character set
translation problem in the LIKE string, but examination of -d5 dump
output provided by Bruno shows that that theory was all wet.  The real
difference is in the date = '09-11-2000' clause.  The tcl/tk trace has
a date constant that corresponds to 2000-09-11, the psql trace a constant
corresponding to 2000-11-09.  The former corresponds to the way that
the input '09-11-2000' would be parsed in the default ISO datestyle,
whereas the latter corresponds to the way it'd be parsed in
Postgres/European datestyle.

But, you say, the Tcl script *is* issuing a SET DATESTYLE!  Well, what
it's actually doing is issuing two queries in a single query string:

query: set datestyle to 'postgres, european'; update cheque set ok='' where num_cpte=8 and date='09-11-2000' and
nb_cheq=4919351and lib like 'gynéco' and ok='x' and deb=200.00 and cred=0.0 

It turns out that by the time the SET command is executed, the
system has already parsed the whole querystring and turned it into
an internal parsetree --- including reduction of the date constant
to internal form.  So the date constant is interpreted in the initial
default datestyle, which is ISO.

In the psql case, even though you enter what seems to be exactly the
same thing, psql breaks up the line at the first semicolon and transmits
the SET and the UPDATE in separate query cycles.  So the problem does
not show up under psql.

This rather surprising behavior should be gone in 7.1, because of some
reorganization of the query processing pipeline that was done for
unrelated reasons.  In the meantime, the workaround is to issue the SET
in a separate command string before you send any queries that depend on
the SET to have been done before they are parsed.

            regards, tom lane