Re: Need your help on using "partion" - Mailing list pgsql-general

My guess is that when you insert into Cities, the sequence field is
incremented one time (=2), and when the Rule is executed to insert
into Capital, the sequence is incremented a second time (=3).  As a
result of these 2 operations, the sequence is incremented 2 times.

I don't know anything about your application or business rules, but
your solution seems unnecessarily complicated.  Remember, the best and
smartest approach is usually the simplest one.  You can do this in a
much simpler way.

On 4/4/07, Huynh Ngoc Doan <hndoan@ghp-fareast.com.vn> wrote:
>
> /*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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>