Yum repository RPM behind release

[Jack Douglas]

Hi

Postgres 13 is out and the yum repos for the release exist, eg: 
https://yum.postgresql.org/13/redhat/rhel-8-x86_64/ 
. However the repository 
RPM suggested at https://www.postgresql.org/download/linux/redhat/ 
 
(https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
 
)
 still points at rc1.

I also noticed that I didn't get rc1 for a while after the release.

Is the repository RPM updated on an automatic schedule?

Ideally I'd like to get db<>fiddle updated on the day of the release, do I have 
to switch back to Debian from CentOS to achieve that?

Jack

pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi,

the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and 
built it without issues like so:

(pgTargetDir is /data/postgres/13.0)

$ ./configure \
--prefix=${pgTargetDir} \
--enable-nls \
--with-icu \
--with-perl \
--with-python \
--with-openssl \
--with-ldap \
--with-libxml \
--with-tclconfig=/usr/lib64
$ make install-world

As I said, no issues at all so far. Now, I set the new environment for version 
13.0 and do:

$ initdb -D "${PGDATANEW}" \
-k \
--encoding=utf8 \
--lc-collate=en_US.UTF-8 \
--lc-ctype=en_US.UTF-8 \
--wal-segsize=32

Still, no issues and all is well.

Now comes the problem: I run pg_upgrade and it spits out problems with 
plpython2:

$ pg_upgrade --check -k
Performing Consistency Checks on Old Live Server

Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
loadable_libraries.txt

Failure, exiting

$ cat loadable_libraries.txt 
could not load library "$libdir/plpython2": ERROR:  could not access file 
"$libdir/plpython2": No such file or directory
In database: postgres

openSUSE has no python2 anymore, only python3. The source database does not 
even have plpython installed:

postgres=# select name, version from pg_available_extension_versions
postgres-# where installed;
  name   | version 
-+-
 plperlu | 1.0
 dblink  | 1.2
 plpgsql | 1.0
 plperl  | 1.0
(4 rows)

Can you please tell me what I am doing wrong here? Yes, I could compile the 
source without Python in this particular case, because it is not needed on this 
particular database cluster. But we have many more database clusters and a 
bunch of them uses plpython, so I definitely need it. To better be safe than 
sorry, I want the issue resolved before migrations start, even for database 
clusters which don't need it.

Any help would be appreciated. Thanks very much in advance.

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi,

sorry, forgot to mention two things (see below)

> On 26. Sep, 2020, at 11:33, Paul Förster  wrote:
> 
> Hi,
> 
> the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source 
> and built it without issues like so:
> 
> (pgTargetDir is /data/postgres/13.0)
> 
> $ ./configure \
>--prefix=${pgTargetDir} \
>--enable-nls \
>--with-icu \
>--with-perl \
>--with-python \
>--with-openssl \
>--with-ldap \
>--with-libxml \
>--with-tclconfig=/usr/lib64
> $ make install-world
> 
> As I said, no issues at all so far. Now, I set the new environment for 
> version 13.0 and do:
> 
> $ initdb -D "${PGDATANEW}" \
>-k \
>--encoding=utf8 \
>--lc-collate=en_US.UTF-8 \
>--lc-ctype=en_US.UTF-8 \
>--wal-segsize=32
> 
> Still, no issues and all is well.
> 
> Now comes the problem: I run pg_upgrade and it spits out problems with 
> plpython2:
> 
> $ pg_upgrade --check -k
> Performing Consistency Checks on Old Live Server
> 
> Checking cluster versions   ok
> Checking database user is the install user  ok
> Checking database connection settings   ok
> Checking for prepared transactions  ok
> Checking for reg* data types in user tables ok
> Checking for contrib/isn with bigint-passing mismatch   ok
> Checking for presence of required libraries fatal
> 
> Your installation references loadable libraries that are missing from the
> new installation.  You can add these libraries to the new installation,
> or remove the functions using them from the old installation.  A list of
> problem libraries is in the file:
>loadable_libraries.txt
> 
> Failure, exiting
> 
> $ cat loadable_libraries.txt 
> could not load library "$libdir/plpython2": ERROR:  could not access file 
> "$libdir/plpython2": No such file or directory
> In database: postgres
> 
> openSUSE has no python2 anymore, only python3. The source database does not 
> even have plpython installed:
> 
> postgres=# select name, version from pg_available_extension_versions
> postgres-# where installed;
>  name   | version 
> -+-
> plperlu | 1.0
> dblink  | 1.2
> plpgsql | 1.0
> plperl  | 1.0
> (4 rows)
> 
> Can you please tell me what I am doing wrong here? Yes, I could compile the 
> source without Python in this particular case, because it is not needed on 
> this particular database cluster. But we have many more database clusters and 
> a bunch of them uses plpython, so I definitely need it. To better be safe 
> than sorry, I want the issue resolved before migrations start, even for 
> database clusters which don't need it.

the two things I forgot to mention are:

a) the versions I will be upgrading from are 11.9 and 12.4. The above thing 
happens with 12.4. Since I didn't check 11.9 yet, I cannot say if it appears 
there too.

b) the 12.4 software also does not have plpython2 files in its lib64 directory. 
Both only have plpython3.so:

$ ll /data/postgres/*/lib64/plpython*
-rwxr-xr-x 1 postgres dba 151672 Aug 13 16:28 
/data/postgres/12.4/lib64/plpython3.so
-rwxr-xr-x 1 postgres dba 151544 Sep 26 10:38 
/data/postgres/13.0/lib64/plpython3.so

Any help would be appreciated. Thanks very much in advance.

Cheers,
Paul

Re: PG 13 trusted extensions and pg_available_extensions

[Julien Rouhaud]

On Fri, Sep 25, 2020 at 2:51 PM Daniel Westermann (DWE)
 wrote:
>
> On Thu, Sep 24, 2020 at 10:58 AM Michael Paquier  wrote:
> >>
> >> On Wed, Sep 23, 2020 at 03:28:45PM +, Daniel Westermann (DWE) wrote:
> >> > I was playing a bit with trusted extensions and wondered if there is
> >> > a reason that the "trusted" flag is not exposed in 
> >> > pg_available_extensions.
> >> > I believe that information would be quite useful so one can easily
> >> > identify extensions that can be installed as "normal" user.
> >>
> >> Adding the trusted flag makes sense for visibility.  There is a bit
> >> more that we could consider though?  For example, what about
> >> "relocatable" and "requires"?
>
> >+1, and also the schema (for non relocatable extensions).

So, apparently pg_available_extension_versions already had those
fields so all the required infrastructure was already there.  I just
added the exact same fields to pg_available_extensions, see attached
patch.


v1-pg_available_extensions_fields.diff
Description: Binary data

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi,

> On 26. Sep, 2020, at 11:42, Paul Förster  wrote:
> 
> the two things I forgot to mention are:
> 
> a) the versions I will be upgrading from are 11.9 and 12.4. The above thing 
> happens with 12.4. Since I didn't check 11.9 yet, I cannot say if it appears 
> there too.
> 
> b) the 12.4 software also does not have plpython2 files in its lib64 
> directory. Both only have plpython3.so:
> 
> $ ll /data/postgres/*/lib64/plpython*
> -rwxr-xr-x 1 postgres dba 151672 Aug 13 16:28 
> /data/postgres/12.4/lib64/plpython3.so
> -rwxr-xr-x 1 postgres dba 151544 Sep 26 10:38 
> /data/postgres/13.0/lib64/plpython3.so
> 
> Any help would be appreciated. Thanks very much in advance.

seems, I found some kind of solution:

- before running "pg_upgrade --check -k":
  drop extension plpythonu;
- run pg_upgrade
- after the upgrade:
  create extension plpython3u;

Is this the correct way?

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Tom Lane]

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
> seems, I found some kind of solution:

> - before running "pg_upgrade --check -k":
>   drop extension plpythonu;
> - run pg_upgrade
> - after the upgrade:
>   create extension plpython3u;

> Is this the correct way?

If you had plpythonu installed before, that's a plausible thing
to do.  (There was discussion some time ago about making the
python-2-to-3 transition less painful for users, but we failed
to come to any consensus about how; so manual fixes like this
are going to be needed for a lot of people.)

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

regards, tom lane

Re: PG 13 trusted extensions and pg_available_extensions

[Tom Lane]

Julien Rouhaud  writes:
> So, apparently pg_available_extension_versions already had those
> fields so all the required infrastructure was already there.  I just
> added the exact same fields to pg_available_extensions, see attached
> patch.

The reason that pg_available_extensions has only the fields it has
is that these other values are potentially extension-version-dependent.
I do not think we can accept this patch.

(Strictly speaking, the "comment" might be version-specific too, but
there's less chance of printing a critically misleading value there.)

regards, tom lane

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi Tom,

> On 26. Sep, 2020, at 16:07, Tom Lane  wrote:
> 
> If you had plpythonu installed before, that's a plausible thing
> to do.  (There was discussion some time ago about making the
> python-2-to-3 transition less painful for users, but we failed
> to come to any consensus about how; so manual fixes like this
> are going to be needed for a lot of people.)

that's one of the things I very much don't like about Python. This version 2 
and 3 gibberish has been going on for years now wherever it's used, be it 
packaging with operating systems or integrated into applications.

> However, I don't understand how "drop extension plpythonu"
> worked for you, given your previous query showing that
> that extension wasn't installed.

that is exactly what I don't understand too. It should have shown up in the 
query, but it didn't. The PostgreSQL 12.4 software was compiled exactly the 
same way, only without ICU support. The other configure options were the same. 
Other than that, I didn't change anything in my build script.

For your reference, below are the two configs:

PostgreSQL 12.4:

$ pg_config 
BINDIR = /data/postgres/12.4/bin
DOCDIR = /data/postgres/12.4/share/doc
HTMLDIR = /data/postgres/12.4/share/doc
INCLUDEDIR = /data/postgres/12.4/include
PKGINCLUDEDIR = /data/postgres/12.4/include
INCLUDEDIR-SERVER = /data/postgres/12.4/include/server
LIBDIR = /data/postgres/12.4/lib64
PKGLIBDIR = /data/postgres/12.4/lib64
LOCALEDIR = /data/postgres/12.4/share/locale
MANDIR = /data/postgres/12.4/share/man
SHAREDIR = /data/postgres/12.4/share
SYSCONFDIR = /data/postgres/12.4/etc
PGXS = /data/postgres/12.4/lib64/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/data/postgres/12.4' '--enable-nls' '--with-perl' 
'--with-python' '--with-openssl' '--with-ldap' '--with-libxml' 
'--with-tclconfig=/usr/lib64'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Werror=vla -Wendif-labels 
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -Wno-format-truncation -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed 
-Wl,-rpath,'/data/postgres/12.4/lib64',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lpthread -lxml2 -lssl -lcrypto -lz -lreadline -lrt 
-lcrypt -ldl -lm 
VERSION = PostgreSQL 12.4

PostgreSQL 13.0:

$ pg_config 
BINDIR = /data/postgres/13.0/bin
DOCDIR = /data/postgres/13.0/share/doc
HTMLDIR = /data/postgres/13.0/share/doc
INCLUDEDIR = /data/postgres/13.0/include
PKGINCLUDEDIR = /data/postgres/13.0/include
INCLUDEDIR-SERVER = /data/postgres/13.0/include/server
LIBDIR = /data/postgres/13.0/lib64
PKGLIBDIR = /data/postgres/13.0/lib64
LOCALEDIR = /data/postgres/13.0/share/locale
MANDIR = /data/postgres/13.0/share/man
SHAREDIR = /data/postgres/13.0/share
SYSCONFDIR = /data/postgres/13.0/etc
PGXS = /data/postgres/13.0/lib64/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--prefix=/data/postgres/13.0' '--enable-nls' '--with-icu' 
'--with-perl' '--with-python' '--with-openssl' '--with-ldap' '--with-libxml' 
'--with-tclconfig=/usr/lib64'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Werror=vla -Wendif-labels 
-Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security 
-fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation 
-O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed 
-Wl,-rpath,'/data/postgres/13.0/lib64',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lpthread -lxml2 -lssl -lcrypto -lz -lreadline -lrt 
-ldl -lm 
VERSION = PostgreSQL 13.0

Cheers,
Paul

Re: PG 13 trusted extensions and pg_available_extensions

[Julien Rouhaud]

On Sat, Sep 26, 2020 at 10:11 PM Tom Lane  wrote:
>
> Julien Rouhaud  writes:
> > So, apparently pg_available_extension_versions already had those
> > fields so all the required infrastructure was already there.  I just
> > added the exact same fields to pg_available_extensions, see attached
> > patch.
>
> The reason that pg_available_extensions has only the fields it has
> is that these other values are potentially extension-version-dependent.
> I do not think we can accept this patch.

Oh, I didn't know there could be multiple control files per extension,
and I missed the "aux" reference.  So indeed this patch is
unacceptable.

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi Tom,

> On 26. Sep, 2020, at 16:07, Tom Lane  wrote:
> 
> =?utf-8?Q?Paul_F=C3=B6rster?=  writes:
>> seems, I found some kind of solution:
> 
>> - before running "pg_upgrade --check -k":
>>  drop extension plpythonu;
>> - run pg_upgrade
>> - after the upgrade:
>>  create extension plpython3u;
> 
>> Is this the correct way?
> 
> If you had plpythonu installed before, that's a plausible thing
> to do.  (There was discussion some time ago about making the
> python-2-to-3 transition less painful for users, but we failed
> to come to any consensus about how; so manual fixes like this
> are going to be needed for a lot of people.)
> 
> However, I don't understand how "drop extension plpythonu"
> worked for you, given your previous query showing that
> that extension wasn't installed.

just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where installed;
  name   | version | installed | superuser | relocatable |   schema   | 
requires |   comment
-+-+---+---+-++--+--
 plperlu | 1.0 | t | t | f   | pg_catalog | 
 | PL/PerlU untrusted procedural language
 dblink  | 1.2 | t | t | t   || 
 | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0 | t | f | f   | pg_catalog | 
 | PL/pgSQL procedural language
 plperl  | 1.0 | t | f | f   | pg_catalog | 
 | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

The "plpython" and "plpython3u" for the drop and create extension statements 
came by entering "plpy" and then pressing tab. So PostgreSQL knew about them. 
Still, as you can see, I could drop pypythonu again though it did not appear in 
the query. After the create extension, it appears as it should:

postgres=# select * from pg_available_extension_versions where installed;
name| version | installed | superuser | relocatable |   schema   | 
requires |   comment
+-+---+---+-++--+--
 plperlu| 1.0 | t | t | f   | pg_catalog |  
| PL/PerlU untrusted procedural language
 dblink | 1.2 | t | t | t   ||  
| connect to other PostgreSQL databases from within a database
 plpython3u | 1.0 | t | t | f   | pg_catalog |  
| PL/Python3U untrusted procedural language
 plpgsql| 1.0 | t | f | f   | pg_catalog |  
| PL/pgSQL procedural language
 plperl | 1.0 | t | f | f   | pg_catalog |  
| PL/Perl procedural language
(5 rows)

Is this a bug in 12.4 not showing the extension?

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Tom Lane]

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
> On 26. Sep, 2020, at 16:07, Tom Lane  wrote:
>> However, I don't understand how "drop extension plpythonu"
>> worked for you, given your previous query showing that
>> that extension wasn't installed.

> just checked with another 12.4. It's the same:

> postgres=# select * from pg_available_extension_versions where installed;
>   name   | version | installed | superuser | relocatable |   schema   | 
> requires |   comment
> -+-+---+---+-++--+--
>  plperlu | 1.0 | t | t | f   | pg_catalog |   
>| PL/PerlU untrusted procedural language
>  dblink  | 1.2 | t | t | t   ||   
>| connect to other PostgreSQL databases from within a database
>  plpgsql | 1.0 | t | f | f   | pg_catalog |   
>| PL/pgSQL procedural language
>  plperl  | 1.0 | t | f | f   | pg_catalog |   
>| PL/Perl procedural language
> (4 rows)

> postgres=# drop extension plpythonu ;
> DROP EXTENSION
> postgres=# create extension plpython3u ;
> CREATE EXTENSION

Actually, now that I think about it, you're querying the wrong view.
I'm too lazy to check the source code right now, but I'm pretty sure
that pg_available_extension_versions is mostly driven off what control
files exist in the on-disk libdir.  But that may have little to do with
what's in the system catalogs.  You should have checked pg_extension,
or just "\dx" in psql.

regards, tom lane

Re: pg_upgrade Python version issue on openSUSE

[Adrian Klaver]

On 9/26/20 7:49 AM, Tom Lane wrote:

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:

On 26. Sep, 2020, at 16:07, Tom Lane  wrote:

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.



just checked with another 12.4. It's the same:



postgres=# select * from pg_available_extension_versions where installed;
   name   | version | installed | superuser | relocatable |   schema   | 
requires |   comment
-+-+---+---+-++--+--
  plperlu | 1.0 | t | t | f   | pg_catalog |
  | PL/PerlU untrusted procedural language
  dblink  | 1.2 | t | t | t   ||
  | connect to other PostgreSQL databases from within a database
  plpgsql | 1.0 | t | f | f   | pg_catalog |
  | PL/pgSQL procedural language
  plperl  | 1.0 | t | f | f   | pg_catalog |
  | PL/Perl procedural language
(4 rows)



postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION


Actually, now that I think about it, you're querying the wrong view.
I'm too lazy to check the source code right now, but I'm pretty sure
that pg_available_extension_versions is mostly driven off what control
files exist in the on-disk libdir.  But that may have little to do with
what's in the system catalogs.  You should have checked pg_extension,
or just "\dx" in psql.


I believe the issue is here:

select * from pg_pltemplate ;


 plpythonu  | f   | f | plpython_call_handler  | 
plpython_inline_handler  | plpython_validator  | $libdir/plpython2 | NULL
 plpython2u | f   | f | plpython2_call_handler | 
plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL
 plpython3u | f   | f | plpython3_call_handler | 
plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL



The default plpython is plpythonu and that points at $libdir/plpython2.

The instructions here:

https://www.postgresql.org/docs/12/plpython-python23.html

offer a work around:

"Daredevils, who want to build a Python-3-only operating system 
environment, can change the contents of pg_pltemplate to make plpythonu 
be equivalent to plpython3u, keeping in mind that this would make their 
installation incompatible with most of the rest of the world."





regards, tom lane





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

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi Tom,

> On 26. Sep, 2020, at 16:49, Tom Lane  wrote:
> 
> Actually, now that I think about it, you're querying the wrong view.
> I'm too lazy to check the source code right now, but I'm pretty sure
> that pg_available_extension_versions is mostly driven off what control
> files exist in the on-disk libdir.  But that may have little to do with
> what's in the system catalogs.  You should have checked pg_extension,
> or just "\dx" in psql.

just created another new empty database cluster because I run out of them on my 
test box here at home. :-) After all, each drop/create extension seems to 
resolve the issue, so the cluster is unusable for repetition, unless I would 
restore it. Ok, I'm too lazy now... :-D

Did the usual initdb -k on the new database cluster. Then the select plus your 
suggested \dx. Nothing there and drop extension didn't work, all as I would 
have expected. This is strange.

I will check further next week on company databases. The ones I did it up to 
now are my private ones at home. I'm really curious about that next week.

Thanks for the tips.

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Adrian Klaver]

On 9/26/20 2:33 AM, Paul Förster wrote:

Hi,

the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and 
built it without issues like so:





openSUSE has no python2 anymore, only python3. The source database does not 
even have plpython installed:



Actually it does:

https://software.opensuse.org/package/python?search_term=%22python%22



Cheers,
Paul




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

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi Adrian,

> On 26. Sep, 2020, at 17:07, Adrian Klaver  wrote:
> 
> I believe the issue is here:
> 
> select * from pg_pltemplate ;
> 
> 
> plpythonu  | f   | f | plpython_call_handler  | 
> plpython_inline_handler  | plpython_validator  | $libdir/plpython2 | NULL
> plpython2u | f   | f | plpython2_call_handler | 
> plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL
> plpython3u | f   | f | plpython3_call_handler | 
> plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL
> 
> 
> The default plpython is plpythonu and that points at $libdir/plpython2.
> 
> The instructions here:
> 
> https://www.postgresql.org/docs/12/plpython-python23.html
> 
> offer a work around:
> 
> "Daredevils, who want to build a Python-3-only operating system environment, 
> can change the contents of pg_pltemplate to make plpythonu be equivalent to 
> plpython3u, keeping in mind that this would make their installation 
> incompatible with most of the rest of the world."

sounds like:

update pg_pltemplate
set
tmplhandler='plpython3_call_handler',
tmplinline='plpython3_inline_handler',
tmplvalidator='plpython3_validator',
tmpllibrary='$libdir/plpython3'
where
tmplname='plpythonu';

And that sounds somewhat dangerous to me, especially if I take the comment on 
the plpython-python23 page into account: "keeping in mind that this would make 
their installation incompatible with most of the rest of the world."

I'd rather not...

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi Adrian,

> On 26. Sep, 2020, at 17:17, Adrian Klaver  wrote:
> 
> On 9/26/20 2:33 AM, Paul Förster wrote:
>> Hi,
>> the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source 
>> and built it without issues like so:
> 
>> openSUSE has no python2 anymore, only python3. The source database does not 
>> even have plpython installed:
> 
> Actually it does:
> 
> https://software.opensuse.org/package/python?search_term=%22python%22

well, actually, it does not. There are still Python2 packages in the repo which 
I can happily install here at home but not in the company. So I will run into 
trouble if I install something here to make something else work, and then take 
it to the company where it does not work. So, to maintain the highest level of 
compatibility to the machines at work, I don't install extra packages.

But yes, that would be the second most proper solution. The first and utmost 
proper solution would be for Python to finally stop that versioning crap after 
years have gone by now.

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Adrian Klaver]

On 9/26/20 8:26 AM, Paul Förster wrote:

Hi Adrian,


On 26. Sep, 2020, at 17:17, Adrian Klaver  wrote:

On 9/26/20 2:33 AM, Paul Förster wrote:

Hi,
the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and 
built it without issues like so:



openSUSE has no python2 anymore, only python3. The source database does not 
even have plpython installed:


Actually it does:

https://software.opensuse.org/package/python?search_term=%22python%22


well, actually, it does not. There are still Python2 packages in the repo which 
I can happily install here at home but not in the company. So I will run into 
trouble if I install something here to make something else work, and then take 
it to the company where it does not work. So, to maintain the highest level of 
compatibility to the machines at work, I don't install extra packages.


I suppose getting them to install Python 2 is out of the question? It is 
an official package.




But yes, that would be the second most proper solution. The first and utmost 
proper solution would be for Python to finally stop that versioning crap after 
years have gone by now.


Well there is always going to be versioning. If you mean the 
incompatibility split, then for 2/3 that is not going away. There will 
be a Python 4, but the core developers have said they learned their 
lesson and it will just be an incremental upgrade.




Cheers,
Paul




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

Re: pg_upgrade Python version issue on openSUSE

[Paul Förster]

Hi Adrian,

> On 26. Sep, 2020, at 17:43, Adrian Klaver  wrote:
> 
> I suppose getting them to install Python 2 is out of the question? It is an 
> official package.

I can try, but chances are at 99% that they refuse.

> Well there is always going to be versioning. If you mean the incompatibility 
> split, then for 2/3 that is not going away. There will be a Python 4, but the 
> core developers have said they learned their lesson and it will just be an 
> incremental upgrade.

so you're saying there will always be two Pythons? One Python 2 and one Python 
x (with x>=3)? Oh my god... Why don't they just make Python 3 backward 
compatible?

Cheers,
Paul

Re: pg_upgrade Python version issue on openSUSE

[Adrian Klaver]

On 9/26/20 8:54 AM, Paul Förster wrote:

Hi Adrian,


On 26. Sep, 2020, at 17:43, Adrian Klaver  wrote:

I suppose getting them to install Python 2 is out of the question? It is an 
official package.


I can try, but chances are at 99% that they refuse.


Well there is always going to be versioning. If you mean the incompatibility 
split, then for 2/3 that is not going away. There will be a Python 4, but the 
core developers have said they learned their lesson and it will just be an 
incremental upgrade.


so you're saying there will always be two Pythons? One Python 2 and one Python x 
(with x>=3)? Oh my god... Why don't they just make Python 3 backward compatible?


Well one would hope folks eventually finish migrating off Python 2, but 
there is a lot of that code out there. There have been tweaks to make 
them more compatible. The sticking point for full compatibility is the 
Unicode transition. That would cause the same breakage as exists now in 
Python 2 --> Python 3, so there is no real point and the developers 
don't want to relive that experience. The goal going forward is for 
everybody to move to Python 3 and have changes in the future be 
incremental. Anyway that is enough for an off-topic discussion.




Cheers,
Paul




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

Re: pg_upgrade Python version issue on openSUSE

[Adrian Klaver]

On 9/26/20 7:35 AM, Paul Förster wrote:

Hi Tom,




just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where installed;
   name   | version | installed | superuser | relocatable |   schema   | 
requires |   comment
-+-+---+---+-++--+--
  plperlu | 1.0 | t | t | f   | pg_catalog |
  | PL/PerlU untrusted procedural language
  dblink  | 1.2 | t | t | t   ||
  | connect to other PostgreSQL databases from within a database
  plpgsql | 1.0 | t | f | f   | pg_catalog |
  | PL/pgSQL procedural language
  plperl  | 1.0 | t | f | f   | pg_catalog |
  | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

The "plpython" and "plpython3u" for the drop and create extension statements came by 
entering "plpy" and then pressing tab. So PostgreSQL knew about them. Still, as you can see, I 
could drop pypythonu again though it did not appear in the query. After the create extension, it appears as 
it should:

postgres=# select * from pg_available_extension_versions where installed;
 name| version | installed | superuser | relocatable |   schema   | 
requires |   comment
+-+---+---+-++--+--
  plperlu| 1.0 | t | t | f   | pg_catalog | 
 | PL/PerlU untrusted procedural language
  dblink | 1.2 | t | t | t   || 
 | connect to other PostgreSQL databases from within a database
  plpython3u | 1.0 | t | t | f   | pg_catalog | 
 | PL/Python3U untrusted procedural language
  plpgsql| 1.0 | t | f | f   | pg_catalog | 
 | PL/pgSQL procedural language
  plperl | 1.0 | t | f | f   | pg_catalog | 
 | PL/Perl procedural language
(5 rows)

Is this a bug in 12.4 not showing the extension?


Could it be that at some point in these instances history plpython* 
where installed as CREATE LANGUAGE and you are dealing with the vestiges 
of that?


Are you able to go back and reconstruct them and then do \dL (languages) 
and \dx (extensions)?




Cheers,
Paul




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

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[John DeSoi]

> On Sep 22, 2020, at 5:28 PM, tutilu...@tutanota.com wrote:
> 
> I hate the "wrong tool for the job" argument. It assumes that everyone has 
> infinite time, energy and brain capacity to learn endless redundant tools 
> just to "use the right tool for the job" rather than "use what you actually 
> know". I know PG. I don't know SQLite. They are very different. So obviously, 
> I want to use PG.


https://duckdb.org/docs/why_duckdb

Maybe this would provide something in between. I have not used it, but it 
sounds interesting. Based on SQLite engine and uses the Postgres SQL parser. 
Lots of built-in functions appear to be the same or very similar to Postgres.


John DeSoi, Ph.D.