General developer forum

Databases: Moodle on PostgreSQL 8.4

 
 
Picture of Trevor Johnson
Databases: Moodle on PostgreSQL 8.4
 

Testing using Moodle 1.9 on PostgreSQL 8.4.

Getting errors...


2009-09-15 13:38:56 ESTWARNING: nonstandard use of \' in a string literal at character 499

2009-09-15 13:38:56 ESTHINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').


2009-09-15 13:39:05 ESTWARNING: nonstandard use of \\ in a string literal at character 105

2009-09-15 13:39:05 ESTHINT: Use the escape string syntax for backslashes, e.g., E'\\'.


Are there specific setting that we need for Moodle/PHP/PostgreSQL ???
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Moodle on PostgreSQL 8.4
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
There is a postgres setting that affects how it parses strings in SQL, and the default may well have changed. If you change that setting you should be able to fix things. I can't remember what the setting is, you will have to search the Postgres manual.
 
Average of ratings: -
Picture of Trevor Johnson
Re: Databases: Moodle on PostgreSQL 8.4
 

Hi Tim

Found one setting "standard_conforming_strings", which according to a Blog post is the right one.

Default value is Off, if I set it to On it breaks other bits...


2009-09-15 14:21:02 ESTERROR: unterminated quoted string at or near "'::character varying,1,2,3,4) ORDER BY id" at character 47

2009-09-15 14:21:02 ESTSTATEMENT: SELECT * FROM mdl_hotpot_strings WHERE id IN ('::character varying,1,2,3,4) ORDER BY id


2009-09-15 14:25:51 ESTERROR: syntax error at or near "RI" at character 244

2009-09-15 14:25:51 ESTSTATEMENT: INSERT INTO mdl_question_categories ( ID, NAME, CONTEXTID, INFO, STAMP, PARENT, SORTORDER ) VALUES ( 4, 'Default for RI - 01/09 - AUR30405 Certificate III in Automotive Technology', 187, 'The default category for questions shared in context \'RI - 01/09 - AUR30405 Certificate III in Automotive Technology\'.', 'devlearnonline.rit.tafensw.edu.au+090217050337+n8rPg2', 0, 999 )


There is also talk of "standard_conforming_strings" having a default setting of On in the near future, that maybe something to look forward to...

Do you know of any step by step instruction for Moodle using PostgreSQL.
I have found heaps on MYSQL, some for MSSQL but none for PostgreSQL...

Trevor

Regards
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Moodle on PostgreSQL 8.4
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
That sounds like the right setting. Normally Postgres just works, which is probably why there are few docs wink
 
Average of ratings: -
Picture of Trevor Johnson
Re: Databases: Moodle on PostgreSQL 8.4
 

Okay, will try a clean install to see if I get the same warnings. This initial test build is using Moodle PHP code that was copied from a production server, and it does have a few plugins etc. included, and maybe a some other tweaks to suit MSSQL and web server load balancing, so a clean Moodle PHP may help.

The errors are coming up as a "Warning" in PostgreSQL event logs, Moodle isn't reporting any errors, and that is with the Debug set to "Show errors, Warnings and notices". So they may not be a problem at all.

I will continue to follow this up so I get a better understanding of what is going on.

At MoodlePosium last week (Canberra Australia) they said we need either MYSQL or PostgreSQL for Moodle 2.0. we currently use MSSQL, hence the reason for test PostgreSQL.

Regards

Trevor

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: Moodle on PostgreSQL 8.4
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Moodle 2.0 will still work on MSSQL and Oracle.

It was not working for a long time in Moodle 2.0 dev, because there were a lot of changes to the database layer (in part to make it easier to support more databases) and the MySQL and Postgres-specific parts were done first. Then Eloy got busy with other things. Eloy has just started work on this again, and MSSQL now works, and Oracle is next.
 
Average of ratings: -
Picture of Trevor Johnson
Re: Databases: Moodle on PostgreSQL 8.4
 

Okay, found out the warning messages were exactly that, just warnings.

For Moodle 1.9.5 on PostgreSQL 8.4 you need to set “standard_conforming_strings” to off, which is the default for PostgreSQL 8.4.

And then to turn the event log warnings off, set escape_string_warning to off, the default is on.

Both of these settings can be found in "postgresql.conf".

I have reinstalled a clean weekly build of Moodle 1.9.5 and have had no errors or warning posted in the PostgreSQL event logs, smile.

Also, the guys on the PostgreSQL email list said

"I recommend you inform the authors of the application that they should update it to use standard-conforming string literals. Then, you can turn "escape_string_warning = off" to suppress the warnings while you are waiting for them to fix it."

So who should we be passing this information on to?

Regards

Trevor

 
Average of ratings: -
Picture of Penny Leach
Re: Databases: Moodle on PostgreSQL 8.4
Group Developers
The best thing to do is make a bug in the tracker about it. Can you possibly get the actual query that was causing the problem and add that to the bug as well? It is pretty hard to debug it without that.

You should be able to get that by bumping up the value of log_min_error_statement, and if that doesn't help, changing log_statement to all.
 
Average of ratings: -
One poor developer...
Re: Databases: Moodle on PostgreSQL 8.4
Group DevelopersGroup Documentation writersGroup Moodle HQGroup Particularly helpful MoodlersGroup Testers
Hi,

just as a follow-up to this, take a look to MDL-22189.

Ciao smile
 
Average of ratings: -