Skip to content
New issue

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

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

Already on GitHub? Sign in to your account

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

Open
snaiffer opened this issue Jan 13, 2020 · 0 comments
Open

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

snaiffer opened this issue Jan 13, 2020 · 0 comments

Comments

@snaiffer
Copy link

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

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

No branches or pull requests

1 participant