Skip to content
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.

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

[YSQL] CREATE, DROP Materialized View #10509

Closed
fizaaluthra opened this issue Nov 5, 2021 · 1 comment
Closed

[YSQL] CREATE, DROP Materialized View #10509

fizaaluthra opened this issue Nov 5, 2021 · 1 comment
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) pgcm

Comments

@fizaaluthra
Copy link
Member

Description

Issue for tracking CREATE MATERIALIZED VIEW , DROP MATERIALIZED VIEW

@fizaaluthra fizaaluthra added the area/ysql Yugabyte SQL (YSQL) label Nov 5, 2021
@fizaaluthra fizaaluthra self-assigned this Nov 5, 2021
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
@fizaaluthra
Copy link
Member Author

Implemented by commit f69988a

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) pgcm
Projects
None yet
Development

No branches or pull requests

2 participants