Recent

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

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
PostgreSQL: typeinfo error
« on: May 24, 2023, 04:45:23 pm »
If I run this query in pgAdmin, it works:

Code: SQL  [Select][+][-]
  1. SELECT * FROM _schema1.table1 EXCEPT SELECT * FROM _schema2.table1;

But through an TSQLQuery, it gives this error:

Code: Text  [Select][+][-]
  1. Error getting typeinfo  (PostgreSQL: ERROR:  syntax error at or near "3910"
  2. LINE 1: 3910

In function TPGHandle.Exec, from unit PQConnection.

What can be the problem? Does the prepare fail or such?

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: PostgreSQL: typeinfo error
« Reply #1 on: May 25, 2023, 10:20:36 am »
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.
« Last Edit: May 25, 2023, 10:43:46 am by SymbolicFrank »

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #2 on: May 25, 2023, 10:46:02 am »
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.

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 <FROM> 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.
« Last Edit: May 25, 2023, 10:49:28 am 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 #3 on: May 25, 2023, 11:49:47 am »
Is this repairable by me, or is it easier to look for a low-level solution, what do you think?

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #4 on: May 25, 2023, 12:27:19 pm »
Is 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][+][-]
  1.  
  2. create view _schema1.ViewOftable1 (vcolumn1, vcolumn2, ...) as SELECT column1, column2, ... FROM _schema1.table1 EXCEPT SELECT * FROM _schema2.table1;
  3.  
  4.  
« Last Edit: May 25, 2023, 12:39:42 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 #5 on: May 25, 2023, 01:12:15 pm »
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][+][-]
  1.   MyConnection.DatabaseName := 'database2';
  2.   MyConnection.Open;
  3.   MyConnection.ExecuteDirect('drop schema if exists _original cascade;');
  4.   MyConnection.ExecuteDirect('create schema _original;');
  5.   MyConnection.ExecuteDirect('set search_path to _current;');
  6.   MyConnection.ExecuteDirect('create extension if not exists postgres_fdw;');
  7.   MyConnection.ExecuteDirect('create server if not exists orgsrv foreign data wrapper postgres_fdw options (host ''localhost'', dbname ''database1'', port ''5432'');');
  8.   MyConnection.ExecuteDirect('create user mapping if not exists for postgres server orgsrv options (user ''<user>'', password ''<pass>'');');
  9.   MyConnection.ExecuteDirect('import foreign schema _current from server orgsrv into _original;');
  10.  

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.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #6 on: May 25, 2023, 01:32:10 pm »
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][+][-]
  1.   MyConnection.DatabaseName := 'database2';
  2.   MyConnection.Open;
  3.   MyConnection.ExecuteDirect('drop schema if exists _original cascade;');
  4.   MyConnection.ExecuteDirect('create schema _original;');
  5.   MyConnection.ExecuteDirect('set search_path to _current;');
  6.   MyConnection.ExecuteDirect('create extension if not exists postgres_fdw;');
  7.   MyConnection.ExecuteDirect('create server if not exists orgsrv foreign data wrapper postgres_fdw options (host ''localhost'', dbname ''database1'', port ''5432'');');
  8.   MyConnection.ExecuteDirect('create user mapping if not exists for postgres server orgsrv options (user ''<user>'', password ''<pass>'');');
  9.   MyConnection.ExecuteDirect('import foreign schema _current from server orgsrv into _original;');
  10.  

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][+][-]
  1. CREATE VIEW myremote_pg_proc AS
  2.   SELECT *
  3.     FROM dblink('dbname=postgres options=-csearch_path=',
  4.                 'select proname, prosrc from pg_proc')
  5.     AS t1(proname name, prosrc text);
  6.  
  7. SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
  8.  
https://www.postgresql.org/docs/current/contrib-dblink-function.html
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 #7 on: May 25, 2023, 01:37:54 pm »
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

  • Hero Member
  • *****
  • Posts: 1313
Re: PostgreSQL: typeinfo error
« Reply #8 on: May 25, 2023, 03:46:02 pm »
When collecting the resulting view (and specifying all the fields), I still get the same error. It does work in pgAdmin.  :(

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #9 on: May 26, 2023, 08:44:57 am »
When 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.
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 #10 on: May 30, 2023, 10:52:03 am »
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.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #11 on: May 30, 2023, 11:00:00 am »
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.

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 "<base 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.
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 #12 on: May 30, 2023, 11:40:19 am »
The thing is, I don't get what happens. In the PQConnection unit, there is this function:

Code: Pascal  [Select][+][-]
  1. procedure TPQConnection.GetExtendedFieldInfo(cursor: TPQCursor;
  2.   Bindings: TFieldBindings);
  3.  
  4. Var
  5.   tt,tc,Tn,S : String;
  6.   I,J : Integer;
  7.   Res : PPGResult;
  8.   toid : oid;
  9.  
  10. begin
  11. s:='';
  12.   For I:=0 to Length(Bindings)-1 do
  13.     if (Bindings[i].TypeOID>0) then
  14.       begin
  15.       if (S<>'') then
  16.         S:=S+', ';
  17.       S:=S+IntToStr(Bindings[i].TypeOID);
  18.       end;
  19.   if (S='') then
  20.     exit;
  21.   Res:=Cursor.Handle.Exec(S,False,'Error getting typeinfo');
  22.   try
  23.     For I:=0 to PQntuples(Res)-1 do
  24.       begin
  25.       toid:=Strtoint(pqgetvalue(Res,i,0));
  26.       tn:=pqgetvalue(Res,i,1);
  27.       tt:=pqgetvalue(Res,i,2);
  28.       tc:=pqgetvalue(Res,i,3);
  29.       J:=length(Bindings)-1;
  30.       while (J>= 0) do
  31.         begin
  32.         if (Bindings[j].TypeOID=toid) then
  33.           Case tt of
  34.            'e':
  35.             Bindings[j].ExtendedFieldType:=eftEnum;
  36.            'citext':
  37.             Bindings[j].ExtendedFieldType:=eftCitext;
  38.           end;
  39.         Dec(J);
  40.         end;
  41.       end;
  42.   finally
  43.     PQClear(Res);
  44.   end;
  45. 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][+][-]
  1.   // TField and TFieldDef only support a limited amount of fields.
  2.   // TFieldBinding and TExtendedFieldType can be used to map PQ types
  3.   // on standard fields and retain mapping info.
  4.   TExtendedFieldType = (eftNone,eftEnum,eftCitext);
  5.  
  6.   TFieldBinding = record
  7.     FieldDef : TSQLDBFieldDef; // FieldDef this is associated with
  8.     Index : Integer; // Tuple index
  9.     TypeOID : oid; // Filled with type OID if it is not standard.
  10.     TypeName : String; // Filled with type name by GetExtendedFieldInfo
  11.     ExtendedFieldType: TExtendedFieldType; //
  12.   end;

Because that isn't what happens.
« Last Edit: May 30, 2023, 11:50:37 am by SymbolicFrank »

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: PostgreSQL: typeinfo error
« Reply #13 on: May 30, 2023, 11:56:19 am »
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?
« Last Edit: May 30, 2023, 11:57:50 am 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 #14 on: May 30, 2023, 12:22:33 pm »
Thanks, I will.

Here.
« Last Edit: May 30, 2023, 12:40:07 pm by SymbolicFrank »

 

TinyPortal © 2005-2018