Unable to INSERT/UPDATE to Postgres SQL Database on Heroku #118
Closed this issue · 17 comments
This was an issue that was previous posted here under the #118 and it was closed by the staff with the argument that it was fixed but it hasn't been.
I'm trying to deploy an app on Hedoku using the latest version of CS50 and the bug still there. I'm gonna just copy and paste the original message from #118 cause it's exactly the same problem:
"After completing the CS50 Finance assignment, I attempted to move the application to Heroku. I converted the SQLite3 database over to Postgres. I am able to connect to the Heroku database using:
from CS50 import SQL
db = SQL("Heroku---URI")
SELECT statements do work and does retrieve the requested data. However, INSERT and UPDATE statements did not work for me. I was able to INSERT and UPDATE to the database using my computer's command line, as well as from pgAdmin 4.
Finally, I was able to get my CS50 Finance working by importing the sqlalchemy module instead, and adding .fetchall() to the end of SELECT statements and after INSERT/UPDATE statements I added the db.commit() statement in a line below it to save the changes.
I didn't see any additional notes (such as .commit()) in the documentation on how to get the CS50 module to work with a Postgres database. Sorry in advance if there's something that I missed. CS50 is such a tremendous class by the way, it's absolutely brilliant! 👍"
Hi @LuisFlavioOliveira! Thanks for opening this issue! I can confirm that the issue still exists, but it's caused by something different. We'll deploy a fix asap.
Hello @kzidane, thanks for your reply.
Nice! I'm gonna fork and see if I can help you guys too.
Wish you a good day,
Luis
@LuisFlavioOliveira actually just to double-check something, does your table not have a primary key?
Indeed, it has not a primary key.
So, to be more precise: The 'INSERT' works perfectly with a table that has a primary key but not with tables using foreign keys (just tested it).
I am having the exact same problem, I can see my update and insert going through the application but it doesn't change my Postgres. When I try to execute the exact same SQL command on the https://adminer.cs50.net/ it works.
Thank you in advance! 👍🏻
This bug still seems to exist. My final project on heroku has been encountering problems with writing-queries as well. The strange thing is that altho the insert queries do not work the majority of the time, sometimes they actually do work...
Is this bug still being looked at or is supposed to be fixed?
Since insert queries work on tables with a primary key, would it be an acceptable solution to give a primary key to all tables that don't have one already (not to use but to bypass this bug)?
Any help/advise would be much appreciated
Hi all, sorry for the quietude. If you do pip3 show cs50, what version are you using if still seeing this error?
Hi all, sorry for the quietude. If you do
pip3 show cs50, what version are you using if still seeing this error?
Hi @dmalan,
Thanks for your answer.
I'm using the version: 5.0.4.
Thanks, @LuisFlavioOliveira. We're still testing on our end too, but want to try out the transactions branch in the meantime, https://github.com/cs50/python-cs50/tree/transactions? Should suffice to update your requirements.txt to contain:
git+git://github.com/cs50/python-cs50@transactions
instead of just:
cs50
This should now be fixed as of >= 6.0.1!
Hello im still facing the Exact same issue described by LuisCardosoOliveria
@luiscardosooliveira and @AtharvaKirkole, recall which INSERT queries were failing for you? And able to dump your PostgreSQL schema that describes your tables?
Reproducible with:
sys.path.insert(0, "../src")
from cs50 import SQL
db = SQL("postgresql://postgres@localhost/test')
db.execute("DROP TABLE IF EXISTS baz")
db.execute("CREATE TABLE baz (id INTEGER, val CHAR(3))")
db.execute('INSERT INTO baz (id, val) VALUES (?, ?)', 1, 'baz')
print(db.execute('SELECT * FROM baz'))
db.execute('BEGIN TRANSACTION')
db.execute('INSERT INTO baz (id, val) VALUES (?, ?)', 2, 'baz')
db.execute('COMMIT')
print(db.execute('SELECT * FROM baz'))
Errs with:
[{'id': 1, 'val': 'baz'}]
[{'id': 1, 'val': 'baz'}]
Exception caught at
Line 360 in d827119
(psycopg2.errors.ObjectNotInPrerequisiteState) lastval is not yet defined in this session
which induces a ROLLBACK on PostgreSQL.
Documenting for posterity:
Previously, our distro for Finance included a finance.db with:
CREATE TABLE users (id INTEGER, username TEXT NOT NULL, hash TEXT NOT NULL, cash NUMERIC NOT NULL DEFAULT 10000.00, PRIMARY KEY(id));
CREATE UNIQUE INDEX username ON users (username);
which, per dimitri/pgloader#563 (comment), relied on ROWID instead of an INDEX, per https://www.sqlite.org/lang_createtable.html#rowid, which pgloader depends on. Changing future distro to
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, hash TEXT NOT NULL, cash NUMERIC NOT NULL DEFAULT 10000.00);
CREATE UNIQUE INDEX username ON users (username);
should facilitate pgloader creating an autoincrementing users table a la:
$ \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+-----------------------------------
id | bigint | | not null | nextval('users_id_seq'::regclass)
username | text | | |
hash | text | | |
cash | numeric | | | 10000.00
Indexes:
"idx_16403_users_pkey" PRIMARY KEY, btree (id)
"idx_16403_username" UNIQUE, btree (username)
The absence of autoincrementing IDs likely explains why PostgreSQL INSERTs tended to raise exceptions (because of our calls to LASTVAL) that silently triggered ROLLBACKs (because we caught the exceptions).
Changing the distro should thus ensure that our own tables remain autoincrementing in PostgreSQL, and catching the exception at the DB level, per 9342365#diff-d12a5fb846ae94c77fc681c21dd86952fa2213c659ec05fe213dbe4e47468056R358-R375, should ensure that other INSERTs on tables without SERIALs or nextval sequences don't trigger ROLLBACKs.
@luiscardosooliveira and @AtharvaKirkole, mind trying v8.0.2 now?
Locally:
pip3 install --upgrade cs50
In a requirements.txt:
cs50==8.0.2