[GENERAL] Versioning/updating schema

[Jan Cruz]

Is there a utility that could update/merge functions/views from a postgresql dump to an existing db?

Re: [GENERAL] [HACKERS] Clarification needed

[David Fetter]

On Wed, Oct 11, 2006 at 12:26:19PM +0530, Indira Muthuswamy wrote:
> Hai,
> 
> Can anyone of you help me in finding the datatype of a particular
> column in a table in Postgres?

Indira,

You'll want to look at the columns view in the information_schema.

http://www.postgresql.org/docs/current/static/information-schema.html

Cheers,
Dave.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

[Ravindran G - TLS, Chennai.]

Thanks for you detail mail on this. We will look into 8.1.4 and start using
it. 

Meanwhile, we would like to know about this error. 

When I start PostgreSQL service, the below error message is displayed and
finally service didn't started. 

The PostgreSQL Database Server 8.0 service of a local computer cannot begin.


Error 1069: Service was not able to begin because it had failed in logon. 

Any idea about this error ?. Hope when we start service it checks for
Windows authentication rather than DB authentication. Not Sure!. 

Regards, Ravi


-Original Message-
From: Jim Nasby [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 11, 2006 7:29 AM
To: Ravindran G - TLS, Chennai.
Cc: pgsql-general@postgresql.org; Hari Krishna D - TLS , Chennai; Sasikala V
- TLS , Chennai
Subject: Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues


PostgreSQL doesn't really have any unstable versions (unless you're  
talking about code right out of CVS). The closest you might come will  
be the initial release of a major version, or of course a beta/ 
release candidate. (Note that the first "dot" indicates a major  
version for PostgreSQL. 8.0 is a major version, as is 8.1). But even  
our betas are generally very solid and stable. If you're looking for  
the utmost in stability, you probably want to go with 8.1.4 (or  
8.1.5, which should be out RSN).

What does happen from time-to-time is a complex bug (usually some  
kind of a race condition) that has the potential to corrupt data will  
be discovered. These are generally very hard to reproduce, and  
usually go back a number of major versions. This is why it's  
important to update to newer minor versions (ie: 8.1.3 to 8.1.4) when  
they come out.

Another issue you'll be facing is that windows support is fairly new;  
8.0 was the first release that had it. So the performance of the  
windows version has been getting better, and some minor bugs are  
still being found and fixed.

I know that may sound a bit scary, but the truth is it's no different  
for commercial databases; they just hide it from you.

Of course, unless you've discovered some magic process for producing  
bug-free code, you'll undoubtedly have to send out bug fixes for your  
product as well. ;) It's very painless to do a minor version upgrade  
of PostgreSQL, so it makes sense to include that with the updates you  
send out for your product.

On Oct 10, 2006, at 9:27 AM, Ravindran G - TLS, Chennai. wrote:
> Thanks for your comments and moving it to general group.
>
> We would like to know which is the most stable version in  
> Postgresql ?.
> Because Postgresql may undergo changes and will have the version
> incremented. In this case, do we need to do the upgrade  
> frequently ?.  Of
> course, its good have the latest version but this cannot be done  
> each and
> every time after deploying our application to end customers.
>
> Please advise.
>
> Regards, Ravi
>
>
> -Original Message-
> From: Jim C. Nasby [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 10, 2006 7:46 PM
> To: Ravindran G - TLS, Chennai.
> Cc: pgsql-general@postgresql.org; Hari Krishna D - TLS , Chennai;  
> Sasikala V
> - TLS , Chennai
> Subject: Re: [PERFORM] Postgre 8.0 Installation - Issues
>
>
> Moving to -general.
>
> On Tue, Oct 10, 2006 at 04:17:06PM +0530, Ravindran G - TLS,  
> Chennai. wrote:
>> All,
>>
>> We are facing few issues while we install Postgres 8.0 in Windows  
>> 2000
>> Japanese OS. Installer kit name : postgresql-8.0-ja
>
> Is there a reason you're not using 8.1.4? 8.0 was the first windows
> release, and as such there's a number of issues that were improved in
> 8.1. You should at least be using the latest 8.0 version (8.0.8).
>
>> Scenario 1: While installing PostGRE 8.0, we got an logon failure  
>> at the
> end
>
> BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist...
>
>> of installing the component telling that it failed to produce the  
>> process
>> for initdb and also that the user name was not able to be  
>> recognized or
> the
>> password is wrong. After the OK button was clicked the whole process
> rolled
>> back automatically and the PostGRE got uninstalled.
>
> Make sure that you have the right password for the account that
> PostgreSQL will be running under. I often find it's easiest to just
> delete that account and let the installer create it for me.
>
>> Scenario 2: In one of the computers we managed to install the  
>> PostGRE 8.0
>> but the database initialization could not be performed. While  
>> creating the
>> database using the Credb patch we got an error telling that the  
>> tables
> were
>> missing and the connection with the local host failed.
>>  
>> Scenario 3: For one of the machines the database has also been  
>> created but
>> once the system is restarted the PostGRE does not work and we get  
>> the same
>> error as in the Scenario2.
>
> These could be issues surrounding administrator rights. PostgreSQL  
> will
> refuse to start if the ac

Re: [GENERAL] plpgsql handling a set of values

[[EMAIL PROTECTED]]

I think you should have a look at the ltree contrib package. It 
implements tree-like structures, and probably makes your problem much 
simpler.


I have never used contrib modules with PostgreSQL - are they easy to 
handle - I mean, especially upon upgrade, etc.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Schema - update search_path

[Alvin]

Hey guys, I would like to know a good way of updating or appending the schema names in the search path, Right now I do a show search_path, get the names of all the schemas, and then set search path; adding the new schema. I'm in the process of integrating a few systems and will need to continuously add new schemas. The manual method above is pretty tiring and prone to errors and all that. Looking through the mailing list I see that this question has been asked before but I don't see an answer.Thanks.

Re: [GENERAL] more anti-postgresql FUD

[Andrew Kelly]

On Tue, 2006-10-10 at 14:50 -0400, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> >> * MySQL is used as a primary development platform. 
> 
> > Another good reason.
> 
> Actually that's *the* reason --- it's always going to be hard for
> Postgres to look good for an application that's been designed/optimized
> for MySQL.  The application has already made whatever compromises it
> had to for that platform, and dropping it onto a different DB won't
> magically undo them.
> 
> Some days I think database independence is a myth.

If it's not even possible to get trustworthy, duplicate renderings of
XHTML/CSS on popular browsers without tweaks, we can truly never expect
something as utopian as that.
Sadly.

Andy


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] STABLE functions

[Rafal Pietrak]

Hi All,

May be someone can help me with the following problem:

1. I need to extend 'featurs' of database user account.

2. I did that by creating a table:
CREATE TABLE users (username text, -- key matching 'current_user'
freaturs text -- thing I need
);

3. I allow acces to that table through VIEWs:
CREATE VIEW my_users AS SELECT * FROM users WHERE 

4. one of the 'featurs' I need is a 'controlled' addition/deletion of
database uses - by 'controlled' I mean "with a little help from stored
procedures"

5. one of the procedures is:
CREATE FUNCTION kill(text) RETURNS boolean as $$ SET ROLE
MASTER_OF_THE_UNIVERSE; EXECUTE 'DROP USER ' || quote_ident($1); reset
role; return true; END $$ LANGUAGE plpgsql STABLE;

6. which is used within: 
CREATE RULE goaway AS ON DELETE TO my_users DO INSTEAD DELETE  WHEN
kill(old.username) = true AND ;

Now, the problem is:
an attempt execution-
ERROR:  SET is not allowed in a non-volatile function
CONTEXT:  SQL statement "set role MASTER_OF_THE_UNIVERSE"
PL/pgSQL function kill line 1 at execute statement
---

I've defined the function as STABLE, since it's *meant* to be called
just once-per-statement (depends just on it's argument, not on any data
within AND clause that follows not on any other actions happening within
the statement).

In other words, I wouldn't like this function to be called again and
again (as with NONE-STABLE functions), for every row to be deleted. Just
once per statement to retrieve the value it would have for this
statement, which depends solely on "old.username" which is supposed to
remain stable throuout the statement.

So I fell into 'semantical ambiquity case':
1. I used the STABLE keyword to tell executor to evaluate the function
just once per statement.
2. while it looks, that the STABLE keyword is there, to tell the
executor, that 'whatever it does' - the outcome of the function remains
the same within a statement.

Which is not exactly the same meaning. Any one knows which one is
'according to standard'?

But. Is there any implementable solution to my design?

BTW: signifficant part of my stored procedures code is there for the
sole reason, that manipulation of USERS (addition/deletion/etc) is not
accessible for a member of priviledged groups until the role is set
explicitly (as in the case of KILL function above). May be there is a
configuration switch to change this default?

BTW-2: My design would be much easier if only I counld:
CREATE TABLE users( 
   id int references pg_authid(oid) on delete cascade, 
   .
);
Which I cannot, apparently. Are there technical reasons for this
restriction?

-- 
-R

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] postgresql.conf shared buffers

[Alexander Staubo]

On Oct 11, 2006, at 03:34 , Jim C. Nasby wrote:


And increase estimated_cache_size to something close
to how much memory you have.


That would be "effective_cache_size".

Alexander.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Problem compiling PostGIS 1.1.4

[Devrim GUNDUZ]

Hello,

On Tue, 2006-10-10 at 11:30 +0200, Ludwig Kniprath wrote:
> "/usr/lib/postgresql/8.1/lib/liblwgeom.so.1.1": libgeos_c.so.1: cannot
> open  shared object file: No such file or directory 

Did you run make install agains geos? Is the path that libgeos_c.so.1
lives in ldconfig path?

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Versioning/updating schema

[Jorge Godoy]

"Jan Cruz" <[EMAIL PROTECTED]> writes:

> Is there a utility that could update/merge functions/views from a postgresql
> dump to an existing db?

I remember seeing something about a 'diff'...  Something like 'pgdiff'...
With it you could compare and generate a script that went from one situation
to the other.

I haven't put my hands on that to see if it really works, though...

There's something that Pentaho can do (another developer told me he used it
for doing that).  

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Versioning/updating schema

[A. Kretschmer]

am  Wed, dem 11.10.2006, um  7:37:11 -0300 mailte Jorge Godoy folgendes:
> "Jan Cruz" <[EMAIL PROTECTED]> writes:
> 
> > Is there a utility that could update/merge functions/views from a postgresql
> > dump to an existing db?
> 
> I remember seeing something about a 'diff'...  Something like 'pgdiff'...
> With it you could compare and generate a script that went from one situation
> to the other.

Yeah! http://pgdiff.sourceforge.net/


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] How does PG access wal files?

[Brad Nicholson]

Is it by file name or by inode?

Brad.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] How does PG access wal files?

[Simon Riggs]

On Wed, 2006-10-11 at 09:47 -0400, Brad Nicholson wrote:
> Is it by file name or by inode?

Filename

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] rewriting query to move filter conditionout of a subselect

[Rhys Stewart]

Hi all I have the following query that sources two tables:

select pi2.* from allpoints2 a1
inner join prem_info pi2 on pi2.prem = a1.prem AND the_geom is null
AND pi2.multiplier > 1
where route in
(select route from prem_info pi
inner join allpoints2 a on a.prem = pi.prem
where feederid = '241/6-210'
group by route)

is it possible to rewrite the query so that i can filter for feederid
outside of a subselect, (so that i could call it from a view maybe) or
should i do a set returning function ?

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] query log corrupted-looking entries

[Tom Lane]

"George Pavlov" <[EMAIL PROTECTED]> writes:
> after an attempt at stress-testing my app i started seeing some
> corrupted-looking entries in the postgresql query log. for example:

It looks like you're getting messages interspersed in the log, ie,
single messages aren't written indivisibly.  This doesn't affect
Postgres itself, but of course makes the log harder to read :-(

What PG version is this, on what operating system?  Do you have
redirect_stderr enabled?

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Schema - update search_path

[Tom Lane]

Alvin <[EMAIL PROTECTED]> writes:
> Hey guys, I would like to know a good way of updating or appending th=
> e schema names in the search path,

Perhaps like this:

regression=# create schema news;
CREATE SCHEMA
regression=# show search_path;
  search_path

 "$user",public
(1 row)

regression=# select set_config('search_path', current_setting('search_path') || 
',' || quote_ident('news'), false);
 set_config
-
 "$user",public,news
(1 row)

regression=# show search_path;
 search_path
-
 "$user",public,news
(1 row)

regression=#

You should read these two pages for several useful functions:
http://www.postgresql.org/docs/8.1/static/functions-info.html
http://www.postgresql.org/docs/8.1/static/functions-admin.html

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] left outer join on multi tables

[Richard Broersma Jr]

Taking a second look. I see a few places I made a mistake. :-(

> CREATE VIEW your_view AS
  ^
> SELECT N.first_name, 
>N.last_name,
>A.street,
>A.city, 
>W.hour,
>
> FROM   person P
> LEFT JOIN name N ON (P.namid = N.id)

> LEFT JOIN address A ON (P.addressid = A.id)
^^^
> LEFT JOIN work W ON (P.workid = W.id)

> ;

Hopefully this works a little better.

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] STABLE functions

[Tom Lane]

Rafal Pietrak <[EMAIL PROTECTED]> writes:
> 5. one of the procedures is:
> CREATE FUNCTION kill(text) RETURNS boolean as $$ SET ROLE
> MASTER_OF_THE_UNIVERSE; EXECUTE 'DROP USER ' || quote_ident($1); reset
> role; return true; END $$ LANGUAGE plpgsql STABLE;

You should use the SECURITY DEFINER property instead of explicitly
fooling with role settings.  It's easy to think of cases where that
RESET will select the *wrong* setting leading to a security hole.

Also, if it has side effects, it IS NOT STABLE.  Period.  So you can't
mark anything doing DROP USER as stable.

> 1. I used the STABLE keyword to tell executor to evaluate the function
> just once per statement.

Wrong.  STABLE is not a directive to the system, it is a promise about
the behavior of your function ... and you're trying to break the
promise.

Your best bet for this is probably to put the DROP in an AFTER DELETE
trigger on the my_users table, instead of trying to use a rule.

> BTW-2: My design would be much easier if only I counld:
>   CREATE TABLE users( 
>  id int references pg_authid(oid) on delete cascade, 
>  .
>   );
> Which I cannot, apparently. Are there technical reasons for this
> restriction?

We don't support triggers on system catalogs.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] more anti-postgresql FUD

[Andrew Sullivan]

On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
> Some days I think database independence is a myth.

On the day when you don't, please tell me what application you found
where it isn't.  I want to buy the developers a drink.  Or maybe a
bar.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do sir?
--attr. John Maynard Keynes

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Is it possible to return custom type as proper ROW?

[Joe Kramer]

Pgsql 8.1.4.

I want return custom type from function as row, not as values in brackets (1,2).

I have following type and function:

CREATE TYPE new_item_return_type AS
  (item_id bigint,
   last_update timestamp without time zone);

CREATE OR REPLACE FUNCTION new_item( new_title int8, new_user_id int8)
RETURNS new_item_return_type AS
$BODY$
DECLARE
ret new_item_return_type%ROWTYPE;
BEGIN
   INSERT INTO item (user_id,title) VALUES (new_user_id,new_title) ;
   ret.item_id:= currval('item_id_seq');
   SELECT time_last_update INTO ret.last_update  FROM item WHERE id
=ret.item_id;
   RETURN ret;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


Seems like in DECLARE ret new_item_return_type%ROWTYPE;
%ROWTYPE is ignored.


When I run SELECT public.new_item(3,2);
I get :
new_item_return_type
-
"(32,"2006-10-11 10:14:39")"


I want to get:
item_id   |   last_update
-
32 |  1234-12-12 12:12:12


Is it possible ? I am using the wrong approach?

Thanks.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Andreas Kretschmer]

Joe Kramer <[EMAIL PROTECTED]> schrieb:
> 
> I want to get:
> item_id   |   last_update
> -
> 32 |  1234-12-12 12:12:12

Untested:

SELECT item_id, last_update from public.new_item(3,2);


HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] plpgsql handling a set of values

[Merlin Moncure]

On 10/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

> I think you should have a look at the ltree contrib package. It
> implements tree-like structures, and probably makes your problem much
> simpler.

I have never used contrib modules with PostgreSQL - are they easy to
handle - I mean, especially upon upgrade, etc.


installation:
make && make install

upgrade issue is tricky.  their is a lower burden of backwards
compatibility than on in-core features.  for example, some of contrib
was gpl and is getting moved out/changed.  however, the more popular
modules, like tsearch are very well supported.  I haven't used ltree
module myself but on the surface it has some advantages and
disadvantages over the approach I suggested.  However, the authors,
namely Teodor Sigaev and Oleg Bartunov are some pretty amazing coders.

merlin

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] more anti-postgresql FUD

[Guy Rouillier]

Andrew Sullivan wrote:
> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> Some days I think database independence is a myth.
> 
> On the day when you don't, please tell me what application you found
> where it isn't.  I want to buy the developers a drink.  Or maybe a
> bar.  

The Mantis bug tracking software http://www.mantisbt.org/ now works with
PostgreSQL (was developed with MySQL.)  It works equally well with both,
including automated installation.

-- 
Guy Rouillier


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Tom Lane]

Andreas Kretschmer <[EMAIL PROTECTED]> writes:
> Joe Kramer <[EMAIL PROTECTED]> schrieb:
>> I want to get:
>> item_id   |   last_update
>> -
>> 32 |  1234-12-12 12:12:12

> Untested:
> SELECT item_id, last_update from public.new_item(3,2);

Or just
SELECT * FROM public.new_item(3,2);

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] more anti-postgresql FUD

[Joshua D. Drake]

Andrew Sullivan wrote:
> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> Some days I think database independence is a myth.
> 
> On the day when you don't, please tell me what application you found
> where it isn't.  I want to buy the developers a drink.  Or maybe a
> bar.

Command Prompt will help sponsor that community event ;)

Joshua D. Drake

> 
> A
> 


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] more anti-postgresql FUD

[Steve Crawford]

Guy Rouillier wrote:
> Andrew Sullivan wrote:
>> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>>> Some days I think database independence is a myth.
>> On the day when you don't, please tell me what application you found
>> where it isn't.  I want to buy the developers a drink.  Or maybe a
>> bar.  
> 
> The Mantis bug tracking software http://www.mantisbt.org/ now works with
> PostgreSQL (was developed with MySQL.)  It works equally well with both,
> including automated installation.
> 

I find that "database independence" == "lowest common denominator". I
may have missed something, but a quick scan of the Mantis code didn't
reveal any use of triggers, rules, foreign-keys, user-defined types, etc.

Whenever I see that a project has been "ported" to PostgreSQL I can
usually be sure that it is not a project that was designed to take
advantage of the features and capabilities that PG offers.

But I suspect that porting something that uses all the features of mySql
to PostgreSQL will be far easier than porting something that uses all
the features of PostgreSQL over to mySql (if it is possible at all).

Cheers,
Steve


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[A. Kretschmer]

am  Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
> Andreas Kretschmer <[EMAIL PROTECTED]> writes:
> > Joe Kramer <[EMAIL PROTECTED]> schrieb:
> >> I want to get:
> >> item_id   |   last_update
> >> -
> >> 32 |  1234-12-12 12:12:12
> 
> > Untested:
> > SELECT item_id, last_update from public.new_item(3,2);
> 
> Or just
>   SELECT * FROM public.new_item(3,2);

Yes, but i have learned, that 'SELECT * ...' is evil...

Thanks for the hint.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Find out the number of rows returned by refcursor?

[Karen Hill]

Karen Hill wrote:
> What is the best way to find out the total number of rows returned by
> an refcursor?  This would allow the client user to know the total
> amount of rows as they are using FETCH FORWARD/BACKWARD.
>
> For example let's say that an refcursor has 300 rows.  The user fetches
> 20 at a time.  I would like the user to know that there are 300
> possible rows.

I probably should re-phrase that question.

CREATE OR REPLACE FUNCTION foobar( refcursor ) RETURNS refcurser AS '
BEGIN
OPEN $1 FOR SELECT * FROM t ORDER by z;
END;
' LANGUAGE 'plpgsql';

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count? Perhaps GET DIAGNOSTICS
ROW_COUNT?
SELECT foobar('mycursor');
-- I want to avoid using count(*) for performance reasons. Getting the
total number of rows the cursor --has.  I suspect it there is a system
variable that has this information...I just don't know which one it
--is.
SELECT COUNT(*) FROM t;

COMMIT;


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Uwe C. Schroeder]

On Wednesday 11 October 2006 10:42, A. Kretschmer wrote:
> am  Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
> > Andreas Kretschmer <[EMAIL PROTECTED]> writes:
> > > Joe Kramer <[EMAIL PROTECTED]> schrieb:
> > >> I want to get:
> > >> item_id   |   last_update
> > >> -
> > >> 32 |  1234-12-12 12:12:12
> > >
> > > Untested:
> > > SELECT item_id, last_update from public.new_item(3,2);
> >
> > Or just
> > SELECT * FROM public.new_item(3,2);
>
> Yes, but i have learned, that 'SELECT * ...' is evil...

Well, "SELECT *" is only evil if your application relies on a specific column 
order to function. The moment you change the table layout and you're using 
"select *" your application will cease functioning.
My app uses tons of select *, but then I wrote an object mapper that queries 
the information schema at startup - so it's aware of table changes and 
adjusts accordingly. 

Uwe


--
Open Source Solutions 4U, LLC   1618 Kelly St
Phone:  +1 707 568 3056 Santa Rosa, CA 95401
Cell:   +1 650 302 2405 United States
Fax:+1 707 568 6416

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Find out the number of rows returned by refcursor?

[Tom Lane]

"Karen Hill" <[EMAIL PROTECTED]> writes:
> -- Is there a way to know the total number of rows the cursor is
> capable of traversing without using --count?

If you want an accurate count, the only way is to traverse the cursor.
Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE
BACKWARD ALL to reset the cursor (the latter at least should be
reasonably cheap).

If you can settle for a (potentially very inaccurate) estimate, consider
using EXPLAIN on the query and noting the planner's rowcount estimate.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Find out the number of rows returned by refcursor?

[Karen Hill]

Tom Lane wrote:
> "Karen Hill" <[EMAIL PROTECTED]> writes:
> > -- Is there a way to know the total number of rows the cursor is
> > capable of traversing without using --count?
>
> If you want an accurate count, the only way is to traverse the cursor.
> Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE
> BACKWARD ALL to reset the cursor (the latter at least should be
> reasonably cheap).
>

Cool.  Quick question, how does one go about noting the rowcount?
Using the rowcount in get diagnostics or something else?

regards,
karen.


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] invalid data in PID file

[J S B]

Hi,I'm trying to start my postgres server using#> pg_ctl start -D /usr/local/pgsql/abcand get an error like:invalid data in PID file "/usr/local/pgsql/abc/postmaster.pid"
can anyone comment whats wrong.My postmaster.pid file is empty at the time when i run this command.Regards,Jas

Re: [GENERAL] [Slony1-general] Using slony with many schema's

[snacktime]

First, thanks for all the feedback.   After spending some more time
evaluating what we would gain by using slony I'm not sure it's worth
it.  However I thought I would get some more feedback before
finalizing that decision.

The primary reason for looking at replication was to move cpu
intensive SELECT queries to a slave.  However, by moving away from
schema's the report queries for all clients on the server become more
cpu intensive instead of just the clients with large data sets.  The
average distribution is that 95% of our clients have less then 5000
rows in any table, and the other 5% can have hundreds of thousands.
So by putting all the data into one schema, every report query now
gets run against a million or more rows instead of just a few  hundred
or thousand.  So all clients will see a drop in query performance
instead of just the clients with large amounts of data.

Chris

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] STABLE functions

[Rafal Pietrak]

On Wed, 2006-10-11 at 10:30 -0400, Tom Lane wrote:
> > 1. I used the STABLE keyword to tell executor to evaluate the function
> > just once per statement.
> 
> Wrong.  STABLE is not a directive to the system, it is a promise about
> the behavior of your function ... and you're trying to break the
> promise.

Ok. so it's semantics version.2.

> Your best bet for this is probably to put the DROP in an AFTER DELETE
> trigger on the my_users table, instead of trying to use a rule.

:) yes! Thenx Tom.

The solution was right in front of my eyes and I couldn't have seen it!!

> > BTW-2: My design would be much easier if only I counld:
> > CREATE TABLE users( 
> >id int references pg_authid(oid) on delete cascade, 
> >.
> > );
> > Which I cannot, apparently. Are there technical reasons for this
> > restriction?
> 
> We don't support triggers on system catalogs.

Apparently there is also no way to "foreign key reference" those, even
without installing any on-delete actions/triggers.

Pity.

Are there any plans for anything like that (referencing keys in system
tables from public schemas)? 

Or may be inharitance of system tables in public schemas? (So one could
have extentions?)

-- 
-R

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] invalid data in PID file

[Martijn van Oosterhout]

On Wed, Oct 11, 2006 at 02:50:59PM -0400, J S B wrote:
> I'm trying to start my postgres server using
> 
> #> pg_ctl start -D /usr/local/pgsql/abc
> 
> and get an error like:
> 
> invalid data in PID file "/usr/local/pgsql/abc/postmaster.pid"
> 
> can anyone comment whats wrong.
> 
> My postmaster.pid file is empty at the time when i run this command.

I think the fact that it's empty may be what's wrong. It should have
something in it.

How did you manage to get into that state?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] [Slony1-general] Using slony with many schema's

[snacktime]

Sorry wrong list, this was meant for the slony list...

Chris

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] invalid data in PID file

[J S B]

i never touched it. when i opened it to check the stuff  inside, found it empty.What should i do now?On 10/11/06, Martijn van Oosterhout <
kleptog@svana.org> wrote:On Wed, Oct 11, 2006 at 02:50:59PM -0400, J S B wrote:
> I'm trying to start my postgres server using>> #> pg_ctl start -D /usr/local/pgsql/abc>> and get an error like:>> invalid data in PID file "/usr/local/pgsql/abc/postmaster.pid"
>> can anyone comment whats wrong.>> My postmaster.pid file is empty at the time when i run this command.I think the fact that it's empty may be what's wrong. It should havesomething in it.
How did you manage to get into that state?Have a nice day,--Martijn van Oosterhout      http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFFLT5uIB7bNG8LQkwRArQGAJkBQ3lvZwdoQ1MRXIJVGCMBA3NoKwCeLrGj
EUBt6BZ7QOgxzrf89BEYpyI==xQpd-END PGP SIGNATURE-

Re: [GENERAL] invalid data in PID file

[J S B]

I deleted postmaster.pid file and got rid of the previous error.Now its a new error which say something as follows after i try starting my postgres server usring pg_ctl start -D /usr/local/pgsql/jsbali    :-postmaster starting
LOG:  could not bind IPv4 socket: Address already in useHINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission denied
Can you please tell me what is this all about?JasOn 10/11/06, J S B <[EMAIL PROTECTED]> wrote:
i never touched it. when i opened it to check the stuff  inside, found it empty.What should i do now?
On 10/11/06, Martijn van Oosterhout <

kleptog@svana.org> wrote:
On Wed, Oct 11, 2006 at 02:50:59PM -0400, J S B wrote:
> I'm trying to start my postgres server using>> #> pg_ctl start -D /usr/local/pgsql/abc>> and get an error like:>> invalid data in PID file "/usr/local/pgsql/abc/postmaster.pid"
>> can anyone comment whats wrong.>> My postmaster.pid file is empty at the time when i run this command.I think the fact that it's empty may be what's wrong. It should havesomething in it.
How did you manage to get into that state?Have a nice day,--Martijn van Oosterhout      http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFFLT5uIB7bNG8LQkwRArQGAJkBQ3lvZwdoQ1MRXIJVGCMBA3NoKwCeLrGj
EUBt6BZ7QOgxzrf89BEYpyI==xQpd-END PGP SIGNATURE-

Re: [GENERAL] invalid data in PID file

[Tom Lane]

"J S B" <[EMAIL PROTECTED]> writes:
> FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission
> denied

> Can you please tell me what is this all about?

It looks to me like you have, or had, another postmaster running under a
different userid.  Perhaps you should back up to the beginning and tell
us what you've been doing with this installation.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] invalid data in PID file

[Madison Kelly]

Tom Lane wrote:

"J S B" <[EMAIL PROTECTED]> writes:

FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission
denied



Can you please tell me what is this all about?


It looks to me like you have, or had, another postmaster running under a
different userid.  Perhaps you should back up to the beginning and tell
us what you've been doing with this installation.

regards, tom lane


IIRC, I had that happen on a botched upgrade of postgres. Even if I did 
a reinstall it wasn't enough. Under Debian I had to try upgrading again, 
then do a "complete uninstall" (or something similar), then a fresh 
reinstall and it finally worked again. It happened a couple of times and 
I never figured out why. Hasn't happened lately though.


Madi

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] invalid data in PID file

[J S B]

Actually i've been trying to setup my postgres database in a new machine.the machine that i'm using right now is goin to die very soon. So i was trying to restore it in another machine but before that I have to install postgres in this new machine.
I installed postgres successfully, created a DB cluster using initdb comand after creating a user account that would use postgres and made it the owned of the location where DB was initialized.Then I started my postgres service using
pg_ctl start -D /usr/local/pgsql/jsbaliThat was the very first time i started it and it did not give me any errors.After that I created a new DB server using PGAdmin3 and the server was created successfully.
When i tried connecting to this server, i gave me an error Ident Authentication failed and wanted me to do appropriate changes in pg_hba.conf fileHowever, in pg_hba.conf file, i have the settings for the trusted connection.
pg_hba.conf and postgresql.conf files i copied from my old Db server and overwrote on the ones that were created as a result of initidb in the new database.Right after this point, pg_ctl start, stop and reload doesn't work at all.
There was some problem with postmaster.pid file, so i just removed it.The last error that i got after tryin to start postgres is :could not bind IPv6 socket: Address already in use.HINT: Is another postmaster already running on port 5432? If not , wait a few seconds and retry.
LOG: could not bind IPv$ socket: Address already in useHINT:  Is another postmaster already running on port 5432? If not , wait a few seconds and retry.WARNING:  could not create listen socket for "*"
FATAL: could not create any TCP/IP socketsOn 10/11/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"J S B" <[EMAIL PROTECTED]> writes:> FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission> denied> Can you please tell me what is this all about?
It looks to me like you have, or had, another postmaster running under adifferent userid.  Perhaps you should back up to the beginning and tellus what you've been doing with this installation.regards, tom lane

Re: [GENERAL] invalid data in PID file

[Tom Lane]

"J S B" <[EMAIL PROTECTED]> writes:
> There was some problem with postmaster.pid file, so i just removed it.

Without bothering to stop the old postmaster first, I take it.

At this point your best bet is to find out the PID of the old postmaster
with "ps" and then kill it with "kill -INT ".  I suspect you've
also been sloppy about which userid you've been starting the postmaster
under ... you have to be consistent about that.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] invalid data in PID file

[J S B]

Well, I've been very particular with the user id. There's just one single user ID am working on my postgres with.when i start the postgres, it creates the follwoing processes for postgres:1 S jsbali    4034  4033  0  76   0 -  2489 -  15:41 pts/2    00:00:00 postgres: logger process
1 S jsbali    4036  4033  0  75   0 -  5042 -  15:41 pts/2    00:00:00 postgres: writer process1 S jsbali    4037  4033  0  76   0 -  2739 -  15:41 pts/2    00:00:00 postgres: stats buffer process1 S jsbali    4038  4037  0  83   0 -  2547 -  15:41 pts/2    00:00:00 postgres: stats collector process
and gives the follwoing errorLOG:  could not bind IPv4 socket: Address already in useHINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.At this point I would have no option but to kill -INT 4033. I do a ps -elf | grep jsbali again after this kill and i have nothing for postgres running. Good enough.
Then i try to start postgres again and it gives me the same error:LOG:  could not bind IPv4 socket: Address already in useHINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
It ends up being a cyclic problem.Don't know how to get rid of it now.Thanks,JasOn 10/11/06, Tom Lane <
[EMAIL PROTECTED]> wrote:"J S B" <
[EMAIL PROTECTED]> writes:> There was some problem with postmaster.pid file, so i just removed it.Without bothering to stop the old postmaster first, I take it.At this point your best bet is to find out the PID of the old postmaster
with "ps" and then kill it with "kill -INT ".  I suspect you'vealso been sloppy about which userid you've been starting the postmasterunder ... you have to be consistent about that.
regards, tom lane

Re: [GENERAL] more anti-postgresql FUD

[Chris Browne]

[EMAIL PROTECTED] (Andrew Sullivan) writes:
> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> Some days I think database independence is a myth.
>
> On the day when you don't, please tell me what application you found
> where it isn't.  I want to buy the developers a drink.  Or maybe a
> bar.

You're sitting across the street from the local headquarters of the
vendor of just such an application.

Of course, they don't use foreign keys, triggers, dates are
represented as char(10), and validity checking is expected to be coded
into either (traditionally) transaction screens or (new technologies)
BAPIs (Business APIs).  Really, the application was designed with IMS
 in mind.

And *they* can afford to pay for a whole bar, once you pay the annual
licensing fee :-(.

Oh, and a cluster of IBM p570s would probably be enough to run a 20
user system :-(.  [Actually, that's probably not *entirely* fair; I
once administered an R/3 system supporting ~30 users on a uniprocessor
DEC Alpha with 256MB of RAM, which by modern standards is pretty
pedestrian...]
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "linuxdatabases.info")
http://www3.sympatico.ca/cbbrowne/sap.html
"Access to a COFF symbol table via ldtbread is even less abstract,
 really sucks in general, and should be banned from earth."
-- SCSH 0.5.1 unix.c

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Merlin Moncure]

On 10/11/06, Uwe C. Schroeder <[EMAIL PROTECTED]> wrote:

Well, "SELECT *" is only evil if your application relies on a specific column
order to function. The moment you change the table layout and you're using
"select *" your application will cease functioning.
My app uses tons of select *, but then I wrote an object mapper that queries
the information schema at startup - so it's aware of table changes and
adjusts accordingly.


+1

assumed column ordering is the real enemy.  Here is another place
where select * is imo better style than non select *:

select q.*, bar from
(
select a, b,c from foo
) q;

what I really wish sql had was the ability to select all but a
particular column :)

merlin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] invalid data in PID file

[Tom Lane]

"J S B" <[EMAIL PROTECTED]> writes:
> Then i try to start postgres again and it gives me the same error:

> LOG:  could not bind IPv4 socket: Address already in use
> HINT:  Is another postmaster already running on port 5432? If not, wait a
> few seconds and retry.

Um, have you tried waiting a bit as the hint suggests?

If the port is still claimed after a minute or two, then there must be
some process holding onto it.  netstat and/or lsof might help you find
out what.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Newbie question about importing text files...

[Scott Marlowe]

On Tue, 2006-10-10 at 04:16, Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 10/09/06 22:43, Jonathan Greenberg wrote:
> > So I've been looking at the documentation for COPY, and I'm curious about a
> > number of features which do not appear to be included, and whether these
> > functions are found someplace else:
> > 
> > 1) How do I skip an arbitrary # of "header" lines (e.g. > 1 header line) to
> > begin reading in data?

Using something like bash, you can do this:

tail -n $(( `wc -l bookability-pg.sql|grep -oP "[0-9]+"` -2 ))
bookability-pg.sql|wc -l

make it an alias and call it skip and have it take an argument:

Put this in .bashrc and run the .bashrc file ( . ~/.bashrc ):

skipper(){
tail -n $(( `wc -l $1|grep -oP "[0-9]+"` -$2 )) $1
}


> > 2) Is it possible to screen out lines which begin with a comment character
> > (common outputs for csv/txt files from various programs)?

grep -vP "^#" filename

will remove all lines that start with #.  grep is your friend in unix. 
If you don't have unix, get cygwin as recommended elsewhere.

> > 3) Is there a way to read in fixed width files?

If you don't mind playing about with sed, you could use it and bash
scripting to do it.  I have before.  It's ugly looking but easy enough
to do.  But I'd recommend a beginner use a scripting language they like,
one of the ones that starts with p is usually a good choice (perl,
python, php, ruby (wait, that's not a p!) etc...)

> 
> Both Python & Perl have CSV parsing modules, and can of course deal
> with fixed-width data, let you skip comments, commit every N rows,
> skip over committed records in can the load crashes, etc, etc, etc.

php has a fgetcsv() built in as well.  It breaks down csv into an array
and is really easy to work with.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] question on renaming a foreign key

[Jonathan Vanasco]

I made a  HUGE mistake, and used 'UK' as the abbreviation for the  
united kingdom ( the ISO abbv is 'GB' )


I've got a database where 8 tables have an FKEY on a table  
'location_country' , using the text 'uk' as the value -- so i've got  
9 tables that I need to swap data out on


can anyone suggest a non-nightmarish way for me to do this ?



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Jeff Davis]

On Wed, 2006-10-11 at 11:05 -0700, Uwe C. Schroeder wrote:
> On Wednesday 11 October 2006 10:42, A. Kretschmer wrote:
> > am  Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
> > > Andreas Kretschmer <[EMAIL PROTECTED]> writes:
> > > > Joe Kramer <[EMAIL PROTECTED]> schrieb:
> > > >> I want to get:
> > > >> item_id   |   last_update
> > > >> -
> > > >> 32 |  1234-12-12 12:12:12
> > > >
> > > > Untested:
> > > > SELECT item_id, last_update from public.new_item(3,2);
> > >
> > > Or just
> > >   SELECT * FROM public.new_item(3,2);
> >
> > Yes, but i have learned, that 'SELECT * ...' is evil...
> 
> Well, "SELECT *" is only evil if your application relies on a specific column 
> order to function. The moment you change the table layout and you're using 
> "select *" your application will cease functioning.
> My app uses tons of select *, but then I wrote an object mapper that queries 
> the information schema at startup - so it's aware of table changes and 
> adjusts accordingly. 
> 

It's aware of the tables as they exist at startup. That may change
between when the mapper looks at the information schema and when it gets
the results of a query.

If you know what it's doing it's probably fine, but that doesn't seem
like a general solution.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] [Slony1-general] Using slony with many schema's

[Vivek Khera]

On Oct 11, 2006, at 2:55 PM, snacktime wrote:


So by putting all the data into one schema, every report query now
gets run against a million or more rows instead of just a few  hundred
or thousand.  So all clients will see a drop in query performance
instead of just the clients with large amounts of data.


Indexes on the customer_id field of the combined data tables helps a  
lot. That and big hardware with big RAM. :-)


We store data for all our customers in the same tables.  some have  
several hundred thousand of their own customers, and millions of  
transactions from them; others have a few hundred.  The  
responsiveness of postgres is still great.




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Problem compiling PostGIS 1.1.4

[Ludwig Kniprath]

Devrim GUNDUZ schrieb:

Hello,

On Tue, 2006-10-10 at 11:30 +0200, Ludwig Kniprath wrote:
  

"/usr/lib/postgresql/8.1/lib/liblwgeom.so.1.1": libgeos_c.so.1: cannot
open  shared object file: No such file or directory 



Did you run make install agains geos? Is the path that libgeos_c.so.1
lives in ldconfig path?

Regards,
  

Hi Devrim,
I did run make against geos, but ldconfig afterwards had no entries for 
libgeos_c.so.l. So I did it again, but read the geos-Install-document 
more carefully:


"Linux: Make sure that /usr/local/lib is added to /etc/ld.so.conf
  Make sure that you run /sbin/ldconfig afterwards"

Afterwards the PostGIS-compilation and installation run without problems.

Thanks

Ludwig

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Jeff Davis]

On Thu, 2006-10-12 at 01:36 +0530, Merlin Moncure wrote:
> On 10/11/06, Uwe C. Schroeder <[EMAIL PROTECTED]> wrote:
> > Well, "SELECT *" is only evil if your application relies on a specific 
> > column
> > order to function. The moment you change the table layout and you're using
> > "select *" your application will cease functioning.
> > My app uses tons of select *, but then I wrote an object mapper that queries
> > the information schema at startup - so it's aware of table changes and
> > adjusts accordingly.
> 
> +1
> 
> assumed column ordering is the real enemy.  Here is another place
> where select * is imo better style than non select *:
> 
> select q.*, bar from
> (
>  select a, b,c from foo
> ) q;
> 

What is "bar"?

Were you trying to show how * can be used when you have already
specified the order in a subquery?

That makes sense to me as long as you always see the order in the query,
and as long as it's always well-defined.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] question on renaming a foreign key

[Richard Broersma Jr]

> I made a  HUGE mistake, and used 'UK' as the abbreviation for the  
> united kingdom ( the ISO abbv is 'GB' )
> 
> I've got a database where 8 tables have an FKEY on a table  
> 'location_country' , using the text 'uk' as the value -- so i've got  
> 9 tables that I need to swap data out on
> 
> can anyone suggest a non-nightmarish way for me to do this ?

If your tables are setup to "ON UPDATE CASCASE" then you are fine.

Just updated the main table and PostgreSQL will take care of the rest.

http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] strange error when inserting via a SRF into a table with a foreign key constraint

[Merlin Moncure]

I got the error mesage,

ERROR:  could not find relation 19693 among query result relations,

from a strange interaction between inser, foreign keys and a udf.
After a but of trial and error, I came up with a test case:

create or replace function explode_array(in_array anyarray) returns
setof anyelement as
$$
   select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

create table fp1(a text, b text, primary key(a,b));
create table foo (a text, b numeric, c text);

insert into foo select 'a', 1, explode_array(string_to_array('a,b',
',')); -- works
truncate foo;

alter table foo add foreign key(a,b) references fp1(a,b) on delete cascade;

insert into foo select 'a', 1, explode_array(string_to_array('a,b',
',')); -- fails

I confirmed this on 8.1.4.

merlin

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Karsten Hilbert]

On Wed, Oct 11, 2006 at 02:08:03PM -0700, Jeff Davis wrote:

> > select q.*, bar from
> > (
> >  select a, b,c from foo
> > ) q;
> > 
> 
> What is "bar"?
XMIN, for example

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Is it possible to return custom type as proper ROW?

[Merlin Moncure]

On 10/12/06, Jeff Davis <[EMAIL PROTECTED]> wrote:

> assumed column ordering is the real enemy.  Here is another place
> where select * is imo better style than non select *:
>
> select q.*, bar from
> (
>  select a, b,c from foo
> ) q;
>

What is "bar"?


bar is somthing else, a constant, field from related join, or
whetever.  Also, i am much more liberal about select * in views,
because the decision about columns is pushed out to the view selector:

create view foobar as
 select * from foo natural join bar;

My rationale here is the major point of the view is relating foo to
bar, not choosing columns. Also, if foo/bar gain lose columns, I have
but to drop/recreate the view without changing it's definition.  This
makes the view more functionally dependant on the tables.

merlin

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] strange error when inserting via a SRF into a table with a foreign key constraint

[Richard Broersma Jr]

> create table fp1(a text, b text, primary key(a,b));
> create table foo (a text, b numeric, c text);
> 
> insert into foo select 'a', 1, explode_array(string_to_array('a,b',
> ',')); -- works
> truncate foo;
> alter table foo add foreign key(a,b) references fp1(a,b) on delete cascade;
> 
> insert into foo select 'a', 1, explode_array(string_to_array('a,b',
> ',')); -- fails

does it matter that fp1.b is text but foo.b is numeric?

Regards,

Richar Broersma Jr.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Looping through Arrays

[adam lawrence]

HiI want to be able to loop through an array. The code I have used in the past is: FOR i IN array_lower(arrayvar,1) .. array_upper(arrayvar,1) LOOP  currentvalue:=arrayvar[1][i];   RAISE NOTICE '%', currentvalue; END LOOP;But now I need to loop through the whole array, not just the one element. I want to do something like:FOR j in LOOP    FOR i IN array_lower(arrayvar,1) .. array_upper(arrayvar,1) LOOP    currentvalue:=arrayvar[j][i];     RAISE NOTICE '%', currentvalue;    END LOOP;END LOOP;  How to I set the upper and lower bounds for j? 
		 
Yahoo! Messenger - with free PC-PC calling and photo sharing.

Re: [GENERAL] UTF-8

[Martins Mihailovs]

Martijn van Oosterhout wrote:

On Fri, Oct 06, 2006 at 12:44:43PM +0300, Martins Mihailovs wrote:
I would be a glad to hear your solutions, experience in web application 
with multi languages (searching with indexing, sorting and others 
problems with multi byte encoding).


For developers: what are your future plans about UTF-8 in Postgres?


Currnetly postgresql using the OS to do sorting, upper/lower
conversions, etc. Which means you stuck with whatever your OS does,
which may or may not be any good.

The fix is easy, make postgresql use some kind of system independant
locale database to do these transformations. We just need someone to
write it




There are some misunderstood. Im using Linux 2.6.16.4, postgresql 8.1.4, 
(there are one of locale:   lv_LV.utf8, for Latvian language). But if I 
want do "lower", then with standard latin symbols all is ok, but with 
others special symbols (like umlaut in Germany) there is problems, and 
sorting is going not like alphabet but like latin alphabet and specials 
symbols after. :(


For sorting there are solution function nls_to_string(), witch is using 
C library and system locales for converting strings to binary. But it 
dont work for search (like, tsearch2, e.c.)


I dont know what to do, but function lower() is not working for special 
symbols (LATIN 7).


And I tried in windows - the same... (as I know windows knows how to 
converts latvian symbols lower, upper... in UTF-8)...






---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] database corruption question

[maa1666]

In the document "Transaction Processing in PostgreSQL"
( http://www.postgresql.org/files/developer/transactions.pdf )
I read :

"Postgres transactions are only guaranteed atomic if a disk page write
is an atomic action. On most modern hard drives that's true if a page
is a physical sector, but most people run with disk pages configured as
8K or so, which makes it a little more dubious whether a page write is
all-or-nothing."

I have a battery-backed disk controller.
In this case:

- can such partial writes happen ?

- I suppose that a file system never splits a 8K page write request
into smaller chunks, and a 8K page will always be fully written ?

- with a battery-backed disk controller, a database never gets
corrupted ?

- Is there a tool that checks if a database is not corrupted ?

Best regards

Nicolas Riesch


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Storing Procedures / Transactions

[Christian Kasprowicz]

Mabye I made myself not clear enough- sorry for that...What I want is having a statement like: PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))BEGIN   ---check if something is valid   ---compute something   ---store values I got via THIS query and put them in table A, B and C   ---see wether everything is okCOMMIT; ...which I execute from a client like: exec MyProcedure(Value1, Value2, Value3) Hope someone can talk me through this… ;) Thanks in advance,Chris

 

[GENERAL] List of supported 64bit OS

[Stanislaw Tristan]

Please, provide a list of OS in which PostgreSQL have a native 64bit 
support.
Thanks! 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Problem with executing PostgreSQL on Embedded Linux

[woonhak kang]

I am porting PostgreSQL 8.1.4 to Embedded Linux 2.x.
Cross-compiling had little problem with zic (timezone),
but I changed cross-compiled zic to native-compiled one so I can 
cross-compile correctly I think.

However, executing postgresql on my ebmedded linux has problem with initdb.
(root accout problem was solved by by-passing checking uid==0 in some source 
files)

When I execute initdb, creating some directories and selecting default 
variable definitions were fine.
While creating template1 database in $LOCAL_DB/base/1, it exits with 
following error message.

Error message - /pgsql/postgres: can't resolve symbol '__isinf'

Anybody know about that problem? 



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Automatic row numbering / sequence in view ?

[Arnaud Lesauvage]

Hi List !

I need to add a column to a view, which would contain an automatically 
generated sequence.
An automatic row numbering would do the trick (I only need unique 
numbers, that's all), but I don't even know how to achieve this.

Does anybody have a solution for this problem ?

Thanks a lot !
--
Arnaud

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] left outer join on multi tables

[Ge Cong]

Thank you, But there seems to be syntax errors. Could you help?


Richard Broersma Jr wrote:
> > table name
> > {
> >id integer primary key
> >first_name text,
> >middle_name text,
> >last_name text
> > }
> >
> > table address
> > {
> >id integer primary key
> >number int,
> >street text,
> >city text,
> >state text
> > }
> >
> > table work
> > {
> >id integer primary key
> >hours text,
> >shift
> > }
> >
> > table person
> > {
> > id integer primary key
> > namid integer references name(id),
> > addressid integer referenes address(id),
> > workid integer references work(id)
> > }
> > nameid, addressid or workid in person table may be empty.
> > I would like to make a view which shows all information about a person
> > in one row
> > first_name, last_name, street, city, workhours, ...
> > In the peron table, if  the workid, addressid, or nameid is empty, just
> > do not display the correspodent information.
> > I think I need left outer join, but I do not know how to use it. Could
> > anyone help?
>
> CREATE VIEW AS
> SELECT N.first_name,
>N.last_name,
>A.street,
>A.city,
>W.hour,
>
> FROM   person P
> LEFT JOIN ON (P.namid = N.id)
> LEFT JOIN ON (P.addressid = A.id)
> LEFT JOIN ON (P.workid = W.id)
> ;
>
> Hopefully this is all correct and is what you are looking for?
>
> Regards,
>
> Richard Broersma Jr.
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] more anti-postgresql FUD

[alexei . vladishev]

Hello,

I'm author and maintainer of ZABBIX and the manual. I would like to add
some comments to the thread.

First of all, ZABBIX supports three database engines: MySQL, Oracle and
PostgreSQL. It uses absolutely standard SQL, same for all three
database engines. We have absolutely no intention to push or recommend
one of those. I'm big fan of PostgreSQL and having a choice I would
choose PostgreSQL for anything except ZABBIX.

Unfortunately PostgreSQL performs much slower than MySQL doing large
number of updates for one single table. By its nature ZABBIX requires
to execute hundreds of updates per second for large installations.
PostgreSQL cannot handle this nicely.

Do a simple test to see my point:

1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop

I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
database settings.

MySQL performs very well, approximately 15000-2 updates per second
with no degradation of performance.

PostgreSQL does approximately 1600 records per second for the first
1, then 200rps for the first 100k records, and then slower and
slower downgrading to 10-20 rps(!!!) when reaching 300k.

The manual states that PostgreSQL works ten times slower for ZABBIX, in
reality it is much worser.

Yes, I'm aware of autovacuuming, etc. But it eats resources and I
cannot handle to run it periodically because I want steady performance
from my application. I do not want to see ZABBIX performing slower just
because of database housekeeper.

Several years ago I contacted PostgreSQL developers but unfortunately
the only answer was "Run vacuum. We won't change PostgreSQL to reuse
unused tuples for updates".

Perhaps something has changed in recent releases of PostgreSQL, I don't
think so. Please correct me if I'm wrong.

Kind regards,
Alexei


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] CUBE, ROLLUP, GROUPING SETS?

[stig erikson]

Hi.
Are there any plans to implement CUBE, ROLLUP and/or GROUPING SETS in future 
PostgreSQL versions?
I could not find any info on the TODO-page.

/stig

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

[Jim Nasby]

PostgreSQL doesn't really have any unstable versions (unless you're  
talking about code right out of CVS). The closest you might come will  
be the initial release of a major version, or of course a beta/ 
release candidate. (Note that the first "dot" indicates a major  
version for PostgreSQL. 8.0 is a major version, as is 8.1). But even  
our betas are generally very solid and stable. If you're looking for  
the utmost in stability, you probably want to go with 8.1.4 (or  
8.1.5, which should be out RSN).


What does happen from time-to-time is a complex bug (usually some  
kind of a race condition) that has the potential to corrupt data will  
be discovered. These are generally very hard to reproduce, and  
usually go back a number of major versions. This is why it's  
important to update to newer minor versions (ie: 8.1.3 to 8.1.4) when  
they come out.


Another issue you'll be facing is that windows support is fairly new;  
8.0 was the first release that had it. So the performance of the  
windows version has been getting better, and some minor bugs are  
still being found and fixed.


I know that may sound a bit scary, but the truth is it's no different  
for commercial databases; they just hide it from you.


Of course, unless you've discovered some magic process for producing  
bug-free code, you'll undoubtedly have to send out bug fixes for your  
product as well. ;) It's very painless to do a minor version upgrade  
of PostgreSQL, so it makes sense to include that with the updates you  
send out for your product.


On Oct 10, 2006, at 9:27 AM, Ravindran G - TLS, Chennai. wrote:

Thanks for your comments and moving it to general group.

We would like to know which is the most stable version in  
Postgresql ?.

Because Postgresql may undergo changes and will have the version
incremented. In this case, do we need to do the upgrade  
frequently ?.  Of
course, its good have the latest version but this cannot be done  
each and

every time after deploying our application to end customers.

Please advise.

Regards, Ravi


-Original Message-
From: Jim C. Nasby [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 7:46 PM
To: Ravindran G - TLS, Chennai.
Cc: pgsql-general@postgresql.org; Hari Krishna D - TLS , Chennai;  
Sasikala V

- TLS , Chennai
Subject: Re: [PERFORM] Postgre 8.0 Installation - Issues


Moving to -general.

On Tue, Oct 10, 2006 at 04:17:06PM +0530, Ravindran G - TLS,  
Chennai. wrote:

All,

We are facing few issues while we install Postgres 8.0 in Windows  
2000

Japanese OS. Installer kit name : postgresql-8.0-ja


Is there a reason you're not using 8.1.4? 8.0 was the first windows
release, and as such there's a number of issues that were improved in
8.1. You should at least be using the latest 8.0 version (8.0.8).

Scenario 1: While installing PostGRE 8.0, we got an logon failure  
at the

end

BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist...

of installing the component telling that it failed to produce the  
process
for initdb and also that the user name was not able to be  
recognized or

the

password is wrong. After the OK button was clicked the whole process

rolled

back automatically and the PostGRE got uninstalled.


Make sure that you have the right password for the account that
PostgreSQL will be running under. I often find it's easiest to just
delete that account and let the installer create it for me.

Scenario 2: In one of the computers we managed to install the  
PostGRE 8.0
but the database initialization could not be performed. While  
creating the
database using the Credb patch we got an error telling that the  
tables

were

missing and the connection with the local host failed.

Scenario 3: For one of the machines the database has also been  
created but
once the system is restarted the PostGRE does not work and we get  
the same

error as in the Scenario2.


These could be issues surrounding administrator rights. PostgreSQL  
will

refuse to start if the account it's running under has Administrator
rights.

Please shed some light on this. If this question is not relevant  
to this

group, please redirect us...

Thanks and regards,
Ravi
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential  
and

intended for the


named recipient(s) only. It shall not attach any liability on the

originator or HCL or its


affiliates. Any views or opinions presented in this email are  
solely those

of the author and


may not necessarily reflect the opinions of HCL or its affiliates.  
Any

form of reproduction,


dissemination, copying, disclosure, modification, distribution  
and / or

publication of this


message without the prior written consent of the author of this e- 
mail is

strictly


prohibited. If you have received this email in error please delete  
it and

notify the sender


immediately. Before opening any mail and attachments please check  
them for

viruses and


defect.

---(end of

Re: [GENERAL] more anti-postgresql FUD

[Tim Tassonis]

Steve Crawford schrieb:

Guy Rouillier wrote:

Andrew Sullivan wrote:

On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:

Some days I think database independence is a myth.

On the day when you don't, please tell me what application you found
where it isn't.  I want to buy the developers a drink.  Or maybe a
bar.  

The Mantis bug tracking software http://www.mantisbt.org/ now works with
PostgreSQL (was developed with MySQL.)  It works equally well with both,
including automated installation.



I find that "database independence" == "lowest common denominator". I
may have missed something, but a quick scan of the Mantis code didn't
reveal any use of triggers, rules, foreign-keys, user-defined types, etc.


Well, that is hardly surprising. What exactly is your point?

If you want to write portable software, you usually stay with generally 
available, standardized features or API's, be it "database independent", 
"platform independent", you name it. You certainly don't go for 
user-defined types. I really think all the nice features and 
capabilities of PostgreSQL are great, but I would never, ever start 
using any of them extensively in a project that might have to run on 
another database. Ever heard of vendor lock-in and "embrace and expand"?




Whenever I see that a project has been "ported" to PostgreSQL I can
usually be sure that it is not a project that was designed to take
advantage of the features and capabilities that PG offers.

But I suspect that porting something that uses all the features of mySql
to PostgreSQL will be far easier than porting something that uses all
the features of PostgreSQL over to mySql (if it is possible at all).


You're certainly right here (I did this before), that's why a lot of 
projects can support PostgreSQL when they started off with mySql. You 
can bet that isn't the case with projects that started off with Oracle 
(care to rewrite a few hundred triggers, packages and statements?).


Bye
Tim



Cheers,
Steve


---(end of broadcast)---
TIP 6: explain analyze is your friend



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] question on renaming a foreign key

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/11/06 16:10, Richard Broersma Jr wrote:
>> I made a  HUGE mistake, and used 'UK' as the abbreviation for the  
>> united kingdom ( the ISO abbv is 'GB' )
>>
>> I've got a database where 8 tables have an FKEY on a table  
>> 'location_country' , using the text 'uk' as the value -- so i've got  
>> 9 tables that I need to swap data out on
>>
>> can anyone suggest a non-nightmarish way for me to do this ?
> 
> If your tables are setup to "ON UPDATE CASCASE" then you are fine.
> 
> Just updated the main table and PostgreSQL will take care of the rest.

I doesn't appear that ALTER TABLE can change constraint characteristics.

You'd have to drop/recreate, no?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFLXqUS9HxQb37XmcRAhNZAJ9sNEgOoEivv89oVuH5yF7rm71ztQCeM9Cn
r6BtOzSoE3+4SxvAlu7teDk=
=KxPL
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] query log corrupted-looking entries

[George Pavlov]

> What PG version is this, on what operating system?  Do you have
> redirect_stderr enabled?

Sorry, I should have included that:

PostgreSQL 8.1.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)

And yes, redirect_stderr = on. I have no definitive way of reproducing
it, just a vague one: "hit the server with lots of queries". Some
solution would be very welcome because once I have a "bad" log file like
that it is very hard (at least for me) to clean it up by hand.

George


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] more anti-postgresql FUD

[Joshua D. Drake]

> Well, that is hardly surprising. What exactly is your point?
> 
> If you want to write portable software, you usually stay with generally
> available, standardized features or API's, be it "database independent",
> "platform independent", you name it. You certainly don't go for
> user-defined types. I really think all the nice features and
> capabilities of PostgreSQL are great, but I would never, ever start
> using any of them extensively in a project that might have to run on
> another database. Ever heard of vendor lock-in and "embrace and expand"?

Bah! Ever heard of crappy software because of database independence? I
have yet to see a good application that supports "database independence".

Joshua D. Drake


> 
>>
>> Whenever I see that a project has been "ported" to PostgreSQL I can
>> usually be sure that it is not a project that was designed to take
>> advantage of the features and capabilities that PG offers.
>>
>> But I suspect that porting something that uses all the features of mySql
>> to PostgreSQL will be far easier than porting something that uses all
>> the features of PostgreSQL over to mySql (if it is possible at all).
> 
> You're certainly right here (I did this before), that's why a lot of
> projects can support PostgreSQL when they started off with mySql. You
> can bet that isn't the case with projects that started off with Oracle
> (care to rewrite a few hundred triggers, packages and statements?).
> 
> Bye
> Tim
> 
>>
>> Cheers,
>> Steve
>>
>>
>> ---(end of broadcast)---
>> TIP 6: explain analyze is your friend
> 
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] more anti-postgresql FUD

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/11/06 14:48, Chris Browne wrote:
> [EMAIL PROTECTED] (Andrew Sullivan) writes:
>> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
[snip]
> Oh, and a cluster of IBM p570s would probably be enough to run a 20
> user system :-(.  [Actually, that's probably not *entirely* fair; I
> once administered an R/3 system supporting ~30 users on a uniprocessor
> DEC Alpha with 256MB of RAM, which by modern standards is pretty
> pedestrian...]


My first programming job was walloping COBOL on a 1.9 MIPS IBM 43xx
with only 6MB RAM.  Supported *70* online users and had a
*relational* database (CA Datacom-DB).

Of course, the FEPs, block-mode terminals and CICS were the crucial
difference.

Damned shame that Unix killed that mentality, and that client-server
was usually implemented so poorly.


- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFLX6wS9HxQb37XmcRAopdAJ9kFEeHx0KXEFhhGGq+REuiYRh7GgCgnxR9
Urj+S/Ce0+b9KrqP4gPVyeM=
=wWG0
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] question on renaming a foreign key

[Richard Broersma Jr]

> >> can anyone suggest a non-nightmarish way for me to do this ?
> > If your tables are setup to "ON UPDATE CASCASE" then you are fine.
> > Just updated the main table and PostgreSQL will take care of the rest.

> I doesn't appear that ALTER TABLE can change constraint characteristics.
> You'd have to drop/recreate, no?

Now that you mention it, I've never tried it or seen it done. Here I what I 
came up with:

CREATE TABLE foo
(
id  text constraint foo_pri_key Primary Key
);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pri_key" 
for table "foo"
CREATE TABLE

INSERT INTO foo (id) values('hello');
INSERT 0 1

CREATE TABLE bar
( 
id serial constraint bar_pri_key Primary key, 
fooid text constraint bar_foo_for_key References foo (id)
);

NOTICE:  CREATE TABLE will create implicit sequence "bar_id_seq" for serial 
column "bar.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "bar_pri_key" 
for table "bar"
CREATE TABLE

INSERT INTO bar (fooid) VALUES ('hello');
INSERT 0 1

update foo SET id = 'goodbye';
ERROR:  update or delete on "foo" violates foreign key constraint 
"bar_foo_for_key" on "bar"
DETAIL:  Key (id)=(hello) is still referenced from table "bar".

alter TABLE bar DROP CONSTRAINT bar_foo_for_key;
ALTER TABLE

 ALTER TABLE bar ADD constraint new_bar_foo_for_key foreign key (fooid) 
references foo (id) on
update cascade;
ALTER TABLE

update foo set id = 'goodbye';
UPDATE 1

select * from bar;
 id |  fooid
+-
  1 | goodbye
(1 row)


It is nice to see things work so well. :-)

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] database corruption question

[Tom Lane]

[EMAIL PROTECTED] writes:
> In the document "Transaction Processing in PostgreSQL"
> ( http://www.postgresql.org/files/developer/transactions.pdf )

That's very, very old information.

> I read :
> "Postgres transactions are only guaranteed atomic if a disk page write
> is an atomic action.

Not true since the addition of full-page images to WAL.  These days the
only thing you really need to worry about is whether your disk drive
lies about write-complete.  A battery backed write cache is probably
safe enough.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] question on renaming a foreign key

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/11/06 18:53, Richard Broersma Jr wrote:
 can anyone suggest a non-nightmarish way for me to do this
 ?
>>> If your tables are setup to "ON UPDATE CASCASE" then you are
>>> fine. Just updated the main table and PostgreSQL will take
>>> care of the rest.
> 
>> I doesn't appear that ALTER TABLE can change constraint
>> characteristics. You'd have to drop/recreate, no?
> 
> Now that you mention it, I've never tried it or seen it done.
> Here I what I came up with:
> 
[snip]
> 
> 
> It is nice to see things work so well. :-)

It would be interesting to see how well it works on a 50M row table.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFLYaSS9HxQb37XmcRAmO+AKDH1ILeOKJEYitfY6LUTrRZKjk33wCdGIZW
ZN0L1iAJphWTNGlAoz63E3E=
=3zQr
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] more anti-postgresql FUD

[Geoffrey]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/11/06 14:48, Chris Browne wrote:

[EMAIL PROTECTED] (Andrew Sullivan) writes:

On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:

[snip]

Oh, and a cluster of IBM p570s would probably be enough to run a 20
user system :-(.  [Actually, that's probably not *entirely* fair; I
once administered an R/3 system supporting ~30 users on a uniprocessor
DEC Alpha with 256MB of RAM, which by modern standards is pretty
pedestrian...]



My first programming job was walloping COBOL on a 1.9 MIPS IBM 43xx
with only 6MB RAM.  Supported *70* online users and had a
*relational* database (CA Datacom-DB).

Of course, the FEPs, block-mode terminals and CICS were the crucial
difference.

Damned shame that Unix killed that mentality, and that client-server
was usually implemented so poorly.



You had that much memory?  Used to run a time reporting system on a 3b2 
400, 4MB Ram, WE32100 10MHz processor, 1.1 MIPS.  Flat file home grown 
database system that used indices in shared memory and semaphore 
communication between three continuously running processes.


The application ran in pretty much all the AT&T factories at the time.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] question on renaming a foreign key

[Richard Broersma Jr]

> >> I doesn't appear that ALTER TABLE can change constraint
> >> characteristics. You'd have to drop/recreate, no?
> > 
> > Now that you mention it, I've never tried it or seen it done.
> > Here I what I came up with:
> > 
> [snip]
> > 
> > 
> > It is nice to see things work so well. :-)
> 
> It would be interesting to see how well it works on a 50M row table.

Good point, but hopefully only small fraction of the 50 M rows would be 
affected and hopefully
updating primary keys isn't a common occurrence.

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] question on renaming a foreign key

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/11/06 19:15, Richard Broersma Jr wrote:
 I doesn't appear that ALTER TABLE can change constraint 
 characteristics. You'd have to drop/recreate, no?
>>> Now that you mention it, I've never tried it or seen it done.
>>>  Here I what I came up with:
>>> 
>> [snip]
>>> 
>>> It is nice to see things work so well. :-)
>> It would be interesting to see how well it works on a 50M row
>> table.
> 
> Good point, but hopefully only small fraction of the 50 M rows
> would be affected and hopefully updating primary keys isn't a
> common occurrence.

I was thinking of the ALTER TABLE ... ADD CONSTRAINT column.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFLY2nS9HxQb37XmcRAjYNAKDibxx/tTAXZxg8uCp3jJfaTGeb/ACgqQdT
yCvtOQMz0vGak51Gg1sC/OE=
=Q+Fz
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] more anti-postgresql FUD

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/11/06 19:10, Geoffrey wrote:
> Ron Johnson wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 10/11/06 14:48, Chris Browne wrote:
>>> [EMAIL PROTECTED] (Andrew Sullivan) writes:
 On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> [snip]
>>> Oh, and a cluster of IBM p570s would probably be enough to run a 20
>>> user system :-(.  [Actually, that's probably not *entirely* fair; I
>>> once administered an R/3 system supporting ~30 users on a uniprocessor
>>> DEC Alpha with 256MB of RAM, which by modern standards is pretty
>>> pedestrian...]
>>
>> 
>> My first programming job was walloping COBOL on a 1.9 MIPS IBM 43xx
>> with only 6MB RAM.  Supported *70* online users and had a
>> *relational* database (CA Datacom-DB).
>>
>> Of course, the FEPs, block-mode terminals and CICS were the crucial
>> difference.
>>
>> Damned shame that Unix killed that mentality, and that client-server
>> was usually implemented so poorly.
>> 
> 
> You had that much memory?  Used to run a time reporting system on a 3b2

Hey, I remember those.

A 3b2 was my first exposure to Unix.  At the time I was a VMS
programmer who loved DCL, and was *not* impressed by Unix.

I *still* use VMS at work, and while DCL is really showing it's age
and while bash 3.1 on Linux (which I'm writing this from now) is
light-years better than sh, VMS is still a great "DP" operating system.

> 400, 4MB Ram, WE32100 10MHz processor, 1.1 MIPS.  Flat file home grown
> database system that used indices in shared memory and semaphore
> communication between three continuously running processes.
>
> The application ran in pretty much all the AT&T factories at the time.

Flat files and minimal user interaction?  Bah.

Now, if *one* machine ran a whole AT&T factory, that would be
impressive.  Of course, VAX/VMS would do it, and no one would bat an
eyelash.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFLY1tS9HxQb37XmcRAvXVAJ9sJrS8FBFDUyAqLTuff3vHun/qYQCgl/Fb
PIc0DnyGF4jitjWBjF1H+z8=
=9ePW
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] more anti-postgresql FUD

[Merlin Moncure]

On 11 Oct 2006 07:54:52 -0700, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

Hello,

I'm author and maintainer of ZABBIX and the manual. I would like to add
some comments to the thread.


just so you know, I brought this up after taking a look at the zabbix
software, which is in my opinion very excellent.  I came across a
little strong in my comments and peter e was correct in pointing out
that the performance related comments were not 'fud'.  I felt a little
bad after opening this thread but you have to take this in context of
the bigger picture.  The postgresql poeple have been dealing with
(sometimes) unfounded prejudices for years.


Unfortunately PostgreSQL performs much slower than MySQL doing large
number of updates for one single table. By its nature ZABBIX requires
to execute hundreds of updates per second for large installations.
PostgreSQL cannot handle this nicely.

Do a simple test to see my point:

1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop


this is a very contrived test:
1. nothing really going on
2. no data
3. single user test
4. zabbix doesn't do this, nor does anything else
5. proves nothing.

zabbix is a bit more complex than that with multiple users, tables and
the ocassional join.  With a high number of servers in play things
might go differently than you expect.


I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
database settings.


8.0, 8.1, and 8.2 are all a bit faster at these types of queries, just
so you know.  8.1 and up i believe have autovcacuum defaulted on.  In
fairness, vacuuming in the 7.1x days was a different beast.


MySQL performs very well, approximately 15000-2 updates per second
with no degradation of performance.

PostgreSQL does approximately 1600 records per second for the first
1, then 200rps for the first 100k records, and then slower and
slower downgrading to 10-20 rps(!!!) when reaching 300k.

The manual states that PostgreSQL works ten times slower for ZABBIX, in
reality it is much worser.

Yes, I'm aware of autovacuuming, etc. But it eats resources and I
cannot handle to run it periodically because I want steady performance
from my application. I do not want to see ZABBIX performing slower just
because of database housekeeper.


vacuum gives you stable performance, not vice verca.  I would imagine
zabbixs server scalability is driven by a number of factors.


Several years ago I contacted PostgreSQL developers but unfortunately
the only answer was "Run vacuum. We won't change PostgreSQL to reuse
unused tuples for updates".



Perhaps something has changed in recent releases of PostgreSQL, I don't
think so. Please correct me if I'm wrong.


well, I am playing with zabbix with the possible eventuality of
rolling it out in our servers I might be able to get you some hard
data on performance.  By the way, I'm currently managing a
spectactularly large mysql database which is getting moved to
postgresql with the next release of the software -- in part because I
was able to show that postgresql gave much more reliable performance
in high load envirnonments.

In light of this discussion, I might be interested in running a little
test to see how zabbix would hold up on postgresql under a
artificially high load.  If I was to show that things were quite so
one-sided as you assumed, would you be willing to say as much in your
documentation? :-)

merlin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Looping through Arrays

[Merlin Moncure]

On 10/10/06, adam lawrence <[EMAIL PROTECTED]> wrote:

But now I need to loop through the whole array, not just the one element. I
want to do something like:

FOR j in LOOP
FOR i IN array_lower(arrayvar,1) .. array_upper(arrayvar,1) LOOP
currentvalue:=arrayvar[j][i];
RAISE NOTICE '%', currentvalue;
END LOOP;
END LOOP;

 How to I set the upper and lower bounds for j?


FOR j IN array_lower(arrayvar, 2)..array_upper(arrayvar, 2) LOOP

:-)

merlin

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] more anti-postgresql FUD

[Stephen Frost]

* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote:
> Unfortunately PostgreSQL performs much slower than MySQL doing large
> number of updates for one single table. By its nature ZABBIX requires
> to execute hundreds of updates per second for large installations.
> PostgreSQL cannot handle this nicely.

If you refuse to vacuum (or have the table autovacuumed) then sure.  Of
course, I don't know of anyone who actually uses PostgreSQL who would
run a system like that.

> I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
> sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
> database settings.

Don't say 'sorry' to us for using MyISAM (though it pretty much
invalidates the test), say 'sorry' to your users...  You can try running
Postgres with fsync=off but I would strongly recommend against it in a
production environment (just like I'd strongly recommend against
MyISAM).

> MySQL performs very well, approximately 15000-2 updates per second
> with no degradation of performance.
> 
> PostgreSQL does approximately 1600 records per second for the first
> 1, then 200rps for the first 100k records, and then slower and
> slower downgrading to 10-20 rps(!!!) when reaching 300k.

If you periodically vacuum the table (where periodically most likely
would mean after some number of write transactions) I expect you'd find
Postgres performance to at *least* stabalize.  If you vacuum with a
periodicity reasonably ratioed to your update statement frequency you'd
find that it will *improve* performance and Postgres will provide a
*consistant* performance.

> Yes, I'm aware of autovacuuming, etc. But it eats resources and I
> cannot handle to run it periodically because I want steady performance
> from my application. I do not want to see ZABBIX performing slower just
> because of database housekeeper.

This, above all things imv, would be FUD here.  Vacuum/autovacuum aren't
something to be feared as damaging, detrimental, or resource hogging.
Vacuum doesn't take an exclusive lock and moves along quite decently if
done with an appropriate frequency.  If you wait far, far, too long to
do a vacuum (to the point where you've got 10x as many dead tuples as
live ones) then sure it'll take a while, but that doesn't make it
resource hogging when you consider what you're having it do.

> Several years ago I contacted PostgreSQL developers but unfortunately
> the only answer was "Run vacuum. We won't change PostgreSQL to reuse
> unused tuples for updates".

That's exactly what vacuum *does*, it marks dead tuples as being
available for reuse.  Please understand that vacuum != vacuum full.

> Perhaps something has changed in recent releases of PostgreSQL, I don't
> think so. Please correct me if I'm wrong.

I'm afraid there's a bit of a misunderstanding about what vacuum is for
and how it can affect the behaviour of Postgres.  Please, please forget
whatever notion you currently have of vacuum and actually run some tests
with it, and post back here (or -performance) if you run into problems,
have questions or concerns.  I expect you could also tune autovacuum to
be frequent enough on the appropriate tables that you wouldn't have to
intersperse your own vacuum commands in.  Also, as pointed out, current
releases (8.1) also have quite a few enhanments and performance 
improvements.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Storing Procedures / Transactions

[Merlin Moncure]

On 10/9/06, Christian Kasprowicz <[EMAIL PROTECTED]> wrote:

PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))
BEGIN
   ---check if something is valid
   ---compute something
   ---store values I got via THIS query and put them in table A, B and C
   ---see wether everything is ok
COMMIT;

...which I execute from a client like: exec MyProcedure(Value1, Value2,
Value3)

Hope someone can talk me through this… ;)


documentation is your friend :  You should read through section V.
'Extending SQL'  PostgreSQL has very rich extensibility features.
IMO, you should focus on pl/pgsql.

create or replace function foo(a int, b text) returns void as
$$
 declare
   valid boolean;
   something numeric;
   values record;
   ok boolean default true;
 begin
   select into valid f1 = a where f2 = b;
   something := 3 + a;
   for values in select * from bar order by z loop
 if values.v >= something then
   ok := false;
 end if;
 update foobar set x = x + 1set where key = b;
   end loop;
   return;
 end;
$$ language plpgsql;

merlin

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] more anti-postgresql FUD

[snacktime]

1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop

I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
database settings.

MySQL performs very well, approximately 15000-2 updates per second
with no degradation of performance.

PostgreSQL does approximately 1600 records per second for the first
1, then 200rps for the first 100k records, and then slower and
slower downgrading to 10-20 rps(!!!) when reaching 300k.


Something is wrong with your test code.  If I had to guess I would say
you did all the updates in a single transaction without committing
them, in which case yes it will slow down until you commit.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Automatic row numbering / sequence in view ?

[A. Kretschmer]

am  Tue, dem 10.10.2006, um 16:33:03 +0200 mailte Arnaud Lesauvage folgendes:
> Hi List !
> 
> I need to add a column to a view, which would contain an automatically 
> generated sequence.
> An automatic row numbering would do the trick (I only need unique 
> numbers, that's all), but I don't even know how to achieve this.
> Does anybody have a solution for this problem ?

create sequence foo;
select nextval('foo'), * from ;


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] user defined aggregate with multiple arguments

[Bill Eaton]

I have a need to create some statistical functions in PostgreSQL that 
will take multiple "column" arguments and return multiple values.


A good example would be a least squares fit of a line through a set of 
(x,y) points. In this case, I would want the function to accept two 
"columns" of data (i.e. x data and y data) as arguments and return two 
parameters: the slope and intercept of a line.


The slope is given by 
  [sum(x^2)*sum(y) - sum(x)*sum*x*y)] / [M*sum(x^2) -(sum(x))^2]
It's not *that* complicated, but it's not that easy to remember either. 
And if you're already doing a lot of work to tease out the x and y 
points from various tables, it simplifies your query if you can do a 
function call.


I can't tell from what have read about user defined functions and user 
defined aggregates whether this kind of function is possible.


Bill Eaton

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] user defined aggregate with multiple arguments

[Bill Eaton]

> I have a need to create some statistical functions in PostgreSQL that 
will take multiple

> "column" arguments and return multiple values.
>
> A good example would be a least squares fit of a line through a set 
of (x,y) points.
> In this case, I would want the function to accept two "columns" of 
data (i.e. x data and
> y data) as arguments and return two parameters: the slope and 
intercept of a line.

>
> The slope is given by   [sum(x^2)*sum(y) - sum(x)*sum*x*y)] / 
[M*sum(x^2) -(sum(x))^2]

>
> It's not *that* complicated, but it's not that easy to remember 
either. And if you're already
> doing a lot of work to tease out the x and y points from various 
tables, it simplifies your query

> if you can do a function call.
>
> I can't tell from what have read about user defined functions and 
user defined aggregates whether

> this kind of function is possible.
>
> Bill Eaton

I just notice that multiple inputs to aggregates are allowed in the 
upcoming 8.2 release. This gives me a great incentive to play with the 
beta and upgrade from 8.0.


One question remains: how about multiple outputs? Can I have a ROW as a 
return value -- i.e. something like SLOPE and INTERCEPT in the original 
example?


-Bill Eaton


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Problem with executing PostgreSQL on Embedded Linux

[Thomas Pundt]

Hi,

On Tuesday 10 October 2006 19:23, woonhak kang wrote:
| I am porting PostgreSQL 8.1.4 to Embedded Linux 2.x.
[...]
| When I execute initdb, creating some directories and selecting default
| variable definitions were fine.
| While creating template1 database in $LOCAL_DB/base/1, it exits with
| following error message.
|
| Error message - /pgsql/postgres: can't resolve symbol '__isinf'

"isinf" normally lives in libm; libm normally gets linked to the postgres 
binary:

cd ~/postgresql-8.1.4/src
grep -- -lm Makefile.global
LIBS = -lssl -lcrypto -lz -lreadline -lcrypt -lresolv -lnsl -ldl -lm

ldd /usr/bin/postgres | grep libm
libm.so.6 => /lib/tls/libm.so.6 (0x40277000)

nm /lib/tls/libm.so.6 | grep isinf
d0c0 t __GI___isinf
000140f0 t __GI___isinff
0001bc50 t __GI___isinfl
d0c0 t __isinf
000140f0 t __isinff
0001bc50 t __isinfl
d0c0 t isinf
000140f0 t isinff
0001bc50 t isinfl

So the obvious question seems to be: is your postgres binary linked against
libm? Just a guess though - I'm not on any kind of Embedded Linux ...

Ciao,
Thomas

-- 
Thomas Pundt <[EMAIL PROTECTED]>  http://rp-online.de/ 

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] more anti-postgresql FUD

[Chris Mair]

> Do a simple test to see my point:
> 
> 1. create table test (id int4, aaa int4, primary key (id));
> 2. insert into test values (0,1);
> 3. Execute "update test set aaa=1 where id=0;" in an endless loop
> 
> I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
> sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
> database settings.
> 
> MySQL performs very well, approximately 15000-2 updates per second
> with no degradation of performance.
> 
> PostgreSQL does approximately 1600 records per second for the first
> 1, then 200rps for the first 100k records, and then slower and
> slower downgrading to 10-20 rps(!!!) when reaching 300k.

Hi,
it would be cool if you could at least:

 - bundle your updates into transactions of, say, 1000 updates at a time
   i.e. wrap a BEGIN; END; around a 1000 of them
 - run postgresql with fsync off, since you're using MyISAM
 - run PostgreSQL at least 8, since you're running MySQL 5

I'd bet MySQL would still be faster on such an artificial, single user
test, but not *that much* faster.

If you don't want to install 8.0, could you maybe at least do the first
two items (shouldn't be a lot of work)...?

Which client are you using? Just mysql/psql or some API?

Bye, Chris.











---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/