Possible savepoint bug - Mailing list pgsql-hackers

As you can see, we have duplicates within the table (heap) of a primary
key value. The index itself only references one of these tuples.

Nearly all data inserted into this table is wrapped in a subtransaction,
and is created a single tuple per subtransaction. About 20% of entries
are duplicate, so we catch the UNIQUE VIOLATION and restore to the
savepoint.

I did keep a copy of the table. Compressed it is about 24MB.

After trying everything below, I also gave it a run with vacuum full. It
did not change the output.


ssdb=# select version();                                 version
---------------------------------------------------------------------------PostgreSQL 8.0.3 on sparc-sun-solaris2.9,
compiledby GCC gcc (GCC)
 
3.4.2
(1 row)

ssdb=# \d feature_keyword_supply_google                  Table "public.feature_keyword_supply_google"
Column                   |         Type          |
 
Modifiers
----------------------------------------------+-----------------------+-----------account_instance_id
      | integer               |
 
not nullkeyword_id                                   | integer               |
not nullfeature_keyword_supply_google_score          | natural_number        |
not nullfeature_keyword_supply_google_last_collected | ss_timestamp_recorded |
not null
Indexes:   "feature_keyword_supply_google_pkey" PRIMARY KEY, btree
(account_instance_id, keyword_id)
Foreign-key constraints:   "feature_keyword_supply_google_account_instance_id_fkey" FOREIGN KEY
(account_instance_id, keyword_id) REFERENCES
feature_keyword(account_instance_id, keyword_id) ON UPDATE CASCADE ON
DELETE CASCADE

ssdb=# set enable_indexscan TO off;
SET
ssdb=# select ctid, xmin, cmin, xmax, account_instance_id = 11916,
keyword_id = 1985374 from feature_keyword_supply_google where keyword_id
= 1985374 and account_instance_id = 11916;  ctid    |   xmin    | cmin |   xmax    | ?column? | ?column?
-----------+-----------+------+-----------+----------+----------(4277,60) | 506766160 | 3593 | 744608069 | t        |
t(4277,72)| 397750949 | 4828 | 506766160 | t        | t
 
(2 rows)

ssdb=# set enable_indexscan TO on;
SET
ssdb=# select ctid, xmin, cmin, xmax, account_instance_id = 11916,
keyword_id = 1985374 from feature_keyword_supply_google where keyword_id
= 1985374 and account_instance_id = 11916;  ctid    |   xmin    | cmin |   xmax    | ?column? | ?column?
-----------+-----------+------+-----------+----------+----------(4277,60) | 506766160 | 3593 | 744608069 | t        |
t
(1 row)

ssdb=# vacuum feature_keyword_supply_google;
VACUUM
ssdb=# set enable_indexscan = off;
SET
ssdb=#  select ctid, xmin, cmin, xmax, account_instance_id = 11916,
keyword_id = 1985374 from feature_keyword_supply_google where keyword_id
= 1985374 and account_instance_id = 11916;  ctid    |   xmin    | cmin |   xmax    | ?column? | ?column?
-----------+-----------+------+-----------+----------+----------(4277,60) | 506766160 | 3593 | 744608069 | t        |
t(4277,72)| 397750949 | 4828 | 506766160 | t        | t
 
(2 rows)

[root@DB1 rbt]# pg_controldata --version
pg_controldata (PostgreSQL) 8.0.3
[root@DB1 rbt]# pg_controldata /var/opt/pgsql/data
pg_control version number:            74
Catalog version number:               200411041
Database system identifier:           4769850195962887551
Database cluster state:               in production
pg_control last modified:             Wed Nov 09 10:10:26 2005
Current log file ID:                  860
Next log file segment:                170
Latest checkpoint location:           35C/A14C8D60
Prior checkpoint location:            35C/908D6470
Latest checkpoint's REDO location:    35C/A1440E20
Latest checkpoint's UNDO location:    0/0
Latest checkpoint's TimeLineID:       1
Latest checkpoint's NextXID:          745383235
Latest checkpoint's NextOID:          30513944
Time of latest checkpoint:            Wed Nov 09 09:42:53 2005
Database block size:                  8192
Blocks per segment of large relation: 131072
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum number of function arguments: 32
Date/time type storage:               floating-point numbers
Maximum length of locale name:        128
LC_COLLATE:                           en_US
LC_CTYPE:                             en_US

--