PostgreSQL – Loop Table to Restart Sequence and Rewrite Old Rows

postgresql

I want table to contain logs and be looped, so when it came to last key in sequence it will start over from one and will rewrite old logs.

I can do it if i mark sequence as cycle and do requests like that:

INSERT INTO logs (initiator_id, target_id, action, message) VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE SET target_id=$2, initiator_id=$1, action=$3, message=$4;

What i don't like, is that i need to repeat same inserts if there is conflict (line 2)

What i'am curios about, is it a good way to do, or there is other more practical ways for tables that you need clear lets say every 3 mounts?

Or maybe there is a way to generate unique id without using sequences?

For now i decided to make 2 columns primary key (date, initiator_id) as log is always 1 action at a time.

Best Answer

you could just use a monotonic sequence (always counting upwards) and delete records based on their age

you could do table partitioning by date and just drop the old partitions

Postgresql never replaces records, an update always creates a new record and marks the old one as unused. Autovacuum sooner or later will make the space used by the old one available to hold new records. If you want something like RRDB your should probably be using somethign like RRDB instead of postgresql.

Related Solutions

PostgreSQL – How to Create Sequence and Table Simultaneously

You can create the sequence implicitely through the serial (pseudo) type and then change its current value:

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER); 
select setval('mytable_gid_seq', 100);

or alternatively using

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER); 
alter sequence mytable_gid_seq restart with 100;

This is slightly different than defining a minvalue - but that would only matter if the sequence was set to wrap. This version also creates a link between the sequence and the table, so that the sequence is automatically dropped when the table is dropped (in your script you would need to alter sequence mytable_gid_seq owner to mytable.gid);

PostgreSQL Date Interval – Get Date Interval from Table Rows

It's actually very simple. Start from the wanted date and descend the dates until you find the first row. If it's 'start' then you are inside an interval. If it's 'stop', you are not:

select 
  ( select startstop
    from mytable 
    where mydate = '2017-02-07' and startstop = 'start'
       or mydate < '2017-02-07'
    order by mydate desc
    limit 1
  ) = 'start'
  as result ;

The complicated WHERE is for dealing with the inclusive date ranges. If inclusive-exclusive were used, the condition would be simper.

A similar way - perhaps a bit more clear - would be:

with ct as 
  ( select mydate, startstop
    from mytable 
    where mydate <= '2017-02-07'
    order by mydate desc
    limit 1
  )
select 
       ('start') = (select startstop from ct) 
    or ('2017-02-07', 'stop') = (table ct) 
  as result ;

Best Answer

Related Solutions

PostgreSQL – How to Create Sequence and Table Simultaneously

PostgreSQL Date Interval – Get Date Interval from Table Rows

Related Question