Обсуждение: [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>