Re: Possible savepoint bug - Mailing list pgsql-hackers

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
> 
--