Remove duplicate rows from table in PostgreSQL

I was recently looking for a quick way to delete duplicate rows from large table (around half a billion rows) in PostgreSQL. In my case the table didn’t have a primary key – it was the result of importing a bunch of CSV files using the copy command. After some googling it turned out that the quickest way is also the easiest to understand. So here is how its done:

-- Create a new table with only unique rows:
create table temp as
    select distinct on (lon,lat,label) * from buildings;

-- Check the before and after counts:
select count(*) as before from buildings;
select count(*) as after from temp;

-- Now drop the original table
drop table buildings;
-- Rename the temporary one.
alter table temp rename to buildings;

Essentially, a new table is created with the duplicated rows removed then the original table is dropped and the new table is renamed. Keep in mind that this method doesn’t specify which row is kept so be careful if that is important to you.

In this case I only needed to have unique values in columns lon, lat and label. You can replace those with what fits you case. If you want to do a full table compare (i.e. your imported CSV files have full duplicated rows) you can use * instead like this:

create table temp as
    select distinct * from buildings;

PHP 7.4 Preloading

A cool new feature coming up with PHP 7.4 will give a nice speed boost while solving one of the problems that hold PHP back. As explained in the internals RFC we’ll be able to specify a single PHP file in php.ini (under the opcache.preload directive) which will take care of loading all other PHP files that contain shared code and keeping them in memory before any application code is executed. The preloader PHP script can either use the include command the traditional way or the new function opcache_compile_file to force scripts to be compiled and kept in memory. It will also resolve any dependencies and load them accordingly which wasn’t possible before using the opcache. The shared code will then be available to any PHP scripts running on the same instance without having to do any include/require or pass through composer’s autoloader.

Performance gains from this feature will depend mainly on how much actual work is done during each request as opposed to bootstrapping the framework. Requests will short run-time should see the highest boost in performance.

While this feature will benefit anyone running their applications isolated it will probably not be available for shared hosting scenarios where the same PHP instance is used for multiple sites.

PHP 7.3 is out

Can be downloaded here http://php.net/releases/7_3_0.php

I personally would stick to 7.2 for now at least until the first point releases of 7.3 are out just to be on the safe side.

To install on Ubuntu 18.04:

sudo add-apt-repository ppa:ondrej/php
sudo apt-get update

deb http://ppa.launchpad.net/ondrej/php/ubuntu bionic main 
deb-src http://ppa.launchpad.net/ondrej/php/ubuntu bionic main

sudo apt-get install php7.3

Xdebug is also available but its currently under beta. Since you won’t be using that in production it doesn’t hurt to try.

Hiking in Amiantos, Cyprus [with photos]

On 19th March went on a hike to Amiantos area in Troodos. The trail started near the village of Kato Amiantos walked along the river reached the flooded entrance of the old asbestos mine and then climbed up the hill over the mine and then descended back to the river and followed the same path back. A total of around 12km of walking, and climbing up and down; this was probably the hardest hike I’ve done in my life and most certainly one of the hardest trails in Cyprus (I am trying to find a harder one just for the challenge).

Walking along the river side was easy; we had to cross the river a few times but it was not that hard, the challenge started when we reached the lake, at that point we took the wrong path and ended up having to climb up the hill which was quite steep. Once at the top we slowly started walking down when we realized we spent 5 hours in what was supposed to take us 3-4 hours. At that point we decided to take a different path so that we could make it off the mountain before sundown. The new path was quite hard, we were essentially climbing down the dry path rainwater created. Because the particular mountain peak is mostly made of sand, the water eroded a steep corridor around a metre wide and around 2 metres deep. We needed to be careful not to touch the walls cause the path would collapse and at the same time had to climb down drops of about 2-3m at a time. It took us another hour to get out of that and then walk along the river back to our starting point.

Was it fun? yes! will I do it again? definitely!

Amazon Drive synced folder

Amazon Drive newest client supports folder syncing

Good news for data hoarders, Amazon Cloud Drive client popped up with an update today and to my surprise after installing what I assumed it was just a bug fix it included a highly requested feature – Folder Synchronization. A feature available in all other similar services such as Dropbox, Microsoft’s Onedrive and Google Drive. Even though Amazon Drive excels in one feature – offering unlimited space – until now it operated more as a backup service where you had to choose when, what and where to upload your files. Note that you could have sync functionality with Amazon Drive even before using third-party clients but it’s always nicer to have it built-in the official one. With the new version it automatically syncs any files you have in Amazon Drive’s online root folder and it allows you to pick any sub folders that are already backed up to be synced with your local Amazon Drive folder. In the meantime any new sub folders you create locally are automatically kept in sync.

Unfortunately syncing doesn’t have a placeholder functionality that OneDrive used to have and newer versions of DropBox implemented but at least they kept the option to back up files without having them synced.

Lefkara village photos

Lefkara is considered one of the most picturesque villages in Cyprus, located in the hills between Limassol and Larnaca it’s a must visit for anyone spending time in Cyprus. It’s a fairly large village with a lot of coffee shops and traditional taverns usually quite busy especially during weekends. Ideally try parking as soon as you find a spot cause streets are narrow and road traffic makes it driving even harder.

Took these photos back in September during an unplanned visit.

Larnaca night photography

Shot these in Larnaca around the Castle area and the recently renovated Piale Pasha walkway. It was quite busy night but managed remove people walking by using a long exposure on tripod. Thankfully there was very little humidity otherwise the lights would have been overblown.

 

JPHP – Compile PHP directly to JVM (Java) Bytecode

I stumbled upon the JPHP project today which adds yet another direction to the PHP world. JPHP is an one man project by Dmitriy Zayceff who’s been working on it for the past 2 years and I must admit he must have spent a lot of time on it. Here’s some key points:

  • Compile PHP code into Java Virtual Machine bytecode (into JAVa’s .class files)
  • Use the standard PHP function library including some extensions.
  • Can use any JAVA class through PHP code (you need to write a wrapper for it but it works)
  • Create cross platform GUI or CLI applications
  • Develop Android apps in PHP
  • It executes faster than the current PHP branch and even faster than PHP7.
  • Allows for both stateful and stateless execution when used as a Web scripting language (i.e. you can either choose to execute as a long-running process with shared memory or reset with every request like standard PHP.
  • Can do just in time compilation of PHP files.

Project’s Github page.

Benchmarks can be found here. Page is in Japanese but all you need to look at is the two tables. The first shows the time taken for the 1st run of the benchmarks and the next run where the code is already pre-compiled and JPHP wins.

Chrome DevTools – Quickly use currently selected element in console

I am sure like me, there’s been many times when using Chrome Developer Tools you selected an DOM element in the Elements panel and wished you could easily get a reference for it so you can use it in the console. It turns out it was possible and super-easy all along by using the $0 magic variable in Console. $0 automatically holds a reference to the currently selected element in the Elements panel. As a bonus the using $_ instead you get access to the last evaluated expression whether its an object or a scalar. Here’s how it looks:

$0 and $_ in Chrome Developer Tools

$0 and $_ in Chrome Developer Tools

 

 

Source: DevTips Daily

What motivates developers – and why I quit my old job

About a year ago I left my 9 to 5 job to pursue my own projects after way too many years stuck working at the same position for the same company. Today I stumbled upon this article that made me realize the true reasons why I quit, you can read it here. It turns out that all 3 points expressed in the article were what I was missing from my experience at the particular company.

  • Autonomy – micromanagement was the root of all evil. If as a manager you keep treating your employees as kindergarten children in time they will eventually start behaving like kindergarten children. Ultimately this killed any signs of team spirit and destroyed employee relations.
  • Mastery – the company culture imposed a slow pace, offered almost no training options (at least not in anything relevant to today’s standards), and experimentation with new technologies was treated by the management as toying around rather than doing useful work – essentially there was no long-term vision.
  • Purpose – developers are ‘creators’ in personality, they are people who gain satisfaction by knowing that their creations are being used. Being tasked to develop things that have no consequence, do not help anyone, are simply being funded as an excuse for the existence of the company in the eyes of its investors and you know beforehand that they will be just filed away as soon as they are completed. This alone is the most demotivating thing anyone can do to a software developer.

The worst part is that once you get comfortable in this culture it takes a long time to realize what is going wrong and how much it affects your way of thinking and your behavior in or outside the office. Like the article says, money was certainly not the issue. I was earning a decent salary, it could have been better but I guess it can always be better so I don’t count this as a factor. If you happen to be reading this and you are stuck in a situation like the one I  described I suggest you leave and never look back. New, better opportunities will come as soon as you exit the door.