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 ...
0
votes
0answers
12 views
Getting rid of partitioning in Postgresql 9
it turns out that our approach with list partitioning was not the best idea. We have way to much small partitions and a lot of time is wasted on partition choosing by query optimizer.
What's the ...
0
votes
1answer
33 views
Use if exists in postgresql
I know about the exists feature, but that does not help me with the given situation. What I want is:
Check if some row exists, and if exists then check for an another row if row exists.
Here's ...
-1
votes
0answers
8 views
pg upgrade saving database definition taking time
I am in process of a pg_upgrade from 8.4 to 9.3. I am using this technique:
http://momjian.us/main/writings/pgsql/pg_upgrade.pdf
The upgrade has been running since 250 hours, and it has been on the ...
2
votes
1answer
17 views
Postgresql: How do full_page_writes help prevent data loss?
From the postgresql docs
[full page writes are] needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that ...
0
votes
0answers
8 views
PGBouncer configuration
Let's assume that, for a Django website, I have a PostgreSQL server with only one database in it. max_connections is set to 200.
Apart from serving the website, I run each day a local process that ...
0
votes
0answers
17 views
PostgreSQL SELECT and JOIN question
Trying to fix the previous post text formatting issue there was and I did not recognize:
I'm using PostgreSQL under ubuntu 14.04. I have two tables as below:
create table material_base_name (
...
0
votes
0answers
21 views
How to deal with finding nearby locations when given a zip code or city?
I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and ...
0
votes
0answers
23 views
SQL JOIN excluding duplicate
I'm using PostgreSQL under ubuntu 14.04. I have two tables as below:
create table material_base_name (
id bigserial,
base_name varchar(50),
constraint pk_materials_base_name primary key (id)
);
...
1
vote
1answer
38 views
joins with “could be blank” conditions
Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off).
I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
2
votes
1answer
59 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
20 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
12 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
26 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
48 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
22 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
27 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
23 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
38 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
37 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
15 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
27 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
43 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
23 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
212 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
18 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 ...
0
votes
3answers
51 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
91 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 ...
2
votes
0answers
34 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
35 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
29 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
31 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
13 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
20 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
13 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
45 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
21 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
16 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
7 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 ...
-1
votes
0answers
13 views
Error regarding SHMMNI parameter in your kernel durign the install of Postgres 9.3
During the install of Postgres 9.3 I get...
FATAL: could not create shared memory segment: No space left on device
DETAIL: Failed system call was shmget(key=1, size=36, 03600).
HINT: This error does ...
0
votes
0answers
14 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
18 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
25 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 ...
0
votes
1answer
13 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
0answers
11 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
16 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
24 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
13 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
30 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
65 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
20 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 ...
