Examining foreign keys in Postgres

I often need to inspect fkey usage in Postgres to audit views , and it can be a PITA combing through data.

I found this little gem on the postgres mailing list by Thomas Kellerer

> SELECT c.table_name || ‘ references ‘ || tu.table_name
FROM information_schema.table_constraints c JOIN information_schema.constraint_table_usage tu ON (c.constraint_name = tu.constraint_name AND c.table_schema = tu.table_schema)
WHERE c.constraint_type = ‘FOREIGN KEY’ ;

That lists all your table fkey relations

With a little bit of magic we can clean it up to alphabetize a bit…

> order by c.table_name , tu.table_name

and if you want to find what tables are referencing the table you’re on…

> WHERE c.table_name = ‘$$NAME$$’ or tu.table_name = ‘$$NAME$$’

and to make things a bit more clear… we can add in the constraint name

> SELECT c.table_name || ‘ references ‘ || tu.table_name || ‘ via ‘ || tu.constraint_name
FROM information_schema.table_constraints c JOIN information_schema.constraint_table_usage tu ON (c.constraint_name = tu.constraint_name AND c.table_schema = tu.table_schema)
WHERE c.constraint_type = ‘FOREIGN KEY’ ORDER BY c.table_name, tu.table_name;

I’m not sure how the actual fkeys are stored in terms of columns, but if anyone has an idea, i’d love to update this call to list the columns on each table

Hulu's dumbest move: blocking boxee

Starting this week, Hulu will block the boxee client from its streams.

The heads of both Hulu and Boxee addressed it on their corporate blogs, calling it unfortunate
[Boxee Posting](http://blog.boxee.tv/2009/02/18/the-hulu-situation/)
[Hulu Posting](http://blog.hulu.com/2009/2/18/doing-hard-things)

The summary is very simple. Hulu’s content providers got mad to learn that people were watching Hulu on TV sets, not on computers. TV ads & Neilsen metrics provide for more income than online view ( online viewing was designed as a way to make extra cash, not make big $$ and cover production costs ) — and the content producers don’t want to steal people away from network viewing. They want to get people to watch ads on their couches in groups, on the big screen, where revenue is better and ROI/efficacy is higher.

The situation is obviously unfortunate to boxee & other set-top-device/media-center users… but also a huge setback for Hulu.

Let’s be clear about this – Hulu didn’t want to do this. They make money every time someone watches a video on their site – whether its Boxee or a PC. They don’t care how people watch the content they distribute. It’s the content providers that forced this action.

But in caving in to their providers, Hulu became responsible for the largest mistake they will have made.

See, internet people are smart and savvy. It won’t be long until someone makes a plugin / patch / version of Boxee that identifies itself as Safari or Internet Explorer or something else — re-enabling the ability for users everywhere to revel in streaming video.

And once people do that, those media devices are identifying themselves as not-boxee. Those devices are saying “Oh, hey there. look at us, see, we’re NOT connected to a TV, we’re just a computer”.

And those devices aren’t saying “Yes! we’re a boxee device! Someone is sitting back on a couch. there are probably multiple people watching. this is probably on a big screen. you can charge 5x the CPM for this display.”

And that is what Hulu – and the brain-dead shortsighted content producers – lost. Boxee and other set-top devices are how people will consume media in the future. It’s happening already. The rise of their popularity combined with the costs of cable in a flailing economy have many people dropping subscription services for broadcast networks and online viewing.

So in short, Hulu shot itself in the foot.

Boxxee users will get hulu content again, surreptitiously, but Hulu will only be making standard CPM rates for it — and losing out on a chance to charge for premium CPMs.

By the time Hulu can make the content providers wake up, far too many projects will be made to liberate video onto set-top devices — and they’ll start spending all of their money on bribing projects to identify themselves as set-top devices , or lawyers to threaten and sue developers. ( and that is successfully or not, as they’re spending money and not winning anything)

So congratulations Big Media, you’re an idiot. Again. Next time technology & culture shifts, try embracing it and designing new revenue streams on it — not try to quash it and live in the past.