Re: [HACKERS] add column .. default
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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] add column .. default
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] add column .. default
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] add column .. default
On Thu, 2003-06-19 at 09:40, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] add column .. default
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] add column .. default
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] add column .. default
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] add column .. default
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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] add column .. default
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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] add column .. default
On Thu, 2003-06-19 at 10:42, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] add column .. default
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] add column .. default
On Thu, 2003-06-19 at 15:00, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] add column .. default
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] add column .. default
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
