wrong timezone precision using plpythonu trigger

From: maizi <maizi(at)lirmm(dot)fr>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: wrong timezone precision using plpythonu trigger
Date: 2010-10-10 16:54:32
Message-ID: 46342c7ba813177212e784a36033a4f7@lirmm.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jeff Davis 2010-10-10 18:48:54 Re: BUG #5702: pg fails to use a conditional index even the where clause matches the condition
Previous Message Netskin | Corin Langosch 2010-10-10 10:48:28 Re: BUG #5702: pg fails to use a conditional index even the where clause matches the condition