On 02/18/2014 02:10 PM, Samuel Gilbert wrote:
I have data warehousing DB 2 fairly big tables : one contains about 200
million rows and the other one contains about 4 billion rows. Some queries
are now taking way too long to run (> 13 hours). I need to get these queries
to run in an hour or so. The slowdown was gradual, but I eventually hit a
wall, when the planner stopped using indexes.
The other issue I'm encountering is that I also have very simple BEFORE UPDATE
and BEFORE INSERT triggers that set the modification date on every single row
:
CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$
BEGIN
NEW.modificationDate := now();
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
The modification date must be updated if any row is modified in any way. I
first tried to define the triggers on the parent table. This worked, but I
realized that if a queries targets explicitly a child table, it could modify a
row without the date being updated. I therefore dropped the triggers on the
parent table and defined them for every child. To my great surprise, the
insert below failed with a message saying that NULLs are not allowed in the
modificationdate column.
INSERT INTO observation
(dataset, station, method, startdate, duration, value)
VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42);
Why isn't the BEFORE INSERT trigger on the child table being executed?
Constraints are checked before triggers are run.
Cheers,
Samuel Gilbert
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
