Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
16 views

How Insert data to child table in different schema using trigger function and auto updates for these child tables

I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing ...
1
vote
1answer
11 views

Update all the elements of JSON array nested in a JSON column (Postgres)

I have json column with data that looks like this: { a: [ { q: 5 }, { q: 8 }, { q: 10 } ] } I'd like to map over the elements in the array and add an extra property ...
0
votes
1answer
11 views

Postgres VACUUM taking forever

I have an specific table that is taking forever o VACUUM. There are no locks in the DB and even letting it overnight the VACUUM didn't finish. According to the statistics (ANALYZE works fine) the ...
0
votes
0answers
15 views

Unit postgresql.service failed to load: No such file or directory

When i try to connect to my postgres database, i get the error below: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
0
votes
1answer
20 views

How SQL query are evaluated

I ran into an issue on trying to optimise the following two query into 1 single query. Result 1: select * from sip_trunks where resporg_account_id_id=62 and identifier = (select action ...
1
vote
1answer
25 views

Why disable Postgres Autovacuum?

I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled. The database was extremely slow, and after a manual VACUUM it could breathe again. The point is, why ...
0
votes
0answers
15 views

Partitions for table that hold only ids in PostgreSQL 9.6

I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table ...
0
votes
0answers
23 views

PostgreSQL does not starting up after power failure

Could not start PostgreSQL after power failure: FATAL: could not find tuple for opclass 1 PostgreSQL version: root@ubuntu:/usr/lib/postgresql/9.6/bin# ./postgres -V postgres (PostgreSQL) 9.6.1 OS ...
3
votes
1answer
45 views

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
0
votes
0answers
40 views

Difference in DELETE performance between physical (snapshot based) and logical (basebackup) clones

We have a relatively large PostgreSQL database (500G - 1TB) and want to perform a big migration task. The migration consists in a bunch of ALTER, UPDATE, DELETE, SELECT. We have performed this ...
0
votes
1answer
22 views

pg_trgm index not used with commutator

PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long. I have the trigram indexes: CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops); CREATE INDEX ...
0
votes
1answer
22 views

Is it possible to do sums of aggregation that stops aggregating once the condition is reached?

Say I have a table amounts with millions of rows tallying up to some number, but I'm really just checking if it's > 100. Is there a way to stop aggregation since taking this: SELECT SUM(amount) FROM ...
0
votes
0answers
28 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
0
votes
1answer
29 views

Optimization for table with text and time columns

I have a simple table with the following columns: id (int4) time (timestamp) name (varchar) service_name (varchar) For every service (there are not more than 500 different names), there are a lot of ...
0
votes
0answers
41 views

Increase shared_buffers without restarting PostgreSQL

According to the official PostgreSQL documentation: shared_buffers (integer) Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 ...
1
vote
1answer
41 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
2
votes
0answers
29 views

Left join with generate_series misleads the planner

Using postgres 9.6, i'm currently managing time-based data with the following structure: Column | Type | Collation | Nullable | Default ----------------+-----...
2
votes
1answer
18 views

pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas

While moving database to another instance I'm using pg_dump and pg_restore with intermediate file. Dump file was created like this: pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
1
vote
1answer
37 views

Postgres choosing a filter instead of index cond when OR is involved

I have a table that daily gets about 20 million records added to it, and I'm trying to paginate through it to let people get access to all the data in it, but the query time has to be "decent" (in my ...
0
votes
1answer
72 views

wal_level both hot_standby and logical

I have a PostgreSQL setup with one master and three slaves. It's configured as streaming replication. Now, I want to replicate only a few tables from the master server to an analytic server using ...
0
votes
0answers
29 views

Postgresql - Can we create streaming and logical replication replication from same server

I have a postgresql setup with 1 master and 3 slaves. Now I want to setup a replication for only one database to reporting server. My problem statements: I can't use bucardo (no approval for ...
0
votes
3answers
91 views

Postgres speed up index creation for large table

I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever ...
0
votes
1answer
23 views

PostgreSQL 9.6: Update on a single row table takes an unreasonable amount of time

I've got a table called source which has a primary key on its id and a “Unique Constraint” on the column name. This is the way the table looks: id int4 name ...
0
votes
1answer
65 views

pg_stat_statements empty with administrator user and unusable for other users

I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed. portaldb=> SELECT * FROM ...
1
vote
2answers
30 views

PostgreSQL partial index not used depending on boolean comparison

I need help understanding why a partial index would be used in one query and would not in second one which is effectively the same. I have the following table: Column | Type ...
0
votes
1answer
39 views

Copy large object field into remote PostgreSQL DB [closed]

We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB. This is the statement used to copy: ...
0
votes
1answer
25 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
0
votes
0answers
23 views

How to get record by date from 1 table and update other table in Postgresql?

I have two tables. In one table (order_product) I have multiple records by date and in the other table (Transfer_product) also multiple record by date. order_product table have correct record. I want ...
0
votes
0answers
53 views

Physical and logical replication slots on Postgres (same time)

I wonder if it's possible to have a setup when changes from a single Postgres master streamed to two (or more) replicas same time, but some replicas to use streaming (logical) replication and some to ...
0
votes
1answer
22 views

update value in row based on value in another row

I have a table with timestamped data. t_stamp | col1 | col2 | col3 I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean ...
0
votes
1answer
56 views

pgAdmin4 version 3.1 how to update to the last version

We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6. In the log of the server, I notice some errors generated by the pga4dash : ERROR: column "waiting" does not exist ...
2
votes
1answer
73 views

Slow performance on postgres query with count/sort

I've inherited an Django/postgres app from another developer and I'm finding many of the queries are very slow. I've spent a lot of time correctly organising/normalising the data which was not done ...
0
votes
0answers
22 views

UPDATE query in small tables taking forever

I'm having trouble creating a UPDATE query to perform the following: I have two tables, ed is a 11k rows table with areas from multiple constructions and a key lote_key wich links to the table unid, ...
0
votes
1answer
30 views

Progressive child tables scan in PostgreSQL?

We have append-only events table where events from different devices are being collected. We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions. Data is partitioned using ...
0
votes
1answer
39 views

Killing long running queries with user notification

Is there any way to inform user that your query has been running more than x minutes, so admin has killed your session. This is especially to the query or read only user. I have a function which will ...
0
votes
1answer
27 views

Is there a place to store ERDs in Postgres?

Is there a designated spot in Postgres to store Entity-Relationship Diagrams (ERDs)? I could just create a table with a BTYEA column or something, but I want to make sure to use what exists if ...
3
votes
2answers
86 views

Database design that requires an index over two tables

Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products ...
0
votes
1answer
33 views

How to select blocks from a result by condition?

Example table: master_id 1 created_at 22.02.1997 master_id 1 created_at 22.03.1997 master_id 1 created_at 22.04.1997 master_id 1 created_at 22.07.1997 master_id 1 created_at 22.08.1997 master_id 1 ...
0
votes
0answers
12 views

Can I use EBS data directory path as temp tablespace in ADS Aurora Postgresql?

I am having issue with RDS Aurora Postgresql's root storage (which is used for Temp files/tables and Logs) since its basically 2*PhysicalMemory. I thought of creating a new tablespace in data storage ...
0
votes
0answers
68 views

work_mem setting for huge queries

I am querying some data from a view using copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates ...
0
votes
0answers
59 views

RDS Aurora postgres temp files removal

A report application generates lots of temp files in RDS aurora postgresql. Temp files loggings shows average 20G files being generated for each query execution. But the query execution not taking ...
0
votes
1answer
22 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
0
votes
0answers
13 views

How to optimise PostgreSQL planning time

I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2 select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006); that is slower than I like for where it is ...
0
votes
0answers
26 views

Do UPDATE statement performance affect indexed column(s) in Postgres?

I have a table which is having millions of rows. While doing update I'm using primary key column in WHERE clause and it uses index scan as per the EXPLAIN plan (within BEGINand ROLLBACK block). I have ...
0
votes
0answers
37 views

PostgreSQL 9.6 slow UPDATE on production server compared to local machine

I have a production vm with centos 7.6 under hyper-v and a windows 10 laptop as a dev machine and both are running postgresql 9.6, on my laptop 10k UPDATE queries only take 9 seconds while on the ...
0
votes
2answers
45 views

Does adding a postgres index concurrently take up disk space gradually or all at once?

I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I ...
0
votes
1answer
80 views

How currval of sequence being preserved during the backup and restore in postgres

If I do backup and restore a huge db (tons of tables in it), will it run without any issues rather rising exception about duplicate key constraints of sequenced column(s). While taking pg_dump (...
1
vote
0answers
27 views

Dumping Postgres schema from Google cloud SQL

Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump ...
0
votes
1answer
12 views

Postgres 9.6.6 on Ubuntu 16.04, lots of errors

We're running perfsonar on a group of our servers, and while doing some maintenance work found that we get giant logfiles under /var/log/postgresql table bloat on the database of the perfsonar ...
3
votes
1answer
80 views

Query a database specific configuration parameter

I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this: ALTER DATABASE mydatabase SET default_transaction_read_only = false; Which works great....