PostgreSQL: typeinfo error

[SymbolicFrank]

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]

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.

[Чебурашка]

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.

[SymbolicFrank]

Is this repairable by me, or is it easier to look for a low-level solution, what do you think?

[Чебурашка]

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.  

[SymbolicFrank]

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.

[Чебурашка]

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

[SymbolicFrank]

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]

When collecting the resulting view (and specifying all the fields), I still get the same error. It does work in pgAdmin. 

[Чебурашка]

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.