how many times did you (or somebody in your environment) did something like this? update (or delete) without proper “where"?

it happened at least couple of times to me.

of course – using transactions solved the problem. but – on the other hand – if i can't trust myself to add proper where, how can i trust myself to add “begin"?

so, is there no hope?

are we doomed to always repeat the same mistake, and spend uncountable hours on recovering from damaged tables?

fortunatelly there is a help.
Continue reading update account set password = ‘new_password'; oops?!

some time ago skype released some tools for postgresql that they developed.

one of the released tools is londiste – simple replication system. it is master to multiple slaves, without cascading.

the problem with skype projects is that documentation is not as full as we expect from pg-related project. luckily dim used the londiste, and was kind enough to write some howto/manual for this project.

all i can say is: take a look at it. it looks as a very simple to use, without all features of slony, but definitely worth to take a look at.

i was informed that jan wieck wrote another function which does cumulative sum – using cursors and srf's to get the same result.

i checked it, and here are the results.
Continue reading cumulative sum in sql – howto part2

i found lately quite interesting issue. how to calculate cumulative_sum across some dataset.

if you will search for cumulative sum, you will find some answers. most of them revolve around using subselect or join.

so, how to calculate it?
Continue reading cumulative sum in sql – howto

some time ago i wrote a piece on values(),() in postgresql 8.2, saying that multi-row inserts are very fast.

some people said that my benchmark is pretty useless as i didn't take into consideration transactions.

others asked me to translate the text to english.

so i decided to redo the test, with more test scenarios, and write it up in english. this is the summary.

at first what i used, what i tested and how.

i used a linux machine, with these things inside:

• cpu: AMD Sempron(tm) Processor 2600+ (1.6ghz)
• memory: 3gb
• discs: 4 250gb hitachi sata discs (only one was used)

i tried to make the machine as predictable as possible, thus i stopped all daemons which were not neccessary. full ps auxwwf output is provided in results tar file. basically – there is postgresql, sshd, openvpn, dhclient and some gettys. no cron, atd, smtpd, httpd or anything like this.

then i wrote a small program which generated test files. i do not distribute test files themselves, as in total they use nearly 70gb!

then i wrote another small program – which basically ran all of the tests (3 times to get an average).

full set of results is downloadable as tar file, which contains 10598 files (tar file is 350k, unpacked directory takes 42megs).

one very important notice. all tests that i have performed inserted random data to table of this structure:

• id int4 primary key,
• some_text text,
• bool_1 bool,
• bool_2 bool,
• some_date date,
• some_timestamp timestamp

so results (especially “break-points" where there is no further gain) will be different when inserting to another tables. the only point of this benchmark is to show which approach can give which results. and what's really worth the trouble 🙂

Continue reading how to insert data to database – as fast as possible

polish disclaimer begin;

w celu trenowania języka, oraz by poszerzyć teoretyczny zasięg bloga będę teraz starał się pisać po anglijsku. wytykanie błędów mile widziane.

polish disclaimer commit;

ok, so you're trying to build something that needs random-text record identifiers. perhaps a new tinyurl-kind-of-service.

and you are thinking about a way to implement random text generation in a way that:

• there will be no direct information which record was added just after given one (knowing it's textual id). i mean – the text id's cannot be sequential like a, b, c, d, …
• the code should be as small as possible. we do not want to start with 40-characters behemoths just to make sure no-one can know which ones were before another.
• it should be as simple as possible.

requirements 1 and 2 are almost contrary, but we can manage.

first – let's assume we will generate random id's out of these characters: a-z, A-Z, 0-9. this gives us 62 different characters.

now. let's do it that way:

1. assume current_length to be 1
2. generate random string of length = current_length
3. if this id is already taken, increment current_length and repear from step 2
4. voila. new id generated.

it matches both first and second requirement from list.

as for simplicity.

let's try to implement:

first, let's create a test table with unique constraint on text-id field (i keep numerical id “just in case"):

CREATE TABLE test_table (
id          BIGSERIAL,
random_code TEXT  NOT NULL DEFAULT '',
something   TEXT ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_test_table_random_code ON test_table (random_code);

now, let's create function for random string generation:

CREATE OR REPLACE FUNCTION get_random_string(string_length INT4)
RETURNS TEXT
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
possible_chars TEXT = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
output TEXT = '';
i INT4;
pos INT4;
BEGIN
FOR i IN 1..string_length LOOP
pos := 1 + CAST( random() * ( LENGTH(possible_chars) - 1) AS INT4 );
output := output || substr(possible_chars, pos, 1);
END LOOP;
RETURN output;
END;
$BODY$;

code looks simply i guess. in case it doesn't – ask question in comments area.

now. the master-code, which is a trigger:

CREATE OR REPLACE FUNCTION trg_test_table_get_random_code()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
temprec RECORD;
new_string TEXT;
BEGIN
LOOP
new_string := get_random_string(string_length);
SELECT COUNT(*) INTO temprec FROM test_table WHERE random_code = new_string;
IF temprec.count = 0 THEN
NEW.random_code := new_string;
EXIT;
END IF;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
RETURN NEW;
END;
$BODY$;
CREATE TRIGGER trg_test_table_get_random_code BEFORE INSERT ON test_table FOR EACH ROW EXECUTE PROCEDURE trg_test_table_get_random_code();

basically this trigger is implementation of algorithm i wrote couple of lines above.

is it done? basically yes.

after 10 inserts i got this content of table:

# SELECT * FROM test_table;
id | random_code | something
----+-------------+-----------
1 | x           | x
2 | B           | x
3 | w           | x
4 | U           | x
5 | b           | x
6 | OE          | x
7 | N           | x
8 | zn          | x
9 | Y           | x
10 | JY          | x
(10 ROWS)

if you know your database-things you will see that the code has one serious (or not serious, depending on your view) problem.

if two inserts will happen at the same time, it is possible that one of them will raise exception of unique violation.

this is because there is a race condition between select count(*) and actual insert which takes place only after trigger finishes.

is there no hope? there is. but we have to modify the way we insert data to test table.

until now, i was able to simply: insert into test_table (something) values (‘x'); and it called my trigger code which set the random_code to whatever ‘s appropriate.

but if i want this to be a more fault-proof, i need to throw away the trigger, and force client to do inserts using select's.

like this:

CREATE OR REPLACE FUNCTION smart_insert(IN in_something TEXT, OUT new_key TEXT)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
BEGIN
LOOP
new_key := get_random_string(string_length);
BEGIN
INSERT INTO test_table(something, random_code) VALUES (in_something, new_key);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
END;
$BODY$;

and now, i can:

SELECT smart_insert('x');

done.

or is it?

what if you'd like to be able to generate these text keys in more than one table? (for simplicity sake let's assume all of them have the same fields).

in such a case we would modify the function to be:

CREATE OR REPLACE FUNCTION smart_insert(IN TABLE_NAME TEXT, IN in_something TEXT, OUT new_key TEXT)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
use_sql TEXT;
BEGIN
LOOP
new_key := get_random_string(string_length);
BEGIN
use_sql := 'INSERT INTO ' || quote_ident( TABLE_NAME ) || ' (something, random_code) VALUES (' || quote_literal(in_something) || ', ' || quote_literal(new_key) || ')';
EXECUTE use_sql;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
END;
$BODY$;

and then i could do:

SELECT smart_insert('test_table', 'xxx');

jak część z was wie byłem ostatnio na pgcon'ie.

tu od razu informacyjnie – byłem dzięki mojemu pracodawcy – firmie eo networks, któremu niniejszym publicznie bardzo dziękuję za umożliwienie mi wzięcia udziału w tej imprezie – jeśli szukacie pracy i znacie się na javie, bazach danych (głównie postgres, ale slyszałem też o jakichś projektach na innych bazach) – warto się odezwać.

wracając do meritum.

byłem tam na prezentacji olega bartunova nt. nowego tsearcha. nowego – nie znaczy, że będzie tsearch3. nowego – czyli tsearch2 zintegrowanego z samym postgresem.

jak się uda wszystko co zaplanowali to będzie tak w 8.3, ale jak się nie uda – no cóż. zobaczymy.

na prezentacji podpatrzyłem jedną rzecz którą wam tu teraz pokażę: wyszukiwanie pełnotekstowe z “poprawianiem" literówek (fuzzy-full text search).

Continue reading tsearch – instalacja, testy, rozszerzanie