Artificial Intelligence: Past, Present, & Futures

I will be presenting a talk on Artificial Intelligence: Past, Present, & Futures at the 2020 Capclave (virtual). That’s this coming Sunday from 1:30 to 2:25. Capclave is running Saturday & Sunday.

Virtual, yes, but they have rather a good line up of former guests of honor, kaffleklatsches, talks, panels, and so on. I’m looking forward. As to my talk:

Artificial Intelligence: Past, Present, Futures (Ends at: 2:25 pm)
Participants: John Ashmead (M)
From Oz’s Tik-Tok to the Mechanical Turk, from Neural Nets & Genetic Algorithms to Chess & StarCraft, from fighting the Coronavirus to flying Killer Drones, from Facial Recognition to Fakes, Deep Fakes, & Anti-Fakes, Artificial Intelligence (AI) is everywhere today. How did it start? What do we mean by AI? What are the basic AI techniques? How is it being used? What are the benefits? risks? and how should we manage AI going forwards?

The Elephant Versus the Bug

Debugging with PostgreSQL -- the Elephant takes on the Bug

Depending on the project, debugging can take 50 to 90% of development time. But it usually gets less than 10% of the press. PostgreSQL has great tools for debugging, but they are most effective when deployed as part of an overall strategy.

Ten days from now I’m doing a webinar on how best to do this. That’s 1pm, July 29th, 2020. You can register here.

It’s a fun talk: I focus on the key ideas, give a few good examples, have a quick quiz “There are three bugs on this slide: you have the next two slides to find them!” and then finish with some of my favorite debugging references: selected as particularly readable & practical.

[Update: the talk was given as planned. Thanks to Lindsay Hooper for great support! Video is online. And the audience of invisible Zoomers upped the number of bugs on the quiz slide from three to five!]

Last time I did this talk, my favorite piece of feedback was from Bruce Momjian, a founder of PostgreSQL and a member of the core team: Bruce said there was stuff he knew but hadn’t heard put into words, and stuff he just hadn’t seen yet.

And that’s how I hope this will be for you!

We will look at strategies for debugging PostgreSQL: how to find bugs, how to fix them, and how to keep them from happening in the first place.

We’ll look at root causes, technical tricks, and scientific strategies, and why — even if you can’t always write perfect code — it is usually a good idea to try.

We’ll hear from Bjarne Stroustrup, Sherlock Holmes, Kernighan and Ritchie, Pogo, & the experts of the PostgreSQL community.

Goal: less time debugging, more time building great tools and apps that stay up & get the job done.

If you can’t wait or just can’t make it, I have the latest version here: PDF, KeyNote, and PowerPoint, name your poison.

Update

Talk went off smoothly, about 65 or 70 attendees. The invisible Zoomers found two more bugs on the “quiz” slide! The event organizer, Lindsay Hooper of the PostgreSQL conference, did a great job setting every thing up, watching over a dry run, and giving feedback during. And she recorded and edited the webinar, so if you are interested in see the video “live”, herewith.

Time dispersion in time-of-arrival measurements

I will be presenting a paper “Time dispersion in time-of-arrival measurements” at the International Assocation for Relativistic Dynamics this coming Wednesday (6/3/2010). The conference was originally scheduled to be held in Prague but has been moved online because of COVID-19. It may still be held as a physical conference as well, we will see.

My own paper is a follow up to my “Time dispersion in quantum mechanics“, published last year as part of the Institute of Physics Conference Series. That took the hypothesis: the quantum wave function should extend in time as it does in space & worked out the implications. The new paper is about experimental tests of the hypothesis: how would we determine if this hypothesis is true. Since it is real science however I turned the question around & made it “how do we prove that the wave function does not extend in time”.

In the new paper I shift focus to the Heisenberg uncertainty principle (HUP), specifically to the Heisenberg uncertainty principle in time and energy. Einstein & Bohr both held it was true, in fact essential if quantum mechanics was to be consistent with relativity. Bohr’s demonstration that it was was the end of Einstein’s direct attempts to falsify quantum mechanics.

Note that the formulation “the Heisenberg uncertainty principle applies to time/energy as it does to space/momentum” is loosely equivalent to “the wave function extends in time as it does in space”. If the wave function extends in time, then we would get the HUP in time/energy as a side-effect. And the most direct tests of the wave function extending in time are really tests of the HUP in time/energy.

The test I primarily focus on is that if the wave function extends in time all measurements in the time dimension would be just a bit fuzzier. In particular, if you are measuring when a particle is detected, if you are measuring the time-of-arrival, then if the wave function is extended in time you expect to see it both sooner & later than otherwise expected.

The advantage of this as a test is that the additional fuzziness if present at all must be present everywhen. Any time-varying experimental setup can potentially serve as a test.

The main problem — somewhat to my surprise — was that we really don’t know how to predict the time-of-arrival in standard quantum mechanics, let alone quantum mechanics with time in play as well! I’m trying to make a pincer attack on time: left jaw — standard quantum mechanics (SQM), right jaw — quantum mechanics with time (TQM). I was focused on the right jaw, but found that actually it was the left jaw that was weak. So I had to backtrack & deal with this problem. Interesting. And this turned out to be the single trickiest bit in the paper.

After getting the left jaw in better shape, good enough to take a punch anyway, I did a recap of TQM. This was probably the 2nd trickiest bit of the paper: how do you describe a hypothesis that took over a hundred pages and nearly five hundred equations to work out in a just a few pages? I found the core ideas coming a bit clearer in my own head at least. That’s gotta be worth something.

Then the payoff bit, the actual tests, is only the last quarter of the paper. And after working out how the additional fuzziness in time plays out, I got to my favorite test: the single slit in time. This is the single cleanest test of the idea. Not an easy experiment however.

Really the best part of tests of TQM is that if it is proved true, great. But if it proved false it will be taking down one or two of its neighbors with it. TQM is built by applying the quantum rules to relativity (or applying relativity to the quantum rules). If it is false, one (or both) of those two has a problem. And that in turn means there are really no null experiments.

And if I know my experimentalists, there is nothing they like more than proving a bunch of theorists wrong. If I have setup the arguments correctly — we’ll see — then they are sure to break something. As the well-known quantum experimentalist Nicholas Gisin said to me a long time ago (I paraphrase, it was quite a long time ago) “Look, I don’t care what your theory of time is. Just give me something I can prove wrong!”

Physical Balticon Cancelled — Virtual Balticon on

Due to the Covid-19, the physical 2020 Baltimore Science Fiction Convention — Balticon — has been cancelled.

The organizers, nothing if not game, are putting on a virtual Balticon. Looks pretty good. And free (tho they ask for a donation to a GoFundMe for new writers and to help Baltimore’s disadvantaged youth).

Unfortunately I had already made other commitments, after the physical Balticon was cancelled and before I knew it was being replaced by a virtual one, so will not be able to speak at the virtual event.

But I hope to catch a number of the events this coming Memorial Day weekend and hope you will be able to do so as well.

The Past, Present, and Futures of Artificial Intelligence

Tik-Tok — one of the early visions of Artificial Intelligence

Artificial Intelligence is in the news, no question. The last few science fiction conventions I’ve been at, panels on AI have been getting filled rooms & lots of questions. It’s all over the news as well. And it’s a subject I find interesting: and — being a professional programmer — an area where I may be able to contribute, perhaps looking at the use of AI techniques to generate physics experiments.

What is meant by AI is one problem: is it anything that uses AI techniques, as Neural Nets or Genetic Algorithms? Or do you need to be pointing in the direction of some kind of sentience for it to be true AI? Will it replace us? Should it? The hype/content ratio sometimes hits near Trumpian levels.

So good subject for a talk. But what line of attack to take? Last week I caught a great talk at DataPhilly on the use of AI for sports betting (and other things). The formal title was:

Practical Scaling: How to Use Simple Tools to Create and Implement Complex Modeling SystemsJames Piette

For “complex modeling systems” think practical AI. My personal favorite of his slides cited three principles:

  • Moravec’s Paradox: AI and humans are good at opposite things. So use AI for what it is good at (crunching) and let the humans do what they are good at (intuiting).
  • Pareto’s Principle or the 80/20 rule. 20% of the work gets you 80% of the value, so focus on the high value parts of the problem.
  • The Scientific method: observe, hypothesize, test, repeat. This works for science — and it works for software debugging (another favorite topic of mine), startups, and AI.

And this strikes me as the kind of no-nonsense, practical, even scientific approach that a subject like AI needs. (Thanks James!) So for my 2020 SF Convention talk:

The Past, Present, and Futures of Artificial Intelligence. — From Oz’s Tik-Tok to the Mechanical Turk, from Neural Nets & Genetic Algorithms to Chess & StarCraft, from Medical Diagnosis to Robot Frogs, from Facial Recognition to Fakes, Deep Fakes, & Anti-Fakes, AI is everywhere today. How did it start? What do we mean by AI? What are the basic AI techniques? How is it being used? What are the benefits? risks? and how should we manage AI going forwards?

Be seeing you:

Philcon 2019 — Recap

Ultimately my “Time dispersion in quantum mechanics” is an attempt to answer Gisin’s question

Got some great questions during my talk at Philcon: lots of stuff I had not considered before. If quarks are high-energy beasts, and if high-energy means short time, and if short time means increased effects of time dispersion, shouldn’t you look at impacts on quark calculations. Should & will! And what of quantum computing: would dispersion in time provide additional bandwidth for quantum computing? Very probably! Not to mention additional insight into the bugaboo of the quantum computing, decoherence.

I also liked that the audience really picked up on why I centered the investigation on falsifiability: I wasn’t trying to prove that there is dispersion in time, I have presented a way to prove there is not. Falsifiability is what makes science science.

I have uploaded the Keynote, PowerPoint, and PDF versions of the talk.

My panels were, as usual, interesting.

Hildy Silverman did a great job moderating Dystopia Now! she kept the discussion focused & moving. Fellow panelist Hakirah D’Almah, a journalist with a focus on the Middle East, was particularly trenchant. Hard to find the bright side of Dystopia, but I think we did. 1984 is a deeply optimistic work: by writing it (Orwell’s last, he died shortly after completing it) Orwell helped us avoid it.

I will admit the Evolution of Mars panel, while interesting, drifted a bit (Wild Marses I Have Known would have been a more accurate description).

I was happy to be the moderator on Looking for Life in our Solar System: the great thing about being a moderator — especially when you are the least qualified person the panel — sit back & let your fellow panelists — Earl Bennett, Dr. H. Paul Shuch, John Skylar — do the heavy lifting. Which they did very well!

And I was also moderator on The Blurry Line between Cutting Edge and Pseudoscience. The panel was right after my talk, so made a nice seque. The best question came from an audience member: how do I tell, when I see stuff on the web, what level of credibility to give it? Just asking that question is the first step. The panelists suggested credentials of the author, links to it, and my personal favorite: does the author find the good in his/her opponent’s arguments, recognize the weak spots in his/her own?

Philcon 2019 — Precap

Lagrange's tightrope, balancing kinetic & potential energy
Working out the effects of quantum mechanics on time requires a delicate balancing between kinetic & potential energy; Lagrange showed the way

The Philcon 2019 schedule is up. I’m doing my Time Dispersion in Quantum Mechanics talk — the tightrope walker is one of the slides, gives you a sense of the style of the whole, balancing ideas against math, time against space, classical against quantum, … — and four panels, all interesting. The con runs from Friday 11/8/2019 through Sunday 11/10. Details:

LOOKING FOR LIFE IN OUR SOLAR SYSTEM

Fri 8:00 pm. John Ashmead (mod), Earl Bennett, Dr. H. Paul Shuch, John Skylar. What’s the latest evidence that we’ve found? Where are the best places to look?

TIME DISPERSION IN QUANTUM MECHANICS

Sat. 4:00 PM. John Ashmead. We know from quantum mechanics that space is fuzzy- that particles don’t have a well-defined position in space — and we know from special relativity that time and space are interchangeable. So shouldn’t time be fuzzy as well? Thanks to recent technical advances in measurements at “short times” we can now put this to the test. Discuss!

THE BLURRY LINE BETWEEN CUTTING EDGE AND PSEUDOSCIENCE

Sat 5:00PM. John Ashmead (mod), Charlie Robertson, Rebecca Robare, Dr. H. Paul Shuch, Carl Fink, Lawrence Kramer. Niels Bohr famously said, “Your theory is crazy but it’s not crazy enough to be true”. How do we keep an open mind but not one so open that our brains fall out? A look at how to tell strange-yet-true science from weapons grade balonium.

THE EVOLUTION OF MARS

Sat 7:00 PM Darrell Schweitzer (mod), John Ashmead, Tom Purdom, James L. Cambias, Earl Bennett. How have depictions of Mars changed in SF from the imaginings of Burroughs and Bradbury to the Mars we know now from studying its surface?

DYSTOPIA NOW

Sat 9:00 PM Hildy Silverman (mod), John Ashmead, Karen Heuler, B. Lana Guggenheim. No one should be surprised that climate change, technological over-reach, and political anxieties have translated themselves into a bumper crop of contemporary dystopian fiction. How coherent are their messages — and how good are the stories? Is there a way to make such a work more than a cautionary tale about the present era’s problems?

Capclave 2019 — Recap

Alice & her dog examine the mysteries of time and quantum mechanics, slide from my talk at Capclave 2019.

Had a great time at Capclave. It’s one of the smaller cons — slightly north of 300 people — and doesn’t have some of the usual con stuff like an art show or cosplay. But for precisely those reasons, you tend to have more of those repeated one-on-one conversations that, for me, are the real life of a con.

Had a good time at the five panels I was on. All were energetic & held the audience.

Technospeed — is technology moving too far too fast? — was the first (Friday evening), with the smallest audience. It was hard to know what to do with the subject, a tad too broad I suspect. Much of the discussion focused on AI, a better subject. (I may take AI that for my big talk next year.) Not a bad panel, with that said: we had a lot of fun with Kurzweil’s Singularity and related topics.

My next two panels (both Saturday), The Coming Civil War & Failed SF Predictions, both had Tom Doyle as moderator. He did a great job, particularly with the Coming Civil War, where he asked the assembled panelists how they would treat present various scenarios from a fictional point of view. How would you tell the story of cities war with the country side? and so on. Kept the conversation from degenerating into what they thought of the [insert-derogatory-noun]-in-chief.

I had a bit of fun with Failed SF Predictions, bringing in some books of pulp age cover art: jet packs, menacing octopi, orbiting cities, threatening robots, giant computers, and attacking space fleets, … The role of women in SF in the days of the pulps is nothing like what it is in the real world today; a lot of the Failed SF Predictions chosen were about gender issues. Not even the first wave of feminist SF writers — LeGuin, Joan Vinge, Joanna Russ, … — fully anticipated how much the field would evolve.

Sunday my first panel was on Secrets of the Dinosaurs. The other three panelists were the GOH Robert Sawyer (author of the Far-Seer trilogy of dinosaur novels), Michael Brett-Surman (Collections Manager of the National Dinosaur Collection at the Smithsonian and co-author/editor of several dinosaur books with Dr. Thomas R. Holtz) and Dr. Thomas R. Holtz (who is the T. Rex of T. Rex scholarship). Being on a dino panel with these three was like being a small mammal in the Jurassic. The primary objective is to not get underfoot and squashed. All three are immensely polite & courteous individuals, who would never think to squash a small mammal who wandered on to the planet panel. I took advantage — as the designated amateur — to ask about dino parental care, how did hadrosaurs defend themselves against a T. Rex (rather easily — those tails are not just ornamental!), and my final q: if dinosaurs lived in groups & relied on visual & auditory display, did they have barn-dances?

My final panel was Exoplanets. My fellow panelists (Inge Heyer & Edward Lerner) were both expert & I had done a fair amount of swotting, so we had a good time going over rogue planets between the stars, planets made of diamond, life within the hidden seas, and various methods of finding new exoplanets — the total of confirmed exoplanets is 4000 & counting!

And my Time Dispersion in Quantum Mechanics talk went well (Saturday afternoon). I had a couple of practice run-thrus with a “volunteer” audience, which left it leaner, shorter, and easier to follow. Same content, but no math (except E=mc-squared, which is so familiar it doesn’t count). Talk went well, good audience and great questions: some I answered there, some I dealt with in the hall discussions, and one or two I had to admit “that’s one for the experimentalists!”

And my thanks to Brent Warner of NASA, who corrected — with great politeness — a couple of soft spots in the presentation. I will incorporate into the next iteration, in two weeks as it happens at Philcon.

And the next morning I got what I think is the best compliment I have ever received: the father of a 10th grader said his daughter was so inspired by my talk she is thinking of going into physics & quantum mechanics. “Here’s my email; tell her to feel free to follow up!” Yes!

Capclave 2019 — Talks & Panels

I’m appearing at Capclave this year (October 18th thru 20th), doing my talk on Time Dispersion in Quantum Mechanics (3pm on Saturday the 19th) and five panels, all great topics: Technospeed, Coming Civil War, Failure of SF Prediction, Secrets of the Dinosaurs, & Exoplanets. Prep for these will be a lot of fun. And the other panelists include a number of old friends and I’m sure some new ones.

Capclave — always one of the best organized cons — did a great job on the schedules, sliced & diced by time, track, & trouble-maker. I can’t improve on theirs for me:

Friday 9:00 pm: Technospeed (Ends at: 9:55 pm) Truman
Panelists:John AshmeadMartin Berman-GorvineBud Sparhawk (M), Christopher Weuve
Is technology moving too far? Too fast? What is coming up in the future? What happens to those left behind? Can people who never learned how to set the time on their VCRs handle what brain-implants and whatever else is coming next? Is this increasing the generation gap?
Saturday 10:00 am: Coming Civil War (Ends at: 10:55 am) Washington Theater
Panelists:John AshmeadTom Doyle (M), Carolyn Ives GilmanSarena UlibarriChristopher Weuve
Is the U.S. dividing again? Or are current difficulties just an historical burp? Why didn’t the US divide in the 1960s? What can be done to keep the Union together? Or would splitting be a good thing? Will the South rise again or will it be cities versus countryside?
Saturday 2:00 pm: Failure of SF Prediction (Ends at: 2:55 pm) Truman
Panelists:John AshmeadTom Doyle (M), Natalie LuhrsSarah PinskerK.M. Szpara
SF is not really supposed to predict the future but presents possibilities. Still, comparisons are inevitable. What did past SF writers get right and wrong about today? How can writers do a better job (or shouldn’t they even bother trying?)
Saturday 3:00 pm: Time Dispersion in Quantum Mechanics (Ends at: 3:55 pm) Truman
Panelists:John Ashmead (M)
John Ashmead gives a science talk on time dispersion. Is time fuzzy? In quantum mechanics space is fuzzy. And in special relativity time and space are interchangeable. But if time and space are interchangeable, shouldnt time be fuzzy as well? Shouldnt quantum mechanics apply — to time? Thanks to recent technical advances we can put this to the test. We ask: How do you get a clock in a box? How do you interfere with time? When is one slit better than two? And what happens at the intersection of time and quantum mechanics?
Sunday 10:00 am: Secrets of the Dinosaurs (Ends at: 10:55 am) Monroe
Panelists:Robert J. SawyerJohn AshmeadMichael Brett-SurmanThomas Holtz (M)
Did dinosaurs really have feathers? Why did people get it wrong for so long? What else did people believe about dinosaurs 50 years ago that is no longer true? Why did people think that then? What of our present knowledge about dinosaurs is most likely to also be incorrect?
Sunday 12:00 pm: Exoplanets (Ends at: 12:55 pm) Truman
Panelists:John AshmeadInge HeyerEdward M. Lerner (M)
What do we know about planets outside our solar system? How do we discover them? What are the implications for aliens Exobiology?

Debugging with PostgreSQL – Sample code

My talk last week at FOSSCon, “Debugging with PostgreSQL: A Strategic Approach” went well. Lots of energy in the room. Good audience.

Bruce Momjian, one of the founders of PostgreSQL, was in the audience & said afterwords (roughly):  “that’s what I’ve been thinking for years; good to hear it spelled out in words”. I got that from a number of other programmers in the audience as well. Much pleased.

Bruce went on to ask I propose the talk for the 2020 World PostgreSQL Conference, which I shall.

I thought it might be helpful to write some of the code examples up in a complete script, so any one who wishes can run and/or hack. I found a few problems and infelicities myself while doing this. Further suggestions very welcome!

Warning: here there be code.

To run the code (assuming you have PostgreSQL 11 installed and call the sample “sample_all.sql”):

psql -U postgres -d postgres -f sample_all.sql > sample_all.out 2>&1

Since it can be tricky to cut-and-paste from a web page, I have uploaded the raw code as “sample_all.txt” (you can’t upload files with an SQL extension for security reasons). For completeness, here are the slides themselves as PDF.

The code is careful to create a sample database, build & test stuff, and then remove the whole thing as if nothing had happened. If you don’t like doing this sort of thing from the postgres user (don’t blame you) create a user with createdb privileges & use that to run this.

Sample Code

/*
	John Ashmead 
		sample_all.sql:  samples as used in my talk "Debugging with PostgreSQL"
		FOSSCon 8/17/2019

	Sample_all.sql is a complete code sample:

		it builds a sample database called sample with a user sample

		then creates a few types, 
		a timestamp trigger function, 
		a table people, 
		and then a small function to set the social security number

	The goal was to provide illustrations for the talk of what I call "self-debugging code"
		1) many problems are trapped, as by type checking, before they can do any harm
		2) in other cases, you will get an exception
		3) and in the worst case, at least you will see what went in and what came out
		
	You can run this as user postgres database postgres.  You could run as any user with createdb,
	if you fix the clean section to go from "postgres" to that user.

	I normally run scripts using psql with "-v ON_ERROR_STOP=1" set on the command line, 
	which will cause psql to exit on the first error.
	
	But in this case you need to allow for errors in the test section. 

	Therefore an appropriate command line is: 
		"psql -U postgres -d postgres -f sample_all.sql > sample_all.out 2>&1"

	The comments are taken from points made in the talk,
	hence their perhaps slightly pedantic character.

	Any comments, my email is "john.ashmead@ashmeadsoftware.com".
*/

\qecho Build user and database
create user sample with password 'HighlySecret';

create database sample with owner = sample;

\c sample sample

set search_path to public;

/*
	Create generic timestamp function: timestamp_trg

	Provided the tables use the fields "updated_at" and "created_at" as timestamps,
		you do not need to rewrite this function on a per table basis.

	It is very useful to have timestamp fields on most tables, even if they are not specifically needed:
		1) knowing "when" something went wrong often takes you much of the way to figuring out "what" went wrong
		2) and using triggers takes the load off the development programmer
		
	I've been working a lot with Ruby-on-Rails which will create & update these fields for you.
	But if you rely on Ruby-on-Rails then you create a lot of traffic on the wire,
	and you can miss cases where the updates were done behind ruby's back,
	as by other scripts & tools.
*/
	
\qecho Create timestamp function
create or replace function public.timestamp_trg() returns trigger
    language plpgsql
    AS $
  begin
	/* assume we have updated_at and created_at fields on the table */
    if new.created_at is null
    then
      new.created_at = now();
    end if;
    new.updated_at = now();
    return new;
  end;
$;

/*
	My own experience has been that it is much better to use logical types, even for simple fields:
		1) it makes changing types much easier:  if three tables are using a social security number, 
		then you only have to change it in one spot
		2) it makes the field names almost self-documenting
		3) and you can include bits of validation, as here, when the field is used

	Obviously this, like any principle, can be carried to extremes.  
	This is, as Captain Barbossa might put it, a guideline rather than a rule.
*/
\qecho Create some types & then the people table

begin;

/*
	Everysooften you run into someone with a single character last name, as Kafka's "K",
	so we allow for that.  

	I prefer text to varchar or character.  Performance about the same (in some cases better) and 
	if you put a fixed length in, what happens when you have to add the last name of a king or queen
	where the name is basically the history of the monarchy?
*/
create domain public.lastname_t text check(length(value) > 0);
comment on domain public.lastname_t is 'holds last name.  Has to be at least one character long.';

create domain public.firstname_t text;
comment on domain public.firstname_t is 'holds first name.  Can be missing';

create domain public.middlename_t text;
comment on domain public.middlename_t is 'holds middle name or initial.  Can be missing';

create domain public.ssn_t text check(value similar to '\d{9}');
comment on domain public.ssn_t is 'holds social security number.  If present, must be 9 digits long.';

/*
	ok_t is self-documenting in the sense that true is good and false is bad.
	This seems obvious enough, but I have seen the reverse convention used.

	As an aside, it is better for maintenance to use positive tests, i.e. "if we_are_ok" 
	rather than negative ones "if not we_are_failed".  Slightly easier to read.
	Which is important when it is 2am and the code has to be working by 9am.

	Further, better to use "not null" whenever possible:  three valued logic is a great source of bugs.
*/
create domain public.ok_t boolean not null;
comment on domain public.ok_t is 'true for success; false for successness challenged';

-- PostgreSQL sequences are a joy!
create sequence public.people_id_seq start 1;

/*
	we are using the ruby convention that we should get the plurals right:  person/people rather than person/persons.
	The only place you see persons is in a police report:
		three persons of a suspicious character were espied leaving the premises in a rushed and furtive manner.
*/
create table public.people (
	id int primary key default nextval('people_id_seq'),
	lastname lastname_t not null,
	firstname firstname_t,
	middlename middlename_t,
	ssn ssn_t,
	updated_at timestamp with time zone default now(),
	created_at timestamp with time zone default now()
);

/*
	In this simple case the comments are, in all candor, redundant.

	But, if you comment everything, then tools like SchemaSpy can give you a nice report of everything in your database.

	And, it is a good habit to get into.
*/
comment on table public.people is 'list of people';
comment on column public.people.id is 'primary key of people table';
comment on column public.people.lastname is 'lastname of person -- mandatory';
comment on column public.people.firstname is 'firstname of person -- optional';
comment on column public.people.middlename is 'middlename of person -- optional';
comment on column public.people.ssn is 'social security number of person -- optional';
comment on column public.people.updated_at is 'last time this row was updated';
comment on column public.people.created_at is 'time this row was created';

-- A unique index on id will be created automagically, so don't bother. 

create index people_name_ix on public.people using btree(lastname, firstname, middlename);

create unique index people_ssn_uix on public.people using btree(ssn);

insert into public.people(lastname, firstname, middlename) values ('Programmer', 'J', 'Random');

select * from public.people order by id;	-- make sure we look OK

/*
	One useful trick is to put a begin at the top of a script & a rollback at the end,
		until you are confident that the script works OK.
	This can be done even for DDL -- i.e. create table -- an incredibly strong feature of PostgreSQL.
*/
	
-- rollback
commit;

-- create ssn_set
\qecho Create the social security function which served as the main example of self-documenting code

-- begin/commit not strictly needed, the create function is an atomic unit, but still a good habit
begin;

create or replace function public.ssn_set( 
	person_id0 public.people.id%type, 	-- makes certain the function & table types are lined up
	ssn0 public.people.ssn%type, 		-- lets us get in a bit of validation (against the ssn type) before we get started
	debug_flag0 boolean default false	-- this lets you turn on debugging at will, if there is a production problem
) 
returns ok_t as $
declare
	person_id1 people.id%type; -- more specific than int
	ssn1 people.ssn%type;	   -- could use ssn_t, but this is still more specific than a generic type
	row_count1 bigint;		   -- more check-y stuff
begin
	if debug_flag0 then
		/*
	   		notice the use of the function name in the message:
	   		always identify the source in an error message! this could be part of a thousand messages
		*/
		raise notice 'ssn_set(%, %)', person_id0, ssn0;	
	end if;

	select id into person_id1 from people where id = person_id0 limit 1; -- limit 1 is overkill
	if person_id1 is null then
		/*
			be as specific as possible in an error message
		*/
		raise exception 'ssn_set:  person_id0 % is not in people table', person_id0;
	end if;

	/*
		We have a unique index on the ssn, but we can issue a more precise error message if we check first.

		This also serves as a double-check if we set the table up incorrectly, unlikely for social security numbers,
		but can happen in general.
	*/
	select id into person_id1 from people 
		where ssn = ssn0 and id != person_id0;
	if person_id1 is not null then
		raise exception 'ssn_set:  ssn % is already in use by id %', ssn0, person_id1;
	end if;

	-- this whole function is really just an elaborate wrapper for this one line
	update people set ssn = ssn0 where id = person_id0;
	/*
		and now make absolutely sure that it worked
	*/
	get diagnostics row_count1 = row_count;
	if row_count1 != 1 then
		raise exception 'ssn_set:  unable to set ssn to % for person# %, rows affected = %', ssn0, person_id0, row_count1;
	end if;

	/*
		giving the exit values as well as entry values of key variables lets us trace
		the flow of gozintas and gozoutas without doing anything more than setting a debug flag
	*/
	if debug_flag0 then
		raise notice 'ssn_set: person %: ssn changed to %', person_id0, ssn0;
	end if;

	/*
		All previous returns were by "raise", this is our first "normal" return.
	*/
	return true;
end; $ language plpgsql;

commit;

/*
	and of course the obligatory red/green tests
	-- bracket the allowed value with three red tests, then verify it works
	-- then check for dups:  one red, one green
*/
\qecho Test the social security function: three red tests then one green

\qecho Expect fail -- nonsense
/*
	We use the "(select...)" in the argument list to avoid hard-coding IDs,
	this will make it easier to extend the tests further, if necessary.

	I didn't bother to assign the "red" values into variables in this section, 
	since we are only using each value once.
*/
select public.ssn_set((select id from public.people where lastname = 'Programmer'), 'unmitigated nonsense'::ssn_t, true);
select * from public.people where lastname = 'Programmer';

\qecho Expect fail -- too short
select public.ssn_set((select id from public.people where lastname = 'Programmer'), '01234567'::ssn_t, true);
select * from public.people where lastname = 'Programmer';

\qecho Expect fail -- too long
select public.ssn_set((select id from public.people where lastname = 'Programmer'), '0123456789'::ssn_t, true);
select * from public.people where lastname = 'Programmer';

-- using variables with psql makes it easier to change up the tests later
\set test_ssn 012345678
\set test_ssn2 987654321
\qecho Expect success -- just right
select public.ssn_set((select id from public.people where lastname = 'Programmer'), :'test_ssn'::ssn_t, true);
select * from public.people where lastname = 'Programmer';

\qecho Second round of testing on the social security function: one red and one green
\qecho Expect fail:  we have already used this SSN
insert into people(lastname) values ('Programmer Junior');
select public.ssn_set((select id from public.people where lastname = 'Programmer Junior'), :'test_ssn'::ssn_t, true);
select * from public.people where lastname = 'Programmer Junior';

\qecho Expect success: give Junior his/her own SSN
select public.ssn_set((select id from public.people where lastname = 'Programmer Junior'), :'test_ssn2'::ssn_t, true);
select * from public.people where lastname = 'Programmer Junior';

-- cleanup:  you have to back out of the sample database and then remove first it, then the role
\qecho A clean database is a happy database

\c postgres postgres
drop database sample;

drop role sample;

WordPress Themes