Tom Lane wrote:
I'm still looking for an example that demonstrates why this is a common problem that we need to worry about. ISTM that if an FK reference is hit when there are still zero entries in the referenced table, that insertion will fail anyway, and so people wouldn't try to load data in such an order.
Tom,
We're filling pairs of tables with rows having nearly a one-to-one mapping; very rarely, the second table will have multiple rows corresponding to one row in the first table. When we insert the first row in the second table, therefore, we've just put the corresponding row into the first table, so the foreign key constraint is satisfied.
I can't say how common this sort of thing will be. It appears to me that BUG #1541 is similar to what we're seeing, and a search of the mailing lists also turns up this message:
http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php
which also describes symptoms similar to what I'm seeing.
We could band-aid this in 8.0 as previously suggested (have the planner assume > 0 pages when it sees actually 0 pages) but without seeing a concrete example I can't tell if that will fix the complaint or not.
It sounds like this could work for us, if it would disable sequential searches into a table which grows from 0 to >60,000 rows in one session. Is breaking and re-establishing the database session the best workaround, or is there a better way to provide a hint to the planner?
Regards,
Keith Browne [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
