15,855 reputation
11338
bio website blog.ringerc.id.au
location Western Australia, Australia
age
visits member for 2 years, 8 months
seen 17 hours ago

I'm a PostgreSQL admin/developer, sysadmin, multi-language programmer (lately mostly Java and Java EE, previously C, C++, Python and good old bash) and general IT JOAT.

I work for 2nd Quadrant, a global PostgreSQL consultancy.

I maintain a technical blog at blog.ringerc.id.au. My PostgreSQL writing now appears on the 2nd quadrant blog.

I'll work in most programming languages with varying degrees of competence, though I'll only reluctantly touch PHP or Perl code. I'm more interested in the libraries, tools, and the code that's built on top of the language.

Most of my scrap code lives on https://github.com/ringerc . Most of my open source code is in the form of scattered patches across too many different bugzillas and JIRAs, rather than single new projects. Over time I've done a lot of work on Scribus and PoDoFo, but have moved away from both projects as my focus and interests changed. I contribute patches - rather infrequently - to PostgreSQL and to various Java EE 6 infrastructure components. Most of my contribution is now non-code, in the form of:

  • Detailed bug reporting with test cases, which I spend an unpleasant proportion of my development time preparing and submitting, mostly against JBoss and java.net projects.
  • User support for PostgreSQL on mailing lists and here on SE
  • PostgreSQL patch review and beta testing
  • Usability review of Java EE libs/tools, as well as usability review of PostgreSQL and its tools
  • Writing, editing and enhancing documentation, HOWTOs, etc
  • Blogging in detail on topics likely to be useful to others.

I've turned into a git fiend and no longer understand why anybody uses anything else. I'm also becoming increasingly obsessive about testing, particularly with the advent of Jenkins CI and Arquillian.


1d
comment Are A or D series VMs better for deploying PostgreSQL on Azure?
Well, if you're doing lots of big sorts etc that spill out of work_mem and to disk then it might help - but the on-disk sort algorithm used by PostgreSQL is optimised for disks with slow seeking anyway. So. Maybe if you're doing lots with temporary tables, and/or doing big sorts and joins over huge data sets that must be materialized. You'd create a tablespace and set temp_tablespaces to point to the storage.
1d
comment Are A or D series VMs better for deploying PostgreSQL on Azure?
Depends a lot on workload, and on the magnetic drives vs the SSDs. Not all drives are equal, not all workloads are equal. If you're running PostgreSQL on local storage that's not durable and crash safe you'll want to turn off PostgreSQL's data durability/crash safety features and rely on replication + really good backups. Use streaming replication + WAL shipping PITR (PgBarman or similar) + periodic logical dumps. Yes, "and".
Dec
16
comment Unique index on columns which are allowed to be NULL
How does it "not work"? Does it permit entries you do not want permitted? Does it prohibit entries you want permitted? Please be specific. Also, remember that NULL = NULL is NULL.
Dec
14
comment What are the side-effects of disabling nested loops in postgres?
If the planner is choosing a slower plan than the best possible, then either the cost parameters aren't correct for your system, the statistics don't reflect the actual data, or there's a planner bug/limitation preventing choice of the best plan. If you've conclusively eliminated the first two, then you could choose to force a join type. I don't do that myself, as I find that it becomes a problem later when the data has changed/grown because it prevents the planner from adapting to changes in the data.
Dec
13
answered What are the side-effects of disabling nested loops in postgres?
Dec
11
answered Slow Postgres updates if OTHER columns are indexed?
Dec
11
answered Adding hstore entries to an uninitialized column
Dec
11
comment PostGIS 1.5 Error with PostgreSQL 8.4
X-post to gis.stackexchange.com/q/125218/12379
Dec
10
comment Why is the CTE much worse than inline subqueries
I wrote about this recently; see blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences . Though there are currently some DNS issues that may limit reachability of that site. Try a subquery in FROM instead of the CTE term for best results.
Dec
10
revised Alter table statement waiting (for hours, on dev machine), but no locks shown
added 321 characters in body
Dec
10
comment Alter table statement waiting (for hours, on dev machine), but no locks shown
@JohnBarça It must've failed to commit its transaction and disconnect for some reason. Weird indeed.
Dec
9
answered Alter table statement waiting (for hours, on dev machine), but no locks shown
Dec
9
comment Is it safe to use set-returning sql functions instead of security-barrier views?
@JackDouglas It's not inherently guaranteed that STRICT blocks inlining, but there's also no sign of that changing any time soon. Arguably we should permit the security_barrier attribute to apply to SQL functions, disabling inlining. There's a really ugly hack you can use that's guaranteed to prevent predicate pushdown, btw: the OFFSET 0 hack. Otherwise known as "PostgreSQL doesn't have hints, it has awful misfeatures that've been grandfathered into not-hints".
Dec
9
comment Is it safe to use set-returning sql functions instead of security-barrier views?
@JackDouglas Yeah; see postgresql.org/message-id/4EB9BB3F.6090504@agliodbs.com for example. I think it might be mentioned in postgresql.org/docs/current/static/xfunc-sql.html but haven't re-read in detail to find the reference, just vaguely recall.
Dec
9
comment Is it safe to use set-returning sql functions instead of security-barrier views?
@JackDouglas Volatility, probably. There's also never any guarantee a function will get inlined, but just because it isn't this time doesn't mean it won't be later, possibly without being caused by specific and obvious changes to the DB or code.
Dec
9
comment Postgresql service run failure
Well, that's different. Are you certain both ends are x64?
Dec
9
comment Error during pg_restore. “text search dictionary ”unaccent“ does not exist”
I'd say you haven't installed the contrib modules. They're often packaged separately.
Dec
9
answered Is it safe to use set-returning sql functions instead of security-barrier views?
Dec
8
revised Getting PAM-compatible password hashes from pgcrypto
edited title
Dec
8
comment Slow max, min query in Postgresql View
@Suanmeiguo Please edit the question to show the full explain analyze result, or use explain.depesz.com