Обсуждение: [GENERAL] Request to confirm which command is use for exclusive operation
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
Re: [GENERAL] Request to confirm which command is use for exclusive operation
От
"David G. Johnston"
Дата:
On Tuesday, March 7, 2017, Yogesh Sharma <yogeshraj95@gmail.com> 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.
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
От
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 <david.g.johnston@gmail.com> wrote:
On Wednesday, March 8, 2017, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, March 7, 2017, Yogesh Sharma <yogeshraj95@gmail.com> 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.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
От
Scott Marlowe
Дата:
On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma <yogeshraj95@gmail.com> 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
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 <scott.marlowe@gmail.com> wrote: > On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma <yogeshraj95@gmail.com> 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
От
Yogesh Sharma
Дата:
Dear Scott,
How to handle table with token?
How to implement this?
Regards,
Yogesh
On Wednesday, March 8, 2017, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wednesday, March 8, 2017, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma <yogeshraj95@gmail.com> 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.
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
От
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 <laurenz.albe@wien.gv.at> wrote:
On Wednesday, March 8, 2017, Albe Laurenz <laurenz.albe@wien.gv.at> 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
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
Re: [GENERAL] Request to confirm which command is use for exclusive operation
От
"David G. Johnston"
Дата:
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
От
"David G. Johnston"
Дата:
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.