CODEWITHSUNDEEP
pythonherokuheroku-postgrespython-telegram-bot
Kron
Kron

Reputation: 75

Heroku postgres DB discards changes every time I run/deploy the application

I'm developing a bot in python for telegram using the python-telegram-bot framework.

Right now it's being deployed on heroku as dyno worker and I've also linked it to an Heroku Postgres DB using the hobby-dev plan.

The problem is, every time I deploy the app (or re-run it locally, the local version also connects to the same DB) the DB discards all the changes the app makes to it while running.

A very bizarre fact is that the DB looks like to somehow keep track of those changes even though the rows I add to the tables disappear, as for example the "serial" value of the 'id' column considers the deleted rows (see the table below).

This is one of the tables the DB contains (the "channel and "creator" columns contain fake telegram chat ids as they're not relevant to the case):

id |   question    |          creation          | mode | status |    channel     |  creator  
----+---------------+----------------------------+------+--------+----------------+-----------

  1 | PollQuestion1 | 2019-06-08 13:08:25.240002 | S    | t      | 0001 | 002

  2 | PollQuestion2 | 2019-06-08 13:08:26.830526 | S    | t      | 0001 | 002

  3 | PollQuestion3 | 2019-06-08 23:31:21.574572 | S    | t      | 0001 | 002

  4 | Poll4Question | 2019-06-09 16:35:58.440345 | S    | t      | 0001 | 002

  7 | PQ5           | 2019-06-09 17:42:14.172598 | S    | f      | 0001 | 002

The polls with id 5 and 6 where added by my application but disappeared after re-deploying it, but as you can see the last one I manually added from the heroku pg:psql terminal has id = 7.

This is a sample function which interacts with the db:

def execute_query(query):
    try:
        res = cur.execute("""{}""".format(query))
        return res
    except psycopg2.Error as e:
        logger.error("Failed executing query {0}: ({1}) {2}".format(query, e.diag.severity, e.diag.message_primary))

def test_command():
    execute_query("insert into poll (question, mode, status, channel, creator) values ('PollT1', 'S', true, '{0}', '{1}')".format(0001, 0002))

If I then executed the query

"select * from Poll"

from within the application, the result of the first query can be found there.

Instead, if I ran the same

"select * from Poll"

query after re-deploying/re-running locally the app, the result of the query contained in the test() function is missing (as you can see from the table above, I performed the same operations twice and therefore the row I later manually added has id = 7).

I know about the ephemeral file-system heroku adopts, but I also read this shouldn't apply to Heroku Postgres and therefore the DB should store the changes I make from within my app, although it's apparently not so.

Note 1: I experienced this issue while issuing both an "INSERT" query and an "UPDATE" one. Note 2: While performing those operations I constantly monitor the console output of the application and no errors occur.

Has anyone had the same issue and knows how to solve it or can point me to the cause of this problem?

Upvotes: 1

Views: 409

Answers (1)

TrebledJ
TrebledJ

Reputation: 8997

You need to commit your changes to the database by calling connection.commit().

commit()

Commit any pending transaction to the database.

By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.

Assuming you have a conn variable representing your connection object, you could call conn.commit() immediately after executing your query. However, the safer, more Pythonic way of ensuring this is to use the with-statement context manager for both your connection and cursor.

def execute_query(query):
    try:
        with conn:
            with conn.cursor() as cur:
                res = cur.execute("""{}""".format(query))
                return res
    except psycopg2.Error as e:
        logger.error("Failed executing query {0}: ({1}) {2}".format(query, e.diag.severity, e.diag.message_primary))

This automatically calls commit() if the query is successful and does a rollback() if the query is unsuccessful.

Upvotes: 1

Related Questions