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@debis.com (Jan Wieck) #