pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
table inheritance partition and indexes
7+ messages / 4 participants
[nested] [flat]

* table inheritance partition and indexes
@ 2022-11-23 15:31  Ted Toth <txtoth@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Ted Toth @ 2022-11-23 15:31 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

I've created a table with a number of indexes and then created a partition
table that inherits from it using "CREATE TABLE... INHERITS..." . I've then
queried pg_indexes on the parent for its indexes and tried creating
matching indexes on the child and attaching them to the parent table
indexes. However "ALTER TABLE ... ATTACH PARTITION ..." is returning the
error "<parent tables index name> is not a table partitioned index". I've
followed the inheritance partitioning example and read the "CREATE INDEX"
docs but I must be missing something, can anyone help me understand what
I'm doing wrong? I'm using version 13.

Ted

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: table inheritance partition and indexes
@ 2022-11-23 19:24  Ron <ronljohnsonjr@gmail.com>
  parent: Ted Toth <txtoth@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Ron @ 2022-11-23 19:24 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Out of curiosity, why INHERITS in v13 instead of PARTITION BY?

On 11/23/22 09:31, Ted Toth wrote:
> I've created a table with a number of indexes and then created a partition 
> table that inherits from it using "CREATE TABLE... INHERITS..." . I've 
> then queried pg_indexes on the parent for its indexes and tried creating 
> matching indexes on the child and attaching them to the parent table 
> indexes. However "ALTER TABLE ... ATTACH PARTITION ..." is returning the 
> error "<parent tables index name> is not a table partitioned index". I've 
> followed the inheritance partitioning example and read the "CREATE INDEX" 
> docs but I must be missing something, can anyone help me understand what 
> I'm doing wrong? I'm using version 13.
>
> Ted
>

-- 
Angular momentum makes the world go 'round.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: table inheritance partition and indexes
@ 2022-11-23 21:49  Ted Toth <txtoth@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Ted Toth @ 2022-11-23 21:49 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Because none of the declarative partition types do what I want.

On Wed, Nov 23, 2022 at 1:24 PM Ron <ronljohnsonjr@gmail.com> wrote:

> Out of curiosity, why INHERITS in v13 instead of PARTITION BY?
>
> On 11/23/22 09:31, Ted Toth wrote:
> > I've created a table with a number of indexes and then created a
> partition
> > table that inherits from it using "CREATE TABLE... INHERITS..." . I've
> > then queried pg_indexes on the parent for its indexes and tried creating
> > matching indexes on the child and attaching them to the parent table
> > indexes. However "ALTER TABLE ... ATTACH PARTITION ..." is returning the
> > error "<parent tables index name> is not a table partitioned index".
> I've
> > followed the inheritance partitioning example and read the "CREATE
> INDEX"
> > docs but I must be missing something, can anyone help me understand what
> > I'm doing wrong? I'm using version 13.
> >
> > Ted
> >
>
> --
> Angular momentum makes the world go 'round.
>
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: table inheritance partition and indexes
@ 2022-11-23 22:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ted Toth <txtoth@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2022-11-23 22:01 UTC (permalink / raw)
  To: Ted Toth <txtoth@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

Ted Toth <txtoth@gmail.com> writes:
> On Wed, Nov 23, 2022 at 1:24 PM Ron <ronljohnsonjr@gmail.com> wrote:
>> Out of curiosity, why INHERITS in v13 instead of PARTITION BY?

> Because none of the declarative partition types do what I want.

Then you're stuck managing it manually.  But ATTACH PARTITION is
not relevant to INHERITS-style partitioning.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: table inheritance partition and indexes
@ 2022-11-23 22:34  Ted Toth <txtoth@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Ted Toth @ 2022-11-23 22:34 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

On Wed, Nov 23, 2022 at 4:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ted Toth <txtoth@gmail.com> writes:
> > On Wed, Nov 23, 2022 at 1:24 PM Ron <ronljohnsonjr@gmail.com> wrote:
> >> Out of curiosity, why INHERITS in v13 instead of PARTITION BY?
>
> > Because none of the declarative partition types do what I want.
>
> Then you're stuck managing it manually.  But ATTACH PARTITION is
> not relevant to INHERITS-style partitioning.
>

That's the part that wasn't clear to me, thanks.


>
>                         regards, tom lane
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: table inheritance partition and indexes
@ 2022-11-24 02:13  David Rowley <dgrowleyml@gmail.com>
  parent: Ted Toth <txtoth@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: David Rowley @ 2022-11-24 02:13 UTC (permalink / raw)
  To: Ted Toth <txtoth@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

On Thu, 24 Nov 2022 at 11:34, Ted Toth <txtoth@gmail.com> wrote:
>
> On Wed, Nov 23, 2022 at 4:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Then you're stuck managing it manually.  But ATTACH PARTITION is
>> not relevant to INHERITS-style partitioning.
>
> That's the part that wasn't clear to me, thanks.

Would this have been more clear if [1] didn't mention both declarative
partitioning and inheritance partition on the same page? I've wondered
before if we should split that into two separate pages.

David

[1] https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: table inheritance partition and indexes
@ 2022-11-25 15:03  Ted Toth <txtoth@gmail.com>
  parent: David Rowley <dgrowleyml@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Ted Toth @ 2022-11-25 15:03 UTC (permalink / raw)
  To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

Docs can always be better, right ;) When I went back and looked at the page
I did realize that the ATTACH INDEX command was only mentioned in the
description of declarative partitioning and not in the inheritance section
so I should have paid closer attention to the location.

Ted

On Wed, Nov 23, 2022 at 8:13 PM David Rowley <dgrowleyml@gmail.com> wrote:

> On Thu, 24 Nov 2022 at 11:34, Ted Toth <txtoth@gmail.com> wrote:
> >
> > On Wed, Nov 23, 2022 at 4:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Then you're stuck managing it manually.  But ATTACH PARTITION is
> >> not relevant to INHERITS-style partitioning.
> >
> > That's the part that wasn't clear to me, thanks.
>
> Would this have been more clear if [1] didn't mention both declarative
> partitioning and inheritance partition on the same page? I've wondered
> before if we should split that into two separate pages.
>
> David
>
> [1]
> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2022-11-25 15:03 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-23 15:31 table inheritance partition and indexes Ted Toth <txtoth@gmail.com>
2022-11-23 19:24 ` Ron <ronljohnsonjr@gmail.com>
2022-11-23 21:49   ` Ted Toth <txtoth@gmail.com>
2022-11-23 22:01     ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-23 22:34       ` Ted Toth <txtoth@gmail.com>
2022-11-24 02:13         ` David Rowley <dgrowleyml@gmail.com>
2022-11-25 15:03           ` Ted Toth <txtoth@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox