pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_repack issues
3+ messages / 3 participants
[nested] [flat]
* pg_repack issues
@ 2014-11-11 22:07 Mark Steben <mark.steben@drivedominion.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Steben @ 2014-11-11 22:07 UTC (permalink / raw)
To: pgsql-admin
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)
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?
If I am double posting I apologize. The pg_repack doc instructs to send
questions to the reorg-general list. I suspect this is an obsolete list as
I haven't received a response to my subscription request in over 2 weeks.
thanks for your help,
--
*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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_repack issues
@ 2014-11-12 02:26 Greg Sabino Mullane <greg@turnstep.com>
parent: Mark Steben <mark.steben@drivedominion.com>
0 siblings, 1 reply; 3+ messages in thread
From: Greg Sabino Mullane @ 2014-11-12 02:26 UTC (permalink / raw)
To: pgsql-admin
-----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-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_repack issues
@ 2014-11-12 20:33 Josh Kupershmidt <schmiddy@gmail.com>
parent: Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Kupershmidt @ 2014-11-12 20:33 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-11-12 20:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-11 22:07 pg_repack issues Mark Steben <mark.steben@drivedominion.com>
2014-11-12 02:26 ` Greg Sabino Mullane <greg@turnstep.com>
2014-11-12 20:33 ` Josh Kupershmidt <schmiddy@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox