Thread: WITH x AS (...) and visibility in UPDATE

WITH x AS (...) and visibility in UPDATE

From
Peter V
Date:
Hello all,

I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.

drop table if exists t;
create table t
(
    identifier   serial,
    title        text
);

with c as
(
    insert into t (title) values ('old') returning *
)
update t set title = 'new' from c where t.identifier = c.identifier;

select * from t;

Can someone explain why this returns 'old' instead of 'new'? Is the new row not yet visible when the update is
evaluated?

Thanks.


Re: WITH x AS (...) and visibility in UPDATE

From
Merlin Moncure
Date:
On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@hotmail.com> wrote:
>
> Hello all,
>
> I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.
>
> drop table if exists t;
> create table t
> (
>     identifier   serial,
>     title        text
> );
>
> with c as
> (
>     insert into t (title) values ('old') returning *
> )
> update t set title = 'new' from c where t.identifier = c.identifier;
>
> select * from t;
>
> Can someone explain why this returns 'old' instead of 'new'? Is the new row not yet visible when the update is
evaluated?

because the update statement isn't doing anything.  (you could have
confirmed this by adding 'returning *' to the update.

While the 'from c' is working, you can't join back to t yet because
the statement hasn't resolved. here's a reduced form of your problem:

postgres=# with c as
(
    insert into t (title) values ('old') returning *
) select * from t join c using (identifier);

The join fails because at the time it happens t isn't yet populated.

merlin

Re: WITH x AS (...) and visibility in UPDATE

From
Peter V
Date:


----------------------------------------
> Date: Wed, 27 Jul 2011 15:58:04 -0500
> Subject: Re: [GENERAL] WITH x AS (...) and visibility in UPDATE
> From: mmoncure@gmail.com
> To: peterv861908@hotmail.com
> CC: pgsql-general@postgresql.org
>
> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@hotmail.com> wrote:
> >
> > Hello all,
> >
> > I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.
> >
> > drop table if exists t;
> > create table t
> > (
> >     identifier   serial,
> >     title        text
> > );
> >
> > with c as
> > (
> >     insert into t (title) values ('old') returning *
> > )
> > update t set title = 'new' from c where t.identifier = c.identifier;
> >
> > select * from t;
> >
>
 > Can someone explain why this returns 'old' instead of 'new'? Is
the new row not yet visible when the update is evaluated?
>
> because the update statement isn't doing anything. (you could have
> confirmed this by adding 'returning *' to the update.
>
> While the 'from c' is working, you can't join back to t yet because
> the statement hasn't resolved. here's a reduced form of your problem:
>
> postgres=# with c as
> (
> insert into t (title) values ('old') returning *
> ) select * from t join c using (identifier);
>
> The join fails because at the time it happens t isn't yet populated.
>
> merlin



This makes sense. I thought that the insert was evaluated first, before the join is resolved. This isn't the case
apparently.



Is there another way to force this? That is, without extracting it to two statements ;)



Thanks.


Re: WITH x AS (...) and visibility in UPDATE

From
Merlin Moncure
Date:
On Wed, Jul 27, 2011 at 4:03 PM, Peter V <peterv861908@hotmail.com> wrote:
>> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@hotmail.com> wrote:
>> >
>> > Hello all,
>> >
>> > I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.
>> >
>> > drop table if exists t;
>> > create table t
>> > (
>> >     identifier   serial,
>> >     title        text
>> > );
>> >
>> > with c as
>> > (
>> >     insert into t (title) values ('old') returning *
>> > )
>> > update t set title = 'new' from c where t.identifier = c.identifier;
>> >
>> > select * from t;
>> >
>>
>  > Can someone explain why this returns 'old' instead of 'new'? Is
> the new row not yet visible when the update is evaluated?
>>
>> because the update statement isn't doing anything. (you could have
>> confirmed this by adding 'returning *' to the update.
>>
>> While the 'from c' is working, you can't join back to t yet because
>> the statement hasn't resolved. here's a reduced form of your problem:
>>
>> postgres=# with c as
>> (
>> insert into t (title) values ('old') returning *
>> ) select * from t join c using (identifier);
>>
>> The join fails because at the time it happens t isn't yet populated.
>>
>> merlin
>
>
>
> This makes sense. I thought that the insert was evaluated first, before the join is resolved. This isn't the case
apparently.
>
> Is there another way to force this? That is, without extracting it to two statements ;)

not in the exact sense you were trying.  what is it you are trying to
do in general? I'm having trouble understanding your use-case.

merlin

Re: WITH x AS (...) and visibility in UPDATE

From
Peter V
Date:


----------------------------------------
> Date: Wed, 27 Jul 2011 16:16:48 -0500
> Subject: Re: [GENERAL] WITH x AS (...) and visibility in UPDATE
> From: mmoncure@gmail.com
> To: peterv861908@hotmail.com
> CC: pgsql-general@postgresql.org
>
> On Wed, Jul 27, 2011 at 4:03 PM, Peter V <peterv861908@hotmail.com> wrote:
> >> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@hotmail.com> wrote:
> >> >
> >> > Hello all,
> >> >
> >> > I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.
> >> >
> >> > drop table if exists t;
> >> > create table t
> >> > (
> >> >     identifier   serial,
> >> >     title        text
> >> > );
> >> >
> >> > with c as
> >> > (
> >> >     insert into t (title) values ('old') returning *
> >> > )
> >> > update t set title = 'new' from c where t.identifier = c.identifier;
> >> >
> >> > select * from t;
> >> >
> >>
> >  > Can someone explain why this returns 'old' instead of 'new'? Is
> > the new row not yet visible when the update is evaluated?
> >>
> >> because the update statement isn't doing anything. (you could have
> >> confirmed this by adding 'returning *' to the update.
> >>
> >> While the 'from c' is working, you can't join back to t yet because
> >> the statement hasn't resolved. here's a reduced form of your problem:
> >>
> >> postgres=# with c as
> >> (
> >> insert into t (title) values ('old') returning *
> >> ) select * from t join c using (identifier);
> >>
> >> The join fails because at the time it happens t isn't yet populated.
> >>
> >> merlin
> >
> >
> >
> > This makes sense. I thought that the insert was evaluated first, before the join is resolved. This isn't the case
apparently.
> >
> > Is there another way to force this? That is, without extracting it to two statements ;)
>
> not in the exact sense you were trying. what is it you are trying to
> do in general? I'm having trouble understanding your use-case.
>

I want to apply updates on a copy of a row, instead on the row itself. The queries are above were simplied to
demonstratethe problem. 

So basically I want to do:

1) create the copy of the row and return the identifier
2) apply updates on the new row identified by the identifier returned in step 1

If possible, I want to write this in a single command, to avoid overhead and mistakes.

I tried writing a rewrite rule or before trigger, but it becomes quickly a mess to avoid infinite loops.

Any ideas are welcome. Thanks.


Re: WITH x AS (...) and visibility in UPDATE

From
Jack Christensen
Date:
On 7/27/2011 4:22 PM, Peter V wrote:
> I want to apply updates on a copy of a row, instead on the row itself.
> The queries are above were simplied to demonstrate the problem.
> So basically I want to do:
>
> 1) create the copy of the row and return the identifier
> 2) apply updates on the new row identified by the identifier returned in step 1
>
> If possible, I want to write this in a single command, to avoid overhead and mistakes.
>
> I tried writing a rewrite rule or before trigger, but it becomes quickly a mess to avoid infinite loops.
>
> Any ideas are welcome. Thanks.
>
Maybe I'm totally missing something, but why insert a copy and then
update instead of directly insert a mutated copy?

Something like:
INSERT INTO t (foo, bar) SELECT 'my new foo', t.bar FROM t WHERE id=123;

Wouldn't the above construction let you make a new row with some new
values and some copied values?

--
Jack Christensen
jackc@hylesanderson.edu


Re: WITH x AS (...) and visibility in UPDATE

From
David Johnston
Date:
> 1) create the copy of the row and return the identifier
> 2) apply updates on the new row identified by the identifier returned in step 1
>
> If possible, I want to write this in a single command, to avoid overhead and mistakes.
>
> I tried writing a rewrite rule or before trigger, but it becomes quickly a mess to avoid infinite loops.
>
> Any ideas are welcome. Thanks.
>
>
Insert into table (cols)
Select ... From table;

As mentioned previously just modify the original row before inserting it into the table as a new record.

David J.