On 02/18/2014 02:42 PM, Samuel Gilbert wrote:
On 2014-02-18 14:25:59 Adrian Klaver wrote:
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
I don't think that is the case since I currently have the BEFORE INSERT
trigger working on the non-partitioned version of the table.
Sorry for steering you wrong. I could have sworn I saw the behavior I
mentioned, previously, when trying to do what you have done.
The modificationdate field has a NOT NULL constraint. Even if I explicitly
provide a NULL for the modificationdate column, a date gets written in the
table. This leads me to believe that the BEFORE INSERT trigger is really
executed before the constraint is checked.
What I don't understand is why the trigger doesn't appear to be executed when
it's defined on a child table. I'll add a RAISE NOTICE to the trigger
function to makes sure it's not getting called.
Still not sure what is going on, but I do have a question based on this
statement from your original post:
"The modification date must be updated if any row is modified in any way."
If that is the case shouldn't the trigger also cover UPDATE?
--
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
