Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
От | |
---|---|
Тема | Re: Restart increment to 0 each year = re-invent the sequences mecanism ? |
Дата | |
Msg-id | 64963.216.238.112.88.1082986721.squirrel@$HOSTNAME обсуждение исходный текст |
Ответ на | Re: Restart increment to 0 each year = re-invent the sequences mecanism ? (Clodoaldo Pinto Neto <clodoaldo_pinto@yahoo.com.br>) |
Ответы |
Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
|
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: