All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
1answer
15 views
IOPS needs of Root Volume vs Database/Log Files Volume
I'm setting up a database server via AWS EC2, using:
Root EBS Volume, General-purpose SSD
EBS Volume, Provisioned IOPS
And intending to configure PostgreSQL to have the database and log files on ...
1
vote
1answer
18 views
Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)
I am new to postgres,
I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing ...
1
vote
0answers
35 views
Can an index become inconsistent for “no reason”?
Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...).
I ask this because, there was an ...
3
votes
0answers
23 views
How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?
I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function).
But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy ...
5
votes
1answer
46 views
Huge mismatch between reported index size and number of buffers in execution plan
The issue
We have a query like
SELECT COUNT(1)
FROM article
JOIN reservation ON a_id = r_article_id
WHERE r_last_modified < now() - '8 weeks'::interval
AND r_group_id = 1
AND ...
0
votes
2answers
28 views
Is there a way to show the creation statement for an index in PostgreSQL
I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new ...
0
votes
2answers
39 views
select query not returning expected result
I have a table which is having 30 columns. And I have executed the below query (using postgres 9.1, executed via pgAdmin)
select column2 || '|' || column3 || '|' || column4
from tablename
order by ...
-3
votes
0answers
38 views
How do I change the schema of PostgreSQL to work into R? [on hold]
R read only the tables of public schema, I'm trying to change the schema to insert tables into R.
1
vote
1answer
20 views
Postgresql equivalent to sqlite pragma
What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql.
pragma synchronous = OFF;
pragma journal_mode = OFF;
pragma count_changes = OFF;
...
1
vote
2answers
105 views
300 inserts per minute, 6 million rows per month
I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot ...
5
votes
2answers
461 views
How to make this query use my multicolumn index?
Currently, I have a view which is defined like this:
View "public.customer_list"
Column | Type | Modifiers | Storage | Description
...
0
votes
0answers
17 views
Investigating tool for Oracle database to PostgreSQL database conversion [on hold]
I'm investigating tool for Oracle database to PostgreSQL database conversion.
It should provide below conversion.
Database full schema
All function and procedures
Kindly suggest are there any ...
0
votes
0answers
10 views
pgpoolAdmin for pgpool-II 3.4 : superuser: unknown (Connection error)
I cannot start/stop/reload my nodes through pgpoolAdmin and it says "superuser: unknown" even though I'm logged with the postgres account. What could be the cause? pgpool.conf is identical to another ...
1
vote
1answer
33 views
ORDER BY text doesn't use my index?!
currently this query doesn't use a index even after I created one:
EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
...
2
votes
2answers
24 views
Can I find all JSON scalars in PostgreSQL 9.3?
We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see ...
2
votes
1answer
48 views
PostgreSQL + Docker: incorrect resource manager data checksum in record at 46F/6A7B6D28
I'm running PostgreSQL 9.3.9, Docker 1.8.2 and Ubuntu 14.04. I have an issue where my hot standby keeps failing with the following error message:
incorrect resource manager data checksum in record at ...
-1
votes
1answer
23 views
Cannot connect Eclipse to PostgreSQL [on hold]
I see this error message when I'm trying to connect to PostgreSQL from Eclipse.
org.postgresql.util.PSQLException: FATAL: password authentication
failed for user "postgres" at
...
3
votes
1answer
33 views
Why is my tsv index not being used?
I'm trying to get the postgres full-text-search facility functional.
I have two tables, one I created just for testing, and the actual one I want to be able to search:
Test Table:
webarchive=# \d ...
0
votes
1answer
39 views
Anybody know story behind pgFoundry (PostgreSQL project site) downtime? [on hold]
pgFoundry site's been unreachable 2 days and counting....
I have googled up some ominous messages from the Spring about it eventually closing, but I had never seen any notice on the site to say that ...
0
votes
1answer
26 views
Interpreting Decision Time (DT) in temporal databases
Could anyone give me an insight for the need to model decision time in temporal databases and specifically how and why decision time differs from valid time and transaction time? What would be the ...
1
vote
1answer
38 views
Why does dynamic SQL behave differently in SELECT FROM than in SELECT in Greenplum? [on hold]
We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and ...
1
vote
0answers
19 views
Why can I not alias a column in a view? [duplicate]
I am trying to create a view that provides all quarter starts from a date dimension. Here it is:
CREATE OR REPLACE VIEW quarter_starts AS
SELECT min(day_number_in_epoch) AS day_num FROM ...
-3
votes
0answers
24 views
Find all the stored procedures that are using a given table [closed]
How to get the list of all the stored procedures which internally uses a given table.
0
votes
0answers
24 views
How to include all sequences in pg_dump/pg_restore?
(Also posted to http://stackoverflow.com/q/32832486/398670)
I have a PostgreSQL 9.4.4 database on my development server that contains test data that I want to dump and then restore to my production ...
0
votes
0answers
14 views
Postgres stream WAL segment removed
We have a master server and replication server running Postgres 9.3.6
Streaming was going slow due to a secondary failover router. It was running behind but still working.
I got a communication ...
0
votes
1answer
15 views
PostgreSQL SQL: updating summaries
I'm trying to optimize a query like:
UPDATE master SET count =
(SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
Where the master table is large, so running this results in a huge ...
1
vote
1answer
49 views
UPDATE id table on the columns with the same name
I may be a newbie question but I don't know how to resolve this. I have a table like:
name | id | value
A 1286487 1286333
B 1286489 1286403
C 1286495 1286455
C ...
1
vote
1answer
53 views
Count how many child a parent has in a recursive cte
This is the query I'm using to find comments by a parent id
WITH RECURSIVE cte (id, content, path, parent_id, depth) AS (
SELECT id,
content,
array[id] AS path,
...
0
votes
0answers
16 views
Create roles in multiple postgresql cluster [closed]
I am quite new to PostgreSQL database. We have started to use it just a week back as a part of our project requirements. It took us a while to understand the architecture of the database.
Having read ...
1
vote
0answers
30 views
How can we plan for a High Availability/Disaster Recovery setup in Postgresql 9.3? [closed]
I am working for mission critical project.
We are planning for High Availability. At any point of time irrespective of the failure (network, hw, or db) one db server should be available for the ...
2
votes
0answers
49 views
Old records disappearing after upgrade from 9.4.1 to 9.4.4
I recently upgraded from 9.4.1 to 9.4.4. Since then millions of rows have disappeared from several tables containing historical data.
The severity of data loss seems to be directly proportional to ...
6
votes
0answers
53 views
Surprising results for data types with type modifier
While discussing a recursive CTE solution for this question:
Get the last 5 distinct values for each ID
@ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
2
votes
1answer
27 views
Dealing with corruption: file-level backup vs. pg_basebackup
The wiki on corruption recommends immediately taking a file-level backup of your database by stopping the postmaster and using tar, but I was under the impression that using pg_basebackup was a better ...
2
votes
1answer
35 views
Excluding rows by lots of ids
I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
1
vote
1answer
31 views
Left join not working with sub-query
I've got table of scores where each entry corresponds to a particular (student, subject) pair.
CREATE TABLE score (
id serial PRIMARY KEY,
value integer NOT NULL,
subject_id integer NOT NULL,
...
2
votes
1answer
29 views
How to log all SQL queries for a single connection or user or client host?
Is there a way to log all SQL queries in Postgres 9.3.9 for a single connection or user or client host?
In other words, I want log_statement = all but only for a certain username or client hostname ...
4
votes
2answers
103 views
Get the last 5 distinct values for each ID
I'm working with PostgreSQL 9.4.
I have a table that contains the following entries:
id | postcode | date_created
---+----------+-----------------
14 | al2 2qp | 2015-09-23 14:46:57
14 | al2 ...
3
votes
1answer
18 views
Replacing a large object in PostgreSQL
What should one do when one wishes to replace a large object with a new large object. An example would be to update an uploaded file with a new version.
One way would presumably be to create a new ...
3
votes
2answers
68 views
Use composite type to create new table
In ORDBMS databases I have seen that complex types can be used:
create type name as( ...)
And I can references those types when creating a new table, like:
create table example (row_name ...
3
votes
1answer
69 views
Prevent explicit insert into serial columns
I want to prevent explicit inserts into serial columns. I have come up to the following trigger:
drop table test_table;
create table test_table(
id bigserial primary key,
foobar text
);
create or ...
4
votes
2answers
75 views
Use column values in ORDER BY and LIMIT clauses
I have this table in a Postgres 9.3 database:
I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name.
The final result ...
5
votes
1answer
70 views
PostgreSQL could not fork autovacuum worker process: Cannot allocate memory
Running into some issues every few days with postgres crashing and entering recovery mode. The logs from postgres look like so
... Lots of this for 5-10minutes
2015-09-24 10:07:27 GMT LOG: could ...
2
votes
1answer
21 views
Query result in a daily date from a date range selection Postgres 9.3
I need to make a report that will output a result in a daily row result based on the datarange selected.
Example:
Table: sales
->id
->type
->gross
->created_at
->updated_at
For ...
-2
votes
1answer
33 views
How can postgresql copy tables to another name
Now is Setempber
I have 3 tables , t1,t2,t3
When it comes to October,
I want to create another 3 tables named t1_09,t2_09,t3_09 which stored data from t1,t2,t3
(copy t1,t2,t3 to t1_09,t2_09,t3_09)
...
0
votes
1answer
22 views
how do you revoke create table from a user on postgresql 9.4?
I'm trying to create a postgresql user that can select but can't create or drop, insert and so on on a database but I cannot for the life of me figure out how to prevent a user from creating its own ...
1
vote
3answers
63 views
Get count estimates from pg_class.reltuples for given conditions
Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'?
Currently on my bigger tables the SELECT count(*) query takes long and I ...
5
votes
1answer
62 views
JSONB with indexing vs. hstore
I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage.
As a first step, understanding that JOINS are expensive, ...
0
votes
1answer
34 views
RETURNING value from SELECT table in INSERT .. SELECT
I have a table that looks like this:
| column | def |
| ------ | ---- |
| id | uuid |
| status | int |
| data | text |
I'd like to write a query to duplicate a row (changing only a few ...
1
vote
1answer
29 views
Postgres : Using WITH … AS insert into to return non-inserted column
I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key.
What I'm doing is more complicated than the following but this simple ...
13
votes
2answers
2k views
Are WHERE clauses applied in the order they are written?
I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query.
select 1
from workdays day
where ...
