Re: ERROR: tablespace "archive2" is not empty

On 10/19/15 4:14 AM, Josip Rodin wrote:
> On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote:
>> Hi,
>>
>> On 2015-10-19 10:49:11 +0200, Josip Rodin wrote:
>>> % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);"
>>> oid | relname | relkind
>>> -----+---------+---------
>>> (0 rows)
>>
>> That's the wrong query. The files on disk are relefilenodes not
>> oids. Try WHERE pg_relation_filenode(oid) IN ...
>
> Oh, okay, but still:
>
> % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);"
> pg_relation_filenode
> ----------------------
>
> (1 row)
>
> % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877056);"
> pg_relation_filenode
> ----------------------

pg_relation_filenode accepts the OID of a table. For what you're trying
to do you'd need pg_relation_filenode(tablespace oid, relfilenode).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com