Support Hasura queries
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Keeping track of common Hasura queries.
SELECT FOR UPDATE across shards if hdb_catalog.event_log is distributed:
```sql
UPDATE hdb_catalog.event_log
SET locked = 't'
WHERE id IN ( SELECT l.id
FROM hdb_catalog.event_log l
WHERE l.delivered = 'f' and l.error = 'f' and l.locked = 'f'
and (l.next_retry_at is NULL or l.next_retry_at <= now())
and l.archived = 'f'
ORDER BY created_at
LIMIT $1
FOR UPDATE SKIP LOCKED )
RETURNING id, schema_name, table_name, trigger_name, payload::json, tries, created_at;
update hdb_catalog.hdb_action_log set status = 'processing'
where
id in (
select id from hdb_catalog.hdb_action_log
where status = 'created'
for update skip locked limit 10
)
returning
id, action_name, request_headers::json, session_variables::json, input_payload::json;
```
Insert with volatile function in returning:
```sql
WITH "hasura_test__mutation_result_alias" AS
(
insert INTO "public"."hasura_test"
(
"value",
"key"
)
VALUES
(
(
$1
)
::jsonb,
($2)::text
)
returning * ,
CASE
WHEN 'true' THEN NULL
ELSE "hdb_catalog"."check_violation"
(
'insert check constraint failed'
)
END ), "hasura_test__all_columns_alias" AS
(
SELECT "key" ,
"value"
FROM "hasura_test__mutation_result_alias" )
SELECT json_build_object('returning',
(
SELECT COALESCE(json_agg("root" ), '[]' ) AS "root"
FROM (
SELECT row_to_json(
(
SELECT "_1_e"
FROM (
SELECT ) AS "_1_e" ) ) AS "root"
FROM (
SELECT *
FROM "hasura_test__all_columns_alias"
WHERE (
'true') ) AS "_0_root.base" ) AS "_2_root" ), 'affected_rows',
(
SELECT count(*)
FROM "hasura_test__all_columns_alias" ) )
```
Example event trigger:
```
CREATE OR REPLACE FUNCTION hdb_views."notify_hasura_test_trigger_INSERT"()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
_old record;
_new record;
_data json;
BEGIN
IF TG_OP = 'UPDATE' THEN
_old := row(OLD );
_new := row(NEW );
ELSE
/* initialize _old and _new with dummy values for INSERT and UPDATE events*/
_old := row((select 1));
_new := row((select 1));
END IF;
_data := json_build_object(
'old', NULL,
'new', row_to_json(NEW )
);
BEGIN
IF (TG_OP <> 'UPDATE') OR (_old <> _new) THEN
PERFORM hdb_catalog.insert_event_log(CAST(TG_TABLE_SCHEMA AS text), CAST(TG_TABLE_NAME AS text), CAST('test_trigger' AS text), TG_OP, _data);
END IF;
EXCEPTION WHEN undefined_function THEN
IF (TG_OP <> 'UPDATE') OR (_old *<> _new) THEN
PERFORM hdb_catalog.insert_event_log(CAST(TG_TABLE_SCHEMA AS text), CAST(TG_TABLE_NAME AS text), CAST('test_trigger' AS text), TG_OP, _data);
END IF;
END;
RETURN NULL;
END;
$function$
```
With hdb_catalog.insert_event_log:
```sql
CREATE OR REPLACE FUNCTION hdb_catalog.insert_event_log(schema_name text, table_name text, trigger_name text, op text, row_data json)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
id text;
payload json;
session_variables json;
server_version_num int;
trace_context json;
BEGIN
id := gen_random_uuid();
server_version_num := current_setting('server_version_num');
IF server_version_num >= 90600 THEN
session_variables := current_setting('hasura.user', 't');
trace_context := current_setting('hasura.tracecontext', 't');
ELSE
BEGIN
session_variables := current_setting('hasura.user');
EXCEPTION WHEN OTHERS THEN
session_variables := NULL;
END;
BEGIN
trace_context := current_setting('hasura.tracecontext');
EXCEPTION WHEN OTHERS THEN
trace_context := NULL;
END;
END IF;
payload := json_build_object(
'op', op,
'data', row_data,
'session_variables', session_variables,
'trace_context', trace_context
);
INSERT INTO hdb_catalog.event_log
(id, schema_name, table_name, trigger_name, payload)
VALUES
(id, schema_name, table_name, trigger_name, payload);
RETURN id;
END;
$function$
```
Contributor guide
Assessment
This issue has not been assessed yet.