Thread

1. update and tcl/tk

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-12-29T21:04:53Z

   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 | gynco |    | 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 'gynco' 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 | gynco |    | 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=4919351 and lib like 'gynco' 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
   
   
   

2. Re: update and tcl/tk

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-01T23:45:21Z

   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=4919351 and lib like 'gynco' 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 'gynco'".  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
   
   

3. Re: update and tcl/tk

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-03T23:04:06Z

   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=4919351 and lib like 'gynco' 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=4919351 and lib like 'gynco' 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