hannesd on irc had a problem with finding overlapping date/time ranges.
basically – in postgresql there is “overlaps" operator, but unfortunately it doesn't use indexes:
Tag: postgresql
overprotective developers
i'm recently under impression that pg developers are overprotective. who do they protect?
us, users.
i mean – don't get me wrong – i live with, and (to some extent) thanks to the piece of marvelous technology which is postgresql ordbms.
i use it, i try to help other use it. i don't think there is any better database available now (at the very least – for my purposes).
but, just lately, i some to see some cases where decissions are made in a way to “protect us (dbas) from our faults".
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.
text to hstore migration
in postgresql 8.2, in contrib, is great new datatype called hstore.
if you're not familiar with it – check the docs.
in short – this is indexable associative-array (hash) in one field.
i was given a task to convert some text field to hstore field.
what should be fixed in postgresql
edit: title was changed because apparently it was too offensive
the title might a bit too offensive, but perhaps it will make it more visible.
some time ago (march 2007) i asked on polish db-related newsgroup about things that people hate about their databases.
to give some example i wrote about 5 things that i hate (dislike?) about postgresql. today i looked back at this topic to check what has changed. many things did change. some things don't piss me anymore. some new things showed up, so, here we go – what i (personally) see as things to be fixed/removed soon to get nicer (for users) database system.
who has birthday tomorrow?
so, there you have a users table, with a very basic structure:
Table "public.users"
Column | Type | Modifiers
-----------+---------+-----------
id | integer | not null
birthdate | date |
Indexes:
"x_pkey" PRIMARY KEY, btree (id)then, you have a task: find a query that will return all users which have birthday tomorrow. how will you do it?
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:
- http://pgedit.com/public/sql/acl_admin/index.html
- http://www.archonet.com/pgdocs/grant-all.html
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.
finding missing pairs
let's assume we have a simple table:
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | integer | not null
Indexes:
"test_pkey" PRIMARY KEY, btree (a, b)
"q" UNIQUE, btree (b, a)now, let's insert some rows to it:
# INSERT INTO test SELECT * FROM generate_series(1,100) AS a, generate_series(1,100) AS b^J; INSERT 0 10000
remove rows with a = b:
# DELETE FROM test WHERE a = b; DELETE 100
and prepare test-case by randomly removing some rows:
# DELETE FROM test WHERE random() < 0.002; DELETE 17
the question is – find all pairs of (a,b) where there is no row (a',b') where (a'=b and b'=a).
in other words – every row (a,b) should be paired. rows with a = 2 and b = 3, is paired by row with a = 3 and b = 2.
how to find incomplete pairs?
“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