CODEWITHSUNDEEP
sqlpython-3.xpostgresqlpsycopg2
amitchone
amitchone

Reputation: 1638

Row not created but increments primary key

I am attempting to insert new rows into the following PostgreSQL table: 

                                       Table "public.users"
    Column     |           Type           | Collation | Nullable |                   Default
---------------+--------------------------+-----------+----------+----------------------------------------------
 user_id       | integer                  |           | not null | nextval('define_user_user_id_seq'::regclass)
 time_created  | timestamp with time zone |           | not null |
 is_active     | boolean                  |           | not null | true
 email_address | text                     |           | not null |
 password_hash | character varying(255)   |           | not null |
 first_name    | text                     |           |          |
 second_name   | text                     |           |          |

Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
Referenced by:
    TABLE "user_to_device" CONSTRAINT "user_to_device_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id)
    TABLE "user_to_horse" CONSTRAINT "user_to_horse_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id)

The table currently only contains 10 records as it is still being used for development. There is no scope to modify the table.

My issue is that, when updating the table from a REST API the operation seemingly successfully and returns a new user_id; upon querying the table, the supposedly created user is not in the table.

If I then create a user manually (SSH'd into the server that's running psql) and use the exact same query then the operation is successful and the newly created user can be seen. Interestingly, the user_id value increments from the value created by the query triggered by the REST API.

This suggests to me that the query triggered via the REST API is successful (?) because the user_id that it creates seems to be recognised by subsequent queries - so why then does the new user not appear in the table?

No errors are thrown at all. Here's the query that I'm using to create a user:

INSERT INTO users (password_hash, is_active, first_name, email_address, second_name, time_created) VALUES ('mypasswordhash', True, 'Orson', '[email protected]', 'Cart', '2018-11-23T12:23:00Z') RETURNING user_id;

I am using psycopg2 from within Python 3.6 when querying via the API. I have multiple other API endpoints that INSERT successfully into other tables so I'm not sure at all what the issue is. Any help is greatly appreciated as this has me truly stumped, thanks.

Upvotes: 1

Views: 206

Answers (1)

Andrejus
Andrejus

Reputation: 26

Are you absolutely sure your commit function is called? In some cases if you yield or return before committing, the function is aborted before your changes get committed.

In this case, I would expect to see an incremented ID without an inserted row, as primary keys get incremented before the query is checked. If your connection terminates abruptly, the row won't get committed.

Your best bet would be to examine your PostgreSQL server logs.

Upvotes: 1

Related Questions