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

Is SQL/JSON supported by SqlAlchemy

I'm interested, are these Postgresql-12 functions supported by sqlalchemy 1.3? jsonb_path_exists(jsonb, jsonpath[, jsonb, bool]) jsonb_path_match(jsonb, jsonpath[, jsonb, bool]) jsonb_path_query(jsonb, jsonpath[, jsonb,…
Aleksey
  • 21
  • 3
1
vote
2 answers

why the query is executed 76 times slower when I put it into function?

When I put next query into function it goes 76times slower. The only difference at plan is: bitmap-index scan VS index scan Plan1: http://tatiyants.com/pev/#/plans/plan_1562919134481 Plan2:…
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
0
votes
3 answers

Can a postgres server be publisher for some tables and subscribers for others?

I am having 2 servers, server (A,B). Can server A be publisher for table 1 and subscriber for table 2. And server B be publisher for table 2 and subscriber for table 1. What i reached so far, server A is publisher for table 1 and server B subscribed…
Yassin Shanwany
  • 56
  • 5
0
votes
2 answers

Postgresql - Idle query keep open connections to all databases

I have a strange situation in my PostgreSQL server. I have the below idle query that keeps running in all databases in the server. datid | 1029662 datname | ganar pid | 19022 usesysid | 10 usename |…
Taha Adel
  • 11
  • 3
0
votes
1 answer

Prevent SQL injection in EXECUTE ... INTO

I have a function that takes ORDER BY field and direction as parameters, and I'm trying to use the format function to interpolate them into the SELECT statement. CREATE OR REPLACE FUNCTION dummy_ordered_select(orderby text) RETURNS users…
Esteban
  • 2,540
  • 21
  • 27
0
votes
2 answers

UPDATE performance when JOIN produces duplicates rows for columns in SET

Consider the following example: There are 3 tables: clients (1) -> (n) orders (1) -> (n) order_updates. I want to update clients based on the corresponding order_updates with JOIN (which produces duplicates for clients data): UPDATE "clients" SET…
potashin
  • 44,205
  • 11
  • 83
  • 107
0
votes
1 answer

PostgreSQL delete query taking hours to execute

I two tables table1 and table2 in postgresql-12: DELETE FROM table2 WHERE (ddate, itemno) NOT IN (SELECT ddate,itemno FROM table1); table1 has around 7000000 entries. table2 has around 9500000 entries. Around 100000 matches are expected. This…
Sourav
  • 129
  • 12
0
votes
0 answers

postgres pg_dump too many to exclude

I have schema let's say student and it has 300 tables and another schema employee which consists of 200 tables The thing is in student schema I only need 20 tables and in employee I only need 15 tables to be backup is there a way to use pg_dump…
Michael
  • 43
  • 6
0
votes
0 answers

GCP PostgreSQL Instance: How to stop WAL continuously increasing in size since implementing datastream

PostgreSQL version: PostgreSQL 12.12 Since implementing datastream to replicate 2 rather large tables to GCP BigQuery, the SQL instances WAL size has been steadily increasing each day. This has been going on for just shy of a month now and it is…
Logan
  • 1
  • 1
0
votes
1 answer

Running the definition of a view returns different results to running the view itself

I am currently using PostgreSQL 12.11 and have a view called previous_customers with the following definition (extracted from DDL): CREATE VIEW previous_customers(customer_id, alumni) as SELECT DISTINCT purchase.customer_id, true AS…
Silas Maughan
  • 3
  • 5
0
votes
0 answers

How can I dump a Sonarqube Database using pg_dump

I want to get a sql dump of Sonarqube, the database is postgresql. I execute the following command: pg_dump -U postgres -h $HOST -p $PORT -d sonar -f sonar.sql pg_dump version: pg_dump (PostgreSQL) 12.15 All this instances are running on Kubernetes…
Mohamed Ayadi
  • 83
  • 1
  • 8
0
votes
0 answers

libpq.so.5: cannot open shared object file

I am trying to take a backup programmatically using pg_dump. I have copied all pg utilities under bin directory of /usr/pgsql-12/. I have also copied the library from /usr/lib/x86_64-linux-gnu to /usr/pgsql-12/lib and added /usr/pgsql-12/lib to PATH…
Chaitanya Deshmukh
  • 1
0
votes
1 answer

In "pg_repack" I faced an Error : cannot update table "table_123" because it does not have a replica identity and publishes updates

ERROR: query failed: ERROR: cannot update table "table_123" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. CONTEXT: SQL statement "UPDATE…
MANJULA KURAPATI
  • 1
0
votes
1 answer

Postgresql12 db pretty size difference

We recently moved from a PG12.7 running on Kubernetes to PG12.14 on a VM. Post migration, we checked all the rowcount and it is there in new db. But when I used pg_pretty_size, the difference of db size is huge in both dbs. SELECT…
Oxycash
  • 167
  • 12
0
votes
1 answer

pgp_pub_decrypt function fails after upgrade from PostgreSQL 12 to PostgreSQL 15

I have a weird behaviour after upgrading our database server from Postgresql 12 to Postgresql 15. I created all the required extensions with pgcrypto even the same version 1.3. It looks like the pgp decryption function with pgp_pub_decrypt works…
Niels
  • 193
  • 2
  • 13
1 2 3
…
36 37