Re: [HACKERS] [PATCH] Lockable views

Robert,

> I just reread those discussions but I don't see that they really make
> any argument for the behavior the patch implements. I see no
> explanation on the thread for why locking a table inside of a subquery
> is more or less likely to cause deadlock than locking one outside of a
> subquery.

If we allow to lock a table in a subquery, following could happen:

We have: CREATE VIEW v1 AS SELECT * FROM t1 WHERE i = (SELECT i FROM t2);

1. Session A tries to lock v1 (I suppose it tries to acquire lock in
the order of t1, then t2). A acquires lock on t1 but yet on t2.

2. Another session B acquires lock on t2.

3. A continues to try to acquire lock on t2 (blocked).

4. B tries to acquire lock on t1. Deadlock occurs.

So if a user mixes locking a view and a underlying base table, there's
a possibility of deadlocks. If we regard that it is user's
responsibility not to mix them or to be careful about the consequence
the mixing of locks so that dealocks do not happen, then I would agree
that we should lock a table inside a subquery.

What do you think?

>>> I think that if we
>>> change the rules for which subqueries get flattened in a future
>>> release, then the behavior will also change. That seems bad.
>>
>> I doubt it could happen in the future but if that happend we should
>> disallow locking on such views.
>
> That doesn't make any sense to me. When someone migrates from
> PostgreSQL 11 to, say, PostgreSQL 14, the view definition is going to
> be recreated from an SQL query. Neither the user nor the database
> will know whether the query was optimized the same way on both
> databases, so how could we disallow locking only those views where
> there was a difference on the two releases? Even if we could, how
> does that help anything? Throwing an error is just as much a
> backward-incompatibility in the command as silently changing what gets
> locked.
>
> But my complaint may have been a little off base all the same -- I
> guess we're doing this based on the rewriter output, rather than the
> optimizer output, which makes it a lot less likely that we would
> decide to change anything here.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp