On 5/16/19 7:25 AM, Winanjaya Amijoyo wrote: > see enclosed screenshot.. > > I thought, the record still locked that's why it returns empty..
I'm not sure I believe that screenshot. For one I don't know why it showing the area and last_update as they are not being returned?
Try the query in psql and see what happens. As an example:
update check_test set fld_1 = '67' where id =1 returning id; id ---- 1 (1 row)
UPDATE 1
> > On Thu, May 16, 2019 at 9:21 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 5/16/19 7:10 AM, Winanjaya Amijoyo wrote: > > 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? > > I'm not sure which pid you are referring to, the INSERT or UPDATE or > both? > > Can you show the output of the query? > > > > 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; > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> >
