PostgreSQL

Friday, August 29, 2008

Re: [ADMIN] psql data restore not working

Are all of your database roles/permissons/search_path set on your new server?

--- On Fri, 8/29/08, Vishal Arora <aroravishal22@hotmail.com> wrote:

From: Vishal Arora <aroravishal22@hotmail.com>
Subject: Re: [ADMIN] psql data restore not working
To: "Daniel Punton" <compass2k@gmail.com>, pgsql-admin@postgresql.org
Date: Friday, August 29, 2008, 5:12 AM

Date: Fri, 29 Aug 2008 10:26:44 +1000
From: compass2k@gmail.com
To: pgsql-admin@postgresql.org
Subject: [ADMIN] psql data restore not working

I would be glad on advice what might be the issue (or how I could narrow it down)
with the non restoration of data from a postgres 7.1 database to a postgres 8 database.

The schema was dumped with
pg_dump -O -s databasename > database_schema_file_name
The data was dumped with
pg_dump -O -a databasename > database_data_file_name

the datafile was gzipped and transferred to another server
and gunzipped

the database was successfully created
the schema was successfully generated
psql -d databasename -f database_schema_file_name
the datafile was cleaned for the carriage return/line feed issue (between pgsql 7-> 8)
sed 's/^M/\\r/' datafilename > datafilename
I attempted to insert the data
psql -d databasename -f database_data_file_name

Have you tried psql -U<userID> databasename < database_data_file_name

no errors but when I query the data - there is none ..

Any ideas what I might be missing or how to see any errors or warnings?

Cheers
Daniel Punton
Senior Analyst/Programmer
Transact/EForms Team CSC

Searching for weekend getaways? Try Live.com Try it!

[pgadmin-hackers] SVN Commit by dpage: r7441 - trunk/pgagent

Author: dpage

Date: 2008-08-29 14:11:11 +0100 (Fri, 29 Aug 2008)

New Revision: 7441

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

Log:
Add licence

Added:
trunk/pgagent/LICENSE

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

Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.

PoolSnoopy wrote:
>
> ***PUSH***
>
> this bug is really some annoyance if you use automatic build environments.
> I'm using phpunit to run tests and as soon as postgres is involved the php
> cli environment segfaults at the end. this can be worked around by disabling
> ssl but it would be great if the underlying bug got fixed.

This is PHP's bug, isn't it? Why are you complaining here?

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

--
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] temp schemas

On Fri, Aug 29, 2008 at 1:40 PM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:

>> Why have you got thousands of them? If you are running with thousands
>> of active backends, may I suggest a connection pooler?
>>
>
> I don't know. It looks like a bug to me where a temp table is created
> and dropped on commit but the next time the function executes and
> creates a new temp table, it does this in another temp schema. It does
> this over and over until I have thousands of temp schemas that aren't
> used.

Is this vanilla PG, or your Greenplum install?

> It looks this SQL is executing when connecting with pgAdmin which
> doesn't exclude temp schemas. Even though I don't have it configured to
> show temp schemas, the SQL doesn't exclude these records. This is with
> 1.8.2 of pgAdmin.

Oh, that's weird - that code is filtering out system objects client
side for some reason. I'll look at that.

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

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

[COMMITTERS] pgsql: Remove all traces that suggest that a non-Bison yacc might be

Log Message:
-----------
Remove all traces that suggest that a non-Bison yacc might be supported, and
change build system to use only Bison. Simplify build rules, make file names
uniform. Don't build the token table header file where it is not needed.

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

[GENERAL] Full Text Search, plus some General Ideas Beforehand

Dear Madam or Sir!

I am just studying PostgreSQL 8.3.1 from a mag CD unfortunately under
MS-Windows.

Aye, it is under the BSD licence which allows for re-proprietarisation of
the code.

I lean more to the GPL, the GPL v3.x being a bit too strict in the case of
my GNU Business Model.

This provides source code and uncertified binaries for the media and
handling costs as is known for OpenSource software.

Money is made, close to the ideas of the FSF, in leasing certified binaries
that are dongled to the particular machine, in this case the GPL v3.x has
to be liberated to a kind of LGPL, which makes the binary tamper proof.

Tightly included into this licence is a generous insurance against any
damages from using this software.

ClosedSource will be unconditionally liable to any damages according to
article 14, paragraph 2 of the German Constitution, we have the concept of
guilt-free damage risk liability.

I have serviced mission-critical embedded computers, so my hard position is
understandable.

This far to the general politics.

Aye, I am an old fossil, learning computers via punched cards over 30 years
ago.

Thus I am influenced not only by XBase, but also from database theory where
indexing everything was paramount.

Your problem of updating BTrees is most visibly in ReiserFS, whose
transactional benefits are counterweighted by the overhead of maintaining
mail and news servers.

There is a fragmentation problem under ext3 for IMAP directories, but this
can be solved by tar-ing and untar-ing the appropriate directories.

So we should leave that to the planner.

However, old DEC Alpha OS's provided an FX32! adaptive IA32 emulator,
actually Sun sports an hot-spot-JIT system in JRE 1.6.x.

A strategy for the PostgreSQL planner, aye this eats space, but this might
result in a much more optimised balance between indexing speedup vs.
overhead.

Well, for Full Text Search, especially copying Google & Co., I advertise to
augment the traditional presence index by a proximity index and a
permutation index. You already have functions to generate such a proximity
index, the permutation index being the least important.

This makes the whole Internet a natural test bed for PostgreSQL, and
somehow a basis for a Google successor.

Kind regards

Norbert Grün (gnor.gpl@googlemail.com)

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

[pgadmin-hackers] SVN Commit by dpage: r7440 - trunk/pgadmin3/xtra

Author: dpage

Date: 2008-08-29 13:54:44 +0100 (Fri, 29 Aug 2008)

New Revision: 7440

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

Log:
Remove the admin/admin81 modules which aren't needed with modern versions of PG. For those that want them, they're on the downloads area of the website, and in pgAdmin 1.8.

Removed:
trunk/pgadmin3/xtra/admin/
trunk/pgadmin3/xtra/admin81/
Modified:
trunk/pgadmin3/xtra/Makefile.am

--
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] Installing PostgreSQL 8.3.3. on Vista

Steve,

That did it. Thanks!

Brandon

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Steve Holdoway
Sent: Thursday, August 28, 2008 4:45 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Installing PostgreSQL 8.3.3. on Vista

Looks like the relevant Visual C runtime library ( VC80.CRT 32 bit )
needs installing first...

Steve

On Thu, 28 Aug 2008 09:42:54 -0500
"Brandon Dybala" <bjd1@martincomp.com> wrote:

> I tried installing PostgreSQL 8.3.3 on Vista Business, and initdb
> failed to start during the installer. I'd really like to make this
> install as automatic as possible since I'm installing PostgreSQL as
> part of my application installer process. It would be best if the
> user does not have to deal with any sort of configuration for
> Postgres. I also tried running the main MSI file with the graphical
> interface, and ran into the same problem. Here is the message I
received first:
> Failed to run initdb: 1!
>
> I checked the initdb.log file it generated, and all it said was "The
> application has failed to start because its side-by-side configuration

> is incorrect. Please see the application event log for more detail."
>
> So I checked the event log, and it had the following:
>
> Log Name: Application
> Source: SideBySide
> Date: 8/28/2008 7:23:49 AM
> Event ID: 33
> Task Category: None
> Level: Error
> Keywords: Classic
> User: N/A
> Computer: VISTATEST
> Description:
> Activation context generation failed for "C:\Program
> Files\PostgreSQL\8.3\bin\initdb.exe". Dependent Assembly
> Microsoft.VC80.CRT,processorArchitecture="x86",publicKeyToken="1fc8b3b
> 9a 1e18e3b",type="win32",version="8.0.50727.762" could not be found.
> Please use sxstrace.exe for detailed diagnosis.
> Event Xml:
> <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
> <System>
> <Provider Name="SideBySide" />
> <EventID Qualifiers="49409">33</EventID>
> <Level>2</Level>
> <Task>0</Task>
> <Keywords>0x80000000000000</Keywords>
> <TimeCreated SystemTime="2008-08-28T14:23:49.000Z" />
> <EventRecordID>593</EventRecordID>
> <Channel>Application</Channel>
> <Computer>VISTATEST</Computer>
> <Security />
> </System>
> <EventData>
>
> <Data>Microsoft.VC80.CRT,processorArchitecture="x86",publicKeyToken="1
> fc 8b3b9a1e18e3b",type="win32",version="8.0.50727.762"</Data>
> <Data>C:\Program Files\PostgreSQL\8.3\bin\initdb.exe</Data>
> </EventData>
> </Event>
>
> I couldn't figure out what I was supposed to do with sxstrace.exe,
> though. The usage example showed parsing a *.etl file, but I can't
> find any of these on my system.
>
> Any help would be appreciated.
>
> Thanks,
>
> Brandon
>

--
Steve Holdoway <steve.holdoway@firetrust.com>

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

--
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] MySQL LAST_INSERT_ID() to Postgres

Magnus Hagander escribió:
> Alvaro Herrera wrote:
> > Russ Brown escribió:
> >> Masis, Alexander (US SSA) wrote:
> >>> "SELECT CURRVAL(
> >>> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
> >> Any reason why you can't just do this?
> >>
> >> CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
> >> SELECT lastval();
> >> $$ LANGUAGE SQL VOLATILE;
> >
> > If your table has a trigger that inserts into another table with its own
> > sequence, you're screwed.
>
> I assume you're equally screwed with MySQL LAST_INSERT_ID() in that case
> - so it'd be bug compatible.

Yeah, which is another reason not to use triggers; more pileups for the
whole "new features are there just for checklist's sake" argument.

The approach proposed by Alexander above does not have such problem,
which is why it is better than the alternative suggested by Russ.

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

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

[pgadmin-hackers] SVN Commit by dpage: r7439 - in trunk/pgagent: . pgaevent

Author: dpage

Date: 2008-08-29 13:49:53 +0100 (Fri, 29 Aug 2008)

New Revision: 7439

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

Log:
Build pgaevent on Windows.

Modified:
trunk/pgagent/CMakeLists.txt
trunk/pgagent/pgaevent/CMakeLists.txt

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

[pgadmin-support] Detected error in pgAdmin III v1.8.4

Hello,

I installed pgAdmin III v1.8.4 released and

detected a problem with manage privileges.

The problem is in the properties of a table on privileges tab.

The roles not appear in the "role's combobox". Just role public appear. This way I can't add privileges for roles.

Eduardo Sá dos Reis
eduardoreis@pjf.mg.gov.br

Re: [GENERAL] temp schemas

> -----Original Message-----
>
> "Roberts, Jon" <Jon.Roberts@asurion.com> writes:
> > I am noticing a large number of temp schemas in my database. We use
> > temp tables but it doesn't appear that the schemas get dropped for
some
> > reason.
>
> That's intentional. There doesn't seem a lot of value in dropping a
> catalog entry that'll just have to be created again later.
>

This is what I'm trying to understand. At what point does PostgreSQL
determine it needs to create a new temp schema versus reusing an
existing one? Maybe we are doing something incorrectly in our code.

> > This greatly slows down how long it takes pgAdmin to connect
> > because it retrieves thousands of pg_temp_% schemas.
>
> Why have you got thousands of them? If you are running with thousands
> of active backends, may I suggest a connection pooler?
>

I don't know. It looks like a bug to me where a temp table is created
and dropped on commit but the next time the function executes and
creates a new temp table, it does this in another temp schema. It does
this over and over until I have thousands of temp schemas that aren't
used.

On Wednesday, we had 170,243 temp schemas and today, we have 173,384.

> (It might be a good idea to fix pgAdmin so it ignores other sessions'
> temp schemas, though.)
>

It looks this SQL is executing when connecting with pgAdmin which
doesn't exclude temp schemas. Even though I don't have it configured to
show temp schemas, the SQL doesn't exclude these records. This is with
1.8.2 of pgAdmin.

SELECT CASE WHEN nspname LIKE E'pg\\_temp\\_%%' THEN 1
WHEN (nspname LIKE E'pg\\_%') THEN 0
ELSE 3 END AS nsptyp,
nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS
namespaceowner, nspacl, description, has_schema_privilege(nsp.oid,
'CREATE') as cancreate
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
WHERE NOT ((nspname = 'pg_catalog' and (SELECT count(*) FROM pg_class
WHERE relname = 'pg_class' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'pgagent' and (SELECT count(*) FROM pg_class WHERE relname =
'pga_job' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'information_schema' and (SELECT count(*) FROM pg_class WHERE
relname = 'tables' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'dbo' and (SELECT count(*) FROM pg_class WHERE relname =
'systables' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'sys' and (SELECT count(*) FROM pg_class WHERE relname =
'all_tables' AND relnamespace = nsp.oid) > 0))
ORDER BY 1, nspname

Jon

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

[GENERAL] warning: libssl.so.4, needed by

Building my application and got the following error.

/usr/bin/ld: warning: libssl.so.4, needed by ../../../external/postgres/lib/libpq.so, not found (try using -rpath or -r
path-link)
/usr/bin/ld: warning: libcrypto.so.4, needed by ../../../external/postgres/lib/libpq.so, not found (try using -rpath or
-rpath-link)
/usr/bin/ld: warning: libldap_r-2.2.so.7, needed by ../../../external/postgres/lib/libpq.so, not found (try using -rpat
h or -rpath-link)
../../../external/postgres/lib/libpq.so: undefined reference to `TLSv1_method'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_set_ex_data'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_connect'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_STORE_load_locations'
../../../external/postgres/lib/libpq.so: undefined reference to `BIO_free'
../../../external/postgres/lib/libpq.so: undefined reference to `BIO_ctrl'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_get_values_len'
../../../external/postgres/lib/libpq.so: undefined reference to `CRYPTO_set_locking_callback'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_NAME_oneline'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_simple_bind'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_set_verify'
../../../external/postgres/lib/libpq.so: undefined reference to `PEM_read_bio_PrivateKey'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_load_error_strings'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_STORE_set_flags'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_get_peer_certificate'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_get_subject_name'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_free'
../../../external/postgres/lib/libpq.so: undefined reference to `PEM_read_bio_X509'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_unbind'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_result'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_count_entries'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_NAME_get_text_by_NID'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_new'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_read'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_value_free_len'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_set_fd'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_pending'
../../../external/postgres/lib/libpq.so: undefined reference to `ENGINE_load_private_key'
../../../external/postgres/lib/libpq.so: undefined reference to `CRYPTO_num_locks'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_first_entry'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_load_verify_locations'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_init'
../../../external/postgres/lib/libpq.so: undefined reference to `ERR_get_error'
../../../external/postgres/lib/libpq.so: undefined reference to `ERR_reason_error_string'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_msgfree'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_library_init'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_err2string'
../../../external/postgres/lib/libpq.so: undefined reference to `CRYPTO_set_id_callback'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_get_ex_data'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_new'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_write'
../../../external/postgres/lib/libpq.so: undefined reference to `OPENSSL_config'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_get_cert_store'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_check_private_key'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_free'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_shutdown'
../../../external/postgres/lib/libpq.so: undefined reference to `BIO_new_file'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_search_st'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_get_error'
../../../external/postgres/lib/libpq.so: undefined reference to `ENGINE_by_id'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_free'
collect2: ld returned 1 exit status

Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail

[pgadmin-hackers] SVN Commit by dpage: r7438 - in trunk/pgagent: . pgaevent

Author: dpage

Date: 2008-08-29 13:39:56 +0100 (Fri, 29 Aug 2008)

New Revision: 7438

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

Log:
Build pgaevent

Added:
trunk/pgagent/pgaevent/CMakeLists.txt
Modified:
trunk/pgagent/CMakeLists.txt
trunk/pgagent/pgaevent/pgamsgevent.rc

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

Thursday, August 28, 2008

Re: [pgsql-es-ayuda] Problemas al concatenar

alter table test1 alter column a type character varying(4);
El día 28 de agosto de 2008 10:43, Alejandro D. Burne
<alejandro.dburne@gmail.com> escribió:
> Necesito concatenar 2 cadenas para ello tengo una tabla con un campo
> caracter(4).
> El problem que se me presenta es que me elimina los espacios en blanco
> del campo de la tabla, ahora si utilizo una cadena no tengo problemas.
> Alguna pista?
>
> PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.4
>
> DB=# CREATE temporary TABLE test1 (a character(4));
> CREATE TABLE
>
> DB=# INSERT INTO test1 VALUES ('ok ');
> INSERT 0 1
>
> DB=# SELECT a||'A', 'ok '||'A' FROM test1;
> ?column? | ?column?
> ----------+----------
> okA | ok A
> (1 row)
>

Si usaras un character varying(4) tambien se comporta de igual forma
??? hice la prueba con la version 8.3 y al usar character varying(4)
respeta el espacio de la cadena 'ok '...

saludos.-
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html

Re: [pgsql-es-ayuda] sobre libro de cvs y postgresql

Moises Galan dijo [Wed, Aug 27, 2008 at 05:09:37PM -0500]:
> Hola, alguien sabe donde puedo conseguir el libro "Desarrollo de
> Código Abierto con CVS", version completa y descargable en español ,
> descargue el codigo fuente de postgresql pero quiero ubicarlo en otro
> directorio. y trabajar sobre un modulo solamente. por lo que necesito
> conocer los comandos de CVS. He visitado varias paginas pero aparecen
> sin la opcion para descargar en PDF. en algunas paginas los links no
> funcionan.
>
> Hice la instalacion del CVS de postgresql guiandome por la
> documentacion, pero necesito mas informacion sobre comandos.

Umh... Te recomiendo fuertemente _no_ aprender específicamente sobre
CVS. Me sorprende ver que el desarrollo de PostgreSQL sigue estando
sobre CVS, que si bien cumple su tarea, conlleva una serie de
restricciones sencillamente por el modelo sobre el cual está
construído - CVS te permite dar seguimiento de manera independiente a
muchos archivos dentro de un depósito, pero sin coherencia real entre
unos y otros; es imposible renombrar archivos; si borras un archivo,
tienes que borrar su historia también; es un modelo completamente
centralizado...

Hoy en día, muy por lo menos, te recomiendo asomarte a SVN - o mucho
mejor aún, a los sistemas de control de versiones distribuídos, como
Git, Bazaar o Mercurial.

Saludos,

--
Gunnar Wolf - gwolf@gwolf.org - (+52-55)5623-0154 / 1451-2244
PGP key 1024D/8BB527AF 2001-10-23
Fingerprint: 0C79 D2D1 2C4E 9CE4 5973 F800 D80E F35A 8BB5 27AF
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

[GENERAL] Need help with simple update / insert trigger

I have table sort of like this:

create table snoz (
a bool default false,
b bool default false,
c bool default false
);

I want to set up a trigger so that any time a row is inserted or
updated, the value of c is set to true if a and/or b is true.

How can I do this?

Thanks!

Matt

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

[DOCS] Doc patch for truncate.sgml

Index: doc/src/sgml/ref/truncate.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/truncate.sgml,v
retrieving revision 1.27
diff -c -r1.27 truncate.sgml
*** doc/src/sgml/ref/truncate.sgml 17 May 2008 23:36:27 -0000 1.27
--- doc/src/sgml/ref/truncate.sgml 28 Aug 2008 14:58:16 -0000
***************
*** 34,40 ****
<command>DELETE</command> on each table, but since it does not actually
scan the tables it is faster. Furthermore, it reclaims disk space
immediately, rather than requiring a subsequent <command>VACUUM</command>
! operation. This is most useful on large tables.
</para>
</refsect1>

--- 34,44 ----
<command>DELETE</command> on each table, but since it does not actually
scan the tables it is faster. Furthermore, it reclaims disk space
immediately, rather than requiring a subsequent <command>VACUUM</command>
! operation. This is most useful on large tables. Also,
! <command>TRUNCATE</command> rewrites system catalogue entries for
! that table, which makes running <command>ANALYZE</command> on a
! freshly-truncated table is a bad idea, because the statistics will be
! updated to indicate that the table is truly empty.
</para>
</refsect1>

Attached is a doc patch for truncate.sgml. It improves info for
TRUNCATE. Thanks to Andrew Sullivan for pointing out this.

This is against head, but could be backpatched, too, I believe.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [HACKERS] September Commit Fest coming soon!

Gregory Stark <stark@enterprisedb.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Yes. You should *not* assume that reviewers are going to look at the
>> git repository.

> However when a committer is actually looking to commit the code he'll want to
> be sure he has the most recent snapshot including any later changes that might
> not have warranted an email.

I'm going to state for the record that I have no intention of digging
around in unofficial repositories for unsubmitted changes (that might
or might not have been intended to go in anyway). If you don't like
what I commit, send a delta patch.

regards, tom lane

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

[GENERAL] pg_dumpall problem when roles have default schemas

I've run into a problem while migrating an existing 8.2.7 data base to a new server running 8.3.3 (although I think the version numbers may not matter -- I think I've seen this problem in the past and just lived with it since so much of Postgresql is so great!).

The problem stems from the fact that for certain roles, I have defined default search paths, and because the output of pg_dumpall includes role definitions first, then data base definitions, then schema definitions.

Thus, when piping the output (from legacy host 192.168.2.2) to populate the newly initialized cluster, by way of running (on the new host 192.168.2.3)

pg_dumpall -h 192.168.2.2|psql

an error occurs in that first section when the script attempts to set a role-specific search path ... because the schema named in the search path hasn't been created yet.

Not sure if there is some different way I should be using these tools to accomplish this, or if there is a work-around, or if this feature needs improvement.

Re: [GENERAL] upgrading from 8.1.4 to 8.3.3

On Thu, 2008-08-28 at 10:53 +0100, Joao Ferreira gmail wrote:
> shell> su postgres -c "/usr/bin/postmaster -D /var/pgsql/data"
> FATAL: database files are incompatible with server
> DETAIL: The data directory was initialized by PostgreSQL version 8.1,
> which is not compatible with this version 8.3.3.

http://www.postgresql.org/docs/8.3/static/install-upgrading.html

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [HACKERS] code coverage patch

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> We're pretty much assuming bison anyway, no? It's been years since
>> I heard of anyone successfully building the backend grammar with plain
>> yacc.

> In my recollection, you were the last holdout on that with the
> occasional HP-UX yacc test. But I seem to recall that that combination
> actually no longer worked the last time.

I don't think I've tried that in this century ;-). Between the sheer
size of the grammar and the fact that we're already depending on the
behavior of several arcane %-options, I really doubt any tool besides
bison will work. Besides, the whole point of shipping the built files
in tarballs is to ensure no one has to use any other tool.

regards, tom lane

--
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] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

On Aug 28, 2008, at 6:26 AM, Matthew Wakeling wrote:

> On Wed, 27 Aug 2008, david@lang.hm wrote:
>> if memory overcommit is disabled, the kernel checks to see if you
>> have an extra 1G of ram available, if you do it allows the process
>> to continue, if you don't it tries to free memory (by throwing away
>> cache, swapping to disk, etc), and if it can't free the memory will
>> return a memroy allocation error (which I believe will cause
>> firefox to exit).
>
> Remember that the memory overcommit check is checking against the
> amount of RAM + swap you have - not just the amount of RAM. When a
> fork occurs, hardly any extra actual RAM is used (due to copy on
> write), but the potential is there for the process to use it. If
> overcommit is switched off, then you just need to make sure there is
> *plenty* of swap to convince the kernel that it can actually fulfil
> all of the memory requests if all the processes behave badly and all
> shared pages become unshared. Then the consequences of processes
> actually using that memory are that the machine will swap, rather
> than the OOM killer having to act.
>
> Of course, it's generally bad to run a machine with more going on
> than will fit in RAM.
>
> Neither swapping nor OOM killing are particularly good - it's just a
> consequence of the amount of memory needed being unpredictable.
>
> Probably the best solution is to just tell the kernel somehow to
> never kill the postmaster.

Or configure adequate swap space?

Cheers,
Steve

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

Re: [GENERAL] pg_dump problem

On Thursday 28 August 2008 4:32:56 am Laura Del Caño wrote:
> Hi,
> I am having a problem with pg_dump and I could not find any reference
> in the archive mailing lists.
> I am issuing the following command:
>
> pg_dump -c -o -s -n distributed -f backups/schema.sql syslog
>
> which apparently works fine. It is only when I tried to restore it in
> a fresh created database using:
>
> psql syslog < backups/schema.sql
>
> that I get lots of errors saying objects do not exist.
>
> Having a look at the schema.sql file I see the following:
>
> SET search_path = distributed, pg_catalog;
> ...
> CREATE SCHEMA distributed;
> ...
> CREATE FUNCTION facility_id(text) RETURNS integer
> AS $_$select id from distributed.facilities where facility = $1$_$
> LANGUAGE sql STABLE SECURITY DEFINER;
>
>
> ALTER FUNCTION distributed.facility_id(text) OWNER TO postgres;
> ...
>
> so I see that the function is created OUT of the schema distributed,
> and the ALTER is run on the same function that should be WITHIN schema
> distributed.
> (and when checking the function in the DB I indeed see it was created
> in the public schema)
>
> Same happens with all the objects in the schema.
>
> Obviously this is a big problem, maybe I am missing something...
> Is there maybe a way to set the search_path only to the schema I dumped?
> (maybe using -N pg_catalog to exclude that schema).
>

From the manual
http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html

Note: When -n is specified, pg_dump makes no attempt to dump any other
database objects that the selected schema(s) might depend upon. Therefore,
there is no guarantee that the results of a specific-schema dump can be
successfully restored by themselves into a clean database.

> Thanks in advance for your help,
> Laura

--
Adrian Klaver
aklaver@comcast.net

--
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] code coverage patch

Tom Lane wrote:
> We're pretty much assuming bison anyway, no? It's been years since
> I heard of anyone successfully building the backend grammar with plain
> yacc.

In my recollection, you were the last holdout on that with the
occasional HP-UX yacc test. But I seem to recall that that combination
actually no longer worked the last time.

If no one else has any interest in maintaining other-yacc support, then
we might as well drop it.

--
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] September Commit Fest coming soon!

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Abhijit Menon-Sen <ams@oryx.com> writes:
>> At 2008-08-28 20:09:21 +0900, umi.tanuki@gmail.com wrote:
>>> For the git hosted projects, should we send in the latest patch file
>>> to this list?
>
>> Yes, please do that.
>
> Yes. You should *not* assume that reviewers are going to look at the
> git repository.

I think we should include both. They serve two different purposes. The GIT
repository might disappear some day or be inaccessible for some users. Also
you might be asking for commentary on specific questions about that specific
snapshot.

However when a committer is actually looking to commit the code he'll want to
be sure he has the most recent snapshot including any later changes that might
not have warranted an email.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

--
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] 8.3.1 query plan

Steve Clark <sclark@netwolves.com> writes:
> Tom Lane wrote:
>> Consider testing the conditions on A at the top level, instead of
>> redundantly checking them inside the sub-query on B.

> Thanks for the response Tom, I am a SQL neophyte, so I'll try to
> rework the query.

What I meant to suggest was just

explain insert into myevents select * from t_unit_event_log a where
a.event_status = 1 and a.event_ref_log_no IS NOT NULL
and a.event_log_no not in (select event_log_no from myevents)
and exists (select b.event_log_no from myevents b
where a.event_ref_log_no = b.event_log_no)

ie, pull everything out of the subquery that doesn't depend on B.

Although, looking at it in this form, it seems like you'd be well
advised to then replace the EXISTS with an IN:

... and a.event_ref_log_no in (select b.event_log_no from myevents b)

Although those two forms should be equivalent, reality is that current
releases of PG are generally smarter about optimizing IN than EXISTS.
(The difference should largely go away as of 8.4.)

regards, tom lane

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

[pdxpug] Fwd: [pgsql-jobs] Please post this position to your group

Re: [GENERAL] Vaccuum best practice: cronjob or autovaccuum?

On Thu, Aug 28, 2008 at 9:47 AM, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:
>
> http://www.postgresql.org/docs/8.3/static/maintenance.html
>
> you'll find that once in a while (start at once/week and build up or
> down from there) you can/should:
>
> - vacuum full
> - reindex your tables
> - reindex your indexes
>

Actually the manuals doesn't recomend VACUUM FULL for routine
maintenance anymore
"""
For those not using autovacuum, one approach is to schedule a
database-wide VACUUM once a day during low-usage period, supplemented
by more frequent vacuuming of heavily-updated tables if necessary.
(Some installations with extremely high update rates vacuum their
busiest tables as often as once every few minutes.) If you have
multiple databases in a cluster, don't forget to VACUUM each one; the
program vacuumdb might be helpful.

VACUUM FULL is recommended for cases where you know you have deleted
the majority of rows in a table, so that the steady-state size of the
table can be shrunk substantially with VACUUM FULL's more aggressive
approach. Use plain VACUUM, not VACUUM FULL, for routine vacuuming for
space recovery.
"""

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. (593) 87171157

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

[GENERAL] WAL file questions - how to relocate on Windows, how to replay after total loss, etc

BACKGROUND INFO BEGINS

Recently I had some questions about doing backups and received very helpful replies. I have now put together a BAT file to do a routine backup, using pg_dumpall with the -g option to get the roles, and pg_dump with the custom format to get all the data.

I am now testing this process to make sure it is possible to recover data up to the last minute in the event of a catastrophic server failure.

I should mention that I'm running PostgreSQL 8.2 on a Windows computer (Win2K for testing) and that I am developing and testing this procedure because I do software development for clients using PG at my recommendation. They are small companies who don't use Unix. In fact, the PG server is likely to be one of the office workstations.

To test, I ran my backup procedure, added a row to a table, then pressed reset on the workstation/server to simulate the catastrophe.

When the computer booted, the server started automatically, so I shut it down. I then renamed the data folder to simulate complete loss of the computer, and I created a new cluster and database and did a restore of the roles and then the data. (My data had an invalid UTF8 character so, thanks to help given here, I extracted that table to plain text, ran iconv on it, and loaded the problem table.)

So now I have everything back except that row I added after doing the backup.

BACKGROUND INFO ENDS, QUESTIONS BEGIN

How to replay WAL files: Now that I have a freshly created and reloaded database, how do I replay the WAL files from the pretend crashed server, now in a renamed folder? I understand I create a recover.conf file and restart the server. But I don't understand the process. I am to put a copy command in the file to copy wal files from the renamed folder, but how does the server know what files to copy? Is something else needed in the recover.conf file? By the way, when I restarted the server, the file was renamed to recover.done but the missing row didn't appear.

On Windows, is it possible to relocate the wal files to another hard drive?

Does pg_dump do a checkpoint?

How do I make sure the wal files are not discarded before another backup is done?

How to release wal files after the backup is done?

The first two questions I could find no answer to. The others I admit I haven't researched very long as I was more interested in the first question.

Thanks

John

--
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] September Commit Fest coming soon!

Abhijit Menon-Sen <ams@oryx.com> writes:
> At 2008-08-28 20:09:21 +0900, umi.tanuki@gmail.com wrote:
>> For the git hosted projects, should we send in the latest patch file
>> to this list?

> Yes, please do that.

Yes. You should *not* assume that reviewers are going to look at the
git repository.

regards, tom lane

--
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] What happend to equality_oper()

Bernd Helmle <mailings@oopsware.de> writes:
> I've used above function to construct OpExpr expressions with equality
> operators in the past, but it seems it was removed and there's no
> replacement for it?

Look at get_sort_group_operators() instead. Or use the type cache
directly.

regards, tom lane

--
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] select on 22 GB table causes"An I/O error occured while sending to the backend." exception

>>> Bill Moran <wmoran@collaborativefusion.com> wrote:
> In response to Matthew Wakeling <matthew@flymine.org>:
>>
>> Probably the best solution is to just tell the kernel somehow to
never
>> kill the postmaster.
>
> This thread interested me enough to research this a bit.
>
> In linux, it's possible to tell the OOM killer never to consider
> certain processes for the axe, using /proc magic. See this page:
> http://linux-mm.org/OOM_Killer
>
> Perhaps this should be in the PostgreSQL docs somewhere?

That sure sounds like a good idea.

Even though the one time the OOM killer kicked in on one of our
servers, it killed a runaway backend and not the postmaster
( http://archives.postgresql.org/pgsql-bugs/2008-07/msg00105.php ),
I think I will modify our service scripts in /etc/init.d/ to pick off
the postmaster pid after a start and echo -16 (or some such) into the
/proc/<pid>/oom_adj file (which is where I found the file on my SuSE
system).

Thanks for the research and the link!

-Kevin

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

Re: [GENERAL] Vaccuum best practice: cronjob or autovaccuum?

On Thu, 2008-08-28 at 19:53 +0800, Phoenix Kiula wrote:
> On our database of about 5GB we vaccuum all of our 12 tables (only one
> is huge, all others have about 100,000 rows or so) every hour or so.

if you refer to manual VACUUM or VACUUM FULL every hour is probably too
much. You should aim your vacuum full for about 1ce per week.

>
> But we also have autovaccuum enabled. Is this okay? Do the two vaccuum
> processes contradict each other, or add unnecessary load to the
> system?

read the manuals in www.postgresql.org

specifically read this seciton:

http://www.postgresql.org/docs/8.3/static/maintenance.html

you'll find that once in a while (start at once/week and build up or
down from there) you can/should:

- vacuum full
- reindex your tables
- reindex your indexes

>
> The reason we introduced the cronjob we felt was that the autovaccuum
> was not really doing its job.

how did you realise that ? turn off the cron job, wait a few days. In
the meanwhile monitor your disk space ocupation (du
-sh /var/lib?/pgsql....????/base/)

if you see that size growing and the total row count (select count(*)
from whatever) isn't gorwing you need external vacuums

> I wonder if anyone can share some
> insight on whether these settings are good for a DB that is basically
> 24x7:

like someone sayd: it's not the 24x7. it's the: how many tuples get
DELETEd or UPDTATEd (for the case of autovacuum) in one day, for
example.

If you find that your db updates/deletes many tuples per hour

>
> autovacuum = on
> autovacuum_vacuum_cost_delay = 20
> vacuum_cost_delay = 20
> autovacuum_naptime = 10
> stats_start_collector = on
> stats_row_level = on
> autovacuum_vacuum_threshold = 75
> autovacuum_analyze_threshold = 25

> autovacuum_analyze_scale_factor = 0.02
> autovacuum_vacuum_scale_factor = 0.01

these two can be tricky: if your database is very big, then 2% of 'very
big' is 'a lot of changes' before autovacuum even tries to vacuum.

read the documentation about these two.

you might want to consider using scale factors of 0 and increase just a
bit both thresholds; p. ex

autovacuum_vacuum_threshold = 20000
autovacuum_analyze_threshold = 10000

Autovacuum is something that you adjust to your needs;

Another tip: edit your postgresql.conf and bring the debug levels to:

log_min_messages = debug3
log_min_error_statement = debug3

then just follow the logfile (location depends on
distro; /var/log/postgresql; /var/pgsql/data/; whatever)

IMPORTANT: don't leave the debug3 forever: it is very verbose and _will_
eat up your disc in no time;

you might want to do something like this:

tail -f logfile | grep vac

tail -f logfile | grep threshold

take your time :) autovacuum requires a bit of dedication but, in the
end it works fine (preventig disc space growth)

note also that VACUUM has nothing to do with REINDEXing and REINDEXing
also frees a considerable amount of disk space in certain cases.

very important: read the docs:

http://www.postgresql.org/docs/8.3/static/maintenance.html

In my case I have autovaccum with scale factors 0 and naptime 600; also
a cron job for vacuum full and reindex everything once a week (during
the night). its working fine on a db with about 2 Giga and average 10000
deletes a day and well above 200000 INSERTs/UPDATEs per day.

cheers
joao

PS: I'm saying all this because I'm just going through this process
myself and I think I'm on the right track. things are starting to work
the way I want them too :)

>
> Thanks! I've read up some websites for this kind of tuning, but often
> the info is for older versions of DBs and some of the tweaks have
> changed since. I am on 8.2.9.
>

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

[pgsql-es-ayuda] Problemas al concatenar

Necesito concatenar 2 cadenas para ello tengo una tabla con un campo
caracter(4).
El problem que se me presenta es que me elimina los espacios en blanco
del campo de la tabla, ahora si utilizo una cadena no tengo problemas.
Alguna pista?

PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.4

DB=# CREATE temporary TABLE test1 (a character(4));
CREATE TABLE

DB=# INSERT INTO test1 VALUES ('ok ');
INSERT 0 1

DB=# SELECT a||'A', 'ok '||'A' FROM test1;
?column? | ?column?
----------+----------
okA | ok A
(1 row)

Saludos, Alejandro
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Consulta sobre index

2008/8/28 Lennin Caro <lennin.caro@yahoo.com>:
> 1-como se cual es el indice que usara el query plan?

siendo el indice creado para el primary key un indice unique yo
creeria que este siempre ganara

> 2-se puede eliminar el indice creado implicitamente por postgresql al crear la PK?

no. y para que querrias hacer eso?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. (593) 87171157
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

[ADMIN] Installing PostgreSQL 8.3.3. on Vista

I tried installing PostgreSQL 8.3.3 on Vista Business, and initdb failed to start during the installer. I'd really like to make this install as automatic as possible since I'm installing PostgreSQL as part of my application installer process. It would be best if the user does not have to deal with any sort of configuration for Postgres. I also tried running the main MSI file with the graphical interface, and ran into the same problem. Here is the message I received first:

Failed to run initdb: 1!

I checked the initdb.log file it generated, and all it said was

"The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more detail."

So I checked the event log, and it had the following:

Log Name:      Application
Source:        SideBySide
Date:          8/28/2008 7:23:49 AM
Event ID:      33
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      VISTATEST
Description:
Activation context generation failed for "C:\Program Files\PostgreSQL\8.3\bin\initdb.exe". Dependent Assembly Microsoft.VC80.CRT,processorArchitecture="x86",publicKeyToken="1fc8b3b9a1e18e3b",type="win32",version="8.0.50727.762" could not be found. Please use sxstrace.exe for detailed diagnosis.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
    <Provider Name="SideBySide" />
    <EventID Qualifiers="49409">33</EventID>
    <Level>2</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2008-08-28T14:23:49.000Z" />
    <EventRecordID>593</EventRecordID>
    <Channel>Application</Channel>
    <Computer>VISTATEST</Computer>
    <Security />
</System>
<EventData>
    <Data>Microsoft.VC80.CRT,processorArchitecture="x86",publicKeyToken="1fc8b3b9a1e18e3b",type="win32",version="8.0.50727.762"</Data>
    <Data>C:\Program Files\PostgreSQL\8.3\bin\initdb.exe</Data>
</EventData>
</Event>

I couldn't figure out what I was supposed to do with sxstrace.exe, though. The usage example showed parsing a *.etl file, but I can't find any of these on my system.

Any help would be appreciated.

Thanks,

Brandon

Re: [BUGS] BUG #4383: operations in a certain row makes server process terminate with signal 11

"Marco Aurelio C. Miranda" <macmiranda@gmail.com> writes:
> the behavior I'm about to demonstrate started to happen after a forced
> reboot but fsck hasn't found anything wrong with the ext3 filesystem.

fsck doesn't even think about whether the data inside user files is
any good.

> Look what happens when I select the id field of a particular row in this
> table:

>> select id from uab_message_read where id=249222;
> id
> ------------
> 1714385506
> (1 row)

If you're lucky, this is just a corrupted index on uab_message_read.id,
and can be fixed via REINDEX. Otherwise, what you've got is a corrupted
table.

> And when I try to select any other field of this row or to update any of the
> fields or to delete the row, the client outputs the following message:
> server closed the connection unexpectedly

That part sounds more like the table row is corrupted :-(. If you
aren't in a position to restore the whole table from backup, you'll
need to try to identify and wipe out the broken row. Usually zeroing
its whole page is the easiest way to do this, though you'll want to
see what else is on that page first. Check the PG archives for
discussions of recovering from corrupted-data scenarios.

regards, tom lane

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

[COMMITTERS] stackbuilder - wizard: Use the project binary and osurce directories,

Log Message:
-----------
Use the project binary and osurce directories, not the current ones

Modified Files:
--------------
wizard:
CMakeLists.txt (r1.16 -> r1.17)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/CMakeLists.txt.diff?r1=1.16&r2=1.17)

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

Re: [ADMIN] Checking Postgres Version Question

2008/8/28 John <lists@johndubchak.com>:
> Hi,
>
> Is there a way to check which version of Postgres is installed on a
> particular Linux box without query the RPM database?
>
> For instance, is there a "psql --version" command, or something similar,
> that I can use?
>
> Thanks,
> John
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

select version();

(for the server version)

psql --version

(for the client version)

Peter

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

Re: [SQL] uuid-ossp contrib in pg 8.1

James Neethling wrote:
> I want to add uuid support to a postgres 8.1 installation. Can I install
> 8.3 uuid-contrib into 8.1?

8.1 does not have a uuid type, so this won't work.

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

Re: [GENERAL] Feature Request: additional extension to UPDATE

"Richard Broersma" <richard.broersma@gmail.com> writes:
> UPDATE Foo
> SET ( a, b, c, ..., n ) = ( SELECT newA, newB, newC, ..., newN
> FROM Bar WHERE foo.id = Bar.id )

> Any thoughts on this?

It's been on the TODO list for quite some time:
http://wiki.postgresql.org/wiki/Todo#UPDATE

regards, tom lane

--
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] indexes on functions and create or replace function

"Matthew Dennis" <mdennis@merfer.net> writes:
> Given table T(c1 int) and function F(arg int) create an index on T using
> F(c1). It appears that if you execute "create or replace function F" and
> provide a different implementation that the index still contains the results
> from the original implementation, thus if you execute something like "select
> * from T where F(c1)" after replacing the function that it now misses rows
> that should be returned. In other words, the index isn't aware the function
> is now returning different values. That's not the correct/expected behavior
> is it? I would have expected that replacing the function would have caused
> any indexes that depend on that function to be reindexed/recreated with the
> new function implementation.

If it did that, you (or someone) would complain about the enormous
overhead imposed on trivial updates of the function. Since determining
whether the function actually did change behavior is Turing-complete,
we can't realistically try to determine that in software. So we leave
it up to the user to reindex if he makes a behavioral change in an
indexed function.

(Changing the behavior of an allegedly IMMUTABLE function has a number
of other pitfalls besides that one, btw.)

regards, tom lane

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

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

> Mira si te sirven las funciones has_schema_privilege().
>
> http://www.postgresql.org/docs/8.3/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
>
> Saludos.

Exelente aporte Fernando!!

Era precisamente lo que necesitaba, gracias!!!
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

[ADMIN] Checking Postgres Version Question

Hi,

Is there a way to check which version of Postgres is installed on a
particular Linux box without query the RPM database?

For instance, is there a "psql --version" command, or something similar,
that I can use?

Thanks,
John

--
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] code coverage patch

Peter Eisentraut <peter_e@gmx.net> writes:
> Korry Douglas wrote:
>> Why not use the %output directive in the grammar file instead; that way
>> you don't need to add any special flags to the Makefile.

> I think only bison supports that directive.

We're pretty much assuming bison anyway, no? It's been years since
I heard of anyone successfully building the backend grammar with plain
yacc.

regards, tom lane

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

[pgsql-es-ayuda] Consulta sobre index

este es el excenario

-Se crea una tabla "tb1"
-Se crea una PK id "pk_id" (automaticamente se crea un indice a esta PK)
-se crea un indice "idx_id" indexando el campo id de la tabla

ahora mi preunta es:
1-como se cual es el indice que usara el query plan?
2-se puede eliminar el indice creado implicitamente por postgresql al crear la PK?

muchas gracias

--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [pgeu-general] LinuxLive UK

On Thu, Aug 28, 2008 at 3:12 PM, David Fetter <david@fetter.org> wrote:
> On Thu, Aug 28, 2008 at 10:13:07AM +0100, Dave Page wrote:
>> I've mentioned previously that we have a table in the .ORG village at
>> LinuxLive, Olympia, London on the 23 - 25 October.
>>
>> http://www.linuxexpo.org.uk/
>
> Shall I put a call for participation in this week's PostgreSQL Weekly
> News?

Yes please :-). Have folks contact me.

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

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

Re: [pgeu-general] LinuxLive UK

On Thu, Aug 28, 2008 at 10:13:07AM +0100, Dave Page wrote:
> I've mentioned previously that we have a table in the .ORG village at
> LinuxLive, Olympia, London on the 23 - 25 October.
>
> http://www.linuxexpo.org.uk/

Shall I put a call for participation in this week's PostgreSQL Weekly
News?

Cheers,
David.
> It's about time that we got organised and figured out who will be
> available to attend, and when. I can volunteer Greg and myself, but we
> need at least a few additional people to man the booth effectively
> over the three days. So, can I get a show of hands from those able to
> attend, along with how many/which days please?
>
> Thanks!
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>
> --
> Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgeu-general

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [ADMIN] [GENERAL] PITR - base backup question

----------------------------------------
> Date: Wed, 27 Aug 2008 06:58:33 -0700
> From: lennin.caro@yahoo.com
> Subject: Re: [ADMIN] [GENERAL] PITR - base backup question
> To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org; richard.broersma@gmail.com
>
>
>
>
> --- On Tue, 8/26/08, Richard Broersma wrote:
>
>> From: Richard Broersma
>> Subject: [GENERAL] PITR - base backup question
>> To: "pgsql-general@postgresql.org" , pgsql-admin@postgresql.org
>> Date: Tuesday, August 26, 2008, 10:53 PM
>> From the following link:
>> http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP
>>
>> Step 3 says to perform the back up.
>>
>> Does this mean a File System Backup of the Data
>> directory?\

It is a file system backup, we use PITR and every time we need a new checkpoint we create a TAR file of the postgresql home directory

>> OR
>> Does this mean performing a pg_dumpall and backing up
>> the dump file?
>>
> is a file system backup of he data directory
>> --
>> Regards,
>> Richard Broersma Jr.
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
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] code coverage patch

Korry Douglas wrote:
>> 1) Use bison's -o option to put the output file in the right place
>> directly,
>> if we are dealing with bison (and don't bother to support code coverage
>> analysis with other yaccs), or
>>
>> 2) Run a pattern replacement across the grammar output files as their are
>> renamed.
>
> Why not use the %output directive in the grammar file instead; that way
> you don't need to add any special flags to the Makefile.

I think only bison supports that directive.

--
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] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

In response to Matthew Wakeling <matthew@flymine.org>:
>
> Probably the best solution is to just tell the kernel somehow to never
> kill the postmaster.

This thread interested me enough to research this a bit.

In linux, it's possible to tell the OOM killer never to consider
certain processes for the axe, using /proc magic. See this page:
http://linux-mm.org/OOM_Killer

Perhaps this should be in the PostgreSQL docs somewhere?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

--
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] code coverage patch

Gregory Stark wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>
>> The reason for that problem is that the shared object needs to be linked
>> with -fprofile-arcs -ftest-coverage. (One of these causes -lgcov to be
>> linked, which includes the missing symbol.) This is not done because the
>> shared object link rules don't use CFLAGS.
>
> Shared object link rules should use another variable (LDFLAGS?) and those
> options should be added that variable as well.

When linking executables, we already use both CFLAGS and LDFLAGS. This
is the standard way in the GNU-enabled world. And it does exactly the
right thing in this gcov case. If we invented another variable, we
would disrupt that system and would further differentiate between
different types of linking, while we should ultimately aim to make it
less different.

--
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] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

On Wed, 27 Aug 2008, david@lang.hm wrote:
> if memory overcommit is disabled, the kernel checks to see if you have an
> extra 1G of ram available, if you do it allows the process to continue, if
> you don't it tries to free memory (by throwing away cache, swapping to disk,
> etc), and if it can't free the memory will return a memroy allocation error
> (which I believe will cause firefox to exit).

Remember that the memory overcommit check is checking against the amount
of RAM + swap you have - not just the amount of RAM. When a fork occurs,
hardly any extra actual RAM is used (due to copy on write), but the
potential is there for the process to use it. If overcommit is switched
off, then you just need to make sure there is *plenty* of swap to convince
the kernel that it can actually fulfil all of the memory requests if all
the processes behave badly and all shared pages become unshared. Then the
consequences of processes actually using that memory are that the machine
will swap, rather than the OOM killer having to act.

Of course, it's generally bad to run a machine with more going on than
will fit in RAM.

Neither swapping nor OOM killing are particularly good - it's just a
consequence of the amount of memory needed being unpredictable.

Probably the best solution is to just tell the kernel somehow to never
kill the postmaster.

Matthew

--
<Taking apron off> And now you can say honestly that you have been to a
lecture where you watched paint dry.
- Computer Graphics Lecturer

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

[COMMITTERS] stackbuilder - wizard: Cleanup source tarball generation

Log Message:
-----------
Cleanup source tarball generation

Modified Files:
--------------
wizard:
CMakeLists.txt (r1.15 -> r1.16)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/CMakeLists.txt.diff?r1=1.15&r2=1.16)

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

Re: [GENERAL] Vaccuum best practice: cronjob or autovaccuum?

On Thu, Aug 28, 2008 at 07:53:17PM +0800, Phoenix Kiula wrote:
> On our database of about 5GB we vaccuum all of our 12 tables (only one
> is huge, all others have about 100,000 rows or so) every hour or so.
>
> But we also have autovaccuum enabled. Is this okay? Do the two vaccuum
> processes contradict each other, or add unnecessary load to the
> system?

They do the latter. Especially vacuuming every hour, you're foiling
the autovacuum and just introducing completely unnecessary load.

> The reason we introduced the cronjob we felt was that the autovaccuum
> was not really doing its job.

What gave you this feeling?

What's the "churn rate" on these tables (i.e. how fast do dead rows
get created?)

My suspicion is that, using the 8.2 autovacuum, your "huge" table
needs to be vacuumed manually, and the others just left to
autovacuum. But that's just a guess at the moment.

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: [GENERAL] Slow PG after upgrade to 8.2.9!!

On Thu, Aug 28, 2008 at 02:44:08PM +0300, Devrim GÜNDÜZ wrote:
> After restart, OS and PostgreSQL caches are cleaned up -- it might also
> slow down PostgreSQL a bit.

I'll bet this is the right answer -- before, you were mostly getting
things out of cache (memory), and right now everything has to come off
the disk. Does iostat seem to confirm that? Are you swapping, by any
chance?

--
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

[COMMITTERS] stackbuilder - wizard: Ensure the translations are properly installed in

Log Message:
-----------
Ensure the translations are properly installed in the appbundle.

Modified Files:
--------------
wizard:
CMakeLists.txt (r1.14 -> r1.15)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/CMakeLists.txt.diff?r1=1.14&r2=1.15)

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

Re: [HACKERS] code coverage patch

> The problem with those files is that the source file contains lines
> like this:
>
> #line 1042 "y.tab.c"
>
> but that source file does not exist, as it is renamed to gram.c.
>
> We could fix that in one of two ways:
>
> 1) Use bison's -o option to put the output file in the right place
> directly,
> if we are dealing with bison (and don't bother to support code
> coverage
> analysis with other yaccs), or
>
> 2) Run a pattern replacement across the grammar output files as
> their are
> renamed.

Why not use the %output directive in the grammar file instead; that
way you don't need to add any special flags to the Makefile.

-- Korry

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

[pgsql-ru-general] freebsd и utf-8

Добрый день,

Установлена FreeBSD 4.9-RELEASE и PostgreSQL 8.3.1.
Initdb выполнена с опциями --encoding=UTF8 --locale=ru_RU.UTF-8.

Не работают функции UPPER/LOWER и полнотекстового поиска. Подскажите в чём
может быть дело?

--
С уважением, Глеб
gleb.chipiga@gmail.com

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

[COMMITTERS] stackbuilder - wizard: Fix CPack options

Log Message:
-----------
Fix CPack options

Modified Files:
--------------
wizard:
CMakeLists.txt (r1.13 -> r1.14)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/CMakeLists.txt.diff?r1=1.13&r2=1.14)

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

Re: [PERFORM] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

On Wed, Aug 27, 2008 at 03:22:09PM -0700, david@lang.hm wrote:
>
> I disagree with you. I think goof Postgres operation is so highly dependant
> on caching as much data as possible that disabling overcommit (and throwing
> away a lot of memory that could be used for cache) is a solution that's as
> bad or worse than the problem it's trying to solve.

Ok, but the danger is that the OOM killer kills your postmaster. To
me, this is a cure way worse than the disease it's trying to treat.
YMMD &c. &c.

--
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

Friday, August 29, 2008

Thursday, August 28, 2008

Blog Archive