I have been investigating Oleg's report of backend crashes induced by
drop index/vacuum/create index in parallel with table updates.  The
problem is pretty simple: there's not enough of an interlock between
DROP INDEX and normal activities on tables.  In particular, it's
entirely possible for DROP INDEX to drop an index that someone else
is busy scanning or adding entries to :-(

I find that things get a *lot* more stable if DROP INDEX grabs an
exclusive lock on the parent table, not just on the target index.
That prevents the DROP from occurring while someone is actively
scanning or updating the index as part of a query on the parent
table.  However, I still get infrequent complaints like
ERROR:  IndexSelectivity: index 292801 not found
The reason for that is that the parser and planner don't have any
kind of lock on the tables they are working with --- we don't grab
locks until the executor starts.  So, the planner can look around
for indexes for the tables in the query, find some, and then discover
that the indexes have gone away when it tries to do something with them.

I was thinking about solving this by moving lock-acquisition out of
the executor and up to the start of the planning process; if we have
some kind of lock on every table mentioned in the query, we can be
sure that DROP INDEX won't be able to remove any indexes that we are
working with in the planner.

However, that doesn't completely eliminate this class of problems,
because the parser and rewriter are still exposed.  They don't care
about indexes, but they do care about table definitions.  For example,
"SELECT * FROM table" could generate an obsolete expansion for "*"
if an ALTER TABLE ADD/DROP/RENAME COLUMN commits after parsing starts.

Grabbing locks in the parser doesn't seem like a hot idea, mainly
because the parser doesn't have full information --- it has no idea what
will happen downstream in the rewriter.  If the parser grabs a read-type
lock on some table, and then a rewrite rule requires us to get a
stronger lock on that table, then we've just created a potential for
deadlock against some other backend.  So I'm not sure there is any cure
that's not worse than the disease for the parser.  (Most of the bad
scenarios here require ALTER TABLE functionality that we don't have
anyway.)

I have no idea what can go wrong in the rewriter if tables are being
altered underneath it, nor whether it's practical to grab locks to
prevent that.  Jan, at what point can we be sure we know the type
of lock needed for each table used in a query?  The planner has
complete info when it starts, but can the rewriter know this before
it's too late?

I'm going to commit the change to make DROP INDEX lock the parent table,
but I wanted to see if anyone has any comments or better ideas about
obtaining execution locks in the planner instead of the executor...

			regards, tom lane

Sun, 21 Nov 1999 14:47:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
locking needed for parsing & planning