| -- White-box tests asserting composition of AO/CO block directory entries. |
| -- All tuples are directed to seg0 and each INSERT has an increasing row count |
| -- to make their identification easy. |
| |
| -------------------------------------------------------------------------------- |
| -- AO tables |
| -------------------------------------------------------------------------------- |
| |
| CREATE TABLE ao_blkdir_test(i int, j int) USING ao_row DISTRIBUTED BY (j); |
| CREATE |
| CREATE INDEX ao_blkdir_test_idx ON ao_blkdir_test(i); |
| CREATE |
| |
| 1: INSERT INTO ao_blkdir_test SELECT i, 2 FROM generate_series(1, 10) i; |
| INSERT 10 |
| -- There should be 1 block directory row with a single entry covering 10 rows |
| SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,1) | 1 | 0 | 0 | 1 | 0 | 10 |
| (1 row) |
| |
| 1: INSERT INTO ao_blkdir_test SELECT i, 2 FROM generate_series(11, 30) i; |
| INSERT 20 |
| -- There should be 2 block directory entries in a new block directory row, and |
| -- the row from the previous INSERT should not be visible. The entry from the |
| -- first INSERT should remain unchanged. |
| SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,2) | 1 | 0 | 0 | 1 | 0 | 10 |
| (0,2) | 1 | 0 | 1 | 101 | 216 | 20 |
| (2 rows) |
| |
| 1: BEGIN; |
| BEGIN |
| 1: INSERT INTO ao_blkdir_test SELECT i, 2 FROM generate_series(31, 60) i; |
| INSERT 30 |
| 2: BEGIN; |
| BEGIN |
| 2: INSERT INTO ao_blkdir_test SELECT i, 2 FROM generate_series(71, 110) i; |
| INSERT 40 |
| 1: COMMIT; |
| COMMIT |
| 2: COMMIT; |
| COMMIT |
| -- The second INSERT of 40 rows above would have landed in segfile 1 (unlike |
| -- segfile 0, like the first INSERT of 30 rows above). This should be reflected |
| -- in the block directory entries for these rows. |
| SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,3) | 1 | 0 | 0 | 1 | 0 | 10 |
| (0,3) | 1 | 0 | 1 | 101 | 216 | 20 |
| (0,3) | 1 | 0 | 2 | 201 | 608 | 30 |
| (0,4) | 2 | 0 | 0 | 1 | 0 | 40 |
| (4 rows) |
| |
| TRUNCATE ao_blkdir_test; |
| TRUNCATE |
| set gp_appendonly_insert_files = 0; |
| SET |
| -- Insert enough rows to overflow the first block directory minipage by 2. |
| INSERT INTO ao_blkdir_test SELECT i, 2 FROM generate_series(1, 292700) i; |
| INSERT 292700 |
| reset gp_appendonly_insert_files; |
| RESET |
| -- There should be 2 block directory rows, one with 161 entries covering 292698 |
| -- rows and the other with 1 entry covering the 2 overflow rows. |
| SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,1) | 1 | 0 | 0 | 1 | 0 | 1818 |
| (0,1) | 1 | 0 | 1 | 1819 | 32760 | 1818 |
| (0,1) | 1 | 0 | 2 | 3637 | 65520 | 1818 |
| (0,1) | 1 | 0 | 3 | 5455 | 98280 | 1818 |
| (0,1) | 1 | 0 | 4 | 7273 | 131040 | 1818 |
| (0,1) | 1 | 0 | 5 | 9091 | 163800 | 1818 |
| (0,1) | 1 | 0 | 6 | 10909 | 196560 | 1818 |
| (0,1) | 1 | 0 | 7 | 12727 | 229320 | 1818 |
| (0,1) | 1 | 0 | 8 | 14545 | 262080 | 1818 |
| (0,1) | 1 | 0 | 9 | 16363 | 294840 | 1818 |
| (0,1) | 1 | 0 | 10 | 18181 | 327600 | 1818 |
| (0,1) | 1 | 0 | 11 | 19999 | 360360 | 1818 |
| (0,1) | 1 | 0 | 12 | 21817 | 393120 | 1818 |
| (0,1) | 1 | 0 | 13 | 23635 | 425880 | 1818 |
| (0,1) | 1 | 0 | 14 | 25453 | 458640 | 1818 |
| (0,1) | 1 | 0 | 15 | 27271 | 491400 | 1818 |
| (0,1) | 1 | 0 | 16 | 29089 | 524160 | 1818 |
| (0,1) | 1 | 0 | 17 | 30907 | 556920 | 1818 |
| (0,1) | 1 | 0 | 18 | 32725 | 589680 | 1818 |
| (0,1) | 1 | 0 | 19 | 34543 | 622440 | 1818 |
| (0,1) | 1 | 0 | 20 | 36361 | 655200 | 1818 |
| (0,1) | 1 | 0 | 21 | 38179 | 687960 | 1818 |
| (0,1) | 1 | 0 | 22 | 39997 | 720720 | 1818 |
| (0,1) | 1 | 0 | 23 | 41815 | 753480 | 1818 |
| (0,1) | 1 | 0 | 24 | 43633 | 786240 | 1818 |
| (0,1) | 1 | 0 | 25 | 45451 | 819000 | 1818 |
| (0,1) | 1 | 0 | 26 | 47269 | 851760 | 1818 |
| (0,1) | 1 | 0 | 27 | 49087 | 884520 | 1818 |
| (0,1) | 1 | 0 | 28 | 50905 | 917280 | 1818 |
| (0,1) | 1 | 0 | 29 | 52723 | 950040 | 1818 |
| (0,1) | 1 | 0 | 30 | 54541 | 982800 | 1818 |
| (0,1) | 1 | 0 | 31 | 56359 | 1015560 | 1818 |
| (0,1) | 1 | 0 | 32 | 58177 | 1048320 | 1818 |
| (0,1) | 1 | 0 | 33 | 59995 | 1081080 | 1818 |
| (0,1) | 1 | 0 | 34 | 61813 | 1113840 | 1818 |
| (0,1) | 1 | 0 | 35 | 63631 | 1146600 | 1818 |
| (0,1) | 1 | 0 | 36 | 65449 | 1179360 | 1818 |
| (0,1) | 1 | 0 | 37 | 67267 | 1212120 | 1818 |
| (0,1) | 1 | 0 | 38 | 69085 | 1244880 | 1818 |
| (0,1) | 1 | 0 | 39 | 70903 | 1277640 | 1818 |
| (0,1) | 1 | 0 | 40 | 72721 | 1310400 | 1818 |
| (0,1) | 1 | 0 | 41 | 74539 | 1343160 | 1818 |
| (0,1) | 1 | 0 | 42 | 76357 | 1375920 | 1818 |
| (0,1) | 1 | 0 | 43 | 78175 | 1408680 | 1818 |
| (0,1) | 1 | 0 | 44 | 79993 | 1441440 | 1818 |
| (0,1) | 1 | 0 | 45 | 81811 | 1474200 | 1818 |
| (0,1) | 1 | 0 | 46 | 83629 | 1506960 | 1818 |
| (0,1) | 1 | 0 | 47 | 85447 | 1539720 | 1818 |
| (0,1) | 1 | 0 | 48 | 87265 | 1572480 | 1818 |
| (0,1) | 1 | 0 | 49 | 89083 | 1605240 | 1818 |
| (0,1) | 1 | 0 | 50 | 90901 | 1638000 | 1818 |
| (0,1) | 1 | 0 | 51 | 92719 | 1670760 | 1818 |
| (0,1) | 1 | 0 | 52 | 94537 | 1703520 | 1818 |
| (0,1) | 1 | 0 | 53 | 96355 | 1736280 | 1818 |
| (0,1) | 1 | 0 | 54 | 98173 | 1769040 | 1818 |
| (0,1) | 1 | 0 | 55 | 99991 | 1801800 | 1818 |
| (0,1) | 1 | 0 | 56 | 101809 | 1834560 | 1818 |
| (0,1) | 1 | 0 | 57 | 103627 | 1867320 | 1818 |
| (0,1) | 1 | 0 | 58 | 105445 | 1900080 | 1818 |
| (0,1) | 1 | 0 | 59 | 107263 | 1932840 | 1818 |
| (0,1) | 1 | 0 | 60 | 109081 | 1965600 | 1818 |
| (0,1) | 1 | 0 | 61 | 110899 | 1998360 | 1818 |
| (0,1) | 1 | 0 | 62 | 112717 | 2031120 | 1818 |
| (0,1) | 1 | 0 | 63 | 114535 | 2063880 | 1818 |
| (0,1) | 1 | 0 | 64 | 116353 | 2096640 | 1818 |
| (0,1) | 1 | 0 | 65 | 118171 | 2129400 | 1818 |
| (0,1) | 1 | 0 | 66 | 119989 | 2162160 | 1818 |
| (0,1) | 1 | 0 | 67 | 121807 | 2194920 | 1818 |
| (0,1) | 1 | 0 | 68 | 123625 | 2227680 | 1818 |
| (0,1) | 1 | 0 | 69 | 125443 | 2260440 | 1818 |
| (0,1) | 1 | 0 | 70 | 127261 | 2293200 | 1818 |
| (0,1) | 1 | 0 | 71 | 129079 | 2325960 | 1818 |
| (0,1) | 1 | 0 | 72 | 130897 | 2358720 | 1818 |
| (0,1) | 1 | 0 | 73 | 132715 | 2391480 | 1818 |
| (0,1) | 1 | 0 | 74 | 134533 | 2424240 | 1818 |
| (0,1) | 1 | 0 | 75 | 136351 | 2457000 | 1818 |
| (0,1) | 1 | 0 | 76 | 138169 | 2489760 | 1818 |
| (0,1) | 1 | 0 | 77 | 139987 | 2522520 | 1818 |
| (0,1) | 1 | 0 | 78 | 141805 | 2555280 | 1818 |
| (0,1) | 1 | 0 | 79 | 143623 | 2588040 | 1818 |
| (0,1) | 1 | 0 | 80 | 145441 | 2620800 | 1818 |
| (0,1) | 1 | 0 | 81 | 147259 | 2653560 | 1818 |
| (0,1) | 1 | 0 | 82 | 149077 | 2686320 | 1818 |
| (0,1) | 1 | 0 | 83 | 150895 | 2719080 | 1818 |
| (0,1) | 1 | 0 | 84 | 152713 | 2751840 | 1818 |
| (0,1) | 1 | 0 | 85 | 154531 | 2784600 | 1818 |
| (0,1) | 1 | 0 | 86 | 156349 | 2817360 | 1818 |
| (0,1) | 1 | 0 | 87 | 158167 | 2850120 | 1818 |
| (0,1) | 1 | 0 | 88 | 159985 | 2882880 | 1818 |
| (0,1) | 1 | 0 | 89 | 161803 | 2915640 | 1818 |
| (0,1) | 1 | 0 | 90 | 163621 | 2948400 | 1818 |
| (0,1) | 1 | 0 | 91 | 165439 | 2981160 | 1818 |
| (0,1) | 1 | 0 | 92 | 167257 | 3013920 | 1818 |
| (0,1) | 1 | 0 | 93 | 169075 | 3046680 | 1818 |
| (0,1) | 1 | 0 | 94 | 170893 | 3079440 | 1818 |
| (0,1) | 1 | 0 | 95 | 172711 | 3112200 | 1818 |
| (0,1) | 1 | 0 | 96 | 174529 | 3144960 | 1818 |
| (0,1) | 1 | 0 | 97 | 176347 | 3177720 | 1818 |
| (0,1) | 1 | 0 | 98 | 178165 | 3210480 | 1818 |
| (0,1) | 1 | 0 | 99 | 179983 | 3243240 | 1818 |
| (0,1) | 1 | 0 | 100 | 181801 | 3276000 | 1818 |
| (0,1) | 1 | 0 | 101 | 183619 | 3308760 | 1818 |
| (0,1) | 1 | 0 | 102 | 185437 | 3341520 | 1818 |
| (0,1) | 1 | 0 | 103 | 187255 | 3374280 | 1818 |
| (0,1) | 1 | 0 | 104 | 189073 | 3407040 | 1818 |
| (0,1) | 1 | 0 | 105 | 190891 | 3439800 | 1818 |
| (0,1) | 1 | 0 | 106 | 192709 | 3472560 | 1818 |
| (0,1) | 1 | 0 | 107 | 194527 | 3505320 | 1818 |
| (0,1) | 1 | 0 | 108 | 196345 | 3538080 | 1818 |
| (0,1) | 1 | 0 | 109 | 198163 | 3570840 | 1818 |
| (0,1) | 1 | 0 | 110 | 199981 | 3603600 | 1818 |
| (0,1) | 1 | 0 | 111 | 201799 | 3636360 | 1818 |
| (0,1) | 1 | 0 | 112 | 203617 | 3669120 | 1818 |
| (0,1) | 1 | 0 | 113 | 205435 | 3701880 | 1818 |
| (0,1) | 1 | 0 | 114 | 207253 | 3734640 | 1818 |
| (0,1) | 1 | 0 | 115 | 209071 | 3767400 | 1818 |
| (0,1) | 1 | 0 | 116 | 210889 | 3800160 | 1818 |
| (0,1) | 1 | 0 | 117 | 212707 | 3832920 | 1818 |
| (0,1) | 1 | 0 | 118 | 214525 | 3865680 | 1818 |
| (0,1) | 1 | 0 | 119 | 216343 | 3898440 | 1818 |
| (0,1) | 1 | 0 | 120 | 218161 | 3931200 | 1818 |
| (0,1) | 1 | 0 | 121 | 219979 | 3963960 | 1818 |
| (0,1) | 1 | 0 | 122 | 221797 | 3996720 | 1818 |
| (0,1) | 1 | 0 | 123 | 223615 | 4029480 | 1818 |
| (0,1) | 1 | 0 | 124 | 225433 | 4062240 | 1818 |
| (0,1) | 1 | 0 | 125 | 227251 | 4095000 | 1818 |
| (0,1) | 1 | 0 | 126 | 229069 | 4127760 | 1818 |
| (0,1) | 1 | 0 | 127 | 230887 | 4160520 | 1818 |
| (0,1) | 1 | 0 | 128 | 232705 | 4193280 | 1818 |
| (0,1) | 1 | 0 | 129 | 234523 | 4226040 | 1818 |
| (0,1) | 1 | 0 | 130 | 236341 | 4258800 | 1818 |
| (0,1) | 1 | 0 | 131 | 238159 | 4291560 | 1818 |
| (0,1) | 1 | 0 | 132 | 239977 | 4324320 | 1818 |
| (0,1) | 1 | 0 | 133 | 241795 | 4357080 | 1818 |
| (0,1) | 1 | 0 | 134 | 243613 | 4389840 | 1818 |
| (0,1) | 1 | 0 | 135 | 245431 | 4422600 | 1818 |
| (0,1) | 1 | 0 | 136 | 247249 | 4455360 | 1818 |
| (0,1) | 1 | 0 | 137 | 249067 | 4488120 | 1818 |
| (0,1) | 1 | 0 | 138 | 250885 | 4520880 | 1818 |
| (0,1) | 1 | 0 | 139 | 252703 | 4553640 | 1818 |
| (0,1) | 1 | 0 | 140 | 254521 | 4586400 | 1818 |
| (0,1) | 1 | 0 | 141 | 256339 | 4619160 | 1818 |
| (0,1) | 1 | 0 | 142 | 258157 | 4651920 | 1818 |
| (0,1) | 1 | 0 | 143 | 259975 | 4684680 | 1818 |
| (0,1) | 1 | 0 | 144 | 261793 | 4717440 | 1818 |
| (0,1) | 1 | 0 | 145 | 263611 | 4750200 | 1818 |
| (0,1) | 1 | 0 | 146 | 265429 | 4782960 | 1818 |
| (0,1) | 1 | 0 | 147 | 267247 | 4815720 | 1818 |
| (0,1) | 1 | 0 | 148 | 269065 | 4848480 | 1818 |
| (0,1) | 1 | 0 | 149 | 270883 | 4881240 | 1818 |
| (0,1) | 1 | 0 | 150 | 272701 | 4914000 | 1818 |
| (0,1) | 1 | 0 | 151 | 274519 | 4946760 | 1818 |
| (0,1) | 1 | 0 | 152 | 276337 | 4979520 | 1818 |
| (0,1) | 1 | 0 | 153 | 278155 | 5012280 | 1818 |
| (0,1) | 1 | 0 | 154 | 279973 | 5045040 | 1818 |
| (0,1) | 1 | 0 | 155 | 281791 | 5077800 | 1818 |
| (0,1) | 1 | 0 | 156 | 283609 | 5110560 | 1818 |
| (0,1) | 1 | 0 | 157 | 285427 | 5143320 | 1818 |
| (0,1) | 1 | 0 | 158 | 287245 | 5176080 | 1818 |
| (0,1) | 1 | 0 | 159 | 289063 | 5208840 | 1818 |
| (0,1) | 1 | 0 | 160 | 290881 | 5241600 | 1818 |
| (0,2) | 1 | 0 | 0 | 292699 | 5274360 | 2 |
| (162 rows) |
| |
| -- Unique index white box tests |
| DROP TABLE ao_blkdir_test; |
| DROP |
| CREATE TABLE ao_blkdir_test(i int UNIQUE, j int) USING ao_row DISTRIBUTED BY (i); |
| CREATE |
| |
| SELECT gp_inject_fault('appendonly_insert', 'suspend', '', '', 'ao_blkdir_test', 1, 1, 0, dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = 0; |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| 1: BEGIN; |
| BEGIN |
| 1&: INSERT INTO ao_blkdir_test VALUES (2, 2); <waiting ...> |
| |
| -- There should be a placeholder row inserted to cover the rows for each INSERT |
| -- session, before we insert the 1st row in that session, that is only visible |
| -- to SNAPSHOT_DIRTY. |
| SELECT gp_wait_until_triggered_fault('appendonly_insert', 1, dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = 0; |
| gp_wait_until_triggered_fault |
| ------------------------------- |
| Success: |
| (1 row) |
| SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0 rows) |
| SET gp_select_invisible TO ON; |
| SET |
| SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+--------------- |
| (0,1) | 1 | 0 | 0 | 1 | 0 | 1099511627775 |
| (1 row) |
| RESET gp_select_invisible; |
| RESET |
| |
| -- The placeholder row is invisible to other transactions (that don't perform a |
| -- uniqueness check) while the INSERT is in progress. |
| 2: SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0 rows) |
| |
| SELECT gp_inject_fault('appendonly_insert', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = 0; |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| 1<: <... completed> |
| INSERT 1 |
| |
| -- The placeholder row is invisible to the INSERTing transaction. Since the |
| -- INSERT finished, there should be 1 visible blkdir row representing the INSERT. |
| 1: SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,2) | 1 | 0 | 0 | 1 | 0 | 1 |
| (1 row) |
| |
| -- The placeholder row is invisible to other transactions (that don't perform a |
| -- uniqueness check) even after the INSERT finishes. The blkdir row representing |
| -- the INSERT should not be visible as the INSERTing transaction hasn't |
| -- committed yet. |
| 2: SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0 rows) |
| |
| 1: COMMIT; |
| COMMIT |
| |
| -- The placeholder row is invisible to other transactions (that don't perform a |
| -- uniqueness check) even after the INSERTing transaction commits. Since the |
| -- INSERTing transaction has committed, the blkdir row representing the INSERT |
| -- should be visible now. |
| 2: SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,2) | 1 | 0 | 0 | 1 | 0 | 1 |
| (1 row) |
| |
| DROP TABLE ao_blkdir_test; |
| DROP |
| |
| -- Test `tupcount` in pg_aoseg == sum of number of `row_count` across all |
| -- aoblkdir entries for each segno. Test with commits, aborts and deletes. |
| |
| -- Case1: without VACUUM ANALYZE |
| CREATE TABLE ao_blkdir_test_rowcount(i int, j int) USING ao_row DISTRIBUTED BY (j); |
| CREATE |
| 1: BEGIN; |
| BEGIN |
| 2: BEGIN; |
| BEGIN |
| 3: BEGIN; |
| BEGIN |
| 4: BEGIN; |
| BEGIN |
| 1: INSERT INTO ao_blkdir_test_rowcount SELECT i, 2 FROM generate_series(1, 10) i; |
| INSERT 10 |
| 2: INSERT INTO ao_blkdir_test_rowcount SELECT i, 3 FROM generate_series(1, 20) i; |
| INSERT 20 |
| 3: INSERT INTO ao_blkdir_test_rowcount SELECT i, 4 FROM generate_series(1, 30) i; |
| INSERT 30 |
| 3: ABORT; |
| ABORT |
| 3: BEGIN; |
| BEGIN |
| 3: INSERT INTO ao_blkdir_test_rowcount SELECT i, 4 FROM generate_series(1, 40) i; |
| INSERT 40 |
| 4: INSERT INTO ao_blkdir_test_rowcount SELECT i, 7 FROM generate_series(1, 50) i; |
| INSERT 50 |
| 1: COMMIT; |
| COMMIT |
| 2: COMMIT; |
| COMMIT |
| 3: COMMIT; |
| COMMIT |
| 4: COMMIT; |
| COMMIT |
| DELETE FROM ao_blkdir_test_rowcount WHERE j = 7; |
| DELETE 50 |
| |
| CREATE INDEX ao_blkdir_test_rowcount_idx ON ao_blkdir_test_rowcount(i); |
| CREATE |
| |
| SELECT segno, sum(row_count) AS totalrows FROM (SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test_rowcount')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0)s GROUP BY segno, columngroup_no ORDER BY segno; |
| segno | totalrows |
| -------+----------- |
| 1 | 10 |
| 2 | 20 |
| 3 | 40 |
| 4 | 50 |
| (4 rows) |
| SELECT segno, sum(tupcount) AS totalrows FROM gp_toolkit.__gp_aoseg('ao_blkdir_test_rowcount') WHERE segment_id = 0 GROUP BY segno; |
| segno | totalrows |
| -------+----------- |
| 1 | 10 |
| 2 | 20 |
| 3 | 40 |
| 4 | 50 |
| (4 rows) |
| |
| -- Case2: with VACUUM ANALYZE |
| DROP TABLE ao_blkdir_test_rowcount; |
| DROP |
| CREATE TABLE ao_blkdir_test_rowcount(i int, j int) USING ao_row DISTRIBUTED BY (j); |
| CREATE |
| CREATE INDEX ao_blkdir_test_rowcount_idx ON ao_blkdir_test_rowcount(i); |
| CREATE |
| 1: BEGIN; |
| BEGIN |
| 2: BEGIN; |
| BEGIN |
| 3: BEGIN; |
| BEGIN |
| 4: BEGIN; |
| BEGIN |
| 1: INSERT INTO ao_blkdir_test_rowcount SELECT i, 2 FROM generate_series(1, 10) i; |
| INSERT 10 |
| 1: INSERT INTO ao_blkdir_test_rowcount SELECT i, 2 FROM ao_blkdir_test_rowcount; |
| INSERT 10 |
| 1: INSERT INTO ao_blkdir_test_rowcount SELECT i, 2 FROM ao_blkdir_test_rowcount; |
| INSERT 20 |
| 2: INSERT INTO ao_blkdir_test_rowcount SELECT i, 3 FROM generate_series(1, 20) i; |
| INSERT 20 |
| 2: INSERT INTO ao_blkdir_test_rowcount SELECT i, 3 FROM ao_blkdir_test_rowcount; |
| INSERT 20 |
| 2: INSERT INTO ao_blkdir_test_rowcount SELECT i, 3 FROM ao_blkdir_test_rowcount; |
| INSERT 40 |
| 3: INSERT INTO ao_blkdir_test_rowcount SELECT i, 4 FROM generate_series(1, 30) i; |
| INSERT 30 |
| 3: INSERT INTO ao_blkdir_test_rowcount SELECT i, 4 FROM ao_blkdir_test_rowcount; |
| INSERT 30 |
| 3: INSERT INTO ao_blkdir_test_rowcount SELECT i, 4 FROM ao_blkdir_test_rowcount; |
| INSERT 60 |
| 4: INSERT INTO ao_blkdir_test_rowcount SELECT i, 7 FROM generate_series(1, 50) i; |
| INSERT 50 |
| 4: INSERT INTO ao_blkdir_test_rowcount SELECT i, 7 FROM ao_blkdir_test_rowcount; |
| INSERT 50 |
| 4: INSERT INTO ao_blkdir_test_rowcount SELECT i, 7 FROM ao_blkdir_test_rowcount; |
| INSERT 100 |
| 1: COMMIT; |
| COMMIT |
| 2: COMMIT; |
| COMMIT |
| 3: ABORT; |
| ABORT |
| 4: COMMIT; |
| COMMIT |
| |
| DELETE FROM ao_blkdir_test_rowcount WHERE j = 7; |
| DELETE 200 |
| VACUUM ANALYZE ao_blkdir_test_rowcount; |
| VACUUM |
| |
| SELECT segno, sum(row_count) AS totalrows FROM (SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test_rowcount')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0)s GROUP BY segno, columngroup_no ORDER BY segno; |
| segno | totalrows |
| -------+----------- |
| 1 | 40 |
| 2 | 80 |
| (2 rows) |
| SELECT segno, sum(tupcount) AS totalrows FROM gp_toolkit.__gp_aoseg('ao_blkdir_test_rowcount') WHERE segment_id = 0 GROUP BY segno; |
| segno | totalrows |
| -------+----------- |
| 1 | 40 |
| 2 | 80 |
| 3 | 0 |
| 4 | 0 |
| (4 rows) |
| |
| UPDATE ao_blkdir_test_rowcount SET i = i + 1; |
| UPDATE 120 |
| VACUUM ANALYZE ao_blkdir_test_rowcount; |
| VACUUM |
| |
| SELECT segno, sum(row_count) AS totalrows FROM (SELECT (gp_toolkit.__gp_aoblkdir('ao_blkdir_test_rowcount')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0)s GROUP BY segno, columngroup_no ORDER BY segno; |
| segno | totalrows |
| -------+----------- |
| 3 | 120 |
| (1 row) |
| SELECT segno, sum(tupcount) AS totalrows FROM gp_toolkit.__gp_aoseg('ao_blkdir_test_rowcount') WHERE segment_id = 0 GROUP BY segno; |
| segno | totalrows |
| -------+----------- |
| 1 | 0 |
| 2 | 0 |
| 3 | 120 |
| 4 | 0 |
| (4 rows) |
| |
| DROP TABLE ao_blkdir_test_rowcount; |
| DROP |
| |
| -------------------------------------------------------------------------------- |
| -- AOCO tables |
| -------------------------------------------------------------------------------- |
| |
| CREATE TABLE aoco_blkdir_test(i int, j int) USING ao_column DISTRIBUTED BY (j); |
| CREATE |
| CREATE INDEX aoco_blkdir_test_idx ON aoco_blkdir_test(i); |
| CREATE |
| |
| 1: INSERT INTO aoco_blkdir_test SELECT i, 2 FROM generate_series(1, 10) i; |
| INSERT 10 |
| -- There should be 2 block directory rows with a single entry covering 10 rows, |
| -- (1 for each column). |
| SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,1) | 1 | 0 | 0 | 1 | 0 | 10 |
| (0,2) | 1 | 1 | 0 | 1 | 0 | 10 |
| (2 rows) |
| |
| 1: INSERT INTO aoco_blkdir_test SELECT i, 2 FROM generate_series(11, 30) i; |
| INSERT 20 |
| -- There should be 2 block directory rows, carrying 2 entries each. The rows |
| -- from the previous INSERT should not be visible. The entries from the first |
| -- INSERT should remain unchanged. |
| SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,3) | 1 | 0 | 0 | 1 | 0 | 10 |
| (0,3) | 1 | 0 | 1 | 101 | 80 | 20 |
| (0,4) | 1 | 1 | 0 | 1 | 0 | 10 |
| (0,4) | 1 | 1 | 1 | 101 | 80 | 20 |
| (4 rows) |
| |
| 1: BEGIN; |
| BEGIN |
| 1: INSERT INTO aoco_blkdir_test SELECT i, 2 FROM generate_series(31, 60) i; |
| INSERT 30 |
| 2: BEGIN; |
| BEGIN |
| 2: INSERT INTO aoco_blkdir_test SELECT i, 2 FROM generate_series(71, 110) i; |
| INSERT 40 |
| 1: COMMIT; |
| COMMIT |
| 2: COMMIT; |
| COMMIT |
| -- The second INSERT of 40 rows above would have landed in segfile 1 (unlike |
| -- segfile 0, like the first INSERT of 30 rows above). This should be reflected |
| -- in the block directory entries for these rows. |
| SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,5) | 1 | 0 | 0 | 1 | 0 | 10 |
| (0,5) | 1 | 0 | 1 | 101 | 80 | 20 |
| (0,5) | 1 | 0 | 2 | 201 | 200 | 30 |
| (0,6) | 1 | 1 | 0 | 1 | 0 | 10 |
| (0,6) | 1 | 1 | 1 | 101 | 80 | 20 |
| (0,6) | 1 | 1 | 2 | 201 | 200 | 30 |
| (0,7) | 2 | 0 | 0 | 1 | 0 | 40 |
| (0,8) | 2 | 1 | 0 | 1 | 0 | 40 |
| (8 rows) |
| |
| TRUNCATE aoco_blkdir_test; |
| TRUNCATE |
| -- Insert enough rows to overflow the first block directory minipage by 2. |
| set gp_appendonly_insert_files = 0; |
| SET |
| INSERT INTO aoco_blkdir_test SELECT i, 2 FROM generate_series(1, 1317143) i; |
| INSERT 1317143 |
| reset gp_appendonly_insert_files; |
| RESET |
| -- There should be 2 block directory rows, 2 for each column, one with 161 |
| -- entries covering 1317141 rows and the other with 1 entry covering the 2 |
| -- overflow rows. |
| SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,1) | 1 | 0 | 0 | 1 | 0 | 8181 |
| (0,1) | 1 | 0 | 1 | 8182 | 32768 | 8181 |
| (0,1) | 1 | 0 | 2 | 16363 | 65536 | 8181 |
| (0,1) | 1 | 0 | 3 | 24544 | 98304 | 8181 |
| (0,1) | 1 | 0 | 4 | 32725 | 131072 | 8181 |
| (0,1) | 1 | 0 | 5 | 40906 | 163840 | 8181 |
| (0,1) | 1 | 0 | 6 | 49087 | 196608 | 8181 |
| (0,1) | 1 | 0 | 7 | 57268 | 229376 | 8181 |
| (0,1) | 1 | 0 | 8 | 65449 | 262144 | 8181 |
| (0,1) | 1 | 0 | 9 | 73630 | 294912 | 8181 |
| (0,1) | 1 | 0 | 10 | 81811 | 327680 | 8181 |
| (0,1) | 1 | 0 | 11 | 89992 | 360448 | 8181 |
| (0,1) | 1 | 0 | 12 | 98173 | 393216 | 8181 |
| (0,1) | 1 | 0 | 13 | 106354 | 425984 | 8181 |
| (0,1) | 1 | 0 | 14 | 114535 | 458752 | 8181 |
| (0,1) | 1 | 0 | 15 | 122716 | 491520 | 8181 |
| (0,1) | 1 | 0 | 16 | 130897 | 524288 | 8181 |
| (0,1) | 1 | 0 | 17 | 139078 | 557056 | 8181 |
| (0,1) | 1 | 0 | 18 | 147259 | 589824 | 8181 |
| (0,1) | 1 | 0 | 19 | 155440 | 622592 | 8181 |
| (0,1) | 1 | 0 | 20 | 163621 | 655360 | 8181 |
| (0,1) | 1 | 0 | 21 | 171802 | 688128 | 8181 |
| (0,1) | 1 | 0 | 22 | 179983 | 720896 | 8181 |
| (0,1) | 1 | 0 | 23 | 188164 | 753664 | 8181 |
| (0,1) | 1 | 0 | 24 | 196345 | 786432 | 8181 |
| (0,1) | 1 | 0 | 25 | 204526 | 819200 | 8181 |
| (0,1) | 1 | 0 | 26 | 212707 | 851968 | 8181 |
| (0,1) | 1 | 0 | 27 | 220888 | 884736 | 8181 |
| (0,1) | 1 | 0 | 28 | 229069 | 917504 | 8181 |
| (0,1) | 1 | 0 | 29 | 237250 | 950272 | 8181 |
| (0,1) | 1 | 0 | 30 | 245431 | 983040 | 8181 |
| (0,1) | 1 | 0 | 31 | 253612 | 1015808 | 8181 |
| (0,1) | 1 | 0 | 32 | 261793 | 1048576 | 8181 |
| (0,1) | 1 | 0 | 33 | 269974 | 1081344 | 8181 |
| (0,1) | 1 | 0 | 34 | 278155 | 1114112 | 8181 |
| (0,1) | 1 | 0 | 35 | 286336 | 1146880 | 8181 |
| (0,1) | 1 | 0 | 36 | 294517 | 1179648 | 8181 |
| (0,1) | 1 | 0 | 37 | 302698 | 1212416 | 8181 |
| (0,1) | 1 | 0 | 38 | 310879 | 1245184 | 8181 |
| (0,1) | 1 | 0 | 39 | 319060 | 1277952 | 8181 |
| (0,1) | 1 | 0 | 40 | 327241 | 1310720 | 8181 |
| (0,1) | 1 | 0 | 41 | 335422 | 1343488 | 8181 |
| (0,1) | 1 | 0 | 42 | 343603 | 1376256 | 8181 |
| (0,1) | 1 | 0 | 43 | 351784 | 1409024 | 8181 |
| (0,1) | 1 | 0 | 44 | 359965 | 1441792 | 8181 |
| (0,1) | 1 | 0 | 45 | 368146 | 1474560 | 8181 |
| (0,1) | 1 | 0 | 46 | 376327 | 1507328 | 8181 |
| (0,1) | 1 | 0 | 47 | 384508 | 1540096 | 8181 |
| (0,1) | 1 | 0 | 48 | 392689 | 1572864 | 8181 |
| (0,1) | 1 | 0 | 49 | 400870 | 1605632 | 8181 |
| (0,1) | 1 | 0 | 50 | 409051 | 1638400 | 8181 |
| (0,1) | 1 | 0 | 51 | 417232 | 1671168 | 8181 |
| (0,1) | 1 | 0 | 52 | 425413 | 1703936 | 8181 |
| (0,1) | 1 | 0 | 53 | 433594 | 1736704 | 8181 |
| (0,1) | 1 | 0 | 54 | 441775 | 1769472 | 8181 |
| (0,1) | 1 | 0 | 55 | 449956 | 1802240 | 8181 |
| (0,1) | 1 | 0 | 56 | 458137 | 1835008 | 8181 |
| (0,1) | 1 | 0 | 57 | 466318 | 1867776 | 8181 |
| (0,1) | 1 | 0 | 58 | 474499 | 1900544 | 8181 |
| (0,1) | 1 | 0 | 59 | 482680 | 1933312 | 8181 |
| (0,1) | 1 | 0 | 60 | 490861 | 1966080 | 8181 |
| (0,1) | 1 | 0 | 61 | 499042 | 1998848 | 8181 |
| (0,1) | 1 | 0 | 62 | 507223 | 2031616 | 8181 |
| (0,1) | 1 | 0 | 63 | 515404 | 2064384 | 8181 |
| (0,1) | 1 | 0 | 64 | 523585 | 2097152 | 8181 |
| (0,1) | 1 | 0 | 65 | 531766 | 2129920 | 8181 |
| (0,1) | 1 | 0 | 66 | 539947 | 2162688 | 8181 |
| (0,1) | 1 | 0 | 67 | 548128 | 2195456 | 8181 |
| (0,1) | 1 | 0 | 68 | 556309 | 2228224 | 8181 |
| (0,1) | 1 | 0 | 69 | 564490 | 2260992 | 8181 |
| (0,1) | 1 | 0 | 70 | 572671 | 2293760 | 8181 |
| (0,1) | 1 | 0 | 71 | 580852 | 2326528 | 8181 |
| (0,1) | 1 | 0 | 72 | 589033 | 2359296 | 8181 |
| (0,1) | 1 | 0 | 73 | 597214 | 2392064 | 8181 |
| (0,1) | 1 | 0 | 74 | 605395 | 2424832 | 8181 |
| (0,1) | 1 | 0 | 75 | 613576 | 2457600 | 8181 |
| (0,1) | 1 | 0 | 76 | 621757 | 2490368 | 8181 |
| (0,1) | 1 | 0 | 77 | 629938 | 2523136 | 8181 |
| (0,1) | 1 | 0 | 78 | 638119 | 2555904 | 8181 |
| (0,1) | 1 | 0 | 79 | 646300 | 2588672 | 8181 |
| (0,1) | 1 | 0 | 80 | 654481 | 2621440 | 8181 |
| (0,1) | 1 | 0 | 81 | 662662 | 2654208 | 8181 |
| (0,1) | 1 | 0 | 82 | 670843 | 2686976 | 8181 |
| (0,1) | 1 | 0 | 83 | 679024 | 2719744 | 8181 |
| (0,1) | 1 | 0 | 84 | 687205 | 2752512 | 8181 |
| (0,1) | 1 | 0 | 85 | 695386 | 2785280 | 8181 |
| (0,1) | 1 | 0 | 86 | 703567 | 2818048 | 8181 |
| (0,1) | 1 | 0 | 87 | 711748 | 2850816 | 8181 |
| (0,1) | 1 | 0 | 88 | 719929 | 2883584 | 8181 |
| (0,1) | 1 | 0 | 89 | 728110 | 2916352 | 8181 |
| (0,1) | 1 | 0 | 90 | 736291 | 2949120 | 8181 |
| (0,1) | 1 | 0 | 91 | 744472 | 2981888 | 8181 |
| (0,1) | 1 | 0 | 92 | 752653 | 3014656 | 8181 |
| (0,1) | 1 | 0 | 93 | 760834 | 3047424 | 8181 |
| (0,1) | 1 | 0 | 94 | 769015 | 3080192 | 8181 |
| (0,1) | 1 | 0 | 95 | 777196 | 3112960 | 8181 |
| (0,1) | 1 | 0 | 96 | 785377 | 3145728 | 8181 |
| (0,1) | 1 | 0 | 97 | 793558 | 3178496 | 8181 |
| (0,1) | 1 | 0 | 98 | 801739 | 3211264 | 8181 |
| (0,1) | 1 | 0 | 99 | 809920 | 3244032 | 8181 |
| (0,1) | 1 | 0 | 100 | 818101 | 3276800 | 8181 |
| (0,1) | 1 | 0 | 101 | 826282 | 3309568 | 8181 |
| (0,1) | 1 | 0 | 102 | 834463 | 3342336 | 8181 |
| (0,1) | 1 | 0 | 103 | 842644 | 3375104 | 8181 |
| (0,1) | 1 | 0 | 104 | 850825 | 3407872 | 8181 |
| (0,1) | 1 | 0 | 105 | 859006 | 3440640 | 8181 |
| (0,1) | 1 | 0 | 106 | 867187 | 3473408 | 8181 |
| (0,1) | 1 | 0 | 107 | 875368 | 3506176 | 8181 |
| (0,1) | 1 | 0 | 108 | 883549 | 3538944 | 8181 |
| (0,1) | 1 | 0 | 109 | 891730 | 3571712 | 8181 |
| (0,1) | 1 | 0 | 110 | 899911 | 3604480 | 8181 |
| (0,1) | 1 | 0 | 111 | 908092 | 3637248 | 8181 |
| (0,1) | 1 | 0 | 112 | 916273 | 3670016 | 8181 |
| (0,1) | 1 | 0 | 113 | 924454 | 3702784 | 8181 |
| (0,1) | 1 | 0 | 114 | 932635 | 3735552 | 8181 |
| (0,1) | 1 | 0 | 115 | 940816 | 3768320 | 8181 |
| (0,1) | 1 | 0 | 116 | 948997 | 3801088 | 8181 |
| (0,1) | 1 | 0 | 117 | 957178 | 3833856 | 8181 |
| (0,1) | 1 | 0 | 118 | 965359 | 3866624 | 8181 |
| (0,1) | 1 | 0 | 119 | 973540 | 3899392 | 8181 |
| (0,1) | 1 | 0 | 120 | 981721 | 3932160 | 8181 |
| (0,1) | 1 | 0 | 121 | 989902 | 3964928 | 8181 |
| (0,1) | 1 | 0 | 122 | 998083 | 3997696 | 8181 |
| (0,1) | 1 | 0 | 123 | 1006264 | 4030464 | 8181 |
| (0,1) | 1 | 0 | 124 | 1014445 | 4063232 | 8181 |
| (0,1) | 1 | 0 | 125 | 1022626 | 4096000 | 8181 |
| (0,1) | 1 | 0 | 126 | 1030807 | 4128768 | 8181 |
| (0,1) | 1 | 0 | 127 | 1038988 | 4161536 | 8181 |
| (0,1) | 1 | 0 | 128 | 1047169 | 4194304 | 8181 |
| (0,1) | 1 | 0 | 129 | 1055350 | 4227072 | 8181 |
| (0,1) | 1 | 0 | 130 | 1063531 | 4259840 | 8181 |
| (0,1) | 1 | 0 | 131 | 1071712 | 4292608 | 8181 |
| (0,1) | 1 | 0 | 132 | 1079893 | 4325376 | 8181 |
| (0,1) | 1 | 0 | 133 | 1088074 | 4358144 | 8181 |
| (0,1) | 1 | 0 | 134 | 1096255 | 4390912 | 8181 |
| (0,1) | 1 | 0 | 135 | 1104436 | 4423680 | 8181 |
| (0,1) | 1 | 0 | 136 | 1112617 | 4456448 | 8181 |
| (0,1) | 1 | 0 | 137 | 1120798 | 4489216 | 8181 |
| (0,1) | 1 | 0 | 138 | 1128979 | 4521984 | 8181 |
| (0,1) | 1 | 0 | 139 | 1137160 | 4554752 | 8181 |
| (0,1) | 1 | 0 | 140 | 1145341 | 4587520 | 8181 |
| (0,1) | 1 | 0 | 141 | 1153522 | 4620288 | 8181 |
| (0,1) | 1 | 0 | 142 | 1161703 | 4653056 | 8181 |
| (0,1) | 1 | 0 | 143 | 1169884 | 4685824 | 8181 |
| (0,1) | 1 | 0 | 144 | 1178065 | 4718592 | 8181 |
| (0,1) | 1 | 0 | 145 | 1186246 | 4751360 | 8181 |
| (0,1) | 1 | 0 | 146 | 1194427 | 4784128 | 8181 |
| (0,1) | 1 | 0 | 147 | 1202608 | 4816896 | 8181 |
| (0,1) | 1 | 0 | 148 | 1210789 | 4849664 | 8181 |
| (0,1) | 1 | 0 | 149 | 1218970 | 4882432 | 8181 |
| (0,1) | 1 | 0 | 150 | 1227151 | 4915200 | 8181 |
| (0,1) | 1 | 0 | 151 | 1235332 | 4947968 | 8181 |
| (0,1) | 1 | 0 | 152 | 1243513 | 4980736 | 8181 |
| (0,1) | 1 | 0 | 153 | 1251694 | 5013504 | 8181 |
| (0,1) | 1 | 0 | 154 | 1259875 | 5046272 | 8181 |
| (0,1) | 1 | 0 | 155 | 1268056 | 5079040 | 8181 |
| (0,1) | 1 | 0 | 156 | 1276237 | 5111808 | 8181 |
| (0,1) | 1 | 0 | 157 | 1284418 | 5144576 | 8181 |
| (0,1) | 1 | 0 | 158 | 1292599 | 5177344 | 8181 |
| (0,1) | 1 | 0 | 159 | 1300780 | 5210112 | 8181 |
| (0,1) | 1 | 0 | 160 | 1308961 | 5242880 | 8181 |
| (0,2) | 1 | 1 | 0 | 1 | 0 | 8181 |
| (0,2) | 1 | 1 | 1 | 8182 | 32768 | 8181 |
| (0,2) | 1 | 1 | 2 | 16363 | 65536 | 8181 |
| (0,2) | 1 | 1 | 3 | 24544 | 98304 | 8181 |
| (0,2) | 1 | 1 | 4 | 32725 | 131072 | 8181 |
| (0,2) | 1 | 1 | 5 | 40906 | 163840 | 8181 |
| (0,2) | 1 | 1 | 6 | 49087 | 196608 | 8181 |
| (0,2) | 1 | 1 | 7 | 57268 | 229376 | 8181 |
| (0,2) | 1 | 1 | 8 | 65449 | 262144 | 8181 |
| (0,2) | 1 | 1 | 9 | 73630 | 294912 | 8181 |
| (0,2) | 1 | 1 | 10 | 81811 | 327680 | 8181 |
| (0,2) | 1 | 1 | 11 | 89992 | 360448 | 8181 |
| (0,2) | 1 | 1 | 12 | 98173 | 393216 | 8181 |
| (0,2) | 1 | 1 | 13 | 106354 | 425984 | 8181 |
| (0,2) | 1 | 1 | 14 | 114535 | 458752 | 8181 |
| (0,2) | 1 | 1 | 15 | 122716 | 491520 | 8181 |
| (0,2) | 1 | 1 | 16 | 130897 | 524288 | 8181 |
| (0,2) | 1 | 1 | 17 | 139078 | 557056 | 8181 |
| (0,2) | 1 | 1 | 18 | 147259 | 589824 | 8181 |
| (0,2) | 1 | 1 | 19 | 155440 | 622592 | 8181 |
| (0,2) | 1 | 1 | 20 | 163621 | 655360 | 8181 |
| (0,2) | 1 | 1 | 21 | 171802 | 688128 | 8181 |
| (0,2) | 1 | 1 | 22 | 179983 | 720896 | 8181 |
| (0,2) | 1 | 1 | 23 | 188164 | 753664 | 8181 |
| (0,2) | 1 | 1 | 24 | 196345 | 786432 | 8181 |
| (0,2) | 1 | 1 | 25 | 204526 | 819200 | 8181 |
| (0,2) | 1 | 1 | 26 | 212707 | 851968 | 8181 |
| (0,2) | 1 | 1 | 27 | 220888 | 884736 | 8181 |
| (0,2) | 1 | 1 | 28 | 229069 | 917504 | 8181 |
| (0,2) | 1 | 1 | 29 | 237250 | 950272 | 8181 |
| (0,2) | 1 | 1 | 30 | 245431 | 983040 | 8181 |
| (0,2) | 1 | 1 | 31 | 253612 | 1015808 | 8181 |
| (0,2) | 1 | 1 | 32 | 261793 | 1048576 | 8181 |
| (0,2) | 1 | 1 | 33 | 269974 | 1081344 | 8181 |
| (0,2) | 1 | 1 | 34 | 278155 | 1114112 | 8181 |
| (0,2) | 1 | 1 | 35 | 286336 | 1146880 | 8181 |
| (0,2) | 1 | 1 | 36 | 294517 | 1179648 | 8181 |
| (0,2) | 1 | 1 | 37 | 302698 | 1212416 | 8181 |
| (0,2) | 1 | 1 | 38 | 310879 | 1245184 | 8181 |
| (0,2) | 1 | 1 | 39 | 319060 | 1277952 | 8181 |
| (0,2) | 1 | 1 | 40 | 327241 | 1310720 | 8181 |
| (0,2) | 1 | 1 | 41 | 335422 | 1343488 | 8181 |
| (0,2) | 1 | 1 | 42 | 343603 | 1376256 | 8181 |
| (0,2) | 1 | 1 | 43 | 351784 | 1409024 | 8181 |
| (0,2) | 1 | 1 | 44 | 359965 | 1441792 | 8181 |
| (0,2) | 1 | 1 | 45 | 368146 | 1474560 | 8181 |
| (0,2) | 1 | 1 | 46 | 376327 | 1507328 | 8181 |
| (0,2) | 1 | 1 | 47 | 384508 | 1540096 | 8181 |
| (0,2) | 1 | 1 | 48 | 392689 | 1572864 | 8181 |
| (0,2) | 1 | 1 | 49 | 400870 | 1605632 | 8181 |
| (0,2) | 1 | 1 | 50 | 409051 | 1638400 | 8181 |
| (0,2) | 1 | 1 | 51 | 417232 | 1671168 | 8181 |
| (0,2) | 1 | 1 | 52 | 425413 | 1703936 | 8181 |
| (0,2) | 1 | 1 | 53 | 433594 | 1736704 | 8181 |
| (0,2) | 1 | 1 | 54 | 441775 | 1769472 | 8181 |
| (0,2) | 1 | 1 | 55 | 449956 | 1802240 | 8181 |
| (0,2) | 1 | 1 | 56 | 458137 | 1835008 | 8181 |
| (0,2) | 1 | 1 | 57 | 466318 | 1867776 | 8181 |
| (0,2) | 1 | 1 | 58 | 474499 | 1900544 | 8181 |
| (0,2) | 1 | 1 | 59 | 482680 | 1933312 | 8181 |
| (0,2) | 1 | 1 | 60 | 490861 | 1966080 | 8181 |
| (0,2) | 1 | 1 | 61 | 499042 | 1998848 | 8181 |
| (0,2) | 1 | 1 | 62 | 507223 | 2031616 | 8181 |
| (0,2) | 1 | 1 | 63 | 515404 | 2064384 | 8181 |
| (0,2) | 1 | 1 | 64 | 523585 | 2097152 | 8181 |
| (0,2) | 1 | 1 | 65 | 531766 | 2129920 | 8181 |
| (0,2) | 1 | 1 | 66 | 539947 | 2162688 | 8181 |
| (0,2) | 1 | 1 | 67 | 548128 | 2195456 | 8181 |
| (0,2) | 1 | 1 | 68 | 556309 | 2228224 | 8181 |
| (0,2) | 1 | 1 | 69 | 564490 | 2260992 | 8181 |
| (0,2) | 1 | 1 | 70 | 572671 | 2293760 | 8181 |
| (0,2) | 1 | 1 | 71 | 580852 | 2326528 | 8181 |
| (0,2) | 1 | 1 | 72 | 589033 | 2359296 | 8181 |
| (0,2) | 1 | 1 | 73 | 597214 | 2392064 | 8181 |
| (0,2) | 1 | 1 | 74 | 605395 | 2424832 | 8181 |
| (0,2) | 1 | 1 | 75 | 613576 | 2457600 | 8181 |
| (0,2) | 1 | 1 | 76 | 621757 | 2490368 | 8181 |
| (0,2) | 1 | 1 | 77 | 629938 | 2523136 | 8181 |
| (0,2) | 1 | 1 | 78 | 638119 | 2555904 | 8181 |
| (0,2) | 1 | 1 | 79 | 646300 | 2588672 | 8181 |
| (0,2) | 1 | 1 | 80 | 654481 | 2621440 | 8181 |
| (0,2) | 1 | 1 | 81 | 662662 | 2654208 | 8181 |
| (0,2) | 1 | 1 | 82 | 670843 | 2686976 | 8181 |
| (0,2) | 1 | 1 | 83 | 679024 | 2719744 | 8181 |
| (0,2) | 1 | 1 | 84 | 687205 | 2752512 | 8181 |
| (0,2) | 1 | 1 | 85 | 695386 | 2785280 | 8181 |
| (0,2) | 1 | 1 | 86 | 703567 | 2818048 | 8181 |
| (0,2) | 1 | 1 | 87 | 711748 | 2850816 | 8181 |
| (0,2) | 1 | 1 | 88 | 719929 | 2883584 | 8181 |
| (0,2) | 1 | 1 | 89 | 728110 | 2916352 | 8181 |
| (0,2) | 1 | 1 | 90 | 736291 | 2949120 | 8181 |
| (0,2) | 1 | 1 | 91 | 744472 | 2981888 | 8181 |
| (0,2) | 1 | 1 | 92 | 752653 | 3014656 | 8181 |
| (0,2) | 1 | 1 | 93 | 760834 | 3047424 | 8181 |
| (0,2) | 1 | 1 | 94 | 769015 | 3080192 | 8181 |
| (0,2) | 1 | 1 | 95 | 777196 | 3112960 | 8181 |
| (0,2) | 1 | 1 | 96 | 785377 | 3145728 | 8181 |
| (0,2) | 1 | 1 | 97 | 793558 | 3178496 | 8181 |
| (0,2) | 1 | 1 | 98 | 801739 | 3211264 | 8181 |
| (0,2) | 1 | 1 | 99 | 809920 | 3244032 | 8181 |
| (0,2) | 1 | 1 | 100 | 818101 | 3276800 | 8181 |
| (0,2) | 1 | 1 | 101 | 826282 | 3309568 | 8181 |
| (0,2) | 1 | 1 | 102 | 834463 | 3342336 | 8181 |
| (0,2) | 1 | 1 | 103 | 842644 | 3375104 | 8181 |
| (0,2) | 1 | 1 | 104 | 850825 | 3407872 | 8181 |
| (0,2) | 1 | 1 | 105 | 859006 | 3440640 | 8181 |
| (0,2) | 1 | 1 | 106 | 867187 | 3473408 | 8181 |
| (0,2) | 1 | 1 | 107 | 875368 | 3506176 | 8181 |
| (0,2) | 1 | 1 | 108 | 883549 | 3538944 | 8181 |
| (0,2) | 1 | 1 | 109 | 891730 | 3571712 | 8181 |
| (0,2) | 1 | 1 | 110 | 899911 | 3604480 | 8181 |
| (0,2) | 1 | 1 | 111 | 908092 | 3637248 | 8181 |
| (0,2) | 1 | 1 | 112 | 916273 | 3670016 | 8181 |
| (0,2) | 1 | 1 | 113 | 924454 | 3702784 | 8181 |
| (0,2) | 1 | 1 | 114 | 932635 | 3735552 | 8181 |
| (0,2) | 1 | 1 | 115 | 940816 | 3768320 | 8181 |
| (0,2) | 1 | 1 | 116 | 948997 | 3801088 | 8181 |
| (0,2) | 1 | 1 | 117 | 957178 | 3833856 | 8181 |
| (0,2) | 1 | 1 | 118 | 965359 | 3866624 | 8181 |
| (0,2) | 1 | 1 | 119 | 973540 | 3899392 | 8181 |
| (0,2) | 1 | 1 | 120 | 981721 | 3932160 | 8181 |
| (0,2) | 1 | 1 | 121 | 989902 | 3964928 | 8181 |
| (0,2) | 1 | 1 | 122 | 998083 | 3997696 | 8181 |
| (0,2) | 1 | 1 | 123 | 1006264 | 4030464 | 8181 |
| (0,2) | 1 | 1 | 124 | 1014445 | 4063232 | 8181 |
| (0,2) | 1 | 1 | 125 | 1022626 | 4096000 | 8181 |
| (0,2) | 1 | 1 | 126 | 1030807 | 4128768 | 8181 |
| (0,2) | 1 | 1 | 127 | 1038988 | 4161536 | 8181 |
| (0,2) | 1 | 1 | 128 | 1047169 | 4194304 | 8181 |
| (0,2) | 1 | 1 | 129 | 1055350 | 4227072 | 8181 |
| (0,2) | 1 | 1 | 130 | 1063531 | 4259840 | 8181 |
| (0,2) | 1 | 1 | 131 | 1071712 | 4292608 | 8181 |
| (0,2) | 1 | 1 | 132 | 1079893 | 4325376 | 8181 |
| (0,2) | 1 | 1 | 133 | 1088074 | 4358144 | 8181 |
| (0,2) | 1 | 1 | 134 | 1096255 | 4390912 | 8181 |
| (0,2) | 1 | 1 | 135 | 1104436 | 4423680 | 8181 |
| (0,2) | 1 | 1 | 136 | 1112617 | 4456448 | 8181 |
| (0,2) | 1 | 1 | 137 | 1120798 | 4489216 | 8181 |
| (0,2) | 1 | 1 | 138 | 1128979 | 4521984 | 8181 |
| (0,2) | 1 | 1 | 139 | 1137160 | 4554752 | 8181 |
| (0,2) | 1 | 1 | 140 | 1145341 | 4587520 | 8181 |
| (0,2) | 1 | 1 | 141 | 1153522 | 4620288 | 8181 |
| (0,2) | 1 | 1 | 142 | 1161703 | 4653056 | 8181 |
| (0,2) | 1 | 1 | 143 | 1169884 | 4685824 | 8181 |
| (0,2) | 1 | 1 | 144 | 1178065 | 4718592 | 8181 |
| (0,2) | 1 | 1 | 145 | 1186246 | 4751360 | 8181 |
| (0,2) | 1 | 1 | 146 | 1194427 | 4784128 | 8181 |
| (0,2) | 1 | 1 | 147 | 1202608 | 4816896 | 8181 |
| (0,2) | 1 | 1 | 148 | 1210789 | 4849664 | 8181 |
| (0,2) | 1 | 1 | 149 | 1218970 | 4882432 | 8181 |
| (0,2) | 1 | 1 | 150 | 1227151 | 4915200 | 8181 |
| (0,2) | 1 | 1 | 151 | 1235332 | 4947968 | 8181 |
| (0,2) | 1 | 1 | 152 | 1243513 | 4980736 | 8181 |
| (0,2) | 1 | 1 | 153 | 1251694 | 5013504 | 8181 |
| (0,2) | 1 | 1 | 154 | 1259875 | 5046272 | 8181 |
| (0,2) | 1 | 1 | 155 | 1268056 | 5079040 | 8181 |
| (0,2) | 1 | 1 | 156 | 1276237 | 5111808 | 8181 |
| (0,2) | 1 | 1 | 157 | 1284418 | 5144576 | 8181 |
| (0,2) | 1 | 1 | 158 | 1292599 | 5177344 | 8181 |
| (0,2) | 1 | 1 | 159 | 1300780 | 5210112 | 8181 |
| (0,2) | 1 | 1 | 160 | 1308961 | 5242880 | 8181 |
| (0,3) | 1 | 0 | 0 | 1317142 | 5275648 | 2 |
| (0,4) | 1 | 1 | 0 | 1317142 | 5275648 | 2 |
| (324 rows) |
| |
| -- Unique index white box tests |
| DROP TABLE aoco_blkdir_test; |
| DROP |
| CREATE TABLE aoco_blkdir_test(h int, i int UNIQUE, j int) USING ao_column DISTRIBUTED BY (i); |
| CREATE |
| |
| SELECT gp_inject_fault('appendonly_insert', 'suspend', '', '', 'aoco_blkdir_test', 1, 1, 0, dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = 0; |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| 1: BEGIN; |
| BEGIN |
| 1&: INSERT INTO aoco_blkdir_test VALUES (2, 2, 2); <waiting ...> |
| |
| -- There should be a placeholder row inserted to cover the rows for each INSERT |
| -- session (for the first non-dropped column), before we insert the 1st row in |
| -- that session, that is only visible to SNAPSHOT_DIRTY. |
| SELECT gp_wait_until_triggered_fault('appendonly_insert', 1, dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = 0; |
| gp_wait_until_triggered_fault |
| ------------------------------- |
| Success: |
| (1 row) |
| SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0 rows) |
| SET gp_select_invisible TO ON; |
| SET |
| SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+--------------- |
| (0,1) | 1 | 0 | 0 | 1 | 0 | 1099511627775 |
| (1 row) |
| RESET gp_select_invisible; |
| RESET |
| |
| -- The placeholder row is invisible to other transactions (that don't perform a |
| -- uniqueness check) while the INSERT is in progress. |
| 2: SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0 rows) |
| |
| -- Before the INSERT commits, if we try to drop column 'h', for which the |
| -- placeholder row was created, the session will block (locking). So it is |
| -- perfectly safe to use 1 placeholder row (and not have 1 placeholder/column) |
| 3&: ALTER TABLE aoco_blkdir_test DROP COLUMN h; <waiting ...> |
| |
| SELECT gp_inject_fault('appendonly_insert', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = 0; |
| gp_inject_fault |
| ----------------- |
| Success: |
| (1 row) |
| 1<: <... completed> |
| INSERT 1 |
| |
| -- The placeholder row is invisible to the INSERTing transaction. Since the |
| -- INSERT finished, there should be 3 visible blkdir rows representing the |
| -- INSERT, 1 for each column. |
| 1: SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,2) | 1 | 0 | 0 | 1 | 0 | 1 |
| (0,3) | 1 | 1 | 0 | 1 | 0 | 1 |
| (0,4) | 1 | 2 | 0 | 1 | 0 | 1 |
| (3 rows) |
| |
| -- The placeholder row is invisible to other transactions (that don't perform a |
| -- uniqueness check) even after the INSERT finishes. The blkdir rows representing |
| -- the INSERT should not be visible as the INSERTing transaction hasn't |
| -- committed yet. |
| 2: SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0 rows) |
| |
| 1: COMMIT; |
| COMMIT |
| |
| -- The placeholder row is invisible to other transactions (that don't perform a |
| -- uniqueness check) even after the INSERTing transaction commits. Since the |
| -- INSERTing transaction has committed, the blkdir rows representing the INSERT |
| -- should be visible now. |
| 2: SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0 ORDER BY 1,2,3,4,5; |
| tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count |
| ---------+-------+----------------+----------+--------------+-------------+----------- |
| (0,2) | 1 | 0 | 0 | 1 | 0 | 1 |
| (0,3) | 1 | 1 | 0 | 1 | 0 | 1 |
| (0,4) | 1 | 2 | 0 | 1 | 0 | 1 |
| (3 rows) |
| |
| -- Now even though the DROP COLUMN has finished, we would still be able to |
| -- properly resolve uniqueness checks (by consulting the first non-dropped |
| -- column's block directory row). |
| 3<: <... completed> |
| ALTER |
| 4: INSERT INTO aoco_blkdir_test VALUES (2, 2); |
| ERROR: duplicate key value violates unique constraint "aoco_blkdir_test_i_key" (seg0 192.168.0.148:7002 pid=176693) |
| DETAIL: Key (i)=(2) already exists. |
| |
| DROP TABLE aoco_blkdir_test; |
| DROP |
| |
| -- Test `tupcount` in pg_ao(cs)seg == sum of number of `row_count` across all |
| -- aoblkdir entries for each <segno, columngroup_no>. Test with commits, aborts |
| -- and deletes. |
| |
| -- Case1: without VACUUM ANALYZE |
| CREATE TABLE aoco_blkdir_test_rowcount(i int, j int) USING ao_column DISTRIBUTED BY (j); |
| CREATE |
| 1: BEGIN; |
| BEGIN |
| 2: BEGIN; |
| BEGIN |
| 3: BEGIN; |
| BEGIN |
| 4: BEGIN; |
| BEGIN |
| 1: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 2 FROM generate_series(1, 10) i; |
| INSERT 10 |
| 2: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 3 FROM generate_series(1, 20) i; |
| INSERT 20 |
| 3: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 4 FROM generate_series(1, 30) i; |
| INSERT 30 |
| 3: ABORT; |
| ABORT |
| 3: BEGIN; |
| BEGIN |
| 3: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 4 FROM generate_series(1, 40) i; |
| INSERT 40 |
| 4: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 7 FROM generate_series(1, 50) i; |
| INSERT 50 |
| 1: COMMIT; |
| COMMIT |
| 2: COMMIT; |
| COMMIT |
| 3: COMMIT; |
| COMMIT |
| 4: COMMIT; |
| COMMIT |
| DELETE FROM aoco_blkdir_test_rowcount WHERE j = 7; |
| DELETE 50 |
| |
| CREATE INDEX aoco_blkdir_test_rowcount_idx ON aoco_blkdir_test_rowcount(i); |
| CREATE |
| |
| SELECT segno, columngroup_no, sum(row_count) AS totalrows FROM (SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test_rowcount')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0)s GROUP BY segno, columngroup_no ORDER BY segno, columngroup_no; |
| segno | columngroup_no | totalrows |
| -------+----------------+----------- |
| 1 | 0 | 10 |
| 1 | 1 | 10 |
| 2 | 0 | 20 |
| 2 | 1 | 20 |
| 3 | 0 | 40 |
| 3 | 1 | 40 |
| 4 | 0 | 50 |
| 4 | 1 | 50 |
| (8 rows) |
| SELECT segno, column_num, sum(tupcount) AS totalrows FROM gp_toolkit.__gp_aocsseg('aoco_blkdir_test_rowcount') WHERE segment_id = 0 GROUP BY segno, column_num; |
| segno | column_num | totalrows |
| -------+------------+----------- |
| 1 | 0 | 10 |
| 1 | 1 | 10 |
| 2 | 0 | 20 |
| 2 | 1 | 20 |
| 3 | 0 | 40 |
| 3 | 1 | 40 |
| 4 | 0 | 50 |
| 4 | 1 | 50 |
| (8 rows) |
| |
| -- Case2: with VACUUM ANALYZE |
| DROP TABLE aoco_blkdir_test_rowcount; |
| DROP |
| CREATE TABLE aoco_blkdir_test_rowcount(i int, j int) USING ao_column DISTRIBUTED BY (j); |
| CREATE |
| CREATE INDEX aoco_blkdir_test_rowcount_idx ON aoco_blkdir_test_rowcount(i); |
| CREATE |
| 1: BEGIN; |
| BEGIN |
| 2: BEGIN; |
| BEGIN |
| 3: BEGIN; |
| BEGIN |
| 4: BEGIN; |
| BEGIN |
| 1: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 2 FROM generate_series(1, 10) i; |
| INSERT 10 |
| 1: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 2 FROM aoco_blkdir_test_rowcount; |
| INSERT 10 |
| 1: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 2 FROM aoco_blkdir_test_rowcount; |
| INSERT 20 |
| 2: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 3 FROM generate_series(1, 20) i; |
| INSERT 20 |
| 2: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 3 FROM aoco_blkdir_test_rowcount; |
| INSERT 20 |
| 2: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 3 FROM aoco_blkdir_test_rowcount; |
| INSERT 40 |
| 3: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 4 FROM generate_series(1, 30) i; |
| INSERT 30 |
| 3: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 4 FROM aoco_blkdir_test_rowcount; |
| INSERT 30 |
| 3: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 4 FROM aoco_blkdir_test_rowcount; |
| INSERT 60 |
| 4: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 7 FROM generate_series(1, 50) i; |
| INSERT 50 |
| 4: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 7 FROM aoco_blkdir_test_rowcount; |
| INSERT 50 |
| 4: INSERT INTO aoco_blkdir_test_rowcount SELECT i, 7 FROM aoco_blkdir_test_rowcount; |
| INSERT 100 |
| 1: COMMIT; |
| COMMIT |
| 2: COMMIT; |
| COMMIT |
| 3: ABORT; |
| ABORT |
| 4: COMMIT; |
| COMMIT |
| |
| DELETE FROM aoco_blkdir_test_rowcount WHERE j = 7; |
| DELETE 200 |
| VACUUM ANALYZE aoco_blkdir_test_rowcount; |
| VACUUM |
| |
| SELECT segno, columngroup_no, sum(row_count) AS totalrows FROM (SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test_rowcount')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0)s GROUP BY segno, columngroup_no ORDER BY segno, columngroup_no; |
| segno | columngroup_no | totalrows |
| -------+----------------+----------- |
| 1 | 0 | 40 |
| 1 | 1 | 40 |
| 2 | 0 | 80 |
| 2 | 1 | 80 |
| (4 rows) |
| SELECT segno, column_num, sum(tupcount) AS totalrows FROM gp_toolkit.__gp_aocsseg('aoco_blkdir_test_rowcount') WHERE segment_id = 0 GROUP BY segno, column_num; |
| segno | column_num | totalrows |
| -------+------------+----------- |
| 1 | 0 | 40 |
| 1 | 1 | 40 |
| 2 | 0 | 80 |
| 2 | 1 | 80 |
| 3 | 0 | 0 |
| 3 | 1 | 0 |
| 4 | 0 | 0 |
| 4 | 1 | 0 |
| (8 rows) |
| |
| UPDATE aoco_blkdir_test_rowcount SET i = i + 1; |
| UPDATE 120 |
| VACUUM ANALYZE aoco_blkdir_test_rowcount; |
| VACUUM |
| |
| SELECT segno, columngroup_no, sum(row_count) AS totalrows FROM (SELECT (gp_toolkit.__gp_aoblkdir('aoco_blkdir_test_rowcount')).* FROM gp_dist_random('gp_id') WHERE gp_segment_id = 0)s GROUP BY segno, columngroup_no ORDER BY segno, columngroup_no; |
| segno | columngroup_no | totalrows |
| -------+----------------+----------- |
| 3 | 0 | 120 |
| 3 | 1 | 120 |
| (2 rows) |
| SELECT segno, column_num, sum(tupcount) AS totalrows FROM gp_toolkit.__gp_aocsseg('aoco_blkdir_test_rowcount') WHERE segment_id = 0 GROUP BY segno, column_num; |
| segno | column_num | totalrows |
| -------+------------+----------- |
| 1 | 0 | 0 |
| 1 | 1 | 0 |
| 2 | 0 | 0 |
| 2 | 1 | 0 |
| 3 | 0 | 120 |
| 3 | 1 | 120 |
| 4 | 0 | 0 |
| 4 | 1 | 0 |
| (8 rows) |
| |
| DROP TABLE aoco_blkdir_test_rowcount; |
| DROP |
