Skip to content

PL/pgSQL scans all partitions at first time per each new session #210

Open
@snaiffer

Description

@snaiffer

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions