Re: rules regression test diff (was Re: [HACKERS] Last call?)

Tom Lane wrote:

> I modified the rules.sql test to perform an EXPLAIN of the query
> that is generating the unexpected result, and it says:
>
> QUERY: explain update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck';
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=0.00 size=1 width=42)
> -> Seq Scan (cost=0.00 size=0 width=0)
> -> Sort (cost=0.00 size=0 width=0)
> -> Seq Scan on rtest_admin (cost=0.00 size=0 width=30)
> -> Seq Scan (cost=0.00 size=0 width=0)
> -> Sort (cost=0.00 size=0 width=0)
> -> Seq Scan on rtest_person (cost=0.00 size=0 width=12)
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on rtest_person (cost=0.00 size=0 width=18)

Isn't it nice to have EXPLAIN doing the rewrite step?

> [...]
>
> the two rows that will be updated have equal sort keys and therefore the
> sort could legally return them in either order. Does Postgres contain
> its own sort algorithm for this kind of operation, or does it depend on
> the system qsort? System library qsorts don't normally guarantee
> stability for equal keys. It could be that we're looking at a byproduct
> of some minor implementation difference between the qsorts on my machine
> and yours. If that's it, though, I'm surprised that I'm the only one
> reporting a difference in the test's output.

Could be the reason. createfirstrun() in psort.c is using
qsort as a first try. Maybe we should add ORDER BY pname,
sysname to the queries to avoid it.

>
> BTW, I get the same query plan if I EXPLAIN the query that you say the
> rule should expand to,
>
> UPDATE rtest_admin SET pname = 'jwieck'
> WHERE rtest_person.pdesc = 'Jan Wieck'
> AND rtest_admin.pname = rtest_person.pdesc;
>
> so there doesn't seem to be anything wrong with the rule rewriter...

Sure. The parsetree generated by the rule system is exactly
that what the parser outputs for this query. I hope some
people understand my new documentation of the rule system.
Sometimes the lonesome rule-rider needs a partner too.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #