Re: [GENERAL] Pg schema diff tools Was: [ANNOUNCE] == PostgreSQL Weekly News - April 08 2007 ==

[Robert Treat]

On Monday 09 April 2007 05:35, Markus Schiltknecht wrote:
> Hi,
>
> David Fetter wrote in the weekly news:
> > Another PostgreSQL Diff Tool 1.0.0_beta20 released.
> > http://pgfoundry.org/projects/apgdiff/
>
> Why is it 'another' one? What others exist? (Specifically, are there
> ones, which don't depend on java?)
>

Theres this one which uses tcl:
https://sourceforge.net/projects/pgdiff

And this one which is written in perl:
http://gborg.postgresql.org/project/pgdiff/projdisplay.php

And this one which is commercial but actually works :-P
http://sqlmanager.net/en/products/postgresql/dbcomparer

And I think there are some others floating around... 

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

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

   http://archives.postgresql.org/

Re: [GENERAL] Errors during recovery of a postgres. Need some help understanding them...

[Tom Lane]

"Dhaval Shah" <[EMAIL PROTECTED]> writes:
> The question I have is, how far does it look behind in time?

I think you only need to hang onto the immediately preceding file;
it only backs up to the last applied WAL record, and that's certainly
not going to span multiple segment files.  The attempt to back up to the
last checkpoint isn't going to happen if you keep it from crashing at
the REDO DONE point.

regards, tom lane

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

Re: [GENERAL] Seg fault in pg_dump?

[Michael Nolan]

No, it isn't a debug-enabled build, and I don't think the debug symbols
package is installed on that system.  I've never used GDB, I'm more of an
'applications' programmer, I may need a 'crash' course on it, if you pardon
the pun.  However, if it's anything like the debugging tools I used to use
when programming in assembler, aeons ago, just relinking the program with
debug symbols in it could fix the problem.  :sigh:

This is on my new production system, I'm getting ready to migrate a backup
system over to 8.2.3, I may be able to do some additional testing there,
assuming I get the same failure.

It doesn't appear that dumpall has the same problem, or at least it didn't
abort when I dumped the entire cluster last night.  It doesn't fail when I
dump that entire database either, just when I dump some of the sequences.

What else can I do to help track this down?
--
Mike Nolan

On 4/5/07, Alvaro Herrera < [EMAIL PROTECTED]> wrote:


Michael Nolan escribió:
> GDB produces:
>
> Program received signal SIGSEGV, Segmentation fault.
> 0x0804fd6f in dumpSequence ()

Not very helpful -- what does it say if you ask for "bt"?  I'm thinking
this is not a debug-enabled build though.  I think you have to install a
separate RPM package in Fedora to get the debug symbols.

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

Re: [GENERAL] backend reset of database

[Tom Lane]

Geoffrey <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Well, this trace doesn't prove any such thing.  If it was a wild jump,
>> we can surmise that it landed someplace in the first few instructions of
>> FileRead (before the call to FileAccess), but there's no proof here that
>> it landed on-the-nose at the first instruction.

> So you're saying that the debugger will simply point to that function if 
> the address it 'jumps to' is somewhere in address space of this 
> function?  I don't know the intricacies of the debugger to understand 
> how all that works.  My expectation is that a random address is unlikely 
> to make any sense to the debugger.

It is a bit surprising that it didn't jump to a nonexistent address and
die immediately, but if you're on a little-endian machine then maybe
this isn't quite as improbable as it looks.  A stack clobber that writes
just a few more bytes than the intended buffer can hold might overwrite
just the first couple bytes of a return address, which are the LSBs on a
little-endian machine, leading to a return address somewhere within 64K
of where it should have been, which most likely is within the code area
rather than off in an unmapped range.  The fact that the code area is
only a small part of the address space doesn't change the odds if that's
what happened.

regards, tom lane

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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Robert Treat]

On Monday 09 April 2007 11:50, Erik Jones wrote:
> On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:
> > On Apr 9, 2007, at 10:09 AM, [EMAIL PROTECTED] wrote:
> >> It would be a really great service to this community if you would
> >> capture those issues and publish documentation (but feel free to
> >> change or omit the names to protect the incompetent^w innocent!).
> >
> > There's no incompetence involved... the guy is clearly not a
> > Postgres person, but overall an excellent DB designer.  He works on
> > extremely large databases at a large public university.
> >
> > The main one that comes to mind is that he suggested adding multi-
> > part primary indexes to keep the data ordered.  Apparently Oracle
> > and/or DB2 keep the data sorted by primary key index.  Since the
> > only reason was to keep the data sorted, the index would be useless
> > under Pg.
>
> You do have CLUSTER available for ordering a table on a single
> index.  However, after you do a CLUSTER new rows and updates don't
> respect that and you have to CLUSTER again periodically, but isn't
> difficult to add to a regular maintenance schedule/script.
>

There are a lot of scenarios where you really cant afford the penelty running 
a cluster entails (24/7 operation, 100gb tables, etc...)

> > Also, he recommended the use of 'index-only' tables -- eg, when the
> > table is just two or three integers, and the PK is a multi-part key
> > of all fields, it makes sense not to store the data twice.
> > However, in Pg you can't do that since visibility is only stored in
> > the data, not the index.
>

You can achieve the same effect with another version of clustered tables 
available in other databases, where the order is preserved when data is 
added/updated.  Of course we don't have that either. 

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

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

[GENERAL] Errors during recovery of a postgres. Need some help understanding them...

[Dhaval Shah]

Here is the situation:

I have a standby postgres which is fed a WAL File every 2 minutes.
Whenever it is fed a WAL file it logs the following:


---
LOG:  restored log file "00010070" from archive
pg_restore::copyWALFile: Moving
/opt/data/mirror/00010071 to pg_xlog/RECOVERYXLOG
LOG:  restored log file "00010071" from archive
pg_restore::copyWALFile: Moving
/opt/data/mirror/00010072 to pg_xlog/RECOVERYXLOG
LOG:  restored log file "00010072" from archive
...
...
pg_restore::copyWALFile: Moving
/opt/data/mirror/00010082 to pg_xlog/RECOVERYXLOG
LOG:  restored log file "00010082" from archive
---

I assume that the above situation is a happy postgres in a recovery
mode. The "copyWALFile" is my message in the serverlog.

After a while, the primary gives up. That is it goes down and I am not
able to pull any WAL file from the primary. So I tell the standby that
I do not have any WAL File to give.


LOG:  could not open file "pg_xlog/00010083" (log file
0, segment 131): No such file or directory
LOG:  redo done at 0/8200D280
Main: Triggering recovery
PANIC:  could not open file "pg_xlog/00010082" (log
file 0, segment 130): No such file or directory
---

The issue above is that I do not have the "001...0083" file and I
return a "file not found". Further when the postgres asks me about
"001...0082", I do not have that either, since in the intervening
minutes, I have moved that file out of my /opt/data/mirror to
/opt/data/tape directory for long term tape storage. So how do I make
my standby postgres happy?

Having run into that situation, the standby also spits out the following:

---
LOG:  could not open file "pg_xlog/00010082" (log file
0, segment 130): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file "pg_xlog/00010080" (log file
0, segment 128): No such file or directory
LOG:  invalid secondary checkpoint record
---

What is happening is that the postgres is looking behind in time for
the "0001...0082" and "0001...0080" files.

The question I have is, how far does it look behind in time? Then I
have to be careful of when I move the WAL file out to tape. Further if
I know how far back in time I have to keep my WAL file, then I can
device an effective strategy of removing older files. That is if I
come and say that I generate WAL file every 2 minutes, then do I keep
10 files or 120 files?

Any insight on this will help.

Regards
Dhaval

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

Re: [GENERAL] backend reset of database

[Geoffrey]

Tom Lane wrote:

Geoffrey <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

The trace is *really* suspicious given what you say here.  I'm inclined
to think that what is really happening is that something is jumping to
never-never land (via a clobbered function pointer or overwriting a
return address on the stack) and it just happens to end up in FileRead.
Unfortunately that guess doesn't provide much help for debugging it :-(


I just find it hard to believe that an address is getting clobbered or 
some such thing and we just happen to fall into the very beginning of a 
function.


Well, this trace doesn't prove any such thing.  If it was a wild jump,
we can surmise that it landed someplace in the first few instructions of
FileRead (before the call to FileAccess), but there's no proof here that
it landed on-the-nose at the first instruction.


So you're saying that the debugger will simply point to that function if 
the address it 'jumps to' is somewhere in address space of this 
function?  I don't know the intricacies of the debugger to understand 
how all that works.  My expectation is that a random address is unlikely 
to make any sense to the debugger.


I would also have expected that all the parms to the FileRead call would 
have been garbage, yet that does not seem to be the case:



#1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)


That's actually one of the things I find suspicious: AFAICS all the
calls to FileRead in the backend (and there aren't many) use a constant
BLCKSZ value for "amount".  So I doubt the 2 is an intentionally passed
parameter, it's more likely whatever happened to be in the relevant word
of the stack.


Thanks, that makes sense.  Now I've just got to figure out how it's 
'getting there' to start with. :)


--
Until later, Geoffrey

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

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

Re: [GENERAL] programmatic way to fetch latest release for a given major.minor version

[Listmail]

Here you go.

Fetches versions and prints most recent minor for each major
Tests all mirrors for speed and prints out the 4 fastest (takes some 
time)
http://www.crummy.com/software/BeautifulSoup/

Have a nice day !

#! /bin/env python
# -*- coding: utf-8  -*-

import urllib, BeautifulSoup, re, time, sys


def get_all_versions():
	soup =  
BeautifulSoup.BeautifulSoup( urllib.urlopen( "http://ftp3.fr.postgresql.org/pub/postgresql/source/";  
).read() )

for a in soup( 'a', {'href': re.compile( r"v\d+.\d+.\d+" ) } ):
		yield map( int, re.search( r"v(\d+)\.(\d+)\.(\d+)*", a['href']  
).groups() )


def get_latest_versions():
lastversions = {}
for a,b,c in sorted( get_all_versions() ):
lastversions[ (a,b) ] = c
return sorted( lastversions.items() )

def parse_query_string( url ):
	return dict( map( urllib.unquote_plus, pair.split('=',1) ) for pair in  
re.split( "&(?:amp;|)", urllib.splitquery( url )[1] ) )


def get_mirrors():
	soup =  
BeautifulSoup.BeautifulSoup( urllib.urlopen( "http://wwwmaster.postgresql.org/download/mirrors-ftp";  
).read() )

for a in soup( 'a', {'href': re.compile( r"\?setmir=.*url=" ) } ):
yield parse_query_string( a['href'] )['url']

def get_fastest_mirrors( urls, filename ):
for url in urls:
sys.stdout.write( "%s\r" % url )
t = time.time()
try:
urllib.urlopen( url + filename )
except:
pass
d = time.time()-t
print "%.02f s" % d
yield d, url

for major, minor in get_latest_versions():
print "%d.%d.%d" % (major[0], major[1], minor)

mirrors = get_mirrors()
fastest = sorted( get_fastest_mirrors( mirrors, "sync_timestamp" ))[:4]
for d, mirror in fastest:
print "%.02f s %s" % (d,mirror)









On Tue, 10 Apr 2007 00:34:02 +0200, Andrew Hammond  
<[EMAIL PROTECTED]> wrote:



On 4/9/07, CAJ CAJ <[EMAIL PROTECTED]> wrote:

On 9 Apr 2007 14:47:20 -0700, Andrew Hammond
<[EMAIL PROTECTED]> wrote:
> I'm writing a script that wants to know the latest release for a given
> major.minor version. Is there some better way than parsing
> http://www.postgresql.org/ftp/source/ or trying to
connect to ftp
> (which is invariably timing out on me today. Is that box getting
> hammered or something?) and doing the parsing that? Both approaches
> feel quite awkward to me.

Use wget to download via  HTTP (added recently).  Probably wise to add a
couple mirrors in your script.


I'm not asking how to download stuff. I'm asking how to figure out the
current release number for a given major.minor. I thought that was
clear in my original post, but I guess not. For example, how do I
determine (programmatically) the lastest version of 8.1.

I'm also interested in a clever way to select one of the "close"
mirrors at random for downloading via http. However I had planned to
hold that question until I'd solved the first issue.

Andrew

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

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




---(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] programmatic way to fetch latest release for a given major.minor version

[Alvaro Herrera]

Andrew Hammond escribió:
> On 4/9/07, CAJ CAJ <[EMAIL PROTECTED]> wrote:
> >On 9 Apr 2007 14:47:20 -0700, Andrew Hammond
> ><[EMAIL PROTECTED]> wrote:
> >> I'm writing a script that wants to know the latest release for a given
> >> major.minor version. Is there some better way than parsing
> >> http://www.postgresql.org/ftp/source/ or trying to
> >connect to ftp
> >> (which is invariably timing out on me today. Is that box getting
> >> hammered or something?) and doing the parsing that? Both approaches
> >> feel quite awkward to me.
> >
> >Use wget to download via  HTTP (added recently).  Probably wise to add a
> >couple mirrors in your script.
> 
> I'm not asking how to download stuff. I'm asking how to figure out the
> current release number for a given major.minor. I thought that was
> clear in my original post, but I guess not. For example, how do I
> determine (programmatically) the lastest version of 8.1.

Maybe get configure.in from CVS:
cvs log configure.in
and parse the "symbolic names" list?

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

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

   http://archives.postgresql.org/

Re: [GENERAL] programmatic way to fetch latest release for a given major.minor version

[Andrew Hammond]

On 4/9/07, CAJ CAJ <[EMAIL PROTECTED]> wrote:

On 9 Apr 2007 14:47:20 -0700, Andrew Hammond
<[EMAIL PROTECTED]> wrote:
> I'm writing a script that wants to know the latest release for a given
> major.minor version. Is there some better way than parsing
> http://www.postgresql.org/ftp/source/ or trying to
connect to ftp
> (which is invariably timing out on me today. Is that box getting
> hammered or something?) and doing the parsing that? Both approaches
> feel quite awkward to me.

Use wget to download via  HTTP (added recently).  Probably wise to add a
couple mirrors in your script.


I'm not asking how to download stuff. I'm asking how to figure out the
current release number for a given major.minor. I thought that was
clear in my original post, but I guess not. For example, how do I
determine (programmatically) the lastest version of 8.1.

I'm also interested in a clever way to select one of the "close"
mirrors at random for downloading via http. However I had planned to
hold that question until I'd solved the first issue.

Andrew

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

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

Re: [GENERAL] programmatic way to fetch latest release for a given major.minor version

[CAJ CAJ]

On 9 Apr 2007 14:47:20 -0700, Andrew Hammond <
[EMAIL PROTECTED]> wrote:


I'm writing a script that wants to know the latest release for a given
major.minor version. Is there some better way than parsing
http://www.postgresql.org/ftp/source/ or trying to connect to ftp
(which is invariably timing out on me today. Is that box getting
hammered or something?) and doing the parsing that? Both approaches
feel quite awkward to me.



Use wget to download via  HTTP (added recently).  Probably wise to add a
couple mirrors in your script.

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

[Brent Wood]

Lorenzo Thurman wrote:
I have three tables using date fields. I want to retrieve the oldest 
date contained in the tables. Can someone show me an example of a 
query that would do that?
You could write a custom function doing the same sort of thing, or 
(perhaps more portable) use generic sqls & views like:


create view min_dates as
select min(date1) from table1as date_1
union
select min(date2) from table2 as date_1
union 
select min(date3) from table3 as date_1;


then either:

select min(date_1) from min_dates;


or

create view min_date as
select min(date_1) as min_date
from min_dates;

then just:

select min_date from min_date;



Cheers

  Brent Wood

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

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

[GENERAL] programmatic way to fetch latest release for a given major.minor version

[Andrew Hammond]

I'm writing a script that wants to know the latest release for a given
major.minor version. Is there some better way than parsing
http://www.postgresql.org/ftp/source/ or trying to connect to ftp
(which is invariably timing out on me today. Is that box getting
hammered or something?) and doing the parsing that? Both approaches
feel quite awkward to me.

Andrew


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

Re: [GENERAL] How to disable duplicate columns

[Stephan Szabo]

On Sun, 8 Apr 2007, Andrus wrote:

> I have tables with large number of columns some of which are duplicate.
> I need to use
>
> SELECT t1.*, t2.* FROM t1 join  t2 using (t)
>
> since I don't know all column names of t1 and t2 tables at design time.
>
> In this case PostgreSQL returns table with duplicate columns.
> How to force Postgres to return only first table column  when second table
> contains column with same name?

There are a few cases where duplicate columns are trimmed, such as select
* from something with a join ...  using or natural join only should result
in one output column for the joined upon column names.

> Code to reproduce:
>
> create table t1 ( id integer
>   /*, a lot of other columns */ );
> create table t2 ( id integer
>   /*, a lot of other columns */ );
> create table t3 as select t1.*,t2.* from t1 join t2 using (id);

If only id were duplicated, then select * from t1 join t2 using(id)
should work.  If other columns are duplicated, then that won't work, but
generally just choosing the first column with a name seems bizarre in that
case.

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

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

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

[Merlin Moncure]

On 4/9/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Dann Corbit" <[EMAIL PROTECTED]> writes:
>> [EMAIL PROTECTED] On Behalf Of Lorenzo Thurman
>> I have three tables using date fields. I want to retrieve the oldest
>> date contained in the tables. Can someone show me an example of a query
>> that would do that?

> Just do a union and return the min

That's probably not enough detail for a newbie ...

SELECT min(x) FROM
(
 SELECT min(datecol1) AS x FROM table1
 UNION ALL
 SELECT min(datecol2) AS x FROM table2
 UNION ALL
 SELECT min(datecol3) AS x FROM table3
) ss;

Exercise for newbie: which of the AS clauses are redundant?


they are all unnecessary :D

select least
(
 (SELECT min(datecol1) FROM table1),
 (SELECT min(datecol2) FROM table2),
 (SELECT min(datecol3) FROM table3)
);

merlin

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

Re: [GENERAL] Postgres Crash Running PLPGSQL Function on 8.2.3

[Tom Lane]

"Gary Winslow" <[EMAIL PROTECTED]> writes:
> I would be happy to provide a "self-contained" test case.  I would have
> to send to you a pg_dump of it which would be rather large to send via
> email.  Some of the code includes confidential business specific calc
> rules that I cannot release to the general public.

Understood; it won't go anywhere.  But how large is "rather large"?
Can you duplicate the problem with no (or less) data?

regards, tom lane

---(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] Can you combine text variable together to referenece a VIEW name ?

[Tom Lane]

"Raymond O'Donnell" <[EMAIL PROTECTED]> writes:
> On 09/04/2007 00:36, Bigjim wrote:
>> I am trying to combine 2 text variable togther to form the name of a
>> VIEW.  example
>> SELECT * FROM ( 'april'||'may') ;

> I think you want to use EXECUTE from within a pl/pgsql function to 
> construct and execute a query dynamically - have a look at this:
> http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

That's the only way to do it (modulo that you can do this in any of the
PLs not only plpgsql) ... but a more general point is that the OP is
trying to swim against the tide.  Almost certainly, rethinking the
design of those views is called for: try to merge them into one view.

regards, tom lane

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

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

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

[Tom Lane]

"Dann Corbit" <[EMAIL PROTECTED]> writes:
>> [EMAIL PROTECTED] On Behalf Of Lorenzo Thurman
>> I have three tables using date fields. I want to retrieve the oldest
>> date contained in the tables. Can someone show me an example of a query
>> that would do that?

> Just do a union and return the min

That's probably not enough detail for a newbie ...

SELECT min(x) FROM
(
 SELECT min(datecol1) AS x FROM table1
 UNION ALL
 SELECT min(datecol2) AS x FROM table2
 UNION ALL
 SELECT min(datecol3) AS x FROM table3
) ss;

Exercise for newbie: which of the AS clauses are redundant?

regards, tom lane

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

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

Re: [GENERAL] join by char(16) or by bytea?

[Tom Lane]

"Sergei Shelukhin" <[EMAIL PROTECTED]> writes:
> Due to limitations of both pg and php I gave up on the attempts to
> make bigint keys work;

Which limitations would those be?

> I have a choice between 16-byte character
> strings or "8-byte" bytea (decode(string_from_above, "hex")) for an
> index column for a table.
> Which one will be faster for exact matches and joins?

bytea, likely, especially if you are using a non-C locale.  In C locale
the charstring comparisons would degenerate to memcmp and probably not
be measurably different from bytea, but in other locales strcoll is
used and that can be pretty slow.  OTOH, the pain-in-the-neck factor
for using decode in all your queries seems pretty bad, so I'm really
wondering what problem you've got with bigint.

regards, tom lane

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

Re: [GENERAL] Problem with copying data

[Tom Lane]

Alan Hodgson <[EMAIL PROTECTED]> writes:
> On Friday 06 April 2007 13:17, Klaas Dellschaft <[EMAIL PROTECTED]> 
> wrote:
>> I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
>> "COPY FROM" into my database. But I'm waiting for the completion of this
>> job since more than 24h. I'm working under Linux and with "top" I can
>> see the two processes which should copy the data but most of the time
>> they are not working. Very seldom they are using some CPU time and then
>> get idle again.

> What does the wait % (%wa) say when they are "idle"?  I would generally 
> assume you're io-bound on a large COPY, especially if the target table is 
> already indexed.  24-hours seems excessive, though, unless this is a 
> notebook drive or something.

If there's other things going on in the database, then another
possibility is that the COPY commands are blocked on locks.
I agree that I/O is the most likely time sink though.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] backend reset of database

[Tom Lane]

Geoffrey <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The trace is *really* suspicious given what you say here.  I'm inclined
>> to think that what is really happening is that something is jumping to
>> never-never land (via a clobbered function pointer or overwriting a
>> return address on the stack) and it just happens to end up in FileRead.
>> Unfortunately that guess doesn't provide much help for debugging it :-(

> I just find it hard to believe that an address is getting clobbered or 
> some such thing and we just happen to fall into the very beginning of a 
> function.

Well, this trace doesn't prove any such thing.  If it was a wild jump,
we can surmise that it landed someplace in the first few instructions of
FileRead (before the call to FileAccess), but there's no proof here that
it landed on-the-nose at the first instruction.

> I would also have expected that all the parms to the FileRead call would 
> have been garbage, yet that does not seem to be the case:

> #1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)

That's actually one of the things I find suspicious: AFAICS all the
calls to FileRead in the backend (and there aren't many) use a constant
BLCKSZ value for "amount".  So I doubt the 2 is an intentionally passed
parameter, it's more likely whatever happened to be in the relevant word
of the stack.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] 8.2.3 AutoVacuum not running

[Matthew T. O'Connor]

OK, do you have the stats system enabled also?  You require at least 
row_level stats for autovacuum to work.



Schwenker, Stephen wrote:
I'm sure.  That column is null for all tables in my databases accept for 
the few tables that I've vacuumed manually.
 
Any other suggestions?  :)



*From:* Tom Lane [mailto:[EMAIL PROTECTED]
*Sent:* Fri 06/04/2007 1:21 PM
*To:* Schwenker, Stephen
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] 8.2.3 AutoVacuum not running

"Schwenker, Stephen" <[EMAIL PROTECTED]> writes:
 > I've just compiled an instance of Postgresql 8.2.3 on a new linux box
 > and have added some databases to it.  I've noticed however that the
 > autovacuum is not running.

How sure are you of that?  Check pg_stat_all_tables to see if the
last_autovacuum column is changing.

regards, tom lane



---(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] How to disable duplicate columns

[Merlin Moncure]

On 4/8/07, Andrus <[EMAIL PROTECTED]> wrote:

I have tables with large number of columns some of which are duplicate.
I need to use

SELECT t1.*, t2.* FROM t1 join  t2 using (t)

since I don't know all column names of t1 and t2 tables at design time.

In this case PostgreSQL returns table with duplicate columns.
How to force Postgres to return only first table column  when second table
contains column with same name?


use can use a natural join:

select * from t1 natural join t2;

this will essentially look for columns that are in both tables, and do
an implicit 'using' on those columns.

be warned, this can lead to surprising behavior -- it only works if
you *want* identically named fields to be part of the join condition,
otherwise the results are undefined.

merlin

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

Re: [GENERAL] plperl "set-valued function" problem

[Alvaro Herrera]

samı escribió:
> Hi
> 
> I am using PostgreSQL 8.2.3 on WindowsXP
> 
> I am using the PL/Perl regression tests located here:
> 
>  
> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/test/Attic/test_queries.sql

The fact that these files are in the "Attic" means they were deleted.
Try the files that actually exist:

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/sql/
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/expected/

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

---(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] 8.2.3 AutoVacuum not running

[Alvaro Herrera]

Schwenker, Stephen wrote:
> Hey,
>  
> I've also notice one difference between my 8.1 instance and my 8.2
> instance.  I run a ps and on the 8.1 instance there is a 'stats buffer
> process' and in the 8.2 instance there is no 'stats buffer instance'
>  
> Does that give you anymore reasons as to why the autovacuum is not working?

No -- the stats buffer process was removed in 8.2 on purpose.

If you do a "show autovacuum", does it show as on?  Maybe it was
disabled due to misconfiguration.

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

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

   http://archives.postgresql.org/

Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

[Dann Corbit]

Just do a union and return the min

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Lorenzo Thurman
> Sent: Monday, April 09, 2007 7:32 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] NEWBIE: How do I get the oldest date contained in 3
> tables
> 
> I have three tables using date fields. I want to retrieve the oldest
> date contained in the tables. Can someone show me an example of a
query
> that would do that?
> TIA
> 
> ---(end of
broadcast)---
> TIP 6: explain analyze is your friend

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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Bill Moran]

In response to Alexander Staubo <[EMAIL PROTECTED]>:

> On Apr 9, 2007, at 18:10 , Gerard Seibert wrote:
> 
> > On Mon, 9 Apr 2007 10:34:22 -0500
> > Erik Jones <[EMAIL PROTECTED]> wrote:
> >
> >> Hmmm...  I didn't have anything HTML set anywhere.  I did however
> >> have message formatting set to Rich-Text (although I was unaware)
> >> and have switched that to plaintext.  This look better?
> >
> > Yes, much better. I believe that 'rich text' is essentially HTML,
> > although I might be mistaken. I know that 'GMail' users have that
> > problem all the time. Of course 'GMail' users have lots of other
> > problems also.
> 
> Erik has actually been posting messages as "multipart/alternative",  
> where the message is included in both 7-bit plaintext *and* HTML, the  
> idea being that the mail viewer itself can pick the format it knows  
> best. You are simply using a mail reader which prioritizes HTML;  
> perhaps it has a setting to let you prefer plaintext?


Sylpheed has this option, which is one of the reasons I use it.


-- 
Bill Moran
http://www.potentialtech.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] Can you combine text variable together to referenece a VIEW name ?

[Raymond O'Donnell]

On 09/04/2007 00:36, Bigjim wrote:


I am trying to combine 2 text variable togther to form the name of a
VIEW.  example

>

   SELECT * FROM ( 'april'||'may') ;


I think you want to use EXECUTE from within a pl/pgsql function to 
construct and execute a query dynamically - have a look at this:


http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

HTH,

Ray.


---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

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

Re: [GENERAL] Problem with copying data

[Alvaro Herrera]

Klaas Dellschaft wrote:
> Hi,
> 
> I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a 
> "COPY FROM" into my database. But I'm waiting for the completion of this 
> job since more than 24h. I'm working under Linux and with "top" I can 
> see the two processes which should copy the data but most of the time 
> they are not working. Very seldom they are using some CPU time and then 
> get idle again.

Are there indexes or foreign keys in the tables?  Check constraints?
Other things we should know about?

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

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

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

[GENERAL] How to disable duplicate columns

[Andrus]

I have tables with large number of columns some of which are duplicate.
I need to use

SELECT t1.*, t2.* FROM t1 join  t2 using (t)

since I don't know all column names of t1 and t2 tables at design time.

In this case PostgreSQL returns table with duplicate columns.
How to force Postgres to return only first table column  when second table 
contains column with same name?

Code to reproduce:

create table t1 ( id integer
  /*, a lot of other columns */ );
create table t2 ( id integer
  /*, a lot of other columns */ );
create table t3 as select t1.*,t2.* from t1 join t2 using (id);

Observed:

Error: Column name is duplicated

Expected:

t3 table should contain single id column

Andrus.



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

[GENERAL] Re: Can you combine text variable together to referenece a VIEW name ?

[Lew]

Bigjim wrote:

Hi there,
I am trying to combine 2 text variable togther to form the name of a
VIEW.  example


   SELECT * FROM ( 'april'||'may') ;


The table (or view) part is not a character string but an identifier.

--
Lew

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

Re: [GENERAL] backend reset of database

[Geoffrey]

Tom Lane wrote:

Magnus Hagander <[EMAIL PROTECTED]> writes:

Geoffrey wrote:

CREATE OR REPLACE FUNCTION pcm_getmiles_s(text,text,integer)
RETURNS float8 AS '/esc/pgrnd/prog/libpcmiler'
LANGUAGE 'c' WITH (isStrict);



Is this function actually a PostgreSQL callable function? Or is it just
a general C function along the line of
float pcm_getmiles_s(char*, char*, int)
?


The code would never have worked at all if that were the case (since
text* is not like char*).  I suspect some subtler portability issue in
Geoffrey's glue functions.  It seems unlikely that moving from PG 7.4.13
to 7.4.16 would in itself have exposed such a problem, but the update in
operating environment from RHEL3 to RHEL4 might've.


This is an interesting point.  I might try to see if I can duplicate the 
problem on a RH3 box.


We'll take a good hard look at our code to see if we have some sloppy 
pointers or string length issues.


--
Until later, Geoffrey

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

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

Re: [GENERAL] perl DBI: problems searching text strings with ' symbol (es d'ambrose)

[filippo]

On 7 Apr, 10:13, "filippo" <[EMAIL PROTECTED]> wrote:

solved, it is a DBD::PgPP bug. I changed to DBD::Pg, now it works fine.


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

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

Re: [GENERAL] 8.2.3 AutoVacuum not running

[Schwenker, Stephen]

Hey,  I've just found something else.  When I compiled postgresql, I assumed 
that because it compiled then it was a supported system.  This was on the first 
line of the configure output.
 
checking build system type... x86_64-unknown-linux-gnu
 
I've looked at the supported systems list and the x86_64 doesn't appear to be 
supported.  Can someone help me figure out how to make that system type to get 
supported?  Maybe I can turn on some debugging to figure out why it's not 
running.  I'm willing to put in some time to figure it out.
 
Thank you,
 
 
Steve.



From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Fri 06/04/2007 1:21 PM
To: Schwenker, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running 



"Schwenker, Stephen" <[EMAIL PROTECTED]> writes:
> I've just compiled an instance of Postgresql 8.2.3 on a new linux box
> and have added some databases to it.  I've noticed however that the
> autovacuum is not running.

How sure are you of that?  Check pg_stat_all_tables to see if the
last_autovacuum column is changing.

regards, tom lane

[GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables

[Lorenzo Thurman]

I have three tables using date fields. I want to retrieve the oldest 
date contained in the tables. Can someone show me an example of a query 
that would do that?

TIA

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

Re: [GENERAL] Postgres Crash Running PLPGSQL Function on 8.2.3

[Gary Winslow]

Tom Lane,

 

I would be happy to provide a "self-contained" test case.  I would have
to send to you a pg_dump of it which would be rather large to send via
email.  Some of the code includes confidential business specific calc
rules that I cannot release to the general public. 

 

How can I provide this to you more directly?

 

Gary Winslow

 

Re: [GENERAL] Problem with copying data

[Alan Hodgson]

On Friday 06 April 2007 13:17, Klaas Dellschaft <[EMAIL PROTECTED]> 
wrote:
> Hi,
>
> I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
> "COPY FROM" into my database. But I'm waiting for the completion of this
> job since more than 24h. I'm working under Linux and with "top" I can
> see the two processes which should copy the data but most of the time
> they are not working. Very seldom they are using some CPU time and then
> get idle again.


What does the wait % (%wa) say when they are "idle"?  I would generally 
assume you're io-bound on a large COPY, especially if the target table is 
already indexed.  24-hours seems excessive, though, unless this is a 
notebook drive or something.


-- 
99 percent of lawyers give the rest a bad name


---(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] Documenting PGSQL database.

[Stefan Berglund]

On Sat, 7 Apr 2007 12:07:44 -0700 (PDT), [EMAIL PROTECTED] (RPK)
wrote:
 in <[EMAIL PROTECTED]> 

>
>I am using PGSQL 8.2.3 on Windows XP. I want to know whether there is any way
>of documenting the schema of PGSQL database so that I can get a printed copy
>of the same.

Björn's tool looks like your best bet, but I'd like to add that the best
way to do this to never create your database(s) other than via script
and then you automatically have what you need by default.  :-)

---
Stefan Berglund

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

Re: [GENERAL] 8.2.3 AutoVacuum not running

[Schwenker, Stephen]

I'm sure.  That column is null for all tables in my databases accept for the 
few tables that I've vacuumed manually.
 
Any other suggestions?  :)



From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Fri 06/04/2007 1:21 PM
To: Schwenker, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running 



"Schwenker, Stephen" <[EMAIL PROTECTED]> writes:
> I've just compiled an instance of Postgresql 8.2.3 on a new linux box
> and have added some databases to it.  I've noticed however that the
> autovacuum is not running.

How sure are you of that?  Check pg_stat_all_tables to see if the
last_autovacuum column is changing.

regards, tom lane

Re: [GENERAL] pg_live

[Kev]

> 1) If I use the 'check CD' option, it tests everything and then
> reports two failed checks.

This is strange...I tried the same CD at work this weekend on an IBM
workstation, and although the check also fails at two spots, if I try
it anyway, it appears to work flawlessly.  I can jump on a
neighbouring computer and start creating tables and such.  Very cool!

Still not sure why my home PC doesn't like it though...

Kev


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

Re: [GENERAL] 8.2.3 AutoVacuum not running

[Schwenker, Stephen]

Hey,
 
I've also notice one difference between my 8.1 instance and my 8.2 instance.  I 
run a ps and on the 8.1 instance there is a 'stats buffer process' and in the 
8.2 instance there is no 'stats buffer instance'
 
Does that give you anymore reasons as to why the autovacuum is not working?
 
:)
 
 



From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Fri 06/04/2007 1:21 PM
To: Schwenker, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running 



"Schwenker, Stephen" <[EMAIL PROTECTED]> writes:
> I've just compiled an instance of Postgresql 8.2.3 on a new linux box
> and have added some databases to it.  I've noticed however that the
> autovacuum is not running.

How sure are you of that?  Check pg_stat_all_tables to see if the
last_autovacuum column is changing.

regards, tom lane

[GENERAL] plperl "set-valued function" problem

[samı]

Hi

I am using PostgreSQL 8.2.3 on WindowsXP

I am using the PL/Perl regression tests located here:

 
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/test/Attic/test_queries.sql

and comparing them to the expected output located here:

 
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/test/Attic/test.expected

==
The following function call gives an error :

CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $
$
return undef;
$$ LANGUAGE plperl;

> SELECT perl_set_int(5);
"ERROR:  set-valued function called in context that cannot accept a
set"


The .expected file above for the regression tests indicates that the
test should be successful:

SELECT perl_set_int(5);
 perl_set_int
--
(0 rows)



Did I miss anything? Is this problem known or is this not a problem at
all?

Thanks

Sami


---(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] newid() in postgres

[marcel.beutner]

Thanks,
I'll try to implement it.

Marcel


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

   http://archives.postgresql.org/

Re: [GENERAL] backend reset of database

[Geoffrey]

Tom Lane wrote:

Geoffrey <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

Is pcmiler a Postgres-specific backend extension?


No.  It's a standalone application that provides an api to build calls 
into there product from other applications.  What we've done is created 
functions that are called from the database that use their api.


Did you recompile/relink these glue functions when moving up to the new
version?  Are they built with debugging enabled?


What we are building is built with debugging, but the libraries provided 
by pcmiler do not have debugging symbols.



The trace is *really* suspicious given what you say here.  I'm inclined
to think that what is really happening is that something is jumping to
never-never land (via a clobbered function pointer or overwriting a
return address on the stack) and it just happens to end up in FileRead.
Unfortunately that guess doesn't provide much help for debugging it :-(


I just find it hard to believe that an address is getting clobbered or 
some such thing and we just happen to fall into the very beginning of a 
function.


I would also have expected that all the parms to the FileRead call would 
have been garbage, yet that does not seem to be the case:


#1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)

One other note regarding this issue.  We have built standalone programs 
that execute from the shell to call the same routines from the third 
party api and they work.


The point at which the actual failure occurs on the code is the point at 
which the process attempts to connect to the pcmiler server process.


--
Until later, Geoffrey

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

---(end of broadcast)---
TIP 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] Can you combine text variable together to referenece a VIEW name ?

[Bigjim]

Hi there,
I am trying to combine 2 text variable togther to form the name of a
VIEW.  example


   SELECT * FROM ( 'april'||'may') ;

I have a 12 different views and I want to be able to select a
different view depending on the contents of 2 fields in a database.

Has anyone done this before or have any ideas on how to do it ?

Thanks


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

[GENERAL] join by char(16) or by bytea?

[Sergei Shelukhin]

One more newbie question.
Due to limitations of both pg and php I gave up on the attempts to
make bigint keys work; I have a choice between 16-byte character
strings or "8-byte" bytea (decode(string_from_above, "hex")) for an
index column for a table.

Which one will be faster for exact matches and joins? Tables will be
big,
there will be no range queries and no partial matching.

Again I cannot test it because I lack production data, I wonder if
it's common knowledge?


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

[GENERAL] Problem with copying data

[Klaas Dellschaft]

Hi,

I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
"COPY FROM" into my database. But I'm waiting for the completion of this
job since more than 24h. I'm working under Linux and with "top" I can
see the two processes which should copy the data but most of the time
they are not working. Very seldom they are using some CPU time and then
get idle again.

I already tried restarting the Postgres server and I also restarted
Linux but nothing seems to work. Do you have any hints how I can find
out what's going on there and why the two processes are idle most of the
time?

Thanks,
Klaas


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

  http://archives.postgresql.org/

[GENERAL] Index "misbehavior" in PostgreSQL 8.2.2?

[Tonnerre LOMBARD]

Salut,

I have four tables, two of which are relevant for this:

searchengine=# \d websites
Table "public.websites"
   Column   |   Type   |   Modifiers
   
+--+---
 id | bigint   | not null default 
nextval('websites_id_seq'::regclass)
 url| text | not null
 title  | text | 
 abstract   | text | 
 lastindex  | timestamp with time zone | 
 authority  | bigint   | not null default 0
 failed | boolean  | not null default false
 spamminess | smallint | not null default 0
Indexes:
"websites_pkey" PRIMARY KEY, btree (id)
"websites_url_key" UNIQUE, btree (url)
"websites_authority_key" btree (authority)
"websites_failed_key" btree (failed)
"websites_lastindex_key" btree (lastindex)
"websites_spamminess_key" btree (spamminess)

searchengine=# SELECT pg_size_pretty (pg_relation_size ('websites'));
 pg_size_pretty 

 293 MB
(1 row)

searchengine=# SELECT COUNT(*) FROM websites;
 count

 828778
(1 row)

searchengine=# \d sitewords
Table "public.sitewords"
   Column   |  Type  | Modifiers 
++---
 id_site| bigint | not null
 id_keyword | bigint | not null
 count  | bigint | not null
 ratio  | bigint | 
Indexes:
"sitewords_key" UNIQUE, btree (id_site, id_keyword)
"sitewords_id_keyword_key" btree (id_keyword)
"sitewords_id_site_key" btree (id_site)

searchengine=# SELECT COUNT(*) FROM sitewords;
  count
--
 46696260
(1 row)

searchengine=# SELECT pg_size_pretty (pg_relation_size ('sitewords'));
 pg_size_pretty 

 3041 MB
(1 row)

searchengine=# 

My effective_cache_size is set to 768MB.

I am trying to run a query:

searchengine=# EXPLAIN ANALYZE SELECT COUNT(me.id_keyword) * SUM(ratio) AS 
relevance, id_site FROM sitewords me JOIN websites ws ON me.id_site = ws.id 
WHERE me.id_keyword IN (4241,28303) GROUP BY id_site ORDER BY relevance DESC 
LIMIT 10;

QUERY PLAN  
  
--
 Limit  (cost=150319.90..150319.91 rows=3 width=24) (actual 
time=9211.903..9212.013 rows=10 loops=1)
   ->  Sort  (cost=150319.90..150319.91 rows=3 width=24) (actual 
time=9211.895..9211.931 rows=10 loops=1)
 Sort Key: ((count(me.id_keyword))::numeric * sum(me.ratio))
 ->  HashAggregate  (cost=150319.81..150319.87 rows=3 width=24) (actual 
time=9180.744..9197.080 rows=3320 loops=1)
   ->  Hash Join  (cost=60470.11..150116.47 rows=27112 width=24) 
(actual time=7816.860..9151.601 rows=5747 loops=1)
 Hash Cond: (me.id_site = ws.id)
 ->  Bitmap Heap Scan on sitewords me  
(cost=609.31..85356.43 rows=27112 width=24) (actual time=3.245..137.026 
rows=5748 loops=1)
   Recheck Cond: (id_keyword = ANY 
('{4241,28303}'::bigint[]))
   ->  Bitmap Index Scan on sitewords_id_keyword_key  
(cost=0.00..602.53 rows=27112 width=0) (actual time=2.169..2.169 rows=5748 
loops=1)
 Index Cond: (id_keyword = ANY 
('{4241,28303}'::bigint[]))
 ->  Hash  (cost=45674.80..45674.80 rows=816080 width=8) 
(actual time=7786.940..7786.940 rows=829078 loops=1)
   ->  Seq Scan on websites ws  (cost=0.00..45674.80 
rows=816080 width=8) (actual time=0.026..4126.069 rows=829078 loops=1)
 Total runtime: 9212.679 ms
(13 rows)

searchengine=# 

The only way to get PostgreSQL to use the index appears to be to set the
random_page_cost to 1.0 (1.1 also doesn't work). In that case, I get:

searchengine=# EXPLAIN ANALYZE SELECT COUNT(me.id_keyword) * SUM(ratio) AS 
relevance, id_site FROM sitewords me JOIN websites ws ON me.id_site = ws.id 
WHERE me.id_keyword IN (4241,28303) GROUP BY id_site ORDER BY relevance DESC 
LIMIT 10;

QUERY PLAN  
  
--
 Limit  (cost=78647.65..78647.66 rows=3 width=24) (actual time=418.368..418.478 
rows=10 loops=1)
   ->  Sort  (cost=78647.65..78647.66 rows=3 width=24) (actual 
time=418.359..418.395 rows=10 loops=1)
 Sort Key: ((count(me.id_keyword))::numeric * sum(me.ratio))
 ->  HashAggregate  (cost=78647.57..7864

Re: [GENERAL] YTA Time Zone Question

[Danny E. Armstrong]

Thanks very much, my issue was addressed exactly. 
My implicit assumption that inserts of timestamps are not formatted by
local time (db time zone setting) in the absence of an explicit time
zone identifier was incorrect. Ok, it seems obvious now. TIMESTAMPTZ is
your friend.

The solution is to rewrite my function that converts the unix timestamp:

SELECT TIMESTAMPtz 'epoch' + INTERVAL '1170728520 seconds';

-Danny

-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 06, 2007 9:38 AM
To: Danny E. Armstrong
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] YTA Time Zone Question

On Thu, Apr 05, 2007 at 05:52:02PM -0700, Danny Armstrong wrote:
> If ruby and python tell me the value I just inserted into the db,
> 1174773136, is Sat Mar 24 21:52:16 UTC 2007, then I expect that
> 
> set time zone 0; -- format as though I'm in utc
> select measurement_time
> from table
> 
> will also yield that time. 
> 
> Instead it yields "2007-03-25 04:52:16+00", which means it interprets
> the original value as local time (I'm PDT), and then formats it as UTC
> by adding 7 hours to it.

Turn on statement logging and see what the Ruby and Python drivers
are executing.  Here's an example Ruby script:

require 'dbi'
require 'time'
t = Time.parse('Sat Mar 24 21:52:16 UTC 2007')
dbh = DBI.connect('dbi:Pg:dbname=test', 'user', 'password')
dbh.do('INSERT INTO foo (t1, t2) VALUES (?, ?)', t, t.to_s)
dbh.disconnect

When I run this script the database logs the following:

INSERT INTO foo (t1, t2) VALUES ('2007-03-24 21:52:16', 'Sat Mar 24
21:52:16 UTC 2007')

Notice that the first value (of class Time) is sent without a
timezone; the database therefore interprets it according to the
database's timezone setting (US/Pacific in my test environment).
The second value (of type String) includes the timezone so the
database interprets it as expected.

test=> SET timezone TO 'UTC';
test=> SELECT t1, t2 FROM foo;
   t1   |   t2   
+
 2007-03-25 04:52:16+00 | 2007-03-24 21:52:16+00
(1 row)

test=> SET timezone TO 'US/Pacific';
test=> SELECT t1, t2 FROM foo;
   t1   |   t2   
+
 2007-03-24 21:52:16-07 | 2007-03-24 14:52:16-07
(1 row)

The Python driver you're using might behave the same way.  I'd
suggest contacting driver authors.

-- 
Michael Fuhr

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

Re: [GENERAL] perl DBI: problems searching text strings with ' symbol (es d'ambrose)

[filippo]

On 7 Apr, 01:26, "filippo" <[EMAIL PROTECTED]> wrote:
> hello,
>
> if I try to insert text like
>
> $dbh->quote(qq/d'ambrose/);
>
> when I try to search names with character lile ', I get this error

I forgot to say that I need LIKE and %. I tried to use placeholders
but nothing changes.


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

[GENERAL] Problem with copying data

[Klaas Dellschaft]

Hi,

I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a 
"COPY FROM" into my database. But I'm waiting for the completion of this 
job since more than 24h. I'm working under Linux and with "top" I can 
see the two processes which should copy the data but most of the time 
they are not working. Very seldom they are using some CPU time and then 
get idle again.


I already tried restarting the Postgres server and I also restarted 
Linux but nothing seems to work. Do you have any hints how I can find 
out what's going on there and why the two processes are idle most of the 
time?


Thanks,
Klaas

---(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] PlPg/SQL mulit-array as a parameter?

[Simon István]

How can i use multi-array parameter in PlPg/SQL?

This array want to put in parameter.
[[0, 1, 3, 0, 3, 4], [7, 12, 1, 9, 1, 2], [12, 24, 20, 42, 43, 44],
[24, 103, 65, 113, 72, 25], [179, 357, 335, 348, 309, 138], [3901,
3491, 4326, 878, 3785, 450]]


---(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] perl DBI: problems searching text strings with ' symbol (es d'ambrose)

[filippo]

hello,

if I try to insert text like

$dbh->quote(qq/d'ambrose/);

when I try to search names with character lile ', I get this error

DBD::PgPP::st execute failed: Unknown message type: '­' at C:/Perl/
site/lib/DBD/
PgPP.pm line 730

I really need to store and select names with ' character, how can I
do?

Thanks,

Filippo


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

Re: [GENERAL] perl DBI: problems searching text strings with ' symbol (es d'ambrose)

[filippo]

On 7 Apr, 01:47, "filippo" <[EMAIL PROTECTED]> wrote:
> On 7 Apr, 01:26, "filippo" <[EMAIL PROTECTED]> wrote:

just to clarify:

I can insert text into database. My problem are the SELECT query like
this

WHERE name LIKE $name

$name = $dbh->quote(qq/ d'am% / )


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

Re: [GENERAL] backend reset of database

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
>> Geoffrey wrote:
>>> CREATE OR REPLACE FUNCTION pcm_getmiles_s(text,text,integer)
>>> RETURNS float8 AS '/esc/pgrnd/prog/libpcmiler'
>>> LANGUAGE 'c' WITH (isStrict);
> 
>> Is this function actually a PostgreSQL callable function? Or is it just
>> a general C function along the line of
>> float pcm_getmiles_s(char*, char*, int)
>> ?
> 
> The code would never have worked at all if that were the case (since
> text* is not like char*).  I suspect some subtler portability issue in
> Geoffrey's glue functions.  It seems unlikely that moving from PG 7.4.13
> to 7.4.16 would in itself have exposed such a problem, but the update in
> operating environment from RHEL3 to RHEL4 might've.

That's what I get for not reading the whole threa dback, I missed the
fact that it had ever worked. Oops :-)

//Magnus

---(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] backend reset of database

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> Geoffrey wrote:
>> CREATE OR REPLACE FUNCTION pcm_getmiles_s(text,text,integer)
>> RETURNS float8 AS '/esc/pgrnd/prog/libpcmiler'
>> LANGUAGE 'c' WITH (isStrict);

> Is this function actually a PostgreSQL callable function? Or is it just
> a general C function along the line of
> float pcm_getmiles_s(char*, char*, int)
> ?

The code would never have worked at all if that were the case (since
text* is not like char*).  I suspect some subtler portability issue in
Geoffrey's glue functions.  It seems unlikely that moving from PG 7.4.13
to 7.4.16 would in itself have exposed such a problem, but the update in
operating environment from RHEL3 to RHEL4 might've.

regards, tom lane

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

Re: [GENERAL] backend reset of database

[Magnus Hagander]

Geoffrey wrote:
> Tom Lane wrote:
>> Geoffrey <[EMAIL PROTECTED]> writes:
>>> A backtrace against this process produces:
>>
>>> Program received signal SIGSEGV, Segmentation fault.
>>> 0x0814acc9 in FileAccess (file=168481968) at fd.c:717
>>> 717 if (FileIsNotOpen(file))
>>> (gdb) bt
>>> #0  0x0814acc9 in FileAccess (file=168481968) at fd.c:717
>>> #1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "",
>>> amount=2)
>>>  at fd.c:972
>>> #2  0x00203ecc in ?? ()
>>
>>> I'm assuming that the portion of the backtrace from frame 2-12 is
>>> likely produced from the pcmiler binaries as we do not have source
>>> and they don't contain debugging symbols.
>>
>> Is pcmiler a Postgres-specific backend extension?
> 
> No.  It's a standalone application that provides an api to build calls
> into there product from other applications.  What we've done is created
> functions that are called from the database that use their api.  for
> example:
> 
> CREATE OR REPLACE FUNCTION pcm_getmiles_s(text,text,integer)
> RETURNS float8 AS '/esc/pgrnd/prog/libpcmiler'
> LANGUAGE 'c' WITH (isStrict);

Is this function actually a PostgreSQL callable function? Or is it just
a general C function along the line of
float pcm_getmiles_s(char*, char*, int)
?

If it's a general C function, you'll need to write a wrapper function
that calls it, and returns the data, using the PostgreSQL macros to
extract the parameters.

//Magnus


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

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

Re: [GENERAL] backend reset of database

[Tom Lane]

Geoffrey <[EMAIL PROTECTED]> writes:
> (gdb) bt
> #0  0x0814acc9 in FileAccess (file=168481968) at fd.c:717
> #1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)
>  at fd.c:972
> #2  0x00203ecc in ?? ()
> #3  0x0a0ad4b0 in ?? ()
> #4  0xbff816ce in ?? ()
> #5  0x0002 in ?? ()
> #6  0x0001 in ?? ()
> #7  0x0a04fdc0 in ?? ()
> #8  0xbff83110 in ?? ()
> #9  0x0001 in ?? ()
> #10 0x0002 in ?? ()
> #11 0xbff83110 in ?? ()
> #12 0x in ?? ()

> I'm assuming that the portion of the backtrace from frame 2-12 is likely 
> produced from the pcmiler binaries as we do not have source and they 
> don't contain debugging symbols.

Actually, on looking closer, most of those return addresses are
obviously silly: control was certainly never passed from addresses 0,
1, or 2, and I suspect a bit of disassembling with gdb would show
that the others aren't immediately after call instructions either.
So it looks like gdb got confused about which words in the stack are
return addresses --- which lends a bit more credibility to the idea
about a stack clobber, but still offers no direct help for debugging.

What I'd be inclined to look for first is local-variable buffers that
might not be big enough for the values that get written into them.
Perhaps the reason the failure occurred now was something as prosaic
as the new installation having longer path names.  In any case you
need to be taking a very hard look at your glue functions, since those
are the weakest link in the chain as far as the extent of portability
testing they've gotten goes (assuming that pcmiler is a reasonably
well-tested library).

regards, tom lane

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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Alexander Staubo]

On Apr 9, 2007, at 18:10 , Gerard Seibert wrote:


On Mon, 9 Apr 2007 10:34:22 -0500
Erik Jones <[EMAIL PROTECTED]> wrote:


Hmmm...  I didn't have anything HTML set anywhere.  I did however
have message formatting set to Rich-Text (although I was unaware)
and have switched that to plaintext.  This look better?


Yes, much better. I believe that 'rich text' is essentially HTML,
although I might be mistaken. I know that 'GMail' users have that
problem all the time. Of course 'GMail' users have lots of other
problems also.


Erik has actually been posting messages as "multipart/alternative",  
where the message is included in both 7-bit plaintext *and* HTML, the  
idea being that the mail viewer itself can pick the format it knows  
best. You are simply using a mail reader which prioritizes HTML;  
perhaps it has a setting to let you prefer plaintext?


Alexander.


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

Re: [GENERAL] backend reset of database

[Tom Lane]

Geoffrey <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Is pcmiler a Postgres-specific backend extension?

> No.  It's a standalone application that provides an api to build calls 
> into there product from other applications.  What we've done is created 
> functions that are called from the database that use their api.

Did you recompile/relink these glue functions when moving up to the new
version?  Are they built with debugging enabled?

The trace is *really* suspicious given what you say here.  I'm inclined
to think that what is really happening is that something is jumping to
never-never land (via a clobbered function pointer or overwriting a
return address on the stack) and it just happens to end up in FileRead.
Unfortunately that guess doesn't provide much help for debugging it :-(

regards, tom lane

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

Re: [GENERAL] backend reset of database

[Geoffrey]

Tom Lane wrote:

Geoffrey <[EMAIL PROTECTED]> writes:

A backtrace against this process produces:



Program received signal SIGSEGV, Segmentation fault.
0x0814acc9 in FileAccess (file=168481968) at fd.c:717
717 if (FileIsNotOpen(file))
(gdb) bt
#0  0x0814acc9 in FileAccess (file=168481968) at fd.c:717
#1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)
 at fd.c:972
#2  0x00203ecc in ?? ()


I'm assuming that the portion of the backtrace from frame 2-12 is likely 
produced from the pcmiler binaries as we do not have source and they 
don't contain debugging symbols.


Is pcmiler a Postgres-specific backend extension?


No.  It's a standalone application that provides an api to build calls 
into there product from other applications.  What we've done is created 
functions that are called from the database that use their api.  for 
example:


CREATE OR REPLACE FUNCTION pcm_getmiles_s(text,text,integer)
RETURNS float8 AS '/esc/pgrnd/prog/libpcmiler'
LANGUAGE 'c' WITH (isStrict);

To produce the above back trace I executed the following from a psql window:

select pcm_getmiles_s('sparta,nc', 'buffalo,ny',0);


It seems fairly
unlikely that it would be calling FileRead() if not.


I find that unlikely as well.


Do you have any
other Postgres-specific libraries loaded into the backend?


I don't believe so.


The immediate issue is that FileRead() is being passed a bogus file
number, which might suggest an unexpected change in the contents of
a struct or something like that, but I see no record of any such changes
in the CVS log between 7.4.13 and 7.4.16.


This is what threw me as well.  It definitely looks like something 
internal to Postgresql.


--
Until later, Geoffrey

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

---(end of broadcast)---
TIP 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] backend reset of database

[Tom Lane]

Geoffrey <[EMAIL PROTECTED]> writes:
> A backtrace against this process produces:

> Program received signal SIGSEGV, Segmentation fault.
> 0x0814acc9 in FileAccess (file=168481968) at fd.c:717
> 717 if (FileIsNotOpen(file))
> (gdb) bt
> #0  0x0814acc9 in FileAccess (file=168481968) at fd.c:717
> #1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)
>  at fd.c:972
> #2  0x00203ecc in ?? ()

> I'm assuming that the portion of the backtrace from frame 2-12 is likely 
> produced from the pcmiler binaries as we do not have source and they 
> don't contain debugging symbols.

Is pcmiler a Postgres-specific backend extension?  It seems fairly
unlikely that it would be calling FileRead() if not.  Do you have any
other Postgres-specific libraries loaded into the backend?

The immediate issue is that FileRead() is being passed a bogus file
number, which might suggest an unexpected change in the contents of
a struct or something like that, but I see no record of any such changes
in the CVS log between 7.4.13 and 7.4.16.

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

[GENERAL] backend reset of database

[Geoffrey]

We have built into the backend of the database a third party application 
called pcmiler that produces mileage between two locations.  This has 
been working in the past, but after upgrading to RHEL 4, Postgresql 
7.4.16 and pcmiler 20 (from pcmiler 17), the server process is 
terminated with a signal 11.  I know that's a lot of variables that 
changed, but one required the other, thus we can't easily backout 
anything (outside of possibly moving down to 7.4.13 of Postgres).


The log of the server process contains:

LOG:  server process (PID 32441) 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.



A backtrace against this process produces:

Program received signal SIGSEGV, Segmentation fault.
0x0814acc9 in FileAccess (file=168481968) at fd.c:717
717 if (FileIsNotOpen(file))
(gdb) bt
#0  0x0814acc9 in FileAccess (file=168481968) at fd.c:717
#1  0x0814b2e7 in FileRead (file=168481968, buffer=0xbff816ce "", amount=2)
at fd.c:972
#2  0x00203ecc in ?? ()
#3  0x0a0ad4b0 in ?? ()
#4  0xbff816ce in ?? ()
#5  0x0002 in ?? ()
#6  0x0001 in ?? ()
#7  0x0a04fdc0 in ?? ()
#8  0xbff83110 in ?? ()
#9  0x0001 in ?? ()
#10 0x0002 in ?? ()
#11 0xbff83110 in ?? ()
#12 0x in ?? ()

I'm assuming that the portion of the backtrace from frame 2-12 is likely 
produced from the pcmiler binaries as we do not have source and they 
don't contain debugging symbols.


Any insights would be greatly appreciated.

--
Until later, Geoffrey

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

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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Gerard Seibert]

On Mon, 9 Apr 2007 10:34:22 -0500
Erik Jones <[EMAIL PROTECTED]> wrote:

> Hmmm...  I didn't have anything HTML set anywhere.  I did however  
> have message formatting set to Rich-Text (although I was unaware)
> and have switched that to plaintext.  This look better?

Yes, much better. I believe that 'rich text' is essentially HTML,
although I might be mistaken. I know that 'GMail' users have that
problem all the time. Of course 'GMail' users have lots of other
problems also.

-- 
Gerard

Having a wonderful wine, wish you were beer.


signature.asc
Description: PGP signature

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Erik Jones]

On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:



On Apr 9, 2007, at 10:09 AM, [EMAIL PROTECTED] wrote:

It would be a really great service to this community if you would  
capture those issues and publish documentation (but feel free to  
change or omit the names to protect the incompetent^w innocent!).


There's no incompetence involved... the guy is clearly not a  
Postgres person, but overall an excellent DB designer.  He works on  
extremely large databases at a large public university.


The main one that comes to mind is that he suggested adding multi- 
part primary indexes to keep the data ordered.  Apparently Oracle  
and/or DB2 keep the data sorted by primary key index.  Since the  
only reason was to keep the data sorted, the index would be useless  
under Pg.


You do have CLUSTER available for ordering a table on a single  
index.  However, after you do a CLUSTER new rows and updates don't  
respect that and you have to CLUSTER again periodically, but isn't  
difficult to add to a regular maintenance schedule/script.




Also, he recommended the use of 'index-only' tables -- eg, when the  
table is just two or three integers, and the PK is a multi-part key  
of all fields, it makes sense not to store the data twice.   
However, in Pg you can't do that since visibility is only stored in  
the data, not the index.


That would be cool.



One thing that was really counter-intuitive to me from a guy who  
runs really large databases, was to get rid of some of the FK's and  
manage them in the application layer.  This one scares me since  
I've had my behind saved at least a couple of times by having the  
extra layer in the DB to protect me... the data integrity would be  
managed by some external program that sweeps the DB every so often  
and purges out data that should no longer be there (ie stuff that  
would have been CASCADE DELETEd).


This is often debated and it does seem strange to here that stance  
from a dba.  It's normally the application developers who want to do  
that.


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




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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Erik Jones]

On Apr 9, 2007, at 9:40 AM, Gerard wrote:


On Monday April 09, 2007 at 10:19:53 (AM) Erik Jones wrote:



On Apr 9, 2007, at 8:15 AM, Vivek Khera wrote:



On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:


Another thing is this, how hard could it possibly be for a MS SQL
DBA or Oracle DBA to pick up using PostgreSQL?
I don't think it would take a decent admin of any database to come
up to speed in a very short time as long as they were interested
in doing so.


We've been working with a consultant to re-design/optimize some
existing DB systems we have running, and his background is mostly
Oracle and DB/2.  Some of the optimizations -- actually
operationally related choices on how to do things -- are remarkably
off-base for Postgres.


Can you give some examples of this?  It's not that I don't believe
you, I'd just like some concrete examples from someone in your
situation.


Erik, would it be possible for you to post in 'plain text' and not  
HTML?

This is a mail forum, not a web page.



Hmmm...  I didn't have anything HTML set anywhere.  I did however  
have message formatting set to Rich-Text (although I was unaware) and  
have switched that to plaintext.  This look better?


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




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

[GENERAL] Tom Kiel/UGW/DE ist außer Haus.

[t . kiel]

Ich werde ab  02.04.2007 nicht im Büro sein. Ich kehre zurück am
10.04.2007.


Ich werde Ihre Nachricht gern nach meiner Rückkehr beantworten.

In dringenden Fällen wenden Sie sich bitte an Stephan Luxenburger.
Herr Luxenburger ist unter der Mailadresse [EMAIL PROTECTED] oder
telefonisch unter der Rufnummer +49 (6 11) 9 77 77-496 erreichbar.

Diese Mail wird nicht automatisch weitergeleitet.

OUT OF OFFICE REPLY:

I will be out of the office from april 2, 2007 until april 10, 2007.

I will reply to your mail when I return.

If you need help before that time please contact Stephan Luxenburger at +49
(6 11) 9 77 77-496 or [EMAIL PROTECTED]

This mail will not be automatically forwarded.



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

   http://archives.postgresql.org/

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Vivek Khera]

On Apr 9, 2007, at 10:09 AM, [EMAIL PROTECTED] wrote:

It would be a really great service to this community if you would  
capture those issues and publish documentation (but feel free to  
change or omit the names to protect the incompetent^w innocent!).


There's no incompetence involved... the guy is clearly not a Postgres  
person, but overall an excellent DB designer.  He works on extremely  
large databases at a large public university.


The main one that comes to mind is that he suggested adding multi- 
part primary indexes to keep the data ordered.  Apparently Oracle and/ 
or DB2 keep the data sorted by primary key index.  Since the only  
reason was to keep the data sorted, the index would be useless under Pg.


Also, he recommended the use of 'index-only' tables -- eg, when the  
table is just two or three integers, and the PK is a multi-part key  
of all fields, it makes sense not to store the data twice.  However,  
in Pg you can't do that since visibility is only stored in the data,  
not the index.


One thing that was really counter-intuitive to me from a guy who runs  
really large databases, was to get rid of some of the FK's and manage  
them in the application layer.  This one scares me since I've had my  
behind saved at least a couple of times by having the extra layer in  
the DB to protect me... the data integrity would be managed by some  
external program that sweeps the DB every so often and purges out  
data that should no longer be there (ie stuff that would have been  
CASCADE DELETEd).




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Gerard]

On Monday April 09, 2007 at 10:19:53 (AM) Erik Jones wrote:


> On Apr 9, 2007, at 8:15 AM, Vivek Khera wrote:
> 
> >
> > On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:
> >
> >> Another thing is this, how hard could it possibly be for a MS SQL  
> >> DBA or Oracle DBA to pick up using PostgreSQL?
> >> I don't think it would take a decent admin of any database to come  
> >> up to speed in a very short time as long as they were interested  
> >> in doing so.
> >
> > We've been working with a consultant to re-design/optimize some  
> > existing DB systems we have running, and his background is mostly  
> > Oracle and DB/2.  Some of the optimizations -- actually  
> > operationally related choices on how to do things -- are remarkably  
> > off-base for Postgres.
> 
> Can you give some examples of this?  It's not that I don't believe  
> you, I'd just like some concrete examples from someone in your  
> situation.

Erik, would it be possible for you to post in 'plain text' and not HTML?
This is a mail forum, not a web page.

Thanks!

-- 
Gerard

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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Erik Jones]

On Apr 9, 2007, at 8:15 AM, Vivek Khera wrote:



On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:

Another thing is this, how hard could it possibly be for a MS SQL  
DBA or Oracle DBA to pick up using PostgreSQL?
I don't think it would take a decent admin of any database to come  
up to speed in a very short time as long as they were interested  
in doing so.


We've been working with a consultant to re-design/optimize some  
existing DB systems we have running, and his background is mostly  
Oracle and DB/2.  Some of the optimizations -- actually  
operationally related choices on how to do things -- are remarkably  
off-base for Postgres.


Can you give some examples of this?  It's not that I don't believe  
you, I'd just like some concrete examples from someone in your  
situation.


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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[btober]

Vivek Khera wrote:


On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:

Another thing is this, how hard could it possibly be for a MS SQL DBA 
or Oracle DBA to pick up using PostgreSQL?
I don't think it would take a decent admin of any database to come up 
to speed in a very short time as long as they were interested in 
doing so.


We've been working with a consultant to re-design/optimize some 
existing DB systems we have running, and his background is mostly 
Oracle and DB/2.  Some of the optimizations -- actually operationally 
related choices on how to do things -- are remarkably off-base for 
Postgres.  There is a *lot* to learn about a system before one can 
truly "know" it. 


It would be a really great service to this community if you would 
capture those issues and publish documentation (but feel free to change 
or omit the names to protect the incompetent^w innocent!).




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

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

[Vivek Khera]

On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:

Another thing is this, how hard could it possibly be for a MS SQL  
DBA or Oracle DBA to pick up using PostgreSQL?
I don't think it would take a decent admin of any database to come  
up to speed in a very short time as long as they were interested in  
doing so.


We've been working with a consultant to re-design/optimize some  
existing DB systems we have running, and his background is mostly  
Oracle and DB/2.  Some of the optimizations -- actually operationally  
related choices on how to do things -- are remarkably off-base for  
Postgres.  There is a *lot* to learn about a system before one can  
truly "know" it. 
 


---(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] Documenting PGSQL database.

[Ashish Karalkar]

Hello All,
   
  here are many  tools available to create the attractive documentation of 
PostgreSQL Database  in different formats.
   
  With Reagrds
  Ashish...

Björn Lundin <[EMAIL PROTECTED]> wrote:
  
7 apr 2007 kl. 21.07 skrev RPK:



  I am using PGSQL 8.2.3 on Windows XP. I want to know whether there is any way
  of documenting the schema of PGSQL database so that I can get a printed copy
  of the same.


  Hmm, I did not get my own post , so I thought I'd reply on this one again
  Looking through what has happened since 2003, when I wrote that 
  documentation thing, I decided to update a bit, translate it to english,
  provide a small sample of how it can look, and changing the toolchain. 
  

  It is at 
  

  
  

  


/Björn
  

  Björn Lundin
  b dot f dot lundin at gmail dot com
  







-
 Here’s a new way to find what you're looking for - Yahoo! Answers 

[GENERAL] Pg schema diff tools Was: [ANNOUNCE] == PostgreSQL Weekly News - April 08 2007 ==

[Markus Schiltknecht]

Hi,

David Fetter wrote in the weekly news:

Another PostgreSQL Diff Tool 1.0.0_beta20 released.
http://pgfoundry.org/projects/apgdiff/


Why is it 'another' one? What others exist? (Specifically, are there 
ones, which don't depend on java?)


Regards

Markus

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