Hi Guys,

I'm starting work on ADD COLUMN.  I'm going to allow:

* SERIAL, SERIAL8
* DEFAULT
* NOT NULL

etc...

The one big programming difficulty I see is the process of running through
all the existing tuples in the relation the column was added to and
evaluating the default for each row.

I assume that's the correct behaviour?  If they specify a default, the
column should be auto-filled with that default, right?

If someone could give me a really quick example look on how to do this, it'd
be really appreciated and would save me heaps of time...

The trick is that the default clause needs to be actually evaluated, not
just set - eg. nextval('"my_seq"') sort of thing.

I guess the other tricky bit is checking that the default value satisfies
the check constraint...?

Chris


Sat, 23 Nov 2002 15:48:25 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Help with ADD COLUMN
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> The one big programming difficulty I see is the process of running through
> all the existing tuples in the relation the column was added to and
> evaluating the default for each row.

Basically you wanna do an "UPDATE tab SET col = <default expr>".  I'd
suggest letting the existing machinery handle as much of that as
possible.

			regards, tom lane

Sun, 24 Nov 2002 01:28:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Help with ADD COLUMN 
At 03:48 PM 23/11/2002 -0800, Christopher Kings-Lynne wrote:
>I assume that's the correct behaviour?  If they specify a default, the
>column should be auto-filled with that default, right?

Good question. We might want some input from other DBs; Dec RDB default 
existing rows to NULL irrespective of the 'DEFAULT' clause.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Sun, 24 Nov 2002 17:34:22 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Help with ADD COLUMN
On Sun, 2002-11-24 at 11:14, Hannu Krosing wrote:
> On Sun, 2002-11-24 at 08:34, Philip Warner wrote:
> > At 03:48 PM 23/11/2002 -0800, Christopher Kings-Lynne wrote:
> > >I assume that's the correct behaviour?  If they specify a default, the
> > >column should be auto-filled with that default, right?
> > 
> > Good question. We might want some input from other DBs; Dec RDB default 
> > existing rows to NULL irrespective of the 'DEFAULT' clause.
> 
> Also, how would I express a new column with default for which I _want_ 
> that column in old records to be NULL ?

Same way as you do now.  Add the column, then alter in the default.

-- 
Rod Taylor <rbt@rbt.ca>


24 Nov 2002 09:24:45 -0500
Rod Taylor <rbt@rbt.ca>
Re: Help with ADD COLUMN
On Sun, 2002-11-24 at 08:34, Philip Warner wrote:
> At 03:48 PM 23/11/2002 -0800, Christopher Kings-Lynne wrote:
> >I assume that's the correct behaviour?  If they specify a default, the
> >column should be auto-filled with that default, right?
> 
> Good question. We might want some input from other DBs; Dec RDB default 
> existing rows to NULL irrespective of the 'DEFAULT' clause.

Also, how would I express a new column with default for which I _want_ 
that column in old records to be NULL ?

----------------
Hannu




24 Nov 2002 18:14:01 +0200
Hannu Krosing <hannu@tm.ee>
Re: Help with ADD COLUMN
Philip Warner <pjw@rhyme.com.au> writes:
> At 03:48 PM 23/11/2002 -0800, Christopher Kings-Lynne wrote:
>> I assume that's the correct behaviour?  If they specify a default, the
>> column should be auto-filled with that default, right?

> Good question.

No, it's perfectly clear in the spec:

         1) The column defined by the <column definition> is added to T.

         2) Let C be the column added to T. Every value in C is the default
            value for C.

The reason we currently reject DEFAULT in an ADD COLUMN is precisely
that the spec requires the semantics we don't have implemented.
(On the other hand, ALTER COLUMN SET DEFAULT is easy because it's not
supposed to affect existing table rows.)

			regards, tom lane

Sun, 24 Nov 2002 12:35:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Help with ADD COLUMN 

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > The one big programming difficulty I see is the process of running
through
> > all the existing tuples in the relation the column was added to and
> > evaluating the default for each row.
>
> Basically you wanna do an "UPDATE tab SET col = <default expr>".  I'd
> suggest letting the existing machinery handle as much of that as
> possible.

Using SPI calls?

Chris


Sun, 24 Nov 2002 12:35:46 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Help with ADD COLUMN 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Basically you wanna do an "UPDATE tab SET col = <default expr>".  I'd
>> suggest letting the existing machinery handle as much of that as
>> possible.

> Using SPI calls?

I wouldn't use SPI; it introduces way too many variables --- besides
which, you already have the default in internal form, why would you want
to deparse and reparse it?

I'd look into building a parsetree for an UPDATE statement and
feeding that to the executor.

An interesting question: should the rewriter be allowed to get its hands
on the thing, or not?  I'm not sure it'd be a good idea to fire rules
for such an operation.  For that matter, perhaps we don't want to fire
triggers either --- just how close should this come to being like a regular
UPDATE?

It would probably net out to not a lot of code to do a heapscan,
heap_modify_tuple, etc if we decide that not firing rules/triggers
is more appropriate behavior.  I'm not sure.

			regards, tom lane

Sun, 24 Nov 2002 16:16:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Help with ADD COLUMN 