[HACKERS] DROP ROLE dependency tracking ...

good morning,

some days ago i have fallen over an issue which feels more or less like
a bug. consider:

test=# create role xy LOGIN;
CREATE ROLE

test=# grant connect on database test to xy;
GRANT

test=# drop role xy;
ERROR: role "xy" cannot be dropped because some objects depend on it
DETAIL: access to database test

this is a totally fresh instance --- all i did was creating a db called
"test".
failing would make sense if i would the owner of an object but i fact i
don't own anything.

test=# SELECT version();

version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)
(1 row)

is this a known issue?

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

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

[HACKERS] Does 'ALTER INDEX' take exclusive lock?

Hi All,

   Does the command ALTER INDEX take exclusive lock on the objects involved? Specifically I am looking at ALTER INDEX ... SET TABLESPACE. The docs do not mention anything about this. I assume it would, and can do a few tests (or look at code :) ), but asking here wouldn't hurt!

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] Execution-time-sensitive timestamp regression tests

Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> Objections, better ideas?

> Alternatively we could do a pg_sleep(.1) to sleep for 100ms. It sounds like
> the ideal would be something like:

> insert 'now'
> pg_sleep(.1)
> begin
> insert 'now'
> select * from table -- expect 1 (not 0 or 2)
> ... the other tests you mention which get bitten by midnight
> end

Roger, will do.

There actually still is a small risk in the sequence: if the BEGIN
block starts *exactly* at midnight, to within the resolution of
gettimeofday(), then 'now' and 'today' will yield the same value so
the expected row counts will not be matched. This seems like an
acceptably small probability to me.

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] \df displaying volatility

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Would anyone object to \df displaying a function's volatility? Maybe
> limit it to \df+?

Huh? \df+ has displayed volatility for a long time, and I don't recall
any great demand to move it into \df.

regards, tom lane

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

Re: [GENERAL] best er modeling tool for postgreSQL

I know some of these have already been mentioned, but here's the summary I had
stashed away. Although, I'm not sure which ones have any issues with default
schemas. If you do find any such problems (especially with the kind GPL
developers), I'm sure they'd appreciate any feedback.

/** GNU-GPL **/
OpenSystemArchitect

http://www.codebydesign.com/
Power*Architect

http://code.google.com/p/power-architect/
Druid

http://druid.sourceforge.net/
ERW

http://erw.dsi.unimi.it/
Dia

http://live.gnome.org/Dia
XML to DDL (python scripts)

http://xml2ddl.berlios.de/
Graphiz - I believe I've read of people using it to reverse engineer from DDL

http://graphviz.org/

/** Multiple versions/licenses (each has a "free" and a "not-free" version) **/
DBVisualizer

http://www.minq.se/products/dbvis/
Toad

http://www.toadsoft.com/

/** Not-free **/
Microsoft Visio - (If you already have it, it does work quite well)
DataStudio - 30 day trial

http://www.aquafold.com
DBWrench - 30 day trial

http://www.dbwrench.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] [GENERAL] Fragments in tsearch2 headline

On Sat, May 24, 2008 at 11:18 PM, Sushant Sinha <sushant354@gmail.com> wrote:

> Does this mean we want a unified function ts_headline and we trigger the
> fragments if NumFragments is specified? It seems that introducing a new
> function which can take configuration OID, or name is complex as there
> are so many functions handling these issues in wparser.c.
>
> If this is true then we need to just add marking of headline words in
> prsd_headline. Otherwise we will need another prsd_headline_with_covers
> function.

I think that we could merge down the two functions using a default
cover of the whole text when NumCovers if missing. I started writing
the function. The only missing information is, as you stated, the
missing posititions in HeadlineParsedText.

--
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] Necesito orientacion en servidor postgresql en servidor DELL

El día 24 de mayo de 2008 17:15, Edwin Salguero <edwin16@gmail.com> escribió:
> Estimados señores de la lista..
>
> Miren me compre un servidor con las caracteristicas que abajo se nombran
> pensando que hiba a mejorar muchisimo.. mi sistema pero me encuentro con que
> casi no tenemos nada de mejora hasta puedo aventurarme a decir que en un
> momento fue hasta mas lento que el anterior... que era una maquina normal
> una amd64.. de 3.ghz o sea una chanchita..
>
> tanto al anterior como a la nueva le instale ubuntu y postgresql 8.2 porque
> el 8.3 no me deja crear LATIN1 mi pregunta es la siguiente.. existe alguna
> forma de que postgresql utilize todos los recursos de este servidor y se
> convierta en un "avion".. en mi impresion me parece que no esta usando todos
> los recursos del nuevo servidor.. existe algo para cambiar en
> postgresql.conf para mejorar el rendimiento ?.. o realmente postgres no
> soporta grandes cantidades de datos Cosa que no lo creo..
>
> El otro dia saque un dump.. de mi base de datos y era mas o menos 600 mb..
> que me parece poco.. y desde mi punto de vista con esa cantidad de
> informacion.. y con el servidor que tengo que es bueno nomas no deberia ser
> lento.. o estoy equivocado..
>
> Por favor agradezco su orientacion.. talvez ubuntu server no es bueno,...
> talvez el error esta ahi.. necesito toda su ayuda por favor.. gracias..
>
> Este es mi nuevo servidor.. pongo los datos para que vean si con estas
> caracteristicas deberia rendir.. rapido --- normal -- o lento.. mi base de
> datos.. la maquina cliente de mi sistema esta hecho en java.. que se conecta
> con hibernate.. tambien usamos jacorb.. y tenemos otro sistemilla web con
> apache2 y php.. pero lo mas importante es la aplicacion en java..
>
> Tambien les comento que en un proceso.. para evitar duplicidad bloqueamos
> toda la tabla.. o este proceso no esta bien soportado.. por postgresql.. y

mmmmmmmmm para bloquear evitar duplicidad bloqueas todas las tablas
??? como es eso ???
en que casos alguien podría necesitar bloquear todas las tablas ???

> muchas veces se nos cuelga en este proceso.. bueno conforme a las respuesta
> les voy comentando mas cosas.. de mi error..
>
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [pgsql-es-ayuda] Necesito orientacion en servidor postgresql en servidor DELL

No olvides modificar SHMMAX

http://www.postgresql.org/docs/8.2/static/kernel-resources.html

Comparto con Jaime el punto de vista respecto a Ubuntu, no lo
utilizaria en un equipo asi, de hecho hasta donde recuerdo Dell ofrece
Suse como plataforma disponible en lugar de Windows. (No estoy
recomendando Suse, solo te cuento cual es la *oficialmente* soportada)

Saludos!

gb.-

On Sat, May 24, 2008 at 6:47 PM, Jaime Casanova <systemguards@gmail.com> wrote:
> 2008/5/24 Edwin Salguero <edwin16@gmail.com>:
>> Estimados señores de la lista..
>>
>> Miren me compre un servidor con las caracteristicas que abajo se nombran
>> pensando que hiba a mejorar muchisimo.. mi sistema pero me encuentro con que
>> casi no tenemos nada de mejora hasta puedo aventurarme a decir que en un
>> momento fue hasta mas lento que el anterior... que era una maquina normal
>> una amd64.. de 3.ghz o sea una chanchita..
>>
>
> podriamos ver el postgresql.conf?
>
>> tanto al anterior como a la nueva le instale ubuntu y postgresql 8.2 porque
>> el 8.3 no me deja crear LATIN1
>
> ???
>
>> mi pregunta es la siguiente.. existe alguna
>> forma de que postgresql utilize todos los recursos de este servidor y se
>> convierta en un "avion"..
>
> claro, configurandolo correctamente
>
>>
>> Por favor agradezco su orientacion.. talvez ubuntu server no es bueno,...
>> talvez el error esta ahi.. necesito toda su ayuda por favor.. gracias..
>>
>
> en realidad, creo que el problema esta del otro lado del teclado
>
>> Tambien les comento que en un proceso.. para evitar duplicidad bloqueamos
>> toda la tabla.. o este proceso no esta bien soportado.. por postgresql.. y
>> muchas veces se nos cuelga en este proceso.. bueno conforme a las respuesta
>> les voy comentando mas cosas.. de mi error..
>>
>
> PostgreSQL maneja bien LOCK TABLE por eso lo tiene... pero como lo
> estes usando o cuantos procesos esten *colgados* esperando que se
> libere la tabla que bloqueaste ya es otra cosa... como referencia las
> tablas solo se desbloquean una vez que termina la transaccion, si la
> transaccion es demorada puedes terminar teniendo muchos procesos
> esperando
>
>> 1311-61954GB 667MHz (8x512MB), Single Ranked DIMMs
>
> esto es la memoria supongo... 4Gb imagino que en el postgresql.conf
> tienes una buena cantidad a shared_buffers (quiza el 10 o 15%, pero
> estoy adivinando solamente hasta que des mas informacion)
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Guayaquil - Ecuador
> Cel. (593) 087171157
> --
> TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán
>

--
Guido Barosio
-----------------------
http://www.globant.com
guido.barosio@globant.com
--
TIP 8: explain analyze es tu amigo

Re: [HACKERS] Execution-time-sensitive timestamp regression tests

Magnus Hagander wrote:

>For the record, what we were talking about was snapshotting the time at
>backend start and then use QueryPerformanceCounter() to see what
>happened and do some calculation.

Although this might not be such a big issue for the regression tests:
Be aware that the reliability of QueryPerformanceCounter() depends on the
hardware: <http://support.microsoft.com/kb/274323/en-us>

I used it in Munnin, but there were too many customer machines affected by
this bug so that I had to abandom it. This was a few years ago, but I'm not
sure whether the function can be trusted today (see for example this
performance problem <http://support.microsoft.com/kb/895980/en-us>).

Rainer

--
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] Optimzing Postgresql

On May 24, 2008, at 11:57 AM, Ram Ravichandran wrote:

> Hi,
>
> I am deciding between MySQL and Postgres. I'm leaning towards
> Postgres mainly due the widely publicized speed when using
> transactions. However, I am not able to find any good books /
> resources for tuning/ optimizing the database. Is there a book like
> "High Performance MySQL" for Postgres that teaches what the
> different parameters are and how to tune them?

The postgresql manual is good.

http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm is a
five minute tuning overview.

http://www.powerpostgresql.com/PerfList/ is a good overview of basic
tuning, written for 8.0 but still pretty applicable.

Hang out on the pgsql-performance mailing list and see what other
people do - "How do I tune a database for X" comes up pretty
regularly, and gets good answers, so trolling through the mailing list
archive can give some very good advice.

>
> Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?

Probably not. Mysql has a very different philosophy to postgresql. And
some approaches that are suggested to work around performance issue on
mysql may actually harm performance on other databases.

Cheers,
Steve

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

Re: [pgsql-es-ayuda] Necesito orientacion en servidor postgresql en servidor DELL

2008/5/24 Edwin Salguero <edwin16@gmail.com>:
> Estimados señores de la lista..
>
> Miren me compre un servidor con las caracteristicas que abajo se nombran
> pensando que hiba a mejorar muchisimo.. mi sistema pero me encuentro con que
> casi no tenemos nada de mejora hasta puedo aventurarme a decir que en un
> momento fue hasta mas lento que el anterior... que era una maquina normal
> una amd64.. de 3.ghz o sea una chanchita..
>

podriamos ver el postgresql.conf?

> tanto al anterior como a la nueva le instale ubuntu y postgresql 8.2 porque
> el 8.3 no me deja crear LATIN1

???

> mi pregunta es la siguiente.. existe alguna
> forma de que postgresql utilize todos los recursos de este servidor y se
> convierta en un "avion"..

claro, configurandolo correctamente

>
> Por favor agradezco su orientacion.. talvez ubuntu server no es bueno,...
> talvez el error esta ahi.. necesito toda su ayuda por favor.. gracias..
>

en realidad, creo que el problema esta del otro lado del teclado

> Tambien les comento que en un proceso.. para evitar duplicidad bloqueamos
> toda la tabla.. o este proceso no esta bien soportado.. por postgresql.. y
> muchas veces se nos cuelga en este proceso.. bueno conforme a las respuesta
> les voy comentando mas cosas.. de mi error..
>

PostgreSQL maneja bien LOCK TABLE por eso lo tiene... pero como lo
estes usando o cuantos procesos esten *colgados* esperando que se
libere la tabla que bloqueaste ya es otra cosa... como referencia las
tablas solo se desbloquean una vez que termina la transaccion, si la
transaccion es demorada puedes terminar teniendo muchos procesos
esperando

> 1311-61954GB 667MHz (8x512MB), Single Ranked DIMMs

esto es la memoria supongo... 4Gb imagino que en el postgresql.conf
tienes una buena cantidad a shared_buffers (quiza el 10 o 15%, pero
estoy adivinando solamente hasta que des mas informacion)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [pgsql-es-ayuda] Necesito orientacion en servidor postgresql en servidor DELL

Gracias Juan lo voy a ver..


Saludos

Re: [pgsql-es-ayuda] Necesito orientacion en servidor postgresql en servidor DELL

Edwin si bien no puedo ayudarte con tu consulta por no estar yo
capacitado, recuerdo que hace muy poco tiempo hubo un thread muy
extenso sobre rendimiento de servidores que planteaba los temas que
vos mencionas....

Debieras remitirte a la pagina de postgreSQL y buscarlo...

Disculpa por no tener mas que agregar...

Saludos,

Juan

El día 24 de mayo de 2008 18:15, Edwin Salguero <edwin16@gmail.com> escribió:
> Estimados señores de la lista..
>
> Miren me compre un servidor con las caracteristicas que abajo se nombran
> pensando que hiba a mejorar muchisimo.. mi sistema pero me encuentro con que
> casi no tenemos nada de mejora hasta puedo aventurarme a decir que en un
> momento fue hasta mas lento que el anterior... que era una maquina normal
> una amd64.. de 3.ghz o sea una chanchita..
>
> tanto al anterior como a la nueva le instale ubuntu y postgresql 8.2 porque
> el 8.3 no me deja crear LATIN1 mi pregunta es la siguiente.. existe alguna
> forma de que postgresql utilize todos los recursos de este servidor y se
> convierta en un "avion".. en mi impresion me parece que no esta usando todos
> los recursos del nuevo servidor.. existe algo para cambiar en
> postgresql.conf para mejorar el rendimiento ?.. o realmente postgres no
> soporta grandes cantidades de datos Cosa que no lo creo..
>
> El otro dia saque un dump.. de mi base de datos y era mas o menos 600 mb..
> que me parece poco.. y desde mi punto de vista con esa cantidad de
> informacion.. y con el servidor que tengo que es bueno nomas no deberia ser
> lento.. o estoy equivocado..
>
> Por favor agradezco su orientacion.. talvez ubuntu server no es bueno,...
> talvez el error esta ahi.. necesito toda su ayuda por favor.. gracias..
>
> Este es mi nuevo servidor.. pongo los datos para que vean si con estas
> caracteristicas deberia rendir.. rapido --- normal -- o lento.. mi base de
> datos.. la maquina cliente de mi sistema esta hecho en java.. que se conecta
> con hibernate.. tambien usamos jacorb.. y tenemos otro sistemilla web con
> apache2 y php.. pero lo mas importante es la aplicacion en java..
>
> Tambien les comento que en un proceso.. para evitar duplicidad bloqueamos
> toda la tabla.. o este proceso no esta bien soportado.. por postgresql.. y
> muchas veces se nos cuelga en este proceso.. bueno conforme a las respuesta
> les voy comentando mas cosas.. de mi error..
>
> 1223-4507 Quad Core Xeon Processor E54302x6MB Cache, 2.66GHz, 1333MHz FSB,
> PE2900
> 1311-7940 Quad Core Xeon E5430 2nd Processor, 2x6MB Cache 2.66GHz 1333MHz
> FSB,PE2900
> 1311-6195 4GB 667MHz (8x512MB), Single Ranked DIMMs
> 1 310-5017No Keyboard Selected
> 1 430-2968LOM NICs are TOE Ready
> 1 341-302973GB 15K RPM Serial-Attach SCSI 3Gbps 3.5-in HotPlug HardDrive
> 1 341-5700SAS6iR SAS RAID Controller
> 1 341-3052No Floppy Drive
> 1420-6320 No Operating System
> 1310-0024 Mouse Option None
> 1430-1496 Broadcom NetXtreme 5721 SinglePort Gigabit Ethernet NIC, Cu PCIe
> x1
> 1430-1496 Broadcom NetXtreme 5721 SinglePort Gigabit Ethernet NIC, Cu PCIe
> x1
> 1313-6016 Combo CDRW / DVD Drive, SATA
> 1313-4363 Tower Bezel Included
> 1310-5095Argentina Cable 220V IRAM
> 1310-7402Electronic Documentation and OpenManage CD Kit, PE2900
> 1341-302973GB 15K RPM Serial-Attach SCSI 3Gbps 3.5-in HotPlug HardDrive
> 1341-5754Integrated SAS/SATA RAID 1 PERC 6/i Integrated/SAS6/iR
> 1313-5853Tower Chassis Orientation
> 1310-7407Redundant Power Supply with Dual Cords for PowerEdge 2900
> 1911-9099Dell Hardware Limited Warranty Plus On Site Service Extended YR
> 1911-9119DECLINED CRITICAL BUSINESS SERVER OR STORAGE SOFTWARE SUPPORT
> PACKAGE-CALL YOUR DELL SALES REP IF UPGRADE NEEDED
> 1911-9138Dell Hardware Limited Warranty Plus On Site Service Inital YR
> 1913-5270Basic: Business Hours (5X10) Next Business Day On Site Hardware
> Limited Warranty Repair Init YR
> 1913-5812Basic: Business Hours (5X10) Next Business Day On Site Hardware
> Limited Warranty Repair 2YR Ext
> 1910-9997On-Site Installation Declined
> 1310-9847PERC6 Documentation, Spanish
> 2430-1496Broadcom NetXtreme 5721 SinglePort Gigabit Ethernet NIC, Cu PCIe x1
>
>
>
> --
> Edwin Salguero C.
> 70738223
> http://www.kernel.com.bo
--
TIP 8: explain analyze es tu amigo

Re: [HACKERS] [GENERAL] Fragments in tsearch2 headline

Now I understand the code much better. A few more questions on headline
generation that I was not able to get from the code:

1. Why is hlparsetext used to parse the document rather than the
parsetext function? Since words to be included in the headline will be
marked afterwords, it seems more reasonable to just use the parsetext
function.

The main difference I see is the use of hlfinditem and marking whether
some word is repeated.

The reason this is important is that hlparsetext does not seem to be
storing word positions which parsetext does. The word positions are
important for generating headline with fragments.

2.
> I would prefer the signature ts_headline( [regconfig,] text, tsquery
>[,text] )and function should accept 'NumFragments=>N' for default
>parser. Another parsers may use another options.

Does this mean we want a unified function ts_headline and we trigger the
fragments if NumFragments is specified? It seems that introducing a new
function which can take configuration OID, or name is complex as there
are so many functions handling these issues in wparser.c.

If this is true then we need to just add marking of headline words in
prsd_headline. Otherwise we will need another prsd_headline_with_covers
function.

3. In many cases people may already have TSVector for a given document
(for search operation). Would it be faster to pass TSVector to headline
function when compared to computing TSVector each time? If that is the
case then should we have an option to pass TSVector to headline
function?

-Sushant.

On Sat, 2008-05-24 at 07:57 +0400, Teodor Sigaev wrote:
> [moved to -hackers, because talk is about implementation details]
>
> > I've ported the patch of Sushant Sinha for fragmented headlines to pg8.3.1
> > (http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php)
> Thank you.
>
> 1 > diff -Nrub postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.c
> now contrib/tsearch2 is compatibility layer for old applications - they don't
> know about new features. So, this part isn't needed.
>
> 2 solution to compile function (ts_headline_with_fragments) into core, but
> using it only from contrib module looks very odd. So, new feature can be used
> only with compatibility layer for old release :)
>
> 3 headline_with_fragments() is hardcoded to use default parser, but what will be
> in case when configuration uses another parser? For example, for japanese language.
>
> 4 I would prefer the signature ts_headline( [regconfig,] text, tsquery [,text] )
> and function should accept 'NumFragments=>N' for default parser. Another parsers
> may use another options.
>
> 5 it just doesn't work correctly, because new code doesn't care of parser
> specific type of lexemes.
> contrib_regression=# select headline_with_fragments('english', 'wow asd-wow
> wow', 'asd', '');
> headline_with_fragments
> ----------------------------------
> ...wow asd-wow<b>asd</b>-wow wow
> (1 row)
>
>
> So, I incline to use existing framework/infrastructure although it may be a
> subject to change.
>
> Some description:
> 1 ts_headline defines a correct parser to use
> 2 it calls hlparsetext to split text into structure suitable for both goals:
> find the best fragment(s) and concatenate that fragment(s) back to the text
> representation
> 3 it calls parser specific method prsheadline which works with preparsed text
> (parse was done in hlparsetext). Method should mark a needed
> words/parts/lexemes etc.
> 4 ts_headline glues fragments into text and returns that.
>
> We need a parser's headline method because only parser knows all about its lexemes.
>
>
> --
> Teodor Sigaev E-mail: teodor@sigaev.ru
> WWW: http://www.sigaev.ru/
>
>

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

[pgsql-es-ayuda] Necesito orientacion en servidor postgresql en servidor DELL

Estimados señores de la lista..

Miren me compre un servidor con las caracteristicas que abajo se nombran pensando que hiba a mejorar muchisimo.. mi sistema pero me encuentro con que casi no tenemos nada de mejora hasta puedo aventurarme a decir que en un momento fue hasta mas lento que el anterior... que era una maquina normal una amd64.. de 3.ghz o sea una chanchita..

tanto al anterior como a la nueva le instale ubuntu y postgresql 8.2 porque el 8.3 no me deja crear LATIN1 mi pregunta es la siguiente.. existe alguna forma de que postgresql utilize todos los recursos de este servidor y se convierta en un "avion".. en mi impresion me parece que no esta usando todos los recursos del nuevo servidor.. existe algo para cambiar en postgresql.conf para mejorar el rendimiento ?.. o realmente postgres no soporta grandes cantidades de datos  Cosa que no lo creo..

El otro dia saque un dump.. de mi base de datos y era mas o menos 600 mb.. que me parece poco.. y desde mi punto de vista con esa cantidad de informacion.. y con el servidor que tengo que es bueno nomas no deberia ser lento.. o estoy equivocado..

Por favor agradezco su orientacion.. talvez ubuntu server no es bueno,... talvez el error esta ahi.. necesito toda su ayuda por favor.. gracias..

Este es mi nuevo servidor.. pongo los datos para que vean si con estas caracteristicas deberia rendir.. rapido --- normal -- o lento.. mi base de datos.. la maquina cliente de mi sistema esta hecho en java.. que se conecta con hibernate.. tambien usamos jacorb.. y tenemos otro sistemilla web con apache2 y php.. pero lo mas importante es la aplicacion en java..

Tambien les comento que en un proceso.. para evitar duplicidad bloqueamos toda la tabla.. o este proceso no esta bien soportado.. por postgresql.. y muchas veces se nos cuelga en este proceso.. bueno conforme a las respuesta les voy comentando mas cosas.. de mi error..

1	223-4507	Quad Core Xeon Processor E54302x6MB Cache, 2.66GHz, 1333MHz FSB, PE2900
1	311-7940	Quad Core Xeon E5430 2nd Processor, 2x6MB Cache 2.66GHz 1333MHz FSB,PE2900
1	311-6195	4GB 667MHz (8x512MB), Single Ranked DIMMs
1	310-5017	No Keyboard Selected
1	430-2968	LOM NICs are TOE Ready
1	341-3029	73GB 15K RPM Serial-Attach SCSI 3Gbps 3.5-in HotPlug HardDrive
1	341-5700	SAS6iR SAS RAID Controller
1	341-3052	No Floppy Drive
1	420-6320	No Operating System
1	310-0024	Mouse Option None
1	430-1496	Broadcom NetXtreme 5721 SinglePort Gigabit Ethernet NIC, Cu PCIe x1
1	430-1496	Broadcom NetXtreme 5721 SinglePort Gigabit Ethernet NIC, Cu PCIe x1
1	313-6016	Combo CDRW / DVD Drive, SATA
1	313-4363	Tower Bezel Included
1	310-5095	Argentina Cable 220V IRAM
1	310-7402	Electronic Documentation and OpenManage CD Kit, PE2900
1	341-3029	73GB 15K RPM Serial-Attach SCSI 3Gbps 3.5-in HotPlug HardDrive
1	341-5754	Integrated SAS/SATA RAID 1 PERC 6/i Integrated/SAS6/iR
1	313-5853	Tower Chassis Orientation
1	310-7407	Redundant Power Supply with Dual Cords for PowerEdge 2900
1	911-9099	Dell Hardware Limited Warranty Plus On Site Service Extended YR
1	911-9119	DECLINED CRITICAL BUSINESS SERVER OR STORAGE SOFTWARE SUPPORT PACKAGE-CALL YOUR DELL SALES REP IF UPGRADE NEEDED
1	911-9138	Dell Hardware Limited Warranty Plus On Site Service Inital YR
1	913-5270	Basic: Business Hours (5X10) Next Business Day On Site Hardware Limited Warranty Repair Init YR
1	913-5812	Basic: Business Hours (5X10) Next Business Day On Site Hardware Limited Warranty Repair 2YR Ext
1	910-9997	On-Site Installation Declined
1	310-9847	PERC6 Documentation, Spanish
2	430-1496	Broadcom NetXtreme 5721 SinglePort Gigabit Ethernet NIC, Cu PCIe x1

--
Edwin Salguero C.
70738223
http://www.kernel.com.bo

Re: [PERFORM] Quad Xeon or Quad Opteron?

On Fri, May 23, 2008 at 3:41 AM, Andrzej Zawadzki <zawadaa@wp.pl> wrote:

Hello,

 We're planning new production server for PostgreSQL and I'm wondering
which processor (or even platform) will be better: Quad Xeon or Quad
Opteron (for example SUN now has a new offer Sun Fire X4440 x64).

When I was buying my last database server, then SUN v40z was a really
very good choice (Intel's base server was slower). This v40z still works
pretty good but I need one more.

AFAIK Intel made some changes in chipset but... is this better then AMD
HyperTransport and Direct Connect Architecture from database point of
view? How about L3 cache - is this important for performance?

Intel's chipset is still broken when using dual sockets and quad core processors.  The problem manifests itself as excessive cache line bouncing.  In my opinion the best bang/buck combo on the CPU side is the fastest dual-core Xeon CPUs you can find.  You get excellent single-thread performance and you still have four processors, which was a fantasy for most people only 5 years ago.  In addition you can put a ton of memory in the new Xeon machines.  64GB is completely practical.

I still run several servers on Opterons but in my opinion they don't make sense right now unless you truly need the CPU parallelism.

-jwb

[pgadmin-support] Unable to set postmaster to accept TCP/IP Connection

Hi,

My name is Jose Esquivel I'm form Costa Rica, I have been using PostgreSQL since my first steps as a system engineer. A few days ago one of the applications I developed stopped working, is a Java stand-alone application that uses the postgresql-8.0-311.jdbc3.jar driver to connect to a PostgreSQL 8.0 database. I installed the DB in a Windows XP in order to function as a server. These are the steps I executed in order to make the postmaster accept TCP/IP connection:

1- Log as the posgresql user.

2- Include the Ips of the computers that are going to connect to the server via TCP/IP in the pg_hba.conf file as follows:

host    all         all         192.168.16.0/24               md5

I used  192.168.16.0/24 because the computers that could ask for connections are: 192.168.16.1, 192.168.16.2, and 192.168.16.3.

3- Set the listen_addresses = '*' in the postgresql.conf

4- Stop the postmaster using the following command:

    pg_ctl -stop -D "C:\Program Files\PostgreSQL\8.0\data"

5- Start the postmaster using the following commad:

    pg_ctl -o -i -D "C:\Program Files\PostgreSQL\8.0\data"

I attached the .conf files in case I missed something. The problem is that these steps used to work but now I have not been able to make it work again, I checked the network and I could ping all of the other computers. Could you  please tell me if I am missing something or what  am I doing wrong?


Thanks a lot for your time.

---

Yahoo! Deportes Beta
¡No te pierdas lo último sobre el torneo clausura 2008!
Entérate aquí http://deportes.yahoo.com

Re: [GENERAL] Optimzing Postgresql

Ram Ravichandran wrote:
> Hi,
>
> I am deciding between MySQL and Postgres. I'm leaning towards Postgres
> mainly due the widely publicized speed when using transactions.
Everything except for a couple of actions in Postgresql are wrapped in
transactions and can be rollback, you can not turn it off like in MySQL.
> However, I am not able to find any good books / resources for tuning/
> optimizing the database. Is there a book like "High Performance MySQL"
> for Postgres that teaches what the different parameters are and how to
> tune them?
> Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?
I can not comment on a book i have never read so i have no idea what is
between the cover of said book. I don't know if it is specific to MySQL
or is general enough to apply to all databases. I would think the book
is specific to MySQL.

Tunning Postgresql performance is really quit painless just very time
consuming.
Greg Smith has written allot stuff that covers the parameters in postgresql
http://www.westnet.com/~gsmith/

Here's another resource.
http://www.postgresqldocs.org/wiki/Performance_Optimization

--
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] Optimzing Postgresql

On 5/24/08, Ram Ravichandran <ramkaka@gmail.com> wrote:
> I am deciding between MySQL and Postgres. I'm leaning towards Postgres
> mainly due the widely publicized speed when using transactions. However, I
> am not able to find any good books / resources for tuning/ optimizing the
> database. Is there a book like "High Performance MySQL" for Postgres that
> teaches what the different parameters are and how to tune them?
> Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?

There's no book, that I know, dedicated to tuning PostgreSQL
performance, but the PostgreSQL book by Korry Douglas has a big
section on tuning and statistics gathering:

http://www.amazon.com/PostgreSQL-Developers-Library-Korry-Douglas/dp/0672327562

There are plenty of overlaps between tuning PostgreSQL and tuning any
other database, such as using the right RAID setup to optimize I/O.
PostgreSQL has a bunch of parameters for controlling buffer sizes,
write-ahead logging, sort memory and so on. Here's a decent overview:

http://www.powerpostgresql.com/PerfList

When looking for material, make sure it's updated to the 8.x series,
which drastically changed the way PostgreSQL manages its cache
buffers.

Alexander.

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

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

On May 18, 2008, at 1:28 AM, Greg Smith wrote:
> I just collected all the good internals information included in
> this thread and popped it onto http://wiki.postgresql.org/wiki/

> Hint_Bits where I'll continue to hack away at the text until it's
> readable. Thanks to everyone who answered my questions here,
> that's good progress toward clearing up a very underdocumented area.
>
> I note a couple of potential TODO items not on the official list
> yet that came up during this discussion:
>
> -Smooth latency spikes when switching commit log pages by
> preallocating cleared pages before they are needed
>
> -Improve bulk loading by setting "frozen" hint bits for tuple
> inserts which occur within the same database transaction as the
> creation of the table into which they're being inserted
>
> Did I miss anything? I think everything brought up falls either
> into one of those two or the existing "Consider having the
> background writer update the transaction status hint bits..." TODO.

Blah, sorry for the double-post, but I just remembered a few things...

Did we completely kill the idea of the bg_writer *or some other
background process* being responsible for setting all hint-bits on
dirty pages before they're written out?

Also, Simon and Tom had an idea at PGCon: Don't set hint-bits in the
back-end if the page isn't already dirty. We'd likely need some
heuristics on this... based on Luke's comments about improved CLOG
caching maybe we want to set the bits anyway if the tuples without
them set are from old transactions (idea being that pulling those
CLOG pages would be pretty expensive). Or better yet; if we have to
read a CLOG page off disk, set the bits.

This could still potentially be a big disadvantage for data
warehouses; though perhaps the way to fix that is recommend a
backgrounded vacuum after data load.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

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

On May 18, 2008, at 1:28 AM, Greg Smith wrote:
> I just collected all the good internals information included in
> this thread and popped it onto http://wiki.postgresql.org/wiki/

> Hint_Bits where I'll continue to hack away at the text until it's
> readable. Thanks to everyone who answered my questions here,
> that's good progress toward clearing up a very underdocumented area.
>
> I note a couple of potential TODO items not on the official list
> yet that came up during this discussion:
>
> -Smooth latency spikes when switching commit log pages by
> preallocating cleared pages before they are needed
>
> -Improve bulk loading by setting "frozen" hint bits for tuple
> inserts which occur within the same database transaction as the
> creation of the table into which they're being inserted
>
> Did I miss anything? I think everything brought up falls either
> into one of those two or the existing "Consider having the
> background writer update the transaction status hint bits..." TODO.

-Evaluate impact of improved caching of CLOG per Greenplum:

Per Luke Longergan:
I'll find out if we can extract our code that did the work. It was
simple but scattered in a few routines. In concept it worked like this:

1 - Ignore if hint bits are unset, use them if set. This affects
heapam and vacuum I think.
2 - implement a cache for clog lookups based on the optimistic
assumption that the data was inserted in bulk. Put the cache one
call away from heapgetnext()

I forget the details of (2). As I recall, if we fall off of the
assumption, the penalty for long scans get large-ish (maybe 2X), but
since when do people full table scan when they're updates/inserts are
so scattered across TIDs? It's an obvious big win for DW work.

--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

[GENERAL] Optimzing Postgresql

Hi,

I am deciding between MySQL and Postgres. I'm leaning towards Postgres mainly due the widely publicized speed when using transactions. However, I am not able to find any good books / resources for tuning/ optimizing the database. Is there a book like "High Performance MySQL" for Postgres that teaches what the different parameters are and how to tune them?
Or do most techniques covered in the High Performance Mysql apply to Postgres too?

Thanks,

Ram

Re: [PERFORM] Posible planner improvement?

Moving to -hackers...

On May 21, 2008, at 9:09 AM, Richard Huxton wrote:
> Luke Lonergan wrote:
>> The problem is that the implied join predicate is not being
>> propagated. This is definitely a planner deficiency.
>
> IIRC only equality conditions are propagated and gt, lt, between
> aren't. I seem to remember that the argument given was that the
> cost of checking for the ability to propagate was too high for the
> frequency when it ocurred.
>
> Of course, what was true for code and machines of 5 years ago might
> not be so today.

Definitely...

How hard would it be to propagate all conditions (except maybe
functions, though perhaps the new function cost estimates make that
more practical) in cases of equality?

For reference, the original query as posted to -performance:

select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;

That took > 84 minutes (the query was a bit longer but this is the
part that made the difference) after a little change the query took
~1 second:

select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
t1.id = t2.id;

--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [PERFORM] shared_buffers performance

On Apr 14, 2008, at 3:31 PM, Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
>> The transition domain where performance drops dramatically as the
>> database
>> starts to not fit in shared buffers but does still fit in
>> filesystem cache.
>
> It looks to me like the knee comes where the DB no longer fits in
> filesystem cache. What's interesting is that there seems to be no
> synergy at all between shared_buffers and the filesystem cache.
> Ideally, very hot pages would stay in shared buffers and drop out
> of the
> kernel cache, allowing you to use a database approximating all-of-RAM
> before you hit the performance wall. It's clear that in this example
> that's not happening, or at least that only a small part of shared
> buffers isn't getting duplicated in filesystem cache.

I suspect that we're getting double-buffering on everything because
every time we dirty a buffer and write it out the OS is considering
that as access, and keeping that data in it's cache. It would be
interesting to try an overcome that and see how it impacts things.
With our improvement in checkpoint handling, we might be able to just
write via DIO... if not maybe there's some way to tell the OS to
buffer the write for us, but target that data for removal from cache
as soon as it's written.

> Of course, that's because pgbench reads a randomly-chosen row of
> "accounts" in each transaction, so that there's exactly zero locality
> of access. A more realistic workload would probably have a Zipfian
> distribution of account number touches, and might look a little better
> on this type of test.

--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [PERFORM] Creating large database of MD5 hash values

On Apr 11, 2008, at 10:25 AM, Alvaro Herrera wrote:

Sorry, yes, I'm behind on email... :(

> If MD5 values will be your primary data and you'll be storing millions
> of them, it would be wise to create your own datatype and operators
> with
> the most compact and efficient representation possible.

If you do this *please* post it. I really think it would be worth
while for us to have fixed-size data types for common forms of binary
data; MD5, SHA1 and SHA256 come to mind.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [pgsql-advocacy] Conference "OSS 2.0 : Leveraging the Open Source community for business"

On May 22, 2008, at 11:28 PM, Andrew Sullivan wrote:
> On Thu, May 22, 2008 at 03:51:23PM -0700, Dirk Riehle wrote:
>> Before anyone gets upset: These academic conference are comparatively
>> cheap, certainly cheaper than commercial conferences, and they are
>> budgeted to break even (non-profit behind it). But of course it is
>> more expensive than a free unconference or something.
>
> Also, it's part of academics' jobs to go to such conferences, so they
> often have an allowance that can be used to pay for this sort of
> thing.

And if people want to present on Postgres I believe funds could be
allocated from SPI for that.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [pgsql-advocacy] Need for PostgreSQL demand?

On May 19, 2008, at 12:22 PM, Rob Napier wrote:
> It seems that I dropped off the advocacy list last year and missed
> all the activity six months ago when once:radix was part of your
> discussion about a killer app.

Thanks for the email. Until now I've recommended Access as a
development environment to folks, but I'm glad there's a commercially
used OSS alternative to that now. No more recommending Access for me. :)
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [PERFORM] Quad Xeon or Quad Opteron?

Knight, Doug wrote:
> Hi,
> As a gauge, we recently purchased several servers as our systems get
> close to going operational. We bought Dell 2900s, with the cheapest quad
> core processors (dual) and put most of the expense into lots of drives
> (8 15K 146GB SAS drives in a RAID 10 set), and the PERC 6 embedded
> controller with 512MB battery backed cache. That gives us more spindles,
> the RAID redundancy we want, plus the high, reliable throughput of the
> BBC. The OS (and probably WAL) will run on a RAID 1 pair of 15K 76GB
> drives. We also went with 8GB memory, which seemed to be the price cost
> point in these systems (going above 8GB had a much higher cost).
> Besides, in our prototyping, or systems had 2GB, which we rarely
> exceeded, so 8GB should be plently (and we can always expand).
>
> So really, if you can save money on processors by going Opteron (and
> your IT department doesn't have an Intel-based system requirement like
> ours), put what you save into a good disk I/O subsystem. Hope that
> helps.
>
Top posting? Bleee ;-) How to read now?

OK I know that IO is most important for database but: I'm sorry, my
question is about processor/platform choice? :-)
I have to buy new server and I want optimal one.
Like I've wrote in different email my IO subsystem is quite good for now.

ps. To admin of that list: what is with Reply-to on that list?

> Doug
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Adam Tauno
> Williams
> Sent: Friday, May 23, 2008 8:22 AM
> To: pgsql-performance
> Subject: Re: [PERFORM] Quad Xeon or Quad Opteron?
>
>
>> Also, based on what I've seen on this list rather than personal
>> experience, you might want to give more thought to your storage than
>>
> to
>
>> CPU power. The usual thrust of advice seems to be: Get a fast, battery
>> backed RAID controller. "Fast" does not mean "fast sequential I/O in
>> ideal conditions so marketing can print a big number on the box"; you
>> need to consider random I/O too. Get lots of fast disks. Get enough
>>
> RAM
>
>> to ensure that your indexes fit in RAM if possible.
>> Note, however, that I have no direct experience with big Pg databases;
>> I'm just trying to provide you with a guide of what information to
>> provide and what to think about so you can get better answers here
>>
> from
>
>> people who actually have a clue.
>>
>
> Yep, we've had PostreSQL databases for a long time. The various
> current generation processors, IMO, have no substantive difference in
> practice; at least not relative to the bang-for-the-buck or more RAM
> and good I/O.
>
>
>

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

Re: [PERFORM] Quad Xeon or Quad Opteron?

Craig Ringer wrote:
> Andrzej Zawadzki wrote:
>
>> Hello,
>>
>> We're planning new production server for PostgreSQL and I'm wondering
>> which processor (or even platform) will be better: Quad Xeon or Quad
>> Opteron (for example SUN now has a new offer Sun Fire X4440 x64).
>>
>
> [snip]
>
>
>> Suggestions?
>>
>
> To get a more useful response here, you might want to include some
> information about your workload and database size, and report on your
> planned disk subsystem and RAM.
>
Disk subsystem:
Hitachi AMS200, 12x10krpm SAS drives in RAID 10 (+1 hot spare), 1GB mem
with battery
Database is ~60GB and growing ;-)
Workloads: ~94% - SELECTS
Q/sek: Avg~300 (1000 in peak)

Server:
v40z is a 4xdouble core with 16GB RAM

> Also, based on what I've seen on this list rather than personal
> experience, you might want to give more thought to your storage than to
> CPU power. The usual thrust of advice seems to be: Get a fast, battery
> backed RAID controller. "Fast" does not mean "fast sequential I/O in
> ideal conditions so marketing can print a big number on the box"; you
> need to consider random I/O too. Get lots of fast disks. Get enough RAM
> to ensure that your indexes fit in RAM if possible.
>
Yes, of course You are right: disks are very important - I know that
especially after switch to SAN.
But server is getting older ;-) - I need good warranty - I have 3 years
from SUN for example.

ps. After reading about HP: SA P800 with StorageWorks MSA70 I'm
considering buying such storage with ~20 disks.

[...]

--
Andrzej Zawadzki

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

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

2008/5/24 Marcos Saldivar <baron.rojo.cuerdas.de.acero@gmail.com>:

El día 24 de mayo de 2008 11:18, Carolina Roman Salgado
<rossyr@abulafia.ciencias.uchile.cl> escribió:

> On Sat, 2008-05-24 at 17:05 +0200, Julio Cesar Rodriguez Dominguez
> wrote:
>
> [...]
>> Corrigiendo tu error de sintaxis por si te sirve esa consulta,
>> quedaria así
>> SELECT title, authors, journal FROM articles WHERE authors LIKE
>>   '%' || (SELECT penname FROM members WHERE uid = 'n') || '%';
>
> ¡Mil gracias! ¡Funciona! Y yo que estaba empezando a temer que nunca
> funcionaría.
>
>>         Creo de deberia existir una tabla autores_articulos y no un
>>         campo que
>>         contenga a los autores...
>>
>> Estoy de acuerdo con la sugerencia de la tabla autores_articulos
>
> Intentaré encontrar el ejemplo que decía Marcos, porque la verdad es que
> no entiendo cómo debería ser la tabla autores_artículos.

debería ser una tabla de intersección, donde autores_articulos tiene
un clave foránea al articulo y otra a los usuarios.

Si, de esta manera tendrias de forma separada los usuarios de ese articulo, y no en un solo campo.
 

saludos.-

--
:: God bless you, every day and every night ::

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

El día 24 de mayo de 2008 11:18, Carolina Roman Salgado
<rossyr@abulafia.ciencias.uchile.cl> escribió:
> On Sat, 2008-05-24 at 17:05 +0200, Julio Cesar Rodriguez Dominguez
> wrote:
>
> [...]
>> Corrigiendo tu error de sintaxis por si te sirve esa consulta,
>> quedaria así
>> SELECT title, authors, journal FROM articles WHERE authors LIKE
>> '%' || (SELECT penname FROM members WHERE uid = 'n') || '%';
>
> ¡Mil gracias! ¡Funciona! Y yo que estaba empezando a temer que nunca
> funcionaría.
>
>> Creo de deberia existir una tabla autores_articulos y no un
>> campo que
>> contenga a los autores...
>>
>> Estoy de acuerdo con la sugerencia de la tabla autores_articulos
>
> Intentaré encontrar el ejemplo que decía Marcos, porque la verdad es que
> no entiendo cómo debería ser la tabla autores_artículos.

debería ser una tabla de intersección, donde autores_articulos tiene
un clave foránea al articulo y otra a los usuarios.

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

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

Re: [GENERAL] best er modeling tool for postgreSQL

Scott Ribe wrote:
> I don't remember if MagicDraw supports multiple schema or not, but back when
> I was looking at CASE-type tools it was one the nicer ones that I found that
> would run on platforms other than Windows.

For Windows, I've been using Case Studio for several years and I've been
very happy with it. However, the company has since been bought by Quest
and the product name was changed to Toad Data Modeler. I have no idea
if it's the same as or /better/worse than CS.

--
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] SUMMARY: Solaved. apache perl cgi script cant load libpq.5.dylib on mac os tiger

Hmmm,

It was the protection on the /usr/local/pgsql directory. Solved, by myself
an hour after making this post. Odd that, its been bugging the heck out of
me for over a week now, and then one hour after posting....

At 16:05 +0100 23/5/08, Fergus McMenemie wrote:
>I have a perl cgi script that wont run on a new mac. The script runs fine
>from the command line, so the perl and postgresql seem good. However
>when run as an apache cgi script the postgresql stuff fails as shown
>below. I altered the script to dump %ENV and the paths seem in order.
>
>The script is not using mod perl. It is using the apple os x 10.4.11 supplied
>version of apache, postgresql-8.3.0 and perl, v5.8.8 with DBD::Pg, DBI and
>JSON installed.
>
>I am at a total loss as to why the library cant be found and would be
>very grateful for any help.
>
>Content-type: text/html Software error:
>
>install_driver(Pg) failed:
> Can't load '/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle'
> for module DBD::Pg: dlopen(/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle, 1):
> Library not loaded: /usr/local/pgsql/lib/libpq.5.dylib
> Referenced from: /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle
> Reason: image not found at /usr/local/lib/perl5/5.8.8/darwin-2level/DynaLoader.pm line 230.
> at (eval 45) line 3
> Compilation failed in require at (eval 45) line 3.
>Perhaps a required shared library or dll isn't installed where expected at /Library/WebServer/cgi-bin/pgcgi.pl line 383
>
> $ENV{AUTH_TYPE} = Basic
> $ENV{DOCUMENT_ROOT} = /Library/WebServer/Documents
> $ENV{DYLD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
> $ENV{GATEWAY_INTERFACE} = CGI/1.1
> $ENV{HTTPS} = on
> $ENV{HTTP_ACCEPT} = text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,/;q=0.5
> $ENV{HTTP_ACCEPT_CHARSET} = ISO-8859-1,utf-8;q=0.7,;q=0.7
> $ENV{HTTP_ACCEPT_ENCODING} = gzip,deflate
> $ENV{HTTP_ACCEPT_LANGUAGE} = en,en-us;q=0.5
> $ENV{HTTP_CONNECTION} = keep-alive
> $ENV{HTTP_HOST} = XXXXX.co.uk
> $ENV{HTTP_KEEP_ALIVE} = 300
> $ENV{HTTP_USER_AGENT} = Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14
> $ENV{LD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
> $ENV{PATH} = /usr/local/pgsql/bin:/usr/local/bin:/bin:/sbin:/usr/bin:/usr/sbin
> $ENV{QUERY_STRING} =
> $ENV{REMOTE_ADDR} = XXXXXX
> $ENV{REMOTE_PORT} = 53698
> $ENV{REMOTE_USER} = fergus
> $ENV{REQUEST_METHOD} = GET
> $ENV{REQUEST_URI} = /cgi-bin/pgcgi.pl
> $ENV{SCRIPT_FILENAME} = /Library/WebServer/cgi-bin/pgcgi.pl
> $ENV{SCRIPT_NAME} = /cgi-bin/pgcgi.pl
> $ENV{SERVER_ADDR} = XXXX
> $ENV{SERVER_ADMIN} = fergus@XXXXX
> $ENV{SERVER_NAME} = XXXXX.co.uk
> $ENV{SERVER_PORT} = 443
> $ENV{SERVER_PROTOCOL} = HTTP/1.1
> $ENV{SERVER_SIGNATURE} = Apache/1.3.41 Server at XXXX.co.uk Port 443
> $ENV{SERVER_SOFTWARE} = Apache/1.3.41 (Darwin) mod_ssl/2.8.31 OpenSSL/0.9.7l
>
>--
>
>===============================================================
>Fergus McMenemie Email:fergus@twig.me.uk
>Techmore Ltd Phone:(UK) 07721 376021
>
>Unix/Mac/Intranets Analyst Programmer
>===============================================================
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

--

===============================================================
Fergus McMenemie Email:fergus@twig.me.uk
Techmore Ltd Phone:(UK) 07721 376021

Unix/Mac/Intranets Analyst Programmer
===============================================================

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

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

On Sat, 2008-05-24 at 17:05 +0200, Julio Cesar Rodriguez Dominguez
wrote:

[...]
> Corrigiendo tu error de sintaxis por si te sirve esa consulta,
> quedaria así
> SELECT title, authors, journal FROM articles WHERE authors LIKE
> '%' || (SELECT penname FROM members WHERE uid = 'n') || '%';

¡Mil gracias! ¡Funciona! Y yo que estaba empezando a temer que nunca
funcionaría.

> Creo de deberia existir una tabla autores_articulos y no un
> campo que
> contenga a los autores...
>
> Estoy de acuerdo con la sugerencia de la tabla autores_articulos

Intentaré encontrar el ejemplo que decía Marcos, porque la verdad es que
no entiendo cómo debería ser la tabla autores_artículos.

Muchas gracias a ambos.

Carolina
--
Carolina Roman Salgado <rossyr@abulafia.ciencias.uchile.cl>

--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [GENERAL] sequences and dependences

On Fri, 23 May 2008, Laurent ROCHE wrote:

> I would like to write a request where I would get the table name and
> column name of a sequence. I have been looking at the sytem table and
> could not link the sequence from pg_class to a table (and even less to a
> table).

There's an example of how to chain between two pg_class entries using
pg_depend that I think is close to what you're looking for at

http://wiki.postgresql.org/wiki/Fixing_Sequences

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] sequences and dependences

> I would like to write a request where I would get the table name and
> column name of a sequence.
> In fact I want the opposite of pg_get_serial_sequence !

You are looking for refobjid in pg_depend, but this
time the sequence could be a dependent to more than table.

A query like the one bellow is should give you the name of the table,
assuming you account for the correct namespace; it will not automatically
show the name of the column:

select objid::regclass, refobjid::regclass
from pg_depend
where objid::regclass::text = 'person_id_seq' ;

Thanks
Ioannis Tambouras

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

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

2008/5/24 Marcos Saldivar <baron.rojo.cuerdas.de.acero@gmail.com>:

El día 24 de mayo de 2008 9:43, Carolina Roman Salgado
<rossyr@abulafia.ciencias.uchile.cl> escribió:

> Hola
>
> Tengo un problema con un select, que no sé siquiera cómo buscar en la
> documentación. Tengo una tabla que contiene información acerca de
> artículos científicos: título, autores, volumen, resumen, etc. En otra
> tabla tengo información acerca de los usuarios del sitio, su nombre,
> número de usuario, mail, "pen name" (el nombre que utilizan para
> publicar), etc. Lo que necesito hacer es buscar los artículos cuyo campo
> autor contenga el pen name para un usuario dado. Algo como
>
> SELECT title, authors, journal FROM articles WHERE authors LIKE
> '%(SELECT penname FROM members WHERE uid = 'n')%';

Corrigiendo tu error de sintaxis por si te sirve esa consulta, quedaria así
SELECT title, authors, journal FROM articles WHERE authors LIKE
  '%' || (SELECT penname FROM members WHERE uid = 'n') || '%';
 

mmmmmmmm que con tiene authors ??? 'cientifico uno, cientifico dos,
cientifico loco' ????

si intentas con algo como:

select a.title, a.authors, a.journal from articles a, members m where
lower(a.authors) ~ lower(a.penname) and m.uid = 'n';

Creo de deberia existir una tabla autores_articulos y no un campo que
contenga a los autores...

Estoy de acuerdo con la sugerencia de la tabla autores_articulos

>
> Eso da un error de sintaxis. Creo que estoy diciéndole tontamente que
> busque autores como _el texto_ "SELECT ..." y el error aparece en las
> comillas interiores, pero no he logrado entender cómo debo escribir la
> búsqueda.
>
> ¿Pueden darme alguna pista? ¡Gracias!

es idea mia o este modelo de datos, sale un un libro de db ??? me
parece haberlo visto ya... saludos.-

--
TIP 7: no olvides aumentar la configuración del "free space map"

--
:: God bless you, every day and every night ::

[GENERAL] editing functions/triggers etc..

Hi,
I was wondering if anyone has written a tool that will drop (cascade) then
allow the edit and then restore the functions/triggers including the adding
the function/triggers to the inserts/deletes/etc.. to the tables.

It turns out that have to change 50 fifty triggers. At the moment I create a
list of the tables -remove the link, edit the code, restore the link. This
is not working very well.

TIA

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: [pgsql-es-ayuda] Select usando datos provenientes de otro select

El día 24 de mayo de 2008 10:45, Carolina Roman Salgado
<rossyr@abulafia.ciencias.uchile.cl> escribió:
> On Sat, 2008-05-24 at 10:28 -0400, Marcos Saldivar wrote:
>> El día 24 de mayo de 2008 9:43, Carolina Roman Salgado
>> <rossyr@abulafia.ciencias.uchile.cl> escribió:
>
> [...]
>
>> mmmmmmmm que con tiene authors ??? 'cientifico uno, cientifico dos,
>> cientifico loco' ????
>
> A ver, déjame sacar mostrarte uno de los campos reales.
>
> J. M. García-Lastra, M.Moreno, M.T.Barriuso
>
> De todos esos autores, solo García-Lastra es usuario. Y no siempre
> aparecerá su nombre de la misma forma, podría ser Juan M. García-Lastra,
> por ejemplo.
>
>> si intentas con algo como:
>>
>> select a.title, a.authors, a.journal from articles a, members m where
>> lower(a.authors) ~ lower(a.penname) and m.uid = 'n';
>>
>> Creo de deberia existir una tabla autores_articulos y no un campo que
>> contenga a los autores...
>
> ¿Qué quieres decir? No puedo saber cuántos autores serán, tampoco si sus
> nombres serán escritos de la misma forma; en realidad no sé nada, salvo
> que son cadenas de texto y que contienen al menos un nombre de usuario.
> Ni siquiera sé cómo podría separar los usuarios, porque algunas revistas
> escriben los autores separados por comas, el último por un and, algunas
> solo por espacios salvo el último, algunas reemplazan algunos autores
> por "et al." :(
>

Si la tabla articles y members solo se pueden relacionar con los
campos athors y penname, solo veo como solucion el select que te
indique, este no te entrega los resultados que esperas ??? sino, yo a
tú no entender... saludos.-
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [GENERAL] XML2 module and xpath_table

Peter Eisentraut wrote on 24.05.2008 13:35:
> Am Mittwoch, 21. Mai 2008 schrieb Thomas Kellerer:
>> How would I achieve the same without using the deprecated xml2 module?
>
> xpath_table is probably the major piece that is not directly covered by the
> new system. So until we have a replacement, we probably won't remove the old
> module.
>
Ah, good to know.

Thanks for the answer

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

Re: [pgsql-es-ayuda] Consulta sobre Exceptions

Seguramente estas usando un raise notice dentro del bloque que atrapa la excepción, deberias usar RAISE EXCEPTION 'tu cadena';

2008/5/24 Juan Manuel Fernandez <jmfernandez@unlu.edu.ar>:

Tengo una  constraint de primary key de nombre «El programa ya esta
cargado en esa asignatura.»  creada con la intencion de que cuando
ingrese una clave ya existente la BD me devuelva ese mensaje pero, a
diferencia de lo esperado me devuelve:

 ERROR: llave duplicada viola restricción de unicidad «El programa ya
esta cargado en esa asignatura.» CONTEXT: sentencia SQL: «INSERT INTO
T_PROGRAMAS_X_ASIGNATURA VALUES ( $1 , $2 , $3 , $4 , $5 , $6 )»
PL/pgSQL function "vincular_asignatura_y_programa" line 13 at SQL
statement

Hay forma de limitar a PostgreSQL a que solo me retorne  ERROR: llave
duplicada viola restricción de unicidad «El programa ya esta cargado
en esa asignatura.» evitando el contexto???

Gracias,

           Saludos,

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

--
:: God bless you, every day and every night ::

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

On Sat, 2008-05-24 at 16:45 +0200, Carolina Roman Salgado wrote:
[...]
>
> A ver, déjame sacar mostrarte uno de los campos reales.
>
> J. M. García-Lastra, M.Moreno, M.T.Barriuso
>
> De todos esos autores, solo García-Lastra es usuario. Y no siempre
> aparecerá su nombre de la misma forma, podría ser Juan M. García-Lastra,
> por ejemplo.
[...]

Olvidé decir que en el ejemplo, penname es "García-Lastra".

Carolina
--
Carolina Roman Salgado <rossyr@abulafia.ciencias.uchile.cl>

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

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

On Sat, 2008-05-24 at 10:28 -0400, Marcos Saldivar wrote:
> El día 24 de mayo de 2008 9:43, Carolina Roman Salgado
> <rossyr@abulafia.ciencias.uchile.cl> escribió:

[...]

> mmmmmmmm que con tiene authors ??? 'cientifico uno, cientifico dos,
> cientifico loco' ????

A ver, déjame sacar mostrarte uno de los campos reales.

J. M. García-Lastra, M.Moreno, M.T.Barriuso

De todos esos autores, solo García-Lastra es usuario. Y no siempre
aparecerá su nombre de la misma forma, podría ser Juan M. García-Lastra,
por ejemplo.

> si intentas con algo como:
>
> select a.title, a.authors, a.journal from articles a, members m where
> lower(a.authors) ~ lower(a.penname) and m.uid = 'n';
>
> Creo de deberia existir una tabla autores_articulos y no un campo que
> contenga a los autores...

¿Qué quieres decir? No puedo saber cuántos autores serán, tampoco si sus
nombres serán escritos de la misma forma; en realidad no sé nada, salvo
que son cadenas de texto y que contienen al menos un nombre de usuario.
Ni siquiera sé cómo podría separar los usuarios, porque algunas revistas
escriben los autores separados por comas, el último por un and, algunas
solo por espacios salvo el último, algunas reemplazan algunos autores
por "et al." :(

[...]

> > ¿Pueden darme alguna pista? ¡Gracias!
>
> es idea mia o este modelo de datos, sale un un libro de db ??? me
> parece haberlo visto ya... saludos.-

Pues si me dijeras el libro te estaría muy agradecida. Pero así, en el
aire, no sé. No tengo ningún libro de bases de datos.

Gracias otra vez.

Carolina
--
Viajes: http://paticucha.blogspot.com
Fotos: http://nano-bio.ehu.es/xavier/gallery
Registered Linux user number 40938 http://counter.li.org/

--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podr� leerlo

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

El día 24 de mayo de 2008 10:28, Marcos Saldivar
<baron.rojo.cuerdas.de.acero@gmail.com> escribió:
> El día 24 de mayo de 2008 9:43, Carolina Roman Salgado
> <rossyr@abulafia.ciencias.uchile.cl> escribió:
>> Hola
>>
>> Tengo un problema con un select, que no sé siquiera cómo buscar en la
>> documentación. Tengo una tabla que contiene información acerca de
>> artículos científicos: título, autores, volumen, resumen, etc. En otra
>> tabla tengo información acerca de los usuarios del sitio, su nombre,
>> número de usuario, mail, "pen name" (el nombre que utilizan para
>> publicar), etc. Lo que necesito hacer es buscar los artículos cuyo campo
>> autor contenga el pen name para un usuario dado. Algo como
>>
>> SELECT title, authors, journal FROM articles WHERE authors LIKE
>> '%(SELECT penname FROM members WHERE uid = 'n')%';
>
> mmmmmmmm que con tiene authors ??? 'cientifico uno, cientifico dos,
> cientifico loco' ????
>
> si intentas con algo como:
>
> select a.title, a.authors, a.journal from articles a, members m where
> lower(a.authors) ~ lower(a.penname) and m.uid = 'n';

correccion: es m.penname y no a.penname

select a.title, a.authors, a.journal from articles a, members m where
lower(a.authors) ~ lower(m.penname) and m.uid = 'n';

>
> Creo de deberia existir una tabla autores_articulos y no un campo que
> contenga a los autores...
>
>>
>> Eso da un error de sintaxis. Creo que estoy diciéndole tontamente que
>> busque autores como _el texto_ "SELECT ..." y el error aparece en las
>> comillas interiores, pero no he logrado entender cómo debo escribir la
>> búsqueda.
>>
>> ¿Pueden darme alguna pista? ¡Gracias!
>
> es idea mia o este modelo de datos, sale un un libro de db ??? me
> parece haberlo visto ya... saludos.-
>
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [pgsql-es-ayuda] Select usando datos provenientes de otro select

El día 24 de mayo de 2008 9:43, Carolina Roman Salgado
<rossyr@abulafia.ciencias.uchile.cl> escribió:
> Hola
>
> Tengo un problema con un select, que no sé siquiera cómo buscar en la
> documentación. Tengo una tabla que contiene información acerca de
> artículos científicos: título, autores, volumen, resumen, etc. En otra
> tabla tengo información acerca de los usuarios del sitio, su nombre,
> número de usuario, mail, "pen name" (el nombre que utilizan para
> publicar), etc. Lo que necesito hacer es buscar los artículos cuyo campo
> autor contenga el pen name para un usuario dado. Algo como
>
> SELECT title, authors, journal FROM articles WHERE authors LIKE
> '%(SELECT penname FROM members WHERE uid = 'n')%';

mmmmmmmm que con tiene authors ??? 'cientifico uno, cientifico dos,
cientifico loco' ????

si intentas con algo como:

select a.title, a.authors, a.journal from articles a, members m where
lower(a.authors) ~ lower(a.penname) and m.uid = 'n';

Creo de deberia existir una tabla autores_articulos y no un campo que
contenga a los autores...

>
> Eso da un error de sintaxis. Creo que estoy diciéndole tontamente que
> busque autores como _el texto_ "SELECT ..." y el error aparece en las
> comillas interiores, pero no he logrado entender cómo debo escribir la
> búsqueda.
>
> ¿Pueden darme alguna pista? ¡Gracias!

es idea mia o este modelo de datos, sale un un libro de db ??? me
parece haberlo visto ya... saludos.-
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [PATCHES] TODO item: Have psql show current values for a sequence

On Sat, May 24, 2008 at 2:25 AM, daveg <daveg@sonic.net> wrote:
> On Sat, May 24, 2008 at 12:27:16AM -0300, Dickson S. Guedes wrote:
>> Hi all,
>>
>> These patch implements the TODO item: Have psql show current values
>> for a sequence.
>> Comments are welcome.
>>
>> Sequence "public.foo_bar_seq"
>> +---------------+---------+---------------------+
>> | Column | Type | Value |
>> +---------------+---------+---------------------+
>> | sequence_name | name | foo_bar_seq |
>> | last_value | bigint | 11 |
>> | start_value | bigint | 1 |
>> | increment_by | bigint | 1 |
>> | max_value | bigint | 9223372036854775807 |
>> | min_value | bigint | 1 |
>> | cache_value | bigint | 1 |
>> | log_cnt | bigint | 31 |
>> | is_cycled | boolean | f |
>> | is_called | boolean | t |
>> +---------------+---------+---------------------+
>
> Is it now the style to draw a complete box around /d* displays? Or can we
> dispense with the top and bottom rows of dashes?

Hi Dave,

This box around the display is because I'm using \pset border 2.

--
[]s
Dickson S. Guedes
-------------------------------------
Projeto Colmeia - Curitiba - PR
+55 (41) 3254-7130 ramal: 27
http://makeall.wordpress.com/
http://pgcon.postgresql.org.br/
http://planeta.postgresql.org.br/

--
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] May Commitfest is done!

Am Mittwoch, 21. Mai 2008 schrieb Richard Huxton:
> Is there a tag in the CVS to mark this point, or better still a tarball
> that people like me can check out and play with over the next month or two?

There is not. But the density of commits is not so high, so you should be
able to target this state by timestamp. Especially as there will be less
(invasive) commits in theory during the noncommitfest times, any repository
state between now and July is good for testing.

--
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] Error por encoding

ANTES DE HACER ESTO :

COPY sales_order FROM e'C:\\192.dat' USING DELIMITERS ','

 

debes de hacer esto :

SET CLIENT_ENCODING TO LATIN1;

 

Miguel Canchas

-----Mensaje original-----
De: Carlos Pineda [mailto:carlospinedave@gmail.com]
Enviado el: Viernes, 23 de Mayo de 2008 04:05 p.m.
Para: pgsql-es-ayuda@postgresql.org
Asunto: [pgsql-es-ayuda] Error por encoding

Hola a todos,

Tengo mi Postgresql para windows 8.3.1 y me conecto con el usuario postgres, en el archivo postgresql.conf tengo seteado el siguiente valor: client_encoding = 'LATIN1'

luego creé la siguiente base de datos:

CREATE DATABASE "PB"
  WITH OWNER = postgres
       ENCODING = 'LATIN1';
ALTER DATABASE "PB" SET client_encoding=latin1;

notese que el encoding de la BD tambien está seteado a LATIN1, pero cuando trato de ejecutar el siguiente comando:

COPY sales_order FROM e'C:\\192.dat' USING DELIMITERS ','

Me arroja el siguiente ERROR:

invalid byte sequence for encoding "UTF8": 0xf3272c32 SQL state: 22021
Hint: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". Context: COPY sales_order, line 1

y la data que contiene la líea en referencia es:

línea #1 -> 2001,101,1996/mar/14 00:00,'r1','mañongó',299

 

Mi pregunta es por que me arroja un error for encoding "UTF8" y como puedo solucionarlo...?

 

Gracias!

[pgsql-es-ayuda] Select usando datos provenientes de otro select

Hola

Tengo un problema con un select, que no sé siquiera cómo buscar en la
documentación. Tengo una tabla que contiene información acerca de
artículos científicos: título, autores, volumen, resumen, etc. En otra
tabla tengo información acerca de los usuarios del sitio, su nombre,
número de usuario, mail, "pen name" (el nombre que utilizan para
publicar), etc. Lo que necesito hacer es buscar los artículos cuyo campo
autor contenga el pen name para un usuario dado. Algo como

SELECT title, authors, journal FROM articles WHERE authors LIKE
'%(SELECT penname FROM members WHERE uid = 'n')%';

Eso da un error de sintaxis. Creo que estoy diciéndole tontamente que
busque autores como _el texto_ "SELECT ..." y el error aparece en las
comillas interiores, pero no he logrado entender cómo debo escribir la
búsqueda.

¿Pueden darme alguna pista? ¡Gracias!

--
Carolina Roman Salgado <rossyr@abulafia.ciencias.uchile.cl>

--
TIP 7: no olvides aumentar la configuraci�n del "free space map"