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