PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Ian Dauncey]

Morning all

Currently we are running PostgreSQL 12.2 on Ubuntu 20.04.1 LTS (Kernel: Linux 
5.4.0-42-generic - Architecture: s390x).

We are looking at upgrading to a later version of PostgreSQL

My question is :- What is the latest available version of PostgreSQL that will 
run on the above Architecture.

Regards
Ian

Disclaimer

The information contained in this communication from the sender is 
confidential. It is intended solely for use by the recipient and others 
authorized to receive it. If you are not the recipient, you are hereby notified 
that any disclosure, copying, distribution or taking action in relation of the 
contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been 
automatically archived by Mimecast, a leader in email security and cyber 
resilience. Mimecast integrates email defenses with brand protection, security 
awareness training, web security, compliance and other essential capabilities. 
Mimecast helps protect large and small organizations from malicious activity, 
human error and technology failure; and to lead the movement toward building a 
more resilient world. To find out more, visit our website.

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Ray O'Donnell]

On 15 June 2022 08:31:42 Ian Dauncey  wrote:

Morning all



Currently we are running PostgreSQL 12.2 on Ubuntu 20.04.1 LTS (Kernel: 
Linux 5.4.0-42-generic - Architecture: s390x).




We are looking at upgrading to a later version of PostgreSQL



My question is :- What is the latest available version of PostgreSQL that 
will run on the above Architecture.


Here you go:

   https://wiki.postgresql.org/wiki/Apt

Ray.







Regards

Ian





Disclaimer

The information contained in this communication from the sender is 
confidential. It is intended solely for use by the recipient and others 
authorized to receive it. If you are not the recipient, you are hereby 
notified that any disclosure, copying, distribution or taking action in 
relation of the contents of this information is strictly prohibited and may 
be unlawful.


This email has been scanned for viruses and malware, and may have been 
automatically archived by Mimecast, a leader in email security and cyber 
resilience. Mimecast integrates email defenses with brand protection, 
security awareness training, web security, compliance and other essential 
capabilities. Mimecast helps protect large and small organizations from 
malicious activity, human error and technology failure; and to lead the 
movement toward building a more resilient world. To find out more, visit 
our website.

RE: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Ian Dauncey]

According to the apt list for PostgreSQL there is nothing for the s390 system 
for version 13 and 14.

# sudo apt list postgresql-14 -a
Listing... Done

# sudo apt list postgresql-13 -a
Listing... Done

But for version 12 there is

# sudo apt list postgresql-12 -a
Listing... Done
postgresql-12/focal-updates,focal-security,now 12.11-0ubuntu0.20.04.1 s390x 
[installed]
postgresql-12/focal 12.2-4 s390x

Regards
Ian

From: Ray O'Donnell 
Sent: Wednesday, 15 June 2022 10:12
To: Ian Dauncey ; pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

External email - treat with caution
On 15 June 2022 08:31:42 Ian Dauncey 
mailto:ian.daun...@bankzero.co.za>> wrote:
Morning all

Currently we are running PostgreSQL 12.2 on Ubuntu 20.04.1 LTS (Kernel: Linux 
5.4.0-42-generic - Architecture: s390x).

We are looking at upgrading to a later version of PostgreSQL

My question is :- What is the latest available version of PostgreSQL that will 
run on the above Architecture.

Here you go:

https://wiki.postgresql.org/wiki/Apt

Ray.





Regards
Ian


Disclaimer

The information contained in this communication from the sender is 
confidential. It is intended solely for use by the recipient and others 
authorized to receive it. If you are not the recipient, you are hereby notified 
that any disclosure, copying, distribution or taking action in relation of the 
contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been 
automatically archived by Mimecast, a leader in email security and cyber 
resilience. Mimecast integrates email defenses with brand protection, security 
awareness training, web security, compliance and other essential capabilities. 
Mimecast helps protect large and small organizations from malicious activity, 
human error and technology failure; and to lead the movement toward building a 
more resilient world. To find out more, visit our website.

Disclaimer

The information contained in this communication from the sender is 
confidential. It is intended solely for use by the recipient and others 
authorized to receive it. If you are not the recipient, you are hereby notified 
that any disclosure, copying, distribution or taking action in relation of the 
contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been 
automatically archived by Mimecast, a leader in email security and cyber 
resilience. Mimecast integrates email defenses with brand protection, security 
awareness training, web security, compliance and other essential capabilities. 
Mimecast helps protect large and small organizations from malicious activity, 
human error and technology failure; and to lead the movement toward building a 
more resilient world. To find out more, visit our website.

Automatic autovacuum to prevent wraparound - PG13.5

[Mauro Farracha]

Hello guys,

Have recently upgraded from PG10 to PG13.5 and would like to understand the
reason why we are seeing triggered autovacuum to prevent the wraparound
while all the metrics are still far off from the multixact/freeze max ages
defined. And inclusive there was one time where it was triggered as
aggressive.

Some background:
- autovacuum_freeze_max_age: 400M
- autovacuum_multixact_freeze_max_age: 800M
- the activity is mostly insert intensive in one particular table (60M
daily)
- the team execute vacuum freeze verbose every day at night to keep the
multixact ids down
- we generally reach near 70M mxids before running vacuum freeze at night
- the postgresql is Aurora

The scenario:
- Out of nowhere (during the weekend), without database activity load or
batches running, with previous nightly run of vacuum freeze, in the middle
of the day, with xids and mxids below 20M we are seeing autovacuum being
triggered to prevent wraparound.

My question is why this is occurring, which condition might be responsible
for this behaviour?

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Ray O'Donnell]

On 15/06/2022 11:58, Ian Dauncey wrote:
According to the apt list for PostgreSQL there is nothing for the s390 
system for version 13 and 14.


That is more than likely your answer, so I'm not knowledgable on 
these things, but perhaps the packagers will be able to say more. I'd be 
guided by that page in any case.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Tom Lane]

Ian Dauncey  writes:
> Currently we are running PostgreSQL 12.2 on Ubuntu 20.04.1 LTS (Kernel: Linux 
> 5.4.0-42-generic - Architecture: s390x).
> We are looking at upgrading to a later version of PostgreSQL
> My question is :- What is the latest available version of PostgreSQL that 
> will run on the above Architecture.

No support has been dropped at the source level, so any PG version ought
to work there.  But you might have to build it yourself --- which versions
are available in binary form from prebuilt repositories is a very
different question.

I'd try grabbing the SRPM for whichever version you want and seeing
if it doesn't build on your machine.  Building from SRPMs is not hard,
and it's a good skill to acquire if you're using non-mainstream
hardware.

regards, tom lane

Re: Automatic autovacuum to prevent wraparound - PG13.5

[Laurenz Albe]

On Wed, 2022-06-15 at 12:13 +0100, Mauro Farracha wrote:
> Have recently upgraded from PG10 to PG13.5 and would like to understand the 
> reason why we
> are seeing triggered autovacuum to prevent the wraparound while all the 
> metrics are still
> far off from the multixact/freeze max ages defined. And inclusive there was 
> one time where
> it was triggered as aggressive.
> 
> Some background:
> - autovacuum_freeze_max_age: 400M
> - autovacuum_multixact_freeze_max_age: 800M
> - the activity is mostly insert intensive in one particular table (60M daily)
> - the team execute vacuum freeze verbose every day at night to keep the 
> multixact ids down
> - we generally reach near 70M mxids before running vacuum freeze at night
> - the postgresql is Aurora
> 
> The scenario:
> - Out of nowhere (during the weekend), without database activity load or 
> batches running,
>   with previous nightly run of vacuum freeze, in the middle of the day, with 
> xids and mxids
>   below 20M we are seeing autovacuum being triggered to prevent wraparound.
> 
> My question is why this is occurring, which condition might be responsible 
> for this behaviour?

A long-running transaction or a prepared transaction.
Or an abandoned replication slot with an old "xmin".

That would be the answer for PostgreSQL.  It might apply to Amazon Aurora, 
unless they
changed the behavior there.  Perhaps ask Amazon.

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

Re: Recent 11.16 release change

[Daniel Brinzila]

Tom,

Thanks very much for the clarification, i was just going over the
regression tests and now makes sense.

However, is this the only function (row_to_json) or are there more of them
affected by this change?

Regards,
Daniel



On Tue, Jun 14, 2022 at 5:23 PM Tom Lane <
tgl_at_sss_pgh_pa_us_5s592v294c5771_de0d8...@icloud.com> wrote:

> Daniel Brinzila  writes:
> > I am a bit confused as to the following change:
> >Stop using query-provided column aliases for the columns of whole-row
> >variables that refer to plain tables (Tom Lane)
>
> > Could someone please give an example of this scenario, one that works in
> > 11.15 and another for 11.16 after the recent change.
>
> Here's the regression test example that changed behavior in that commit:
>
> regression=# select row_to_json(i) from int8_tbl i(x,y);
>   row_to_json
> 
>  {"q1":123,"q2":456}
>  {"q1":123,"q2":4567890123456789}
>  {"q1":4567890123456789,"q2":123}
>  {"q1":4567890123456789,"q2":4567890123456789}
>  {"q1":4567890123456789,"q2":-4567890123456789}
> (5 rows)
>
> The fields of the JSON output used to be labeled "x" and "y", after
> the column aliases of the FROM item.  But now that doesn't work and
> you get the table's original column names (which happen to be "q1"
> and "q2" in this test case).
>
> The workaround proposed in the release note is to do this if you
> need to relabel the columns of the whole-row variable "i":
>
> regression=# select row_to_json(i) from (select * from int8_tbl) i(x,y);
>  row_to_json
> --
>  {"x":123,"y":456}
>  {"x":123,"y":4567890123456789}
>  {"x":4567890123456789,"y":123}
>  {"x":4567890123456789,"y":4567890123456789}
>  {"x":4567890123456789,"y":-4567890123456789}
> (5 rows)
>
> With the extra sub-select, "i" is no longer of the named composite
> type associated with int8_tbl, but of an anonymous record type,
> so it can have the column names you want.
>
> regards, tom lane
>
>
>

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Adrian Klaver]

On 6/15/22 03:58, Ian Dauncey wrote:
According to the apt list for PostgreSQL there is nothing for the s390 
system for version 13 and 14.





But for version 12 there is

# sudo apt list postgresql-12 -a

Listing... Done

postgresql-12/focal-updates,focal-security,now 12.11-0ubuntu0.20.04.1 
s390x [installed]


This is from the Ubuntu repo and Ubuntu pins a Postgres version to each 
Ubuntu version in its repos. That means there will not be newer versions 
available.


The community repo:

https://www.postgresql.org/download/linux/ubuntu/

does not support s390, so that is not a solution.

As Tom Lane said you have the options of building from source or SRPM.



postgresql-12/focal 12.2-4 s390x

Regards

Ian



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

Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390

[Imre Samu]

> According to the apt list for PostgreSQL there is nothing for the s390
system for version 13 and 14.

Multiple postgres docker images exist for   "OS/ARCH=linux/s390x"
- "postgres:14.3-bullseye"( debian )
- "postgres:15beta1-bullseye"  ( debian )
- "postgres:15beta1-alpine3.16"   ( alpine )
- "postgres:13.7-alpine3.16"( alpine )
- etc.
maybe useful for testing on linux/s390x
( https://hub.docker.com/_/postgres?tab=tags )

regards,
 Imre


Ian Dauncey  ezt írta (időpont: 2022. jún. 15.,
Sze, 12:59):

> According to the apt list for PostgreSQL there is nothing for the s390
> system for version 13 and 14.
>
>
>
> # sudo apt list postgresql-14 -a
>
> Listing... Done
>
>
>
> # sudo apt list postgresql-13 -a
>
> Listing... Done
>
>
>
> But for version 12 there is
>
>
>
> # sudo apt list postgresql-12 -a
>
> Listing... Done
>
> postgresql-12/focal-updates,focal-security,now 12.11-0ubuntu0.20.04.1
> s390x [installed]
>
> postgresql-12/focal 12.2-4 s390x
>
>
>
> Regards
>
> Ian
>
>
>
> *From:* Ray O'Donnell 
> *Sent:* Wednesday, 15 June 2022 10:12
> *To:* Ian Dauncey ;
> pgsql-general@lists.postgresql.org
> *Subject:* Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390
>
>
>
> External email - treat with caution
>
> On 15 June 2022 08:31:42 Ian Dauncey  wrote:
>
> Morning all
>
>
>
> Currently we are running PostgreSQL 12.2 on Ubuntu 20.04.1 LTS (Kernel:
> Linux 5.4.0-42-generic - Architecture: s390x).
>
>
>
> We are looking at upgrading to a later version of PostgreSQL
>
>
>
> My question is :- What is the latest available version of PostgreSQL that
> will run on the above Architecture.
>
>
>
> Here you go:
>
>
>
> https://wiki.postgresql.org/wiki/Apt
>
>
>
> Ray.
>
>
>
>
>
>
>
>
>
>
>
> Regards
>
> Ian
>
>
>
> *Disclaimer*
>
> The information contained in this communication from the sender is
> confidential. It is intended solely for use by the recipient and others
> authorized to receive it. If you are not the recipient, you are hereby
> notified that any disclosure, copying, distribution or taking action in
> relation of the contents of this information is strictly prohibited and may
> be unlawful.
>
> This email has been scanned for viruses and malware, and may have been
> automatically archived by Mimecast, a leader in email security and cyber
> resilience. Mimecast integrates email defenses with brand protection,
> security awareness training, web security, compliance and other essential
> capabilities. Mimecast helps protect large and small organizations from
> malicious activity, human error and technology failure; and to lead the
> movement toward building a more resilient world. To find out more, visit
> our website.
>
>
>
>
> *Disclaimer*
>
> The information contained in this communication from the sender is
> confidential. It is intended solely for use by the recipient and others
> authorized to receive it. If you are not the recipient, you are hereby
> notified that any disclosure, copying, distribution or taking action in
> relation of the contents of this information is strictly prohibited and may
> be unlawful.
>
> This email has been scanned for viruses and malware, and may have been
> automatically archived by Mimecast, a leader in email security and cyber
> resilience. Mimecast integrates email defenses with brand protection,
> security awareness training, web security, compliance and other essential
> capabilities. Mimecast helps protect large and small organizations from
> malicious activity, human error and technology failure; and to lead the
> movement toward building a more resilient world. To find out more, visit
> our website.
>

ERROR: failed to find conversion function from key_vals_nn to record[]

[Bryn Llewellyn]

I’ve copied a self-contained testcase below. Is the error that the "as 
intended" test causes due to a known limitation—or even a semantic dilemma that 
I'm failing to spot? Or might it be due to a bug?

If you're interested, the testcase rests on this thinking:

Many SQL experts claim that the RDBMS pioneers made a mistake when they made 
data types nullable by default—and they argue that the optional modifier should 
have been *nullable*. Anyway, the philosophy is clear:

«
Nulls bring all sorts of risks to the programmer that are trivially avoided in 
most cases where you don’t anyway want nulls.
»

However, it’s a pain to have to remember to write "not null" in a gazillion 
places. And the user-defined domain (conspicuous by its absence in Oracle 
Database) is the perfect device to impose your intentions from a single point 
of maintenance.

I've gone to the max. with the "nn" domain approach in my testcase. It uses a 
composite type thus:

  type key_val as (k text_nn, v text_nn);

(At least the "text_nn" idea doesn't cause problems.)

It uses the "any()" array function to test if a given "key_val" value is found 
in an array of such values.

The error that occurs when I write what I mean, using a "key_val_nn" value and 
a "key_vals_nn" value.

I can work around the problem by typecasting my values back to their base 
"key_val" and "key_val[]" values by hand.

So I'm surprised that PG can't manage this typecasting for itself.

——

create domain text_nn as text not null;
create type key_val as (k text_nn, v text_nn);

create domain key_val_nn  as key_val not null;
create domain key_vals_nn as key_val_nn[] not null;

create function f(which in text)
  returns text
  language plpgsql
as $body$
declare
  -- Use the raw composite type.
  kv1 constant key_val := ('a', 'b');
  kv2 constant key_val := ('a', 'b');
  kvs constant key_val[]   := array[kv1, kv2];

  -- Use the domains that bring null-safety.
  kv1_nn  constant key_val_nn  := ('a', 'b');
  kvs_nn  constant key_vals_nn := array[kv1, kv2];

  -- Typecast the null-safe values back to the raw composite type.
  kv1_0   constant key_val := kv1_nn;
  kvs_0   constant key_val[]   := kvs_nn;
begin
  case which
when 'without NOT NULL safety' then
  return (kv1 = any(kvs));
when 'as intended' then
  return (kv1_nn = any(kvs_nn));
when 'workaround' then
  return (kv1_0 = any(kvs_0));
  end case;
end;
$body$;

select f('without NOT NULL safety');
select f('workaround');

/*
  This one cases the error, thus:

  ERROR:  failed to find conversion function from key_vals_nn to record[]
  CONTEXT:  SQL expression "(kv1_nn = any(kvs_nn))"
*/;
select f('as intended');

Re: ERROR: failed to find conversion function from key_vals_nn to record[]

[David G. Johnston]

On Wednesday, June 15, 2022, Bryn Llewellyn  wrote:

> I’ve copied a self-contained testcase below. Is the error that the "as
> intended" test causes due to a known limitation—or even a semantic dilemma
> that I'm failing to spot? Or might it be due to a bug?
>

I read the note in create domain as basically “don’t do this” (the not null
part) but the issue you are pointing out seems unrelated to that.


> */**
>
>
>
>
>
> *  This one cases the error, thus:  ERROR:  failed to find conversion
> function from key_vals_nn to record[]  CONTEXT:  SQL expression "(kv1_nn =
> any(kvs_nn))"*/;select f('as intended');*
>

The fact that a domain over an array isn’t being seen as an array here
seems like a bug.  POLA violation at least, and I don’t recall any notes
regarding this dynamic in the docs.

However, a more trivial case does work, at least in HEAD:

postgres=# create domain mytext as text[] not null;
CREATE DOMAIN
postgres=# select '1' = any(array['1','2']::mytext);
 ?column?
--
 t
(1 row)

However, as you show:

postgres=# create type kv AS ( key text, val text );
CREATE TYPE

postgres=# create domain kvarr as kv[];
CREATE DOMAIN

postgres=# select ('1','one')::kv = any (array[('1','one')::kv]);
 ?column?
--
 t
(1 row)

postgres=# select ('1','one')::kv = any ((array[('1','one')::kv])::kvarr);
ERROR:  failed to find conversion function from kvarr to record[]

So the interaction of a composite type and the domain over array seems to
be the scope of the issue - which makes me thing bug even more.

David J.

Re: ERROR: failed to find conversion function from key_vals_nn to record[]

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> I’ve copied a self-contained testcase below. Is the error that the "as 
>> intended" test causes due to a known limitation—or even a semantic dilemma 
>> that I'm failing to spot? Or might it be due to a bug?
> 
> I read the note in create domain as basically “don’t do this” (the not null 
> part) but the issue you are pointing out seems unrelated to that. 
> 
>> /*
>>   This one cases the error, thus:
>> 
>>   ERROR:  failed to find conversion function from key_vals_nn to record[]
>>   CONTEXT:  SQL expression "(kv1_nn = any(kvs_nn))"
>> */;
>> select f('as intended');
> 
> The fact that a domain over an array isn’t being seen as an array here seems 
> like a bug.  POLA violation at least, and I don’t recall any notes regarding 
> this dynamic in the docs.
> 
> However, a more trivial case does work, at least in HEAD:
> 
> create domain mytext as text[] not null;
> select '1' = any(array['1','2']::mytext);
>  ?column?
> --
>  t
> 
> However, as you show:
> 
> create type kv AS ( key text, val text );
> create domain kvarr as kv[];
> select ('1','one')::kv = any (array[('1','one')::kv]);
>  ?column?
> --
>  t
> (1 row)
> 
> select ('1','one')::kv = any ((array[('1','one')::kv])::kvarr);
> ERROR:  failed to find conversion function from kvarr to record[]
> 
> So the interaction of a composite type and the domain over array seems to be 
> the scope of the issue - which makes me thing bug even more.

Thanks for the quick response, David. I'll use my workaround for now. And I'll 
look out for news about a possible fix.

About this from the "create domain" doc:

«
Best practice therefore is to design a domain's constraints so that a null 
value is allowed, and then to apply column NOT NULL constraints to columns of 
the domain type as needed, rather than directly to the domain type.
»

What an enormous disappointment. It defeats a large part of what I wanted to 
adopt as a practice. As I understand that “don’t do this” caution, and the 
discussion that surrounds it, the advice applies only to the case that a domain 
with a not null constraint is used as the data type of a column in a table. I 
tried this variant on what the doc has:

create domain text_nn as text not null;
create table t1(k int primary key, v text not null);
insert into t1(k, v) values(1, 'x');

-- Causes:
-- null value in column "v" of relation "t1" violates not-null constraint
insert into t1(k, v) values(2, (select 'y'::text_nn where false));

Right, the subquery evaluates to "null". Then failure comes, as is intended, 
when the attempt is made to assign "null" to "t.v" in the to-be-inserted row.

Then I repeated the test like this:

create table t2(k int primary key, v text_nn);
insert into t2(k, v) values(1, 'x');
insert into t2(k, v) values(2, (select 'y'::text_nn where false));

\pset null ''
select
  k,
  v,
  pg_typeof(v) as "pg_typeof(v)"
from t2;

No error—and this result:

 k |   v| pg_typeof(v) 
---++--
 1 | x  | text_nn
 2 |  | text_nn

This is what the doc promises. But how can you see it as anything but a bug? 
The subquery evaluates to "null", and only then is the attempt made to create a 
new row which self-evidently violates the domain's constraint. How is it any 
different from this:

insert into t2(k, v) values(1, null);

This obligingly causes "domain text_nn does not allow null values".

Re: ERROR: failed to find conversion function from key_vals_nn to record[]

[David G. Johnston]

On Wed, Jun 15, 2022 at 11:07 PM Bryn Llewellyn  wrote:

> This is what the doc promises. But how can you see it as anything but a
> bug? The subquery evaluates to "null", and only then is the attempt made to
> create a new row which self-evidently violates the domain's constraint. How
> is it any different from this:
>

Because NULL is doing double-duty.  The absence of any possible value is
represented by null, as is knowing that a value exists but not knowing what
that value is.  The most obvious example of this problem is:
(not tested, may have typos)

SELECT a.vala, b.valb, b.lbl
FROM (values (1)) AS a (vala)
LEFT JOIN (values (2, null)) AS b (valb, lbl) ON vala = valb;

vs.

SELECT a.vala, b.valb, b.lbl
FROM (values (1)) AS a (vala)
LEFT JOIN (values (1, null)) AS b (valb, lbl) ON vala = valb;

The resultant value of "b.lbl" is null in both queries, but not for the
same reason (left join missing semantics pre-empt null type value
semantics).

So, yes, queries can produce NULL even for domains that are defined not
null.  If you want protection from null values in your database tables you
must define your columns to not accept nulls.  It becomes a habit and can
be readily checked for in the catalogs (I suggest adding a column comment
for why columns defined as null are allowed to be null, then you can query
the column contents to exclude those columns where null was intended - or
go with your idea and just report every column as non-conforming. COMMENT
ON table.column IS '@NULLABLE - optional information the customer might not
provide').

David J.