Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Bill Moran
On Wed, 22 Jun 2016 10:20:38 +
Sameer Kumar  wrote:

> On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov  wrote:
> 
> > I am running PostgreSQL 9.5.
> >
> > CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);
> >
> > 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.
> >
> 
> Define a function which does a count of the rows and if count is 3 it
> return false if count is less it returns true.

An exclusion constraint might be a better solution.

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread John R Pierce

On 6/22/2016 3:07 AM, Vlad Arkhipov wrote:


CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);

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.


select name from t group by name having count(id)>3

will return all names with more than 3 records in a single query...  now 
the question is, what do you want to do with this information ?




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Jehan-Guillaume de Rorthais
Le Wed, 22 Jun 2016 18:07:46 +0800,
Vlad Arkhipov  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  > > 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).


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Jehan-Guillaume de Rorthais
Le Wed, 22 Jun 2016 10:49:13 +,
Albe Laurenz  a écrit :

> Sameer Kumar wrote:
> > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov  wrote:
> >> I am running PostgreSQL 9.5.
> >> 
> >> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);
> >> 
> >> 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.
> > 
> > 
> > Define a function which does a count of the rows and if count is 3 it
> > return false if count is less it returns true.
> > 
> > Use check constraint with this function. I have not tried this so not sure
> > if you can use function with SELECT on same table in CHECK constraint. So
> > test it out first.
> > 
> > If this works, any insert trying to get the 4th record in table would fail.
> 
> You cannot use subqueries in a check constraint:
> 
> ALTER TABLE t
>ADD CONSTRAINT name_count
>   CHECK ((SELECT count(*) FROM t t1 WHERE t1.name = name) <= 3);
> ERROR:  cannot use subquery in check constraint
> 
> > A last resort could be using triggers. But either of these approaches will
> > cause issues if you have high concurrency.
> 
> Yes, triggers is the way to go:
> 
> CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
> $$BEGIN
>IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
>   RAISE EXCEPTION 'More than three values!';
>END IF;
>RETURN NEW;
> END;$$;
> 
> CREATE TRIGGER check_t AFTER INSERT OR UPDATE ON t FOR EACH ROW
>EXECUTE PROCEDURE check_t();
> 
> But be warned that this will only work if all transactions involved use
> the isolation level SERIALIZABLE.
> 
> Otherwise two concurrent INSERTs would not see each other's entry, and the
> triggers would not raise an error even if there are more than three entries
> after COMMIT.

Use advisory locks to be able to use this in any isolation level:

  CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
  $$BEGIN
 PERFORM pg_advisory_xact_lock(hashtext(NEW.name));

 IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
RAISE EXCEPTION 'More than three values!';
 END IF;
 RETURN NEW;
  END;$$;


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Vlad Arkhipov
That is why I need to lock the table before. The transactions are 
running at the READ COMMITTED isolation level.


On 06/22/2016 06:49 PM, Albe Laurenz wrote:

But be warned that this will only work if all transactions involved use
the isolation level SERIALIZABLE.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Albe Laurenz
Sameer Kumar wrote:
> On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov  wrote:
>> I am running PostgreSQL 9.5.
>> 
>> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);
>> 
>> 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.
> 
> 
> Define a function which does a count of the rows and if count is 3 it return 
> false if count is less it
> returns true.
> 
> Use check constraint with this function. I have not tried this so not sure if 
> you can use function
> with SELECT on same table in CHECK constraint. So test it out first.
> 
> If this works, any insert trying to get the 4th record in table would fail.

You cannot use subqueries in a check constraint:

ALTER TABLE t
   ADD CONSTRAINT name_count
  CHECK ((SELECT count(*) FROM t t1 WHERE t1.name = name) <= 3);
ERROR:  cannot use subquery in check constraint

> A last resort could be using triggers. But either of these approaches will 
> cause issues if you have
> high concurrency.

Yes, triggers is the way to go:

CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
   IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
  RAISE EXCEPTION 'More than three values!';
   END IF;
   RETURN NEW;
END;$$;

CREATE TRIGGER check_t AFTER INSERT OR UPDATE ON t FOR EACH ROW
   EXECUTE PROCEDURE check_t();

But be warned that this will only work if all transactions involved use
the isolation level SERIALIZABLE.

Otherwise two concurrent INSERTs would not see each other's entry, and the
triggers would not raise an error even if there are more than three entries
after COMMIT.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Sameer Kumar
On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov  wrote:

> I am running PostgreSQL 9.5.
>
> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);
>
> 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.
>

Define a function which does a count of the rows and if count is 3 it
return false if count is less it returns true.

Use check constraint with this function. I have not tried this so not sure
if you can use function with SELECT on same table in CHECK constraint. So
test it out first.

If this works, any insert trying to get the 4th record in table would fail.

A last resort could be using triggers. But either of these approaches will
cause issues if you have high concurrency.



>
>
> On 06/22/2016 05:20 PM, Sameer Kumar wrote:
>
>
>
> On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov 
> 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).
>
>
>>
>>
>> --
>> Sent via pgsql-general mailing list ( 
>> pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>
>
> --
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Vlad Arkhipov

I am running PostgreSQL 9.5.

CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);

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 > 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).




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com





Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Albe Laurenz
Vlad Arkhipov wrote:
> 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?

Can you describe your check in more detail?
Why don't you use simple unique constraints?

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Sameer Kumar
On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov  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).


>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com


[GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

2016-06-22 Thread Vlad Arkhipov

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?



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general