postgresql - 慢触发器使用 postgresql 检查主键约束

Question

我正在尝试使用 plpythonu 触发器 (check_pk) 检查 postgresql 中沿继承树的主键约束。我正在使用 (PostgreSQL) 9.4.5。

我的问题是为什么插入或更新至少需要 50 毫秒，而触发器本身的执行最多需要 5 毫秒？难道我做错了什么？有没有办法改善这一点？

代码

( git@github.com:collorg/oopg.git )

表格:

我正在测试三个表 parent、childb 和 childc(请参阅下面的触发器):

create table parent(
   a text primary key
);

create trigger check_pk
    before insert or update on parent
    for each row execute procedure check_pk();

create table childb(
   b text,
   primary key(a, b)
) inherits(parent);

create trigger check_pk
    before insert or update on childb
    for each row execute procedure check_pk();

create table childc(
   c text,
   primary key(a, c)
) inherits(parent);

create trigger check_pk
    before insert or update on childc
    for each row execute procedure check_pk();

测试:

insert into parent (a) values ('a')         -- OK
insert into childb (a, b) values ('a', 'a') -- FAILS
insert into childb (a, b) values ('b', 'a') -- OK
insert into parent (a) values ('b')         -- FAILS
insert into parent (a) values ('b')         -- FAILS
insert into childc (a, c) values ('b', 'a') -- FAILS
insert into childc (a, c) values ('c', 'a') -- OK
select * from parent                        -- a, b, c
update parent set a = 'b'                   -- FAILS
update childb set a = 'c'                   -- FAILS
update childb set a = 'd'                   -- OK

这是 postgresql 日志的摘录(我在 postgresql.conf 中将 log_min_duration_statement 设置为 10 毫秒):

======== get_pk_fields(59959)
check_pk_oid: SELECT a FROM public.parent WHERE a = 'c' limit 1
CLEF DUPLIQUEE
check_pk_oid duration: 0:00:00.003948
check_pk duration: 0:00:00.004504
2015-12-10 08:53:16 CET LOG:  durée : 71.940 ms, instruction : update parent set a = 'c'

当 check_pk 触发器的执行需要 4.5 毫秒时，更新需要 71.940 毫秒。

触发器:

create language plpythonu;

--
--
--

CREATE FUNCTION check_pk()
    RETURNS trigger
AS $$
from datetime import datetime
from sys import stderr

begin = datetime.now()
oid = TD['relid']
GD['td'] = TD
ok = plpy.execute(
    "SELECT check_pk_oid({})".format(oid))[0]['check_pk_oid']
stderr.write("check_pk duration: {}\n".format(datetime.now() - begin))
if not ok:
    return 'SKIP'
$$ LANGUAGE plpythonu;

--
--
--

CREATE FUNCTION check_pk_oid(integer)
  returns boolean
AS $$
"""Return False if the key is found in any of the parents."""
from datetime import datetime
from sys import stderr
from psycopg2.extensions import adapt

begin = datetime.now()
oid = args[0]
stderr.write("{} check_pk_oid({})\n".format(8*'=', oid))
TD = GD['td']
stderr.write("GD['td'] = {}\n".format(TD))
parent_oid = plpy.execute(
    "SELECT get_inhparent('{}')".format(oid))[0]['get_inhparent']
stderr.write("oid du parent {}\n".format(parent_oid))
if parent_oid:
   # recurse on parent_oid
   query = ("SELECT check_pk_oid({})".format(parent_oid))
   stderr.write("check uid request: {}\n".format(query))
   return plpy.execute(query)[0]['check_pk_oid']
# Get the FQTN and the field names of the primary key
pk_infos = plpy.execute(
   "SELECT get_pk_fields({})".format(oid))[0]['get_pk_fields']
fqtn, pk_fieldnames = pk_infos[0], pk_infos[1:]
if not pk_fieldnames:
   stderr.write(
       "check_pk_oid duration ok 1: {}\n".format(datetime.now() - begin))
   return True

# Clause for the SELECT request
fields = []
clause = []
for field in pk_fieldnames:
   fields.append(field)
   if TD['new'][field] == 0:
     valeur = 0
   else:
     valeur = TD['new'][field] or ""
     valeur = adapt(valeur)
   clause.append("{} = {}".format(field, str(valeur)))

# construction de la requête d''extraction
req = "SELECT {} FROM {} WHERE {} limit 1".format(
    ', '.join(fields), fqtn, ' and '.join(clause))
stderr.write("check_pk_oid: {}\n".format(req))
if len(plpy.execute(req)) == 1:
    stderr.write("CLEF DUPLIQUEE\n")
    stderr.write("check_pk_oid duration: {}\n".format(datetime.now() - begin))
    return False

stderr.write("check_pk_oid duration ok 2: {}\n".format(datetime.now() - begin))
return True
$$ LANGUAGE plpythonu;

--
--
--

CREATE FUNCTION get_inhparent(integer)
    RETURNS integer
AS $$
from sys import stderr
relid = args[0]
stderr.write("{} get_inhparent({})\n".format(8*'=', relid))
query = (
    "SELECT inhparent FROM pg_catalog.pg_inherits WHERE inhrelid = {}".format(
    relid))
stderr.write('get_inhparent: {}\n'.format(query))
rec = plpy.execute(query)
try:
   return rec[0]['inhparent']
except:
   return 0
$$ LANGUAGE plpythonu;

--
--
--

CREATE FUNCTION get_pk_fields(oid)
    RETURNS varchar[]
AS $$
"""
Return the field names in the primary key
"""
from sys import stderr
oid = args[0]
stderr.write("{} get_pk_fields({})\n".format(8*'=', oid))
# rec_st : record contenant schemaname et relname
rec_st = plpy.execute(
   """SELECT schemaname, relname 
      FROM pg_catalog.pg_stat_all_tables
      WHERE relid = {}""".format(oid))
schemaname = rec_st[0]['schemaname']
relname = rec_st[0]['relname']
l_fieldnames = plpy.execute(
   """
SELECT
    a.attrelid AS tableid,
    c.relkind AS tablekind,
    n.nspname::varchar AS schemaname,
    c.relname::varchar AS relationname,
    array_agg(distinct i.inhparent) as parent,
    array_agg(a.attname::varchar) AS fieldnames,
    array_agg(a.attnum) as attnums,
    array_agg(a.attislocal) AS local,
    cn_pk.contype AS pkey
FROM
    pg_class c -- table
    LEFT JOIN pg_namespace n ON
    c.relname = '{}' and
    n.oid = c.relnamespace and
    n.nspname = '{}'
    LEFT JOIN pg_inherits i ON
    i.inhrelid = c.oid
    LEFT JOIN pg_attribute a ON
    a.attrelid = c.oid
    JOIN pg_type pt ON
    a.atttypid = pt.oid
--    LEFT JOIN pg_constraint cn_uniq ON
--    cn_uniq.contype = 'u' AND
--    cn_uniq.conrelid = a.attrelid AND
--    a.attnum = ANY( cn_uniq.conkey )
    JOIN pg_constraint cn_pk ON
    cn_pk.contype = 'p' AND
    cn_pk.conrelid = a.attrelid AND
    a.attnum = ANY( cn_pk.conkey )
WHERE
    n.nspname <> 'pg_catalog'::name AND
    n.nspname <> 'information_schema'::name AND
    ( c.relkind = 'r'::"char" )
GROUP BY
    a.attrelid,
    c.relkind,
    n.nspname,
    c.relname,
    cn_pk.contype""".format(relname, schemaname))[0]['fieldnames']
fqtn = "{}.{}".format(schemaname, relname)
return [fqtn] + l_fieldnames
fieldnames = ','.join(l_fieldnames)
resultat = fqtn + ":" + fieldnames
stderr.write("{}\n".format(resultat))
return resultat
$$ LANGUAGE plpythonu;

Answer 1

我得到了答案。 plpython 非常适合原型(prototype)制作，但它需要付出代价。如果我使用这个基本上什么都不做的触发器，插入时间大约为 30 毫秒......所以我想如果我想要更好的性能，我必须用 C 编写代码。

create language plpythonu;

CREATE OR REPLACE FUNCTION slow()
    RETURNS trigger
AS $$
pass
$$ LANGUAGE plpythonu;

注意:此处发布的代码已过时且存在错误(没有多重继承，...)但它在 https://github.com/collorg/oopg 上继续发展.