PostgreSQL

Tuesday, June 10, 2008

Re: [HACKERS] Overhauling GUCS

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> Oh, and wal_buffers, the default for which we should just change if it
>> weren't for SHMMAX.
>
> Uh, why? On a workload of mostly small transactions, what value is
> there in lots of wal_buffers?

None. But there's also little to no harm in having a higher setting; at
worst you waste a few megabytes of memory. Besides, most databases are
initialized from some outside source in the beginning, and data loading
does benefit from a higher wal_buffers setting.

Ideally, of course, there would be no wal_buffers setting, and WAL
buffers would be allocated from shared_buffers pool on demand...

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

[pgsql-it-generale] PGDay 2008: Call For Papers

Il 17 e 18 Ottobre 2008 si svolegrà a Prato la seconda giornata nazionale
dedicata a PostgreSQL (PGDay 2008). E' possibile partecipare presentando un
intervento, una esperienza, un caso d'uso o attività di ricerca legata a
PostgreSQL semplicemente aderendo alla Call For Papers disponibile
all'indirizzo http://www.pgday.org/it/call4papers.

Per sottomettere il proprio intervento è necessario compilare l'apposito
modulo che si trova all'indirizzo web http://www.pgday.org/it/call4papers e
inviarlo tramite e-mail a callforpapers@pgday.org, specificando:

* Titolo dell'intervento
* Autore/i
* Descrizione dell'intervento
* Licenza con la quale viene presentato il materiale

oltre ad indicare quale fra le seguenti sessioni sono di interesse per
l'intervento stesso:

- Beginners
- Intermedio
- Avanzato
- Teoria
- Enterprise

## Date Importanti ##

* 21 Luglio 2008 invio del proprio contributo;
* 31 Agosto 2008 notifica di accettazione;
* 8 Settembre 2008 invio della versione revisionata nel caso di
accettazione condizionata;
* 15 Settembre 2008 invio della versione finale del contributo, per il suo
inserimento negli atti della conferenza

Per maggiori informazioni vedere il sito web ufficiale

http://www.pgday.org/it/ e in particolare la pagina

http://www.pgday.org/it/call4papers

Saluti,
Luca

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

Re: [PATCHES] Refactoring xlogutils.c

Tom Lane wrote:
> "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
>> Attached is an updated version of my patch to refactor the
>> XLogOpenRelation/XLogReadBuffer interface, in preparation for the
>> relation forks patch, and subsequently the FSM rewrite patch.
>
> The code motion in md.c looks fairly bogus; was that a copy-and-paste
> error?

This one?

> *** a/src/backend/storage/smgr/md.c
> --- b/src/backend/storage/smgr/md.c
> ***************
> *** 208,216 **** mdcreate(SMgrRelation reln, bool isRedo)
> char *path;
> File fd;
>
> - if (isRedo && reln->md_fd != NULL)
> - return; /* created and opened already... */
> -
> Assert(reln->md_fd == NULL);
>
> path = relpath(reln->smgr_rnode);
> --- 208,213 ----

That's intentional. That check has been moved to smgrcreate(), so that
it's done before calling TablespaceCreateDbSpace(). The reason for that
is that smgrcreate() is now called for every XLogReadBuffer()
invocation, so we want to make it exit quickly if there's nothing to do.

On second look though, I think we should actually leave that check in
mdcreate(). Even though it'd be dead code since we do the same check in
smgrcreate already, removing it changes the semantics of mdcreate():
it'd no longer be acceptable to call mdcreate() if the file is open already.

> Otherwise it looks pretty sane, but I have one stylistic gripe:
> I'm dubious about your willingness to assume that pfree() is enough for
> getting rid of a fake relcache entry. Relcache entries are complex
> beasts and it may not continue to work to do that. It's also possible
> that we'd have additional resources attached to them someday. So
> I think it'd be worth having a FreeFakeRelcacheEntry() routine to
> localize the knowledge of how to get rid of them.

Yeah, I guess you're right.

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

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

Re: [ADMIN] Disk Space issue

Hi,
I think you have a lot DML operation in your database and you are not
vacuuming your database . So try to execute command vacuum analyze
<<table name >>; in your schema where you are facing problem.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Devendra Singh
Rawat
Sent: Wednesday, June 11, 2008 5:07 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Disk Space issue

Hi,

We are using PostgreSQL 7.4.5 and facing issues with increasing disk
space.

Here is some information about our configuration.

The data dump size is around 3 GB. And it is occupying around 120GB on
the disk. When we recreate the database from data dump it takes around
3.5GB of disk space.
But the database size increases by 1 GB daily though actual data added
maybe in KB. We are not able to decipher as to what is causing this
increase.

Suggestions on the likely problems will be appreciated.

Regards,
Devendra

**************** CAUTION - Disclaimer ***************** This e-mail
contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the
use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further,
you are not to copy, disclose, or distribute this e-mail or its contents
to any other person and any such actions are unlawful. This e-mail may
contain viruses. Infosys has taken every reasonable precaution to
minimize this risk, but is not liable for any damage you may sustain as
a result of any virus in this e-mail. You should carry out your own
virus checks before opening the e-mail or attachment. Infosys reserves
the right to monitor and review the content of all messages sent to or
from this e-mail address. Messages sent to or from this e-mail address
may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

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

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

Re: [PATCHES] Refactoring xlogutils.c

Teodor Sigaev wrote:
>> - For the routines that need a fake, or lightweight, Relation struct
>> anyway, there's a new function called
>> XLogFakeRelcacheEntry(RelFileNode), that returns a palloc'd Relation
>> struct.
>
> Is that fake structure applicable for ReadBuffer()?

Yes.

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

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

Re: [GENERAL] encoding confusion

Sim Zacks wrote:
> We originally tested it on mysql and now we are migrating it
> to postgresql.
>
> The messages are stored in a longblob field on mysql and a bytea field
> in postgresql.
>
> I set the database up as UTF-8, even though we get emails that are not
> UTF encoded, mostly because I didn't know what else to try that would
> incorporate all the possible encodings. Examples of 3 encodings we
> regularly receive are: UTF-8, Windows-1255, ISO-8859-8-I.

[...]

> It would not transfer through the dbi-link, so I wrote a python script
> (see below) to read a row from mysql and write a row to postgresql
> (using pygresql and mysqldb).
> When I used pygresql's escape_bytea function to copy the data, it went
> smoothly, but the data was corrupt.
> When I tried the escape_string function it died because the data it was
> moving was not UTF-8.
>
> I finally got it to work by defining a database as SQL-ASCII and then
> using escape_string worked. After the data was all in place, I pg_dumped
> and pg_restored into a UTF-8 database and it surprisingly works now.

It's very dificult to know what exactly happened unless you have some
examples of a byte sequence that illustrates what you describe:
How it looked in MySQL, how it looked in your Python script, what you
fed to escape_bytea.

What client encoding did you use in your Python script?

Yours,
Laurenz Albe

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

Re: [pgsql-advocacy] Booth Swag

On Tue, Jun 10, 2008 at 8:47 PM, Michael Alan Brewer <mbrewer@gmail.com> wrote:
> I'd love to see oval, Euro-style stickers with either "PG", "PgSQL",
> or "psql" on them; those, plus temporary tattoos of our elephant logo
> should work.

Ooh, I like that sticker idea. We had elephant logo stickers at LFNW.

> (I'm also teaching myself how to make balloon elephants, in case I get
> to work booth duty. ;) )

Love it! If you're making balloon elephants you'll "get" to do booth
duty, all right. ;)

gabrielle

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

[pgus-board] Resolution Views

/resolutions
* All published resolutions <-- Viewable by all authenticated
/resolutions/unpublished
* All unpublished resolutions <-- Viewable only by board
/resolutions/approved
* All approved resolutions <-- Viewable by all authenticated
/resolutions/failed
* All failed resolutions <-- Viewable by all authenticated

Let me know what you think and I will publish it to the site.

Sincerely,

Joshua D. Drake

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

Re: [GENERAL] encoding confusion

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

The data in the longblob field might be text, which could be causing the
confusion. For example, when I look at the data in the longblob field, I
see /n for a newline and when I look at the bytea it is 012.

I can only tell you what happened in the client end, in terms of
corruption. I am using the Thunderbord client. When I clicked on a
message, it didn't show the data and when I looked at the headers, it
was just a big mess. I'm guessing that somehow the newlines didn't work
and the headers and message were overlaid on top of each other.

Richard Huxton wrote:
> Your Python script seems to think it's dealing it's dealing with text
> rather than a stream of bytes too. I'm not a Python programmer, but I'd
> guess it's treating one of the database fields (either MySQL or
> PostgreSQL) as text not bytes. You'll need to check the docs for
> binary-data handling in your Python libraries.
>
> I'm puzzled as to how the data was corrupted with escape_bytea() - I
> can't imagine it would be that difficult for the library to get right.
> I'd be suspicious that the source data wasn't what I thought it was.

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

iEYEARECAAYFAkhPZD8ACgkQjDX6szCBa+pKkwCfXwzX9iZJl6OS7gji9WXczfy7
T7UAn0sCr+Ut5lbZzXQizC8V3P/Irwrh
=/9bo
-----END PGP SIGNATURE-----

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

Re: [SQL] Update and trigger

Medi Montaseri wrote:
> Hi,
>
> I need to increament a counter such as myTable.Counter of type integer
> everytime myTable.status a boolean column is updated. Can you help me
> complete this...
>
> create trigger counter_trigger after update on myTable.counter
> execute procedure 'BEGIN statement; statement; statement END'
>
> Q1- how do I narrow the event to "update on a column not a row"

Use a row-level trigger and test to see if the column of interest has
been altered. Eg:

IF new.fieldname IS DISTINCT FROM old.fieldname THEN
-- Do the work
END IF;

> Q2- can I just provide an inline procedure for the execute

No, at present you must create a function that returns TRIGGER and then
use that as the target to execute. At least as far as I know.

--
Craig Ringer

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

Re: [SQL] Update and trigger

am Tue, dem 10.06.2008, um 18:45:51 -0700 mailte Medi Montaseri folgendes:
> Hi,
>
> I need to increament a counter such as myTable.Counter of type integer
> everytime myTable.status a boolean column is updated. Can you help me complete
> this...
>
> create trigger counter_trigger after update on myTable.counter
> execute procedure 'BEGIN statement; statement; statement END'

much simpler, use a RULE instead a TRIGGER like my example:

Suppose, i have a table called foo, it contains now:

test=# select * from foo;
i
---
1
2
(2 rows)

I create a sequence and a RULE:

test=*# create sequence foo_counter;
CREATE SEQUENCE
test=*# create or replace rule foo_update as on update to foo do also select nextval('foo_counter');
CREATE RULE

And now i do a update on foo:

test=*# update foo set i=2;
nextval
---------
1
(1 row)

test=*# update foo set i=3;
nextval
---------
2
(1 row)

test=*# select currval('foo_counter');
currval
---------
2
(1 row)

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

Re: [HACKERS] why copy tuple in the end of trigger when nothing changed in NEW OLD record variable

Tom Lane,

er, your explanation is reasonable.

But at least the comment
if (newtuple != tuple) /* modified by Trigger(s) */
{
.....
is likely to misdirect us.

It took me a few hours to figure it out. :-(

======= 2008-06-10 23:43:00 In your letter you say:=======

>"billy" <billywq@163.com> writes:
>> I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.
>
>I don't especially see the point of adding extra complexity here.
>AFAICS you are talking about avoiding one or two palloc/pfree
>cycles, which is surely down in the noise compared to the cost of
>calling a plpgsql trigger.
>
> 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

　　　　　　　　billy
　　　　　　　　billywq@163.com
　　　　　　　　　　2008-06-11

--
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-advocacy] Booth Swag

Ciao Gabrielle,

> - mugs

Yes. Definitely.

> - pens (good ones...I hate trying to write with a sucky pen.)

We have a stock for PGDay here already. The blue pens that we organised
for PGDay last year and that ITPUG made in February (a few hundreds were
brought at FOSDEM).

> - flyer: "talking points" PostgreSQL vs MySQL

Yes - more in a way to explain the differences (objectively) rather than
put it in a "fight situation".

Ciao,
Gabriele

--
Gabriele Bartolini: Open source programmer and data architect
Current Location: Prato, Tuscany, Italy
Associazione Italian PostgreSQL Users Group: www.itpug.org
gabriele.bartolini@gmail.com | www.gabrielebartolini.it
"If I had been born ugly, you would never have heard of Pelé", George Best
http://www.linkedin.com/in/gbartolini

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

Re: [pgsql-advocacy] Booth Swag

Ciao Josh,

Josh Berkus ha scritto:
> T-shirts

Yes. We have pretty much finished last year's t-shirts. As soon as we
start the sponsorship campaign for PGDay (it is taking longer than
expected to review the contract), depending on budget ITPUG will
evaluate the chance to make a few t-shirts:

1) a standard blue t-shirt (maybe a slightly better quality)
2) a standard white t-shirt for female (like last year)
3) a black one for us "nerds" :)

We could evaluate also to get polo shirts.

> Stickers??

ITPUG just made 2k stickers. They are really cool. We sent a few of them
in Greece.

> Other stuff
> Brochure holder (need to buy)

Yes. That is one. I believe that if we get somebody to design it, we
could print several of copies here n Italy for PGDay and then reuse them
for the European Group. It is just that until we start getting sponsors,
we can't decide.

> Donation Box

Exactly. I wanted so much a donation box in the shape of an elephant!

In terms of flyers, I believe a MySQL to PostgreSQL flyer would be very
useful.

Ciao,
Gabriele

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

[ODBC] what gives: SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE

Server logs below. Notice how an error raised in a connection to the
proj02u20411 database forces the transaction in the instrumentation
database to rollback.
Can anyone explain why connections that as far as PG should conserned
are unrelated are actually interferring with one another?
--------------------------------------------------------------------------------

2008-06-10 15:04:51instrumentation LOG: statement:
BEGIN;
INSERT INTO Dev.Tagables( devicecode )
VALUES ( E'PROCESS SWITCH' )
RETURNING tagableid;
2008-06-10 15:04:52instrumentation LOG: statement:
INSERT INTO Dev.Instrumentassemblies( partnbr,
equipmentnbr, tagableid,
devicecode, manufacture,
fundedby, purchasedby )
VALUES( E'1140-181-201-D', E'RW-0184-402.02',
1, E'PROCESS SWITCH',
E'HAMON RESEARCH COTTRELL',
E'02U20410', NULL );
2008-06-10 15:04:52instrumentation LOG: statement:
INSERT INTO Dev.SignalingDevices( tagableid, devicecode )
VALUES( 1, E'PROCESS SWITCH' );
2008-06-10 15:04:52instrumentation LOG: statement:
INSERT INTO Dev.Switches( tagableid, devicecode )
VALUES( 1, E'PROCESS SWITCH' );
2008-06-10 15:04:52instrumentation LOG: statement:
INSERT INTO Dev.Loopcontainment( endprojectnbr, area,
pcode, loopnbr, loopsuf,
tagableid, instrumentcode,
instrumentsuff, startprojectnbr )
VALUES( E'AS-BUILT', 84, E'L', 3117, E'', 1, E'LSL',
E'', E'02U20410' );
2008-06-10 15:04:52instrumentation LOG: statement:
INSERT INTO Dev.Tags( instrumentcode, instrumentsuff,
area, loopnbr, hasbackup,
endprojectnbr,
"p&idsection" )
VALUES( E'LSL', E'', 84, 3117, '0', E'AS-BUILT', 14 );
2008-06-10 15:04:52proj02u20411 LOG: statement:
SELECT "rid" ,"mid" ,"pid"
FROM "dev"."rtl_tagnr_mat_purch"
WHERE ("rid" = NULL)
2008-06-10 15:04:52proj02u20411 LOG: statement:
SELECT "itid" ,"itcode"
FROM "dev"."insttype"
WHERE ("itid" = 58)
2008-06-10 15:04:52proj02u20411 LOG: statement:
SELECT "vid" ,"name"
FROM "dev"."vendor"
WHERE ("vid" = NULL)
2008-06-10 15:04:52proj02u20411 LOG: statement:
SELECT "cid" ,"designation"
FROM "equ"."component" "CA"
WHERE ("cid" = NULL)
2008-06-10 15:04:52proj02u20411 LOG: statement:
SELECT "sid" ,"sectno"
FROM "docs"."section"
WHERE ("sid" = 40)
2008-06-10 15:04:52proj02u20411 LOG: statement:
SELECT "cid" ,"designation"
FROM "equ"."component" "CB"
WHERE ("cid" = NULL)
2008-06-10 15:04:52instrumentation ERROR: syntax error at or near
"SELECT" at character 16
2008-06-10 15:04:52instrumentation STATEMENT: SELECT INVALID SELECT
STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE
2008-06-10 15:04:52instrumentation LOG: statement: ROLLBACK

--
Regards,
Richard Broersma Jr.

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

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

Re: [pgsql-advocacy] Booth Swag

On Tue, Jun 10, 2008 at 6:19 PM, Selena Deckelmann
<selenamarie@gmail.com> wrote:
> STICKERS! I'm thinking glossy laptop stickers in various sizes with
> the elephant on them. I made some for LFNW and they were a hit.

I'd love to see oval, Euro-style stickers with either "PG", "PgSQL",
or "psql" on them; those, plus temporary tattoos of our elephant logo
should work.

(I'm also teaching myself how to make balloon elephants, in case I get
to work booth duty. ;) )

---Michael Brewer
mbrewer@gmail.com

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

Re: [HACKERS] why copy tuple in the end of trigger when nothing changed in NEW OLD record variable

"billy" <billywq@163.com> writes:
> I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.

I don't especially see the point of adding extra complexity here.
AFAICS you are talking about avoiding one or two palloc/pfree
cycles, which is surely down in the noise compared to the cost of
calling a plpgsql trigger.

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: [pgus-board] Final changes to send to Attorney

I think that's all fine; I can't think of anything else to add, as I
didn't think the proposed changes to the mission statement (by LAPUG)
passed consent here.

Might be wrong, though; Selena, am I forgetting anything here?

---Michael Brewer
mbrewer@gmail.com

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

Re: [pgsql-es-ayuda] OT: Base de datos de paises de todo el mundo

2008/6/10 Marcos Saldivar <baron.rojo.cuerdas.de.acero@gmail.com>:
> El día 10 de junio de 2008 21:49, Julio Cesar Rodriguez Dominguez
> <jurasec@gmail.com> escribió:
>> Este SQL contiene una base de datos con "casi" todas las localidades
>> del mundo agrupadas en regiones y países. Cada registro está en ocho
>> idiomas (Ruso, Inglés, Alemán, Francés, Castellano, Italiano,
>> Portugués y Chino). Contiene un total de 214 países, 1997 regiones y
>> 129602 localidades. Contiene también la posición devuelta por la API
>> de Google Maps para latitud y longitud y si esa posición es exacta o
>> no.
>>
>> FUENTE:
>> http://meneame.net/story/base-datos-paises-regiones-localidades-todo-mundo-geoposicionados
>
> ufffffffffff tiene los datos con códigos html, en vez de ingresar
> ñandú ingresaban ñandú ve vuelvo mono pasando los todos
> a su correspondiente, de todas formas remplace los siguientes
> caracteres:
>
> á/á
> á/á
> é/é
> í/í
> ó/ó
> ú/ú
> ñ/ñ
> ü/ü
> Á/Á
> É/É
> Í/Í
> Ó/Ó
> Ú/Ú
> Ñ/Ñ
> ã/ã
> ç/ç
> ô/ô
> ë/ë
> ï/ï
> ö/ö
> ü/ü
>
> y quien quiera probarlo en PostgreSQL aqui dejo el script modificado:
>
> http://rapidshare.com/files/121587405/paises.pgsql.sql.bz2.html

Me pregunto que paso con los datos de la tabla de paises...

>
> saludos
>
> ps: ojo el original es de MySQL
>

--
:: God bless you, every day and every night ::
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?

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

RE: [pgsql-es-ayuda] Como puedo eliminar ' de una cadena

Podes incluir esta funcion:

translate(string text, from text, to text)
text
(Any character in string that matches a character in the from set is
replaced by the corresponding character in the to set )
translate('12345', '14', 'ax') a23x5

----------------------------------------------------------------------------
------------------------------------------
Tu ejemplo seria de la siguiente manera:

base=# select translate('tu,cadena,completa',',','_') as conversion;
conversion
--------------------
tu_cadena_completa
(1 fila)
----------------------------------------------------------------------------
------------------------------------------

Espero te sea util.

Atte.
___________________________
ASC Daniel Ferrer
Gerente de Sistemas - CTD SRL
Rosario - Argentina
mailto:sistemas@ctd.com.ar
___________________________

-----Mensaje original-----
De: pgsql-es-ayuda-owner@postgresql.org
[mailto:pgsql-es-ayuda-owner@postgresql.org] En nombre de
badillo@cav.desoft.cu
Enviado el: Martes, 10 de Junio de 2008 08:49 p.m.
Para: pgsql-es-ayuda@postgresql.org
Asunto: [pgsql-es-ayuda] Como puedo eliminar ' de una cadena

Saludos,

Cómo puedo eliminar o sustituir el caracter ', de un acadena para poder
hacer un insert o update.

ejemplo, una de las tablas que tengo tomar datos de ella, en los campos de
tipo texto tienen en su contenido el caracter ', y cuando trato de hacer
mi cadena para ejecutarla me emite un error.

como puedo decirle al valor que trae el campo que omita ese caracter.

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

No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 270.2.0/1494 - Release Date: 2008-06-10
07:22 a.m.

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 270.2.0/1494 - Release Date: 2008-06-10
07:22 a.m.

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

Re: [pgsql-es-ayuda] SELECT NOW()

herreraedgardo@gmail.com wrote:
> hola lista...
>
> postgres 8.1.... sistema operativo Windows XP...
>
> la siguiente sentencia
>
> SELECT now();
>
> me devuelve una fecha y hora que no corresponde al sistema... me
> devuelve un formato de fecha con un margen de error de aproximadamente
> 5 horas.
> la misma sentencia la he ejecutado en dos maquinas distintas. el
> sistema operativo tiene la misma hora del BIOS de la pc.
>
> Preguntas:
>
> de donde obtiene postgre la fecha que devuelve la sentencia SELECT
> now();?
> como obtengo la hora del bios o del sistema operativo?
>
Now() es equivalente a current_timestamp() y te regresa el horario y la
fecha en base al uso horario especificado, no recuerdo en que parte de
postgres esta esto, pero puedes especificarlo manualmente de la
siguiente forma:

SELECT now() AT TIME ZONE interval '-06:00';

El uso horario -06 es el que me corresponde en la ciudad de mexico,
prueba poniendolo asi y vez si esta mal configurado tu uso horario (time
zone) en tu sistema. Aunque mucho ojo con el horario de verano.

--
Saludos,

Julio Cesar Sánchez González.

--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.

www.sistemasyconectividad.com.mx

http://darkavngr.blogspot.com/

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

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

[HACKERS] why copy tuple in the end of trigger when nothing changed in NEW OLD record variable

Hi pghackers:

version 8.3.0 in function plpgsql_exec_trigger.

in a trigger, if NEW is returned as the result and we do nothing to NEW.

for example, we have a table like this:

create table test (a int);
insert into test values(1);

and a trigger like:

create or replace function test_trigger() returns trigger as $$
begin
return new;
end; $$language plpgsql;

create trigger before_update_test before update
on test for each row execute procedure test_trigger();

in this trigger, we don't change the value of NEW.

than execute:

update test set a = 3;

after execution:

/* Copy tuple to upper executor memory */
rettup = SPI_copytuple((HeapTuple) (estate.retval));

we come to function ExecUpdate():

HeapTuple newtuple;
newtuple = ExecBRUpdateTriggers(estate, resultRelInfo,
tupleid, tuple);

Since the trigger's return value is copied to another memory address, the newtuple is impossible equal to the oldtuple.

so the following condition:

if (newtuple != tuple) /* modified by Trigger(s) */
{

is FALSE for ever.

I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.

　　　　　　　　billy
　　　　　　　　billywq@163.com
　　　　　　　　　　2008-06-11

--
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] OT: Base de datos de paises de todo el mundo

> y quien quiera probarlo en PostgreSQL aqui dejo el script modificado:
>
> http://rapidshare.com/files/121587405/paises.pgsql.sql.bz2.html

Que bien.

>
> saludos
>
> ps: ojo el original es de MySQL
>

--
:: God bless you, every day and every night ::
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] SELECT NOW()

1.- lista tu zona horaria.
postgres=# show timezone;
TimeZone
-----------
localtime
(1 fila)

2.- Si no te sale "localtime" pasa al paso 3
postgres=# select now();
3.- cambia el parametro de time zone
postgres=# SET TIME ZONE DEFAULT;
4.- saca la fecha y hora actual
postgres=# select now();

Ojo si esto corrige el problema esta solución es temporal mientras este la instancia BD postgres corriendo, para hacerlo fijo cambia en el archivo de configuración de postgresql.conf

Éxitos.
Atte:
JareD

El día 10 de junio de 2008 19:47, <herreraedgardo@gmail.com> escribió:

hola lista...

postgres 8.1.... sistema operativo Windows XP...

la siguiente sentencia

SELECT now();

me devuelve una fecha y hora que no corresponde al sistema... me devuelve un formato de fecha con un margen de error de aproximadamente 5 horas.
la misma sentencia la he ejecutado en dos maquinas distintas. el sistema operativo tiene la misma hora del BIOS de la pc.

Preguntas:

de donde obtiene postgre la fecha que devuelve la sentencia SELECT now();?
como obtengo la hora del bios o del sistema operativo?

Re: [pgsql-www] Git access?

On Tue, Jun 10, 2008 at 03:12:11PM -0700, David Fetter wrote:
> Why not? They can be revoked any time.

Because the people who have to maintain them with the current manual
system say they're not willing to do so, because it costs them too
much time.

Adding more administrators to that pile does not help the problem, it
merely moves the problem of automation tools to another place.

--
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] OT: Base de datos de paises de todo el mundo

El día 10 de junio de 2008 21:49, Julio Cesar Rodriguez Dominguez
<jurasec@gmail.com> escribió:
> Este SQL contiene una base de datos con "casi" todas las localidades
> del mundo agrupadas en regiones y países. Cada registro está en ocho
> idiomas (Ruso, Inglés, Alemán, Francés, Castellano, Italiano,
> Portugués y Chino). Contiene un total de 214 países, 1997 regiones y
> 129602 localidades. Contiene también la posición devuelta por la API
> de Google Maps para latitud y longitud y si esa posición es exacta o
> no.
>
> FUENTE:
> http://meneame.net/story/base-datos-paises-regiones-localidades-todo-mundo-geoposicionados

ufffffffffff tiene los datos con códigos html, en vez de ingresar
ñandú ingresaban ñandú ve vuelvo mono pasando los todos
a su correspondiente, de todas formas remplace los siguientes
caracteres:

á/á
á/á
é/é
í/í
ó/ó
ú/ú
ñ/ñ
ü/ü
Á/Á
É/É
Í/Í
Ó/Ó
Ú/Ú
Ñ/Ñ
ã/ã
ç/ç
ô/ô
ë/ë
ï/ï
ö/ö
ü/ü

y quien quiera probarlo en PostgreSQL aqui dejo el script modificado:

http://rapidshare.com/files/121587405/paises.pgsql.sql.bz2.html

saludos

ps: ojo el original es de MySQL
--
TIP 8: explain analyze es tu amigo

Re: [INTERFACES] pqlib large object error

On Tue, 2008-06-10 at 21:24 -0400, Tom Lane wrote:
> Edward Amsden <amsden_linux@embarqmail.com> writes:
> > Thanks for all your help. I'm somewhat amateur with C and even less
> > experienced with PostgreSQL (I'm a recent MySQL convert). Even after
> > some googling, I have no idea what this BEGIN block is. Is it C or is it
> > SQL? That probably makes me a n00b. :-|
>
> You need a SQL "BEGIN" (or "START TRANSACTION") command and a SQL
> "COMMIT" (or "END") command around anything that involves having a
> large object descriptor open. It might help to look at the sample
> program here:
>
> http://www.postgresql.org/docs/8.3/static/lo-examplesect.html
>
> It's not amazingly well commented :-(, but the lines
>
> res = PQexec(conn, "begin");
> res = PQexec(conn, "end");
>
> are *not* optional.
Thanks for your help with this. I successfully wrote about 7MB in and
read it back out :-)
>
> Meanwhile, I'm still wondering what happened to your pg_largeobject
> index. You said you saw the problem in multiple databases, which
> suggests that the index was broken in template1 and then the damage
> was propagated to other databases by CREATE DATABASE. Can you still
> see a problem if you make your program connect to some other database?
There is no issue with other databases. I created another database, and
had no issues.

It is my understanding that it shouldn't be database-dependent, since
the only part of large objects that is stored outside of the postgres
database is references to the large object OIDs.

Again, thanks for your help. Let me know if I should check on anything
else regarding the index corruption.

Also, I will post a link to the tutorial in my blog as a reply to this
thread as soon as the blog post is up.

Edward

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

[pgsql-es-ayuda] OT: Base de datos de paises de todo el mundo

Este SQL contiene una base de datos con "casi" todas las localidades
del mundo agrupadas en regiones y países. Cada registro está en ocho
idiomas (Ruso, Inglés, Alemán, Francés, Castellano, Italiano,
Portugués y Chino). Contiene un total de 214 países, 1997 regiones y
129602 localidades. Contiene también la posición devuelta por la API
de Google Maps para latitud y longitud y si esa posición es exacta o
no.

FUENTE:
http://meneame.net/story/base-datos-paises-regiones-localidades-todo-mundo-geoposicionados

--
:: God bless you, every day and every night ::
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

[SQL] Update and trigger

Hi,

I need to increament a counter such as myTable.Counter of type integer everytime myTable.status a boolean column is updated. Can you help me complete this...

create trigger counter_trigger after update on myTable.counter
execute procedure 'BEGIN statement; statement; statement END'

Q1- how do I narrow the event to "update on a column not a row"
Q2- can I just provide an inline procedure for the execute

Thanks
Medi

Re: [HACKERS] Proposal - improve eqsel estimates by including histogram bucket numdistinct statistics

Tom Lane wrote:
> This gets back to the discussions at PGCon about needing to have a more
> explicit representation of partitioning. Right now, for a
> many-partition table we spend huge amounts of time deriving the expected
> behavior from first principles, each time we make a plan. And even then
> we can only get it right for limited cases (eg, no parameterized
> queries). If the planner actually understood that a set of tables
> formed a partitioned set then it'd be a lot easier and faster to get the
> desired behavior, not only with respect to the rowcount estimates but
> the plan's structure.
>
>
>

Even if this doesn't solve every problem, it's surely worth doing for
those it will solve.

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: [pgsql-advocacy] Booth Swag

many people use caps... and like parches for their backpacks and stuff (Like me)

2008/6/11 Joshua D. Drake <jd@commandprompt.com>:
>
>
> On Tue, 2008-06-10 at 17:16 -0700, gabrielle wrote:
>> On Tue, Jun 10, 2008 at 3:14 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> > High-Value Giveaways:
>> > (stuff to give people we actually have significant conversations with)
>> > Folders w/ vendor info
>> > USB sticks??
>
>> I'm not sure where all of this should go (free or whatever), but here
>> are some ideas:
>> - stickers. These were a big hit at LFNW.
>> - temporary tattoos
>> - more of the bags; people really appreciated something to put all
>> their *other* schwag in.
>
> We have (or will have just based on our standard distribution):
>
> Bags
> Folders
> Brochure
> Flyers
> Partner Brochures
> T-Shirts
>
> I like the idea of Tattoos (silly but fun)
>
> Sincerely,
>
> Joshau D. Drake
>
>
>
> --
> Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-advocacy
>

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

Re: [ADMIN] Disk Space issue

Devendra Singh Rawat <Devendra_Rawat@infosys.com> writes:
> We ran the SQL to get the table which are consuming maximum space.
> The results are as shown:-
> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
> relname | relpages
> --------------------------------------------------+----------
> <table-1> | 52869
> <table-2> | 31267
> <table-3> | 15418
> <table-4> | 9693
> <table-5> | 6426

> But these only cover around few GB of data.

pg_class.relpages is only up-to-date as of the last VACUUM.
There's already good reason to suspect that you're not vacuuming
enough; maybe some big tables are not getting vacuumed at all.

I'd suggest having a look into the data directory to see for yourself
which are the big files. The documentation might help you:
http://www.postgresql.org/docs/8.3/static/storage.html
(IIRC, all of this except the material on tablespaces will apply
to 7.4.)

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: [pgsql-advocacy] POP material

Hmmm mainly, informative material in odp or pdf formats, case studies
and stuff like that

you can what i mean here:
https://wiki.ubuntu.com/VenezuelaTeam/MaterialPOP?action=AttachFile&do=get&target=volante_ubuntu-ve.odt
https://wiki.ubuntu.com/VenezuelaTeam/MaterialPOP?action=AttachFile&do=get&target=triptico_instalacion_8_04.odt
https://wiki.ubuntu.com/VenezuelaTeam/MaterialPOP?action=AttachFile&do=get&target=sabores_ubuntu.odt

2008/6/11 Josh Berkus <josh@agliodbs.com>:
> Santiago,
>
>> This weekend i'm going to the 4th National Free Software Congress
>> (Congreso Nacional de Software Libre) in Venezuela, and im going to
>> bring some flyers, few caps (for free :p) and some other stuff with
>> info of the venezuelan pug (grupove.org.ve) but i would like to know
>> if any of you have some other material to see if i can take some
>> more... i've been looking arround but well havent found anything that
>> really picks up our attention...
>
> What kind of stuff were you looking for?
>
> --
> --Josh
>
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>

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

Re: [INTERFACES] pqlib large object error

Edward Amsden <amsden_linux@embarqmail.com> writes:
> Thanks for all your help. I'm somewhat amateur with C and even less
> experienced with PostgreSQL (I'm a recent MySQL convert). Even after
> some googling, I have no idea what this BEGIN block is. Is it C or is it
> SQL? That probably makes me a n00b. :-|

You need a SQL "BEGIN" (or "START TRANSACTION") command and a SQL
"COMMIT" (or "END") command around anything that involves having a
large object descriptor open. It might help to look at the sample
program here:

http://www.postgresql.org/docs/8.3/static/lo-examplesect.html

It's not amazingly well commented :-(, but the lines

res = PQexec(conn, "begin");
res = PQexec(conn, "end");

are *not* optional.

Meanwhile, I'm still wondering what happened to your pg_largeobject
index. You said you saw the problem in multiple databases, which
suggests that the index was broken in template1 and then the damage
was propagated to other databases by CREATE DATABASE. Can you still
see a problem if you make your program connect to some other database?

regards, tom lane

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

Re: [HACKERS] Core team statement on replication in PostgreSQL

Josh Berkus <josh@agliodbs.com> wrote:

> I still see having 2 different settings:
>
> Synchronous: XID visibility is pushed to the master. Maintains synchronous
> failover, and users are expected to run *1* master to *1* slave for most
> installations.
>
> Asynchronous: replication stops on the slave whenever minxid gets out of
> synch. Could have multiple slaves, but noticeable lag between master and
> slave.

I agree with you that we have sync/async option in log-shipping.
Also, we could have another setting - synchronous-shipping and
asynchronous-flushing. We won't lose transactions if both servers are
down at once and can avoid delays to flush wal files into primary's disks.

As for multiple slaves, we could have a cascading configuration;
WAL receiver also delivers WAL records to other servers.
I think it is simple that the postgres core has only one-to-one replication
and multiple slaves are supported by 3rd party's WAL receivers.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

--
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] Disk Space issue

We ran the SQL to get the table which are consuming maximum space.
The results are as shown:-
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
--------------------------------------------------+----------
<table-1> | 52869
<table-2> | 31267
<table-3> | 15418
<table-4> | 9693
<table-5> | 6426

But these only cover around few GB of data.

Regards,
Devendra

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, 11 June 2008 10:06 AM
To: Devendra Singh Rawat
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Disk Space issue

On Wed, 2008-06-11 at 09:58 +1000, Devendra Singh Rawat wrote:
> Hi Joshua,
>
> We used Vacuum Full but were not successful. The reclaimed disk space was only few MB.
> I did not get to which version I need to update my Postgres.

VACUUM FULL may not reclaim your index space if it bloated out. What you
are best to look at is the type of queries are being run. Determine what
is getting bloated and look at modifying how you maintain that relation.

7.4.19 (which hits soon)

Joshua D. Drake

>
> Regards,
> Devendra
>
> Devendra Singh Rawat
> Infosys Technologies Ltd.
> Tel: +61 3 9860 2276 (D)
> Fax: +61 3 9860 2501
> Email: devendra_rawat@infosys.com
> www.infosys.com
> Powered by Intellect
> Driven by Values
>
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Wednesday, 11 June 2008 9:55 AM
> To: Devendra Singh Rawat
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Disk Space issue
>
>
>
> On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote:
> > Hi,
> >
> > We are using PostgreSQL 7.4.5 and facing issues with increasing disk space.
> >
> > Here is some information about our configuration.
> >
> > The data dump size is around 3 GB. And it is occupying around 120GB on the disk. When we recreate the database from data dump it takes around 3.5GB of disk space.
> > But the database size increases by 1 GB daily though actual data added maybe in KB. We are not able to decipher as to what is causing this increase.
> >
> > Suggestions on the likely problems will be appreciated.
>
> You are likely not vacuuming nearly enough.
>
> I would also suggest strongly you update to the latest 7.4 dot release.
>
> Joshua D. Drake
>
> >
> > Regards,
> > Devendra
> >
> > **************** CAUTION - Disclaimer *****************
> > This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> > for the use of the addressee(s). If you are not the intended recipient, please
> > notify the sender by e-mail and delete the original message. Further, you are not
> > to copy, disclose, or distribute this e-mail or its contents to any other person and
> > any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> > every reasonable precaution to minimize this risk, but is not liable for any damage
> > you may sustain as a result of any virus in this e-mail. You should carry out your
> > own virus checks before opening the e-mail or attachment. Infosys reserves the
> > right to monitor and review the content of all messages sent to or from this e-mail
> > address. Messages sent to or from this e-mail address may be stored on the
> > Infosys e-mail system.
> > ***INFOSYS******** End of Disclaimer ********INFOSYS***
> >
>
>

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

Re: [INTERFACES] pqlib large object error

Thanks for all your help. I'm somewhat amateur with C and even less
experienced with PostgreSQL (I'm a recent MySQL convert). Even after
some googling, I have no idea what this BEGIN block is. Is it C or is it
SQL? That probably makes me a n00b. :-| If you could explain I'd
appreciate it :-)

BTW once I get this working I'm going to write a PostgreSQL Large Object
tutorial on my blog, so others don't have to struggle with the code like
I am :-)
On Tue, 2008-06-10 at 19:05 -0400, Tom Lane wrote:
> Edward Amsden <amsden_linux@embarqmail.com> writes:
> > On Tue, 2008-06-10 at 16:49 -0400, Tom Lane wrote:
> >> Hmph. The only thing I can think of is that that index is corrupt,
> >> but it seems unlikely that it would be so in all databases. What
> >> happens if you REINDEX pg_largeobject?
>
> > The error has disappeared.
>
> Hmm, so something did happen to your index.
>
> > Now it can't write to the object. lo_open
> > seems to be successful, but lo_write is failing with:
> >> ERROR: invalid large-object descriptor: 0
>
> You forgot to put the open-and-use-descriptor sequence inside a
> BEGIN block.
>
> regards, tom lane

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

Re: [ODBC] Re: " for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons

alepaes - aldeia digital wrote:
> Sorry,
>
> I mean: "PGODBC 8.03.0200 have the same problem"
>
>
> alepaes - aldeia digital wrote:
>> Hi,
>>
>> Postgresql 8.2.3 have the same problem:
>>
>> <192.168.1.10>ERROR: syntax error at or near "read" at character 487
>> <192.168.1.10>STATEMENT: declare "SQL_CUR01B5BDE8" cursor for SELECT
>> ct12emp04, ct12numlot, ct12numlan, ct12dtlanc, ct12docume, ct12empcc1,
>> ct12empcc2, ct12empcc3, ct12emptr1, ct12emptr2, ct12emptr3,
>> ct12dtdig, ct12tradu1, ct12ccust1, ct12tradu2, ct12ccust2,
>> ct12tradu3, ct12ccust3, ct12valor, ct12histo1, ct12histo2,
>> ct12histo3, ct12histo4, ct12histo5, ct12histo6, ct12renume,
>> ct12origem FROM CT12T WHERE ct12emp04 = '500' AND ct12numlot =
>> '20080532' AND ct12numlan = '52' FOR UPDATE OF CT12T for read
>> only;fetch 100 in "SQL_CUR01B5BDE8"

Could you try the driver testing for 8.3.0201 at

http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue

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

Re: [HACKERS] RFD: ALTER COLUMN .. SET STORAGE COMPRESSED;

On Tue, Jun 10, 2008 at 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dawid Kuroczko" <qnex42@gmail.com> writes:
>> As we already have four types of ALTER COLUMN .. SET STORAGE
>> { PLAIN | EXTERNAL | EXTENDED | MAIN } I would like to add
>> "COMPRESSED" which would force column compression (if column is
>> smaller than some minimun, I guess somwehwere between 16 and 32 bytes).
>
> Please see previous discussions about per-column toasting parameters,
> for instance
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
> http://archives.postgresql.org/pgsql-general/2007-08/msg01129.php
>
> I think the general consensus was that we want more flexible access to
> the compression knobs than just another STORAGE setting.

Sounds like a right way to do it. Perhaps the syntax should be something like:

ALTER TABLE tab ALTER COLUMN x WITH (storage_parameter = value, ...);

With storage parameters like:
compress -- enable/disable compression (like PLAIN or EXTERNAL)
min_input_size -- don't compress if smaller than size
min_comp_rate -- leave uncompressed if rate is smaller than
toast -- for out-of-line storage parameters?
compression_algo -- for specifying alternative algorithms if any
(per Alvaro's suggestion).

Perhaps it would be wise to introduce GUCs with default values (as we have now
ALTER COLUMN .. SET STATISTICS and default_statistics_target), named
for example:
default_column_min_input_size (and so on).

ALTER COLUMN .. SET STORAGE ... should be aliases for WITH (...) and be
deprecated I guess.

The HEAP_HASEXTERNAL infomask bit should probably be used to "trigger"
TOASTing code. Perhaps it should be renamed then? I am worried if storage
parameters wouldn't introduce overhead in PostgreSQL's key parts.

...as for compression_algo, perhaps it could be an oid of compression
function(s)
(we need to decompress too). Also we would need to store information which
algo was used to compress the column. Perhaps a byte between varvarlena
herader and actual compressed data (this way we could have multiple algos
simultaneousley).

Speaking of algorithms, I think that e2compr (ext2 filesystem with transparent
compression) could be a nice source of input in this area.

http://e2compr.sourceforge.net/
(Having algos as plugins would allow us to use foreign licenses (gzip) or
event patented algos in countries where software patents are prohibited
without risking anything in core PostgreSQL)

OK, enough for today. Good night.

Regards,
Dawid
--
Solving [site load issues] with [more database replication] is a lot
like solving your own personal problems with heroin - at first it
sorta works, but after a while things just get out of hand.

--
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-advocacy] Booth Swag

On Tue, 2008-06-10 at 17:16 -0700, gabrielle wrote:
> On Tue, Jun 10, 2008 at 3:14 PM, Josh Berkus <josh@agliodbs.com> wrote:
> > High-Value Giveaways:
> > (stuff to give people we actually have significant conversations with)
> > Folders w/ vendor info
> > USB sticks??

> I'm not sure where all of this should go (free or whatever), but here
> are some ideas:
> - stickers. These were a big hit at LFNW.
> - temporary tattoos
> - more of the bags; people really appreciated something to put all
> their *other* schwag in.

We have (or will have just based on our standard distribution):

Bags
Folders
Brochure
Flyers
Partner Brochures
T-Shirts

I like the idea of Tattoos (silly but fun)

Sincerely,

Joshau D. Drake

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

Re: [pgsql-advocacy] Booth Swag

On Tue, Jun 10, 2008 at 3:14 PM, Josh Berkus <josh@agliodbs.com> wrote:
> High-Value Giveaways:
> (stuff to give people we actually have significant conversations with)
> Folders w/ vendor info
> USB sticks??

I am really into the USB sticks as a way to distribute the latest version.

If we decide to burn CDs again, I suggest that the URL to grab the
latest version be printed on the CD. :)

> Low-Value Giveaways:
> (stuff to give to the hoi-polloi whom we don't have time to talk to;
> primarily just for trade shows)
> ???
> Donation Swag
> (stuff to use to solicit donations)
> T-shirts
> Enamel Pins
> Stickers??
> Blue Elephants?? (need to find cheaper source)
> USB sticks??

I'm not sure where all of this should go (free or whatever), but here
are some ideas:
- stickers. These were a big hit at LFNW.
- temporary tattoos
- more of the bags; people really appreciated something to put all
their *other* schwag in.
- mugs
- pens (good ones...I hate trying to write with a sucky pen.)

> Information Sheets
> (stuff for people to find out about PostgreSQL)
> What Is PostgreSQL? Flyer -- 1 page
> Topical Flyer (depends on timing)
> 4-page brochure (don't really want to hand these out casually, do we?)
> Case Studies -- TBA
> "Info cards": business cards with generic e-mail, download info

- business cards for the local PUG (group URL, meeting date)
- flyer: list of all PostgreSQL-related talks at that conference
- flyer: new features in latest version
- flyer: "talking points" PostgreSQL vs MySQL
- flyer: "what to tell your bosses when they want to use MySQL"
- flyer: a tutorial of how to install & get temp database up (we were
asked for that several times at LFNW...would be neat if we had a
printed sheet.)

> Signs
> New Large Banner
> Pop-up display (need to purchase soon)

Oooh, one of the rollup ones? Those are nice.

> Other stuff
> Brochure holder (need to buy)
> Donation Box

I intend to purchase a locking cashbox to bring to OSCON.

gabrielle

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

Re: [ADMIN] Disk Space issue

7.4.19 (which hits soon)

Joshua D. Drake

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

Re: [ADMIN] Disk Space issue

Hi Joshua,

We used Vacuum Full but were not successful. The reclaimed disk space was only few MB.
I did not get to which version I need to update my Postgres.

Regards,
Devendra

Devendra Singh Rawat
Infosys Technologies Ltd.
Tel: +61 3 9860 2276 (D)
Fax: +61 3 9860 2501
Email: devendra_rawat@infosys.com
www.infosys.com
Powered by Intellect
Driven by Values

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, 11 June 2008 9:55 AM
To: Devendra Singh Rawat
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Disk Space issue

On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote:
> Hi,
>
> We are using PostgreSQL 7.4.5 and facing issues with increasing disk space.
>
> Here is some information about our configuration.
>
> The data dump size is around 3 GB. And it is occupying around 120GB on the disk. When we recreate the database from data dump it takes around 3.5GB of disk space.
> But the database size increases by 1 GB daily though actual data added maybe in KB. We are not able to decipher as to what is causing this increase.
>
> Suggestions on the likely problems will be appreciated.

You are likely not vacuuming nearly enough.

I would also suggest strongly you update to the latest 7.4 dot release.

Joshua D. Drake

>
> Regards,
> Devendra
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of the addressee(s). If you are not the intended recipient, please
> notify the sender by e-mail and delete the original message. Further, you are not
> to copy, disclose, or distribute this e-mail or its contents to any other person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> every reasonable precaution to minimize this risk, but is not liable for any damage
> you may sustain as a result of any virus in this e-mail. You should carry out your
> own virus checks before opening the e-mail or attachment. Infosys reserves the
> right to monitor and review the content of all messages sent to or from this e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>

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

Re: [ADMIN] Disk Space issue

You are likely not vacuuming nearly enough.

I would also suggest strongly you update to the latest 7.4 dot release.

Joshua D. Drake

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

[ADMIN] Disk Space issue

Hi,

We are using PostgreSQL 7.4.5 and facing issues with increasing disk space.

Here is some information about our configuration.

The data dump size is around 3 GB. And it is occupying around 120GB on the disk. When we recreate the database from data dump it takes around 3.5GB of disk space.
But the database size increases by 1 GB daily though actual data added maybe in KB. We are not able to decipher as to what is causing this increase.

Suggestions on the likely problems will be appreciated.

Regards,
Devendra

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

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

[BUGS] No way to list DDL for a new type

Hi,
I created a new type as follows:
CREATE TYPE compfoo AS (f1 int, f2 text);

Now I need the DDL which postgres used to create this type.

Thanks.

With Regards,
rc_bio

[pgus-board] [Fwd: Re: [pgus-general] PgUS bylaws]

What did I do wrong?

Monday, June 9, 2008

[COMMITTERS] pgrecovery - alpha: New Directory

Update of /cvsroot/pgrecovery/alpha/src
In directory pgfoundry.org:/tmp/cvs-serv81892/src

Log Message:
Directory /cvsroot/pgrecovery/alpha/src added to the repository

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

[COMMITTERS] pgrecovery - alpha: New Directory

Update of /cvsroot/pgrecovery/alpha
In directory pgfoundry.org:/tmp/cvs-serv79899/alpha

Log Message:
Directory /cvsroot/pgrecovery/alpha added to the repository

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

Re: [pgeu-general] Date for the EU conference

Hi,

On Tue, 2008-06-10 at 07:29 +0200, Gabriele Bartolini wrote:
> It's October 17 and 18 in Prato, Italy. We are finalising the setup
> from an administrative point of view.

Perfect. Let me book my flight, then :)

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

[PATCHES] VACUUM Improvements - WIP Patch

Here is a WIP patch based on the discussions here:
http://archives.postgresql.org/pgsql-hackers/2008-05/msg00863.php

The attached WIP patch improves the LAZY VACUUM by limiting or
avoiding the second heap scan. This not only saves considerable time
in VACUUM, but also reduces the double-writes of vacuumed blocks. If
the second heap scan is considerably limited, that should also save
CPU usage and reduce WAL log writing.

With HOT, the first heap scan prunes and defrags every page in the
heap. That truncates all the dead tuples to their DEAD line pointers
and releases all the free space in the page. The second scan only
removes these DEAD line pointers and records the free space in the
FSM. The free space in fact does not change from the first pass. But
to do so, it again calls RepairPageFragmentation on each page, dirties
the page and calls log_heap_clean() again on the page. This clearly
looks like too much work for a small gain.

As this patch stands, the first phase of vacuum prunes the heap pages
as usual. But it marks the DEAD line pointers as DEAD_RECLAIMED to
signal that the index pointers to these line pointers are being
removed, if certain conditions are satisfied. Other backend when
prunes a page, also reclaims DEAD_RECLAIMED line pointers by marking
them UNUSED. We need some additional logic to do this in a safe way:

- An additional boolean pg_class attribute (relvac_inprogress) is used
to track the status of vacuum on a relation. If the attribute is true,
either vacuum is in progress on the relation or the last vacuum did
not complete successfully.

When VACUUM starts, it sets relvac_inprogress to true. The transaction
is committed and a new transaction is started so that all other
backends can see the change. We also note down the transactions which
may already have the table open. VACUUM then starts the first heap
scan. It prunes the page, but it can start marking the DEAD line
pointers as DEAD_RECLAIMED only after it knows that all other backends
can see that VACUUM is in progress on the target relation. Otherwise
there is a danger that backends might reclaim DEAD line pointers
before their index pointers are removed and that would lead to index
corruption. We do that by periodic conditional waits on the noted
transactions ids. Once all old transactions are gone, VACUUM sets the
second scan limit to the current block number and starts marking
subsequent DEAD line pointers as DEAD_RECLAIMED.

In most of the cases where the old transactions quickly go away, and
for large tables, the second scan will be very limited. In the worst
case, we might incur the overhead of conditional waits without any
success.

TODO:

- We can potentially update FSM at the end of first pass. This is not
a significant issue if the second scan is very limited. But if we do
this, we need to handle the truncate case properly.

- As the patch stands, we check of old transactions at every block
iteration. This might not be acceptable for the cases where there are
long running transactions. We probably need some exponential gap here.

- As the patch stands, the heap_page_prune handles reclaiming the
DEAD_RECLAIMED line pointers since it already has ability to WAL log
similar changes. We don't do any extra work to trigger pruning though
(except than setting page_prune_xid). May be we should trigger pruning
if we got a line pointer bloat in a page too.

Please let me know comments/suggestions and any other improvements.

Thanks,
Pavan

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

Re: [pgeu-general] Date for the EU conference

Ciao Devrim,

Devrim GÜNDÜZ ha scritto:
> Did I miss any thread? Did we decide about the EU conference date?
> I want to make my travel plans ;)

Fair enough. :)

It's October 17 and 18 in Prato, Italy. We are finalising the setup from
an administrative point of view. Thanks.

Ciao,
Gabriele

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

Re: [ADMIN] turn off auto index for foreign key

um...Your soln is possible. But if the user created an unique index using constraint name <table_name>_<column_name>_key5, it will not be able to distinguish it from other auto index.

On Tue, Jun 10, 2008 at 12:58 PM, Alex Hunsaker <badalex@gmail.com> wrote:

On Mon, Jun 9, 2008 at 10:17 PM, Tommy Cheng <csckcac@gmail.com> wrote:
> you are right. I double checked. So, is there any way to turn off auto index
> creation for the parent? (the best is changing postgresql database setting)
> I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64
>

Again, there is no auto index creation for foriegn keys. However if
you declared the column unique or its the pkey (ala unique) (As Scott
said) then there will an index.

No you cant turn it off because its how postgres enforces uniqueness
see http://www.postgresql.org/docs/8.1/interactive/index-unique-checks.html
for more

My guess, use the name of the index to try to distinguish. Auto
generated ones will always be in the form:
pkey: <table_name>_pkey(num)
unique: <table_name>_<column_name>_key(num)

Re: [pgsql-es-ayuda] Error al consultar una vista

2008/6/9 Alvaro Herrera <alvherre@commandprompt.com>:
> Jairo Graterón escribió:
>
>> *2008-05-28 19:17:29 GMT LOG: statement: SELECT * , "ctid" FROM
>> view_egresocaja where fecha = '2008-05-28'
>> ORDER BY idegresocaja
>
>> **2008-05-28 19:17:29 GMT ERROR: column "ctid" does not exist at character
>> 12
>> 2008-05-28 19:17:29 GMT STATEMENT: SELECT * , "ctid" FROM view_egresocaja
>> where fecha = '2008-05-28'
>> ORDER BY idegresocaja*
>>
>> por una extraña razón le esta agregando el campo "ctid" que no existe a la
>> consulta,
>
> Creo que psqlODBC usa la columna ctid (que agrega automaticamente a la
> consulta) para identificar una tupla que posteriormente se modificara
> (update o delete, supongo). Las vistas no tienen CTID. Supongo que
> tiene que ser posible indicarle a psqlODBC "esta es una vista" o mas
> bien "esta consulta no sera usada en un cursor actualizable", o algo por
> el estilo.
>

tengo un programa que hice alguna vez que se conecta a un odbc que
escoja y ejecuta la sentencia (o script) sql que le diga...
probe con psqlODBC versiones 08_00 hasta 08_03 (solo las ultimas
versiones de cada una) con 08_00 no pude conectar (no es gran
sorpresa), pero de ahi en adelante se conecto y con todos pude
consultar una vista sin problema... probe con 8.3 en windows (bueno en
realidad es 8.4devel pero es del 18 de mayo asi que supongo que no es
mucha la variacion)

revisando el post original veo que no solo se añade la columna "ctid"
sino tambien un ORDER BY, me parece que hay algo que no nos estan
contando

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

Re: [ADMIN] turn off auto index for foreign key

On Mon, Jun 9, 2008 at 10:17 PM, Tommy Cheng <csckcac@gmail.com> wrote:
> you are right. I double checked. So, is there any way to turn off auto index
> creation for the parent? (the best is changing postgresql database setting)
> I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64
>

Again, there is no auto index creation for foriegn keys. However if
you declared the column unique or its the pkey (ala unique) (As Scott
said) then there will an index.

No you cant turn it off because its how postgres enforces uniqueness
see http://www.postgresql.org/docs/8.1/interactive/index-unique-checks.html
for more

My guess, use the name of the index to try to distinguish. Auto
generated ones will always be in the form:
pkey: <table_name>_pkey(num)
unique: <table_name>_<column_name>_key(num)

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

[pgeu-general] Date for the EU conference

Did I miss any thread? Did we decide about the EU conference date?

I want to make my travel plans ;)

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

Re: [PATCHES] SQL: table function support

2008/6/10 Neil Conway <neilc@samurai.com>:
> On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote:
>> this patch add support of table functions syntax like ANSI SQL 2003.
>
> I'm not necessarily opposed to this, but I wonder if we really need
> *more* syntax variants for declaring set-returning functions. The
> existing patchwork of features is confusing enough as it is...
>

internally is table functions implemenation identical with SRF.
Semantically is far - user's doesn't specify return type (what is from
PostgreSQL), but specifies return table, what is more natural. What
more - for users is transparent chaotic joice betwen "SETOF RECORD"
for multicolumns sets and "SETOF type".

Pavel

> -Neil
>
>
>

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

Re: [PATCHES] SQL: table function support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane escribió:
>> (It's also worth asking where the import is coming from. Who implements
>> the spec syntax anyway? DB2 maybe, but when was the last time we heard
>> from anyone trying to migrate from DB2 to PG?)

> Sourceforge?

They gave up on us years ago :-(

regards, tom lane

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

Re: [PATCHES] SQL: table function support

2008/6/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Neil Conway <neilc@samurai.com> writes:
>> On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote:
>>> this patch add support of table functions syntax like ANSI SQL 2003.
>
>> I'm not necessarily opposed to this, but I wonder if we really need
>> *more* syntax variants for declaring set-returning functions.
>
> I've been saying right along that we don't. The proposed patch adds
> no measurable new functionality; its only reason to live is standards
> compliance, and I'm not convinced that's worth the confusion. Our
> implementation of functions is (and always will be) far enough away
> from the standard that notational issues like this are hardly the top
> of the problem list for someone wishing to import a spec-compliant
> function.

a) current syntax is strange for beginers (and I am sure - isn't nice)
- look to mailing lists. I belive so ansi syntax is better.

b) it's needed for well SQL/PSM support. With table functions and
RETURN QUERY we are neer standard.

>
> (It's also worth asking where the import is coming from. Who implements
> the spec syntax anyway? DB2 maybe, but when was the last time we heard
> from anyone trying to migrate from DB2 to PG?)
>

lot of smaller new databases respect ANSI SQL 200x well - not only db2

> regards, tom lane
>

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

Re: [HACKERS] a question about exec_simple_query()

"=?GB2312?Q?=CD=F4=E7=F9?=" <billywq@163.com> writes:
> In other words, what's the benifit we use a portal to run a simple query?

er, because it doesn't work otherwise?

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] turn off auto index for foreign key

you are right. I double checked. So, is there any way to turn off auto index creation for the parent? (the best is changing postgresql database setting)
I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64

On Tue, Jun 10, 2008 at 11:50 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Mon, Jun 9, 2008 at 8:48 PM, Tommy Cheng <csckcac@gmail.com> wrote:
> Hi admins,
> I would like to turn off the auto index for child table's foreign key. It
> is because i want to read the information_schema for table, column, index,
> unique and so on after creating the database. How to turn off the auto
> behavior? Or Is there a way to distinguish between auto one and user created
> index?

There is no auto index creation for child relationships, only for the
parent (unique index for primary key etc)

Re: [HACKERS] Proposal: GiST constraints

On Mon, 2008-06-09 at 21:00 -0400, Tom Lane wrote:
> 1. It's btree-specific and can't be shared by other index AMs that might
> wish to implement constraints.
>

This can be solved by my proposal, but I just don't know how it would
apply to something like GIN, for instance. It could replace the unique
constraint for BTree, but I'm not sure it would perform as well. It's
not that my proposal is GiST-specific, it's just that is the only use
case I can think of that is an improvement.

> 2. It involves the index AM reaching into the heap, which is at the
> least a serious failure of modularity.

We need to reach into the heap for visibility information, if we're to
implement any constraints at all. Also, we have to test against values
being inserted by other concurrent transactions, and those values can
be variable in size. What other mechanism do we have to share those
variable-sized values among several backends?

> 3. There's no way to implement a deferred uniqueness check, nor even to
> handle the within-statement conflict problem.

This is the big one.

> Now admittedly I don't have a solution that addresses these objections
> (much less one that does it without losing any performance) but I'm
> hesitant to see us building new features in this area without any idea
> how we will fix these things --- especially #3, which is a SQL-spec
> violation as well as a frequent user complaint. I'd like to have at
> least a design plan for fixing these things, so we know whether we are
> painting ourselves (further) into a corner.

I'll see if I can come up with something. I agree that's an important
problem to solve. Does anyone know how other DBMSs do this? I found this
thread from the TODO:

http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php

Regards,
Jeff Davis

--
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] turn off auto index for foreign key

On Mon, Jun 9, 2008 at 8:48 PM, Tommy Cheng <csckcac@gmail.com> wrote:
> Hi admins,
> I would like to turn off the auto index for child table's foreign key. It
> is because i want to read the information_schema for table, column, index,
> unique and so on after creating the database. How to turn off the auto
> behavior? Or Is there a way to distinguish between auto one and user created
> index?

There is no auto index creation for child relationships, only for the
parent (unique index for primary key etc)

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

Tuesday, June 10, 2008

Monday, June 9, 2008

Blog Archive