Foreign table created with inherits stuck

Question

I've been trying to create a foreign table in my PSQL database. The point here is to concatenate the same steps table from 2 different databases. I want to use INHERITS to copy the schema.

I have a steps table locally, and a steps table (with millions of lines) on my cache_server. I've tried to create the foreign table with:

CREATE FOREIGN TABLE cache_steps  ()
INHERITS (steps)
SERVER cache_server 
OPTIONS ( schema_name 'public', table_name 'steps');

The thing is when I do that, the local steps table becomes unusable, there's no lock but it loads forever, I can't do any request to this table. I don't understand why this inheritance has any impact on my steps table.

If I create my foreign table with the schema instead of inherits, everything works fine

CREATE FOREIGN TABLE cache_steps  (
    column1 text,
    column2 text
)
SERVER cache_server 
OPTIONS ( schema_name 'public', table_name 'steps')

score 2 · Accepted Answer · answered Nov 18 '20 at 13:15

INHERITS doesn't copy the schema per se. It instead, allows you to structure your data in a way to prevent the common 'issues' of inheritance in databases:

Duplication of data: parent's data is present in every child table
Foreign keys from child to parent (eg. Dog.AnimalID -> Animal.Id)
One big table with everything (parents and all types of children) in it

With INHERITS, Posgres's engine takes care of rejoigning the data for you, so it looks like you have all your child data in the same place when you query for it, but actually it's spread into individual tables each holding one child type's data and the parent is left with only the data specific to parents.

So in your case, your local steps table doesn't have any columns and thus is looks like you 'inherited schema' but really not.

Your table seems stuck only because it's loading your millions of rows from the remote server, so there, depending on the network conditions and your query, it could be quite normal for it to take too long to return.

Reference for further reading: https://www.postgresql.org/docs/12/tutorial-inheritance.html

Foreign table created with inherits stuck

1 Answers1