[ADMIN] Restoring Backup With OIDs As Primary Key

Hello,

I am trying to restore from a backup db that used oids as primary keys * ... when I do this the max oid value in the new database is around the 1700 mark where as the database I am pulling in is around 20,000,000 . which means any new data added to the dataabse is going to be clobbered.

Is there a way to increment the OID by changing a value in the system tables?

thanks in advance

* at the time this was implemented way back at 7.x, from what I had read it seemed like acceptable practice :/

Re: [GENERAL] Dump/restore with bad data and large objects

Joshua

The TOC feature sounds good, as does converting a single table to plain text.

But I can't find documentation for the TOC feature under pg_dump or pg_restore. I'm looking in postgresql-8.2.1-US.pdf.

Neither could I see anything about converting a single table to a plain text dump.

Also, I stumbled across the statement that you can't restore large objects for a single table. Is that true?

Another thing I couldn't find was how to dump roles using -Fc.

John

On Mon, 25 Aug 2008 10:04:13 -0700, Joshua Drake wrote:

>On Mon, 25 Aug 2008 10:21:54 -0400
>"John T. Dow" <john@johntdow.com> wrote:
>
>> By "bad data", I mean a character that's not UTF8, such as hex 98.
>>
>> As far as I can tell, pg_dump is the tool to use. But it has
>> serious drawbacks.
>>
>> If you dump in the custom format, the data is compressed (nice) and
>> includes large objects (very nice). But, from my tests and the
>> postings of others, if there is invalid data in a table, although
>> PostgreSQL won't complain and pg_dump won't complain, pg_restore will
>> strenuously object, rejecting all rows for that particular table (not
>> nice at all).
>
>You can use the TOC feature of -Fc to remove restoring of that single
>table. You can then convert that single table to a plain text dump and
>clean the data. Then restore it separately.
>
>If you have foregin keys and indexes on the bad data table, don't
>restore the keys until *after* you have done the above.
>
>Sincerely,
>
>Joshua D. Drake
>
>--
>The PostgreSQL Company since 1997: http://www.commandprompt.com/
>PostgreSQL Community Conference: http://www.postgresqlconference.org/
>United States PostgreSQL Association: http://www.postgresql.us/
>Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

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

[COMMITTERS] pgsql: Update URL to Ross William's paper.

Log Message:
-----------
Update URL to Ross William's paper.

Devrim Gunduz.

Modified Files:
--------------
pgsql/src/backend/utils/hash:
pg_crc.c (r1.19 -> r1.20)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/hash/pg_crc.c?r1=1.19&r2=1.20)

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

[pgsql-www] Mailing list spam filtering

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Any progress being made on the mailing lists spam filtering front?
Here's one from hackers, looks like we are not checking the subject
line or the body at all:

Return-Path: <owner-pgsql-general-postgresql.org@postgresql.org>
Delivered-To: pgsql-general-postgresql.org@postgresql.org
Received: from localhost (unknown [200.46.204.183])
by postgresql.org (Postfix) with ESMTP id A696C64FCDF;
Sat, 23 Aug 2008 05:44:11 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
with ESMTP id 40394-04; Sat, 23 Aug 2008 05:44:03 -0300 (ADT)
X-Greylist: delayed 01:04:25.448822 by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from idiomas.udea.edu.co (idiomas.udea.edu.co [200.24.17.72])
by postgresql.org (Postfix) with ESMTP id EA71464FCC7;
Sat, 23 Aug 2008 05:44:05 -0300 (ADT)
Received: from idiomas.udea.edu.co (localhost [127.0.0.1])
by idiomas.udea.edu.co (Postfix) with ESMTP id 8C33B458600;
Fri, 22 Aug 2008 21:06:23 -0500 (COT)
From: "UK LOTTERY BOARD" <info@lottery.co.uk>
Reply-To: webcontactoffice008@gmail.com
Subject: Your Email Won You 3,500,000 GBP 23rd August 2008.....
Date: Fri, 22 Aug 2008 22:06:23 -0400
Message-Id: <20080823013505.M16754@yahoo.fr>
X-Mailer: Open WebMail 2.51 20050228
X-OriginatingIP: 81.199.198.89 (servicios)
MIME-Version: 1.0
Content-Type: text/plain;
charset=iso-8859-1
To: undisclosed-recipients:;
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=3.224 tagged_above=0 required=5
tests=DATE_IN_PAST_06_12=1.854, DCC_CHECK=1.37
X-Spam-Level: ***

UK LOTTERY BOARD

We are pleased to inform you that you have won
3,500,000 GBP in the UK National Lottery Draw held on the
23rd August 2008.For more information contact your claims agent
with the information below:

Mr.Richard Green.
E-mail: richardgreendept1@live.co.uk
Fiduciary claims agent.

Regards,

Mr Norris Warner.
Online Coordinator.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200808251311
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkiy59cACgkQvJuQZxSWSsjvfACfQgtYX+nUHk++yi87lVbxtnE1
vXoAoP3Uu3Jk/FOEVYF1srUQPGBm6Kad
=LNgz
-----END PGP SIGNATURE-----

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

Re: [HACKERS] initdb change

On Mon, Aug 25, 2008 at 09:54:26AM -0700, Joshua D. Drake wrote:
> On Mon, 25 Aug 2008 09:42:21 -0700
> David Fetter <david@fetter.org> wrote:
>
> > > We either need to provide a way to initialize it at initdb, allow
> > > xlogs to be in table space or add a GUC for the location.
> >
> > There's already a way to specify where xlogs should be via
> > -X/--xlogdir.
>
> Sorry should have checked 8.3 initdb instead of 8.2.
>
> > What that doesn't do is put the xlogdir where a DBA
> > would naturally expect to find it. When that DBA doesn't find it in
> > the place they expect, very bad knock-on decisions are likely to
> > result.
>
> O.k. when using 8.3 I did this:
>
> initdb -D /tmp/foo -X /tmp/xlogs
>
> And I got:
>
> /tmp/foo/pg_xlog which is a link to /tmp/xlogs

Oops. Well, this isn't quite the foot-gun I'd previously thought :P

> That seems perfectly logical. If I (without removing the old initdb) do
> this:
>
> /usr/lib/postgresql/8.3/bin/initdb -D /tmp/bar -X /tmp/xlog
>
> I get:
>
> initdb: directory "/tmp/xlog" exists but is not empty
> If you want to store the transaction log there, either
> remove or empty the directory "/tmp/xlog".
> initdb: removing data directory "/tmp/bar"
>
> I just reread your original message a little slower and see that what
> you want is if:
>
> /var/lib/pgsql/data/ exists but is empty you can initdb within that
> directory. However if there is anything in it you can not. You are
> asking that if pg_xlog exists but is empty that we still be able to use
> the DATADIR and you can pass existing so that it will also use pg_xlog
> if it is empty.
>
> My take would be to not add a new flag. Instead to implicitly allow it.
> If initdb finds that DATADIR and pg_xlog is empty it will use both.

Is there some reason why initdb shouldn't just Do The Right Thing™
when it finds an empty extant $PGDATA/pg_xlog directory that passes
the same tests an empty extant $PGDATA would?

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

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

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

Re: [GENERAL] SERIAL datatype

On Mon, Aug 25, 2008 at 10:23 AM, Mark Roberts
<mailing_lists@pandapocket.com> wrote:
>
> On Thu, 2008-08-21 at 13:53 -0600, Scott Marlowe wrote:
>> Regular SERIAL type is limited to a 32 bit int. BIGSERIAL uses a 64
>> bit int.
>
> I think one of the things that would be offsetting is the size
> difference between the two types (32 vs 64 bits, 5 foreign keys, and a
> billion rows or so makes for alot of pain).

Well, of course a 64 bit int is gonna be bigger than a 32 bit, but
with alignment issues and on 64 bit hardware, I'm guessing the
difference isn't exactly twice as slow / twice as much storage. And
it's way faster than a GUID which was what I think started this
thread.

--
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] Dump/restore with bad data and large objects

Tom

My mistake in not realizing that 8.1 and later can dump large objects in the plain text format. I guess when searching for answers to a problem, the posted information doesn't always specify the version. So, sorry about that.

But the plain text format still has serious problems in that the generated file is large for byte arrays and large objects, there is no ability to selectively restore a table, and bad data still isn't detected until you try to restore.

Or did I miss something else?

John

PS: Yes, I know you can pipe the output from pg_dumpall into an archiver, but it's my understanding that the binary data is output in an inefficient format so even if zipped, the resulting file would be significantly larger than the custom format.

On Mon, 25 Aug 2008 12:14:41 -0400, Tom Lane wrote:

>"John T. Dow" <john@johntdow.com> writes:
>> If you dump in plain text format, you can at least inspect the dumped
>> data and fix it manually or with iconv. But the plain text
>> format doesn't support large objects (again, not nice).
>
>It does in 8.1 and later ...
>
>> Also, neither of these methods gets information such as the roles,
>
>Use pg_dumpall.
>
> 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] initdb change

David Fetter wrote:
> There's already a way to specify where xlogs should be via
> -X/--xlogdir. What that doesn't do is put the xlogdir where a DBA
> would naturally expect to find it. When that DBA doesn't find it in
> the place they expect, very bad knock-on decisions are likely to
> result.

I don't understand what that natural place is that you refer to, and it
seems that others don't either. Could you walk us through how one would
use the new option?

mount something somewhere
???
initdb -D /mnt/data -X ???
postgres -D /mnt/data

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
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] initdb change

On Mon, 25 Aug 2008 09:42:21 -0700
David Fetter <david@fetter.org> wrote:

> > We either need to provide a way to initialize it at initdb, allow
> > xlogs to be in table space or add a GUC for the location.
>
> There's already a way to specify where xlogs should be via
> -X/--xlogdir.

Sorry should have checked 8.3 initdb instead of 8.2.

> What that doesn't do is put the xlogdir where a DBA
> would naturally expect to find it. When that DBA doesn't find it in
> the place they expect, very bad knock-on decisions are likely to
> result.

O.k. when using 8.3 I did this:

initdb -D /tmp/foo -X /tmp/xlogs

And I got:

/tmp/foo/pg_xlog which is a link to /tmp/xlogs

That seems perfectly logical. If I (without removing the old initdb) do
this:

/usr/lib/postgresql/8.3/bin/initdb -D /tmp/bar -X /tmp/xlog

I get:

initdb: directory "/tmp/xlog" exists but is not empty
If you want to store the transaction log there, either
remove or empty the directory "/tmp/xlog".
initdb: removing data directory "/tmp/bar"

I just reread your original message a little slower and see that what
you want is if:

/var/lib/pgsql/data/ exists but is empty you can initdb within that
directory. However if there is anything in it you can not. You are
asking that if pg_xlog exists but is empty that we still be able to use
the DATADIR and you can pass existing so that it will also use pg_xlog
if it is empty.

My take would be to not add a new flag. Instead to implicitly allow it.
If initdb finds that DATADIR and pg_xlog is empty it will use both.

Sincerely,

Joshua D. Drake

>
> Cheers,
> David.

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

--
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] playing with catalog tables limits? dangers? was: seq bug 2073 and time machine

On Mon, 25 Aug 2008 12:07:23 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >> If you're feeling corageous, you can remove the pg_depend
> >> entries for that sequence. Make sure to try it in a
> >> transaction and drop
>
> > I'd like to understand better the risks of being courageous?
> > I think my life would be easier if I'd know when it is safe to
> > put hands in the system tables.
>
> Well, it's safe if (a) you know what you're doing, (b) you don't
> make any mistakes, and (c) you don't forget any changes needed to
> keep all the catalogs consistent.
>
> You can protect yourself against (b) by using a transaction, but
> the other two tend to require hacker-grade knowledge of how the
> backend works, so we try to discourage people from doing it.

Why hacker-grade knowledge of the backend?
With "hacker-grade" you mean: undocumented or RTSL?
Isn't the knowledge about how catalog stuff maps on SQL to "guess"
how to achieve certain results?

> pg_depend in particular tends to have rather obscure contents,
> and what's worse is that messing it up usually doesn't have any
> immediately-obvious consequences.

OK... what about concurrent works?
eg. supposing I write the correct SQL should I take care to be the
only one accessing the DB in that moment?

What could be the use case of directly accessing the catalog?

I'd like to have an idea if it is something to invest my time in.
My main interest would be refactoring.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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

[GENERAL] just work installation & configuration

Hi!

I'm a newbie and want to startup my just installed postgres :

* 8.3-community
* SPARC
* Solaris 10

it answers that it has to find postgresql.conf but I can't find it anywere
do I have to write it specifying the most basic options?

Re: [pgsql-www] The headache of psqlODBC binary Download

On Mon, Aug 25, 2008 at 4:24 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> It seems to me that there are too many steps (some are broken) needed
> to find the actual download page for the ODBC driver. My fellow
> co-workers agree with my concern. I would expect that other ODBC users
> feel the same.
>
> Here are the steps needed to find the download page. I'll try to
> point out the steps that I consider "broken".
>
> 1) www.postgresql.org -> downloads
> 2) ./download/ -> Software Catalogue
> 3) ./download/product-categories -> Drivers and interfaces
> 4) ./download/products/2 -> psqlODBC
> 5) ./pgfoundry.org/projects/psqlodbc -> no where to go from here

Yeah, pgFoundry is quite horribly broken in that regard - GBorg did
much better at allowing custom text and links on the homepage.

I've updated the product listing in the catalogue to point to the
download pages directly now.

--
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] SERIAL datatype

On Thu, 2008-08-21 at 13:53 -0600, Scott Marlowe wrote:
> Regular SERIAL type is limited to a 32 bit int. BIGSERIAL uses a 64
> bit int.

I think one of the things that would be offsetting is the size
difference between the two types (32 vs 64 bits, 5 foreign keys, and a
billion rows or so makes for alot of pain).

-Mark

--
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] Dump/restore with bad data and large objects

"John T. Dow" <john@johntdow.com> writes:
> If you dump in plain text format, you can at least inspect the dumped
> data and fix it manually or with iconv. But the plain text
> format doesn't support large objects (again, not nice).

It does in 8.1 and later ...

> Also, neither of these methods gets information such as the roles,

Use pg_dumpall.

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

[pgus-general] Update: 1023, bank account etc...

Hello,

Just a quick update for everyone. I received confirmation last Friday
that our excellent attorney's at DWT (http://www.dwt.com/) had filed
our 1023 with the IRS. I will work on getting that up on the website
shortly. Apparently the IRS still requires the 1023 to be filed via
dead trees; the document is large and will need to be scanned.

We have finalized our banking situation. Our bank is
http://www.sterlingsavingsbank.com/ . This bank was originally chosen
because it is the only bank that has branches where I live and in
Portland. There services are on par with larger banks without being a
huge (such as BoFA) bank (they are not small).

We have also setup accounts with PayPal and Google Checkout. This is a
temporary measure while the corporation builds a financial history. I
hope to have us set up with a real merchant account shortly after the
annual meeting.

As we have been working on the web site, we found a great Drupal Module
called Ubercart. This has allowed us to create a very simple donation
and membership procedure. It is currently integrated with Paypal
(because the google checkout module needs to be ported to PostgreSQL).
The integration allows a very cool feature.

UberCart has a module called roles. This module allows us to specify,
this member just registered under "Professional + West" their order was
successful. Thus they automatically belong to the roles pgconwest and
professsional_membership and they expire in 12 months. This will allow
us to keep the effort of tracking membership to a relatively low burden.

I expect to open membership sometime this week and will announce here
when I do. At the same time people will be able to register for West.

Lastly, I wanted to thank everyone for their patience as we roll
through this process. I know a lot of people are anxious for this to be
done and I can assure you that I am one of them. The only thing I can
offer is; all good things come in time.

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

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

Re: [HACKERS] initdb change

On Mon, 25 Aug 2008 08:40:17 -0700
David Fetter <david@fetter.org> wrote:

> Folks,
>
> While initdb allows you to choose a directory for transaction logs, it
> can't already exist, so it can't be in its usual place under $PGDATA.
> I'd like to propose that this be allowed by having an alternate syntax
> for the -X option, namely, "existing."
>
> When -X is set to "existing", it would check whether pg_xlog is a
> directory and the only thing in $PGDATA. One way to do that is to add
> a new return code to check_data_dir() and a new branch of the case
> statement after it's called.
>

Why not just implicitly do it? If the directory already exists we throw
a notice saying:

NOTICE: pg_xlog destination already exists, creating pg_xlog underneath
(or some such thing)

> What say?
>
> Cheers,
> David.

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

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

[HACKERS] initdb change

Folks,

While initdb allows you to choose a directory for transaction logs, it
can't already exist, so it can't be in its usual place under $PGDATA.
I'd like to propose that this be allowed by having an alternate syntax
for the -X option, namely, "existing."

When -X is set to "existing", it would check whether pg_xlog is a
directory and the only thing in $PGDATA. One way to do that is to add
a new return code to check_data_dir() and a new branch of the case
statement after it's called.

What say?

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

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

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

[COMMITTERS] pgsql: Have man page generation work on VPATH builds too.

Log Message:
-----------
Have man page generation work on VPATH builds too.

Modified Files:
--------------
pgsql/doc/src/sgml:
Makefile (r1.106 -> r1.107)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/Makefile?r1=1.106&r2=1.107)

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

Re: [HACKERS] temporary statistics option at initdb time

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> Tom Lane wrote:
>>> I think this is introducing complication and race conditions to solve a
>>> problem that no one will really care about. Just let people change the
>>> filename at SIGHUP and document that doing that on-the-fly may cause
>>> stats queries to fail for a short interval.
>
>> Ok, I'll do it that way.
>
> BTW, it might be worth tweaking the stats collector to dump stats
> immediately after responding to SIGHUP, just to narrow this window
> as much as possible.

Pah, that is too obvious :-)
Yeah, should've thought of that, will make that happen.

> [ squint... ] Actually, it looks like the stats collector SIG_IGNores
> SIGHUP? That can't be right (any more) can it?

It did, until after my patch that was applied a couple of minutes ago.
After the patch, it cares about SIGHUP.

//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: [HACKERS] temporary statistics option at initdb time

I wrote:
> [ squint... ] Actually, it looks like the stats collector SIG_IGNores
> SIGHUP? That can't be right (any more) can it?

Oh, never mind, I see my hour-old CVS pull is obsolete ...

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: [pgsql-es-ayuda] Problemas al realizar un pg_dump y pg_restore

On 8/25/08, mario ramirez cervera <marioramirezph@gmail.com> wrote:
> hola a todos:
>
> Estoy realiando la migracion la base de datos de un postgres. Relalizo un
> pg_dump -Ft -b gforge >gforge.dump y un restore en una bbdd vacia de la
> siguiente maner a pg_restore -Ft gfore gforge.dump;;;; pero me d aun monto
> de errores
>

si dices que errores te da seria mas facil ayudar

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. (593) 87171157
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [HACKERS] temporary statistics option at initdb time

Magnus Hagander <magnus@hagander.net> writes:
> Tom Lane wrote:
>> I think this is introducing complication and race conditions to solve a
>> problem that no one will really care about. Just let people change the
>> filename at SIGHUP and document that doing that on-the-fly may cause
>> stats queries to fail for a short interval.

> Ok, I'll do it that way.

BTW, it might be worth tweaking the stats collector to dump stats
immediately after responding to SIGHUP, just to narrow this window
as much as possible.

[ squint... ] Actually, it looks like the stats collector SIG_IGNores
SIGHUP? That can't be right (any more) can it?

regards, tom lane

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

[pgsql-www] The headache of psqlODBC binary Download

It seems to me that there are too many steps (some are broken) needed
to find the actual download page for the ODBC driver. My fellow
co-workers agree with my concern. I would expect that other ODBC users
feel the same.

Here are the steps needed to find the download page. I'll try to
point out the steps that I consider "broken".

1) www.postgresql.org -> downloads
2) ./download/ -> Software Catalogue
3) ./download/product-categories -> Drivers and interfaces
4) ./download/products/2 -> psqlODBC
5) ./pgfoundry.org/projects/psqlodbc -> no where to go from here

This is the step that I think is broken. At this point, there is no
other link at pgfoundry to take you to the psql ODBC binaries. You
have to read the page and find the text: "Downloads are available on
the PostgreSQL Mirror network at http://www.postgresql.org/ftp/odbc/".
Since the above text is only text and not a hyper-link, you have to
copy the link and paste it into that address-bar of the web-browsers.
Also, pgfoundry creates a bit of confusion since it has its own
download link "http://pgfoundry.org/frs/?group_id=1000125&release_id=364"
and following this link takes the user to a page with nothing to
download.

6) after pasting the link, we are finally at the ODBC download page:
www.postgresql.org/ftp/odbc/

To me 6 steps are a lot, it would be nice if there were fewer steps.
Also to me, pgfoundry is effectively a dead-end having not further
links to the actual download page.

--
Regards,
Richard Broersma Jr.

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

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

[HACKERS] Another refactoring proposal: move stuff into nodes/nodeFuncs.[ch]

So I was starting to implement an exprLocation() function according to
previous discussion
http://archives.postgresql.org/pgsql-hackers/2008-08/msg01131.php
and I wondered where to put it. One idea is next to exprType() in
parser/parse_expr.c, but that seems a bit unsatisfactory because it's
likely to eventually be used in many subsystems besides parser/.
So this resurrected a bee that's been in my bonnet for awhile: the
backend has a number of widely-used functions that provide general-purpose
functionality on node trees, but are scattered around in random places
depending on where the need arose first. Some examples are
exprType()
exprTypmod()
expression_tree_walker()
expression_tree_mutator()
query_tree_walker()
query_tree_mutator()
ISTM all of these belong in a central location under backend/nodes.
You could make weaker cases for some other functions like
contain_vars_of_level(), but these ones are definitely widely used
and pretty general-purpose.

The advantages of doing this would be (a) reduce the number of places
to look in when implementing a new node type; (b) eliminate some
cross-subsystem #inclusions that weaken modularity of the backend.

What I'm thinking of doing is migrating these functions into
nodes/nodeFuncs.h and nodes/nodeFuncs.c, which are existing files that
contain almost nothing useful (I think the functions that are there
now are dead or nearly so).

Any objections? Any nominees for additional functions to put there?

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

[COMMITTERS] pgbuildfarm - client-code: Make sure temp file is unlinked

Log Message:
-----------
Make sure temp file is unlinked

Modified Files:
--------------
client-code:
run_build.pl (r1.96 -> r1.97)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbuildfarm/client-code/run_build.pl.diff?r1=1.96&r2=1.97)

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

[COMMITTERS] pgsql: Make stats_temp_directory PGC_SIGHUP, and document how it may

Log Message:
-----------
Make stats_temp_directory PGC_SIGHUP, and document how it may cause a temporary
"outage" of the statistics views.

This requires making the stats collector respond to SIGHUP, like the other
utility processes already did.

Modified Files:
--------------
pgsql/src/backend/utils/misc:
guc.c (r1.469 -> r1.470)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc.c?r1=1.469&r2=1.470)
pgsql/doc/src/sgml:
config.sgml (r1.187 -> r1.188)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/config.sgml?r1=1.187&r2=1.188)
pgsql/src/backend/postmaster:
pgstat.c (r1.179 -> r1.180)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgstat.c?r1=1.179&r2=1.180)
postmaster.c (r1.561 -> r1.562)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c?r1=1.561&r2=1.562)

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

Re: [HACKERS] temporary statistics option at initdb time

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> Actually, I think maybe not so hard. Attached is a patch that fixes
>> this. It's done by keeping the old filename around. When you change the
>> path, the stats collector will start writing the new file the next time
>> it writes something (which should be max 0.5 seconds later if something
>> is happening). The backends will immediately try to read from the new
>> filename, but if that one is not found, they will switch to reading the
>> old filename. This obviously fails if you change the temp directory
>> twice in less than half a second, but I really don't see a use-case for
>> that...
>
> I think this is introducing complication and race conditions to solve a
> problem that no one will really care about. Just let people change the
> filename at SIGHUP and document that doing that on-the-fly may cause
> stats queries to fail for a short interval.

Ok, I'll do it that way.

//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: [pgsql-es-ayuda] Problemas al realizar un pg_dump y pg_restore

mario ramirez cervera <marioramirezph@gmail.com> escribio:
>hola a todos:
>Estoy realiando la migracion la base de datos de un postgres. Relalizo un
pg_dump -Ft -b gforge >gforge.dump y un restore en una bbdd vacia de la
>siguiente maner a pg_restore -Ft gfore gforge.dump;;;; pero me d aun monto
de errores

Hola
No se que tanto necesitas un dump en formato tar, pero te sugiero que mejor
uses un formato comprimido asi: "pg_dump -U usuario -Fc base_de_datos" y el
restore del dump en una BD vacia lo haces asi: "pg_restore -U usuario -d
base_de_datos". De la forma anterior te incluye los objetos blob, pero creo
que en el formato que estabas haciendo tu backup, no permite incluir blobs.
Saludos.

--------------------
Juan Luis Echeverria
juanluise@gmail.com

--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [HACKERS] IN, BETWEEN, spec compliance, and odd operator names

Dimitri Fontaine <dfontaine@hi-media.com> writes:
> Le lundi 25 août 2008, Gregory Stark a écrit :
>> I'm not sure it's made explicit anywhere in the documentation but those
>> properties *are* what btree operator classes define. You would end up
>> duplicating the same structures (like, LT is meaningless unless you
>> associate it with the corresponding EQUALITY, LE, GT, and GE operators).

> But, IIRC, only in the context of index searches, not at the planner level.

No, that's not true at all. There are lots and lots of places now where
we use btree and/or hash operator classes to reason about the properties
of operators. The most in-your-face example right now is ORDER BY: if
you say ORDER BY x, that's gonna fail outright unless x's type has a
default btree opclass. And if it does, the < member of the opclass is
what defines the sort order.

So we have certainly set some precedents involving using opclasses to
make semantic choices. What's really bothering me is that we aren't
consistent about it: some things like ORDER BY are opclass-driven,
and some things like BETWEEN are operator-name-driven.

regards, tom lane

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

Re: [HACKERS] IN, BETWEEN, spec compliance, and odd operator names

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sun, Aug 24, 2008 at 09:24:23PM -0400, Tom Lane wrote:
>> My own feeling is that we should avoid imputing particular semantics
>> to particular operator names, and so these constructs should always be
>> defined by reference to operators found in a default opclass for the
>> datatype, rather than by specific operator names.

> ISTM the problem is that there's no easy way to refer to "operators
> found in a default opclass", so perhaps we could invent a construct:

> A OPERATOR(btree,2) B

Huh? I don't understand why you think we need to expose this to users.
A user would presumably just write the name of the operator he wants,
if he's writing out a direct operator call.

To me the issue is what we consider IN and BETWEEN and similar
constructs to "mean", which in a datatype world boils down to choosing
which of the datatype's operators to implement the construct with.

> The problem is inferring the type, if A and B are
> different types, which operator class do you use?

Yeah, the cross-type problem occurred to me this morning too. For
instance consider

int4_var BETWEEN int8_var AND numeric_var

The current implementation of BETWEEN acts fairly sanely in this case;
although it ends up choosing two entirely unrelated operators (int48_ge
and numeric_le, which are not in any of the same operator families),
which is not great for subsequent optimization purposes. I'm not sure
about how we'd make a good choice using an opclass-driven approach.
Would we want to insist that both operators are found in the same
family? Perhaps so, because otherwise it's not real clear that you've
created a meaningful range constraint. Yet it's definitely possible
that such a requirement would cause the query to fail where it used to
work (for some value of "work").

Another way to approach it would be to consider the problem as being
similar to overloaded-function resolution, ie think of it as trying
to match an implicit or explicit function between(anyelement,
anyelement, anyelement). But that would fail to take into account
whether there are actually any suitable comparison operators for
whichever common type it chooses.

There's also an issue of not wanting to coerce variables unnecessarily.
In the above example, suppose int4_var has an index. The derived
clause int4_var int48_ge int8_var will be indexable using that index,
whereas int4_var::numeric numeric_le numeric_var won't be, If we coerce
all three inputs to numeric to enforce that we have consistent semantics
on both ends of the range check, then neither end will be indexable;
which seems like a step backwards.

So it's all a bit harder than it looks. Thoughts?

regards, tom lane

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

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

Dave Cramer wrote:

> Well, I go the extra mile and kill any remaing autovac procs
>
> Here are the logs
>
> 2008-08-25 04:00:01 EDT [32276] LOG: autovacuum launcher shutting down
> 2008-08-25 04:00:01 EDT [20526] LOG: autovacuum launcher started

What did you SIGHUP, the launcher or postmaster? You need the latter.
The launcher should exit automatically at that time.

--
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: [pgsql-es-ayuda] Tildes

Gracias por sus respuestas.

Luego de algunos días me di cuenta de la falla, era algo sencillo que no había tomado en cuenta ni había leído.

Como comentaba estoy trabajando con VS Studio 2003 y para acceder a la BD se utiliza la librería Npgsql. La cadena de conexión utiliza por defecto codificación ASCII por esto los formularios no me daban problemas con las tildes sino al momento de extraer datos de la BD con tildes.

La solución fue aumentar en la cadena de conexión lo siguiente "Encoding=UNICODE" y listo! ya con esto todo funciona normal !!

> -----Mensaje original-----
> De: Miguel Beltran R. [mailto:yourpadre@gmail.com]
> Enviado el: lunes, 04 de agosto de 2008 13:16
> Para: Jose Luis Balle
> CC: frank; pgsql-es-ayuda@postgresql.org
> Asunto: Re: [pgsql-es-ayuda] Tildes
>
> En ocasiones los navegadores no toman en cuenta esta instrucción
> porque el servidor web (al menos con Zope) manda también la forma que
> se envían los datos. Yo me di cuenta con firefox dándole con el botón
> derecho del mouse sobre la pagina y en la opción "Ver Información de
> la pagina". Yo la necesitaba como LATIN1 pero la procesaba como
> LATIN15. Que es casi lo mismo pero los códigos extendidos (osea lo
> caracteres raros) cambian.
>
> El día 28 de julio de 2008 13:00, Jose Luis Balle
> <joseluisballe@gmail.com> escribió:
> > la página html tiene la línea:
> > <meta http-equiv="content-type" content="text/html; charset=UTF-8">
> >
> >
> >
> > El día 28 de julio de 2008 14:58, Jose Luis Balle
> > <joseluisballe@gmail.com> escribió:
> >> Lo siento pero no conozco la plataforma que usas.
> >> De todas maneras es sin dudas una cuestion de character set, fijate si
> >> se lo podes explicitar en la conexion a la base de datos.
> >> Exitos!
> >>
> >> El día 28 de julio de 2008 14:50, frank <frankyfa@gmail.com> escribió:
> >>> Estoy desarrollando sobre Visual Studio 2003 con ASP.NET y con servidor Web
> Apache2 sobre Debian Etch.
> >>>
> >>> Los formularios Web están todos codificados con UTF-8 y la base igual charset
> UTF-8.
> >>>
> >>> Si la consulta la hago desde la consola del Debian me sale sin problemas pero si
> la incrusto en el Web Form para que se muestre en alguno de los objetos (combos,
> checks, etc.) las tildes salen erroneas.
> >>>
> >>> La verdad no sé qué otra cosa pueda ser !!
> >>>
> >>>> -----Mensaje original-----
> >>>> De: Jose Luis Balle [mailto:joseluisballe@gmail.com]
> >>>> Enviado el: lunes, 28 de julio de 2008 9:38
> >>>> Para: frank
> >>>> CC: pgsql-es-ayuda@postgresql.org
> >>>> Asunto: Re: [pgsql-es-ayuda] Tildes
> >>>>
> >>>> En que lenguaje estas desarrollando?
> >>>> Sobre que servidor web?
> >>>>
> >>>> El día 28 de julio de 2008 11:26, frank <frankyfa@gmail.com> escribió:
> >>>> > Postgres está sobre Debian Etch.
> >>>> >
> >>>> > Hice la prueba desde consola con psql y los datos salen normales. Es una
> aplicación
> >>>> Web pero las consultas cuyos datos contienen tildes se muestran como
> caracteres
> >>>> raros. La base de datos está codificada con UTF-88.
> >>>> >
> >>>> > En los Web forms ya les cambié la codificación a UTF-8 esto me ayudó para
> las
> >>>> tildes de las etiquetas pero ahora son aquellas que se muestran en los
> DropDownList
> >>>> los cuales dan errores con las tildes.
> >>>> >
> >>>> > Alguna idea?
> >>>> >
> >>>> >> -----Mensaje original-----
> >>>> >> De: Jose Luis Balle [mailto:joseluisballe@gmail.com]
> >>>> >> Enviado el: jueves, 24 de julio de 2008 12:56
> >>>> >> Para: frank
> >>>> >> CC: pgsql-es-ayuda@postgresql.org
> >>>> >> Asunto: Re: [pgsql-es-ayuda] Tildes
> >>>> >>
> >>>> >> Frank,
> >>>> >> si la aplicación es web tenés que fijarte que estás usando el charset
> correcto.
> >>>> >> http://www.cristalab.com/tips/34839/entidades-html-charset-y-
> codificacion-de-
> >>>> >> texto.html
> >>>> >> http://html.conclase.net/w3c/html401-es/charset.html
> >>>> >>
> >>>> >> Desde la consola como ves los datos?
> >>>> >> Sobre que sistema operativo está postgres?
> >>>> >>
> >>>> >>
> >>>> >> 2008/7/24 frank <frankyfa@gmail.com>:
> >>>> >> >
> >>>> >> > Saludos lista,
> >>>> >> >
> >>>> >> > Antes de nada gracias por las respuestas acerca del campo serial, lo
> solucioné
> >>>> >> actualizando el pgadmin aunque al parecer en la versión que tenía también
> estaba,
> >>>> no
> >>>> >> me di cuenta muy bien.
> >>>> >> >
> >>>> >> > Ahora con algunas consultas en las cuales las palabras son con tildes no
> me las
> >>>> >> muestra como debiera aunque la base está con codificación UTF-8
> aparentemente.
> >>>> >> >
> >>>> >> > Alguna idea acerca de esto?
> >>>> >> >
> >>>> >> > --
> >>>> >> > TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
> >>>> >> > http://archives.postgresql.org/pgsql-es-ayuda
> >>>
> >>>
> >>> --
> >>> TIP 4: No hagas 'kill -9' a postmaster
> >>>

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

[pgsql-es-ayuda] tama=?ISO-8859-1?B?8W8=?= del registro a insertar demasiado grande

tengo un error al insertar registros en una tabla que tiene
1101 columnas,
puedo crear la tabla pero al momento de insertar valores me
manda el siguiente mensaje:

ERROR: el registro es demasiado grande: tamaño 9896,
tamaño máximo 8136

Alguien podria ayudarme?, es posible agrandar el maximo
permitido por postgres?.

Gracias de antemano.

att. Juan PAblo
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [BUGS] Latin2 and UTF-8 encoding.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Aug 25, 2008 at 12:29:14PM +0200, kpeter@oszk.hu wrote:
> Dear Sur!
>
> My name is Peter Kovacs and I work at the National Szechenyi Library in
> Hunagry.
> The reason why im writing this letter is the follow:
> We are using Potgresql to store our data. We have UTF-8, LATIN1 and LATIN2
> encoded databases.
> I haven't got any problem with database encoding before, but when I tried
> ro restore our database dumps to a postgresql 8.3.3 server it faild.
> If I tried to create a database with NON-UTF-8 character encoding I got the
> following error:
>
> *SQL error:*
>
> ERROR: encoding LATIN2 does not match server's locale en_AU.UTF-8
> DETAIL: The server's LC_CTYPE setting requires encoding UTF8.

You should set the locale to the right value on the server start script.
Make sure your platform supports it.

> *In statement:*
> CREATE DATABASE "probadb" WITH ENCODING='LATIN2'
>
>
> The problem is that we need both UTF-8, LATIN1 and LATIN2 characters.
>
> My question is: How could I create a database with NON-UTF-8 encoding.

I don't quite understand. You cannot have a database which is UTF-8,
LATIN1 and LATIN2 at the same time.

By the way, this is the wrong list for this question. This list is
dedicated to bugs of PostgreSQL and this is not it.

Feel free to mail pgsql-novice@postgresql.org (or mail me privately if
you think I can be of any help).

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIssHSBcgs9XrR2kYRAiHJAJ49drH5Br/Kak2MoRhPUf1HXVuHoQCdFbPO
dRs9T99wV+aKGqsjJDT1bIQ=
=OKLd
-----END PGP SIGNATURE-----

--
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] Need default password for the user "sa"

2008/8/25 Janardhanachari, Jagadeesha <Jagadeesha.Janardhanachari@fmr.com>:
> What is the password for postgres ?

You filled it on win platforms, on unix platform this password isn't
filled. Try to login as system user postgres, then open local connect
to postgresql and then you can change password.

Pavel
>
>
> _______________________________________
> Jagu
> Fidelity Business Services India.
> Mobile: 9900540134
> Direct : 40334528
> Voip : 8-658-1596
>
>
>
> Any comments or statements made in this email are not necessarily those
> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
> Investments group companies. The information transmitted is intended
> only for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. If you have received this in
> error, please contact the sender and delete the material from any
> computer. All e-mails sent from or to Fidelity Business Services India
> Pvt. Ltd. may be subject to our monitoring procedures
>
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
> Sent: Monday, August 25, 2008 7:47 PM
> To: Janardhanachari, Jagadeesha
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] Need default password for the user "sa"
>
> Hello
>
> PostgreSQL doesn't know username "sa". Same role is "postgres" in
> PostgreSQL.
>
> What is your operation system?
>
> regards
> Pavel Stehule
>
> p.s. it isn't bug, so mail to mailing list general -
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> 2008/8/25 Janardhanachari, Jagadeesha
> <Jagadeesha.Janardhanachari@fmr.com>:
>> HI,
>>
>> I have installed "Postgresql" but don't know the login password for
>> "sa".
>> please help me.
>>
>> _______________________________________
>> Jagu
>> Fidelity Business Services India.
>> Mobile: 9900540134
>> Direct : 40334528
>> Voip : 8-658-1596
>>
>>
>>
>> Any comments or statements made in this email are not necessarily
> those
>> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
>> Investments group companies. The information transmitted is intended
>> only for the person or entity to which it is addressed and may contain
>> confidential and/or privileged material. If you have received this in
>> error, please contact the sender and delete the material from any
>> computer. All e-mails sent from or to Fidelity Business Services India
>> Pvt. Ltd. may be subject to our monitoring procedures
>>
>>
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>
>

--
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] Latin2 and UTF-8 encoding.

On Monday 25 August 2008 13:29:14 kpeter@oszk.hu wrote:
> ERROR: encoding LATIN2 does not match server's locale en_AU.UTF-8
> DETAIL: The server's LC_CTYPE setting requires encoding UTF8.

In 8.3, it no longer works to create databases of different or incompatible
encodings. You need to pick one encoding for all databases and you need to
pick a locale setting to matches that.

If you work for a Hungarian library, you probably don't want a locale setting
en_AU... either. Instead, you probably want hu_HU.utf8.

> The problem is that we need both UTF-8, LATIN1 and LATIN2 characters.

UTF-8 can store all characters that LATIN1 and LATIN2 can encode. You just
need to configure your client encoding correctly and the data sent by the
client will automatically be converted to UTF-8 and back.

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

[GENERAL] Dump/restore with bad data and large objects

By "bad data", I mean a character that's not UTF8, such as hex 98.

As far as I can tell, pg_dump is the tool to use. But it has
serious drawbacks.

If you dump in the custom format, the data is compressed (nice) and
includes large objects (very nice). But, from my tests and the postings of
others, if there is invalid data in a table, although PostgreSQL won't complain and
pg_dump won't complain, pg_restore will strenuously object, rejecting all rows for that
particular table (not nice at all).

If you dump in plain text format, you can at least inspect the dumped
data and fix it manually or with iconv. But the plain text
format doesn't support large objects (again, not nice). While byte arrays are supported, they result in very large dump files.

Also, neither of these methods gets information such as the roles, so
that has to be captured some other way if the database has to be rebuilt
from scratch.

Is my understanding incomplete or wrong? Is there no good solution?

Why isn't there a dumpall that writes in compressed format and allows recovery from bad data?

John

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

Re: [PERFORM] Identifying the nature of blocking I/O

On Fri, Aug 22, 2008 at 7:52 AM, Peter Schuller
<peter.schuller@infidyne.com> wrote:
> Is there currently a way of dumping such information? I.e., asking PG
> "what are backends waiting on right now?".

Unfortunately, not within Postgres itself. The question, "what is the
database waiting on?" is a good one, and one Oracle understood in the
early 90's. It is for that reason that EnterpriseDB added RITA, the
Runtime Instrumentation and Tracing Architecture, to their Advanced
Server product. RITA gives DBAs some of the same information as the
Oracle Wait Interface does regarding what the database is waiting for,
such as locks, I/O, and which relation/block. While it's not as
efficient as DTrace due to Linux's lack of a good high-resolution
user-mode timer, no one has found it to have a noticible overhead on
the throughput of a system in benchmarks or real-world applications.

If you're on a DTrace platform, I would suggest using it. Otherwise,
you can try and use strace/ltrace on Linux, but that's probably not
going to get you the answers you need quickly or easily enough. Until
enough users ask for this type of feature, the community isn't going
to see it as valuable enough to add to the core engine. IIRC,
systemtap is pretty much dead :(

--
Jonah H. Harris, Senior DBA
myYearbook.com

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

Re: [BUGS] Latin2 and UTF-8 encoding.

kpeter@oszk.hu wrote:
> The problem is that we need both UTF-8, LATIN1 and LATIN2 characters.
>
> My question is: How could I create a database with NON-UTF-8 encoding.

Just use UTF-8. UTF-8 can represent all characters that are present in
LATIN1 and LATIN2.

You can still use LATIN1 or LATIN2 as client_encoding if you need to,
even if the server uses UTF-8.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.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: [GENERAL] Issue with creation of Partial_indexes (Immutable?)

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:
> On Mon, 2008-08-25 at 12:23 +0800, Ow Mun Heng wrote:
>> CREATE INDEX idx_d_trh_code_id_partial
>> ON xmms.d_trh_table
>> USING btree
>> (code_id) where code_id not in ('P000','000') and code_id is not null;
>> ERROR: functions in index predicate must be marked IMMUTABLE

> BTW, this is on 8.2.9 Seems to work OK on 8.3.3.

I suppose code_id is varchar or some such?

Try "where code_id::text not in ...". There's an array type coercion
underlying the right-hand side of the NOT IN, and 8.2 had some problems
with correctly identifying the volatility of such coercions.

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: [COMMITTERS] pgsql: Add missing descriptions for aggregates, functions and

Bernd Helmle wrote:
> --On Samstag, August 23, 2008 17:59:55 -0400 Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
>
>> Where's the Windows equivalent to that?
>
> Uhm, good question ;) Do you have a hint, since i never hacked our
> windows built infrastructure?

See src/tools/msvc/Install.pm

It would be good to add a comment on both places that the other one
needs to be updated in conjunction.

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

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

Re: [HACKERS] IN, BETWEEN, spec compliance, and odd operator names

On Monday 25 August 2008 04:24:23 Tom Lane wrote:
> Seems to me that what this boils down to is whether we want to read the
> spec literally ("it says the construct is defined in terms of operators
> named >= and <=, therefore we should do that") or by intent (obviously
> what they *want* is a construct that behaves sensibly in terms of the
> datatype's semantics).

I would be generally in favor of moving more toward a semantics-based approach
instead of a name-based approach.

It would be interesting to see an enumeration of all the parts of the system
that hardcode operator names. Based on that we might gain more insight into
what it would take to go one way or another.

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

Re: [BUGS] Need default password for the user "sa"

Hello

PostgreSQL doesn't know username "sa". Same role is "postgres" in PostgreSQL.

What is your operation system?

regards
Pavel Stehule

p.s. it isn't bug, so mail to mailing list general -
http://www.postgresql.org/mailpref/pgsql-general

2008/8/25 Janardhanachari, Jagadeesha <Jagadeesha.Janardhanachari@fmr.com>:
> HI,
>
> I have installed "Postgresql" but don't know the login password for
> "sa".
> please help me.
>
> _______________________________________
> Jagu
> Fidelity Business Services India.
> Mobile: 9900540134
> Direct : 40334528
> Voip : 8-658-1596
>
>
>
> Any comments or statements made in this email are not necessarily those
> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
> Investments group companies. The information transmitted is intended
> only for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. If you have received this in
> error, please contact the sender and delete the material from any
> computer. All e-mails sent from or to Fidelity Business Services India
> Pvt. Ltd. may be subject to our monitoring procedures
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [pgsql-www] [BUGS] BUG #4279: Bad codepage in our web-site

On Sat, Aug 23, 2008 at 03:45:09PM +0300, Peter Eisentraut wrote:
> The bottom range of Unicode codepoints is the same as ISO 8859-1, but not the
> bottom range of UTF-8 encoded bytes.

Oh, right, silly me. Well, then, those'll need fixing, too.

I'm not sure I see why this is such a problem. It's obvious that if
you're going to support on-the-fly internationalisation on your site
(as postgresql.org does), then you need to pick the one encoding that
allows you to do that.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.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: [pgsql-es-ayuda] Afinamiento de la Base de Datos. OT

> >
> > Igualmente Jaime y apesar de haber comprendido bien lo
> que debia hacer por que tu material es muy claro,
> >
>
> gracias
>
> > me gustaria poder ahondar mas en los conceptos y el
> porque de estos valores, donde podria encontrar material que
> sea
> > didactico como tu presentacion y profundice el tema.
> >
>
> La mayoria salio de aqui:
> http://wiki.postgresql.org/wiki/Performance_Optimization te
> diria cual
> de todos pero en realidad es la suma de todos
>
> >
> > Espero no lo consideres una mala critica
>
> por supuesto que no... y en cuanto pueda hacer un poco de
> tiempo para
> ello tratare de añadir algunos enlaces de referencias
> (aunque tendre
> que volver a chequear cuales articulos fueron los
> efectivos, pero
> todos son de la wiki eso si)
>

Bueno el que esta agradecido doblemente soy yo.

Resulta que mientras la gran mayoria esta preocupado en sacarle rendimiento a PostgreSQL en grandes servidores y enormes bases de datos yo estoy preocupado por hacer que postgresql sea la BD ideal para aplicaciones de escritorios en PYMES.

PYMES latino americanas, donde por ejemplo SBS de Microsoft queda grande, pues las PMES del norte son grandes empresas en nuestros paises y las pymes latino americanas no existen en la politica de los que desarrollan software alla.

Entonces aca nos encontramos con PYMES donde se piensa que gastar en un servidor para una lan de 10 a 15 puestos, "donde el servidor no hace nada", es solo un mal, muy pocas veces necesario.

En una sola empresa puede poner un Linux con un postgresql y un servidor IBM, incluso en uno tuve que poner un windows 2000, "por si por algun motivo tenemos que usarla", y el servidor por mucho tiempo fue un compaq p III 500 mhz y 512 mb de ram, sustituido por un P4 intel con xp con 1gb de ram y 3.0 mhz y un disco sata.

Pero si bien estas pymes son chicas, manejan volumenes de informacion muy importantes.

De ahi sale mi slogan de que PostgreSQL trabaja en el ambiente del software propietarios con condiciones de hardware donde su propia base de datos propietaria no puede correr.

Esta situacion ha hecho que en el proceso he tenido que cambiar muchas consultas y funciones incluso modificar el software de aplicacion para obtener mejor rendimiento, cosa que voy a seguir haciendo.

Con el afinamiento de la base de datos obtengo ahora una posibilidad mayor de dar una mejor respuesta, mientras sigo optimizando mi software.

Obtengo ademas mas confianza en lo que vengo predicando, en el sentido de que PostgreSQL puede seguir el desarrollo de una empresa desde pyme a gran empresa, escalando sin tener que migrar en el futuro, solo haciendo inversiones de Hardward que a la postre es lo mas barato y menos traumatico.

Se que otras soluciones se pueden implementar con otros sgdb para las pymes, pero ninguna como postgresql puede asegurar que si la empresa quiere crecer el sgdb le va a poder seguir el paso hasta donde quiera y pueda llegar.

El afinamiento o tunning, son una gran solucion a estos grandes problemas, tanto si trabajamos con grandes volumenes y complicadas implementaciones, como si trabajamos en las mas pauperrimas condiciones.

De hecho el afinamiento debe ser considerado una obligacion para casi cualquier desarrollo, puesto que bien hechho lo unico que aporta es mejor rendimiento y mayor satisfaccion.

Atte.
Gabriel Colina

____________________________________________________________________________________
Yahoo! MTV Blog & Rock &gt;¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV! Participa aquí http://mtvla.yahoo.com/
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

Andrew Sullivan wrote:
> On Mon, Aug 25, 2008 at 11:34:00AM +0200, Tino Wildenhain wrote:
>> (e.g. pg_dump | pg_restore to avoid wasting space 2 times)
>> then run slony to permanently update the last bits
>
> If you're going to run slony, then the pg_dump|pg_restore step is
> completely wasted. Slony will restore all the data again. You do
> need to run pg_dump -s, of course.

good point :-)

Regards
Tino

Re: [GENERAL] Easy upgrade on Cpanel *without* downtime

On Mon, Aug 25, 2008 at 11:34:00AM +0200, Tino Wildenhain wrote:
> (e.g. pg_dump | pg_restore to avoid wasting space 2 times)
> then run slony to permanently update the last bits

If you're going to run slony, then the pg_dump|pg_restore step is
completely wasted. Slony will restore all the data again. You do
need to run pg_dump -s, of course.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

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

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

On 24-Aug-08, at 10:17 PM, Alvaro Herrera wrote:

> Hi Dave,
>
> Dave Cramer wrote:
>> I'd like to stop autovac by changing the conf file then sending the
>> server a HUP
>>
>> This "appears" to work, the logs show autovac terminated by
>> administrative command. Then a few minutes later I see a vacuum
>> process
>> spawned.
>>
>> Is it possible that there are timers that aren't being properly
>> terminated here ?
>
> I wonder whether there are tables that need an emergency vacuum (i.e.
> they have reached the freeze horizon). What version are you running
> anyway?
>
version 8.3 and doubtful.

Since a number of people actually do this it would seem like a better
way to temporarily suspend autovac should be on the todo ?

Dave
> --
> 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: [HACKERS] can't stop autovacuum by HUP'ing the server

On 24-Aug-08, at 10:12 PM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>> I'd like to stop autovac by changing the conf file then sending the
>> server a HUP
>
> Uh ... why should that stop an autovac already in progress? I'd
> only expect it to affect future launches.
>

Well, I go the extra mile and kill any remaing autovac procs

Here are the logs

2008-08-25 04:00:01 EDT [32276] LOG: autovacuum launcher shutting down
2008-08-25 04:00:01 EDT [20526] LOG: autovacuum launcher started
2008-08-25 04:00:01 EDT [21404] FATAL: terminating autovacuum
process due to administrator command
2008-08-25 04:00:01 EDT [23418] FATAL: terminating autovacuum
process due to administrator command
2008-08-25 04:00:01 EDT [21703] FATAL: terminating autovacuum
process due to administrator command

So now that I've read this why would the launcher start again ?

Dave
> 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: [COMMITTERS] pgsql: Add missing descriptions for aggregates, functions and

--On Samstag, August 23, 2008 17:59:55 -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Where's the Windows equivalent to that?

Uhm, good question ;) Do you have a hint, since i never hacked our windows
built infrastructure?

--
Bernd

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