> > > Please explain in detail your idea of how it will work. > > OK. I will try to explain the abstract idea, i have. a) Referential integrity gets violated, when there are referencing key values, not present in the referenced key values. We are maintaining the integrity by taking a Select for Share Lock during the foreign key checks, so that referred value is not updated/deleted during the operation.
b) We can do the same in the reverse way. When there is a update/delete of the referred value, we don't want any new inserts with the referred value in referring table, any update that will update its value to the referred value being updated/deleted. So we will take some kind of lock, which will stop such a happening. This can be achieved through i) predicate locking infrastructure already present (or) ii) a temporary B-Tree index ( no WAL protection ), that gets created only for the referred value updations and holds those values that are being updated/deleted (if we are scared of predicate locking). So whenever we de foreign key checks, we just need to make sure there is no such referential integrity lock in our own PGPROC structure(if implemented with predicate locking) / check the temporary B-Tree index for any entry matching the entry that we are going to insert/update to.( the empty tree can be tracked with a flag to optimize ) May be someone can come up with better ideas than this. Gokul.
