pg_tables anomalies

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_tables anomalies

ribal
My environment is Centos 6, Postgre 10.11.

pgadmin deleted a table called m_rt_temp.

However, it is marked as still present in the catalog tree.


So I checked pg_tables.

select * from pg_catalog.pg_tables

This Table is marked as present.


However, if you execute select, alter, drop, insert, update, it is said that
it does not exist.

But when I create table m_rt_temp

It says that it already exists and fails.


Another unusual situation

select * from pg_catalog.pg_tables where tablename = 'm_rt_temp'
select * from pg_catalog.pg_tables where tablename like 'm_%'

There is no result in this query,

select * from pg_catalog.pg_tables where tablename like '% m_rt_temp%'
select * from pg_catalog.pg_tables where tablename like '% m_rt_temp'
select * from pg_catalog.pg_tables where tablename like '_m_rt_temp'

This query gives the result.


I thought 'm' might have a problem

SELECT *, ASCII (SUBSTR (tablename, 1,1))
FROM pg_catalog.pg_tables
where schemaname = 'public' and tablename like ('% m_rt_temp%')

Confirmation result with query

The first letter is 109 (0x6d), so 'm' is correct.

What should I do in this case?

Help



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html


Reply | Threaded
Open this post in threaded view
|

Re: pg_tables anomalies

Tom Lane-2
ribal <[hidden email]> writes:
> pgadmin deleted a table called m_rt_temp.
> However, it is marked as still present in the catalog tree.

I see no reason to think there is a bug here.  I would bet on one
of several types of user error:

* Your discussion has not mentioned schemas.  There might be more
or less similarly named tables in different schemas, some of which
are in your search_path and some of which aren't.

* "_" is a wildcard in LIKE patterns, making it possible that those
searches are matching more than you think they are.

* Some of your queries appear to be searching for patterns with
spaces in them.  That's allowed in SQL identifiers, but you could
not access such a table name without writing double quotes around it.

But, since you have not shown the actual results of any of these
queries, it's hard to tell exactly what the confusion is from.

                        regards, tom lane