Thread: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
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.
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?
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>
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?
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>
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.
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>
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>