Question 89121 in PostgreSQL Question posted in
The official documentation can be found here.

AWS lambda does not update rds postgres table but the execution is successful – Postgresql

I have a postgres DB in RDS. When I connect it from my local and insert into it via sqlalchemy, I can see the updated results but when I run the same code from Lambda, the execution is shown as completed (i.e code runs correctly) but the results are not updated in the DB. In the same code when I print out result of `SELECT * table_name*, I can see the new row addition but when I check from local (using both pgadmin and through code) whether the row was really added or not, it it is missing.

One strange thing I observed is that I have a column ‘ID’ which is a primary key. When I add the row with ID 25 from lambda followed by adding another row from local, the row added from local is assigned row 27 (instead of 26 which was assigned to row added from lambda, however I can’t see the row with id 26).

The following is my code:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, text
engine = create_engine("postgresql://%s:%s@%s:%s/%s" % (username, password, host, port, database))
conn = engine.connect()
print("CONNECTED TO RDS")

query = 'select * from predictions'
result = conn.execute(text(query)).fetchall()
print("THE QUERY RESULT IS", 'n', result)
upload_query = "INSERT INTO predictions (image_url, image_class) VALUES ('LOCAL_ADD', 'LOCAL_ADD');"
conn.execute(text(upload_query))
query = 'select * from predictions'
result = conn.execute(text(query)).fetchall()
print("THE QUERY RESULT IS", 'n', result)

conn.close()

My lambda has no VPC to it. However my RDS DB has 4 security groups with two of them being inbound/outbound rules set to ‘All Traffic’ with ‘0.0.0.0’

Tags:

2

Answers


  1. 0

    Enable autocommit or explicitly COMMIT the transacion in the Lambda function.

    Sequence values are updated whenever the next value is requested. This occurs outside of the transaction. It can be viewed as occurring in an independent transaction. This is necessary so that sequences can be used by concurrent transactions without introducing waits or deadlocks.

    Login or Signup to reply.
  2. 0

    You’ve executed the changes with conn.execute, but if autocommit is not set within the lambda session then the changes won’t be issued to the database until you commit them with conn.commit.
    So add the line conn.commit() like so:

    conn.execute(text(upload_query))
conn.commit()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search