Every now and then somebody asks how to make diff of database schemata.
Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.
Personally I think that such approach is inherently flawed. Why?
Well, the biggest information is that hot-backups on slave work. And they work fine. Really fine.
Some more information (with nice graph!):
“Zainspirowany" pismem o kuchni amerykańskiej, zrobiłem sobie słodko-ostry chłodnik arbuzowy. Wyszło nieźle, więc dla potomności:
Yesterday (August, 7th), Tom Lane committed:
Log Message: ----------- Recognize functional dependency on primary keys. This allows a table's other columns to be referenced without listing them in GROUP BY, so long as the primary key column(s) are listed in GROUP BY. Eventually we should also allow functional dependency on a UNIQUE constraint when the columns are marked NOT NULL, but that has to wait until NOT NULL constraints are represented in pg_constraint, because we need to have pg_constraint OIDs for all the conditions needed to ensure functional dependency. Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane
Continue reading Waiting for 9.1 – Recognize functional dependency on primary keys.
On 28th of July, Simon Riggs committed patch which:
Log Message: ----------- Reduce LOCK levels OF CREATE TRIGGER AND SOME ALTER TABLE, CREATE RULE actions. Avoid hard-coding lockmode used FOR many altering DDL commands, allowing easier future changes OF LOCK levels. Implementation OF initial analysis ON DDL sub-commands, so that many LOCK levels are now at ShareUpdateExclusiveLock OR ShareRowExclusiveLock, allowing certain DDL NOT TO block reads/writes. FIRST OF NUMBER OF planned changes IN this area; additional docs required WHEN FULL project complete.
Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE
Some guy came to #postgresql today. Described his problem, got additional question, and then … well .. I wouldn't believe if it didn't happen to me.
Continue reading How to make sure you will not get any help on IRC
On 25th Robert Haas committed patch which adds first of ‘CREATE IF NOT EXISTS' commands:
Log Message: ----------- CREATE TABLE IF NOT EXISTS. Reviewed BY Bernd Helmle.
Continue reading Waiting for 9.1 – CREATE TABLE IF NOT EXISTS
Let's imagine simple situation – you have table of objects (each with id), and you want objects 3, 71, 5 and 16. And in that order!
How to do it?
Continue reading How to order by some random – query defined – values?
One of the questions that pop up frequently on IRC is how to see queries are now executed on the server, and what queries were earlier.
Theoretically answer to this is simple – pg_stat_activity and log_min_duration_statement. Or log_statement. What is the difference? That's exactly why I'm writing this post.
On 20th of July, Robert Haas committed (and later committed change to it) patch which adds another \* command to psql:
Log Message: ----------- Add \conninfo command to psql, to show current connection info. David Christensen. Reviewed by Steve Singer. Some further changes by me.