Re: [GENERAL] Populating a sparse array piecemeal in plpgsql

Thank you for your reply, but I don't really understand how to use this
information.
My problem is that I can put one value into this array, and that's it.
Any subsequent attempts to put another value elsewhere in the array are
rebuffed, saying that the subscripts are out of range. I don't
understand what I would do "by hand" that would help this.

Topher Eliot
christopher.eliot@nagrastar.com
[]

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, May 15, 2008 12:30 AM
> To: Eliot, Christopher
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Populating a sparse array piecemeal in plpgsql
>
> "Eliot, Christopher" <christopher.eliot@nagrastar.com> writes:
> > CREATE FUNCTION func1()
> > RETURNS VOID AS $$
> > DECLARE
> > a INTEGER[2][2][2][200];
> > BEGIN
> > a[1][2][1][33] = 0;
> > a[2][1][1][33] = 0;
> > END;
> > $$ LANGUAGE PLPGSQL;
>
> > When I run this function, I get:
> > ERROR: array subscript out of range.
>
> Yeah. I'm afraid that declaration is basically just noise:
> it doesn't do anything for you that "DECLARE a integer[];"
> wouldn't do. If you want the array actually filled out to
> the indicated dimensions then you have to do that by hand.
>
> regards, tom lane
>

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

Re: [BUGS] problem in installing pgsql-8.3.1

shohorab hossain wrote:
> Dear sir/madam,
>
> This is to inform you that I am an oracle database user/administrator. But I am going to join in a company where I have to administrate postgresql database. I am new to this database system. For few days I am trying to install postgresql-8.3.1 in RHEL4. My machine configuration is Intel Pentium IV. I have followed the installation document of postgresql that was included with source distribution.
>
> I am facing the problem during the building step of source. The problem is that when I start building with gmake command, it continuously shows messages in console like 'checking for …………. yes / no' and I have waited approximately for 3 hours but it continues. At last I have cancelled the building process.
>
> Please give me your appropriate and valuable solution in this regard. I am eagerly looking forward for your quick reply.
>

btw, this belongs on a different mail list than postgres bugs, as its
highly unlikely its any sort of postgres bug.

I just grabbed 8.3.1 source on a RHEL5 (centos 5) system running on a
p3-800Mhz, 512MB ram, single IDE drive system.... the ./configure step
took

$ ./configure
.......
real 0m57.002s
user 0m33.256s
sys 0m21.346s

eg, about a minute. the rest of the make, the actual compilation phase
is taking somewhat longer.

$ make
.......
All of PostgreSQL successfully made. Ready to install.

real 11m45.484s
user 10m50.175s
sys 0m47.616s

but, generally, on a RHEL system, you should install the RPMs from the
postgres website...
http://www.postgresql.org/ftp/binary/v8.3.1/linux/rpms/redhat/rhel-4-i386/

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

Re: [HACKERS] [rfc,patch] PL/Proxy in core

On 5/15/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Marko Kreen" <markokr@gmail.com> writes:
> > How about following patch? I have bison 2.3 and it seems not to do
> > global allocation, so it should be fine. There may be early exit
> > with elog(ERRROR) inside so I'd like to avoid malloc() itself.
>
> None of our other parsers fool with bison's memory allocation;
> why does yours need to?

Because that way I can be sure I understand their allocation behaviour.

Eg. how does src/backend/parser/gram.c not leak memory on syntax error?
I don't understand it.

But if I force them use palloc(), always, I can be sure memore is freed.

--
marko

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

Re: [pgadmin-support] Please help! Postgres stopped working after restarting computer!

Wei,

> PostgreSQL version: 8.3.1
> Operating system: windows xp
> Description: postgres stops working after
> restart
> Details:
> I installed 8.3.1 fine and the service starts and
> works fine. However, after I restart the computer and opens up pgAdmin3.exe and

> "Failed to start server pgsql-8.3:Errcode=1069. Check
> event log for
> details."

So, propably you have installed PostgreSQL as a service (= standard on Windows )

Please digg out the eventlog of Windows (eventvwr is the programm) and
look into the application log.

To see what failed, also open services.msc and try to start pgsql-8.3
manually. After failure, recheck application error log with eventvwr
(do not forget to refresh)

> The log folder doesn't show any error problems. When I
What is EXACTLY created within data\pg_log?

is there a new logfile with a name/timestamp indicating it was created
"just at last system start" ? what is in that logfile?

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

Re: [pgadmin-hackers] multi-select for privileges

On Thu, May 15, 2008 at 2:32 PM, Kev <kevinjamesfield@gmail.com> wrote:
> Hi,
>
> This is just a wish-list item if anybody is interested in implementing
> it. I think it would make pgadmin pretty powerful in terms of
> managing privileges.
>
> When you click "Views", "Tables", etc. in the tree at the left in the
> main view, the Properties tab at the right shows a grid of objects and
> their comments. This grid has multi-select enabled, which makes it
> handy to drop multiple objects at once, but if you right-click and
> choose Properties, you can only modify one object at a time. It would
> be very handy to be able to select many objects and define who has
> what access to them, especially in the case when you're creating a new
> login group and want to give the group access to a large set of
> tables.
>
> What do you think?

You can use the Grant Wizard for that - though it's not quite as
convenient as selecting them all in the list.

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

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

[BUGS] problem in installing pgsql-8.3.1

Dear sir/madam,

This is to inform you that I am an oracle database user/administrator. But I am going to join in a company where I have to administrate postgresql database. I am new to this database system.  For few days I am trying to install postgresql-8.3.1 in RHEL4. My machine configuration is Intel Pentium IV. I have followed the installation document of postgresql that was included with source distribution.

I am facing the problem during the building step of source. The problem is that when I start building with gmake command, it continuously shows messages in console like 'checking for …………. yes / no' and I have waited approximately for 3 hours but it continues.  At last I have cancelled the building process.

Please give me your appropriate and valuable solution in this regard. I am eagerly looking forward for your quick reply.

Thanks in advance:
---------------------------
Hopeless
Shohorab

Send instant messages to your online friends http://uk.messenger.yahoo.com

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

Re: [BUGS] BUG #4169: Problem in installing

"shohorab hossain" <shohorab23@yahoo.com> writes:
> I am facing the problem during the building step of source. The problem is
> that when I start building with gmake command, it continuously shows
> messages in console like ‘checking for …………. yes / no’ and I
> have waited approximately for 3 hours but it continues. At last I have
> cancelled the building process.

Check your system clock. I have seen make get into a loop like that
when the source files all appeared to have timestamps in the future.

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

[pgadmin-hackers] SVN Commit by dpage: r7292 - in trunk/pgadmin3: . pgadmin/dlg pgadmin/include/utils pgadmin/schema pgadmin/ui

Author: dpage

Date: 2008-05-15 15:25:45 +0100 (Thu, 15 May 2008)

New Revision: 7292

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

Log:
Add support for TRUNCATE triggers on PostgreSQL 8.4+.

Modified:
trunk/pgadmin3/CHANGELOG
trunk/pgadmin3/pgadmin/dlg/dlgTrigger.cpp
trunk/pgadmin3/pgadmin/include/utils/pgDefs.h
trunk/pgadmin3/pgadmin/schema/pgTrigger.cpp
trunk/pgadmin3/pgadmin/ui/dlgTrigger.xrc
trunk/pgadmin3/pgadmin/ui/xrcDialogs.cpp

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

[pgadmin-support] Please help! Postgres stopped working after restarting computer!

Here is the general info:

Bug reference: 4172
Logged by: alex
Email address: shaselai@yahoo.com
PostgreSQL version: 8.3.1
Operating system: windows xp
Description: postgres stops working after
restart
Details:

I installed 8.3.1 fine and the service starts and
works fine. However,
after
I restart the computer and opens up pgAdmin3.exe and
try to start the
service again i get error message:
"Failed to start server pgsql-8.3:Errcode=1069. Check
event log for
details."

The log folder doesn't show any error problems. When I
run the psql.exe
on
the command line i get:
"psql: could not connect to server: Connection refused
(0x0000274D/10061)
Is the server running on host "???" and
accepting
TCP/IP connections on port 5432? "
I checked using netstat -an and 5432 is not even
listed being in
use/listening. I also tried changing the port in
pgadmin to something
else
like 5050 but same error.

Right now the only "fix" i can come up with is to
uninstall and
reinstall...

is there a fix to this problem? thanks!

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

Re: [HACKERS] SSL and USER_CERT_FILE

On May 15, 2008, at 6:31 AM, pgsql@mohawksoft.com wrote:

>> Mark Woodward wrote:
>>> I am using PostgreSQL's SSL support and the conventions for the
>>> key and
>>> certifications don't make sense from the client perspective.
>>> Especially
>>> under Windows.
>>>
>>> I am proposing a few simple changes:
>>>
>>> Adding two API
>>> void PQsetSSLUserCertFileName(char *filename)
>>> {
>>> user_crt_filename = strdup(filename);
>>> }
>>> PQsetSSLUserKeyFileName(char *filename)
>>> {
>>> user_key_filename = strdup(filename);
>>> }
>>>
>>>
>>>
>> [snip]
>>> Any comments?
>>>
>>>
>>
>>
>> I think it would probably be much better to allow for some
>> environment
>> variables to specify the locations of the client certificate and key
>> (and the CA cert and CRL) - c.f. PGPASSFILE.
>>
>> That way not only could these be set by C programs but by any libpq
>> user
>> (I'm sure driver writers who use libpq don't want to have to bother
>> with
>> this stuff.) And we wouldn't need to change the API at all.
>>
>
> The problem I have with environment variables is that they tend not
> to be
> application specific and almost always lead to configuration issues.
> As a
> methodology for default configuration, it adds flexibility. Also, the
> current configuration does not easily take in to consideration the
> idea
> that different databases with different keys can be used from the same
> system the same user.

Environment variables don't have to be set in your shell.

This would seem to give the same functionality you suggest above,
given support for environment variables:

void PQsetSSLUserCertFileName(char * filename)
{
setenv("PGCERTFILE", filename);
}

void PQsetSSLUserKeyFileName(char *filename)
{
setenv("PGKEYFILE", filename);
}

Or, in perl, $ENV{PGKEYFILE} = $file and so on. It seems
less intrusive than adding new API calls.

Cheers,
Steve

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

Re: [pgsql-www] hub.org Nagios monitoring

I won't get to it until later today, but if you go into AMS and edit VM
options for the postgresql.org VPSs, you can mark them as Monitoring Off
... if you don't have time for this, i can do it when I get online this
evening ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org MSN . scrappy@hub.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664

On Thu, 15 May 2008, Dave Page wrote:

> Hi Marc,
>
> webmaster@postgresql.org is getting spammed with irrelevant nagios
> alerts from smc.hub.org. Can you fix that please?
>
> Thanks.
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>

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

[BUGS] BUG #4172: postgres stops working after restart

The following bug has been logged online:

Bug reference: 4172
Logged by: alex
Email address: shaselai@yahoo.com
PostgreSQL version: 8.3.1
Operating system: windows xp
Description: postgres stops working after restart
Details:

I installed 8.3.1 fine and the service starts and works fine. However, after
I restart the computer and opens up pgAdmin3.exe and try to start the
service again i get error message:
"Failed to start server pgsql-8.3:Errcode=1069. Check event log for
details."

The log folder doesn't show any error problems. When I run the psql.exe on
the command line i get:
"psql: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "???" and accepting
TCP/IP connections on port 5432? "
I checked using netstat -an and 5432 is not even listed being in
use/listening. I also tried changing the port in pgadmin to something else
like 5050 but same error.

Right now the only "fix" i can come up with is to uninstall and reinstall...
is there a fix to this problem? thanks!

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

Re: [HACKERS] SSL and USER_CERT_FILE

pgsql@mohawksoft.com writes:
> Maybe we need to go even further and add it to the PQconnect API
> sslkey=filename and sslcrt=filename in addition to sslmode?

If there's a case to be made for this at all, it should be handled the
same way as all other libpq connection parameters.

regards, tom lane

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

Re: [GENERAL] Password safe web application with postgre

You could try to have a function in your application that encrypts the connection string and store it in a session variable.  When you need it you decrypted from the session variables.  Session variables are stored as files on the server, therefore the risk is not as high.

Just a thought.

Fernando.

Bohdan Linda wrote:

Hello,  I have the following problem. A multiuser app has authentization and authorization done based on pgsql.  The frontend is web based so it is stateless; it is connecting to database on every get/post. There is also a requirement that the user is transparently logged in for some period of time.  Tha most easy way is to store login credentials into the session. The drawback is that session is stored in file, so the credentials are readable. I want to avoid it.   My first step was hashing the password with the same mechanizm as pgsql does, but I am not able to pass it to the server. I did some research with mighty google and found reply by Tom Lane:  "No, you need to put the plain text of the password into the connInfo. Knowing the md5 doesn't prove you know the password. "  Thus the next logical step is keeping sessions in servers memory rather than files. Memory dump could compromise it, but this is acceptable risk.  I would like to ask you, if someone had solved this problem is some more elegant way.  Thank you, Bohdan     

Re: [PERFORM] Update performance degrades over time

On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84
<SSubbiah@motorola.com> wrote:
> Hi All,
>
> We are doing some load tests with our application running postgres 8.2.4. At
> times we see updates on a table taking longer (around
> 11-16secs) than expected sub-second response time. The table in question is
> getting updated constantly through the load tests. In checking the table
> size including indexes, they seem to be bloated got it confirmed after
> recreating it (stats below). We have autovacuum enabled with default
> parameters. I thought autovaccum would avoid bloating issues but looks like
> its not aggressive enough. Wondering if table/index bloating is causing
> update slowness in over a period of time. Any ideas how to troubleshoot this
> further.

Sometimes it is necessary to not only VACUUM, but also REINDEX. If
your update changes an indexed column to a new, distinct value, you
can easily get index bloat.

Also, you should check to see if you have any old, open transactions
on the same instance. If you do, it's possible that VACUUM will have
no beneficial effect.

-jwb

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

Re: [HACKERS] [rfc,patch] PL/Proxy in core

"Marko Kreen" <markokr@gmail.com> writes:
> How about following patch? I have bison 2.3 and it seems not to do
> global allocation, so it should be fine. There may be early exit
> with elog(ERRROR) inside so I'd like to avoid malloc() itself.

None of our other parsers fool with bison's memory allocation;
why does yours need to?

regards, tom lane

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

[GENERAL] problem with serial data type and access

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFILECusbKx4ZV8clIRAs5cAJ0TQi3mYx2xkjSH9tJXH2OXETF4fwCfeHxr
CCI2m+0r2lf4KWtlRhiLVrM=
=1fIy
-----END PGP SIGNATURE-----
I know it's not fully IT with the list, but maybe somebody can help me.

I'm dealing with this scenario: access 97 is connected through odbc to a
postgresql server. All tables are saved in postgresql and access is used
only to generated the program interface.

Everything works fines, but I'm having problems with the serial data
type. I know a serial is an integer having as default the next value of
a sequence. Since it is an integer, access does not recognize it as an
autoincrement value, and it asks for is value.

Did anyone of you already have this problem?

Thanks...

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.

Re: [HACKERS] SSL and USER_CERT_FILE

>
> On May 15, 2008, at 6:31 AM, pgsql@mohawksoft.com wrote:
>
>>> Mark Woodward wrote:
>>>> I am using PostgreSQL's SSL support and the conventions for the
>>>> key and
>>>> certifications don't make sense from the client perspective.
>>>> Especially
>>>> under Windows.
>>>>
>>>> I am proposing a few simple changes:
>>>>
>>>> Adding two API
>>>> void PQsetSSLUserCertFileName(char *filename)
>>>> {
>>>> user_crt_filename = strdup(filename);
>>>> }
>>>> PQsetSSLUserKeyFileName(char *filename)
>>>> {
>>>> user_key_filename = strdup(filename);
>>>> }
>>>>
>>>>
>>>>
>>> [snip]
>>>> Any comments?
>>>>
>>>>
>>>
>>>
>>> I think it would probably be much better to allow for some
>>> environment
>>> variables to specify the locations of the client certificate and key
>>> (and the CA cert and CRL) - c.f. PGPASSFILE.
>>>
>>> That way not only could these be set by C programs but by any libpq
>>> user
>>> (I'm sure driver writers who use libpq don't want to have to bother
>>> with
>>> this stuff.) And we wouldn't need to change the API at all.
>>>
>>
>> The problem I have with environment variables is that they tend not
>> to be
>> application specific and almost always lead to configuration issues.
>> As a
>> methodology for default configuration, it adds flexibility. Also, the
>> current configuration does not easily take in to consideration the
>> idea
>> that different databases with different keys can be used from the same
>> system the same user.
>
> Environment variables don't have to be set in your shell.
>
> This would seem to give the same functionality you suggest above,
> given support for environment variables:
>
> void PQsetSSLUserCertFileName(char * filename)
> {
> setenv("PGCERTFILE", filename);
> }
>
> void PQsetSSLUserKeyFileName(char *filename)
> {
> setenv("PGKEYFILE", filename);
> }
>
> Or, in perl, $ENV{PGKEYFILE} = $file and so on. It seems
> less intrusive than adding new API calls.
>
> Cheers,
> Steve

Doesn't it make sense that the connection be configured in one place? I
agree with Tom, if it should be done, it should be done in PQconnectdb.

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

Re: [HACKERS] SSL and USER_CERT_FILE

Mark Woodward wrote:
> I am using PostgreSQL's SSL support and the conventions for the key and
> certifications don't make sense from the client perspective. Especially
> under Windows.
>
> I am proposing a few simple changes:
>
> Adding two API
> void PQsetSSLUserCertFileName(char *filename)
> {
> user_crt_filename = strdup(filename);
> }
> PQsetSSLUserKeyFileName(char *filename)
> {
> user_key_filename = strdup(filename);
> }
>
>
>
[snip]
> Any comments?
>
>

I think it would probably be much better to allow for some environment
variables to specify the locations of the client certificate and key
(and the CA cert and CRL) - c.f. PGPASSFILE.

That way not only could these be set by C programs but by any libpq user
(I'm sure driver writers who use libpq don't want to have to bother with
this stuff.) And we wouldn't need to change the API at all.

cheers

andrew

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

[pgsql-es-ayuda] Re: [pgsql-es-ayuda] Certificación en postgresql

>
>----- Original Message -----
>From: Jose Luis Stragnari
>To: Raul Andres Duque ; Lista - PostgreSQL
>Sent: Thursday, May 15, 2008 6:30 AM
>Subject: RE: [pgsql-es-ayuda] Certificación en postgresql
>
>
>Raúl, este link te puede ser de ayuda
>
>http://www.sraoss.co.jp/postgresql-ce/about_en.html
>

Desafortunadamente lo último que leo es que SOLO esta disponible en paises
asiaticos !!!

Atentamente,

RAUL DUQUE
Bogotá, Colombia

>Saludos
>
>José Luis Stragnari
>Córdoba, Argentina
>
>
>De: pgsql-es-ayuda-owner@postgresql.org
>[mailto:pgsql-es-ayuda-owner@postgresql.org] En nombre de Raul Andres Duque
>Enviado el: Jueves, 15 de Mayo de 2008 07:31 a.m.
>Para: Lista - PostgreSQL
>Asunto: [pgsql-es-ayuda] Certificación en postgresql
>
>Cordial Saludo.
>
>Tengo la duda si existe alguna institución que realiza exámenes de
>certificación en postgresql que tenga validez mundial?
>
>Qué niveles/tipos de certificación existen para postgresql?
>
>Atentamente,
>
>RAÚL DUQUE
>BOGOTA, COLOMBIA

--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [PERFORM] I/O on select count(*)

Matthew Wakeling wrote:
> On Thu, 15 May 2008, Luke Lonergan wrote:
>> ...HINT bit optimization, but avoids this whole ³write the data,
>> write it to the log also, then write it again just for good measure²
> ...
> The hint data will be four bits per tuple plus overheads, so it could be
> made very compact, and therefore likely to stay in the cache fairly
> well.

Does it seem like these HINT bits would be good candidates to move
off to map forks similar to how the visibility map stuff will be handled?

Since (if I understand right) only the hint bits change during the
select(*) it seems a lot less write-IO would happen if such a map
were updated rather than the data pages themselves.

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

Re: [HACKERS] SSL and USER_CERT_FILE

> pgsql@mohawksoft.com writes:
>> Maybe we need to go even further and add it to the PQconnect API
>> sslkey=filename and sslcrt=filename in addition to sslmode?
>
> If there's a case to be made for this at all, it should be handled the
> same way as all other libpq connection parameters.
>
> regards, tom lane
>

Here's the use case:

I have an application that must connect to multiple PostgreSQL databases
and must use secure communications and the SSL keys are under the control
of the business units the administer the databases, not me. In addition my
application also communicates with other SSL enabled versions of itself.

I think you would agree that a hard coded immutable location for "client"
interface is problematic.

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

Re: [NOVICE] Transaction inside of functions

am Thu, dem 15.05.2008, um 15:32:57 +0200 mailte A B folgendes:
> Is it not possible to define transactions inside of a function?

No.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA

http://wwwkeys.de.pgp.net

--
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] I/O on select count(*)

On Thu, 15 May 2008, Heikki Linnakangas wrote:
> > Is it really safe to update the hint bits in place? If there is a
> > power cut in the middle of writing a block, is there a guarantee from
> > the disc that the block will never be garbled?
>
> Don't know, to be honest. We've never seen any reports of corrupted data
> that would suggest such a problem, but it doesn't seem impossible to me
> that some exotic storage system might do that.

Hmm. That problem is what WAL full-page-writes is meant to handle, isn't
it? So basically, if you're telling people that WAL full-page-writes is
safer than partial WAL, because it avoids updating pages in-place, then
you shouldn't be updating pages in-place for the hint bits either. You
can't win!

>> In fact, if the tuple's creating transaction has aborted, then the tuple
>> can be vacuumed right there and then before it is even written.
>
> Not if you have any indexes on the table. To vacuum, you'll have to scan all
> indexes to remove pointers to the tuple.

Ah. Well, would that be so expensive? After all, someone has to do it
eventually, and these are index entries that have only just been added
anyway.

I can understand index updating being a bit messy in the middle of a
checkpoint though, as you would have to write the update to the WAL, which
you are checkpointing...

So, I don't know exactly how the WAL updates to indexes work, but my guess
is that it has been implemented as "write the blocks that we would change
to the WAL". The problem with this is that all the changes to the index
are done individually, so there's no easy way to "undo" one of them later
on when you find out that the transaction has been aborted during the
checkpoint.

An alternative would be to build a "list of changes" in the WAL without
actually changing the underlying index at all. When reading the index, you
would read the "list" first (which would be in memory, and in an
efficient-to-search structure), then read the original index and add the
two. Then when checkpointing, vet all the changes against known aborted
transactions before making all the changes to the index together. This is
likely to speed up index writes quite a bit, and also allow you to
effectively vacuum aborted tuples before they get written to the disc.

Matthew

--
Vacuums are nothings. We only mention them to let them know we know
they're there.

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

[pgadmin-hackers] multi-select for privileges

Hi,

This is just a wish-list item if anybody is interested in implementing
it. I think it would make pgadmin pretty powerful in terms of
managing privileges.

When you click "Views", "Tables", etc. in the tree at the left in the
main view, the Properties tab at the right shows a grid of objects and
their comments. This grid has multi-select enabled, which makes it
handy to drop multiple objects at once, but if you right-click and
choose Properties, you can only modify one object at a time. It would
be very handy to be able to select many objects and define who has
what access to them, especially in the case when you're creating a new
login group and want to give the group access to a large set of
tables.

What do you think?

Kev

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

[NOVICE] Transaction inside of functions

Is it not possible to define transactions inside of a function?
I guess you can do

BEGIN WORK;
select my_function()
COMMIT;

instead of defining the function
CREATE FUNCTION my_function()...
BEGIN
BEGIN WORK;
....
COMMIT;
END;

but what if you would like to do

CREATE FUNCTION my_function()...
BEGIN
.... do stuff.....
BEGIN WORK;
....
COMMIT;
do more stuff ....
END;

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

Re: [HACKERS] SSL and USER_CERT_FILE

> Mark Woodward wrote:
>> I am using PostgreSQL's SSL support and the conventions for the key and
>> certifications don't make sense from the client perspective. Especially
>> under Windows.
>>
>> I am proposing a few simple changes:
>>
>> Adding two API
>> void PQsetSSLUserCertFileName(char *filename)
>> {
>> user_crt_filename = strdup(filename);
>> }
>> PQsetSSLUserKeyFileName(char *filename)
>> {
>> user_key_filename = strdup(filename);
>> }
>>
>>
>>
> [snip]
>> Any comments?
>>
>>
>
>
> I think it would probably be much better to allow for some environment
> variables to specify the locations of the client certificate and key
> (and the CA cert and CRL) - c.f. PGPASSFILE.
>
> That way not only could these be set by C programs but by any libpq user
> (I'm sure driver writers who use libpq don't want to have to bother with
> this stuff.) And we wouldn't need to change the API at all.
>

The problem I have with environment variables is that they tend not to be
application specific and almost always lead to configuration issues. As a
methodology for default configuration, it adds flexibility. Also, the
current configuration does not easily take in to consideration the idea
that different databases with different keys can be used from the same
system the same user.

Maybe we need to go even further and add it to the PQconnect API
sslkey=filename and sslcrt=filename in addition to sslmode?

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

Re: [GENERAL] Howto return values from a function

am Thu, dem 15.05.2008, um 14:46:02 +0200 mailte A B folgendes:
> > What exactly about the documentation isn't clear?
> I would have liked a few more examples... but that is perhaps just me.

http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm

>
> > Like the documentation says: SETOF sometype.
> Ah, so I just create my own type with "CREATE TYPE ..." and use that
> type in the function.

One solution, there are much more.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA

http://wwwkeys.de.pgp.net

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

[GENERAL] Tsearch2 Upgrade from 8.2 to 8.3.1

Hello!

I want to upgrade from 8.2 to 8.3.1 but I've problems:
I did a pg_dumpall but this doesn't work. I found the migration guide with
a trick to load the new contrib/tsearch2 module. But how is this done
exactly?
-------------------------------------------------------------------------
http://www.postgresql.org/docs/8.3/interactive/textsearch-migration.html
The old contrib/tsearch2 functions and other objects must be suppressed
when loading pg_dump output from a pre-8.3 database. While many of them
won't load anyway, a few will and then cause problems. One simple way to
deal with this is to load the new contrib/tsearch2 module before restoring
the dump; then it will block the old objects from being loaded.
-------------------------------------------------------------------------

Some other link I've found:
http://sql-info.de/postgresql/notes/converting-tsearch2-to-8.3.html
http://www.sai.msu.su/~megera/wiki/Tsearch2_83_changes
http://archives.postgresql.org/pgsql-hackers/2007-10/msg00509.php

http://translate.google.com/translate?u=http%3A%2F%2Fwww.nabble.com%2FAtualiza%25C3%25A7%25C3%25A3o-de-Mediawiki-para-8.3-td15722654.html&hl=en&ie=UTF8&sl=es&tl=en
http://www.postgresql.org/docs/8.3/interactive/textsearch-migration.html
http://www.postgresql.org/docs/current/static/tsearch2.html#AEN102824
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
http://people.planetpostgresql.org/greg/index.php?/archives/123-MediaWiki-is-Postgres-8.3-compatible.html

Thank you.

Ciao,
Gerhard

--
http://www.wiesinger.com/

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

Re: [PERFORM] I/O on select count(*)

On Thursday 15 May 2008 03:02:19 Tom Lane wrote:
> "Jan de Visser" <jdevisser@digitalfairway.com> writes:
> > Obviously, this issue is tied to the slow count(*) one, as I found out
> > the hard way. Consider the following scenario:
> > * Insert row
> > * Update that row a couple of times
> > * Rinse and repeat many times
> >
> > Now somewhere during that cycle, do a select count(*) just to see
> > where you are. You will be appalled by how slow that is, due to not
> > only the usual 'slow count(*)' reasons. This whole hint bit business
> > makes it even worse, as demonstrated by the fact that running a vacuum
> > before the count(*) makes the latter noticably faster.
>
> Uh, well, you can't blame that entirely on hint-bit updates. The vacuum
> has simply *removed* two-thirds of the rows in the system, resulting in
> a large drop in the number of rows that the select even has to look at.
>
> It's certainly true that hint-bit updates cost something, but
> quantifying how much isn't easy. The off-the-cuff answer is to do the
> select count(*) twice and see how much cheaper the second one is. But
> there are two big holes in that answer: the first is the possible cache
> effects from having already read in the pages, and the second is that
> the follow-up scan gets to avoid the visits to pg_clog that the first
> scan had to make (which after all is the point of the hint bits).
>
> I don't know any easy way to disambiguate the three effects that are at
> work here. But blaming it all on the costs of writing out hint-bit
> updates is wrong.
>
> regards, tom lane

True. But it still contributes to the fact that queries sometimes behave in a
non-deterministic way, which IMHO is the major annoyance when starting to
work with pgsql. And contrary to other causes (vacuum, checkpoints) this is
woefully underdocumented.

jan

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

Re: [pgsql-es-ayuda] charla sobre postgres

Colegas:

Propongo nos relajemos (acá en el Caribe eso no es una palabra mala, lo
digo para evitar otra confusión ;-)) ... tomemos las cosas con calma y
podemos hacer cursos zonales ... alguien de determinado zona de nuestra
América toda ... participa y replica y así ... podemos terminar tomando
baños de playa y aprendiendo Postgres ... por mi Isla o archipiélago
como os guste.

Gilberto.
El mié, 14-05-2008 a las 22:06 +0200, Oswaldo Hernández escribió:
> Jenaro Centeno Gomez escribió:
> > No sé porque pero con este comentario recordé a Bill Gates.
> >
> > Personalmente llegué a PostgreSQL por le necesidad de tener una base de
> > datos segura que respaldara mis proyectos, los cuales son comerciales,
> > yo no vendo el software, yo vendo los servicios de implementación,
> > capacitación y soporte. En mi caso particular siempre que alguien se
> > acerca con una duda sobre PostgreSQL trato de ayudarlo si puedo, si no,
> > pues mejor le digo a donde dirigirse para que encuentre la información.
> > Así que mi involucramiento con el proyecto es netamente una retribución
> > a todo lo que recibo de este, sin embargo, siento que si en cierto
> > momento comenzará a pensar en cobrar por la ayuda que le doy a la gente
> > sobre PostgreSQL, eso haría que me sintiera un tanto despreciable,
> > estaría haciendo negocios por ambos lados y no creo que eso sea
> > correcto, no con este tipo de proyectos. No estoy en contra de que haya
> > una cuota de recuperación si te trata de recuperar los recursos que se
> > involucren, materiales, instalaciones, pero de eso a "pagar" por la
> > capacitación en este caso no creo que sea correcto. Sobre todo porque lo
> > que yo detecto en esto de los cursos que estaban ofreciendo los
> > compañeros se notaba que eran de manera voluntaria y sin afán de lucro
> > que es lo que Olga propone. Llevo mucho tiempo involucrado en proyectos
> > relacionados con bases de datos, en todos existen grupos de usuarios que
> > se encargan de la difusión y desarrollo de eventos para sus proyectos,
> > he asistido incluso a reuniones de grupos de usuarios de la empresa de
> > las ventanitas que vende la base de datos de las baterías gigantes y
> > nunca me ha tocado pagar un cinco, y no porque no quiera o no pueda sino
> > porque simplemente ese es el espíritu de compartir, y en verdad que he
> > visto eventos muy bien realizados. Por cierto nunca he tenido la
> > oportunidad de asistir a algún evento de PostgreSQL :S...
> >
> > Al final, se trata de mantener el conocimiento libre, y con ese
> > comentario que se hizo se puede echar al traste la intención inicial de
> > los compañeros.
> >
>
> Disculpa pero no estoy de acuerdo contigo.
>
> Comentas que tratas de ayudar desinteresadamente para resolver dudas de postgres, lo que me parece
> fantástico, y creo que es algo que todos los que estamos en esta lista hacemos en la medida de
> nuestros conocimientos y tiempo disponible. Pero imagino que para tí, como para mi, lo primero es el
> trabajo que es no que nos da de comer y cuando este nos deja tiempo atendemos al resto. No creo
> dejes de hacer un servicio a un cliente por responder a una duda de postgres.
>
> La organización de unos cursos requiere tiempo y dedicación para la preparación de materiales,
> documentación, temario, etc.. ademas del compromiso de horario. Creo que a los asistentes no les
> daria mucho gusto que el profesor los dejara colgados porque le ha salido un trabajito que si va a
> cobrar.
>
> Todo esto no tiene nada que ver con el espíritu de compartir, sino que se le esta pidiendo a unas
> personas que den servicio a sus clientes, que en este caso somos nosotros.
>
>

--
TIP 3: Si encontraste la respuesta a tu problema, publ�cala, otros te lo agradecer�n

[ADMIN] Trouble with recovering disc space

Dear list,

I have the following issue with recovering disc space...

Interscan Web Security Suite(Trend Micro gateway antivirus appl.) uses
Postgres database to store access logs(to the internet) in table tb_url_usage.

This table occupies approximately 12GB based on oid:

-rw------- 1 iscan iscan 1073741824 May 15 14:37 48495427
-rw------- 1 iscan iscan 1073741824 May 15 02:00 48495427.1
-rw------- 1 iscan iscan 1073741824 May 15 11:25 48495427.10
-rw------- 1 iscan iscan 1073741824 May 14 02:01 48495427.2
-rw------- 1 iscan iscan 1073741824 May 14 02:01 48495427.3
-rw------- 1 iscan iscan 1073741824 May 14 13:34 48495427.4
-rw------- 1 iscan iscan 1073741824 May 14 13:34 48495427.5
-rw------- 1 iscan iscan 1073741824 May 15 14:37 48495427.6
-rw------- 1 iscan iscan 1073741824 May 15 13:19 48495427.7
-rw------- 1 iscan iscan 1073741824 May 15 11:58 48495427.8
-rw------- 1 iscan iscan 1073741824 May 15 11:30 48495427.9
-rw------- 1 iscan iscan 399007744 May 15 09:07 48495427.11

Now, if I run a DELETE(some rows) on this table after that VACUUM FULL it
does not return any space to the operating system. These files remains. If I
am right, VACUUM FULL should decrease the physical size of the table, am I?
May I delete som of these files? Or how can I free up space on filesystem ?

I am running Postgresql version 8.0 on Fedora Core 4. If you need some
additional information I send you...

Please any advise could help!

Kind regards,

chris

--- reklama -----------------------------------------------------
Vieš, čo Ťa dnes čaká? Pozri si horoskop!
http://horoskop.zoznam.sk

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

Re: [PERFORM] I/O on select count(*)

On Thu, 15 May 2008, Luke Lonergan wrote:
> BTW ­ we¹ve removed HINT bit checking in Greenplum DB and improved the
> visibility caching which was enough to provide performance at the same level
> as with the HINT bit optimization, but avoids this whole ³write the data,
> write it to the log also, then write it again just for good measure²
> behavior.

This sounds like a good option. I believe I suggested this a few months
ago, however it was rejected because in the worst case (when the hints are
not cached), if you're doing an index scan, you can do twice the number of
seeks as before.

http://archives.postgresql.org/pgsql-performance/2007-12/msg00217.php

The hint data will be four bits per tuple plus overheads, so it could be
made very compact, and therefore likely to stay in the cache fairly well.
Each tuple fetched would have to be spaced really far apart in the
database table in order to exhibit the worst case, because fetching a page
of hint cache will cause 64kB or so of disc to appear in the disc's
read-ahead buffer, which will be equivalent to 128MB worth of database
table (assuming eight tuples per block and no overhead). As soon as you
access another tuple in the same 128MB bracket, you'll hit the disc
read-ahead buffer for the hints.

On balance, to me it still seems like a good option.

Matthew

--
Those who do not understand Unix are condemned to reinvent it, poorly.
-- Henry Spencer
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] I/O on select count(*)

Matthew Wakeling wrote:
> Is it really safe to update the hint bits in place? If there is a power
> cut in the middle of writing a block, is there a guarantee from the disc
> that the block will never be garbled?

Don't know, to be honest. We've never seen any reports of corrupted data
that would suggest such a problem, but it doesn't seem impossible to me
that some exotic storage system might do that.

> Is there a way to make a shortcut and have the hint bits written the
> first time the data is written to the table? One piece of obvious
> low-hanging fruit would be to enhance step five above, so that the
> bgwriter or checkpoint that writes the data to the database table checks
> the pg_clog and writes the correct hint bits.

Yep, that's an idea that's been suggested before. In fact, I seem to
remember a patch to do just that. Don't remember what happened to it,

> In fact, if the tuple's
> creating transaction has aborted, then the tuple can be vacuumed right
> there and then before it is even written.

Not if you have any indexes on the table. To vacuum, you'll have to scan
all indexes to remove pointers to the tuple.

> However, this idea does not deal well with bulk data loads, where the
> data is checkpointed before transaction is committed or aborted.

Yep, that's the killer :-(.

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

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

[HACKERS] SSL and USER_CERT_FILE

I am using PostgreSQL's SSL support and the conventions for the key and
certifications don't make sense from the client perspective. Especially
under Windows.

I am proposing a few simple changes:

Adding two API
void PQsetSSLUserCertFileName(char *filename)
{
user_crt_filename = strdup(filename);
}
PQsetSSLUserKeyFileName(char *filename)
{
user_key_filename = strdup(filename);
}

Adding two static vars in fe-secure.c

char *user_key_filename=NULL;
char *user_crt_filename=NULL;

In client_cert_cb(...)

Add:
if(user_crt_filename)
strncpy(fnbuf, sizeof(fnbuf), user_crt_filename);
else
snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);

and:

if(user_key_filename)
strncpy(fnbuf, sizeof(fnbuf), user_key_filename);
else
snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_KEY_FILE);

The purpose of these changes is to make it easier to configure SSL in an
application which uses libpq.

Any comments?

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

Re: [GENERAL] Howto return values from a function

> What exactly about the documentation isn't clear?
I would have liked a few more examples... but that is perhaps just me.

> Like the documentation says: SETOF sometype.
Ah, so I just create my own type with "CREATE TYPE ..." and use that
type in the function.

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

Re: [PATCHES] Patch to change psql default banner v6

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Ah, OK. I had forgotten. Here is the new output:
>
> > $ sql test
> > psql (8.4devel) Type "help" for help.
>
> > test=> help
>
> You are being unreasonably cryptic here. What happens when there
> is optional output --- ie, version mismatch warning and/or SSL info?

Oh, good point. Let me look at that. Thanks. You prefer:

$ sql test
psql (8.4devel)
Type "help" for help.

test=> help

That looked so sparse to me.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Re: [PERFORM] I/O on select count(*)

On Wed, 14 May 2008, Alvaro Herrera wrote:
> Hint bits are used to mark tuples as created and/or deleted by
> transactions that are know committed or aborted. To determine the
> visibility of a tuple without such bits set, you need to consult pg_clog
> and possibly pg_subtrans, so it is an expensive check.

So, as I understand it, Postgres works like this:

1. You begin a transaction. Postgres writes an entry into pg_clog.
2. You write some tuples. Postgres writes them to the WAL, but doesn't
bother fsyncing.
3. At some point, the bgwriter or a checkpoint may write the tuples to the
database tables, and fsync the lot.
4. You commit the transaction. Postgres alters pg_clog again, writes that
to the WAL, and fsyncs the WAL.
5. If the tuples hadn't already made it to the database tables, then a
checkpoint or bgwriter will do it later on, and fsync the lot.
6. You read the tuples. Postgres reads them from the database table, looks
in pg_clog, notices that the transaction has been committed, and
writes the tuples to the database table again with the hint bits set.
This write is not WAL protected, and is not fsynced.

This seems like a good architecture, with some cool characteristics,
mainly that at no point does Postgres have to hold vast quantities of data
in memory. I have two questions though:

Is it really safe to update the hint bits in place? If there is a power
cut in the middle of writing a block, is there a guarantee from the disc
that the block will never be garbled?

Is there a way to make a shortcut and have the hint bits written the first
time the data is written to the table? One piece of obvious low-hanging
fruit would be to enhance step five above, so that the bgwriter or
checkpoint that writes the data to the database table checks the pg_clog
and writes the correct hint bits. In fact, if the tuple's creating
transaction has aborted, then the tuple can be vacuumed right there and
then before it is even written. For OLTP, almost all the hint bits will be
written first time, and also the set of transactions that will be looked
up in the pg_clog will be small (the set of transactions that were active
since the last checkpoint), so its cache coherency will be good.

However, this idea does not deal well with bulk data loads, where the data
is checkpointed before transaction is committed or aborted.

Matthew

--
Now, you would have thought these coefficients would be integers, given that
we're working out integer results. Using a fraction would seem really
stupid. Well, I'm quite willing to be stupid here - in fact, I'm going to
use complex numbers. -- Computer Science Lecturer

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

Re: [GENERAL] Howto return values from a function

On Thu, May 15, 2008 at 02:25:36PM +0200, A B wrote:
> I'm still trying to learn to write plpgsql functions, but I find the
> docs a little short on examples on how to return stuff from a
> function. I'm very grateful for any help on this.

What exactly about the documentation isn't clear?

http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

> CREATE FUNCTION foo() RETURNS ???? AS

> but what do I write instead of ????

Like the documentation says: SETOF sometype.

> I'm nto sure here, but It seems to mee that there are two other ways
> of doing case 2.

How you generate the results is up to you. when you have them you
either use RETURN NEXT or RETURN QUERY to return them to the caller.

> 3) In the third case, I want to create the values I return by joining
> many values. Something like this
> CREATE FUNCTION foo() RETURNS ???? AS

You can always use out parameters if you feel better about it:

CREATE FUNCTION foo(col1 int4 OUT, col2 text OUT, ...) AS ...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [GENERAL] problem with importing dbf file into postgresql using navicat on mac OS X

On May 12, 2008, at 1:28 PM, Mathias Ghys wrote:

> Error Message: ERROR: invalid byte sequence for encoding "UTF8":
> 0xe96f70
> HINT: This error can also happen if the byte sequence does not
> match the encoding expected by the server, which is controlled by
> "client_encoding".
>
> My postgresql database is UTF-8 encoded. With importing I get the
> following data:
>
> http://server58.dedicatedusa.com/~mathias/mathias/datatypes.tiff
>
> do you guys have any idea what i've been doing wrong and how I could
> get the *.dbf files into my postgresql database?

I think the problem is that Navicat sets your client connection to
UTF-8 but the data you are importing from the file is not UTF-8.
Navicat should have a way for you to change your client encoding to
match what is in the file. The main problem is you need to know
exactly what encoding is being used in the file.

If the dbf file is really broken (invalid sequences no mater how it is
encoded) then you'll need to fix that first.

John DeSoi, Ph.D.

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

[GENERAL] Howto return values from a function

I'm still trying to learn to write plpgsql functions, but I find the
docs a little short on examples on how to return stuff from a
function. I'm very grateful for any help on this.

There are some basic cases I've identified.

1) when I want to return all records found by a query , like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
RETURN QUERY SELECT a,b,c,d,... FROM T1,T2,... WHERE ....;
END;

but what do I write instead of ????

2) when I select stuff, iterate over the result before returning it
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
FOR result IN SELECT .....
LOOP
do something with result...
RETURN NEXT result
END LOOP;
END

I'm nto sure here, but It seems to mee that there are two other ways
of doing case 2.
2a) run the query once more and RETURN QUERY at the end instead of the
RETURN NEXT statement.
2b) store the result in some temporary storage... (I'm not sure how,
it's just a feeling I get that this should be possible, I might be
completely wrong) and then return the whole result a once.
As usual, what do I write instead of ????

3) In the third case, I want to create the values I return by joining
many values. Something like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
myvar := .....
myvar2 := ....
FOR result IN SELECT ...
LOOP
FOR result2 IN SELECT .....
LOOP
RETURN NEXT ????????????;
END LOOP;
END LOOP;
RETURN
END

The ?????????????? part should perhaps be something like rows with the values
[ myvar, myvar2, result.f1, result.f2, result2.f5, result2.f7 ]

--
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] which ext3 fs type should I use for postgresql

On Thu, 15 May 2008, david@lang.hm wrote:
> IIRC postgres likes to do 1M/file, which isn't very largeas far as the -T
> setting goes.

ITYF it's actually 1GB/file.

> think twice about this. ext2/3 get slow when they fill up (they have
> fragmentation problems when free space gets too small), this 5% that only
> root can use also serves as a buffer against that as well.

It makes sense to me that the usage pattern of Postgres would be much less
susceptible to causing fragmentation than normal filesystem usage. Has
anyone actually tested this and found out?

Matthew

--
Isn't "Microsoft Works" something of a contradiction?

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

Re: [PERFORM] which ext3 fs type should I use for postgresql

On Thu, 15 May 2008, Matthew Wakeling wrote:

> On Thu, 15 May 2008, Philippe Amelant wrote:
>> using mkfs.ext3 I can use "-T" to tune the filesytem
>>
>> mkfs.ext3 -T fs_type ...
>>
>> fs_type are in /etc/mke2fs.conf (on debian)
>
> If you look at that file, you'd see that tuning really doesn't change that
> much. In fact, the only thing it does change (if you avoid "small" and
> "floppy") is the number of inodes available in the filesystem. Since Postgres
> tends to produce few large files, you don't need that many inodes, so the
> "largefile" option may be best. However, note that the number of inodes is a
> hard limit of the filesystem - if you try to create more files on the
> filesystem than there are available inodes, then you will get an out of space
> error even if the filesystem has space left.
> The only real benefit of having not many inodes is that you waste a little
> less space, so many admins are pretty generous with this setting.

IIRC postgres likes to do 1M/file, which isn't very largeas far as the -T
setting goes.

> Probably of more use are some of the other settings:
>
> -m reserved-blocks-percentage - this reserves a portion of the filesystem
> that only root can write to. If root has no need for it, you can kill
> this by setting it to zero. The default is for 5% of the disc to be
> wasted.

think twice about this. ext2/3 get slow when they fill up (they have
fragmentation problems when free space gets too small), this 5% that
only root can use also serves as a buffer against that as well.

> -j turns the filesystem into ext3 instead of ext2 - many people say that
> for Postgres you shouldn't do this, as ext2 is faster.

for the partition with the WAL on it you may as well do ext2 (the WAL is
written synchronously and sequentially so the journal doesn't help you),
but for the data partition you may benifit from the journal.

David Lang

--
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] Need for help!

On Thu, May 15, 2008 at 3:48 PM, Semi Noob <seminoob@gmail.com> wrote:
>
>
> I set max_connections is 200.

What error message you get when you try with more than 64 clients ?

> 57 seems a small number, according to you, how much tps is normal or fast?

Its difficult to say how much is good. On my laptop for s = 10, c =
40, t = 1000, I get 51 tps. But on a larger 2 CPU, 2 GB, 3 RAID 0
disks for data and a separate disk for xlog, I get 232 tps.

> and what is the different of "shared_buffers" and "effective_cache_size".
>

"shared_buffers" is the size of the buffer pool which Postgres uses to
cache the data blocks.
"effective_cache_size" is usually size of the shared buffer plus
estimate of whatever data OS can cache. Planner uses this
approximation to choose right plan for execution.

http://www.postgresql.org/docs/8.3/interactive/runtime-config-query.html

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

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

Re: [JDBC] Testing JDBC Prepared Statements

Kris Jurka schrieb:

On Wed, 14 May 2008, Daniel Migowski wrote:

Is it possible with the PostgreSQL JDBC driver (leaving the JDBC API space is allowed) to serverside prepare a statement without executing it?

PreparedStatement.getParameterMetaData will prepare a statement without executing it.

Thank you. I also already helped me with something like statement.executeQuery("PREPARY test AS "+myQueryToTest), but your variant does apropriate query parsing, which seem better.

With best regards,
Daniel Migowski

--

 |¯¯|¯¯|    IKOffice GmbH             Daniel Migowski  |  |  |/|                            Mail: dmigowski@ikoffice.de  |  | // |  Nordstr. 10               Tel.: 0441 21 98 89 52  |  | \\ |  26135 Oldenburg           Fax.: 0441 21 98 89 55  |__|__|\|  http://www.ikoffice.de    Mob.: 0176 22 31 20 76

Re: [pgsql-es-ayuda] Consulta SQL

2008/5/15 Miguel Rodríguez Penabad <penabad@gmail.com>:
> El día 15 de mayo de 2008 11:58, Antonio Antonio
> <a2076totonio@yahoo.es> escribió:
>
> Por ello te recomendaría que definieses EXACTAMENTE lo que quieres
> recuperar, y así podríamos ayudarte mejor a solucionar el problema en
> Postgres (en SQL estándar).
>

y con resultados razonables

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

[pgsql-www] hub.org Nagios monitoring

Hi Marc,

webmaster@postgresql.org is getting spammed with irrelevant nagios
alerts from smc.hub.org. Can you fix that please?

Thanks.

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

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

On Tue, May 13, 2008 at 03:30:09PM +0530, Elizabeth George wrote:
> SET CLIENT_ENCODING TO 'UNICODE';
> copy (select 1 as F1) to E'c:\\test.out' csv QUOTE AS E'\xFE' FORCE
> QUOTE F1;
>
> I got error like
>
> ERROR: invalid byte sequence for encoding "UTF8": 0xfe
> HINT: This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".

Right, because 0xfe is not a valid character in UTF-8. Pick something
else.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [GENERAL] Need for help!

Thank you for your answer!
"You did not give CPU and disk info. But still 57 seems a small number.
What I guess is you're running pgbench with scale factor 1 (since you
haven't mentioned scale factor) and that causes extreme contention for
smaller tables with large number of clients."

My CPU is 2CPU: Intel(R) Xeon(TM) CPU 3.20GHz. Disk: disk system is RAID-5; OS CentOS. the number of scale in pgbench initialization is 100. It will be generate 10 000 000 rows in the accounts table. Fill factor is default.
In the other way, I heard that: PostgreSQL working with RAID-10 better than RAID-5 is it right?

       "Regarding maximum number of clients, check your "max_connections" setting."

I set max_connections is 200.

57 seems a small number, according to you, how much tps is normal or fast? and what is the different of "shared_buffers" and "effective_cache_size".

Thank you once more!
Regards,
Semi Noob

2008/5/15 Pavan Deolasee <pavan.deolasee@gmail.com>:

On Tue, May 13, 2008 at 2:43 PM, Semi Noob <seminoob@gmail.com> wrote:
>  But after upgrade the max clients is
> also 64 (?!?) Is this the maximum clients support by program pgbench (my
> server on Linux ver8.2.5, pgbench on Windows - version postgresql is 8.3.1)?
> And the number 57 tps is fast?
>

You did not give CPU and disk info. But still 57 seems a small number.
What I guess is you're running pgbench with scale factor 1 (since you
haven't mentioned scale factor) and that causes extreme contention for
smaller tables with large number of clients.

Regarding maximum number of clients, check your "max_connections" setting.

> Another questions, i heard that PostgreSQL does not support HT Technology,
> is it right?
>

I'm not sure what do you mean by HT, but if it's hyper threading, then
IMO that statement is not completely true. Postgres is not
multi-threaded, so a single process (or connection) may not be able to
use all the CPUs, but as long as there are multiple connections (each
connection corresponds to one backend process), as many CPUs will be
used.

> Last question, i don't understand so much the shmmax, shared_buffers, after
> upgrading my server from 4 GB RAM to 8 GB RAM, first i configure shmmax to
> 2GB, share_buffers to 1GB and start server, it runs, after that i set shmmax
> to 4GB and restart, it fails (?!?). The error logs said that not enough
> share memory!  and final i set shmmax to 3GB and share buffer to 2GB, it
> runs. Don't know why, can you explain?

That doesn't make sense. I am guessing that you are running a 32 bit
OS. 4GB of shmmax won't work on a 32 bit OS.

Thanks,
Pavan

Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com