Stephan Szabo wrote:

On Wed, 16 Mar 2005, David Gagnon wrote:


I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM IC statement is issued.  For what I
understand the performance problem seem to came from those selects that
point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x
WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know
where they are comming from.

I think they come from the FK checking code.  Try to run a VACUUM on the
IC table just before you delete from the other tables; that should make
the checking almost instantaneous (assuming the vacuuming actually
empties the table, which would depend on other transactions).

I'll try to vaccum first before I start the delete to see if it change

There is probably a good reason why but I don't understant why in a
foreign key check it need to check the date it points to.

You delete a row from table IC and do a check for integrity on tables
that have foreign keys on IC (make sense). But why checking back IC?

Because in the general case there might be another row which satisfies the constraint added between the delete and the check.

So it's means if I want to reset the shema with DELETE FROM Table statemnets I must first drop indexes, delete the data and then recreate indexes and reload stored procedure.

Or I can suspend the foreign key check in the db right. I saw something on this. Is that possible to do this from the JDBC interface?

Is there any other options I can consider ?

Thanks for your help!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

Reply via email to