Re: BUG #9398: DELETE refering to a materialized view produces "cannot lock rows in materialized view" error

On Sat, Mar 1, 2014 at 6:51 PM, <no-email(at)example(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 9398
> Logged by: nakag
> Email address: no-email(at)example(dot)com
> PostgreSQL version: 9.3.3
> Operating system: Linux
> Description:
>
> CREATE TABLE base ( id int primary key );
> CREATE MATERIALIZED VIEW mv AS SELECT * FROM base;
> CREATE TABLE d ( id int primary key );
> DELETE FROM d WHERE EXISTS ( SELECT * FROM mv WHERE mv.id = d.id );
>
> The above code produces an ERROR "cannot lock rows in materialized view."
This smells like a limitation to matviews and not a bug... This error
message refers to CheckValidRowMarkRel:execMain.c:
case RELKIND_MATVIEW:
/* Should not get here */
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot lock rows in materialized view \"%s\"",
RelationGetRelationName(rel))));
break;
Even if it is clearly written that this code path should not be
taken... Well it is actually taken.

Note that doing a similar operation on a foreign table or a view works:
=# create table aa (a int);
CREATE TABLE
=# create materialized view bb as select * from aa;
SELECT 0
=# delete from aa using bb where aa.a = bb.a;
ERROR: 42809: cannot lock rows in materialized view "bb"
LOCATION: CheckValidRowMarkRel, execMain.c:1109
Time: 0.929 ms
=# create view cc as select * from aa;
CREATE VIEW
Time: 10.108 ms
=# delete from aa using cc where aa.a = cc.a;
DELETE 0
-- Create FDW server, etc...
=# CREATE FOREIGN TABLE aa_foreign (a int) SERVER postgres_server
OPTIONS (table_name 'aa');
CREATE FOREIGN TABLE
Time: 2.290 ms
=# delete from aa using aa_foreign where aa.a = aa_foreign.a;
DELETE 0

For views, planner expands the view to the parent relations to not
face this error. But this is not doable for a matview because I do not
think we can take locks on its rows without support for incremental
updates. Am I right? Shouldn't the error message be more explicit
here?
Regards,
--
Michael