Backstage: Backend migrations fail on Postgres for ~v0.1.1-alpha.15

[andrewthauer]

After upgrading a standalone app around 0.1.1-alpha.15, I started getting migration failures for a new migration. I tried dropping and re-creating the databases and same problem. Have not tried sqlite3 and assume it may work fine.

Expected Behavior

The migrations run correctly.

Current Behavior

The following error occurs:

migration file "20200721115244_location_update_log_latest_deduplicate.js" failed
migration failed with error: 
  CREATE VIEW location_update_log_latest AS
  SELECT t1.* FROM location_update_log t1
  JOIN 
  (
     SELECT location_id, MAX(created_at) AS MAXDATE
     FROM location_update_log
     GROUP BY location_id
  ) t2
  ON t1.location_id = t2.location_id
  AND t1.created_at = t2.MAXDATE
  GROUP BY t1.location_id
  ORDER BY created_at DESC;
 - column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function

Possible Solution

?

Your Environment

• NodeJS Version (v12): 12
• Operating System and Version (e.g. Ubuntu 14.04): macOS
• Postgres Version: 12.3 running in docker (debian)

[benjdlambert]

@shmidt-i I guess this is related to the changes with the service catalog polling?

[shmidt-i]

Yes, seems so. Will look into that👍

[andrewthauer]

@shmidt-i - I have not tested this for correctness, but this will run without error on both pg & sqlite3 clients:

SELECT t1.*
FROM location_update_log t1
  JOIN 
  (
    SELECT location_id, MAX(created_at) AS MAXDATE
    FROM location_update_log
    GROUP BY location_id
  ) t2
    ON t1.location_id = t2.location_id AND t1.created_at = t2.MAXDATE
GROUP BY t1.id, t1.location_id
ORDER BY created_at DESC;

Just added the GROUP BY t1.id

[shmidt-i]

Yes, that part I figured, however atm I'm looking into bringing the pg workflow into our end-to-end test to avoid this kind of situations ;)

[andrewthauer]

Yeah, that's a great idea!