Thread: Partitioned tables and triggers

Partitioned tables and triggers

From
mephysto
Date:
Hello to everyone, yesterday I created my first montly partitioned table with
its insert trigger following the specific manual page.

At the moment table seems to work correctly, but I have some doubts:

1 - After an insert operation, I can see data either in master and in child
table. Is this a normal behavior?
2 - I created only insert trigger, but not update and delete. Do I need also
these triggers to managing data correctly?
3 - When a BEFORE INSERT trigger function execute in its turn an INSERT
operation, are the origina INSERT executed or not? Are the data inserted in
master table also, and not only in child table?

Thanks in advance.

Mephysto



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Partitioned-tables-and-triggers-tp5622192p5622192.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Re: Partitioned tables and triggers

From
Andreas Kretschmer
Date:
mephysto <mephystoonhell@gmail.com> wrote:

> Hello to everyone, yesterday I created my first montly partitioned table with
> its insert trigger following the specific manual page.
>
> At the moment table seems to work correctly, but I have some doubts:
>
> 1 - After an insert operation, I can see data either in master and in child
> table. Is this a normal behavior?

Yeah, but you can say 'select ... from ONLY ...'


> 2 - I created only insert trigger, but not update and delete. Do I need also
> these triggers to managing data correctly?

Depends. Is this a insert-only - table, or do you have updates/deletes?


> 3 - When a BEFORE INSERT trigger function execute in its turn an INSERT
> operation, are the origina INSERT executed or not? Are the data inserted in
> master table also, and not only in child table?

Your INSERT-Function should return NULL to avoid insert into the
main-table.




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Partitioned tables and triggers

From
mephysto
Date:
Andreas Kretschmer-2 wrote
>
> Depends. Is this a insert-only - table, or do you have updates/deletes?
>

No, certainly I need UPDATE, and probably DELETE also. Do I need to create
UPDATE and DELETE trigger?



Andreas Kretschmer-2 wrote
>
> Your INSERT-Function should return NULL to avoid insert into the
> main-table.
>
I understood, thank you very much.

Bye.

Mephysto

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Partitioned-tables-and-triggers-tp5622192p5622304.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Re: Partitioned tables and triggers

From
Chetan Suttraway
Date:


On Fri, Apr 6, 2012 at 12:26 PM, mephysto <mephystoonhell@gmail.com> wrote:
Hello to everyone, yesterday I created my first montly partitioned table with
its insert trigger following the specific manual page.

At the moment table seems to work correctly, but I have some doubts:

1 - After an insert operation, I can see data either in master and in child
table. Is this a normal behavior?

WRT data in master table, different people/setup have different strategies.
For ex: in case of oracle, there is no data in master table. All the data comes from partitions.
 
2 - I created only insert trigger, but not update and delete. Do I need also
these triggers to managing data correctly?

Yes.
Note that In case you are allowing to update the key values and it may happen to move to another partition,
then you need to take care of this row movement.


3 - When a BEFORE INSERT trigger function execute in its turn an INSERT
operation, are the origina INSERT executed or not? Are the data inserted in
master table also, and not only in child table?

Thanks in advance.

Regards,
Chetan

--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

EnterpriseDB Blog : http://blogs.enterprisedb.com