Re: drop view race condition

On Sunday December 5 2004 12:54, Ed L. wrote:
> There appears to be a race condition in which dropping a view and
> replacing it with an identically-named table inside one transaction while
> other transactions are concurrently updating the view/table causes the
> following error:
>
> WARNING:  Error occurred while executing PL/pgSQL function f
> WARNING:  line 16 at SQL statement
> ERROR:  RelationClearRelation: relation NNN deleted while still in use
>
> Here's basically how to reproduce this bug:
>
> create table foo_1...
> create table foo_2...  (identical to foo_1)
> create view foo as select * from foo_1 union all select * from foo_2;
> create "do instead" rule on foo view that redirects foo inserts into
> foo_2;
>
> create table bar...
> create trigger+func that inserts into foo on insert to bar;
>
> Next, start a couple of processes that continually insert one row at a
> time into bar as fast as possible (they should result in rows inserted
> into foo_1);
>
> Finally, run the following in a transaction:
>
>     begin;
>         drop view foo cascade;
>         alter table foo_2 rename to foo;
>     end;
>
> Sometimes this works without error, but many times it will result in the
> above error.  Wratcheting up the number of processes writing to bar seems
> to increase the odds of encountering the race condition error.
>
> Ed

Forgot to mention this occurs on 7.3.4 and 7.4.6.