pg_advisory_lock (1,2) too small to handle oid's bigger than 2GB

[Mark-Steben]

Hello,
I have a database who's table oid's exceed the two gigabyte boundary. I therefore receive this error when I attempt pg_repack on any of these tables:

pg_repack -U postgres mavmail -t mailer_admin -h prod-legacy-psql.server.int
INFO: repacking table "mailer_admin"
ERROR: query failed: ERROR: value "3048471620" is out of range for type integer
DETAIL: query was: SELECT pg_try_advisory_lock($1, $2)

Thank you
Mark Steben
Database Administrator
Dominion Dealer Solutions
tel (413-327-3045)

[schmiddy]

I see: the problem is that per http://www.postgresql.org/docs/current/static/datatype-oid.html, OIDs are "implemented as an unsigned four-byte integer", and the two-argument form of pg_advisory_lock we are using takes two signed integers as arguments.

One option would be that we could use the single-argument form of pg_advisory_lock, which takes a bigint argument instead of ints. The only downside there is that we'd lose the ability to specify a PG_REPACK_PREFIX distinguishing pg_repack from other applications which may be using advisory locks.

Another option would be keeping the two-argument call to pg_advisory_lock, but write it like:

SELECT pg_try_advisory_lock($1, -2147483648 + $2);

which I think should work to squeeze those unsigned OID values into the same-sized signed int.

[Mark-Steben]

Hi Josh,

Thank you for taking the reins on this and providing a fix. I just tested
with the source on your development branch https://
github.com/reorg/pg_repack.
Looks fine to me. I am just beginning my usage of git and am not that
familiar with how you run your source migration.
Is the development branch source-code appropriate for pushing into our
production environment?
Or are you going to migrate to your (what I believe is your) production
platform http://pgxn.org/dist/pg_repack/? http://pgxn.org/dist/pg_repack/
If so, when will that happen? Any insight appreciated.

Thanks, have a great weekend,

Mark
http://pgxn.org/dist/pg_repack/

On Thu, Nov 13, 2014 at 8:55 PM, Josh Kupershmidt notifications@github.com
wrote:

Closed #30 #30 via 4b33474
4b33474
.

—
Reply to this email directly or view it on GitHub
#30 (comment).

Mark Steben
Database Administrator
@utoRevenue http://www.autorevenue.com/ | Autobase
http://www.autobase.net/
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com http://www.autorevenue.com/

http://autobasedigital.net/marketing/DD12_sig.jpg

[schmiddy]

Yeah, we tag releases of the "master" branch of pg_repack periodically, which are made available on PGXN as you've seen. Our master branch is pretty stable at this point; we should (hopefully) be tagging a 1.2.2 release fairly soon, though feel free to either use the fix in 4b33474 by itself or the master branch as-is until we get around to the 1.2.2 release.

[Mark-Steben]

Hi Josh,

We did install the master dev0 and created a new extension. We now get the
following error when we attempt to repack a table with oid > 2GB:

pg_repack -t mailer_admin -U postgres -d mavmail -h
prod-legacy-psql.server.int
INFO: repacking table "mailer_admin"
ERROR: query failed: ERROR: value "3048471620" is out of range for type
integer
DETAIL: query was: SELECT pg_try_advisory_lock($1, -2147483648 + $2)

Let me know if I need to repost this in the pg_repack issues site or if
this is sufficient.

thank you, Mark

On Sun, Nov 16, 2014 at 10:08 PM, Josh Kupershmidt <notifications@github.com

wrote:

Yeah, we tag releases of the "master" branch of pg_repack periodically,
which are made available on PGXN as you've seen. Our master branch is
pretty stable at this point; we should (hopefully) be tagging a 1.2.2
release fairly soon, though feel free to either use the fix in 4b33474
4b33474
by itself or the master branch as-is until we get around to the 1.2.2
release.

—
Reply to this email directly or view it on GitHub
#30 (comment).

Mark Steben
Database Administrator
@utoRevenue http://www.autorevenue.com/ | Autobase
http://www.autobase.net/
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com http://www.autorevenue.com/

http://autobasedigital.net/marketing/DD12_sig.jpg

[schmiddy]

Oops, looks like I was too quick with 4b33474. Can you verify that the latest fix (6d3c085) now in master works for you?

[Mark-Steben]

Josh sorry for late response. Yes this works great now. Thank you!

On Thu, Nov 20, 2014 at 5:25 PM, Josh Kupershmidt notifications@github.com
wrote:

Oops, looks like I was too quick with 4b33474
4b33474.
Can you verify that the latest fix (6d3c085
6d3c085)
now in master works for you?

—
Reply to this email directly or view it on GitHub
#30 (comment).

Mark Steben
Database Administrator
@utoRevenue http://www.autorevenue.com/ | Autobase
http://www.autobase.net/
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com http://www.autorevenue.com/

http://autobasedigital.net/marketing/DD12_sig.jpg