On Mon, Apr 1, 2013 at 10:53 PM, Andres Freund <and...@2ndquadrant.com> wrote: > On 2013-04-01 21:24:06 +0200, Magnus Hagander wrote: >> On Mon, Apr 1, 2013 at 9:09 PM, Andres Freund <and...@2ndquadrant.com> wrote: >> > Hi, >> > >> > On 2013-04-01 20:43:36 +0200, Magnus Hagander wrote: >> >> Maybe my thinking is just missing something obvious here. But looking >> >> at a fresh initdb, I'm seeing a number of files in the data directory >> >> for this database that don't have corresponding entries in pg_class. I >> >> looked for entries based on relfilenode in pg_class - am I missing >> >> some other way we put files in there? >> >> >> >> I'm seeing the same on both head and 9.1 as an example, but different >> >> oids. >> >> >> >> I feel I'm either missing something obvious, or we have a problem >> >> here? And somehow the first one seems more likely.. >> >> >> >> The query I ran (yeah, i'm sure it can be written prettier, but this >> >> was quick-n-dirty): >> >> >> >> with t as ( >> >> select * from pg_ls_dir('<datadir>/base/1') l(f) >> >> where f not in ( >> >> select relfilenode::text from pg_class >> >> union all select relfilenode::text || '_vm' from pg_class >> >> union all select relfilenode::text || '_fsm' from pg_class) >> >> ) >> >> select f, >> >> size, >> >> modification >> >> from t, pg_stat_file('<datadir>/base/1/' || f) >> >> order by 1; >> > >> > You're missing nailed tables which don't have a valid relfilenode (but >> > InvalidOid/0) in pg_class.relfilenode. You need to query the refilenode >> > like >> > SELECT pg_relation_filenode(pg_class.oid) FROM pg_class. >> >> Ha. Of course. I knew it was me getting it wrong. >> >> You'd get extra happy if only you knew how many pretty senior pg folks >> i pestered about that one on irc before posting here :D > > Most of those probably didn't spend too much time on developing wal->logical > changes transformations ;) > > For some real reason to send this email: Even if you add > pg_relation_filenode() > to the above query, its still not correct. You also need to disambiguate by > tablespace. Just for the case youre using it for something more interesting > than a freshly initdbed cluster.
Right. I was, but with a single tablespace :) You also need to add a IS NOT NULL to the definitions, or it's always going to return zero rows (just in case somebody is picking up the query) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers