Postgres Pro
Downloads
Home   >   mailing lists

Thread: Some strange bug with drop table with slony cluster

Some strange bug with drop table with slony cluster

From
Maxim Boguk
Date:
DB version: PostgreSQL 8.3.6 (under linux)
no server/db crashes happen before.

Server was slave in slony replication.

Now problem:
table was unsubscribed from replication (without any errors)
and then dropped from master without any errors

But when i try drop table from slave i got very strange error:

hh=# drop TABLE metro_station_old;
ERROR:  "area_pk" is an index

hh=# \d+ metro_station_old
                             Table "public.metro_station_old"
       Column      |         Type          |           Modifiers           | Description
------------------+-----------------------+-------------------------------+-------------
  metro_station_id | integer               | not null                      |
  city_id          | integer               | not null                      |
  metro_line_id    | integer               | not null                      |
  city_district_id | integer               | not null                      |
  name             | character varying(64) | not null                      |
  image_point_x    | integer               |                               |
  image_point_y    | integer               |                               |
  acronym_line     | character varying(10) | default ''::character varying |
  colocation       | integer               |                               |
Foreign-key constraints:
     "metro_station_ibfk_1" FOREIGN KEY (city_id) REFERENCES area(area_id)
     "metro_station_ibfk_2" FOREIGN KEY (metro_line_id) REFERENCES metro_line(metro_line_id)
     "metro_station_ibfk_3" FOREIGN KEY (city_district_id) REFERENCES tables_to_drop.city_district(city_district_id)
Has OIDs: no

hh=# \d+ area_pk
      Index "public.area_pk"
  Column  |  Type   | Description
---------+---------+-------------
  area_id | integer |
primary key, btree, for table "public.area"

complete independent things.
Also:
hh=# ALTER TABLE metro_station_old drop constraint "metro_station_ibfk_1";
ERROR:  "area_pk" is an index
hh=# ALTER TABLE metro_station_old drop constraint "metro_station_ibfk_2";
ERROR:  "metro_line_pk" is an index
hh=# ALTER TABLE metro_station_old drop constraint "metro_station_ibfk_3";
ERROR:  "city_district_pk" is an index

First thing witch i think it is corrupted cache in shared memory or something like...
but fresh restart of db server do not change situation.

second thing is possible broken system indexes... but
reindex system hh;
not help too

hh=# SELECT oid from pg_class where relname='metro_station_old';
   oid
-------
  17542
(1 row)

hh=# SELECT oid from pg_class where relname='area_pk';
   oid
-------
  18933
(1 row)

Now i out of ideas.

Main thing what made me worry is second slave show same issue. So that is not single random error.

Because one of slaves is pure backup i can easy experiment with him.
But need any hints what to look next.

PS: one additional info... i can rename table via alter without errors. But still can't drop it.

--
SY, Maxim Boguk

Re: Some strange bug with drop table with slony cluster

From
Tom Lane
Date:
Maxim Boguk <mboguk@masterhost.ru> writes:
> Server was slave in slony replication.

> Now problem:
> table was unsubscribed from replication (without any errors)
> and then dropped from master without any errors

> But when i try drop table from slave i got very strange error:
> hh=# drop TABLE metro_station_old;
> ERROR:  "area_pk" is an index

You'd be best off to ask about this on the Slony mailing lists.
Slony plays some strange games with the system catalogs, and
I think that cases like this are fairly common if you don't perform
schema changes on slon-ified tables in just exactly the right way.
Hopefully somebody over there will recognize this particular
symptom and help you out.

            regards, tom lane

Re: Some strange bug with drop table with slony cluster

From
Scott Marlowe
Date:
2009/6/17 Maxim Boguk <mboguk@masterhost.ru>:
> DB version: PostgreSQL 8.3.6 (under linux)
> no server/db crashes happen before.
>
> Server was slave in slony replication.
>
> Now problem:
> table was unsubscribed from replication (without any errors)
> and then dropped from master without any errors
>
> But when i try drop table from slave i got very strange error:

We've run into issues with drop table and slony as well.  What version
of slony are you running?  We're running 1.2.14.  Latest version in
that branch in 1.2.16, but we haven't had cause to upgrade to it just
yet.  I'll be looking at 2.0.latest and 1.2.16 over the summer to see
if one or the other can fix this issue for us.

Re: Some strange bug with drop table with slony cluster

From
Tom Lane
Date:
Scott Marlowe <scott.marlowe@gmail.com> writes:
> We've run into issues with drop table and slony as well.  What version
> of slony are you running?  We're running 1.2.14.  Latest version in
> that branch in 1.2.16, but we haven't had cause to upgrade to it just
> yet.  I'll be looking at 2.0.latest and 1.2.16 over the summer to see
> if one or the other can fix this issue for us.

FWIW, I believe one of the design goals for 2.0 was to get rid of their
direct manipulation of the system catalogs, which should in turn remove
this type of hazard.  I don't know the actual status though.

            regards, tom lane