dezso's questions

在 Azure Synapse 专用 SQL 池中，我有以下设置：

-- a custom DB role to manage privileges
CREATE ROLE [owner];

-- there is a schema owned by this role
CREATE SCHEMA [myschema] AUTHORIZATION [owner];

-- an Azure AD group to allow its members to log in
CREATE USER [radish] FROM EXTERNAL PROVIDER;

-- the AAD group is a member of the owner role
EXEC sp_addrolemember 'owner', 'radish';

-- privileges are assigned exclusively through custom DB roles
GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];

如果我作为 AAD 组的成员登录，radish则无法在以下位置创建表myschema：

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.

一旦我获得 Synapse 管理员角色，就可以了。（当一个成员时，SESSION_USER是dbo。）这并不奇怪，因为这个角色基本上可以做给定工作区的 SQL 池中的所有事情。

但是，当我从 Synapse 管理员角色中删除自己时，会发生以下情况：

DROP TABLE [myschema].[test];
-- completes successfully, but then:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.

可能我对彼此之间的必要特权CREATE TABLE和DROP TABLE相互关联的理解是完全错误的，但直到现在我认为这是两者都需要的相同特权。有人可以告诉我我的想法在哪里错了吗？

到目前为止，我看到了以下可能性：

• 该owner角色不适用于仅间接成为其成员的用户（即通过 AAD 组的成员身份） - 这似乎与https://learn.microsoft.com/en-us/azure/synapse-analytics/security相矛盾/how-to-set-up-access-control#step-8-add-users-to-security-groups
• 拥有足够权限时创建的表由我自己拥有，而不是架构所有者角色。“警告”部分与以下内容相矛盾：“对架构具有 ALTER 权限的用户可以创建架构所有者拥有的过程、同义词和视图。” 此外，sp_tables报告owner为table_owner.

另外，有趣的是，我可以GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];成功地创建表，而创建表失败。

我有一个奇怪的情况，从日志中看到：

Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. 
Process 53807 waits for ExclusiveLock on advisory lock [16421,999999,12864395,2]; blocked by process 37278. 
Process 37278: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24) 
Process 53807: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)",
"See server log for query details.",,,"SQL statement ""SELECT pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer)""

这本身就已经很奇怪了，因为它看起来像是两个进程阻塞了同一个咨询锁，但实际上都没有抓住它。

尝试获取锁的函数如下：

CREATE OR REPLACE FUNCTION zel_event.create_new_partition(
    p_table_name text
)
RETURNS void AS
$BODY$
DECLARE
    _schema text;
BEGIN
    IF NOT EXISTS (table from catalog)
    THEN
        PERFORM pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer);

        IF NOT EXISTS (table from catalog)
        THEN
            EXECUTE format('
                CREATE TABLE %1$I.%2$I (
                    LIKE zel_event.%2$I INCLUDING ALL
                ) INHERITS (zel_event.%2$I)', _schema, p_table_name);
        END IF;
    END IF;
    RETURN;

END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

在我看来，这里没有任何“经典”死锁原因，因为只有一种逻辑......
其背后的想法是按需创建新表，其中需求可以从多个连接以非常高的频率出现。当插入函数执行针对父表的插入时调用它，并由调度程序触发器转移到适当的子表。

这是交易的样子：

INSERT INTO parent (zel_api.insert_event())
        |
     trigger
        |
        +----> child exists? ---no---> CREATE TABLE child
                      |                         |
                     yes                        |
                      |                         |
                      V                         V
              INSERT INTO child (zel_event.create_new_partition())   

PostgreSQL 版本为 9.2

了解这是如何发生的（当然还有减轻），这将是非常有趣的。

我必须收集一些关于 PostgreSQL 角色的信息。一条信息是角色是否具有与“通常”不同的设置，通常意味着这里只有几个参数的默认值（例如log_statement）。

到目前为止，我只有一个想法：遍历所有用户并发出命令

\c postgres username

SHOW log_statement;

然后处理输出。

我想知道是否有类似的方法，例如查询pg_settings视图，显示每个用户的设置？我不需要知道特定于会话的差异，只需要知道那些持续存在的差异。

我想知道关于stackoverflow 问题的答案中提供的解决方案的相对性能，我决定运行一些测试。

OP 希望在给定一组优先级递减的条件的情况下获得第一个匹配行。两种解决方案都涉及一个伪列，但一个（我的）涉及将多个SELECT语句UNION ALL组合在一起，而另一个则构建一个CASE表达式。

我分享我的结果，希望有人会觉得这有用。

我有以下问题。

SELECT * FROM tgvbn();
ERROR:  function tgvbn() does not exist
LINE 1: SELECT * FROM tgvbn();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

由于官方文档的附录 A仅说明了 error_codes 和条件名称，而没有说明实际的消息，因此只能猜测“No function matches...”指的是42883 / undefined_function. 我可以设置log_error_verbosity = verbose-postgresql.conf但这会影响日志，而不是返回给客户端的消息：

控制记录在服务器日志中的每条消息的详细信息量。

好吧，我可以查阅日志以获取这条信息，但是有什么方法可以将它包含在错误消息中吗？

让我们考虑以下示例（从 psql 脚本开始）：

\c :db_to_run_on

TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there

现在，如果它是通过命令运行的

psql [connection details] -v db_to_run_on=\'dev_database\'

然后它就会运行并且用户很高兴。但是，如果他（她）决定指定-v db_to_run_on=production_database呢？（让我们假设这可能发生，就像人们偶尔跑步一样rm -rf / # don't try this at home!!!。）希望该表有一个新的备份......

那么问题来了：如何检查传递给脚本的变量并根据它们的值停止进一步处理？

我已经考虑这个问题很久了。

基本问题是：如何对存储过程进行单元测试？

我看到我可以相对容易地为经典意义上的函数设置单元测试（我的意思是它们得到零个或多个参数并返回一个值）。但是，如果我考虑一个看似简单的过程在某处插入一行的真实示例，并且在插入之前或之后有几个触发器执行此操作，那么即使定义“单元”的边界也非常困难。我应该只测试INSERT它本身吗？我认为这相当简单——价值相对较低。我应该测试整个事件链的结果吗？除了这是否是单元测试的问题之外，设计一个合适的测试可能是一项相当艰巨的工作，其中会出现许多额外的问号。

然后是不断变化的数据的问题。在UPDATE影响不止几行的情况下，每个可能受影响的行都必须以某种方式包含在测试用例中。s等的进一步困难DELETE等等。

那么如何对存储过程进行单元测试呢？复杂性是否存在完全绝望的临界点？维护需要哪些资源？

编辑另一个小问题，基于 AlexKuznetsov 的回答：或者是否有一个门槛，在它之下它是完全没用的？

我有以下表格和索引定义：

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

为什么以下查询中没有使用 munkalap_id 上的任何索引？

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

即使我添加一个过滤器也是一样的：

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms

我有一个非常慢的视图 :) 它只连接两个表，有几个简单的 WHERE 条件和一个子查询形式（见下面的 SubPlan1）。最简单的条件是检查索引扫描，这在某种程度上非常慢：

QUERY PLAN
Result  (cost=19.41..570789.79 rows=18 width=4) (actual time=18724.919..18724.919 rows=0 loops=1)
  One-Time Filter: ((hashed SubPlan 2) AND (('now'::text)::time with time zone >= ($2)::time with time zone) AND (('now'::text)::time with time zone <= ($3)::time with time zone))
  InitPlan 3 (returns $2)
    ->  Function Scan on f_konstansok_select f  (cost=0.26..0.27 rows=1 width=146) (actual time=0.147..0.147 rows=1 loops=1)
  InitPlan 4 (returns $3)
    ->  Function Scan on f_konstansok_select f  (cost=0.26..0.27 rows=1 width=146) (actual time=0.103..0.103 rows=1 loops=1)
  ->  Nested Loop  (cost=18.87..570789.25 rows=18 width=4) (actual time=18724.357..18724.357 rows=0 loops=1)
        Join Filter: (((ml.idopont_tervezett)::timestamp without time zone < now()) OR ((ml.idopont_tervezett IS NULL) AND ((ml.idopont + (((ms.hatarido)::text || ' minutes'::text))::interval) < now())))
        ->  Nested Loop Semi Join  (cost=0.00..570746.76 rows=42 width=45) (actual time=6.393..18711.516 rows=61 loops=1)
              ->  Index Scan using idx_munkalap_lepes_aktualis on munkalap_lepes ml  (cost=0.00..570080.42 rows=1634 width=45) (actual time=6.379..18678.306 rows=3256 loops=1)
                    Index Cond: (aktualis = true)
                    Filter: (NOT (SubPlan 1))
                    SubPlan 1
                      ->  Nested Loop  (cost=681.75..2041.93 rows=1 width=0) (actual time=5.712..5.712 rows=0 loops=3268)
                            ->  Hash Join  (cost=681.75..2033.65 rows=1 width=4) (actual time=5.711..5.711 rows=0 loops=3268)
                                  Hash Cond: (ls.munkalap_statusz_figyelmeztetes_id = msf.munkalap_statusz_figyelmeztetes_id)
                                  Join Filter: (ls.idopont > (now() - (((msf.figyelmeztetes_periodus)::text || ' minutes'::text))::interval))
                                  ->  Bitmap Heap Scan on lepes_sorrend ls  (cost=680.26..2032.12 rows=1 width=16) (actual time=5.706..5.707 rows=0 loops=3268)
                                        Recheck Cond: (munkalap_statusz_figyelmeztetes_id IS NOT NULL)
                                        Filter: (munkalap_lepes_id = ml.munkalap_lepes_id)
                                        ->  Bitmap Index Scan on lepes_sorrend_idx2  (cost=0.00..680.26 rows=36389 width=0) (actual time=1.814..1.814 rows=36337 loops=3268)
                                              Index Cond: (munkalap_statusz_figyelmeztetes_id IS NOT NULL)
                                  ->  Hash  (cost=1.22..1.22 rows=22 width=8) (actual time=0.009..0.009 rows=22 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                        ->  Seq Scan on munkalap_statusz_figyelmeztetes msf  (cost=0.00..1.22 rows=22 width=8) (actual time=0.003..0.004 rows=22 loops=1)
                            ->  Index Scan using pk_munkalap_lepes on munkalap_lepes ml2  (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=12)
                                  Index Cond: (munkalap_lepes_id = ml.munkalap_lepes_id)
                                  Filter: aktualis
              ->  Index Scan using pk_munkalap on munkalap m  (cost=0.00..0.41 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=3256)
                    Index Cond: (munkalap_id = ml.munkalap_id)
                    Filter: ((NOT lezarva) AND (NOT torolve))
        ->  Index Scan using pk_munkalap_statusz on munkalap_statusz ms  (cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=61)
              Index Cond: (munkalap_statusz_id = ml.munkalap_statusz_id)
  SubPlan 2
    ->  Seq Scan on munkanapok  (cost=0.00..16.27 rows=1027 width=4) (actual time=0.008..0.113 rows=1027 loops=1)
Total runtime: 18725.001 ms

索引定义为：

CREATE INDEX idx_munkalap_lepes_aktualis ON hibabejelentes.munkalap_lepes
  USING btree (aktualis)
  WHERE aktualis;

“aktualis”是一个布尔标志，这个名字的意思是“当前”（对于那些想知道的人：）

玩了一段时间后，我仍然不知道如何让它变快……省略其他条件会使查询运行得相当快：

QUERY PLAN
Hash Join  (cost=2.01..428.98 rows=3271 width=4) (actual time=0.449..3.780 rows=3268 loops=1)
  Hash Cond: (ml.munkalap_statusz_id = ms.munkalap_statusz_id)
  ->  Index Scan using idx_munkalap_lepes_aktualis on munkalap_lepes ml  (cost=0.00..381.99 rows=3271 width=8) (actual time=0.410..2.846 rows=3268 loops=1)
        Index Cond: (aktualis = true)
  ->  Hash  (cost=1.45..1.45 rows=45 width=4) (actual time=0.025..0.025 rows=45 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 2kB
        ->  Seq Scan on munkalap_statusz ms  (cost=0.00..1.45 rows=45 width=4) (actual time=0.007..0.012 rows=45 loops=1)
Total runtime: 3.971 ms

所有表都自动清理。

提前感谢任何想法。

编辑：包括 SELECT 语句

CREATE OR REPLACE VIEW hibabejelentes.v_munkalap_figyelmeztetes_kuldes(
    munkalap_id)
AS
  SELECT ml.munkalap_id
  FROM munkalap_lepes ml
       JOIN munkalap_statusz ms USING (munkalap_statusz_id)
  WHERE ml.aktualis AND
        (ml.idopont_tervezett::timestamp without time zone < now() OR
        ml.idopont_tervezett IS NULL AND
        (ml.idopont +((ms.hatarido || ' minutes' ::text) ::interval)) < now()) AND

        NOT (EXISTS (
                      SELECT ls.munkalap_statusz_figyelmeztetes_id
                      FROM lepes_sorrend ls
                           JOIN munkalap_lepes ml2 USING (munkalap_lepes_id)
                           JOIN munkalap_statusz_figyelmeztetes msf USING (
                           munkalap_statusz_figyelmeztetes_id)
                      WHERE ml2.aktualis AND
                            ml2.munkalap_lepes_id = ml.munkalap_lepes_id AND
                            ls.munkalap_statusz_figyelmeztetes_id IS NOT NULL AND
                            ls.idopont >(now() -((msf.figyelmeztetes_periodus ||
                             ' minutes' ::text) ::interval))
        )) AND
        ('now' ::text::date IN (
                                 SELECT munkanapok.datum
                                 FROM munkanapok
        )) AND
        'now'        ::text::time with time zone >=((
                                                      SELECT f.ertek::time
                                                       without time zone AS
                                                        ertek
                                                      FROM f_konstansok_select(
                                                      'MUNKAIDO_ELEJE' ::text,
                                                       'now' ::text::date,
                                                        NULL::integer) f(
                                                        konstans_id,
                                                         konstans_nev, datum,
                                                          ertek, torolve,
                                                           bolthalozat_id)
        )) ::time with time zone AND
        'now'        ::text::time with time zone <=((
                                                      SELECT f.ertek::time
                                                       without time zone AS
                                                        ertek
                                                      FROM f_konstansok_select(
                                                      'MUNKAIDO_VEGE' ::text,
                                                       'now' ::text::date,
                                                        NULL::integer) f(
                                                        konstans_id,
                                                         konstans_nev, datum,
                                                          ertek, torolve,
                                                           bolthalozat_id)
        )) ::time with time zone;