Digamos que temos duas tabelas usere book.

No nível do aplicativo, para garantir a integridade dos dados e evitar condições de corrida, bloqueamos a entidade no início da transação.

Questão 1:

Digamos que no nível do aplicativo temos duas solicitações em execução ao mesmo tempo:

Solicitação 1 (nível do aplicativo):

start trx
lock for update user with id 1
lock for update book with id 2
do changes
commit

Solicitação 2 (nível do aplicativo):

start trx
lock for update book with id 2
lock for update user with id 1
do changes
commit

Pode acontecer que ambos os primeiros bloqueios da solicitação sejam aplicados antes dos segundos bloqueios, solicitação de 1 bloqueio do usuário 1 e solicitação de 2 bloqueios do livro 2, o que resultará em bloqueio morto.

Como podemos evitar isso? Minha única idéia é escrever em algum lugar uma ordem de bloqueio para todas as tabelas user->booke garantir que a sigamos.

Questão 2:

Se fizermos duas consultas como essa em solicitações diferentes para bloquear entidades antes da mutação

• Solicitação 1SELECT * FROM user WHERE id IN [1,2] FOR UPDATE
• Solicitação 2SELECT * FROM user WHERE id IN [2,1] FOR UPDATE

Isso pode resultar em impasse? Por exemplo, se UMA consulta por algum motivo bloquear o usuário 1 primeiro e depois mudar para a solicitação 2 e depois voltar para bloquear o usuário 2.

Estou carregando alguns dados em meu banco de dados postgres usando um script python. Esses dados estão todos em arquivos CSV enormes, então eu os leio preguiçosamente e chamo instruções INSERT em lotes de dados. No entanto, esses dados estão todos "conectados" de alguma forma, por isso é bom manter todo o pipeline de carregamento de dados como uma única transação, para que, se alguma parte dele falhar, tudo possa ser revertido.

Minha pergunta é: estou me esforçando para manter esses enormes arquivos csv fora da memória em meu script python, mas, como todo o carregamento de dados é uma única transação, todos os dados acabam na memória no lado do postgres das coisas? Uma única transação INSERT precisa acomodar todos os seus dados inteiramente na memória?

Os documentos dizem

Se a assinatura tiver sido configurada com run_as_owner = true, nenhuma troca de usuário ocorrerá. Em vez disso, todas as operações serão realizadas com as permissões do proprietário da assinatura. Nesse caso, o proprietário da assinatura só precisa de privilégios para SELECT, INSERT, UPDATE e DELETE da tabela de destino e não precisa de privilégios para SET ROLE para o proprietário da tabela. No entanto, isso também significa que qualquer usuário que possua uma tabela na qual a replicação esteja acontecendo pode executar código arbitrário com os privilégios do proprietário da assinatura. Por exemplo, eles poderiam fazer isso simplesmente anexando um gatilho a uma das tabelas de sua propriedade. Como geralmente é indesejável permitir que uma função assuma livremente os privilégios de outra, esta opção deve ser evitada, a menos que a segurança do usuário no banco de dados não seja motivo de preocupação.

Não tenho certeza se entendi por que isso é um risco à segurança. Não é sempre que o proprietário da tabela pode forçar a execução de código em funções que usam sua tabela? Minha intuição ingênua é que o proprietário da tabela é uma função sensível precisamente porque possui esses privilégios, enquanto a função de proprietário da assinatura pode ter privilégios muito restritivos (apenas CRUD em suas tabelas). Por que essa intuição está errada?

Meu sistema (OpenMage/Magento 1) está executando algumas consultas como as seguintes e, embora não esteja usando tabelas grandes, parece que elas levam um tempo anormalmente alto para serem executadas. Às vezes eles demoram alguns minutos, às vezes eles expiram depois de um dia.

Por exemplo

# Time: 240405  7:37:19
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8345298  Schema: db_example3  QC_hit: No
# Query_time: 140.075668  Lock_time: 0.000176  Rows_sent: 1  Rows_examined: 15
# Rows_affected: 0  Bytes_sent: 148
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 253976
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  ram_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       const   2       2.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  gdiktia_idx     ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID 8       db_example3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  primary_camera_idx      range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       2.00    75.00   25.00   Using where; Using index; Using join buffer (flat, BNL join)
# explain: 1    SIMPLE  screen_resolution_idx   eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  memory_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_example3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  core_count_idx  eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_example3.ram_idx.entity_id,const       1       1.00    100.00  100.00  Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.ram_idx.entity_id  1       1.00    100.00  100.00  Using index
#
SET timestamp=1712291839;
SELECT `gdiktia_idx`.`value`, COUNT(gdiktia_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '234'
 INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41'
 INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54'
 INNER JOIN `catalog_product_index_eav` AS `memory_idx` ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND memory_idx.store_id = 1 AND memory_idx.value = '62'
 INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '315'
 INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '90'
 INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN `catalog_product_index_eav` AS `gdiktia_idx` ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = 232 AND gdiktia_idx.store_id = '1' GROUP BY `gdiktia_idx`.`value`;

ou

# Time: 240405  7:34:29
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8344334  Schema: db_example3  QC_hit: No
# Query_time: 74.418149  Lock_time: 0.000100  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 142
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  extra_specs_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID      2       const   1       0.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  manufacturer2_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  primary_camera_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  ram_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  operating_system_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_example3.extra_specs_idx.entity_id,const,const      1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_example3.extra_specs_idx.entity_id,const       1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.extra_specs_idx.entity_id  1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  screen_type_idx range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       NULL    75.00   NULL    Using where; Using index; Using join buffer (flat, BNL join)
#
SET timestamp=1712291669;
SELECT `extra_specs_idx`.`value`, COUNT(extra_specs_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_product_index_eav` AS `manufacturer2_idx` ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND manufacturer2_idx.store_id = 1 AND manufacturer2_idx.value = '6'
 INNER JOIN `catalog_product_index_eav` AS `screen_type_idx` ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '37'
 INNER JOIN `catalog_product_index_eav` AS `operating_system_idx` ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
 INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309'
 INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '87'
 INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '52'
 INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN `catalog_product_index_eav` AS `extra_specs_idx` ON extra_specs_idx.entity_id = e.entity_id AND extra_specs_idx.attribute_id = 213 AND extra_specs_idx.store_id = '1' WHERE ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) >= 99.995000) AND ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) < 199.995000) GROUP BY `extra_specs_idx`.`value`;

Pelo que posso ver as tabelas estão usando índices e geralmente nada é escrito no disco, tudo está acontecendo na memória, então não tenho certeza de como otimizá-las e fazê-las rodar mais rápido.

Minha versão do mariadb é

Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution

Aqui estão as definições da tabela

create table db_example3.catalog_category_product_index
(
    category_id int unsigned      default 0 not null comment 'Category ID',
    product_id  int unsigned      default 0 not null comment 'Product ID',
    position    int                         null comment 'Position',
    is_parent   smallint unsigned default 0 not null comment 'Is Parent',
    store_id    smallint unsigned default 0 not null comment 'Store ID',
    visibility  smallint unsigned           not null comment 'Visibility',
    primary key (category_id, product_id, store_id),
    constraint FK_CATALOG_CATEGORY_PRODUCT_INDEX_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
        foreign key (category_id) references db_example3.catalog_category_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (product_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Category Product Index' row_format = COMPRESSED;

create index `15D3C269665C74C2219037D534F4B0DC`
    on db_example3.catalog_category_product_index (store_id, category_id, visibility, is_parent, position);

create index IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY
    on db_example3.catalog_category_product_index (product_id, store_id, category_id, visibility);

create table db_example3.catalog_product_index_eav
(
    entity_id    int unsigned      not null comment 'Entity ID',
    attribute_id smallint unsigned not null comment 'Attribute ID',
    store_id     smallint unsigned not null comment 'Store ID',
    value        int unsigned      not null comment 'Value',
    primary key (entity_id, attribute_id, store_id, value),
    constraint FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references db_example3.eav_attribute (attribute_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product EAV Index Table' row_format = COMPRESSED;

create index IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID
    on db_example3.catalog_product_index_eav (attribute_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID
    on db_example3.catalog_product_index_eav (entity_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID
    on db_example3.catalog_product_index_eav (store_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE
    on db_example3.catalog_product_index_eav (value);

create index idx_attribute_store_value_entity
    on db_example3.catalog_product_index_eav (attribute_id, store_id, value, entity_id);

create table db_example3.catalog_product_index_price
(
    entity_id         int unsigned                not null comment 'Entity ID',
    customer_group_id smallint unsigned           not null comment 'Customer Group ID',
    website_id        smallint unsigned           not null comment 'Website ID',
    tax_class_id      smallint unsigned default 0 null comment 'Tax Class ID',
    price             decimal(12, 4)              null comment 'Price',
    final_price       decimal(12, 4)              null comment 'Final Price',
    min_price         decimal(12, 4)              null comment 'Min Price',
    max_price         decimal(12, 4)              null comment 'Max Price',
    tier_price        decimal(12, 4)              null comment 'Tier Price',
    group_price       decimal(12, 4)              null comment 'Group price',
    primary key (entity_id, customer_group_id, website_id),
    constraint FK_CAT_PRD_IDX_PRICE_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID
        foreign key (customer_group_id) references db_example3.customer_group (customer_group_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_WS_ID_CORE_WS_WS_ID
        foreign key (website_id) references db_example3.core_website (website_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product Price Index Table' row_format = COMPRESSED;

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID
    on db_example3.catalog_product_index_price (customer_group_id);

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE
    on db_example3.catalog_product_index_price (min_price);

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID
    on db_example3.catalog_product_index_price (website_id);

create index IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
    on db_example3.catalog_product_index_price (website_id, customer_group_id, min_price);

As linhas contadas em cada tabela

select count(*) from catalog_product_index_eav; #418
select count(*) from catalog_product_index_price; #84
select count(*) from catalog_category_product_index; # 314

O servidor possui um processador AMD Ryzen 9 3900 de 12 núcleos com 128 GB de RAM e discos Nvme.

Qualquer ajuda é apreciada

Estou usando o PostgreSQL 13.3 em x86_64-pc-linux-gnu e estou tentando encontrar a causa raiz de:

ERROR:  out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction.

Então eu testei usando BEGIN ISOLATION LEVEL SERIALIZABLE; então consulte com condições, o problema é que mesmo o número de SIReadLock é maior que max_pred_locks_per_transaction*max_connections, ainda posso consultar, não há 'falta de memória compartilhada':

smart=*# show max_pred_locks_per_transaction;
 max_pred_locks_per_transaction 
--------------------------------
 10
(1 row)

smart=*# show max_connections;
 max_connections 
-----------------
 3
(1 row)

smart=*# SELECT count(*) FROM pg_locks WHERE mode = 'SIReadLock';
 count 
-------
   250
(1 row)

Então, por favor, ajude-me a indicar que SIReadLock é limitado por qual configuração? e qual configuração e bloqueio estão relacionados a 'Você pode precisar aumentar max_pred_locks_per_transaction'?

Obrigado.

Eu tenho uma tabela PostgresSQL que possui um campo para TIMESTAMP e um procedimento armazenado associado que pega INTERVALe calcula a diferença de now() + in_interval. Quero usar dbeaverpara testar, mas não consigo descobrir como fazer isso.

Eu tentei definir uma variável, por exemplo, @set interval = now() + 'INTERVAL 1 MINUTE'e usá-la com:interval

Usando uma seleção aninhada

SELECT insert_event('event_type', (select (now() + interval '1 minute')));

E algumas outras coisas que perdi, mas continuo recebendo erros. Isso é possível?

CREATE TABLE events (
    event      TEXT
    event_time TIMESTAMP
); 

E um sproc

CREATE OR REPLACE FUNCTION insert_event(
    in_event     TEXT
    in_interval  INTERVAL

)
RETURNS void
AS $$
DECLARE
    v_now                   TIMESTAMP := now() at time zone 'utc';
    v_end_time              TIMESTAMP := v_now + in_interval;
BEGIN
    ...use the value later on...
END

Eu conheço os usuários do postgres, mas não tenho uma ideia correta sobre eles. A diferença que eu sei é

• Usuário autenticado - O usuário que insere suas credenciais para fazer login no banco de dados postgres.
• Usuário da sessão - O usuário que possui a sessão, mas estou confuso sobre qual é a diferença entre sessão e usuário de autenticação
• Usuário externo - É algo relacionado ao definidor de função, mas não tenho ideia exata
• Usuário atual – A pessoa que está executando a consulta

Alguém pode me corrigir nas definições acima e se algum exemplo puder ser dado, também será útil

Então eu tenho uma tabela que possui uma coluna chamada 'sequência', na verdade não é um tipo de dados de sequência, apenas um campo numérico que se traduz no modelo de objeto para mostrar os itens em uma determinada ordem. Em alguns casos, devido à manipulação do banco de dados, alguns registros terão uma sequência duplicada, resultando na incapacidade de reordenar na IU. Noutros casos, existem lacunas na sequenciação (o que não é um problema grave). O que pretendo fazer é renumerar esses campos numéricos começando em 1, mas é necessário agrupá-los pelo campo 'co' e depois pelo campo de sequência existente que você vê abaixo:

Esta é a consulta que executei para obter os resultados que você vê:

SELECT row_number() OVER (ORDER BY C.co, T.sequence) AS RowNum, C.co, T.sequence
FROM TNode T
INNER JOIN CInfo C ON C.guidfield = T.contextID
    WHERE T.nodeTypeID = '0EC43D28-EA1F-4FBE-BA50-06161AFAB382'
    AND T.parentNodeID ='A276DF06-4569-11D5-8052-00D0B7696EF9'
GROUP BY C.co, T.sequence

O que estou procurando alcançar é isso. Observe como a sequência é ligeiramente renumerada em registros onde não é um 1, 2, 3 direto... mas tem que começar de 1 novamente cada vez que um novo 'co' é encontrado, e então também ordenar pela sequência existente que é lá :

Estamos vendo commits aguardando excessivamente WALWritedurante determinados momentos de alta carga. Não apenas certas sessões, mas todos os commits, até mesmo o vácuo. Ao tirar fotos pg_stat_all_tables, acho que pg_statio_all_tablesfica claro que durante esses momentos específicos, onde os WALWriteeventos são tão proeminentes, a culpa é de uma mesa em particular e sua mesa de brindes. WALWriteEle recebe inserções, atualizações e exclusões pesadas durante os momentos em que há muitas esperas. 'Pesado' tanto em termos de muitas sessões (cerca de 20) quanto em termos de quantidade de dados (grandes campos de texto que são torrados).

Olhando os instantâneos dos pg_stat_walmomentos em que as esperas acontecem, posso ver que:

• wal_recordspor segundo subiu de uma média de 2.000 para 10.000-20.000
• wal_fpipor segundo subiu de uma média de 150 para 500-1.000
• wal_kbytespor segundo subiu de uma média de 600 para 3.000-5.000
• wal_writee wal_syncpor segundo subiram de uma média de 50 para 200

Os números mencionados acima indicam pg_stat_walqual é o motivo dos eventos WALWrite?

• É o número de commits por segundo?
• É o tamanho dos commits (em megabytes)?
• É o número de sessões que são confirmadas ao mesmo tempo?

Se esses números não retratam uma imagem clara, como posso descobrir? Gostaria de consertar o gargalo, mas preciso identificá-lo primeiro.

Atualização nas especificações

• PG 14.1 no AWS RDS (db.r6i.4xlarge (16 vCPU, 128 GB de RAM) com 1 réplica (db.r6i.2xlarge).
• O tipo de armazenamento é SSD de uso geral (gp3) com IOPS provisionados de 12.000 IOPS e taxa de transferência de armazenamento de 500 MiBps.
• SEM volume de log dedicado para PostgreSQL
• checkpoint_timeout900
• max_wal_size 15360
• min_wal_size 8192
• synchronous_standby_namesNULO
• wal_levelréplica
• wal_buffers64 MB
• shared_buffers32 GB
• wal_compressionSOBRE
• commit_delay0
• synchronous_commitSOBRE

Eu criei uma tabela assim,

CREATE TABLE Leaderboard(
  userId bigint not null,              
  matchId bigint not null,
  score mediumint not null,
  country CHAR(10),
  tournamentId int not null
)

Estou criando partidas de 5 pessoas, todas de 5 países distintos ("Reino Unido", "EUA", "ESPANHA", "ALEMANHA", "FRANÇA)

Quero inserir cerca de um milhão de entradas nesta tabela com os seguintes requisitos,

Uma partida será formada assim

usergermany | userUSA | userSPAIN | userUK | userFrance 

então uma partida terá 5 usuários, todos de países distintos. Um torneio terá muitas partidas e um usuário só poderá jogar uma partida em um torneio.

Então, uma tabela de exemplo ficará assim

userid    matchid    score     country   tournamentid

...
988654    3877543    random    USA       177
388654    3877543    random    GERMANY   177
433432    3877543    random    FRANCE    177
776212    3877543    random    UK        177
1632987   3877543    random    SPAIN     177
2113242   3877544    random    SPAIN     177
2918974   3877544    random    USA       177
111738    3877544    random    UK        177
1772342   3877544    random    FRANCE    177
1343243   3877544    random    GERMANY   177
123131    3877545    random    UK        178
1231414   3877545    random    FRANCE    178
2858348   3877545    random    GERMANY   178
1122432   3877545    random    USA       178
2923434   3877545    random    SPAIN     178
...

Um userId não pode existir duas vezes em um torneio,

Um país não pode existir duas vezes em uma partida (os grupos de partidas são formados por países distintos)

Além disso, como 5 jogadores competem numa partida, um matchid só aparecerá 5 vezes na mesa.

Quero inserir um milhão de entradas nesta tabela, sendo o ID do usuário aleatoriamente de 1 a 3 milhões com as restrições acima. 10 mil partidas para cada torneio, então serão 100 torneios começando em 1.

100 torneios, 10.000 partidas cada, perfazem até 1 milhão de linhas.

--ATUALIZAR--

create table seq_data as
with recursive tmp(x) as (
    select 1
    union all
    select x+1 from tmp
    limit 3000000
)
select * from tmp;
 
CREATE TABLE if not exists Leaderboard(
  userId bigint not null,
  matchId bigint not null,
  score mediumint not null,
  country CHAR(10),
  tournamentId int not null
);
 
DELIMITER //
CREATE PROCEDURE InsertLeaderboardData()
BEGIN
  DECLARE tournamentloop INT DEFAULT 0;
  DECLARE matchloop INT DEFAULT 0;
  DECLARE groupLoop INT DEFAULT 0;
  DECLARE matchidKey INT DEFAULT 1;
  WHILE tournamentloop < 1 DO
    SET tournamentloop = tournamentloop + 1;
    SET matchloop = 0;
    WHILE matchloop < 10000 DO
      SET matchidKey = matchidKey + 1;
      SET matchloop = matchloop + 1;
      SET groupLoop = 0;
      WHILE groupLoop < 5 DO
       SET groupLoop = groupLoop + 1;
       INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId)
       VALUES (1, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);
       SELECT matchidKey;
      END WHILE;
    END WHILE;
  END WHILE;
END;
//
 
DELIMITER ;
CALL InsertLeaderboardData();
DROP PROCEDURE InsertLeaderboardData;

Isso funciona, mas lentamente, mas quero mais um recurso.

Quanto a INSERT INTOdefini userIdo valor como 1 por enquanto para editá-lo mais tarde. O que eu quero fazer é criar números sequenciados na tabela seq_data começando de 1 a 3 milhões. Para cada WHILE tournamentloop < 100 DOescopo, quero obter 50 mil números DISTINTOS da seq_datatabela. para que eu possa fornecer um ID aos usuários.

A moral é que nenhum userid pode aparecer duas vezes em um torneio. portanto, para cada escopo de torneio, preciso recuperar IDs aleatórios distintos e atribuí-los a linhas. Já criei a tabela para números sequenciados, não sei qual a melhor abordagem daqui.

-- Declaração

INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId) VALUES (useridKey, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);

Eu transformo isso em

DECLARE tournamentloop INT DEFAULT 0;
DECLARE matchloop INT DEFAULT 0;
DECLARE groupLoop INT DEFAULT 0;
DECLARE matchidKey INT DEFAULT 1;
DECLARE useridKey INT DEFAULT 1;
PREPARE insertStmt FROM 'INSERT INTO Leaderboard (userId, matchId, score, country, tournamentId)VALUES (useridKey, matchidKey, FLOOR(RAND() * 1000) + 1, ELT(groupLoop, "SPAIN", "FRANCE", "UK", "USA", "GERMANY"), tournamentloop);';
 EXECUTE insertStmt;

Eu recebo um erro:Unknown column 'useridKey' in 'field list'