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 doesn

Re: pg_restore 12 "permission denied for schema" errors

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 template1, right? No --- my ears went u

Re: pg_restore 12 "permission denied for schema" errors

Hi Ron, Am 20.10.22 um 02:58 schrieb Ron: RDS Postgresql 12.11 Backed up a database from one RDS instance, and now am trying to restore it to a new instance.  (If you're wondering why, it's to restore in an instance with less disk space.) Here are the commands: export PGHOST=${RDSENV}.x

PG RPMS for RHEL 9 on aarch64?

I'm trying to get PostGIS installed for Postgresql 13 on an Oracle 9 / aarch64 box. PostgreSQL-server installs OK from the RPMS provided by RHEL/Oracle. However, the default repos don't include PostGIS. I usually install the RPMs from download.postgresql.org[1] anyway, but there are only RPMs

pg_restore 12 "permission denied for schema" errors

RDS Postgresql 12.11 Backed up a database from one RDS instance, and now am trying to restore it to a new instance.  (If you're wondering why, it's to restore in an instance with less disk space.) Here are the commands: export PGHOST=${RDSENV}..us-east-1.rds.amazonaws.com cd /mig

Re: Postgres 15 upgrades and template1 public schema

Bruno Wolff III writes: > On Wed, Oct 19, 2022 at 23:30:58 +0200, > Thomas Kellerer wrote: >> This is explained in the release notes: >> >> The change applies to new database clusters and to newly-created >> databases in existing clusters. >> Upgrading a cluster or restoring a database dump wi

Re: Postgres 15 upgrades and template1 public schema

On Wed, Oct 19, 2022 at 23:30:58 +0200, Thomas Kellerer wrote: Bruno Wolff III schrieb am 19.10.2022 um 22:36: I noticed when I did an upgrade from Postgres 14 to 15 that the public schema in template1 was still owned by postgres instead of pg_database_owner. I was expecting it to change beca

Re: Custom function ROWS hint ignored due to inlining?

Philip Semanchuk writes: > I have a custom function where the ROWS hint is getting ignored. I think it’s > because the function is getting inlined, but I’d like a second opinion. Yeah, I believe you're right about that. > I would prefer to have the function inlined for better performance, but >

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

This seems like a bad use of a stored procedure. Why wouldn't you spin up 40 clients with a table name and run it across 40 connections? But also, I don't like loops in stored procedures. Working with loops in a set based system hurts me but it's personal preference. Like, I could write this in py

Re: Column value derived from generated column in INSERT?

On Wed, Oct 19, 2022 at 12:34 PM Mark Raynsford wrote: > On 2022-10-19T11:58:07 -0700 > "David G. Johnston" wrote: > > > On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford < > co+org.postgre...@io7m.com> > > wrote: > > > > > insert into t (y) values (t.x * 2); > > > > > > I can think of various w

Re: Column value derived from generated column in INSERT?

> On 19/10/2022 23:51 CEST Adrian Klaver wrote: > > On 10/19/22 12:58 PM, Adrian Klaver wrote: > > On 10/19/22 12:48, Mark Raynsford wrote: > >> On 2022-10-19T12:43:31 -0700 > >> Adrian Klaver wrote: > >>> > >>> HINT:  There is an entry for table "t", but it cannot be referenced from > >>> this p

Re: Column value derived from generated column in INSERT?

On 10/19/22 12:58 PM, Adrian Klaver wrote: On 10/19/22 12:48, Mark Raynsford wrote: On 2022-10-19T12:43:31 -0700 Adrian Klaver wrote: HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query. HINT:  There is a column named "x" in table "t", but it canno

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

gogala.mla...@gmail.com schrieb am 19.10.2022 um 01:46: Amazon, lead by Kevin Closson, the guy who has famously designed Oracle Exadata among other things, even came up with the recipe how to migrate it to Postgres: https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-t

Re: Postgres 15 upgrades and template1 public schema

Bruno Wolff III schrieb am 19.10.2022 um 22:36: I noticed when I did an upgrade from Postgres 14 to 15 that the public schema in template1 was still owned by postgres instead of pg_database_owner. I was expecting it to change because the release notes said that new database clusters would have

Re: Cannot restore windows dump on linux

> On 19/10/2022 17:43 CEST ertan.kucuko...@1nar.com.tr wrote: > > I am using PostgreSQL 14.5 > > I tried to move a Linux database to Windows. Both identical version. > Linux dump successfully loaded on Windows system. > Reason for changing system didn’t work out and now I am trying to move it back

Postgres 15 upgrades and template1 public schema

I noticed when I did an upgrade from Postgres 14 to 15 that the public schema in template1 was still owned by postgres instead of pg_database_owner. I was expecting it to change because the release notes said that new database clusters would have that. But shouldn't new clusters use what is set

Re: Speeding up adding fky on a very large table

Our issue is that it takes 20hrs to index the full table. Hopefully we can add FK in multiple child partitions concurrently, otherwise doing it per partition offers no advantage from performance pov. Need to test. Hopefully PG should not lock the referred table during the first build, stopping c

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

It seems that I made a thread-discipline error when I asked a question that had nothing to do with the frequency, or the cost, of committing when I saw this reply (paraphrased for brevity here) from Christophe: > You [cannot] commit in [a] BEGIN / END [block statement] that has an > exception h

Re: Column value derived from generated column in INSERT?

On 10/19/22 12:48, Mark Raynsford wrote: On 2022-10-19T12:43:31 -0700 Adrian Klaver wrote: HINT: There is an entry for table "t", but it cannot be referenced from this part of the query. HINT: There is a column named "x" in table "t", but it cannot be referenced from this part of the query.

Re: Column value derived from generated column in INSERT?

On 2022-10-19T12:43:31 -0700 Adrian Klaver wrote: > > HINT: There is an entry for table "t", but it cannot be referenced from > this part of the query. > > HINT: There is a column named "x" in table "t", but it cannot be > referenced from this part of the query. Yes, I saw those, hence asking

Re: Column value derived from generated column in INSERT?

On 10/19/22 12:30, Mark Raynsford wrote: On 2022-10-19T11:58:07 -0700 "David G. Johnston" wrote: On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford wrote: insert into t (y) values (t.x * 2); I can think of various ways to do it with multiple statements, but a single statement would be pref

Re: Column value derived from generated column in INSERT?

On 2022-10-19T11:58:07 -0700 "David G. Johnston" wrote: > On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford > wrote: > > > insert into t (y) values (t.x * 2); > > > > I can think of various ways to do it with multiple statements, but a > > single statement would be preferable. > > > > > No, b

Custom function ROWS hint ignored due to inlining?

Hi, I have a custom function where the ROWS hint is getting ignored. I think it’s because the function is getting inlined, but I’d like a second opinion. Here’s my working (contrived) example. CREATE TABLE my_table ( id int primary key GENERATED ALWAYS AS IDENTITY, base_value int NOT NUL

Re: Column value derived from generated column in INSERT?

On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford wrote: > insert into t (y) values (t.x * 2); > > I can think of various ways to do it with multiple statements, but a > single statement would be preferable. > > No, by extension of the documented constraint: "The generation expression can refer t

Column value derived from generated column in INSERT?

Hello! I'd like to be able to do something sort of analogous to this: create table t ( x integer not null generated always as identity, y integer not null ); insert into t (y) values (t.x * 2); In the real project, the "t.x * 2" expression is obviously something a lot more comple

RE: Cannot restore windows dump on linux

Hello again, While I was searching for a solution, I saw an example of manual running of dump file within psql. Tried this and it did work just fine. \connect mydb \i last_backup.bak Above two commands completed without any loading error at all. Now I wonder why command line did not work. Do le

Cannot restore windows dump on linux

Hello, I am using PostgreSQL 14.5 I tried to move a Linux database to Windows. Both identical version. Linux dump successfully loaded on Windows system. Reason for changing system didn't work out and now I am trying to move it back because it has some modifications. I just dumped a datab

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

On 2022-Oct-19, Dominique Devienne wrote: > Anybody has an answer to my question regarding how substr() works on > bytea values? I.e. is it "pushed down" / optimized enough that it > avoids reading the whole N-byte value, to then pass it to substr(), > which then returns an M-byte value (where M

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

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 option, I'm afraid. You should reconsider. Ruling out that opti

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

On 10/19/22 06:38, Andreas Joseph Krogh wrote: På onsdag 19. oktober 2022 kl. 13:21:38, skrev Dominique Devienne : On Wed, Oct 19, 2022 at 1:00 PM Andreas Joseph Krogh wrote: > Ok, just something to think about; Thank you. I do appreciate the feedback. > Will your databas

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

On 10/19/22 04:47, Dominique Devienne wrote: [snip] PS: Another drawback of lo is that because it's a single table, it still subject to the 32 TB limit on a relation. The 4TB limit per lo is way more than we need, but the 32 TB limit may actually be more of an issue for our larger clients,

Re: Speeding up adding fky on a very large table

On 10/19/22 08:31, Ravi Krishna wrote: AWS Aurora based on PG 13. Large partitioned table of 5+ billion rows and 7TB in size. ALTER TABLE abc ADD CONSTRAINT fk_123 FOREIGN KEY (a,b,c) REFERENCES xyz(1,2,3); It seems this is not parallelized. Is there a way. Or directly going into each part

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

On 10/19/22 08:06, Mladen Gogala wrote: [snip] Applications do this kind of thing all the time, very successfully; it was just that the loop was in the application rather than in the procedure. High commit rates happen all the time, and they don't break PostgreSQL.  For example, an IoT app

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

Dominique Devienne wrote: > PostgreSQL bytea is much better and simpler, except limited to 1GB... > Bytea also has no direct random access, except via substr[ing], but > how efficient and "random access" is that? Bytea contents are compressed before being sliced (in chunks of TOAST_MAX_CH

Re: Is this error expected ?

On 10/19/22 01:50, Yavuz TANRIVERDİ wrote: |Hi,| |i have an | | |"ERROR: UNION types "char" and text cannot be matched CASE WHEN indisprimary THEN"|| |error from | |https://github.com/yiisoft/yii/blob/e7c298343bf1f76186d443b62ff853d2d36e19f0/framework/db/schema/pgsql/CPgsqlSchema.php#L233| |I r

Re: byte-size of column values

Dominique Devienne writes: > On Tue, Oct 18, 2022 at 6:04 PM Tom Lane wrote: >> Thus, there's always a header to store the actual length. That can >> be either 1 or 4 bytes (I think the doc you are looking at might be >> a little out of date on that point). > Even the doc on v15 (or devel) stil

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

On Wed, Oct 19, 2022 at 3:05 PM Alvaro Herrera wrote: > On 2022-Oct-19, Dominique Devienne wrote: > > OTOH, lo has random access, which I also need... > > Generally speaking, bytea sucks for random access, because if a TOAST > item is compressed, it has to be always read from the beginning in orde

Speeding up adding fky on a very large table

AWS Aurora based on PG 13. Large partitioned table of 5+ billion rows and 7TB in size. ALTER TABLE abc ADD CONSTRAINT fk_123 FOREIGN KEY (a,b,c) REFERENCES xyz(1,2,3); It seems this is not parallelized. Is there a way. Or directly going into each partition is the only way ( not even sure it is po

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

On Wed, Oct 19, 2022 at 3:01 PM Daniel Verite wrote: > Dominique Devienne wrote: > > the fact the lo table is unique for the whole database would allow > > users to see blobs from any schema, as I understand it. > Each large object has its own set of permissions. This is a significant > d

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

Comments in-line. On Tue, 2022-10-18 at 21:02 -0700, Christophe Pettus wrote: > > > > On Oct 18, 2022, at 19:18, gogala.mla...@gmail.com wrote: > > > > Commit within a loop is an extremely bad idea. > > This is an over-generalization.  There are many use-cases for this > (if there were not, pr

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

On 2022-Oct-19, Dominique Devienne wrote: > Upfront, I have to state that I'm not keen on lo, because of security > considerations. We store blobs in many different schemas, and users > can access some schemas, and not others. So the fact the lo table is > unique for the whole database would allow

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

Dominique Devienne wrote: > the fact the lo table is unique for the whole database would allow > users to see blobs from any schema, as I understand it. Direct access to pg_largeobject is only possible for superusers. If lo_compat_privileges is on, any user can read any large object with

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

We had the same thought of storing the blobs inside LO’s as well many years ago. But ultimately chose cloud storage and stored a pointer in the database instead. Now that we are approaching a terabyte of just normal data I don’t regret this decision one bit. Just handling backups and storage is

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

On Wed, Oct 19, 2022 at 1:38 PM Andreas Joseph Krogh wrote: > There's a reason “everybody” advices to move blobs out of DB, I've learned. > I get that. I really do. But the alternative has some real downsides too. Especially around security, as I already mentioned. That's why I'd like if possibl

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

På onsdag 19. oktober 2022 kl. 13:21:38, skrev Dominique Devienne < ddevie...@gmail.com >: On Wed, Oct 19, 2022 at 1:00 PM Andreas Joseph Krogh wrote: > Ok, just something to think about; Thank you. I do appreciate the feedback. > Will your database grow beyond 10T

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

On Wed, Oct 19, 2022 at 1:00 PM Andreas Joseph Krogh wrote: > Ok, just something to think about; Thank you. I do appreciate the feedback. > Will your database grow beyond 10TB with blobs? The largest internal store I've seen (for the subset of data that goes in the DB) is shy of 3TB. But we are

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

På onsdag 19. oktober 2022 kl. 12:48:24, skrev Dominique Devienne < ddevie...@gmail.com >: On Wed, Oct 19, 2022 at 12:17 PM Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: First advice, don't do it. We started off storing blobs in DB for “TX safety” Not re

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

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 option, I'm afraid. > , but backup/restore quickly became too cumbersome so we ended up moving > all blobs out and only store reference

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

First advice, don't do it. We started off storing blobs in DB for “TX safety”, but backup/restore quickly became too cumbersome so we ended up moving all blobs out and only store reference in DB. This required us to make a “vacuum system” that cleans up the blob-storage regularly as ROLLBACK/c

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

Hi. I'd like some advice storing blobs (millions of them), ranging from very small, to large > 1GB. I know about bytea versus lo, and I have probably read most of what's out there about them :) Upfront, I have to state that I'm not keen on lo, because of security considerations. We store blobs in

Re: byte-size of column values

On Tue, Oct 18, 2022 at 6:04 PM Tom Lane wrote: > Dominique Devienne writes: > > I'm surprised by the result for bit(3) and char > > The doc does mention 5-8 bytes overhead, but I expected > > those for varying bit, not fixed-sized bit typed values. > > Your expectation is incorrect. Postgres al

Re: byte-size of column values

On Tue, Oct 18, 2022 at 6:04 PM David G. Johnston wrote: > On Tue, Oct 18, 2022 at 8:53 AM Dominique Devienne > wrote: >> I'm surprised by the result for bit(3) and char, when calling >> pg_column_size(). > The base type is what matters, if the length of the actual type is a parameter > (the (

Re: Is this error expected ?

Ok, thank you very much, On Wed, Oct 19, 2022 at 10:15 AM Laurenz Albe wrote: > On Wed, 2022-10-19 at 09:50 +0300, Yavuz TANRIVERDİ wrote: > > i have an > > "ERROR: UNION types "char" and text cannot be matched CASE WHEN > indisprimary THEN" > > error from > > > https://github.com/yiisoft/yii/b

Re: Is this error expected ?

On Wed, 2022-10-19 at 09:50 +0300, Yavuz TANRIVERDİ wrote: > i have an > "ERROR: UNION types "char" and text cannot be matched CASE WHEN indisprimary > THEN" > error from > https://github.com/yiisoft/yii/blob/e7c298343bf1f76186d443b62ff853d2d36e19f0/framework/db/schema/pgsql/CPgsqlSchema.php#L2