Skip site navigation (1) Skip section navigation (2)

Fwd: Advice on moving rows to history

From: Matthew Terenzio <webmaster(at)localnotion(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Fwd: Advice on moving rows to history
Date: 2004-12-29 22:49:37
Message-ID: EAF2A278-59EB-11D9-B3B3-0003938BDF32@localnotion.com (view raw or flat)
Thread:
Lists: pgsql-general
Meant this to go to whole list , sorry for duplicate

Begin forwarded message:

>
> On Dec 29, 2004, at 4:58 PM, Dave Smith wrote:
>
>> I am trying to figure out the fastest way to move rows from a current
>> table to a history table. There are currently 150,000 rows of which
>> about 60,000 get moved (monthly). There are multiple queries involved 
>> to
>> figure out whether or not a row should be moved, so I am making 
>> multiple
>> passes over the table.
> But no matter what, if all candidates for removal are to be treated in 
> the same way . . .
>
>> Right now I see two options
>>
>> 1) Write a function that inserts a row into the history table and then
>> removes it from the current
> Create a trigger BEFORE a DELETE event that moves these items to the 
> history table
>
> as explained better here:
>
> http://www.postgresql.org/docs/current/static/triggers.html
>
>> 2) Have a status flag that marks the rows for history and then insert
>> them into the history table then remove all of these rows from the
>> current.
>>
>> I would like to know what other peoples experiences have been with 
>> this
>> problem, and what would you suggest.
>>
>>
>> -- 
>> Dave Smith
>> CANdata Systems Ltd
>> 416-493-9020
>>
>>
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to 
>> majordomo(at)postgresql(dot)org)


pgsql-general by date

Next:From: Tom LaneDate: 2004-12-29 22:56:05
Subject: Re: pg_dump and pgpool
Previous:From: Steven KlassenDate: 2004-12-29 22:43:39
Subject: Re: pgsql question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group