Re: hardware checks (was Re: [GENERAL] invalid memory alloc request size)

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> Janning Vygen <[EMAIL PROTECTED]> writes:
> > one more question: You mentioned standard disk and memory checks. Can you 
> > point to some link where i can find more about it or which software do you 
> > mean? I guess i have to start looking at it.
> 
> The stuff I've heard recommended is memtest86 for memory checks and
> badblocks for disk checks.  But perhaps someone on the list has better
> ideas.

I second memtest86, though even the author says memory errors can be tricksy
things. Sometimes a large compile finds memory errors that even memtest86
doesn't find (the symptom is gcc crashing).

However I fear using badblocks alone is pretty useless these days. Modern IDE
drives detect bad blocks and remap them to other locations. If you just use
badblocks you'll see mysterious errors that disappear or might not see any
errors at all. You need to use tools like smartctl to query the drive's SMART
firmware about errors. It's not easy to interpret but if you watch the numbers
for a while you can tell if a drive is going bad and continually remapping bad
blocks. badblocks is useful still as a way of ensuring that every block is
read and written to, but then you have to look at the SMART data to see what
happened.

-- 
greg


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

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

Re: [GENERAL] FATAL: terminating connection due to administrator command

[surabhi.ahuja]

Title: RE: [GENERAL] FATAL: terminating connection due to administrator command 






The exact message i saw is this:

LOG:  received fast shutdown request
LOG:  aborting any active transactions
FATAL:  terminating connection due to administrator command

so does this mean that someone is trying to stop postmaster by sending it a kill signal?

i also have these questions:

1. many times i have seen two instances of postmaster running. how does that happen and how to prevent it from happening?

2. into the logfile (which i specify at the time of starting postmaster), i want to add timestamps, for each log/warning etc. How do i do that?

thanks,
regards
Surabhi

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Fri 1/13/2006 8:56 PM
To: surabhi.ahuja
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] FATAL: terminating connection due to administrator command

***
Your mail has been scanned by iiitb VirusWall.
***-***


"surabhi.ahuja" <[EMAIL PROTECTED]> writes:
> why is it coming:
> FATAL:  terminating connection due to administrator command

Something sent the backend a SIGTERM signal.  With no more information
than that, it's difficult to say more.

            regards, tom lane

Re: [GENERAL] Does this look ethical to you?

[Marc G. Fournier]

the blue links at the top (as it states in the top right) are sponsor'd 
links ... they pay for those to be there, and, I'd imagine, pay quite 
heavily :(




On Mon, 23 Jan 2006, Tony Caduto wrote:


George Pavlov wrote:

they have the same kind of page setup for pg Admin:

http://pgsql.navicat.com/PG_Admin/index.php

this one renders...

both pages seem to tell robots not to cache them, so can't view a cached
view on google.



At least PG Admin is free software and doing that is not really hurting 
anyone since (at least I don't think) no one is making money off of PG Admin 
III.


I do mention my product as a PG Admin III alternative but I would never use 
Navicat or EMS PG Manager by name anywhere on my site. I use words like 
"Other Companies" etc.


I would like to know how they get such good placment on all the search 
engines, you do a search on "Postgresql GUI admin" and all you get on the 
first page is their stuff.


Later,

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

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

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




Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

Re: [GENERAL] Does this look ethical to you?

[Tony Caduto]

George Pavlov wrote:

they have the same kind of page setup for pg Admin:

http://pgsql.navicat.com/PG_Admin/index.php

this one renders...

both pages seem to tell robots not to cache them, so can't view a cached
view on google.



At least PG Admin is free software and doing that is not really hurting 
anyone since (at least I don't think) no one is making money off of PG 
Admin III.


I do mention my product as a PG Admin III alternative but I would never 
use Navicat or EMS PG Manager by name anywhere on my site. I use words 
like "Other Companies" etc.


I would like to know how they get such good placment on all the search 
engines, you do a search on "Postgresql GUI admin" and all you get on 
the first page is their stuff.


Later,

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

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

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

Re: [GENERAL] Does this look ethical to you?

[Joshua D. Drake]

I don't know about that, the link is still there, it just gives a 
error of No input file specified, which just indicates the index.php 
in http://pgsql.navicat.com/PG_Lightning_Admin/ is gone or having a 
problem.


I asked them nicely to remove it within 24 hours, so we shall see.



Well the could just have a generic url rewriter


Thanks again for the input.




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

  http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?

[Tony Caduto]

[EMAIL PROTECTED] wrote:
 >

Well, since the page is gone already I'd say both parties seem to be
playing fair.

Good deal!

brew


I don't know about that, the link is still there, it just gives a error 
of No input file specified, which just indicates the index.php in 
http://pgsql.navicat.com/PG_Lightning_Admin/ is gone or having a problem.


I asked them nicely to remove it within 24 hours, so we shall see.

Thanks again for the input.

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

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

Re: [GENERAL] Does this look ethical to you?

[George Pavlov]

they have the same kind of page setup for pg Admin:

http://pgsql.navicat.com/PG_Admin/index.php

this one renders...

both pages seem to tell robots not to cache them, so can't view a cached
view on google.


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

   http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?

[brew]

Tony.

> Maybe if you take the high road you could work something out that would
> help you both?

Well, since the page is gone already I'd say both parties seem to be
playing fair.

Good deal!

brew

 ==
  Strange Brew   ([EMAIL PROTECTED])
  Check out my Stock Option Covered Call website  http://www.callpix.com
 and my Musician's Online Database Exchange http://www.TheMode.com
 ==


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

   http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?

[Tony Caduto]

Bruce Momjian wrote:



Oh, I thought they were shipping PG lighening admin too.  The URL no
longer works so I wonder if they thought better of the practice.



No, PG Lightning Admin is my product name, and I think they are doing 
something with a link to my page in their page because my page is down 
now also , something is really fishy with Navicat.


Seems odd that my ISP is down and their page has a input problem...hmmm

I did send them a email about it about 1/2 hour ago, so maybe they are 
in the process of fixing this issue.


Thanks for your input on this guys.

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

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

Re: [GENERAL] Does this look ethical to you?

[brew]

Tony.

> I was doing a search on Google and found this link on Navicat's web page
>
> http://pgsql.navicat.com/PG_Lightning_Admin/index.php
>
> I am kind of ticked off that they are hijacking my product name this way.

Well, technically they aren't hijacking your product name, they are
hijacking some of your potential users by putting your product name in the
page title and page url and saying they have a replacement that's better.

It sucks.

However, there are some positives.

They didn't put your product name in the page keywords.

It makes them look bad, sort of like when politicians sling mud.  I make
it a point NEVER to vote for a candidate that does that.  (Obviously I
vote for a lot of Independents).

They are giving your product some extra credence by even using it as a
measuring stick to theirs.

But it still sucks..

Occasionally I find somebody stealing my Musicians Classified Listings and
I've had the same ticked off feeling.  In the long run I just grin and
take it (and do what I can to make it harder for other sites to steal
listings, but I can't think how you could do something like that in this
situation).

Maybe if you take the high road you could work something out that would
help you both?

Well, good luck with it

brew

 ==
  Strange Brew   ([EMAIL PROTECTED])
  Check out my Stock Option Covered Call website  http://www.callpix.com
 and my Musician's Online Database Exchange http://www.TheMode.com
 ==


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

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

Re: [GENERAL] Does this look ethical to you?

[Tony Caduto]

Joshua D. Drake wrote:

Tony Caduto wrote:




I would call it fairly shady. I am guessing that by doing so they are 
getting placement within search

engines or something.


Hi Joshua,
Yep, that's the part that I don't like.
Using Postgresql in there is one thing as it's good because you have to 
have Postgresql in order to use any admin program.


I once by mistake used Instant Messenger in a title on one of my pages 
and had links that had Instant Messenger and AOL had a cow and made me 
remove the links.   I don't see how Navicat using PG Lightning Admin in 
their links and title is any different.


They must be threatend by my product :-)

I did ask them nicely to remove the link, we shall see.

Thanks for the input.


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

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

Re: [GENERAL] Quoted NULLs with COPY FROM

[George Pavlov]

> > I need to load CSV files that have quotes in data fields 
> > that I want to
> > map to NULLs in the destination table. So if I see 
> > ...,"",... that needs
> > to be mapped to a NULL (in an INTEGER field in this 
> > particular case).
> > Are there any COPY command options that can do that? It 
> > seems that PgSQL
> > COPY expects the NULL to be always unquoted. There is an 
> > option (FORCE
> > NOT NULL) for doing the opposite. How do I specify that the 
> > NULLs are quoted? I am on 8.0.5.
> 
> Did you try NULL AS ''?

yes i did. that is the default and does not change the outcome (same
errors about trying to insert an empty string into a numeric field.

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

Re: [GENERAL] Does this look ethical to you?

[Bruce Momjian]

Joshua D. Drake wrote:
> Bruce Momjian wrote:
> > Tony Caduto wrote:
> >   
> >> I was doing a search on Google and found this link on Navicat's web page
> >>
> >> http://pgsql.navicat.com/PG_Lightning_Admin/index.php
> >>
> >> I am kind of ticked off that they are hijacking my product name this way.
> >>
> >> This isn't really postgresal related but I was just wondering what 
> >> others thought about this.
> >> 
> >
> > I don't have a problem with it, especially since they mention it is
> > their version of PostgreSQL (they use our name).
> >   
> I might be misunderstanding your point Bruce but I believe Tony's 
> problem is not that they are using
> PostgreSQL's name but they are using Pg Lightning in their name which is 
> Tony's product not
> Navicats..
> 
> Think about as if Command Prompt would to start using SRAAPowergres in 
> their URL's..
> 
> At least that I how I read it.

Oh, I thought they were shipping PG lighening admin too.  The URL no
longer works so I wonder if they thought better of the practice.

-- 
  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 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Does this look ethical to you?

[Joshua D. Drake]

Bruce Momjian wrote:

Tony Caduto wrote:
  

I was doing a search on Google and found this link on Navicat's web page

http://pgsql.navicat.com/PG_Lightning_Admin/index.php

I am kind of ticked off that they are hijacking my product name this way.

This isn't really postgresal related but I was just wondering what 
others thought about this.



I don't have a problem with it, especially since they mention it is
their version of PostgreSQL (they use our name).
  
I might be misunderstanding your point Bruce but I believe Tony's 
problem is not that they are using
PostgreSQL's name but they are using Pg Lightning in their name which is 
Tony's product not

Navicats..

Think about as if Command Prompt would to start using SRAAPowergres in 
their URL's..


At least that I how I read it.

Sincerely,

Joshua D. Drake



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

Re: [GENERAL] Does this look ethical to you?

[Joshua D. Drake]

Tony Caduto wrote:

I was doing a search on Google and found this link on Navicat's web page

http://pgsql.navicat.com/PG_Lightning_Admin/index.php

I am kind of ticked off that they are hijacking my product name this way.

This isn't really postgresal related but I was just wondering what 
others thought about this.
Well IMHO it is PostgreSQL related as you are both offering a PostgreSQL 
product. It is not
uncommon to do something like this as a keyword on Google Adwords or 
something but

to place a direct URL

I would call it fairly shady. I am guessing that by doing so they are 
getting placement within search

engines or something.

Sincerely,

Joshua D. Drake




Thanks,




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

Re: [GENERAL] Does this look ethical to you?

[Bruce Momjian]

Tony Caduto wrote:
> I was doing a search on Google and found this link on Navicat's web page
> 
> http://pgsql.navicat.com/PG_Lightning_Admin/index.php
> 
> I am kind of ticked off that they are hijacking my product name this way.
> 
> This isn't really postgresal related but I was just wondering what 
> others thought about this.

I don't have a problem with it, especially since they mention it is
their version of PostgreSQL (they use our name).

-- 
  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: Have you checked our extensive FAQ?

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

Re: [GENERAL] Quoted NULLs with COPY FROM

[Bruce Momjian]

George Pavlov wrote:
> I need to load CSV files that have quotes in data fields that I want to
> map to NULLs in the destination table. So if I see ...,"",... that needs
> to be mapped to a NULL (in an INTEGER field in this particular case).
> Are there any COPY command options that can do that? It seems that PgSQL
> COPY expects the NULL to be always unquoted. There is an option (FORCE
> NOT NULL) for doing the opposite. How do I specify that the NULLs are
> quoted? I am on 8.0.5.

Did you try NULL AS ''?

-- 
  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 5: don't forget to increase your free space map settings

[GENERAL] Does this look ethical to you?

[Tony Caduto]

I was doing a search on Google and found this link on Navicat's web page

http://pgsql.navicat.com/PG_Lightning_Admin/index.php

I am kind of ticked off that they are hijacking my product name this way.

This isn't really postgresal related but I was just wondering what 
others thought about this.


Thanks,

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

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

[GENERAL] Quoted NULLs with COPY FROM

[George Pavlov]

I need to load CSV files that have quotes in data fields that I want to
map to NULLs in the destination table. So if I see ...,"",... that needs
to be mapped to a NULL (in an INTEGER field in this particular case).
Are there any COPY command options that can do that? It seems that PgSQL
COPY expects the NULL to be always unquoted. There is an option (FORCE
NOT NULL) for doing the opposite. How do I specify that the NULLs are
quoted? I am on 8.0.5.

George



---(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] mac os x compile failure

[Michael Glaesemann]

On Jan 22, 2006, at 3:10 , Tom Lane wrote:


That's odd --- AFAIR I've never had trouble building bison on my OS X
laptop.  What happens when you try?


I neglected to take any notes. I remember it was complaining about  
"muscle" something-or-other during make.


One workaround would be to check out from CVS and then drop in the  
bison

output files from the 8.1.2 release tarball.


I'll give that a shot when I get back to that machine, probably this  
coming weekend.


Michael Glaesemann
grzm myrealbox com




---(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] Installing Postgres 8.1 on Windows Server 2003 R2

[Carl Conard]

I've successfully installed Postgres 8.1.2 on WS 2003 R2 on 
a Lenovo (IBM) ThinkPad.  I used the default installation options and 
everything seems peachy keen for a single user (using localhost).  

 
However, when we started performance testing 
Postgres (vs. MySQL) using a 3rd party tool (I forget the name) running on 
the server, Postgres would drop connections after about the 10th or 11th virtual 
user (of 20) hit the DB.  The remaining user processes 
would remain visible in Window's Task Manager and just "hang."  We've 
used the same tool, code, DB model, etc. on the same machine using 
MySQL and have gotten well over 100 virtual users with no issues.  Try as I 
might, I can't find anything that might indicate a cause for Postgres to drop or 
lock out the connections.
The app is written in PHP using PEAR DB class for DB access.  
Running PHP 5.1.1, Postgres 8.1.2, latest and greatest WS 2003 R2 updates.  The app is read/write 
intensive.
 
Any help in getting us migrated from MySQL to Postgres 
(which, BTW, is showing about 50% performance gain over MySQL!) would be MORE 
than appreciated...
 
Carl


From: Postgres User 
[mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 6:55 
PMTo: Magnus Hagander; 
pgsql-general@postgresql.orgSubject: Re: Installing Postgres 8.1 on 
Windows Server 2003 R2

That's what I was afraid of... it's a new install of Win Server 2003 R2, so 
I can rule out any third party firewall. Windows Firewall is NOT 
installed.  And I've installed Postgres on a Windows XP 
box behind the same router, so it's not a router-firewall issue. 
 
It's probably a new R2 feature, I'll ping someone at Microsoft 
because I don't have a clue at this point what the problem is...
 
Jon  
On 1/8/06, Magnus 
Hagander <[EMAIL PROTECTED]> 
wrote: 
> 
  Has anyone tried to install Postgres on Windows Sever 2003> version 
  R2?  R2 is actually shipping as a 'new' Microsoft > product- 
  it's basically an interim update to Windows Server (> http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.msp> 
  x  
  ).Not that I know of, but it's been on my list of things to try :-) 
  > I've installed Postgres on other versions of Windows with 
  no> problem, so I'm afraid that the error I'm seeing now is> 
  related to some great new 'feature' from Microsoft.  Here's> 
  the error message returned by Postgres before install begins- > Error 
  binding the test network socket: 10013That error means: An attempt was 
  made to access a socket in a wayforbidden by its access 
  permissions.> Microsoft Antispyware has been turned off (closed the 
  > application) and Windows Firewall isn't running.  There's 
  no> other AV or firewall software on this system yet.>> 
  Any ideas on what might be going on?It certainly *sounds* a lot like a 
  firewall issue :-) I'd double and triple check that. We've seen it several 
  times before and it has AFAIKalways been a 
  firewall.//Magnus

Re: hardware checks (was Re: [GENERAL] invalid memory alloc request

[Terry Fielder]

I second Tom:

badblocks and memtest86 are what I use and works great on all kinds of 
hardware.  You don't even need a specific OS for memtest86 because you 
can make a bootable floppy and test any old piece of hardware it recognizes.


Terry


--
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

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

[Jimmy Rowe]

unsubscribe


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

hardware checks (was Re: [GENERAL] invalid memory alloc request size)

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
> one more question: You mentioned standard disk and memory checks. Can you 
> point to some link where i can find more about it or which software do you 
> mean? I guess i have to start looking at it.

The stuff I've heard recommended is memtest86 for memory checks and
badblocks for disk checks.  But perhaps someone on the list has better
ideas.

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] invalid memory alloc request size

[Janning Vygen]

Am Montag, 23. Januar 2006 21:57 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> > ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can?
>
> Up to you --- you have more risk of compatibility issues if you do that,
> whereas within-branch updates are supposed to be painless.  Depends
> whether you have the time right now to deal with testing your applications
> against 8.1.

ok, i will think about it.

one more question: You mentioned standard disk and memory checks. Can you 
point to some link where i can find more about it or which software do you 
mean? I guess i have to start looking at it.

kind regards,
janning


---(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] invalid memory alloc request size

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
>> Hmm ... the one part of that that jumps out at me is plperl.  We already
>> know that plperl can screw up the locale settings; I wonder whether
>> there are other bugs.  Anyway, if you are using plperl I *strongly*
>> recommend updating to the latest PG release ASAP (8.0.6 in your case).

> ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can?

Up to you --- you have more risk of compatibility issues if you do that,
whereas within-branch updates are supposed to be painless.  Depends
whether you have the time right now to deal with testing your applications
against 8.1.

regards, tom lane

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

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

Re: [GENERAL] invalid memory alloc request size

[Janning Vygen]

TOM! Ich will ein Kind von Dir!!
(it means 'something like': thank you so much. you just saved my life!)

Am Montag, 23. Januar 2006 21:16 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> >> OK, what's the schema of this table exactly?
> >
> > ...
> > Regeln:
> > cache_stip_delete AS
> > ON DELETE TO spieletipps DO  UPDATE tsptcache SET tc_cache = -2
>>  [...]
> 
> Oh, I should have thought of that: the bare DELETE operation doesn't
> care what's in the tuple, but this ON DELETE rule sure does.  That's
> why the delete crashes, it's trying to extract the field contents so
> it can execute the rule.

I dropped the rule and deleted the row successfully with the ctid. Thanks a 
lot for the great support! This problem will be my first article in my 
PostgreSQL Troubleshooting Guide for Dummies. "We" really need it for guys 
like me. 

> > yes, they should both be "alteheide". Is it possible to open the file and
> > just fix the bit?
>
> Yeah, if you have a suitable hex editor.  You'll probably need to shut
> down the postmaster first, as it may have a cached copy of the page.

i decided not to poke to postgres internal file storage.

> > I have no clue, why it happens. But i changed my schema a few month
> > ago to use a materialized view (You see all the rules in this schema
> > above). i need some complicated ranking algorithm to calculate the
> > materialzed view. everything is implemented inside postgresql with
> > rules and functions (pgperl and plpgsql). One more aspect are temp
> > tables to me. I use lots of them for a specific tasks (reusing the
> > calculating algorithm mentioned above for a different data view). With
> > lots of temp tables i got problems with pg_type where some old temp
> > values reside and i got to delete some of them manually a few times
> > per month.
>
> Hmm ... the one part of that that jumps out at me is plperl.  We already
> know that plperl can screw up the locale settings; I wonder whether
> there are other bugs.  Anyway, if you are using plperl I *strongly*
> recommend updating to the latest PG release ASAP (8.0.6 in your case).

ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can?

> If you cannot, at least make sure the postmaster is launched with the
> same LC_XXX settings in its environment as are embedded in the database.

i will look at it! 

kind regards
janning


---(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] Linux - postgres RAID

[Rick Gigger]

Thanks!  That's just the sort of info I am looking for.

I am definitely going with the fastest scsi drives I can get.   
Probably a 6 or 8 disk system.  Is there a huge jump between using 4  
and 6 drives, or 6 and 8 drives?


On Jan 23, 2006, at 12:42 PM, Steve Atkins wrote:



On Jan 23, 2006, at 11:13 AM, Rick Gigger wrote:

I figure this would be a good place to ask. I want to build / buy  
a new linux postgres box.  I was wondering if anyone on this list  
had some experience with this they'd like to share.  I'm thinking  
somewhere in the $7k - 15k range.  The post important things are  
write speed to the disk and good linux driver support for the raid  
card.  Can anyone recommend a specific raid card / server vendor?


It'll depend on what you're planning on using it for. Performance  
requirements, capacity

and so on.

For fast writes you'll probably want something like RAID10 with  
lots of spindles. SCSI
is likely to be somewhat faster, but a lot more expensive. For SATA  
RAID most people
like the 3ware and Areca cards. I'm using this system, from ASA,  
for pretty much that
and it seems OK. 5U server, 24 external bay, 2 internal bay  
capacity. And it's opterons,
which seem to be a bit more PG-friendly than Xeons. It uses the  
3ware 9550SX card.
(The general opinion here seems to be that the Areca cards are  
somewhat "better"

than the 3wares, but a lot more expensive).

http://www.asaservers.com/config_system.asp?config_id=5USATA

Cheers,
  Steve


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




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

  http://archives.postgresql.org

Re: [GENERAL] Linux - postgres RAID

[Carlos Moreno]

Rick Gigger wrote:

I figure this would be a good place to ask. I want to build / buy a  
new linux postgres box.  I was wondering if anyone on this list had  
some experience with this they'd like to share.  I'm thinking  
somewhere in the $7k - 15k range.  The post important things are  
write speed to the disk and good linux driver support for the raid  
card.  Can anyone recommend a specific raid card / server vendor?



Instead of (or in addition to) RAID configurations, you may want to
consider using multiple disks, connected to independent channels
(not a problem if you're talking SATA), such that PG can perform
simultaneous access to the various filesystems.  I'm not sure which
parts are critical, or how many different partitions you would need
for optimal performance, but the "PG Performance" mailing list
archives should prove useful to find out about this.

Maybe you could use SATA Raptor drives -- the 10k RPM, which
I believe has a sustained transfer rate in the order of 80 or 90 MB/sec.

Do make sure that you get a MB with fast internal bus (533 or 800
MB/sec at least), so that you don't waste your money on multiple
independently-connected hard drives that hit a bottleneck when the
data reaches the motherboard.

And don't even bother to show up again if you were planning to
put less than 4GB of memory!!  :-)

HTH,

Carlos
--


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

Re: [GENERAL] invalid memory alloc request size

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
>> OK, what's the schema of this table exactly?

> ...
> Regeln:
> cache_stip_delete AS
> ON DELETE TO spieletipps DO  UPDATE tsptcache SET tc_cache = -2
>FROM tippspieltage2spiele tspt2sp, spiele sp
>   WHERE tsptcache.tr_kurzname = old.tr_kurzname AND tspt2sp.tr_kurzname = 
> old.tr_kurzname AND tspt2sp.sp_id = old.sp_id AND tspt2sp.sp_id = sp.sp_id 
> AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND 
> sign((old.stip_heimtore - old.stip_gasttore)::double precision) = 
> sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND 
> tsptcache.tc_cache <> -2

Oh, I should have thought of that: the bare DELETE operation doesn't
care what's in the tuple, but this ON DELETE rule sure does.  That's
why the delete crashes, it's trying to extract the field contents so
it can execute the rule.

> yes, they should both be "alteheide". Is it possible to open the file and 
> just 
> fix the bit?

Yeah, if you have a suitable hex editor.  You'll probably need to shut
down the postmaster first, as it may have a cached copy of the page.

> I have no clue, why it happens. But i changed my schema a few month
> ago to use a materialized view (You see all the rules in this schema
> above). i need some complicated ranking algorithm to calculate the
> materialzed view. everything is implemented inside postgresql with
> rules and functions (pgperl and plpgsql). One more aspect are temp
> tables to me. I use lots of them for a specific tasks (reusing the
> calculating algorithm mentioned above for a different data view). With
> lots of temp tables i got problems with pg_type where some old temp
> values reside and i got to delete some of them manually a few times
> per month.

Hmm ... the one part of that that jumps out at me is plperl.  We already
know that plperl can screw up the locale settings; I wonder whether
there are other bugs.  Anyway, if you are using plperl I *strongly*
recommend updating to the latest PG release ASAP (8.0.6 in your case).
If you cannot, at least make sure the postmaster is launched with the
same LC_XXX settings in its environment as are embedded in the database.

regards, tom lane

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

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

[GENERAL] ANN: Bricolage 1.10

[David Wheeler]

It is with great pleasure that the Bricolage development team  
announces

the release of Bricolage 1.10. The culmination of over 19 months of
development, version 1.10 represents a significant advance for the
celebrated open-source content management and publishing system.  
Here

are some of the highlights:

PHP Templating

Bricolage is the first content management system to support three
different Perl-based templating architectures (Mason, Template  
Toolkit,

and HTML::Template) as well as one in a completely different
programming language: PHP 5. Bricolage 1.10 adds PHP templating
support, allowing template developers to use the popular Web
programming language to formatting their documents for output. This
functionality is thanks to a killer new technology, known as
PHP::Interpreter, that loads the PHP 5 interpreter into a Perl 5
interpreter, and affords transparent access between PHP and Perl  
code.

The upshot is that PHP templaters get full access to the entire
Bricolage API, as well as the ability to use whatever other PHP  
or Perl

libraries they wish.

Our expect is that this development will push Bricolage into new
environments where PHP developers can make use of the powerful  
content

management and publishing system without having to learn a new
programming language. Furthermore, we hope that PHP::Interpreter  
will
act as a bridge between the Perl and PHP communities, such that  
there

is a greater exchange of ideas and a greater ability to use each
other's libraries.

PHP::Interpreter was developed by OmniTI. PHP::Interpreter and  
the PHP

templating support in Bricolage were sponsored by SAPO--Portugal
Online.

LDAP Authentication

Bricolage 1.10 includes support for a pluggable authentication
architecture, and in addition to its built-in authentication has  
added
a module for authentication against an LDAP directory server.  
This new

feature is sure to be welcome in busy enterprises that rely on a
directory server, such as Windows Active Directory
http://www.microsoft.com/windowsserver2003/technologies/ 
directory/activ

edirectory/default.mspx, Novel eDirectory
http://www.novell.com/products/edirectory/, or OpenLDAP
http://www.openldap.org/. Authentication can be limited to  
members of a

directory group, and supports LDAP v.3 and TLS connectivity.
Contributed by Kineticode.

Revamped Interface

Bricolage 1.10 sports a completely revamped browser interface  
that is

XHTML compliant and handles all styling via CSS. Yes, our 1999-era
table-driven interface is officially a thing of the past. The  
upshot is
that the interface is much more elegant, easier to skin with  
your own
look (by overriding its CSS files), allows search results and  
editing

fields to expand and contract with the browser window size, and
delivers pages as much as 70% smaller than they were before. The  
new

interface was Contributed by Marshall Roch.

A second major new UI feature is the revamped "Bulk Edit"  
interface.

Gone is the old "Super Bulk Edit" interface, with the Bulk Edit
revisions overtaking its functionality. Now you can edit the entire
contents of a story document, from the top-most element to the
bottom-most field, in a single textarea field with no reloads.

The secret to allowing the full-text editing of Bricolage's unique
hierarchical element structures is Plain Old Documentation, or  
"POD".

Subelements are denoted by a new =begin POD tag, and end with a
matching =end tag. The result is a much more natural editing  
interface.

Even related stories and media are supported by new POD tags. We
believe that this improvement will greatly facilitate the editing
process, making Bricolage a much more enjoyable product for content
editors to work with.

The Bulk Edit revision is complemented by two new additions: diff
support and a JavaScript-powered "Find and Replace" dialog box.  
Users
can now see at a glance the changes between one version of a  
document

and another. The changes are shown on a word-by-word basis, with
additions in green with an underline and deletions in red with a
strikeout. A similar interface is used to show the differences  
between

versions of templates using the traditional "unified diff" format
rather than word-by word.

The JavaScript-powered "Find and Replace" dialog box can be used to
search by strings or regular expressions in a Bulk Edit or Template
editing environment. Found bits of text can also be replaced or  
even
globally replaced. We believe that this powerful new feature,  
combined
with the new Bulk Edit interface, makes Bricolage a compelling  
content

editing environment.

The Bulk Edit, diff, and Find and Replace features were  
contributed by

K

[GENERAL] Fedora and pgadmin3

[Martin Krüger]

Hi 

I have fedora core4 and i've installed the pgadmin III RPM when i try to
run the pgadmin i get a 'Speicherzugriffsfehler' which means something
like memory access error.

can someone help me please

thanks martin


---(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] psql(18967) malloc: *** vm_allocate(size=8421376)

[Brian A. Seklecki]

What about upping ulimt(3) via ulimit(1) in builtin(1)  -- assuming you're 
running Bash.


~BAS

On Mon, 9 Jan 2006, Ari Kahn wrote:

I'm doing a query that really should be too taxing. But when I execute it I 
get the following error(s):


psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3)
psql(18967) malloc: *** error: can't allocate region
psql(18967) malloc: *** set a breakpoint in szone_error to debug

EXPLAIN ANALYZE SELECT a1.qname, a1.symbol, a1.num, a1.ge, a1.start, a1.stop, 
a1.cr, a1.str, a1.ex

FROM singlehits a1, singlehit_ge a2
WHERE a2.cnt>1 AND a2.symbol=a2.symbol;
   QUERY PLAN
--
Nested Loop  (cost=89.36..6086.42 rows=273312 width=88) (actual 
time=0.113..24456.508 rows=54952794 loops=1)
 ->  Seq Scan on singlehits a1  (cost=0.00..530.82 rows=17082 width=88) 
(actual time=0.043..71.127 rows=17082 loops=1)
 ->  Materialize  (cost=89.36..89.52 rows=16 width=0) (actual 
time=0.000..0.418 rows=3217 loops=17082)
   ->  Seq Scan on singlehit_ge a2  (cost=0.00..89.34 rows=16 width=0) 
(actual time=0.049..5.167 rows=3217 loops=1)

 Filter: ((cnt > 1) AND ((symbol)::text = (symbol)::text))
Total runtime: 30024.664 ms
(6 rows)

Given this post http://xy1.org/pgsql-general@postgresql.org/msg01154.html
I tried both VACUUM FULL and ANALYZE on the DB to no avail.
Thanks,
Ari

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


l8*
-lava

x.25 - minix - bitnet - plan9 - 110 bps - ASR 33 - base8

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

Re: [GENERAL] invalid memory alloc request size

[Janning Vygen]

Am Montag, 23. Januar 2006 20:30 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> > Ok, i got the reffilnode from pg_class and compiled pg_filedump. result
> > of ./pg_filedump -i -f -R 3397
> > /home/postgres8/data/base/12934120/12934361 > filedump.txt is attached
>
> OK, what's the schema of this table exactly?  It looks like there are
> a couple of text or varchar columns to start, but I'm not sure about the
> last three columns.

kicktipp.de=> \d spieletipps
 Tabelle »public.spieletipps«
Spalte |   Typ| Attribute
---+--+---
 tr_kurzname   | text | not null
 mg_name   | text | not null
 sp_id | integer  | not null
 stip_heimtore | smallint | not null
 stip_gasttore | smallint | not null
Indexe:
»pk_spieletipps« PRIMARY KEY, btree (tr_kurzname, mg_name, sp_id)
»ix_stip_fk_spiele« btree (tr_kurzname, sp_id) CLUSTER
Fremdschlüssel-Constraints:
»fk_mitglieder« FOREIGN KEY (tr_kurzname, mg_name) REFERENCES 
mitglieder(tr_kurzname, mg_name) ON UPDATE CASCADE ON DELETE CASCADE 
DEFERRABLE INITIALLY DEFERRED
»fk_tippspieltage2spiele« FOREIGN KEY (tr_kurzname, sp_id) REFERENCES 
tippspieltage2spiele(tr_kurzname, sp_id) ON UPDATE CASCADE ON DELETE CASCADE 
DEFERRABLE INITIALLY DEFERRED
Regeln:
cache_stip_delete AS
ON DELETE TO spieletipps DO  UPDATE tsptcache SET tc_cache = -2
   FROM tippspieltage2spiele tspt2sp, spiele sp
  WHERE tsptcache.tr_kurzname = old.tr_kurzname AND tspt2sp.tr_kurzname = 
old.tr_kurzname AND tspt2sp.sp_id = old.sp_id AND tspt2sp.sp_id = sp.sp_id 
AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND 
sign((old.stip_heimtore - old.stip_gasttore)::double precision) = 
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND 
tsptcache.tc_cache <> -2
cache_stip_insert AS
ON INSERT TO spieletipps DO  UPDATE tsptcache SET tc_cache = -2
   FROM tippspieltage2spiele tspt2sp, spiele sp
  WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname = 
new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id 
AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND 
sign((new.stip_heimtore - new.stip_gasttore)::double precision) = 
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND 
tsptcache.tc_cache <> -2
cache_stip_update AS
ON UPDATE TO spieletipps DO  UPDATE tsptcache SET tc_cache = -2
   FROM tippspieltage2spiele tspt2sp, spiele sp
  WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname = 
new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id 
AND sp.sp_abpfiff = true AND tsptcache.tspt_sort >= tspt2sp.tspt_sort AND 
(sign((new.stip_heimtore - new.stip_gasttore)::double precision) = 
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) OR 
sign((old.stip_heimtore - old.stip_gasttore)::double precision) = 
sign((sp.sp_heimtore - sp.sp_gasttore)::double precision)) AND 
tsptcache.tc_cache <> -2

> > but i guess its item 49 which makes trouble
> >   1258: 0100 616c7465 68656964 6500  alteheide...
> >
> > But it doesn't look very diffrent to item 48:
> >   12a0: 0d00 616c7465 68656964 6500  alteheide...
>
> If these are both supposed to be strings 'alteheide', then the problem 
> is the bogus length word on the first one: instead of starting with
> 0100 it should start with 0d00, like the second one does.

yes, they should both be "alteheide". Is it possible to open the file and just 
fix the bit?

> It's conceivable that this stems from a software problem, but I'm
> wondering about hardware problems causing dropped bits, myself.

I have no clue, why it happens. But i changed my schema a few month ago to use 
a materialized view (You see all the rules in this schema above). i need some 
complicated ranking algorithm to calculate the materialzed view. everything 
is implemented inside postgresql with rules and functions (pgperl and 
plpgsql). One more aspect are temp tables to me. I use lots of them for a 
specific tasks (reusing the calculating algorithm mentioned above for a 
different data view). With lots of temp tables i got problems with pg_type 
where some old temp values reside and i got to delete some of them manually a 
few times per month. After all my "feeling" is that i encouter problems like 
this one too often to believe in hardware problems. But this time it seems to 
be a new one and i have no clue if hardware or software related. At this time 
i just want to fix it. But if you want to take a close look at it, i will 
send you all you need.

> Another point is that AFAICS this tuple could not pose a problem for
> DELETE all by itself, because it doesn't have any toasted fields.
> Perhaps there is more corruption elsewhere.  Could you get a stack
> trace from the crashed DELETE, rather than a crashed SELECT?

Maybe the rule is a problem?

here you are. I did:

select ctid from spieletipps limit 1 offset 387439;
 

Re: [GENERAL] Linux - postgres RAID

[Steve Atkins]

On Jan 23, 2006, at 11:13 AM, Rick Gigger wrote:

I figure this would be a good place to ask. I want to build / buy a  
new linux postgres box.  I was wondering if anyone on this list had  
some experience with this they'd like to share.  I'm thinking  
somewhere in the $7k - 15k range.  The post important things are  
write speed to the disk and good linux driver support for the raid  
card.  Can anyone recommend a specific raid card / server vendor?


It'll depend on what you're planning on using it for. Performance  
requirements, capacity

and so on.

For fast writes you'll probably want something like RAID10 with lots  
of spindles. SCSI
is likely to be somewhat faster, but a lot more expensive. For SATA  
RAID most people
like the 3ware and Areca cards. I'm using this system, from ASA, for  
pretty much that
and it seems OK. 5U server, 24 external bay, 2 internal bay capacity.  
And it's opterons,
which seem to be a bit more PG-friendly than Xeons. It uses the 3ware  
9550SX card.
(The general opinion here seems to be that the Areca cards are  
somewhat "better"

than the 3wares, but a lot more expensive).

http://www.asaservers.com/config_system.asp?config_id=5USATA

Cheers,
  Steve


---(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] Linux - postgres RAID

[Rick Gigger]

Ok then, the size of the postgres data directory is about 1 GB.  The  
OS will be a version of linux with a 2.6 kernel.  100 GB of total  
storage would be plenty.  The load load would probably be around  
2k-3k transactions / minute.  I plan on doing a lot of research on my  
own I am just looking for a place to start looking.


On Jan 23, 2006, at 12:19 PM, Wes Williams wrote:

For those funds you best be served by defining the database size,  
projected
capacity, load, specific OS, and other details in order to even  
remotely

receive a usable reply.




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Rick Gigger
Sent: Monday, January 23, 2006 2:13 PM
To: pgsql general
Subject: [GENERAL] Linux - postgres RAID


I figure this would be a good place to ask. I want to build / buy a
new linux postgres box.  I was wondering if anyone on this list had
some experience with this they'd like to share.  I'm thinking
somewhere in the $7k - 15k range.  The post important things are
write speed to the disk and good linux driver support for the raid
card.  Can anyone recommend a specific raid card / server vendor?

Thanks,

Rick

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





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

Re: [GENERAL] invalid memory alloc request size

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
> Ok, i got the reffilnode from pg_class and compiled pg_filedump. result of 
> ./pg_filedump -i -f -R 3397 /home/postgres8/data/base/12934120/12934361 > 
> filedump.txt is attached

OK, what's the schema of this table exactly?  It looks like there are
a couple of text or varchar columns to start, but I'm not sure about the
last three columns.

> but i guess its item 49 which makes trouble
>   1258: 0100 616c7465 68656964 6500  alteheide...

> But it doesn't look very diffrent to item 48:
>   12a0: 0d00 616c7465 68656964 6500  alteheide...

If these are both supposed to be strings 'alteheide', then the problem
is the bogus length word on the first one: instead of starting with
0100 it should start with 0d00, like the second one does.

It's conceivable that this stems from a software problem, but I'm
wondering about hardware problems causing dropped bits, myself.

Another point is that AFAICS this tuple could not pose a problem for
DELETE all by itself, because it doesn't have any toasted fields.
Perhaps there is more corruption elsewhere.  Could you get a stack
trace from the crashed DELETE, rather than a crashed SELECT?

regards, tom lane

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

[GENERAL] Are indexes used with LIKE?

[Kovács Péter]

Hi,

Are indexes on VARCHAR columns used with the LIKE operator, and if so, 
how efficiently are they used?


I can imagine that using indexes can be easy with the starting literal 
characters up to the first percent sign such as in:


LIKE 'ZOE%QQWE%'

But, after the first % sign, things can get more difficult.

The reason I am asking is that we are thinking about discriminating 
between rows of a table based on a VARCHAR column containing various 
one-character "flags". We could then use the LIKE operator for 
formulating filter conditions.


Any help is appreciated!

Regards
Peter

---(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] RAID 5 and postgresql

[Vivek Khera]

On Jan 21, 2006, at 8:09 AM, Sander Steffann wrote:

Dell has used (and rebranded) Adaptec and LSI controllers for their  
PERC series, and I agree that the Adaptec controllers perform  
badly. As far as I know the LSI based controllers are quite good  
(and some come with 256MB battery backed cache, which is nice :-)


Over the last 5 years I've had a *LOT* of Dell equipment, most of  
which has RAID.   The adaptec-based ones are OK.  Not great  
performance, but very easy to manage.


The LSI based ones are pretty good, but the older ones were somehow  
deficient in that the expected performance was never achieved.


However, the latest one I have is a PE 1850 with the PERC43/Si (LSI  
based) which is surprisingly fast.  It comes with battery + 256MB RAM  
and just screams in RAID1 mode (only have two disks on this box).


I'd buy that server again anyday.

However, for my big DB server arrays, I always go with the LSI  
branded cards and run them in RAID10 config.




---(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] Linux - postgres RAID

[Rick Gigger]

I figure this would be a good place to ask. I want to build / buy a  
new linux postgres box.  I was wondering if anyone on this list had  
some experience with this they'd like to share.  I'm thinking  
somewhere in the $7k - 15k range.  The post important things are  
write speed to the disk and good linux driver support for the raid  
card.  Can anyone recommend a specific raid card / server vendor?


Thanks,

Rick

---(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] DBMirror.pl performance change

[Peter Wilson]

Achilleus Mantzios wrote:

Peter,
It is much more convinient for you to make a test,
(just change the last function in DBmirror.pl), than for me
(grab whitebeam, compile for FreeBSD, etc...)

Of course you would need to use the original .conf format
than the one you are using now.

It would be interesting to see some numbers.

P.S.

Please include my address explicitly, pgsql-general comes
to me in digest mode.


I'll take a look into this when I get a chance. Right now the only replicated systems I have are for live commercial clients - my development systems 
aren't replicated, just backed-up periodically.


It is worth looking through the Perl version some more though. I'm pretty sure I worked around most of the escaping/unescaping when I looked at the 
'C' version. I'm pretty sure some of the same approach could be used to improve performance of the Perl version. The main thing I found was that the 
data table is un-escaped when read from the table and then re-escaped before being sent to the slave database. In practice the data doesn't have to be 
touched.


My own preference right now is to stick with the C version now I have it. Replication is just about simultaneous with negligible CPU usage. When I get 
a chance, I'm intending decoupling the 'C' version from the whole of Whitebeam so it can be built by itself. At the time I needed a solution quickly 
so making use of a few Whitebeam utility classes got me there.


Pete

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

Re: [GENERAL] Transact SQL compatibility layer

[Scott Marlowe]

On Sun, 2006-01-22 at 18:46, James Harper wrote:
> Is there such a thing as a translator/proxy that can pretend to be a
> Microsoft SQL server and proxy all the commands to a PostgreSQL server,
> and the results back again? Obviously the purpose of it would be to
> allow an application written for MSSQL Server to work with PostgreSQL
> without modification.
> 
> If not, has such a thing been considered? What would be the obstacles to
> developing such a proxy?

The amount of work required here would be pretty huge.

Plus, what versions of MSSQL do you emulate?  What about newer versions
of both MSSQL and PGSQL.  If constant maintenance wasn't done on it, it
would soon become a legacy project.

My guess is that the only way you could make it happen would be to form
a company and sell it commercially.  I doubt any folks in the open
source community would feel a burning desire to spend a lot of time
making PostgreSQL act like it was MSSQL server.

There are few, if any, projects that require MSSQL that I'd be
interested enough in to put that much effort into adapting PostgreSQL to
act like it was MSSQL server.


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

Re: [GENERAL] Transact SQL compatibility layer

[Shelby Cain]

--- James Harper <[EMAIL PROTECTED]> wrote:

> Is there such a thing as a translator/proxy that can pretend to be a
> Microsoft SQL server and proxy all the commands to a PostgreSQL
> server,
> and the results back again? Obviously the purpose of it would be to
> allow an application written for MSSQL Server to work with PostgreSQL
> without modification.
> 
> If not, has such a thing been considered? What would be the obstacles
> to
> developing such a proxy?
> 

Assuming your goal is compatibility to a level such that no changes are
required in the client application, I'd say it'd be a tremendous amount
of work.  At the very least you'd have to reverse-engineer mssql
server's communication protocol and write a proxy that could understand
and translate that to something compatible with postgresql.  Then you'd
have to deal with the difference between the two server sql dialects.

All in all, you'd be much better off designing your application such
that it explicitly supports multiple databases to begin with.

Regards,

Shelby Cain

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

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

Re: [GENERAL] invalid memory alloc request size

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
> I shouldn't call gdb while my database is up and running, don't i?

Sure you can.  Especially against a core dump --- that mode doesn't have
anything to do with the running processes.

> $ delete from spieletipps where ctid = '(3397,49)';
> Server beendete die Verbindung unerwartet

Hmm ... as far as I can think at the moment, this suggests a problem
with a toasted field; DELETE wouldn't need to look at the contents of
a target row except if it has to find and delete subsidiary toast rows.
But looking at the gdb backtrace would help to confirm or deny that.

Another thing that would be useful at this point is to get a dump of the
page containing the corrupted tuple, which we now know is block 3397 of
that table.  See pg_filedump from
http://sources.redhat.com/rhdb/utilities.html
Something like "pg_filedump -i -f -R 3397 $PGDATA/base//", where
 is the database OID and  is the table's relfilenode.

regards, tom lane

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

Re: [GENERAL] invalid memory alloc request size

[Janning Vygen]

Am Montag, 23. Januar 2006 17:05 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> > pg_dump: ERROR:  invalid memory alloc request size 18446744073709551614
> > pg_dump: SQL command to dump the contents of table "spieletipps" failed:
> > PQendcopy() failed.
>
> This looks more like a corrupt-data problem than anything else.  Have
> you tried the usual memory and disk testing programs?

no, i didn't. What are the usual memory and disk testing programs? ( a few 
weeks ago i wanted to start a troubleshooting guide for guys like me, but i 
didn't start yet this needs to be documented.). I am not a system 
administrator and a hard disk is a black box to me.

By the way: the database is still running and serving requests.

> > recent thread on HACKERS but sorry guys: i dont know how to produce a
> > backtrace.
>
> Time to learn ;-)
>
>   gdb /path/to/postgres_executable /path/to/core_file
>   gdb> bt
>   gdb> q

I shouldn't call gdb while my database is up and running, don't i?

I tried to find and delete the corrupted row (as you mentioned in 
http://archives.postgresql.org/pgsql-admin/2006-01/msg00117.php)

I found it:

$ select sp_id from spieletipps limit 1 offset 387583;
Server beendete die Verbindung unerwartet
Das heißt wahrscheinlich, daß der Server abnormal beendete
bevor oder während die Anweisung bearbeitet wurde.
Die Verbindung zum Server wurde verloren.  Versuche Reset: Fehlgeschlagen.
!> \q

and i can get the ctid:

$ select ctid from spieletipps limit 1 offset 387583;
   ctid
---
 (3397,49)
(1 Zeile)


but when i want to delete it:
$ delete from spieletipps where ctid = '(3397,49)';
Server beendete die Verbindung unerwartet
Das heißt wahrscheinlich, daß der Server abnormal beendete
bevor oder während die Anweisung bearbeitet wurde.
Die Verbindung zum Server wurde verloren.  Versuche Reset: Fehlgeschlagen.

How can i get rid of it? (I don't have oids in the table, i created them 
without oids)

> > The core file will be somewhere under $PGDATA, named either "core" or 
> "core.n" depending on your kernel settings.  If you don't see one
> then it's probable that the postmaster was started under "ulimit -c 0".
> Put "ulimit -c unlimited" in your postgres startup script, restart,
> trigger the crash again.
>
> It's also a good idea to look in the postmaster log to see if any
> unusual messages appeared before the crash.

this is form the postmaster log:

LOG:  server process (PID 14756) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
FATAL:  the database system is starting up
LOG:  database system was interrupted at 2006-01-23 09:46:03 CET
LOG:  checkpoint record is at 1/D890C0E0
LOG:  redo record is at 1/D88F93E8; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 485068; next OID: 16882321
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  redo starts at 1/D88F93E8
LOG:  record with zero length at 1/D8953988
LOG:  redo done at 1/D8953920
LOG:  database system is ready
LOG:  server process (PID 15198) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited 
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat 
your command.
FATAL:  the database system is in recovery mode
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2006-01-23 09:46:15 CET
LOG:  checkpoint record is at 1/D8953988
LOG:  redo record is at 1/D8953988; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 485130; next OID: 16882321
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  redo starts at 1/D89539D0
LOG:  record with zero length at 1/D8966BF8
LOG:  redo done at 1/D8966BC8
LOG:  database system is ready
LOG:  server process (PID 15400) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited 
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat 
your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2006-01-23 09:46:24 CET
LOG:  checkpoint record is at 1/D8966BF8
LOG:  redo record is at 1/D8966BF8; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 485183; next OID: 16882321
LOG:  database system was not properly shut down; automatic recovery

Re: [GENERAL] RAID 5 and postgresql

[Lincoln Yeoh]

At 10:01 AM 1/23/2006 -0600, Scott Marlowe wrote:


I'm not sure if it's Dell's BIOS on the mobos, or something with the LSI
cards, but the performance was substandard.

So if you're working somewhere that you simply have to use Dell (not
uncommon), at least make sure you get the LSI based RAID controller.


How about software RAID?

Linux software RAID appears to perform better than most RAID controllers 
except perhaps those that can do read interleaving for RAID1 (I believe 
some 3ware controllers can do it). Linux RAID mirroring doesn't do read 
interleaving, only read balancing, which may not be so good for a single 
sequential read, but pretty good for concurrent sequential reads - each 
drive in a mirror set can handle one sequential read.


I find many of these RAID controllers fail significantly more than basic 
SCSI controllers (which hardly ever fail). And the support under Linux for 
such controllers can be a bit patchy sometimes - you want to be able to 
easily know if a drive has died.


It just seems strange to pay a fair bit for something that doesn't perform 
well and is less reliable.


Of course you get the "convenience" of the RAID stuff being abstracted away 
so it just looks like one drive.





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

[GENERAL] ROLLBACK triggers?

[Daisuke Maki]

Hi,

First, apologies if my question is a bit off-course. Please feel free to
direct me to a different mailing list if not appropriate.

I'm currently trying to embed Senna full text search engine
(http://qwik.jp/senna/) into postgres. I'm trying to achieve this by
using triggers (implemented in C) to cause an update to senna's index at
various points.

This seemed to work fine until I realized that while postgres' SQL
commands could be rolled back, Senna's index remained already-changed.
There are other potential issues with regards to transaction safety, but
currently this seems to be a problem that I cannot fix by simply
patching Senna. So I thought that if there was a rollback trigger, I
could call whatever necessary to undo the changes that were made to the
index.

A quick scan of the docs and the source code tree seems to indicate that
there is no such thing as a rollback trigger, short of hacking it.

Now, I'm wondering:
  1. Is there a rollback/commit trigger? If not, is it planned to be
 implemented at all?
  2. Is there a way to undo changes to data external to postgres
 when a rollback occurs, OR, only update that external data
 when a commit occurs?

Thanks in advance,
--d


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

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

[GENERAL] Full Text Indexing Using Tsearch2-Module

[Praveen Kumar (TUV)]

 
Hello All,
I have installed Tsearch-Module for full text indexing .But when I search text 
using gist(idxFTI) index on table I also found 
all data which have same accent. Example
1.If I try search for MANI word it also search for MANY word.
2.If I try search for ANDY word it also search for ANDI word.
Please can you tell me how to avoid this problem ? If I want to search text 
MANI it should search only for MANI not MANY.

-Original Message-
From: Jim Nasby [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 12, 2006 1:01 AM
To: Praveen Kumar (TUV); [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Subject: RE: [SPAM] - how can we use outer join in Postures - Found
word(s) if you received this in error in the Text body


This should really have been sent to the -general mailing list, so I'm adding 
it.

You will need to use OUTER JOIN syntax to accomplish this in PostgreSQL; see 
http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html#QUERIES-FROM

Note that most databases (including Oracle) now support JOIN syntax instead of 
other hacks to support outer joins.

-Original Message-
From: Praveen Kumar (TUV) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 11, 2006 6:39 AM
To: Jim Nasby; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL 
PROTECTED]
Subject: [SPAM] - how can we use outer join in Postures - Found word(s) if you 
received this in error in the Text body




Hello All,
I want to create one table with from many different tables using outer 
joins.Please can you guide how is possible to create in Postgresql.
Let we have syntax for creating table in oracle.If we want to create same table 
in Postgresql  then how will we replace (+) in syntax sothat we can use outer 
join facility in 
PostgreSQL.

CREATE table comp_prod_cert 
AS
select 
tuv_tuvdotcom_mast.tuvdotcom as tuvdotcom,
COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name) 
|| ' ' || 
COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name) as 
company_name,
tuv_certificate_mast.cert_id as cert_id,
tuv_certificate_mast.cert_number as certificate_number,
tuv_certificate_mast.cust_id as cust_id,
COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description) as 
description,
tuv_tuvdotcom_mast.tuvdotcom_id as tuvdotcom_id,tuv_tuvdotcom_mast.status_id as 
status_id,
'' page_valid_from, sysdate page_creation_date,
tuv_tuvdotcom_mast.tuvdotcom||' 
'||COALESCE(tuv_tuvdotcom_intl.sublease_company_name,tuv_tuvdotcom_mast.sublease_company_name)||'
 '
||COALESCE(tuv_tuvdotcom_intl.marketing_info,tuv_tuvdotcom_mast.marketing_info)||'
 '||tuv_certificate_mast.cert_number||' '
||tuv_certificate_mast.scope_english||' '||tuv_certificate_mast.scope_german||' 
'||tuv_certificate_mast.scope_local||' '
||tuv_zart_mast.zart_name||' 
'||COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description)||' '
||COALESCE(tuv_customer_mast_intl.url,tuv_customer_mast.url)||' 
'||COALESCE(tuv_customer_mast_intl.email,tuv_customer_mast.email)||' '
||tuv_customer_mast.name_local||' '||tuv_customer_mast.address_local||' 
'||tuv_customer_mast.building_local||' '
||tuv_customer_mast.city_local||' 
'||COALESCE(tuv_customer_mast_intl.title,tuv_customer_mast.title)||' '
||COALESCE(tuv_customer_mast_intl.first_name,tuv_customer_mast.first_name)||' '
||COALESCE(tuv_customer_mast_intl.second_name,tuv_customer_mast.second_name)||' 
'
||COALESCE(tuv_customer_mast_intl.third_name,tuv_customer_mast.third_name)||' '
||COALESCE(tuv_customer_mast_intl.fourth_name,tuv_customer_mast.fourth_name)||' 
'
||tuv_location_mast.post_code||' '||tuv_location_mast.phone||' 
'||tuv_location_mast.fax||' '
||COALESCE(tuv_location_mast_intl.title,tuv_location_mast.title)||' '
||COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name)||' '
||COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name)||' 
'
||COALESCE(tuv_location_mast_intl.third_name,tuv_location_mast.third_name)||' '
||COALESCE(tuv_location_mast_intl.fourth_name,tuv_location_mast.fourth_name)||' 
'
||COALESCE(tuv_location_mast_intl.street_1,tuv_location_mast.street_1)||' '
||COALESCE(tuv_location_mast_intl.street_2,tuv_location_mast.street_2)||' '
||COALESCE(tuv_location_mast_intl.city_1,tuv_location_mast.city_1)||' '
||COALESCE(tuv_location_mast_intl.city_2,tuv_location_mast.city_2)||' '
||COALESCE(tuv_location_mast_intl.state,tuv_location_mast.state)||' 
'||COALESCE(tuv_location_mast_intl.country,tuv_location_mast.country)
as search_data
from
tuv_tuvdotcom_mast,
tuv_tuvdotcom_intl,
tuv_tuvdotcom_type_mast,
tuv_tuvdotcom_system_certs,
tuv_certificate_mast,
tuv_customer_location, 
tuv_location_mast,
tuv_location_mast_intl,
tuv_customer_mast, 
tuv_customer_mast_intl,
tuv_zart_mast,
tuv_zart_mast_intl
where 
tuv_tuvdotcom_mast.tuvdotcom_id = tuv_tuvdotcom_intl.tuvdotcom_id(+) and
tuv_tuvdotcom_mast.tdc_type_id = tuv_tuvdotcom_type_mast.tdc_type_id and
tuv

Re: [GENERAL] joining tables

[Edmund]

[EMAIL PROTECTED] writes:

> Hi,
> 
> If you have two tables, each with a column called "keys" and a column
> called "values", and they are both incomplete, such as:
> 
> table 1:
> 
> keys | values
> -+--
> 1| (null)
> 2| two
> 3| (null)
> 
> table 2:
> 
> keys | values
> -+-
> 1| one
> 2| (null)
> 3| three
> 
> is there a way to join them, in order to get:
> 
> keys | values
> -+-
> 1| one
> 2| two
> 3| three
> 
> The closest I could get was with NATURAL FULL JOIN:
> 
> keys | values
> -+-
> 1| one
> 1| (null)
> 2| two
> 2| (null)
> 3| three
> 3| (null)
> 
> Thanks

Try something like:

SELECT key, 
CASE when table1.value IS NOT NULL THEN k1.value
ELSE table2.value END as value
FROM table1
FULL JOIN table2 USING(key);


You might want to use 'IS DISTINCT FROM table2.value' if you want the
value for table1 to be returned in preference to table2.value.



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

[GENERAL] Transact SQL compatibility layer

[James Harper]

Is there such a thing as a translator/proxy that can pretend to be a
Microsoft SQL server and proxy all the commands to a PostgreSQL server,
and the results back again? Obviously the purpose of it would be to
allow an application written for MSSQL Server to work with PostgreSQL
without modification.

If not, has such a thing been considered? What would be the obstacles to
developing such a proxy?

Thanks

James

(Not subscribed to the list at the moment - I'll check the archives but
please cc me on a response)

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

[GENERAL] Combine, Merge, Concatenate

[Andrej Kastrin]

Dear pgsql users,

I have a problem, which is quite hard to solve it in Perl (for me, of
course). I have to tables, which looks like

First Table:

1|001|002|003
2|006|04|002

Second Table:

001|text1|text2|text3
002|text6|text1|text2

Now I would like to concatenate this two tables into new table:

Third Table:

1|text1|text2|text3|text6 *
2|etc

*Notes; key=1 from first table; substitute 001 from first table with
text1|text2|text3 and 002 with text6 etc...

Is that possible in pgsql?

Thanks in advance for any notes and suggestions,

Cheers, Andre


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

Re: [GENERAL] What is made a mistake with SP?

[John DeSoi]

On Jan 23, 2006, at 8:34 AM, Marcos wrote:


ERROR: type "tipo_compras_calculado" not exists.


You have a set returning function (RETURNS setof  
tipo_compras_calculado), so this means you need to declare this type.  
So you should have a CREATE TYPE statement somewhere which defines  
the fields of tipo_compras_calculado. Something like:


CREATE TYPE tipo_compras_calculado (
id text,
produto integer,
...
);


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

Re: [GENERAL] invalid memory alloc request size

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
> pg_dump: ERROR:  invalid memory alloc request size 18446744073709551614
> pg_dump: SQL command to dump the contents of table "spieletipps" failed: 
> PQendcopy() failed.

This looks more like a corrupt-data problem than anything else.  Have
you tried the usual memory and disk testing programs?

> recent thread on HACKERS but sorry guys: i dont know how to produce a 
> backtrace. 

Time to learn ;-)

gdb /path/to/postgres_executable /path/to/core_file
gdb> bt
gdb> q

The core file will be somewhere under $PGDATA, named either "core" or
"core.n" depending on your kernel settings.  If you don't see one
then it's probable that the postmaster was started under "ulimit -c 0".
Put "ulimit -c unlimited" in your postgres startup script, restart,
trigger the crash again.

It's also a good idea to look in the postmaster log to see if any
unusual messages appeared before the crash.

regards, tom lane

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

Re: [GENERAL] RAID 5 and postgresql

[Scott Marlowe]

On Sat, 2006-01-21 at 07:09, Sander Steffann wrote:
> Hi,
> 
> > I would suppliment this with just saying that your controller card is
> > your performance,
> > the only cards I've seen score well on linux, and people have
> > expressed on this list for SCSI are the LSI card, for SATA, LSI, 3ware
> > (now AMCC) and Areca claim good linux support and seem to work well.
> > Steer full clear of Adaptec, Dell and Compaq controllers, and their
> > linux support is abysmal, and the performance reflects that,
> > particularly in RAID 5.
> 
> Dell has used (and rebranded) Adaptec and LSI controllers for their PERC 
> series, and I agree that the Adaptec controllers perform badly. As far as I 
> know the LSI based controllers are quite good (and some come with 256MB 
> battery backed cache, which is nice :-)

Last place I worked we used Dell rackmounts (2600 series mostly) and
they came, by default with the Adaptec based controllers.  Those were
horrific, locking up under load, really poor performance, etc...

The LSIs, as you mentioned, were much better.  We had exactly one Dell
2600 with the LSI (hmmm.  Bet you can't guess who specced that machine
out, eh?  hehe) with 256 Meg BBCache.  While it never locked up or hung,
it's I/O was noticeable slower than the machine it replaced, which also
had an LSI RAID controller with BBCache, bascially, the same chipset.

I'm not sure if it's Dell's BIOS on the mobos, or something with the LSI
cards, but the performance was substandard.

So if you're working somewhere that you simply have to use Dell (not
uncommon), at least make sure you get the LSI based RAID controller.

---(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] What is made a mistake with SP?

[Marcos]

Hi John

Thanks for response.

> In other words, what does
> select * from fun_compras_calculado();
> return and if it is not an error, why is it wrong?

My problem is in CREATE the function, see:

[EMAIL PROTECTED] psql teste -U teste < teste.sql
ERRO:  tipo "tipo_compras_calculado" não existe

In english I think that it means

ERROR: type "tipo_compras_calculado" not exists.

This is my problem with this SP...

Marcos.


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

Re: [GENERAL] numeric data type?

[Martijn van Oosterhout]

On Mon, Jan 23, 2006 at 09:48:52AM -0500, John D. Burger wrote:
> I have a (only vaguely) related question about NUMERICs.  I'm using 
> someone else's schema to copy data from their DB into mine.  They use 
> NUMERIC quite a bit, with scale 0, where I would use one of the integer 
> types.  My question is whether joining and matching on NUMERIC is 
> likely to be slower than, say, INTEGER.  Note that I'm never doing math 
> with these values, they are just IDs.

Yes, it's will be slower. Whether it's noticable... it depends on how
often you do it. The question is really, do you need to use numeric?
Will you be multiplying large numbers, do you expect decimals when you
divide, etc. Decide your answer to that before deciding about
performance issues.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] numeric data type?

[John D. Burger]

I have a (only vaguely) related question about NUMERICs.  I'm using 
someone else's schema to copy data from their DB into mine.  They use 
NUMERIC quite a bit, with scale 0, where I would use one of the integer 
types.  My question is whether joining and matching on NUMERIC is 
likely to be slower than, say, INTEGER.  Note that I'm never doing math 
with these values, they are just IDs.


Thanks for any info provided!

- John D. Burger
  MITRE


---(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] numeric data type?

[Zlatko Matić]

OK. Thanks for clarification.

- Original Message - 
From: "Doug McNaught" <[EMAIL PROTECTED]>

To: "Zlatko Matić" <[EMAIL PROTECTED]>
Cc: ; "Tony Caduto" 
<[EMAIL PROTECTED]>

Sent: Sunday, January 22, 2006 2:39 PM
Subject: Re: [GENERAL] numeric data type?


Zlatko Matić <[EMAIL PROTECTED]> writes:


So, it seems that numeric without parameters (precision, scale) behave
similar to float, but is much exact. Am I right or I missunderstood?


Right.  It's also considerably slower, since floating point
calculations can use the hardware.  Unless you're doing a huge number
of computations this may not be an issue.

-Doug

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



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

Re: [GENERAL] DBMirror.pl performance change

[Achilleus Mantzios]

Peter,
It is much more convinient for you to make a test,
(just change the last function in DBmirror.pl), than for me
(grab whitebeam, compile for FreeBSD, etc...)

Of course you would need to use the original .conf format
than the one you are using now.

It would be interesting to see some numbers.

P.S.

Please include my address explicitly, pgsql-general comes
to me in digest mode.
-- 
-Achilleus


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

Re: [GENERAL] What is made a mistake with SP?

[John DeSoi]

On Jan 23, 2006, at 3:27 AM, Marcos wrote:


I need create SP that returns cursos so that I can work with them.

I have many SQLs used for search records in database, then I will make
SP to return the results.

The example that I'm trying is:



I did not notice any obvious errors. It would help to know exactly  
what problem you are having. In other words, what does


select * from fun_compras_calculado();

return and if it is not an error, why is it wrong?



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(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] Postgresql/

[A. Kretschmer]

am  23.01.2006, um 11:38:28 +0100 mailte Janning Vygen folgendes:
> I would like to talk to some professional Postgresql DBA personally 
> (preferred 
> in german language). Of course we will pay for it. I don't want to talk to 
> sells personal, i want to talk to someone who really knows and has lots of 

http://www.credativ.de/

Peter Eisentraut and Michael Meskes, german PostgreSQL-Developers,
working there.


HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

Re: [GENERAL] DBMirror.pl performance change

[Peter Wilson]

The Whitebeam implementation of DBMirror.pl :
http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm

is a complete re-write in 'C' which avoids a lot of the text processing, and what text processing is required is done using a state machine rather 
than repeated regular expressions. Before I wrote the 'C' implementation I did look at optimising the Perl version. One of my big concerns was the 
time taking escaping and re-escaping the strings. I can't remember the details now but as far as I can remember a lot of that is unnecessary. There 
seemed to be an unescape of the data then a re-escape to the target database. In practice the data was in the correct format.


We make quite heavy use of both BYTEA and large varchar fields in our database. I did some load testing at the time and found the new version could 
replicate 10s of file objects per second - where the Perl version took 10 minutes to replicate a 120K BYTEA field (both on a slowish machine, but the 
Perl version wasn't much better on a fast machine *and* took 97% CPU).


I also took the opportunity to make the 'C' version much more tolerant to lost DB connections without having to restart and added a few other tweaks 
to make it more flexible.


It's released under the BSD license now as well

Pete
--
http://www.whitebeam.org
http://www.yellowhawk.co.uk


Achilleus Mantzios wrote:

I discovered a problem in DBMirror.pl, performance wise.

pending.c stores data in a way
very similar to the PgSQL input "\" escaped format.

When the field is of type bytea, and the source of data is binary, then
this produces 2 additional backslashes for every unprintable
char.

The performance in function extractData in DBMirror.pl, really suffers
from this condition, since it breaks data in chunks of "\" delimited
strings.

Informally speaking, performance tends to be O(n) where n is the size
of the data.

This can be remedied if we break data in chunks of "'" rather than "\".
"'" happens much more infrequently in common binary files (bz2, tiff, jpg, 
pdf etc..), and if we notice that odd number of contained "\", signals an
intermidiate "'", whereas even number of "\" signals the final "'", 
then we can make this routine run much faster.


I attach the new extractData function.

Now replicating a 400 k tiff takes 3 seconds instead of 12 minutes
it used to do.

I am wondering about the state of 
http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm


Please feel free for any comments.

Pete could you test this new DBMirror.pl, to see how it behaves
in comparison with your C++ solution?





sub extractData($$) {
  my $pendingResult = $_[0];
  my $currentTuple = $_[1];
  my $fnumber;
  my %valuesHash;
  $fnumber = 4;
  my $dataField = $pendingResult->getvalue($currentTuple,$fnumber);
  my $numofbs;

  while(length($dataField)>0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\".*?\")/s;
my $fieldName = $1;
$dataField = substr $dataField ,length($fieldName);
$fieldName =~ s/\"//g; #Remove the surronding " signs.

if($dataField =~ m/(^= )/s) {
  #Matched null
$dataField = substr $dataField , length($1);
  $valuesHash{$fieldName}=undef;
}
elsif ($dataField =~ m/(^=\')/s) {
  #Has data.
  my $value;
  $dataField = substr $dataField ,2; #Skip the ='
LOOP: {  #This is to allow us to use last from a do loop.
 #Recommended in perlsyn manpage.
  do {
my $matchString;
my $matchString2;
#Find the substring ending with the first ' or first \
	$dataField =~ m/(.*?[\'])?/s; 
	$matchString = $1;


$numofbs = ($matchString =~ tr/\\//) % 2;   

if ($numofbs == 1) { #// odd number of \, i.e. intermediate '
$matchString2 = substr $matchString,0, length($matchString)-2;
$matchString2 =~ s//\\/g;
$value .= ($matchString2 . "\'");
$dataField = substr $dataField,length($matchString);
}
else { #// even number of \, i.e. found end of data
$matchString2 = substr $matchString,0, length($matchString)-1;
$matchString2 =~ s//\\/g;
$value .= $matchString2;
$dataField = substr $dataField,length($matchString)+1;
last;
}

	   
  } until(length($dataField)==0);

  }
  $valuesHash{$fieldName} = $value;
  
  
  }#else if 
	  else {
	
	logErrorMessage "Error in PendingData Sequence Id " .

$pendingResult->getvalue($currentTuple,0);
die;
  }



  } #while

  return %valuesHash;

}






---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire 

[GENERAL] Postgresql/DBA/Sysadmin Consultant in Düsseldorf, Germany

[Janning Vygen]

Hi, 

we are running a very popular german website[*] which has grown over the years 
since 1995. We manage between 10 and 20 millions pageviews a month. We are a 
small company and myself is responsible for programming, DBA, system 
administration and hardware. I am a self-educated person since the beginning 
of the internet. In last couple of months we encountered a lot of problems 
with our postgresql installation which we couldn't handle appropiate. Most of 
them are hardware related. There are lot of reasons why some things went bad. 
We want to start over before things are getting worse. 

I would like to talk to some professional Postgresql DBA personally (preferred 
in german language). Of course we will pay for it. I don't want to talk to 
sells personal, i want to talk to someone who really knows and has lots of 
expierence. Some topics:

- which hardware to use
- where to place our hardware (data center)
- backup/failover strategies
- performance / postgresql.conf
- monitoring performance and system health

I had a meeting with sun consultants already and i will have another one. That 
will be fine. But i would like to talk to some who is independent from 
company selling things i could need.

If there is someone nearby who might help, please answer via personal E-Mail 
([EMAIL PROTECTED]). I guess we could meet very for a few hours and we will 
see if further consultancy is needed. 

kind regards,
janning

[*] which i not named to get not listed in google.de, but it's  called 
w w w . k i c k t i p p . d e

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

[GENERAL] DBMirror.pl performance change

[Achilleus Mantzios]

I discovered a problem in DBMirror.pl, performance wise.

pending.c stores data in a way
very similar to the PgSQL input "\" escaped format.

When the field is of type bytea, and the source of data is binary, then
this produces 2 additional backslashes for every unprintable
char.

The performance in function extractData in DBMirror.pl, really suffers
from this condition, since it breaks data in chunks of "\" delimited
strings.

Informally speaking, performance tends to be O(n) where n is the size
of the data.

This can be remedied if we break data in chunks of "'" rather than "\".
"'" happens much more infrequently in common binary files (bz2, tiff, jpg, 
pdf etc..), and if we notice that odd number of contained "\", signals an
intermidiate "'", whereas even number of "\" signals the final "'", 
then we can make this routine run much faster.

I attach the new extractData function.

Now replicating a 400 k tiff takes 3 seconds instead of 12 minutes
it used to do.

I am wondering about the state of 
http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm

Please feel free for any comments.

Pete could you test this new DBMirror.pl, to see how it behaves
in comparison with your C++ solution?

-- 
-Achilleus
sub extractData($$) {
  my $pendingResult = $_[0];
  my $currentTuple = $_[1];
  my $fnumber;
  my %valuesHash;
  $fnumber = 4;
  my $dataField = $pendingResult->getvalue($currentTuple,$fnumber);
  my $numofbs;

  while(length($dataField)>0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\".*?\")/s;
my $fieldName = $1;
$dataField = substr $dataField ,length($fieldName);
$fieldName =~ s/\"//g; #Remove the surronding " signs.

if($dataField =~ m/(^= )/s) {
  #Matched null
$dataField = substr $dataField , length($1);
  $valuesHash{$fieldName}=undef;
}
elsif ($dataField =~ m/(^=\')/s) {
  #Has data.
  my $value;
  $dataField = substr $dataField ,2; #Skip the ='
LOOP: {  #This is to allow us to use last from a do loop.
 #Recommended in perlsyn manpage.
  do {
my $matchString;
my $matchString2;
#Find the substring ending with the first ' or first \
$dataField =~ m/(.*?[\'])?/s; 
$matchString = $1;

$numofbs = ($matchString =~ tr/\\//) % 2;   

if ($numofbs == 1) { #// odd number of \, i.e. intermediate '
$matchString2 = substr $matchString,0, length($matchString)-2;
$matchString2 =~ s//\\/g;
$value .= ($matchString2 . "\'");
$dataField = substr $dataField,length($matchString);
}
else { #// even number of \, i.e. found end of data
$matchString2 = substr $matchString,0, length($matchString)-1;
$matchString2 =~ s//\\/g;
$value .= $matchString2;
$dataField = substr $dataField,length($matchString)+1;
last;
}

   
  } until(length($dataField)==0);
  }
  $valuesHash{$fieldName} = $value;
  
  
  }#else if 
  else {

logErrorMessage "Error in PendingData Sequence Id " .
$pendingResult->getvalue($currentTuple,0);
die;
  }



  } #while
  return %valuesHash;

}

---(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] What is made a mistake with SP?

[Marcos]

Hi,

I need create SP that returns cursos so that I can work with them.

I have many SQLs used for search records in database, then I will make
SP to return the results.

The example that I'm trying is:

CREATE OR REPLACE FUNCTION fun_compras_calculado() RETURNS SETOF
tipo_compras_calculado AS '
DECLARE
   linha_comprasRECORD;
   linha_calculada  tipo_compras_calculado;
BEGIN
   linha_calculada.acumulado := 0;
   FOR linha_compras IN
 SELECT id, qtd, produto, unitario FROM compras ORDER BY id
   LOOP
 linha_calculada.id := linha_compras.id;
 linha_calculada.produto := linha_compras.produto;
 linha_calculada.qtd := linha_compras.qtd;
 linha_calculada.unitario := linha_compras.unitario;
 linha_calculada.valor := linha_compras.qtd * linha_compras.unitario;
 linha_calculada.acumulado := linha_calculada.acumulado + 
linha_calculada.valor;
 RETURN NEXT linha_calculada;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql';


---(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] logging connections

[surabhi.ahuja]

Title: RE: [GENERAL] logging connections







please clarify the following:

log_destination (string)
PostgreSQL supports several methods for logging server messages, including stderr and syslog. On Windows, eventlog is also supported. Set this option to a list of desired log destinations separated by commas. The default is to log to stderr only. This option can only be set at server start or in the postgresql.conf configuration file.



can u please tell what do "stderr" and "syslog" mean?

thanks,
regards
Surabhi Ahuja

-Original Message-
From: [EMAIL PROTECTED] on behalf of surabhi.ahuja
Sent: Mon 1/23/2006 2:04 PM
To: Nikola Ivanov
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] logging connections

***
Your mail has been scanned by iiitb VirusWall.
***-***



i did the above, however still no looging is being done.

for eg if i have a c++ program where i establish a connection to postgres and does some activity.

the postgres log should log the above connection to postgres when i run the program.

similarly say if i do psql database_name

this is also another connection, and shd get logged.

however by the changes u suggested, i am not able to do so.

thanks,
reagrds

surabhi

-Original Message-
From: [EMAIL PROTECTED] on behalf of Nikola Ivanov
Sent: Fri 1/20/2006 7:41 PM
To: surabhi.ahuja
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] logging connections


***
Your mail has been scanned by iiitb VirusWall.
***-***


You need to edit your postgresql.conf file and in the "Error reporting and
logging" section set "log_connections=true", "log_disconnections=true", and
"log_hostname=true"

On 1/20/06, surabhi.ahuja <[EMAIL PROTECTED]> wrote:
>
> i want to know, how i can log connections to postgres.
>
> the sample log file is:
> LOG:  shutting down
> LOG:  database system is shut down
> LOG:  database system was shut down at 2006-01-17 18:18:24 CST
> LOG:  checkpoint record is at 0/B035D0
> LOG:  redo record is at 0/B035D0; undo record is at 0/0; shutdown TRUE
> LOG:  next transaction ID: 1267; next OID: 17728
> LOG:  database system is ready
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  received fast shutdown request
> LOG:  aborting any active transactions
> FATAL:  terminating connection due to administrator command
> LOG:  shutting down
> LOG:  database system is shut down
> LOG:  database system was shut down at 2006-01-20 11:00:00 CST
> LOG:  checkpoint record is at 0/3C339CB4
> LOG:  redo record is at 0/3C339CB4; undo record is at 0/0; shutdown TRUE
> LOG:  next transaction ID: 2283381; next OID: 1159413
> LOG:  database system is ready
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
>
>
>
> however, i am still not able to know how to log into the above file,
> who has connected etc
>
>
> if i can log something like " LOG:  connection received: host=client1
> > port=3775"
>
> and also if i shut down postmaster, using pg_ctl stop, no logging takes
> place to the above log file.
>
> How to enable the above,
>
> thanks,
> regards
> Surabhi
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Tom Lane
> Sent: Fri 1/20/2006 3:13 AM
> To: Nik
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Connections not closing
>
> ***
> Your mail has been scanned by iiitb VirusWall.
> ***-***
>
>
> "Nik" <[EMAIL PROTECTED]> writes:
> > Ok, I simplified the problem. I tried just running psql from the
> > command line, and I noticed that it opens two connection on two
> > different ports, and it closes only one.
> > For example I do the following in the command prompt:
>
> > C:\> psql -h host_name -p 5432 -d db_name -U user_name
> > Password:
>
> > 2006-01-19 09:50:29 [unknown] LOG:  connection received: host=client1
> > port=3775
>
> > 2006-01-19 09:50:31 [unknown] LOG:  connection received: host=client1
> > port=3778
>
> > 2006-01-19 09:50:31 test LOG:  connection authorized: user=user_name
> > database=db_name
>
> It tries to connect, gets told it needs a password (the log verbosity
> level is not high enough to record the rejection), asks you for the
> password, and connects again.  I don't see anything funny here.
>
> 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] [HACKERS] Need help in installing postgresql 8.1.2 on Windows

[Gurjeet Singh]

Hopefully your problem is solved by now; but if not, here's the link:

do read the README expanded at the end of the file-list:
http://www.postgresql.org/ftp/binary/v8.1.2/win32/

I could give you a long lecture on how to look for the things on your
own a little bit before pestering these mailing lists; but I think I
should spare you this one time.

Good luck with open-source.
Gurjeet.

On 18/01/06, Sarvjot Kaur <[EMAIL PROTECTED]> wrote:
>
> Sir
> I am trying to install Globus Toolkit4 on Windows machine. Postgresql8.1.2
> is required software for installing GT4. But i cant get installation steps
> from anywhere..
> Please help me and do reply
> Thanks
> Sarvjot
>
>  
> Yahoo! Photos – Showcase holiday pictures in hardcover
>  Photo Books. You design it and we'll bind it!
>
>

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

[GENERAL] invalid memory alloc request size

[Janning Vygen]

Hi,

my cron job which is dumping the databse fails this night. I got:

pg_dump: ERROR:  invalid memory alloc request size 18446744073709551614
pg_dump: SQL command to dump the contents of table "spieletipps" failed: 
PQendcopy() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request size 
18446744073709551614
pg_dump: The command was: COPY public.spieletipps (tr_kurzname, mg_name, 
sp_id, stip_heimtore, stip_gasttore) TO stdout;

I am running 
  postgresql-server-8.0.3-1.2
  on SuSE Linux 9.3 (x86-64)

I had this a few days before and decided to use a recent backup. It works fine 
for two days only. Maybe my harddisk is broken? Maybe 64-bit is broken? i 
have no clue and no idea what do to. i ve searched the archives and found a 
recent thread on HACKERS but sorry guys: i dont know how to produce a 
backtrace. 

select count(*) from spieletipps;
  count
--
 11612957
(1 Zeile)

works fine. When i do something like this:

$ select * from spieletipps where sp_id > 1000;

Server beendete die Verbindung unerwartet
Das heißt wahrscheinlich, daß der Server abnormal beendete
bevor oder während die Anweisung bearbeitet wurde.
Die Verbindung zum Server wurde verloren.  Versuche Reset: Fehlgeschlagen.

(it means: server closed the connection unexpectedly. ... Try to reset 
connection failed.)

Please help me!

kind regards,
janning

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

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

Re: [GENERAL] logging connections

[surabhi.ahuja]

Title: RE: [GENERAL] logging connections







i did the above, however still no looging is being done.

for eg if i have a c++ program where i establish a connection to postgres and does some activity.

the postgres log should log the above connection to postgres when i run the program.

similarly say if i do psql database_name

this is also another connection, and shd get logged.

however by the changes u suggested, i am not able to do so.

thanks,
reagrds

surabhi

-Original Message-
From: [EMAIL PROTECTED] on behalf of Nikola Ivanov
Sent: Fri 1/20/2006 7:41 PM
To: surabhi.ahuja
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] logging connections

***
Your mail has been scanned by iiitb VirusWall.
***-***


You need to edit your postgresql.conf file and in the "Error reporting and
logging" section set "log_connections=true", "log_disconnections=true", and
"log_hostname=true"

On 1/20/06, surabhi.ahuja <[EMAIL PROTECTED]> wrote:
>
> i want to know, how i can log connections to postgres.
>
> the sample log file is:
> LOG:  shutting down
> LOG:  database system is shut down
> LOG:  database system was shut down at 2006-01-17 18:18:24 CST
> LOG:  checkpoint record is at 0/B035D0
> LOG:  redo record is at 0/B035D0; undo record is at 0/0; shutdown TRUE
> LOG:  next transaction ID: 1267; next OID: 17728
> LOG:  database system is ready
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  received fast shutdown request
> LOG:  aborting any active transactions
> FATAL:  terminating connection due to administrator command
> LOG:  shutting down
> LOG:  database system is shut down
> LOG:  database system was shut down at 2006-01-20 11:00:00 CST
> LOG:  checkpoint record is at 0/3C339CB4
> LOG:  redo record is at 0/3C339CB4; undo record is at 0/0; shutdown TRUE
> LOG:  next transaction ID: 2283381; next OID: 1159413
> LOG:  database system is ready
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
> LOG:  unexpected EOF on client connection
>
>
>
> however, i am still not able to know how to log into the above file,
> who has connected etc
>
>
> if i can log something like " LOG:  connection received: host=client1
> > port=3775"
>
> and also if i shut down postmaster, using pg_ctl stop, no logging takes
> place to the above log file.
>
> How to enable the above,
>
> thanks,
> regards
> Surabhi
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Tom Lane
> Sent: Fri 1/20/2006 3:13 AM
> To: Nik
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Connections not closing
>
> ***
> Your mail has been scanned by iiitb VirusWall.
> ***-***
>
>
> "Nik" <[EMAIL PROTECTED]> writes:
> > Ok, I simplified the problem. I tried just running psql from the
> > command line, and I noticed that it opens two connection on two
> > different ports, and it closes only one.
> > For example I do the following in the command prompt:
>
> > C:\> psql -h host_name -p 5432 -d db_name -U user_name
> > Password:
>
> > 2006-01-19 09:50:29 [unknown] LOG:  connection received: host=client1
> > port=3775
>
> > 2006-01-19 09:50:31 [unknown] LOG:  connection received: host=client1
> > port=3778
>
> > 2006-01-19 09:50:31 test LOG:  connection authorized: user=user_name
> > database=db_name
>
> It tries to connect, gets told it needs a password (the log verbosity
> level is not high enough to record the rejection), asks you for the
> password, and connects again.  I don't see anything funny here.
>
> 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] Numbers

[Martijn van Oosterhout]

On Sun, Jan 22, 2006 at 02:25:33PM -0500, Tom Lane wrote:
> I seem to recall that someone had come up with a datatype that would
> store numbers with units attached, which seems like what you want here.
> Check the PG list archives, and/or poke around on pgfoundry and gborg.

Hmm, I only just noticed this thread, but it might work for him. OTOH,
if feet are the only units he's interested in then it's slight
overkill. Also, it doesn't deal with alternate spellings (1 foot,2
feet). If you decided you would only use abbreviations that wouldn't
matter ofcourse.

http://svana.org/kleptog/pgsql/taggedtypes.html

Quick example:

test=# create table physics_units ( name text, abbrev text );
CREATE TABLE
test=# insert into physics_units values ('feet','ft');
INSERT 2205045 1
test=# insert into physics_units values ('metres','m');
INSERT 2205046 1
test=# select create_tagged_type( 'physics_type', 'float', 'physics_units' );
NOTICE:  type "physics_type" is not yet defined
DETAIL:  Creating a shell type definition.
NOTICE:  argument type physics_type is only a shell
 create_tagged_type 

   
(1 row)

test=# select 
create_tagged_operator('physics_type','+','physics_type','physics_type');
NOTICE:  +(physics_type,physics_type) => physics_type maps to +(double 
precision,double precision) => double precision
 create_tagged_operator 

   
(1 row)

test=# select create_tagged_operator('physics_type','*','float','physics_type');
NOTICE:  *(physics_type,double precision) => physics_type maps to *(double 
precision,double precision) => double precision
 create_tagged_operator 

   
(1 row)

test=# select '10 feet'::physics_type + '22 feet'::physics_type;
 ?column? 
--
 32 feet
(1 row)

test=# select '10 feet'::physics_type * 5;
 ?column? 
--
 50 feet
(1 row)

test=# select '10 feet'::physics_type + '3 metres'::physics_type;
ERROR:  Using tagged operator +(physics_type,physics_type) with incompatable 
tags (feet,metres)

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature