Re: problem with transaction

On Fri, 18 Oct 2002, Guillermo Trutner wrote:

> I have two tables as follow:
>
> -- Table: d_coning
> CREATE TABLE "d_coning" (
>
> "anio" int4 NOT NULL,
>
> "codigo_con" int4 NOT NULL,
>
> CONSTRAINT "d_coning_pkey" PRIMARY KEY ("anio", "codigo_con")
>
> ) WITH OIDS;
>
> -- Table: d_recaud
>
> CREATE TABLE "d_recaud" (
>
> "anio" int4 NOT NULL,
>
> "codigo_con" int4 NOT NULL,
>
> CONSTRAINT "d_recaud_pkey" PRIMARY KEY ("anio"),
>
> CONSTRAINT "<unnamed>" FOREIGN KEY ("anio", "codigo_con") REFERENCES
> "d_coning" ("anio", "codigo_con") ON DELETE NO ACTION ON UPDATE NO
> ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
>
> ) WITH OIDS;
>
> When I put the follow into a transaction:
>
> -- Primero borro las tablas correspondientes a ao
>
> DELETE FROM d_recaud WHERE anio = 2000;
>
> DELETE FROM d_coning WHERE anio = 2000;
>
> -- Ahora las inserto nuevamente
>
> INSERT INTO d_coning (anio, codigo_con)
>
> SELECT anio, codigo_con
>
> FROM d_coning_pipe
>
> WHERE anio = 2000
>
> ;
>
> INSERT INTO d_recaud (anio, codigo_con)
>
> SELECT anio, codigo_con
>
> FROM d_recaud_pipe
>
> WHERE anio = 2000 ;
>
> I get the following error:
>
> An error has ocurred in pgAdmin II:frmSQLInput.cmdExecute_Click:
>
> Number: -2147467259
> Description: Error while executing query;
> ERROR: <unnamed> referential integrity violation - key in d_coning still referenced from d_recaud
>
> If someone has got this error, please tell me the solution.

My guess is that one of the d_recaud records being re-added
has an anio=2000. I believe this bug is fixed in 7.3b2.