Re: [HACKERS] WITH RECUSIVE patches 0723

Hello

2008/7/29 Martijn van Oosterhout <kleptog@svana.org>:
> On Mon, Jul 28, 2008 at 07:57:16PM +0100, Andrew Gierth wrote:
>> Which will be a serious pessimization in many common cases if you do
>> it all the time. Googling for examples of non-recursive WITH queries
>> shows that it is very widely used for clarity or convenience, in
>> contexts where you _don't_ want materialization.
>
> Since the problem is using the result of a WITH clause more than once,
> would it be sufficient to simply detect that case and bail? You don't
> want materialisation is most cases, there's just a few where it is
> needed.
>

I thing so materialisation is more important than you thing. Without
materialisation I could use derived tables, but materialisation in
WITH statement is unique feature usefull for analytical queries. I am
sure, so materialisation should be one from possible strategies.

I like to see this feature in core, with/without materialisation is
usefull for recursive queries and I thing so materialisation should be
add in next months. I don't see it as mayor break. I prefere early
commit of this patch (with neccessary documentation), because there
will be some work that cannot be commited concurently - analytic
queries and my implementation of rollup and cube operator.

Regards
Pavel Stehule

> Have a nice day,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
>> Please line up in a tree and maintain the heap invariant while
>> boarding. Thank you for flying nlogn airlines.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIjkcuIB7bNG8LQkwRAto4AJwPkKlCWD/yBjAyEBL/LXMLK08LPwCfZ2dq
> qSHGPoPPGwGIQOP62eQimGE=
> =Yog+
> -----END PGP SIGNATURE-----
>
>

--
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] Protocol 3, Execute, maxrows to return, impact?

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

On Mon, Jul 28, 2008 at 02:24:22PM -0400, A.M. wrote:
>
> On Jul 28, 2008, at 1:54 PM, Dave Cramer wrote:
>>
>> As Joshua mentioned BSD is the preferred postgresql license. As I
>> understand it I can't even look at your code and subsequently use anything
>> in the JDBC driver
>
> The GPL does not cover implementation ideas [...]

Exactly. What you can't do is copy code verbatim or link to the code
without being bound by the GPL. Taking ideas is free.

(Disclaimer: I'm clearly biased towards the GPL, but not so much as to
not understand that BSD would make more sense in the context of a BSD
project).

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

iD8DBQFIjrMpBcgs9XrR2kYRAtK4AJ99e4hf74WvBR9qz+mMcZDjKAzWwgCcCJBo
BwgZ3g71VPqmFztKlUheKfg=
=gPPR
-----END PGP SIGNATURE-----

--
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] Review: DTrace probes (merged version) ver_03

Tom Lane wrote:
> By "break" I meant "fail to function usefully". Yes, it would still
> compile, but if you don't have the fork number available then you won't
> be able to tell what's really happening in the buffer pool. You might
> as well not pass any of the buffer tag as pass only part of it.
>

Got it.

>> The issue is with Apple's dtrace implementation, not Xcode. For more
>> info, please see the link below.
>> http://www.opensolaris.org/jive/thread.jspa?messageID=252503&#252503
>>
>
> I think what this is complaining about is whether allegedly built-in
> typedefs like uintptr_t work.

This is the message I tried to convey with the comment in probe.d, but I
guess it was not clear.
> What we care about is different: can
> we write an explicit typedef in the .d file?

Yes.

> I do not know if that
> worked in XCode 3.0 or not, but it seems to work fine in the version
> of dtrace shipped in 3.1. (And I'm perfectly fine with telling people
> that they can't compile Postgres dtrace support with less than the most
> recent tool set, especially since it'll be fairly old by the time 8.4
> ships.)
>
I tested on both Xcode 3.0 & 3.1 and both worked.

--
Robert Lor Sun Microsystems
Austin, USA http://sun.com/postgresql

--
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] normalizacion off topic

--- El lun 28-jul-08, Alvaro Herrera <alvherre@alvh.no-ip.org> escribió:

> De: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Asunto: Re: [pgsql-es-ayuda] normalizacion off topic
> Para: "Gabriel Hermes Colina Zambra" <hermeszambra@yahoo.com>
> Cc: pgsql-es-ayuda@postgresql.org, "Calabaza" <calalinux@gmail.com>
> Fecha: lunes, 28 de julio de 2008, 7:24 pm
> Gabriel Hermes Colina Zambra escribió:
>
> > De acuerdo con Calabaza en que se trato demasiado y ya
> es un off topic.
> > Contradictoriamente me queda una duda.
> >
> > Cuanto es desnormalizar un poco, 1%, 2%,5%,10%... etc,
> etc, etc.
> >
> > Creo que si esta desnormalizada esta desnormalizada,
> es como en
> > ginecologia, una mujer no puede estar medio
> embarazada, supongo¿?, por
> > que de eso tambien se poco.
>
> La normalización tiene poco que ver con el embarazo. En
> fútbol tampoco
> puedes hacer medio gol. Pero sí puedes tener una BD que
> esté
> "totalmente normalizada" (dependiendo de cuáles
> reglas de normalización
> quieras considerar; generalmente si cumples las 3 primeras
> formas
> normales estás OK, de ahí para arriba es discutible), y
> romper "un poco"
> la normalización para conseguir una mejora particular de
> rendimiento
> para algo que sea un cuello de botella.
>
> En todo caso la desnormalización es algo que debes hacer
> una vez que
> tienes todo arriba y la aplicación demuestra que una
> consulta en
> particular es demasiado lenta y que la desnormalización la
> ayuda.
> Diseñar la desnormalización desde el principio es pésima
> idea.
>
> --
> Alvaro Herrera
> http://www.advogato.org/person/alvherre
> Criptografía: Poderosa técnica algorítmica de
> codificación que es
> empleada en la creación de manuales de computadores.
> --
> TIP 4: No hagas 'kill -9' a postmaster

____________________________________________________________________________________
¡Buscá desde tu celular!

Yahoo! oneSEARCH ahora está en Claro

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

Re: [GENERAL] why can't I load pgxml.sql

Yi Zhao wrote:
> my version is 8.3.3:
> I found that it's part of the server.
> is it said that I should build postgresql with libxml again??
>
googling gzopen64 throws up a whole lot of hits - most of them were
debian/ubuntu and boiled down to some problem with versions of libz.so

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

--
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] oscon booth report - more details.

On Mon, 2008-07-28 at 20:33 -0700, Josh Berkus wrote:
> Josh,
>
> > Well it is the same cost as the "Make" booth was asking for theirs
> > (according to Mbrewer). However, what I was thinking was that we put a
> > shirt, tote and key all together for 25.00.
>
> Hmmm. I thought the idea of the USB keys was to load them up with software,
> maybe a bootable demo image. Then give them to "special people" in terms of
> genuine interest.

Yes, due to circumstances outside the booths control, that didn't
happen.

>
> Also, we appear to be almost out of shirts.
>

Yes which we knew.

> I seem to recall mentioning that they wouldn't "sell" on their own with some
> booths giving away USB keys for the price of listening to a sales pitch.

At this point we can do whatever we want, they are paid for but
remember, they cost the same as a shirt.

Joshua D. Drake

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

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

Re: [JDBC] GSSAPI/KRB5 and JDBC (again)

On Fri, 25 Jul 2008, Peter Koczan wrote:

> I tried kinit, and it didn't work, but putting my real Kerberos
> password in the password field worked. It looks like it's trying to
> get a new set of credentials/tickets when authenticating, instead of
> using stashed or readily available credentials.

It tries to use the provided password only if it doesn't find an existing
set of credentials. It definitely works for me without a password after
kinit(ing). Perhaps your ticket cache or keytab is in a non-standard
place? This is the first and only time I've setup a kerberos server, so I
may have done something unusual on my end too, but I basically just did a
stock Debian install. Perhaps you need some additional options from here
in your login.conf to let it know things particular to your setup?

http://java.sun.com/j2se/1.4.2/docs/guide/security/jaas/spec/com/sun/security/auth/module/Krb5LoginModule.html

Kris Jurka

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

Re: [GENERAL] why can't I load pgxml.sql

Yi Zhao <yi.zhao@alibaba-inc.com> writes:
> my version is 8.3.3:

I kinda doubt that, actually, because you should not have been getting
"function not found" errors in 8.3. An 8.3 server built without XML
support should react more like this:

regression=# select xpath('foo','bar');
ERROR: unsupported XML feature
DETAIL: This functionality requires the server to be built with libxml support.
HINT: You need to rebuild PostgreSQL using --with-libxml.

In any case the error you're showing us suggests that the libxml
installation is broken, not PG itself.

You still haven't told us the platform...

regards, tom lane

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

Re: [GENERAL] a SQL query question

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

On Jul 28, 2008, at 10:18 PM, Rajarshi Guha wrote:
> aid pid nmol
> - --- --- ----
> 3 23 100
> 2 45 3445
> 4 78 12
>
> I can easily do the first step, but am struggling to make the SQL
> for the second step. Any pointers would be appreciated

Thanks to the posters for helpful solutions

- -------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84
- -------------------------------------------------------------------
Alcohol, an alternative to your self
- 'Alcohol' by the Bare Naked Ladies

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkiOnG8ACgkQZqGSLFHnnoR2qQCeMntkTpqR/ZaVS/nY1izO5u5y
0FYAn0dwi8v0jSB4OvK4OnwMr+7ypQPp
=pNGY
-----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: [JDBC] Timestamp Problems

On Wed, 23 Jul 2008, David Rees wrote:

> What is strange is that this does not happen all the time, and that it
> only affects this one particular column!

Anything going wrong should be reproducible unless your app is doing
something weird temporarily behind the scenes like TimeZone.setDefault.

> Timestamp 1 = 2008-07-23 18:33:59.0 - new Date: Wed Jul 23 18:33:59 EDT 2008
> Update & Refresh
> Timestamp 2 = 2008-07-23 22:33:59.0 - new Date: Wed Jul 23 18:33:59 EDT 2008
>
> Yeah, that's right, getTime() is returning the same value even though
> toString() prints different values and different values get inserted
> in to the database!

You need to check getTimezoneOffset on the two timestamp values. You
can see the dates being printed with the timezone showing they're the
same, but I'm betting they aren't for the timestampts.

> After a lot of troubleshooting, I've finally figured out that by
> disabling the stored procedure cache used by Hibernate (I've
> configured Hibernate to use c3p0's database connection pool and
> prepared statement cache) this issue doesn't crop up any more.
>

I can't imagine why that would be relevant.

Kris Jurka

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

Re: [JDBC] numeric type

On Sun, 20 Jul 2008, Peter wrote:

> For numeric types (with no explicit scale and precision) JDBC returns 0 for
> both precision and scale (ResultSetMetaData.getPrecision and getScale
> methods). This is breaking my app and IMO does not reflect true state of
> things since Postgres docs state: "NUMERIC without any precision or scale
> creates a column in which numeric values of any precision and scale can be
> stored, up to the implementation limit on precision".
>
> Shouldn't PG JDBC driver return maximum possible values for precision and
> scale in such cases?
>

There are a couple of issues here:

1) While PG supports larger numeric values, the maximum precision you can
constrain it to is 1000.

jurka=# create temp table tt (a numeric (1001, 1));
ERROR: NUMERIC precision 1001 must be between 1 and 1000

So do you return 1000 as the maximum precision because that's the maximum
that they can pass into create table even if they might actually receive a
value back that has a higher precision? Or do you return something larger
(the internal pg limit which I don't know offhand) even though they can't
create a table column with that limit?

2) Once you've set the maximum precision, how do you set the maximum
scale as the scale is a dependent on the precision. Is it (1000,500) ?
(1000, 0)? (1000, 1000)?

What we do now certainly isn't all that great, but I haven't heard a
concrete suggestion on what's better.

Kris Jurka

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

Re: [JDBC] Passing arrays

On Thu, 24 Jul 2008, Konrad Machlowski wrote:

> I've been looking for a way to pass from Java an array value (
> Object[]/Integer[]/String[]/... ) to postgresql int2[]/varchar[] stored
> function.

You need to call setArray on a PreparedStatement or CallableStatement.
This takes java.sql.Array instead of Integer[]. You can create a
java.sql.Array easily if you're using a recent JDBC4 driver via
Connection.createArrayOf. If it's an older driver than you must create
a class that implementes java.sql.Array yourself.

Kris Jurka

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

Re: [JDBC] implementing binary datatransfers in pg jdbc

Gustav Trede wrote:
>
> Im interested in fixing "Allow binary data transfers for all datatypes
> not just bytea".
> where is the affected serverside code ?.
>

The server is already prepared to accept binary data, the change just
needs to be made in the driver. The place to start is definitely with
Mikko Tiihonen's patches to do this already[1] and assorted mailing list
discussion. His patches already do ninety something percent of what we
need. I unfortunately don't recall the exact state of the patches and
what the open issues are, so the first place to start is the archives.

Kris Jurka

[1] http://mokki.dyndns.org/~mtiihone/postgresql/binarytransfer/

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

Re: [ADMIN] Time Type

you try to do something like this

select '00:01'::time + interval '10 minutes'

--- On Mon, 7/28/08, Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

> From: Rafael Domiciano <rafael.domiciano@gmail.com>
> Subject: [ADMIN] Time Type
> To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Date: Monday, July 28, 2008, 6:16 PM
> Hi there,
>
> I'm trying to do some as following and I couldn't:
> select '00:01:00'::time + '00:10:00'::time
> ERROR: operator is not unique: time without time zone +
> time without time
> zone at character 127
> HINT: Could not choose a best candidate operator. You
> might need to add
> explicit type casts.
>
> Postgres version: 8.3.3
>
> Anyone knows anything?
>
> Regards,
>
> Rafael Domiciano

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

Re: [GENERAL] why can't I load pgxml.sql

my version is 8.3.3:
I found that it's part of the server.
is it said that I should build postgresql with libxml again??

On Mon, 2008-07-28 at 23:00 -0400, Tom Lane wrote:
> Yi Zhao <yi.zhao@alibaba-inc.com> writes:
> > 1. ./configure --with-libxml --with-libxslt
> > 2. make&&make install (successful)
> > 3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql
>
> > error appearance:
> > SET
> > psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load
> > library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
> > undefined symbol: gzopen64
>
> Well, you've still failed to mention most all of the relevant
> information, like what platform this is and what PG version.
> But what the above suggests is that libxml2 is linked to a
> version of libz that doesn't appear anywhere in the dynamic
> linker's search path. How did you obtain or build libxml2,
> exactly? If this is a Linux platform, I think you might have
> missed out a ldconfig call or two.
>
> regards, tom lane
>

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

Re: [GENERAL] Clone a database to other machine

you can use slony-i

http://slony.info/

--- On Mon, 7/28/08, Garg, Manjit <mgarg@corbus.com> wrote:

> From: Garg, Manjit <mgarg@corbus.com>
> Subject: [GENERAL] Clone a database to other machine
> To: pgsql-general@postgresql.org
> Date: Monday, July 28, 2008, 4:44 PM
> Hi All,
>
> I'm stuck to an issue while cloning the pgsql database,
> can you please
> help, or give any docs to help out.
>
> Query - Trying to have same database on two seprate linux
> servers. One
> will be used to upport Applications and other will be used
> for Report
> generation only.
>
> Want to keep both the database in Sync, hourly or nightly.
>
> Kindly help to achive the same.
>
>
>
> Thanks and regards,
>
> Manjit Garg
> Corbus Global Support Team
> INDIA
> ------------------------------------------------------
> Email: mgarg@corbus.com
> Phone: +91-120-304-4000, Ext 252
> Fax : +91-120-256-7040
>
> Mob : 9810679256
> ------------------------------------------------------
>
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This message, including any attachments hereto,
> (collectively the Email Message) is intended solely for the
> personal and confidential use of the designated recipient(s)
> and may contain privileged, proprietary, or otherwise
> private information which may be subject to attorney-client
> privilege or may constitute inside information protected by
> law. If the reader of this message is not the intended
> recipient, you are hereby notified of the following: (i)
> Any disclosure, printing, copying, or distribution of this
> Email Message by you or (ii) the taking of any action by
> you based on the contents of this Email Message or (iii)
> any other use of this Email Message by you, are strictly
> prohibited. If you have received this message in error,
> please notify the sender immediately and remove all traces
> of the electronic mail message and its attachments from
> your system.

--
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] oscon booth report - more details.

Josh,

> Well it is the same cost as the "Make" booth was asking for theirs
> (according to Mbrewer). However, what I was thinking was that we put a
> shirt, tote and key all together for 25.00.

Hmmm. I thought the idea of the USB keys was to load them up with software,
maybe a bootable demo image. Then give them to "special people" in terms of
genuine interest.

Also, we appear to be almost out of shirts.

I seem to recall mentioning that they wouldn't "sell" on their own with some
booths giving away USB keys for the price of listening to a sales pitch.

--
Josh Berkus
PostgreSQL
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: [GENERAL] a SQL query question

>
> Hi, I have a table of the form
>
> aid pid nmol
> - --- --- ----
> 1 23 34
> 2 45 3445
> 3 23 100
> 4 78 12
> 5 45 14
> 6 45 200
> 7 null null
>
> In general, aid is unique, pid and nmol are non-unique.
>
> What I'm trying to do is to select those rows where pid is not null,
> grouped by pid. So I'd get the following
>
> From within each group I'd like to select the row that has the
> maximum value of nmol. So I'd end up with
>
> aid pid nmol
> - --- --- ----
> 3 23 100
> 2 45 3445
> 4 78 12
>
> I can easily do the first step, but am struggling to make the SQL for
> the second step. Any pointers would be appreciated
>

Normally this is a difficult sort of thing to do, but it's made easier
by a unique feature of Postgresql. Please try the following:

SELECT DISTINCT ON (pid) aid, pid, nmol
FROM tbl
WHERE pid IS NOT NULL
ORDER BY pid ASC, nmol DESC

More information can be found here:
http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-DISTINCT

--
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] Encrytion in postgres field in table

use md5 function in a trigger when insert data

or see this link

http://www.postgresql.org/docs/8.3/static/pgcrypto.html

--- On Mon, 7/28/08, Chris Preston <chris@thetrafalgartravel.com> wrote:

> From: Chris Preston <chris@thetrafalgartravel.com>
> Subject: [SQL] Encrytion in postgres field in table
> To: pgsql-sql@postgresql.org
> Date: Monday, July 28, 2008, 1:25 PM
> How do I setup a password table that I only want to encrypt
> 1 field
> "password"

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

Re: [GENERAL] a SQL query question

Rajarshi Guha wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi, I have a table of the form
>
> aid pid nmol
> - --- --- ----
> 1 23 34
> 2 45 3445
> 3 23 100
> 4 78 12
> 5 45 14
> 6 45 200
> 7 null null
>
> In general, aid is unique, pid and nmol are non-unique.
>
> What I'm trying to do is to select those rows where pid is not null,
> grouped by pid. So I'd get the following
>
> aid pid nmol
> - --- --- ----
> 1 23 34
> 3 23 100
> 2 45 3445
> 5 45 14
> 6 45 200
> 4 78 12
>
> From within each group I'd like to select the row that has the maximum
> value of nmol. So I'd end up with
>
> aid pid nmol
> - --- --- ----
> 3 23 100
> 2 45 3445
> 4 78 12
>
> I can easily do the first step, but am struggling to make the SQL for
> the second step. Any pointers would be appreciated
>

This should do it:

SELECT DISTINCT ON (pid) aid, pid, nmol
FROM foobar
WHERE pid IS NOT NULL
ORDER BY pid ASC, nmol DESC;

The pid ASC satisfies the requirement for the DISTINCT ON part, while
the nmol DESC ensures we get the MAX from each group. Or something like
that.

brian

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

Como interpretar apache ASP.Net , con el componente de mono?

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-849-8087

* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo comun"
*-------------------------------------------------------*

> From: frankyfa@gmail.com
> To: pgsql-es-ayuda@postgresql.org
> Subject: RE: [pgsql-es-ayuda] Tildes
> Date: Mon, 28 Jul 2008 12:50:28 -0500
>
> Estoy desarrollando sobre Visual Studio 2003 con ASP.NET y con servidor Web Apache2 sobre Debian Etch.
>
> Los formularios Web están todos codificados con UTF-8 y la base igual charset UTF-8.
>
> Si la consulta la hago desde la consola del Debian me sale sin problemas pero si la incrusto en el Web Form para que se muestre en alguno de los objetos (combos, checks, etc.) las tildes salen erroneas.
>
> La verdad no sé qué otra cosa pueda ser !!
>
>> -----Mensaje original-----
>> De: Jose Luis Balle [mailto:joseluisballe@gmail.com]
>> Enviado el: lunes, 28 de julio de 2008 9:38
>> Para: frank
>> CC: pgsql-es-ayuda@postgresql.org
>> Asunto: Re: [pgsql-es-ayuda] Tildes
>>
>> En que lenguaje estas desarrollando?
>> Sobre que servidor web?
>>
>> El día 28 de julio de 2008 11:26, frank escribió:
>>> Postgres está sobre Debian Etch.
>>>
>>> Hice la prueba desde consola con psql y los datos salen normales. Es una aplicación
>> Web pero las consultas cuyos datos contienen tildes se muestran como caracteres
>> raros. La base de datos está codificada con UTF-88.
>>>
>>> En los Web forms ya les cambié la codificación a UTF-8 esto me ayudó para las
>> tildes de las etiquetas pero ahora son aquellas que se muestran en los DropDownList
>> los cuales dan errores con las tildes.
>>>
>>> Alguna idea?
>>>
>>>> -----Mensaje original-----
>>>> De: Jose Luis Balle [mailto:joseluisballe@gmail.com]
>>>> Enviado el: jueves, 24 de julio de 2008 12:56
>>>> Para: frank
>>>> CC: pgsql-es-ayuda@postgresql.org
>>>> Asunto: Re: [pgsql-es-ayuda] Tildes
>>>>
>>>> Frank,
>>>> si la aplicación es web tenés que fijarte que estás usando el charset correcto.
>>>> http://www.cristalab.com/tips/34839/entidades-html-charset-y-codificacion-de-
>>>> texto.html
>>>> http://html.conclase.net/w3c/html401-es/charset.html
>>>>
>>>> Desde la consola como ves los datos?
>>>> Sobre que sistema operativo está postgres?
>>>>
>>>>
>>>> 2008/7/24 frank :
>>>>>
>>>>> Saludos lista,
>>>>>
>>>>> Antes de nada gracias por las respuestas acerca del campo serial, lo solucioné
>>>> actualizando el pgadmin aunque al parecer en la versión que tenía también estaba,
>> no
>>>> me di cuenta muy bien.
>>>>>
>>>>> Ahora con algunas consultas en las cuales las palabras son con tildes no me las
>>>> muestra como debiera aunque la base está con codificación UTF-8 aparentemente.
>>>>>
>>>>> Alguna idea acerca de esto?
>>>>>
>>>>> --
>>>>> TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
>>>>> http://archives.postgresql.org/pgsql-es-ayuda
>
>
> --
> TIP 4: No hagas 'kill -9' a postmaster

_________________________________________________________________
Stay in touch when you're away with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_messenger2_072008--
TIP 8: explain analyze es tu amigo

Re: [GENERAL] a SQL query question

Rajarshi Guha wrote:
> What I'm trying to do is to select those rows where pid is not null,
> grouped by pid.
>
> From within each group I'd like to select the row that has the
> maximum value of nmol.
>
Distinct on should do the job for you.
select distinct on (pid) aid, pid, nmol
from atable
where pid is not null
order by pid, nmol desc

If you want the rows tie for max nmol within a pid then you can go to
select aid,pid,nmol
from atable
where (pid,nmol) in (select pid, max(nmol) from atable where pid is
not null group by pid)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

--
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] why can't I load pgxml.sql

Yi Zhao <yi.zhao@alibaba-inc.com> writes:
> 1. ./configure --with-libxml --with-libxslt
> 2. make&&make install (successful)
> 3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql

> error appearance:
> SET
> psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load
> library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
> undefined symbol: gzopen64

Well, you've still failed to mention most all of the relevant
information, like what platform this is and what PG version.
But what the above suggests is that libxml2 is linked to a
version of libz that doesn't appear anywhere in the dynamic
linker's search path. How did you obtain or build libxml2,
exactly? If this is a Linux platform, I think you might have
missed out a ldconfig call or two.

regards, tom lane

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

Re: [pgsql-advocacy] oscon booth report - more details.

On Tue, 2008-07-29 at 02:32 +0000, Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> > The USB keys did not do as well as we'd hoped. People really
> > wanted them as a freebie.
>
> Yes, I still think that's what we should do. It's silly to charge
> for a USB key. How many do we have left anyway?

Well it is the same cost as the "Make" booth was asking for theirs
(according to Mbrewer). However, what I was thinking was that we put a
shirt, tote and key all together for 25.00.

Joshua D. Drake

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

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

Re: [pgsql-advocacy] oscon booth report - more details.

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

> The USB keys did not do as well as we'd hoped. People really
> wanted them as a freebie.

Yes, I still think that's what we should do. It's silly to charge
for a USB key. How many do we have left anyway?

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

iEYEAREDAAYFAkiOgRMACgkQvJuQZxSWSsg5wACgibr9alH8jsYxarAz5PkNiUN/
RPcAnR9deNmcumVM0XulktE1lvk2ry+5
=OePJ
-----END PGP SIGNATURE-----

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

[GENERAL] why can't I load pgxml.sql

1. ./configure --with-libxml --with-libxslt
2. make&&make install (successful)
3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql

error appearance:
SET
psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:15: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:19: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:23: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:27: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:31: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:35: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:41: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:46: ERROR: function
xpath_list(text, text, unknown) does not exist
LINE 2: AS 'SELECT xpath_list($1,$2,'','')'
^
提示: No function matches the given name and argument types. You might
need to add explicit type casts.
psql:/usr/local/pgsql/share/contrib/pgxml.sql:56: ERROR: function
xpath_nodeset(text, text, unknown, unknown) does not exist
LINE 3: AS 'SELECT xpath_nodeset($1,$2,'''','''')'
^
提示: No function matches the given name and argument types. You might
need to add explicit type casts.
psql:/usr/local/pgsql/share/contrib/pgxml.sql:62: ERROR: function
xpath_nodeset(text, text, unknown, text) does not exist
LINE 3: AS 'SELECT xpath_nodeset($1,$2,'''',$3)'
^
提示: No function matches the given name and argument types. You might
need to add explicit type casts.
psql:/usr/local/pgsql/share/contrib/pgxml.sql:69: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:79: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64
psql:/usr/local/pgsql/share/contrib/pgxml.sql:86: ERROR: could not load
library "/usr/local/pgsql/lib/pgxml.so": /usr/lib/libxml2.so.2:
undefined symbol: gzopen64

thanks all.

regards.

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

[GENERAL] a SQL query question

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

Hi, I have a table of the form

aid pid nmol
- --- --- ----
1 23 34
2 45 3445
3 23 100
4 78 12
5 45 14
6 45 200
7 null null

In general, aid is unique, pid and nmol are non-unique.

What I'm trying to do is to select those rows where pid is not null,
grouped by pid. So I'd get the following

aid pid nmol
- --- --- ----
1 23 34
3 23 100
2 45 3445
5 45 14
6 45 200
4 78 12

From within each group I'd like to select the row that has the
maximum value of nmol. So I'd end up with

aid pid nmol
- --- --- ----
3 23 100
2 45 3445
4 78 12

I can easily do the first step, but am struggling to make the SQL for
the second step. Any pointers would be appreciated

- -------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84
- -------------------------------------------------------------------
"whois awk?", sed Grep.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkiOfd4ACgkQZqGSLFHnnoSOKACguioqdY0/Ut7su2KUYu+IRP7D
xOUAoKZsQKveWM52RTe422i3SRGWZk2u
=Xs+n
-----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

[COMMITTERS] npgsql - Npgsql2: Updated changelog for Npgsql 2.0 RC1 release (version

Log Message:
-----------
Updated changelog for Npgsql 2.0 RC1 release (version 1.99.1)

Modified Files:
--------------
Npgsql2/src/Npgsql:
AssemblyInfo.cs (r1.12 -> r1.13)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/AssemblyInfo.cs.diff?r1=1.12&r2=1.13)

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

[COMMITTERS] npgsql - Npgsql2: Updated changelog for Npgsql 2.0 RC1 release

Log Message:
-----------
Updated changelog for Npgsql 2.0 RC1 release

Modified Files:
--------------
Npgsql2:
ChangeLog (r1.6 -> r1.7)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/ChangeLog.diff?r1=1.6&r2=1.7)

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

[COMMITTERS] npgsql - Npgsql2: Make Npgsql more stable in the face of Thread.Abort.

Log Message:
-----------
Make Npgsql more stable in the face of Thread.Abort. Many thanks to Andreas Schönebeck for reporting this and providing a very solid test case.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlConnector.cs (r1.19 -> r1.20)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlConnector.cs.diff?r1=1.19&r2=1.20)

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

[COMMITTERS] npgsql - Npgsql2: Make Npgsql more stable in the face of Thread.Abort.

Log Message:
-----------
Make Npgsql more stable in the face of Thread.Abort. Many thanks to Andreas Schönebeck for reporting this and providing a very solid test case.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlConnectorPool.cs (r1.9 -> r1.10)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlConnectorPool.cs.diff?r1=1.9&r2=1.10)

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

[COMMITTERS] npgsql - Npgsql2: Make Npgsql more stable in the face of Thread.Abort.

Log Message:
-----------
Make Npgsql more stable in the face of Thread.Abort. Many thanks to Andreas Schönebeck for reporting this and providing a very solid test case.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlTransaction.cs (r1.5 -> r1.6)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlTransaction.cs.diff?r1=1.5&r2=1.6)

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

[COMMITTERS] npgsql - Npgsql2: Bug 1010440 - Check for correct type when getting

Log Message:
-----------
Bug 1010440 - Check for correct type when getting store type.
Thanks to Thanh Trung Le for reporting the bug and offering the solution.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlProviderManifest.cs (r1.4 -> r1.5)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlProviderManifest.cs.diff?r1=1.4&r2=1.5)

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

Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?

"David E. Wheeler" <david@kineticode.com> writes:
> To upgrade from citext, I expect
> that what one will have to do is to alter the column to change its
> data type from citext to TEXT + collation.

What I'm wondering is how closely that will match the semantics of the
contrib module ...

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] window function v03 against HEAD

On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote:
> I happily announce that the first design of window function was
> finished and the patch against HEAD is released online. See
> http://umitanuki.net/pgsql/wfv03/design.html

I've put up a git repository at
<http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary>

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

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

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

Re: [GENERAL] why my postgresql auto crashed???

Yi Zhao wrote:
> yes, I thinks it is unrelated with index, beacause the problem is still
> exist after reindex.
>
> I try to change the work memory or shared memory, it's no use:(

Of course not.

Please post the schema of involved tables, and enough data in them to be
able to reproduce the problem.

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

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

Re: [GENERAL] why my postgresql auto crashed???

yes, I thinks it is unrelated with index, beacause the problem is still
exist after reindex.

I try to change the work memory or shared memory, it's no use:(

regards,
Yi

On Mon, 2008-07-28 at 11:10 -0400, Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
> > Yi Zhao wrote:
> >> I don't konw why.
>
> > It almost certainly means that you have a corrupt index.
>
> No, because that assert is nowhere near the index code. I think it's a
> garden-variety bug, but we need a reproducible test case to fix it.
>
> regards, tom lane
>

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

Re: [GENERAL] array_accum() and quoted content

On Mon, Jul 28, 2008 at 04:11:26PM -0400, Raymond C. Rodgers wrote:
> Alvaro Herrera wrote:
>> Raymond C. Rodgers escribió:
>>
>>
>>> Drat, thanks. Other than array_accum() I've never used arrays in
>>> PostgreSQL, so I wasn't aware of that behavior.
>>>
>>
>> Why do you want to use array_accum() in the first place? Maybe there
>> are better ways to do what you are using it for, that do not subject you
>> to the awkward ways of arrays.
>>
> I'm not a database professional, so I'll explain this as best I can.
> There are two tables that are linked via entries in a third: company,
> publisher, and company-publisher association. A publisher can be
> referenced by multiple companies, so the company-publisher association
> table is a simple two column table that consists of foreign keyed
> references to the company table's primary key and the publisher table's
> primary key. The query in which I'm using array_accum() is building a
> list of companies and the associated publishers for each. For example:
>
> SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS
> publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON
> c.company_id = cpa.company_id LEFT JOIN publisher_table p ON
> cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name
> ORDER BY company_name

You could do something like

array_to_string(
array_accum(p.publisher_name),
'|' -- or any other string guaranteed not to appear in the publisher_name
)

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

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

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

Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?

On Jul 28, 2008, at 12:29, Tom Lane wrote:

> Andrew Dunstan <andrew@dunslane.net> writes:
>> ISTM that Tom's objection is really that citext is a hack, and that
>> it
>> will actually make it harder for us to get to a collation-based case
>> insensitive comparison.
>
> Well, it won't make it harder to implement collations; but I worry
> that
> people who have been relying on the citext syntax will have a hard
> time
> migrating to collations. Perhaps if someone did the legwork to
> determine exactly what that conversion would look like, it would
> assuage
> the fear.

Well, there is no syntax for citext. Right now, lots of folks are
using LOWER() all over the place, in indexes and queries, to get the
behavior implemented by citext, and that will be a *lot* harder to
migrate from than citext will be. To upgrade from citext, I expect
that what one will have to do is to alter the column to change its
data type from citext to TEXT + collation.

Am I missing something here?

Thanks,

David

--
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] WITH RECUSIVE patches 0723

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

> Martijn van Oosterhout <kleptog@svana.org> writes:
>> Since the problem is using the result of a WITH clause more than
>> once, would it be sufficient to simply detect that case and bail?
>> You don't want materialisation is most cases, there's just a few
>> where it is needed.

Tom> Really? I tried googling to see what other people thought that
Tom> the WITH clause was for, and the first relevant hit I got was
Tom> this one: http://www.oracle-developer.net/display.php?id=212
Tom> which certainly treats it as a key part of the feature.

Try searching for "common table expression".

--
Andrew (irc:RhodiumToad)

--
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-fr-generale] enlever un langage

Bonjour,

William Dode wrote:
> Bonjour,
>
> Lorsque je fais un pg_dump il m'ajoute
>
> CREATE FUNCTION plpython_call_handler() RETURNS language_handler
> AS '$libdir/plpython', 'plpython_call_handler'
> LANGUAGE c;
>
>
> Et du coup quand je restaure cette base ailleur il m'indique
>
> doit être super-utilisateur pour créer le langage de procédur
> es « plpythonu »
>
> Hors je n'utilise pas plpython, j'avais fait des tests avec il
> y a longtemps, ce doit être pour ça qu'il reste présent.
> J'aimerai savoir comment je peux l'enlever de ma base initiale pour
> qu'ils ne vienne plus polluer mes pg_dump... Et également comme je peux
> savoir s'il est présent ou pas dans une base.

postgres=# select lanname from pg_language ;
lanname
----------
internal
c
sql
(3 rows)

>
> J'ai le même problème avec plpgsql, que j'utilise parfois dans d'autres
> bases par contre...
>
> Merci des tuyaux
>

Stéphane Bunel.

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

Re: [GENERAL] Clone a database to other machine

Hi,

Yes, I'm able to pg_dump the database to other server. And database is
working fine there.

But, actually I want to keep both the Databse in Sync. I want clone db
to get the data from Master in certain intervals.

DB dump size is 3 GB.

Thanks and regards,

-----Original Message-----
From: Francisco Reyes [mailto:lists@stringsutils.com]
Sent: Monday, July 28, 2008 10:42 PM
To: Garg, Manjit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Clone a database to other machine

On 12:44 pm 07/28/08 "Garg, Manjit" <mgarg@corbus.com> wrote:
> I'm stuck to an issue while cloning the pgsql database, can you
> please help, or give any docs to help out.

What is the issue?

> Query - Trying to have same database on two seprate linux servers.

Have you been able to pg_dump from the source DB and then read it in the
target?

Are you trying to do replication or you just want point in times?
How large is the database?

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

[GENERAL] Connecting to an existing transaction state.

Hello,

I'm in the process of creating a set of scripts for testing certain locking features in an application.
What I would like to do:
1. Start a connection from machine-01 through the m01-s1.sql script.
2.While (1) is running, start another transaction on the same database from machine-02 using m02-s1.sql.

At this point in time, there are two open transactions on certain tables in the same database.

3. Using m01-s2.sql I would like to execute a certain SQL statement – BUT within the scope of the transaction begun by m01-s1.sql.
4. Current situation: Since there are several .sql scripts, each getting its own connection and executing sql stmts – they are not aware of activities of the other scripts (i.e. the open transactions).
5. What I'd like to do: After a transaction has been started from a machine, I should be able to save the transaction reference (id?) temporarily somewhere.
6. The next statement (new .sql file) that wishes to execute within the scope of the above transaction – should be able to get the transaction reference (id) and latch onto it in its current state. This way it continues to perform as part of a whole – rather than only executing the statements that it had.

Any guidance in this will help.

Cheers!
AlexiG

__________________________________________________________
Not happy with your email address?.
Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html

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

Re: [GENERAL] why my postgresql auto crashed???

I'm sorry for the lack of information given in this mail;
Postgresql: 8.3.3
System:Linux 2.6.9-55.ELsmp
Install: I compile it myself

Others:
the same command, it works fine on another machine(called A):(

I EXPLAIN the sql machine A and machine B, I found that on machine B, it
never use index when command execute, but on machine A, index used:((,
so I "set enable_seqscan TO off;" on machine B, it's works beacause of
the usage of index!!!

I don't konw why.

thanks all, any help is appreciated.

On Mon, 2008-07-28 at 16:32 +0800, Craig Ringer wrote:
> Hi
>
> When creating a new thread (asking a new question, etc) on a mailing
> list please create a new message rather than replying to an existing
> one. It helps people reading the mailing list keep track.
>
> > so, I check the pg_log
> > ------------
> > TRAP: FailedAssertion("!(file->curFile >= 0)", File: "buffile.c", Line:
> > 317)
> > LOG: server process (PID 4121) was terminated by signal 6: Aborted
>
> This `postgres' backend detected a problem and terminated its self by
> calling abort().
>
> This forced the postmaster to terminate all other backends and restart
> the whole server.
>
> You omitted some very important information from your post, including:
>
> - Your PostgreSQL version
> - Your operating system
> - The version of your operating system
> - How you installed PostgreSQL and where you got it from
> - Whether the problem is repeatable, ie does the server crash every time
> you issue the problem command? Sometimes? Only ever happened once?
>
> --
> Craig Ringer
>

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

[COMMITTERS] prefix - prefix: make deb target with automatic orig archive handling

Log Message:
-----------
make deb target with automatic orig archive handling

Modified Files:
--------------
prefix:
Makefile (r1.5 -> r1.6)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/Makefile.diff?r1=1.5&r2=1.6)

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

Re: [PERFORM] how does pg handle concurrent queries and same queries

Faludi Gábor wrote:

> . Why does the second and the later queries take the whole on second
> if the dataset is the same . Shouldn't PG realise that the query is the same
> so i give the user the same resultset ?

That would require a result cache. I don't know if Pg even has a query
result cache - I don't think so, but I'm not sure. Even if it does, it'd
still only be useful if the queries were issued under *exactly* the same
conditions - in other words, no writes had been made to the database
since the cached query was issued, and the first query had committed
before the second began (or was read-only). Additionally, no volatile
functions could be called in the query, because their values/effects
might be different when the query is executed a second time. That
includes triggers, etc.

Since 7.4 doesn't do lazy xid allocation it can't really tell that
nothing has been changed since the previous query was cached. So, if I'm
not missing something here, a query result cache would be useless anyway.

> . How do I know if one query blocks the other ?

Examination of pg_catalog.pg_locks is certainly a start. It's trickier
with lots of short-running queries, though.

> . Is there a way to log the long running queries in 7.4 ? If not is
> it available in any newer version ?

It's certainly available in 8.3, as log_min_duration_statement in
postgresql.conf . You can find out if it's in 7.4, and if not what
version it was introduced in, by looking through the documentation for
versions 7.4 and up.

--
Craig Ringer

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

Re: [HACKERS] Protocol 3, Execute, maxrows to return, impact?

I replied to this post, yesterday. Yet I don't see my reply appear,
could it have been caught in a spamfilter or something?
--
Sincerely,
Stephen R. van den Berg.

"Even if man could understand women, he still wouldn't believe it."

--
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] why my postgresql auto crashed???

Hi

When creating a new thread (asking a new question, etc) on a mailing
list please create a new message rather than replying to an existing
one. It helps people reading the mailing list keep track.

> so, I check the pg_log
> ------------
> TRAP: FailedAssertion("!(file->curFile >= 0)", File: "buffile.c", Line:
> 317)
> LOG: server process (PID 4121) was terminated by signal 6: Aborted

This `postgres' backend detected a problem and terminated its self by
calling abort().

This forced the postmaster to terminate all other backends and restart
the whole server.

You omitted some very important information from your post, including:

- Your PostgreSQL version
- Your operating system
- The version of your operating system
- How you installed PostgreSQL and where you got it from
- Whether the problem is repeatable, ie does the server crash every time
you issue the problem command? Sometimes? Only ever happened once?

--
Craig Ringer

--
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] patch: Add a separate TRUNCATE permission

Robert Haas napsal(a):
> Here's a patch implementing the TODO item "Add a separate TRUNCATE
> permission". Hopefully I found all the bits that needed to be
> modified to make this work.
>
> Any feedback appreciated.
>

Added to the next commit fest patch list.
http://wiki.postgresql.org/wiki/CommitFest:2008-09

Zdenek

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

Re: [PERFORM] how does pg handle concurrent queries and same queries

Hi,

here is what the original query was which was obviously nonsense :
EXPLAIN ANALYZE SELECT DISTINCT letoltes.cid, s.elofordulas FROM letoltes
INNER JOIN (select letoltes.cid, count(letoltes.cid) AS elofordulas FROM
letoltes GROUP BY cid) s ON s.cid=letoltes.cid ORDER BY s.elofordulas DESC
LIMIT 5;

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------
Limit (cost=73945.35..73945.65 rows=5 width=12) (actual
time=4191.396..4351.966 rows=5 loops=1)
-> Unique (cost=73945.35..77427.99 rows=58800 width=12) (actual
time=4191.390..4351.956 rows=5 loops=1)
-> Sort (cost=73945.35..75106.23 rows=464351 width=12) (actual
time=4191.386..4283.545 rows=175944 loops=1)
Sort Key: s.elofordulas, letoltes.cid
-> Merge Join (cost=9257.99..30238.65 rows=464351 width=12)
(actual time=652.535..2920.304 rows=464351 loops=1)
Merge Cond: ("outer".cid = "inner".cid)
-> Index Scan using idx_letoltes_cid on letoltes
(cost=0.00..12854.51 rows=464351 width=4) (actual time=0.084..1270.588
rows=464351 loops=1)
-> Sort (cost=9257.99..9258.73 rows=294 width=12)
(actual time=652.434..810.941 rows=464176 loops=1)
Sort Key: s.cid
-> Subquery Scan s (cost=9242.26..9245.94
rows=294 width=12) (actual time=651.343..652.028 rows=373 loops=1)
-> HashAggregate (cost=9242.26..9243.00
rows=294 width=4) (actual time=651.339..651.661 rows=373 loops=1)
-> Seq Scan on letoltes
(cost=0.00..6920.51 rows=464351 width=4) (actual time=0.014..307.469
rows=464351 loops=1)
Total runtime: 4708.434 ms
(13 sor)

However after fixing the query this is 1/4 th of the time but still blocks
the site :

EXPLAIN ANALYZE SELECT DISTINCT letoltes.cid, count(letoltes.cid) AS
elofordulas FROM letoltes GROUP BY cid ORDER BY elofordulas DESC LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------
Limit (cost=9255.05..9255.09 rows=5 width=4) (actual time=604.734..604.743
rows=5 loops=1)
-> Unique (cost=9255.05..9257.26 rows=294 width=4) (actual
time=604.732..604.737 rows=5 loops=1)
-> Sort (cost=9255.05..9255.79 rows=294 width=4) (actual
time=604.730..604.732 rows=5 loops=1)
Sort Key: count(cid), cid
-> HashAggregate (cost=9242.26..9243.00 rows=294 width=4)
(actual time=604.109..604.417 rows=373 loops=1)
-> Seq Scan on letoltes (cost=0.00..6920.51
rows=464351 width=4) (actual time=0.022..281.413 rows=464351 loops=1)
Total runtime: 604.811 ms

here is the table :
\d letoltes
TĂĄbla "public.letoltes"
Oszlop | TĂ­pus | MĂłdosĂ­tĂł
--------+---------+------------------------------------------------
id | integer | not null default nextval('letoltes_seq'::text)
cid | integer |
Indexes:
"idx_letoltes_cid" btree (cid)
"idx_letoltes_id" btree (id)

select count(1) from letoltes;
count
--------
464351

VACUM ANALYZE runs overnight every day.

thanks,
Gabor

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Claus Guttesen
Sent: Monday, July 28, 2008 8:56 AM
To: Faludi Gábor
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] how does pg handle concurrent queries and same
queries

> I have taken over the maintenance of a server farm , recently. 2 webserver
> on db server. They are quite powerful 2 processor xeon w/ 6Gig of ram .
>
> Couple of days ago we had a serious performance hit and the db server (pg.
> v7.4) was overloaded w/ something in a way that operating system was
almost
> not able to respond or in cases it did not.
>
> After some analysis i suspect that there is a query that takes up to 1
> second and that is the cause. Upon each page loading this query fires and
> takes the one second and blocks the page to load completly . The load was
> roughly ~300 connections in one minute .
>
> So here are my questions :
>
> . Why does the second and the later queries take the whole on
second
> if the dataset is the same . Shouldn't PG realise that the query is the
same
> so i give the user the same resultset ?
>
> . How do I know if one query blocks the other ?
>
> . Is there a way to log the long running queries in 7.4 ? If not
is
> it available in any newer version ?

Can you post the queries? Can you provide an 'analyze explain'? Do you
perform a 'vacuum analyze' on a regular basis?

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

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

No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.138 / Virus Database: 270.5.6/1576 - Release Date: 2008.07.27.
16:16

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

Re: [HACKERS] WITH RECUSIVE patches 0723

> At David's request I've been looking through this patch.
>
> Regarding documentation: if it would help, I can write some; I have
> already made a start on writing down what is going on internally in
> order to understand it myself.
>
> I've found three more bugs so far:
>
> 1)
>
> create view v2(id) as values (1);
> with recursive t(id) as (select id from v2
> union all select id+1 from t where id < 5)
> select * from t;
> ERROR: could not open relation 1663/16384/24588: No such file or directory
>
> Here it seems that rewriting is simply not being applied to CTEs where
> a recursive clause is present; the reference to "v2" remains in the
> query up until execution time, at which point it errors out (in
> ExecInitSeqScan called from InitPlan).
>
> 2)
>
> with recursive t(id) as (values (1)
> union all select id+1 from t where id < 5
> union all values (2))
> select * from t;
> ERROR: table "t" has 0 columns available but 1 columns specified
>
> This seems to be caused by incorrect assumptions in checkWellFormedCte
> and checkCteSelectStmt (which should have been rejecting the query).
> The query tree as seen by checkWellFormedCte here is (values(1) union
> all select ...) union all (values (2)), and when the left subtree is
> passed to checkCteSelectStmt, it believes it to be non-recursive due
> to the lack of any From clause. The unexpected error is produced
> later.

Included patches from Yoshiyuki should fix 1) and 2). I also add your
SQLs to the regression test. Thanks.

> 3)
>
> with recursive t(id)
> as (values (1)
> union all select t.id+1
> from t left join (values (1)) as s(x) on (false)
> where t.id < 5)
> select * from t;
> id
> ----
> 1
> 2
> (2 rows)
>
> This behaviour is clearly intentional, since the entire mechanism of
> estate->es_disallow_tuplestore exists for no other reason, but it
> seems to me to be clearly wrong. What is the justification for it?

Yes, this is due to prevent infinit recursion caused by following
case for example.

CREATE TABLE test (a TEXT, b TEXT);

INSERT INTO test VALUES ('aaa', 'bbb');
INSERT INTO test VALUES ('bbb', 'ccc');
INSERT INTO test VALUES ('ddd', 'eee');
INSERT INTO test VALUES ('ccc', 'qqq');

WITH RECURSIVE x AS (
SELECT * FROM test WHERE a = 'aaa'

UNION ALL

SELECT test.* FROM x LEFT JOIN test on test.a = x.b
) SELECT * FROM x;

Now we think that we were wrong. This type of query should run into
infinit recursion and it's user's responsibility that he does not make
such a query.

Another idea would be prohibiting *any* outer joins in the recursive
term (DB2 style), but this may be overkill.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Re: [GENERAL] copy ... from stdin csv; and bytea

Tom Lane wrote:
> Klint Gore <kgore4@une.edu.au> writes:
> > David Wilson wrote:
> >> I'm not certain how to check the actual byte width of a column within a
> >> row,
>
> > select length(bytea_field) from table
>
> If you want the actual on-disk footprint, use pg_column_size()
>
>
Size on disk would have the compression from the default storage =
extended wouldn't it?

I verified it for myself manually anyway.

copy (select * from original limit 5) to stdout with csv;

create table foo (like original);
alter table foo alter column bytea_field set storage external;
copy foo from stdin with csv;

select |reltoastrelid from pg_class where relanem = 'original'

found the file for it and looked at it with a hex viewer.
|
klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

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

Andrew Dunstan wrote:
> Kevin Grittner wrote:
>>>>> "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
>>> We really need a more reliable way of detecting that a file has been
>>> fully copied.
>>
>> In our scripts we handle this by copying to a temp directory on the
>> same mount point as the archive directory and doing a mv to the
>> archive location when the copy is successfully completed. I think
>> that this even works on Windows. Could that just be documented as a
>> strong recommendation for the archive script?
>
> Needs testing at least. If it does in fact work then we can just adjust
> the docs and be done

Yeah.

> - or maybe provide a .bat file or perl script that
> would work as na archive_command on Windows.

We're not talking about archive_command. We're talking about the thing
that copies files to the directory that pg_standby polls.

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

[GENERAL] why my postgresql auto crashed???

hi, all
when I do the command from a sql file by psql client, I got the message:
----
psql:/home/zhay/insert.sql:8: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:/home/zhay/insert.sql:8: connection to server was lost
----
my sql file is :
set search_path to lives;

insert into store_all select c.*, z.t from (
select b.*, y.t from (
select a.*, x.t from store a
left join (select pid,
array_to_string(array_accum(anchor), ' ') as t
from recommend group by pid)x on x.pid = a.id
)b left join (select pid, array_to_string(array_accum(anchor), '
') as
t from tag group by pid)y on y.pid = b.id
)c left join (select pid, array_to_string(array_accum(anchor), ' ') as t
from categorie group by pid)z on z.pid = c.id;

so, I check the pg_log
------------
TRAP: FailedAssertion("!(file->curFile >= 0)", File: "buffile.c", Line:
317)
LOG: server process (PID 4121) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2008-07-27
07:36:16 UTC
LOG: database system was not properly shut down; automatic recovery in
progress
FATAL: the database system is in recovery mode
LOG: redo starts at D/3FB00BC0
LOG: record with zero length at D/3FB59898
LOG: redo done at D/3FB59868
LOG: last completed transaction was at log time 2008-07-27
07:40:53.70866+00
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
------------

thanks all.

regards.

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