Thread: Table Partitioning
Hi All,
We are using postgres 10.12 and we have one table that size is more then 20Gb. We are planning to do the range partition on that table, as we have date filed that can be used. We are able to create partition and it work fine, however one scenario where we are getting error.
We have created the yearly range for the partition, however we have some scenario where we have to update the date column and respective record need to move from other child tables.
For ex:
Table X has 3 partition X2019, X2020, X2021. Column is action_date, that we are using for partitioning.
insert one record in X2019 and action date = 2019-11-16
however now i have to update the date form '2019-11-16' to '2020-02-18', when we try to runt he Update statement, it give me an error called "SQL-23514 - new row for relation X2019 violates partition constraint".
Can you please suggest, how can we handle that scenario? Do I have to write the trigger in Update statement and move the record manually or perform the insert and delete operation.
Regards,
Anjul TYAGI
ü Go Green
On Tue, Jan 19, 2021 at 10:21:27AM +0000, Anjul Tyagi wrote: > Hi All, > We are using postgres 10.12 and we have one table that size is more then 20Gb. We are planning to do the range partitionon that > table, as we have date filed that can be used. We are able to create partition and it work fine, however one scenario wherewe are > getting error. > We have created the yearly range for the partition, however we have some scenario where we have to update the date columnand > respective record need to move from other child tables. > For ex: > Table X has 3 partition X2019, X2020, X2021. Column is action_date, that we are using for partitioning. > insert one record in X2019 and action date = 2019-11-16 > however now i have to update the date form '2019-11-16' to '2020-02-18', when we try to runt he Update statement, it giveme an > error called "SQL-23514 - new row for relation X2019 violates partition constraint". > Can you please suggest, how can we handle that scenario? Do I have to write the trigger in Update statement and move therecord > manually or perform the insert and delete operation. The simplest solution would be to delete the rows, and immediately insert into appropriate partition. for example: with q as (delete * from x where action = '2019-11-16 returning *) insert into x select other, columns, '2020-02-18' from q; depesz
Can we handle that in DB side with trigger?
Regards,
Anjul TYAGI
ü Go Green
------ Original Message ------
From: "hubert depesz lubaczewski" <depesz@depesz.com>
To: "Anjul Tyagi" <anjul@ibosstech-us.com>
Cc: "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: 1/19/2021 5:12:46 PM
Subject: Re: Table Partitioning
On Tue, Jan 19, 2021 at 10:21:27AM +0000, Anjul Tyagi wrote:Hi All,We are using postgres 10.12 and we have one table that size is more then 20Gb. We are planning to do the range partition on thattable, as we have date filed that can be used. We are able to create partition and it work fine, however one scenario where we aregetting error.We have created the yearly range for the partition, however we have some scenario where we have to update the date column andrespective record need to move from other child tables.For ex:Table X has 3 partition X2019, X2020, X2021. Column is action_date, that we are using for partitioning.insert one record in X2019 and action date = 2019-11-16however now i have to update the date form '2019-11-16' to '2020-02-18', when we try to runt he Update statement, it give me anerror called "SQL-23514 - new row for relation X2019 violates partition constraint".Can you please suggest, how can we handle that scenario? Do I have to write the trigger in Update statement and move the recordmanually or perform the insert and delete operation.The simplest solution would be to delete the rows, and immediatelyinsert into appropriate partition.for example:with q as (delete * from x where action = '2019-11-16 returning *)insert into x select other, columns, '2020-02-18' from q;depesz
On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote: > Can we handle that in DB side with trigger? I don't think so. At least not easily. You could make a function to do so, though. Best regards, depesz
On Tue, Jan 19, 2021 at 7:12 AM hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote:
> Can we handle that in DB side with trigger?
I don't think so. At least not easily.
You could make a function to do so, though.
Best regards,
depesz
As depesz said, a trigger is possible, but very tricky. I was never able to find a good trigger-based, solution to these kinds of updates for pg_partman myself. Thankfully support for updates that move data between child tables was added for native partitioning in PG11. I would highly suggest planning an upgrade to your major version of PG. Skip 11 and go right to 12 or 13. An upgrade would be arguably easier than trying to code this yourself. And the added features to native partitioning in 11 and above will make managing them far better in the long run.
Op 0
From: Keith Fiske <keith.fiske@crunchydata.com>
Sent: Tuesday, January 19, 2021 1:32:34 PM
To: depesz@depesz.com <depesz@depesz.com>
Cc: Anjul Tyagi <anjul@ibosstech-us.com>; pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Table Partitioning
Sent: Tuesday, January 19, 2021 1:32:34 PM
To: depesz@depesz.com <depesz@depesz.com>
Cc: Anjul Tyagi <anjul@ibosstech-us.com>; pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Table Partitioning
On Tue, Jan 19, 2021 at 7:12 AM hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote:
> Can we handle that in DB side with trigger?
I don't think so. At least not easily.
You could make a function to do so, though.
Best regards,
depesz
As depesz said, a trigger is possible, but very tricky. I was never able to find a good trigger-based, solution to these kinds of updates for pg_partman myself. Thankfully support for updates that move data between child tables was added for native partitioning in PG11. I would highly suggest planning an upgrade to your major version of PG. Skip 11 and go right to 12 or 13. An upgrade would be arguably easier than trying to code this yourself. And the added features to native partitioning in 11 and above will make managing them far better in the long run.
I am totally agree with Keith opinion on better to go Upgrade rather than managing partitioning code.
Even PG version 11 has lot of partitioning features over older one, was written a blog on same, can have a glance.
On Tue, Jan 19, 2021 at 7:03 PM Keith Fiske <keith.fiske@crunchydata.com> wrote:
On Tue, Jan 19, 2021 at 7:12 AM hubert depesz lubaczewski <depesz@depesz.com> wrote:On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote:
> Can we handle that in DB side with trigger?
I don't think so. At least not easily.
You could make a function to do so, though.
Best regards,
depeszAs depesz said, a trigger is possible, but very tricky. I was never able to find a good trigger-based, solution to these kinds of updates for pg_partman myself. Thankfully support for updates that move data between child tables was added for native partitioning in PG11. I would highly suggest planning an upgrade to your major version of PG. Skip 11 and go right to 12 or 13. An upgrade would be arguably easier than trying to code this yourself. And the added features to native partitioning in 11 and above will make managing them far better in the long run.--
After postgresql 11, it is possible to update the partitioning key and the affected rows are moved to a valid partition under the hood. So I think the best approach is to upgrade your PG version.
Regards
El mar, 19 ene 2021 a las 10:15, Amit jain (<amit7.jain@gmail.com>) escribió:
I am totally agree with Keith opinion on better to go Upgrade rather than managing partitioning code.Even PG version 11 has lot of partitioning features over older one, was written a blog on same, can have a glance.On Tue, Jan 19, 2021 at 7:03 PM Keith Fiske <keith.fiske@crunchydata.com> wrote:On Tue, Jan 19, 2021 at 7:12 AM hubert depesz lubaczewski <depesz@depesz.com> wrote:On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote:
> Can we handle that in DB side with trigger?
I don't think so. At least not easily.
You could make a function to do so, though.
Best regards,
depeszAs depesz said, a trigger is possible, but very tricky. I was never able to find a good trigger-based, solution to these kinds of updates for pg_partman myself. Thankfully support for updates that move data between child tables was added for native partitioning in PG11. I would highly suggest planning an upgrade to your major version of PG. Skip 11 and go right to 12 or 13. An upgrade would be arguably easier than trying to code this yourself. And the added features to native partitioning in 11 and above will make managing them far better in the long run.--
Thanks Amit and Keith for your advise.
Regards,
Anjul TYAGI
ü Go Green
------ Original Message ------
From: "Amit jain" <amit7.jain@gmail.com>
To: "Keith Fiske" <keith.fiske@crunchydata.com>
Cc: depesz@depesz.com; "Anjul Tyagi" <anjul@ibosstech-us.com>; "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: 1/19/2021 8:45:31 PM
Subject: Re: Table Partitioning
I am totally agree with Keith opinion on better to go Upgrade rather than managing partitioning code.Even PG version 11 has lot of partitioning features over older one, was written a blog on same, can have a glance.On Tue, Jan 19, 2021 at 7:03 PM Keith Fiske <keith.fiske@crunchydata.com> wrote:On Tue, Jan 19, 2021 at 7:12 AM hubert depesz lubaczewski <depesz@depesz.com> wrote:On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote:
> Can we handle that in DB side with trigger?
I don't think so. At least not easily.
You could make a function to do so, though.
Best regards,
depeszAs depesz said, a trigger is possible, but very tricky. I was never able to find a good trigger-based, solution to these kinds of updates for pg_partman myself. Thankfully support for updates that move data between child tables was added for native partitioning in PG11. I would highly suggest planning an upgrade to your major version of PG. Skip 11 and go right to 12 or 13. An upgrade would be arguably easier than trying to code this yourself. And the added features to native partitioning in 11 and above will make managing them far better in the long run.--