I have a constraint that requires a table to be locked before checking it (i.e. no more than 2 records with the same value in the same column). If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or autovacuuming) process prevents me from checking the constraint. What are possible solutions?
May be you would like to share-
- Table Structure
- PostgreSQL version
This will help people who would try to help you.
I think you might want to consider an optimistic way of locking your records, instead of locking them. Or look at advisory locks (but that depends on your Postgres version).