Pitfall in copy table in Postgres

We countered a very wield runtime error today, after migrated some data from a legacy database.

Because there is no change on the models, so we just create the table, and copied the data from the legacy database directly.
To ensure the migration doesn’t break anything, we also wrote some migration test to verify the data integrality. And we found all tests are passed.

Everything looks perfect until the app goes live. We found the app crashes occasionally when we’re trying to create new data record in the system.
Sometimes it works fine, but sometimes we got an error says “duplicate key value violates unique constraint ‘xxxxx_pkey’”.

It is wield because we’re really confident about our unit test and migration test. The problem must not related to migration and logic.

After some manually tests, we found we also got error when create entry with raw SQL Insert Query. So it seems to be a postgres issue.
And the problem is caused because of the primary key, which is a auto-generated id.

Postgres introduces the Sequence to generate the auto-increase index. Sequence remember the last index number it generated, and calculate the new index by +1.
During the data migration, we copy the data rows from another table to a new table. To keep the relationship between records, we also copied the primary key in the row. As a result, although we had inserted a number of records into the table, but the sequence binding to the primary key doesn’t been updated.

For example, we have inserted the following 3 entries:

  • {id: 1, name: ‘Jack’}
  • {id: 2, name: ‘Rose’}
  • {id: 4, name: ‘Hook’}

But because the id is also inserted, so the sequence is still at 1, so when we execute the following SQL: `

Insert entry
1
2
3
4
INSERT INTO users (name)
VALUES ('Robinhood');

And sequence will generate 1 as the id, which is conflicted with entry {id: 1, name: 'Jack'}, and then database yield exception “duplicated key”.
But usually the id is not continues because of deletion of the records, which looks like there are “holes” in the records. So our app can successfully insert entry successfully when new entry falls into the “hole”.

To solve this problem, we need to also update the sequences in the table, including the primary sequence. Postgres allow Sequence to be updated by using ALTER SEQUENCE command, and we can set the sequence to a big enough integer:

Update Sequence
1
2
3
ALTER SEQUENCE users_id_seq RESTART 10000

A smarter way is to query the whole table to find out the maximum id number, and set the sequence to that number + 1.

Ruby Class Inheritance II: Differences between inheritance and mixin

Guys familiar with Rails are very likely used to the following code, and will not be surprised by it:

ActiveRecord
1
2
3
4
5
6
class User < ActiveRecord::Base
end
first_user = User.find(0)

But actually the code is not as simple as it looks like, especially for the ones from Java or C# world.
In this piece of code, we can figure out that the class User inherited the method find from its parent class ActiveRecord::Base(If you are doubt or interested in how it works, you can check this post Ruby Class Inheritance).

If you write the following code, it should works fine:

Simple Class
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
class Base
def self.foo
bar_result = new.bar
"foo #{bar_result}"
end
def bar
'bar'
end
end
class Derived < Base
end
Base.new.bar.should == 'bar'
Derived.new.bar.should == 'bar'
Base.foo.should == "foo bar"
Derived.foo.should == "foo bar"

In Ruby’s world, most of the time you can replace a inheritance with a module mixin. So we try to refactor the code as following:

Exract to Module
1
2
3
4
5
6
7
8
9
10
11
12
13
14
module Base
def self.foo
bar_result = new.bar
"foo #{bar_result}"
end
def bar
'bar'
end
end
class Derived
include Base
end

If we run the tests again, the 2nd test will fail:

Test
1
2
3
4
Dervied.new.bar.should == 'bar' # Passed
Dervied.foo.should == 'foo bar' # Failed

The reason of the test failure is that the method ‘foo’ is not defined!
So it is interesting, if we inherits the class, the class method of base class will be available on the subclass; but if we include a module, the class methods on the module will be available on the host class!

As we discussed before(Ruby Class Inheritance), the module mixed-in is equivalent to include insert a anonymous class with module’s instance methods into the ancestor chain of child class.

So is there any way to make all tests passed with module approach? The answer is yes absolutely but we need some tricky thing to make it happen:

Exract to Module ver 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
module Base
module ClassMethods
def foo
bar_result = new.bar
"foo #{bar_result}"
end
end
def bar
'bar'
end
private
def self.included(mod)
mode.extend ClassMethods
end
end
class Derived
include Base
end
Dervied.new.bar.should == 'bar' # Passed
Dervied.foo.should == 'foo bar' # Passed

Pitfall in fs.watch: fs.watch fails when switch from TextMate to RubyMine

I’m writing a cake script that helps me to build the growlStyle bundle.
And I wish to my script can watch the change of the source file, and rebuild when file changed.
So I wrote the code as following:

Watching code change
1
2
3
4
5
files = fs.readdirSync getLocalPath('source')
for file in files
fs.watch file, ->
console.log "File changed, rebuilding..."
build()

The code works when I edits the code with TextMate, but fails when I uses RubyMine!

Super weird!

After half an hour debugging, I found the following interesting phenomena:

  • Given I’m using TextMate
    When I changed the file 1st time
    Then a ‘change’ event is captured
    When I changed the file 2nd time
    Then a ‘change’ event is captured
    When I changed the file 3rd time
    Then a ‘change’ event is captured

  • Given I’m using RubyMine
    When I change the file 1st time
    Then a ‘rename’ event is captured
    When I changed the file 2nd time
    Then no event is captured
    When I changed the file 3rd time
    Then no event is captured

From the result, we can easily find out that the script fails is because “change” event is not triggered as expected when using RubyMine.
And the reason of RubyMine’s “wried” behavior might be that RubyMine what to keep the file integrity so they “write” the file in an atomic way as following:

  1. RubyMine write the file content to a temp file
  2. RubyMine remove the original file
  3. RubyMine rename the temp file to original file

This workflow ensures that the content is fully written or not written. So in a word, RubyMine does not actually write the file, it actually replace the original file with another one, and the original one is removed or stored to some special location.

And on the other hand, according to Node.js document of fs.watch, node uses kqueue on Mac to implement this behavior.
And according to kqueue document, it uses file descriptor as identifier, and file descriptor is bound to the file itself rather than its path. So when the file is renamed, we will keep to track the file with new name. That’s why we lost the status of the file after the first ‘rename’ event.
And in our case, we actually wish to identify the file by file path rather than by ‘file descriptor’.

To solve this issue, we have 2 potential solutions:

  1. Also apply fs.watch to the directory that holds the source file besides of the source file itself.
    When the file is directly updated as TextMate does, the watcher on the file will raise the “change” event.
    When the file is atomically updated as RubyMine does, the watcher on the directory will raise 2 “rename” events.
    So theoretically, we could track the change of the file no matter how it is updated.

  2. Use the old fashioned fs.watchFile function, which tracks the change the with fs.stat.
    Comparing to fs.watch, fs.watchFile is less efficient because its polling mechanism, but it does track the file with file name rather than file descriptor. So it won’t be charmed by the fancy atomic writing.

Obviously, the 1st solution looks better than the 2nd one, because its uses the event rather than old-fashioned polling. Even document of fs.watchFile also says that try to use fs.watch instead of fs.watchFile when possible.

But actually it is kind of painful to write such code, since ‘rename’ event on the directory is not only triggered by the file update, it also can be triggered by adding file and removing file.

And the ‘rename’ event will be triggered twice when updating the file. Obviously we cannot rebuild the code when the first ‘rename’ event fired, or the build might fail because of the absence of the file. And we will trigger the build twice in a really short period of time.

So in fact, to solve our problem, the polling fs.watchFile is more useful, its old-fashion protected itself being charmed by the ‘fancy’ atomic file writing.

So finally, we got the following code:

fs.watchFile
1
2
3
4
5
6
7
8
9
10
11
runInWatch = (options, task) ->
action(options) unless options.watch
console.info "INFO: Watching..."
files = fs.readdirSync getLocalPath('source')
console.log '"Tracking files:'
for file in files
console.log "#{file}"
fs.watchFile getLocalPath('source', file), (current, previous) ->
unless current.mtime == previous.mtime
console.log "#{file} Changed..."
task(options)

HINT: Be careful about the differens of fs.watch and fs.watchFile:

  • The meaning of filename parameter
    The filename parameter of fs.watch is path sensitive, which accept ‘source.jade’ or ‘/path/to/source.jade’ The filename parameter of fs.watchFile isn’t path sensitive, which only accept ‘/path/to/source.jade’
  • Callback is invocation condition
    fs.watch invokes callback when the file is renamed or changed fs.watchFile invokes callback when the file is accessed, including write and read.
    So you need to compare the mtime of the fstat, file is changed when mtime changed.
  • Response time
    fs.watch uses event, which captures the “change” almost in realtime. fs.watchFile uses ‘polling’, which might differed for a period of time. By default, the maximum could be 5s.