Cakephp and PGBouncer

Need Help
1

Hi,

I have set up a pgbouncer but my cake is getting and error as if the table is not present.
when I manually login to the pgbouncer I can see the tables.
I cannot make pgbouncer work with cakephp.
It gives me an error as the table X cannot be described.
I checked the port and host, passwords. I can connect to the pgbouncer, but I cannot make it work to get table descriptions etc from the framework.

Has anyone tried to set up PGBouncer with CakePHP?
I am using cake 4

Thanks , Gustavo

2

Does your pgbouncer setup work with any other postgresql client like a basic psql CLI?

3

hello, yes it does. the pgbouncer is working and up. I connect to the port 6432, and manually I can perform SQL statements. But when I modify the config/app_local.php with the connection, Connection Manager does connect to the database, but cannot find the tables. (even being present there).
there is another thing: my cakephp looks for a specific schema. (ie, not the public schema from postgres). Cake shows an error saying it cannot describe the tables.

4

By default cakephp uses the public schema as you can see here:

If your error indicates, that cakephp tries another schema then something else in your app overwrite the connection config.

You can try to debug that class as well like the connect() method to check the actually applied config.

5

Hello,

I can connect to the pgbouncer database that I created (which is just a connection).
But when I try to describe the table, I get an error I cannot describe the database.
Looks like an issue with pgbouncer that cannot provide the same properties as postgres does.
something related to the describe

Cannot describe users. It has 0 columns.

CORE/src/Database/Schema/Collection.php

$table = $this->_connection->getDriver()->newTableSchema($name);

        $this->_reflect('Column', $name, $config, $table);
        if (count($table->columns()) === 0) {
            throw new DatabaseException(sprintf('Cannot describe %s. It has 0 columns.', $name));
        }

If I switch back to connect directly to the postgres, it connects and works as expected.
But when I point the connection to the pgbouncer it fails.

I am trying to use the pgbouncer because I am experiencing that over time, looks like default connection manager (from cake framework) cannot manage connections correctly and starts to fail after a certain period of time.
Is there any efficient way to manage connections under cakephp?
Any configuration that I can modify to avoid get SQL errors from Cake ConnectionManager after a certain period of time (tipically when the number of users surpass 100 per hour).

Thanks

6

What kind of Connection configuration are you currently using?

There is a separate persistent key you can set in your config/app_local.php datasource config to enable persistent connections. Otherwise the one used from its base configuration in the config/app.php app/app.php at 4.x · cakephp/app · GitHub is used.

it usually is set to false because persistent SQL connection can introduce weird problems and the minimal performance boost one can get from it is not worth the hassle.

Therefore each new HTTP request will establish a new PostgreSQL connection, fetch whatever data you request from it inside CakePHP and will close that connection automatically when everything gets deconstructed at the very end of the PHP execution.

If you have that config set to true or not set at all then you will get persistent SQL connections from PHP PDO.

7

I am using the app_local.php config file to set the database connection. I double checked and the configuration persistent variable is set to false.
When I connect directly to the database everything works fine, the problem occur when I change the parameters and connect to the pgbouncer. The connection (->connect()) method from the ConnectionManager works fine, the problem happens when I query something in the database. (for example, If I query the users table, cakephp gives me the error mentioned before, as the table did not exist - but it does exist.).

If you have any other idea on how to mitigate the problem, or which part of the framework I should debug. Please let me know.

Thank you

8

No idea. The fact, that it works fine with a “direct” PostgreSQL connection means nothing is wrong with the framework. Maybe good old google knows something.

9

I have found GitHub - t1nkl/Laravel-PostgreSQL-pgbouncer-Fix: This is fix for Laravel when pgbouncer enabled with parameter 'PDO::ATTR_EMULATE_PREPARES => true' which of course is Laravel specific implementation of the fix.

But it seems like you need to set an extra PDO flag on the connection.
You can set extra flags like described here: app/app.php at 4.x · cakephp/app · GitHub

So try this in your app_local.php

'flags' => [\PDO::ATTR_EMULATE_PREPARES => true]
10

I tried this parameter and did not work :frowning:

app_local.php

‘flags’ => [\PDO::ATTR_EMULATE_PREPARES => true],

11

Just to be sure: You have to put that array key and value inside a “specific place”, not just anywhere in that file.

    'Datasources' => [
        'default' => [
            // Other DB info
            'flags' => [\PDO::ATTR_EMULATE_PREPARES => true],
        ]
    ]
12

Thanks for the tip. I did exactly what you have suggested, but still does not work. Looks like there is some parameter that needs to be passed to PGBouncer to emulate correctly the default postgres environment.
I am still trying to figure out what that is.