[YSQL] CREATE, DROP Materialized View #10509
Comments
fizaaluthra
pushed a commit
that referenced
this issue
Dec 10, 2021
…TERIALIZED VIEW
Summary:
`CREATE MATERIALIZED VIEW`:
- Created like any other YB relation.
`REFRESH MATERIALIZED VIEW`:
- In vanilla PG, a non-concurrent refresh on the materialized view entails the following:
# Load the new matview data into a transient relation.
# Refresh the matview by swapping it with the transient relation. The swap is done by swapping the `relfilenode` of the two relations. (Therefore the `relfilenode` of the matview is now the `OID` of the transient relation).
# Reindex the new matview.
- In YB mode we do the following:
# Load the new matview data into a transient relation, and swap the `relfilenode` (as in vanilla PG).
# We implement a `YBGetStorageRelid` utility function that returns, if valid, the `relfilenode` (which will be different from the `OID` if there has been a swap) or the `OID` itself.
# Any subsequent scans/drops on the matview will now use `YBGetStorageRelid` to retrieve the `OID` of the appropriate relation in DocDB.
# We reindex the matview by dropping and recreating the matview's indexes.
`REFRESH MATERIALIZED VIEW CONCURRENTLY`:
- In PG, a concurrent refresh entails the following:
# Ensure there's a unique index on the matview.
# Load the new data into a temporary table.
# Ensure there are no duplicate rows without any null values (i.e., duplicate rows are only allowed when at least one column value is null).
# Create a temporary diff table which tells us what rows are present in the old data and not in the new data and vice versa.
- The diff table is computed as a full join of the old matview and the new temporary table on the unique index.
# Delete rows in the matview where row.ctid = any old matview ctid in diff where the newdata is null. (i,e,, if the join did not find a match for this row amidst the new data, this row has been deleted).
# Insert rows from the new data in the diff table where old matview ctid is null. (i,e., if the join did not find a match amidst the old data, this row has been newly inserted).
- In YB mode:
# We do essentially the same thing, except that since YB doesn't have ctids we use all the column values instead.
# Additionally, since there aren't any ctids, if the new data has rows with **all** null columns, the joined diff table will have duplicate entries for such rows.
# Therefore, in YB mode, we disallow rows with all null columns.
`DROP MATERIALIZED VIEW`:
- The syscatalog tuples are dropped like any other YB relation.
- `YBGetStorageRelid` is used to drop the appropriate relation in DocDB.
Test Plan:
Jenkins: urgent, test regex: .*TestPgRegressFeature.*|.*TestPgRegressExtension.*|.*TestPgRegressPartitions.*
Run the `yb_feature_matview` test using the `TestPgRegressFeature` test suite.
Reviewers: myang, mihnea
Reviewed By: myang, mihnea
Subscribers: bogdan, yql
Differential Revision: https://phabricator.dev.yugabyte.com/D13792
|
Implemented by commit f69988a |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Description
Issue for tracking
CREATE MATERIALIZED VIEW,DROP MATERIALIZED VIEWThe text was updated successfully, but these errors were encountered: