Recognize partitions in scaffolding

[roji]

General support for table partitioning is tracked by #1035. This issue is about recognizing partitions when scaffolding, and at the very least excluding them so they don't cause lots of useless entity types to be scaffolded.

[akillablitz]

Hey @roji - sorry to resurrect an old issue... but just come across this...

and at the very least excluding them so they don't cause lots of useless entity types to be scaffolded

It looks like scaffolding currently does ignore the parent partitioned table... this is not desirable for us.,. we'd like to scaffold both parent and partitions (just like the old inheritance partitioning would)... is there anything we can do to get them?

Edit - continuance.... why do I want to scaffold all of them?

• I can query from the parent Partitioned Table and get the benefit of seeing all the child records.
• I can mutate the Parent record and have it naturally CRUD the right partition... #winning.
• I can query / join / mutate child tables too... which is one of our use cases... partition by discriminator for FK

If these were timestamp related partitions (e.g. for a log or transactional table) , I could agree that scaffolding children is perhaps undesirable. But that is only 1 use case.

We're on .Net / EFCore 8...

[roji]

@akillablitz there's no (intentional) specific code in EFCore.PG for ignoring parent partitioned tables when scaffolding; but it could be that the query for tables somehow leaves them out... I'm also not quite sure whether scaffolding all partitions would be the right thing (and desirable for most users) - but I unfortunately don't have time to dive into this and investigate.

In any case, keep in mind that scaffolding generates the initial code for you, but you can also manually add anything that's missing (that's very much by design). So I'd recommend simply adding the parent partitioned table if that's what your application requires.

[akillablitz]

Thanks @roji - from crude testing, it certainly looks like I can't even target the parent partition with --table... so we will have to manually add it.. the extension capability is awesome and we leverage it heavily. Downside is it pushes unforeseen/unrelated/uncoupled maintenance onto developers or QA to discover.

If this comes up in future discussions, we'd like full flexibility to have access to both - parent partition (not available today) and children (available and scaffolded as first class tables with relations / navigational properties).

As always... thanks for this project! its awesome.

[Jure-BB]

there's no (intentional) specific code in EFCore.PG for ignoring parent partitioned tables when scaffolding; but it could be that the query for tables somehow leaves them out

@roji NpgsqlDatabaseModelFactory.GetTables query does indeed leave them out:

https://github.com/npgsql/efcore.pg/blob/837c2f21cf6e422ca674c54345848b342b6595cc/src/EFCore.PG/Scaffolding/Internal/NpgsqlDatabaseModelFactory.cs#L204

It should also check relkind for p (partitioned table).

Additional columns that contain useful information regarding partitions are relispartition and relhassubclass.

NpgsqlDatabaseCreator.CreateHasTablesCommand already checks for p:

https://github.com/npgsql/efcore.pg/blob/837c2f21cf6e422ca674c54345848b342b6595cc/src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs#L138

[JohnYoungers]

I just stumbled upon this as well: I'm trying to setup a YugabyteDB example that utilizes partitioning and tablespaces to geo-locate rows, and was unable to run dotnet ef dbcontext scaffold with the specific --table option (Unable to find a table in the database matching the selected table ...)

If I have:

my_geo_table
my_geo_table_us
my_geo_table_eu

In code, I'd just want to reference the parent. I'm scaffolding from a temporary database, so I think as pre-req step I'll pull out the partitioning, now that I understand where the issue is (I originally figured it was an incompatibility problem with yugabyte)