Disabling Postgres constraints for pg_dump
By Greg Sabino Mullane
July 13, 2016
Constraints in Postgres are very powerful and versatile: not only are foreign keys, primary keys, and column uniqueness done internally via constraints, but you may create your own quite easily (at both the column and table level). Most of the time constraints are simply set and forget, but there is one time constraints may become a problem: copying the database using the pg_dump program.
The issue is that constraints are usually added before the data is copied to the new table via the COPY command. This means the constraint fires for each added row, to make sure that the row passes the conditions of the constraint. If the data is not valid, however, the COPY will fail, and you will not be able to load the output of your pg_dump into a new database. Further, there may be a non-trivial performance hit doing all that validation. Preventing the constraint from firing may provide a significant speed boost, especially for very large tables with non-trivial constraints.
Let’s explore one way to work around the problem of pg_dump failing to work because some of the data is not valid according to the logic of the constraints. While it would be quicker to make some …
postgres
Liquid Galaxy on PBS
By Ben Witten
July 12, 2016
PBS recently aired a segment about the Liquid Galaxy! Just before we presented at New York Tech Meetup, we were interviewed about the Liquid Galaxy for SciTech Now, a PBS publication. The interview took place in NYU’s Skirball Center For The Performing Arts, which is where New York Tech Meetup takes place every month.
The Liquid Galaxy segment, which can be viewed above, features Ben Goldstein and me talking with complementary visuals playing at the same time.
Ben Goldstein opens the segment by talking about how the Liquid Galaxy is a panoramic system that engages your peripheral vision, and is immersive in that way.
I go on to add that the system consists of large paneled screens set up in an arch around the viewer. The Liquid Galaxy includes a touchscreen and 3D joystick that allows users can fly around the world. From there, with the use of End Point’s Content Management System, users can add images, video, kml, other overlay, to add interactivity and build custom presentations on the system. Thus far, the Liquid Galaxy has been particularly popular in real estate, museums, aquariums, research libraries, hospitality, and travel.
Ben concludes the segment by talking about how …
visionport
Scrape web content with PHP (no API? no problem)
By Piotr Hankiewicz
July 7, 2016
Introduction
There is a lot of data flowing everywhere. Not structured, not useful pieces of data moving here and there. Getting this data and structuring, processing can make it really expensive. There are companies making billions of dollars just (huh?) for scraping web content and showing in a nice form.
Another reason for doing such things can be for example, lack of an API from a source website. In this case, it’s the only way to get data that you need to process.
Today I will show you how to get web data using PHP and that it can be as easy as pie.
Just do it
There are multiple scraping scripts ready to use. I can recommend one of them: PHP Simple HTML DOM Parser. It’s extremely easy to start with and initial cost is almost nothing, it’s open sourced also.
First, download a library from an official site: https://sourceforge.net/project/showfiles.php?group_id=218559. You can use a composer version too, it’s here: https://github.com/sunra/php-simple-html-dom-parser.
Let’s say that you have downloaded this file already. It’s just a one PHP file called simple_html_dom.php. Create a new PHP file called scraper.php and include mentioned library like this:
<?php
require( …html php
WAL-based Estimates For When a Record Was Changed
By Josh Williams
July 1, 2016
I originally titled this: Inferring Record Timestamps by Analyzing PITR Streams for Transaction Commits and Cross-Referencing Tuple xmin Values. But that seemed a little long, though it does sum up the technique.
In other words, it’s a way to approximate an updated_at timestamp column for your tables when you didn’t have one in the first place.
PostgreSQL stores the timestamp of a transaction’s commit into the transaction log. If you have a hot standby server, you can see the value for the most-recently-applied transaction as the output of the pg_last_xact_replay_timestamp() function. That’s useful for estimating replication lag. But I hadn’t seen any other uses for it, at least until I came up with the hypothesis that all the available values could be extracted wholesale, and matched with the transaction ID’s stored along with every record.
If you’re on 9.5, there’s track_commit_timestamps in postgresql.conf, and combined with the pg_xact_commit_timestamp(xid) function has a similar result. But it can’t be turned on retroactively.
This can – sort of. So long as you have those transaction logs, at least. If you’re doing Point-In-Time Recovery you’re likely to at least have …
database postgres
Garbage collection in your head, or how to vacation
By Jeff Boes
June 28, 2016
Recently I returned from the longest (8 workdays) vacation I have ever taken from this job (almost 11 years). I made an interesting discovery which I’m happy to share with you:
Life goes on without you.
I spent most of the time aboard a small cruise ship touring Alaska’s Inside Passage and Glacier Bay. During almost all of that, I was out of cell phone range (and, unlike a lot of my colleagues, I don’t carry a smart phone but just a dumb $5 flip phone). With no wifi on the ship, I was completely cut off from the Internet for the longest stretch in at least the last 15 years—maybe the longest since I first got Internet at my house back in the mid–90s.
Life (on the Internet) goes on without you.
Facebook posts get posted, liked, commented on. Tweets happen, get re-tweeted. Emails are sent (for those that still use it, anyway). And life goes on.
I can’t say I came back from vacation recharged in some stupendous way, but I think I’m better off than if I’d taken a shorter vacation in an Internet-connected location, checking up on the virtual world before breakfast and bedtime every day.
So take vacations, and take meaningful ones—disconnect from work. Don’t worry about what’s piling …
travel
Free Encryption for All, In Our Time
By
Lee Azzarello
June 27, 2016
The commodification of encryption algorithms happened in the 1990s. The conversation prior to this event was rife with controversy. Here are some paraphrased FAQs from the era:
- “If encryption is free how will national security continue to be assured?”
- “If these ideas become a commodity, bad people will use them to become worse!”
or one of my favorites,
- “These ideas are dangerous because I do not understand them.”
Fortunately, history was on the side of the algorithm authors and now we have commodified encryption and can securely buy stuff from Amazon. Mission accomplished. Pats own back.
Unfortunately, everything didn’t go as planned. SSL certificate vendors built small empires on asymmetric knowledge about a complicated process. In the USA, the National Security Agency influenced vendors to inject secret backdoors into their security products. Web browsers were installed with poorly implemented SSL certificate management. Academics wrote papers about experiments to break various kinds of security, then published them.
So where does the casual web developer stand in this world? There’s too much chaos for any rational thought—besides, all I want is a secure webpage that just …
security
Postgres migration speedup with table change analysis
By Greg Sabino Mullane
June 13, 2016
One of our clients recently reached out to us for help in upgrading their Postgres database. The use of the pg_upgrade program was not an option, primarily because the client was also taking the opportunity to change from their SQL_ASCII encoding to UTF-8. (If any of your databases, gentle reader, are still SQL_ASCII, please do the same!). Naturally, we also took advantage of the lack of pg_upgrade to enable the use of data checksums, another action we highly recommend. Although there were plenty of wrinkles, and stories to be told about this migration/upgrade, I wanted to focus on one particular problem we had: how to detect if a table has changed.
We needed to know if any applications were modifying certain tables because the speed of the migration was very important. If we could assert that no changes were made, there were some shortcuts available that would greatly speed things up. Initial testing showed that the migration was taking over eight hours, a time unacceptable to the client (no worries, we eventually reduced the time to under an hour!).
Looking closer, we found that over half …
postgres
End Point CEO and NYSE Bell Ringing
By Rick Peltzman
June 10, 2016
Wall Street. Back where it all started for me some 35 years ago. Only instead of being an employee of Oppenheimer and Company, I had the experience and honor of representing End Point as one of 7 companies chosen to “ring the bell” at the New York Stock Exchange, chiming in Small Business Week for JPMorgan Chase. (They work with 4,000,000 small companies.)
The morning started early by going through security checks rivaling the airport, except I didn’t have to take my shoes off. After getting my nifty credential, we went back to the street where the president of the NYSE gave a welcoming speech, pointing out the buildings that are still standing from when Hamilton, Monroe, and Aaron Burr all started their own banks. As well as where George Washington was sworn in.
All this while also getting free coffee from the main small business honoree, Gregory’s Coffee, and picture taking from the business paparazzi!
We then went inside the storied NYSE building and made our way to the trading floor. The surroundings were immensely impressive, as the stock exchange still inhabits a huge floor with gorgeous 40 foot mid-18th century ceilings high above, holding up all sorts of 21st century …
company