Re: alter table to multi partitions

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