Postgres Pro
Downloads
Home   >   mailing lists

Thread: Hstore OID bigger than an integer

Hstore OID bigger than an integer

From
Roberto Mireles
Date:
Hello team, 

This is the first time I post here, if you can provide some help, would be much appreciated.

I have an application that can not access the database due to OID value for hstore extension is bigger than an integer value. Application uses a NpgSql driver that only supports integer types for OIDs. 

We have a new app version, which uses a new driver version that supports bigint and has no issues at all, but for that specific database, we still need to use that old version of the app.

I have searched for a workaround or something that can help us to be able to connect to the database, but have not found anything related. 

I also tried by dropping, creating extension again, but same result.

Does any of you know any workaround that can help here?

We are using postgresql 9.2.24.

Thanks.

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited. If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING. Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, 42Q (or any of its subsidiaries), or any other person or entity.
Attachment

Re: Hstore OID bigger than an integer

From
Alvaro Herrera
Date:
On 2019-Aug-23, Roberto Mireles wrote:

> I have an application that can not access the database due to OID value for
> hstore extension is bigger than an integer value. Application uses a NpgSql
> driver that only supports integer types for OIDs.

That's a bug in Npgsql.  OIDs are unsigned.

> I have searched for a workaround or something that can help us to be able
> to connect to the database, but have not found anything related.
> 
> I also tried by dropping, creating extension again, but same result.

As a workaround, it would probably work to change the OID counter back
to the positive side of the signed value (probably using pg_resetxlog,
beware), then drop and recreate the extension.

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

Re: Hstore OID bigger than an integer

From
Tomas Vondra
Date:
On Fri, Aug 23, 2019 at 08:58:50AM -0500, Roberto Mireles wrote:
>Hello team,
>
>This is the first time I post here, if you can provide some help, would be
>much appreciated.
>
>I have an application that can not access the database due to OID value for
>hstore extension is bigger than an integer value. Application uses a NpgSql
>driver that only supports integer types for OIDs.
>

I'm not familiar with npgsql, but OID is an unsigned integer. So if you
have an OID greater than 2147483647, then that can't be converted into a
32-bit integer, of course (npgsql likely gets a text value, and then fails
while parsing it).

>We have a new app version, which uses a new driver version that supports
>bigint and has no issues at all, but for that specific database, we still
>need to use that old version of the app.
>
>I have searched for a workaround or something that can help us to be able
>to connect to the database, but have not found anything related.
>
>I also tried by dropping, creating extension again, but same result.
>
>Does any of you know any workaround that can help here?
>
>We are using postgresql 9.2.24.
>

Well, this is likely an issue you should raise with npgsql instead. I'd
assume they have a way to deal with OID values properly.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Hstore OID bigger than an integer

From
Merlin Moncure
Date:
On Fri, Aug 23, 2019 at 9:26 AM Roberto Mireles
<roberto.mireles@42-q.com> wrote:
>
> Hello team,
>
> This is the first time I post here, if you can provide some help, would be much appreciated.
>
> I have an application that can not access the database due to OID value for hstore extension is bigger than an
integervalue. Application uses a NpgSql driver that only supports integer types for OIDs.
 
>
> We have a new app version, which uses a new driver version that supports bigint and has no issues at all, but for
thatspecific database, we still need to use that old version of the app.
 
>
> I have searched for a workaround or something that can help us to be able to connect to the database, but have not
foundanything related.
 
>
> I also tried by dropping, creating extension again, but same result.
>
> Does any of you know any workaround that can help here?

A full dump/restore of the database (via pg_dump) might work, as long
as the oids are not dumped with the database, which I believe to be
the default.  This ought to reset the oid counter. Some while back,
oids were changed so the counter was table specific (IIRC).  Not sure
was after 9.2.  If it was, upgrading the database (which you should be
looking at anyways) might help.  Also, raise an issue upstream.

merlin