DAL is dropping column when not supposed to

[Alex Glaros]

Problem trying to add a column when Postgres is the database

When I add the column in DAL with either migrate=True, fake_migrate, or fake_migrate_all, nothing happens on Postgres side.  I just get "column does not exist" from web2py error.

I then add the column from within Postgres itself which makes error go away but the column never appears in web2py

Web2py sql.log below shows that DAL seems to be deleting the column when it should be adding it.

ALTER TABLE SkillPersonIntersection DROP COLUMN response_time_expectation_in_days;

success!

success!

success!

success!

success!

Does any one have a sure-fire way of make a change in DAL appear on the physical Postgres table and have the column also appear within web2py?

thanks,

Alex Glaros

[Anthony]

We need to see some code.

[Alex Glaros]

For db connection I've used every combination possbile below (migrate_enabled=True, fake_migrate, etc.) including every combination of deleting or leaving table w2p databases folder, dropping the table from Postgres, dropping from web2py, etc.

db = DAL('postgres://postgres:MyPasswordlocalhost:5432/postgres', pool_size=1,check_reserved=['all'], migrate_enabled=True, lazy_tables=False, fake_migrate_all = False) #migrate=False # fake_migrate_all = True #fake_migrate=True, 

It doesn't matter which table I use, if I add a column in db.py (with migrate_enabled=True, which is what always worked with sqLite), it doesn't get added in either w2p or the Postgres table. No errors, just doesn't appear as a new field.

If I use fake_migrate = True, error is: <class 'psycopg2.ProgrammingError'> column "reputation_comments" of relation "reputation" does not exist 

sql.log below shows new column added, then inexplicably deleted without any action on my part.

timestamp: 2016-02-07T18:48:25.390000

ALTER TABLE Reputation ADD reputation_comments VARCHAR(512);

faked!

success!

success!

success!

success!

timestamp: 2016-02-07T18:48:29.605000

ALTER TABLE Reputation DROP COLUMN reputation_comments;

thanks,

Alex Glaros

[Anthony]

We need to see the model code.

[Alex Glaros]

same results regardless of table or field.  Example below where new field is added at bottom:

BEFORE

db.define_table('Reputation',

    Field('reputation_code', 'string'),

    Field('reputation_description', 'string'))

AFTER

db.define_table('Reputation', 

    Field('reputation_code', 'string'),

    Field('reputation_description', 'string'),

    Field('reputation_comments', 'string')) 

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/YfeaAMef33c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

Specify the model as you want it. If that model is consistent with the current state of the database, run the code once with fake_migrate so web2py will update the migration metadata. If that model is not consistent with the database, then delete the relevant *.table file and run the code with a regular migration so web2py will create any missing fields.

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Alex Glaros]

If that model is consistent with the current state of the database, run the code once with fake_migrate so web2py will update the migration metadata.

Well,, I forced it to be consistent with model by creating the field on Postgres side.  When ran fake_migrate, I received 

 column "reputation_comments" of relation "reputation" does not exist 

If that model is not consistent with the database, then delete the relevant *.table file and run the code with a regular migration so web2py will create any missing fields.

To test in this situation, I deleted the file in the web2py databases folder, and, dropped the table from the Postgres side. Ran migrate=True, and for some reason it remembered the old table model, not the version with the new field.  It created the old table with no errors on the Postgres side.  Why would it remember the old model? I clearly saved the new model version in db.py.

[Marin Pranjić]

Hey Alex,

could it be that you define the table twice in the code?

Marin

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Alex Glaros]

okay, just tested on PythonAnywherre and it works perfectly as Anthony and the manual describe.

it is something related to my specific setup at home

will test on different desktop tomorrow

[Anthony]

Is your app compiled? If so, changes you make to the .py files will have no effect unless you remove the compiled files.

Anthony

[Alex Glaros]

no, I did not compile.

Postgres changes created by DAL work correctly on site #1 local machine. They only do not work on site #2 when I copy the app from site #1.

Could the problem be how I transfer the development to site #2?  

On site #1 I do a "pack all" in web2py, and a "pg_dump" on Postgres.

Then at site #2, I do an "Upload and install packed application" on web2py and a "pg_restore" on Postgres.  At this point, I receive "table already exists" errors, so I delete all databases in web2py database folder, drop and re-create Postgres database from within Postgres, and re-run pg_restore.  Now the app on site #2 looks and works identical to site #1...until I try modifying the model.  Then I get the behaviour described above where DAL changes never get implemented either on the Postgres side or the web2py side. sql.log shows the changes implemented then inexplicably deleted. The model is stuck in time.

Did I copy from site #1 to #2 incorrectly?

[Niphlod]

do a pack all, install the app, run pg_restore, don't run any code. set fake_migrate_all=True, hit appadmin, set fake_migrate_all=False. voilà.

[Alex Glaros]

the whole problem, which I spent weeks on, was that the web2py.exe was or became corrupted in a subtle way.

I downloaded a fresh (same version) and everything worked perfectly.

Alex 

[Alex Glaros]

better description of the problem:

DAL updates to change/add columns don't work when Postgres has data in it

If I create duplicate empty version of Postgres schema, then collumns get created/changed correctly

If I try on production copy of schema that has data, it doesn't take

does this description describe a problem that can be solved?

To work around the problem, I first update the columns in an empty copy of Postgres schema then copy the Postgres results to the production copy that has data in it.  E.g., ALTER TABLE public.roletype ADD COLUMN role_type_super_object_fk integer; 

thanks,

Alex