Обсуждение: pg_class.relnamespace NOT IN pg_namespace.oid
Hello, It apperas that I have some entries in pg_class that have relnamespace not appearing in pg_namespace. So pg_dump fails with "pg_dump: schema with OID 52072764 does not exist", I guess. Like the schema in question was dropped, but not cascading to all its members? How to get rid of or fix these damaged entries? Just try to delete from pg_class manually (and then delete files pointed to by their relfilenode)? Or create a new schema and update pg_class.relnamespace to the oid of the new schema and then inspect and drop? Or any other higher-level tools? Server version is 8.4.9. Thanks Irek.
Ireneusz Pluta <ipluta@wp.pl> writes:
> It apperas that I have some entries in pg_class that have relnamespace not appearing in
> pg_namespace. So pg_dump fails with "pg_dump: schema with OID 52072764 does not exist", I guess.
That's a bit disturbing --- do you have any idea what triggered that?
> How to get rid of or fix these damaged entries? Just try to delete from pg_class manually (and then
> delete files pointed to by their relfilenode)? Or create a new schema and update
> pg_class.relnamespace to the oid of the new schema and then inspect and drop?
I'd do the latter I think. Keep in mind that there are probably also
entries in pg_depend linking the tables to the schemas. If your goal is
only to get to a clean dumpable state and then dump and reload the
database, you probably don't need to worry about fixing pg_depend.
However, if you intend to keep on using the database without a reload,
it'd be prudent to make sure pg_depend is straightened out as well.
regards, tom lane
W dniu 2012-02-27 21:59, Tom Lane pisze: > Ireneusz Pluta<i..@wp.pl> writes: >> It apperas that I have some entries in pg_class that have relnamespace not appearing in >> pg_namespace. So pg_dump fails with "pg_dump: schema with OID 52072764 does not exist", I guess. > That's a bit disturbing --- do you have any idea what triggered that? Not exactly. This is an environment where a user running his periodic reports, per logic of his script, for each run creates a new schema which then serves as a separated runtime sandbox. The schema after such a run is left alone uselessly unless one eventualy wants to look into for comparing with other runs or debugging, I guess. Just a subject of further garbage collecting. It may happen that not each run finishes succesfully, particularly in cases of manual runs on a development/debugging course and Ctrl-C hits. The orphaned tables seem to be the same as appearing in another schemas I see here. They are not complete set of what can be seen in another schemas, just two out of much more. >> How to get rid of or fix these damaged entries? Just try to delete from pg_class manually (and then >> delete files pointed to by their relfilenode)? Or create a new schema and update >> pg_class.relnamespace to the oid of the new schema and then inspect and drop? > I'd do the latter I think. Keep in mind that there are probably also > entries in pg_depend linking the tables to the schemas. If your goal is > only to get to a clean dumpable state and then dump and reload the > database, you probably don't need to worry about fixing pg_depend. > However, if you intend to keep on using the database without a reload, > it'd be prudent to make sure pg_depend is straightened out as well. I found rows of missing namespace oid in pg_depend.refobjid. I understand that I update them to oid of a newly created empty schema? But, as in the menatime I played with the case separately on a test database, I found also pg_type entries need fixing. I was not aware of pg_depend, but found that after only tweaking both pg_class, and pg_type the database got dumpable.
Ireneusz Pluta <ipluta@wp.pl> writes:
> W dniu 2012-02-27 21:59, Tom Lane pisze:
>> I'd do the latter I think. Keep in mind that there are probably also
>> entries in pg_depend linking the tables to the schemas. If your goal is
>> only to get to a clean dumpable state and then dump and reload the
>> database, you probably don't need to worry about fixing pg_depend.
>> However, if you intend to keep on using the database without a reload,
>> it'd be prudent to make sure pg_depend is straightened out as well.
> I found rows of missing namespace oid in pg_depend.refobjid. I understand that I update them to oid
> of a newly created empty schema?
Right.
> But, as in the menatime I played with the case separately on a test database, I found also pg_type
> entries need fixing. I was not aware of pg_depend, but found that after only tweaking both pg_class,
> and pg_type the database got dumpable.
Hm. We've seen occasional reports of this sort of behavior (that is,
DROP of a schema failing to cascade to all the contained objects) but
never been able to reproduce it. If you do see it happen again, and
can work out a scenario that causes it (even only intermittently)
we'd love to have a test case.
One possible theory for cascaded drops to fail like that is that the
indexes on pg_depend are corrupt, so you might want to consider
REINDEXing that catalog, just in case.
regards, tom lane
W dniu 2012-02-27 23:57, Tom Lane pisze:
> One possible theory for cascaded drops to fail like that is that the indexes on pg_depend are
> corrupt, so you might want to consider REINDEXing that catalog, just in case.
so before reindexing it would be worth veryfing the theory and check indexes for corruption in their
current state. But I am not sure if I know how to perform these particular checks. The only ones I
could invent myself, pretty naively right now, are as follows, but I am not sure if they give the
information wee look for:
# select * from pg_depend where classid = 1259 and objid in (52072904, 52072932);
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+----------+----------+------------+----------+-------------+---------
1259 | 52072904 | 0 | 2615 | 52072764 | 0 | n
1259 | 52072932 | 0 | 2615 | 52072764 | 0 | n
(2 rows)
Time: 0.637 ms
# EXPLAIN ANALYZE select * from pg_depend where refclassid = 2615 and refobjid = 52072764;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_depend_reference_index on pg_depend (cost=0.00..4.43 rows=1 width=25) (actual
time=14.393..21.852 rows=2 loops=1)
Index Cond: ((refclassid = 2615::oid) AND (refobjid = 52072764::oid))
Total runtime: 21.906 ms
(3 rows)
Time: 22.360 ms
# EXPLAIN ANALYZE select * from pg_depend where classid = 1259 and objid in (52072904, 52072932);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pg_depend (cost=4.70..8.67 rows=2 width=25) (actual time=22.042..22.051
rows=2 loops=1)
Recheck Cond: ((classid = 1259::oid) AND (objid = ANY ('{52072904,52072932}'::oid[])))
-> Bitmap Index Scan on pg_depend_depender_index (cost=0.00..4.70 rows=2 width=0) (actual
time=22.017..22.017 rows=2 loops=1)
Index Cond: ((classid = 1259::oid) AND (objid = ANY ('{52072904,52072932}'::oid[])))
Total runtime: 22.104 ms
(5 rows)
Ireneusz Pluta <ipluta@wp.pl> writes:
> W dniu 2012-02-27 23:57, Tom Lane pisze:
>> One possible theory for cascaded drops to fail like that is that the indexes on pg_depend are
>> corrupt, so you might want to consider REINDEXing that catalog, just in case.
> so before reindexing it would be worth veryfing the theory and check indexes for corruption in their
> current state. But I am not sure if I know how to perform these particular checks. The only ones I
> could invent myself, pretty naively right now, are as follows, but I am not sure if they give the
> information wee look for:
It's difficult to prove anything after the fact. You could check whether
select * from pg_depend where refclassid = 2615 and refobjid = <missing
schema's OID>;
finds entries for all the objects that currently refer to the missing
schema. But there are various strange rules (eg, I think we don't
bother to make a separate entry for a table's pg_type row), and more
to the point what happens today doesn't necessarily prove much about
what happened when the DROP failed. Addition/deletion of other index
entries could mask a failure condition that occurred before.
regards, tom lane
W dniu 2012-02-27 23:57, Tom Lane pisze: > Hm. We've seen occasional reports of this sort of behavior (that is, > DROP of a schema failing to cascade to all the contained objects) but > never been able to reproduce it. If you do see it happen again, and > can work out a scenario that causes it (even only intermittently) > we'd love to have a test case. I think we could try to play here a bit and arrange some possible scenarios with (abnormal) execution of the the script which creates/drops the schemas and see what happens. Could you help me find what is the particular order of cascaded table drops? Is it the exact same order as indicated in the list following the NOTICE: drop cascades to xx other objects? I can see that logged order is the same as the order of table creation - so order by oid? If yes then the orphaned two tables of my case are the very last ones expected to be dropped.
Ireneusz Pluta <ipluta@wp.pl> writes:
> Could you help me find what is the particular order of cascaded table drops? Is it the exact same
> order as indicated in the list following the NOTICE: drop cascades to
> xx other objects?
I think the actual deletions happen in the opposite order, but the
notices do reflect the search order.
regards, tom lane