Hi David, thanks for your advise, as I am new with postgresql.. I try to use LOCK as below, but it does not returning pid? what I missed?
BEGIN TRANSACTION; LOCK TABLE test IN ACCESS EXCLUSIVE MODE; WITH s AS ( SELECT pid FROM test WHERE area = 'test4' ), i AS ( INSERT INTO test (area) SELECT 'test4' WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING pid ) UPDATE area SET last_update = CURRENT_TIMESTAMP WHERE pid = (SELECT pid FROM s UNION SELECT pid FROM i) RETURNING pid; COMMIT TRANSACTION; On Thu, May 16, 2019 at 4:25 PM David Rowley <david.row...@2ndquadrant.com> wrote: > On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo > <winanjaya.amij...@gmail.com> wrote: > > When record not found then insert and return pid value or if not found > then update based on pid and again return its pid. > > You could do something like: > > WITH s AS ( > SELECT pid FROM test WHERE area = 'test5' > ), i AS ( > INSERT INTO test (area) > SELECT 'test5' > WHERE NOT EXISTS (SELECT 1 FROM s) > RETURNING pid > ) > UPDATE area > SET last_update = CURRENT_TIMESTAMP > WHERE pid = (SELECT pid FROM s UNION SELECT pid FROM i) > RETURNING pid; > > but be warned, it's could all fall over as soon as you have concurrent > sessions running this at the same time. You could protect against > that by doing some advisory locking ( > https://www.postgresql.org/docs/11/explicit-locking.html#ADVISORY-LOCKS > ), or taking an access exclusive lock on "test". > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
