All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

7
votes
1answer
107 views

Postgres with pgpool architecture

Below is an example pgpool architecture: This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends ...
0
votes
0answers
21 views

Postgres Public Synonym Issue

PostgreSQL ver 9.4.5. Linux OS. Application: Web Based Platform: App Server (java) --> jdbc call --> Database Server (PostgreSQL) I do know that PostgreSQL does not support the public ...
0
votes
1answer
31 views

Do Foreign Keys Impose Uniqueness

Do FOREIGN KEYs impose the uniqueness of the the referent? I.e. if I have a foreign key that refers to a UNIQUE constrained column, will the FOREIGN KEY column be unique? I'm particularly wondering ...
1
vote
2answers
25 views

Recreate all foreign keys in all tables as deferrable (batch)

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?
2
votes
1answer
26 views

Replace content of only one table with new version (should disable foreign keys?)

I have 2 tables. 2nd has foreign keys to reference 1st. I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and ...
0
votes
1answer
28 views

New column to a base table

I want to add a new column to a base table, and add that new column to an existing view... So all selects/updates/deletes translate back to the original table How can I do that? Thanks ...
7
votes
1answer
82 views

Why does adding a smallint column to a postgres query increase execution time 60X?

Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2. Here is the table: cow_dev=# \d carc ...
2
votes
2answers
45 views

PostgreSQL is using more memory than I'd expect

I have PostgreSQL configured mostly how pgtune recommended, and the settings are supposedly conservative. PostgreSQL is using more memory for a single connection than the settings would suggest is ...
0
votes
0answers
26 views

Is it possible to do a VACUUM FULL and allow read-only access to the table?

I have a PostgreSQL (9.3ish) database which has a lot of data written to it frequently. Disk space is constantly being used. I have had success with VACUUM FULL with trimming the bloat and reducing ...
9
votes
1answer
386 views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
0
votes
1answer
41 views

Three queries returning 1 row each

So.. I've got: 3 DB Queries returning 1 row each .... SELECT value FROM ja_settings WHERE varname='clickatel_api_id' SELECT value FROM ja_settings WHERE varname='clickatel_user' SELECT value FROM ...
6
votes
1answer
58 views

Index not used with `=any()` but used with `in`

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t ...
2
votes
1answer
30 views

Postgres plpgsql: Is a view better than a select query in a plpgsql function

I have a recursive query to the general effect of: WITH RECURSIVE text1 (selfid, parentid, text) AS ( SELECT DISTINCT text.selfid, text.parentid, text.text FROM text WHERE text.selfid = ...
1
vote
0answers
42 views

Why does this function return an error by itself, but when called by another function it doesn't return an error?

I have a function that calls a sub-function as part of the function. When running the sub-function with the same data it throws an error, when running the main function it doesn't. I would have ...
1
vote
1answer
50 views

Simplest filesystem backup of Postgres database

Situation: A backup system performs filesystem-level daily backups on a server containing a large but fairly static PostgreSQL 9.2 database. The filesystem backups are incremental and generally take ...
0
votes
0answers
35 views

How to get CPU and memory usage of PostgreSQL server and queries from database system views?

We are looking for the way to get CPU and memory usage data of PostgreSQL 9.x for the monitoring purposes. The requirement is to provide the data on server, database, session and query level. We are ...
3
votes
0answers
94 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
1
vote
1answer
130 views

Slow query - Optimize - PostgreSQL

I've got a slow query here... could you please help me to optimize it? Query: SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer, sum(revenue) AS revenue, ...
4
votes
1answer
45 views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they ...
3
votes
1answer
79 views

Versioning data in a database [duplicate]

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
1
vote
1answer
43 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
3
votes
1answer
15 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
1
vote
1answer
86 views

Mapping between each bit in a bitmap index and tuple-pointers

Bitmap Indexes are described here, but I don't understand some part of it. A plain indexscan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. ...
2
votes
0answers
53 views

Postgresql Streaming Replication Error: WAL segment removed

I want to set up PostgreSQL streaming replication, but get the following error: FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000006A has already ...
1
vote
0answers
51 views

How to fix postgres stuck in recovery mode?

I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space ...
6
votes
1answer
348 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
5
votes
0answers
144 views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
2
votes
2answers
81 views

Filesystem tuning for PostgreSQL

I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to get a good read-write (50/50-balance) performance keep a ...
4
votes
1answer
67 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
3
votes
1answer
27 views

Postgres - estimate end time for a long update

I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three ...
2
votes
1answer
30 views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
0
votes
1answer
40 views

How to return a set of rows from this function?

I am new to Postgres functions and have a table with the following structure: CREATE TABLE options.options ( delta double precision, gamma double precision, rho double precision, theta double ...
0
votes
0answers
21 views

Postgres 9.2 replica can't catchup to master

I have configured a master/replica setup for postgres 9.2 on Amazon Linux. PS output shows that the replica is connected and streaming, but also recovering. This has been going on for about 24 hours. ...
0
votes
0answers
15 views

Where to put snowball files to add a snowball stemmer to PostgreSQL database?

I've downloaded Czech snowball stemmer source files (.c, .h files) and compiled them into .o files and I know the correct SQL syntax for adding the dictionary (it's in the docs). I keep getting the ...
5
votes
1answer
94 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
1
vote
2answers
69 views

Postgresql text column character Limit

Im not even close to a DBA, so plain English please. I recently needed to look into a performance issue on a relatively small PG database, but yet is performing very slow. the server is running PGSql ...
0
votes
1answer
27 views

postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/

While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file: /usr/share/postgresql /etc/postgresql/9.3/main/ ...
4
votes
1answer
41 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...
1
vote
1answer
37 views

Migrate Postgres from VM to VM

I have 2 VMs, both are identical in structural terms (came from the same snapshot, nothing installed, upgraded or removed). One VM is "clean", never used. The other VMs was heavily used and it has ...
4
votes
2answers
88 views

Optimize a query with small LIMIT, predicate on one column and order by another

I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times: Query #1 EXPLAIN ANALYZE SELECT posts.* FROM posts WHERE posts.source_id IN ...
3
votes
0answers
83 views

Postgresql Strange Characters

I've found a lot of strange characters on data rows like this ÃÃÂà The data inserted by perl script which converting to UTF8 ...
1
vote
1answer
220 views

Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset

I'm trying to upgrade my PostgreSQL 9.4 database to PostgreSQL 9.5 but everytime while running sudo pg_upgradecluster 9.4 main I get this error: Stopping old cluster... Disabling connections to the ...
3
votes
1answer
38 views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
0
votes
1answer
11 views

How Can I Access the postgres database in a Script Executing pg_dumpall?

I want to run the following script using my role which includes the postgres database. pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql Here are the settings in pg_hba.conf: # ...
3
votes
1answer
46 views

PostgreSQL translate special characters?

Description: PostgreSQL 9.3 String: 'ì ằ ú ề' Desired Result: 'i a u e' My code: select translate ('ì ằ ú ề', 'ìằúề', 'iaue') ; -- it works. Result: i a u e Question: If I use it this way, I ...
1
vote
0answers
10 views

how to clear the postgrsql-9.1-main-log

Is there an easy way to clear the logfile of postgresql? the logfile is 6044 MB, can you use cp /dev/null file-name or do you mess up the database?
1
vote
0answers
14 views

remote postgres user logon with certificate

I would like to have each remote user logon using an SSL certificate and use their postgres logon role to better track their use of the system. I can set up a certificate for a given user per the ...
1
vote
0answers
28 views

postgresql columns are inside quotes - “ ” and uppercase but occurs relation does not exist error when queried in java

I'm using Java and Postgresql and I've created a relation "UserTable", and when I open PgAdmin, this is how I see my database. But when I query the UserTable, in Spring Tool Suite using Java, I get ...
0
votes
1answer
44 views

Postgres datetime sort slow and not using index

Using Postgres 9.4, I'm trying to order a query by an indexed datetime column. explain SELECT g.*, array_agg(gp.platform_id) as platform_list, array_agg(gm.metacritic_id) as ...
3
votes
1answer
39 views

Join table into different columns

I have two tables (simplified). table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer) I want to join these two tables, but depending on the type, I want ...