Questions tagged [postgres-11]
10 questions
4
votes
1 answer
Move rows older that x days to archive table or partition table in Postgres 11
I would like to speed up the queries on my big table that contains lots of old data.
I have a table named post that has the date column created_at. The table has over ~31 million rows and ~30 million rows older than 30 days.
Actually, I want…
user3281975
- 63
- 8
2
votes
2 answers
Transaction commit error - invalid transaction terminations
Can you please guide what is wrong with below code which gives an issue with below error:
ERROR: invalid transaction termination.
CREATE OR REPLACE PROCEDURE "app"."sproc_data_create"
( name varchar(100))
LANGUAGE plpgsql
AS
$$
Declare idIdentity…
dsi
- 3,199
- 12
- 59
- 102
1
vote
1 answer
First column value from multiple rows in postgres
I am using postgres 11, and have below table
+----------+-------------------+-----------+-----------------------------+
| username | filters | flag | time …
name_doesnt_matters
- 789
- 2
- 12
- 34
0
votes
0 answers
postgres does not use the available index
I have a large table with events, on which I have a couple of queries. These queries are way too slow for what they do. I have simplified one of the queries to this:
select distinct status_id FROM events e;
I know there are only 4 distinct…
Paul S
- 434
- 4
- 13
0
votes
0 answers
Performance side effects to promoting a Postgres RR to a standalone DB?
I promoted a Postgres 11.16 RDS cluster secondary to function as a stand-alone database. This caused a significant decrease in the RR’s performance. Digging deeper, when comparing the results of running Postgres EXPLAIN on a single production read…
Toaster
- 1,911
- 2
- 23
- 43
0
votes
0 answers
How to install libreadline.so.7 on Ubuntu 22
I have an Ubuntu 22.04 machine where I need to install PostgreSQL 11.9 and its client (psql). Psql requires libreadline.so.7 and it is not available in the Ubuntu repositories. Only libreadline.so.8 is present in the /usr/lib/x86_64-linux-gnu…
Salvatore D'angelo
- 1,019
- 3
- 14
- 39
0
votes
1 answer
sequence sync option with pg_dump
I use pg_dump to backup and restore to target and manually sync (using setval function) sequences on tables. Is following an option to avoid manual sync or manual sync is always required?
pg_dump -s (schema-only) + pg_dump -a (only data from…
Falcon
- 47
- 6
0
votes
1 answer
Postgres date_trunc function with timezone
Im using a C# query that automatically uses the date_trunc with timezone function, however when trying to run the function in postgres it doesnt work. I receive this error:
ERROR: function date_trunc(unknown, timestamp with time zone, unknown) does…
TheProgrammer
- 1,314
- 5
- 22
- 44
0
votes
0 answers
Postgres11 vacuum taking days
I have set maintenance_work_mem 50GB and trying to run 200GB non partitioned table with 10 indexes manually. I am wondering it is taking 3 days still not completed vacuum.
I know, these many indexes bad but not understanding why vacuum not…
satya prakash
- 21
- 3
0
votes
0 answers
Postgres Stored Procedure Transaction and Exception handling
Hi I have more used with SQL procedure. Just trying to achieve below scenarios.
In Postgres Stored Procedure,
do we need to commit if there are multiple insert statements
OR By default Postgressql handle commit and no need to mention (if we don't…
dsi
- 3,199
- 12
- 59
- 102