Category: Uncategorized

smtp + sql = more than it seems so (part 1)

for quite some time i wanted to write about my experiences with combining smtp server with sql server.

this subject for some people might be too boring, for others – it might be seen as a simple howto. but in my opinion it clearly shows that when you combine 2 such technologies outcome is much better then it would seem (it's called synergy in merketoid-speak).

this subject will be divided into several parts, that will be posted separately (yeah, let's get some traffic to website 🙂

also, i hope to extend it in future if/when i'll find something else that can be added/changed in proposed solution.

so, without further ado, let's start:

Continue reading smtp + sql = more than it seems so (part 1)

encrypted passwords in database

in most applications you have some variant of this table:

CREATE TABLE users (
    id serial PRIMARY KEY,
    username TEXT NOT NULL,
    passwd TEXT
);

and, usually, the passwd stores user password in clear text way.

this is usually not a problem, but in case you'd like to add password encryption in database, there are some ways to do it – and i'll show you which way i like most.

Continue reading encrypted passwords in database

grant XXX on * ?

one of the more common problems new users have with postgresql (especially those that came from mysql background), is the lack of easy way to grant/revoke/do-something with many objects (tables/sequences/views) at once.

there are number of pages that deal with the problem, let's just name some from #postgresql infobots:

now, both of these pages have their benefits, but i'd like to show something simpler, yet (perhaps) more powerful.

instead of giving you the fish (figuratively speaking) i will give you the net and the skills so you'll be able to do the magic yourself.

Continue reading grant XXX on * ?

“FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas

UPDATE (2012-06-24): Version 9.1 of PostgreSQL renamed ident to peer (for local connections). So if you're having errors about “Peer authentication failed" – it is the same as “Ident authentication failed", and all described in this blogpost is still relevant.

ever seen one of those? i mean the “fatal: ident authentication failed"?

or, ever seen anyone having this problem when connecting to postgresql?

how often is this problem related to debian/post-debian linux distributions? 99%? 100%?

on #postgresql on irc.freenode.net it is the most common problem. my own irc logs show that “ident" showed over 300 times over last 41 days. now, that's something. and how come we have this problem? what can be done with it? read on.

Continue reading “FATAL: Ident authentication failed", or how cool ideas get bad usage schemas

finding optimum tables placement in 2-tablespace situation

just recently we got another array for out main production database. this means – we will be able to add new tablespace, thus making everything go faster.

in theory – it's nice. but which tables to move to the other?

the basic assumption is simple – index on table should not be on the same tablespace as the table itself. that's easy. but – should we really put all tables on one tablespace, and all indexes on another?

we decided that the important things that should be “boosted" are seeks and writes. sequential reads are (in our situation) more or less irrelevant.

read on to check how we split the load.

Continue reading finding optimum tables placement in 2-tablespace situation

easy access to postgresql documentation from firefox

for whatever reason i seem to remember names of files in postgresql documentation.

for example – i know, that psql docs are in app-psql.html, base page for configuration options is runtime-config.html. manual for “alter table" sql command, is in sql-altertable.html.

yet full page url (http://www.postgresql.org/docs/current/app-psql.html) is very long. on the other hand going to http://www.postgresql.org/docs/current/ is not really useful, as i need to find specific page which i remember filename, but not necessarily title.

is there any solution? apparently, yes, simply follow these steps:

  1. go to http://www.postgresql.org/docs/current/
  2. bookmark this page
  3. go to “Bookmarks" -> “Organize Bookmarks…"
  4. find the bookmark that you just created, select it, and click on “properties" in toolbar
  5. in “keyword" enter letter “p" (without quotes)
  6. change url to http://www.postgresql.org/docs/current/%s.html
  7. press “ok" (you might want to create a special folder to hide this bookmark from standard view, as you will never use it directly)

and that's all. after you've done it, you can simply enter: “p app-psql" in your location edit box, press enter and you will be directed to correct page.

of course this trick works not only with postgresql docs. it can be used for google, search.postgresql.org, anything you want. but using this for postgresql manual will definitely shorten time spent on “going to manual" 🙂

dell powervault md1000 – storage test

i recently got new toy for tests – brand new dell powervault md1000.

what's this, you ask? basically – a rather nice das (direct attached storage) from dell.

the box i got had 15 sas discs, each disc being 72gb, 15krpm.

since this will be used as database storage, i wanted to make some performance tests.

Continue reading dell powervault md1000 – storage test

postgresql tips & tricks

cortilap @ freenode's #postgresql asked about how to create a check() that will allow only one of the columns to be not null.

it doesn't sound cool, let's see:

with 2 columns (a,b) you make a check: check ( (a is not null and b is null) or (a is null and b is not null) or (a is null and b is null))

whoa. and what about 3 columns? 4?

of course it creates some questions about the schema, but is there a way to do it? without such long checks?

one solution is to make a function to check it. but perhaps a simpler solution is possible?

luckily all of the fields are ints.

a quick think, and here we go:

check (coalesce(a*0, 1) + coalesce(b*0, 1) + coalesce(c*0, 1) > 1)

and what is the field was text? same thing, but instead of doing “X"*0, i would do “length(X)*0" 🙂