Supporting MERGE on updatable views

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Supporting MERGE on updatable views
Date: 2022-12-08 10:03:29
Message-ID: CAEZATCVcB1g0nmxuEc-A+gGB0HnfcGQNGYH7gS=7rq0u0zOBXA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have been playing around with making updatable views support MERGE,
and it looks to be fairly straightforward.

I'm intending to support auto-updatable views, WITH CHECK OPTION, and
trigger-updatable views, but not views with rules, as I think that
would be more trouble than it's worth.

Per the SQL standard, if the view isn't auto-updatable, it requires
the appropriate INSTEAD OF INSERT/UPDATE/DELETE triggers to perform
the merge actions. One limitation with the current patch is that it
will only work if the view is either auto-updatable with no INSTEAD OF
triggers, or it has a full set of INSTEAD OF triggers for all
INSERT/UPDATE/DELETE actions mentioned in the MERGE command. It
doesn't support a mix of those 2 cases (i.e., a partial set of INSTEAD
OF triggers, such as an INSTEAD OF INSERT trigger only, on an
otherwise auto-updatable view). Perhaps it will be possible to
overcome that limitation in the future, but I think that it will be
hard.

In practice though, I think that this shouldn't be very limiting -- I
think it's uncommon for people to define INSTEAD OF triggers on
auto-updatable views, and if they do, they just need to be sure to
provide a full set.

Attached is a WIP patch, which I'll add to the next CF. I still need
to do more testing, and update the docs, but so far, everything
appears to work.

Regards,
Dean

Attachment Content-Type Size
support-merge-into-view-v1.patch text/x-patch 106.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2022-12-08 10:40:58 Re: Aggregate node doesn't include cost for sorting
Previous Message Peter Eisentraut 2022-12-08 09:27:25 Re: refactor ExecGrant_*() functions