1

I am fairly new to PostgreSQL and would appreciate any help and recommendations.

I'm running the following function to update a table:

$BODY$
  DECLARE
    commercial_period date := (SELECT periods.period FROM common.periods WHERE periods.type = 'commercial');
   --period text := concat('_',right((date_part('year',residential_period))::text,2),'_',right((date_part('year',residential_period))::text,2)::integer+1);
  BEGIN
-------------------------------------------------------------------------------------------------------------
    --copies alternative data from temporal table to common.status
    UPDATE
      common.status
    SET
      implementation_status = A.implementation_status,
      latest_version = A.latest_version
    FROM
      monitoring_commercial_temporal.possible_alternatives AS A
    WHERE
      status.reference = A.reference
      AND (status.update_period = A.update_period
      OR status.update_period IS NULL);
-------------------------------------------------------------------------------------------------------------
    --sets as lastest version any site, not in the temporal list of alternatives
    UPDATE
      common.status
    SET
      latest_version = 'yes'
    WHERE
      (reference, update_period) NOT IN (SELECT reference, update_period FROM monitoring_commercial_temporal.possible_alternatives)
      AND update_period = commercial_period + interval '1 year'
      AND implementation_status <> 'non-preferred alternative'
      AND latest_version IS NULL;
-------------------------------------------------------------------------------------------------------------
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.monitoring_update_alternatives_commercial()
  OWNER TO postgres;

but I get the following error:

ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

> CONTEXT:  SQL function "st_intersects" during inlining SQL statement
> "SELECT (SELECT 
>                                     array_agg(status.reference) 
>                                   FROM 
>                                     common.geometries AS A, 
>                                     common.geometries AS B, 
>                                     common.status,
>                                     common.permission_details 
>                                   WHERE 
>                                     NEW.reference = A.reference 
>                                     AND status.reference = B.reference 
>                                     AND status.reference = permission_details.app
>                                     AND NEW.reference <> status.reference
>                                     AND ST_Intersects(B.geom, A.geom)
>                                     AND (NEW.update_period = residential_period + INTERVAL '1 YEAR' OR NEW.update_period IS NULL)
>                                     AND status.update_period = residential_period + INTERVAL '1 YEAR'
>                                     AND permission_details.monitoring_type <> 'commercial'
>                                     AND (((ST_Area(ST_Intersection(B.geom, A.geom))+0.01) /
> (ST_Area(B.geom)+0.01) >= 0.2)
>                                     OR ((ST_Area(ST_Intersection(B.geom, A.geom))+0.01) /
> (ST_Area(A.geom)+0.01) >= 0.2)))" PL/pgSQL function
> monitoring_update_status_before() line 72 at assignment SQL statement
> "UPDATE 
>     common.status AS A   SET
>     trigger = TRUE   WHERE
>     A.latest_version = NEW.reference
>     AND A.update_period = NEW.update_period" PL/pgSQL function monitoring_update_status_after() line 5 at SQL statement

Then it keeps repeating the last 6 lines from the UPDATE

  • Do you have any triggers on the table status? – a_horse_with_no_name Jul 29 at 9:03
  • Yes, there is an update function for status table: – Shohreh Jul 29 at 9:11
  • And what does that trigger function do? – a_horse_with_no_name Jul 29 at 9:18
  • This is where I get stuck: SQL statement "UPDATE common.status AS A SET trigger = TRUE WHERE A.latest_version = NEW.reference AND A.update_period = NEW.update_period" PL/pgSQL function monitoring_update_status_after() line 5 at SQL statement – Shohreh Jul 29 at 9:22
1

Presumably your trigger firing on UPDATE executes an UPDATE on the same table, which itself fires the triggers, and so on recursively until the stack is full and the stack depth limit exceeded error is raised.

Here's a bare bones example demonstrating the problem:

create table tst(id int primary key, col int, trigger boolean);

create function trg() returns trigger  as $$
 begin update tst set trigger=true where tst.id=NEW.id; return NEW; END
$$ language plpgsql;

create trigger t before update on tst for each row execute procedure trg();

insert into tst(id) values(1),(2);

update tst set col=1 where id=1;
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "update tst set trigger=false where tst.id=NEW.id"
PL/pgSQL function trg() line 1 at SQL statement
SQL statement "update tst set trigger=false where tst.id=NEW.id"

The mistake in the above case is to issue an UPDATE of the table already being updated, inside the trigger. The correct way is to assign directly NEW.trigger, as in:

create function trg() returns trigger  as
$$
 begin NEW.trigger=true; return NEW; END
$$ language plpgsql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.