postgresql – Page 59 – select * from depesz;

Waiting for 8.5 – GUC per user and database

On 7th of October Alvaro Herrera committed his own patch, which adds quite interesting possibilty:

Log Message:
-----------
Make it possibly to specify GUC params per user and per database.
 
Create a new catalog pg_db_role_setting where they are now stored, and better
encapsulate the code that deals with settings into its realm.  The old
datconfig and rolconfig columns are removed.
 
psql has gained a \drds command to display the settings.
 
Backwards compatibility warning: while the backwards-compatible system views
still have the config columns, they no longer completely represent the
configuration for a user or database.
 
Catalog version bumped.

Continue reading Waiting for 8.5 – GUC per user and database

Waiting for 8.5 – “GRANT ALL”

One of the most common (or perhaps even the most common) question people have regarding PostgreSQL, is how to change privileges for all tables.

Usually they want something like MySQL's:

GRANT ... ON database.* TO ...

Which is interesting as it is actually 2 distinct features in one:

grant privileges on all existing tables
automatically grant privileges on all tables that will be created in this database in the future

Now, thanks to 2 new patches – we have this possibility in PostgreSQL.

Continue reading Waiting for 8.5 – “GRANT ALL"

Waiting for 8.5 – ‘samehost’ and ‘samenet’ in pg_hba.conf

October, finally. “Only" 1 month of backlog in new features in 8.5, but I'm getting there 🙂

So, on 1st of October Tom Lane committed patch by Stef Walter:

Log Message:
-----------
Support "samehost" and "samenet" specifications in pg_hba.conf,
by enumerating the machine's IP interfaces to look for a match.
 
Stef Walter

For some reason I cannot find it in mailing list archive, but luckily have copy of email (without all headers unfortunately).

Continue reading Waiting for 8.5 – ‘samehost' and ‘samenet' in pg_hba.conf

Calculating backlog of events to handle

Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description:

We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of tasks added every 5 minutes, calculate backlog at any given moment.

Did you understand the problem? Well – I didn't. So, let's see the data, and expected output.

Continue reading Calculating backlog of events to handle

Waiting for 8.5 – changes in hstore

Andrew Gierth (RhodiumToad on irc) wrote, and Tom Lane committed:

Assorted improvements in contrib/hstore.
 
Remove the 64K limit on the lengths of keys and values within an hstore.
(This changes the on-disk format, but the old format can still be read.)
Add support for btree/hash opclasses for hstore --- this is not so much
for actual indexing purposes as to allow use of GROUP BY, DISTINCT, etc.
Add various other new functions and operators.
 
Andrew Gierth

Continue reading Waiting for 8.5 – changes in hstore

Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

On 29th of September (I know, there is a backlog – I'll work on it, I promise), Tom Lane committed another patch from Pavel Stehule:

Allow MOVE FORWARD n, MOVE BACKWARD n, MOVE FORWARD ALL, MOVE BACKWARD ALL
in plpgsql.  Clean up a couple of corner cases in the MOVE/FETCH syntax.
 
Pavel Stehule

Continue reading Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

Speeding up dump/restore process

As some of you know, I've been working lately for OmniTI company. When doing things for them (PostgreSQL related of course :), I stumbled on very interesting problem.

One of our clients is working on PostgreSQL 8.2, and wants to upgrade to 8.3. This is generally trivial – pg_dump, pg_restore/psql, and you're done.

But, this situation was with a twist – we had only very limited time-frame to do the migration. To be exact – we had 4 hours that we can bring the website down for.

So, we had to dump database, transfer it to new server, and load. All within 4 hours. Simple? Sure. Database was ~ 200GB (after restore – around 130GB). How to do it?

Continue reading Speeding up dump/restore process

CREATE ROLE privilege cannot be inherited?!

One of my clients hit a strange limitation – apparently you cannot inherit CREATE ROLE privilege.

First, let's test if it's really true:

Continue reading CREATE ROLE privilege cannot be inherited?!

Waiting for 8.5 – Machine readable EXPLAIN

After very long discussions, on 10th of August, Tom Lane committed patch by Robert Haas:

Log Message:
-----------
Extend EXPLAIN to support output in XML or JSON format.
 
There are probably still some adjustments to be made in the details
of the output, but this gets the basic structure in place.
 
Robert Haas

Continue reading Waiting for 8.5 – Machine readable EXPLAIN

=$
|

Tag: postgresql