PostgreSQL surge #1: make simpletest works again

Hmmm, didn't we get rid of all t() in .install files no ? see http://drupal.org/node/332123 - however, it seems like block.install wasn't fully done ..

Subscribing. Will help test these surges shortly.

I can confirm that t()s in hook_schema() definitions are a bug that was fixed in #332123: Remove t() from all schema descriptions. So is this patch still necessary?

Ok, so according to http://drupal.org/node/337146 we do have a pgsql community out there who would rather us not rip out pgsql support. So, folks. We need at least one or two of you testing this patch so this can get in.

Not everything ran so far (see #338586: PostgreSQL surge #4: fix undefined function call)
what I got are 2 exceptions untill I got the error

• Import feeds from OPML functionality
  INSERT INTO simpletest362640aggregator_category (cid, title, description) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2) - Array ( ) SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of sequence "simpletest362640aggregator_category_cid_seq" is not yet defined in this session
  (after 'No reserved message at login page')
• Blog api test
  INSERT INTO simpletest (test_id, test_class, status, message, message_group, function, line, file) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7) - Array ( ) SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "t"
  (after Valid HTML found on "http://sub.drupal7/admin/content/taxonomy/2/add")

I did not check-out the code, but here are the common mistakes when running MySQL code under PostgreSQL:

1) Use of reserved keywords

CREATE TABLE foo (
is varchar,
...
)

is a reserved keyword. It could be written "is"
but this is not a suitable solution as there is no garantee that the SQL will run anywhere.

So we should avoid using SQL99 reserved keywords.
The only way to know if a word is reserved is to use PostgreSQL, MySQL will not complain.

2) SELECT sum(foo) bar, foo2, bar2 FROM table
will fail. Need to write SELECT sum(foo) AS bar, foo2, bar2 FROM table
I have seen this issue at least 10 times today in D6 modules.

3) PostgreSQL has strong logging possibilities PLUS statistics (at column, table, view, etc .. ) levels

PostgreSQL query logger is independant from the code.
So it does not slow down the server and I always log my queries, including execution time.

So looking at the query log, you should be able to understand why it stopped.
I will make a screencast tomorrow showing how to debug SQL queries server-side.

I have spent time today looking at this patch.

Setup: Debian Etch vserver running on Debian Etch host. PHP 5.2.0-8+etch13, PostgreSQL 8.1.11, Apache2, 2.2.3-4+etch5

1. Got the latest drupal from CVS as per http://drupal.org/node/320
2. Create a new Postgres user, create a new postgres database with that user as the owner.
3. Installed Drupal, no issues
4. Enabled Simpletest
5. Tried to run tests. Encountered issue:

[Tue Nov 25 10:03:26 2008] [notice] child pid 26222 exit signal Segmentation fault (11)

6. Changed php conf.d folder so curl.ini loaded last, after all the pgsql files. Not sure if this was necessary.
7. Installed this patch as per http://drupal.org/node/60108
8. Increased max execution time and allowed memory in php
9. Ran some tests successfully.
10. Ran all tests until the following PHP Fatal:

Call to undefined method DatabaseSchema_pgsql::_createFieldSql() in /home/ben/projects/drupal/cvs/htdocs/includes/database/pgsql/schema.inc on line 250

The summary got as far as "2241 passes, 100 fails, and 30 exceptions". There are still issues but at least the tests run.

Small update + correction to my comment #10:

1. I appear to not need the conf.d change from point 6, however others in IRC do and it didn't hurt to have it.
2. I am running etch vservers on a Debian lenny host, not etch, which may impact 1. but I am not sure how.

I can confirm I really need to reorder the loading of the php modules (running xubuntu here), so curl.so *after* pgsql.so modules. After that, no more segfaults for me running tests in the browser or cli.

Some more exceptions
Forum test:
INSERT INTO simpletest (test_id, test_class, status, message, message_group, function, line, file) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7) - Array ( ) SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "t"
in database.inc 72 DatabaseConnection_pgsql->query()
(after Valid HTML found on "http://sub.drupal7/admin/build/forum/add/container")

Search engine ranking:
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
in database.inc 72 DatabaseConnection_pgsql->query()
(after Valid HTML found on "http://sub.drupal7/node/12")

Top visitor blocking:
INSERT INTO simpletest (test_id, test_class, status, message, message_group, function, line, file) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7) - Array ( ) SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "s" in statistics.test
(after Valid HTML found on "http://sub.drupal7/user" )

After this, the testing in the browser completely failed on registry, running them again now on cli with even more memory.

Note, you need the patch in comment #5 on http://drupal.org/node/338586 to get pass the database test, however, #3 is better, because that makes the schema api test fail, my patch makes it work, but that doesn't mean it's fixed :)

These are PHP error codes. It would be usefull to turn on debugging in PostgreSQL backend. It will display easier error information.
In: /etc/postgresql/8.3/main/postgresql.conf

Change the following values:

logging_collector = on 
log_duration = on
log_directory = 'pg_log'

Then restart PostgreSQL:
/etc/init.d/postgresql-8.3 restart

Now check data_directory in /etc/postgresql/8.3/main/postgresql.conf
My data directory is /home/jmpoure/postgresql/8.3/main
On normal systems, it should be /usr/var/postgresql/8.3/main or the like

The logs are in:
whater_data_dir/postgresql/8.3/main/pg_log/

You should be able to read a clear error statement.
This should display 30 error statements at least.

Feel free to report the exact SQL queries and I will try to evaluate how to fix them.
Sorry if I did not have the time to join IRC or install from CVS.

I propose to make a collection of common SQL queries not executing well under PostgreSQL and SQL99, so that Drupal hackers can avoid them.

As an example, here is the kind of error statement I get when debugging Drupal 6 modules:

2008-11-25 14:16:21 CET ERREUR:  erreur de syntaxe sur ou près de « LEFT » au caractère 44
2008-11-25 14:16:21 CET INSTRUCTION :  DELETE FROM l10n_community_error AS e
             LEFT JOIN l10n_community_release AS r ON e.rid = r.rid
             WHERE r.pid = 1
2008-11-25 14:16:21 CET ERREUR:  erreur de syntaxe sur ou près de « , » au caractère 40
2008-11-25 14:16:21 CET INSTRUCTION :  DELETE FROM l10n_community_project AS p, l10n_community_release AS r, l10n_community_file AS f, l10n_community_line AS l
             INNER JOIN p ON p.pid = r.pid
             INNER JOIN f ON r.rid = f.rid
             INNER JOIN l ON f.fid = l.fid
             WHERE p.pid = 1

You can then ask for help in this thread and I will try to do my best withing hours.

@serenecloud : it would make sense to add a minimum requirement of PostgreSQL 8.3. Why?
1=> PostgreSQL is evolving rapidly, like any project with a large developer community. 8.1 is ancien. In 8.3, you will benefit from full-text search for example.
2=> Users and hosting companies can easily migrate from PostgreSQL 8.1 to 8.3.
Just my 2 cents.

I finaly tested Drupal 7 with patch and full debugging.
The only warnings during installation is that some backslashes are escaped using another backslash (i.e. \\)

PostgreSQL warns that this is a temporary support which will be removed in the future.
In place, we should write E'\\'.

Again this must be an ISO issue, that is respected by PostgreSQL. Sorry!
This is probably a database abstraction issue in Drupal.

I am running the 136 testes. Will attach PostgreSQL log shortly. Stay tuned.

Okay. Thanks.

Ok, no problem. Simpletests on postgres start running with this patch, so RTBC.

webchick has been reviewing this so I'll leave it up to her to drive this one home.

Cool! Committed. Yay for removing infinite loops. ;)

I found and filed patch for two more bugs :

taxonomy_term_save: http://drupal.org/node/341872
which I don't think actually worked to begin with, but passed mysql tests

file_space_used: http://drupal.org/node/341910
this one makes better SQL.