locking needed for parsing & planning - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | locking needed for parsing & planning |
Date | |
Msg-id | 5938.943213626@sss.pgh.pa.us Whole thread Raw |
List | pgsql-hackers |
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
pgsql-hackers by date: