CakePHP2 - Postgres driver without getColumnMeta and special escaping support
This driver was changed in two ways:
- The default CakePHP 2.x Postgres driver uses
getColumnMetato infer column types from the server. Although the PHP part has been optimized in recent years , it still incurs an overhead to hit the Postgres database on every call with a query like
SELECT RELNAME FROM PG_CLASS WHERE OID=...
Thus, this implementation is born which foregoes any use of the meta
data and simply uses
- There's a problem with special crafted SQL statements which contain the
\character  which actually isn't CakePHP specific but a problem of the underlying PDO/PgSQL driver .
\Postgres::value() was overriden to apply the special C-style
escape operation to strings .
- Add the line
"mfn/cakephp2-postgres-no-meta": "^0.0.2"to your
php composer.phar require mfn/cakephp2-postgres-no-meta
- Load the plugin in
app/Config/bootstrap.phpwith the line
- Use the driver in your
During the switch of a big application from MySQL to Postgres it was discovered that much overhead was lost on Postgres and it was finally discovered that these meta queries incur a measurable overhead.
The individual queries are very fast but, depending on your queries, they may add up until a measurable point.
In our case there were performance improvement of up to 50% without any additional changes except activating this class. YMMV.
A little bit later also found problems with the generated SQL statements, which in special cases were translated from:
INSERT INTO models(field) VALUES('\'':1');
INSERT INTO models(field) VALUES('\''$1');
before sent to the server, causing various problems.