Обсуждение: Problem after installing triggering function

Поиск
Список
Период
Сортировка

Problem after installing triggering function

От
Yan Cheng Cheok
Дата:
I have a stored procedure execute the following code :

    INSERT INTO unit(fk_lot_id, cycle)
    VALUES(_lotID, _cycle) RETURNING  * INTO _unit;
    raise notice 'AFTER INSERT INTO UNIT,  _unit.unit_id = %', _unit.unit_id ;

unit_id column, is an auto generated primary key. I will always get a non-null value.

However, after I install a trigger function, and create a table named unit_0 inherit from table unit,

NOTICE:  AFTER INSERT INTO UNIT,  _unit.unit_id = <NULL>

will be printed.

The following is the trigger function :

within trigger function, it able to detect unit table (represented by NEW) is having unit_id 28.

why outside trigger function, I will get null?

Thanks!

DECLARE
    unit_table_index bigint;
    low bigint;
    high bigint;
    unit_table_name text;
BEGIN
    unit_table_index = NEW.unit_id >> 20;
    -- 2^20 = 1048576
    low = unit_table_index * 1048576;
    high = low + 1048575;
    unit_table_name = 'unit_' || unit_table_index;

    -- code to dynamically create unit_0, unit_1, unit_2 ...
    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = unit_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
        (
          PRIMARY KEY (unit_id),
          CHECK (unit_id between ' || low || ' and ' || high || '),

          CONSTRAINT fk_lot_id_' || unit_table_index || ' FOREIGN KEY (fk_lot_id)
              REFERENCES lot (lot_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE
        ) INHERITS (unit);';

        EXECUTE 'CREATE INDEX idx_unit_id_' || unit_table_index ||
          ' ON ' || quote_ident(unit_table_name) ||
          ' USING btree
          (unit_id);';

        EXECUTE 'CREATE INDEX idx_fk_lot_id_' || unit_table_index ||
          ' ON ' || quote_ident(unit_table_name) ||
          ' USING btree
          (fk_lot_id);';
    END IF;


    -- NOTICE:  IN unit_insert_trigger, table is unit_0
    -- NOTICE:  IN unit_insert_trigger, NEW.unit_id is 28
    raise notice 'IN unit_insert_trigger, table is %', unit_table_name;
    raise notice 'IN unit_insert_trigger, NEW.unit_id is %', NEW.unit_id;

    EXECUTE 'INSERT INTO ' || quote_ident(unit_table_name) ||
    '(unit_id, fk_lot_id, cycle) VALUES (' ||
    NEW.unit_id || ',' || NEW.fk_lot_id || ',' || NEW.cycle || ')';

    RETURN NULL;
END;


Thanks and Regards
Yan Cheng CHEOK

Re: Problem after installing triggering function

От
Tom Lane
Дата:
Yan Cheng Cheok <yccheok@yahoo.com> writes:
> I have a stored procedure execute the following code :
>     INSERT INTO unit(fk_lot_id, cycle)
>     VALUES(_lotID, _cycle) RETURNING  * INTO _unit;
>     raise notice 'AFTER INSERT INTO UNIT,  _unit.unit_id = %', _unit.unit_id ;

> unit_id column, is an auto generated primary key. I will always get a non-null value.

> However, after I install a trigger function, and create a table named unit_0 inherit from table unit,

> NOTICE:  AFTER INSERT INTO UNIT,  _unit.unit_id = <NULL>

> will be printed.

If you installed it as a BEFORE trigger, the problem is here:

>     RETURN NULL;

That's suppressing the INSERT action.

            regards, tom lane

Re: Problem after installing triggering function

От
Yan Cheng Cheok
Дата:
Thanks and Regards
Yan Cheng CHEOK


--- On Thu, 1/28/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] Problem after installing triggering function
> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Thursday, January 28, 2010, 12:34 AM
> Yan Cheng Cheok <yccheok@yahoo.com>
> writes:
> > I have a stored procedure execute the following code
> :
> >     INSERT INTO unit(fk_lot_id,
> cycle)
> >     VALUES(_lotID, _cycle)
> RETURNING  * INTO _unit;
> >     raise notice 'AFTER INSERT
> INTO UNIT,  _unit.unit_id = %', _unit.unit_id ;
>
> > unit_id column, is an auto generated primary key. I
> will always get a non-null value.
>
> > However, after I install a trigger function, and
> create a table named unit_0 inherit from table unit,
>
> > NOTICE:  AFTER INSERT INTO UNIT, 
> _unit.unit_id = <NULL>
>
> > will be printed.
>
> If you installed it as a BEFORE trigger, the problem is
> here:
>    

You are right. I am inserting BEFORE trigger.

CREATE TRIGGER insert_unit_trigger
    BEFORE INSERT ON unit
    FOR EACH ROW EXECUTE PROCEDURE unit_insert_trigger();


> >     RETURN NULL;
>
> That's suppressing the INSERT action.
>
>

But... I am not implementing table partition. I want to ensure my parent table "unit" is empty, and "unit_0" is being
filled.But, all my query can be performed through parent table "unit". 

I am referring to http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/

        
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Problem after installing triggering function

От
Yan Cheng Cheok
Дата:
Sorry. Some correction.

Change

But... I am not implementing table partition

to

But... I am *now* implementing table partition

Thanks and Regards
Yan Cheng CHEOK


--- On Thu, 1/28/10, Yan Cheng Cheok <yccheok@yahoo.com> wrote:

> From: Yan Cheng Cheok <yccheok@yahoo.com>
> Subject: Re: [GENERAL] Problem after installing triggering function
> To: "Tom Lane" <tgl@sss.pgh.pa.us>
> Cc: pgsql-general@postgresql.org
> Date: Thursday, January 28, 2010, 8:53 AM
>
> Thanks and Regards
> Yan Cheng CHEOK
>
>
> --- On Thu, 1/28/10, Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
>
> > From: Tom Lane <tgl@sss.pgh.pa.us>
> > Subject: Re: [GENERAL] Problem after installing
> triggering function
> > To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> > Cc: pgsql-general@postgresql.org
> > Date: Thursday, January 28, 2010, 12:34 AM
> > Yan Cheng Cheok <yccheok@yahoo.com>
> > writes:
> > > I have a stored procedure execute the following
> code
> > :
> > >     INSERT INTO unit(fk_lot_id,
> > cycle)
> > >     VALUES(_lotID, _cycle)
> > RETURNING  * INTO _unit;
> > >     raise notice 'AFTER INSERT
> > INTO UNIT,  _unit.unit_id = %', _unit.unit_id ;
> >
> > > unit_id column, is an auto generated primary key.
> I
> > will always get a non-null value.
> >
> > > However, after I install a trigger function, and
> > create a table named unit_0 inherit from table unit,
> >
> > > NOTICE:  AFTER INSERT INTO UNIT, 
> > _unit.unit_id = <NULL>
> >
> > > will be printed.
> >
> > If you installed it as a BEFORE trigger, the problem
> is
> > here:
> >    
>
> You are right. I am inserting BEFORE trigger.
>
> CREATE TRIGGER insert_unit_trigger
>     BEFORE INSERT ON unit
>     FOR EACH ROW EXECUTE PROCEDURE
> unit_insert_trigger();
>
>
> > >     RETURN NULL;
> >
> > That's suppressing the INSERT action.
> >
> >
>
> But... I am not implementing table partition. I want to
> ensure my parent table "unit" is empty, and "unit_0" is
> being filled. But, all my query can be performed through
> parent table "unit".
>
> I am referring to http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/
>
>         
> > regards, tom lane
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
>
>

Re: Problem after installing triggering function

От
Yan Cheng Cheok
Дата:
To reproduce the problem, here is some simple steps to follow :

(1) create database named "tutorial"

(2) perform the following SQL query :

CREATE TABLE impressions_by_day (
    advertiser_id SERIAL NOT NULL,
    day DATE NOT NULL DEFAULT CURRENT_DATE,
    impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, day)
);

CREATE OR REPLACE FUNCTION insert_table()
  RETURNS void AS
$BODY$DECLARE
    _impressions_by_day impressions_by_day;
BEGIN
    INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING  * INTO _impressions_by_day;

    RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;

(3) create database named "tutorial_partition"

(4)  perform the following SQL query :

CREATE TABLE impressions_by_day (
    advertiser_id SERIAL NOT NULL,
    day DATE NOT NULL DEFAULT CURRENT_DATE,
    impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, day)
);

CREATE OR REPLACE FUNCTION insert_table()
  RETURNS void AS
$BODY$DECLARE
    _impressions_by_day impressions_by_day;
BEGIN
    INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING  * INTO _impressions_by_day;

    RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;

CREATE TABLE impressions_by_day_y2010m1ms2 (
    PRIMARY KEY (advertiser_id, day),
    CHECK ( day >= DATE '2010-01-01' AND day < DATE '2010-03-01' )
) INHERITS (impressions_by_day);


CREATE INDEX impressions_by_day_y2010m1ms2_index ON impressions_by_day_y2010m1ms2 (day);


CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.day >= DATE '2010-01-01' AND NEW.day < DATE '2010-03-01' ) THEN
        INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Something wrong with the impressions_by_day_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_impressions_by_day_trigger
    BEFORE INSERT ON impressions_by_day
    FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();

(5) execute

SELECT * FROM insert_table() on tutorial

We get

NOTICE:  After insert, the returned advertiser_id is 1

(6) execute

SELECT * FROM insert_table() on tutorial_partition

We get

NOTICE:  After insert, the returned advertiser_id is <NULL>



How is it possible to get advertiser_id is 1 too, in tutorial_partition?

Thanks!
Cheok

Re: Problem after installing triggering function

От
Alban Hertroys
Дата:
On 29 Jan 2010, at 2:06, Yan Cheng Cheok wrote:

> CREATE OR REPLACE FUNCTION insert_table()
>  RETURNS void AS
> $BODY$DECLARE
>    _impressions_by_day impressions_by_day;
> BEGIN
>    INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING  * INTO _impressions_by_day;
>
>    RAISE NOTICE 'After insert, the returned advertiser_id is %', _impressions_by_day.advertiser_id;
> END;$BODY$
>  LANGUAGE 'plpgsql' VOLATILE;

...

> CREATE TABLE impressions_by_day_y2010m1ms2 (
>     PRIMARY KEY (advertiser_id, day),
>     CHECK ( day >= DATE '2010-01-01' AND day < DATE '2010-03-01' )
> ) INHERITS (impressions_by_day);

...

> CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     IF ( NEW.day >= DATE '2010-01-01' AND NEW.day < DATE '2010-03-01' ) THEN
>         INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
>     ELSE
>         RAISE EXCEPTION 'Date out of range.  Something wrong with the impressions_by_day_insert_trigger() function!';
>     END IF;
>     RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER insert_impressions_by_day_trigger
>     BEFORE INSERT ON impressions_by_day
>     FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();

...

> (6) execute
>
> SELECT * FROM insert_table() on tutorial_partition
>
> We get
>
> NOTICE:  After insert, the returned advertiser_id is <NULL>
>
>
>
> How is it possible to get advertiser_id is 1 too, in tutorial_partition?


You didn't actually insert any data in the table you're querying as you return NULL in the BEFORE INSERT trigger, so of
courseyou get NULL back for results. 
If you want to get the row back that got inserted into the child table as a side effect then you will have to query the
childtable. 

The case you're showing here obviously doesn't have any purpose other then to show what's going on, so it's hard to
advisehow to work around this problem. You could probably solve your situation by creating a trigger on each child
table,it depends on what needs to be done. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b62cac310751585411885!