Re: Trigger / constraint issue

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(at)gmail(dot)com> 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(at)gmail(dot)com> 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(dot)klaver(at)gmail(dot)com> 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(dot)klaver(at)gmail(dot)com
>>>
>>
>>
>