Advisory locks support for postgres

[MOZGIII]

It would be great if this crate implemented support for advisory locks (the pg_advisory(_xact)_(try_|un)lock functions).

[weiznich]

I'm happy to consider such an addition. Please outline the API you would like to implement and submit a PR afterwards.

[MOZGIII]

An extract from my codebase:

//! The Postgres' advisory locks API subset that we use.

use diesel::prelude::sql_function;
use diesel::sql_types::BigInt;
use generic_array::typenum;

sql_function! {
    /// The transactional advisory lock.
    fn pg_advisory_xact_lock(key: BigInt);
}

/// Compute the lock key from the provided bytes.
///
/// Requires the size of the input to be exactly 64 bits.
pub fn key(bytes: [u8; 8]) -> i64 {
    i64::from_be_bytes(bytes)
}

/// Compute the lock key from the provided bytes, truncating the leading part.
///
/// Requires the size of the input to be at least 64 bits.
pub fn key_from_truncated<S>(bytes: impl Into<generic_array::GenericArray<u8, S>>) -> i64
where
    S: typenum::IsGreater<typenum::U8, Output = typenum::True> + generic_array::ArrayLength<u8>,
{
    let mut truncated = [0u8; 8];
    truncated.copy_from_slice(&bytes.into()[0..8]); // slice access safety if guaranteed by types
    key(truncated)
}

For the complete API we could add other calls mentioned at the docs (and also variants with to ints vs one bigint).

@weiznich how can we implement the API for the functions with arguments overload?

[weiznich]

Adding the relevant sql_function! defintions to diesel seems fine for me as long as a PR includes documentation + the corresponding helper type exports.

For functions with argument overloads we need to define two separate definitions with different names due to how name resolution works in rust. See the diesel-full-text-search crate for examples.