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

[Duk Liu]

Hi all
I wanna make ERP5 work with PostgreSQL instead of MySQL.
Which module I should modify?
Is there any entry talk about it I should reference?
Is there anybody done it before?
I have to finish it before next week, can I?

Thanks,
Dan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20070628/4b286bcc/attachment.htm>

[Jacek Medrzycki]

Post by Duk Liu
Hi all
I wanna make ERP5 work with PostgreSQL instead of MySQL.
Which module I should modify?
Is there any entry talk about it I should reference?
Is there anybody done it before?
I have to finish it before next week, can I?

I think You should make a Business Template similar to
erp5_mysql_innodb_catalog, but working with psql.
As far as I know, there is no such bt in public repository. Correct me
if I'm wrong.

J.

[Jean-Paul Smets]

Post by Jacek Medrzycki

Post by Duk Liu
Hi all
I wanna make ERP5 work with PostgreSQL instead of MySQL.
Which module I should modify?
Is there any entry talk about it I should reference?
Is there anybody done it before?
I have to finish it before next week, can I?

I think You should make a Business Template similar to
erp5_mysql_innodb_catalog, but working with psql.

If someone made erp5_pgsql_catalog, it would be nice.

Post by Jacek Medrzycki
As far as I know, there is no such bt in public repository. Correct me
if I'm wrong.

Some people decided to use PostgreSQL instead of MySQL but sadly, they
never contributed anything public.
Please note that when we measured PostgreSQL in the ERP5 context, we
found that inserts were nearly 10 times slower than MySQL inserts (maybe
in other contexts, PostgreSQL is better).

Regards,

JPS.

Post by Jacek Medrzycki
J.
_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://erp5.org/mailman/listinfo/erp5-dev

[bartek]

Post by Jean-Paul Smets

Post by Jacek Medrzycki

Post by Duk Liu
Hi all
I wanna make ERP5 work with PostgreSQL instead of MySQL.
Which module I should modify?
Is there any entry talk about it I should reference?
Is there anybody done it before?
I have to finish it before next week, can I?

I think You should make a Business Template similar to
erp5_mysql_innodb_catalog, but working with psql.

If someone made erp5_pgsql_catalog, it would be nice.

Post by Jacek Medrzycki
As far as I know, there is no such bt in public repository. Correct me
if I'm wrong.

Some people decided to use PostgreSQL instead of MySQL but sadly, they
never contributed anything public.
Please note that when we measured PostgreSQL in the ERP5 context, we
found that inserts were nearly 10 times slower than MySQL inserts (maybe
in other contexts, PostgreSQL is better).

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.

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

Bartek

Post by Jean-Paul Smets
Regards,
JPS.

Post by Jacek Medrzycki
J.
_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://erp5.org/mailman/listinfo/erp5-dev

_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://erp5.org/mailman/listinfo/erp5-dev

[Jean-Paul Smets]

Hi,
Please also note that pgSQL tests we did showed 10 times slower
performance for insert. So, it will work, but I doubt it will be that
fast for reindexing.
JPS.

Post by bartek

Post by Jean-Paul Smets

Post by Jacek Medrzycki

Post by Duk Liu
Hi all
I wanna make ERP5 work with PostgreSQL instead of MySQL.
Which module I should modify?
Is there any entry talk about it I should reference?
Is there anybody done it before?
I have to finish it before next week, can I?

I think You should make a Business Template similar to
erp5_mysql_innodb_catalog, but working with psql.

If someone made erp5_pgsql_catalog, it would be nice.

Post by Jacek Medrzycki
As far as I know, there is no such bt in public repository. Correct me
if I'm wrong.

Some people decided to use PostgreSQL instead of MySQL but sadly, they
never contributed anything public.
Please note that when we measured PostgreSQL in the ERP5 context, we
found that inserts were nearly 10 times slower than MySQL inserts (maybe
in other contexts, PostgreSQL is better).

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.
I have a prospect client who is a Postgres fanatic, and not using
postgresql is a deal-breaker, don't ask me why :)
Bartek

Post by Jean-Paul Smets
Regards,
JPS.

Post by Jacek Medrzycki
J.
_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://erp5.org/mailman/listinfo/erp5-dev

_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://erp5.org/mailman/listinfo/erp5-dev

[Jérome Perrin]

Post by bartek
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 ?).

Post by bartek
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