Ytukyg

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', 'user@example.com', '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.

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.