Re: SQL Diff ?

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