Horton

Thread

Commits

  1. postgres_fdw: Fix assertion in estimate_path_cost_size().

  2. postgres_fdw: Fix costing of pre-sorted foreign paths with local stats.

  1. BUG #17713: Assert with postgres_fdw in v12

    The Post Office <noreply@postgresql.org> — 2022-12-12T04:12:13Z

    The following bug has been logged on the website:

Bug reference:      17713
Logged by:          Robins Tharakan
Email address:      tharakan@gmail.com
PostgreSQL version: 12.13
Operating system:   Ubuntu 20.04
Description:        

Hi,

This is a repro that triggers an assert (only) in REL_12_STABLE. In
particular the issue seems to have begun after this commit -
08d2d58a2a1c8ef8d39e8132d39ee14a1d029500. The foreign table
is empty so am unsure if Assert()'s expectations are valid.

Since all versions v13+ seem to work okay, it is possible that a future
bug-fix was not backpatched far enough to v12.


Backtrace
=========
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fda51fc9859 in __GI_abort () at abort.c:79
#2  0x00005591731936b6 in ExceptionalCondition (
    conditionName=0x7fda39d454d0 "!(fpinfo->retrieved_rows >= 1)",
    errorType=0x7fda39d45020 "FailedAssertion",
    fileName=0x7fda39d4515b "postgres_fdw.c", lineNumber=2752) at
assert.c:54
#3  0x00007fda39d33e1f in estimate_path_cost_size (root=0x5591745e8580,
    foreignrel=0x559174629660, param_join_conds=0x0, pathkeys=0x0,
    fpextra=0x55917462c870, p_rows=0x7fff6c788538, p_width=0x7fff6c788534,
    p_startup_cost=0x7fff6c788540, p_total_cost=0x7fff6c788548)
    at postgres_fdw.c:2752
#4  0x00007fda39d3b1c3 in add_foreign_final_paths (root=0x5591745e8580,
    input_rel=0x559174629660, final_rel=0x55917462c290,
extra=0x7fff6c788750)
    at postgres_fdw.c:6321
#5  0x00007fda39d3a612 in postgresGetForeignUpperPaths
(root=0x5591745e8580,
    stage=UPPERREL_FINAL, input_rel=0x559174629660,
    output_rel=0x55917462c290, extra=0x7fff6c788750) at
postgres_fdw.c:5883
#6  0x0000559172ee0074 in grouping_planner (root=0x5591745e8580,
    inheritance_update=false, tuple_fraction=136) at planner.c:2412
#7  0x0000559172edd757 in subquery_planner (glob=0x5591745e84e8,
    parse=0x5591746d2890, parent_root=0x0, hasRecursion=false,
    tuple_fraction=0) at planner.c:1012


Full Backtrace excerpt
======================
#2  0x00005591731936b6 in ExceptionalCondition (
    conditionName=0x7fda39d454d0 "!(fpinfo->retrieved_rows >= 1)",
    errorType=0x7fda39d45020 "FailedAssertion",
    fileName=0x7fda39d4515b "postgres_fdw.c", lineNumber=2752) at
assert.c:54
No locals.
#3  0x00007fda39d33e1f in estimate_path_cost_size (root=0x5591745e8580,
    foreignrel=0x559174629660, param_join_conds=0x0, pathkeys=0x0,
    fpextra=0x55917462c870, p_rows=0x7fff6c788538, p_width=0x7fff6c788534,
    p_startup_cost=0x7fff6c788540, p_total_cost=0x7fff6c788548)
    at postgres_fdw.c:2752
        run_cost = 0
        fpinfo = 0x55917462a338
        rows = 3.9525251667299724e-323
        retrieved_rows = 0
        width = 32767
        startup_cost = 6.9532335201825916e-310
        total_cost = 6.4042594576845104e-315



Repro SQL
=========

-- drop database t;
create database t;
\c t
create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10)
unique);
begin;

CREATE EXTENSION 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 'postgres12', password 'postgres12');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback
 OPTIONS (user 'postgres12', password 'postgres12');

create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
  server loopback options (table_name 'loct3', use_remote_estimate
'true');

SELECT FROM (SELECT FROM public.ft3 AS ref_3 WHERE NULL) AS subq_1 WHERE
NULL LIMIT 1;

rollback;

Thanks to SQLSmith / SQLReduce for helping with the find.
-
Robins Tharakan
Amazon Web Services

  2. Re: BUG #17713: Assert with postgres_fdw in v12

    Richard Guo <guofenglinux@gmail.com> — 2022-12-13T03:20:42Z

    On Mon, Dec 12, 2022 at 7:10 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

> This is a repro that triggers an assert (only) in REL_12_STABLE. In
> particular the issue seems to have begun after this commit -
> 08d2d58a2a1c8ef8d39e8132d39ee14a1d029500. The foreign table
> is empty so am unsure if Assert()'s expectations are valid.
>
> Since all versions v13+ seem to work okay, it is possible that a future
> bug-fix was not backpatched far enough to v12.


Yes, I think it is 5e7fa189e, which should be back-patched to v12.

Thanks
Richard

  3. Re: BUG #17713: Assert with postgres_fdw in v12

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2022-12-14T10:02:58Z

    Hi Richard,

On Tue, Dec 13, 2022 at 12:20 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Mon, Dec 12, 2022 at 7:10 PM PG Bug reporting form <noreply@postgresql.org> wrote:
>> This is a repro that triggers an assert (only) in REL_12_STABLE. In
>> particular the issue seems to have begun after this commit -
>> 08d2d58a2a1c8ef8d39e8132d39ee14a1d029500. The foreign table
>> is empty so am unsure if Assert()'s expectations are valid.
>>
>> Since all versions v13+ seem to work okay, it is possible that a future
>> bug-fix was not backpatched far enough to v12.
>
> Yes, I think it is 5e7fa189e, which should be back-patched to v12.

That was also pointed out by Robins:

https://www.postgresql.org/message-id/CAEP4nAza%2B0fTCLkgkKYux3JDo3tUBTQORehP%2BaCxSNURpSFpHw%40mail.gmail.com

Will back-patch to v12 as well.  Thanks for looking at this!

Best regards,
Etsuro Fujita

  4. Re: BUG #17713: Assert with postgres_fdw in v12

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2022-12-15T12:33:31Z

    On Wed, Dec 14, 2022 at 7:02 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Tue, Dec 13, 2022 at 12:20 PM Richard Guo <guofenglinux@gmail.com> wrote:
> > On Mon, Dec 12, 2022 at 7:10 PM PG Bug reporting form <noreply@postgresql.org> wrote:
> >> This is a repro that triggers an assert (only) in REL_12_STABLE. In
> >> particular the issue seems to have begun after this commit -
> >> 08d2d58a2a1c8ef8d39e8132d39ee14a1d029500. The foreign table
> >> is empty so am unsure if Assert()'s expectations are valid.
> >>
> >> Since all versions v13+ seem to work okay, it is possible that a future
> >> bug-fix was not backpatched far enough to v12.
> >
> > Yes, I think it is 5e7fa189e, which should be back-patched to v12.

> Will back-patch to v12 as well.

Done.

Best regards,
Etsuro Fujita

  5. Re: BUG #17713: Assert with postgres_fdw in v12

    Robins Tharakan <tharakan@gmail.com> — 2022-12-19T12:03:44Z

    On Thu, 15 Dec 2022 at 23:02, Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > Will back-patch to v12 as well.
> Done.

Appreciate a quick confirmation / patch.
-
robins