Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

This feature was added in DB2 year ago. AFAIK it was not very successful. Regular compression techniques proved serve a broader and purpose and save more space. http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0056482.html [http://www.ibm.com/support/k

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/9/16 11:02 PM, Corey Huinker wrote: There's actually another use case here that's potentially extremely valuable for warehousing and other "big data": compact representation of a default value. I too would benefit from tables having either a default value in the event of a NO

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

> > > There's actually another use case here that's potentially extremely > valuable for warehousing and other "big data": compact representation of a > default value. > > I too would benefit from tables having either a default value in the event of a NOT-NULL column being flagged null, or a flat-o

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/6/16 11:01 AM, Tom Lane wrote: Something based on missing_value/absent_value could work for me too. If we name it something involving "default", that definitely increases the possibility for confusion with the regular user-settable default. Also worth thinking about here is that the regula

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/6/16, Serge Rielau wrote: >> On Oct 6, 2016, at 9:20 AM, Tom Lane wrote: >> Vitaly Burovoy writes: >>> But what I discover for myself is that we have pg_attrdef separately >>> from the pg_attribute. Why? >> >> The core reason for that is that the default expression needs to be >> a separat

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

> On Oct 6, 2016, at 9:20 AM, Tom Lane wrote: > > Vitaly Burovoy writes: >> But what I discover for myself is that we have pg_attrdef separately >> from the pg_attribute. Why? > > The core reason for that is that the default expression needs to be > a separate object from the column for purpos

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/6/16, Vitaly Burovoy wrote: > Ough. I made a mistake about pg_attribute because I forgot about the > pg_attrdef. > If we do not merge these tables, the pg_attrdef is the best place to > store evaluated expression as a constant the same way defaults are > stored in adbin. Oops. While I was w

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/6/16, Serge Rielau wrote: >> On Oct 6, 2016, at 9:01 AM, Tom Lane wrote: >> BTW, it also occurs to me that there are going to be good implementation >> reasons for restricting it to be a hard constant, not any sort of >> expression. We are likely to need to be able to insert the value in >

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

Vitaly Burovoy writes: > But what I discover for myself is that we have pg_attrdef separately > from the pg_attribute. Why? The core reason for that is that the default expression needs to be a separate object from the column for purposes of dependency analysis. For example, if you have a column

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/6/16, Tom Lane wrote: > Serge Rielau writes: >>> On Oct 6, 2016, at 5:25 AM, Vitaly Burovoy >>> wrote: Which makes me think we should call this missing_value or absent_value Be honest Simon Rigg's wrote that words. so its clear that it is not a "default" it is the value we use

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

> On Oct 6, 2016, at 9:01 AM, Tom Lane wrote: > > BTW, it also occurs to me that there are going to be good implementation > reasons for restricting it to be a hard constant, not any sort of > expression. We are likely to need to be able to insert the value in > low-level code where general exp

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

Serge Rielau writes: >> On Oct 6, 2016, at 5:25 AM, Vitaly Burovoy wrote: >>> Which makes me think we should call this missing_value or absent_value >>> so its clear that it is not a "default" it is the value we use for >>> rows that do not have any value stored for them. > I like Tom’s “cre

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

> On Oct 6, 2016, at 5:25 AM, Vitaly Burovoy wrote: > > On 10/6/16, Simon Riggs wrote: >> On 6 October 2016 at 04:43, Serge Rielau wrote: > Or should I compose some sort of a design document? >> >> Having read this thread, I'm a little unclear as to what you're >> writing now, though ther

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/6/16, Simon Riggs wrote: > On 6 October 2016 at 04:43, Serge Rielau wrote: Or should I compose some sort of a design document? > > Having read this thread, I'm a little unclear as to what you're > writing now, though there's definitely good ideas here. > > I think it would be beneficia

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 6 October 2016 at 04:43, Serge Rielau wrote: >>> Or should I compose some sort of a design document? Having read this thread, I'm a little unclear as to what you're writing now, though there's definitely good ideas here. I think it would be beneficial to write up a single coherent descriptio

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

> On Oct 5, 2016, at 5:52 PM, Vitaly Burovoy wrote: > > On 10/5/16, Serge Rielau wrote: >> I want to point out as a minor "extension" that there is no need for the >> default to be immutable. It is merely required that the default is evaluate >> at time of ADD COLUMN and then we remember the ac

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Serge Rielau wrote: >On Wed, Oct 5, 2016 at 4:19 PM, Vitaly Burovoy >wrote: >> But what I discover for myself is that we have pg_attrdef separately >> from the pg_attribute. Why? >> Is it time to join them? For not presented defaults it would be only >> one bit per row(if we avoid "a

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.0.74&pv=10.11.6&source=email_footer_2] On Wed, Oct 5, 2016 at 4:19 PM, Vitaly Burovoy wrote: On 10/5/16, Tom Lane wrote: > I wrote: >> Need a better name for the concept, since evidently this name isn't >> conveying the idea. > > May

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Tom Lane wrote: > I wrote: >> Need a better name for the concept, since evidently this name isn't >> conveying the idea. > > Maybe "creation default" would work better? Point being it's the > default value at the time of column creation. Hmm... Personaly for me the original topic nam

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On Thu, Oct 6, 2016 at 12:05 AM, Serge Rielau wrote: > > via Newton Mail > > > On Wed, Oct 5, 2016 at 3:58 PM, Tom Lane wrote: > > Andres Freund writes: > > On 2016-10-05 15:44:56 -0700, Jeff Janes wrote: > >>

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.0.74&pv=10.11.6&source=email_footer_2] On Wed, Oct 5, 2016 at 3:58 PM, Tom Lane wrote: Andres Freund writes: > On 2016-10-05 15:44:56 -0700, Jeff Janes wrote: >>> No, "a second “exist default"" was mentioned, i.e. it is an additional

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

I wrote: > Need a better name for the concept, since evidently this name isn't > conveying the idea. Maybe "creation default" would work better? Point being it's the default value at the time of column creation. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 2016-10-05 18:58:47 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2016-10-05 15:44:56 -0700, Jeff Janes wrote: > >>> No, "a second “exist default"" was mentioned, i.e. it is an additional > >>> column in a system table (pg_attribute) as default column values of > >>> the "pre-alter" era

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Andres Freund wrote: > On 2016-10-05 15:44:56 -0700, Jeff Janes wrote: >> On Wed, Oct 5, 2016 at 3:29 PM, Andres Freund wrote: >> > ALTER TABLE foo ALTER COLUMN withdefault DROP DEFAULT; >> > INSERT id = 1; >> > ALTER TABLE foo ALTER COLUMN withdefault SET DEFAULT 1; >> > ALTER TABLE

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On Wed, Oct 5, 2016 at 11:44 PM, Jeff Janes wrote: > On Wed, Oct 5, 2016 at 3:29 PM, Andres Freund wrote: > >> On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote: >> > On 10/5/16, Andres Freund wrote: >> > > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: >> > >> Dear Hackers, >> > >> I’m wor

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

Andres Freund writes: > On 2016-10-05 15:44:56 -0700, Jeff Janes wrote: >>> No, "a second “exist default"" was mentioned, i.e. it is an additional >>> column in a system table (pg_attribute) as default column values of >>> the "pre-alter" era. It solves changing of the default expression of >>>

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 2016-10-05 15:44:56 -0700, Jeff Janes wrote: > On Wed, Oct 5, 2016 at 3:29 PM, Andres Freund wrote: > > > On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote: > > > On 10/5/16, Andres Freund wrote: > > > > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: > > > >> Dear Hackers, > > > >> I’m wo

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On Wed, Oct 5, 2016 at 3:29 PM, Andres Freund wrote: > On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote: > > On 10/5/16, Andres Freund wrote: > > > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: > > >> Dear Hackers, > > >> I’m working on a patch that expands PG’s ability to add columns to a

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Vitaly Burovoy wrote: > On 10/5/16, Andres Freund wrote: >> On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote: >>> On 10/5/16, Andres Freund wrote: >>> > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: >>> >> Dear Hackers, >>> >> I’m working on a patch that expands PG’s ability to

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Andres Freund wrote: > On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote: >> On 10/5/16, Andres Freund wrote: >> > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: >> >> Dear Hackers, >> >> I’m working on a patch that expands PG’s ability to add columns to a >> >> table >> >> withou

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On Wed, Oct 5, 2016 at 3:23 PM, Vitaly Burovoy wrote: On 10/5/16, Andres Freund wrote: > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: >> Dear Hackers, >> I’m working on a patch that expands PG’s ability to add columns to a table >> without a table rewrite (i.e. at O(1) cost) from the >> null

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote: > On 10/5/16, Andres Freund wrote: > > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: > >> Dear Hackers, > >> I’m working on a patch that expands PG’s ability to add columns to a table > >> without a table rewrite (i.e. at O(1) cost) from the

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Andres Freund wrote: > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: >> Dear Hackers, >> I’m working on a patch that expands PG’s ability to add columns to a table >> without a table rewrite (i.e. at O(1) cost) from the >> nullable-without-default to a more general case. > > If I u

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 2016-10-05 11:58:33 -0700, Serge Rielau wrote: > Dear Hackers, > I’m working on a patch that expands PG’s ability to add columns to a table > without a table rewrite (i.e. at O(1) cost) from the nullable-without-default > to a more general case. E.g. CREATE TABLE T(pk INT NOT NULL PRIMARY KEY)

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On Wed, Oct 5, 2016 at 2:45 PM, Vitaly Burovoy wrote: On 10/5/16, Serge Rielau wrote: > Dear Hackers, > > I’m working on a patch that expands PG’s ability to add columns to a table > without a table rewrite (i.e. at O(1) cost) from the > nullable-without-default to a more general case. E.g. ... >

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

On 10/5/16, Serge Rielau wrote: > Dear Hackers, > > I’m working on a patch that expands PG’s ability to add columns to a table > without a table rewrite (i.e. at O(1) cost) from the > nullable-without-default to a more general case. E.g. > > CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); > INSERT IN

[HACKERS] Fast AT ADD COLUMN with DEFAULTs

Dear Hackers, I’m working on a patch that expands PG’s ability to add columns to a table without a table rewrite (i.e. at O(1) cost) from the nullable-without-default to a more general case. E.g. CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); INSERT INTO T VALEUS (1), (2), (3); ALTER TABLE T ADD C