It is also possible that you will get a foreign key violation exception on the process inserting into table 2, but you will not get database inconsistency.
Yelai, Ramkumar IN BLR STS worte: > Sent: Wednesday, October 31, 2012 12:40 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Parallel Insert and Delete operation
> > Hi All, > > Please clarify me the following example. > > I have 2 tables > > Table1 - ( it has one primary key and few columns ) > Table2 - ( it has one primary key and few columns. It has one foreign key, which refers table1 > primary key ). > > I have 2 operations, which are written in pl/pgsql procedure. > > Operation1() - Inserts the records to table2 at every hour basis. > Operation2() - Delete the records from Table 1 and Table2 based on the primary key. > > What if both operations are running at the time for the same primary key. > > what I have to take care to run these two operations perfectly without creating inconsistency in > database.
With the foreign key in place there can be no entry in table2 that does not have a corresponding entry in table1.
Concurrency is solved with locks, so one of the concurrent operations might have to wait until the other one is done.
That is handled by the database system automatically.
