Though i like to think that i am quite adept at javascript understanding sometimes i forget the quirks it has.
Just today i was programming and wanted to call a simple function editPost, then when testing what i wrote i get an error saying “editPost is not function”, damn i said to myself thinking that i mistyped somewhere, but as it turned out i used editPost as a global variable in another function, there i forgot to add the ‘var’ and it became global. I think its a common mistake and sometimes its hard to maintain such code especially when working on bigger projects witch has lots of javascript lines. Sure there are methods to solve scope problems like namespacing, using OOP programming style in general, but you must apply these methods early in development in order to escape the mess later. Here’s a simple example of my problem that i had:

function getPost(){
    editPost = 'not used';  //no 'var' means the variable is global scope; this type of variable is called implied global
    ...
    return post;
}

function editPost(id){
    ...
    //browser will shout an error, that "editPost is not a function
    ...
}

There is a good book about these kind of quirks that Javascript has i recommend you reading it if you are interested, its called “JavaScript: The Good Parts” written by Douglas Crockford. It’s a small book, but informative and explains javascripts buggy parts like:

'' == '0'          // false
0 == ''            // true
0 == '0'           // true

false == 'false'   // false
false == '0'       // true

false == undefined // false
false == null      // false
null == undefined  // true

' \t\r\n ' == 0    // true

and many other awful and beautiful features of javascript insight-fully.

Recently i had to work with big database, were really big tables like 200gb were not partitioned, and query performance from those tables was getting really slow.  So it was decided to partition them in order to speed up database performance.

So how exactly do you do this, how do you partition the table?

Well i won’t get deep into specifics, but  basically saying you create or presumably  already have a master table from witch one you derive other tables,  i’m talking about table inheritance here, then create a trigger on the master table witch redirects data inserts into the right derived table and thus partitioning is born. To be more clear here are some examples.

Lets say that we already have a master table ‘Users’ that contains  (id, user_id, login, password, desc)  columns. So to derive-create table that inherits all master tables description we do this:

CREATE TABLE users_by_id.user_1 (
	PRIMARY KEY (id, user_id), CHECK ( user_id = '1' )
) INHERITS (public.users);
CREATE INDEX user_1_index ON users_by_id.user_1 (user_id);

So here we created a child table that inherits  from its master table ‘users’, the CHECK is used by postgresql to determinate from witch table to SELECT, UPDATE… data and to unsure that data written to this table is limited to user  who’s id is 1  . Later we create an INDEX on ‘user_id’ to increase fields usage speed. Note that ‘public’ and ‘users_by_id’ are postgresql schema’s,  i use them to keep tables separate for better readability.

Most likely now after carefully reading what i wrote and in your mind after applying it to an actual application  you may have one or few questions.

The questions i had were these:

1. What if i have many users and new users are created constantly is there a way to automate the process of creating child tables and indexes instead of doing it manually?

2. How do i direct database INSERT’s into the right tables?

The answer to these two main questions i had is quite simple. You create a TRIGGER function that on data INSERT dynamically checks if child table is created for user. If  it is – you insert data into user table, if not – you create the child table for the user and after that you insert data into that table. For better understanding check code examples.

Code examples here:

CREATE OR REPLACE FUNCTION users_data_insert_trigger()
  RETURNS "trigger" AS
$BODY$

DECLARE

data record;

BEGIN

EXECUTE  'SELECT tablename from pg_tables where tablename=''user_'||NEW.user_id||''' AND schemaname=''users_by_id''' INTO data;
IF data.tablename is null THEN

    EXECUTE  'CREATE TABLE users_by_id.coordinates_'||NEW.user_id||' (
    PRIMARY KEY (id, user_id), CHECK ( user_id = '''||NEW.user_id||''' )
    ) INHERITS (public.users);
    CREATE INDEX user_'||NEW.user_id||'_index ON users_by_id.user_'||NEW.user_id||' (user_id);';

    EXECUTE  'INSERT INTO users_by_id.user_'||NEW.user_id||' VALUES('||quote_literal(NEW.id)||','||quote_literal(NEW.user_id)||', '||quote_literal(NEW.login)||', '||quote_literal(NEW.password)||', '||quote_literal(NEW.desc)||' );';

ELSEIF data.tablename is not null THEN
    EXECUTE  'INSERT INTO users_by_id.user_'||NEW.user_id||' VALUES('||quote_literal(NEW.id)||','||quote_literal(NEW.user_id)||', '||quote_literal(NEW.login)||', '||quote_literal(NEW.password)||', '||quote_literal(NEW.desc)||' );';
ELSE

    RAISE NOTICE 'Insert trigger ERROR!';
    RAISE EXCEPTION 'Insert trigger ERROR!';
END IF;

RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Well that wraps it up, i think the code is self explanatory and there is no need to add something.

Blog Intro

Yet another blog, yet another blog in the wast internet sea, yet another blog knocking in your internet browsers doors…

Yes..,  like a woman giving birth to a child, this blog is born, like a child stepping his firsts steps, let me say “Hello World”, as a child learning new things, finding interests.., this blog as well will focus, on my, that is Deividas Gustys interests, hobbies. Before you start judging me for these cheese “child, mother metaphors” let me say, that this blog mostly will be about  IT related  stuff, that is: programming, hardware, software, work, technologies, etc…, and it will be written from my perspective, experience , thought pattern, so if you don’t agree with my opinion or have something to say please comment or write me email. For me this “blog” is exactly what it is, a “web log”, its purpose is to show me what i learned, experienced and to remind me what i still new to do.., as for you imho “readers” , i hope that you find something interesting and worth time reading.

Uhhh, i almost forgot to introduce myself.

Hello, my name is Deivid’as Gustys, i was born in Lithuania one of the baltic countries, Deividas as you might guest in English is ‘David’ so please feel free to call me like that. Currently i am a programmer at company “UAB Ruptela”, its not a very big company, but its expanding very fast, maybe the reason is that it provides vehicle tracking and monitoring services quite new and salable product, thus my work is to program maps, be it google, teleatlas, other. Our system is web based, so that makes me a Web programmer/developer. My skill-set includes php, pg/sql’s, javascript, python, C#, other.  Well that is it , i kept it brief, if you like to hear more about me – ask.