Re: How to create "auto-increment" field WITHOUT a sequence object?

[Dmitry Koterov <dmitry@xxxxxxxxxx>]

Thanks to all, I'll try to live with this solution.

(It is not ideal, but pretty good for my case.)

The better way may be to create an additional table with ANY structure and no data and LOCK using it, but not the "tbl" table. It theoretically decrease race conditions - the only thing which I need is to make mutex around only one update statement.

On Fri, Jul 1, 2011 at 12:01 AM, Dmitry Koterov <dmitry@xxxxxxxxxx> wrote:

OK.

Possible next solution is ON AFTER UPDATE trigger:

BEGIN

  LOCK TABLE tbl IN SHARE UPDATE EXCLUSIVE MODE;

  UPDATE tbl SET uniq_id=(SELECT MAX(uniq_id) + 1 FROM tbl) WHERE id = NEW.id;

END;

Seems SHARE UPDATE EXCLUSIVE is a minimum locking which conflicts with itself and which does not conflict with pg_dump.

(Unfortunately it conflicts with VACUUM which is performed by autovacuum process.)

SHARE UPDATE EXCLUSIVE is better than default LOCK (which also blocks reading).

On Thu, Jun 30, 2011 at 11:38 PM, A.M. <agentm@xxxxxxxxxxxxxxxxxxxxx> wrote:

On Jun 30, 2011, at 3:36 PM, Dmitry Koterov wrote:

> ...possibly within ON AFTER INSERT trigger:
>
> BEGIN
>  pg_advisory_lock(0xDEADBEEF);
>  UPDATE tbl SET uniq_id=(SELECT MAX(uniq_id) + 1 FROM tbl) WHERE id =
> NEW.id;
> END;
>
> Would it work without explicit pg_advisory_unlock() - would the locking be
> released on COMMIT/ROLLBACK?

No- advisory locks are managed by the application, so that is exactly what you don't want. The exclusive table lock is still exactly what you need unless you can postpone the generation of the secondary IDs.

Cheers,
M