Thread: PATCH proposed with new features for CREATE TABLE
Hi people!.I developed a work for an university course, which I wish to share with you.
I extended the foreign key clause in the create table in order to permit insertions and updates on a referencing table
(atable with foreign key
attributes) with all kind of actions (the existing ones plus CASCADE, SET NULL and SET DEFAULT).
I think it is important to handle situations where the referencing data is available but it cannot be inserted due to
thelack of the referenced
tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion since
itcan be done
automatically with the proposed functionality.
Applying it in the context of a product with a well-defined execution model of triggers, like PostgreSQL, I do not
introduceany kind of
indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending
onthe order of creation of
the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing
tableis updated or tuples
are added to it, even when this table is the origin of various referential chains of verifications, the resulting
behavioronly depends on the
order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem for
whichno database product
includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples
froma text of Markowitz)
and it works well.
The new syntax for the column_constraint_clause (and table_constraint_clause) of the CREATE TABLE statement that I
propose(and implement) is:
... [ ON INSERT action ] [ ON DELETE action ] [ ON UPDATE_LEFT action ] [ ON UPDATE_RIGHT action ]
...
where
"ON DELETE action" stays the same as before (it refers to deletes in the referenced table),
"ON UPDATE_RIGHT action" is the original ON UPDATE action (like before, it refers to modifications in the referenced
table),
"ON UPDATE_LEFT action" specifies the action to do when a referencing column (a FK_column) in the referencing table is
beingupdated to a new value, and this new
value do not exist like pk_value in the pk_table. If the row is updated, but the referencing column is not changed, no
actionis done. There are
the following actions.
NO ACTION Disallows update of row.
RESTRICT Disallows update of row.
CASCADE Updates the value of the referenced column (the pk_column) to the new value of the
referencingcolumn (the fk_column).
SET NULL Sets the referencing column values to NULL.
SET DEFAULT Sets the referencing column values to their default value.
"ON INSERT action" specifies the action to do when a referencing row (a FK_row) in the referencing table is being
inserted,and the new fk_values do not exist
like pk_values in the referenced table (pk_table). There are the following actions.
NO ACTION Disallows insert of row.
RESTRICT Disallows insert of row.
CASCADE Inserts a new row into the referenced table which pk_columns take the values of the new fk_columns,
andthe other attributes are set to
NULL values (if it is allowed).
SET NULL Sets the referencing column values to NULL.
SET DEFAULT Sets the referencing column values to their default value.
I have not added new files, just modified the existing ones (so the makefiles stay like before). I send a diff (-c)
againstthe version 7.0.2
(the one I worked with).
In summary, the patch contains:
* modifications to the grammar to include the new syntax of the CREATE TABLE statement (to recognize the new tokens and
dothe appropriate
stuff).
* Addition of definitions of flags and masks for FOREIGN KEY constraints in CreateStmt.
* the new generic trigger procedures for referential integrity constraint checks.
* modifications to the parser stage to accept them (in procedures transformCreateStmt() and transformAlterTableStmt()
).
* update to declarations for operations on built-in types.
* extension of the definition of the system "procedure" relation (pg_proc) along with the relation's initial
contents.
* modifications to the TRIGGERs support code to accept the new characteristics.
Many thanks in advance to those who read and (maybe) consider all this, regards
Jose Luis Ozzano (jozzano@exa.unicen.edu.ar)
Can someone comment on this? > Hi people!.I developed a work for an university course, which I wish to share with you. > > I extended the foreign key clause in the create table in order to permit insertions and updates on a referencing table(a table with foreign key > attributes) with all kind of actions (the existing ones plus CASCADE, SET NULL and SET DEFAULT). > > I think it is important to handle situations where the referencing data is available but it cannot be inserted due to thelack of the referenced > tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion sinceit can be done > automatically with the proposed functionality. > Applying it in the context of a product with a well-defined execution model of triggers, like PostgreSQL, I do not introduceany kind of > indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending onthe order of creation of > the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing tableis updated or tuples > are added to it, even when this table is the origin of various referential chains of verifications, the resulting behavioronly depends on the > order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem forwhich no database product > includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples froma text of Markowitz) > and it works well. > > The new syntax for the column_constraint_clause (and table_constraint_clause) of the CREATE TABLE statement that I propose(and implement) is: > > ... > [ ON INSERT action ] > [ ON DELETE action ] > [ ON UPDATE_LEFT action ] > [ ON UPDATE_RIGHT action ] > ... > where > > "ON DELETE action" > stays the same as before (it refers to deletes in the referenced table), > > "ON UPDATE_RIGHT action" > is the original ON UPDATE action (like before, it refers to modifications in the referenced table), > > "ON UPDATE_LEFT action" > specifies the action to do when a referencing column (a FK_column) in the referencing table is being updated to a newvalue, and this new > value do not exist like pk_value in the pk_table. If the row is updated, but the referencing column is not changed, noaction is done. There are > the following actions. > > NO ACTION > Disallows update of row. > > RESTRICT > Disallows update of row. > > CASCADE > Updates the value of the referenced column (the pk_column) to the new value of the > referencing column (the fk_column). > > SET NULL > Sets the referencing column values to NULL. > > SET DEFAULT > Sets the referencing column values to their default value. > > "ON INSERT action" > specifies the action to do when a referencing row (a FK_row) in the referencing table is being inserted, and the newfk_values do not exist > like pk_values in the referenced table (pk_table). There are the following actions. > > NO ACTION > Disallows insert of row. > > RESTRICT > Disallows insert of row. > > CASCADE > Inserts a new row into the referenced table which pk_columns take the values of the new fk_columns, and the otherattributes are set to > NULL values (if it is allowed). > > SET NULL > Sets the referencing column values to NULL. > > SET DEFAULT > Sets the referencing column values to their default value. > > I have not added new files, just modified the existing ones (so the makefiles stay like before). I send a diff (-c) againstthe version 7.0.2 > (the one I worked with). > > In summary, the patch contains: > > * modifications to the grammar to include the new syntax of the CREATE TABLE statement (to recognize the new tokens anddo the appropriate > stuff). > > * Addition of definitions of flags and masks for FOREIGN KEY constraints in CreateStmt. > > * the new generic trigger procedures for referential integrity constraint checks. > > * modifications to the parser stage to accept them (in procedures transformCreateStmt() and > transformAlterTableStmt() ). > > * update to declarations for operations on built-in types. > > * extension of the definition of the system "procedure" relation (pg_proc) along with the > relation's initial contents. > > * modifications to the TRIGGERs support code to accept the new characteristics. > > Many thanks in advance to those who read and (maybe) consider all this, regards > > Jose Luis Ozzano (jozzano@exa.unicen.edu.ar) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Fri, 24 Aug 2001, Bruce Momjian wrote: > Can someone comment on this? I sent him some concerns I had (including the fact that we can't rename ON UPDATE since it's in the spec). I'm working through some more behavioral concerns I have, but I haven't decided whether or not they're actually problems. The patch is pretty long and I haven't had a chance to look through it, but my guess is that it won't apply entirely cleanly since there's been work done on alot of the sections it touches since 7.0.x, but it probably shouldn't be too hard to beat it into submission.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Fri, 24 Aug 2001, Bruce Momjian wrote:
>> Can someone comment on this?
> I sent him some concerns I had (including the fact that we
> can't rename ON UPDATE since it's in the spec).
I was also concerned about the fact that it didn't seem to have a lot
to do with the SQL-mandated behaviors... even though we don't currently
have all the SQL features, adding non-spec stuff now might create
problems when we want to add the spec features.
regards, tom lane
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On Fri, 24 Aug 2001, Bruce Momjian wrote: > >> Can someone comment on this? > > > I sent him some concerns I had (including the fact that we > > can't rename ON UPDATE since it's in the spec). > > I was also concerned about the fact that it didn't seem to have a lot > to do with the SQL-mandated behaviors... even though we don't currently > have all the SQL features, adding non-spec stuff now might create > problems when we want to add the spec features. Yea, I know, but already have lots of non-standard stuff that is going to cause problems when get go to standards and this features is missing from every release, and people ask for it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
To get this applied, we will need to hear from people who want this functionality. Sorry. > Hi people!.I developed a work for an university course, which I wish to share with you. > > I extended the foreign key clause in the create table in order to permit insertions and updates on a referencing table(a table with foreign key > attributes) with all kind of actions (the existing ones plus CASCADE, SET NULL and SET DEFAULT). > > I think it is important to handle situations where the referencing data is available but it cannot be inserted due to thelack of the referenced > tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion sinceit can be done > automatically with the proposed functionality. > Applying it in the context of a product with a well-defined execution model of triggers, like PostgreSQL, I do not introduceany kind of > indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending onthe order of creation of > the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing tableis updated or tuples > are added to it, even when this table is the origin of various referential chains of verifications, the resulting behavioronly depends on the > order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem forwhich no database product > includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples froma text of Markowitz) > and it works well. > > The new syntax for the column_constraint_clause (and table_constraint_clause) of the CREATE TABLE statement that I propose(and implement) is: > > ... > [ ON INSERT action ] > [ ON DELETE action ] > [ ON UPDATE_LEFT action ] > [ ON UPDATE_RIGHT action ] > ... > where > > "ON DELETE action" > stays the same as before (it refers to deletes in the referenced table), > > "ON UPDATE_RIGHT action" > is the original ON UPDATE action (like before, it refers to modifications in the referenced table), > > "ON UPDATE_LEFT action" > specifies the action to do when a referencing column (a FK_column) in the referencing table is being updated to a newvalue, and this new > value do not exist like pk_value in the pk_table. If the row is updated, but the referencing column is not changed, noaction is done. There are > the following actions. > > NO ACTION > Disallows update of row. > > RESTRICT > Disallows update of row. > > CASCADE > Updates the value of the referenced column (the pk_column) to the new value of the > referencing column (the fk_column). > > SET NULL > Sets the referencing column values to NULL. > > SET DEFAULT > Sets the referencing column values to their default value. > > "ON INSERT action" > specifies the action to do when a referencing row (a FK_row) in the referencing table is being inserted, and the newfk_values do not exist > like pk_values in the referenced table (pk_table). There are the following actions. > > NO ACTION > Disallows insert of row. > > RESTRICT > Disallows insert of row. > > CASCADE > Inserts a new row into the referenced table which pk_columns take the values of the new fk_columns, and the otherattributes are set to > NULL values (if it is allowed). > > SET NULL > Sets the referencing column values to NULL. > > SET DEFAULT > Sets the referencing column values to their default value. > > I have not added new files, just modified the existing ones (so the makefiles stay like before). I send a diff (-c) againstthe version 7.0.2 > (the one I worked with). > > In summary, the patch contains: > > * modifications to the grammar to include the new syntax of the CREATE TABLE statement (to recognize the new tokens anddo the appropriate > stuff). > > * Addition of definitions of flags and masks for FOREIGN KEY constraints in CreateStmt. > > * the new generic trigger procedures for referential integrity constraint checks. > > * modifications to the parser stage to accept them (in procedures transformCreateStmt() and > transformAlterTableStmt() ). > > * update to declarations for operations on built-in types. > > * extension of the definition of the system "procedure" relation (pg_proc) along with the > relation's initial contents. > > * modifications to the TRIGGERs support code to accept the new characteristics. > > Many thanks in advance to those who read and (maybe) consider all this, regards > > Jose Luis Ozzano (jozzano@exa.unicen.edu.ar) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026