主页 / dba / 问题

问题[postgresql](dba)

Martin Hope
bellysavalas
Asked: 2024-11-12 10:57:30 +0800 CST
  • 6

我正在尝试在 pgAdmin 中编写 Postgres 过程。我编写的过程失败，并显示不同的错误消息，具体取决于我将光标放在哪里。有时它甚至似乎成功了，但没有创建任何过程。

我的问题：

  1. 我的代码有什么问题？
  2. 有没有更好/更简单的方法来实现同样的目标？

下面的代码尝试遍历一个值表（外循环），并针对该表中的每一行，遍历另一个具有一些缺失值的表（内循环）。每当在内循环表中发现缺失值时，如果外表中存在可用的值，我会用该值更新内表。

CREATE OR REPLACE PROCEDURE impute() 
LANGUAGE plpgsql 
AS $$ 
DECLARE 
    cntry_dec_means RECORD; 
    table_row RECORD; 
BEGIN 
    <<outer_loop>> 
    FOR cntry_dec_means IN 
        SELECT * FROM country_decade_means 
    LOOP 
        -- if all imputed values are null, don't do anything and move to the next row 
        IF ISNULL(cntry_dec_means.gdppc) 
            AND ISNULL(cntry_dec_means.gdp) 
            AND ISNULL(cntry_dec_means.ed_gdp) 
            AND ISNULL(cntry_dec_means.population) THEN 
                CONTINUE; 
 
        -- get matching rows from the actual table 
        <<inner_loop>> 
        FOR table_row IN 
            SELECT * FROM inls625_impute 
            WHERE country = cntry_dec_means.country 
            AND decade = cntry_dec_means.decade 
        LOOP 
            IF (table_row.gdppc IS NULL) AND (cntry_dec_means.gdppc IS NOT NULL) THEN   
                UPDATE inls625_impute SET gdppc = cntry_dec_means.gdppc 
                WHERE inls625_impute.country = cntry_dec_means.country 
                AND inls625_impute.decade = cntry_dec_means.decade; 
        END LOOP; --<<inner_loop>> 
    END LOOP; --<<outer_loop>>
END; 
$$;

更新

也感谢@prasad 阐明了如何使用循环标签。不幸的是，它似乎对我不起作用。

以下是错误（在 psql 中）：

CREATE OR REPLACE PROCEDURE impute()
LANGUAGE plpgsql
AS $$"
LINE 1: $$;
        ^
INLS-623-Labs=#     table_row RECORD;
ERROR:  syntax error at or near "table_row"
LINE 1: table_row RECORD;
        ^
INLS-623-Labs=# BEGIN
INLS-623-Labs-#     <<outer_loop>>
INLS-623-Labs-#     FOR cntry_dec_means IN
INLS-623-Labs-#         SELECT * FROM country_decade_means
INLS-623-Labs-#     LOOP
INLS-623-Labs-#         -- if all imputed values are null, don't do anything and move to the next row
INLS-623-Labs-#         IF ISNULL(cntry_dec_means.gdppc)
INLS-623-Labs-#             AND ISNULL(cntry_dec_means.gdp)
INLS-623-Labs-#             AND ISNULL(cntry_dec_means.ed_gdp)
INLS-623-Labs-#             AND ISNULL(cntry_dec_means.population) THEN
INLS-623-Labs-#                 CONTINUE outer_loop;
ERROR:  syntax error at or near "<<"
LINE 2:     <<outer_loop>>
            ^
INLS-623-Labs=#
INLS-623-Labs=#         -- get matching rows from the actual table
INLS-623-Labs=#         <<inner_loop>>
INLS-623-Labs-#         FOR table_row IN
INLS-623-Labs-#             SELECT * FROM inls625_impute
INLS-623-Labs-#             WHERE country = cntry_dec_means.country
INLS-623-Labs-#             AND decade = cntry_dec_means.decade
INLS-623-Labs-#         LOOP
INLS-623-Labs-#             IF ISNULL(table_row.gdppc) AND NOT ISNULL(cntry_dec_means.gdppc) THEN
INLS-623-Labs-#                 UPDATE inls625_impute SET gdppc = cntry_dec_means.gdppc
INLS-623-Labs-#                 WHERE inls625_impute.country = cntry_dec_means.country
INLS-623-Labs-#                 AND inls625_impute.decade = cntry_dec_means.decade;
ERROR:  syntax error at or near "<<"
LINE 1: <<inner_loop>>
        ^
INLS-623-Labs=#         END LOOP inner_loop;
ERROR:  syntax error at or near "LOOP"
LINE 1: END LOOP inner_loop;
            ^
INLS-623-Labs=#     END LOOP outer_loop;
ERROR:  syntax error at or near "LOOP"
LINE 1: END LOOP outer_loop;
            ^
INLS-623-Labs=# END;
WARNING:  there is no transaction in progress
COMMIT
INLS-623-Labs=# $$;
INLS-623-Labs$#

此外，我复现了@prasad 提供的示例并得到了类似的错误：

CREATE OR REPLACE PROCEDURE t_proc()
LANGUAGE plpgsql
AS $$"
LINE 1: $$;
        ^
INLS-623-Labs=#     t2_rec RECORD;
ERROR:  syntax error at or near "t2_rec"
LINE 1: t2_rec RECORD;
        ^
INLS-623-Labs=# BEGIN
INLS-623-Labs-#     <<outer_loop>>
INLS-623-Labs-#     FOR t_rec IN
INLS-623-Labs-#         SELECT * FROM t
INLS-623-Labs-#     LOOP
INLS-623-Labs-#         IF t_rec.name IS NULL AND t_rec.data IS NULL THEN
INLS-623-Labs-#             CONTINUE outer_loop;
ERROR:  syntax error at or near "<<"
LINE 2:     <<outer_loop>>
            ^
INLS-623-Labs=#         END IF;
ERROR:  syntax error at or near "IF"
LINE 1: END IF;
            ^
INLS-623-Labs=#         RAISE NOTICE 't.id value: %', t_rec.id;
ERROR:  syntax error at or near "RAISE"
LINE 1: RAISE NOTICE 't.id value: %', t_rec.id;
        ^
INLS-623-Labs=#         <<inner_loop>>
INLS-623-Labs-#         FOR t2_rec IN
INLS-623-Labs-#             SELECT * FROM t2 WHERE t2.id = t_rec.id
INLS-623-Labs-#         LOOP
INLS-623-Labs-#             RAISE NOTICE 't2.id value: %', t2_rec.id;
ERROR:  syntax error at or near "<<"
LINE 1: <<inner_loop>>
        ^
INLS-623-Labs=#         END LOOP inner_loop;
ERROR:  syntax error at or near "LOOP"
LINE 1: END LOOP inner_loop;
            ^
INLS-623-Labs=#     END LOOP outer_loop;
ERROR:  syntax error at or near "LOOP"
LINE 1: END LOOP outer_loop;
            ^
INLS-623-Labs=# END;
WARNING:  there is no transaction in progress
COMMIT
INLS-623-Labs=# $$;
postgresql
Martin Hope
Jitendra Loyal
Asked: 2024-11-11 19:49:49 +0800 CST
  • 5

在 PostgreSQL 16 中运行 pg_dumpall 的 OUTPUT 后，是否需要对表进行 VACUUM 或 ANALYZE，或者两者兼而有之？

  1. pg_dumpall > all_database.sql
  2. 全新安装 PostgreSQL 16.4
  3. psql < all_database.sql

完成上述步骤后，是否需要对表（特别是较大的表）运行 VACUUM 和/或 ANALYZE？

我个人认为 VACUUM 是不需要的，因为所有数据都是最近创建的，但我希望有经验的人能支持我的观点。至于 ANALYZE，我无法说。

postgresql
Martin Hope
muttBunch
Asked: 2024-11-11 15:56:01 +0800 CST
  • 5

我正在考虑以下问题：

SELECT
    to_char(date_trunc('hour', "TimeStamp"), 'HH12 AM') || ' - ' || to_char(date_trunc('hour', "TimeStamp")  + interval '1 hour', 'HH12 AM') as time,
    COUNT(*) as totals
FROM 
    eventtotals
    
WHERE "TimeStamp" >= NOW() - Interval '24 HOURS'

GROUP BY date_trunc('hour', "TimeStamp")

它显示以下输出：

TIME                TOTAL
04 PM - 05 PM       300
05 PM - 06 PM       452
06 PM - 07 PM       393
07 PM - 08 PM       356
08 PM - 09 PM       356
09 PM - 10 PM       361
10 PM - 11 PM       359
11 PM - 12 AM       367
12 AM - 01 AM       357
01 AM - 02 AM       360
02 AM - 03 AM       286

我知道

TRIM(LEADING '0'...

但是，我想从小时中删除前导零，我只是不确定在上面列出的查询中实际将修剪行放在哪里，除非有其他方法。

谢谢

postgresql
Martin Hope
Dante
Asked: 2024-11-08 15:22:25 +0800 CST
  • 5

我知道我可以使用锁定整个表LOCK TABLE，但我只是好奇我是否可以通过使用语句获得相同的结果FOR UPDATE，假设所有更新该表的事务在其事务开始时使用从该表中选择特定行（或者可能有一个名为的唯一列FOR UPDATE的单独表，并在每个事务之前使用它来获取锁）？lockslock_nameSELECT lock_name FROM locks WHERE lock_name = 'foo' FOR UPDATE

我之所以问这个问题，是因为我必须使用邻接表或闭包表将树数据结构保存在数据库表中。当多个用户尝试添加新节点或重新定位子树时，我必须选择一些行并进行一些计算，然后根据这些计算更新表，除非我锁定整个表，否则此操作是不可能的。

postgresql
Martin Hope
Björn Morén
Asked: 2024-11-08 14:43:57 +0800 CST
  • 8

我想以三种排序顺序之一返回我的帖子行，由sorting_param函数参数控制。排序很复杂，不适合简单的排序ORDER BY CASE ...。所以我正在考虑将每个排序写入其自己的 CTE，然后选择其中一个 CTE 作为最终输出。但是，我不希望 PostgreSQL 浪费时间在这三个上，只浪费我选择的那个。那么这三个都会实现还是只有我选的那个会实现？或者有更好的方法吗？PostgreSQL 版本 16。简化代码如下。

WITH posts_by_order1 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 1]
    LIMIT 10
),
posts_by_order2 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 2]
    LIMIT 10
),
posts_by_order3 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 3]
    LIMIT 10
)
SELECT * FROM posts_by_order1 WHERE sorting_param = 0
UNION ALL
SELECT * FROM posts_by_order2 WHERE sorting_param = 1
UNION ALL
SELECT * FROM posts_by_order3 WHERE sorting_param = 2;

不幸的是我不能这样做：

    ORDER BY 
        CASE 
            WHEN sorting_param = 0 THEN p.date 
            WHEN sorting_param = 1 THEN p.name 
            ELSE NULL 
        END DESC, ...
postgresql
Martin Hope
Dante
Asked: 2024-11-02 11:27:04 +0800 CST
  • 8

我在 Postgres 17 数据库中有两个表：site和page。每个站点可以有多个页面，每个页面可以有一个父页面：

CREATE TABLE site (
  id INT PRIMARY KEY
);
CREATE TABLE page (
  id INT PRIMARY KEY,
  site_id INT references site(id),
  parent_id INT references page(id)
);

现在假设我有两个站点：

INSERT INTO site (id) VALUES (1);
INSERT INTO site (id) VALUES (2);

每个站点一个根页面：

INSERT INTO page (id, site_id, parent_id) VALUES (1, 1, NULL);
INSERT INTO page (id, site_id, parent_id) VALUES (2, 2, NULL);

如何防止为站点 2 插入父页面属于站点 1 的新页面？

INSERT INTO page (id, site_id, parent_id) VALUES (3, 2, 1);
postgresql
Martin Hope
David Tonhofer
Asked: 2024-11-02 05:31:28 +0800 CST
  • 5

我正在运行postgresql-server-16.3-3.fc41.x86_64并想要为XWiki wiki 软件创建一个数据库。

按照此处详细的说明，我应该发出以下CREATE DATABASE指令：

CREATE DATABASE xwiki
WITH 
OWNER = postgres
ENCODING = 'UNICODE'
LOCALE_PROVIDER = 'builtin'
LOCALE = 'C.UTF-8'
TABLESPACE = pg_default;

当我发布该声明时，我得到：

ERROR:  unrecognized locale provider: builtin

这是出乎意料的，因为 PostgreSQL 文档指出这builtin是一个有效的关键字。从页面中可以看到CREATE DATABASE：

locale_provider：指定此数据库中默认排序规则使用的提供程序。可能的值是builtin、icu（如果服务器是使用 ICU 支持构建的）或libc。默认情况下，提供程序与模板的提供程序相同。有关详细信息，请参阅 第 23.1.4 节 。

这个错误信息从何而来？

或者，我可以使用LOCALE_PROVIDER等于libc，与模板数据库 的区域设置提供程序相同template1。在这种情况下，排序规则也必须等于模板数据库 的排序规则template1。那一个恰好是en_US.UTF-8（请注意，根据字符集支持的文档，UNICODE实际上意味着UTF-8，因此没有矛盾）：

CREATE DATABASE xwiki
WITH 
OWNER = postgres
ENCODING = 'UNICODE'
LOCALE_PROVIDER = 'libc'
LOCALE = 'en_US.UTF-8'
TABLESPACE = pg_default;

这很好用。

或者可以使用具有适当ICU 语言环境的ICU（虽然我不知道 XWiki 是否可以处理这个问题。但它应该可以，毕竟它只是一个语言环境）：

CREATE DATABASE xwiki
WITH 
OWNER = postgres
TEMPLATE = 'template0'
ENCODING = 'UNICODE'
LOCALE_PROVIDER = 'icu'
ICU_LOCALE = 'en-US'
TABLESPACE = pg_default
postgresql
Martin Hope
Ricardo Mayerhofer
Asked: 2024-11-02 04:10:05 +0800 CST
  • 5

我有一个创建函数语句，我想将其包装在 DO 语句中以检查该函数是否存在。但是，我对语法有点困惑。任何帮助我都感激不尽。谢谢！

功能、工作原理：

CREATE FUNCTION write_pos_history()
RETURNS TRIGGER AS $$
BEGIN
   EXECUTE ...
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

尝试换行，错误：输入结束时函数定义意外结束

DO $$
BEGIN
   IF NOT EXISTS (SELECT 1 FROM pg_proc
               WHERE proname = 'write_pos_history'
               AND pg_function_is_visible(oid)) THEN
      CREATE FUNCTION write_pos_history()
      RETURNS TRIGGER AS $$
      BEGIN
         EXECUTE ...
         RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
   END IF;
END $$;
postgresql
Martin Hope
sebarys
Asked: 2024-10-31 23:05:15 +0800 CST
  • 7

我有users桌子

CREATE TABLE tasks(
    id UUID PRIMARY KEY,
    status TEXT REFERENCES statuses, -- available statuses are 'NEW', 'PENDING', 'PROCESSING', 'COMPLETE'
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NULL
);

我想确保在多次并发更新尝试的情况下，只有单个进程的状态会从 更改为PENDING，PROCESSING其余进程应该观察到零更新行。

任务状态变化是由处理外部 HTTP 请求驱动的，重要的是只有一个请求能够成功，其余的应该检测变化并返回 409 冲突，我们无法控制可以获得多少个并发请求。

更新语句是作为较大事务的一部分执行的第一件事，在update事件进程观察到它更新了tasks行（更新的行数为 1）之后，它会在其他表中插入/更新一些数据等。

我已经测试了以下更新查询

UPDATE tasks 
  SET status = 'PROCESSING'
  WHERE id = '00000000-0000-0000-0000-000000000000' AND status = 'PENDING'

使用 Java JDBC、多线程、连接等，我观察到在使用默认隔离级别时只有单个进程进行更新READ COMMITTED。

当我思考这个问题时，我认为我需要FOR UPDATE语句UPDATE或SERIALIZABLE事务的隔离级别，但在实际测试中，我无法在多次更新的情况下捕获不一致的情况。

id我的问题是：通过过滤并status确保只有单个进程执行更新是否足够，或者我需要采取其他措施来排除一般的意外行为？

postgresql