Dolphin's questions

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.

Eu tenho uma tabela com assinaturas de usuários no meu Postgres 13 DB. Eu adicionei um índice GiST para não permitir intervalos de tempo sobrepostos para o mesmo usuário:

CREATE INDEX user_sub_exclusion_index ON public.user_sub USING gist (user_id, tstzrange(sub_start, sub_end))

Mas hoje descobri que não funcionou.
Esta é a tabela DDL:

-- DROP TABLE public.user_sub;
CREATE TABLE public.user_sub (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    app_id varchar NOT NULL,
    product_id int4 NOT NULL,
    iap_product_id int8 NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    user_id int8 NOT NULL,
    sub_start_time int8 NOT NULL DEFAULT 0,
    sub_end_time int8 NOT NULL DEFAULT 0,
    enabled int2 NOT NULL DEFAULT 1,
    order_id varchar NOT NULL,
    sub_start timestamptz NOT NULL,
    sub_end timestamptz NOT NULL,
    CONSTRAINT user_sub_new_pk PRIMARY KEY (id),
    CONSTRAINT user_sub_new_un UNIQUE (order_id)
);

CREATE INDEX user_sub_exclusion_index ON public.user_sub USING gist (user_id, tstzrange(sub_start, sub_end));
CREATE INDEX user_sub_tstzrange_user_id_product_id_excl ON public.user_sub USING gist (tstzrange(sub_start, sub_end, '[]'::text), user_id, product_id);

Aqui está uma amostra dos dados da tabela agora: ela contém intervalos de tempo sobrepostos para o mesmo usuário:

INSERT INTO public.user_sub (app_id, product_id, iap_product_id, created_time, updated_time, user_id, sub_start_time, sub_end_time, enabled, order_id, sub_start, sub_end)
VALUES
  ('vOghoo10L9',  9,  6, 1680251663942, 1680251663942, 77, 1680501039421, 1680587439421, 1, '627599858277646336', '2023-04-03 13:50:39.421', '2023-04-04 13:50:39.421')
, ('vOghoo10L9',  9,  6, 1680263287925, 1680263287925, 77, 1680587439422, 1680673839422, 1, '627697298195189760', '2023-04-04 13:50:39.422', '2023-04-05 13:50:39.422')
, ('vOghoo10L9',  9,  6, 1680263497256, 1680263497256, 77, 1680673839423, 1680760239423, 1, '627679779371601920', '2023-04-05 13:50:39.423', '2023-04-06 13:50:39.423')
, ('vOghoo10L9',  9,  6, 1680263539890, 1680263539890, 77, 1680760239424, 1680846639424, 1, '627680143827259392', '2023-04-06 13:50:39.424', '2023-04-07 13:50:39.424')
, ('vOghoo10L9',  9,  6, 1680273609032, 1680273609032, 77, 1680846639425, 1680933039425, 1, '627601223242579968', '2023-04-07 13:50:39.425', '2023-04-08 13:50:39.425')
, ('vOghoo10L9',  9,  6, 1680275903068, 1680275903068, 77, 1680933039426, 1681019439426, 1, '627610724383956992', '2023-04-08 13:50:39.426', '2023-04-09 13:50:39.426')
, ('vOghoo10L9',  9,  6, 1680276434561, 1680276434561, 77, 1681019439427, 1681105839427, 1, '627612898614681600', '2023-04-09 13:50:39.427', '2023-04-10 13:50:39.427')
, ('vOghoo10L9',  9,  6, 1680277832740, 1680277832740, 77, 1681105839428, 1681192239428, 1, '627618880539664384', '2023-04-10 13:50:39.428', '2023-04-11 13:50:39.428')
, ('vOghoo10L9',  9,  6, 1680282545888, 1680282545888, 77, 1681192239429, 1681278639429, 1, '627411682153152512', '2023-04-11 13:50:39.429', '2023-04-12 13:50:39.429')
, ('vOghoo10L9',  9,  6, 1680327772145, 1680327772145, 77, 1681278639430, 1681365039430, 1, '627601513341616128', '2023-04-12 13:50:39.430', '2023-04-13 13:50:39.430')
, ('vOghoo10L9',  9,  5, 1680761228691, 1680761228691, 79, 1680761228687, 1680847628687, 1, '629789245600776192', '2023-04-06 14:07:08.687', '2023-04-07 14:07:08.687')
, ('vOghoo10L9',  9,  5, 1680763488691, 1680763488691, 77, 1680763488689, 1680849888689, 1, '629798689575354368', '2023-04-06 14:44:48.689', '2023-04-07 14:44:48.689')
, ('vOghoo10L9',  9,  5, 1680763634694, 1680763634694, 77, 1680849888690, 1688712288690, 1, '629799331018653696', '2023-04-07 14:44:48.690', '2023-07-07 14:44:48.690')
, ('vOghoo10L9',  9,  6, 1680850885924, 1680850885924, 80, 1680850881709, 1680937281709, 1, '630165239553671168', '2023-04-07 15:01:21.709', '2023-04-08 15:01:21.709')
, ('vOghoo10L9',  9,  6, 1681461059268, 1681461059268, 81, 1681461059263, 1681547459263, 1, '632723434021126144', '2023-04-14 16:30:59.263', '2023-04-15 16:30:59.263')
, ('vOghoo10L9',  9,  6, 1681483885008, 1681483885008, 82, 1681483885006, 1681570285006, 1, '632820205569245184', '2023-04-14 22:51:25.006', '2023-04-15 22:51:25.006')
, ('iYDFo0PQQX', 11, 14, 1682762462986, 1682762462986, 86, 1682762462971, 1682848862971, 1, '638182956129267712', '2023-04-29 18:01:02.971', '2023-04-30 18:01:02.971')
, ('iYDFo0PQQX', 11, 14, 1682764832099, 1682764832099, 86, 1682764832090, 1682851232090, 1, '638192942803423232', '2023-04-29 18:40:32.090', '2023-04-30 18:40:32.090')
, ('iYDFo0PQQX', 11, 14, 1682765939081, 1682765939081, 86, 1682765939052, 1682852339052, 1, '638197106476421120', '2023-04-29 18:58:59.052', '2023-04-30 18:58:59.052')
, ('iYDFo0PQQX', 11, 14, 1682766222551, 1682766222551, 86, 1682766193447, 1682852593447, 1, '638198676681232384', '2023-04-29 19:03:13.447', '2023-04-30 19:03:13.447')
;

Estou esquecendo de algo? Como fazer essa restrição funcionar? Ou seja, para evitar que o mesmo usuário assine com intervalos de tempo sobrepostos.

Eu tenho uma tabela que armazena informações de assinatura do usuário, agora quero adicionar uma restrição para fazer com que o usuário não assine o produto várias vezes no mesmo período de tempo. O que estou tentando fazer assim:

CREATE INDEX user_sub_exclusion_index ON user_sub USING GIST (
  user_id,
  tsrange(sub_start_time, sub_end_time)
);

e criar restrição:

ALTER TABLE user_sub ADD CONSTRAINT user_sub_exclusion_constraint EXCLUDE USING GIST (
  user_id WITH =,
  tsrange(sub_start_time, sub_end_time) WITH &&
);

parece que o tsrange precisa do tipo de dados timestamp. Eu tentei a função de intervalo, mas não funcionou. o que devo fazer para que o gist index funcione e não altere o tipo de dado? Esta é a tabela DDL:

CREATE TABLE public.user_sub (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    app_id varchar NOT NULL,
    product_id int4 NOT NULL,
    iap_product_id varchar NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    user_id int8 NOT NULL,
    sub_start_time int8 NOT NULL DEFAULT 0,
    sub_end_time int8 NOT NULL DEFAULT 0,
    enabled int2 NOT NULL DEFAULT 1,
    order_id varchar NOT NULL,
    CONSTRAINT user_sub_new_pk PRIMARY KEY (id),
    CONSTRAINT user_sub_new_un UNIQUE (order_id)
);

Eu tentei alterar o campo para timestamptz e ajustar o sql assim:

CREATE INDEX user_sub_exclusion_index ON user_sub USING GIST (
  user_id,
  tstzrange(sub_start, sub_end)
);

mostrar erro:

SQL Error [42704]: ERROR: data type bigint has no default operator class for access method "gist"
  Hint: You must specify an operator class for the index or define a default operator class for the data type.

o que devo fazer para adicionar a restrição?

Quando eu quero usar este sql para verificar usando índice ou não:

explain SELECT id AS id, sub_source_id AS sub_source_id 
FROM article 
WHERE sub_source_id IN (
select sub_source_id 
    from sub_relation sr 
    where user_id =14
    and sub_status = 1

) ORDER BY created_time DESC LIMIT 50

mostrar informações como esta:

    Workers Planned: 2
    ->  Sort  (cost=74344.09..74697.64 rows=141422 width=24)
          Sort Key: article.created_time DESC
          ->  Hash Join  (cost=77.11..69646.15 rows=141422 width=24)
                Hash Cond: (article.sub_source_id = sr.sub_source_id)
                ->  Parallel Seq Scan on article  (cost=0.00..67792.05 rows=675805 width=24)
                ->  Hash  (cost=70.56..70.56 rows=524 width=8)
                      ->  Seq Scan on sub_relation sr  (cost=0.00..70.56 rows=524 width=8)
                            Filter: ((user_id = 14) AND (sub_status = 1))

Já criei multi-column index com column sub_source_idand created_time, porque o sql não usou esse article_sub_source_id_create_time_idx índice? Esta é a estrutura da tabela:

-- public.article definition

-- Drop table

-- DROP TABLE public.article;

CREATE TABLE public.article (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    user_id int8 NOT NULL,
    title varchar(512) NOT NULL,
    author varchar(256) NOT NULL,
    guid varchar(512) NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    link varchar(512) NULL,
    pub_time timestamptz NULL,
    sub_source_id int8 NOT NULL,
    cover_image varchar(1024) NULL,
    channel_reputation int4 NOT NULL DEFAULT 0,
    editor_pick int4 NULL DEFAULT 0,
    permanent_store int2 NOT NULL DEFAULT 0,
    CONSTRAINT article_id_seq_pk PRIMARY KEY (id),
    CONSTRAINT article_title_sub_source_id_key UNIQUE (title, sub_source_id)
);
CREATE INDEX article_sub_source_id_create_time_idx ON public.article USING btree (sub_source_id, created_time);
CREATE INDEX article_title_gin ON public.article USING gin (to_tsvector('english'::regconfig, (title)::text));
CREATE INDEX article_title_zh_gin ON public.article USING gin (to_tsvector('dolphinzhcfg'::regconfig, (title)::text));

quando removo a subconsulta e a substituo pelo id de sub fonte estática, a consulta externa pode usar o article_sub_source_id_create_time_idx index.

Eu armazenei dados jsonb no PostgreSQL 13 assim:

[{"code": "OFFICIAL"}, {"code": "FULLTEXT"}]

como consultar o tipo de dados jsonb? Eu tentei assim:

select count(*) from test where tags::jsonb ? 'code';
select count(*) from test where tags::jsonb ->> 'code' = 'OFFICIAL';
select count(*) from test where tags::jsonb @> '{"code": "OFFICIAL"}';

ambos não poderiam funcionar. esta é a tabela DDL:

CREATE TABLE public.test (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    tags jsonb NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
);

o que devo fazer para que funcione? Também tentei assim:

select * from (
    select jsonb_array_elements(tags) as tt from test
) a
where  tt -> 'code' = 'OFFICIAL'

Eu mudei a configuração de log no postgres.confarquivo do servidor assim:

log_filename = 'postgresql-%i.log'

então eu executo este comando no lado do servidor:

pg_ctl reload;

o novo nome do arquivo de log parece não ter gerado, é possível recarregar esta configuração e fazer efeito? Eu também executei este comando:

SELECT pg_reload_conf();

o log continua assim:

[root@k8smasterone log]# ls -alh
total 368M
drwx------  2 1000 1000 4.0K Jul 29 08:00 .
drwx------ 20 1000 1000 4.0K Jul 29 18:59 ..
-rw-------  1 1000 1000  81M Jul 28 08:00 postgresql-2022-07-27_143753.log
-rw-------  1 1000 1000  53M Jul 28 14:10 postgresql-2022-07-28_000000.log
-rw-------  1 1000 root 140M Jul 29 08:00 postgresql-2022-07-28_061040.log
-rw-------  1 1000 root  95M Jul 29 19:03 postgresql-2022-07-29_000000.log

eu tive que reiniciar o servidor postgresql?

Quantas conexões devo definir para o PostgreSQL (versão 13.x)? pelo núcleo da CPU? o tamanho da memória? qual valor devo escolher? como estimar o valor da conexão para o PostgreSQL? devo estimar assim?

(single_thread_memory） = thread_stack（256KB） + binlog_cache_size（32KB） + join_buffer_size（256KB） + sort_buffer_size（256KB） + read_buffer_size（128KB） + read_rnd_buffer_size（256KB）= 1MB

Eu tenho duas tabelas article e article_content no PostgreSQL, o article_id na tabela article_content era o id da tabela do artigo. Agora o sql está assim:

select * 
from article_content ac 
where article_id not in(
   select id from article a 
)
limit 10

encontre o registro em article_content que não existe em article. esta é a consulta simples:

Limit  (cost=1000.00..44996.68 rows=1 width=415)
  ->  Gather  (cost=1000.00..38955542254.16 rows=885420 width=415)
        Workers Planned: 2
        ->  Parallel Seq Scan on article_content ac  (cost=0.00..38955452712.16 rows=368925 width=415)
              Filter: (NOT (SubPlan 1))
              SubPlan 1
                ->  Materialize  (cost=0.00..101153.51 rows=1775167 width=8)
                      ->  Seq Scan on article a  (cost=0.00..85342.67 rows=1775167 width=8)

agora a tabela article e article_content têm tantas linhas. parece que este sql não pode ser concluído para sempre. o que devo fazer para remover as linhas de conteúdo do artigo que não existiam no artigo?

Uma vez VACUUM FULLque as tabelas são bloqueadas, é inaceitável para nós em nosso ambiente de produção.

É possível executar apenas VACUUMe nunca VACUUM FULLem um sistema de produção?

Torna VACUUMo espaço reutilizável para novos INSERTs?

Eu tenho uma tabela de menus no PostgreSQL 13 assim:

-- Drop table

-- DROP TABLE public.menu_resource;

CREATE TABLE public.menu_resource (
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" varchar NOT NULL,
    res_type int4 NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    remark varchar NULL,
    "path" varchar NOT NULL,
    parent_id int4 NOT NULL,
    component varchar NULL,
    sort int4 NOT NULL,
    name_zh varchar NOT NULL,
    tree_id_path varchar NULL,
    CONSTRAINT auth_resource_id_seq PRIMARY KEY (id)
);

e os dados do menu se parecem com isso com uma estrutura de árvore marcada por parent_ide id:

INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(13, 'job', 1, 1632389739938, 1632389739938, NULL, '/app/job', 1, NULL, 2, '求职管理', '5-1-13');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(14, 'interview', 1, 1632389739938, 1632389739938, NULL, '/app/job/interview', 13, NULL, 2, '面试列表', '5-1-13-14');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(15, 'menu', 1, 1632389739938, 1632389739938, NULL, '/privilege/menu', 2, './permission/menu', 0, '菜单列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(3, 'cruise', 1, 1632389739938, 1632389739938, NULL, '/app/cruise', 1, NULL, 2, 'Cruise', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(5, '系统菜单', 1, 1632389739938, 1632389739938, NULL, '/demo', 0, NULL, 0, '系统菜单', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(4, 'channel', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/channel', 3, './apps/cruise/channel', 3, '频道', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(1, 'app', 1, 1632389739938, 1632389739938, NULL, '/app', 5, NULL, 1, '应用', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(2, 'privilege', 1, 1632389739938, 1632389739938, NULL, '/privilege', 5, NULL, 4, '权限管理', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(6, 'role', 1, 1632389739938, 1632389739938, NULL, '/privilege/role', 2, './permission/role', 5, '角色列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(7, 'article', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/article', 3, './apps/cruise/article', 3, '文章', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(8, 'user', 1, 1632389739938, 1632389739938, NULL, '/privilege/user', 2, './permission/user', 5, '用户列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(9, 'welcome', 1, 1632389739938, 1632389739938, NULL, '/welcome', 5, './Welcome', 0, '欢迎', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(10, 'overview', 1, 1632389739938, 1632389739938, NULL, '/app/overview', 1, NULL, 2, '应用概览', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(11, 'product', 1, 1632389739938, 1632389739938, NULL, '/app/overview/product', 10, NULL, 2, '产品列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(12, 'list', 1, 1632389739938, 1632389739938, NULL, '/app/overview/list', 10, NULL, 2, '应用列表', NULL);

agora eu quero gerar o caminho completo do id pai-filho, por exemplo, o tree_id_pathfor job pode ficar assim: 5-1-13, a entrevista pode parecer 5-1-13-14. o que devo fazer para que funcione assim?

Agora eu tenho uma string de dados como esta 2002年05月11日, quero converter essa string como um timestamp de data unix, o que devo fazer para converter desta vez? Eu tentei desta forma:

select STR_TO_DATE("2002年05月11日", "YYYY年MM月dd日")

não funcionou. Então eu tentei isso:

select STR_TO_DATE("2002年05月11日", "%Y年%M月%d日")

ainda não funcionou. o que devo fazer para convertê-lo em formato de data que possa entender por banco de dados?

Tenho uma tabela que não contém id, agora adiciono a coluna id e quero atualizar o id como número da linha, o que devo fazer? esta é a minha tabela DDL:

CREATE TABLE `spark_car_brand` (
  `alpha` varchar(255) DEFAULT NULL,
  `id` int(11) DEFAULT NULL,
  `brand_id` varchar(255) DEFAULT NULL,
  `brand_name` varchar(255) DEFAULT NULL,
  `brand_logo` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Estou usando este SQL parece que não funcionou:

update spark_car_brand
set id = ROW_NUMBER();

Estou usando este sql para fazer um group by no MySQL 5.7, quando quero agrupar por mês:

select
    max(statistic_time) as statistic_time
    from home_overview_daily
    group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m')
    order by statistic_time desc

mas quando eu agrupo por semana:

select
        max(statistic_time) as statistic_time
        from home_overview_daily
        group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%w')
        order by statistic_time desc

os dados do resultado ficam assim:

1640789999999   2021-12-3
1640703599999   2021-12-2
1640617199999   2021-12-1
1640530799999   2021-12-0
1640444399999   2021-12-6
1640357999999   2021-12-5
1640271599999   2021-12-4
1638284399999   2021-11-2
1638197999999   2021-11-1
1638111599999   2021-11-0
1638025199999   2021-11-6
1637938799999   2021-11-5
1637852399999   2021-11-4
1637765999999   2021-11-3

por que o mês 12 tem 7 semanas? onde estou errando? Estou usando o formato assim %Y-%m-%u:

select
        max(statistic_time) as statistic_time,
        date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%u')
        from home_overview_daily
        group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%u')
        order by statistic_time desc

mas o resultado ficou assim:

1640789999999   2021-12-52
1640530799999   2021-12-51
1639925999999   2021-12-50
1639321199999   2021-12-49
1638716399999   2021-12-48
1638284399999   2021-11-48
1638111599999   2021-11-47
1637506799999   2021-11-46
1636901999999   2021-11-45
1636297199999   2021-11-44

cada mês tem 5 semanas?

Meu banco de dados (MySQL 5.7) armazena o formato de aniversário do usuário assim 1990年1月1日, agora quero calcular a idade do usuário a partir desse formato datetime. Meu sql se parece com isso, mas retorna null para todos os dados:

select period_diff(date_format(now(), '%Y'), date_format(birthday, '%Y年')) as months 
from spark_user;

a tabela DDL fica assim:

CREATE TABLE `spark_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `birthday` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4;

Eu também tentei assim:

select period_diff(date_format(now(), '%Y'), date_format(STR_TO_DATE(birthday, '%Y'), '%Y')) as months 
from spark_user;

isso funciona:

    select period_diff( date_format(STR_TO_DATE(birthday, '%Y年%m月%d日'), '%Y'),date_format(now(), '%Y')) as months,
birthday as birthday 
from spark_user;

mas o resultado é estranho:

57  1990年1月1日
57  1990年1月1日
57  1990年1月1日
57  1990年10月10日
47  1980年1月1日
-20 2001年1月1日
63  1996年10月10日

Na minha tabela armazena duas colunas de ID do usuário, o que estou tentando fazer é marcar o ID do usuário com par. Por exemplo, o ID do usuário assim:

user_1_id  user_2_id

  2          3
  3          2

pensamos que os dois gravam como um par (apenas dois usuários, mas a sequência é diferente). Eu adicionei uma nova coluna de geração que calcula com user_1_id + user_2_id, se o resultado for o mesmo, é um par. Mas neste momento estou enfrentando um novo problema que não funciona perfeito porque o usuário 0 e 5 estava em conflito com o registro, na verdade o 0 e 5 era um novo par de usuário. o que devo fazer para marcar o par de usuários perfeitamente? Eu tenho pensando neste problema por horas. esta é a informação real do usuário no meu projeto:

Esta é a minha tabela DDL:

CREATE TABLE `spark_game_user_contact` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  `target_user_id` bigint(20) NOT NULL,
  `is_exchange` int(11) NOT NULL DEFAULT '0',
  `is_whisper` int(11) NOT NULL DEFAULT '0',
  `pair_flag` bigint(20) GENERATED ALWAYS AS ((`user_id` + `target_user_id`)) STORED,
  `updated_time` bigint(20) NOT NULL,
  `created_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_gut` (`game_id`,`user_id`,`target_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4;

Estou usando pg_stat_statementspara encontrar as consultas lentas em minha produção PostgreSQL 13. Duas coisas:

• Não tenho certeza do impacto no desempenho dessa extensão.

• Existe algo que eu possa fazer para melhorar seu desempenho? A consulta abaixo leva > 1s - alguma sugestão?

    -- add the plugin 
    create extension pg_stat_statements;
    select pg_stat_reset();
    select pg_stat_statements_reset();

e depois:

-- find slow queries using the extension:

select * from pg_stat_statements order by total_exec_time desc limit 50;

Estou usando este comando para substituir a string da coluna PostgreSQL:

update sentence 
set sentence_en = replace(sentence_en,"1.  ","")

mas eu só quero substituir a primeira ocorrência do início esquerdo da coluna da tabela. o que devo fazer para que funcione como esperado? o resultado desejado que eu quero quando sentence_enassim: 1. xxxxxx1. xxxx1. , o resultado pode ser assim:xxxxxx1. xxxx1.

Eu quero mudar o idle_in_transaction_session_timeoutparâmetro no PostgreSQL 13, então executo este sql:

alter system set idle_in_transaction_session_timeout='30min';

quando mostro o novo valor definido, continua o valor antigo, parece que não entrou em vigor agora, pois o PostgreSQL estava online, então é possível fazer a alteração ter efeito sem reiniciar o PostgreSQL? Meu PostgreSQL foi implantado no cluster kubernetes.

Estou usando este comando para criar índice no PostgreSQL 13:

CREATE INDEX dict_word_idx ON dict (word); 

mas agora o sql é executado por mais de 12 horas, não tenho certeza se o sql ainda está funcionando ou algo assim. Eu uso este comando para mostrar o sql em execução atual:

SELECT 
  procpid, 
  start, 
  now() - start AS lap, 
  current_query 
FROM 
  (SELECT 
    backendid, 
    pg_stat_get_backend_pid(S.backendid) AS procpid, 
    pg_stat_get_backend_activity_start(S.backendid) AS start, 
    pg_stat_get_backend_activity(S.backendid) AS current_query 
  FROM 
    (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
  ) AS S 
WHERE 
  current_query <> '<IDLE>' 
ORDER BY 
  lap DESC;

mostra o resultado de criar índice assim:

1303    2021-10-04 09:24:27 13:12:49.940239 CREATE INDEX dict_word_idx ON dict (word);

mas pg_stat_progress_create_indexnão mostra nada:

select *
from pg_stat_progress_create_index;

então tentei usar este sql para matar a tarefa de criação de índice:

SELECT pg_cancel_backend(1303);

Parece não funcionar, o comando create index ainda está lá. o que devo fazer para descobrir o que está acontecendo agora. O SQL ainda está ocupado rodando e devo esperar? o comando craeted index não deve ser morto assim? o que devo fazer para lidar adequadamente com essa situação e não fazer com que o banco de dados falhe no futuro.

Eu tenho uma tabela com mais de 1000000 linhas que foram importadas do arquivo CSV. A tabela não tinha uma chave primária, quero adicionar uma com bigint incrementado à tabela. Esta é a minha tabela DDL:

CREATE TABLE public.ecdict (
    word varchar(256) NULL,
    phonetic varchar(512) NULL,
    definition varchar(80000) NULL,
    "translation" varchar(80000) NULL,
    pos varchar(100) NULL,
    collins int4 NULL,
    oxford int4 NULL,
    tag varchar(128) NULL,
    bnc int4 NULL,
    frq int4 NULL,
    exchange varchar(128) NULL,
    detail varchar(2000) NULL,
    audio varchar(8) NULL,
    id int8 NULL
);

A idcoluna é nova add e todos os dados são NULL, tentei usar este comando:

update ecdict c
set id = c2.seqnum
from (
   select c2.*, row_number() over () as seqnum
   from ecdict c2
) c2
where c2.pkid = c.pkid;

Mas não consegui encontrar uma coluna exclusiva para a tabela para junção, o que devo fazer para adicionar a idcoluna com valores incrementados bigint?