Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-10]

PostgreSQL version 10

0
votes
0answers
7 views

Logical replication - Manually triggering update of data on destination tables (after schema change)

I'm testing out Logical replication on Postgresql (10.4 at the moment) with a quite specific use case. I have some central DB (publisher) from where I need to replicate few tables to distributed DB's ...
0
votes
1answer
18 views

ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host

I am trying to create a logical replication between 2 postgres servers. But when I try to create a subscription from the slave to the master I get the error ERROR: could not connect to the ...
0
votes
0answers
16 views

Hibernate does not close idle session [migrated]

I cannot kill session in idle status on postgres 10 This caused a very big problem during some test and fill postgres connection pull. After close connection, the session is in idle state and wont ...
0
votes
1answer
13 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
0
votes
0answers
18 views

Is there a possibility to run 2 minor Postgres version in parallel (Postgres 10.x)?

I have Postgres db instance 10.6 running in production say node-b: rhel 7.6 while I am building another cluster (not instance) of 2 nodes. Out of these 2 nodes one posgres node will reside on this ...
0
votes
1answer
32 views

Postgres 10 and malformatted array literal when using EXECUTE

I am trying to create a function on Postgres 10 that would check whether all the given ids exist in the target table. CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"( _ids TEXT[], ...
2
votes
0answers
22 views

AWS RDS PostgreSQL: Significant query runtime difference between two (identical?) instances

TL;DR: Solution The problem was that one instance hat magnetic storage attached and was therefore significantly slower. Original Question I have two identical AWS RDS PostgreSQL 10.6 databases ...
0
votes
0answers
18 views

How to optimize a query with select, row number and a lot of LIKE

I try to find search rank of a product in today's product history records. For this I use 6 ILIKE and 2 inner join. I have indexed all columns but still so slow. It takes approximately 10 sec. to ...
0
votes
0answers
9 views

Why is restart_lsn not set, even on checkpoint - postgresql

A logical slot will emit each change just once in normal operation. The current position of each slot is persisted only at checkpoint, so in the case of a crash the slot may return to an earlier ...
0
votes
1answer
37 views

Error when creating a stored procedure in postgreSQL-10 [closed]

I couldn't get my first stored procedure to work, so I copied this example from the manual: CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT ...
5
votes
2answers
110 views

Why are partial PostgreSQL HASH indices not smaller than full indices?

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial. Now I'm wondering why a partial HASH index isn't ...
0
votes
1answer
32 views

Preparing query in postgreSQL with variable table name?

I'm trying to do an INSERT INTO a table whose name is in the variable myTable. In MySQL, I'd do it like this: SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...'); ...
1
vote
2answers
58 views

Can a trigger create a TABLE when inserting data?

I have a trigger that keeps track of history of table dev by inserting a row in table dev_hist: CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$ DECLARE action_name ...
0
votes
0answers
21 views

How to create multiple tables based on existing tables in postgreSQL?

I have several tables, and I need to create a matching _hist table for each one. For example if I have: CREATE TABLE public.test ( first text, middle text, "last" text, ...
0
votes
1answer
36 views

In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp?

I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field. My current solution is like this: CREATE OR REPLACE FUNCTION ...
1
vote
1answer
44 views

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes. My question ...
1
vote
1answer
34 views

Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade

I (successfully) followed the upgrade procedure in §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old ...
4
votes
1answer
63 views

Slow left join lateral in subquery

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
2
votes
1answer
18 views

Is it possible to add a spatial index to a partition with Postgres 10?

I am creating declarative partitions like: CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY ...
0
votes
2answers
124 views

pg_resetxlog command not found

I can't find pg_resetxlog under /usr/bin. postgres (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) cd /usr/bin postgres@node-1:/usr/bin$ ./pg_resetxlog -su: ./pg_resetxlog: No such file or directory
2
votes
1answer
110 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
1
vote
0answers
21 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
1
vote
1answer
57 views

JSONB vs BRIN Index

The requirements go like this: Each user has a wallet that can contain tens to a hundred or so different currencies, and for each currency an amount Number of currency can grow > 1600 (make me ...
0
votes
1answer
103 views

How do I cleanup Postgres WAL?

What is the correct way to cleanup WAL postgres? I have a database with more than 100 GB and it haves around 600 GB in pg_wal. Also I have 2 logical replications set up. Master and Slaves - Postgres ...
2
votes
1answer
29 views

How to modify values on INSERT based on referenced table, even if foreign key doesn't exist yet?

Summary I want to add rows to a (projected) very huge table, but switch 2 values for references in other tables. The catch is that the referenced tables should be updated automatically when new ...
0
votes
0answers
28 views

Very high memory usage by Postgres 10.1 on Windows 2008 R2

On Windows start up, ram usage is around 5% but it gradually keeps increasing and will eventually balloon to where it is using almost all the ram.The DB size is only 4941MB. The server has 8gb total ...
1
vote
2answers
89 views

How to restore Postgresql without pg_control but with file system backup and wal files?

OS: Ubuntu 16.04, Docker Postgresql: 10 I'm using an alternative file-system backup that is making “consistent snapshot” of the data directory every 24 hours. I'm experiencing the warning from the ...
1
vote
1answer
30 views

Suboptimal query plan when updating partitioned table

Background I have a simple CTE used to update a declaratively-partitioned table. The subquery runs quickly (1.7sec per EXPLAIN ANALYZE) and returns 3,769 records (CTE y in the query below). The ...
0
votes
0answers
15 views

Adding constraints to child tables in pg_partman

I'm trying to build a logging table and using pg_partman partitioning it on a daily level. This part is setup. The issue is I need a unique constraint on the fk_id and timestamp. The process to fill ...
0
votes
0answers
33 views

Postgres 10 partition pruning through Extended Query protocol

From what I can tell, in postgres 10, parition pruning through declaritive partitioning only works if the partition key value is included in the query explicitly SELECT * FROM partitioned_table WHERE ...
1
vote
1answer
77 views

Query to obtain weighted percentile

Trying generate SQL to compute a weighted continuous value at a given set of percentile values (the 25%,50%,and 75% levels used below, but solution should allow for an arbitrary parameter level). In ...
1
vote
1answer
37 views

What happened to pg_xlog?

There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. ...
0
votes
2answers
110 views

Postgres jsonb vs composite type performance differences

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure? For example, consider a column like that used in the Postgres documentation: ...
0
votes
0answers
12 views

Utilizing DBLINK[_EXEC] to control transactions within PL/PgSQL functions?

I am exploring options to control transactions within functions, mainly to reduce the memory footprint for large operations over multiple individual units. I.e. large data-sets are inserted regularly, ...
0
votes
1answer
35 views

archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health

I had replication set up with a simple archive_command in postgresql.conf: archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f' Which worked great for 2+ years until ...
1
vote
0answers
16 views

Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in ...
0
votes
2answers
67 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
1
vote
1answer
74 views

Adding LEFT JOIN to a query has increased execution time 10 times

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-...
1
vote
0answers
40 views

Logical replication sync time

I'm trying to create a logical replication between 2 Postgres 10 databases in separate clusters. All the process seem to be working, no error and \dRs+ shows the subscription correctly. The master ...
0
votes
0answers
35 views

Filter rows by objects in jsonb array in PostgreSQL

I have a json document for each row in my table (PostgreSQL 10) with the following structure { "partyboat": { "participants": [{ "name": "Bill Lambeer", "role": "Lessee" }, { ...
0
votes
0answers
28 views

Create new table with unique foreign key or Nullable columns for one to zero/one relation? Better design?

I am newer to database design, and was hoping I could get some input on a concept I have not seen that much discussion on when I look it up. I am using Postgres 10. Basically, I have a User table ...
3
votes
1answer
188 views

“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp

I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table): select to_timestamp(...
5
votes
1answer
71 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
0
votes
1answer
21 views

Postgresql streaming replication slow on macOS

I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower ...
2
votes
1answer
169 views

PostgreSQL JSONB update array elements query

Given a table in PostgreSQL 10: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"id":"a uuid_v1 here", "widgets": [ {"id":"1", "permissions": ["user1", "...
1
vote
1answer
305 views

Downgrading PostgreSQL 10 to 9.4

Are there any particular precautions I should take when downgrading PG 10 to 9.4? I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
0
votes
1answer
46 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
1
vote
0answers
52 views

Make use of PostgreSQL 10 multi-column statistics when planning index scans

I'm using PostgreSQL 10 and am trying to optimize query performance on a large table (~70M rows). The query looks like: SELECT * FROM large_table -- many joins -- WHERE col_3 = 'particular date'::...
1
vote
1answer
526 views

could not load library “/usr/pgsql-10/lib/postgis-2.5.so”: /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

I have installed postgresql 10 on centos 7.4 and installed postgis2.5 few months back. The installation was successful and Iam able to use postgis. All of a sudden Iam getting the following error. ...
1
vote
2answers
68 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...