Skip site navigation (1) Skip section navigation (2)

Re: Patch for dependency traversal during DROP

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-patches(at)postgresql(dot)org
Subject: Re: Patch for dependency traversal during DROP
Date: 2008-06-08 19:14:13
Message-ID: 7995.1212952453@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-patches
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

In response to

pgsql-patches by date

Next:From: Alvaro HerreraDate: 2008-06-08 22:26:26
Subject: Re: GIN improvements
Previous:From: Tom LaneDate: 2008-06-08 16:26:06
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group