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
5 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
13 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
25 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
13 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
24 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
20 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
0answers
37 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
15 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
29 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
12 views

how to write hstore column in where clause of spring data jpa query [migrated]

I am facing a problem in writing the "hstore" type column in where clause of spring data jpa query. @Transactional(readOnly = true) @Query( value = "select ld from LocationData ld inner join fetch ...
0
votes
0answers
10 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
12 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
39 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
9 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
30 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
23 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
0answers
5 views

Standby servers in Postgresql

I want to know how many standby servers we can create in Postgresql? Like in Postgresql we can create log shipping standby, streaming replication,hot standby etc..
0
votes
2answers
26 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): ...
1
vote
1answer
13 views

Wraparound Transaction ID

Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
5
votes
1answer
71 views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
0
votes
1answer
42 views

Generate data delta between two databases PostgreSQL

I have a big database with a lot of data ~50GB that spans around 40 tables - running PostgreSQL 9.6. This database is copied and sits in number of client sites without an Internet connection. ...
0
votes
0answers
35 views

Match and update records in a table

I have two tables table1: | id | A | B | C | date1 | date2 | |-----|------|-----|------|--------------|--------------| | 1 | 100 | 100 | 100 | 10-Jan-2019 | 11-Jan-2019 ...
1
vote
1answer
78 views

Relation does not exist while scanning schema

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS ...
0
votes
0answers
14 views

Is it possible to make thosunds of concurrent transactions even if the max_connections is set to 100 in Postgres?

This is maybe a dummy queestion but I couldn't understand to have thousands of concurrent transactions in a database even if the max_connections=100. Definition of max_connections from postgres ...
0
votes
1answer
11 views

Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)?

We have a system on AWS RDS Aurora, PostgreSQL v 9.6.8 option. We are trying to move its roles to a new encrypted 10.5 instance, but we don't have superuser access that's required to do pg_dumpall -g ...
1
vote
1answer
26 views

Sort output of plpgsql function

I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
0
votes
1answer
61 views

Replicate Postgres to Cockroach

We are planning to replicate our Postgres database to Cockroach. We are looking for a relatively simpler solution to achieve that. Currently, we are working on a Stream based replication (something ...
2
votes
2answers
49 views

Postgres CTE optimization with nested json_build_object

I'm trying to write a query that returns data from multiple tables and aggregates it in one nested JSON field. I feel like this would perform great on SqlServer but, as Brent Ozar wrote in this post, ...
0
votes
1answer
19 views

generate_series not working in single user mode

A table is not being populated with generate_series for below. backend> create table t1 (i int, j int); backend> insert into t1 values (generate_series(1,200000), generate_series(1,200000)); ...
0
votes
1answer
38 views

Combining separate columns into smallest possible related ranges

I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
0
votes
1answer
37 views

Error with pg_upgrade and PostGIS

I have a PostgreSQL 9.2 setup (with Postgis) and am trying to upgrade it to 9.6. When performing the pg_upgrade, I get the following error: ERROR: could not find function "geography_analyze" in ...
1
vote
1answer
34 views

Optimizing SELECT query consistently showing up in slow-log

In a app called 'Links', users post links and photos of interesting content they've discovered recently (and others comment on the said postings). These posted comments under the photos are saved in ...
0
votes
0answers
9 views

Why would PSQL on AWS RDS trigger an unexpected recovery?

Setup AWS RDS db.t2.small Postgres 9.6.3 Conditions - seen in monitoring Free mem 1GB Swap grew up to about 15 MB before the recovery Nothing unstable in any other monitoring Event logs lost not ...
0
votes
0answers
33 views

How to run multiple sql statements in Postgres using the tool PGADMIN?

I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint. All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
1
vote
1answer
28 views

Postgres 9.6 (pgAdmin 4) - How to query daily statistics

Is there a way I can query through pgAdmin in a way I will get statistics for daily query usage? I need to find what queries are run against my api as I get huge network traffic. I'm looking for a ...
2
votes
1answer
20 views

How to get distinct day number with window function (Postgres 9.6)

I am trying to get the distinct day number with a set of data that contains different dates that are not always consecutive. For example, if I have this data: date | id 2019-01-01 | 1 2019-01-...
0
votes
0answers
28 views

Where each partition has only one value in the constraint column, can I avoid filtering?

I have partitioned a table in postgres 9.6 by date. Every date within a parition is identical. When I select data, a relatively expensive Seq Scan filter operation happens against every row in the ...
0
votes
0answers
41 views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
1
vote
0answers
14 views

PostgreSQL database for GITLAB - Grant privileges only to user PUBLIC?

I manage a Postgresql database for a GITLAB application. The developer provided me with a backup file for migrating his database to a new server that contains the following statements: ALTER DEFAULT ...
0
votes
0answers
16 views

PostgreSQL database for GITLAB - Must it use the public schema?

I have a PostgreSQL database for the GITLAB application that we need to upgrade. My developer is telling me that GITLAB needs the schema to be public instead of our current schema. Has anyone run into ...
3
votes
2answers
134 views

GRANT SELECT ON SEQUENCE succeeds, but to no effect?

I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the ...
0
votes
1answer
22 views

Getting error on a particular query on a table in Postgres

I am getting the following error on one query in Postgres: select * from sampletable where sample_id = 1234; Error is : ERROR: could not access status of transaction <some-id> Detail: CLOG ...
0
votes
0answers
64 views

What makes ALTER TABLE X SET UNLOGGED; to be slow?

We develop and maintain a JEE Application with Postgres (multiple version of it as we've got different customers using different versions). The Code evolves over time; database changes are organized ...
0
votes
2answers
15 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
0
votes
1answer
19 views

Will Postgresql statements execute synchronously if autocommit=true?

If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server? I read that on the postgres server side, "there ...
0
votes
0answers
47 views

Occasional slow insertion speed because of FK trigger

I have two tables in master-child relationship (several children per master). In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert ...
0
votes
0answers
100 views

Cannot revoke permissions or drop user in PgSQL AWS RDS

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
0
votes
0answers
185 views

PostgreSQL 9.6 won't start: “FATAL: could not write lock file ”postmaster.pid“: Input/output error”

I am using PostgreSQL 9.6 running on Debian 9.2. I cannot get my PostgreSQL up and running after the disk it was writing to became read only (it's on a separate disk from the OS). Here is the log ...
2
votes
1answer
36 views

How to insert into table multiple geometry rows from function JSON param?

My table: CREATE TABLE Points ( id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass), info character varying(20) NOT NULL, my_point geometry(Point,4326), CONSTRAINT ...