To implement a custom inheritance model in my database using PostgreSQL 10.4 and to avoid coupling the base type to the specialized types, I created a small table
concepttype_regclass for specialized types to register their relation name. It allows a trigger to check the completeness without explicit knowledge of the specialized types.
CREATE TABLE concepttype( concepttype_id integer NOT NULL, concepttype_reflabel text NOT NULL, comment text NOT NULL, CONSTRAINT pk_concepttype PRIMARY KEY (concepttype_id), CONSTRAINT uq_concepttype_reflabel UNIQUE (concepttype_reflabel) ); COMMENT ON TABLE concepttype IS 'A type of concept.'; INSERT INTO concepttype (concepttype_id, concepttype_reflabel, comment) VALUES (E'1', E'stuff', E'A stuff.'); INSERT INTO concepttype (concepttype_id, concepttype_reflabel, comment) VALUES (E'2', E'thing', E'A thing.'); CREATE TABLE concepttype_regclass( concepttype_id integer NOT NULL, specializationregclass regclass NOT NULL, CONSTRAINT pk_concepttype_regclass PRIMARY KEY (concepttype_id), CONSTRAINT uq_concepttype_regclass_specializationregclass UNIQUE (specializationregclass) ); COMMENT ON TABLE concepttype_regclass IS 'The relation name of a specialization of a given concept type.'; ALTER TABLE concepttype_regclass ADD CONSTRAINT fk_concepttype FOREIGN KEY (concepttype_id) REFERENCES concepttype (concepttype_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE; CREATE TABLE concept( concept_id integer NOT NULL DEFAULT 1, concepttype_id integer NOT NULL, CONSTRAINT pk_concept PRIMARY KEY (concept_id) ); COMMENT ON TABLE concept IS 'A concept abstraction of a given concept type.'; ALTER TABLE concept ADD CONSTRAINT fk_concepttype FOREIGN KEY (concepttype_id) REFERENCES concepttype (concepttype_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE; CREATE TABLE stuff( concept_id integer NOT NULL, designation text NOT NULL, CONSTRAINT pk_stuff PRIMARY KEY (concept_id), CONSTRAINT uq_stuff_designation UNIQUE (designation) ); COMMENT ON TABLE stuff IS 'A stuff. Also a concrete specialization corresponding to a given concept.'; ALTER TABLE stuff ADD CONSTRAINT fk_concept FOREIGN KEY (concept_id) REFERENCES concept (concept_id) MATCH SIMPLE ON DELETE CASCADE ON UPDATE CASCADE; -- Register the specialized type INSERT INTO concepttype_regclass (concepttype_id, specializationregclass) VALUES (E'1', E'stuff');
The point here, is that I store data
concepttype_regclass.specializationregclass as Object identifiers (OIDs) with the regclass alias.
I need a read-only reporting replica of my data to a remote server and I was wondering if using OID was possible in that case.
Since I have no control over the OID values, then the OID value of the relation
stuff in the main database might not necessarily correspond to the OID of the relation
stuff in the replicated database.
I do not know so much about PostgreSQL replication possibilities and I did not choose which type to use yet, but I assume different cases:
if the data values themselves are replicated, then my design will fail since OIDs may be different in different databases.
if the SQL statements are replicated, then the string "stuff" will be casted to regclass on the replicated database and therefore will have the correct OID, in which case my design will succeed.
From what I understand in the documentation, it seems that Trigger-Based Master-Standby Replication, Asynchronous Multimaster Replication and Synchronous Multimaster Replication perform like case 1, and Statement-Based Replication Middleware performs like case 2.
I believe the Logical Replication is best fitted for my read-only reporting replica, but what about it? Is it performing like 1, 2 or something else?
Since my need is to have a read-only replica, the
concepttype_regclass will never be exploited in the replica, but things change and the replica may not stay read-only all the time. I want to open my possibilities and ideally keep the dynamic regclass cast check when registering a specialized type (replacing
text would still work but the cast would only be done later in the function that check the completeness).