why scan partition table error?

[wonb168]

If query the root partition table need 40s, but if query directly the son table only 10s in greenplum.
So, I postgres_scanner the root table in duckdb, the sql run 40s,
but scan the son table, create view success,

create or replace view biz.rst_ra_skc_org_detail_20221118 as SELECT * FROM postgres_scan('postgres://gpadmin:pwd@192.168.1.2:2345/prod', 'biz', 'rst_ra_skc_org_detail_1_prt_p20221118')

but run the sql, error, :

con.execute("select count(*) from biz.rst_ra_skc_org_detail_20221118").fetch_df()

error:

-- 
drop table if exists tmp_rst_ra_skc_org_detail;
create temp table tmp_rst_ra_skc_org_detail as
select b.operating_unit_sk,a.skc_sk::int as skc_sk
      ,a.human_allot_out_org_sk::int human_allot_out_org_sk,a.human_allot_in_org_sk::int human_allot_in_org_sk
      ,a.out_org_before_ra_include_stock_qty,a.in_org_before_ra_include_stock_qty
      ,a.out_org_after_human_ra_include_stock_qty ,a.in_org_after_human_ra_include_stock_qty
from biz.rst_ra_skc_org_detail_20221118 a
--from (select * from biz.rst_ra_skc_org_detail where day_date = '2022-11-18') a
inner join(select batch_id ,operating_unit_sk from tenant_yuege_biz.gto_action_record) b on a.batch_id::int = b.batch_id
where a.day_date = '2022-11-18' and 
a.ra_source <> '3' and a.is_deleted = '0'

---------------------------------------------------------------------------
IOException                               Traceback (most recent call last)
Cell In [271], line 23
      9 sql=f"""-- 
     10 drop table if exists tmp_rst_ra_skc_org_detail;
     11 create temp table tmp_rst_ra_skc_org_detail as
   (...)
     20 a.ra_source <> '3' and a.is_deleted = '0'
     21 {v_where}"""
     22 print(sql)
---> 23 con.execute(sql)#43.3s rst_ra_skc_org_detail_20221118 rst_ra_skc_org_detail_20221118 

IOException: IO Error: Unable to query Postgres: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
 server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

just count also error:

IOException                               Traceback (most recent call last)
Cell In [272], line 1
----> 1 con.execute("select count(*) from tenant_yuege_biz.rst_ra_skc_org_detail_20221118").fetch_df()

IOException: IO Error: Unable to query Postgres: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
 server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

[hannes]

Please follow the issue template.

[wonb168]

Could you please try to provide some context, and make this issue reproducible?

I dont't know how to reproduce, I create a simple table, it run OK

pgscan(gpstr,'duck','test_partition_list','2017-01-01')
con.execute("select * from duck.test_partition_list_20170101 limit 2").fetch_df()
	id	fdate
0	1	2017-01-01

but, query the comlex table :

pgscan(gpstr,'tenant_yuege_biz','rst_ra_skc_org_detail','2022-11-18')
con.execute("select id from tenant_yuege_biz.rst_ra_skc_org_detail_20221118 limit 2").fetch_df()

error:

---------------------------------------------------------------------------
IOException                               Traceback (most recent call last)
Cell In [13], line 2
      1 pgscan(gpstr,'tenant_yuege_biz','rst_ra_skc_org_detail','2022-11-18')
----> 2 con.execute("select id from tenant_yuege_biz.rst_ra_skc_org_detail_20221118 limit 2").fetch_df()

IOException: IO Error: Unable to query Postgres: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
 server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

but scan the root table, also OK：(Even after the son table error, scan root alse OK)

pgscan(gpstr,'tenant_yuege_biz','rst_ra_skc_org_detail')
con.execute("select * from tenant_yuege_biz.rst_ra_skc_org_detail limit 2").fetch_df()

def pgscan(gpstr,schema,table,part_day=''):
    if part_day=='':
        sql=f"""create or replace view {schema}.{table} as SELECT * FROM postgres_scan('{gpstr}', '{schema}', '{table}')"""
    else:#分区表 rst_ra_skc_org_detail_1_prt_p20221117
        sql=f"""create or replace view {schema}.{table}_{part_day.replace('-','')} as SELECT * FROM postgres_scan('{gpstr}', '{schema}', '{table}_1_prt_p{part_day.replace('-','')}')"""
    print(sql)
    con.execute(sql)