Hello, Just over a year ago, I posted a patch (based on a previous patch by Bernd Helmle) that attempted to add pg_constraint rows for NOT NULL constraints. http://archives.postgresql.org/message-id/20110707213401.ga27...@alvh.no-ip.org That patch was rather long and complex, as it tried to handle all the hairy issues directly with a completely new 'contype' value for NOT NULL constraints; so the code had to deal with inheritance of constraints, pg_dump issues, and a lot of nitty-gritty. In the end it was killed by a simple realization of Peter Eisentraut's: "Why not just transform these into the equivalent CHECK constraints instead?" That ended up being discussing at length, and this patch, much smaller than the previous one, is an attempt to do things that way.
This patch is not final yet, because there are some issues still open; but the interesting stuff already works. Simply declaring a column as NOT NULL creates a CHECK pg_constraint row; similarly, declaring a CHECK (foo IS NOT NULL) constraint sets the pg_attribute.attnotnull flag. If you create a child table, the NOT NULL constraint will be inherited. One thing that might be of interest is that we accumulate names of not-nullable columns during parse analysis if they can't be dealt with immediately; later, MergeAttributes is in charge of walking that list to determine which columns need to have is_not_null set. This is a bit ugly but necessary: consider the following: CREATE TABLE foo (CHECK (a IS NOT NULL), a INT); At the point where the CHECK is processed, there is not yet any ColumnDef node to set is_not_null to. Also CREATE TABLE foo (a INT); CREATE TABLE bar (CHECK (a IS NOT NULL)) INHERITS (foo); Same thing. We also handle this correctly: CREATE TABLE foo (a INT, b INT CHECK (a IS NOT NULL)); i.e. the NOT NULL check is declared on the "wrong" column (this last command is not actually standard SQL, because column constraints are supposed to apply only to the current column; but we take it anyway.) Another thing is that pg_dump now reads attnotnull as always false for 9.3 servers, hoping that there will be a corresponding CHECK constraint. I think this is okay, because a missing CHECK constraint means that somebody has been messing with the catalogs and so if it bombs out it's not our fault. But if somebody opines differently let me know. Another point to keep in mind is that I haven't touched syntax definitions. This means that ALTER TABLE / SET NOT NULL does not let you specify a constaint name, so you get an auto-generated name. I think this is okay; if you want a different name, use ALTER TABLE / ADD CONSTRAINT instead. If you do CREATE TABLE foo (a INT NOT NULL, CHECK (a IS NOT NULL)) you get two constraints. Some of the open items here: * declaring CHECK (foo IS NOT NULL) NO INHERIT doesn't work (i.e. the constraint is inherited) * declaring CHECK (foo IS NOT NUL) NOT VALID doesn't work (i.e. the constraint is tested on existing rows). * I've only handled raw_expr, not cooked_expr, in ColumnDef. I think this means that stuff such as CREATE TABLE AS and CREATE TABLE LIKE don't work. Haven't tested that yet. * the information_schema needs updating (mainly to remove some UNION branches, I think) * Haven't looked at domains. -- Álvaro Herrera <alvhe...@commandprompt.com>
Description: Binary data
-- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers