Comments (9)
@madelson I can confirm the prepared statement exception has disappeared once I used UseTransaction()
.
from distributedlock.
@madelson Is there any reason why you support pg_advisory_lock()
but not pg_advisory_xact_lock()
? In continuation of our discussion in #175 , I read that with pgbouncer
in transaction mode this is the only way to correctly set up advisory locks for PostgreSQL.
from distributedlock.
@P4rpleSky thanks for the great info. I think we should add transactional pg lock support to the library.
would you or @droivan (or anyone else) be interested in contributing?
from distributedlock.
@tiago156 this is an interesting issue and not one I was aware of. Suporting this will required a change to the library. A few questions:
- What is the error thrown when this happens?
- Can you point to any pgbouncer/Npgsql docs describing this issue and how to deal with it?
- Is there any way that
PosgresDatabaseConnection
could detect that pgbouncer is used (e.g. by inspecting the connection string)?
from distributedlock.
@tiago156 have you looked at https://stackoverflow.com/questions/20150503/prepared-statement-doesnt-exist ? Seems like possibly this is fixable with PG configuration?
from distributedlock.
@P4rpleSky im not opposed to adding support for that and in fact we do support the equivalent capability for SQL server.
IIRC the reason I didn’t do it initially was that in SQL server I had bad experiences with transaction-based locks. At least on that database keeping transactions open for a long time can cause issues like blocking the transaction log from being flushed. I have no idea if the same is true for Postgres (of course, not all applications need to hold distributed locks for a long time).
can you point me to the resource where you were reading about using transaction-based locks with pg_bouncer?
from distributedlock.
@madelson Here are some useful links:
- https://www.pgbouncer.org/features.html
- https://blog.devgenius.io/what-are-postgres-advisory-locks-and-their-use-cases-71ace601e06b
- https://prog.world/synchronizing-applications-using-advisory-locks-postgresql-what-is-it-why-and-the-nuances-of-working-with-pgbouncer/
The problem with prepared statements is just the beginning. My comment above is related to this type of problem: if our application unexpectedly shuts down (or our database connection is closed) while having a session-based lock, we won't be sure that pgbouncer
with transaction pooling won't make us a lock forever. With transaction-based locks, we'll guarantee that even in the situation described above, it will be unlocked because the transaction will end.
Maybe this problem is somehow solved by calling pg_sleep
in your code, but I'm not sure about it. Can you tell me something about it, please? I will be very grateful!
from distributedlock.
Could you please provide an update on whether the issue with accessing the database via PgBouncer has been resolved? Thanks in advance for your assistance.
from distributedlock.
@tiago156 @P4rpleSky @droivan I've implemented transaction-based locking in the upcoming release. Can anyone confirm that we do not need to disable prepared statements (per the original ask)?
from distributedlock.
Related Issues (20)
- Redis timeout on RedLockRelease HOT 3
- Too many UnobservedTaskException HOT 2
- IOException: directory already exists in Ubuntu
- Elaborate on ZooKeeper setup docs HOT 1
- Upgrade to NUnit 4 and leverage new features like Assert.ThatAsync and Assert.Multiple
- Use NUnit.Analyzers to prepare for migration to NUnit 4
- Add additional TFM so the dependency on System.Threading.AccessControl can be removed HOT 2
- Azure Semaphore implementation HOT 1
- Local development and CI using test containers HOT 5
- Dependency on Microsoft.Data.SqlClient 5.2.0 may be unnecessary HOT 3
- Postgres: Add support for transaction-scoped advisory locks with external DB connections HOT 9
- EventWaitHandle support for local namespace HOT 2
- Rare race condition with keepalive after upgrading to Npgsql 8.0.3
- Core Next Minor - normalize locking in DatabaseConnection.CreateCommand()
- Upgrade to Npgsql 8.0.3
- Take advantage of """ strings in various files
- RedisScript<T> whitespace regex doesn't need to consider single whitespace chars
- Single-node in-memory implementation HOT 2
- Sqlite implementation
- Postgres: allow ShouldPrepareCommands to be false to work with npgsql driver Multiplexing
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from distributedlock.