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

---------- Forwarded message ---------
From: Abhishek Tripathi <postforabhishek@gmail.com>
Date: Thu, Oct 11, 2018 at 3:18 PM
Subject: Want to acquire lock on tables where primary of one table is
foreign key on othere
To: <pgsql-bugs@lists.postgresql.org>

Dear Concerned Person,
I write this email to get the information about locking which I am not
getting from anywhere.
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.

Thanks and Regards
Abhishek

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

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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 Thu, Oct 11, 2018 at 8:26 PM Alban Hertroys <haramrae@gmail.com> wrote:

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