Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value? - Mailing list pgsql-general

From James(王旭)
Subject Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
Date
Msg-id tencent_10CD0FFC540E29992BA299BA@qq.com
Whole thread Raw
In response to Re: Issue related with patitioned table:How can I quickly determinewhich child table my record is in,given a specific primary key value?  (Luca Ferrari <fluca1978@gmail.com>)
List pgsql-general
Hi Luca,

Yes, that's the answer,It really works!
Thanks again Luca, you actually saved my day!

James.
------------------ Original ------------------
Date:  Wed, Jul 17, 2019 06:49 PM
To:  "James(王旭)"<wangxu@gu360.com>;
Cc:  "pgsql-general"<pgsql-general@lists.postgresql.org>;
Subject:  Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
 
On Wed, Jul 17, 2019 at 11:41 AM James(王旭) <wangxu@gu360.com> wrote:
> From these results I can tell the route to a table is not even related with the mod function, right?
> So It's hard for me to do any kind of guesses...

Because it is the wrong function.
According to \d+ on a child table and partbounds.c the function called
is satisfied_hash_partition:

testdb=# select satisfies_hash_partition('153221'::oid, 3, 0, 6521);
 satisfies_hash_partition
--------------------------
 t
(1 row)

testdb=# select satisfies_hash_partition('153221'::oid, 3, 1, 6521);
 satisfies_hash_partition
--------------------------
 f
(1 row)

The first argument is the table id (partitioned one, the root), the
second is the reminder, third is the partition table, last is your
value.
Therefore I suspect you have to iterate on your partition numbers from
0 to x to see if a value fits in that partition, and then extract the
table name from that.

Hope its clear.

Luca

pgsql-general by date:

Previous
From: Luca Ferrari
Date:
Subject: Re: Issue related with patitioned table:How can I quickly determinewhich child table my record is in,given a specific primary key value?
Next
From: Rory Campbell-Lange
Date:
Subject: Re: How to run a task continuously in the background