jwieck@debis.com (Jan Wieck) writes:
> These two queries will produce join plans. Since there are no
> indices on any of the tables, they should produce tuples in
> exactly the order they where entered into the table.
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)
The thing that jumps out at me here is the "sort" step (which I assume
is on pname for rtest_admin and pdesc for rtest_person, those being the
fields to be joined). Since the prior state of rtest_admin is
QUERY: select * from rtest_admin;
pname|sysname
-----+-------
jw |orion
jw |notjw
bm |pluto
(3 rows)
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.
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...
regards, tom lane