8000 Use exclusive lock for view maintenance caused by UPDATE or DELETE by yugo-n · Pull Request #42 · sraoss/pg_ivm · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Use exclusive lock for view maintenance caused by UPDATE or DELETE #42

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Dec 16, 2022
Merged

Use exclusive lock for view maintenance caused by UPDATE or DELETE #42

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -237,7 +237,7 @@ When a base table is truncated, the IMMV is also truncated and the contents beco

### Concurrent Transactions

Suppose an IMMV is defined on two base tables and each table was modified in different a concurrent transaction simultaneously. In the transaction which was committed first, the IMMV can be updated considering only the change which happened in this transaction. On the other hand, in order to update the IMMV correctly in the transaction which was committed later, we need to know the changes occurred in both transactions. For this reason, `ExclusiveLock` is held on an IMMV immediately after a base table is modified in `READ COMMITTED` mode to make sure that the IMMV is updated in the latter transaction after the former transaction is committed. In `REPEATABLE READ` or `SERIALIZABLE` mode, an error is raised immediately if lock acquisition fails because any changes which occurred in other transactions are not be visible in these modes and IMMV cannot be updated correctly in such situations. However, as an exception if the IMMV has only one base table and doesn't use DISTINCT or GROUP BY, the lock held on the IMMV is `RowExclusiveLock`.
Suppose an IMMV is defined on two base tables and each table was modified in different a concurrent transaction simultaneously. In the transaction which was committed first, the IMMV can be updated considering only the change which happened in this transaction. On the other hand, in order to update the IMMV correctly in the transaction which was committed later, we need to know the changes occurred in both transactions. For this reason, `ExclusiveLock` is held on an IMMV immediately after a base table is modified in `READ COMMITTED` mode to make sure that the IMMV is updated in the latter transaction after the former transaction is committed. In `REPEATABLE READ` or `SERIALIZABLE` mode, an error is raised immediately if lock acquisition fails because any changes which occurred in other transactions are not be visible in these modes and IMMV cannot be updated correctly in such situations. However, as an exception if the IMMV has only one base table and doesn't use DISTINCT or GROUP BY, and the table is modified by `INSERT`, then the lock held on the IMMV is `RowExclusiveLock`.

### Row Level Security

Expand Down
12 changes: 12 additions & 0 deletions createas.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -635,6 +635,18 @@ CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock
}
}

/*
* XXX: When using DELETE or UPDATE, we must use exclusive lock for now
* because apply_old_delta(_with_count) doesn't work in concurrent situations.
*
* If the view doesn't have aggregate, distinct, or tuple duplicate, then it
* would work. However, we don't have any way to guarantee the view has a unique
* key before opening the IMMV at the maintenance time because users may drop
* the unique index. We need something to resolve the issue!!
*/
if (type == TRIGGER_TYPE_DELETE || type == TRIGGER_TYPE_UPDATE)
ex_lock = true;

ivm_trigger->funcname =
(timing == TRIGGER_TYPE_BEFORE ? SystemFuncName("IVM_immediate_before") : SystemFuncName("IVM_immediate_maintenance"));

Expand Down
0