Hello,
ref:
and
I came across this error/crash when using partitioning of a table with foreign tables (parquet_fdw) as partitions.
It might be an issue with parquet_fdw nothing with postgresql, but I am not able to figure out why it crashes around the sorted option set.
to reproduce,
vijay=# CREATE FOREIGN TABLE example1 (
one INT8,
two INT8[],
three TEXT,
four TIMESTAMP,
five DATE,
six BOOL,
seven FLOAT8)
SERVER parquet_srv
OPTIONS (filename '/tmp/parquet_fdw/data/simple/example1.parquet', sorted 'one');
CREATE FOREIGN TABLE example2 (
one INT8,
two INT8[],
three TEXT,
four TIMESTAMP,
five DATE,
six BOOL,
seven FLOAT8)
SERVER parquet_srv
OPTIONS (filename '/tmp/parquet_fdw/data/simple/example2.parquet', sorted 'one');
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
vijay=# select * from example1;
one | two | three | four | five | six | seven
-----+------------+-------+---------------------+------------+-----+-------
1 | {1,2,3} | foo | 2018-01-01 00:00:00 | 2018-01-01 | t | 0.5
2 | {NULL,5,6} | bar | 2018-01-02 00:00:00 | 2018-01-02 | f |
3 | {7,8,9} | baz | 2018-01-03 00:00:00 | 2018-01-03 | t | 1
4 | {10,11,12} | uno | 2018-01-04 00:00:10 | 2018-01-04 | f | 1.5
5 | {13,14,15} | dos | 2018-01-05 00:00:00 | 2018-01-05 | f |
6 | {16,17,18} | tres | 2018-01-06 00:00:00 | 2018-01-06 | f | 2
(6 rows)
vijay=# select * from example2;
one | two | three | four | five | six | seven
-----+---------+-------+---------------------+------------+-----+-------
1 | {19,20} | eins | 2018-01-01 00:00:00 | 2018-01-01 | t |
3 | {21,22} | zwei | 2018-01-03 00:00:00 | 2018-01-03 | f |
5 | {23,24} | drei | 2018-01-05 00:00:00 | 2018-01-05 | t |
7 | {25,26} | vier | 2018-01-07 00:00:00 | 2018-01-07 | f |
9 | {27,28} | fünf | 2018-01-09 00:00:00 | 2018-01-09 | t |
(5 rows)
vijay=# create table example (
vijay(# one INT8,
vijay(# two INT8[],
vijay(# three TEXT,
vijay(# four TIMESTAMP,
vijay(# five DATE,
vijay(# six BOOL,
vijay(# seven FLOAT8)
vijay-# partition by list(three);
CREATE TABLE
vijay=# alter table example attach partition example1 for values in ('foo', 'bar', 'baz', 'uno', 'dos', 'tres');
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
ERROR: could not find pathkey item to sort
vijay=# alter table example1 options (drop sorted);
ALTER TABLE
vijay=# alter table example2 options (drop sorted);
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Foreign Scan on example1 (cost=0.00..0.06 rows=1 width=93) (actual time=0.537..0.912 rows=1 loops=1)
Filter: (three = 'foo'::text)
Rows Removed by Filter: 5
Reader: Single File
Row groups: 1, 2
Planning Time: 0.888 ms
Execution Time: 1.399 ms
one INT8,
two INT8[],
three TEXT,
four TIMESTAMP,
five DATE,
six BOOL,
seven FLOAT8)
SERVER parquet_srv
OPTIONS (filename '/tmp/parquet_fdw/data/simple/example1.parquet', sorted 'one');
CREATE FOREIGN TABLE example2 (
one INT8,
two INT8[],
three TEXT,
four TIMESTAMP,
five DATE,
six BOOL,
seven FLOAT8)
SERVER parquet_srv
OPTIONS (filename '/tmp/parquet_fdw/data/simple/example2.parquet', sorted 'one');
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
vijay=# select * from example1;
one | two | three | four | five | six | seven
-----+------------+-------+---------------------+------------+-----+-------
1 | {1,2,3} | foo | 2018-01-01 00:00:00 | 2018-01-01 | t | 0.5
2 | {NULL,5,6} | bar | 2018-01-02 00:00:00 | 2018-01-02 | f |
3 | {7,8,9} | baz | 2018-01-03 00:00:00 | 2018-01-03 | t | 1
4 | {10,11,12} | uno | 2018-01-04 00:00:10 | 2018-01-04 | f | 1.5
5 | {13,14,15} | dos | 2018-01-05 00:00:00 | 2018-01-05 | f |
6 | {16,17,18} | tres | 2018-01-06 00:00:00 | 2018-01-06 | f | 2
(6 rows)
vijay=# select * from example2;
one | two | three | four | five | six | seven
-----+---------+-------+---------------------+------------+-----+-------
1 | {19,20} | eins | 2018-01-01 00:00:00 | 2018-01-01 | t |
3 | {21,22} | zwei | 2018-01-03 00:00:00 | 2018-01-03 | f |
5 | {23,24} | drei | 2018-01-05 00:00:00 | 2018-01-05 | t |
7 | {25,26} | vier | 2018-01-07 00:00:00 | 2018-01-07 | f |
9 | {27,28} | fünf | 2018-01-09 00:00:00 | 2018-01-09 | t |
(5 rows)
vijay=# create table example (
vijay(# one INT8,
vijay(# two INT8[],
vijay(# three TEXT,
vijay(# four TIMESTAMP,
vijay(# five DATE,
vijay(# six BOOL,
vijay(# seven FLOAT8)
vijay-# partition by list(three);
CREATE TABLE
vijay=# alter table example attach partition example1 for values in ('foo', 'bar', 'baz', 'uno', 'dos', 'tres');
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
ERROR: could not find pathkey item to sort
vijay=# alter table example1 options (drop sorted);
ALTER TABLE
vijay=# alter table example2 options (drop sorted);
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Foreign Scan on example1 (cost=0.00..0.06 rows=1 width=93) (actual time=0.537..0.912 rows=1 loops=1)
Filter: (three = 'foo'::text)
Rows Removed by Filter: 5
Reader: Single File
Row groups: 1, 2
Planning Time: 0.888 ms
Execution Time: 1.399 ms
--
Vijaykumar Jain
Database Engineer
+91 9769545966
vijaykumar.sampat.jain@adjust.com | www.adjust.com
Adjust GmbH
Saarbrücker Str. 37A | 10405 Berlin | Germany
Registration Number: Local Court Berlin-Charlottenburg, HRB 140616 B
Representatives (Geschäftsführer): Simon Dussart
Seat of the company: Berlin, Germany ______________________________________ The protection of your personal data is particularly important to Adjust. To find out more about how Adjust processes such personal data and what your rights are, please see our Privacy Policy.
