Problem with last inserted id.

[btrewern]

Hudgi,

FYI the currval() function is safe for doing things like this and should not cause race conditions.

From the PostgreSQL docs:

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

BTW in new versions of PostgreSQL there is the possibility to use:

Code: Select all

INSERT INTO table_name (id, field1, field2, ...) VALUES (default, 'a', 'b', ...) RETURNING id;

which would save a call to the database server but was only added in pg 8.2.

And while I'm at it, another thing I've found is when you dump a table made like:

Code: Select all

CREATE TABLE test (id serial);

and then dump it you get:

Code: Select all

CREATE TABLE test (
    id integer NOT NULL
);
ALTER TABLE public.test OWNER TO postgres;
CREATE SEQUENCE test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER TABLE public.test_id_seq OWNER TO postgres;
ALTER SEQUENCE test_id_seq OWNED BY test.id;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);

notice the ALTER SEQUENCE bit. It seems that this is the link that makes the id field a sequence and makes sure the test_id_seq is dropped when the table is dropped.

Regards,

Ben

[hudgi]

It seems your information is more up to date then mine. Thank you for the quote, i missed this sentence in the doc. It's a good news, isn't it? So Mark was right at the first...

But what makes it sure that the user executed this ALTER SEQUENCE .. OWNED BY statement? It is quite a good stuff, but it was also introduced just in 8.2.

As we have to insist on remaining backward compatible, we have to use possibilities available in the smallest supported version. Or is there a way to differentiate between versions (based on protocol property??)? If yes, we have the chance to support newer versions better.

--hudgi

[puunnnk]

Does it work if I insert it in a Timer????

For example every 1ms, I read the table and returns me the last id.

[btrewern]

Does what work? What are you trying to achieve?

If you keep doing

Code: Select all

SELECT currval('seq_name');

you will keep getting the same answer (as these value are cached per connection). If you keep doing

Code: Select all

SELECT max(id) FROM table_name;

every 1ms you will just cause lots of network traffic.

Regards,

Ben