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 (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 the lack of the referenced
tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion since it 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 introduce any kind of
indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending on the order of creation of
the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing table is updated or tuples
are added to it, even when this table is the origin of various referential chains of verifications, the resulting behavior only depends on the
order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem for which no database product
includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples from a 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 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 action 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 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, and the 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) against the 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 do 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)
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 the lack of the referenced
tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion since it 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 introduce any kind of
indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending on the order of creation of
the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing table is updated or tuples
are added to it, even when this table is the origin of various referential chains of verifications, the resulting behavior only depends on the
order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem for which no database product
includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples from a 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 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 action 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 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, and the 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) against the 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 do 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?
--
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, Pennsylvania 19026
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, Pennsylvania 19026To 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 the lack of the referenced
tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion since it 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 introduce any kind of
indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending on the order of creation of
the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing table is updated or tuples
are added to it, even when this table is the origin of various referential chains of verifications, the resulting behavior only depends on the
order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem for which no database product
includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples from a 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 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 action 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 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, and the 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) against the 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 do 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?
--
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, Pennsylvania 19026