| Problem using PostgreSQL 9.1 [message #2840] |
Tue, 13 December 2011 09:14  |
roderickm
Messages: 4
Registered: December 2011
|
Junior Member |
|
|
Hello Tony
I've installed Radicore on Ubuntu 11.10 using Postgresql 9.1.
I can logon but when I click any of the tabs at the top (e.g. "Menu System") I get the following error:
"
Fatal Error: PostgreSQL: ERROR: 42883: function date_sub(date, smallint) does not exist LINE 4: ...do.user_id='MGR' AND mnu_todo.is_complete='N' AND DATE_SUB(d... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. LOCATION: ParseFuncOrColumn, parse_func.c:304 (# 42883).
SQL query: SELECT count(*) FROM mnu_todo LEFT JOIN mnu_task ON (mnu_task.task_id=mnu_todo.task_id) LEFT JOIN mnu_user ON (mnu_user.user_id=mnu_todo.user_id) WHERE mnu_todo.user_id='MGR' AND mnu_todo.is_complete='N' AND DATE_SUB(due_date, visibility) <= '2011-12-13'
Error in line 497 of file '/var/www/radicore/includes/dml.pgsql.class.inc'.
Host Info: localhost, Server Version: 9.1.1
Client Encoding: UTF8, Server Encoding: UTF8
Database: RADICORE, Schema: "MENU", PUBLIC
PHP_SELF: /radicore/menu/menu.php
CURRENT DIRECTORY: /var/www/radicore/menu
SERVER_ADDR: 127.0.0.1
SERVER_NAME: localhost
HTTP_HOST: localhost
User Id: MGR
REMOTE_ADDR: 127.0.0.1
REQUEST_URI: /radicore/menu/menu.php?session_name=menu3&selection=men u01
"
1. Does Radicore support Postgresql 9.1 or do I have to use an earlier version (e.g. 8.4)?
2. I read the "Creating a Postgresql driver" page pointed to from the installation guide but initially assumed there was nothing to do there. Following this error, I read more carefully and added the functions in "mysqlcompat" to the "radicore" database but I still get the above error. Is there anything else I needed to do from that page?
3. I tried "mysqlcompat-1.04b.zip" from the developers site as it's readme says Postgresql 9.1 support was included and tested but this didn't make a difference.
4. I have confirmed that the function "date_sub" does exist in my database.
5. It would seem like Postgresql cannot match the function as the types are wrong. Expecting: date_sub(timestamp, interval) but (date, smallint) is being passed to it? Perhaps some explicit casting is required?
Before I dig deeper (possibly in the wrong direction) I thought it appropriate to ask you...
Thanks in advance for the help 
|
|
|
|
| Re: Problem using PostgreSQL 9.1 [message #2842 is a reply to message #2840] |
Tue, 13 December 2011 15:48  |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
It does exist in the public schema of the radicore database, which means that it is supposed to be accessible to everybody, jus as are all the other functions. This is how it has always worked in the past, but something must have changed in version 9.1. I've tried granting 'execute' access on all the functions to everybody, but I still can't get it to work. I'm afraid it will take someone with a bit more knowledge of the PostgreSQL permissions system to sort this one out.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Problem using PostgreSQL 9.1 [message #2843 is a reply to message #2842] |
Wed, 14 December 2011 04:30 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I have been trying for several hours to get this working on my PC, and I now realise that it has nothing to do with permissions at all, it is all about casting the column 'visibility', which is an integer, to an interval.
In MySQL I would use "WHERE DATE_SUB(due_date, INTERVAL visibility DAY) <= '$today'"
In previous versions of Postgres the expression "WHERE DATE_SUB(due_date, visibility) <= '$today'" did not cause an error, but now it does.
I have been searching through the Postgres documentation trying to find out how I can cast an integer to an interval inside a function call, but I cannot find anything that works.
I'll keep looking, but if you find an answer first can you let me know?
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: Problem using PostgreSQL 9.1 [message #2845 is a reply to message #2844] |
Wed, 14 December 2011 12:15 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I've managed to change the pgsql class to convert the arguments in the DATE_SUB() function into the correct types, as follows:
WHERE DATE_SUB(due_date::timestamp, visibility * '1 day'::interval) <= '2011-12-14'
I've also had to change the where2indexedArray() function in the library to accept '::' in column names.
The two changed files are in the attached update. See if this fixes your problem.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
| Re: Problem using PostgreSQL 9.1 [message #2849 is a reply to message #2847] |
Thu, 15 December 2011 04:30 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
This is not actually a platform issue, but converting a query that works in MySQL to one that works in PostgreSQL. In this case my code (which was written primarily for MySQL) generates a query containing:
DATE_SUB(due_date, INTERVAL visibility DAY)
Inside dml.pgsql.class.inc this is converted to:
DATE_SUB(due_date::timestamp, (visibility * INTERVAL '1 day')::interval)
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
