This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms

0
votes
1answer
314 views

createdb command error : could not connect to database [closed]

I am using the following command for creating database in postgresql. createdb -D pg_default -E UTF8 -h localhost -p 5432 -U pramil -W pramil mydb but this command results prompt for password.When ...
3
votes
1answer
971 views

delete rows in 3 tables with on delete cascade

For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk. Let two constraints on the t3 table, each one with "on delete cascade", if I do: delete ...
1
vote
1answer
124 views

Keeping version history of functions in PostgreSQL

For many of my tables, I've added auditing triggers (based on this wiki page). It works very well and has helped me several times figure out who changed what in the system. We have a Python/Django ...
4
votes
2answers
2k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
3
votes
2answers
524 views

PostgreSQL: Can the archive command for master and standby point to the same directory?

I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server. The only difference in the files is the ...
2
votes
3answers
968 views

Postgres connection access denied on IPv6 address

Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP ...
0
votes
1answer
79 views

PostgreSQL insert into table (not origin) based on a condition on fields on different tables

Lets say you have 4 different tables: table_1 is a table holding the original data: table_1 id1 | id2 | score1 | score2 | name_1 | name_2 ...
0
votes
2answers
363 views

Postgres wont shutdown due to wal archiving

I commanded Postgres to shutdown using the init.d scripts (Linux) over 18h ago. I can still see the processes running: -bash-3.2$ ps -fe | grep postg postgres 2299 3265 0 16:06 pts/5 00:00:00 ...
7
votes
1answer
841 views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
3
votes
2answers
301 views

Change built-in default privileges in PostgreSQL?

Introduction. When I create a database, postgres=# CREATE DATABASE test2 OWNER test2; it is created with an empty privileges column: Name | Owner | Encoding | Collate | Ctype | ...
-2
votes
1answer
369 views

Use select query in stored procedure

I have some questions about stored procedures. Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast or if ...
1
vote
0answers
52 views

How to make continues cluster?

I have a report table with the following index: providerid, date The table is around 30M records and it grows about 100K rows per day. I want to use in the index above as a cluster, but as I ...
3
votes
0answers
115 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 ...
1
vote
1answer
258 views

How to monitor or do vacuum without stop all?

I have postgres9.1 I try to run full vacuum on a specific table , but it run for more than 5 hours. I stop all process against this table and i cannot have the process down any longer. Is there a ...
1
vote
2answers
132 views

Group Database Entries by time difference

I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
0
votes
0answers
441 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 ...
0
votes
1answer
41 views

Storing no of players the last seven days in an online game

I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how ...
0
votes
1answer
359 views

Cannot create PostgreSQL user

I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04. Using the following StackOverflow question, I tried to create a user/role in postgressql: ...
1
vote
1answer
738 views

How to insert binary data into a PostgreSQL BYTEA column using libpqxx?

I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx. What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is ...
3
votes
1answer
2k views

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to ...
0
votes
1answer
87 views

PostgreSQL, How to keep only one schema?

I don't want some users be available to create schemas, and only use the public schema. I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 ...
6
votes
3answers
5k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
1
vote
1answer
867 views

Pass a table array as a parameter to an upsert function in postgresql

i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
0
votes
1answer
182 views

Postgres 9.1 wait events

Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle? I need to find queries which are waiting for a long time, and the events they are blocked on.
0
votes
1answer
446 views

Set field values to newly imported rows in PostgreSQL table with existing data

I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import. Question: For the newly imported ...
-1
votes
1answer
308 views

Passing argument in trigger dynamically

CREATE TRIGGER audit_proc_tr AFTER INSERT OR UPDATE OR DELETE ON "log".hi FOR EACH ROW EXECUTE PROCEDURE "log".audit_proc(argument); CREATE OR REPLACE FUNCTION ...
2
votes
1answer
743 views

Limiting number of results in a Partition using OVER(PARTITION BY)

In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
0
votes
1answer
67 views

Cannot create perlplu function

Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos. From the shell, I have executed: createlang plperl db_name; createlang plperlu db_name; As the superuser running psql, I have ...
1
vote
1answer
1k views

How to use array variable in query in PostgreSQL

Create table t1 ( xcheck varchar[], name text ); CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[]) RETURNS record AS DECLARE xrc as record; execute 'select name from t1 where xcheck @> ...
2
votes
2answers
633 views

PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
1
vote
1answer
860 views

Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost. ie: CREATE OR REPLACE FUNCTION my_function() RETURNS setof record AS $BODY$ -- ...
0
votes
1answer
614 views

Cannot `create function` in plpython3u, permission denied

As postgres user, I have create extension plpython3u; in my database then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true but ...
0
votes
1answer
342 views

Cannot use python3 as stored procedure language in posgresql database

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
1
vote
1answer
481 views

Create database on new partition

I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package. I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
0
votes
2answers
325 views

Starting a Postgres slave that is in hotstandy - stuck after start

I have a postgres 9.1 hot-standby that has been down for a few days and having problems starting back up. When I try to restart the server I see the following: -sh-4.1$ /usr/local/pgsql/bin/pg_ctl ...
1
vote
1answer
718 views

Create log file only for my executed queries

I have been using the following to set up the log file: log_statement='all', log_collector='on', log_destination='on', log_filename='filename' After that, for testing I just executed a query ...
0
votes
2answers
782 views

Interactive INSERT / UPDATE function to implement UPSERT

I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
0
votes
0answers
297 views

Create Log file for insert,update and delete records and retrive the log details

How to create/update log file for each modifications(insert,update,delete) in my database. i cant find any feasible methods to achieve so. can anybody know how to implement this.? if so then please ...
5
votes
1answer
601 views

PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
2
votes
0answers
689 views

“custom archiver out of memory” error when restoring large DB using pg_restore

I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
2
votes
1answer
992 views

How to view the current settings of Autovacuum in Postgres?

I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration. Postgres 9.1 is the version I'm most interested in.
5
votes
3answers
1k views

How to pass a table type with an array field to a function in postgresql

i have a table called book CREATE TABLE book ( id smallint NOT NULL DEFAULT 0, bname text, btype text, bprices numeric(11,2)[], CONSTRAINT key PRIMARY KEY (id ) ) and a ...
0
votes
2answers
130 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
1
vote
1answer
122 views

Single column join vs multicolumn join

In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected. In one scheme a surrogate key is used which means that in the join condition ...
2
votes
2answers
286 views

How can I 'fake' stats for generate_series data?

With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning: ...
0
votes
0answers
161 views

PostgreSQL can't start after tuning some options

I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this ...
1
vote
1answer
150 views

PostgreSQL replication

I have two projects working on different databases. The problem is: I need to replicate changes in the databases only for a few tables. I need it to synchronize the data. Need an advice how can I ...
2
votes
1answer
206 views

PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files?

I have a PostgreSQL 9.1 hot standby slave that had all of its wal files removed. How can I restart the standby server and have the slave resync from the master? Thanks
0
votes
1answer
524 views

Postgres 9.1 statistics in pg_stat_database

I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be ...
4
votes
1answer
2k views

Using Solr/Lucene for searching non-text tables?

I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns. ...