Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #123 > unrolled thread

Review question

Back to article view | Back to comp.databases.postgresql

Contents

  Review question Mladen Gogala <no@email.here.invalid> - 2011-05-16 17:19 +0000
    Re: Review question "Laurenz Albe" <invite@spam.to.invalid> - 2011-05-17 10:34 +0200
      Re: Review question Mladen Gogala <gogala.mladen@gmail.com> - 2011-05-17 12:19 +0000
      Re: Review question Mladen Gogala <no@email.here.invalid> - 2011-05-18 18:19 +0000

#123 — Review question

I have a partitioned table and the insert statements find the right 
partition through the following trigger function:

CREATE OR REPLACE FUNCTION moreover.moreover_insert_trgfn()
  RETURNS trigger AS
$BODY$
BEGIN
    IF ( NEW.created_at >= TIMESTAMP '2011-04-01 00:00:00' AND
            NEW.created_at < TIMESTAMP '2011-05-01 00:00:00' ) THEN
        INSERT INTO moreover.moreover_documents_y2011m04 VALUES (NEW.*);
    ELSIF ( NEW.created_at >= TIMESTAMP '2011-05-01 00:00:00' AND
            NEW.created_at < TIMESTAMP '2011-06-01 00:00:00' ) THEN
        INSERT INTO moreover.moreover_documents_y2011m05 VALUES (NEW.*);
    ELSIF ( NEW.created_at >= TIMESTAMP '2011-06-01 00:00:00' AND
            NEW.created_at < TIMESTAMP '2011-07-01 00:00:00' ) THEN
        INSERT INTO moreover.moreover_documents_y2011m06 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range. 
                         Fix the moreover_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

The only problem with that function is that I have to change it from time 
to time. Being lazy bum that I am, I came up with the following trigger 
function on my test DB:

CREATE OR REPLACE FUNCTION moreover.moreover_insert_trgfn()
  RETURNS trigger AS
$BODY$
DECLARE
V_MONTH SMALLINT;
V_YEAR  VARCHAR(4);
V_INS   VARCHAR(512):='insert into moreover.moreover_documents_y';
V_VALS  VARCHAR(128):=' VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,
                               $13,$14,$15,$16,$17,$18)';
BEGIN
    SELECT EXTRACT(MONTH FROM NEW.created_at)  INTO V_MONTH;
    SELECT EXTRACT(YEAR  FROM NEW.created_at)  INTO V_YEAR;
    IF (V_MONTH>=10) THEN 
        V_INS:=V_INS||V_YEAR||'m'||V_MONTH||V_VALS;
    ELSE 
        V_INS:=V_INS||V_YEAR||'m0'||V_MONTH||V_VALS;
    END IF;
    execute V_INS USING NEW.document_id,
                        NEW.dre_reference,
                        NEW.headline,
                        NEW.author,
                        NEW.url,
                        NEW.rank,
                        NEW.content,
                        NEW.stories_like_this,
                        NEW.internet_web_site_id,
                        NEW.harvest_time,
                        NEW.valid_time,
                        NEW.keyword,
                        NEW.article_id,
                        NEW.media_type,
                        NEW.source_type,
                        NEW.created_at,
                        NEW.autonomy_fed_at,
                        NEW.language;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

The function tests perfectly but it is a bit more complex. It should 
route approximately 1 million records every day to the right partition. 
Any thoughts or words of caution?

-- 
http://mgogala.byethost5.com

[toc] | [next] | [standalone]

#124

Mladen Gogala wrote:
> I have a partitioned table and the insert statements find the right
> partition through the following trigger function:
>
[static SQL]
>
> The only problem with that function is that I have to change it from time
> to time. Being lazy bum that I am, I came up with the following trigger
> function on my test DB:
>
> CREATE OR REPLACE FUNCTION moreover.moreover_insert_trgfn()
>  RETURNS trigger AS
> $BODY$
> DECLARE
> V_MONTH SMALLINT;
> V_YEAR  VARCHAR(4);
> V_INS   VARCHAR(512):='insert into moreover.moreover_documents_y';
> V_VALS  VARCHAR(128):=' VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,
>                               $13,$14,$15,$16,$17,$18)';
> BEGIN
>    SELECT EXTRACT(MONTH FROM NEW.created_at)  INTO V_MONTH;
>    SELECT EXTRACT(YEAR  FROM NEW.created_at)  INTO V_YEAR;
>    IF (V_MONTH>=10) THEN
>        V_INS:=V_INS||V_YEAR||'m'||V_MONTH||V_VALS;
>    ELSE
>        V_INS:=V_INS||V_YEAR||'m0'||V_MONTH||V_VALS;
>    END IF;
>    execute V_INS USING NEW.document_id,
>                        NEW.dre_reference,
>                        NEW.headline,
>                        NEW.author,
>                        NEW.url,
>                        NEW.rank,
>                        NEW.content,
>                        NEW.stories_like_this,
>                        NEW.internet_web_site_id,
>                        NEW.harvest_time,
>                        NEW.valid_time,
>                        NEW.keyword,
>                        NEW.article_id,
>                        NEW.media_type,
>                        NEW.source_type,
>                        NEW.created_at,
>                        NEW.autonomy_fed_at,
>                        NEW.language;
>    RETURN NULL;
> END;
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100;
>
> The function tests perfectly but it is a bit more complex. It should
> route approximately 1 million records every day to the right partition.
> Any thoughts or words of caution?

There will be a performance impact because the dynamic statement must be
prepared whenever it is used, but that should not be too bad with a
simple INSERT statement.

Note that you remove one hard-coded dependency on the partitions, but
introduce another one on the column names.

You might get away shorter and more flexibly with something like that:

...
V_VALS  text:=' VALUES($1.*)';
...
execute V_INS USING NEW;
...

I tried it on PostgreSQL 8.4, I don't know if it works as nicely
on older versions.

Yours,
Laurenz Albe 

[toc] | [prev] | [next] | [standalone]

#125

On Tue, 17 May 2011 10:34:29 +0200, Laurenz Albe wrote:


> You might get away shorter and more flexibly with something like that:
> 
> ...
> V_VALS  text:=' VALUES($1.*)';
> ...
> execute V_INS USING NEW;

Thanks, Laurenz. I will try that.



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#126

On Tue, 17 May 2011 10:34:29 +0200, Laurenz Albe wrote:

> You might get away shorter and more flexibly with something like that:
> 
> ...
> V_VALS  text:=' VALUES($1.*)';
> ...
> execute V_INS USING NEW;
> ...

Works as a charm on 9.0.4.



-- 
http://mgogala.freehostia.com

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web