Unexpected update behaviour

Started by Victor Yegorovover 12 years ago2 messagesgeneral
Jump to latest
#1Victor Yegorov
vyegorov@gmail.com

Greetings.

I've hit a strange issue. This is a simplified setup.
First, run create.sql to get a couple of tables.
Then, run the update query.

Tested on 9.2.6 and 9.3.2.

Now:
- if I remove the UNION ALL part of the inner query, UPDATE works;
- if I move the `raw` subquery into the CTE, UPDATE works (but takes
hours on my full data);
- if I convert this UPDATE into a SELECT, I get the row to be updated.

Could you kindly explain me why the query as it is updates no records?

--
Victor Y. Yegorov

Attachments:

create.sqlapplication/octet-stream; name=create.sqlDownload
update.sqlapplication/octet-stream; name=update.sqlDownload
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Victor Yegorov (#1)
Re: Unexpected update behaviour

Victor Yegorov <vyegorov@gmail.com> writes:

Could you kindly explain me why the query as it is updates no records?

It's a bug, that's why. See
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=324577f39bc8738ed0ec24c36c5cb2c2f81ec660
or for 9.2,
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5d545b7ed53a8b2058c1152bd1c9ae227b3280e3

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general