Re: [GENERAL] How to write a function that manipulates a set of results

[Ashley Moran]

On 15 Mar 2007, at 00:21, Tom Lane wrote:

Temp tables stay in RAM until they are bigger than temp_buffers.   
If you

need them to be big and quick, maybe it would be appropriate to use
indexes (note these count towards temp_buffers), ANALYZE, etc.


You do need to realize that creation of a temp table involves making
entries in the system catalogs.  If you can set it up so that you  
reuse

the same temp table(s) for the life of a connection, you'll save a lot
of thrashing and need for catalog vacuuming (the ON COMMIT DELETE ROWS
option for temp tables might help here).  Other than that gotcha, they
should be pretty efficient.


Thanks for these tips, they are extremely useful

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

Re: [GENERAL] Plperl Question

[Chris Coleman]

Great - that works fine.

If I continue to quote all values that I place into the query string
could I be running into problems later down the line?  As far as I can
work out then PG will try to convert the quoted values to whatever the
correct datatype for the column is anyway.

This seems much cheaper than trying to determine the data types by
querying the pg_catalog tables, and much cleaner assuming it is free
from any pitfalls...

Thanks again,
Chris

-Original Message-
From: Stuart Cooper [mailto:[EMAIL PROTECTED] 
Sent: 14 March 2007 22:25
To: Chris Coleman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Plperl Question

Answers in place:

> I'm trying to write a plperl function to copy the new row e.g. NEW in
> plpgsql into another table.  I was looking for a similar effect to the

> INSERT INTO blah VALUES (NEW.*)

> Syntax that can be used in plpgsql.  So fat the best I have come up
with
> is:

> $collist = "";
> $vallist = "";
> while (($col, $val) = each(%{$_TD->{new}}))
> {
> $collist .= ($col.",");
>
> #Need to fix issues here with quoting in the value list.
> $vallist .= ("'".$val."',");
> }
> chop($collist);
> chop($vallist);

> However, this leads to issues with numerical columns being quoted, and
> worse still NULL numerical column being entered as '' which results in
> "Invalid syntax for integer" errors.

NULL values will have $val undefined, so you can just avoide adding them
to
$collist and $vallist in the first place

next if ( ! defined $val); # don't add NULL values

as the first line of your while loop body will easily acheive this.

Numbers are trickier- you could go with the heuristic that if $val
looks like a number, it is a number and don't quote it. However then
you run into problems with number data in char columns. And then you
start thinking about your pg_catalog solution again.

> The only solution I can see at present is to look up the type of each
> column name in the pg_catalog tables, and based upon this, quote as
> necessary.

Good luck,
Stuart.
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

This e-mail is confidential and may be read only by the intended recipient.
If you are not the intended recipient, please do not forward, copy or take
any action based on it and, in addition, please delete this email and
inform the sender.
We cannot be sure that this e-mail or its attachments are free from
viruses.  In keeping with good computing practice, please ensure that
you take adequate steps to check for any viruses.  Before replying
or sending any email to us, please consider that the internet is inherently
insecure and is an inappropriate medium for certain kinds of information.
We reserve the right to access and read all e-mails and attachments
entering or leaving our systems.

Registered office: Eurocom House, Ashbourne Road, Derby DE22 4NB Company 
number: 01574696. 


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

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

Re: [GENERAL] quoted identifier behaviour

[Albe Laurenz]

Randall Smith wrote:
>>> Are there plans to make Postgresql's behavior SQL compliant with
regards 
>>> to quoted identifiers?  My specific need is to access tables named
in 
>>> lower case by an uppercase quoted identifier, which is in line with
the 
>>> SQL standard.
>> 
>> You must not change the names of system tables, but you can
>> certainly create upper case views for them.
> 
> That's my current solution.  I imagine this is not very 
> efficient, but I could be wrong.

I think that the performance impact is negligible.
The pain is to maintain the views - the catalog tables can
change in every new release...

Yours,
Laurenz Albe

---(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] database locks

[Albe Laurenz]

Tomasz Rakowski wrote:
> I'm facing frustrating problem with locking in
> postgres server. I have application which do update
> one table few thousands time each minute. From time to
> time call to database is locked and is waiting for something 
> (In pgAdmin3 in 'Server Status' window in 'Lock' tab I
> can see UPDATE statement siting there for hours... and
> usually it is the the only lock! ) At the same time I
> can connect to database from other applications
> (including pgAdmin3) and do any query on that table.

SELECT statements are never blocked by row locks, so the
latter is not surprising.

The first step is probably to find out what holds the lock for
such a long time:

When the hang occurs, do a 'SELECT * FROM pg_locks', that should
show you which backend is waiting for the lock and which backend
holds it. If you have stats_command_string set to on, you will
see the executing statement in pg_stat_activity.

Yours,
Laurenz Albe

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

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

Re: [GENERAL] Automating access grants

[Douglas McNaught]

"Kynn Jones" <[EMAIL PROTECTED]> writes:

> We have an in-house Postgres database that we would like to make
> publicly accessible via a password-less login (user: anonymous).  (We
> already have a web front-end for this database, but we have had a lot
> of requests to allow programmatic access in a way that does not
> require scraping web pages; FWIW, web scraping of this site is already
> disallowed in our TOS.)

Honestly, I would consider writing a web (i.e. SOAP or XML-RPC)
service for this purpose rather than using allowing direct access.
That lets you control what kind of queries can be run.  It's more
work, but much cleaner and more secure.  There are too many ways even
a read-only user can perform a DOS attack.

-Doug

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

Re: [GENERAL] Automating access grants

[Stephen Frost]

* Kynn Jones ([EMAIL PROTECTED]) wrote:
> Now, supposing we have a fresh batch of host registration requests
> that have passed all the filters we may impose on them (i.e. they have
> been "approved" somehow).  How best to automate the process of
> granting access to these host?  I suppose that the script/program in
> charge of this could, in principle, update the pg_hba.conf file, and
> bounce the server with a suitable "kill -HUP", but I'm queasy about
> such unsupervised bouncing of the server.  I could use some words of
> wisdom on this topic.

Call the init.d script with 'reload' or call pg_ctl directly with
'reload'.

> More generally, are we even on the right track here?  Or is the whole
> idea of making our database publicly accessible totally foolish?

In general I'd recommend against making the database publicly available
(as in, allowing psql/etc connections on port 5432).  It depends a great
deal on what you're doing too though.  

> We are specifically trying to avoid, at this initial stage at least,
> any solution that would require creating a proxy server for the sole
> purpose of authenticating and/or validating requests (e.g. ensuring
> that the request include a unique key, etc.)  Our hope is that we may
> be able to craft a  solution using only PostgreSQL's standard security
> facilities that would be adequate for at least the first several
> months of operation, if not much longer.

One big question I have is, is this completely read-only?  Or is it
read-write?  Or what?  If it's something which is just read-only it
seems to me that it'd make more sense to write something in perl to pull
the data out of the database, put it into a portable format (ala csv or
similar) and provide that as a CGI.  You could then protect that using
alot of different ways- apache htaccess style, something in the perl
code which checks a table in the database, etc.  Things are more
complicated if it's actually read/write. :)  If by 'host' you mean 'IP
address', then you really should probably also get your firewall
involved so that connections aren't even allowed to the PG port unless
they're coming from an approved IP (note that, of course, that doesn't
solve all problems..  MITAs, viruses/hackers, naughty upstreams, etc).

> Many thanks for your thoughts and opinions on this!

Good luck..

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Automating access grants

[Kynn Jones]

On 3/15/07, Stephen Frost <[EMAIL PROTECTED]> wrote:

* Kynn Jones ([EMAIL PROTECTED]) wrote:



One big question I have is, is this completely read-only?


Sorry, I should have made this clear: the access we had in mind is
strictly read-only, and only a subset of the tables at that.

kj

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

  http://archives.postgresql.org/

Re: [GENERAL] Automating access grants

[David Fetter]

On Thu, Mar 15, 2007 at 07:38:25AM -0400, Douglas McNaught wrote:
> "Kynn Jones" <[EMAIL PROTECTED]> writes:
> 
> > We have an in-house Postgres database that we would like to make
> > publicly accessible via a password-less login (user: anonymous).
> > (We already have a web front-end for this database, but we have
> > had a lot of requests to allow programmatic access in a way that
> > does not require scraping web pages; FWIW, web scraping of this
> > site is already disallowed in our TOS.)
> 
> Honestly, I would consider writing a web (i.e. SOAP or XML-RPC)
> service for this purpose rather than using allowing direct access.
> That lets you control what kind of queries can be run.  It's more
> work, but much cleaner and more secure.  There are too many ways
> even a read-only user can perform a DOS attack.

Simple example: you allow reads on table foo.  Attacker does:

SELECT * FROM foo f1, foo f2, foo f3, foo f4, foo f5, foo f6, foo f7 ,
foo f8, foo f9, foo f10, foo f11, foo f12, foo f13, foo f14, foo f15,
foo f16, foo f17, foo f18, foo f19, foo f20;

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

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

---(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] Automating access grants

[Stephen Frost]

* Kynn Jones ([EMAIL PROTECTED]) wrote:
> On 3/15/07, Stephen Frost <[EMAIL PROTECTED]> wrote:
> >* Kynn Jones ([EMAIL PROTECTED]) wrote:
> 
> >One big question I have is, is this completely read-only?
> 
> Sorry, I should have made this clear: the access we had in mind is
> strictly read-only, and only a subset of the tables at that.

Then I would definitely encourage setting up a webpage to provide the
information..  There's no need to grant access to the database directly,
and for that matter it'll probably be easier for your *users* to get the
data in a portable format directly rather than having to install
something which can talk the PG protocol.

Enjoy,

Stephen


signature.asc
Description: Digital signature

[GENERAL] PLpgSQL debugger

[Ashish Karalkar]

Hello All,
I have downloaded the PLpgSQl Debugger tarball edb-debugger-20061107-src.tar.gz

after issuing gmake 
it gives me list of errors.

But not getting how to install it.

Can anybody tell me what are the steps to install it.

Thanks in advance

With Regards
Ashish..

Re: [GENERAL] Automating access grants

[Kynn Jones]

I realize that direct access gives an outside user the opportunity to
overload the server.  In fact, I am far less worried about malicious
DOS-type attacks than I am about plain old incompetence, such as having a
buggy script hammer our server with an infinite loop.

BTW, is there a way to configure a PostgreSQL server to abort a query if it
takes longer than a certain amount of time, and/or to limit the number of
queries allowed per host per unit time (say, per hour)?

That's why registration of a host is mandatory for this access.  Any
registered host that violates the TOS gets summarily removed from the
allowed hosts list.  (They get a second chance if they convince us that it
won't happen again.  No third chance.)

I should point out that the information that we will be serving is readily
available from other sources; our service just provides it in a more
convenient form.  The data in question is of academic interest only; it has
little or no economic value.

At any rate, if we were to do this, we would announce it as an "experimental
feature".  If server-overload (whether from malicious attacks, or from inept
usage) becomes an intractable problem, we will just retire the service.

That said, for this experimental feature to work at all, it is necessary to
have a solid way to automate the granting of access to those servers that
request it and meet our conditions.

kj


On 3/15/07, Stephen Frost <[EMAIL PROTECTED]> wrote:


* Kynn Jones ([EMAIL PROTECTED]) wrote:
> On 3/15/07, Stephen Frost <[EMAIL PROTECTED]> wrote:
> >* Kynn Jones ([EMAIL PROTECTED]) wrote:
>
> >One big question I have is, is this completely read-only?
>
> Sorry, I should have made this clear: the access we had in mind is
> strictly read-only, and only a subset of the tables at that.

Then I would definitely encourage setting up a webpage to provide the
information..  There's no need to grant access to the database directly,
and for that matter it'll probably be easier for your *users* to get the
data in a portable format directly rather than having to install
something which can talk the PG protocol.

Enjoy,

Stephen

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF+TtHrzgMPqB3kigRAkRNAJ9JeWKQ6y2yjqpRxuHMOxRAtZgMwgCglkO7
KllW1Aa2hyYuIFG7tSspSZY=
=xqHu
-END PGP SIGNATURE-

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/15/07 01:41, Peter Eisentraut wrote:
> Ron Johnson wrote:
>> On 03/14/07 18:55, Ken Johanson wrote:
>>> In SQL servers in general, or in PG, is there a native field type
>>> to store fractions? Or must one resort to char or separate
>>> numerator/denominator columns?
>> 1/3 repeats ad infinitum, and '1/3' would have to be converted to
>> 0.33 before used in a computation, so:
>>
>> What's your ultimate purpose or goal?
> 
> His goal may be to store and compute rational numbers exactly.  The 
> answer is that there is no data type in PostgreSQL that supports this.
> 
True.  However, with a composite type and stored procedures he could
fulfill that purpose.

CREATE TYPE ty_fraction AS
(
n   SMALLINT,
d   SMALLINT
);

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF+UNvS9HxQb37XmcRAvmBAKDdk8CRsjUe0ziI5TIx5Yd5DIKfPACgvy3M
jfYxlhzONa8hCTrtHy/fd1Y=
=I4q8
-END PGP SIGNATURE-

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

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[Stephane Bortzmeyer]

On Thu, Mar 15, 2007 at 07:41:18AM +0100,
 Peter Eisentraut <[EMAIL PROTECTED]> wrote 
 a message of 22 lines which said:

> His goal may be to store and compute rational numbers exactly.  The
> answer is that there is no data type in PostgreSQL that supports
> this.

But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)

http://www.postgresql.org/docs/8.0/interactive/xtypes.html

---(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] Role & User - Inheritance?

[Alexi Gen]

[GENERAL] Role & User - Inheritance?

A ROLE dba01 has been given the option of SUPERSUSER.
A USER user01 is created and tagged to the above ROLE dba01.
When attempting to create a Tablespace (logged in as user01) it generates 
the following message:
"permission denied to create tablespace (tblspc01). Must be superuser to 
create a tablespace.".
Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt 
user01 also inherit this? If not - what benefit exists in grouping users 
under a Role?


Cheers!

_
Spice up your IM conversations. New, colorful and animated emoticons. Get 
chatting! http://server1.msn.co.in/SP05/emoticons/



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

[GENERAL] Could you send me a copy of pg8.1beta1?Thank you very much!

[Zhang Qian]

Dear sirs,

For some reason,I need a copy of pg8.1beta1,not other ones.But I can't find one 
on the web.Would you please send it to me?
Waiting for your mail.
Thank you for your time.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

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

[GENERAL] passing passords to pgsql/pg_create/pg_dump programmatically

[filippo]

Hello,

I have written a program perl/Tkprogram, based on postgres. For
maintenance reasons in my program I use these commands:

  `dropdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
  `createdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
  `pg_dump -U postgres -h $DATABASE_SERVER $DATABASE_NAME | psql -
U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;

my @psqlOutput = `psql -l -U postgres -h $_`;


my program has a graphic interface but whenever I use these command,
postgres ask for passord in the command line. How can I give these
commands the right passowrd programmatically or how can I   interact
with these to give the passwords by a graphic box?

Thanks and best refards,

Filippo


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

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

Re: [GENERAL] which is more scalable for the database?

[Timasmith]

On Mar 8, 2:26 pm, [EMAIL PROTECTED] (Shane Ambler) wrote:
> Timasmithwrote:
> > Suppose I have a database table with 20 fields which are lookups to a
> > single table.
>
> > configtable(configtable_id, a_field, something_lookup_id,
> > another_lookup_id, ...)
> > lookup(lookup_id, value, description, ...)
>
> > what is going to be faster to map the rows to an Object which needs
> > the 'value' for every field ending in lookup_id
>
> How long is ball of string 'a' compared to ball of string 'b'?
>
> That will depend on a lot on how large the lookup table will be (100
> rows or 10 million rows?)

  100-500,000 rows

and on how long your descriptions are.

  10-60, perhaps averaging around 20 characters

Will
> you want all 20 descriptions for each query you run?

  Yes, it is the price I pay with Hibernate for vastly simplifying
data access

If your
> descriptions are 200 characters each then maybe you can reduce some data
> transfer by caching these at the client instead of transferring them
> each time you retrieve a result. But then how will you keep your local
> cache up to date?

  The data I would cache would change infrequently in the production
environment and there would be
  no expectation it would be 'immediate' on change.
  I would provide services to flush on demand, or on save (of
reference data), and/or through a scheduler etc.

>
> I would suggest the view for a couple of reasons - first it will
> simplify your queries as you won't need all the join details in every
> query you run. Second the select for the query can be parsed and stored
> whereas separate selects will be parsed each time.

  If I was caching on the client, I would select all data only once
and there would be no queries, it would only be
  select * from sometable
  and the client (actually server side bean), would populate the
object with the missing lookups.

>
> A lot of this will depend on what you are doing and what you are using
> to do it. If you are using php then you have less options for caching
> than say a client program written in c. php may run pretty fast but it
> won't outrun a compiled c program.

   Java hashtable.


>
>
>
>
>
> > a) View
>
> > select c.configtable_id, l1.value as something_lookup, l2.value as
> > another_lookup
> > from configtable c,
> >lookup l1,
> >lookup l2
> > where c.something_lookup_id = l1.lookup_id
> > and c.another_lookup_id = l2.lookup_id
>
> > foreach row
> >map values to object
> > end
>
> > b) Cache all lookup values and populate
>
> > select c.* from configtable
>
> > foreach row
> >map values to object
> >if lookup_id
> >find value from hashtable and map value to object
> >endif
> > end
>
> > It seems that the latter *might* be better to scale outward better,
> > as
> > you could add application servers to do the caching/mapping and you
> > only select from a single table?
>
> Maybe but then postgresql may do the lookups quicker than what you have
> available at the client end.

Right, but could it keep up, how much extra effort is it to do the
indexed lookups (almost all in memory), for the client.

>
> The only way you will really know is to load up some sample data and
> test each method yourself.

Its very hard to simulate though due to production hardware having 16
cpus, Gigs of memory, huge databases, hundereds of concurrent users -
I just dont have access to that kind of environment.

>
> How far are you expecting to scale? The simplest method may just work
> fast enough that all the extra caching and setup/maintenance of this
> will outweigh any benefit.

I would like to scale to 10,000s of power users on a complex OLTP
system.  Adding servers to expand out is generally feasible, but one
can only scale upward so far before reaching a hardware peak.

>
> --
>
> Shane Ambler
> [EMAIL PROTECTED]
>
> Get Sheeky @http://Sheeky.Biz
>
> ---(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- Hide quoted text -
>
> - Show quoted text -



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

[GENERAL] SQL Question - Group By and % results per row

[Mike]

I have a SQL statement that uses group-by to generate reports as such:


GroupFieldClicks
---
Page 1  50
Page 2  20
Page 3  30

Now, as an added field, I also want it to tell me what percent of
total clicks each row represents.

GroupFieldClicks Percent
---   
Page 1  50   50%
Page 2  20   20%
Page 3  30   30%

How do I get access to the total of all clicks on per row basis so I
can divide it? The only solution that comes to my mind is create a
subquery that does a (select count(*) from... where... ) of the
original grouped by sql statement.

Thank you for your help in advance,
Regards,
Mike


---(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] query advice

[Nathanial Mayweather]

I'm stuck on a query, I'm hoping someone can point me to the obvious

I need to limit the results of a query based on 2 criteria:
1- total rows grouped by column_a
2- total rows all

given:
books:   id , author , subject , title

i need to

SELECT * FROM books WHERE subject= 'database'
Limit to : no more than 10 records per 'author'
Limit to : 100 records total

I'm really rusty on my sql this week.

Any pointers would be appreciated.

_
Get a FREE Web site, company branded e-mail and more from Microsoft Office 
Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/



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

[GENERAL] plperl question: how to specifiy perl version?

[Dave Thompson]

Hi,

I setup a plperl handler in Postgres 8.1 running under Solaris. The 
machine has multiple versions of Perl installed, and plperl is not using 
the correct Perl binary. How do I set which Perl binary plperl should use?


Thanks
-Dave

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

  http://archives.postgresql.org/

Re: [GENERAL] Is This A Set Based Solution?

[Stefan Berglund]

On Mon, 12 Mar 2007 10:41:21 -0400, [EMAIL PROTECTED] (Tom Lane) wrote:
 in <[EMAIL PROTECTED]> 

>Stefan Berglund <[EMAIL PROTECTED]> writes:
>> On Sat, 10 Mar 2007 00:37:08 -0500, [EMAIL PROTECTED] (Tom Lane) wrote:
>>> It looks pretty ugly to me too, but you haven't explained your problem
>>> clearly enough for anyone to be able to recommend a better solution path.
>>> Why do you feel you need to do this?  What is the context?
>
>> What I want to do is to create a function that takes a comma separated
>> string of numbers and produces a table (where each row is one of those
>> numbers) that can be joined to other tables as in the example first
>> provided.
>
>That was what you said before.  The question is why you need to do that.
>It strikes me that having such a requirement is a symptom of poor data
>representation choices.  Perhaps an array would be better, or maybe you
>ought to refactor your table layout altogether.  But, as I said, you
>haven't provided any info that would let someone give advice at that
>level.

Perhaps it is a case of poor data representation choices and that is
exactly why I posted originally - because I wasn't sure if that was the
best way of doing what I want to do:

I have an app where the user makes multiple selections from a list.  I
can either construct a huge WHERE clause such as SELECT blah blah FROM
foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
alternatively pass the string of IDs ('53016,27,292,512') to a table
returning function which TABLE is then JOINed with the table I wish to
query instead of using the unwieldy WHERE clause.  The latter strikes me
as a far more scalable method since it eliminates having to use dynamic
SQL to construct the ridiculously long WHERE clause which will no doubt
ultimately bump up against parser length restrictions or some such.

I didn't find any examples that showed JOINing an array with a table.
How do other developers solve this basic problem and why does my
approach seem so foreign?

SELECT blah blah
FROM
  fn_Split_List('53016,27,292,512') SL INNER JOIN
  foo T ON SL.N=T.ID;

or

SELECT blah blah
FROM foo
WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512)
---
Stefan Berglund

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

[GENERAL] pg_dumpall and version confusion

[Alan J Batsford]

I inherited an existing system with no documents on how it was originally
configuerd, so I'll provide as much as I can. OS is CentOS 4.3 Linux
Distribution.

When I search my system for anything postgres I find a boatload of 8.0.4
source, and a boatload of 7.4.8 documentation.

postgres --version yields 8.1.4 (thats no typo)
psql --version yields 7.4.8
pg_dump --version yields 7.4.8
pg_dumpall --version yields 7.4.8

When doing 'which' on all of the above binaries I get /usr/bin/ for all
4.

The original problem was recieving 'pg_dumpall: query failed: ERROR:
column "datpath" does not exist' error when trying to run pg_dumpall. I
learned that this is expected error if pg_dumpall is too new for the
database, but it claims to be 7.4.8. I hate to ask a terribly vague
question but how do I find out what version I'm running when postgres and
psql don't agree? There's no trace of 8.1.4 files on the system so I'm
without a lead as to how I have a binary for it.

Thanks for any assistance you can provide.


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

[GENERAL] java.net.SocketException: Connection reset

[Gloria Rodriguez]

 Hello!! 
I am working with hermes 2 postgresql and I obtain this mistake I am thinking 
this mistake is because it not conect very well. Can someone help me?? thanks 
very mach   2007-03-15 11:02:51 [Thread-6]   hk.hku.cecid.piazza.commons.net.ConnectionException: Unable to connect to 
incoming mail server by javax.mail.MessagingException: Connect failed;  nested 
exception is: java.net.SocketException: Connection reset at 
hk.hku.cecid.piazza.commons.net.MailReceiver.connect(MailReceiver.java:66) at 
hk.hku.cecid.ebms.spa.task.MailCollector.getTaskList(MailCollector.java:49) at 
hk.hku.cecid.piazza.commons.module.ActiveTaskModule.execute(ActiveTaskModule.java:137)
 at hk.hku.cecid.piazza.commons.module.ActiveModule.run(ActiveModule.java:205) 
at java.lang.Thread.run(Thread.java:534)Caused by: 
javax.mail.MessagingException: Connect failed;  nested exception is: 
java.net.SocketException: Connection reset at 
com.sun.mail.pop3.POP3Store.protocolConnect(POP3Store.java:120) at 
javax.mail.Service.connect(Service.java:255) at 
javax.mail.Service.connect(Service.java:134) at 
javax.mail.Service.connect(Service.java:86) at 
hk.hku.cecid.piazza.commons.net.MailReceiver.connect(MailReceiver.java:63) ... 
4 more
_
Consigue el nuevo Windows Live Messenger
http://get.live.com/messenger/overview

[GENERAL] Windows psql.exe password from pgpass.conf

[laurentpointca]

Postgres 8.1 on a windows machine.

I followed instructions and went to APPDIR to setup the file
pgpass.conf:
*   *   myuser   mypass

I also tried: *   *   databasenamemyuser   mypass

I also tried: *:*:myuser:mypass

When i start psql -U myuser, it always requests that I type the
password. What could be wrong? I've found very little information
regarding the pgpass.conf file...

thank you


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

Re: [GENERAL] How to write a function that manipulates a set of results

[Stefan Berglund]

On Wed, 14 Mar 2007 18:50:27 +, [EMAIL PROTECTED] (Ashley
Moran) wrote:
 in <[EMAIL PROTECTED]> 

>It's more complicated than that.  What we need to do is something  
>along the lines of:
>
>results = SELECT * FROM foo();
>DELETE FROM results WHERE (some condition involving results);
>some_value = SELECT value FROM results WHERE (etc);
>
>and so on...
>
>All of which is easy with table variable, but I can't see how to  
>translate it to PL/pgsql.  Is there any way to manipulate result sets  
>in a set-based manner like this?

A table returning function or SRF can be used in joins with other tables
or subqueries.  In fact, you can use it in either of two formats:

If the SRF returns a native data type then you can use just the function
name.  Consider the function foo() which returns INTEGER.

CREATE OR REPLACE FUNCTION foo () RETURNS SETOF INTEGER AS

SELECT *
FROM
  foo() F INNER JOIN
  some_table T ON F=T.id;

If the SRF returns a composite type then you can use the function name
qualified by any of the members of the list of types.

SELECT *
FROM
  foo() F INNER JOIN
  some_table T ON F.num=T.id;

---
Stefan Berglund

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

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

[GENERAL] PITR and WAL archiving

[Adam Groves]

I have been ramming my head against the wall for the past two
evenings, trying to get PITR working and it's gotten to the point
where I need to ask someone for help.

I'm basically trying to follow the instructions in the manual in
chapter 23.3.

Here's how I'm starting out:
===

Contents of 'data':

PG_VERSION
base
global
pg_clog
pg_hba.conf
pg_ident.conf
pg_multixact
pg_subtrans
pg_tblspc
pg_twophase
pg_xlog
postgresql.conf
postmaster.opts


data/pg_xlog/archive_status is empty.

I have the following line in postgresql.conf:

archive_command = 'cp -i %p /mnt/server/archive/wal/%f < /dev/null'

/mnt/server/archive/wal is empty.

Here's my procedure:
===

1. pg_ctl start -l logfile
2. SELECT pg_start_backup('label');
3. tar -zcf /mnt/server/archive/file/backup.tar.gz . (the contents of
'data')
4. SELECT pg_stop_backup();
5. pg_ctl stop
6. I now delete the contents of 'data' (making a copy before hand)
7. In 'data': tar xzvf /mnt/server/archive/file/backup.tar.gz
8. echo \"restore_command = 'cp /mnt/server/archive/wal/%f %p'\" >
recovery.conf
9. pg_ctl start -l logfile

And here are the logs:


LOG:  database system was shut down at 2007-03-13 23:07:33 CET
LOG:  checkpoint record is at 0/1E20
LOG:  redo record is at 0/1E20; undo record is at 0/0; shutdown
TRUE
LOG:  next transaction ID: 0/161203; next OID: 17156
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down at 2007-03-13 23:09:21 CET
LOG:  checkpoint record is at 0/1E68
LOG:  redo record is at 0/1E68; undo record is at 0/0; shutdown
TRUE
LOG:  next transaction ID: 0/161203; next OID: 17156
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
LOG:  database system was interrupted at 2007-03-13 23:11:21 CET
LOG:  starting archive recovery
LOG:  restore_command = "cp /mnt/server/archive/wal/%f %p"
cp: /mnt/server/archive/wal/0001.history: No such file or
directory
LOG:  restored log file "0001001E.00B0.backup"
from archive
LOG:  restored log file "0001001E" from archive
LOG:  checkpoint record is at 0/1EB0
LOG:  redo record is at 0/1EB0; undo record is at 0/0; shutdown
FALSE
LOG:  next transaction ID: 0/161205; next OID: 17156
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  automatic recovery in progress
LOG:  redo starts at 0/1EF8
cp: /mnt/server/archive/wal/0001001F: No such file or
directory
LOG:  could not open file "pg_xlog/0001001F" (log file
0, segment 31): No such file or directory
LOG:  redo done at 0/1EF8
LOG:  restored log file "0001001E" from archive
LOG:  archive recovery complete
LOG:  database system is ready
LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down at 2007-03-13 23:17:32 CET
LOG:  checkpoint record is at 0/1F68
LOG:  redo record is at 0/1F68; undo record is at 0/0; shutdown
TRUE
LOG:  next transaction ID: 0/161207; next OID: 17156
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
overwrite /mnt/server/archive/wal/0001001E? (y/n [n])
not overwritten
LOG:  archive command "cp -i pg_xlog/0001001E /mnt/
server/archive/wal/0001001E < /dev/null" failed:
return code 256

Where oh where might I be going wrong? I'd be grateful for any
suggestions.

Regards

Adam


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

Re: [GENERAL] Windows psql.exe password from pgpass.conf

[Laurent]

Apparently I forgot one combination, which was the correct one. For
reference:

localhost:*:databasename:username:password



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

   http://archives.postgresql.org/

[GENERAL] PgSql on Vista?

[Arkan]

Hi all,

have anybody installed PgSQL on Windows Vista? I tried yesterday but I
fail... on XP and linux i've installed pgsql much times but on
vista...  nothing to do! Version in 8.2.3 with the installer.

If I install with my user account (with administrative rights), the
installlation fails on setting file permission, about at 3/4 of the
process.

If I install with Administrator account, the installer fail when
starting the service..

any ideas?


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

   http://archives.postgresql.org/

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Alvaro Herrera]

Joshua D. Drake escribió:
> Tom Lane wrote:
> > "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> >> Erik Jones wrote:
> >>> I really hope you meant upgrades to 8.2.x.  And, no, it's not worth
> >>> waiting.  Upgrade at the soonest available opportunity, expecially the
> >>> 7.4.x servers.
> > 
> >> I don't really agree with this. If he is running 7.4.16 there very well
> >> may be zero compelling reason for him to upgrade.
> > 
> > Really?  There are any number of anecdotal reports of massive speed
> > improvements between 7.x and various 8.x versions.  Not to mention a
> > few feature improvements.
> 
> There is zero question that 8.2 is faster than 7.4 *but* if 7.4 isn't
> slow for them... Note, that I meant no reason for him to upgrade 7.4
> *right now*. He could wait for 8.3. (I think he should get off 7.4 in
> general)

He could wait for 8.4 as well, as it will be probably faster and have
more features than 8.3.  Following your reasoning, one could wait
essentially forever.

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

---(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] Could you send me a copy of pg8.1beta1?Thank you very much!

[Alvaro Herrera]

Zhang Qian wrote:
> Dear sirs,
> 
> For some reason,I need a copy of pg8.1beta1,not other ones.But I can't find 
> one on the web.Would you please send it to me?
> Waiting for your mail.

You can obtain it via CVS, using the tag REL8_1_0BETA1.

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

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

Re: [GENERAL] passing passords to pgsql/pg_create/pg_dump programmatically

[Alvaro Herrera]

filippo wrote:
> Hello,
> 
> I have written a program perl/Tkprogram, based on postgres. For
> maintenance reasons in my program I use these commands:
> 
>   `dropdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>   `createdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>   `pg_dump -U postgres -h $DATABASE_SERVER $DATABASE_NAME | psql -
> U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
> 
> my @psqlOutput = `psql -l -U postgres -h $_`;
> 
> 
> my program has a graphic interface but whenever I use these command,
> postgres ask for passord in the command line. How can I give these
> commands the right passowrd programmatically or how can I   interact
> with these to give the passwords by a graphic box?

You can use the PGPASSWORD environment variable, or the ~/.pgpass file
(which can actually be somewhere else by using the PGPASSFILE
environment variable, allowing you to store passwords in /tmp or
something like that, to avoid destroying a .pgpass file the user may
have).

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

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

Re: [GENERAL] Is This A Set Based Solution?

[George Weaver]

Stefan Berglund wrote:


foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
alternatively pass the string of IDs ('53016,27,292,512') to a table
returning function which TABLE is then JOINed with the table I wish to


Stefan,

The user selections will be in some sort of list.  Could you not use WHERE 
ID IN (the list)?


Regards,
George 



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

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

Re: [GENERAL] Role & User - Inheritance

[David Legault]

Unfortunately,

Everything that is a permission (CREATEROLE, etc) when creating a role
cannot be inherited. Only the GRANT stuff is inherited and I think only when
the WITH .. is given on the GRANT.

I may be wrong on the last part though.

David

On 3/15/07, Alexi Gen <[EMAIL PROTECTED]> wrote:


[GENERAL] Role & User - Inheritance?

A ROLE dba01 has been given the option of SUPERSUSER.
A USER user01 is created and tagged to the above ROLE dba01.
When attempting to create a Tablespace (logged in as user01) it generates
the following message:
"permission denied to create tablespace (tblspc01). Must be superuser to
create a tablespace.".
Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt
user01 also inherit this? If not - what benefit exists in grouping users
under a Role?

Cheers!

_
Spice up your IM conversations. New, colorful and animated emoticons. Get
chatting! http://server1.msn.co.in/SP05/emoticons/


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

Re: [GENERAL] Automating access grants

[Douglas McNaught]

I would still recommend making it available as a web service rather
than giving direct access; besides the security/load issues, it lets
you change the representation of the data without necessarily
affecting customers (of course, you can do this also at the DB level
with views).  The web service is a little extra work, but it lets you
re-use the data access layer that already exists in your web
application, so it's just a matter of putting a SOAP layer on top of
that.

My general philosophy is to only allow trusted apps/users to hit the
database directly.

-Doug

---(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] PgSql on Vista?

[A. Kretschmer]

am  Wed, dem 14.03.2007, um  1:24:01 -0700 mailte Arkan folgendes:
> Hi all,
> 
> have anybody installed PgSQL on Windows Vista? I tried yesterday but I
> fail... on XP and linux i've installed pgsql much times but on
> vista...  nothing to do! Version in 8.2.3 with the installer.
> 
> If I install with my user account (with administrative rights), the
> installlation fails on setting file permission, about at 3/4 of the
> process.

Someone write this about vista:

1. Disable UAC
2. EnableAdministrator account
3. Login as administrator
4. Install postgres the normal way
5. Enure, that the pgadmin can be executed by everybode
6. Logout and login as normal user
7. enable UAC


Don't ask me, i'm not familar with windoze...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   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/

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[John D. Burger]

Stephane Bortzmeyer wrote:


But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)


It's a little tricky to get good performance for all the operations:

The addition and subtraction operations are complex. They will  
require approximately two gcd operations, 3 divisions, 3  
multiplications and an addition on the underlying integer type.   
The multiplication and division operations require two gcd  
operations, two multiplications, and four divisions.  The  
comparison operations require two gcd operations, two  
multiplications, four divisions and a comparison in the worst  
case.  On the assumption that IntType comparisons are the cheapest  
of these operations (and that comparisons agains zero may be  
cheaper still), these operations have a number of special case  
optimisations to reduce the overhead where possible.  In  
particular, equality and inequality tests are only as expensive as  
two of the equivalent tests on the underlying integer type.


(From the Booost rational package - http://www.boost.org/libs/ 
rational/rational.html)


I'd try to link to an existing library that provides rationals, or  
model my code closely after one.


- John D. Burger
  MITRE



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

Re: [GENERAL] Exception handling in plperl

[Jasbinder Singh Bali]

Hi,
Actually, if I rephrase my requirement, I need to catch an exception at any
point ,where ever it is raised, in the perl code.
E.g during an insert, there is a foreign key contraint violation, then i
need to catch this specific error and do something with it.
Hope i make some sense here.

Thanks,
Jas


On 3/14/07, Michael Fuhr <[EMAIL PROTECTED]> wrote:


On Tue, Mar 13, 2007 at 11:23:03PM -0400, Jasbinder Singh Bali wrote:
> I have a stored procedure written in plperl.
> This procedure has series of inserts at various levels. A few inserts on
> certain tables invoke triggers
> that launch tools outside the domain of the database.
>
> How can I make everything as one single transaction and simply roll back
> everything whenever an exception occurs.

Statements are always executed in a transaction; if you're not
inside an explicit transaction block then statements will be
implicitly wrapped in a transaction for you.  If the outermost
statement is "SELECT function_that_does_inserts()" then everything
that happens inside that function is part of the same transaction,
and if any of the function's statements fail then the entire
transaction will fail unless you trap the error.  In plperlu you
can trap errors with "eval"; see the Perl documentation for more
information.

Are you wanting to trap errors so you can roll back actions that
happened outside the database?  If so then you could use eval to
handle failures, then do whatever cleanup needs to be done (and can
be done) outside the database, then use elog to raise an error and
make the current transaction fail.  However, if this is what you're
trying to do then understand that actions outside the database
aren't under the database's transaction control and might not be
able to be rolled back.

If I've misunderstood what you're asking then please provide more
information about what you're trying to do.

--
Michael Fuhr

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

[Scott Marlowe]

On Wed, 2007-03-14 at 17:33, Randall Smith wrote:
> Scott Marlowe wrote:
> > This whole discussion is reminding me of one of my personal mantras, and
> > that is that relying on "artifacts" of behaviour is generally a bad
> > idea.
> > 
> > For instance, many databases accept != for not equal, but the sql
> > standard quite clearly says it's <>.
> > 
> > If you're relying on case folding meaning that you don't have to
> > consistently use the same capitalization when referring to variables,
> > table names, people, or anything else, you're asking for trouble down
> > the line, and for little or no real gain today.
> > 
> > I know that a lot of times we are stuck with some commercial package
> > that we can't do anything to fix, so I'm not aiming this comment at the
> > average dba, but at the developer.
> > 
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> > 
> >http://archives.postgresql.org/
> > 
> 
> Yea, this is a commercial package, but it's actually doing it right. 
> Since it doesn't know how a user will name a table or column, it always 
> calls them as quoted strings in upper case which is standards compliant, 
> but doesn't work with PG.  So if a user names a table 55 and mine, it 
> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it 
> right to me.

No they did it wrong.  I can make a table in oracle like this:

create table "Mine" ("MyName" varchar(200), "id" int)

and it won't work with your application, because it's broken.  Assuming
that everything is in upper case is just as bad as assuming it's in
lower case.  You can't assume the case because the user could be quoting
the name when he creates the table.

The rule, for Oracle, PostgreSQL, etc is that if you use quotes to name
it, you use quotes to access it.

With that philosophy, Oracle and PostgreSQL work just fine.

If you ignore that simple rule, you will get yourself into a corner with
either database.

---(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] Lifecycle of PostgreSQL releases

[Joshua D. Drake]

>> There is zero question that 8.2 is faster than 7.4 *but* if 7.4 isn't
>> slow for them... Note, that I meant no reason for him to upgrade 7.4
>> *right now*. He could wait for 8.3. (I think he should get off 7.4 in
>> general)
> 
> He could wait for 8.4 as well, as it will be probably faster and have
> more features than 8.3.  Following your reasoning, one could wait
> essentially forever.

You have got to be kidding. There is quite a bit of difference between 3
months and 17 months. From the persons email, he obviously has an array
of production machines. This isn't hack fest 2000, just load up whatever.

My professional opinion, and frankly the opinion we are telling our
customers (except those that will explicitly benefit from something in
8.2) is to wait for 8.3.

Sincerely,

Joshua D. Drake


-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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] Lifecycle of PostgreSQL releases

[Scott Marlowe]

On Thu, 2007-03-15 at 00:10, Tom Lane wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> > Erik Jones wrote:
> >> I really hope you meant upgrades to 8.2.x.  And, no, it's not worth
> >> waiting.  Upgrade at the soonest available opportunity, expecially the
> >> 7.4.x servers.
> 
> > I don't really agree with this. If he is running 7.4.16 there very well
> > may be zero compelling reason for him to upgrade.
> 
> Really?  There are any number of anecdotal reports of massive speed
> improvements between 7.x and various 8.x versions.  Not to mention a
> few feature improvements.
> 
> Now it could well be that none of those affect the OP but 8.3 will have
> the particular shade of magic pixie dust his queries need.  But it seems
> at least as likely that 8.3 will be no significant improvement over 8.2
> for him.  Without any real details about his app, you can't call it
> either way.
> 
> I tend to agree with Erik that if you have a window now to upgrade off
> of 7.x, you should do it, rather than waiting for the next release.

I love pgsql as much as anybody, and I generally don't run the latest
release in production until it's about 6 months out of the gate. 
Between testing and approval and upgrading all the systems that aren't
production, by the time a new version sees production it pretty much has
to be 4 to 6 months old.

I also tend to run every other version.  I've run 7.2, then 7.4, then
8.1.  I've tested and played with 8.2 and speed wise, it wasn't a
compelling enough upgrade to start the very long process of replacing
8.1 with.  By the time 8.3 comes out, I'll be about ready to start
evaluating our next version of pgsql.

I have to say the update to 8.1 was very compelling, as the query
optimizer was much better, and the ability to use indexes on date
columns with references like now() - interval '5 days' was a huge thing
for my reporting apps.

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

Re: [GENERAL] pg_dumpall and version confusion

[Tom Lane]

Alan J Batsford <[EMAIL PROTECTED]> writes:
> When I search my system for anything postgres I find a boatload of 8.0.4
> source, and a boatload of 7.4.8 documentation.

"rpm -qa | grep postgres" would probably prove enlightening.  It sounds
like you have postgresql-server at 8.1.4 and the other subpackages at
older releases, which is pretty odd; the RPMs should have had
cross-requires that would prevent that.

> The original problem was recieving 'pg_dumpall: query failed: ERROR:
> column "datpath" does not exist' error when trying to run pg_dumpall. I
> learned that this is expected error if pg_dumpall is too new for the
> database, but it claims to be 7.4.8.

No, usually you have version skew problems if pg_dumpall is too *old*
for the server, which is exactly the case here.  We try to make pg_dump
cope with prior server releases, but since we lack a time machine we
can't go back and teach old versions about subsequent server changes...

regards, tom lane

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

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Joshua D. Drake]

> I also tend to run every other version.  I've run 7.2, then 7.4, then
> 8.1.  I've tested and played with 8.2 and speed wise, it wasn't a
> compelling enough upgrade to start the very long process of replacing
> 8.1 with.  By the time 8.3 comes out, I'll be about ready to start
> evaluating our next version of pgsql.
> 
> I have to say the update to 8.1 was very compelling, as the query
> optimizer was much better, and the ability to use indexes on date
> columns with references like now() - interval '5 days' was a huge thing
> for my reporting apps.

Yeah 8.1 was a great milestone, being able to allocate more
shared_buffers has been a great boon for many of our customers that are
running 4+ GIG of ram.

Joshua D. Drake




-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

Re: [GENERAL] pg_dumpall and version confusion

[Tony Caduto]

Tom Lane wrote:

We try to make pg_dump
cope with prior server releases, but since we lack a time machine we
can't go back and teach old versions about subsequent server changes...
  

Tom,
How come version 8.2 of pg_dump uses GRANT ON SEQUENCE when dumping a 
8.1 or older database?
Was it just a oversight?  Seems it should be a simple matter to add some 
logic that says IF version >= 8.2 THEN use grant on seq  else don't use it.


Bruce told me just the opposite of what you said in the above message.

Thanks,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(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] plperl question: how to specifiy perl version?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> I setup a plperl handler in Postgres 8.1 running under Solaris. The 
> machine has multiple versions of Perl installed, and plperl is not using 
> the correct Perl binary. How do I set which Perl binary plperl should use?

You need to set the PERL environment variable before running ./configure. 
Not sure when this was added - if it doesn't work for your version 
of Postgres, you can also fiddle with the PATH.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200703151208
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFF+W/KvJuQZxSWSsgRAzQRAKD19GcOc+50Y15lruQ+o/iVln8wMgCeJIjv
azShu4leT7aqEhvwfqgle9Q=
=6fjs
-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] DST problem on Windows Server

[MargaretGillon]

>Date: Wed, 14 Mar 2007 20:39:17 +0100
>From: Magnus Hagander <[EMAIL PROTECTED]>
>To: George Weaver <[EMAIL PROTECTED]>
>Cc: pgsql-general 
>Subject: Re: DST problem on Windows Server
>Message-ID: <[EMAIL PROTECTED]>
>George Weaver wrote:
> A client has PostgreSQL 8.2.1 installed on a Windows 2000 server.
> 
> The time on the server is correct but PostgreSQL is returning a time 1
> hour in advance.  In other words, it the server time is 1:00 pm,
> PostgreSQL is returning 2:00 pm.  Both the PostgreSQL server and Windows
> server have been rebooted, but the problem remains.
> 
> postgresql.conf is using the default timezone setting:
> 
> #timezone = unknown# actually, defaults to TZ
>  # environment setting
> 
>  and the time zone setting on the Windows server is (GMT - 6) Cental
> Time (US and Canada), which is the correct location.
> 
> Any suggestions how to resolve this, or why its happening?

>It could be that it's mis-detecting the OS timezone - is the OS even
>updated for the new DST rules? I thought they didn't release such
>patches for win2k unless you paid a bunch extra for it?
>Anyway, try hardcoding the timezone value in postgresql.conf to whatever
>zone you're in, and see if that solves it.

>//Magnus

George,

We had to run a special utility from Windows on all our Win2k clients and 
servers to reset the time zone. The file is called TZEDIT.exe date 
12/2/1999 and I think our network admin downloaded it from Microsoft. It 
lets you reset the dates that the DST takes place.

I will email instructions our admin put together in a separate email to 
you so the file doesn't go to the list. 

I saw what happened on my machine if this utility wasn't used. I could set 
my clock to the correct time but any files I used on another server showed 
the time as being a hour off, even though the problem was on my computer. 
When mine was correct the time showing from other servers was correct.

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

This e-mail message and any attachment(s) are for the sole use of the 
intended recipient(s) and may contain proprietary and/or confidential 
information which may be privileged or otherwise protected from 
disclosure.  Any unauthorized review, use, disclosure or distribution is 
prohibited.  If you are not the intended recipient(s), please contact the 
sender by reply email and destroy the original message and any copies of 
the message as well as any attachment(s) to the original message.

Re: [GENERAL] pg_dumpall and version confusion

[Alvaro Herrera]

Tony Caduto wrote:
> Tom Lane wrote:
> >We try to make pg_dump
> >cope with prior server releases, but since we lack a time machine we
> >can't go back and teach old versions about subsequent server changes...
> >  
> Tom,
> How come version 8.2 of pg_dump uses GRANT ON SEQUENCE when dumping a 
> 8.1 or older database?

What's wrong with that?  8.2 will understand the GRANT ON SEQUENCE
without a problem.

> Bruce told me just the opposite of what you said in the above message.

So Bruce told you that hackers do have a time machine?  If so, he hasn't
let me know about it, and I'm very interested.  I'll ask him.

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

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

Re: [GENERAL] pg_dumpall and version confusion

[Tony Caduto]

Alvaro Herrera wrote:


What's wrong with that?  8.2 will understand the GRANT ON SEQUENCE
without a problem.

  

Yes, but 8.1 and earlier wont!

If you dump a 8.1 database (with 8.2 pg_dump) and then use that dump to 
restore to another 8.1  server, any permissions on sequences will fail 
to restore because the 8.2 pg_dump can't handle NOT using GRANT ON 
SEQUENCE on the lower version of the server.
Would it really be that big of a deal to add some logic to 8.2 pg_dump 
to say: Hey I am not dumping a 8.2 server, so don't use GRANT ON SEQUENCE?


most Admin tools ship with the latest version of pg_dump and restore, so 
If I attempt to restore that dump via pgAdmin III (or other tools) which 
is using 8.2 versions of dump and restore it will fail on a 8.1 server.


Do you see the point I am trying to make?   Should admin tool vendors 
start shipping every version of pg_dump now?


In a earlier message about this same thing Bruce said (I am quoting from 
memory so it's not exact) that we don't try and make pg_dump backwards 
compatible and to use the dump that came with whatever version you are 
dumping from"  So to me that seemed opposite of what Tom said.


If I misquoted anyone I  apologize  in advance.

Thanks,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

  http://archives.postgresql.org/

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

[Stephan Szabo]

On Wed, 14 Mar 2007, Randall Smith wrote:

> Stephan Szabo wrote:
> > On Wed, 14 Mar 2007, Randall Smith wrote:
> >
> >> Scott Marlowe wrote:
> >>> This whole discussion is reminding me of one of my personal mantras, and
> >>> that is that relying on "artifacts" of behaviour is generally a bad
> >>> idea.
> >>>
> >>> For instance, many databases accept != for not equal, but the sql
> >>> standard quite clearly says it's <>.
> >>>
> >>> If you're relying on case folding meaning that you don't have to
> >>> consistently use the same capitalization when referring to variables,
> >>> table names, people, or anything else, you're asking for trouble down
> >>> the line, and for little or no real gain today.
> >>>
> >>> I know that a lot of times we are stuck with some commercial package
> >>> that we can't do anything to fix, so I'm not aiming this comment at the
> >>> average dba, but at the developer.
> >> Yea, this is a commercial package, but it's actually doing it right.
> >> Since it doesn't know how a user will name a table or column, it always
> >> calls them as quoted strings in upper case which is standards compliant,
> >> but doesn't work with PG.  So if a user names a table 55 and mine, it
> >> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
> >> right to me.
> >
> > Maybe, but the 55 and mine example may or may not actually work. 55 and
> > mine isn't a valid regular identifier. "55 and mine" would be a valid
> > identifier, but that's not the same identifier as "55 AND MINE".
> >
> Your right. Its not a correct example.  I think the point is clear, though.

Well, I was arguing about whether the app was doing it right. Assuming
that you can uppercase and put quotes around an arbitrary table name is
wrong too, because that's only valid for regular identifiers in SQL, so I
was wondering if it had support for things that were created as quoted
identifiers (which you might be able to use as a temporary workaround).
This isn't an argument against putting spec compliant behavior into
PostgreSQL, just more of a point that getting this right through the whole
system from app to db can be somewhat tricky even in the best case.

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

Re: [GENERAL] pg_dumpall and version confusion

[Dave Page]

Tony Caduto wrote:

Alvaro Herrera wrote:


What's wrong with that?  8.2 will understand the GRANT ON SEQUENCE
without a problem.

  

Yes, but 8.1 and earlier wont!

If you dump a 8.1 database (with 8.2 pg_dump) and then use that dump to 
restore to another 8.1  server, any permissions on sequences will fail 
to restore because the 8.2 pg_dump can't handle NOT using GRANT ON 
SEQUENCE on the lower version of the server.
Would it really be that big of a deal to add some logic to 8.2 pg_dump 
to say: Hey I am not dumping a 8.2 server, so don't use GRANT ON SEQUENCE?


most Admin tools ship with the latest version of pg_dump and restore, so 
If I attempt to restore that dump via pgAdmin III (or other tools) which 
is using 8.2 versions of dump and restore it will fail on a 8.1 server.


Do you see the point I am trying to make?   Should admin tool vendors 
start shipping every version of pg_dump now?


This is a problem I've been thinking about on and off recently - and I 
am starting to come to the conclusion that shipping each version of the 
utilities is the only way things are likely to work unless someone puts 
some really significant effort into adding backwards compatibility modes 
to pg_dump (which I imagine is likely to meet resistance if offered as a 
patch anyway).


That will be a real PITA though - we already have a bunch of code in 
pgAdmin to find just the current versions of PostgreSQL's and EDB's 
pg_dump et al. (yes, they are different), nevermind having to do it 
right back to 7.3 which is the earliest version we currently support.


The other issue will be having to ship multiple copies of libpq and 
supporting libraries for each version :-(


For the moment though, our SVN trunk code will allow you to override the 
paths to the PostgreSQL and EnterpriseDB utilities, so you can point 
pgAdmin at the appropriate verion for your server.


Regards, Dave

---(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] Lifecycle of PostgreSQL releases

[Joshua D. Drake]

Tom Lane wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>> I can't really argue for 8.2 versus 8.3, but I can argue that as 8.3 is
>> literally around the corner, it may make sense to wait.
> 
> Today is the ides of March ... while the most optimistic estimate I've
> heard for 8.3 release is high summer.  Maybe that's just around the
> corner by some time scales, but I strongly counsel the OP not to try to
> hold his breath till then.

Well I guess that comes back to user requirements. Some general non
tested statistics:

1. More people will run 8.3 than 8.2. Why? Because 8.3 will be in the
wild as current stable longer than 8.2.

2. Red Hat ES will likely never ship 8.2, ES 5 shipped with 8.1. That
means more yet even more people will run 8.1 versus 8.2 (which doesn't
argue for 8.3 but does argue against 8.2)

3. Ubuntu Dapper, which is LTS (like ES) ships with 8.1, the next LTS
will ship with 8.3 (likely).

4. Novell SUSE, shipps 8.1.5 in 10.2, 10.3 is going to ship with 8.3
(unless 8.3 slips horribly)

4. Solaris (JoshB help me here), ships with 8.1.


The trend here is that although 8.2 is a good release, people won't see
the benefits of 8.2 until they install 8.3 or 8.4. Further, because
people are going to wait (we are talking generally here).

It just doesn't make sense, to go through an entire upgrade cycle over
multiple machines just to upgrade to what will likely be obsolete
(because 8.3 will be out) by the time he works out all the kinks of the
upgrade in the first place.

Not everyone will agree, and that's cool. If he wants to upgrade, have
at it.

Sincerely,

Joshua D. Drake





> 
>   regards, tom lane
> 
> ---(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/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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

Re: [GENERAL] pg_dumpall and version confusion

[Tony Caduto]

Dave Page wrote:
This is a problem I've been thinking about on and off recently - and I 
am starting to come to the conclusion that shipping each version of 
the utilities is the only way things are likely to work unless someone 
puts some really significant effort into adding backwards 
compatibility modes to pg_dump (which I imagine is likely to meet 
resistance if offered as a patch anyway).




I never had a issue before the 8.2 dump and the GRANT ON SEQUENCE. 
The version differences in PGSQL are nothing compared to what goes on 
with MySQL.


Maybe that would be a good Google summer of code project :-)  (make 
pg_dump more backwards compatible to at least 8.0)


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] pg_dumpall and version confusion

[Scott Marlowe]

On Thu, 2007-03-15 at 11:53, Tony Caduto wrote:
> Dave Page wrote:
> > This is a problem I've been thinking about on and off recently - and I 
> > am starting to come to the conclusion that shipping each version of 
> > the utilities is the only way things are likely to work unless someone 
> > puts some really significant effort into adding backwards 
> > compatibility modes to pg_dump (which I imagine is likely to meet 
> > resistance if offered as a patch anyway).
> >
> 
> I never had a issue before the 8.2 dump and the GRANT ON SEQUENCE. 
> The version differences in PGSQL are nothing compared to what goes on 
> with MySQL.
> 
> Maybe that would be a good Google summer of code project :-)  (make 
> pg_dump more backwards compatible to at least 8.0)

It would be quite useful to have a -sourcever and -targetver flag in
pg_dump that understood the latest version and the last two or three
versions.  So, if you had pg_dump for v8.2 you could do something like:

pg_dump -sourcever 8.2 -targetver 8.0 > dump.sql

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

[GENERAL] simple coordinate system

[Robin Ericsson]

Hi,

I'm planning a simple coordinate system, where objects are described
as x, y and z. Are there any contribs or extensions available that can
help me with datatypes, calculation of length between two points, etc?

--
   regards,
   Robin

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

Re: [GENERAL] simple coordinate system

[Webb Sprague]

... planning a simple coordinate system, where objects are described

as x, y and z. Are there any contribs or extensions available that can
help me with datatypes, calculation of length between two points, etc?


google postgis.  It is for geographic stuff, so maybe overkill, but
maybe not.  There are are also geometry types native to Postgres that
you can find in the docs



--
regards,
Robin

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



---(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] pg_dumpall and version confusion

[Tom Lane]

Tony Caduto <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> What's wrong with that?  8.2 will understand the GRANT ON SEQUENCE
>> without a problem.
>> 
> Yes, but 8.1 and earlier wont!

You're missing the point entirely.  The versioning contract for pg_dump
is that version M.N can dump from servers of versions <= M.N, but the
dump it produces is meant to load into server versions >= M.N.  If you
want a dump that will reload into 8.1, use 8.1's pg_dump.

pg_dump is messy enough already just trying to support this contract.
I have *no* interest in trying to get it to support an --output-version
switch...

regards, tom lane

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

Re: [GENERAL] simple coordinate system

[Robin Ericsson]

On 3/15/07, Webb Sprague <[EMAIL PROTECTED]> wrote:

... planning a simple coordinate system, where objects are described
> as x, y and z. Are there any contribs or extensions available that can
> help me with datatypes, calculation of length between two points, etc?

google postgis.  It is for geographic stuff, so maybe overkill, but
maybe not.  There are are also geometry types native to Postgres that
you can find in the docs


I was thinking about PostGIS, but it seemed overkill for my purpose.
Therefore I asked in the first place :)

--
   regards,
   Robin

---(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] Lifecycle of PostgreSQL releases

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> 1. More people will run 8.3 than 8.2. Why? Because 8.3 will be in the
> wild as current stable longer than 8.2.

Oh, gimme a break, Josh.  A year or more from now that argument would be
relevant, but unless you are going to counsel your customers not to
update till mid-2008, it's completely irrelevant to whether it makes
sense to update now.  If you *are* going to tell them to wait until
8.3.4 or so (which I can see an argument for, if you don't like being
an early adopter), won't you then be in exactly the same position that
"8.4 is just around the corner"?

Your other four points are mere rehashings of that one.

regards, tom lane

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

Re: [GENERAL] plperl function called > once in cascading triggers

[Martijn van Oosterhout]

On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote:
> What I have noticed is that once the innermost instance exits, none of 
> the outer instances execute any further, suggesting that the plperl 
> routine is not "re-entrant" (if I am using that term correctly).

Doesn't sound right, do you have a test case?

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] Lifecycle of PostgreSQL releases

[Brandon Aiken]

If they have a support contract for, say, RHEL, why migrate to something
that support contract doesn't cover?  Those had better be some very
important features or some very critical bug fixes, the latter of which
are very likely to get backported if they're versions covered by a
support contract.

The upgrade question is "why?" not "why not?".

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Thursday, March 15, 2007 2:00 PM
To: Joshua D. Drake
Cc: Erik Jones; CAJ CAJ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Lifecycle of PostgreSQL releases 

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> 1. More people will run 8.3 than 8.2. Why? Because 8.3 will be in the
> wild as current stable longer than 8.2.

Oh, gimme a break, Josh.  A year or more from now that argument would be
relevant, but unless you are going to counsel your customers not to
update till mid-2008, it's completely irrelevant to whether it makes
sense to update now.  If you *are* going to tell them to wait until
8.3.4 or so (which I can see an argument for, if you don't like being
an early adopter), won't you then be in exactly the same position that
"8.4 is just around the corner"?

Your other four points are mere rehashings of that one.

regards, tom lane

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

** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.


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

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Joshua D. Drake]

> 
> Your other four points are mere rehashings of that one.

Yes. All of my points directly revolve around the reality that 8.2 is a
short cycle release and that 8.3 is a long cycle release. Further that
due to 8.2 being a short cycle release, it will not see as much
production action as 8.3 (and definitely not 8.1 per the current
enterprise releases).

That to me is an extremely valid point, and a point that my customers
have made *to me*.

Example discussion with customer:

Customer: CMD, should we update to 8.2.3
CMD: Is there something in 8.2.3 that will benefit you?
Customer: We don't know
CMD: Are you having problems with 8.1? (We try to push all customers to
at least 8.1)
Customer: No, it is just that 8.2 is the current release
CMD: True, but 8.3 is due out in the summer and 8.3 is a standard cycle
release
Customer: Oh... o.k. let's wait.
CMD: I think that is probably prudent.


I am not just coming up with this stuff to be difficult. This is real
world here. Couple the above, with my previous post and *unless* there
is something that 8.2 gives you explicitly (and there are reasons to
upgrade to 8.2), there *may* (note word *may*) not be a reason to upgrade.

Take that and add, that 8.3 is just around the corner and my argument
stands.

The only argument anyone that I see against the above is the, "upgrade
because it is shiny argument". Which indeed may (there is that word
again) be enough. In business, shiny can be bad.

What I see in this thread, is people saying 8.2.3 is the cat's meow,
which of course is true. That doesn't mean that you need to upgrade.

I have a 8 year old Saab 9-5 V6 Turbo. It has leather, heated and air
conditioned seats. True, it is 8 years old, but it only has 62k on it.
The new model, offers some better styling, a 4 cylinder with more
horsepower and the paint reflects light just a little better.

Does that mean I want to take my debt free car, and trade it in for a
new 40k loan? Not on your life, my 8 year old Saab has at least 2 more
years in it and I was smart and bought an extended warranty.

Why is it, that every time someone suggests that someone may not need to
upgrade to the latest and greatest paint job, social networking site or
piece of software that people get upset?

Sincerely,

Joshua D. Drake


> 
>   regards, tom lane
> 


-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Erik Jones]

On Mar 15, 2007, at 1:55 PM, Joshua D. Drake wrote:





Your other four points are mere rehashings of that one.


Yes. All of my points directly revolve around the reality that 8.2  
is a

short cycle release and that 8.3 is a long cycle release. Further that
due to 8.2 being a short cycle release, it will not see as much
production action as 8.3 (and definitely not 8.1 per the current
enterprise releases).

That to me is an extremely valid point, and a point that my customers
have made *to me*.

Example discussion with customer:

Customer: CMD, should we update to 8.2.3
CMD: Is there something in 8.2.3 that will benefit you?
Customer: We don't know
CMD: Are you having problems with 8.1? (We try to push all  
customers to

at least 8.1)
Customer: No, it is just that 8.2 is the current release
CMD: True, but 8.3 is due out in the summer and 8.3 is a standard  
cycle

release
Customer: Oh... o.k. let's wait.
CMD: I think that is probably prudent.


I am not just coming up with this stuff to be difficult. This is real
world here. Couple the above, with my previous post and *unless* there
is something that 8.2 gives you explicitly (and there are reasons to
upgrade to 8.2), there *may* (note word *may*) not be a reason to  
upgrade.


Take that and add, that 8.3 is just around the corner and my argument
stands.

The only argument anyone that I see against the above is the, "upgrade
because it is shiny argument". Which indeed may (there is that word
again) be enough. In business, shiny can be bad.

What I see in this thread, is people saying 8.2.3 is the cat's meow,
which of course is true. That doesn't mean that you need to upgrade.

I have a 8 year old Saab 9-5 V6 Turbo. It has leather, heated and air
conditioned seats. True, it is 8 years old, but it only has 62k on it.
The new model, offers some better styling, a 4 cylinder with more
horsepower and the paint reflects light just a little better.

Does that mean I want to take my debt free car, and trade it in for a
new 40k loan? Not on your life, my 8 year old Saab has at least 2 more
years in it and I was smart and bought an extended warranty.

Why is it, that every time someone suggests that someone may not  
need to
upgrade to the latest and greatest paint job, social networking  
site or

piece of software that people get upset?


Are you saying you don't have a MySpace account :)?

erik jones <[EMAIL PROTECTED]>
sofware developer
615-296-0838
emma(r)

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[Ken Johanson]

John D. Burger wrote:

Stephane Bortzmeyer wrote:


But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)


It's a little tricky to get good performance for all the operations:

The addition and subtraction operations are complex. They will require 
approximately two gcd operations, 3 divisions, 3 multiplications and 


[..trimed]


(From the Booost rational package - 
http://www.boost.org/libs/rational/rational.html)


I'd try to link to an existing library that provides rationals, or model 
my code closely after one.




Yes, it may be easy to create composite type but the operands would
still be needed.

Select n FROM t WHERE frac1 < frac2;

John, do you how compatible the Booost license is (or can be made :-)
with PG, in the case where adding this to the server as a standard
datum-type might be very useful (for me anyway).

Ken

PS - Sorry for the re-send with-the-list, John



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

Fwd: [GENERAL] Native type for storing fractions (e.g 1/3)?

[John D. Burger]

Oops, I though Ken had written privately to me - here's my reply:

(From the Booost rational package - http://www.boost.org/libs/ 
rational/rational.html)
I'd try to link to an existing library that provides rationals, or  
model my code closely after one.


Yes, it may be easy to create composite type but the operands would  
still be needed.


Select n FROM t WHERE frac1 < frac2;

John, do you how compatible the Booost license is (or can be  
made :-) with PG, in the case where adding this to the server as a  
standard datum-type might be very useful (for me anyway).


I was just using the Boost rational package as an example, mostly to  
quote those implementation comments from the docs.  Boost is a set of  
C++ libraries, PG is C, so no direct adoption possible.


However, I think the Boost license is essentially BSD-like, so it  
might be possible to steal the algorithms pretty easily.  I haven't  
looked, but I would be surprised if there was much OOP in the  
rational code, it might be fairly trivial to "dumb it down" to C.


- John D. Burger
  MITRE




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

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

[GENERAL] Problem to install pgAdmin

[Ezequias R. da Rocha]

Hi,

We here in my job are having many trouble of compatibility with the 
wx-widgets and it has been a quite difficult job.


Having so many installation dependencies we are almost give up.

Could someone list all dependencies to install pgAdmin (actually I just 
need to put pgAgent to run) on Red Hat 4 (postgresql 8.2.3) ?


Any help would be very welcomed.

Ezequias


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

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

Re: [GENERAL] PITR and WAL archiving

[Florian G. Pflug]

Adam Groves wrote:

I have been ramming my head against the wall for the past two
evenings, trying to get PITR working and it's gotten to the point
where I need to ask someone for help.

I'm basically trying to follow the instructions in the manual in
chapter 23.3.

Which postgres version are you using?


1. pg_ctl start -l logfile
2. SELECT pg_start_backup('label');
3. tar -zcf /mnt/server/archive/file/backup.tar.gz . (the contents of
'data')
4. SELECT pg_stop_backup();
5. pg_ctl stop
6. I now delete the contents of 'data' (making a copy before hand)
7. In 'data': tar xzvf /mnt/server/archive/file/backup.tar.gz
8. echo \"restore_command = 'cp /mnt/server/archive/wal/%f %p'\" >
recovery.conf
9. pg_ctl start -l logfile

Are you doing this all in one row, without any other queries
(especially updates) hitting the database?

The reason that I ask is that postgres 8.1 will *only* archive a
wal segment (a wal file) if it's completly filled. Since every
WAL segment in 8.1 is *exactly* 16MB large, archive_command will
only get called after 16MB worth of wal traffic has happened.

In 8.1, pg_stop_backup() won't trigger archive_command either -
it can't, because postgres 8.1 can't close a wal segment early -
it *has* to write 16MB first. Therefore, the part of the wal
that is bring your filesystem backup into a consistent state
lives in the currently open wal after pg_stop_backup is called.

Now, you remove your datadir, and restore your filesystem backup.
Postgres realizes that it needs the wal segement that was current
when pg_start_backup() was called, and requests this from
restore_command. But since this segement was the current segment
up to the point where you removed your datadir, it got never archived :-(.

Postgres 8.2 improves the situation. It can now switch wal segments
early, and will do so if you call pg_stop_backup().

greetings, Florian Pflug

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

  http://archives.postgresql.org/

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[Florian G. Pflug]

Ron Johnson wrote:

CREATE TYPE ty_fraction AS
(
n   SMALLINT,
d   SMALLINT
);


You'd need a type for large integers first - otherwise your
ty_fraction will be quite limited. I think numeric could be
used for that, though I don't know if numeric guarantees that
at least the operators +,-,* and modulo return exact results
(Don't loose digits). But even if they do, using some existing
library for arbitrary sized integers would probably lead to
better performance.

greetings, Florian Pflug

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

Re: [GENERAL] Problem to install pgAdmin

[Florian G. Pflug]

Ezequias R. da Rocha wrote:

Hi,

We here in my job are having many trouble of compatibility with the 
wx-widgets and it has been a quite difficult job.


Having so many installation dependencies we are almost give up.

Could someone list all dependencies to install pgAdmin (actually I just 
need to put pgAgent to run) on Red Hat 4 (postgresql 8.2.3) ?


Any help would be very welcomed.

For pgAdmin3 1.6.2 you need wxWidgets 2.8.0, libxml2, libxslt and
libpq.

libxml2 AFAIK only requires zlib, and even that is optional.
libxslt requires libxml2.
wxWidgets requires gtk and X11 headers - but those should be
part of your distribution - you need to find out which
packages contain those (try libgtk-dev, libgtk-devel, libgtk2-deve
or something like that).

greetings, Florian Pflug

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

  http://archives.postgresql.org/

Re: [GENERAL] [SQL] PostgreSQL to Oracle

[Robert Treat]

On Friday 09 March 2007 13:32, Jonah H. Harris wrote:
> To copy the data and table definitions, I'd use a database link (on
> the Oracle side) with hsodbc connecting to your PostgreSQL system via
> ODBC.
>

Do you find this works well?  I've used it from some older Oracle instances 
connecting back into PostgreSQL and the results I had have been flakey at 
best. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

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

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[Martijn van Oosterhout]

On Thu, Mar 15, 2007 at 09:11:23PM +0100, Florian G. Pflug wrote:
> You'd need a type for large integers first - otherwise your
> ty_fraction will be quite limited. I think numeric could be
> used for that, though I don't know if numeric guarantees that
> at least the operators +,-,* and modulo return exact results
> (Don't loose digits). But even if they do, using some existing
> library for arbitrary sized integers would probably lead to
> better performance.

Numeric is an arbitrary sized number library, so using that would be
smart.

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] simple coordinate system

[Webb Sprague]

http://www.postgresql.org/docs/8.2/static/datatype-geometric.html

Have you looked at these yet?  If not, you asked your question
prematurely and should have read the docs.  If so, in what respect do
they not work for you?


On 3/15/07, Robin Ericsson <[EMAIL PROTECTED]> wrote:

On 3/15/07, Webb Sprague <[EMAIL PROTECTED]> wrote:
> ... planning a simple coordinate system, where objects are described
> > as x, y and z. Are there any contribs or extensions available that can
> > help me with datatypes, calculation of length between two points, etc?
>
> google postgis.  It is for geographic stuff, so maybe overkill, but
> maybe not.  There are are also geometry types native to Postgres that
> you can find in the docs

I was thinking about PostGIS, but it seemed overkill for my purpose.
Therefore I asked in the first place :)

--
   regards,
   Robin



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

Re: [GENERAL] pg_dumpall and version confusion

[Dave Page]

Tom Lane wrote:

I have *no* interest in trying to get it to support an --output-version
switch...


There speaks a man who has the luxury of not having to worry about 
multi-version admin tools :-(


/D

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

  http://archives.postgresql.org/

Re: [GENERAL] pg_dumpall and version confusion

[Alvaro Herrera]

Dave Page wrote:
> Tom Lane wrote:
> >I have *no* interest in trying to get it to support an --output-version
> >switch...
> 
> There speaks a man who has the luxury of not having to worry about 
> multi-version admin tools :-(

Is it more difficult for you to ship a pg_dump-8.0, pg_dump-8.1, etc
along pgAdmin?  I think you (and phpPgAdmin?) are already shipping
pg_dump binaries, right?

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

---(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] Is This A Set Based Solution?

[Bruno Wolff III]

On Mon, Mar 12, 2007 at 11:15:01 -0700,
  Stefan Berglund <[EMAIL PROTECTED]> wrote:
> 
> I have an app where the user makes multiple selections from a list.  I
> can either construct a huge WHERE clause such as SELECT blah blah FROM
> foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
> alternatively pass the string of IDs ('53016,27,292,512') to a table
> returning function which TABLE is then JOINed with the table I wish to
> query instead of using the unwieldy WHERE clause.  The latter strikes me
> as a far more scalable method since it eliminates having to use dynamic
> SQL to construct the ridiculously long WHERE clause which will no doubt
> ultimately bump up against parser length restrictions or some such.

How big is huge?
If the list of IDs is in the 1000s or higher, then it may be better to
load the data into a temp table and ANALYSE it before running your query.
Otherwise, for smaller lists the IN suggestion should work well in recent
versions.

---(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] [SQL] PostgreSQL to Oracle

[Jonah H. Harris]

On 3/15/07, Robert Treat <[EMAIL PROTECTED]> wrote:

Do you find this works well?  I've used it from some older Oracle instances
connecting back into PostgreSQL and the results I had have been flakey at
best.


It really just depends on the data types in use... but I've never
really had anything I'd call, "flakey" happen this way.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation| fax: 732.331.1301
33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED]
Iselin, New Jersey 08830| http://www.enterprisedb.com/

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

Re: [GENERAL] SQL Question - Group By and % results per row

[Bruno Wolff III]

On Mon, Mar 12, 2007 at 12:53:11 -0700,
  Mike <[EMAIL PROTECTED]> wrote:
> 
> How do I get access to the total of all clicks on per row basis so I
> can divide it? The only solution that comes to my mind is create a
> subquery that does a (select count(*) from... where... ) of the
> original grouped by sql statement.

That's how you do it.

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

Re: [GENERAL] Native type for storing fractions (e.g 1/3)?

[Florian G. Pflug]

Martijn van Oosterhout wrote:

On Thu, Mar 15, 2007 at 09:11:23PM +0100, Florian G. Pflug wrote:

You'd need a type for large integers first - otherwise your
ty_fraction will be quite limited. I think numeric could be
used for that, though I don't know if numeric guarantees that
at least the operators +,-,* and modulo return exact results
(Don't loose digits). But even if they do, using some existing
library for arbitrary sized integers would probably lead to
better performance.


Numeric is an arbitrary sized number library, so using that would be
smart.

Yeah, but it can do much more than just arbitrary sizes integers -
so I figured a library doing just integers might do them faster then
the numeric support in postgres does, or more space-efficient, or 
whatever...


It would only make sense to use a different library if it provides
a substantial advantage in speed or space, of course.

greetings, Florian Pflug



---(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] CREATE TABLESPACE dynamically

[Florian G. Pflug]

Richard Huxton wrote:

William Garrison wrote:
I am writing scripts to create a database that I want to run in my 
development, testing, and production environments.  That means I need 
to be able to do something like


IF 
  CREATE TABLESPACE foo LOCATION E'C:\database';
ELSE
  CREATE TABLESPACE foo LOCATION E'Z:\database';

I can't seem to find a way to do this, for two reasons:
1) I can't do IF statements unless I create a PL/PGSQL function.  And 
a PL/PGSQL function cannot issue a CREATE TABLESPACE command.


Windows (at least XP & 2003 Server) actually supports real symbolic 
links, even if they are not exposed through the UI provided by explorer. 
They are called "junktions" on windows, and you can only create linsk to

directories, not to files. But you could use them to e.g. create a link
from C:\postgres.data to E:\postgres.data on one machine, and to
F:\postgres.data on another. Than you can just write "LOCATION 
C:\postgres.data" in your script, and it will still use the correct

partition.

This is not an answer to your question, but maybe it's an easier 
solution to your problem.


greetings, Florian Pflug


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

Re: [GENERAL] PgSql on Vista?

[Paul Lambert]

Arkan wrote:

Hi all,

have anybody installed PgSQL on Windows Vista? I tried yesterday but I
fail... on XP and linux i've installed pgsql much times but on
vista...  nothing to do! Version in 8.2.3 with the installer.

If I install with my user account (with administrative rights), the
installlation fails on setting file permission, about at 3/4 of the
process.

If I install with Administrator account, the installer fail when
starting the service..

any ideas?



I've got it running on my vista machine, you just need to turn off User 
Account Control from the Control Panel -> Security Settings -> Other 
Settings for the duration of the installation.


After install completes you can turn it back on... if you want - 
personally I leave it off, it's an incredibly annoying "feature".


Note, turning UAC off and on both require reboots.

P.

--
Paul Lambert
Database Administrator
AutoLedgers

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

Re: [GENERAL] pg_dumpall and version confusion

[Dave Page]

Alvaro Herrera wrote:

Dave Page wrote:

Tom Lane wrote:

I have *no* interest in trying to get it to support an --output-version
switch...
There speaks a man who has the luxury of not having to worry about 
multi-version admin tools :-(


Is it more difficult for you to ship a pg_dump-8.0, pg_dump-8.1, etc
along pgAdmin?  I think you (and phpPgAdmin?) are already shipping
pg_dump binaries, right?



Yes, because we also have to ship the appropriate versions of libpq.dll 
and it's 5 or 6 dependencies as well, and keep them all in seperate 
directories. pgAdmin also support EnterpriseDB, so might also ship the 
different versions of those utilities in the future, along with their 
slightly different dependencies.


Even if we went through the insane pain (for me at least) of maintaining 
multiple branches of the Windows build of PostgreSQL, *and* custom 
builds of the utilities built against a single libpq, we still couldn't 
ship a single set because pg_dumpall.v80.exe wouldn't find 
pg_dump.v80.exe without modification of the source.


Whichever way you cut it, it'll be a ridiculous amount of work to 
maintain, unless there were a --match-version option in pg_dump to allow 
dumps of prior versions of the server, in a format appropriate for that 
version.


Regards, Dave

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

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

Re: [GENERAL] PgSql on Vista?

[Dave Page]

Paul Lambert wrote:

After install completes you can turn it back on... if you want - 
personally I leave it off, it's an incredibly annoying "feature".


Doesn't the security center keep popping up to point out that it's 
turned off?


Regards Dave

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

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

Re: [GENERAL] PgSql on Vista?

[Paul Lambert]

Dave Page wrote:

Paul Lambert wrote:

After install completes you can turn it back on... if you want - 
personally I leave it off, it's an incredibly annoying "feature".


Doesn't the security center keep popping up to point out that it's 
turned off?


Regards Dave




Ahh, but there is an option on the menu on the lefthand side of security 
center "Change the way security center alerts me" through which you can 
select to either have an icon in your taskbar with a popup, to have just 
an icon in your taskbar, or to do nothing.


--
Paul Lambert
Database Administrator
AutoLedgers

---(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] plperl function called > once in cascading triggers

[Kenneth Downs]

Martijn van Oosterhout wrote:

On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote:
  
What I have noticed is that once the innermost instance exits, none of 
the outer instances execute any further, suggesting that the plperl 
routine is not "re-entrant" (if I am using that term correctly).



Doesn't sound right, do you have a test case?

Have a nice day,
  


Yes, but it is all tied up in my framework.  I'll put together a 
hardcoded example.

[GENERAL] pg_dump warning format?

[Erik Jones]

Hello, I'm currently working on an automated data archival script  
that will dump an existing archive db, drop it, recreate it from a  
template, and then fill it with any data ready to be archived from  
our production db.  My question here is:  What is the format of the  
warnings and/or errors that pg_dump will spit out on standard error?   
Does redirect_stderr set in the config file affect the pg_dump  
utility?  Basically I need a way for my script to know if it needs to  
abort because there were issues with the dump before it follows  
through with the drop db...


erik jones <[EMAIL PROTECTED]>
sofware developer
615-296-0838
emma(r)

Re: [GENERAL] PgSql on Vista?

[Alvaro Herrera]

Dave Page escribió:
> Paul Lambert wrote:
> 
> >After install completes you can turn it back on... if you want - 
> >personally I leave it off, it's an incredibly annoying "feature".
> 
> Doesn't the security center keep popping up to point out that it's 
> turned off?

You mean, like this?

http://images.apple.com/movies/us/apple/getamac/apple-getamac-security_480x376.mov

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

---(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] pg_dumpall and version confusion

[Martijn van Oosterhout]

On Thu, Mar 15, 2007 at 09:19:31PM +, Dave Page wrote:
> Even if we went through the insane pain (for me at least) of maintaining 
> multiple branches of the Windows build of PostgreSQL, *and* custom 
> builds of the utilities built against a single libpq, we still couldn't 
> ship a single set because pg_dumpall.v80.exe wouldn't find 
> pg_dump.v80.exe without modification of the source.

Debian for years has been running a system where multiple versions can
be co-installed and if you call pg_dumpall it calls the right pg_dump,
you just have to indicate the cluster you want to operate on.

Porting it to Windows could be tricky though, given its use of symlinks
and wrapper scripts.

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] PgSql on Vista?

[Dave Page]

> --- Original Message ---
> From: Alvaro Herrera <[EMAIL PROTECTED]>
> To: Dave Page <[EMAIL PROTECTED]>
> Sent: 15/03/07, 22:32:50
> Subject: Re: [GENERAL] PgSql on Vista?
> 
> Dave Page escribió:
> > Paul Lambert wrote:
> > 
> > >After install completes you can turn it back on... if you want - 
> > >personally I leave it off, it's an incredibly annoying "feature".
> > 
> > Doesn't the security center keep popping up to point out that it's 
> > turned off?
> 
> You mean, like this?
> 
> http://images.apple.com/movies/us/apple/getamac/apple-getamac-security_480x376.mov
> 

I haven't watched yet as I'm on my pda, but would it seem a little ironic if I 
pointed out that the Windows distribution of PostgreSQL is built on a Macbook 
Pro these days?

/D

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

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

Re: [GENERAL] pg_dumpall and version confusion

[Paul Lambert]

Porting it to Windows could be tricky though, given its use of symlinks
and wrapper scripts.




FWIW, as of Vista and Server Longhorn, Windows now supports Symlinks. I 
don't know about wrapper scripts though.


--
Paul Lambert
Database Administrator
AutoLedgers


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

  http://archives.postgresql.org/

Re: [GENERAL] PgSql on Vista?

[Paul Lambert]

Alvaro Herrera wrote:

Dave Page escribió:

Paul Lambert wrote:

After install completes you can turn it back on... if you want - 
personally I leave it off, it's an incredibly annoying "feature".
Doesn't the security center keep popping up to point out that it's 
turned off?


You mean, like this?

http://images.apple.com/movies/us/apple/getamac/apple-getamac-security_480x376.mov



Hillarious :) Yet sadly accurate...

--
Paul Lambert
Database Administrator
AutoLedgers

---(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] pg_dumpall and version confusion

[Joshua D. Drake]

Dave Page wrote:
> Tom Lane wrote:
>> I have *no* interest in trying to get it to support an --output-version
>> switch...
> 
> There speaks a man who has the luxury of not having to worry about
> multi-version admin tools :-(

I am afraid that I have to agree with Tom here. Pg_dump has plenty of
other deficiencies let's not add to complexity by having an
--output-version.

If you want that, create a wrapper program that calls to different
statically compiled versions of pg_dump.

Joshua D. Drake


> 
> /D
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>   http://archives.postgresql.org/
> 


-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

[GENERAL] exception handling in plperlu

[Jasbinder Singh Bali]

Hi,
Actually, if I rephrase my requirement, I need to catch an exception at any
point ,where ever it is raised, in the perl code.
E.g during an insert, there is a foreign key contraint violation, then i
need to catch this specific error and do something with it.
Hope i make some sense here.

Thanks,
Jas

Re: [GENERAL] exception handling in plperlu

[Tom Lane]

"Jasbinder Singh Bali" <[EMAIL PROTECTED]> writes:
> Actually, if I rephrase my requirement, I need to catch an exception at any
> point ,where ever it is raised, in the perl code.
> E.g during an insert, there is a foreign key contraint violation, then i
> need to catch this specific error and do something with it.

Since PG 8.0, you should be able to trap exceptions with eval{}, same as
you would do in any Perl code.

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] exception handling in plperlu

[Jasbinder Singh Bali]

How about using a try catch block?
isn't that more efficient that eval?

~Jas


On 3/15/07, Tom Lane <[EMAIL PROTECTED]> wrote:


"Jasbinder Singh Bali" <[EMAIL PROTECTED]> writes:
> Actually, if I rephrase my requirement, I need to catch an exception at
any
> point ,where ever it is raised, in the perl code.
> E.g during an insert, there is a foreign key contraint violation, then i
> need to catch this specific error and do something with it.

Since PG 8.0, you should be able to trap exceptions with eval{}, same as
you would do in any Perl code.

   regards, tom lane

Re: [GENERAL] About the new day-time saving rule

[Michael Su]

Hi,

 

Please ignore my previous email.

 

Sorry,

 

Michael

  _  

From: Michael Su [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 15, 2007 6:43 PM
To: 'pgsql-general@postgresql.org'
Subject: About the new day-time saving rule

 

Hi,

 

I'm just wondering that does postgres have a time zone updater for the new
day time saving rule like java has?
(http://java.sun.com/javase/tzupdater_README.html). Or do I need to change
the time manually myself? If that is the case, if the time changes again
sometime in September then I would need to manually change it myself again?
Please advice when you get a chance.

 

Thank you in advance,

 

Michael

 

 

Re: [GENERAL] pg_dump warning format?

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> Basically I need a way for my script to know if it needs to  
> abort because there were issues with the dump before it follows  
> through with the drop db...

Well, just checking the exit status from pg_dump ought to give you at
least a go/no-go indicator.  If you want to be significantly more
paranoid, you could try the theory that *any* output to stderr is
indicative of a problem.

regards, tom lane

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

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

[GENERAL] About the new day-time saving rule

[Michael Su]

Hi,

 

I'm just wondering that does postgres have a time zone updater for the new
day time saving rule like java has?
(http://java.sun.com/javase/tzupdater_README.html). Or do I need to change
the time manually myself? If that is the case, if the time changes again
sometime in September then I would need to manually change it myself again?
Please advice when you get a chance.

 

Thank you in advance,

 

Michael

 

 

Re: [GENERAL] Role & User - Inheritance?

[Tom Lane]

"Alexi Gen" <[EMAIL PROTECTED]> writes:
> Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt 
> user01 also inherit this?

No, he has to actually SET ROLE to the superuser account to make himself
a superuser.  SUPERUSER and the other non-GRANTable attributes of a role
don't inherit through mere membership.  This is partly an implementation
limitation but I happen to think it's a good idea ... superuserness is a
mighty big hammer to be letting loose accidentally.

regards, tom lane

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

Re: [GENERAL] pre-parser query manipulation

[Randall Smith]

Erik Jones wrote:
This may be a dupe.  I sent and had some issues with my mail client and 
afterwards it was sitting all alone in my outbox, if you've already seen 
this, sorry for the resend...


On Mar 14, 2007, at 2:08 PM, Randall Smith wrote:


Hi,

I'm attempting to process a query written in the form:

SELECT fields FROM table WHERE conditions FOR UPDATE OF field_name

when Postgresql supports:

SELECT fields FROM table WHERE conditions FOR UPDATE OF table_name

I'd like to remove the OF clause in the case only one table is queried.

Anyone know of a solution to this?


The OF clause is optional in postrges.  The semantics are thus:

Without OF clause:  lock all rows in all tables in the from clause that 
contribute data to the rows returned by your select statement.  So, if 
I'm understanding what you're asking here, just chop off the OF clause 
and you're set.


With OF clause: lock only rows from the specified tables that contribute 
data to the rows returned by your select statement.


There isn't any way that I know of to lock specific columns on a table 
as it seems the first query you listed is doing.


erik jones <[EMAIL PROTECTED] >
sofware developer
615-296-0838
emma(r)





I'm at a loss as to how to remove the OF clause at the server.  It it 
possible to do it with a rule?  I don't have control over the 
application and it's written to work with Oracle.  I do have control 
over the JDBC driver, so maybe I should look for a solution there. 
Maybe a wrapper or something.


Randall


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

[GENERAL] Practical question.

[louis gonzales]

Hello List,
I want to write a statement-level trigger - one that happens once per 
statement - such that, immediately after an insert into a table(which 
gets a unique integer value as an ID from a defined sequence, being the 
primary key on the table), a new table is created with foreign key 
constraint on that unique ID.
My concern is with what happens if two such inserts occur at almost the 
same time, what is the best way to ensure that I never miss the creation 
of one of the tables due to the 2nd one, possibly reading the same "max" 
or nextvalue that the immediate previous trigger read.


As an example:
insertX which initiates the trigger reads the 'nextvalue' from the 
sequence and begins to create the associcated table
insertY happens almost at the same time, so that it gets the same 
'nextvalue' from the sequence and would then create or attempt to create 
the 'same' assoicated table while missing it's 'true' 'nextvalue'.


Thanks for any insight!


--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

Re: [GENERAL] pre-parser query manipulation

[Tom Lane]

Randall Smith <[EMAIL PROTECTED]> writes:
> I'm at a loss as to how to remove the OF clause at the server.  It it 
> possible to do it with a rule?

No, because the syntax error will occur long before any rule has a
chance to get involved.  You'd have to actually hack the C code
(see transformLockingClause in src/backend/parser/analyze.c).

It's unfortunate that somebody decided to use the standard FOR UPDATE
syntax to mean something that's got nearly 0 to do with what the spec
intends it to mean :-(.

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] Practical question.

[Tom Lane]

louis gonzales <[EMAIL PROTECTED]> writes:
> As an example:
> insertX which initiates the trigger reads the 'nextvalue' from the 
> sequence and begins to create the associcated table
> insertY happens almost at the same time, so that it gets the same 
> 'nextvalue' from the sequence 

[ blink... ]  Whatever makes you think that could happen?

regards, tom lane

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