5

I am currently running Postgresql 10.6 locally which I interface with using PgAdmin 4.12, up until today everything was running fine. However today I ran the following query in the pgAdmin query editor:

SELECT * FROM test_table LIMIT 100

and got the following error:

ERROR:  operator does not exist: - oid at character 125
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.
STATEMENT:  SELECT at.attname, at.attnum, ty.typname
        FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
        WHERE attrelid=-1519044407::oid AND attnum = ANY (
            (SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid
            AND con.contype='p' WHERE rel.relkind IN ('r','s','t', 'p') AND rel.oid = -1519044407::oid)::oid[])

The strange thing is when I run the same command on tables which were created yesterday, the data is output into the Pgadmin data output window successfully. I also tried running the same command with psql:

psql -U postgres -d geodata -c 'SELECT * FROM test_table LIMIT 100'

which was also successful. I am able to create tables in Pgadmin just not output them directly. Any new table I create and then output ends with the top error, the only difference is the oid changes. I have uninstalled Pgadmin and made sure to delete all leftover folders and reinstalled with no change.

Does anyone have any ideas what the problem is? Is the issue coming from pgadmin or is my Postgresql server corrupted in some way?

5

Never seen a negative OID before. This is a first-class "crime scene"!

... attrelid=-1519044407::oid ...
... rel.oid = -1519044407::oid ...

Facts

0.

2^32 - 1519044407 = 2775922889

And we have verified that OID 2775922889 indeed exists in your DB. Tests:

SELECT * FROM pg_class WHERE oid = 2775922889;
SELECT * FROM pg_class WHERE oid = '-1519044407';
SELECT * FROM pg_class WHERE relname = 'test_table';

1.

The manual about object identifier types:

The oid type is currently implemented as an unsigned four-byte integer.

2.

Postgres cast accepts signed integer anyway (!)

The Postgres I/O conversion from string literals, as well as the cast from integercurrently (pg 12) accepts negative integer values / literals as input anyway. Seems to just binary coerce a signed four-byte integer to unsigned four-byte integer and vice versa. Worth keeping in mind at least.

These, oddly, work:

test=# SELECT '-1519044407'::oid, '-1519044407'::int::oid;
    oid     |    oid     
------------+------------
 2775922889 | 2775922889

Leads to different representation when casting to int versus bigint:

test=# SELECT (oid '2775922889')::int
test-#      , (oid '2775922889')::bigint;
    int4     |    int8    
-------------+------------
 -1519044407 | 2775922889  -- !!

3.

The manual on Numeric Constants:

Note that any leading plus or minus sign is not actually considered part of the constant; it is an operator applied to the constant.

4.

The cast operator :: takes precedence over the unary minus operator (-).

Conclusions

1.

I have never seen OID numbers in that range in system catalogs before, and I have been working with all kinds of big databases. You have a problem in your DB (cluster).

Improved with comments from Daniel Vérité:

Either you are burning OID numbers at an insane rate - already 2.8 billion numbers. ~ 1.5 billion remain until OID wraparound. Do you have any tables created with WITH OIDS? (Nobody should any more. The feature is deprecated and removed in Postgres 12.) Or some code excessively creating / dropping new objects? The OID counter is per instance, not per database, so all dbs contribute to OID consumption.
There is a comment in the source code for GetNewOidWithIndex for how OID collisions are dealt with after wraparound. Collisions incur a minor performance penalty.

Or somebody/something messed with your system catalogs.

2.

If the above query was generated by pgAdmin4, there is a serious bug.

Maybe that did not surface, yet, as nobody had OIDs in that range in the system catalogs, yet?

Seems like it operates with the integer representation of OIDs, and naively pastes those as numeric literals including the sign by mistake in queries. A string literal would work: '-1519044407'::oid. Or parentheses would make it work: (-1519044407)::oid.

But this does not:

-1519044407::oid 

Because:

    1. 1519044407 is taken as numeric literal and initially coerced to integer.
    1. The cast operator :: takes precedence over the sign operator - and the integer is cast to the (wrong!!) oid.
    1. Finally, Postgres tries to apply the sign operator and, luckily, fails with the reported error message:
    ERROR:  operator does not exist: - oid at character 125
    

    If it would not fail there, serious nonsense might occur.

db<>fiddle here

I have posted a note to the pgadmin-hackers list.
A related bug has been logged before there, too. (Access with Postgres community account.) It was traced back to a psycopg2 issue using the wrong data type for OID in 32 bit versions. Should be fixed in psycopg2 version 2.8.4 (which pgAdmin4 depends upon).

|improve this answer|||||
  • Ah that makes sense, we do have some queries that use WITH expensively. So I guess we burned through all the OIDS as the query SELECT * FROM pg_class WHERE oid = 2775922889; returns a result. I think its time to redesign the queries. Thank you for your help! – Trashmonk Sep 6 '19 at 4:59
  • 1
    To avoid confusion: queries using a WITH clause (CTEs) are unrelated. I was asking about the WITH OIDS clause of the CREATE TABLE command, which would burn OIDs for all rows in user tables. – Erwin Brandstetter Sep 6 '19 at 5:02
  • 1
    @Trashmonk: OID wraparound occurs at 2^32, so you still have 2^32-2775922889, or about 1.5 billion OIDs to consume before that. Still, it's a good idea to check if you have per-row OIDs on some tables, and a very high rate of create/drop objects would also have that effect. – Daniel Vérité Sep 6 '19 at 11:30
  • @DanielVérité: I updated with clearer explanation. Looks like a bug in pgAdmin4? Do you think the Postgres int <--> oid conversion makes sense? I find it a bit surprising that a cast to bigint yields a different result than a cast to int. Guess binary coercion is just fastest ... – Erwin Brandstetter Sep 6 '19 at 16:12
  • 1
    @ErwinBrandstetter: it looks very much like a simple bug in pgAdmin4. The [big]int<-->oid conversions by postgres seem okay to me. See GetNewOidWithIndex for how OIDs collisions are dealt with after an OID-wraparound (there is a performance penalty for collisions). Also the OID counter is per instance, not per database, so another db could be responsible for over-consuming OIDs. – Daniel Vérité Sep 6 '19 at 22:10
2

This issue is already logged @ https://redmine.postgresql.org/projects/pgadmin4. This issue is introduced in psycopg2 library and it's been reported.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.