Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





----------------------------------------
> Date: Wed, 27 Jul 2011 16:16:48 -0500
> Subject: Re:  WITH x AS (...) and visibility in UPDATE
> From: mmoncure@xxxxxxxxx
> To: peterv861908@xxxxxxxxxxx
> CC: pgsql-general@xxxxxxxxxxxxxx
>
> On Wed, Jul 27, 2011 at 4:03 PM, Peter V <peterv861908@xxxxxxxxxxx> wrote:
> >> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@xxxxxxxxxxx> 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 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.
 		 	   		  
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux