Re: 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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]/messages/by-id/CAM-w4HOZ5fPS7GoCTTrW42q01+wPrOWFCnr9H0iDyVTZP2H1CA@mail.gmail.com.
Thanks,
Amit
[1]: /messages/by-id/CAM-w4HOZ5fPS7GoCTTrW42q01+wPrOWFCnr9H0iDyVTZP2H1CA@mail.gmail.com
/messages/by-id/CAM-w4HOZ5fPS7GoCTTrW42q01+wPrOWFCnr9H0iDyVTZP2H1CA@mail.gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | p | table | amit
public | p1 | table | amit
public | p1a | table | amit
(3 rows)
select tablename from pg_tables where schemaname = 'public';
tablename
-----------
p
p1
p1a
(3 rows)
select table_name from information_schema.tables where table_schema =
'public';
table_name
------------
p
p1
p1a
(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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers