Track a "column FOO does not exist" bug.

[Riccardo Cagnasso]

I have a table Activity that had a strategic_project_name column.

I removed the strategic_project_name column from the declarative definition of the Activity table and then the strategic_project_name column from the database itself.

Now I get this error message every time I try to update the one Activity record.

  File "/home/phas/virtualenvs/o35/lib/python3.5/site-packages/SQLAlchemy-1.3.8-py3.5-linux-x86_64.egg/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "strategic_project_name" does not exist

[SQL: UPDATE activity SET subtitle=%(subtitle)s WHERE activity.id = %(activity_id)s]
[parameters: {'activity_id': 200, 'subtitle': 'gjh'}]

which is very odd because the SQL doesn't contain the column strategic_project_name

If I manually create a strategic_project_name column in the database activity table, it starts to work again, so it's not referring to some different table.


I can't track why does sqlalchemy think that Activity would still have a "strategic_project_name" column. I removed all the occurrencies of the word "strategic_project_name" from my whole project, but I'm still getting this error.

Do you have any advice on how to track this in the internals of sqlalchemy?

[Steven James]

Do you have a trigger or a constraint that references that column?

[Riccardo Cagnasso]

I don't think so. Wouldn't postgres prevented me deleting the column if it were?

[Mike Bayer]

the internals of SQLAlchemy don't have this string name either.   your best bet would be to turn on logging in the Postgresql server and see what it's seeing directly

https://www.postgresql.org/docs/9.0/runtime-config-logging.html

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/729b8817-a4e5-45ef-b31d-17772ab9016b%40googlegroups.com.

[Jonathan Vanasco]

On Thursday, September 5, 2019 at 8:58:16 AM UTC-4, Riccardo Cagnasso wrote:

I don't think so. Wouldn't postgres prevented me deleting the column if it were?

PostgreSQL will prevent you from deleting the column if it were a CONSTRAINT, but would not necessarily notice it on a trigger / function.

I would check the db to see if there are any triggers/functions that used that column.

I would also trash any/all .pyc and .pyo files (or other compiled filed) to make sure you're not picking up an old file. That sometimes happens.

For example:

 CREATE TABLE foo_bar (
 id INT PRIMARY KEY,
 foo BOOLEAN DEFAULT NULL,
 bar BOOLEAN DEFAULT NULL
 );


 CREATE OR REPLACE FUNCTION foo_bar__trigger() RETURNS trigger AS $foo_bar__trigger$
 BEGIN
 IF NEW.foo is True
 THEN
 NEW.bar := True;
 END IF;
 RETURN NEW;
 END;
 $foo_bar__trigger$ LANGUAGE plpgsql;


 DROP TRIGGER IF EXISTS foo_bar__trigger on foo_bar;


 CREATE TRIGGER foo_bar__trigger BEFORE INSERT OR UPDATE ON foo_bar
 FOR EACH ROW EXECUTE PROCEDURE foo_bar__trigger();


 ALTER TABLE foo_bar DROP foo;
 
 INSERT INTO foo_bar (id, foo) VALUES (1, True);

That creates this error:

ERROR: record "new" has no field "bar"
 CONTEXT: PL/pgSQL function foo_bar__trigger() line 5 at assignment