mfn / cakephp2-postgres-no-meta
A CakePHP2 Postgres database driver without getColumnMeta() support
Installs: 1 630
Dependents: 0
Suggesters: 0
Security: 0
Stars: 8
Watchers: 2
Forks: 4
Open Issues: 0
Type:cakephp-plugin
Requires
- php: >=5.6
- composer/installers: *
README
This driver was changed in the following ways:
- The default CakePHP 2.x Postgres driver uses
getColumnMeta
to infer column types from the server. Although the PHP part has been optimized in recent years [1], it still incurs an overhead to hit the Postgres database on every call with a query likeSELECT RELNAME FROM PG_CLASS WHERE OID=...
[2]
Thus, this implementation is born which foregoes any use of the meta
data and simply uses PDO::FETCH_ASSOC
.
- There's a problem with special crafted SQL statements which contain the
\
character [3] which actually isn't CakePHP specific but a problem of the underlying PDO/PgSQL driver [4].
The method \Postgres::value()
was overriden to apply the special C-style
escape operation to strings [5].
- The default PHP/PDO
lastInsertId
always returns a string. This driver is adapted to return an integer ifis_numeric
returns true on it. This allows easier integration with codebases usingstrict_types=1
.
Requirements and Installation
- You need at least CakePHP 2.10.12
For CakePHP >= 2.0 and < 2.10.12 , you can use version0.0.2
of this package - Add the line
"mfn/cakephp2-postgres-no-meta": "^0.0.6"
to yourapp/composer.json
- Run
php composer.phar require mfn/cakephp2-postgres-no-meta
- Load the plugin in
app/Config/bootstrap.php
with the line
CakePlugin::load('PostgresNoMeta');
- Use the driver in your
app/Config/database.php
:PostgresNoMeta.Database/PostgresNoMeta
(instead ofDatabase/Postgres
) - Profit!
Rational
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');
to
INSERT INTO models(field) VALUES('\''$1');
before sent to the server, causing various problems.