Re: Test tidscan,sql is not immune to autovacuum in v14

On Thu, 20 Aug 2026 at 14:50, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I don't really like this proposal. I think the only reason these
> test cases exist at all is to remind us that we aren't very smart
> about joins on ctid. In the event that somebody fixes that, these
> plans would change --- except that it's really unlikely that
> such an optimization could "see through" the DISTINCT you propose
> to add. So I think this change basically makes these tests unfit
> for any purpose.

This surprised me. When I looked at the commit message for 0a6ea4001,
I expected that you added these tests to exercise the newly added
hashtid() functions. It makes sense to me that you added the query
with and without EXPLAIN as you wanted to verify all 10k rows were
found and also that the chosen plan was a Hash Join. If the plan had
flipped to a Merge Join or Nested Loop, then it wouldn't be doing a
very good job of exercising hashtid().

Are you sure that's not the reason?

> I think the reasonable choices are
>
> (1) Remove these test cases completely in all branches. The
> argument for this is that we're expending test cycles for pretty
> nearly no benefit, and if someone did fix the case then the
> tests could be put back with new expected output.
>
> (2) Fix only in v14, to ensure that we don't see another of
> these failures.
>
> (3) Do nothing, reasoning that we probably won't see another
> such failure in v14's remaining lifespan anyway.
>
> I'm kind of leaning to (1), but (3) is plausible too.
> I'm not seeing a lot of point in (2).

I'm not for #2 either. IMO, #1 reduces the coverage for hashing of
tids, though we probably don't need that done on a 10k-row table. I
thought about #3, but it still leaves a query in our tests that we're
looking at the EXPLAIN for that has two equally cheap lowest-cost
plans. I thought it was best to do something before another reason
appears that introduces something new to make that fail randomly.

Another way to make the alternative join order less favourable would
be with something like:

select count(*) from tenk1 t1 inner join tenk1 t2 on t1.ctid = t2.ctid
where t2.ten = 0;

I make that:

-> Hash Join (cost=482.50..975.00 rows=1000 width=0)

and with the join order swapped to put the filtering on the probe
side, the costs are:

-> Hash Join (cost=570.00..1053.75 rows=1000 width=0)

Which is likely a large enough difference to stop any future random failures.

David