You forgot to CC the list. Also, top-posting is generally not appreciated here.
> On Thu, Oct 11, 2018 at 8:26 PM Alban Hertroys <haramrae@gmail.com> wrote:
> On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
> <postforabhishek@gmail.com> wrote:
> > Actually I have acquired a "Select for Update" on a table whose id is refrence as a foreign key on another table So
Iwant those table won't update until there is lock. Is it possible? Becuase postgres is acquiring lock but AccessShare
Lockwhich allow to write on those table How I restrict this.
>
> For what purpose do you want that? What is inadequate about the lock
> that Postgres acquires?
>
> Table locks are very rarely what you want, as it blocks all concurrent
> access to the entire table, while that is only necessary for a few
> rarely used corner cases; a foreign key update is not among those.
> On Fri, Oct 12, 2018 at 10:33 AM Abhishek Tripathi <postforabhishek@gmail.com> wrote:
> Thank you for your response.
>
> I am explaining my situation there is table A on which I have taken a row lock and the primary key of table A is
relatedwith table B as a foreign key so automatically I can not insert new row with that foreign key now the primary
keyof table B is used as foreign key in table C and insertion can be done on table C. I just want to lock table C also
Noinsertion can be done on table C related to table B primary key.
>
> On 12 Oct 2018, at 8:08, Abhishek Tripathi <postforabhishek@gmail.com> wrote:
>
> My bad sorry actually there is updation-
>
> there is table A on which I have taken a row lock and the primary key of table A is related with table B as a foreign
keyso automatically I can not insert new row with that foreign key on table B that is fine now table C any insertion
canbe done on table C. I just want to lock table C also No insertion can be done on table C becuse table C primary key
isrelated to table B as a foreign key of B.
So your tables (simplified) are something like this?:
create table A (id primary key)
create table B (id primary key, a_id references A (id))
create table C (id primary key, b_id references B (id))
And you insert a new value for b_id in C? That's not possible, unless that value exists in table B, which in turn can't
existunless it's a_id exists in table A.
That's how foreign key constraints are designed, you don't need to do manual locking for that to work.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
