Skip to content

Дублирование записей при многоуровневом наследовании #155

@the20login

Description

@the20login

При использовании партиционирования на таблице-потомке записи в выборке дублируются.

CREATE OR REPLACE FUNCTION inheritance_with_pathman() RETURNS SETOF text
AS $$
BEGIN
  CREATE TABLE records(
    id SERIAL PRIMARY KEY,
    time TIMESTAMPTZ NOT NULL,
    state text
  );

  CREATE TABLE records_open() INHERITS (records);

  CREATE TABLE records_archive() INHERITS (records);

  PERFORM create_range_partitions('public.records_archive', 'time', now(), '1 day'::INTERVAL, 1);

  INSERT INTO records_archive(time, state) VALUES(now(), 'archived');

  RETURN NEXT (select count(*) FROM records_archive);
  RETURN NEXT (select count(*) FROM records);
END $$ LANGUAGE 'plpgsql';
select inheritance_with_pathman();
 inheritance_with_pathman 
----------------------------------------
 1
 2

Однако, если использовать только наследование, без партиционирования, то все нормально.

CREATE OR REPLACE FUNCTION inheritance_without_patman() RETURNS SETOF text
AS $$
BEGIN
  CREATE TABLE records(
    id SERIAL PRIMARY KEY,
    time TIMESTAMPTZ NOT NULL,
    state text
  );

  CREATE TABLE records_open() INHERITS (records);

  CREATE TABLE records_archive() INHERITS (records);
  CREATE TABLE records_archive_old() INHERITS (records_archive);

  INSERT INTO records_archive_old(time, state) VALUES(now(), 'archived');

  RETURN NEXT (select count(*) FROM records_archive);
  RETURN NEXT (select count(*) FROM records);
END $$ LANGUAGE 'plpgsql';
select inheritance_without_patman();
 inheritance_without_patman 
----------------------------------------
 1
 1

Environment

      extname       | extowner | extnamespace | extrelocatable | extversion |    extconfig    | extcondition 
--------------------+----------+--------------+----------------+------------+-----------------+--------------
 plpgsql            |       10 |           11 | f              | 1.0        |                 | 
 pgcrypto           |    16388 |         2200 | t              | 1.2        |                 | 
 pg_stat_statements |    16388 |         2200 | t              | 1.3        |                 | 
 pg_pathman         |    16388 |         2200 | f              | 1.4        | {355267,355278} | {"",""}
 uuid-ossp          |    16388 |         2200 | t              | 1.0        |                 | 

PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit

get_pathman_lib_version 
-------------------------
 10409

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions