Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » RADICORE Installation Issues » Problem using PostgreSQL 9.1
Problem using PostgreSQL 9.1 [message #2840] Tue, 13 December 2011 09:14 Go to next message
roderickm is currently offline  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 Smile
Re: Problem using PostgreSQL 9.1 [message #2842 is a reply to message #2840] Tue, 13 December 2011 15:48 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2352
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.

Re: Problem using PostgreSQL 9.1 [message #2843 is a reply to message #2842] Wed, 14 December 2011 04:30 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2352
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?


Re: Problem using PostgreSQL 9.1 [message #2844 is a reply to message #2843] Wed, 14 December 2011 09:20 Go to previous messageGo to next message
roderickm is currently offline  roderickm
Messages: 4
Registered: December 2011
Junior Member
Hi Tony

I was out of the office today due to a strategic planning session. I'm quite new to php and postgresql which is why I asked for help :-/ I'll let you know if I find anything. Appreciate your help.

Thanks!
Re: Problem using PostgreSQL 9.1 [message #2845 is a reply to message #2844] Wed, 14 December 2011 12:15 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2352
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.


Re: Problem using PostgreSQL 9.1 [message #2847 is a reply to message #2840] Thu, 15 December 2011 00:16 Go to previous messageGo to next message
smithcharles is currently offline  smithcharles
Messages: 1
Registered: December 2011
Location: 1945 Sundance Ln #314, Co...
Junior Member
Here this section shows additional platform-specific issues regarding the installation and setup of PostgreSQL.You can follow the step for all type of PostgreSQL version.Visit this link and get more details on PostgreSOL versions:
www.postgresql.org/docs/9.1/static/installation-platform-not es.html


(spam signature removed)
Re: Problem using PostgreSQL 9.1 [message #2848 is a reply to message #2845] Thu, 15 December 2011 03:16 Go to previous messageGo to next message
roderickm is currently offline  roderickm
Messages: 4
Registered: December 2011
Junior Member
Excellent! Thanks! So far so good Smile
Re: Problem using PostgreSQL 9.1 [message #2849 is a reply to message #2847] Thu, 15 December 2011 04:30 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2352
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)


Re: Problem using PostgreSQL 9.1 [message #2933 is a reply to message #2849] Tue, 20 March 2012 11:31 Go to previous messageGo to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
Apache Version: 2.2.21
PHP Version: 5.3.10
PostgreSQL: 9.1
Radicore: 1.72.0

Could this is still be an issue even after the applying the patch? After fresh install + postgres fix, cannot proceed beyond the msg of the day menu screen. Click any menu item and this error message will appear:

This application has encountered an unrecoverable error

The following has been reported to the administrator:

2012-03-20 15:21:33

Fatal Error: PostgreSQL: ERROR: 42883: function date_sub(timestamp without time zone, interval) 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, .\src\backend\parser\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::timestamp, visibility * '1 day'::interval) <= '2012-03-20'

Error in line 506 of file 'C:\wamp\www\radicore72\includes\dml.pgsql.class.inc'.

Host Info: localhost, Server Version: 9.1.3
Client Encoding: UTF8, Server Encoding: UTF8
Database: RADICORE, Schema: "MENU", PUBLIC

PHP_SELF: /radicore72/menu/menu.php

CURRENT DIRECTORY: C:\wamp\www\radicore72\menu

SERVER_ADDR: 127.0.0.1

SERVER_NAME: localhost

HTTP_HOST: localhost

User Id: MGR

REMOTE_ADDR: 127.0.0.1

REQUEST_URI: /radicore72/menu/menu.php?session_name=menu9&selection=d ictionary
Re: Problem using PostgreSQL 9.1 [message #2934 is a reply to message #2933] Tue, 20 March 2012 14:29 Go to previous message
AJM is currently offline  AJM
Messages: 2352
Registered: April 2006
Location: Surrey, UK
Senior Member
Try loading in the date_sub() function which can be found in file 'radicore/dict/sql/postgresql/mysqlcompat-1.0b3/datetime.sql '.

Previous Topic: Login Error
Next Topic: Class 'radicore_view' not found
Goto Forum:
  


Current Time: Mon Jul 15 08:28:42 EDT 2024

Total time taken to generate the page: 0.01481 seconds