Panics with certain Pg FKs about sea-schema HOT 10 CLOSED

I realized I can give you something to reproduce it with. I attached a pg_dump of the musicbrainz schema without any data:
mb.sql.txt

from sea-schema.

It's not really my schema, it's from the musicbrainz project - https://musicbrainz.org/doc/MusicBrainz_Database/Schema

As for getting foreign keys, there's some good discussion here, I think - https://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk

from sea-schema.

I dug into this some more and I think I found what's causing it. The artist_release table has two partitions named artist_release_nonva and artist_release_va. Here's the SQL to create all of these:

CREATE TABLE artist_release (
    -- `is_track_artist` is TRUE only if the artist came from a track
    -- AC and does not also appear in the release AC. Track artists
    -- that appear in the release AC are not stored.
    is_track_artist                     BOOLEAN NOT NULL,
    artist                              INTEGER NOT NULL, -- references artist.id, CASCADE
    first_release_date                  INTEGER,
    catalog_numbers                     TEXT[],
    country_code                        CHAR(2),
    barcode                             BIGINT,
    -- Prior to adding these materialized tables, we'd order releases
    -- by name only if all other attributes where equal. It's not too
    -- common that an artist will have tons of releases with no dates,
    -- catalog numbers, countries, or barcodes (though it can be seen
    -- on some big composers). As a compromise between dropping the
    -- name sorting and having to store the entire name here (which,
    -- as a reminder, is duplicated for every artist on the release),
    -- we only store the first character of the name for sorting.
    sort_character                      CHAR(1) COLLATE musicbrainz NOT NULL,
    release                             INTEGER NOT NULL -- references release.id, CASCADE
) PARTITION BY LIST (is_track_artist);

CREATE TABLE artist_release_nonva
    PARTITION OF artist_release FOR VALUES IN (FALSE);

CREATE TABLE artist_release_va
    PARTITION OF artist_release FOR VALUES IN (TRUE);

So I think we end up' seeing the same FK to artist 3 times because of this. There's also an identical partitioning scheme for the artist_release_group table.

I'm not sure exactly what the right fix is.

The simple solution would be to simply ignore the partitions and only generate entities for the main table. Then any FKs on those partitions could also be ignored.

But that's really not great. It'd probably be better to generate entities for both the parent table and its child partitions. I think the children should reuse the parent's FKs exactly, rather than having them retrieved separately? I'm not really sure since I haven't used Pg's partitioning features much. Is there a case where a child table would have different FKs than the parent?

from sea-schema.

Hey @autarch, thank you so much for the report! We will investigate it.

from sea-schema.

I realized I can give you something to reproduce it with. I attached a pg_dump of the musicbrainz schema without any data: mb.sql.txt

Oh my, your schema is huge!
And after doing some research online. Seems there is not a easy and standard way of getting a list of foreign keys from Postgres.

from sea-schema.

Hey @autarch, you could try #30 by running

cargo run --manifest-path tests/writer/postgres/Cargo.toml

You might need to update tests/writer/postgres/src/main.rs accordingly

use sea_schema::postgres::discovery::SchemaDiscovery;
use sea_schema::sea_query::PostgresQueryBuilder;
use sqlx::PgPool;

#[async_std::main]
async fn main() {
    let connection = PgPool::connect("postgres://sea:sea@localhost/sea_schema_issues_28")
        .await
        .unwrap();

    let schema_discovery = SchemaDiscovery::new(connection, "musicbrainz");

    let schema = schema_discovery.discover().await;

    for table in schema.tables.iter() {
        println!("{};", table.write().to_string(PostgresQueryBuilder));
        println!();
    }
}

from sea-schema.

Do we have a minimal reproducible schema?

from sea-schema.

Do we have a minimal reproducible schema?

Nope. I still don't understand what happen here. Inside the "information_schema"."referential_constraints".

select * from "information_schema"."referential_constraints"

will get... two set of identical rows... for some reason

from sea-schema.

Do we have a minimal reproducible schema?

Just added, 3ee593d, but it's not that minimal loll

from sea-schema.

So I think we end up' seeing the same FK to artist 3 times because of this. There's also an identical partitioning scheme for the artist_release_group table.

Yes... I think so.

But that's really not great. It'd probably be better to generate entities for both the parent table and its child partitions.

I think it did discover the parent and both partitions

• https://github.com/SeaQL/sea-schema/runs/4469888248#step:11:798

from sea-schema.