Recent

Author Topic: PostgreSQL: typeinfo error  (Read 2338 times)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: PostgreSQL: typeinfo error
« Reply #15 on: May 30, 2023, 04:46:30 pm »
Michael has fixed it  :D

Чебурашка

  • Hero Member
  • *****
  • Posts: 566
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #16 on: May 30, 2023, 05:16:48 pm »
Michael always fixes it :D

Jokes apart, also the problem of the table dropped and recreated with same name but different structure was there?
« Last Edit: May 30, 2023, 05:18:22 pm by Чебурашка »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: PostgreSQL: typeinfo error
« Reply #17 on: May 31, 2023, 08:47:20 am »
Yes, it now works.

Correction: it kinda works. It fails if you have fields of type json in the database, as PostgreSQL cannot compare those, so any query that tries to do that results in an error.

One more reason not to put json in a database. It's not a data type, it's a file format. They're not the same.
« Last Edit: May 31, 2023, 09:23:23 am by SymbolicFrank »

Чебурашка

  • Hero Member
  • *****
  • Posts: 566
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #18 on: May 31, 2023, 01:27:26 pm »
One more reason not to put json in a database. It's not a data type, it's a file format. They're not the same.

Agree, in the sense that should I need to put json in db I'd store it a string or binary field. The fact that such a field has a certain content is unrelevant to the rdbms.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: PostgreSQL: typeinfo error
« Reply #19 on: May 31, 2023, 03:24:04 pm »
After some consideration, I decided that probably the best way to handle these errors would be to extend PostgreSQL with a custom json equals operator. Which sounded like a lot of work, but I found this.

(Here in sequence and fixed.)

Code: SQL  [Select][+][-]
  1. CREATE OR REPLACE FUNCTION json_hash(json)
  2.   RETURNS INTEGER
  3.   LANGUAGE SQL
  4.   IMMUTABLE
  5.   STRICT
  6. AS $function$
  7.   SELECT CASE f
  8.     WHEN 'n' THEN 0
  9.     WHEN 't' THEN 1
  10.     WHEN 'f' THEN 2
  11.     WHEN '"' THEN hashtext(CAST('[' || j || ']' AS json) ->> 0)
  12.     WHEN '[' THEN COALESCE((SELECT bit_or(json_hash(e)) FROM json_array_elements($1) AS e), 3)
  13.     WHEN '{' THEN COALESCE((SELECT bit_and(hashtext(k) | json_hash(v)) FROM (SELECT DISTINCT ON (k) * FROM json_each($1) AS j(k, v) ORDER BY k, ROW_NUMBER() OVER () DESC) AS pairs), 4)
  14.     ELSE hash_numeric(CAST(CAST('[' || j || ']' AS json) ->> 0 AS NUMERIC))
  15.   END
  16.   FROM (
  17.     SELECT TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($1 AS text)) AS j
  18.   ) AS jsons,
  19.   LATERAL (
  20.     SELECT SUBSTRING(j FROM 1 FOR 1) AS f
  21.   ) AS firsts
  22. $function$;
  23.  
  24. CREATE OR REPLACE FUNCTION json_equals(json, json)
  25.   RETURNS BOOLEAN
  26.   LANGUAGE SQL
  27.   IMMUTABLE
  28.   STRICT
  29. AS $function$
  30.   SELECT CASE f1
  31.     WHEN '{' THEN -- object
  32.       CASE f2
  33.         WHEN '{' THEN (
  34.           SELECT COALESCE(bool_and(k1 IS NOT NULL AND k2 IS NOT NULL AND json_equals(v1, v2)), TRUE)
  35.           FROM (SELECT DISTINCT ON (k1) * FROM json_each($1) AS j1(k1, v1) ORDER BY k1, ROW_NUMBER() OVER () DESC) AS j1
  36.           FULL JOIN (SELECT DISTINCT ON (k2) * FROM json_each($2) AS j2(k2, v2) ORDER BY k2, ROW_NUMBER() OVER () DESC) AS j2 ON j1.k1 = j2.k2
  37.         )
  38.         ELSE FALSE
  39.       END
  40.     WHEN '[' THEN -- array
  41.       CASE f2
  42.         WHEN '[' THEN (
  43.           SELECT COALESCE(bool_and(r1 IS NOT NULL AND r2 IS NOT NULL AND json_equals(e1, e2)), TRUE)
  44.           FROM (SELECT e1, ROW_NUMBER() OVER () AS r1 FROM json_array_elements($1) AS e1) AS e1
  45.           FULL JOIN (SELECT e2, ROW_NUMBER() OVER () AS r2 FROM json_array_elements($2) AS e2) AS e2 ON e1.r1 = e2.r2
  46.         )
  47.         ELSE FALSE
  48.       END
  49.     WHEN 'n' THEN -- null
  50.       CASE f2
  51.         WHEN 'n' THEN TRUE
  52.         ELSE FALSE
  53.       END
  54.     WHEN 't' THEN -- true
  55.       CASE f2
  56.         WHEN 't' THEN TRUE
  57.         ELSE FALSE
  58.       END
  59.     WHEN 'f' THEN -- false
  60.       CASE f2
  61.         WHEN 'f' THEN TRUE
  62.         ELSE FALSE
  63.       END
  64.     WHEN '"' THEN -- string
  65.       CASE f2
  66.         WHEN '"' THEN (CAST('[' || j1 || ']' AS json) ->> 0)
  67.                     = (CAST('[' || j2 || ']' AS json) ->> 0)
  68.         ELSE FALSE
  69.       END
  70.     ELSE -- number
  71.       CASE f2
  72.         WHEN '{' THEN FALSE
  73.         WHEN '[' THEN FALSE
  74.         WHEN 'n' THEN FALSE
  75.         WHEN 't' THEN FALSE
  76.         WHEN 'f' THEN FALSE
  77.         WHEN '"' THEN FALSE
  78.         ELSE CAST(CAST('[' || j1 || ']' AS json) ->> 0 AS NUMERIC)
  79.            = CAST(CAST('[' || j2 || ']' AS json) ->> 0 AS NUMERIC)
  80.       END
  81.   END
  82.   FROM (
  83.     SELECT TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($1 AS text)) AS j1,
  84.            TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($2 AS text)) AS j2
  85.   ) AS jsons,
  86.   LATERAL (
  87.     SELECT SUBSTRING(j1 FROM 1 FOR 1) AS f1,
  88.            SUBSTRING(j2 FROM 1 FOR 1) AS f2
  89.   ) AS firsts
  90. $function$;
  91.  
  92. DROP OPERATOR IF EXISTS = (json, json) CASCADE;
  93.  
  94. CREATE OPERATOR = (
  95.   PROCEDURE   = json_equals,
  96.   LEFTARG     = json,
  97.   RIGHTARG    = json,
  98.   COMMUTATOR  = =,
  99.   RESTRICT    = eqsel,
  100.   JOIN        = eqjoinsel,
  101.   HASHES,
  102.   MERGES
  103. );
  104.  
  105. CREATE OPERATOR CLASS json_ops
  106.   DEFAULT
  107.   FOR TYPE json
  108.   USING hash AS
  109.   OPERATOR 1 =,
  110.   FUNCTION 1 json_hash(json);

It works like a charm :)

Чебурашка

  • Hero Member
  • *****
  • Posts: 566
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #20 on: May 31, 2023, 04:50:52 pm »
After some consideration, I decided that probably the best way to handle these errors would be to extend PostgreSQL with a custom json equals operator. Which sounded like a lot of work, but I found this.

(Here in sequence and fixed.)

Code: SQL  [Select][+][-]
  1.    // ...
  2.  

It works like a charm :)

My humble notes about this solution:

1. Is not necessarily portable to other RBDMS
2. You must make sure the DB is "extended" before the questioned json comparison operation takes place

(1) will not be a problem if portability is not a requirement, but if is so, then either you find a equivalent script for the other RDBMS, or you "translate" it (risk of mistakes high IMHO)
(2) could be solved either putting the necessary call in the software an run it at startup or with a dedicated installer program that makes sure DB is extended before actual software starts operating for first time.

« Last Edit: May 31, 2023, 04:53:49 pm by Чебурашка »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: PostgreSQL: typeinfo error
« Reply #21 on: June 01, 2023, 10:31:23 am »
True. But a simple EXCEPT query is far easier than doing it by hand: finding out the PK(s), making a list and then comparing all the fields individually, in code.

I might still do that when needed, but for now, this solution works.

The only error left is:
Code: Text  [Select][+][-]
  1. Execution of query failed  (PostgreSQL: ERROR:  type "earth" does not exist
  2. CONTEXT:  SQL function "ll_to_earth" during inlining

That's a tricky one. There's an index that calculates the distances to each record, it tries to inline that function, which fails because the source table is in a different database. I'm not sure I can solve it.

 

TinyPortal © 2005-2018