Início / dba / Perguntas / 86370
Accepted
user4150760
Asked: 2014-12-17 22:11:36 +0800 CST

CTE recursivo para encontrar slug único

  • 772

Eu tenho uma tabela de artigos onde quero que o slug seja único.

CREATE TABLE article (
   title char(50) NOT NULL,
   slug  char(50) NOT NULL
);

Quando o usuário insere um título, por exemplo News on Apple, quero verificar o banco de dados para ver se existe um slug correspondente, por exemplo news-on-apple. Em caso afirmativo, colocarei um sufixo em um valor numérico até encontrar um único, por exemplo news-on-apple-1. Isso pode ser alcançado com uma consulta CTE recursiva em vez de fazer recursão em meu ORM. Existe um bom número aproximado onde devo parar de recorrer e errar. Posso imaginar pessoas usando o mesmo título 1.000 vezes, o que resultaria em 1.000 consultas apenas para criar 1 artigo.

É possível que meu entendimento de CTE recursivo esteja incorreto e não haja maneira melhor de encontrar um slug exclusivo. Por favor, sugira quaisquer alternativas.

database-design postgresql

1 respostas

  1. Best Answer

    Primeiro, você não quer usar char(50). Use varchar(50)ou apenas text. Consulte Mais informação:

    Supondo as seguintes regras:

    • Lesmas básicas nunca terminam com um traço.
    • Lesmas duplicadas são sufixadas com um traço e um número sequencial ( -123).

    Observe que todos os métodos a seguir estão sujeitos a condições de corrida : operações simultâneas podem identificar o mesmo nome "livre" para o próximo slug.
    Para se defender contra isso, você pode impor uma restrição UNIQUE sluge estar preparado para repetir um INSERT em caso de violação de chave duplicada ou para obter um bloqueio de gravação na tabela no início da transação.

    Se você colar o sufixo ao nome do slug básico com um traço e permitir que os slugs básicos terminem em números separados, a especificação será um pouco ambígua (veja os comentários). Em vez disso, sugiro um delimitador exclusivo de sua escolha (o que não é permitido).

    rCTE eficiente

    WITH RECURSIVE
  input AS (SELECT 'news-on-apple'::text AS slug)  -- input basic slug here once
, cte   AS (
   SELECT slug || '-' AS slug  -- append '-' once, if basic slug exists
        , 1 as suffix          -- start with suffix 1
   FROM   article
   JOIN   input USING (slug)

   UNION ALL
   SELECT c.slug, c.suffix + 1  -- increment by 1 ...
   FROM   cte     c
   JOIN   article a ON a.slug = c.slug || c.suffix  -- ... if slug-n already exists
   )
(
SELECT slug || suffix AS slug
FROM   cte
ORDER  BY suffix DESC  -- pick the last (free) one
LIMIT  1
)  -- parentheses required
UNION  ALL  -- if the basic slug wasn't taken, fall back to that
SELECT slug FROM input
LIMIT  1;

    Melhor desempenho sem rCTE

    Se você se preocupa com milhares de slugs competindo pelo mesmo slug ou geralmente deseja otimizar o desempenho, considero uma abordagem diferente e mais rápida.

    WITH input AS (SELECT 'news-on-apple'::text  AS slug
                    , 'news-on-apple-'::text AS slug1)  -- input basic slug here
SELECT i.slug
FROM   input        i
LEFT   JOIN article a USING (slug)
WHERE  a.slug IS NULL  -- doesn't exist yet.

UNION ALL
(  -- parentheses required
SELECT i.slug1 || COALESCE(right(a.slug, length(i.slug1) * -1)::int + 1, 1)
FROM   input        i
LEFT   JOIN article a ON a.slug LIKE (i.slug1 || '%')  -- match up to last "-"
                     AND right(a.slug, length(i.slug1) * -1) ~ '^\d+$' -- suffix numbers only
ORDER  BY right(a.slug, length(i.slug1) * -1)::int DESC
)
LIMIT  1;

    • Se o slug básico ainda não foi executado, o segundo mais caro nuncaSELECT é executado - o mesmo que acima, mas muito mais importante aqui. Verifique com , Postgres é inteligente dessa forma com consultas. Relacionado:EXPLAIN ANALYZELIMIT

    • Verifique a string inicial e o sufixo separadamente, para que a LIKEexpressão possa usar um índice btree básico com text_pattern_opscomo

      CREATE INDEX article_slug_idx ON article (slug text_pattern_ops);

      Explicação detalhada:

    • Converta o sufixo para inteiro antes de aplicar max(). Os números na representação de texto não funcionam.

    Otimize o desempenho

    Para obter o ideal, considere armazenar o sufixo separado do slug básico e concatenar o slug conforme necessário:concat_ws('-' , slug, suffix::text) AS slug

    CREATE TABLE article (
   article_id serial PRIMARY KEY
 , title text NOT NULL
 , slug  text NOT NULL
 , suffix int
);

    A consulta para um novo slug torna-se então:

    SELECT slug
    || COALESCE((
          SELECT '-'::text || (max(suffix) + 1)::text
          FROM   article a
          WHERE  a.slug = i.slug), '') As slug
FROM  (SELECT 'news-on-apple'::text AS slug) i  -- input basic slug here

    Idealmente suportado com um índice exclusivo em (slug, suffix).

    Consulta para lista de slugs

    Em qualquer versão do Postgres, você pode fornecer linhas em uma VALUESexpressão.

    SELECT *
FROM   article
JOIN  (
   VALUES
     ('slug-foo'::text, 1)
     ('slug-bar',7)
   ) u(slug,suffix) USING (slug,suffix);

    Você também pode usar INcom um conjunto de expressões de tipo de linha que é mais curta:

    SELECT *
FROM   article
WHERE (slug,suffix) IN (('slug-foo', 1), ('slug-bar',7));

    Detalhes nesta questão relacionada (conforme comentado abaixo):

    Para listas longas, o JOINpara uma VALUESexpressão é normalmente mais rápido.

    No Postgres 9.4 (lançado hoje!), você também pode usar a nova variante unnest()para desaninhar vários arrays em paralelo.

    Dado um array de slugs básicos e um array correspondente de sufixos (conforme comentário):

    SELECT *
FROM   article
JOIN   unnest('{slug-foo,slug-bar}'::text[]
            , '{1,7}'::int[]) AS u(slug,suffix) USING (slug,suffix);
    • 7

relate perguntas