From: | "Nestor A(dot) Diaz" <nestor(at)tiendalinux(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Autovacuum on partitioned tables in version 9.1 |
Date: | 2014-11-12 12:56:02 |
Message-ID: | 546358E2.8030607@tiendalinux.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/11/2014 07:52 PM, David G Johnston wrote:
> TL;DR - the interaction of ANALYZE and inheritance hierarchies seems to be
> broken for the uncommon use case where the inserts temporarily remain on the
> master table in order to allow RETURNING to work.
Yeah, if I do:
# vacuum verbose public.tablename;
if finish immediately, but if I do:
# vacuum analyze verbose public.tablename;
it takes forever, it seems it analyze every child table.
So for now is just a matter of removing vacuum for the master table and
then doing it manually every night I guess.
Nestor A. Diaz wrote
>> The autovacuum process on version 9.1 keeps vacuuming the master tables
>> and that takes a lot of time considering the master table have no
>> records of its own.
>>
>> The trigger itself insert into the master table, then into the child and
>> then remove the record from the master, we do that way because we need
>> to get the inserted row info.
> If you say so...but the second paragraph makes your conclusion in the first
> paragraph false.
Actually we have trigger like this:
-- Insert Trigger
CREATE TRIGGER insert_tablename_trigger
BEFORE INSERT ON tablename
FOR EACH ROW
EXECUTE PROCEDURE insert_tablename_trigger();
-- Delete Trigger
CREATE TRIGGER delete_tablename_trigger
AFTER INSERT ON tablename
FOR EACH ROW
EXECUTE PROCEDURE delete_tablename_trigger();
-- Insert Function
CREATE OR REPLACE FUNCTION insert_tablename_trigger() RETURNS trigger
AS $BODY$
BEGIN
EXECUTE 'INSERT INTO partitions.' || quote_ident(_table_name) || '
VALUES ($1.*)' USING NEW;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
-- Delete Function
CREATE OR REPLACE FUNCTION delete_tablename_trigger() RETURNS trigger
AS $BODY$
BEGIN
DELETE FROM ONLY public."tablename" WHERE "id" = NEW."id"; -- delete
row again.
RETURN NULL;
END
$BODY$
LANGUAGE plpgsql;
When we insert into the master table, then the row is inserted twice (
in the master and the child ) we return the row inserted at the child
and remove the one inserted at the master, this is how it works.
This is a requirement for most ORM since they usually need an
automatically generated row id column of the table, before that we
didn't set up that way, but in order to keep the application happy we
had do set up that way, or there exist a better approach for this ?
The requirement is simple:
We need partitioning and for every row inserted we need to return the
whole row after an insert, like in: INSERT INTO TABLE ... RETURNING *;
From the docs:
The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily useful
for obtaining values that were supplied by defaults, such as a serial
sequence number. However, any expression using the table's columns is
allowed. The syntax of the RETURNING list is identical to that of the
output list of SELECT.
slds.
--
Nestor A Diaz
From | Date | Subject | |
---|---|---|---|
Next Message | Ted Toth | 2014-11-12 22:45:40 | sepgsql where are the security labels |
Previous Message | Benjamin Scherrey | 2014-11-12 12:02:16 | Using bdr replication with SERIAL pseudo-type. |