Re: Christopher Browne

[Simon Riggs]

On Wed, 4 Nov 2020 at 23:29, Steve Singer  wrote:
>
>
> It is with much sadness that I am letting the community know that Chris
> Browne passed away recently.

Sad news. He was a good man and a valued contributor.

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

Re: Error While reinstalling PG 11

[sivapostg...@yahoo.com]

Hello,
Windows Server 2008 R2, Postgresql 11.8
We formatted our server and tried to install postgres again by setting the same 
old data directory.  And we got the following error. 
There has been an error:Unknown error while running C:\Windows\System32\icards 
"D:\PG Data\V11" /T /Q /grant "NT AUTHORITY\NetworkService:(OI)(CI)F" The 
application will exit now.
What settings we need to do to reinstall postgres again ?
Happiness AlwaysBKR Sivaprakash

Re: PANIC: could not write to log file {} at offset {}, length {}: Invalid argument

[Magnus Hagander]

On Thu, Nov 5, 2020 at 3:12 AM Michael Paquier  wrote:
>
> On Wed, Nov 04, 2020 at 01:24:46PM +0100, Andreas Kretschmer wrote:
> >> Any ideas about what is the problem? or anything else I need to check?
> >
> > wild guess: Antivirus Software?
>
> Perhaps not.  To bring more context in here, PostgreSQL opens any
> files on WIN32 with shared writes and reads allowed to have an
> equivalent of what we do on all *nix platforms.  Note here that the
> problem comes from a WAL segment write, which is done after the file
> handle is opened in shared mode.  As long as the fd is correctly
> opened, any attempt for an antivirus software to open a file with an
> exclusive write would be blocked, no?

The problem with AVs generally doesn't come from them opening files in
non-share mode (I've, surprisingly enough, seen backup software that
causes that problem for example). It might happen on scheduled scans
for example, but the bigger problem with AV software has always been
their filter driver software which intercepts both the open/close and
the read/write calls an application makes and "does it's magic" on
them before handing the actual call up to the operating system. It's
completely independent of how the file is opened.

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

ssl certification

[Lu, Chenyang]

Hi~

Forgive me for not being familiar with SSL.

When I try to use SSL certification function.(in postgresql9.5.22)
The service uses the following configuration
Set ssl=on in postgresql.conf
　　　Set ssl_cert_file=server.crt in postgresql.conf
　　　Set ssl_key_file=server.key in postgresql.conf
　　　Set ssl_ca_file=root.crt in postgresql.conf
CASE 1. Add hostssltest all all md5 in 
pg_hba.conf
CASE 2. Add hostssltest all all cert in 
pg_hba.conf

In CASE 1 : use  psql -U test -d 
"postgresql://193.xxx.xxx.xxx/test?sslmode=verify-ca"
I can connect normally.
In CASE 2 : use  the same connection string
I got "psql: FATAL:  connection requires a valid client 
certificate (10689)"

Question:cert in pg_hba.conf means what? How can I configure the client 
Certificate it needs.

Thanks~

Re: Christopher Browne

[Jonathan Katz]

> On Nov 4, 2020, at 6:29 PM, Steve Singer  wrote:
> 
> 
> It is with much sadness that I am letting the community know that Chris 
> Browne passed away recently.
> 
> Chris had been a long time community member and was active on various 
> Postgresql mailing lists.  He was a member of the funds committee, the PgCon 
> program committee and served on the board of the PostgreSQL Community 
> Association of Canada. Chris was a maintainer of the Slony replication system 
> and worked on various PG related tools.
> 
> I worked with Chris for over 10 years and anytime someone had a problem he 
> would jump at the chance to help and propose solutions. He always had time to 
> listen to your problem and offer ideas or explain how something worked.

I am deeply saddened by this news.

I was very lucky to work with Chris on some of the committees you mentioned. He 
was always generous, helpful, friendly and, as you said, would be willing to 
listen and work with you on any problem.

> I will miss Chris

+1. We will certainly miss him. My sincerest condolences to his family.

Jonathan

Large index

[Yambu]

What disadvantage does a large table (30mil records) has over a small table
about 20k records when it comes to querying using an indexed column?

Analyze and Statistics

[luis . roberto]

Hi! 

Analyzing a table which has a statistic object raises the message: statistics 
object "public.new_statistic" could not be computed for relation 
"public.client" 

Re: Large index

[Michael Lewis]

On Thu, Nov 5, 2020, 6:52 AM Yambu  wrote:

> What disadvantage does a large table (30mil records) has over a small
> table about 20k records when it comes to querying using an indexed column?
>

Table with 20k rows will likely fit entirely into shared_buffers and not
involve any disk i/o for its use.

>

precautions/prerequisites to take for specific table

[Vasu Madhineni]

Hi All,

In my organisation a newly built project application team requirement on
tables like have a column (text type), with size can reach around 3 MB, and
45 million records annually.

Are there any specific precautions/prerequisites we have to take from DBA
end to handle this type of table.


Thanks in advance.

Regards,
Vasu

greater than vs between in where clause

[Yambu]

May i know if there is a difference in speed between 1 ,2 and 3 below , if
column start_date is indexed

1.  select * from table_1 where start_date > '1 Oct 2020'
2. select   * from table_1 where start_date between '1 Oct 2020' and now()
3. select * from table_1 where start_date between '1 Oct 2020' and '5 Nov
2020 23:59:59'

Re: greater than vs between in where clause

[Adrian Klaver]

On 11/5/20 6:49 AM, Yambu wrote:
May i know if there is a difference in speed between 1 ,2 and 3 below , 
if column start_date is indexed


1.  select * from table_1 where start_date > '1 Oct 2020'
2. select   * from table_1 where start_date between '1 Oct 2020' and now()
3. select * from table_1 where start_date between '1 Oct 2020' and '5 
Nov 2020 23:59:59'


Run EXPLAIN ANALYZE(https://www.postgresql.org/docs/13/sql-explain.html) 
on each and let us know.


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

Re: precautions/prerequisites to take for specific table

[Philip Semanchuk]

> On Nov 5, 2020, at 8:49 AM, Vasu Madhineni  wrote:
> 
> Hi All,
> 
> In my organisation a newly built project application team requirement on 
> tables like have a column (text type), with size can reach around 3 MB, and 
> 45 million records annually. 
> 
> Are there any specific precautions/prerequisites we have to take from DBA end 
> to handle this type of table.


Hi Vasu,
Postgres can handle that just fine. We have a table with two text columns 
that’s 18Gb and almost 400 million rows, and that’s not a big table by some 
people’s standards.

If you have specific concerns, you’ll need to tell us more about your situation 
and why you think you won’t be satisfied.

Cheers
Philip

JSONB order?

[Tony Shelver]

I am getting data out of a spreadsheet (Google API) and loading it into a
Python 3.8 dict.
I then dump it to json format. On printing, it's in the correct order:
{
"Timestamp": "05/11/2020 17:08:08",
"Site Name": "SureSecurity Calgary",
"Last Name": "Shelver",
"First Name": "Anthony",
"Middle Name(s)": "",
"Phone": 555757007,
 "Person visited": "test",
 "Body Temperature": 44,
 "Fever or chills": "No",
 "Difficulty breathing or shortness of breath": "No",
 "Cough": "No",
 "Sore throat, trouble swallowing": "No",
 "Runny nose/stuffy nose or nasal congestion": "No",
 "Decrease or loss of smell or taste": "No",
 "Nausea, vomiting, diarrhea, abdominal pain": "No",
 "Not feeling well, extreme tiredness, sore muscles":
 "Yes", "Have you travelled outside of Canada in the past 14 days?": "No",
 "Have you had close contact with a confirmed or probable case of
COVID-19?": "No"
 }

It's passed to a plpgsql function, using a jsonb parameter variable.
This insets it into the table, into into a jsonb column.

When looking at what the column contents are, it's been rearranged.  The
order always seems to have been rearranged in the same way, as below:
{
"Cough": "No",
"Phone": 757007,
"Last Name": "Shelver",
"Site Name": "SureSecurity Calgary",
"Timestamp": "04/11/2020 17:34:48",
"First Name": "Anthony",
"Middle Name(s)": "",
"Person visited": "Many",
"Fever or chills": "No",
"Body Temperature": 44,
"Sore throat, trouble swallowing": "No",
"Decrease or loss of smell or taste": "No",
"Nausea, vomiting, diarrhea, abdominal pain": "No",
"Runny nose/stuffy nose or nasal congestion": "No",
"Difficulty breathing or shortness of breath": "No",
"Not feeling well, extreme tiredness, sore muscles": "No",
"Have you travelled outside of Canada in the past 14 days?": "No",
"Have you had close contact with a confirmed or probable case of
COVID-19?": "No"
}

If the order had remained the same, it's child's play to pull the data out
and present it in a report, even if the data elements change.
But...  seen above, the order gets mixed up.

Any ideas?

Thanks

Tony Shelver

Re: JSONB order?

[Christophe Pettus]

> On Nov 5, 2020, at 07:34, Tony Shelver  wrote:
> But...  seen above, the order gets mixed up.
> 
> Any ideas?

JSON objects, like Python dicts, are not automatically ordered by key.  Once 
you move from the column space to the JSON object space, you can't rely on the 
object keys being in a consistent order.

You'll want to have a step when ingesting the JSON object into a report that 
lines up the key values appropriately with the right presentation in the report.
--
-- Christophe Pettus
   x...@thebuild.com

Re: JSONB order?

[Magnus Hagander]

On Thu, Nov 5, 2020 at 4:35 PM Tony Shelver  wrote:
>
> I am getting data out of a spreadsheet (Google API) and loading it into a 
> Python 3.8 dict.
> I then dump it to json format. On printing, it's in the correct order:
> {
> "Timestamp": "05/11/2020 17:08:08",
> "Site Name": "SureSecurity Calgary",
> "Last Name": "Shelver",
> "First Name": "Anthony",
> "Middle Name(s)": "",
> "Phone": 555757007,
>  "Person visited": "test",
>  "Body Temperature": 44,
>  "Fever or chills": "No",
>  "Difficulty breathing or shortness of breath": "No",
>  "Cough": "No",
>  "Sore throat, trouble swallowing": "No",
>  "Runny nose/stuffy nose or nasal congestion": "No",
>  "Decrease or loss of smell or taste": "No",
>  "Nausea, vomiting, diarrhea, abdominal pain": "No",
>  "Not feeling well, extreme tiredness, sore muscles":
>  "Yes", "Have you travelled outside of Canada in the past 14 days?": "No",
>  "Have you had close contact with a confirmed or probable case of COVID-19?": 
> "No"
>  }
>
> It's passed to a plpgsql function, using a jsonb parameter variable.
> This insets it into the table, into into a jsonb column.
>
> When looking at what the column contents are, it's been rearranged.  The 
> order always seems to have been rearranged in the same way, as below:
> {
> "Cough": "No",
> "Phone": 757007,
> "Last Name": "Shelver",
> "Site Name": "SureSecurity Calgary",
> "Timestamp": "04/11/2020 17:34:48",
> "First Name": "Anthony",
> "Middle Name(s)": "",
> "Person visited": "Many",
> "Fever or chills": "No",
> "Body Temperature": 44,
> "Sore throat, trouble swallowing": "No",
> "Decrease or loss of smell or taste": "No",
> "Nausea, vomiting, diarrhea, abdominal pain": "No",
> "Runny nose/stuffy nose or nasal congestion": "No",
> "Difficulty breathing or shortness of breath": "No",
> "Not feeling well, extreme tiredness, sore muscles": "No",
> "Have you travelled outside of Canada in the past 14 days?": "No",
> "Have you had close contact with a confirmed or probable case of 
> COVID-19?": "No"
> }
>
> If the order had remained the same, it's child's play to pull the data out 
> and present it in a report, even if the data elements change.
> But...  seen above, the order gets mixed up.
>
> Any ideas?

The json standard declares that the keys in a document are unordered,
and can appear at any order.

In PostgreSQL, jsonb will not preserve key ordering,  as a feature for
efficiency. The plain json datatype will, so if key ordering is
important you should use json instead of jsonb (but you should
probably also not use the json format in general, as it does not
guarantee this)

See https://www.postgresql.org/docs/13/datatype-json.html

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

Fwd: JSONB order?

[Tony Shelver]

-- Forwarded message -
From: Tony Shelver 
Date: Thu, 5 Nov 2020 at 17:45
Subject: Re: JSONB order?
To: Christophe Pettus 


Thanks Christophe, that's what I thought.
Just seemed weird that they were 'disordered' in exactly the same way every
time.

FYI, as of Python 3.7, dicts *are* ordered.

The problem is that we are possibly going to have many versions of these
forms with slightly differing keys, which will be a pain to order in some
hard coded way.



On Thu, 5 Nov 2020 at 17:40, Christophe Pettus  wrote:

>
>
> > On Nov 5, 2020, at 07:34, Tony Shelver  wrote:
> > But...  seen above, the order gets mixed up.
> >
> > Any ideas?
>
> JSON objects, like Python dicts, are not automatically ordered by key.
> Once you move from the column space to the JSON object space, you can't
> rely on the object keys being in a consistent order.
>
> You'll want to have a step when ingesting the JSON object into a report
> that lines up the key values appropriately with the right presentation in
> the report.
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

Re: precautions/prerequisites to take for specific table

[Ron]

On 11/5/20 7:49 AM, Vasu Madhineni wrote:

Hi All,

In my organisation a newly built project application team requirement on 
tables like have a column (text type), with size can reach around 3 MB, 
and 45 million records annually.


Are there any specific precautions/prerequisites we have to take from DBA 
end to handle this type of table.


Think about partitioning it by date.   Indexes will be smaller, and 
archiving become trivial.


--
Angular momentum makes the world go 'round.

Re: JSONB order?

[Christophe Pettus]

> On Nov 5, 2020, at 07:45, Tony Shelver  wrote:
> Thanks Christophe, that's what I thought.  
> Just seemed weird that they were 'disordered' in exactly the same way every 
> time.
> 
> FYI, as of Python 3.7, dicts are ordered.
> 
> The problem is that we are possibly going to have many versions of these 
> forms with slightly differing keys, which will be a pain to order in some 
> hard coded way.

As Magnus noted, you can use JSON instead of JSONB.  JSON is basically a text 
blob with a syntax check wrapper around it, so it will be order-stable once 
created.  (If you run it through a JSONB-expecting function, then the ordering 
may change again.)  It's less efficient to operate on than JSONB, but that 
might be OK for your purposes.

--
-- Christophe Pettus
   x...@thebuild.com

Re: JSONB order?

[David G. Johnston]

On Thu, Nov 5, 2020 at 8:46 AM Tony Shelver  wrote:

>
>
> -- Forwarded message -
> From: Tony Shelver 
> Date: Thu, 5 Nov 2020 at 17:45
> Subject: Re: JSONB order?
> To: Christophe Pettus 
>
>
> Thanks Christophe, that's what I thought.
> Just seemed weird that they were 'disordered' in exactly the same way
> every time.
>
> FYI, as of Python 3.7, dicts *are* ordered.
>
> The problem is that we are possibly going to have many versions of these
> forms with slightly differing keys, which will be a pain to order in some
> hard coded way.
>

The Google Sheet source document has column ordering.  If that is what you
want to rely upon have your Python code capture that into an array and
attach that array to the json document as a separate
"field_headers_ordered" key (or something similar).

David J.

Re: JSONB order?

[Rob Sargent]

> On Nov 5, 2020, at 8:45 AM, Tony Shelver  wrote:
> 
> 
> 
> -- Forwarded message -
> From: Tony Shelver mailto:tshel...@gmail.com>>
> Date: Thu, 5 Nov 2020 at 17:45
> Subject: Re: JSONB order?
> To: Christophe Pettus mailto:x...@thebuild.com>>
> 
> 
> Thanks Christophe, that's what I thought.  
> Just seemed weird that they were 'disordered' in exactly the same way every 
> time.
> 
> FYI, as of Python 3.7, dicts are ordered.
> 
> The problem is that we are possibly going to have many versions of these 
> forms with slightly differing keys, which will be a pain to order in some 
> hard coded way.
> 
> 
> 
> On Thu, 5 Nov 2020 at 17:40, Christophe Pettus  > wrote:
> 
> 
> > On Nov 5, 2020, at 07:34, Tony Shelver  > > wrote:
> > But...  seen above, the order gets mixed up.
> > 
> > Any ideas?
> 
> JSON objects, like Python dicts, are not automatically ordered by key.  Once 
> you move from the column space to the JSON object space, you can't rely on 
> the object keys being in a consistent order.
> 
> You'll want to have a step when ingesting the JSON object into a report that 
> lines up the key values appropriately with the right presentation in the 
> report.
> --
> -- Christophe Pettus
>x...@thebuild.com 
> 

Sounds like you’ll need a separate mechanism for maintaining versions of the 
forms and which headers represent the same data concept.  Always access data 
via canonical header translated to current form.

Diagnose memory leak in logical replication?

[Raphaël Enrici]

Dear all,

Hope everyone is doing well.

While playing with wal2json and debezium we faced an issue where 
walsender processes eat more and more memory (several hundred megabytes 
per hour) until we restart them by stopping their consumption.


We first thought to something wrong in wal2json[1] but after more 
testing today we have the exact same situation with pgoutput plugin.


The environment is Debian buster with packages from apt.postgresql.org 
(currently using 12.2-2.pgdg100+1):


postgresql-12 - 12.2-2.pgdg100+1
postgresql-12-wal2json - 2.2-1.pgdg100+1
postgresql-client-12 - 12.2-2.pgdg100+1
postgresql-client-common - 213.pgdg100+1
postgresql-common - 213.pgdg100+1

The steps to create slot and consume wal2json are the following:

pg_recvlogical -d test_db -U test_u -p 5433 -W --slot test_slot 
--create-slot -P wal2json
pg_recvlogical -d test_db -U test_u -p 5433 -W --slot test_slot --start 
-f /dev/null


The steps done to create slots and consume with pgoutput plugin are the 
following:

In PostgreSQL:
CREATE PUBLICATION dbz_minimal_publication FOR TABLE public.xxx;

And then from the shell:
pg_recvlogical -d test_db --slot pgoutput_minimal_test_slot 
--create-slot -P pgoutput
pg_recvlogical -d test_db -U test_u -p 5433 -W --slot 
pgoutput_minimal_test_slot --start -h localhost -o proto_version=1 -o 
publication_names='dbz_minimal_publication' -f /dev/null



A few minutes after creating these slots (Thu Nov  5 12:55:45 2020)
Every 5.0s: ps eo user,pid,vsz,rss $(pgrep -f walsender)
USER   PIDVSZ   RSS
postgres  3080 11672352 916664  <- this is the walsender using wal2json 
plugin
postgres  5740 11533412 760332 <- this is the walsender using pgoutput 
plugin

...
postgres 14333 13011864 2246328 <- those are other walsender using 
wal2json plugin

postgres 14383 13011864 2244788
postgres 14384 13011864 2246316

Same thing a few hours later: Thu Nov  5 14:01:55 2020
Every 5.0s: ps eo user,pid,vsz,rss $(pgrep -f walsender)
USER   PIDVSZ   RSS
postgres  3080 12153832 1400920 <- this is the walsender using wal2json 
plugin
postgres  5740 12014716 1245524 <- this is the walsender using pgoutput 
plugin

...
postgres 14333 13493032 2728520
postgres 14383 13493032 2727012
postgres 14384 13493032 2728672

Per wal2json author suggestion and based on other posts on this list we 
attached gdb to one of the walsender process a few weeks ago and did the 
following:

p (long)MemoryContextStats((long)TopMemoryContext)

It seems we have a lot of ReorderBufferToastHash under the Logical 
decoding context:
TopMemoryContext: 435000 total in 9 blocks; 29864 free (17 chunks); 
405136 used
  Type information cache: 24352 total in 2 blocks; 2624 free (0 chunks); 
21728 used
  pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 1456 
free (0 chunks); 6736 used
  wal2json output context: 8192 total in 1 blocks; 7936 free (0 chunks); 
256 used
  RowDescriptionContext: 8192 total in 1 blocks; 6896 free (0 chunks); 
1296 used
  MessageContext: 8192 total in 1 blocks; 6896 free (1 chunks); 1296 
used
Replication command context: 82104 total in 5 blocks; 25848 free (12 
chunks); 56256 used
  Logical decoding context: 344176 total in 5 blocks; 26672 free (12 
chunks); 317504 used
snapshot builder context: 524288 total in 7 blocks; 172928 free 
(24 chunks); 351360 used
ReorderBuffer: 32768 total in 3 blocks; 28048 free (26 chunks); 
4720 used
  ReorderBufferToastHash: 524288 total in 7 blocks; 28784 free 
(22 chunks); 495504 used
  ReorderBufferToastHash: 524288 total in 7 blocks; 28784 free 
(22 chunks); 495504 used
  ReorderBufferToastHash: 524288 total in 7 blocks; 28784 free 
(22 chunks); 495504 used
  ReorderBufferToastHash: 524288 total in 7 blocks; 28784 free 
(22 chunks); 495504 used

...
   ReorderBufferByXid: 16384 total in 2 blocks; 3544 free (2 
chunks); 12840 used
  Tuples: 2080374888 total in 248 blocks (1259603 chunks); 
241400 free (265927 chunks); 2080133488 used

  TXN: 648 total in 0 blocks; 0 free (0 chunks); 648 used
  Change: 79881960 total in 9751 blocks; 74080 free (926 
chunks); 79807880 used
  Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 
7632 used


The full output can be found on github[2]

Please note that all of this seems to be related to the DB activity, 
simply doing a test with for example a pgbench in loop does not allow to 
reproduce this "leak".


Would someone point me in the right direction to identify what could be 
the reason for this behaviour so that we can try to fix this?


Thanks,
Raph
[1] https://github.com/eulerto/wal2json/issues/180
[2] https://github.com/eulerto/wal2json/files/5292603/memory.txt

Re: Fwd: JSONB order?

[Adrian Klaver]

On 11/5/20 7:45 AM, Tony Shelver wrote:



-- Forwarded message -
From: *Tony Shelver* mailto:tshel...@gmail.com>>
Date: Thu, 5 Nov 2020 at 17:45
Subject: Re: JSONB order?
To: Christophe Pettus mailto:x...@thebuild.com>>


Thanks Christophe, that's what I thought.
Just seemed weird that they were 'disordered' in exactly the same way 
every time.


Probably because that resolves to the most efficient way to store in 
jsonb for that particular record.




FYI, as of Python 3.7, dicts /_are_/ ordered.


By insertion order so updating a dict with a new item will add new key 
to end.




The problem is that we are possibly going to have many versions of these 
forms with slightly differing keys, which will be a pain to order in 
some hard coded way.




On Thu, 5 Nov 2020 at 17:40, Christophe Pettus > wrote:




 > On Nov 5, 2020, at 07:34, Tony Shelver mailto:tshel...@gmail.com>> wrote:
 > But...  seen above, the order gets mixed up.
 >
 > Any ideas?

JSON objects, like Python dicts, are not automatically ordered by
key.  Once you move from the column space to the JSON object space,
you can't rely on the object keys being in a consistent order.

You'll want to have a step when ingesting the JSON object into a
report that lines up the key values appropriately with the right
presentation in the report.
--
-- Christophe Pettus
x...@thebuild.com 




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

how to check that recovery is complete

[Dmitry O Litvintsev]

Hi, 

I have a workflow where I recover from PITR backup and run a query on it. The 
program that runs query 
checks that it can connect to database in a loop, until it can, and then runs 
the query. 
This has worked fine far. Recently I upgraded to 11 and I see that I can 
connect to DB while recovery is 
not complete yet. See this:

< 2020-11-05 03:34:36.114 CST  >LOG:  starting archive recovery
< 2020-11-05 03:34:36.590 CST  >LOG:  restored log file 
"000102EF00F9" from archive
< 2020-11-05 03:34:36.641 CST  >LOG:  redo starts at 2EF/F928
...
< 2020-11-05 03:34:46.392 CST  >LOG:  restored log file 
"000102F8" from archive
< 2020-11-05 03:34:46.658 CST 127.0.0.1 >FATAL:  the database system is 
starting up
< 2020-11-05 03:34:47.028 CST  >LOG:  restored log file 
"000102F9" from archive

You can see above fail to connect , but sometime into recover I see;

< 2020-11-05 04:07:51.987 CST  >LOG:  restored log file 
"000102F20029" from archive
< 2020-11-05 04:08:23.195 CST 127.0.0.1 >ERROR:  canceling statement due to 
conflict with recovery
< 2020-11-05 04:08:23.195 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:23.195 CST 127.0.0.1 >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:23.195 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:23.195 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:23.195 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:23.195 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:23.232 CST  >LOG:  background worker "parallel worker" (PID 
13577) exited with exit code 1
< 2020-11-05 04:08:23.244 CST  >LOG:  background worker "parallel worker" (PID 
13578) exited with exit code 1
< 2020-11-05 04:08:23.244 CST 127.0.0.1 >FATAL:  terminating connection due to 
conflict with recovery
< 2020-11-05 04:08:23.244 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:23.244 CST 127.0.0.1 >HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
< 2020-11-05 04:08:25.354 CST  >LOG:  restored log file 
"000102F2002A" from archive
< 2020-11-05 04:08:55.555 CST 127.0.0.1 >ERROR:  canceling statement due to 
conflict with recovery
< 2020-11-05 04:08:55.555 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:55.555 CST 127.0.0.1 >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:55.556 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:55.556 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:55.561 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:55.561 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:55.640 CST  >LOG:  background worker "parallel worker" (PID 
13683) exited with exit code 1
< 2020-11-05 04:08:55.653 CST  >LOG:  background worker "parallel worker" (PID 
13684) exited with exit code 1
< 2020-11-05 04:08:55.653 CST 127.0.0.1 >FATAL:  terminating connection due to 
conflict with recovery
< 2020-11-05 04:08:55.653 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:55.653 CST 127.0.0.1 >HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
< 2020-11-05 04:09:00.307 CST  >LOG:  restored log file 
"000102F2002B" from archive

As you can see a query "select count(*) from file" failed due to table not 
being restored yet. BUT connection was allowed 
before DB was ready Only few hours after ;

< 2020-11-05 09:31:30.319 CST  >LOG:  archive recovery complete
< 2020-11-05 09:34:51.729 CST  >LOG:  database system is ready to accept 
connections

After which the query runs fine without errors. This is bad because I see that 
select count(*) takes progressively 
longer to execute and the count(*) is not what I am interested in. I run the 
"real" query after that. As a result I add
hours to program execution time. 

Is there a more robust method to it? Ideally I do not want to be able to 
connect to db until :

< 2020-11-05 09:31:30.319 CST  >LOG:  archive recovery complete
< 2020-11-05 09:34:51.729 CST  >LOG:  database system is ready to accept 
connections


And I believe this was the behavior before upgrade. If connection can't be 
disabled, how can I detect 
condition "database system is ready to accept connections"

I believe "pg_isready" utility would succeed once it can connect. And as can 
see I could 
connect way before DB is really ready. 

Thanks!
Dmitry

pgagent

[Gabi Draghici]

Hi,

I have installed postgresql 12 on sles 15 for some tests. Now I'm
interested in some sort of scheduler and from what I've read so far,
pgagent should do the job. So I've installed pgagent 4.0. I've added a job
(which I can see in pgagent.pga_job) but everytime I ran it (from pgadmin)
nothing happens ! When I start the pgagent I can see "... pgagent ...
connection authorized" in the main log. What else should I check ?

Thanks,
Gabi

Re: pgagent

[Adrian Klaver]

On 11/5/20 1:07 PM, Gabi Draghici wrote:


Hi,

I have installed postgresql 12 on sles 15 for some tests. Now I'm 
interested in some sort of scheduler and from what I've read so far, 
pgagent should do the job. So I've installed pgagent 4.0. I've added a 
job (which I can see in pgagent.pga_job) but everytime I ran it (from 
pgadmin) nothing happens ! When I start the pgagent I can see "... 
pgagent ... connection authorized" in the main log. What else should I 
check ?


pg_cron:

https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/

For pgagent what is the job doing and when? Could it be it hasn't run 
because it has not reached it's scheduled time.




Thanks,
Gabi





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

Re: PANIC: could not write to log file {} at offset {}, length {}: Invalid argument

[Michael Paquier]

On Thu, Nov 05, 2020 at 10:21:40AM +0100, Magnus Hagander wrote:
> The problem with AVs generally doesn't come from them opening files in
> non-share mode (I've, surprisingly enough, seen backup software that
> causes that problem for example). It might happen on scheduled scans
> for example, but the bigger problem with AV software has always been
> their filter driver software which intercepts both the open/close and
> the read/write calls an application makes and "does it's magic" on
> them before handing the actual call up to the operating system. It's
> completely independent of how the file is opened.

This one is a bit new to me.  I certainly saw my share of stat() or
open() calls failing on ENOPERM because of file handles taken
exclusively by external scanners around here or even with
customer-related issues, and I did not expect that such dark magic
could be involved in a write.  It would indeed not be surprising to
see a PANIC depending on what gets done.
--
Michael


signature.asc
Description: PGP signature

Re: PANIC: could not write to log file {} at offset {}, length {}: Invalid argument

[Michael Paquier]

On Wed, Nov 04, 2020 at 10:23:04PM -0500, Tom Lane wrote:
> The latter case would result in a LOG message "unrecognized win32 error
> code", so it would be good to know if any of those are showing up in
> the postmaster log.

Yeah.  Not sure which one it could be here:
https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-writefile

One possibility could also be ERROR_OPERATION_ABORTED, which is not in
the mapping table.  So that would map to EINVAL.

> Seems like maybe it wasn't a great idea for _dosmaperr's fallback
> errno to be something that is also a real error code.

You could say that for any fallback errno as long as you don't know if
there's a LOG to show that a DWORD does not map with the table of
win32error.c, no?

(I got to wonder whether it would be worth the complexity to show more
information when using _dosmaperr() for WIN32 on stuff like 
elog(ERROR, "%m"), just a wild thought).
--
Michael


signature.asc
Description: PGP signature

Re: PANIC: could not write to log file {} at offset {}, length {}: Invalid argument

[Tom Lane]

Michael Paquier  writes:
> (I got to wonder whether it would be worth the complexity to show more
> information when using _dosmaperr() for WIN32 on stuff like 
> elog(ERROR, "%m"), just a wild thought).

Maybe.  It's been in the back of my mind for a long time that the
_dosmaperr() mapping may be confusing us in some of these hard-to-explain
trouble reports.  It'd be great if we could see the original Windows error
code too.  Not quite sure how to mechanize that, though.  Places where we
do stuff like save-and-restore errno across some other operation would break
any easy solution.

regards, tom lane

Re: Christopher Browne

[Robert Treat]

On Wed, Nov 4, 2020 at 6:29 PM Steve Singer  wrote:
>
>
> It is with much sadness that I am letting the community know that Chris
> Browne passed away recently.
>
> Chris had been a long time community member and was active on various
> Postgresql mailing lists.  He was a member of the funds committee, the PgCon
> program committee and served on the board of the PostgreSQL Community
> Association of Canada. Chris was a maintainer of the Slony
> replication system and worked on various PG related tools.
>
> I worked with Chris for over 10 years and anytime someone had a problem he
> would jump at the chance to help and propose solutions. He
> always had time to listen to your problem and offer ideas or explain how
> something worked.
>
> I will miss Chris
>

Chris is one of the folks whom I got to meet online very early in my
time with the project, who was there in Toronto at the first
developer's conference [1], and was one of the folks I always looked
forward to seeing in Ottawa, even if it was just to quickly catch-up.
I suspect his contributions to the project are understated, but a lot
of time, troubleshooting, and tutoring came from Chris to many others
of us around Slony (back when Slony was the main game in town) and
around plpgsql. Of course, he knew his way around an OS just as well
as he knew Postgres.

I think my fondest memory of Chris was one, many years back, when I
just happened to be in Toronto for non-postgres related business, and
we coordinated to meet up for a quick drink and to catch-up. He was
kind enough to offer me an invitation to a private "computer users"
dinner that he often frequented at a hole-in-the-wall Hungarian place.
I'm always game for a bit of adventure and I'm so glad I was because
ended up being a truly special night, learning much more about Chris
away from the database stuff, with a bunch of great food (still don't
know half of what it was), and the realization that this small group
of friends included several luminaries in the computer science field,
(as one example Henry Spencer, who wrote the "regex" software library
for regular expressions), who I can't imagine ever having the
opportunity to meet otherwise.

Most of us are not nearly as open and as kind as he was, and he will
indeed be missed.

[1] https://ic.pics.livejournal.com/obartunov/24248903/36575/36575_original.jpg,
he is the one in the back, holding up the sign.


Robert Treat
https://xzilla.net

Re: JSONB order?

[Tony Shelver]

On Thu, 5 Nov 2020 at 18:27, Rob Sargent  wrote:

>
>
> On Nov 5, 2020, at 8:45 AM, Tony Shelver  wrote:
>
>
>
> -- Forwarded message -
> From: Tony Shelver 
> Date: Thu, 5 Nov 2020 at 17:45
> Subject: Re: JSONB order?
> To: Christophe Pettus 
>
>
> Thanks Christophe, that's what I thought.
> Just seemed weird that they were 'disordered' in exactly the same way
> every time.
>
> FYI, as of Python 3.7, dicts *are* ordered.
>
> The problem is that we are possibly going to have many versions of these
> forms with slightly differing keys, which will be a pain to order in some
> hard coded way.
>
>
> Sounds like you’ll need a separate mechanism for maintaining versions of
> the forms and which headers represent the same data concept.  Always access
> data via canonical header translated to current form.
>

Did a workaround.  For what I needed, I used the python dict to json
function, which creates a string, and then stored that string in a varchar
column on the DB, leaving the json in place for other use.

For reporting, pulled the varchar back and used the json to dict function
on python.  Minimal code changes required.

The output is formatted into an Excel spreadsheet, writing the column
header / title from the dict keys, and then formatting the values
underneath.


Not exactly 3rd normal form and all the other best practices, but this is a
hard prototype we have some customers for, so ease of being able to iterate
multiple forms and changes is key.