Re: update and tcl/tk

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