Re: [PATCHES] Patch for dependency traversal during DROP

I wrote:
> The attached patch rewrites DROP recursion according to my sketch here:
> http://archives.postgresql.org/pgsql-hackers/2008-05/msg00301.php

> I was afraid while writing the patch that it might be too slow due to
> reliance on simple linear list searching in ObjectAddresses lists ---
> that means that deleting N objects is O(N^2),

I did some investigation with gprof. In a test case involving dropping
about 13000 objects (basically, replicating the regression "public"
schema 10 times with a common owner, and then doing DROP OWNED BY),
I found that object_address_present_add_flags() accounted for about 4
seconds of CPU out of a total elapsed runtime of 60 seconds.
Extrapolating, that function would be accounting for a third of the
runtime at 100K objects and 85% of the runtime at 1M objects. So unless
anyone foresees people routinely dropping millions of objects at a time,
it seems we don't need to bother improving the ObjectAddresses search
algorithm.

> Another possible objection to this patch is that it takes an awful lot
> of locks on things that we never locked before; a large DROP operation
> could easily run out of locktable shared memory when it did not before.
> That's fixable by increasing max_locks_per_transaction, but I wonder
> whether there will be any push-back about it.

This, on the other hand, might be a real problem --- I had to double the
default value of max_locks_per_transaction to run the 13K-objects
example.

I'm toying with the idea of not taking a deletion lock when traversing
an INTERNAL dependency, on the grounds that no one could be deleting
the dependent object anyway unless they have lock on its owner object ---
which we already have. This would make for a noticeable reduction in
the number of new locks taken as a result of this patch; for instance
we'd not bother locking the rowtype of a relation when dropping the
relation. Can anyone think of a case where this would go wrong?

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches