On Aug 26, 2007, at 9:02 AM, Dawid Kuroczko wrote:
> On 8/26/07, Kevin Kempter <kevin@kevinkempterllc.com> wrote:
>> On Saturday 25 August 2007 21:10:19 Ron Johnson wrote:
>>> On 08/25/07 21:51, Kevin Kempter wrote:
>>>> Hi List;
>>>>
>>>> I have a very large table (52million rows) - I'm creating a copy
>>>> of it to
>>>> rid it of 35G worth of dead space, then I'll do a sync, drop the
>>>> original
>>>> table and rename table2.
>>>
>>> What is your definition of "dead space"?
>>>
>>> Bad rows, duplicate rows, old rows? Something else?
>>
>> deleted rows that should have been cleaned up with vacuum, problem
>> is the
>> client let it go so long that now I cant get a vacuum to finish
>> cause it
>> impacts the day2day operations too much. Long story, see my
>> recent questions
>> on the performance list for more info.
>
> In your place I would do something like Slony-I does, when
> it replicates the tables. Create on insert/update/delete triggers
> on table1 which will log operations on table1 to some table1_log
> table. Then copy table1 to table2. Then replay table1_log on
> table2, then BEGIN;LOCK tablel1;finish replaying the lock;DROP table1;
> alter table rename...;commit;
>
> Or perhaps actually use Slony-I for the above steps? Should work
> quite nicely... Or perhaps use SkyTools for it (I've never used it)?
>
Yeah, for trigger based replication it'd be simpler to just use Slony-
I or Skytools. However, if you're on 8.2, with row-wise comparisons,
you could do something like:
begin;
lock table1;
insert into table2
select *
from table1
where id not in (select id from test2);
drop table1;
alter table2 rename to table1;
commit;
Here id is your primary key. Note that if your ids are generated by
a sequence you'll need to use setval on the sequence to get it
"caught up" before that commit or you'll get duplicate key errors
immediately.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
