pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
8+ messages / 3 participants
[nested] [flat]
* [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-08 08:13 BenLaKnet <benlaknet@icqmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: BenLaKnet @ 2003-08-08 08:13 UTC (permalink / raw)
To: pgsql-sql
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 NOT NULL,
"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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-08 15:52 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: BenLaKnet <benlaknet@icqmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Stephan Szabo @ 2003-08-08 15:52 UTC (permalink / raw)
To: BenLaKnet <benlaknet@icqmail.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-11 07:07 Benoît Bournon <benoit.bournon@adelis.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 8+ messages in thread
From: Benoît Bournon @ 2003-08-11 07:07 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: BenLaKnet <benlaknet@icqmail.com>; pgsql-sql
No trigger
Just triggers for foreign key in spectacle and none in spectacle_v
How is it possible to verify triggers for foreign keys ?
Stephan Szabo a écrit:
>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?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-11 07:23 BenLaKnet <benlaknet@icqmail.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 8+ messages in thread
From: BenLaKnet @ 2003-08-11 07:23 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
No trigger
Just triggers for foreign key in spectacle and none in spectacle_v
How is it possible to verify triggers for foreign keys ?
Stephan Szabo a écrit:
>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?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-11 13:14 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Benoît Bournon <benoit.bournon@adelis.com>
0 siblings, 1 reply; 8+ messages in thread
From: Stephan Szabo @ 2003-08-11 13:14 UTC (permalink / raw)
To: Benoît Bournon <benoit.bournon@adelis.com>; +Cc: BenLaKnet <benlaknet@icqmail.com>; pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-11 13:40 Benoît Bournon <benoit.bournon@adelis.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 8+ messages in thread
From: Benoît Bournon @ 2003-08-11 13:40 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: BenLaKnet <benlaknet@icqmail.com>; pgsql-sql
we make a dump before ... and with a product pgmanager (ems tech) we do
not show any foreign keys.
But I think all of triggers of referenced table are not deleted.
We recreate the schemas ... and now is running.
How is it possible to identify Triggers with no referenced tables ?
Stephan Szabo a écrit:
>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?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-11 13:44 BenLaKnet <benlaknet@icqmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: BenLaKnet @ 2003-08-11 13:44 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
we make a dump before ... and with a product pgmanager (ems tech) we do
not show any foreign keys.
But I think all of triggers of referenced table are not deleted.
We recreate the schemas ... and now it's running.
How is it possible to identify Triggers with no referenced tables ?
Stephan Szabo a écrit:
>On Mon, 11 Aug 2003, BenLaKnet wrote:
>
>
>
>>How could I do a pg_dump -s ??
>>
>>on local server ??
>>
>>
>
>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.
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
@ 2003-08-11 13:48 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: BenLaKnet <benlaknet@icqmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2003-08-11 13:48 UTC (permalink / raw)
To: BenLaKnet <benlaknet@icqmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-08-11 13:48 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-08 08:13 [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3 BenLaKnet <benlaknet@icqmail.com>
2003-08-08 15:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-11 07:07 ` Benoît Bournon <benoit.bournon@adelis.com>
2003-08-11 13:14 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-11 13:40 ` Benoît Bournon <benoit.bournon@adelis.com>
2003-08-11 07:23 ` BenLaKnet <benlaknet@icqmail.com>
2003-08-11 13:44 Re: [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3 BenLaKnet <benlaknet@icqmail.com>
2003-08-11 13:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox