Import schema and timestamp(-78) about oracle_fdw HOT 7 CLOSED

Now that is strange.

The (internal) table SYS.XDS_ACE is defined differently in different Oracle versions, but it does not look like what you see in the ones I have at hand.

In Oracle 10.2:

SQL> SELECT data_length, data_precision, data_scale
  2  FROM all_tab_columns
  3  WHERE owner='SYS' and table_name='XDS_ACE' AND column_name='START_DATE';

no rows selected

In Oracle 11.2:

SQL> SELECT data_length, data_precision, data_scale
  2  FROM all_tab_columns
  3  WHERE owner='SYS' and table_name='XDS_ACE' AND column_name='START_DATE';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         13                         9

In Oracle 12.1:

SQL> SELECT data_length, data_precision, data_scale
  2  FROM all_tab_columns
  3  WHERE owner='SYS' and table_name='XDS_ACE' AND column_name='START_DATE';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
       4000                         0

What is your Oracle server version?

And LIMIT TO and EXCEPT work fine here.
I wonder what is going on in your installation.

Can you tell me the result of SELECT oracle_diag('oracle')?

from oracle_fdw.

SELECT` oracle_diag('oracle');
                                       oracle_diag 
----------------------------------------------------------------------------------------
  oracle_fdw 1.3.0, PostgreSQL 9.5.0, Oracle client 11.2.0.4.0, Oracle 
server 11.2.0.1.0
(1 row)

from oracle_fdw.

Could it be that there is a bug or data corruption in your Oracle database?
I tested on 11.2.0.4, but that shouldn't make much of a difference.

Can you try

ALTER VIEW SYS.XDS_ACE COMPILE;

and see if that makes a difference?

How did you test that LIMIT TO and EXCEPT does not work?
I tried with schema SYS like you did and didn't notice anything odd.

Can you maybe attach a debugger and see what happens in IsImportableForeignTable() in src/backend/foreign/foreign.c?

from oracle_fdw.

On 2/17/16 4:26 AM, Laurenz Albe wrote:

Can you try

|ALTER VIEW SYS.XDS_ACE COMPILE; |

and see if that makes a difference?

Unfortunately I don't have the required perms. I've asked my client to
try this.

How did you test that |LIMIT TO| and |EXCEPT| does not work?
I tried with schema |SYS| like you did and didn't notice anything odd.

Can you maybe attach a debugger and see what happens in
|IsImportableForeignTable()| in |src/backend/foreign/foreign.c|?

Turns out the problem here is with how things are coded. The FDW creates
a monster set of CREATE FOREIGN TABLE commands, which get parsed
inside ImportForeignSchema(), before even attempting to see whether we
should be importing the table or not. Since we're getting bogus data for
xds_ace, the parsing fails. That could maybe be fixed, but I suspect

it's not worth it.

Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

from oracle_fdw.

oracle_fdw uses the default of not pushing down LIMIT TO and EXCEPT.
I have considered it, but it would be a challenge because I'd have to take case translation into account.

So what remains to be seen to is SYS.XDS_ACE.
I'll wait for what your client says.

from oracle_fdw.

from oracle_fdw.

Strange, but good that it is solved!

from oracle_fdw.