[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