Subject: Re: Partitioning Tables
To: pgsql-admin@lists.postgresql.org
References: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
 <B77077E0-3B97-4408-9BFA-D4F5137C2689@elevated-dev.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <48402931-ad7a-7cf8-b30a-4d1a86408927@gmail.com>
Date: Fri, 5 Feb 2021 12:59:24 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <B77077E0-3B97-4408-9BFA-D4F5137C2689@elevated-dev.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 2/5/21 12:42 PM, Scott Ribe wrote:
>> On Feb 5, 2021, at 11:27 AM, Campbell, Lance <lance@illinois.edu> wrote:
>>
>> So if I do a query like the below it would ideally pull from partition group_member_a rather than group_member_other.  However, how would PostgreSQL know on insert a group_member into the proper partition?
>>   
>> Select group_member.* from group, group_member WHERE group.type=’A’ and group.id=group_member.user=’bob smith’;
> When you define the partition, you declare what values go into it. With PG 12, you can insert into the parent and the row will get redirected into the right place. Some older versions (I don't remember how old) required you to insert into the right table, or some voodoo with rewrite rules or triggers.
>
> In other words, smarts about which partitions to scan in your example pre-dates smarts about inserting.
>
> I don't remember for sure, but I think maybe PG 13 adds the feature where if you update the group type, the row gets moved. Prior, you'd have to delete and re-insert. Someone correct me?

I don't know about v13, but v12 (and below) error on updating the partition 
key.  (That's a common limitation which helps the optimizer /know/ what's in 
a partition.)

-- 
Angular momentum makes the world go 'round.