Search Postgresql Archives

Re: Trigger / constraint issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



OK I have got it down to a simple test

#connect_string = 'dbname=bmos user=bmos'
connect_string = 'dbname=bmos user=postgres'

if __name__ == "__main__":
    conn = psycopg2.connect(connect_string)
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    cur.execute("INSERT INTO sensor_values (timestamp, value, sensor_id) " \
                "VALUES ('2010-09-30 23:00:00.084000+00:00', '99.8570022583', '21130')")

    conn.commit()

    cur.close()
    conn.close()
~                


When I connect with 'dbname=bmos user=bmos' everything works
but with 'dbname=bmos user=postgres'  it fails


Traceback (most recent call last):
  File "./tests/integrity_error.py", line 42, in <module>
    cur.execute("INSERT INTO sensor_values (timestamp, value, sensor_id) " \
  File "/usr/lib/python2.6/dist-packages/psycopg2/extras.py", line 118, in execute
    return _cursor.execute(self, query, vars)
psycopg2.IntegrityError: new row for relation "sensor_values_2010q4" violates check constraint "sensor_values_2010q4_timestamp_check"
CONTEXT:  SQL statement "INSERT INTO sensor_values_2010q4 VALUES ( $1 .*)"
PL/pgSQL function "sensor_values_timestamp_sensor_func_insert_trigger" line 25 at SQL statement


Why does the connecting user effect things ?


On 6 December 2012 16:34, Glenn Pierce <glennpierce@xxxxxxxxx> wrote:
so the issue comes down to this 

CREATE TABLE sensor_values_2010q4 (CHECK ( timestamp >= TIMESTAMP WITH TIME ZONE '2010-10-01 00:00:00.000000+00:00' AND timestamp < TIMESTAMP WITH TIME ZONE '2011-01-01 00:00:00.000000+00:00' )) INHERITS (sensor_values);

Trigger:

IF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2010-10-01 00:00:00.000000+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE '2011-01-01 00:00:00.000000+00:00' )
THEN INSERT INTO sensor_values_2010q4 VALUES (NEW.*);


Is there a way to check NEW.timestamp is correct repect to timezone ?


On 6 December 2012 16:18, Glenn Pierce <glennpierce@xxxxxxxxx> wrote:
I'm running 8.4
timestamps are passed as strings 

I found another timestamp that fails

2010-09-30 23:00:00.084000+00:00 UTC

this string  was created from the timestamp  1285887600.084000
ie  Thu, 30 Sep 2010 23:00:00  with added micro seconds

In my timezone BST which should not be used it would be
Fri Oct 01 2010 00:00:00 BST

'new row for relation "sensor_values_2010q4" violates check constraint "sensor_values_2010q4_timestamp_check"\nCONTEXT:  SQL statement "INSERT INTO sensor_values_2010q4 VALUES ( $1 .*)"\nPL/pgSQL function "sensor_values_timestamp_sensor_func_insert_trigger" line 25 at SQL statement\n'


So it must pass the trigger date check but then fail the table constraint.


Out of curiosity I also removed the milliseconds and that still failed

GMT ERROR:  new row for relation "sensor_values_2010q4" violates check constraint "sensor_values_2010q4_timestamp_check"
2012-12-06 16:16:11 GMT CONTEXT:  SQL statement "INSERT INTO sensor_values_2010q4 VALUES ( $1 .*)"
        PL/pgSQL function "sensor_values_timestamp_sensor_func_insert_trigger" line 25 at SQL statement
2012-12-06 16:16:11 GMT STATEMENT:  INSERT INTO sensor_values (timestamp, value, sensor_id) VALUES ('2010-09-30 23:00:00+00:00', '99.8570022583', '2113')






On 6 December 2012 15:11, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
On 12/06/2012 01:51 AM, Glenn Pierce wrote:
The reason you don't see datetime values is the data I am inserting  is
actually coming from the same table and I am selecting the timestamps
like so

"to_char(timestamp::timestamptz, 'YYYY-MM-DD HH24:MI:SS US TZ') AS time"

Which are the strings I use on the insert.




The log shows

LOG:  statement: INSERT INTO sensor_values (timestamp, value, sensor_id)
VALUES ('2011-06-30 23:00:00.001000+00:00', '0', '2103');


show timezone;  shows
TimeZone
----------
  UTC


I set UTC from the script as well as all my values should be stored
and received in UTC.


The queries look identical. It's completely bizarre ?

Well the thing I notice is the time zone is not being set. Given the to_char() format you have there should be a timezone abbreviation:

test=> select to_char(now(), 'YYYY-MM-DD HH24:MI:SS US TZ') AS time
test-> ;
              time
--------------------------------
 2012-12-06 07:05:17 752641 PST
(1 row)


test=> set time zone 'UTC';
SET
test=> select now();
              now
-------------------------------
 2012-12-06 15:07:05.435609+00
(1 row)

test=> select to_char(now(), 'YYYY-MM-DD HH24:MI:SS US TZ') AS time;
              time
--------------------------------
 2012-12-06 15:07:20 886646 UTC

(1 row)


What version of Postgres are you running?
What do the original timestamps look like?





--
Adrian Klaver
adrian.klaver@xxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux