Questions tagged [postgresql-11]
PostgreSQL version 11
221
questions
3
votes
1
answer
86
views
Is it safe to use default value with not null when adding a new column?
We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below:
ALTER TABLE "blogs" ADD "published" ...
- 235
0
votes
0
answers
51
views
Clear database cache in SQL Server and also Postgresql
My current tasks involve optimizing queries for both SQL Server (using TSQL language) and PostgreSQL.
There are ways to run large batches of the same query to get timing results.
However, it appears ...
- 125
0
votes
1
answer
39
views
No speedup using parallel restore with pg_restore
I need a large database like 10TB-15TB in Postgresql for benchmarking.
I've created a smaller ~100GB sample databases with pgbench like this:
pgbench -i -s 7000 --no-vacuum exampledb
However, I ...
- 411
0
votes
1
answer
53
views
Immutable dynamic sql functions
Scenario #1
Basically, lets say we have a function that's using dynamic SQL but, also know for a fact that it will always return the same output for the same input and declare it immutable will it ...
- 934
0
votes
0
answers
35
views
UPDATE columns using JSON data
I have found two solutions that update columns based on JSON data. The first solution is based on CTE:
WITH list AS (
SELECT * FROM json_populate_record
(null::tsk,'{"tsk_id":"2",&...
- 156
1
vote
0
answers
68
views
Error "set-valued function called in context that cannot accept a set"
I have sql script which runs correctly on postgres 11.3 but not on 13.4
I have about 5 tables, used in script - code below
SELECT "enterprise_workplace"."id",
"...
1
vote
1
answer
31
views
Postgresql (11.9) LIKE query not using text_ops index
I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals:
CREATE ...
- 111
0
votes
1
answer
72
views
Postgres optimizing IN queries
I am trying to optimize the 'IN' QUERY in Postgres 11.
I tested 3 approaches:
Case 1:
DELETE FROM mytable WHERE
id1='fffe9411-3b9d-40dc-9cc6-14407785be8b'
and
id2 IN ('00000140-1ae9-41f7-9614-...
0
votes
1
answer
41
views
logical replication. How do I change the subscriber to new master?
I'm migrating Linux distros and need to migrate the Postgres 11 to new master. I can do a streaming replication and have little to no downtime or do a dump. That I got down.
My problem is how do I ...
1
vote
3
answers
699
views
PostgreSQL 11.9 Unable to restart the server with error -- pg_ctl: directory "/var/lib/postgresql/11/main" is not a database cluster directory
We have been trying hard to restart the DB cluster server which was shutdown after power failure. We have tried systemctl , pg_ctl and pg_ctlcluster commands but all the time we are getting same error....
- 11
0
votes
0
answers
28
views
Bucardo Race Condition
I have an application that uses multiple PostgreSQL databases and Bucardo to sync between the various master/slave nodes. I'm seeing a race condition where bucardo dies on a master node and doesn't ...
1
vote
1
answer
298
views
Why is my spatial query slower in Postgres 13 than in Postgres 11?
Postgres versions
PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
- 113
0
votes
0
answers
54
views
A strange index that is always marked pg_index.indisvalid=false . Why indexes with ON ONLY option are always rebuilt in invalid state?
I'm looking in database (postresql 11.10) and see an index on a partitioned table that marked as invalid:
SELECT
indrelid::regclass, indexrelid::regclass, indisvalid, ddlx_create_index(...
- 203
1
vote
0
answers
66
views
PostgreSQL 11 - Tuning of the background writer process (maxwritten_clean)
I notice that the metric maxwritten_clean in the table pg_stat_bgwriter increases (for example going from 0 to 1000 in several seconds). I guess this is understandable as it happens while there are ...
- 87
1
vote
1
answer
73
views
Help understanding behavior of try_cast() with numeric strings
Erwin made a great, generic try_cast function for postgres (copied below). I'm interested in using it for an EAV schema where attributes are stored with a type field. I can use a similar function (...
- 13
2
votes
0
answers
323
views
Postgresql 11: terminating walsender process due to replication timeout
I have found some questions about the same error, but didn't find any of them answering my problem.
The setup is that I have two Postgres11 clusters (A and B) and they are making use of publication ...
- 31
1
vote
0
answers
29
views
How to design a table schema to optimize insert and updates for a unique numeric column
Suppose there is a table with 3 columns
id, name, score
Name and score are both unique columns.
The use case is that,
The table is pre-populated with 10 rows. Scores are set from 1-9.
A user can ...
- 11
1
vote
0
answers
27
views
Add Unique constraint to existing table with large amount of data
I have an existing table with following schema
create table event_logs
(
event_id integer not null
constraint event_logs_event_id_fkey
references event,
user_id ...
- 111
0
votes
0
answers
51
views
How to Properly Configure a Time History Table?
I'm migrating a poorly planned table (in an Azure PostgreSQL db) that stores time history data, and I'm a bit overwhelmed. This history table has a composite primary key on 5 columns, 4 of which are a ...
- 13
0
votes
1
answer
662
views
PostgreSQL 11: terminating walreceiver process due to administrator command
one of my PostgreSQL 11 servers was throwing a strange error and refused to correctly initiate streaming replication after a basebackup:
FATAL: terminating walreceiver process due to administrator ...
- 15
1
vote
1
answer
50
views
PostgreSQL 11 - Can I turn a schema into the wild west?
I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, ...
- 23
3
votes
1
answer
42
views
Why do two queries run faster than combined subquery?
I'm running postgres 11 on Azure.
If I run this query:
select min(pricedate) + interval '2 days' from pjm.rtprices
It takes 0.153 sec and has the following explain:
"Result (cost=2.19..2.20 ...
- 601
0
votes
1
answer
41
views
Configure listen_addresses automatically in custom installation
I have included Postgres in a custom installer for a Windows application. I do not use the installer binary, but extract the Postgres zip archive for installation and then run initdb from a batch ...
- 111
1
vote
1
answer
207
views
When changing the default port number from 5432 to 5332, why do I get a unix domain socket error?
I successfully followed the Postgres 11 installation instructions on Centos 7 found here. I am able to successfully create schemas / tables and insert data into the database over port 5432 with no ...
- 329
4
votes
2
answers
3k
views
How to set statement timeout per user?
I have multiple users in Postgres. I would like to set up different statement timeouts for different users.
Eg: Guest 5 minutes and Admin 10 minutes.
Is it possible in Postgres 11.11?
0
votes
0
answers
193
views
Postgresql, joining most recent row from one table to another based on dates
We have several tables we have to bring together to match the most recently effective price with the transaction date. The simplified query is shown below:
SELECT t.transaction_id,
price.*
...
- 151
0
votes
1
answer
58
views
Run DELETE on each INSERT to enforce row limit
Using PostgreSQL 11.
Consider a table like
CREATE TABLE "logs"
(
"id" INTEGER NOT NULL,
"userId" INTEGER NOT NULL,
"timestamp" TIMESTAMP ...
- 289
2
votes
1
answer
47
views
Why does PostgreSQL accept junk when parsing an INTERVAL
psql (11.8)
Type "help" for help.
public=# select '!@#$%^&*()00:00:00.01<>?<>:'::interval;
interval
-------------
00:00:00.01
(1 row)
Seems to take just about anything......
- 169
0
votes
1
answer
45
views
Problem calling Postgres function
I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, ...
4
votes
1
answer
878
views
What is the correct way to make an exact copy of a database with pg_dump?
Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc.
Is this doable with pg_dump or does psql ...
- 303
0
votes
1
answer
19
views
In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables?
Ι have the following view:
CREATE OR REPLACE VIEW public.my_view as
SELECT
col1,
col11
from
table_a JOIN table_b on table_a.id=table_b.a_id
join
(
select id,col56 from table_c where ...
1
vote
1
answer
108
views
Same postgres query in two different instances with the same data but with different times
I have the same postgres query running in two different instances restored with the same dump file:
one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-...
- 11
0
votes
1
answer
157
views
How does Postgres decide if to use additional workers?
I have the following table with an added BTREE-index on "captured_at".
CREATE TABLE datagram
(
id bigserial NOT NULL,
src_re integer ...
1
vote
1
answer
31
views
Having regex_matches exclude cases where both capturing groups match a 0-length string
I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so:
I'm looking to split sequences that begin with a positive or ...
- 115
1
vote
0
answers
235
views
Postgres row estimate is very low for hash join
I have a query that keeps thinking that the result of a hash join will be 1 and I don't know which knob to turn to fix it.
The query takes a form field values in a revision of a document and maps them ...
- 353
0
votes
1
answer
38
views
How I can check whether 2 databases despite being in different postgresql servers of versions are the same?
I copied an existing posttgresql database from one server to another. The source of my data is a postgresql 9.6 whilst the newer server is in postgresql 11.
So I want to know how I can check that both ...
0
votes
1
answer
68
views
Multicolumn GIN index not fully facilitated by query planner
I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage.
After defining an index on two columns (a,b) of a large table and executing a query ...
- 157
1
vote
1
answer
240
views
PostgreSQL undocumented timenow() function?
Looking at the documentation of PostgreSQL I couldn't find anything on timenow(). Yet if I call the function it works.
So what is the difference between now() and timenow() ?
I am going to guess that ...
- 934
4
votes
2
answers
758
views
Postgres 11: Query plan uses seq scan after upgrade
The Situation
We have a database hosted on RDS with a few hundred tables, a few of which are quite large.
We recently upgraded the database from 9.5.22 to 11.8 and performance is significantly ...
- 63
0
votes
1
answer
393
views
Unable to upgrade to postgresql 13
I'm getting the following error:
postgres@:~/>cat loadable_libraries.txt could not load library
"$libdir/plpython2": ERROR: could not access file
"$libdir/plpython2": No such ...
0
votes
1
answer
110
views
How can I trace the cause of a memory spike in Postgres?
The title tells the story, but how can I trace the cause of a memory spike in Postgres 11.10? As you can see from the screen shot below there was a memory spike all the way up to 32 GB which caused ...
user219430
0
votes
0
answers
30
views
Is it possible to record read access to a row?
A table has a creation timestamp column with DEFAULT and an update timestamp column with DEFAULT ... ON UPDATE .... Similar to the ON UPDATE|DELETE ... constraint, we're looking for a way to have a ...
- 101
0
votes
1
answer
25
views
Using activity over last few days to label user as "active"
Here's an example dataset.
with activity_cte (day, user_id, act1, act2) as (
values
('2020-01-01'::date, 1, 0, 1),
('2020-01-01'::date, 3, 1, 0),
('2020-01-02'::date, 1, 3, ...
- 115
1
vote
0
answers
774
views
Error that role of the previous master user does not exist when restoring a database to a different server with different master username
I have to move a postgres database to another server. Both servers are Postgres 11.8 on Amazon RDS. I'm dumping the original jira database like this:
pg_dump \
--dbname=postgresql://postgres:***@...
- 111
1
vote
1
answer
467
views
PostgreSQL extremely slow counts
I'm experiencing extremely slow count(*) speeds, inside a table with 5845897rows, counting the number of rows takes around 2 minutes (113832.950 ms).
EXPLAIN ANALYZE SELECT COUNT(*) FROM campaigns;
...
- 111
1
vote
0
answers
14
views
How I can find whitch reports use views that are generated from specific tables?
I have the following table named reports:
report_code PK VARCHAR 5
report_sql TEXT
The report_sql stores the sql code that generates a report. The sql code is in postgresql flavor. So from that ...
0
votes
2
answers
176
views
search path is getting updated automatically
In postgres 11.5 for a running session suddenly lot of errors reported in pg_logs "relation does not exist". After enabling extra logging we have found out that just before the errors the ...
- 125
0
votes
2
answers
84
views
What SQL execution time should I expect when querying 4k records in a 150M records table?
We currently have performance issues with a SQL query on RDS Postgres 11.8. Here is the query:
SELECT id FROM answer_submission WHERE student_id = 3983137;
The table "answer_submission" has ...
1
vote
0
answers
73
views
Reduce Count(*) time for many records [closed]
Query:
EXPLAIN ANALYZE select count(*) from product;
ROWS: 534965
EXPLANATION :
Finalize Aggregate (cost=53840.85..53840.86 rows=1 width=8) (actual time=5014.774..5014.774 rows=1 loops=1)
-> ...
- 121
5
votes
1
answer
155
views
Determine Nodes in Network with PostgreSQL
I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
- 53