Обсуждение: 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

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