Thread: Partitioning existing table issue - Help needed!
Dear All,
I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
The below steps which I am using are :
- created parent table
- create sub-partitioned table
- created index for each partition
- created function
- creating trigger but end with below error:-
SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table
Detail: Partitioned tables cannot have ROW triggers.
Please let me know what is the issue with this?
Kindly advise.
Regards,
AShok
On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote: > > Dear All, > > I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13. > > > > The below steps which I am using are : > > > > created parent table > create sub-partitioned table > created index for each partition > created function > creating trigger but end with below error:- > > SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table > > Detail: Partitioned tables cannot have ROW triggers. > > > > Please let me know what is the issue with this? The ability to add row triggers on partitioned tables was added in PostgreSQL 11, see the release notes at https://www.postgresql.org/docs/11/release-11.html. In v10 you have to create the triggers individually for each partition. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
SQL Error [54001]: ERROR: stack depth limit exceeded
Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
<dbatocloud17@gmail.com> wrote:
>
> Dear All,
>
> I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
>
>
>
> The below steps which I am using are :
>
>
>
> created parent table
> create sub-partitioned table
> created index for each partition
> created function
> creating trigger but end with below error:-
>
> SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table
>
> Detail: Partitioned tables cannot have ROW triggers.
>
>
>
> Please let me know what is the issue with this?
The ability to add row triggers on partitioned tables was added in
PostgreSQL 11, see the release notes at
https://www.postgresql.org/docs/11/release-11.html.
In v10 you have to create the triggers individually for each partition.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
Thanks Magnus,I was able to create trigger for each partition now successfully.Now I tried to insert records from the old tableto newly created partition table but I am receiving the below error.Comand # insert into core.contact_transaction_history_Parent_PART_BKP_AM select * from core.contact_transaction_history;
SQL Error [54001]: ERROR: stack depth limit exceeded
Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander <magnus@hagander.net> wrote:On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
<dbatocloud17@gmail.com> wrote:
>
> Dear All,
>
> I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
>
>
>
> The below steps which I am using are :
>
>
>
> created parent table
> create sub-partitioned table
> created index for each partition
> created function
> creating trigger but end with below error:-
>
> SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table
>
> Detail: Partitioned tables cannot have ROW triggers.
>
>
>
> Please let me know what is the issue with this?
The ability to add row triggers on partitioned tables was added in
PostgreSQL 11, see the release notes at
https://www.postgresql.org/docs/11/release-11.html.
In v10 you have to create the triggers individually for each partition.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
Dear All,I want to increase this value in postgreSQL but it is not allowing do it .alter system set max_stack_depth='12288kb';SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM command
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Fri, Feb 5, 2021 at 5:34 PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote:Thanks Magnus,I was able to create trigger for each partition now successfully.Now I tried to insert records from the old tableto newly created partition table but I am receiving the below error.Comand # insert into core.contact_transaction_history_Parent_PART_BKP_AM select * from core.contact_transaction_history;
SQL Error [54001]: ERROR: stack depth limit exceeded
Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander <magnus@hagander.net> wrote:On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
<dbatocloud17@gmail.com> wrote:
>
> Dear All,
>
> I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
>
>
>
> The below steps which I am using are :
>
>
>
> created parent table
> create sub-partitioned table
> created index for each partition
> created function
> creating trigger but end with below error:-
>
> SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table
>
> Detail: Partitioned tables cannot have ROW triggers.
>
>
>
> Please let me know what is the issue with this?
The ability to add row triggers on partitioned tables was added in
PostgreSQL 11, see the release notes at
https://www.postgresql.org/docs/11/release-11.html.
In v10 you have to create the triggers individually for each partition.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
You must be or become superuser in order to use this command. Check which roles are superuser using \du in psql. Maybe you can switch to a superuser role using
SET ROLE to abcdef;
if abcdef is a superuser role.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
On Feb 5, 2021, at 8:08 AM, Holger Jakobs <holger@jakobs.com> wrote:
Am 05.02.21 um 14:02 schrieb dbatoCloud Solution:Dear All,I want to increase this value in postgreSQL but it is not allowing do it .alter system set max_stack_depth='12288kb';SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM command
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Fri, Feb 5, 2021 at 5:34 PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote:Thanks Magnus,I was able to create trigger for each partition now successfully.Now I tried to insert records from the old tableto newly created partition table but I am receiving the below error.Comand # insert into core.contact_transaction_history_Parent_PART_BKP_AM select * from core.contact_transaction_history;
SQL Error [54001]: ERROR: stack depth limit exceeded
Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander <magnus@hagander.net> wrote:On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
<dbatocloud17@gmail.com> wrote:
>
> Dear All,
>
> I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
>
>
>
> The below steps which I am using are :
>
>
>
> created parent table
> create sub-partitioned table
> created index for each partition
> created function
> creating trigger but end with below error:-
>
> SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table
>
> Detail: Partitioned tables cannot have ROW triggers.
>
>
>
> Please let me know what is the issue with this?
The ability to add row triggers on partitioned tables was added in
PostgreSQL 11, see the release notes at
https://www.postgresql.org/docs/11/release-11.html.
In v10 you have to create the triggers individually for each partition.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/You must be or become superuser in order to use this command. Check which roles are superuser using \du in psql. Maybe you can switch to a superuser role using
SET ROLE to abcdef;if abcdef is a superuser role.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
(please avoid top-posting, it makes the thread really hard to read) On Fri, Feb 5, 2021 at 1:04 PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote: > > Thanks Magnus, > > I was able to create trigger for each partition now successfully. > > Now I tried to insert records from the old tableto newly created partition table but I am receiving the below error. > Comand # insert into core.contact_transaction_history_Parent_PART_BKP_AM select * from core.contact_transaction_history; > > SQL Error [54001]: ERROR: stack depth limit exceeded > Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stackdepth limit is adequate. > Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)" > PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement > SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)" > PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement > SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)" > PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement > SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)" > PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement > SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)" > PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement > SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)" > PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement This looks a lot like you may have created a trigger that actually fires recursively. What does your trigger actually do that would require that much stack? Can you post the code of the trigger? -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
My Apologies!
The below trigger which I created for after partitioning for each partitions
CREATE trigger insert_contact_transaction_history_P_PART_BKP_Y2017_AM
BEFORE INSERT ON core.contact_transaction_history_P_PART_BKP_Y2017_AM
FOR EACH ROW EXECUTE PROCEDURE core.contact_transaction_history_Parent_PART_BKP_AM_();
CREATE trigger insert_contact_transaction_history_P_PART_BKP_Y2025_AM
BEFORE INSERT ON core.contact_transaction_history_P_PART_BKP_Y2025_AM
FOR EACH ROW EXECUTE PROCEDURE core.contact_transaction_history_Parent_PART_BKP_AM_();
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
(please avoid top-posting, it makes the thread really hard to read)
On Fri, Feb 5, 2021 at 1:04 PM dbatoCloud Solution
<dbatocloud17@gmail.com> wrote:
>
> Thanks Magnus,
>
> I was able to create trigger for each partition now successfully.
>
> Now I tried to insert records from the old tableto newly created partition table but I am receiving the below error.
> Comand # insert into core.contact_transaction_history_Parent_PART_BKP_AM select * from core.contact_transaction_history;
>
> SQL Error [54001]: ERROR: stack depth limit exceeded
> Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
> Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
> SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
> SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
> SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
> SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
> SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
> PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
This looks a lot like you may have created a trigger that actually
fires recursively. What does your trigger actually do that would
require that much stack? Can you post the code of the trigger?
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
Holler is correct, do you have the rds_superuser credentials? Or can you do as Holger specifies?Sent from my iPadOn Feb 5, 2021, at 8:08 AM, Holger Jakobs <holger@jakobs.com> wrote:
Am 05.02.21 um 14:02 schrieb dbatoCloud Solution:Dear All,I want to increase this value in postgreSQL but it is not allowing do it .alter system set max_stack_depth='12288kb';SQL Error [42501]: ERROR: must be superuser to execute ALTER SYSTEM command
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Fri, Feb 5, 2021 at 5:34 PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote:Thanks Magnus,I was able to create trigger for each partition now successfully.Now I tried to insert records from the old tableto newly created partition table but I am receiving the below error.Comand # insert into core.contact_transaction_history_Parent_PART_BKP_AM select * from core.contact_transaction_history;
SQL Error [54001]: ERROR: stack depth limit exceeded
Hint: Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform's stack depth limit is adequate.
Where: SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
SQL statement "INSERT INTO core.contact_transaction_history_P_PART_BKP_Y2020_AM VALUES (NEW.*)"
PL/pgSQL function core.contact_transaction_history_parent_part_bkp_am_() line 14 at SQL statement
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
Dubai , UAE | BLR , INDIA
M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
On Thu, Feb 4, 2021 at 7:40 PM Magnus Hagander <magnus@hagander.net> wrote:On Thu, Feb 4, 2021 at 2:47 PM dbatoCloud Solution
<dbatocloud17@gmail.com> wrote:
>
> Dear All,
>
> I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13.
>
>
>
> The below steps which I am using are :
>
>
>
> created parent table
> create sub-partitioned table
> created index for each partition
> created function
> creating trigger but end with below error:-
>
> SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table
>
> Detail: Partitioned tables cannot have ROW triggers.
>
>
>
> Please let me know what is the issue with this?
The ability to add row triggers on partitioned tables was added in
PostgreSQL 11, see the release notes at
https://www.postgresql.org/docs/11/release-11.html.
In v10 you have to create the triggers individually for each partition.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/You must be or become superuser in order to use this command. Check which roles are superuser using \du in psql. Maybe you can switch to a superuser role using
SET ROLE to abcdef;if abcdef is a superuser role.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
dbatoCloud Solution schrieb am 04.02.2021 um 14:47: > Dear All, > > I am partitioning the large table using declarative partitioning method in PostgreSQL 10.13. > > 1. created parent table > 2. create sub-partitioned table > 3. created index for each partition > 4. created function > 5. creating trigger but end with below error:- > > SQL Error [42809]: ERROR: "contact_transaction_history_parent_part_bkp_am" is a partitioned table > > Detail: Partitioned tables cannot have ROW triggers. What do you need the trigger for? And what does the trigger do? If you insert into the parent table, Postgres will automatically store the row in the appropriate partition. Apart from the need to upgrade because row triggers on a partitioned table require Postgres 11, you might want to consider upgrading to at least Postgres 12 anyway, as there were substantial partitioning improvements in 11 and 12 (performance and usability wise)