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.rowley@xxxxxxxxxxxxxxx> wrote:
On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo
<winanjaya.amijoyo@xxxxxxxxx> 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
