Re: Event Triggers and Dropping Objects

[Luca Ferrari]

On Fri, Oct 4, 2019 at 10:38 PM Miles Elam  wrote:
>
> The event trigger firing matrix lists tags like DROP TABLE and DROP FUNCTION 
> are listed below the ddl_command_end event, but when I created a basic audit 
> table and event trigger, they don't seem to fire. I know sql_drop exists, but 
> again the matrix lists DROP commands in the ddl_command_end event.

Yes, I think this is a little misleading:
.
The ddl_command_end is issued, and the function is invoked, but
pg_event_trigger_ddl_commands() returns NULL on such invocation
because sql_drop is the event with the attached data.

Hope this helps,
Luca

Re: Performance on JSONB select

[PegoraroF10]

I think I solved my problem. I didn´t know it was possible but I´ve created
an almost dynamic index, because it´ll index for status and corresponding
datevalue of an object with that status value. 

Postgres, I love you.
 
Just one B-Tree index with 2 fields, Status and DateTime of that respective
Status object. My Json has always a status and a respective object of that
status with other values. So I know if it has that status it has a Date
Value or not on its correspondent object. 

{
"status": "visitadescartada",
"contrato": {},
"atribuido": {"datevalue": "2019-09-05 14:47:11.149095-03"},
"trabalhando": {},
"visitaagendada": {"datevalue": "2019-09-05
15:06:24.255548-03","caption": "Agendado"},
"visitadescartada": {"datevalue": "2019-09-12
11:47:17.45782-03","caption": "Desagendado"},
"digitacaodescartada": {}
}

create index IndexByStatusAndDate on MyTable (
(JsonBField->>$$status$$),
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')) 
where (JsonBField ? $$status$$);

select * from MyTable where (JsonBField ? $$status$$) and
case when JsonBField->>$$status$$=$$cadastrodescartado$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> castimmutabletimestamp($$2019-10-01$$)
 when JsonBField->>$$status$$=$$visitadescartada$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> castimmutabletimestamp($$2019-10-01$$)
 when JsonBField->>$$status$$=$$contrato$$ then
castimmutabletimestamp(funilvendas->(JsonBField->>$$status$$)->>'data') >
castimmutabletimestamp($$2019-10-01$$)
 when JsonBField->>$$status$$=$$naoatribuido$$ then True end;

And performance now is great because I´m using both fields on index.



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Performance on JSONB select

[PegoraroF10]

I think I solved my problem. I didn´t know it was possible but I´ve created
an almost dynamic index, because it´ll index for status and corresponding
datevalue of an object with that status value.

Postgres, I love you.
 
Just one B-Tree index with 2 fields, Status and DateTime of that respective
Status object. My Json has always a status and a respective object of that
status with other values. So I know if it has that status it has a Date
Value or not on its correspondent object.

{
"status": "visitadescartada",
"contrato": {},
"atribuido": {"datevalue": "2019-09-05 14:47:11.149095-03"},
"trabalhando": {},
"visitaagendada": {"datevalue": "2019-09-05
15:06:24.255548-03","caption": "Agendado"},
"visitadescartada": {"datevalue": "2019-09-12
11:47:17.45782-03","caption": "Desagendado"},
"digitacaodescartada": {}
}

create index IndexByStatusAndDate on MyTable (
(JsonBField->>$$status$$),
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue'))
where (JsonBField ? $$status$$);

select * from MyTable where (JsonBField ? $$status$$) and
case when JsonBField->>$$status$$=$$cadastrodescartado$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> castimmutabletimestamp($$2019-10-01$$)
 when JsonBField->>$$status$$=$$visitadescartada$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> castimmutabletimestamp($$2019-10-01$$)
 when JsonBField->>$$status$$=$$contrato$$ then
castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'data') >
castimmutabletimestamp($$2019-10-01$$)
 when JsonBField->>$$status$$=$$naoatribuido$$ then True end;

And performance now is great because I´m using both fields on index.




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Postgres 12: backend crashes when creating non-deterministic collation

[Thomas Kellerer]

Tom Lane schrieb am 04.10.2019 um 19:36:

Hm.  This trace says that the crash happened somewhere down inside ICU
itself, during the ucol_open() call in get_collation_actual_version().
There isn't much we could have done to mess up the arguments to that
function.  That would seem to mean that it's ICU's bug not ours.
Maybe another reason not to be using such an old ICU version :-(.


I would like to test this with a newer ICU version.

So I managed to setup the build environment with Visual Studio, but I can't 
figure out how to enable ICU for the build.

I created a config.pl to specify the location of the downloaded ICU libraries 
with the following content

# Configuration arguments for vcbuild.
use strict;
use warnings;

our $config = {
  icu   => "d:\Projects\postgres\libs\icu\lib64\"# --with-icu=
};

The build is successful, but when I run "install targetfolder" no ICU libraries 
are included and pg_config only shows:

   CONFIGURE = --enable-thread-safety --with-ldap --without-zlib

I have no idea what I am missing.

I also tried building with Msys2 but even when I run configure with the 
--with-icu option, no ICU DLLs are copied

Regards
Thomas

Re: Postgres 12: backend crashes when creating non-deterministic collation

[Thomas Kellerer]

Thomas Kellerer schrieb am 05.10.2019 um 13:39:

Hm.  This trace says that the crash happened somewhere down inside ICU
itself, during the ucol_open() call in get_collation_actual_version().
There isn't much we could have done to mess up the arguments to that
function.  That would seem to mean that it's ICU's bug not ours.
Maybe another reason not to be using such an old ICU version :-(.


I would like to test this with a newer ICU version.

So I managed to setup the build environment with Visual Studio, but I can't 
figure out how to enable ICU for the build.


Ah, figured it out.

config.pl has a different format compared to config_default.pl

   $config->{icu}='d:\Projects\postgres\libs\icu'

did the trick, and postgres was built with ICU support.

I can confirm that with ICU 65 the crash does not occur and the case 
insensitive comparison works fine as well.

Regards
Thomas

Re: Postgres 12: backend crashes when creating non-deterministic collation

[Peter Eisentraut]

On 2019-10-04 19:36, Tom Lane wrote:
> Hm.  This trace says that the crash happened somewhere down inside ICU
> itself, during the ucol_open() call in get_collation_actual_version().
> There isn't much we could have done to mess up the arguments to that
> function.  That would seem to mean that it's ICU's bug not ours.

Some build farm coverage of Windows+ICU would be nice.  We have test
cases in place that might have caught this.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Performance on JSONB select

[Pavel Stehule]

Hi

so 5. 10. 2019 v 13:34 odesílatel PegoraroF10  napsal:

> I think I solved my problem. I didn´t know it was possible but I´ve created
> an almost dynamic index, because it´ll index for status and corresponding
> datevalue of an object with that status value.
>

you created multicolumn functional index (there are no any dynamic index :))

Pavel


>
> Postgres, I love you.
>
> Just one B-Tree index with 2 fields, Status and DateTime of that respective
> Status object. My Json has always a status and a respective object of that
> status with other values. So I know if it has that status it has a Date
> Value or not on its correspondent object.
>
> {
> "status": "visitadescartada",
> "contrato": {},
> "atribuido": {"datevalue": "2019-09-05 14:47:11.149095-03"},
> "trabalhando": {},
> "visitaagendada": {"datevalue": "2019-09-05
> 15:06:24.255548-03","caption": "Agendado"},
> "visitadescartada": {"datevalue": "2019-09-12
> 11:47:17.45782-03","caption": "Desagendado"},
> "digitacaodescartada": {}
> }
>
> create index IndexByStatusAndDate on MyTable (
> (JsonBField->>$$status$$),
> castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue'))
>
> where (JsonBField ? $$status$$);
>
> select * from MyTable where (JsonBField ? $$status$$) and
> case when JsonBField->>$$status$$=$$cadastrodescartado$$ then
> castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> > castimmutabletimestamp($$2019-10-01$$)
>  when JsonBField->>$$status$$=$$visitadescartada$$ then
> castimmutabletimestamp(JsonBField->(JsonBField->>$$status$$)->>'datevalue')
> > castimmutabletimestamp($$2019-10-01$$)
>  when JsonBField->>$$status$$=$$contrato$$ then
> castimmutabletimestamp(funilvendas->(JsonBField->>$$status$$)->>'data') >
> castimmutabletimestamp($$2019-10-01$$)
>  when JsonBField->>$$status$$=$$naoatribuido$$ then True end;
>
> And performance now is great because I´m using both fields on index.
>
>
>
> --
> Sent from:
> https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

Re: Performance on JSONB select

[PegoraroF10]

I told it was almost dynamic because it uses DateValue from an object or
another, depending on value of staus key.



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Performance on JSONB select

[Fabrízio de Royes Mello]

Em sáb, 5 de out de 2019 às 11:49, PegoraroF10  escreveu:

> I told it was almost dynamic because it uses DateValue from an object or
> another, depending on value of staus key.


>
Actually it’s named partial index.

Regards,

> --
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: PMChildFlags array

[bhargav kamineni]

Thanks Tom Lane for detailing the issue.
>So ... how many is "a bunch"?
more than 85

>Looking at the code, it seems like it'd be possible for a sufficiently
>aggressive spawner of incoming connections to reach the
>MaxLivePostmasterChildren limit.  While the postmaster would correctly
>reject additional connection attempts after that, what it would not do
>is ensure that any child slots are left for new parallel worker processes.
>So we could hypothesize that the error you're seeing in the log is from
>failure to spawn a parallel worker process, due to being out of child
>slots.
Thanks Tom Lane for detailing the issue.

we have enabled "max_parallel_workers_per_gather = 4".  20 days before we
ran into this issue .


>However, given that max_connections = 500, MaxLivePostmasterChildren()
>would be 1000-plus.  This would mean that reaching this condition would
>require *at least* 500 concurrent connection-attempts-that-haven't-yet-
>been-rejected, maybe well more than that if you didn't have close to
>500 legitimately open sessions.  That seems like a lot, enough to suggest
>that you've got some pretty serious bug in your client-side logic.

below errors observed after crash in postgres logfile :

ERROR:  xlog flush request  is not satisfied for couple of tables , we have
initiated the vacuum full on those tables and the error went off after that.
ERROR:  right sibling's left-link doesn't match: block 273660 links to
273500 instead of expected 273661 in index -- observed this error while
doing vacuum freeze on databsase , we have dropped this index and created a
new one

Observations :

Vacuum freeze analyze job is getting stuck at database end which is
initiated thru cronjob, pg_cancel_backend(), pg_termiante_backend() is not
able to terminate those stuck  process , Restarting the database only able
to clear those process , i am thinking this is happening due to corruption
(if this is true how can i detect this ? pg_dump ?). is  there any way to
overcome this problem ?

does migrating the database to a new instance (pg_basebackup and switching
over to new instance ) solves this issue ?

Anyway, I think it's clearly a bug that canAcceptConnections() thinks the
number of acceptable connections is identical to the number of allowed
child processes; it needs to be less, by the number of background
processes we want to support.  But it seems like a darn hard-to-hit bug,
so I'm not quite sure that that explains your observation.

On Fri, 4 Oct 2019 at 03:49, Tom Lane  wrote:

> bhargav kamineni  writes:
> >> What was the database doing just before the FATAL line?
>
> > Postgres was rejecting a bunch of connections from a user who is having a
> > connection limit set. that was the the FATAL error that i could see in
> log
> > file.
> >  FATAL,53300,"too many connections for role ""user_app"""
>
> So ... how many is "a bunch"?
>
> Looking at the code, it seems like it'd be possible for a sufficiently
> aggressive spawner of incoming connections to reach the
> MaxLivePostmasterChildren limit.  While the postmaster would correctly
> reject additional connection attempts after that, what it would not do
> is ensure that any child slots are left for new parallel worker processes.
> So we could hypothesize that the error you're seeing in the log is from
> failure to spawn a parallel worker process, due to being out of child
> slots.
>
> However, given that max_connections = 500, MaxLivePostmasterChildren()
> would be 1000-plus.  This would mean that reaching this condition would
> require *at least* 500 concurrent connection-attempts-that-haven't-yet-
> been-rejected, maybe well more than that if you didn't have close to
> 500 legitimately open sessions.  That seems like a lot, enough to suggest
> that you've got some pretty serious bug in your client-side logic.
>
> Anyway, I think it's clearly a bug that canAcceptConnections() thinks the
> number of acceptable connections is identical to the number of allowed
> child processes; it needs to be less, by the number of background
> processes we want to support.  But it seems like a darn hard-to-hit bug,
> so I'm not quite sure that that explains your observation.
>
> regards, tom lane
>

Re: Performance on JSONB select

[PegoraroF10]

No, no, no, partial index is the where clause of it and I´m not talking about
that. 
I´m talking about datevalue, which will be used one or another, depending on
status value

This record iindex will be status and datevalue from "visitadescartada"
object
{
  "status": "visitadescartada",
  "atribuido": {"datevalue": "2019-09-05 14:47:11"},
  "visitadescartada": {"datevalue": "2019-09-12 11:47:17"},
  "digitacaodescartada": {}
}

And here the index record will be status and datevalue from "atribuido"
object
{
  "status": "atribuido",
  "atribuido": {"datevalue": "2019-09-05 14:47:11"},
  "visitadescartada": {"datevalue": "2019-09-12 11:47:17"},
  "digitacaodescartada": {}
}



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Event Triggers and Dropping Objects

[Miles Elam]

Thanks, it does!


On Sat, Oct 5, 2019 at 1:50 AM Luca Ferrari  wrote:

> On Fri, Oct 4, 2019 at 10:38 PM Miles Elam 
> wrote:
> >
> > The event trigger firing matrix lists tags like DROP TABLE and DROP
> FUNCTION are listed below the ddl_command_end event, but when I created a
> basic audit table and event trigger, they don't seem to fire. I know
> sql_drop exists, but again the matrix lists DROP commands in the
> ddl_command_end event.
>
> Yes, I think this is a little misleading:
> .
> The ddl_command_end is issued, and the function is invoked, but
> pg_event_trigger_ddl_commands() returns NULL on such invocation
> because sql_drop is the event with the attached data.
>
> Hope this helps,
> Luca
>