Skip to main content

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
0 votes
1 answer
1k views

how to call lo_unlink(lo) in batches

In Postgres 9.5 I am tying to reproduce the postgres tool vacuumlo to get rid of unreferenced large object to free disk space. References are stored in text type columns by hibernate so using the tool ...
5 votes
2 answers
26k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
0 votes
1 answer
4k views

Drop tables but space not claimed in postgres 12

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) ...
0 votes
1 answer
337 views

pglogical library

I would like to try pglogical to set up an archive server from a live database, filtering with INSERT, UPDATE statements (DELETE statements will not be replicated in the archive server). I follow the ...
5 votes
2 answers
593 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
9 votes
1 answer
2k views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
14 votes
1 answer
26k views

Changing foreign key to ON DELETE CASCADE with least amount of impact

I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction: begin; alter table posts drop ...
0 votes
3 answers
305 views

Postgres Specific table access

We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any ...
0 votes
1 answer
551 views

How to calculate quantity of WAL in db without archiving?

I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are ...
0 votes
1 answer
5k views

Postgresql: Permission denied to schema to user created based on master with rights

I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the ...
0 votes
1 answer
1k views

Default Postgres privileges not showing on \ddp

As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using: CREATE USER foo WITH PASSWORD 'foo'; \connect bar; GRANT SELECT ON ALL ...
9 votes
4 answers
25k views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0 votes
0 answers
50 views

Postgres usage always using 100 cpu usage

In certain interval (like 60 minutes), postgres always uses 100%cpu. This problem is just starting last Thursday. I had to manually to kill the pid through HTOP. The one that makes me confused is ...
2 votes
1 answer
2k views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
1 vote
3 answers
2k views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
14 votes
1 answer
24k views

Refresh a PostgreSQL materialized view automatically without using triggers

I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this ...
4 votes
1 answer
2k views

List PostgreSQL extensions per database

I have a Postgres 9.5 server with 30 databases. I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin. I know that SELECT * FROM ...
0 votes
1 answer
141 views

idle_in_transaction_session_timeout Parameter changing

I should change the parameter "idle_in_transaction_session_timeout" in my RDS postgresql. As I know with set command("SET idle_in_transaction_session_timeout=5s") I will change it ...
0 votes
1 answer
52 views

How to get variable value in function PostgreSQL in an SQL statement

I am setting a parameter with SET app.id To :appId; CREATE FUNCTION get_car_Price(price_from int, price_to int) RETURNS integer LANGUAGE plpgsql AS $$ DECLARE car_count integer; BEGIN ...
5 votes
2 answers
2k views

Limit of psql_history

I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit ...
2 votes
1 answer
309 views

Using pg_repack to remove OIDs from tables in PostgreSQL

I'm prepping a legacy PostgreSQL 9.5 database for upgrade. I need to remove OIDs from tables without locking the tables for long periods of time. I have developed a strategy that seems to work, but I ...
4 votes
1 answer
4k views

Postgresql 9.5 BRIN index dramatically slower than expected

I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations. I assumed that my situation was ...
8 votes
1 answer
27k views

Alter default privileges for role

I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. Problem ...
0 votes
0 answers
89 views

PostgreSQL 9.5 Create Publication?

Based on the official doc, CREATE PUBLICATION command is available on PostgreSQL 10 and later. However, I have a requirement to replicate PostgreSQL 9.5 to BigQuery using datastream, which require us ...
3 votes
2 answers
4k views

Effectively searching through entire 1 level nested JSONB [closed]

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
5 votes
0 answers
480 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
24 votes
1 answer
4k views

Postgres: How is SET NOT NULL "more efficient" than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit ...
29 votes
2 answers
47k views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
2 votes
3 answers
21k views

How to update complex jsonb column?

I have a table with following definition: create table json_test ( filter_data jsonb); and i insert to it values like this: '{"task_packets": [ { "state": "PROCEEDING", "...
31 votes
2 answers
31k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
3 votes
1 answer
4k views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
13 votes
3 answers
31k views

How to insert 10000 new rows?

Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id). I have used: ...
0 votes
2 answers
189 views

Wal position to wal archive number

I want to manage the copy of archiving to remote location. For that I want to delete all the archives I copied unless they are required by the standby. So, for running pg_archivecleanup $ARCLOC $...
-1 votes
1 answer
192 views

How to upgrade from psql 9.5 to 12 with short downtime and no corruption

We have two databases in two different servers with PostgreSQL 9.5 One database size is 800GB and the other is 1.8 TB. What would be the most efficient and safe way of upgrading both clusters from ...
0 votes
1 answer
95 views

Simplify duplicate INSERT commands placing NULL for values that are not found

I have many trigger functions written in PL/pgSQL that follow the below logic: SELECT my_name INTO l_my_name FROM data WHERE data.my_number = NEW.test_num; IF NOT FOUND THEN INSERT INTO test_event ...
1 vote
0 answers
1k views

Why this aggregate function cannot be converted with pg_upgrade from PostgreSQL 9.5 to PG 14.3?

I am trying to port old PostgreSQL v9.5 database (or family of databases) to PostgreSQL v14.3, with pg_upgrade utility. After many hassles with old v9.5 binaries, source files (had to be recompiled to ...
3 votes
3 answers
367 views

Execution Performance Issue with Similar Query

I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows. If I run a query only on the ...
1 vote
1 answer
108 views

Why is this simple query making up 50% execution time?

I'm using postgresql 9.5. I installed the pg_stat_statements extension to analyze queries and got the following surprising result: scheduler=# SELECT substring(query, 1, 30) AS query, ...
0 votes
3 answers
2k views

How to pivot multiple columns in PostgreSQL 9.5?

I have the sample data as follows and want to find if I can use crosstab function in this case. Sample Data: ITEM | CD | TYPE |PARTS | PART | CNT | Item 1 | A | AVG |2 | 1 | 10 | Item ...
5 votes
1 answer
8k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
7 votes
4 answers
21k views

“using stale statistics instead of current ones because stats collector is not responding”

My postgresql 9.5 logs shows every minute the message: using stale statistics instead of current ones because stats collector is not responding Except a post on serverfault which is not helpfull ...
0 votes
1 answer
133 views

Postgis distance filter does not work as expected

I have a issue with simple PostGIS query. Here is a query: SELECT id, location, ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance FROM pins WHERE ...
1 vote
3 answers
1k views

pg_repack slows down PostgreSQL replication

I have a master PostgreSQL 9.5 server and a standby server. For replication I use repmgr (WAL streaming). Typically the delay between master and standby is <5s: $ psql -t -c "SELECT extract(epoch ...
0 votes
1 answer
171 views

postgresql has more DB connection in staging servers than production server

We have postgresql 9.5 where we use it for an application confluence. in staging and in production both max connection is same. But we see staging uses more connection than production e.g staging has ...
6 votes
2 answers
25k views

PostgreSQL: ANY (VALUES(...)) in WHERE clause causes drastic slowdown

Okay, I previously asked a question regarding a large dataset but it was never answered, so I decided to cut it down and ask about a smaller subset of the previous setup and simplify what I am trying ...
1 vote
2 answers
5k views

Pgpool2 doesn't use the slave for load balancing

I've enabled streaming replication in my postgres 9.5.10 installed from ubuntu xenial repo. I want to enable load balance for pgpool-2 (3.7.0 amefuriboshi) installed from sources. So I have this ...
6 votes
3 answers
17k views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
0 votes
1 answer
49 views

Unable to locate and login to by PostgreSQL

I am using Ubuntu 20.04 LTS, and I have configured PostgreSQL 9.5 to work on the server. I am trying to log in but I have forgotten the password for the user postgres and therefore I tried to reset it ...
9 votes
1 answer
36k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
2 votes
2 answers
3k views

Possible for two simple updates on the same table to deadlock?

Table A is: id integer version varchar data jsonb (large data 1mb) fkToBid integer (references B.id constraint) Table B is: id integer other... Processes are aggressively running the two updates ...

1
2 3 4 5
8