Postgres Pro
Downloads
Home   >   mailing lists

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_3DC4B18C6E40FB7B57E655F2@qq.com
Whole thread Raw
In response to Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?  ("James(王旭)" <wangxu@gu360.com>)
Responses 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
Tree view
Here's my PG version:

PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

Hope this helps. 
------------------ Original ------------------
Date:  Wed, Jul 17, 2019 05:36 PM
To:  "Luca Ferrari"<fluca1978@gmail.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?
 
Thanks you Luca.

Yes my intention was " SELECT (hashint2(6365::SMALLINT)% 3) ", that's my mistake, thank you for pointing out.
 
Actually I just did a simple query in my single table:

SELECT distinct(symbol_id) FROM xxxx_0

and I got these results:

"symbol_id"
6521
1478
1964
5642
7470
1158
2429
9882
4542
5196
9178
8303
1091
9435
8133
1437
9072

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...
 
------------------ Original ------------------
Date:  Wed, Jul 17, 2019 05:13 PM
To:  "王旭"<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 9:19 AM 王旭 <wangxu@gu360.com> wrote:
> I tried something like this:  e.g., for symbol_id 6365,
> SELECT (hashint2(6365::SMALLINT)% 10)

shouldn't this be modulus 3 instead of 10?
The problem is that record 6365 is not where you expected to be?

As far as I know, there is no easy user-level way to get the route to
a table, but you can juggle with the expression that defined each
table and make a good guess.
However, your query should give a good idea:

# SELECT 'my_table_' || (hashint2(6365::smallint)% 3);
  ?column?
------------
 my_table_2

pgsql-general by date:

Previous
From: "James(王旭)"
Date:
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?
Next
From: Dirk Mika
Date:
Subject: Re: How to run a task continuously in the background