Is there a way to avoid calling nextval () if the insertion does not work in PostgreSQL?

Question

In a PostgreSQL database, I have a table with a primary key and another field that must be unique.

CREATE TABLE users ( id INTEGER PRIMARY KEY DEFAULT nextval('groups_id_seq'::regclass), name VARCHAR(255) UNIQUE NOT NULL ); INSERT users (name) VALUES ('foo'); INSERT users (name) VALUES ('foo'); INSERT users (name) VALUES ('bar'); 

The second insert fails, but the sequence of groups_id_seq is already increasing, so when "bar" is added, it leaves a space in the id numbers.

Is it possible to tell PostgreSQL to get the next value only if other constraints are met, or should I check SELECT first if the name is not duplicated? This still does not guarantee the absence of spaces, but at least it will reduce their number to rare cases when there is another process trying to insert the same name at the same time.

Answer 1

I don’t think so: the main feature of sequences is that spaces are possible (think of two parallel transactions, with one of them doing ROLLBACK). You must ignore spaces. Why is this a problem in your case?

Answer 2

If you need gapless sequences - there are ways to do this, but this is not trivial, but definitely much slower.

Also - if you are worried about "using too many identifiers" - just define id as bigserial.

Answer 3

You can, though cumbersome, do it. As bortzmeyer says , it is dangerous to rely on values ​​from sequences that are contiguous, so it's best to leave it as it is if you can.

If you can not:

Every access to the table, which can lead to the fact that the row will have a specific name (i.e. each INSERT to this table), and if you allow (albeit carelessly) every UPDATE that can change the name ) should do this inside a transaction that first blocks soemthing . The simplest and least efficient option is to simply lock the entire table with LOCK users IN EXCLUSIVE MODE (adding the last three words allows parallel access for reading by other processes, which is safe).

However, this is a very crude lock that slows down performance if there are many simultaneous changes to users ; a better option would be to block one matching row in another table that should already exist. This row can be locked with SELECT ... FOR UPDATE . This only makes sense when working with a "child" table, which has an FK dependency on another "parent" table.

For example, imagine that we are actually trying to safely create new orders for a customer and that these orders somehow identify the "names". (I know a bad example ...) orders has an FK dependency on customers . Then, to prevent the creation of two orders with the same name for this customer, you can do the following:

 BEGIN; -- Customer 'jbloggs' must exist for this to work. SELECT 1 FROM customers WHERE id = 'jbloggs' FOR UPDATE -- Provided every attempt to create an order performs the above step first, -- at this point, we will have exclusive access to all orders for jbloggs. SELECT 1 FROM orders WHERE id = 'jbloggs' AND order_name = 'foo' -- Determine if the preceding query returned a row or not. -- If it did not: INSERT orders (id, name) VALUES ('jbloggs', 'foo'); -- Regardless, end the transaction: END; 

Please note: it’s not enough just to lock the corresponding row in users with SELECT ... FOR UPDATE - if the row does not exist yet, several simultaneous processes can simultaneously indicate that the row does not exist, and then try to perform simultaneous inserts, which leads to unsuccessful transactions and therefore sequence gaps.

Any locking scheme will work; The important thing is that anyone trying to create a string with the same name should try to lock the same object .