From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Mario De Frutos Dieguez <mariodefrutos@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: Problem with a "complex" upsert
Date: Fri, 3 Aug 2018 19:17:05 +0900
Message-ID: <8e5d96df-95d0-ffdd-46cb-784da36b0e14@lab.ntt.co.jp> (raw)
In-Reply-To: <CAEZATCUzdUerONYpaZ-YTVKOyzm1B-Ya9XzFXoMs0yrwJr0hkw@mail.gmail.com>
References: <CAFYwGJ0xfzy8jaK80hVN2eUWr6huce0RU8AgU04MGD00igqkTg@mail.gmail.com>
<CAFYwGJ3Cpb2T+177rEopU7_aQE3W03UoqNZXdP5erCtSRgDGVA@mail.gmail.com>
<18795.1529600753@sss.pgh.pa.us>
<8da3739d-da24-1125-cab6-b68d6ec1bc6a@lab.ntt.co.jp>
<30415.1531256373@sss.pgh.pa.us>
<4b956567-8283-1b8a-7cd7-f8b6e626bba9@lab.ntt.co.jp>
<CAEZATCWqLba_VVT1cdaT3YOijESRT8qewLN93Oc7H7ikW2fd-Q@mail.gmail.com>
<CAEZATCUzdUerONYpaZ-YTVKOyzm1B-Ya9XzFXoMs0yrwJr0hkw@mail.gmail.com>
Thanks Dean for taking a look.
On 2018/08/03 18:40, Dean Rasheed wrote:
> On 3 August 2018 at 07:52, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> This doesn't look right to me. It breaks the following case ...
Hmm yeah, matching view and base relation names like that was silly.
> Here's an updated patch that fixes this.
>
>> I also don't see why it should reject columns from the view that
>> aren't in the base relation.
>
> This patch also allows access to view columns that aren't in the
> underlying base relation. The rationale for the result in the new test
> case where it attempts to insert (1,'y') into columns (aa,bb) of the
> view is that the new view row that would have resulted if the insert
> had succeeded is ('y',1,(1,'y')), hence that's what excluded.* should
> be for the view in the "on conflict" action, and there should be no
> problem referring to any part of that excluded view tuple.
Ah, I see what you did there with converting EXCLUDED column references.
I had tried to do the coversion from view attnos to base rel attnos using
tupconvert.c. When fiddling with that, I had to install that restriction
of not allowing accessing view's own columns via EXCLUDED, *because of*
trying to convert using tupconvert.c. Somehow, I had also became
convinced that restricting it like that made sense semantically, which as
you've shown, it doesn't.
After seeing your first email, I had started replacing the tupconvert.c
based conversion (which wouldn't even be readily backpatchable to 9.5) to
ReplaceVarsFromTargetList one, but you beat me to it.
Your updated version looks good and also nice that it has more tests. One
thing that stood out to me was how column references via EXCLUDED now
refer to base rel column names, but I guess that's fine.
create table foo (a int unique, b int);
create view foo_view as select b as bb, a + 1 as cc, a as aa from foo;
explain insert into foo_view (aa, bb) select 1, 1 on conflict (aa) do
update set bb = excluded.bb where excluded.cc > 1;
QUERY PLAN
─────────────────────────────────────────────────
Insert on foo (cost=0.00..0.01 rows=1 width=8)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: foo_a_key
Conflict Filter: ((excluded.a + 1) > 1)
-> Result (cost=0.00..0.01 rows=1 width=8)
(5 rows)
explain insert into foo_view (aa, bb) select 1, 1 on conflict (aa) do
update set bb = excluded.bb where excluded.aa > 1;
QUERY PLAN
─────────────────────────────────────────────────
Insert on foo (cost=0.00..0.01 rows=1 width=8)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: foo_a_key
Conflict Filter: (excluded.a > 1)
-> Result (cost=0.00..0.01 rows=1 width=8)
(5 rows)
Thanks again.
Regards,
Amit
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, dean.a.rasheed@gmail.com, tgl@sss.pgh.pa.us, mariodefrutos@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: Problem with a "complex" upsert
In-Reply-To: <8e5d96df-95d0-ffdd-46cb-784da36b0e14@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox