Re: Revoke SQL doesn't take effect
‐‐‐ Original Message ‐‐‐ On Tuesday, January 29, 2019 9:20 PM, Tim Cross wrote: > On Wed, 30 Jan 2019 at 07:49, Jason W wrote: > >> I have two postgresql accounts created by someone else who I do not know (So >> I do not know setting for those accounts and tables created). One is read >> only account e.g. read_only_user (This can perform select operations only). >> The other is admin account e.g. admin_user (This can perform grant, revoke, >> CRUD, and so on operations). >> >> The read only account can query (select sql) a table (suppose it's called >> table1) under a specific schema (suppose it's schema1). For instance select >> * from schema1.table1. Now I received a request to revoke select for that >> read only account on table1. So I execute >> >> revoke select on schema1.table1 from read_only_user >> >> psql returns REVOKE string (or something similar showing the sql execution >> was successful) on console. However, when check with read_only_user account. >> I am still able to query table1. Searching the internet, [1] looks like the >> closest to my problem. But I do not find solution in that thread. >> >> So my question: >> What steps do I need to perform in order to exactly revoke select from read >> only user account for a particular table? So the read only user account >> wont' be able query that specific table with select permission revoke (psql >> should returns info like permission denied). >> >> Thanks >> >> [1]. >> https://www.postgresql.org/message-id/00ce01cbef04%24910606b0%24b3121410%24%40yahoo.com > > It is likely that permissions for the user are being granted via a role > rather than granted directly to the user (think of a role as a user account > which does not have the login permission). First thing to check would be to > look at what roles have been granted to the read_only user and if one of > those grants select on schema1.table1, revoke/remove it from the role. There > may be other complications, such as roles which do a grant select on all > tables in a schema, so getting the order of things correct is important. > First step, understanding how permissions are granted, then you should be > able to revoke them effectively. > > Tim > > -- > regards, > > Tim > > -- > Tim Cross Sorry my fault. After double checking, the problem is our side which is not postgresql issue. So revoke select did take effect. Thanks for the advice!
Server goes to Recovery Mode when run a SQL
I have a complex query which puts my server in recovery mode every time I run it. I don´t need to say that recovery mode is a situation you don´t want your server goes to.*If I´m using some subselects I´ll get that situation*with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs,Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo)...*but if I run those subselects previously, get those values and put the results to my statement, then it works. *with StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$), StatusDigitacaoReceptivoAgendados(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$), StatusDigitacaoReceptivoTrabalhando(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados), (select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from StatusDigitacaoReceptivoTrabalhando);*Then i get these results (8, 14 and 17) and replace those subselects with these values and run, now it runs fine.*with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (*8*)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (*14*) and Intkey not in (*17*)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs,Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo)*My original query, which worked for some days and then went to recovery mode was ...*with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ andIntkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ andIntkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) andIntkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs,Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo); as (select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID, D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso, D.Telefone, D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro, D.Email, D.Idade, D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio, D.Serie, D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia, D.Status, Status.Descricao lkStatus, Fonte.Fonte lkFonte, DigitadorPessoa.Apeli
Re: Server goes to Recovery Mode when run a SQL
I´m using Postgres 10 on ubuntu in a Google VM (8 cores, 32Gb RAM, 250Gb SSD) and DB has 70GB -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Server goes to Recovery Mode when run a SQL
On 2/3/19 7:53 AM, PegoraroF10 wrote: First the below is unreadable, probably because it is being sent through Nabble. Please subscribe to list directly and try again. In meantime could not tell from below, but what relevant information is in the logs before and after? I have a complex query which puts my server in recovery mode every time I run it. I don´t need to say that recovery mode is a situation you don´t want your server goes to. *If I´m using some subselects I´ll get that situation* with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) ... *but if I run those subselects previously, get those values and put the results to my statement, then it works. * with StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$), StatusDigitacaoReceptivoAgendados(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$), StatusDigitacaoReceptivoTrabalhando(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$) select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados), (select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from StatusDigitacaoReceptivoTrabalhando); *Then i get these results (8, 14 and 17) and replace those subselects with these values and run, now it runs fine.* with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (*8*)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (*14*) and Intkey not in (*17*)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) *My original query, which worked for some days and then went to recovery mode was ...* with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo); as (select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID, D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso, D.Telefone, D.Celular, D.Nasci
Re: Server goes to Recovery Mode when run a SQL
Just to be readable ... And ... server log has only "the database system is in recovery mode" every time I run that query. I have a complex query which puts my server in recovery mode every time I run it. I don´t need to say that recovery mode is a situation you don´t want your server goes to. If I´m using some subselects I´ll get that situation with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) ... But if I run those subselects previously, get result values and put the results to my statement, then it works. with StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$), StatusDigitacaoReceptivoAgendados(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$), StatusDigitacaoReceptivoTrabalhando(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$) select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados), (select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from StatusDigitacaoReceptivoTrabalhando); Then i get these results (8, 14 and 17) and replace those subselects with these values and run, now it runs fine. with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (8)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (14) and Intkey not in (17)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) My original query, which worked for some days and then went to recovery mode was ... with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) as (select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID, D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso, D.Telefone, D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro, D.Email, D.Idade, D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio, D.Serie, D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.
Re: Server goes to Recovery Mode when run a SQL
On 2/3/19 8:32 AM, PegoraroF10 wrote: Just to be readable ... And ... server log has only "the database system is in recovery mode" every time I run that query. I would assume that is in the log from the restart of the server after it crashed and then restarted in recovery mode. What is in the previous log, the one that was active when the query crashed the server? Also anything relevant from the OS system logs? I have a complex query which puts my server in recovery mode every time I run it. I don´t need to say that recovery mode is a situation you don´t want your server goes to. Well actually that is how standby_servers run: https://www.postgresql.org/docs/11/warm-standby.html "... while each standby server operates in continuous recovery mode, reading the WAL files from the primary." Is this server a standby? If I´m using some subselects I´ll get that situation with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) ... But if I run those subselects previously, get result values and put the results to my statement, then it works. with StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$), StatusDigitacaoReceptivoAgendados(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$), StatusDigitacaoReceptivoTrabalhando(IntKey) as (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$) select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados), (select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from StatusDigitacaoReceptivoTrabalhando); Then i get these results (8, 14 and 17) and replace those subselects with these values and run, now it runs fine. with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (8)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (14) and Intkey not in (17)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, Fluxo) My original query, which worked for some days and then went to recovery mode was ... with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTel
Re: Server goes to Recovery Mode when run a SQL
On Sun, Feb 03, 2019 at 10:05:46AM -0800, Adrian Klaver wrote: > On 2/3/19 8:32 AM, PegoraroF10 wrote: >> I have a complex query which puts my server in recovery mode every time I >> run it. >> I don´t need to say that recovery mode is a situation you don´t want your >> server goes to. Do you mean that your server crashes after running a SQL query? That could be a bug. > Well actually that is how standby_servers run: > > https://www.postgresql.org/docs/11/warm-standby.html > > "... while each standby server operates in continuous recovery mode, reading > the WAL files from the primary." > > Is this server a standby? If you could post a self-contained test case, that would be really helpful to see if there is an actual bug. You can obfuscate the schema if need be, as long as the problem can be reproduced that's fine. -- Michael signature.asc Description: PGP signature
JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)
I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where the only difference is the receiver column. |-|--|-|--| | Receiver | Event | Date| Location | |-|--|-|--| | Alpha | 3| 12 | USA | |-|--|-|--| | Bravo | 3| 12 | USA | |-|--|-|--| | Charlie | 3| 12 | USA | |-|--|-|--| While redesigning the database, I have considered using an array with a GIN index instead of the current B-Tree index on receiver. My proposed new table would look like this: |---|--||---| | Receivers | Event|Date| Location | |---|--||---| | ["Alpha", "Bravo", "Charlie"] | 3 | 12 | USA | |---|--||---| More Information: · Receiver names are of the type (a-z, 1-5, .) · 95% of all queries currently look like this: SELECT * FROM table WHERE Receiver = Alpha, with the new format this would be SELECT * FROM table WHERE receivers @> '"Alpha"'::jsonb; · The table currently contains over 4 billion rows (with duplication) and the new proposed schema would cut it down to under 2 billion rows. · Question: 1. Does it make more sense to use Postgres Native Text Array? 2. Would a jsonb_path_ops GIN index on receivers make sense here? 3. Which option is more efficient? Which is faster?
Re: Server goes to Recovery Mode when run a SQL
Olá Marcos, > My original query, which worked for some days and then went to > recovery mode > was ... If it was working and then ceased to function, did any of the following occur:- 1) Postgres version changed? 2) OS version changed? 3) Schema changes affecting the tables/views used in your query? I assume that you have a development data base. Can you bump up the log level on that, run the query and see any errors in the log. Cheers, Robert
FDW, too long to run explain
Hi, with pg v10.1 I have a setup enabled as below. 7 shards ( 1RW, 2 RO ) they all are fronted by FDW talking to each other. we use writes directly to shards, and reads via FDW from all shards (RO) our DB size is ~ 500GB each shard, and tables are huge too. 1 table ~ 200GB, 1 ~55GB, 1 ~40GB and a lot of small tables, but large indices on large table. the sharding was done based on a key to enable shard isolation at app layer using a fact table. select id,shard from fact_table; server resources are, 32GB mem, 8 vcpu, 500GB SSD. the FDW connect to each other shard via FDW fronted by haproxy -> pgbouncer -> postgresql. Hope this is good enough background :) now we have some long running queries via FDW that take minutes and get killed explain runs as idle in transaction on remote servers. (we set use_remote_estimate = true ) when the query is run on individual shards directly, it runs pretty quickly, but when run via FDW, it takes very long. i even altered fetch_sie to 1, so that in case some filters do not get pushed, those can be applied on the FDW quickly. but i am lost at the understanding of why explain runs for ever via FDW. we have a view on remote servers. we import public schema from remote servers, into coordinator custom schema, and then union all select * from ( select * from sh01.view1 union all select * from sh01.view1 ... ) t where t.foo = 'bar' limit 10; now the explain for select * from sh01.view1 keeps running for minutes sometimes, then fetch too keeps running for minutes, although the total rows are < 1 maybe. idle in transaction | FETCH 1 FROM c1 we have very aggressive settings for autovacuum and auto analyze. autovacuum_naptime = '15s' autovacuum_vacuum_scale_factor = '0.001' autovacuum_analyze_scale_factor = '0.005' log_autovacuum_min_duration = '0' maintenance_work_mem = '2GB' autovacuum_vacuum_cost_limit = '5000' autovacuum_vacuum_cost_delay = '5ms' other questions: also, what is the cost of fetch_size? we have in our settings => use_remote_estimate=true,fetch_size=1 I mean given we have a query select * from foobar limit 1; via FDW limit 1 does not get pushed. so it seems all rows some to FDW node and then limit is applied? i currently do not have the queries, but i have a screenshot for long running explain via FDW. also since the whole query does not show up in pg_stat_statement, i am not sure, that would be of great help since predicate although applied, do not show up in pg_stat_activity. I know, there can be more info i can provide, but if anyone has experienced this, pls let me know. BTW, i know citus is an option, but can we keep that option aside. we see better ways to handle this in future, by sharding on ids and further partitioning of tables and parallel execution of FDW queries, but we need to know if this is a known issue of pg10 or i am doing something wrong which will bite in pg11 too. Appreciate your help, always. Regards, Vijay
