Logical Replication - Give One Subscription Priority Over Other Subscriptions

[Avi Weinberg]

Hi all,

I use logical replication to synchronize one big table + some small tables.

I know that if all tables are part of the same subscription it will be executed 
in transactional order. In other words, as long as the transaction that updated 
the big table is not synchronized and executed on the subscriber side, no other 
transactions to the smaller tables will be synchronized to the subscriber side. 
 Therefore, I split it into two subscriptions, one for the big table and 
another for the smaller tables.

  *   How can I give higher priority to the subscription that synchronizing the 
smaller tables over the subscription that synchronizes the large table?
  *   If that is not possible, how do I make sure the subscription that 
synchronizes the big table does not "starve" the subscription that synchronizes 
the small tables and leave it with almost no bandwidth?



Thanks!

IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.

Re: Mysterious performance degradation in exceptional cases

[Matthias Apitz]

El día jueves, septiembre 15, 2022 a las 08:40:24a. m. -0700, Adrian Klaver 
escribió:

> On 9/14/22 22:33, Matthias Apitz wrote:
> > El día miércoles, septiembre 14, 2022 a las 07:19:31a. m. -0700, Adrian 
> > Klaver escribió:
> > 
> > > On 9/14/22 01:31, Matthias Apitz wrote:
> 
> > > Where is the inter library software, in your application or are you 
> > > reaching
> > > out to another application?
> > 
> > The above 'app-server' fulfills the search requested by the
> > 'ILL-software' (or the 'test search'), i.e. looks up for one single
> > librarian record (one row in the PostgreSQL database) and delivers
> > it to the 'ILL-software'. The request from the 'ILL-software' is not
> > a heavy duty, more or less 50 requests per day.
> > 
> > > Is the search running across a remote network?
> > 
> > The real search comes over the network through a stunnel. But we
> > watched with tcpdump the incoming search and the response by the
> > 'app-server' locally. In the case of the timeout, the 'app-server' does not
> > answer within 180 seconds, i.e. does not send anything into the stunnel,
> > and the remote 'ILL-software' terminates the connection with an F-packet.
> 
> The 'app-server' does not answer, but does the database not answer also?
> 
> Have you looked to see if the database is providing a response in a timely
> manner and if it is getting 'lost' in the 'app-server'?

I do not "think" that the time is spent elsewhere in the 'app-server'.
to make a fact of the "thinking", I enabled the tracing of our dblayer
showing how many milliseconds have been spent between entering the dblayer and
returning the result to the application layers. This is in effect since
36 hours now. Since September 13 the problem has not showed up anymore.
We are waiting for it...

> Also have you considered Tom Lane's suggestion of using auto_explain?

I'm afraid that this would affect all other applications using the same
server. We still have other options to use. When the above tracing shows
a result, i.e. which of the high level operations of the dblayer uses
more time (more milliseconds) than normal, the next level is the detailed 
logging
of the ESQL/C operations (where we added time stamps which normaly this
logging does not have in its source code).

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Sahra Wagenknecht im Bundestag: "Aber die Vorstellung, dass wir Putin dadurch
bestrafen, dass wir Millionen Familien in Deutschland in die Armut stürzen und
dass wir unsere Industrie zerstören, während Gasprom Rekordgewinne macht – ja,
wie bescheuert ist das denn?" Recht hat sie!

Re: Mysterious performance degradation in exceptional cases

[Adrian Klaver]

On 9/18/22 02:30, Matthias Apitz wrote:

El día jueves, septiembre 15, 2022 a las 08:40:24a. m. -0700, Adrian Klaver 
escribió:


On 9/14/22 22:33, Matthias Apitz wrote:

El día miércoles, septiembre 14, 2022 a las 07:19:31a. m. -0700, Adrian Klaver 
escribió:


On 9/14/22 01:31, Matthias Apitz wrote:





The 'app-server' does not answer, but does the database not answer also?

Have you looked to see if the database is providing a response in a timely
manner and if it is getting 'lost' in the 'app-server'?


I do not "think" that the time is spent elsewhere in the 'app-server'.
to make a fact of the "thinking", I enabled the tracing of our dblayer
showing how many milliseconds have been spent between entering the dblayer and
returning the result to the application layers. This is in effect since
36 hours now. Since September 13 the problem has not showed up anymore.
We are waiting for it...


Is this with or without your every 10 sec 'ping' search program?




Also have you considered Tom Lane's suggestion of using auto_explain?


I'm afraid that this would affect all other applications using the same
server. We still have other options to use. When the above tracing shows
a result, i.e. which of the high level operations of the dblayer uses
more time (more milliseconds) than normal, the next level is the detailed 
logging
of the ESQL/C operations (where we added time stamps which normaly this
logging does not have in its source code).


You can load auto_explain per session as shown here:

https://www.postgresql.org/docs/current/auto-explain.html

F.4.2. Example



Thanks

matthias




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Mysterious performance degradation in exceptional cases

[Matthias Apitz]

El día domingo, septiembre 18, 2022 a las 07:47:32a. m. -0700, Adrian Klaver 
escribió:

> On 9/18/22 02:30, Matthias Apitz wrote:
> > El día jueves, septiembre 15, 2022 a las 08:40:24a. m. -0700, Adrian Klaver 
> > escribió:
> > 
> > > On 9/14/22 22:33, Matthias Apitz wrote:
> > > > El día miércoles, septiembre 14, 2022 a las 07:19:31a. m. -0700, Adrian 
> > > > Klaver escribió:
> > > > 
> > > > > On 9/14/22 01:31, Matthias Apitz wrote:
> > > 
> 
> > > The 'app-server' does not answer, but does the database not answer also?
> > > 
> > > Have you looked to see if the database is providing a response in a timely
> > > manner and if it is getting 'lost' in the 'app-server'?
> > 
> > I do not "think" that the time is spent elsewhere in the 'app-server'.
> > to make a fact of the "thinking", I enabled the tracing of our dblayer
> > showing how many milliseconds have been spent between entering the dblayer 
> > and
> > returning the result to the application layers. This is in effect since
> > 36 hours now. Since September 13 the problem has not showed up anymore.
> > We are waiting for it...
> 
> Is this with or without your every 10 sec 'ping' search program?

The 'ping' search was stopped on September 16, 7:45 CEST. The 'ping' search
never showed the problem.

> > > Also have you considered Tom Lane's suggestion of using auto_explain?
> > 
> > I'm afraid that this would affect all other applications using the same
> > server. We still have other options to use. When the above tracing shows
> > a result, i.e. which of the high level operations of the dblayer uses
> > more time (more milliseconds) than normal, the next level is the detailed 
> > logging
> > of the ESQL/C operations (where we added time stamps which normaly this
> > logging does not have in its source code).
> 
> You can load auto_explain per session as shown here:

Every connect from the ILL forks a new 'app-server' session which
creates a new ESQL/C session, and all this occur randomly when the ILL
wants to search for a book title if this is available in that library.
In short: no way.

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Sahra Wagenknecht im Bundestag: "Aber die Vorstellung, dass wir Putin dadurch
bestrafen, dass wir Millionen Familien in Deutschland in die Armut stürzen und
dass wir unsere Industrie zerstören, während Gasprom Rekordgewinne macht – ja,
wie bescheuert ist das denn?" Recht hat sie!

Re: Mysterious performance degradation in exceptional cases

[Adrian Klaver]

On 9/18/22 09:36, Matthias Apitz wrote:


You can load auto_explain per session as shown here:


Every connect from the ILL forks a new 'app-server' session which
creates a new ESQL/C session, and all this occur randomly when the ILL
wants to search for a book title if this is available in that library.
In short: no way.


How is it random?

Are you saying that the ESQL/C session does not 'know' the search is 
coming from the ILL connection?




Thanks

matthias




--
Adrian Klaver
adrian.kla...@aklaver.com

Where's the doc for "array()" — as in "select array(values (17), (42))"

[Bryn Llewellyn]

I noticed that "array()" is used in the query that implements the "\du" psql 
meta-command. It has some similarity with "array_agg()" thus:

create temporary view x(v) as (values (17), (42));
select array_agg(v) from x;
select array(select v from x);

But there are differences. The "array()" function requires that its argument is 
a subquery that returns a single column. (Its data type can be composite.) But 
the "array_agg()" function's argument must be the select list in the larger 
context of a select statement—and in general together with "group by".

It seems that the functionality of "array()" can always be achieved by using 
"array_agg()"—but the overall construct might be less compact. I don't know if 
the converse is true. (I didn't think it through.)

Anyway, I can't find where "array()" is documented. Google does find me a hit 
on stackexchange.com . But it doesn't x-ref to the 
PG doc.

Finally, my new realization that even the humble "length()" is just a function 
in the pg_catalog schema that comes with any newly-created database led me to 
think that I'd find "array()" there. But while "\df length" and "\df array_agg" 
give me useful information, "\df array" gives me nothing. More carefully 
stated, this:

select proname from pg_proc
where proname in ('length', 'array_agg', 'array');

gets rows for "length" and "array_agg" but not for "array". What's going on 
here? I wondered if "array()" might be part of SQL syntax, like (loosely) 
"as(…)" is. But the account of the "select" statement doesn't mention it.

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Adrian Klaver]

On 9/18/22 13:25, Bryn Llewellyn wrote:
I noticed that "array()" is used in the query that implements the "\du" 
psql meta-command. It has some similarity with "array_agg()" thus:


create temporary view x(v) as (values (17), (42));
select array_agg(v) from x;
select array(select v from x);



Anyway, I can't find where "array()" is documented. Google does find me 
a hit on stackexchange.com . But it doesn't 
x-ref to the PG doc.


https://www.postgresql.org/docs/current/sql-expressions.html

4.2.12. Array Constructors

"It is also possible to construct an array from the results of a 
subquery. In this form, the array constructor is written with the key 
word ARRAY followed by a parenthesized (not bracketed) subquery. For 
example:


SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
..."

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Adrian Klaver]

On 9/18/22 13:25, Bryn Llewellyn wrote:
I noticed that "array()" is used in the query that implements the "\du" 
psql meta-command. It has some similarity with "array_agg()" thus:


create temporary view x(v) as (values (17), (42));
select array_agg(v) from x;
select array(select v from x);

But there are differences. The "array()" function requires that its 
argument is a subquery that returns a single column. (Its data type can 
be composite.) But the "array_agg()" function's argument must be the 
select list in the larger context of a select statement—and in general 
together with "group by".




Lack of an explicit GROUP BY falls through to an implied one:

https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY

"If there are aggregate functions but no GROUP BY clause, the query is 
treated as having a single group comprising all the selected rows."



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[David G. Johnston]

On Sunday, September 18, 2022, Adrian Klaver 
wrote:

> On 9/18/22 13:25, Bryn Llewellyn wrote:
>
>> I noticed that "array()" is used in the query that implements the "\du"
>> psql meta-command. It has some similarity with "array_agg()" thus:
>>
>> create temporary view x(v) as (values (17), (42));
>> select array_agg(v) from x;
>> select array(select v from x);
>>
>>
> Anyway, I can't find where "array()" is documented. Google does find me a
>> hit on stackexchange.com . But it doesn't
>> x-ref to the PG doc.
>>
>
> https://www.postgresql.org/docs/current/sql-expressions.html
>

Which on can find fairly directly via the index entry for array:

https://www.postgresql.org/docs/current/bookindex.html#indexdiv-A

Or browsing the syntax chapter via the table of contents (everything is
either a function or syntax, and you ruled out the former).

https://www.postgresql.org/docs/current/sql-syntax.html

David J.

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> I noticed that "array()" is used in the query that implements the "\du" psql 
>> meta-command. It has some similarity with "array_agg()" thus:
>> 
>> create temporary view x(v) as (values (17), (42));
>> select array_agg(v) from x;
>> select array(select v from x);
>> 
>> Anyway, I can't find where "array()" is documented. Google does find me a 
>> hit on stackexchange-dot-com. But it doesn't x-ref to the PG doc.
> 
> www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
> 
>> 4.2.12. Array Constructors
>> 
>> It is also possible to construct an array from the results of a subquery. In 
>> this form, the array constructor is written with the key word ARRAY followed 
>> by a parenthesized (not bracketed) subquery. For example:
>> 
>> SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

Thanks. I can't, even now, formulate a search that finds this—unless I know 
that it's a kind of array constructor. (I'll concede, with the benefit of 
hindsight, that I should have thought of that.) I hope that I'll be able to 
learn to navigate the PG docs better over time.

Why is the "array()" constructor not found in "pg_proc"? After all, section 
4.2.12 refers to "array_agg()" as a constructor. And that *is* found in 
"pg_proc".

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[David G. Johnston]

On Sunday, September 18, 2022, Bryn Llewellyn  wrote:
>
> Why is the "array()" constructor not found in "pg_proc"?
>

Because it isn’t a function.


>
>  After all, section 4.2.12 refers to "array_agg()" as a constructor. And
> that *is* found in "pg_proc".
>

I do not see this 4.2.12 reference you speak of.

David J.

Re: Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

[Tom Lane]

Bryn Llewellyn  writes:
> Why is the "array()" constructor not found in "pg_proc"?

Because it isn't a function.  Yeah, it kind of looks like one,
but its argument is a subquery.  If SQL had first-class functions
and closures, maybe ARRAY() could be implemented as an ordinary
function.  But I don't see any plausible way to do that as things
stand.

There are a bunch of other things that look like functions but
aren't in pg_proc, too :-(.  Most of them are just catering to
the SQL committee's weird allergy to writing functions with
plain function syntax.  But ARRAY()'s problem is semantic not
syntactic.

regards, tom lane

Re[4]: CVE-2022-2625

[misha1966 misha1966]

How can I check this vulnerability. Which SQL to execute?
  
>Четверг, 15 сентября 2022, 17:22 +09:00 от Laurenz Albe 
>:
> 
>On Thu, 2022-09-15 at 07:24 +0300, misha1966 misha1966 wrote:
>> > Четверг, 15 сентября 2022, 1:58 +09:00 от Laurenz Albe < 
>> > laurenz.a...@cybertec.at >:
>> >  
>> > On Wed, 2022-09-14 at 17:02 +0300, misha1966 misha1966 wrote:
>> > > Tell me, is there a CVE-2022-2625 vulnerability in posgresql 9.5?
>> > > If so, who knows how to patch it? Patches from version 10 are not 
>> > > suitable at all...
>> >
>> > Yes, that vulnerability exists in 9.5.
>> >
>> > To patch that, you'd have to try and backpatch the commit to 9.5 yourself:
>> >  
>> > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b9b21acc766db54d8c337d508d0fe2f5bf2daab0
>> >
>> > Since 9.5 is out of support, there are no more bugfixes for it provided
>> > by the community. If security were a real concern for you, you would
>> > certainly not be running a PostgreSQL version that is out of support.
>>
>> All business processes are hooked on postgresql 9.5. There is no way to 
>> update.
>> Unfortunately, I don't have the proper qualifications to change it.
>So these "business processes" are more important than security at your site.
>That's fine; everybody has to make their choices.
>But remember that there are also known data-eating bugs lurking in your
>outdated software.
>
>Yours,
>Laurenz Albe
>--
>Cybertec |  https://www.cybertec-postgresql.com
 

Re: Re[4]: CVE-2022-2625

[Laurenz Albe]

On Mon, 2022-09-19 at 07:35 +0300, misha1966 misha1966 wrote:
> > Четверг, 15 сентября 2022, 17:22 +09:00 от Laurenz Albe 
> > :
> >  
> > On Thu, 2022-09-15 at 07:24 +0300, misha1966 misha1966 wrote:
> > > > Четверг, 15 сентября 2022, 1:58 +09:00 от Laurenz Albe 
> > > > :
> > > >  
> > > > On Wed, 2022-09-14 at 17:02 +0300, misha1966 misha1966 wrote:
> > > > > Tell me, is there a CVE-2022-2625 vulnerability in posgresql 9.5?
> > > > > If so, who knows how to patch it? Patches from version 10 are not 
> > > > > suitable at all...
> > > > 
> > > > Yes, that vulnerability exists in 9.5.
> > > > 
> > > > To patch that, you'd have to try and backpatch the commit to 9.5 
> > > > yourself:
> > > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b9b21acc766db54d8c337d508d0fe2f5bf2daab0
> > > > 
> > > > Since 9.5 is out of support, there are no more bugfixes for it provided
> > > > by the community. If security were a real concern for you, you would
> > > > certainly not be running a PostgreSQL version that is out of support.
> > > 
> > > All business processes are hooked on postgresql 9.5. There is no way to 
> > > update.
> > > Unfortunately, I don't have the proper qualifications to change it.
> > 
> > So these "business processes" are more important than security at your site.
> > That's fine; everybody has to make their choices.
> > But remember that there are also known data-eating bugs lurking in your
> > outdated software.
>
> How can I check this vulnerability. Which SQL to execute?

Look at the commit message in the link above.

You create a database object (a function or view).  Then you create an 
extension,
and in the SQL script you put "CREATE OR REPLACE ..." for that same object.

If PostgreSQL allows you to create the extension, you are vulnerable.

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