Tagged Questions
PostgreSQL 9.4 : 2014 release of PostgreSQL
1
vote
1answer
19 views
Does index occupy disk memory for rows for which index expression is NULL?
Let's assume that I have following pg_trgm index on mydata JSONB column:
CREATE TABLE mytable (mydata JSONB);
CREATE INDEX trgm_index_1 ON mytable
USING gin ((mydata#>>'{myfield}') ...
2
votes
1answer
47 views
Can this UPDATE be faster with indexes?
update line_items set product_id = dups.latest_product_id
from line_items as li
join products p on li.product_id = p.id
join vendors v on p.vendor_id = v.id
join vendorgroups vg on ...
1
vote
0answers
20 views
debugging PostgreSQL serialization failures
I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format:
...
3
votes
2answers
47 views
Performance hickups after Postgres 9.4 upgrade
After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump. So the database was converted to SQL and then imported into ...
1
vote
1answer
16 views
Unable to create read only user— postgresql 9.4
I am using PostgreSQL 9.4, I want to make a user with permanent read only access on public schema.
I have tried many different ways to get this but using
REVOKE ALL on schema public to user1;
GRANT ...
0
votes
0answers
15 views
installing pgagent on centos
I have installed pgagent on centos. Now I am trying to connect it to postgres database. I ran:
/usr/bin/pgagent_94 -l 2 host = 127.0.0.1 dbname = postgres user = postgres port = 5432 password=mypass
...
-1
votes
0answers
8 views
udf to encode strings works in psql but not in python [migrated]
I wrote a user defined function in Postgres 9.4 to encode strings:
CREATE OR REPLACE FUNCTION platform.encode_sig(sig text)
RETURNS bigint AS $BODY$
declare sig_id bigint;
begin
lock ...
0
votes
1answer
27 views
set session - custom variable to store user id
I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this:
set session "myapp.user" = '12345';
...
SELECT ...
0
votes
1answer
66 views
Postgresql 9.4.1 stuck all queries when making multi updates
Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory.
After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run ...
1
vote
1answer
22 views
Upgraded Postgres; how to access now-missing databases?
My local connection on my Mac (10.9.5) wasn't working to my Postgres (9.3) database (and therefore I couldn't actually export the data), so I tried upgrading it to 9.4. Luckily I can now connect to ...
-1
votes
1answer
21 views
PsQl data dump import [closed]
Psql databasename < file.db is the recommended method to import to a database.
This doesn't work why?
0
votes
0answers
30 views
Online Recovery for pgPool-II “cannot find xlog functions”
I am trying to set up Online Recovery for pgPool-II [Replication,Load Balancing, Connection Pooling] modes enabled.
I followed the sample provided on the pgPool-II website for the PITR recovery ...
1
vote
1answer
76 views
postgresql bdr 0.8.x - adding another downstream server only partially works
I have the following postgresql 9.4 bdr setup:
upstream server with db called "bdrdemo" running on 10.1.1.1
downstream server(1) with db called bdrdemo running on 10.2.2.2 (replicates with ...
0
votes
1answer
29 views
How can I drop and create postgresql views in dependency order?
I found the following http://stackoverflow.com/a/9712051/61249 which is good but still too much manual labor for me. I need to recreate the views in the right order how would I go about doing that?
...
0
votes
1answer
43 views
+50
PgAdmin III - Number of lines returned during insertion
Not crucial question here, just curiosity.
I am inserting data via multiple scripts in a base running in PostgreSQL.
I wanted to test the time requested to end the insert, so I requested many times ...
2
votes
1answer
33 views
Does Postgres preserve insertion order of records?
For example when I'm using query which returns record ids
INSERT INTO projects(name)
VALUES (name1), (name2), (name3) returning id;
Which produce output:
1
2
3
Will this ids point to ...
0
votes
2answers
46 views
Postgres: difference between CTE and temporary table
In Postgres is there a difference between a CTE and a temporary table other than the fact that the CTE exists just for the context of one statement?
Documentation says that
Common Table ...
0
votes
1answer
24 views
Clarification on UNION ALL of JSONB_EACH result
This function used to merge 2 JSONB and delete certain key when the value is null, refer to this question.
CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
...
0
votes
1answer
41 views
PostgreSQL update and delete property from JSONB column
From this article, I tried to update or delete property of a JSONB column:
CREATE TABLE xxx (id BIGSERIAL, data JSONB);
INSERT INTO xxx(data) VALUES( '{"a":1,"b":2}' );
SELECT * FROM data;
id | ...
0
votes
0answers
15 views
Getting SSL Working
So, I'm trying to configure PostgreSQL to allow SSL connections, on a Windows box.
First, per http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html I added this line to pg_hba.conf (and ...
1
vote
0answers
21 views
Is C collation suitable for my Postgres database?
I am working with 500m rows (120GB) in Postgres 9.4, and I am trying to make SELECT statements as fast as possible. I am less concerned about the speed of INSERT and UPDATE, because my data only ...
0
votes
2answers
25 views
Get ID which has two rows with different values for one column [closed]
Basically, I have a dataset that is a list of internal location identifiers attached to an ID (so each 'set' of geographical points has its own ID). So an 'id' column and a 'location' column exist in ...
0
votes
0answers
45 views
Thousands of the same weird syntax error after 9.4 upgrade
We moved our production database from 9.1 to 9.4.1 and started seeing the following errors all throughout the logs:
ERROR: syntax error at or near "''" at character 1
STATEMENT: ''
LOG: statement: ...
0
votes
1answer
41 views
Working with Materialized View
I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4.
When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH ...
2
votes
1answer
96 views
Can bdr only replicate one database per server?
I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm.
My Problem: I want to add more databases to the replication but every time if I added the other database and ...
1
vote
2answers
147 views
Postgresql 9.4 high availability
I currently have a master postgresql 9.4 server containing many databases on Ubuntu 14.04.
I tried to use Barman to setup backup, but would like to set up streaming replica so that when the master ...
0
votes
0answers
27 views
Insert into a specific partition, create if not exists
So I have my master table:
p_reports: id(pk), web_id, other(data)
At the moment I create my partitions which inherits from p_reports by using:
CREATE TABLE IF NOT EXISTS ...
1
vote
2answers
69 views
Postgres Index a query with MAX and groupBy
Is there any way to index the following query?
SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id;
I've tried creating sorted (non-composite) indexes on frame and ...
0
votes
1answer
41 views
Delete index while query is running, is it possible?
I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still ...
1
vote
1answer
51 views
Rely on .pgpass in CREATE USER MAPPING
I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do ...
4
votes
1answer
128 views
PostgreSQL operator uses index but underlying function does not
I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
3
votes
1answer
153 views
PostgreSql JSONB SELECT against multiple values
I have a very simple JSON table which I populate with some sample data:
CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO ...
1
vote
0answers
23 views
Error when creating Trigger (PostgreSQL 9.4)
I created a table 'Company', and I want to create a trigger so a company can only present in one city, but it can be present in several different cities in the same country. For example, there's only ...
1
vote
1answer
57 views
Use result of aggregate in same select?
Is it possible to feed the result of an aggregate select into the same select with Postgresql?
I'm aware of the WITH clause -- but, this would be true syntactical sugar, wouldn't it?
SELECT ...
2
votes
1answer
68 views
Retrieving latest record using DISTINCT ON is slow [duplicate]
Using Postgres 9.4.
I have a table cartests with 5.5M rows. Each row is a car test:
\d log.cartests;
Table "log.cartests"
Column | Type | Modifiers
...
4
votes
1answer
178 views
Is there a way to insert multiple rows into a table with default values for all columns?
I can insert multiple rows into a table with default values for all columns the RBAR way:
create table course(course_id serial primary key);
do $$
begin
for i in 1..100000 loop
insert into ...
0
votes
0answers
619 views
Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade
I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this:
sudo pg_dropcluster 9.4 main ...
1
vote
0answers
47 views
PostgreSQL timezone setting
While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku ...
0
votes
0answers
23 views
Is PostgreSQL JSONB @> operator equal to ->''=?
Is the jsonb_column @> '{"key":value}'::jsonb operator equal to jsonb_column->'key' = value? in terms of result, performance, and indexes that will be used?
0
votes
0answers
66 views
Does someone have a cannonical example of setting up PostgreSQL with PLV8?
I'm hoping that someone can either post or point me to a guide for setting up PostgreSQL (9.4) and PLV8.
I'm not an expert at PostgreSQL by any means and have been reading about and wanting to play ...
2
votes
2answers
510 views
Refresh materalized view incrementally in PostgreSQL
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph (
...
0
votes
0answers
111 views
Are A or D series VMs better for deploying PostgreSQL on Azure?
In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series). Considering this space is wiped on reboot...
Is there a benefit to choosing the SSD option for ...
2
votes
2answers
417 views
PostgreSQL joining using JSONB
I have this SQL:
CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}');
INSERT INTO test(data) ...
3
votes
1answer
201 views
PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key
If I have a table containing:
CREATE TABLE test(
id SERIAL PRIMARY KEY,
name VARCHAR(200),
age INT,
data JSONB
);
and data column populated with {"name": xxx, "age": yyy}, sometimes ...
2
votes
1answer
121 views
SELECT query with DISTINCT on a table-structure for graphs is very slow
I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph.
CREATE TABLE edges (
node_from bigint NOT NULL,
...
