Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-13 Thread Amit Langote 
On 2017/04/14 5:28, Robert Haas wrote:
> On Thu, Apr 6, 2017 at 3:14 AM, Amit Langote
>  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

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-13 Thread Robert Haas 
On Thu, Apr 6, 2017 at 3:14 AM, Amit Langote
 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

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-06 Thread Amit Langote 
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
>>  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

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-04-06 Thread Noah Misch 
On Wed, Jan 25, 2017 at 01:19:00PM -0500, Robert Haas wrote:
> On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut
>  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

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-03-30 Thread Amit Langote 
On 2017/01/26 3:19, Robert Haas wrote:
> On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut
>  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




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-01-25 Thread Robert Haas 
On Wed, Jan 25, 2017 at 1:04 PM, Peter Eisentraut
 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

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-01-25 Thread Peter Eisentraut 
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

Re: [HACKERS] Declarative partitioning vs. information_schema

2017-01-18 Thread Robert Haas 
On Tue, Jan 10, 2017 at 4:17 AM, Amit Langote
 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