Reputation
19,629
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
1 15 43
Newest
 Announcer
Impact
~463k people reached

15h
comment Select a count with some conditions
Look up the HAVING clause.
16h
comment Automatic failover mechanism for PostgreSQL
@DanielVérité I strongly agree. Automated, manually triggered failover is a good idea, if regularly tested. Automated failover is only IMO a good plan if you force it to happen regularly (e.g. ChaosMonkey) and the failover is quick and low impact.
1d
comment Automatic failover mechanism for PostgreSQL
No, that's absolutely not correct. It'd be pretty useless if that were the case.
1d
comment Automatic failover mechanism for PostgreSQL
Use repmgr to manage a streaming replication master/slave setup, and pgbouncer or haproxy for switching connections around. Use at least two replicas.
1d
comment Does PostgreSQL support online schema modification (DDL)?
The lock strength reductions in 9.5 will help a bit, at least
1d
comment Does PostgreSQL support online schema modification (DDL)?
Adding a new nullable column is. You have to be careful when adding a not-null col with a default. Add it as nullable with no default, ALTER to add the default, UPDATE to assign the default to existing rows, and finally ALTER TABLE ... SET NOT NULL the col. Even then the SET NOT NULL takes an ACCESS EXCLUSIVE lock that even blocks SELECTs, totally unnecessarily, until 9.5, and it might take a while on a big table. I'm impressed by what MySQL's done here.
1d
answered Does PostgreSQL support online schema modification (DDL)?
1d
comment How can I combine two event tables into a single timeline, in PostgreSQL?
Interesting problem isn't it. I think an efficient solution probably requires creation of a coalesce-like window function.
1d
comment How can I combine two event tables into a single timeline, in PostgreSQL?
To save anyone else the hassle, sqlfiddle.com/#!15/511414
1d
comment Locking issues under transaction on Postgres
@s.m. Yes, rollback to savepoint does too
1d
awarded  Announcer
2d
answered Locking issues under transaction on Postgres
2d
comment PostgreSQL becomes very slow after heavy utilization
Sounds like lots of churn; you've probably generated plenty of heap (and maybe index) bloat, especially if it's all done in a giant transaction or a few big transactions and if autovaccum is off (or even at defaults, not turned up to run more). Are you using large objects (pg_largeobject), bytea, something else?
Jul
1
comment When does pg_dump not export CREATE EXTENSION?
Please show the exact pg_dump and pg_restore (or psql) commands used.
Jul
1
comment Is there a way to determine if a postgres function depends on a user-defined type?
@AlexPopov It's really not worth the effort, as any given type is probably depended on by more than one function, and there's no CREATE OR REPLACE TYPE.
Jul
1
comment Is there a way to determine if a postgres function depends on a user-defined type?
@dezso huh, that's surprising. Thanks for pointing it out. I'm surprised.
Jul
1
comment Is there a way to determine if a postgres function depends on a user-defined type?
What's the underlying problem you're trying to solve? Why do you need to do this? Is there some reason you're not just loading all your types then all your functions?
Jul
1
comment Is there a way to determine if a postgres function depends on a user-defined type?
I don't get it. If the type is used in the function body in PL/PgSQL it's validated unless you force it not to be by setting check_function_bodies off. You can't prove user-defined types aren't referenced by dynamic SQL with EXECUTE; attempts to do so would fail on the halting problem.
Jul
1
comment Locking issues under transaction on Postgres
@YvesMartin Only available disk space, and since PostgreSQL can write and checkpoint incomplete transactions to the heap, you can do bigger transactions than you have available space in pg_xlog.
Jul
1
comment Locking issues under transaction on Postgres
@YvesMartin I think you've confused PostgreSQL for Oracle. PostgreSQL does not have any undo and redo logs. It uses a single write-ahead log followed by storing new rows in the heap and cleaning up old rows later. Yes, there are issues with xlog size, checkpointing, and bloat, but they're not all that similar to Oracle's issues and do not involve any undo/redo logs. Also, batches are often better with many thousands of rows. It shouldn't be necessary to batch a 20k row operation.