Thread: Re: [HACKERS] Declarative partitioning vs. information_schema
On Tue, Jan 10, 2017 at 4:17 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/01/10 14:44, Keith Fiske wrote:
>> Is there any reason for the exclusion of parent tables from the pg_tables
>> system catalog view? They do not show up in information_schema.tables as
>> well. I believe I found where to make the changes and I tested to make sure
>> it works for my simple case. Attached is my first attempt at patching
>> anything in core. Not sure if there's anywhere else this would need to be
>> fixed.
>
> That's an oversight. The original partitioning patch didn't touch
> information_schema.sql and system_views.sql at all. I added the relkind =
> 'P' check in some other views as well, including what your patch considered.
I took a look at this patch:
* The SQL for pg_seclabels contained an obvious syntax error.
* pg_seclabels should only return object types that could be used in
the SECURITY LABEL command, so it needs to return 'table' not
'partitioned table' for the new relkind.
* There's a pointless change from v.relkind = 'v' to v.relkind IN ('v').
* I don't see any indication on the Internet that "PARTITIONED TABLE"
is a legal value for the table type in information_schema.tables.
Unless we can find something official, I suppose we should just
display BASE TABLE in that case as we do in other cases. I wonder if
the schema needs some broader revision; for example, are there
information_schema elements intended to show information about
partitions?
* Even though unique/primary key/foreign key constraints can't
currently be defined on partitioned tables, it seems best to change
the related reference symmetrically with the others, because we might
support it in the future and then this would break again. Similarly
for key_column_usage.
Committed with fixes for those issues.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 1/18/17 2:32 PM, Robert Haas wrote: > Unless we can find something official, I suppose we should just > display BASE TABLE in that case as we do in other cases. I wonder if > the schema needs some broader revision; for example, are there > information_schema elements intended to show information about > partitions? Is it intentional that we show the partitions by default in \d, pg_tables, information_schema.tables? Or should we treat those as somewhat-hidden details? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 1/18/17 2:32 PM, Robert Haas wrote: >> Unless we can find something official, I suppose we should just >> display BASE TABLE in that case as we do in other cases. I wonder if >> the schema needs some broader revision; for example, are there >> information_schema elements intended to show information about >> partitions? > > Is it intentional that we show the partitions by default in \d, > pg_tables, information_schema.tables? Or should we treat those as > somewhat-hidden details? I'm not really sure what the right thing to do is there. I was hoping you had an opinion. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2017/01/26 3:19, Robert Haas wrote: > On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: >> On 1/18/17 2:32 PM, Robert Haas wrote: >>> Unless we can find something official, I suppose we should just >>> display BASE TABLE in that case as we do in other cases. I wonder if >>> the schema needs some broader revision; for example, are there >>> information_schema elements intended to show information about >>> partitions? >> >> Is it intentional that we show the partitions by default in \d, >> pg_tables, information_schema.tables? Or should we treat those as >> somewhat-hidden details? > > I'm not really sure what the right thing to do is there. I was hoping > you had an opinion. I guess this is an open item then. I think Greg Stark brought this up too on the original partitioning thread [1]. Thanks, Amit [1] https://www.postgresql.org/message-id/CAM-w4HOZ5fPS7GoCTTrW42q01%2BwPrOWFCnr9H0iDyVTZP2H1CA%40mail.gmail.com
On Wed, Jan 25, 2017 at 01:19:00PM -0500, Robert Haas wrote: > On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: > > On 1/18/17 2:32 PM, Robert Haas wrote: > >> Unless we can find something official, I suppose we should just > >> display BASE TABLE in that case as we do in other cases. I wonder if > >> the schema needs some broader revision; for example, are there > >> information_schema elements intended to show information about > >> partitions? > > > > Is it intentional that we show the partitions by default in \d, > > pg_tables, information_schema.tables? Or should we treat those as > > somewhat-hidden details? > > I'm not really sure what the right thing to do is there. I was hoping > you had an opinion. The bulk of operations that work on traditional tables also work on partitions and partitioned tables. The next closest kind of relation, a materialized view, is far less table-like. Therefore, I recommend showing both partitions and partitioned tables in those views. This is also consistent with the decision to use words like "partition" and "partitioned" in messages only when partitioning is relevant to the error. For example, ATWrongRelkindError() distinguishes materialized views from tables, but it does not distinguish tables based on their participation in partitioning.
On 2017/04/06 16:02, Noah Misch wrote: > On Wed, Jan 25, 2017 at 01:19:00PM -0500, Robert Haas wrote: >> On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut >> <peter.eisentraut@2ndquadrant.com> wrote: >>> On 1/18/17 2:32 PM, Robert Haas wrote: >>>> Unless we can find something official, I suppose we should just >>>> display BASE TABLE in that case as we do in other cases. I wonder if >>>> the schema needs some broader revision; for example, are there >>>> information_schema elements intended to show information about >>>> partitions? >>> >>> Is it intentional that we show the partitions by default in \d, >>> pg_tables, information_schema.tables? Or should we treat those as >>> somewhat-hidden details? >> >> I'm not really sure what the right thing to do is there. I was hoping >> you had an opinion. > > The bulk of operations that work on traditional tables also work on partitions > and partitioned tables. The next closest kind of relation, a materialized > view, is far less table-like. Therefore, I recommend showing both partitions > and partitioned tables in those views. This is also consistent with the > decision to use words like "partition" and "partitioned" in messages only when > partitioning is relevant to the error. For example, ATWrongRelkindError() > distinguishes materialized views from tables, but it does not distinguish > tables based on their participation in partitioning. +1 Thanks, Amit
On Thu, Apr 6, 2017 at 3:14 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote: >> The bulk of operations that work on traditional tables also work on partitions >> and partitioned tables. The next closest kind of relation, a materialized >> view, is far less table-like. Therefore, I recommend showing both partitions >> and partitioned tables in those views. This is also consistent with the >> decision to use words like "partition" and "partitioned" in messages only when >> partitioning is relevant to the error. For example, ATWrongRelkindError() >> distinguishes materialized views from tables, but it does not distinguish >> tables based on their participation in partitioning. > > +1 OK, whoever wants to write the patch, please step forward. /me steps backward. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2017/04/14 5:28, Robert Haas wrote:
> On Thu, Apr 6, 2017 at 3:14 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> The bulk of operations that work on traditional tables also work on partitions
>>> and partitioned tables. The next closest kind of relation, a materialized
>>> view, is far less table-like. Therefore, I recommend showing both partitions
>>> and partitioned tables in those views. This is also consistent with the
>>> decision to use words like "partition" and "partitioned" in messages only when
>>> partitioning is relevant to the error. For example, ATWrongRelkindError()
>>> distinguishes materialized views from tables, but it does not distinguish
>>> tables based on their participation in partitioning.
>>
>> +1
>
> OK, whoever wants to write the patch, please step forward.
Sorry, perhaps I'm missing something, but I thought there was no patch
left to be written, because the original patch (this thread) implemented
what Noah recommended.
As of HEAD (6cfaffc0ddc):
create table p (a int, b char) partition by list (a);
create table p1 partition of p for values in (1) partition by list (b);
create table p1a partition of p1 for values in ('a');
\d List of relationsSchema | Name | Type | Owner
--------+------+-------+-------public | p | table | amitpublic | p1 | table | amitpublic | p1a | table | amit
(3 rows)
select tablename from pg_tables where schemaname = 'public';tablename
-----------pp1p1a
(3 rows)
select table_name from information_schema.tables where table_schema =
'public';table_name
------------pp1p1a
(3 rows)
Also, it seems that this open item has been listed under Non-bugs, with
remark "firm support for status quo, lack of firm support for alternatives".
Thanks,
Amit