pg_pathman
pg_pathman copied to clipboard
PL/pgSQL scans all partitions at first time per each new session
Problem description
PL/pgSQL scans all partitions at first time per each new session.
prepare enviroment
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series('2015-01-01'::date, '2015-01-05'::date, '1 hour') as g;
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval);
Tests
Make ACCESS EXCLUSIVE for one table in separate transaction:
begin;
lock table journal_2 in ACCESS EXCLUSIVE mode;
SQL-request is good:
SELECT * FROM journal WHERE dt >= '2015-01-05';
"Append (cost=0.00..20.70 rows=1070 width=49)" " -> Seq Scan on journal_5 (cost=0.00..20.70 rows=1070 width=48)"
PL/pgSQL request is strange:
create or replace function test()
returns void
language plpgsql
as $function$
begin
perform * FROM journal WHERE dt >= '2015-01-05';
end;
$function$;
select test();
It'll be wait until the lock for journal_2 is released Note: if release the lock and lock again, then execute function test() again in the same session, then everything will be good. The request won't be wait the lock release.
Environment
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------------+----------+--------------+----------------+------------+---------------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
pg_pathman | 10 | 2200 | f | 1.5 | {58332,58343} | {"",""}
<!-- Put the result of (SELECT version()) below -->
version
--------------------------------------------------------------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit
<!-- For Postgres Pro: put the result of (SELECT pgpro_version()) below -->
pgpro_version
-----------------------------------------------------------------------------------------------------
PostgresPro 9.6.11.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit
<!-- For Postgres Pro: put the result of (SELECT pgpro_edition()) below -->
pgpro_edition
---------------
standard
<!-- SELECT pathman_version() -->
pathman_version
-----------------
1.5.10