This tag is specifically for PostgreSQL version 9.1
0
votes
1answer
36 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
14 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
0answers
18 views
Failover - automatic switching between servers in Postgresql 9.1
I'm pretty new to Postgresql 9.1 and failover replication. We have a simple master-slave setup, for failover replication, which our application (client) is connected to. On a failure of the master, ...
1
vote
1answer
41 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
39 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 ...
0
votes
1answer
50 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 ...
1
vote
1answer
37 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 ...
1
vote
2answers
36 views
How do I list all schemas in PostgreSQL?
When using PostgreSQL v9.1, how do I list all of the schemas using SQL?
I was expecting something along the lines of:
SELECT something FROM pg_blah;
3
votes
3answers
99 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 ...
0
votes
1answer
26 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.
1
vote
0answers
29 views
Write performance of Postgresql 9.1 with read-only slave
I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
0
votes
0answers
32 views
How I can copy from local file to remote DB in PostgreSQL?
I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?
I am using the following command
\COPY test(user_id, product_id, value)
FROM '/Users/testuser/test.tsv' ...
0
votes
1answer
32 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 ...
2
votes
1answer
69 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
15 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 ...
0
votes
1answer
59 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 @> ...
-1
votes
1answer
55 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 ...
1
vote
1answer
50 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
0
votes
1answer
44 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
57 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
53 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 ...
0
votes
0answers
36 views
Streaming replication WAL files pg_xlog
We are using this awesome tool repmgr developed by 2nd Quadrant for streaming replication.
We set 1000 WALs to be archived. What I noticed though is that on master we have about 600 WALs in the ...
0
votes
1answer
47 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
1answer
57 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
0answers
26 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 ...
0
votes
0answers
4 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 ...
5
votes
1answer
398 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 ...
1
vote
1answer
54 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.
2
votes
0answers
150 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 ...
5
votes
3answers
98 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 ...
1
vote
1answer
55 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 ...
0
votes
0answers
69 views
Truncate table is taking too long in PostgreSQL
I have many databases on the same server, all with same templates. When I execute truncate command on exceptions table in each database, it works fine and executes immediately but on database named ...
0
votes
0answers
45 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
...
0
votes
2answers
73 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
81 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
87 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 ...
1
vote
1answer
53 views
Something wrong in pg_hba.conf
this my configuration:
#postgres.conf:
listen_addresses = 'localhost'
#pg_hba.conf:
local all postgres md5
local all appuser trust
local all devuser trust
local all all peer
i'm connecting in ...
2
votes
1answer
266 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.
...
1
vote
1answer
68 views
Postgres: 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 ...
3
votes
2answers
192 views
Granting access to all tables for a user
I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
6
votes
3answers
165 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
0
votes
1answer
146 views
one trigger for multiple tables
i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 ...
0
votes
2answers
68 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 ...
0
votes
2answers
152 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 ...
5
votes
3answers
191 views
Casting issue when calling a function with composite type parameter
I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.
what could be the issue?
CREATE OR REPLACE FUNCTION ...
1
vote
1answer
203 views
Postgresql nested row_to_json not working
This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like ...
0
votes
1answer
105 views
Postgres 9.1 query plan not using check_constraints on partitions after large data load
I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip ...
1
vote
1answer
81 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 ...
1
vote
1answer
104 views
How to setup a PostgreSQL cluster
Could you point me to a good book about setting up a PostgreSQL cluster? The goal is to set up an active-passive cluster spread across two different data centers to achieve high availability.
1
vote
1answer
133 views
How do I list all tables in all schemas owned by the current user in Postgresql?
I can list all tables in all schemas using
> \dt *.*
but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
