主页 / user-112361

joanolo's questions

Martin Hope
joanolo
Asked: 2017-04-05 11:20:51 +0800 CST
  • 6

当使用 pgAdmin 4（实际上还有其他几个充当数据库客户端的程序）时，与服务器的连接会在 10 或 15 分钟不活动后断开。一杯咖啡和一个电话，您会收到类似于“对不起，与数据库的连接丢失。您要我尝试重新连接吗？”的消息。

并且 pgAdmin 重新连接总是需要不止一次的尝试，并且已经扩展的对象树会崩溃......所以，这有点烦人。

pgAdmin 似乎没有与该行为相关的任何参数。（似乎有一些方法可以更改某些连接超时，但它们与 pgAdmin 在服务器连接时间过长时的行为方式有关）。

可以做些什么来避免 pgAdmin 与数据库断开连接？

披露：这实际上是一个“伪问题”。它是另一个的衍生产品，最终与失去连接无关......鉴于我已经有了一个答案，我（不是很谦虚）认为值得“询问”，以防万一答案对某人有帮助。

postgresql connectivity
Martin Hope
joanolo
Asked: 2017-01-09 15:21:03 +0800 CST
  • 13

给定两个数字n和m，我想生成一系列表格

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

并重复m几次。

例如，对于n = 3and m = 4，我想要以下 24 个数字的序列：

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

我知道如何通过以下两种方法之一在 PostgreSQL 中实现此结果：

使用以下查询，它使用该generate_series函数，以及一些技巧来保证顺序是正确的：

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT 
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0 
CROSS JOIN 
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

...或使用带有伴随和嵌套循环的函数用于相同目的：

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

我怎么可能在标准 SQL 或 Transact-SQL / SQL Server 中做同样的事情？

sql-server postgresql
Martin Hope
joanolo
Asked: 2017-01-07 15:32:53 +0800 CST
  • 6

像这样的查询SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end是可能的，但是：这是一个好习惯吗？

通常在查询的 WHERE 部分使用参数，并在 SELECT 部分有一些计算列，但参数化 ORDER BY 子句并不常见。

假设我们有一个列出二手车的应用程序（à la CraigsList）。汽车列表可以按价格或颜色排序。我们有一个函数，给定一定数量的参数（例如价格范围、颜色和排序标准）返回一组记录和结果。

为了使其具体化，我们假设cars都在下表中：

CREATE TABLE cars
(
  car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
  make text NOT NULL,       /* unnormalized, for the sake of simplicity */
  model text NOT NULL,      /* unnormalized, for the sake of simplicity */
  year integer,             /* may be null, meaning unknown */
  euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
  colour text               /* may be null, meaning unknown */
) ;

该表将具有大多数列的索引...

CREATE INDEX cars_colour_idx
  ON cars (colour);
CREATE INDEX cars_price_idx
  ON cars (price);
/* etc. */

并且有一些商品枚举：

CREATE TYPE car_sorting_criteria AS ENUM
   ('price',
    'colour');

...和一些样本数据

INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES 
    ('Ford',   'Mondeo',   1990,  2000.00, 'green'),
    ('Audi',   'A3',       2005,  2500.00, 'golden magenta'),
    ('Seat',   'Ibiza',    2012, 12500.00, 'dark blue'),
    ('Fiat',   'Punto',    2014,     NULL, 'yellow'),
    ('Fiat',   '500',      2010,  7500.00, 'blueish'),
    ('Toyota', 'Avensis',  NULL,  9500.00, 'brown'), 
    ('Lexus',  'CT200h',   2012, 12500.00, 'dark whitish'), 
    ('Lexus',  'NX300h',   2013, 22500.00, NULL) ;

我们要进行的查询类型如下：

SELECT
    make, model, year, euro_price, colour
FROM
    cars.cars
WHERE
    euro_price between 7500 and 9500 
ORDER BY
    colour ;

我们想在函数中查询这种风格：

CREATE or REPLACE FUNCTION get_car_list
   (IN _colour    text, 
    IN _min_price numeric, 
    IN _max_price numeric, 
    IN _sorting_criterium car_sorting_criteria) 
RETURNS record AS
$BODY$
      SELECT
          make, model, year, euro_price, colour
      FROM
          cars
      WHERE
           euro_price between _min_price and _max_price
           AND colour = _colour
      ORDER BY
          CASE WHEN _sorting_criterium = 'colour' THEN
            colour
          END,
          CASE WHEN _sorting_criterium = 'price' THEN
            euro_price
          END 
$BODY$
LANGUAGE SQL ;

代替这种方法，这个函数中的 SQL 可以作为字符串动态生成（在 PL/pgSQL 中），然后执行。

我们可以感觉到任何一种方法的一些限制、优点和缺点：

  1. 在一个函数中，很难找到某个语句的查询计划（如果可能的话）。然而，当我们经常使用某些东西时，我们倾向于使用函数。
  2. 静态 SQL 中的错误将（大部分）在编译函数或首次调用时被捕获。
  3. 动态 SQL 中的错误只会在函数编译后才被捕获（moslty），并且所有执行路径都已检查（即：对函数执行的测试次数可能非常多）。
  4. 像公开的那样的参数查询可能比动态生成的查询效率低；然而，执行者将有更难的工作解析/制作查询树/每次决定（这可能会影响相反方向的效率）。

问题：

如何“两全其美”（如果可能）？【效率+编译检查+轻松调试+轻松优化】

注意：这将在 PostgreSQL 9.6 上运行。

postgresql functions
Martin Hope
joanolo
Asked: 2017-01-01 02:09:40 +0800 CST
  • 19

在某些情况下，在制作了一个巨大update的insert或delete从一张桌子之后，我开始了一个VACUUM FULL ANALYZE以确保数据库不会变得过于臃肿。在生产数据库中执行此操作让我发现这不是一个好主意，因为我可能会长时间阻塞该表。所以，我取消了这个过程，也许只是尝试VACUUM（不完整）或者让AUTOVACUUM以后做任何可以做的事情。

问题是：如果我“中途”停止 VACUUM 或 AUTOVACUUM，是否所有处理都已经丢失？

例如，如果VACUUM已经找到 1 M 死行并且我停止它，那么所有这些信息都会丢失吗？VACUUM 是否以完全事务的方式工作（“全有或全无”，就像大量的 PostgreSQL 进程一样）？

如果 VACUUM 可以安全地中断而不会丢失所有工作，那么有什么方法可以vacuum增量工作吗？[工作 100 毫秒，停止，等待 10 毫秒以允许不阻塞世界其他地方......等等]。我知道您可以通过调整 autovacuum 参数来完成其中的一部分，但我正在考虑能够以编程方式控制这一点，以便能够在特定时间/特定条件下执行此操作。

注意：在这种情况下，停止/取消/终止进程意味着：

  • 如果使用 pgAdmin，请按“取消查询”按钮。
  • 如果以编程方式工作，请调用 pg_cancel_backend()。

我假设两者是等价的。我没有使用任何 shell/系统级别的 kill 命令。

postgresql vacuum
Martin Hope
joanolo
Asked: 2016-12-30 00:02:32 +0800 CST
  • 55

每当我需要检查表中是否存在某行时，我倾向于总是写一个条件，例如：

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

其他一些人这样写：

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

当条件NOT EXISTS不是EXISTS: 在某些情况下，我可能会用 aLEFT JOIN和一个额外的条件（有时称为antijoin）来编写它：

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

我尽量避免它，因为我认为含义不太清楚，特别是当您primary_key的内容不是那么明显时，或者当您的主键或连接条件是多列时（并且您很容易忘记其中一列）。但是，有时您维护由其他人编写的代码……它就在那里。

  1. 有什么区别（除了风格）来SELECT 1代替SELECT *吗？
    有没有表现不同的极端情况？

  2. 虽然我写的是（AFAIK）标准SQL：不同的数据库/旧版本有这样的区别吗？

  3. 明确写反加入有什么好处吗？
    当代计划者/优化者是否将其与NOT EXISTS条款区别对待？

sql-server mysql
Martin Hope
joanolo
Asked: 2016-12-27 12:27:58 +0800 CST
  • 3

假设我必须非常简单的表格

CREATE TABLE a(id integer PRIMARY KEY, 
       t timestamp default now(), 
       sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY, 
       t timestamp default now(), 
       sensor_readings real[]);

有一些关于他们的数据

INSERT INTO a(id) SELECT generate_series(    1,   100);
INSERT INTO b(id) SELECT generate_series(10001, 10100);

实际上，表 a 可能有大约 100_000 行，而表 b 大约有 50_000 行。在实践中，id 序列也可能有间隙（大约 %）。因此，笛卡尔积 axb 具有数十亿的基数。

我想随机抽取 1_000 个排序对（a.id，b.id）。我可以使用类似以下查询的内容：

SELECT  
    *
FROM
(
    SELECT
        *
    FROM
        (
        SELECT 
            a.id AS a_id, b.id AS b_id
        FROM
            a CROSS JOIN b
        ORDER BY
            random()
        ) AS s0
    LIMIT
        1000 
) AS s1
ORDER BY
    a_id, b_id ;

...但是一旦 a 或 b 上的行数增加（由于 CROSS JOIN 的增长），它就会变得非常低效。

有没有办法以最佳方式做与此等效的事情？也就是说，是否有一种实用的方法可以从a x b关系中获取随机行样本，而无需实际实例化它。

注意：对于 a.id 或 b.id 可以重复的事实没有限制。虽然这对 (a.id, b.id) 不能。

如果我试图用命令式语言对此进行编程，我很可能会使用循环并执行类似以下伪代码的操作（然后，请统计学家对其进行检查，以确保我真的采取了样本其中所有对具有相同的被选中概率）：

start with a result set equal to {} (empty set)
while size of result set < 1000
    Pick the id value from a random row from table a -> rand_id_a
    Pick the id value from a random row from table b -> rand_id_b
    If (rand_id_a, rand_id_b) not in result set
        append (rand_id_a, rand_id_b) to result set
    end if
end while
sort result set and return it

有没有办法在不求助于循环的情况下获得等效的结果？如果没有，是否有使用 plpgSQL 的有效方法？（或任何其他语言）

postgresql random