Skip to main content

Questions tagged [postgresql-15]

Use this tag if your question is about a PostgreSQL version 15 feature or behaviour. Please also add the generic [tag:postgres] tag.

Filter by
Sorted by
Tagged with
0 votes
0 answers
10 views

How to best set value of identity sequence for GENERATE BY DEFAULT column when supplying identity value when inserting?

I have a table with an identity column that generates by default. I need to be able to write specific values to the identity column due to logically replicating the table, so hence why using GENERATE ...
Applez00800's user avatar
0 votes
0 answers
13 views

How to use replicate tables with NULLable composite unique identifier fields?

I will preface with saying that I'm no SQL expert. I have tables that exists in multiple databases in a hub and spoke style architecture where there are Machine DBs which logically publishes their ...
Applez00800's user avatar
0 votes
0 answers
32 views

Logical replication fails with "publisher did not send replica identity column expected by the logical replication target relation <table>"

I have two postgre databases where one is set to publish a table and the other subscribes to it for logical replication. This is setup in PgAdmin. This is a simple test case, and the table i'm ...
Applez00800's user avatar
0 votes
1 answer
49 views

postgres 15: removing create privileges on public not working

Unfortunately, all our production and stage databases use the public schema. I am trying to create a read only user which can only read data, specifically not insert or or delete data, or create or ...
John Little's user avatar
0 votes
0 answers
40 views

Postgres Bad Planner Estimates on Join

I have four tables in a model hierarchy: customer, subscription, billing_cycle, and product_version. Every object in that hierarchy has a foreign key to its parent object. There are a number of ...
Ben H's user avatar
  • 1
0 votes
1 answer
47 views

Are default values for a column still required if using audit triggers?

I have a couple of audit columns in my table: modified_by modified_date I have an AFTER trigger set up to automatically update these columns whenever data is inserted or modified, with current_user ...
greenandblue's user avatar
0 votes
1 answer
40 views

Postgres list all roles with permission to execute a function

I would like to list my functions & stored procedures along with the roles that are able to execute them. I can get similar information on tables via SELECT grantee , CONCAT(table_schema, '.', ...
beehive's user avatar
  • 137
2 votes
1 answer
340 views

Column used in the publication WHERE expression is not part of the replica identity

I'm trying to create a logical replication setup where I have a single large database with smaller read replicas branching of filtered on a sub id. For example, if I wanted a subset of items pushed to ...
goldfishalpha's user avatar
2 votes
2 answers
469 views

Improve query performance of filtered left outer join of big tables

I am trying to optimize a query that joins two large tables (40MM+ rows) in PostgreSQL 15.4. SELECT files.id, ARRAY_AGG(b.status) FROM files LEFT OUTER JOIN processing_tasks b ON (files.id = b....
Jeff G's user avatar
  • 123
0 votes
0 answers
40 views

Duplicating (but not using) a materialized CTE makes the query perform as it should

I have a large query with many CTE's and joins, and once I changed the amount of data in the database (from 20k rows to 10k), it started performing horribly: https://explain.depesz.com/s/TSu3 ...
localhost's user avatar
  • 101
1 vote
0 answers
96 views

Postgres 15 doesn't use index under RLS

Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4 and noticed extremely high disk consumption on the following query execution: select (exists (select 1 as "one" from &...
Alexander Ok.'s user avatar
1 vote
0 answers
27 views

PostgreSQL - Table partitioned on date doesn't prune partitions when querying with a daterange

Our tables are partitioned by month on the dt date column CREATE TABLE IF NOT EXISTS ta ( link_dir text COLLATE pg_catalog."default" NOT NULL, dt date NOT NULL, tod time without ...
raphael's user avatar
  • 549
0 votes
0 answers
35 views

How to continuous wal archive and replay from one master to another master in PostgreSQL?

Basically I have 3 node BD cluster with 3 PgPool in total 6 node. I also made another cluster of 6 node to backup of 1st 6 node cluster. 2nd 6 node cluster will be for reporting purpose. How can I ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
302 views

Database owner unable to create tables under Postgres 15 (RDS)

I realise a change was introduced relating to default permissions in Postgres 15: https://www.postgresql.org/about/news/postgresql-15-released-2526/, specifically PostgreSQL 15 also revokes the ...
whoasked's user avatar
  • 251
0 votes
0 answers
530 views

TimeZone Setting On PostgreSQL Databases

I have a Patroni cluster with two Ubuntu 22 db server. The timezone of my instances was set to UTC. I have needed to edit them to set the local timezone to Europe/Istanbul. I have made the timezone ...
user avatar
0 votes
1 answer
31 views

Recommendation on storing 3D flight data in PostgreSQL. Use POINTZM?

I am looking to store latitude, longitude (both decimal) and altitude (meters) for use with flight data in PostgreSQL 15. I have decided on using the POINT data type in PostgreSQL for latitude and ...
Justin's user avatar
  • 449
0 votes
1 answer
17 views

PostgreSQL - Spiting table into two (current and history)

I am looking at a legacy application that manages objects stored in a PostgreSQL database. Each object has multiple property sets that are stored as JSONB objects. Each of those property sets ...
George G's user avatar
3 votes
0 answers
146 views

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/...
simonw's user avatar
  • 43
1 vote
1 answer
221 views

Slower than expected query performance on Postgres

I just moved a FrontEnd web from querying a small Redshift Cluster to Postgres, looking for, primarily getting rid of Redshift but mostly to get better performance and user experience from a reporting ...
Alejandro's user avatar
  • 113
1 vote
1 answer
1k views

Modeling Polymorphic Relations in Postgres 15 with bi-directional (cyclic) FK constraints

I am drawn to this design like a moth to a flame. I've read rumblings that cyclic foreign keys are a nightmare. Generally, I can see why they should be avoided. In this particular case, however, I don'...
untitled90's user avatar
1 vote
1 answer
270 views

PostgreSQL lock contention on pg_database

We run a write-heavy application backed by PostgreSQL. Our mainline has been running against PostgreSQL 11 for years and we have started rolling out a major update that runs against PG 15. I am ...
jdx's user avatar
  • 13
0 votes
0 answers
223 views

Postgres causing kernel panic?

Not sure if this should have been a stackoverflow question or dba.stackexchange, but here it is. I have 2 servers both of them have Postgresql15-server installed. Both the nodes share the disk, which ...
Abhishek B's user avatar
2 votes
1 answer
171 views

random_page_cost and memoize plan relation

Postgres 15.2. I have the following simple query: SELECT mark.last_modified FROM mark INNER JOIN element ON mark.element_id = element.id INNER JOIN model ON element.model_id = model.id WHERE ...
Cowabunga's user avatar
  • 135
5 votes
1 answer
1k views

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 ...
soliz's user avatar
  • 293
1 vote
0 answers
473 views

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 ...
Ravers UA's user avatar
0 votes
0 answers
266 views

Psql error when attempting to restore schema backup to Postgres database

I am trying to clone a schema in my remotely hosted Postgres 15 database, without affecting the original schema. I decided to do this in the method outlined here in the most-upvoted answer, i.e. using ...
user274130's user avatar
3 votes
0 answers
225 views

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 ...
user297020's user avatar
3 votes
0 answers
97 views

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 ...
jde0503's user avatar
  • 31
0 votes
0 answers
235 views

Postgres logical replication gets stuck at synchronized 's' state and does not move to 'r' state

I am currently trying out logical replication in postgres for upgrading postgres between major versions. Some tables which are part of a subscription gets stuck at 's' state of pg_subscription_rel. I ...
Padma govind's user avatar
0 votes
1 answer
39 views

Indexing on ANY queries

I have a table that I am trying to query. Here is the create statement for the table CREATE TABLE IF NOT EXISTS public.TableName ( i integer NOT NULL, t integer NOT NULL, v ...
optimcode's user avatar
  • 101
0 votes
1 answer
59 views

PostgreSQL doesn't use grant for a view

I have a view called viw_insideworktime and it makes a SELECT for the worktime table, however when trying to select the error below appears: SELECT * FROM viw_insideworktime; ERROR: permission denied ...
Tom's user avatar
  • 428
0 votes
1 answer
58 views

Is UPSERT feature complete?

Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value. Say we have a table of businesses and some ...
Ken Lyle's user avatar
3 votes
1 answer
6k views

"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 ...
MrPowerGamerBR's user avatar
7 votes
3 answers
3k views

Why not use two columns instead of collation for case insensitive data?

I need to store emails (like '[email protected]') in Postgres 15, be able to search them in case-insensitive manner ('[email protected]', '[email protected]', etc are the same), and be able to retrieve the ...
IvanD's user avatar
  • 311
25 votes
3 answers
40k views

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 ...
Miqueias Kevison's user avatar
2 votes
1 answer
381 views

Error while creating continuous aggregate (materialized view) when source and new time columns have the same name (PSQL/TSDB)

I'm facing a problem when creating a continuous aggregate on timescaledb (based on materialized views) when the source time column and the new time column have the same name like below: CREATE ...
Héctor's user avatar
  • 297
3 votes
1 answer
2k views

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 ...
Héctor's user avatar
  • 297
0 votes
1 answer
518 views

How to keep Image data in file system with Postgres database?

I am wondering is there any way to store image type data (binary data) in postgresql not directly into the columns but resides in the file system but not gonna store the path link as text in database ...
Saadman Karim's user avatar
-1 votes
1 answer
426 views

Use pgdump sql file to upgrade version?

Can I use a pgdump .sql file generated by one version as a way to upgrade to another version? For example: If I created a backup on PostgreSQL 14 with: pg_dump --no-privileges --no-owner $DB_NAME > ...
Michael's user avatar
  • 153
1 vote
3 answers
1k views

Set custom constant in Postgres script

In Postgres 15, how can we define constants that pertain to business logic rather than db configuration settings? I'd like to define them within SQL scripts rather than passed through the psql -v ...
eliangius's user avatar
  • 145
3 votes
1 answer
383 views

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 ...
Tom's user avatar
  • 131
0 votes
0 answers
346 views

Understanding memory situation for tuning a PostgreSQL database on Ubuntu 22.04.1 LTS

I'm trying to better understand my performance / tuning options for a PostgreSQL database. System: Ubuntu 22.04.1 LTS aarch64, 80 cores, 256 GB RAM PostgreSQL 15 Right now, the shared buffer size is ...
Ralf's user avatar
  • 101
0 votes
2 answers
269 views

way to get system_identifier from files in data directory on disk

Context Using postgresql, I would like a way to make sure that the server I am talking with over a pgsql connection is the one that serves a specific /path/to/data/ directory on the local server. I ...
LeGEC's user avatar
  • 105
3 votes
2 answers
298 views

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 ...
mesr's user avatar
  • 133
3 votes
0 answers
1k views

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 ...
tinlyx's user avatar
  • 3,620