To retrieve domain model data from PostgreSQL, I sometimes select against a view rather than a table. I do this so that I can return as much information as possible in one query, rather than fire off multiple queries to resolve all relational dependencies. Mostly this is because there is a 1-to-1 relationship between tables, or they are lookup tables and I just need certain columns, etc. To prevent certain race conditions, one strategy I'm using is
SELECT ... FOR SHARE/UPDATE queries. I'm wondering how these behave when we start using them against views.
Suppose we have two tables
B, each with a column
ID. Now, suppose we have a view
AB that is a join operation between those two tables. Let's assume this is a Cartesian join:
SELECT "A"."ID" AS "AID", "B"."ID" AS "BID" FROM "A" CROSS JOIN "B"
If we then
SELECT FOR UPDATE against
AB what rows of what tables will get locked in the following instances?
SELECT * FROM "AB" WHERE "AB"."AID" = :ID
SELECT * FROM "AB" WHERE "AB"."BID" = :ID
SELECT * FROM "AB" WHERE "AB"."AID" = :ID1 AND "AB"."BID" = :ID2