Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger / constraint issue

Trigger / constraint issue

From
Glenn Pierce
Date:
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"<br />CONTEXT:  SQL statement "INSERT INTO sensor_values_2011q3 VALUES (NEW.*)"<br />PL/pgSQL function "sensor_values_timestamp_sensor_func_insert_trigger" line 32 at SQL statement<br /><br />

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

Re: Trigger / constraint issue

From
Adrian Klaver
Date:
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"<br />CONTEXT:  SQL statement
> "INSERT INTO sensor_values_2011q3 VALUES (NEW.*)"<br />PL/pgSQL function
> "sensor_values_timestamp_sensor_func_insert_trigger" line 32 at SQL
> statement<br /><br />
>
> 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

Re: Trigger / constraint issue

From
Glenn Pierce
Date:
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:
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



Re: Trigger / constraint issue

From
Adrian Klaver
Date:
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