Strange "missing tables" problem

Hello,

Small correction to my previous email :

I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!!!!!

Example :

$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+--------------------------+-------+--------------
import | rebates_products | table | postgres
import | rebates_customers | table | postgres
rma | categories | table | postgres
rma | customers | table | postgres
rma | defauts | table | postgres
rma | providers | table | postgres

No trace of my import.customers table ?????

But if I do :

bw_rma=# SELECT count(*) FROM import.customers;
count
-------
86703
(1 ligne)

My table is there and I can access it !!!

Any hint or help would be greatly appreciated !

I can do without it, but it's a little strange not to be able to list
the objects present in the database...

Denis

hi,

You may try checking:
SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
to what's the status of your table.

Regards,
foo

Denis BUCHER wrote:

Hello,

That's what I found, do you see something inside that looks interesting ?

bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+----------------------------------------------------------
customers | 17013 | 17022 | 10 | 0 | 17021 |
0 | 16202 | 86685 | 0 | 0 | f
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers | 17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)

bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
-----------------+-----------+------------+------------+------------+----------+-------------
bw_import_as400 | clients | postgres | | f | f
| f
rma | clients | postgres | | t | f
| f
(2 lignes)

Thanks a lot for your help :-)

Denis

Wojtek a �crit :

Hello,

It's even more strange :

# \dt customers
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+---------+-------+--------------
rma | customers | table | postgres
(1 ligne)

# \dt import.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
(1 ligne)

# \dt *.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
rma | customers | table | postgres
(2 lignes)

I really don't understand what's happening here ?

Denis

Possible xid rollover problem? (We saw behavior similar to this during a recent
xid rollover fiasco, where tables didn't appear in the various catalogs and psql
catalog commands, but the data was still there.)

What version of PostgreSQL are you on?

If you try a VACUUM on the pg_catalog.pg_class and pg_catalog.pg_tables tables,
do the proper rows come back?

- Bill

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Denis BUCHER
Sent: Sunday, August 23, 2009 8:55 AM
To: Wojtek
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange "missing tables" problem

Hello,

That's what I found, do you see something inside that looks interesting ?

bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl

---------+--------------+---------+----------+-------+-------------+------------
---+----------

+-----------+---------------+---------------+-------------+-------------+-------
--+----------

+-----------+-------------+----------+----------+---------+------------+--------
----+---------

----+----------------+----------------------------------------------------------

customers | 17013 | 17022 | 10 | 0 | 17021 |
0 | 16202 | 86685 | 0 | 0 | f
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers | 17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)

bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename =
'customers';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers

-----------------+-----------+------------+------------+------------+----------+
-------------

Denis BUCHER wrote:

Hello,

It's even more strange :

why, looks logical to me :)

# \dt customers
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+---------+-------+--------------
rma | customers | table | postgres
(1 ligne)

you have 1 'customers' table in 'rma' schema and (my guess) currently
you're connected as main user for this schema

# \dt import.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
(1 ligne)

you have 1 'customers' table in 'import' schema

# \dt *.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
rma | customers | table | postgres
(2 lignes)

you have two tables 'customers', one in schema 'import', the other one
in schema 'rma'

I assume you want to keep two copies of this table, right?

foo

Hello,

Wojtek a �crit :

It's even more strange :

why, looks logical to me :)

# \dt customers
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+---------+-------+--------------
rma | customers | table | postgres
(1 ligne)

you have 1 'customers' table in 'rma' schema and (my guess) currently
you're connected as main user for this schema

Yes but why the import.customers table doesn't appear, then ?

# \dt import.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
(1 ligne)

you have 1 'customers' table in 'import' schema

# \dt *.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
rma | customers | table | postgres
(2 lignes)

you have two tables 'customers', one in schema 'import', the other one
in schema 'rma'

Yes that's correct

I assume you want to keep two copies of this table, right?

Yes, in fact they are two different tables...

Denis

Hello,

# VACUUM pg_catalog.pg_class;
VACUUM
# VACUUM pg_catalog.pg_tables;
ATTENTION: ignore « pg_tables » --- could not execute VACUUM on indexes, views or system tables
VACUUM

Denis

Bill Bartlett a �crit :

Possible xid rollover problem? (We saw behavior similar to this during a recent
xid rollover fiasco, where tables didn't appear in the various catalogs and psql
catalog commands, but the data was still there.)

What version of PostgreSQL are you on?

If you try a VACUUM on the pg_catalog.pg_class and pg_catalog.pg_tables tables,
do the proper rows come back?

- Bill

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Denis BUCHER
Sent: Sunday, August 23, 2009 8:55 AM
To: Wojtek
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange "missing tables" problem

Hello,

That's what I found, do you see something inside that looks interesting ?

bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl

---------+--------------+---------+----------+-------+-------------+------------
---+----------
+-----------+---------------+---------------+-------------+-------------+-------
--+----------
+-----------+-------------+----------+----------+---------+------------+--------
----+---------
----+----------------+----------------------------------------------------------

customers | 17013 | 17022 | 10 | 0 | 17021 |
0 | 16202 | 86685 | 0 | 0 | f
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers | 17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)

bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename =
'customers';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers

-----------------+-----------+------------+------------+------------+----------+
-------------

bw_import_as400 | clients | postgres | | f | f
| f
rma | clients | postgres | | t | f
| f
(2 lignes)

Thanks a lot for your help :-)

Denis

Wojtek a �crit :

hi,

You may try checking:
SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
to what's the status of your table.

Regards,
foo

Denis BUCHER wrote:

Hello,

Small correction to my previous email :

I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!!!!!

Example :

$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+--------------------------+-------+--------------
import | rebates_products | table | postgres
import | rebates_customers | table | postgres
rma | categories | table | postgres
rma | customers | table | postgres
rma | defauts | table | postgres
rma | providers | table | postgres

No trace of my import.customers table ?????

But if I do :

bw_rma=# SELECT count(*) FROM import.customers;
count
-------
86703
(1 ligne)

My table is there and I can access it !!!

Any hint or help would be greatly appreciated !

I can do without it, but it's a little strange not to be able to list
the objects present in the database...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Denis Bucher

--

Denis Bucher Horus Digital Solutions s�rl Each problem has a solution
___________________________________________________________________________
T�l. +41-22-8000625 Fax: +41-22-8000622 www.hsolutions.ch

Denis BUCHER <dbucherml@hsolutions.ch> writes:

I really don't understand what's happening here ?

"\dt customers" will show you the customers table that's visible
according to your search_path setting. Apparently schema "import"
is either not in your search path at all, or behind "rma".

regards, tom lane

Le dimanche 23 août 2009 à 14:26:06, Denis BUCHER a écrit :

Hello,

I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!!!!!

Example :

$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+--------------------------+-------+--------------
import | rebates_products | table | postgres
import | rebates_customers | table | postgres
rma | categories | table | postgres
rma | customers | table | postgres
rma | defauts | table | postgres
rma | providers | table | postgres

No trace of my import.clients table ?????

But if I do :

bw_rma=# SELECT count(*) FROM import.customers;
count
-------
86703
(1 ligne)

My table is there and I can access it !!!

Any hint or help would be greatly appreciated !

I can do without it but, it's a little strange not to be able to list
the objects present in the database...

\d does not show all the objects available in the database. If one is
available in schema A and in schema B, it will be displayed at most once,
depending on your search_path configuration.

I suppose you have something like 'rma, import, ...' for search_path, so it
only displays rma.customers and not impor.customers.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Hello Tom,

Tom Lane a �crit :

Denis BUCHER <dbucherml@hsolutions.ch> writes:

I really don't understand what's happening here ?

"\dt customers" will show you the customers table that's visible
according to your search_path setting. Apparently schema "import"
is either not in your search path at all, or behind "rma".

It is in the search path.

Do you mean that \dt customers shows only the FIRST "customers" table
found ?

If yes, that's a part of the explanation, BUT :
If I do \dt (without specifying a table name), does it lists only ONE
table of each name, and only the one in the first schema present in the
search path.

Denis

Hello,

Guillaume Lelarge a �crit :

I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!!!!!

Example :

$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+--------------------------+-------+--------------
import | rebates_products | table | postgres
import | rebates_customers | table | postgres
rma | categories | table | postgres
rma | customers | table | postgres
rma | defauts | table | postgres
rma | providers | table | postgres

No trace of my import.clients table ?????

But if I do :

bw_rma=# SELECT count(*) FROM import.customers;
count
-------
86703
(1 ligne)

My table is there and I can access it !!!

Any hint or help would be greatly appreciated !

I can do without it but, it's a little strange not to be able to list
the objects present in the database...

\d does not show all the objects available in the database. If one is
available in schema A and in schema B, it will be displayed at most once,
depending on your search_path configuration.

I suppose you have something like 'rma, import, ...' for search_path, so it
only displays rma.customers and not impor.customers.

Yes that's correct. Therefore my "problem" is the "normal" behavior of
\dt. But "normal" means "expected". But I don't find it very
secure/handy, because you expect to see all your tables. Is there a way
to change the behavior of \dt so that it lists ALL tables present in
search path ?

Thanks a lot for your help

Denis

Hi Denis,

Le lundi 24 août 2009 à 10:21:33, Denis BUCHER a écrit :

[...]
I suppose you have something like 'rma, import, ...' for search_path, so
it only displays rma.customers and not impor.customers.

Yes that's correct. Therefore my "problem" is the "normal" behavior of
\dt. But "normal" means "expected". But I don't find it very
secure/handy, because you expect to see all your tables. Is there a way
to change the behavior of \dt so that it lists ALL tables present in
search path ?

No. But you can always take a look a this:

http://radek.cc/2009/08/15/psqlrc-tricks-table-sizes/

You can probably do a \set dt your_query, and then :dt; instead of \dt. ... a
few moments later... I tried and it works.

Put this line in your .psqlrc file:
\set dt '(SELECT n.nspname as \"Schéma\", c.relname as \"Nom\", CASE c.relkind
WHEN \'r\' THEN \'table\' WHEN \'v\' THEN \'vue\' WHEN \'i\' THEN \'index\'
WHEN \'S\' THEN \'séq uence\' WHEN \'s\' THEN \'spécial\' END as \"Type\",
r.rolname as \"Propriétaire\" FROM pg_catalog.pg_class c JOIN
pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN pg_cata
log.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (\'r\',\'\')
AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\') ORDER BY 1,2)'

It should be one line only, and you can add more schemas on the n.nspname
filter (information_schema for example).

Now, I get this with this .psqlrc trick:

guillaume@laptop:~$ psql -q a
a=# set search_path to public, toto;
a=# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+-----+-------+--------------
public | t1 | table | ab1
public | t2 | table | a2
public | t3 | table | postgres
toto | t4 | table | guillaume
(4 lignes)

a=# :dt;
Schéma | Nom | Type | Propriétaire
--------------------+-------------------------+-------+--------------
information_schema | sql_features | table | guillaume
information_schema | sql_implementation_info | table | guillaume
information_schema | sql_languages | table | guillaume
information_schema | sql_packages | table | guillaume
information_schema | sql_sizing | table | guillaume
information_schema | sql_sizing_profiles | table | guillaume
pgagent | pga_exception | table | guillaume
pgagent | pga_job | table | guillaume
pgagent | pga_jobagent | table | guillaume
pgagent | pga_jobclass | table | guillaume
pgagent | pga_joblog | table | guillaume
pgagent | pga_jobstep | table | guillaume
pgagent | pga_jobsteplog | table | guillaume
pgagent | pga_schedule | table | guillaume
public | t1 | table | ab1
public | t2 | table | a2
public | t3 | table | postgres
toto | t1 | table | guillaume
toto | t4 | table | guillaume
(19 lignes)

Hope it helps.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Denis BUCHER <dbucherml@hsolutions.ch> writes:

Yes that's correct. Therefore my "problem" is the "normal" behavior of
\dt. But "normal" means "expected". But I don't find it very
secure/handy, because you expect to see all your tables. Is there a way
to change the behavior of \dt so that it lists ALL tables present in
search path ?

It *is* the expected behavior. The idea is that "\dt foo" should
describe the same table that "select * from foo" would find.
If you want to see all the possible matches for foo, use "\dt *.foo".

regards, tom lane

Tom Lane a �crit :

Denis BUCHER <dbucherml@hsolutions.ch> writes:

Yes that's correct. Therefore my "problem" is the "normal" behavior of
\dt. But "normal" means "expected". But I don't find it very
secure/handy, because you expect to see all your tables. Is there a way
to change the behavior of \dt so that it lists ALL tables present in
search path ?

It *is* the expected behavior. The idea is that "\dt foo" should
describe the same table that "select * from foo" would find.
If you want to see all the possible matches for foo, use "\dt *.foo".

regards, tom lane

Oh yes, now I found the "list all tables" command :

It's :

\dt *.*

Denis