Get all of the tables linked by a Foreign Key

Here is a handy little PostgreSQL query that will list all of the tables linked by a foreign key to the table YOUR_TABLE.
The output will include the keys on each side.
This can be very useful if you want to build up a map of your database through the relationships between the tables.

select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col
from pg_attribute af,
pg_attribute a,
(select conrelid,confrelid,conkey[i] as conkey,
confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = ‘f’) ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid AND (conrelid::regclass = ‘YOUR_TABLE’::regclass);

How to kill all sessions in Postgres so that they stay dead.

Postgres Connections

Have you ever tried to drop or rename a postgres database or even create another database from one as a template, only to be confronted by the dreaded message:

postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.

So, we kill those sessions off with something like the below SQL that will kill all sessions that are connected to that database:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()pg_stat_activity.datname = ‘TARGET_DB’
AND pg_stat_activity.datname = ‘A_DATABASE’;

Job done, now we can create our database… But wait!

postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.

Someone has beaten us too it and reconnected!

If you are like me, the only logical solution is to try to do the above statements really really fast. Unfortunately, I normally lose, so I need a way to stop sessions from reconnecting to the database that doesn’t involve changing config files etc.

Fortunately, the simple solution is to disallow all connections to that database and we can do it with this line:

update pg_database set datallowconn = false where datname = ‘a_database’;

Once you are finished, you just need to set it back to True and the database will be open for connections again.

How to make a Decision

How to Make a Decision
How to Make a Decision

I am slightly embarrassed to admit that I have often avoided making decisions throughout my life. That’s not to say that I haven’t made things happen but I have often avoided putting my foot down and choosing a path one way or the other, preferring instead to allow circumstances to make the decision myself. That’s not to say that I haven’t subconsciously pushed circumstances one way or another, but it’s not the same and does not give you the same benefits as being decisive and telling the world what you want.

The thing is that an argument could be made that the whole point of life is to make decisions (if we take any spiritual questions out of the equation). The biological definition of life is that ‘life is irritable’ meaning that the environment does something and life does something back. This is true for all life – a plant decides on some level to move towards the light and if it were to stop doing it, it would die. The same is true for us on a much more complex level. If we were to stop making decisions, we would not get up, eat or drink and we would soon die.

Continue reading “How to make a Decision”

How to live 10 Years Longer by Playing Games

Live 10 years LongerPlus be happier and have fewer regrets

“I’m a gamer so I like to have goals. I like special missions and secret objectives, so here’s my special mission for this talk. I’m going to try to increase the lifespan of everyone in this room by seven and a half minutes.”
– Jane McGonigal

Jane Mcdonigal gave a talk at TED recently that I loved. Its backed by some real research and gives us a way to not only live longer but to enjoy life while we live it and to avoid the top 5 deathbed regrets which are:

Top 5 deathbed regrets: Continue reading “How to live 10 Years Longer by Playing Games”

How to instantly be more powerful and feel better about yourself.

Body LanguageThe cheaters guide to being more powerful, confident and becoming the person you wish you could be (and living longer as a bonus).Its quite a promise but you will easily get that and a lot more from this short post and video.

A word of warning though is that you get absolutely no benefit whatsoever from this until you physically do it. On the plus side there is nothing easier to test and you can immediately feel the benefits. What even better is that the more that you do it, the greater the benefit that you get and the more ingrained the changes will become.
Continue reading “How to instantly be more powerful and feel better about yourself.”