time taking deletion on large tables

2020-12-03 Thread Atul Kumar
Hi, The feed_posts table has over 50 Million rows. When I m deleting all rows of a certain type that are over 60 days old. When I try to do a delete like this: it hangs for an entire day, so I need to kill it with pg_terminate_backend(pid). DELETE FROM feed_posts WHERE feed_definition_id = 'bf

Re: time taking deletion on large tables

2020-12-03 Thread Ravikumar Reddy
Hi Atul, Please try the code below. Execute all the statements in one transaction. select * into new_table from old_table where type = 'abcz'; truncate table old_table; inesrt into old_table select * from new_table; On Thu, Dec 3, 2020 at 8:16 PM Atul Kumar wrote: > Hi, > > The feed_post

Re: time taking deletion on large tables

2020-12-03 Thread Justin Pryzby
On Thu, Dec 03, 2020 at 08:43:57PM +0530, Ravikumar Reddy wrote: > Please try the code below. Execute all the statements in one transaction. > > select * into new_table from old_table where type = 'abcz'; > truncate table old_table; > inesrt into old_table select * from new_table; This looks l

Re: time taking deletion on large tables

2020-12-03 Thread Tom Lane
Justin Pryzby writes: > On Thu, Dec 03, 2020 at 08:43:57PM +0530, Ravikumar Reddy wrote: >> When I try to do a delete like this: it hangs for an entire day, so I >> need to kill it with pg_terminate_backend(pid). >> >> DELETE FROM feed_posts >> WHERE feed_definition_id = 'bf33573d-936e-4e55-8607

Re: time taking deletion on large tables

2020-12-03 Thread Andrew Dunstan
On 12/3/20 11:16 AM, Tom Lane wrote: > Justin Pryzby writes: >> On Thu, Dec 03, 2020 at 08:43:57PM +0530, Ravikumar Reddy wrote: >>> When I try to do a delete like this: it hangs for an entire day, so I >>> need to kill it with pg_terminate_backend(pid). >>> >>> DELETE FROM feed_posts >>> WHERE

Temporarily disable not null constraints

2020-12-03 Thread Nagaraj Raj
Hi, Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints?  SETsession_replication_role = ‘replica’; alter table table_name disable trigger user;” above two options are working for unique constraints violation exception.  Thanks,Rj

Re: Temporarily disable not null constraints

2020-12-03 Thread Michael Lewis
On Thu, Dec 3, 2020 at 1:00 PM Nagaraj Raj wrote: > Hi, > > Can we disable not null constraints temporarily in the session-based > transaction, like we disable FK constraints? > > SET session_replication_role = ‘replica’; > alter table table_name disable trigger user;” > > above two options are w

Re: Temporarily disable not null constraints

2020-12-03 Thread Milos Babic
generally, you shouldn't be disabling your constraints, especially if you are having multiple parallel processes accessing your db. instead, you should create them DEFERRABLE and have them checked at the end of your transaction. regarding your question about NOT NULL: it is not possible to have it

Re: Temporarily disable not null constraints

2020-12-03 Thread Justin Pryzby
On Thu, Dec 03, 2020 at 07:58:15PM +, Nagaraj Raj wrote: > Can we disable not null constraints temporarily in the session-based > transaction, like we disable FK constraints?  If you're trying to temporarily violate the not-null constraint.. I don't know if it's a good idea.. ..but maybe thi

Re: Pg_locks and pg_stat_activity

2020-12-03 Thread Kyotaro Horiguchi
At Fri, 4 Dec 2020 12:11:59 +0530, Ravikumar Reddy wrote in > Hi, > pg_stat_activity -- Providers the active and ideal connection for our > database > Pg_locks -- Provider the queries/procedure/function details if > any object is locked at the current in our database. Yeah.. That re