Need your help on using "partion"

From: Huynh Ngoc Doan <hndoan(at)ghp-fareast(dot)com(dot)vn>
To: pgsql-general(at)postgresql(dot)org
Subject: Need your help on using "partion"
Date: 2007-04-04 09:42:20
Message-ID: 7.0.1.0.1.20070404164154.019eb620@ghp-fareast.com.vn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


/*I'm learning how to use the partion in Postgres 8.2.3 so I want to
do some test for my awareness.
I create 2 tables:
The parent table is cities:*/
CREATE TABLE xxx.cities
(
id serial NOT NULL,
name text,
population real,
altitude integer,
CONSTRAINT pk_cities PRIMARY KEY (id)
)
/*and a partion of cities is capital:*/
CREATE TABLE xxx.capital
(
CONSTRAINT pk_capital PRIMARY KEY (id),
CONSTRAINT capital_id_check CHECK (id > 3)
) INHERITS (xxx.cities)

/*My purpose is that the cities will contain the rows having id <= 3
and the capital will contain the rows having id >=3.
So I create a rule cities_insert_capital to redirect data to the
table capital when new row's id >3
Here it is:
*/
CREATE OR REPLACE RULE cities_insert_capital AS
ON INSERT TO xxx.cities
WHERE new.id > 3 DO INSTEAD INSERT INTO xxx.capital(id,name,
population, altitude) VALUES (new.id,new.name, new.population, new.altitude)

After completing the reparation stage, I insert some rows into cities table:
--restart the serial value to 1
alter sequence xxx.cities_id_seq restart with 1;
--serial is 1 at this time
--1.
INSERT INTO xxx.cities(name, population, altitude)
VALUES('HCM',10,10);
--data is inserted into cities table as the result, serial is 3,and
the expected value is 2 =>what wrong here?
--2.Try more insert
INSERT INTO xxx.cities(name, population, altitude)
VALUES('Ha Noi',10,10);
--data is inserted into capital while it's is expected to insert to cities.
--serial is 5 at this time,and expected value is 3 => what wrong here?

/*Conclusion:
The serial increases by 2 when excuting one insert command.I try many
ways to find out the reason why, but I can't.
Can you help me to explain this unexpected behavior and give me some
advices to deal with this problem.
I want that the serial will increase by 1 unit after a insert comand.
Thank you very much.
Note:
I want id will be add automatically when a row is inserted.
*/
Best regard,
Doan. From VietNam

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Froggy / Froggy Corp. 2007-04-04 11:39:04 Problem with a transaction on dump
Previous Message Tommy Gildseth 2007-04-04 09:34:22 Re: UPDATE on two large datasets is very slow