[Bucardo-general] Dropping Tables Breaks VAC

Markova, Nina Nina.Markova at NRCan-RNCan.gc.ca
Tue Oct 29 21:54:16 UTC 2013


Hello,

I am experience similar behaviour as  David described, even I deleted from bucardo tables as in 1)

0)   bucardo remove table public.locations   # removed it from the herd
1)   deal with bucardo
        psql webearthquakes             # as user postgres 
        select oid from pg_class where relname=locations;   
  oid=17793

        drop table locations ;         
        \c webearthquakes bucardo
        delete  from bucardo_delta_targets where tablename=17793
            #      did it very quick, so no  VAC errors
        drop table delta_public_locations
        drop table track_public_locations
        drop table stage_public_locations

3)  re-create table 'locations'
     
        create table locations as select * from locations2;
        alter table only locations add primary key (solution_id);

4)    Add locations to herd again   
        bucardo add table herd=webeq_herd db=sta_pgo3       # and here made a mistake - wrong db, locations wasn't added to that herd
  
5) tried with right db
        bucardo add table herd=webeq_herd db=sta_pgo3
     
VAC errors started:

NOTICE:  Rows deleted from delta_public_location_blacklist: 0 Rows deleted from track_public_location_blacklist: 0
(24364) [Tue Oct 29 21:25:29 2013] VAC Warning! VAC was killed at line 6793: DBD::Pg::st pg_result failed: ERROR:  relation "bucardo.delta_16728" does not exist
LINE 1: DELETE FROM bucardo."delta_16728" USING (SELECT txntime AS t...
                    ^
QUERY:  DELETE FROM bucardo."delta_16728" USING (SELECT txntime AS tt FROM bucardo."track_16728" GROUP BY 1 HAVING COUNT(*) = 3) AS foo WHERE txntime = tt AND txntime < now() - interval '45 seconds'
CONTEXT:  PL/pgSQL function "bucardo_purge_delta" line 46 at EXECUTE statement
SQL statement "SELECT                  bucardo.bucardo_purge_delta($1, myrec.tablename)"
PL/pgSQL function "bucardo_purge_delta" line 13 at SQL statement at /usr/local/share/perl5/Bucardo.pm line 6793.

I checked all databases and didn't find such oid??!
Any idea or help will be appreciated. 

webearthquakes=# select distinct tablename 
from bucardo.bucardo_delta_targets where 
tablename not in (select oid from pg_class);
 tablename 
-----------
(0 rows)

Code from Bucardo.pm below. In Bucardo.pm line 6793 is:
$count = $sth{"vac_$dbname"}->pg_result() 

in this block of code:

## Finish each one up
            for my $dbname (sort keys %{ $self->{sdb}} ) {

                $x = $self->{sdb}{$dbname};

                ## As above, skip if not a source or no schema available
                next if ! $x->{needsvac};

                next if ! $x->{hasschema};

                my $xdbh = $x->{dbh};

                $self->glog(qq{Finish and fetch bucardo_purge_delta on database "$dbname"}, LOG_DEBUG);
                $count = $sth{"vac_$dbname"}->pg_result();                        # --------------------------------------->>>>> line 6793 -------------------------------

                my $info = $sth{"vac_$dbname"}->fetchall_arrayref()->[0][0];
                $xdbh->commit();

                $self->glog(qq{Purge on db "$dbname" gave: $info}, LOG_VERBOSE);

            } ## end each source database

        } ## end of attempting to vacuum


Thanks,
Nina

-----Original Message-----
From: bucardo-general-bounces at bucardo.org [mailto:bucardo-general-bounces at bucardo.org] On Behalf Of David E. Wheeler
Sent: October-01-13 18:04
To: bucardo-general at bucardo.org List
Subject: [Bucardo-general] Dropping Tables Breaks VAC

Fellow Bucardoans,

We recently dropped a table that was being replicated by several syncs. First, we deactivated the syncs:

    for sync in sync1 sync2 sync3
    do
        bucardo deactivate $sync
    done

Next, we dropped the table from the database, then told Bucardo:

    bucardo remove table foo.bar

Finally, we restarted the syncs:

    for sync in sync1 sync2 sync3
    do
        bucardo activate $sync
    done

So far so good. The delta_foo_bar, stage_foo_bar, and track_foo_bar tables are still around, but I assume I can drop them and that will be that, right?

Except for one thing. The VAC process is choking on this error:

    (22360) [Tue Oct  1 12:49:37 2013] VAC Warning! VAC was killed at line 6785: DBD::Pg::st pg_result failed: ERROR:  relation "bucardo.delta_44905" does not exist
    LINE 1: DELETE FROM bucardo."delta_44905" USING (SELECT txntime AS t...
                        ^
    QUERY:  DELETE FROM bucardo."delta_44905" USING (SELECT txntime AS tt FROM bucardo."track_44905" GROUP BY 1 HAVING COUNT(*) = 4) AS foo WHERE txntime = tt AND txntime < now() - interval '45 seconds'
    CONTEXT:  PL/pgSQL function bucardo.bucardo_purge_delta(text,text) line 46 at EXECUTE statement
    SQL statement "SELECT                  bucardo.bucardo_purge_delta($1, myrec.tablename)"
    PL/pgSQL function bucardo_purge_delta(text) line 13 at SQL statement at /usr/share/perl5/vendor_perl/Bucardo.pm line 6785.

I was confused at first as to where Bucardo was finding 44905, but after digging into the code, I discovered that the VAC process is getting the list of tables from the bucardo_delta_targets table, where the tablename column is an OID. When you drop a table, it is removed from pg_class, so oid::regclass will return the integral values. Bizarrely, we have a bunch of bogus OIDs in that table:

    hi=# select distinct tablename::text, tablename::regclass
           from bucardo.bucardo_delta_targets
          where tablename::text = tablename::regclass::text;
     tablename | tablename 
    -----------+-----------
     45176     | 45176
     45111     | 45111
     44879     | 44879
     44843     | 44843
     45083     | 45083
     44659     | 44659
     44740     | 44740
     44991     | 44991
     44701     | 44701
     44955     | 44955
     45157     | 45157
     44830     | 44830
     44789     | 44789
     44905     | 44905
     45126     | 45126
     45026     | 45026
     44760     | 44760
     45046     | 45046
     44802     | 44802
    (19 rows)

44905 is one of them, of course. Another way of getting this info is to join to pg_class:

     select distinct tablename
       from bucardo.bucardo_delta_targets
      where tablename not in (select oid from pg_class);
     tablename 
    -----------
         44991
         44830
         45026
         45126
         44802
         45083
         44879
         44905
         44843
         44701
         44659
         45176
         44789
         44760
         44955
         45111
         45157
         44740
         45046

So, a few questions:

* Is it safe to delete these records?
* What other stuff do I need to clean up for dropped tables?
* Do I need to change anything on target systems, or just sources?
* Should we updated the `validate` command to clean things up, something like:

    DELETE FROM bucardo.bucardo_delta_targets
     where tablename NOT IN (select oid from pg_class);

?

Thanks,

David

_______________________________________________
Bucardo-general mailing list
Bucardo-general at bucardo.org
https://mail.endcrypt.com/mailman/listinfo/bucardo-general


More information about the Bucardo-general mailing list