Devart Forums

Posted: **Fri 14 Oct 2011 12:30**

Hello,

I'm trying to reverse engineer a database to get foreign key infos. I have a SQL query which works fine in pgAdmin:

Code: Select all

SELECT conrelid::regclass as "table",
               a.attname as columns,
               confrelid::regclass as "foreign table",
               af.attname as "foreign columns",
               conrelid
          FROM pg_attribute AS af,
               pg_attribute AS a,
               ( SELECT conrelid,
                        confrelid,
                        conkey[i] AS conkey,
                        confkey[i] as confkey
                   FROM ( SELECT conrelid,
                                 confrelid,
                                 conkey,
                                 confkey,
                                 generate_series(1, array_upper(conkey, 1)) AS i
                            FROM pg_constraint
             WHERE contype = 'f'
                 ) AS ss
               ) AS ss2
         WHERE af.attnum = confkey
           AND af.attrelid = confrelid
           AND a.attnum = conkey
           AND a.attrelid = conrelid
           AND conrelid::regclass = 'table_name'::regclass
           AND a.attname = 'field_name';

However this query returns an error when ran through a TUniQuery. The error was "active transaction is required for operations on large objects". I then wrapped my UniQuery as such:

Code: Select all

myQuery := TUniQuery.Create(nil);
myQuery.Connection := DatabaseConnection;
myQuery.SQL.Text := SQLQuery;

DatabaseConnection.StartTransaction;
myQuery.Execute;
[...]
myQuery.Free;
DatabaseConnection.Commit;

But then the first query is successful, but the second always fail with an error like "large object xxx does not exists". Am I missing something terribly obvious?

Posted: **Mon 17 Oct 2011 11:33**

Hello,

Hello,

This error is connected with the fact that the "conrelid" field contains a link to an object that does not exist.
To resolve the problem, you should set the OIDAsInt specific option to True like:

Code: Select all

  myQuery.SpecificOptions.Values['OIDAsInt']:='true';

Posted: **Mon 17 Oct 2011 13:46**

Hello,

I'm not really sure why there is a reference to an object which does not exist, but thanks for the clarification and pointing out the specific option, it works correctly now.

Posted: **Tue 18 Oct 2011 10:40**

Hello,

Fields with the OID type are for storing integer values that are pointers to database objects. So if you see a filed with the OID type and the OIDAsInt property is set to false, we are trying to receive an object by this link, and, if this object does not exist, you get the corresponding error message.

Devart Forums

Postgres: Large object xxx does not exists

Postgres: Large object xxx does not exists