Friday, August 29, 2008

[GENERAL] advisory locks in stored procedures

Hello,

 

I’ve got a large multi-process/multi-threaded VOIP application, that uses UDP ports all over the place (internal communication, SIP ports, RTP ports, etc).

 

Because of the opportunity for port duplication, we decided to have the ports allocated from a table/stored procedure from our postgres database; and to avoid duplication, we used advisory locks inside the stored procedure.

 

It’s a simple function; it does a few un-related things, but the meat of it is:

 

--snip—

 

create or replace function fc_system_next_session() returns smallint as $$

declare

       u_port smallint;

begin

       perform pg_advisory_lock(1);

 

       select into u_port id from udp_ports where status = 0 limit 1;

       if not found then

              perform perform pg_advisory_unlock(1);

              return 0;

       end if;

 

       update udp_ports set status = 1 where id = u_port;

       if not found then

              perform perform pg_advisory_unlock(1);

              return 0;

       end if;

 

.. do some other stuff here ..

 

       perform pg_advisory_unlock(1);

 

       return u_port;

end;

$$ language plpgsql;

 

--snip—

 

But this doesn’t seem to work- I end up getting duplicate ports returned when the application starts, and forks()’s off processes.

 

Changing the “perform pg_advisory_lock(1);“ line to “lock table udp_ports in SHARE ROW EXCLUSIVE mode;“ makes the function work fine.

 

I realize I can use a select .. for update, but I’d prefer to use advisory locks if possible.

 

Do advisory locks work inside functions?

 

Could it be related somehow to PERFORM instead of SELECT?

 

Any thoughts would be greatly appreciated.

 

Cheers,

 

Mike

Re: [ADMIN] compat-postgresql-libs not in the repo?

Hello,

On Fri, 2008-08-29 at 11:42 -0400, slamp slamp wrote:
> answered my own question. i guess its just harder to find on the site
> now since the yum repo went live. for anyone else looking, they are in
> the following link.
>
> http://www.postgresql.org/ftp/binary/v8.2.9/linux/rpms/redhat/rhel-5-i386/

FWIW, it is not a URL for the *yum repository*
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [ADMIN] compat-postgresql-libs not in the repo?

Hi,

On Fri, 2008-08-29 at 11:36 -0400, slamp slamp wrote:
> Hi I am trying to install postgresql on centos 5.2 using the yum repo
> provided by postgresql. How can I get compat-postgresql-libs?

Which PostgreSQL version actually? For 8.3, we already have that package
in the repo.

http://yum.pgsqlrpms.org/8.3/redhat/rhel-5-x86_64/repoview/letter_c.group.html
or
http://yum.pgsqlrpms.org/8.3/redhat/rhel-5-i386/repoview/letter_c.group.html

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [ADMIN] compat-postgresql-libs not in the repo?

answered my own question. i guess its just harder to find on the site
now since the yum repo went live. for anyone else looking, they are in
the following link.

http://www.postgresql.org/ftp/binary/v8.2.9/linux/rpms/redhat/rhel-5-i386/

On Fri, Aug 29, 2008 at 11:36 AM, slamp slamp <slackamp@gmail.com> wrote:
> Hi I am trying to install postgresql on centos 5.2 using the yum repo
> provided by postgresql. How can I get compat-postgresql-libs?
>

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

Re: [HACKERS] User defined I/O conversion casts

Tom Lane wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>> Patch attached. I'm using a magic OID "1" in pg_cast.castfunc field to
>> mark these extra I/O conversion casts.
>
> Ugh. That's really unacceptable (doesn't it make the oidjoins
> regression test fail?),

Yeah, it does if you create a cast like that. And pg_dump/restore will
need to be taught about it as well.

> I think that as things stand at the moment, you can get I/O conversion
> casts to work for a user-defined type by making it be of string
> category. Maybe that would be an easier way to get the effect.

Hmm. That would be sensible only for types that are, well, strings. Not
if you wanted to make the cast from, say, int4 to text implicit.

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

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

[ADMIN] compat-postgresql-libs not in the repo?

Hi I am trying to install postgresql on centos 5.2 using the yum repo
provided by postgresql. How can I get compat-postgresql-libs?

--
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] Dumping/Restoring with constraints?

On Fri, Aug 29, 2008 at 11:29:55PM +0800, Phoenix Kiula wrote:
> So how can I change the locale on my local OSX machine? This could be
> it. Thanks!

Before you start restoring, put a LANG environment variable. So
something like

LANG=en_US.UTF-8 psql

or else

LANG=en_US.UTF-8; export LANG
[other stuff]

A

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

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

[pgadmin-hackers] SVN Commit by dpage: r7449 - branches/REL-2_0_0

Author: dpage

Date: 2008-08-29 16:32:48 +0100 (Fri, 29 Aug 2008)

New Revision: 7449

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

Log:
pgAgent 2.0.0 branch


Added:
branches/REL-2_0_0/pgagent/

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

Re: [GENERAL] Dumping/Restoring with constraints?

Thanks so much! I think we may be on to something:


On the pg_dump machine:

LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

On the pg_restore machine:


LANG=
LC_COLLATE="C"
LC_CTYPE="UTF-8"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL=

So how can I change the locale on my local OSX machine? This could be
it. Thanks!

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

[pgadmin-hackers] SVN Commit by dpage: r7448 - trunk/www/download

Author: dpage

Date: 2008-08-29 16:28:44 +0100 (Fri, 29 Aug 2008)

New Revision: 7448

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

Log:
Add pgAgent downloads

Added:
trunk/www/download/pgagent.php
Modified:
trunk/www/download/index.php
trunk/www/download/menu.php

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

Re: [PERFORM] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

James Mansion wrote:
> I can't see how an OS can lie to processes about memory being allocated
> to them and not be ridiculed as a toy, but there you go. I don't think
> Linux is the only perpetrator - doesn't AIX do this too?

This is a leftover from the days of massive physical modeling (chemistry, physics, astronomy, ...) programs written in FORTRAN. Since FORTRAN didn't have pointers, scientists would allocate massive three-dimensional arrays, and their code might only access a tiny fraction of the memory. The operating-system vendors, particularly SGI, added features to the various flavors of UNIX, including the ability to overcommit memory, to support these FORTRAN programs, which at the time were some of the most important applications driving computer science and computer architectures of workstation-class computers.

When these workstation-class computers evolved enough to rival mainframes, companies started shifting apps like Oracle onto the cheaper workstation-class computers. Unfortunately, the legacy of the days of these FORTRAN programs is still with us, and every few years we have to go through this discussion again.

Disable overcommitted memory. There is NO REASON to use it on any modern server-class computer, and MANY REASONS WHY IT IS A BAD IDEA.

Craig

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

Re: [GENERAL] Dumping/Restoring with constraints?

On Friday 29 August 2008 8:12:35 am Phoenix Kiula wrote:
> On 8/29/08, Andrew Sullivan <ajs@commandprompt.com> wrote:
> > Hrm. Well, what about the locale, as I suggested? I have no idea if
>
> How should I check for the locale?

On Linux:

aklaver@tucker:~$ locale
LANG=en_US.UTF-8
LANGUAGE=en
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=


--
Adrian Klaver
aklaver@comcast.net

--
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] MySQL LAST_INSERT_ID() to Postgres

On 29/08/2008 05:45, Tom Lane wrote:

> A general comment on those pages is that the tabular lists of functions
> are intended to give one-liner descriptions of what the functions do.
> For cases where a one-liner isn't sufficient, there's a sentence or a
> paragraph following the table.
>
> I don't find this layout remarkably intuitive myself, but I wonder
> whether anyone has a concrete plan for making it better?

Maybe a simple "see further below" or the like after the one-liner?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

--
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] Dumping/Restoring with constraints?

On Fri, Aug 29, 2008 at 11:12:35PM +0800, Phoenix Kiula wrote:
>
> How should I check for the locale?

As the user that you issue the pg_dump with on the machine you
generated it, and as the user you're trying to use to restore the
data, at the command line type

$ locale

You end up with something like this:

stovepipe:~ ajs$ locale
LANG="en_CA.UTF-8"
LC_COLLATE="en_CA.UTF-8"
LC_CTYPE="en_CA.UTF-8"
LC_MESSAGES="en_CA.UTF-8"
LC_MONETARY="en_CA.UTF-8"
LC_NUMERIC="en_CA.UTF-8"
LC_TIME="en_CA.UTF-8"
LC_ALL=

I can't remember whether the client encoding is going to be affected
by this during dump and restore, and whether in these versions that is
captured in the dump file.

A

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

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

Re: [HACKERS] New FSM allocation policy

Gregory Stark <stark@enterprisedb.com> writes:
> One idea, we could scan the rest of the current page and use the first match.

> Another, given the way your tree structure works you can also descend the tree
> with a "target" page. You can find the first page with enough free space after
> the target page if there are any. (Take left branch if it's > target and has
> enough free space else take right branch if there's enough free space else
> take left branch).

I think the trick here is how to also preserve the property that
different backends tend to be inserting into different pages. There may
be no very good way to do that without maintaining some shared state,
ie the last page handed out.

However, it would probably be sufficient to do that for some pretty
small number of tables, allowing a fixed-size shared memory area to be
sufficient.

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

[pgadmin-hackers] SVN Commit by dpage: r7447 - branches

Author: dpage

Date: 2008-08-29 16:13:43 +0100 (Fri, 29 Aug 2008)

New Revision: 7447

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

Log:

Added:
branches/REL-2_0_0/

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

Re: [GENERAL] Dumping/Restoring with constraints?

On 8/29/08, Andrew Sullivan <ajs@commandprompt.com> wrote:
>
>
> Hrm. Well, what about the locale, as I suggested? I have no idea if


How should I check for the locale?

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

[pgadmin-hackers] SVN Commit by dpage: r7446 - trunk/pgagent

Author: dpage

Date: 2008-08-29 16:09:19 +0100 (Fri, 29 Aug 2008)

New Revision: 7446

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

Log:
Even more minimal linking!

Modified:
trunk/pgagent/CMakeLists.txt

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

Re: [HACKERS] User defined I/O conversion casts

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Patch attached. I'm using a magic OID "1" in pg_cast.castfunc field to
> mark these extra I/O conversion casts.

Ugh. That's really unacceptable (doesn't it make the oidjoins
regression test fail?),

I think that as things stand at the moment, you can get I/O conversion
casts to work for a user-defined type by making it be of string
category. Maybe that would be an easier way to get the effect.

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

[pgadmin-hackers] SVN Commit by dpage: r7445 - trunk/pgagent

Author: dpage

Date: 2008-08-29 15:57:14 +0100 (Fri, 29 Aug 2008)

New Revision: 7445

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

Log:
Add licence and readme files to the installer

Modified:
trunk/pgagent/CMakeLists.txt

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

Re: [GENERAL] 8.3.1 query plan

Tom Lane wrote:
> Steve Clark <sclark@netwolves.com> writes:
>
>>Tom Lane wrote:
>>
>>>Consider testing the conditions on A at the top level, instead of
>>>redundantly checking them inside the sub-query on B.
>
>
>>Thanks for the response Tom, I am a SQL neophyte, so I'll try to
>>rework the query.
>
>
> What I meant to suggest was just
>
> explain insert into myevents select * from t_unit_event_log a where
> a.event_status = 1 and a.event_ref_log_no IS NOT NULL
> and a.event_log_no not in (select event_log_no from myevents)
> and exists (select b.event_log_no from myevents b
> where a.event_ref_log_no = b.event_log_no)
>
> ie, pull everything out of the subquery that doesn't depend on B.
>
> Although, looking at it in this form, it seems like you'd be well
> advised to then replace the EXISTS with an IN:
>
> ... and a.event_ref_log_no in (select b.event_log_no from myevents b)
>
> Although those two forms should be equivalent, reality is that current
> releases of PG are generally smarter about optimizing IN than EXISTS.
> (The difference should largely go away as of 8.4.)
>
> regards, tom lane
>
>
Thanks again Tom.

It appears to me that in both cases the planner ends up doing a seq scan of the myevents table
for each proposed new insertion from the t_unit_event_log table which make the query take to
long to be feasible. What I need to do is somehow immediately eliminate the candidate row from
the t_unit_event_log if the row is already in the myevents table, but I am at a loss as to how to do
that.

Regards,
Steve

srm2=# explain insert into myevents select * from t_unit_event_log a where
srm2-# a.event_status = 1 and a.event_ref_log_no IS NOT NULL
srm2-# and a.event_log_no not in (select event_log_no from myevents)
srm2-# and exists (select b.event_log_no from myevents b
srm2(# where a.event_ref_log_no = b.event_log_no);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using indx_tuel_usn_ec_es on t_unit_event_log a (cost=31711.73..3597826539.34 rows=51479 width=145)
Index Cond: (event_status = 1)
Filter: ((event_ref_log_no IS NOT NULL) AND (subplan) AND (NOT (subplan)))
SubPlan
-> Materialize (cost=31711.73..42857.85 rows=830612 width=4)
-> Seq Scan on myevents (cost=0.00..28041.12 rows=830612 width=4)
-> Index Scan using indx1myevents on myevents b (cost=0.00..8.37 rows=1 width=4)
Index Cond: ($1 = event_log_no)
(8 rows)

srm2=# explain insert into myevents select * from t_unit_event_log a where
a.event_status = 1 and a.event_ref_log_no IS NOT NULL
and a.event_log_no not in (select event_log_no from myevents)
and a.event_ref_log_no in (select b.event_log_no from myevents b);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Merge IN Join (cost=40821629.90..3265326013.41 rows=39021 width=145)
Merge Cond: (a.event_ref_log_no = b.event_log_no)
-> Index Scan using indx_tuel_erln on t_unit_event_log a (cost=31711.73..8616020218.25 rows=102959 width=145)
Filter: ((event_ref_log_no IS NOT NULL) AND (event_status = 1) AND (NOT (subplan)))
SubPlan
-> Materialize (cost=31711.73..42857.85 rows=830612 width=4)
-> Seq Scan on myevents (cost=0.00..28041.12 rows=830612 width=4)
-> Materialize (cost=119646.12..130028.77 rows=830612 width=4)
-> Sort (cost=119646.12..121722.65 rows=830612 width=4)
Sort Key: b.event_log_no
-> Seq Scan on myevents b (cost=0.00..28041.12 rows=830612 width=4)
(11 rows)


--
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] Dumping/Restoring with constraints?

On Fri, Aug 29, 2008 at 10:26:14PM +0800, Phoenix Kiula wrote:
> Thanks Andrew.
>
> On the server (the DB to be dumped) everything is "UTF8".
>
> On my home server (where I would like to mirror the DB), this is the output:

Hrm. Well, what about the locale, as I suggested? I have no idea if
it's relevant here, but give it a whack. Other than that, I'm not
sure. You might want to troll the release notes to see if there was
an encoding bug fixed in the intermediate releases between
8.2.whatever-you-converted-on and 8.2.current.

A


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

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

Re: [GENERAL] pg_dumpall problem when roles have default schemas

Tom Lane wrote:
> btober@ct.metrocast.net writes:
>> Thus, when piping the output (from legacy host 192.168.2.2) to
>> populate the newly initialized cluster, by way of running (on the new
>> host 192.168.2.3)
>> pg_dumpall -h 192.168.2.2|psql
>> an error occurs in that first section when the script attempts to
>> set a role-specific search path ... because the schema named in the
>> search path hasn't been created yet.
>
> Could we see a complete example?
>
> Recent versions of the backend are not supposed to throw hard errors in
> this situation, because of exactly that hazard. For instance:
>
> regression=# create role joe;
> CREATE ROLE
> regression=# alter role joe set search_path to foo, bar;
> NOTICE: schema "foo" does not exist
> NOTICE: schema "bar" does not exist
> ALTER ROLE
> regression=#
>
> which AFAICS is exactly what will happen while restoring a pg_dumpall
> dump.

Ah, I understand. I think.

What I saw was not literally and "error", but rather a "notice": The
"alter role ... set search_path" statement doesn't actually fail.

The real problem is my preconceived notion. I was thinking in terms of,
say, a database insert to a table with a foreign key, i.e., that since
the schema to be set doesn't exist, the the statement should actually fail.

The implication of your illustration above is that the "alter role ...
set search_path" statement completes successfully even though it is
setting an invalid search path (invalid because the schema referenced in
the search path to be set does not exist at the time the statement is
executed).

So this behavior, which seems a little counter intuitive, actually makes
for a robust end result.

--
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] Dumping/Restoring with constraints?

Thanks Andrew.

On the server (the DB to be dumped) everything is "UTF8".

On my home server (where I would like to mirror the DB), this is the output:


=# \l
List of databases
Name | Owner | Encoding
-----------+-----------------+-----------
postgres | postgres | SQL_ASCII
pkiula | pkiula_pkiula | UTF8
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)

This is a fresh install as you can see. The database into which I am
importing ("pkiula") is in fact listed as UTF8! Is this not enough?

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

Re: [pgadmin-support] Detected error in pgAdmin III v1.8.4

Hello

its not a bug - its a feature :)
go to File->Options, Preferences and check "Show Users For Privileges".

Eduardo Sá dos Reis rašė:
> Hello,
>
> I installed pgAdmin III v1.8.4 released and
> detected a problem with manage privileges.
> The problem is in the properties of a table on privileges tab.
> The roles not appear in the "role's combobox". Just role public
> appear. This way I can't add privileges for roles.
>
>
>
> Eduardo Sá dos Reis
> eduardoreis@pjf.mg.gov.br <mailto:eduardoreis@pjf.mg.gov.br>


--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


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

Re: [pgsql-es-ayuda] Manual de instalación

Edwin, te pidió ayuda en "Español".
¿Podrías traducirle el manual?

atte.

El día 28 de agosto de 2008 17:12, Edwin Quijada
<listas_quijada@hotmail.com> escribió:
>
> Aca hay uno
> Run Installer
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> NEXT
> Por si acaso
> NEXT
> NEXT
> NEXT
> NEXT
>
> :P)
> *-------------------------------------------------------*
> *-Edwin Quijada
> *-Developer DataBase
> *-JQ Microsistemas
> *-809-849-8087
>
> * " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo comun"
> *-------------------------------------------------------*
>
>
> ________________________________
> From: mmayerling4@hotmail.com
> To: pgsql-es-ayuda@postgresql.org
> Subject: [pgsql-es-ayuda] Manual de instalación
> Date: Thu, 28 Aug 2008 15:37:01 +0000
>
>
>
>
>
>
>
>
> Buenos Días
>
>
> Amigos, espero que se encuentren muy bien, me gustaria que me ayudaran con un manual de instalación de postgresql en windows, si es posible que sea en español...
>
> Muchas Gracias
>
> Saludos
>
>
>
>
> Mayerling J.
> Molina V.
>
>
>
> Especialista de
> Desarrollo
>
> Telf.: (58-212) 953-2067 / 2365 / 3906
>
>
>
> Ext: 1106
>
> Fax: (58-212) 952-2549
>
>
>
>
> ________________________________
> ¡Atrévete con Abecedario, el juego del momento! Invita a tus contactos de Messenger y demuéstrales lo que sabes Abecedario
>
> _________________________________________________________________
> Get Windows Live and get whatever you need, wherever you are. Start here.
> http://www.windowslive.com/default.html?ocid=TXT_TAGLM_WL_Home_082008--
> TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda
>
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Left join anidados

Verny Mata escribió:
> Se puede hacer asi??:
>
> select * from
> matricula_asignatura a,
> calificacion b
> where
> a.asignatura = b.asignatura

Claro, pero eso no es un outer join, sino inner join.


--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Endurecerse, pero jamás perder la ternura" (E. Guevara)
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

[pgadmin-hackers] SVN Commit by dpage: r7444 - trunk/pgadmin3/docs/en_US

Author: dpage

Date: 2008-08-29 15:08:30 +0100 (Fri, 29 Aug 2008)

New Revision: 7444

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

Log:
Clarify that pgAgent must be downloaded separately.


Modified:
trunk/pgadmin3/docs/en_US/extend.html

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

[pgadmin-hackers] SVN Commit by dpage: r7443 - trunk/pgadmin3/docs/en_US

Author: dpage

Date: 2008-08-29 14:55:25 +0100 (Fri, 29 Aug 2008)

New Revision: 7443

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

Log:
Note that pgAgent is distributed separately.


Modified:
trunk/pgadmin3/docs/en_US/pgagent.html

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

[pgadmin-hackers] SVN Commit by dpage: r7442 - trunk/pgagent

Author: dpage

Date: 2008-08-29 14:49:02 +0100 (Fri, 29 Aug 2008)

New Revision: 7442

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

Log:
Add a README, explaining how to build pgAgent.


Added:
trunk/pgagent/README

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

Re: [PERFORM] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

david@lang.hm escribió:
> On Thu, 28 Aug 2008, Alvaro Herrera wrote:
>
>> david@lang.hm escribi?:
>>> On Thu, 28 Aug 2008, Scott Marlowe wrote:
>>
>>>> scenario 1: There's a postmaster, it owns all the child processes.
>>>> It gets killed. The Postmaster gets restarted. Since there isn't one
>>>
>>> when the postmaster gets killed doesn't that kill all it's children as
>>> well?
>>
>> Of course not. The postmaster gets a SIGKILL, which is instant death.
>> There's no way to signal the children. If they were killed too then
>> this wouldn't be much of a problem.
>
> I'm not saying that it would signal it's children, I thought that the OS
> killed children (unless steps were taken to allow them to re-parent)

Oh, you were mistaken then.

>>> well, if you aren't going through the postmaster, what process is
>>> recieving network messages? it can't be a group of processes, only one
>>> can be listening to a socket at one time.
>>
>> Huh? Each backend has its own socket.
>
> we must be talking about different things. I'm talking about the socket
> that would be used for clients to talk to postgres, this is either a TCP
> socket or a unix socket. in either case only one process can listen on
> it.

Obviously only one process (the postmaster) can call listen() on a given
TCP address/port. Once connected, the socket is passed to the
backend, and the postmaster is no longer involved in the communication
between backend and client. Each backend has its own socket. If the
postmaster dies, the established communication is still alive.


>>> and if the postmaster isn't needed for the child processes to write to
>>> the datastore, how are multiple child processes prevented from writing to
>>> the datastore normally? and why doesn't that mechanism continue to work?
>>
>> They use locks. Those locks are implemented using shared memory. If a
>> new postmaster starts, it gets a new shared memory, and a new set of
>> locks, that do not conflict with the ones already held by the first gang
>> of backends. This is what causes the corruption.
>
> so the new postmaster needs to detect that there is a shared memory
> segment out that used by backends for this database.

> this doesn't sound that hard,

You're welcome to suggest actual improvements to our interlocking
system, after you've read the current code and understood its rationale.


>> Any other signal gives it the chance to signal the children before
>> dying.
>
> are you sure that it's not going to die from a memory allocation error?
> or any other similar type of error without _always_ killing the children?

I am sure. There are no memory allocations in that code. It is
carefully written with that one purpose.

There may be bugs, but that's another matter. This code was written
eons ago and has proven very healthy.

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

--
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] Proposal: new border setting in psql

On Fri, 29 Aug 2008 06:55:45 -0400
"D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> On Fri, 29 Aug 2008 01:29:14 -0400
> I think that your scan may have been a bit too cursory. Those
> characters, while significant in ReST, only matter when used in very
> specific ways. The following works just fine in my ReST application.
>
> +----+-------+
> | id | name |
> +====+=======+
> | 8 | T'est |
> +----+-------+
> | 9 | T*est |
> +----+-------+
> | 10 | T\est |
> +----+-------+

Oops. I was wrong about this one. The backslash needs to be escaped.
It also means expanding the other rows to match so this is the corner
case. In fact, for one or two backslashes you can double it and remove
the space at the start and/or end which is not so bad.

I'm surprised that we don't have a general option to escape special
characters. Perhaps that's the next small enhancement.

darcy=# \pset escape \

For example. The general output filter I suggested previously could
also deal with that, of course.

--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

--
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] Cómo cambio el plan de ejecución de un query

"Carlos Beltrán V." escribió:

> Con el actual estado de la base de datos y el ejemplo antes descrito un
> EXPLAIN ANALYZE corrido en las dos versiones (8.2.6 y 8.3.3) da una
> diferencia abismal ya que en 8.3.3 entra a evaluar la función PL por
> cada linea de la tabla declaraciones_importacion.

Puedes cambiar el COST de la funcion para que intente evaluarla menos
veces. (Nota que si la funcion es "volatile" esto no puede hacerse).

Si eso no resulta, prueba poniendo un OFFSET 0 en el subselect, que
actua como una barrera de optimizacion (para que no aplane el
subselect).

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)
--
TIP 8: explain analyze es tu amigo

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

--- On Fri, 8/29/08, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> From: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Subject: Re: [pgsql-es-ayuda] Consulta sobre index
> To: "Lennin Caro" <lennin.caro@yahoo.com>
> Cc: pgsql-es-ayuda@postgresql.org
> Date: Friday, August 29, 2008, 1:34 PM
> Lennin Caro escribió:
>
> > --- On Thu, 8/28/08, Alvaro Herrera
> <alvherre@alvh.no-ip.org> wrote:
> >
> > > Lennin Caro escribió:
>
> > > > ahora mi preunta es:
> > > > 1-como se cual es el indice que usara el
> query plan?
> > >
> > > ¿Qué importancia tiene eso?
> >
> > queria saber si podia decidir por cual indice se
> resuelve la consulta,
> > con la finalidad de medir los tiempos de respuestas
> que se generarian
> > por cada indice para cuando cada indice este en un
> tablespace
> > diferente que se encuentren en disco diferente.
>
> No creo que sea un uso productivo de tu tiempo, pero puedes
> mover un
> indice a otro tablespace usando ALTER INDEX ... SET
> TABLESPACE.
>
> Si tienes discos de sobra, lo que podría ser recomendable
> es que crees
> un arreglo RAID y pongas toda la BD en el.
>
Ya los indices estan en tablespace diferentes y en discos diferentes, gracias por la sugerencia voy a intentarlo a ver como resultan las pruebas

gracias


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

Re: [GENERAL] Dumping/Restoring with constraints?

On Fri, Aug 29, 2008 at 01:53:28PM +0800, Phoenix Kiula wrote:

> My database encoding on BOTH the sides is UTF8. The dumped DB is UTF8.

Are you sure? I know people asked up-thread for the output of \l, but
I haven't seen it. If your encoding is UTF-8, then I don't understand
how you got non-UTF-8 characters in there, unless there's some bug in
the 8.2 series of which I'm unaware.

> The one I am importing into is also UTF8. So why is it showing me this
> error? Do I also have to look at stuff like "collation"?

You shouldn't have to. That's a constraint on sorting and such like.
It doesn't control what can get into the database.

Hmm. I wonder if the problem is the locale you're using for pg_dump
vs. what you're using when handing the data back in. If the locale
were different, it might be possible that your client_encoding was
different. I have no idea if this is the case, but you might want to
try it. Check your locale at the command line on each system.

> Also, how can I ensure that my dumped database on the server has only
> UTF8 data? What constraint or function can I use?

As I said before, if the database encoding is UTF-8, then it's
supposed to be _impossible_ that you get non-UTF-8 data in there.
That's the whole point of having the database encoding.

A

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

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

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

Lennin Caro escribió:

> --- On Thu, 8/28/08, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> > Lennin Caro escribió:

> > > ahora mi preunta es:
> > > 1-como se cual es el indice que usara el query plan?
> >
> > ¿Qué importancia tiene eso?
>
> queria saber si podia decidir por cual indice se resuelve la consulta,
> con la finalidad de medir los tiempos de respuestas que se generarian
> por cada indice para cuando cada indice este en un tablespace
> diferente que se encuentren en disco diferente.

No creo que sea un uso productivo de tu tiempo, pero puedes mover un
indice a otro tablespace usando ALTER INDEX ... SET TABLESPACE.

Si tienes discos de sobra, lo que podría ser recomendable es que crees
un arreglo RAID y pongas toda la BD en el.

--
Alvaro Herrera Valdivia, Chile Geotag: -39,815 -73,257
"[PostgreSQL] is a great group; in my opinion it is THE best open source
development communities in existence anywhere." (Lamar Owen)
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [HACKERS] code coverage patch

Peter Eisentraut wrote:
> Michelle Caisse wrote:
>> gcov gets confused when source files are generated. I eliminated
>> src/backend/bootstrap and ../parser from coverage analysis to avoid
>> errors of this type.
>
> The problem with those files is that the source file contains lines like this:
>
> #line 1042 "y.tab.c"
>
> but that source file does not exist, as it is renamed to gram.c.

This problem is now fixed, so the workaround in the coverage patch can
be dropped.


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

[SQL] pg_dump and "could not identify an ordering operator for type name"

Hi dudes. Im facing a problem with pg_dump,

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not identify an
ordering operator for type name
HINT: Use an explicit ordering operator or modify the query.
pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname as
indexname, pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef,
t.relnatts as indnkeys, i.indkey, i.indisclustered, c.contype,
c.conname, c.tableoid as contableoid, c.oid as conoid, (SELECT spcname
FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as
tablespace, array_to_string(t.reloptions, ', ') as options FROM
pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid =
i.indexrelid) LEFT JOIN pg_catalog.pg_depend d ON (d.classid =
t.tableoid AND d.objid = t.oid AND d.deptype = 'i') LEFT JOIN
pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid
= c.oid) WHERE i.indrelid = '7929896'::pg_catalog.oid ORDER BY indexname

Dumping of other databases works fine. Looks like a corrupted internal
table, isnt?

Any hints?

Gerardo

--
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] temp schemas

> On Fri, Aug 29, 2008 at 1:40 PM, Roberts, Jon
<Jon.Roberts@asurion.com>
> wrote:
>
> >> Why have you got thousands of them? If you are running with
thousands
> >> of active backends, may I suggest a connection pooler?
> >>
> >
> > I don't know. It looks like a bug to me where a temp table is
created
> > and dropped on commit but the next time the function executes and
> > creates a new temp table, it does this in another temp schema. It
does
> > this over and over until I have thousands of temp schemas that
aren't
> > used.
>
> Is this vanilla PG, or your Greenplum install?

Good memory. Both. GP is based on 8.2.6 while our PostgreSQL installs
are 8.3.0 and 8.3.1.

Here is an example from my local PG database:

C:\>psql elt0n elt0n
Welcome to psql 8.3.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.

elt0n=# select nspname from pg_namespace where nspname like 'pg_temp%';
nspname
---------
(0 rows)

elt0n=# create temporary table t1 on commit drop as select 'jon'::text
as col1;
SELECT
elt0n=# select nspname from pg_namespace where nspname like 'pg_temp%';
nspname
-----------
pg_temp_1
(1 row)

elt0n=# \c elt0n elt0n
You are now connected to database "elt0n".
elt0n=# create temporary table t1 on commit drop as select 'jon'::text
as col1;
SELECT
elt0n=# select nspname from pg_namespace where nspname like 'pg_temp%';
nspname
-----------
pg_temp_1
pg_temp_2
(2 rows)

elt0n=# select version();
version
-----------------------------------------------------
PostgreSQL 8.3.0, compiled by Visual C++ build 1400
(1 row)

elt0n=#


So now I have two pg_temp_% schemas that don't seem to get reused ever
and if I have lots of functions using temp tables, this really slows
down a connection to PG with pgAdmin.

Jon

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

--- On Thu, 8/28/08, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> From: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Subject: Re: [pgsql-es-ayuda] Consulta sobre index
> To: "Lennin Caro" <lennin.caro@yahoo.com>
> Cc: pgsql-es-ayuda@postgresql.org
> Date: Thursday, August 28, 2008, 3:49 PM
> Lennin Caro escribió:
> > este es el excenario
> >
> > -Se crea una tabla "tb1"
> > -Se crea una PK id "pk_id" (automaticamente
> se crea un indice a esta PK)
> > -se crea un indice "idx_id" indexando el
> campo id de la tabla
>
> Este segundo indice es redundante.
>
> > ahora mi preunta es:
> > 1-como se cual es el indice que usara el query plan?
>
> ¿Qué importancia tiene eso?

queria saber si podia decidir por cual indice se resuelve la consulta, con la finalidad de medir los tiempos de respuestas que se generarian por cada indice para cuando cada indice este en un tablespace diferente que se encuentren en disco diferente.


>
> > 2-se puede eliminar el indice creado implicitamente
> por postgresql al crear la PK?
>
> No. Pero sí puedes eliminar el otro.
>


Gracias


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

Re: [ADMIN] psql data restore not working

Are all of your database roles/permissons/search_path set on your new server?

--- On Fri, 8/29/08, Vishal Arora <aroravishal22@hotmail.com> wrote:
From: Vishal Arora <aroravishal22@hotmail.com>
Subject: Re: [ADMIN] psql data restore not working
To: "Daniel Punton" <compass2k@gmail.com>, pgsql-admin@postgresql.org
Date: Friday, August 29, 2008, 5:12 AM






Date: Fri, 29 Aug 2008 10:26:44 +1000
From: compass2k@gmail.com
To: pgsql-admin@postgresql.org
Subject: [ADMIN] psql data restore not working

I would be glad on advice what might be the issue (or how I could narrow it down)
with the non restoration of data from a postgres  7.1 database to a postgres 8 database.

The schema was dumped with
pg_dump -O -s databasename > database_schema_file_name
The data was dumped with
pg_dump -O -a databasename > database_data_file_name

the datafile was gzipped and transferred to another server
and gunzipped

the database was successfully created
the schema was successfully generated
psql -d databasename -f database_schema_file_name
the datafile was cleaned for the carriage return/line feed issue (between pgsql 7-> 8)
sed 's/^M/\\r/' datafilename > datafilename
I attempted to insert the data
psql -d databasename -f database_data_file_name

Have you tried psql -U<userID> databasename < database_data_file_name
 
no errors but when I query the data - there is none ..

Any ideas what I might be missing or how to see any errors or warnings?

Cheers
Daniel Punton
Senior Analyst/Programmer
Transact/EForms Team CSC



Searching for weekend getaways? Try Live.com Try it!

[pgadmin-hackers] SVN Commit by dpage: r7441 - trunk/pgagent

Author: dpage

Date: 2008-08-29 14:11:11 +0100 (Fri, 29 Aug 2008)

New Revision: 7441

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

Log:
Add licence


Added:
trunk/pgagent/LICENSE

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

Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.

PoolSnoopy wrote:
>
> ***PUSH***
>
> this bug is really some annoyance if you use automatic build environments.
> I'm using phpunit to run tests and as soon as postgres is involved the php
> cli environment segfaults at the end. this can be worked around by disabling
> ssl but it would be great if the underlying bug got fixed.

This is PHP's bug, isn't it? Why are you complaining here?

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

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

Re: [GENERAL] temp schemas

On Fri, Aug 29, 2008 at 1:40 PM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:

>> Why have you got thousands of them? If you are running with thousands
>> of active backends, may I suggest a connection pooler?
>>
>
> I don't know. It looks like a bug to me where a temp table is created
> and dropped on commit but the next time the function executes and
> creates a new temp table, it does this in another temp schema. It does
> this over and over until I have thousands of temp schemas that aren't
> used.

Is this vanilla PG, or your Greenplum install?

> It looks this SQL is executing when connecting with pgAdmin which
> doesn't exclude temp schemas. Even though I don't have it configured to
> show temp schemas, the SQL doesn't exclude these records. This is with
> 1.8.2 of pgAdmin.

Oh, that's weird - that code is filtering out system objects client
side for some reason. I'll look at that.


--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

[COMMITTERS] pgsql: Remove all traces that suggest that a non-Bison yacc might be

Log Message:
-----------
Remove all traces that suggest that a non-Bison yacc might be supported, and
change build system to use only Bison. Simplify build rules, make file names
uniform. Don't build the token table header file where it is not needed.

Modified Files:
--------------
pgsql:
configure (r1.602 -> r1.603)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/configure?r1=1.602&r2=1.603)
configure.in (r1.564 -> r1.565)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/configure.in?r1=1.564&r2=1.565)
pgsql/config:
programs.m4 (r1.23 -> r1.24)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/config/programs.m4?r1=1.23&r2=1.24)
pgsql/contrib/cube:
.cvsignore (r1.1 -> r1.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/cube/.cvsignore?r1=1.1&r2=1.2)
Makefile (r1.21 -> r1.22)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/cube/Makefile?r1=1.21&r2=1.22)
pgsql/contrib/seg:
.cvsignore (r1.1 -> r1.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/seg/.cvsignore?r1=1.1&r2=1.2)
Makefile (r1.20 -> r1.21)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/seg/Makefile?r1=1.20&r2=1.21)
pgsql/doc/src/sgml:
installation.sgml (r1.311 -> r1.312)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/installation.sgml?r1=1.311&r2=1.312)
pgsql/src:
Makefile.global.in (r1.241 -> r1.242)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/Makefile.global.in?r1=1.241&r2=1.242)
pgsql/src/backend:
Makefile (r1.129 -> r1.130)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/Makefile?r1=1.129&r2=1.130)
pgsql/src/backend/bootstrap:
.cvsignore (r1.1 -> r1.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/.cvsignore?r1=1.1&r2=1.2)
Makefile (r1.36 -> r1.37)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/Makefile?r1=1.36&r2=1.37)
bootscanner.l (r1.46 -> r1.47)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/bootscanner.l?r1=1.46&r2=1.47)
pgsql/src/backend/parser:
.cvsignore (r1.1 -> r1.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/.cvsignore?r1=1.1&r2=1.2)
Makefile (r1.46 -> r1.47)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/Makefile?r1=1.46&r2=1.47)
keywords.c (r1.199 -> r1.200)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/keywords.c?r1=1.199&r2=1.200)
parser.c (r1.73 -> r1.74)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parser.c?r1=1.73&r2=1.74)
scan.l (r1.144 -> r1.145)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/scan.l?r1=1.144&r2=1.145)
pgsql/src/include:
Makefile (r1.25 -> r1.26)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/Makefile?r1=1.25&r2=1.26)
pgsql/src/interfaces/ecpg/preproc:
Makefile (r1.135 -> r1.136)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/Makefile?r1=1.135&r2=1.136)
pgsql/src/pl/plpgsql/src:
.cvsignore (r1.1 -> r1.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/.cvsignore?r1=1.1&r2=1.2)
Makefile (r1.32 -> r1.33)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/Makefile?r1=1.32&r2=1.33)
pl_comp.c (r1.128 -> r1.129)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_comp.c?r1=1.128&r2=1.129)
pl_exec.c (r1.217 -> r1.218)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_exec.c?r1=1.217&r2=1.218)
pl_funcs.c (r1.72 -> r1.73)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_funcs.c?r1=1.72&r2=1.73)
pl_handler.c (r1.39 -> r1.40)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_handler.c?r1=1.39&r2=1.40)
pgsql/src/tools/msvc:
clean.bat (r1.13 -> r1.14)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/tools/msvc/clean.bat?r1=1.13&r2=1.14)
pgbison.bat (r1.8 -> r1.9)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/tools/msvc/pgbison.bat?r1=1.8&r2=1.9)

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

[GENERAL] Full Text Search, plus some General Ideas Beforehand

Dear Madam or Sir!

I am just studying PostgreSQL 8.3.1 from a mag CD unfortunately under
MS-Windows.

Aye, it is under the BSD licence which allows for re-proprietarisation of
the code.

I lean more to the GPL, the GPL v3.x being a bit too strict in the case of
my GNU Business Model.

This provides source code and uncertified binaries for the media and
handling costs as is known for OpenSource software.

Money is made, close to the ideas of the FSF, in leasing certified binaries
that are dongled to the particular machine, in this case the GPL v3.x has
to be liberated to a kind of LGPL, which makes the binary tamper proof.

Tightly included into this licence is a generous insurance against any
damages from using this software.

ClosedSource will be unconditionally liable to any damages according to
article 14, paragraph 2 of the German Constitution, we have the concept of
guilt-free damage risk liability.

I have serviced mission-critical embedded computers, so my hard position is
understandable.

This far to the general politics.

Aye, I am an old fossil, learning computers via punched cards over 30 years
ago.

Thus I am influenced not only by XBase, but also from database theory where
indexing everything was paramount.

Your problem of updating BTrees is most visibly in ReiserFS, whose
transactional benefits are counterweighted by the overhead of maintaining
mail and news servers.

There is a fragmentation problem under ext3 for IMAP directories, but this
can be solved by tar-ing and untar-ing the appropriate directories.

So we should leave that to the planner.

However, old DEC Alpha OS's provided an FX32! adaptive IA32 emulator,
actually Sun sports an hot-spot-JIT system in JRE 1.6.x.

A strategy for the PostgreSQL planner, aye this eats space, but this might
result in a much more optimised balance between indexing speedup vs.
overhead.

Well, for Full Text Search, especially copying Google & Co., I advertise to
augment the traditional presence index by a proximity index and a
permutation index. You already have functions to generate such a proximity
index, the permutation index being the least important.

This makes the whole Internet a natural test bed for PostgreSQL, and
somehow a basis for a Google successor.

Kind regards

Norbert Grün (gnor.gpl@googlemail.com)

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

[pgadmin-hackers] SVN Commit by dpage: r7440 - trunk/pgadmin3/xtra

Author: dpage

Date: 2008-08-29 13:54:44 +0100 (Fri, 29 Aug 2008)

New Revision: 7440

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

Log:
Remove the admin/admin81 modules which aren't needed with modern versions of PG. For those that want them, they're on the downloads area of the website, and in pgAdmin 1.8.


Removed:
trunk/pgadmin3/xtra/admin/
trunk/pgadmin3/xtra/admin81/
Modified:
trunk/pgadmin3/xtra/Makefile.am

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

Re: [ADMIN] Installing PostgreSQL 8.3.3. on Vista

Steve,

That did it. Thanks!

Brandon

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Steve Holdoway
Sent: Thursday, August 28, 2008 4:45 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Installing PostgreSQL 8.3.3. on Vista

Looks like the relevant Visual C runtime library ( VC80.CRT 32 bit )
needs installing first...

Steve

On Thu, 28 Aug 2008 09:42:54 -0500
"Brandon Dybala" <bjd1@martincomp.com> wrote:

> I tried installing PostgreSQL 8.3.3 on Vista Business, and initdb
> failed to start during the installer. I'd really like to make this
> install as automatic as possible since I'm installing PostgreSQL as
> part of my application installer process. It would be best if the
> user does not have to deal with any sort of configuration for
> Postgres. I also tried running the main MSI file with the graphical
> interface, and ran into the same problem. Here is the message I
received first:
> Failed to run initdb: 1!
>
> I checked the initdb.log file it generated, and all it said was "The
> application has failed to start because its side-by-side configuration

> is incorrect. Please see the application event log for more detail."
>
> So I checked the event log, and it had the following:
>
> Log Name: Application
> Source: SideBySide
> Date: 8/28/2008 7:23:49 AM
> Event ID: 33
> Task Category: None
> Level: Error
> Keywords: Classic
> User: N/A
> Computer: VISTATEST
> Description:
> Activation context generation failed for "C:\Program
> Files\PostgreSQL\8.3\bin\initdb.exe". Dependent Assembly
> Microsoft.VC80.CRT,processorArchitecture="x86",publicKeyToken="1fc8b3b
> 9a 1e18e3b",type="win32",version="8.0.50727.762" could not be found.
> Please use sxstrace.exe for detailed diagnosis.
> Event Xml:
> <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
> <System>
> <Provider Name="SideBySide" />
> <EventID Qualifiers="49409">33</EventID>
> <Level>2</Level>
> <Task>0</Task>
> <Keywords>0x80000000000000</Keywords>
> <TimeCreated SystemTime="2008-08-28T14:23:49.000Z" />
> <EventRecordID>593</EventRecordID>
> <Channel>Application</Channel>
> <Computer>VISTATEST</Computer>
> <Security />
> </System>
> <EventData>
>
> <Data>Microsoft.VC80.CRT,processorArchitecture="x86",publicKeyToken="1
> fc 8b3b9a1e18e3b",type="win32",version="8.0.50727.762"</Data>
> <Data>C:\Program Files\PostgreSQL\8.3\bin\initdb.exe</Data>
> </EventData>
> </Event>
>
> I couldn't figure out what I was supposed to do with sxstrace.exe,
> though. The usage example showed parsing a *.etl file, but I can't
> find any of these on my system.
>
> Any help would be appreciated.
>
> Thanks,
>
> Brandon
>


--
Steve Holdoway <steve.holdoway@firetrust.com>

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

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

Re: [GENERAL] MySQL LAST_INSERT_ID() to Postgres

Magnus Hagander escribió:
> Alvaro Herrera wrote:
> > Russ Brown escribió:
> >> Masis, Alexander (US SSA) wrote:
> >>> "SELECT CURRVAL(
> >>> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
> >> Any reason why you can't just do this?
> >>
> >> CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
> >> SELECT lastval();
> >> $$ LANGUAGE SQL VOLATILE;
> >
> > If your table has a trigger that inserts into another table with its own
> > sequence, you're screwed.
>
> I assume you're equally screwed with MySQL LAST_INSERT_ID() in that case
> - so it'd be bug compatible.

Yeah, which is another reason not to use triggers; more pileups for the
whole "new features are there just for checklist's sake" argument.

The approach proposed by Alexander above does not have such problem,
which is why it is better than the alternative suggested by Russ.

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

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

[pgadmin-hackers] SVN Commit by dpage: r7439 - in trunk/pgagent: . pgaevent

Author: dpage

Date: 2008-08-29 13:49:53 +0100 (Fri, 29 Aug 2008)

New Revision: 7439

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

Log:
Build pgaevent on Windows.


Modified:
trunk/pgagent/CMakeLists.txt
trunk/pgagent/pgaevent/CMakeLists.txt

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

[pgadmin-support] Detected error in pgAdmin III v1.8.4

Hello,
 
I installed pgAdmin III v1.8.4 released and
detected a problem with manage privileges.
The problem is in the properties of a table on privileges tab.
The roles not appear in the "role's combobox". Just role public appear. This way I can't add privileges for roles.
 
 
 
Eduardo Sá dos Reis
eduardoreis@pjf.mg.gov.br

Re: [GENERAL] temp schemas

> -----Original Message-----
>
> "Roberts, Jon" <Jon.Roberts@asurion.com> writes:
> > I am noticing a large number of temp schemas in my database. We use
> > temp tables but it doesn't appear that the schemas get dropped for
some
> > reason.
>
> That's intentional. There doesn't seem a lot of value in dropping a
> catalog entry that'll just have to be created again later.
>

This is what I'm trying to understand. At what point does PostgreSQL
determine it needs to create a new temp schema versus reusing an
existing one? Maybe we are doing something incorrectly in our code.

> > This greatly slows down how long it takes pgAdmin to connect
> > because it retrieves thousands of pg_temp_% schemas.
>
> Why have you got thousands of them? If you are running with thousands
> of active backends, may I suggest a connection pooler?
>

I don't know. It looks like a bug to me where a temp table is created
and dropped on commit but the next time the function executes and
creates a new temp table, it does this in another temp schema. It does
this over and over until I have thousands of temp schemas that aren't
used.

On Wednesday, we had 170,243 temp schemas and today, we have 173,384.

> (It might be a good idea to fix pgAdmin so it ignores other sessions'
> temp schemas, though.)
>

It looks this SQL is executing when connecting with pgAdmin which
doesn't exclude temp schemas. Even though I don't have it configured to
show temp schemas, the SQL doesn't exclude these records. This is with
1.8.2 of pgAdmin.

SELECT CASE WHEN nspname LIKE E'pg\\_temp\\_%%' THEN 1
WHEN (nspname LIKE E'pg\\_%') THEN 0
ELSE 3 END AS nsptyp,
nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS
namespaceowner, nspacl, description, has_schema_privilege(nsp.oid,
'CREATE') as cancreate
FROM pg_namespace nsp
LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
WHERE NOT ((nspname = 'pg_catalog' and (SELECT count(*) FROM pg_class
WHERE relname = 'pg_class' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'pgagent' and (SELECT count(*) FROM pg_class WHERE relname =
'pga_job' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'information_schema' and (SELECT count(*) FROM pg_class WHERE
relname = 'tables' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'dbo' and (SELECT count(*) FROM pg_class WHERE relname =
'systables' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'sys' and (SELECT count(*) FROM pg_class WHERE relname =
'all_tables' AND relnamespace = nsp.oid) > 0))
ORDER BY 1, nspname


Jon

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

[GENERAL] warning: libssl.so.4, needed by

Building my application and got the following error.
 
/usr/bin/ld: warning: libssl.so.4, needed by ../../../external/postgres/lib/libpq.so, not found (try using -rpath or -r
path-link)
/usr/bin/ld: warning: libcrypto.so.4, needed by ../../../external/postgres/lib/libpq.so, not found (try using -rpath or
 -rpath-link)
/usr/bin/ld: warning: libldap_r-2.2.so.7, needed by ../../../external/postgres/lib/libpq.so, not found (try using -rpat
h or -rpath-link)
../../../external/postgres/lib/libpq.so: undefined reference to `TLSv1_method'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_set_ex_data'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_connect'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_STORE_load_locations'
../../../external/postgres/lib/libpq.so: undefined reference to `BIO_free'
../../../external/postgres/lib/libpq.so: undefined reference to `BIO_ctrl'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_get_values_len'
../../../external/postgres/lib/libpq.so: undefined reference to `CRYPTO_set_locking_callback'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_NAME_oneline'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_simple_bind'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_set_verify'
../../../external/postgres/lib/libpq.so: undefined reference to `PEM_read_bio_PrivateKey'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_load_error_strings'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_STORE_set_flags'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_get_peer_certificate'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_get_subject_name'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_free'
../../../external/postgres/lib/libpq.so: undefined reference to `PEM_read_bio_X509'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_unbind'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_result'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_count_entries'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_NAME_get_text_by_NID'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_new'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_read'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_value_free_len'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_set_fd'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_pending'
../../../external/postgres/lib/libpq.so: undefined reference to `ENGINE_load_private_key'
../../../external/postgres/lib/libpq.so: undefined reference to `CRYPTO_num_locks'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_first_entry'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_load_verify_locations'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_init'
../../../external/postgres/lib/libpq.so: undefined reference to `ERR_get_error'
../../../external/postgres/lib/libpq.so: undefined reference to `ERR_reason_error_string'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_msgfree'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_library_init'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_err2string'
../../../external/postgres/lib/libpq.so: undefined reference to `CRYPTO_set_id_callback'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_get_ex_data'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_new'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_write'
../../../external/postgres/lib/libpq.so: undefined reference to `OPENSSL_config'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_get_cert_store'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_check_private_key'
../../../external/postgres/lib/libpq.so: undefined reference to `X509_free'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_shutdown'
../../../external/postgres/lib/libpq.so: undefined reference to `BIO_new_file'
../../../external/postgres/lib/libpq.so: undefined reference to `ldap_search_st'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_get_error'
../../../external/postgres/lib/libpq.so: undefined reference to `ENGINE_by_id'
../../../external/postgres/lib/libpq.so: undefined reference to `SSL_CTX_free'
collect2: ld returned 1 exit status


Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail

[pgadmin-hackers] SVN Commit by dpage: r7438 - in trunk/pgagent: . pgaevent

Author: dpage

Date: 2008-08-29 13:39:56 +0100 (Fri, 29 Aug 2008)

New Revision: 7438

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

Log:
Build pgaevent


Added:
trunk/pgagent/pgaevent/CMakeLists.txt
Modified:
trunk/pgagent/CMakeLists.txt
trunk/pgagent/pgaevent/pgamsgevent.rc

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