I would like to create an UNLOGGED materialized view. I seem to understand that is not possible with the current implementation of Postgresql (14).
I am investigating whether it is possible for me to do the following:
- Given that I have two tables
A
andB
. - Where
A
depends onB
, soB
would be the data source andA
the materialized view. In my case onlyA
will be UNLOGGED.B
will be LOGGED. - Add a new entry into the
pg_depends
catalog entry to manually specify thatA
depends onB
, this way I retain the benefit of postgres doing the right thing for my dependencies when it comes to DROPPING the tables.
Dropping the table B
should given an error if A
is still present on the database, unless ON CASCADE
is used.
I need some help with this last step as I am not familiar with the postgresql catalog and the oid
machinery.
To keep this question practical I will be attaching a schema of my views:
CREATE UNLOGGED TABLE A(a int, b int);
CREATE TABLE B(a int, c int);
As a final note I understand that messing with the postgresql catalog in the above way is not advised.
CodePudding user response:
That entry would look as follows:
INSERT INTO pg_depend (classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)
VALUES (
'pg_class'::regclass,
'a'::regclass,
0,
'pg_class'::regclass,
'b'::regclass,
0,
'n' -- "normal" dependency
);
You can find pg_depend
described in the documentation.