Make it easier to fix out-of-sync sequences via Mathesar UI

[polymathematic]

Description

My workflow requires me to interact with a particular Postgres table with two tools: first a script to create new records and then Mathesar to manually update details about those records. I noticed today that for this table, when I attempt to create a new record with the Mathesar interface, that the primary key column does not populate, and therefore the record cannot be created due to the PK constraint.

Expected behavior

I expected the primary key field to populate a new, unique value for the table even after other tools have inserted rows.

To Reproduce

Create a table in Mathesar. Add new records using the interface to validate normal behavior. Create a new row using an INSERT script. Attempt to create additional rows in the Mathesar interface to validate the PK is no longer populating automatically for new records.

[pavish]

@polymathematic The issue you're facing is due to the default behaviour of PostgreSQL's sequences.

When inserting rows into PostgreSQL tables, if values are manually specified for columns with sequences, the sequences might go out-of-sync. When combined with PK or unique keys, this might cause duplicate key errors.

Let me first try to explain the cause, and then I'll provide ways to resolve it.

Cause

1. Let's create a table called authors in Postgres, with a primary key column that uses a sequence.

   CREATE TABLE authors (
     id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     name text
   );

   The table columns would look like the following:

   # \d authors;
   
                             Table "public.authors"
   Column |  Type  | Collation | Nullable |             Default              
   -------+--------+-----------+----------+----------------------------------
   id     | bigint |           | not null | generated by default as identity
   name   | text   |           |          | 
   Indexes:
       "authors_pkey" PRIMARY KEY, btree (id)
   

2. Insert rows without specifying any values for id. (This is how Mathesar inserts rows).

   INSERT INTO authors (name) VALUES('Pikachu');
   INSERT INTO authors (name) VALUES('Raichu');

   These will succeed. The rows will look like the following:

   select * from authors;
   
   id  |  name   
   ----+---------
     1 | Pikachu
     2 | Raichu
   (2 rows)

3. Now insert rows by manually specifying id.

   INSERT INTO authors (id, name) VALUES(3, 'Ash');
   INSERT INTO authors (id, name) VALUES(4, 'Misty');

   These will also succeed. The rows will look like the following:

   select * from authors;
   
   id |  name   
   ----+---------
     1 | Pikachu
     2 | Raichu
     3 | Ash
     4 | Misty
   (4 rows)

   However, these commands make the sequence for id go out-of-sync. The value maintained by the PostgreSQL sequence for id column would still be 3.

4. Now, try inserting, without specifying a value for id as we did in step 2 above.

   INSERT INTO authors (name) VALUES('Brock');
   
   ERROR:  duplicate key value violates unique constraint "authors_pkey"
   DETAIL:  Key (id)=(3) already exists.
   

   Since the sequence is still at 3, Postgres tries to insert the row with value 3 for the id column. But there is already a row with the same value, and so the insert query fails.

Ways to resolve this for your usecase

Approach 1 (Recommended)

In your scripts, do not manually specify values for the id column for the INSERT queries.

✅ Use:

INSERT INTO authors (name) VALUES ('somename');

❌ instead of:

INSERT INTO authors VALUES (1, 'somename');

If you maintain a different id of sorts, eg., StudentID, it is recommended to create a different column for it instead of using the default 'id' column.

Approach 2 (only if you have to specify a value for id manually)

Run your INSERT scripts, and run the following to fix the out-of-sync sequence.

1. Find the name of the sequence:

   SELECT pg_get_serial_sequence('public.authors', 'id');

   Result:

   pg_get_serial_sequence 
   ------------------------
   public.authors_id_seq
   (1 row)
   

2. Update the sequence public.authors_id_seq to the max value of id.

   SELECT setval('public.authors_id_seq',(SELECT GREATEST(MAX(id), nextval('public.authors_id_seq')-1) FROM public.authors));

   Result:

   setval 
   --------
         4
   (1 row)
   

   Replace the command above with your sequence name and the table names. This stackoverflow thread might be useful.

   After this, the INSERT commands without specifying id would work.

   This approach can be used to fix the sequences for tables you've already created in Mathesar as described in your issue.

What we can try to do in Mathesar

Though this is the expected behaviour in Postgres, we will take your usecase into consideration and try to make it easier to fix sequence out-of-sync states via the UI. While we cannot guarantee that we would do this, we will discuss it as a team and track any updates on this issue. We will keep the issue open until then.

Feel free to update us if you've managed to fix this and/or if you have questions/thoughts.

[pavish]

I'm updating the title to better describe what we can do in Mathesar.

[polymathematic]

Thank you for the detailed response. This makes total sense and I learned something new about Postgres today.