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
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
