Questions tagged [postgresql-14]
Use this tag if your question is about PostgreSQL version 14 specific feature or behaviour. Please also add the generic [postgres] tag.
108 questions
1
vote
0
answers
36
views
the different partion by fields value but get the greater than 1 row number when using row_number [closed]
I want to keep the first row of dulplicate records in PostgreSQL 14, this is the sql look like:
WITH xzqh_info AS (
SELECT
xzqhdm,
xzqhmc
FROM
area_table
...
- 939
0
votes
0
answers
30
views
how to avoid the postgresql 14 shared buffer swap out
I am facing a issue that the sql first time query was slow. I read the execution plan and found out the index are not in shared buffers the first time query. then I prewarm the index like this:
SELECT ...
- 939
1
vote
0
answers
52
views
how to make the execution plan as same with the actual query in PostgreSQL 14
When I use the execution plan in PostgreSQL 14, I found the execution plan much faster than the actual query, this is how I generate the execution plan:
explain (analyze true, timing true) long sql...
...
- 939
0
votes
0
answers
41
views
is it possible to make the CTE parallel in PostgreSQL 14
I have an analyse query in PostgreSQL 14 that use many CTEs, like this:
with
a0 as (select count(*) from ccc0) ,
a1 as (select count(*) from ccc1) ,
a2 as (select count(*) from ccc2) ,
a3 as (...
- 939
0
votes
0
answers
21
views
postgresql row number function with large amount of result
I have a sql like this in PostgreSQL 14:
SELECT
biz_id
login_time,
ROW_NUMBER() OVER (PARTITION BY biz_id ORDER BY login_time DESC) AS priority_rn
FROM example
then I ...
- 939
0
votes
0
answers
36
views
Postgres - would multiple triggers for different record types on the same table improve performance?
I have a trigger currently defined like this...
CREATE TRIGGER trig1
AFTER INSERT ON public.posMessage
FOR EACH ROW EXECUTE PROCEDURE public.parsemess();
The procedure parsemess() will parse one of ...
- 1,313
1
vote
1
answer
71
views
Poor performance bucketing historical data with DISTINCT
I'm running into performance problems with a query that buckets a set of historical records of daily counts into a form that can be rendered easily as a daily graph.
Context
Essentially, the table in ...
- 111
1
vote
1
answer
88
views
Adding a col to PostgreSQL database caused indefinite hang
I recently deployed a migration to production that added a new column to a table. The migration was very simple and should have only locked the table for milliseconds:
ALTER TABLE "table_1" ...
- 11
0
votes
1
answer
285
views
PostgreSQL throwing "53100: could not extend file "base/xxxxx/xxxxx" with FileFallocate()" despite ample space in the volume
I recently upgraded PostgreSQL 14 to PostgreSQL 16 using --clone method. Both my data directories (old & new cluster) were on same volume. After few days, I started receiving below error.
53100: ...
- 135
0
votes
1
answer
125
views
Postgres Replication to existing active database server
I'm working on a large project that includes some postgres database work, but I'm not very familiar w/ the details of postgres. Basically, I need to set-up a read-only DB that is a copy of our ...
Egyas
0
votes
1
answer
61
views
PostgreSQL one to many relationship transposing second table
I have two tables, for sake of simplification let's call them employees and employee_comms:
employee:
id
name
dept
1
Jane
Maintenance
2
Joe
Design
employee_comms:
id
contact_id
comms_type
comms_value
...
- 3
2
votes
1
answer
565
views
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, <more...>
FROM tbl
INNER JOIN UNNEST($1) ...
- 123
0
votes
0
answers
50
views
Issues with High Write Transactions in PostgreSQL Cluster with Pgpool Load Balancer
I am currently managing a 3-node PostgreSQL asynchronous streaming replication cluster setup, which includes one master node and two standby nodes, all sitting behind a pgpool load balancer. During ...
0
votes
1
answer
130
views
How to connect/take over a Postgresql instance without any login?
I have inherited a Postgresql server but have no records of the admin credentials or any user account.
I do have local admin access to the Windows VM that hosts this Postgresql server.
How can I gain ...
- 3,592
0
votes
0
answers
33
views
Change cipher PBEWithMD5AndDES
I am using PostgreSQL 14. I created a database, set up a user, and performed a migration.
Now, the database is connected to the application, and a security test was conducted.
I have been tasked with ...
- 45