Re: [HACKERS] Proposal: Local indexes for partitioned table

Alvaro Herrera writes: > Tom Lane wrote: >> It might work to build the new key in a context that's initially a >> child of CurrentMemoryContext, then reparent it to be a child of >> CacheMemoryContext when done. > That's another way (than the PG_TRY block), but I think

Re: [HACKERS] Proposal: Local indexes for partitioned table

> Robert Haas writes: > > We could do that, but the motivation for the current system was to > > avoid leaking memory in a long-lived context. Yeah, my approach here is to use a CATCH block that deletes the memory context just created, thus avoiding a long-lived leak.

Re: [HACKERS] Proposal: Local indexes for partitioned table

Robert Haas writes: > On Fri, Oct 27, 2017 at 7:27 PM, Alvaro Herrera > wrote: >> I noticed that RelationBuildPartitionKey is generating a partition key >> in a temp context, then creating a private context and copying the key >> into that. That

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Fri, Oct 27, 2017 at 7:27 PM, Alvaro Herrera wrote: > I noticed that RelationBuildPartitionKey is generating a partition key > in a temp context, then creating a private context and copying the key > into that. That seems leftover from some previous iteration of some

Re: [HACKERS] Proposal: Local indexes for partitioned table

Alvaro Herrera wrote: > I'm now working on the ability to build unique indexes (and unique > constraints) on top of this. So I think there's not a lot of additional code required to support unique indexes with the restrictions mentioned; proof-of-concept (with several holes still) attached. As

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 2017/10/24 1:15, Alvaro Herrera wrote: > Robert Haas wrote: >> On Mon, Oct 23, 2017 at 11:12 AM, Alvaro Herrera >> wrote: >>> I started with Maksim's submitted code, and developed according to the >>> ideas discussed in this thread. Attached is a very WIP patch series

Re: [HACKERS] Proposal: Local indexes for partitioned table

Robert Haas wrote: > On Mon, Oct 23, 2017 at 11:12 AM, Alvaro Herrera > wrote: > > I started with Maksim's submitted code, and developed according to the > > ideas discussed in this thread. Attached is a very WIP patch series for > > this feature. > > > > Many things

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Mon, Oct 23, 2017 at 11:12 AM, Alvaro Herrera wrote: > I started with Maksim's submitted code, and developed according to the > ideas discussed in this thread. Attached is a very WIP patch series for > this feature. > > Many things remain to be done before this is

Re: [HACKERS] Proposal: Local indexes for partitioned table

Hello I started with Maksim's submitted code, and developed according to the ideas discussed in this thread. Attached is a very WIP patch series for this feature. Many things remain to be done before this is committable: pg_dump support needs to be written. ALTER INDEX ATTACH/DETACH not yet

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Tue, Oct 10, 2017 at 11:22 AM, Alvaro Herrera wrote: > Hmm ... yeah, ATTACH and DETACH sound acceptable to me. On DETACH, the > abstract index should be marked indisvalid=false unless a substitute > index already exists; and on ATTACH when indisvalid=false we verify

Re: [HACKERS] Proposal: Local indexes for partitioned table

Robert Haas wrote: > On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera > wrote: > > 2. create one index for each existing partition. These would be > >identical to what would happen if you created the index directly on > >each partition, except that there is an

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Sat, Oct 7, 2017 at 7:04 PM, Robert Haas wrote: > On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera > wrote: >> 2. create one index for each existing partition. These would be >>identical to what would happen if you created the index

Re: [HACKERS] Proposal: Local indexes for partitioned table

07.10.17 16:34, Robert Haas wrote: On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera wrote: One thing I'm a bit worried about is how to name these subordinate indexes. They have to have names because that's how pg_class works, and those names can't all be the same,

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera wrote: > 2. create one index for each existing partition. These would be >identical to what would happen if you created the index directly on >each partition, except that there is an additional dependency to the >

Re: [HACKERS] Proposal: Local indexes for partitioned table

Hi! On 06.10.2017 19:37, Alvaro Herrera wrote: As you propose, IMO this new feature would use the standard index creation syntax: CREATE [UNIQUE] INDEX someindex ON parted_table (a, b); This command currently throws an error. We'd have it do two things: 1. create catalog rows in

Re: [HACKERS] Proposal: Local indexes for partitioned table

Hello I've been thinking about this issue too. I think your patch is not ambitious enough. Here's my brain dump on the issue, to revive discussion. As you propose, IMO this new feature would use the standard index creation syntax: CREATE [UNIQUE] INDEX someindex ON parted_table (a, b);

Re: [HACKERS] Proposal: Local indexes for partitioned table

10.08.17 23:01, Robert Haas wrote: On Wed, Apr 19, 2017 at 5:25 AM, Maksim Milyutin wrote: Ok, thanks for the feedback. Then I'll use a new relkind for local partitioned index in further development. Any update on this? I'll continue to work soon. Sorry for so

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Wed, Apr 19, 2017 at 5:25 AM, Maksim Milyutin wrote: > Ok, thanks for the feedback. Then I'll use a new relkind for local > partitioned index in further development. Any update on this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 19.04.2017 11:42, Ashutosh Bapat wrote: On Tue, Apr 18, 2017 at 4:43 PM, Maksim Milyutin wrote: Local partitioned indexes can be recognized through the check on the relkind of table to which the index refers. Something like this: heap =

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Tue, Apr 18, 2017 at 4:43 PM, Maksim Milyutin wrote: > On 18.04.2017 13:08, Amit Langote wrote: >> >> Hi, >> > > Hi, Amit! > >> On 2017/04/17 23:00, Maksim Milyutin wrote: >>> >>> >>> Ok, thanks for the note. >>> >>> But I want to discuss the relevancy of

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 18.04.2017 13:08, Amit Langote wrote: Hi, Hi, Amit! On 2017/04/17 23:00, Maksim Milyutin wrote: Ok, thanks for the note. But I want to discuss the relevancy of introduction of a new relkind for partitioned index. I could to change the control flow in partitioned index creation

Re: [HACKERS] Proposal: Local indexes for partitioned table

Hi, On 2017/04/17 23:00, Maksim Milyutin wrote: > On 10.04.2017 14:20, Robert Haas wrote: >> On Tue, Apr 4, 2017 at 12:10 PM, Maksim Milyutin >> wrote: >>> 1. I have added a new relkind for local indexes named RELKIND_LOCAL_INDEX >>> (literal 'l'). >> >> Seems like it

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 10.04.2017 14:20, Robert Haas wrote: On Tue, Apr 4, 2017 at 12:10 PM, Maksim Milyutin wrote: 1. I have added a new relkind for local indexes named RELKIND_LOCAL_INDEX (literal 'l'). Seems like it should maybe be RELKIND_PARTITIONED_INDEX. There's nothing

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 10.04.2017 13:46, Greg Stark wrote: On 4 April 2017 at 17:10, Maksim Milyutin wrote: 3. As I noticed early pg_depend table is used for cascade deleting indexes on partitioned table and its children. I also use pg_depend to determine relationship between parent

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Tue, Apr 4, 2017 at 12:10 PM, Maksim Milyutin wrote: > 1. I have added a new relkind for local indexes named RELKIND_LOCAL_INDEX > (literal 'l'). Seems like it should maybe be RELKIND_PARTITIONED_INDEX. There's nothing particularly "local" about it. I suppose what

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 4 April 2017 at 17:10, Maksim Milyutin wrote: > > 3. As I noticed early pg_depend table is used for cascade deleting indexes > on partitioned table and its children. I also use pg_depend to determine > relationship between parent and child indexes when reindex

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 01.03.2017 13:53, Maksim Milyutin wrote: Hi hackers! As I've understood from thread [1] the main issue of creating local indexes for partitions is supporting REINDEX and DROP INDEX operations on parent partitioned tables. Furthermore Robert Haas mentioned the problem of creating index on key

Re: [HACKERS] Proposal: Local indexes for partitioned table

On 02.03.2017 11:41, Robert Haas wrote: Sounds generally good. One thing to keep in mind is that - in this system - a UNIQUE index on the parent doesn't actually guarantee uniqueness across the whole partitioning hierarchy unless it so happens that the index columns or expressions are the same

Re: [HACKERS] Proposal: Local indexes for partitioned table

On Wed, Mar 1, 2017 at 4:23 PM, Maksim Milyutin wrote: > As I've understood from thread [1] the main issue of creating local indexes > for partitions is supporting REINDEX and DROP INDEX operations on parent > partitioned tables. Furthermore Robert Haas mentioned the

[HACKERS] Proposal: Local indexes for partitioned table

Hi hackers! As I've understood from thread [1] the main issue of creating local indexes for partitions is supporting REINDEX and DROP INDEX operations on parent partitioned tables. Furthermore Robert Haas mentioned the problem of creating index on key that is represented in partitions with