Questions tagged [postgresql-10]
PostgreSQL version 10
348 questions
0
votes
2
answers
434
views
Postgres - how to work out which tablespace is short of space?
I'm trying to add a INTEGER DEFAULT 0 field to a very large (partitioned) table spread across two tablespaces.
Each tablespace is on a different disc (one on C drive, the other on D). I get the ...
- 1,313
2
votes
2
answers
640
views
Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?
I've got a table with ~2 billions rows of data, and I would like to make another table with some aggregates. It looks like PostgreSQL uses temporary disk space for these queries. I can create the ...
- 305
3
votes
1
answer
2k
views
IN clause makes query slower
I have a question about the performance tuning of in clause in sql query. The Postgres version is 10.18.
I did two queries below. The difference of performance of those two queries are huge. For sql, ...
- 31
0
votes
1
answer
2k
views
Investigating which operation is causing high CPU usage on PostgreSQL - How?
Today we had an incident in production which started as a 100% CPU usage on our RDS followed by a general performance degradation. It took us several hours of investigation on the PostgreSQL side to ...
- 103
0
votes
1
answer
690
views
PostgreSQL stops continuous recovery without error
I have some Postgresql 10 instances running on Windows Server that are in continuous recovery mode. Once in a while they just stop recovering without giving any errors, like in this example log file (...
- 43
0
votes
0
answers
371
views
Error- Index is not a b-tree postgres
In our production, environment suddenly we recieved error that index is not a btree for one index.After performing reindex on the same, we were able to access the table, But we are unable to get root ...
- 1
0
votes
0
answers
179
views
Suddenly TPS is too high like 90K to 3000K but active session is 250
My DB application connected with pgBouncer and pgpool
Node info:
3 Pgbouncer
3 pgpool
3 dbnode (1 is primary 2 is standby)
For pgbouncer: default_pool_size=200 ,reserve_pool_size=10
For pgpool: ...
4
votes
1
answer
13k
views
How do you reset a serial type back to 0 after deleting all rows in a table?
I previously asked this question , and somebody posted a very helpful answer on db fiddle here. This has worked really well for me. However, now I would like to ask a new (related) question :
Suppose ...
- 463
1
vote
1
answer
1k
views
how to optimize multiple unions for the same table with different condition into a single query
Environment : PostgreSQL v10.6 (AWS RDS instance)
For the given table:
CREATE temp TABLE lve
("fin" timestamptz, "vehiculo" int, "equipo" int)
;
INSERT INTO lve
...
0
votes
0
answers
435
views
I have an issue with postgresql, something is preventing the postgres to startup. I cant figure out the solution for this issue
I have an issue with postgresql, something is preventing the postgres to startup in the cloud server. I cant figure out the solution for this issue.
here i attached the log file.
2021-12-18 10:19:08....
0
votes
0
answers
102
views
How to drop a table with invalid block in pg_attribute?
QGIS is unable to access one my DB.
Tracking the issue, I found that the broken relation was pg_attribute.
From there, I managed to identify the table causing the issue.
select * from pg_attribute ...
- 65
1
vote
0
answers
200
views
How to write the query in postgres in which i have to join two tables with specific condition?
I have to build a query the example fiddle is given here.
In this ctx.params.user_id Is a variable
The conditions which i have to satisfy is as follows.
If the value in effective_employee_type = ALL ...
- 69
0
votes
0
answers
320
views
pg_ctl error when using pg_upgrade
We are attempting to upgrade Postgres 10.10 to 12.4 using pg_upgrade on a Windows 2016 Server machine using the command
"C:/Program Files/PostgreSQL/12/bin/pg_upgrade.exe”
--old-datadir "C:/...
- 1
1
vote
0
answers
685
views
FK constraint ON DELETE RESTRICT not working on some database instances
My database instances appear to treat FK constraint ON DELETE RESTRICT differently and I don't know why. On production database instance (and a clone, where I do my testing), I believe the RESTRICT is ...
- 111
0
votes
1
answer
2k
views
Create table with foreign keys blocked by other Table-level Lock
I'm not pretty sure it is database question or development-process question, but I will kindly give it a try.
When creating a new table B with foreign keys to table A, the operation tries to acquires ...
- 145