Thread: delete/recreate indexes

Hi

I'm a postgres novice so ....

I have this fairly simple table
-------------------------------------------------
 device integer not null,
 group integer not null,
 datum timestamp without time zone not null,
 val1 numeric(7,4) not null default 0.000,
 val2 numeric(7,4) not null default 0.000
-------------------------------------------------

The device column is a foreign-key to the PK of my device table.
and I have a UNIQUE INDEX on 3 columns device, group, datum

This is just a test database and I want to keep the "datum" values
(timestamps that span approx 1 month) all bound to CURRENT_DATE().

So I thought I’d just run this once (via cron) every morning.
    BEGIN;
        DROP INDEX data_unique;
        UPDATE data SET datum = (data.datum + interval '24 hours');
        CREATE UNIQUE INDEX data_unique ON public.data USING BTREE
(device, group, datum);
    COMMIT;

But
1.    it’s taking forever and
2.    I’m seeing that my disk is filling up real fast.

Any suggestions?

Alan

On Wed, 2011-10-19 at 08:03 -0700, alan wrote:
> So I thought I’d just run this once (via cron) every morning.
>     BEGIN;
>         DROP INDEX data_unique;
>         UPDATE data SET datum = (data.datum + interval '24 hours');
>         CREATE UNIQUE INDEX data_unique ON public.data USING BTREE
> (device, group, datum);
>     COMMIT;
>
> But
> 1.    it’s taking forever and
> 2.    I’m seeing that my disk is filling up real fast.

An unrestricted update will end up rewriting the whole table. It's
advisable to run VACUUM afterward, so that the wasted space can be
reclaimed. What version are you on? Do you have autovacuum enabled?

Also, to take a step back, why do you try to keep the timestamps
changing like that? Why not store the information you need in the record
(e.g. insert time as well as the datum) and then compute the result you
need using a SELECT (or make it a view for convenience)? Fundamentally,
these records aren't changing, you are just trying to interpret them in
the context of the current day. That should be done using a SELECT, not
an UPDATE.

Regards,
    Jeff Davis

> An unrestricted update will end up rewriting the whole table. 
> It's advisable to run VACUUM afterward, so that the wasted 
> space can be reclaimed. What version are you on? Do you have 
> autovacuum enabled?
> 
> Also, to take a step back, why do you try to keep the 
> timestamps changing like that? Why not store the information 
> you need in the record (e.g. insert time as well as the 
> datum) and then compute the result you need using a SELECT 
> (or make it a view for convenience)? Fundamentally, these 
> records aren't changing, you are just trying to interpret 
> them in the context of the current day. That should be done 
> using a SELECT, not an UPDATE.
> 

I like Jeff's idea of redefining the problem. If you need the data to contain dates in the last 30 days, you might want
toconsider storing an interval, then using a view that includes a calculation using CURRENT_DATE().
 

Regards,
Paul Bort
Systems Engineer
TMW Systems, Inc.
pbort@tmwsystems.com
216 831 6606 x2233
216 8313606 (fax)

Thanks Jeff,

On Oct 20, 4:51 am, pg...@j-davis.com (Jeff Davis) wrote:
> Also, to take a step back, why do you try to keep the timestamps
> changing like that? Why not store the information you need in the record
> (e.g. insert time as well as the datum) and then compute the result you
> need using a SELECT (or make it a view for convenience)? Fundamentally,
> these records aren't changing, you are just trying to interpret them in
> the context of the current day. That should be done using a SELECT, not
> an UPDATE.

Well this is not the way my "production" table is getting updated.
This was a developer's test DB so I thought the update statement would
be a
quick way to just shift all the values.

To mimic how my "production" database is being updated I should be
doing
this once each morning:

    1. delete the old entries older than 6 days (i.e.: my table holds
one week's data)
    2. add new entries for yesterday

I'm doing this via a perl script. For 1. I just do a
DELETE FROM device WHERE datum < (CURRENT_DATE - interval ' 7 days' )

For 2. I tried this but I get  an "invalid input syntax for type
timestamp:" error:
    my $val1 = rand(100);
    my $val2 = rand(100);
    my $stmt = "INSERT INTO data (device,group,datum,val1,val2)
VALUES(?,?,?,?,?)";
        my $insert = $dbh->prepare($stmt) or die $dbh->errstr;
    my $timestamp = "TO_TIMESTAMP(text(CURRENT_DATE - interval '1
day'),'YYYY-MM-DD HH24:MI:SS')";
    $insert->execute($device,$groupid,$timestamp,$val1,$val2));

Alan