Re: Fwd: Problem with a "complex" upsert

Wow, that's amazing news. Sorry for not being doing this in a proper way,
it was my first time guessing if I'm confronting a bug or not. For the next
time, I'll provide a more prepared answer :)

Thank you very much to all :)

2018-06-22 10:11 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:

> On 2018/06/22 2:05, Tom Lane wrote:
> > Mario De Frutos Dieguez <mariodefrutos(at)gmail(dot)com> writes:
> >> I'm trying to do an upsert to an updatable view with the following SQL
> >> query:
> >> ...
> >> If I don't get any conflict everything works as intended but if we hit a
> >> conflict then I get the following error message:
> >> ERROR: attribute 2 of type record has the wrong type
> >> DETAIL: Table has type character varying, but query expects double
> >> precision.
> >
> > When filing a bug report, it's a good idea to provide both a self-
> > contained test case and a mention of what PG version you're using.
> >
> > I guess from the ROW() syntax you used here, which isn't accepted
> pre-v10,
> > that you're using 10.0 or later, but that's not specific enough.
> >
> > I tried to duplicate this problem using the attached script, but it
> > works for me.
> >
> > FWIW, that error message definitely looks like a bug, but I can't
> > tell whether it's an already-fixed bug or there's some triggering
> > detail you didn't mention.
>
> 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;
>
> insert into foo_view values (1, 'xyxyxy')
> on conflict (a) do update set b = excluded.b;
> ERROR: attribute 1 of type record has wrong type
> DETAIL: Table has type text, but query expects double precision.
>
> -- crash occurs if, like OP, I change EXCLUDED reference to target table
> insert into foo_view values (1, 'xyxyxy') on conflict (a) do update set b
> = foo_view.b;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> 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.
>
> Thanks,
> Amit
>