[pgsql-jobs] Immediate Postgres Opportunity

Hello all

This opening is located in Palm Desert, CA. just outside of Palm Springs. I am looking for someone to help evaluate queries and fine tune performance on PostgreSQL 8.2 running on Windows. If you are qualified, available, interested, planning to make a change, or know of a friend who might have the required qualifications and interest, please call me ASAP at (909) 809-8662. Remote is a consideration.

 

 

Allen Kreger

Director, Technical Operations

Sparebackup.com

72-575 Fred Waring Drive

Palm Desert, CA 92260

o 760-779-0251 x328

c 909-809-8662

f 760-779-0291

 

The information contained in this message is confidential and may contain privileged work product or attorney-client information that is intended only for the use of the individual or entity to which it is addressed.  If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited.  If you have received this message in error, please notify the above.  Thank you.

 

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

Here's how you do it on restore step from a pg_dump in -Fc format.

pg_restore -l dumpfile > list
edit the file "list" to remove references to slony objects
pg_restore -L list <other options you want> dumpfile

--
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] Making sure \timing is on

Tom Lane escribió:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > David Fetter escribi�:
> >> Thanks for the heads-up :)
> >>
> >> Second patch attached, this time with some docs.
>
> > Added to July commitfest.
>
> Surely this is merely proof of concept and not a complete patch.

David, ya heard da man :-)

--
Alvaro Herrera

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

--
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] Fwd: [GLO] OT: como calcular CRC?

---------- Forwarded message ----------
From: Miguel Beltran R. <yourpadre@gmail.com>
Date: 2008/5/12
Subject: Re: [GLO] OT: como calcular CRC?
To: glo@glo.org.mx

Lo que quiero hacer es que la aplicacion cliente:

* Grabe los datos de manera local y tenga un campo (que dije CRC, pero
mas bien seria un checksum entonces?) en el que sepa si modificaron
alguno de los otros datos.

* En realizadad la aplicación debe de mandar los datos por internet en
tiempo real, se conectan por http al servidor y envian los datos a un
formulario, en el servidor checo que esten correctos (eso es otro
rollo). Aqui tengo duda, entonces es seguro que cuando la aplicación
mande lo datos no me va a cambiar ningun dato.

* Los datos se guardados localmente son por si no se puede por
internet en tiempo "real", se mande la información cuando este en
linea de nuevo.

--
________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [GENERAL] Substring Problem

ludwig@kni-online.de wrote:
> Type casting is required since 8.3, try
>
> SELECT substring(date :: varchar from 1 for 7) AS stryearmonth,
^^^^^^

sorry but this hurts and should not recommended. I think depesz
approach with to_string() and the correct format string is the
better solution. (think of datetyle oddities) - one of the reasons
I believe most of the implicit casts have gone ayway.

> COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP
> BY stryearmonth ORDER BY stryearmonth ASC

btw, whats the reason for the subselect?

T.

Re: [HACKERS] psql wrapped format default for backslash-d commands

Bruce Momjian wrote:

> I think we can wrap if there is whitespace within a few characters
> before the break point, and use a dash if we have to break a word. Is
> that what people want?

Ugh.

--
Alvaro Herrera

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

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

Re: [GENERAL] PG -v- MySQL

Gauthier, Dave wrote:
> Hi:

> I've been invited to participate in a forum which discusses technical
> issues for relational DBs used in the corporation. The 3 DBs they are
> discussing are Oracle, SQL-Server and MySQL. I'd like to introduce PG,
> but want to be able to address the "Why not use MySQL" questions when
> they arise. I know in the past there have been many comparisons between
> the 2 DBs but would like to know if there are any good, recent ones.
> Also, any "off-the-cuff" comments would be welcome, especially if you
> have used both DBs.

I suggest you take a look at:

http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project
http://sql-info.de/mysql/gotchas.html
http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-postgresql
http://www.commandprompt.com/files/mysql_learn.pdf
http://people.planetpostgresql.org/mha/index.php?/archives/168-guid.html
http://archives.postgresql.org/pgsql-hackers/2004-04/msg00992.php

Sincerely,

Joshua D. Drake

P.S. In short, we rock.

--
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] Making sure \timing is on

Alvaro Herrera <alvherre@commandprompt.com> writes:
> David Fetter escribió:
>> Thanks for the heads-up :)
>>
>> Second patch attached, this time with some docs.

> Added to July commitfest.

Surely this is merely proof of concept and not a complete patch.

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: [PERFORM] Installation Steps to migrate to Postgres 8.3.1

On Tue, May 13, 2008 at 6:00 AM, Knight, Doug <dknight@wsi.com> wrote:
> Hi,
> Along these lines, the usual upgrade path is a pg_dump/pg_restore set.
> However, what if your database is large (> 50GB), and you have to
> minimize your downtime (say less than an hour or two). Any suggestions
> on how to handle that kind of situation? It sure would be nice to have
> some kind of tool to update in-place a database, though I know that's
> not a likely path.

look up Slony

--
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] Arbitary file size limit in twophase.c

Gavin Sherry <swm@alcove.com.au> writes:
> There's an apparently arbitary limit of 10,000,000 bytes in twophase.c
> on the size of a two phase commit file. I can't see why this limit
> exists.

The comment seems perfectly clear about why the limit exists:

* Check file length. We can determine a lower bound pretty easily. We
* set an upper bound mainly to avoid palloc() failure on a corrupt file.

although certainly the specific value has been picked out of the air.

Perhaps it'd be better to use malloc() than palloc(), so that we'd not
lose control on out-of-memory, and then deem the file "too big" only
if we couldn't malloc the space.

Or we could try to fix things so that the file doesn't have to be all in
memory, but that seems pretty invasive.

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] Making sure \timing is on

David Fetter escribió:

> Thanks for the heads-up :)
>
> Second patch attached, this time with some docs.

Added to July commitfest.

--
Alvaro Herrera

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

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

Re: [GENERAL] DB Connections in TIME_WAIT state

John Gateley <gateley@jriver.com> writes:
> Hi, I'm using the Pg perl module to connect to Postgresql 8.1
> via localhost (127.0.0.1) from webscripts. I'm noticing a lot
> of TIME_WAIT socket connections:

> tcp 0 0 127.0.0.1:39291 127.0.0.1:5432 TIME_WAIT
> tcp 0 0 127.0.0.1:60720 127.0.0.1:5432 TIME_WAIT
> tcp 0 0 127.0.0.1:60735 127.0.0.1:5432 TIME_WAIT
> tcp 0 0 127.0.0.1:60769 127.0.0.1:5432 TIME_WAIT
> tcp 0 0 127.0.0.1:39281 127.0.0.1:5432 TIME_WAIT
> ...

This seems like a kernel bug to me. The TCP stack ought to know it
doesn't need any shutdown delay on a local connection.

> I think the number is high enough (200~250) that sometime the
> server runs out of sockets.

Maybe you need to stop using so many connections --- quite aside
from any kernel issues, a database session isn't exactly cheap
to launch. Consider some form of connection pooling.

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: [HACKERS] psql wrapped format default for backslash-d commands

Bryce Nesbitt wrote:
> It's not that hard to do.
>
> I chose not to, when writing the patch, because it makes the result flow
> over many more lines.
> And regardless, it pretty much has to cut long "words", of which there
> are many in typical SQL output.
> And, I hardly ever read actual large blocks of English at the SQL
> prompt, so: designing for that use case did
> not float my boat at the time.
> And, word wrapping messes with the cell contents more than I was
> comfortable doing. With the current wrapping all whitespace is preserved
> and apparent.
>
> But, if your boat is floated, then by all means go for it.

Yea, you could probably call it a 'chopped' format rather than a
'wrapped' format.

While I got a lot of feedback on screen width detection for wrapped, I
got little freedback on the display format. We can address that now.

I think we can wrap if there is whitespace within a few characters
before the break point, and use a dash if we have to break a word. Is
that what people want?

Also, about the format 'auto' idea that uses expanded display, I am
thinking because expanded is a separate setting and not a format, I
should just add a possible 'auto' value to the expanded format that
could be triggered by either 'aligned' or 'wrapped' formats when the
output doesn't fit on the screen.

(In hindsight, I think we should have had pager be on/auto/off rather
than always/on/off; the later is kind of confusing and would be
inconsistent if I add 'auto' to expanded.)

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

Re: [HACKERS] Problem returning strings with pgsql 8.3.x

On Tue, May 13, 2008 at 8:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Mon, May 12, 2008 at 11:23:17PM -0600, Josh Tolley wrote:
> >> SPI_push();
> >> retval =
> >> InputFunctionCall(&flinfo, lolVarGetString(returnVal, true),
> >> resultTypeIOParam, -1);
> >> SPI_pop();
>
> > Won't this cause the return value to be allocated inside a new memory
> > block which gets freeds at the SPI_pop?
>
> The SPI_pop in itself is harmless ... the problem is the SPI_finish
> further down, which will release all simple palloc's done within the
> SPI function context. What he needs is something comparable to this bit
> in plpgsql:
>
> /*
> * If the function's return type isn't by value, copy the value
> * into upper executor memory context.
> */
> if (!fcinfo->isnull && !func->fn_retbyval)
> {
> Size len;
> void *tmp;
>
> len = datumGetSize(estate.retval, false, func->fn_rettyplen);
> tmp = SPI_palloc(len);
> memcpy(tmp, DatumGetPointer(estate.retval), len);
> estate.retval = PointerGetDatum(tmp);
> }
>
> ie, push the data into something allocated with SPI_palloc().

I'll give this a shot as soon as I can... many thanks

> I would bet large amounts of money that the problem is not "new in
> 8.3.0", either. Perhaps Josh was not testing in an --enable-cassert
> (CLOBBER_FREED_MEMORY) build before.

I'll check... that's definitely not unlikely. Again, thanks.

- Josh

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

Re: [ADMIN] how to get a number of seconds in some interval ?

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> So it seems that the arbitary assumption of (1 month=30 days, and 1 day = 24 hours) has already been made in the extract function.

Right, as I said:

>> You can derive a number if you're willing to make arbitrary assumptions
>> about the relationships of the different units (as indeed extract(epoch)
>> does),

The point is that we don't want to propagate that bogus assumption
further than we have to. EXTRACT() already implies loss of information,
so it's reasonable for it to deliver a simplified view of reality, but
it doesn't follow that division should.

regards, tom lane

--
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] no privileges were granted

Volkan YAZICI <yazicivo@ttmail.com> writes:
> test_1_5_0_0=> GRANT EXECUTE ON FUNCTION emove.emove_outgoingmessages_delete_for_commsrv() TO emove;
> WARNING: no privileges were granted for "emove_outgoingmessages_delete_for_commsrv"
> GRANT

> Does anybody have an idea about what might be causing the problem?

What this means is that the user doing the GRANT doesn't actually have
permissions to do it. (Yeah, the message pretty much sucks, but the
behavior is mandated by SQL spec.)

Most likely, you aren't the owner of the function.

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: [HACKERS] Fairly serious bug induced by latest guc enum changes

Magnus Hagander <magnus@hagander.net> writes:
> Right, but I still need the other part of the check, right? This one
> still fails the same check as my patch, no? Because I assume the hole
> you found there was that get_sync_bit() will return 0 for two different
> sync methods as long as none of them are O_SYNC or O_DSYNC...

No, my point was that there are three possible states of sync_bit and
your patch only accounted for transitions between two of 'em. For
instance, if sync_bit goes to 0 we must close and reopen the file,
else we'll be doing both O_SYNC flush and whatever flush method
is supposed to be getting used.

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: [ADMIN] EDB database link to Oracle

Thanks for the head up. I was just informed.

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Tuesday, May 13, 2008 8:56 AM
To: Travis Smith
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] EDB database link to Oracle

Travis Smith wrote:
> When using a database link in Postgres Plus AS 8.3 to an Oracle
instance
> I receive...

You should contact EnterpriseDB for support with their products.

Joshua D. Drake

_______________________________________________________

The information in this email or in any file attached
hereto is intended only for the personal and confiden-
tial use of the individual or entity to which it is
addressed and may contain information that is propri-
etary and confidential. If you are not the intended
recipient of this message you are hereby notified that
any review, dissemination, distribution or copying of
this message is strictly prohibited. This communica-
tion is for information purposes only and should not
be regarded as an offer to sell or as a solicitation
of an offer to buy any financial product. Email trans-
mission cannot be guaranteed to be secure or error-
free. P6070214

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

Re: [HACKERS] Problem returning strings with pgsql 8.3.x

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Mon, May 12, 2008 at 11:23:17PM -0600, Josh Tolley wrote:
>> SPI_push();
>> retval =
>> InputFunctionCall(&flinfo, lolVarGetString(returnVal, true),
>> resultTypeIOParam, -1);
>> SPI_pop();

> Won't this cause the return value to be allocated inside a new memory
> block which gets freeds at the SPI_pop?

The SPI_pop in itself is harmless ... the problem is the SPI_finish
further down, which will release all simple palloc's done within the
SPI function context. What he needs is something comparable to this bit
in plpgsql:

/*
* If the function's return type isn't by value, copy the value
* into upper executor memory context.
*/
if (!fcinfo->isnull && !func->fn_retbyval)
{
Size len;
void *tmp;

len = datumGetSize(estate.retval, false, func->fn_rettyplen);
tmp = SPI_palloc(len);
memcpy(tmp, DatumGetPointer(estate.retval), len);
estate.retval = PointerGetDatum(tmp);
}

ie, push the data into something allocated with SPI_palloc().

I would bet large amounts of money that the problem is not "new in
8.3.0", either. Perhaps Josh was not testing in an --enable-cassert
(CLOBBER_FREED_MEMORY) build before.

regards, tom lane

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

[GENERAL] PG -v- MySQL

Hi:

 

I work for a large corporation that uses many different databases.  I used to use Oracle but then moved away, mainly because of the expense and complexity.  I wanted to stay on Linux, so I shy’ed away from SQL-Server.  Many others around here use MySQL, but I decided to go with Postgres because at the time, it had more features and seemed more stable and robust.  I’ve been very pleased with PG so far.

 

I’ve been invited to participate in a forum which discusses technical issues for relational DBs used in the corporation.  The 3 DBs they are discussing are Oracle, SQL-Server and MySQL.  I’d like to introduce PG, but want to be able to address the “Why not use MySQL” questions when they arise.  I know in the past there have been many comparisons between the 2 DBs but would like to know if there are any good, recent ones.  Also, any “off-the-cuff” comments would be welcome, especially if you have used both DBs.

 

Thanks in Advance

-dave

 

Re: [GENERAL] Substring Problem

Type casting is required since 8.3, try

SELECT substring(date :: varchar from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC

Bye...
Ludwig

Hi there,

it seems to work with 8.1, but not anymore with 8.3. What is wrong with this substring request? Or is it some installation issue? Thanks for any suggestion!

SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC

It says:

ERROR:  function pg_catalog.substring(date, integer, integer) does not exist LINE 1: SELECT substring(date from 1 for 7) as stryearmonth, COUNT(i...

Re: [GENERAL] Substring Problem

On Tue, May 13, 2008 at 03:26:57PM +0200, Stefan Schwarzer wrote:
> SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS
> countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY
> stryearmonth ORDER BY stryearmonth ASC

Another way of doing this is by using date_trunc, i.e.:

SELECT date_trunc('month',date) AS yearmonth...

I think it'll still realise it can use indexes (if they're appropriate)
that way.

Sam

--
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] EDB database link to Oracle

Travis Smith wrote:
> When using a database link in Postgres Plus AS 8.3 to an Oracle instance
> I receive…

You should contact EnterpriseDB for support with their products.

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: [HACKERS] Fairly serious bug induced by latest guc enum changes

Tom Lane wrote:
> I wrote:
> > Okay, but you failed to correctly reproduce the conditions for
> > closing the old file.
>
> A more bulletproof solution might involve passing sync_method to
> get_sync_bit as an explicit parameter, and then the assign hook
> could do
> if (get_sync_bit(sync_method) !=
> get_sync_bit(new_sync_method)) XLogFileClose();

Right, but I still need the other part of the check, right? This one
still fails the same check as my patch, no? Because I assume the hole
you found there was that get_sync_bit() will return 0 for two different
sync methods as long as none of them are O_SYNC or O_DSYNC...

//Magnus

--
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] Relation missing?

am Tue, dem 13.05.2008, um 15:43:52 +0200 mailte A B folgendes:
> In this function I create no table, I just do insert into various
> already existing tables.

Okay, sorry.

> It is not a prepared statement, but a function I created, so I'm not
> sure if EXECUTE is what I should use.
> This function has successfully run many times before, over several days.
>
> Excuse my ignorance, but what is DDL commands? Is that like creating tables?

Right, Data Definition Language.

Regards, 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: [HACKERS] Fairly serious bug induced by latest guc enum changes

I wrote:
> Okay, but you failed to correctly reproduce the conditions for closing
> the old file.

A more bulletproof solution might involve passing sync_method to
get_sync_bit as an explicit parameter, and then the assign hook
could do
if (get_sync_bit(sync_method) != get_sync_bit(new_sync_method))
XLogFileClose();

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

[ADMIN] EDB database link to Oracle

When using a database link in Postgres Plus AS 8.3 to an Oracle instance I receive…

 

 

SQL> create database link ezdev connect to pguser identified by pguser

  2  using oci '//devdb:1521/ezdev'

  3  ;

 

Database Link created.

 

SQL> select * from ezorder@ezdev;

ERROR near line 1:

unable to load OCI library: Access is denied.

 

 

Im on windows the enterprisedb user is an Administrator.  What am I missing?  Thanks

 

 

-TaS

---

The information in this email or in any file attached hereto is intended only for the personal and confidential use of the individual or entity to which it is addressed and may contain information that is proprietary and confidential. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product. Email transmission cannot be guaranteed to be secure or error-free. P6070214

Re: [HACKERS] Fairly serious bug induced by latest guc enum changes

Magnus Hagander <magnus@hagander.net> writes:
> Since it didn't really sound like a nice option, here's a third one I
> came up with later. Basically, this one splits things apart so we only
> use one variable, which is sync_method. Instead of using a macro to get
> the open sync bit, it uses a function. This makes the assign hook only
> responsible for flushing and closing the old file.

Okay, but you failed to correctly reproduce the conditions for closing
the old file.

> Thoughts? And if you like it, is it enough to expect the compiler to
> figure out to inline it or should we explicitly inline it?

I don't think we care that much, since it's only invoked when we're
about to do a moderately expensive kernel call.

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: [NOVICE] Relation missing?

In this function I create no table, I just do insert into various
already existing tables.
It is not a prepared statement, but a function I created, so I'm not
sure if EXECUTE is what I should use.
This function has successfully run many times before, over several days.

Excuse my ignorance, but what is DDL commands? Is that like creating tables?

2008/5/13 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> am Tue, dem 13.05.2008, um 15:08:51 +0200 mailte A B folgendes:
>
>
> > I got an error
> >
> > SELECT ERecord(100, 3492,'2008-05-13 10:25:57', 1, 0);
> >
> > gave me (translating into english)
> >
> > ERROR: relation with OID 31330 doesn't exist
> > CONTEXT: SQL-statement: "INSERT INTO eLog (customer,shop,event,time)
> > VALUES ( $1 , $2 , $3 , $4 )"
> > PL/pgSQL function "erecord" line 5 at SQL statement
> >
> > ERecord is a function written in PL/pgSQL... so what can this mean?
>
> Wild gues: within this function you create this table and you don't use
> EXECUTE for that. When you start a new session, the first time yout
> function works fine, but the second call fails. Right?
>
> PG caches the plan and the OID's for objects, because this fact it fails
> the second time. To prevent this, use EXECUTE for all DDL-commands.
>
>
> 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
>

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

Re: [NOVICE] Relation missing?

am Tue, dem 13.05.2008, um 15:08:51 +0200 mailte A B folgendes:
> I got an error
>
> SELECT ERecord(100, 3492,'2008-05-13 10:25:57', 1, 0);
>
> gave me (translating into english)
>
> ERROR: relation with OID 31330 doesn't exist
> CONTEXT: SQL-statement: "INSERT INTO eLog (customer,shop,event,time)
> VALUES ( $1 , $2 , $3 , $4 )"
> PL/pgSQL function "erecord" line 5 at SQL statement
>
> ERecord is a function written in PL/pgSQL... so what can this mean?

Wild gues: within this function you create this table and you don't use
EXECUTE for that. When you start a new session, the first time yout
function works fine, but the second call fails. Right?

PG caches the plan and the OID's for objects, because this fact it fails
the second time. To prevent this, use EXECUTE for all DDL-commands.

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

[ANNOUNCE] Call for papers in PGCon Brazil 2008

The Brasilian PostgreSQL User Group is proud to invite you to
participate in PGCon-Brazil 2008[1]. After the realization of
PGCon-Brazil 2007[2], we inviting the international comunity to submit
your proposal of lectures to our second release of this event.
PGCon-Brasil 2008 will happen in 26 and 27 of september, at
UNICAMP[3].

* Submissions:
o We will choose up to 2 lectures in this year;
o Each lecture will have 60 minutes including time for questions;
o Any one could send more then one proposal, but is possible
that no more than one will be select;
o Only will be accepted submissions done according the calendar;
o Selected speakers need to sent a draft of presentation
according the calendar;
o All slides used in the lectures will be published in
www.postgresql.org.br;
o Please submit all proposals thorugh this mail:
pgcon@postgresql.org.br whith subject "International Call For Paper"
and put the following information on the body:
+ Full name
+ E-mail
+ Phone number
+ Small biograph (up to 500 characters)
+ Title of presentation
+ Resume of presentation (up to 500 characters)
+ Full description of presentation (up to 3000 characters)
+ Place where you will come from (country / city)
+ Your primary language that you prefer to speak;
+ Other languages that you feel confortable to speak;
+ Estimate cost of transport (please put a link to the
place you find these price or at least the name of the company and the
selected class)
+ Have any partner who could sponsor the trip?
+ Have any type of personal founds to help to cost the trip?

* Calendar:
o 2008/05/12 – Publish of call for papers;
o 2008/05/31 – Last date for send your proposal;
o 2008/06/18 – Last date for select papers and send results
to speakers;
o 2008/08/04 – Last date to confirm the presence of selected
papers and publish result;
o 2008/08/29 – Present a draft of the slides to be used in
the lecture;

* Observation: PGCon Brasil don't have 100% shure that founds will go
for this, so there is no promisse that Brasilian PUG will pay all the
costs of the two selected speakers. However, with the success of our
first PGCon Brazil its very resonable, but nobody really shure about
any think now. So we start these process early to work for it. Any
help in contact international sponsors will be very nice too.

[1]http://pgcon.postgresql.org.br
[2]http://pgcon.postgresql.org.br/2007
[3]http://www.unicamp.br

Hope see you at Brazil soon,
Fábio Telles
--
blog: http://www.midstorm.org/~telles/
e-mail / jabber: fabio.telles@gmail.com

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe@postgresql.org

[GENERAL] Strange behaviour - performance decreases after each TRUNCATE

Hallow.
My name is IVO GELOV. Please excuse my English, it is not my primary language.
I've started to learn PostgreSQL with version 8.1, doing 2 upgrades - to 8.2.4 and now 8.3.1
I consider myself still a novice. Currently I'm porting our small warehouse application from MySQL to Postgre. After
designing tables, indexes and triggers, I wrote several PHP scripts to transfer data from MySQL to Postgre. Since there
are much more checks in my new schema in Postgre, I've able to find many discrepancies inside the old MySQL data.
This "trial and error" process resulted in many restarts of PHP scripts for data transfer. Each of my scripts transfers one
table at a time. It always begins with TRUNCATE CASCADE, and then all the other SQL is between BEGIN/COMMIT,
forming a transaction.
Most of my tables are small - the biggest one is BALANCE with about 840 000 records (all the others are under 150 000).
Each script shows its execution time at the end. I've noticed something strange - first time the construction of
table BALANCE took 419 seconds, and after then each time I run the script - it shows more and more time. I read in documentation,
that indexes may "bloat" - I saw that indexes of several tables (including BALANCE) were 90% fragmented, so I did
REINDEX for the whole database. But the running time of the script for BALANCE still increases - it is now 1190 seconds !
I'm really confused why is this happening. Am I misunderstanding something ? Can anybody help me or just point me ?

Thanks.

--
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] Substring Problem

On Tue, May 13, 2008 at 03:26:57PM +0200, Stefan Schwarzer wrote:
> it seems to work with 8.1, but not anymore with 8.3. What is wrong
> with this substring request? Or is it some installation issue? Thanks
> for any suggestion!
> SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS
> countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY
> stryearmonth ORDER BY stryearmonth ASC

in release notes it says that 8.3 removes some implicit casts. for
example the ones from date to text.

change your substring to:
to_char(date, 'YYYY-MM')
and You should be fine.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

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

[GENERAL] Substring Problem

Hi there,

it seems to work with 8.1, but not anymore with 8.3. What is wrong with this substring request? Or is it some installation issue? Thanks for any suggestion!

SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC

It says:

ERROR:  function pg_catalog.substring(date, integer, integer) does not exist LINE 1: SELECT substring(date from 1 for 7) as stryearmonth, COUNT(i...

[GENERAL] DB Connections in TIME_WAIT state

Hi, I'm using the Pg perl module to connect to Postgresql 8.1
via localhost (127.0.0.1) from webscripts. I'm noticing a lot
of TIME_WAIT socket connections:

tcp 0 0 127.0.0.1:39291 127.0.0.1:5432 TIME_WAIT
tcp 0 0 127.0.0.1:60720 127.0.0.1:5432 TIME_WAIT
tcp 0 0 127.0.0.1:60735 127.0.0.1:5432 TIME_WAIT
tcp 0 0 127.0.0.1:60769 127.0.0.1:5432 TIME_WAIT
tcp 0 0 127.0.0.1:39281 127.0.0.1:5432 TIME_WAIT
...

I think the number is high enough (200~250) that sometime the
server runs out of sockets.

What's the proper way to close the postgresql connection so that
it doesn't go into a TIME_WAIT state?

I tried:

use Pg;
my $Connection = Pg::connectdb($ConnectString);
... do some stuff
$Connection = 0;

That doesn't seem to close the connection.

Any ideas?

Thanks,

j
--
John Gateley <gateley@jriver.com>

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

[pgadmin-hackers] SVN Commit by dpage: r7287 - trunk/pgadmin3/pkg/mac

Author: dpage

Date: 2008-05-13 14:11:51 +0100 (Tue, 13 May 2008)

New Revision: 7287

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

Log:
Add psql to the appbundle for use as a plugin

Modified:
trunk/pgadmin3/pkg/mac/Makefile.am

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

[NOVICE] Relation missing?

I got an error

SELECT ERecord(100, 3492,'2008-05-13 10:25:57', 1, 0);

gave me (translating into english)

ERROR: relation with OID 31330 doesn't exist
CONTEXT: SQL-statement: "INSERT INTO eLog (customer,shop,event,time)
VALUES ( $1 , $2 , $3 , $4 )"
PL/pgSQL function "erecord" line 5 at SQL statement

ERecord is a function written in PL/pgSQL... so what can this mean?

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

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

Thanks guys, that's exactly what I wanted.

----- Original Message ----
> From: Richard Huxton <dev@archonet.com>
> To: Glyn Astill <glynastill@yahoo.co.uk>
> Cc: slony1-general@lists.slony.info; pgsql-general@postgresql.org
> Sent: Tuesday, 13 May, 2008 1:34:18 PM
> Subject: Re: [GENERAL] Stripping out slony after / before / during pg_restore?
>
> Glyn Astill wrote:
> > Hi people,
> >
> > I'm setting us up a separate staging / test server and I want to read
> > in a pg_dump of our current origin stripping out all the slony stuff.
> >
> > I was thinking this could serve two purposes a) test out backups
> > restore properly and b) provide us with us with the staging / test
> > server
> >
> > What's the best way to remove all the slony bits?
>
> Well, you can always just drop the slony schema (with a cascade) - that
> should do it.
>
> --
> Richard Huxton

__________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html

--
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] Is this a bug? (changing sequences in default value)

En un mensaje anterior, Merlin Moncure escribió:
[...]
> >> > Am I doing something wrong?
> >>
> >> yes and no when you created the table initially you probably made it
> >> a 'serial' column which set up the ownership that prevents the drop
> >> operation. that ownership did not go away when you altered the
> >> default to the new serial.
> >>
> >> to fix this,
> >> alter sequence sequence table1_id_seq owned by none; -- now you can drop
> >
> > Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation
> > of this syntax I tried gives me error as, apparently, it should:
> >
> > \h ALTER SEQUENCE
> > Command: ALTER SEQUENCE
> > Description: change the definition of a sequence generator
> > Syntax:
> > ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
> > [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
> > MAXVALUE ]
> > [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
>
> oop, you are using 8.1 :-). This was added in a later version. drop
> sequence ... cascade should probably work. you can try it out in a
> transaction to be sure.

Thanks for your help, but cascade doesn't make a difference.

Fernando.

--
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] Stripping out slony after / before / during pg_restore?

On Tue, May 13, 2008 at 5:42 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:

Hi people,

I'm setting us up a separate staging / test server and I want to read in a pg_dump of our current origin stripping out all the slony stuff.

I was thinking this could serve two purposes a) test out backups restore properly and b) provide us with us with the staging / test server

What's the best way to remove all the slony bits?

I was thinking read in the dump, then use uninstall node - but I'd rather not have to run the slon daemons.

Or should I just leave all the slony stuff in there... would it cause us any problems? There'd be no slons running and the next night it's all wiped and restored again...

Anyone got any ideas? Anyone got something similar already?

    You need to have a slon daemon running, configured to monitor the restored database, and the essential settings for this to work are: host name, port-number, database name and the Slony cluster name. Since you do not have a slon daemon for the restored database, I guess you are fine after restoring the database.

    If you really need to be sure that this restored database does not take part in replication, you can go ahead an DROP CASCADE the replication schema from the database. For eg. if your Slony cluster name was my_repl_cluster, then you can connect to the restored database and issue 'DROP SCHEMA _my_repl_cluster CASCADE;' to get rid of the replication information. Now, even if there's a slon daemon running for this DB, it won't be able to do anything; you can eye the slon's log to see the warnings it will generate.

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: [GENERAL] pg_standby / WAL archive-restore through system restarts

David Wall a écrit :
> [...]
> During regular operations, Server A and Server B may independently need
> to be rebooted or postgresql restarted, etc. Is there any sync-ing
> between Server A and Server B I have to worry about when doing this?
> That is, will Server B need to know anything about the fact that the
> postmaster on Server A going up and down?

Server B doesn't need to know anything.

> Will it just run normally,
> waiting for the restarted Server A to send over new WAL archives?
>

Yes, it will. Anyways, be careful on the command line options you use
with pg_standby.

> Can I shutdown Server B simply by killing the postmaster and restart it
> back in recovery mode to continue re-syncing where it left off? Or does
> stopping Server B in recovery mode require any manual re-sync steps
> before it can resume recovery?
>

I think you'll need to redo the whole process : restore the full data
backup, create the recovery.conf file, etc.

> If I ever tell Server B to stop recovery (assuming I didn't intend for
> it to become the new primary), I cannot just resume recovery mode unless
> I do the full data backup again from Server A and restore on Server B in
> start in recovery mode. That is, once you tell PG to end recovery, it
> becomes a primary and no longer can resume recovery mode without redoing
> the recovery setup steps.
>

Correct.

--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.com

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

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

Glyn Astill wrote:
> Hi people,
>
> I'm setting us up a separate staging / test server and I want to read
> in a pg_dump of our current origin stripping out all the slony stuff.
>
> I was thinking this could serve two purposes a) test out backups
> restore properly and b) provide us with us with the staging / test
> server
>
> What's the best way to remove all the slony bits?

Well, you can always just drop the slony schema (with a cascade) - that
should do it.

--
Richard Huxton

--
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] Upgreade 8.3.0 to 8.3.1 on windows: insufficient privileges to install system service

Kev wrote:
> I had a different problem (initdb errors) where the only difference
> was whether I was on Terminal Services or not. Running TS using
> "mstsc.exe /console" (even within a session) worked for me.

This is why I also tried with VNC which doesn't face this problem.

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

Re: [HACKERS] odd output in restore mode

I wrote:
>
> However, we should probably make the behaviour switchable. If the
> archive_command populating the archive_directory were rsync, for
> example, this problem should not occur, because it copies to a temp
> file, and then renames it, so we should never see an incomplete file
> even though rsync also apparently preallocates space.
>
>

Another and probably simpler thing to try would be the GnuWin32 version
of cp. If we can verify that it behaves itself, we should probably
recommend it for use in archive_command instead of the native Windows copy.

I'm still not sure how to construct a test, though.

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

[GENERAL] Stripping out slony after / before / during pg_restore?

Hi people,

I'm setting us up a separate staging / test server and I want to read in a pg_dump of our current origin stripping out all the slony stuff.

I was thinking this could serve two purposes a) test out backups restore properly and b) provide us with us with the staging / test server

What's the best way to remove all the slony bits?

I was thinking read in the dump, then use uninstall node - but I'd rather not have to run the slon daemons.

Or should I just leave all the slony stuff in there... would it cause us any problems? There'd be no slons running and the next night it's all wiped and restored again...

Anyone got any ideas? Anyone got something similar already?

Cheers
Glyn

__________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html

--
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] Server not listening

On Mon, May 12, 2008 at 6:53 PM, D Galen <degalen@comcast.net> wrote:
> If this isn't the right place to post this, please advise.
>
> I've spent a week trying to get PostgreSQL 8.3 to install correctly on
> WIN2K. Server will load & I see the server processes loaded but none of
> them have any open ports. I keep getting the message the server isn't
> listening. Server set up to connect to default port 5432 on localhost but
> doesn't appear to be opening the port when it loads.

odd. can you try configuring the server to listen on '*' in postgresql.conf?

merlin

--
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] RAID controllers for Postgresql on large setups

On Tue, May 13, 2008 at 8:00 AM, Francisco Reyes <lists@stringsutils.com> wrote:
> PFC writes:
>
>
> > You say that like you don't mind having PCI in a server whose job
> is to perform massive query over large data sets.
> >
>
> I am in my 4th week at a new job. Trying to figure what I am working with.
> From what I see I will likely get as much improvement from new hardware as
> from re-doing some of the database design. Can't get everything done at
> once, not to mention I have to redo one machine sooner rather than later so
> I need to prioritize.
>
>
>
> > In fact for bulk IO a box with 2 SATA drives would be just as fast as
> your monster RAID, lol.
> >
>
> I am working on setting up a standard test based on the type of operations
> that the company does. This will give me a beter idea. Specially I will work
> with the developers to make sure the queries I create for the benchmark are
> representative of the workload.
>
>
>
> > Adding more drives will help random reads/writes but do nothing for
> throughput since the tiny PCI pipe is choking.
> >
>
> Understood, but right now I have to use the hardware they already have.
> Just trying to make the most of it. I believe another server is due in some
> months so then I can better plan.
>
> In your opinion if we get a new machine with PCI-e, at how many spindles
> will the SCSI random access superiority start to be less notable? Specially
> given the low number of connections we usually have running against these
> machines.
>
>
>
> > However RAID5 will choke and burn on small random writes, which
> will come from UPDATing random rows in a large table, updating indexes,
> etc. Since you are doing this apparently, RAID5 is therefore NOT advised !
> >
>
> I thought I read a while back in this list that as the number of drives
> increased that RAID 5 was less bad. Say an external enclosure with 20+
> drives.

maybe, but I don't think very many people run that many drives in a
raid 5 configuration...too dangerous. with 20 drives in a single
volume, you need to be running raid 10 or raid 6. 20 drive raid 50 is
pushing it as well..I'd at least want a hot spare.

merlin

--
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] Installation Steps to migrate to Postgres 8.3.1

Hi,
Along these lines, the usual upgrade path is a pg_dump/pg_restore set.
However, what if your database is large (> 50GB), and you have to
minimize your downtime (say less than an hour or two). Any suggestions
on how to handle that kind of situation? It sure would be nice to have
some kind of tool to update in-place a database, though I know that's
not a likely path.

Doug

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Scott
Marlowe
Sent: Tuesday, May 13, 2008 4:26 AM
To: Gauri Kanekar
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Installation Steps to migrate to Postgres 8.3.1

On Mon, May 12, 2008 at 11:40 PM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:
> Hi,
>
> We want to migrate from postgres 8.1.3 to postgres 8.3.1.
> Can anybody list out the installation steps to be followed for
migration.
> Do we require to take care of something specially.

First, I'd recommend updating your 8.1.x install to 8.1.11 or whatever
the latest is right now.

There are some ugly bugs hiding in 8.1.3 if I remember correctly (Tom
just mentioned one that could do things like leaving orphaned objects
in the db in another thread.) It's always a good idea to keep up to
date on the updates of pgsql. Some updates aren't critical, but most
early ones in the 7.x through 8.1 tended to have a lot of bugs fixed
in them in the first few updates.

Then, your migration to 8.3.x can be done a bit more leisurely and
well planned and tested, without putting your current data in danger.

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

--
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] odd output in restore mode

Simon Riggs wrote:
> On Tue, 2008-05-13 at 08:42 +0100, Dave Page wrote:
>
>> On Tue, May 13, 2008 at 2:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>>> Andrew Dunstan <andrew@dunslane.net> writes:
>>>
>>>
>>>> Simon Riggs wrote:
>>>>
>>> >> Well, the patch was rejected long ago, not sure why its in this
>>> >> commitfest. But its an open issue on the Windows port.
>>>
>>> > Surely the right fix is to use the recently implemented
>>> > pgwin32_safestat() (if we aren't already - I suspect we probably are)
>>> > and remove the kluge in pg_standby.c.
>>>
>>> I think the open issue is how to know whether pgwin32_safestat fixes the
>>> problem that the kluge tried to work around.
>>>
>> Per the comments on the commitfest page, I don't believe it is.
>> pgwin32_safestat fixes a bug in which stat() returns stale information
>> (if memory serves). The hack in pg_standby was added because copy in
>> Windows appears to preallocate the required space for the file it's
>> copying, thus checking the file size to verify that the copy has
>> completed is not a valid test.
>>
>
> Could somebody suggest and test an improvement to the Windows code, to
> fix the kluge?
>
>

Given what Dave says, I'm not sure there is an easy one, at least
without a lot of testing. Greg Stark's suggestion might or might not work.

However, we should probably make the behaviour switchable. If the
archive_command populating the archive_directory were rsync, for
example, this problem should not occur, because it copies to a temp
file, and then renames it, so we should never see an incomplete file
even though rsync also apparently preallocates space.

We should also document it better in the code, along the lines of Dave's
comment above.

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

Re: [PERFORM] RAID controllers for Postgresql on large setups

PFC writes:

> You say that like you don't mind having PCI in a server whose job is to
> perform massive query over large data sets.

I am in my 4th week at a new job. Trying to figure what I am working with.
From what I see I will likely get as much improvement from new hardware as
from re-doing some of the database design. Can't get everything done at
once, not to mention I have to redo one machine sooner rather than later so
I need to prioritize.

>In fact for bulk IO a box with 2 SATA drives would be just as fast as
> your monster RAID, lol.

I am working on setting up a standard test based on the type of operations
that the company does. This will give me a beter idea. Specially I will work
with the developers to make sure the queries I create for the benchmark are
representative of the workload.

>Adding more drives will help random reads/writes but do nothing for
> throughput since the tiny PCI pipe is choking.

Understood, but right now I have to use the hardware they already have. Just
trying to make the most of it. I believe another server is due in some
months so then I can better plan.

In your opinion if we get a new machine with PCI-e, at how many spindles
will the SCSI random access superiority start to be less notable? Specially
given the low number of connections we usually have running against these
machines.

>If you mean doing large COPY or inserting/updating lots of rows using one
> SQL statement, you are going to need disk bandwidth.

We are using one single SQL statement.

> http://tweakers.net/reviews/557/17/comparison-of-nine-serial-ata-raid-5-adapters-pagina-17.html

I have heard great stories about Areca controllers. That is definitely one
in my list to research and consider.

> However RAID5 will choke and burn on small random writes, which will come
> from UPDATing random rows in a large table, updating indexes, etc. Since
> you are doing this apparently, RAID5 is therefore NOT advised !

I thought I read a while back in this list that as the number of drives
increased that RAID 5 was less bad. Say an external enclosure with 20+
drives.

>Have you considered Bizgres ?

Yes. In my todo list, to check it further. I have also considered Greenplums
may DB offering that has clustering, but when I initially mentioned it there
was some reluctance because of cost. Also will look into Enterprise DB.

Right now I am trying to learn usage patterns, what DBs need to be
re-designed and what hardware I have to work with. Not to mention learning
what all these tables are. Also need to make time to research/get a good
ER-diagram tool and document all these DBs. :(

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

[GENERAL] Upgreade 8.3.0 to 8.3.1 on windows: insufficient privileges to install system service

Hello !

I obtain an error message when I launch the upgrade.bat on windows 2000
server using an administrator account (the one used to install postgresql
8.3.0): insufficient privileges to install a system service.
I log in with VNC, and I have the same message using Terminal server.
Is this a problem with the installer or with my configuration ?
What could I do or check to avoid this message ?

I did not have this problem on my development server which is identically
configured (appart the fact I do not use a distant connection)

Thanks

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