PostgreSQL: typeinfo error lazarus / 16-Databases ---------------------------------------------------------------------- SymbolicFrank · 2023-05-24 14:45 If I run this query in pgAdmin, it works: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT * FROM _schema1.table1 EXCEPT SELECT * FROM _schema2.table1; But through an TSQLQuery, it gives this error: Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Error getting typeinfo  (PostgreSQL: ERROR:  syntax error at or near "3910"LINE 1: 3910 In function TPGHandle.Exec, from unit PQConnection. What can be the problem? Does the prepare fail or such? ---------------------------------------------------------------------- SymbolicFrank · 2023-05-25 08:20 Using a debug build of FPC and Lazarus and inserting all the field names instead of the asterisks makes no difference. I have no idea where to look for a fix. Edit: the error seems to occur in TPQConnection.AddFieldDefs, when collection the type info of the TFields in the dataset. That sounds like it is probably simpler to try and use the low-level PostgreSQL API than to try and fix that. ---------------------------------------------------------------------- Чебурашка · 2023-05-25 08:46 Quote from: SymbolicFrank on May 25, 2023, 10:20:36 amUsing a debug build of FPC and Lazarus and inserting all the field names instead of the asterisks makes no difference. I have no idea where to look for a fix. Hello, sorry for that. I don't know if this is the case, but in the past I was analyzing the behaviour of the db framework when using sqlite. My feeling was that the automation that creates the fields with their types for the resulting dataset, is organized in a way that favours the operations versus tables (which is also somehow natural). When entities in the aren't directly tables (or, at least they are but they are difficult to identify like that) the automation not always works as one would expect. ---------------------------------------------------------------------- SymbolicFrank · 2023-05-25 09:49 Is this repairable by me, or is it easier to look for a low-level solution, what do you think? ---------------------------------------------------------------------- Чебурашка · 2023-05-25 10:27 Quote from: SymbolicFrank on May 25, 2023, 11:49:47 amIs this repairable by me, or is it easier to look for a low-level solution, what do you think? When I had to deal with sqlite issues, I implemented local workarounds instead of touching the fpc rtl/packages. First because once I will upgrade fpc ecosystem, I will only need to adapt workarounds in my code, not patches to it. Second, honestly, because modifying fpc distribution is not easy. After posting first, I had an idea, maybe could help: if fields in _schema1.table1 are known (not a table subject to change that you what it to be handles anyway by code by * even if changed over time) you could try creating a view on that query result on postgres server side. After that TSQLQuery should encounter less diffculties while determining the filed types, something like: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  create view _schema1.ViewOftable1 (vcolumn1, vcolumn2, ...) as SELECT column1, column2, ... FROM _schema1.table1 EXCEPT SELECT * FROM _schema2.table1; ---------------------------------------------------------------------- SymbolicFrank · 2023-05-25 11:12 Ok, I'll try. I'm trying to compare two different databases, so I map one of them to a schema in the other, like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   MyConnection.DatabaseName := 'database2';  MyConnection.Open;  MyConnection.ExecuteDirect('drop schema if exists _original cascade;');  MyConnection.ExecuteDirect('create schema _original;');  MyConnection.ExecuteDirect('set search_path to _current;');  MyConnection.ExecuteDirect('create extension if not exists postgres_fdw;');  MyConnection.ExecuteDirect('create server if not exists orgsrv foreign data wrapper postgres_fdw options (host ''localhost'', dbname ''database1'', port ''5432'');');  MyConnection.ExecuteDirect('create user mapping if not exists for postgres server orgsrv options (user '''', password '''');');  MyConnection.ExecuteDirect('import foreign schema _current from server orgsrv into _original;');  pgAdmin sees them as "foreign tables", but in a query you can simply specify schema.table.field without any problem. I do it like this, because in PostgreSQL, you cannot query over multiple databases directly. But that might be too complex for the TPQConnection.AddFieldDefs to resolve in this specific case. Although for simpler queries it works well, as I use that to import lots of data. ---------------------------------------------------------------------- Чебурашка · 2023-05-25 11:32 Quote from: SymbolicFrank on May 25, 2023, 01:12:15 pmOk, I'll try. I'm trying to compare two different databases, so I map one of them to a schema in the other, like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   MyConnection.DatabaseName := 'database2';  MyConnection.Open;  MyConnection.ExecuteDirect('drop schema if exists _original cascade;');  MyConnection.ExecuteDirect('create schema _original;');  MyConnection.ExecuteDirect('set search_path to _current;');  MyConnection.ExecuteDirect('create extension if not exists postgres_fdw;');  MyConnection.ExecuteDirect('create server if not exists orgsrv foreign data wrapper postgres_fdw options (host ''localhost'', dbname ''database1'', port ''5432'');');  MyConnection.ExecuteDirect('create user mapping if not exists for postgres server orgsrv options (user '''', password '''');');  MyConnection.ExecuteDirect('import foreign schema _current from server orgsrv into _original;');  pgAdmin sees them as "foreign tables", but in a query you can simply specify schema.table.field without any problem. I do it like this, because in PostgreSQL, you cannot query over multiple databases directly. But that might be too complex for the TPQConnection.AddFieldDefs to resolve in this specific case. Although for simpler queries it works well, as I use that to import lots of data. What about using dblinks? That should allow you to access the external db data without doing import. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE VIEW myremote_pg_proc AS  SELECT *    FROM dblink('dbname=postgres options=-csearch_path=',                'select proname, prosrc from pg_proc')    AS t1(proname name, prosrc text); SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%'; https://www.postgresql.org/docs/current/contrib-dblink- function.html ---------------------------------------------------------------------- SymbolicFrank · 2023-05-25 11:37 According to some posts on StackOverflow, that was more limited and far slower. The postgres_fdw extension is newer and should make the access seamless. But I'll try it. ---------------------------------------------------------------------- SymbolicFrank · 2023-05-25 13:46 When collecting the resulting view (and specifying all the fields), I still get the same error. It does work in pgAdmin. ---------------------------------------------------------------------- Чебурашка · 2023-05-26 06:44 Quote from: SymbolicFrank on May 25, 2023, 03:46:02 pmWhen collecting the resulting view (and specifying all the fields), I still get the same error. It does work in pgAdmin.  I remember that after some struggling, I ended up using a materialized view in fpc code (= create real table, put there query output, use, drop it). I was lucky because structure of table was defined and static, so I could do that. ---------------------------------------------------------------------- SymbolicFrank · 2023-05-30 08:52 It does work with a table (not a view), once. It's definitely broken, I guess it caches the table structure and assumes that hasn't changed. With a FPC/Lazarus debug build, execution always stops on the first line of TPGHandle.Connect, like there's a breakpoint there. Bah. ---------------------------------------------------------------------- Чебурашка · 2023-05-30 09:00 Quote from: SymbolicFrank on May 30, 2023, 10:52:03 amIt does work with a table (not a view), once. It's definitely broken, I guess it caches the table structure and assumes that hasn't changed. With a FPC/Lazarus debug build, execution always stops on the first line of TPGHandle.Connect, like there's a breakpoint there. Bah. Sorry for that. I was lucky because the structure of my table was not changing. One idea in your case is that you could create the table and drop it each time, but using different name "" + progressive integer for example. But it the postgres access software makes any kind of caching of the structure metadata, you might need to restart the process from time to time to avoid filling up all the memory. ---------------------------------------------------------------------- SymbolicFrank · 2023-05-30 09:40 The thing is, I don't get what happens. In the PQConnection unit, there is this function: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TPQConnection.GetExtendedFieldInfo(cursor: TPQCursor;  Bindings: TFieldBindings); Var  tt,tc,Tn,S : String;  I,J : Integer;  Res : PPGResult;  toid : oid; begins:='';  For I:=0 to Length(Bindings)-1 do    if (Bindings[i].TypeOID>0) then      begin      if (S<>'') then        S:=S+', ';      S:=S+IntToStr(Bindings[i].TypeOID);      end;  if (S='') then    exit;  Res:=Cursor.Handle.Exec(S,False,'Error getting typeinfo');  try    For I:=0 to PQntuples(Res)-1 do      begin      toid:=Strtoint(pqgetvalue(Res,i,0));      tn:=pqgetvalue(Res,i,1);      tt:=pqgetvalue(Res,i,2);      tc:=pqgetvalue(Res,i,3);      J:=length(Bindings)-1;      while (J>= 0) do        begin        if (Bindings[j].TypeOID=toid) then          Case tt of           'e':            Bindings[j].ExtendedFieldType:=eftEnum;           'citext':          Bindings[j].ExtendedFieldType:=eftCitext;          end;        Dec(J);        end;      end;  finally    PQClear(Res);  end;end; According to the code, it is used to convert the TypeOID of a field to the extended type info. The thing is, it executes a query, that consists of a list of numbers, separated by comma's: the TypeOID's. That's it. In this case there's just one: '3910'. So, it doesn't execute something like this: "select extendedtype from infotable where type in (3910);" or such. Just "3910". Which gives an error, of course, as that isn't a valid query. At the top of the unit, there is this definition, to muddy the waters: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   // TField and TFieldDef only support a limited amount of fields.  // TFieldBinding and TExtendedFieldType can be used to map PQ types  // on standard fields and retain mapping info.  TExtendedFieldType = (eftNone,eftEnum,eftCitext);   TFieldBinding = record    FieldDef : TSQLDBFieldDef; // FieldDef this is associated with    Index : Integer; // Tuple index    TypeOID : oid; // Filled with type OID if it is not standard.    TypeName : String; // Filled with type name by GetExtendedFieldInfo    ExtendedFieldType: TExtendedFieldType; //  end; Because that isn't what happens. ---------------------------------------------------------------------- Чебурашка · 2023-05-30 09:56 I did a gitblame on the fpc source and seems that in this commit something got broken: 68c52cf02549c6d527b20f139f9233c823d0815f Would you mind posting an issue to gitlab? ---------------------------------------------------------------------- SymbolicFrank · 2023-05-30 10:22 Thanks, I will. Here. ---------------------------------------------------------------------- SymbolicFrank · 2023-05-30 14:46 Michael has fixed it ---------------------------------------------------------------------- Чебурашка · 2023-05-30 15:16 Michael always fixes it Jokes apart, also the problem of the table dropped and recreated with same name but different structure was there? ---------------------------------------------------------------------- SymbolicFrank · 2023-05-31 06:47 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. ---------------------------------------------------------------------- Чебурашка · 2023-05-31 11:27 Quote from: SymbolicFrank on May 31, 2023, 08:47:20 amOne 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. ---------------------------------------------------------------------- SymbolicFrank · 2023-05-31 13:24 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE OR REPLACE FUNCTION json_hash(json)  RETURNS INTEGER  LANGUAGE SQL  IMMUTABLE  STRICTAS $function$  SELECT CASE f    WHEN 'n' THEN 0    WHEN 't' THEN 1    WHEN 'f' THEN 2    WHEN '"' THEN hashtext(CAST('[' || j || ']' AS json) ->> 0)    WHEN '[' THEN COALESCE((SELECT bit_or(json_hash(e)) FROM json_array_elements($1) AS e), 3)    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)    ELSE hash_numeric(CAST(CAST('[' || j || ']' AS json) ->> 0 AS NUMERIC))  END  FROM (    SELECT TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($1 AS text)) AS j  ) AS jsons,  LATERAL (    SELECT SUBSTRING(j FROM 1 FOR 1) AS f  ) AS firsts$function$; CREATE OR REPLACE FUNCTION json_equals(json, json)  RETURNS BOOLEAN  LANGUAGE SQL  IMMUTABLE  STRICTAS $function$  SELECT CASE f1    WHEN '{' THEN -- object      CASE f2        WHEN '{' THEN (          SELECT COALESCE(bool_and(k1 IS NOT NULL AND k2 IS NOT NULL AND json_equals(v1, v2)), TRUE)          FROM (SELECT DISTINCT ON (k1) * FROM json_each($1) AS j1(k1, v1) ORDER BY k1, ROW_NUMBER() OVER () DESC) AS j1          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        )        ELSE FALSE      END    WHEN '[' THEN -- array      CASE f2        WHEN '[' THEN (          SELECT COALESCE(bool_and(r1 IS NOT NULL AND r2 IS NOT NULL AND json_equals(e1, e2)), TRUE)          FROM (SELECT e1, ROW_NUMBER() OVER () AS r1 FROM json_array_elements($1) AS e1) AS e1        FULL JOIN (SELECT e2, ROW_NUMBER() OVER () AS r2 FROM json_array_elements($2) AS e2) AS e2 ON e1.r1 = e2.r2        )        ELSE FALSE      END    WHEN 'n' THEN -- null      CASE f2        WHEN 'n' THEN TRUE        ELSE FALSE      END    WHEN 't' THEN -- true    CASE f2        WHEN 't' THEN TRUE        ELSE FALSE      END    WHEN 'f' THEN -- false      CASE f2        WHEN 'f' THEN TRUE        ELSE FALSE      END    WHEN '"' THEN -- string      CASE f2        WHEN '"' THEN (CAST('[' || j1 || ']' AS json) ->> 0)                    = (CAST('[' || j2 || ']' AS json) ->> 0)        ELSE FALSE      END    ELSE -- number      CASE f2        WHEN '{' THEN FALSE        WHEN '[' THEN FALSE        WHEN 'n' THEN FALSE        WHEN 't' THEN FALSE      WHEN 'f' THEN FALSE        WHEN '"' THEN FALSE        ELSE CAST(CAST('[' || j1 || ']' AS json) ->> 0 AS NUMERIC)           = CAST(CAST('[' || j2 || ']' AS json) ->> 0 AS NUMERIC)      END  END  FROM (    SELECT TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($1 AS text)) AS j1,           TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($2 AS text)) AS j2  ) AS jsons,  LATERAL (    SELECT SUBSTRING(j1 FROM 1 FOR 1) AS f1,           SUBSTRING(j2 FROM 1 FOR 1) AS f2  ) AS firsts$function$; DROP OPERATOR IF EXISTS = (json, json) CASCADE; CREATE OPERATOR = (  PROCEDURE   = json_equals,  LEFTARG     = json,  RIGHTARG    = json,  COMMUTATOR  = =,  RESTRICT    = eqsel,  JOIN        = eqjoinsel,  HASHES,  MERGES); CREATE OPERATOR CLASS json_ops  DEFAULT  FOR TYPE json  USING hash AS  OPERATOR 1 =,  FUNCTION 1 json_hash(json); It works like a charm ---------------------------------------------------------------------- Чебурашка · 2023-05-31 14:50 Quote from: SymbolicFrank on May 31, 2023, 03:24:04 pmAfter 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i    // ...  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. ---------------------------------------------------------------------- SymbolicFrank · 2023-06-01 08:31 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Execution of query failed  (PostgreSQL: ERROR:  type "earth" does not existCONTEXT:  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.