[Meta] Make ERPAL for Service Providers work with PostgreSQL

Problem/Motivation

Even if Erpal is a lot faster than OpenAtrium, is still heavy on database, due to the inner nature of ERP ( especially with node_access joins)
PostgreSQL rocks with join algorithms ( see http://posulliv.github.io/2012/06/29/mysql-postgres-bench/ ) and performace improvements are visibile also in Devel Queries.

Choose installation method

7.x-2.2 version is using by default the quick installation, which will fails on PostgreSQL, because is based on a predefined MySQL dump database.
Use instead 7.x-2.x-dev with standard default installation.

Temporary core patches

I say temporary, because they will be committed in core

• #1003788-240: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID is an ancient issue from 2010, finally committed in Drupal 7.37. Since Erpal dev is using Drupal 7.34, you need to apply it manually, for the moment
• #998898-33: Make sure that the identifiers are not more the 63 characters on PostgreSQL 998898-63chars-identifier-limit-nomd5-D7.patch. This is already committed in D8 and needs backport to D7
  First was discovered due to SQLSTATE[42P07]: Duplicate table: 7 ERROR: relation "field_revision_field_contractor_ref_field_contractor_ref_target" already exists. Maybe other tables would be affected. I don’t care, because the patch fix them all.
• Another not recommended approach would be : By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. It's not possible to alter this option - it needs to be changed in source file src/include/pg_config_manual.h. Then Postgres needs to be recompiled, data directory initialized with initdb and data restored. Every security and bugfix minor release will then have to be patched and recompiled. This is bad thing to do. http://stackoverflow.com/questions/3836247/how-do-i-change-the-namedatal...

Fixed issues

Even some of them are reported with needs work status, they are stable, but ugly fixes

• #2503431: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "user" LINE 3: user bigint CHECK (user >= 0) NOT NULL default 0, ^ This was intialy reported at http://www.erpal.info/blog/blog/erpal-for-service-providers-installation
• #2503461: ERROR: syntax error at or near "tt" at character 8 STATEMENT: DELETE tt.* FROM tokenauth_tokens tt WHERE NOT EXISTS (SELECT * FROM users u WHERE u.uid=tt.uid)
• #2503479: PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer when creating users on PostgreSQL
• #2506601: Invalid text representation: 7 ERROR: invalid input syntax for integer: "structure"
• #2509358: Erpal search fails on postgresql
• #1851398-27: PDOException:Invalid text representation when attempting to load an entity with a string ID patch is required for #2577947: References dialog relation for erpal tasks to work on pgsql, otherwise the autocomplete list isn't populated

Other issues

In the first place, we considered #2447871: PDOException: SQLSTATE[42S22]: Column not found: 1054 Postgres related. But it was present also on MySQL, fixed by updating Relation Module.

Conclusion

As you can see, it was adventurous to make Erpal work with PostgreSQL, but it worth the effort, because Erpal is a really beautiful piece of software.
Generally, this isn’t Erpal fault, is all about known core issues and contributed modules, which are expected in a large Drupal distribution.
Even there was some tricky issues, PDOException:Invalid text representation is the usual suspect , where MySQL silently fails anyway.
The final result is stable.

We know that we “have killed some kittens”, but we needed to provide fast results with Erpal. This is a reason we have posted anyway... maybe somebody could dive in with nice fixes, easily overcoming initial issues.