Skip site navigation (1) Skip section navigation (2)

Re: Primary Key Increment Doesn't Seem Correct Under Table Partition

From: Yan Cheng Cheok <yccheok(at)yahoo(dot)com>
To: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Primary Key Increment Doesn't Seem Correct Under Table Partition
Date: 2010-01-27 03:22:42
Message-ID: 537352.91797.qm@web65714.mail.ac4.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
Hello all,

I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint is not being carried over to child table.

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS 
$BODY$DECLARE
    measurement_table_index bigint;
    measurement_table_name text;
BEGIN
    -- 20 is just an example here right now. The true value will be 100,000,000
    measurement_table_index = NEW.measurement_id % 20;
    measurement_table_name = 'measurement_' || measurement_table_index;
    
    -- Since measurement_id for parent table is already a bigserial
    -- Do I still need to create index for child's measurement_id?
    
    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = measurement_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(measurement_table_name) || '
        (
          CONSTRAINT pk_measurement_id_' || measurement_table_index || ' PRIMARY KEY (measurement_id),
          CONSTRAINT fk_unit_id_' || measurement_table_index || ' FOREIGN KEY (fk_unit_id)
              REFERENCES unit (unit_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE        
        ) INHERITS (measurement);';
        EXECUTE 'CREATE INDEX ' || quote_ident(measurement_table_name) || '_measurement_id ON ' || quote_ident(measurement_table_name) || '(measurement_id);';      
    END IF;
    
    EXECUTE 'INSERT INTO ' || quote_ident(measurement_table_name) || '(measurement_id, fk_unit_id, v) VALUES (' || NEW.measurement_id || ',' || NEW.fk_unit_id || ',' || quote_literal(NEW.v) || ')';
    RETURN NULL;
END;$BODY$
LANGUAGE plpgsql;


      


Responses

pgsql-general by date

Next:From: Ken WinterDate: 2010-01-27 03:39:27
Subject: Re: Problem with execution of an update rule
Previous:From: Yan Cheng CheokDate: 2010-01-27 03:16:47
Subject: Re: Partitioning on Table with Foreign Key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group