Re: System (and DB) upgrade problem.

[Laurenz Albe]

On Wed, 2021-01-13 at 07:41 +0100, W.P. wrote:
> I am upgrading Fedora 24 to (now) 26, PostgreSQL stopped starting (as 
> expected), the message from systemctl was to do "postgresql-setup 
> --upgrade".
> 
> Did installed the tool, loaunched.
> 
> But it fails with (attached logs) messages, I am missing some libraries, 
> what should I install?

The logs complain about missing the "dblink" library, so you forgot to install
the -contrib package.

Remember to REINDEX all indexes on string columns, because the collations
may have changed.

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

Re: Define hash partition for certain column values

[Голубева Яна]

Sorry, I think I 've described my case not precisely enough. "Randomly" is not pure random in my case. My solution is planning to be used on different servers with different DBs. The initial data of the base table depends on the DB. But I know that the key value of the new rows is increasing. Not monotonously, but still.I need a common solution for all DBs. The size of a base table could be very different (from millions to hundreds of billions). For tests I've used 2 different dumps. Ranges that were suitable for the first dump show for the second the situation like I've described (2-3 partitions with 95% of data). And vice versa.Besides, permanent increasing of key value of new rows means that some ranges will be permanently increasing meanwhile others will have the same amount of data or even less (outdated data is clearing). Hash partitioning shows that I will have partitions with not exactly the same size of data but similar enough. And this result is actual for both dumps. So that I've decided to use hash partitioning. Thank you,Iana Golubeva12.01.2021, 19:41, "Michael Lewis" :On Tue, Jan 12, 2021 at 9:37 AM Alban Hertroys  wrote:
> On 12 Jan 2021, at 16:51, Голубева Яна  wrote:
> 
> Values for the key partitioning column are generated randomly and I can't predict their distribution between ranges.
> If I just create some ranges I won't have any guarantee that partitions will have similar amount of data. It is possible that I will have 2 or 3 extremely big partitions and a bit of data in others.

A hash of a random number is also random, so when using hashes for partitioning you will get the same problem.

If you want to distribute values equally over a fixed number of partitions, I suggest you partition on a modulo of a monotonously increasing number (a sequence for example), instead of relying on a random number.

> 12.01.2021, 17:55, "Michael Lewis" :
> On Tue, Jan 12, 2021 at 1:21 AM Голубева Яна  wrote:
> List or range partitioning isn't suitable for my case.
> I am using a column of numeric(20) type as a base for partitioning. The values of the column are generated randomly. 
> So there will be too many partitions if I use list partitioning as is.
> 
> Sorry, but why is range not suited for this? It would seem fairly trivial to create 50 or 1000 partitions to break up the range of values allowed by your field definition.

Alban HertroysThat said, there is no reason you should need near-perfectly-even distribution anyway. You can also split partitions later, or do another level of partitioning on large partitions if they somehow end up significantly unbalanced.How many rows are we talking about initially/over time? Do you plan to drop old data at all? Perhaps the initial decision to partition was decided on a bit too hastily.

Docker Image with Python support ?

[Markur Sens]

Is there any available Postgres image that has been compiled with the
--with-python flag?

I could use and modify (comment out this)
https://github.com/docker-library/postgres/blob/03e769531fff4c97cb755e4a608b24935c27/13/alpine/Dockerfile
but looks like too much for a simple flag.

Re: How to keep format of views source code as entered?

[Adam Brusselback]

> Admittedly, the system probably should be made to save the text, should
someone wish to write such a patch.

Just wanted to throw $0.02 behind this idea if anyone does want to take it
up later. Using a source control system is better obviously. But even if
you use source control it is still incredibly annoying you cannot compare
the view you have in source control to the view definition in PG and tell
if it's the same or changed. It has been a major annoyance for views with
complex subqueries or where clauses, the PG representation is absolutely
unreadable.

-Adam

Re: How to keep format of views source code as entered?

[Laurenz Albe]

On Wed, 2021-01-13 at 20:39 -0500, Adam Brusselback wrote:
> > Admittedly, the system probably should be made to save the text, should 
> > someone wish to write such a patch. 
> 
> It has been a major annoyance for views with complex subqueries or where 
> clauses, the PG representation is absolutely unreadable.

This is not going to happen, and I dare say that such a patch would be rejected.

Since PostgreSQL stores view definitions in their parsed form, the query does
not contain the name of the used objects, but only their object ID.

This allows you for example to rename the underlying objects, because that
does not change the object ID:

CREATE TABLE t (id integer);

CREATE VIEW v AS SELECT * FROM t;

\d+ v
[...]
View definition:
 SELECT t.id
   FROM t;

ALTER TABLE t RENAME TO quaxi;

\d+ v
[...]
View definition:
 SELECT quaxi.id
   FROM quaxi;

If PostgreSQL were to store the original text, either that text would become
wrong, or you would have to forbid renaming of anything that is referenced
by a view.

A database is no source versioning system.  The next thing someone will request
is that the original CREATE TABLE or CREATE INDEX statements should be 
preserved.

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

Re: How to keep format of views source code as entered?

[David G. Johnston]

On Wed, Jan 13, 2021 at 6:40 PM Adam Brusselback 
wrote:

>  It has been a major annoyance for views with complex subqueries or where
> clauses, the PG representation is absolutely unreadable.
>

The path to a solution here is to write a schema-to-text system that
presents the derived output in a more human-friendly way instead of a
machine-readable way.  Or maybe write a formatter that takes the supposedly
unreadable output and does stuff like "change 'character varying' to
'text'".

In any case, though, what you can do is install the view in source into a
database, dump both, compare both (same version of PG), and decide whether
the database version is different from the source control version and, if
so, decide how to update the database.  It's a view, it's not like you get
to do "alter" incremental changes anyway.

Or hold your nose and adopt a path of least resistance - accept what is
easy to accomplish and be glad you aren't writing more user-friendly stuff,
but that is only cosmetically different, yourself.  Learning what the
canonical outputs mean is annoying but not hard, especially if you do have
an original human-readable document to answer questions.

David J.

Re: How to keep format of views source code as entered?

[Paul Förster]

Hi Laurenz,

> On 14. Jan, 2021, at 04:59, Laurenz Albe  wrote:
> 
> If PostgreSQL were to store the original text, either that text would become
> wrong, or you would have to forbid renaming of anything that is referenced
> by a view.

this is why views, procedures, functions and packages can become invalid in 
Oracle, which I really hate because as a DBA, it's almost impossible to quickly 
see (or in fact see at all) why this happens, or having to debug applications 
that you don't know and/or can't fix anyway. Oracle's invalid object concept 
doesn't make sense.

So, I'm not at all in favor of saving the original statement text.

Cheers,
Paul