PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

6
votes
0answers
264 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
6
votes
0answers
256 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
5
votes
0answers
86 views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
5
votes
0answers
144 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
4
votes
0answers
142 views

PostgreSQL Transaction Committing for hours

I'm running into an issue whereby I have two connections from a user to my PostgreSQL server which have been running for about 4 hours and have been in a commit state for quite some time (at least 1 ...
4
votes
0answers
198 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
4
votes
0answers
82 views

Postgres TupleTable like data structure

I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to ...
3
votes
0answers
112 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
3
votes
0answers
61 views

Postgres multidisk master replicating to single disk slave

I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
3
votes
0answers
626 views

psql: FATAL: sorry, too many clients already

I am suddenly getting this error when either trying to access the website that uses the postgresql database, or even when using the psql utility or pgadmin3. My database is set to handle 150 maximum ...
3
votes
0answers
203 views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
3
votes
0answers
270 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
3
votes
0answers
549 views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
3
votes
0answers
265 views

Stubborn autovacuum process slow things dows

We are facing a strange problem since yesterday. In a database of ours there is a partitioned table containing historical data. That is, every week's data goes to a different partition and stays ...
3
votes
0answers
134 views

What happen when Postgresql tablespace is null?

I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution ...
3
votes
0answers
238 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
2
votes
0answers
59 views

Prepared statement overhead

I am using libpqxx to communicate with a PostgreSQL 9.3 server. For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each. If they are all ...
2
votes
0answers
50 views

Why is the CTE much worse than inline subqueries

Im trying to better understand how the query planner work in postgresql. I have this query: select id from users where id <> 2 and gender = (select gender from users where id = 2) ...
2
votes
0answers
57 views

UPDATE/DELETE performance on partitioned table

Hi I'm using table partitioning in my Postgres 9.3. I've notice that the DELETE and UPDATE queries are much slower when I'm doing them via the parent table. I see that the constraint_exclusion is ...
2
votes
0answers
52 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
2
votes
0answers
46 views

Cannot drop a Postgres table

I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop. I need to drop a few tables in my Postgres DB. I tried the ...
2
votes
0answers
39 views

How can I avoid breaking Postgres sync replication when building a large index?

We have pairs of Postgres 9.2 servers using synchronous replication. Sometimes rebuilding an index on a large table will break replication due to WAL segments not being kept around long enough. We are ...
2
votes
0answers
45 views

How to optimize update using join in postgresql

I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
2
votes
0answers
53 views

Postgres and numeric json arrays

I have a table defined as: create table dummy (jdata jsonb); I inserted the following two rows: insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ...
2
votes
0answers
44 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
2
votes
0answers
28 views

Can you force pgpool to require SSL connections?

Can you force pgpool to drop non-ssl connections? I know you can with postgresql (Force PostgreSQL clients to use SSL?), but dropping that same syntax (hostssl instead of host) results in a ...
2
votes
0answers
147 views

Stack Depth Limit Exceeded (After delete trigger)

CREATE TABLE parent ( parent_id VARCHAR(30) PRIMARY KEY ); CREATE TABLE child ( parent_id VARCHAR(30) REFERENCES parent ON DELETE CASCADE, child_id VARCHAR(30) PRIMARY KEY ); CREATE OR ...
2
votes
0answers
260 views

Is there a way to replicate Postgres databases in Amazon RDS between regions?

We have a Postgres RDS instance in the AWS Oregon Region. For some supposed legal reasons, we have to host our client's European data in the Ireland AWS Region, but we need that data synchronized to ...
2
votes
0answers
231 views

Index for finding an element in a JSON Array in PostgreSQL (with multiple JSON fields)

Original question of what this is based on: http://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array This works fine if you only want simple matches. Suppose tracks ...
2
votes
0answers
42 views

Efficiently joining two irregular series representing changes

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.) I have a number of tables that represent a series of changes. These tables all have the same structure: ...
2
votes
0answers
280 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
2
votes
0answers
75 views

How to make PostgreSQL default_tablespace work properly?

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'? I made the following script, with it's output following, to show the problem I am having : #!/bin/bash # export ...
2
votes
0answers
45 views

Postgresql import user from linux

I install postgresql 9.2 on a Ubuntu 12.10 machine I tried to import linux's user to postgresql but only way I can figure out is CREATE USER Jack WITH PASSWORD '1234'; and do it 10 times (if ...
2
votes
0answers
368 views

Converting a SQL_ASCII database to UTF-8

I have a postgresql (version 8.1) database that has the encoding set to SQL_ASCII, combined with several apps that some are running using UTF8 and some with LATIN1. With this scope I have a database ...
2
votes
0answers
94 views

Postgres don't start after an unexpected shutdown

After an unexpected shutdown in postgres 8.4, I'm getting this error. couldn't open the file 'pg_xlog/....'. No such file or directory. invalid primary checkpoint record. Complete message in ...
2
votes
0answers
1k views

Setting password for 9.3 PostgreSQL server

I currently have a 9.3 server running (on Ubuntu 14.04) with "trust" set as the authentification method in pg_hba.conf. How can I set the password again. In pgadmin the server currently doesn't allow ...
2
votes
0answers
99 views

Postgres: missing disk space after truncate and re-insert

We've got a IP address range dataset stored in a table that was truncated and then re-populated with a slightly modified version of the dataset (almost identical in size). However, after truncating ...
2
votes
0answers
51 views

Postgres Replication From Different Masters?

I'm getting a Postgres 9.3.3 install sorted out. My application is Drupal (so read/write), but low-traffic, so all application traffic is sent to the primary master. I have two sites (main and ...
2
votes
0answers
193 views

Why does this query cause the Postgres worker process to consume so much memory?

I have a PostgreSQL 9.3.4 instance running in a CentOS 5.x VM (Linux 2.6.18-308.8.2.el5xen) with 6GB RAM. I've set shared_buffers to 1GB and work_mem to 1MB (a low value for testing). Here are my ...
2
votes
0answers
605 views

Get postgres snapshot from Amazon RDS

I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with. Right now I'm doing a pg_dump when I ...
2
votes
0answers
2k views

Unable to install postgresql 9.3 on windows 8.1

I have been trying, without success, to get posgresql 9.3 up and running on Windows 8.1 on and off over the last ten days. Here is what I have done so far: I got the windows installer from ...
2
votes
0answers
88 views

Postgresql | Database with OID doesn't exist

So, how it started, DB started throwing errors about "database with OID 1" 2014-02-07 22:54:46.561 GET >ERROR: database is not accepting commands to avoid wraparound data loss in database with ...
2
votes
0answers
190 views

Fastest way to get the length of the intersection of two text arrays in PostgreSQL

This is what I am currently using: CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS $$ select array ( select unnest($1) intersect select unnest($2) order by 1 ...
2
votes
0answers
667 views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
2
votes
0answers
168 views

How do you stop Postgres from performing writes inside base/ directory during pg_dump?

We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec ...
2
votes
0answers
141 views

Limiting PostgreSQL logging

I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this: CREATE TABLE testtable ( id SERIAL PRIMARY KEY, name TEXT UNIQUE ...
2
votes
0answers
106 views

Replication slave re-reading WAL on restart

I have a standard streaming replication setup going with PostgreSQL 9.1. It works fine. However, if I stop the slave cleanly (to move it or perform other maintenance) and restart it a short time ...
2
votes
0answers
296 views

Postgres: PANIC: WAL contains references to invalid pages

I have a slave database that is being replicated from a Master DB. All of a sudden, my slave DB just exited and wasn't able to restart. Here are the error messages when it exits. Similar ones appear ...
2
votes
0answers
546 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
2
votes
0answers
86 views

How can I use an environment variable in a Postgres function?

Let's say I have a windows user environment variable SOME_STRING. Is it possible to use its value in a PL/pgSQL function? If not, do I need to use an untrusted language (like PL/pythonu) or can I ...