Comments (4)
I have had complaints about slow Oracle dictionary queries before, see #584. But you figured that problem out.
In principle, the query in Oracle could be restricted like you suggest. But there is a difficulty that is caused by the fact that Oracle folds identifiers to upper case, while PostgreSQL folds them to lower case. By default, oracle_fdw tries to be smart about converting names (see the case
option to IMPORT FOREIGN SCHEMA
). I cannot think of a good way to translate this to Oracle - or at least in the default case of case = 'smart'
. So I don't restrict the Oracle query and let PostgreSQL filter the translated table names.
from oracle_fdw.
Perhaps concatenating the tables listed in the limit to
clause and passing as a bind variable (tables_filters
) like 'table_a, table_b'
to a query like this could work:
SELECT col.table_name, col.column_name, col.data_type, col.data_type_owner,
col.char_length, col.data_precision, col.data_scale, col.nullable,
CASE WHEN primkey_col.position IS NOT NULL THEN 1 ELSE 0 END AS primary_key
FROM all_tab_columns col,
(SELECT con.table_name, cons_col.column_name, cons_col.position
FROM all_constraints con, all_cons_columns cons_col
WHERE con.owner = cons_col.owner AND con.table_name = cons_col.table_name
AND con.constraint_name = cons_col.constraint_name
AND con.constraint_type = 'P' AND con.owner = :nsp) primkey_col
WHERE col.table_name = primkey_col.table_name(+) AND col.column_name = primkey_col.column_name(+)
AND col.owner = :nsp
AND UPPER(col.table_name) in (SELECT regexp_substr(UPPER(:table_filters), '[^,]+', 1, LEVEL)
FROM dual
CONNECT BY regexp_substr(UPPER(:table_filters), '[^,]+', 1, LEVEL) IS NOT NULL)
ORDER BY col.table_name, col.column_id
from oracle_fdw.
I implemented support for this.
Could you test if the latest code works for you?
from oracle_fdw.
Related Issues (20)
- Segmentation fault when copying CLOB data 1MB or longer HOT 16
- How to use TCPS to connect to Oracle via the FDW HOT 4
- Is there support for Oracle External tables? HOT 10
- Does oracle_fdw support test query to check cached sesssion valid HOT 4
- oracle_fdw on AlmaLinux 8 installation failed HOT 4
- New Feature - Possibility to map column by name, not by position HOT 3
- cannot build 2.6.0 on windows x64 (error LNK2001: unresolved external symbol XactReadOnly) HOT 21
- Error: Could not load library "/usr/lib/postgresql/13/lib/oracle_fdw.so"
- Support for Postgres v16? HOT 3
- Not able to create extension oracle_fdw HOT 2
- Incompatible library - magic block mismatch. Server has NAMEDATALEN=64, library has 256 HOT 5
- Using use_remote_estimate HOT 7
- LIMIT is not pushed down in JOIN query HOT 8
- A questions about the development of Postgres Extension Development. (No related to this project.) HOT 2
- Internal oracle_fdw error: encountered unknown node type 164. HOT 3
- oracle_fdw error when upgrading postgres 11 to postgres 15 on almalinux8 HOT 11
- oracle_fdw-ORACLE_FDW_2_5_0 not able to make HOT 7
- Issue with Installing on Postgres16.1 on same server as Postgres15.5 HOT 4
- Does oracle_fdw support LOAD_BALANCE or FAILOVER HOT 7
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from oracle_fdw.