Questions tagged [postgres-14]
35 questions
3
votes
3 answers
Postgres query planner won't use less expensive composite index
We've run into a strange issue with Postgres, initially unearthed as we prepare to upgrade from 10.21 to 11+. What we'd initially thought was a performance regression in newer versions of Postgres appears, in fact, to be a performance regression…
Sydin
- 607
- 5
- 14
2
votes
1 answer
Foreign key verification is very slow while creating empty partitions
I am using postgres 14 and dealing with multi-level partitioning.
An sample table design looks like :
Table A :
CREATE TABLE issue (
id bigserial,
catalog_id bigint …
harsh kumar
- 21
- 3
1
vote
1 answer
Postgres - Replace full String including occurances using regexp_replace
I am using Postgres regexp_replace method to :
Replace *.dataMain AS *__dataMain, full Row as Empty,
where * can be any alias for ex. m, n, m2, n1, etc any alphanumeric.
I have below sample data :
n.dataPrev AS n__dataPrev,
n.dataMain AS…
LuFFy
- 8,799
- 10
- 41
- 59
1
vote
1 answer
How are materialized CTE's stored in Postgres14
I was able to significantly speed up a query that looks something as follows:
WITH MATERIALIZED t1 as (
SELECT x1, x2 from v1 where v1.id = :id
) select * from t1
Here, v1 is a view (unmaterialized). My question, where is the materialized…
user5735224
- 461
- 1
- 7
- 16
1
vote
2 answers
Bad query plan on different boolean values
I have a table which is used as a job queue. New rows are continuously being inserted in the table and the fetched rows are updated. There are JOINs in the query and Postgres uses Nested Loop join for it. Around 400 rows are picked up from this…
Abdul Rauf
- 766
- 2
- 7
- 19
1
vote
0 answers
Postgres unable to create postgis extension
I currently working to install PostgreSQL (v14.4) with the PostGIS (v3.1.4) extension from source using MinGW. I was able to build out all of the dependencies and the build the extension. When I connect to the db, I see that the PostGIS extension is…
amerature_dev
- 11
- 1
0
votes
1 answer
how to deploy the spring boot war on tomcat?
I am trying to make the war file of the spring boot application in vs code using this command
mvn clean
mvn clean install
or
mvn clean install
or
mvn package
when i am deploying the war on tomcat and check into the browser and hit this url…
Vikki Tyagi
- 1
- 2
0
votes
2 answers
Postgres query involving joins and count too slow
This query below takes way too long to execute
Number of records in each table:
stories - 416,355 records
event_relations - 24,050,862 records
districts - 10 records
industries - 100 records
industry_districts - 40 records
subsectors - 200…
Code
- 9
- 3
0
votes
0 answers
Postgres 14, which data type should I use to handle monetary requirements for an application that handle integration with marketplaces
I have an application that handle integration among marketplaces. Precision is very desirable because, for example, one customer may buy 1099 times a product costing 0.93. Resulting in 1022.07. Afterwards I will have to make some statistics for…
Diego Alves
- 2,462
- 3
- 32
- 65
0
votes
0 answers
Postgres DB - Using JPA StoredProcedureQuery invoke Stored Procedure in Postgres DB which has OUT Parameter as Ref Cursor
Please do not confuse this with Function, its specifically for Stored Procedure.
Need to call a Stored Procedure in Postgres DB using JPA's StoredProcedureQuery. Stored Procedure has OUT parameter as REF Cursor.
CREATE OR REPLACE PROCEDURE…
Vishwanath Joshi
- 129
- 2
- 8
0
votes
1 answer
View (and subquery) returns rows without filtering
Postgresql 14.
Hi! I have multiple tables that are named like model_ and each table has columns that are named like element_. All of these tables are in models schema. And I have query that transform information_schema.columns view in…
Prosto_Oleg
- 322
- 3
- 13
0
votes
0 answers
Firing triggers for individual changes as they arrive on the subscriber
I am trying to have a trigger refresh a materialized view when a subscriber table gets updated.
In the Postgresql Docs, under typical use-cases for logical replication lists "Firing triggers for individual changes as they arrive on the…
Ben S
- 1
- 1
0
votes
0 answers
Postgres password and user change in Plausible Analytics with Postgres:14-alpine docker image
Is there a way to initially provide with custom db, user and password for postgres during installation. I am using self-hosted docker installation.
If I try to give custom user and password, I get an error Password Authentication failed for user…
Rahul Beelur
- 1
- 1
0
votes
1 answer
Will a column be dropped if deleted it from information_schema.columns?
I use Postgres 14. I know about ALTER TABLE DROP COLUMN. But this option doesn't really work in my case.
Are these two queries equal:
ALTER TABLE
DROP COLUMN IF EXISTS ,
DROP COLUMN IF EXISTS ;
ALTER TABLE…
Prosto_Oleg
- 322
- 3
- 13
0
votes
1 answer
Insert/delete rows in batches with plpgsql script
I need to partition a table with ~400M records. It had several pitfalls and setbacks due to DB resources, WAL sizes etc. After different approaches the solutions seems to be this:
create child tables for every month in the scope, representing the…
Bylaw
- 5
- 6