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