Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Stephen Frost]

Greetings,

* Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
> 
> As mentioned there, the slides are linked, as long as they have been 
> delivered by the speakers, in the talk descriptions in the schedule.

I'm not sure what the point of duplicating that information on to the
wiki is..?

Seems like what we should do here is add a feature to the conference
system where a particular URL will produce a list of talks with links
to slides, if uploaded.  That shouldn't be hard to do if someone is
interested and knows a little python/django- the code is here:

https://git.postgresql.org/gitweb/?p=pgeu-website.git;a=summary

Patches very much accepted. :)

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Magnus Hagander]

On Mon, Nov 19, 2018 at 2:27 PM Stephen Frost  wrote:

> Greetings,
>
> * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> > https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
> >
> > As mentioned there, the slides are linked, as long as they have been
> delivered by the speakers, in the talk descriptions in the schedule.
>
> I'm not sure what the point of duplicating that information on to the
> wiki is..?
>
> Seems like what we should do here is add a feature to the conference
> system where a particular URL will produce a list of talks with links
> to slides, if uploaded.  That shouldn't be hard to do if someone is
> interested and knows a little python/django- the code is here:
>

It can already be done on the session list, if one just uses the template
functioinality that's there. Rumor has it Daniel has a hidden branch
somewhere... :)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Magnus Hagander]

On Mon, Nov 19, 2018 at 2:38 PM Magnus Hagander  wrote:

> On Mon, Nov 19, 2018 at 2:27 PM Stephen Frost  wrote:
>
>> Greetings,
>>
>> * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
>> >
>> https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
>> >
>> > As mentioned there, the slides are linked, as long as they have been
>> delivered by the speakers, in the talk descriptions in the schedule.
>>
>> I'm not sure what the point of duplicating that information on to the
>> wiki is..?
>>
>> Seems like what we should do here is add a feature to the conference
>> system where a particular URL will produce a list of talks with links
>> to slides, if uploaded.  That shouldn't be hard to do if someone is
>> interested and knows a little python/django- the code is here:
>>
>
> It can already be done on the session list, if one just uses the template
> functioinality that's there. Rumor has it Daniel has a hidden branch
> somewhere... :)
>
>
This has now been pushed, so both the schedule and the session list on
pgconf.eu will now indicate which sessions have slides uploaded.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Achilleas Mantzios]

On 19/11/18 3:27 μ.μ., Stephen Frost wrote:

Greetings,

* Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:

https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018

As mentioned there, the slides are linked, as long as they have been delivered 
by the speakers, in the talk descriptions in the schedule.

I'm not sure what the point of duplicating that information on to the
wiki is..?



It is nice to have a single source to all slides from all conferences, so at 
least a link to the conference page should be there on the wiki.




Seems like what we should do here is add a feature to the conference
system where a particular URL will produce a list of talks with links
to slides, if uploaded.  That shouldn't be hard to do if someone is
interested and knows a little python/django- the code is here:

https://git.postgresql.org/gitweb/?p=pgeu-website.git;a=summary

Patches very much accepted. :)

Thanks!

Stephen



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

RE: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Charles Clavadetscher]

> -Original Message-
> From: Achilleas Mantzios 
> Sent: Montag, 19. November 2018 15:43
> To: pgsql-general@lists.postgresql.org
> Subject: Re: pgconf eu 2018 slides entry missing from
> https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio
> ns
> 
> On 19/11/18 3:27 μ.μ., Stephen Frost wrote:
> > Greetings,
> >
> > * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> >>
> https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
> >>
> >> As mentioned there, the slides are linked, as long as they have been
> delivered by the speakers, in the talk descriptions in the schedule.
> > I'm not sure what the point of duplicating that information on to the
> > wiki is..?
> 
> 
> It is nice to have a single source to all slides from all conferences, so
> at least a link to the conference page should be there on the wiki.

[>] It is since this morning.

> 
> 
> >
> > Seems like what we should do here is add a feature to the conference
> > system where a particular URL will produce a list of talks with links
> > to slides, if uploaded.  That shouldn't be hard to do if someone is
> > interested and knows a little python/django- the code is here:
> >
> > https://git.postgresql.org/gitweb/?p=pgeu-website.git;a=summary
> >
> > Patches very much accepted. :)
> >
> > Thanks!
> >
> > Stephen
> 
> 
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
> 

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Stephen Frost]

Greetings,

* Magnus Hagander (mag...@hagander.net) wrote:
> This has now been pushed, so both the schedule and the session list on
> pgconf.eu will now indicate which sessions have slides uploaded.

That's better, but couldn't we make that an actual link..?

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Stephen Frost]

Greetings,

* Achilleas Mantzios (ach...@matrix.gatewaynet.com) wrote:
> On 19/11/18 3:27 μ.μ., Stephen Frost wrote:
> >* Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> >>https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
> >>
> >>As mentioned there, the slides are linked, as long as they have been 
> >>delivered by the speakers, in the talk descriptions in the schedule.
> >I'm not sure what the point of duplicating that information on to the
> >wiki is..?
> 
> It is nice to have a single source to all slides from all conferences, so at 
> least a link to the conference page should be there on the wiki.

Having it for *all* conferences would be yet another, different,
thing...  We could do that for each of the systems (PgEU, PgUS, PgDU,
PGConfAPAC) potentially, but then we might also want to include links to
things like the videos from those conferences which record and post to
youtube, like: https://www.youtube.com/user/postgresopen

I do think Magnus has some thoughts about providing a way to search each
system for talks that might be interesting to a particular user and we
might even be able to provide a federated search through .Org
eventually..

Not sure if the wiki is the best place for that, but we could at least
start there, I suppose, if someone wants to pull that together.

Thanks!

Stephen


signature.asc
Description: PGP signature

plpgsql and intarray extension; int[] - int[] operator does not exist ?

[Day, David]

I have installed the intarray extension installed in the public schema and am 
attempting to  use this in a plpgsql trigger function from another schema.
When the triggers executes this I get an exception to the effect 

{
"hint": "No operator matches the given name and argument type(s). You might 
need to add explicit type casts.",
"details": null,
"code": "42883",
"message": "operator does not exist: integer[] - integer[]"
}

However,  If I write a similar test function and attempt similar array 
arithmetic successfully from a different schema in a non-trigger function It 
recognizes the intarray methods.

CREATE OR REPLACE FUNCTION admin.djd_test()
  RETURNS integer[] AS
$BODY$
DECLARE
_old_tag_ids INTEGER[];
_new_tag_ids INTEGER[];
   

BEGIN
_old_tag_ids := ARRAY[1,2,3];
_new_tag_ids := ARRAY[3,4,5];

RETURN _old_tag_ids - _new_tag_ids;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE


Of course in the trigger function  the declared int[] arrays  the content is 
dynamically initialized.
Any suggestions as to why the int[] operations are not understood in the 
trigger context.?

.
Thanks


Dave Day

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Magnus Hagander]

On Mon, Nov 19, 2018 at 4:01 PM Stephen Frost  wrote:

> Greetings,
>
> * Magnus Hagander (mag...@hagander.net) wrote:
> > This has now been pushed, so both the schedule and the session list on
> > pgconf.eu will now indicate which sessions have slides uploaded.
>
> That's better, but couldn't we make that an actual link..?
>

That could potentially look quite crap for a session that has a large set
of files and/or links.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Magnus Hagander]

On Mon, Nov 19, 2018 at 4:35 PM Stephen Frost  wrote:

> Greetings,
>
> * Achilleas Mantzios (ach...@matrix.gatewaynet.com) wrote:
> > On 19/11/18 3:27 μ.μ., Stephen Frost wrote:
> > >* Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> > >>
> https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
> > >>
> > >>As mentioned there, the slides are linked, as long as they have been
> delivered by the speakers, in the talk descriptions in the schedule.
> > >I'm not sure what the point of duplicating that information on to the
> > >wiki is..?
> >
> > It is nice to have a single source to all slides from all conferences,
> so at least a link to the conference page should be there on the wiki.
>
> Having it for *all* conferences would be yet another, different,
> thing...  We could do that for each of the systems (PgEU, PgUS, PgDU,
> PGConfAPAC) potentially, but then we might also want to include links to
> things like the videos from those conferences which record and post to
> youtube, like: https://www.youtube.com/user/postgresopen
>
> I do think Magnus has some thoughts about providing a way to search each
> system for talks that might be interesting to a particular user and we
> might even be able to provide a federated search through .Org
> eventually..
>
> Not sure if the wiki is the best place for that, but we could at least
> start there, I suppose, if someone wants to pull that together.
>

Yes, I do have some sort of long term idea for how to do that. I haven't
figured out the details yet though, but that's where I'd like to end up
eventually.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: plpgsql and intarray extension; int[] - int[] operator does not exist ?

[Tom Lane]

"Day, David"  writes:
> Any suggestions as to why the int[] operations are not understood in the 
> trigger context.?

The search_path in the trigger probably doesn't include public.
You could add a "SET search_path = whatever" clause to the trigger
function definition to ensure it runs with a predictable path.

regards, tom lane

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Stephen Frost]

Greetings,

* Magnus Hagander (mag...@hagander.net) wrote:
> On Mon, Nov 19, 2018 at 4:01 PM Stephen Frost  wrote:
> > * Magnus Hagander (mag...@hagander.net) wrote:
> > > This has now been pushed, so both the schedule and the session list on
> > > pgconf.eu will now indicate which sessions have slides uploaded.
> >
> > That's better, but couldn't we make that an actual link..?
> 
> That could potentially look quite crap for a session that has a large set
> of files and/or links.

That's a relatively rare case, isn't it..?

Thanks!

Stephen


signature.asc
Description: PGP signature

postgresql10-server RPM unpacking of archive failed

[Chris Mair]

Hi,

on a CentOS 7.5 machine with PostgreSQL 10.3 installed from the PGDG yum repo,
I have a strange error when trying to update to 10.6.

A simple "yum update" updated everything except postgresql10-server.x86_64!

That package gives (repeatedly) the error message:

Error unpacking rpm package postgresql10-server-10.6-1PGDG.rhel7.x86_64
error: unpacking of archive failed: cpio: lstat

(full output below)

I'm a bit at loss here... I'd interpret the message towards a corrupted
package (?).

Has anybody seen something like this?

Thanks,
Bye,
Chris.



# yum update
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/x86_64/metalink | 25 kB 00:00:00
 * base: centos.mirror.garr.it
 * epel: epel.mirror.far.fi
 * extras: centos.mirror.garr.it
 * updates: centos.mirror.garr.it
base | 3.6 kB 00:00:00
epel | 3.2 kB 00:00:00
extras | 3.4 kB 00:00:00
pbiso | 2.5 kB 00:00:00
pgdg10 | 4.1 kB 00:00:00
pgdg96 | 4.1 kB 00:00:00
updates | 3.4 kB 00:00:00
(1/12): base/7/x86_64/group_gz | 166 kB 00:00:00
(2/12): extras/7/x86_64/primary_db | 205 kB 00:00:00
(3/12): pbiso/x86_64/primary_db | 5.1 kB 00:00:00
(4/12): epel/x86_64/group_gz | 88 kB 00:00:00
(5/12): pgdg10/7/x86_64/group_gz | 245 B 00:00:00
(6/12): pgdg96/7/x86_64/group_gz | 249 B 00:00:00
(7/12): pgdg96/7/x86_64/primary_db | 203 kB 00:00:00
(8/12): pgdg10/7/x86_64/primary_db | 176 kB 00:00:00
(9/12): epel/x86_64/updateinfo | 932 kB 00:00:02
(10/12): epel/x86_64/primary | 3.6 MB 00:00:03
(11/12): base/7/x86_64/primary_db | 5.9 MB 00:00:03
(12/12): updates/7/x86_64/primary_db | 6.0 MB 00:00:03
epel 12706/12706
Resolving Dependencies
--> Running transaction check
---> Package postgresql10-server.x86_64 0:10.3-1PGDG.rhel7 will be updated
---> Package postgresql10-server.x86_64 0:10.6-1PGDG.rhel7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

===
 Package Arch Version Repository Size
===
Updating:
 postgresql10-server x86_64 10.6-1PGDG.rhel7 pgdg10 4.6 M

Transaction Summary
===
Upgrade 1 Package

Total download size: 4.6 M
Is this ok [y/d/N]: y
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
postgresql10-server-10.6-1PGDG.rhel7.x86_64.rpm | 4.6 MB 00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating : postgresql10-server-10.6-1PGDG.rhel7.x86_64 1/2
Error unpacking rpm package postgresql10-server-10.6-1PGDG.rhel7.x86_64
error: unpacking of archive failed: cpio: lstat
  Verifying : postgresql10-server-10.6-1PGDG.rhel7.x86_64 1/2
postgresql10-server-10.3-1PGDG.rhel7.x86_64 was supposed to be removed but is 
not!
  Verifying : postgresql10-server-10.3-1PGDG.rhel7.x86_64 2/2

Failed:
  postgresql10-server.x86_64 0:10.3-1PGDG.rhel7 postgresql10-server.x86_64 
0:10.6-1PGDG.rhel7

Complete!

RE: plpgsql and intarray extension; int[] - int[] operator does not exist ?

[Day, David]

Tom

I was thinking something similar after finding that my test function recreated 
in the problematic schema would execute correctly
As one user-role but not another and that they had different search_path 
settings.

After adding public to search patch for that role all was good.

The error message "no operator matches the given name and argument type." 
does not make me easily come around to a search path issue.
In any event thanks much for the assistance.


Issue resolved.


Dave




-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Monday, November 19, 2018 12:56 PM
To: Day, David 
Cc: pgsql-gene...@postgresql.org
Subject: Re: plpgsql and intarray extension; int[] - int[] operator does not 
exist ?

"Day, David"  writes:
> Any suggestions as to why the int[] operations are not understood in the 
> trigger context.?

The search_path in the trigger probably doesn't include public.
You could add a "SET search_path = whatever" clause to the trigger function 
definition to ensure it runs with a predictable path.

regards, tom lane

Re: plpgsql and intarray extension; int[] - int[] operator does not exist ?

[Merlin Moncure]

On Mon, Nov 19, 2018 at 11:56 AM Tom Lane  wrote:
>
> "Day, David"  writes:
> > Any suggestions as to why the int[] operations are not understood in the 
> > trigger context.?
>
> The search_path in the trigger probably doesn't include public.
> You could add a "SET search_path = whatever" clause to the trigger
> function definition to ensure it runs with a predictable path.

Might be worth considering:
SET LOCAL search_path = whatever

So that LOCAL makes the change local to the transaction.

merlin

Re: plpgsql and intarray extension; int[] - int[] operator does not exist ?

[Tom Lane]

Merlin Moncure  writes:
> On Mon, Nov 19, 2018 at 11:56 AM Tom Lane  wrote:
>> The search_path in the trigger probably doesn't include public.
>> You could add a "SET search_path = whatever" clause to the trigger
>> function definition to ensure it runs with a predictable path.

> Might be worth considering:
> SET LOCAL search_path = whatever
> So that LOCAL makes the change local to the transaction.

What I was suggesting was to create the trigger function using

create function ... as $$ ...body... $$ set search_path = whatever;

That makes the setting local to each function call.

regards, tom lane

Re: plpgsql and intarray extension; int[] - int[] operator does not exist ?

[Merlin Moncure]

On Mon, Nov 19, 2018 at 4:36 PM Tom Lane  wrote:
>
> Merlin Moncure  writes:
> > On Mon, Nov 19, 2018 at 11:56 AM Tom Lane  wrote:
> >> The search_path in the trigger probably doesn't include public.
> >> You could add a "SET search_path = whatever" clause to the trigger
> >> function definition to ensure it runs with a predictable path.
>
> > Might be worth considering:
> > SET LOCAL search_path = whatever
> > So that LOCAL makes the change local to the transaction.
>
> What I was suggesting was to create the trigger function using
>
> create function ... as $$ ...body... $$ set search_path = whatever;
>
> That makes the setting local to each function call.

Oh, I see.  Yes, that's even better.

merlin

replication lag despite corrective config

[Wyatt Alt]

I've been struggling to eliminate replication lag on a Postgres 9.6.6
instance on Amazon RDS. I believe the lag is caused by early cleanup
conflicts from vacuums on the master, because I can reliably resolve it by
killing long-running queries on the standby. I most recently saw ten hours
of lag on Saturday and addressed it this way.

The standby is running with
hot_standby_feedback = on
max_standby_streaming_delay = 5min
max_standby_archive_delay = 30s

I am not using replication slots on the primary due to reported negative
interactions with pg_repack on large tables.

My rationale for the first two settings is that hot_standby_feedback should
address my issues almost all the time, but that max_standby_streaming_delay
would sometimes be necessary as a fallback, for instance in cases of a
transient connection loss between the standby and primary. I believe these
settings are mostly working, because lag is less frequent than it was when
I configured them.

My questions are,
* Am I overlooking anything in my configuration?
* What would explain lag caused by query conflicts given the
max_standby_streaming_delay setting? Shouldn't those queries be getting
killed?
* Is there any particular diagnostic info I should be collecting on the
next occurrence, to help me figure out the cause? Note that as I'm on RDS,
I don't have direct access to the datadir -- just psql.

Thanks for any advice!
Wyatt

Re: replication lag despite corrective config

[Wyatt Alt]

Sorry, I see now there was a similar question a few days ago:
https://www.postgresql.org/message-id/CAJw4d1WtzOdYzd8Nq2=ufk+z0jy0l_pfg9tvcwprmt3nczq...@mail.gmail.com

Two ideas proposed (aside from disconnects):
* Autovacuum is truncating a page on the master and taking an
AccessExclusiveLock on the table in use on the replica
* A "pin conflict", which I'm still unfamiliar with.

The user's response says they are in the first bucket, but the argument
relies on max_standby_streaming_delay set to -1, while mine is 5 minutes. I
need to understand pin conflicts better, but the likely scenario Andrew
outlined doesn't apply to me. My offending queries were doing bitmap heap
scans on a 300GB table.

Reading the thread I see Andres ask for the "precise conflict" the user
gets -- is there a way I can get that without a datadir? And to re-frame
the original question, are there causes of replication lag that
max_standby_streaming_delay would not be expected to prevent, that would be
resolved by killing long standby queries? If so, what's the best way to
confirm?

Wyatt

On Mon, Nov 19, 2018 at 5:46 PM Wyatt Alt  wrote:

> I've been struggling to eliminate replication lag on a Postgres 9.6.6
> instance on Amazon RDS. I believe the lag is caused by early cleanup
> conflicts from vacuums on the master, because I can reliably resolve it by
> killing long-running queries on the standby. I most recently saw ten hours
> of lag on Saturday and addressed it this way.
>
> The standby is running with
> hot_standby_feedback = on
> max_standby_streaming_delay = 5min
> max_standby_archive_delay = 30s
>
> I am not using replication slots on the primary due to reported negative
> interactions with pg_repack on large tables.
>
> My rationale for the first two settings is that hot_standby_feedback
> should address my issues almost all the time, but that
> max_standby_streaming_delay would sometimes be necessary as a fallback, for
> instance in cases of a transient connection loss between the standby and
> primary. I believe these settings are mostly working, because lag is less
> frequent than it was when I configured them.
>
> My questions are,
> * Am I overlooking anything in my configuration?
> * What would explain lag caused by query conflicts given the
> max_standby_streaming_delay setting? Shouldn't those queries be getting
> killed?
> * Is there any particular diagnostic info I should be collecting on the
> next occurrence, to help me figure out the cause? Note that as I'm on RDS,
> I don't have direct access to the datadir -- just psql.
>
> Thanks for any advice!
> Wyatt
>

Re: replication lag despite corrective config

[Rene Romero Benavides]

Not sure about the root cause but I can make these observations and raise
some questions:
1) 9.6.6 is five bug fix versions behind
2) 300GB is so big a table, wouldn't make sense to you to partition it ?
2a) or if it's partitioned, doesn't the time of creation or dropping of new
partitions match the time of the conflict?
3) can you track long running transactions on the master?
4) what are the isolation levels on master / replica?
5) check for active locks in the replica, I guess you should see some
blocked transactions during big delays, I've seen this in the past when
standby_feedback is turned off.
6) any out of the ordinary messages in the replica's logs? any evidence
that has been canceling statements?
7) are master and replica exactly the same in terms of resources and main
parameters?
8) how is performance in both nodes while the big delay is happening? IO /
cpu load / etc.

Also, check this out:
https://www.alibabacloud.com/forum/read-383


Am Mo., 19. Nov. 2018 um 21:46 Uhr schrieb Wyatt Alt :

> Sorry, I see now there was a similar question a few days ago:
>
> https://www.postgresql.org/message-id/CAJw4d1WtzOdYzd8Nq2=ufk+z0jy0l_pfg9tvcwprmt3nczq...@mail.gmail.com
>
> Two ideas proposed (aside from disconnects):
> * Autovacuum is truncating a page on the master and taking an
> AccessExclusiveLock on the table in use on the replica
> * A "pin conflict", which I'm still unfamiliar with.
>
> The user's response says they are in the first bucket, but the argument
> relies on max_standby_streaming_delay set to -1, while mine is 5 minutes. I
> need to understand pin conflicts better, but the likely scenario Andrew
> outlined doesn't apply to me. My offending queries were doing bitmap heap
> scans on a 300GB table.
>
> Reading the thread I see Andres ask for the "precise conflict" the user
> gets -- is there a way I can get that without a datadir? And to re-frame
> the original question, are there causes of replication lag that
> max_standby_streaming_delay would not be expected to prevent, that would be
> resolved by killing long standby queries? If so, what's the best way to
> confirm?
>
> Wyatt
>
> On Mon, Nov 19, 2018 at 5:46 PM Wyatt Alt  wrote:
>
>> I've been struggling to eliminate replication lag on a Postgres 9.6.6
>> instance on Amazon RDS. I believe the lag is caused by early cleanup
>> conflicts from vacuums on the master, because I can reliably resolve it by
>> killing long-running queries on the standby. I most recently saw ten hours
>> of lag on Saturday and addressed it this way.
>>
>> The standby is running with
>> hot_standby_feedback = on
>> max_standby_streaming_delay = 5min
>> max_standby_archive_delay = 30s
>>
>> I am not using replication slots on the primary due to reported negative
>> interactions with pg_repack on large tables.
>>
>> My rationale for the first two settings is that hot_standby_feedback
>> should address my issues almost all the time, but that
>> max_standby_streaming_delay would sometimes be necessary as a fallback, for
>> instance in cases of a transient connection loss between the standby and
>> primary. I believe these settings are mostly working, because lag is less
>> frequent than it was when I configured them.
>>
>> My questions are,
>> * Am I overlooking anything in my configuration?
>> * What would explain lag caused by query conflicts given the
>> max_standby_streaming_delay setting? Shouldn't those queries be getting
>> killed?
>> * Is there any particular diagnostic info I should be collecting on the
>> next occurrence, to help me figure out the cause? Note that as I'm on RDS,
>> I don't have direct access to the datadir -- just psql.
>>
>> Thanks for any advice!
>> Wyatt
>>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Package-support plans?

[Nicklas Karlsson]

Hi,

  (sorry if this has been asked before, "package" gives a lot of hits for
RPMs etc, I couldn't find much on this after 2005 in the lists)

Are there any plans for including Oracle-style packages with
namespace/visibility support at some point or is it just "use schemas"? I
could even live without package-state but I find it frustrating with the
namespace pollution when the application grows.

Thanks in advance,
  Nik

-- 
Nicklas Karlsson, +358 40 5062266
Vaakunatie 10 as 7, 20780 Kaarina

Re: replication lag despite corrective config

[Laurenz Albe]

On Mon, 2018-11-19 at 17:46 -0800, Wyatt Alt wrote:
> I've been struggling to eliminate replication lag on a Postgres 9.6.6
> instance on Amazon RDS. I believe the lag is caused by early cleanup
> conflicts from vacuums on the master, because I can reliably resolve
> it by killing long-running queries on the standby. I most recently
> saw ten hours of lag on Saturday and addressed it this way.
> 
> The standby is running with
> hot_standby_feedback = on
> max_standby_streaming_delay = 5min
> max_standby_archive_delay = 30s

With these settings, any conflicting query will be canceled after five
minutes.  Perhaps your actual settings are different.

What do you get for

SELECT * FROM pg_settings WHERE name = 'max_standby_streaming_delay';

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Package-support plans?

[Thomas Kellerer]

Nicklas Karlsson schrieb am 20.11.2018 um 07:32:
> Are there any plans for including Oracle-style packages with
> namespace/visibility support at some point or is it just "use
> schemas"? I could even live without package-state but I find it
> frustrating with the namespace pollution when the application grows.
>

If you don't need package-level variables, you could use schemas put functions 
in different namespaces.

  create schema package_one;
  create function package_one.function_one() as ...;

  create schema package_two;
  create function package_two.function_two() as ...;

Get link postgresql repo from command line (Send REST or POST request)

[Пацев Антон]

Hello!
I try write playbook for install postgresql.
When i want get link repo i go to https://www.postgresql.org/download/ and
choose OS version, PostgreSQL version  - get link repo (for example -
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-6-x86_64/pgdg-redhat11-11-2.noarch.rpm
)

How get link postgresql repo from command line (Send REST or POST request) ?
Thanks!
P.S. May be need sent to pgsql-admin
 ?

-- 
С уважением, Антон Пацев.
Best regards, Anton Patsev.

Re: Package-support plans?

[Nicklas Karlsson]

Yes, that it surely one option but is there any grant that can be made on a
function so that it can only be called from within the same schema? Even
then it is a bit cumbersome

On Tue, Nov 20, 2018 at 9:12 AM Thomas Kellerer  wrote:

> Nicklas Karlsson schrieb am 20.11.2018 um 07:32:
> > Are there any plans for including Oracle-style packages with
> > namespace/visibility support at some point or is it just "use
> > schemas"? I could even live without package-state but I find it
> > frustrating with the namespace pollution when the application grows.
> >
>
> If you don't need package-level variables, you could use schemas put
> functions in different namespaces.
>
>   create schema package_one;
>   create function package_one.function_one() as ...;
>
>   create schema package_two;
>   create function package_two.function_two() as ...;
>
>
>
>
>
>
>

-- 
Nicklas Karlsson, +358 40 5062266
Vaakunatie 10 as 7, 20780 Kaarina