Re: Trigger / constraint issue

[Glenn Pierce <glennpierce@xxxxxxxxx>]

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