Thread: Can't find a relation in pg_class
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
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.
Angular momentum makes the world go 'round.
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
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.
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
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.
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