Re: [GENERAL] not valid character for Unicode

2006-06-10 Thread Adam Witney


Martijn van Oosterhout wrote:
 On Fri, Jun 09, 2006 at 04:32:35PM +0100, Adam Witney wrote:
 The database will do it for you. Note that the client encoding affects
 input *and* output. So if you set it to latin1, the database will
 convert all strings to latin1 before sending them to you...
 ok, so my current database (7.4.12) is UNICODE, but from psql when i run
 this
 
 snip
 
 SELECT identifier from dba_data_base where bioassay_id = 1291 and
 identifier ilike '[EMAIL PROTECTED]';
   identifier
 --
  [EMAIL PROTECTED] (0A11)

 so the mu chatacter is not showing up. So im not sure if the database is
 converting the output?
 
 Is the character actually there? Do a length(identifier) on it to see
 how many characters there are. When doing an interactive session it's
 important that the client_encoding matches your display, otherwise you
 might find it dropping characters or messing up in other ways.

yep it is there, when i display the data from the application (PHP) it
shows the character on the web page. Also this causes errors when i dump
from 7.4 and try to load into 8.1 (i've read that the UNICODE checking
became more stringent in 8)

so basically 8.1 won't accept this character... im just not entirely
sure what to do about that?

thanks again for your help

adam

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(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] not valid character for Unicode

2006-06-10 Thread Jorge Godoy
Em Sábado 10 Junho 2006 05:31, Adam Witney escreveu:
 yep it is there, when i display the data from the application (PHP) it
 shows the character on the web page. Also this causes errors when i dump
 from 7.4 and try to load into 8.1 (i've read that the UNICODE checking
 became more stringent in 8)

 so basically 8.1 won't accept this character... im just not entirely
 sure what to do about that?

Are you on a Unix/Linux machine?  You can dump the file there and run file 
dump.sql to see what type of file it reports.  If it says something other 
than a string containing text and utf-8, then you can edit the dump 
manually and set the client encoding to whatever it is reported and try 
restoring it or you can run iconv on the file and see if the conversion to 
utf-8 works. 

-- 
Jorge Godoy  [EMAIL PROTECTED]


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

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


Re: [GENERAL] php 5.1.4 with PostgreSQL 8.1.4 causes Apache 1.3.36

2006-06-10 Thread [EMAIL PROTECTED]

[EMAIL PROTECTED] wrote:

I wrote about this, but I lost the thread.

Today I did clean install in a new virtual machine of FreeBSD 5.4, 
Apache 1.3.36, php 5.1.4 and PostgreSQL 8.1.4 from ports - and apache 
did core dump again.


Commenting pgsql.so in the php extensions solves the problem, but I need 
it.


Is there anybody who has the above working?

php 5.1.2 with PostgreSQL 8.0.8 works fine.

Iv.


SOLVED

Somebody from the list came in to debug with us. He examined httpd.core 
and found out that the problem is around 'get host by name'. Then he 
added a line in 'hosts' and gave the host name in 'httpd.conf' and all 
worked fine.


Interesting that the server worked without this for quite a while.

Iv.

--


---(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] TOAST not working

2006-06-10 Thread list_man

Hi,

I wonder if anyone can help.

I have a VERY wide table and rows. There are over 800 columns of type:
numeric(11,2)

I can create the table no problem, but when I go to fill out a full row
with data, I get the message about reaching the 8k limit.

Can someone tell me if I have to 'enable' TOAST on columns to have  it
kick in. According to my research, numeric data types are toastable.

I'd appreciate if anyone can give me lit1e.

Thanks

PS. I'm running Postgres v8.0x


---(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] TOAST not working

2006-06-10 Thread Tom Lane
list_man [EMAIL PROTECTED] writes:
 I have a VERY wide table and rows. There are over 800 columns of type:
 numeric(11,2)

Perhaps you should reconsider your data design.  Maybe some of those
columns would more logically form an array?

regards, tom lane

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


Re: [GENERAL] TOAST not working

2006-06-10 Thread list_man
Thanks for the suggestion Tom... I'm looking into it.

To continue however and educate me on datatypes  TOAST, should a row of
10k+, in this case consisting of the datatype listed below, store OK,
when TOAST kicks in?

thanks... Angus

On Sat, 2006-06-10 at 12:28 -0400, Tom Lane wrote:
 list_man [EMAIL PROTECTED] writes:
  I have a VERY wide table and rows. There are over 800 columns of type:
  numeric(11,2)
 
 Perhaps you should reconsider your data design.  Maybe some of those
 columns would more logically form an array?
 
   regards, tom lane
 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend
 


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


Re: [GENERAL] pgxml xpath_table

2006-06-10 Thread Tom Lane
John Gray [EMAIL PROTECTED] writes:
 This is the reason why xpath_table allows you to specify an
 identifying field  (usually a primary key but doesn't have to be)- the
 solution to your question is to join an xpath_table that just fetches the
 document number against the primary key, e.g.:

John, do you think anything could be done in the pgxml documentation to
make this usage pattern clearer?

regards, tom lane

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


[GENERAL] Error Starting Back END

2006-06-10 Thread Rojas, Fernando \(CIAT\)
HELLO:

I have now an error similar to one posted under the subject:

We had format the DISK and reinstall the SO (Red Hat Linux 8.0 3.2-7); we don't 
have a pg_dumpall file of support; but we have 
The copy of the data file that was running ok under (PG-8.0.7).
We reinstall P.G 8.0.7 and try to start post-master with the database cluster 
but have this error in the log:

FATAL:  invalid value for parameter lc_messages: en_US.UTF-8
FATAL:  invalid value for parameter lc_messages: en_US.UTF-8
FATAL:  invalid value for parameter lc_messages: en_US.UTF-8

When I change the postgresql.conf file changing en_US.UTF-8  by C; the error 
dissapears but show me this another:

FATAL:  database files are incompatible with operating system
DETAIL:  The database cluster was initialized with LC_COLLATE en_US.UTF-8, 
which is not recognized by setlo
cale().
HINT:  It looks like you need to initdb or install locale support

When I run:

Locale obtain this:
[EMAIL PROTECTED] data]# locale
LANG=en_US.UTF-8
LC_CTYPE=en_US.UTF-8
LC_NUMERIC=en_US.UTF-8
LC_TIME=en_US.UTF-8
LC_COLLATE=en_US.UTF-8
LC_MONETARY=en_US.UTF-8
LC_MESSAGES=en_US.UTF-8
LC_PAPER=en_US.UTF-8
LC_NAME=en_US.UTF-8
LC_ADDRESS=en_US.UTF-8
LC_TELEPHONE=en_US.UTF-8
LC_MEASUREMENT=en_US.UTF-8
LC_IDENTIFICATION=en_US.UTF-8
LC_ALL=

But when I locale - a the result is:

[EMAIL PROTECTED] data]# locale -a
C
POSIX
en_AU
en_AU.utf8
en_BW
en_BW.utf8
en_CA
en_CA.utf8
en_DK
en_DK.utf8
en_GB
en_GB.iso885915
en_GB.utf8
en_HK
en_HK.utf8
en_IE
en_IE.utf8
[EMAIL PROTECTED]
[EMAIL PROTECTED]
en_IN
en_NZ
en_NZ.utf8
en_PH
en_PH.utf8
en_SG
en_SG.utf8
en_US
en_US.iso885915
en_US.utf8
en_ZA
en_ZA.utf8
en_ZW
en_ZW.utf8


I don't see en_US.UTF-8 there   why..??

I have tried doing initdb to an empty cluster  and copying the data folder  but 
the erros is the same.

Could you help me to resolve this..


Thank you very much for your colaboration.


FERNANDO ROJAS
Ingeniero de Sistemas y Computación
Unidad de Sistemas de Información
Centro Internacional de Agricultura Tropical (CIAT)
E-mail: [EMAIL PROTECTED]
Phone:(57-2)445-01-00 Ext 3647 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dirk Lutzebäck
Sent: Friday, June 09, 2006 8:26 AM
To: pgsql-admin@postgresql.org; pgsql-general@postgresql.org; 
pgsql-bugs@postgresql.org
Cc: [EMAIL PROTECTED]
Subject: [ADMIN] How to set the global OID counter? COPY WITH OIDS does not set 
global OID counter?

Hi,

how can one set the global OID counter in 8.1.X? We think it would work in 
8.0.X using the COPY WITH OIDS command but this does not work in 8.1.X anymore.

We have the problem that we made a dump using 'pg_dump -o' in 8.0.X, created a 
new database in 8.1.X and read back in but the global OID counter stayed at 
40.000 so OIDs will be allocated again!

Thanks for help,

Dirk

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

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


Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully

2006-06-10 Thread dananrg
Lots of great conversation here. Thanks to all for participating.

David, you wrote:
Be aware that Pascal, along with Date and Darwen, are...how do I put
this gently...cranks.  They've been getting more strident and
irrational as the decades go by.

I can't speak to that statement directly. Indirectly, however, the
sources Pascal cites in the body of the text (if I had to guess, and I
do because I'm too lazy to count them all) seem to be ~90% works by
C.J. Date. So it seems chiefly to be a distillation of Date's ideas,
e.g. potentially a cloistered treatise.

Is Pascal an academic who doesn't have real world knowledge gained from
having logically and physically designed, then brought to production,
monitored, refined, and tuned databases, and has answered to end users,
for a wide variety of customers and projects?

His book's subtitle is: A Reference for the Thinking Practitioner.
If he is not himself a practitioner out there in the trenches, doing
the best with whatever technology presently exists, then I'm slightly
less interested in what he has to say, although I appreciate what he's
trying to do. But I don't want to dwell on the OUGHT in terms of future
technology; I want to dwell on the IS. Pleas that we ought to demand
better technology are all to the good -

Is there a good book out there about DB design written *by* real-world
practitioners, *for* real-world practitioners that addresses Pascal and
Date's concerns, yet focuses on the technology we have to live with
today? Something vendor-neutral if possible.
 
Dana


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


Re: [GENERAL] Insert into partition table hangs

2006-06-10 Thread Nikola Ivanov
Database is running on Windows 2003 Advanced Server and it is PostgreSQL 8.1.3.The Java application is running from a remote machine running Windows XP Professional SP2.On 6/9/06, 
Tom Lane [EMAIL PROTECTED] wrote:
Qingqing Zhou [EMAIL PROTECTED] writes: A wild guess is that when we continue to sleep on the semaphore, magically we get it, but how come?
Buggy SysV semaphore support in the kernel, maybe?What platform isthis exactly?regards, tom lane


Re: [GENERAL] Problem with 'postgres' db with 8.0 on a MacBook

2006-06-10 Thread Jonathon McKitrick

Thanks for the help, guys.  I've learned from this the importance of keeping a
journal of software installation.

Jonathon McKitrick
--
My other computer is your Windows box.

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


Re: [GENERAL] Best open source tool for database design / ERDs?

2006-06-10 Thread dananrg
Bjørn T Johansen wrote:
 Yes, that is annoying But you can remove the legend by right clicking on 
 the name of
 the ER and choosing Properties.. Under Legend, you can change Location to 
 None;
 that will remove it...

Thanks Bjorn. I'll give that a try. Hopefully that will be turned off
by default in the next release. It's a nice program. Using DIA +
add-ons to do the same thing seemed inelegant. Although I hope the DIA
project continues. Needs to be added to Open Office.

Dana


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


[GENERAL] TOAST not working

2006-06-10 Thread list_man
Hi,

I wonder if anyone can help.

I have a VERY wide table and rows. There are over 800 columns of type:
numeric(11,2)

I can create the table no problem, but when I go to fill out a full row
with data, I get the message about reaching the 8k limit.

Can someone tell me if I have to 'enable' TOAST on columns to have  it
kick in. According to my research, numeric data types are toastable.

I'd appreciate if anyone can give me lit1e.

Thanks

PS. I'm running Postgres v 8k


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


[GENERAL] Logging and Viewing SQL History

2006-06-10 Thread Adam

I tried searching for this but didn't see anything.

Is there a way I can see the last 5 or last 1 SQL statements that have been 
run on the server?  I'm using 8.1.3 



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


Re: [GENERAL] Logging and Viewing SQL History

2006-06-10 Thread A. Kretschmer
am  10.06.2006, um 14:09:36 -0500 mailte Adam folgendes:
 I tried searching for this but didn't see anything.
 
 Is there a way I can see the last 5 or last 1 SQL statements that have been 
 run on the server?  I'm using 8.1.3 ---(end of 

'log_statement = all'

in your postgresql.conf

Than can you see all of your SQL-Statements in the log.


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

---(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] Questions about indexes

2006-06-10 Thread Pat Maddox

I've got a table with that has 5 fields.  Nearly every query I make to
this table is of the form
SELECT * FROM table1 WHERE field1='foo' AND field2=7;

It's always those two exact fields.  How should I index this to get
the best performance?

Also, how can I find what other parts of my app would benefit from more indexes?

Thanks,
Pat

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

2006-06-10 Thread John Gray
On Sat, 2006-06-10 at 14:06 -0400, Tom Lane wrote:
 John Gray [EMAIL PROTECTED] writes:
  This is the reason why xpath_table allows you to specify an
  identifying field  (usually a primary key but doesn't have to be)- the
  solution to your question is to join an xpath_table that just fetches the
  document number against the primary key, e.g.:
 
 John, do you think anything could be done in the pgxml documentation to
 make this usage pattern clearer?
 

Yes - I'll write a doc patch for this and include an example like this
by way of demonstration - I notice now that this particular feature
(which I went to some lengths to incorporate in the code!) is completely
undocumented. I'll also consider whether another function with different
behaviour in this situation is possible or helpful.

Give me a few days...

Regards

John

   regards, tom lane


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


Re: [GENERAL] Questions about indexes

2006-06-10 Thread Alan Hodgson
On Saturday 10 June 2006 13:30, Pat Maddox [EMAIL PROTECTED] wrote:
 I've got a table with that has 5 fields.  Nearly every query I make to
 this table is of the form
 SELECT * FROM table1 WHERE field1='foo' AND field2=7;

 It's always those two exact fields.  How should I index this to get
 the best performance?

A single index on field1,field2 (or field2, field1) would obviously be 
ideal.


 Also, how can I find what other parts of my app would benefit from more
 indexes?

Log the queries that are being run, extract the ones that are run the most, 
and examine them and their underlying data in detail and with explain 
analyze.

If you can think of a way to force programmers to do this while building 
their apps, that would also be useful ...

-- 
In a truly free society, Alcohol, Tobacco and Firearms would be a
convenience store chain.


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

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


Re: [GENERAL] TOAST not working

2006-06-10 Thread Christopher Browne
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (list_man) would 
write:
 Can someone tell me if I have to 'enable' TOAST on columns to have  it
 kick in. According to my research, numeric data types are toastable.

TOAST is only used on individual columns that exceed 8K in size.

The only way you'll be TOASTing numeric columns is if they are
individually defined to occupy  8192 bytes.

Those are *BIG* numeric values; perhaps you're misunderstanding the
intent of TOAST...
-- 
let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];;
http://linuxdatabases.info/info/slony.html
What do little birdies see when they get knocked unconscious?

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

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


Re: [GENERAL] TOAST not working

2006-06-10 Thread Christopher Browne
After a long battle with technology, [EMAIL PROTECTED] (list_man), an 
earthling, wrote:
 Hi,

 I wonder if anyone can help.

 I have a VERY wide table and rows. There are over 800 columns of type:
 numeric(11,2)

 I can create the table no problem, but when I go to fill out a full row
 with data, I get the message about reaching the 8k limit.

 Can someone tell me if I have to 'enable' TOAST on columns to have  it
 kick in. According to my research, numeric data types are toastable.

No, TOAST will be entirely irrelevant in this case.  TOAST allows
large columns to be moved out of the table proper.  

Thus, if you had a column containing a MIME-encoded document (as is
the case when attachments are put into tickets in the RT3 ticketing
system), the Very Large Column would get moved to the associated
TOAST table.

But this will be of little assistance when no individual column is
large.

 I'd appreciate if anyone can give me lit1e.

Perhaps your schema is poorly designed, and you should consider
redesigning it.

If someone brought to me a schema consisting of 800 columns, I'd be
inclined to strike them on the head with the Elisp reference manual in
the hopes that even if it brought no enlightenment, the concussion
would discourage them from bringing it back...
-- 
let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];;
http://linuxdatabases.info/info/nonrdbms.html
If you have a procedure with ten parameters, you probably missed some.
-- Alan J. Perlis

---(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] are there static variables in pgsql?

2006-06-10 Thread Yavuz Kavus
i am writing a recursive procedure in pl/pgsql.i need to check whether a condition is true in any step of recursive calls.if i get a true response in one call, i wont make other recursive calls anymore, because i get what i need.
if no one gives me a true response, then uppest procedure will return false as a result.i think i may achieve this with a static variable(shared among all calls).is it possible?or another solution?
thanks a lot for any help.


Re: [GENERAL] are there static variables in pgsql?

2006-06-10 Thread Terry Lee Tucker
On Saturday 10 June 2006 06:31 pm, Yavuz Kavus [EMAIL PROTECTED] thus 
communicated:
-- i am writing a recursive procedure in pl/pgsql.
-- i need to check whether a condition is true in any step of recursive
 calls. --
-- if i get a true response in one call, i wont make other recursive calls
-- anymore, because i get what i need.
-- if no one gives me a true response, then uppest procedure will return
 false -- as a result.
--
-- i think i may achieve this with a static variable(shared among all
 calls). --
-- is it possible?
--
-- or another solution?
--
-- thanks a lot for any help.
--
I don't know of any static variable capability; however, I use various utility 
tables to accomplish this type of task. You can insert records into a table 
like this while inside a transaction and the current process will be the only 
process that can see it. When you are through with it, delete the record, all 
within the same transaction, and your purpose will be served.

HTH...

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

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


Re: [GENERAL] TOAST not working

2006-06-10 Thread Jim C. Nasby
On Sat, Jun 10, 2006 at 05:10:06PM -0400, Christopher Browne wrote:
 Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (list_man) would 
 write:
  Can someone tell me if I have to 'enable' TOAST on columns to have  it
  kick in. According to my research, numeric data types are toastable.
 
 TOAST is only used on individual columns that exceed 8K in size.
 
 The only way you'll be TOASTing numeric columns is if they are
 individually defined to occupy  8192 bytes.

Actually, it's BLCKSZ/4. From
http://www.postgresql.org/docs/8.1/interactive/storage-toast.html:

The TOAST code is triggered only when a row value to be stored in a
table is wider than BLCKSZ/4 bytes (normally 2Kb).

BTW, 'row value' seems a bit prone to confusion (could be interpreted as
the row itself). It'd probably be better to say 'field'. Barring
objections, I'll submit a patch.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(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] are there static variables in pgsql?

2006-06-10 Thread Jim C. Nasby
On Sat, Jun 10, 2006 at 06:37:35PM -0400, Terry Lee Tucker wrote:
 On Saturday 10 June 2006 06:31 pm, Yavuz Kavus [EMAIL PROTECTED] thus 
 communicated:
 -- i am writing a recursive procedure in pl/pgsql.
 -- i need to check whether a condition is true in any step of recursive
  calls. --
 -- if i get a true response in one call, i wont make other recursive calls
 -- anymore, because i get what i need.
 -- if no one gives me a true response, then uppest procedure will return
  false -- as a result.
 --
 -- i think i may achieve this with a static variable(shared among all
  calls). --
 -- is it possible?
 --
 -- or another solution?
 --
 -- thanks a lot for any help.
 --
 I don't know of any static variable capability; however, I use various 
 utility 
 tables to accomplish this type of task. You can insert records into a table 
 like this while inside a transaction and the current process will be the only 
 process that can see it. When you are through with it, delete the record, all 
 within the same transaction, and your purpose will be served.

If you use a temp table for that, you can have it truncate on
commit/rollback.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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


Re: [GENERAL] TOAST not working

2006-06-10 Thread Tom Lane
Jim C. Nasby [EMAIL PROTECTED] writes:
 Actually, it's BLCKSZ/4. From
 http://www.postgresql.org/docs/8.1/interactive/storage-toast.html:

 The TOAST code is triggered only when a row value to be stored in a
 table is wider than BLCKSZ/4 bytes (normally 2Kb).

 BTW, 'row value' seems a bit prone to confusion (could be interpreted as
 the row itself). It'd probably be better to say 'field'.

No, because that would be wrong; the statement is correct as written.
The toaster tries to do something about rows that are wider than
BLCKSZ/4 (well, actually MaxTupleSize/4 ... try grepping the source
for TOAST_TUPLE_THRESHOLD).

The OP's problem is that he's got too dang many fields.  A TOAST pointer
is 20 bytes wide (on most machines, at least) so even if we toast every
single field out-of-line, we can't support more than about 400 toastable
fields in a row.  The FAQ says

   Maximum number of columns in a table? 250-1600 depending on column
   types

but the 1600 figure is for datatypes like int4 that only take 4 bytes
anyway.

The OP was trying to store numeric(11,2) fields.  If I'm counting on my
fingers correctly, such a value would occupy 16 bytes natively, which
means that pushing it out-of-line would be a dead loss anyway.  But he's
still not going to get more than 512 of them into an 8K page.

regards, tom lane

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


Re: [GENERAL] Error Starting Back END

2006-06-10 Thread Tom Lane
Rojas, Fernando \(CIAT\) [EMAIL PROTECTED] writes:
 But when I locale - a the result is:
 ...
 en_US.utf8
 ...

You probably need to spell it exactly like that, not en_US.UTF-8.
No, I don't have any idea why it worked before, if you're using the
exact same OS release ... maybe you're missing a locale package that
provided the other spelling?

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] Problems with database bloat

2006-06-10 Thread Alex Turner
I suspect that my manualy vacuum every 10 days or so really wasn't nearly enough ;)AlexOn 6/9/06, Jim C. Nasby 
[EMAIL PROTECTED] wrote:This tells me that you need to be vacuuming more. Autovac is your
friend.On Thu, Jun 08, 2006 at 07:14:01PM -0400, Alex Turner wrote: Yeah - I just did a reindex, that fixed the indexes at least. Alex On 6/8/06, Jim C. Nasby 
[EMAIL PROTECTED] wrote:  On Thu, Jun 08, 2006 at 06:03:23PM -0400, Alex Turner wrote:  I hope I'm reading this query wrong:   trend=# select relname, relpages*8192/reltuples from pg_class where
  reltuples0 order by relpages desc limit 10; relname| ?column?  ---+--  property| 
19935.4468376195  result_entry_pkey | 1611.15654062026  result_entry| 1417.71707157196  person| 7107.41684585612  property_feature_pkey | 
98.7810833557521  property_feature| 60.2035684051268  person_name_i | 3358.93641334398  property_price_i| 1978.89907374882  property_mls_listing_number_i | 
1923.61833274788  property_spatial| 1784.73493686332  (10 rows)   If I have this query right in my head, this means that the average tuple in
  the property relation is taking up 19k? and the average tuple in the  result_entry_pkey index is take 1.5k?!   Is there a way I can get the database to coalesce free space?A vacuum
  verbose shows that I have enough entries in the free space map...  A lazy vacuum won't reclaim empty space, only a VACUUM FULL will.  If that drops the size of the relations substantially, you'll probably
 want to REINDEX everything to reclaim lost space in the indexes as well. -- Jim C. Nasby, Sr. Engineering Consultant[EMAIL PROTECTED]
 Pervasive Softwarehttp://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
 --Jim C. Nasby, Sr. Engineering Consultant[EMAIL PROTECTED]Pervasive Softwarehttp://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461