Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

1
vote
0answers
10 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
9 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
82 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
19 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
35 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
13 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
16 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
30 views

PostgreSQL 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
29 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
38 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
32 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 ...
0
votes
0answers
17 views

Postgresql explain cost vs actual database CPU usage

Let's suppose there are two queries A and B getting the same result. explain analyze A cost: 10000 execution time: 2 sec explain analyze B cost: 20000 execution time: 1 sec # Though it is not exact ...
0
votes
2answers
28 views

How to verify SSL always being used on Postgresql 9.6

I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers. Although I believe my postgresqls are configured to always use ...
0
votes
0answers
32 views

Insert max numeric value in Postgres column

Suppose I have a table like CREATE TABLE public.products ( id INTEGER NOT NULL article CHARACTER VARYING(255) name CHARACTER VARYING(255) quantity NUMERIC(10, 3) ) As you ...
0
votes
0answers
39 views

Postgresql Replica is in sync but repmgr says node unattached

I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't ...
-1
votes
1answer
26 views

Database volume completely full

/dev/mapper/pgdatavg-base 138304552 131262788 16384 100% /var/lib/pgsql/data/base pg_xlog is on a separate volume, and it's got plenty of space. [postgres@fpslbxmbstgl05 ...
1
vote
1answer
57 views

Equivalent Join Conditions Yield Vastly Different Results

The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
2
votes
1answer
86 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
-1
votes
0answers
40 views

pg_basebackup taking way too long

I'm trying to set up a replica of my Postgresql DB using repmgr, and every single time it takes way too long for it to finish. This is the command that I actually run: repmgr -h MASTER_DB_ADDRESS -U ...
0
votes
1answer
15 views

Increase in number of temp files being generated after increasing checkpoint_timeout

I have pgbadger set up to analyze the log files from my postgres db. I recently changed a couple of parameters in my postgres config. I changed the checkpoint_timeout from the default value of 300 ...
1
vote
1answer
22 views

How to prepend an element to a JSON array?

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty). How ...
0
votes
1answer
14 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
1answer
29 views

Why is searching by one varchar column not working correctly on replica?

PostgreSQL: 9.6, master, replica. Async replication, wal archiving. We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we ...
1
vote
1answer
17 views

query on 2 fields but 1 is a unique value

postgresql 9.6, windows 7 Here is an example of a database : fld1 fld2 abc D123 xyz D123 abc D789 how can I select rows with fld1='abc' if/only for fld2 value exists just one time in the ...
-1
votes
0answers
39 views

ERROR: tablespace “data_db” is not empty

I'm trying to move all objects from my data_db tablespace to another. I'm following this guide: http://www.postgresqltutorial.com/postgresql-drop-tablespace/ I've moved database objects using: ...
0
votes
1answer
55 views

Postgresql schema only works when wrapped in quotation marks

I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns [42601] ERROR: syntax error at or near "." The query is simply: select count(*) from "authorization"....
0
votes
2answers
43 views

Postgres list constraints that use a stored procedure

I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions. something like: CREATE TABLE payment ( amount bigint, CONSTRAINT amount_gt_0 CHECK (...
1
vote
1answer
19 views

How do you you suppress a specific warning in the postgresql log output?

Is there any way to suppress a specific warning in the postgresql log output? Basically, I have an application that often does a spurious commit before beginning database operations, and it's leading ...
0
votes
1answer
20 views

PostgreSQL query suddenly very slow after new column addition

I have a query that's performing terribly after adding a new column. Here's the slow version: SELECT COUNT(*) FROM activities INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."...
1
vote
0answers
11 views

Python transfer all data of table to table of different database

I can connect to a database Postgres with psycopg2, but this just 1 database. I have a problem that use Python code to move data of this table (database No.1) to another table (database No.2). The ...
0
votes
1answer
30 views

JOIN tables and return count even if it is 0

I have this query: select settings.customer_id, current::jsonb->'alarms' as alarms, from settings join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res....
0
votes
1answer
33 views

How to restore only functions in windows

I have taken function backup using following command: pg_dump -h localhost -u username -fc -f db_dump dbname How to list the backup functions and how can I restore those functions to other ...
2
votes
1answer
28 views

postgreSQL - Checking if one date interval does not overlap a date interval in array of intervals for each row

PostgreSQL(9.6), I am trying to check whether a date interval ('2018-11-18 12:00','2018-11-20 12:00') does not overlap any of the following date intervals in the array {('2018-11-21 12:00','2018-11-...
0
votes
1answer
19 views

View still depends on column even after I have removed the explicit (as well as transitive) dependencies?

Assume I have the following table and view: CREATE TABLE my_table (a int, b text, c text); CREATE VIEW my_view AS SELECT * FROM my_table WHERE b = 'foo'; The table has some data: INSERT INTO ...
0
votes
1answer
30 views

PGAUDIT on RHEL 7.4

I have download PostGreSQL 9.6 and pgaudit 9.6 from my RHEL repository. I have installed the rpm. How to I verify it is installed correctly? I have not been able to find good directions. It seems ...
2
votes
0answers
34 views

PostgreSQL: foreign table with comments

When creating a foreign table in PostgreSQL 9.6, comments (COMMENT ON smth IS "Text.") are not copied from the source table/view to the destination foreign table. How do I import the comments, ...
1
vote
1answer
45 views

Why does LIKE not work with CONCAT but works with STRING value

Please look on the following scenario; my_db=# select version(); version -----------------------------------------------------------------...
6
votes
1answer
83 views

Postgresql is failing to use my covering index and falling back to a much slower bitmap scan

I'm trying to figure out why a table of mine is using a Bitmap Heap Scan when a index scan is dramatically faster. Table: webarchive=# \d web_pages ...
0
votes
1answer
400 views

Improve performance of this query finding nearby locations

I am trying to execute this SQL, but the query is really slow (almost 2 hours), It's actually querying 1 million records (property_ids), that's why it's slow. But can someone suggest me a strategy to ...
-1
votes
0answers
31 views

Store or log actual dynamic SQL EXECUTE … format() … USING

exec_str := format('UPDATE table SET id = $1 WHERE val = $2 AND json#>>''{' || '%I' || '}'' like $3 AND id is null', _element); ...
1
vote
1answer
26 views

postgres pg_xlog explodes during set tablespace

I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and ...
6
votes
2answers
104 views

Postgres Transaction OOM on 100k DDL statements

We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't ...
1
vote
2answers
67 views

Create 2D truth table from “cross product” of two tables via PostgreSQL view or function

I have a working Excel-based method for creating a truth table from two vectors exported from a PostgreSQL database. The process takes about 4 hours to complete due to a large number of VLOOKUP and ...
0
votes
1answer
29 views

Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime

I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime. I thought I could : on the old Postgres, activate archive_command to copy WAL to a NFS ...
1
vote
1answer
272 views

Converting VARCHAR to TIMESTAMP with milleseconds yields “ERROR: date/time field value out of range”

I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command: select last_run, to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - ...
1
vote
0answers
46 views

pg_dump and pg_dumpall tools backup create zero size backup file from linux putty terminal

I have installed postgres database version 9.6, 10 on OS Centos 7 64 bit. I'm trying to take a backup with this command: -bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/...
0
votes
0answers
45 views

Change to query with a number of CTEs confuses query planner

The below query runs normally in about 1 minute. I made some changes to it for reasons and suddenly performance was ~90x worse. When I run the query with every CTE instead inserting into a temporary ...
1
vote
1answer
40 views

How to debug locked transaction on a remote system? [closed]

I have a remote database that is currently throwing errors. On a primary key of type BIGSERIAL it says no new rows can be created because a value for the primary key already exists (the value itself ...
1
vote
0answers
22 views

Exclusive and Inclusive ranking queries in Postgres 9.6

I'm trying to build a filtering/ranking search that would let me order and optionally filter out people based on a 1-to-many relationship with job "focuses". The tables are shown at the bottom. A "...
1
vote
0answers
33 views

Create a postgres user role that can't access a specific table

I have a postgres 9.6 database with many tables. I need to create a role that doesn't have access to one of these tables. I think I need to do a Grant Permissions, but I haven't been able to find ...