Обсуждение: wrong timezone precision using plpythonu trigger

Hello,

I am using some code since postgres 8 and testing it on postgresql 9.
Here are the informations about the packages and system used:

# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux unstable (sid)
Release:        unstable
Codename:       sid
# dpkg -l | grep postgres
ii  postgresql-9.0                       9.0.1-1
  object-relational SQL database, version 9.0 server
[...]
ii  postgresql-plpython-9.0              9.0.1-1
  PL/Python procedural language for PostgreSQL 9.0


If I create a database using the following code:

-- ========================
CREATE LANGUAGE plpythonu ;

CREATE FUNCTION wrong() RETURNS trigger AS $wrong$
from mx import DateTime

TD['new']['modif_time'] = DateTime.now()
return 'MODIFY'
$wrong$ LANGUAGE plpythonu;

CREATE TABLE pb ( a TEXT, modif_time TIMESTAMP(0) WITHOUT TIME ZONE ) ;

CREATE TRIGGER wrong BEFORE UPDATE ON pb FOR EACH ROW EXECUTE PROCEDURE
wrong() ;
-- ========================

The following code

-- ========================
INSERT INTO pb VALUES ( 'a', now() ) ;
SELECT * FROM pb ;
UPDATE pb SET a = 'b' ;
SELECT * FROM pb ;
-- ========================

produces this result:

#========================
INSERT 0 1
 a |     modif_time
---+---------------------
 a | 2010-10-10 18:30:30
(1 ligne)

UPDATE 1
 a |       modif_time
---+------------------------
 b | 2010-10-10 18:30:29.74
(1 ligne)
#========================

notice the ".74" and the fact that in this example the modif_time of the
modified tuple is earlier than the preceding one!

Best regards

Joël

maizi <maizi@lirmm.fr> writes:
> CREATE LANGUAGE plpythonu ;

> CREATE FUNCTION wrong() RETURNS trigger AS $wrong$
> from mx import DateTime

> TD['new']['modif_time'] = DateTime.now()
> return 'MODIFY'
> $wrong$ LANGUAGE plpythonu;

> CREATE TABLE pb ( a TEXT, modif_time TIMESTAMP(0) WITHOUT TIME ZONE ) ;

> CREATE TRIGGER wrong BEFORE UPDATE ON pb FOR EACH ROW EXECUTE PROCEDURE
> wrong() ;

> INSERT INTO pb VALUES ( 'a', now() ) ;
> SELECT * FROM pb ;
> UPDATE pb SET a = 'b' ;
> SELECT * FROM pb ;

> produces this result:

> #========================
> INSERT 0 1
>  a |     modif_time
> ---+---------------------
>  a | 2010-10-10 18:30:30
> (1 ligne)

> UPDATE 1
>  a |       modif_time
> ---+------------------------
>  b | 2010-10-10 18:30:29.74
> (1 ligne)
> #========================

> notice the ".74" and the fact that in this example the modif_time of the
> modified tuple is earlier than the preceding one!

It looks to me like this used to work and got broken by this patch:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3ab8b7fa6f9ac2fb04096f8860261dc42d59a570

which lobotomized plpython to not care about passing the right typmod to
I/O functions.  This is a regression, and it's particularly annoying
because the other three standard PLs get this case right.  Peter?

            regards, tom lane

On mån, 2010-10-11 at 00:13 -0400, Tom Lane wrote:
> It looks to me like this used to work and got broken by this patch:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3ab8b7fa6f9ac2fb04096f8860261dc42d59a570
>
> which lobotomized plpython to not care about passing the right typmod
> to
> I/O functions.  This is a regression, and it's particularly annoying
> because the other three standard PLs get this case right.  Peter?

Never thought of that, typmods have a tendency to get lost somehow. :-/
Fix looks good; also good to get rid of the useless PLyTypeInfo
argument.