BUG #3110: Online Backup introduces Duplicate OIDs - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3110
Logged by:          Randy Isbell
Email address:      jisbell@cisco.com
PostgreSQL version: 8.2.3
Operating system:   FreeBSD 6.1 i386
Description:        Online Backup introduces Duplicate OIDs
Details:

This issue is observed in version 8.0.7 on FreeBSD 6.0 and 8.0.7 on FreeBSD
6.1 as well.

When restoring the output of an online backup, many tables now have
duplicate OID values / primary keys, viz:

--- (cut here) ---
# psql -Upostgres sn
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

sn=# \d at_dns
                   Table "public.at_dns"
       Column        |           Type           | Modifiers
---------------------+--------------------------+-----------
 ac_b                | text                     | not null
 ac_zone             | text                     | not null
 ac_host             | text                     | not null
 ac_ttl              | bigint                   | not null
 ac_type             | text                     | not null
 ac_data             | text                     | not null
 ac_password         | text                     |
 ac_desc             | text                     |
 ac_lastupdate       | timestamp with time zone |
 ac_lastddns         | timestamp with time zone |
 ac_mx_priority      | bigint                   |
 ac_soa_contact      | text                     |
 ac_soa_serial       | bigint                   |
 ac_soa_refresh      | bigint                   |
 ac_soa_retry        | bigint                   |
 ac_soa_expire       | bigint                   |
 ac_soa_minimum      | bigint                   |
 ac_srv_priority     | integer                  |
 ac_srv_weight       | integer                  |
 ac_srv_port         | integer                  |
 ac_naptr_flags      | text                     |
 ac_naptr_order      | integer                  |
 ac_naptr_preference | integer                  |
 ac_naptr_service    | text                     |
 ac_naptr_regexp     | text                     |
 ac_naptr_ac_n       | text                     |
Indexes:
    "ai_dns_host" PRIMARY KEY, btree (ac_zone, ac_host, ac_type, ac_data)
    "ai_dns_b_host" UNIQUE, btree (ac_b, ac_zone, ac_host, ac_type,
ac_data)
    "ai_oid_dns" UNIQUE, btree (oid)
Triggers:
    at_dns_delete_serial AFTER DELETE ON at_dns FOR EACH ROW EXECUTE
PROCEDURE at_delete_serial()
    at_dns_touch AFTER INSERT OR DELETE OR UPDATE ON at_dns FOR EACH
STATEMENT EXECUTE PROCEDURE af_file_touch()
    at_dns_update_serial AFTER INSERT OR UPDATE ON at_dns FOR EACH ROW
EXECUTE PROCEDURE at_update_serial()


sn=# select ac_zone,ac_host,ac_type,ac_data, count(oid) from at_dns group by
ac_zone,ac_host,ac_type,ac_data having count(oid) > 1;
      ac_zone      | ac_host | ac_type |        ac_data         | count
-------------------+---------+---------+------------------------+-------
 nqa5.l1.cisco.com | @       | soa     | ns1.nqa5.l1.cisco.com. |     2
(1 row)

sn=# reindex table at_dns;
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.

--- (cut here) ---

I verified the source database does NOT have duplicate values prior to the
online dump.

Note that this problem seems to occur when the database is under load.  The
above error results when approximately 30 transactions per second are active
during the online backup.  A quiescent database does NOT exhibit this
problem.

Thoughts?

- r.