Thread: pg_repack issues
Good afternoon,
I have recently be using pg_repack and for one of my production databases have the following issue: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)
--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Mark Steben wrote: > ERROR: query failed: ERROR: value "3048471620" is out of > range for type integer > DETAIL: query was: SELECT pg_try_advisory_lock($1, $2) > > This happens because the oid values of the tables is this database are > all over 2 GB. Can I get some insight to make pg_repack work in this > situation? That was an unfortunate choice they made in having their second (int) argument be an OID. A quick fix would be to have it use the single (bigint) form of pg_try_advisory_lock. Something like this (untested): diff --git a/bin/pg_repack.c b/bin/pg_repack.c index af510ca..d638164 100644 - --- a/bin/pg_repack.c +++ b/bin/pg_repack.c @@ -1520,13 +1520,12 @@ static bool advisory_lock(PGconn *conn, const char *relid) { PGresult *res = NULL; bool ret = false; - - const char *params[2]; + const char *params[1]; - - params[0] = REPACK_LOCK_PREFIX_STR; - - params[1] = relid; + params[0] = relid; - - res = pgut_execute(conn, "SELECT pg_try_advisory_lock($1, $2)", - - 2, params); + res = pgut_execute(conn, "SELECT pg_try_advisory_lock($1)", + 1, params); if (PQresultStatus(res) != PGRES_TUPLES_OK) { elog(ERROR, "%s", PQerrorMessage(connection)); You also may want to raise this as an official issue here: https://github.com/reorg/pg_repack/issues - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201411112120 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlRixSsACgkQvJuQZxSWSsjuFwCfRvWEMeP3Rnb7F0HuRvIMNKwi wNwAoPq3Sg6Q64C2tK/FdLdTHBo3zqlm =DiA9 -----END PGP SIGNATURE-----
Just happened to see this thread -- the github issues page is the proper place to send bug reports about pg_repack, and OP has opened https://github.com/reorg/pg_repack/issues/30 already. On Tue, Nov 11, 2014 at 9:26 PM, Greg Sabino Mullane <greg@turnstep.com> wrote: > That was an unfortunate choice they made in having their second (int) > argument be an OID. A quick fix would be to have it use the single (bigint) > form of pg_try_advisory_lock. Something like this (untested): [patch to use single-argument form of pg_try_advisory_lock] Yeah, that would work. The reason we're using the two-argument form of pg_try_advisory_lock() at all is an attempt to play nice with any other applications which might also be using advisory locks; maybe that's being excessively cautious. I don't have a strong preference, but I believe this should also work: SELECT pg_try_advisory_lock($1, -2147483648 + $2) i.e. cram the unsigned 4-byte OID into a signed 4-byte integer. Josh