query indent

amrigo

Is there a php script or a program that can indent my querys because i am tired to format then in my editor

thank´s in advance

Sxooter

This looks useful, if you have vim installed (most linux distros do, and it's easy to get for Windows too. I'm sure BSD has it available as well, if it's not installed by default.)

godwheel

Not sure about other programs but homesite lets you easily customize your tags.

amrigo

in vim and homesite i can take a one line query :

SELECT * FROM table WHERE field=value

and with one command transform in something very clear like:

SELECT
*
FROM
table
WHERE
field=value

What is point in time recovery from postgresql ?

amrigo

maybe a reg expression that can do this ...

Sxooter

Looking at the size of the indent.c code from gnu, I'd guess it's way more than a single regex could do. Maybe a sed script could do something like it. I'm no sed programmer though.

PITR (point in time recovery) is a method whereby one first makes a file copy of a working server, copies it somewhere safe or faraway, then commences to ship the system transaction logs there too, as they are created, in a stream. Then, x days / weeks / months / years down the road, when a meteor hits the primary data center, or a user issues a delete from table without a where clause, you can execute those transaction logs against the original data set and stop the replay at a particular point in time.

It's one of those "enterprise" features that PostgreSQL has been lacking for some time. Once table partitioning and multi-master replication come online, most of the big enterprise features will have been realized.

amrigo

would you like to post another enterprise features you know !?

i do translate pgadmin and phppgadmin in my country and i am a big fan of postgresql

Sxooter

savepoints are another one, which pgsql gets with 8.0 as well. It allows you to catch / retry on errors that would have, in the past, caused the whole transaction to fail.

Synchronous log level replication would be nice. Slony-I is great, but being asynchronous, certain types of transactional systems just aren't gonna be able to use it. I think the Mammoth replicator does log level synchronous replication, but it's $1,000 so I don't know. :⁽

pgpool does syncrhonous, but on the statement level, which can lead to some strange anaomolies in things like dates being different etc...

What I really want is something like Oracle's RAC, where clustering and failover are used to make multiple servers look like one server to the average user, and the implementation details are not seen by anyone but an admin. It's getting closer every year.

Lazy and automatic vacuuming (lazy vacuum is in 8.0, automatic vacuuming via pg_autovacuum is in /contrib, getting there quickly)

row locked tables instead of MVCC tables for highly updated queues and such. (Likely not gonna happen in my lifetime, as it's apparently a MAJOR pain to write something like that in PostgreSQL's codebase, which is built heavily around MVCC).
2 (or possibly 3) phase commit. save points would allow a basic implementation to be done in user land now.

table partitioning, materialized views, and updateable views are all things you can do now in userland if you know what you're doing, but would be nice to have built right in.

A query optimizer that learns from its mistakes.

I'm sure there are a few more. Nowadays it's mostly little things whereas a few years ago it was major issues like vacuum eating up all your bandwidth (or even further back, locking your tables) that kept PostgreSQL out of the enterprise. Nowadays it's a likely as not to be a lack of available enterprise talent that keeps it out of the hosting center. Which is a good thing, for people with the talent. 🙂

csn

I thought Postgres/MVCC could do row-level locking, no?

Sxooter

It isn't that it can't lock at the row level, it's that it uses MVCC to do it, and on a frequently updated table the vacuum requirements can cause an issue. Especially ion queue type operations where each record is created, exists for a few minutes, then gets deleted.

It's a minor issue, and one that a good dba can usually take care of with a properly scheduled vacuum.

csn

Does that really affect performance though? Seems like it might be a space issue since for updates Postgres just uses a new row and throws away the old, rather than reusing it. Indexes are updated after each update.

csn

Will PITR play a big part in replication?

Sxooter

It affects performance because it requires frequent vacuuming, and in some intensive situations, the vacuuming can't keep up with the insert rate. It's only a problem for a very few high volumn applications. I.e. youre creating dead rows faster than vacuum can reclaim them, even if it's running constantly. I personally haven't run into it, but my boss has in a past life. I'm convinced that a faster Disk subsystem or even a Solid state disk drive would fix any such issues that might crop up, and they'd still be cheaper than an oracle license.