Re: Want to acquire lock on tables where primary of one table is foreign key on othere

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(at)gmail(dot)com> wrote:
> On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
> <postforabhishek(at)gmail(dot)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 I want those table won't update until there is lock. Is it possible? Becuase postgres is acquiring lock but AccessShare Lock which 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(at)gmail(dot)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 related with table B as a foreign key so automatically I can not insert new row with that foreign key now the primary key of 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 No insertion can be done on table C related to table B primary key.
>

> On 12 Oct 2018, at 8:08, Abhishek Tripathi <postforabhishek(at)gmail(dot)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 key so automatically I can not insert new row with that foreign key on table B that is fine now table C any insertion can be 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 is related 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 exist unless 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.