Re: [GENERAL] Two efficiency questions - clustering and ints

[Richard Huxton]

John D. Burger wrote:
I have a good-size DB (some tables approaching 100M rows), with 
essentially static data.


Should I always cluster the tables?  That is, even if no column jumps 
out as being involved in most queries, should I pick a likely one and 
cluster on it?  (Of course, this assumes that doing so won't cause bad 
correlation with any other oft-used column.)


Well you cluster on an index, and if you don't think the index is 
useful, I'd drop it. If you have an index, clustering isn't necessarily 
going to help you unless you regularly read a series of rows in order.


Another question, about integer types - if no cross-type coercion is 
involved, is there any reason not to choose the smallest int type that 
will fit my data?  In particular, I have a column of small-integer 
ratings with, say, values in [1, 10].  If I'm only comparing within such 
ratings, and possibly computing floating point averages, etc., what are 
the good and bad points of using, say, SMALLINT?  What about NUMERIC(1) 
or (2)?


(int2, int2) should pack into 4 bytes on-disk, but due to alignment 
issues I think (int2, int4) still takes up 8 bytes. There has been 
discussion about being able to have different physical column ordering 
on-disk vs. in SQL but no decision as to whether the effort will be 
worthwhile.


Numeric types tend to be slower than their int equivalent, and though 
I've not checked their storage requirements, I'd assume they take more 
space too.


HTH
--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] how to check SQLSTATE

[Hugo]

sorry, forgot to mention psql8.1.4 on fedora core 4On 10/6/06, A. Kretschmer <[EMAIL PROTECTED]
> wrote:am  Thu, dem 05.10.2006, um 17:45:36 -0300 mailte Hugo folgendes:> Hi,
>> is it possible to check for sqlstate inside a function , something like:>>loop>   fetch bla.>   if sqlstate = '02000' then> exit;
>   end if;>   >end loop;>  if I try to save the above I get a :  sqlstate not defined errorWhich version?With release 8.1 you can use SQLSTATE and SQLERRM inside exception
blocks.-> http://www.postgresql.org/docs/8.1/interactive/release-8-1.htmlYou can find the errorcodes there:
http://www.pgadmin.org/docs/1.4/pg/errcodes-appendix.html#errcodes-tableHTH, Andreas--Andreas KretschmerKontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   
http://wwwkeys.de.pgp.net---(end of broadcast)---TIP 6: explain analyze is your friend

[GENERAL] postgresql /var fill

[km]

Hi all,

I have installed postgresql(8.1.4) data dir on a partition (/data) which rests 
on a separate disk from OS disk. The install dir is default (usr/local/pgsql). 
Now when i use use pgbench with scaling factor of 1000 it creates a whooping 15 
GB database. but i see /var partition used space increases considerably - why 
is this happening ? i expected more free disk space to be used in /data !
does it mean that the actual database created lies in /var instead of /data ?
how do i fix it ?

regards,
KM
 

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

Re: [GENERAL] postgresql /var fill

[Ray Stell]

ls -l /
maybe /data is a symlink?



On Fri, Oct 06, 2006 at 07:10:12PM +0530, km wrote:
> Hi all,
> 
> I have installed postgresql(8.1.4) data dir on a partition (/data) which 
> rests on a separate disk from OS disk. The install dir is default 
> (usr/local/pgsql). 
> Now when i use use pgbench with scaling factor of 1000 it creates a whooping 
> 15 GB database. but i see /var partition used space increases considerably - 
> why is this happening ? i expected more free disk space to be used in /data !
> does it mean that the actual database created lies in /var instead of /data ?
> how do i fix it ?
> 
> regards,
> KM
>  
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

-- 

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

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

Re: [GENERAL] postgresql /var fill

[km]

On Fri, Oct 06, 2006 at 09:38:46AM -0400, Ray Stell wrote:
> 
> ls -l /
> maybe /data is a symlink?
> 
no /data is the label for  separate SCSI disk.
no symlinks !!!
/var resides on /dev/sda, and  /data in /dev/sdb
regards,
KM

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

Re: [GENERAL] postgresql /var fill

[Csaba Nagy]

> /var resides on /dev/sda, and  /data in /dev/sdb

I bet you're running a default installation of postgres which has it's
data in /var.
Check your real data directory by running 'ps auxww|grep post', and see
what's after the '-D' parameter... and then when you figure out that the
startup script is using the system default and not what you've
initdb-ed, fix your start script ;-)

Cheers,
Csaba.



---(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 /var fill

[km]

On Fri, Oct 06, 2006 at 03:57:47PM +0200, Csaba Nagy wrote:
> > /var resides on /dev/sda, and  /data in /dev/sdb
> 
> I bet you're running a default installation of postgres which has it's
> data in /var.
> Check your real data directory by running 'ps auxww|grep post', and see
> what's after the '-D' parameter... and then when you figure out that the
> startup script is using the system default and not what you've
> initdb-ed, fix your start script ;-)

nope! i have purposefully deselected postgres 7.4 installation at OS install.
then downloaded postgresql sources of 8.1.4 and installed it in 
/usr/local/pgsql with data dir as /data/pgdata. later , i have set PGDATA to 
/data/pgdata in startup script from contrib/scripts of sources and placed it in 
/etc/init.d
to be sure, i re-checked with rpm -qa|postgres too which didnt give me any 
package listing.

ps auxww |grep postgresql gives /data/pgdata as the data dir.

whats happening ?

regards,
KM



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

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

Re: [GENERAL] postgresql /var fill

[Richard Huxton]

km wrote:

nope! i have purposefully deselected postgres 7.4 installation at OS install.
then downloaded postgresql sources of 8.1.4 and installed it in 
/usr/local/pgsql with data dir as /data/pgdata. later , i have set PGDATA to 
/data/pgdata in startup script from contrib/scripts of sources and placed it in 
/etc/init.d
to be sure, i re-checked with rpm -qa|postgres too which didnt give me any 
package listing.

ps auxww |grep postgresql gives /data/pgdata as the data dir.

whats happening ?


Have you tried looking in /var to see what's there?

find /var -type f -msize +k

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Europe/Moscow timezone

[Brandon Metcalf]

>From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
a timezone input string.  However, this doesn't seem to work:

  db=> INSERT INTO synctimes (time, sreplica, shost, dreplica, dhost, seconds, 
pseconds) VALUES ('10-05-2006 18:26:13 Europe/Moscow', 9407, 27362, 18516, 
35361, 1606, 47);
  ERROR:  invalid input syntax for type timestamp with time zone: "10-05-2006 
18:26:13 Europe/Moscow"

Any ideas as to what I'm doing wrong here?

Thanks.

-- 
Brandon

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

Re: [GENERAL] pg web hosting with tsearch2?

[Robby Russell]

On Sep 29, 2006, at 10:45 AM, Rick Schumeyer wrote:

I hope pg-general is the correct forum for this question…if not  
please let me know the correct location.




I have a pg application that uses tsearch2.  I would like to move  
this application off my local machine and onto a web host  
somewhere.  I have some questions regarding this:


2)   Can someone suggest a web host service that includes  
tsearch2 (or an equivalent text searching component)?


http://www.planetargon.com/

3)All the web hosts I am aware of are still offering only  
pg 7.4.  Does anybody offer pg 8.x ?


http://www.planetargon.com/postgresql_hosting.html

-Robby



---(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 /var fill

[Frank Finner]

Maybe you switched full statement logging on in postgresql.conf? This can 
result in a considerable amount of logfile data in /var/log/somewhere, often 
/var/log/postgresql/ (although 15 GB is really quite a lot).

Regards, Frank.


On Fri, 6 Oct 2006 19:10:12 +0530 km <[EMAIL PROTECTED]> thought long, then sat 
down and wrote:

> Hi all,
> 
> I have installed postgresql(8.1.4) data dir on a partition (/data) which 
> rests on a separate disk from OS disk. The install dir is default 
> (usr/local/pgsql). 
> Now when i use use pgbench with scaling factor of 1000 it creates a whooping 
> 15 GB database. but i see /var partition used space increases considerably - 
> why is this happening ? i expected more free disk space to be used in /data !
> does it mean that the actual database created lies in /var instead of /data ?
> how do i fix it ?
> 
> regards,
> KM
>  
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster


-- 
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606Mail: [EMAIL PROTECTED]
Telefax: 0271 231 8608Web:  http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF  6E6A A74E 67E4 E788 2651



pgp0ZDrln25cG.pgp
Description: PGP signature

Re: [GENERAL] Europe/Moscow timezone

[Martijn van Oosterhout]

On Fri, Oct 06, 2006 at 09:10:33AM -0500, Brandon Metcalf wrote:
> >From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
> a timezone input string.  However, this doesn't seem to work:
> 
>   db=> INSERT INTO synctimes (time, sreplica, shost, dreplica, dhost, 
> seconds, pseconds) VALUES ('10-05-2006 18:26:13 Europe/Moscow', 9407, 27362, 
> 18516, 35361, 1606, 47);
>   ERROR:  invalid input syntax for type timestamp with time zone: "10-05-2006 
> 18:26:13 Europe/Moscow"
> 
> Any ideas as to what I'm doing wrong here?

8.0 only supports such timezones in the form:

'10-05-2006 18:26:13' AT TIME ZONE 'Europe/Moscow';

Including it in the string is only supported from 8.2 beta onward. So
try that out and see how it goes.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Europe/Moscow timezone

[A. Kretschmer]

am  Fri, dem 06.10.2006, um  9:10:33 -0500 mailte Brandon Metcalf folgendes:
> >From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
> a timezone input string.  However, this doesn't seem to work:
> 
>   db=> INSERT INTO synctimes (time, sreplica, shost, dreplica, dhost, 
> seconds, pseconds) VALUES ('10-05-2006 18:26:13 Europe/Moscow', 9407, 27362, 
> 18516, 35361, 1606, 47);
>   ERROR:  invalid input syntax for type timestamp with time zone: "10-05-2006 
> 18:26:13 Europe/Moscow"
> 
> Any ideas as to what I'm doing wrong here?

Perhaps an error in this version, i have 8.1, or wrong syntax.


test=# select TIMESTAMP WITH TIME ZONE '10-05-2006 18:26:13 ' AT TIME ZONE 
'Europe/Moscow';
  timezone
-
 2006-10-05 20:26:13
(1 row)


Try this:

test=# insert into xy values (TIMESTAMP WITH TIME ZONE '10-05-2006 18:26:13' AT 
TIME ZONE 'Europe/Moscow');
INSERT 0 1



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] postgresql /var fill

[km]

> Have you tried looking in /var to see what's there?
> 
> find /var -type f -msize +k

ya looking for files bigger than KB showed only:
/var/lib/rpm/RpmPackages
/var/lib/rpm/Filemd5s

regards,
KM


---(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 /var fill

[Tom Lane]

km <[EMAIL PROTECTED]> writes:
> whats happening ?

Unless you're using tablespaces, the server will definitely not write
anywhere outside its assigned data directory.  Are you sure that the
growth in /var is related at all?  Maybe something spewing messages
to syslog?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Europe/Moscow timezone

[Tom Lane]

"Brandon Metcalf" <[EMAIL PROTECTED]> writes:
> From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
> a timezone input string.

Sorry, no.  That's actually new for 8.2.

regards, tom lane

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

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

[GENERAL] server closed the connection unexpectedly

[Mark Greenbank]

Hi,I get this error when accessing the postgresl database  -- any ideas? What should I look at?

I can query all of the other tables in the database, just not the email_queue table. Weird!
Thanks in advance,
Mark# psql --versionpsql (PostgreSQL) 7.3.2
contains support for command-line editing
# psql
mncis2-rel=# select count(*) from members;
count
---
22676
(1 row)

mncis2-rel=# select count(*) from email_queue;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \q

Re: [GENERAL] postgresql /var fill

[Richard Huxton]

km wrote:

Have you tried looking in /var to see what's there?

find /var -type f -msize +k


ya looking for files bigger than KB showed only:
/var/lib/rpm/RpmPackages
/var/lib/rpm/Filemd5s


1. Try a smaller size and see if you get lots of files at say 5000k.
2. Try something else to find out why /var is being used up
   du -sm /var/*
   Then try the same a little deeper.
3. Check again that /var really is being used up.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] server closed the connection unexpectedly

[Richard Huxton]

Mark Greenbank wrote:

# psql --version
psql (PostgreSQL) 7.3.2


May I be the first to say "GODS ALIVE MAN! WHAT ARE YOU STILL DOING 
RUNNING 7.3.2!". Even if you can't upgrade from 7.3, the latest release 
is 7.3.15 - that's 13 sets of bug-fixes you're ignoring. There's a 
passing chance one of those bugs might be responsible for your error.



contains support for command-line editing

# psql
mncis2-rel=# select count(*) from members;
count
---
22676
(1 row)

mncis2-rel=# select count(*) from email_queue;
server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


What do the logs say?

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] server closed the connection unexpectedly

[Mark Greenbank]

Hi Richard,Thanks for the reply -- yeah, I know it's and old version but management has been reluctant to update a production database. As for the logs, I looked around an didn't see any. pg_ctl start is not using the -l option for logging and output is redirected to /dev/null :(
Anyway, now I have some room to update, which leads to the following questions:1) I'm assuming that if I update 7.3.2 to 7.3.15 I can leave the data in place (that is, without doing a dump/restore) -- is this correct?
2) If I up upgrade to 8.x can just copy the data files or do I have to do a dump/restore? The latter would be hard since pg_dump also fails on this same table and pg_dump doesn't seem to have an 'ignore table' option.
MarkOn 10/6/06, Richard Huxton  wrote:
Mark Greenbank wrote:> # psql --version> psql (PostgreSQL) 7.3.2May I be the first to say "GODS ALIVE MAN! WHAT ARE YOU STILL DOINGRUNNING 7.3.2!". Even if you can't upgrade from 7.3, the latest release
is 7.3.15 - that's 13 sets of bug-fixes you're ignoring. There's apassing chance one of those bugs might be responsible for your error.> contains support for command-line editing>> # psql
> mncis2-rel=# select count(*) from members;> count> ---> 22676> (1 row)>> mncis2-rel=# select count(*) from email_queue;> server closed the connection unexpectedly
>This probably means the server terminated abnormally>before or while processing the request.> The connection to the server was lost. Attempting reset: Failed.What do the logs say?
--   Richard Huxton   Archonet Ltd

Re: [GENERAL] server closed the connection unexpectedly

[Geoffrey]

Mark Greenbank wrote:

Hi,

I get this error when accessing the postgresl database  -- any ideas? What
should I look at?

I can query all of the other tables in the database, just not the
email_queue table. Weird!

Thanks in advance,
Mark

# psql --version
psql (PostgreSQL) 7.3.2
contains support for command-line editing


I will tell you this.  We were seeing a similar problem on 7.4.6 and we 
upgraded this past Thursday morning to 7.4.13 and thus far we have not 
seen another of these errors.  Thursday and Friday are our busiest days 
and we saw this error more on these days then any other.


As far as we are concerned, the jury is still out until we have more 
time to verify this resolved the issue.


I'd suggest you upgrade to the latest 7.4, although from 7.3.2 that may 
not be as simple as it was for us.


--
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] server closed the connection unexpectedly

[Douglas McNaught]

"Mark Greenbank" <[EMAIL PROTECTED]> writes:

> 1) I'm assuming that if I update 7.3.2 to 7.3.15 I can leave the data in place
> (that is, without doing a dump/restore) -- is this correct?

Yes--minor releases don't require dump/restore.

> 2) If I up upgrade to 8.x can just copy the data files or do I have to do a
> dump/restore? The latter would be hard since pg_dump also fails on this same
> table and pg_dump doesn't seem to have an 'ignore table' option.

You would use the 8.x pg_dump in this case (against the 7.3 database),
which may or may not help if you have corrupted table data.  If that's
the problem, it's best to fix the corruption before trying to upgrade.

-Doug

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

Re: [GENERAL] server closed the connection unexpectedly

[Mark Greenbank]

I've enabled logging, having upgraded to 7.3.4 (since that was the simplest yum updated :) and I see the following messages:FATAL:  unsupported frontend protocol
LOG:  server process (pid 26548) was terminated by signal 11
LOG:  terminating any other active server processesFATAL:  The database system is in recovery mode
WARNING:  Message from PostgreSQL backend:    The Postmaster has informed me that some other backend
    died abnormally and possibly corrupted shared memory.
    I have rolled back the current transaction and am    going to terminate your database system connection and exit.
    Please reconnect to the database system and repeat your query.
LOG:  all server processes terminated; reinitializing shared memory and semaphores
LOG:  database system was interrupted at 2006-10-06 11:34:17 EDTLOG:  checkpoint record is at D/72C79A94
LOG:  redo record is at D/72C79A94; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 74267383; next oid: 89469046LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  ReadRecord: record with zero length at D/72C79AD4
LOG:  redo is not requiredFATAL:  unsupported frontend protocol
FATAL:  The database system is starting upFATAL:  unsupported frontend protocol
FATAL:  The database system is starting up
FATAL:  unsupported frontend protocolFATAL:  The database system is starting up
FATAL:  unsupported frontend protocolFATAL:  The database system is starting up
FATAL:  unsupported frontend protocol
FATAL:  The database system is starting upLOG:  database system is readyThe worrying bit (at least to my uneducated eye) is the line:
LOG:  ReadRecord: record with zero length at D/72C79AD4Thanks,MarkOn 10/6/06, 
Mark Greenbank <[EMAIL PROTECTED]> wrote:
Hi Richard,Thanks for the reply -- yeah, I know it's and old version but management has been reluctant to update a production database. As for the logs, I looked around an didn't see any. pg_ctl start is not using the -l option for logging and output is redirected to /dev/null :(
Anyway, now I have some room to update, which leads to the following questions:1) I'm assuming that if I update 7.3.2 to 7.3.15 I can leave the data in place (that is, without doing a dump/restore) -- is this correct?
2) If I up upgrade to 8.x can just copy the data files or do I have to do a dump/restore? The latter would be hard since pg_dump also fails on this same table and pg_dump doesn't seem to have an 'ignore table' option.
MarkOn 10/6/06, Richard Huxton <
dev@archonet.com> wrote:
Mark Greenbank wrote:> # psql --version> psql (PostgreSQL) 7.3.2May I be the first to say "GODS ALIVE MAN! WHAT ARE YOU STILL DOINGRUNNING 7.3.2!". Even if you can't upgrade from 7.3, the latest release
is 7.3.15 - that's 13 sets of bug-fixes you're ignoring. There's apassing chance one of those bugs might be responsible for your error.> contains support for command-line editing>> # psql

> mncis2-rel=# select count(*) from members;> count> ---> 22676> (1 row)>> mncis2-rel=# select count(*) from email_queue;> server closed the connection unexpectedly

>This probably means the server terminated abnormally>before or while processing the request.> The connection to the server was lost. Attempting reset: Failed.What do the logs say?
--   Richard Huxton   Archonet Ltd

Re: [GENERAL] server closed the connection unexpectedly

[Mark Greenbank]

Hi Doug,Are there any pointers as to how to fix corrupted data? Is seems like that might be the problem as I'm seeing a zero-length read in the log file (see my previous email).Thanks again,Mark
On 10/6/06, Douglas McNaught <[EMAIL PROTECTED]> wrote:
"Mark Greenbank" <[EMAIL PROTECTED]> writes:> 1) I'm assuming that if I update 7.3.2 to 7.3.15 I can leave the data in place> (that is, without doing a dump/restore) -- is this correct?
Yes--minor releases don't require dump/restore.> 2) If I up upgrade to 8.x can just copy the data files or do I have to do a> dump/restore? The latter would be hard since pg_dump also fails on this same
> table and pg_dump doesn't seem to have an 'ignore table' option.You would use the 8.x pg_dump in this case (against the 7.3 database),which may or may not help if you have corrupted table data.  If that's
the problem, it's best to fix the corruption before trying to upgrade.-Doug

Re: [GENERAL] Europe/Moscow timezone

[Brandon Metcalf]

k == kleptog@svana.org writes:

 k> On Fri, Oct 06, 2006 at 09:10:33AM -0500, Brandon Metcalf wrote:
 k> > >From what I can tell, PostgreSQL 8.0.3 should support Europe/Moscow as
 k> > a timezone input string.  However, this doesn't seem to work:
 k> >
 k> >   db=> INSERT INTO synctimes (time, sreplica, shost, dreplica, dhost, 
seconds, pseconds) VALUES ('10-05-2006 18:26:13 Europe/Moscow', 9407, 27362, 
18516, 35361, 1606, 47);
 k> >   ERROR:  invalid input syntax for type timestamp with time zone: 
"10-05-2006 18:26:13 Europe/Moscow"
 k> >
 k> > Any ideas as to what I'm doing wrong here?

 k> 8.0 only supports such timezones in the form:

 k> '10-05-2006 18:26:13' AT TIME ZONE 'Europe/Moscow';

 k> Including it in the string is only supported from 8.2 beta onward. So
 k> try that out and see how it goes.


That doesn't work, either.

  db=> INSERT INTO synctimes (time, sreplica, shost, dreplica, dhost, seconds, 
pseconds) VALUES ('10-05-2006 18:26:13' AT TIME ZONE 'Europe/Moscow', 99, 
27362, 18516, 35361, 1606, 47);
  ERROR:  time zone "europe/moscow" not recognized

-- 
Brandon

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

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

Re: [GENERAL] server closed the connection unexpectedly

[Tom Lane]

"Mark Greenbank" <[EMAIL PROTECTED]> writes:
> [ select count(*) from email_queue leads to ]
> LOG:  server process (pid 26548) was terminated by signal 11

If this is repeatable then it probably indicates corrupt data on-disk
(which 7.3.2 is mostly lacking any defenses for).  The trick is to find
and delete the corrupted rows or pages.  You can generally get somewhere
by a process of binary search, ie, find the largest N such that
"select ctid,* from email_queue limit N" doesn't fail.  The last ctid
you can extract this way will be one or two before the (first) bad row.

Given that it's a count(*) that's failing, the problem is probably with
a page or tuple header and not any individual data field, so you might
not have any good recovery option beyond zeroing the whole page
containing the error.  See the mailing list archives for past
discussions of recovering from data corruption for more info.

> The worrying bit (at least to my uneducated eye) is the line:
> LOG:  ReadRecord: record with zero length at D/72C79AD4

That's normal operation, don't worry about it.

regards, tom lane

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

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

Re: [GENERAL] PostgreSQL Database Transfer between machines(again)

[Brian J. Erickson]

> > You can boot from any rescue CD, mount the partition, copy the database
> > directory away and then copy it back once you have reinstalled. This is
> > safe because it is on the same machine. It is not safe to copy the
> > database to some arbitrary computer and expect it to run.
That is basically the plan but I want to make sure that I have all of the
data.

> O.k. hold on... are you getting any errors in /var/log/messages?
Here is some of the "/var/log/messages" file
---Begin
--
Oct  6 07:57:27 Info1A kernel: PCI: Using configuration type 1
Oct  6 07:57:27 Info1A kernel: PCI: Probing PCI hardware
Oct  6 07:57:27 Info1A mount: mount: can't open /etc/mtab for writing:
Input/output error
Oct  6 07:57:27 Info1A kernel: PCI: Probing PCI hardware (bus 00)
Oct  6 07:57:27 Info1A netfs: Mounting other filesystems:  failed
.
.
.
Oct  6 07:57:33 Info1A su(pam_unix)[1229]: session opened for user postgres
by (uid=0)
Oct  6 07:57:33 Info1A su(pam_unix)[1229]: session closed for user postgres
Oct  6 07:57:34 Info1A postgresql: Starting postgresql service:  failed
-End
-



- Original Message -
From: "Joshua D. Drake" <[EMAIL PROTECTED]>
To: "AgentM" <[EMAIL PROTECTED]>
Cc: "PostgreSQL Mailing lists" 
Sent: Thursday, October 05, 2006 5:42 PM
Subject: Re: [GENERAL] PostgreSQL Database Transfer between machines(again)


> AgentM wrote:
> >
> > On Oct 5, 2006, at 19:10 , Brian J. Erickson wrote:
> >
> >>> And since it's a text file, can't someone fix it with $EDITOR?
> >>
> >> I tried to edit the file, but I get the Input/Output error.
> >>
>
> O.k. hold on... are you getting any errors in /var/log/messages?
>
> Joshua D. Drake
>
>
>
>
> >> The recommendatation was to re-install the OS.
> >> However, I DO NOT want to lose my database,
> >> so I am tring to backup the database.
> >
> > You can boot from any rescue CD, mount the partition, copy the database
> > directory away and then copy it back once you have reinstalled. This is
> > safe because it is on the same machine. It is not safe to copy the
> > database to some arbitrary computer and expect it to run. Make sure to
> > match the database version. Good luck!
> >
> > -M
> >
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> >   http://archives.postgresql.org
> >
>
>
> --
>
>=== The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>Providing the most comprehensive  PostgreSQL solutions since 1997
>  http://www.commandprompt.com/
>
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>


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

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

Re: [GENERAL] Europe/Moscow timezone

[Tom Lane]

"Brandon Metcalf" <[EMAIL PROTECTED]> writes:
> k == kleptog@svana.org writes:
>  k> 8.0 only supports such timezones in the form:
>  k> '10-05-2006 18:26:13' AT TIME ZONE 'Europe/Moscow';

> That doesn't work, either.

I think AT TIME ZONE was updated to allow long-form tz names in 8.1.
In 8.0 you probably can't do anything except SET TIME ZONE with 'em.

regards, tom lane

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

Re: [GENERAL] PostgreSQL Database Transfer between machines(again)

[Joshua D. Drake]

Brian J. Erickson wrote:
>>> You can boot from any rescue CD, mount the partition, copy the database
>>> directory away and then copy it back once you have reinstalled. This is
>>> safe because it is on the same machine. It is not safe to copy the
>>> database to some arbitrary computer and expect it to run.
> That is basically the plan but I want to make sure that I have all of the
> data.
> 
>> O.k. hold on... are you getting any errors in /var/log/messages?
> Here is some of the "/var/log/messages" file


That's odd. Have you tried removing /etc/mtab manually and rebooting?
does the problem still occur? As far as PostgreSQL... you need to look
in to postgresql logs.

Did you compile from source? What version of Linux is this?

Joshua D. Drake


> ---Begin
> --
> Oct  6 07:57:27 Info1A kernel: PCI: Using configuration type 1
> Oct  6 07:57:27 Info1A kernel: PCI: Probing PCI hardware
> Oct  6 07:57:27 Info1A mount: mount: can't open /etc/mtab for writing:
> Input/output error
> Oct  6 07:57:27 Info1A kernel: PCI: Probing PCI hardware (bus 00)
> Oct  6 07:57:27 Info1A netfs: Mounting other filesystems:  failed
> .
> .
> .
> Oct  6 07:57:33 Info1A su(pam_unix)[1229]: session opened for user postgres
> by (uid=0)
> Oct  6 07:57:33 Info1A su(pam_unix)[1229]: session closed for user postgres
> Oct  6 07:57:34 Info1A postgresql: Starting postgresql service:  failed
> -End
> -
> 
> 
> 
> - Original Message -
> From: "Joshua D. Drake" <[EMAIL PROTECTED]>
> To: "AgentM" <[EMAIL PROTECTED]>
> Cc: "PostgreSQL Mailing lists" 
> Sent: Thursday, October 05, 2006 5:42 PM
> Subject: Re: [GENERAL] PostgreSQL Database Transfer between machines(again)
> 
> 
>> AgentM wrote:
>>> On Oct 5, 2006, at 19:10 , Brian J. Erickson wrote:
>>>
> And since it's a text file, can't someone fix it with $EDITOR?
 I tried to edit the file, but I get the Input/Output error.

>> O.k. hold on... are you getting any errors in /var/log/messages?
>>
>> Joshua D. Drake
>>
>>
>>
>>
 The recommendatation was to re-install the OS.
 However, I DO NOT want to lose my database,
 so I am tring to backup the database.
>>> You can boot from any rescue CD, mount the partition, copy the database
>>> directory away and then copy it back once you have reinstalled. This is
>>> safe because it is on the same machine. It is not safe to copy the
>>> database to some arbitrary computer and expect it to run. Make sure to
>>> match the database version. Good luck!
>>>
>>> -M
>>>
>>> ---(end of broadcast)---
>>> TIP 4: Have you searched our list archives?
>>>
>>>   http://archives.postgresql.org
>>>
>>
>> --
>>
>>=== The PostgreSQL Company: Command Prompt, Inc. ===
>> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>>Providing the most comprehensive  PostgreSQL solutions since 1997
>>  http://www.commandprompt.com/
>>
>>
>>
>> ---(end of broadcast)---
>> TIP 3: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/docs/faq
>>
> 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

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

Re: [GENERAL] Two efficiency questions - clustering and ints

[John D. Burger]

Richard Huxton wrote:

Should I always cluster the tables?  That is, even if no column  
jumps out as being involved in most queries, should I pick a  
likely one and cluster on it?


Well you cluster on an index, and if you don't think the index is  
useful, I'd drop it. If you have an index, clustering isn't  
necessarily going to help you unless you regularly read a series of  
rows in order.


Right, but clustering on that index means an index scan (with that  
index) will be faster.  This is uninteresting if the index doesn't  
get used, but is there any =downside= to doing it?


Here's a simpler question - for static data, should I always cluster  
on the index I think will be used the most?


Thanks.

- John D. Burger
  MITRE


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

[GENERAL] DELETE eats up all memory and crashes box

[Worky Workerson]

When I issue a fairly large DELETE query which has multiple tables
with FOREIGN KEY  CASCADE on them, Postgres eats up *all* the
memory on my system and the system crashes.  I figure that there are
two problems, one is PG eating up all of the memory, the other is the
system crashing and not telling me anything (neither /var/log/messages
nor kernel logging tell me anything).  What could cause PG to eat up
all the memory on a system?

Any ideas and/or monitoring methods that I could use to figure out
what is going wrong?  I had a top running, and the last thing that I
see is that PG is using up about 22gb of memory and postmaster and
kswapd is working hardest.  Perhaps I also set some of my
postgresql.conf numbers incorrectly?

Thanks!



Simplified schema:

CREATE TABLE ip_info (
   ip IP4R PRIMARY KEY,
   country VARCHAR,
   ...
);

CREATE TABLE flow (
   fm_ip IP4R NOT NULL REFERENCES ip_info (ip) ON DELETE CASCADE,
   ...
);

Offending query: DELETE FROM ip_info WHERE country IN ('Canada',
'Yugoslavia', ...);

Hardware: Quad Dual-core Opteron, 16GB RAM, 8GB swap
Software: PostgreSQL 8.1.3 on RHEL4 x64_64
Purpose:  Dedicated PG data-warehouse server

Changed config settings:
 shared_buffers = 6
 temp_buffers = 1
 work_mem = 524288
 maintenance_work_mem = 524288
 max_fsm_pages=200
 max_fsm_relations=10
 wal_buffers=128
 checkpoint_segments=128
 checkpoint_timeout=3000
 effective_cache_size = 120
 random_page_cost = 2

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

Re: [GENERAL] server closed the connection unexpectedly

[Jeff Davis]

On Fri, 2006-10-06 at 11:20 -0400, Mark Greenbank wrote:
> Hi Richard,
> 
> Thanks for the reply -- yeah, I know it's and old version but
> management has been reluctant to update a production database. 

If there exists a patchlevel (the third component of the version) higher
than the one you're using, generally it's safer to upgrade the
production system than not. Updating the patchlevel does not require a
dump/restore, just a restart. Just schedule a minute or two of downtime.

> 1) I'm assuming that if I update 7.3.2 to 7.3.15 I can leave the data
> in place (that is, without doing a dump/restore) -- is this correct? 

Correct, no dump/reload necessary, just upgrade and restart postgres.

> 2) If I up upgrade to 8.x can just copy the data files or do I have to
> do a dump/restore? The latter would be hard since pg_dump also fails
> on this same table and pg_dump doesn't seem to have an 'ignore table'
> option. 

Upgrading the first or second component of the version number (i.e. 7.3
to 7.4 or 7.4 to 8.0) requires a full dump/reload. However, it might be
worth considering since you're using a version that's been obsolete for
years.

Regards,
Jeff Davis


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

Re: [GENERAL] DELETE eats up all memory and crashes box

[Tom Lane]

"Worky Workerson" <[EMAIL PROTECTED]> writes:
> When I issue a fairly large DELETE query which has multiple tables
> with FOREIGN KEY  CASCADE on them, Postgres eats up *all* the
> memory on my system and the system crashes.

Well, the memory eating is easy to explain: pending-trigger-event list.
System crash sounds like a kernel bug or misconfiguration.  You might
want to make sure you have "strict" memory overcommit mode set, else the
problem might just be an inopportune choice of target by the OOM killer.

(I *think* they fixed the OOM killer to never select init, but there are
still plenty of processes you'd not like it to pick ...)

regards, tom lane

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

Re: [GENERAL] storing transactions

[kaspro]

Mabye I made myself not clear enough- sorry for that...
What I want is having a statement like:

PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))
BEGIN
   ---check if something is valid
   ---compute something
   ---store values I got via THIS query and put them in table A, B and C
   ---see wether everything is ok
COMMIT;

...which I execute from a client like: exec MyProcedure(Value1, Value2, Value3)

Sorry for beeing not exact enough...

Thanks in advance,
Chris

> -Ursprüngliche Nachricht-
> Von: Chris Mair <[EMAIL PROTECTED]>
> Gesendet: 05.10.06 18:43:23
> An: [EMAIL PROTECTED]
> CC: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] storing transactions


> 
> > I’ve been studying the whole evening and don’t seem to find an answer:
> > I want to “store” transactions on the server- like view’s, or, 
> > (sorry) as in M$ SQL Server CREATE OR REPLACE TRANSACTION xyz() 
> 
> Of course, it's possible.
> What you need is
> CREATE OR REPLACE FUNCTION xyz() RETURNS trigger AS ...
> then
> CREATE TRIGGER ... EXECUTE PROCEDURE xyz();
> 
> 
> Look here for an example:
> http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html
> 
> Bye,
> Chris.
> 
> 


_
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=0066


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

[Martins Mihailovs]

Hello!

I'm using PgSQL for a 3 years for web applications, but not only. But 
the main problem is in encoding. My web applications are used by 
international (mostly 3 languages: latvian (LATIN7), english and 
russian). The best (mostly) solution is to use UTF-8, but there are a 
lot of problems. The main problem is in searching (tsearch2, lower, 
upper, e.c.) and sorting.


I would be a glad to hear your solutions, experience in web application 
with multi languages (searching with indexing, sorting and others 
problems with multi byte encoding).


For developers: what are your future plans about UTF-8 in Postgres?

thanx
Martins

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

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

[GENERAL] URGENT - startup process (PID 29541) was terminated by signal 6

[andy rost]

Our Opteron DB server had a problem with its RAID controller requiring 
an immediate shutdown of our Postgres server (8.1.3 on FreeBSD 6.0 
release number 10). We used kill -QUIT on the postmaster PID.


After repairing and rebooting the server we tried to start Postgres and 
get the following:


2006-10-06 12:32:40 CDT FATAL:  the database system is starting up
2006-10-06 12:32:40 CDT FATAL:  the database system is starting up
2006-10-06 12:32:40 CDT PANIC:  heap_clean_redo: no block
2006-10-06 12:32:40 CDT LOG:  startup process (PID 29541) was terminated 
by signal 6
2006-10-06 12:32:40 CDT LOG:  aborting startup due to startup process 
failure



At shutdown we had the following:

2006-10-06 12:08:48 CDT PANIC:  right sibling is not next child in 
"winddir_idxu"

2006-10-06 12:08:48 CDT STATEMENT:  VACUUM ANALYZE VERBOSE
2006-10-06 12:08:49 CDT LOG:  server process (PID 91933) was terminated 
by signal 6

2006-10-06 12:08:49 CDT LOG:  terminating any other active server processes

Anything I can do?


Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
[EMAIL PROTECTED]
http://www.nohrsc.noaa.gov



---(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] PL/SQL Optimum search

[Pailloncy Jean-Gérard]

Hi,

I would to know if there is some libraries with general algorithm for  
Optimum search.


Exemple: I have some function
res := error_estimate(x real, y real, z real, t real)

I wrote a function that
set of res := iterate(nb_iteration, start_x real, step_x real, 
that return a
res := ROW(best_x, best_y, best_z, best_t, best_res)

But each time I want to test a new function, I have to write a new  
iterete one too.


I would like to know if someone as already writen something like
row(array{{dimension, value, step}}, best_value) := optimum_search 
( function_name, array{{dimension, start, step}}, max_iteration,  
min_delta)


Note: the function I try to find optimum is enough smooth to do a  
gradient search.


I would appreciate any information.
JG Pailloncy 


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

Re: [GENERAL] [pgadmin-support] Installation on mandriva 2006

[hugoksouza]

Hi Devrim,

Thanks for your prompt and accurate reply.

It seems it worked for me.


Thanks and regards,


-Original Message-
From: devrim (at) commandprompt (dot) com
To: Hugo Kawamorita de Souza
Cc: pgadmin-support@postgresql.org
Sent: Fri, 6 Oct 2006 3:32 AM
Subject: Re: [pgadmin-support] Installation on mandriva 2006

 Hi,

On Fri, 2006-10-06 at 00:42 -0400, [EMAIL PROTECTED] wrote:

 Is there a real solution ofr this issue?


Sure.

http://developer.postgresql.org/~devrim/rpms/compat/

Use the RPM which is suitable for your platform. This is Red Hat (so its
clone) problem if you install PGDG RPMs instead of Red Hat RPMs. RH
provide 7.4 RPMs, and they build php-pgsql stuff and other related
things with libpq3. Recent PGDG RPMs for RHEL 4 provide libpq.so.4.
That's why we built a compat lib that solves this problem. Just run rpm
-ivh against this.


  I am not a MySQL fan, but MySQL has a compatibility-libs pkg/RPM
that takes care od issues like this..


I think you should Google a bit more before talking about My$QL (it
drives me crazy).

Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



Check Out the new free AIM(R) Mail -- 2 GB of storage and 
industry-leading spam and email virus protection.



---(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] Storing images in PostgreSQL databases (again)

[Jean-Christophe Roux]

Why would I set a bytea column (containing picures) as a primary key? Because I want to be sure that the same image is inserted only once (that requirement comes from a real project) and using a primary key for that purpose makes sense to me. Am I going to retrieve an image row by its image data? I would certainly like! For instance, I would like to get the pictures whose main color is green (requirement from a real project), and a select * from images where main_color(image) = 'green' would be nice.JCR- Original Message From: Alexander Staubo <[EMAIL PROTECTED]>To: Jean-Christophe Roux
 <[EMAIL PROTECTED]>Cc: pgsql-general@postgresql.orgSent: Thursday, October 5, 2006 7:35:04 PMSubject: Re: [GENERAL] Storing images in PostgreSQL databases (again)On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote:> By the way, is it practical to set a bytea column (containing  > pictures) as primary key? That would severely slow down many  > operations I guess.Why would you? It's possible, but completely impractical, since image  data typically exceeds the index page size. Moreover, are you really  going to retrieve an image row by its image data?Alexander.---(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] Installation on mandriva 2006

[hugoksouza]

Hi all, hi Hendrick,

Just a note on this.

I was trying to install pgadmin3-1.4.3-1.i686.rpm on CentOS 4.4 with 
PostgreSQL 8.1.4, but I could not install the PgAdmin3 1.4.3 because it 
requires the /usr/lib/libpq.so.3.
I tried to do the symbolic link and for my surprise it did not work, it 
complained in the same way, even after rebooting, just in case. 
libpq.so.3 was not in my "ldconfig -p".


As this workaround did not work, I had to "break" yum/RPM 
"integrity/consistency", which I really do not like.
I ended up forcing the RPM installation of 
postgresql-libs-7.4.13-2.RHEL4.1 ...
This RPM rewrote some locale files, so after that I forced the 
resinstallation of my postgresql-libs-8.1.4-3PGDG.


Is there a real solution ofr this issue?

I am not a MySQL fan, but MySQL has a compatibility-libs pkg/RPM that 
takes care od issues like this...
I have not seen something similar for PostgreSQL and I have not found 
anything else on the Internet about this issue...


This is my config:

CentOS 4.4
Linux localhost 2.6.9-42.0.2.EL #1 Tue Aug 22 23:56:05 CDT 2006 i686 
i686 i386 GNU/Linux

rpm -qa | egrep -i -e "(postg)|(pg)|(wx)|(sdl)" | sort:
gnupg-1.2.6-6
gpg-pubkey-443e1821-421f218f
libgpg-error-1.0-1
pgadmin3-1.4.3-1
postgresql-8.1.4-3PGDG
postgresql-contrib-8.1.4-3PGDG
postgresql-libs-7.4.13-2.RHEL4.1
postgresql-libs-8.1.4-3PGDG
postgresql-server-8.1.4-3PGDG
SDL-1.2.7-8
wxGTK-2.6.3-2.6.3.2.1


Regards,

Check Out the new free AIM(R) Mail -- 2 GB of storage and 
industry-leading spam and email virus protection.



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

  http://archives.postgresql.org

Re: [GENERAL] Two efficiency questions - clustering and ints

[Casey Duncan]

On Oct 6, 2006, at 11:12 AM, John D. Burger wrote:


Richard Huxton wrote:

Should I always cluster the tables?  That is, even if no column  
jumps out as being involved in most queries, should I pick a  
likely one and cluster on it?


Well you cluster on an index, and if you don't think the index is  
useful, I'd drop it. If you have an index, clustering isn't  
necessarily going to help you unless you regularly read a series  
of rows in order.


Right, but clustering on that index means an index scan (with that  
index) will be faster.  This is uninteresting if the index doesn't  
get used, but is there any =downside= to doing it?


Here's a simpler question - for static data, should I always  
cluster on the index I think will be used the most?


Depends on the queries. If the index is on a foreign key value where  
there may be many rows with the same key scattered about it will help  
queries that lookup using that foreign key. Clustering on a column  
with high cardinality isn't much of a win typically for single key  
lookups (depends on the lookup pattern), but would be for ranges and  
possibly for sorting on that column.


It also depends on the size of the table and indices. If they are  
small enough to fit in memory then clustering to reduce random access  
isn't really helpful.


I would suggest doing some timing tests on typical queries with the  
data unclustered and clustered to know what you are gaining.


-Casey

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

  http://archives.postgresql.org

Re: [GENERAL] DELETE eats up all memory and crashes box

[Worky Workerson]

On 10/6/06, Tom Lane <[EMAIL PROTECTED]> wrote:

"Worky Workerson" <[EMAIL PROTECTED]> writes:
> When I issue a fairly large DELETE query which has multiple tables
> with FOREIGN KEY  CASCADE on them, Postgres eats up *all* the
> memory on my system and the system crashes.

Well, the memory eating is easy to explain: pending-trigger-event list.
System crash sounds like a kernel bug or misconfiguration.  You might
want to make sure you have "strict" memory overcommit mode set, else the
problem might just be an inopportune choice of target by the OOM killer.


You were right ... had my vm.overcommit_memory set to 0 (default).
Now the process gets killed soon after it starts.

Is there any way to tune PG to execute such a query, or am I forced to
forgo the convenience of the "ON DELETE CASCADE" and manually delete
the records with a subselect?

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

  http://archives.postgresql.org

Re: [GENERAL] Storing images in PostgreSQL databases (again)

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/05/06 18:54, Jean-Christophe Roux wrote:
> Why would I set a bytea column (containing picures) as a primary
> key? Because I want to be sure that the same image is inserted
> only once (that requirement comes from a real project) and using
> a primary key for that purpose makes sense to me.

A hash function makes better sense.  SHA-1 creates a 20 byte string
that is the digest of the file.  Almost perfect as a primary key.

SHA-256 (so far!) guarantees uniqueness in 32 bytes.

>Am I going to
> retrieve an image row by its image data? I would certainly like!
> For instance, I would like to get the pictures whose main color
> is green (requirement from a real project), and a select * from
> images where main_color(image) = 'green' would be nice.

That would speedily be handled by preprocessing metadata records as
the image is inserted.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFJql5S9HxQb37XmcRAgYjAKCeTur3N/AUwQLI79GYbde8nCMYwACfejg+
Xe3qGl7GHlhC4jssvGkAJ6w=
=dW03
-END PGP SIGNATURE-

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

Re: [GENERAL] DELETE eats up all memory and crashes box

[Tom Lane]

"Worky Workerson" <[EMAIL PROTECTED]> writes:
> On 10/6/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Well, the memory eating is easy to explain: pending-trigger-event list.

> Is there any way to tune PG to execute such a query, or am I forced to
> forgo the convenience of the "ON DELETE CASCADE" and manually delete
> the records with a subselect?

You'd have to tweak the query to not delete so many records at once.
Note that whether you have CASCADE or not is not the issue --- if you
are doing a delete in a foreign-key-referenced relation at all, you
are going to have a trigger event per deleted row no matter what the
details of the FK are.

We've had a TODO item for awhile to spill the pending-trigger-event list
to disk when it gets too big, but no one's gotten around to it, probably
because once you're in that regime performance is going to suck anyway :-(

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

[GENERAL] how to get number of minutes?

[Chris Hoover]

If I subtract 2 timestamps, how do I get the results returned as the total number of minutes.examplenow() - (now()-'2 hours'::interval) = 2:00:00  -- I need the result returned as 120 minutes.Thanks,
Chris

Re: [GENERAL] Storing images in PostgreSQL databases (again)

[Merlin Moncure]

On 10/5/06, Jean-Christophe Roux <[EMAIL PROTECTED]> wrote:


Why would I set a bytea column (containing picures) as a primary key?
Because I want to be sure that the same image is inserted only once (that
requirement comes from a real project) and using a primary key for that
purpose makes sense to me.
Am I going to retrieve an image row by its image data? I would certainly
like! For instance, I would like to get the pictures whose main color is
green (requirement from a real project), and a
select * from images where main_color(image) = 'green' would be nice.
JCR


standard postgresql indexes use a btree which would be completely
impractical to index bitmaps.  what you would want is a hand rigged
unqiue constraint which is combination of insert trigger and hash
algorithm plus some code to resolve collisions.  this is what i would
do if bitmaps were stored in/out of the database.

merlin

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

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

Re: [GENERAL] URGENT - startup process (PID 29541) was terminated by signal 6

[Tom Lane]

andy rost <[EMAIL PROTECTED]> writes:
> Our Opteron DB server had a problem with its RAID controller requiring 
> an immediate shutdown of our Postgres server (8.1.3 on FreeBSD 6.0 
> release number 10). We used kill -QUIT on the postmaster PID.

> 2006-10-06 12:32:40 CDT PANIC:  heap_clean_redo: no block
> ...
> 2006-10-06 12:08:48 CDT PANIC:  right sibling is not next child in 
> "winddir_idxu"

Um, were you running with full_page_writes off?  Bad idea in 8.1 :-( ...
especially on hardware that turns out to not be 100% reliable.

I think your only hope of restarting the database is to use
pg_resetxlog.  This is likely to leave you with some database
corruption, in the form of partially applied recent transactions.
I'd recommend a dump and reload, or at least REINDEXing all the
indexes, to forestall problems from that.  While you are at it,
update to 8.1.4.

regards, tom lane

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

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

Re: [GENERAL] DELETE eats up all memory and crashes box

[Worky Workerson]

Note that whether you have CASCADE or not is not the issue --- if you
are doing a delete in a foreign-key-referenced relation at all, you
are going to have a trigger event per deleted row no matter what the
details of the FK are.


So the best/fastest* way to do this would be to remove the FK
relationship from the tables, delete all my rows with DELETE ... WHERE
ip IN (SELECT ...) in the previously FK-ed tables,  delete all the
rows in the PK table, then recreate the FK relationships?  I tried
this and it was pretty snappy, assuming that all the indexes are
built.

*note: loading/creating a datawarehouse, guaranteed exclusive access.
Current DW size is about 10 GB.

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

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

Re: [GENERAL] Storing images in PostgreSQL databases (again)

[Jacob Coby]

you could store the pkey as a md5 or sha1
of the image's data.  or any of the other large hashing algorithms. 
that way your index only has to compare 32 or 40 bytes instead of kilobytes per
row.  

 

as for the main color, you could generate histogram-like
columns (or even a single column) with the relative strengths of each channel
and store them into a smallint or bitstring.  you could then do whatever you
wanted per channel and it could be indexed.

 









From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jean-Christophe Roux
Sent: Thursday, October 05, 2006
7:55 PM
To: Alexander Staubo
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Storing
images in PostgreSQL databases (again)



 





Why would I set a bytea
column (containing picures) as a primary key? Because I want to be sure that
the same image is inserted only once (that requirement comes from a real
project) and using a primary key for that purpose makes sense to me. 
Am I going to retrieve an image row by its image data? I would certainly like!
For instance, I would like to get the pictures whose main color is green
(requirement from a real project), and a 
select * from images where main_color(image) = 'green' would be nice.
JCR





- Original Message

From: Alexander Staubo <[EMAIL PROTECTED]>
To: Jean-Christophe Roux <[EMAIL PROTECTED]>
Cc: pgsql-general@postgresql.org
Sent: Thursday, October 5, 2006 7:35:04 PM
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)



On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote:

> By the way, is it practical to set a bytea column (containing  
> pictures) as primary key? That would severely slow down many  
> operations I guess.

Why would you? It's possible, but completely impractical, since
image  
data typically exceeds the index page size. Moreover, are you
really  
going to retrieve an image row by its image data?

Alexander.

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

[km]

Hi all, 
Iam using postgresql 8.1.4 with 8GB physical RAM.

OS kernel max shared memory usage is set to( /proc/sys/kernel/shmmax) 33554432
dont know if this number is in bytes or bits
now how do i set my shared_buffer setting in postgresql.conf such that ican use 
max shared memory setting of the kernel.

also default shared_buffer setting is 1000 - its not clear if i have to 
multiply this number with 8KB to be <= max shared memory value or it is 1000 
bytes or bits. 
can i set the max shared memory value to use atleast half of my physicl RAM 
available ?
any idea ?
also i would like to know how is the performance hit when we change 
shared_buffer value in general ?

regards,
KM

---(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] Alter table alter column

[Marc Munro]

Am I right in thinking that altering a column from varchar(n) to
varchar(n+m) requires each tuple to be visited?

Recent experience suggests this is the case but my reading of the docs
has left me uncertain why this should be so.  We are not changing the
fundamental type of the column, nor are we attempting an operation that
will fail due to existing data being incompatible with the new
definition.

Is there some fundamental reason why placing a higher limit on the
length of existing data cannot be done solely by changing the system
catalogs?  Is this an optimisation that could be added to the TODO list?

__
Marc


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

Re: [GENERAL] how to get number of minutes?

[Casey Duncan]

select extract(epoch from interval '2 hours')/60;

'epoch' returns the number epoch seconds that comprise the interval.  
That differs from 'seconds' which just returns the "seconds place",  
which is zero for 2:00:00 of course.


-Casey

On Oct 6, 2006, at 12:22 PM, Chris Hoover wrote:

If I subtract 2 timestamps, how do I get the results returned as  
the total number of minutes.


example

now() - (now()-'2 hours'::interval) = 2:00:00  -- I need the result  
returned as 120 minutes.


Thanks,

Chris



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

Re: [GENERAL] Alter table alter column

[Tom Lane]

Marc Munro <[EMAIL PROTECTED]> writes:
> Am I right in thinking that altering a column from varchar(n) to
> varchar(n+m) requires each tuple to be visited?

Yes.  Doing otherwise would require an unreasonable amount of
data-type-specific knowledge hardwired into ALTER COLUMN TYPE.

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] Storing images in PostgreSQL databases (again)

[Gregory S. Williamson]

Hate to suggest corporate software, but there is an Informix/Illustra blade 
that could do something like what you're after (I remember a demo of 
sunset/sunrise photos being selected on the basis of color values) ...

But I think they used smart blobs and didn't use them as key values.

G

-Original Message-
From:   [EMAIL PROTECTED] on behalf of Jean-Christophe Roux
Sent:   Thu 10/5/2006 4:54 PM
To: Alexander Staubo
Cc: pgsql-general@postgresql.org
Subject:Re: [GENERAL] Storing images in PostgreSQL databases (again)

Why would I set a bytea column (containing picures) as a primary key? Because I 
want to be sure that the same image is inserted only once (that requirement 
comes from a real project) and using a primary key for that purpose makes sense 
to me. 
Am I going to retrieve an image row by its image data? I would certainly like! 
For instance, I would like to get the pictures whose main color is green 
(requirement from a real project), and a 
select * from images where main_color(image) = 'green' would be nice.
JCR


- Original Message 
From: Alexander Staubo <[EMAIL PROTECTED]>
To: Jean-Christophe Roux <[EMAIL PROTECTED]>
Cc: pgsql-general@postgresql.org
Sent: Thursday, October 5, 2006 7:35:04 PM
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)

On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote:

> By the way, is it practical to set a bytea column (containing  
> pictures) as primary key? That would severely slow down many  
> operations I guess.

Why would you? It's possible, but completely impractical, since image  
data typically exceeds the index page size. Moreover, are you really  
going to retrieve an image row by its image data?

Alexander.

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









---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4526a19c122019835456387&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4526a19c122019835456387!
---




---(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] exploiting features of pg to obtain polymorphism maintaining ref. integrity

[Ivan Sergio Borgonovo]

Is there any good documentation, example, tutorial, pamphlet, discussion... to 
exploit pg features to obtain "polymorphic" behavior without renouncing to 
referential integrity?

Inheritance seems *just* promising.

Any methodical a approach to the problem in pg context?

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


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

[Hugo]

Hi again, thanks for your guidance, this is the error I got trying to save my fuction:ERROR:  unrecognized exception condition "no_data"CONTEXT:  compile of PL/pgSQL function "fn_verificar_aportes_socio" near line 36
the symbol is correct , i checked it in the appendix A postgres error codes and constants, have you got any idea what the problem could be.loop  Begin 
       fetch bla.   Exception  when no_data then        exit; end;  end loop;  
..this is on suse 10, postgres 8.1.4thanks in advanceHugo

Re: [GENERAL] how to check SQLSTATE

[Tom Lane]

Hugo <[EMAIL PROTECTED]> writes:
> Hi again, thanks for your guidance, this is the error I got trying to save
> my fuction:
> ERROR:  unrecognized exception condition "no_data"
> CONTEXT:  compile of PL/pgSQL function "fn_verificar_aportes_socio" near
> line 36

NO_DATA isn't an error condition, only a warning, thus there is no case
in which an exception block would trap it.  So plpgsql doesn't bother to
recognize it.

regards, tom lane

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

Re: [GENERAL] exploiting features of pg to obtain polymorphism maintaining ref. integrity

[Richard Broersma Jr]

> Is there any good documentation, example, tutorial, pamphlet, discussion... 
> to exploit pg
> features to obtain "polymorphic" behavior without renouncing to referential 
> integrity?
> 
> Inheritance seems *just* promising.
> 
> Any methodical a approach to the problem in pg context?

I don't know if this is what you are after, but is was a VERY interesting 
discussion that sounds
similar to what your are looking for?

http://archives.postgresql.org/pgsql-sql/2006-05/msg00179.php
http://archives.postgresql.org/pgsql-general/2006-05/msg01125.php

basically the OP, wanted to create a template/parent schema.

next he would create a new schema for each client and then create inherited 
tables from the parent
schema.

Then he would create views/fuctions that would work on the tables in the 
current schema.

Last he would set the search_path to the schema of any client of enterest and 
use the default
views/funtions of the newly set search_path of currently set schema.


One advantage (possible the polymorphism you were looking for) with this design 
was that any
modifications made to the template/parent schema would cascade to all of the 
client schemas as a
feature of table inheritance.  And he could also summarize all of the data from 
all clients by
selecting from the template/parent schema.

Regards,

Richard Broersma Jr.

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

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

Re: [GENERAL] shared_buffer setting

[Talha Khan]

Hi Km,
 
The shmmax setting is in bits.
 
For knowing the details about the shared_buffer settings and other configuration features follow the link given below:
 
http://www.powerpostgresql.com/PerfList
 
The shmmax value set in your kernel (i.e 33554432) seems quite low seeng that you have 8GB physical RAM. Yes! you can set shmmax to half of your RAM but it all depends upon how you use your system .I would recommend setting it to 2/3 of your 
RAM.You can set the value in sysctl.conf file. and then reflect the setting by using sysctl -p switch.
 
Regards
Talha Khan 
On 10/6/06, km <[EMAIL PROTECTED]> wrote:
Hi all,Iam using postgresql 8.1.4 with 8GB physical RAM.OS kernel max shared memory usage is set to( /proc/sys/kernel/shmmax) 33554432
dont know if this number is in bytes or bitsnow how do i set my shared_buffer setting in postgresql.conf such that ican use max shared memory setting of the kernel.also default shared_buffer setting is 1000 - its not clear if i have to multiply this number with 8KB to be <= max shared memory value or it is 1000 bytes or bits.
can i set the max shared memory value to use atleast half of my physicl RAM available ?any idea ?also i would like to know how is the performance hit when we change shared_buffer value in general ?regards,
KM---(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] shared_buffer setting

[Talha Khan]

Hi Km,
 
The shmmax setting is in *BYTES*.
 
Regards
Talha Khan
On 10/6/06, Talha Khan <[EMAIL PROTECTED]> wrote:

Hi Km,
 
The shmmax setting is in bits.
 
For knowing the details about the shared_buffer settings and other configuration features follow the link given below:
 
http://www.powerpostgresql.com/PerfList
 
The shmmax value set in your kernel (i.e 33554432) seems quite low seeng that you have 8GB physical RAM. Yes! you can set shmmax to half of your RAM but it all depends upon how you use your system .I would recommend setting it to 2/3 of your 
RAM.You can set the value in sysctl.conf file. and then reflect the setting by using sysctl -p switch.
 
Regards
Talha Khan 

On 10/6/06, km <[EMAIL PROTECTED]> wrote:
 
Hi all,Iam using postgresql 8.1.4 with 8GB physical RAM.OS kernel max shared memory usage is set to( /proc/sys/kernel/shmmax) 33554432 
dont know if this number is in bytes or bitsnow how do i set my shared_buffer setting in postgresql.conf such that ican use max shared memory setting of the kernel.also default shared_buffer setting is 1000 - its not clear if i have to multiply this number with 8KB to be <= max shared memory value or it is 1000 bytes or bits. 
can i set the max shared memory value to use atleast half of my physicl RAM available ?any idea ?also i would like to know how is the performance hit when we change shared_buffer value in general ?regards, 
KM---(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