Re: Returning empty on insert

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(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo
> <winanjaya(dot)amijoyo(at)gmail(dot)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
>