Обсуждение: Duplicate public schema and user tables

Hi,

We suddenly stumbled upon duplicate entities. Some of our databases
ended up with two 'public' schemas and several duplicate user tables
(sharing the same oid).
After checking through the logs, it doesn't appear to be a problem
resulting from wrap-around OID's. Though the logs mention
transaction-wraparound may have happened.

For the moment, we are trying to get ride of the duplicates and can't
manage to pull that off. We tried restarting the server in singe-user
mode with -P option to reindex the database, but it failed complaining
about the uniqueness of the index in 'pg_class'.

We're running PostgreSQL 8.0.1. Any help would be appreciated.

Regards,

Romain

The two public schemas :

bddreco=# SELECT *,oid from pg_namespace ;
       nspname       | nspowner |               nspacl                |
   oid
--------------------+----------+-------------------------------------+----------
  pg_toast           |        1 |                                     |
       99
  pg_temp_1          |        1 |                                     |
    16847
  pg_catalog         |        1 | {postgres=UC/postgres,=U/postgres}  |
       11
  public             |        1 | {postgres=UC/postgres,=UC/postgres} |
     2200
  information_schema |        1 | {postgres=UC/postgres,=U/postgres}  |
    17057
  public             |        1 | {postgres=UC/postgres,=UC/postgres} |
73794132
(6 rows)


Duplicate user table :

bddreco=# SELECT *,oid from pg_class where relname='series';
  relname | relnamespace | reltype  | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass |                                          relacl
                                     |   oid

  series  |     73794132 | 73794327 |      102 |     0 |    73794326 |
            0 |        0 |         0 |             0 |             0 | t
           | f           | r       |        4 |         0 |           5
|        0 |        0 |       0 | t          | t          | f
| f              |
{granitadmin=arwdRxt/granitadmin,granitguest=r/granitadmin,granitserie=arwd/granitadmin}
| 73794326
  series  |     73794132 | 73794327 |      102 |     0 |    73794326 |
            0 |        0 |         0 |             0 |             0 | t
           | f           | r       |        4 |         0 |           5
|        0 |        0 |       0 | t          | t          | f
| f              |
{granitadmin=arwdRxt/granitadmin,granitguest=r/granitadmin,granitserie=arwd/granitadmin}
| 73794326
  series  |     73794132 | 73794327 |      102 |     0 |    73794326 |
            0 |        0 |         0 |             0 |             0 | t
           | f           | r       |        4 |         0 |           5
|        0 |        0 |       0 | t          | t          | f
| f              |
{granitadmin=arwdRxt/granitadmin,granitguest=r/granitadmin,granitserie=arwd/granitadmin}
| 73794326
  series  |     73794132 | 73794327 |      102 |     0 |    73794326 |
            0 |        1 |         1 |             0 |             0 | t
           | f           | r       |        4 |         0 |           5
|        0 |        0 |       0 | t          | t          | f
| f              |
{granitadmin=arwdRxt/granitadmin,granitguest=r/granitadmin,granitserie=arwd/granitadmin}
| 73794326
(4 rows)

On Wed, Sep 26, 2007 at 10:51:43AM +0200, Romain Roure wrote:
> Hi,
>
> We suddenly stumbled upon duplicate entities. Some of our databases
> ended up with two 'public' schemas and several duplicate user tables
> (sharing the same oid).
> After checking through the logs, it doesn't appear to be a problem
> resulting from wrap-around OID's. Though the logs mention
> transaction-wraparound may have happened.

Please shouw us the xmin,xmax columns to the pg_class tables. But if
you've wrapped around so far to get old column, then you passed the
wraparound horizon 2 billion transactions ago. Please show us exactly
what the logs say:

Oh, and do you have any backups?

> We're running PostgreSQL 8.0.1. Any help would be appreciated.

You need to be running VACUUM...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Wed, Sep 26, 2007 at 10:51:43AM +0200, Romain Roure wrote:
>> After checking through the logs, it doesn't appear to be a problem
>> resulting from wrap-around OID's. Though the logs mention
>> transaction-wraparound may have happened.

> Please shouw us the xmin,xmax columns to the pg_class tables. But if
> you've wrapped around so far to get old column, then you passed the
> wraparound horizon 2 billion transactions ago. Please show us exactly
> what the logs say:

If there are complaints like that in the logs, it seems hugely
optimistic to suppose that you don't have a wraparound problem ...

If it is wraparound, it's possible that a VACUUM on pg_class would fix
this.  It will certainly not do any harm to try it.

>> We're running PostgreSQL 8.0.1. Any help would be appreciated.

> You need to be running VACUUM...

Not to mention running a much newer release.  8.0 is still supported,
but it's up to 8.0.14 now.  You're not going to find a lot of sympathy
if this turns out to have been caused by a bug that was fixed since
8.0.1 --- that subrelease was obsoleted over two years ago.

            regards, tom lane