Partitioned tables are not supported by everything related to schema cache #1783

bitlab-code · 2021-04-01T12:30:29Z

Environment

PostgreSQL version: with docker: postgres:13.2
PostgREST version: with docker: postgrest/postgrest:nightly-2021-03-05-19-03-d3a8b5f
Operating system: MacOS

Description of issue

This issue is related #1592 (and the fix: #1593)
I've tested the last the nightly builds and I still have the same issue, but only with partitioned tables.

It seem that PostgREST don't recognize foreign keys if the tables are partitioned, but if I remove the partitions all work fine.

CREATE TABLE public."Diary"
(
  school text NOT NULL,
  day text NOT NULL,
  student text NOT NULL,
  rif text NOT NULL GENERATED ALWAYS AS (immutable_concat_ws('|',school,day,student)) STORED,
  room text NOT NULL,
  -- ...other...
  PRIMARY KEY (school, day, student),
  FOREIGN KEY (school) REFERENCES public."School" (id),
  FOREIGN KEY (school, student) REFERENCES public."Student" (school, student),
  FOREIGN KEY (school, room) REFERENCES public."Room" (school, room),
  CONSTRAINT CHECK_Diary_day CHECK (day ~ '^\d{4}\-(0[1-9]|1[012])\-(0[1-9]|[12][0-9]|3[01])$')
)  PARTITION BY LIST (school);
-- ... (indexes)

CREATE TABLE public."Student"
(
  school text NOT NULL,
  student text NOT NULL,
  rif text NOT NULL GENERATED ALWAYS AS (school || '|' || student) STORED,
  room text NOT NULL,
  -- ...other...
  PRIMARY KEY (school, student),
  FOREIGN KEY (school) REFERENCES public."School" (id),
  FOREIGN KEY (school, room) REFERENCES public."Room" (school, room)
) PARTITION BY LIST (school);
-- ... (indexes)

The text was updated successfully, but these errors were encountered:

wolfgangwalther · 2021-04-01T18:24:42Z

Can you give us a bit more information in the form of a self-contained example?

I think you can remove the Diary table and instead add the School or Room table (just very basic setup to reproduce). And then show us the exact request you're doing and what response you get.

bitlab-code · 2021-04-02T00:07:57Z

@wolfgangwalther sorry, I've copy-pasted a piece of code to simply show how i've setted partitions.
Now I've reproduced the issue with the following:

Environment

PostgreSQL version: with docker: postgres:13.2
PostgREST version: with docker: postgrest/postgrest:nightly-2021-03-05-19-03-d3a8b5f

Steps to reproduce:

From a fresh installation: I used the same example from issue Resource embedding is not working on join tables using composite keys #1592 (fixed by Removing single column limit from join table M2M mapping detection. #1593) but the tables are partitioned

DROP TABLE IF EXISTS public.test_a CASCADE; 
CREATE TABLE public.test_a(
 id1 int NOT NULL,
 id2 int NOT NULL,
 a VARCHAR(64) NOT NULL,
 PRIMARY KEY (id1, id2)
) PARTITION BY LIST (id1);

DROP TABLE IF EXISTS public.test_b CASCADE; 
CREATE TABLE public.test_b(
 id1 int NOT NULL,
 id2 int NOT NULL,
 b VARCHAR(64) NOT NULL,
 PRIMARY KEY (id1, id2)
) PARTITION BY LIST (id1) ;

DROP TABLE IF EXISTS public.test_lnk CASCADE; 
CREATE TABLE public.test_lnk (
 a1 INT NOT NULL,
 a2 INT NOT NULL,
 b1 INT NOT NULL,
 b2 INT NOT NULL,
 CONSTRAINT fk_test_a FOREIGN KEY (a1,a2) REFERENCES public.test_a(id1,id2),
 CONSTRAINT fk_test_b FOREIGN KEY (b1,b2) REFERENCES public.test_b(id1,id2)
) PARTITION BY LIST (a1);

Query:

http://localhost:5430/test_a?select=test_b(*)

Response:

Status: 400 Bad Request
Body:

{
   "message": "Could not find foreign keys between these entities. No relationship found between test_a and test_b"
}

Even the following queries don't work:

Query: http://localhost:5430/test_a?select=test_lnk(*)
- Result: Could not find foreign keys between these entities. No relationship found between test_a and test_lnk
Query: http://localhost:5430/test_lnk?select=test_a(*)
- Result: Could not find foreign keys between these entities. No relationship found between test_lnk and test_a

Also tried after created partitions on all tables and insert some values

Dropped the tables, recreated without the partitions, and refreshed schema cache: everything works as expected!(thanks to last fix added in the nightly ver.)

wolfgangwalther · 2021-04-02T09:03:03Z

This is not only related to this specific case of embedding. I wanted to take a look at this, but already noticed that the tables in your example do not even show up in the openapi schema at the root endpoint.

This is because none of the queries in DbStructure.hs includes the relkind p which is used for the partitioned table:

postgrest/src/PostgREST/DbStructure.hs

Line 270 in 9f074ce

c.relkind in ('r', 'v', 'f')

postgrest/src/PostgREST/DbStructure.hs

Line 291 in 9f074ce

c.relkind in ('v', 'r', 'm', 'f')

postgrest/src/PostgREST/DbStructure.hs

Line 411 in 9f074ce

c.relkind IN ('r', 'v','f')

postgrest/src/PostgREST/DbStructure.hs

Line 423 in 9f074ce

WHERE c.relkind IN ('v','r','m','f')

postgrest/src/PostgREST/DbStructure.hs

Line 461 in 9f074ce

AND c.relkind IN ('r', 'v', 'f', 'm')

postgrest/src/PostgREST/DbStructure.hs

Line 519 in 9f074ce

AND c.relkind in ('r', 'v', 'f', 'm')

postgrest/src/PostgREST/DbStructure.hs

Line 618 in 9f074ce

AND r.relkind = 'r'

postgrest/src/PostgREST/DbStructure.hs

Line 655 in 9f074ce

AND r.relkind = 'r'

baz-bakerhughes · 2021-06-07T20:24:31Z

Hello team. Any thoughts on this bug? We also have the same issue with embedded resources and partitions.

wolfgangwalther · 2021-06-08T16:45:16Z

Hello team. Any thoughts on this bug? We also have the same issue with embedded resources and partitions.

This shouldn't be too hard to implement. It will be mainly about adding some test cases and adding the relevant relkinds to the queries. Not even much haskell involved.

Do you want to give it a try?

wolfgangwalther · 2021-11-03T09:22:26Z

Seems like just adding the partitioned tables to the schema cache is not giving us 100% support for partitioned tables, yet.

There's a report at #1903 (comment) which says M2M embedding with partitioned tables does not work, yet.

Still need to confirm, but let's reopen this in the meantime.

wolfgangwalther · 2021-11-03T09:24:53Z

There's also another report here: #1903 (comment)

Looks like there might be an issue with creating duplicated entries in the schema cache in some kind of schema.

@hanumanth-mv can you share a code example reproducing the problem you've reported?

BakerHughesCharlesSewell · 2021-11-03T14:27:04Z

Here are the test scenario results using the Film/Actor/Director example provided on the PostgREST documentation website under Resource Embedded section. Below is the SQL Script to generate the Film tables.

Tested Scenarios:

First Level table relationships queries work for both
- Partitioned table works /film?select=title,director(name)
- Non-non-partitioned table works /film_nonpartitioned?select=title,director_nonpartitioned(name)
Second Level table relationships do not work for partitioned tables
- Partition table is unable to understand relationship /film?select=title,actor(name)
- Non-Partitioned table works /film_nonpartitioned?select=title,actor_nonpartitioned(name)
Target Disambiguation works for both
- Partition table works for multiple address relationships /actor?select=name,primary_address(street,city,state, country),secondary_address(street,city,state, country)
- Non-Partition table works for multiple address relationships /actor_nonpartitioned?select=name,primary_address(street,city,state, country),secondary_address(street,city,state, country)

Work Around for Partitioned Table 2nd Level & Above Relationship Error

You can chain the embedded queries to get 2nd level and higher relationship data. For example, the 2nd level query that failed above will work if the "role" table which has a direct relationship to films is used as a bridge to get the the "actor" table /film?select=title,role(character_name,actor(name))

CREATE SCHEMA IF NOT EXISTS api;
--Create Address Partitioned Table
DROP TABLE IF EXISTS api.address CASCADE;
DROP TABLE IF EXISTS api.address_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.address
(
    id integer NOT NULL,
    street varchar NOT NULL,
    city varchar NOT NULL,
    state varchar NOT NULL,
    country varchar NOT NULL,
    UNIQUE(id, country)
) PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.address_usa PARTITION OF api.address FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.address_default PARTITION OF api.address DEFAULT;
--Create Address Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.address_nonpartitioned
(
    id integer PRIMARY KEY,
    street varchar NOT NULL,
    city varchar NOT NULL,
    state varchar NOT NULL,
    country varchar NOT NULL
);
GRANT SELECT ON TABLE api.address TO web_anon;
GRANT SELECT ON TABLE api.address_nonpartitioned TO web_anon;

--Create Actor Partitioned Table
DROP TABLE IF EXISTS api.actor CASCADE;
DROP TABLE IF EXISTS api.actor_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.actor
(
    id integer NOT NULL,
    name varchar NOT NULL,
    primary_address_id int NOT NULL,
    secondary_address_id int,
    country varchar NOT NULL,
	CONSTRAINT actor_pkey PRIMARY KEY (id, country),
    CONSTRAINT primary_address FOREIGN KEY (primary_address_id, country) REFERENCES api.address (id, country),
    CONSTRAINT secondary_address FOREIGN KEY (secondary_address_id, country) REFERENCES api.address (id, country)
)PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.actor_usa PARTITION OF api.actor FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.actor_default PARTITION OF api.actor DEFAULT;
--Create Actor Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.actor_nonpartitioned
(
    id integer PRIMARY KEY,
    name varchar NOT NULL,
    primary_address_id int NOT NULL,
    secondary_address_id int,
    CONSTRAINT primary_address FOREIGN KEY (primary_address_id) REFERENCES api.address_nonpartitioned (id),
    CONSTRAINT secondary_address FOREIGN KEY (secondary_address_id) REFERENCES api.address_nonpartitioned (id)
);
GRANT SELECT ON TABLE api.actor TO web_anon;
GRANT SELECT ON TABLE api.actor_nonpartitioned TO web_anon;

--Create Director Partitioned Table
DROP TABLE IF EXISTS api.director CASCADE;
DROP TABLE IF EXISTS api.director_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.director
(
    id integer NOT NULL,
    name varchar NOT NULL,
    primary_address_id int NOT NULL,
    secondary_address_id int,
    country varchar NOT NULL,
    CONSTRAINT director_pkey PRIMARY KEY (id, country),
    CONSTRAINT primary_address FOREIGN KEY (primary_address_id, country) REFERENCES api.address (id, country),
    CONSTRAINT secondary_address FOREIGN KEY (secondary_address_id, country) REFERENCES api.address (id, country)
) PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.director_usa PARTITION OF api.director FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.director_default PARTITION OF api.director DEFAULT;
--Create Director Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.director_nonpartitioned
(
    id integer PRIMARY KEY,
    name varchar NOT NULL,
    primary_address_id int NOT NULL,
    secondary_address_id int,
    CONSTRAINT primary_address FOREIGN KEY (primary_address_id) REFERENCES api.address_nonpartitioned (id),
    CONSTRAINT secondary_address FOREIGN KEY (secondary_address_id) REFERENCES api.address_nonpartitioned (id)
);
GRANT SELECT ON TABLE api.director TO web_anon;
GRANT SELECT ON TABLE api.director_nonpartitioned TO web_anon;

--Create Film Partitioned Table
DROP TABLE IF EXISTS api.film CASCADE;
DROP TABLE IF EXISTS api.film_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.film
(
    id integer NOT NULL,
    director_id integer NOT NULL,
    title varchar NOT NULL,
    year integer NOT NULL,
    rating integer NOT NULL,
    country varchar NOT NULL,
    CONSTRAINT film_pkey PRIMARY KEY (id, country),
    CONSTRAINT fk_film_director FOREIGN KEY (director_id, country) REFERENCES api.director (id, country)
) PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.film_usa PARTITION OF api.film FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.film_default PARTITION OF api.film DEFAULT;
--Create Film Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.film_nonpartitioned
(
    id integer PRIMARY KEY,
    director_id integer NOT NULL,
    title varchar NOT NULL,
    year integer NOT NULL,
    rating integer NOT NULL,
    CONSTRAINT fk_film_director FOREIGN KEY (director_id) REFERENCES api.director_nonpartitioned (id)
);
GRANT SELECT ON TABLE api.film TO web_anon;
GRANT SELECT ON TABLE api.film_nonpartitioned TO web_anon;

--Create Role Partitioned Table
DROP TABLE IF EXISTS api.role CASCADE;
DROP TABLE IF EXISTS api.role_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.role
(
    role_id integer NOT NULL,
    film_id integer NOT NULL,
    actor_id integer NOT NULL,
    character_name varchar NOT NULL,
    country varchar NOT NULL,
    CONSTRAINT fk_film_role FOREIGN KEY (film_id, country) REFERENCES api.film (id, country),
    CONSTRAINT fk_role_actor FOREIGN KEY (actor_id, country) REFERENCES api.actor (id, country)
) PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.role_usa PARTITION OF api.role FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.role_default PARTITION OF api.role DEFAULT;
--Create Role Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.role_nonpartitioned
(
    role_id integer NOT NULL,
    film_id integer NOT NULL,
    actor_id integer NOT NULL,
    character_name varchar NOT NULL,
    CONSTRAINT fk_film_role FOREIGN KEY (film_id) REFERENCES api.film_nonpartitioned (id),
    CONSTRAINT fk_role_actor FOREIGN KEY (actor_id) REFERENCES api.actor_nonpartitioned (id)
);
GRANT SELECT ON TABLE api.role TO web_anon;
GRANT SELECT ON TABLE api.role_nonpartitioned TO web_anon;

--Create Competition Partitioned Table
DROP TABLE IF EXISTS api.competition CASCADE;
DROP TABLE IF EXISTS api.competition_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.competition
(
    id integer NOT NULL,
    name varchar NOT NULL,
    year varchar NOT NULL,
    country varchar NOT NULL,
	CONSTRAINT competition_pkey PRIMARY KEY (id, country)
)PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.competition_usa PARTITION OF api.competition FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.competition_default PARTITION OF api.competition DEFAULT;
--Create Competition Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.competition_nonpartitioned
(
    id integer PRIMARY KEY,
    name varchar NOT NULL,
    year varchar NOT NULL
);
GRANT SELECT ON TABLE api.competition TO web_anon;
GRANT SELECT ON TABLE api.competition_nonpartitioned TO web_anon;

--Create Nomination Partitioned Table
DROP TABLE IF EXISTS api.nomination CASCADE;
DROP TABLE IF EXISTS api.nomination_nonpartitioned CASCADE;
CREATE TABLE IF NOT EXISTS api.nomination
(
    nomination_id integer NOT NULL,
    film_id integer NOT NULL,
    competition_id integer NOT NULL,
    award varchar NOT NULL,
    country varchar NOT NULL,
    CONSTRAINT fk_film_nomination FOREIGN KEY (film_id, country) REFERENCES api.film (id, country),
    CONSTRAINT fk_nomination_competition FOREIGN KEY (competition_id, country) REFERENCES api.competition (id, country)
) PARTITION BY LIST (country);
CREATE TABLE IF NOT EXISTS api.nomination_usa PARTITION OF api.nomination FOR VALUES IN ('USA');
CREATE TABLE IF NOT EXISTS api.nomination_default PARTITION OF api.nomination DEFAULT;
--Create Nomination Non-Partitioned Table
CREATE TABLE IF NOT EXISTS api.nomination_nonpartitioned
(
    nomination_id integer NOT NULL,
    film_id integer NOT NULL,
    competition_id integer NOT NULL,
    award varchar NOT NULL,
    CONSTRAINT fk_film_nomination FOREIGN KEY (film_id) REFERENCES api.film_nonpartitioned (id),
    CONSTRAINT fk_nomination_competition FOREIGN KEY (competition_id) REFERENCES api.competition_nonpartitioned (id)
);
GRANT SELECT ON TABLE api.nomination TO web_anon;
GRANT SELECT ON TABLE api.nomination_nonpartitioned TO web_anon;

--Insert Data into Partitioned Tables
INSERT INTO api.address(id, street, city, state, country) VALUES
(1,'328 Main','Los Angeles','CA','USA'),
(2,'101 Mannor','Los Angeles','CA','USA'),
(3,'22291 Long Bow','San Fransico','CA','USA'),
(4,'22 Boward','Boston','MA','USA'),    
(5,'1193 Leafline','Taos','NM','USA'),  
(6,'213 Goose Ln','Fairbanks','AK','USA'),
(7,'328 Devenshire','Aspen','CO','USA'),
(8,'1122 Leafline','Taos','NM','USA'),  
(9,'2223 Northline','Denver','CO','USA'),
(10,'38 Ripple','New York','NY','USA'),
(11,'3229 Whispering Woods','Atlanta','GA','USA'),
(12,'122 Patrice','Jackson','WY','USA');
INSERT INTO api.actor(id, name, primary_address_id, secondary_address_id, country) VALUES 
(1,'Harrison Ford',1,11,'USA'),
(2,'Mark Hamill',2,null,'USA'),
(3,'Jonny Depp',3,12,'USA'),
(4,'Orlando Bloom',4,9,'USA'),
(5,'Emily Blunt',7,10,'USA');
INSERT INTO api.director(id, name, primary_address_id, secondary_address_id, country) VALUES
(1,'George Lucas',5,9,'USA'),
(2,'Gore Verbinski',6,null,'USA'),
(3,'Tim Burton',8,null,'USA'),
(4,'John Krasinski',7,10,'USA');
INSERT INTO api.film(id, director_id, title, year, rating, country) VALUES
(1,1,'Star Wars',1977,5,'USA'),
(2,2,'Pirates of the Carribean',1992,5,'USA'),
(3,3,'Edward Scissorhands',1998,3,'USA'),
(4,4,'Quiet Place 2',2021,4,'USA');
INSERT INTO api.role(role_id, film_id, actor_id, character_name, country) VALUES
(3,2,3,'Jack Sparrow','USA'),
(4,2,4,'Will Turner','USA'),
(5,3,3,'Edward','USA'),
(6,4,5,'Evelyn Abbott','USA'),
(1,1,1,'Han Solo','USA'),
(2,1,2,'Luke Skywalker','USA');
INSERT INTO api.competition(id, name, year, country) VALUES 
(1,'Oscars', '1998', 'USA'),
(2,'Golden Globes','2021','USA');
INSERT INTO api.nomination(nomination_id, film_id, competition_id, award, country) VALUES
(1,3,1,'Best Animation','USA'),
(2,4,2,'Best Supporting Actress','USA');

--Insert Data into Non-Partitioned Tables
INSERT INTO api.address_nonpartitioned(id, street, city, state, country) VALUES
(1,'328 Main','Los Angeles','CA','USA'),
(2,'101 Mannor','Los Angeles','CA','USA'),
(3,'22291 Long Bow','San Fransico','CA','USA'),
(4,'22 Boward','Boston','MA','USA'),    
(5,'1193 Leafline','Taos','NM','USA'),  
(6,'213 Goose Ln','Fairbanks','AK','USA'),
(7,'328 Devenshire','Aspen','CO','USA'),
(8,'1122 Leafline','Taos','NM','USA'),  
(9,'2223 Northline','Denver','CO','USA'),
(10,'38 Ripple','New York','NY','USA'),
(11,'3229 Whispering Woods','Atlanta','GA','USA'),
(12,'122 Patrice','Jackson','WY','USA');
INSERT INTO api.actor_nonpartitioned(id, name, primary_address_id, secondary_address_id) VALUES 
(1,'Harrison Ford',1,11),
(2,'Mark Hamill',2,null),
(3,'Jonny Depp',3,12),
(4,'Orlando Bloom',4,9),
(5,'Emily Blunt',7,10);
INSERT INTO api.director_nonpartitioned(id, name, primary_address_id, secondary_address_id) VALUES
(1,'George Lucas',5,9),
(2,'Gore Verbinski',6,null),
(3,'Tim Burton',8,null),
(4,'John Krasinski',7,10);
INSERT INTO api.film_nonpartitioned(id, director_id, title, year, rating) VALUES
(1,1,'Star Wars',1977,5),
(2,2,'Pirates of the Carribean',1992,5),
(3,3,'Edward Scissorhands',1998,3),
(4,4,'Quiet Place 2',2021,4);
INSERT INTO api.role_nonpartitioned(role_id, film_id, actor_id, character_name) VALUES
(3,2,3,'Jack Sparrow'),
(4,2,4,'Will Turner'),
(5,3,3,'Edward'),
(6,4,5,'Evelyn Abbott'),
(1,1,1,'Han Solo'),
(2,1,2,'Luke Skywalker');
INSERT INTO api.competition_nonpartitioned(id, name, year) VALUES 
(1,'Oscars', '1998'),
(2,'Golden Globes','2021');
INSERT INTO api.nomination_nonpartitioned(nomination_id, film_id, competition_id, award) VALUES
(1,3,1,'Best Animation'),
(2,4,2,'Best Supporting Actress');

laurenceisla · 2021-11-03T17:22:12Z

@BakerHughesCharlesSewell Thanks for the SQL script!

Second Level table relationships do not work for partitioned tables

Partition table is unable to understand relationship /film?select=title,actor(name)

Non-Partitioned table works /film_nonpartitioned?select=title,actor_nonpartitioned(name)

This request /film?select=title,actor(name) returns the following for me:

{
    "hint": "By following the 'details' key, disambiguate the request by changing the url to /origin?select=relationship(*) or /origin?select=target!relationship(*)",
    "details": [
        {
            "relationship": "api.role[fk_film_role][fk_role_actor]",
            "origin": "api.film",
            "cardinality": "m2m",
            "target": "api.actor"
        },
        {
            "relationship": "api.role_usa[fk_film_role][fk_role_actor]",
            "origin": "api.film",
            "cardinality": "m2m",
            "target": "api.actor"
        },
        {
            "relationship": "api.role_default[fk_film_role][fk_role_actor]",
            "origin": "api.film",
            "cardinality": "m2m",
            "target": "api.actor"
        }
    ],
    "message": "More than one relationship was found for film and actor"
}

Which means that PostgREST can reach the actor table but there's more than one way to do it, i.e. through the partitions. To disambiguate you should build the query using a hint, like this /film?select=title,actor!role(name) to use the role table.

This means that you could also use the partitions, like /film?select=title,actor!role_usa(name) or /film?select=title,actor!role_default(name) after granting permissions to select them.

Let us know if that solves the issue.

BakerHughesCharlesSewell · 2021-11-03T18:49:41Z

@laurenceisla - The existence of table partitions should be hidden. When PostgreSQL creates the partitioned Role table, it automatically generates foreign keys directly to the partitioned tables (see image below) which is apparently confusing the Embedded Resource PostgREST query with too many paths to follow.

As a work around, the following approaches can be followed:

Use nested resource embedding by including Role to get to Actor /film?select=title,role(actor(name))
[{"title": "Star Wars",
"role": [
{"actor": {"name": "Harrison Ford"}}, {"actor": {"name": "Mark Hamill"}}
]
}]
Use the Hint you provided /film?select=title,actor!role(name)
[{"title": "Star Wars",
"actor": [{"name": "Harrison Ford"}, {"name": "Mark Hamill"}]
}]

A better approach would be to ignore foreign keys for partitioned tables and only cache the foreign key relationships for the primary table. Ideally, the same PostgREST query should work for both partitioned and non-partitioned tables.

wolfgangwalther · 2021-11-03T19:36:11Z

The existence of table partitions should be hidden.

I agree.

Two approaches:

Just ignore all partitions in the schema cache already, via pg_class.relispartition. Although that would be a breaking change...
Mark partitions and their FK relationships as such - and when choosing the correct embedding relationship treat the partitioned table and it's partition as "the same" and use the partitioned table for embedding by default, unless overridden by hint.

The second approach is a bit more complicated, but probably what everyone would expect.

laurenceisla · 2021-11-03T20:26:57Z

The existence of table partitions should be hidden.

I agree.

Ah, I understand the issue now.

Mark partitions and their FK relationships as such - and when choosing the correct embedding relationship treat the partitioned table and it's partition as "the same" and use the partitioned table for embedding by default, unless overridden by hint.

I agree on this approach. Unless the partitions shouldn't be used in embedding at all, I think this should be the go to.

wolfgangwalther · 2021-11-04T07:35:10Z

Unless the partitions shouldn't be used in embedding at all

I thought about that, too - but there could also be a case where only a partition is in the exposed table and not the partitioned table. Or where you have a view based on a partition etc. - so we should still allow to explicitly specify partitions.

BakerHughesCharlesSewell · 2021-11-04T15:01:41Z

Partitions are typically created when your table is extremely large to virtually divide the table into smaller segments to improve query performance. As a best practice, we do not expose the underlying partitioned tables since table security policies are defined at the parent and not the individual partitions.

While someone might consider a View targeting LIST or RANGE partitions, this makes no sense for HASH partitions. While it is technically possible to create a View that directly queries a partition table, this is a brittle design since the partition can be dropped or renamed and is completely unnecessary since you can always have the view query the parent table with the partition key value as an input to the View.

In my opinion, the default behavior would be to prune off the foreign keys for the partitioned tables to avoid the need for the odd hint nomenclature. You could add a startup parameter in the PostgREST config file to control this behavior so that someone could retain those foreign keys and leverage the hint methodology.

wolfgangwalther · 2021-11-05T07:32:09Z

Following this logic, it would make most sense to just completely ignore partitions everywhere. In the schema cache, in the OpenApi output - and certainly for embedding relationships, too.

Should also be simpler to do.

It is a breaking change. Although there is a workaround, by just creating views to the parent table that selects by partition key.

steve-chavez · 2021-11-06T02:02:58Z

Mark partitions and their FK relationships as such - and when choosing the correct embedding relationship treat the partitioned table and it's partition as "the same" and use the partitioned table for embedding by default, unless overridden by hint.

Hm, this approach sounds similar to how we do self-joins(#1643 (comment)) now, which are a special case that we want to get rid off. I'd say we should avoid adding more special cases, these complicate the codebase and are also hard to document(inconsistent with the feature set and complicated for the user).

Just ignore all partitions in the schema cache already, via pg_class.relispartition. Although that would be a breaking change...

Agree. We could add a config param as Charles suggested to avoid the breaking change but I think ignoring the partitions is the right behavior. We can take advantage of the new major version to do this breaking change now.

In regards to a config param, maybe it could be like:

db-detect-relationships-on = "tables,views" # default
db-detect-relationships-on = "tables,views,partitions" # custom
db-detect-relationships-on = "none" # basically disabling embedding

Some users only expose functions through PostgREST so they don't really need embedding, that's where the none option could be useful(detecting relationships is generally fast, but could still take a while on big databases).

wolfgangwalther · 2021-11-06T11:46:10Z

We could add a config param

Hm. I feel like we don't need to add config options for everything. Every config option adds overhead (code complexity, ...). We should make sure to have config options for those things that really matter. I feel the same way about db-embed-default-join in #1949 - more is not always better.

Not including partitions in the embedding just makes sense. Plus there is any easy workaround if you really need to - create views.

steve-chavez · 2021-11-06T22:11:37Z

We should make sure to have config options for those things that really matter. I feel the same way about db-embed-default-join in #1949 - more is not always better.

Yeah, I agree. Maybe I was too quick to add that config, I see users already preferring to use !inner explicitly(#2009 (comment)). Since it's not released yet, I think we can remove it now. WDYT?

wolfgangwalther · 2021-11-07T13:52:12Z

I feel the same way about db-embed-default-join in #1949 - more is not always better.

Since it's not released yet, I think we can remove it now. WDYT?

Agreed!

wolfgangwalther changed the title ~~Resource embedding is not working on join partitioned tables using composite keys~~ Partitioned tables are not supported by everything related to schema cache Apr 2, 2021

wolfgangwalther added the bug label Apr 2, 2021

wolfgangwalther added the difficulty: beginner Pure Haskell task label Jun 8, 2021

laurenceisla mentioned this issue Aug 6, 2021

feat: Include partitioned tables into the schema cache #1903

Merged

5 tasks

steve-chavez added enhancement a feature, ready for implementation and removed bug labels Aug 11, 2021

laurenceisla closed this as completed in #1903 Aug 13, 2021

wolfgangwalther reopened this Nov 3, 2021

laurenceisla mentioned this issue Nov 12, 2021

Remove partitions from the schema cache #2022

Merged

5 tasks

steve-chavez mentioned this issue Nov 16, 2021

Remove partitions from the schema cache PostgREST/postgrest-docs#459

Merged

laurenceisla closed this as completed in #2022 Nov 18, 2021

This was referenced Nov 23, 2021

Remove the db-embed-default-join config #2034

Merged

Ensure only schemas in db-schemas and db-extra-search-path are scanned for the schema cache #2015

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Partitioned tables are not supported by everything related to schema cache #1783

Partitioned tables are not supported by everything related to schema cache #1783

bitlab-code commented Apr 1, 2021 •

edited

Loading

wolfgangwalther commented Apr 1, 2021

bitlab-code commented Apr 2, 2021 •

edited

Loading

wolfgangwalther commented Apr 2, 2021

baz-bakerhughes commented Jun 7, 2021

wolfgangwalther commented Jun 8, 2021

wolfgangwalther commented Nov 3, 2021

wolfgangwalther commented Nov 3, 2021

BakerHughesCharlesSewell commented Nov 3, 2021

laurenceisla commented Nov 3, 2021 •

edited

Loading

BakerHughesCharlesSewell commented Nov 3, 2021

wolfgangwalther commented Nov 3, 2021

laurenceisla commented Nov 3, 2021 •

edited

Loading

wolfgangwalther commented Nov 4, 2021

BakerHughesCharlesSewell commented Nov 4, 2021

wolfgangwalther commented Nov 5, 2021 •

edited

Loading

steve-chavez commented Nov 6, 2021 •

edited

Loading

wolfgangwalther commented Nov 6, 2021 •

edited

Loading

steve-chavez commented Nov 6, 2021

wolfgangwalther commented Nov 7, 2021

Partitioned tables are not supported by everything related to schema cache #1783

Partitioned tables are not supported by everything related to schema cache #1783

Comments

bitlab-code commented Apr 1, 2021 • edited Loading

Environment

Description of issue

wolfgangwalther commented Apr 1, 2021

bitlab-code commented Apr 2, 2021 • edited Loading

Environment

Steps to reproduce:

wolfgangwalther commented Apr 2, 2021

baz-bakerhughes commented Jun 7, 2021

wolfgangwalther commented Jun 8, 2021

wolfgangwalther commented Nov 3, 2021

wolfgangwalther commented Nov 3, 2021

BakerHughesCharlesSewell commented Nov 3, 2021

Tested Scenarios:

Work Around for Partitioned Table 2nd Level & Above Relationship Error

laurenceisla commented Nov 3, 2021 • edited Loading

BakerHughesCharlesSewell commented Nov 3, 2021

wolfgangwalther commented Nov 3, 2021

laurenceisla commented Nov 3, 2021 • edited Loading

wolfgangwalther commented Nov 4, 2021

BakerHughesCharlesSewell commented Nov 4, 2021

wolfgangwalther commented Nov 5, 2021 • edited Loading

steve-chavez commented Nov 6, 2021 • edited Loading

wolfgangwalther commented Nov 6, 2021 • edited Loading

steve-chavez commented Nov 6, 2021

wolfgangwalther commented Nov 7, 2021

bitlab-code commented Apr 1, 2021 •

edited

Loading

bitlab-code commented Apr 2, 2021 •

edited

Loading

laurenceisla commented Nov 3, 2021 •

edited

Loading

laurenceisla commented Nov 3, 2021 •

edited

Loading

wolfgangwalther commented Nov 5, 2021 •

edited

Loading

steve-chavez commented Nov 6, 2021 •

edited

Loading

wolfgangwalther commented Nov 6, 2021 •

edited

Loading