Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.6]

for PostgreSQL questions specific to version 9.6

On 29 September 2016, PostgreSQL 9.6.0 was released. Major enhancements in PostgreSQL 9.6 include:

  • Parallel sequential scans, joins and aggregates

  • Elimination of repetitive scanning of old data by autovacuum

  • Synchronous replication now allows multiple standby servers for increased reliability

  • Full-text search for phrases

  • Support for remote joins, sorts, and updates in postgres_fdw

  • Substantial performance improvements, especially in the area of improving scalability on many-CPU servers.

Use this tag for questions specific to any of the above new features.

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

767 questions
8
votes
2 answers

Postgresql | No space left on device

I am getting space issue while running a batch process on PostgreSQL database. However, df -h command shows that machine has enough space below is the exact error org.springframework.dao.DataAccessResourceFailureException:…
YogeshR
  • 1,144
  • 1
  • 15
  • 27
8
votes
1 answer

Optimizing a postgres similarity query (pg_trgm + gin index)

I have defined the following index: CREATE INDEX users_search_idx ON auth_user USING gin( username gin_trgm_ops, first_name gin_trgm_ops, last_name gin_trgm_ops ); I am performing the following query: PREPARE…
Anentropic
  • 26,635
  • 9
  • 86
  • 130
8
votes
1 answer

PostgreSQL: Encrypt Column With pgcrypto

I need to encrypt some columns in a PostgreSQL 9.6 database. The data being encrypted is inherently sensitive; however, the data are not passwords or other authentication credentials. This data will need to be decrypted for statistical analysis and…
losthorse
  • 1,450
  • 13
  • 31
7
votes
1 answer

How can I fire a trigger at the end of a chain of updates?

I have a couple of tables that interact with each other using triggers, and the current way I've been handling the trigger execution uses pg_trigger_depth() < 2 which is ugly. I really want the final trigger to run only once and at the end after all…
deinspanjer
  • 451
  • 6
  • 19
7
votes
1 answer

Postgresql array && operator performing slower than expected

Sample data I've got 2 tables that I need to join on column that holds array of integers. CREATE TABLE table_array_1 (key1 int, values1 int[]); CREATE TABLE table_array_2 (key2 int, values2 int[]); One table has small amount of rows but large array…
Łukasz Kamiński
  • 4,657
  • 1
  • 13
  • 27
6
votes
1 answer

Build predicates for a postgres jsonb column with criteria builder using JPA criteria

I need to add a predicate to my list of existing predicates for a JSONB column. Entity: @Entity @Table(name = "a") @TypeDefs({ @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class), }) public class EntityA { @Id …
Niv
  • 205
  • 6
  • 15
6
votes
0 answers

pg_restore FATAL: terminating connection because protocol synchronization was lost

I have a 10G directory pg dump and I'm trying to restore it with the following command: docker-compose exec db pg_restore --user=postgres -C -c -O -F d -j 4 -d MyDB /tmp The restore appears to be working for around 3 minutes, but then exits with the…
five_dollar_shake
  • 348
  • 1
  • 10
6
votes
3 answers

PostgreSql : ERROR: relation "sequence" does not exist while restoring from dump file

I get the following error while restoring database from dump file on server: ERROR: relation "table_id_seq" does not exist LINE 1: SELECT pg_catalog.setval('table_id_seq', 362, true); my local psql version is 10.2 server psql version is…
Saly
  • 1,016
  • 1
  • 10
  • 18
6
votes
1 answer

Globally replace in Postgres JSONB field

I need to globally replace a particular string that occurs multiple places in a nested JSON structure, thats stored as jsonb in a postgres table. For example: { "location": "tmp/config", "alternate_location": { "name": "config", …
crowhoplaminar
  • 113
  • 1
  • 6
6
votes
1 answer

How to use an array as a variable in Postgres?

I have this .sql script: DO $$ DECLARE user_list integer[] = (select user_id from user where state = 'ACTIVE'); BEGIN CREATE CREATE MATERIALIZED VIEW accounts_with_active_users AS select * from accounts where user_account IN…
AndreFontaine
  • 1,258
  • 1
  • 12
  • 27
6
votes
2 answers

array_agg with distinct works in postgres 9.4 but not in postgres 9.6

I have a query that use array_agg with distinct as an argument and is not accepted on postgres 9.6. I created this sample to illustrate the issue: create table numbers (id integer primary key, name varchar(10)); insert into numbers…
Browser_80
  • 131
  • 1
  • 5
6
votes
0 answers

PostgreSQL write amplification

I'm trying to find how much stress PostgreSQL puts on disks and results are kind of discouraging so far. Please take a look on methodology, apparently I'm missing something or calculating numbers in a wrong way. Environment PostgreSQL…
icuken
  • 1,096
  • 7
  • 9
5
votes
1 answer

When and how does Postgres use "transactionid" locks

I've ran into a need to figure this out on 9.6, but information on anything 9.6 or later would be appreciated. I'm having an issue with my application being blocked on a database call because it's attempting to acquire an implicit transactionid lock…
Pawel Veselov
  • 3,527
  • 4
  • 32
  • 55
5
votes
1 answer

Materialized view using a function using a temporary table

I have a stored procedure, which uses a temporary table in its body. Trying to create a materialized view using this procedure, like CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_function; Gives me an error: ERROR: cannot create temporary…
Przemek
  • 5,540
  • 10
  • 38
  • 59
5
votes
2 answers

Temporary tables bloating pg_attribute

I'm using COPY to insert large batches of data into our database from CSVs. The insert looks something like this: -- This tmp table will contain all the items that we want to try to insert CREATE TEMP TABLE tmp_items ( field1 INTEGER NULL, …
Joel
  • 7,162
  • 6
  • 50
  • 87
1
2
3
…
51 52