Thread: Related To Hash Partition
Hi Team,
While using hash partition i am facing the problem that the data is not going into the proper partition table .
Due to this, records are not visible in the main table when I am searching for a particular record, as it searches only that partition.
Any suggestions on how to overcome this problem and why this happened.
For example, in my case, explain is searching for the record in the 10th child table, but the record is actually placed in the 5th child table.
When I delete this record and insert it again, it goes to the 10th child table.
I want to know how the records are shuffled.
And in the future, if i truncate the records from the main table and insert them back again, will the shuffling of records happen or not.
Any ideas regarding why this happened?
Moreover, i have not changed anything from my side. Just add up the indexing.
Regards
Rohit Sachdeva
ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> writes:
> While using hash partition i am facing the problem that the data is not
> going into the proper partition table .
Define "proper". It's generally best to assume that the hash function
is a black box --- if you assume you know which partition a given key
value will be mapped to, you are doing something very fragile, and people
will have no sympathy for you when it breaks.
If you want a predictable mapping, use list or range partitioning,
not hash.
regards, tom lane
Hi Tom,
ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> writes:
> While using hash partition i am facing the problem that the data is not
> going into the proper partition table .
Define "proper". It's generally best to assume that the hash function
is a black box --- if you assume you know which partition a given key
value will be mapped to, you are doing something very fragile, and people
will have no sympathy for you when it breaks.
If you want a predictable mapping, use list or range partitioning,
not hash.
regards, tom lane
Hi Team,
While using hash partition i am facing the problem that the data is not going into the proper partition table .
Due to this, records are not visible in the main table when I am searching for a particular record, as it searches only that partition.
Any suggestions on how to overcome this problem and why this happened.
And in the future, if i truncate the records from the main table and insert them back again, will the shuffling of records happen or not.
Any ideas regarding why this happened?
Just add up the indexing.
Hi Jeff,
Thanks for the reply.
Below are the replies to your queries regarding my issue.
Are you using a custom hashing function or a built-in one?
Answer: I am not using a custom hash function
What is the datatype of the partition key?
Answer: The datatype is bigint.
Has this system been through any pg_upgrade cycles?
Answer: No
Have you tried to reproduce it on your own system?
Answer: Yes, in the system, and all things are fine.
What is your version, hardware, and OS?
Answer: It's the 14 version, and it's an RDS service of AWS, which has a configuration of db.r6g.16xlarge.
Do you have error correcting code RAM?
Answer: It is hosted on the AWS cloud.
Just add up the indexing.
Answer : I was meant to say that the only change i did in table strutute was creation of index.
On Sun, Apr 23, 2023 at 3:04 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,
While using hash partition i am facing the problem that the data is not going into the proper partition table .
Due to this, records are not visible in the main table when I am searching for a particular record, as it searches only that partition.
Any suggestions on how to overcome this problem and why this happened.This seems like a bug, or some kind of corruption. But there isn't enough info here to identify what the bug might be. Are you using a custom hashing function, or a built-in one? What is the datatype of the partition key? Has this system been through any pg_upgrade cycles? Have you tried to reproduce it on your own system? What is your version and hardware and OS? Do you have error correcting code RAM?And in the future, if i truncate the records from the main table and insert them back again, will the shuffling of records happen or not.
Any ideas regarding why this happened?I don't think there is a way to answer that without first identifying what went wrong in the first place.Just add up the indexing.
I don't know what that means.Cheers,Jeff
Regards
Rohit Sachdeva
Hi Jeff,
Thanks for the reply.
Below are the replies to your queries regarding my issue.
Are you using a custom hashing function or a built-in one?
Answer: I am not using a custom hash function
What is the datatype of the partition key?
Answer: The datatype is bigint.
Has this system been through any pg_upgrade cycles?
Answer: No
Have you tried to reproduce it on your own system?
Answer: Yes, in the system, and all things are fine.
What is your version, hardware, and OS?
Answer: It's the 14 version, and it's an RDS service of AWS, which has a configuration of db.r6g.16xlarge.
Do you have error correcting code RAM?
Answer: It is hosted on the AWS cloud.Just add up the indexing.
Answer : I was meant to say that the only change i did in table strutute was creation of index.On Fri, Apr 28, 2023 at 4:26 AM Jeff Janes <jeff.janes@gmail.com> wrote:On Sun, Apr 23, 2023 at 3:04 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,
While using hash partition i am facing the problem that the data is not going into the proper partition table .
Due to this, records are not visible in the main table when I am searching for a particular record, as it searches only that partition.
Any suggestions on how to overcome this problem and why this happened.This seems like a bug, or some kind of corruption. But there isn't enough info here to identify what the bug might be. Are you using a custom hashing function, or a built-in one? What is the datatype of the partition key? Has this system been through any pg_upgrade cycles? Have you tried to reproduce it on your own system? What is your version and hardware and OS? Do you have error correcting code RAM?And in the future, if i truncate the records from the main table and insert them back again, will the shuffling of records happen or not.
Any ideas regarding why this happened?I don't think there is a way to answer that without first identifying what went wrong in the first place.Just add up the indexing.
I don't know what that means.Cheers,Jeff--Have a Good day !!!
Regards
Rohit Sachdeva
Regards
Rohit Sachdeva
Hi Jeff,Today when i am running the updates on a sharding table i.e updating the sharding column with other values.Data has been unsync i.e data should be in the fourth partition according to the explain method but the data is in the first partition.How can I overcome this problem?plz help me with this issue as this is a production issue.RegardsRohit Sachdeva
I am using postgres 14.8 and yes it's rds system.
On Thu, Aug 24, 2023 at 11:53 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Jeff,Today when i am running the updates on a sharding table i.e updating the sharding column with other values.Data has been unsync i.e data should be in the fourth partition according to the explain method but the data is in the first partition.How can I overcome this problem?plz help me with this issue as this is a production issue.RegardsRohit SachdevaI imagine you're going to have to give a concrete, reproducible example before anyone is going to be able to assist any further. Also which specific, patched version of PG14 to see if there might be a bug that was fixed?Even if that example only works in RDS at least that would narrow this down to being an issue specifically there. And if this turns out to only be an RDS issue, it at least points out to you that you will likely have to contact their support since RDS is actually a proprietary fork of PostgreSQL.Keith