Help with ADD COLUMN

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

"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)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

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 |/

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(at)rbt(dot)ca>

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

Philip Warner <pjw(at)rhyme(dot)com(dot)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

> "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)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

"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)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