CODEWITHSUNDEEP
sqlpostgresqlhibernate
Traspler
Traspler

Reputation: 865

Changing column type in Postgres leads to pg_attrdef warnings

We are trying to change a numeric(10,2) column type to a numeric(17,2) type. When we do it with the naïve approach:

ALTER TABLE table_a ALTER COLUMN col_a SET DATA TYPE numeric(17,2);

We get WARNINGs which we don't understand:

WARNING:  1 attrdef record(s) missing for rel table_a
WARNING:  generating possibly-non-unique OID for "pg_attrdef"

The column type does get changed correctly to numeric(17,2) (at least \d table_a tells us so) and I can't see anything wrong with the resulting table but the warnings stay.

Our table definition looks something like:

                         Table "public.table_a"
         Column          |          Type          | Collation | Nullable | Default
-------------------------+------------------------+-----------+----------+---------
 col_a                   | numeric(10,2)          |           | not null | 0.00

After consulting the postgres documentation we tried it for another column by first dropping the Default constraint:

ALTER TABLE table_a ALTER COLUMN col_b DROP DEFAULT;
ALTER TABLE table_a ALTER COLUMN col_b SET DATA TYPE numeric(17,2);
ALTER TABLE table_a ALTER COLUMN col_b SET DEFAULT 0.00;

Leading to even more WARNINGs:

WARNING:  2 attrdef record(s) missing for rel table_a
WARNING:  generating possibly-non-unique OID for "pg_attrdef"

As you can see after running the query on another column we are now missing 2 records in pg_attrdef. The WARNINGs seem to persist and randomly show up when running queries.

We inspected the pg_attrdef table to see if we can find anything an noticed that the :location field in the adbin internal representation has been set to -1:

adrelid | 37294
adnum   | 37
adbin   | {FUNCEXPR :funcid 1703 :funcresulttype 1700 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 :args ({CONST :consttype 1700 :consttypmod -1 :constcollid 0 :constlen -1 :constbyval false :constisnull false :location -1 :constvalue 6 [ 24 0 0 0 0 -127 ]} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location -1 :constvalue 4 [ 6 0 17 0 0 0 0 0 ]}) :location -1}
adsrc   | 0.00

But compiling it with:

select pg_get_expr(pa.adbin, 'table_a'::regclass) from pg_attrdef pa where adrelid = 37294 and adnum = 37;

works and returns the correct 0.00 value.

Some further background info: The table was generated by Hibernate and the column definition looks as follows:

@Column(nullable = false, columnDefinition = "Decimal(10,2) default '0.00'")
private Double col_a = 0.0;

I am running the queries on a PostreSQL 10.0 DB but want to run it on an 8.4 DB later.

Can someone help us figure out what these WARNINGs mean and how we can avoid them?

Upvotes: 3

Views: 701

Answers (1)

Neil Anderson
Neil Anderson

Reputation: 1375

Something has happened to the pg_catalog.attrdef table on your server such that the unique index that should be present on the oid column of attrdef is missing.

You can see the warning being raised here https://github.com/postgres/postgres/blob/master/src/backend/catalog/catalog.c#L312

I read it as that the statements you are issuing are attempting to set a default so they are writing to the attrdef table. Postgres has a built in assumption that catalog tables will have a unique index on the oid column and outputs a warning if one is missing. I couldn't recreate on my v10 box so I think someone has dropped the unique index on your specific instance.

The index that appears to be missing is called 'pg_attrdef_oid_index' so if that's not available in pg_catalog.pg_class there is the issue.

There are probably no ramifications other than the warning you are seeing.

Neil

Upvotes: 1

Related Questions