Thread: Does PostgreSQL have complete functional test cases?

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

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

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/

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






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

>
> (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/

Thanks again!

t




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

>
> --
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
>
>   + It's impossible for everything to be true. +

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.
========================

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

>
> (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/

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

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

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

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

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

On 11/09/2012 10:36 AM, Alvaro Herrera wrote:
> 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.
Except for cases where the test is verifying that the optimizer chooses
the right plan. Those must fail if different optimizer inputs are
provided. OTOH, maybe those tests should explicitly set the required
optimizer parameters, rather than assuming that the PostgreSQL install's
defaults are what the test wants.

For tests without ORDER BY it'd certainly be nice to sort the output and
the expected result and *then* compare, so simple ordering differences
due to non-deterministic ordering are ignored. That isn't really
possible when the regression tests are just diffs between expected/ and
actual output, though.

--
Craig Ringer

On Fri, Nov  9, 2012 at 10:50:42AM +0800, Craig Ringer wrote:
> On 11/09/2012 10:36 AM, Alvaro Herrera wrote:
> > 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.
> Except for cases where the test is verifying that the optimizer chooses
> the right plan. Those must fail if different optimizer inputs are
> provided. OTOH, maybe those tests should explicitly set the required
> optimizer parameters, rather than assuming that the PostgreSQL install's
> defaults are what the test wants.
>
> For tests without ORDER BY it'd certainly be nice to sort the output and
> the expected result and *then* compare, so simple ordering differences
> due to non-deterministic ordering are ignored. That isn't really
> possible when the regression tests are just diffs between expected/ and
> actual output, though.

I question if the regression tests really warrant this kind of detailed
work.  Not only is it work that could be applied somewhere else, but if
it makes adding new tests harder, it might be a net loss.

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

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

Bruce Momjian wrote:
> On Fri, Nov  9, 2012 at 10:50:42AM +0800, Craig Ringer wrote:

> > > 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.
> > Except for cases where the test is verifying that the optimizer chooses
> > the right plan. Those must fail if different optimizer inputs are
> > provided.

Of course.

> > OTOH, maybe those tests should explicitly set the required
> > optimizer parameters, rather than assuming that the PostgreSQL install's
> > defaults are what the test wants.

That doesn't sound to me as good.  With what I propose (not that I'm
volunteering to implement it) you could test the optimizer just by
running the tests with varying parameters, and ensuring the resultsets
are always the same.

> > For tests without ORDER BY it'd certainly be nice to sort the output and
> > the expected result and *then* compare, so simple ordering differences
> > due to non-deterministic ordering are ignored. That isn't really
> > possible when the regression tests are just diffs between expected/ and
> > actual output, though.
>
> I question if the regression tests really warrant this kind of detailed
> work.  Not only is it work that could be applied somewhere else, but if
> it makes adding new tests harder, it might be a net loss.

I see this issue as similar to the isolation test framework.  There is
no way that the regression test framework could have been used to
implement those tests; yet the new tests were necessary.  Are isolation
tests specs harder to write than plain regression tests?  Sure, they are
somewhat harder.  Could that work have been applied elsewhere?  Of
course (but that choice was Kevin and Heikki's prerogative).  Has it
been worth their effort?  You bet.

If somebody shows up that wants to implement such a thing, I wouldn't
stop them.

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