[Erp5-dev] How to make ERP5 work with PostgreSQL?

[Jérome Perrin]

bartek a écrit :
> So, all it takes is a rewrite of all methods in portal_catalog so that 
> they comply with particularities of the Postgres syntax? Sounds ok. If 
> all the z-methods which are in the skins are ok then it seems quite 
> feasible.

There are 3 different uses of MySQL in ERP5:
  * indexing objects and issuing queries with portal_catalog;
  * storing CMFActivity message queues in a persistent, distributable
and high performance way;
  * making stock calculations, this is SimulationTool and Resource_zGet*
methods in erp5_core.
  ( there are also SQL caches, but those are optionnal )

I know that lots of queries uses special mysql semantics for "group by"
clauses.
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

Also, does postgresql supports full text searches ?

What could really be a problem is the reliability of PostgreSQL
connector. We know that ZMySQLDA works very well under high load, I
don't know if it's the case for ZPsycopgDA, ZSQLAlchemyDA or other
postgres connector (are they any other ?).

> I have a prospect client who is a Postgres fanatic, and not using 
> postgresql is a deal-breaker, don't ask me why  :) 

If they just want to be able to use postgresql to query data from ERP5,
it's very easy to add new ZSQL methods in the catalog that uses a
postgresql connection, and configure them to be executed in
catalog_object_list.
This way portal_catalog indexes documents in ERP5 standard tables, and
in some postgresql tables with a structure that would make it easier to
query directly.
This approach is used successfully to store information about persons in 
LDAP.

That said, it would be nice to have a postgresql (or generic SQL) 
storage for catalog, but be prepared to some long debugging nights ;)

Jérome