RE: ERROR: operator does not exist: - regclass

weinerk's profile image weinerk posted 4 years ago in General Permalink

RE: ERROR: operator does not exist: - regclass

i am getting this error a lot for some tables it must be some integer wrap around problem

i see this in the logging window:

SELECT -1377221952::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377221952::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
weinerk's profile image weinerk posted 4 years ago Permalink

i believe it is the case where OID is larger than 2^31

you must have a signed int instead of unsigned

so instead of

2,917,745,344

we end up with

-1,377,221,952

because

4,294,967,296 (2^32)  -  2,917,745,344  =  1,377,221,952
ansgar's profile image ansgar posted 4 years ago Permalink

Could you please post an excerpt of your SQL log from the bottom, so I can probably find the code area where that happens? And what's your server version?

And yes, I can confirm that ::regclass is working from 0 to 2^32-1 only.

weinerk's profile image weinerk posted 4 years ago Permalink
  1. thanks for your work and a great tool

  2. yes per docs it should be unsigned 4 byte int

https://www.postgresql.org/docs/9.5/datatype-oid.html

  1. somewhere you have a conversion to a signed int

  2. as far as narrowing down where:

seems like every select has a couple calls before it and a couple after;

see below a how it: works for "select 1;" fails for "select from SomeBigOidTable ;"

query: SELECT 1 ;

log:
SELECT 1;
SELECT 0::regclass;
SELECT 0::regclass;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.000 sec. (+ 0.156 sec. network) */
SELECT 0::regclass;
SELECT 0::regclass;
query: select * from billing.customer_payment_plans LIMIT 11 ;

log:
select * from billing.customer_payment_plans LIMIT 11 ;
SELECT -1377221952::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377221952::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
SELECT -1377221952::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377221952::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
/* Affected rows: 0  Found rows: 11  Warnings: 0  Duration for 1 query: 0.000 sec. (+ 0.172 sec. network) */
SELECT -1377221952::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377221952::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
SELECT -1377221952::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377221952::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
ansgar's profile image ansgar posted 4 years ago Permalink

That "0" regclass is InvalidOid, defined in postgres_ext.h, but that does not tell anything about negative object ids.

Could you probably post the CREATE code of your customer_payment_plans table here, so I can try to reproduce?

weinerk's profile image weinerk posted 4 years ago Permalink

this is happening to any table with high enough OID

for example even the simplest:

CREATE TABLE billing.referrals
(
  referral_id integer NOT NULL,
  name text,
  phone text,
  email text,
  state character(2),
  status character(1),
  created_dt timestamp with time zone,
  created_by text,
  CONSTRAINT referrals_pkey PRIMARY KEY (referral_id)
)
SELECT * FROM referrals ;
SELECT -1377232518::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377232518::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
SELECT -1377232518::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377232518::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.000 sec. (+ 0.156 sec. network) */
SELECT -1377232518::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377232518::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
SELECT -1377232518::regclass;
/* ERROR:  operator does not exist: - regclass
LINE 1: SELECT -1377232518::regclass
               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. */
ansgar's profile image ansgar posted 4 years ago Permalink

I don't get that error here, on PG 10.3.0. Probably you are on an earlier or later version? Please report which it is.

I can - or probably should - force negative object ids returned by PQftable to zero, to prevent such errors.

But that is more a workaround. It will leave HeidiSQL not knowing the name of a table name in a given result.

Code modification/commit ffb6170 from Ansgar Becker <anse@heidisql.com>, 4 years ago, revision 10.2.0.5728
Fix definition of PostgreSQL Oid from Integer to Cardinal, as it is in postgres_ext.h . Should fix negative Oids in TPGQuery.TableName. See https://www.heidisql.com/forum.php?t=34819
ansgar's profile image ansgar posted 4 years ago Permalink

I just found the definition of POids in HeidiSQL was wrong - signed 32bit integer. I just changed it to unsigned 32bit integer, namely Cardinal. That PQftable() function is most probably returning just a large uint number, where I thought it was out of even that range.

Please update to the latest build and try again. I say this is fixed now.

weinerk's profile image weinerk posted 4 years ago Permalink
  1. thanks !
  2. that is exactly what i wanted to explain - that you receive back from PQftable the correct oid (if it is treated as unsigned), but you figured it all out quickly :-)
  3. will try the new version

thanks very much for great work that you do!

weinerk's profile image weinerk posted 4 years ago Permalink

update: all looks good thanks!

Please login to leave a reply, or register at first.