Alvaro Herrera <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
