Postgres Pro
Downloads
Home   >   mailing lists

Thread: Deleting an index confuses SQL dialogue window

Deleting an index confuses SQL dialogue window

From
Erwin Brandstetter
Date:
Hi Dave,    the last thing I want is to mess with your blood pressure, but I 
just might have another one here ...

If I run a query in the SQL dialogue window which utilizes an index and 
delete this index afterwards, the same query will result in an error 
after that.

FEHLER: could not open relation with OID 1134569
SQL state: XX000
Context: SQL-Anweisung »SELECT  (...)

Recreating the index under the same name does not help, as pgAdmin seems 
to be looking for the OID of the index. It seems the query plan is being 
saved and executed over and over again. Executing other queries in the 
same SQL dialogue window works fine. Trying the original query keeps 
failing.
Executing the same query in a new SQL dialogue window works fine.

Not sure if this is a bug or by design. It certainly looks like a bug to me.


Regards
Erwin

Re: Deleting an index confuses SQL dialogue window

From
Dave Page
Date:

> If I run a query in the SQL dialogue window which utilizes an index and delete
> this index afterwards, the same query will result in an error after that.>
>
> FEHLER: could not open relation with OID 1134569
> SQL state: XX000
> Context: SQL-Anweisung »SELECT  (...)
>
> Recreating the index under the same name does not help, as pgAdmin seems to be
> looking for the OID of the index. It seems the query plan is being saved and
> executed over and over again. Executing other queries in the same SQL dialogue
> window works fine. Trying the original query keeps failing.
>
> Executing the same query in a new SQL dialogue window works fine.
>
> Not sure if this is a bug or by design. It certainly looks like a bug to me.

That error is coming from PostgreSQL - the query tool never looks at any
indexes; it just sends the query and displays whatever comes back. What is
the exact sequence of events needed to reproduce this?

Regards, Dave.

Re: Deleting an index confuses SQL dialogue window

From
Erwin Brandstetter
Date:
dpage@vale-housing.co.uk wrote
> That error is coming from PostgreSQL - the query tool never looks at any
> indexes; it just sends the query and displays whatever comes back. What is
> the exact sequence of events needed to reproduce this?
>   

You are right. My queries included plpgsql functions and I stubled 
across a well documented side effect of plpgsql storing execution plans 
for the life of a database connection.
http://www.postgresql.org/docs/8.1/static/plpgsql.html#PLPGSQL-OVERVIEW
Consequently I could reproduce the same effect with psql.

So it is by design. Sorry for the noise.


Regards
Erwin