Make all tests pass on PostgreSQL

I running Drupal7 test suite and reporting SQL errors as seen by PostgreSQL 8.3 backend.
Will report later ...

The log files a huge, I am not sure this is the right approach. Running again on seperate tests, starting with database test.

The database test breaks during the last stage (95%):

An error occurred.
/drupal-head/batch?id=11&op=do

Fatal error: Call to undefined method DatabaseSchema_pgsql::_createFieldSql() in /home/jmpoure/drupal/drupal/includes/database/pgsql/schema.inc on line 250

Call Stack:
0.0002 129120 1. {main}() /home/jmpoure/drupal/drupal/index.php:0
0.1998 19324488 2. menu_execute_active_handler() /home/jmpoure/drupal/drupal/index.php:22
0.2098 20368272 3. call_user_func_array() /home/jmpoure/drupal/drupal/includes/menu.inc:398
0.2098 20368392 4. system_batch_page() /home/jmpoure/drupal/drupal/includes/menu.inc:0
0.2108 20500352 5. _batch_page() /home/jmpoure/drupal/drupal/modules/system/system.admin.inc:1900
0.2132 20552392 6. _batch_do() /home/jmpoure/drupal/drupal/includes/batch.inc:45
0.2132 20552392 7. _batch_process() /home/jmpoure/drupal/drupal/includes/batch.inc:117
0.2133 20558288 8. call_user_func_array() /home/jmpoure/drupal/drupal/includes/batch.inc:202
0.2133 20558368 9. _simpletest_batch_operation() /home/jmpoure/drupal/drupal/includes/batch.inc:0
0.3684 34719712 10. DrupalWebTestCase->run() /home/jmpoure/drupal/drupal/modules/simpletest/simpletest.module:413
13.3989 52743232 11. SchemaTestCase->testSchema() /home/jmpoure/drupal/drupal/modules/simpletest/drupal_web_test_case.php:305
13.4525 52816784 12. db_add_field() /home/jmpoure/drupal/drupal/modules/simpletest/tests/schema.test:75
13.4526 52817888 13. DatabaseSchema_pgsql->addField() /home/jmpoure/drupal/drupal/includes/database/database.inc:1968

schema.test line 75 is :
db_add_field($ret, 'test_table', 'test_serial', array('type' => 'int', 'not null' => TRUE, 'default' => 0));

@jmpoure, cf http://drupal.org/node/338586

Thanks, as I am new to the list, I stop posting. You seem to resolve everything using issues ... Sorry for filling the list.

By the way, clean simpletest does not drop the left-over table under PostgreSQL. There are now more than 600 tables in my database ...

Does the "Clean Envoirnment" button work at clearing the tables? If the tests fail somehow, the tables are not cleaned.

I could clean the table using this little pgsql script, which has nothing to do with Drupal:

CREATE OR REPLACE FUNCTION drop_drupal_test()
  RETURNS integer AS
$BODY$
DECLARE
    mytables RECORD;
BEGIN

    create temporary table tempdrop 
    AS SELECT relname from pg_class 
    WHERE relname ilike 'simpletest%'
    and relname <> 'simpletest'
    and relname <> 'simpletest_test_id' 
    and relkind = 'r';

    DROP table tempdrop;

    FOR mytables IN SELECT * FROM tempdrop LOOP
       EXECUTE 'DROP TABLE ' || quote_ident(mytables.relname);
    END LOOP;

    RETURN 1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

As I am new to Drupal, I don't know to what extent Drupal is aware of the table names.

Maybe it would be usefull to store the names of tables in a seperate table
named for example "simpletest_tables" to be able to drop them cleanly.

Database API goes like a charm after Damien patch.
Testing ... Here are the remaining problems:

Test still fails .
Please find attached a PDF with the resulting status.

There seems to be a problem on serial fields, as D7 displays this error several times:
currval of sequence "simpletest29323aggregator_category_cid_seq" is not yet defined in this session

Will investigate later on.
Feel free to open an issue, I am just a newcomer to Drupal.

The error in Search Engine ranking (search.test 317) is very common (group by):
SELECT COUNT(*) FROM (SELECT i.type, i.sid, (? * COALESCE((n.sticky), 0)) AS calculated_score FROM {search_index} i INNER JOIN {search_total} t ON i.word = t.word INNER JOIN {node} n ON n.nid = i.sid WHERE n.status = 1 AND (i.word = ?) AND i.type = ? GROUP BY i.type, i.sid HAVING COUNT(*) >= ?) n1 - Array ( [0] => 10 [1] => rocks [2] => node [3] => 1 ) SQLSTATE[42803]: Grouping error: 7 ERROR: column "n.sticky" must appear in the GROUP BY clause or be used in an aggregate function

This test suite rocks! I can only congratulate you for this work.
I will use debugging tools and execute PHP step-by-step to try to find errors.

Later on. Good night!

I looked at Search module and found this issue:

line 952:
$select = "SELECT SUM(i.score * t.count) AS calculated_score FROM {search_index} i $join WHERE $conditions GROUP BY i.type, i.sid HAVING COUNT(*) >= %d ORDER BY calculated_score DESC";

This query will always fail under PostgreSQL.

The correct query would be:
$select = "SELECT SUM(i.score * t.count) AS calculated_score, i.type, i.sid FROM {search_index} i $join WHERE $conditions GROUP BY i.type, i.sid HAVING COUNT(*) >= %d ORDER BY calculated_score DESC";

My knowledge of D7 internals is not sufficient to provide a patch, because I don't understand (yet) how is used $select.
Can someone fix and submit a patch?

@damien: I would be happy to extract all PostgreSQL surge issues. I tried advanced seach with "PostgreSQL surge*" parameter with no luck. Any idea?
Answer: look for "PostgreSQL surge%". Okay.

See this list : http://drupal.org/project/issues?projects=3060&text=PostgreSQL%20surge&v...

http://drupal.org/node/342503 fixes 4 fails on the system test
http://drupal.org/node/342493 fixes 1 exception in the aggregator tests

both need code review

Great news! PostgreSQL now only has one fail left in core! this particular fail is a bad test that also fails with MySQL on innodb and would have failed on MySQLite if not for a bug in the MySQLite driver that let it pass.

there is an outstanding issue with a patch to fix this #355225: Inconsistant Insert Queries Between Database Drivers

Can anyone confirm that #291026: change E_NOTICE to warning and allow selection of error level introduced test failures on PostgreSQL ? See attached screenshot

I haven't looked at them in detail, but they run fine on MySQL.

I'll take a look at it.

Just tried latest HEAD on PostgreSQL 8.3 and the error handler tests ran fine w/ 23 passes, 0 fails. One interesting thing to note is that your assertion pass of "GET to http://d7p/system-test/trigger-pdo-exception, response is 74 bytes." has a big reponse byte difference from my assertions pass of "GET to http://pgsql.drupalhead.local/system-test/trigger-pdo-exception, response is 3597 bytes."

Hm... this is... interesting. This is either a "feature" or a bug of php 5.3

Response of /system-test/trigger-exception (or trigger-pdo-exception)

MySQL
PHP 5.2: Looks good
PHP 5.3: Looks good

PostgreSQL
PHP 5.2: Looks good
PHP 5.3: Fatal error: Exception thrown without a stack frame in Unknown on line 0

Any idea what could produce this?

Yay for PHP 5.3. #360605: PHP 5.3 Compatibility

This PHP 5.3 issue is a issue for #360605: PHP 5.3 Compatibility, not here as Drupal 7 requirements are PHP 5.1. issues with 5.3 should be carried on over there. Since Postgres 8.3 is passing 100% now, I can happliy mark this issue as fixed :)

Actually D7 requires PHP 5.2, because date handling didn't stop sucking until then.

And WOOHOO! Rock on!