From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: onderkalaci@gmail.com
Subject: BUG #18467: postgres_fdw (deparser) ignores LimitOption
Date: Wed, 15 May 2024 07:03:12 +0000
Message-ID: <18467-7bb89084ff03a08d@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18467
Logged by: Onder Kalacı
Email address: onderkalaci@gmail.com
PostgreSQL version: 16.2
Operating system: MacOs
Description:
Hi, it seems the same query with `LimitOption` on a heap table and on an
postgres_fdw table pointing to the same heap table is returning different
results.
Steps to reproduce:
-- create heap table, and insert 2 rows
CREATE TABLE heap_table (a int);
INSERT INTO heap_table VALUES (1), (1), (1);
-- create a foreign table, pointing to the same heap_table
CREATE FOREIGN TABLE ft1 (
a int
) SERVER loopback OPTIONS (table_name 'heap_table');
-- same query returning different results
SELECT * FROM heap_table ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES ;
a
---
1
1
1
(3 rows)
SELECT * FROM ft1 ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES ;
a
---
1
1
(2 rows)
-- seems like the deparser doesn't properly handle LimitOption
explain (verbose) SELECT * FROM ft1 ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES
;
QUERY PLAN
-----------------------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.00..100.07 rows=2 width=4)
Output: a
Remote SQL: SELECT a FROM public.heap_table ORDER BY a ASC NULLS LAST
LIMIT 2::bigint
(3 rows)
fdw-setup steps I used:
-- setup
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1 OPTIONS (user 'value',
password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
Thanks,
Onder
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, onderkalaci@gmail.com
Subject: Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption
In-Reply-To: <18467-7bb89084ff03a08d@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox