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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> ohp(at)pyrenet(dot)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