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=3ab8b7fa6f9ac2fb04096f8860261dc42d59a570which 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.