Inheritence and Primary Key Constraints

Question

I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also happen to the supertable and vice versa.

Question 1: How is data physically stored if you have 2 tables and table 1 has no constraints but table 2 has an identity primary key column and also inherits columns from table 1.

Question 2: Since one table inherits columns from the other and inserts in table 2 result also in population of table 1, is the data stored completely isolated and redundant in two different tuples on different pages or is are the tuples linked somehow? And if its separated, does that mean both tuples get locked in case of a writing operation?

Question 3: How does an index affect table inheritance?

Question 4: What's an actual use for object oriented features in Postgres?

The tables used:

create table person (
    nachname varchar(30),
    age int,
    eigenschaft varchar(30)
    ) 

create table praktikant (
    praktikant_id int generated always as identity primary key,
    klassenstufe int) inherits(person);

the insert:

insert into praktikant(nachname, age, eigenschaft, klassenstufe)
values('Schmidt', 16, 'fleißig', 9)

Answer 1

The two tables person and praktikant in your example are two distinct tables. Inserting rows into praktikant does not insert rows in person, but

SELECT ... FROM person;

is effectively the same as

SELECT ... FROM ONLY person
UNION ALL
SELECT ... FROM praktikant;

Look at the EXPLAIN output for both queries to confirm that.

Each of these tables has its own constraints and indexes, which don't affect the other table.

So the answers to your questions would be:

1. The tables are completely independent, except that

   • praktikant must have all columns of person

   • querying person will include rows from praktikant

2. That question is not quite clear, but I assume you can answer it yourself from the above. Locks are taken on the rows of each table independently.

3. Not at all. Also, a constraint on one table does not affect the other table. Specifically, you cannot guarantee uniqueness of entries across the two tables.

4. There are few real-world uses. Before the advent of declarative partitioning in v10, it was used to implement partitioning in PostgreSQL.