Thread: add column .. default
I took a quick glance at this. It boils down to essentially an: 1) ALTER TABLE .. ADD COLUMN <column> DEFAULT <default> <constraints> 2) UPDATE .. SET <column> = <default> IF <default> IS NOT NULL; 3) Add <constraints> including NOT NULL, CHECK, Foreign Key, etc. each of which will do it's own confirmation pass on the values inserted into the table by step 2 for the table for the new column. If all additional constraints are added after the values are in the table (via standard ALTER TABLE functions), is there anything the update (step 2) needs to check aside from Domain constraints (coerce <default> to datatype of <column>)? For the update I intend to use double space, as if the user did those items as individual commands within the same transaction. Someone can make it more efficient in regards to constraint checks, etc. in the future if they want -- I don't intend to. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
> For the update I intend to use double space, as if the user did those
> items as individual commands within the same transaction.
There is no alternative, unless you want the command to be
non-roll-back-able.
> Someone can
> make it more efficient in regards to constraint checks, etc. in the
> future if they want -- I don't intend to.
It'd be nice if you at least ensure that all the constraints are checked
in a single pass over the table (not one per constraint). Right offhand
I do not see why they couldn't be checked in the same pass that does the
UPDATE. For extra credit, detect that the default expression is
immutable or stable, and do the checks *once* not once per row.
regards, tom lane
> > Someone can > > make it more efficient in regards to constraint checks, etc. in the > > future if they want -- I don't intend to. > > It'd be nice if you at least ensure that all the constraints are checked > in a single pass over the table (not one per constraint). Right offhand > I do not see why they couldn't be checked in the same pass that does the > UPDATE. For extra credit, detect that the default expression is > immutable or stable, and do the checks *once* not once per row. I suppose that can be done. It will take a significant reorganization of the ALTER TABLE functions (or lots of duplicated code), but shouldn't be that time consuming. Anyway, I suppose you have indirectly confirmed that user triggers, etc. should NOT fire on for the data update. I didn't see anything in the spec that said one way or the other. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
> Anyway, I suppose you have indirectly confirmed that user triggers, etc.
> should NOT fire on for the data update. I didn't see anything in the
> spec that said one way or the other.
Actually, I didn't mean to take a position one way or the other. You
could certainly argue that they should fire ...
regards, tom lane
On Thu, 2003-06-19 at 09:40, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > Anyway, I suppose you have indirectly confirmed that user triggers, etc. > > should NOT fire on for the data update. I didn't see anything in the > > spec that said one way or the other. > > Actually, I didn't mean to take a position one way or the other. You > could certainly argue that they should fire ... Do we want them to? If we don't mind them being executed, it is far easier to: - alter table structure - Add all new constraints (without confirming their correctness at that time) - update table contents via an SPI call to UPDATE WHERE <column> IS NULL The where clause would avoid issues with inherited data being overwritten when the child tables are updated. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor writes: > Anyway, I suppose you have indirectly confirmed that user triggers, etc. > should NOT fire on for the data update. I didn't see anything in the > spec that said one way or the other. The spec doesn't say that they fire, so that means that they don't fire. -- Peter Eisentraut peter_e@gmx.net
On Thu, Jun 19, 2003 at 09:52:14AM -0400, Rod Taylor wrote: > On Thu, 2003-06-19 at 09:40, Tom Lane wrote: > Do we want them to? If we don't mind them being executed, it is far > easier to: > > - alter table structure > - Add all new constraints (without confirming their correctness at that > time) > - update table contents via an SPI call to UPDATE WHERE <column> IS NULL Sorry, I haven't read the spec, but what happens when there is a default value already and it's not NULL? Are tuples where column = default updated? Are tuples where column IS NULL updated? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Y dijo Dios: "Que sea Satanás, para que la gente no me culpe de todo a mí." "Y que hayan abogados, para que la gente no culpe de todo a Satanás"
Rod Taylor <rbt@rbt.ca> writes:
> - alter table structure
> - Add all new constraints (without confirming their correctness at that
> time)
> - update table contents via an SPI call to UPDATE WHERE <column> IS NULL
> The where clause would avoid issues with inherited data being
> overwritten when the child tables are updated.
But it creates issues with failing to check the new constraints at those
same child rows.
regards, tom lane
On Thu, 2003-06-19 at 10:05, Alvaro Herrera wrote: > On Thu, Jun 19, 2003 at 09:52:14AM -0400, Rod Taylor wrote: > > On Thu, 2003-06-19 at 09:40, Tom Lane wrote: > > > Do we want them to? If we don't mind them being executed, it is far > > easier to: > > > > - alter table structure > > - Add all new constraints (without confirming their correctness at that > > time) > > - update table contents via an SPI call to UPDATE WHERE <column> IS NULL > > Sorry, I haven't read the spec, but what happens when there is a default > value already and it's not NULL? Are tuples where column = default > updated? Are tuples where column IS NULL updated? We're talking about add column. Since it's a new column, there will never be a default and all entries are NULL in PostgreSQL. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
> > Anyway, I suppose you have indirectly confirmed that user triggers, etc. > > should NOT fire on for the data update. I didn't see anything in the > > spec that said one way or the other. > > The spec doesn't say that they fire, so that means that they don't fire. Sounds like a definitive answer to me. I'll go that route then. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
> On Thu, 2003-06-19 at 10:05, Alvaro Herrera wrote:
>> Sorry, I haven't read the spec, but what happens when there is a default
>> value already and it's not NULL? Are tuples where column =3D default
>> updated? Are tuples where column IS NULL updated?
> We're talking about add column. Since it's a new column, there will
> never be a default and all entries are NULL in PostgreSQL.
That's an overly simplistic analysis, given that the column may already
exist in child tables. I am not sure that the behavior is fully
consistent even now in such cases, but we at least should make certain
it doesn't become less consistent.
regards, tom lane
On Thu, 2003-06-19 at 10:42, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > On Thu, 2003-06-19 at 10:05, Alvaro Herrera wrote: > >> Sorry, I haven't read the spec, but what happens when there is a default > >> value already and it's not NULL? Are tuples where column =3D default > >> updated? Are tuples where column IS NULL updated? > > > We're talking about add column. Since it's a new column, there will > > never be a default and all entries are NULL in PostgreSQL. > > That's an overly simplistic analysis, given that the column may already > exist in child tables. I am not sure that the behavior is fully > consistent even now in such cases, but we at least should make certain > it doesn't become less consistent. Right now if the column exists in the child table, the add column is rejected. I assume that will remain. It is consistent with the user providing a column that inherits a column from a parent by the same name. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
> Right now if the column exists in the child table, the add column is
> rejected. I assume that will remain.
Have you actually tried it?
regression=# create table p1 (f1 int);
CREATE TABLE
regression=# create table c1 (f2 int) inherits(p1);
CREATE TABLE
regression=# alter table p1 add column f2 int;
NOTICE: ALTER TABLE: merging definition of column "f2" for child c1
ALTER TABLE
regression=#
regards, tom lane
On Thu, 2003-06-19 at 15:00, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > Right now if the column exists in the child table, the add column is > > rejected. I assume that will remain. > > Have you actually tried it? I used different datatypes which, of course, was the wrong test. When I update the values in t1 I need to ensure I only get data from t1 that has the new column NULL. Constraints to be checked are still only the ones attached to the altered table directly. A constraint on t1 only will not affect data on t2. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
> There is no alternative, unless you want the command to be > non-roll-back-able. Well, you can do a cluster-type table duplication... > > Someone can > > make it more efficient in regards to constraint checks, etc. in the > > future if they want -- I don't intend to. > > It'd be nice if you at least ensure that all the constraints are checked > in a single pass over the table (not one per constraint). Right offhand > I do not see why they couldn't be checked in the same pass that does the > UPDATE. For extra credit, detect that the default expression is > immutable or stable, and do the checks *once* not once per row. And check domain constraints...? Chris
On Thu, 2003-06-19 at 21:22, Christopher Kings-Lynne wrote: > > There is no alternative, unless you want the command to be > > non-roll-back-able. > > Well, you can do a cluster-type table duplication... Thats still double the disk space, although that has the nice side effect of not requiring a vacuum. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
> Thats still double the disk space, although that has the nice side > effect of not requiring a vacuum. Also, a rollback after 99% of the updates have been done will waste no diskspace... Chris