Re: [GENERAL] Trigger / constraint issue
On 12/06/2012 10:31 AM, Glenn Pierce wrote:
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
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 ?
Have you done this:
http://www.postgresql.org/docs/9.2/interactive/sql-alterrole.html
ALTER ROLE name [ IN DATABASE database_name ] SET
configuration_parameter { TO | = } { value | DEFAULT }
To check:
http://www.postgresql.org/docs/9.2/interactive/app-psql.html
\drds [ role-pattern [ database-pattern ] ]
Lists defined configuration settings. These settings can be
role-specific, database-specific, or both. role-pattern and
database-pattern are used to select specific roles and databases to
list, respectively. If omitted, or if * is specified, all settings are
listed, including those not role-specific or database-specific,
respectively.
The ALTER ROLE and ALTER DATABASE commands are used to define per-role
and per-database configuration settings.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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
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 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.00+00:00' AND timestamp < TIMESTAMP WITH
> TIME ZONE '2011-01-01 00:00:00.00+00:00' )) INHERITS (sensor_values);
>
> Trigger:
>
> IF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2010-10-01
> 00:00:00.00+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE
> '2011-01-01 00:00:00.00+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 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 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, '-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(), '-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(), '-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?
>>>
>>>
>>> --
Re: [GENERAL] Trigger / constraint issue
On 12/05/2012 02:24 PM, Glenn Pierce wrote:
The error I get is
new row for relation "sensor_values_2011q3" violates check constraint
"sensor_values_2011q3_timestamp_check"CONTEXT: SQL statement
"INSERT INTO sensor_values_2011q3 VALUES (NEW.*)"PL/pgSQL function
"sensor_values_timestamp_sensor_func_insert_trigger" line 32 at SQL
statement
I have printed the query that causes this error and it is
INSERT INTO sensor_values (timestamp, value, sensor_id) VALUES
('2011-06-30 23:00:00.001000+00:00', '0', '2103')
So the trigger has chosen the wrong child table to insert into ?
The funny thing is from psql this insert query works fine. ?
I am not sure why 2011-06-30 23:00:00.001000+00:00 is selected to go
into sensor_values_2011q3 and not sensor_values_2011q2
I suspect its due to UTC / BST as that date time is on the border of the
contraint. Either way I not sure why I get an error and
why does PSQL work ?
I would suspect UTC/BST also.
Do you have 'mod' logging enabled?
If so what does the INSERT from the Python script show for a time value?
If not can you log the output from the Python script to get the value?
Any suggestions / help would be great
Thanks
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger / constraint issue
Hi I wonder if someone can help me I am getting a bit confused about an
error I am getting.
I have a partitioned table called sensor_values which is partitioned on a
timestamp entry.
The parent and some of the child tables are defined like so
(The child tables are yearly quarters and in my actual code they span 15
years)
CREATE TABLE sensor_values (
id SERIAL PRIMARY KEY,
timestamp timestamp with time zone NOT NULL,
value real NOT NULL DEFAULT 'NaN',
sensor_id integer NOT NULL,
FOREIGN KEY (sensor_id) REFERENCES sensors(id)
);
CREATE TABLE sensor_values_2011q2 (CHECK ( timestamp >= TIMESTAMP WITH TIME
ZONE '2011-04-01 00:00:00.00+00:00' AND timestamp < TIMESTAMP WITH TIME
ZONE '2011-07-01 00:00:00.00+00:00' )) INHERITS (sensor_values);
CREATE TABLE sensor_values_2011q3 (CHECK ( timestamp >= TIMESTAMP WITH TIME
ZONE '2011-07-01 00:00:00.00+00:00' AND timestamp < TIMESTAMP WITH TIME
ZONE '2011-10-01 00:00:00.00+00:00' )) INHERITS (sensor_values);
I have a trigger to determine which table the insert will occur on.
Ie
IF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2011-04-01
00:00:00.00+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE
'2011-07-01 00:00:00.00+00:00' )
THEN INSERT INTO sensor_values_2011q2 VALUES (NEW.*);
ELSIF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2011-07-01
00:00:00.00+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE
'2011-10-01 00:00:00.00+00:00' )
THEN INSERT INTO sensor_values_2011q3 VALUES (NEW.*);
The trouble is I have a python script that inserts some values and I am
getting the following error on one timestamp
The error I get is
new row for relation "sensor_values_2011q3" violates check constraint
"sensor_values_2011q3_timestamp_check"CONTEXT: SQL statement "INSERT
INTO sensor_values_2011q3 VALUES (NEW.*)"PL/pgSQL function
"sensor_values_timestamp_sensor_func_insert_trigger" line 32 at SQL
statement
I have printed the query that causes this error and it is
INSERT INTO sensor_values (timestamp, value, sensor_id) VALUES ('2011-06-30
23:00:00.001000+00:00', '0', '2103')
So the trigger has chosen the wrong child table to insert into ?
The funny thing is from psql this insert query works fine. ?
I am not sure why 2011-06-30 23:00:00.001000+00:00 is selected to go into
sensor_values_2011q3 and not sensor_values_2011q2
I suspect its due to UTC / BST as that date time is on the border of the
contraint. Either way I not sure why I get an error and
why does PSQL work ?
Any suggestions / help would be great
Thanks
