Re: Postgres Version Upgrade to 14.1 error

[Pavel Stehule]

Hi

pá 4. 2. 2022 v 7:02 odesílatel rob stan  napsal:

> Hello,
>
> We have "pgq" extensions on our clusters when I am trying to upgrade from
> 9.6.24 to 14.1 with pg_upgrade --link method, i am getting error;
>
>
> First i was getting this error ;
>
> could not load library "$libdir/pgq_lowlevel": ERROR:  could not access
> file "$libdir/pgq_lowlevel": No such file or directory
> could not load library "$libdir/pgq_triggers": ERROR:  could not access
> file "$libdir/pgq_triggers": No such file or directory
>
> After i copied pgq files to extension directory;
> /usr/lib/postgresql/9.6/lib# cp pgq_lowlevel.so
> /usr/lib/postgresql/14.1/lib/
> /usr/lib/postgresql/9.6/lib# cp pgq_triggers.so
> /usr/lib/postgresql/14.1/lib/
>
> I am getting this error message;
> could not load library "$libdir/pgq_triggers": ERROR:  could not load
> library "/usr/lib/postgresql/14.1/lib/pgq_triggers.so":
> /usr/lib/postgresql/14.1/lib/pgq_triggers.so: undefined symbol: elog_finish
>
>
> Do you have any ideas on how to solve this problem?
>

PostgreSQL extensions are compiled against one specific major release - so
you cannot use pgq for 9.6 in Postgres 14. You should install pgq for
PostgreSQL 14.

Regards

Pavel


> Thanks!
>

Postgres Version Upgrade to 14.1 error

[rob stan]

Hello,

We have "pgq" extensions on our clusters when I am trying to upgrade from
9.6.24 to 14.1 with pg_upgrade --link method, i am getting error;


First i was getting this error ;

could not load library "$libdir/pgq_lowlevel": ERROR:  could not access
file "$libdir/pgq_lowlevel": No such file or directory
could not load library "$libdir/pgq_triggers": ERROR:  could not access
file "$libdir/pgq_triggers": No such file or directory

After i copied pgq files to extension directory;
/usr/lib/postgresql/9.6/lib# cp pgq_lowlevel.so
/usr/lib/postgresql/14.1/lib/
/usr/lib/postgresql/9.6/lib# cp pgq_triggers.so
/usr/lib/postgresql/14.1/lib/

I am getting this error message;
could not load library "$libdir/pgq_triggers": ERROR:  could not load
library "/usr/lib/postgresql/14.1/lib/pgq_triggers.so":
/usr/lib/postgresql/14.1/lib/pgq_triggers.so: undefined symbol: elog_finish


Do you have any ideas on how to solve this problem?

Thanks!

Re: Can Postgres beat Oracle for regexp_count?

[Merlin Moncure]

On Wed, Feb 2, 2022 at 4:26 PM Tom Lane  wrote:
>
> "David G. Johnston"  writes:
> > Given we don't have a regexp_count function this isn't surprising...
>
> FYI, it's there in HEAD.
>
> In the meantime, you could possibly do something like
>
> =# select count(*) from regexp_matches('My High Street', '([A-Z][a-z]+[\s])', 
> 'g');
>  count
> ---
>  2
> (1 row)

alternate version:
postgres=# select array_upper(regexp_split_to_array('My High Street My
High Street', 'My High Street'), 1) - 1;
 ?column?
──
2

can help to slide this into complex queries a little bit easier by
avoiding the aggregation :-).

merlin

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[A Shaposhnikov]

Tomas,

thank you! The query:

select 1 from data as d, data_class as dc
where dc.data_id = d.id and d.id > 205284974
  and dc.data_id > 205284974 -- new condition
   order by d.id
   limit 1000;

totally solved it - it is now fast under all conditions! I thought
that the optimizer would be able to infer it itself.

Thank you

On Thu, Feb 3, 2022 at 4:24 PM Tomas Vondra
 wrote:
>
>
>
> On 2/3/22 20:32, A Shaposhnikov wrote:
> > I made a mistake yesterday claiming that the created statistics
> > changed the row counts in the estimates - it did not  - I looked at
> > the wrong query yesterday. In the correct query plan the row estimate
> > still differs from the actual by many orders of magnitude:
> >
> > Nested Loop  (cost=1.01..27584834.53 rows=59608439 width=164) (actual
> > time=0.047..2.723 rows=854 loops=1)
> >
> > It completely ignores the "limit" in SQL statement when doing the row
> > estimates - the estimates do not change when I change it. The business
> > logic really needs only 1000 rows in the result, I never need the
> > whole table in the result. The query always runs fast if the limit is
> > <= 46, when the limit >=47, it sometimes chooses to do a merge join on
> > 2 tables with hundreds of millions of rows  instead of using the
> > indexes. The runtime difference is 4000x.
> >
>
> Yes, it ignores LIMIT while calculating the estimates - the estimates
> below LIMIT are as if the query executed to completion. But the "actual"
> value are affected by LIMIT, because the execution terminates early. So
> you can't compare these two values and conclude the estimates are off.
>
> Let me illustrate this on an example:
>
> create table t (a int, b int);
> insert into t select i, i from generate_series(1,100) s(i);
> create index on t (a,b);
> vacuum analyze t;
>
> explain analyze select b from t order by a limit 10;
>QUERY PLAN
> ---
>   Limit  (cost=0.42..0.79 rows=10 width=8)
>  (actual time=0.018..0.033 rows=10 loops=1)
> ->  Index Only Scan using t_a_b_idx on t
> (cost=0.42..36214.93 rows=100 width=8)
> (actual time=0.016..0.021 rows=10 loops=1)
>   Heap Fetches: 10
>   Planning Time: 0.047 ms
>   Execution Time: 0.049 ms
>
> The database has *perfect* stats in this case and the estimates are spot
> on too. But comparing rows=100 and rows=10 would lead to conclusion
> to a bogus conclusion that the estimates are wrong.
>
> Which is why I suggested getting an explain for the query without the
> limit clause.
>
> > I migrated the data to the latest postgres 14.1. Both versions run
> > either the slow plan or the fast plan seemingly at random. The only
> > reliable way to make it choose the fast plan is to decrease the
> > effective_cache_size to a value 20x lower than the memory available in
> > the system. Dropping and creating the statistics on the join table
> > makes no difference in the estimated row counts and query plans.
> > PostgreSql seems to be caching something internally and choosing the
> > query plans at random - sometimes it is fast, sometimes 4000x slower
> > without any changes in configuration or statistics. The runtime
> > difference is 4000x.  Such randomness is clearly unacceptable. I think
> > I would have to try to use the "hint" extension suggested by Imre to
> > make it consistently choose the fast plan.
> >
>
> Does the plan change depending on parameters in the query. Or does it
> change if you run the same query (including the same parameters in all
> the conditions)?
>
> One possible explanation is that there's some sort of correlation
> between parameters. Limit assumes the matching rows are distributed
> uniformly in the input, and if that's not true (due to some sort of
> correlation), it may pick the wrong plan.
>
> In this case there are joins so the correlation may be more complicated
> - e.g. through a join condition, between (dc.class_id, dc.data_id) or
> something like that.
>
> This part of the plan hints this might be the case, because it forces
> scanning a huge part of data_class table just to get to the interesting
> data_id values.
>
>-> Index Only Scan using data_class_pkey on data_class ta
>   (cost=0.57..4935483.78 rows=216964862 width=8)
>   (actual time=0.018..35022.908 rows=151321889 loops=1)
>   Heap Fetches: 151321889
>
> That's hard to fix, because there's no way to tell the database about
> such dependencies. I'd try two things:
>
> (1) Try putting the same condition on ID on data_class too. I mean, if
> you do
>
>select 1 from data as d, data_class as dc
> where dc.data_id = d.id and d.id > 205284974
>order by d.id
>limit 1000;
>
> then clearly dc.data_id > 205284974, but the database does not realize
> it and will scan the index from start for mergejoin. Nestedloop does
> direct lookup for individual ID values, so it doesn't have this issue.
>
> So try this:
>
> 

Re: Increase fetch fize of oracl_fdw(ALTER SERVER)

[aditya desai]

Thanks David.

On Fri, Feb 4, 2022 at 9:44 AM David G. Johnston 
wrote:

> On Thu, Feb 3, 2022 at 9:03 PM aditya desai  wrote:
>
>>
>> For Postgres_Fdw we have below command to increase the fetch size.
>>
>> ALTER SERVER ServerName OPTIONS ( fetch_size='5');
>>
>> Do we have a similar command for oracle_fdw?
>>
>>
> The SQL command itself is generic.  The option name that seems to be
> similar is called "prefetch".  It is documented in the project's README.md
> file.
>
> David J.
>
>

Re: Increase fetch fize of oracl_fdw(ALTER SERVER)

[David G. Johnston]

On Thu, Feb 3, 2022 at 9:03 PM aditya desai  wrote:

>
> For Postgres_Fdw we have below command to increase the fetch size.
>
> ALTER SERVER ServerName OPTIONS ( fetch_size='5');
>
> Do we have a similar command for oracle_fdw?
>
>
The SQL command itself is generic.  The option name that seems to be
similar is called "prefetch".  It is documented in the project's README.md
file.

David J.

Fwd: Increase fetch fize of oracl_fdw(ALTER SERVER)

[aditya desai]

Hi,
For Postgres_Fdw we have below command to increase the fetch size.

ALTER SERVER ServerName OPTIONS ( fetch_size='5');

Do we have a similar command for oracle_fdw?

Thanks in advance.

Regards,
Aditya.

Re: Undetected Deadlock

[Michael Harris]

> If Michael's analysis were accurate, I'd agree that there is a robustness
> issue, but I don't think there is.  See timeout.c:220:

Actually that only sets a new timer after the nearest timeout has expired.

The pattern I was seeing went like this:

1. Command occurs during which a signal was not delivered (due to our
custom function). signal_pending is set and never cleared.

2. This command finishes normally, so the deadlock timeout is removed.
As long as there are either no more active timeouts, or the nearest
active timeout is still in the future, then no interval timer is set.

3. A later command starts, and again because the new timeout is in the
future, no new interval timer is set.

4. This transaction gets into a deadlock, but because the interval
timer is not running, ALRM is never received so the process is stuck.

In fact once a backend gets into this state it is permanently stuck
like this because new calls to schedule timeouts are always scheduling
them for a future time.

I was thinking that to improve robustness, we could add a check for
`now < signal_due_at` to schedule_alarm line 300:

if (signal_pending && now < signal_due_at && nearest_timeout
>= signal_due_at)
return;

That way, even if we think we are due for a signal, if we are already
past the time we were expecting it we go ahead and set a new one
anyway.

Even though the fault in this case was a faulty custom function, there
could be other scenarios in which a signal isn't delivered (that bit
of code Tom highlighted is specifically to cater for that, going by
the comments).

One other thing that struck me when reading this code: the variable
signal_due_at is not declared as volatile sig_atomic_t, even though it
is read from / written to by the signal handler. Maybe that could
cause problems?

One other question: I've fixed our custom function, so that it
correctly restores any interval timers that were running, but of
course if our function takes longer than the remaining time on the
postgres timer the signal will be delivered late. Beyond the fact that
a deadlock or statement timeout will take longer than expected, are
there any other negative consequences? Are any of the timeouts
time-critical such that being delayed by a few seconds would cause a
problem?

Thanks for any feedback.

Regards
Mike

Re: Subscription stuck at initialize state

[Abhishek Bhola]

Hi Vijaykumar

Thank you so much for taking out so much of your time to recreate the bug.
I checked the max_logical_replication_workers on both the  nodes and they
are set at 4 at the moment.
The reason why it is failing is that there are 3 existing replications on
the target node already and when I create this 4th one, it is not able to
create that temporary replication worker, needed to copy the initial data,
and therefore it never moves ahead of that state.
Since these are production databases, I can't restart them during the
weekdays, so I will try to change them during the weekend.
But I did find the warning message in the target DB logs.

"WARNING,53400,"out of logical replication worker slots",,"You might need
to increase max_logical_replication_workers.",,,"","logical replication
worker"
So I am sure this will work.

THANK YOU SO MUCH.


On Fri, Feb 4, 2022 at 3:04 AM Vijaykumar Jain <
vijaykumarjain.git...@gmail.com> wrote:

> trimming the email, to avoid noise.
>
> I spent a lot of time trying multiple options/combinations and finally
> managed to replicate your debug output.
> this is when i have ,
>
> postgres=# show max_logical_replication_workers;
> * max_logical_replication_workers*
> -
> * 2*
> (1 row)
>
>
> on publisher
> postgres@controller:~$ psql -p 5001
> psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
> Type "help" for help.
>
> postgres=# create table t_bytea(id int primary key, somebytea bytea);
> CREATE TABLE
> postgres=# create table t1(id int);
> CREATE TABLE
> postgres=# create table t2(id int);
> CREATE TABLE
> postgres=# create table t3(id int);
> CREATE TABLE
> postgres=# create publication mypub1 for table t1;
> CREATE PUBLICATION
> postgres=# create publication mypub2 for table t2;
> CREATE PUBLICATION
> postgres=# create publication mypub3 for table t3;
> CREATE PUBLICATION
> postgres=# create publication mypub4 for table t3;
> CREATE PUBLICATION
> postgres=# create publication mypub5 for table t_bytea;
> CREATE PUBLICATION
> postgres=# insert into t_bytea  select x,repeat(repeat('xxx', 100),
> 1000)::bytea from generate_series(1, 1000) x;
> INSERT 0 1000
>
>
>
> on subscriber
> postgres@controller:~$ psql -p 5002
> psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
> Type "help" for help.
>
> postgres=# create table t_bytea(id int primary key, somebytea bytea);
> CREATE TABLE
> postgres=#  create table t1(id int);
> CREATE TABLE
> postgres=#  create table t2(id int);
> CREATE TABLE
> postgres=#  create table t3(id int);
> CREATE TABLE
> postgres=#  create table t4(id int);
> CREATE TABLE
> postgres=#  create table t5(id int);
> CREATE TABLE
> postgres=# create subscription mysub1 connection 'port=5001' publication
> mypub1;
> NOTICE:  created replication slot "mysub1" on publisher
> CREATE SUBSCRIPTION
> postgres=# create subscription mysub2 connection 'port=5001' publication
> mypub2;
> NOTICE:  created replication slot "mysub2" on publisher
> CREATE SUBSCRIPTION
> postgres=# create subscription mysub3 connection 'port=5001' publication
> mypub3;
> NOTICE:  created replication slot "mysub3" on publisher
> CREATE SUBSCRIPTION
> postgres=# create subscription mysub4 connection 'port=5001' publication
> mypub4;
> NOTICE:  created replication slot "mysub4" on publisher
> CREATE SUBSCRIPTION
> postgres=# create subscription mysub5 connection 'port=5001' publication
> mypub5;
> NOTICE:  created replication slot "mysub5" on publisher
> CREATE SUBSCRIPTION
> *postgres=# select count(1) from t_bytea;*
> * count*
> *---*
> * 0*
> *(1 row)*
>
> postgres=# table pg_subscription_rel;
>  srsubid | srrelid | srsubstate | srsublsn
> -+-++---
>16406 |   16391 | r  | 0/1722838
>  *  16407 |   16394 | i  |*
> *   16408 |   16397 | i  |*
> *   16409 |   16397 | i  |*
> *   16410 |   16384 | i  |*
> (5 rows)
>
> # as expected no data in t_bytea (as it could not get any worker)
> postgres=# select count(1) from t_bytea;
>  count
> ---
>  0
> (1 row)
>
>
>
> but logs clearly state the problem
> 2022-02-03 23:18:31.107 IST [3430] LOG:  logical replication table
> synchronization worker for subscription "mysub1", table "t1" has started
> 2022-02-03 23:18:31.138 IST [3430] LOG:  logical replication table
> synchronization worker for subscription "mysub1", table "t1" has finished
> 2022-02-03 23:18:40.730 IST [3433] LOG:  logical replication apply worker
> for subscription "mysub2" has started
> *2022-02-03 23:18:40.737 IST [3433] WARNING:  out of logical replication
> worker slots*
> *2022-02-03 23:18:40.737 IST [3433] HINT:  You might need to increase
> max_logical_replication_workers.*
> *2022-02-03 23:18:45.865 IST [3433] WARNING:  out of logical replication
> worker slots*
>
>
> *#publisher logs*
> 2022-02-03 23:18:31.096 IST [3427] STATEMENT:  CREATE_REPLICATION_SLOT
> "mysub1" LOGICAL pgoutput NOEXPORT_SNAPSHOT
> 2022-02-03 23:18:31.106 IST [3429] LOG:  starting logical decoding 

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[Michael Lewis]

I would guess that autovacuum is analyzing the table and causing the stats
to change which is resulting in different estimates that result in
different plans. Unless you can get the estimate much more accurate, you
won't get far with expecting a stable plan that performs well.

How is data_class_pkey? If you run a query like this, how far off are the
estimates?


explain analyze
select d.time as time,d.id as id, a.query_symbol as query_symbol

from
data as d
join data_class as dc ON dc.data_id = d.id
join class as a ON dc.class_id = a.id
where
d.id > 205284974
order by d.id
limit 1000;

If you run 'analyze data( id );' and then run the query again, do you get a
better estimate? Have you tried adjusting default_stats_target? Are you
running the default value for random_page_cost with SSDs?

I'm seeing Index Only Scan nodes, but a high number of fetches so it seems
like you would benefit from vacuum to update pg_class.relallvisible value.

Re: pg_cron for vacuum - dynamic table set

[Ron]

On 2/3/22 3:29 PM, saket bansal wrote:
Hi, I am trying to schedule pg_cron to generate a set of commands and then 
run them. E.g |select 'vacuum freeze '||table_name from 
inventory_for_vacuum \gexec| . This works well at command line, but when 
scheduled in pg_cron, it fails with syntax error |ERROR: syntax error at 
or near "\"| .
Since vacuum doesn't work inside a transaction block |ERROR: VACUUM cannot 
run inside a transaction block|, I cannot use a function directly to run 
over a loop.

Any guidance to make it work or an alternative?


What about running the command from regular cron?

--
Angular momentum makes the world go 'round.

Re: pg_cron for vacuum - dynamic table set

[Michael Lewis]

Can't you use a do script to construct and execute the statement?

>

Re: sort order for UTF-8 char column with Japanese UTF-8

[Thomas Munro]

On Fri, Feb 4, 2022 at 8:11 AM Matthias Apitz  wrote:
> On my FreeBSD laptop the same file sorts as
>
> guru@c720-r368166:~ $ LANG=de_DE.UTF-8 sort swd
> A
> ゲアハルト・A・リッター
> ゲルハルト・A・リッター
> チャールズ・A・ビアード
> A010STRUKTUR
> A010STRUKTUR
> A010STRUKTUR
> A0150SUPRALEITER

Wow, so it's one thing to have a different default "script order" than
glibc and ICU (which is something you can customise IIRC), but isn't
something broken here if the Japanese text comes between "A" and
"A0..."??  Hmm, it's almost as if it completely ignored the Japanese
text.  From my FreeBSD box:

tmunro=> select * from t order by x collate "de_DE.UTF-8";
x
--
 ゲアハルト
 A
 ゲアハルト・A・リッター
 A0
 A010STRUKTUR
 AA
 ゲアハルト・AA・リッター
 ゲアハルト・B・リッター
(8 rows)

tmunro=> select * from t order by x collate "ja_JP.UTF-8";
x
--
 A
 A0
 A010STRUKTUR
 AA
 ゲアハルト
 ゲアハルト・AA・リッター
 ゲアハルト・A・リッター
 ゲアハルト・B・リッター
(8 rows)

Seems like something to investigate in FreeBSD land.

Re: pg_cron for vacuum - dynamic table set

[David G. Johnston]

On Thu, Feb 3, 2022 at 2:29 PM saket bansal  wrote:

> Hi, I am trying to schedule pg_cron to generate a set of commands and then
> run them. E.g select 'vacuum freeze '||table_name from
> inventory_for_vacuum \gexec . This works well at command line, but when
> scheduled in pg_cron, it fails with syntax error ERROR: syntax error at
> or near "\" .
>

Right, because you don't get to use psql meta-commands if you are not using
psql.

>
> Since vacuum doesn't work inside a transaction block ERROR: VACUUM cannot
> run inside a transaction block, I cannot use a function directly to run
> over a loop.
> Any guidance to make it work or an alternative?
>

Have a pg_cron job that dynamically creates other pg_cron jobs that only
run once?

David J.

Re: pg_cron for vacuum - dynamic table set

[Adrian Klaver]

On 2/3/22 1:29 PM, saket bansal wrote:
Hi, I am trying to schedule pg_cron to generate a set of commands and 
then run them. E.g |select 'vacuum freeze '||table_name from 
inventory_for_vacuum \gexec| . This works well at command line, but when 
scheduled in pg_cron, it fails with syntax error |ERROR: syntax error at 
or near "\"| .


\gexec is a psql metacommand and will only run in that client.

I'm guessing that is not the case in the pg_cron task?

Since vacuum doesn't work inside a transaction block |ERROR: VACUUM 
cannot run inside a transaction block|, I cannot use a function directly 
to run over a loop.

Any guidance to make it work or an alternative?





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

pg_cron for vacuum - dynamic table set

[saket bansal]

Hi, I am trying to schedule pg_cron to generate a set of commands and then
run them. E.g select 'vacuum freeze '||table_name from inventory_for_vacuum
\gexec . This works well at command line, but when scheduled in pg_cron, it
fails with syntax error ERROR: syntax error at or near "\" .
Since vacuum doesn't work inside a transaction block ERROR: VACUUM cannot
run inside a transaction block, I cannot use a function directly to run
over a loop.
Any guidance to make it work or an alternative?

Re: Regular Expression For Duplicate Words

[Shaozhong SHI]

Hi, Peter,  Interesting.

On Thu, 3 Feb 2022 at 19:48, Peter J. Holzer  wrote:

> On 2022-02-02 08:00:00 +, Shaozhong SHI wrote:
> > regex - Regular Expression For Duplicate Words - Stack Overflow
> >
> > Is there any example in Postgres?
>
> It's pretty much the same as with other regexp dialects: User word
> boundaries and a word character class to match any word and then use a
> backreference to match a duplicate word. All the building blocks are
> described on
>
> https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> and except for [[:<:]] and [[:>:]] for the word boundaries, they are
> also pretty standard.
>
> So
>
> [[:<:]]start of word
> ([[:alpha:]]+) one or more alphabetic characters in a capturing group
> [[:>:]]end of word
> \W+one or more non-word characters
> [[:<:]]start of word
> \1 the content of the first (and only) capturing group
> [[:>:]]end of word
>
> All together:
>
> select * from t where t ~ '[[:<:]]([[:alpha:]]+)[[:>:]]\W[[:<:]]\1[[:>:]]';
>
> Give a good example if you can.
>

Regards,

David

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[A Shaposhnikov]

the random_page_cost = 1.2 and seq_page_cost= 1 in my setup are the
same. I also run the vacuum analyze on all tables. I have no idea why
the optimizer thinks that the merge on 2 200M row tables is faster
than 50 index lookups, other than it "thinks" that in-memory merges
are very low cost regardless of size. Another guess is that it almost
ignores the "limit" parameter or uses it in some incorrect way.

On Thu, Feb 3, 2022 at 12:31 PM Vijaykumar Jain
 wrote:
>
>
> On Fri, 4 Feb 2022 at 01:03, A Shaposhnikov  wrote:
>>
>> I made a mistake yesterday claiming that the created statistics
>> changed the row counts in the estimates - it did not  - I looked at
>> the wrong query yesterday. In the correct query plan the row estimate
>> still differs from the actual by many orders of magnitude:
>>
>> Nested Loop  (cost=1.01..27584834.53 rows=59608439 width=164) (actual
>> time=0.047..2.723 rows=854 loops=1)
>>
>> It completely ignores the "limit" in SQL statement when doing the row
>> estimates - the estimates do not change when I change it. The business
>> logic really needs only 1000 rows in the result, I never need the
>> whole table in the result. The query always runs fast if the limit is
>> <= 46, when the limit >=47, it sometimes chooses to do a merge join on
>> 2 tables with hundreds of millions of rows  instead of using the
>> indexes. The runtime difference is 4000x.
>>
>> I migrated the data to the latest postgres 14.1. Both versions run
>> either the slow plan or the fast plan seemingly at random. The only
>> reliable way to make it choose the fast plan is to decrease the
>> effective_cache_size to a value 20x lower than the memory available in
>> the system. Dropping and creating the statistics on the join table
>> makes no difference in the estimated row counts and query plans.
>> PostgreSql seems to be caching something internally and choosing the
>> query plans at random - sometimes it is fast, sometimes 4000x slower
>> without any changes in configuration or statistics. The runtime
>> difference is 4000x.  Such randomness is clearly unacceptable. I think
>> I would have to try to use the "hint" extension suggested by Imre to
>> make it consistently choose the fast plan.
>
>
> there was a similar concern raised on slack, where on shared plan (where less 
> effective_cache_size resulted in faster execution)
>
> "
> Hey all, I have been diagnosing a slow running query and found that it speeds 
> up when the effective_cache_size is reduced.  It's set at ~21.5gb, and 
> lowering to 10gb or under massively improves the query.  Plans are here and 
> here.  You can see the difference is 33 - 42, where the order of execution 
> results in a bunch more rows having to be filtered out later in the first 
> plan.  Everything I've read suggests that a bigger effective_cache_size  
> should be better (assuming it's within the recommended boundaries of total 
> memory of course).  Can anyone offer any insight into why this might happen, 
> and what a good workaround would be?
> "
>
> https://explain.depesz.com/s/VsaY
> https://explain.depesz.com/s/nW3d
>
> I am not sure, though effective_cache_size is the culprit. if there are bad 
> estimates, even a simple query like below produces a poor plan.
> i did try to check the code postgres/costsize.c at master · postgres/postgres 
> (github.com)  (I am not a developer but try to read the english from code) 
> and tried to understand how effective_cache_sizealters the cost and changes 
> the path, but could not simulate on my laptop with small tables and less 
> resources.
> but yeah, i know 1TB is a huge setup, but can run vaccumdb and analyze all 
> the tables in question (maybe reindex concurrently if required) and check if 
> the row stats are still off in the plan and reducing effective_cache_size 
> improves the execution time? I am not an expert, but just try to figure out 
> if this is a red herring.
>
> i also play with some gucs as in PostgreSQL: Documentation: 13: 19.7. Query 
> Planning  to check if enabling/disabling  some of gucs results in a big 
> difference in execution and then try to zero in what needs immediate vaccum 
> or reindex or join count etc or bumping resources if required to use more 
> cache.
>
>
> postgres=# create table t(id int primary key, col1 int);
> CREATE TABLE
> postgres=# alter table t set (autovacuum_enabled = false, 
> toast.autovacuum_enabled = false);
> ALTER TABLE
>
> postgres=# insert into t select x, 1 from generate_series(1, 100) x;
> INSERT 0 100
>
> postgres=# \x
> Expanded display is off.
>
> postgres=# do $$
> declare i int;
> begin
> for i in 1..10 loop
> update t set col1 = i::int;
> commit;
> end loop;
> end; $$;
> DO
> ---this is the default plan picked up by optimizer (tables still not analyzed)
>
> postgres=# explain analyze select * from t where id < 100;
>   QUERY PLAN
> 

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[Vijaykumar Jain]

On Fri, 4 Feb 2022 at 01:03, A Shaposhnikov  wrote:

> I made a mistake yesterday claiming that the created statistics
> changed the row counts in the estimates - it did not  - I looked at
> the wrong query yesterday. In the correct query plan the row estimate
> still differs from the actual by many orders of magnitude:
>
> Nested Loop  (cost=1.01..27584834.53 rows=59608439 width=164) (actual
> time=0.047..2.723 rows=854 loops=1)
>
> It completely ignores the "limit" in SQL statement when doing the row
> estimates - the estimates do not change when I change it. The business
> logic really needs only 1000 rows in the result, I never need the
> whole table in the result. The query always runs fast if the limit is
> <= 46, when the limit >=47, it sometimes chooses to do a merge join on
> 2 tables with hundreds of millions of rows  instead of using the
> indexes. The runtime difference is 4000x.
>
> I migrated the data to the latest postgres 14.1. Both versions run
> either the slow plan or the fast plan seemingly at random. The only
> reliable way to make it choose the fast plan is to decrease the
> effective_cache_size to a value 20x lower than the memory available in
> the system. Dropping and creating the statistics on the join table
> makes no difference in the estimated row counts and query plans.
> PostgreSql seems to be caching something internally and choosing the
> query plans at random - sometimes it is fast, sometimes 4000x slower
> without any changes in configuration or statistics. The runtime
> difference is 4000x.  Such randomness is clearly unacceptable. I think
> I would have to try to use the "hint" extension suggested by Imre to
> make it consistently choose the fast plan.
>

there was a similar concern raised on slack, where on shared plan (where
less effective_cache_size resulted in faster execution)

*"*
*Hey all, I have been diagnosing a slow running query and found that it
speeds up when the effective_cache_size is reduced.  It's set at ~21.5gb,
and lowering to 10gb or under massively improves the query.  Plans are here
 and here
.  You can see the difference is 33 -
42, where the order of execution results in a bunch more rows having to be
filtered out later in the first plan.  Everything I've read suggests that a
bigger effective_cache_size  should be better (assuming it's within the
recommended boundaries of total memory of course).  Can anyone offer any
insight into why this might happen, and what a good workaround would be?*
*"*

https://explain.depesz.com/s/VsaY
https://explain.depesz.com/s/nW3d

I am not sure, though effective_cache_size is the culprit. if there are bad
estimates, even a simple query like below produces a poor plan.
i did try to check the code postgres/costsize.c at master ·
postgres/postgres (github.com)

(I am not a developer but try to read the english from code) and tried to
understand how effective_cache_sizealters the cost and changes the path,
but could not simulate on my laptop with small tables and less resources.
but yeah, i know 1TB is a huge setup, but can run vaccumdb and analyze all
the tables in question (maybe reindex concurrently if required) and check
if the row stats are still off in the plan and reducing effective_cache_size
improves the execution time? I am not an expert, but just try to figure out
if this is a red herring.

i also play with some gucs as in PostgreSQL: Documentation: 13: 19.7. Query
Planning   to
check if enabling/disabling  some of gucs results in a big difference in
execution and then try to zero in what needs immediate vaccum or reindex or
join count etc or bumping resources if required to use more cache.


postgres=# create table t(id int primary key, col1 int);
CREATE TABLE
postgres=# alter table t set (autovacuum_enabled = false,
toast.autovacuum_enabled = false);
ALTER TABLE

postgres=# insert into t select x, 1 from generate_series(1, 100) x;
INSERT 0 100

postgres=# \x
Expanded display is off.

postgres=# do $$
declare i int;
begin
for i in 1..10 loop
update t set col1 = i::int;
commit;
end loop;
end; $$;
DO
---this is the default plan picked up by optimizer (tables still not
analyzed)

postgres=# explain analyze select * from t where id < 100;
  QUERY PLAN
--
 Seq Scan on t  (cost=0.00..1472242.50 rows=28995800 width=8) (actual
time=198.740..4849.556 rows=99 loops=1)
   Filter: (id < 100)
   Rows Removed by Filter: 01
 Planning Time: 1.296 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming
true
   Timing: Generation 1.194 ms, Inlining 4.383 ms, Optimization 13.051 ms,
Emission 6.954 ms, 

Re: Regular Expression For Duplicate Words

[Peter J. Holzer]

On 2022-02-02 08:00:00 +, Shaozhong SHI wrote:
> regex - Regular Expression For Duplicate Words - Stack Overflow
> 
> Is there any example in Postgres?

It's pretty much the same as with other regexp dialects: User word
boundaries and a word character class to match any word and then use a
backreference to match a duplicate word. All the building blocks are
described on
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
and except for [[:<:]] and [[:>:]] for the word boundaries, they are
also pretty standard.

So

[[:<:]]start of word
([[:alpha:]]+) one or more alphabetic characters in a capturing group
[[:>:]]end of word
\W+one or more non-word characters
[[:<:]]start of word
\1 the content of the first (and only) capturing group
[[:>:]]end of word

All together:

select * from t where t ~ '[[:<:]]([[:alpha:]]+)[[:>:]]\W[[:<:]]\1[[:>:]]';

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

[A Shaposhnikov]

I made a mistake yesterday claiming that the created statistics
changed the row counts in the estimates - it did not  - I looked at
the wrong query yesterday. In the correct query plan the row estimate
still differs from the actual by many orders of magnitude:

Nested Loop  (cost=1.01..27584834.53 rows=59608439 width=164) (actual
time=0.047..2.723 rows=854 loops=1)

It completely ignores the "limit" in SQL statement when doing the row
estimates - the estimates do not change when I change it. The business
logic really needs only 1000 rows in the result, I never need the
whole table in the result. The query always runs fast if the limit is
<= 46, when the limit >=47, it sometimes chooses to do a merge join on
2 tables with hundreds of millions of rows  instead of using the
indexes. The runtime difference is 4000x.

I migrated the data to the latest postgres 14.1. Both versions run
either the slow plan or the fast plan seemingly at random. The only
reliable way to make it choose the fast plan is to decrease the
effective_cache_size to a value 20x lower than the memory available in
the system. Dropping and creating the statistics on the join table
makes no difference in the estimated row counts and query plans.
PostgreSql seems to be caching something internally and choosing the
query plans at random - sometimes it is fast, sometimes 4000x slower
without any changes in configuration or statistics. The runtime
difference is 4000x.  Such randomness is clearly unacceptable. I think
I would have to try to use the "hint" extension suggested by Imre to
make it consistently choose the fast plan.



On Wed, Feb 2, 2022 at 3:34 PM Tomas Vondra
 wrote:
>
> On 2/2/22 22:10, Artyom Shaposhnikov wrote:
> > the row estimate became ~1000x smaller with the stat in place, so it
> > looks like it grossly miscalculates the query plans without the stats
> > for large tables representing M:M relations.
> >
>
> Well, if the estimates are significantly off (and 3 orders of magnitude
> certainly qualifies), then all bets are off. There's no magical option
> that'd fix planning in such conditions.
>
> Ultimately, fixing the estimates (e.g. by creating extended statistics)
> is the right "fix" as it gives the optimizer the information needed to
> pick the right plan.
>
> > On Wed, Feb 2, 2022 at 11:47 AM Michael Lewis  wrote:
> >>
> >> What does the row estimate look like on the scan of data table with that 
> >> statistic in place? Anytime the stats give a mis-estimate this far off, I 
> >> wouldn't expect that plans would be optimal except by luck.
> >>
> >> Index Scan using data_pkey on data t (cost=0.57..21427806.53 rows=58785023 
> >> width=131) (actual time=0.024..0.482 rows=854 loops=1)
> >> Index Cond: (id > 205284974)
> >
>
> It'd be interesting to see the plans without the LIMIT, as that makes
> the "actual" values low simply by terminating early.
>
>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Re: Using the indexing and sampling APIs to realize progressive features

[Vijaykumar Jain]

On Thu, Feb 3, 2022, 8:55 PM  wrote:

> Hi,
>
>
>
> I have some questions regarding the indexing and sampling API.
>
> My aim is to implement a variant of progressive indexing as seen in this
> paper (link). To summarize,
>
> I want to implement a variant of online aggregation, where an aggregate
> query (Like Sum, Average, etc.) is answered in real time, where the result
> becomes more and more accurate as Tuples are consumed.
>
> I thought that I could maybe use a custom sampling routine to consume
> table samples until I have seen the whole table with no duplicate tuples.
>
>

I am not sure if I understand correctly, but if this is referring to
faceted search, then then the below may be of some help.

 https://github.com/citusdata/postgresql-hll
https://github.com/hyperstudio/repertoire-faceting

Performance may vary, but it would help you get an idea of the
implementation.
And you also have rollups and cubes, but they get slow over large tables
and require more resources to speed up.

https://www.cybertec-postgresql.com/en/postgresql-grouping-sets-rollup-cube/

If this is not what you wanted, feel free to ignore.

>

Re: sort order for UTF-8 char column with Japanese UTF-8

[Matthias Apitz]

El día jueves, febrero 03, 2022 a las 10:00:37 -0500, Tom Lane escribió:

> Matthias Apitz  writes:
> > El día jueves, febrero 03, 2022 a las 11:14:55 +0100, Matthias Apitz 
> > escribió:
> >> With ESQL/C on a PostgreSQL 13.1 server I see the result of this query:
> >> select katkey,normform from swd_anzeige where normform >= 'A' ORDER BY ASC;
> >> coming out in this order:
> >> ...
> >> I loaded the same table in my server, but can't get the same order with
> >> psql:
> 
> Do the two machines produce the same results if you sort the data in
> question with sort(1)?  (Being careful to set LANG=de_DE.UTF-8 of
> course.)  I rather doubt this has anything to do with Postgres as such;
> there are lots of inter-system and inter-release discrepancies in
> collation behavior.

I've created a small text file 'swd' with some of the UTF-8 lines in
question. On my Linux mobile phone (a Debian Linux) it gives:

purism@pureos:~$ LANG=de_DE.UTF-8 sort swd
A
A010STRUKTUR
A010STRUKTUR
A010STRUKTUR
A0150SUPRALEITER
ゲアハルト・A・リッター
ゲルハルト・A・リッター
チャールズ・A・ビアード

On my FreeBSD laptop the same file sorts as

guru@c720-r368166:~ $ LANG=de_DE.UTF-8 sort swd
A
ゲアハルト・A・リッター
ゲルハルト・A・リッター
チャールズ・A・ビアード
A010STRUKTUR
A010STRUKTUR
A010STRUKTUR
A0150SUPRALEITER

I'll check tomorrow with the admins of the remote server (a SuSE Linux).

Thanks for the hint.

matthias

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


signature.asc
Description: PGP signature

Re: Subscription stuck at initialize state

[Vijaykumar Jain]

trimming the email, to avoid noise.

I spent a lot of time trying multiple options/combinations and finally
managed to replicate your debug output.
this is when i have ,

postgres=# show max_logical_replication_workers;
* max_logical_replication_workers*
-
* 2*
(1 row)


on publisher
postgres@controller:~$ psql -p 5001
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# create table t_bytea(id int primary key, somebytea bytea);
CREATE TABLE
postgres=# create table t1(id int);
CREATE TABLE
postgres=# create table t2(id int);
CREATE TABLE
postgres=# create table t3(id int);
CREATE TABLE
postgres=# create publication mypub1 for table t1;
CREATE PUBLICATION
postgres=# create publication mypub2 for table t2;
CREATE PUBLICATION
postgres=# create publication mypub3 for table t3;
CREATE PUBLICATION
postgres=# create publication mypub4 for table t3;
CREATE PUBLICATION
postgres=# create publication mypub5 for table t_bytea;
CREATE PUBLICATION
postgres=# insert into t_bytea  select x,repeat(repeat('xxx', 100),
1000)::bytea from generate_series(1, 1000) x;
INSERT 0 1000



on subscriber
postgres@controller:~$ psql -p 5002
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# create table t_bytea(id int primary key, somebytea bytea);
CREATE TABLE
postgres=#  create table t1(id int);
CREATE TABLE
postgres=#  create table t2(id int);
CREATE TABLE
postgres=#  create table t3(id int);
CREATE TABLE
postgres=#  create table t4(id int);
CREATE TABLE
postgres=#  create table t5(id int);
CREATE TABLE
postgres=# create subscription mysub1 connection 'port=5001' publication
mypub1;
NOTICE:  created replication slot "mysub1" on publisher
CREATE SUBSCRIPTION
postgres=# create subscription mysub2 connection 'port=5001' publication
mypub2;
NOTICE:  created replication slot "mysub2" on publisher
CREATE SUBSCRIPTION
postgres=# create subscription mysub3 connection 'port=5001' publication
mypub3;
NOTICE:  created replication slot "mysub3" on publisher
CREATE SUBSCRIPTION
postgres=# create subscription mysub4 connection 'port=5001' publication
mypub4;
NOTICE:  created replication slot "mysub4" on publisher
CREATE SUBSCRIPTION
postgres=# create subscription mysub5 connection 'port=5001' publication
mypub5;
NOTICE:  created replication slot "mysub5" on publisher
CREATE SUBSCRIPTION
*postgres=# select count(1) from t_bytea;*
* count*
*---*
* 0*
*(1 row)*

postgres=# table pg_subscription_rel;
 srsubid | srrelid | srsubstate | srsublsn
-+-++---
   16406 |   16391 | r  | 0/1722838
 *  16407 |   16394 | i  |*
*   16408 |   16397 | i  |*
*   16409 |   16397 | i  |*
*   16410 |   16384 | i  |*
(5 rows)

# as expected no data in t_bytea (as it could not get any worker)
postgres=# select count(1) from t_bytea;
 count
---
 0
(1 row)



but logs clearly state the problem
2022-02-03 23:18:31.107 IST [3430] LOG:  logical replication table
synchronization worker for subscription "mysub1", table "t1" has started
2022-02-03 23:18:31.138 IST [3430] LOG:  logical replication table
synchronization worker for subscription "mysub1", table "t1" has finished
2022-02-03 23:18:40.730 IST [3433] LOG:  logical replication apply worker
for subscription "mysub2" has started
*2022-02-03 23:18:40.737 IST [3433] WARNING:  out of logical replication
worker slots*
*2022-02-03 23:18:40.737 IST [3433] HINT:  You might need to increase
max_logical_replication_workers.*
*2022-02-03 23:18:45.865 IST [3433] WARNING:  out of logical replication
worker slots*


*#publisher logs*
2022-02-03 23:18:31.096 IST [3427] STATEMENT:  CREATE_REPLICATION_SLOT
"mysub1" LOGICAL pgoutput NOEXPORT_SNAPSHOT
2022-02-03 23:18:31.106 IST [3429] LOG:  starting logical decoding for slot
"mysub1"
2022-02-03 23:18:31.106 IST [3429] DETAIL:  Streaming transactions
committing after 0/1722800, reading WAL from 0/17227C8.
2022-02-03 23:18:31.106 IST [3429] STATEMENT:  START_REPLICATION SLOT
"mysub1" LOGICAL 0/0 (proto_version '2', publication_names '"mypub1"')
2022-02-03 23:18:31.106 IST [3429] LOG:  logical decoding found consistent
point at 0/17227C8
2022-02-03 23:18:31.106 IST [3429] DETAIL:  There are no running
transactions.
2022-02-03 23:18:31.106 IST [3429] STATEMENT:  START_REPLICATION SLOT
"mysub1" LOGICAL 0/0 (proto_version '2', publication_names '"mypub1"')
2022-02-03 23:18:31.129 IST [3431] LOG:  logical decoding found consistent
point at 0/1722800
2022-02-03 23:18:31.129 IST [3431] DETAIL:  There are no running
transactions.
2022-02-03 23:18:31.129 IST [3431] STATEMENT:  CREATE_REPLICATION_SLOT
"pg_16406_sync_16391_7060540926182153512" LOGICAL pgoutput USE_SNAPSHOT
2022-02-03 23:18:31.135 IST [3431] LOG:  starting logical decoding for slot
"pg_16406_sync_16391_7060540926182153512"
2022-02-03 23:18:31.135 IST [3431] DETAIL:  Streaming transactions
committing after 0/1722838, reading WAL from 0/1722800.
2022-02-03 23:18:31.135 IST 

Re: Undetected Deadlock

[Tom Lane]

Simon Riggs  writes:
> On Thu, 3 Feb 2022 at 06:25, Michael Harris  wrote:
>> Some of these functions trigger fetching of remote resources, for
>> which a timeout is set using `alarm`. The function unfortunately does
>> not re-establish any pre-existing interval timers after it is done,
>> which leads to postgresql missing it's own expected alarm signal.
>> 
>> The reason that this was not affecting us on previous postgres
>> versions was this commit:
>> 
>> https://github.com/postgres/postgres/commit/09cf1d52267644cdbdb734294012cf1228745aaa#diff-b12a7ca3bf9c6a56745844c2670b0b28d2a4237741c395dda318c6cc3664ad4a
>> 
>> After this commit, once an alarm is missed, that backend never sets
>> one again, so no timeouts of any kind will work. Therefore, the
>> deadlock detector was never being run. Prior to that, the next time
>> any timeout was set by the backend it would re-establish it's timer.
>> 
>> We will of course fix our own code to prevent this issue, but I am a
>> little concerned at the above commit as it reduces the robustness of
>> postgres in this situation. Perhaps I will raise it on the
>> pgsql-hackers list.

> Hmm, so you turned off Postgres' alarms so they stopped working, and
> you're saying that is a robustness issue of Postgres?

If Michael's analysis were accurate, I'd agree that there is a robustness
issue, but I don't think there is.  See timeout.c:220:

/*
 * Get the time remaining till the nearest pending timeout.  If it is
 * negative, assume that we somehow missed an interrupt, and force
 * signal_pending off.  This gives us a chance to recover if the
 * kernel drops a timeout request for some reason.
 */
nearest_timeout = active_timeouts[0]->fin_time;
if (now > nearest_timeout)
{
signal_pending = false;
/* force an interrupt as soon as possible */
secs = 0;
usecs = 1;
}

Now admittedly we don't have a good way to test this stanza, but
it should result in re-establishing the timer interrupt the next
time any timeout.c API is invoked after a missed interrupt.
I don't see anything more that we could or should do.  We're
not going to issue setitimer() after every user-defined function
call.

regards, tom lane

Re: Can Postgres beat Oracle for regexp_count?

[Shaozhong SHI]

Hi, David,

Many thanks.

I am investigating into transformation of data quality validation through
automation with application of Postgres/PostGIS.

Regards,

David

On Thu, 3 Feb 2022 at 13:00, David G. Johnston 
wrote:

>
>
> On Thursday, February 3, 2022, Shaozhong SHI 
> wrote:
>
>>
>> Is it correct to say that this ?: construction of a regex can be applied
>> for checking whether cell values meet specifications?
>>
>>>
>>>
> It does exactly what our examples shows it does.  I don’t understand what
> you mean above but if that helps you remember its purpose, great.
>
> David J.
>
>

Using the indexing and sampling APIs to realize progressive features

[hohenstein]

Hi, 

 

I have some questions regarding the indexing and sampling API.

My aim is to implement a variant of progressive indexing as seen in this
paper (link
 ). To summarize, 

I want to implement a variant of online aggregation, where an aggregate
query (Like Sum, Average, etc.) is answered in real time, where the result
becomes more and more accurate as Tuples are consumed. 

I thought that I could maybe use a custom sampling routine to consume table
samples until I have seen the whole table with no duplicate tuples. 

Meanwhile, with every consumed sample and returned partial answer, I want to
add the tuples consumed to a progressively evolving index.

This would mean that I would have to be able to uniquely identify each row
to be able to add them to the growing index, right? Since OID is deprecated
/ phased out, I am still unsure of how to solve this. 

Does this sound reasonable or is there an obvious flaw in my thinking?

I would also be thankful if there is any material beyond the Postgres
documentation which helps me to start out modifying the source to realize
something like this.

 

Regards

Michael H.

 

Re: Oracle to postgresql migration

[Laurenz Albe]

On Thu, 2022-02-03 at 20:24 +0530, Rama Krishnan wrote:
> How do we take care of database character set when trying
> to migrate from oracle to postgres

If NLS_SORT and NLS_COMP are set to German in Oracle,
just create the PostgreSQL to a German collation to get
a similar behavior.

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

Re: sort order for UTF-8 char column with Japanese UTF-8

[Tom Lane]

Matthias Apitz  writes:
> El día jueves, febrero 03, 2022 a las 11:14:55 +0100, Matthias Apitz escribió:
>> With ESQL/C on a PostgreSQL 13.1 server I see the result of this query:
>> select katkey,normform from swd_anzeige where normform >= 'A' ORDER BY ASC;
>> coming out in this order:
>> ...
>> I loaded the same table in my server, but can't get the same order with
>> psql:

Do the two machines produce the same results if you sort the data in
question with sort(1)?  (Being careful to set LANG=de_DE.UTF-8 of
course.)  I rather doubt this has anything to do with Postgres as such;
there are lots of inter-system and inter-release discrepancies in
collation behavior.

regards, tom lane

Re: max_connections different between primary and standby: is it possible?

[Tom Lane]

Julien Rouhaud  writes:
> On Thu, Feb 03, 2022 at 05:39:57PM +0530, Bharath Rupireddy wrote:
>> ... Instead, it would be better
>> if the server emits a single log with all the insufficient
>> parameters(max_connections, max_worker_processes, max_wal_senders,
>> max_prepared_transactions and max_locks_per_transaction) values and
>> crashes FATALly. The users can look at the logs at once, set all the

> Sure, but one failed start / inspect logs / modify configuration / start will
> always by longer than just reading the docs and making sure that the
> configuration is appropriate.  It also won't help if you want to modify the
> settings on your primary and make sure that you won't have an incident on your
> HA setup.

I don't recall any field complaints, ever, about this behavior.
So I'm skeptical that it's a place to expend effort.

regards, tom lane

Re: max_connections different between primary and standby: is it possible?

[Julien Rouhaud]

On Thu, Feb 03, 2022 at 05:39:57PM +0530, Bharath Rupireddy wrote:
> 
> Agree that the standby should atleast have the capacity that the
> primary has in terms of resources. But what I don't like about that
> code is calling RecoveryRequiresIntParameter for each parameter
> separately and crashing the server FATALly for each insufficient
> parameter. Imagine if all the parameters were set to insufficient
> values on the standby and users keep setting the reported parameter to
> the right value and restart the server. At max, 5 FATAL failure-set
> right value-restart have to be performed. Instead, it would be better
> if the server emits a single log with all the insufficient
> parameters(max_connections, max_worker_processes, max_wal_senders,
> max_prepared_transactions and max_locks_per_transaction) values and
> crashes FATALly. The users can look at the logs at once, set all the

Sure, but one failed start / inspect logs / modify configuration / start will
always by longer than just reading the docs and making sure that the
configuration is appropriate.  It also won't help if you want to modify the
settings on your primary and make sure that you won't have an incident on your
HA setup.

Re: Can Postgres beat Oracle for regexp_count?

[David G. Johnston]

On Thursday, February 3, 2022, Shaozhong SHI  wrote:

>
> Is it correct to say that this ?: construction of a regex can be applied
> for checking whether cell values meet specifications?
>
>>
>>
It does exactly what our examples shows it does.  I don’t understand what
you mean above but if that helps you remember its purpose, great.

David J.

Re: max_connections different between primary and standby: is it possible?

[Bharath Rupireddy]

On Thu, Feb 3, 2022 at 3:17 PM Julien Rouhaud  wrote:
>
> Hi,
>
> On Thu, Feb 03, 2022 at 10:36:37AM +0100, Luca Ferrari wrote:
> > Hi all,
> > running PostgreSQL 14, physical replication with slot, after changing
> > (increasing) the max_connections on the primary, I had this message at
> > a restart from the standby:
> >
> > DETAIL:  max_connections = 100 is a lower setting than on the primary
> > server, where its value was 300.
> >
> > and the standby does not start until I raise also its max_connections.
> > Why is PostgreSQL requiring the max_connections to be aligned between
> > the primary and the standby?
>
> The value needs to be at least equal as the value on the primary node, but it
> can be bigger.
>
> That's because the standby needs to have enough resources to replay the
> activity from the primary, including some heavyweight locks acquisition, and
> the number of locks you can hold at one time is partially based on
> max_connections.

Agree that the standby should atleast have the capacity that the
primary has in terms of resources. But what I don't like about that
code is calling RecoveryRequiresIntParameter for each parameter
separately and crashing the server FATALly for each insufficient
parameter. Imagine if all the parameters were set to insufficient
values on the standby and users keep setting the reported parameter to
the right value and restart the server. At max, 5 FATAL failure-set
right value-restart have to be performed. Instead, it would be better
if the server emits a single log with all the insufficient
parameters(max_connections, max_worker_processes, max_wal_senders,
max_prepared_transactions and max_locks_per_transaction) values and
crashes FATALly. The users can look at the logs at once, set all the
insufficient parameters to right values and restart the server.

Regards,
Bharath Rupireddy.

Re: sort order for UTF-8 char column with Japanese UTF-8

[Matthias Apitz]

El día jueves, febrero 03, 2022 a las 11:14:55 +0100, Matthias Apitz escribió:

> 
> Hello,
> 
> With ESQL/C on a PostgreSQL 13.1 server I see the result of this query:
> 
> select katkey,normform from swd_anzeige where normform >= 'A' ORDER BY ASC;
> 
> coming out in this order:
> 
> query: fetch swd_anzeige_seq
> RESULT: A
> query: fetch swd_anzeige_seq
> RESULT: ゲアハルト・A・リッター
> query: fetch swd_anzeige_seq
> RESULT: ゲルハルト・A・リッター
> query: fetch swd_anzeige_seq
> RESULT: チャールズ・A・ビアード
> query: fetch swd_anzeige_seq
> RESULT: A010STRUKTUR
> query: fetch swd_anzeige_seq
> RESULT: A010STRUKTUR
> query: fetch swd_anzeige_seq
> RESULT: A010STRUKTUR
> query: fetch swd_anzeige_seq
> RESULT: A0150SUPRALEITER
> 
> I loaded the same table in my server, but can't get the same order with
> psql:
> 
> 
>  katkey |normform
> +-
>  233871 | A
>  ...
>   92938 | ゲアハルト・A・リッター
>   92938 | ゲルハルト・A・リッター
>   92938 | リッター0ゲルハルト・A
> 
> i.e. the Japanese rows are sorted at the end after all others. Why?
> 

I forgot to say, both database have:

psql -l
...
 ueisis| sisis  | UTF8 | de_DE.UTF-8  | de_DE.UTF-8  |

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

sort order for UTF-8 char column with Japanese UTF-8

[Matthias Apitz]

Hello,

With ESQL/C on a PostgreSQL 13.1 server I see the result of this query:

select katkey,normform from swd_anzeige where normform >= 'A' ORDER BY ASC;

coming out in this order:

query: fetch swd_anzeige_seq
RESULT: A
query: fetch swd_anzeige_seq
RESULT: ゲアハルト・A・リッター
query: fetch swd_anzeige_seq
RESULT: ゲルハルト・A・リッター
query: fetch swd_anzeige_seq
RESULT: チャールズ・A・ビアード
query: fetch swd_anzeige_seq
RESULT: A010STRUKTUR
query: fetch swd_anzeige_seq
RESULT: A010STRUKTUR
query: fetch swd_anzeige_seq
RESULT: A010STRUKTUR
query: fetch swd_anzeige_seq
RESULT: A0150SUPRALEITER

I loaded the same table in my server, but can't get the same order with
psql:


 katkey |normform
+-
 233871 | A
 ...
  92938 | ゲアハルト・A・リッター
  92938 | ゲルハルト・A・リッター
  92938 | リッター0ゲルハルト・A

i.e. the Japanese rows are sorted at the end after all others. Why?

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Better a wall than a war. And, while the GDR was still 
existing,
no German troups and bombs have been killed in Yugoslavia, Afghanistan, 
Afrika...

Re: max_connections different between primary and standby: is it possible?

[Julien Rouhaud]

Hi,

On Thu, Feb 03, 2022 at 10:36:37AM +0100, Luca Ferrari wrote:
> Hi all,
> running PostgreSQL 14, physical replication with slot, after changing
> (increasing) the max_connections on the primary, I had this message at
> a restart from the standby:
> 
> DETAIL:  max_connections = 100 is a lower setting than on the primary
> server, where its value was 300.
> 
> and the standby does not start until I raise also its max_connections.
> Why is PostgreSQL requiring the max_connections to be aligned between
> the primary and the standby?

The value needs to be at least equal as the value on the primary node, but it
can be bigger.

That's because the standby needs to have enough resources to replay the
activity from the primary, including some heavyweight locks acquisition, and
the number of locks you can hold at one time is partially based on
max_connections.

Re: Subscription stuck at initialize state

[Abhishek Bhola]

* *Basic Connectivity from target(subscriber) to source (Publisher)-*
postgres@targethost:~$ psql -h sourcedb-vip --port=5432 --user=dba -d
sourcedb
Password for user dba:
psql (13.3)
Type "help" for help.

sourcedb=#

* *Create Publication*
 sourcedb=# CREATE PUBLICATION omx_archive FOR TABLE
 sample_table1, sample_table2, sample_table3
;

* *Create subscription* - *Please see the table sample_tables exists on
both and have the same structure*
targetdb=# CREATE SUBSCRIPTION sub_omx_archive_tci
CONNECTION 'host=sourcedb-vip port=5432 user=dba dbname=sourcedb
password=x'
PUBLICATION omx_archive  with (enabled =true, create_slot = true, copy_data
= true);

* *Target DB replication origin status having 0/0 remote_lsb*
targetdb=# select * from pg_replication_origin_status;
 local_id |  external_id  |   remote_lsn   |   local_lsn
--+---++
1 | *pg_3615756798 *| 9E98/9F62A7A8  | 15197/E0CA940
2 | *pg_3616584803 *| 0/0| 0/0
(2 rows)

*postgres@targethost:~$ ps -ef | grep subscription*
postgres  30997 238163  0 18:07 pts/000:00:00 grep --color=auto
subscription
postgres 168277 124365  1 13:46 ?00:04:23 postgres: logical
replication worker for subscription *3616584803*
postgres 178901 124365  0 05:08 ?00:00:08 postgres: logical
replication worker for subscription *3615756798*

* *Strace for the subscription 3616584803 (PID 168277) - the one with the
problem. The other one is okay and hence not shown*
postgres@targethost:~$ *strace -p 168277 *
strace: Process 168277 attached
epoll_wait(21, [], 1, 831)  = 0
close(21)   = 0
sendto(8,
"d\0\0\0\0\0\236\230\343\275V\370\0\0\236\230\343\275V\370\0\0\236\230\343\275V\370\0\2"...,
39, MSG_NOSIGNAL, NULL, 0) = 39
recvfrom(8, 0x21f60f0, 16384, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
epoll_create1(EPOLL_CLOEXEC)= 21
epoll_ctl(21, EPOLL_CTL_ADD, 12, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804168,
u64=34804168}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 3, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804192,
u64=34804192}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 8, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804216,
u64=34804216}}) = 0
epoll_wait(21, [], 1, 1000) = 0
close(21)   = 0
recvfrom(8, 0x21f60f0, 16384, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
epoll_create1(EPOLL_CLOEXEC)= 21
epoll_ctl(21, EPOLL_CTL_ADD, 12, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804168,
u64=34804168}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 3, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804192,
u64=34804192}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 8, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804216,
u64=34804216}}) = 0
epoll_wait(21, [], 1, 1000) = 0
close(21)   = 0
sendto(8,
"d\0\0\0\0\0\236\230\343\275V\370\0\0\236\230\343\275V\370\0\0\236\230\343\275V\370\0\2"...,
39, MSG_NOSIGNAL, NULL, 0) = 39
recvfrom(8, 0x21f60f0, 16384, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
epoll_create1(EPOLL_CLOEXEC)= 21
epoll_ctl(21, EPOLL_CTL_ADD, 12, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804168,
u64=34804168}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 3, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804192,
u64=34804192}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 8, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804216,
u64=34804216}}) = 0
epoll_wait(21, [], 1, 1000) = 0
close(21)   = 0
recvfrom(8, 0x21f60f0, 16384, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
epoll_create1(EPOLL_CLOEXEC)= 21
epoll_ctl(21, EPOLL_CTL_ADD, 12, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804168,
u64=34804168}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 3, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804192,
u64=34804192}}) = 0
epoll_ctl(21, EPOLL_CTL_ADD, 8, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=34804216,
u64=34804216}}) = 0
epoll_wait(21, [], 1, 1000) = 0
close(21)   = 0
sendto(8,
"d\0\0\0\0\0\236\230\343\275V\370\0\0\236\230\343\275V\370\0\0\236\230\343\275V\370\0\2"...,
39, MSG_NOSIGNAL, NULL, 0) = 39
recvfrom(8, 0x21f60f0, 16384, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 167, MSG_NOSIGNAL, NULL,
0) = 167
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 184, MSG_NOSIGNAL, NULL,
0) = 184
write(2, "\0\0\371\0U\221\2\0T2022-02-03 18:08:21.440"..., 258) = 258
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 167, MSG_NOSIGNAL, NULL,
0) = 167
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 184, MSG_NOSIGNAL, NULL,
0) = 184
write(2, "\0\0\371\0U\221\2\0T2022-02-03 18:08:21.440"..., 258) = 258
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 167, MSG_NOSIGNAL, NULL,
0) = 167
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 184, MSG_NOSIGNAL, NULL,
0) = 184
write(2, "\0\0\371\0U\221\2\0T2022-02-03 18:08:21.440"..., 258) = 258
sendto(9, "<133>Feb  3 18:08:21 postgres[16"..., 167, MSG_NOSIGNAL, 

Re: max_connections different between primary and standby: is it possible?

[Vijaykumar Jain]

On Thu, Feb 3, 2022, 3:07 PM Luca Ferrari  wrote:

> Hi all,
> running PostgreSQL 14, physical replication with slot, after changing
> (increasing) the max_connections on the primary, I had this message at
> a restart from the standby:
>
> DETAIL:  max_connections = 100 is a lower setting than on the primary
> server, where its value was 300.
>
> and the standby does not start until I raise also its max_connections.
> Why is PostgreSQL requiring the max_connections to be aligned between
> the primary and the standby?
>
> Thanks,
> Luca
>
>
>
> No. iirc, It has to be the same on all nodes in the physical replication
> setup.



But if vis pgbouncer, you can maintain the same max_connection but alter
> active front-end backend connections.
>


>

max_connections different between primary and standby: is it possible?

[Luca Ferrari]

Hi all,
running PostgreSQL 14, physical replication with slot, after changing
(increasing) the max_connections on the primary, I had this message at
a restart from the standby:

DETAIL:  max_connections = 100 is a lower setting than on the primary
server, where its value was 300.

and the standby does not start until I raise also its max_connections.
Why is PostgreSQL requiring the max_connections to be aligned between
the primary and the standby?

Thanks,
Luca

Re: Can Postgres beat Oracle for regexp_count?

[Shaozhong SHI]

Many thanks, Tom,

select regexp_matches('My High Street', '(?:[A-Z][a-z]+[\s]*)+', 'g');
looks very interesting.

I did read the documentation, but found it is difficult to read.
Particularly, the documentation on the use ?: does not state clear sense.
There is only limited explanation on ?:.

Is it correct to say that this ?: construction of a regex can be applied
for checking whether cell values meet specifications?

Regards,

David

On Thu, 3 Feb 2022 at 05:59, Tom Lane  wrote:

> Shaozhong SHI  writes:
> > The following has been attempted but no luck.
>
> > select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)+', 'g')
> > It is intended to match 'My High Street, but it turned out only 'Street'
> > was matched.
>
> You've got the parentheses in the wrong place, ie inside not outside the
> "+" quantifier.  Per the fine manual [1], the result is determined by the
> last match of quantified capturing parens.
>
> You could avoid using any capturing parens, so that the result is
> the whole match:
>
> regression=# select regexp_matches('My High Street',
> '(?:[A-Z][a-z]+[\s]*)+', 'g');
>regexp_matches
> 
>  {"My High Street"}
> (1 row)
>
> or you could do
>
> regression=# select regexp_matches('My High Street',
> '(([A-Z][a-z]+[\s]*)+)', 'g');
>   regexp_matches
> ---
>  {"My High Street",Street}
> (1 row)
>
> but then you have two sets of capturing parens and you get results for
> both, so you might prefer
>
> regression=# select regexp_matches('My High Street',
> '((?:[A-Z][a-z]+[\s]*)+)', 'g');
>regexp_matches
> 
>  {"My High Street"}
> (1 row)
>
> In any case, there's no substitute for reading the manual.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
>

Re: Undetected Deadlock

[Simon Riggs]

On Thu, 3 Feb 2022 at 06:25, Michael Harris  wrote:
>
> Hi again
>
> Some good news. After some more debugging & reflection, I realized
> that the likely cause is one of our own libraries that gets loaded as
> part of some custom functions we are using.
>
> Some of these functions trigger fetching of remote resources, for
> which a timeout is set using `alarm`. The function unfortunately does
> not re-establish any pre-existing interval timers after it is done,
> which leads to postgresql missing it's own expected alarm signal.
>
> The reason that this was not affecting us on previous postgres
> versions was this commit:
>
> 
> https://github.com/postgres/postgres/commit/09cf1d52267644cdbdb734294012cf1228745aaa#diff-b12a7ca3bf9c6a56745844c2670b0b28d2a4237741c395dda318c6cc3664ad4a
>
> After this commit, once an alarm is missed, that backend never sets
> one again, so no timeouts of any kind will work. Therefore, the
> deadlock detector was never being run. Prior to that, the next time
> any timeout was set by the backend it would re-establish it's timer.
>
> We will of course fix our own code to prevent this issue, but I am a
> little concerned at the above commit as it reduces the robustness of
> postgres in this situation. Perhaps I will raise it on the
> pgsql-hackers list.

Hmm, so you turned off Postgres' alarms so they stopped working, and
you're saying that is a robustness issue of Postgres?

Yes, something broke and it would be nice to avoid that, but the
responsibility for that lies in the user code that was called.
Postgres can't know what kernel calls are made during a custom
function.

Perhaps you could contribute a test case for this situation and a new
call to check/reset any missing alarms? Or alternatively, contribute
the function library that fetches remote resources, so that can become
an optional part of Postgres, in contrib.

-- 
Simon Riggshttp://www.EnterpriseDB.com/

Re: Subscription stuck at initialize state

[Vijaykumar Jain]

On Thu, 3 Feb 2022 at 12:44, Abhishek Bhola 
wrote:

> Hi Vijaykumar,
>
> I checked the pg_subscription_rel and all the tables in that subscription
> are in the state - i (initialize).
> I also tried creating a new publication on the source DB with just one
> table and tried to subscribe it, it doesn't work either.
> However, when I try to subscribe it on some other DB than the one
> mentioned above, it works.
> By which I am deducing that publication and the source DB are okay, the
> problem is on the target DB and it's subscription.
> Maybe I will have to restart the DB as a last resort, but I am not sure if
> that will solve the problem either.
>
>
its a very verbose mail, so if it noisy, kindly ignore.

else,

can you check basic connectivity from the subscriber to publisher using
psql  and run a simple query ?
can you share your "create publication" and "create subscription"
commands/statements too please?

i am attaching a general logical replication setup on a single server and
put some scenarios where replication breaks and how to monitor and how to
resume.
and how that is monitored.


postgres@controller:~$ tail db1/postgresql.conf db2/postgresql.conf
==> db1/postgresql.conf <==

# Add settings for extensions here
wal_level=logical
archive_mode = on
archive_command = '/bin/true'
max_wal_size = 48MB
min_wal_size = 32MB
shared_buffers = 32MB
port = 5001
max_logical_replication_workers = 10

==> db2/postgresql.conf <==
# Add settings for extensions here
wal_level=logical
archive_mode = on
archive_command = '/bin/true'
max_wal_size = 48MB
min_wal_size = 32MB
shared_buffers = 32MB
port = 5002
max_logical_replication_workers = 10

postgres@controller:~$ pg_ctl -D db1 -l db1.log start
waiting for server to start done
server started
postgres@controller:~$ pg_ctl -D db2 -l db2.log start
waiting for server to start done
server started
postgres@controller:~$ psql -p 5001
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# create table t1(id int primary key);
CREATE TABLE
postgres=# create table t2(id int); -- this will throw error on delete, no
replica identity
CREATE TABLE
postgres=# insert into t1 select x from generate_series(1, 100) x;
INSERT 0 100
postgres=# insert into t2 select x from generate_series(1, 100) x;
INSERT 0 100
postgres=# checkpoint;
CHECKPOINT
postgres=# \q
postgres@controller:~$ psql -p 5002
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=#  create table t1(id int primary key);
CREATE TABLE
postgres=# create table t2(id int);
CREATE TABLE
postgres=# \q
postgres@controller:~$ ps aux | grep -i postgres:
postgres1116  0.0  0.4 113632  8232 ?Ss   13:24   0:00
postgres: checkpointer
postgres1117  0.0  0.2 113496  5868 ?Ss   13:24   0:00
postgres: background writer
postgres1118  0.0  0.3 113496  6964 ?Ss   13:24   0:00
postgres: walwriter
postgres1119  0.0  0.4 114032  8432 ?Ss   13:24   0:00
postgres: autovacuum launcher
postgres1120  0.0  0.2 113496  4132 ?Ss   13:24   0:00
postgres: archiver
postgres1121  0.0  0.2  72112  4896 ?Ss   13:24   0:00
postgres: stats collector
postgres1122  0.0  0.3 113928  6732 ?Ss   13:24   0:00
postgres: logical replication launcher
postgres1128  0.0  0.3 113496  5956 ?Ss   13:24   0:00
postgres: checkpointer
postgres1129  0.0  0.3 113496  5916 ?Ss   13:24   0:00
postgres: background writer
postgres1130  0.0  0.3 113496  6952 ?Ss   13:24   0:00
postgres: walwriter
postgres1131  0.0  0.4 114032  8384 ?Ss   13:24   0:00
postgres: autovacuum launcher
postgres1132  0.0  0.2 113496  4160 ?Ss   13:24   0:00
postgres: archiver
postgres1133  0.0  0.2  72112  4868 ?Ss   13:24   0:00
postgres: stats collector
postgres1134  0.0  0.3 113928  6804 ?Ss   13:24   0:00
postgres: logical replication launcher
postgres1186  0.0  0.0   8164   724 pts/0S+   13:26   0:00 grep -i
postgres:
postgres@controller:~$ psql -p 5001
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# create publication mypub for all tables

postgres=# create publication mypub for all tables with
(publish='insert,update,delete,truncate');
CREATE PUBLICATION
postgres=# \q
postgres@controller:~$ psql -p 5002
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# create subscription mysub connection 'port=5001' publication
mypub;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
postgres=# select count(1) from t1;
 count
---
   100
(1 row)

postgres=# select count(1) from t2;
 count
---
   100
(1 row)

postgres=# \q

postgres@controller:~$ ps aux | grep postgres:  # strace the below pids for
movement
postgres1195  0.0  0.7 114800 14744 ?Ss   13:27   0:00
postgres: logical replication worker for subscription 16392
postgres1196  0.0  0.7 114728 14676 ?