Trigger / constraint issue

Started by Glenn Pierceover 13 years ago4 messagesgeneral

glennpierce@gmail.com

over 13 years ago

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.000000+00:00' AND timestamp < TIMESTAMP WITH TIME
ZONE '2011-07-01 00:00:00.000000+00:00' )) INHERITS (sensor_values);

CREATE TABLE sensor_values_2011q3 (CHECK ( timestamp >= TIMESTAMP WITH TIME
ZONE '2011-07-01 00:00:00.000000+00:00' AND timestamp < TIMESTAMP WITH TIME
ZONE '2011-10-01 00:00:00.000000+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.000000+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE
'2011-07-01 00:00:00.000000+00:00' )
THEN INSERT INTO sensor_values_2011q2 VALUES (NEW.*);
ELSIF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2011-07-01
00:00:00.000000+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE
'2011-10-01 00:00:00.000000+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

Adrian Klaver

adrian.klaver@aklaver.com

over 13 years ago

In reply to: Glenn Pierce (#1)

Re: 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.klaver@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

Glenn Pierce

glennpierce@gmail.com

over 13 years ago

In reply to: Glenn Pierce (#1)

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@gmail.com> wrote:

Show quoted text

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@gmail.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.klaver@gmail.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.klaver@gmail.com

Import Notes

Reply to msg id not found: CAM5ipV_dR383g9MW5YMtLExKWDRc7QuuZ--KEnmAfFXKCNRUHg@mail.gmail.com

Adrian Klaver

adrian.klaver@aklaver.com

over 13 years ago

In reply to: Glenn Pierce (#3)

Re: 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 <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 ?

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.klaver@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