PostgreSQL: May 23, 2008

Friday, May 23, 2008

Re: [ADMIN] Continuing issues... Can't vacuum!

>>> Carol Walter <walterc@indiana.edu> wrote:
> On May 23, 2008, at 2:39 PM, Joshua D. Drake wrote:
>>
>> pg_ctl -D /path/to/data/dir -m fast stop
>>
> Does this do an orderly, safe stop? Also do I then bring it back up

> with the usual command?

"Three different shutdown methods can be selected with the -m option:
"Smart" mode waits for all the clients to disconnect. This is the
default. "Fast" mode does not wait for clients to disconnect. All
active transactions are rolled back and clients are forcibly
disconnected, then the server is shut down. "Immediate" mode will
abort all server processes without a clean shutdown. This will lead to
a recovery run on restart."

From the docs:

http://www.postgresql.org/docs/8.2/interactive/app-pg-ctl.html

-Kevin

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

[pgus-board] Modifying mission statement

I would like to modify the mission to:

Current:

* Continue the PostgreSQL Community Conference series including West,
East and PGDays at OSCON and LWE.

New:

* Continue the support of the PostgreSQL Community Conference Series
including West, East and PGDays at OSCON and LWE.

Sincerely,

Joshua D. Drake

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

Re: [ADMIN] Continuing issues... Can't vacuum!

Does this do an orderly, safe stop? Also do I then bring it back up
with the usual command?

Thanks,
Carol

On May 23, 2008, at 2:39 PM, Joshua D. Drake wrote:

>
>
> On Fri, 2008-05-23 at 14:35 -0400, Carol Walter wrote:
>> Oh, yes. I have restarted it several times - or at least I thought
>> that I did. Actually, it's not stopping. I grepped for "post" after
>> I did the stop and it is still running. I really don't like the idea
>> of issuing the kill command on the database processes. Is there
>> another option?
>>
>
> pg_ctl -D /path/to/data/dir -m fast stop
>
> Sincerely,
>
> Joshua D. Drake
>
>

--
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] Continuing issues... Can't vacuum!

On Fri, 2008-05-23 at 14:35 -0400, Carol Walter wrote:
> Oh, yes. I have restarted it several times - or at least I thought
> that I did. Actually, it's not stopping. I grepped for "post" after
> I did the stop and it is still running. I really don't like the idea
> of issuing the kill command on the database processes. Is there
> another option?
>

pg_ctl -D /path/to/data/dir -m fast stop

Sincerely,

Joshua D. Drake

--
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] Continuing issues... Can't vacuum!

Oh, yes. I have restarted it several times - or at least I thought
that I did. Actually, it's not stopping. I grepped for "post" after
I did the stop and it is still running. I really don't like the idea
of issuing the kill command on the database processes. Is there
another option?

Carol
On May 23, 2008, at 2:00 PM, Joshua D. Drake wrote:

>
>
> On Fri, 2008-05-23 at 13:54 -0400, Carol Walter wrote:
>> Hello,
>
>> vacuumdb: vacuuming database "km"
>> NOTICE: number of page slots needed (2275712) exceeds max_fsm_pages
>> (200000)
>> HINT: Consider increasing the configuration parameter
>> "max_fsm_pages" to a value over 2275712.
>>
>> The problem is I've found the max_fsm_pages parameter in the
>> postgresql.conf file, but changing it doesn't seem to be having any
>> effect.
>
> You have to restart the database.
>
>> So I'm going to ask some questions that are probably pretty
>> silly, but I hope you'll help me. First of all, perhaps the
>> postgresql.conf file that I am editing may not be the one that is
>> being read when the database server starts. There are several
>> postgresql.conf files on this system. How do I tell which one is the
>> one being read?
>
> Woah... :)
>
>> From psql: show config_file;
>
>
>> The max_fsm_pages parameter says what the minimum setting is in the
>> file. Is there also a maximum?
>>
>
> It is limited by your shared memory but consider that is not your
> problem. Your problem is you don't vacuum enough. My suggestion is
> to do
> the following:
>
> Up the max_fsm_pages a considerable amount
> Restart
> Vacuum the entire database
> backup
> reinitialize with backup
> make sure you are vacuuming properly.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
> --
> 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: [PATCHES] WITH RECURSIVE patch V0.1

On Sat, May 24, 2008 at 03:21:01AM +0900, Yoshiyuki Asaba wrote:
> Hi,
>
> From: David Fetter <david@fetter.org>
> Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1
> Date: Sun, 18 May 2008 11:47:37 -0700
>
> > I tried a bunch of different queries, and so far, only these two
> > haven't worked. Any ideas what I'm doing wrong here?
> >
> > WITH RECURSIVE t(n) AS (
> > SELECT 1
> > UNION ALL
> > SELECT n+1
> > FROM t
> > WHERE n < 100
> > )
> > SELECT * FROM t;
> > ERROR: cannot extract attribute from empty tuple slot
>
> Thank you for the report. I've fixed.
>
> postgres=# WITH RECURSIVE t(n) AS (
> SELECT 1
> UNION ALL
> SELECT n+1
> FROM t
> WHERE n < 100
> )
> SELECT count(*) FROM t;
> count
> -------
> 100
> (1 row)
>
> Regards,
> --
> Yoshiyuki Asaba
> y-asaba@sraoss.co.jp

Great!

Where is the new patch?

Cheers,
David.
--
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 pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [PATCHES] WITH RECURSIVE patch V0.1

Hi,

From: David Fetter <david@fetter.org>
Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1
Date: Sun, 18 May 2008 11:47:37 -0700

> I tried a bunch of different queries, and so far, only these two
> haven't worked. Any ideas what I'm doing wrong here?
>
> WITH RECURSIVE t(n) AS (
> SELECT 1
> UNION ALL
> SELECT n+1
> FROM t
> WHERE n < 100
> )
> SELECT * FROM t;
> ERROR: cannot extract attribute from empty tuple slot

Thank you for the report. I've fixed.

postgres=# WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1
FROM t
WHERE n < 100
)
SELECT count(*) FROM t;
count
-------
100
(1 row)

Regards,
--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp

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

Re: [pgsql-es-ayuda] Error con el COPY

Debes revisar los permisos del archivo

2008/5/23, Carlos Pineda <carlospinedave@gmail.com>:
> Hola a todos..!
>
> Intento copiar una data delimitada por ","a partir de un archivo .dat en una
> de mis tablas en postgresql 8.3 para windows, pero me arroja un error de
> permisología, esto es bastante extraño por que el archivo está en mi
> escritorio y tengo permiso de lectura y escritura...
> La línea de comando es la siguiente:
> COPY sales_order FROM e'C:\\DOCUMENTS AND
> SETTINGS\\CPINEDA\\ESCRITORIO\\UNLOAD\\192.dat' DELIMITERS ','
> y el error que muestra es:
> ERROR: could not open file "C:\DOCUMENTS AND
> SETTINGS\CPINEDA\ESCRITORIO\UNLOAD\192.dat" for reading: Permission denied
> SQL state: 42501
>
> Gracias de antemano!!!
>

--
Fabio Hernando Arias Vera
Cel. 314 411 7776
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [pgsql-es-ayuda] Error con el COPY

Agrega permisos full a esta carpeta para el usuario 'postgres'.

Atentamente,

RAUL DUQUE

Bogotá, Colombia

----- Original Message -----

From: Carlos Pineda

To: pgsql-es-ayuda@postgresql.org

Sent: Friday, May 23, 2008 12:21 PM

Subject: [pgsql-es-ayuda] Error con el COPY

Hola a todos..!

Intento copiar una data delimitada por ","a partir de un archivo .dat en una de mis tablas en postgresql 8.3 para windows, pero me arroja un error de permisología, esto es bastante extraño por que el archivo está en mi escritorio y tengo permiso de lectura y escritura...

La línea de comando es la siguiente:

COPY sales_order FROM e'C:\\DOCUMENTS AND SETTINGS\\CPINEDA\\ESCRITORIO\\UNLOAD\\192.dat' DELIMITERS ','

y el error que muestra es:

ERROR: could not open file "C:\DOCUMENTS AND SETTINGS\CPINEDA\ESCRITORIO\UNLOAD\192.dat" for reading: Permission denied
SQL state: 42501

Gracias de antemano!!!

Re: [ADMIN] Continuing issues... Can't vacuum!

Carol Walter wrote:
>
> vacuumdb: vacuuming database "km"
> NOTICE: number of page slots needed (2275712) exceeds max_fsm_pages
> (200000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages"
> to a value over 2275712.
>
> The problem is I've found the max_fsm_pages parameter in the
> postgresql.conf file, but changing it doesn't seem to be having any
> effect. So I'm going to ask some questions that are probably pretty
> silly, but I hope you'll help me. First of all, perhaps the
> postgresql.conf file that I am editing may not be the one that is
> being read when the database server starts. There are several
> postgresql.conf files on this system. How do I tell which one is the
> one being read?
>
Carol,

Do the following in psql:

show config_file;

You should get some output like so:

config_file
-------------------------------------
/var/lib/pgsql/data/postgresql.conf
(1 row)

Go forth and edit that file. :-)
> The max_fsm_pages parameter says what the minimum setting is in the
> file. Is there also a maximum?
>
I'm not sure I understand the question. max_fsm_pages is the maximum
number of pages to be used in the free space map. See Jim Nasby's
article here: http://decibel.org/~decibel/pervasive/fsm.html
> I can't back up my database with the error right now, because of the
> error. I have a backup but I'm afraid to restore it. I feel like I
> have many indices that are bloated and I want to get the databases
> vacuumed before I start trying to create and load a new database. I'm
> concerned that there won't be enough index space for the system to
> sort and copy the indices.
What's the error? All I see in this email is a warning about
max_fsm_pages and that should not stop you from doing a pg_dump.

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC

http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

--
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] Continuing issues... Can't vacuum!

On Fri, 2008-05-23 at 13:54 -0400, Carol Walter wrote:
> Hello,

> vacuumdb: vacuuming database "km"
> NOTICE: number of page slots needed (2275712) exceeds max_fsm_pages
> (200000)
> HINT: Consider increasing the configuration parameter
> "max_fsm_pages" to a value over 2275712.
>
> The problem is I've found the max_fsm_pages parameter in the
> postgresql.conf file, but changing it doesn't seem to be having any
> effect.

You have to restart the database.

> So I'm going to ask some questions that are probably pretty
> silly, but I hope you'll help me. First of all, perhaps the
> postgresql.conf file that I am editing may not be the one that is
> being read when the database server starts. There are several
> postgresql.conf files on this system. How do I tell which one is the
> one being read?

Woah... :)

>From psql: show config_file;

> The max_fsm_pages parameter says what the minimum setting is in the
> file. Is there also a maximum?
>

It is limited by your shared memory but consider that is not your
problem. Your problem is you don't vacuum enough. My suggestion is to do
the following:

Up the max_fsm_pages a considerable amount
Restart
Vacuum the entire database
backup
reinitialize with backup
make sure you are vacuuming properly.

Sincerely,

Joshua D. Drake

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

[ADMIN] Continuing issues... Can't vacuum!

Hello,

I posted yesterday about a corrupt index. I'm continuing to
experience problems. I know that part of my problem is that the
databases have not been vacuumed as they should have been. I've
tried to vacuum them but it's not working. The message I'm getting
is as follows:

vacuumdb: vacuuming database "km"
NOTICE: number of page slots needed (2275712) exceeds max_fsm_pages
(200000)
HINT: Consider increasing the configuration parameter
"max_fsm_pages" to a value over 2275712.

The problem is I've found the max_fsm_pages parameter in the
postgresql.conf file, but changing it doesn't seem to be having any
effect. So I'm going to ask some questions that are probably pretty
silly, but I hope you'll help me. First of all, perhaps the
postgresql.conf file that I am editing may not be the one that is
being read when the database server starts. There are several
postgresql.conf files on this system. How do I tell which one is the
one being read?

The max_fsm_pages parameter says what the minimum setting is in the
file. Is there also a maximum?

I can't back up my database with the error right now, because of the
error. I have a backup but I'm afraid to restore it. I feel like I
have many indices that are bloated and I want to get the databases
vacuumed before I start trying to create and load a new database.
I'm concerned that there won't be enough index space for the system
to sort and copy the indices.

Please, help.

Carol

--
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] Error con el COPY

pero lo tienes como el usuario de postgres?
ya intentaste agregando al usuario "Todos" con todos los permisos?

2008/5/23 Carlos Pineda <carlospinedave@gmail.com>:
> Hola a todos..!
>
> Intento copiar una data delimitada por ","a partir de un archivo .dat en una
> de mis tablas en postgresql 8.3 para windows, pero me arroja un error de
> permisología, esto es bastante extraño por que el archivo está en mi
> escritorio y tengo permiso de lectura y escritura...
> La línea de comando es la siguiente:
> COPY sales_order FROM e'C:\\DOCUMENTS AND
> SETTINGS\\CPINEDA\\ESCRITORIO\\UNLOAD\\192.dat' DELIMITERS ','
> y el error que muestra es:
> ERROR: could not open file "C:\DOCUMENTS AND
> SETTINGS\CPINEDA\ESCRITORIO\UNLOAD\192.dat" for reading: Permission denied
> SQL state: 42501
>
> Gracias de antemano!!!

--
________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida
--
TIP 5: ¿Has leído nuestro extenso FAQ?

http://www.postgresql.org/docs/faqs.FAQ.html

Re: [GENERAL] Installed pgadmin3-1.4.3 with 8.3.1 database..errors

Hi,

On Thu, 2008-05-22 at 09:38 -0400, Barbara Stephenson wrote:

> We have recently upgraded from 7.4.19 to 8.3.1. I am running Red Hat
> Enterprise Linux WS release 4 (Nahant Update 6) on my laptop and I
> finally was able to install pgadmin3-1.4.3.

You can find up2date version here:

http://yum.pgsqlrpms.org/8.3/redhat/rhel-4-x86_64/repoview/pgadmin3.html

or here:

http://yum.pgsqlrpms.org/8.3/redhat/rhel-4-i386/repoview/pgadmin3.html

depending on your arch.

Regards,
--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

[pgsql-es-ayuda] Error con el COPY

Hola a todos..!

Intento copiar una data delimitada por ","a partir de un archivo .dat en una de mis tablas en postgresql 8.3 para windows, pero me arroja un error de permisología, esto es bastante extraño por que el archivo está en mi escritorio y tengo permiso de lectura y escritura...

La línea de comando es la siguiente:

COPY sales_order FROM e'C:\\DOCUMENTS AND SETTINGS\\CPINEDA\\ESCRITORIO\\UNLOAD\\192.dat' DELIMITERS ','

y el error que muestra es:

ERROR: could not open file "C:\DOCUMENTS AND SETTINGS\CPINEDA\ESCRITORIO\UNLOAD\192.dat" for reading: Permission denied
SQL state: 42501

Gracias de antemano!!!

[BUGS] BUG #4191: Include hint for Windows-like locals in documentation

The following bug has been logged online:

Bug reference: 4191
Logged by: Martin Saschek
Email address: m.saschek@automationwr.de
PostgreSQL version: 8.2.7; 8.3.1
Operating system: Windows XP
Description: Include hint for Windows-like locals in documentation
Details:

include a hint on the Windows-like encoding of locale settings in "Chapter
21. Localization".

Only by chance and after several hours (!) I found out that on Windows I
should use "German_Germany" rather than "de_DE".

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

Re: [SQL] Extremely Low performance with ODBC

Thanks.. All logs are already off and I'm just sending one query directly
from the application (from Visual Foxpro using SQL Pass through and in
VB.net using ado.net). I've found that the "problem" is that I'm using
implicit joins, and when I change it to explicit Inner Joins the query only
takes 3 seconds (as with PgAdmin). I still can't understand if the problem
resides on the ODBC driver, the Query Planner or it's just a mix of both.

-----Mensaje original-----
De: Richard Broersma [mailto:richard.broersma@gmail.com]
Enviado el: Viernes, 23 de Mayo de 2008 01:04 p.m.
Para: Sebastian Rychter
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Extremely Low performance with ODBC

On Thu, May 22, 2008 at 11:29 AM, Sebastian Rychter
<srychter@anvet.com.ar> wrote:
> Hi, I'm executing a query through psql ODBC which is taking around 2
minutes
> to complete. When I run it from PgAdmin it takes less than 3 seconds.
>
> Any idea ?

1) turn off all ODBC query logging from both the ODBC driver and the
ODBC administrator. This feature is a performance killer. Only use
this feature if you are trying to debug a problem.
2) Some client programs try to perform the joins on the client side
rather than the server side. I would check if the client is actually
passing the actual SQL statement back to the server. If it is trying
to perform the join on the client, it could be a big performance
killer.

--
Regards,
Richard Broersma Jr.

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

__________ NOD32 3124 (20080522) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com

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

[PATCHES] Free Space Map rewrite

Hi,

Here's a new snapshot of the FSM rewrite I've been working on. The "map
fork" stuff hasn't been changed since last patch (I have some work to do
there based on Tom's recent comments), but the FSM implementation itself
is now starting to get in shape. So the thing to look at in this patch
is freespace.c. It's unreadable in diff format because the whole file
has basically been rewritten, you'll have to apply the patch. I've also
attached a README, which is also part of the patch.

Still a lot of work to be done, like ironing out race conditions between
updates and searches, the approach I'm planning to take there is
explained in the README, and WAL-logging, but I'm fairly happy with
what's there now.

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

Re: [ADMIN] How do I force users to change their password?

Blakely, Jerel (Mission Systems) wrote:

How do I force users to change their password?
The only setting I see is an account expire, but I do not want to lock the people out of the accounts.
I just want to force each user to set a new password on minimum intervals.

Does Postgres have a way to require users to change their passwords every 90 days for example, with out locking the account and forcing an admin to go in and update it?

I think the best you could do would be to tie authentication into some external system that requires a password change after a specified interval. This can be accomplished with LDAP, Kerberos, or PAM. However, I don't think that the native mechanism will allow you to do what you are trying to accomplish.

--  Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC  27560 919-463-0999/877-258-8987 http://www.otg-nc.com

Re: [GENERAL] Fwd: sql_type tinterval not supported by DBD::Pg

Well, what can I say; thanks Radek.

I am looking forward to see this fix included in stable DBD::Pg realease

2008/5/23 Radoslaw Zielinski <radek@pld-linux.org>:

Filip Rembiałkowski <plk.zuber@gmail.com> [23-05-2008 10:58]:
[...]

> the same with interval type:
[...]
> calling bind_param( 6, '14 days', { 'pg_type' => 1186 } )
> Cannot bind 6, sql_type interval not supported by DBD::Pg

http://rt.cpan.org/Ticket/Display.html?id=36138

--
Radosław Zieliński <radek@pld-linux.org>

--
Filip Rembiałkowski

Re: [PERFORM] index performance on large tables with update and insert

Jessica Richard wrote:
> I have a large table with about 2 million rows and it will keep
> growing...
>
> I need to do update/inserts, and select as well.
>
> An index will speed up the select, but it will slow down the updates.
>
> Are all Postgres indexes ordered? i.e., with every update, the index
> pages will have to be physically reordered?
>
> Does Postgres have any kind of non-ordered indexes (like Syabse's
> non-clustered index)?

All PostgreSQL indexes are like the non-clustered ones in Sybase or SQL
Server.

> What is the common way to take care of the performance issue when you
> have to do both update and select on the same large table?

Create the indexes you actually need to make the selects and updates
fast, just make sure you don't create any unnecessary ones. Usually,
your UPDATEs will also require indexes - only the INSERTs actually are
losing.

//Magnus

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

Re: [pgus-board] Membership Benefits

On Fri, 2008-05-23 at 09:52 -0400, Michael Alan Brewer wrote:
> Ahh; gotcha. We'll probably need to modify the mission statement to
> make the relationship with the PostgreSQL Community Conference series
> more clear.
>

Yeah I was just thinking about that.

Joshua D. Drake

> ---Michael Brewer
> mbrewer@gmail.com
>

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

Re: [GENERAL] Fwd: sql_type tinterval not supported by DBD::Pg

Filip Rembiałkowski <plk.zuber@gmail.com> [23-05-2008 10:58]:
[...]
> the same with interval type:
[...]
> calling bind_param( 6, '14 days', { 'pg_type' => 1186 } )
> Cannot bind 6, sql_type interval not supported by DBD::Pg

http://rt.cpan.org/Ticket/Display.html?id=36138

--
Radosław Zieliński <radek@pld-linux.org>

[ADMIN] How do I force users to change their password?

The only setting I see is an account expire, but I do not want to lock the people out of the accounts.
I just want to force each user to set a new password on minimum intervals.

Does Postgres have a way to require users to change their passwords every 90 days for example, with out locking the account and forcing an admin to go in and update it?

[HACKERS] Random order of archiving files

Hi All,

Can this random order in archival of files be explained?

grep archived /var/log/localmessages

2008-05-23 08:43:43 PDTLOG: archived transaction log file "00000001000009BD0000009D"
2008-05-23 08:44:41 PDTLOG: archived transaction log file "00000001000009BD000000A0" <----- 9D and 9F archived much later
2008-05-23 08:45:01 PDTLOG: archived transaction log file "00000001000009BD000000A1"
2008-05-23 08:46:00 PDTLOG: archived transaction log file "00000001000009BD000000A4"
2008-05-23 08:46:26 PDTLOG: archived transaction log file "00000001000009BD0000006C"
2008-05-23 08:46:29 PDTLOG: archived transaction log file "00000001000009BD00000069"
2008-05-23 08:46:32 PDTLOG: archived transaction log file "00000001000009BD0000006D"
2008-05-23 08:46:33 PDTLOG: archived transaction log file "00000001000009BD00000071"
2008-05-23 08:46:34 PDTLOG: archived transaction log file "00000001000009BD00000073"
2008-05-23 08:46:35 PDTLOG: archived transaction log file "00000001000009BD00000074"
2008-05-23 08:46:38 PDTLOG: archived transaction log file "00000001000009BD00000075"
2008-05-23 08:46:41 PDTLOG: archived transaction log file "00000001000009BD00000076"
2008-05-23 08:46:42 PDTLOG: archived transaction log file "00000001000009BD00000077"
2008-05-23 08:46:43 PDTLOG: archived transaction log file "00000001000009BD0000007A"
2008-05-23 08:46:49 PDTLOG: archived transaction log file "00000001000009BD0000007B"
2008-05-23 08:46:52 PDTLOG: archived transaction log file "00000001000009BD0000007D"
2008-05-23 08:46:53 PDTLOG: archived transaction log file "00000001000009BD0000007F"
2008-05-23 08:46:54 PDTLOG: archived transaction log file "00000001000009BD000000A7" <--- chain broken here again...
2008-05-23 08:47:59 PDTLOG: archived transaction log file "00000001000009BD000000AA"
2008-05-23 08:48:21 PDTLOG: archived transaction log file "00000001000009BD000000AB"
2008-05-23 08:49:33 PDTLOG: archived transaction log file "00000001000009BD000000AD"
2008-05-23 08:50:30 PDTLOG: archived transaction log file "00000001000009BD000000AE"
2008-05-23 08:52:30 PDTLOG: archived transaction log file "00000001000009BD000000AF"
2008-05-23 08:54:30 PDTLOG: archived transaction log file "00000001000009BD000000B0"
2008-05-23 08:55:42 PDTLOG: archived transaction log file "00000001000009BD000000B1"
2008-05-23 08:57:39 PDTLOG: archived transaction log file "00000001000009BD000000B2"
2008-05-23 08:59:27 PDTLOG: archived transaction log file "00000001000009BD000000B3"
2008-05-23 09:01:26 PDTLOG: archived transaction log file "00000001000009BD00000085"
2008-05-23 09:01:27 PDTLOG: archived transaction log file "00000001000009BD00000080"
2008-05-23 09:01:28 PDTLOG: archived transaction log file "00000001000009BD00000081"
2008-05-23 09:01:29 PDTLOG: archived transaction log file "00000001000009BD00000086"
2008-05-23 09:01:30 PDTLOG: archived transaction log file "00000001000009BD00000088"
2008-05-23 09:01:34 PDTLOG: archived transaction log file "00000001000009BD00000089"
2008-05-23 09:01:37 PDTLOG: archived transaction log file "00000001000009BD0000008C"
2008-05-23 09:01:38 PDTLOG: archived transaction log file "00000001000009BD0000008F"
2008-05-23 09:01:39 PDTLOG: archived transaction log file "00000001000009BD00000090"
2008-05-23 09:01:42 PDTLOG: archived transaction log file "00000001000009BD00000091"
2008-05-23 09:01:46 PDTLOG: archived transaction log file "00000001000009BD00000093"
2008-05-23 09:01:47 PDTLOG: archived transaction log file "00000001000009BD00000094"
2008-05-23 09:01:48 PDTLOG: archived transaction log file "00000001000009BD00000095"
2008-05-23 09:01:52 PDTLOG: archived transaction log file "00000001000009BD00000097"
2008-05-23 09:01:54 PDTLOG: archived transaction log file "00000001000009BD00000099"
2008-05-23 09:01:55 PDTLOG: archived transaction log file "00000001000009BD0000009A"
2008-05-23 09:01:56 PDTLOG: archived transaction log file "00000001000009BD0000009B"
2008-05-23 09:01:57 PDTLOG: archived transaction log file "00000001000009BD0000009C"
2008-05-23 09:01:58 PDTLOG: archived transaction log file "00000001000009BD0000009E"
2008-05-23 09:01:59 PDTLOG: archived transaction log file "00000001000009BD0000009F"
2008-05-23 09:02:01 PDTLOG: archived transaction log file "00000001000009BD000000A2"
2008-05-23 09:02:04 PDTLOG: archived transaction log file "00000001000009BD000000A3"
2008-05-23 09:02:06 PDTLOG: archived transaction log file "00000001000009BD000000A5"
2008-05-23 09:02:06 PDTLOG: archived transaction log file "00000001000009BD000000B4" <--- continuing from where it left earlier!!!
2008-05-23 09:02:58 PDTLOG: archived transaction log file "00000001000009BD000000B5"
2008-05-23 09:04:32 PDTLOG: archived transaction log file "00000001000009BD000000B6"
2008-05-23 09:06:28 PDTLOG: archived transaction log file "00000001000009BD000000B7"
2008-05-23 09:08:21 PDTLOG: archived transaction log file "00000001000009BD000000B8"

I always assumed that the XLogs are generated in sequence. Apparently, the hot-standby server is expecting and consuming the XLogs in-order, and not in the order they are being generated here!

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [ADMIN] index performance

I have a large table with about 2 million rows and it will keep growing...

I need to do update/inserts, and select as well.

An index will speed up the select, but it will slow down the updates.

Are all Postgres indexes ordered? i.e., with every update, the index pages will have to be physically reordered?

Does Postgres have any kind of non-ordered indexes (like Syabse's non-clustered index)?

What is the common way to take care of the performance issue when you have to do both update and select on the same large table?

You might consider table partitioning and enabling constraint exclusion.

--  Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC  27560 919-463-0999/877-258-8987 http://www.otg-nc.com Ask me about OTG's Expert On-Site & Public enrollment PostgreSQL, PostGIS, and other Open Source courses.

Re: [SQL] Extremely Low performance with ODBC

On Thu, May 22, 2008 at 11:29 AM, Sebastian Rychter
<srychter@anvet.com.ar> wrote:
> Hi, I'm executing a query through psql ODBC which is taking around 2 minutes
> to complete. When I run it from PgAdmin it takes less than 3 seconds.
>
> Any idea ?

--
Regards,
Richard Broersma Jr.

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

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

Re: [lapug] [pgsql-advocacy] Do we want to do a PGDay at LISA '08?

On Thu, May 22, 2008 at 1:46 PM, Dan Langille <dan@langille.org> wrote:

>>> Oh, for people who don't follow links: Nov 9-14 in San Diego.
>>
>> However, I have proposed a BOF, an HTGR session, and a The Guru Is In
>> session.
>
>
> FWIW, I may be there.

I can probably attend this also. I'll also bring it up at the next
PUG meeting to see if anyone else wouldn't mind taking a trip down to
San Diego.

--
Regards,
Richard Broersma Jr.

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

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

[pgsql-www] TorontoPUG added to common web pages

Hello!

I'm looking for TorontoPUG to be added to:

/community/lists
/community/lists/subscribe

Please add torontopug to the menu for User Groups at:
http://archives.postgresql.org/

The mailing list already exists, it just needs to be linked in.

-selena

--
Selena Deckelmann
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily

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

Re: [GENERAL] tsearch2 on-demand dictionary loading & using functions in tsearch2

Hello,

We definitely came across this issue recently. When new postgres
backend is started it uses ~3MB of the memory accordingly to pmap.
When one runs within this backend several typical queries that our
application generates its consumed memory increases to 5-8MB which is
not critical for us. But when one hits some FTS function with the
token that requires ispell dictionaries to be loaded we instantly get
26MB of consumed memory in this backend.

Having 50 backends behind pgbouncer all of them containing ~20MB
redundant FTS data is a serious penalty on some hardware since during
the peak load kernel invalidates huge parts of its disk cache with
actually 'hot' data to allocate more RAM for postgres backends and
we've got huge iowait as a result.

We definitely observe this scenario on one of the servers now and
ability to save so much RAM by putting some FTS data in shared memory
would help here. We alter dictionaries once per couple of months and
would endure even postgres restart after such a change.

--
Regards,
Ivan

>> This is probably a stupid question, but: with PostgreSQL's use of
>> shared memory, is it possible to load dictionaries into a small
>> reserved shm area when the first backend starts, then use the
>> preloaded copy in subsequent backends?
>>
>> That way the postmaster doesn't have to do any risky work.
>>
>> Anything that reduces backend startup costs and per-backend unshared
>> memory would have to be a good thing.
>>
>> I've found it useful in the past to share resources with an mmap()ped
>> file, too, especially if I want write protection from some or all
>> processes. If the postmaster forked a process to generate the
>> mmap()able compiled dictionary files on startup then it'd be pretty
>> safe from any misbehaviour of the dictionary compiling process.
>>
>> Then again, I can't say I've personally noticed the cost of loading
>> tsearch2 dictionaries.
>
> So the dictionary will be parsed on the first usage by the given backend,
> and from that moment on, all running backends and all backends that will be
> spawned afterwards will have access to the parsed dictionary structures
> thanks to the shm?
>
> That seems to solve all issues - speed, memory and updating. Would this be a
> way to go? Obviously, it might boil down to "write a patch", but if someone
> actually wrote a patch, would this approach be acceptable?

--
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] Re: [pgsql-es-ayuda] Como puedo cambiar la contraseña

usa:

ALTER USER postgres WITH PASWORD 'nuevopassword';

Atentamente,

RAUL DUQUE
Bogotá, Colombia

----- Original Message -----
From: "MIGUEL CANCHAS" <mcanchas@tsr.com.pe>
To: <pgsql-es-ayuda@postgresql.org>
Sent: Friday, May 23, 2008 10:09 AM
Subject: [pgsql-es-ayuda] Como puedo cambiar la contraseña

> Uso Postgresql 8.2
> Win XP SP2
>
> Como puedo cambiar mi clave, sin querer lo puse en mayuscula, necesito
> tenerlo en minuscula.
>
>
> Miguel
> --
> TIP 7: no olvides aumentar la configuración del "free space map"

--
TIP 4: No hagas 'kill -9' a postmaster

[pgsql-es-ayuda] Re: [pgsql-es-ayuda] Como puedo cambiar la contraseña

MIGUEL CANCHAS wrote:
> Uso Postgresql 8.2
> Win XP SP2
>
> Como puedo cambiar mi clave, sin querer lo puse en mayuscula, necesito
> tenerlo en minuscula.
>
>
> Miguel
> --
> TIP 7: no olvides aumentar la configuración del "free space map"
>
>
>
dale una leida por aca :
http://www.postgresql.org/docs/8.2/interactive/sql-alterrole.html
Leonel

--
TIP 8: explain analyze es tu amigo

Re: [pgsql-es-ayuda] Donde se ha ido el pg_config

Raul Andres Duque wrote:
> Cordial Saludo listeros.
>
> Tengo un Debian Etch con postgresql 8.3 instalado desde apt-get.
>
> Intento compilar/instalar el DBT2, ya he cumplido todo los prerequisitos sin embargo me dice que no encuentra en el path el pg_config ... he buscado esta utilidad por toda la máquina y efectivamente no existe !!!
>
> Mi primera impresión era que depronto la habían eliminado en 8.3, pero leyendo la documentación no es así:
>
> http://www.postgresql.org/docs/8.3/static/app-pgconfig.html
>
> Alguna sugerencia?
>
> Atentamente,
>
> RAUL DUQUE
> Bogotá, Colombia
>
>
>

instala el paquete libpq-dev

Leonel

--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?

http://archives.postgresql.org/pgsql-es-ayuda

[PERFORM] IBM ServRAID-MR10M / LSI1078ROC advice

We're about to try out a new BBU controller card, and would welcome
any advice from anyone with experience with this hardware.

It is an IBM ServeRAID-MR10M SAS/SATA Controller with the optional
BBU. The docs say it is "a LSI1078ROC-based PCI Express RAID
adapter." We're hooking it up to four drawers of 12 drives each; all
drives are 146 GB 3.5 inch 15 kRPM HS SAS. Our hope is to set up
three drawers in RAID 10. I don't know if this will work -- our
previous adapter refused to allow more than 14 drives in RAID 10, so
we had to use RAID 5 for the "big" partition. The other drawer will
be RAID 5.

Tips?

-Kevin

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

[pgsql-es-ayuda] Como puedo cambiar la contraseña

Uso Postgresql 8.2
Win XP SP2

Como puedo cambiar mi clave, sin querer lo puse en mayuscula, necesito
tenerlo en minuscula.

Miguel
--
TIP 7: no olvides aumentar la configuración del "free space map"

[GENERAL] apache perl cgi script cant load libpq.5.dylib on mac os tiger

I have a perl cgi script that wont run on a new mac. The script runs fine
from the command line, so the perl and postgresql seem good. However
when run as an apache cgi script the postgresql stuff fails as shown
below. I altered the script to dump %ENV and the paths seem in order.

The script is not using mod perl. It is using the apple os x 10.4.11 supplied
version of apache, postgresql-8.3.0 and perl, v5.8.8 with DBD::Pg, DBI and
JSON installed.

I am at a total loss as to why the library cant be found and would be
very grateful for any help.

Content-type: text/html Software error:

install_driver(Pg) failed:
Can't load '/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle'
for module DBD::Pg: dlopen(/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle, 1):
Library not loaded: /usr/local/pgsql/lib/libpq.5.dylib
Referenced from: /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle
Reason: image not found at /usr/local/lib/perl5/5.8.8/darwin-2level/DynaLoader.pm line 230.
at (eval 45) line 3
Compilation failed in require at (eval 45) line 3.
Perhaps a required shared library or dll isn't installed where expected at /Library/WebServer/cgi-bin/pgcgi.pl line 383

$ENV{AUTH_TYPE} = Basic
$ENV{DOCUMENT_ROOT} = /Library/WebServer/Documents
$ENV{DYLD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
$ENV{GATEWAY_INTERFACE} = CGI/1.1
$ENV{HTTPS} = on
$ENV{HTTP_ACCEPT} = text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,/;q=0.5
$ENV{HTTP_ACCEPT_CHARSET} = ISO-8859-1,utf-8;q=0.7,;q=0.7
$ENV{HTTP_ACCEPT_ENCODING} = gzip,deflate
$ENV{HTTP_ACCEPT_LANGUAGE} = en,en-us;q=0.5
$ENV{HTTP_CONNECTION} = keep-alive
$ENV{HTTP_HOST} = XXXXX.co.uk
$ENV{HTTP_KEEP_ALIVE} = 300
$ENV{HTTP_USER_AGENT} = Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14
$ENV{LD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
$ENV{PATH} = /usr/local/pgsql/bin:/usr/local/bin:/bin:/sbin:/usr/bin:/usr/sbin
$ENV{QUERY_STRING} =
$ENV{REMOTE_ADDR} = XXXXXX
$ENV{REMOTE_PORT} = 53698
$ENV{REMOTE_USER} = fergus
$ENV{REQUEST_METHOD} = GET
$ENV{REQUEST_URI} = /cgi-bin/pgcgi.pl
$ENV{SCRIPT_FILENAME} = /Library/WebServer/cgi-bin/pgcgi.pl
$ENV{SCRIPT_NAME} = /cgi-bin/pgcgi.pl
$ENV{SERVER_ADDR} = XXXX
$ENV{SERVER_ADMIN} = fergus@XXXXX
$ENV{SERVER_NAME} = XXXXX.co.uk
$ENV{SERVER_PORT} = 443
$ENV{SERVER_PROTOCOL} = HTTP/1.1
$ENV{SERVER_SIGNATURE} = Apache/1.3.41 Server at XXXX.co.uk Port 443
$ENV{SERVER_SOFTWARE} = Apache/1.3.41 (Darwin) mod_ssl/2.8.31 OpenSSL/0.9.7l

===============================================================
Fergus McMenemie Email:fergus@twig.me.uk
Techmore Ltd Phone:(UK) 07721 376021

Unix/Mac/Intranets Analyst Programmer
===============================================================

--
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] Donde se ha ido el pg_config

creo que en /usr/lib/postgresqlxxxx/bin

(creo , no uso debian)

gb.-

2008/5/23 Raul Andres Duque <ra_duque@yahoo.com.mx>:
> Cordial Saludo listeros.
>
> Tengo un Debian Etch con postgresql 8.3 instalado desde apt-get.
>
> Intento compilar/instalar el DBT2, ya he cumplido todo los prerequisitos sin
> embargo me dice que no encuentra en el path el pg_config ... he buscado esta
> utilidad por toda la máquina y efectivamente no existe !!!
>
> Mi primera impresión era que depronto la habían eliminado en 8.3, pero
> leyendo la documentación no es así:
>
> http://www.postgresql.org/docs/8.3/static/app-pgconfig.html
>
> Alguna sugerencia?
>
> Atentamente,
>
> RAUL DUQUE
> Bogotá, Colombia
>
>

--
Guido Barosio
-----------------------
http://www.globant.com
guido.barosio@globant.com
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

[pgsql-es-ayuda] Donde se ha ido el pg_config

Cordial Saludo listeros.

Tengo un Debian Etch con postgresql 8.3 instalado desde apt-get.

Intento compilar/instalar el DBT2, ya he cumplido todo los prerequisitos sin embargo me dice que no encuentra en el path el pg_config ... he buscado esta utilidad por toda la máquina y efectivamente no existe !!!

Mi primera impresión era que depronto la habían eliminado en 8.3, pero leyendo la documentación no es así:

http://www.postgresql.org/docs/8.3/static/app-pgconfig.html

Alguna sugerencia?

Atentamente,

RAUL DUQUE
Bogotá, Colombia

Re: [GENERAL] Using role priviledges for pg_dump

Bill,

* Bill Moran (wmoran@collaborativefusion.com) wrote:
> In response to Bill Moran <wmoran@collaborativefusion.com>:
> > If I have a database called db1 to which the role dumpable has enough
> > permissions to do a full pg_dump, but he user joe does not, how can
> > joe do a pg_dump? Is it possible?
>
> Apologies, I left out a key piece of information:
> The role dumpable has superuser privileges, and this is the reason that
> dumpable is able to dump the database. As noted in the docs, superuser,
> createdb, and createrole privs don't seem to inherit. If I remove
> superuser from role dumpable, that role can't do pg_dump either.

I've got the exact same situation and I don't believe there's currently
a way to fix it in PostgreSQL. I've been asking for this ability on
-hackers and will probably come up with a patch to implement it soon.
In my ideal world it'd get into 8.4 and maybe back-patched to older
releases if it's not too invasive.

Glad to hear I'm not alone in wanting to have this ability though. :)

Thanks!

Stephen

Re: [pgus-board] Membership Benefits

Ahh; gotcha. We'll probably need to modify the mission statement to
make the relationship with the PostgreSQL Community Conference series
more clear.

---Michael Brewer
mbrewer@gmail.com

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

Re: [pgsql-es-ayuda] Problema con pg_restore

Gabriel Hermes Colina Zambra escribió:

> --- Alvaro Herrera <alvherre@commandprompt.com>
> escribió:

> > Prueba algo asi:
> >
> > PGCLIENTENCODING=latin1 pg_restore -d l151 <bla bla>
>
> Perdon Alavaro, en windows se definiria asi tambien ??
> me refiero a poner primero pgclientencoding=latin1
> por que si pgclientencoding es una variable de
> ambiente quiza tendria que ser
> set pgclientencoding=latin1 <enter>
> y entonces pg_restore -d 1151 <bla bla> <enter>

Es una variable de ambiente. No tengo idea como funciona Windows a
estas alturas.

Si yo tuviera que usar Windows, instalaria bash y lo haria como dije
arriba.

Si no pudiera instalar bash, le pediria al administrador.

Si el admnistrador no lo instalara, le prenderia fuego.

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

[BUGS] BUG #4190: sparc64 test suite fails

The following bug has been logged online:

Bug reference: 4190
Logged by: Dennis Gilmore
Email address: dennis@ausil.us
PostgreSQL version: 8.3.1
Operating system: Linux
Description: sparc64 test suite fails
Details:

errors test fails. the following is the output. which to me indicates it
passed but gave different output.

*** ./expected/errors.out Sat Nov 10 09:36:44 2007
--- ./results/errors.out Fri May 23 09:18:27 2008
***************
*** 304,312 ****
select 1/0;
ERROR: division by zero
select 1::int8/0;
! ERROR: division by zero
select 1/0::int8;
! ERROR: division by zero
select 1::int2/0;
ERROR: division by zero
select 1/0::int2;
--- 304,314 ----
select 1/0;
ERROR: division by zero
select 1::int8/0;
! ERROR: floating-point exception
! DETAIL: An invalid floating-point operation was signaled. This probably
means an out-of-range result or an invalid operation, such as division by
zero.
select 1/0::int8;
! ERROR: floating-point exception
! DETAIL: An invalid floating-point operation was signaled. This probably
means an out-of-range result or an invalid operation, such as division by
zero.
select 1::int2/0;
ERROR: division by zero
select 1/0::int2;

======================================================================

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

[Suspected Spam] Re: [GENERAL] F-Secure and PostgreSQL

----- Original Message -----
From: "Konsta Tiihonen" <konsta@gmx.net>
To: <pgsql-general@postgresql.org>
Sent: Friday, May 23, 2008 5:29 AM
Subject: Re: [GENERAL] F-Secure and PostgreSQL

>
>> Konsta Tiihonen wrote:
>> > Hi,
>> >
>> > after some research a month ago I found out that there is some issue
>> > between the software F-secure Internet Security (2006, 2007, 2008)
>> > and the Windows version of postgreSQL. I cannot connect to
>> > postgreSQL.
>>
>> You probably need to check the F-Secure manual for details, but
>> presumably you need to stop the firewall from blocking port 5432 (which
>> is what PostgreSQL uses).
>>
>> Can you connect from the maching PostgreSQL is installed on? It would be
>> unusual if the firewall is blocking localhost.
>>
>> Oh, and you'll want to make sure the anti-virus scanner isn't scanning
>> the database folder too. There have been cases where av scanners
>> interfere with the database.
>
> I opened the port 5432 inbound and outbound already. As far as I konw
> there are some other ports for negotiation as well. I opened those up,
> too. Connecting to it is not possible. The postgreSQL service is running
> on the same machine, so there is no other machine I could try to connect
> from.
>
> I will give 'not scanning the directory' a shot.
>
> Is there somebody else who is running F-Secure and postgreSQL on a Windows
> PC or am I the only person with this setting?
>

I was able to get PostgeSQL working with F-Secure on Windows XP, but it was
INCREDIBLY slow. I was not able to resolve the problem even with the
assistance of my ISP tech support staff who supplies F-Secure as part of my
internet service package. Turning off the F-Secure service (all components)
did NOT speed things up. PostgreSQL only returned to normal when the
F-Secure software was totally uninstalled. I installed and uninstalled
F-Secure several times to duplicate the problem. I finally gave up and
installed Norton Internet Security 2008 and everything ran normally with no
special configuration required.

George

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

--
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] Problema con pg_restore

--- Alvaro Herrera <alvherre@commandprompt.com>
escribió:

> Victor Benitez escribió:
> > Estimados,
> >
> >
> > Tengo el siguiente problema, necesito levantar un
> respaldo de una base
> > de datos (de una tabla en particular) y me arroja
> el siguiente Error:
> >
> >
> > pg_restore -d l151 respaldo.backup --data-only -t
> tabla -e
> > --no-data-for-failed-tables -c -v
>
> > pg_restore: [archiver (db)] COPY failed: ERROR:
> secuencia de bytes no
> > válida para codificación «UTF8»: 0x9e
> > HINT: Este error tambien puede ocurrir si la
> secuendia de bites no
> > coincide con la codificacion esperada por el
> servidor, lo cual es
> > controlado por "client_encoding".
>
> "secuendia de bites"? Es eso literal en el mensaje,
> o te pifiaste al
> transcribirlo?
>
> Prueba algo asi:
>
> PGCLIENTENCODING=latin1 pg_restore -d l151 <bla bla>

Perdon Alavaro, en windows se definiria asi tambien ??
me refiero a poner primero pgclientencoding=latin1
por que si pgclientencoding es una variable de
ambiente quiza tendria que ser
set pgclientencoding=latin1 <enter>
y entonces pg_restore -d 1151 <bla bla> <enter>

Atte.
Gabriel Colina

>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
> --
> TIP 1: para suscribirte y desuscribirte, visita
> http://archives.postgresql.org/pgsql-es-ayuda
>

____________________________________________________________________________________
Yahoo! Deportes Beta
¡No te pierdas lo último sobre el torneo clausura 2008! Entérate aquí http://deportes.yahoo.com
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [BUGS] BUG #4187: function to_tsvector not immutable

"Martin Sullivan" <ms08@zois.co.uk> writes:
> The only wrinkle being that to_tsvector needs to be ALTERed
> to IMMUTABLE using the following (as user postgres):
> alter function to_tsvector(regconfig,text) immutable
> alter function to_tsvector(text) immutable

The current settings are intentional: the one-parameter form of
to_tsvector depends on a GUC parameter, so claiming it is immutable
is simply wrong. Please read the documentation concerning how to
use these functions with an index.

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

Re: [GENERAL] Error: Could not open relation...

On Friday 23 May 2008 6:02 am, Howard Cole wrote:
> Howard Cole wrote:
> >> Looks like someone or something changed the permissions on the
> >> postgresql folders or files.
> >>
> >> Sincerely,
> >>
> >> Joshua D. Drake
> >
> > I've had a look at this file, and postgres has "Full Control".
> > Howard
>
> Further, the system works fine normally. The permissions error appears
> to be an isolated error. Is it possible that this error means something
> else?
>
> Are there likely to be serious integrety implications if Postgres failed
> to access/write to this table for whatever reason, or would the
> transaction be rolled back.

Previous reports of this type of intermittent behavior on Windows have been
traced back to AV software.

--
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: [GENERAL] Results of stored procedures in WHERE clause

I'm assuming you are having problems because the tree structure allows
for n levels and you need to get all of the child records.

I am guessing you mean this:

create table cms_items
(itm_id integer primary key not null,
itm_parent integer default 0 not null,
url varchar(100) not null);

insert into cms_items values (1, 0, 'postgresql.org');
insert into cms_items values (2, 1, 'foo');
insert into cms_items values (3, 1, 'bar');
insert into cms_items values (4, 0, 'cnn.com');
insert into cms_items values (5, 2, 'foo2');
insert into cms_items values (6, 5, 'foo3');

create or replace function fn_get_root (p_itm_id integer) returns setof
cms_items as
$$
declare
v_rec cms_items;
v_rec2 cms_items;
i integer := 0;
v_last_itm_id cms_items.itm_id%type;

begin
<<outside_loop>>
for v_rec in select * from cms_items where itm_parent = p_itm_id loop
return next v_rec;
<<inside_loop>>
while i is not null loop
i := i + 1;

if i = 1 then
v_last_itm_id := v_rec.itm_id;
end if;

select * into v_rec2 from cms_items where itm_parent =
v_last_itm_id;

if v_rec2.itm_id is not null then
return next v_rec2;
else
i := null;
end if;

v_last_itm_id := v_rec2.itm_id;

end loop inside_loop;
end loop outside_loop;

end;
$$
language 'plpgsql';

select * from fn_get_root(1);
2;1;"foo"
5;2;"foo2"
6;5;"foo3"
3;1;"bar"

It gets the direct child records and then it also gets the child's child
(foo2) and then the child's child's child (foo3). It will go all of the
way through the hierarchy too.

Jon
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Gordon
> Sent: Tuesday, May 20, 2008 11:03 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Results of stored procedures in WHERE clause
>
> I have a table representing tree structures of pages on a website.
> they have an itm_id column (integer key) and an itm_parent column
> (pointer to item's parent node). Any item with an itm_parent of 0 is
> a root node, representing a website. Anything with a non-zero parent
> is a non-root node representing a folder or document in a website.
>
> I need to be able to do queries that restrict my result set to items
> belonging to a specified site and ignore all nodes that belong to
> different sites. To determine the ID of the site an item belongs to I
> wrote a stored procedure:
>
> CREATE OR REPLACE FUNCTION cms.getroot(node integer)
> RETURNS integer AS
> $BODY$DECLARE
> thisnode integer := node;
> thisparent integer := node;
> BEGIN
> WHILE thisparent != 0 LOOP
> SELECT itm_id, itm_parent
> INTO thisnode, thisparent
> FROM cms.cms_items
> WHERE itm_id = thisparent;
> END LOOP;
> RETURN thisnode;
> END;
> $BODY$
> LANGUAGE 'plpgsql' STABLE
> COST 100;
>
> This returns the ID of the root node for non-root nodes, the node's
> own ID for root-nodes and NULL for invalid IDs.
>
> I'm writing a query to do document searching (the version given is
> simplified to the problem in hand).
>
> SELECT cms_v_items.* ,
> getroot (cms_v_items.itm_id) AS itm_root
> FROM cms_v_items
> WHERE itm_root = ?;
>
> I was hoping this query would return a set of items that had the same
> root node. Instead it throws an error, column itm_root does not
> exist.
>
> I'm obviously doing something wrong here, but what?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

[PERFORM] index performance on large tables with update and insert

I have a large table with about 2 million rows and it will keep growing...

I need to do update/inserts, and select as well.

An index will speed up the select, but it will slow down the updates.

Are all Postgres indexes ordered? i.e., with every update, the index pages will have to be physically reordered?

Does Postgres have any kind of non-ordered indexes (like Syabse's non-clustered index)?

What is the common way to take care of the performance issue when you have to do both update and select on the same large table?

Thanks,
Jessica

[ADMIN] index performance

Re: [JDBC] How embarrassing: optimization of a one-shot query doesn't work

Dave Cramer <pg@fastcrypt.com> writes:
> Any word on 8.3.2 ?

Obviously, nothing is happening during PGCon ;-)

There was some discussion a week or so back about scheduling a set of
releases in early June, but it's not formally decided.

regards, tom lane

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

Friday, May 23, 2008

Blog Archive