Feature Request: `LIMIT` and `COUNT` pushdown

[cobolbaby]

Issue report

The following information is very important in order to help us to help you. Omission of the following details cause delays or could receive no attention at all.

Operating system

On recent GNU/Linux distributions, you can provide the content of the file /etc/os-release

PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
NAME="Debian GNU/Linux"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Version of tds_fdw

From a psql session, paste the outputs of running \dx

If you built the package from Git sources, also paste the output of running git log --source -n 1 on your git clone from a console

2.0.3

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

PostgreSQL 12.15 (Debian 12.15-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Version of FreeTDS

How to get it will depend on your Operating System and how you installes FreeTDS

From a console:

• On RPM based systems: rpm -qa|grep freetds
• On Deb based systems: dpkg -l|grep freetds
• _If you built your own binaries from source code, then go to the sources, and run: grep 'AC_INIT' configure.ac_

Replace this with the output

Logs

Please capture the logs when the error you are reporting is happening, as well as commands with their outputs if you are reporting a problem build or installing

_For problems using tdsfdw on PostgreSQL how to do it will depend on your system, but if your PostgreSQL is installed on GNU/Linux, you will want to use tail -f with the log of the PostgreSQL cluster

For MSSQL you will need to use the SQL Server Audit Log

Replace this with the commands and outputs

Sentences, data structures, data

This will depend on the exact problem you are having and data privacy restrictions

However the more data you provide, the more likely we will be able to help

As a bare minimum, you should provide

• The SQL sentence that is failing
• The data structure on the PostgreSQL side and on the MSSQL side

set client_min_messages = 'DEBUG3';

explain analyse verbose
select * from public."Employee" limit 1;

/*
"Limit  (cost=200.00..299.98 rows=1 width=100) (actual time=4.691..4.692 rows=1 loops=1)"
"  Output: ""BadgeID"", ""BadgeName"", ""DepID"", ""JobGrade"", ""JobName"", ""CardID"", ""BeginDate"", ""ReportTo"", ""EMail"", ""LastDate"", ""JoinDate"", ""Company"""
"  ->  Foreign Scan on public.""Employee""  (cost=200.00..327032.69 rows=3269 width=100) (actual time=4.689..4.689 rows=1 loops=1)"
"        Output: ""BadgeID"", ""BadgeName"", ""DepID"", ""JobGrade"", ""JobName"", ""CardID"", ""BeginDate"", ""ReportTo"", ""EMail"", ""LastDate"", ""JoinDate"", ""Company"""
"        Remote query: SELECT [BadgeID], [BadgeName], [DepID], [JobGrade], [JobName], [CardID], [BeginDate], [ReportTo], [EMail], [LastDate], [JoinDate], [Company] FROM FISDATA.[dbo].[Employee]"
"Planning Time: 22.060 ms"
"Execution Time: 11.332 ms"
*/

explain analyse verbose
select count(1) from public."Employee";

/*
"Aggregate  (cost=327040.86..327040.87 rows=1 width=8) (actual time=31.974..31.975 rows=1 loops=1)"
"  Output: count(1)"
"  ->  Foreign Scan on public.""Employee""  (cost=200.00..327032.69 rows=3269 width=100) (actual time=1.713..31.496 rows=3269 loops=1)"
"        Output: ""BadgeID"", ""BadgeName"", ""DepID"", ""JobGrade"", ""JobName"", ""CardID"", ""BeginDate"", ""ReportTo"", ""EMail"", ""LastDate"", ""JoinDate"", ""Company"""
"        Remote query: SELECT NULL FROM FISDATA.[dbo].[Employee]"
"Planning Time: 13.148 ms"
"Execution Time: 39.372 ms"
*/

[GeoffMontee]

Thanks for the report!

[cobolbaby]

I wanna know who confirms and executes the development plan for tds_fdw? @GeoffMontee

[GeoffMontee]

Hi @cobolbaby,

I am the developer of tds_fdw, so that would be me.

[gavinwahl]

@GeoffMontee I am interested in working on this. Do you have any pointers?