Thread: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

[pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
BenLaKnet
Date:
I have to duplicate a table to save different instances of datas.
 Structure de la table "spectacle"  
-------------------------------------------------------- */ 
CREATE TABLE "spectacle" (  id_spectacle SERIAL,   "id_membre_adherent" INTEGER NOT NULL,   "id_genre_festival"
INTEGER,  "id_festival" INTEGER,   "nom" VARCHAR(255),   "compagnie_interprete" VARCHAR(255),   "vitaculture" INTEGER
NOTNULL,   "regionales" INTEGER NOT NULL,   "presentation" TEXT,   "genre_precision" VARCHAR(255),   "duree"
VARCHAR(255),  "photo1" VARCHAR(255),   "photo1_credit" VARCHAR(255),   "photo2" VARCHAR(255),   "photo2_credit"
VARCHAR(255),  "salle" TEXT,   "tarifs" TEXT,   "id_traitement" INTEGER DEFAULT 1,   "distribution" TEXT,
"type_public"VARCHAR(255),   PRIMARY KEY("id_spectacle"),   FOREIGN KEY ("id_festival") REFERENCES
"festival"("id_festival") ON DELETE RESTRICT  ON UPDATE RESTRICT  NOT DEFERRABLE,   FOREIGN KEY ("id_genre_festival")
REFERENCES
 
"genre_festival"("id_genre_festival")  ON DELETE RESTRICT  ON UPDATE RESTRICT  NOT DEFERRABLE,   FOREIGN KEY
("id_membre_adherent")REFERENCES 
 
"membre_adherent"("id_membre_adherent")  ON DELETE CASCADE  ON UPDATE RESTRICT  NOT DEFERRABLE,   FOREIGN KEY
("id_traitement")REFERENCES "traitement"("id_traitement")  ON DELETE RESTRICT  ON UPDATE RESTRICT  NOT DEFERRABLE 
 
) WITH OIDS; 
CREATE INDEX "adherant_spectacle_fk" ON "spectacle" 
USING btree ("id_membre_adherent"); 
CREATE INDEX "genre_spectacle_fk" ON "spectacle" 
USING btree ("id_genre_festival"); 
CREATE INDEX "spectacle_au_festival_fk" ON "spectacle" 
USING btree ("id_festival");


When I duplicate this code in an other table named spectacle_v without 
Foreygn key ... all is running.

But when I try to delete a spectacle_membre, linked value in spectacle 
are correctly deleted, but I have an error for spectacle_v which is not 
linked :

(ERROR: referential integrity violation - key in membre_adherent still 
referenced from spectacle_v )

I do not understand this message error, because any foreign key is 
referenced with this table.






Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
Stephan Szabo
Date:
On Fri, 8 Aug 2003, BenLaKnet wrote:

> When I duplicate this code in an other table named spectacle_v without
> Foreygn key ... all is running.
>
> But when I try to delete a spectacle_membre, linked value in spectacle
> are correctly deleted, but I have an error for spectacle_v which is not
> linked :
>
> (ERROR: referential integrity violation - key in membre_adherent still
> referenced from spectacle_v )

What triggers are defined on membre_adherent?



Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
BenLaKnet
Date:
No trigger <br /><br /> Just triggers for foreign key in spectacle and none in spectacle_v<br /><br /> How is it
possibleto verify triggers for foreign keys ? <br /><br /><br /><br /> Stephan Szabo a écrit:<br /><blockquote
cite="mid20030808085155.V71867-100000@megazone.bigpanda.com"type="cite"><pre wrap="">On Fri, 8 Aug 2003, BenLaKnet
wrote:
 </pre><blockquote type="cite"><pre wrap="">When I duplicate this code in an other table named spectacle_v without
Foreygn key ... all is running.

But when I try to delete a spectacle_membre, linked value in spectacle
are correctly deleted, but I have an error for spectacle_v which is not
linked :

(ERROR: referential integrity violation - key in membre_adherent still
referenced from spectacle_v )   </pre></blockquote><pre wrap="">
What triggers are defined on membre_adherent?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>)
 
 </pre></blockquote>

Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
Stephan Szabo
Date:
On Mon, 11 Aug 2003, [ISO-8859-1] Beno�t Bournon wrote:

> No trigger
>
> Just triggers for foreign key in spectacle and none in spectacle_v
>
> How is it possible to verify triggers for foreign keys ?

Generally a select on pg_trigger.  Each foreign key should have 3
triggers, 1 on the referencing table and 2 on the referenced. You find the
tables involved by crossreferencing tgrelid against the oid of the row
in pg_class.

Can you send the results of a pg_dump -s?



Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
BenLaKnet
Date:
we make a dump before ... and with a product pgmanager (ems tech) we do not show any foreign keys.<br /><br /> But I
thinkall of triggers of referenced table are not deleted.<br /><br /> We recreate the schemas ... and now it's
running.<br/><br /> How is it possible to identify Triggers with no referenced tables ?<br /><br /><br /><br /><br />
StephanSzabo a écrit:<br /><blockquote cite="mid20030811063822.W72304-100000@megazone.bigpanda.com" type="cite"><pre
wrap="">OnMon, 11 Aug 2003, BenLaKnet wrote:
 
 </pre><blockquote type="cite"><pre wrap="">How could I do a pg_dump -s ??

on local server ??   </pre></blockquote><pre wrap="">
Yes.  I figured you wouldn't want to send your backups with data (I assume
you have pg_dump generated backups ;) ).

You can use pg_dump locally or remotely (using -h and -p options).
Locally, log in as the postgres user and run pg_dump -s <databasename>,
remotely do
pg_dump -s -h <databasehost> -p <databaseport> <databasename>
I believe.
 </pre></blockquote>

Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
Stephan Szabo
Date:
On Mon, 11 Aug 2003, BenLaKnet wrote:

> we make a dump before ... and with a product pgmanager (ems tech) we do
> not show any foreign keys.

That's possible, I'd have guessed that it should be dumping the triggers
as CREATE CONSTRAINT TRIGGER commands in the dump.

> But I think all of triggers of referenced table are not deleted.

If you can make a repeatable test case (a simpler one than your full data
set) that'd be helpful to look at.  One possibility is that older versions
(7.1 and earlier) had a bug that would cause the constraint triggers to be
reloaded without the information of which table it was associated with,
but I don't see how that'd apply here.

> We recreate the schemas ... and now it's running.
>
> How is it possible to identify Triggers with no referenced tables ?

Generally you'd need to look through pg_trigger manually looking for
something that doesn't match up correctly against the constraints you
expect to have.




Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
Benoît Bournon
Date:
we make a dump before ... and with a product pgmanager (ems tech) we do not show any foreign keys.<br /><br /> But I
thinkall of triggers of referenced table are not deleted.<br /><br /> We recreate the schemas ... and now is
running.<br/><br /> How is it possible to identify Triggers with no referenced tables ?<br /><br /><br /> Stephan Szabo
aécrit:<br /><blockquote cite="mid20030811061208.T72304-100000@megazone.bigpanda.com" type="cite"><pre wrap="">On Mon,
11Aug 2003, [ISO-8859-1] Beno?t Bournon wrote:
 
 </pre><blockquote type="cite"><pre wrap="">No trigger

Just triggers for foreign key in spectacle and none in spectacle_v

How is it possible to verify triggers for foreign keys ?   </pre></blockquote><pre wrap="">
Generally a select on pg_trigger.  Each foreign key should have 3
triggers, 1 on the referencing table and 2 on the referenced. You find the
tables involved by crossreferencing tgrelid against the oid of the row
in pg_class.

Can you send the results of a pg_dump -s?


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              <a class="moz-txt-link-freetext"
href="http://archives.postgresql.org">http://archives.postgresql.org</a>
 </pre></blockquote>

Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3

From
Benoît Bournon
Date:
No trigger <br /><br /> Just triggers for foreign key in spectacle and none in spectacle_v<br /><br /> How is it
possibleto verify triggers for foreign keys ? <br /><br /><br /><br /> Stephan Szabo a écrit:<br /><blockquote
cite="mid20030808085155.V71867-100000@megazone.bigpanda.com"type="cite"><pre wrap="">On Fri, 8 Aug 2003, BenLaKnet
wrote:
 </pre><blockquote type="cite"><pre wrap="">When I duplicate this code in an other table named spectacle_v without
Foreygn key ... all is running.

But when I try to delete a spectacle_membre, linked value in spectacle
are correctly deleted, but I have an error for spectacle_v which is not
linked :

(ERROR: referential integrity violation - key in membre_adherent still
referenced from spectacle_v )   </pre></blockquote><pre wrap="">
What triggers are defined on membre_adherent?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>)
 
 </pre></blockquote>