hasura / hasura/graphql-engine
Postgres Function not added when running migrations
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
### Version Information
Server Version: v2.25.0
CLI Version (for CLI related issue): v2.25.0
Postgres Mac App: v2.6.2 (190)
Postgres: v15.3
### Environment
OSS on Docker Mac with native Postgres (non-docker).
I was also able to replicate the issue using docker-based postgres: postgis/postgis:15-3.3-alpine
### What is the current behaviour?
I successfully generated a migration via the SQL GUI and logic was added to the database. However, when running the cli migration command when setting up a new instance - the logic (Postgres CREATE FUNCTION) does not get added to the database and there are no failure/error warnings from Hasura indicating an issue. And then I'm warned about a metadata inconsistency issue (due to database logic not getting added).
The same create function logic works if I manually add it with my database client (TablePlus) but not not via Hasura migration file. I've seen this before and specifically with Postgres database create function logic.
### What is the expected behaviour?
Add Postgres Function to database using CLI migrations.
### How to reproduce the issue?
1. Manually add Postgres function via SQL UI
2. Rerun migrations (silent failure: function is not added)
3. Confirm that create function works by adding manually via database client
### Screenshots or Screencast
N/A
### Please provide any traces or logs that could help here.
I tried this generic function (and several others):
```sql
CREATE TABLE jsonb_result (
result jsonb
);
CREATE OR REPLACE FUNCTION add_key_value_to_jsonb(input jsonb, key text, value text)
RETURNS SETOF jsonb_result AS $$
BEGIN
INSERT INTO jsonb_result (result)
VALUES (input || jsonb_build_object(key, value))
RETURNING jsonb_result.*;
END;
$$ LANGUAGE plpgsql;
```
### Any possible solutions/workarounds you're aware of?
Manually add create function script via 3rd party database client
### Keywords
migration, cli, postgres
Contributor guide
Research direction
Reproduce the supplied CREATE TABLE and CREATE FUNCTION migration with the CLI against PostgreSQL 15.3, then trace the CLI migration execution path to determine why the function is silently skipped. Done means the function is created on a fresh instance through migrations and no metadata inconsistency is reported.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgres
- Domain
- cli, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100