Re: Re: [COMMITTERS] pgsql: Fix brain fade in DefineIndex(): it was continuing to access the

Alvaro Herrera <alvherre@commandprompt.com> writes:
> ohp@pyrenet.fr wrote:
>> While waiting for my application for another animal, I made some tests and
>> was surprised that cluster test failed with an ordering error.

> This is running with CLOBBER_CACHE_ALWAYS set, right?  I think it is
> quite possible that an autovacuum came and processed the catalog,
> leading to different ordering.

I've seen this exact ordering difference once or twice before but hadn't
got round to looking into the cause.  I think Alvaro is right though,
because what I see in pg_constraint after a "typical" serial regression
test is
 ctid  |            conname            
--------+-------------------------------(0,1)  | cardinal_number_domain_check(0,5)  | check_con(0,6)  |
sequence_con(0,7) | insert_con(0,8)  | insert_tbl_check(0,9)  | rule_and_refint_t1_pkey(0,10) |
rule_and_refint_t2_pkey(0,11)| rule_and_refint_t3_pkey(0,12) | rule_and_refint_t3_id3a_fkey(0,13) |
rule_and_refint_t3_id3a_fkey1(1,1) | copy_con(1,10) | foo(1,11) | inhx_pkey(3,4)  | clstr_tst_s_pkey(3,5)  |
clstr_tst_pkey(3,6) | clstr_tst_con(3,26) | con_check(4,2)  | str_domain2_check(4,3)  | pos_int_check
 
(19 rows)

The planner seems to prefer to do the query at issue by seqscan,
regardless of whether pg_constraint has been vacuumed/analyzed lately.
So the result will depend on where these two rows get dropped.  As
you can see, page 2 is entirely empty, so we could see the reported
result if clstr_tst_pkey went into page 3 and then an autovacuum
reported page 2 as having free space before the clstr_tst_con row was
inserted.  This is a sufficiently narrow window to be unlikely, but
not impossible; and it's easy to believe that CLOBBER_CACHE_ALWAYS could
widen the window.

ORDER BY added, as suggested by Alvaro.
        regards, tom lane