Thread: Re: BUG #15446: Crash on ALTER TABLE
On 1/8/19 4:48 PM, Dean Rasheed wrote: > On Tue, 8 Jan 2019 at 19:34, Andrew Dunstan > <andrew.dunstan@2ndquadrant.com> wrote: >> Here's a patch that I think cures the problem. >> > Hmm, that doesn't quite work because the table might not actually be > rewritten as a result of the type change. For example: > > DROP TABLE IF EXISTS foo; > CREATE TABLE foo (a int); > INSERT INTO foo VALUES (1); > ALTER TABLE foo ADD COLUMN b varchar(10) DEFAULT 'xxx'; > ALTER TABLE foo ALTER COLUMN b SET DEFAULT 'yyy'; > INSERT INTO foo VALUES (2); > SELECT * FROM foo; > a | b > ---+----- > 1 | xxx > 2 | yyy > (2 rows) > > ALTER TABLE foo ALTER COLUMN b TYPE varchar(20) USING b::varchar(20); > SELECT * FROM foo; > a | b > ---+----- > 1 | > 2 | yyy > (2 rows) > Ouch, OK, looks like we need something more complex. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 1/8/19 7:41 PM, Andrew Dunstan wrote: > On 1/8/19 4:48 PM, Dean Rasheed wrote: >> On Tue, 8 Jan 2019 at 19:34, Andrew Dunstan >> <andrew.dunstan@2ndquadrant.com> wrote: >>> Here's a patch that I think cures the problem. >>> >> Hmm, that doesn't quite work because the table might not actually be >> rewritten as a result of the type change. For example: >> >> DROP TABLE IF EXISTS foo; >> CREATE TABLE foo (a int); >> INSERT INTO foo VALUES (1); >> ALTER TABLE foo ADD COLUMN b varchar(10) DEFAULT 'xxx'; >> ALTER TABLE foo ALTER COLUMN b SET DEFAULT 'yyy'; >> INSERT INTO foo VALUES (2); >> SELECT * FROM foo; >> a | b >> ---+----- >> 1 | xxx >> 2 | yyy >> (2 rows) >> >> ALTER TABLE foo ALTER COLUMN b TYPE varchar(20) USING b::varchar(20); >> SELECT * FROM foo; >> a | b >> ---+----- >> 1 | >> 2 | yyy >> (2 rows) >> > > Ouch, OK, looks like we need something more complex. > > Here's another attempt. For the rewrite case it kept the logic of the previous patch to clear all the missing attributes, but if we're not rewriting we reconstruct the missing value according to the new type settings. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
On Wed, 9 Jan 2019 at 23:24, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote: > Here's another attempt. For the rewrite case it kept the logic of the > previous patch to clear all the missing attributes, but if we're not > rewriting we reconstruct the missing value according to the new type > settings. > Looks good to me. Regards, Dean