The new table has a new table OID, but concurrent transaction have already resolved the table name to the OID of the previous table. They will fail either way, since the table they were trying to write to has been deleted from under them. Other transaction (in other sessions) trying to INSERT / UPDATE / DELETE in the same table after your transaction has taken the SHARE lock, will wait until the lock is released or a timeout kicks in, whichever comes first. You would only go that route if you don't have enough free space on disk or cannot afford to lock the whole table or generate errors for concurrent write attempts. Any other method of updating in place has to rewrite the whole table as well, just in a more expensive fashion. , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid) , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() ORDER BY ? - optionally order rows favorably while being at it. SET LOCAL work_mem = '? MB' - just for this transaction ) and recreate them afterwards.ĭo it all in one transaction to avoid incomplete states. It also requires that you delete any object depending on the table type (views, functions using the table type in the signature. Only this last step acquires an exclusive lock on the old table for the rest of the transaction - which should be very short now. When the new table is ready, drop the old and rename the new to make it a drop-in replacement. When updating large portions of a table it is much faster to create indices from scratch than to add rows iteratively. Then add constraints, foreign keys, indices, triggers etc. If you are going to reorder rows, be sure to set work_mem high enough to do the sort in RAM or as high as you can afford (just for your session, not globally). See below.Ĭopy the whole table while populating the new column on the fly - possibly ordering rows favorably while being at it. Attempts to write to the table will wait and eventually fail. Lock the table against concurrent changes in SHARE mode (still allowing concurrent reads). The below code uses a function from the additional uuid-oss module.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |