Hi,
we have some question about behavior SELECT FOR UPDATE. We want find record with open bounds tstzrange, close it a insert new open. We use SELECT FOR UPDATE in function, but sometimes 2rows inserted. I show this on simple example with integer data type. Here is:
--tested on postgresql 9.5.0
CREATE TABLE test(x int);
INSERT INTO test VALUES (1);
-------------------------------------------------------------------------
--transaction1
BEGIN;
SELECT * FROM test WHERE x=1 FOR UPDATE;
x
---
1
(1 row)
UPDATE test SET x=2 WHERE x=1;
--UPDATE 1
INSERT INTO test VALUES (1);
--INSERT 0 1
SELECT * FROM test ;
x
---
2
1
(2 rows)
------------------------------------------------------------------
--transaction2
BEGIN;
SELECT * FROM test WHERE x=1 FOR UPDATE; --here transaction hang, thats what we want...
---------------------------------------------------------------------
--transaction1
COMMIT;
--------------------------------------------------------------------
--transaction2
--now lock released
SELECT * FROM test WHERE x=1 FOR UPDATE;
x
---
(0 row)
-- but we cant see inserted row with value 1, only updated records can we see
-- so our function here insert new row with value 1, becouse don't know about about existing row
-- if we tray repeat select now we can see row that was inserted by transaction1
SELECT * FROM test WHERE x=1;
x
---
1
(1 row)
-------------------------------------------------------------------------
We try prevent this situation, i know we can use EXCLUDE index on tstzrange column, but transaction2 rollback or we can use LOCK TABLE test IN EXCLUSIVE MODE - this working but locks whole table or we need ask table again with SELECT FOR UPDATE - some double check before insert ...Is there any other way how to close tstzrange with minimum locks?
Its correct behavior or not?
Thanks
David Turoň
--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava
tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz
mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------