PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
0answers
116 views

postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407

This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log. # tail -f /var/log/postgresql/postgresql-9.3-main.log 2015-01-08 14:10:16 IST CONTEXT: automatic ...
1
vote
1answer
51 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
1
vote
0answers
46 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
1
vote
1answer
457 views

Postgresql memory usage [closed]

I'm trying to optimize my database (its quite heavy), I have external server only for it with 6GB RAM. I was looking for settings and saw that WORK_MEM is setted to 36MB. I thought it is possible just ...
1
vote
0answers
117 views

export data to xml by the use of query to xml then importing it using xpath

i have use QUERY_TO_XML function in order to export data to xml , after that i want to export this data. this data might contain reserved chars in the xml tags like "' & ..." ex electrical ' ...
1
vote
1answer
57 views

Table-based custom type does not verify check constraints

Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table? Assume the following table that serves as a source for the ...
1
vote
0answers
120 views

ETL - Moving data from Postgres/Oracle to a TripleStore (RDF)

I'm looking for pointers on how to handle an ETL process that I have to implement for my organization. I know how to query SQL database but i'm not SQL DB specialist. I work more in the sphere of the ...
1
vote
1answer
890 views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
1
vote
0answers
72 views

Postgres drop type XX000 “cache lookup failed for type”

I'm currently working on a PostgreSQL 9.2.x Database with a lots of Clients, and tables and functions. We deploy code constantly and sometimes it is necessary to even drop a type or a function due ...
1
vote
0answers
60 views

Postgresql service run failure

Trying to setup replication through the wal shipping using Postgresql 9.3 x64. Used this instruction from youtube. The problem is - the slave postgresql service won't run. This is what in logs ...
1
vote
1answer
353 views

Slow max, min query in Postgresql View

I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" ...
1
vote
0answers
85 views

PostgreSQL Idle Session in Transactions with RELEASE_EXEC_SVP queries and lock

I am having a problem with having idle sessions in transactions. In pgAdmin Server Status, it is showing "RELEASE_EXEC_SVP_XXXXXX" (XXXXXX data are varied) as its query and it's locks also contain a ...
1
vote
1answer
174 views

How to remove object from json array?

My table: CREATE TABLE items ( id BIGINT PRIMARY KEY NOT NULL, name VARCHAR, images json ); images format: [ { "id": "owner", "full": "<url>", "thumb": ...
1
vote
1answer
174 views

How to design tables for airbnb style booking system [closed]

all. My English is not very good, but I will try to describe my question as clear as possible. I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system ...
1
vote
0answers
94 views

Multiple databases with medium tables or Single database with huge tables

I've developed a shop management software using Postgresql and JAVA. I would like to know which of the solutions below is the best one for my specific situation: Only one database with huge tables ...
1
vote
2answers
107 views

Need help designing a database

We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a ...
1
vote
0answers
99 views

Optimizing SELECT queries

I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
1
vote
0answers
83 views

Pgpool load-balancing vs. consistency

In what ways is consistency weakened when using pgpool-II load-balancing on Postgresql servers running streaming replication? Obviously with asynchronous replication you can insert a row on the ...
1
vote
0answers
131 views

How to keep the sync active with Bucardo

I have a master-master sync setup between 3 nodes or 3 different host machines using bucardo. All works good. However when one of the database or a host is not reachable or goes down for some reason, ...
1
vote
1answer
61 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
1
vote
0answers
18 views

Is there a faster way to get UNION ALL behavior in Postgres? [duplicate]

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
1answer
50 views

Is there a faster way to get OR behavior in Postgres?

Is there a faster way to do an inner join with an OR clause. I have a query that looks something like this: INNER JOIN (select u_id, b_id from t1) AS t2 ON t2.u_id = t1.u_id OR (t2.b_id = ...
1
vote
0answers
115 views

Postgres advisory lock deadlocks itself?

I am executing pg_advisory_locks using the following set of statements: SELECT pg_advisory_lock(317,2); UPDATE posts SET dislikers = array_remove(dislikers, 7) WHERE id = 317; update posts set ...
1
vote
1answer
89 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
1answer
166 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 ...
1
vote
1answer
161 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 ...
1
vote
0answers
77 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 ...
1
vote
1answer
141 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 | ...
1
vote
1answer
53 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
0answers
32 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 ?
1
vote
0answers
45 views

Can we pass a varchar/text datatype in to a variable in pgbench?

--pgbench sql script [pg93@db1 load_test]$ cat get_email.sql \setrandom cnt 1 100000 \set v_username 'user_' || :cnt begin; select get_user_email(:v_username); end; get_user_email() is a function ...
1
vote
1answer
134 views

Postgres create an insert return rule

I am failing to create a rule which will return the insert's data. The table is: CREATE TABLE foo(a int, b text); My attempt for the rule is: CREATE RULE return_data AS ON INSERT TO foo DO ...
1
vote
0answers
63 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
175 views

pg_basebackup could not get WAL end position from server

I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...
1
vote
1answer
89 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
1
vote
1answer
179 views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
1
vote
0answers
353 views

Debugging Postgres 9.3 Triggers

Right now I have a trigger in my database that is giving me some issues. I've set up this line so I can check what is being fetched from a query RAISE NOTICE 'hstore_key = %',hstore_key; as well I ...
1
vote
0answers
81 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1. The scenario is: I receive heavy insertion into Table1 (about 100 rows per sec). For each new row, I have to check the previous and next row of the source table if those are ...
1
vote
0answers
31 views

Postgres_Failover Not Working

I was able to configure and Replicate between master and Slave ,so when we insert anything on master db it will automatically replicate to slave, Now,requirement is that if we shutdown master can we ...
1
vote
0answers
255 views

Getting vacuumdb :invalid page header in block error while vacuuming

I am newbie to PostgreSQL and using version 9.2.Disk space for data folder is full causing DB to stop. When i am trying to perform a vacuum to reclaim space, i am getting below error. vacuumdb: ...
1
vote
0answers
96 views

listen_addresses does not respect postgresql.conf

I have PostgreSQL 9.3, and I'm trying to set it up on CentOS. One part that has been driving me nuts is the listen_addresses setting. I set it to * inside postgresql.conf: listen_addresses = '*' ...
1
vote
1answer
172 views

create trigger hangs

I have a trigger function that just copies inserted values to another table. Then, each night I need to drop and recreate this trigger for some export operation. --trigger function CREATE OR ...
1
vote
0answers
209 views

How to restore everything (including “postgres” role) from pg_dumpall backup

I have a postgres 9.1 server running and I do backups with pg_dumpall -c -f /nfs/backup/fulldump.sql I'm testing restoring the backup to another server with psql -f /nfs/backup/fulldump.sql ...
1
vote
0answers
153 views

Multischema view for postgres , querying multiple databases

Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
1
vote
0answers
177 views

How to measure bandwidth requirements for MySQL, Postgres, …?

TLTR: how to configure MySQL/Postgres to get a fair measurement of the I/O requirments for analytical queries? Hello, for certain database and especially database hardware experiments for want to ...
1
vote
1answer
45 views

Understanding pg-pool in details

I have following problem regarding the pg-pool (3.3.3) When master fails, automatic failover happens and standby is promoted to master. But this new master don't have any replicators. Other slave ...
1
vote
0answers
78 views

Importing PostgreSQL backup into slightly different database

I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to ...
1
vote
1answer
174 views

Paritioning performance for full table scan on non-paritioned index

We have a table in Postgres that we are considering partitioning. We occasionally query an indexed field that is separate from one with the partition key. My question is: Can partitioning the table ...
1
vote
0answers
69 views

upgrading from Postgresql 9.1 to 9.3 on Ubuntu using Londiste

I am undertaking a project to upgrade our Postgresql production database from 9.1 to 9.3 using Londiste. The reason I choose Londiste is to avoid substantial downtime from restoring the dump file or ...
1
vote
0answers
48 views

Selecting aggregated string from database

I have a table in my database consisting of one column which is NewLanguage(varchar). I insert new languages to the table and I want to select the rows of the table and assign them to a string ...