Gross overconfidence with public data

The Australian Buerau of Statistics is showing all the signs of being grossly overconfident with every aspect of the 2016 Census, bordering on incompetent.

You've heard all about the data retention in broad terms, but what exactly does it mean? And why could it be bad? After all the data is "anonymized" such that personally identifiable data is removed before being shared, right? Their original non-anonymized versions are encrypted and safe in the hands of ABS administration, so there's nothing to worry about.

Well, it's not that simple.

Lets talk about anonymization vs aggregation, how de-anonymization works, and why the "statistical linkage key" is appallingly flawed.

On JPA fetch groups

It looks like JPA 2.1 has been released with support for "fetch groups". Hopefully that'll meet the previously identified need for control over JPA fetch behaviour.

It's come too late for me, as I'm no longer working with Java EE or JPA and am feeling badly burned by my experience with the platform after adopting EE 6. Hopefully this'll help others who're struggling with JPA performance issues.

Generating random bytea values in PostgreSQL

While playing around with answering an interesting question on dba.stackexchange.com I wrote a simple C extension to PostgreSQL that generates random bytea values of a user-specified size. Fast.

In case anyone else is looking for a good way to dummy up random binary data in PostgreSQL, you can find the code in my scrapcode repository on GitHub

See the extension's README for details.

There's a pure SQL version fast enough to use for generating a few 100s of KB of data, too, or a couple of MB if you're patient.

Avoiding PostgreSQL database corruption

TL;DR: Don't ever set fsync=off, don't kill -9 the postmaster then delete postmaster.pid, don't run PostgreSQL on network file systems.

Reports of database corruption on the PostgreSQL mailing list are uncommon, but hardly rare. While a few data corruption issues have been found in PostgreSQL over the years, the vast majority of issues are caused by:

• Administrator action;
• Misconfiguration; or
• Bad hardware

A recent mailing list post asked what can be done to reduce the chance of corruption and keep data safe.

---

If you think you have a corrupt PostgreSQL database, stop the database server and take a complete copy of the data directory now. See the Corruption page on the wiki. Then ask for help on the pgsql-general mailing list, or for critical/urgent issues contact a profesional support provider.

Do not attempt to fix the problem before taking a complete copy of the entire data directory. You might make the problem much worse, turning a recoverable problem into unrecoverable data loss.

---

Here's my advice for avoiding DB corruption issues, with some general PostgreSQL administration advice thrown in for good measure:

For anyone who programs in any language or designs databases and formats

Anybody who does any kind of design or programming needs to read these two articles. So should anybody who designs data formats, database schema, or specifies any kind of standards.

The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

Falsehoods programmers believe about names

If you haven't read - and acted on - these then you're probably producing bugs at a fair rate. I harp on about some of these points fairly regularly, so I thought I'd collect the articles in one place.

Sadly it's often hard to avoid the whole "exactly one name structured in two parts" thing the real world, because you're often dealing with other mis-designed systems that expect them to be different fields. Some of those systems aren't even software; they can be business processes, legal processes, and more.

The same applies to gender/sex, where it really isn't as simple as that [M/F] radio button or pulldown you probably have ... but half the legal processes and 3rd party APIs you work with think it is. Your user is trans-F-to-M or XXY indeterminate? They just have to shove themselves into a box.

Sadly, most people with non-western-Eurpoean names living in western countries are used to butchering their names to fit the split name model. For that matter, people with western European names are used to butchering them to work in systems that like to turn "Renée" into "Renee" or - depressingly frequently - "Renée". Does your last name have a space in it? You're doomed to being "Jacobsen, James van", "Jacobsen, James Van" (thanks to "helpful" auto-capitalisation) or even "Jacobsen, James V." forever.

Would you like to have your name "corrected" - to something wrong - or fail to validate in every second system you use? If not, consider those for whom that's true and fix your software. Ditto for gender/sex - don't ask for it if possible, and if you must, provide a free-form field.

I usually compromise by having a "display name" field that's used throughout the application, and by making no assumptions about names being unique, comparable, or in any way divisible. If interaction with 3rd party systems that want split-form names is required I have an additional field for the user to enter the name they usually use in identity documents, etc, but I don't use it within my app and I make its purpose clear.

PostgreSQL rocks, and so does explain.depesz.com

I was modifying one of my aggregate views today and I was struck by just how impressive PostgreSQL is, and how powerful relational databases in general really are. PostgreSQL was executing queries against the view a completely different way when one entry was being queried vs when the whole view was being requested.

You might take this for granted - but when you think about it, it's seriously amazing how a database can take your description of what you want and work out the how for its self. Mostly.

Imagine writing this yourself. Say you're working in Java. You have a complex Criteria query against several different tables related to information about customers. You now want to get information for just one customer, so you add a WHERE clause to the top level. If the database didn't nearly magically push this filter criterion down into all those complex sub-queries and joins you'd be at it for hours; days or weeks if you wanted to make it re-usable and generic across a set of similar criteria queries.

Instead, the DB just does it for you.

Update on JPA 2.1 and fetch control

UPDATE: JSR388 has been released with support for "fetch groups", which at first glance seem to meet this need.

I re-posted my query to javaee-spec-users after seeing no response on the jpa-spec users list. A reply from Linda DeMichiel on the javaee-spec list looks promising.

Separate discussion (twitter) suggests it's conference season so everyone's busy and sidetracked as well.

The spec proposal does indeed list:

Support for the use of "fetch groups" and/or "fetch plans" to provide further control over data that is fetched, detached, copied, and/or used in merging.

but I'd seen no discussion of it on the EG list, which I've been monitoring since near start. (UPDATE: There'd been a wee bit, I just missed it.) I didn't realise they were working to an ordered, specific agenda, though it certainly makes sense to tackle such a big task in small chunks like that.

The JPA 2.1 spec proposal also has some other good stuff. Points of particular interest to me include:

• Methods for dirty detection
• Additional event listeners and callback methods; availability of entity manager to callbacks

Dirty detection in particular has caused me a lot of pain in the past, so it's good to see it on the list. It'd simplify quite a few tasks around working with JPA, particularly in apps with longer-lived sessions like interactive GUI / swing apps, Vaadin web apps, etc.

One thing that isn't there that perhaps should be is handling failure more cleanly. Currently one needs to clone entity graphs before attempting to persist them using a tool like org.hibernate.util.SerializationHelper, because they may be in an inconsistent state if a persist operation fails. It's slow and ugly to need to serialize/deserialize to clone an entity graph, but hard to get around because dynamic weaving and enrichment means you can't properly clone an entity just by allocating a new one and copying properties into it. Worse, you have to handle all the cascades yourself. Many common use cases involve situations where failure is normal: serialization failures of serializable transactions, optimistic locking issues, etc. Often you want to re-fetch the entities and re-do the work from scratch, but that's painful if you have user-modified state in those entities and all you want to do is retry a merge after a database serialization failure.

Guess I'd better pipe up about that too. It isn't news, but if it isn't on the agenda maybe it should be added for discusson.

Now is the time to join the jpa-spec users list on http://java.net/projects/jpa-spec and pipe up about practical, real-world issues you face with JPA. Even if everyone's out conferencing, email queues up nicely.

Mail to the JPA 2.1 Expert Group re fetch control

I'm setting up another opportunity to look foolish - which I view as a good thing; if I don't risk looking foolish, I don't learn nearly as much.

I've mailed the JPA 2.1 EG re control over fetch mode and strategy on a per-property, per query basis, as I'm concerned this may not otherwise be considered for JPA 2.1 and thus Java EE 7. As previously expressed here I think it's a big pain point in Java EE development.

I'd appreciate your support if you've found JPA 2's control over eager vs lazy fetching limiting and challenging in your projects. Please post on the JPA 2.1 users mailing list.

---

UPDATE:: JSR388 has been released with support for "fetch graphs", a feature that appears to meet the specified needs. I'm no longer working with Java EE or JPA, so I haven't tested it out.

UPDATE: http://blog.ringerc.id.au/2012/06/update-on-jpa-21-and-fetch-control.html

UPDATE: Many of these problems are solved, albeit in non-standard and non-portalbe ways, by EclipseLink. EclipseLink extensions can be used to gain much greater control over fetches at the entity definition level and more importantly via powerful per-query hints. Glassfish uses EclipseLink by default, but you can use EclipseLink instead of Hibernate as your persistence provider in JBoss AS 7.

---

JPA2 is very inflexible with eager/lazy fetching

This must be a common situation with JPA2, but seems poorly catered for. I feel like I must be missing something obvious. It's amazingly hard to override lazy fetching of properties on a per-query basis, forcing eager fetching of normally lazily fetched properties.

There doesn't appear be any standard API to control whether fetching of a given relationship or property is done eagerly or lazily in a particular query. Nor is there a standard hint for setHint(...) - EclipseLink offers some limited control, Hibernate has no hint equivalent to its own Criteria.setFetchMode( ). JPQL/HQL's left join fetch and the (somewhat broken) Criteria equivalent scale very poorly to more than a couple of properties or to nested lazy properties, and don't permit other fetch strategies like subselect or SELECT fetching to be used.

Tell me I'm wrong and there's some facility in JPA for this.

Read on before replying with "just use left join fetch". I wish it were that simple, and not just because a join fetch isn't always an appropriate strategy.

PostgreSQL usability - PgAdmin-III and Pg need some usability love

As anyone who's read much here will know, I'm a huge fan of PostgreSQL, the powerful open source relational database management system. It is an amazingly project with a team that keeps on releasing high quality updates full of very useful new features and improvements.

This is my blog, so there must be a "but", right? You're quite right.

I already wrote a PostgreSQL: Great even when you can see the warts in response to a perhaps overly glowing write-up someone did a while ago. I'm not covering that again here; this post is specifically about a topic more and more dear to my heart, that of usability.

As a long-time UNIX geek I've always found PostgreSQL quite easy to use, because I live on the command line where psql is just wonderful. Recently, though, I had to use PostgreSQL on Windows, and in the process couldn't help seeing it from the new user's point of view. In fact, I tried to think like a new user as I performed the tasks I needed to do.

It wasn't all roses. I knew enough about Pg to get past the issues pretty easily, but some of them would be real roadblocks for new or even intermediate users, and I think they're worth highlighting. Many of the issues were with PgAdmin-III, but far from all of them.

This post started out as a minor critique of a few usability sore points in Pg and PgAdmin-III. As I went through step-by-step producing screenshots and thinking through the process as a user, though, I realised it's an absolute, complete and utter train-wreck. If this had been my first experience with PostgreSQL, I'd be a MySQL, MS-SQL or Oracle user now.

PostgreSQL: Great even when you can see the warts

I’m a very happy PostgreSQL user, and was interested to read a recent post by an MS SQL Server DBA advocating PostgreSQL.

This post is a response, enumerating areas where Pg may not be so hot after all. You might say it's my tendency to always see the negative, or perhaps it's just my desire to inform others of the downsides of something so the loud advocacy doesn't leave them feeling let-down when the reality hits. I'd like to think it's the latter. Telling someone how it is from the start is much better than showing them the rosy-goggles view until they're committed. So, from an enthusiastic PostgreSQL user who spends a lot of time helping out on the mailing lists, here's a list of some of the warts and quirks I'm aware of, so you don't have to find out about them the hard way:

This post is based on PostgreSQL 9.2. Each version improves a great deal, so don't be surprised if some of these issues are gone soon.

Database preferences and product selection methodolgy

I recently stumbled across an interesting weblog post by a DBA who expresses a strong preference for Oracle over PostgreSQL. I thought I'd respond to it with a few thoughts here, not so much because of the opinion expressed as the reasons given for it.