Age of root partition table is always 2147483647 ( 2^31-1 ) in Greenplum 6.x
Article ID: 297038
Updated On:
Products
VMware Tanzu Greenplum
Issue/Introduction
Unlike Greenplum 4.x and 5.x, the age of root partition table is always 2147483647 (2^31-1) after creating it in Greenplum 6.x.
1. Check which Pivotal Greenplum version you are running:
2. Create the partition tables with an DDL file.
3. Check the table list if those are created.
4. Check the age of the partition tables:
Even after running VACUUM FREEZE or FULL on all partition tables, the age of root partition table never decreased.
1. Check which Pivotal Greenplum version you are running:
[gpadmin@mdw6 ~]$ psql
psql (9.4.24)
Type "help" for help.
gpadmin=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
PostgreSQL 9.4.24 (Greenplum Database 6.1.0 build commit:6788ca8c13b2bd6e8976ccffea07313cbab30560) on x86_64-unknown-linux-gnu, compiled by gcc (GCC
) 6.4.0, 64-bit compiled on Nov 1 2019 22:06:07
(1 row)
gpadmin=# \q
2. Create the partition tables with an DDL file.
[gpadmin@mdw6 ~]$ vi public.test_date.sql
SET gp_default_storage_options = '';
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UHC';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE public.test_date (
log_date timestamp without time zone NOT NULL,
test_id numeric,
count numeric DEFAULT 0
) DISTRIBUTED BY (test_id) PARTITION BY RANGE(log_date)
(
PARTITION p2008 START ('2008-01-01 00:00:00'::timestamp without time zone) END ('2009-01-01 00:00:00'::timestamp without time zone) WITH (tablename='test_date_1_prt_p2008', appendonly='false'),
PARTITION p2009 START ('2009-01-01 00:00:00'::timestamp without time zone) END ('2010-01-01 00:00:00'::timestamp without time zone) WITH (tablename='test_date_1_prt_p2009', appendonly='false'),
PARTITION p2010 START ('2010-01-01 00:00:00'::timestamp without time zone) END ('2011-01-01 00:00:00'::timestamp without time zone) WITH (tablename='test_date_1_prt_p2010', appendonly='false'),
DEFAULT PARTITION pother WITH (tablename='test_date_1_prt_pother', appendonly='false')
);
ALTER TABLE public.test_date OWNER TO gpadmin;
COPY public.test_date (log_date, test_id, count) FROM stdin;
2019-11-20 11:10:00 111111111111111111111111111111111111111111111111111111111111111111111111111 1
2019-11-20 11:10:00 1111111111111111111111111111111111111111111111111111111111111111111111111112222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222 2
2019-11-20 11:10:00 111111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222233333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333 3
\.
[gpadmin@mdw6 ~]$ psql -f public.test_date.sql
3. Check the table list if those are created.
gpadmin=# \dt;
List of relations
Schema | Name | Type | Owner | Storage
--------+------------------------+-------+---------+---------
public | test_date | table | gpadmin | heap
public | test_date_1_prt_p2008 | table | gpadmin | heap
public | test_date_1_prt_p2009 | table | gpadmin | heap
public | test_date_1_prt_p2010 | table | gpadmin | heap
public | test_date_1_prt_pother | table | gpadmin | heap
(5 rows)
gpadmin=# \q
4. Check the age of the partition tables:
[gpadmin@mdw6 ~]$ vi table_age.sql
SELECT
coalesce(n.nspname, ''),
relname,
relkind,
relstorage,
age(relfrozenxid)
FROM
pg_class c
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
relkind = 'r' AND relstorage NOT IN ('x')
ORDER BY 5 DESC;
[gpadmin@mdw6 ~]$ psql -f ./table_age.sql
coalesce | relname | relkind | relstorage | age
--------------------+--------------------------+---------+------------+------------
public | test_date | r | h | 2147483647
~~ skip
public | test_date_1_prt_p2010 | r | h | 2
public | test_date_1_prt_p2008 | r | h | 2
public | test_date_1_prt_pother | r | h | 2
public | test_date_1_prt_p2009 | r | h | 2
Even after running VACUUM FREEZE or FULL on all partition tables, the age of root partition table never decreased.
Environment
Product Version: 6.1
OS: RHEL and CentOS 7
OS: RHEL and CentOS 7
Resolution
In versions prior to Greenplum 6.x, root partitions and internal parent partitions do not contain any data. However, now these tables still have a valid relfrozenxid and their age keeps growing.
In order to bring the age down, you need to run VACCUM on root, which then also trickles down and C=VACCUM's each and every child partition as well.
If only a leaf was modified these can be vacuumed in isolation, reducing the age and avoiding overhead of vacuuming the full hierarchy.
Similar to AO, CO, external tables, etc, for root and parent partition records, the relfrozenxid is 0 (InvalidTransaction) during table creation.
This works since these tables will never store any xids. This would prevent age calculation and prevent you from upgrading to Greenplum 6.x and future versions.
Note: Ideally, the same can be achieved by defining root and internal parent partition as AO tables, but this is a significant amount of work requiring a lot of DDL and modification.
Feedback
Yes
No
Powered by
