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-9.6]

PostgreSQL version 9.6

0
votes
0answers
9 views

Copy OID field into remote postgresql DB

We have configure postgres_fdw to move data from a db to remote db. It works fine but the OID field's data in source db not getting copied to remote DB. INSERT INTO archive_remote.target_db(id, ...
0
votes
1answer
11 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
20 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
12 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
19 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
12 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
32 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
19 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
27 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
21 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
25 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
75 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
20 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
9 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
36 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
19 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
18 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
10 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
21 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
29 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
36 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
25 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
13 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
11 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
68 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....
0
votes
0answers
22 views

I need some help about indexing a JSONB column

I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it. Some of the fields are being indexed already, but there's a ...
0
votes
1answer
73 views

In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index? SET enable_seqscan to FALSE; EXPLAIN ...
0
votes
1answer
73 views

Speed up UPDATE on large table

We are migrating our ids from INTEGER to BIGINT in some of our tables. The first step for us is to create a new temporary column id2 that has the correct type, and then we want to migrate the existing ...
0
votes
1answer
20 views

Putting a constraints on a already made table in PostgreSQL

I was wondering is it possible to add Not Null to a variable created already in the PostgreSQL database. I created a table already, but I forgot to add not null constraints to the variable ?
0
votes
0answers
29 views

Does Aurora Postgres automatically kill process if replication lag is too high?

I have aurora postgres (9.6) master and reader. Time to time its getting swapped and message shows server process(PID 111111) was terminated by signal 9: Killed I didn't kill anything, and suspecting ...
0
votes
0answers
48 views

Postgres query speed widely varies for no perceivable reason

I'm running an instance of Postgres 9.6.11 on AWS. I have a few tables that aren't huge (one with a couple million records, one with a hundred thousand). I'm trying to query for the recommendation ...
1
vote
1answer
34 views

PostgreSQL doesn't generate index-only plan when using NOT-IN but does when using IN with multicolumn covering index

I have two tables: A(id, x, cid) and B(cid). I need to fetch some records from A and excluding records with same cid in B. I have btree index on A (cid, id). Query-1 (NOT-IN): select id from a where ...
0
votes
1answer
26 views

Find queries causing a lot of read IO

I'm trying to find the cause of a higher amount of VolumeReadIOPs in our Aurora Postgres instance on AWS. This is what the past 6 weeks look like: I found the cause of the largest period (02/27 to 03/...
0
votes
0answers
48 views

PostgreSQL disconnection session time

I have a simple app which supports just GET & POST using JDBCTemplate, in tomcat configuration (context.xml) is set where it can find the Database like below: <Resource name="jdbc/MYDB" auth="...
1
vote
1answer
211 views

PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

This is my remote function: CREATE OR REPLACE FUNCTION public._test1() RETURNS record LANGUAGE plpgsql AS $function$ DECLARE rec record; BEGIN select 1,2 into rec; return rec; END $function$; ...
1
vote
2answers
39 views

Grant CREATE SCHEMA (only) to users

Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth). The public space is where I ...
0
votes
0answers
14 views

How to create an optimized view that fills in sparse observations with modeled data?

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int which is partitioned by month (e.g.: observations_201903). There are approximately 150k sensors, providing ...
0
votes
0answers
16 views

Why doesn't tsrange('2018-01-01', NULL) @> transactions.transacted_at use the b-tree index? [duplicate]

Given CREATE TABLE transactions ( transacted_at TIMESTAMP ); CREATE INDEX IF NOT EXISTS index_transactions_on_transacted_at ON transactions (transacted_at); Then this doesn't use the index: ...
0
votes
0answers
27 views

Combine different index types in postgres

we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type? for instance: select * from foo_tbl foo where foo....
0
votes
0answers
37 views

Postgres COPY locking on a busy table

Does COPY lock a table for writes? I have a busy table, both reads and writes, and I'd like to COPY it to a logical replica. The table is several GBs, so the COPY will take a while.
0
votes
1answer
54 views

postgresql - trouble to order a list obtained by string_agg

I obtained a list of numbers (and text) by a string_agg. For instance, that : "09,12,20,200,217,400,500,90,91,92,Exp3" This is the string_agg: string_agg(DISTINCT t_ligne.ligne_code::text, ','::...
0
votes
1answer
16 views

How to capture client host name in postgresql?

pg_stat_activity system view has client_address and client_hostname, but it always empty or null. Do I need to tweak anything to update the view?
1
vote
1answer
51 views

Index Optimization for Datetime comparing day of week and hour

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
0
votes
0answers
11 views

what is replay_lsn in postgres stat replication

We are trying to implement Postgres streaming replication for which I'm reading PostgreSQL streaming replication material. Thus far things are looking good but I'm puzzled about what is replay_lsn ...
0
votes
0answers
17 views

Change user password at the next login with Postgres

Is there a default way to make the user change the password at the next login.
1
vote
0answers
43 views

Validation within a transaction with all violations in response

I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total ...
0
votes
0answers
12 views

Have anybody configured PostgreSQL BDR replication? how useful it is?

We have a requirement to configure multimaster replication in postgreSQL. we have 2nd Quadrant BDR replication but I wanted to make sure is it error prone or any disadvantages are there configuring ...
0
votes
1answer
85 views

How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql?

Please guide me to store LOB-image or PDF in Postgresql and let me know what is the impact of storing these types of objects on Postgresql database. Any help will be appreciated.
1
vote
1answer
27 views

Optimizing query performance with aggregation tables and partitioning

Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem: We have a large table (items) with about 500 million rows (grows with ~1....
0
votes
2answers
27 views

Most efficient way of updating table from another - Postgres

I need to populate a foreign key column based on the contents of another table, and both methods I've tried are prohibitively slow. I have the below tables (some columns omitted): ...