tag:blogger.com,1999:blog-7476449567742726187.post7753856029010623214..comments2023-12-18T12:25:52.296-08:00Comments on Database Soup: ALTER TABLE and downtime, part IJosh Berkushttp://www.blogger.com/profile/09671139717468724246noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-7476449567742726187.post-49932171715947462962014-09-25T18:03:57.936-07:002014-09-25T18:03:57.936-07:00It does, but in this case it will be very fast bec...It does, but in this case it will be very fast because it doesn't need to update the table.grayhemphttps://www.blogger.com/profile/10468769612315616319noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-2072867257709399842014-09-23T15:33:06.131-07:002014-09-23T15:33:06.131-07:00thank you so much for taking the time to write thi...thank you so much for taking the time to write this comment. does that SET NOT NULL require an exclusive lock on the table? I'm using version 9.1Alexandros Bourantashttps://www.blogger.com/profile/00773567313392353396noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-45995054100479547832013-11-08T10:31:44.571-08:002013-11-08T10:31:44.571-08:00Ah, just realized that you published part 2 regard...Ah, just realized that you published part 2 regarding the lock condition!Anonymoushttps://www.blogger.com/profile/16323136227317323131noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-49723685840787473342013-11-08T10:26:57.718-08:002013-11-08T10:26:57.718-08:00Thank you for the write up Josh!
And Sergey, very...Thank you for the write up Josh! <br />And Sergey, very nice too. If I understood it correctly, the key is to make UPDATE run in short amount of time during the deployment. This would be the one way.<br /><br />I would also like to mention that 1st statement in Josh's example:<br /><br /> ALTER TABLE users ADD COLUMN hat_size TEXT DEFAULT 'L'; <br /><br />This will lock the table which makes it even less preferable for aiming no-downtime schema change deployment :-)<br /><br />- Miho<br />Anonymoushttps://www.blogger.com/profile/16323136227317323131noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-73079075234640039242013-11-07T12:24:58.171-08:002013-11-07T12:24:58.171-08:00Just an example of the full "non blocking&quo...Just an example of the full "non blocking" column adding cycle.<br /><br />Creating a column.<br /><br />ALTER TABLE public.comments ADD source_type integer;<br /><br />ALTER TABLE public.comments ALTER source_type SET DEFAULT 0;<br /><br />Creating the assisting index to ease defaults migration.<br /><br />CREATE INDEX CONCURRENTLY i_comments_migration_tmp<br />ON comments (id) WHERE source_type IS NULL;<br /><br />Now migrating the unset defaults by small chunks in shell.<br /><br />PSQL=psql<br />total_updated=0<br />updated=1<br />while [ $updated -gt 0 ]; do<br /> updated=$(($PSQL -X sports_ua <<EOF<br />UPDATE comments SET source_type = 0<br />WHERE id IN (<br /> SELECT id FROM comments<br /> WHERE source_type IS NULL LIMIT 5000);<br />EOF<br /> ) | cut -d ' ' -f 2)<br /> (( total_updated+=updated ))<br /> echo -ne "\r$total_updated"<br />done<br /><br />Dropping the assisting index.<br /><br />DROP INDEX i_comments_migration_tmp;<br /><br />And adding NOT NULL if it is needed.<br /><br />ALTER TABLE public.comments ALTER source_type SET NOT NULL;grayhemphttps://www.blogger.com/profile/10468769612315616319noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-66333717990510867752013-11-07T12:14:38.209-08:002013-11-07T12:14:38.209-08:00This comment has been removed by the author.grayhemphttps://www.blogger.com/profile/10468769612315616319noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-82607176884616608552013-11-07T00:10:09.978-08:002013-11-07T00:10:09.978-08:00Excellent post! Thanks for sharing.Excellent post! Thanks for sharing.Anonymoushttps://www.blogger.com/profile/05683544764949933581noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-36937107720472176112013-11-07T00:02:02.584-08:002013-11-07T00:02:02.584-08:00Very Good
Very Good<br />Anonymoushttps://www.blogger.com/profile/10023826100251507825noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-88769890039492622272013-11-06T09:08:55.288-08:002013-11-06T09:08:55.288-08:00Crisp and concise, Josh. Thank you.Crisp and concise, Josh. Thank you.Brad Wallacehttps://www.blogger.com/profile/00137764482675850746noreply@blogger.com