Row not created but increments primary key












1















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.










share|improve this question

























  • Sounds like a missing commit somewhere in your code

    – a_horse_with_no_name
    Nov 23 '18 at 13:02











  • @a_horse_with_no_name It runs through the same function that is updating, inserting and deleting to and from other tables correctly. A commit is definitely called.

    – Adam Mitchell
    Nov 23 '18 at 13:03








  • 4





    Generation of the primary key using a sequence happens before constraints are checked. If some constraint is violated the records will not be inserted but the sequence would be incremented. May it be the problem in your case?

    – Roman Konoval
    Nov 23 '18 at 13:14













  • @RomanKonoval I don't believe so but I will do some testing around this area. If this was the case, however, would psql not throw an error?

    – Adam Mitchell
    Nov 23 '18 at 13:17











  • @RomanKonoval On second thoughts, this can't be the issue. The same query (using identical values) works when I INSERT locally.

    – Adam Mitchell
    Nov 23 '18 at 13:20


















1















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.










share|improve this question

























  • Sounds like a missing commit somewhere in your code

    – a_horse_with_no_name
    Nov 23 '18 at 13:02











  • @a_horse_with_no_name It runs through the same function that is updating, inserting and deleting to and from other tables correctly. A commit is definitely called.

    – Adam Mitchell
    Nov 23 '18 at 13:03








  • 4





    Generation of the primary key using a sequence happens before constraints are checked. If some constraint is violated the records will not be inserted but the sequence would be incremented. May it be the problem in your case?

    – Roman Konoval
    Nov 23 '18 at 13:14













  • @RomanKonoval I don't believe so but I will do some testing around this area. If this was the case, however, would psql not throw an error?

    – Adam Mitchell
    Nov 23 '18 at 13:17











  • @RomanKonoval On second thoughts, this can't be the issue. The same query (using identical values) works when I INSERT locally.

    – Adam Mitchell
    Nov 23 '18 at 13:20
















1












1








1








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.










share|improve this question
















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.







sql python-3.x postgresql psycopg2






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 23 '18 at 13:02









a_horse_with_no_name

306k46468567




306k46468567










asked Nov 23 '18 at 12:40









Adam MitchellAdam Mitchell

7952629




7952629













  • Sounds like a missing commit somewhere in your code

    – a_horse_with_no_name
    Nov 23 '18 at 13:02











  • @a_horse_with_no_name It runs through the same function that is updating, inserting and deleting to and from other tables correctly. A commit is definitely called.

    – Adam Mitchell
    Nov 23 '18 at 13:03








  • 4





    Generation of the primary key using a sequence happens before constraints are checked. If some constraint is violated the records will not be inserted but the sequence would be incremented. May it be the problem in your case?

    – Roman Konoval
    Nov 23 '18 at 13:14













  • @RomanKonoval I don't believe so but I will do some testing around this area. If this was the case, however, would psql not throw an error?

    – Adam Mitchell
    Nov 23 '18 at 13:17











  • @RomanKonoval On second thoughts, this can't be the issue. The same query (using identical values) works when I INSERT locally.

    – Adam Mitchell
    Nov 23 '18 at 13:20





















  • Sounds like a missing commit somewhere in your code

    – a_horse_with_no_name
    Nov 23 '18 at 13:02











  • @a_horse_with_no_name It runs through the same function that is updating, inserting and deleting to and from other tables correctly. A commit is definitely called.

    – Adam Mitchell
    Nov 23 '18 at 13:03








  • 4





    Generation of the primary key using a sequence happens before constraints are checked. If some constraint is violated the records will not be inserted but the sequence would be incremented. May it be the problem in your case?

    – Roman Konoval
    Nov 23 '18 at 13:14













  • @RomanKonoval I don't believe so but I will do some testing around this area. If this was the case, however, would psql not throw an error?

    – Adam Mitchell
    Nov 23 '18 at 13:17











  • @RomanKonoval On second thoughts, this can't be the issue. The same query (using identical values) works when I INSERT locally.

    – Adam Mitchell
    Nov 23 '18 at 13:20



















Sounds like a missing commit somewhere in your code

– a_horse_with_no_name
Nov 23 '18 at 13:02





Sounds like a missing commit somewhere in your code

– a_horse_with_no_name
Nov 23 '18 at 13:02













@a_horse_with_no_name It runs through the same function that is updating, inserting and deleting to and from other tables correctly. A commit is definitely called.

– Adam Mitchell
Nov 23 '18 at 13:03







@a_horse_with_no_name It runs through the same function that is updating, inserting and deleting to and from other tables correctly. A commit is definitely called.

– Adam Mitchell
Nov 23 '18 at 13:03






4




4





Generation of the primary key using a sequence happens before constraints are checked. If some constraint is violated the records will not be inserted but the sequence would be incremented. May it be the problem in your case?

– Roman Konoval
Nov 23 '18 at 13:14







Generation of the primary key using a sequence happens before constraints are checked. If some constraint is violated the records will not be inserted but the sequence would be incremented. May it be the problem in your case?

– Roman Konoval
Nov 23 '18 at 13:14















@RomanKonoval I don't believe so but I will do some testing around this area. If this was the case, however, would psql not throw an error?

– Adam Mitchell
Nov 23 '18 at 13:17





@RomanKonoval I don't believe so but I will do some testing around this area. If this was the case, however, would psql not throw an error?

– Adam Mitchell
Nov 23 '18 at 13:17













@RomanKonoval On second thoughts, this can't be the issue. The same query (using identical values) works when I INSERT locally.

– Adam Mitchell
Nov 23 '18 at 13:20







@RomanKonoval On second thoughts, this can't be the issue. The same query (using identical values) works when I INSERT locally.

– Adam Mitchell
Nov 23 '18 at 13:20














1 Answer
1






active

oldest

votes


















1














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.






share|improve this answer
























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446906%2frow-not-created-but-increments-primary-key%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    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.






    share|improve this answer




























      1














      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.






      share|improve this answer


























        1












        1








        1







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 23 '18 at 16:49









        AndrejusAndrejus

        264




        264
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446906%2frow-not-created-but-increments-primary-key%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()