Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Tom Lane]

Karsten Hilbert <[EMAIL PROTECTED]> writes:
> The fact that different versions of PostgreSQL get it right or
> wrong in a variety of ways indicates that the logic may need
> to be fixed but does show that in principle it is quite
> possible.

7.1's version of AT TIME ZONE was so badly broken that it doesn't really
matter whether it accidentally failed to malfunction in your particular
test case.  That's simply not relevant to later versions.

regards, tom lane

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

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

Re: [GENERAL] Immutable attributes?

[Stephane Bortzmeyer]

On Sun, Apr 24, 2005 at 02:53:51PM -0700,
 David Fetter <[EMAIL PROTECTED]> wrote 
 a message of 21 lines which said:

> > PostgreSQL 7.4, switching to 8.0 would be difficult.
> 
> Now is easier than later.

Do you mean that PostgreSQL 8 has immutable attributes ? I do not find
that.


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

Re: [GENERAL] Immutable attributes?

[Stephane Bortzmeyer]

On Sun, Apr 24, 2005 at 10:05:53PM -0600,
 Michael Fuhr <[EMAIL PROTECTED]> wrote 
 a message of 85 lines which said:

> CREATE FUNCTION check_immutable() RETURNS trigger AS '
> for col in TD["args"]:

Ah, yes, much better than mine. Thanks.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Karsten Hilbert]

On Mon, Apr 25, 2005 at 03:05:12AM -0400, Tom Lane wrote:

> > The fact that different versions of PostgreSQL get it right or
> > wrong in a variety of ways indicates that the logic may need
> > to be fixed but does show that in principle it is quite
> > possible.
> 
> 7.1's version of AT TIME ZONE was so badly broken that it doesn't really
> matter whether it accidentally failed to malfunction in your particular
> test case.  That's simply not relevant to later versions.

Neither am I complaining about 7.1 malfunctioning nor am I
saying that the actual result matters. What I *am* saying is
that - although some versions may be wrong or right in
different ways - one can surely get values mapped to different
time zones for one and the same stored timestamp out of the
database in a single query. Which was what I thought the OP
said wasn't possible.

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

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

   http://archives.postgresql.org

Re: [GENERAL] Help-Urgent

[Mario Guenterberg]

ElayaRaja S schrieb:
> Hi,
>I am using Redhat Linux 9 and PostgreSQL 7.4.5. While shutdown the
> linux server i forget to stop the postgresql. After starting the linux
> server, now i am unable to stop the postgres. When stopping the
> postgres  i am getting the following error as
> 
> bash-2.05b$ pg_ctl status
> pg_ctl: postmaster is running (PID: 17173)
> Command line was:
> /usr/local/pgsql/bin/postmaster
> 
> bash-2.05b$ pg_ctl stop
> /usr/local/pgsql/bin/pg_ctl: line 274: kill: (17173) - No such process
> waiting for postmaster to shut
> down
> failed
> pg_ctl: postmaster does not shut down
> 
> 
> Note: please help me to shutdown the postgresql.
> 
> 

Hi

remove your pidfile in /usr/local/pgsql or elsewhere.
Then start the postgresql server.

best regards

-- 
Mario Günterberg
mattheis. werbeagentur
IT Engineer / Projektleiter

Zillestrasse 105a. D - 10585 Berlin
Tel#49-(0)30 . 34 80 633 - 0
Fax#49-(0)30 . 34 80 633 50
http://www.mattheis-berlin.de

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] What does "tuple concurrently updated" mean?

[Florian G. Pflug]

Hi
During a batch load of records (using COPY) I got the following
postgres error today "tuple concurrently updated".
What could have caused this?
The only two occurences of the message in the source is in
the functions simple_head_delete and simple_heap_update
in src/backend/access/heap/heapam.c
greetings, Florian Pflug


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] SQLException "Connection is closed. Operation is not

[Ragnar =?ISO-8859-1?Q?Hafsta=F0?=]

On Mon, 2005-04-25 at 03:38 +0530, Rajiv Verma wrote:
> I'm accessing postgres database through tomcat 4.3.1.
> I'm able to execute the select and update query through my application
> but Insert query is giving following SQLException :
> Connection is closed.  Operation is not permitted.

looks like your application is closing the database
connection before the insert.

are you doing the insert within the same tomcat
request as the selects ?

con you reproduce this using the simplest possible
test case. i.e:
have your doPost() only 
  a) open db connection
  b) perform select
  c) perform insert
  d) close db connection
  e) return ok page

gnari



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

[GENERAL] column size in libpq

[Piotr Filipczuk]

How to determinate column size in query in libpq? Especially when column 
is declared for exapmle as varchar(64).  PQfsize return -1 which mean 
that is variable and PQfmod returns 68.

Piotr Filipczuk
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Installation problem with the version 8.0.2

[K.RajaSekar]

Hi

I am trying to install the 8.0.2 version in windows 2000 server. After
opening the installer, I have got the welcome screen. I have selected the
Language English/English in the welcome screen and clicked the start button.
I am getting the following error.

"There is a problem with this windows installer package. A DLL required for
this install to complete could not be run. Contact your support personnel or
package vendor."

What could be the problem?. While replying to this mail, I am requesting
everyone, please send your reply to [EMAIL PROTECTED]

Regards
K.RajaSekar


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] SQLException "Connection is closed. Operation is not

[Clifton Zama]

On Monday 25 April 2005 10:54, you wrote:
> On Mon, 2005-04-25 at 03:38 +0530, Rajiv Verma wrote:
> > I'm accessing postgres database through tomcat 4.3.1.
> > I'm able to execute the select and update query through my application
> > but Insert query is giving following SQLException :
> > Connection is closed.  Operation is not permitted.
>
> looks like your application is closing the database
> connection before the insert.
>
> are you doing the insert within the same tomcat
> request as the selects ?
>
> con you reproduce this using the simplest possible
> test case. i.e:
> have your doPost() only
>   a) open db connection
>   b) perform select
>   c) perform insert
>   d) close db connection
>   e) return ok page
>
> gnari
 
I am having a similar problem from a stand-alone application.The log says 
something about an unexpected EOF , so I thought maybe it was the program 
closing the connection.But this is not the case because it closes while 
iterating through a result set.It reads the first one , then when rs.next() 
is called again , it says : Connection is closed.  Operation is not 
permitted.

Clifton

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Optimising Union Query.

[Rob Kirkbride]

Jim C. Nasby wrote on 25/04/2005 01:28:
On Sat, Apr 23, 2005 at 10:39:14PM +, Patrick TJ McPhee wrote:
 

In article <[EMAIL PROTECTED]>,
Rob Kirkbride <[EMAIL PROTECTED]> wrote:
% I've done a explain analyze and as I expected the database has to check 
% every row in each of the three tables below but I'm wondering if I can 

This is because you're returning a row for every row in the three
tables.
% select l.name,l.id from pa i,locations l where i.location=l.id union 
% select l.name,l.id from andu i,locations l where i.location=l.id union 
% select l.name,l.id from idu i,locations l where i.location=l.id;

You might get some improvement from
select name,id from locations
 where id in (select distinct location from pa union
  select distinct location from andu union
  select distinct location from idu);
   

Note that SELECT DISTINCT is redundant with a plain UNION. By
definition, UNION does a DISTINCT. In fact, this is going to hurt you;
you'll end up doing 4 distinct operations (one for each SELECT DISTINCT
and one for the overall UNION). Unless some of those tables have a lot
of duplicated location values, you should either use UNION ALLs or drop
the DISTINCTs. Note that going with DISTINCTs is different than what
your original query does.
You should also consider this:
SELECT name, id FROM locations l
   WHERE EXISTS (SELECT * FROM pa p WHERE p.location=l.id)
   OR EXISTS (SELECT * FROM andu a WHERE a.location=l.id)
   OR EXISTS (SELECT * FROM idu i WHERE i.location=l.id)
This query would definately be helped by having indexes on
(pa|andu|idu).location.
 

Thanks for that. I tried a few things, including using DISTINCTS and 
UNION ALLs but none made a big difference.
However  your query above sped things up by a factor of more than 2.

Thanks very much!
Rob
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Hosting Service Recommendations

[Chris Kratz]

It seems the consensus on this list is that when running postgres, Opterons 
outperform Xeons by a significant margin not to mention the CS storms 
postgres seems to cause on Xeons.  We are looking for a hosting service for a 
postgresql based application.  Unfortunately, it seems most services 
standardize on Dell and by extension Xeon hardware.  Do anyone have 
recommendations for hosting services that would be able to provide Opteron 
based hardware and have decent service?  Or are we stuck with Xeons?
-- 
Chris Kratz

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

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Michael Fuhr]

On Mon, Apr 25, 2005 at 08:28:47AM +0200, Karsten Hilbert wrote:
> > > gnumed=> create teable test (f timestamp with time zone);
> > > CREATE
> > 
> > Does that really work in 7.1.3?  Trying to create a "teable" fails
>
> This results from "script" logging backspaces into the log
> file instead of deleting characters in there and my not
> spotting that when cleaning up the log file. Nothing to do with
> the question at hand.

It doesn't matter in this particular case because we could see what
was intended (I honestly wondered if that was a typo that 7.1.3
accepted), but sometimes subtle differences can matter so it's
better to post code that others can copy and paste verbatim into
their database -- that way the corrections themselves don't introduce
different behavior.

> >   select f, f::timestamp at time zone 'MEZ' from test;
> >  f   |  timezone  
> >   ---+
> >2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
> >   (1 row)
>
> Huh ? This IS different time zones from the same timestamp
> within the same query, isn't it ??

No, it isn't.  In the above example from 7.2.7 the second column
has no time zone specification whatsoever; if you cast it to timestamp
with time zone then you get the following:

select f, (f::timestamp at time zone 'MEZ')::timestamptz from test;
   f   |   timezone
---+---
 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+00
(1 row)

There's only one time zone displayed here: +00.  Those are different
times being displayed in the same time zone, not the same time being
displayed in different time zones.  Other versions of PostgreSQL
give varying output, but they all show both columns in the same
time zone.

The problem I was discussing involves getting *different* time zone
specifictions in the output.  That is, something like this (which
apparently is possible in 7.1.3 but not in later versions):

   f   |   timezone
---+---
 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01

> The fact that different versions of PostgreSQL get it right or
> wrong in a variety of ways indicates that the logic may need
> to be fixed but does show that in principle it is quite
> possible.

Certainly it's possible in principle; the question is how to do it
in practice in modern versions of PostgreSQL.  As far as I can tell
it's not possible (short of changing the type to text, as one of
my earlier examples did), but again, I'd be pleased to be corrected
(preferably with an example that works in 8.0.2).

BTW, this is mostly academic to me, but others have asked similar
questions in the past so I've been curious about whether it could
be done.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] Installation problem with the version 8.0.2

[Akbar]

http://www.microsoft.com/downloads/details.aspx?FamilyID=4b6140f9-2d36-4977-8fa1-6f8a0f5dca8f&DisplayLang=en


On Mon, 2005-04-25 at 15:37 +0530, K.RajaSekar wrote:
> Hi
> 
> I am trying to install the 8.0.2 version in windows 2000 server. After
> opening the installer, I have got the welcome screen. I have selected the
> Language English/English in the welcome screen and clicked the start button.
> I am getting the following error.
> 
> "There is a problem with this windows installer package. A DLL required for
> this install to complete could not be run. Contact your support personnel or
> package vendor."
> 
> What could be the problem?. While replying to this mail, I am requesting
> everyone, please send your reply to [EMAIL PROTECTED]
> 
> Regards
> K.RajaSekar
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 


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

Re: [GENERAL] Hosting Service Recommendations

[Jerry Sievers]

Chris Kratz <[EMAIL PROTECTED]> writes:

> It seems the consensus on this list is that when running postgres,
> Opterons outperform Xeons by a significant margin not to mention the
> CS storms postgres seems to cause on Xeons.  We are looking for a
> hosting service for a postgresql based application.  Unfortunately,
> it seems most services standardize on Dell and by extension Xeon
> hardware.  Do anyone have recommendations for hosting services that
> would be able to provide Opteron based hardware and have decent
> service?  Or are we stuck with Xeons?  -- Chris Kratz

You might consider a co-located server.  You supply the hardware of
your choosing and the ISP puts it in their datacenter.

Less popular than dedicated server (owned by the ISP) but a good
choice if you have special hardware needs.

Not all ISPs do colo so, better check around.

HTH

-- 
---
Jerry Sievers   305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobilehttp://www.JerrySievers.com/

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

Re: [GENERAL] Immutable attributes?

[Michael Fuhr]

On Mon, Apr 25, 2005 at 09:18:16AM +0200, Stephane Bortzmeyer wrote:
> On Sun, Apr 24, 2005 at 02:53:51PM -0700, David Fetter <[EMAIL PROTECTED]> 
> wrote 
> > > 
> > > PostgreSQL 7.4, switching to 8.0 would be difficult.
> > 
> > Now is easier than later.
> 
> Do you mean that PostgreSQL 8 has immutable attributes ? I do not find
> that.

I think that was a comment about upgrading in general, not an
implication that 8.0 supports a particular feature.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] Pgpool questions

[Scott Marlowe]

On Sun, 2005-04-24 at 00:53, Ron Mayer wrote:
> Tatsuo Ishii wrote:
> >>
> >>Is there anyway to load balance selects to more than 2 servers? ex.  1 
> >>master with 2 slaves?  Run multiple instances?
> > 
> > It's on my TODO but will not be a trivial change.
> 
> Could I set up 2 pairs of pgpool-balanced servers, and use a
> third pgpool to load balance across the two pools?
> Or is that just silly.

I've actually thought of doing the same thing.  I can't see a reason why
it wouldn't work.

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

Re: [GENERAL] Pgpool questions

[Brian Maguire]

Thanks.

 

Is it on your todo list to something like this…?  It is the
opposite of "/* NO LOAD BALANCE */".

 

Make a "/* SLAVE */" type comment to force it to a slave? It
would be helpful in forcing a query to a reporting server if you know your what
your reporting queries are. 

 

“Set weight_secondary to 0.”  

 

Did you mean Set weight_master to 0.  Unfortunately this would not help
on a query by query basis, but the above would.

 

 

 

 

 

 

-Original Message-
From: Tatsuo Ishii [mailto:[EMAIL PROTECTED] 
Sent: Saturday, April 23, 2005 7:23 PM
To: Brian Maguire
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Pgpool questions

 

> I have a couple Pgpool questions regarding running pgpool as a
load balancer with Sony as the replicator.

> 

>  

> 

> Is there anyway to load balance selects to more than 2 servers?
ex.  1 master with 2 slaves?  Run multiple instances?

 

It's on my TODO but will not be a trivial change.

 

> Is there any way to tell a select (ex. with a function) to go only
the master and not the slave?

 

Pgpool will send any query not starting with "SELECT" or
"select" to

the master. So you can insert a white space or a comment, for example

 "/* NO LOAD BALANCE */" at the beginning of the line to
prevent the

 query to be sent to the slave.

 

> Is there any way to tell a select query to only go to the slave?  

 

Set weight_secondary to 0.

--

 

Tatsuo Ishii

Re: [GENERAL] Pgpool questions

[Tatsuo Ishii]

> On Sun, 2005-04-24 at 00:53, Ron Mayer wrote:
> > Tatsuo Ishii wrote:
> > >>
> > >>Is there anyway to load balance selects to more than 2 servers? ex.  1 
> > >>master with 2 slaves?  Run multiple instances?
> > > 
> > > It's on my TODO but will not be a trivial change.
> > 
> > Could I set up 2 pairs of pgpool-balanced servers, and use a
> > third pgpool to load balance across the two pools?
> > Or is that just silly.
> 
> I've actually thought of doing the same thing.  I can't see a reason why
> it wouldn't work.

Oh, cascading pgpool! Actually once I have tried it and it definitly
worked. The only concern is the overhead due to the cascading...
--
Tatsuo Ishii

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

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

[GENERAL] OpenRPT (was Re: [ANNOUNCE] == PostgreSQL Weekly News - April 24 2005 ==)

[Ned Lilly]

David Fetter wrote:
== PostgreSQL Product News ==
OpenRPT is a graphical SQL report writer, designer and rendering
engine, optimized for PostgreSQL. WYSIWYG display, GUI built with Qt,
runs on Linux, Windows, Mac OS X. Server-side rendering engine.
Reports can be saved as XML, either as files or in a database.
http://pgfoundry.org/projects/openrpt/
Dave, thanks for the plug - and thanks to everyone who's expressed an 
interest in OpenRPT so far.  It seems there are still some performance issues 
with pgFoundry  (pending setup of new servers?) - so for now, we're going to do 
our best not to contribute to the load, and point people to OpenRPT's home on 
SourceForge, where the iron is big and the pipes fat:
http://openrpt.sf.net/
We're very interested in engaging with the PostgreSQL community to help make 
OpenRPT better.  For those who aren't familiar with us, the OpenRPT project has 
spun out of an ERP application called OpenMFG.  I'm the CEO of OpenMFG LLC, the 
company; I was previously one of the founders of Great Bridge, a 
first-generation Postgres company that was a casualty of the dot-com boom/bust 
cycle four years ago.  I formed OpenMFG to bring a Postgres-based ERP system to 
market (www.openmfg.com) ... and along the way, part of what we built with our 
ERP was a multiplatform report writer.
The OpenMFG ERP Suite is inexpensive but not free; source code provided and patches accepted, but not 
OSI-certified "open source."  We decided that the report writer level of the "stack" was 
different, however, and decided to release OpenRPT under the GPL for anyone and everyone who wants to abide 
by the terms of the GPL.  For those who would like to "opt out" of the GPL, we also offer a 
commercial license.  For more on the licensing, as well as downloads, CVS, screenshots, docs, project info, 
etc., please check out the project homepage at http://openrpt.sf.net.
Thanks very much,
Ned
--
Ned Lilly
President and CEO
OpenMFG, LLC
420 North Center Drive
Building 11, Suite 115
Norfolk, VA 23502
tel: 757-461-3022
mailto: [EMAIL PROTECTED]
www.openmfg.com
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Hosting Service Recommendations

[Alex Turner]

I have found that even the larger hosting companies such as Rackspace
are pretty clueless when trying to configure even a mid size database
server.  They gave us a 4 drive raid 5 even after being explicitly
asked for a RAID 10. They also put the controller in write through
cache mode.  Pretty bad config - the server blew chunks, our single
drive ATA cheapo box ran significantly faster.

I would definatley suggest looking at co-lo.

Alex Turner
netEconomist

On 4/25/05, Chris Kratz <[EMAIL PROTECTED]> wrote:
> It seems the consensus on this list is that when running postgres, Opterons
> outperform Xeons by a significant margin not to mention the CS storms
> postgres seems to cause on Xeons.  We are looking for a hosting service for a
> postgresql based application.  Unfortunately, it seems most services
> standardize on Dell and by extension Xeon hardware.  Do anyone have
> recommendations for hosting services that would be able to provide Opteron
> based hardware and have decent service?  Or are we stuck with Xeons?
> --
> Chris Kratz
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
>

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] UltraSPARC versus AMD

[Richard_D_Levine]

I am looking at options for a customer with an installed base of ~5000 Sun
workstations running 400-500MHz UltraSPARCs.  They're not getting the
performance they need.  They shipped me two Tadpole Bullfrog machines, a
Bullfrog I and a Bullfrog II for evaluation.

http://www.tadpole.com

1.28GHz single or dual CPU UltraSPARCs.  On board SCSI, but they installed
IDE drives instead.

In my *utter* lack of enthusiasm over this option, I was gathering
ammunition for better hardware.  I went to spec.org for speed comparisons,
and sun.com for price comparisons.  Sun's *entry* level servers are more
powerful when running AMD CPUs.

I note with interest and appreciation comments about the bigger iron from
Sun and IBM.  That's not what I'm in the market for, but good info as
always.

My evaluation is that a single or dual core AMD 64 Athlon in a rugged
laptop is going to give a performance enhancement (SPECMark wise) of about
an order of magnitude over their current hardware base.  And it's cheaper.

The current hardware base contains a 10k SCSI Fast Wide Ultra single disk
on a 440MHz CPU as well as a 7200 IDE on a 500MHz CPU.  The SCSI with the
slower CPU runs the application 8% faster.  Obviously I'll need to work on
the proper I/O subsystem because that's apparently more of a limiter than
the CPU speed.

Cheers,

Rick

[EMAIL PROTECTED] wrote on 04/23/2005 11:02:17 AM:

> As someone who works in a nationwide bank, let me tell you why we
> choose IBM and Sun hardware: support. If we want to get a server for a
> project, we can't just go get the most cost-efficient thing out there
> for the job. We have a short list of servers that can be picked from,
> and that's it. A given server makes it onto that list if and only if it
> can be supported by a vendor in a matter of hours for at least 3 years.
> We don't always purchase that support, but bank policy says it has to
> be an option.
>
> We don't generally purchase monster machines. Sure, there are some
> mainframes, but they are few and far between. Everything else doesn't
> really have anything more than 32 procs.
>
> On Apr 23, 2005, at 2:58 AM, William Yu wrote:
>
> > As for why financial/insurance institutions use IBMs or Suns -- I
> > would suggest limited choice is a bigger issue than any specific
> > sub-system performance factor. A nationwide bank doesn't have any
> > choice except to pick a monster 100+ processor machine because
> > anything slower couldn't handle their 20,000 employees. Not many
> > options really. Plus, people in big companies tend to make safe
> > decisions -- get the company with the most name value so you don't get
> > fired if the machine turns out to be a lemon.
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster


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

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

Re: [GENERAL] What does "tuple concurrently updated" mean?

[Tom Lane]

"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> During a batch load of records (using COPY) I got the following
> postgres error today "tuple concurrently updated".

I don't think plain COPY could have produced that, since it only inserts
tuples.  Tell us about triggers and foreign keys on this table?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Karsten Hilbert]

> > >   select f, f::timestamp at time zone 'MEZ' from test;
> > >  f   |  timezone  
> > >   ---+
> > >2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
> > >   (1 row)
> >
> > Huh ? This IS different time zones from the same timestamp
> > within the same query, isn't it ??
> 
> No, it isn't.  In the above example from 7.2.7 the second column
> has no time zone specification whatsoever;
You are correct.

However, how about this:

version
---
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

=> select now() as "MESZ", now() at time zone 'MEZ' as "MEZ", now() at time 
zone 'UTC' as "UTC" ;

  MESZ  |  MEZ   |  UTC   
++
 2005-04-25 17:13:19+02 | 2005-04-25 16:13:19+01 | 2005-04-25 15:13:19+00
(1 row)

Is that eventually it ?

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

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

[GENERAL] DDL from psql console?

[John Browne]

Hello,

I was curious if there was a way to get the DDL for a particular table
from the psql client console?  I have two postgres boxes (development
and production) and would like to copy & paste the DDL "CREATE TABLE"
statements from the development console to the production console when
I'm moving a particular table definition over.  I tried \dt+ but it
didn't appear to show it.

Any thoughts?

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

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

Re: [GENERAL] artificial keys or not?

[Jeff Eckermann]

"Scott Ribe" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>> ..so the only question is:
>>
>> drop the the serials as PKs and just use short text handles? our
>> database size is arround 290 mb. there shouldn't be speed issues as long
>> as proper indexes exit, right? some of the conditions are dynamic
>> though, so there can't be an proper index in any case. any other 
>> concerns?
>
> Someone (a manager) somewhere (probably marketing) sometime (when you 
> least
> expect it) will demand that those short text names be changed, possibly 
> for
> a completely irrational reason. I know there are still text books that 
> claim
> that artificial keys are evil for some inscrutable reason, but hard
> experience has taught me to never, ever, under any circumstance, make a
> primary key out of data that comes from humans, and to be very suspicious 
> of
> using data that will be visible to humans.

This is my experience also.  But opinions vary, and I wouldn't be surprised 
to see a further post from someone which argues the exact opposite. :-)

>
>
> -- 
> Scott Ribe
> [EMAIL PROTECTED]
> http://www.killerbytes.com/
> (303) 665-7007 voice
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>   http://archives.postgresql.org
> 



---(end of broadcast)---
TIP 3: 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] installation as service failed on windows xp

[Akbar]

Hi,

The first time I install postgresql in Windows XP, I install it as
service. The installer asked me to create postgres account. Ok, fine...
then someday I uninstalled postgresql so I can install the newer version
of postgresql. But first to make it clean, I delete the postgres account
from right clicking the My Computer, then click the properties. Choose
the advanced tab, then click the user profiles setting buttons.

Then when I try to install as service with postgres account, it said :
Invalid user name specified: Logon failure: unknown user name or bad
password.

I try to create postgres account from start menu ---> account or control
panel --> user account, it said that The account already exists. But
there is no postgres account in User Accounts window.

So the questions is:
1. How do I really delete the postgres account that created by
postgresql installer???

Thank you.

Regards,

Akbar


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> The problem I was discussing involves getting *different* time zone
> specifictions in the output.  That is, something like this (which
> apparently is possible in 7.1.3 but not in later versions):

>f   |   timezone
> ---+---
>  2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01

It was not really possible in 7.1 either (nor any previous version).
The secret to the above is that in 7.1 the timezone() function's
result is *text*, not a timestamp object.  So it's purely a display
artifact ...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] DDL from psql console?

[Thomas F . O'Connell]

Any reason not to use pg_dump -s?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Apr 25, 2005, at 10:29 AM, John Browne wrote:
Hello,
I was curious if there was a way to get the DDL for a particular table
from the psql client console?  I have two postgres boxes (development
and production) and would like to copy & paste the DDL "CREATE TABLE"
statements from the development console to the production console when
I'm moving a particular table definition over.  I tried \dt+ but it
didn't appear to show it.
Any thoughts?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] DDL from psql console?

[John Browne]

Yeah, I know about pg_dump.  I just was curious if there was another
way, since I always have two psql consoles already open at all times
anyway. :-)

On 4/25/05, John Browne <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I was curious if there was a way to get the DDL for a particular table
> from the psql client console?  I have two postgres boxes (development
> and production) and would like to copy & paste the DDL "CREATE TABLE"
> statements from the development console to the production console when
> I'm moving a particular table definition over.  I tried \dt+ but it
> didn't appear to show it.
> 
> Any thoughts?
>

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

Re: [GENERAL] DDL from psql console?

[Scott Marlowe]

On Mon, 2005-04-25 at 10:29, John Browne wrote:
> Hello,
> 
> I was curious if there was a way to get the DDL for a particular table
> from the psql client console?  I have two postgres boxes (development
> and production) and would like to copy & paste the DDL "CREATE TABLE"
> statements from the development console to the production console when
> I'm moving a particular table definition over.  I tried \dt+ but it
> didn't appear to show it.

I don't think you can get it from within psql, but you can get it with
pg_dump from the command line:

pg_dump -st tablename dbname

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

   http://archives.postgresql.org

Re: [GENERAL] Hosting Service Recommendations

[Steve Atkins]

On Mon, Apr 25, 2005 at 11:04:27AM -0400, Alex Turner wrote:

> I have found that even the larger hosting companies such as Rackspace
> are pretty clueless when trying to configure even a mid size database
> server.  They gave us a 4 drive raid 5 even after being explicitly
> asked for a RAID 10. They also put the controller in write through
> cache mode.  Pretty bad config - the server blew chunks, our single
> drive ATA cheapo box ran significantly faster.
> 
> I would definatley suggest looking at co-lo.

Definitely, yes. The managed facilities tend to be tuned for webserver
usage rather than anything else until you get to the high end.


We use http://www.asaservers.com/ as a build-to-order hardware
vendor. They also offer very reasonably priced colocation. So you can
spec the box you want and they'll build it, install an OS and drop it
in their rack (at a decent colo facility). It makes real colo as painless
to set up as a managed hosting outfit. If you call 'em, ask for Abhi.


> On 4/25/05, Chris Kratz <[EMAIL PROTECTED]> wrote:
> > It seems the consensus on this list is that when running postgres, Opterons
> > outperform Xeons by a significant margin not to mention the CS storms
> > postgres seems to cause on Xeons.  We are looking for a hosting service for 
> > a
> > postgresql based application.  Unfortunately, it seems most services
> > standardize on Dell and by extension Xeon hardware.  Do anyone have
> > recommendations for hosting services that would be able to provide Opteron
> > based hardware and have decent service?  Or are we stuck with Xeons?

Cheers,
  Steve

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

Re: [GENERAL] DDL from psql console?

[Tom Lane]

John Browne <[EMAIL PROTECTED]> writes:
> I was curious if there was a way to get the DDL for a particular table
> from the psql client console?

No.  Try
pg_dump -s -t tablename dbname

regards, tom lane

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

Re: [GENERAL] DDL from psql console?

[Michael Fuhr]

On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote:
>
> Yeah, I know about pg_dump.  I just was curious if there was another
> way, since I always have two psql consoles already open at all times
> anyway. :-)

You could do "\!pg_dump ..."

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

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

Re: [GENERAL] Playing with PostgreSQL and Access VBA

[Jeff Eckermann]

"Tony Caduto" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> In a real application that uses a client/server type database you should 
> NEVER construct queries that return a million rows.
> You have to keep in mind that each record has to be sent accross the wire 
> via TCP/IP and it is going to take a lot of time to bring back 1 million 
> rows and I am sure your network admin would notice increased network 
> traffic.
>
> In a real application you would use something like the Delphi Tdataset 
> based components which will only bring back the records that can be seen 
> on the screen, then when you scroll, it brings back more.
>
> you can manually create something like this using Postgresql handy LIMIT 
> and OFFSET sql keywords in your query to build a paging system.
>
> Access is actually a very poor choice for client/server application 
> development, like I said before, invest some time learning Delphi, it's

Access may be a poor choice for a Delphi developer to use.  But for many of 
us, it's an excellent choice.  Everything depends on your situation and on 
your needs.

The OP is starting from ground zero in learning about Access (or anything, 
apparently) and client-server apps.  Of course there will be difficulty, 
regardless of the choice of interface.

> not difficult at all and you won't have these problems.
> You can get a copy of Delphi 7 personal edition and you can use the zeos 
> components with it.  http://www.zeoslib.net
>
> Tony
>
>> .
>>  When I had it loop and add 1000 rows, it was ok...   When I told it to 
>> add a million rows then after 250,000 the Access application hung 
>> (but not the whole machine, and not the
>>
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 



---(end of broadcast)---
TIP 3: 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] Delphi personal (was Playing with PostgreSQL and Access VBA)

[Ben Trewern]

I'm pretty sure that zeosdbo needs a version of Delphi with TDataset 
support.  I don't think that the Personal editions have that.  You can use 
the direct access parts of Zeos with the personal editions but then you 
might as well use Free Pascal and Lazarus as they have just ported ZeosDbo 
see http://www.lazarus.freepascal.org/.

Ben

"Tony Caduto" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> All you need is the Personal Edition of say Delphi 6 or Delphi 7, and even 
> Delphi 2005.
> For Delphi 2005 personal edition see this link:
> http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake
> Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005 
> can do, and I bet you can find version 5 on ebay or elseware for dirt 
> cheap.
>
> The personal editions do not include database access components by 
> themselves, but you certainly can use third party tools such as Zeos 
> (http://www.zeoslib.net) or those from
> http://www.microolap.com.
> You could even grab a copy of libpq.pas if you want to do direct access to 
> PG.
>
> You get what you pay for and there is nothing available for Python or 
> WXwidgets that even comes close to Delphi for RAD/GUI database 
> development.
> Not to mention all the other stuff you can do with Delphi, it's also a 
> great programming language for console apps, services, TCP/IP servers. 
> There are even remote control applications built with it ala PC Anywhere.
>
>
> Plus if you call borland, I am sure they would give you the upgrade price 
> if you have a old copy of VB laying around for a competive upgrade.
>
>>>  I was very impressed by what one member said regarding Delphi, but, 
>>> when I looked at pricing,... well I would have to lie through my teeth 
>>> to get the cheap academic version, and the personal version sounds like 
>>> it doesnt have the file access abilities for PostgreSQL. And the prices 
>>> for enterprise versions at programmersparadise.com  like $4000, sort of 
>>> puts me off.
>>
>>
>
>
> ---(end of broadcast)---
> TIP 3: 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
> 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] DDL from psql console?

[Bruce Momjian]

Tom Lane wrote:
> John Browne <[EMAIL PROTECTED]> writes:
> > I was curious if there was a way to get the DDL for a particular table
> > from the psql client console?
> 
> No.  Try
>   pg_dump -s -t tablename dbname

Oh, from psql:  :-)  (We really should have an easier way of show all
information from psql)

test=> CREATE TABLE test (x SERIAL);
NOTICE:  CREATE TABLE will create implicit sequence "test_x_seq" for
serial column "test.x"
CREATE TABLE
test=> \! pg_dump -s -t test test
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE test (
x serial NOT NULL
);


ALTER TABLE public.test OWNER TO postgres;

--
-- PostgreSQL database dump complete
--

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] DDL from psql console?

[John Browne]

Actually, that's a thought..  I could even create a bash wrapper
script so I wouldn't have to type the database name each time.  Will
give it a shot.

Thanks

On 4/25/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote:
> >
> > Yeah, I know about pg_dump.  I just was curious if there was another
> > way, since I always have two psql consoles already open at all times
> > anyway. :-)
> 
> You could do "\!pg_dump ..."
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

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

Re: [GENERAL] DDL from psql console?

[Bruce Momjian]

John Browne wrote:
> Actually, that's a thought..  I could even create a bash wrapper
> script so I wouldn't have to type the database name each time.  Will
> give it a shot.
> 
> Thanks
> 
> On 4/25/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> > On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote:
> > >
> > > Yeah, I know about pg_dump.  I just was curious if there was another
> > > way, since I always have two psql consoles already open at all times
> > > anyway. :-)
> > 
> > You could do "\!pg_dump ..."

Also, what is it you want to see that \d doesn't give you?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: 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] timezone() with timeofday() converts the wrong direction?

[Karsten Hilbert]

On Mon, Apr 25, 2005 at 11:36:23AM -0400, Tom Lane wrote:

> > ---+---
> >  2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01
> 
> It was not really possible in 7.1 either (nor any previous version).
> The secret to the above is that in 7.1 the timezone() function's
> result is *text*, not a timestamp object.  So it's purely a display
> artifact ...
Aha, there's the explanation. Thanks, Tom.

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

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

Re: [GENERAL] What does "tuple concurrently updated" mean?

[Florian G. Pflug]

Tom Lane wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
During a batch load of records (using COPY) I got the following
postgres error today "tuple concurrently updated".
I don't think plain COPY could have produced that, since it only inserts
tuples.  Tell us about triggers and foreign keys on this table?
Sorry, I was confused. The error didn't actually happen while copying,
it happened while "analyze"-ing the tables after the import. I
confused this, because the analyze is started from our import-script,
and the log just showed the error-message postgres gave ;-)
So - does "tuple concurrently updated" make sense for "analyze"?
greetings, Florian Pflug



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] What does "tuple concurrently updated" mean?

[Tom Lane]

"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> So - does "tuple concurrently updated" make sense for "analyze"?

Yeah, it's entirely possible, if you have a background process that
might issue analyzes too (do you use autovacuum?).  The error comes when
two sessions concurrently try to update the same row in pg_statistic.
I've looked at preventing it, but the cure seems worse than the disease
--- we don't really want ANALYZE to take any strong locks on the table,
and in any case all you are losing is presumably-near-duplicate stats
from one session or the other.

regards, tom lane

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

Re: [GENERAL] What does "tuple concurrently updated" mean?

[Florian G. Pflug]

Tom Lane wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
So - does "tuple concurrently updated" make sense for "analyze"?
Yeah, it's entirely possible, if you have a background process that
might issue analyzes too (do you use autovacuum?).  The error comes when
two sessions concurrently try to update the same row in pg_statistic.
I've looked at preventing it, but the cure seems worse than the disease
--- we don't really want ANALYZE to take any strong locks on the table,
and in any case all you are losing is presumably-near-duplicate stats
from one session or the other.
Hm... could this be turned into a warning then? I'll fix this
by ignoring db-errors when issuing analyze, but I belive this
will bite more people...
Anyway, thanks for your fast reply - I'll sleep much better,
now that I know the cause for this ;-)
greetings, Florian Pflug


smime.p7s
Description: S/MIME Cryptographic Signature

[GENERAL] unsubscribe

[Wilson, David]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Playing with PostgreSQL and Access VBA

[Tony Caduto]

I guess I was thinking more of the "big picture"
Your right of course, for someone just fooling around at home or for a very 
small business Access is great, but I am saying from experience that Access is
a poor choice for any kind of large project or corp use.

Access may be a poor choice for a Delphi developer to use.  But for many of 
us, it's an excellent choice.  Everything depends on your situation and on 
your needs.

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

[GENERAL] plpgsql function not working

[Ruff, Jeffry C. SR.]

Title: plpgsql function not working






I found this function on line [Thanks to Jeff Eckermann and Juerg Rietmann] that takes the results of a query and creates a comma delimited  string. However when I run it I get no values. Any help would be appreciated. I apologize if this is the wrong forum.

Jeff Ruff

Tycoelectronics Power Systems


Function:

DROP FUNCTION userinfo.group_list(text);

CREATE FUNCTION userinfo.group_list(text) RETURNS text AS'

   DECLARE

    rec RECORD;

    string text := NULL;

   BEGIN

    FOR rec IN SELECT * FROM userinfo.userdb_groups WHERE username = $1 LOOP

   string := string || rec.groupname || \',\';

    END LOOP;

    string := substr(string, 1, length(string)-1);

    RETURN string;

    END;

'LANGUAGE 'plpgsql';


Acts on database listing name userdb_groups


key username    groupname

    === 

1023    jruff       srcadm

1024    jruff       libadm

1025    jruff       mpdev

1026    jruff       systems

1027    jruff       ug

1027    jruff       cadadm

1028    jruff       mppm

1029    jruff       corerd

1030    jruff       weblive

    


Jeff Ruff

Tyco Electronics Power Systems, Inc.

CAD Support Group

phone: 972-284-4267

email: [EMAIL PROTECTED]

Re: [GENERAL] column size in libpq

[John DeSoi]

On Apr 25, 2005, at 4:53 AM, Piotr Filipczuk wrote:
How to determinate column size in query in libpq? Especially when 
column is declared for exapmle as varchar(64).  PQfsize return -1 
which mean that is variable and PQfmod returns 68.
68 - 4 is the correct size for varchar(64). There is 4 bytes of 
overhead included in the return value.

If you are trying to do something like column formatting where you need 
to know the longest length, maybe take a look at the psql source and 
see how it works there.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Calculated bigserial column in a view

[Bruno Wolff III]

Please keep replies copied to the list so that other people can learn from
and comment on the discussion unless to have a good reason to make the
thread private.

On Mon, Apr 25, 2005 at 21:46:20 +0200,
  Zlatko Matic <[EMAIL PROTECTED]> wrote:
> The reason for such crazy idea is my front-end MS Access which considers 
> views as tables and have problem with tables ( views also) if there is no 
> unique numeric field...
> Anyway, how to do it ?
> Thanks.

I don't know MS Access, so I am not going to be able to help much with this.
There may be some way to trick MS Access into thinking that your view has
a unique numeric field. It would help to know how it knows that a column
has those properties and how it uses the information.

> 
> - Original Message - 
> From: "Bruno Wolff III" <[EMAIL PROTECTED]>
> To: "Zlatko Matic" <[EMAIL PROTECTED]>
> Cc: 
> Sent: Monday, April 25, 2005 6:31 AM
> Subject: Re: [GENERAL] Calculated bigserial column in a view
> 
> 
> >On Sun, Apr 24, 2005 at 22:16:14 +0200,
> > Zlatko Matic <[EMAIL PROTECTED]> wrote:
> >>I know that it sounds crazy,
> >>but I need a bigserial coulumn in a view that is consisted of several 
> >>tables.
> >>That column should not be based on bigserial column of any table, but 
> >>should be a calculated column...
> >>How can I accomplish it ?
> >
> >This doesn't really make sense. It sounds like you want a key that is 
> >unique
> >accross the union of several tables and that doesn't change when other 
> >rows
> >in the union are added or removed. There are a couple of ways you might
> >go about doing something like this. Is this what you really want?
> >
> >---(end of broadcast)---
> >TIP 5: Have you checked our extensive FAQ?
> >
> >  http://www.postgresql.org/docs/faq
> >
> 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] plpgsql function not working

[John DeSoi]

On Apr 25, 2005, at 3:40 PM, Ruff, Jeffry C. SR. wrote:
I found this function on line [Thanks to Jeff Eckermann and Juerg 
Rietmann] that takes the results of a query and creates a comma 
delimited  string. However when I run it I get no values. Any help 
would be appreciated. I apologize if this is the wrong forum.
Try changing
string text := NULL;
to
string text := '';
Any text concatenated with NULL results in a NULL value.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] plpgsql function not working

[Michael Fuhr]

On Mon, Apr 25, 2005 at 02:40:29PM -0500, Ruff, Jeffry C. SR. wrote:
>
> CREATE FUNCTION userinfo.group_list(text) RETURNS text AS'
>DECLARE
>   rec RECORD;
>   string text := NULL;
>BEGIN
>   FOR rec IN SELECT * FROM userinfo.userdb_groups WHERE username = $1 LOOP
>string := string || rec.groupname || \',\';

The result of "NULL || anything" is NULL.  Try initializing "string"
to an empty string instead of NULL.

In 7.4 and later you can use an array constructor and array_to_string():

SELECT array_to_string(array(
  SELECT groupname FROM userinfo.userdb_groups WHERE username = 'jruff'
), ',');

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] plpgsql function not working

[John DeSoi]

On Apr 25, 2005, at 4:07 PM, Ruff, Jeffry C. SR. wrote:
Thanks for the reply. After making the change I now get the following
ERROR:  unterminated string
CONTEXT:  compile of PL/pgSQL function "group_list" near line 6
Ok make that
string text := ;
I'm now spoiled with dollar quoting :)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] rollback vs. commit for closing read-only transaction

[David Parker]

If an 
application transaction is known to be read-only, is there any reason to 
prefer COMMIT or ROLLBACK for closing that transaction? Would there be any 
performance difference between the two commands?
- 
DAP--David 
Parker    Tazz Networks    (401) 
709-5130 
 

Re: [GENERAL] rollback vs. commit for closing read-only transaction

[Bruce Momjian]

David Parker wrote:
> If an application transaction is known to be read-only, is there any
> reason to prefer COMMIT or ROLLBACK for closing that transaction? Would
> there be any performance difference between the two commands?

Doesn't matter.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] rollback vs. commit for closing read-only transaction

[Dann Corbit]

Probably, turning fsync off would be helpful, since you know it is
read-only.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Bruce Momjian
> Sent: Monday, April 25, 2005 3:01 PM
> To: David Parker
> Cc: postgres general
> Subject: Re: [GENERAL] rollback vs. commit for closing read-only
> transaction
> 
> David Parker wrote:
> > If an application transaction is known to be read-only, is there any
> > reason to prefer COMMIT or ROLLBACK for closing that transaction?
Would
> > there be any performance difference between the two commands?
> 
> Doesn't matter.
> 
> --
>   Bruce Momjian|  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us   |  (610) 359-1001
>   +  If your life is a hard drive, |  13 Roberts Road
>   +  Christ can be your backup.|  Newtown Square, Pennsylvania
> 19073
> 
> ---(end of
broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]

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

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

Re: [GENERAL] rollback vs. commit for closing read-only transaction

[Tom Lane]

"Dann Corbit" <[EMAIL PROTECTED]> writes:
> Probably, turning fsync off would be helpful, since you know it is
> read-only.

Wouldn't make any difference: a transaction that hasn't modified the
database doesn't bother to write any commit/abort WAL record at all.

regards, tom lane

---(end of broadcast)---
TIP 3: 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] Toad Adm. Datasource Location

[Typing80wpm]

I am just curious to see if I can get Toad to connect to Postgresql.  
I have Access talking to it via ODBC.  Toad is asking for Datasource and 
Location.  I cant figure out what to put there.  I have done some 
searching in google.
 
Thanks for any suggestions.

Re: [GENERAL] Intuitive Software and Mario Brothers

[Rob Stone]

Title: Message




WELL... I did that, and , LO AND  BEHOLD,... there the darn thing 
is now WHO in the name of Heaven would ever dream of going there and 
right clicking?
Maybe it was some bloke called Gates who 
couldn't figure out what to do with a single button mouse. After all, it is 
"intuitive", isn't it?
 
 

  
  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED] Sent: Sunday, April 24, 2005 1:46 
  AMTo: pgsql-general@postgresql.orgSubject: [GENERAL] 
  Intuitive Software and Mario Brothers
  I just had an amusing thought, this morning. You know those old Mario 
  Brothers games, video games , where you have to bounce on everything you 
  see, every rock and tree and flower until you discover the secret 
  place where you get power points, or warp into the next level?
   
  Well. A lot of software is just like that!
   
   
   
  Last night, trying to make a VBA routine to talk to Postgresql 
  through MSAccess with ODBC it is like BOINKING and poking on every single 
  place, nook and cranny until you get it right, by accident.  Come to 
  think of it, I did hours of boinking and poking to get Postgresql windows 
  install to work. (They should just have a few words for dummies like me, 
  saying "Hey, dont be afraid to install this as a windows service, because the 
  uninstall can undo everything,... AND dont think of installing it as the other 
  option, unless you really know what you are doing, and can do the initdb 
  thingey yourself, because it just wont work.)
   
   
   
   
   
  Some guy had a tutorial about Excel and VBA and he says "go here to add a 
  BUTTON" to excel... so... I launch  excel, and look EVERYWHERE,... and 
  nothing in sight,... SO, I go back  to his tutorial and he says "right 
  click ANYWHERE in the task bar, and these options open up"  ...
   
  WELL... I did that, and , LO AND  BEHOLD,... there the darn 
  thing is now WHO in the name of Heaven would ever dream of going there 
  and right clicking?

[GENERAL] Corruption on production system

[Eric B. Ridge]

We've got a PG 7.3.6 installation that just started receiving this  
error:
2005-04-25 19:28:54 ERROR:  Invalid page header in block 1110 of  
the_table_name

We're also unable to dump that table using pg_dump.  Postgres isn't  
actually crashing, so I'm not sure how to get a backtrace out of it.

SELECT version():
 PostgreSQL 7.3.6 on i686-pc-linux-gnu, compiled by GCC gcc  
(GCC) 3.3.2 20031218 (Gentoo Linux 3.3.2-r5, propolice-3.3-7)

(Don't get upset that it says Gentoo -- I built it myself from  
official Postgres sources)

Is page header corruption a known issue with 7.3.6 and should I  
upgrade to 7.3.9 at the same time that I'm restoring this table from  
backup?

Any quick advice will be greatly appreciated!
thanks
eric
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] question about about future 8.1 and IN, INOUT, and OUT parameters

[Michael Fuhr]

On Sun, Apr 24, 2005 at 11:05:57PM -0500, Tony Caduto wrote:
>
> I saw that the recently released pl/java has the ablity to use IN, INOUT, and 
> OUT parameters.
> 
> Is the syntax going to be exactly like it is in Oracle?

I'm not familiar with the Oracle syntax and I don't know how close
the PostgreSQL implementation is to being final, but here's an
example that works with the most recent code from HEAD (8.1devel):

CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$
BEGIN
y := y + 5;
z := x + 5;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

SELECT foo(10, 20);
   foo   
-
 (25,15)
(1 row)

SELECT (foo(10, 20)).*;
 y  | z  
+
 25 | 15
(1 row)

SELECT (foo).* FROM (SELECT foo(10, 20)) AS s;
 y  | z  
+
 25 | 15
(1 row)

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] Corruption on production system

[Alvaro Herrera]

On Mon, Apr 25, 2005 at 08:24:03PM -0400, Eric B. Ridge wrote:

> Is page header corruption a known issue with 7.3.6 and should I  
> upgrade to 7.3.9 at the same time that I'm restoring this table from  
> backup?

Well, you can get a page header corruption on any release as long as you
have faulty hardware ... RAM randomly dropping bits is not unheard of.
Have you run memtest?

If you don't mind losing that page of data, you could turn on
zero_damaged_pages on postgresql.conf.  If you do, you could try
pg_filedump (to be found somewhere on sources.redhat.com/rhdb IIRC) to
examine the damaged page more closely.

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

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

Re: [GENERAL] Corruption on production system

[Eric B. Ridge]

On Apr 25, 2005, at 9:42 PM, Alvaro Herrera wrote:
Well, you can get a page header corruption on any release as long  
as you
have faulty hardware ... RAM randomly dropping bits is not unheard of.
Have you run memtest?
No doubt.  :)  What I was asking was if 7.3.9 (or .8 or .7) had a  
known issue that caused page corruption and if upgrading would fix  
that bug.  Regardless, I went ahead and upgraded.

eric
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] UltraSPARC versus AMD

[Mike Mascari]

[EMAIL PROTECTED] wrote:
In my *utter* lack of enthusiasm over this option, I was gathering
ammunition for better hardware.  I went to spec.org for speed comparisons,
and sun.com for price comparisons.  Sun's *entry* level servers are more
powerful when running AMD CPUs.
Just in case people still hold a bias against CISC processors capable of 
running x86 code as necessarily inferior to more expensive 64-bit RISC 
processors, in spite of the overwhelmingly obvious specint results to 
the contrary, I'd like to offer up this little baby:

http://www.cray.com/products/xt3/index.html
Something everyone should have in their office...
Mike Mascari
---(end of broadcast)---
TIP 3: 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] pgexplorer adm

[Typing80wpm]

I went to http://www.pgexplorer.com 
and downloaded their adm program for postgresql running on windows.  The 
site proclaims that it is now FREE, yet there is a moment of confusion when you 
go to install and it talks about 30 evaluation period. Yet, once you install, if 
you click on the REGISTER button, it tells you that this is a FREE copy.  
Installed and ran with no effort on my part.  I think I shall try this out 
for a while.  I have yet to get Toad to run because I cant figure out what 
it wants me to fill in in the odbc dsn setup for datasource and location. But I 
imagine that pgexplorer.com will work ok for now. But thanks everyone for your 
suggestions.
 
I am still shopping around for a language to use for front end.  I 
suppose that Access will do a lot for me.  I feel very comfortable with 
Liberty Basic, but it does not offer any easy way to do 
odbc connections.  I am looking at Truebasic and Powerbasic.  I 
really wish that there were some knd of easy to use IDE window painter like 
Powerbuilder used to be.  But, I guess if I use MSAccess, and VBA, then I 
shall be learning some useful skills.  I realize the virtues of Delphi, but 
I just dont feel like attempting it at this stage of the game.  But, at 
least, each week, I am making a little more progress, and learning some 
interesting things.
 
Is there some kind of drag and drop Java ide painter which would be fairly 
straight forward, without having to become an expert in Java, just 
curious.  I know nothing about Java.  I tried out a free Cincom 
Smalltalk, which has a nice tutorial, but I have not seen a tutorial which walks 
you through doing a menu and windows for a SQL application.  
 
 

[GENERAL] pgadminIII - creating servers

[Typing80wpm]

I just now tried creating a second server with 
pgadminIII.    I am confused as to why one would have more than 
one server.  There does not seem to be any way to "switch" from one server 
to another, or to log into one particular server (and not the other), or to 
create tables specifically for one server. I find the concept rather 
confusing.  I understand that if I limit myself to one server, then I may 
define different database, and log to a particular database, and see only the 
tables in that database.  Any explanation regarding more than one server 
will be appreciated. Thanks.

[GENERAL] I can't create a new database with GB18030 encoding

[Huang GeHua]

I want create a new database with the GB18030 encoding.But the
pgAdminIII tell a error "GB18030 is not a valid encoding name".How can
i do for it?

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

Re: [GENERAL] Migrating MySQL app to postgres?

[Joe Healy]

Hi Rich,
  Guess now's the time. I'm trying to find a copy of pygresql to use, but
it's not easy (www.pygresql.org leads to druid.net and there's nothing 
on the
page about pygresql). I'm learning python to use on a major project 
here (but
the core libraries will remain in C) so I'd like to use that for the 
UI on
this project, too. I'm still not sure about the reports, but one step 
at a
time. That is, will the pygresql display nicely formatted reports 
based on
sql queries to the backend? Need to find out.
I have found using python with reportlab 
(http://www.reportlab.org/rl_toolkit.html)
very easy to create pdf reports based on queries.

Hope this helps,
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Postgres source (tar file) for Fedora Core OS?

[Dinesh Pandey]

From where can I download latest Postgres source (tar
file) for Fedora Core OS?

Regards
Dinesh Pandey

 

Re: [GENERAL] question about about future 8.1 and IN, INOUT, and OUT parameters

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Sun, Apr 24, 2005 at 11:05:57PM -0500, Tony Caduto wrote:
>> Is the syntax going to be exactly like it is in Oracle?

> I'm not familiar with the Oracle syntax and I don't know how close
> the PostgreSQL implementation is to being final, but here's an
> example that works with the most recent code from HEAD (8.1devel):

> CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$

FWIW, we will also take the more Oracle-ish spelling of the parameter
list:

CREATE FUNCTION foo(x IN integer, y IN OUT integer, z OUT integer) AS ...

... although given all the other in-detail discrepancies between plpgsql
and Oracle's pl/sql, I'm not sure how much this will really make
anyone's life easier.  In any case, Michael's example is the preferred
syntax because it is what the SQL spec calls for.

regards, tom lane

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

Re: [GENERAL] I can't create a new database with GB18030 encoding

[Tom Lane]

Huang GeHua <[EMAIL PROTECTED]> writes:
> I want create a new database with the GB18030 encoding.But the
> pgAdminIII tell a error "GB18030 is not a valid encoding name".How can
> i do for it?

The docs say that GB18030 is supported as a client-side encoding only.
So pick any superset encoding (UTF8 maybe?) as the database encoding,
and then set client_encoding to GB18030.

regards, tom lane

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

Re: [GENERAL] Toad Adm. Datasource Location

[Guy Rouillier]

[EMAIL PROTECTED] wrote:
> I am just curious to see if I can get Toad to connect to Postgresql. 
> I have Access talking to it via ODBC.  Toad is asking for Datasource
> and Location.  I cant figure out what to put there.  I have done some
> searching in google.   
> 
> Thanks for any suggestions.

Every version of Toad I've ever used connects to Oracle using the OCI
driver.  You'll never get the OCI driver talking to PG - completely
different communication protocol (well, never say never, but not today
anyway...)

-- 
Guy Rouillier

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] optimal hardware for postgres?

[Guy Rouillier]

William Yu wrote:

> On other note -- if you are thinking about SMP Opteron, you may
> actually get better performance from 1x275 (Dual Core 2.2ghz) versus
> 2x248 (2.2ghz). Full duals have twice the bandwidth but without good
> NUMA support, memory has to be interleaved between CPUs.

I thought the 2.6 kernel had good NUMA support?  (I realize I just made
an assumption that the original poster was running PG on Linux 2.6.)

-- 
Guy Rouillier


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

Re: [GENERAL] optimal hardware for postgres?

[Alex Turner]

The first thing you need to do is assess where your bottleneck is
going to be.  Is it RAM? or CPU or IO?  If your system is IO bound,
you are better off spending your $s on a good IO controller and lots
of drives that racking up the $$s with Expensive Opteron 275s or 250s,
when a 242 would do the job just fine for les than $200 ea.

The Tyan S2885 mobo is a good chioce for a rig, it's not too expense,
and has 2 independant PCI buses if you want to run two seperate
controlers, which if you are going to run both database and webserver
on one box I would heartily recommend doing.  Run the webserver on a
RAID 1 on the first controler with WAL on another RAID 1, then run
your DB on the other controller with a 4 or 6 drive RAID 10 (This is
what you might want to look at if you are IO bound).

This kind of server can be built for $4-7k in either SCSI or SATA
configs depending on your preference/budget/size requirements.  SCSI
is a bit more, but you get better performance, SATA raptor has good
performance, and gives you more space for your dollar if you don't
mind going SATA (plus if you use multi-lane with 3ware controller you
get a reasonable number of cables instead of spaghetti junction).

Don't bother with Xeon, the performance has been shown to be pathetic
relative to Opteron for Postgresql and also not as cost effective.

Check out the size of your data set, and your search requirement.  The
more data you can cache in RAM the better off you will be, and the
quicker you will move the bottleneck from IO to RAM/CPU (It looks like
you will be read intensive not write from your comments).  If you need
more RAM, put it in as it can save on the disk cost, and you can go
with a generic 4U chasis instead of an expensive chasis with backplace
built in.  Antec has a nice 4U that has 8 drive bays that will fit
2x3U 4 drive modules for 8 drives total plus room for a CD ROM to
spare.  If you need more than this check out rackmountmart.com.  They
have a nice 4U and 5U chasis that has lots of space for drives.

Thats my 0.02

Alex Turner
netEconomist

On 4/24/05, peter pilsl <[EMAIL PROTECTED]> wrote:
> 
> I'm just going to buy a new server which will mainly run a
> postgreSQL-database and a apache2 with mod_perl and do little more than
> deliver loads of index-based select-queries. But it will have to do a
> lot of them. And it will be master for a replication to slaves.
> As always of course we dont know how many selects we'll have to face.
> There will be "many" and the current Athlon1800+ with 1GB Ram is too
> slow. (to its excuse : It has to perform loads of other tasks as well)
> 
> I was now wondering which hardware would fit this server best. I think
> about 2GB RAM, a fast Raid1 and now I'm not sure about the CPU.
> I was considering 64-bit AMD : A Athlon 64 FX-51 or a Opteron 2.2GHz.
> The hosting system will be a 64-Bit linux.
> 
> Does postgres benefit from 64-bit AMD's or would I be better off with a
> cheaper AthlonXP or even with a Pentium or a more expensive Xeon?
> Or is my question faulty at all, cause CPU is only 20% of the whole system.
> 
> Any comments appretiated,
> thnx,
> peter
> 
> --
> mag. peter pilsl
> goldfisch.at
> IT-management
> tel +43 699 1 3574035
> fae +43 699 4 3574035
> [EMAIL PROTECTED]
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>

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

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

Re: [GENERAL] Migrating MySQL app to postgres?

[Alex Turner]

Be warned that if you use python, expect it to be slow.  Many basic
associative array operations are more than 10x slower than C++'s
comparative operations leading to long page execution time if you are
dealing with alot of data.  I have written a simple web framework in
Python and XML, and it's a nice language, but it is slow in places.  I
should mention that string manipulation in python is faster than the
C++ string class though.

I can't stand PHP anymore because they just keep changing it
underneath me, and I can't do anything about it.  I'm one frustated
X-PHPer.  Moving all to mod_python and taking on the chin with the
performance hit.

Alex Turner
netEconomist

On 4/22/05, Rich Shepard <[EMAIL PROTECTED]> wrote:
> On Fri, 22 Apr 2005, Scott Marlowe wrote:
> 
> > I like Python, but it's felt half-finished for quite some time now. So, I
> > use PHP and / or Java (just learning it really) for most stuff.
> >
> > PHP, for all it's warts and blemishes, has been a stable language (not
> > the definition, the VM / implementation) with usable connect libs for
> > quite some time.
> >
> > Ruby on Rails has promise, but also feels like it's really just getting
> > started.
> 
>I don't make my living writing software so I'm neither a language bigot nor
> a language collector. If it works, it's good enough. :-)
> 
> Thanks,
> 
> Rich
> 
> --
> Dr. Richard B. Shepard, President
> Applied Ecosystem Services, Inc. (TM)
>    Voice: 503-667-4517   Fax: 503-667-8863
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>

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