Comments for select * from depesz;

Comment on What is the point of social sites? / Do czego służą serwisy społecznościowe? by Anonymous

Anonymous — Wed, 23 May 2012 15:26:50 +0000

Hey, I’m that alien too.

Comment on Understanding postgresql.conf : log* by Oscar Jacuinde

Oscar Jacuinde — Sun, 20 May 2012 21:07:46 +0000

Good article!, I needed to learn how to log bad querys in a server that we are developing some jsp apps, now i can see what errors we have in our query , were n00bs btw too

Comment on Getting session variables without touching postgresql.conf by depesz

depesz — Sun, 20 May 2012 18:07:57 +0000

@Taai:
no, i was wrong. i use 9.2 on daily basis, and in 9.2 custom_variable_classes is gone.

in pre 9.1 you can use the trick like i described in this post.

and going back to your original question – yes, backend start is ok. and you don’t need “limit 1″ in your query, but the query should be:
select backend_start from pg_stat_activity where procpid = pg_backend_pid();

Comment on Getting session variables without touching postgresql.conf by Taai

Taai — Sun, 20 May 2012 17:57:48 +0000

Looks like I misunderstood the “without modifying postgresql.conf”. So, modifying the “postgresql.conf” is the only way?

Comment on How to manage changes to your database? by Joel Jacobson

Joel Jacobson — Sun, 20 May 2012 12:57:56 +0000

You might want to check out a patch I submitted to pghackers, which add a –split option to pg_dump. Each table, sequence, view, function, etc, are dumped in separate files, optimal for version controlling. It’s then super-easy to diff two different schemas, simply use git to compare two schemas, and you will easily see in the summary which files are affected.

I’ve found the summary extremely important, you don’t want to be bothered with the actual diff in the first stage. First you want to see what objects changed, many of them can probably be ignored in case you are comparing a development database with production, as people tend to add crazy stuff to development databases, safe to ignore.

You can find the patch here:

http://archives.postgresql.org/pgsql-hackers/2010-12/msg02285.php

Comment on Getting session variables without touching postgresql.conf by depesz

depesz — Sat, 19 May 2012 16:56:51 +0000

@Taai:
just add “sess” to custom_variable_classes variable in postgresql.conf.

afterwards pg_ctl reload should make the class work.

Comment on Getting session variables without touching postgresql.conf by Taai

Taai — Sat, 19 May 2012 16:53:14 +0000

Can you, please, show me an example? I’m new at PostgreSQL and I don’t understand what is “class” and can’t find how to create a “class”.

I’m using version 9.1. For example, this:
SELECT set_config(‘sess.uid’, ’123′, FALSE);
gives me an error:
[Err] ERROR: unrecognized configuration parameter “sess.uid”

Comment on Getting session variables without touching postgresql.conf by depesz

depesz — Sat, 19 May 2012 15:10:34 +0000

@Taai:
please note that in recent pg (the article you commented on is nearly 3 years old) you can use any “class” for uguc, without defining it with “custom_variable_classes”.

so just use normal select set_config() and select current_setting() functions.

Comment on Getting session variables without touching postgresql.conf by Taai

Taai — Sat, 19 May 2012 12:56:53 +0000

I really want to store a “session” data / a global variable during the connection and this article is the best I found. Can you, please, consider my suggestion and tell me if it may work?

I suggest to use the timestamp of session when it started.

SELECT backend_start FROM pg_stat_activity WHERE pg_stat_activity.procpid = procpid LIMIT 1

It could be used instead of “expires_on” column and I think this should solve pid wraparound problem. Can you test this on some connection pooler?

Comment on Waiting for 9.1 – adding values to enums by Gyorgy Abraham

Gyorgy Abraham — Thu, 17 May 2012 15:26:05 +0000

Also:

“ALTER TYPE … ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.”

We got serious work while being on 9.0 with enums, 9.1 won’t really help us because of this limitation.

Comment on Waiting for 8.5 – exclusion constraints by depesz

depesz — Wed, 16 May 2012 21:28:41 +0000

@yokoloko:
1. epoch – try changing timezone to very different, and retry.
2. 2000-01-01 – to get smaller numbers. it doesn’t matter for pg, but makes for simpler reading
3. you definitely could do it, but it would require installation of gist_btree extension.

Comment on Waiting for 8.5 – exclusion constraints by yokoloko

yokoloko — Wed, 16 May 2012 21:26:03 +0000

Really really nice and helpful.

But there are few things i don’t really get.

- Why is it flawed to use extract(epoch from field at time zone ‘UTC’)? Cos i’m implementing it and it works perfectly

- Why are you substracting ’2000-01-01′::date to the timestamp?

- And also for comprehension is there a reason why you put the room number in the box and not outside as in EXCLUDE USING GIST (room_number WITH =, box ( … ) WITH &&);

Comment on Waiting for 9.2 – DROP INDEX CONCURRENTLY by Valentine Gogichashvili

Valentine Gogichashvili — Fri, 11 May 2012 10:01:28 +0000

Now it is time actually for somebody to implement REINDEX CONCURRENTLY… as all the missing parts are already there! Cool

Comment on ERROR: invalid byte sequence for encoding by Piotr

Piotr — Tue, 08 May 2012 13:37:29 +0000

Dzięki za przydatną pigułkę. Walczyłem z kodowaniem w Delphi+Zeos+Postgresql i bardzo mi pomogłeś w ogarnięciu i rozwiązaniu problemu.

Comment on Bloat happens by Han Zheng

Han Zheng — Thu, 03 May 2012 15:30:59 +0000

@DEPESZ:
thanks! I will try that.

Comment on Bloat happens by depesz

depesz — Thu, 03 May 2012 13:34:30 +0000

@Han Zheng:
I try to reorg tables as soon as they hit 20% of bloat (as reported by check_postgres.pl), or, if the table is small (<200MB) – 30-40%

Comment on Bloat happens by Han Zheng

Han Zheng — Thu, 03 May 2012 12:56:47 +0000

hi depesz. there’s one thing I wanna know: when is bloat too big?
what’s your exp. num of “warning” and “critical” bloat?

Comment on Setting WAL Replication by depesz

depesz — Tue, 24 Apr 2012 13:08:18 +0000

@Ajay:
what’s your restore command?

Comment on Setting WAL Replication by ajay

ajay — Tue, 24 Apr 2012 12:05:22 +0000

I am facing certain issue on my standby server here is the output from log file on standby.
`/baflbackup/wal-archive/000000010000007200000080′ -> `pg_xlog/RECOVERYXLOG’
LOG: restored log file “000000010000007200000080″ from archive
`/baflbackup/wal-archive/000000010000007200000081′ -> `pg_xlog/RECOVERYXLOG’
LOG: restored log file “000000010000007200000081″ from archive
`/baflbackup/wal-archive/000000010000007200000082′ -> `pg_xlog/RECOVERYXLOG’
LOG: restored log file “000000010000007200000082″ from archive
FATAL: could not restore file “000000010000007200000083″ from archive: return code 15
LOG: received smart shutdown request
LOG: startup process (PID 19570) exited with exit code 1
LOG: aborting startup due to startup process failure
How should I deal with this?

Thank you
Ajay

Comment on Waiting for 8.4 – pl/PgSQL RAISE by PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant

PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant — Fri, 20 Apr 2012 11:09:31 +0000

[...] hreflang=”en” xml:lang=”en”>Waiting for 8.4 – PL/pgSQL RAISE [...]

Comment on Understanding postgresql.conf : log* by Groum Workalemahu

Groum Workalemahu — Thu, 19 Apr 2012 14:45:11 +0000

It is an excellent article we are using Ubuntu and postgres to develop a data base for mobile payment system and we have had some issues on monitoring the performances of the database your article helped us a lot Thank you .

siniduwork scientific computing

Comment on Waiting for 8.4 – CASE in pl/PgSQL by PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant

PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant — Thu, 19 Apr 2012 07:53:14 +0000

[...] hreflang=”en” xml:lang=”en”>Waiting for 8.4 – CASE in PL/pgSQL [...]

Comment on OmniPITR by depesz

depesz — Wed, 18 Apr 2012 17:10:02 +0000

@Nicolas:
answer is simple, but unlikely to be helpful – monitor is simply not yet implemented. It is in queue, and it will be under development soon-ish, but there is no eta as for now.

Comment on OmniPITR by Nicolas

Nicolas — Wed, 18 Apr 2012 16:52:34 +0000

We are trying to use the tool in a study for pg 9.2 we are doing for our company.

However the omnipitr-monitor seems not to work:

Can’t locate object method “new” via package “OmniPITR::Program::Monitor” at /var/opt/hosting/build/omniti-labs-omnipitr-e499c60/bin/omnipitr-monitor line 10.

I checked the omnipitr/lib/OmniPITR/Program/Monitor.pm file is very light..

If you can help..

Thanks

Nicolas

Comment on Waiting for 9.1 – Synchronous replication by JWolf

JWolf — Tue, 17 Apr 2012 01:53:52 +0000

Thanks! nicely written. Your blogs are way cool!

Comment on Waiting for 9.1 – Synchronous replication by depesz

depesz — Mon, 16 Apr 2012 20:57:18 +0000

@JWolf:
hmnmm … like this one: http://www.depesz.com/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/

Comment on Waiting for 9.1 – Synchronous replication by JWolf

JWolf — Mon, 16 Apr 2012 16:54:22 +0000

makes sense. Thanks,
Any plans on writing a blog on horizontal shards using postgres

Comment on Waiting for 9.1 – Synchronous replication by depesz

depesz — Mon, 16 Apr 2012 09:22:06 +0000

@JWolf:

Well, SET … is just an sql query. So you can run it the same way you run any other query. No idea how that works in JDBC, but I don’t see why it would be different than (for example) calling update or delete.

Comment on Waiting for 9.1 – Synchronous replication by JWolf

JWolf — Mon, 16 Apr 2012 00:35:08 +0000

Our application is written in Java and uses standard JDBC driver API ( behind the scenes postgres jdbc provider) to connect to postgres.

I am wondering how to turn on and off synchronous_replication for some transactions only.
This way I can have the replication enabled for some important tables and not for other tables.

Do you happen to know how to set this when using jdbc drivers synchronous_replication

In your example, I see that you are using psql to set the same. But wondering how to set this in the application that typically does not use psql.

Comment on r/trees ( recursive trees, what did you think about? ) by diego

diego — Sat, 07 Apr 2012 17:26:52 +0000

Thank you, Depesz! You write exceptionally useful stuff.