Skip to content

ERROR: could not find pathkey item to sort #237

New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Open
longtian001 opened this issue Nov 12, 2021 · 1 comment
Open

ERROR: could not find pathkey item to sort #237

longtian001 opened this issue Nov 12, 2021 · 1 comment

Comments

@longtian001
Copy link

longtian001 commented Nov 12, 2021

Problem description

1.parent table(90 columns)

create table test_t1(
id_hbs_sale_order varchar(36) not null default uuid_generate_v4(),
......
pcode varchar(40),
ccode varchar(3),
status varchar(10),
created_by varchar(150) not null default 'system',
created_date timestamp(0) with time zone not null default now(),
updated_by varchar(150) not null default 'system',
updated_date timestamp(0) with time zone default now()
);
2. create range partition tables every 7 days
select create_range_partitions(
'test_t1'::regclass,
'created_date',
'2020-07-01 00:00:00'::timestamp(0) with time zone,
interval '7 days',
72,
false
);

insert some data

  1. execute prepare sql
    prepare test_prepare(
    timestamp,
    timestamp,
    varchar(40),
    varchar(40),
    varchar(3),
    varchar(3),
    varchar(10))
    as
    select *
    from test_t1
    where
    created_date between $1 and $2
    and pcode in ($3,$4)
    and ccode in ($5,$6)
    and status !=$7
    order by updated_date desc
    limit 20 offset 0;

execute test_prepare(
'2020-07-08 00:00:00','2020-07-17 00:00:00',
'102345','10023','3','m','13'
);

error info
ERROR: could not find pathkey item to sort

Environment

OS: RHEL 7.4 X86 64
DB: postgresql 10.11 source install
plugin: pg_pathman 1.4.12 (try to update 1.5.11, but the problem still exists)

@kovdb75
Copy link
Collaborator

kovdb75 commented Jan 14, 2022

Unfortunately I don't have RHEL.
I tried to repeat a problem on current branch REL_10_STABLE (PostgreSQL 10.19) under Windows 10 Pro (21H2 19044.1466) and under Ubuntu 20.04.3 LTS:

  1. PostgreSQL source code:
git clone git://git.postgresql.org/git/postgresql.git
git checkout REL_10_STABLE
  1. Plugin pg_pathman source code:
git clone https://github.com/postgrespro/pg_pathman
  1. After compilation (PostgreSQL + pg_pathman) I created new database, put "shared_preload_libraries='pg_variables'" into postgresql.conf and executed script:
CREATE EXTENSION pg_pathman;
CREATE EXTENSION pgcrypto; 

SELECT version();
SELECT pathman_version();

create table test_t1(
id_hbs_sale_order varchar(36) not null default gen_random_uuid(), 
pcode varchar(40), 
ccode varchar(3), 
status varchar(10), 
created_by varchar(150) not null default 'system', 
created_date timestamp(0) with time zone not null default now(), 
updated_by varchar(150) not null default 'system', 
updated_date timestamp(0) with time zone default now()
);

select create_range_partitions(
'test_t1'::regclass,
'created_date',
'2020-07-01 00:00:00'::timestamp(0) with time zone,
interval '7 days',
72,
false
);

prepare test_prepare(
timestamp,
timestamp,
varchar(40),
varchar(40),
varchar(3),
varchar(3),
varchar(10))
as
select *
from test_t1
where
created_date between $1 and $2
and pcode in ($3,$4)
and ccode in ($5,$6)
and status !=$7
order by updated_date desc
limit 20 offset 0;

execute test_prepare(
'2020-07-08 00:00:00','2020-07-17 00:00:00',
'102345','10023','3','m','13'
);

DEALLOCATE test_prepare;
DROP TABLE test_t1 CASCADE;
DROP EXTENSION pgcrypto; 
DROP EXTENSION pg_pathman;
  1. Result: no errors for Windows (for Ubuntu - similar result):
CREATE EXTENSION
CREATE EXTENSION
                           version                           
-------------------------------------------------------------
 PostgreSQL 10.19, compiled by Visual C++ build 1929, 64-bit
(1 row)

 pathman_version 
-----------------
 1.5.12
(1 row)

CREATE TABLE
 create_range_partitions 
-------------------------
                      72
(1 row)

PREPARE
 id_hbs_sale_order | pcode | ccode | status | created_by | created_date | updated_by | updated_date 
-------------------+-------+-------+--------+------------+--------------+------------+--------------
(0 rows)

DEALLOCATE
DROP TABLE
DROP EXTENSION
DROP EXTENSION
  1. Need an example that generates an error ...

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants