graphql-engine: "database query error" with event trigger when mutation happens through v1/query or v2/query

{
	"type": "http-log",
	"timestamp": "2021-06-19T18:33:42.160+0000",
	"level": "error",
	"detail": {
		"operation": {
			"user_vars": {
				"x-hasura-role": "admin"
			},
			"error": {
				"internal": {
					"statement": "\n           SELECT hdb_catalog.insert_event_log($1, $2, $3, $4, $5)\n                ",
					"prepared": true,
					"error": {
						"exec_status": "FatalError",
						"hint": null,
						"message": "invalid input syntax for type json",
						"status_code": "22P02",
						"description": "The input string ended unexpectedly."
					},
					"arguments": ["(Oid 25,Just (\"public\",Binary))", "(Oid 25,Just (\"medical_providers\",Binary))", "(Oid 25,Just (\"MP\",Binary))", "(Oid 25,Just (\"MANUAL\",Binary))", "(Oid 114,Just (\"{\\\"row\\\":{\\\"is_direct_billing\\\":false,\\\"email\\\":null,\\\"status\\\":0,\\\"phone\\\":null,\\\"expired_at\\\":null,\\\"latitude\\\":\\\"10.68894\\\",\\\"address\\\":null,\\\"is_excluded\\\":false,\\\"name\\\":\\\"an giang\\\",\\\"medical_provider_id\\\":\\\"130a52ef-e155-4879-9a67-5c98672917c8\\\",\\\"updated_at\\\":\\\"2021-06-19T18:02:17.232539+00:00\\\",\\\"deleted_at\\\":null,\\\"geocode_data\\\":{\\\"Municipality\\\":\\\"Ch\\195\\162u \\196\\144\\225\\187\\145c\\\",\\\"Country\\\":\\\"VNM\\\",\\\"SubRegion\\\":\\\"An Giang\\\",\\\"Geometry\\\":{\\\"Point\\\":[105.148,10.68894]},\\\"Label\\\":\\\"B\\225\\187\\135nh Vi\\225\\187\\135n \\196\\144a Khoa Khu V\\225\\187\\177c T\\225\\187\\137nh An Giang, Ch\\195\\162u \\196\\144\\225\\187\\145c, Vi\\225\\187\\135t Nam\\\",\\\"Neighborhood\\\":\\\"Ph\\198\\176\\225\\187\\157ng V\\196\\169nh M\\225\\187\\185\\\"},\\\"province\\\":null,\\\"created_at\\\":\\\"2021-06-19T17:54:08.038602+00:00\\\",\\\"type\\\":\\\"BVDK_C\\\",\\\"warning_level\\\":0,\\\"longitude\\\":\\\"105.148\\\",\\\"description\\\":null}}\",Binary))"]
				},
				"path": "$.args",
				"error": "database query error",
				"code": "unexpected"
			},
			"request_id": "08561e4b-f940-44c4-af1f-22159fcd051a",
			"response_size": 1643,
			"query": {
				"resource_version": null,
				"args": {
					"payload": {
						"row": {
							"is_direct_billing": false,
							"email": null,
							"status": 0,
							"phone": null,
							"expired_at": null,
							"latitude": "10.68894",
							"address": null,
							"is_excluded": false,
							"name": "an giang",
							"medical_provider_id": "130a52ef-e155-4879-9a67-5c98672917c8",
							"updated_at": "2021-06-19T18:02:17.232539+00:00",
							"deleted_at": null,
							"geocode_data": {
								"Municipality": "Châu Đốc",
								"Country": "VNM",
								"SubRegion": "An Giang",
								"Geometry": {
									"Point": [105.148, 10.68894]
								},
								"Label": "Bệnh Viện Đa Khoa Khu Vực Tỉnh An Giang, Châu Đốc, Việt Nam",
								"Neighborhood": "Phường Vĩnh Mỹ"
							},
							"province": null,
							"created_at": "2021-06-19T17:54:08.038602+00:00",
							"type": "BVDK_C",
							"warning_level": 0,
							"longitude": "105.148",
							"description": null
						}
					},
					"name": "MP",
					"source": "default"
				},
				"version": 1,
				"type": "pg_invoke_event_trigger"
			}
		},
		"request_id": "08561e4b-f940-44c4-af1f-22159fcd051a",
		"http_info": {
			"status": 500,
			"http_version": "HTTP/1.1",
			"url": "/v1/metadata",
			"ip": "172.19.0.1",
			"method": "POST",
			"content_encoding": null
		}
	}
}

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 10
Comments: 38 (12 by maintainers)

Most upvoted comments

We know the root cause and will fix it soon.

+12

tirumaraiselvan on Jul 22, 2021

Hello folks, thank you for reporting the issue and your patience. This has been addressed in the latest release of the graphql-engine (v2.0.7). Apologies for the delay and any inconvenience caused.

kolharsam on Aug 18, 2021

This has now been fixed in 2.0.9 and rollout of the same version is happening on Cloud as well.

Sorry for the delay on this, there were 2 issues in this report and the second one turned out to be quite complicated.

tirumaraiselvan on Sep 8, 2021

we are investigatting this folks

rikinsk on Jul 19, 2021

Any updates on this @tirumaraiselvan @kolharsam? Thanks in advance! ❤️

anttiviljami on Aug 9, 2021

i have user table and using events to run when my laravel application trying to save user information it through error

Undefined function: 7 ERROR: function hdb_catalog.insert_event_log(text, text, text, text, json) does not exist\nLINE 1: SELECT hdb_catalog.insert_event_log(CAST('public' AS text), ...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\nQUERY: SELECT hdb_catalog.insert_event_log(CAST('public' AS text), CAST('users' AS text), CAST('SendEmailVerificationNotification' AS text), TG_OP, _data)\nCONTEXT: PL/pgSQL function hdb_catalog."notify_hasura_SendEmailVerificationNotification_INSERT"() line 29 at PERFORM (SQL: insert into "users"

Qasim-Aziz on Jun 26, 2023

Hey we can’t launch in Production because of this issue. We “upgraded” Hasura just before moving to production and now we can’t launch for a month now. Can you folks make this a priority (or invest to give us some more runway?) It’s feeling like Hasura was a big mistake. 💰 🚀 🔥

Maxhodges on Aug 16, 2021

@frandiox Thanks for the confirmation. The original issue was for manual event trigger which has been fixed in 2.0.7

We have found another issue when mutation happens through v1/query or v2/query which is related to your comment. We will fix this ASAP. Sorry for the inconvenience.

tirumaraiselvan on Aug 20, 2021

I have similar problem. In our case it’s a trigger triggered by remote schema mutation.

invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: 
funkcja PL/pgSQL hdb_catalog.insert_event_log(text,text,text,text,json), wiersz 12 w przypisanie
SQL statement \"SELECT hdb_catalog.insert_event_log(CAST('public' AS text), CAST('rungs_userrung' AS text), CAST('updateUserRung' AS text), TG_OP, _data)\"
funkcja PL/pgSQL hdb_catalog.\"notify_hasura_updateUserRung_UPDATE\"(), wiersz 25 w PERFORM

jakubste on Oct 17, 2023

@joserocha3 @frandiox Yes, we have a fix in progress and expect to land in few days. I will re-open this issue and change the title to reflect the same.

tirumaraiselvan on Sep 6, 2021

@Varun-Choudhary @rikinsk Hi! This problem will prevent us from deploying our production app in a few weeks so I just wanted to ask: do you think this bug will be handled soonish or should I start adding pg client + DB credentials in my Hasura events for raw SQL access? Thanks!

frandiox on Jul 13, 2021

I can see this error after upgrading to Hasura 2.0.1-Cloud (from 1.3.3 in Docker) when using raw SQL statements (without even retrying events):

"statement": "\n          INSERT INTO order_fees(order_id, type_id, amount_usd)\n          VALUES ('f75ce340-af2d-42bd-96a8-1dc91c4d4d05', 'PERCENT', 557), ('f75ce340-af2d-42bd-96a8-1dc91c4d4d05', 'MINIMUM', 0)\n          ON CONFLICT (order_id, type_id) WHERE item_id IS null DO UPDATE SET amount_usd = EXCLUDED.amount_usd;\n        ",
"prepared": false,
"error": {
  "exec_status": "FatalError",
  "hint": null,
  "message": "invalid input syntax for type json",
  "status_code": "22P02",
  "description": "The input string ended unexpectedly."
},
"arguments": []

The SQL statement is like this:

INSERT INTO order_fees(order_id, type_id, amount_usd)
VALUES ('f75ce340-af2d-42bd-96a8-1dc91c4d4d05', 'PERCENT', 557), ('f75ce340-af2d-42bd-96a8-1dc91c4d4d05', 'MINIMUM', 0)
ON CONFLICT (order_id, type_id) WHERE item_id IS null DO UPDATE SET amount_usd = EXCLUDED.amount_usd;

The reason we use raw SQL for things like this is because Hasura does not support some features we need yet. It works if I paste this code in the console directly, but not from the API. I’ve tried using the deprecated /v1/query and the new /v2/query with run_sql type.

frandiox on Jul 13, 2021