Thread: IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

I've implemented IMPORT FOREIGN SCHEMA support for an fdw called ogr_fdw
against the PostgreSQL 9.5RC1 code base.

Code is here:  https://github.com/robe2/pgsql-ogr-fdw

It works great except in the Case of LIMIT TO  clause  (and possible EXCEPT
though I have to retest EXCEPT to see if there is a case it doesn't work).  
In LIMIT case sometimes works and it sometimes doesn't and when it doesn't
no foreign tables are created.

Key bit starts here:
https://github.com/robe2/pgsql-ogr-fdw/blob/master/ogr_fdw.c#L1389

When it doesn't work, no foreign tables are created, however when I inspect
the out list coming from the function, I see the create foreign table
statements 
And the right number as well but for some reason they never get executed.

I thought perhaps that I have a memory leak somewhere, but then I discovered
that if I take out my logic for LIMIT  and EXCEPT handling,
It still doesn't work.  In the sense that I have a ton of create foreign
table statements but none of them ever get run.

-- ogr_all is a catchall schema just to say - don't bother checking schema.

So I'm guessing there is some other filtering going on that I'm not aware
of.

Here is basic logic I have to exercise this behavior.
I should note: the two laundering options I have in place are used to
preserve or not preserve the table name and column names
When not specified, the import tries to change the table and column names so
that they don't require quoting when used in SQL.

DROP SERVER IF EXISTS northwind  CASCADE;

CREATE SERVER northwind FOREIGN DATA WRAPPER ogr_fdw OPTIONS (   datasource 'C:/fdw_data/northwind.mdb',   format
'ODBC');
 

CREATE SCHEMA IF NOT EXISTS northwind;

-- doesn't work - no tables created, but I see the two create table
statements coming out of my import function
IMPORT FOREIGN SCHEMA ogr_all    LIMIT TO("Orders", "Products")      FROM SERVER northwind INTO northwind
OPTIONS(launder_table_names 'false', launder_column_names 'false');

-- works and I get the 2 tables created
IMPORT FOREIGN SCHEMA ogr_all    LIMIT TO("Orders", "Products")      FROM SERVER northwind INTO northwind;


If I use the EXCEPT clause, it works in both these cases and produces 24
tables (with these two missing) as expected.

I tried exercising the same kind of issue with the postgres_fdw wrapper, but
was not successful replicating the issue with that one.


Thanks,
Regina Obe

"Paragon Corporation" <lr@pcorp.us> writes:
> I've implemented IMPORT FOREIGN SCHEMA support for an fdw called ogr_fdw
> against the PostgreSQL 9.5RC1 code base.
> Code is here:  https://github.com/robe2/pgsql-ogr-fdw
> It works great except in the Case of LIMIT TO  clause  (and possible EXCEPT
> though I have to retest EXCEPT to see if there is a case it doesn't work).  
> In LIMIT case sometimes works and it sometimes doesn't and when it doesn't
> no foreign tables are created.

Case-folding issue, perhaps?  Are you taking care to double-quote the
table names in the generated CREATE FOREIGN TABLE statements?  Because
if you don't, they'll be smashed to lower case and then won't match the
quoted table names in your example.
        regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Thursday, December 31, 2015 3:36 PM
To: Paragon Corporation <lr@pcorp.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table
commands are those further filtered in LIMIT and EXCEPT cases?

"Paragon Corporation" <lr@pcorp.us> writes:
>> I've implemented IMPORT FOREIGN SCHEMA support for an fdw called 
>>  ogr_fdw against the PostgreSQL 9.5RC1 code base.
>> Code is here:  https://github.com/robe2/pgsql-ogr-fdw
>> It works great except in the Case of LIMIT TO  clause  (and possible 
>> EXCEPT though I have to retest EXCEPT to see if there is a case it
doesn't work).
>> In LIMIT case sometimes works and it sometimes doesn't and when it 
>> doesn't no foreign tables are created.

> Case-folding issue, perhaps?  Are you taking care to double-quote the
table names in the generated CREATE FOREIGN TABLE statements?  Because if
you don't, they'll be smashed to lower case and then won't match the quoted
table names in your > example.

>            regards, tom lane


Tom,

Yes using quote identifier etc. 

I just rewrote my logic to go by the resulting table name instead of the
remote data source name and that seems to do the trick.

So I now write:

IMPORT FOREIGN SCHEMA ogr_all    LIMIT TO(orders, products)   FROM SERVER northwind INTO northwind;

But with the laundering options off, I would do this:

IMPORT FOREIGN SCHEMA ogr_all    LIMIT TO("Orders", "Products")   FROM SERVER northwind INTO northwind
OPTIONS(launder_table_names
'false', launder_column_names 'false') ;

So I am guessing you guys are filtering the list so that it works for all
FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT clause?
But not sure where that is being done.

So instead of doing this check in my code (which is what I was doing)

Relname = layer_name    (where in my case layer_name is the remote
datasource which can be pretty crazy for spatial data like web service
layers )

I'm doing this

Relname = table_name 

In the case where laundering is on which is the default  table_name !=
layer_name.  In case where user chooses to preserve layer name then
table_name == layer_name

Thanks,
Regina

"Paragon Corporation" <lr@pcorp.us> writes:
> So I am guessing you guys are filtering the list so that it works for all
> FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT clause?

Yes.  The FDW author is not expected to implement those clauses; where
do you see documentation saying otherwise?
        regards, tom lane

"Paragon Corporation" <lr@pcorp.us> writes:
>> So I am guessing you guys are filtering the list so that it works for 
>> all FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT
clause?

> Yes.  The FDW author is not expected to implement those clauses; where do
you see documentation saying otherwise?
        > regards, tom lane

I didn't. I was copying the postgres_fdw and noticed it implemented them
directly.

Is there an issue with handling it ahead of time?  In the case of ogr_fdw,
there is an extra call after getting list of tables to get the data
structure for a specific table,
So I thought implementing it directly would save some cycles, especially
since some data sources require inspection of the data to infer the
structure so could be very expensive.


Thanks,
Regina

"Paragon Corporation" <lr@pcorp.us> writes:
>> Yes.  The FDW author is not expected to implement those clauses; where do
>> you see documentation saying otherwise?

> I didn't. I was copying the postgres_fdw and noticed it implemented them
> directly.

Ah.  I believe that's mostly just an efficiency hack (and a fairly trivial
one too, at least in postgres_fdw's case).  I suppose there might be an
argument that if we don't exclude tables at this stage, we could get a
parse-time failure from features not supported on the local server, which
would be unexpected if the user had told us to skip such tables.  But that
seems a bit implausible.

I'll go add a comment about this though, to clarify matters for the
next person.
        regards, tom lane