All perguntas(dba)

O arquivo de configuração postgresql.conf contém uma configuração para max_connections.

Tenho algumas perguntas relacionadas...

1. No contexto desse valor, o que exatamente constitui uma "conexão"?
2. Qual é a melhor maneira de calcular isso?
3. Quais são os sintomas/mensagens de erro que podem ser esperados em um banco de dados que precisa ser max_connectionsaumentado?

Vi várias postagens sobre como contar o número de conexões atuais...

1)  SELECT count(*) from pg_stat_activity;

2)  SELECT count(distinct(numbackends)) FROM pg_stat_database;

3)  SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null; 

4)  SELECT count(*) FROM pg_stat_activity WHERE datname is not null;

5)  select sum(numbackends) from pg_stat_database;

Eles geralmente produzem resultados diferentes e levam tempos variados para serem executados.

Qual consulta deve fornecer os resultados mais precisos e como posso saber se preciso aumentar max_connections?

Sou meio que um "DBA acidental", então posso estar totalmente enganado.

Estamos no local e estamos falando sobre nosso ambiente de teste.

Estamos planejando mudar de empresa de hospedagem.

Vamos mover nosso AG para a nova empresa de hospedagem

Gostaríamos de poder retornar rapidamente para a antiga empresa de hospedagem, caso algo não esteja funcionando na nova.

Temos muitas tabelas temporais, então a replicação normal via log de transações não é possível.

Embora seja um ambiente de teste, há muita atividade de teste que não queremos perder se uma nova empresa de hospedagem falhar. (Poderíamos fazer backup e restaurar, mas eu estava procurando uma solução mais rápida)

O logshipping pode ser uma solução para isso ou há outra maneira que você sugeriria?

cumprimentos

Estou usando um banco de dados postgresql e atualmente estou experimentando algumas coisas de vácuo/vácuo automático/vácuo total.

Até onde eu sei, as linhas atualizadas/removidas na tabela ainda ocupam espaço (e são marcadas apenas como ocultas) até que o comando vacuum seja chamado. Após a execução do comando vacuum, essas tuplas inativas são marcadas como espaço livre, e esse espaço pode ser recuperado pela mesma tabela (ou talvez por tabelas diferentes? Não tenho certeza), mas o espaço não é retornado ao SO/FS e ainda é reservado pelo banco de dados até que o comando VACUUM FULL seja executado.

E aqui vem a minha pergunta. Sou capaz de determinar quanto espaço a tabela ocupa no disco

SELECT pg_size_pretty(pg_relation_size('fluffy_table')) AS data_only,
       pg_size_pretty(pg_total_relation_size('fluffy_table')) AS with_indexes;

Este deve ser o espaço "reservado" para a tabela e, após executar VACUUM FULL, esse valor provavelmente mudará (se houver algumas linhas excluídas).

Mas existe alguma opção, como obter o tamanho desta tabela após VACUUM FULL antes de executar VACUUM FULL? Ou algum uso em porcentagens, talvez? Então, posso dizer que esta tabela tem 100 MB de tamanho, mas apenas 60% (60 MB) são usados ​​por dados reais, então posso adicionar 40% a mais de linhas a ela antes que ela cresça novamente?

E é possível com postgresql nativo (sem extensões)?

Pensei na soma das tuplas vivas + mortas, calculando o tamanho médio da linha e, em seguida, múltiplas tuplas vivas/mortas com base nesse valor. Mas já chamei VACUUM, então não tenho mais informações sobre tuplas mortas.

Estou tentando configurar o PostgreSQL em um novo computador (Linux Mint). Tudo funciona perfeitamente até eu tentar acessar o banco de dados. Quando o faço, como meu próprio usuário, recebo o seguinte erro (esperado):

$ psql 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "me" does not exist

Isso faz sentido, porque no Linux há um postgresusuário separado com acesso ao banco de dados. Tentei usá-lo:

$ sudo su postgres
[sudo] password for me:
$ psql
Password for user postgres:  psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Nesse ponto, fiquei confuso: o postgresusuário deveria ter acesso sem senha a todos os bancos de dados PostgreSQL. Além disso, tentei configurar meu pg_hba.confarquivo para permitir acesso local a todos os usuários:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Entretanto, mesmo depois de reiniciar o PostgreSQL (com /etc/init.d/postgresql reload), ainda fui solicitado a fornecer uma senha de banco de dados para o usuário postgres.

Alguém pode me ajudar a entender:

1. Por que estou sendo solicitado a digitar uma senha quando tento acessar meu banco de dados como postgresusuário (em uma nova instalação)?
2. Por que ainda recebo a solicitação mesmo depois de definir conexões locais IPv4/IPv6 como trust?
3. Como posso corrigir meu postgresusuário para que fique igual a outras máquinas Linux (ou seja, não tenha nenhuma senha de banco de dados, mas ainda consiga acessar todos os bancos de dados)?

Quero selecionar várias linhas no bloco de seleção do PostgreSQL. Então tentei usar a função row para fazer uma consulta no PostgreSQL 15 assim:

select c.a from ( select row(1 as a, 2 as b) as c)

mostra erro ERROR: syntax error at or near "as, eu também tentei:

select (c).a from ( select row(1 , 2 ) as c(a,b))

Estou esquecendo de alguma coisa? Ou como obter o valor da linha? Descobri que isso funciona bem:

select * from ( select row(1 , 2 ) ) a

Como conseguir o 1 ou o 2? Eu tentei:

select a[0] from ( select row(1 , 2 ) ) a

parece que não funcionou.

Introdução

Tenho lido muitos artigos sobre exclusão de bancos de dados e os perigos de usar o comando shrink. No entanto, tenho a oportunidade de mover um banco de dados para um novo servidor com uma grande janela de inatividade, então quero redefinir o banco de dados e reduzir seu tamanho (para aumentar o desempenho e economizar custos em um ambiente hospedado).

Fundo

• O SQL Server existente é a Web Edition 2016.
• Nova edição Standard do SQL Server 2022.
• Tamanho do banco de dados ~120 GB.
• Algumas tabelas grandes compõem a maior parte disso, assim como os índices nessas tabelas grandes.
• Quero me concentrar apenas na redução do tamanho do banco de dados. Estou confortável com as outras partes da criação de um novo servidor.

Processo proposto

Antes da interrupção

1. Execute procedimentos armazenados que usam DELETE FROMsintaxe. Eu os testei cuidadosamente. Eles agrupam as exclusões e serão executados ao longo de alguns dias.
2. Monitore os arquivos de log e o disco de log para verificar se há espaço suficiente.
3. Garanta que o processamento de exclusão seja concluído antes de chegarmos à janela de interrupção.

Durante a interrupção

4. Faça um backup completo do banco de dados e coloque-o em um local seguro.
5. Elimine os maiores índices (há cerca de 3 ou 4 que eu vou focar).
6. Coloque o banco de dados no modo de recuperação simples.
7. Reduza os arquivos de log e de banco de dados o máximo possível.
8. Faça backup do banco de dados.
9. Copie o arquivo de backup para o novo servidor.
10. Restaurar o banco de dados para os locais de arquivo corretos
11. Aplique os índices que foram removidos.
12. Reconstrua todos os índices.
13. Atualizar estatísticas.
14. Retorne o banco de dados ao modelo de recuperação completa.
15. Testando e reiniciando aplicativos

Estamos tentando combinar eventos com linhas com base na distância geográfica mais próxima

--pseudo code
LATERAL JOIN (
    SELECT line_id, geom 
    FROM lines 
    ORDER by distance(lines.geom, events.geom) 
    LIMIT 1) nearest_line

Às vezes, surpreendentemente, há empates. Queremos garantir que, se nosso pipeline de ETL for executado novamente com os mesmos conjuntos de dados, as mesmas linhas sejam retornadas. Uma ideia foi incluir line_idno ORDER BY, mas isso poderia distorcer nossa correspondência para valores mais baixos line_id. Existe alguma maneira de desfazermos os empates aleatoriamente, produzindo a mesma saída se nosso ETL tivesse que ser refeito nos mesmos conjuntos de dados?

Eu estava pensando em algo como, random()mas definir a semente com base em uma combinação de event_ide line_id. Algo como usar mod()nos IDs poderia ser... OK?

Executar a mesma consulta em duas tabelas que diferem apenas no número de linhas (~7,8M vs ~1,4M) resulta em dois planos diferentes, o que parece razoável. Mas a execução na tabela menor é de 4 a 5 vezes mais lenta e eu gostaria de entender o porquê.

As tabelas são definidas como estão:

   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 image_id   | bigint                   |           | not null | 
 h3_cell    | h3index                  |           | not null | 
 created_at | timestamp with time zone |           | not null | 
 location   | geometry(PointZ,4326)    |           | not null | 
Indexes:
    "images_a_pkey" PRIMARY KEY, btree (image_id)
    "images_a_created_at_idx" btree (created_at)
    "images_a_h3_cell_idx" btree (h3_cell)

A consulta é a seguinte

h3_cells AS (
    SELECT UNNEST(h3_linestring_to_cells(:line_string, 13, 1)) AS cell
)
SELECT COUNT(*)
FROM images
JOIN h3_cells hc ON images.h3_cell = hc.cell

A h3_linestring_to_cells()função retorna um array cujo h3indextamanho pode, em alguns casos, chegar a dezenas de milhares de valores. Nos exemplos abaixo, ele retorna cerca de 50.000.

Na tabela com 7,8 milhões de linhas, as entradas de plano e execução são as seguintes (valores da matriz redigidos para brevidade):

Aggregate  (cost=347404.47..347404.48 rows=1 width=8) (actual time=74.311..74.312 rows=1 loops=1)
  Buffers: shared hit=154681 read=328
  I/O Timings: shared read=1.362
  ->  Nested Loop  (cost=0.43..346724.23 rows=272093 width=0) (actual time=0.051..74.246 rows=833 loops=1)
        Buffers: shared hit=154681 read=328
        I/O Timings: shared read=1.362
        ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..4.113 rows=51377 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
        ->  Index Only Scan using images_a_h3_cell_idx on images_a  (cost=0.43..6.68 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=51377)
              Index Cond: (h3_cell = (unnest('{...}'::h3index[])))
              Heap Fetches: 354
              Buffers: shared hit=154681 read=328
              I/O Timings: shared read=1.362
Planning Time: 139.421 ms
Execution Time: 74.345 ms

Enquanto na tabela menor de 1,4 milhões de linhas, o plano e a execução são estes:

Aggregate  (cost=105040.78..105040.79 rows=1 width=8) (actual time=327.586..327.587 rows=1 loops=1)
  Buffers: shared hit=148358 read=6315 written=41
  I/O Timings: shared read=26.521 write=0.327
  ->  Merge Join  (cost=4791.05..104802.14 rows=95455 width=0) (actual time=321.174..327.575 rows=118 loops=1)
        Merge Cond: (ptilmi.h3_cell = (unnest('{...}'::h3index[])))
        Buffers: shared hit=148358 read=6315 written=41
        I/O Timings: shared read=26.521 write=0.327
        ->  Index Only Scan using images_b_h3_cell_idx on images_b ptilmi  (cost=0.43..95041.10 rows=1415438 width=8) (actual time=0.026..245.897 rows=964987 loops=1)
              Heap Fetches: 469832
              Buffers: shared hit=148358 read=6315 written=41
              I/O Timings: shared read=26.521 write=0.327
        ->  Sort  (cost=4790.62..4919.07 rows=51377 width=8) (actual time=11.181..13.551 rows=51390 loops=1)
              Sort Key: (unnest('{...}'::h3index[]))
              Sort Method: quicksort  Memory: 1537kB
              ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..3.716 rows=51377 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 146.617 ms
Execution Time: 327.626 ms

No caso de uma matriz de origem menor, por exemplo, de tamanho 25.000, o plano na tabela menor muda para o primeiro (loop aninhado) e seu tempo de execução se torna mais alinhado com as expectativas (mais rápido do que na tabela maior).

Não consigo entender o que desencadeia essa mudança de plano para um menos eficiente.

Observe que estou usando CTE+JOIN em vez de eg WHERE h3_cell = ANY(h3_linestring_to_cells(:line_string, 13, 1)), pois o array resultante costuma ser bem grande e descobri que o primeiro método costuma ser mais eficiente neste caso. Curiosamente, com um array de 50.000 entradas, a = ANY()abordagem é mais rápida na tabela menor; com 25.000, é mais lenta.

Em primeiro lugar, como estou usando SET XACT_ABORT ON. Se ocorrer uma exceção em qualquer uma das instruções " INSERTou UPDATE" dentro do bloco de transação, ele deve encerrar e reverter toda a transação . Ou seja, se houver um erro em qualquer uma das instruções " INSERTe UPDATE" dentro do BEGIN TRAN. Então ele deve fazer o ROLLBACK da transação automaticamente .

Além disso, estou usando o SQL Server 2017. No meu script, inicializei uma variável de tabela DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50));e ela está temporariamente implementada no meu script para depurar meu procedimento armazenado devido à falta de recursos de depuração integrados no meu IDE do SQL Server Management Studio.

Aqui está meu script T-SQL completo:

SET XACT_ABORT ON; -- To automatically rollback the entire transaction if an exception is encountered
                    
BEGIN TRAN;

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

SELECT TOP 1
    @d_pay_method_id = pay_method_id
FROM
    [testDB].[dbo].[Payment]
WHERE
    method_name LIKE '%Bank Transfer%';
            
SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'PENDING';

SET @d_batch_code = 'BGUID' + '-' + CONVERT(VARCHAR,GETDATE(),23)
    + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13);--DATETIME2(4)-&-12-RANDOM-CODE

INSERT INTO -- first batch
    [testDB].[dbo].[Orders]
(
    order_id,
    batch_code,
    quantity,
    cart_id,
    user_id,
    pay_method_id,
    stat_ref_id
)
OUTPUT
    INSERTED.order_id,
    INSERTED.cart_id
INTO
    @order_id_tbl
SELECT
    'ORDER' + '-' + CONVERT(VARCHAR,GETDATE(),23)
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13) AS order_id,--DATE-&-12-RANDOM-CODE
    @d_batch_code AS batch_code,
    cart.quantity,
    cart.cart_id,
    @user_id AS user_id,
    @d_pay_method_id AS pay_method_id,
    @d_stat_ref_id AS stat_ref_id
FROM
    [testDB].[dbo].[PinkBasket] AS cart
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    cart.stat_ref_id = stat_ref.stat_ref_id
WHERE
    cart.user_id = @user_id
    AND cart.disable = 0
    AND cart.is_selected = 1
    AND cart.is_purchased = 0
    AND cart.is_wishlisted = 0
    AND stat_ref.com_usd_wrds = 'PENDING';

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('first set');

END

INSERT INTO -- first batch
    [testDB].[dbo].[Payment]
(
    cnb_pay_id,
    pay_ref_no,
    amount_to_pay,
    order_id
)
SELECT
    'PGUID' + '-' + REPLACE(REPLACE(REPLACE(CAST(sysdatetime() AS DATETIME2(4)), ' ', '-'), ':', '-'), '.', '-')
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')),1,13), --DATETIME2(4)-&-12-RANDOM-CODE
    @pay_ref_no,
    @amount_to_pay,
    tempData.order_id
FROM
    @order_id_tbl AS tempData;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('second set');

END

SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'ORDERED';

UPDATE -- first batch
    [testDB].[dbo].[PinkBasket]
SET
    stat_ref_id = @d_stat_ref_id,
    is_purchased = 1
WHERE
    cart_id IN
        (
            SELECT
                tempData.cart_id
            FROM
                @order_id_tbl AS tempData
        );

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('third set');

END

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

IF (@place_order_fbatch_cntr + @place_order_sbatch_cntr) >= 4 --@place_order_fbatch_cntr should be 3, and @place_order_sbatch_cntr should be greater than or equal to 1

BEGIN

    COMMIT TRAN;

    SELECT @successful AS json_data;

END

ELSE

BEGIN

    ROLLBACK TRAN;

    SELECT(SELECT exec_checker FROM @cust_debugger FOR JSON PATH, INCLUDE_NULL_VALUES) AS json_data; -- debugging purposes only

END

Cada vez que executei meu procedimento armazenado, recebi esta saída (no meu serviço de API Express.js):

spOutput = "[{\"exec_checker\":\"first set\"},{\"exec_checker\":\"second set\"},{\"exec_checker\":\"third set\"}]"

Você notará que a variável de tabela @cust_debugger obteve com sucesso as três primeiras seções de IF @@ROWCOUNT > 0validação (ou seja, com uma mistura de duas INSERTe uma UPDATEinstrução).

O problema é: por que as duas últimas seções não são executadas corretamente? Mas, como declarei SET XACT_ABORT ONantes BEGIN TRAN, não há exceção durante o processo de transação.

Entretanto, se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON(para fins de teste e investigação), aqui está o seguinte script:

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

SELECT * FROM @cust_debugger

A seguinte saída está sendo gerada:

Minha pergunta é: por que o quarto e o quinto conjuntos funcionam corretamente somente quando executados sem BEGIN TRAN, COMMIT TRAN, e ROLLBACK TRAN?

Se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON), então ele deverá executar a IF @@ROWCOUNT > 0instrução. (ou seja, a variável também deverá ser incrementada: SET @place_order_sbatch_cntr += 1;).

Por outro lado, descobri que as tabelas Producte SubProducttêm um TRIGGER definido, aqui estão as seguintes funções TRIGGER:

GATILHO do produto:

ALTER TRIGGER [dbo].[ProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[Product]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.ProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_id = d.prod_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.ProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.Product
        WHERE
            prod_id IN (SELECT prod_id FROM deleted);
    END
END

GATILHO do subproduto:

ALTER TRIGGER [dbo].[SubProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[SubProduct]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.SubProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_var_id = d.prod_var_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.SubProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.SubProduct
        WHERE
            prod_var_id IN (SELECT prod_var_id FROM deleted);
    END
END

Ainda assim, é questionável por que,

se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRAN e SET XACT_ABORT ON)

Apesar de desabilitar os dois TRIGGERs.

e após novos testes, o problema permanece.

Aqui está o que tenho pensado para resolver meu caso:

• É recomendável separar o processo de leitura complexa (SELECT) para minhas duas últimas seções? Ou seja, preciso passar os dados do SELECT para uma tabela temporária ou variável de tabela. Então, esses dados armazenados serão eventualmente processados ​​com o UPDATE com menor complexidade devido ao conceito de separação de responsabilidades.

• I want to know how to review the processing of my script where the issue arises. Such as if I could review why the UPDATE process doesn't update, then I could also decide what measures of response I needed to take.

• How to check for Locks / Deadlocks / Blocking, hoping that it might cause the unexpected outcomes.

Here are my assumptions that might be causing the conflicts:

• For the Product and SubProduct tables, there are triggers in each of the two tables. But it is defined with INSTEAD OF DELETE. Could it affect the process? Ahhh, because UPDATE statements are also deleting a record, right? The first thing it does is DELETE, and the second is INSERT. Because that's how UPDATE works under the hood of SQL Server, right? I forgot to look at it; it seems it was because of the TRIGGER.

• According to what I've observed. When you create a TRIGGER. You can use the INSERTED table and the DELETED table (if it's handling an UPDATE statement). When you UPDATE a table, it will delete the record first, then it will be stored in the DELETED table, then the DELETED table will pass the data into the INSERTED table and use the argument that is passed to the UPDATE statement instead. Take note, I'm not sure how SQL Server processes the DELETED table and INSERTED table when the UPDATE triggers.

• Since I am using a test server. Here's the screenshot of the test server specifications:

• Is 8GB RAM and 178 GB of storage insufficient for testing purposes?

Does anyone know why this is happening?

Preciso de uma consulta que possa ser executada no banco de dados Postgres de um usuário e determinar se ele tem permissão para criar procedimentos/funções. Precisa ser uma consulta apenas porque se trata de uma verificação de inicialização do software que distribuo, e os usuários o conectam aos seus próprios bancos de dados, que às vezes são compartilhados e eles não têm permissões de procedimento.

Preciso de uma maneira somente SQL para determinar se eles têm a capacidade de usar CREATE FUNCTION. A documentação afirma que:

Para poder criar uma função, você deve ter privilégio USAGE nos tipos de argumento e no tipo de retorno.

Tentei usar este SQL, mas não tenho certeza se ele me dará algo útil.

SELECT * FROM information_schema.role_usage_grants 
WHERE grantee = 'postgres';

postgres    postgres    prism_v4    information_schema  cardinal_number DOMAIN  USAGE   YES
postgres    postgres    prism_v4    information_schema  character_data  DOMAIN  USAGE   YES
postgres    postgres    prism_v4    information_schema  sql_identifier  DOMAIN  USAGE   YES
postgres    postgres    prism_v4    information_schema  time_stamp  DOMAIN  USAGE   YES
postgres    postgres    prism_v4    information_schema  yes_or_no   DOMAIN  USAGE   YES

No MySQL posso usar SHOW GRANTS FOR CURRENT_USER();e verificarCREATE_ROUTINE