Crafting manual pg_depend entry

Question

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 and B.
Where A depends on B, so B would be the data source and A the materialized view. In my case only A will be UNLOGGED. B will be LOGGED.
Add a new entry into the pg_depends catalog entry to manually specify that A depends on B, 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.

What kind of dependency do you want? Should dropping B cause an error or drop A automatically? I recommend *not* to modify catalog tables. — Laurenz Albe, Apr 27 '22 at 05:48
I updated the above. This is the relevant bit: Dropping the table `B` should given an error if `A` is still present on the database, unless `ON CASCADE` is used. Let's say that this question is more for a theoretical sake and to familiarise with pgsql catalogs. — Antonio L., Apr 27 '22 at 05:51

score 1 · Accepted Answer · answered Apr 28 '22 at 06:16

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.

Crafting manual pg_depend entry

1 Answers1