Обсуждение: Can't find a relation in pg_class

Поиск
Список
Период
Сортировка

Can't find a relation in pg_class

От
Ron
Дата:
Hi,

v9.6.6

10.143.169.100(38184) TAP TAPb 14310 PARSE waiting [unknown] 00000
       LOG:  process 14310 still waiting for AccessShareLock
       on relation 767445914 of database 767442254 after 1000.389 ms at character 39

We found this in the log file, so went looking for what relation 767445914 is, but don't see it in the pg_class table.  Am I doing something wrong?

postgres=# select oid, datname from pg_database;
    oid    |  datname
-----------+-----------
     13269 | postgres
         1 | template1
     13268 | template0
 767442254 | TAPb
(4 rows)

postgres=# \c TAPb

TAPb=# select n.nspname,
              c.relname,
              c.relfilenode
       from pg_class c,
               pg_namespace n
       where n.oid = c.relnamespace
         and c.relfilenode > 767445900
       order by c.relfilenode;
  nspname |                          relname                          | relfilenode
----------+-----------------------------------------------------------+-------------
 css      | branding_file_branding_file_id_seq                        |   767445903
 css      | cssbatch_control_cssbatch_control_id_seq                  |   767445920
 css      | cssdata_partition_ds_cssdata_partition_ds_id_seq          |   767447650
 css      | cssdata_partition_policy_cssdata_partition_policy_id_seq  |   767447655
[snip]

Thanks

--
Angular momentum makes the world go 'round.

Re: Can't find a relation in pg_class

От
Tom Lane
Дата:
Ron <ronljohnsonjr@gmail.com> writes:
> 10.143.169.100(38184) TAP TAPb 14310 PARSE waiting [unknown] 00000
>         LOG:  process 14310 still waiting for AccessShareLock
>         on relation *767445914* of database *767442254* after 1000.389 ms at
> character 39

> We found this in the log file, so went looking for what relation 767445914
> is, but don't see it in the pg_class table.  Am I doing something wrong?

This message would be citing relation OID not relfilenode, I believe.

Another possibility is that you can't see the row because it's either
uncommitted (new table) or committed dead (just-deleted table).  But
it's not real clear how there could be a lock conflict in such cases.

            regards, tom lane

Re: Can't find a relation in pg_class

От
Ron
Дата:
On 11/15/2018 12:56 PM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> 10.143.169.100(38184) TAP TAPb 14310 PARSE waiting [unknown] 00000
>>          LOG:  process 14310 still waiting for AccessShareLock
>>          on relation *767445914* of database *767442254* after 1000.389 ms at
>> character 39
>> We found this in the log file, so went looking for what relation 767445914
>> is, but don't see it in the pg_class table.  Am I doing something wrong?
> This message would be citing relation OID not relfilenode, I believe.

In what table?

> Another possibility is that you can't see the row because it's either
> uncommitted (new table) or committed dead (just-deleted table).  But
> it's not real clear how there could be a lock conflict in such cases.

That's what puzzles us.  (It's part of a huge transaction which drops old 
bits of partitoned tables and then adds new bits.  It worked flawlessly in 
8.4 but not in 9.6.)


-- 
Angular momentum makes the world go 'round.

Re: Can't find a relation in pg_class

От
Alvaro Herrera
Дата:
On 2018-Nov-15, Ron wrote:

> On 11/15/2018 12:56 PM, Tom Lane wrote:
> > Ron <ronljohnsonjr@gmail.com> writes:
> > > 10.143.169.100(38184) TAP TAPb 14310 PARSE waiting [unknown] 00000
> > >          LOG:  process 14310 still waiting for AccessShareLock
> > >          on relation *767445914* of database *767442254* after 1000.389 ms at
> > > character 39
> > > We found this in the log file, so went looking for what relation 767445914
> > > is, but don't see it in the pg_class table.  Am I doing something wrong?
> > This message would be citing relation OID not relfilenode, I believe.
> 
> In what table?

select ... from pg_class where oid = 767445914

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Can't find a relation in pg_class

От
Ron
Дата:
On 11/15/2018 02:02 PM, Alvaro Herrera wrote:
> On 2018-Nov-15, Ron wrote:
>
>> On 11/15/2018 12:56 PM, Tom Lane wrote:
>>> Ron <ronljohnsonjr@gmail.com> writes:
>>>> 10.143.169.100(38184) TAP TAPb 14310 PARSE waiting [unknown] 00000
>>>>           LOG:  process 14310 still waiting for AccessShareLock
>>>>           on relation *767445914* of database *767442254* after 1000.389 ms at
>>>> character 39
>>>> We found this in the log file, so went looking for what relation 767445914
>>>> is, but don't see it in the pg_class table.  Am I doing something wrong?
>>> This message would be citing relation OID not relfilenode, I believe.
>> In what table?
> select ... from pg_class where oid = 767445914

That's what I thought.  But select oid, relfilenode from pg_class shows that 
c.oid always has the same value as c.relfilenode.

-- 
Angular momentum makes the world go 'round.

Re: Can't find a relation in pg_class

От
Alvaro Herrera
Дата:
On 2018-Nov-15, Ron wrote:

> On 11/15/2018 02:02 PM, Alvaro Herrera wrote:
> > On 2018-Nov-15, Ron wrote:
> > 
> > > On 11/15/2018 12:56 PM, Tom Lane wrote:
> > > > Ron <ronljohnsonjr@gmail.com> writes:
> > > > > 10.143.169.100(38184) TAP TAPb 14310 PARSE waiting [unknown] 00000
> > > > >           LOG:  process 14310 still waiting for AccessShareLock
> > > > >           on relation *767445914* of database *767442254* after 1000.389 ms at
> > > > > character 39
> > > > > We found this in the log file, so went looking for what relation 767445914
> > > > > is, but don't see it in the pg_class table.  Am I doing something wrong?
> > > > This message would be citing relation OID not relfilenode, I believe.
> > > In what table?
> > select ... from pg_class where oid = 767445914
> 
> That's what I thought.  But select oid, relfilenode from pg_class shows that
> c.oid always has the same value as c.relfilenode.

Then the other cases Tom mentioned apply.

(Note that that equality doesn't apply in general.  The relfilenode will
change on VACUUM FULL, or CLUSTER, or table-rewriting ALTER TABLE,
whereas the OID will always stay unchanged.)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services