Stack Overflow
Stack Overflow

Questions tagged [postgresql-10]

for PostgreSQL questions specific to version 10

On 5 October 2017, PostgreSQL 10.0 was released. Major enhancements in PostgreSQL 10 include:

  • Logical replication using publish/subscribe
  • Declarative table partitioning
  • Improved query parallelism
  • Significant general performance improvements
  • Stronger password authentication based on SCRAM-SHA-256
  • Improved monitoring and control

Note that as of version 10, PostgreSQL has moved from 3 component version ids to 2 components, so 10.1 is a minor release.

The official documentation for this version is available at: http://www.postgresql.org/docs/10/static/index.html

632 questions
3
votes
1 answer

How to add an exclude constraint with an existing index with specified operator?

Assuming the following index: CREATE INDEX CONCURRENTLY blocked_date_idx_on_since_until_range ON blocked_date USING gist( tstzrange( "blocked_since", "blocked_until", '[]' ) ); Which is a range on 2 dates, I'd like…
Francesco Belladonna
  • 11,361
  • 12
  • 77
  • 147
3
votes
3 answers

How to use regexp_matches() in an UPDATE statement?

I am trying to clean up a table that has a very messy varchar column, with entries of the sorts: VA Lidar OR
Vlad
  • 387
  • 2
  • 11
3
votes
1 answer

PostgreSQL 10 Logical Replication: Double entries or primary key conflict

I have built a master -> slave setup with PostgreSQL 10 to sync data from some specific tables to the slave machine, which is the public system. Now during the whole time, I encounter some strange behaviour: Some tables do not sync at all. Looking…
resonic
  • 31
  • 3
3
votes
2 answers

Why is the query planner unable to transform a correlated subquery?

In How does PostgreSQL approach a 1 + n query?, I learned that a correlated subquery can be rewritten as a left join: select film_id, title, ( select array_agg(first_name) from actor inner join…
Jelly Orns
  • 197
  • 1
  • 2
  • 8
3
votes
2 answers

Calculating average time interval length

I have prepared a simple SQL Fiddle demonstrating my problem - In PostgreSQL 10.3 I store user information, two-player games and the moves in the following 3 tables: CREATE TABLE players ( uid SERIAL PRIMARY KEY, name text NOT…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
3
votes
0 answers

Race condition in querying over partitioned table in PostgreSQL?

Assuming PostgreSQL 10.3 and Read Committed: does a query over a partitioned table introduce a race condition in the case one of the partitions is modified by another process? E.g. given this schema: CREATE TABLE foo ( id VARCHAR, state…
Davide R.
  • 860
  • 5
  • 24
3
votes
2 answers

Posgres 10: "spoofing" now() in an immutable function. A safe idea?

My app reports rainfall and streamflow information to whitewater boaters. Postgres is my data store for the gauge readings that come in 15 minute intervals. Over time, these tables get pretty big and the availabity of range partitioning in Postgres…
Debaser
  • 427
  • 1
  • 5
  • 17
3
votes
0 answers

server closed the connection unexpectedly in postgresql while compiling plpytghon3u function

While defining a PL/Python function in psql, I get the error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The function: CREATE OR REPLACE…
Rahul Gour
  • 487
  • 2
  • 7
  • 21
3
votes
1 answer

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Postgres version: 9.3) Asking this after searching around without any solution: I create a simple materialized view (in practice, I have a few of them). psql my_db -c "CREATE MATERIALIZED VIEW my_view AS (SELECT * FROM my_table);" I call pg_dump…
gilad905
  • 2,842
  • 2
  • 16
  • 23
2
votes
1 answer

PostgreSQL idle_in_transaction_session_timeout seems to have no effect

I'm working with a PostgreSQL server version 10.5 (can't upgrade it at the moment) running in Docker. XL Deploy is connected to it and I upload a new archive that is 232MB. I get the error FATAL: terminating connection due to idle-in-transaction…
sffortytwo
  • 127
  • 1
  • 7
2
votes
1 answer

Optimize PostgreSQL query with levenshtein() function

I have a table with approximately 7 million records. The table has a first_name and last_name column which I want to search on using the levenshtein() distance function. select levenshtein('JOHN', first_name) as fn_distance, …
user1452701
  • 144
  • 1
  • 10
2
votes
0 answers

Azure Postgres AUTOVACUM AND ANALYZE THRESHOLD - How to change it?

I am coming again with another Postgres question. We are using the Managed Service from Azure that uses autovacuum. Both vacuum and statistics are automatic. The problem I am getting is that for a specific query, when it is running at specific…
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
2
votes
4 answers

Another postgres not starting: "could not map anonymous shared memory"

I noticed there are several questions about Postgres (10) not able to boot because of the shared memory; despite that I couldn't really make it run. Now everytime I try to start the cluster I keep getting this error: 2021-10-24 10:13:43.269 UTC …
Federico Loro
  • 65
  • 1
  • 6
2
votes
0 answers

postgreSQL slow query with high shared read

i have a problem with my postgreSQL database, actually i'm developer not DBA so i don't have enough experience to deal with problems like this. I ran this query at my psql: explain (analyze true, buffers true) select json_build_object('tracker_id',…
DrJempo
  • 75
  • 1
  • 9
2
votes
1 answer

How to automatically create a comment on CREATE in Postgres?

I would like to create a trigger which gets fired once a table is created in a schema. This trigger should add a comment to the newly created table, something like: CREATE TRIGGER my_trigger AFTER CREATE ON my_schema EXECUTE PROCEDURE…
JoeBe
  • 1,224
  • 3
  • 13
  • 28
1 2 3
…
42 43