Use this tag if your question is about a PostgreSQL version 15 feature or behaviour. Please also add the generic [postgres] tag.
Questions tagged [postgresql-15]
64 questions
39
votes
3 answers
Collation version mismatch
I replicated a database, it's working fine but when I enter the replication database it displays the following message:
The database was created using collation version 2.31, but the operating system provides version 2.35.
Rebuild all objects in…
Miqueias Kevison
- 750
- 1
- 5
- 9
7
votes
3 answers
Why not use two columns instead of collation for case insensitive data?
I need to store emails (like 'ivan@email.com') in Postgres 15, be able to search them in case-insensitive manner ('iVaN@email.com', 'IVAN@email.com', etc are the same), and be able to retrieve the original email to use it for actual sending of…
IvanD
- 311
- 2
- 9
5
votes
1 answer
How to determine the collation of an index in PostgreSQL?
In PostgreSQL 15, when an index is created with an implicit collation, the default collation used for the index is documented:
By default, the index uses the collation declared for the column to be indexed or the result collation of the expression…
soliz
- 293
- 1
- 10
4
votes
1 answer
"ERROR: could not access status of transaction Could not open file "pg_xact/0029": No such file or directory" when attempting to LISTEN
When attempting to LISTEN to any channel, PostgreSQL says that Could not open file "pg_xact/0029": No such file or directory.
This is a freshly restored database, so it can't be database corruption.
Changing to another database /c anotherdb works,…
MrPowerGamerBR
- 141
- 1
- 4
3
votes
0 answers
Postgres hot standby WAL history file mssing
I'm trying to create a hot standby. I've replicated the primary with pg_basebackup and configured recovery on the standby however the logs repeatedly show:
cp: cannot stat '/var/lib/postgresql/15/backup/00000002.history': No such file or…
simonw
- 43
- 3
3
votes
0 answers
Unable to Install PostGIS Extension for PostgreSQL 15 on Mac Book with M1 Chip and macOS Ventura 13.3.1
Issue: Unable to install PostGIS extension for PostgreSQL 15 on Mac Book with M1 chip and macOS Ventura 13.3.1.
Steps I have taken:
Installed PostgreSQL 15 using the terminal.
Installed PostGIS using the command brew install postgis.
Tried…
Ravers UA
- 31
- 1
3
votes
0 answers
PostGres with 62 GB of table gobbling up over 500 GB on disk? SOLVED
Solution (short version):
Coding error in the SQL used by the feeder apps caused "no transaction in progress" warnings (not errors!) that were logged every time an insertion into the tables happened. The log folder ballooned to 400 GB.
Solution…
user297020
- 31
- 2
3
votes
0 answers
How to create a view that returns all child records in a single row without aggregation?
I am an electrical engineer developing a parts library database and explorer tool for internal use in my organization. If anyone is familiar with OrCAD schematic capture tools, I am essentially recreating Cadence's CIS (Component Information System)…
jde0503
- 31
- 2
3
votes
1 answer
ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function
I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.
There is any workaround for this apart from making a function…
Héctor
- 307
- 5
- 14
3
votes
2 answers
Workaround logical replication & FOR EACH STATEMENT triggers
We are using logical replication to sync data into a table. Perfect solution so far!
The problem now is we need triggers on this subscriber table. Those triggers make heavy calculations (think of calculating balances and aggregated data from the…
Tom
- 131
- 3
3
votes
2 answers
How to prevent what looks like PostgreSQL faulty optimization inside a function?
I have defined a function that uses PostgreSQL's COPY facility for reading the output of a shell program which reads its input from stdin and returns a string through stdout. Because of the particular design of the COPY facility, I must first save…
mesr
- 133
- 4
3
votes
0 answers
Unable to locate package postgresql-server-dev-15 while installing PostgreSQL 15 using pdpg?
I am trying to upgrade PostgreSQL to version 15 but some packages from 14 seems to be missing. There was postgresql-server-dev-14 in PostgreSQL 14. Trying to upgrade, I tried:
sudo apt install postgresql-server-dev-15
but got:
Reading package…
tinlyx
- 3,810
- 14
- 50
- 79
2
votes
0 answers
Deadlock seems to occasionally and regularly lead to replication failure
We have two PostgreSQL databases running on RHEL virtual servers (PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit). Our production server/database which we'll call Simon runs Airflow to do ETL…
raphael
- 599
- 2
- 4
- 20
2
votes
2 answers
(non-breaking space) is not considered whitespace by Postgres?
String-functions in Postgres do not recognize non-breaking-space as whitespace, neither when trimming nor when using regular expressions:
select 'x' || test || 'x'
, 'x' || trim(test) || 'x'
, 'x' || regexp_replace(test, '\s+', '') ||…
Rune
- 123
- 5
2
votes
1 answer
UNNEST vs ANY()
When selecting a number of records from a Postgres table based on a list of ids passed as a parameter, what are the benefits and drawbacks of
SELECT id,
FROM tbl
INNER JOIN UNNEST($1) AS id USING (id)
versus
SELECT id,
FROM…
Alex O
- 123
- 3