[Info-ingres] Compression weighs heavy on my defaults.

[Martin Bowes]

We have seen some other issues with alter table, but by and large it's been very useful to us.

I've raised a bug on this issue.

From: Paul White [mailto:paul.white at shift7solutions.com.au]
Sent: 17 October 2017 09:43
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Compression weighs heavy on my defaults.

I avoid alter table .. add column.


From: info-ingres-bounces at lists.planetingres.org<mailto:info-ingres-bounces at lists.planetingres.org> [mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Martin Bowes
Sent: Tuesday, 17 October 2017 6:30 PM
To: info-ingres at lists.planetingres.org<mailto:info-ingres at lists.planetingres.org>
Subject: [Info-ingres] Compression weighs heavy on my defaults.

Hi All,

II 10.2.0 (a64.lnx/100) +p15162

If we alter table add column specifying the column default then we expect the existing rows in the table to all have the specified default as their entry. And that seems to be the case...unless the table has compression in which case the data default seems to be used instead. Any new data added to the table will use the correct default.

For example...
create table test_alter(a integer4 not null with default) with nojournaling, page_size=8192;

insert into test_alter values(1), (2), (3);

modify test_alter to btree unique on a with compression;

alter table test_alter add column b integer4 not null with default -1;
modify test_alter to reconstruct;

select * from test_alter;

┌─────────────┬─────────────┐
│a            │b            │
├─────────────┼─────────────┤
│            1│            0│
│            2│            0│
│            3│            0│
└─────────────┴─────────────┘
(3 rows)

insert into test_alter(a) values (4);
select * from test_alter;

┌─────────────┬─────────────┐
│a            │b            │
├─────────────┼─────────────┤
│            1│            0│
│            2│            0│
│            3│            0│
│            4│           -1│
└─────────────┴─────────────┘
(4 rows)

The same problem occurs if I'd used compression = (nokey, data).

If you run the above but this time with no compression then the final select will show the expected data:
┌─────────────┬─────────────┐
│a            │b            │
├─────────────┼─────────────┤
│            1│           -1│
│            2│           -1│
│            3│           -1│
│            4│           -1│
└─────────────┴─────────────┘
(4 rows)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20171017/b9ea6a74/attachment.html>