Latest update crashes with PostgreSQL

[TobiasDax]

Description

When i try to update Super Table to the latest versions, or install on a fresh system i get stuck in SQL Errors

Steps to reproduce

1. Setup a fresh craft installation with PostgreSQL DB
2. Install Super Table
3. Crash

Additional info

Eine der Migrationen von Super Table ist fehlgeschlagen.

Database Exception: SQLSTATE[42703]: Undefined column: 7 ERROR: column mbt.handle does not exist
LINE 1: SELECT "f"."handle", "mbt"."handle" AS "blockTypeHandle"
^
HINT: Perhaps you meant to reference the column "f.handle".
The SQL being executed was: SELECT "f"."handle", "mbt"."handle" AS "blockTypeHandle"
FROM "fields" "f"
INNER JOIN "fieldlayoutfields" "flf" ON "flf"."fieldId" = "f"."id"
INNER JOIN "supertableblocktypes" "mbt" ON "mbt"."fieldLayoutId" = "flf"."layoutId"
WHERE "mbt"."fieldId"=125

Migration: verbb\supertable\migrations\m190117_000003_fix_supertablecontent_tables

Output:

update in {{%fields}} ... done (time: 0.064s)
update in {{%fields}} ... done (time: 0.004s)
update in {{%fields}} ... done (time: 0.006s)
update in {{%fields}} ... done (time: 0.004s)
update in {{%fields}} ... done (time: 0.004s)
update in {{%fields}} ... done (time: 0.004s)
update in {{%fields}} ... done (time: 0.004s)
execute SQL: ALTER TABLE "public"."assetindexdata" DISABLE TRIGGER ALL; ALTER TABLE "public"."assets" DISABLE TRIGGER ALL; ALTER TABLE "public"."assettransformindex" DISABLE TRIGGER ALL; ALTER TABLE "public"."assettransforms" DISABLE TRIGGER ALL; ALTER TABLE "public"."categories" DISABLE TRIGGER ALL; ALTER TABLE "public"."categorygroups" DISABLE TRIGGER ALL; ALTER TABLE "public"."categorygroups_sites" DISABLE TRIGGER ALL; ALTER TABLE "public"."content" DISABLE TRIGGER ALL; ALTER TABLE "public"."craftidtokens" DISABLE TRIGGER ALL; ALTER TABLE "public"."deprecationerrors" DISABLE TRIGGER ALL; ALTER TABLE "public"."elementindexsettings" DISABLE TRIGGER ALL; ALTER TABLE "public"."elements" DISABLE TRIGGER ALL; ALTER TABLE "public"."elements_sites" DISABLE TRIGGER ALL; ALTER TABLE "public"."entries" DISABLE TRIGGER ALL; ALTER TABLE "public"."entrydrafts" DISABLE TRIGGER ALL; ALTER TABLE "public"."entrytypes" DISABLE TRIGGER ALL; ALTER TABLE "public"."entryversions" DISABLE TRIGGER ALL; ALTER TABLE "public"."fieldgroups" DISABLE TRIGGER ALL; ALTER TABLE "public"."fieldlayoutfields" DISABLE TRIGGER ALL; ALTER TABLE "public"."fieldlayouts" DISABLE TRIGGER ALL; ALTER TABLE "public"."fieldlayouttabs" DISABLE TRIGGER ALL; ALTER TABLE "public"."fields" DISABLE TRIGGER ALL; ALTER TABLE "public"."globalsets" DISABLE TRIGGER ALL; ALTER TABLE "public"."info" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixblocks" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixblocktypes" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_1" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_2" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_3" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_4" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_5" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_6" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_7" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_aufklappboxelemente_8" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_1" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_2" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_3" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_4" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_5" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_6" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_7" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_inhalt_8" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_matrix" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_mbookdeutsch_matrix" DISABLE TRIGGER ALL; ALTER TABLE "public"."matrixcontent_mguidereptilien_matrix" DISABLE TRIGGER ALL; ALTER TABLE "public"."migrations" DISABLE TRIGGER ALL; ALTER TABLE "public"."plugins" DISABLE TRIGGER ALL; ALTER TABLE "public"."queue" DISABLE TRIGGER ALL; ALTER TABLE "public"."relations" DISABLE TRIGGER ALL; ALTER TABLE "public"."resourcepaths" DISABLE TRIGGER ALL; ALTER TABLE "public"."searchindex" DISABLE TRIGGER ALL; ALTER TABLE "public"."sections" DISABLE TRIGGER ALL; ALTER TABLE "public"."sections_sites" DISABLE TRIGGER ALL; ALTER TABLE "public"."sequences" DISABLE TRIGGER ALL; ALTER TABLE "public"."sessions" DISABLE TRIGGER ALL; ALTER TABLE "public"."shunnedmessages" DISABLE TRIGGER ALL; ALTER TABLE "public"."sitegroups" DISABLE TRIGGER ALL; ALTER TABLE "public"."sites" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_15_erkundungspunkte" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_366cc98f-b067-4624-be4f-906e269fcd17_inhalt" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_3_inhalt" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_56_inhalt" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_60_erkundungspunkte" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_66_inhalt" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_70_erkundungspunkte" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_865851af-d9c4-4152-845a-ebdb9a7f7594_erkundungspunkte" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_erkundungspunkte" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_flipcards" DISABLE TRIGGER ALL; ALTER TABLE "public"."stc_inhalt" DISABLE TRIGGER ALL; ALTER TABLE "public"."structureelements" DISABLE TRIGGER ALL; ALTER TABLE "public"."structures" DISABLE TRIGGER ALL; ALTER TABLE "public"."supertableblocks" DISABLE TRIGGER ALL; ALTER TABLE "public"."supertableblocktypes" DISABLE TRIGGER ALL; ALTER TABLE "public"."systemmessages" DISABLE TRIGGER ALL; ALTER TABLE "public"."taggroups" DISABLE TRIGGER ALL; ALTER TABLE "public"."tags" DISABLE TRIGGER ALL; ALTER TABLE "public"."templatecacheelements" DISABLE TRIGGER ALL; ALTER TABLE "public"."templatecachequeries" DISABLE TRIGGER ALL; ALTER TABLE "public"."templatecaches" DISABLE TRIGGER ALL; ALTER TABLE "public"."tokens" DISABLE TRIGGER ALL; ALTER TABLE "public"."usergroups" DISABLE TRIGGER ALL; ALTER TABLE "public"."usergroups_users" DISABLE TRIGGER ALL; ALTER TABLE "public"."userpermissions" DISABLE TRIGGER ALL; ALTER TABLE "public"."userpermissions_usergroups" DISABLE TRIGGER ALL; ALTER TABLE "public"."userpermissions_users" DISABLE TRIGGER ALL; ALTER TABLE "public"."userpreferences" DISABLE TRIGGER ALL; ALTER TABLE "public"."users" DISABLE TRIGGER ALL; ALTER TABLE "public"."volumefolders" DISABLE TRIGGER ALL; ALTER TABLE "public"."volumes" DISABLE TRIGGER ALL; ALTER TABLE "public"."widgets" DISABLE TRIGGER ALL; ... done (time: 0.032s)
execute SQL: create table {{%stc_inhalt_1}} as select * from {{%stc_inhalt}} ... done (time: 0.008s)
add primary key stc_inhalt_1_id_pk on {{%stc_inhalt_1}} (id) ... done (time: 0.009s)
execute SQL: create sequence [[stc_inhalt_1_id_seq]] start with 1 owned by [[stc_inhalt_1.id]] ... done (time: 0.005s)
execute SQL: alter table {{%stc_inhalt_1}} alter column [[id]] set default nextval('stc_inhalt_1_id_seq') ... done (time: 0.002s)
execute SQL: alter table {{%stc_inhalt_1}} alter column [[elementId]] set not null ... done (time: 0.002s)
execute SQL: alter table {{%stc_inhalt_1}} alter column [[siteId]] set not null ... done (time: 0.003s)
execute SQL: alter table {{%stc_inhalt_1}} alter column [[dateCreated]] set not null ... done (time: 0.002s)
execute SQL: alter table {{%stc_inhalt_1}} alter column [[dateUpdated]] set not null ... done (time: 0.001s)
execute SQL: alter table {{%stc_inhalt_1}} alter column [[uid]] set not null ... done (time: 0.001s)
create unique index stc_inhalt_1_elementId_siteId_unq_idx on {{%stc_inhalt_1}} (elementId,siteId) ... done (time: 0.006s)
add foreign key stc_inhalt_1_elementId_fk: {{%stc_inhalt_1}} (elementId) references {{%elements}} (id) ... done (time: 0.011s)
add foreign key stc_inhalt_1_siteId_fk: {{%stc_inhalt_1}} (siteId) references {{%sites}} (id) ... done (time: 0.010s)
delete from {{%stc_inhalt_1}} ... done (time: 0.008s)
Exception: SQLSTATE[42703]: Undefined column: 7 ERROR: column mbt.handle does not exist
LINE 1: SELECT "f"."handle", "mbt"."handle" AS "blockTypeHandle"
^
HINT: Perhaps you meant to reference the column "f.handle".
The SQL being executed was: SELECT "f"."handle", "mbt"."handle" AS "blockTypeHandle"
FROM "fields" "f"
INNER JOIN "fieldlayoutfields" "flf" ON "flf"."fieldId" = "f"."id"
INNER JOIN "supertableblocktypes" "mbt" ON "mbt"."fieldLayoutId" = "flf"."layoutId"
WHERE "mbt"."fieldId"=125 (/Users/arendt.t/sites/craft/vendor/yiisoft/yii2/db/Schema.php:664)
#0 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'SELECT "f"."han...')
#1 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT "f"."han...')
#2 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/db/Query.php(161): yii\db\Query->all(NULL)
#5 /Users/arendt.t/sites/craft/vendor/verbb/super-table/src/migrations/m190117_000003_fix_supertablecontent_tables.php(139): craft\db\Query->all()
#6 /Users/arendt.t/sites/craft/vendor/verbb/super-table/src/migrations/m190117_000003_fix_supertablecontent_tables.php(101): verbb\supertable\migrations\m190117_000003_fix_supertablecontent_tables->_cleanUpTable(125, '{{%stc_inhalt_1...', Array)
#7 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/db/Migration.php(56): verbb\supertable\migrations\m190117_000003_fix_supertablecontent_tables->safeUp()
#8 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/db/MigrationManager.php(243): craft\db\Migration->up(true)
#9 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/db/MigrationManager.php(163): craft\db\MigrationManager->migrateUp(Object(verbb\supertable\migrations\m190117_000003_fix_supertablecontent_tables))
#10 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/services/Updates.php(223): craft\db\MigrationManager->up()
#11 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(445): craft\services\Updates->runMigrations(Array)
#12 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/controllers/UpdaterController.php(208): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#13 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#14 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#15 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#16 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/web/Controller.php(109): yii\base\Controller->runAction('migrate', Array)
#17 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('migrate', Array)
#18 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/web/Application.php(297): yii\base\Module->runAction('updater/migrate', Array)
#19 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/web/Application.php(683): craft\web\Application->runAction('updater/migrate')
#20 /Users/arendt.t/sites/craft/vendor/craftcms/cms/src/web/Application.php(243): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#21 /Users/arendt.t/sites/craft/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#22 /Users/arendt.t/sites/craft/web/index.php(21): yii\base\Application->run()
#23 /Users/arendt.t/.composer/vendor/laravel/valet/server.php(151): require('/Users/arendt.t...')
#24 {main}

[engram-design]

This should be fixed in 2.1.4.2

[engram-design]

Additionally, 2.1.5, introduces a new "Content Table Checker/Fixer" which I'd recommend people run via http://mysite.com/actions/super-table/plugin/check-content-tables to ensure everything is okay. This check won't write any changes, but will give you the opportunity to do so if any errors our found.

If any errors are found, you can run the fixer. Database backups are triggered before the fixer runs as well, just in case something goes wrong at that point.