Automatically resolve inconsistencies that are solved by a `CREATE` instruction during a migration

### Version Information

Server Version: latest

### Environment

All environments.

### What is the current behaviour?

This issue has been extracted from #8807.

When applying a `CREATE` DDL instruction to resolve some inconsistencies, the inconsistencies are not resolved. An explicit `reload_metadata` or `replace_metadata` request needs to be made to resolve them.

### What is the expected behaviour?

We expect that the inconsistencies are automatically resolved if the `CREATE` instruction has modified the schema to be compatible with the metadata.

### How to reproduce the issue?

See the above issue for an example.

### Any possible solutions?

The following is copied from @0x777's comment in https://github.com/hasura/graphql-engine/issues/8807#issuecomment-1250900910:

> The inconsistencies raised by migrations normally get resolved after the final hasura metadata apply

Interesting. I have not considered this at all. So why isn't the schema cache being rebuilt after the last `pg_run_sql`, which would've resolved the inconsistencies?

```haskell
queryModifiesSchema :: RQLQuery -> Bool
queryModifiesSchema = \case
RQInsert _ -> False
RQSelect _ -> False
RQUpdate _ -> False
RQDelete _ -> False
RQCount _ -> False
RQRunSql q -> Postgres.isSchemaCacheBuildRequiredRunSQL q
RQCitusRunSql q -> Postgres.isSchemaCacheBuildRequiredRunSQL q
RQCockroachRunSql q -> Postgres.isSchemaCacheBuildRequiredRunSQL q
RQMssqlRunSql q -> MSSQL.isSchemaCacheBuildRequiredRunSQL q
RQMysqlRunSql _ -> False
RQBigqueryRunSql _ -> False
RQBigqueryDatabaseInspection _ -> False
RQBulk l -> any queryModifiesSchema l

Postgres.isSchemaCacheBuildRequiredRunSQL :: RunSQL -> Bool
Postgres.isSchemaCacheBuildRequiredRunSQL RunSQL {..} =
case rTxAccessMode of
Q.ReadOnly -> False
Q.ReadWrite -> fromMaybe (containsDDLKeyword rSql) rCheckMetadataConsistency
where
containsDDLKeyword =
TDFA.match
$$( quoteRegex
TDFA.defaultCompOpt
{ TDFA.caseSensitive = False,
TDFA.multiline = True,
TDFA.lastStarGreedy = True
}
TDFA.defaultExecOpt
{ TDFA.captureGroups = False
}
"\\balter\\b|\\bdrop\\b|\\breplace\\b|\\bcreate function\\b|\\bcomment on\\b"
)
```

We seem to return `false` when the SQL statement is something along `create table(..)`. If I remember correctly, this design was meant to prevent a consistent object becoming inconsistent. Hence, we only look for the keywords `alter`, `replace` etc, i.e, statements which could potentially change the behaviour of a metadata object (`create function` I think exists because you can create overloaded functions and that causes function objects in metadata to be come inconsistent). However we never really accounted for the transition of a metadata object from inconsistent to consistent. Maybe we should take a look at this again.

### Can you identify the location in the source code where the problem exists?

See above.

### If the bug is confirmed, would you be willing to submit a PR?

Yes, I plan on working on this.

Start with issue #8807 and the shown queryModifiesSchema and Postgres.isSchemaCacheBuildRequiredRunSQL functions. Trace how CREATE SQL is handled during migrations and how schema-cache inconsistencies are resolved. Done means a CREATE that makes metadata compatible automatically resolves those inconsistencies without an explicit reload_metadata or replace_metadata request.

Written by the indexing model from the issue text.