Re: False unique constraint violation (exception block)
От | whiplash |
---|---|
Тема | Re: False unique constraint violation (exception block) |
Дата | |
Msg-id | 51689CED.8060801@bss.org.ua обсуждение исходный текст |
Ответ на | Re: False unique constraint violation (exception block) (Jeff Janes <jeff.janes@gmail.com>) |
Список | pgsql-general |
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).
В списке pgsql-general по дате отправления: