Обсуждение: Columns defined not matching pg_attibute
Hey guys, having a bizarre issue I have not seen before and couldn't locate anything via Google (though I might not be choosing the correct phrasing). We have a table being dumped via pg_dumpall that is receiving the error...
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: column "raic_state_opt_switch" of relation "rai_control" does not exist
pg_dump: The command was: COPY public.rai_control (raic_arid, raic_fed_num, raic_raifac_id, raic_nat_prov_id, raic_fac_name, raic_fac_addr1, raic_fac_addr2, raic_fac_city, raic_fac_state, raic_fac_zip, raic_cms_cert_num, raic_fac_contact, raic_fac_phone, raic_fac_ext, raic_mdsv3_dt, raic_nh_pattype1, raic_nh_pattype2, raic_nh_pattype3, raic_nh_pattype4, raic_nh_pattype5, raic_nh_provnum1, raic_nh_provnum2, raic_nh_provnum3, raic_nh_provnum4, raic_nh_provnum5, raic_tickler_fmt, raic_mds_locking, raic_st_auth, raic_allres_cc_rsm, raic_care_plan_prompt, raic_use_mult_ltc_dept, raic_dept, raic_dept_fac_id, raic_dept_fed_num, raic_dept_fac_name, raic_dept_provnum, raic_dept_natprovid, raic_fed_rate_table, raic_fed_calc_type, raic_state_rug_code, raic_state_calc_type, raic_state_opt_switch, raic_state_prov_num) TO stdout;
pg_dumpall: pg_dump failed on database "site", exiting.
A "\d" on said table returns...
Table "public.rai_control"
Column | Type | Modifiers
------------------------+-------------------------+-----------
raic_arid | numeric(4,0) | not null
raic_fed_num | character varying(12) |
raic_raifac_id | character varying(16) |
raic_nat_prov_id | character varying(10) |
raic_fac_name | character varying(30) |
raic_fac_addr1 | character varying(30) |
raic_fac_addr2 | character varying(30) |
raic_fac_city | character varying(20) |
raic_fac_state | character varying(2) |
raic_fac_zip | character varying(11) |
raic_cms_cert_num | character varying(12) |
raic_fac_contact | character varying(30) |
raic_fac_phone | character varying(10) |
raic_fac_ext | character varying(5) |
raic_mdsv3_dt | date |
raic_nh_pattype1 | character varying(1) |
raic_nh_pattype2 | character varying(1) |
raic_nh_pattype3 | character varying(1) |
raic_nh_pattype4 | character varying(1) |
raic_nh_pattype5 | character varying(1) |
raic_nh_provnum1 | character varying(15) |
raic_nh_provnum2 | character varying(15) |
raic_nh_provnum3 | character varying(15) |
raic_nh_provnum4 | character varying(15) |
raic_nh_provnum5 | character varying(15) |
raic_tickler_fmt | character varying(1) |
raic_mds_locking | character varying(1) |
raic_st_auth | character varying(1) |
raic_allres_cc_rsm | character varying(1) |
raic_care_plan_prompt | character varying(1) |
raic_use_mult_ltc_dept | character varying(1) |
raic_dept | numeric(3,0)[] |
raic_dept_fac_id | character varying(16)[] |
raic_dept_fed_num | character varying(12)[] |
raic_dept_fac_name | character varying(30)[] |
raic_dept_provnum | character varying(14)[] |
raic_dept_natprovid | character varying(10)[] |
raic_fed_rate_table | character varying(1) |
raic_fed_calc_type | character varying(1) |
raic_state_rug_code | character varying(2) |
raic_state_calc_type | character varying(1) |
raic_state_opt_switch | character varying(1) |
raic_state_prov_num | character varying(15) |
Indexes:
"raic_pkey" PRIMARY KEY, btree (raic_arid)
and "SELECT * FROM pg_attribute WHERE attrelid IN (SELECT oid FROM pg_class WHERE relname = 'rai_control');" returns...
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | attt
ypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount
----------+------------------------+----------+---------------+--------+--------+----------+-------------+-----
------+----------+------------+----------+------------+-----------+--------------+------------+-------------
21198 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 |
-1 | t | p | i | t | f | f | t | 0
21198 | cmax | 29 | 0 | 4 | -6 | 0 | -1 |
-1 | t | p | i | t | f | f | t | 0
21198 | xmax | 28 | 0 | 4 | -5 | 0 | -1 |
-1 | t | p | i | t | f | f | t | 0
21198 | cmin | 29 | 0 | 4 | -4 | 0 | -1 |
-1 | t | p | i | t | f | f | t | 0
21198 | xmin | 28 | 0 | 4 | -3 | 0 | -1 |
-1 | t | p | i | t | f | f | t | 0
21198 | ctid | 27 | 0 | 6 | -1 | 0 | -1 |
-1 | f | p | s | t | f | f | t | 0
21198 | raic_arid | 1700 | -1 | -1 | 1 | 0 | -1 | 2
62148 | f | m | i | t | f | f | t | 0
21198 | raic_fed_num | 1043 | -1 | -1 | 2 | 0 | -1 |
16 | f | x | i | f | f | f | t | 0
21198 | raic_raifac_id | 1043 | -1 | -1 | 3 | 0 | -1 |
20 | f | x | i | f | f | f | t | 0
21198 | raic_nat_prov_id | 1043 | -1 | -1 | 4 | 0 | -1 |
14 | f | x | i | f | f | f | t | 0
21198 | raic_fac_name | 1043 | -1 | -1 | 5 | 0 | -1 |
34 | f | x | i | f | f | f | t | 0
21198 | raic_fac_addr1 | 1043 | -1 | -1 | 6 | 0 | -1 |
34 | f | x | i | f | f | f | t | 0
21198 | raic_fac_addr2 | 1043 | -1 | -1 | 7 | 0 | -1 |
34 | f | x | i | f | f | f | t | 0
21198 | raic_fac_city | 1043 | -1 | -1 | 8 | 0 | -1 |
24 | f | x | i | f | f | f | t | 0
21198 | raic_fac_state | 1043 | -1 | -1 | 9 | 0 | -1 |
6 | f | x | i | f | f | f | t | 0
21198 | raic_fac_zip | 1043 | -1 | -1 | 10 | 0 | -1 |
15 | f | x | i | f | f | f | t | 0
21198 | raic_cms_cert_num | 1043 | -1 | -1 | 11 | 0 | -1 |
16 | f | x | i | f | f | f | t | 0
21198 | raic_fac_contact | 1043 | -1 | -1 | 12 | 0 | -1 |
34 | f | x | i | f | f | f | t | 0
21198 | raic_fac_phone | 1043 | -1 | -1 | 13 | 0 | -1 |
14 | f | x | i | f | f | f | t | 0
21198 | raic_fac_ext | 1043 | -1 | -1 | 14 | 0 | -1 |
9 | f | x | i | f | f | f | t | 0
21198 | raic_mdsv3_dt | 1082 | -1 | 4 | 15 | 0 | -1 |
-1 | t | p | i | f | f | f | t | 0
21198 | raic_nh_pattype1 | 1043 | -1 | -1 | 16 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_nh_pattype2 | 1043 | -1 | -1 | 17 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_nh_pattype3 | 1043 | -1 | -1 | 18 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_nh_pattype4 | 1043 | -1 | -1 | 19 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_nh_pattype5 | 1043 | -1 | -1 | 20 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_nh_provnum1 | 1043 | -1 | -1 | 21 | 0 | -1 |
19 | f | x | i | f | f | f | t | 0
21198 | raic_nh_provnum2 | 1043 | -1 | -1 | 22 | 0 | -1 |
19 | f | x | i | f | f | f | t | 0
21198 | raic_nh_provnum3 | 1043 | -1 | -1 | 23 | 0 | -1 |
19 | f | x | i | f | f | f | t | 0
21198 | raic_nh_provnum4 | 1043 | -1 | -1 | 24 | 0 | -1 |
19 | f | x | i | f | f | f | t | 0
21198 | raic_nh_provnum5 | 1043 | -1 | -1 | 25 | 0 | -1 |
19 | f | x | i | f | f | f | t | 0
21198 | raic_tickler_fmt | 1043 | -1 | -1 | 26 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_mds_locking | 1043 | -1 | -1 | 27 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_st_auth | 1043 | -1 | -1 | 28 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_allres_cc_rsm | 1043 | -1 | -1 | 29 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_care_plan_prompt | 1043 | -1 | -1 | 30 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_use_mult_ltc_dept | 1043 | -1 | -1 | 31 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_dept | 1231 | -1 | -1 | 32 | 1 | -1 | 1
96612 | f | x | i | f | f | f | t | 0
21198 | raic_dept_fac_id | 1015 | -1 | -1 | 33 | 1 | -1 |
20 | f | x | i | f | f | f | t | 0
21198 | raic_dept_fed_num | 1015 | -1 | -1 | 34 | 1 | -1 |
16 | f | x | i | f | f | f | t | 0
21198 | raic_dept_fac_name | 1015 | -1 | -1 | 35 | 1 | -1 |
34 | f | x | i | f | f | f | t | 0
21198 | raic_dept_provnum | 1015 | -1 | -1 | 36 | 1 | -1 |
18 | f | x | i | f | f | f | t | 0
21198 | raic_dept_natprovid | 1015 | -1 | -1 | 37 | 1 | -1 |
14 | f | x | i | f | f | f | t | 0
21198 | raic_fed_rate_table | 1043 | -1 | -1 | 38 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_fed_calc_type | 1043 | -1 | -1 | 39 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_state_rug_code | 1043 | -1 | -1 | 40 | 0 | -1 |
6 | f | x | i | f | f | f | t | 0
21198 | raic_state_calc_type | 1043 | -1 | -1 | 41 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_state_opt_switch | 1043 | -1 | -1 | 42 | 0 | -1 |
5 | f | x | i | f | f | f | t | 0
21198 | raic_state_prov_num | 1043 | -1 | -1 | 43 | 0 | -1 |
19 | f | x | i | f | f | f | t | 0
(49 rows)
yet when I run "SELECT * FROM rai_control;" I get the header of...
raic_arid | raic_fed_num | raic_raifac_id | raic_nat_prov_id | raic_fac_name | raic_fac_addr1 | raic_fac_addr2 | raic_fac_city | raic_fac_state | raic_fac_zip | raic_cms_cert_num | raic_fac_contact | raic_fac_phone | raic_fac_ext | raic_mdsv3_dt | raic_nh_pattype1 | raic_nh_pattype2 | raic_nh_pattype3 | raic_nh_pattype4 | raic_nh_pattype5 | raic_nh_provnum1 | raic_nh_provnum2 | raic_nh_provnum3 | raic_nh_provnum4 | raic_nh_provnum5 | raic_tickler_fmt | raic_mds_locking | raic_st_auth | raic_allres_cc_rsm | raic_care_plan_prompt | raic_use_mult_ltc_dept | raic_dept | raic_dept_fac_id | raic_dept_fed_num | raic_dept_fac_name | raic_dept_provnum | raic_dept_natprovid | raic_fed_rate_table | raic_fed_calc_type | raic_state_rug_code | raic_state_calc_type
-----------+--------------+----------------+------------------+----------------------+-----------------+----------------+---------------+----------------+--------------+-------------------+------------------+----------------+--------------+---------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+--------------+--------------------+-----------------------+------------------------+---------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------+---------------------+----------------------
which is missing the last 2 columns defined by pg_attribute and \d.
Any ideas on what I can look at or do to correct this problem (if it can be corrected)?
We are running postgres 8.3.4 on 64 bit Red Hat kernel release 2.6.18-164.el5
Thanks for the help!
- Justin
On 12/12/2012 07:46 AM, Justin Arnold wrote: > Hey guys, having a bizarre issue I have not seen before and couldn't > locate anything via Google (though I might not be choosing the correct > phrasing). We have a table being dumped via pg_dumpall that is receiving > the error... > > pg_dump: SQL command failed > which is missing the last 2 columns defined by pg_attribute and \d. > Any ideas on what I can look at or do to correct this problem (if it can > be corrected)? Well one suggestion is to upgrade to the latest version of 8.3.x; 8.3.22. There are a lot of bug fixes between 8.3.4 and 8.3.22. If you want to look before you leap go through the release notes for each of the minor versions and see if anything fits. > > We are running postgres 8.3.4 on 64 bit Red Hat kernel > release 2.6.18-164.el5 > > Thanks for the help! > > - Justin -- Adrian Klaver adrian.klaver@gmail.com
Justin Arnold <jharnold81@gmail.com> writes:
> Hey guys, having a bizarre issue I have not seen before and couldn't locate
> anything via Google (though I might not be choosing the correct phrasing).
Apparently, those last couple of rows in pg_attribute are visible to a
regular MVCC scan but not to SnapshotNow scans. This suggests
transaction wraparound or maybe broken hint-bit settings. Try doing a
VACUUM on pg_attribute, then VACUUM FREEZE pg_attribute. It's hard to
predict in advance of the experiment whether you'll end up with the rows
visible or not-visible, so you might lose the data in those columns ---
but apparently you're not using them anyway, or you'd be seeing more
stuff fall over than just pg_dump.
If you need to be sure that the rows end up visible, another line of
attack would be to do a manual no-op UPDATE on just those rows.
> We are running postgres 8.3.4 on 64 bit Red Hat kernel
As noted elsewhere, this is an old version with many known bugs.
regards, tom lane