Skip to content

SELECT pg_sleep_until('#800Monies');

Pavlo recently pointed out that the pg_sleep() function in PostgreSQL has two alternatives: pg_sleep_for() and pg_sleep_until().

What do they do?

 

Continue reading "SELECT pg_sleep_until('#800Monies');"
  • Twitter
  • Bookmark SELECT pg_sleep_until('#800Monies'); at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg SELECT pg_sleep_until('#800Monies');
  • Bloglines SELECT pg_sleep_until('#800Monies');
  • Technorati SELECT pg_sleep_until('#800Monies');
  • Fark this: SELECT pg_sleep_until('#800Monies');
  • Bookmark SELECT pg_sleep_until('#800Monies'); at YahooMyWeb
  • Bookmark SELECT pg_sleep_until('#800Monies'); at Furl.net
  • Bookmark SELECT pg_sleep_until('#800Monies'); at reddit.com
  • Bookmark SELECT pg_sleep_until('#800Monies'); at blinklist.com
  • Bookmark SELECT pg_sleep_until('#800Monies'); at Spurl.net
  • Bookmark SELECT pg_sleep_until('#800Monies'); at Simpy.com
  • Bookmark SELECT pg_sleep_until('#800Monies'); at blogmarks
  • Bookmark SELECT pg_sleep_until('#800Monies'); with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

role ... cannot be dropped because some objects depend on it

I want to drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But PostgreSQL has objections:

ERROR: role "xyz" cannot be dropped because some objects depend on it
DETAIL: access to schema public

Continue reading "role ... cannot be dropped because some objects depend on it"
  • Twitter
  • Bookmark role ... cannot be dropped because some objects depend on it at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg role ... cannot be dropped because some objects depend on it
  • Bloglines role ... cannot be dropped because some objects depend on it
  • Technorati role ... cannot be dropped because some objects depend on it
  • Fark this: role ... cannot be dropped because some objects depend on it
  • Bookmark role ... cannot be dropped because some objects depend on it at YahooMyWeb
  • Bookmark role ... cannot be dropped because some objects depend on it at Furl.net
  • Bookmark role ... cannot be dropped because some objects depend on it at reddit.com
  • Bookmark role ... cannot be dropped because some objects depend on it at blinklist.com
  • Bookmark role ... cannot be dropped because some objects depend on it at Spurl.net
  • Bookmark role ... cannot be dropped because some objects depend on it at Simpy.com
  • Bookmark role ... cannot be dropped because some objects depend on it at blogmarks
  • Bookmark role ... cannot be dropped because some objects depend on it with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

create language if not exist

In a customer project i have to setup a database from a Makefile. Part of my problem: on windows the installer may or may not install "plpgsql" into template1 so in consequence this language would be activated in every new database. But that's not predictable. This problem can appear on different Linux/Unix distributions too.

In contrary to some other opinions in #postgresql i dislike filtering error messages and i prefer clean solutions.

Continue reading "create language if not exist"
  • Twitter
  • Bookmark create language if not exist at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg create language if not exist
  • Bloglines create language if not exist
  • Technorati create language if not exist
  • Fark this: create language if not exist
  • Bookmark create language if not exist at YahooMyWeb
  • Bookmark create language if not exist at Furl.net
  • Bookmark create language if not exist at reddit.com
  • Bookmark create language if not exist at blinklist.com
  • Bookmark create language if not exist at Spurl.net
  • Bookmark create language if not exist at Simpy.com
  • Bookmark create language if not exist at blogmarks
  • Bookmark create language if not exist with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Understanding SQL

Just have to port a phpkit database to something else. It seems, the phpkit ppl did not really understand what SQL is for.

The 'phpkit_config' table in this case contains 255(!) columns and a lot of this columns seems to be for extensions.

This is a very good example where you should use normalization: you don't add more and more columns to one table but you have a key/value table and for new entries you add a new key with a value. No need to change the table at all.

  • Twitter
  • Bookmark Understanding SQL at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Understanding SQL
  • Bloglines Understanding SQL
  • Technorati Understanding SQL
  • Fark this: Understanding SQL
  • Bookmark Understanding SQL at YahooMyWeb
  • Bookmark Understanding SQL at Furl.net
  • Bookmark Understanding SQL at reddit.com
  • Bookmark Understanding SQL at blinklist.com
  • Bookmark Understanding SQL at Spurl.net
  • Bookmark Understanding SQL at Simpy.com
  • Bookmark Understanding SQL at blogmarks
  • Bookmark Understanding SQL with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

table size, database size

I have seen this question more than once in the past: "How much storage space do i need for my table?"
PostgreSQL can give you this information.


The storage space (in bytes) for one specific table:

ads=# select pg_relation_size('size_test');
pg_relation_size
------------------
5668864
(1 row)

Same query, but with the result in a human-readable format:

ads=# select pg_size_pretty(pg_relation_size('size_test'));
pg_size_pretty
----------------
5536 kB(1 row)

Remember that this is only the size of the table, not included an index or additional stuff.
The size including any index can be found out with:


ads=# select pg_size_pretty(pg_total_relation_size('size_test'));
pg_size_pretty
----------------
7656 kB
(1 row)


The size of a complete database:

ads=# select pg_size_pretty(pg_database_size('ads'));
pg_size_pretty
----------------
11 MB
(1 row)


You can even find out, how much space a specific value needs:

ads=# select pg_column_size(5::smallint);
pg_column_size
----------------
2
(1 row)

ads=# select pg_column_size(5::int);
pg_column_size
----------------
4
(1 row)

ads=# select pg_column_size(5::bigint);
pg_column_size
----------------
8
(1 row)

ads=# select pg_column_size('This is a string'::varchar);
pg_column_size
----------------
20
(1 row)

ads=# select length('This is a string'::varchar);
length
--------
16
(1 row)

The 4 byte difference between the column size and the string length is the storage overhead needed for varchar columns.

The size for a tablespace can be found out with pg_tablespace_size(), but i don't have a machine with configured tablespace at hand to show an example.
  • Twitter
  • Bookmark table size, database size at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg table size, database size
  • Bloglines table size, database size
  • Technorati table size, database size
  • Fark this: table size, database size
  • Bookmark table size, database size at YahooMyWeb
  • Bookmark table size, database size at Furl.net
  • Bookmark table size, database size at reddit.com
  • Bookmark table size, database size at blinklist.com
  • Bookmark table size, database size at Spurl.net
  • Bookmark table size, database size at Simpy.com
  • Bookmark table size, database size at blogmarks
  • Bookmark table size, database size with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca