Обсуждение: about partitioning

dear all,
i created a master table and a sub table which inherits the main table.
and then i made a trigger and a function that want to keep the master table
empty.
but the trigger didn't work anyway.when i inserted data into the table
"news",both the master table
and the sub table were inserted.
why? i just want the empty master table,any good ideas?
lots of thanks for all.

here r DDls:
-------------------------------------------------------------------------
--master table��
CREATE TABLE "public"."news" (
"id" SERIAL,
"title" VARCHAR(100) NOT NULL,
"content" VARCHAR NOT NULL,
"author" VARCHAR(50) NOT NULL,
"date"   DATE DEFAULT now(),
CONSTRAINT "news_pkey" PRIMARY KEY("id")
)WITHOUT OIDS;

--rule��
CREATE RULE "news_current_partition" AS ON INSERT TO "public"."news"
DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title,
new.content, new.author));

--trigger��
CREATE TRIGGER "news_triggers" BEFORE INSERT
ON "public"."news" FOR EACH ROW
EXECUTE PROCEDURE "public"."deny_insert"();

--function��
CREATE OR REPLACE FUNCTION "public"."deny_insert" () RETURNS trigger AS
$body$
BEGIN
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

--sub table��
CREATE TABLE "public"."news_001" (
CONSTRAINT "news_001_date_check" CHECK ((date >= '2006-03-29'::date) AND
(date < '2006-04-28'::date))
) INHERITS ("public"."news")
WITHOUT OIDS;

CREATE INDEX "news_001_index" ON "public"."news_001"
USING btree ("id");
------------------------------------------------------------------------------------------

On 4/1/06, fufay <fufay@126.com> wrote:
> dear all,
> i created a master table and a sub table which inherits the main table.
> and then i made a trigger and a function that want to keep the master table
> empty.
> but the trigger didn't work anyway.when i inserted data into the table
> "news",both the master table
> and the sub table were inserted.
> why? i just want the empty master table,any good ideas?
> lots of thanks for all.
>
> here r DDls:
> -------------------------------------------------------------------------
> --master table；
> CREATE TABLE "public"."news" (
> "id" SERIAL,
> "title" VARCHAR(100) NOT NULL,
> "content" VARCHAR NOT NULL,
> "author" VARCHAR(50) NOT NULL,
> "date"   DATE DEFAULT now(),
> CONSTRAINT "news_pkey" PRIMARY KEY("id")
> )WITHOUT OIDS;
>
> --rule；
> CREATE RULE "news_current_partition" AS ON INSERT TO "public"."news"
> DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title,
> new.content, new.author));
>
> --trigger；
> CREATE TRIGGER "news_triggers" BEFORE INSERT
> ON "public"."news" FOR EACH ROW
> EXECUTE PROCEDURE "public"."deny_insert"();
>
> --function；
> CREATE OR REPLACE FUNCTION "public"."deny_insert" () RETURNS trigger AS
> $body$
> BEGIN
> RETURN NULL;
> END;
> $body$
> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
>
> --sub table；
> CREATE TABLE "public"."news_001" (
> CONSTRAINT "news_001_date_check" CHECK ((date >= '2006-03-29'::date) AND
> (date < '2006-04-28'::date))
> ) INHERITS ("public"."news")
> WITHOUT OIDS;

Since the fields don't exist in news_001, it has to store them
somewhere - in the table it inherits from.

Inheritence is meant to be used to change something in the
substructure/child table/whatever.

If that object isn't in the child, it has to go back to the parent to
work out what to do (in your case, store the entry).

--
Postgresql & php tutorials
http://www.designmagick.com/

hi chris
as u know i'm a chinese and a freshman to postgres so forgive my poor
english.
child table "news_001" dose inherit the table "news".
for that reason it gets all fields which the master has,and i changed
nothing on it.
when i executed a query like
 "INSERT INTO news(title,content,author) VALUES('just a test','test
too','fufay')"
it stored data both "news" and "news_001" that falls short of my
expectations.
since i did a rule to redirect the "INSERT" to the child and a trigger on
"news"
that "RETURN NULL" when get "INSERT" action,but they didn't work any way.
if we can't restrict to insert data into parent,we'll get two copies at
all --in parent and in child.

best regards
fufay


""chris smith"" <dmagick@gmail.com> д����Ϣ
news:3c1395330603311641q1530e2a3x743d4dc33401fd79@mail.gmail.com...
> On 4/1/06, fufay <fufay@126.com> wrote:
>> dear all,
>> i created a master table and a sub table which inherits the main table.
>> and then i made a trigger and a function that want to keep the master
>> table
>> empty.
>> but the trigger didn't work anyway.when i inserted data into the table
>> "news",both the master table
>> and the sub table were inserted.
>> why? i just want the empty master table,any good ideas?
>> lots of thanks for all.
>>
>> here r DDls:
>> -------------------------------------------------------------------------
>> --master table��
>> CREATE TABLE "public"."news" (
>> "id" SERIAL,
>> "title" VARCHAR(100) NOT NULL,
>> "content" VARCHAR NOT NULL,
>> "author" VARCHAR(50) NOT NULL,
>> "date"   DATE DEFAULT now(),
>> CONSTRAINT "news_pkey" PRIMARY KEY("id")
>> )WITHOUT OIDS;
>>
>> --rule��
>> CREATE RULE "news_current_partition" AS ON INSERT TO "public"."news"
>> DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES
>> (new.title,
>> new.content, new.author));
>>
>> --trigger��
>> CREATE TRIGGER "news_triggers" BEFORE INSERT
>> ON "public"."news" FOR EACH ROW
>> EXECUTE PROCEDURE "public"."deny_insert"();
>>
>> --function��
>> CREATE OR REPLACE FUNCTION "public"."deny_insert" () RETURNS trigger AS
>> $body$
>> BEGIN
>> RETURN NULL;
>> END;
>> $body$
>> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
>>
>> --sub table��
>> CREATE TABLE "public"."news_001" (
>> CONSTRAINT "news_001_date_check" CHECK ((date >= '2006-03-29'::date) AND
>> (date < '2006-04-28'::date))
>> ) INHERITS ("public"."news")
>> WITHOUT OIDS;
>
> Since the fields don't exist in news_001, it has to store them
> somewhere - in the table it inherits from.
>
> Inheritence is meant to be used to change something in the
> substructure/child table/whatever.
>
> If that object isn't in the child, it has to go back to the parent to
> work out what to do (in your case, store the entry).
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>