will pg_party support hourly partitioning in postgresql 10.4 ?

[buragaddapavan]

No description provided.

[derkan]

Hourly partitioning may be added. But give me some time for patching pg_party for automatic partitioning for PG11.

[derkan]

Also any help is appreciated.

[buragaddapavan]

im trying to update create partition command in pg_party script to support pg 10.

EXECUTE 'CREATE TABLE ' || schema_name || '.' || quote_ident(part_name)
|| ' (CHECK ((' || part_col || ' >= TIMESTAMP ''' || start_time || '+00:00'''
|| ' AND ' || part_col || ' < TIMESTAMP ''' || end_time || '+00:00'''
|| '))) INHERITS (' || schema_name || '.' || master_table || ')';

pg 10 partition syntax :

EXECUTE 'CREATE TABLE ' || schema_name || '.' || quote_ident(part_name)
partition of ' || schema_name || '. ' || master_table|| ' for values from
( ''' || start_time|| ''') to ( ''' || end_time|| ''')';

After modifying script, tried to execute script but fails with error at line 68.... but im not able to find problem

can u try it once

[derkan]

Missing ' before partition of

EXECUTE 'CREATE TABLE ' || schema_name || '.' || quote_ident(part_name) ||
' partition of ' || schema_name || '. ' || master_table|| ' for values from
( ''' || start_time|| ''') to ( ''' || end_time|| ''')';

[buragaddapavan]

It is available in the script... I missed it here while typing....

I have doubt on this part but not sure

( ''' || start_time|| ''') to ( ''' || end_time|| ''')';

Can u test it once

[derkan]

schema_name || '. ' || maybe space after dot? Pls check output with:

RAISE NOTICE 'CREATE TABLE ' || schema_name || '.' || quote_ident(part_name) ||
' partition of ' || schema_name || '. ' || master_table|| ' for values from
( ''' || start_time|| ''') to ( ''' || end_time|| ''')';

[buragaddapavan]

im getting below error..

NOTICE: Checking for partition ccmiddl.audit_2018326
NOTICE: New partition ccmiddl.audit_2018326is added to table ccmiddl.audit on column request_ts
ERROR: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function pg_party_date_partition(text,text,text,text,integer) line 68 at EXECUTE

[buragaddapavan]

Hi derkan, i just updated below to the script

EXECUTE 'CREATE TABLE ' || schema_name || '.' || quote_ident(part_name) ||
' partition of ' || schema_name || '. ' || master_table|| ' for values from
( ''' || start_time|| ''') to ( ''' || end_time|| ''')';

im getting below error..

NOTICE: Checking for partition ccmiddl.audit_2018326
NOTICE: New partition ccmiddl.audit_2018326is added to table ccmiddl.audit on column request_ts
ERROR: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function pg_party_date_partition(text,text,text,text,integer) line 68 at EXECUTE

Did u find the issue behind the error.....

[derkan]

I've added hourly partitioning support and PG10 support. Can you please check this new version?

[buragaddapavan]

tried with new script..

im using PG 10 and i got below error.Not sure why it is trying t o create inherit type partition table in PG10.

ERROR.cannot inherit from partitioned table.

[derkan]

Can you share DDL of parent table?

[buragaddapavan]

create table client_audit (
id bigint,
bg varchar(80),
request_ts timestamp,
reply_ts timestamp
) partition by range (request_ts);

FYI, already 10 partitions exists for this parent table and 4 indexes on each partitioned table.

[derkan]

I found the problem, it's OK now. Thanks for informing about the problem.

[derkan]

Also pls check docs, PG 10 doesn't allow to create indexes on parent table. So it is impossible to copy indexes from that table to partitions. So I've added pg_party_config_ddl table to let define DDLs to be run after new partition is created. You can add your index/constraint DDL's into that table, it is documented in README. Each DDL in that table will be run for new partititions.

[buragaddapavan]

Script works now...

I removed --no-password from the script and tested it by providing password when ever it prompts...
Is there any way to update password in the script?

Error I'm facing with --no-password is
Psql: fe_sendauth : no password supplied..

FYI... I'm using this script for my remote rds postgres database
Is there any way to update password in the script?

[derkan]

Glad, it worked. If you run script with postgres user on same host with DB, it will not require password. If you want to run it with different user you should modify your pg_hba.conf file for either ident or trust authentication types.

[derkan]

Closing issue as it is resolved.