Re: SV: Log files polluted with permission denied error messages after every 10 seconds

[Andrus]

Any hints in Windows event viewer? Events  occurring at the same time showing 
up there.


Looked into Administrative Events/Custom views and few others. There are 
no messages about this. Windowsi perfomance monitor and Filemon show 
files opened by process.


How to do reverse: log processes and threads which use files in pg_wal 
directory ?


Is there some utility for this or can restart manager or other API  used 
to create such log?


Andrus.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Brent Wood]

They are discussed here,
https://kb.objectrocket.com/postgresql/function-vs-stored-procedure-602

(from this page):
The main difference between function and stored procedure is that user-defined 
functions do not execute transactions. This means, inside a given function you 
cannot open a new transaction, neither can you commit or rollback the current 
transaction. It is important to note that stored procedures are just functions 
that can support transactions and were introduced in Postgresql 11. PostgreSQL 
functions can be created in a variety of languages including SQL, PL/pgSQL, C, 
Python.

Perhaps add some detail from here to the official docs?

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529


From: Guyren Howe 
Sent: Tuesday, March 9, 2021 17:31
To: Tom Lane ; raf 
Cc: Adrian Klaver ; Bysani, Ram 
; pgsql-general@lists.postgresql.org 

Subject: Re: Need help with clarification on stored procedure support in 
PostGreSQL database

This seems like an important consideration. I’ve spent 10 minutes searching the 
documentation for PG 11 and can’t find where it is documented. Perhaps it 
should be made more prominent?
On Mar 8, 2021, 16:53 -0800, raf , wrote:
On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane  wrote:

raf  writes:
Is there any important different between a stored procedure and
a stored function that returns void? I've been using functions
for everything but calling them procedures in my head (even though
most of them do return a value or result set).

A procedure can commit or rollback a transaction (and then start
a new one).

regards, tom lane

thanks.

cheers,
raf



[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png][https://niwa.co.nz/sites/niwa.co.nz/files/ETNZ_Official-supplier-logo-75h.png]
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz 
Facebook 
LinkedIn 
Twitter 
Instagram
To ensure compliance with legal requirements and to maintain cyber security 
standards, NIWA's IT systems are subject to ongoing monitoring, activity 
logging and auditing. This monitoring and auditing service may be provided by 
third parties. Such third parties can access information transmitted to, 
processed by and stored on NIWA's IT systems

Import data from one instance another

[Rama Krishnan]

Hi Team,

I have two instances called A and B, in A instances I have one table name
called sports I want to import data from A into B instances I found foreign
data wrapper is the best solution when the table is huge here I have doubt
is it foreign data wrapper and foreign tables are same?


Thanks

Rama Krishnan

Re: Import data from one instance another

[Wim Bertels]

Hello Rama,

not quiet sure what the question is,
you can choose the name of the foreign table
https://www.postgresql.org/docs/current/postgres-fdw.html

Rama Krishnan schreef op di 09-03-2021 om 14:47 [+0530]:
> Hi Team, 
> 
> I have two instances called A and B, in A instances I have one table
> name called sports I want to import data from A into B instances I
> found foreign data wrapper is the best solution when the table is
> huge here I have doubt is it foreign data wrapper and foreign tables
> are same?
> 
> 
> Thanks
> 
> Rama Krishnan
-- 
mvg,
Wim Bertels
--
https://coronalert.be

Lector
UC Leuven-Limburg
--
"... an experienced, industrious, ambitious, and often quite often
picturesque liar."
-- Mark Twain

curious cost vs execution time (local test)

[Wim Bertels]

Hello,

Using the chinook database
https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_PostgreSql.sql
locally,
so there should be little side-effects (network, host, cache..)
for the following two equivalent queries,
the cost and execution times are curious.

(tip if you have problems on linux psql for the chinook database
#set client_encoding to 'windows-1251';
)

Can anyone confirm or correct this?

The equivalent queries are

SELECT r1."Title",hs."Name",hs."Milliseconds"
FROM "Album" r1 LEFT JOIN LATERAL
 (SELECT *
 FROM "Track" b2
natural inner join "Album"
 WHERE r1."AlbumId" = b2."AlbumId"
 ORDER BY "Milliseconds" desc
 FETCH FIRST 2 ROW ONLY) hs
ON true;

vs

SELECT "Title", "Name", "Milliseconds"
FROM "Album" A NATURAL INNER JOIN "Track" T
WHERE 1 >= (SELECT COUNT(*)
   FROM "Album" NATURAL INNER JOIN "Track"
   WHERE "Album"."Title" = A."Title"
   AND "Track"."Milliseconds" < T."Milliseconds");



Running explain (analyze) a few times gives the following curious
result:

With the Lateral join query:
   
  QUERY
PLAN   
  
-
-
--
 Nested Loop Left Join  (cost=16.37..5701.03 rows=694 width=43) (actual
time=0.043..7.347 rows=612 loops=1)
   ->  Seq Scan on "Album" r1  (cost=0.00..6.47 rows=347 width=27)
(actual time=0.006..0.022 rows=347 loops=1)
   ->  Limit  (cost=16.37..16.37 rows=2 width=856) (actual
time=0.021..0.021 rows=2 loops=347)
 ->  Sort  (cost=16.37..16.39 rows=10 width=856) (actual
time=0.021..0.021 rows=2 loops=347)
   Sort Key: b2."Milliseconds" DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop  (cost=0.28..16.27 rows=10 width=856)
(actual time=0.009..0.019 rows=10 loops=347)
 ->  Seq Scan on "Album"  (cost=0.00..7.34 rows=1
width=4) (actual time=0.008..0.016 rows=1 loops=347)
   Filter: ("AlbumId" = r1."AlbumId")
   Rows Removed by Filter: 346
 ->  Index Scan using "IFK_TrackAlbumId" on "Track"
b2  (cost=0.28..8.83 rows=10 width=24) (actual time=0.001..0.002
rows=10 loops=347)
   Index Cond: ("AlbumId" = r1."AlbumId")
 Planning Time: 0.094 ms
 Execution Time: 7.388 ms



With the correlated subquery (where):
QUE
RY
PLAN   
  
-
-
-
 Hash Join  (cost=10.81..148.79 rows=1168 width=43) (actual
time=0.109..80.502 rows=612 loops=1)
   Hash Cond: (t."AlbumId" = a."AlbumId")
   Join Filter: (1 >= (SubPlan 1))
   Rows Removed by Join Filter: 2891
   ->  Seq Scan on "Track" t  (cost=0.00..80.03 rows=3503 width=24)
(actual time=0.003..0.193 rows=3503 loops=1)
   ->  Hash  (cost=6.47..6.47 rows=347 width=27) (actual
time=0.054..0.055 rows=347 loops=1)
 Buckets: 1024  Batches: 1  Memory Usage: 29kB
 ->  Seq Scan on "Album" a  (cost=0.00..6.47 rows=347 width=27)
(actual time=0.003..0.025 rows=347 loops=1)
   SubPlan 1
 ->  Aggregate  (cost=16.23..16.24 rows=1 width=8) (actual
time=0.022..0.022 rows=1 loops=3503)
   ->  Nested Loop  (cost=0.28..16.22 rows=3 width=0) (actual
time=0.011..0.022 rows=7 loops=3503)
 ->  Seq Scan on "Album"  (cost=0.00..7.34 rows=1
width=4) (actual time=0.008..0.018 rows=1 loops=3503)
   Filter: (("Title")::text = (a."Title")::text)
   Rows Removed by Filter: 346
 ->  Index Scan using "IFK_TrackAlbumId" on
"Track"  (cost=0.28..8.85 rows=3 width=4) (actual time=0.001..0.003
rows=7 loops=3503)
   Index Cond: ("AlbumId" = "Album"."AlbumId")
   Filter: ("Milliseconds" < t."Milliseconds")
   Rows Removed by Filter: 8
 Planning Time: 0.206 ms
 Execution Time: 80.561 ms


Summarized:

Lateral:  (cost=16.37..5701.03 rows=694 rows=1168) (actual
time=0.043..7.347 )
C Subq: (cost=10.81..148.79 rows=1168 width=43) (actual
time=0.109..80.502)
with the actual amount of rows for this queries being 612,
so the amount of rows for the C Subq is not well estimated.

Nonetheless in this case, the most expensive query here, is the fastest
one.

(PS: Analyze was run on the tables prior to the queries)


-- 
mvg,
Wim Bertels
--
https://coronalert.be

--
"Not Hercules could have knock'd out his brains, for he had none."
-- Shakespeare

Re: SV: Log files polluted with permission denied error messages after every 10 seconds

[Thomas Munro]

On Tue, Mar 9, 2021 at 9:43 PM Andrus  wrote:
>  > Any hints in Windows event viewer? Events occurring at the same time 
> showing up there.
>
> Looked into Administrative Events/Custom views and few others. There are no 
> messages about this. Windowsi perfomance monitor and Filemon show files 
> opened by process.
>
> How to do reverse: log processes and threads which use files in pg_wal 
> directory ?

Maybe this does the reverse?

https://docs.microsoft.com/en-us/sysinternals/downloads/handle

Re: Log files polluted with permission denied error messages after every 10 seconds

[Thomas Kellerer]

Andrus schrieb am 05.03.2021 um 18:36:
> Hi!
>
>>Windows? Don't let anti-virus software mess with the data directory.
>
> Windows default Windows Defender is active. I excluded data, pg_wal folders 
> and postgres process:
>
>
> Then turned real-time protection off:
>
> Problem persists. New entry is written after every 10 seconds.
>
> pg_wal also contains files with .deleted extension like
>
> 0001000500B2.deleted
>
> Andrus.
>
>
The data directory should not be stored in "C:\Program File"s on Windows.

I wouldn't be surprised if "Program Files" has some additional security 
settings that come into play here.

%ProgramData% is a better location for the data directory.


Thomas

jsonpath with @@ and iterating over arrays

[Thomas Kellerer]

I have a question regarding the processing of arrays when using the "short 
jsonpath"
with the @@ operator.

E.g. when trying to "translate" this jsonpath

   where jsonb_path_exists(the_column, '$[*] ? (@.id == 42 && @.type == "foo")')

to be used with the @@ operator, then the "? (...)" cannot be used.

The following seems to work

   where the_column @@ '$[*].id == 42 && $[*].type == "foo"'


However, I have two questions regarding this:

1. is the order of the array processing guaranteed to be the same?

In other words: are @.id and @.type guaranteed to always refer to the keys of
the same element in the array?

2. is this less efficient that the jsonb_path_exists?

The jsonpath for the @@ operator contains two "iterator expression", and I 
wonder
if Postgres is smart enough to only iterate over the array once.


Thanks
Thomas

Re: Question about when PostgreSQL 11.0 was released

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Ram Bysani asked:
> I am not finding the release cadence for the PostgreSQL databases.
> Please let me know how I can find the base and dot version release dates for:

You can see all the release dates on one page here:

https://bucardo.org/postgres_all_versions

- --
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iF0EARECAB0WIQQlKd9quPeUB+lERbS8m5BnFJZKyAUCYEd1hgAKCRC8m5BnFJZK
yMlLAJwM62zTcEGy7yZRBqi8xsqbmm8t5wCghNDRk4Bqe1seEclN11HP57pVL8Q=
=/dnl
-END PGP SIGNATURE-

Re: PostgreSQL container crash trouble.

[Daisuke Ikeda]

Thank you for your reply!
It was helpful.

I'll check more about the state of writing on the disk storage.

And I will reinforce the backup & restore strategy.



2021年3月9日(火) 0:28 Laurenz Albe :

> On Mon, 2021-03-08 at 22:30 +0900, Daisuke Ikeda wrote:
> > I'm running PostgreSQL (enabled timescaledb extension) in the following
> environment.
> > And I had db pod crashed error several times irregularly.
> >
> > I want to know the cause of this fail.
> >
> > Environment:
> >
> >   - Base: Kubernetes(microk8s)
> >   - DB: PostgreSQL 12.4 (TimescaleDB) Container
> >   - DB OS: Alpine Linux
> >   - microk8s host OS: CentOS 7.6, Amazon Linux 2 (Occured under some
> hosts)
> >   - DB data location: Mounted host directory (for data persistence)
> >
> > 1) PostgreSQL crash and cannot start Pod..
> >
> > I noticed that the timescaledb pod was restarted repeatedly with the
> following error.
> >
> > ---
> > PostgreSQL Database directory appears to contain a database; Skipping
> initialization
> > [1] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-musl, compiled by
> gcc (Alpine 9.3.0) 9.3.0, 64-bit
> > [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
> > [1] LOG:  listening on IPv6 address "::", port 5432
> > [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
> > [20] LOG:  database system was shut down at 2021-02-15 21:15:12 UTC
> > [20] LOG:  invalid primary checkpoint record
> > [20] PANIC:  could not locate a valid checkpoint record
> > [1] LOG:  startup process (PID 20) was terminated by signal 6: Aborted
> > [1] LOG:  aborting startup due to startup process failure
> > [1] LOG:  database system is shut down
> > ---
> >
> > I thought it might be WAL trouble and tried to check with pg_controldata
> and pg_waldump.
>
> It is WAL trouble.
> WAL does not contain the checkpoint from before the crash.
>
> > I thought it might be WAL trouble and tried to check with pg_controldata
> and pg_waldump.
> >
> > ---
> > $ pg_controldata 
> > [...]
> > Latest checkpoint location:   15/8FB002C0
> > Latest checkpoint's REDO location:15/8FAF3018
> > Latest checkpoint's REDO WAL file:00010015008F
> > [...]
> >
> > I checked the latest checkpoint's REDO WAL file.
> >
> > ---
> > $ pg_waldump -n 10 pg_wal/00010015008F
> > pg_waldump: fatal: WAL segment size must be a power of two between 1 MB
> and 1 GB, but the WAL file "00010015008F" header specifies 0
> bytes
> > ---
> >
> > I cannot read wal data.
> > This file size is 16MB (according to the wal size setting).
> > But the content is all zero data. I checked this situation with "od"
> command.
> >
> > ---
> > $ od -N 40 -A d -v pg_wal/00010015008F
> > 000 00 00 00 00 00 00 00 00
> > 016 00 00 00 00 00 00 00 00
> > 032 00 00 00 00
> > 040
> > ---
>
> Looks like modifications to this file were lost.
>
> > 2) Run pg_resetwal and successful start Pod
> >
> > I ran the pg_resetwal command to repair the WAL trouble.
> > And the DB pod successfully started.
>
> Yes, but "pg_resetwal" on a crashed cluster leads to data corruption.
> The best you can do now is salvage what you can.
>
> > But, I received select query fail for some tables.
> >
> > 3) failed select query
> >
> > ---
> > sampledb1=# select * from table1 limit 1;
> > ERROR:  missing chunk number 0 for toast value 27678 in pg_toast_2619
> > ---
> >
> > I thought taht this error is raised for pg_statistic table (and related
> toast table (pg_toast_2619)).
> > So, I deleted the broken rows in pg_statistic table and ran ANALYZE
> query.
> > After then, this tables is recovered.
>
> Lucky you!
>
> > 4) another data trouble
> >
> > I had an another data trouble after pg_resetwal.
> >
> > In some table, SELECT query did not return any rows.
> > And INSERT query failed (no response. Waiting forever...)
> > This table have only primary key index.
> > I thought it might have been caused by an pkey index trouble after
> pg_resetwal.
> > I didn't know how to repair the index of the primary key, and finally I
> dropped table and restore.
>
> You should "pg_dumpall" the cluster and load it into a cluster
> that was newly created with "initdb".
>
> Any problems loading the data have to be resolved manually.
>
> > I thought this wal trouble was caused by disk IO troubles. But any error
> was not raised in OS syslog.
> > I want to know any other causes.
>
> One cause might be unreliable storage that doesn't honor fsync requests
> correctly.  But given that your checkpoint location is pretty late in
> the file, it seems unlikely that none of the data did make it to disk.
>
> Somehow you lost the data for a WAL segment, and you cannot recover.
>
> Of course, if you have a backup, you know what to do.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Question about when PostgreSQL 11.0 was released

[Joe Conway]

On 3/9/21 8:20 AM, Greg Sabino Mullane wrote:
> Ram Bysani asked:
>> I am not finding the release cadence for the PostgreSQL databases.
>> Please let me know how I can find the base and dot version release dates for:
> 
> You can see all the release dates on one page here:
> 
> https://bucardo.org/postgres_all_versions

Nice!

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

[Alexander Farber]

Tom, you are so eagle eyed -

On Mon, Mar 8, 2021 at 8:53 PM Tom Lane  wrote:

> Alexander Farber  writes:
> > However there are cases, when I only have the out_gid value, I do not
> want
> > to return any other values.
> > My question is: do I have to set the other OUT params explicitly to NULL?
>
> plpgsql initializes them to null by default, I believe, just like ordinary
> local variables.
>
> > org.postgresql.util.PSQLException: ERROR: column "out_uid" does not
> exist|
>
> This isn't related to what your function does internally.
>
> I think the issue is you renamed out_uid to uid in your SELECT:
>
> >String SQL_JOIN_GAME   =
> >"SELECT " +
> >"out_uidAS uid," +
>
>
thank you and sorry for my silly mistake

how to limit statement memory allocation

[Radoslav Nedyalkov]

Hi all,

Occasionally  we get bad queries on our db that consume a lot of memory.
These typically are full joins by mistake or just too large result sets.
My understanding is these should go to a temp file but apparently memory
allocation is preferred.
Last time a statement hit 150GB RAM and did not get a cancel or terminate
signal, so we had to
restart the db after a few hours, after another one popped up.

The only relevant , i guess,  settings on the server are:
huge_pages = 'on'
shared_buffers = 196GB
work_mem = 1GB
maintenance_work_mem = 4GB
effective_cache_size = 720GB

Thank you in advance,
Rado

Re: how to limit statement memory allocation

[Tom Lane]

Radoslav Nedyalkov  writes:
> Occasionally  we get bad queries on our db that consume a lot of memory.
> These typically are full joins by mistake or just too large result sets.
> My understanding is these should go to a temp file but apparently memory
> allocation is preferred.

Perhaps the accumulation is happening on the client side?  libpq doesn't
have any provision for spilling a result set to disk.

If that's it, you could consider revising your application to read results
row-at-a-time, although that might require a good deal of effort.

https://www.postgresql.org/docs/current/libpq-single-row-mode.html

regards, tom lane

Re: how to limit statement memory allocation

[Radoslav Nedyalkov]

On Tue, Mar 9, 2021 at 6:03 PM Tom Lane  wrote:

> Radoslav Nedyalkov  writes:
> > Occasionally  we get bad queries on our db that consume a lot of memory.
> > These typically are full joins by mistake or just too large result sets.
> > My understanding is these should go to a temp file but apparently memory
> > allocation is preferred.
>
> Perhaps the accumulation is happening on the client side?  libpq doesn't
> have any provision for spilling a result set to disk.
>
> If that's it, you could consider revising your application to read results
> row-at-a-time, although that might require a good deal of effort.
>
> https://www.postgresql.org/docs/current/libpq-single-row-mode.html
>
> regards, tom lane
>

Ah, I named it result set wrongly perhaps.
These are queries , part of a larger ETL function or statement which at the
end just write to a table.
The huge join is an intermediate step.

So I'm still wondering.

Thanks and regards,
Rado

Re: how to limit statement memory allocation

[Tom Lane]

Radoslav Nedyalkov  writes:
> On Tue, Mar 9, 2021 at 6:03 PM Tom Lane  wrote:
>> Perhaps the accumulation is happening on the client side?  libpq doesn't
>> have any provision for spilling a result set to disk.

> Ah, I named it result set wrongly perhaps.
> These are queries , part of a larger ETL function or statement which at the
> end just write to a table.
> The huge join is an intermediate step.

Hm.  What's the query plan look like?

The only thing I'm aware of that could consume unbounded memory
on the server side is hash aggregation.  (v13 has improved that
situation, but I'm guessing you are running some older version.)
The planner won't choose hash aggregation if it estimates that
the hash table would need to exceed work_mem, but its estimate
could be wrong.  If that's the scenario, you could back off
work_mem until hash aggregation isn't used, or you could try to
improve the planner's estimates.  If your ETL process involves
intermediate tables, you might need to explicitly ANALYZE those
after you fill them so that the planner has a better idea how
to plan the later steps.

regards, tom lane

Re: how to limit statement memory allocation

[Radoslav Nedyalkov]

On Tue, Mar 9, 2021 at 6:53 PM Tom Lane  wrote:

> Radoslav Nedyalkov  writes:
> > On Tue, Mar 9, 2021 at 6:03 PM Tom Lane  wrote:
> >> Perhaps the accumulation is happening on the client side?  libpq doesn't
> >> have any provision for spilling a result set to disk.
>
> > Ah, I named it result set wrongly perhaps.
> > These are queries , part of a larger ETL function or statement which at
> the
> > end just write to a table.
> > The huge join is an intermediate step.
>
> Hm.  What's the query plan look like?
>
> The only thing I'm aware of that could consume unbounded memory
> on the server side is hash aggregation.  (v13 has improved that
> situation, but I'm guessing you are running some older version.)
> The planner won't choose hash aggregation if it estimates that
> the hash table would need to exceed work_mem, but its estimate
> could be wrong.  If that's the scenario, you could back off
> work_mem until hash aggregation isn't used, or you could try to
> improve the planner's estimates.  If your ETL process involves
> intermediate tables, you might need to explicitly ANALYZE those
> after you fill them so that the planner has a better idea how
> to plan the later steps.
>
> regards, tom lanea


Thanks Tom,
Query and plan attached.

Rado

db=# EXPLAIN 
db-# CREATE TEMP TABLE gross_set_merchants AS (
db(# WITH gs_merchants as (
db(# SELECT
db(# u.merchant_id,
db(# uel.user_id
db(# FROM public.user_event_logs as uel
db(# JOIN users as u ON uel.user_id = u.id
db(# where 
db(# --u.merchant_id = 1030616
db(# uel.created_at >= '2018-01-01'
db(# AND uel.category = 'settings'
db(# AND uel.description IN ('Gross settlement 
enabled','Gross settlement disabled')
db(# GROUP BY 1,2
db(# ), setting_change as (
db(# SELECT
db(# gsm.merchant_id,
db(# cer.date,
db(# SUM(CASE 
db(# WHEN uel.description = 'Gross 
settlement enabled' THEN 1
db(# WHEN uel.description = 'Gross 
settlement disabled' THEN -1
db(# ELSE 0
db(# END) change_setting
db(# FROM external.currency_exchange_rates as cer
db(# CROSS JOIN gs_merchants as gsm --full join, having 
all days for all gs merchants
db(# LEFT JOIN public.user_event_logs as uel ON 
uel.created_at::date = cer.date 
db(# AND gsm.user_id = uel.user_id 
db(# AND uel.created_at >= '2018-01-01'
db(# AND uel.category = 'settings'
db(# AND uel.description IN ('Gross settlement 
enabled','Gross settlement disabled')
db(# WHERE cer.currency = 'GBP'
db(# AND cer.date >= '2018-01-01'
db(# GROUP BY 1,2
db(# 
db(# )
db(# 
db(# SELECT
db(# sc.merchant_id,
db(# sc.date,
db(# SUM(change_setting) OVER (PARTITION BY 
sc.merchant_id ORDER BY date) as gs_on
db(# FROM setting_change as sc); 

 QUERY PLAN 

 
-
 WindowAgg  (cost=183885316.97..183890372.97 rows=252800 width=40)
   CTE gs_merchants
 ->  Group  (cost=2475689.43..2534384.63 rows=567218 width=8)
   Group Key: u.merchant_id, uel.user_id
   ->  Gather Merge  (cost=2475689.43..2532021.22 rows=472682 width=8)
 Workers Planned: 2
 ->  Group  (cost=2474689.41..2476461.97 rows=236341 width=8)
   Group Key: u.merchant_id, uel.user_id
   ->  Sort  (cost=2474689.41..2475280.26 rows=236341 
width=8)
 Sort Key: u.merchant_id, uel.user_id
 ->  Parallel Hash Join  
(cost=265272.50..2453595.37 rows=236341 width=8)
 

Re: how to limit statement memory allocation

[Tom Lane]

Radoslav Nedyalkov  writes:
> On Tue, Mar 9, 2021 at 6:53 PM Tom Lane  wrote:
>> The only thing I'm aware of that could consume unbounded memory
>> on the server side is hash aggregation.  (v13 has improved that
>> situation, but I'm guessing you are running some older version.)
>> The planner won't choose hash aggregation if it estimates that
>> the hash table would need to exceed work_mem, but its estimate
>> could be wrong.

> Query and plan attached.

Yeah, so, this looks suspicious:

 ->  HashAggregate  (cost=181320662.52..181323190.52 rows=252800 width=16)
   Group Key: gsm.merchant_id, cer.date
   ->  Merge Left Join  (cost=161250580.17..170174828.82 rows=89196 
width=71)

How accurate is that estimate for the number of merchant_id/date
groups?  Is the estimate for the size of the input join reasonable?

Assuming this is the problem, a brute-force fix could be to turn off
enable_hashagg.  But it'd be better to try to get the planner's
estimates more in line with reality.

regards, tom lane

unexpected character used as group separator by to_char

[Vincent Veyron]

Hi,

I'm having a problem with to_char() inserting unexpected characters as group 
separator.

For the record below, I wish to display 'credit' properly formated in an html 
form, using to_char(credit, 'FM999G990D00') 

SELECT *, to_char(credit, 'FM999G990D00') as yo from tblprospect_balance where 
id_item = 33;

 id_prospect | id_item | date_item  | libelle | debit | credit  | lettrage | 
id_facture |yo
-+-++-+---+-+--++--
 385 |  33 | 2021-03-09 | yo man  |  0.00 | 2345.10 |  |
  8 | 2 345,10

The numbers are properly displayed in the html form. However, updating the 
record requires the user to manually delete the space in '2 345,10', otherwise 
the last digit is lost. Typing numbers including a group separator using the 
space bar works fine.

I exported the record to a text file and inspected it with emacs in hexadecimal 
mode :

3338 3509  0932 3032 312d 3033 2d30  385.33.2021-03-0
0010: 3909 796f 206d 616e 0930 2e30 3009 3233  9.yo man.0.00.23
0020: 3435 2e31 3009 0938 0932 e280 af33 3435  45.10..8.2...345
0030: 2c31 300a,10.

As you can see, the space in field 'libelle' (yo man) is different from the one 
in field 'yo' (2...345,10)

The difference is also apparent in a link built using the record's fields as 
parameters : 

/base/prospect?id_prospect=385&balance=0&update_item=0&id_item=33&id_facture=8&date_item=2021-03-09&libelle=yo%20man&debit=0,00&credit=2%E2%80%AF345%2C10&lettrage=

^   
What can I do to get a standard space as group separator for my numbers?

 system information 
I use postgresql 11.9 on Debian buster

My settings are : 

show lc_numeric;
 lc_numeric  
-
 fr_FR.UTF-8

locale
LANG=C.UTF-8
LANGUAGE=
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=

\d tblprospect_balance
Table "public.tblprospect_balance"
   Column| Type  | Collation | Nullable |   
Default
-+---+---+--+--
 id_prospect | integer   |   | not null | 
 id_item | integer   |   | not null | 
nextval('tblprospect_balance_id_item_seq'::regclass)
 date_item   | date  |   | not null | CURRENT_DATE
 libelle | text  |   |  | 
 debit   | numeric(10,2) |   | not null | 0
 credit  | numeric(10,2) |   | not null | 0
 lettrage| text  |   |  | 
 id_facture  | integer   |   | not null | 0

-- 
Bien à vous, Vincent Veyron

https://marica.fr/
Logiciel de gestion des sinistres assurances, des dossiers contentieux et des 
contrats pour le service juridique

Re: unexpected character used as group separator by to_char

[David G. Johnston]

On Tue, Mar 9, 2021 at 1:28 PM Vincent Veyron  wrote:

> For the record below, I wish to display 'credit' properly formated in an
> html form, using to_char(credit, 'FM999G990D00')
>
>  ^   
> What can I do to get a standard space as group separator for my numbers?
>

By using "G" you are giving up control and letting your locale settings
decide what gets output.  You can continue to use to_char but take control
back by being explicit, or pass actual numbers into and out of the database
and let your front-end deal with presentation concerns.  I suggest the
latter.

David J.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Mar 8, 2021 at 9:41 PM Tom Lane  wrote:
>> Guyren Howe  writes:
>>> This seems like an important consideration. I've spent 10 minutes
>>> searching the documentation for PG 11 and can't find where it is
>>> documented. Perhaps it should be made more prominent?

>> https://www.postgresql.org/docs/current/xproc.html

> CREATE FUNCTION links into 37.3 but CREATE PROCEDURE doesn't link into 37.4
> Even if this may not be sufficient it seems reasonable to at least remain
> consistent.  I suspect most people start at CREATE, not "Server
> Programming".

Yeah, fair point, and it also seems like we ought to give those links
more prominence.  In the attached proposed patch, I put them into the
introductory section of the reference pages.  I also failed to resist
the temptation to do some wordsmithing in 38.4 ...

regards, tom lane

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9d41967ad3..9242c54329 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1959,7 +1959,8 @@ SELECT * FROM get_available_flightid(CURRENT_DATE);
  or DO block can call a procedure
  using CALL.  Output parameters are handled
  differently from the way that CALL works in plain
- SQL.  Each INOUT parameter of the procedure must
+ SQL.  Each OUT or INOUT
+ parameter of the procedure must
  correspond to a variable in the CALL statement, and
  whatever the procedure returns is assigned back to that variable after
  it returns.  For example:
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3c1eaea651..d18a5ab5d2 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -100,6 +100,11 @@ CREATE [ OR REPLACE ] FUNCTION
To be able to create a function, you must have USAGE
privilege on the argument types and the return type.
   
+
+  
+   Refer to  for further information on writing
+   functions.
+  
  
 
  
@@ -578,12 +583,6 @@ CREATE [ OR REPLACE ] FUNCTION
 
 

-
-   
-Refer to  for further information on writing
-functions.
-   
-
  
 
  
diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml
index e258eca5ce..b4d2e5920c 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -76,6 +76,11 @@ CREATE [ OR REPLACE ] PROCEDURE
To be able to create a procedure, you must have USAGE
privilege on the argument types.
   
+
+  
+   Refer to  for further information on writing
+   procedures.
+  
  
 
  
@@ -328,6 +333,10 @@ $$;
 
 CALL insert_data(1, 2);
 
+
+  
+   For more information and examples, see .
+  
  
 
  
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 2863f7c206..c492348261 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -81,19 +81,19 @@
   
 

-A procedure is a database object similar to a function.  The difference is
+A procedure is a database object similar to a function.  One difference is
 that a procedure does not return a value, so there is no return type
 declaration.  While a function is called as part of a query or DML
 command, a procedure is called in isolation using
 the CALL command.  If the CALL command is not
-part of an explicit transaction, a procedure in many server-side
-languages can commit, rollback, and begin new transactions during
-its execution, which is not possible in functions.
+part of an explicit transaction block, a procedure can commit or roll back
+transactions (then automatically beginning a new transaction) during
+its execution, which a function cannot do.

 

-The explanations on how to define user-defined functions in the rest of
-this chapter apply to procedures as well, except that
+The explanations in the following sections about how to define
+user-defined functions apply to procedures as well, except that
 the CREATE PROCEDURE command is used instead, there is
 no return type, and some other features such as strictness don't apply.

Re: unexpected character used as group separator by to_char

[Vincent Veyron]

On Tue, 9 Mar 2021 13:33:19 -0700
"David G. Johnston"  wrote:
> 
> By using "G" you are giving up control and letting your locale settings
> decide what gets output.  You can continue to use to_char but take control
> back by being explicit, or pass actual numbers into and out of the database
> and let your front-end deal with presentation concerns.  I suggest the
> latter.

Well, that was a fast answer :-)

Indeed, replacing "G" with \s works. However, the application is destined to be 
multi-lingual, and number formats vary widely between countries :

1,000.25 (US)
1'000,25 (CH)
1 000,25 (FR)
1.000,25 (GER)
etc...

So, I intended to follow this advice by Tom Lane :

https://www.postgresql-archive.org/GENERAL-setting-LC-NUMERIC-td1857521.html

Having a database with the proper lc_numeric setting for each country, and 
using to_char/to_number to manipulate numbers is much more appealing than 
writing my own parser in my front end. But this weird space is getting in my 
way.

-- 
Bien à vous, Vincent Veyron

https://marica.fr/
Logiciel de gestion des sinistres assurances, des dossiers contentieux et des 
contrats pour le service juridique

Re: unexpected character used as group separator by to_char

[Tom Lane]

Vincent Veyron  writes:
> Having a database with the proper lc_numeric setting for each country, and 
> using to_char/to_number to manipulate numbers is much more appealing than 
> writing my own parser in my front end. But this weird space is getting in my 
> way.

The point here is that 'G' and related format codes act as specified by
your LC_MONETARY locale.  If you don't like the results, you need to use
a different locale.

(I suppose you could also use regexp_replace to convert random forms
of whitespace to plain ASCII space.)

regards, tom lane

Re: unexpected character used as group separator by to_char

[Rob Sargent]

On 3/9/21 2:22 PM, Tom Lane wrote:

Vincent Veyron  writes:

Having a database with the proper lc_numeric setting for each country, and 
using to_char/to_number to manipulate numbers is much more appealing than 
writing my own parser in my front end. But this weird space is getting in my 
way.

The point here is that 'G' and related format codes act as specified by
your LC_MONETARY locale.  If you don't like the results, you need to use
a different locale.

(I suppose you could also use regexp_replace to convert random forms
of whitespace to plain ASCII space.)

regards, tom lane


I'm in David's camp on this one: there are plenty of currency format 
conversion libraries from which to choose.


rjs

Re: Need help with clarification on stored procedure support in PostGreSQL database

[David G. Johnston]

On Tue, Mar 9, 2021 at 1:57 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Mon, Mar 8, 2021 at 9:41 PM Tom Lane  wrote:
> >> Guyren Howe  writes:
> >>> This seems like an important consideration. I've spent 10 minutes
> >>> searching the documentation for PG 11 and can't find where it is
> >>> documented. Perhaps it should be made more prominent?
>
> >> https://www.postgresql.org/docs/current/xproc.html
>
> > CREATE FUNCTION links into 37.3 but CREATE PROCEDURE doesn't link into
> 37.4
> > Even if this may not be sufficient it seems reasonable to at least remain
> > consistent.  I suspect most people start at CREATE, not "Server
> > Programming".
>
> Yeah, fair point, and it also seems like we ought to give those links
> more prominence.  In the attached proposed patch, I put them into the
> introductory section of the reference pages.  I also failed to resist
> the temptation to do some wordsmithing in 38.4 ...
>
>
Thanks.  Some observations.

The omission of the "OUT" parameter mode seems intentional since at present
our procedures do not support OUT mode parameters.

Instead of "The difference" or "One difference" I would suggest: "However,
a procedure does not return a value, so there is no return type
declaration; though a procedure can declare INOUT (but not plain OUT)
parameters."

Relocating the links to the description instead of usage is good.  The
additional procedure link after the examples seems redundant, particularly
as the linked to location doesn't actually have more examples.  The
"...further information on writing" doesn't really match up with reality
either.  It is more "...further information on incorporating
functions/procedures into applications."

David J.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Tom Lane]

"David G. Johnston"  writes:
> The omission of the "OUT" parameter mode seems intentional since at present
> our procedures do not support OUT mode parameters.

Um, I just created one.  I think this *used* to be true, and this bit of
the docs didn't get fixed.  If I back-patch this, I'll have to research
when it changed.

> Instead of "The difference" or "One difference" I would suggest: "However,
> a procedure does not return a value, so there is no return type
> declaration; though a procedure can declare INOUT (but not plain OUT)
> parameters."

Not sure if that's an improvement.

> Relocating the links to the description instead of usage is good.  The
> additional procedure link after the examples seems redundant, particularly
> as the linked to location doesn't actually have more examples.

I was modeling that on the existing pattern in create_function.sgml,
which has similar verbiage in the EXAMPLES section.  But I suppose
we could drop that if we have a link in the description section.

regards, tom lane

Re: unexpected character used as group separator by to_char

[Vincent Veyron]

On Tue, 09 Mar 2021 16:22:07 -0500
Tom Lane  wrote:

> The point here is that 'G' and related format codes act as specified by
> your LC_MONETARY locale.  If you don't like the results, you need to use
> a different locale.

This is a numeric(10,2) type field though. I tried casting it to money type, 
with lc_monetary set to "fr_FR.UTF-8", same weird space

> 
> (I suppose you could also use regexp_replace to convert random forms
> of whitespace to plain ASCII space.)

No dice. 'G' formatting looks like a whitespace, but is different (it appears 
to be slightly narrower when displayed in html, too)  :

select regexp_replace(to_char(1234.56, 'FM999 990D00'), E'[\\s]', 'x');
 regexp_replace 

 1x234,56
(1 row)


select regexp_replace(to_char(1234.56, 'FM999G990D00'), E'[\\s]', 'x');
 regexp_replace 

 1 234,56
(1 row)

Re: unexpected character used as group separator by to_char

[Rob Sargent]

On 3/9/21 4:03 PM, Vincent Veyron wrote:

No dice. 'G' formatting looks like a whitespace, but is different (it appears 
to be slightly narrower when displayed in html, too)  :

An "n-space", no?

Re: Need help with clarification on stored procedure support in PostGreSQL database

[David G. Johnston]

On Tue, Mar 9, 2021 at 3:21 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > The omission of the "OUT" parameter mode seems intentional since at
> present
> > our procedures do not support OUT mode parameters.
>
> Um, I just created one.  I think this *used* to be true, and this bit of
> the docs didn't get fixed.  If I back-patch this, I'll have to research
> when it changed.
>

Five months ago it seems.

https://github.com/postgres/postgres/commit/2453ea142233ae57af452019c3b9a443dad1cdd0

The patch and email thread for that commit make me pause, though I cannot
put into words why.


>
> > Instead of "The difference" or "One difference" I would suggest:
> "However,
> > a procedure does not return a value, so there is no return type
> > declaration; though a procedure can declare INOUT (but not plain OUT)
> > parameters."
>
> Not sure if that's an improvement.
>

The "however" part is probably a wash; I just dislike seeing a count
started and not having an ending and thus being left in a state of "what
didn't they include that's important".

The part about commenting about OUT/INOUT parameters still working even
though there is no return provide complete coverage of the
differences/similarities between functions and procedures with respect to
passing back data to the caller.

>
> > Relocating the links to the description instead of usage is good.  The
> > additional procedure link after the examples seems redundant,
> particularly
> > as the linked to location doesn't actually have more examples.
>
> I was modeling that on the existing pattern in create_function.sgml,
> which has similar verbiage in the EXAMPLES section.  But I suppose
> we could drop that if we have a link in the description section.
>
>
Yeah, it was an existing deficiency, but being a bit more invasive seems
warranted, and as you say it is be located partly because it is much higher
level content being pointed to (hence description, not usage/examples).

David J.

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Tom Lane]

"David G. Johnston"  writes:
> On Tue, Mar 9, 2021 at 3:21 PM Tom Lane  wrote:
>> "David G. Johnston"  writes:
>>> Instead of "The difference" or "One difference" I would suggest:
>>> "However, a procedure does not return a value, so there is no return type
>>> declaration; though a procedure can declare INOUT (but not plain OUT)
>>> parameters."

>> Not sure if that's an improvement.

> The "however" part is probably a wash; I just dislike seeing a count
> started and not having an ending and thus being left in a state of "what
> didn't they include that's important".

The rest of the paragraph is what's important enough to include here.

I very strongly dislike the existing "The difference ..." wording,
because it implies that that's the only difference, which is immediately
belied by the rest.  "However" isn't a lot better.  Would it be better
to turn the para into a bulleted list, which we could introduce with
"The key differences are:" ?

regards, tom lane

Re: Need help with clarification on stored procedure support in PostGreSQL database

[David G. Johnston]

On Tue, Mar 9, 2021 at 4:50 PM Tom Lane  wrote:

> I very strongly dislike the existing "The difference ..." wording,
> because it implies that that's the only difference, which is immediately
> belied by the rest.


Agreed!


> Would it be better
> to turn the para into a bulleted list, which we could introduce with
> "The key differences are:" ?
>

Indeed, reworking the rest of the paragraph around that introduction would
be much better.

David J.

Re: unexpected character used as group separator by to_char

[Tom Lane]

Vincent Veyron  writes:
> Tom Lane  wrote:
>> (I suppose you could also use regexp_replace to convert random forms
>> of whitespace to plain ASCII space.)

> No dice. 'G' formatting looks like a whitespace, but is different (it appears 
> to be slightly narrower when displayed in html, too)  :

Huh ... do you have lc_monetary and lc_ctype set the same?  The
latter is (I believe) what determines the behavior of regex \s.

In any case, you could force the issue with a pattern like '[\s\u]'
for whatever the code point of that character is.

regards, tom lane

Re: Need help with clarification on stored procedure support in PostGreSQL database

[Tom Lane]

"David G. Johnston"  writes:
> On Tue, Mar 9, 2021 at 4:50 PM Tom Lane  wrote:
>> Would it be better
>> to turn the para into a bulleted list, which we could introduce with
>> "The key differences are:" ?

> Indeed, reworking the rest of the paragraph around that introduction would
> be much better.

v2 attached.

regards, tom lane

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9d41967ad3..9242c54329 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1959,7 +1959,8 @@ SELECT * FROM get_available_flightid(CURRENT_DATE);
  or DO block can call a procedure
  using CALL.  Output parameters are handled
  differently from the way that CALL works in plain
- SQL.  Each INOUT parameter of the procedure must
+ SQL.  Each OUT or INOUT
+ parameter of the procedure must
  correspond to a variable in the CALL statement, and
  whatever the procedure returns is assigned back to that variable after
  it returns.  For example:
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3c1eaea651..f1001615f4 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -100,6 +100,11 @@ CREATE [ OR REPLACE ] FUNCTION
To be able to create a function, you must have USAGE
privilege on the argument types and the return type.
   
+
+  
+   Refer to  for further information on writing
+   functions.
+  
  
 
  
@@ -578,12 +583,6 @@ CREATE [ OR REPLACE ] FUNCTION
 
 

-
-   
-Refer to  for further information on writing
-functions.
-   
-
  
 
  
@@ -661,8 +660,7 @@ CREATE FUNCTION foo(int, int default 42) ...
   Examples
 
   
-   Here are some trivial examples to help you get started.  For more
-   information and examples, see .
+   Add two integers using a SQL function:
 
 CREATE FUNCTION add(integer, integer) RETURNS integer
 AS 'select $1 + $2;'
diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml
index e258eca5ce..6dbc012719 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -76,6 +76,11 @@ CREATE [ OR REPLACE ] PROCEDURE
To be able to create a procedure, you must have USAGE
privilege on the argument types.
   
+
+  
+   Refer to  for further information on writing
+   procedures.
+  
  
 
  
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 2863f7c206..325ce9222a 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -81,21 +81,55 @@
   
 

-A procedure is a database object similar to a function.  The difference is
-that a procedure does not return a value, so there is no return type
-declaration.  While a function is called as part of a query or DML
-command, a procedure is called in isolation using
-the CALL command.  If the CALL command is not
-part of an explicit transaction, a procedure in many server-side
-languages can commit, rollback, and begin new transactions during
-its execution, which is not possible in functions.
+A procedure is a database object similar to a function.
+The key differences are:
+
+
+ 
+  
+   Procedures are defined with
+   the CREATE
+   PROCEDURE command, not CREATE
+   FUNCTION.
+  
+ 
+ 
+  
+   Procedures do not return a function value; hence CREATE
+   PROCEDURE lacks a RETURNS clause.
+   However, procedures can instead return data to their callers via
+   output parameters.
+  
+ 
+ 
+  
+   While a function is called as part of a query or DML command, a
+   procedure is called in isolation using
+   the CALL command.
+  
+ 
+ 
+  
+   If the invoking CALL command is not part of an
+   explicit transaction block, a procedure can commit or roll back
+   transactions (then automatically beginning a new transaction)
+   during its execution, which a function cannot do.
+  
+ 
+ 
+  
+   Certain function attributes such as strictness don't apply to
+   procedures, because those attributes control how the function is
+   used in a query, which isn't relevant to procedures.
+  
+ 
+

 

-The explanations on how to define user-defined functions in the rest of
-this chapter apply to procedures as well, except that
-the CREATE PROCEDURE command is used instead, there is
-no return type, and some other features such as strictness don't apply.
+The explanations in the following sections about how to define
+user-defined functions apply to procedures as well, except for the
+points made above.

 

Logical Replication, CPU load and Locking contention

[Martín Fernández]

Hello,

I’m troubleshooting a problem at my company with a pg 12 cluster that we run.

We are using Amazon DMS to replicate data from our database into S3 buckets.  
DMS replicates data by using logical replication slots. 

After introducing DMS in our environment, we have seen an increase in CPU load 
of 20 points at business hours (from ~60% to ~80%).

The other thing that we have identified is that AccessShareLocks increase 
considerably when DMS running.

Based on this information, I’m trying to understand if this is something 
expected when running logical replication or not. We’ve been running physical 
replication for several years and we haven’t seen nothing like this. It could 
be the case that the issue is not related at all with logical replication and 
is purely a DMS artifact. 

Thanks before hand!

Best,
Martín

Using a 'loopback' FDW

[Steve Baldwin]

I'm guessing I'm doing something wrong here. I've used postgres_fdw before
to connect between servers, but in this instance I'm trying to use it
to connect back to itself.

(This is postgres 13.2)

In my local DB have a user 'slaw_owner' which has a password of 'password'.
This user has been granted usage on postgres_fdw.

slaw_owner@slaw=> \des+
   List of foreign servers
 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version |
FDW options | Description
--+---+--+---+--+-+-+-
(0 rows)

slaw_owner@slaw=> create server caas foreign data wrapper postgres_fdw
options(dbname 'slaw', host 'localhost');
CREATE SERVER
slaw_owner@slaw=> create user mapping for slaw_owner server caas options
(user 'slaw_owner', password 'password');
CREATE USER MAPPING

So far so good. When I try to use this mapping however

slaw_owner@slaw=> import foreign schema caas limit to (api_key, buyer_user)
from server caas into fdw;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a
password.
HINT:  Target server's authentication method must be changed or
password_required=false set in the user mapping attributes.

When I look at the server (csv) log it _seems_ to be connecting
successfully:

2021-03-10 02:49:22.100
UTC,"slaw_owner","slaw",167,"172.20.0.1:63186",6048331e.a7,9,"idle",2021-03-10
02:46:54 UTC,3/106,0,LOG,0,"statement: import foreign schema caas limit
to (api_key, buyer_user) from server caas into fdw;","psql","client
backend"
2021-03-10 02:49:22.104
UTC,,,667,"127.0.0.1:54290",604833b2.29b,1,"",2021-03-10
02:49:22 UTC,,0,LOG,0,"connection received: host=127.0.0.1
port=54290","","not initialized"
2021-03-10 02:49:22.106
UTC,"slaw_owner","slaw",667,"127.0.0.1:54290",604833b2.29b,2,"authentication",2021-03-10
02:49:22 UTC,4/17,0,LOG,0,"connection authorized: user=slaw_owner
database=slaw application_name=postgres_fdw","","client backend"
2021-03-10 02:49:22.109
UTC,"slaw_owner","slaw",167,"172.20.0.1:63186",6048331e.a7,10,"IMPORT
FOREIGN SCHEMA",2021-03-10 02:46:54 UTC,3/106,0,ERROR,2F003,"password is
required","Non-superuser cannot connect if the server does not request a
password.","Target server's authentication method must be changed or
password_required=false set in the user mapping attributes.""import
foreign schema caas limit to (api_key, buyer_user) from server caas into
fdw;",,,"psql","client backend"
2021-03-10 02:49:22.111
UTC,"slaw_owner","slaw",667,"127.0.0.1:54290",604833b2.29b,3,"idle",2021-03-10
02:49:22 UTC,,0,LOG,0,"disconnection: session time: 0:00:00.008
user=slaw_owner database=slaw host=127.0.0.1
port=54290","postgres_fdw","client backend"

If, as a superuser I modify the user mapping, everything works:

[~/git/slaw]$ psql -U dba
psql (13.2)
Type "help" for help.

dba@slaw=# alter user MAPPING FOR slaw_owner server caas options (add
password_required 'false');
ALTER USER MAPPING
dba@slaw=#
\q
[~/git/slaw]$ psql
psql (13.2)
Type "help" for help.

slaw_owner@slaw=> import foreign schema caas limit to (api_key, buyer_user)
from server caas into fdw;
IMPORT FOREIGN SCHEMA

I don't understand why it doesn't like it when I define a password in the
user mapping.

Any ideas gratefully received.

Thanks,

Steve

Re: Using a 'loopback' FDW

[Tom Lane]

Steve Baldwin  writes:
> I'm guessing I'm doing something wrong here. I've used postgres_fdw before
> to connect between servers, but in this instance I'm trying to use it
> to connect back to itself.

What are your pg_hba.conf settings for local connections?

> ERROR:  password is required
> DETAIL:  Non-superuser cannot connect if the server does not request a
> password.
> HINT:  Target server's authentication method must be changed or
> password_required=false set in the user mapping attributes.

This suggests that you're using trust, peer, or something else in which
the password isn't actually relevant.

regards, tom lane

Re: Using a 'loopback' FDW

[Steve Baldwin]

Thanks Tom. I'm running postgres from the standard alpine docker container
and haven't changed that file. I'm not at my laptop ATM but will check it
out when I get home.
Cheers, Steve

On Wed, 10 Mar 2021, 14:42 Tom Lane,  wrote:

> Steve Baldwin  writes:
> > I'm guessing I'm doing something wrong here. I've used postgres_fdw
> before
> > to connect between servers, but in this instance I'm trying to use it
> > to connect back to itself.
>
> What are your pg_hba.conf settings for local connections?
>
> > ERROR:  password is required
> > DETAIL:  Non-superuser cannot connect if the server does not request a
> > password.
> > HINT:  Target server's authentication method must be changed or
> > password_required=false set in the user mapping attributes.
>
> This suggests that you're using trust, peer, or something else in which
> the password isn't actually relevant.
>
> regards, tom lane
>

Re: unexpected character used as group separator by to_char

[Alvaro Herrera]

On 2021-Mar-10, Vincent Veyron wrote:

> On Tue, 09 Mar 2021 16:22:07 -0500
> Tom Lane  wrote:

> > (I suppose you could also use regexp_replace to convert random forms
> > of whitespace to plain ASCII space.)
> 
> No dice. 'G' formatting looks like a whitespace, but is different (it
> appears to be slightly narrower when displayed in html, too)  :

That space (0xe280af) is U+202F, which appears to be used for French and
Mongolian languages (exclusively?).  It is quite possible that in the
future some other language will end up using some different whitespace
character, possibly breaking any code you write today -- the use of
U+202F appears to be quite recent.

Maybe it'd be better to have the query return two fields: one destined
for printing, the other you can use to feed the database back.


-- 
Álvaro Herrera   Valdivia, Chile
"Porque Kim no hacía nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)

Re: Using a 'loopback' FDW

[Steve Baldwin]

Thanks so much Tom. That was indeed the problem. In hindsight the error
information makes perfect sense (at least after reading the docs on
pg_hba.conf).

Regards,

Steve

On Wed, Mar 10, 2021 at 3:04 PM Steve Baldwin 
wrote:

> Thanks Tom. I'm running postgres from the standard alpine docker container
> and haven't changed that file. I'm not at my laptop ATM but will check it
> out when I get home.
> Cheers, Steve
>
> On Wed, 10 Mar 2021, 14:42 Tom Lane,  wrote:
>
>> Steve Baldwin  writes:
>> > I'm guessing I'm doing something wrong here. I've used postgres_fdw
>> before
>> > to connect between servers, but in this instance I'm trying to use it
>> > to connect back to itself.
>>
>> What are your pg_hba.conf settings for local connections?
>>
>> > ERROR:  password is required
>> > DETAIL:  Non-superuser cannot connect if the server does not request a
>> > password.
>> > HINT:  Target server's authentication method must be changed or
>> > password_required=false set in the user mapping attributes.
>>
>> This suggests that you're using trust, peer, or something else in which
>> the password isn't actually relevant.
>>
>> regards, tom lane
>>
>