Обсуждение: Failures in 'rules' regression test
Is anyone else seeing failure of the "rules" regression test with
current CVS sources, or is it just me?
Looking at the differences, I see that rules.sql uses getpgusername(),
which means that it is certain to create a "failure" if run under any
unusual user name. This is bad (and the fact that the committed version
of rules.out was evidently made under the nonstandard name "pgsql"
doesn't help). I suggest removing that usage.
The other differences seem to be ones where the same tuples are returned
but not in the same order as is obtained on the system where the
expected-output file was made. I recall a similar complaint back in
late October 98, and I think the root cause now is the same as it was
then. To produce the "shoelace" view, Postgres is doing a merge join,
which involves qsort()'ing the tuples of the base tables --- and for
equal-keyed items qsort() can return the items in an
implementation-dependent order. So the regression test will succeed or
fail depending on the vagaries of the local qsort().
I suggest adding "ORDER BY sl_name", or some such, to each of the views
in the rules test that is made from a join.
BTW, it's possible that this system-dependency in the rules test was
previously masked by the optimizer bugs that Bruce has fixed recently;
that would explain why it wasn't seen before. I know I wasn't seeing
this difference until last week. But if the optimizer was previously
picking a join method that didn't involve a sort, the problem would
be masked.
regards, tom lane
*** expected/rules.out Tue Feb 9 17:44:57 1999
--- results/rules.out Sat Feb 13 14:31:56 1999
***************
*** 919,929 **** sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm
| 100 sl7 | 7|brown | 60|cm | 60
- sl3 | 0|black | 35|inch | 88.9 sl4 | 8|black | 40|inch | 101.6 sl8
| 1|brown | 40|inch | 101.6
- sl5 | 4|brown | 1|m | 100 sl6 | 0|brown | 0.9|m | 90 (8
rows) QUERY: SELECT * FROM shoe_ready WHERE total_avail >= 2;
--- 919,929 ---- sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm
| 100 sl7 | 7|brown | 60|cm | 60 sl4 | 8|black | 40|inch |
101.6
+ sl3 | 0|black | 35|inch | 88.9 sl8 | 1|brown | 40|inch | 101.6 sl6
| 0|brown | 0.9|m | 90
+ sl5 | 4|brown | 1|m | 100 (8 rows) QUERY: SELECT * FROM shoe_ready WHERE total_avail
>=2;
***************
*** 950,957 **** QUERY: UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; QUERY: SELECT * FROM
shoelace_log;sl_name |sl_avail|log_who|log_when
! ----------+--------+-------+--------
! sl7 | 6|pgsql |epoch (1 row) QUERY: CREATE RULE shoelace_ins AS ON INSERT TO shoelace
--- 950,957 ---- QUERY: UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; QUERY: SELECT * FROM
shoelace_log;sl_name |sl_avail|log_who |log_when
! ----------+--------+--------+--------
! sl7 | 6|postgres|epoch (1 row) QUERY: CREATE RULE shoelace_ins AS ON INSERT TO shoelace
***************
*** 997,1030 **** sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm
| 100 sl7 | 6|brown | 60|cm | 60
- sl3 | 0|black | 35|inch | 88.9 sl4 | 8|black | 40|inch | 101.6 sl8
| 1|brown | 40|inch | 101.6
! sl5 | 4|brown | 1|m | 100 sl6 | 0|brown | 0.9|m | 90 (8
rows) QUERY: insert into shoelace_ok select * from shoelace_arrive; QUERY: SELECT * FROM shoelace; sl_name
|sl_avail|sl_color |sl_len|sl_unit |sl_len_cm ----------+--------+----------+------+--------+---------
- sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm | 100 sl7
| 6|brown | 60|cm | 60 sl4 | 8|black | 40|inch | 101.6 sl3
| 10|black | 35|inch | 88.9
- sl8 | 21|brown | 40|inch | 101.6 sl5 | 4|brown | 1|m | 100 sl6
| 20|brown | 0.9|m | 90 (8 rows) QUERY: SELECT * FROM shoelace_log; sl_name
|sl_avail|log_who|log_when
! ----------+--------+-------+--------
! sl7 | 6|pgsql |epoch
! sl3 | 10|pgsql |epoch
! sl6 | 20|pgsql |epoch
! sl8 | 21|pgsql |epoch (4 rows) QUERY: CREATE VIEW shoelace_obsolete AS
--- 997,1030 ---- sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm
| 100 sl7 | 6|brown | 60|cm | 60 sl4 | 8|black | 40|inch |
101.6sl8 | 1|brown | 40|inch | 101.6
! sl3 | 0|black | 35|inch | 88.9 sl6 | 0|brown | 0.9|m | 90
+ sl5 | 4|brown | 1|m | 100 (8 rows) QUERY: insert into shoelace_ok select * from
shoelace_arrive;QUERY: SELECT * FROM shoelace; sl_name |sl_avail|sl_color |sl_len|sl_unit |sl_len_cm
----------+--------+----------+------+--------+---------sl2 | 6|black | 100|cm | 100
+ sl1 | 5|black | 80|cm | 80 sl7 | 6|brown | 60|cm | 60
+ sl8 | 21|brown | 40|inch | 101.6 sl4 | 8|black | 40|inch | 101.6 sl3
| 10|black | 35|inch | 88.9 sl5 | 4|brown | 1|m | 100 sl6
| 20|brown | 0.9|m | 90 (8 rows) QUERY: SELECT * FROM shoelace_log; sl_name |sl_avail|log_who
|log_when
! ----------+--------+--------+--------
! sl7 | 6|postgres|epoch
! sl3 | 10|postgres|epoch
! sl6 | 20|postgres|epoch
! sl8 | 21|postgres|epoch (4 rows) QUERY: CREATE VIEW shoelace_obsolete AS
***************
*** 1053,1065 **** QUERY: SELECT * FROM shoelace; sl_name |sl_avail|sl_color |sl_len|sl_unit |sl_len_cm
----------+--------+----------+------+--------+---------
- sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm | 100 sl7
| 6|brown | 60|cm | 60
- sl4 | 8|black | 40|inch | 101.6 sl3 | 10|black | 35|inch | 88.9
! sl8 | 21|brown | 40|inch | 101.6 sl10 | 1000|magenta | 40|inch | 101.6 sl5
| 4|brown | 1|m | 100 sl6 | 20|brown | 0.9|m | 90 (9 rows)
--- 1053,1065 ---- QUERY: SELECT * FROM shoelace; sl_name |sl_avail|sl_color |sl_len|sl_unit |sl_len_cm
----------+--------+----------+------+--------+---------sl2 | 6|black | 100|cm | 100
+ sl1 | 5|black | 80|cm | 80 sl7 | 6|brown | 60|cm | 60 sl3
| 10|black | 35|inch | 88.9
! sl4 | 8|black | 40|inch | 101.6 sl10 | 1000|magenta | 40|inch | 101.6
+ sl8 | 21|brown | 40|inch | 101.6 sl5 | 4|brown | 1|m | 100 sl6
| 20|brown | 0.9|m | 90 (9 rows)
----------------------
Tom Lane wrote:
>
> Is anyone else seeing failure of the "rules" regression test with
> current CVS sources, or is it just me?
"me too"
Though my output is in a slightly different order again, ie., different
system, so your qsort() theory seems good.
BTW, the error messages seem to have changed (running NetBSD-current),
so apart from rules, everything passes.
float8 .. failed
geometry .. failed
misc .. failed
rules .. failed
*** expected/float8-NetBSD.out Sat Feb 6 19:53:55 1999
--- results/float8.out Sun Feb 14 14:16:38 1999
***************
*** 209,217 **** (5 rows) QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
! ERROR: Bad float8 input format '10e400' QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
! ERROR: Bad float8 input format '-10e400' QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); QUERY: INSERT INTO
FLOAT8_TBL(f1)VALUES ('-10e-400'); QUERY: DELETE FROM FLOAT8_TBL;
--- 209,217 ---- (5 rows) QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
! ERROR: Input '10e400' is out of range for float8 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
! ERROR: Input '-10e400' is out of range for float8 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); QUERY:
INSERTINTO FLOAT8_TBL(f1) VALUES ('-10e-400'); QUERY: DELETE FROM FLOAT8_TBL;
*** expected/geometry-NetBSD.out Sat Feb 6 19:53:55 1999
--- results/geometry.out Sun Feb 14 14:16:40 1999
***************
*** 87,93 **** QUERY: SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection FROM LSEG_TBL l, POINT_TBL p;
! ERROR: There is more than one possible operator '#' for types 'lseg' and 'point' You will have to retype this
queryusing an explicit cast QUERY: SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest FROM LSEG_TBL l, POINT_TBL
p;
--- 87,93 ---- QUERY: SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection FROM LSEG_TBL l, POINT_TBL p;
! ERROR: Unable to identify an operator '#' for types 'lseg' and 'point' You will have to retype this query
usingan explicit cast QUERY: SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest FROM LSEG_TBL l, POINT_TBL p;
*** expected/misc.out Sun Feb 14 14:16:25 1999
--- results/misc.out Sun Feb 14 14:18:42 1999
***************
*** 6,19 **** SET stringu1 = reverse_name(onek.stringu1) WHERE onek.stringu1 = 'JBAAAA' and onek.stringu1
=tmp.stringu1;
- NOTICE: Non-functional update, only first update is performed
- NOTICE: Non-functional update, only first update is performed QUERY: UPDATE tmp SET stringu1 =
reverse_name(onek2.stringu1) WHERE onek2.stringu1 = 'JCAAAA' and onek2.stringu1 = tmp.stringu1;
- NOTICE: Non-functional update, only first update is performed
- NOTICE: Non-functional update, only first update is performed QUERY: DROP TABLE tmp; QUERY: COPY onek TO
'/home/prlw1/pgsql/src/test/regress/input/../results/onek.data';QUERY: DELETE FROM onek;
--- 6,15 ----
Cheers,
Patrick
>
> Is anyone else seeing failure of the "rules" regression test with
> current CVS sources, or is it just me?
Must have been me :-(
I added some more tests recently - will take a look at it.
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) #