Thread: False unique constraint violation (exception block)

Hello.

This is simple example for describe my problem.

I created a table:

CREATE TABLE table0
(
     id serial NOT NULL,
     field0 integer,
     field1 text
);

I created an unique index for this table:

CREATE UNIQUE INDEX idx_table0_unique
ON table0 ( field0, coalesce ( field1, 'INDEX_COLUMN_NULL' ) );

I created function for insert only unique record (part of code):

BEGIN
     INSERT INTO table0 ( field0, field1 ) VALUES ( p_field0, p_field1 )
RETURNING id INTO v_table0_id;
EXCEPTION WHEN unique_violation THEN
     SELECT id FROM table0 WHERE field0 = p_field0 AND field1 = p_field1;
END;

I use this function for add data to table and sometimes I getting false
unique violation (in block above). Why?

Thanks for replay.

whiplash <whiplash@bss.org.ua> wrote:

> sometimes I getting false unique violation

How do you know that they are false?

What version of PostgreSQL is this?

Can you create a small self-contained test case that demonstrates
the issue?  (The snippets you provided probably excluded the cause
of the problem.)

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

PostgreSQL version:
PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

OS:
Ubuntu 12.10
Linux machine0 3.5.0-26-generic #42-Ubuntu SMP Fri Mar 8 23:18:20 UTC
2013 x86_64 x86_64 x86_64 GNU/Linux

As for my case, I use this function as function in INSERT-rule of VIEW
vw_table0 (CREATE VIEW vw_table0 AS SELECT * FROM table0). For debug, I
output field 'id':

p_row (parameter of this function) - ROW of VIEW vw_table0

...

p_field0 := p_row.field0;
p_field1 := p_row.field1;
v_table0_id := NULL;

BEGIN
      INSERT INTO table0 ( field0, field1 )
      VALUES ( p_field0, p_field1 )
      RETURNING id INTO v_table0_id;
EXCEPTION WHEN unique_violation THEN
      SELECT id
      INTO v_table0_id
      FROM table0
      WHERE field0 = p_field0 AND
            field1 = p_field1;
END;

RAISE NOTICE 'id = %', v_table0_id;

...

I think, for any input data (values of p_row.field0 and p_row.field1) I
should getting v_table0_id != NULL.

But I try this code (many times):

DELETE FROM vw_table0 WHERE id = ( SELECT id FROM vw_table0 WHERE field0
= 0 AND field1 = '0' );
INSERT INTO vw_table0 ( field0, field1 ) VALUES ( 0, '0' );

and sometimes getting notice:

id = <NULL>

because (after debugging) exception of unique violation and following
SELECT id INTO v_table0_id returning NULL. That is extended description
of my trouble.

P.S. This is synthetic example, real table is big (in columns and in data).

I found error in my DB - sequence for column id had value less "SELECT
max ( id ) FROM table0" (sequence throwed unique violation exception
too). I set valid value for sequence and issue was resolved.