Duniter Squid bug for UD history custom SQL

[HugoTrentesaux]

In Duniter Squid crash for out of range integer, we had to use BigInt for Ud value instead of number, but now, the SQL function defined in udHistoryFunction_up.sql is not valid anymore and leads to database error in queries about UD. @poka do you have time to adapt this SQL query?

database logs

db-1  | 2024-10-28 14:09:19.743 UTC [2067474] ERROR:  structure of query does not match function result type
db-1  | 2024-10-28 14:09:19.743 UTC [2067474] DETAIL:  Returned type numeric does not match expected type integer in column 2.
db-1  | 2024-10-28 14:09:19.743 UTC [2067474] CONTEXT:  SQL statement "SELECT
db-1  | 	            CAST('ud-' || identity_row.name || '-' || ud.block_number || '-' || ud.event_id AS character varying) AS id,
db-1  | 	            ud.amount,
db-1  | 	            ud.block_number,
db-1  | 	            b.timestamp,
db-1  | 	            identity_row.id AS identity_id
db-1  | 	        FROM
db-1  | 	            public.universal_dividend ud
db-1  | 	        JOIN public.block b ON ud.block_number = b.height
db-1  | 	        WHERE
db-1  | 	            EXISTS (
db-1  | 	                SELECT 1
db-1  | 	                FROM (
db-1  | 	                    SELECT
db-1  | 	                        me1.block_number as creation_block,
db-1  | 	                        COALESCE(
db-1  | 	                            (
db-1  | 	                                SELECT me2.block_number
db-1  | 	                                FROM public.membership_event me2
db-1  | 	                                WHERE me2.identity_id = me1.identity_id
db-1  | 	                                    AND me2.event_type = 'Removal'
db-1  | 	                                    AND me2.block_number > me1.block_number
db-1  | 	                                ORDER BY me2.block_number
db-1  | 	                                LIMIT 1
db-1  | 	                            ),
db-1  | 	                            (SELECT MAX(block_number) FROM public.universal_dividend)
db-1  | 	                        ) as removal_block
db-1  | 	                    FROM public.membership_event me1
db-1  | 	                    WHERE me1.identity_id = identity_row.id
db-1  | 	                        AND me1.event_type = 'Creation'
db-1  | 	                ) as membership_periods
db-1  | 	                WHERE ud.block_number >= membership_periods.creation_block
db-1  | 	                    AND ud.block_number <= membership_periods.removal_block
db-1  | 	            )"
db-1  | 	PL/pgSQL function get_ud_history(identity) line 3 at RETURN QUERY
db-1  | 2024-10-28 14:09:19.743 UTC [2067474] STATEMENT:  SELECT  coalesce((json_agg("root" )->0), 'null' ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."id" AS "id", "_root.ar.root.transfersIssued"."transfersIssued" AS "transfersIssued", "_root.ar.root.transfersReceived"."transfersReceived" AS "transfersReceived", "_root.or.identity"."identity" AS "identity", "_root.or.linkedIdentity"."linkedIdentity" AS "linkedIdentity", "_root.ar.root.wasIdentity"."wasIdentity" AS "wasIdentity", 'Account' AS "__typename"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."account"  WHERE (("public"."account"."id") = (($5)::varchar))     ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.identity.base"."id" AS "id", "_root.or.identity.base"."name" AS "name", upper("_root.or.identity.base"."status" ) AS "status", "_root.or.identity.cf.udHistory"."udHistory" AS "udHistory", 'Identity' AS "__typename"       ) AS "_e"      ) ) AS "identity" FROM  (SELECT  *  FROM "public"."identity"  WHERE (("_root.base"."id") = ("account_id"))    LIMIT 1 ) AS "_root.or.identity.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("udHistory" ORDER BY "root.or.identity.cf.udHistory.pg.block_number" DESC NULLS FIRST), '[]' ) AS "udHistory" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.identity.cf.udHistory.base"."id" AS "id", "_root.or.identity.cf.udHistory.base"."timestamp" AS "timestamp", "_root.or.identity.cf.udHistory.base"."amount" AS "amount", "_root.or.identity.cf.udHistory.base"."block_number" AS "blockNumber", 'UdHistory' AS "__typename"       ) AS "_e"      ) ) AS "udHistory", "_root.or.identity.cf.udHistory.base"."block_number" AS "root.or.identity.cf.udHistory.pg.block_number" FROM  (SELECT  *  FROM "public"."get_ud_history"("_root.or.identity.base") AS "_get_ud_history" WHERE (("_get_ud_history"."block_number") > (($4)::integer))   ORDER BY "block_number" DESC NULLS FIRST LIMIT 20 ) AS "_root.or.identity.cf.udHistory.base"    ORDER BY "root.or.identity.cf.udHistory.pg.block_number" DESC NULLS FIRST  ) AS "_root.or.identity.cf.udHistory"      ) AS "_root.or.identity.cf.udHistory" ON ('true')      ) AS "_root.or.identity" ON ('true') LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.linkedIdentity.base"."id" AS "id", "_root.or.linkedIdentity.base"."name" AS "name", 'Identity' AS "__typename"       ) AS "_e"      ) ) AS "linkedIdentity" FROM  (SELECT  *  FROM "public"."identity"  WHERE (("_root.base"."linked_identity_id") = ("id"))    LIMIT 1 ) AS "_root.or.linkedIdentity.base"      ) AS "_root.or.linkedIdentity" ON ('true') LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transfersReceived" ORDER BY "root.ar.root.transfersReceived.pg.block_number" DESC NULLS FIRST), '[]' ) AS "transfersReceived" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.transfersReceived.base"."id" AS "id", "_root.ar.root.transfersReceived.base"."from_id" AS "fromId", "_root.ar.root.transfersReceived.base"."amount" AS "amount", "_root.ar.root.transfersReceived.base"."timestamp" AS "timestamp", "_root.ar.root.transfersReceived.base"."block_number" AS "blockNumber", "_root.ar.root.transfersReceived.or.comment"."comment" AS "comment", 'Transfer' AS "__typename"       ) AS "_e"      ) ) AS "transfersReceived", "_root.ar.root.transfersReceived.base"."block_number" AS "root.ar.root.transfersReceived.pg.block_number" FROM  (SELECT  *  FROM "public"."transfer"  WHERE ((("_root.base"."id") = ("to_id")) AND (("public"."transfer"."block_number") > (($3)::integer)))   ORDER BY "block_number" DESC NULLS FIRST LIMIT 10 ) AS "_root.ar.root.transfersReceived.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.transfersReceived.or.comment.base"."id" AS "id", "_root.ar.root.transfersReceived.or.comment.base"."remark" AS "remark", upper("_root.ar.root.transfersReceived.or.comment.base"."type" ) AS "type", 'TxComment' AS "__typename"       ) AS "_e"      ) ) AS "comment" FROM  (SELECT  *  FROM "public"."tx_comment"  WHERE (("_root.ar.root.transfersReceived.base"."comment_id") = ("id"))    LIMIT 1 ) AS "_root.ar.root.transfersReceived.or.comment.base"      ) AS "_root.ar.root.transfersReceived.or.comment" ON ('true')    ORDER BY "root.ar.root.transfersReceived.pg.block_number" DESC NULLS FIRST  ) AS "_root.ar.root.transfersReceived"      ) AS "_root.ar.root.transfersReceived" ON ('true') LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transfersIssued" ORDER BY "root.ar.root.transfersIssued.pg.block_number" DESC NULLS FIRST), '[]' ) AS "transfersIssued" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.transfersIssued.base"."id" AS "id", "_root.ar.root.transfersIssued.base"."to_id" AS "toId", "_root.ar.root.transfersIssued.base"."amount" AS "amount", "_root.ar.root.transfersIssued.base"."timestamp" AS "timestamp", "_root.ar.root.transfersIssued.base"."block_number" AS "blockNumber", "_root.ar.root.transfersIssued.or.comment"."comment" AS "comment", 'Transfer' AS "__typename"       ) AS "_e"      ) ) AS "transfersIssued", "_root.ar.root.transfersIssued.base"."block_number" AS "root.ar.root.transfersIssued.pg.block_number" FROM  (SELECT  *  FROM "public"."transfer"  WHERE ((("_root.base"."id") = ("from_id")) AND (("public"."transfer"."block_number") > (($2)::integer)))   ORDER BY "block_number" DESC NULLS FIRST LIMIT 10 ) AS "_root.ar.root.transfersIssued.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.transfersIssued.or.comment.base"."id" AS "id", "_root.ar.root.transfersIssued.or.comment.base"."remark" AS "remark", upper("_root.ar.root.transfersIssued.or.comment.base"."type" ) AS "type", 'TxComment' AS "__typename"       ) AS "_e"      ) ) AS "comment" FROM  (SELECT  *  FROM "public"."tx_comment"  WHERE (("_root.ar.root.transfersIssued.base"."comment_id") = ("id"))    LIMIT 1 ) AS "_root.ar.root.transfersIssued.or.comment.base"      ) AS "_root.ar.root.transfersIssued.or.comment" ON ('true')    ORDER BY "root.ar.root.transfersIssued.pg.block_number" DESC NULLS FIRST  ) AS "_root.ar.root.transfersIssued"      ) AS "_root.ar.root.transfersIssued" ON ('true') LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("wasIdentity" ), '[]' ) AS "wasIdentity" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.wasIdentity.base"."id" AS "id", "_root.ar.root.wasIdentity.base"."next_id" AS "nextId", "_root.ar.root.wasIdentity.or.identity"."identity" AS "identity", 'ChangeOwnerKey' AS "__typename"       ) AS "_e"      ) ) AS "wasIdentity" FROM  (SELECT  *  FROM "public"."change_owner_key"  WHERE (("_root.base"."id") = ("previous_id"))     ) AS "_root.ar.root.wasIdentity.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.wasIdentity.or.identity.base"."name" AS "name", 'Identity' AS "__typename"       ) AS "_e"      ) ) AS "identity" FROM  (SELECT  *  FROM "public"."identity"  WHERE (("_root.ar.root.wasIdentity.base"."identity_id") = ("id"))    LIMIT 1 ) AS "_root.ar.root.wasIdentity.or.identity.base"      ) AS "_root.ar.root.wasIdentity.or.identity" ON ('true')      ) AS "_root.ar.root.wasIdentity"      ) AS "_root.ar.root.wasIdentity" ON ('true')      ) AS "_root"      

(tracked in #38)

[poka]

fix ud amount integer (a257cc76) · Commits · nodes / duniter-squid · GitLab

[HugoTrentesaux]

TL;DR : il ne fallait pas cast le bigint en int (évidemment), mais bien changer le type de retour attendu par la fonction, donc le schéma de udHistory.

---

Chouette
Je me doutais qu’il suffirait d’indiquer un type quelque part, et donc là c’est un simple cast, j’avais juste la flemme de chercher où le mettre ><, je dirais donc “mérci poka tes contribitions à duniter v2s!”.

Est-ce que tu veux mettre en ligne sur ton indexeur https://gdev-indexer.p2p.legal/v1/graphql ? Pour l’instant vu comme son gérées les migrations, ça implique de tout réindexer, alors qu’on pourrait simplement ajouter une migration pour mettre à jour la fonction. Si on regarde la table migrations :

A priori, il suffirait d’ajouter une nouvelle migration dans /hasura/migrations, mais vu que c’est généré, ce serait plutôt dans /db/migrations. À voir si c’est compatible avec le script add_custom_squid_migrations.sh.

---

[edit] bon bah non, je me suis réjouit trop vite, ça donne évidement l’erreur “ApolloError: integer out of range”, il reste au moins des choses à faire côté client. → mais là c’est bien côté serveur que ça pêche. Essaye de faire une requête en affichant les DU récents, ceux qui sont out of range, ça devrait planter.

@HugoTrentesaux

Je me doutais qu’il suffirait d’indiquer un type quelque part, et donc là c’est un simple cast

C’était effectivement une information de type, pas un cast. Si on fait un cast d’un numeric en integer, on se retrouve avec un out of range, c’est normal. Et l’information de type manquante était dans le schéma qui définissait le type de retour de la fonction. [à suivre]

[poka]

@HugoTrentesaux

l’information de type manquante était dans le schéma

Oui clairement le plus long dans ma MR était d’écrire le bon prompt pour générer l’image…

Je savais bien en poussant le cast qui ça pèterait pour des non int, mais j’ai quand même poussé ainsi pour la beauté du geste.

A noter que je pense que le fix aurait était le bon avec Cursor en utilisant le context du workspace et pas juste le fichier SQL que j’avais ouvert. A tester pour la science. J’aime bien expérimenter ce genre de choses ^^