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.
0
votes
1answer
19 views
Possible to make database owner read only
The database owner can revoke certain privileges from themselves such as INSERT but is it possible to revoke further privileges such as DROP for read-only operation?
1
vote
1answer
22 views
Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails?
On the PostgreSQL Concurrency With MVCC page, it says:
know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation ...
0
votes
0answers
17 views
How calculate per tenant disk usage in a multitenant database with tenant_id columns?
I would like to provide forum hosting as a service, for forum software I'm developing. The data is stored in a PostgreSQL database, in tables with a forum_id column. That is, all forums share the same ...
1
vote
2answers
25 views
Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?
We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to ...
2
votes
0answers
34 views
Optimizing a single table, write-once, read-many PostgreSQL database
I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single ...
0
votes
0answers
15 views
Postgres user without password
I'm creating unit tests on a python application that uses digital certs on the server. Since I am not testing the connection between the server and the database, I need to setup the temporary test ...
0
votes
1answer
22 views
Query performance with primary keys of different data types
Is there a significant difference in performance if i decide to use INT or BIGINT as data type of a primary key in a table? Of course if i use BIGINT the value will have twice it's size. But as the ...
0
votes
1answer
19 views
How to know when Postgresql password is changed
My question is how to know the date when a postgresql user password is changed.
Is it possible?
I know postgresql has account expiry feature,
but I'm asked to check if users have changed their ...
0
votes
1answer
17 views
Optimizing a Postgres query with a large IN
This query gets a list of Posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization ...
1
vote
0answers
44 views
Database on Xeon E3 1270-v3 Quad Core performs much better than E5 2630v2 Hex-Core [on hold]
Configuration:
RAM: 16 GB DDR3.
HDD: Seagate 500 GB 7200 3Gb/s 3.5 inch SATA - idential disks.
Postgres 9.4
Windows 2008 R2 Web
work_mem = 32MB
max_connections = 600
shared_buffers = 1024MB
...
0
votes
0answers
21 views
Append elements to array only if the element does't exist
WITH upd AS (
UPDATE univ.products
SET source = source::int[] || _source
WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source))
RETURNING id
)
, ins AS (
...
0
votes
1answer
16 views
Postgresql - Stuck building a plpgsql function
I am a beginner in plpgsql and i am stuck coding a function.
I need a function that does the following:
Giving a table, a id_field in that table and another field in that table it does:
ORIGINAL ...
1
vote
1answer
40 views
PostgreSQL Replication on a daily basis
With PostgreSQL, I want to replicate a subset of all tables from Database A to B, but only once a day. Since I do not know what to search for: is there a technical term for such a scenario? Does e.g. ...
0
votes
0answers
25 views
Executing a .sql file via a command prompt Ubuntu PostgreSQL [on hold]
For the past hours, I have been trying to make it work but after trying many options I cannot simply execute this file. While following this tutorial, I got to the point where I had to create a .sql ...
0
votes
0answers
23 views
Explain output for Postgres Query [on hold]
I am trying to understand the Explain output for a query on Postgres. Can anyone explain this output to me?
Merge Join (cost=0.25..668591683.56 rows=16714280340 width=128)
Merge Cond: ...
0
votes
1answer
28 views
What is held in memory during the life of a CTE?
Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement?
Is all data except RETURNING data discarded at the end of each sub-statement?
2
votes
1answer
26 views
Can PostgreSQL use multiple partial indexes per query?
I have read that PostgreSQL can use multiple indexes in general, but in the specific case of a query that ranges across two indexes, will it use both? If so, will they be loaded sequentially or ...
0
votes
0answers
28 views
EXISTS CPU consumption
I have one large wCTE that, before any rows are inserted, has to determine if each new row conforms to the relational structure of the database as well as other constraints not implementable yet ...
0
votes
1answer
15 views
HAVING ANY in Postgres
I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition.
For example if I wanted to select a group where at ...
3
votes
0answers
51 views
Slow transactions, many locks
At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
1
vote
1answer
18 views
invalid memory alloc request size on simple query with array_agg on Postgres 9.3
Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine)
I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is:
select ...
0
votes
1answer
15 views
privilages system table in postgres
Is it possible to know all the tables a user has access to using a single query?
I tried with pg_role and pg_user with no luck. Can anyone tell me about a system table which has the data of all the ...
0
votes
0answers
14 views
Exception handler on multiple row insert PostgreSQL [duplicate]
I'm trying to create a function which inserts rows from one database table to another, this is the part of the function which goes wrong:
BEGIN
INSERT INTO suppliers("id","name", "url", "logo", ...
0
votes
0answers
36 views
Query gives not result
I have a quite simple table with different values. When I search a specific column no results are given. What could be the error?
For example I search for an "id":
db=# select * from table where id ...
0
votes
1answer
20 views
Database Change Management
What are ways in which teams handle database change management? I have a team of 8 developers making database changes concurrently.
Each developer is responsible for updating their changes in what we ...
0
votes
1answer
9 views
How to create a citation/quote from a Postgres text field?
I have a database with a lot of text and I want to find the surrounding text for a word that I am searching for i.e. If I search this text for "text" I should get something like (and more consistent ...
0
votes
1answer
44 views
Why is Postgres on RDS maxing out on CPU every few hours?
Using Amazon RDS, we are running ETL scripts to migrate our data. However, every few hours there is a huge CPU spike.
Here are the ETL specs (per ETL):
50 records inserted / second
pool of 1000 ...
0
votes
1answer
19 views
Querying sums of grouped consecutive rows in PostgreSQL 9
I have data about people traveling in different countries like this:
country | begintimestamp | distance
Germany | 2015-01-01 00:00:00 | 100
Germany | 2015-01-01 01:12:13 | 30
France | ...
0
votes
1answer
26 views
AWS RDS PostgreSQL dump/restore - syntax error
I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following...
1) Dump EC2 DB:
pg_dump --host localhost --port 5432 -Fc ...
1
vote
3answers
32 views
Add fields to view from select (transpose fields)
I've got a table with following structure:
CREATE TABLE test
(
id integer,
field character varying,
value character varying
);
field value
------ -----
field1 value1
field2 ...
1
vote
2answers
36 views
Referential integrity after disable/enable trigger all
I wonder how to check referential integrity on a table after disabling and reenabling foreign key constraints.
For example :
postgres=# CREATE TABLE main(id serial, id_delta integer, PRIMARY KEY ...
0
votes
2answers
71 views
pg_stat_activity - is it possible to decompose a stored procedure further?
I got a long running query in pg_stat_activity:
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | ...
3
votes
0answers
43 views
Recovery when failover
1/ Description:
I have 2 Centos machine: master, slave (streaming replication). Both of them are built on PostgreSQL 9.3 (image below) and configured failover event (failover PG 9.3 document).
Last ...
1
vote
2answers
38 views
Postgres multiple columns to json
I am running postgresql 9.3.4. I have a table with 3 fields:
id name addr
--- ---- ----
1 n1 ad1
2 n2 ad2
...
I need to move the data to a new table with fields like:
id data
...
0
votes
1answer
22 views
Get TEXT value of a CLOB OID in Postgresql
I have a database table that looks like:
create table answers(
id int not null,
question_id int not null,
answer text null
)
This table was originally build by Hibernate using the @Lob ...
-4
votes
0answers
17 views
I can not find what's wrong with this sql. Full-Text Search missing FROM-clause entry [on hold]
SELECT to_tsvector(appservice_servicedb.language::regconfig,unaccent(appservice_servicedb.name)) ||
to_tsvector(appservice_servicedb.language::regconfig,unaccent(appservice_servicedb.what)) ||
...
2
votes
1answer
22 views
When to break large delete queries up?
I've got an auto generated join table (three columns, two of them keys to other tables), which was recently corrupted during an aborted migration. As a result there are around 1 million duplicate rows ...
2
votes
1answer
194 views
Foreign Key NULL vs Foreign Key to an empty string
This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information)
CREATE TABLE medicine (
id serial PRIMARY KEY,
...
1
vote
2answers
40 views
How to update the parent/child of all rows in the tree (ltree)?
Here are my configurations:
DB type: postgresql (9.3 if that's necessary)
Table name: product_sections
Column names: section_id (integer) & section_path (ltree)
Detail:
I have one reference ...
0
votes
1answer
52 views
How to change a web application that now needs to become OLTP and OLAP application?
Previously, I did a simple web application for an enterprise client using PHP and MySQL.
After some time, it has grown to a point where I realized after talking with people here that I need to treat ...
0
votes
0answers
15 views
Turn off serialization failure logging
Is it possible to turn off serialization failure logging? If so, how?
For example, my log is filled with this example:
ERROR: could not serialize access due to read/write dependencies among ...
0
votes
0answers
16 views
index of aggregate value with fast update?
Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire ...
1
vote
2answers
62 views
Is unique index better than unique constraint when an index with an operator class is required
Postgres docs say:
The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
0
votes
0answers
17 views
pg_dump in 1G VPS crashes
I'm trying to run pg_dump on a relatively small database (2G), but pg_dump keeps crashing with an out of memory error.
du -h --max-depth=0 9.4
1.9G 9.4
The command:
pg_dump -Fp -h "$HOSTNAME" ...
0
votes
2answers
33 views
Product Attribute System While Avoiding EAV
I'm working on building an attribute system for products. The issue that I'm running into is that various products can have very different attribute requirements.
Some E-commerce websites such as ...
0
votes
1answer
21 views
Postgres - Inject 0 values when rows do not exist
I have 3 tables that I need for reporting:
*dates*
date_sk | full_date | day_number_of_month
1 | 2013-01-01 | 1
2 | 2013-02-01 | 1
3 | ...
1
vote
1answer
20 views
Count of 3 consecutive unsynced dates
I have a table set out like so:
user_id the_date minutes_asleep
----------------------------
1 2015-01-01 480
1 2015-01-02 0
1 2015-01-03 0
1 2015-01-04 0
...
0
votes
1answer
21 views
PostgreSQL Primary key disappears from test table
I have a somewhat convoluted scenario where a test table I had created with a primary key won't return the primary key. pgAdmin III reports there are no constraints. I have the entire PostgreSQL query ...
0
votes
0answers
26 views
find tables present in a query [closed]
I have a table with 1 column (varchar(max)) that holds a list of distinct queries. Does anyone have a query that could go through that list and select all table names (not just the first one) from the ...
1
vote
0answers
33 views
How to optimize inefficient query in PostGIS
I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow).
This query takes about ...
