In either case, you should be careful to first understand how stable the data in the column is so you don't miss new changes. However, the application has been working most of the time without high contention locking. The tradeoff here is that we can observe more resources used, and more space allocated to the table. SELECT * FROM pg_stat_statements WHERE query like '%optionB%' We could review stats from this command with following command: After no rows need changes, we can switch the columnsĪLTER /*optionB*/ TABLE PRU DROP COLUMN A ĪLTER /*optionB*/ TABLE PRU DROP COLUMN A1_CHANGED ĪLTER /*optionB*/ TABLE PRU RENAME A1 TO A SELECT COUNT(1) FROM PRU WHERE A1_CHANGED is null SELECT COUNT(1) FROM PRU WHERE A1_CHANGED=true UPDATE /*optionB*/ PRU SET A1=A::INTEGER, A1_CHANGED=true WHERE id IN (SELECT id FROM PRU WHERE A1_CHANGED is null limit 100000) This sentence must be repeated multiple times until all rows were updated Update sentence with limit number or fows to update in single transaction Trigger to take care of ongoing changes from the applicationsĮLSEIF (NEW.a is null and OLD.a is not null) THENĮLSEIF (NEW.a is not null and OLD.a is null) THEN It can be executed over multiple hours or days as needed.ĪLTER TABLE PRU ADD COLUMN A1 INTEGER, ADD COLUMN A1_CHANGED BOOLEAN The advantages of this method is you have more control over the process. You might have to stop your application to perform this type of long running operation.Īnother approach to change the datatype of the column could be to This exclusive lock could generate errors in the application. This method is the easiest one, but could generate high contention due to required exclusive lock for the table. EDIT : This is the solution I ended up taking: ALTER TABLE myschema. SELECT * FROM pg_stat_statements WHERE query like '%optionA%' How can change VARCHAR (32) type to TEXT without lock table, I need continue to push some data in table between the update. We could review stats from the command above with following query: Generate rows until 2M, by looping the following statement:ĪLTER /*optionA*/ TABLE PRU ALTER COLUMN A TYPE INTEGER USING A::INTEGER If you'd like to follow along with an example of this scenario, let's first create a table and generate data for it. Let's say we want to change the type of column A to Integer. In the second column called A we have integer data currently saved as Text type. One is a column called id with type bigserial. ALTER TABLE test ALTER COLUMN id TYPE integer But it returns: ERROR: column id cannot be cast automatically to type integer SQL state: 42804 Hint: Specify a USING expression to perform the conversion. Suppose we have a table PRU with two columns. So let’s try to change the column type to integer. Due to performance and locking reasons, changing a datatype column using ALTER COLUMN can be a long-running operation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |