BUG #1740: Deferred foreign key constraint isn't deferred
The following bug has been logged online:
Bug reference: 1740
Logged by: Daniel Cristian Cruz
Email address: dccruz@mega.com.br
PostgreSQL version: 8.0.3
Operating system: Windows XP Professional
Description: Deferred foreign key constraint isn't deferred
Details:
Below there is a script which raises the bug:
CREATE TABLE mat_comissao_itens (
mat_nfs_diretorio character varying(6),
mat_nfs_in_codigo integer,
mat_nsi_in_ordem integer
);
CREATE TABLE mat_nf_saida_itens (
mat_nfs_diretorio character varying(6) NOT NULL,
mat_nfs_in_codigo integer NOT NULL,
mat_nsi_in_ordem integer,
PRIMARY KEY (mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
);
COPY mat_comissao_itens (mat_nfs_diretorio, mat_nfs_in_codigo,
mat_nsi_in_ordem) FROM stdin;
001001 339 1
001001 339 1
001001 339 1
001001 339 2
001001 339 2
001001 339 2
\.
COPY mat_nf_saida_itens (mat_nfs_diretorio, mat_nfs_in_codigo,
mat_nsi_in_ordem) FROM stdin;
001001 339 1
001001 339 2
\.
ALTER TABLE ONLY mat_comissao_itens
ADD CONSTRAINT mat_nf_saida_itens_pa_mat_comissao_itens FOREIGN KEY
(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem) REFERENCES
mat_nf_saida_itens(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
BEGIN;
DELETE FROM mat_nf_saida_itens WHERE mat_nsi_in_ordem = 2;
UPDATE mat_comissao_itens SET mat_nsi_in_ordem = 1 WHERE mat_nsi_in_ordem =
2;
COMMIT;
And here is the output:
CREATE TABLE
psql:rafa2.backup:11: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "mat_nf_saida_itens_pkey" for table "mat_nf_saida_itens"
CREATE TABLE
ALTER TABLE
BEGIN
psql:rafa2.backup:28: ERROR: update or delete on "mat_nf_saida_itens"
violates foreign key constraint "mat_nf_saida_itens_pa_mat_comissao_itens"
on "mat_comissao_itens"
DETAIL: Key
(mat_nfs_diretorio,mat_nfs_in_codigo,mat_nsi_in_ordem)=(001001,339,2) is
still referenced from table "mat_comissao_itens".
psql:rafa2.backup:29: ERROR: current transaction is aborted, commands
ignored until end of transaction block
ROLLBACK
On Thu, Jun 30, 2005 at 02:01:39PM +0100, Daniel Cristian Cruz wrote:
ALTER TABLE ONLY mat_comissao_itens
ADD CONSTRAINT mat_nf_saida_itens_pa_mat_comissao_itens FOREIGN KEY
(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem) REFERENCES
mat_nf_saida_itens(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
Note the following in the CREATE TABLE documentation:
RESTRICT
Produce an error indicating that the deletion or update would
create a foreign key constraint violation. This is the same as
NO ACTION except that the check is not deferrable.
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Big, big mistake... Thanks for the help, sorry about the post...
Michael Fuhr wrote:
On Thu, Jun 30, 2005 at 02:01:39PM +0100, Daniel Cristian Cruz wrote:
ALTER TABLE ONLY mat_comissao_itens
ADD CONSTRAINT mat_nf_saida_itens_pa_mat_comissao_itens FOREIGN KEY
(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem) REFERENCES
mat_nf_saida_itens(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;Note the following in the CREATE TABLE documentation:
RESTRICT
Produce an error indicating that the deletion or update would
create a foreign key constraint violation. This is the same as
NO ACTION except that the check is not deferrable.http://www.postgresql.org/docs/8.0/static/sql-createtable.html
- --
Daniel Cristian Cruz
Desenvolvimento Mega Small Business
Ramal 8511
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCw/kU6TuR6lz8YgMRArTSAJ9QH8u6IRhBDu5/4CfsgIu6/M9dTwCfWApU
+9ejxg51x5QnZAXcFcI0odM=
=4zgh
-----END PGP SIGNATURE-----
On Thu, 30 Jun 2005, Daniel Cristian Cruz wrote:
ALTER TABLE ONLY mat_comissao_itens
ADD CONSTRAINT mat_nf_saida_itens_pa_mat_comissao_itens FOREIGN KEY
(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem) REFERENCES
mat_nf_saida_itens(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;BEGIN;
DELETE FROM mat_nf_saida_itens WHERE mat_nsi_in_ordem = 2;
UPDATE mat_comissao_itens SET mat_nsi_in_ordem = 1 WHERE mat_nsi_in_ordem =
2;
COMMIT;
Referential actions are immediate (they're defined as actions that happen
upon the occurrance), it's the constraint checks that are deferrable with
the deferrable/initially deferred.