0

Context

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.

Question

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:

  1. if the data values themselves are replicated, then my design will fail since OIDs may be different in different databases.

  2. 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?

Side note

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 regclass by text would still work but the cast would only be done later in the function that check the completeness).

  • Logical replication, replicates data changes, not statements. But your example sounds as if you are trying to re-implement partitioning? What exactly is the underlying problem you are trying to solve with your own "inheritance implementation"? – a_horse_with_no_name Jun 20 '18 at 11:18
  • I am not sure I understand what replicating a "data change" perform. In short, my real inheritance implementation allows me to create connections between different types of concepts by referencing the base table concept from a table concept_connection. Only some connections are allowed depending on the concept types involved, this is checked by a trigger and a table that list all the possibilities. – soliz Jun 20 '18 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.