Re: [HACKERS] Last call?

Tom Lane wrote:

> I am still getting a discrepancy in the "rules" regression test,
> namely a difference in the order in which tuples are returned:
>
> *** expected/rules.out Fri Oct 2 12:28:01 1998
> --- results/rules.out Sun Oct 25 19:31:42 1998
> ***************
> *** 315,322 ****
> pname |sysname
> ------+-------
> bm |pluto
> - jwieck|orion
> jwieck|notjw
> (3 rows)
>
> QUERY: delete from rtest_system where sysname = 'orion';
> --- 315,322 ----
> pname |sysname
> ------+-------
> bm |pluto
> jwieck|notjw
> + jwieck|orion
> (3 rows)
>
> QUERY: delete from rtest_system where sysname = 'orion';
>
> ----------------------
>
> This happens on all four permutations of HPUX version and compiler.
> Are other people really seeing the tuple order given in the "expected"
> file?

I think they should be in the order given in the expected
file.

The rows inserted into the rtest_admin table (really a table,
not a view) are:

pname|sysname
-----+-------
jw |orion
jw |notjw
bm |neptun

Then two updates are performed. The rules that are there
would add parsetrees as if these queries are given:

UPDATE rtest_admin SET sysname = 'pluto'
WHERE rtest_system.sysname = 'neptun'
AND rtest_admin.sysname = rtest_system.sysname;

UPDATE rtest_admin SET pname = 'jwieck'
WHERE rtest_person.pdesc = 'Jan Wieck'
AND rtest_admin.pname = rtest_person.pdesc;

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. An
UPDATE creates a new tuple, inserts it and outdates the
current by ctid.

In rtest_system and rtest_person there are only 1 row that
matches each of the given qualifications. So the question is
why on HPUX the order of tuples returned in the resulting
join plans differs from other OS's. The SELECT that produces
the wrong order above should result in a SeqScan, and that
must return the tuples in ctid order.

The first rule update on rtest_admin (fired at the UPDATE on
rtest_system.sysname) doesn't change the order of the tuples
(or did you omit this in your mail?). So why the hell does
the second? Updated rows always appear at the end of a
SeqScan in the order they where updated. There is no vacuum
between the updates, so the space from the outdated tuples
should not be reused here.

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) #