Stack Overflow
Stack Overflow

Questions tagged [postgresql-12]

For PostgreSQL questions specific to version 12.

PostgreSQL 12 is a major release of the PostgreSQL RDBMS.

Improvements include, but are not limited to:

  • performance improvements for partitioning and indexes
  • CTEs (WITH queries) are by default inlined for better query performance
  • JSON path queries per SQL/JSON specification
  • support for case- and accent-insensitive ICU collations
  • (stored) generated columns
  • extended statistics for “most common values”
  • page checksums can be enabled and disabled without initdb
  • authentication: support for encrypted GSSAPI authentication and LDAP server discovery
  • “pluggable storage” to add different table storage methods

More information regarding the release is available here.

554 questions
1
vote
1 answer

Correct way of installing postgresql extension with custom library

TL;DR One has to compile their custom library as shared library: gcc -c -fPIC warp_client.c -o warp_client.o gcc -shared warp_client.o libwarp-client.so Include the shared library and additional dependencies of that shared library in the…
Hyrikan
  • 59
  • 7
1
vote
0 answers

Installing PL/Python2 on Ubuntu for PostgreSQL 12

PL/Python, and specifically PL/Python 2, is a requirement for the MADlib extension - which appears to support Postgres 9.2+. I am using Postgres 12 on Ubuntu 20.04. Unfortunately, while I have symlinked Python2.7 to be the target for generic python,…
LanceRD
  • 51
  • 2
1
vote
0 answers

Postgres 12 Extensions Not Installed on RHEL 7

Looking to install the pgstattuple extension on my Postgres 12 DB on RHEL 7 to see index fragmentation. Currently I only have one extension installed and that's the plpgsql extension. Executing 'CREATE EXTENSION pgstattuple' results in the…
Jack Allen
  • 103
  • 1
  • 7
1
vote
1 answer

What are the difference between background writer and checkpoint in postgresql?

As per my understanding checkpoint write all dirty buffer(data) periodically into disk and background writer writes some specific dirty buffer(data) into disk It looks both do almost same work. But what are the specific dirty buffer(data) writes…
Sheikh Wasiu Al Hasib
  • 450
  • 5
  • 18
1
vote
1 answer

How to perform a GRANT in a stored procedure using a variable?

As an alternative to Post-create hook for BLOB values in Hibernate I thought an in-database trigger would be a better solution, so I attempted to write the following trigger: CREATE FUNCTION lo_default_grant() RETURNS trigger AS $$ DECLARE …
OrangeDog
  • 36,653
  • 12
  • 122
  • 207
1
vote
0 answers

Why does joining on the result of UNION prevent using other keys in PostgreSQL?

Why is the "union" query below so much slower than the "subquery" one? Why isn't it using the unique key? CREATE TABLE slow_union ( primary_key TEXT PRIMARY KEY, unique_key TEXT UNIQUE ); INSERT INTO slow_union (primary_key, unique_key) SELECT…
MichaelMitchell
  • 99
  • 1
  • 4
1
vote
0 answers

Concurrent Insertions in PostgreSQL

The documentation for migrating to PostgreSQL 12.6 says Concurrent insertions could lead to a corrupt index with entries placed in the wrong pages. It's recommended to reindex any GiST index that's been subject to concurrent insertions. I am…
Avocado
  • 871
  • 6
  • 23
1
vote
2 answers

Redundant use of manual entry after using auto increment

Source: GoalKicker.com SQL Notes I found so many redundant queries in the lecture notes. Like inserting id value manually after declaring id column to auto-increment itself in the DDL. Isn't it? DDL: CREATE TABLE Books ( Id INT NOT NULL…
Tapan Das
  • 21
  • 5
1
vote
1 answer

Error when installing pg_partman and pg_squeeze with postgres 12 on ubuntu 18

I have a dedicated server (ubuntu 18) for database, which has postgres 10,11 and 12 installed. I am currently using postgres 12. I am trying to install pg_partman and pg_squeeze extension on postgres 12. I have downloaded the git repo in…
Omer Farooq
  • 3,754
  • 6
  • 31
  • 60
1
vote
1 answer

PostgreSQL SELECT based on value in JSON object

I would like to query a table, orders, anad select all rows based on a value in a json object in a column, updates. I want to select all rows that are within 5 days from the last time it was closed. It is a PostgreSQL 12 database. [ { "time":…
Gustaf
  • 1,299
  • 8
  • 16
1
vote
0 answers

Same query takes 5x as long when inside a function - what is wrong with the function?

I have the following query to run, which takes 50-60ms (query plan here): SELECT (SUM(added_vacancies) + (SELECT active_vacancies FROM report.vacancy_stats WHERE date = make_date(2021,06,01) AND company_id = 6886 LIMIT 1) - SUM(archived_vacancies))…
Daniel Ziltener
  • 647
  • 1
  • 6
  • 21
1
vote
1 answer

Slow Query in partitioned table

We have postgresql12 and have a large table of size 108gb including index. Since, the query got slow, we tried partitioning the table. But this did not help. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT val_a AS x, val_b AS y, …
success malla
  • 80
  • 5
1
vote
1 answer

AWS Opsworks Chef recipe fails when installing Postgresql

I'm running this script in AWS Opsworks. It worked before, but now it fails with the error message below the script. The script creates the linux-2 server instance and then immediately runs the script. Some suggest setting pgpcheck=0, but I don't…
siwix
  • 91
  • 1
  • 7
1
vote
1 answer

Postgres Materialized view refresh failure

I want to know if materialized view in Postgres would hold the current data(last refreshed data) in case the 'materialized view refresh' fails for some reason?
Mano
  • 601
  • 10
  • 32
1
vote
1 answer

Is BRIN used for delete cascade?

I need indexes on some large tables to support ON DELETE CASCADE, but the size of btree indexes would be a problem. I therefore tried with BRIN, since the performance isn't critical. However, it seems they are never used, or at least the deletes are…
Rasmus
  • 69
  • 7
1 2 3
…
36 37