Re: alter table to multi partitions

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Catalin Maftei <catalinmaftei(at)yahoo(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: alter table to multi partitions
Date: 2022-02-25 09:36:01
Message-ID: d5b884723c32e4a59809a33c6c59c1267a7d9bfe.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, 2022-02-24 at 12:48 +0000, Catalin Maftei wrote:
> on a project I have large tables, with more than 2-5.000.000 rows, and the system is starting to be slow,
> This tables have many dependencies functions, procedures and views.
>
> I want to partition this tables. 
>
> I think for this scenario for table Orders:
> step1:     create new table with same structure ->  Orders_Part
> step 2:    create all the partitions&triggers on new table Orders_Part
> step 3:    copy/moved data from Orders to Orders_Part
> step 4:    rename Orders to Orders_Old
> If i want to drop Orders, it's necessary to drop all dependencies (and that's not what I want)
> step 5:    rename Orders_Part to Orders
> here is the problem - when rename Orders to Orders_old, postgres automatically change Orders to Orders_old in all dependencies (views, functions, sp) 
>
> is there any postgres setting I can use to not  automatically change the in all the depandencies?

No, that is not possible.

Note that the dependencies don't change at all.
Rather, the dependencies are on the object itself, not the name of the object.
So if you change the name, the dependencies don't change.

You'll have to drop and re-create foreign keys, views and the like.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Kumar, Mukesh 2022-02-25 13:06:23 Transactions starting with Cursor with Hold are not closing in database.
Previous Message Laurenz Albe 2022-02-25 09:33:12 Re: \set \e and newline