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

From
Subject Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
Date
Msg-id 64963.216.238.112.88.1082986721.squirrel@$HOSTNAME
Whole thread Raw
In response to Re: Restart increment to 0 each year = re-invent the sequences mecanism ?  (Clodoaldo Pinto Neto <clodoaldo_pinto@yahoo.com.br>)
Responses Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
List 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 by date:

Previous
From:
Date:
Subject: Re: Restart increment to each year = re-invent the
Next
From: "John Sidney-Woollett"
Date:
Subject: Re: Restart increment to each year = re-invent the