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: ser...@linuxbox.cz -------------------------------------
