> 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
