Manually generate PostgreSQL table ID with concurrent access

Question

INSERT INTO rehus.curriculumsfile (fileid, originalfilename, filename, path, curriculumid, userid, companyid) VALUES ((SELECT COALESCE(MAX(fileid), 0) + 1 FROM rehus.curriculumsfile WHERE curriculumid = $1 AND userid = $2 AND companyid = $3), $4, $5, $6, $1, $2, $3)

I'm trying to insert into the database file records as the upload concludes. But I'm receiving duplicated key erros, because the select is generating the same ID in multiple concurrent accesses. How can I solve this issue? And why does this happen? It's the same query, both insert and select statements should be resolved under the same "db status".

I know that sequences or series should solve the problem but they don't really work for me.

Possible duplicate: stackoverflow.com/questions/18614303/insert-into-postgres-sql — Claudi
– Claudi, Commented Nov 12, 2014 at 14:51
I have composite keys and need them to follow an sequence like (1, 1), (1, 2), (1, 3); (2, 1), (2, 2), etc... — José Neves
– José Neves, Commented Nov 12, 2014 at 14:59

Kouber Saparev · Accepted Answer · 2014-11-12 15:36:05Z

Either you have to forget about manually, either about concurrent, you cannot have both. It is up to you to decide which one is more important for you.

If you choose to break concurrency, then you can lock the table at the beginning of the transaction, then insert the data and finally commit (which will release the lock). Then the next transaction will be processed and so on, consecutively.

BEGIN;

LOCK TABLE rehus.curriculumsfile;

INSERT INTO rehus.curriculumsfile ... ;

...

COMMIT;

If you choose to not generate these IDs manually, then you can rely on sequences to do the job for you and then if you wish use a window function to display consecutive numbers to the users.

CREATE SEQUENCE curriculumsfile_fileid_seq;

ALTER TABLE
  rehus.curriculumsfile
ALTER COLUMN
  fileid
SET DEFAULT
  NEXTVAL('curriculumsfile_fileid_seq');

Then replace the calling of COALESCE(MAX(fileid)+1, 0) in your query with default.

And finally when you query the data use row_number() to construct the numbering dynamically.

SELECT
  ROW_NUMBER() OVER (ORDER BY fileid) AS beautiful_fileid,
  ...
FROM
  rehus.curriculumsfile;

Or if you want to achieve composite numbering (by userid for example), then you can partition by it.

ROW_NUMBER() OVER (PARTITION BY userid ORDER BY fileid) AS beautiful_fileid

Collectives™ on Stack Overflow

Manually generate PostgreSQL table ID with concurrent access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related