Home > Software design >  Crafting manual pg_depend entry
Crafting manual pg_depend entry

Time:04-28

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.

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.

  • Related