Change or migrate table name in local DB

Anvil Q&A
1

What I’m trying to do:
I need to change the name of a table in a local, productive DB from “image_archive” to “defect_archive”. The table has ~55k entries and 10 cols with multiple cols referencing other tables.

Here is what I am currently trying (on a copy of the live system):

  1. launch anvil-app-server (anvil-app-server --app . --uplink-key server_XXX)
  2. connect to DB using psql-anvil-app-server
  3. confirm that I am using the old schema:
postgres=# \d
                   List of relations
   Schema   |          Name           | Type |  Owner   
------------+-------------------------+------+----------
 app_tables | image_archive          | view | postgres
  1. execute ALTER TABLE image_archive RENAME TO defect_archive;
  2. confirm that the changes were applied
postgres=# \d
                   List of relations
   Schema   |          Name           | Type |  Owner   
------------+-------------------------+------+----------
 app_tables | defect_archive          | view | postgres
  1. close psql and anvil-app-server
  2. update anvil.yaml to the new schema

old schema:

  image_archive:
    client: none
    server: full
    columns:
    - name: datetime
      admin_ui: {order: 0, width: 200}
      type: datetime
      ...

new schema:

  defect_archive:
    client: none
    server: full
    columns:
    - name: datetime
      admin_ui: {order: 0, width: 200}
      type: datetime
      ...
  1. launch anvil-app-server

At this point anvil-app-server still wants to drop the old table and create a new one:

[INFO  anvil.app-server.tables] Data tables schema out of date. Here is the migration that will run if you restart Anvil with the --auto-migrate command-line flag:
[INFO  anvil.app-server.tables] ({:type :CREATE_TABLES,
  :tables
  {"defect_archive"
   {:client "none",
    :server "full",
    :columns
    ({:name "datetime",
      :admin_ui {:order 0, :width 200},
      :type "datetime"}
    :title "ImageArchive"}}}
     ...
 {:type :DELETE_TABLE, :table "image_archive"})

[INFO  anvil.app-server.tables] Anvil will now exit. Run with --ignore-invalid-schema to startup anyway, or --auto-migrate to apply the changes above.

When launching anvil-app-server with --ignore-invalid-schema and connecting via psql the table is again listed under the old name. It seems as the changes from ALTER TABLE are reverted after restarting anvil-app-server

Is there something I am missing here?

Also this whole process described above is rather cumbersome. Is there a better way to rename a table in a local DB?

2

I have managed to resolve this issue. The problem is that anvil uses some tables that are not visible in the webeditor or when using psql-anvil-app-server to determine if the schema is correct.
During launch the following psql query is performed and compared to the contents of anvil.yaml:

SELECT * FROM app_storage_access JOIN app_storage_tables ON table_id=id

That means that it is also necessary to modify app_storage_access and app_storage_tables. I have managed to perform the migration using the following SQL code:

-- required changes for renaming a the table image_archive to  defect_archive
BEGIN;

-- Update the entry in public.app_storage_access
UPDATE public.app_storage_access
SET python_name = 'defect_archive'
WHERE python_name = 'image_archive';

-- Update the entry in public.app_storage_tables
UPDATE public.app_storage_tables
SET name = 'DefectArchive'
WHERE name = 'ImageArchive';

-- Rename the table
ALTER TABLE app_tables.image_archive
RENAME TO defect_archive;
COMMIT;
1 Like