pgpartman / pg_partman Goto Github PK

We've been running pg_partman for over more than a year without a problem on this specific workload on a Postgres 9.1 server. Yesterday we ran into a wait cascade on an INSERT/UPDATE heavy database. I don't know how the exact interaction looked like but I guess run_maintenance regularly waited until autovacuum moved further until it grabbed a lock on temp_download_log causing the huge backlog of open queries effectively halting the writing application.

Not sure if there's anything pg_partman can do about this other than possibly waiting/disabling/aborting autovacuum jobs?

top rows from SELECT * FROM pg_stat_activity ORDER BY xact_start DESC;

 datid |  datname  | procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |                                                                                 current_query                                                                                  
-------+-----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 16387 |   dllog   |   31833 |    16385 | root     | psql             |             |                 |          -1 | 2014-11-05 01:35:01.34915+01  | 2014-11-05 01:35:01.351475+01 | 2014-11-05 01:35:01.351475+01 | t       | SELECT run_maintenance()
 16387 |   dllog   |    6479 |       10 | postgres |                  |             |                 |             | 2014-11-05 01:58:26.975293+01 | 2014-11-05 01:58:26.996639+01 | 2014-11-05 01:58:26.996639+01 | f       | autovacuum: VACUUM public.requestlog_p2014_11_02 (to prevent wraparound)
 16387 |   dllog   |    4406 |    16385 | root     | psql             |             |                 |          -1 | 2014-11-05 02:10:05.954496+01 | 2014-11-05 02:10:05.957845+01 | 2014-11-05 02:10:05.957845+01 | t       | /* pganalyze-collector */                                                                                                                                                     +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         | SELECT n.nspname AS schema,                                                                                                                                                   +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |        c.relname AS table,                                                                                                                                                    +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |        pg_catalog.pg_table_size(c.oid) AS tablesize,                                                                                                                          +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |        a.attname AS name,                                                                                                                                                     +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |        pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,                                                                                                          +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |   (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)                                                                                                                          +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |    FROM pg_catalog.pg_attrdef d                                                                                                                                               +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |    WHERE d.adrelid = a.attrelid                                                                                                                                               +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |      AND d.adnum = a.attnum                                                                                                                                                   +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |      AND a.atthasdef) AS default_value,                                                                                                                                       +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |        a.attnotnull AS not_null,                                                                                                                                              +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |        a.attnum AS position                                                                                                                                                   +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         | FROM pg_catalog.pg_class c                                                                                                                                                    +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace                                                                                                                 +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         | LEFT JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid                                                                                                                     +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         | WHERE c.relkind = 'r'                                                                                                                                                         +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |   AND n.nspname <> 'pg_catalog'                                                                                                                                               +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |   AND n.nspname <> 'information_schema'                                                                                                                                       +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |   AND n.nspname !~ '^pg_toast'                                                                                                                                                +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |   AND a.attnum > 0                                                                                                                                                            +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |   AND NOT a.attisdropped                                                                                                                                                      +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         | ORDER BY n.nspname,                                                                                                                                                           +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |          c.relname,                                                                                                                                                           +
       |           |         |          |          |                  |             |                 |             |                               |                               |                               |         |          a.attnum;
 16387 |   dllog   |    1483 |    16384 |   dllog  |                  | ::1         |                 |       57125 | 2014-11-05 02:19:38.832365+01 | 2014-11-05 02:33:05.420165+01 | 2014-11-05 02:33:05.420268+01 | t       | UPDATE temp_download_log SET stop=now(), bytes = $2 WHERE ticket = $1
 16387 |   dllog   |    3356 |    16384 |   dllog  |                  | 127.0.0.1   |                 |       52575 | 2014-11-05 01:49:49.893653+01 | 2014-11-05 02:33:05.423826+01 | 2014-11-05 02:33:05.423982+01 | t       | UPDATE temp_download_log SET stop=now(), bytes = $2 WHERE ticket = $1
 16387 |   dllog   |   30256 |    16384 |   dllog  |                  | ::1         |                 |       57052 | 2014-11-05 02:07:29.180277+01 | 2014-11-05 02:33:05.429638+01 | 2014-11-05 02:33:05.429858+01 | t       | INSERT INTO temp_download_log (ticket,ip,server,storage_id,auth,filesize,start ) SELECT $1,$2,$3,$4,$5,$6,$7 WHERE NOT EXISTS(SELECT 1 FROM temp_download_log WHERE ticket=$1)
 16387 |   dllog   |   31952 |    16384 |   dllog  |                  | ::1         |                 |       56300 | 2014-11-05 01:35:27.541884+01 | 2014-11-05 02:33:05.431168+01 | 2014-11-05 02:33:05.431281+01 | t       | UPDATE temp_download_log SET stop=now(), bytes = $2 WHERE ticket = $1
 16387 |   dllog   |   18562 |    16384 |   dllog  |                  | ::1         |                 |       57087 | 2014-11-05 02:13:25.486869+01 | 2014-11-05 02:33:05.436537+01 | 2014-11-05 02:33:05.436714+01 | t       | INSERT INTO temp_download_log (ticket,ip,server,storage_id,auth,filesize,start ) SELECT $1,$2,$3,$4,$5,$6,$7 WHERE NOT EXISTS(SELECT 1 FROM temp_download_log WHERE ticket=$1)
 16387 |   dllog   |   10088 |    16384 |   dllog  |                  | ::1         |                 |       56766 | 2014-11-05 01:59:36.840651+01 | 2014-11-05 02:33:05.437661+01 | 2014-11-05 02:33:05.437779+01 | t       | UPDATE temp_download_log SET stop=now(), bytes = $2 WHERE ticket = $1
 16387 |   dllog   |   22462 |    16384 |   dllog  |                  | ::1         |                 |       56782 | 2014-11-05 02:02:38.133033+01 | 2014-11-05 02:33:05.438928+01 | 2014-11-05 02:33:05.439098+01 | t       | INSERT INTO temp_download_log (ticket,ip,server,storage_id,auth,filesize,start ) SELECT $1,$2,$3,$4,$5,$6,$7 WHERE NOT EXISTS(SELECT 1 FROM temp_download_log WHERE ticket=$1)
 16387 |   dllog   |    5163 |    16384 |   dllog  |                  | ::1         |                 |       56645 | 2014-11-05 01:55:46.16061+01  | 2014-11-05 02:33:05.442612+01 | 2014-11-05 02:33:05.442742+01 | t       | UPDATE temp_download_log SET stop=now(), bytes = $2 WHERE ticket = $1
 16387 |   dllog   |   30646 |    16384 |   dllog  |                  | ::1         |                 |       57061 | 2014-11-05 02:08:39.371713+01 | 2014-11-05 02:33:05.443971+01 | 2014-11-05 02:33:05.444176+01 | t       | INSERT INTO temp_download_log (ticket,ip,server,storage_id,auth,filesize,start ) SELECT $1,$2,$3,$4,$5,$6,$7 WHERE NOT EXISTS(SELECT 1 FROM temp_download_log WHERE ticket=$1)
 16387 |   dllog   |   26309 |    16384 |   dllog  |                  | ::1         |                 |       56901 | 2014-11-05 02:03:48.401067+01 | 2014-11-05 02:33:05.444772+01 | 2014-11-05 02:33:05.44494+01  | t       | INSERT INTO temp_download_log (ticket,ip,server,storage_id,auth,filesize,start ) SELECT $1,$2,$3,$4,$5,$6,$7 WHERE NOT EXISTS(SELECT 1 FROM temp_download_log WHERE ticket=$1)
 16387 |   dllog   |    7482 |    16384 |   dllog  |                  | ::1         |                 |       57252 | 2014-11-05 02:23:03.367669+01 | 2014-11-05 02:33:05.445987+01 | 2014-11-05 02:33:05.446114+01 | t       | UPDATE temp_download_log SET stop=now(), bytes = $2 WHERE ticket = $1

Relevant part_config entries:

       parent_table       |    type     | part_interval |  control  | premake | retention | retention_keep_table | retention_keep_index | datetime_string |            last_partition            | undo_in_progress | retention_schema | constraint_cols | jobmon | use_run_maintenance | inherit_fk 
--------------------------+-------------+---------------+-----------+---------+-----------+----------------------+----------------------+-----------------+--------------------------------------+------------------+------------------+-----------------+--------+---------------------+------------
 public.temp_download_log | time-static | 1 day         | start     |       5 | 2 days    | f                    | t                    | YYYY_MM_DD      | public.temp_download_log_p2014_11_09 | f                |                  |                 | t      | t                   | t
 public.requestlog        | time-static | 1 day         | timestamp |       4 | 2 days    | f                    | t                    | YYYY_MM_DD      | public.requestlog_p2014_11_08        | f                |                  |                 | t      | t                   | t
 public.download_log      | time-static | 7 days        | start     |       4 | 6 weeks   | f                    | t                    | IYYY"w"IW       | public.download_log_p2014w49         | f                |                  |                 | t      | t                   | t

Hi, this is just (as per requested) a reminder regarding the discussion we had on irc.

If for any reason, the system which is populating the database experiences an outage and restart within a delay which is larger than the number of pre-allocated partitions, then new data is inserted in Parent table (which is working as designed).
Problem is that using partition_data will re-create new partitions but will not re-create the trigger function. Thus new, records still populate the parent table.
a workaround (as discussed) is to

1. unpartition the table
2. partition it with a number of pre-allocated partitions that is larger than the expected maximum outage
3. run partition_data

Instead of unpartitioning, we found another solution which is the following:
[partitioned table is public.history]

1. run python script in a "watch" command to move data from parent table into child tables:
   watch -n2 'partition_data.py -p public.history -t id -c "host=localhost dbname=zabbixdb" -i 100000'
2. execute the following statement as soon as there are no more rows to be inserted
   update partman.part_config set last_partition=(select tablename from pg_tables where tablename like 'history_p%'order by 1 desc limit 1);

select partman.create_id_function('public.history', (select cast(trim(leading 'history_p' from tablename) as bigint) from pg_tables where tablename like 'history_p%'order by 1 desc limit 1
));

Currently, we have all of our partitions set to weekly. I would like to switch them all to daily, but I don't see an easy way to do that. I was thinking sub-partitioning could be used, but my first few attempts did not work how I expected:

csoc=# begin;
csoc=# \dt platform_events*
                 List of relations
 Schema |           Name           | Type  | Owner
--------+--------------------------+-------+-------
 eyrie  | platform_events          | table | eyrie
 eyrie  | platform_events_p2014w40 | table | eyrie
 eyrie  | platform_events_p2014w41 | table | eyrie
 eyrie  | platform_events_p2014w42 | table | eyrie
 eyrie  | platform_events_p2014w43 | table | eyrie
 eyrie  | platform_events_p2014w44 | table | eyrie
 eyrie  | platform_events_p2014w45 | table | eyrie
 eyrie  | platform_events_p2014w46 | table | eyrie
(8 rows)
csoc=# SELECT partman.create_sub_parent('eyrie.platform_events', 'event_ts', 'time-static', 'daily', p_premake := 2);
 create_sub_parent
-------------------
 t
(1 row)

csoc=# \dt platform_events*
                       List of relations
 Schema |                 Name                 | Type  | Owner
--------+--------------------------------------+-------+-------
 eyrie  | platform_events                      | table | eyrie
 eyrie  | platform_events_p2014w40             | table | eyrie
 eyrie  | platform_events_p2014w40_p2017_04_06 | table | eyrie
 eyrie  | platform_events_p2014w41             | table | eyrie
 eyrie  | platform_events_p2014w41_p2017_05_07 | table | eyrie
 eyrie  | platform_events_p2014w42             | table | eyrie
 eyrie  | platform_events_p2014w42_p2017_06_06 | table | eyrie
 eyrie  | platform_events_p2014w43             | table | eyrie
 eyrie  | platform_events_p2014w43_p2017_07_07 | table | eyrie
 eyrie  | platform_events_p2014w44             | table | eyrie
 eyrie  | platform_events_p2014w44_p2017_08_07 | table | eyrie
 eyrie  | platform_events_p2014w45             | table | eyrie
 eyrie  | platform_events_p2014w45_p2017_09_06 | table | eyrie
 eyrie  | platform_events_p2014w46             | table | eyrie
 eyrie  | platform_events_p2014w46_p2017_10_07 | table | eyrie
(15 rows)

How come the daily partitions have _p2017_in their name (this was with version 1.8.0)? I was expecting it to be _p2014_. How can I make sure that 7 daily partitions are made for each existing weekly partition? Once the daily sub partitions are created, should I just do an INSERT...SELECT from each weekly partition back to the main table to get it redirected?

Alternatively, should I just change the values in partman.part_config for part_interval? I tried that, but run_maintenance doesn't create any new daily partitions.

I've got a few tables where the initial partition interval estimation was quite off, either creating partitions only a few hundred megabytes in size or on the other end of the spectrum, tens of gigabytes.

Is there a way to change the interval for future segments without either interfering with the partition creation/dropping mechanic or having to undo and redo all the partitions effectively copying all the data?

What I see as a problem is, that the retention/dropping logic expects partitions to have a specific format - losing that automation would be a compromise which would be ok (at least for me).

Hi,

I'm running pg_partman v1.8.6, cloned straight from your git.

I have a table that is time partitionned per week, and subpartitionned per day, on which I ran undo_partiton. I then re-partitionned it. When trying to move the rows to the subtables, partition_data_time failed on two specific child tables with the following error message:

cdr=# SELECT partman.partition_data_time('public.cel_p2015w23');
ERROR: invalid input syntax for type timestamp: "ANSWER"
CONTEXT: PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text) line 52 at EXECUTE statement

I get the same error when running partition_data.py.

When I do a select on that sub table, all the data looks correct. There are no rows that have "ANSWER" in a timestamp column. I'm trying to see if there are any special characters in those lines and the lines around it that might cause parsing issues, but I cannot see anything out of the ordinary.

This is a lab setup, so I can share a pg_dump of the database if need be.

See comment from blog

http://www.keithf4.com/table-partitioning-and-foreign-keys/#comment-1426659496

The LOOP query that creates the v_row.ref_table variable will not have the schema set if it's in the search_path of the role that called apply_foreign_keys()

Additional option for check_name_length that if it finds another object of the same name, it adds a number onto it. Not so much for the child partitions, but for other object name (if partition parameter is false, add the number on)

I have a table, already partitioned using pg_partman, based on timestamp column as time-static monthly partitions. There are currently over 365 million records in child tables and new records are being added with order of about 1.3 million records per day. Here is the current state of tables: https://gist.github.com/ali-sattari/c2a2c8d49ab409680968

Since the rate that new records are being added has increased more than previously expected, we are loosing performance on inserts and updates near then end of each month, as child tables, and consequently indexes, get bigger.

I plan, as a solution, to move from time based partitioning to id (static) based partitions with maximum of 20 million records per partition. The problem is that I can't (due to the number of records) do an undo-partion and then redo partitiong as id-static. And I also have no intention to migrate previous child tables, they are just fine to remain as monthly partitioned tables, since they are read only (time-static).

As far as I know, partioning is control by both a trigger functions and partman config table, which is after all maintained by run_maintenance() function periodically. So in order to change time-static partitioning to id-static, I should change both trigger function and config record to reflect the changes. I am not sure if the run_maintenance() function would generate future tables correctly.

Please advice on any solution to make this transition smoothly.

It would be extremely handy if the create_parent and run_maintenance functions would apply any foreign key constraints defined on the parent table to each partition it creates. Right now, indexes are copied. That foreign keys are not automatically inherited is a wart in general, but it would be extremely helpful if pg_partman could fill that gap. I'm about to use the DDL event system to accomplish the same goal, but it would be nice if it was built-in to pg_partman.

Currently, DELETE and UPDATE triggers that are defined on the parent will be silently ignored if they trigger action in a child table. INSERT triggers fire correctly, but from what I can tell, DELETE/UPDATE do not at all. I'm trying to automate updating an external system on changes to any data in the child tables. I can create them manually, but it would be great if pg_partman did it when it created new partitions.

PostgreSQL 9.3.1 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit

latest partman from git.

select partman.partition_data_time('public.login_history'::text, 1, interval '1 month' , 30)

ERROR: column "v_max_partition_id" does not exist
LINE 3: ' AND '||v_control||' < '||v_max_partition_i...
^
QUERY: SELECT 'SELECT * FROM ' || p_parent_table ||
' WHERE '||v_control||' >= '||v_min_control||
' AND '||v_control||' < '||v_max_partition_id
||' FOR UPDATE NOWAIT'
CONTEXT: PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric) line 75 at assignment
********** Error **********

ERROR: column "v_max_partition_id" does not exist
SQL state: 42703
Context: PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric) line 75 at assignment

See if FKs on the parent table can be determined dynamically from catalog tables and applied to all children.
If so, also create a maintenance function/script to reapply FKs to child tables.

I'm trying to partition an existing table (cdr2 has previous 3 months of data) with daily time-static. I've run create_parent() and run_maintenance() and it premakes the 4 daily partition tables while updating the cdr2_part_trig_func(). I then use partition_data.py to move the previous 3 months data into partition tables. It creates the partition tables but it doesn't update the cdr2_part_trig_func() with the new child tables.
./partition_data.py -c "dbname=test" -p public.cdr2 -t time -i "1 hour" -b 1440

I manually update cdr2_part_trig_func() to take the new previous 3 months partition tables into consideration but run_maintenance() will wipe out my changes.

Is there a way to make sure run_maintenance() either doesn't wipe my edit to cdr2_part_trig_func() out or adds all the partitions tables to cdr2_part_trig_func() correctly?

This one threw me for a loop for a while:

eventdb2=# SELECT create_parent('public.transactions',
eventdb2(# 'paid_on',
eventdb2(# 'time_dynamic',
eventdb2(# 'monthly');
ERROR: function public.transactions_part_trig_func() does not exist

... clearly we need to add value-checking for the parameters (time-dynamic, not time_dynamic). Putting this on my personal TODO list for 1.4.6 or so.

Would be nice to have either an option for partition type 'id-static' to create trigger functions without automatic partition creation or to have an additional partition type without this functionality at all.

Of course, run_maintenance() has to be updated accordantly.

Beside the efficiency gain by checking p_control within the trigger function for insert only , it would prevent issues on tables with a lot of inserts via parallel sessions:

< 2014-03-16 13:39:12.452 CET >ERROR:  duplicate key value violates unique constraint "pg_type_typname_nsp_index"
< 2014-03-16 13:39:12.452 CET >CONTEXT:  PL/pgSQL function history_part_trig_func() line 30 at assignment

As a wishlist item, it would be nice to be able to specify a different schema for child tables.

When partitions are dropped by run_maintenance() / drop_partition_id(), the corresponding trigger function is not rebuilt, so still contains references to the dead partition.

This means that new inserts within the non-retained range have rather unwelcome behaviour: If the table is just disinherited, the insert will proceed, but not be visible afterwards. If the table is dropped or moved, the trigger function will raise an error.

Example: the following will produce ERROR: relation "public.test_p0" does not exist:

create table test ( id serial, value text not null );

select partman.create_parent('public.test', 'id', 'id', '100');

update partman.part_config set partition_interval = '100'::text;
update partman.part_config set retention = '200'::text;
update partman.part_config set retention_keep_table = false;

insert into test ( value )
select 'test1: ' || generate_series(0, 500);

select partman.run_maintenance();

select min(id), max(id) from test;

insert into test ( id, value )
values ( 10, 'invalid' );

Our tables are partitioned by month. The partition key is an integer representation of a date, in example 20140630. We currently use our in-house dynamic SQL scripts and rules to maintain this, but would ideally like to move to pg_partman (as we only found out about it recently). I quickly had a look at the documentation and I am under the impression that our use case is currently not supported. Is this correct?

Keith,

I may be reading the code wrong, but it seems to me that partition_data_id always starts from the beginning of the parent table, regardless of whether it's been called before and the table is already partly partitioned. Am I missing something in the logic here?

Implement a method to allow child tables to themselves be parent tables of another partition set.

Hi Keith,

I created a POC with PartMan and ran into an issue with running run_maintenance().

CREATE TABLE daily_event
(
event_id serial NOT NULL,
description character varying(64),
event_date timestamp with time zone NOT NULL,
CONSTRAINT daily_event_pk PRIMARY KEY (event_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE daily_event
OWNER TO postgres;

I then added a bunch of rows within the initial date range (plus or minus 4 days).

Created the partitions and moved the rows from the parent to children -
SELECT partman.create_parent('public.daily_event', 'event_date', 'time-static', 'daily');
SELECT partman.partition_data_time('public.daily_event', 100)

So far so good. I then added rows outside the initial date range and ran run_maintenance().

$ psql -c 'SELECT partman.run_maintenance(false)' partman_test
Password:

run_maintenance

(1 row)

The parent table trigger wasn't updated and no new partitions were created.

I then tried running this using pgAdmin3 -
SELECT partman.partition_data_time('public.daily_event', 100)

It created the partitions and copied the rows from the parent, but the trigger and part_config.last_partition value wasn't updated.

Any thoughts on what I might be doing wrong?

When running run_maintenance(null, false), analyzing isn't done on creation of new child tables. However, analyzing is still performed if partman.part_config has a non-null value in the constraint_cols column for the specified parent table.

I have custom statistics set for some columns in the parent table but they don't propagate to the children. Possible to add this to the create_*_partition() or a hint on how to best do this after running run_maintenance()?

ALTER TABLE parent ALTER COLUMN column SET STATISTICS 1000;

I know that is probably not the right place for such an request but I don't know where to put it else. So are there plans to support other field types then date and integer? Because I want to this based on an uuid field.

I have a number of existing tables which have been partitioned using an old scheme. I would like to transition to using pg_partman for this going forward.

On the first execution of the run_maintenance function, it doesn't recognise the format of my legacy partition names (in drop_partition_time function) which is understandable as our legacy naming scheme is different, and so calculates the v_partition_timestamp as "0001-01-01 00:00:00+00 BC". This lead to the removal of inheritance from all legacy partitions and the data in those partitions immediately becoming inaccessible to queries against the parent table.

Would it not be preferable to ignore partitions that are not under pg_partman control and therefore don't yield valid timestamps when searching for partitions to drop during maintenance? Management of those partitions would be left to the user and the approach would make migrating from a legacy partition management scheme to pg_partman more feasible.

Hi.

Is running ANALYZE on parent table is mandatory after creating new partition? Although we don't have any records in parent table it takes some huge time to do it:

xivastore01e/xivadb M # select count(*) from only xiva.notifications;
 count
-------
     0
(1 row)

Time: 1.086 ms
xivastore01e/xivadb M # analyze xiva.notifications;
ANALYZE
Time: 157471.619 ms
xivastore01e/xivadb M #

Since partman.run_maintenance is a function and all its work is done in a single transaction we get locking problems when we call it. A simple change solves our problem but how secure is it?

We are using version 1.8.2 and part_config looks as following:

xivastore01e/xivadb M # select * from partman.part_config ;
-[ RECORD 1 ]--------+-------------------------------
parent_table         | xiva.notifications
type                 | time-static
part_interval        | 1 day
control              | event_dt
constraint_cols      | [null]
premake              | 15
inherit_fk           | t
retention            | 30 days
retention_schema     | [null]
retention_keep_table | t
retention_keep_index | t
datetime_string      | YYYY_MM_DD
last_partition       | xiva.notifications_p2015_05_29
use_run_maintenance  | t
jobmon               | t
undo_in_progress     | f

Time: 1.164 ms
xivastore01e/xivadb M #

Example:

insert into example_table (field1,field2) values(1,1) returning id;

Normally in postgresql this will return the ID of the inserted row. On my tables using pg_partman extension I don't get the ID back. I'm using an ORM which is basically breaking because this is unexpected for a postgresql database.

It was a struggle to get this installed in windows. Below is how I successfully was able to install it. Would be nice to add to the documentation. Windows installs done through the Windows Installer don't support 'make' commands for installation of Postgres extensions. So these instructions will replace the 2 make step commands. These could be put into a Windows bat file for easier installation.

• Download the zip from of this project, and unzip it.
• Open the pg_partman.control file in a text editor. Make note of the default_version value. At the time of writing, it is 1.8.3.
• Open up a Windows command prompt. Navigate to the location where you unzipped it. For example:

cd C:\Users\Scott\Downloads\pg_jobmon-master\pg_jobmon-master

• Run this command to copy all the sql files into 1 file sql file. Change '1.8.3' to the version noted in the above step.
  If using Postgres 9.2 or higher:

copy sql\types\*.sql + sql\tables\*.sql + sql\functions\*.sql + sql\92\tables\*.sql pg_partman--1.8.3.sql /B

If using Postgres 9.1 or lower

copy sql\types\*.sql + sql\tables\*.sql + sql\functions\*.sql pg_partman--1.8.3.sql /B

• Now move or copy the the pg_partman--1.8.3.sql and pg_partman.control files into the {postgres_install}\share\extension folder.

That completes the equivelant of the 'make' steps from the instructions. You can now proceed to the next steps creating the extension.

Hey, I didn't see an easy way to 'ask a question' or 'suggest a feature' might be nice to have a mailing list of some type for that purpose created.
Cheers!
-roger-
[the feedback was "dang, I really wanted it to at least be an option to automatically create new partitions, what we want to have is basically oracle's interval partitioning but for postgres"]
Thanks!

Upcoming 9.4 feature (possibly):
Create a background worker process that can be dynamically loaded that will automate partition maintenance without requiring any external scheduling tool (cron, etc).

There are problems with tables with OIDs. PostgreSQL won't create any partition if inherited table hasn't got oids and parent one has.

error I've got:
ERROR: table "transactions_p2013_11" without OIDs cannot inherit from table "transactions" with OIDs

********** Błąd **********

ERROR: table "transactions_p2013_11" without OIDs cannot inherit from table "transactions" with OIDs
Stan SQL: P0001

Keith, could we get the extras scripts into the pgxn package? Maybe just installed as docs or something?

Otherwise now you have to go pull them from github even if you pgxn install pg_partman.

If migrating from a legacy partition management approach to pg_partman, and the existing time-static partitions contain the _p character sequence in their name, run_maintenance fails with ERROR: invalid value "<substring from legacy partition name>" for "<datetime_string based on partitioning period>" based on partition.

A minimal example to demonstrate the issue:

CREATE TABLE public.my_parent(id bigserial, ts timestamp without time zone not null, txt varchar(10));
CREATE TABLE public.my_parent_201511() INHERITS (public.my_parent) WITH (OIDS=FALSE);
CREATE TABLE public.my_parent_201512() INHERITS (public.my_parent) WITH (OIDS=FALSE);
CREATE TABLE public.my_parent_201513() INHERITS (public.my_parent) WITH (OIDS=FALSE);
CREATE TABLE public.my_parent_201514() INHERITS (public.my_parent) WITH (OIDS=FALSE);
SELECT * FROM partman.create_parent('public.my_parent','ts','time-static', 'weekly', null, 1, true, null, true, false, false);
SELECT * FROM partman.run_maintenance('public.my_parent', false, false);

This produces the following error:

ERROR: invalid value "aren" for "IYYY"
********** Error **********

I just installed this extension and try to add new partition set.

When i execute SQL:

SELECT partman.create_parent('public.phone', 'region_id', 'id-dynamic', '100')

Server responses error (for some reasons i see errors in Russian, so i translated it back to English):

ERROR: syntax error (the approximate position: "-")

My table structure:

CREATE TABLE phone
(
  "number" bigint NOT NULL,
  status integer NOT NULL,
  region_id integer NOT NULL,
  CONSTRAINT phone_pkey PRIMARY KEY (number),
  CONSTRAINT phone_status_fkey FOREIGN KEY (status)
      REFERENCES phone_status (phone_status_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT phone_region_id_fkey FOREIGN KEY (region_id)
      REFERENCES region (region_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Referencing tables:

CREATE TABLE phone_status
(
  phone_status_id serial NOT NULL,
  name character varying(256),
  CONSTRAINT phone_status_pkey PRIMARY KEY (phone_status_id)
)
WITH (
  OIDS=FALSE
);
CREATE TABLE region
(
  region_id serial NOT NULL,
  name character varying(64),
  CONSTRAINT region_pkey PRIMARY KEY (region_id)
)
WITH (
  OIDS=FALSE
);

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
pg_partman v1.7.2
pg_jobmon v1.2.0

I am using mimeo to pull in data from multiple sources in different time zones. The destination is partitioned using partman. The trigger function created by partman, does not seem to work with these disparate sources.

select to_char(timestamp with time zone '2015-02-02 00:00:00.343+11', 'IYYY"w"IW')
Return 2015w06, which is the current behaviour of the trigger function, however the constraint that has been generated does not match. It is using the postgres.conf timezone of Australia/Melbourne (+11) however the constraints are generated based on UTC.

select to_char(timestamp with time zone '2015-02-02 00:00:00.343+11' at time zone 'utc', 'IYYY"w"IW')
Returns 2015w05 which matches what the constraint is requiring.

The constraint is
CONSTRAINT data_value_p2015w06_partition_check CHECK (creation_timestamp >= '2015-02-02 11:00:00+11'::timestamp with time zone AND creation_timestamp < '2015-02-09 11:00:00+11'::timestamp with time zone)
)
and this is the table the trigger is trying to insert into.

postgres@terbium ~ $ /usr/lib64/postgresql-9.1/bin/run_refresh.py -c "host=localhost dbname=process_data user=postgres" -v
Running inserter_serial replication for table: cojafex.data_value
Traceback (most recent call last):
File "/usr/lib64/postgresql-9.1/bin/run_refresh.py", line 102, in
single_process(result, mimeo_schema)
File "/usr/lib64/postgresql-9.1/bin/run_refresh.py", line 60, in single_process
cur.execute(sql, [i[0]])
psycopg2.InternalError: new row for relation "data_value_p2015w06" violates check constraint "data_value_p2015w06_partition_check"

For now, I have added to the trigger function, the "at time zone 'UTC'".

custom_time_partitions should be cleaned by undo_partition_time().
Currently, if you partition a table with a custom interval and undo the partitioning, you can't partition it again until you have manually removed the entries from custom_time_partitions.

Hi, i'm using the 1.8.4 release of pg_partman.
I wanted to time partion a table on my postgresql 9.3 db with an interval of partitioning of one hour, and so i set up a cronjob to call run_maintenance two times every hour. Premake is set to 2. I set up keep table and keep index on part_config at false (i want to delete older child partition) . I'm testing this configuration on my pc.
My problem is that last night, before i turned off my pc last night, i had the following tables in my db:

mytable
mytable_p2015_04_26_1300
...
mytable_p2015_04_26_1900t
mytable_p2015_04_15_2000

This morning (2014-04-27 at 10:00) i launched run_maintenance and the older child tables have been destroyed (which is something expected) but newer tables have not been created, and the only table in my database is "mytable" (the parent table), and insert operation are performed directly on it.
I suppose that this is due to the fact that run_maintenance has not been called twice the partitioning interval (because my computer was turned off during this interval).
In the same database I have another table that is partitioned daily but run_maintenance correctly created the new child table and deleted the older.
There is any way to recover from this state?

Thank you for your help

The example in README.md doesn't work:

CREATE TABLE test.part_test (col1 serial, col2 text, col3 timestamptz DEFAULT now());
SELECT public.create_parent('test.part_test', 'col3', 'time-static', 'daily');
ERROR:  P0001: Control column (col3) for parent table (test.part_test) must be NOT NULL

I'd like to be able to add an index to the parent and then run reapply_indexes.py to have it propagated to the children without having it drop and recreate all the exisiting indexes. Alternatively, a separate script to do this.

Hello,

I want to partition a table by month with an history starting one year ago. partition_data.py work well but only migrate data from start of this year and left all data for 2013 into parent table.

Is there a way to migrate all my historical data into children table using partman, or do I have to migrate manually data from previous year ?

Thanks for your work

Hi,

We use pg_partman since a year but we are facing table size issues.

We use id-static partitionning for our table (zabbix), but we see that run_maintenance procedure take ages to complete (when it complete) because of this query
EXECUTE 'SELECT '||v_row.control||' - ('||v_row.control||' % '||v_row.part_interval::int||') FROM '||v_row.parent_table||'
WHERE '||v_row.control||' = (SELECT max('||v_row.control||') FROM '||v_row.parent_table||')'
INTO v_current_partition_id;

The fact is the explain plan will be awfull and will consume all ressources.

I think that epoch based partitionning could handle the issue, do you think that it's something feasable ?

in pg_partman.md partition_data_time is documented as:
partition_data_time(p_parent_table text, p_batch_interval interval DEFAULT NULL, p_batch_count int DEFAULT 1) RETURNS bigint

The source says:
partition_data_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_lock_wait numeric DEFAULT 0)

Seems like batch count and interval got changed around.

br.
Kjell

Is there any support for pg_partman to create foreign keys between the partitions of two tables; ie. where the parent tables have a foreign key constraint between them, and have pg_partman establish the foreign keys between each of the corresponding partitions beneath those two tables?

The behaviour I observe is that the foreign key is established between the partition and the parent related table rather than the partition of the related table.

I'd like a mode that would use non-dynamic SQL to manage data within premake intervals of the current timestamp and dynamic sql (possibly with partition table creation too) for events outside that range, rather than putting those events in the parent table. So this would be a hybrid of dynamic and static.

I'm having this issue when running run_maintenance() in a very busy database system and child tables aren't created making the parent table receive data and have to run partition_data.py constantly to clean parent tables. Do you have an idea of how to avoid deadlocks? maybe if run_maintenance() locks the table before any other transaction until it finishes all set of child table creation. Maybe with an external script in python.

partition_data_time fails if it is run in a different timezone than the constraints and triggers on the target table were created in. This caught us when we attempted to run partition_data.py from a machine in California against a table that had been created from a connection with timezone = 'UTC'.

Example script to reproduce the issue:

SET timezone = 'UTC';
CREATE SCHEMA repro;
CREATE TABLE repro.broken (ts timestamptz NOT NULL);
INSERT INTO repro.broken VALUES ('2014-10-01 17:00:00-07'::timestamptz);
SELECT partman.create_parent (
    p_parent_table := 'repro.broken',
    p_control := 'ts',
    p_type := 'time-static',
    p_interval := 'daily'
);
SET timezone = 'US/Pacific';
SELECT partman.partition_data_time ('repro.broken');

This script fails with the following constraint violation:

ERROR:  new row for relation "broken_p2014_10_01" violates check constraint "broken_p2014_10_01_partition_check"
DETAIL:  Failing row contains (2014-10-01 17:00:00-07).
CONTEXT:  SQL statement "WITH partition_data AS (
            DELETE FROM ONLY repro.broken WHERE ts >= '2014-10-01 00:00:00' AND ts < '2014-10-02 00:00:00' RETURNING *)
            INSERT INTO repro.broken_p2014_10_01 SELECT * FROM partition_data"
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text) line 144 at EXECUTE statement

This is simple enough to fix on our part, but has me a bit worried that other maintenance functions and triggers may be affected by timezone issues as well.

As part of #23, foreign keys are now applied to child tables. However, the DEFFERABLE/ NOT DEFERRABLE and INITIALLY DEFERRED / INITIALLY IMMEDIATE flags are not copied. I use a pattern of deleting data and using COPY in (ensuring that everything is consistent at commit time), which this change breaks. These 2 flags are available via pg_constraint ( http://www.postgresql.org/docs/current/static/catalog-pg-constraint.html ) as condeferrable and condeferred. The alternative is to use pg_get_constraintdef ( http://www.postgresql.org/docs/current/static/functions-info.html ) to generate the exact same definition as the existing one, and just apply it with a new name to the child table.

id-Static partitioning is not working probably, when using the extension on a table with data inserted, all pre-partition data does not go to child tables and after running run_maintenance, no child tables are being created, how is it possible to overcome this problem?

Keith,

Is there a good reason not to use argparse for handling the arguments for partman? If that's just a relic, I'll do a quick overhaul on my fork and submit it.