Alter table with check constraint and inheritance

[hemant agrawal]

HI

There is possible to alter table with check constraint and inheritance. 

I explain my case like

I have parent table  main_event

create table   main_event (eventdate  int,username character varying(1024), bytes bigint);

create index data_idx on events_current(eventdate);

I have child table week1. 

create FOREIGN table   week1(CHECK(eventdate >= 20180101 AND eventdate <= 20180107 )) inherits(main_event_week) SERVER cstore_server OPTIONS(compression 'pglz');

now I want to delete one row from the table. 

so I have created a temporary table and copy all data except deleted data into the temporary table. 

create FOREIGN table   temp_week1 (eventdate  int,username character varying(1024), bytes bigint);

now I drop wee1 table and alter temp_week1 table to child table of main_event with check constraint. 

 

can you tell me how can I achieve this thing?

exampl 

ALTER FOREIGN TABLE temp_week1  RENAME TO week1 ADD CONSTRAINT CHECK(eventdate >= 20180101 AND eventdate <= 20180107) inherits(main_event);

Is this way possible ?

[Murat Tuncer]

Hi Hemant;

Sorry for the delay, I was away from my desk for a while

Postgreql is now supporting partitioned tables. They are somehow more refined from inherited tables. I recommend using that instead as all new developments are focused on partitioned tables.

Syntax is very similar to inherited tables.

- You first create a main table which does not contain any data.

- Then create tables that are partition of the main table this is where you explicitly set ranges.

- You can attach a new partition table to partitioned table, or detach an existing partition.

You should be aware that  all ranges have to be disjoint. you can't have overlapping ranges. (PG11 brings notion of default range as fall back partition when no other matches)

From your description, I understand that eventdate is always increasing field, therefore there is no risk of inserting to old partitions. If it is the case you can use following model

- create main table

create table   main_event (eventdate  int,username character varying(1024), bytes bigint) partition by range (eventdate);

-- create a sufficiently large partition to hold incoming data

create table default_partition partition of main_event for values from (20180101) to (21000101);

- when you need to perform partition work1. create a new cstore table
 create foreign table week1 (eventdate  int,username character varying(1024), bytes bigint)

 server cstore_server options (compression 'pglz');

2. copy data from default_partition to new table
 insert into week1 select * from default_partition where eventdate >= 20180101 and eventdate < 20180108;

3. delete  copied data from default_partition
  delete from default_partition where eventdate < 20180108;

4. detach default_partition from main_table
  alter table main_event detach partition default_partition;

5. attach cstore table as a partition specifying its range (start range is inclusive end range is exclusive)
  alter table main_event attach partition week1 for values from (20180101) to (20180108);

6. re-attach default_partition such that it now has range starting from a later data.
  alter table main_event attach partition default_partition for values from (20180108) to (21000101);

You would need to make  a compromise here depending on whether you are willing to stop query or data ingestion during steps 3,4,5 and 6. 

If you want to keep inserting new data but can afford to stop queries for a while. You can do in the exact same order. You would need to but steps 4,5,6 into a transaction block.

If you want continuous querying but can afford to stop data ingestion. Put steps 3,4,5,6 into a transaction. Only difference between two is insertion would be blocked when deleting copied data from default_partition.

Hope it helps

Murat

--
You received this message because you are subscribed to the Google Groups "cstore users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cstore-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Murat Tuncer
Software Engineer | Citus Data
mtu...@citusdata.com

[hemant agrawal]

I have one more question. 

if I want to create child table using Partition table then how can I create?

like 

create FOREIGN table   day_20180101(CHECK(eventdate = 20180101)) inherits(main_event) SERVER cstore_server OPTIONS(compression 'pglz');

now above table create using partition table. 

please give me how can I create this table. 

[Murat Tuncer]

that would be

create foreign table day_20180101 partition of main_event for values from (20180101) to (20180102) server cstore_server options (compression 'pglz');

Notice that range includes 20180101 but not 20180102.

--
You received this message because you are subscribed to the Google Groups "cstore users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cstore-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[hemant agrawal]

ya thanks...

but I have done poc and I got the same result in both inheritance and partitioning table but delete record functionality using alter table achieve only by partitioning table. 

actually we are creating a new architecture to fulfill our requirement. Our application accept syslog and store all syslog after parse & summarised in postgresql. then we create different report for user. but now we want to change the architecture to achieve more optimized way. So we are doing POC on columnar database last one month and we explore more database but I got best result in columnar. So we go ahead with columnar database in postgresql and generate day wise table because syslog flow is very huge and we provide solution in the box.  For that , we developing new architecture and write new code from starting.

If you have any suggestion or tips then please give me. 

Thank You Again.

On Monday, 18 June 2018 15:35:02 UTC+5:30, hemant agrawal wrote: