Category “PostgreSQL” — 131 articles

Let’s say you need to ALTER TABLE foo ALTER COLUMN bar TYPE bigint;, and PostgreSQL is helpfully telling you that no you can’t because such and such views depend on the column. The basic way to deal with that is to copy paste from the error message the names of the views involved, then prepare a script wherein you first DROP VIEW then ALTER TABLE and finally CREATE VIEW again, all in the same transaction.

You certainly know that implementing dynamic triggers in PLpgSQL is impossible. But I had a very bad night, being up from as soon as 3:30 am today, so that when a developer asked me about reusing the same trigger function code from more than one table and for a dynamic column name, I didn’t remember about it being impossible. Here’s what happens in such cases, after a long time on the problem (yes, overall, that’s a slow day).

Hannu just gave me a good idea in this email on -hackers, proposing that pg_basebackup should get the xlog files again and again in a loop for the whole duration of the base backup. That’s now done in the aforementioned tool, whose options got a little more useful now: Usage: pg_basebackup.py [-v] [-f] [-j jobs] "dsn" dest Options: -h, --help show this help message and exit --version show version and quit -x, --pg_xlog backup the pg_xlog files -v, --verbose be verbose and about processing progress -d, --debug show debug information, including SQL queries -f, --force remove destination directory if it exists -j JOBS, --jobs=JOBS how many helper jobs to launch -D DELAY, --delay=DELAY pg_xlog subprocess loop delay, see -x -S, --slave auxilliary process --stdin get list of files to backup from stdin Yeah, as implementing the xlog idea required having some kind of parallelism, I built on it and the script now has a --jobs option for you to setup how many processes to launch in parallel, all fetching some base backup files in its own standard ( libpq) PostgreSQL connection, in compressed chunks of 8 MB (so that’s not 8 MB chunks sent over).

These days, thanks to my community oriented job, I’m working full time on a PostgreSQL patch to terminate basic support for extending SQL. First thing I want to share is that patching the backend code is not as hard as one would think. Second one is that git really is helping. “Not as hard as one would think, are you kidding me?”, I hear some say. Well, that’s true. It’s C code in there, but with a very good layer of abstractions so that you’re not dealing with subtle problems that much.

The PostgreSQL IRC channel is a good place to be, for all the very good help you can get there, because people are always wanting to remain helpful, because of the off-topics discussions sometime, or to get to talk with community core members. And to start up your day too. This morning’s question started simple : “how can I check if today is the “first sunday fo the month”. or “the second tuesday of the month” etc?