Re: [GENERAL] HELP FOR LOADING a .psql file (same question again but explained neatly)

[Ranbeer Makin]

On the postgres command prompt you can use "/i filename" ... try if it
works

Cheers


On Thu, Feb 28, 2008 at 9:07 AM, akshay bhat <[EMAIL PROTECTED]> wrote:

> hello i am new to psql or any database stuff.
> i have downloaded an .psql file from internet and wish to open it and see
> the data inside.
> i am working on windows xp and have installed the software successfully.
> please help i am my wits end.
> it is huge file 800mb
> and is supposed to contain a database.
> the question is how to load it?
>
> it was downloaded from this link http://conceptnet.media.mit.edu/
> the description says
> The ConceptNet 3 
> database,
> as a PostgreSQL input file. You will need to be running a PostgreSQL server
> to install ConceptNet 3.
>
> how do i load it?
> thanks a lot
>
> --
> akshay uday bhat.
> t.y.c.e.
> department of chemical engineering
> university institute of chemical technology
> mumbai India
>
> On action alone be thy interest,
> Never on its fruits.
> Let not the fruits of action be thy motive,
> Nor be thy attachment to inaction.
>
> Ask and it shall be given to you; seek and you shall find; knock and it
> shall be opened to you

Re: [GENERAL] Query meltdown: caching results

[Norman Peelman]

Gordon wrote:

(Sorry for the repost but I thought this would be appropriate to both
groups. I did tell Google to delete my first post but odds are some
guys got that copy already anyway)

After a lot of hairpulling, I finally found a mechanism in PHP for
doing what I wanted.  I just had to know 2 things:

1) How to get the PDO engine to use my customized prepared statement
class instead of PDOStatement
2) Extending PDOStatement to transparently add results caching is too
difficult and complex in the timeframe required

Once I knew these things I made a PDOStatement extension class that
instead of trying to transparently add caching to the existing methods
added a couple of new ones instead.  Code below:

 resultCache [$hash])
{
$this -> misses++;
// Execute the query
if ($this -> execute ($params))
{
// Cache the results
$this -> resultCache [$hash] = $this -

fetchAll

(PDO::FETCH_ASSOC);
}
}
else
{
$this -> hits++;
}
return ($this -> resultCache [$hash]);
}
public function tell (array $params = array ())
// Execute a prepared statement that causes the database to be
modified
{
// Execute the query
if ($this -> execute ($params))
{
$rowCount   = $this -> rowCount ();
if ($rowCount)
{
// Tell the parent Database object to
clear statement caches
$this -> database -> clearResults ();
}
return ($rowCount);
}
}
public function clearResults ()
// Clear cache
{
$this -> resultCache = array ();
}
private function __construct ($db)
// Class constructor
{
$this -> database= $db;
//print_r ($this);
}

}

class Database extends PDO
// Adds some extra functionality to the built in PDO class
{
private $statementCache = array ();
private $txCount= 0;
private $txErr  = false;

// Prepared statement cacheing
public function prepare ($statement, array $options = array
())
{
$hash   = md5 ($statement);
if ((!isset ($this -> statementCache [$hash]))
|| (!is_object ($this -> statementCache [$hash])))
{
//echo ('Preparing statement "'.
$statement .'"');
$this -> statementCache [$hash] =
parent::prepare ($statement,
$options);
}
else
{
//echo ('Statement "' . $statement . '"
already prepared');
}
return ($this -> statementCache [$hash]);
}
public function clearResults ()
// Clear the results cache of all associated prepared
statements
{
foreach ($this -> statementCache as $thisStatement)
{
$thisStatement -> clearResults ();
}
}
// Add support for transaction nesting
public function beginTransaction ()
{
if (($this -> txCount == 0) && (!$this -> txErr))
{
$result = parent::beginTransaction ();
}
$this -> txCount ++;
if (DEBUG_TX)
{
echo ('begin: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
public function commit ()
{
$this -> txCount --;
if ($this -> txCount <= 0)
{
$this -> txErr?  $result =
parent::rollback ():  $result =
parent::commit ();
$this -> txErr   = false;
}
if (DEBUG_TX)
{
echo ('commit: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
public function rollback ()
{
$this -> txErr = true;
$this -> txCount --;
if ($this -> txCount <= 0)
{
$result = parent::rollback ();
$this -> txErr   = false;
}
if (DEBUG_TX)
{
echo ('rollback: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
// Housekee

[GENERAL] HELP FOR LOADING a .psql file (same question again but explained neatly)

[akshay bhat]

hello i am new to psql or any database stuff.
i have downloaded an .psql file from internet and wish to open it and see
the data inside.
i am working on windows xp and have installed the software successfully.
please help i am my wits end.
it is huge file 800mb
and is supposed to contain a database.
the question is how to load it?

it was downloaded from this link http://conceptnet.media.mit.edu/
the description says
The ConceptNet 3
database,
as a PostgreSQL input file. You will need to be running a PostgreSQL server
to install ConceptNet 3.

how do i load it?
thanks a lot

-- 
akshay uday bhat.
t.y.c.e.
department of chemical engineering
university institute of chemical technology
mumbai India

On action alone be thy interest,
Never on its fruits.
Let not the fruits of action be thy motive,
Nor be thy attachment to inaction.

Ask and it shall be given to you; seek and you shall find; knock and it
shall be opened to you

[GENERAL] beginner postgis question lat/lon

[shadrack]

Hi,
This may seem like a very simple question...it is...but I can't find
documentation on it to help. I've seen some posts about lat/long but
none that give simple solutions on how to insert lat/long in tables.
I'm a new user to postgis...I've been using mysql for a while but
needed the spatial functions so just recently downloaded postgresql/
postgis.
How do I create a simple table with several linestrings that have
coordinates in latitude/longitude?
Eventually, I'd like to create polygons with coordinates in lat/long
and see if they intersect various linestrings, but I'm trying to start
out simple.
Here's what I've done so far...

postgis=# \d routes_geom;
 Table "public.routes_geom"
 Column | Type  | Modifiers
+---+---
 id | integer   |
 name   | character varying(25) |
 geom   | geometry  |
Check constraints:
"enforce_dims_geom" CHECK (ndims(geom) = 2)
"enforce_geotype_geom" CHECK (geometrytype(geom) =
'LINESTRING'::text OR geom IS NULL)
"enforce_srid_geom" CHECK (srid(geom) = 4326)
postgis=# insert into routes_geom values(1, 'J084',
GeomFromText('LINESTRING(38.20 -121.00, 38.20, -118.00)', 4326));

I receive this error:
ERROR:  parse error - invalid geometry
CONTEXT:  SQL function "geomfromtext" statement 1

Do you have a good way to input lat/lon and do spatial relationships
using lat/long coords?
Thanks for any help,
Shad

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

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

Re: [GENERAL] help for loading a psql file

[Chris]

akshay bhat wrote:

hello i am new to psql or any database stuff.
i have downloaded an .psql file from internet and wish to open it and 
see the data inside.


Drag it into your text editor.

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Can't start Postgres anymore

[Gevik Babakhani]

IIRC, you cannot state runservice runservice -N "." from commandline.
just try pg_ctl -D 


  _  

From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of George Younan
Sent: Wednesday, February 27, 2008 10:08 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Can't start Postgres anymore


Hallo,
 
I have Postgres installed as a service on Windows xp pro. It has been
working fine but suddenly i couldn't start it anymore. I looked in different
forums for a similar problem but didn't find any solution sofar. So any help
is very appreciated. one more thing to say is that i am a beginner with
Postgres and don't have much administrative experience...
As i said above, i can't start my Postgres anymore. I always get the error
message "could not connect to server: Connection refused
(0x274D/10061)Is the server running on host "???" and accepting TCP/IP
connections on port 5432?". 
I checked if the service is running, but it wasn't running. I tried to start
ist but i couldn't. Doing this from the console i got the following error:
"C:\Programme\PostgreSQL\8.1\bin>pg_ctl.exe runservice -N "pgsql-8.1" -D
"C:\Programme\PostgreSQL\8.1\data\"
pg_ctl: could not start service "pgsql-8.1": error code 1063"
 
 I chekced postgres.conf and the configuration is as follows:
# - Connection Settings -
listen_addresses = 'localhost'
port = 5432
max_connections = 100
 
The last log file is:
2008-02-08 07:40:38 LOG:  could not connect socket for statistics collector:
Es konnte keine Verbindung hergestellt werden, da der Zielcomputer die
Verbindung verweigerte.
 

2008-02-08 07:40:38 LOG:  disabling statistics collector for lack of working
socket
2008-02-08 07:40:38 WARNING:  autovacuum not started because of
misconfiguration
2008-02-08 07:40:38 HINT:  Enable options "stats_start_collector" and
"stats_row_level".
2008-02-08 07:40:40 LOG:  database system was shut down at 2008-02-08
07:37:57 Westeuropäische Normalzeit
2008-02-08 07:40:40 LOG:  checkpoint record is at 0/45AE50
2008-02-08 07:40:40 LOG:  redo record is at 0/45AE50; undo record is at 0/0;
shutdown TRUE
2008-02-08 07:40:40 LOG:  next transaction ID: 5100; next OID: 16515
2008-02-08 07:40:40 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2008-02-08 07:40:41 LOG:  database system is ready
2008-02-08 07:40:41 LOG:  transaction ID wrap limit is 2147484148, limited
by database "postgres"



 I'm using Zonealarm firewall but Postgres is authorized on 127.0.0.1:5432.
It's been working fine for the last month since installtion and i didn't
change any configuration. I stopped both zonealarm and the windows firewall
but still success, so i guess it's not a firewall problem.

 I tried to ping the server using "ping 127.0.0.1" which was successful.

 I tried to connect to the host with telnet using "telnet 127.0.0.1 5432"
but i couldn't connect.
 
anyone has a suggestion what can i do to get it working again. I dont want
to lose the data stored on it!
 
thanks in advance
 
George
 

Re: [GENERAL] How to monitor the progress of a stored procedure?

[Klint Gore]

Kynn Jones wrote:

(Sorry for asking so many questions!)

What techniques can one use to monitor the progress of a stored 
procedure?  

Specifically, how can I get the procedure to print a progress 
indicator message to the screen every once in a while?


[...](In case it matters, the procedure is written in PL/perl.  I 
tried to use Perl's print statement, unbuffered, to print out a 
message periodically from within the procedure, but I see no output.)
elog?  (see example in 
http://www.postgresql.org/docs/8.3/interactive/plperl-database.html)


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


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

Re: [GENERAL] RHEL3 configure ssl

[Devrim GÜNDÜZ]

Hi,

On Wed, 2008-02-27 at 16:16 -0500, Ray Stell wrote:
> /usr/include/openssl/kssl.h:72:18: krb5.h: No such file or directory

Add 

--with-includes=/usr/kerberos/include --with-libraries=/usr/kerberos/lib

to your configure parameters. This is what we do while building
PostgreSQL RPMs on RHEL 3.

Regards,
-- 
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/


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

[GENERAL] PostgreSQL UK 2008 Conference

[Simon Riggs]

Just a reminder to everybody to come along to the PostgreSQL UK 2008
Conference in Birmingham, UK on April 2nd.

I've just had confirmation that the Early Bird Rate has been extended
until March 10th. There are some concessionary places available for
students also, so we want *everyone* there.

This is a non-profit conference - all fees go towards conference
organisation and PostgreSQL funds.

Thanks everyone,

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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

Re: [GENERAL] How to monitor the progress of a stored procedure?

[Colin Wetherbee]

Kynn Jones wrote:
I have a stored procedure that has been running for a very long time. 
 Before doing the current "real" run, I tested the procedure on a toy 
case and confirmed that it produced the right result.  So I know that 
when it finishes it will be OK, but I can't tell whether it will finish 
in an hour or in a century...  I have no idea of how far along it is. 
 (In case it matters, the procedure is written in PL/perl.  I tried to 
use Perl's print statement, unbuffered, to print out a message 
periodically from within the procedure, but I see no output.)


When I print() from PL/Perl, the messages show up in the PostgreSQL log 
file.  It's not an obvious place, but it makes sense because PL/Perl 
functions aren't always called from interactive terminals.  Did you look 
there?


Colin

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

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

Re: [GENERAL] How to monitor the progress of a stored procedure?

[Erik Jones]

On Feb 27, 2008, at 5:25 PM, Kynn Jones wrote:


(Sorry for asking so many questions!)

What techniques can one use to monitor the progress of a stored  
procedure?


Specifically, how can I get the procedure to print a progress  
indicator message to the screen every once in a while?


I have a stored procedure that has been running for a very long  
time.  Before doing the current "real" run, I tested the procedure  
on a toy case and confirmed that it produced the right result.  So  
I know that when it finishes it will be OK, but I can't tell  
whether it will finish in an hour or in a century...  I have no  
idea of how far along it is.  (In case it matters, the procedure is  
written in PL/perl.  I tried to use Perl's print statement,  
unbuffered, to print out a message periodically from within the  
procedure, but I see no output.)


Thanks in advance,


Read up on this chapter: http://www.postgresql.org/docs/8.2/ 
interactive/plpgsql-errors-and-messages.html


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

  http://archives.postgresql.org/

[GENERAL] How to monitor the progress of a stored procedure?

[Kynn Jones]

(Sorry for asking so many questions!)
What techniques can one use to monitor the progress of a stored procedure?

Specifically, how can I get the procedure to print a progress indicator
message to the screen every once in a while?

I have a stored procedure that has been running for a very long time.
 Before doing the current "real" run, I tested the procedure on a toy case
and confirmed that it produced the right result.  So I know that when it
finishes it will be OK, but I can't tell whether it will finish in an hour
or in a century...  I have no idea of how far along it is.  (In case it
matters, the procedure is written in PL/perl.  I tried to use Perl's print
statement, unbuffered, to print out a message periodically from within the
procedure, but I see no output.)

Thanks in advance,

Kynn

Re: [GENERAL] RHEL3 configure ssl

[Greg Smith]

On Wed, 27 Feb 2008, Ray Stell wrote:


/usr/include/openssl/kssl.h:72:18: krb5.h: No such file or directory


Ah, the RHEL3 Kerberos mess.  If you have further problems compiling 
there's a heap of other paths you can pass through to sort around this 
issue; the end of http://www.ejabberd.im/node/172 has a good list.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

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

Re: [GENERAL] UUID-OSSP for windows.

[Tim Uckun]

>  citext is not part of core PostgreSQL or something we have any
>  intention to include in the Windows distribution at this time.

Is there an alternative for people wanting a case insensitive collation?

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

   http://archives.postgresql.org/

Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.

[Andrej Ricnik-Bay]

On 28/02/2008, Olmec Sinclair <[EMAIL PROTECTED]> wrote:
>  Anyway, I uninstalled and installed using the ubuntu package manager. I
> tried again this morning - removing 8.2 and then reinstalling 8.2 again...
> still won't start.
Are you seeing any error messages?  W/o more detail it's impossible
to even start trying to support you.
What does /var/log/postgresql/postgresql-8.2-main.log say?

>  I'm sure I can work through this (I seem to remember having a starting
> issue before) but the data locale thing is a worry.
That, too, is indeed.  Now ... gutsy doesn't have 8.3 officially yet
(I don't think).  What other repositories did you tie in with your apt?


>  Cheers (from Christchurch)
>
>  Olmec
Cheers,
Andrej

---(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 "paste two tables side-by-side"?

[Erik Jones]

On Feb 27, 2008, at 7:22 AM, Kynn Jones wrote:


On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones <[EMAIL PROTECTED]> wrote:
Suppose I have two tables, A and B, with k(A) and k(B) columns  
respectively, and let's assume to begin with that they have the  
same number of rows r(A) = r(B) = r.


What's the simplest way to produce a table C having r rows and k(A)  
+ k(B) columns, and whose i-th row consists of the k(A) columns of  
the i-th row of A followed by the k(B) columns of the i-th row of B  
(for i = 1,...,r)?  (By "i-th row of A" I mean the i-th row of the  
listing one would get from "SELECT * FROM A", and likewise for B.)


Expanding on my own post here, it occurred to me that it would be  
very nice to have a function (say) index, that, when used in a  
SELECT list, would yield the position in the corresponding table of  
the current row.  E.g. the expression


  SELECT index(*) FROM A;

would produce the same table as

  SELECT generate_series( 1, r(A) );

It would also be useful to have a "subscripting function" s (which  
may be regarded as somewhat of the inverse of index()) that, given  
a table expression E, and an "index expression" I (which could be a  
single index or range, or a list of such), will return the table  
consisting of the rows in E designated by the indices in I).  For  
example, either one these queries


  SELECT s( A, 1, 2, 3 );
  SELECT * FROM s( A, 1, 2, 3 );

would produce the same table as

  SELECT * FROM A LIMIT 3;

Does anything like index() or s() exist?  If not, are there other  
functions that may be useful in an implementation of index() or s()?


If I'm understanding what you're asking for here, no it doesn't and  
it doesn't make sense for it to.  Table rows are inherently  
unordered, to get an ordering you supply and ORDER BY clause.  To get  
a certain position in that ordering you take on an OFFSET and/or  
LIMIT clause.


Another way of putting it is that at the base table level, rows do  
not have position within the table.  As soon as you update a row, it  
"moves" as the original row is deleted and a new one is inserted.   
Once vacuum does it's job, then newly inserted rows are inserted over  
previously deleted rows, i.e in the "middle" of the table.


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

Re: [GENERAL] RHEL3 configure ssl

[Ray Stell]

On Wed, Feb 27, 2008 at 03:22:57PM -0500, Tom Lane wrote:
> Ray Stell <[EMAIL PROTECTED]> writes:
> > How to get ssl configured on RHEL3?  Can't seem to hook up
> > with openssl/ssl.h.  This is 8.3.0 and it works on RHEL4:
> 
> Weird.  Did you look into config.log to see exactly why the probes
> for ssl.h are failing?


forgot...

| #include 
configure:10944: result: no
configure:10948: checking openssl/ssl.h presence
configure:10958: gcc -E  -D_GNU_SOURCE  -I/usr/include conftest.c
In file included from /usr/include/openssl/ssl.h:179,
 from conftest.c:54:
/usr/include/openssl/kssl.h:72:18: krb5.h: No such file or directory


$ find / -name krb5.h -print 2>/dev/null
/usr/kerberos/include/krb5.h


$ ./configure --prefix=/usr/local/pgsql830 --with-openssl 
--with-includes=/usr/kerberos/include/ 

cleans it up, thanks Tom.

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

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

[GENERAL] Can't start Postgres anymore

[George Younan]

Hallo,

I have Postgres installed as a service on Windows xp pro. It has been working 
fine but suddenly i couldn't start it anymore. I looked in different forums for 
a similar problem but didn't find any solution sofar. So any help is very 
appreciated. one more thing to say is that i am a beginner with Postgres and 
don't have much administrative experience...
As i said above, i can't start my Postgres anymore. I always get the error 
message "could not connect to server: Connection refused (0x274D/10061)Is 
the server running on host "???" and accepting TCP/IP connections on port 
5432?". 
I checked if the service is running, but it wasn't running. I tried to start 
ist but i couldn't. Doing this from the console i got the following error: 
"C:\Programme\PostgreSQL\8.1\bin>pg_ctl.exe runservice -N "pgsql-8.1" -D 
"C:\Programme\PostgreSQL\8.1\data\"
pg_ctl: could not start service "pgsql-8.1": error code 1063"

 I chekced postgres.conf and the configuration is as follows:
# - Connection Settings -
listen_addresses = 'localhost'
port = 5432
max_connections = 100

The last log file is:
2008-02-08 07:40:38 LOG:  could not connect socket for statistics collector: Es 
konnte keine Verbindung hergestellt werden, da der Zielcomputer die Verbindung 
verweigerte.

 
2008-02-08 07:40:38 LOG:  disabling statistics collector for lack of working 
socket
2008-02-08 07:40:38 WARNING:  autovacuum not started because of misconfiguration
2008-02-08 07:40:38 HINT:  Enable options "stats_start_collector" and 
"stats_row_level".
2008-02-08 07:40:40 LOG:  database system was shut down at 2008-02-08 07:37:57 
Westeuropäische Normalzeit
2008-02-08 07:40:40 LOG:  checkpoint record is at 0/45AE50
2008-02-08 07:40:40 LOG:  redo record is at 0/45AE50; undo record is at 0/0; 
shutdown TRUE
2008-02-08 07:40:40 LOG:  next transaction ID: 5100; next OID: 16515
2008-02-08 07:40:40 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2008-02-08 07:40:41 LOG:  database system is ready
2008-02-08 07:40:41 LOG:  transaction ID wrap limit is 2147484148, limited by 
database "postgres"



 I'm using Zonealarm firewall but Postgres is authorized on 127.0.0.1:5432. 
It's been working fine for the last month since installtion and i didn't change 
any configuration. I stopped both zonealarm and the windows firewall but still 
success, so i guess it's not a firewall problem.

 I tried to ping the server using "ping 127.0.0.1" which was successful.

 I tried to connect to the host with telnet using "telnet 127.0.0.1 5432" but i 
couldn't connect.

anyone has a suggestion what can i do to get it working again. I dont want to 
lose the data stored on it!

thanks in advance

George

Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.

[Olmec Sinclair]

Andrej,

Thanks for the tips (should I post this in a different way?)

Anyway, I uninstalled and installed using the ubuntu package manager. I 
tried again this morning - removing 8.2 and then reinstalling 8.2 
again... still won't start.


I'm sure I can work through this (I seem to remember having a starting 
issue before) but the data locale thing is a worry.


Cheers (from Christchurch)

Olmec

Andrej Ricnik-Bay wrote:

On 28/02/2008, Olmec Sinclair <[EMAIL PROTECTED]> wrote:
Hello again!

And please remember to hit reply-to-all so everyone on the
list sees what you're saying.  Also please note that top-posting
is frowned upon here. :)

  

 I am running postgres on Ubuntu 7.10 (recent convert from windows) and I
think my previous version of postgresql was 8.2



  

 Last night I un installed 8.3 and reinstalled 8.2 but now I can't get that
to start so I'm not making much progress. The problem is I don't really
know what I am doing :)


By which means did you do the installation/un-install of both versions?
What data-directory is/was which version using?



  

 I am learning slowly though.

 Olmec


Cheers from sunny Wellington,
Andrej

  

Re: [GENERAL] reindexing

[Lew]

Greg Smith wrote:
If some big-iron shop who is so blind to security issues that they want 
to keep 7.4 on life support, they certainly can find someone to deliver 
such a support agreement on a contract basis.  But they shouldn't expect 
the public project to keep them afloat for free, and saying this project 
"must be ready" to handle them is quite debatable.  Given the limited 
resources of the public volunteers here, supporting ancient versions is 
a drain it's hard to justify outside the context of such a support 
agreement.  Using your own examples, Oracle and Sun sure don't, why 
should PostgreSQL?


I am not arguing that Postgres, Oracle, Sun or anyone else should have to 
support such obsolete products, or that they are the only source for that 
support.  I only state the fact that many organizations are slow to move off 
even obsolete products - this is something I have observed more than once in 
more than one contract.  I only claimed in my post that "we must be ready to 
deal with that", since it is a fact, not that vendors should have to support 
those products for free.


For example, in my work I deal with that by strongly urging my clients not to 
use obsolete software, after explaining that the software in question is 
actually obsolete.  They don't always agree with my recommendation, then I 
deal with that in turn.  It's not like they make me their decision maker.


I agree that no one should have to support obsolete products for free, and 
that these organizations should upgrade.


--
Lew

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

Re: [GENERAL] RHEL3 configure ssl

[Tom Lane]

Ray Stell <[EMAIL PROTECTED]> writes:
> How to get ssl configured on RHEL3?  Can't seem to hook up
> with openssl/ssl.h.  This is 8.3.0 and it works on RHEL4:

Weird.  Did you look into config.log to see exactly why the probes
for ssl.h are failing?

regards, tom lane

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

Re: [GENERAL] reindexing

[Tom Lane]

Greg Smith <[EMAIL PROTECTED]> writes:
> If some big-iron shop who is so blind to security issues that they want to 
> keep 7.4 on life support, they certainly can find someone to deliver such 
> a support agreement on a contract basis.  But they shouldn't expect the 
> public project to keep them afloat for free, and saying this project "must 
> be ready" to handle them is quite debatable.

Well, whether we *must* do it or not is arguable; but the point in this
thread is that we *do* do it.  The 7.4 branch is up to 7.4.19.  But the
OP was (apparently) still running 7.4.1, and his failure to take
advantage of that free support was what I was lecturing him about ;-)

regards, tom lane

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

Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.

[Andrej Ricnik-Bay]

On 28/02/2008, Olmec Sinclair <[EMAIL PROTECTED]> wrote:
Hello again!

And please remember to hit reply-to-all so everyone on the
list sees what you're saying.  Also please note that top-posting
is frowned upon here. :)

>  I am running postgres on Ubuntu 7.10 (recent convert from windows) and I
> think my previous version of postgresql was 8.2

>  Last night I un installed 8.3 and reinstalled 8.2 but now I can't get that
> to start so I'm not making much progress. The problem is I don't really
> know what I am doing :)
By which means did you do the installation/un-install of both versions?
What data-directory is/was which version using?



>  I am learning slowly though.
>
>  Olmec
Cheers from sunny Wellington,
Andrej

-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

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

Re: [GENERAL] reindexing

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wed, 27 Feb 2008 11:28:32 -0500
Lew <[EMAIL PROTECTED]> wrote:

> In my experience at various "big-iron" shops (government agencies,
> large health-care organizations and the like), four years is not a
> long time for enterprise software - a version often has to be at
> least four years old before the powers-that-be decide to try it.  One
> has only to look at how many organizations still use Oracle 8, or
> Java 1.3, for example, to see how conservative many shops are with
> respect to upgrades.

Yes but Tom wasn't talking about upgrades. He was talking about
maintenance. You can bet that any respectable enterprise shop is at
least running the latest service packs for the respective releases. The
community does support 7.4 still. However the version that is supported
is service release (or service pack) 19. Thus 7.4.19.

> 
> I'm not saying they should be that conservative, but many
> organizations are and we must be ready to deal with that.
> 

And we already do, far more than we should IMO. This idea that the
volunteer community should somehow provide enterprise class support is
a non starter. That is what the companies surrounding the community
are for. If companies want the community and not the companies
surrounding the community to provide that kind of support, those
companies need to start paying for it.


Sincerely,

Joshua D. Drake
- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit

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

iD8DBQFHxbU/ATb/zqfZUUQRAt1hAJ9NCAK6xTQtF6hcI95rwolqlNpXoACdHIoJ
IVfVRiN5PTpwxAWH6ohY1us=
=L7ov
-END PGP SIGNATURE-

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

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

[GENERAL] RHEL3 configure ssl

[Ray Stell]

How to get ssl configured on RHEL3?  Can't seem to hook up
with openssl/ssl.h.  This is 8.3.0 and it works on RHEL4:

$ cat /etc/issue
Red Hat Enterprise Linux AS release 3 (Taroon Update 9)
Kernel \r on an \m

$ rpm -qa | grep ssl
docbook-style-dsssl-1.76-8
mod_ssl-2.0.46-70.ent
openssl-0.9.7a-33.24
openssl-devel-0.9.7a-33.24
openssl-perl-0.9.7a-33.24
openssl096b-0.9.6b-16.46

$ rpm -ql openssl-devel | grep ssl.h
/usr/include/openssl/hmac.h
/usr/include/openssl/kssl.h
/usr/include/openssl/ssl.h

$ ls -l /usr/include/openssl/ssl.h
-rw-r--r--1 root root74519 Oct 16 15:37 
/usr/include/openssl/ssl.h

$ ./configure --prefix=/usr/local/pgsql830 --with-openssl 
--with-includes=/usr/include/ 
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking which template to use... linux
checking whether to build with 64-bit integer date/time support... no
checking whether NLS is wanted... no
checking for default port number... 5432
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables... 
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ANSI C... none needed
checking if gcc supports -Wdeclaration-after-statement... yes
checking if gcc supports -Wendif-labels... no
checking if gcc supports -fno-strict-aliasing... yes
configure: using CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline 
-Wdeclaration-after-statement -fno-strict-aliasing
checking whether the C compiler still works... yes
checking how to run the C preprocessor... gcc -E
checking allow thread-safe client libraries... no
checking whether to build with Tcl... no
checking whether to build Perl modules... no
checking whether to build Python modules... no
checking whether to build with GSSAPI support... no
checking whether to build with Kerberos 5 support... no
checking whether to build with PAM support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with OpenSSL support... yes
checking for egrep... grep -E
configure: using CPPFLAGS= -D_GNU_SOURCE  -I/usr/include/
configure: using LDFLAGS= 
checking for ld used by GCC... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... yes
checking for tar... /bin/tar
checking whether ln -s works... yes
checking for gawk... gawk
checking for bison... bison -y
configure: using bison (GNU Bison) 1.875c
checking for flex... /usr/bin/flex
configure: using /usr/bin/flex version 2.5.4
checking for perl... /usr/bin/perl
checking for main in -lm... yes
checking for library containing setproctitle... no
checking for library containing dlopen... -ldl
checking for library containing socket... none required
checking for library containing shl_load... no
checking for library containing getopt_long... none required
checking for library containing crypt... -lcrypt
checking for library containing fdatasync... none required
checking for library containing shmget... none required
checking for -lreadline... yes (-lreadline -ltermcap)
checking for inflate in -lz... yes
checking for CRYPTO_new_ex_data in -lcrypto... yes
checking for SSL_library_init in -lssl... yes
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking crypt.h usability... yes
checking crypt.h presence... yes
checking for crypt.h... yes
checking dld.h usability... no
checking dld.h presence... no
checking for dld.h... no
checking fp_class.h usability... no
checking fp_class.h presence... no
checking for fp_class.h... no
checking getopt.h usability... yes
checking getopt.h presence... yes
checking for getopt.h... yes
checking ieeefp.h usability... no
checking ieeefp.h presence... no
checking for ieeefp.h... no
checking langinfo.h usability... yes
checking langinfo.h presence... yes
checking for langinfo.h... yes
checking poll.h usability... yes
checking poll.h presence... yes
checking for poll.h... yes
checking pwd.h usability... yes
checking pwd.h presence... yes
checking for pwd.h... yes
checking sys/ipc.h usability... yes
checking sys/ipc.h presence... yes
checking for sys/ipc.h... yes
checking sys/poll.h usability... yes
checking sys/poll.h presence... yes
checking for sys/poll.h... yes
checking sys/pstat.h usability... no
checking sys/pstat.h presence... no
checking for sys/pstat.h... no
checking sys/resource.h usability... yes
checking sys/resource.h presence... yes

Re: [GENERAL] reindexing

[Greg Smith]

On Wed, 27 Feb 2008, Lew wrote:

One has only to look at how many organizations still use Oracle 8, or 
Java 1.3, for example, to see how conservative many shops are with 
respect to upgrades.  I'm not saying they should be that conservative, 
but many organizations are and we must be ready to deal with that.


Companies that act so conversatively are already getting nailed by lack of 
support in the public versions of software.  For example, in 2007 DST was 
moved around in the US for no good reason, requiring an update to the 
Olson Timezone Database.  If you're a Java user, and you're on 1.3, you 
couldn't get that update unless you have a support contract--the free 
version won't do it.  (ref: 
http://java.sun.com/javase/timezones/DST_faq.html )  Even there only a 
small subset of platforms are supported.  Getting older Oracle versions to 
work there obviously requires the appropriate support contract to see the 
Metalink update, and as I can tell only 8.1 was updated, people running 
8.0 were left out.


If some big-iron shop who is so blind to security issues that they want to 
keep 7.4 on life support, they certainly can find someone to deliver such 
a support agreement on a contract basis.  But they shouldn't expect the 
public project to keep them afloat for free, and saying this project "must 
be ready" to handle them is quite debatable.  Given the limited resources 
of the public volunteers here, supporting ancient versions is a drain it's 
hard to justify outside the context of such a support agreement.  Using 
your own examples, Oracle and Sun sure don't, why should PostgreSQL?


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

[Tom Lane]

Lew <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> There never was a 7.1.4 release, so I suspect the OP meant 7.4.1
>>  not that that speaks very much better for his software maintenance
>> habits.  Even with the more charitable interpretation, it's a version
>> that was obsoleted four years ago next week.

> In my experience at various "big-iron" shops (government agencies, large 
> health-care organizations and the like), four years is not a long time for 
> enterprise software - a version often has to be at least four years old 
> before 
> the powers-that-be decide to try it.  One has only to look at how many 
> organizations still use Oracle 8, or Java 1.3, for example, to see how 
> conservative many shops are with respect to upgrades.

This is not equivalent to "still using Oracle 8".  This is "still using
Oracle 8 and we haven't applied any of Oracle's updates for it".  Is it
even possible for a shop to do that?  I can hardly believe that Oracle
would honor a support contract for a version that's missing four years
worth of bug fixes.

As for the "not wanting to adopt too quickly" argument, why'd they adopt
7.4.1 in the first place?  If you're of the view that no software is
acceptably stable till it's been out a couple years, you should be using
something with a minor number rather higher than 1.

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

[paul rivers]

Lew wrote:

Tom Lane wrote:

There never was a 7.1.4 release, so I suspect the OP meant 7.4.1
 not that that speaks very much better for his software maintenance
habits.  Even with the more charitable interpretation, it's a version
that was obsoleted four years ago next week.


In my experience at various "big-iron" shops (government agencies, 
large health-care organizations and the like), four years is not a 
long time for enterprise software - a version often has to be at least 
four years old before the powers-that-be decide to try it.  One has 
only to look at how many organizations still use Oracle 8, or Java 
1.3, for example, to see how conservative many shops are with respect 
to upgrades.


I'm not saying they should be that conservative, but many 
organizations are and we must be ready to deal with that.




This is completely the opposite of my experience at a very large global 
financial company.  They are extremely concerned with staying current, 
and in fact audit regulations require it for any software not written 
in-house.  If they were still running Oracle 8, for example, they would 
fail internal audit precisely because it is no longer a supported Oracle 
version, and thus security and such patches are no longer available.  
The same would go for operating system patches, firmware, whatever.  The 
release cycle does tend to be slower (from quarterly to yearly) for, 
say, things like AIX or z/OS or DB2, but updates are coming out 
routinely [including security and bug fixes, as well as feature 
additions], and in my experience these shops are definitely keeping up.


The only places I've had direct experience with that tend to run very 
old versions of things are doing so for all the wrong reasons.  They 
seem to be learning, albeit slowly and painfully, the demerits of not 
keeping current.


Just my $0.02,
Paul


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

Re: [GENERAL] Query meltdown: caching results

[Gordon]

(Sorry for the repost but I thought this would be appropriate to both
groups. I did tell Google to delete my first post but odds are some
guys got that copy already anyway)

After a lot of hairpulling, I finally found a mechanism in PHP for
doing what I wanted.  I just had to know 2 things:

1) How to get the PDO engine to use my customized prepared statement
class instead of PDOStatement
2) Extending PDOStatement to transparently add results caching is too
difficult and complex in the timeframe required

Once I knew these things I made a PDOStatement extension class that
instead of trying to transparently add caching to the existing methods
added a couple of new ones instead.  Code below:

 resultCache [$hash])
{
$this -> misses++;
// Execute the query
if ($this -> execute ($params))
{
// Cache the results
$this -> resultCache [$hash] = $this -
> fetchAll
(PDO::FETCH_ASSOC);
}
}
else
{
$this -> hits++;
}
return ($this -> resultCache [$hash]);
}
public function tell (array $params = array ())
// Execute a prepared statement that causes the database to be
modified
{
// Execute the query
if ($this -> execute ($params))
{
$rowCount   = $this -> rowCount ();
if ($rowCount)
{
// Tell the parent Database object to
clear statement caches
$this -> database -> clearResults ();
}
return ($rowCount);
}
}
public function clearResults ()
// Clear cache
{
$this -> resultCache = array ();
}
private function __construct ($db)
// Class constructor
{
$this -> database= $db;
//print_r ($this);
}

}

class Database extends PDO
// Adds some extra functionality to the built in PDO class
{
private $statementCache = array ();
private $txCount= 0;
private $txErr  = false;

// Prepared statement cacheing
public function prepare ($statement, array $options = array
())
{
$hash   = md5 ($statement);
if ((!isset ($this -> statementCache [$hash]))
|| (!is_object ($this -> statementCache [$hash])))
{
//echo ('Preparing statement "'.
$statement .'"');
$this -> statementCache [$hash] =
parent::prepare ($statement,
$options);
}
else
{
//echo ('Statement "' . $statement . '"
already prepared');
}
return ($this -> statementCache [$hash]);
}
public function clearResults ()
// Clear the results cache of all associated prepared
statements
{
foreach ($this -> statementCache as $thisStatement)
{
$thisStatement -> clearResults ();
}
}
// Add support for transaction nesting
public function beginTransaction ()
{
if (($this -> txCount == 0) && (!$this -> txErr))
{
$result = parent::beginTransaction ();
}
$this -> txCount ++;
if (DEBUG_TX)
{
echo ('begin: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
public function commit ()
{
$this -> txCount --;
if ($this -> txCount <= 0)
{
$this -> txErr?  $result =
parent::rollback ():  $result =
parent::commit ();
$this -> txErr   = false;
}
if (DEBUG_TX)
{
echo ('commit: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
public function rollback ()
{
$this -> txErr = true;
$this -> txCount --;
if ($this -> txCount <= 0)
{
$result = parent::rollback ();
$this -> txErr   = false;
}
if (DEBUG_TX)
{
echo ('rollback: ' . $this -> txCount . '
transaction(s)');
}
return ($result);
}
// Housekeeping
pri

Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.

[Andrej Ricnik-Bay]

On 27/02/2008, Olmec Sinclair <[EMAIL PROTECTED]> wrote:
Hi Olmec,

>  I have encountered a similar situation that came about when I
> upgraded to postgres 8.3. Now it won't start stating that:

>  The server must be started under the locale : which does not
> exist any more
>
>  (it looks like the locale is set to {blank} ???)
>
>  Not sure what I should do here. Removing mostprest to an older version
> might be the way to go?
That was Richards suggestion for Geoff who had the
problem.  What OS are you running PostgreSQL on?


> Olmec Sinclair
Cheers,
Andrej


-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

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

Re: [GENERAL] reindexing

[Lew]

Tom Lane wrote:

There never was a 7.1.4 release, so I suspect the OP meant 7.4.1
 not that that speaks very much better for his software maintenance
habits.  Even with the more charitable interpretation, it's a version
that was obsoleted four years ago next week.


In my experience at various "big-iron" shops (government agencies, large 
health-care organizations and the like), four years is not a long time for 
enterprise software - a version often has to be at least four years old before 
the powers-that-be decide to try it.  One has only to look at how many 
organizations still use Oracle 8, or Java 1.3, for example, to see how 
conservative many shops are with respect to upgrades.


I'm not saying they should be that conservative, but many organizations are 
and we must be ready to deal with that.


--
Lew

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

  http://archives.postgresql.org/

Re: [GENERAL] Regarding interval conversion functions and a seeming lack of usefulness

[Alvaro Herrera]

Bill Moran wrote:

> It just seemed like this would be something so common that there'd
> be something in existence already.  I guess I was wrong.

Yeah, I have wished for the same thing myself.

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

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

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

[GENERAL] help for loading a psql file

[akshay bhat]

hello i am new to psql or any database stuff.
i have downloaded an .psql file from internet and wish to open it and see
the data inside.
i am working on windows xp and have installed the software successfully.
please help i am my wits end.


-- 
akshay uday bhat.
t.y.c.e.
department of chemical engineering
university institute of chemical technology
mumbai India

On action alone be thy interest,
Never on its fruits.
Let not the fruits of action be thy motive,
Nor be thy attachment to inaction.

Ask and it shall be given to you; seek and you shall find; knock and it
shall be opened to you

Re: [GENERAL] Regarding interval conversion functions and a seeming lack of usefulness

[Bill Moran]

In response to Tom Lane <[EMAIL PROTECTED]>:

> Bill Moran <[EMAIL PROTECTED]> writes:
> > There seems to be a lack of useful functions for converting intervals
> > to useful representations.  For example, I want to display an interval
> > in hours and fractions of hours only, not hours and minutes.
> 
> Perhaps EXTRACT(EPOCH ...) to get total seconds and then do some
> arithmetic?

Well, that's more or less what I've been doing (although the EPOCH
thing shortened the code up a bit, thanks!).

It just seemed like this would be something so common that there'd
be something in existence already.  I guess I was wrong.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

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

Re: [GENERAL] Query meltdown: caching results

[Gordon]

On Feb 27, 10:37 am, Gordon <[EMAIL PROTECTED]> wrote:
> On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
>
>
>
> > "Norman Peelman" <[EMAIL PROTECTED]> writes:
> > >> My options are, as far as I can tell,
>
> > >> 1) replace the Database PDO extending class with something else that
> > >> provides query results caching in PHP, or
> > >> 2) get Postgres itself to cache the results of queries to avoid
> > >> running them repeatedly during a session.
>
> > You might consider looking at memcached. One way to use it would be to have
> > the PHP application check for the cached object first and use it rather than
> > do any database queries. Then you can use pgmemcached to allow triggers to
> > invalidate cached objects whenever the underlying data changes. (Or you 
> > could
> > even just use pl/php to update or invalidate the cached object through the
> > same code library)
>
> > --
> >   Gregory Stark
> >   EnterpriseDB  http://www.enterprisedb.com
> >   Ask me about EnterpriseDB's Slony Replication support!
>
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
>
> Thanks for the replies, but the problem is really centered around how
> my script interacts with the database.  I know what the problem is,
> and if I was using a structured approach I could figure out
> workarounds fairly quickly, but I chose to do this project in OOP for
> a few reasons (the old CMS was hacked together in a hurry by an
> employee who is now long gone, is horribly written and completely
> unmaintainable, the size of the project seemed to warrant an OOP
> approach, we recently upgraded to PHP 5 and I wanted to take advantage
> of the new features, PDO and prepared statements appeared from the
> documentation to offer major security and performance enhancements,
> and I wanted to increase my experience with OOP development as most
> work I've done before now has been structured) and the high level of
> compartmentalization that OOP demands is coming back to haunt me
> now.
>
> The problem comes when a document is published or unpublished.  All
> the documents that relate to that document need to be republished to
> add or remove the link to the document in question.  When the document
> is published or unpublished the script gets related documents (at the
> moment other documents that share the same folder, but this may change
> in the future to cover more related content) and republishes them.
>
> Each document has associated data, such as the ID of the template to
> apply, its parent, its path back to root, etc, that are generated by
> querying the database in various ways.  For example, the route back to
> path is fetched by iterativly getting the parent folder, getting that
> folder's parent, etc until the script hits the route.  Templates are
> fetched by looking ath the template ID associated with the document.
> If this is 0, then the script goes to the parent folder and gets the
> template associated with the folder.  If that is 0 as well then it
> iterativly goes up until it finds a template to apply or until it hits
> the root, in which case it applies a default template.  The code
> fragment from the script that does this looks like this:
>
> $db -> beginTransaction ();
> if ($doc= CmsItem::factory ('CmsDoc', intval ($_GET 
> ['itm_id']),
> $db, $user))
> {
> if ((($doc  -> itemProps ['itm_publish']) && ($doc   -> 
> unpublish ()))
> || ($doc-> publish ()))
> {
> // Republish related documents
> foreach ($doc -> getSiblingObjects () as $thisDoc)
> {
> if ((get_class ($thisDoc)   == 'CmsDoc')
> && ($thisDoc-> itemProps ['itm_publish']))
> {
> $thisDoc-> republish ();
> }
> }
> // Item status changed
> $db -> commit ();
> $_SESSION ['messageStack'][]= ($doc -> itemProps 
> ['itm_publish']?
> 'Item published':
> 'Item unpublished');
> }
> else
> {
> // Couldn't change published status
> $db -> rollback ();
> $_SESSION ['messageStack'][]= ($doc -> itemProps 
> ['itm_publish']?
> 'Unable to unpublish item':
> 'Unable to publish item');
> }
>
> }
>
> GetSiblingObjects () runs a query that gets a list of IDs that share
> the same parent as the current document.  It then iterates the list
> and spawns a new CMS item for each item in the list and returns them
> as an array.  As folders could be returned as well as documents we
> only run republish () on those items.
>
> CmsDoc -> publish () and CmsDoc -> unpublish () toggle a boolean
> column in 

Re: [GENERAL] Query meltdown: caching results

[paul rivers]

Gordon wrote:

On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
  

"Norman Peelman" <[EMAIL PROTECTED]> writes:


My options are, as far as I can tell,

1) replace the Database PDO extending class with something else that

provides query results caching in PHP, or
2) get Postgres itself to cache the results of queries to avoid
running them repeatedly during a session.


You might consider looking at memcached. One way to use it would be to have
the PHP application check for the cached object first and use it rather than
do any database queries. Then you can use pgmemcached to allow triggers to
invalidate cached objects whenever the underlying data changes. (Or you could
even just use pl/php to update or invalidate the cached object through the
same code library)

--
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

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


The problem is that there is a vast number of redundant queries being
run.    the principle that objects should not depend on a knowledge
of the inner workings of unrelated objects.   Results caching would 
eliminate the problem of
the same queries beign run over and over  
The problem is the mechanics of actually implementing this caching.

I'm using prepared statements almost exclusivly throughout the design,
meaning that the PDOStatement class probably needs to be extended
somehow and my Database prepare()  I can't have been the first person to 
run up against this problem
  


With memcached, your methods to retrieve data go from "get data from db" 
to "get data from cache, and on cache miss get from db and leave a copy 
for the next guy in cache".   Updating the data is not much more 
complicated.  I don't see why this doesn't work for you?  It won't 
compromise anything on the encapsulation front you are concerned about, 
and you can still use your prepared statements for hitting the db, etc.?


Regards,
Paul




---(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] Regarding interval conversion functions and a seeming lack of usefulness

[Tom Lane]

Bill Moran <[EMAIL PROTECTED]> writes:
> There seems to be a lack of useful functions for converting intervals
> to useful representations.  For example, I want to display an interval
> in hours and fractions of hours only, not hours and minutes.

Perhaps EXTRACT(EPOCH ...) to get total seconds and then do some
arithmetic?

regards, tom lane

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

Re: [GENERAL] win32: how to backup (dump does not work)

[Magnus Hagander]

On Wed, Feb 27, 2008 at 03:59:24PM +0100, [EMAIL PROTECTED] wrote:
> On 22:37 Tue 26 Feb , Magnus Hagander wrote:
> > [EMAIL PROTECTED] wrote:
> 
> >> AFAIK stopping the server, zipping data dir, and restarting the server
> >> creates a zip file which is not easily portable to other computers due
> >> to some ntfs file system permission problems.
> >
> > What exactly would those problems be? If you can shut your server down like 
> > this, that's absolutely the easiest way to get it done. It should be 
> > portable across all win32 machines at least - and if that's not enough, 
> 
> FYI (worked around):
> 
> issue (1) may have a little relation to the way pg-installer sets the
> permissions, issue (2) is just a pitfall:
> 
> (1)
> I've simply tried to stop the server, copied the 'data' directory,
> (renamed old one data.bak), started server on a standard stand-alone 
> WinXP (SP2) machine: compared to the working 'data.bak' directory, 
> the copied 'data' folder for some reason gets an additional 
> permissions entry for the postgres user (see security options of 
> directory 'data') which is inherited from the pg's parent directory 
> ('8.2' version directory in my case): this entry removes some 
> permissions so that the effective permissions for user postgres 
> are not enough anymore. Also, when viewing the directories permissions
> of the copied dir, it warns that the order of the permissions are not
> correct and therefore "probably not usable".
> 
> I'm not an expert in win32, but the workaround was to disable
> inheritance for the new data dir, remove the extra entry, and set
> permissions for the postgres user to somewhat full (including
> subfolders).

That is the proper fix. We apply a deny permission on user "postgres" so it
cannot change it's own binaries, in case of an exploit. Then we explicitly
remove this deny permission on the data directory - but if you delete it
and recreate it (or rename and recreate), it will inherit the deny
permission again.

> (2)
> The pg service is not started if some empty folders are missing. Some
> zip progs, however, do not zip/unzip these folders by default. So, one
> has to make sure that these folders are included. The pg server adds a
> hint into windows event log.

Ah, that's interesting. That means you need to take care what ZIP program
you use :-)

//Magnus

---(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] PostgreSQL-installation-problem on Windows XP Home edition

[Magnus Hagander]

On Wed, Feb 27, 2008 at 02:58:22PM +, Richard Huxton wrote:
> Magnus Hagander wrote:
> >On Wed, Feb 27, 2008 at 02:41:09PM +, Richard Huxton wrote:
> >>I'm guessing that you're missing some of the locale (language) files for 
> >> your machine. Have you got anything unusual selected in your language 
> >>settings?
> >
> >That's not actually the error. That happens everywhere :-(
> 
> Ah - it was near the end of the file, and the only error I could see.
> 
> >For some reason I couldn't view that logfile at all,
> 
> I had that - I'm guessing there are high-bit chars that shouldn't be 
> there. Didn't stop vi though :-)

It stopped my vi :-(



> > but look for something
> >that started and never finished. This one has the "Action ended" statement
> >for GetAvailableLocales, my guess is that there is something further down
> >that has a start but no end.
> 
> This is the end of the file. The only un-ended action I can find is 
> CheckAvailable DLLs
> 
> MSI (c) (A4:AC): Doing action: LaunchConditions
> Action start 14:20:50: LaunchConditions.
> Action ended 14:20:50: LaunchConditions. Return value 1.
> MSI (c) (A4:AC): Doing action: CheckAvailableDLLs
> Action start 14:20:50: CheckAvailableDLLs.
> MSI (c) (A4:AC): Creating MSIHANDLE (164) of type 790542 for thread 2988

Ok, that's a known issue. See:
http://www.postgresql.org/docs/faqs.FAQ_windows.html#3.1

particularly the part about cygwin - that's most often the case. But the
other parts also apply.

//Magnus

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

Re: [GENERAL] PostgreSQL-installation-problem on Windows XP Home edition

[Richard Huxton]

Magnus Hagander wrote:

On Wed, Feb 27, 2008 at 02:41:09PM +, Richard Huxton wrote:
I'm guessing that you're missing some of the locale (language) files for 
 your machine. Have you got anything unusual selected in your language 
settings?


That's not actually the error. That happens everywhere :-(


Ah - it was near the end of the file, and the only error I could see.


For some reason I couldn't view that logfile at all,


I had that - I'm guessing there are high-bit chars that shouldn't be 
there. Didn't stop vi though :-)


> but look for something

that started and never finished. This one has the "Action ended" statement
for GetAvailableLocales, my guess is that there is something further down
that has a start but no end.


This is the end of the file. The only un-ended action I can find is 
CheckAvailable DLLs


MSI (c) (A4:AC): Doing action: LaunchConditions
Action start 14:20:50: LaunchConditions.
Action ended 14:20:50: LaunchConditions. Return value 1.
MSI (c) (A4:AC): Doing action: CheckAvailableDLLs
Action start 14:20:50: CheckAvailableDLLs.
MSI (c) (A4:AC): Creating MSIHANDLE (164) of type 790542 for thread 2988

--
  Richard Huxton
  Archonet Ltd

---(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] Regarding interval conversion functions and a seeming lack of usefulness

[Bill Moran]

This has come up a few times over the last few months, and I'm not
too keen on the solutions we've been using.

There seems to be a lack of useful functions for converting intervals
to useful representations.  For example, I want to display an interval
in hours and fractions of hours only, not hours and minutes.  There
are lots of examples of when certain representations are more appropriate
than others (think pregnancy terms, why on earth is the woman never
pregnant for 3 months?  It's always 12 weeks!)

Anyway, rant aside, I can't seem to find anything to do this in PG.
I could write a stored procedure easily enough, but I want to make
sure I'm not reinventing the wheel first (I find it hard to believe
I'm the first person ever wanting to do this!)

Something like:
$ SELECT CONVERT('12 days 13 hours'::INTERVAL AS hour);
 hour
--
   301
$ SELECT CONVERT('6 hours 17 minutes'::INTERVAL AS hour);
 hour
--
6.2833

Am I approaching this problem wrong? or is there something out there
and my Google skills are lacking?

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

---(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-installation-problem on Windows XP Home edition

[Magnus Hagander]

On Wed, Feb 27, 2008 at 02:41:09PM +, Richard Huxton wrote:
> Jan Christensen wrote:
> >Dear Sirs.
> >   
> >  I have a problem with installing PostgreSQL 8.3 on my computer
> >  with Windows XP Home edition and AVG FREE antivirus 7.5.
> >   
> >  The installation-program asks for a installing-language and to save 
> >  the installation-process in a logfile. When I choose English and press
> >  the button "Start", the installation-program writes the message
> >  "Prepare to install" in a short period, and after that nothing is 
> >  happening.
> >   
> >  What is wrong ???
> >   
> >  I enclose the logfile.
> 
> The error seems to be this:
> 
> DEBUG: Error 2769:  Custom Action GetAvailableLocales did not close 1 
> MSIHANDLEs.
> The installer has encountered an unexpected error installing this 
> package. This may indicate a problem with this package. The error code 
> is 2769. The argumens are: GetAvailableLocales, 1,
> Action ended 14:20:50: GetAvailableLocales. Return value 1.
> 
> I'm guessing that you're missing some of the locale (language) files for 
>  your machine. Have you got anything unusual selected in your language 
> settings?

That's not actually the error. That happens everywhere :-(

For some reason I couldn't view that logfile at all, but look for something
that started and never finished. This one has the "Action ended" statement
for GetAvailableLocales, my guess is that there is something further down
that has a start but no end.

(And for the record, we do close all the MSIHANDLEs, that's an API bug at
some point)

//Magnus

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

Re: [GENERAL] PostgreSQL-installation-problem on Windows XP Home edition

[Richard Huxton]

Jan Christensen wrote:

Dear Sirs.
   
  I have a problem with installing PostgreSQL 8.3 on my computer

  with Windows XP Home edition and AVG FREE antivirus 7.5.
   
  The installation-program asks for a installing-language and to save 
  the installation-process in a logfile. When I choose English and press

  the button "Start", the installation-program writes the message
  "Prepare to install" in a short period, and after that nothing is happening.
   
  What is wrong ???
   
  I enclose the logfile.


The error seems to be this:

DEBUG: Error 2769:  Custom Action GetAvailableLocales did not close 1 
MSIHANDLEs.
The installer has encountered an unexpected error installing this 
package. This may indicate a problem with this package. The error code 
is 2769. The argumens are: GetAvailableLocales, 1,

Action ended 14:20:50: GetAvailableLocales. Return value 1.

I'm guessing that you're missing some of the locale (language) files for 
 your machine. Have you got anything unusual selected in your language 
settings?


--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] PostgreSQL-installation-problem on Windows XP Home edition

[Jan Christensen]

Dear Sirs.
   
  I have a problem with installing PostgreSQL 8.3 on my computer
  with Windows XP Home edition and AVG FREE antivirus 7.5.
   
  The installation-program asks for a installing-language and to save 
  the installation-process in a logfile. When I choose English and press
  the button "Start", the installation-program writes the message
  "Prepare to install" in a short period, and after that nothing is happening.
   
  What is wrong ???
   
  I enclose the logfile.
   
  My e-mail-address is : [EMAIL PROTECTED]
   
  I hope to hear from you.
   
  Best regards.
   
  Jan Christensen
   
   
   

   
-

 Find din nye laptop på kelkoo.dk. Se de gode tilbud her! 

postgresql-8.3.log
Description: 99633930-postgresql-8.3.log

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

Re: [GENERAL] autovacuum not freeing up unused space on 8.3.0

[Stuart Brooks]

Are you measuring index as well as table size?  VACUUM FULL is no good
at compacting indexes.
  
  
I am measuring pg_total_relation_size which I believe includes indexes.  
How does one go about compacting indexes if a VACUUM doesn't do the  
trick? I see that a recommendation is to drop and recreate the indexes.  
If one has a system running 24-7, then this might not be feasible.



The simplest way is to use REINDEX INDEX, but it needs a strong lock.

The more complex way is to do

CREATE INDEX CONCURRENTLY index_2 ...-- duplicating the original index
DROP INDEX index;

which does not need to grab a lock for a long period.

  


That does the trick and gets the table size down to what I'd expect from 
a 'clean' run. Now I just need to run a few tests to work out what a 
stable size is for a table with this many rows. Thanks for all the help 
tracking this down. It's really appreciated :)


Kind regards
Stuart


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

Re: [GENERAL] autovacuum not freeing up unused space on 8.3.0

[Alvaro Herrera]

Stuart Brooks wrote:

>> Are you measuring index as well as table size?  VACUUM FULL is no good
>> at compacting indexes.
>>   
> I am measuring pg_total_relation_size which I believe includes indexes.  
> How does one go about compacting indexes if a VACUUM doesn't do the  
> trick? I see that a recommendation is to drop and recreate the indexes.  
> If one has a system running 24-7, then this might not be feasible.

The simplest way is to use REINDEX INDEX, but it needs a strong lock.

The more complex way is to do

CREATE INDEX CONCURRENTLY index_2 ...-- duplicating the original index
DROP INDEX index;

which does not need to grab a lock for a long period.

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

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

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

Re: [GENERAL] How to "paste two tables side-by-side"?

[Kynn Jones]

On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones <[EMAIL PROTECTED]> wrote:

> Suppose I have two tables, A and B, with k(A) and k(B) columns
> respectively, and let's assume to begin with that they have the same number
> of rows r(A) = r(B) = r.
> What's the simplest way to produce a table C having r rows and k(A) + k(B)
> columns, and whose i-th row consists of the k(A) columns of the i-th row of
> A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)?  (By
> "i-th row of A" I mean the i-th row of the listing one would get from
> "SELECT * FROM A", and likewise for B.)
>

Expanding on my own post here, it occurred to me that it would be very nice
to have a function (say) index, that, when used in a SELECT list, would
yield the position in the corresponding table of the current row.  E.g. the
expression

  SELECT index(*) FROM A;

would produce the same table as

  SELECT generate_series( 1, r(A) );

It would also be useful to have a "subscripting function" s (which may be
regarded as somewhat of the inverse of index()) that, given a table
expression E, and an "index expression" I (which could be a single index or
range, or a list of such), will return the table consisting of the rows in E
designated by the indices in I).  For example, either one these queries

  SELECT s( A, 1, 2, 3 );
  SELECT * FROM s( A, 1, 2, 3 );

would produce the same table as

  SELECT * FROM A LIMIT 3;

Does anything like index() or s() exist?  If not, are there other functions
that may be useful in an implementation of index() or s()?

Thanks!

Kynn

[GENERAL] How to "paste two tables side-by-side"?

[Kynn Jones]

Suppose I have two tables, A and B, with k(A) and k(B) columns respectively,
and let's assume to begin with that they have the same number of rows r(A) =
r(B) = r.
What's the simplest way to produce a table C having r rows and k(A) + k(B)
columns, and whose i-th row consists of the k(A) columns of the i-th row of
A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)?  (By
"i-th row of A" I mean the i-th row of the listing one would get from
"SELECT * FROM A", and likewise for B.)
The question could be generalized slightly to the case where the numbers of
rows r(A) and r(B) are not equal.  For example, if r(A) < r(B), the desired
table C would have r(B) rows, and the first k(A) columns of its last r(B) -
r(A) rows would be nulls, reminiscent of a table produced by a right outer
join.

Also, what's the technical term for this type of operation on two tables?

Thanks!

kynn

Re: [GENERAL] Query meltdown: caching results

[Gordon]

On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
> "Norman Peelman" <[EMAIL PROTECTED]> writes:
> >> My options are, as far as I can tell,
>
> >> 1) replace the Database PDO extending class with something else that
> >> provides query results caching in PHP, or
> >> 2) get Postgres itself to cache the results of queries to avoid
> >> running them repeatedly during a session.
>
> You might consider looking at memcached. One way to use it would be to have
> the PHP application check for the cached object first and use it rather than
> do any database queries. Then you can use pgmemcached to allow triggers to
> invalidate cached objects whenever the underlying data changes. (Or you could
> even just use pl/php to update or invalidate the cached object through the
> same code library)
>
> --
>   Gregory Stark
>   EnterpriseDB  http://www.enterprisedb.com
>   Ask me about EnterpriseDB's Slony Replication support!
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend

Thanks for the replies, but the problem is really centered around how
my script interacts with the database.  I know what the problem is,
and if I was using a structured approach I could figure out
workarounds fairly quickly, but I chose to do this project in OOP for
a few reasons (the old CMS was hacked together in a hurry by an
employee who is now long gone, is horribly written and completely
unmaintainable, the size of the project seemed to warrant an OOP
approach, we recently upgraded to PHP 5 and I wanted to take advantage
of the new features, PDO and prepared statements appeared from the
documentation to offer major security and performance enhancements,
and I wanted to increase my experience with OOP development as most
work I've done before now has been structured) and the high level of
compartmentalization that OOP demands is coming back to haunt me
now.

The problem comes when a document is published or unpublished.  All
the documents that relate to that document need to be republished to
add or remove the link to the document in question.  When the document
is published or unpublished the script gets related documents (at the
moment other documents that share the same folder, but this may change
in the future to cover more related content) and republishes them.

Each document has associated data, such as the ID of the template to
apply, its parent, its path back to root, etc, that are generated by
querying the database in various ways.  For example, the route back to
path is fetched by iterativly getting the parent folder, getting that
folder's parent, etc until the script hits the route.  Templates are
fetched by looking ath the template ID associated with the document.
If this is 0, then the script goes to the parent folder and gets the
template associated with the folder.  If that is 0 as well then it
iterativly goes up until it finds a template to apply or until it hits
the root, in which case it applies a default template.  The code
fragment from the script that does this looks like this:

$db -> beginTransaction ();
if ($doc= CmsItem::factory ('CmsDoc', intval ($_GET ['itm_id']),
$db, $user))
{
if ((($doc  -> itemProps ['itm_publish']) && ($doc  -> unpublish 
()))
|| ($doc-> publish ()))
{
// Republish related documents
foreach ($doc -> getSiblingObjects () as $thisDoc)
{
if ((get_class ($thisDoc)   == 'CmsDoc')
&& ($thisDoc-> itemProps ['itm_publish']))
{
$thisDoc-> republish ();
}
}
// Item status changed
$db -> commit ();
$_SESSION ['messageStack'][]= ($doc -> itemProps 
['itm_publish']?
'Item published':
'Item unpublished');
}
else
{
// Couldn't change published status
$db -> rollback ();
$_SESSION ['messageStack'][]= ($doc -> itemProps 
['itm_publish']?
'Unable to unpublish item':
'Unable to publish item');
}
}

GetSiblingObjects () runs a query that gets a list of IDs that share
the same parent as the current document.  It then iterates the list
and spawns a new CMS item for each item in the list and returns them
as an array.  As folders could be returned as well as documents we
only run republish () on those items.

CmsDoc -> publish () and CmsDoc -> unpublish () toggle a boolean
column in the database between true and false for the item being (un)
published.  unpublish () also deletes the concrete file associated
with the DB entry.

publish () and republish () write out a concrete HTML file based on
the content stored in the table for the document 

Re: [GENERAL] Slow query with 100% cpu

[Clodoaldo]

I just ANALYZEd the database and times are back to normal. Sorry for the noise.

Regards, Clodoaldo Pinto Neto

2008/2/27, Clodoaldo <[EMAIL PROTECTED]>:
> Postgresql 8.2.6, Fedora 8, 2 GB memory.
>
>  A query that used to perform in a few seconds is now taking 64 seconds
>  with 100% cpu:
>
>  fahstats=> explain analyze
>  fahstats-> select
>  fahstats->   donor::smallInt as new_members,
>  fahstats->   active_members,
>  fahstats->   d.data::date as day,
>  fahstats->   isodow(d.data::date)::smallInt as dow
>  fahstats-> from
>  fahstats->   team_active_members_history as tam
>  fahstats->   inner join
>  fahstats->   datas as d on d.data_serial = tam.serial_date
>  fahstats->   inner join
>  fahstats->   select_new_members(13802, 8) as nm on nm.data::date = 
> d.data::date
>  fahstats-> where
>  fahstats->   team_number = 13802
>  fahstats->   and
>  fahstats->   d.data_serial = (
>  fahstats(> select data_serial
>  fahstats(> from datas
>  fahstats(> where date_trunc('day', data) = date_trunc('day', d.data)
>  fahstats(> order by data desc
>  fahstats(> limit 1
>  fahstats(> )
>  fahstats-> ;
>
>  QUERY PLAN
>  
> --
>   Hash Join  (cost=46026.35..120281.84 rows=1020 width=16) (actual
>  time=64110.099..64404.762 rows=56 loops=1)
>Hash Cond: ((d.data)::date = (nm.data)::date)
>->  Hash Join  (cost=46001.35..120154.33 rows=204 width=12) (actual
>  time=3378.246..63602.774 rows=812 loops=1)
>  Hash Cond: (d.data_serial = tam.serial_date)
>  ->  Index Scan using data_ndx on datas d
>  (cost=0.00..73990.18 rows=32 width=12) (actual time=19.474..63583.966
>  rows=879 loops=1)
>Filter: (data_serial = (subplan))
>SubPlan
>  ->  Limit  (cost=0.00..11.69 rows=1 width=12) (actual
>  time=10.076..10.078 rows=1 loops=6302)
>->  Index Scan Backward using pk_data on datas
>  (cost=0.00..374.05 rows=32 width=12) (actual time=10.070..10.070
>  rows=1 loops=6302)
>  Filter: (date_trunc('day'::text, data) =
>  date_trunc('day'::text, $0))
>  ->  Hash  (cost=45499.27..45499.27 rows=40167 width=8)
>  (actual time=10.398..10.398 rows=821 loops=1)
>->  Bitmap Heap Scan on team_active_members_history tam
>   (cost=664.25..45499.27 rows=40167 width=8) (actual time=0.428..9.110
>  rows=821 loops=1)
>  Recheck Cond: (team_number = 13802)
>  ->  Bitmap Index Scan on
>  ndx_team_active_members_history  (cost=0.00..654.21 rows=40167
>  width=0) (actual time=0.314..0.314 rows=821 loops=1)
>Index Cond: (team_number = 13802)
>->  Hash  (cost=12.50..12.50 rows=1000 width=12) (actual
>  time=794.528..794.528 rows=56 loops=1)
>  ->  Function Scan on select_new_members nm  (cost=0.00..12.50
>  rows=1000 width=12) (actual time=794.356..794.411 rows=56 loops=1)
>   Total runtime: 64405.040 ms
>  (18 rows)
>
>  I REINDEXed the database before this explain analyze. These are the
>  non commented lines of postgresql.conf
>
>  listen_addresses = '127.0.0.1,10.1.1.101'   # what IP 
> address(es) to listen on;
>  max_connections = 150   # (change requires restart)
>  shared_buffers = 64MB   # min 128kB or max_connections*16kB
>  max_prepared_transactions = 150 # can be 0 or more
>  work_mem = 32MB # min 64kB
>  maintenance_work_mem = 768MB# min 1MB
>  max_stack_depth = 4MB   # min 100kB
>  max_fsm_pages = 100 # min max_fsm_relations*16, 6 bytes each
>  vacuum_cost_delay = 1000# 0-1000 milliseconds
>  vacuum_cost_limit = 5000# 0-1 credits
>  bgwriter_lru_maxpages = 0   # 0-1000 buffers max written/round
>  bgwriter_all_maxpages = 0   # 0-1000 buffers max written/round
>  fsync = off # turns forced synchronization on or 
> off
>  full_page_writes = off
>  wal_buffers = 1024kB# min 32kB
>  checkpoint_segments = 8 # in logfile segments, min 1, 16MB 
> each
>  checkpoint_timeout = 10min  # range 30s-1h
>  effective_cache_size = 768MB
>  geqo_effort = 8 # range 1-10
>  default_statistics_target = 50  # range 1-1000
>  redirect_stderr = on# Enable capturing of stderr into log
>  log_directory = 'pg_log'# Directory where log files are 
> written
>  log_truncate_on_rotation = on   # If on, any existing log file of 
> the same
>  log_rotation_age = 1d   # Automatic rotation of logfiles will
>  log_rotation_size = 0   # Automatic rotation of logfiles will
>  debug_pretty_print = on
>  datestyle 

[GENERAL] Slow query with 100% cpu

[Clodoaldo]

Postgresql 8.2.6, Fedora 8, 2 GB memory.

A query that used to perform in a few seconds is now taking 64 seconds
with 100% cpu:

fahstats=> explain analyze
fahstats-> select
fahstats->   donor::smallInt as new_members,
fahstats->   active_members,
fahstats->   d.data::date as day,
fahstats->   isodow(d.data::date)::smallInt as dow
fahstats-> from
fahstats->   team_active_members_history as tam
fahstats->   inner join
fahstats->   datas as d on d.data_serial = tam.serial_date
fahstats->   inner join
fahstats->   select_new_members(13802, 8) as nm on nm.data::date = d.data::date
fahstats-> where
fahstats->   team_number = 13802
fahstats->   and
fahstats->   d.data_serial = (
fahstats(> select data_serial
fahstats(> from datas
fahstats(> where date_trunc('day', data) = date_trunc('day', d.data)
fahstats(> order by data desc
fahstats(> limit 1
fahstats(> )
fahstats-> ;

 QUERY PLAN
--
 Hash Join  (cost=46026.35..120281.84 rows=1020 width=16) (actual
time=64110.099..64404.762 rows=56 loops=1)
   Hash Cond: ((d.data)::date = (nm.data)::date)
   ->  Hash Join  (cost=46001.35..120154.33 rows=204 width=12) (actual
time=3378.246..63602.774 rows=812 loops=1)
 Hash Cond: (d.data_serial = tam.serial_date)
 ->  Index Scan using data_ndx on datas d
(cost=0.00..73990.18 rows=32 width=12) (actual time=19.474..63583.966
rows=879 loops=1)
   Filter: (data_serial = (subplan))
   SubPlan
 ->  Limit  (cost=0.00..11.69 rows=1 width=12) (actual
time=10.076..10.078 rows=1 loops=6302)
   ->  Index Scan Backward using pk_data on datas
(cost=0.00..374.05 rows=32 width=12) (actual time=10.070..10.070
rows=1 loops=6302)
 Filter: (date_trunc('day'::text, data) =
date_trunc('day'::text, $0))
 ->  Hash  (cost=45499.27..45499.27 rows=40167 width=8)
(actual time=10.398..10.398 rows=821 loops=1)
   ->  Bitmap Heap Scan on team_active_members_history tam
 (cost=664.25..45499.27 rows=40167 width=8) (actual time=0.428..9.110
rows=821 loops=1)
 Recheck Cond: (team_number = 13802)
 ->  Bitmap Index Scan on
ndx_team_active_members_history  (cost=0.00..654.21 rows=40167
width=0) (actual time=0.314..0.314 rows=821 loops=1)
   Index Cond: (team_number = 13802)
   ->  Hash  (cost=12.50..12.50 rows=1000 width=12) (actual
time=794.528..794.528 rows=56 loops=1)
 ->  Function Scan on select_new_members nm  (cost=0.00..12.50
rows=1000 width=12) (actual time=794.356..794.411 rows=56 loops=1)
 Total runtime: 64405.040 ms
(18 rows)

I REINDEXed the database before this explain analyze. These are the
non commented lines of postgresql.conf

listen_addresses = '127.0.0.1,10.1.1.101'   # what IP address(es) 
to listen on;
max_connections = 150   # (change requires restart)
shared_buffers = 64MB   # min 128kB or max_connections*16kB
max_prepared_transactions = 150 # can be 0 or more
work_mem = 32MB # min 64kB
maintenance_work_mem = 768MB# min 1MB
max_stack_depth = 4MB   # min 100kB
max_fsm_pages = 100 # min max_fsm_relations*16, 6 bytes each
vacuum_cost_delay = 1000# 0-1000 milliseconds
vacuum_cost_limit = 5000# 0-1 credits
bgwriter_lru_maxpages = 0   # 0-1000 buffers max written/round
bgwriter_all_maxpages = 0   # 0-1000 buffers max written/round
fsync = off # turns forced synchronization on or off
full_page_writes = off
wal_buffers = 1024kB# min 32kB
checkpoint_segments = 8 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 10min  # range 30s-1h
effective_cache_size = 768MB
geqo_effort = 8 # range 1-10
default_statistics_target = 50  # range 1-1000
redirect_stderr = on# Enable capturing of stderr into log
log_directory = 'pg_log'# Directory where log files are written
log_truncate_on_rotation = on   # If on, any existing log file of the 
same
log_rotation_age = 1d   # Automatic rotation of logfiles will
log_rotation_size = 0   # Automatic rotation of logfiles will
debug_pretty_print = on
datestyle = 'iso, ymd'
timezone = UTC  # actually, defaults to TZ
extra_float_digits = 2  # min -15, max 2
lc_messages = 'en_US.UTF-8' # locale for system error 
message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'  # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for 

Re: [GENERAL] UUID-OSSP for windows.

[Dave Page]

On Wed, Feb 27, 2008 at 3:44 AM, Tim Uckun <[EMAIL PROTECTED]> wrote:
> Where can I get uuid-ossp for windows?  Also where can I get citext for 
> windows.
>
>  These two are missing from the windows installer.

uuid-ossp is missing because until a few days ago the underlying
uuid-ossp wasn't ported to Windows.

citext is not part of core PostgreSQL or something we have any
intention to include in the Windows distribution at this time.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The HOT PostgreSQL Company

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

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

[GENERAL] dump/restore on different locale

[Ivan Sergio Borgonovo]

I just got aware of the difficulties of restoring db from different
locales (money data type).

Even if I was aware money was deprecated it was used just for a
"temp" table that I forgot to drop before dump.

I do have to move stuff from boxes with different locale. Should I be
aware of other problems?

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year

[Alban Hertroys]

On Feb 27, 2008, at 2:32 AM, Bruno Baguette wrote:


Hello !

I have a week number (ISO 8601) and a year, based on theses values,  
I would like to get the first and the last dates of that week.


How I can do that ?

The only solution is doing a big generate_series to build a subset  
that contains the week of all the dates between the 01/01 || year  
and the 31/12 || year. But I find that solution quite dirty and  
ressources consumming.


Is there a cleanest way to do that ?


You can use to_date for most of that, like:
development=> select to_date('01 02 2008', 'ID IW ') AS start,
to_date('07 02 2008', 'ID IW ') AS end;
   start|end
+
 2008-01-07 | 2008-01-07
(1 row)

I'm a bit surprised that specifying the weekdays doesn't make any  
difference here, maybe it's my version?:


development=> select version();
version
 

 PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc  
(GCC) 3.4.6 [FreeBSD] 20060305

(1 row)


Anyway, you can solve that by adding an interval '6 days' to the end  
result.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47c525af233091991417831!



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

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

Re: [GENERAL] citext in windows.

[Magnus Hagander]

On Wed, Feb 27, 2008 at 11:11:52AM +1300, Tim Uckun wrote:
> I just downloaded postgres 8.3 for windows and noticed that citext is
> not an option for columns.
> 
> The web site has the source code but no binaries for windows.

That is something you need to talk to the citext creators about. Ideally,
they shold provide a stackbuilder compatible binary download for windows.


> I downloaded the enterprisedb and noticed that it has the citext.dll
> in the lib directory so I copied the dll over to the postgres lib
> directory and tried to run the sql to install the type buy no joy.
> Postgres complains it can't load the DLL. I triple checked it and it's
> in the proper place.

EnterpriseDB is a different product from PostgreSQL. There may be some
moduels that work in both, but don't count on it. For one thing  I think
the latest EDB is based on 8.2, not 8.3. But there could be a lot of other
reasons as well. If you want a module to load in PostgreSQL, it has to be a
PostgreSQL module.


> What do I have to do in order to get citext to work on windows?

I think you need to talk to the citext people. Unless somebody else has
already made a package available somewhere? 

//Magnus

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

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

Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year

[Richard Huxton]

Bruno Baguette wrote:

Hello !

I have a week number (ISO 8601) and a year, based on theses values, I 
would like to get the first and the last dates of that week.


Broken down step-by-step. End of week left as an exercise

SELECT
  doy,
  EXTRACT(dow FROM doy) AS offset,
  (doy - EXTRACT(dow FROM doy) * '1 day'::interval)::date AS start_of_week
FROM
  (SELECT ('2008-01-04'::date + 8 * '1 week'::interval)::date AS doy)
AS foo;

doy | offset | start_of_week
++---
 2008-02-29 |  5 | 2008-02-24
(1 row)

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] win32: how to backup (dump does not work)

[Magnus Hagander]

On Tue, Feb 26, 2008 at 11:51:34PM -, Joris Dobbelsteen wrote:
> >> AFAIK stopping the server, zipping data dir, and restarting 
> >the server 
> >> creates a zip file which is not easily portable to other 
> >computers due 
> >> to some ntfs file system permission problems.
> >> 
> >IIRC you have to re-assign owner and change permissions after 
> >you copy your data dir to the new server.
> 
> I believe something like NTBackup (or any decent windows backup tool)
> can properly restore permissions. However, this will only work with
> centralized authentication (with NT domain) and NO local computer
> accounts used, when moving between servers (for obvious reasons). I
> think even WinRAR contains an option to do so, but cannot remember for
> sure and I never tried it anyways.

Actually, it will work for local oens as well, as long as you only use
built-in principals. However, the pg install by default uses a custmo
account which, as you say, will not be portable to a different machine.


> In cause you mean the permissions prevent you from making a backup, this
> should be a smaller problem if you are infact a backup operator and use
> a windows backup tool (they get more rights when requested).

If you are a backup operator, it shouldn't be a problem *at all*. If it is,
something is wrong.

//Magnus

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

Re: [GENERAL] win32: how to backup (dump does not work)

[Magnus Hagander]

On Tue, Feb 26, 2008 at 10:30:02PM +0100, Gevik Babakhani wrote:
> > AFAIK stopping the server, zipping data dir, and restarting 
> > the server creates a zip file which is not easily portable to 
> > other computers due to some ntfs file system permission problems.
> > 
> IIRC you have to re-assign owner and change permissions after you copy your
> data dir to the new server.

You don't need to reassign owner normally - we don't care about that. And
if you extract into an existing directory, it should inherit it fine :-)
But true, if you extract into a nwe directory, you may need to change the
permissions.

//Magnus

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