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