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: [email protected]
[mailto:[email protected]] On Behalf Of David E. Wheeler
Sent: October-01-13 18:04
To: [email protected] 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
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
_______________________________________________
Bucardo-general mailing list
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
