On Sun, 2006-06-11 at 19:39 -0400, cbbrowne at ca.afilias.info wrote:
> > On 6/9/2006 5:45 PM, Christopher Browne wrote:
> >
> >> 2.  Load the data, and throw reindexing to a separate thread that might
> >> take place concurrently...
> >>
> >>     for each table t
> >>        deactivate indexes on t
> >>        copy data for T
> >>        reactivate indexes on t
> >>        throw table t to reindexing thread
> >>    done
> >
> > This won't work at all. Because the COPY thread's transaction isn't
> > committed yet, any other connection won't see the data to index.
> 
> It works if we modify slightly:
> 
>      for each table t
>         BEGIN;   -- on subscriber
>         deactivate indexes on t
>         copy data for T
>         reactivate indexes on t
>         COMMIT;  -- on subscriber
>         throw table t to reindexing thread
>     done


> In any case, by all means shoot holes in this.  The assumption that the
> subscriber does things in a single transaction seems to me to be one that
> probably needs to become invalid...

Pretty ugly to fiddle with table renaming. Especially bad if the process
stops midway through and needs to be restarted.

How about kicking off the thread earlier instead?

for each table t
        -- kick off new thread for subscriber DB work
        -- Establish connection to subscriber
        BEGIN; -- on subscriber
        DEACTIVATE INDEXES
        DEACTIVATE FKEY CONSTRAINTS (somehow...)
        COPY DATA for T
        -- indicate to parent process to continue with next table
        REACTIVATE INDEXES & REINDEX
        REACTIVATE FKEY CONSTRAINTS & FKEY CHECK
        COMMIT;
        -- Close subscriber db connection for T
        -- Close out subscriber thread for T
done

The next table can start the copy process while the thread is indexing
and enforcing constraints. Still a single transaction on the provider.
Still a single transaction on the subscriber for each table being
loaded.

Does copy defer fkey constraint enforcement already and do a bulk
comparison? If not, there is probably a pretty good performance gain to
be had by doing a bulk scan like ALTER TABLE ADD FKEY does.

If it does defer fkey checks, then there is nothing to
disable/re-enable.

The main reason to defer Fkey checks is to ensure that the FKey table
isn't going to block our COPY (t is foo, t+1 is bar, bar references
foo).

--