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:

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 ?

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

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

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

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

[pgsql-advocacy] Is the biggest SQL database in the world now a Postgres fork?

Interesting article here:

http://www.informationweek.com/news/software/database/showArticle.jhtml?articleID=207801436

Yahoo Claims Record With Petabyte Database

Yahoo claims it has the largest SQL database in a production
environment and that it will grow larger.
...
Yahoo started with the PostgreSQL engine and replaced the query
processing layer with code designed for its commodity hardware cluster.

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

Re: [NOVICE] How to connect to the database server

Selon Guillaume Yziquel <guillaume.yziquel@bluebottle.com>:

> Harold A. Gim�nez Ch. a �crit :
> > Have you tried loging in with the default user (postgres)?
> >
> > psql -U postgres
> >
> > or, try su'ing as postgres and login in then:
> >
> > su - postgres<enter>
> > psql<enter>

Hello, Harold.

I thank you very much for your answer.

psql -U postgres yields:

> yziquel@seldon:~$ psql -U postgres
> psql: impossible de se connecter au serveur: Permission non accord�e
> Est-ce que le serveur tourne sur l'h�te local
> et accepte des connexions au socket Unix �
/var/run/postgresql/.s.PGSQL.5432 �?

Doing su - postgres and psql yields:

> postgres@seldon:~$ psql
> psql: FATAL: La base de donn�es � postgres � n'existe pas
> postgres@seldon:~$

Seems like I need to know what are the databases inside my cluster...

Thanks again for your help.

Guillaume.

> > On Thu, May 22, 2008 at 1:14 PM, <guillaume.yziquel@free.fr
> > <mailto:guillaume.yziquel@free.fr>> wrote:
> >
> > Hello list.
> >
> > My problem is the following: I've installed a postgresql server on
> > my debian
> > GNU/linux system. I've not installed for postgresql itself, but
> > rather because I
> > needed a database server for other applications that use the
> > database for
> > storage purposes.
> >
> > Usually, using aptitude is always a pleasure... However, I now have
> > a database
> > server and I do not have any more the credentials to access the
> > database as the
> > database administrator (or perhaps I have them somewhere, but I'm
> > unable to find
> > them again).
> >
> > My question is the following: How can I get to connect directly to
> > the database
> > as the database superuser? Do I need to delete and reinstall my
> > whole database?
> >
> > I've been looking for documentation on this topic, but perhaps in
> > the wrong
> > placem or perhaps in the right but failing to see the light... So if
> > you have a
> > relevant link, I'm dying to hear about it.
> >
> > Guillaume.

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

Re: [PERFORM] Index creation time and distribution

On Thu, May 22, 2008 at 6:50 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Just curious, what happens if you create the date index first, then
> the clazz one?

It's not due to any cache effect if it's your question. It's mostly
CPU time and changing the order doesn't change the behaviour.

I'll make some tests with 8.3 in a few weeks (I'll be out of town next
week) to see if using PostgreSQL qsort reduces the problem.

--
Guillaume

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

[PATCHES] Extending grant insert on tables to sequences

? config.log
? config.status
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.146
diff -c -r1.146 aclchk.c
*** src/backend/catalog/aclchk.c 12 May 2008 00:00:46 -0000 1.146
--- src/backend/catalog/aclchk.c 22 May 2008 18:13:18 -0000
***************
*** 360,365 ****
--- 360,402 ----
}

ExecGrantStmt_oids(&istmt);
+
+ /*
+ * If the objtype is a relation and the privileges includes INSERT, UPDATE
+ * or SELECT then extends the GRANT/REVOKE to the sequences owned by the
+ * relation
+ */
+ if (istmt.objtype == ACL_OBJECT_RELATION) &&
+ (istmt.privileges & (ACL_INSERT | ACL_UPDATE | ACL_SELECT))
+ {
+ AclMode priv;
+ foreach(cell, istmt.objects)
+ {
+ InternalGrant istmt_seq;
+
+ istmt_seq.is_grant = istmt.is_grant;
+ istmt_seq.objtype = ACL_OBJECT_SEQUENCE;
+ istmt_seq.grantees = istmt.grantees;
+ istmt_seq.grant_option = istmt.grant_option;
+ istmt_seq.behavior = istmt.behavior;
+
+ istmt_seq.all_privs = false;
+ istmt_seq.privileges = ACL_NO_RIGHTS;
+
+ istmt_seq.objects = getOwnedSequences(lfirst_oid(cell));
+ if (istmt_seq.objects != NIL)
+ {
+ if (istmt.privileges & (ACL_INSERT))
+ istmt_seq.privileges |= ACL_USAGE;
+ else if (istmt.privileges & (ACL_UPDATE))
+ istmt_seq.privileges |= ACL_UPDATE;
+ else if (istmt.privileges & (ACL_SELECT))
+ istmt_seq.privileges |= ACL_SELECT;
+
+ ExecGrantStmt_oids(&istmt_seq);
+ }
+ }
+ }
}

/*
Hi,

The idea of this patch is to avoid the need to make explicit grants on
sequences owned by tables.

This patch make:
- GRANT INSERT ON TABLE extend to GRANT USAGE ON SEQUENCE (currval, nextval)
- GRANT UPDATE ON TABLE extend to GRANT UPDATE ON SEQUENCE (nextval, setval)
- GRANT SELECT ON TABLE extend to GRANT SELECT ON SEQUENCE (currval)

comments?

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157

Re: [ADMIN] Index problem...

On Thu, May 22, 2008 at 01:37:56PM -0400, Carol Walter wrote:
> When I ran the query you specified I got relname of "pg_toast_16429.
> When I ran the REINDEX TABLE pg_toast_16429 the system responds that
> relation "pg_toast_16429" does not exist.

I think it should be pg_toast.pg_toast_16429
so:
REINDEX TABLE pg_toast.pg_toast_16429;

check relnamespace in pg_class for this table, and check it against
pg_namespace to be sure.

depesz

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

Re: [NOVICE] How to connect to the database server

Have you tried loging in with the default user (postgres)?

psql -U postgres

or, try su'ing as postgres and login in then:

su - postgres<enter>
psql<enter>

On Thu, May 22, 2008 at 1:14 PM, <guillaume.yziquel@free.fr> wrote:

Hello list.

My problem is the following: I've installed a postgresql server on my debian
GNU/linux system. I've not installed for postgresql itself, but rather because I
needed a database server for other applications that use the database for
storage purposes.

Usually, using aptitude is always a pleasure... However, I now have a database
server and I do not have any more the credentials to access the database as the
database administrator (or perhaps I have them somewhere, but I'm unable to find
them again).

My question is the following: How can I get to connect directly to the database
as the database superuser? Do I need to delete and reinstall my whole database?

I've been looking for documentation on this topic, but perhaps in the wrong
placem or perhaps in the right but failing to see the light... So if you have a
relevant link, I'm dying to hear about it.

Guillaume.

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

[pgsql-es-ayuda] Problema con pg_restore

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: connecting to database for restore
pg_restore: restoring data for table "tala"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1793; 0 16482 TABLE
DATA tabla postgres
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".
CONTEXT: COPY tabla, línea 2
pg_restore: *** aborted because of error

La tabla en cuestión tiene un campo bytea, El server esta WINXP y la
version de Postgres es 8.2.1,

Agradeciendo su Ayuda.

Victor Benitez

--
TIP 5: �Has le�do nuestro extenso FAQ?

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

Re: [GENERAL] Short-circuiting FK check for a newly-added field

On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote:
> I need to add a field to a fairly large table. In the same alter statement
> I'd like to add a FK constraint on that new field. Is there any way to
> avoid the check of the table that the database is doing right now? The
> check is pointless because the newly added field is nothing but NULLs.
>
I don't see the problem. FK constraints don't fire on NULL values. I
think you might be imagining that a problem exists when it doesn't.

If the FK column is created as NOT NULL, there is a problem. The best
way to handle this case is to add the column (allowing nulls), populate
the columns, then alter the column to make it NOT NULL. You can wrap
that all in a transaction if you like.

> This is version 8.1.mumble.
>
You can get the version using select version().

-Reece

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

Re: [ADMIN] Index problem...

When I ran the query you specified I got relname of "pg_toast_16429.
When I ran the REINDEX TABLE pg_toast_16429 the system responds that
relation "pg_toast_16429" does not exist.

Carol

On May 22, 2008, at 1:02 PM, Tomeh, Husam wrote:

>
> You may want to try to reindex the associated toast table and see if
> that clears it up.
>
> To get the name of the associated toast table, you may run this query:
>
> select relname from pg_class where oid =
> (select reltoastrelid from pg_class where relname = 'maps');
>
> Then, reindex it.
>
> reindex table "toast table name" ..
>
> -------
>
> Regards,
> Husam
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Carol Walter
> Sent: Thursday, May 22, 2008 9:42 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Index problem...
>
> Greetings...
>
> I'm getting an error. The error says...
>
> places=# REINDEX INDEX maps_map_id_seq;
> ERROR: "maps_map_id_seq" is not an index
> places=# SELECT * FROM maps;
> ERROR: missing chunk number 0 for toast value 131343627
>
> I have tried reindexing the index, the table, and the database. I
> continue to get this error. What can I do to recover from this.
>
> Carol Walter
>
> PS Let me say that I know this is my fault. This index is very
> bloated. Yesterday, I tried to vacuum. I got errors because
> max_fsm_pages what set at a number that was too small.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
> **********************************************************************
> This message contains confidential information intended only for
> the use of the addressee(s) named above and may contain information
> that is legally privileged. If you are not the addressee, or the
> person responsible for delivering it to the addressee, you are
> hereby notified that reading, disseminating, distributing or
> copying this message is strictly prohibited. If you have received
> this message by mistake, please immediately notify us by replying
> to the message and delete the original message immediately thereafter.
>
> Thank you.
>
> FADLD Tag
> **********************************************************************

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

[NOVICE] How to connect to the database server

Hello list.

My problem is the following: I've installed a postgresql server on my debian
GNU/linux system. I've not installed for postgresql itself, but rather because I
needed a database server for other applications that use the database for
storage purposes.

Usually, using aptitude is always a pleasure... However, I now have a database
server and I do not have any more the credentials to access the database as the
database administrator (or perhaps I have them somewhere, but I'm unable to find
them again).

My question is the following: How can I get to connect directly to the database
as the database superuser? Do I need to delete and reinstall my whole database?

I've been looking for documentation on this topic, but perhaps in the wrong
placem or perhaps in the right but failing to see the light... So if you have a
relevant link, I'm dying to hear about it.

Guillaume.

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

[GENERAL] Is this correct usage of generate_series?

Hi, there.

These three queries below works as I expect, which is wonderful, but are these correct usages?
If these are intended features, I would like them to be documented at
http://www.postgresql.org/docs/8.3/interactive/functions-srf.html

=> select generate_series(1, 3) as i;
 i
---
 1
 2
 3
(3 rows)

=> select 'a' as a, generate_series(1, 3) as i;
 a | i
---+---
 a | 1
 a | 2
 a | 3
(3 rows)

=> select 'a' as a, i from generate_series(1, 3) i;
 a | i
---+---
 a | 1
 a | 2
 a | 3
(3 rows)

Here is an example using this technique, which shows column positions and names in
the specified index.

select attnum, attname
from pg_catalog.pg_attribute a
join (
    select
    indrelid, indkey[i] as pos
    from (
        select
        indrelid, indkey, generate_series(lb, ub) as i
        from (
            select indrelid, indkey, array_lower(indkey, 1) as lb, array_upper(indkey, 1) as ub
            from pg_catalog.pg_index
            where indexrelid = (
                select oid
                from pg_catalog.pg_class
                where relnamespace = (select oid from pg_catalog.pg_namespace where nspname = 'public')
                and relkind = 'i'
                and relname = '<your_index_name_here>'
            )
        ) x
    ) y
) z on a.attrelid = z.indrelid and a.attnum = z.pos;

Thanks in advance,
Hiroaki Nakamura

Re: [ADMIN] Index problem...

You may want to try to reindex the associated toast table and see if
that clears it up.

To get the name of the associated toast table, you may run this query:

select relname from pg_class where oid =
(select reltoastrelid from pg_class where relname = 'maps');

Then, reindex it.

reindex table "toast table name" ..

-------

Regards,
Husam

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Carol Walter
Sent: Thursday, May 22, 2008 9:42 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Index problem...

Greetings...

I'm getting an error. The error says...

places=# REINDEX INDEX maps_map_id_seq;
ERROR: "maps_map_id_seq" is not an index
places=# SELECT * FROM maps;
ERROR: missing chunk number 0 for toast value 131343627

I have tried reindexing the index, the table, and the database. I
continue to get this error. What can I do to recover from this.

Carol Walter

PS Let me say that I know this is my fault. This index is very
bloated. Yesterday, I tried to vacuum. I got errors because
max_fsm_pages what set at a number that was too small.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

FADLD Tag
**********************************************************************

--
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] Finding records that are not there

At 6:47 PM +0200 5/22/08, hubert depesz lubaczewski wrote:
>On Thu, May 22, 2008 at 12:21:35PM -0400, Owen Hartnett wrote:
>> The SQL I've tried is:
>> select commcost.maplot, commcost.unitno from commcost
>> where not exists(select 1 from commcost, bldg
>> where commcost.maplot = bldg.maplot and
>> commcost.unitno = bldg.unitno)
>> order by commcost.maplot
>
>change it to:
>
>> select commcost.maplot, commcost.unitno from commcost
>> where not exists(select 1 from bldg
>> where commcost.maplot = bldg.maplot and
>> commcost.unitno = bldg.unitno)
>> order by commcost.maplot
>
>or simply write:
>
>select * from commcost except select * from bldg;
>
>depesz

Thank you very much for your quick response!

-Owen

--
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] Finding records that are not there

You have commcost in the correlated subquery which shouldn't be there.

Here are three ways to achieve the results you want:

select commcost.maplot,
commcost.unitno
from commcost
where not exists(select null
from bldg
where commcost.maplot = bldg.maplot
and commcost.unitno = bldg.unitno)
order by commcost.maplot;

select commcost.maplot,
commcost.unitno
from commcost
left join bldg
on commcost.maplot = bldg.maplot
and commcost.unitno = bldg.unitno
where bldg.maplot is null
order by commcost.maplot;

select commcost.maplot,
commcost.unitno
from commcost
except
select bldg.maplot,
bldg.unitno
from bldg
order by maplot;

Jon

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Owen Hartnett
> Sent: Thursday, May 22, 2008 11:22 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Finding records that are not there
>
>
> Hi:
>
> This is gotta be elementary SQL 101, but I'm having a mental block as
> to why this doesn't work.
>
> I have two tables that have identical index fields, maplot and
> unitno, (both indexes span two columns) and I want to find all the
> records in the commcost table that don't have a corresponding record
> in the bldg file.
>
> The SQL I've tried is:
>
> select commcost.maplot, commcost.unitno from commcost
> where not exists(select 1 from commcost, bldg
> where commcost.maplot = bldg.maplot and
> commcost.unitno = bldg.unitno)
> order by commcost.maplot
>
> It returns no records although I know that there are records in
> commcost which do not match keys with records from bldg.
>
> Help! What am I doing wrong?
>
> -Owen
>
> --
> 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: [PERFORM] Index creation time and distribution

On Thu, May 22, 2008 at 6:32 AM, Guillaume Smet
<guillaume.smet@gmail.com> wrote:
> Hi -performance,
>
>
> LOG: duration: 1636301.317 ms statement: CREATE INDEX
> index_journal_clazz ON journal USING btree (clazz);
> LOG: duration: 20613.009 ms statement: CREATE INDEX
> index_journal_date ON journal USING btree (date);

Just curious, what happens if you create the date index first, then
the clazz one?

--
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] Finding records that are not there

On Thu, May 22, 2008 at 12:21:35PM -0400, Owen Hartnett wrote:
> The SQL I've tried is:
> select commcost.maplot, commcost.unitno from commcost
> where not exists(select 1 from commcost, bldg
> where commcost.maplot = bldg.maplot and
> commcost.unitno = bldg.unitno)
> order by commcost.maplot

change it to:

> select commcost.maplot, commcost.unitno from commcost
> where not exists(select 1 from bldg
> where commcost.maplot = bldg.maplot and
> commcost.unitno = bldg.unitno)
> order by commcost.maplot

or simply write:

select * from commcost except select * from bldg;

depesz

--
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] Finding records that are not there

> I have two tables that have identical index fields, maplot and
> unitno, (both indexes span two columns) and I want to find all the
> records in the commcost table that don't have a corresponding record
> in the bldg file.
>
> The SQL I've tried is:
>
> select commcost.maplot, commcost.unitno from commcost
> where not exists(select 1 from commcost, bldg
> where commcost.maplot = bldg.maplot and
> commcost.unitno = bldg.unitno)
> order by commcost.maplot
>
> It returns no records although I know that there are records in
> commcost which do not match keys with records from bldg.
>

You shouldn't put "commcost" in your inner select, since it's
already in your outer select.

Or try this, it's probably faster:

Select commcost.maplot, commcost.unitno from commcost c
left join bldg b on c.maplot = b.maplot and c.unitno = b.unitno
where b.unitno is null

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

[ADMIN] Index problem...

Greetings...

I'm getting an error. The error says...

places=# REINDEX INDEX maps_map_id_seq;
ERROR: "maps_map_id_seq" is not an index
places=# SELECT * FROM maps;
ERROR: missing chunk number 0 for toast value 131343627

I have tried reindexing the index, the table, and the database. I
continue to get this error. What can I do to recover from this.

Carol Walter

PS Let me say that I know this is my fault. This index is very
bloated. Yesterday, I tried to vacuum. I got errors because
max_fsm_pages what set at a number that was too small.

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

[GENERAL] Finding records that are not there

Hi:

This is gotta be elementary SQL 101, but I'm having a mental block as
to why this doesn't work.

I have two tables that have identical index fields, maplot and
unitno, (both indexes span two columns) and I want to find all the
records in the commcost table that don't have a corresponding record
in the bldg file.

The SQL I've tried is:

select commcost.maplot, commcost.unitno from commcost
where not exists(select 1 from commcost, bldg
where commcost.maplot = bldg.maplot and
commcost.unitno = bldg.unitno)
order by commcost.maplot

It returns no records although I know that there are records in
commcost which do not match keys with records from bldg.

Help! What am I doing wrong?

-Owen

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