Обсуждение: 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