Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Albe Laurenz]

Yogesh Sharma wrote:
> I observed there is some problem in REINDEX operation in older PostgreSQL 
> versions.
> That why i want to add explicitly lock.

Which problem?

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] Request to confirm which command is use for exclusive operation

[David G. Johnston]

On Wed, Mar 8, 2017 at 3:42 AM, Yogesh Sharma  wrote:

> I observed there is some problem in REINDEX operation in older PostgreSQL
> versions.
> That why i want to add explicitly lock.
>
>
​You should probably define "current" and "older" in your personal context
- what version(s) are you targeting?

David J.​

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[David G. Johnston]

On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma 
wrote:

> Dear David,
>
>  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> And vice versa.
> So, please let me know this type of handling is possible.
>
>
​You want to exclusively lock a table during every insert just because
something might run a concurrent reindex?

If you want to write a higher-level locking scheme for your system and not
use what is provided natively by PostgreSQL that's your choice.  Its not
worth volunteering my time to help do that, though.  The docs have
considerable info regarding explicit locking using the LOCK command.  You
should start there.

I see in a subsequent response that you have doubts that REINDEX is
actually working properly.  If you are able to demonstrate that maybe the
underlying problem could be addressed.

David J.
​

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear Albe,

Thanks for your support.

I observed there is some problem in REINDEX operation in older PostgreSQL
versions.
That why i want to add explicitly lock.

Regards,
Yogesh

On Wednesday, March 8, 2017, Albe Laurenz  wrote:

> Yogesh Sharma wrote:
> >  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> > And vice versa.
> > So, please let me know this type of handling is possible.
>
> Maybe I misunderstand something, but you don't need to do that because it
> happens automatically.
>
> If you run REINDEX, it will take an ACCESS EXCLUSIVE lock on the index
> before it starts its work.
>
> That means that it has to wait until all earlier INSERTing transactions are
> ended, and all INSERTs that come after the REINDEX will have to wait until
> the REINDEX is done.
>
> The database takes care that the data are consistent, so why would you
> want to do that explicitly?
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Albe Laurenz]

Yogesh Sharma wrote:
>  I want to apply explicitly lock mechanism once inset operation is in 
> progress then REINDEX will wait.
> And vice versa.
> So, please let me know this type of handling is possible.

Maybe I misunderstand something, but you don't need to do that because it
happens automatically.

If you run REINDEX, it will take an ACCESS EXCLUSIVE lock on the index
before it starts its work.

That means that it has to wait until all earlier INSERTing transactions are
ended, and all INSERTs that come after the REINDEX will have to wait until
the REINDEX is done.

The database takes care that the data are consistent, so why would you
want to do that explicitly?

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] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear Scott,

How to handle table with token?
How to implement this?

Regards,
Yogesh

On Wednesday, March 8, 2017, Scott Marlowe  wrote:

> On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe  > wrote:
> > On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma  > wrote:
> >> Dear David,
> >>
> >>  I want to apply explicitly lock mechanism once inset operation is in
> >> progress then REINDEX will wait.
> >> And vice versa.
> >> So, please let me know this type of handling is possible.
> >> Regrds,
> >> Yogesh
> >
> > Create two roles grant / revoke permissions as needed. maybe
> > pg_stat_activity for locks etc
>
> Also you could have a table with a simple token in it etc. active
> process gets token, all other processes wait on it.
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Scott Marlowe]

On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe  wrote:
> On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma  wrote:
>> Dear David,
>>
>>  I want to apply explicitly lock mechanism once inset operation is in
>> progress then REINDEX will wait.
>> And vice versa.
>> So, please let me know this type of handling is possible.
>> Regrds,
>> Yogesh
>
> Create two roles grant / revoke permissions as needed. maybe
> pg_stat_activity for locks etc

Also you could have a table with a simple token in it etc. active
process gets token, all other processes wait on it.


-- 
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] Request to confirm which command is use for exclusive operation

[Scott Marlowe]

On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma  wrote:
> Dear David,
>
>  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> And vice versa.
> So, please let me know this type of handling is possible.
> Regrds,
> Yogesh

Create two roles grant / revoke permissions as needed. maybe
pg_stat_activity for locks etc


-- 
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] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear David,

 I want to apply explicitly lock mechanism once inset operation is in
progress then REINDEX will wait.
And vice versa.
So, please let me know this type of handling is possible.
Regrds,
Yogesh

On Wednesday, March 8, 2017, David G. Johnston 
wrote:

> On Tuesday, March 7, 2017, Yogesh Sharma  > wrote:
>
>> Dear all,
>>
>> Thanks for your support.
>>
>> I need to perfrom INSERT and REINDEX operation exclusively.
>> For example:
>> If REINDEX operation is in progress then INSERT operation will wait  and
>> vice versa.
>>
>> Please let me know if any approach is available.
>>
>>
> The docs describe REINDEX locking mechanics.
>
> https://www.postgresql.org/docs/current/static/sql-reindex.html
>
> That a reindex blocks writes implies the reverse, any writes in progress
> will prevent the reindex from starting.
>
> David J.
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[David G. Johnston]

On Tuesday, March 7, 2017, Yogesh Sharma  wrote:

> Dear all,
>
> Thanks for your support.
>
> I need to perfrom INSERT and REINDEX operation exclusively.
> For example:
> If REINDEX operation is in progress then INSERT operation will wait  and
> vice versa.
>
> Please let me know if any approach is available.
>
>
The docs describe REINDEX locking mechanics.

https://www.postgresql.org/docs/current/static/sql-reindex.html

That a reindex blocks writes implies the reverse, any writes in progress
will prevent the reindex from starting.

David J.

[GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear all,

Thanks for your support.

I need to perfrom INSERT and REINDEX operation exclusively.
For example:
If REINDEX operation is in progress then INSERT operation will wait  and
vice versa.

Please let me know if any approach is available.

Regards,
Yogesh sharma