Author Archives: Chris F Carroll

Postgres quick start for MS SQL / T-SQL developers

After 17 years on T-SQL I’m at last on my first project with Postgres. Here are the first things I needed for the transition:

  • Do this first: https://wiki.postgresql.org/wiki/First_steps as it answers most of your first questions about connections, databases, using roles for logins, users and groups, and so on.
  • 'String concatenation is done with ' || 'a double pipe '.
  • No NVarchar is needed, just varchar. The default installation uses UTF-8.
  • Date & Time: use timestamp for datetime/datetime2
  • Read https://www.postgresql.org/docs/9.1/static/datatype-datetime.html for more on dates, times and intervals.
  • Use more semicolon terminators If ... Then ... ; End If ;
  • plpqsql script is separated from “pure” SQL in a way that T-SQL just doesn’t bother with. If you are writing scripts, plpqsql must either be inside a function or inside an ‘anonymous function block’, usually delimited by $$ or $somestring$; and Declare variables in one block before Begin :
    Select 'This line is plain SQL' ;
    Do $$
    Declare
      anumber integer ;
      astring varchar(100);
    Begin
    If 1=1 Then
     Raise Notice 'This block is plpgsql' ;
    End If;
    End $$ ;
    Select 'This line is SQL again' ;
    

    In fact, functions are defined in a string, and the $$…$$ delimiters is a postgres syntax for specifying a string constant without having to escape any special characters at all. They can be used anywhere in code. Functions can be defined in other languages than plpgsql. See e.g. https://pgxn.org/dist/plv8/doc/plv8.html.

  • Variables: just use a plain identifier: myvariablename with no @ symbol or other decoration. BUT as a consequnce you must avoid variable names in a query that are the same as a column name in the same query. BUT BUT in Ado.Net Commands with Parameters, still use the @parametername syntax as you would for SQL Server
  • Use Function not procedure:
    Create or Replace Function procedureName( forId int, newName varchar(20) )
    Returns int -- use returns void for no return
    As $$
    Declare 
      localvariable int;
      othervariable varchar(10);
    Begin
     Insert into mytable (id,name) values (forId, newName) On Conflict (id) Do Update Set Name=NewName ;
    End $$;
  • Ooh, did you notice how postgres has a really simple upsert syntax?
  • Postgres does function overloads, so to drop a function you must give the signature: Drop function functionname(int)
  • Whereas the T-SQLer does everything in T-SQL, other database systems use non-sql commands for common tasks. For working at a command-line, learn about psql meta-commands.. Start with \c to change database, \l to list object, and \? to list other meta commands.
  • Postgres have (I think) made more effort than MS to stay close to the SQL standards and as with T-SQL, the docs go into detail on deviations. But this means that much of your code for databases, schemas, users, tables, views, etc can be translated fairly quickly.
  • Replace Identity with Serial. For more complex options, Postgres uses SQL sequences.
  • Replace Select top 1 ... with Select ... Fetch First 1 Only.
  • role covers all of logins (Create Role ... Login) and database users (Grant Connect on Database ... TO ...) and also covers groups.
  • The Nuget package for Ado.Net, including for .Net core, is Npgsql
  • Scan these

Converting T-SQL to Postgres

Here’s my initial search/replace list for converting existing code:

Search Replace
@variableName _VariableName
NVarChar VarChar
Identity Serial
UniqueIdentifier uuid
Raise Raise Exception
Print Raise Notice
Insert Into table Select … Insert into Table (COLNAMES) Select …
If Then Begin … End Else Begin … End If Then … ; Else … ; End If ;
Create Table #Name Create Temporary Table _Name
NewId() websearch Create Extension “uuid-ossp” and then use uuid_generate_v1() or uuid_generate_v4()
Alter Function|Procedure Create or Replace Function

Deep Learning & Unintended Algorithm Bias

This was a 5 minute talk on deep learning for the very excellent @chesterdevs. Like others talking about deep learning, I took visuals and the face-learning example from the landmark 2012 paper, Quoc Le/Google/Andrew Ng paper, “Building High-level Features Using Large Scale Unsupervised Learning.”

Only afterwards did I notice that the subset of images which their system show as “most like a face” from their test set were 90% male and 90% white, as is the prototypical face that the machine outputs.

And so we have a neat demonstration of unintended algorithm bias: their input was 10 million randomly-chosen youtube videos; the output was white and male. I bet they didn’t expect that.

A salutary reminder that—as the hard-working statistician will tell you—“random selection” does not mean “unbiased”.

AutoHotKey script for they who, being Mac Users and also equipped with an Apple keyboard, yet still they work at a Windows desktop

Surprising how much time you can spend on these little niggles…

  • Irritated that Windows doesn’t have an ellipsis key?
  • Wondering how to do printscreen from your apple keyboard?
  • Really really fed up with swapping between Cut‘n’Paste is “⌘-C,⌘-V” and Cut‘n’Paste is “Ctrl-c-Ctrl-v”?

Help is at hand.

AutoHotKey

When I first came across I was a bit unsure about using AutoHotKey. But I have seen the light. It is the bee’s knees. It is open source, widely used for years, free, small footprint and is the ultimate customise-all-the-things tool for Windows. It is a combined scripting tool & Keyboard/Mouse hotkey manager.

My AutoHotKey for a Mac User with an Apple keyboard on Windows Script

https://gist.github.com/chrisfcarroll/dddf32fea1f29e75f564

There you are. It’s all you need. That, and a few hours to customise it yourself. Then a few more hours to… oh, never mind.

Disclaimer

When I said that AHK is the bee’s knees, I didn’t say that the language isn’t arcane, unintuitive and bearing signs of organic growth over a decade or more…

Conway’s Law & Distributed Working. Some Comments & Experience

The eye-opener in my personal experience of Conway’s law was this:

A company with an IT department on the 1st floor, and a marketing department on the 2nd floor, where the web servers were managed by the marketing department (really), and the back end by the IT department.

I was a developer in the marketing department. I could discuss and change web tier code in minutes. To get a change made to the back end would take me days of negotiation, explanation and release co-ordination.

Guess where I put most of my code?

Inevitably the architecture of the system became Webtier vs Backend. And inevitably, I put code on the webserver which, had we been organised differently, I would have put in a different place.

This is Conway’s law: That the communication structure – the low cost of working within my department vs the much higher cost of working across a department boundary – constrained my arrangement of code, and hence the structure of the system. The team “just downstairs” was just too far. That distance was composed of gaps & differences in priorities, release schedules, code ownership, and personal acquaintance.

Conway’s Law vs Distributed Working

Mark Seemann has recently argued that successful, globally distributed, OSS projects demonstrate that co-location isn’t all it’s claimed to be. Which set me thinking about communication in OSS projects.

In my example above, I had no ownership (for instance, no commit rights) to back end code and I didn’t know, and hence didn’t communicate with, the people who did. The tools of OSS—a shared visible repository, the ability to ‘see’ who is working on what, public visibility of discussion threads, being able to get in touch, to to raise pull requests—all serve to reduce the cost of communication.

In other words, the technology helps to re-create, at a distance, the benefits enjoyed by co-located workers.

When thinking of communication & co-location, I naturally think of talking. But @ploeh‘s comments have prodded me into thinking that code ownership is just as big a deal as talking. It’s just something that we take for granted in a co-located team. I mean, if your co-located team didn’t have access to each other’s code, what would be the point of co-locating?

Another big deal with co-location is “tacit” knowledge, facilitated by, as Alistair Cockburn put it, osmotic communication. When two of my colleagues discuss something, I can overhear it and be aware of what’s going on without having to be explicitly invited. What’s more, I can quickly filter out what isn’t relevant to me, or I can spontaneously join conversations & decisions that do concern me. Without even trying, everyone is involved when they need to be in a way that someone working in a separate room–even one that’s right next door–can’t achieve.

But a distributed project can achieve this too. By forcing most communication through shared public channels—mailing lists, chatrooms, pull request conversations—a distributed team can achieve better osmotic communication than a team which has two adjacent rooms in a building.

The cost, I guess, is that typing & reading is more expensive (in time) than talking & listening. Then again, the time-cost of talking can be quite high too (though not nearly as a high as the cost of failing to communicate).

I still suspect that twenty people in a room can work faster than twenty people across the globe. But the communication pathways of a distributed team can be less constrained than those same people in one building but separated even by a flimsy partition wall.

References