PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent

On Sep 1, 2021, at 8:08 AM, FOUTE K. Jaurès <jauresfoute@gmail.com> wrote:

Hello,

I have a strange issue in a production database on a customer and need to help to know how to solve the problem.

Any select on the table other than pg_catalog table throws the error FATAL: catalog is missing 1 attribute(s) for

Any idea how to solve this issue is really appreciated????

Any chance there’s a version mis-match between client and server?

No, I am using PostgreSQL 12 (Server and Client)

Le mer. 1 sept. 2021 à 16:12, Rob Sargent <robjsargent@gmail.com> a écrit :

On Sep 1, 2021, at 8:08 AM, FOUTE K. Jaurès <jauresfoute@gmail.com> wrote:

Hello,

I have a strange issue in a production database on a customer and need to
help to know how to solve the problem.

Any select on the table other than pg_catalog table throws the error FATAL:
catalog is missing 1 attribute(s) for

Any idea how to solve this issue is really appreciated????

Any chance there’s a version mis-match between client and server?

--
Jaurès FOUTE

On Sep 1, 2021, at 8:14 AM, FOUTE K. Jaurès <jauresfoute@gmail.com> wrote:

No, I am using PostgreSQL 12 (Server and Client)

Le mer. 1 sept. 2021 à 16:12, Rob Sargent <robjsargent@gmail.com <mailto:robjsargent@gmail.com>> a écrit :

On Sep 1, 2021, at 8:08 AM, FOUTE K. Jaurès <jauresfoute@gmail.com <mailto:jauresfoute@gmail.com>> wrote:

Hello,

I have a strange issue in a production database on a customer and need to help to know how to solve the problem.

Any select on the table other than pg_catalog table throws the error FATAL: catalog is missing 1 attribute(s) for

Any idea how to solve this issue is really appreciated????

Any chance there’s a version mis-match between client and server?

from the title it looks like you have a problematic index if not a corrupted table. Can you re-index the table for that pkey?

On Wed, Sep 1, 2021 at 8:08 AM FOUTE K. Jaurès <jauresfoute@gmail.com>
wrote:

Any idea how to solve this issue is really appreciated????

Have you restarted the server?

In pg_attribute for one of the problematic tables are all of the columns
present that should be (i.e., is this a catalog contents error or, say, a
relation cache or lookup failure)?

The client application shouldn't have anything to do with these errors -
the server is doing all of the work.

David J.

On 9/1/21 8:08 AM, FOUTE K. Jaurès wrote:

Hello,

I have a strange issue in a production database on a customer and need
to help to know how to solve the problem.

Any select on the table other than pg_catalog table throws the error
FATAL: catalog is missing 1 attribute(s) for

To be clear this only happens when you select a particular user table?

Also the error message should have end 'for relid <id>', can you provide
that information.

Any idea how to solve this issue is really appreciated????

--
Jaurès FOUTE

--
Adrian Klaver
adrian.klaver@aklaver.com

I already restart the server (x3)

Le mer. 1 sept. 2021 à 16:25, David G. Johnston <david.g.johnston@gmail.com>
a écrit :

On Wed, Sep 1, 2021 at 8:08 AM FOUTE K. Jaurès <jauresfoute@gmail.com>
wrote:

Any idea how to solve this issue is really appreciated????

Have you restarted the server?

In pg_attribute for one of the problematic tables are all of the columns
present that should be (i.e., is this a catalog contents error or, say, a
relation cache or lookup failure)?

The client application shouldn't have anything to do with these errors -
the server is doing all of the work.

David J.

--
Jaurès FOUTE

For all table except the pg_catalo table.
The dump on the database is not possible too. (pg_dump: error: invalid
column numbering in table "xxxxxx")

Le mer. 1 sept. 2021 à 16:27, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :

On 9/1/21 8:08 AM, FOUTE K. Jaurès wrote:

Hello,

I have a strange issue in a production database on a customer and need
to help to know how to solve the problem.

Any select on the table other than pg_catalog table throws the error
FATAL: catalog is missing 1 attribute(s) for

To be clear this only happens when you select a particular user table?

Also the error message should have end 'for relid <id>', can you provide
that information.

Any idea how to solve this issue is really appreciated????

--
Jaurès FOUTE

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Jaurès FOUTE

On Wed, Sep 1, 2021 at 8:29 AM FOUTE K. Jaurès <jauresfoute@gmail.com>
wrote:

I already restart the server (x3)

Ok. During server startup (or shutdown for that matter) are there any
warnings or errors in the log file? Is there anything in the server logs
from around the time this started to occur?

You haven't commented on the contents of the catalogs being accurate or not
yet. Assuming they are not (seems likely) I'd say the decision is about
how much effort to spend figuring out why they are incorrect versus just
restoring from backups (you can/should save the PostgreSQL installation and
data once the server is shutdown).

WAL may provide some clues if continued research is needed (in addition to
whatever logs or other data you collect) though that is not an area I am
practiced in.

David J.

=?UTF-8?Q?FOUTE_K=2E_Jaur=C3=A8s?= <jauresfoute@gmail.com> writes:

For all table except the pg_catalo table.
The dump on the database is not possible too. (pg_dump: error: invalid
column numbering in table "xxxxxx")

It seems that pg_attribute is messed up. If you are really lucky,
it might be only a problem in pg_attribute's indexes, in which case
reindexing pg_attribute would fix it. However, I recommend proceeding
on the assumption that you have possibly-irrecoverable damage. Before
you do ANYTHING, make a complete filesystem-level backup of the cluster
(stop the server, then do "tar cf backup.tar $PGDATA" or equivalent).

Even if reindexing pg_attribute seems to fix it, I'd counsel then doing
a pg_dump and restore, in hopes of curing any other problems that may
have stemmed from the same root cause.

Speaking of root cause, have you had any crashes lately? Is your
Postgres up-to-date? How about the underlying OS?

regards, tom lane

Hello Tom,

Thank You for your Answer. It solves the problem.
Thank you very much for your support.

Le mer. 1 sept. 2021 à 16:46, Tom Lane <tgl@sss.pgh.pa.us> a écrit :

=?UTF-8?Q?FOUTE_K=2E_Jaur=C3=A8s?= <jauresfoute@gmail.com> writes:

For all table except the pg_catalo table.
The dump on the database is not possible too. (pg_dump: error: invalid
column numbering in table "xxxxxx")

It seems that pg_attribute is messed up. If you are really lucky,
it might be only a problem in pg_attribute's indexes, in which case
reindexing pg_attribute would fix it. However, I recommend proceeding
on the assumption that you have possibly-irrecoverable damage. Before
you do ANYTHING, make a complete filesystem-level backup of the cluster
(stop the server, then do "tar cf backup.tar $PGDATA" or equivalent).

Even if reindexing pg_attribute seems to fix it, I'd counsel then doing
a pg_dump and restore, in hopes of curing any other problems that may
have stemmed from the same root cause.

Speaking of root cause, have you had any crashes lately? Is your
Postgres up-to-date? How about the underlying OS?

regards, tom lane

--
Jaurès FOUTE

After running: REINDEX TABLE pg_catalog.pg_attribute ;

Le mer. 1 sept. 2021 à 17:54, FOUTE K. Jaurès <jauresfoute@gmail.com> a
écrit :

Hello Tom,

Thank You for your Answer. It solves the problem.
Thank you very much for your support.

Le mer. 1 sept. 2021 à 16:46, Tom Lane <tgl@sss.pgh.pa.us> a écrit :

=?UTF-8?Q?FOUTE_K=2E_Jaur=C3=A8s?= <jauresfoute@gmail.com> writes:

For all table except the pg_catalo table.
The dump on the database is not possible too. (pg_dump: error: invalid
column numbering in table "xxxxxx")

It seems that pg_attribute is messed up. If you are really lucky,
it might be only a problem in pg_attribute's indexes, in which case
reindexing pg_attribute would fix it. However, I recommend proceeding
on the assumption that you have possibly-irrecoverable damage. Before
you do ANYTHING, make a complete filesystem-level backup of the cluster
(stop the server, then do "tar cf backup.tar $PGDATA" or equivalent).

Even if reindexing pg_attribute seems to fix it, I'd counsel then doing
a pg_dump and restore, in hopes of curing any other problems that may
have stemmed from the same root cause.

Speaking of root cause, have you had any crashes lately? Is your
Postgres up-to-date? How about the underlying OS?

regards, tom lane

--
Jaurès FOUTE

--
Jaurès FOUTE