Re: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table
От | postgresql2@realityexists.net |
---|---|
Тема | Re: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table |
Дата | |
Msg-id | 554FA13D.5020208@realityexists.net обсуждение исходный текст |
Ответ на | Re: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | pgsql-bugs |
That's odd! I just confirmed again that I get the exact same error with COMMIT (and no warnings). Are you able to run all the commands before the COMMIT successfully and get 1 row back from the SELECT? On 10/05/2015 7:47 PM, David G. Johnston wrote: > On Fri, Apr 24, 2015 at 9:57 AM, <postgresql2@realityexists.net > <mailto:postgresql2@realityexists.net>>wrote: > > The following bug has been logged on the website: > > Bug reference: 13148 > Logged by: Evan Martin > Email address: postgresql2@realityexists.net > <mailto:postgresql2@realityexists.net> > PostgreSQL version: 9.3.6 > Operating system: Windows 7 x64 SP1 > Description: > > I have a deferred EXCLUDE constraint on a derived table. Inside a > transaction I insert a new row that conflicts with an existing one > (so the > constraint would fail if it was immediate), delete the old row and > run an > unrelated UPDATE on the new row, then try to commit. I would > expect the > commit to succeed, since there is now no conflict, but it fails with > > ERROR: conflicting key value violates exclusion constraint > "uq_derived_timeslice_dup_time_ex" > SQL state: 23P01 > Detail: Key (feature_id, valid_time_begin, interpretation, > (COALESCE(sequence_number, (-1))))=(1, 2015-01-01 00:00:00, X, -1) > conflicts > with existing key (feature_id, valid_time_begin, interpretation, > (COALESCE(sequence_number, (-1))))=(1, 2015-01-01 00:00:00, X, -1). > > If I run the delete statement first it works. If I remove the > (seemingly > unrelated) update statement it also works. Reproducible under > PostgreSQL > 9.3.6 64-bit on Windows 7 and Postgresql 9.2.10 32-bit on Ubuntu > using the > script below. > > > -- **** One-off set-up **** > /* > -- DROP TABLE IF EXISTS base_timeslice CASCADE; > > CREATE TABLE base_timeslice > ( > timeslice_id integer NOT NULL, > feature_id integer NOT NULL, > valid_time_begin timestamp NOT NULL, > interpretation text NOT NULL, > sequence_number integer, > CONSTRAINT pk_base_timeslice PRIMARY KEY (timeslice_id) > ); > > CREATE TABLE derived_timeslice > ( > timeslice_id integer NOT NULL, > feature_id integer NOT NULL, > name text NOT NULL, > CONSTRAINT pk_derived_timeslice PRIMARY KEY (timeslice_id), > CONSTRAINT uq_derived_timeslice_dup_time_ex EXCLUDE > USING btree (feature_id WITH =, valid_time_begin WITH =, > interpretation > WITH =, (COALESCE(sequence_number::integer, (-1))) WITH =) > DEFERRABLE INITIALLY DEFERRED > ) > INHERITS (base_timeslice); > > INSERT INTO derived_timeslice (timeslice_id, feature_id, > valid_time_begin, > interpretation, name) > VALUES (51, 1, '2015-01-01', 'X', 'Test'); > */ > > -- **** Repro **** > > BEGIN; > > -- Insert row that violates deferred constraint > INSERT INTO derived_timeslice (timeslice_id, feature_id, > valid_time_begin, > interpretation, name) > VALUES (52, 1, '2015-01-01', 'X', 'Test'); > > -- Delete the old row - now there should be no more conflict > DELETE FROM derived_timeslice WHERE timeslice_id = 51; > > -- Problem doesn't occur without an UPDATE statement > UPDATE derived_timeslice SET name = 'Updated' WHERE timeslice_id = 52; > > -- This confirms there is only 1 row - no conflict > SELECT timeslice_id, valid_time_begin FROM derived_timeslice WHERE > feature_id = 1; > > --COMMIT; > SET CONSTRAINTS ALL IMMEDIATE; -- Enfore constraint - error occurs > here > > ROLLBACK; > > > â I can provide a limited affirmation that the above example is > problematic on 9.3 > > Changing "SET CONSTRAINTS ALL IMMEDIATE" to "COMMIT;" results in a > warning that there is no currently active transaction and the > transaction itself appears to have been rolled back. > > David J.â >
В списке pgsql-bugs по дате отправления: