Does PostgreSQL have complete functional test cases?

hello

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Hi,

Does anyone know whether PostgreSQL has complete functional test cases? And
where can I find them?

Currently, I only find some test cases in "PG_SOURCE/src/tests", but it
seems that they are pretty simple and do not cover a lot of features of
PostgreSQL.

Thanks a lot!
Tianyin

did you see postgresql/src/test/regress/sql ?

Regards

Pavel Stehule

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing? Am I
right?

(I thought they're test cases for regression)

T

On Tue, Nov 6, 2012 at 12:00 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

hello

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Hi,

Does anyone know whether PostgreSQL has complete functional test cases?

And

where can I find them?

Currently, I only find some test cases in "PG_SOURCE/src/tests", but it
seems that they are pretty simple and do not cover a lot of features of
PostgreSQL.

Thanks a lot!
Tianyin

did you see postgresql/src/test/regress/sql ?

Regards

Pavel Stehule

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing? Am I
right?

yes

Thanks again!

t

On Tue, Nov 6, 2012 at 12:24 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing? Am

I

right?

yes

(I thought they're test cases for regression)

T

On Tue, Nov 6, 2012 at 12:00 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

hello

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Hi,

Does anyone know whether PostgreSQL has complete functional test

cases?

And
where can I find them?

Currently, I only find some test cases in "PG_SOURCE/src/tests", but

it

seems that they are pretty simple and do not cover a lot of features

of

PostgreSQL.

Thanks a lot!
Tianyin

did you see postgresql/src/test/regress/sql ?

Regards

Pavel Stehule

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote:

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing? Am I
right?

yes

Those tests are hardly "complete", as in testing every possible input
and output.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

2012/11/7 Bruce Momjian <bruce@momjian.us>:

On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote:

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing? Am I
right?

yes

Those tests are hardly "complete", as in testing every possible input
and output.

sure - but almost all implemented important functionality is covered

Pavel

Hi, Pavel, Bruce,

Thanks for the explanation!

I have another question regarding the regress test suite.

What does the test result mean to the users/administrators? Are they the
basic functions that have to be supported by PG server? Or, they are just
some benchmarks (failure is ok?)

Under certain configuration, I ran the test suite and got several failure
(see the report below). What does it mean to me? The test results show that
"regex", "create_index", "inherit", etc, failed (even "select" failed). But
I still don't know whether these are severe problems to my PG server.

(p.s., in the default configuration, all the test suites are passed.)

Best,
Tianyin

===============================================

test tablespace ... ok
test boolean ... ok
test char ... ok
test name ... ok
test varchar ... ok
test text ... ok
test int2 ... ok
test int4 ... ok
test int8 ... ok
test oid ... ok
test float4 ... ok
test float8 ... ok
test bit ... ok
test numeric ... ok
test txid ... ok
test uuid ... ok
test enum ... ok
test money ... ok
test rangetypes ... ok
test strings ... ok
test numerology ... ok
test point ... ok
test lseg ... ok
test box ... ok
test path ... ok
test polygon ... ok
test circle ... ok
test date ... ok
test time ... ok
test timetz ... ok
test timestamp ... ok
test timestamptz ... ok
test interval ... ok
test abstime ... ok
test reltime ... ok
test tinterval ... ok
test inet ... ok
test macaddr ... ok
test tstypes ... ok
test comments ... ok
test geometry ... ok
test horology ... ok
test regex ... FAILED
test oidjoins ... ok
test type_sanity ... ok
test opr_sanity ... ok
test insert ... ok
test create_function_1 ... ok
test create_type ... ok
test create_table ... ok
test create_function_2 ... ok
test create_function_3 ... ok
test copy ... ok
test copyselect ... ok
test create_misc ... ok
test create_operator ... ok
test create_index ... FAILED
test create_view ... ok
test create_aggregate ... ok
test create_cast ... ok
test constraints ... ok
test triggers ... ok
test inherit ... FAILED
test create_table_like ... ok
test typed_table ... ok
test vacuum ... ok
test drop_if_exists ... ok
test sanity_check ... ok
test errors ... ok
test select ... FAILED
test select_into ... ok
test select_distinct ... ok
test select_distinct_on ... ok
test select_implicit ... ok
test select_having ... ok
test subselect ... ok
test union ... ok
test case ... ok
test join ... FAILED
test aggregates ... FAILED
test transactions ... ok
test random ... ok
test portals ... ok
test arrays ... FAILED
test btree_index ... ok
test hash_index ... ok
test update ... ok
test delete ... ok
test namespace ... ok
test prepared_xacts ... ok
test privileges ... ok
test security_label ... ok
test collate ... ok
test misc ... ok
test rules ... ok
test select_views ... ok
test portals_p2 ... ok
test foreign_key ... ok
test cluster ... ok
test dependency ... ok
test guc ... ok
test bitmapops ... ok
test combocid ... ok
test tsearch ... ok
test tsdicts ... ok
test foreign_data ... ok
test window ... ok
test xmlmap ... ok
test functional_deps ... ok
test advisory_lock ... ok
test json ... ok
test plancache ... ok
test limit ... ok
test plpgsql ... ok
test copy2 ... ok
test temp ... ok
test domain ... ok
test rangefuncs ... ok
test prepare ... ok
test without_oid ... ok
test conversion ... ok
test truncate ... ok
test alter_table ... FAILED
test sequence ... ok
test polymorphism ... ok
test rowtypes ... ok
test returning ... ok
test largeobject ... ok
test with ... ok
test xml ... ok
test stats ... FAILED

========================
9 of 131 tests failed.
========================

On Wed, Nov 7, 2012 at 10:00 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2012/11/7 Bruce Momjian <bruce@momjian.us>:

On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote:

2012/11/6 Tianyin Xu <tixu@cs.ucsd.edu>:

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing?

Am I

right?

yes

Those tests are hardly "complete", as in testing every possible input
and output.

sure - but almost all implemented important functionality is covered

Pavel

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

Hello

2012/11/8 Tianyin Xu <tixu@cs.ucsd.edu>:

Hi, Pavel, Bruce,

Thanks for the explanation!

I have another question regarding the regress test suite.

What does the test result mean to the users/administrators? Are they the
basic functions that have to be supported by PG server? Or, they are just
some benchmarks (failure is ok?)

no, there must not be failure!- it is not benchmark

Under certain configuration, I ran the test suite and got several failure
(see the report below). What does it mean to me? The test results show that
"regex", "create_index", "inherit", etc, failed (even "select" failed). But
I still don't know whether these are severe problems to my PG server.

probably you disable some important internal functionality - this
usually means critical problem, because some documented feature is
broken

look to diff file src/test/regress/regression.diffs - and there you
can see, what doesn't work

Regards

Pavel Stehule

On Thu, Nov 8, 2012 at 10:47 AM, Tianyin Xu <tixu@cs.ucsd.edu> wrote:

Hi, Pavel, Bruce,

Thanks for the explanation!

I have another question regarding the regress test suite.

What does the test result mean to the users/administrators? Are they the
basic functions that have to be supported by PG server? Or, they are just
some benchmarks (failure is ok?)

Under certain configuration, I ran the test suite and got several failure
(see the report below). What does it mean to me? The test results show that
"regex", "create_index", "inherit", etc, failed (even "select" failed). But
I still don't know whether these are severe problems to my PG server.

You have to check the individual log files about what exactly the
differences are.

Sometimes the failures are for spurious reasons, for example the
regression test was assuming rows would be returned in one order, but
they were returned in a different order instead, yet the order is not
meaningful to the correctness.

If you build with a non-default block size, for example, you get many
of these types of failures. Verifying that they are all acceptable is
tedious, and I don't know of an easy way to do it.

Cheers,

Jeff

On 2012-11-08 10:47:18 -0800, Tianyin Xu wrote:

(p.s., in the default configuration, all the test suites are passed.)

What did you change?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Thanks a lot, Pavel, Jeff, Andres!

I just changed the configuration file, postgresql.conf.

Using the default one, all the regress tests are passed (so it should not
be the block size?). But when I changed something, quite a number of tests
are failed.

I looked at the regression.diffs file, but I don't understand what it said.

For example, I have a bunch of things as follows:

***
/home/tianyin/source_code/postgresql-9.2.1/src/test/regress/expected/select.out
2012-09-19 14:47:58.000000000 -0700
---
/home/tianyin/source_code/postgresql-9.2.1/src/test/regress/results/select.out
2012-11-08 11:02:54.306666883 -0800
***************
*** 219,234 ****
SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;
unique1 | unique2 | two | four | ten | twenty | hundred | thousand |
twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 |
string4

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
! 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA |
OOOOxx
1 | 214 | 1 | 1 | 1 | 1 | 1 | 1
| 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA |
OOOOxx
2 | 326 | 0 | 2 | 2 | 2 | 2 | 2
| 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA |
OOOOxx
3 | 431 | 1 | 3 | 3 | 3 | 3 | 3
| 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA |
VVVVxx
- 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4
| 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA |
HHHHxx
5 | 541 | 1 | 1 | 5 | 5 | 5 | 5
| 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA |
HHHHxx
- 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6
| 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA |
OOOOxx
7 | 647 | 1 | 3 | 7 | 7 | 7 | 7
| 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA |
VVVVxx
8 | 653 | 0 | 0 | 8 | 8 | 8 | 8
| 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA |
HHHHxx
! 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9
| 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA |
HHHHxx
(10 rows)

......

*** 268,292 ****
WHERE onek2.unique1 > 980;
unique1 | stringu1
---------+----------
! 981 | TLAAAA
! 982 | ULAAAA
983 | VLAAAA
- 984 | WLAAAA
- 985 | XLAAAA
- 986 | YLAAAA
- 987 | ZLAAAA
- 988 | AMAAAA
989 | BMAAAA
990 | CMAAAA
991 | DMAAAA
! 992 | EMAAAA
993 | FMAAAA
994 | GMAAAA
! 995 | HMAAAA
! 996 | IMAAAA
! 997 | JMAAAA
! 998 | KMAAAA
! 999 | LMAAAA
(19 rows)

And also something like that:

***
/home/tianyin/source_code/postgresql-9.2.1/src/test/regress/expected/join.out
2012-09-19 14:47:58.000000000 -0700
---
/home/tianyin/source_code/postgresql-9.2.1/src/test/regress/results/join.out
2012-11-08 11:02:56.210666929 -0800
***************
*** 2545,2561 ****
( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
USING (qq)
INNER JOIN tenk1 c ON qq = unique2;
! QUERY
PLAN
!
-------------------------------------------------------------------------------------------------------
! Nested Loop
! -> Hash Full Join
! Hash Cond: (COALESCE(a.q1, 0::bigint) = COALESCE(b.q2,
(-1)::bigint))
! -> Seq Scan on int8_tbl a
! -> Hash
! -> Seq Scan on int8_tbl b
! -> Index Scan using tenk1_unique2 on tenk1 c
! Index Cond: (unique2 = COALESCE((COALESCE(a.q1, 0::bigint)),
(COALESCE(b.q2, (-1)::bigint))))
! (8 rows)

SELECT qq, unique1
FROM

***
/home/tianyin/source_code/postgresql-9.2.1/src/test/regress/expected/stats.out
2012-09-19 14:47:58.000000000 -0700
---
/home/tianyin/source_code/postgresql-9.2.1/src/test/regress/results/stats.out
2012-11-08 11:04:15.206668806 -0800
***************
*** 99,105 ****
WHERE st.relname='tenk2' AND cl.relname='tenk2';
?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
! t | t | t | t
(1 row)

  SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
--- 99,105 ----
   WHERE st.relname='tenk2' AND cl.relname='tenk2';
   ?column? | ?column? | ?column? | ?column?
  ----------+----------+----------+----------
!  t        | t        | f        | f
  (1 row)

SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
***************
*** 108,114 ****
WHERE st.relname='tenk2' AND cl.relname='tenk2';
?column? | ?column?
----------+----------
! t | t
(1 row)

  -- End of Stats Test
--- 108,114 ----
   WHERE st.relname='tenk2' AND cl.relname='tenk2';
   ?column? | ?column?
  ----------+----------
!  t        | f
  (1 row)

-- End of Stats Test

======================================================================

On Thu, Nov 8, 2012 at 11:09 AM, Andres Freund <andres@2ndquadrant.com>wrote:

On 2012-11-08 10:47:18 -0800, Tianyin Xu wrote:

(p.s., in the default configuration, all the test suites are passed.)

What did you change?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

On 11/09/2012 03:28 AM, Tianyin Xu wrote:

Thanks a lot, Pavel, Jeff, Andres!

I just changed the configuration file, postgresql.conf.

Using the default one, all the regress tests are passed (so it should
not be the block size?). But when I changed something, quite a number
of tests are failed.

Changed "something". What did you change?

! 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 1 | AAAAAA |
KMBAAA | OOOOxx
1 | 214 | 1 | 1 | 1 | 1 | 1 | 1
| 1 | 1 | 1 | 2 | 3 | BAAAAA |
GIAAAA | OOOOxx
2 | 326 | 0 | 2 | 2 | 2 | 2 | 2
| 2 | 2 | 2 | 4 | 5 | CAAAAA |
OMAAAA | OOOOxx
3 | 431 | 1 | 3 | 3 | 3 | 3 | 3
| 3 | 3 | 3 | 6 | 7 | DAAAAA |
PQAAAA | VVVVxx
- 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4
| 4 | 4 | 4 | 8 | 9 | EAAAAA |
BGBAAA | HHHHxx
5 | 541 | 1 | 1 | 5 | 5 | 5 | 5
| 5 | 5 | 5 | 10 | 11 | FAAAAA |
VUAAAA | HHHHxx
- 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6
| 6 | 6 | 6 | 12 | 13 | GAAAAA |
QLBAAA | OOOOxx
7 | 647 | 1 | 3 | 7 | 7 | 7 | 7
| 7 | 7 | 7 | 14 | 15 | HAAAAA |
XYAAAA | VVVVxx
8 | 653 | 0 | 0 | 8 | 8 | 8 | 8
| 8 | 8 | 8 | 16 | 17 | IAAAAA |
DZAAAA | HHHHxx
! 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9
| 9 | 9 | 9 | 18 | 19 | JAAAAA |
XBAAAA | HHHHxx

This is a context diff. http://en.wikipedia.org/wiki/Diff#Context_format .

Lines beginning with + mean the line is added. Lines with - mean the
line is removed. Lines with ! mean the line is changed.

Here, you can see that two rows are not output that should be, and two
others are output with different-than-expected values; they could've
been swapped with each other, or just have totally unexpected values.
See the expected/ files for the output that should be produced.

--
Craig Ringer

Thanks, Craig,

Yes, I know "context diff". What I don't know is whether + or - some rows
is a big problem, let's say correctness problem. I didn't write the test
cases so I don't know what these test cases are exactly doing.
If you tell me the failure of these test cases are severe and not
acceptable, I'm fine with it. It means these configurations are not
allowed.

For this particular case, I figured out that it's because of the following
settings,

cpu_index_tuple_cost = 2147483647

which assigned a big number to the cpu_index_tuple_cost, affecting the
query planner.

But to me, the configuration settings should not affect the correctness,
right? Because whatever optimizations you do, the results should be the
same (what matters is the performance). And that's why I need testing
before adjusting these values.

T

On Thu, Nov 8, 2012 at 3:29 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

On 11/09/2012 03:28 AM, Tianyin Xu wrote:

Thanks a lot, Pavel, Jeff, Andres!

I just changed the configuration file, postgresql.conf.

Using the default one, all the regress tests are passed (so it should not
be the block size?). But when I changed something, quite a number of tests
are failed.

Changed "something". What did you change?

! 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA |
OOOOxx
1 | 214 | 1 | 1 | 1 | 1 | 1 | 1
| 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA |
OOOOxx
2 | 326 | 0 | 2 | 2 | 2 | 2 | 2
| 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA |
OOOOxx
3 | 431 | 1 | 3 | 3 | 3 | 3 | 3
| 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA |
VVVVxx
- 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4
| 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA |
HHHHxx
5 | 541 | 1 | 1 | 5 | 5 | 5 | 5
| 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA |
HHHHxx
- 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6
| 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA |
OOOOxx
7 | 647 | 1 | 3 | 7 | 7 | 7 | 7
| 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA |
VVVVxx
8 | 653 | 0 | 0 | 8 | 8 | 8 | 8
| 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA |
HHHHxx
! 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9
| 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA |
HHHHxx

This is a context diff. http://en.wikipedia.org/wiki/Diff#Context_format .

Lines beginning with + mean the line is added. Lines with - mean the line
is removed. Lines with ! mean the line is changed.

Here, you can see that two rows are not output that should be, and two
others are output with different-than-expected values; they could've been
swapped with each other, or just have totally unexpected values. See the
expected/ files for the output that should be produced.

--
Craig Ringer

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

On Thu, Nov 8, 2012 at 05:37:22PM -0800, Tianyin Xu wrote:

Thanks, Craig,

Yes, I know "context diff". What I don't know is whether + or - some rows is a
big problem, let's say correctness problem. I didn't write the test cases so I
don't know what these test cases are exactly doing.
If you tell me the failure of these test cases are severe and not acceptable,
I'm fine with it. It means these configurations are not allowed.

For this particular case, I figured out that it's because of the following
settings,

cpu_index_tuple_cost = 2147483647

which assigned a big number to the cpu_index_tuple_cost, affecting the query
planner.

But to me, the configuration settings should not affect the correctness, right?
Because whatever optimizations you do, the results should be the same (what
matters is the performance). And that's why I need testing before adjusting
these values.

We can't test the optimizer is reasonable if you change settings in this
way, so no, I am not surprised it failed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 11/09/2012 09:37 AM, Tianyin Xu wrote:

Thanks, Craig,

Yes, I know "context diff". What I don't know is whether + or - some
rows is a big problem, let's say correctness problem. I didn't write
the test cases so I don't know what these test cases are exactly doing.

The SQL to the test cases is right there in the regress directory,
usually with comments explaining what each test is for.

If you tell me the failure of these test cases are severe and not
acceptable, I'm fine with it. It means these configurations are not
allowed.

It depends on the test. Some are testing whether the optimizer behaves
as expected, choosing a particular plan. Some tests assume rows are
being returned in a particular order, so if you change settings you can
see at a glance that while the test fails the output is actually OK.
Others are correctness tests and must produce exactly the the expected
results.

This is generally clear from the test query and comments.

which assigned a big number to the cpu_index_tuple_cost, affecting the
query planner.

The planner tests are written for a particular configuration, so if you
change the configuration they won't produce the expected results. That's
OK; just make sure other tests are fine.

It'd be nice to split the tests up into clearer groups - "will fail if
planner settings are changed; WARNING", "will fail only if incorrect
result is returned; FATAL" etc. Right now, AFAIK that hasn't been done.

--
Craig Ringer

Ok, I agree that "2147483647" is not a reasonable setting. But what's the
definition of "reasonable"? I just want to study the impact of the setting
so I test the big number first.

Having the setting:

cpu_index_tuple_cost = 10

I still get failures of "create_index", "inherit", "join", "stats".

When you give users the flexibility of configurations, you cannot say all
the values mismatching with your expectations are not allowed. In fact the
system allowed such settings.

T

On Thu, Nov 8, 2012 at 6:10 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Nov 8, 2012 at 05:37:22PM -0800, Tianyin Xu wrote:

Thanks, Craig,

Yes, I know "context diff". What I don't know is whether + or - some

rows is a

big problem, let's say correctness problem. I didn't write the test

cases so I

don't know what these test cases are exactly doing.
If you tell me the failure of these test cases are severe and not

acceptable,

I'm fine with it. It means these configurations are not allowed.

For this particular case, I figured out that it's because of the

following

settings,

cpu_index_tuple_cost = 2147483647

which assigned a big number to the cpu_index_tuple_cost, affecting the

query

planner.

But to me, the configuration settings should not affect the correctness,

right?

Because whatever optimizations you do, the results should be the same

(what

matters is the performance). And that's why I need testing before

adjusting

these values.

We can't test the optimizer is reasonable if you change settings in this
way, so no, I am not surprised it failed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

Thanks, Craig.

That makes sense. Yes, it's quite a number of work to do. :-) I'll take a
look at the comments and code and try to understand it.

T

On Thu, Nov 8, 2012 at 6:28 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

On 11/09/2012 09:37 AM, Tianyin Xu wrote:

Thanks, Craig,

Yes, I know "context diff". What I don't know is whether + or - some
rows is a big problem, let's say correctness problem. I didn't write
the test cases so I don't know what these test cases are exactly doing.

The SQL to the test cases is right there in the regress directory,
usually with comments explaining what each test is for.

If you tell me the failure of these test cases are severe and not
acceptable, I'm fine with it. It means these configurations are not
allowed.

It depends on the test. Some are testing whether the optimizer behaves
as expected, choosing a particular plan. Some tests assume rows are
being returned in a particular order, so if you change settings you can
see at a glance that while the test fails the output is actually OK.
Others are correctness tests and must produce exactly the the expected
results.

This is generally clear from the test query and comments.

which assigned a big number to the cpu_index_tuple_cost, affecting the
query planner.

The planner tests are written for a particular configuration, so if you
change the configuration they won't produce the expected results. That's
OK; just make sure other tests are fine.

It'd be nice to split the tests up into clearer groups - "will fail if
planner settings are changed; WARNING", "will fail only if incorrect
result is returned; FATAL" etc. Right now, AFAIK that hasn't been done.

--
Craig Ringer

--
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

Craig Ringer wrote:

It'd be nice to split the tests up into clearer groups - "will fail if
planner settings are changed; WARNING", "will fail only if incorrect
result is returned; FATAL" etc. Right now, AFAIK that hasn't been done.

Not sure that's enough of an improvement. Really, these tests should
pass whatever the optimizer parameters are set to; instead of checking
the exact order of the result set, they should check that the right
tuples are returned. Except, of course, in those cases that specify
ORDER BY.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Tianyin Xu wrote:

Ok, I agree that "2147483647" is not a reasonable setting. But what's the
definition of "reasonable"? I just want to study the impact of the setting
so I test the big number first.

Please don't top-post.

Those values are not wrong. They just don't match what our current
testing framework expects. Whether the generated plans are sensible or
not is entirely another question; the queries should still return the
same resultsets. Ordering of tuples within the resultset shouldn't
matter, but the test framework is not smart enough to compare them
that way.

When you give users the flexibility of configurations, you cannot say all
the values mismatching with your expectations are not allowed. In fact the
system allowed such settings.

Sure.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services