OID out of range
I am getting ERROR: OID out of range while firing below SQL , what could be
the reason? I am joining pg_class and one user_created table to compare
tables size.
select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as
current_time,pg_size_pretty(pg_relation_size(c.oid)) as
current_size,pg_size_pretty(u.table_size) as
previous_size,pg_size_pretty(pg_relation_size(c.oid) -
pg_relation_size(u.table_size)) as diff from user_tables_sizes u join
pg_class c on u.relid::bigint = c.oid::bigint where c.relkind='r'
and c.relnamespace::regnamespace::text='rpx_reporting_stage' and
u.captured_dt::date=current_date - interval '1 days'ERROR: OID out of
rangetest-# \d user_tables_sizes
Table "user_tables_sizes"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
relid | numeric | | |
table_size | bigint | | |
captured_dt | timestamp without time zone | | |
Indexes:
"user_tables_sizes_relid_captured_dt_idx" UNIQUE, btree (relid,
(captured_dt::date))\d pg_class
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
relname | name | | not null |
relnamespace | oid | | not null |
reltype | oid | | not null |
reloftype | oid | | not null |
relowner | oid | | not null |
relam | oid | | not null |
relfilenode | oid | | not null |
reltablespace | oid | | not null |
relpages | integer | | not null |
reltuples | real | | not null |
relallvisible | integer | | not null |
reltoastrelid | oid | | not null |
relhasindex | boolean | | not null |
relisshared | boolean | | not null |
relpersistence | "char" | | not null |
relkind | "char" | | not null |
relnatts | smallint | | not null |
relchecks | smallint | | not null |
relhasoids | boolean | | not null |
relhaspkey | boolean | | not null |
relhasrules | boolean | | not null |
relhastriggers | boolean | | not null |
relhassubclass | boolean | | not null |
relrowsecurity | boolean | | not null |
relforcerowsecurity | boolean | | not null |
relispopulated | boolean | | not null |
relreplident | "char" | | not null |
relispartition | boolean | | not null |
relfrozenxid | xid | | not null |
relminmxid | xid | | not null |
relacl | aclitem[] | | |
reloptions | text[] | | |
relpartbound | pg_node_tree | | |
On 1/15/20 10:18 AM, Konireddy Rajashekar wrote:
I am getting ERROR: OID out of range while firing below SQL , what could
be the reason? I am joining pg_class and one user_created table to
compare tables size.select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as current_time,pg_size_pretty(pg_relation_size(c.oid)) as current_size,pg_size_pretty(u.table_size) as previous_size,pg_size_pretty(pg_relation_size(c.oid) - pg_relation_size(u.table_size)) as diff from user_tables_sizes u join pg_class c on u.relid::bigint = c.oid::bigint where c.relkind='r' and c.relnamespace::regnamespace::text='rpx_reporting_stage' and u.captured_dt::date=current_date - interval '1 days'ERROR: OID out of rangetest-# \d user_tables_sizes
Given this:
https://www.postgresql.org/docs/12/datatype-oid.html
"The oid type is currently implemented as an unsigned four-byte integer. "
I am pretty sure this:
c.oid::bigint
is the problem.
--
Adrian Klaver
adrian.klaver@aklaver.com
Any workaround to make it work ?
On Thu, 16 Jan 2020 at 00:00, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Show quoted text
On 1/15/20 10:18 AM, Konireddy Rajashekar wrote:
I am getting ERROR: OID out of range while firing below SQL , what could
be the reason? I am joining pg_class and one user_created table to
compare tables size.select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as
current_time,pg_size_pretty(pg_relation_size(c.oid)) as
current_size,pg_size_pretty(u.table_size) as
previous_size,pg_size_pretty(pg_relation_size(c.oid) -
pg_relation_size(u.table_size)) as diff from user_tables_sizes u join
pg_class c on u.relid::bigint = c.oid::bigint where c.relkind='r' and
c.relnamespace::regnamespace::text='rpx_reporting_stage' and
u.captured_dt::date=current_date - interval '1 days'ERROR: OID out of
rangetest-# \d user_tables_sizesGiven this:
https://www.postgresql.org/docs/12/datatype-oid.html
"The oid type is currently implemented as an unsigned four-byte integer. "I am pretty sure this:
c.oid::bigint
is the problem.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Wed, Jan 15, 2020 at 11:36 AM bhargav kamineni <bhargavpostgres@gmail.com>
wrote:
Any workaround to make it work ?
Convert both to text and join on that? Curious choice making relid
numeric...
David J.
tried casting to text,varchar but no luck
select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as
current_time,pg_size_pretty(pg_relation_size(c.oid)) as
current_size,pg_size_pretty(u.table_size) as
previous_size,pg_size_pretty(pg_relation_size(c.oid) -
pg_relation_size(u.table_size)) as diff from user_tables_sizes u join
pg_class c on u.relid::varchar= c.oid::varchar where c.relkind='r' and
c.relnamespace::regnamespace::text='rpx_reporting_stage' and
u.captured_dt::date=current_date - interval '1 days'
;
ERROR: OID out of range
On Thu, Jan 16, 2020 at 12:12 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Wed, Jan 15, 2020 at 11:36 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:Any workaround to make it work ?
Convert both to text and join on that? Curious choice making relid
numeric...David J.
On 2020-Jan-15, Konireddy Rajashekar wrote:
rangetest-# \d user_tables_sizes
Table "user_tables_sizes"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
relid | numeric | | |
Why do you have this column defined as numeric? It seems more sensible
to have it as type oid; it's probably that column where you have the
out-of-range values.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
@Alvaro Herrera, Can i alter the datatype of relid to oid and try ?
On Thu, Jan 16, 2020 at 12:41 AM Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:
Show quoted text
On 2020-Jan-15, Konireddy Rajashekar wrote:
rangetest-# \d user_tables_sizes
Table "user_tables_sizes"
Column | Type | Collation | Nullable |Default
-------------+-----------------------------+-----------+----------+---------
relid | numeric | | |
Why do you have this column defined as numeric? It seems more sensible
to have it as type oid; it's probably that column where you have the
out-of-range values.--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Please don't top-post here.
On Wed, Jan 15, 2020 at 12:01 PM Konireddy Rajashekar <
rajkonireddy@gmail.com> wrote:
tried casting to text,varchar but no luck
select u.relid,c.relnamespace::regnamespace::text,c.relname,now() as
current_time,pg_size_pretty(pg_relation_size(c.oid)) as
current_size,pg_size_pretty(u.table_size) as
previous_size,pg_size_pretty(pg_relation_size(c.oid) -
pg_relation_size(u.table_size)) as diff from user_tables_sizes u join
pg_class c on u.relid::varchar= c.oid::varchar where c.relkind='r' and
c.relnamespace::regnamespace::text='rpx_reporting_stage' and
u.captured_dt::date=current_date - interval '1 days'
;ERROR: OID out of range
Yeah, the join isn't the problem, the error is casting to OID, not from...
Pretty sure your problem is:
pg_relation_size(u.table_size)
David J.
