[Bucardo-general] Dropping Tables Breaks VAC
David E. Wheeler
david at justatheory.com
Tue Oct 1 22:04:21 UTC 2013
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
More information about the Bucardo-general
mailing list