Oh, and the duplication is not isolated but I only went through the one
case when checking the indexes.
ssdb=# select ctid, xmin, cmin, xmax, account_instance_id, keyword_id
from feature_keyword_supply_google where (account_instance_id,
keyword_id) in (select account_instance_id, keyword_id from
feature_keyword_supply_google group by account_instance_id, keyword_id
having count(*) > 1) order by account_instance_id, keyword_id; ctid | xmin | cmin | xmax |
account_instance_id|
keyword_id
------------+-----------+-------+-----------+---------------------+------------ (5454,81) | 338184867 | 2259 |
485608742| 1215 |
1646046(3396,123) | 485608742 | 2480 | 0 | 1215 |
1646046(3396,128) | 485608853 | 2552 | 0 | 1215 |
1646058 (5454,83) | 338185099 | 2335 | 485608853 | 1215 |
1646058(3396,126) | 485608832 | 2516 | 0 | 1215 |
1646076 (5454,82) | 338184954 | 2297 | 485608832 | 1215 |
1646076(3396,130) | 485608909 | 2588 | 0 | 1215 |
1646092 (5454,85) | 338185239 | 2420 | 485608909 | 1215 |
1646092(3396,132) | 485608931 | 2624 | 0 | 1215 |
1646097 (5454,86) | 338185486 | 2458 | 485608931 | 1215 |
1646097 (3414,82) | 309534239 | 9967 | 620905091 | 1603 |
1431827(3997,116) | 620905091 | 6859 | 0 | 1603 |
1431827 (4718,58) | 721916095 | 1046 | 0 | 2034 |
53759 (6580,86) | 357774736 | 4616 | 721916095 | 2034 |
53759 (183,47) | 499027939 | 7824 | 621991044 | 3673 |
41599 (183,14) | 621991044 | 7154 | 0 | 3673 |
41599 (1836,46) | 389868188 | 18424 | 628568217 | 4163 |
622560 (9202,69) | 628568217 | 13409 | 0 | 4163 |
622560 (52,89) | 340438230 | 10823 | 0 | 4634 |
32949 (52,38) | 95782780 | 37 | 340438230 | 4634 |
32949 (594,7) | 113806807 | 11714 | 506856848 | 5218 |
255688 (594,43) | 506856848 | 4489 | 0 | 5218 |
255688(2589,115) | 206806182 | 14750 | 206854773 | 6594 |
36790(2599,115) | 206854773 | 18022 | 0 | 6594 |
36790(2585,134) | 206798169 | 10492 | 206845937 | 6594 |
110879 (2598,24) | 206845937 | 12819 | 0 | 6594 |
110879 (5589,81) | 439221415 | 1636 | 552554533 | 8127 |
2207941(8626,114) | 552554533 | 13066 | 743644204 | 8127 |
2207941 (1662,71) | 487505211 | 6370 | 487566654 | 8527 |
68364 (1662,54) | 487566654 | 6367 | 0 | 8527 |
68364 (615,115) | 487515981 | 7604 | 0 | 8527 |
696634 (609,69) | 487395439 | 6666 | 487515981 | 8527 |
696634 (5771,9) | 340367613 | 300 | 508552100 | 10003 |
660208 (5771,28) | 508552100 | 6390 | 650701855 | 10003 |
660208 (7827,7) | 278400268 | 14702 | 587980438 | 10604 |
148263 (7827,94) | 587980438 | 9543 | 0 | 10604 |
148263(8267,128) | 283872022 | 16451 | 547602059 | 10781 |
1158532(3479,119) | 547602059 | 15092 | 0 | 10781 |
1158532 (8267,55) | 283853649 | 13761 | 547597049 | 10781 |
1274445 (2794,66) | 547597049 | 12576 | 0 | 10781 |
1274445 (3198,86) | 485581600 | 7681 | 0 | 10892 |
99247 (3198,51) | 365165699 | 14941 | 485581600 | 10892 |
99247 (757,116) | 123605388 | 13927 | 377061887 | 11888 |
330758 (757,19) | 377061887 | 12522 | 0 | 11888 |
330758 (4277,76) | 397751291 | 4921 | 506766356 | 11916 |
1985203 (4277,66) | 506766356 | 3649 | 0 | 11916 |
1985203 (4277,82) | 506766470 | 3733 | 0 | 11916 |
1985208 (4277,79) | 397751662 | 5017 | 506766470 | 11916 |
1985208 (4277,81) | 506766418 | 3705 | 0 | 11916 |
1985226 (4277,78) | 397751549 | 4985 | 506766418 | 11916 |
1985226 (4277,73) | 397751023 | 4860 | 506766292 | 11916 |
1985354 (4277,64) | 506766292 | 3621 | 0 | 11916 |
1985354 (4277,60) | 506766160 | 3593 | 744608069 | 11916 |
1985374 (4277,72) | 397750949 | 4828 | 506766160 | 11916 |
1985374 (5007,42) | 397790263 | 10647 | 506787452 | 11916 |
1985394 (3767,68) | 506787452 | 7682 | 0 | 11916 |
1985394 (3421,94) | 573110542 | 6390 | 0 | 12399 |
2922605 (2815,48) | 573017088 | 5568 | 573110542 | 12399 |
2922605 (1975,38) | 413415231 | 10327 | 573873717 | 12883 |
443196 (1975,24) | 573873717 | 7400 | 0 | 12883 |
443196(7842,106) | 412053873 | 11664 | 0 | 13111 |
769855 (7842,43) | 300750744 | 4849 | 412053873 | 13111 |
769855 (7469,79) | 512363410 | 4282 | 512399373 | 13174 |
1317593 (7469,99) | 512399373 | 4262 | 0 | 13174 |
1317593 (7454,28) | 396487100 | 4877 | 396555126 | 13235 |
1199747 (7454,17) | 274011632 | 2793 | 396487100 | 13235 |
1199747 (9781,43) | 674282871 | 8976 | 0 | 13269 |
259255(7736,122) | 277309368 | 8612 | 674282871 | 13269 |
259255 (3350,12) | 606411328 | 2401 | 744153965 | 13272 |
428208 (3350,65) | 464622255 | 2128 | 606411328 | 13272 |
428208(8438,121) | 711745644 | 1275 | 0 | 13607 |
1559247 (8438,27) | 530172971 | 4847 | 711745644 | 13607 |
1559247 (2004,64) | 389876221 | 9583 | 546064672 | 14335 |
696146 (2004,41) | 546064672 | 8668 | 0 | 14335 |
696146 (904,73) | 553271853 | 13376 | 0 | 14404 |
2208853 (251,19) | 447353031 | 13395 | 553271853 | 14404 |
2208853 (8654,23) | 553245993 | 9327 | 0 | 14404 |
2208932 (60,60) | 447334230 | 9340 | 553245993 | 14404 |
2208932 (9442,71) | 644636546 | 7353 | 0 | 16423 |
404048 (3092,59) | 644579575 | 6363 | 644636546 | 16423 |
404048
(80 rows)
On Wed, 2005-11-09 at 10:27 -0500, Rod Taylor wrote:
> 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, compiled by 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 null
> keyword_id | integer |
> not null
> feature_keyword_supply_google_score | natural_number |
> not null
> feature_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
>
--
