Re: [BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

Peter Schuller wrote:

> It would be embarressing if I caused this problem myself by
> misunderstanding wal_archiving. My understanding has been that once
> wal_archive gets called, no one ever cares what happens with the file
> except if I want to do PITR (since the whole point is to offload it
> somewhere or similar).
>
> I'll go and re-read the documentation on this immediately. If this is
> the problem, I do apologies for the noise and people's time.

Yeah, this is the problem. The archive command must not delete the
file it is copying. It is supposed to only copy it. The file must
continue to be on the server until a checkpoint is executed.

In fact you'd have a problem on recovery after any crash, not just
vacuum full.

I think it's the documentation's fault that this is not a lot more
prominent.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

> > -rw------- 1 postgres postgres 16777216 Aug 26 17:16 0000000100000018000000EE
> > drwx------ 2 postgres postgres 305232 Aug 26 17:14 archive_status
> >
> > Note that the archival of the ....ED xlog file started at 17:14:52,
> > and I cancelled the VACUUM FULL at 17:16:06.
>
> What's your archive_command?

archive_command = '/path/to/wal-archive.sh "%p" "%f"'

(only path to script changed for privacy)

The script just removes the archive file; the intent was to have
archival enabled such that we could start using it for real without
re-starting the server in the future. The script contents is:

===
#!/bin/bash

set -e

path="$1"
file="$2"

# put segment somewhere useful here

rm $path

echo "$(date): $path" > /tmp/wal-archive-stamp

exit 0
===

It would be embarressing if I caused this problem myself by
misunderstanding wal_archiving. My understanding has been that once
wal_archive gets called, no one ever cares what happens with the file
except if I want to do PITR (since the whole point is to offload it
somewhere or similar).

I'll go and re-read the documentation on this immediately. If this is
the problem, I do apologies for the noise and people's time.

--
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org

Re: [PERFORM] Autovacuum does not stay turned off

On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
>
> Does anyone know what will cause this bahavior for autovacuum?

You're probably approaching the wraparound limit in some database.

If you think you can't afford the overhead when users are accessing
the system, when are you vacuuming?

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:

> Ok, back to why
>
> turns out template0 is the culprit, why is autovac not vacuuming this ?

Hmm ... template0 is not supposed to need vacuuming, because it is
frozen ... is it marked with datallowconn=false?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-es-ayuda] consulta a varias tablas en una sola funcion en plpgsql

Roberto:

A mi se me ocurre que crees una vista con esos resultado y los campos
que necesites ... así solo tienes que buscar lo que deseas en la vista.

Saludos,
Gilberto.
El mar, 05-08-2008 a las 14:35 -0400, Roberto Rodríguez Pino escribió:
> Hola a todos!
> Estoy algo complicado haciendo una función en plpgsql, les paso a
> contar.
> Necesito retornar un registro que tenga las columnas "código",
> "cantidad", "costo".
> Por otro lado, tengo 7 tablas. Donde una tiene los códigos que quiero
> ocupar y las otras 6 tablas, tienen la información necesaria para
> determinar cantidad y costo. Según el código que se ocupe se debe ir a
> distintas tablas de las 6 que menciono.
> En resumen existen 3 consultas (cada una se hace por el código antes
> mencionado) a diversas tablas que retornan registros iguales.
> ¿Es posible hacer esto? ¿Alguien me puede tirar algunas pistas?
> Saludos y gracias!
> Roberto
>
> PD: por alguna razón me he complicado asimilando plpgsql :-(

--
TIP 8: explain analyze es tu amigo

Re: [PERFORM] Autovacuum does not stay turned off

On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
> Does anyone know what will cause this bahavior for autovacuum?

http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html
-> autovacuum_freeze_max_age

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

Peter Schuller wrote:

> The pg_xlog directory contains:
>
> -rw------- 1 postgres postgres 16777216 Aug 26 17:16 0000000100000018000000EE
> drwx------ 2 postgres postgres 305232 Aug 26 17:14 archive_status
>
> Note that the archival of the ....ED xlog file started at 17:14:52,
> and I cancelled the VACUUM FULL at 17:16:06.

What's your archive_command?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:

> AutoVacuumShmem->av_signal[AutoVacForkFailed] = false;
> pg_usleep(100000L); /* 100ms */
>
> SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER);
> continue;
>
> Do these signals get cleaned up on a reload ?

Well, not on a reload specifically, but this signal is of prompt
response (i.e. the postmaster acts immediately on it). See
CheckPostmasterSignal. Also, note that this code starts a worker, not
the launcher which is what you're seeing.

The signal you're looking for is PMSIGNAL_START_AUTOVAC_LAUNCHER (see
the varsup.c code in the vicinity of what I posted earlier). The
postmaster response is to set start_autovac_launcher (see
sigusr1_handler in postmaster.c) and when this is seen set, the launcher
is started (see ServerLoop in postmaster.c). However the bit you're
interested in is *why* the signal is being sent, which is what the
freeze limits determine.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

Hello,

[note: in pastes below, the only thing changes are database names and
hostnames, for privacy reasons; otherwise plain cut'n'paste including
typos...)

Very short version: I cancelled a VACUUM FULL; server crashed; won't
start again because it tries to access an obsolete WAL log file that
is likely to be from the point in time when VACUUM FULL started.

Long version:

The following refers to PostgreSQL 8.2.4 running on Linux 2.6.19-3 on
an old sarge system. I realize 8.2.4 is too old a release, but the bug
seems distinct enough that I felt I should definitely report it. I did
not find anything that looked relevant in the point release changelog
for 8.2.

Background is that I have a couple of databases on the system with >
1000 relations (if that is at all relevant). Because of autovacuum
issues (likely having to do with a too small max_fsm_relations) I
wanted to perform a VACUUM FULL.

I did, but realized I actually wanted VACUUM VERBOSE FULL. So after a
few seconds I hit ctrl-c in the psql client (run locally on the
machine as the postgres user; thus ident authed and connected as the
postgres PostgreSQL user). Output was:

contentsharing=# \timing
Timing is on.
contentsharing=# vacuum full
contentsharing-# ;
Cancel request sent
ERROR: canceling statement due to user request
PANIC: cannot abort transaction 830706491, it was already committed
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
!> \q

After this I tried re-starting the database which failed (i.e., it was
not up after the start attempt). Checking the log file I had:

Aug 26 17:14:52 host postgres[5094]: [3116-1] user=,db= LOG: archived transaction log file "0000000100000018000000ED"
Aug 26 17:16:06 host postgres[28467]: [2-1] user=postgres,db=databasename ERROR: canceling statement due to user request
Aug 26 17:16:06 host postgres[28467]: [2-2] user=postgres,db=databasename STATEMENT: vacuum full
Aug 26 17:16:06 host postgres[28467]: [2-3] ^I;
Aug 26 17:16:06 host postgres[28467]: [3-1] user=postgres,db=databasename PANIC: cannot abort transaction 830706491, it was already committed
Aug 26 17:16:06 host postgres[5091]: [2-1] user=,db= LOG: server process (PID 28467) was terminated by signal 6
Aug 26 17:16:06 host postgres[5091]: [3-1] user=,db= LOG: terminating any other active server processes
Aug 26 17:16:06 host postgres[16307]: [2792-1] user=databasename,db=databasename WARNING: terminating connection because of crash of another server process
Aug 26 17:16:06 host postgres[16307]: [2792-2] user=databasename,db=databasename DETAIL: The postmaster has commanded this server process to roll back the current
Aug 26 17:16:06 host postgres[16307]: [2792-3] transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
Aug 26 17:16:06 host postgres[16307]: [2792-4] user=databasename,db=databasename HINT: In a moment you should be able to reconnect to the database and repeat your
Aug 26 17:16:06 host postgres[16307]: [2792-5] command.
Aug 26 17:16:06 host postgres[5091]: [4-1] user=,db= LOG: all server processes terminated; reinitializing
Aug 26 17:16:07 host postgres[28483]: [5-1] user=postgres,db=databasename FATAL: the database system is starting up
Aug 26 17:16:07 host postgres[28482]: [5-1] user=,db= LOG: database system was interrupted at 2008-08-26 17:15:52 CEST
Aug 26 17:16:07 host postgres[28482]: [6-1] user=,db= LOG: could not open file "pg_xlog/0000000100000018000000ED" (log file 24, segment 237): No such file or directory
Aug 26 17:16:07 host postgres[28482]: [7-1] user=,db= LOG: invalid primary checkpoint record
Aug 26 17:16:07 host postgres[28482]: [8-1] user=,db= LOG: could not open file "pg_xlog/0000000100000018000000ED" (log file 24, segment 237): No such file or directory
Aug 26 17:16:07 host postgres[28482]: [9-1] user=,db= LOG: invalid secondary checkpoint record
Aug 26 17:16:07 host postgres[28482]: [10-1] user=,db= PANIC: could not locate a valid checkpoint record
Aug 26 17:16:07 host postgres[5091]: [5-1] user=,db= LOG: startup process (PID 28482) was terminated by signal 6
Aug 26 17:16:07 host postgres[5091]: [6-1] user=,db= LOG: aborting startup due to startup process failure

And the following is presumably from my manual start-up attempt (based on timestamps):

Aug 26 17:17:11 host postgres[28511]: [1-1] user=,db= LOG: could not bind socket for statistics collector: Cannot assign requested address
Aug 26 17:17:11 host postgres[28511]: [2-1] user=,db= LOG: disabling statistics collector for lack of working socket
Aug 26 17:17:11 host postgres[28511]: [3-1] user=,db= WARNING: autovacuum not started because of misconfiguration
Aug 26 17:17:11 host postgres[28511]: [3-2] user=,db= HINT: Enable options "stats_start_collector" and "stats_row_level".
Aug 26 17:17:11 host postgres[28512]: [4-1] user=,db= LOG: database system was interrupted at 2008-08-26 17:15:52 CEST
Aug 26 17:17:11 host postgres[28512]: [5-1] user=,db= LOG: could not open file "pg_xlog/0000000100000018000000ED" (log file 24, segment 237): No such file or directory
Aug 26 17:17:11 host postgres[28512]: [6-1] user=,db= LOG: invalid primary checkpoint record
Aug 26 17:17:11 host postgres[28512]: [7-1] user=,db= LOG: could not open file "pg_xlog/0000000100000018000000ED" (log file 24, segment 237): No such file or directory
Aug 26 17:17:11 host postgres[28512]: [8-1] user=,db= LOG: invalid secondary checkpoint record
Aug 26 17:17:11 host postgres[28512]: [9-1] user=,db= PANIC: could not locate a valid checkpoint record
Aug 26 17:17:11 host postgres[28511]: [4-1] user=,db= LOG: startup process (PID 28512) was terminated by signal 6
Aug 26 17:17:11 host postgres[28511]: [5-1] user=,db= LOG: aborting startup due to startup process failure

The pg_xlog directory contains:

-rw------- 1 postgres postgres 16777216 Aug 26 17:16 0000000100000018000000EE
drwx------ 2 postgres postgres 305232 Aug 26 17:14 archive_status

Note that the archival of the ....ED xlog file started at 17:14:52,
and I cancelled the VACUUM FULL at 17:16:06.

It is entirely likely, though I cannot say for sure, that I started
the VACUUM FULL prior to the archival of ...ED.

I still have the entire pgsql database directory untouched except for
the manual start-up attempt. I am afraid I cannot dump the thing
somewhere for inspection (it contains confidential information), but I
can assist in inspecting the contents.

--
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org

Re: [ADMIN] restoring from dump

Hi Alvaro,

> Maybe initdb was executed?

Yes.  I installed 8.3.3 afresh and as a part of
such initdb was executed.  I have saved (renamed)
the previous distribution (8.3.0) and there are
many files in pg_clog directory there.  The oldest
one is named 00, and consecutively named in hex
2 digit name as 01, 02, .. , 09, 0A, 0B, .. , 84.
Then it skipped to 88 (no 85, 86, 87).  The size
of each file is 262144 bytes, with exception of
88, which is 163840.

I just looked at the one in new (8.3.3) directory:
  -bash-3.00$ date
  Tue Aug 26 08:40:35 PDT 2008
  -bash-3.00$ pwd
  /usr/local/pgsql/data/pg_clog
  -bash-3.00$ ll
  total 24
  -rw-------  1 postgres postgres 24576 Aug 26 02:33 0000

As I compare the size with what's in last night's email,
there is no difference in size.  The timestamp is in the
middle of night.

Fascinating...

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Tue 8/26/2008 7:29 AM
To: Tena Sakai
Cc: Marcelo Martins; jeff@frostconsultingllc.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restoring from dump

Tena Sakai wrote:
> Hi Alvaro,
>
> I just looked in pg_clog directory and there is
> only one file:
>   -rw-------  1 postgres postgres 24576 Aug 25 20:18 0000
> I saw the same file about 10:30 am and I believe
> it was roughly the same size.  This is a brand new
> installation of 8.3.3.

Maybe initdb was executed?  That would cause the files to disappear.
Of course, so would the data; it would have to be restored from a
backup.

Note that these files use only 2 bits per transaction, so in 24756 bytes
you have enough room to cover 99024 transactions.  Furthermore, they
always grow in 8192-byte increments.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:
> On Tue, Aug 26, 2008 at 11:41 AM, Alvaro Herrera <alvherre@commandprompt.com

> > What's the max age(pg_database.datfrozenxid)?
>
> select datfrozenxid from pg_database ;
> datfrozenxid
> --------------
> 201850617
> 101850961
> 86039359
> 21522712

Well, the first one is over the limit, isn't it?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On Tue, Aug 26, 2008 at 11:51 AM, Dave Cramer <pg@fastcrypt.com> wrote:

On Tue, Aug 26, 2008 at 11:41 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Dave Cramer wrote:
> On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera <alvherre@commandprompt.com

> > The only possible explanation for this behavior is that somebody is
> > signalling the postmaster due to Xid wraparound issues.  This is keyed
> > on some GUC vars -- Perhaps you have autovacuum_freeze_max_age set to an
> > insane value?
>
> Doesn't appear to be insane ?
>
> #autovacuum_freeze_max_age = 200000000  # maximum XID age before forced
> vacuum

Not only sane, but also the default ;-)

What's the max age(pg_database.datfrozenxid)?

select datfrozenxid from pg_database ;
 datfrozenxid
--------------
    201850617
    101850961
     86039359
     21522712

this code in autovacuum.c looks like it might be interesting

                        if (AutoVacuumShmem->av_signal[AutoVacForkFailed])
                        {
                                /*
                                 * If the postmaster failed to start a new worker, we sleep
                                 * for a little while and resend the signal.  The new worker's
                                 * state is still in memory, so this is sufficient.  After
                                 * that, we restart the main loop.
                                 *
                                 * XXX should we put a limit to the number of times we retry?
                                 * I don't think it makes much sense, because a future start
                                 * of a worker will continue to fail in the same way.
                                 */
                                AutoVacuumShmem->av_signal[AutoVacForkFailed] = false;
                                pg_usleep(100000L);             /* 100ms */
                                SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER);
                                continue;

Do these signals get cleaned up on a reload ?

Dave

[pgsql-es-ayuda] Obtener los permisos por esquema

Hola que tal!

Alguno de ustedes sabe como obtener los permisos USAGE y CREATE de los
esquemas para
cada uno de los roles que existen en la base de datos ???

Estoy utilizando PostgreSQL 8.2 en Windows XP

Ya me he revisado la vista information_schema, encontre un apartado
que muestra los permisos
que les menciono... pero.... solo lo hace para el usuario/rol que
actualmente esta logeado en el server
y lo que yo necesito es un listado de todos los roles con sus
respectivos permisos.

Ojala me puedan orientar al respecto, de antemando gracias.-
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On Tue, Aug 26, 2008 at 11:41 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Dave Cramer wrote:
> On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera <alvherre@commandprompt.com

> > The only possible explanation for this behavior is that somebody is
> > signalling the postmaster due to Xid wraparound issues.  This is keyed
> > on some GUC vars -- Perhaps you have autovacuum_freeze_max_age set to an
> > insane value?
>
> Doesn't appear to be insane ?
>
> #autovacuum_freeze_max_age = 200000000  # maximum XID age before forced
> vacuum

Not only sane, but also the default ;-)

What's the max age(pg_database.datfrozenxid)?

select datfrozenxid from pg_database ;
 datfrozenxid
--------------
    201850617
    101850961
     86039359
     21522712

--
Alvaro Herrera                                http://www.CommandPrompt.com/

PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:
> On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera <alvherre@commandprompt.com

> > The only possible explanation for this behavior is that somebody is
> > signalling the postmaster due to Xid wraparound issues. This is keyed
> > on some GUC vars -- Perhaps you have autovacuum_freeze_max_age set to an
> > insane value?
>
> Doesn't appear to be insane ?
>
> #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
> vacuum

Not only sane, but also the default ;-)

What's the max age(pg_database.datfrozenxid)?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] Large number of tables slow insert

On Tue, Aug 26, 2008 at 6:50 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Sat, 23 Aug 2008, Loic Petit wrote:
>>
>> I use Postgresql 8.3.1-1 to store a lot of data coming from a large amount
>> of sensors. In order to have good
>> performances on querying by timestamp on each sensor, I partitionned my
>> measures table for each sensor. Thus I create
>> a lot of tables.
>
> As far as I can see, you are having performance problems as a direct result
> of this design decision, so it may be wise to reconsider. If you have an
> index on both the sensor identifier and the timestamp, it should perform
> reasonably well. It would scale a lot better with thousands of sensors too.

Properly partitioned, I'd expect one big table to outperform 3,000
sparsely populated tables.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Dave Cramer wrote:
> On Tue, Aug 26, 2008 at 9:59 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>
> > On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera <
> > alvherre@commandprompt.com> wrote:

> >> Certainly not, and that's not what I see here either.  I assume process
> >> 25407 is (was) the postmaster, yes?
> >>
> >> If you "show autovacuum", is it on?
> >
> > Yes that was the postmaster, and I did check to see if autovacuum was on,
> > and it was not.
> >

> So where do we go from here ?

The only possible explanation for this behavior is that somebody is
signalling the postmaster due to Xid wraparound issues.  This is keyed
on some GUC vars -- Perhaps you have autovacuum_freeze_max_age set to an
insane value?

Doesn't appear to be insane ?

#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

phoenix.kiula@gmail.com ("Phoenix Kiula") writes:
> See, this is where I get confused. I want to upgrade from 8.2.3 to
> 8.3.3. The recommendation is to try Slony. So I download Slony and try
> to configure it. The configure command gives me this:
>
>
> ----
> checking for correct version of PostgreSQL... "error"
> configure: error: Your version of PostgreSQL (8.2) is lower
> than the required 8.3. Slony-I needs functionality included in
> a newer version.
> ----
>
>
> Well, if Slony needs a newer version, then how can it be used to upgrade?!

I expect that what you downloaded was a pre-release candidate for
version 2.0, which does indeed "eschew" older versions (for some good
reasons - you can't support *all* versions of *everything*,
*forever*).

With version 1.2.14, the latest *official* release, versions of
PostgreSQL as far back as 7.4 are certainly supported.
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://cbbrowne.com/info/lsf.html
Dickson's Gardening Rule: When weeding, the best way to make sure you
are removing a weed and not a valuable plant is to pull on it. If it
comes out of the ground easily, it is a valuable plant.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [BUGS] BUG #4281: some types of errors do not log statements

Thomas H. wrote:
> maybe its by design (to not insert badly encoded characters into the
> utf8 encoded logs)? nevertheless to debug those faulty programm/codes,
> it would help to see what query provokes the error...

Well, the problem is mainly that there is no query, because the bytes arriving
are garbage. A human observer could make sense of it in some cases, but not
a computer in the general case.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:
> On Tue, Aug 26, 2008 at 9:59 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>
> > On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera <
> > alvherre@commandprompt.com> wrote:

> >> Certainly not, and that's not what I see here either. I assume process
> >> 25407 is (was) the postmaster, yes?
> >>
> >> If you "show autovacuum", is it on?
> >
> > Yes that was the postmaster, and I did check to see if autovacuum was on,
> > and it was not.
> >
> So where do we go from here ?

The only possible explanation for this behavior is that somebody is
signalling the postmaster due to Xid wraparound issues. This is keyed
on some GUC vars -- Perhaps you have autovacuum_freeze_max_age set to an
insane value?

varsup.c line 246
/*
* We'll start trying to force autovacuums when oldest_datfrozenxid gets
* to be more than autovacuum_freeze_max_age transactions old.
*
* Note: guc.c ensures that autovacuum_freeze_max_age is in a sane range,
* so that xidVacLimit will be well before xidWarnLimit.
*
* [...]
*/
xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age;

...

if (TransactionIdFollowsOrEquals(curXid, xidVacLimit) &&
IsUnderPostmaster)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);

However, I think that in allowed configurations you should also receive
these warnings:

/* Give an immediate warning if past the wrap warn point */
if (TransactionIdFollowsOrEquals(curXid, xidWarnLimit))
ereport(WARNING,
(errmsg("database \"%s\" must be vacuumed within %u transactions",
NameStr(*oldest_datname),
xidWrapLimit - curXid),
errhint("To avoid a database shutdown, execute a full-database VACUUM in \"%s\".",
NameStr(*oldest_datname))));

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] Bug introduced in 8.0

On Tue, Aug 26, 2008 at 7:10 AM, Andrew Sullivan <ajs@commandprompt.com> wrote:
>
> The minor releases are security and stability releases. If your
> management thinks that they are optional to install, then get that in
> writing, because they are instructing you to run known-dangerous
> software. They are being negligent. Patching your software for known
> defects is not "upgrading", it's "doing your job."

Also, start looking for another job where your bosses aren't idiots.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On Tue, Aug 26, 2008 at 6:31 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On 8/26/08, Tomasz Ostrowski <tometzky@batory.org.pl> wrote:
>>
>> I think nobody would guide you step by step. Either read documentation
>> and do it yourself or hire an expert:
>
>
> Thanks. I suppose that spirit is quite evident in the documentation.
> Why make it easy or easily understandable when you can win fanatical
> fans by requiring them to invest months of their time!

Christ, remind to do you no favors. I'm sure if I had written a step
by step guide and one part of it didn't work you'd be right back here
threatening to sue me or something.

Look, it's a comlex subject, and you need to have a pretty good clue
what you're doing so if something goes wrong you're not making a big
mistake and losing all your data. And you can stop with the
histrionics. It took me less than one weekend to install and test
slony for migration / backup at work.

Remember, for every problem, there is a simple, elegant, easy
solution. Which is wrong.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On Tue, Aug 26, 2008 at 5:39 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>
>> You need to download Slony 1.2.14, which supports both 8.2 and 8.3.
>> You'll find it under Quick downloads.
>>
>
>
>
> Ok done. Slony is installed. Now what? How should I install a new
> database which is 8.3.3?

Which OS are you running? How was 8.2 installed?

> I was also told that the postgresql.conf settings across 8.2 and 8.3
> are different so I cannot (or should not) use the same old
> postgresql.conf for the new database install. Is this true? Where can
> I find the instructions? I read this lengthy document --
> http://www.postgresql.org/docs/8.3/static/release-8-3.html -- but it
> is not clear which variables I need to change.

Well, look at the entries that aren't commented out in your 8.2 and
make the same changes in your 8.3 postgresql.conf

I just want to point out that the reason there aren't a lot of step by
step guides on this is that it's a complex subject. There are many
different OSes and several ways you could have installed postgresql,
and step by step instructions for RHEL4 may not be the same as for
Ubuntu 8.04 etc...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On Tue, Aug 26, 2008 at 9:59 AM, Dave Cramer <pg@fastcrypt.com> wrote:

On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Dave Cramer wrote:

> Ok, here are the logs from last night
>
> 2008-08-26 04:00:02 EDT [25407]  LOG:  received SIGHUP, reloading
> configuration files
> 2008-08-26 04:00:02 EDT [22649]  LOG:  autovacuum launcher shutting down
> 2008-08-26 04:00:02 EDT [30438]  LOG:  autovacuum launcher started
>
>
> you can see the SIGHUP, the launcher being shut down, and starting right
> back up again ???
>
> is this expected behaviour ?

Certainly not, and that's not what I see here either.  I assume process
25407 is (was) the postmaster, yes?

If you "show autovacuum", is it on?

Yes that was the postmaster, and I did check to see if autovacuum was on, and it was not.

Dave

So where do we go from here ?

Re: [ADMIN] restoring from dump

Tena Sakai wrote:
> Hi Alvaro,
>
> I just looked in pg_clog directory and there is
> only one file:
> -rw------- 1 postgres postgres 24576 Aug 25 20:18 0000
> I saw the same file about 10:30 am and I believe
> it was roughly the same size. This is a brand new
> installation of 8.3.3.

Maybe initdb was executed? That would cause the files to disappear.
Of course, so would the data; it would have to be restored from a
backup.

Note that these files use only 2 bits per transaction, so in 24756 bytes
you have enough room to cover 99024 transactions. Furthermore, they
always grow in 8192-byte increments.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [pgsql-es-ayuda] tablas por esquema

2008/8/26 José Fermín Francisco Ferreras <josefermin54@hotmail.com>:
> Hola,
> Quisiera saber la forma d presentar las tablas por esquema en psql??
>
> Tengo 3 esquemas, esquema1, esquema2, public.
>
> Necesito ver si existe una instrucción q me diga algo:
>
> \dt esquema1 -> con esto digo q me despliegue las tablas del esquema1
>
> Nota: hago la pregunta sólo por curiosidad.

Supongo que te refieres a
\dt esquema1.*

Casi te respondes a ti mismo en la pregunta :)

Saludos

--
Miguel Rodríguez Penabad
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgadmin-hackers] Dialogs Review new patch

On Tue, Aug 26, 2008 at 3:22 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> Guillaume Lelarge a écrit :

>> New patch is available on:
>> http://developer.pgadmin.org/~guillaume/dialogreview_20080825.patch.bz2
>>
>> It'll be commited tomorrow afternoon (if no one objects).
>>
>
> Done.

:-)

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

Re: [pgadmin-hackers] Dialogs Review new patch

Guillaume Lelarge a écrit :
> [...]
> I'll only commit some pieces of my patch. Here are the dialogs that
> won't be commited:
>
> * dlgFunction (status bar, OK and cancel buttons don't display
> correctly on Mac)
> * dlgLanguage (strange error about wxChoice::GetString on Mac...
> strange because there's no wxChoice widget on this dialog)
> * dlgTable (wxListCtrl issue on Columns tab, on Mac)
> * dlgType (on Mac and Windows)
> * dlgRole (no chkValue on Variables tab, on Linux)
> * dlgSchedule (Days and Times tabs, on all platforms)
>
> New patch is available on:
> http://developer.pgadmin.org/~guillaume/dialogreview_20080825.patch.bz2
>
> It'll be commited tomorrow afternoon (if no one objects).
>

Done.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

[pgadmin-hackers] SVN Commit by guillaume: r7420 - in trunk/pgadmin3: . pgadmin pgadmin/agent pgadmin/ctl pgadmin/dlg pgadmin/include/agent pgadmin/include/dlg pgadmin/include/utils pgadmin/ui

Author: guillaume

Date: 2008-08-26 15:21:01 +0100 (Tue, 26 Aug 2008)

New Revision: 7420

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7420&view=rev

Log:
Review of the dialogs to make them growable.

Modified:
trunk/pgadmin3/CHANGELOG
trunk/pgadmin3/pgadmin/agent/dlgJob.cpp
trunk/pgadmin3/pgadmin/ctl/ctlSecurityPanel.cpp
trunk/pgadmin3/pgadmin/dlg/dlgAggregate.cpp
trunk/pgadmin3/pgadmin/dlg/dlgDatabase.cpp
trunk/pgadmin3/pgadmin/dlg/dlgEditGridOptions.cpp
trunk/pgadmin3/pgadmin/dlg/dlgForeignKey.cpp
trunk/pgadmin3/pgadmin/dlg/dlgIndex.cpp
trunk/pgadmin3/pgadmin/dlg/dlgPackage.cpp
trunk/pgadmin3/pgadmin/dlg/dlgProperty.cpp
trunk/pgadmin3/pgadmin/dlg/dlgSchema.cpp
trunk/pgadmin3/pgadmin/dlg/dlgSequence.cpp
trunk/pgadmin3/pgadmin/dlg/dlgTablespace.cpp
trunk/pgadmin3/pgadmin/dlg/dlgTextSearchConfiguration.cpp
trunk/pgadmin3/pgadmin/dlg/dlgTextSearchDictionary.cpp
trunk/pgadmin3/pgadmin/dlg/dlgView.cpp
trunk/pgadmin3/pgadmin/include/agent/dlgJob.h
trunk/pgadmin3/pgadmin/include/dlg/dlgAggregate.h
trunk/pgadmin3/pgadmin/include/dlg/dlgDatabase.h
trunk/pgadmin3/pgadmin/include/dlg/dlgEditGridOptions.h
trunk/pgadmin3/pgadmin/include/dlg/dlgForeignKey.h
trunk/pgadmin3/pgadmin/include/dlg/dlgIndex.h
trunk/pgadmin3/pgadmin/include/dlg/dlgPackage.h
trunk/pgadmin3/pgadmin/include/dlg/dlgProperty.h
trunk/pgadmin3/pgadmin/include/dlg/dlgSchema.h
trunk/pgadmin3/pgadmin/include/dlg/dlgSequence.h
trunk/pgadmin3/pgadmin/include/dlg/dlgTablespace.h
trunk/pgadmin3/pgadmin/include/dlg/dlgTextSearchConfiguration.h
trunk/pgadmin3/pgadmin/include/dlg/dlgTextSearchDictionary.h
trunk/pgadmin3/pgadmin/include/dlg/dlgView.h
trunk/pgadmin3/pgadmin/include/utils/misc.h
trunk/pgadmin3/pgadmin/pgAdmin3.cpp
trunk/pgadmin3/pgadmin/ui/dlgAddFavourite.xrc
trunk/pgadmin3/pgadmin/ui/dlgAggregate.xrc
trunk/pgadmin3/pgadmin/ui/dlgCast.xrc
trunk/pgadmin3/pgadmin/ui/dlgCheck.xrc
trunk/pgadmin3/pgadmin/ui/dlgColumn.xrc
trunk/pgadmin3/pgadmin/ui/dlgConnect.xrc
trunk/pgadmin3/pgadmin/ui/dlgConversion.xrc
trunk/pgadmin3/pgadmin/ui/dlgDatabase.xrc
trunk/pgadmin3/pgadmin/ui/dlgDirectDbg.xrc
trunk/pgadmin3/pgadmin/ui/dlgDomain.xrc
trunk/pgadmin3/pgadmin/ui/dlgEditGridOptions.xrc
trunk/pgadmin3/pgadmin/ui/dlgFindReplace.xrc
trunk/pgadmin3/pgadmin/ui/dlgForeignKey.xrc
trunk/pgadmin3/pgadmin/ui/dlgGroup.xrc
trunk/pgadmin3/pgadmin/ui/dlgHbaConfig.xrc
trunk/pgadmin3/pgadmin/ui/dlgIndex.xrc
trunk/pgadmin3/pgadmin/ui/dlgIndexConstraint.xrc
trunk/pgadmin3/pgadmin/ui/dlgJob.xrc
trunk/pgadmin3/pgadmin/ui/dlgMainConfig.xrc
trunk/pgadmin3/pgadmin/ui/dlgManageFavourites.xrc
trunk/pgadmin3/pgadmin/ui/dlgManageMacros.xrc
trunk/pgadmin3/pgadmin/ui/dlgOperator.xrc
trunk/pgadmin3/pgadmin/ui/dlgPackage.xrc
trunk/pgadmin3/pgadmin/ui/dlgPgpassConfig.xrc
trunk/pgadmin3/pgadmin/ui/dlgReassignDropOwned.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepCluster.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepClusterUpgrade.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepListen.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepNode.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepPath.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepSequence.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepSet.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepSetMerge.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepSetMove.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepSubscription.xrc
trunk/pgadmin3/pgadmin/ui/dlgRepTable.xrc
trunk/pgadmin3/pgadmin/ui/dlgRule.xrc
trunk/pgadmin3/pgadmin/ui/dlgSchema.xrc
trunk/pgadmin3/pgadmin/ui/dlgSelectConnection.xrc
trunk/pgadmin3/pgadmin/ui/dlgSequence.xrc
trunk/pgadmin3/pgadmin/ui/dlgServer.xrc
trunk/pgadmin3/pgadmin/ui/dlgStep.xrc
trunk/pgadmin3/pgadmin/ui/dlgSynonym.xrc
trunk/pgadmin3/pgadmin/ui/dlgTablespace.xrc
trunk/pgadmin3/pgadmin/ui/dlgTextSearchConfiguration.xrc
trunk/pgadmin3/pgadmin/ui/dlgTextSearchDictionary.xrc
trunk/pgadmin3/pgadmin/ui/dlgTextSearchParser.xrc
trunk/pgadmin3/pgadmin/ui/dlgTextSearchTemplate.xrc
trunk/pgadmin3/pgadmin/ui/dlgTrigger.xrc
trunk/pgadmin3/pgadmin/ui/dlgUser.xrc
trunk/pgadmin3/pgadmin/ui/dlgView.xrc
trunk/pgadmin3/pgadmin/ui/xrcDialogs.cpp

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

Re: [ADMIN] Problem with reinstall of PostgreSQL 8.3

Glad to know your problem was solved :-)


Goutham Naval wrote:

Hi Rommel,
 
Thank you very much for your help. As a windows admin, I deleted the old postgres user account, and uninstalled postgresql and installed it to get past the issue.
 
Kind Regards,
 
Gotham.


 

---

Date: Mon, 25 Aug 2008 13:32:54 -0400
From: icecrew@gmail.com
To: pgsql-admin@postgresql.org
CC: gnaval@hotmail.com
Subject: Re: [ADMIN] Problem with reinstall of PostgreSQL 8.3

Hi,

This happened to me before and the problem was with the 'postgres' Windows account that was created during the first install. If you don't specify a password for this account, the installer will choose a very hard to guess, hard to remember password :-). I logged in as an administrator and removed the postgres user account and let the installer re-create it. You also have the option of manually setting the password for this account, then telling the installer what that password is.

I don't know if your problem is the same as the one I experienced, but I hope this helps.

Rommel.


Goutham Naval wrote:

Hi,
 
I installed postgreSQL 8.3 on windows and changed few settings for ssl. After which I uninstalled it by executing the .msi file again. My intention was to reinstall from scratch, and unfortunately during the reinstall I am getting a error
 
'Invalid username specified: logon failure: unknown user name or bad password.'
 
I am providing valid windows user name and pwd.
 
How can I get past this issue, please help.
 
Thanks.

---

Be the filmmaker you always wanted to be—learn how to burn a DVD with Windows®. Make your smash hit

---

Get ideas on sharing photos from people like you. Find new ways to share. Get Ideas Here!

Re: [HACKERS] Another refactoring proposal: move stuff into nodes/nodeFuncs.[ch]

On Mon, 2008-08-25 at 11:24 -0400, Tom Lane wrote:

> The advantages of doing this would be (a) reduce the number of places
> to look in when implementing a new node type; (b) eliminate some
> cross-subsystem #inclusions that weaken modularity of the backend.

Are we doing either of those things in this release?

Might these changes screw up patches already in progress? Can we hold
off making these changes until we're sure the latter isn't true?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] Restoring Backup With OIDs As Primary Key

Ben C wrote:

> Is there a way to increment the OID by changing a value in the system tables?

Not a system table, but you can with pg_resetxlog -o.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Restoring Backup With OIDs As Primary Key

On Tue, Aug 26, 2008 at 01:30:23PM +1000, Ben C wrote:
>
> Is there a way to increment the OID by changing a value in the system tables?

I don't think so, but I just tried creating a table in 8.3 with a
column named "oid", and it worked. Maybe you can load in your data
that way, so that you don't need to change your application. (I'd
test it a little more carefully for side effects than I just did, mind.)

A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Dave Cramer wrote:

> Ok, here are the logs from last night
>
> 2008-08-26 04:00:02 EDT [25407]  LOG:  received SIGHUP, reloading
> configuration files
> 2008-08-26 04:00:02 EDT [22649]  LOG:  autovacuum launcher shutting down
> 2008-08-26 04:00:02 EDT [30438]  LOG:  autovacuum launcher started
>
>
> you can see the SIGHUP, the launcher being shut down, and starting right
> back up again ???
>
> is this expected behaviour ?

Certainly not, and that's not what I see here either.  I assume process
25407 is (was) the postmaster, yes?

If you "show autovacuum", is it on?

Yes that was the postmaster, and I did check to see if autovacuum was on, and it was not.

Dave

--
Alvaro Herrera                                http://www.CommandPrompt.com/

The PostgreSQL Company - Command Prompt, Inc.

[sydpug] SydPUG meeting Wednesday September 3rd

Hello everybody,

The Sydney PostgreSQL Users Group will meet on Wednesday, September 3rd,
at 6:30PM, at Fujitsu Australia in North Sydney.

Gavin Sherry will give a talk on data warehousing and business
intelligence: what it is, why it's important and why PostgreSQL users
should be interested in it.

He'll also look at some of the issues he has faced building petabyte
range data warehouses based on PostgreSQL, how they were addressed and
why they might be problems for you very soon.

Meet in the foyer of 15 Blue St North Sydney at 6:30pm. The meeting
will be held in the View Room on level 16. Security restrictions
apply, so you will not be able to go straight up to the room. Please
call me on 0439 594 738 if there are any problems.

The building is next to the North Sydney train station exit on Blue St.

Free beer provided.

Thanks,

Charles Duffy

--
Sent via sydpug mailing list (sydpug@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/sydpug

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:

> Ok, here are the logs from last night
>
> 2008-08-26 04:00:02 EDT [25407] LOG: received SIGHUP, reloading
> configuration files
> 2008-08-26 04:00:02 EDT [22649] LOG: autovacuum launcher shutting down
> 2008-08-26 04:00:02 EDT [30438] LOG: autovacuum launcher started
>
>
> you can see the SIGHUP, the launcher being shut down, and starting right
> back up again ???
>
> is this expected behaviour ?

Certainly not, and that's not what I see here either. I assume process
25407 is (was) the postmaster, yes?

If you "show autovacuum", is it on?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgadmin-support] Error Message: ERROR: column "datpath" does not exist at character 22

On Tue, Aug 26, 2008 at 1:46 PM, salheed albert <salbert@mtechcomm.com> wrote:
> Name: pgAdmin II
> Version: 1.6.0
> Descripton:
...
> Version: 8.2.1
> Descripton: PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)
...
> Description: ERROR: column "datpath" does not exist at character 22
> Number: -2147467259
> Routine: pgAdmin II:frmMain.tvServer

pgAdmin II has not been supported for many years and almost certainly
will give errors with PostgreSQL 8.x. Please upgrade to pgAdmin III.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: [ADMIN] Bug introduced in 8.0

On Tue, Aug 26, 2008 at 08:21:53AM -0300, Sergio Gabriel Rodriguez wrote:

> "Fix bug introduced in 8.0 that could allow ReadBuffer to return an
> already-used page as new, potentially causing loss of
> recently-committed data (Tom)"
> http://www.postgresql.org/docs/8.0/static/release-8-0-6.html
>
> I know an upgrade is a best solution but it's impossible now for me (I
> do not have authorization), my answer is, what exactly means this bug?
> is serious?

Yes, it's serious. It means that you can lose data that has been
committed.

Get authorization. You can install the latest version of the 8.0.x
software directly in place, with no dump or restore. It's a drop-in
replacement.

The minor releases are security and stability releases. If your
management thinks that they are optional to install, then get that in
writing, because they are instructing you to run known-dangerous
software. They are being negligent. Patching your software for known
defects is not "upgrading", it's "doing your job."

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On Tue, Aug 26, 2008 at 5:31 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

> I suppose that spirit is quite evident in the documentation.
All kidding aside, the problem that you are having IS recognized as a
weakness with PostgreSQL. This is why some are already working on
solving the problem of in place upgrades. Some time in the future
perhaps > 8.4 this will be a mute point. In the meantime, there are
other workarounds (which of course can be complex) to mitigate this
problem.

> Why make it easy or easily understandable when you can win fanatical
> fans by requiring them to invest months of their time!

I sense your frustration. But it is important to remember that the
PostgreSQL project is largely supported by volunteers. I am sure that
you agree that no one intentionally designs any give task to be more
complicated than necessary. As a side note there are many useful "how
to" articles on the web. Many of these HOWTOs were created by users
(like yourself) that have faced a difficult problem with no apparent
solution. However, after struggling with the problem and then finding
a solution, they document it for all to benefit from. Perhaps some
good with come from overcoming this challenge.

So in the mean time, the best advice is not to rush time upgrade but
take your time to insure that you do it right the first time.
Some useful article that you should consider are:
http://www.depesz.com/index.php/2008/05/05/error-operator-does-not-exist-integer-text-how-to-fix-it/
http://people.planetpostgresql.org/greg/index.php?/archives/136-Upgrading-to-8.3-MediaWiki-lessons-learned.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On Mon, Aug 25, 2008 at 08:36:34PM -0600, Scott Marlowe wrote:

> Slony replication lets postgresql accomplish this, which is really
> quite impressive.

Pleased as I am to hear accounts of Slony being used successfully to
solve the upgrade problem -- it was one of our design goals in the
early discussions at Afilias -- I have to confess that if you find
Postgres administration arcane, Slony administration is going to seem
very like interpreting runes.

I have heard that Londiste (in the skytools package) can also do this,
and it is intended to be easier to administer. I have no personal
experience trying it.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [PERFORM] Large number of tables slow insert

On Sat, 23 Aug 2008, Loic Petit wrote:
> I use Postgresql 8.3.1-1 to store a lot of data coming from a large amount of sensors. In order to have good
> performances on querying by timestamp on each sensor, I partitionned my measures table for each sensor. Thus I create
> a lot of tables.

As far as I can see, you are having performance problems as a direct
result of this design decision, so it may be wise to reconsider. If you
have an index on both the sensor identifier and the timestamp, it should
perform reasonably well. It would scale a lot better with thousands of
sensors too.

Matthew

--
And why do I do it that way? Because I wish to remain sane. Um, actually,
maybe I should just say I don't want to be any worse than I already am.
- Computer Science Lecturer

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] pg_dump roles support

--- postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c 2008-01-30 19:35:55.000000000 +0100
+++ postgresql-8.3.1/src/bin/pg_dump/pg_dump.c 2008-08-26 12:26:56.000000000 +0200
@@ -208,6 +208,7 @@
const char *pgport = NULL;
const char *username = NULL;
const char *dumpencoding = NULL;
+ const char *pgrole = NULL;
const char *std_strings;
bool oids = false;
TableInfo *tblinfo;
@@ -258,6 +259,7 @@
{"no-acl", no_argument, NULL, 'x'},
{"compress", required_argument, NULL, 'Z'},
{"encoding", required_argument, NULL, 'E'},
+ {"role", required_argument, NULL, 'r'},
{"help", no_argument, NULL, '?'},
{"version", no_argument, NULL, 'V'},

@@ -302,7 +304,7 @@
}
}

- while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:",
+ while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:r:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
@@ -374,6 +376,10 @@
pgport = optarg;
break;

+ case 'r': /* role */
+ pgrole = optarg;
+ break;
+
case 'R':
/* no-op, still accepted for backwards compatibility */
break;
@@ -539,6 +545,18 @@
exit(1);
}
}
+
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ PQExpBuffer roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ PGresult *res = PQexec(g_conn, roleQry->data);
+ check_sql_result(res, g_conn, roleQry->data, PGRES_COMMAND_OK);
+
+ PQclear(res);
+ destroyPQExpBuffer(roleQry);
+ }

/*
* Get the active encoding and the standard_conforming_strings setting, so
@@ -771,6 +789,8 @@
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" -r, --role set role before dump\n"));
+

printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
Hello,

Stephen Frost wrote:
> As I discuss above, it'd be really nice have a --role or similar option
> to ask pg_dump to set role to a particular user before dumping the
> database.

I created a patch to set the role to a specified name just after the db connection.
Please review it for possible upstream inclusion.

Regards,
Laszlo Benedek

[pgadmin-support] Error Message: Invalid procedure call or argument

-- System Information --

Platform:               Windows XP
Version:                5.1
Build:          2600 Service Pack 2

-- Application Information --

Name:           pgAdmin II
Version:                1.6.0
Descripton:    

Name:           pgSchema
Version:                1.6.0
Descripton:     PostgreSQL Schema Objects v1.6.0

-- Database Information --

Version:                8.2.1
Descripton:     PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

-- Driver Information --

Name:           PostgreSQL
Version:                7.3.100
Descripton:     PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

-- Error Information --

Description:    Invalid procedure call or argument
Number:                 5
Routine:                pgAdmin II:frmMain.svServer

************************************************************

Insert your comment:

[pgadmin-support] Error Message: ERROR: column "datpath" does not exist at character 22

-- System Information --

Platform:               Windows XP
Version:                5.1
Build:          2600 Service Pack 2

-- Application Information --

Name:           pgAdmin II
Version:                1.6.0
Descripton:    

Name:           pgSchema
Version:                1.6.0
Descripton:     PostgreSQL Schema Objects v1.6.0

-- Database Information --

Version:                8.2.1
Descripton:     PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

-- Driver Information --

Name:           PostgreSQL
Version:                7.3.100
Descripton:     PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

-- Error Information --

Description:    ERROR:  column "datpath" does not exist at character 22
Number:                 -2147467259
Routine:                pgAdmin II:frmMain.tvServer

************************************************************

Insert your comment:

[pgsql-es-ayuda] tablas por esquema

Hola,
Quisiera saber la forma d presentar las tablas por esquema en psql??
 
Tengo 3 esquemas, esquema1, esquema2, public.
 
Necesito ver si existe una instrucción q me diga algo:
 
\dt esquema1 -> con esto digo q me despliegue las tablas del esquema1
 
Nota: hago la pregunta sólo por curiosidad.
 
 


<html><div>ing. José Fermín Francisco Ferreras <BR>San Francisco de Macorís, Rep. Dom. <BR></div></html>

---

Your PC, mobile phone, and online services work together like never before. See how Windows® fits your life.

Re: [BUGS] Problem with planer

> can you share the PostgreSQL version you are using
PG8.3.1
>maybe you are facing some sort of corruption?
I do not know. All other works fine except that.

----- Original Message -----
From: "Hans-Juergen Schoenig" <postgres@cybertec.at>
To: <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, August 26, 2008 1:15 PM
Subject: Re: [BUGS] Problem with planer

> Eugen.Konkov@aldec.com wrote:
>> select ats.id, ap.value from akh_test_suit ats
>> LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id
>> where ats.ID = 472
>> id | value
>> 472 | 472
>> ID -- integer
>> value -- text
>> select * from akh_test_suit ats
>> LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id
>> where ats.ID = 472 and ap.value::integer = ats.ID
>> ERROR: invalid input syntax for integer: "--username sergeiz --password
>> sergeiz --non-interactive svn://sergeiz"
>> akh_properties.values has non numeric values, but those rows do not (MUST
>> NOT) participate in results as showed in first query
>> Why PG check them?
>
> can you share the PostgreSQL version you are using with us?
> maybe you can compile a test case?
> or maybe you are facing some sort of corruption? what happens after dump /
> reload?
>
> many thanks,
>
> hans
>
> --
> Cybertec Schönig & Schönig GmbH
> PostgreSQL Solutions and Support
> Gröhrmühlgasse 26, A-2700 Wiener Neustadt
> Tel: +43/1/205 10 35 / 340
> www.postgresql-support.de, www.postgresql-support.com
>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On Tue, Aug 26, 2008 at 5:02 AM, Tomasz Ostrowski
<tometzky@batory.org.pl> wrote:

> http://www.slony.info/documentation/versionupgrade.html
> I think nobody would guide you step by step.

Well they may, then is nothing wrong with asking especially when
"breaking new ground." Since my databases are currently pretty small,
a dump and reload is not a problem. However, I am following this
tread with keen interest for future reference. Perhaps others are
also?

> Either read documentation
> and do it yourself or hire an expert:
> http://www.postgresql.org/support/professional_support

Reading the documentation is always good advice. Ofcourse, I think
that DBA/SA wants and should be able to perform all tasks associated
with maintaining a database. However, I very much agree that buying a
support aggreement for "mission critical" applications is an important
safety net to have.

> Find out what changes were made to your old postgresql.conf (compare it
> to the default) and make the same changes to new posgresql.conf.

I very much agree. There wasn't that much that changed between 8.2
and 8.3. Just look for the uncommented postgresql.conf setting in 8.2
and then compare with 8.3.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On 8/26/08, Tomasz Ostrowski <tometzky@batory.org.pl> wrote:
>
> I think nobody would guide you step by step. Either read documentation
> and do it yourself or hire an expert:

Thanks. I suppose that spirit is quite evident in the documentation.
Why make it easy or easily understandable when you can win fanatical
fans by requiring them to invest months of their time!

Cheers.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] gsoc, oprrest function for text search take 2

On Tue, 2008-08-26 at 12:45 +0200, Jan Urbański wrote:

> > put it in a file called selfuncs_ts.c so it is similar to the existing
> > filename?
>
> I followed the pattern of ts_parse.c, ts_utils.c and so on.
> Also, I see geo_selfuncs.c. No big deal, though, I can move it.

No don't worry. You're right, the horse has already bolted.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On 2008-08-26 13:39, Phoenix Kiula wrote:

> Ok done. Slony is installed. Now what?

http://www.slony.info/documentation/versionupgrade.html
I think nobody would guide you step by step. Either read documentation
and do it yourself or hire an expert:
http://www.postgresql.org/support/professional_support

> I was also told that the postgresql.conf settings across 8.2 and 8.3
> are different so I cannot (or should not) use the same old
> postgresql.conf for the new database install.

Find out what changes were made to your old postgresql.conf (compare it
to the default) and make the same changes to new posgresql.conf.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On Mon, Aug 25, 2008 at 1:07 PM, Dave Cramer <pg@fastcrypt.com> wrote:

On 25-Aug-08, at 10:43 AM, Alvaro Herrera wrote:

Dave Cramer wrote:

Well, I go the extra mile and kill any remaing autovac procs

Here are the logs

2008-08-25 04:00:01 EDT [32276]  LOG:  autovacuum launcher shutting down
2008-08-25 04:00:01 EDT [20526]  LOG:  autovacuum launcher started

What did you SIGHUP, the launcher or postmaster?  You need the latter.
The launcher should exit automatically at that time.

No, I am HUP'ing the postmaster then subsequently killing any autovacuums still around, which may be the problem.

I may be killing the launcher prematurely. I'll try again tonight.

Ok, here are the logs from last night

2008-08-26 04:00:02 EDT [25407]  LOG:  received SIGHUP, reloading configuration files
2008-08-26 04:00:02 EDT [22649]  LOG:  autovacuum launcher shutting down
2008-08-26 04:00:02 EDT [30438]  LOG:  autovacuum launcher started


you can see the SIGHUP, the launcher being shut down, and starting right back up again ???

is this expected behaviour ?

Dave