PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

2
votes
1answer
226 views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
0
votes
0answers
36 views

postgres: access to schema objects within a function

Here's my problem: I have Schema A, owned by User 1, that owns all objects. I've created schema B, owned by User 2. I have granted User 2 all privileges to the objects owned by User 1. When logged ...
1
vote
1answer
21 views

Removing a Key From HSTORE in Postgresql

At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According ...
0
votes
1answer
45 views

When is the postgres auto vacuum executed

I am using an older version of postgres(8.4.20). I know that the auto vaccum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database ...
1
vote
3answers
78 views

Longest matching suffix while using aggregate functions

Background Looking to find the longest matching string suffix. Setup Consider the following fiddle: CREATE TABLE noun ("label" varchar(10)) ; INSERT INTO noun ("label") VALUES ...
1
vote
1answer
47 views

Tables names in a supertype/subtype schema

I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited ...
1
vote
2answers
40 views

SET ROLE via parameterized query

In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I ...
1
vote
1answer
53 views

Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is ...
0
votes
1answer
46 views

Check for blocked queries periodically and cancel them

I have series of update queries which are executed every 5-10 seconds. Sometimes it happens that one of these will block all the pending update queries as for some reason it just freezes. Since I can ...
0
votes
2answers
46 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
0answers
40 views

Unsupported languages in Postgres Full text search

My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search. If I were to go ...
0
votes
2answers
37 views

pg_stat_activity entries with no corresponding process on the client

If I see an entry in pg_stat_activity for a client_addr:client_port combination where there is no process bound to that port on the indicated client, what does that mean? Just that the client process ...
0
votes
2answers
53 views

Postgresql. Generate sets of random integers

With this command I can generate 16384 random integers between 1 AND 200,000. SELECT generate_series (1,16384),(random()*200000)::int AS id I want to generate 10 sets of such integers. Each set ...
1
vote
1answer
36 views

PostgreSQL Common Table Expressions vs a temporary table?

The PostgreSQL documentation on WITH shows the following example: WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), ...
2
votes
2answers
305 views

PostgreSQL maximize performance SSD

I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the ...
0
votes
2answers
57 views

Postgres: error message “does not exist” when dropping existing trigger

I have a trigger associated to a table. When i drop it via DROP TRIGGER IF EXISTS myTrigger on dummytable; postgres tells me NOTICE: trigger "mytrigger" for table "dummytable" does not ...
1
vote
3answers
57 views

Can I perform a MAX over a pair of columns?

I would like to execute the following: SELECT person, MAX( (date, priority) ) FROM table GROUP BY person; It would return a person, date and priority row for each distinct value in the person ...
1
vote
2answers
176 views

Slow querys on billions-rows-table // index used

Since I'm a young developer and not realy skiled in using databases (PostgreSQL 9.3) i ran into some problems with a project, where i realy need help with. My project is about collecting data of ...
3
votes
1answer
50 views

PL/pgSQL - Indexes vs Collation vs Pattern Ops

I'm using PostgreSQL 9.3. Having already read articles and answers on DBA stackexchange: Why would you index text pattern ops on a text column PostgreSQL documentation: Indexes - Opclass Pattern ...
2
votes
0answers
44 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
1
vote
1answer
58 views

Problem with LISTEN - NOTIFY mechanism in PostgreSQL

I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism. I open two consoles and connect to my database using psql client: psql -U postgres test_db In the first console I subscribe to some ...
3
votes
1answer
47 views

PostgreSQL server problem in Windows XP

2014-10-01 09:22:49 IST LOG: database system was interrupted; last known up at 2014-09-30 18:30:24 IST 2014-10-01 09:22:49 IST FATAL: the database system is starting up 2014-10-01 09:22:49 IST LOG: ...
0
votes
0answers
18 views

What is the recommended pgpool mode?

I'm trying to determine which pgpool mode to use (http://www.pgpool.net/docs/latest/pgpool-en.html#config). In order of importance my requirements are: All nodes accessible via shared public ...
1
vote
0answers
25 views

Select all foreign key relationship chains from any set of potentially gapped table names

I am using this query to find all foreign key relationships: SELECT con.relname AS child_table, att2.attname AS child_column, cl.relname AS parent_table, att.attname AS parent_column ...
0
votes
1answer
16 views

Getting information on users payments via subscriptions

My current table db design looks like this : - User has one subscription - One subscription has many payments Table subscription has a user_id, which is FK to user table. Table payment has a ...
3
votes
2answers
54 views

How to save an interval of years in postgresql

I have to save an interval of years, like: id intervalYears 1 2014/2015 2 2015/2016 3 2016/2017 And so on. Those years are like accademic years, so the first year must be lesser then the second, ...
1
vote
1answer
28 views

use variable to determine the name of a column in the select in postgre

I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it. The old table is like this: -- old table (OldTable) name| col_a | ...
0
votes
0answers
40 views

Importing CSV into multiple tables using PostgreSQL

I have a very big CSV file with the following format: TAG,TIME,VALUE as an example row: footag,2014-06-25 08:00:00.0,3400.0 I used to import it easily inside PostgreSQL 9.3 using the following: ...
0
votes
0answers
21 views

when install pgpool is also needed install hearbeat and watchdog

I am trying to get the PostgreSQL services High Availability (HA) and then I am using pgpool-II. When I install and configure pgpool, the WATCHDOG section seems to doesn't works, then I have a ...
0
votes
0answers
16 views

PgBouncer and Application Connection Pooling

I understand that PgBouncer is a dedicated component to handle connection pooling and my application would connect to PG server through PgBoucer. The connection pooling on PgBouncer side is strongly ...
1
vote
1answer
24 views

List distinct column values where those rows share other column values

I'm trying to write a query to detect possibly-invalid data in a PostgreSQL table. We have a table of city names like this: # `city_names` id | name | language | dialect | city_id ...
1
vote
2answers
42 views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
1
vote
2answers
38 views

How to restore postgreSQL schema dump to schema with different name

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema. The dump type is "custom". So effectively I want ...
0
votes
1answer
26 views

Does PGStrom calculate relational comparisons?

I prefer index calculation and comparison be executed on the GPU. I have stumbled upon PGStrom. It seems one could infer that it will calculate index comparisons, but the claim doesn't seem to be ...
0
votes
0answers
32 views

could not connect to pgpool server

I am configure my pgpool server to allow access from: host all all 0.0.0.0 trust The service pgpool is running: user@pgpool:~$ sudo service pgpool2 status * pgpool-II is running. ...
1
vote
1answer
63 views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
1
vote
1answer
22 views

Connection pooling with 3 databases but 1 database as master for repmgr replication?

I'm using pgbouncer for connection pooling against 3 database on different servers, now I'm planning to use repmgr for replication, my question is, can I keep doing connection pooling on these 3 ...
0
votes
0answers
36 views

Using Configuration Management for DBA tasks?

We have a couple of Postgres 9.3 database servers in our system, all of them with their respective config files, databases, roles, role groups, permissions on schema/tables, extensions etc. We use ...
1
vote
1answer
84 views

PostgreSQL performance worse when adding CPUs

I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this: Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs. Ubuntu 14.04, standard PostgreSQL 9.3 ...
0
votes
0answers
21 views

Postgresql: Domain of two values

I have to create a custom domain that is an interval between two years, but i actually need both years. For example: '2013/2014' '2015/2016' Is there a way to pack both years inside a single ...
0
votes
1answer
53 views

How to connect to an Amazon PostgreSQL database using SSL

I'm trying to log into an Amazon PostgreSQL DBS using SSL. I have all credentials but my psql version doesn't seem to support SSL. I've tried searching Postgres docs but all I see is how it has ...
1
vote
1answer
37 views

How much should I care about prepared statements for simple queries?

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements. The question originates from the fact that I recently noticed ...
1
vote
1answer
39 views

Format timezone to +-#### instead of +-##

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O". PHP Docs: O | ...
0
votes
1answer
16 views

Revoke delete from table for database owner

I am trying to revoke the DELETE privilege on a table for a database owner. I execute the command: REVOKE DELETE on my_table FROM database_owner; But when querying this via SELECT ...
1
vote
1answer
41 views

Replicating a postgres database for testing

I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better ...
0
votes
0answers
21 views

pgAgent does not run

I referred this to set up a scheduled job and did everything as it mentioned(ran the pgagent.sql as well) I need a very simple task to run periodically. In the 'Definition' tab in the steps, few ...
1
vote
1answer
47 views

Proxy authentication for PostgeSQL

The following seems likely to be a fairly common situation, but I haven't seen any solutions. I have a PostgreSQL database containing views that are specific to the $user, in order to restrict data ...
0
votes
1answer
35 views

How the xlogs files travels form Master to Slave

I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs. If I have this configuration in Master postgresql.conf: archive_command = 'test ! -f ...
0
votes
2answers
52 views

for archive_command use an script rather than a command

I want to create a configuration to get a PostgreSQL HA. In my Master I want to configure a postgresql.conf file with this: archive_command = 'local_backup_script.sh "%p" "%f"' instead of: ...
1
vote
0answers
21 views

Does pg-pool-II support two phase commit?

When pg-pool II is used with sharding, what happens if I want to do a transaction which involves two nodes ? Does pgpool support two phase commit ?