Re: ERROR: tablespace "archive2" is not empty

On 10/19/2015 03:18 AM, Josip Rodin wrote:
> On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote:
>> On 2015-10-19 11:14:33 +0200, 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)
>>
>> Please actually run the query I suggested
>> above. pg_relation_filenode(oid) returns the relfilenode of the table
>> with the passed in oid - which you then compare with the relfilenode you
>> saw on disk.
>
> Oh, sorry, but yet again, there's just nothing there:
>
> % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE pg_relation_filenode(oid) IN (7877054, 7877056);"
> oid | relname | relkind
> -----+---------+---------
> (0 rows)
>

What happens if you do?:

select oid, * from pg_tablespace ;

select * from pg_class where reltablespace = <archive2 oid>;

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com