Re: [pgsql-es-ayuda] Return Query

On Sat, Jun 28, 2008 at 12:26 PM, Edwin Quijada
<listas_quijada@hotmail.com> wrote:
>>>
>>> Consigo el sigte error:
>>> ERROR: a column definition list is required for functions returning "record"
>>>
>>
>> create or replace function f1(out id_record integer, out alias text)
>> returns setof record as
>> $$
>> begin
>> return query select id_record,alias from uno_email_alias;
>> end;
>> $$ language plpgsql;
>>
> Jaime, como la invoco?

select * from f1();

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

[pgsql-es-ayuda] Retorno de tipo boolean

Hola a todos,
quisiera saber si existe alguna forma de retornar los booleans con su
valor entero (0,1) en vez de 't' o 'f'.

Gracias
--
Eduardo Villagrán Morales
Alumno memorista
Usach

[BUGS] BUG #4272: No password can CREATE & DROP database!!

The following bug has been logged online:

Bug reference: 4272
Logged by: y.y.chen
Email address: yychen@clhs.tyc.edu.tw
PostgreSQL version: 8.2 & 8.3
Operating system: Server Fedora3 ,client WIN-XP
Description: No password can CREATE & DROP database!!
Details:

I can execute command "dropdb database and createdb database" without any
password in WIN-XP text mode after PgAdminIII 1.8.x had saved connection
USER and PASSWORD!;

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

[BUGS] BUG #4271: dropped columns conflict with returning rules

The following bug has been logged online:

Bug reference: 4271
Logged by: Alexey Bashtanov
Email address: bashtanov@imap.cc
PostgreSQL version: 8.3.1
Operating system: linux
Description: dropped columns conflict with returning rules
Details:

I have created a partitioned table cache with partitions cache_id_g_4184088
and cache_id_le_4184088 those inherit cache.
I provided insert by the following rule:
CREATE RULE cache_partic AS ON INSERT TO cache DO INSTEAD INSERT INTO
cache_id_g_4184088 VALUES (new.*) RETURNING cache_id_g_4184088.*;

after I ran
ALTER TABLE cache add column foo
ALTER TABLE cache add column bar
ALTER TABLE cache drop column bar
ALTER TABLE cache add column quackquack

this rule started to work incorrectly: it did not store foo and quackquack
values but used nulls instead.

When I tried to ReCREATE this rule, POSTGRESQL said 'ERROR: cannot convert
relation containing dropped columns to view'

--
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] dblink to non postgresql dbms

Klint Gore wrote:
> Maybe these things from pgfoundary might help
>
> http://pgfoundry.org/projects/dbi-link/
>
> http://pgfoundry.org/projects/odbclink/
>
> http://pgfoundry.org/projects/dblink-tds/

Thank you for the links.

I'm just getting into looking at them now.

I had forgotten to say before that my instance of postgresql is running on
windows.

I can't seem to find any evidence of freetds running on windows, I guess
there is not a great deal of need since MS's native client is there. So I'm
thinking of re-writing the dblink-tds to use Mircosoft's native client
rather than tds. The odbclink does not have any files yet, but looks like
exactly what I'd need.

David.

-----Original Message-----
From: Klint Gore [mailto:kgore4@une.edu.au]
Sent: 27 June 2008 00:45
To: David Rowley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dblink to non postgresql dbms

David Rowley wrote:
>
> Hello all,
>
> I'm looking for a solution to query a SQL Server 2000 instance from
> PostgreSQL 8.3.3.
>
> I've been trawling the internet for some type of solution with out any
> luck, I only found old references to someone talking about
> implementation of create database link to postgresql
>
> Does anyone know of anything?
>
> I'm trying to avoid having to write a C table returning function.
>
> Any help would be much appreciated
>
Maybe these things from pgfoundary might help

http://pgfoundry.org/projects/dbi-link/

http://pgfoundry.org/projects/odbclink/

http://pgfoundry.org/projects/dblink-tds/

--
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: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?

"Ulrich" <ulrich.mierendorff@gmx.net> writes:

> EXPLAIN ANALYZE SELECT speed FROM processors WHERE id IN (SELECT processorid
> FROM users_processors WHERE userid=4040) ORDER BY speed ASC LIMIT 10 OFFSET 1;
>
> Limit (cost=113.73..113.75 rows=7 width=5) (actual time=0.335..0.340 rows=10 loops=1)
> -> Sort (cost=113.73..113.75 rows=8 width=5) (actual time=0.332..0.333 rows=11 loops=1)

^^

> Sort Key: processors.speed
> Sort Method: quicksort Memory: 17kB
> -> Nested Loop (cost=47.22..113.61 rows=8 width=5) (actual time=0.171..0.271 rows=13 loops=1)
> -> HashAggregate (cost=47.22..47.30 rows=8 width=4) (actual time=0.148..0.154 rows=13 loops=1)
> -> Bitmap Heap Scan on users_processors (cost=4.36..47.19 rows=12 width=4) (actual time=0.074..0.117 rows=13 loops=1)

^^

> Index Cond: (userid = 4040)
> -> Index Scan using processors_pkey on processors (cost=0.00..8.28 rows=1 width=9) (actual time=0.006..0.007 rows=1 loops=13)
> Index Cond: (processors.id = users_processors.processorid)

It looks to me like you have some processors which appear in
"users_processors" but not in "processors". I don't know your data model but
that sounds like broken referential integrity to me.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's 24x7 Postgres 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: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?

Hi,
I have added a bit of dummy Data, 100000 processors, 10000 users, each
user got around 12 processors.

I have tested both queries. First of all, I was surprised that it is
that fast :) Here are the results:

EXPLAIN ANALYZE SELECT speed FROM processors WHERE id IN (SELECT
processorid FROM users_processors WHERE userid=4040) ORDER BY speed ASC
LIMIT 10 OFFSET 1;

Limit (cost=113.73..113.75 rows=7 width=5) (actual time=0.335..0.340
rows=10 loops=1)
-> Sort (cost=113.73..113.75 rows=8 width=5) (actual
time=0.332..0.333 rows=11 loops=1)
Sort Key: processors.speed
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=47.22..113.61 rows=8 width=5) (actual
time=0.171..0.271 rows=13 loops=1)
-> HashAggregate (cost=47.22..47.30 rows=8 width=4)
(actual time=0.148..0.154 rows=13 loops=1)
-> Bitmap Heap Scan on users_processors
(cost=4.36..47.19 rows=12 width=4) (actual time=0.074..0.117 rows=13
loops=1)
Recheck Cond: (userid = 4040)
-> Bitmap Index Scan on
users_processors_userid_index (cost=0.00..4.35 rows=12 width=0) (actual
time=0.056..0.056 rows=13 loops=1)
Index Cond: (userid = 4040)
-> Index Scan using processors_pkey on processors
(cost=0.00..8.28 rows=1 width=9) (actual time=0.006..0.007 rows=1 loops=13)
Index Cond: (processors.id =
users_processors.processorid)
Total runtime: 0.471 ms
(13 rows)

___________

EXPLAIN ANALYZE SELECT speed FROM processors WHERE EXISTS (SELECT 1 FROM
users_processors WHERE userid=4040 AND processorid=processors.id) ORDER
BY speed ASC LIMIT 10 OFFSET 1;

Limit (cost=831413.86..831413.89 rows=10 width=5) (actual
time=762.475..762.482 rows=10 loops=1)
-> Sort (cost=831413.86..831538.86 rows=50000 width=5) (actual
time=762.471..762.473 rows=11 loops=1)
Sort Key: processors.speed
Sort Method: quicksort Memory: 17kB
-> Seq Scan on processors (cost=0.00..830299.00 rows=50000
width=5) (actual time=313.591..762.411 rows=13 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using users_processors_pkey on
users_processors (cost=0.00..8.29 rows=1 width=0) (actual
time=0.006..0.006 rows=0 loops=100000)
Index Cond: ((userid = 4040) AND (processorid = $0))
Total runtime: 762.579 ms
(10 rows)

As you can see the second query is much slower. First I thought "Just a
difference of 0.3ms?", but then I realized that it was 762ms not 0.762 ;-).
Both queries return the same result, so I will use #1 and count(*) takes
just 0.478ms if I use query #1.

Kind Regards,
Ulrich

Tom Lane wrote:
> Ulrich <ulrich.mierendorff@gmx.net> writes:
>
>> People say that [EXISTS is faster]
>>
>
> People who say that are not reliable authorities, at least as far as
> Postgres is concerned. But it is always a bad idea to extrapolate
> results on toy tables to large tables --- quite aside from measurement
> noise and caching issues, the planner might pick a different plan when
> faced with large tables. Load up a realistic amount of data and then
> see what you get.
>
> regards, tom lane
>
>

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

[COMMITTERS] pgbuildfarm - client-code: remove XP_EXIT_FIX hack that never worked

Log Message:
-----------
remove XP_EXIT_FIX hack that never worked properly anyway, and is now redundant.

Modified Files:
--------------
client-code:
run_build.pl (r1.93 -> r1.94)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbuildfarm/client-code/run_build.pl.diff?r1=1.93&r2=1.94)

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

[HACKERS] Buildfarm client code 3.1 released

I have just released version 3.1 of the buildfarm client code, which
includes a fix for a perl quoting bug made 15 months ago that probably
accounts for some odd failures we have been seeing.

See http://pgfoundry.org/frs/?group_id=1000040 to download.

All users are strongly recommended to upgrade to the latest version.

cheers

andrew

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

[COMMITTERS] pgbuildfarm - client-code: fix quoting bug introduced in 1.75

Log Message:
-----------
fix quoting bug introduced in 1.75

Modified Files:
--------------
client-code:
run_build.pl (r1.92 -> r1.93)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbuildfarm/client-code/run_build.pl.diff?r1=1.92&r2=1.93)

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

Re: [pgsql-www] Removal of the patches email list

Am Friday, 27. June 2008 schrieb Bruce Momjian:
> It was agreed during the developer's meeting at PGCon, and based on
> email discussions we have had on this list.

There was no agreement on any email list changes at the meeting. The minutes
say that I am supposed to follow up on this, which I incidentally did a few
weeks ago, but apparently that post has been blocked. I'll try again later,
perhaps with a different subject or something.

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

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

On Tue, Jun 24, 2008 at 10:27:28PM +0200, Magnus Hagander wrote:
> >>>> Now, if you can give us a step-by-step on how to set it up, that
> >>>> would certainly help ;-)
> >>> Gitosis does not, as far as I can tell, have that delegation
> >>> capability, but I've come up with a way to do this:
> >>>
> >>> 1. Use git-shell. Yes, this does involve creating one shell account
> >>> for each project, but git-shell is, by design, very short on
> >>> exploitable capability.
> >>>
> >>> 2. Make the .ssh directory a git repository.
> >>>
> >>> 3. Edit .ssh/authorized_keys and push via git.
> >> I was looking into being able to do it using gitosis, with an
> >> interface on top of it's existing GIT repository for being able
> >> to delegate this.
> >
> > I discussed this with gitosis's author, and he wants to keep
> > gitosis from becoming "a sourceforge reimplementation." He did,
> > however, commit to stamping it 1.0 and putting up a TODO list.
> > I'd like to package it up for FreeBSD and Fedora, those being two
> > common platforms.
>
> That would be good.

It *would* be good, if the author seemed even vaguely interested in
packaging up so much as a tarball, but he is not. His attitude
is (paraphrasing from conversations with him the past few days), "it's
good enough as a git repository, and everybody who's using it is a git
administrator, so they should know how to wrangle git repositories."
While he may someday outgrow this, we really should not put him and
his attitude in critical paths for our project.

Let's go with git-shell, which is supported and packaged software on
just about every platform, and stop waiting for Godot^Wgitosis.

> >> What do you think of this idea?
> >
> > It's complicated :(
> >
> > Wouldn't it be easier to have a gitosis admin team with the needed
> > access?
>
> Yes, that'd probably be easier, and it's what I'd start the
> implementation out at.

Here's an even simpler implementation: git-ssh and public keys. Yes,
it involves work by administrators, which I'd be delighted to do.

Cheers,
David (cutting a few Gordian knots here)
--
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: [ADMIN] Major upgrade advice

On Wed, Jun 18, 2008 at 2:00 PM, Roberto Garcia
<roberto.garcia@cptec.inpe.br> wrote:
> Just to mention one issue we had here:
>
> In 8.1 we did this to retrieve all data from a specific date:
> SELECT * FROM xxx
> WHERE <timestamp_column> LIKE '2008-05-20%'
>
> In 8.3 we had to change to:
> SELECT * FROM xxx
> WHERE <timestamp_column> >= CAST('2008-05-20' as timestamp) AND
> <timestamp_column> < CAST('2008-05-21' as timestamp)

Also, don't forget that bareword numbers don't automatically cast to
text anymore.

For instance, assuming col is a text type (char, varchar, text), the query

select * from table where col = 1000

Will throw an error in 8.3 whereas it will work under pre-8.3
releases. You can one either of the following to make it work.

select * from table where col = 1000::text
select * from table where col = '1000'

I've been bitten by that bug a few times.

Peter

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

Re: [PERFORM] Planner should use index on a LIKE 'foo%' query

On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
> SELECT distinct url from item where url like 'http://www.micro%' limit
> 10;

Here, the planner knows the pattern beforehand, and can see that it's a
simple prefix.
> select *
> from result
> where exists
> (select * from item where item.url LIKE result.url || '%' limit 1)
> limit 10;

Here it cannot (what if result.url was '%foo%'?).

Try using something like (item.url >= result.url && item.url <= result.url ||
'z'), substituting an appropriately high character for 'z'.

> The only explaination is that I don't use a constant when comparing the
> values. But actually it is a constant...

It's not a constant at planning time.

Also note that you'd usually want to use IN instead of a WHERE EXISTS.

/* Steinar */
--
Homepage: http://www.sesse.net/

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

[pgsql-es-ayuda] [OT] Cubanos en la lista

Hay algun cubano por aca?
Les tengo un regalo, contactenme antes del lunes. Pero que vivan en Cuba.

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

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

_________________________________________________________________
Blog your life in 3D with Windows Live Writer.
http://www.windowslive.com/overview.html?ocid=TXT_TAGLM_Wave2_wl_writer_022008--
TIP 8: explain analyze es tu amigo

Re: [HACKERS] stat() vs cygwin

Andrew Dunstan schrieb:
> Magnus Hagander wrote:
>> Heh. Maybe not dead, but certainly not really alive either ;-) Given the
>> evidence in your patch that clearly 8.3 isn't quite up to speed on
>> cygwin, and nobody has really noticed until now.
>>
>
> AIUI, only the gssapi stuff is broken. Most users are not likely to want
> it on Cygwin I should think. (And plenty of distros are still on 8.2 and
> earlier, anyway).

Well, native windows users is a very nice to have. Actually a killer
feature.

> What would be nice would be for Reini to set up a Cygwin buildfarm
> member that uses all the switches that the Cygwin distro wants to use.

Without ENABLE_SSPI I just need
--enable-nls --with-CXX --with-openssl --with-perl --with-python
--with-libxml --with-libxslt --with-ldap
and these build out of the box.

>> I also notice this in auth.c:
>> +#ifdef·__CYGWIN__
>> +#define·WIN32
>> +

Re: [pgadmin-hackers] Support for integrated tsearch configuration

> On Sat, Jun 28, 2008 at 1:54 PM, Guillaume Lelarge
> > Nothing is really simple on pgAdmin's coding (says the guy who tries
> hardly
> > to compile it under Windows since last evening... grmbl... :) ).
>
> That shouldn't be hard - what was the problem?

You really think so? Getting pgAdmin to compile in Windows is very
difficult. I thought it was done on purpose to make people dislike
Windows.

Jon

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