Обсуждение: CREATE MATERIALIZED VIEW .. FOR UPDATE

Does the combination in $SUBJECT make sense?  It is currently allowed,
but of course the underlying locks only last while the creating
transaction is open, and they are reacquired during a refresh.

Somewhat related is that the error message they emit is a bit
nonstandard:

cannot lock rows in materialized view \"%s\"

After checking the reason for this, I noticed that it doesn't even match
what the code thinks it should (CheckValidRowMarkRel()):
    case RELKIND_MATVIEW:        /* Should not get here */        ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),                errmsg("cannot lock rows in materialized view \"%s\"",
         RelationGetRelationName(rel))));
 

apparently this function believes that the check should be applied
earlier, but it isn't.  I think we ought to either add a check to the
parser stage; *or* we should remove the "should not get here" comment.

I also propose we make these errors consistent with the wording of the
other related errors, i.e. "FOR UPDATE is not allowed with materialized
views", and of course change it for all the other cases in that
function.

Opinions?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

> Does the combination in $SUBJECT make sense?

I don't think so; I don't know what it would mean.

> It is currently allowed,

I will take a look.


--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

I just realized I mixed two different (but related) cases in my previous
email:

Alvaro Herrera wrote:
> Does the combination in $SUBJECT make sense?  It is currently allowed,
> but of course the underlying locks only last while the creating
> transaction is open, and they are reacquired during a refresh.

This paragraph is talking about a FOR UPDATE clause in the CREATE
MATERIALIZED VIEW command, as in the email subject.

> Somewhat related is that the error message they emit is a bit
> nonstandard:
> 
> cannot lock rows in materialized view \"%s\"

This other paragraph, and everything below it, is talking about a
SELECT .. FROM matview FOR UPDATE
command.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Kevin Grittner <kgrittn@ymail.com> wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
>> Does the combination in $SUBJECT make sense?
>
> I don't think so; I don't know what it would mean.

Oh, I see -- it's part of the SELECT statement, causing a row-level
lock on each row as it is accessed.

>> It is currently allowed,
>
> I will take a look.

Now that I'm reading it correctly, it seems within the realm of
possibility that someone might have a legitimate use for this.  I
don't see any reason to jump through any hoops to prevent anyone
from doing it.  It is worth noting that both CREATE VIEW and CREATE
TABLE AS allow the same thing.

Thanks for pointing this out, though.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company