5 ms·

Hmm, something I'm not understanding from the description of the implementation is how it handles this sequence of operations: 1.) Start with a nullable column

Hmm, something I'm not understanding from the description of the implementation is how it handles this sequence of operations: 1.) Start with a nullable column with some existing rows with null values. 2.) Make the column required with default=100. Postgres now marks atthasmissing as true and attmissingval as 100. Rows will be lazy-populated to 100, but aren't immediately modified. 3.) Make the column nullable again. 4.) Add some new null values. 5.) Make the column required with default=200. At this point, the right behavior is that all originally-null rows from step #1 have value 100 and all rows added in step #4 have value 200, but that's not representable with the atthasmissing/attmissingval system. I guess maybe in steps 3 or 5 it needs to just bail out and do a full table rewrite? I was thinking through this problem a while ago and one idea I had was that you could version your schema and have each row remember what the schema version was when the row was created. Then you could lazy-populate any null value by looking up the next default value for the column chronologically in schema history. But I guess maybe that's complex enough or would hurt common-case perf enough that Postgres realistically isn't ever going to implement it.

kdmytro 8y ago
The patch is about ALTER TABLE ADD COLUMN with non-null DEFAULT. Your step 1 does not have a default value so this patch will not affect the database behavior. Postgres will not do anything with atthasmissing or attmissingval on steps 2 and 5 because these are ALTER TABLE ALTER COLUMN DROP NOT NULL, which is out of scope of the discussed patch.
- alangpierce 8y ago
  I see, thanks! I guess I was interpreting it as "this is the new way that default values work in Postgres", but really it's only specific schema changes that are affected.
colanderman 8y ago
Setting a column NOT NULL with a default does not replace NULLs with the default value, it throws an error.
da_chicken 8y ago
As others have said, this is about adding columns entirely to tables, not modifying their schema. However.... This should fail at step 2 (and again at step 5). Modifying the default value of a column never modifies existing rows of a column. DDL (e.g., ALTER TABLE) should never modify data. You need DML (e.g., UPDATE) to do that. Example: CREATE TABLE product ( id SERIAL PRIMARY KEY NOT NULL, quantity int NULL ); INSERT INTO product (quantity) VALUES (NULL); ALTER TABLE product ALTER COLUMN quantity SET DEFAULT 0; ALTER TABLE product ALTER COLUMN quantity SET NOT NULL; The second ALTER TABLE will fail with "Column 'quantity' contains null values." SELECT * FROM product WHERE quantity IS NULL; And that will still return values. You'd need to run this before modifying the NULL constraint to NOT NULL: UPDATE product SET quantity = 0 WHERE quantity IS NULL; When you add or modify a constraint, the existing data must already conform to the new constraint or it will fail. When you modify a default value, you only affect future rows inserted into the table. A good RDBMS doesn't silently modify your data and errors when it tries to do something that you didn't explicitly tell it to do. Remember that a column that doesn't have a default value specified actually has an implicit DEFAULT NULL default value (unless it's something special like SERIAL). That's how RDBMS know to throw an error when you don't specify a NOT NULL column. It tries to default the column to NULL and can't.
- alangpierce 8y ago
  Thanks! I guess I had some gaps in my mental model of how postgres handles these things, this clears it up quite a bit! Just to confirm/repeat back: * When you say DDL operations (ALTER TABLE) will "never modify data", they're still allowed to add a column with a non-null default since that's not "modifying data" by your definition, it's picking the value to use when introducing new data. I shouldn't think of it as "set implicit null to 3", I should think of it as "set nothingness to 3", just like how it would "set nothingness to null" in normal column creation. But it would never be willing to "set null to 3" in a DDL operation. * Postgres doesn't have an O(1) way (meaning: never does a table scan while holding a significant lock) to transition a nullable column to non-null, since it'll need to check the contents of the entire table. (Although maybe it could use an index?) I guess I was hoping that the default would help here, but really it only affects creation of new values. * More generally speaking, Postgres's design direction is to put data integrity as a higher priority than scale. It'll do both if it can (like in the article), but there are still cases where schema changes might cause user disruption if done on a gigantic table. It could have been designed so that schema changes are always fast (like they tend to be in NoSQL systems), but that would come at the expense of other nice properties.

kdmytro 8y ago

The patch is about ALTER TABLE ADD COLUMN with non-null DEFAULT. Your step 1 does not have a default value so this patch will not affect the database behavior. Postgres will not do anything with atthasmissing or attmissingval on steps 2 and 5 because these are ALTER TABLE ALTER COLUMN DROP NOT NULL, which is out of scope of the discussed patch.

alangpierce 8y ago
I see, thanks! I guess I was interpreting it as "this is the new way that default values work in Postgres", but really it's only specific schema changes that are affected.

colanderman 8y ago

Setting a column NOT NULL with a default does not replace NULLs with the default value, it throws an error.

da_chicken 8y ago

As others have said, this is about adding columns entirely to tables, not modifying their schema. However.... This should fail at step 2 (and again at step 5). Modifying the default value of a column never modifies existing rows of a column. DDL (e.g., ALTER TABLE) should never modify data. You need DML (e.g., UPDATE) to do that. Example: CREATE TABLE product ( id SERIAL PRIMARY KEY NOT NULL, quantity int NULL ); INSERT INTO product (quantity) VALUES (NULL); ALTER TABLE product ALTER COLUMN quantity SET DEFAULT 0; ALTER TABLE product ALTER COLUMN quantity SET NOT NULL; The second ALTER TABLE will fail with "Column 'quantity' contains null values." SELECT * FROM product WHERE quantity IS NULL; And that will still return values. You'd need to run this before modifying the NULL constraint to NOT NULL: UPDATE product SET quantity = 0 WHERE quantity IS NULL; When you add or modify a constraint, the existing data must already conform to the new constraint or it will fail. When you modify a default value, you only affect future rows inserted into the table. A good RDBMS doesn't silently modify your data and errors when it tries to do something that you didn't explicitly tell it to do. Remember that a column that doesn't have a default value specified actually has an implicit DEFAULT NULL default value (unless it's something special like SERIAL). That's how RDBMS know to throw an error when you don't specify a NOT NULL column. It tries to default the column to NULL and can't.

alangpierce 8y ago
Thanks! I guess I had some gaps in my mental model of how postgres handles these things, this clears it up quite a bit! Just to confirm/repeat back: * When you say DDL operations (ALTER TABLE) will "never modify data", they're still allowed to add a column with a non-null default since that's not "modifying data" by your definition, it's picking the value to use when introducing new data. I shouldn't think of it as "set implicit null to 3", I should think of it as "set nothingness to 3", just like how it would "set nothingness to null" in normal column creation. But it would never be willing to "set null to 3" in a DDL operation. * Postgres doesn't have an O(1) way (meaning: never does a table scan while holding a significant lock) to transition a nullable column to non-null, since it'll need to check the contents of the entire table. (Although maybe it could use an index?) I guess I was hoping that the default would help here, but really it only affects creation of new values. * More generally speaking, Postgres's design direction is to put data integrity as a higher priority than scale. It'll do both if it can (like in the article), but there are still cases where schema changes might cause user disruption if done on a gigantic table. It could have been designed so that schema changes are always fast (like they tend to be in NoSQL systems), but that would come at the expense of other nice properties.