Option 'query' has 500 character limit

[optik-aper]

This may be a result of Postgres settings but I have not yet found any documentation that refers to this area of the foreign tables. The gist is, through psql, is I can create foreign tables with a query option string however long I would like it to be. Then, when viewing the options in the properties of the new foreign table (in pgAdmin), the query value is obviously cut off mid-statement and the select throws a syntax error back from SQL Server. Doing some testing reveals that there appears to be a hard limit of 500 characters on the value field. Is this an issue with the data wrapper? Is this a limit with Postgres?

[GeoffMontee]

Interesting. Thanks for the report.

The question is: when is the query getting truncated?

Can you please perform the following query in the PostgreSQL database containing your foreign table?

SELECT * FROM pg_catalog,pg_foreign_table;

[optik-aper]

I believe it is at or around 500 characters in the query string that it cuts off. Browsing the source doesn't show anywhere that is programmatically setting this limit, but I wonder if it's a result of something in the C functions (I don't program in C).

I tried to query pg_catalog, but it didn't like that: ERROR: relation "pg_catalog" does not exist

The output for the foreign table in question is:

"ftrelid"|"ftserver"|"ftoptions"

40186|40175|"{database=SPDaxGold,""query=declare @CompanyParameter varchar(3) = 'sp' ,@itemList varchar(max) = '',@saleableItemGroups varchar(1000),@excludeItemGroups varchar(1000) ,@customerGroup varchar(1000),@forecastMethod varchar(60) ,@forecastCalendar varchar(60),@forecastDiscontinued int
set @customerGroup = (select customerGroup from wilson.frePPLe.dbo.dataParameters) set @saleableItemGroups = (select saleableItemGroups from wilson.frePPLe.dbo.dataParameters) set @excludeItemGroups = (select excludeItemGroups from wilson.frePPLe.dbo.dataParameters) set @forecastMethod = (select forecastMethod from wilson.frePPLe.dbo.dataParameters) @forecastDiscontinued = (select forecastDiscontinued from wilson.frePPLe.dbo.dataParameters) set @forecastCalendar = (select forecastCalendar from wilson.frePPLe.dbo.dataParameters)   
select i.ITEMID + isnull(cl.INVENTCOLORID,'') + ' - ' + c.Value as [name] ,i.ITEMID + isnull(cl.INVENTCOLORID,'') as [item] ,i.ITEMNAME ,c.value as [customer]
    ,@forecastCalendar as [calendar]
    ,@forecastMethod as [foecast_method]
    ,'' as [description]
    ,'' as [category]
    ,'' as [subcategory]
    ,10 as [priority]
    ,'TRUE' as [discrete]
    ,'TRUE' as [planned]

    ,'' as [deliveryOperation]
    ,'' as [minimumShip]
    ,'' as [maxLate]
    ,'DAX' as [source]
    ,convert(varchar(27),GETDATE(),126) as [last modified]  /* format code 120 will remove the T if needed */

from 
    INVENTTABLE as i

    inner join
    INVENTITEMSALESSETUP as ss
    on i.ITEMID = ss.ITEMID
    and i.DATAAREAID = ss.DATAAREAID
    and ss.INVENTDIMID = 'AllBlank'

    left outer join
    INVENTCOLOR as cl
    on i.ITEMID = cl.ITEMID
    and i.DATAAREAID = cl.DATAAREAID
    and cl.DFSTOPPEDSALES = 0

    cross join
    (select * from dbo.Split(@customerGroup,',')) as c

where
    i.DATAAREAID = @CompanyParameter

    and i.ITEMGROUPID in (select * from dbo.Split(@saleableItemGroups,','))
    and i.ITEMGROUPID not in (select * from dbo.Split(@excludeItemGroups,','))

    and case    
            when @forecastDiscontinued = 0 then i.JS_DISCONTINUED
            else @forecastDiscontinued
            end = @forecastDiscontinued

    and ss.STOPPED = 0
    and i.JSKITENABLED = 0
    ""}"

When retrieved that way the entire query is present. When retrieved from the properties of the table, under the options field 'query' for the same table, it appears like this:

declare 
    @CompanyParameter varchar(3) = "sp" ,
    @itemList varchar(max) = "",
    @saleableItemGroups varchar(1000),
    @excludeItemGroups varchar(1000) ,
    @customerGroup varchar(1000),
    @forecastMethod varchar(60) ,
    @forecastCalendar varchar(60),
    @forecastDiscontinued int

set @customerGroup = (select customerGroup from wilson.frePPLe.dbo.dataParameters) 
set @saleableItemGroups = (select saleableItemGroups from wilson.frePPLe.dbo.dataParameters) 
set @excludeItemGroups = (select e

That is all. It is cut off beyond that. When I try to run a select on the foreign table I get this message:

NOTICE: DB-Library notice: Msg #: 102, Msg state: 1, Msg: Incorrect syntax near 'e'., Server: WILSON, Process: , Line: 16, Level: 15

Which makes sense because it is not a complete statement.

[GeoffMontee]

I tried to query pg_catalog, but it didn't like that:

Sorry about that. It didn't like that because I had a typo in my query. The comma (,) should have been a dot (.):

SELECT * FROM pg_catalog.pg_foreign_table;

pg_catalog is in search_path by default, so it looks like you were able to get the contents of pg_foreign_table anyway.

The output for the foreign table in question is:

So the query field contains the entire query if you do SELECT * FROM pg_foreign_table;?

When retrieved that way the entire query is present. When retrieved from the properties of the table, under the options field 'query' for the same table, it appears like this:

When you say "when retrieved from the properties of the table, under the options field..." you are referring to some pgAdmin III GUI?

[optik-aper]

So the query field contains the entire query if you do SELECT * FROM pg_foreign_table;?

That's correct. I can't test it right now but I do remember seeing the query in its entirety when I originally created the foreign table through the psql console.

When you say "when retrieved from the properties of the table, under the options field..." you are referring to some pgAdmin III GUI?

Yes, sorry. I should have made that clear. Looking at the foreign table properties through pgAdmin III shows the truncated version of the query. That said, even when I try to select from the foreign table in the psql console--where it showed the full query on creating--it still fails to return any data.

[GeoffMontee]

Does the full query show up if you use the \det command in psql via the command-line? psql man page

[optik-aper]

To sanity check myself I dropped the foreign table and recreated it through psql. When I run \det+ I see the entire query. When I select from the foreign table in psql, no rows are returned and no SQL errors are returned. When I run SELECT * FROM pg_catalog.pg_foreign_table; I see the entire query. When I open the table in pgAdmin III, go to the Options tab in Properties, I see the truncated version of the query.

In this iteration of the CREATE FOREIGN TABLE ... attempt, the query truncation occurs at a point that will not cause TSQL syntax errors so, instead, it just returns no rows since the script appears to only declare values, never selecting anything.

[GeoffMontee]

To sanity check myself I dropped the foreign table and recreated it through psql. When I run \det+ I see the entire query. When I select from the foreign table in psql, no rows are returned and no SQL errors are returned. When I run SELECT * FROM pg_catalog.pg_foreign_table; I see the entire query.

Hmm, interesting. I'm glad it wasn't truncated when using psql.

When I open the table in pgAdmin III, go to the Options tab in Properties, I see the truncated version of the query.

Maybe pgAdmin has some length limitation on some of its windows. If you think you've discovered a bug, you might want to bring it up on their mailing list.

In this iteration of the CREATE FOREIGN TABLE ... attempt, the query truncation occurs at a point that will not cause TSQL syntax errors so, instead, it just returns no rows since the script appears to only declare values, never selecting anything.

If you are curious and you want to see how tds_fdw executes queries and fetches results (and you don't mind looking at C), the relevant function is tdsIterateForeignScan.

I believe there may be multiple reasons for why your query isn't working:

• tds_fdw assumes the query parameter is a single statement with a single set of results. If you look at the C code, dbcmd, dbsqlexec, and dbresults are all only executed once. Your query is actually multiple statements. i.e.: a declare statement, followed by multiple set statements.

Even if all statements get executed (i'm not sure how FreeTDS would handle it), I think tds_fdw will only fetch the result set for the first statement.

Do you know if the entire query was executed on the MS SQL side?

• tds_fdw assumes the results can be fetched with dbnextrow. It might be that this only covers SELECT statements and maybe stored procedures. I'm not entirely sure.

Could you refactor this somehow? Maybe create a view or stored procedure on the MS SQL side that would present the data how you want, and then create a foreign table to select from that?

[optik-aper]

Maybe pgAdmin has some length limitation on some of its windows. If you think you've discovered a bug, you might want to bring it up on their mailing list.

That's a good point. It could be that the entire query is actually there, but the field is unable to display it. I may bring it up on that mailing list.

tds_fdw assumes the query parameter is a single statement with a single set of results. If you look at the C code, dbcmd, dbsqlexec, and dbresults are all only executed once. Your query is actually multiple statements. i.e.: a declare statement, followed by multiple set statements.

Good to know. I didn't follow the code closely enough to notice that. In retrospect it makes sense to me that it behaves this way and it seems idiomatic of Postgres to only return the results from one query--which is a shift from what I am used to in MSSQL. I'm still learning.

Do you know if the entire query was executed on the MS SQL side?

I do not, but I may try to run a trace and see exactly what is being passed.

Could you refactor this somehow? Maybe create a view or stored procedure on the MS SQL side that would present the data how you want, and then create a foreign table to select from that?

Yeah, that's what I ended up doing after all. It was really good to see that I could create a foreign table just as easily from a view as from a table. I was inquiring about the query option out of curiosity and hoping to shed some light on it in case it was not performing as it was expected. When I get some time I will take a look at the function and see what I can learn from how it works.

All in all it's probably a fringe use case but I thought it was interesting. Thank you for all your help troubleshooting my issue! For what it's worth, I was over and over again impressed with how gracefully this FDW handled connecting and transferring data. I expected that it would take a lot of configuration and tweaking, but I went down the documentation and it worked "out-of-the-box," as it were, and that really astounded me. Thanks for maintaining such a solid and useful product!

[GeoffMontee]

Thank you for all your help troubleshooting my issue!

No problem! Feel free to ask for help again if you have any other questions or issues.

For what it's worth, I was over and over again impressed with how gracefully this FDW handled connecting and transferring data. I expected that it would take a lot of configuration and tweaking, but I went down the documentation and it worked "out-of-the-box," as it were, and that really astounded me. Thanks for maintaining such a solid and useful product!

Thanks! I'm glad you are pleased with it.