Installing Elxis 2008.1 on postgreSQL database

[turno]

I get the following errors at Database manager -> Monitor tables:

Bad $rs in rs2html. Connection or SQL invalid. Try using $connection->debug=true;

At Archive manager a get an error and two php warnings:

URI: index2.php?option=com_content&task=showarchive&sectionid=0
Message: ERROR: operator does not exist: character varying = integer LINE 2: INNER JOIN elx_sections s ON s.id::VARCHAR = c.sectionid ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
SELECT c.created_by AS value, u.name AS text FROM elx_content c INNER JOIN elx_sections s ON s.id::VARCHAR = c.sectionid LEFT JOIN elx_users u ON u.id = c.created_by WHERE c.state = '-1' GROUP BY c.created_by, u.name ORDER BY u.name

URI: index2.php?option=com_content&task=showarchive&sectionid=0
Path: /administrator/components/com_content/admin.content.php
Line: 444
array_merge() [function.array-merge]: Argument #2 is not an array

URI: index2.php?option=com_content&task=showarchive&sectionid=0
Path: /includes/Core/elxis.php
Line: 1190
reset() [function.reset]: Passed variable is not an array or object

The most serious bug (still present), however, is that connections to the database are not properly closed: try to monitor your processes while you navigate through your website and you will see more and more postgres daemons. Can you please verify and correct that? That might eventually crash the server...

[datahell]

How to fix this error
Open administrator/components/com_content/content.php file
Go to line 434

Change this:
$query .= "\n INNER JOIN #__sections s ON s.id::VARCHAR = c.sectionid";
To this:
$query .= "\n INNER JOIN #__sections s ON s.id::VARCHAR = c.sectionid::VARCHAR";

Save the file, done.


Elxis uses persistent connections. If you don't want this you can change PConnect to Connect in includes/Core/database.php

$con = $this->_resource->PConnect($host, $user, $pass);

The connection is controlled by adoDB. As I am not a postgreSQL expert if you have any recommendations on making PostgreSQL support better I will be glad to see them.

I just tested the postgre processes on my local windows machine. I see that the number of the processes does not get increased during elxis browsing. in my tests: when I start the postgre server and without visiting elxis I have 8 processes (I think this comes from php.ini, the 8 processes are generated to be ready to serve user requests - due to persistence connection) and when I visit elxis these are, normally, increased to 9 and stay there regardless how many clicks I do.

[turno]

Thanks for clarifying about persistent connections: you're perfectly right  Sorry for mistakenly reporting a bug.

I have fixed the error in the Archive Manager, but not the error in the Database Manager. I have set $connection->debug = true in abodb.inc.php and now I get a more explicit error message:

URI: index3.php?option=com_database&task=monexec&do=tables
Message: ERROR: function pg_catalog.btrim(oid) does not exist LINE 2: on b.relname = 'pg_toast_'||trim(a.relfilenode) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select a.relname as tablename,(a.relpages+CASE WHEN b.relpages is null THEN 0 ELSE b.relpages END+CASE WHEN c.relpages is null THEN 0 ELSE c.relpages END)*8 as size_in_K,a.relfilenode as "OID" from pg_class a left join pg_class b on b.relname = 'pg_toast_'||trim(a.relfilenode) left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index' where a.relname in (select tablename from pg_tables where tablename not like 'pg_%') order by 1

Bad $rs in rs2html. Connection or SQL invalid. Try using $connection->debug=true;

Can this be easily fixed as well?

[datahell]

I saw the error yesterday, and you are absolutely right.
The error derives from the adodb's perfmonitor (includes/adodb/adodb-perf.inc.php) which is an external class and not from Elxis itself.
This feature has nothing to do with Elxis functionality. So you have no problem even if it is not working. You can live without tables monitoring feature, 99.99% of the Elxis users have never even seen this page... Besides this, i will try to fix it.

Elxis 2008.1 works fine with postgreSQL.
Here is my testing enviroment:

DataBase Type     postgres7
Database Description    PostgreSQL 8.3.4, compiled by Visual C++ build 1400
Database Version    8.3.4
Database Host    localhost
Database Name    elxis20081
User    postgres
Raise Error FN    elxisdbHandler
Debug    No