From: Bobus <roblocke@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: fetching unique pins in a high-transaction environment...
Date: 29 Oct 2006 22:13:22 -0800
Message-ID: <1162188802.731436.198130@h48g2000cwc.googlegroups.com> (raw)
References: <1162139808.031657.23340@f16g2000cwb.googlegroups.com>
I think we've figured out a way to implement the equivalent of a
READPAST hint in a function.
The basic idea is to loop until we find the next available unlocked
row, using the lock_not_available exception to determine if the record
is locked or not. Our early testing seems to indicate that this
solution will work, but we would love to hear about simpler and more
efficient ways to accomplish this.
Here's a simplified version of the function which illustrates the
principle:
CREATE OR REPLACE FUNCTION "getpin"() RETURNS varchar
as $$
DECLARE
v_id integer := 0;
v_pin varchar;
BEGIN
LOOP
BEGIN
-- Find the first available PIN.
-- Note: we cannot lock down the row here since we need to be
-- able to store the ID of the pin to implement the READPAST.
select id into v_id from pins where id > v_id and status = 0
order by id limit 1;
-- Exit if there are no PINs available.
IF NOT FOUND THEN
RAISE EXCEPTION 'no pins available';
END IF;
-- Lock down the PIN. If another transaction beat us to it, we
-- trap the error (see below) and loop looking for the next
-- available pin. If another transaction already updated the
-- status to 1 in between this select and the previous, then we
-- loop (see ELSE statement).
select pin into v_pin from pins where id = v_id and status = 0
for update nowait;
IF FOUND THEN
-- Update the PIN. The status = 0 check is unnecessary,
-- but better safe than sorry.
update pins set status = 1 where id = v_id and status = 0;
-- I don't think this should ever happen.
IF NOT FOUND THEN
RAISE EXCEPTION 'this should never happen';
END IF;
RETURN v_pin;
ELSE
-- Somebody snuck in and updated/grabbed the pin. Loop.
END IF;
EXCEPTION WHEN lock_not_available THEN
-- Loop looking for the next available unlocked pin.
END;
END LOOP;
END;
$$
language plpgsql;
Thanks...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roblocke@gmail.com
Subject: Re: fetching unique pins in a high-transaction environment...
In-Reply-To: <1162188802.731436.198130@h48g2000cwc.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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