Обсуждение: Automate to rename table

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

Automate to rename table

От
Rajesh Kumar
Дата:
Hi friends,

What is the best way to rename the table name automatically every 30 minutes?

Automate to rename table

От
"Wetmore, Matthew (CTR)"
Дата:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 

Re: Automate to rename table

От
Wells Oliver
Дата:
Wild. What's the use-case here?

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 



--

Re: Automate to rename table

От
Rajesh Kumar
Дата:
I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.


So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

How do we automate it?

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

What's the best way?

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:
Wild. What's the use-case here?

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 



--

Re: Automate to rename table

От
Holger Jakobs
Дата:
Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.


Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@gmail.com>:
I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.


So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

How do we automate it?

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

What's the best way?

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:
Wild. What's the use-case here?

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 



--

Re: Automate to rename table

От
Ron
Дата:
That requires adding the date field to the PK.

On 8/10/23 05:11, Holger Jakobs wrote:
Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.


Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@gmail.com>:
I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.


So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

How do we automate it?

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

What's the best way?

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:
Wild. What's the use-case here?

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 



--

--
Born in Arizona, moved to Babylonia.

Re: Automate to rename table

От
Ron
Дата:
Old school trigger+function based partitioning is exactly what you want.

On 8/10/23 04:52, Rajesh Kumar wrote:
I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.


So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

How do we automate it?

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

What's the best way?

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:
Wild. What's the use-case here?

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 



--

--
Born in Arizona, moved to Babylonia.

Automate to rename table

От
"Wetmore, Matthew (CTR)"
Дата:

A partitioned table with less than 500M rows will suffer from performance issues.  Just my $0.02

 

From: Holger Jakobs <holger@jakobs.com>
Sent: Thursday, August 10, 2023 3:12 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Automate to rename table

 

Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.

 

Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@gmail.com>:

I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.

 

 

So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

 

How do we automate it?

 

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

 

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

 

What's the best way?

 

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:

Wild. What's the use-case here?

 

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 


 

--

Re: Automate to rename table

От
Rajesh Kumar
Дата:
@Wetmore, Matthew (CTR) How exactly?, In this case, we are not gonna need the old table. That is just kind of backup only

On Thu, 10 Aug 2023 at 08:27, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

A partitioned table with less than 500M rows will suffer from performance issues.  Just my $0.02

 

From: Holger Jakobs <holger@jakobs.com>
Sent: Thursday, August 10, 2023 3:12 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Automate to rename table

 

Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.

 

Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@gmail.com>:

I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.

 

 

So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

 

How do we automate it?

 

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

 

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

 

What's the best way?

 

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:

Wild. What's the use-case here?

 

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 


 

--

Re: Automate to rename table

От
Keith Fiske
Дата:


On Thu, Aug 10, 2023 at 11:49 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
@Wetmore, Matthew (CTR) How exactly?, In this case, we are not gonna need the old table. That is just kind of backup only

On Thu, 10 Aug 2023 at 08:27, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

A partitioned table with less than 500M rows will suffer from performance issues.  Just my $0.02

 

From: Holger Jakobs <holger@jakobs.com>
Sent: Thursday, August 10, 2023 3:12 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Automate to rename table

 

Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.

 

Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@gmail.com>:

I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.

 

 

So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

 

How do we automate it?

 

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

 

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

 

What's the best way?

 

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:

Wild. What's the use-case here?

 

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 


 

--


Partitioned tables have overhead for routing the rows to the proper underlying table. As a table grows in size, that ovehead becomes less and less relevant vs the performance of dealing with a larger and larger table. 

Partitioning would seem to be an ideal solution here if that overhead is not relevant for your situation. I would recommend at least trying it out. Trigger-based partitioning would be significantly more overhead than built-in declarative partitioning, so I wouldn't recommend going down the trigger route unless you discover it's necessary.

pg_partman can help with automating both the new child table creation and retention of the old table either being detached or dropped from the partition set. It also has some work-arounds for the primary key issue that may or may not work for you


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

Re: Automate to rename table

От
Keith Fiske
Дата:
Just noticed you said you don't have a date column in this table. That would be needed with pg_partman. I would recommend adding some sort of date column into this table either way just so it's easier to keep track of the age of the data itself you're dealing with. Could just be a simple "created_at" column that is just set as a default of CURRENT_TIMESTAMP and no user has to worry about setting it.

On Thu, Aug 10, 2023 at 12:21 PM Keith Fiske <keith.fiske@crunchydata.com> wrote:


On Thu, Aug 10, 2023 at 11:49 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
@Wetmore, Matthew (CTR) How exactly?, In this case, we are not gonna need the old table. That is just kind of backup only

On Thu, 10 Aug 2023 at 08:27, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

A partitioned table with less than 500M rows will suffer from performance issues.  Just my $0.02

 

From: Holger Jakobs <holger@jakobs.com>
Sent: Thursday, August 10, 2023 3:12 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Automate to rename table

 

Creating a partitioned table with a partition by month would be the way to go. Then drop the tables you no longer need. This can be done in a cron job or by using pg_agent, pg_cron or pg_timetable.

 

Am 10. August 2023 11:52:37 MESZ schrieb Rajesh Kumar <rajeshkumar.dba09@gmail.com>:

I have a table A where datas are coming in...and as it gets old (say 30 days), we would not want this on same table and wants it in another table 30days old (A_old) and any new request will be in A table.

 

 

So, what we are already doing is, created a function to create a table A if not exists , then we are renaming the table A to A_old manually. And when we execute that function it create a new table A so that new data gets inserted. Then after 60 days we drop drop A_old table. Which means we need 30 days data in new table, 30 days data in old table and remaining we don't need it.

 

How do we automate it?

 

I am thinking of partioning, but unsure whether it's a complex process ..also don't have date column.

 

Can we simply create a function to alter table A rename to A_old , and schedule to execute this function every 30 days? 

 

What's the best way?

 

On Thu, 10 Aug, 2023, 2:34 PM Wells Oliver, <wells.oliver@gmail.com> wrote:

Wild. What's the use-case here?

 

On Wed, Aug 9, 2023 at 9:50 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

Simple CRON entry with a script that runs a psql command

 

You can:

 

alter table rename

Or

create table x as select * from original table.

 

Depending on you DDL needs or permissions/setup

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Wednesday, August 9, 2023 1:39 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Automate to rename table

 

Hi friends,

 

What is the best way to rename the table name automatically every 30 minutes?

 


 

--


Partitioned tables have overhead for routing the rows to the proper underlying table. As a table grows in size, that ovehead becomes less and less relevant vs the performance of dealing with a larger and larger table. 

Partitioning would seem to be an ideal solution here if that overhead is not relevant for your situation. I would recommend at least trying it out. Trigger-based partitioning would be significantly more overhead than built-in declarative partitioning, so I wouldn't recommend going down the trigger route unless you discover it's necessary.

pg_partman can help with automating both the new child table creation and retention of the old table either being detached or dropped from the partition set. It also has some work-arounds for the primary key issue that may or may not work for you


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


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