Re: Autovacuum on partitioned tables in version 9.1 - Mailing list 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