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

Reply via email to