Radicore Forum: RADICORE Installation Issues » Problem using PostgreSQL 9.1

Home » RADICORE » RADICORE Installation Issues » Problem using PostgreSQL 9.1

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 Smile

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2842 is a reply to message #2840]

Tue, 13 December 2011 15:48

AJM
Messages: 2382
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

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2843 is a reply to message #2842]

Wed, 14 December 2011 04:30

AJM
Messages: 2382
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

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2844 is a reply to message #2843]

Wed, 14 December 2011 09:20

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!

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2845 is a reply to message #2844]

Wed, 14 December 2011 12:15

AJM
Messages: 2382
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.

Attachment: postgres-fix-2011-12-14.zip
(Size: 53.55KB, Downloaded 1417 times)

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2847 is a reply to message #2840]

Thu, 15 December 2011 00:16

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)

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2848 is a reply to message #2845]

Thu, 15 December 2011 03:16

roderickm
Messages: 4
Registered: December 2011

Junior Member

Excellent! Thanks! So far so good Smile

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2849 is a reply to message #2847]

Thu, 15 December 2011 04:30

AJM
Messages: 2382
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

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2933 is a reply to message #2849]

Tue, 20 March 2012 11:31

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

Report message to a moderator

Re: Problem using PostgreSQL 9.1 [message #2934 is a reply to message #2933]

Tue, 20 March 2012 14:29

AJM
Messages: 2382
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 '.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Sat Jul 05 17:46:33 EDT 2025

Total time taken to generate the page: 0.10252 seconds