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