NDAs at SFPUG: Survey Results

It's boomtime in San Francisco, which means we're also full swing into The Cult of the NDA.  This includes many of our venues for SFPUG meetups; they require signing a confidentiality disclaimer before entering their office.  While I question the utility of this, since these hosts are providing us with free space, food, and drink, I'm really not in a position to argue.  So I launched a survey a month ago to see how much of a problem this is for our members.  I thought it might be useful to share the results with other PUG leaders so that they can also develop policies around this.

Here's the results. First, let me give you the overall results in a pie chart.  Scroll down to the bottom of my post for my suggested policy conclusions.


Incidentally, these are all graphed using iPython Notebook and Pylab, which is awesome way to do one-off graphs.  Here's the code for that graph:

    %matplotlib inline
    import psycopg2
    from pylab import *

    conn=psycopg2.connect('dbname=sfpug host=127.0.0.1')
    cur = conn.cursor()
    cur.execute("""SELECT att, count(*) as members
        FROM ndasurvey GROUP BY att ORDER BY att""");

    labels = []
    fracs = []
    explode = []
    for rec in cur:
        labels.append(rec[0])
        fracs.append(rec[1])
        explode.append(0.05)

    figure(1, figsize=(6,6))
    pie(fracs, explode=explode, labels=labels,
                    autopct='%1.0f%%', shadow=True, startangle=90)
    title('Attitudes Towards Venue NDAs: Overall')
    show()

So overall we have a somewhat split distribution.  BTW, here's the definitions of the attitudes:

• won't attend: I won't go to a meetup which requires signing
• needs review: I/my employer must review the agreement first
• depends on text: depends on what agreement says
• go anway: I don't like them, but I'll still go
• don't care: I don't care, whatever

Does this differ for which of our three divisions (San Francisco, East Bay, and South Bay) it is?






So, East Bay attendees don't seem to care in general, and South Bay attendees are much more concerned about reviewing the text of the confidentiality statement.  Which makes sense, if you think about it.

The other division we have is how frequent of an attendee someone is; is there a difference in attitude about our regulars?  Apparently there is:




... so the "won't attend" group is also, generally, a group which doesn't regularly go to SFPUG meetups.  What does this tell us for a policy for SFPUG? 

Well, the 5 options actually fall into three broader groups: those who will go regardless, those who need to see the text of the agreement, and those who won't go.  What if we group it that way?





So from the look of things, 83% of SFPUG will go to a meetup with an confidentiality agreement, provided that they get to review it first.  This suggests that a good policy for SFPUG should be:

"Venues which require confidentiality agreements are acceptable, if not preferred, provided that the text of the agreement is disclosed to the group beforehand."

It also suggests that I and the other leaders should be dilligent in finding out about such agreements whenever booking a venue.

Oh, and for full data, here's the population breakdown of the above:

Today's update release: NOW it's time to apply

You may have noticed that we've had a lot of churn in PostgreSQL update releases lately, doing three updates in less than two months.  We expect that we're done with that now, so it's time to get on updating all of your servers with today's update release.

Users of PostgreSQL 9.3 and 9.4 should update this weekend if possible.  This cumulative update fixes several problems with our "multixact" mechanism which have been plaguing Postgres since the release of 9.3.0.  While most users will not hit these issues, if you do, the bugs cause unrecoverable data corruption.  In other words, waiting "until you see a problem" is a really, really bad idea.

Additionally, one of the problems fixed requires cleaning up some garbage left by the version of pg_upgrade which shipped with Postgres versions 9.3.0 to 9.3.4.  If you used pg_upgrade to upgrade to one of those versions -- even if you subsequently upgraded to 9.4 -- then the server is liable to trigger a database-wide all-table autovacuum as soon as you restart after applying 9.3.9 or 9.4.2. 

If you are prepared for minutes to hours of degraded performance after applying the update, this is not a problem.  If it is a problem, though, you can do precautionary vacuuming before you apply the update.  Detailed instructions are supplied in the release notes on how to do this.

Now, go update those servers!

Determining your danger of multixact wraparound corruption

The PostgreSQL Project released an update on May 22, and the only really important fix in that update was a patch to prevent data corruption due to "Multixact member wraparound".  However,  it turns out that there are some issues with that update release, including one with file permissions, and potentially another one with Multixact truncation.  As a result, users are feeling justifiably reluctant to apply our last update release.

If there's a lesson for the project in this, it's "don't wrap an update release the same week as Feature Freeze".

Anyway, given this it would be good for users to determine more definitively if they are in actual danger of Multixact member wraparound (MMW hereafter), so that they know if they need to apply the update right away despite issues.  Here's how to do that:

1. navigate to the PostgreSQL data directory on your server(s), e.g. "/var/lib/postgresql/9.3/main"
2. switch to the subdirectory "pg_multixact/members" 
3. count the number of files in this directory, e.g. "ls -l | wc -l"

If you have a few dozen, hundred, or even a few thousand multixact member files, you're fine.  If you have over 10,000, and that number increases rapidly, then you are potentially in danger of MMW, and should apply the update sooner rather than later. 

Of course, there are other fixes in this latest update, and if one of them specifically affects you, you may have updated already.

Thank you to Thomas Munro for pointing out the simple way to determine this and giving me background material on the fixes.

Note: the above is my personal advice, and has not been approved by the PostgreSQL project, core team, or PostgreSQL Experts Inc. The PostgreSQL project generally advises applying all updates promptly.

Recursive cycle detection is recursive

In prior posts, I've gone over some methods to prevent cycles from being added to your database.  However, imagine that someone has handed you an existing adjacency list tree -- perhaps migrated from another DBMS -- and you need to find all of the cycles as part of data cleaning?  How do you do that?

One way, obviously, would be just explore all paths and flag the ones where any ID appeared twice:

    WITH RECURSIVE prev AS (
        SELECT folders.id, 1 AS depth, array[id] as seen, false as cycle
        FROM folders
        UNION ALL
        SELECT folders.id, prev.depth + 1, path || folders.id as seen,
            folders.id = any(seen) as cycle
        FROM prev
        INNER JOIN folders on prev.id = parent_id
    )
    SELECT *
    FROM prev;


However, the above has a serious issue: the query itself will cycle and never complete (in fact, it will error out). So we need to terminate each cycle when the first repeat happens.  Fortunately, that's easy to do, and we'll filter for only the cycles while we're at it:

    WITH RECURSIVE prev AS (
        SELECT folders.id, 1 AS depth, array[id] as seen, false as cycle
        FROM folders
        UNION ALL
        SELECT folders.id, prev.depth + 1, seen || folders.id as seen,
            folders.id = any(seen) as cycle
        FROM prev
        INNER JOIN folders on prev.id = parent_id
        AND prev.cycle = false
    )
    SELECT *
    FROM prev
    WHERE cycle = true;

The results of the above query look like this:

    id | depth |       seen       | cycle
   ----+-------+------------------+-------
    21 |     2 | {21,21}          | t
    13 |     5 | {13,14,15,11,13} | t
    14 |     5 | {14,15,11,13,14} | t
    15 |     5 | {15,11,13,14,15} | t
    11 |     5 | {11,13,14,15,11} | t
    (5 rows)

One thing to notice is that you'll get a row for every node in a cycle loop.  That's because with cycles, the choice of starting point is arbitrary.  So the above query isn't the best choice for a deep tree where you have a lot of existing cycles.  There's a 2nd way to detect cycles in a tree; lets see if any of the commentors can post that query.