Writeable CTEs
in [PgSql]
|
Prev: We no longer have a fallback for machines withoutworking int64
Next: pgsql: Get rid of the need for manualmaintenance of the initial
From: Greg Stark on 5 Jan 2010 12:21 On Tue, Jan 5, 2010 at 4:42 PM, Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> wrote: > => with t as (delete from foo returning *) > -> insert into bar > -> select * from t; > INSERT 0 2 > > It correctly reports 2 affected rows (one deleted and one inserted), but is > this the answer we want? It doesn't seem all that useful to know the total > amount of affected rows. My first thought is that the number should correspond to the INSERT. It didn't INSERT two rows so it seems wrong. More importantly in a case like with t as (delete from foo returning *) select * from t where x=? applications will almost certainly expect the number to match the actual number of rows returned and may well misbehave if they don't. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: David Fetter on 5 Jan 2010 12:34 On Tue, Jan 05, 2010 at 05:21:12PM +0000, Greg Stark wrote: > On Tue, Jan 5, 2010 at 4:42 PM, Marko Tiikkaja > <marko.tiikkaja(a)cs.helsinki.fi> wrote: > > => with t as (delete from foo returning *) > > -> insert into bar > > -> select * from t; > > INSERT 0 2 > > > > It correctly reports 2 affected rows (one deleted and one > > inserted), but is this the answer we want? �It doesn't seem all > > that useful to know the total amount of affected rows. > > My first thought is that the number should correspond to the INSERT. > It didn't INSERT two rows so it seems wrong. More importantly in a > case like > > with t as (delete from foo returning *) select * from t where x=? > > applications will almost certainly expect the number to match the > actual number of rows returned and may well misbehave if they don't. I'm not sure how relevant this could be, as existing apps can't use future functionality. We have precedents with RULEs, which can make the arguments pretty meaningless. In some future version, we may want to redo the infrastructure to support "modified" values for multiple statements, but for now, that seems like an unnecessary frill. Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Marko Tiikkaja on 5 Jan 2010 12:45 On 2010-01-05 19:21 +0200, Greg Stark wrote: > with t as (delete from foo returning *) > select * from t where x=? > > applications will almost certainly expect the number to match the > actual number of rows returned and may well misbehave if they don't. I probably wasn't clear about the actual problem in the original post. The problem only affects INSERT, UDPATE and DELETE where you are actually counting affected rows (i.e. PQcmdTuples(), not PQntuples()) so the this example would work as expected. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 5 Jan 2010 13:40 Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> writes: > => with t as (delete from foo returning *) > -> insert into bar > -> select * from t; > INSERT 0 2 > It correctly reports 2 affected rows (one deleted and one inserted), but > is this the answer we want? No. The returned tag should consider only the top-level operation, not what happened inside any CTEs. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Josh Berkus on 5 Jan 2010 16:20
On 1/5/10 9:45 AM, Marko Tiikkaja wrote: > On 2010-01-05 19:21 +0200, Greg Stark wrote: >> with t as (delete from foo returning *) >> select * from t where x=? >> >> applications will almost certainly expect the number to match the >> actual number of rows returned and may well misbehave if they don't. > > I probably wasn't clear about the actual problem in the original post. > The problem only affects INSERT, UDPATE and DELETE where you are > actually counting affected rows (i.e. PQcmdTuples(), not PQntuples()) so > the this example would work as expected. I don't think there is an "as expected" for this situation; people won't know what to expect. So what do we think is resonable? The current behavior, which reports the total count of rows expected, works for me. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |