Advisory Lock BIGINT Values

On Fri, Mar 9, 2012 at 04:36:08PM -0800, David E. Wheeler wrote:

Hackers,

The documentation for pg_locks says that, for BIGINT advisory locks:

A bigint key is displayed with its high-order half in the classid column, its low-order half in the objid column

I was in need of knowing what the bigint is that is waiting on a lock, and Andrew Dunstan was kind enough to help me out with that. Since other folks might also need it, here’s a doc patch.

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
new file mode 100644
index 9564e01..de1c266
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 7313,7319 ****
A <type>bigint</type> key is displayed with its
high-order half in the <structfield>classid</> column, its low-order half
in the <structfield>objid</> column, and <structfield>objsubid</> equal
!    to 1.  Integer keys are displayed with the first key in the
<structfield>classid</> column, the second key in the <structfield>objid</>
column, and <structfield>objsubid</> equal to 2.  The actual meaning of
the keys is up to the user.  Advisory locks are local to each database,
--- 7313,7322 ----
A <type>bigint</type> key is displayed with its
high-order half in the <structfield>classid</> column, its low-order half
in the <structfield>objid</> column, and <structfield>objsubid</> equal
!    to 1. The original <type>bigint</type> value can be reassembled with the
!    expression <literal>(classid::int::bit(64) &lt;&lt; 32 |
!    objid::int::bit(64))::bigint</literal>. Integer keys are displayed with the
!    first key in the
<structfield>classid</> column, the second key in the <structfield>objid</>
column, and <structfield>objsubid</> equal to 2.  The actual meaning of
the keys is up to the user.  Advisory locks are local to each database,

Thanks, applied.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:

On Fri, Mar 9, 2012 at 04:36:08PM -0800, David E. Wheeler wrote:

A <type>bigint</type> key is displayed with its
high-order half in the <structfield>classid</> column, its low-order half
in the <structfield>objid</> column, and <structfield>objsubid</> equal
! to 1. The original <type>bigint</type> value can be reassembled with the
! expression <literal>(classid::int::bit(64) &lt;&lt; 32 |
! objid::int::bit(64))::bigint</literal>. Integer keys are displayed with the
! first key in the
<structfield>classid</> column, the second key in the <structfield>objid</>
column, and <structfield>objsubid</> equal to 2. The actual meaning of
the keys is up to the user. Advisory locks are local to each database,

Thanks, applied.

This formula is not actually correct, as you'd soon find out if you
experimented with values with the high-order bit of the low-order word
set. (Hint: sign extension.)

The correct formula is both simpler and far more efficient:

(classid::int8 << 32) | objid::int8

This works because oidtoi8 correctly treats the OID value as unsigned.

regards, tom lane

On Aug 27, 2012, at 8:56 PM, Tom Lane wrote:

This formula is not actually correct, as you'd soon find out if you
experimented with values with the high-order bit of the low-order word
set. (Hint: sign extension.)

The correct formula is both simpler and far more efficient:

(classid::int8 << 32) | objid::int8

This works because oidtoi8 correctly treats the OID value as unsigned.

Oh, nice, thanks!

David

On Mon, Aug 27, 2012 at 11:56:32PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Fri, Mar 9, 2012 at 04:36:08PM -0800, David E. Wheeler wrote:

A <type>bigint</type> key is displayed with its
high-order half in the <structfield>classid</> column, its low-order half
in the <structfield>objid</> column, and <structfield>objsubid</> equal
! to 1. The original <type>bigint</type> value can be reassembled with the
! expression <literal>(classid::int::bit(64) &lt;&lt; 32 |
! objid::int::bit(64))::bigint</literal>. Integer keys are displayed with the
! first key in the
<structfield>classid</> column, the second key in the <structfield>objid</>
column, and <structfield>objsubid</> equal to 2. The actual meaning of
the keys is up to the user. Advisory locks are local to each database,

Thanks, applied.

This formula is not actually correct, as you'd soon find out if you
experimented with values with the high-order bit of the low-order word
set. (Hint: sign extension.)

The correct formula is both simpler and far more efficient:

(classid::int8 << 32) | objid::int8

This works because oidtoi8 correctly treats the OID value as unsigned.

OK, docs updated with attached patch. Thanks. (I used 'bigint' instead
of int8 to be consistent with the surrounding text.)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +