cbw <cbwhitebu@gmail.com> writes:
> I have a DO block that has a couple of inserts (copying large amounts
> of data from staging tables) that I am passing to the server using
> JDBC,
> When I execute the inserts independently, they work fine. But when I
> submit them as part of the do block, the backend goes into
> trigger.c:afterTriggerInvokeEvents and never returns. This happens
> after the second insert starts running.
Have you got deferred uniqueness or exclusion constraints on the
target table?
If so, perhaps a plausible theory is that when you submit the queries
separately, the unique_key_recheck trigger is never fired at all --- but
if they are in the same transaction, then recheck events get queued
because the index can't tell whether the earlier row should be treated
as committed. This requires some assumptions about the table schema
(which you haven't shown us) but it's a bit hard to see why the second
query would act differently in the two contexts otherwise.
A variant of that theory is that foreign key trigger firings are being
skipped in one case but not the other; but offhand I think those
optimizations only apply to update/delete cases not inserts. Anyway
that still requires some assumptions about moving parts that you
haven't shown us.
The short answer very likely is going to be that you need to perform
the queries as separate transactions, or the second one drowns in
trigger overhead. Tracing down exactly why might not be worth a
lot of trouble.
regards, tom lane