Thread: BUG #13034: Inconsistent attrelid field in pg_attribute table after adding columns to table.

The following bug has been logged on the website:

Bug reference:      13034
Logged by:          Alejandro
Email address:      alemagox@gmail.com
PostgreSQL version: 9.3.5
Operating system:   Ubuntu 14.04 64 bits
Description:

Hello!

At my work we are trying to automatically generate some tables based on
already existing ones. For that reason, we need to check different system
tables such as 'pg_attribute', 'pg_class' and 'pg_namespace' so we can
retrieve the datatype of the existing columns.

This error is easily reproducible from scratch by simply connecting the
postgreSQL server as postgres user.

Firstly, I will show the query we are trying to make work. Simply run the
following queries to retrieve the type of 'id' field form a 'test' table
inside a 'test' database:

CREATE DATABASE test;

\c test

CREATE SCHEMA ab;

CREATE TABLE ab.test
(
  id integer NOT NULL
);

SELECT format_type(a.atttypid, a.atttypmod)
    FROM pg_attribute a
        JOIN pg_class b ON (a.attrelid = b.relfilenode)
        JOIN pg_namespace c ON (c.oid = b.relnamespace)
    WHERE b.relname = 'test' AND c.nspname = 'ab' AND a.attname = 'id';

After this last SELECT we receive the following output as expected:

 format_type
-------------
 integer

The problem comes when we now try to add an extra column to 'test' table. We
do it with the following query:

ALTER TABLE ab.test ADD COLUMN time TIMESTAMP WITH TIME ZONE DEFAULT now();

Now, the query with 'format_type' returns empty set.

I have run also a couple of extra queries and the issue seems to be that
'attrelid' field inside 'pg_attribute' table does not get update after the
ALTER TABLE. For example, before running the ALTER TABLE we can run these
queries:

SELECT b.relfilenode FROM pg_class b, pg_namespace c WHERE c.oid =
b.relnamespace AND b.relname='test' AND c.nspname='ab';

Here we receive as output that 'refilenode' has a value of '1861610'. If we
now run the ALTER TABLE query and repeat the previous one we receive a value
of '1861614'. If we try now the query below, we will receive empty set:

SELECT a.* FROM pg_attribute a WHERE a.attrelid=1861614;

But if we repeat the previous query using the number 1861610, we will
receive all the fields of the table including the added 'time' column.

If we change the attrelid field manually we can rerun the 'format_type'
query and we will receive the integer output as expected:

UPDATE pg_attribute SET attrelid=1861614 WHERE attrelid=1861610;

SELECT format_type(a.atttypid, a.atttypmod)
    FROM pg_attribute a
        JOIN pg_class b ON (a.attrelid = b.relfilenode)
        JOIN pg_namespace c ON (c.oid = b.relnamespace)
    WHERE b.relname = 'test' AND c.nspname = 'ab' AND a.attname = 'id';

 format_type
-------------
 integer

As far as I understand, the field should be automatically updated as the
user should not be touching these system tables.

Thank you very much in advance for you attention.

Best regards,
Alejandro

alemagox@gmail.com wrote:

> SELECT format_type(a.atttypid, a.atttypmod)
>     FROM pg_attribute a
>         JOIN pg_class b ON (a.attrelid = b.relfilenode)
>         JOIN pg_namespace c ON (c.oid = b.relnamespace)
>     WHERE b.relname = 'test' AND c.nspname = 'ab' AND a.attname = 'id';

pg_attribute.attrelid must be joined to pg_class.oid, not relfilenode.
The relfilenode changes when the table is rewritten, which happens
during some forms of ALTER TABLE and others.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services