2
votes
1answer
59 views

Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

Steps to reproduce Create database CREATE DATABASE citiesdb WITH OWNER = citiesowner ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' ...
1
vote
1answer
78 views

INSERT using results of CTE INSERT to provide unique id values

I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as ...
0
votes
0answers
25 views

Postgresql replica lagging behind

I restored a master database after a fallback by creating a new basebackup and making the promoted slave stand_by again. Per the logs everything is fine, however the following functions are not ...
0
votes
1answer
22 views

Postgresql: How to add a role that inherits from another?

Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?
1
vote
0answers
30 views

Postgresql 9.2 - Auto vacuum running continuously for 18+ hours

I currently have a table that is updated daily (easily 20%+ rows are updated, all floats/integers) and the vacuum is kicking off and running for more than 18 hours. There are 19 million rows total. ...
-1
votes
0answers
38 views

Bloating effect in Postgresql [closed]

I've done some performance analysis on my DB and I have releved that: Read head 8192 CPU power used 25% Bloating 56% (84GB of 150) 20% occupied by index unused Now I have find that 99% of ...
1
vote
1answer
42 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
4
votes
1answer
83 views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
0
votes
1answer
11 views

Close all connections to postgres without having a connection

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
1
vote
2answers
56 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
1
vote
2answers
41 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
0
votes
0answers
32 views

Postgres 9.2 on Windows

Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
1
vote
1answer
33 views

Postgres settings reported differently in pgsql

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = ...
2
votes
1answer
30 views

PostgreSQL 9.2; Altering a table to use another sequence

I've created a table like so: create table doki_data_item2 (like doki_data_item including defaults); but this new table uses the same sequence as the old table. So two questions: How can I copy ...
0
votes
1answer
59 views

Streaming replication between postgresql 9.2 and 9.3

Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?
2
votes
1answer
53 views

Postgresql 9.2.6 ignoring archive_command

I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between. My ...
2
votes
1answer
79 views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
1
vote
1answer
78 views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
1
vote
1answer
94 views

Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB. These DB are running on ...
0
votes
2answers
87 views

Can't run PostgreSQL locally

I am working to run PostgreSQL is running locally but I cannot connect. I installed postgress using brew, then I downloaded http://postgresapp.com When I try to create a database createdb: ...
1
vote
1answer
49 views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
0
votes
3answers
61 views

Postgresql: Make Column Default a Query

Is there a way I can set up a column in PostGreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
0
votes
1answer
60 views

Complex “activity” based query optimization

I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's ...
1
vote
1answer
80 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
0
votes
1answer
152 views

pg_upgrade 9.2 to 9.3 error

I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server. I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it ...
2
votes
1answer
169 views

Strange Postgres Hstore syntax with curly braces {key=value}

I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program ...
5
votes
1answer
83 views

Is Postgres ignoring my function cost annotation?

Why does this: create or replace function dummy() returns double precision as $$ SELECT random() $$ LANGUAGE SQL COST 777; explain select dummy(); return this: Result (cost=0.00..0.01 rows=1 ...
2
votes
2answers
119 views

How to define alias in an ARRAY_AGG expression?

I'm trying to return pure JSON from a Postgres 9.2 table. SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW)) FROM ( SELECT album, max(release_year) AS release_year, ...
4
votes
1answer
127 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
1
vote
0answers
222 views

PgPool is pushing Postgres to 100% cpu usage

I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
2
votes
1answer
183 views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...
1
vote
1answer
104 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
0
votes
0answers
673 views

PostgreSQL isn't listen on 5432 in Windows 7

I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented: listen_addresses="*" port=5432 And this is the ...
2
votes
1answer
95 views

PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'. Various revisions of this SQL isn't constraining '-/+infinity' input: CREATE TABLE bill ( id ...
2
votes
1answer
138 views

How to solve 'tuple concurrently updated' error?

The following query is performed concurrently by two threads logged in with two different users: WITH raw_stat AS ( SELECT host(client_addr) as client_addr, pid , usename ...
1
vote
3answers
206 views

Newly installed Postgresql 9.2 on same box as 9.1

I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed. I have ...
0
votes
1answer
51 views

How to index refined null values?

I have table with two indexes: latest_channel_snapshots_views_idx (view_count DESC NULLS LAST) latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST) What I want is ...
2
votes
1answer
331 views

postgresql replication - pg_stat_replication is showing empty columns

I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list. $ ps aux ...
0
votes
1answer
1k views

Error: Unable to write inside TEMP environment variable path

I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1. I get the following, rather famous it seems, error: There has been an error. Unable to write inside TEMP ...
1
vote
1answer
43 views

Parsing the value and copying it to another column in postgresql

column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, ...
2
votes
1answer
225 views

Finding by ID in postgresql very slow on big table

I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is ...
1
vote
0answers
105 views

Getting multiple queries with “show transaction isolation level” in pg_activity

I am using PostgreSQL server for my production use. When I fire a query like select * from pg_stat_activity on my server, I am getting 98% of queries like SHOW TRANSACTION ISOLATION LEVEL and ...
0
votes
0answers
91 views

Can't connect to service after updating ODBC driver

I am upgrading a VB6 program at work and one of the changes is that it now uses PostgreSQL 9.2.4 instead of 8. I was getting a 'client encoding mismatch' error, so I updated the ODBC driver, and the ...
1
vote
0answers
72 views

postgres-xc : Address already in use

I am starting a postgres-xc server with two datanodes and one coordinator. I used same parameter for both nodes but from one node I am getting the following error: LOG: could not bind IPv4 socket: ...
0
votes
0answers
40 views

postgres-xc - ERROR: object already defined [closed]

I am configuring two data nodes and a coordinator on postgres-xc. I get eh following error: ERROR: PGXC Node datanode1: object already defined CREATE NODE pgxc_pool_reload ------------------ t (1 ...
1
vote
1answer
146 views

Client Encoding Mismatch when creating new database

I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run ...
0
votes
1answer
202 views

Running a UPDATE on 3 million records

I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table ...
0
votes
0answers
28 views

Login in pgAdmin without password (windows 7) [duplicate]

I want login in pgAdmin without user password (I forgot password) As I konw, I can change pg_hba.conf so, that can be possible to login into pgAdmin without password. Can you tell please what must ...
1
vote
1answer
264 views

Setting up binary replication between two PostgreSQL instances

I have two virtual machines: dbrepa (-.175) and dbrepb (-.191). Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone. I need to have the ...
0
votes
1answer
809 views

Create Language plperl - Error: could not load library plperl.dll

When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found. But in my computer, ...