Postgres 11 partitions support

[lskrajny]

trafficstars

Are there any work/plans to support declarative partitioning introduced in PostgreSQL 11?

Current behaviour is lets say i have got "fruit" table partitioned by type with separate partitions for each type ("apple", "banana", etc.)

Postgraphile generates schema for child tables and not for parent one :/

[benjie]

In general, features like this are added to PostGraphile in the following ways:

1. Someone offers to write the functionality and works (hopefully with my input) to plan and introduce the feature, possibly over multiple PRs to keep review manageable.
2. Someone wants the functionality and pays me to implement it. Theoretically multiple interested parties could "club together" to pay for the feature but this hasn't happened yet.
3. I want the feature for something I am building, so I build it.
4. Enough sponsors, contributors and users say that they want the feature that I dedicate my sponsored time to working on it.

Option 4 is how most things get done, but it takes a long time because my sponsored time also covers a lot of other duties, and because I have my own perceived priorities on which tasks or features should be built first. Excitingly, as my sponsorship levels grow, the rate of change in option 4 is increasing 🙂

The demand for this feature is not (yet) particularly high. People who are interested in this feature should use the :+1: emoji response on the above comment.

[ben-pr-p]

@ lskrajny you could always use smart comments + a view to force postgraphile to do the right thing – in this case it would be:

create view fruits as select * from fruit;

comment on table apple is '@omit';
comment on table bananas is '@omit';

[ab-pm]

Hi @lskrajny, I plan to work on #387 to introduce interfaces. While table inheritance is imo not the best way of representing shared fields between types in postgres, I do plan to cover it (or at least prepare for it). Aren't declarative partitions a very similar thing? If you could post some examples of how you use declarative partitioning and how you would expect the generated schema to look like (in enough detail that I can use them as tests), I can have a look at those as well.

[wesselvdv]

I'd love partitions to become first class citizens.

[wesselvdv]

I am actually running into a similar issue with timescale Hypertables. This is chunking the table in multiple different partitions dynamically and this seems to awry with wal2json and live queries. Since the chance is reported for the chunk (child) instead of the hypertable itself. (parent)

[joaopaulobdac]

Hey @wesselvdv I'm also using timescale for my project and I'm thinking about migrating from prisma to graphile. Did you solve this issue, and have you ran into other problems with graphile+timescale?

[wesselvdv]

Hey @wesselvdv I'm also using timescale for my project and I'm thinking about migrating from prisma to graphile. Did you solve this issue, and have you ran into other problems with graphile+timescale?

Yes, I did solve this issue for subscriptions, and I didn’t run into any issues with queries or mutations with timescale. You can query hypertables perfectly fine.

[joaopaulobdac]

Nice. When you said subscriptions did you mean live queries too?

[wesselvdv]

Yes, that's what I mean. :-)

[benjie]

The issue is that where we filter on rel.relkind here:

https://github.com/graphile/graphile-engine/blob/1bc8cfefdab7a61fd7ad287bcdff66298352e308/packages/graphile-build-pg/src/plugins/introspectionQuery.js#L190

we're explicitly excluding partitioned tables (p) - see pg_class docs.

We should explicitly include the p tables, and explicitly exclude their partitions.

We also need to reflect the indexes.

[abner]

The issue is that where we filter on rel.relkind here:

https://github.com/graphile/graphile-engine/blob/1bc8cfefdab7a61fd7ad287bcdff66298352e308/packages/graphile-build-pg/src/plugins/introspectionQuery.js#L190

we're explicitly excluding partitioned tables (p) - see pg_class docs.

We should explicitly include the p tables, and explicitly exclude their partitions.

We also need to reflect the indexes.

There is some perspective to support partitions in postgraphile?

Here locally I patched the introspectionQuery.js to include the partitioned table (p) as suggested by @benjie

I would send a PR with this change.

[benjie]

V5 supports partitions out of the box; here's a test for it: https://github.com/graphile/crystal/blob/main/postgraphile/postgraphile/tests/queries/v4/partitions.test.graphql

[divya-sk]

I am raising one question addressing why Postraphile isn't exposing the parent table instead of the partition tables.

[benjie]

ref the identical question #1991