Re: High CPU usage

On 2022-10-20 15:59, ertan.kucuko...@1nar.com.tr wrote: Hello, I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very high CPU usage on my Linux system as below PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 2357756 postgres 20 0 2441032

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far

High CPU usage

Hello, I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very high CPU usage on my Linux system as below PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 2357756 postgres 20 0 2441032 2,3g 4 S 298,7 67,9 2114:58 Tspjzj2Z I could not

Re: PG RPMS for RHEL 9 on aarch64?

On Wednesday, October 19, 2022 11:46:06 PM PDT Johannes Münch wrote: > Hi Ben, > > > I faced the same issue few weeks back and opened a ticket for this, > please see below. > > https://redmine.postgresql.org/issues/7724 > > It seems like they now have a RHEL 9 aarch64 build machine but I was >

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

> On Oct 20, 2022, at 11:45, Vince McMahon > wrote: > > Thanks. I guess it is time for vacuum.  Ironically, vacuum is the main issue. The main reason that you can't just slap a few gigabytes onto the end of a table is that the next time vacuum runs, it will happily truncate it all

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

Thanks. I guess it is time for vacuum.  On Thu, Oct 20, 2022, 13:34 Christophe Pettus wrote: > > > > On Oct 20, 2022, at 10:29, Ron wrote: > > There's no pre-allocation in Postgresql. > > This is correct in that you can't say "please allocate 3GB for this table > for me now." The number of

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

> On Oct 20, 2022, at 10:29, Ron wrote: > There's no pre-allocation in Postgresql. This is correct in that you can't say "please allocate 3GB for this table for me now." The number of pages that are added to the end of a file isn't always one though; it can allocate more based on recent

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

On 10/20/22 12:31, Rob Sargent wrote: On 10/20/22 11:29, Ron wrote: On 10/20/22 12:19, Vince McMahon wrote: In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth?

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

On 10/20/22 11:29, Ron wrote: On 10/20/22 12:19, Vince McMahon wrote: In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth? There's no pre-allocation in Postgresql. 

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

On 10/20/22 12:19, Vince McMahon wrote: In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth? There's no pre-allocation in Postgresql.  When you run CREATE TABLE, an (I

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far

Does standalone postfrsql have autogrowth/ manual growth on table space?

In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth?

Re: Explain returns different number of rows

> On Oct 20, 2022, at 09:52, Vince McMahon > wrote: > The number of rows are different. This isn't unexpected. EXPLAIN does not actually run the query and determine how many rows are returned; it calculates an estimate based on the current system statistics, which vary constantly

Explain returns different number of rows

I did get reply so I am trying again. I executed the following statements 3 times > explain(analyze, buffet) select * from table1 > > The number of rows are different. Is the table corrupted? How to confirm > and how to fix it? >

Re: Creating LATIN9 database with PostgreSQL 15 on Windows

Answering to myself: I was using pgAdmin to create the database, and this tool does not show much collations. I could create my database from the command line: createdb --username=postgres --port=5436 --template=template0 --encoding=latin9 --locale=English_US.28605 test2 Stop me if I am

Creating LATIN9 database with PostgreSQL 15 on Windows

Hi! On my Windows 10 with PostgreSQL 15, I try to create a database with LATIN9 encoding (for testing purpose), but I have only the choice to select the C, POSIX and English_United States.1252 collations... C and POSIX are compatible with LATIN9, but SQL functions like UPPER() fail.

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far as I know your pg_restore command would

MERGE output doubt

Hi all, I'm experimenting with the new MERGE command, but I'm not getting the output total count. From the docs : "The total_count is the total number of rows changed ". This is my simple setup: testdb=> table scores; pk | name | score

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far as I know your pg_restore command would import the data into

Re: How to store "blobs" efficiently for small and large sizes, with random access

On Thu, Oct 20, 2022 at 12:21 PM Laurenz Albe wrote: > On Thu, 2022-10-20 at 10:32 +0200, Dominique Devienne wrote: > > FWIW, when Oracle introduced SecureFile blobs years ago in v11, it > > represented a leap forward in > > performance, and back then we were seeing them being 3x faster than LO >

Re: How to store "blobs" efficiently for small and large sizes, with random access

On 10/20/22 03:32, Dominique Devienne wrote: [snip] And from the posts here, the issues with large blobs may be more related to backup/restore perhaps, than runtime performance. From my long experience as a first a programmer and then a database administrator, backups and archiving are at the

Aw: Re: How to store "blobs" efficiently for small and large sizes, with random access

> I don't know what exactly they are, but I suspect that they are just > files (segments?) in Oracle's "file system" (tablespaces/datafiles). > So pretty much what we recommend. Maybe so, but if those large segments are presented "seamlessly" in the form of a table integrated with PGs

Re: How to store "blobs" efficiently for small and large sizes, with random access

On Thu, 2022-10-20 at 10:32 +0200, Dominique Devienne wrote: > But before I finish this thread for now, I'd like to add that I > consider unfortunate a state of affairs where > NOT putting the data in the DB is the mostly agreed upon advice. It > IMHO points to a weak point of > PostgreSQL, which

Re: How to store "blobs" efficiently for small and large sizes, with random access

På torsdag 20. oktober 2022 kl. 10:32:44, skrev Dominique Devienne < ddevie...@gmail.com >: On Wed, Oct 19, 2022 at 5:05 PM Laurenz Albe wrote: > On Wed, 2022-10-19 at 12:48 +0200, Dominique Devienne wrote: > > On Wed, Oct 19, 2022 at 12:17 PM Andreas Joseph Krogh

Re: How to store "blobs" efficiently for small and large sizes, with random access

On Wed, Oct 19, 2022 at 5:05 PM Laurenz Albe wrote: > On Wed, 2022-10-19 at 12:48 +0200, Dominique Devienne wrote: > > On Wed, Oct 19, 2022 at 12:17 PM Andreas Joseph Krogh > > wrote: > > > First advice, don't do it. We started off storing blobs in DB for “TX > > > safety” > > Not really an

Re: How to store "blobs" efficiently for small and large sizes, with random access

On Wed, Oct 19, 2022 at 5:30 PM Alvaro Herrera wrote: > That's exactly what I was trying to say. > If there's no compression, we don't read prior chunks. Great to read that. I'll probably try to benchmark w/ and w/o compression eventually. Need to deal with other issues first, will take a while

Re: How to store "blobs" efficiently for small and large sizes, with random access

On Wed, Oct 19, 2022 at 4:29 PM Daniel Verite wrote: > In your case I would probably opt for bytea (as opposed to large > objects), and slicing the blobs in the application in chunks Thanks for the advice, and the valuable info on LO permissions. --DD

Re: pg_restore 12 "permission denied for schema" errors

Hi Tom, Am 20.10.22 um 07:12 schrieb Tom Lane: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far as I know your pg_restore command would import the data

Re: PG RPMS for RHEL 9 on aarch64?

Hi Ben, I faced the same issue few weeks back and opened a ticket for this, please see below. https://redmine.postgresql.org/issues/7724 It seems like they now have a RHEL 9 aarch64 build machine but I was told it would take some time for the packages to be built. Joe On 10/20/22 5:13

Re: Postgres 15 upgrades and template1 public schema

On Wed, Oct 19, 2022 at 19:59:52 -0400, Tom Lane wrote: The release notes could probably use some tweaking here. It looks to me like pg_dumpall (and hence pg_upgrade) will adjust the ownership and permissions of template1's public schema to match what was in the old installation, but it