From: | Winanjaya Amijoyo <winanjaya(dot)amijoyo(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Returning empty on insert |
Date: | 2019-05-16 14:25:51 |
Message-ID: | CAAHo4LNoRwb_Hoa2L0ciuJrSBRVWppfi+df6MFz1YJs6v2eUGg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
see enclosed screenshot..
I thought, the record still locked that's why it returns empty..
On Thu, May 16, 2019 at 9:21 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
Attachment | Content-Type | Size |
---|---|---|
|
image/png | 39.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Winanjaya Amijoyo | 2019-05-16 14:26:33 | Re: Returning empty on insert |
Previous Message | Adrian Klaver | 2019-05-16 14:20:58 | Re: Returning empty on insert |