This tag is specifically for PostgreSQL version 9.2
0
votes
1answer
16 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
2answers
34 views
Creating a PostgreSQL SERIAL column using pgAdmin3
When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?
Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
1answer
33 views
Postgres: Given some parent id's how can I SELECT X child records for each?
In Postgres, if I have :
Parent Table
id, name
1, Parent1
2, Parent2
.....
5, Parent5
Child Table
id, parent_id, name
1, 1, Child1
2, 1, Child2
.....
6, 2, Child6
.....
25, 5, Child25
so 5 ...
2
votes
2answers
66 views
PostgreSQL: different way result in big diff in performance
I have 2 similar functions, different way result in big diff in performance.
The PostgreSQL version: 9.2.1.
Function 1
create or replace function func_1() returns text as
$$
declare
v_i ...
0
votes
1answer
23 views
Postgres sql dump import error
I'm facing a issue while importing the dump in my system, i'm getting the error as
" Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;"
and exiting with "out of memory"
We had ...
0
votes
1answer
101 views
which postgres do i have installed? what directory needs to be in my path to run initdb?
I need to use postgresql with ruby on rails.
I have these directories
/Library/PostgreSQL
and
/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2
This 2nd directory name is ...
1
vote
2answers
68 views
Recursive query using plpgsql
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.
I have a data table and a table to link the data together:
DATATABLE
-----------
id ...
1
vote
1answer
39 views
clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server
After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.
Below is the actual command:
...
1
vote
1answer
114 views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
0
votes
0answers
108 views
Install PostgreSQL 9.2 on Windows using WIN1252 encoding.
I had installed PostgreSQL 9.2 earlier and it always installed with the encoding being WIN1252 (the default database was WIN1252). I then some time ago reinstalled it with the encoding being UTF8 (I ...
0
votes
1answer
58 views
Postgresql Run 'Jobs' At Specific Times
I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute .
Scenario :
PostgreSQL 9.2
Need To Run A ...
2
votes
2answers
200 views
PostgreSQL user can not connect to server after changing password
I've met this with 4 roles I created:
After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more.
pgAdmin III show error message:
An error has occurred:
...
4
votes
1answer
73 views
Materialized View PostgreSQL
Is there any possibility of having built-in materialized views in PostgreSQL ?
I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something ...
1
vote
1answer
47 views
Running a TPC-C Benchmark Without sleep() i.e. key-in + think time
We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost ...
0
votes
0answers
3 views
Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability
I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability.
I have 1 master and 1 hot standby configured to replicate the data from ...
1
vote
1answer
124 views
Tuning Postgres config for new Database Server
I have finally gotten a Purpose Built Database machine for a project i'm working on.
The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives.
I have just got the database moved ...
0
votes
1answer
115 views
Ubuntu 12, PostgreSQL 9.2, PostGIS 2.0
At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2?
I checked their website and to my understanding it is not possible...
I hope that's not the case. Can anyone ...
0
votes
1answer
87 views
slow queries - set weight to token type in tsquery - postgresql
Postgresql version 9.2.3!
I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm ...
0
votes
1answer
127 views
Why is a new user allowed to create a table?
I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core:
postgres=# \l
List of ...
3
votes
1answer
65 views
Tools and methodologies to keep to DBs aligned
2 DBs having schemas that represent the same semantic objects.
The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres.
Once in a while ...
0
votes
1answer
58 views
Can this array group count query be improved?
So I have the following query
explain analyze
with tags as (
select unnest(tags) as tag_name from tasks where user_id = 1
) select
count(9),
tag_name
from
tags
...
3
votes
2answers
222 views
Struggling with pgBouncer
I've got Postgres 9.2 and PgBouncer (The version that came with the 'stack builder'). I have a .net application connecting via Npgsql version 2.0.12 on a win7 machine.
My application can happily ...
3
votes
2answers
152 views
Full Text Search With PostgreSQL
i have a table with this rows:
Stickers
------------------------------------------------------
ID | Title |Keywords (ts_vector)
------------------------------------------------------
...
1
vote
2answers
109 views
inet_client_addr in PostgreSQL don't return IP
i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return is '::1/128'.
This is a problem with my script or with the server?
CREATE OR REPLACE FUNCTION ...
0
votes
1answer
44 views
Basic Question on input to PostgreSQL
I have installed PostgreSQL on a Windows XP laptop so I can learn about using PostgreSQL. My problem is that the PostgreSQL tutorial starts of with the instruction
To create a new database, in this ...
2
votes
1answer
297 views
How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?
I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
1
vote
1answer
165 views
Lost postgreSQL password in Windows installation
I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password.
I looked at answers given to a similar question asked 6 months ago. The answer given there required the ...
0
votes
3answers
204 views
Postgresql 9.2 won't start after rerunning installer
All,
I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that ...
4
votes
1answer
55 views
Postgresql, write archive file over mount, secure against simultaneous access?
I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share.
I just use the basic command, given as an example in the postgresql.conf
test ! -f /mnt/server/archivedir/%f && cp ...
1
vote
2answers
251 views
Cannot shutdown old postmaster when upgrading to Postgres 9.2
I'm upgrading to Postgres 9.2.2 (from 9.1.4). When I try to upgrade the DBs using:
pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.2/bin -d ...
2
votes
1answer
119 views
Performance impact of setting PostgreSQL to log all statements
PostgreSQL 9.2.2 on Windows 7 64-bit.
The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting ...
