Обсуждение: Regression test fails when BLCKSZ is 1kB
I compiled postgreSQL with 1kB block size and regresion test fails. Main problem
is that output is correct but in different order. See attachment.
I think affected test should contain order by keyword.
Any comments?
Zdenek
*** ./expected/join.out Wed Jan 9 21:42:28 2008
--- ./results/join.out Mon Apr 21 13:50:53 2008
***************
*** 214,226 ****
WHERE t1.a = t2.d;
xxx | a | e
-----+---+----
- | 0 |
| 1 | -1
| 2 | 2
- | 2 | 4
| 3 | -3
| 5 | -5
| 5 | -5
(7 rows)
--
--- 214,226 ----
WHERE t1.a = t2.d;
xxx | a | e
-----+---+----
| 1 | -1
| 2 | 2
| 3 | -3
+ | 2 | 4
| 5 | -5
| 5 | -5
+ | 0 |
(7 rows)
--
***************
*** 1569,1581 ****
FROM J1_TBL INNER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
-- Same as above, slightly different syntax
--- 1569,1581 ----
FROM J1_TBL INNER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
-- Same as above, slightly different syntax
***************
*** 1583,1595 ****
FROM J1_TBL JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
SELECT '' AS "xxx", *
--- 1583,1595 ----
FROM J1_TBL JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
SELECT '' AS "xxx", *
***************
*** 1625,1637 ****
FROM J1_TBL NATURAL JOIN J2_TBL;
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
SELECT '' AS "xxx", *
--- 1625,1637 ----
FROM J1_TBL NATURAL JOIN J2_TBL;
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
SELECT '' AS "xxx", *
***************
*** 1638,1650 ****
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
xxx | a | b | c | d
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
SELECT '' AS "xxx", *
--- 1638,1650 ----
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
xxx | a | b | c | d
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
SELECT '' AS "xxx", *
***************
*** 1651,1659 ****
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
xxx | a | b | c | d
-----+---+---+------+---
- | 0 | | zero |
| 2 | 3 | two | 2
| 4 | 1 | four | 2
(3 rows)
-- mismatch number of columns
--- 1651,1659 ----
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
xxx | a | b | c | d
-----+---+---+------+---
| 2 | 3 | two | 2
| 4 | 1 | four | 2
+ | 0 | | zero |
(3 rows)
-- mismatch number of columns
***************
*** 1662,1674 ****
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
xxx | a | b | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows)
--
--- 1662,1674 ----
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
xxx | a | b | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
(7 rows)
--
***************
*** 1678,1690 ****
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
xxx | i | j | t | i | k
-----+---+---+-------+---+----
- | 0 | | zero | 0 |
| 1 | 4 | one | 1 | -1
| 2 | 3 | two | 2 | 2
- | 2 | 3 | two | 2 | 4
| 3 | 2 | three | 3 | -3
| 5 | 0 | five | 5 | -5
| 5 | 0 | five | 5 | -5
(7 rows)
SELECT '' AS "xxx", *
--- 1678,1690 ----
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
xxx | i | j | t | i | k
-----+---+---+-------+---+----
| 1 | 4 | one | 1 | -1
| 2 | 3 | two | 2 | 2
| 3 | 2 | three | 3 | -3
+ | 2 | 3 | two | 2 | 4
| 5 | 0 | five | 5 | -5
| 5 | 0 | five | 5 | -5
+ | 0 | | zero | 0 |
(7 rows)
SELECT '' AS "xxx", *
***************
*** 1691,1699 ****
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
xxx | i | j | t | i | k
-----+---+---+------+---+---
- | 0 | | zero | | 0
| 2 | 3 | two | 2 | 2
| 4 | 1 | four | 2 | 4
(3 rows)
--
--- 1691,1699 ----
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
xxx | i | j | t | i | k
-----+---+---+------+---+---
| 2 | 3 | two | 2 | 2
| 4 | 1 | four | 2 | 4
+ | 0 | | zero | | 0
(3 rows)
--
***************
*** 1762,1774 ****
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
| | | |
| | | | 0
(9 rows)
--- 1762,1774 ----
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
| | | |
| | | | 0
(9 rows)
***************
*** 1777,1789 ****
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
- | 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
- | 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
| | | |
| | | | 0
(9 rows)
--- 1777,1789 ----
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
xxx | i | j | t | k
-----+---+---+-------+----
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 3 | 2 | three | -3
+ | 2 | 3 | two | 4
| 5 | 0 | five | -5
| 5 | 0 | five | -5
+ | 0 | | zero |
| | | |
| | | | 0
(9 rows)
======================================================================
*** ./expected/arrays.out Thu Mar 20 22:42:48 2008
--- ./results/arrays.out Mon Apr 21 13:50:52 2008
***************
*** 139,146 ****
a | b | c
---------------+-----------------------+-------------------
{16,25,3,4,5} | {{{113,142},{1,147}}} | {}
- {16,25,23} | {{3,4},{4,5}} | {foobar,new_word}
[4:4]={NULL} | {3,4} | {foo,new_word}
(3 rows)
--
--- 139,146 ----
a | b | c
---------------+-----------------------+-------------------
{16,25,3,4,5} | {{{113,142},{1,147}}} | {}
[4:4]={NULL} | {3,4} | {foo,new_word}
+ {16,25,23} | {{3,4},{4,5}} | {foobar,new_word}
(3 rows)
--
======================================================================
*** ./expected/misc.out Mon Apr 21 13:50:12 2008
--- ./results/misc.out Mon Apr 21 13:50:55 2008
***************
*** 356,361 ****
--- 356,362 ----
e | 18 | | |
e | | hi elisa | |
e | | | -4 |
+ f | 25 | | -9 |
f | 19 | hi claire | -5 |
f | 20 | hi mike | -6 |
f | 21 | hi marcel | |
***************
*** 362,368 ****
f | 22 | | -7 |
f | | hi keith | -8 |
f | 24 | hi marc | |
- f | 25 | | -9 |
f | 26 | | |
f | | hi allison | -10 |
f | | hi jeff | |
--- 363,368 ----
***************
*** 416,421 ****
--- 416,422 ----
e | 18 |
e | |
e | |
+ f | 25 |
f | 19 |
f | 20 |
f | 21 |
***************
*** 422,428 ****
f | 22 |
f | |
f | 24 |
- f | 25 |
f | 26 |
f | |
f | |
--- 423,428 ----
======================================================================
*** ./expected/polymorphism.out Fri Jan 11 19:39:41 2008
--- ./results/polymorphism.out Mon Apr 21 13:51:03 2008
***************
*** 355,362 ****
select f3, myaggp01a(*) from t group by f3;
f3 | myaggp01a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 355,362 ----
select f3, myaggp01a(*) from t group by f3;
f3 | myaggp01a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 363,370 ****
select f3, myaggp03a(*) from t group by f3;
f3 | myaggp03a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 363,370 ----
select f3, myaggp03a(*) from t group by f3;
f3 | myaggp03a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 371,378 ****
select f3, myaggp03b(*) from t group by f3;
f3 | myaggp03b
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 371,378 ----
select f3, myaggp03b(*) from t group by f3;
f3 | myaggp03b
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 379,386 ****
select f3, myaggp05a(f1) from t group by f3;
f3 | myaggp05a
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 379,386 ----
select f3, myaggp05a(f1) from t group by f3;
f3 | myaggp05a
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 387,394 ****
select f3, myaggp06a(f1) from t group by f3;
f3 | myaggp06a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 387,394 ----
select f3, myaggp06a(f1) from t group by f3;
f3 | myaggp06a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 395,402 ****
select f3, myaggp08a(f1) from t group by f3;
f3 | myaggp08a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 395,402 ----
select f3, myaggp08a(f1) from t group by f3;
f3 | myaggp08a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 403,410 ****
select f3, myaggp09a(f1) from t group by f3;
f3 | myaggp09a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 403,410 ----
select f3, myaggp09a(f1) from t group by f3;
f3 | myaggp09a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 411,418 ****
select f3, myaggp09b(f1) from t group by f3;
f3 | myaggp09b
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 411,418 ----
select f3, myaggp09b(f1) from t group by f3;
f3 | myaggp09b
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 419,426 ****
select f3, myaggp10a(f1) from t group by f3;
f3 | myaggp10a
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 419,426 ----
select f3, myaggp10a(f1) from t group by f3;
f3 | myaggp10a
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 427,434 ****
select f3, myaggp10b(f1) from t group by f3;
f3 | myaggp10b
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 427,434 ----
select f3, myaggp10b(f1) from t group by f3;
f3 | myaggp10b
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 435,442 ****
select f3, myaggp20a(f1) from t group by f3;
f3 | myaggp20a
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 435,442 ----
select f3, myaggp20a(f1) from t group by f3;
f3 | myaggp20a
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 443,450 ****
select f3, myaggp20b(f1) from t group by f3;
f3 | myaggp20b
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 443,450 ----
select f3, myaggp20b(f1) from t group by f3;
f3 | myaggp20b
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 451,458 ****
select f3, myaggn01a(*) from t group by f3;
f3 | myaggn01a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 451,458 ----
select f3, myaggn01a(*) from t group by f3;
f3 | myaggn01a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 459,466 ****
select f3, myaggn01b(*) from t group by f3;
f3 | myaggn01b
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 459,466 ----
select f3, myaggn01b(*) from t group by f3;
f3 | myaggn01b
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 467,474 ****
select f3, myaggn03a(*) from t group by f3;
f3 | myaggn03a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 467,474 ----
select f3, myaggn03a(*) from t group by f3;
f3 | myaggn03a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 475,482 ****
select f3, myaggn05a(f1) from t group by f3;
f3 | myaggn05a
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 475,482 ----
select f3, myaggn05a(f1) from t group by f3;
f3 | myaggn05a
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 483,490 ****
select f3, myaggn05b(f1) from t group by f3;
f3 | myaggn05b
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 483,490 ----
select f3, myaggn05b(f1) from t group by f3;
f3 | myaggn05b
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
***************
*** 491,498 ****
select f3, myaggn06a(f1) from t group by f3;
f3 | myaggn06a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 491,498 ----
select f3, myaggn06a(f1) from t group by f3;
f3 | myaggn06a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 499,506 ****
select f3, myaggn06b(f1) from t group by f3;
f3 | myaggn06b
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 499,506 ----
select f3, myaggn06b(f1) from t group by f3;
f3 | myaggn06b
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 507,514 ****
select f3, myaggn08a(f1) from t group by f3;
f3 | myaggn08a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 507,514 ----
select f3, myaggn08a(f1) from t group by f3;
f3 | myaggn08a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 515,522 ****
select f3, myaggn08b(f1) from t group by f3;
f3 | myaggn08b
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 515,522 ----
select f3, myaggn08b(f1) from t group by f3;
f3 | myaggn08b
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 523,530 ****
select f3, myaggn09a(f1) from t group by f3;
f3 | myaggn09a
----+-----------
- b | {}
c | {}
a | {}
(3 rows)
--- 523,530 ----
select f3, myaggn09a(f1) from t group by f3;
f3 | myaggn09a
----+-----------
c | {}
+ b | {}
a | {}
(3 rows)
***************
*** 531,538 ****
select f3, myaggn10a(f1) from t group by f3;
f3 | myaggn10a
----+-----------
- b | {1,2,3}
c | {1,2}
a | {1,2,3}
(3 rows)
--- 531,538 ----
select f3, myaggn10a(f1) from t group by f3;
f3 | myaggn10a
----+-----------
c | {1,2}
+ b | {1,2,3}
a | {1,2,3}
(3 rows)
======================================================================
Am Montag, 21. April 2008 schrieb Zdenek Kotala: > I compiled postgreSQL with 1kB block size and regresion test fails. Main > problem is that output is correct but in different order. See attachment. This was previously reported: http://archives.postgresql.org/pgsql-hackers/2006-11/msg00901.php > I think affected test should contain order by keyword. For previously established reasons, we don't want to add ORDER BY clauses to every test that might fail under exceptional circumstances so we test all plan types equally. I think very small block sizes are fairly exceptional, unless you have a reason up your sleeve why they are a good idea.
On Mon, Apr 21, 2008 at 5:55 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > > For previously established reasons, we don't want to add ORDER BY clauses to > every test that might fail under exceptional circumstances so we test all > plan types equally. I think very small block sizes are fairly exceptional, > unless you have a reason up your sleeve why they are a good idea. > Now that we have autovacuum on by default, we might get into random failures because of re-ordering. Though I don't seem to recall anybody complaining yet, it could just be that we are lucky or our regression suite don't have long enough running tests to give autovacuum chance to recycle some of the dead tuples. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
"Pavan Deolasee" <pavan.deolasee@gmail.com> writes:
> Now that we have autovacuum on by default, we might get into random
> failures because of re-ordering. Though I don't seem to recall anybody
> complaining yet, it could just be that we are lucky or our regression
> suite don't have long enough running tests to give autovacuum chance
> to recycle some of the dead tuples.
No, the reason you don't see that is that plain VACUUM doesn't move
tuples around.
regards, tom lane
Peter Eisentraut napsal(a): > Am Montag, 21. April 2008 schrieb Zdenek Kotala: >> I compiled postgreSQL with 1kB block size and regresion test fails. Main >> problem is that output is correct but in different order. See attachment. > > This was previously reported: > http://archives.postgresql.org/pgsql-hackers/2006-11/msg00901.php > >> I think affected test should contain order by keyword. > > For previously established reasons, we don't want to add ORDER BY clauses to > every test that might fail under exceptional circumstances so we test all > plan types equally. I think very small block sizes are fairly exceptional, > unless you have a reason up your sleeve why they are a good idea. I'm only testing behavior with different block size and I think it is not good idea to support only 8kB for regtest. When 4kB is used then PG fails in Join regresion test and with 16kB, 32kB it fails because: *** ./expected/bitmapops.out Fri Apr 11 00:25:26 2008 --- ./results/bitmapops.out Mon Apr 21 15:30:18 2008 *************** *** 20,25 **** --- 20,26 ---- set enable_seqscan=false; -- Lower work_mem to trigger use of lossy bitmaps set work_mem = 64; + ERROR: 64 is outside the valid range for parameter "work_mem" (256 .. 2097151) -- Test bitmap-and. SELECT count(*) FROMbmscantest WHERE a = 1 AND b = 1; count Zdenek
Am Montag, 21. April 2008 schrieb Zdenek Kotala: > I'm only testing behavior with different block size and I think it is not > good idea to support only 8kB for regtest. When 4kB is used then PG fails > in Join regresion test and with 16kB, 32kB it fails because: > > *** ./expected/bitmapops.out Fri Apr 11 00:25:26 2008 > --- ./results/bitmapops.out Mon Apr 21 15:30:18 2008 > *************** > *** 20,25 **** > --- 20,26 ---- > set enable_seqscan=false; > -- Lower work_mem to trigger use of lossy bitmaps > set work_mem = 64; > + ERROR: 64 is outside the valid range for parameter "work_mem" (256 .. > 2097151) -- Test bitmap-and. > SELECT count(*) FROM bmscantest WHERE a = 1 AND b = 1; > count This should probably be fixed by using a unit specification on work_mem. Do you want to prepare a patch?
On Mon, Apr 21, 2008 at 02:25:31PM +0200, Peter Eisentraut wrote: > > I think affected test should contain order by keyword. > > For previously established reasons, we don't want to add ORDER BY clauses to > every test that might fail under exceptional circumstances so we test all > plan types equally. I think very small block sizes are fairly exceptional, > unless you have a reason up your sleeve why they are a good idea. I wonder if it would be feasable to, whenever a regression test fails to sort both files and compare again. This should tell you if the difference are *only* rearrangement automatically, without having to eyeball the output. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Am Montag, 21. April 2008 schrieb Martijn van Oosterhout: > I wonder if it would be feasable to, whenever a regression test fails > to sort both files and compare again. This should tell you if the > difference are *only* rearrangement automatically, without having to > eyeball the output. That sounds like it should be worth a try.
Peter Eisentraut wrote: > Am Montag, 21. April 2008 schrieb Martijn van Oosterhout: > >> I wonder if it would be feasable to, whenever a regression test fails >> to sort both files and compare again. This should tell you if the >> difference are *only* rearrangement automatically, without having to >> eyeball the output. >> > > That sounds like it should be worth a try. > I think we need first to identify cases where we don't care that much about output order. Teaching pg-regress the new check shouldn't be very hard. cheers andrew
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Montag, 21. April 2008 schrieb Martijn van Oosterhout:
>> I wonder if it would be feasable to, whenever a regression test fails
>> to sort both files and compare again. This should tell you if the
>> difference are *only* rearrangement automatically, without having to
>> eyeball the output.
> That sounds like it should be worth a try.
That sounds like a pretty bad idea, since it would treat ordering
differences as insignificant even when they aren't --- for example,
an ordering difference in the output of a query that *has* an
ORDER BY is usually a bug.
regards, tom lane
On Mon, Apr 21, 2008 at 8:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > No, the reason you don't see that is that plain VACUUM doesn't move > tuples around. > I know. But plain VACUUM can free up dead space which can be used for subsequent updates/inserts and that can cause reordering. For example: Case 1. Insert 100 records --- goes into block 1 .. 10 Delete 100 records Insert 100 more records --- goes into 11 .. 20 Case 2. Insert 100 records --- goes into block 1 .. 10 Delete 100 records *Autovacuum triggers* Insert 100 more records -- goes into block 1 .. 10 Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Montag, 21. April 2008 schrieb Zdenek Kotala:
>> set work_mem = 64;
>> + ERROR: 64 is outside the valid range for parameter "work_mem" (256 ..
>> 2097151) -- Test bitmap-and.
> This should probably be fixed by using a unit specification on work_mem. Do
> you want to prepare a patch?
The problem is that guc.c enforces a lower limit of 8*BLCKSZ on the
work_mem setting. Unless we add an explicit unit specifier for "blocks"
to GUC's vocabulary, there doesn't seem to be any way to name that value
in the SET command. And it's not entirely clear that the SET would
still have the desired effect for this test, anyway, if it were getting
translated to 256K or more.
Another possible answer is to change the minimum to be just 64K always.
I'm not certain that it's really sensible to tie the minimum work_mem to
BLCKSZ --- I don't think we do anything where work_mem is controlling a
pool of page buffers, do we?
regards, tom lane
On Mon, Apr 21, 2008 at 10:54 PM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote: > Case 1. > > Insert 100 records --- goes into block 1 .. 10 > Delete 100 records > Insert 100 more records --- goes into 11 .. 20 > > > Case 2. > > Insert 100 records --- goes into block 1 .. 10 > Delete 100 records > *Autovacuum triggers* > Insert 100 more records -- goes into block 1 .. 10 > Its probably not a very neat example because in this simplistic case, the ordering would still be same, but we can easily construct a slightly complex example to prove the point. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Am Montag, 21. April 2008 schrieb Tom Lane: > That sounds like a pretty bad idea, since it would treat ordering > differences as insignificant even when they aren't --- for example, > an ordering difference in the output of a query that *has* an > ORDER BY is usually a bug. Well, we wouldn't treat ordering differences as OK, but we could print foo ... FAILED (only ordering differences) which might give a clue. Then again, the effort to make this bulletproof might be more than continuing to field the occasional question about the issue.
Peter Eisentraut napsal(a):
> Am Montag, 21. April 2008 schrieb Tom Lane:
>> That sounds like a pretty bad idea, since it would treat ordering
>> differences as insignificant even when they aren't --- for example,
>> an ordering difference in the output of a query that *has* an
>> ORDER BY is usually a bug.
>
> Well, we wouldn't treat ordering differences as OK, but we could print
>
> foo ... FAILED (only ordering differences)
>
> which might give a clue.
When you are able detect ordering difference you are able also check if it is
important for the test or not without any extra effort. Only what we need is put
some flag to test that order is not important.
> Then again, the effort to make this bulletproof might be more than continuing
> to field the occasional question about the issue.
Regression test MUST BE bulletproof. If you get a error you must know that it is
really error (in postgresql or regtest) and must be fixed. When you start to
ignore some errors because it can happen sometimes you fall in the trap soon.
Zdenek
Andrew Dunstan napsal(a):
>
>
> Peter Eisentraut wrote:
>> Am Montag, 21. April 2008 schrieb Martijn van Oosterhout:
>>
>>> I wonder if it would be feasable to, whenever a regression test fails
>>> to sort both files and compare again. This should tell you if the
>>> difference are *only* rearrangement automatically, without having to
>>> eyeball the output.
>>>
>>
>> That sounds like it should be worth a try.
>>
>
> I think we need first to identify cases where we don't care that much
> about output order. Teaching pg-regress the new check shouldn't be very
> hard.
It seems to me only ORDER BY clauses must return sort order. Or are there any
other cases?
Zdenek
Tom Lane napsal(a):
> Another possible answer is to change the minimum to be just 64K always.
> I'm not certain that it's really sensible to tie the minimum work_mem to
> BLCKSZ --- I don't think we do anything where work_mem is controlling a
> pool of page buffers, do we?
Yeah, I try to find all usage and it seems everything is related to tuplestore,
Bitmap or Hash join. I think we can set 64K set limit without any problem.
By the way is any reason to have work_mem * 1024 "everywhere" when we have unit
support in GUC?
Zdenek
On Tue, Apr 22, 2008 at 10:31:53AM +0200, Zdenek Kotala wrote: > When you are able detect ordering difference you are able also check if it > is important for the test or not without any extra effort. Only what we > need is put some flag to test that order is not important. Not true. Sorting the file is going jumble all the results together. Since we perform many tests in one file, you're not going to be able to seperate them. > Regression test MUST BE bulletproof. If you get a error you must know that > it is really error (in postgresql or regtest) and must be fixed. When you > start to ignore some errors because it can happen sometimes you fall in the > trap soon. I think people are misunderstanding. You posted a bunch of diffs with that comment that they *appeared* to only be ordering differences. How good did you check? If an 8 become a 9 chances are you'd miss it. Having a second test checking the sorted results would at least preclude the chance that there really is something wrong. It was a guide, not a way of getting out of tests. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout napsal(a): > On Tue, Apr 22, 2008 at 10:31:53AM +0200, Zdenek Kotala wrote: >> When you are able detect ordering difference you are able also check if it >> is important for the test or not without any extra effort. Only what we >> need is put some flag to test that order is not important. > > Not true. Sorting the file is going jumble all the results together. > Since we perform many tests in one file, you're not going to be able to > seperate them. Each statement result must be sort separately, otherwise it should hide problems. For example one statement return A instead of B and second returns B instead of A. When sort will be used on whole file then it will be reported as a ordering problem. > >> Regression test MUST BE bulletproof. If you get a error you must know that >> it is really error (in postgresql or regtest) and must be fixed. When you >> start to ignore some errors because it can happen sometimes you fall in the >> trap soon. > > I think people are misunderstanding. You posted a bunch of diffs with > that comment that they *appeared* to only be ordering differences. How > good did you check? If an 8 become a 9 chances are you'd miss it. > Having a second test checking the sorted results would at least > preclude the chance that there really is something wrong. > > It was a guide, not a way of getting out of tests. > > Have a nice day,
On Tue, Apr 22, 2008 at 4:25 PM, Martijn van Oosterhout <<a
href="mailto:kleptog@svana.org">kleptog@svana.org</a>>wrote:<br /><div class="gmail_quote"><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><divclass="Ih2E3d">On Tue, Apr 22, 2008 at 10:31:53AM +0200, Zdenek Kotala wrote:<br /> > When you are able
detectordering difference you are able also check if it<br /> > is important for the test or not without any extra
effort.Only what we<br /> > need is put some flag to test that order is not important.<br /><br /></div>Not true.
Sortingthe file is going jumble all the results together.<br /> Since we perform many tests in one file, you're not
goingto be able to<br /> seperate them.<br /><div class="Ih2E3d"><br /> > Regression test MUST BE bulletproof. If
youget a error you must know that<br /> > it is really error (in postgresql or regtest) and must be fixed. When
you<br/> > start to ignore some errors because it can happen sometimes you fall in the<br /> > trap soon.<br
/><br/></div>I think people are misunderstanding. You posted a bunch of diffs with<br /> that comment that they
*appeared*to only be ordering differences. How<br /> good did you check? If an 8 become a 9 chances are you'd miss
it.<br/> Having a second test checking the sorted results would at least<br /> preclude the chance that there really is
somethingwrong.<br /><br /> It was a guide, not a way of getting out of tests.<br /></blockquote></div><br />In the
past,I had faced and tried to work on this exact problem... here's what I had in mind:<br /><br />in the .expected
file,we would demarcate the section of lines we expect to come in any order, by using two special markers. Then, when
comparingthe actual output with expected output, we would take the demarcated group of lines, and the corresponding
linesfrom actual output, and compare them after sorting.<br /><br /> For eg.<br /><br />foo.expected:<br /><br
/>select* from tenk where col1 <= 3 limit 3;<br /><br />col1 | col2 | col3<br />-------------------------<br
clear="all"/>?unsorted_result_start<br />1 | 10 | 100<br />2 | 20 | 200<br />3 | 30 | 300<br /> ?unsorted_result_end<br
/><br/><br />foo.out:<br />select * from tenk where col1 <= 3 limit 3;<br /><br /> col1 | col2 | col3<br />
-------------------------<brclear="all" />3 | 30 | 300<br /> 2 | 20 | 200<br /> 1 | 10 | 100<br /><br /><br /> So,
thediff program should discard the lines beginning with '?' (meta character), and then sort and match exactly the same
numberof lines.<br /><br /> There's another option of putting these '?' lines in a separate file with corresponding
begin/endline numbers of the unsorted group, and using this as a parameter to the diffing program.<br /><br /> Of
course,this needs a change in the (standard) diff that we use from pg_regress!<br /><br />Best regards,<br />-- <br
/>gurjeet[.singh]@EnterpriseDB.com<br/>singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com<br /><br />
EnterpriseDB<a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />Mail sent from my
BlackLaptopdevice
<br /><br /><blockquote><hr />I was wandering if there where any intentions of moving some of the currently internal numericfunction prototypes to the Numeric header file. I believe such functions like cmp_numerics, add_var, etc would comein handy when writing user defined C code.<br /><br />Would there be many ill effects by doing this?<br /><br />Whatshould I look out for if I try it?<br /></blockquote><br /><hr />Grab it. <a href="http://mycareer.com.au/?s_cid=596065" target="_new">You dream job is up for grabs.</a>
Aaron Spiteri wrote: > > > ------------------------------------------------------------------------ > I was wandering if there where any intentions of moving some of > the currently internal numeric function prototypes to the Numeric > header file. I believe such functions like cmp_numerics, add_var, > etc would come in handy when writing user defined C code. > > Would there be many ill effects by doing this? > > What should I look out for if I try it? > > > <http://mycareer.com.au/?s_cid=596065%20> Umm ... you mean like this in builtins.h? extern Datum numeric_cmp(PG_FUNCTION_ARGS); cheers andrew
"Aaron Spiteri" <azzmosphere@hotmail.com> writes: > I was wandering if there where any intentions of moving some of the > currently internal numeric function prototypes to the Numeric header file. I > believe such functions like cmp_numerics, add_var, etc would come in handy > when writing user defined C code. All the var stuff is internal to the numeric data type operators. What kind of user defined code are you writing? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> Regression test MUST BE bulletproof.
I'm sorry, but this is not, never has been, and never will be an
iron-clad project rule. When you get a failure you are supposed
to inspect it to see if it's a problem.
regards, tom lane
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> By the way is any reason to have work_mem * 1024 "everywhere" when we have unit
> support in GUC?
Well, would you like to be able to set work_mem higher than 4GB on large
machines?
regards, tom lane
Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> Regression test MUST BE bulletproof.
>
> I'm sorry, but this is not, never has been, and never will be an
> iron-clad project rule. When you get a failure you are supposed
> to inspect it to see if it's a problem.
Yes, but when you find it you should fix or report a problem in postgresql or
regression test. Keep it untouched is not good idea. It is what I meant. Try to
look on Apache Derby unit/regression tests.
Zdenek
Tom Lane napsal(a): > Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes: >> By the way is any reason to have work_mem * 1024 "everywhere" when we have unit >> support in GUC? > > Well, would you like to be able to set work_mem higher than 4GB on large > machines? I see, another int64 issues. Thanks Zdenek
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> Tom Lane napsal(a):
>> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>>> Regression test MUST BE bulletproof.
>>
>> I'm sorry, but this is not, never has been, and never will be an
>> iron-clad project rule. When you get a failure you are supposed
>> to inspect it to see if it's a problem.
> Yes, but when you find it you should fix or report a problem in postgresql or
> regression test. Keep it untouched is not good idea.
There are times when it's the most effective answer, though. I'm not
prepared to invest very large amounts of effort to fix corner-case
regression test problems, if those problems don't represent any actual
bug in the software. There are more productive ways to spend our time.
This is especially true if a proposed fix has negative consequences
beyond just the time to implement it --- reducing the scope of test
coverage or making the tests run longer would count as negative
consequences IMHO.
regards, tom lane
On Apr 21, 2008, at 7:25 AM, Peter Eisentraut wrote: > Am Montag, 21. April 2008 schrieb Zdenek Kotala: >> I compiled postgreSQL with 1kB block size and regresion test >> fails. Main >> problem is that output is correct but in different order. See >> attachment. > > This was previously reported: > http://archives.postgresql.org/pgsql-hackers/2006-11/msg00901.php > >> I think affected test should contain order by keyword. > > For previously established reasons, we don't want to add ORDER BY > clauses to > every test that might fail under exceptional circumstances so we > test all > plan types equally. I think very small block sizes are fairly > exceptional, > unless you have a reason up your sleeve why they are a good idea. What if we used the OFFSET 0 trick to force the ordering to occur outside of what we're testing? IE: SELECT * FROM (query we're testing OFFSET 0) ORDER BY blah; -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
I wrote:
> Another possible answer is to change the minimum to be just 64K always.
> I'm not certain that it's really sensible to tie the minimum work_mem to
> BLCKSZ --- I don't think we do anything where work_mem is controlling a
> pool of page buffers, do we?
I've committed this change in HEAD. There's no desire to back-patch it
is there?
regards, tom lane
Tom Lane napsal(a):
> I wrote:
>> Another possible answer is to change the minimum to be just 64K always.
>> I'm not certain that it's really sensible to tie the minimum work_mem to
>> BLCKSZ --- I don't think we do anything where work_mem is controlling a
>> pool of page buffers, do we?
>
> I've committed this change in HEAD. There's no desire to back-patch it
> is there?
Back patch is not necessary. It is important for head, because
--with-blocksize=BLOCKSIZE allows to run buildfarm with different blocksize and
I think users will start to play more with this option.
Zdenek