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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx=9.0.74=10.11.6=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

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

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

Re: [HACKERS] Fast AT ADD COLUMN with DEFAULTs

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx=9.0.74=10.11.6=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

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 >

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

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

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

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

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

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

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

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

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

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

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

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 >

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

[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