Обсуждение: Partitioning existing table issue - Help needed!

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



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/

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)