This tag is specifically for PostgreSQL version 9.1
6
votes
3answers
212 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 ...
5
votes
3answers
201 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 ...
5
votes
3answers
133 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 ...
5
votes
2answers
112 views
PostgreSQL: Query DB for rows containing integer[] > or < than specified value
I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
5
votes
1answer
227 views
Unexpected Seq Scan when doing query against boolean with value NULL
I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.
CREATE INDEX index_table_on_auto_renew ON table USING ...
5
votes
1answer
421 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 ...
5
votes
1answer
416 views
Database archive solutions
In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
4
votes
2answers
520 views
Postgresql 9 speeding up indexed inserts (JPA)
I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
4
votes
1answer
2k views
How to use aes-encryption in PostgreSQL?
I tried aes-encryption by using following statement:
SELECT encrypt('test', 'key', 'aes');
which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
3
votes
1answer
107 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
310 views
Index max row size error
Is there a upper bound for an array column?
I am getting this error when inserting into the array field -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
Here's my ...
3
votes
2answers
67 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 | ...
3
votes
2answers
196 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
3answers
216 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 ...
3
votes
1answer
373 views
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
3
votes
2answers
127 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 ...
3
votes
2answers
387 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 ...
3
votes
1answer
481 views
Replication has failed; how to get going on again?
I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of ...
3
votes
3answers
307 views
Maximum number of databases for single instance of PostgreSQL 9
Developing a multicustomer application we plan to use a different database for each customer.
But it could be more than 1000 customers (applications).
Will PostgreSQL handle it without any problems?
...
3
votes
0answers
46 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 ...
2
votes
2answers
247 views
Is it a good idea to move high-volume and highly-accessed tables to a separate database?
Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
2
votes
2answers
306 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 ...
2
votes
1answer
413 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.
...
2
votes
2answers
172 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:
...
2
votes
1answer
42 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 ...
2
votes
1answer
2k views
PostgreSQL: changing password for a user is not working
I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres
I do
$ sudo -u postgres psql
psql (9.1.5)
Type "help" for help.
postgres=# ALTER USER postgres WITH ...
2
votes
1answer
127 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 ...
2
votes
1answer
105 views
Postgres Backup queries on AWS after reading Postgres docs
I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance.
So there are 2 ways to do it.
Create a dump using pg_dump.
File level backup with ...
2
votes
2answers
110 views
Which performance can I expect?
I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1.
I'm ...
2
votes
2answers
154 views
How do I save functions to individual files in PostgreSQL?
I maintain a legacy application that uses a PostgreSQL database.
The application is heavily dependent on stored procedures (aka functions).
I want to save these functions to files named after the ...
2
votes
1answer
80 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 ...
2
votes
1answer
142 views
How to switch master server and warm standby (with streaming replication) in PostgreSQL?
I would like to know what is the way to switch the role of 2 servers when using warm standby?
I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
2
votes
2answers
155 views
Select a range of integers
I have a 'range' of integers (in this case destination ports) that occur:
For example: 33848-33254.
I want to select 'all' of these without having to do one at a time. is there a range operator? Or ...
2
votes
0answers
205 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 ...
1
vote
3answers
53 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 ...
1
vote
2answers
91 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;
1
vote
1answer
59 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
1answer
70 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 ...
1
vote
1answer
268 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 ...
1
vote
2answers
69 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 ...
1
vote
1answer
95 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$
-- ...
1
vote
1answer
79 views
What is the purpose of the keyword “ID” in PostgreSQL?
I'm new to PostgreSQL and I ran into problems using the following query:
SELECT * FROM table1 WHERE ID = 1
ERROR: column "id" does not exist
LINE 2: WHERE ID = 1
where ID is a column I ...
1
vote
2answers
31 views
Forgotten PostgreSQL Windows password
This morning I’ve been trying to connect the Postgresql database on my Windows 7 professional desktop.
The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
1
vote
1answer
86 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 ...
1
vote
1answer
89 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
1
vote
1answer
88 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
2answers
108 views
postgresql sticking “enable_seqscan=False” in a UDF sensible?
I have a query which is taking 40 seconds to execute over the 80ms that it should if postgres where to filter off the primary key rather than doing a sequential scan. I have to force postgres to ...
1
vote
1answer
79 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.
1
vote
1answer
66 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 ...
1
vote
1answer
119 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.
