This tag is specifically for PostgreSQL version 9.2
5
votes
1answer
56 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
35 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
80 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 ...
0
votes
0answers
52 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
58 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 ...
0
votes
1answer
48 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
64 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
47 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
38 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
95 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
32 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 ...
0
votes
1answer
59 views
Trigger - Add to a date using a subquery
(postgresql 9.2)
I'v 2 tables
Table 1 (extinguisher)
extinguisher_id (serial)
type_designation_extinguisher_type(text)
manufacturing_date (date)
life_date(date)
Table 2 (extinguisher_designation)
...
1
vote
1answer
122 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
0answers
229 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
27 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
106 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
52 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
58 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
55 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
32 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
83 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
127 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
37 views
Show clients with staff assigned and no staff assigned
I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query:
SELECT g.name AS group, cl.name_lastfirst_cs AS client
FROM clients ...
1
vote
1answer
94 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
286 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, ...
0
votes
1answer
63 views
Is PostgreSQL appropriate for processing this large but simple dataset? [closed]
I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y ...
-3
votes
1answer
68 views
Make “NOT NULL” default in Postgresql 9.2 [closed]
I use NOT NULL a lot when creating columns.
Is there a way to make this default when defining columns? In other words, make column is_nullable default to NO. (I realize this would make it harder ...
2
votes
0answers
102 views
PostgreSQL index array of int4range using GIN / GIST - custom operator class
Here is my table:
CREATE TABLE
mytable
(
id INT NOT NULL PRIMARY KEY,
val int4range[]
);
I want to index the val column:
CREATE ...
0
votes
1answer
53 views
changing server roles in streaming replication
I want to implement streaming replication using postgresql-9.2.4 in my reporting application. I came stuck on the below scenario.
create master and slave with synchronous replication which is ...
4
votes
2answers
284 views
PostgreSQL CREATE TABLE creates with incorrect owner
I'm using PostgreSQL 9.2.4. When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly ...
1
vote
1answer
77 views
Do I need to perform any tasks after Postgres goes into recovery mode?
For starters, I do not have any sort of automated failover in place.
After two scenarios, I'm unsure of the state of the database and any required actions, if any, to take:
My master server ...
2
votes
1answer
172 views
I need help understanding Postgres's archive cleanup functionality
I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform ...
1
vote
2answers
55 views
Constraint on Composite Type
How can I create a constraint on a sub-field of composite type?
Pseudocode
create type axis(
major_axis float,
minor_axis float,
angle float constraint angle_constraint check(angle ...
3
votes
1answer
203 views
Group By primary key or DISTINCT increase query time over 1000x with limit
Also see http://stackoverflow.com/questions/17741167/hibernate-jpa-improve-performance-of-distinct-query but I realized this is mainly a PostgreSQL issue.
My application uses a 3rd party extension to ...
1
vote
1answer
97 views
Why does Postgres generate an already used PK value?
I'm using Django, and every once in a while I get this error:
IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
DETAIL: Key (id)=(1) already exists.
My database ...
0
votes
1answer
41 views
Wrong return results
I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
0
votes
1answer
57 views
pg_upgrade fails with lc_ctype cluster values do not match
I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X.
When trying to upgrade the database I ...
4
votes
1answer
173 views
Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?
The pg_xlog directory for my master cluster looks like this:
-rw------- 1 postgres postgres 16M Jun 21 21:42 000000010000000000000001
-rw------- 1 postgres postgres 16M Jun 21 22:42 ...
1
vote
1answer
80 views
How does Postgres know which logs have been archived?
I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave ...
0
votes
1answer
483 views
How to retrieve data from Postgresql index using java
I am writing an application for searching, using text search of Postgresql. I am a beginner in Database. I created a Gin index and i am trying to get data from it. My code is
import ...
5
votes
1answer
141 views
PostgreSQL “freeze”/“unfreeze” command equivalents
In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply ...
3
votes
2answers
102 views
PostgreSQL: Executing DDL on every schema
I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my ...
1
vote
1answer
203 views
Why does `pg_lsclusters` not list my Postgres cluster?
I apt-get installed Postgres 9.2.4 (using PPA).
I used pg_dropcluster --stop 9.2 main to remove the default database cluster, because I intended on creating a new cluster on a separate SSD volume (a ...
0
votes
0answers
170 views
How to import a text file with '|' delimited data to PostgreSQL database? [closed]
I have a text file with | delimited data that I want to import to a table in PostgreSQL database. PgAdminIII only exports CSV files. I converted the file to a CSV file using MS excel but still was ...
0
votes
0answers
165 views
PostgreSQL 9.2.4 (Windows 7) - Service won't start, “could not load pg_hba.conf”
I am trying to get Postgres 9.2.4 to run as a service on Windows 7. After installing postgres, the service was running fine. However, after setting postgres up as a server for another program, the ...
0
votes
0answers
87 views
postgres-xc: ERROR: cannot set transaction read-write mode during recovery!
I am trying to make a HA in postgres-xc After creating an environment of streaming replication I am trying to create a table in default database as coordinator logged in. But it gives me the following ...
1
vote
1answer
314 views
Postgresql function to create table
I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:
CREATE OR ...
3
votes
1answer
32 views
Workaround the snapshot isolation in a writable CTE
The postgres docs say:
The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the ...
2
votes
2answers
334 views
Postgres 9.2 select multiple specific rows in one query
I have a table with three columns,
Id Serial.
Value real.
timein Timestamp.
I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
