tag:blogger.com,1999:blog-7476449567742726187.post7989704744576919417..comments2023-12-18T12:25:52.296-08:00Comments on Database Soup: ALTER TABLE and downtime, part IIJosh Berkushttp://www.blogger.com/profile/09671139717468724246noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-7476449567742726187.post-31988953344100079492016-07-14T14:20:38.382-07:002016-07-14T14:20:38.382-07:00For ALTER operations that still lock reads & w...For ALTER operations that still lock reads & writes (such as adding a column), what are the pros / cons of acquiring a share lock on the table prior to running the ALTER? I.e.:<br /><br />BEGIN; LOCK my_table IN SHARE MODE; ALTER TABLE...<br /><br />I'd think this would lead to a period of time in between acquiring the share lock and acquiring the exclusive lock where writes are piling up, but should guarantee that reads aren't affected (assuming the ALTER statement itself is fast). For read-heavy workloads, presumably this would avoid the problems described in this post?<br />Joshhttps://www.blogger.com/profile/05357239722302350072noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-21281883441066937462016-02-08T02:18:01.600-08:002016-02-08T02:18:01.600-08:00Hello,
sorry for resurrecting this old post, but I...Hello,<br />sorry for resurrecting this old post, but I googled for downtimes on alter column type with 9.3, and this final note was very promising for me:<br />"There is one special case of ALTER TABLE you need to know about in addition to this, though. That's ALTER TABLE ALTER COLUMN ... TYPE . More on this in a succeeding post."<br /><br />Does this post exist? Can it be linked? Searched through your blog but failed.<br />Thank you.Glebhttps://www.blogger.com/profile/09969933652558177771noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-56030304179100449432013-11-12T01:38:11.542-08:002013-11-12T01:38:11.542-08:00Why do you need a high query frequency in order to...Why do you need a high query frequency in order to trigger the problem ? Wouldn't any long-running transaction + hardware pushed to its limits by other queries exhibit the "queue queries, and then take ages to dequeue" symptom ?vdphttps://www.blogger.com/profile/07836622731674773883noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-29451570292474342502013-11-08T15:04:44.486-08:002013-11-08T15:04:44.486-08:00Hm... I thought it is inevitable. Eagerly waiting ...Hm... I thought it is inevitable. Eagerly waiting for the lock-pooling example then.grayhemphttps://www.blogger.com/profile/10468769612315616319noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-2737544885623131432013-11-08T14:56:46.871-08:002013-11-08T14:56:46.871-08:00The problem with that is that it's going to bl...The problem with that is that it's going to block all queries which came in after it for 300ms. I'll post an example lock-polling function later.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-63957026003353368442013-11-08T14:55:50.731-08:002013-11-08T14:55:50.731-08:00Ooops, thanks.Ooops, thanks.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-55442837567724547592013-11-08T14:28:38.233-08:002013-11-08T14:28:38.233-08:00MVCC = Multiversion Concurrency ControlMVCC = Multiversion Concurrency ControlAlexandre Savarishttps://www.blogger.com/profile/15214255778748519416noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-71742244192869360702013-11-08T12:03:04.096-08:002013-11-08T12:03:04.096-08:00BTW, I faced an interesting nuance about NOT NULL ...BTW, I faced an interesting nuance about NOT NULL and inheritence recently. When you add a column to the parent table and then try to SET NOT NULL on the propagated columns in the child tables then it checks all the partitions every time for each partition. However, it you add columns to the child tables directly and then SET NOT NULL on this columns, then it will check the partition itself only for each partition.grayhemphttps://www.blogger.com/profile/10468769612315616319noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-49156738223203890552013-11-08T11:49:52.642-08:002013-11-08T11:49:52.642-08:00> In many cases I get around this by polling fo...> In many cases I get around this by polling for a manual lock on the table, and proceeding with the ALTER once I get one<br /><br />How do you do this exactly?<br /><br />I usually repeat a kind of the block below to work around it.<br /><br />BEGIN;<br />SET LOCAL statement_timeout TO '300ms';<br />ALTER TABLE table1 ADD COLUMN new_column text;<br />END;<br />grayhemphttps://www.blogger.com/profile/10468769612315616319noreply@blogger.com