Re: Restart increment to 0 each year = re-invent the sequences mecanism ?

[<btober@computer.org>]

> You don't have to mess with sequences.
> If there are two fields ID and year then the next number is:
>
> next_number := ( select ID from table_name where year =
> year_from_current_date order by ID desc limit 1 ) + 1;

Gee, I wonder why no one else thought of that... lets see, what is this
thing I've heard about called, er, what was that word... oh yeah, I
remember: "concurrency". Hmm, I wonder...

CREATE TABLE test.test_table
(
  column1 int4 DEFAULT 0,
  column2 int4 DEFAULT 0
);

INSERT INTO test.test_table VALUES (1,0);

-- Process #1
BEGIN;
UPDATE test.test_table SET column2 = 1+(SELECT column2
		FROM test.test_table
		WHERE column1=1
		ORDER BY column2 DESC
		LIMIT 1);

/* ... */

-- Process #2
BEGIN;
UPDATE test.test_table SET column2 = 1+(SELECT column2
		FROM test.test_table
		WHERE column1=1
		ORDER BY column2 DESC
		LIMIT 1);

-- Note: Process #2 hangs indefinitely at this point.

COMMIT;
SELECT * FROM test.test_table;

/* ... */

-- Process #1
COMMIT;
SELECT * FROM test.test_table;

-- Result: (1,1)


/* ... */

-- Process #2
-- Result: (1,1)

-- "D-"

--Berend Tober




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster