Re: Fwd: Problem with a "complex" upsert - Mailing list pgsql-bugs

On 2018/06/22 2:05, Tom Lane wrote:
> Mario De Frutos Dieguez <mariodefrutos@gmail.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