Tag: Databases

Removing (dropping) a Foreign Key Constraint in PostgreSQL

Had a need to drop a Foreign Key Constraint in PostgreSQL 8.x today, and this is how you do it:

database=> \d table_name;
Table "public.table_name"
Column | Type | Modifiers
------------------+------------------------+-----------
id | integer |
field | character varying(20) |
field_description | character varying(150) |
Indexes:
[..]
Foreign-key constraints:
"table_name_id_fkey" FOREIGN KEY (id) REFERENCES other_table(id) ON DELETE CASCADE

database=> ALTER TABLE table_name DROP CONSTRAINT "table_name_id_fkey";
ALTER TABLE
database=>

As simple as that. The name of the constraint is shown when describing the table with \d under “Foreign-key constraints”, and you simply do an ALTER statement to drop the constraint.

Translating Drizzle to Norwegian

As Monty asked for help with translations of the current strings available in Drizzle on his blog yesterday, I sat down a couple of hours yesterday and a couple of hours today to at least attempt to contribute something to the project. As my primary language is Norwegian and I have some experience writing, I decided to tackle the Norwegian (Bokmål, not Nynorsk) translation of Drizzle. I’ve currently finished the 358 available messages, but I’d really appreciate it if someone spent a couple of minutes / hours to read through them and confirm that my assumptions are sane.

The most troubling part when it comes to definitions are the issue of MySQL/Drizzle’s ‘relay log’ which I translated into ‘replikasjonslogg’ – which mainly means “replication log”. This sounds much better in Norwegian, but suddenly the code mentioned both a “replication log” and a “relay log”. I tried finding out what the semantic difference in MySQL were, but were unable to grok anything from the MySQL manual or through a Google search. If anyone has any advice here, it’d be very appreciated. I also made a few notes of where there are obvious errors in the original english strings:

Error on close of '%'s (Errcode: %d)
 - Located in mysys/errors.c:28 

Errcode:
Can't read value for symlink '%s' (Error %d)
 - Located in mysys/errors.c:47 
Can't create symlink '%s' pointing at '%s' (Error %d)
 - Located in mysys/errors.c:48
Copy text Error on realpath() on '%s' (Error %d)
 - Located in mysys/errors.c:49 

%*s(Defaults to on; use --skip-%s to disable.)
 - Missing space
 - Located in mysys/my_getopt.c:1170 

The event could not be processed no other hanlder error happened
 - hanlder
 -  Located in mysys/my_handler_errors.h:118 

SSL information in the master info file ('%s') are ignored because this MySQL slave was compiled without SSL support.
 - MySQL
 - Located in drizzled/rpl_mi.cc:276 

Slave I/O thread killed while waitnig to reconnect after a failed registration on master
 - waitnig
 - Located in drizzled/slave.cc:90 

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog'..
 - mysqlbinlog
 - Located in drizzled/slave.cc:1864 

Found wrong key definition in %s; Please do "ALTER TABLE '%s' FORCE " to fix it!
 - feil spaceplass
 - Located in drizzled/table.cc:1162 

Table '%-.64s' was created with a different version of MySQL and cannot be read
 - MySQL
 - Located in drizzled/table.cc:1818

I could probably submit a patch for this, but seeing as the source is very much in flux these days, I think I’ll wait until it settles down a bit — unless someone is interesting in reviewing and committing an “unimportant” patch at this stage.

BTW: Launchpad worked great for doing translations, so I’m going to look into using gettext and Launchpad for doing translations for pwned.no and my other services in the future.

The Day of Four Letter Abbreviations: BASE vs ACID

Any person that has spent some time with databases will have heard the acronym ACID some time or the other. ACID is what transactions are aimed to achieve, so that you’re able to define a set of operations that are connected to each other. ACID is a definition of what these transactions should be able to provide to the user of the RDBMS.

Via xarpb I got hold of an article from ACM Queue, titled BASE: An ACID Alternative:

If ACID provides the consistency choice for partitioned databases, then how do you achieve availability instead? One answer is BASE (basically available, soft state, eventually consistent).

BASE is diametrically opposed to ACID. Where ACID is pessimistic and forces consistency at the end of every operation, BASE is optimistic and accepts that the database consistency will be in a state of flux. Although this sounds impossible to cope with, in reality it is quite manageable and leads to levels of scalability that cannot be obtained with ACID.

Proves to be a very interesting read, and something that’s very in time with how we’re already doing large partitioning of large databases, where the application tier is responsible for a lot of the distributed “transactions”.

Drizzle – Making MySQL Leaner and Meaner

As noted I recently went for a total of five days of vacation. In the meantime, the intarwebs of blogospheres and the seas of web 2.0 exploded with posts and discussion over Drizzle. Drizzle is a fork of MySQL with the intention of making things more suitable for cloud computing and the regular web use cases. I’m actually quite intrigued by this, and I really look forward to getting some more time to read up on the issues at hand. It will be interesting to see how things compare to CouchDB, Amazon S3, Solr and other service that take a different road than regular relationial databases. Interesting. Anyways, here’s the posts I’d suggest checking out to get better and more usable information about Drizzle:

I’ll take it for a testdrive as soon as the first public version becomes available, and I’m looking forward to it. Might be fun!