From: | Jehan-Guillaume de Rorthais <ioguix(at)free(dot)fr> |
---|---|
To: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Protect a table against concurrent data changes while allowing to vacuum it |
Date: | 2016-06-22 11:09:06 |
Message-ID: | 20160622130906.56cb30cb@firost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Le Wed, 22 Jun 2016 18:07:46 +0800,
Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> a écrit :
> I am running PostgreSQL 9.5.
>
> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);
I guess this is not your definitive definition of the table and you might
have some other fields isn't it ?
I can see multiple way to change this schema that seems broken, but we probably
lack informations to pick the right one...
> The constraint that the data must satisfy is `there is no more than 3
> records with the same name`.
>
> I am not in control of queries that modify the table, so advisory locks
> can hardly be of help to me.
>
> On 06/22/2016 05:20 PM, Sameer Kumar wrote:
> >
> >
> > On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru
> > <mailto:arhipov(at)dc(dot)baikal(dot)ru>> wrote:
> >
> > Hello,
> >
> > 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).
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-06-22 13:30:39 | Re: pg_restore error-s after pg_dump |
Previous Message | Jehan-Guillaume de Rorthais | 2016-06-22 11:03:01 | Re: Protect a table against concurrent data changes while allowing to vacuum it |