Suppose I run this SQL query:
INSERT into my_table(id, value) VALUES (4,'hey')
This will insert this new row as a heap tuple in PostgreSQL.
To insert this row, the following steps will happen on a high level:
- Acquire ROW EXCLUSIVE LOCK
- Fetch Relation Data from system catalogs table if not cached in relcache.
- Appropriate BlockNumber will be selected for insertion
- The data is written into that Page.
Now for point 3, Postgres uses the method: RelationGetBufferForTuple
In case BulkInsertState is false, postgres gets the block using: the RelationGetTargetBlock method which is essentially:
#define RelationGetTargetBlock(relation)
( (relation)->rd_smgr != NULL ? (relation)->rd_smgr->smgr_targblock : InvalidBlockNumber )
If have questions regarding this:
- The RelationGetTargetBlock method isn’t thread safe, what if multiple writers are using this targblock, and then updating it using the RelationSetTargetBlock method? I see no lock acquired before accessing RelationData or specifically SMgrRelationData
- RelationSetTargetBlock method too isnt thread safe in case of multiple writers.
- If this block is filled already, postgres extends the relation using RelationAddBlocks method, but in case of multiple writers, wont the relation keep getting extended, since lot of inserts might have picked up a stale target block.
I tried inspecting if any locks are acquired to access RelationData but didnt found anything conclusive. Also extending the relation with one more block could potentially extend Relation Blocks by more than 1.
2