Strange/Correct? behavior of SELECT FOR UPDATE

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(at)linuxbox(dot)cz
-------------------------------------