7,511 reputation
31842
bio website
location Berlin, Germany
age 39
visits member for 3 years, 2 months
seen 4 hours ago

Feb
6
comment Optimizing a single table, write-once, read-many PostgreSQL database
@a_horse_with_no_name with RDS, it is not that easy to do most of those changes, I'm afraid.
Feb
6
comment Slow transactions, many locks
Are you able to upgrade to 9.4.1 and check if this problem remains?
Feb
6
comment Query performance with primary keys of different data types
I am wondering if you could notice the difference. But it is quite easy to set up a test for checking this. Like CREATE TABLE test_bigint AS SELECT i::bigint FROM generate_series(1, 1000000) t(i); and then, after adding the index, do EXPLAIN ANALYZE.
Feb
6
comment mysqlfailover utility not ablt to connect
Have you checked the thing it suggest, namely name resolution?
Feb
5
comment Database performs differently on the two same Xeon E3 1270-v3 Quad Core machines
Are both DBs freshly restored from the same dump? I/O susbystem identical?
Feb
5
comment Exception handler on multiple row insert PostgreSQL
And how did you find the solution proposed in my answer on the linked question? I see no disadvantage of it, while being (supposedly) much faster than processing row by row in python.
Feb
4
comment Exception handler on multiple row insert PostgreSQL
INSERT INTO suppliers ... SELECT ... FROM dblink(...) t WHERE NOT EXISTS (SELECT 1 FROM suppliers s WHERE s.id = t.id or something like that. See dba.stackexchange.com/questions/30499/…
Feb
4
comment Query gives not result
Could you please post a test case? With table definition, insert statements and so on.
Feb
4
comment Referential integrity after disable/enable trigger all
I think you should add this as an answer. You can even accept it later.
Feb
3
comment Referential integrity after disable/enable trigger all
As the comment I referred to was removed, I have to readd its content: I don't see fixing this any other way than removing the FK and adding it again.
Feb
3
comment Referential integrity after disable/enable trigger all
It only works for constraint previously marked as NOT VALID, see postgresql.org/docs/9.4/static/sql-altertable.html I was wondering about this solution myself, but after trying the bad row is still in the table.
Feb
3
comment pg_stat_activity - is it possible to decompose a stored procedure further?
Let me add that these data appear in that view only on call completion. Furthermore, the data are aggregated, so for checking a single run needs some additional work.
Feb
3
comment pg_stat_activity - is it possible to decompose a stored procedure further?
You can possibly change the function definition to emit some progress information (doing this, you may have to set client_min_messages or log_min_messages). In this case, from the next run on, you will see what it is doing. Changing an already running function is impossible.
Feb
3
comment Referential integrity after disable/enable trigger all
You can check with a simple query (NOT IN, EXISTS or a left join). For validating, I have no better idea than @a_horse_with_no_name's.
Feb
3
comment Recovery when failover
Is this a real setup? What happens when the host of the master goes completely down? In this case, it appears, you cannot get the missing WALs in any way, which means you cannot really fail over to the slave.
Feb
3
comment How to update the parent/child of all rows in the tree (ltree)?
Yes, this is a nice solution with ltree-only functionality. Mine is a bit less advanced but more readable :)
Feb
2
comment How to update the parent/child of all rows in the tree (ltree)?
I cannot, but the docs can ;) postgresql.org/docs/9.4/static/ltree.html
Feb
2
comment How to update the parent/child of all rows in the tree (ltree)?
Does anything prevent you from casting your values to text, do a replace and then cast back to ltree? Like SELECT replace('a.b.c'::ltree::text, 'b', 'd')::ltree;
Feb
2
comment index of aggregate value with fast update?
You cannot create an index like that. However, you can keep a separate table for the aggregate which is then updated on data changes. One can implement such a system using triggers, for example. You can use a materialized view, too, but you have to check the performance hit the refresh takes.
Feb
2
comment Turn off serialization failure logging
You cannot single out different types of ERRORs. You can etiher get rid of all of them or see them all. On the other hand, if this comes from a single user, you can disable the logging for it (but still, either all or none of them).