PostgREST
v10: Randomly detected FK relationships / embeddings on views
Environment
- PostgreSQL version: 14.5
- PostgREST version: v10.0.0
Description of issue
With v10.0.0 some relationships in the schema are detected seemingly randomly. Start PostgREST 10 times and - for me - about 1-2 out of 10 times, it detects one set of relationships, while in the other 8-9 cases in detects another set.
Example:
CREATE SCHEMA hidden;
CREATE TABLE hidden.t (
id INT PRIMARY KEY,
parent INT REFERENCES hidden.t,
type TEXT
);
CREATE VIEW v1 AS
SELECT parent.parent AS grandparent,
child.parent,
child.id
FROM hidden.t AS parent
JOIN hidden.t AS child
ON child.parent = parent.id
WHERE child.type = 'A';
CREATE VIEW v2 AS
SELECT parent.parent AS grandparent,
child.parent,
child.id
FROM hidden.t AS parent
JOIN hidden.t AS child
ON child.parent = parent.id
WHERE child.type = 'B';
CREATE VIEW v3 AS
SELECT parent.parent AS grandparent,
child.parent,
child.id
FROM hidden.t AS parent
JOIN hidden.t AS child
ON child.parent = parent.id
WHERE child.type = 'C';
Sometimes, I get this OpenAPI output:
"v1": {
"properties": {
"grandparent": {
"format": "integer",
"type": "integer"
},
"id": {
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
},
"parent": {
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
}
},
"type": "object"
},
"v2": {
"properties": {
"grandparent": {
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"id": {
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
},
"parent": {
"format": "integer",
"type": "integer"
}
},
"type": "object"
},
"v3": {
"properties": {
"grandparent": {
"format": "integer",
"type": "integer"
},
"id": {
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
},
"parent": {
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
}
},
"type": "object"
}
But most of the time I get:
"v1": {
"properties": {
"grandparent": {
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"id": {
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
},
"parent": {
"format": "integer",
"type": "integer"
}
},
"type": "object"
},
"v2": {
"properties": {
"grandparent": {
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"id": {
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
},
"parent": {
"format": "integer",
"type": "integer"
}
},
"type": "object"
},
"v3": {
"properties": {
"grandparent": {
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"id": {
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
},
"parent": {
"format": "integer",
"type": "integer"
}
},
"type": "object"
}
So most of the time, the FKs are put on the grandparent column. But sometimes they appear on the parent column.
For one, this breaks my CI workflow, which includes a snapshot of the OpenAPI output of my schema. This randomly breaks.
But then, I wondered whether this could also affect embedding for certain requests. And it does:
curl 'http://localhost:3000/v1?select=*,grandparent(*)'
This request works most of the time. But in the rare case, I get:
{
"v1": {
"code": "PGRST200",
"details": null,
"hint": "Verify that 'v1' and 'grandparent' exist in the schema 'public' and that there is a foreign key relationship between them. If a new relationship was created, try reloading the schema cache.",
"message": "Could not find a relationship between 'v1' and 'grandparent' in the schema cache"
}
}
In v9 the results were at least predictable. Although I'm not exactly sure which relationships should be detected in this case. My first guess would be that there should always be relationships on both parent and grandparent, right?
At the same time it seems random that those relationships are always pointing to v1 - why not v2 or v3?
I cannot reproduce the inconsistency in the responses, maybe it has to do with the size of the db? But still, there's a difference between the foreign keys in v1 and the rest of the views in my result:
"v1":
{
"properties":
{
"grandparent":
{
"format": "integer",
"type": "integer"
},
"parent":
{
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"id":
{
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
}
},
"type": "object"
},
"v3":
{
"properties":
{
"grandparent":
{
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"parent":
{
"format": "integer",
"type": "integer"
},
"id":
{
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
}
},
"type": "object"
},
"v2":
{
"properties":
{
"grandparent":
{
"description": "Note:\nThis is a Foreign Key to `v1.id`.<fk table='v1' column='id'/>",
"format": "integer",
"type": "integer"
},
"parent":
{
"format": "integer",
"type": "integer"
},
"id":
{
"description": "Note:\nThis is a Primary Key.<pk/>",
"format": "integer",
"type": "integer"
}
},
"type": "object"
}
At the same time it seems random that those relationships are always pointing to v1 - why not v2 or v3?
When the table is exposed in the same schema as the views, these detect the relationship from the original table t, not from v1. Maybe the logic was to find the main table, but since it's not present in the cache, the first view is the one that's used instead.
I cannot reproduce the inconsistency in the responses, maybe it has to do with the size of the db?
Ah, I guess I forgot to mention that I'm not only restarting PostgREST, but also re-create the PostgreSQL cluster each time from scratch. So maybe it has to do with some internal ordering of database objects in PostgreSQL or so, although I'd expect this to be reproducible, too.
But still, there's a difference between the foreign keys in v1 and the rest of the views in my result:
Ok, so you can at least reproduce the wrong result.
When the table is exposed in the same schema as the views, these detect the relationship from the original table t, not from v1.
Yes, I observed this as well - and with the tables in the exposed schema, I did not get the randomness either - the result was at least consistent.
Maybe the logic was to find the main table, but since it's not present in the cache, the first view is the one that's used instead.
I'm not exactly sure what the logic for the OpenAPI output is, anyway. It doesn't seem like all FKs are shown here, but maybe only the "first" one? Whatever "first" means. But it seems like this affects embedding itself, too, so there must be something going on in the schema cache as well.
@wolfgangwalther Was this one fixed with https://github.com/PostgREST/postgrest/pull/2521 too?
Unfortunately it is still present. I will try to put together a test-case, just not sure how, yet.
Trying to reproduce it in postgrest's nix-shell right now, I now consistently get the same result as @laurenceisla. So I can't get the variance between runs, but the 3 view definitions have different FKs detected. That means:
- the following requests fail:
/v1?select=*,v1!grandparent(*) /v2?select=*,v2!parent(*) /v3?select=*,v3!parent(*) - the following requests pass:
/v1?select=*,v1!parent(*) /v2?select=*,v2!grandparent(*) /v3?select=*,v3!grandparent(*)
v9.0.0 seemed to be more consistent at least: It failed to detect any relationships for the test-case here. Seems to be related to changes regarding embedding of self-referencing FKs?
I ran git bisect again and it tells me that d2719420 broke this, too.
I ran git bisect again and it tells me that https://github.com/PostgREST/postgrest/commit/d2719420f46a537a75d76a4666f8bffec582d4f8 broke this, too.
I'm not sure whether "broke" is the correct term here. It's that commit that introduced the change. But after inspecting the schema cache dump before and after it looks like the schema cache content was a big mess before as well for this test-case. So it seems the restructuring only surfaced this problem. We moved from consistently-wrong to randomly-correct.
I debugged a bit more: The problem is, that the allViewsKeyDependencies query returns one row with {"(parent,grandparent)","(parent,parent)"} for column_dependencies. The array here is meant to store multiple columns for a multi-column foreign key - but not multiple references to the same FK.
I have a similar problem, possibly the same:
I have a table in "public" schema which references a table in the "auth" schema (this is a Supabase db, btw). Those references are (consistently, it seems, so far at least) wrong - the description contains a <fk pointing to a incorrect table/column.
To be precise: table public.A has column created_by which points to auth.users.id table public.B has column created_by which points to auth.users.id Postgrest reports that public.A.created_by points to B.created_by.
Is this the same bug, or something else?
Is this the same bug, or something else?
This doesn't sound like the same thing as in this issue. Can you please create a minimal reproducer and report it in a new issue?
Is this the same bug, or something else?
This doesn't sound like the same thing as in this issue. Can you please create a minimal reproducer and report it in a new issue?
I created a new supabase-project to try to reproduce it, but in this case I did not get any FK-info at all. Besides, supabase uses Postgrest 9.0.1.20220717, not the latest. I will work around this issue on my side and hopefully it will be fixed at some point in time.
However, please keep up the good work. I, at least, appreciate a correct and detailed OpenAPI document :)
I'm working with a view but found that postgrest was dropping these PK notes in where I was just trying to do comment on documentation (because it's a view anyway) and they would get merged. IT turned out it was introspection by postgrest on the view seeing that one of my columns references a PK column in one of the tables selected in the view. I was able to just pull that column from a different place in the select so it couldn't follow that path.
Note:
This is a Primary Key.<pk/>
