Open BenoitRanque opened 2 years ago
In transaction pooling mode, prepared statements cannot be used: https://www.pgbouncer.org/features.html
Hence, if transaction pooling mode is desired then prepared statements should be disabled via use_prepared_statements
field in source configuration.
Okay, we do have this mentioned in our DigitalOcean getting started guide: https://hasura.io/docs/latest/graphql/core/deployment/deployment-guides/digital-ocean-one-click.html#connection-pooling
Should we also have this elsewhere probably and also convert this to a discussion so that this can be searchable?
Folks a question: I understand that
if transaction pooling mode is desired then prepared statements should be disabled
But what should the user do, for a typical use case? Given that hasura will use pools internally? In the ticket that lead to this issue being created, the user was connecting to the pool they had created mostly for other software to connect to the database with.
Should we recommend that, where possible, the user connect hasura directly to the db, and use the pool for other software to connect to the database?
@BenoitRanque I think there's some confusion here -- it may be on my side trying to parse the discussion, apologies if so. Anyway, trying to clarify:
graphql-engine's internal connection pooling should handle prepared statements just fine, assuming the database endpoint supports them. Whether or not it tries to use prepared statements depends on the data source setting, e.g. "use_prepared_statements": true
.
So I think what we should recommend to the user is, "if you're running pgbouncer, disabled use_prepared_statements
", which is what @tirumaraiselvan said above.
@robx I was actually wondering if we should recommend that the user not use an external proxy with connection pooling, and instead prefer using hasura's pooling, which, combined with prepared statements, would give better performance than the alternative (an external pool and no prepared statements)
Ah, now I understand, thank you. That's a good question... I'm sure we'd have benchmarked with vs without use_prepared_statements
before but I'm not aware of such numbers. I'll ask for input.
Version Information
Server Version: 2.0.10-cloud.1
Environment
Tested on Cloud, probably an issue on all platforms
What is the expected behaviour?
Hasura should be able to connect to database connection pools. Alternatively, if hasura expects to be connected directly to databases when using prepared statements, it should be documented.
Keywords
Hasura, Cloud, Prepared Statement, Inconsistency, Digital Ocean, PGBouncer
What is the current behaviour?
When a hasura instance that is connected to a database via a PGBouncer connection pool is restarted, the following errors will be shown in console and the instance enters an inconsistent state:
(error text for searchability)
How to reproduce the issue?
Screenshots or Screencast
Any possible solutions?
Connecting to the database directly solves the problem
Can you identify the location in the source code where the problem exists?
The error seems to indicate that hasura is attempting to create a stored procedure that already exists. This leads me to think that stored procedures outlive the hasura connection to the pgbouncer pool, and thus still exist when hasura re-establishes connection with the database.
I do not know if this is a bug of hasura, pgbouncer, or both.