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.kla...@aklaver.com
<mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
--
Adrian Klaver
adrian.kla...@aklaver.com
