postgresql - Docker 上的 Postgres - 超出堆栈深度限制
问题描述
我在本地 Docker 上安装了 Postgres(最新版本):
码头工人撰写:
version: '3.1'
services:
postgres:
image: postgres
restart: always
ports:
- 5435:5432
environment:
POSTGRES_PASSWORD: xxxxxxx
volumes:
- pgdata:/var/lib/postgresql/data
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
command:
- "postgres"
- "-c"
- "max_stack_depth=7680"
volumes:
pgdata:
networks:
network1:
我的桌子:
create TABLE dictionary.language (
id bigserial NOT NULL PRIMARY KEY,
lang_name text NOT NULL
);
create TABLE dictionary.level (
id bigserial NOT NULL PRIMARY KEY,
level smallint NOT NULL
);
create TABLE dictionary.word (
id bigserial NOT NULL PRIMARY KEY,
name text,
translation text,
language_abbr_id bigint references language(id),
level_id smallint references level(id),
last_reviewed TIMESTAMP not null DEFAULT clock_timestamp()
);
create TABLE dictionary.examples (
id bigserial NOT NULL PRIMARY KEY,
sentence text NOT NULL,
translation text NOT NULL,
word_id bigint references word(id)
);
create TABLE dictionary.user (
id bigserial NOT NULL PRIMARY KEY,
username text NOT NULL,
password text NOT NULL
);
CREATE OR REPLACE FUNCTION dictionary.set_last_reviewed()
RETURNS trigger as
$$
BEGIN
INSERT INTO dictionary.word(last_reviewed)
VALUES(now());
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
create trigger updateLastReviewedAfterUpdate
before update on dictionary.word
for each row
execute procedure dictionary.set_last_reviewed();
create trigger updateLastReviewedAfterInsert
before insert on dictionary.word
for each row
execute procedure dictionary.set_last_reviewed();
我想在我的表中插入一些东西:
insert into word(id,name,translation,language_abbr_id,level,last_reviewed) values(1,'fabulous', 'bajeczny, fantastyczny' , 1 , 6 , '11/09/2018 23:58' );
然后我得到错误:
查询执行失败
原因:SQL 错误 [54001]: ERROR: stack depth limit exceeded 提示:在确保平台的堆栈深度限制足够之后,增加配置参数“max_stack_depth”(当前为 7680kB)。其中:SQL 语句“INSERT INTO dictionary.word(last_reviewed)
VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO
字典.word(last_reviewed)
VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO
字典.word(last_reviewed)
VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO
字典.word(last_reviewed)
VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO
字典.word(last_reviewed)
VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement ...
它可能与我的触发器有关吗?我不知道如何解决我的问题
解决方案
您的插入触发器向表word
中插入一个新行,这反过来又触发插入触发器,它插入一个新行来触发触发器......等等。
据我所知,您只是想为该列设置一个默认值。
您可以通过更改触发器处理的记录来执行此操作,而不是通过在表中插入一个全新的行:
只需将触发器更改为:
CREATE OR REPLACE FUNCTION dictionary.set_last_reviewed()
RETURNS trigger as
$$
BEGIN
new.last_reviewed := now();
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
推荐阅读
- python-2.7 - 访问名称中包含点的 Python 字典元素
- c# - 使用 Ajax 请求验证表单
- c - 向服务器发送 WebSockets 消息
- android - 如何在Android中实现从左到右的动画
- javascript - 通过 innerHTML 工作但不加载将脚本插入到 div
- javascript - ReactJS defaultProps 空函数声明
- elixir - 用于提供重复条目的字符串的自定义自动递增字段
- sorting - 如何在 Ansible 中对版本号进行排序
- sql - 将每行的最近日期加入表,然后加权平均
- amazon-web-services - AWS 帐户已终止,仍在访问托管在 ec2 机器上的站点?