Linux Update Experience

[Zwettler Markus (OIZ)]

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30
+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
   Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?

Thanks,
Markus

Re: Linux Update Experience

[Jiří Fejfar]

On 28.05.2020 9:59, Zwettler Markus (OIZ) wrote:


We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum 
update).


We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30

+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing 
requirements:


Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)


Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating 
anymore?


Thanks,

Markus


Hi Markus,

we are using PGDG on Debian / Ubuntu trying to update Linux as frequent 
as possible. I do not remember many issues if any. I would not expect:


* problems between postgres minor version upgrades (12.2 -> 12.3). We 
have PG 12.3 & PostGIS 3.0 on Ubuntu Bionic and do not remember any 
update problems.


* major upgrades when keeping same Linux distribution version. Our older 
Ubuntu Xenial keeps PG 9.6 & PostGIS 2.3.


So from my perspective: linux updates should not trigger major DB 
upgrades (or postGis), minor DB upgrades should be smooth -> it is 
possible to update Linux continuously. Major DB upgrades have so be 
triggered manually (on same Linux distro version or newer). On that 
Ubuntu Bionic server, we started on PG10 upgrading DB together with 
PostGis every year with dump / restore procedure to fresh new DB.


--

Jiří Fejfar

AW: Linux Update Experience

[Marco Lechner]

Hi Markus,

at the moment we are facing similar conflicts on Oracle LInux 7 (wich is 
derived from RHEL) - we manage our machines using Spacewalk. The conflicts 
occur (as expected) on Spacewalk as well as on manually using yum:

Fehler: Paket: postgresql11-devel-11.8-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql11)
Benötigt: llvm-toolset-7-clang >= 4.0.1
Fehler: Paket: postgis30_12-3.0.1-5.rhel7.x86_64 (oraclelinux7postgresql12)
Benötigt: proj70 >= 7.0.1
Installiert: proj70-7.0.0-2.rhel7.x86_64 (@oraclelinux7postgresql11)
proj70 = 7.0.0-2.rhel7
Verfügbar: proj70-7.0.0-1.rhel7.x86_64 (oraclelinux7postgresql11)
proj70 = 7.0.0-1.rhel7
Fehler: Paket: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql12)
Benötigt: llvm-toolset-7-clang >= 4.0.1

Marco


--
Dr. Marco Lechner
Bundesamt fuer Strahlenschutz / Federal Office for Radiation Protection
RN Radiologischer Notfallschutz / Radiological Emergency Preparedness and 
Response
RN1 Koordination Notfallschutzsysteme / Coordination Emergency Systems
Rosastrasse 9 | D-79098 Freiburg | Germany
mlech...@bfs.de | +49 (0)3018 333 6724 | 
www.bfs.de

Hinweis zu Anhängen die auf .p7m/.p7c/.p7s oder .asc/.asc.sig enden:
Die .p7?- und .asc-Dateien sind ungefährliche Signaturdateien (digitale 
Unterschriften). In E-Mail-Clients mit S/MIME Konfiguration (.p7?) oder 
PGP-Erweiterung (.asc) dienen sie zur:
- Überprüfung des Absenders
- Überprüfung einer evtl. Veränderung des Inhalts während der Übermittlung über 
das Internet
Die Signaturdateien können ebenso dazu verwendet werden dem Absender dieser 
Signatur eine E-Mail mit verschlüsseltem Inhalt zu senden. In E-Mail-Clients 
ohne S/MIME Konfiguration oder PGP-Erweiterung erscheinen die Dateien als 
Anhang und können ignoriert werden.



Von: Zwettler Markus (OIZ) [mailto:markus.zwett...@zuerich.ch]
Gesendet: Donnerstag, 28. Mai 2020 09:59
An: PostgreSQL General 
Betreff: Linux Update Experience

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30
+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
   Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?

Thanks,
Markus

Re: Suggestion to Monitoring Tool

[Stéphane KANSCHINE]

Hi !

Le mer. 27 mai, vers 22:15, postgann2020 s exprimait :
> 
> Thanks for your support.
> 
> Environment Details:
> OS: RHEL 7.2
> Postgres: 9.5.15
> Master-Slave with Streaming replication
> 
> We are planning to implement the monitoring tool for our environment.
> 
> Could someone please suggest the Monitoring Tool based on your experience.

Last year, i setup prometheus with :
- postgres exporter for generic metrics
- some custom queries via postgres exporter to compute replication lag
  and few other things
- script exporter to explore logs and other things
- node exporter for disk filling.
- alertmanager to send slack notifications
- grafana dashboards for a nice view
- kibana / sentry manage logs and used as datasource for prometheus.

Regards,
-- 
Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
＠ steph...@hexack.fr
  +33 6 64 31 72 52

Re: AW: Linux Update Experience

[Alan Hodgson]

On Thu, 2020-05-28 at 09:00 +, Marco Lechner wrote:
> Hi Markus,
>  
> at the moment we are facing similar conflicts on Oracle LInux 7 (wich
> is derived from RHEL) – we manage our machines using Spacewalk. The
> conflicts occur (as expected) on Spacewalk as well as on manually
> using yum:
>  
> Fehler: Paket: postgresql11-devel-11.8-1PGDG.rhel7.x86_64
> (oraclelinux7postgresql11)
> Benötigt: llvm-toolset-7-clang >= 4.0.1

FWIW, postgresql-devel can't be updated on CentOS 7 currently either.
The 12.2 packages were fine but I have not been able to update to 12.3.

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 (pg12)
   Requires: llvm-toolset-7-clang >= 4.0.1

That llvm-toolset-7-clang dependency is not present in the CentOS, EPEL
or PostgreSQL repos.

AW: Linux Update Experience

[Zwettler Markus (OIZ)]

Hi Marco,

How do you handle these conflicts? No longer updating that regularly or not at 
all anymore?

Thanks,
Markus




Von: Marco Lechner 
Gesendet: Donnerstag, 28. Mai 2020 11:01
An: Zwettler Markus (OIZ) ; PostgreSQL General 

Betreff: AW: Linux Update Experience

Hi Markus,

at the moment we are facing similar conflicts on Oracle LInux 7 (wich is 
derived from RHEL) - we manage our machines using Spacewalk. The conflicts 
occur (as expected) on Spacewalk as well as on manually using yum:

Fehler: Paket: postgresql11-devel-11.8-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql11)
Benötigt: llvm-toolset-7-clang >= 4.0.1
Fehler: Paket: postgis30_12-3.0.1-5.rhel7.x86_64 (oraclelinux7postgresql12)
Benötigt: proj70 >= 7.0.1
Installiert: proj70-7.0.0-2.rhel7.x86_64 (@oraclelinux7postgresql11)
proj70 = 7.0.0-2.rhel7
Verfügbar: proj70-7.0.0-1.rhel7.x86_64 (oraclelinux7postgresql11)
proj70 = 7.0.0-1.rhel7
Fehler: Paket: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql12)
Benötigt: llvm-toolset-7-clang >= 4.0.1

Marco


--
Dr. Marco Lechner
Bundesamt fuer Strahlenschutz / Federal Office for Radiation Protection
RN Radiologischer Notfallschutz / Radiological Emergency Preparedness and 
Response
RN1 Koordination Notfallschutzsysteme / Coordination Emergency Systems
Rosastrasse 9 | D-79098 Freiburg | Germany
mlech...@bfs.de | +49 (0)3018 333 6724 | 
www.bfs.de

Hinweis zu Anhängen die auf .p7m/.p7c/.p7s oder .asc/.asc.sig enden:
Die .p7?- und .asc-Dateien sind ungefährliche Signaturdateien (digitale 
Unterschriften). In E-Mail-Clients mit S/MIME Konfiguration (.p7?) oder 
PGP-Erweiterung (.asc) dienen sie zur:
- Überprüfung des Absenders
- Überprüfung einer evtl. Veränderung des Inhalts während der Übermittlung über 
das Internet
Die Signaturdateien können ebenso dazu verwendet werden dem Absender dieser 
Signatur eine E-Mail mit verschlüsseltem Inhalt zu senden. In E-Mail-Clients 
ohne S/MIME Konfiguration oder PGP-Erweiterung erscheinen die Dateien als 
Anhang und können ignoriert werden.



Von: Zwettler Markus (OIZ) [mailto:markus.zwett...@zuerich.ch]
Gesendet: Donnerstag, 28. Mai 2020 09:59
An: PostgreSQL General 
mailto:pgsql-general@lists.postgresql.org>>
Betreff: Linux Update Experience

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30
+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
   Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?

Thanks,
Markus

Order by lower(column-alias) doesn't work...

[Andreas Joseph Krogh]

Hi. 

This works: 
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from 
onp_crm_person p order by fullname;

But this doesn't: 
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from 
onp_crm_person p order by lower(fullname);
ERROR: column "fullname" does not exist 
 LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);

This is just an example-query, in my real query I have a more complex query 
generating an array of a custom-type which is then referenced to as a 
column-alias, and then ORDER BY on a function tranforming this array doesn't 
work: 

SELECT ... 
ARRAY(WITH RECURSIVE t AS (SELECT 
...
) SELECT ROW(t.entity_id, t.name)::BigIntVarChar from t order by level DESC) 
as folder_parent_array


ORDER BY bigintvarchar_to_text_value_flatten(folder_parent_array) ASC; 
column "folder_parent_array" does not exist 

What bigintvarchar_to_text_value_flatten() does is to take the "varchar"-part 
out of the BigintVarchar-type and "flatten" the array by that value so that it 
sorts nicely. 

 Any way round this? 


--
 Andreas Joseph Krogh 

AW: Linux Update Experience

[Marco Lechner]

Dear Markus,

we are doing Updates almost continously/daily. The dependeny problems occur 
since a few days/1-2 weeks (?).
The Updates are pending since then - hoping for package maintainers to fix 
this, but did not yet address this in Bugtracker.

Marco

Von: Zwettler Markus (OIZ) [mailto:markus.zwett...@zuerich.ch]
Gesendet: Donnerstag, 28. Mai 2020 13:42
An: Marco Lechner ; PostgreSQL General 

Betreff: AW: Linux Update Experience

Hi Marco,

How do you handle these conflicts? No longer updating that regularly or not at 
all anymore?

Thanks,
Markus




Von: Marco Lechner mailto:mlech...@bfs.de>>
Gesendet: Donnerstag, 28. Mai 2020 11:01
An: Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>>; PostgreSQL 
General 
mailto:pgsql-general@lists.postgresql.org>>
Betreff: AW: Linux Update Experience

Hi Markus,

at the moment we are facing similar conflicts on Oracle LInux 7 (wich is 
derived from RHEL) - we manage our machines using Spacewalk. The conflicts 
occur (as expected) on Spacewalk as well as on manually using yum:

Fehler: Paket: postgresql11-devel-11.8-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql11)
Benötigt: llvm-toolset-7-clang >= 4.0.1
Fehler: Paket: postgis30_12-3.0.1-5.rhel7.x86_64 (oraclelinux7postgresql12)
Benötigt: proj70 >= 7.0.1
Installiert: proj70-7.0.0-2.rhel7.x86_64 (@oraclelinux7postgresql11)
proj70 = 7.0.0-2.rhel7
Verfügbar: proj70-7.0.0-1.rhel7.x86_64 (oraclelinux7postgresql11)
proj70 = 7.0.0-1.rhel7
Fehler: Paket: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql12)
Benötigt: llvm-toolset-7-clang >= 4.0.1

Marco


--
Dr. Marco Lechner
Bundesamt fuer Strahlenschutz / Federal Office for Radiation Protection
RN Radiologischer Notfallschutz / Radiological Emergency Preparedness and 
Response
RN1 Koordination Notfallschutzsysteme / Coordination Emergency Systems
Rosastrasse 9 | D-79098 Freiburg | Germany
mlech...@bfs.de | +49 (0)3018 333 6724 | 
www.bfs.de

Hinweis zu Anhängen die auf .p7m/.p7c/.p7s oder .asc/.asc.sig enden:
Die .p7?- und .asc-Dateien sind ungefährliche Signaturdateien (digitale 
Unterschriften). In E-Mail-Clients mit S/MIME Konfiguration (.p7?) oder 
PGP-Erweiterung (.asc) dienen sie zur:
- Überprüfung des Absenders
- Überprüfung einer evtl. Veränderung des Inhalts während der Übermittlung über 
das Internet
Die Signaturdateien können ebenso dazu verwendet werden dem Absender dieser 
Signatur eine E-Mail mit verschlüsseltem Inhalt zu senden. In E-Mail-Clients 
ohne S/MIME Konfiguration oder PGP-Erweiterung erscheinen die Dateien als 
Anhang und können ignoriert werden.



Von: Zwettler Markus (OIZ) [mailto:markus.zwett...@zuerich.ch]
Gesendet: Donnerstag, 28. Mai 2020 09:59
An: PostgreSQL General 
mailto:pgsql-general@lists.postgresql.org>>
Betreff: Linux Update Experience

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30
+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
   Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?

Thanks,
Markus

Re: Order by lower(column-alias) doesn't work...

[Geoff Winkless]

On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh  wrote:
> This works:
> select p.firstname, p.lastname, p.firstname || p.lastname as fullname from 
> onp_crm_person p order by fullname;
>
> But this doesn't:
> select p.firstname, p.lastname, p.firstname || p.lastname as fullname from 
> onp_crm_person p order by lower(fullname);
> ERROR:  column "fullname" does not exist
> LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);

Wrap the original query in either a CTE or a temporary table.
eg

=> SELECT REPLACE(name, '_', ' ') AS nm FROM subs ORDER BY lower(nm);
ERROR:  column "nm" does not exist
=> SELECT * FROM (SELECT REPLACE(name, '_', ' ') AS nm FROM subs) AS t
ORDER BY lower(nm);
[results]
=> WITH t AS (SELECT REPLACE(name, '_', ' ') AS nm FROM subs)   SELECT
* FROM t ORDER BY lower(nm);
[results]

Geoff

Re: Order by lower(column-alias) doesn't work...

[Andreas Joseph Krogh]

På torsdag 28. mai 2020 kl. 14:50:54, skrev Geoff Winkless mailto:pgsqlad...@geoff.dj>>: 
On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh  wrote:
 > This works:
 > select p.firstname, p.lastname, p.firstname || p.lastname as fullname from 
onp_crm_person p order by fullname;
 >
 > But this doesn't:
 > select p.firstname, p.lastname, p.firstname || p.lastname as fullname from 
onp_crm_person p order by lower(fullname);
 > ERROR: column "fullname" does not exist
 > LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);

 Wrap the original query in either a CTE or a temporary table.
 eg
 [..] 

Yea, I was hoping to avoid that, as the query is generated and rewriting it is 
a pain... 

Is there a way to define "sorting-rules" on custom-types so that I can have 
ORDER BY  and PG will pick my custom odering? 


--
 Andreas Joseph Krogh

Re: Order by lower(column-alias) doesn't work...

[Tom Lane]

Andreas Joseph Krogh  writes:
> Is there a way to define "sorting-rules" on custom-types so that I can have 
> ORDER BY  and PG will pick my custom odering? 

You'd have to write your own type, which would be a lotta work :-(.

A possible partial answer is to define the composite type as

firstname citext, lastname citext, other-fields-here

and then the regular composite-type comparison rule would give you
approximately what you said you wanted ... but only approximately.

regards, tom lane

Re: Order by lower(column-alias) doesn't work...

[Andreas Joseph Krogh]

På torsdag 28. mai 2020 kl. 15:26:42, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: 
Andreas Joseph Krogh  writes:
 > Is there a way to define "sorting-rules" on custom-types so that I can have
 > ORDER BY  and PG will pick my custom 
odering?

 You'd have to write your own type, which would be a lotta work :-(.

 A possible partial answer is to define the composite type as

 firstname citext, lastname citext, other-fields-here

 and then the regular composite-type comparison rule would give you
 approximately what you said you wanted ... but only approximately.

 regards, tom lane 


Hm, ok. I think the most non-intrusive way for me is to craft a sub-select 
producing the "varchar-string-flattened" so I can order by that alias. 


--
 Andreas Joseph Krogh

Re: Linux Update Experience

[Adrian Klaver]

On 5/28/20 12:59 AM, Zwettler Markus (OIZ) wrote:

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30

+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing 
requirements:


Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)


    Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?


See here:

https://yum.postgresql.org/news-newreporpmsreleased.php

And if you have community account:

https://redmine.postgresql.org/issues/5483

To contact the RPM packagers directly:

https://yum.postgresql.org/contact.php



Thanks,

Markus




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

AW: Linux Update Experience

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Adrian Klaver 
> Gesendet: Donnerstag, 28. Mai 2020 16:15
> An: Zwettler Markus (OIZ) ; PostgreSQL General
> 
> Betreff: Re: Linux Update Experience
> 
> On 5/28/20 12:59 AM, Zwettler Markus (OIZ) wrote:
> > We are running PGDG Postgres 9.6 and 12 on RHEL7.
> >
> > Our Linux team does global Linux updates on a quarterly basis (yum update).
> >
> > We are hitting more and more update problems.
> >
> > Some troubles this time:
> >
> > + Postgis24 has been updated to Postgis30
> >
> > + Postgres 12.2 has been updated to Postgres 12.3 claiming missing
> > requirements:
> >
> > Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64
> > (imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
> >
> >     Requires: llvm-toolset-7-clang >= 4.0.1
> >
> > Question: How to you handle your Linux update cycles? Not updating anymore?
> 
> See here:
> 
> https://yum.postgresql.org/news-newreporpmsreleased.php
> 
> And if you have community account:
> 
> https://redmine.postgresql.org/issues/5483
> 
> To contact the RPM packagers directly:
> 
> https://yum.postgresql.org/contact.php
> 
> >
> > Thanks,
> >
> > Markus
> >
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
[Zwettler Markus (OIZ)] 



Hi Adrian,

I'm not talking about this specific bug or its resolution.

I want to talk about the Linux update problem in general.

Anyone updating Linux might get such nerving dependency troubles.

How do you handle this situation? Updating more frequently? Updating less 
frequently? Not updating anymore?

Cheers,
Markus

Re: AW: Linux Update Experience

[Adrian Klaver]

On 5/28/20 7:36 AM, Zwettler Markus (OIZ) wrote:

-Ursprüngliche Nachricht-
Von: Adrian Klaver 
Gesendet: Donnerstag, 28. Mai 2020 16:15
An: Zwettler Markus (OIZ) ; PostgreSQL General

Betreff: Re: Linux Update Experience

On 5/28/20 12:59 AM, Zwettler Markus (OIZ) wrote:

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30

+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing
requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)

     Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?


See here:

https://yum.postgresql.org/news-newreporpmsreleased.php

And if you have community account:

https://redmine.postgresql.org/issues/5483

To contact the RPM packagers directly:

https://yum.postgresql.org/contact.php



Thanks,

Markus




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

[Zwettler Markus (OIZ)]



Hi Adrian,

I'm not talking about this specific bug or its resolution.

I want to talk about the Linux update problem in general.

Anyone updating Linux might get such nerving dependency troubles. >
How do you handle this situation? Updating more frequently? Updating less 
frequently? Not updating anymore?


If you are installing via packages and the package managers are doing 
there job then there should not be an issue. The dependencies should be 
taken care of. As to version changes, that depends on the software. For 
instance Postgres 12.2 --> 12.3 is a minor/bug release so it should be 
something you get. Not sure about the PostGIS upgrade, that probably 
depends on what repo(s) you are using.  The bottom line is if you ask 
for upgrades/updates you are going to get them. This means keeping track 
of what is happening with updates to your software. Now you can pin/lock 
versions, search on pinning/locking packages. The downside to that is 
missing important bug fixes. I would say not updating qualifies as a 
foot gun.




Cheers,
Markus





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

Re: Linux Update Experience

[Karsten Hilbert]

On Thu, May 28, 2020 at 02:36:34PM +, Zwettler Markus (OIZ) wrote:

> Hi Adrian,
>
> I'm not talking about this specific bug or its resolution.
>
> I want to talk about the Linux update problem in general.
>
> Anyone updating Linux might get such nerving dependency troubles.
>
> How do you handle this situation? Updating more frequently? Updating less 
> frequently? Not updating anymore?

If we ask ourselves general questions there can't be, by the
very nature of the question, any more specific answer beyond:

It depends.

Conventional wisdom holds it that updating "more frequently"
(not "accruing technical debt") helps -- the trick is to find
the balance between early effort and lag.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

psql12.3 + jdbc_fdw - return wrong query results by using OR

[emilu]

Hello,

psql12.3 + jdbc_fdw(oracle18.x), tried:

[1]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1);

return 100 records

[2]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1
OR
a.c2 = b.c1
   );

return only 2 records

(no null values in both tables.)

May I know what may cause the error please?

Thanks a lot.

Re: psql12.3 + jdbc_fdw - return wrong query results by using OR

[Adrian Klaver]

On 5/28/20 8:39 AM, em...@encs.concordia.ca wrote:

Hello,

psql12.3 + jdbc_fdw(oracle18.x), tried:

[1]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1);

return 100 records

[2]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1
     OR
     a.c2 = b.c1
    );

return only 2 records

(no null values in both tables.)

May I know what may cause the error please?


I'm guessing you are seeing this:

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

4.2.14. Expression Evaluation Rules

". Boolean expressions (AND/OR/NOT combinations) in those clauses can be 
reorganized in any manner allowed by the laws of Boolean algebra."


It would help to see the EXPLAIN ANALYZE for the queries above.



Thanks a lot.






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

Re: psql12.3 + jdbc_fdw - return wrong query results by using OR

[Adrian Klaver]

On 5/28/20 9:59 AM, Ying Lu wrote:

Hello,


would help to see the EXPLAIN ANALYZE for the queries above.


And the EXPLAIN ANALYZE for the first query?

Also please include the entire query, for example:

EXPLAIN ANALYZE select count(*) from oracle_t1 as a inner join local_t1 
as b on (a.c1 = b.c1 OR a.c2 = b.c1);


This is important because in below I see:

Filter: (yr= '2020'::text)

which I don't see in your original queries.



Please find the explain analyze info
   QUERY PLAN
--
  Nested Loop  (cost=0.00..736.49 rows=489 width=333) (actual 
time=313.495..1224.671 rows=9 loops=1)
Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
Rows Removed by Join Filter: 2106
->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=13) (actual 
time=0.016..0.029 rows=49 loops=1)
->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 width=320) 
(actual time=3.445..24.977 rows=43 loops=49)
  Filter: (yr= '2020'::text)
  Rows Removed by Filter: 255
  Planning Time: 0.532 ms
  Execution Time: 1327.697 ms

Thanks.


psql12.3 + jdbc_fdw(oracle18.x), tried:
[1]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1);

return 100 records

[2]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1
  OR
  a.c2 = b.c1
 );

return only 2 records

(no null values in both tables.)

May I know what may cause the error please?


I'm guessing you are seeing this:

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

4.2.14. Expression Evaluation Rules

". Boolean expressions (AND/OR/NOT combinations) in those clauses can be
reorganized in any manner allowed by the laws of Boolean algebra."

It would help to see the EXPLAIN ANALYZE for the queries above.

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




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

Re: psql12.3 + jdbc_fdw - return wrong query results by using OR

[Ying Lu]

Hello, 

Please find the info for both SQLs (removed yr)

For Q1: 

  QUERY PLAN
---
 Aggregate  (cost=5.40..5.41 rows=1 width=8) (actual time=1267.001..1267.001 
rows=1 loops=1)
   ->  Hash Join  (cost=2.10..4.78 rows=245 width=0) (actual 
time=0.134..1265.840 rows=2650 loops=1)
 Hash Cond: (a.c1 = b.c1)
 ->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 width=32) 
(actual time=0.026..1257.823 rows=14625 loops=1)
 ->  Hash  (cost=1.49..1.49 rows=49 width=5) (actual time=0.030..0.030 
rows=49 loops=1)
   Buckets: 1024  Batches: 1  Memory Usage: 10kB
   ->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=5) 
(actual time=0.014..0.021 rows=49 loops=1)
 Planning Time: 0.178 ms
 Execution Time: 1363.482 ms


For Q2: 

 QUERY PLAN

 Aggregate  (cost=737.71..737.72 rows=1 width=8) (actual 
time=1197.366..1197.366 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..736.49 rows=489 width=0) (actual 
time=16.649..1197.292 rows=70 loops=1)
 Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
 Rows Removed by Join Filter: 14555
 ->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=5) (actual 
time=0.016..0.023 rows=49 loops=1)
 ->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 width=64) 
(actual time=0.002..24.284 rows=298 loops=49)
 Planning Time: 0.972 ms
 Execution Time: 1299.896 ms


Thanks a lot.

Re: psql12.3 + jdbc_fdw - return wrong query results by using OR

[Tom Lane]

Ying Lu  writes:
> For Q1: 

>   QUERY PLAN
> ---
>  Aggregate  (cost=5.40..5.41 rows=1 width=8) (actual time=1267.001..1267.001 
> rows=1 loops=1)
>->  Hash Join  (cost=2.10..4.78 rows=245 width=0) (actual 
> time=0.134..1265.840 rows=2650 loops=1)
>  Hash Cond: (a.c1 = b.c1)
>  ->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 
> width=32) (actual time=0.026..1257.823 rows=14625 loops=1)
>  ->  Hash  (cost=1.49..1.49 rows=49 width=5) (actual 
> time=0.030..0.030 rows=49 loops=1)
>Buckets: 1024  Batches: 1  Memory Usage: 10kB
>->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=5) 
> (actual time=0.014..0.021 rows=49 loops=1)
>  Planning Time: 0.178 ms
>  Execution Time: 1363.482 ms

> For Q2: 

>  QUERY PLAN
> 
>  Aggregate  (cost=737.71..737.72 rows=1 width=8) (actual 
> time=1197.366..1197.366 rows=1 loops=1)
>->  Nested Loop  (cost=0.00..736.49 rows=489 width=0) (actual 
> time=16.649..1197.292 rows=70 loops=1)
>  Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
>  Rows Removed by Join Filter: 14555
>  ->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=5) 
> (actual time=0.016..0.023 rows=49 loops=1)
>  ->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 
> width=64) (actual time=0.002..24.284 rows=298 loops=49)
>  Planning Time: 0.972 ms
>  Execution Time: 1299.896 ms

The numbers here are consistent with the theory that there are 14625 rows
in the foreign table, but when oracle_t1 is scanned on the inside of a
nest loop, the FDW returns all of them on the first scan and then forgets
to return any when rescanned.  This'd be a bug in jdbc_fdw, and a
pretty bad one :-(.  But you'd have to report it to the jdbc_fdw
author(s) --- the core Postgres project doesn't maintain that.

regards, tom lane

Re: AW: Linux Update Experience

[Tim Cross]

Zwettler Markus (OIZ)  writes:

> Hi Marco,
>
>  
>
> How do you handle these conflicts? No longer updating that regularly or not 
> at all anymore?
>

Not doing the updates is a poor option due to the potential security
vulnerabilities this may lead to. Likewise, delaying the application of
updates is going to increase risks as well. In fact, we have found such
approaches can make the situation worse. Delaying updates tends to
result in more updates being applied at once, which makes it harder to
identify problems when they do occur.

I think the best approach is to apply updates as soon as possible. Apply
the updates to a test or uat environment (or your dev environment if you
don't have a test/uat/staging one). If there are issues, resolve them
before applying the updates in prod.

We have found it rare for updates to be an issue if your running the
packages from the distribution. Problems seem to be more common when
your running packages sourced from an external repository which may lag
behind changes made by the distribution. When issues do occur, we look
at the type of update e.g. security, bug fix, bump in dependency
versions, new version etc and make a call as to the priority and respond
accordingly. This may mean delaying applying the update, actively
working to resolve the issue with investigations and debugging, raising
an issue/bug with the package maintainers etc.

We also classify all our systems, services, databases etc according to
whether they are core business processes or supporting processes. We
apply updates to supporting systems before core systems. This also
affects when we apply updates. For example, we would not apply updates
to a core system on Friday afternoon. In fact, we may apply updates to
core systems outside main business hours. If issues are encountered when
applying updates to core systems, resolution of those issues are highest
priority. For secondary systems, we are more likely to do the updates
during business hours, will accept longer outages/down times and may not
make resolution of issues the highest priority. 

Re: GPG signing

[Marc Munro]

On Wed, 2020-05-27 at 14:42 -0700, Michel Pelletier wrote:
> Hi Marc,
> 
> You can sign content with pgsodium:
> 
> https://github.com/michelp/pgsodium

Michel,
Yay!  A modern crypto implementation.  And it seems to do most of what
I need right out of the box with way less work than pgcrypto.

Any chance that crypto_sign_detatched() and
crypto_sign_verify_detatched() will be implemented soon?

I'll implement it and provide a patch if you'd like.

__
Marc

Re: GPG signing

[Michel Pelletier]

On Thu, May 28, 2020 at 5:14 PM Marc Munro  wrote:

> On Wed, 2020-05-27 at 14:42 -0700, Michel Pelletier wrote:
> > Hi Marc,
> >
> > You can sign content with pgsodium:
> >
> > https://github.com/michelp/pgsodium
>
> Michel,
> Yay!  A modern crypto implementation.  And it seems to do most of what
> I need right out of the box with way less work than pgcrypto.
>
> Any chance that crypto_sign_detatched() and
> crypto_sign_verify_detatched() will be implemented soon?
>
> I'll implement it and provide a patch if you'd like.
>

Yes please!  Always happy to merge a PR.

-Michel


> __
> Marc
>
>
>

Re: psql12.3 + jdbc_fdw - return wrong query results by using OR

[Laurenz Albe]

On Thu, 2020-05-28 at 11:39 -0400, em...@encs.concordia.ca wrote:
> psql12.3 + jdbc_fdw(oracle18.x), tried:

[got bad query results]

How about giving oracle_fdw a try?

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