Re: Fwd: Problem with a "complex" upsert

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Mario De Frutos Dieguez <mariodefrutos(at)gmail(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: Fwd: Problem with a "complex" upsert
Date: 2018-07-10 20:59:33
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-admin pgsql-bugs

Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
> Having worked a little bit on the ON CONFLICT code recently, I was able to
> guess at the triggering detail. At least, I was able to reproduce the
> error and crash seen in the OP's report. Here's a minimal example:

> create table foo (a text unique, b float);
> insert into foo values ('xyxyxy', 1);

> -- note the different order of columns in the view
> create view foo_view as select b, a from foo;


> I tried debugging why that happens and concluded that rewriteTargetView
> fails to *completely* account for the fact that the view's column's may
> have different attribute numbers than the underlying table that the DO
> UPDATE action will be applied to. Especially, even if the view's Vars are
> replaced with those corresponding underlying base table's columns, the
> TargetEntry's into which those Vars are contained are not refreshed, that
> is, their resnos don't match varattnos.

> I created a patch that seems to fix the issue, which also adds a
> representative test in updatable_view.sql.

Hm. I looked at this patch a bit. While the onConflictSet change looks
reasonable, I find the exclRelTlist change fishy. Shouldn't those resnos
correspond to the exclRelTlist's *own* vars, independently of what is or
isn't in the view_targetlist? And why is it OK to ignore failure to find
a match?

The provided test case doesn't seem to me to prove that that code is OK.
AFAICS, exclRelTlist only gets used by EXPLAIN, and there's no EXPLAIN
output in the test case.

regards, tom lane

In response to


Browse pgsql-admin by date

  From Date Subject
Next Message Mariel Cherkassky 2018-07-11 13:09:19 wal exist in slave but getting err requested WAL segment has already been removed
Previous Message Anjul Tyagi 2018-07-09 07:03:18 Re: logical Replication

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-07-10 22:47:48 Re: BUG #15251: query plan affected by grant select on partition
Previous Message Dmitry Dolgov 2018-07-10 20:39:28 Problem with tupdesc in jsonb_to_recordset