Обсуждение: Table Partitioning

Поиск
Список
Период
Сортировка

Table Partitioning

От
"Anjul Tyagi"
Дата:
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


Re: Table Partitioning

От
hubert depesz lubaczewski
Дата:
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



Re: Table Partitioning

От
"Anjul Tyagi"
Дата:
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 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. 
 
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
 
 

Re: Table Partitioning

От
hubert depesz lubaczewski
Дата:
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




Re: Table Partitioning

От
Keith Fiske
Дата:


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.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: Table Partitioning

От
Graham Everton
Дата:
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
 


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.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: Table Partitioning

От
Amit jain
Дата:
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,

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.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: Table Partitioning

От
lazaro garcia
Дата:
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,

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.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Re: Table Partitioning

От
"Anjul Tyagi"
Дата:
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>
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,

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.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com