Better way to execute sql files in PostgreSQL

My previous post  is kind of dumb. It’s simple, but dumb. First of all, the /o command will only write the output of a particular query into file. It won’t write any error or any other information that pop out during the execution. The last time I ran that, it failed and I don’t know what happened or where it stop.

So, a better way to do it is by running psql with -f option.


A little explanation here:

  • -a tells the psql to print out all the input commands to Standard Output. It basically print out all the queries inside your SQL file. This is handy so that you know what went wrong and where did it stop.
  • -f FILENAME tells the psql to run the given file
  • > LOGFILENAM is the basic UNIX command to redirect the standard output to a file. In other words, it says that whatever output that you are supposed to see in psql will be written into a file instead.
  • 2>&1 is very important because it asks the system to also print any errors into the standard output. Since we’ve redirected that standard output into a file, it means that any error will be logged into that file also.

For more information, checkout the psql manual and also the Bash redirection.


PostgreSQL Input and Output

NOTE: this is one way of doing it. Find the better solution here.

Let’s imagine a scenario where you have 10 different SQL queries to run and they will take few hours each. You don’t want to run it in the peak hours because it will slow down your entire server. You don’t want to stay back late either. So you decided to put all the queries in a text file and run them with your PostgreSQL client at the end of the day and you go for a bottle of beer at the nearest club and call it a day.

Here is how you do it in PostgreSQL. First, fire up your PostgreSQL client such as

psql -h your-host-name -U your-username -d your-dbname

and then, you run the \i command. Well, “i” is obviously stands for “input”.

\i /path/to/your/sql/file

VOILA, the sql are running. And then you pack your stuffs and call up your friends to meet you up at that bar you talked about earlier. But wait a minute. What if something went wrong? what if my console suddenly close itself? I won’t know if all the queries ran successfully or not. Don’t worry, you say. We can stream the output into a log file. And when we come back tomorrow morning, we will know where to check. And so, you pressed CTRL+C to cancel your previous job and waited a while until it finish cancelling it and then you start stomping some other commands:

\o /path/to/your/log/file
\i /path/to/your/sql/file

And voila!! you can really go to that pub where your friends are waiting now. Have a good evening, folks!!


The theory of Object Oriented Programming vs its application.

Speaking of Object Orientated Programming (OOP), I remember how absurd it was to learn for the first time. In the university, they first teach you a bunch of concepts, ideas and terminologies. After that, you are on your own.

Being a totally newbie in the programming at the time, and a very horrible English speaker in a All English Speaking programming class, I was so proud to be able to use For loops in my C++ class. The same things goes to how proud I was to understand theoretically all the terms that the lecturer explained. I was so proud to get it right about the hierarchy of the daily product family. And the similarity between how your father will left you some fortunes when he died and when a child class inherit a mother class. Theoretically, I was an expert.

When it’s time to apply the theory into practice, everybody was left amazed by why you don’t create a Milk super class and have cheese, butter, and yoghurt extends the Mother Milk just like the Hierarchy diagram explained. You don’t create any classes for them, but instead, you created a class called <<ProgramName>> and store the daily product in the database.

The OOP era ended for me pretty quickly because I was introduced PHP and suddenly I’m happy with my life without thinking much of making the codes look more like what it does in the real world. Carrying the OOP theory in the very deep side of my backpack, I fast forward 5 years, surviving finely. Throughout my working history, I’ve never been in a company that really embrace OOP.

However, things is going to change. The first time ever in the real world I was in a job interview where the people actually care about how much I know about OOP. Digging back into my memory, I managed to answer some of the questions correctly and miss some. I have to be honest with them that I have no experience with OOP at all. But they offered me a job and now I’m happy that I can finally create the Milk class that I didn’t create 5 years ago.

For the moment, OOP is still a very vague concept for me. But I’m looking forward to apply those theories into real practice this time.

Web Tools

Back to Yii

I finally solved the problem with the Selenium testing. The problem wasn’t because of the “Failed opening ‘SiteTest: Firefox.php’” at all. It was just a warning message. The real problem is the curl extension in PHP. I thought I already enabled it, but not when the CLI version is not using the same ini file. So, after I enable the curl in the CLI version of the ini file, it works fine.

I’m still wondering though, how do I make the CLI version use the same ini as the web version.

Web Tools

I wish that I’m done with Yii

No offence Yii, but I’m going to move on. First, your lack of examples in your user manual – where the the manual itself is mediocre to start with – is tolerable. But, I started to wonder if people really use you. For a tool like you, you should be proud if people talk a lot about you, especially best if they talks about the problems they are having with you. At least by then, there will be lots of devoted worshipper that are willing to find the solutions for every problem that people talk about.

I just can’t do it any more. I’m stuck here with a problem that nobody (that I can reach) knows. It all started with one very promising book “Agile Web Application Sevelopment with Yii 1.1 and PHP5” which is really good to start with. But it only take 3 chapters for you to left me bleeding at a corn field near my uncle Jimmy’s house. I’m still bleeding by the way, just so you know. I just can’t figure out how to make the Selenium works without giving me that “Failed opening ‘SiteTest: Firefox.php'”error. I can’t proceed without Selenium, the whole book is about Test-driven approach. And if the testing tools doesn’t work, how to proceed?

There are still plenty of fishes in the sea. Though you are the chosen one and I have to come back to you one day, I think I’ll just forget about you until then.


The Ruby, C++, PHP and C++

I’m taking the Stanford Online Course called the Design and Analysis of Algorithm 1. So far so good, and then the Programming Exercise 4 hit me hard.

First of all, we are allowed to use any programming language. They only want the final result, regardless of how you process them. So, at week 4, we are supposed to read through a file that represent a Graph with thousands of nodes. And we are asked to find the five largest strongly connected components or SCC.

So, I started with Ruby, which is my language of choice for this course. The algorithm that the professor teaches use recursive. So, after finishing the program and running it trough few test cases which seems to work properly, I run it on the actual file (the thousands of nodes graph). It crashed in the first few seconds saying that “stack level Too deep”. Apparently the recursive is too deep for my Ruby to process. I tried to find how to increase the stack level allowed, but it’s either I don’t understand what they are talking about or the solution is for Mac or Linux. I’m working on a Windows 7.

So, I think maybe I should do it on C++. One reason is because it’s fast, and it’s difficult (so I can learn to be a better programmer, not just a guy who get pampered by modern programming language). Then, if you know C++ and also know Ruby or PHP, you can see that C++ is so much different. So much more difficult. PHP pretty much take care of many things behind the screen for you,things like dynamic array or array with random index name (hash in Ruby, associative array in PHP), or passing the array around like tossing pancake from the pan to the plate. They are not that simple in C++. Array is just a pointer with the size. Passing array is complicated, not to mention about multilevel arrays. It doesn’t have hash or associative array.

I have to admit that I’m too pampered by modern (high level) programming language and it’s not the C++ fault, and creating your own function to search an array or to enable dynamic index naming is just too much for me. So, I go back to PHP and try out it’s maximum stack level allowed. First try, it return error on the 100th recursive. But don’t worry, it turned out that it’s just the limitation that X-Debug set. I remove X-Debug and it can run for as deep as your computer memory can supply (which is a lot and a good news).

I immediately converted the Ruby code into PHP code and voilà, it works on the test cases. Working on PHP is like returning home. It feels natural for me as if I’m talking in my mother tongue. However, When I run it on the real graph, I hit a lot of memory limit error. I increase it few times, from 128M to 256M, to 1024M to 2048M. It consumed more than 1GB or RAM to process 5 millions connections of 800 thousand of nodes. And it run forever. And ever. I never get the result back yet. And I’m suspecting an infinite loop, but can’t really find the proof of it. Therefore, I have to conclude that it’s the PHP problem. It’s simply to slow.

So, my only hope is C++ now, which I’m not good at, and I have to build many functions from scratch. Well, probably I can find some library online. But, I don’t know. It feels like I’m forced to speak Spanish when I only know a few words like “la nina come pan” and “el niño bebemos leche”. And I’m literally not sure is it bebemos or beben? Oh, I’m screwed.


Rant: WordPress App on Android

So, I have a challenge blog, where I challenged myself to draw one eye a day and post it there.

I normally use my Android phone (Xperia Mini Pro) to post the picture of the eye to WordPress. Therefore, I have that little WordPress app installed.

Here is the story. One of my pen is dying, the ink is almost gone. So, I’ve decided to draw an eye using it and write a long dramatic story about it. When I am done writing it, and done trying not to drop my tears, I decided to add some more pictures. So here is what I did,

  • I press on the little camera button on the bottom right (inside edit mode).
  • I choose “Select a photo from gallery”
  • Try to find the picture of my pen which I thought I’ve taken few days ago, but couldn’t find.
  • Still in the gallery browser, I saw the camera button on the top right, which I know will lead me to the camera, so I pressed it.
  • Took some pictures of my dying pen.
  • Oops, I can’t remember what I did after that. I try to simulate it but didn’t come out the way it was.
Anyway, the conclusion is, I ended up in the home screen and the story that I’ve written is no where to be found. So, here is my rants:
  • Android offer too many things, which you think you can rely on them but, meh… (i.e : multitasking, read the story above).
  • Android allow maximum customization. But without customization, the phone is a shit.
  • Bugs everywhere. Well, to fix those, you either wait for the updates (which takes forever depend on what brand your phone is), or root it and install custom rom or install some other apps to replace the original shitty and buggy apps (which (most of the apps I’ve tried) will leads to more bugs).
Well, this is it. It’s time to try and rewrite my sad story about my dying pen again.
Web Tools

jQuery: It’s Easy to Create Websites Nowadays

I used to be proud of being a programmer. But now, given that things are a lot easier with jQuery, I feels that I’m not more than a Web Designer. Not to say that web designers are not cool, they are superb in their graphical creativity, which I’m lack of.

Friend: So, what exactly do you do at work?

Me: I create functionality for websites.

Friend: is it hard?

Me: not at all. In fact, I can teach you some cool tricks. You can just put something like $(“#banner”).slideDown(); and the banner will slide down.

But from the other point of view, jQuery has bring me to a much a higher level excitement. From a web developer that only care of back-end processes (that I can be proud of, but nobody really care) to somebody that is willing to improve website experience (that more people will “WOW”).

Web Fun

Curve made of lines

I was playing with WebGL the other day and was trying to create some cool and beautiful random triangles after watching the Doodling in Math Class video on youtube. Well, I did create a function to randomly create one triangle. Yes, only one triangle. Not sure how to write the algorithm to make my computer draw triangles as good as the lady in the video did.

And then, I figured out that, WebGL is not a correct term for what I’ve done. Basically, I didn’t actually use WebGL. WebGL is more powerful and support 3D drawing. What I was using was only the native build in HTML5 Canvas 2D library (not sure what it’s really called).

And then, I started to remember what we used to do during math class when I was younger. We draw lines on the edge of the pages and it become a nicely drawn decorative curve. So, using my newly learned drawing skill, I started to draw lines. And here is what I’ve got, a randomly generated, colourful, on the edge curve. Feel free to play around it and hope you enjoy it.