hasura / hasura/graphql-engine

Postgres Function not added when running migrations

Open
#9,653 3 comments 0 reactions 0 assignees View on GitHub
k/bug support/needs-more-info
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.