date:20060315


Christopher Condit wrote:

Thanks for your response, Josh.  Actually I'm looking for the most
general way to do this, since my remote database might not be psql.  In
fact, I will probably be streaming through a java process.  So I'd like
to go from the java process directly into the psql db.  Is it still
possible?


I think recent JDBC drivers allow COPY, but you'll need to check the 
documentation.


The other thing to do is to batch your inserts into groups of (say) 
1000. That will provide a real speed increase.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] out of memory using Postgres with Spring/Hibernate/Java


maarten roosendaal wrote:

Hi,

We are currently having a problem that our Postgres DB
is throwing an SQL error which states that it's 'out
of memory'. 


What we have is a DB with 1 table that has 3.9 million
records. We need to find certain records that are to
be processed by a Java App so we do a "select id from
table where type=a and condition in (1, 2) order by id
limit 2000". When this query gets executed we see the
memory on the DB Server increasing and after it has
finishes it drops a bit but we see it growing a few MB
per few minutes.


So it's postgresql's memory usage that is increasing?
From what to what?
How many backends are we talking about, and does this happen to all of them?
How much memory does the machine have and what else is using it?
Oh, and what version of PG, JDBC, Spring, Hibernate etc?

> This has caused an out of memory

after the system has been processing for a day or 2.


Each new connection will start a new backend, so presumably this 
connection is continuously active for days.



The query is heavy because of the order by but that
does not explain why the memory is increasing.

We use a DAO which extends HibernateDaoSupport and the
method (findIds) has been marked as
propagation_required. So we assume Spring manages the
transaction and thus closing of the resultset.


First step has to be to turn query-logging on for the problem 
application. It might be that there is a memory leak in PostgreSQL, but 
it might be that something isn't being released properly by the 
applicaton libraries.


You can turn statement logging on or off in the postgresql.conf file, or 
by issuing "set log_statement=XXX" as a query after connecting. That way 
we can see exactly what is happening.


--
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] Inserting é in psql - inv


CSN wrote:

I created a new database with encoding UTF8, connected
using psql, and ensured the client encoding is also
UTF8 (Unicode). But when I try to insert characters
like 'é', I get this error:

ERROR:  invalid byte sequence for encoding "UNICODE":
0xe9


Something isn't UTF-8, possibly your terminal settings?

If you have a text-editor that lets you set character-encoding then you 
can try feeding the query into psql from a file. If that works then it's 
your terminal.


I always have endless trouble with this sort of stuff myself, so you're 
not alone.

--
  Richard Huxton
  Archonet Ltd


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

  http://archives.postgresql.org

[GENERAL] full text indexing

2006-03-15 Thread chris smith

Hi all,

Just wondering which full text module is better & what the differences
are between tsearch and fti ?

The table in question has roughly 80,000 rows.

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

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

Re: [GENERAL] full text indexing


chris smith wrote:

Hi all,

Just wondering which full text module is better & what the differences
are between tsearch and fti ?

The table in question has roughly 80,000 rows.


I've been very happy with tsearch2. Note that if you're running an old 
version of PostgreSQL (7.4?) there are some manual steps you'll need to 
take to dump + restore.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org

Re: [GENERAL] out of memory using Postgres with

2006-03-15 Thread Simon Riggs

On Tue, 2006-03-14 at 22:06 -0800, maarten roosendaal wrote:

> We are currently having a problem that our Postgres DB
> is throwing an SQL error which states that it's 'out
> of memory'. 
> 
> What we have is a DB with 1 table that has 3.9 million
> records. We need to find certain records that are to
> be processed by a Java App so we do a "select id from
> table where type=a and condition in (1, 2) order by id
> limit 2000". When this query gets executed we see the
> memory on the DB Server increasing and after it has
> finishes it drops a bit but we see it growing a few MB
> per few minutes. This has caused an out of memory
> after the system has been processing for a day or 2.
> The query is heavy because of the order by but that
> does not explain why the memory is increasing.

Could be a memory leak in PG sort, so please explain further.

> We use a DAO which extends HibernateDaoSupport and the
> method (findIds) has been marked as
> propagation_required. So we assume Spring manages the
> transaction and thus closing of the resultset. Here's
> part of the DAO method: Query q =
> getSession(false).createQuery(query);
> q.setMaxResults(RESULT_SIZE);
> List list = q.list();
> 
> No rocketscience but somehow this causes a problem on
> the DB Server.
> 
> Does anyone have an idea where to look?

The java backtrace mentions bulk update. Where does that fit into this?

You've got a rather large stack of software there and you need to
isolate the problem. If you are running multiple system components on
one system then it is possible that a memory leak in one component can
cause a problem in another. A memory intensive task such as sort would
then be likely to highlight the problem, but that doesn't mean its the
cause of the leak. 

If you can reproduce this problem with a simple repetitive test case
using a script executing psql, that will help. If you cannot, then that
points to a failure in another system component.

Right now, you've not told us much about the query, what release level
you are using etc. Are you staying connected and re-issuing the same
command repeatedly or? We'll need to know more about the memory growth:
which processes does it effect.

We'll also need to know about any custom datatypes involved, or any
parts of the query executing user developed code.

Best Regards, Simon Riggs

---(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] full text indexing

2006-03-15 Thread Oleg Bartunov


On Wed, 15 Mar 2006, chris smith wrote:


Hi all,

Just wondering which full text module is better & what the differences
are between tsearch and fti ?


if you need online indexing and linguistic support (dictionaries, stop 
words, ranking) tsearch2 is fine. If your data are static and you need

only strict search, fti could works for you.
We hope to develop inverted index support for tsearch2 this year, though.



The table in question has roughly 80,000 rows.


how many unique words and how long are documents ?




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

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



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

  http://archives.postgresql.org

Re: [GENERAL] Turn OFF Stats of Postgresql

2006-03-15 Thread Marcos

Hi Ycrux

Thank very much for your help.

Marcos

Em Ter, 2006-03-14 às 21:28 +0100, Ycrux escreveu:
> Ho Marcos!
> You can also try to adpat this parameters to your config:
> 
> TUNING:
>   * First, see:
>   http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
> 
>   * Adjust this params in your "/etc/sysctl.conf" to obtain the 
>   best performances:
> 
>   kernel.shmmax=7000
>   kernel.shmall=1350
> 
> net.core.rmem_max=8388608
> net.core.rmem_default=65536
> net.core.wmem_max=8388608
> net.core.wmem_default=65536
> 
> * Adapt and copy the "postgresql.conf" to your
> "/var/pgsql/data" directory.
>   
> Younes
> 
> Marcos a écrit :
> > Hi,
> >
> > I have a search engine and for increase the performance I want to turn
> > off the stats of Postgresql but i don't know if i should do it.
> >
> > Should I make this?
> >
> > Thanks.
> >
> > Marcos
> >
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
> >
> >   
> 


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

[GENERAL] tsearch is slow

2006-03-15 Thread Abbath

Hi,

Recently I have tried TSearch2 (also on Linux and win with an
Athlon64 3000+ machine).
I have a table and I loaded some text files into it, so I have a row
number, and a text column and there is a ts_vec column for tsvector.
I created the gist index for ts_vec. The table has ~ 1 million
records. It seems that using a regular search query like:

SELECT line_number, headline(line, to_tsquery('keyword'))
FROM tstexts
WHERE ts_vec @@ to_tsquery('keyword')
ORDER BY rank(ts_vec, to_tsquery('keyword')) DESC

is slow for the first time (7-15 sec), but then using the same keyword
next time it is fast (10-100 ms). The reason is, as I read, first time
it is not cached at all, but next time the index pages are
cached so it is fast.

I think in a real word application, in this form, it is useless, because
the 15 sec search time is not allowable for any user. Is there a way
to initially cache the index or tune some postgres parameter? (I tried
to increase "shared_buffers", "effective_cache_size", "work_mem" but
had no effect on it) Or I should look for another search technology? (I
just want to provide a 2 sec maximum search time at 1 million records,
I think it is not a big expectation nowadays)


Abbath




---(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] Wisconsin Circuit Court Access (WCCA) on

2006-03-15 Thread Stephen Frost

* Bruno Wolff III ([EMAIL PROTECTED]) wrote:
> On Tue, Mar 14, 2006 at 23:27:24 -0500,
>   Stephen Frost <[EMAIL PROTECTED]> wrote:
> > About which vendors they use and what contracts they have and you might
> > be able to figure out which vendors have such a clause.  I don't know
> > that such a request could compel the performance data out associated
> > with a specific vendor when that's clearly against a license the state
> > is currently under.
> 
> But we already have an unofficial comment on the performance, we just don't
> know what database postgres is being compared to.

We've probably got a pretty good idea already. :)  Besides, all you'd be
able to get down to would be: what database vendors the state uses
(probably more than one) filtered by which of those have such a clause
in their license (also probably more than one), so in the end all you
know is that it one of a set.

Besides, I don't think it's a good move for us to go digging around
trying to force the state to tell us and then assuming we can corrolate
that to what Kevin was talking about.  Both from a "it's really not that
big a deal" and a "Kevin's a nice guy, let's not get him into trouble
and make him feel like he can't say anything" perspective. :)

Thanks,

Stephen

signature.asc
Description: Digital signature

Re: [GENERAL] question about postgresql time intervals

2006-03-15 Thread Linda

According to the SQL standard, shouldn't this work?

select '506:47:04'::interval day to second ;

Is there a portable way to do this without using justify_hours()?

- Thanks

On Tuesday 14 March 2006 6:58 pm, Michael Glaesemann wrote:
> You will get better, faster answers by sending questions to a  
> PostgreSQL mailing list. By emailing me directly you may not get a  
> timely response if I don't have time to answer. Others can then  
> answer and learn from the subsequent discussion. I'm ccing this to  
> pgsql-general.
> 
> On Mar 15, 2006, at 6:45 , Linda wrote:
> 
> > Hi, Michael
> >
> > I have a question about the output format of the INTERVAL type in  
> > version
> > 8.1.1.  In previous versions, I could do the following:
> >
> > select (uptime::varchar)::interval  from machine_info;
> >
> > where uptime is an INTEGER type, the number of seconds the machine  
> > has been
> > up since last reboot.  This used to produce output in this format:
> > 21 days 02:47:04
> >
> > Now in v8.1.1, the output format is
> > 506:47:04
> >
> > How can I get the "justified" output as before?  Is there some  
> > setting of
> > datestyle that affects the output?  I have tried specifying  
> > "interval day
> > to second" but that doesn't work.  Using the new justify_hours  
> > function
> > works, but is it possible to do something that will run on older  
> > versions
> > of postgresql?
> >
> > Thanks,
> > Linda
> >
> > -- 
> > Linda Gray
> > Unitrends Corporation
> > 803.454.0300 ext. 241
> >
> 
> justify_hours is also in 8.1 and should do what you want.
> 
> test=# select '506:47:04'::interval;
> interval
> ---
> 506:47:04
> (1 row)
> 
> test=# select justify_hours('506:47:04'::interval);
>justify_hours
> --
> 21 days 02:47:04
> (1 row)
> 
> test=# select version();
>  
> version
>  
> --
> PostgreSQL 8.1.3 on powerpc-apple-darwin8.4.0, compiled by GCC  
> powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.  
> build 5247)
> (1 row)
> 
> 
> Michael Glaesemann
> grzm myrealbox com
> 
> 
> 
> 

-- 
Linda Gray
Unitrends Corporation
803.454.0300 ext. 241

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

Re: [GENERAL] tsearch is slow


Abbath wrote:

is slow for the first time (7-15 sec), but then using the same keyword
next time it is fast (10-100 ms). The reason is, as I read, first time
it is not cached at all, but next time the index pages are
cached so it is fast.

I think in a real word application, in this form, it is useless, because
the 15 sec search time is not allowable for any user.


What, never? Even if this facility is only used once a year by one user 
and you have 1000 other users who need their queries to complete in 0.2 
secs at most? What you mean is that it's not useful for *your* 
application - don't assume the same applies to all applications.


> Is there a way

to initially cache the index or tune some postgres parameter? (I tried
to increase "shared_buffers", "effective_cache_size", "work_mem" but
had no effect on it) Or I should look for another search technology? (I
just want to provide a 2 sec maximum search time at 1 million records,
I think it is not a big expectation nowadays)


If you want to force the data to be cached, just put a cron-job in to 
run a query for "abc" or whatever once a minute.


Of course, if it turns out that your users aren't running the query very 
often then you're wasting resources, and if they are running it often 
then again you're wasting resources. But - if you really need to, that's 
the solution.


--
  Richard Huxton
  Archonet Ltd

---(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] Dumping rows into an array?

[EMAIL PROTECTED] writes:
> I've been unable to come up with the counterpart to select the
> keywords and populate an array that I can return..  

I think you want something along the lines of

kwlist := array(select keyword from keywords where ...);

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] Inserting in psql - invalid byte sequence for encoding "UNICODE": 0xe9

2006-03-15 Thread Alejandro Michelin Salomon \( Adinet \)

CSN <[EMAIL PROTECTED]> writes:
> I created a new database with encoding UTF8, connected
> using psql, and ensured the client encoding is also
> UTF8 (Unicode). But when I try to insert characters
> like 'é', I get this error:

> ERROR:  invalid byte sequence for encoding "UNICODE":
> 0xe9

Whatever you're typing in is not actually sending UTF8 to psql.
You probably need to be fooling with the settings of xterm or
whatever window system you're working in.

regards, tom lane

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

[GENERAL] Five reasons why you should never use PostgreSQL -- ever

2006-03-15 Thread TJ O'Donnell


Slashdot had this today.

http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html

TJ O'Donnell
www.gnova.com

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

Re: [GENERAL] Five reasons why you should never use PostgreSQL -- ever

2006-03-15 Thread Thomas Hallgren


TJ O'Donnell wrote:

Slashdot had this today.

http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html 




Interesting. JDBC is now a 'language'.

Regards,
Thomas Hallgren


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

[GENERAL] encoding aliases

2006-03-15 Thread Vivek Khera

We're developing a DB that will be storing email messages.  The clear  
winner for the DB encoding is UTF8.  However, I will need to set the  
proper client encoding based on the encoding as defined in the email  
message.


Looking at the docs (http://www.postgresql.org/docs/8.1/static/ 
multibyte.html), there are many encodings that I can use for the  
client.  However they do not match the canonical names used in  
email.  For example, WINDOWS-1252 is accepted, presumably as an alias  
for WIN1252, though it is not listed as an alias. The commentary in  
utils/mb/encnames.c indicates that the dashes are irrelevant, so we  
know ISO-8859-1 and ISO88591 are equivalent.


I've only tried a handful of encoding values found in email so far,  
but the only one that is not accepted is US-ASCII.


My only concern is that names like WINDOWS-1252 is really an alias  
for WIN1252.  What would make this 100% clear is if "SHOW  
client_encoding" would report the canonical name rather than the name  
passed to it.  The source shows it is, but the docs do not.


So, is it fair to assume that the longer form names are safe to use  
(ie, should I submit a doc patch)?


And does it make sense to make US-ASCII an alias for SQL-ASCII?



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

Re: [GENERAL] [pgsql-advocacy] Wisconsin Circuit Court Access (WCCA) on

2006-03-15 Thread Darcy Buskermolen

On Tuesday 14 March 2006 16:00, Kevin Grittner wrote:
> >>> On Tue, Mar 14, 2006 at  2:08 am, in message
>
> <[EMAIL PROTECTED]>, Simon Riggs
>
> <[EMAIL PROTECTED]> wrote:
> > On Mon, 2006- 03- 13 at 13:27 - 0600, Kevin Grittner wrote:
> >> Even more important is the fast response we have had when posting
> >> problems to the lists.  We have normally had a fix within 24 hours.
> >>
> >> Frankly, the support has been amazing.
> >
> > Kevin, well done. We've all watched your progress with interest.
>
> Thanks to all who have offered congratulations.
>
> > The reason you've got excellent support is because of the detailed
> > postings you've made, together with responses to all replies. Doing
>
> all
>
> > your homework before posting is essential; unfortunately many people
> > don't do this and then leave disappointed.
>
> Here I think you underestimate how well the community helps people in
> these lists.  I have witnessed remarkable patience here when people post
> vague messages asking for help.  You (as a community) generally succeed
> in drawing out sufficient detail to provide good advice, and / or
> identify areas for product improvement.  I do try to give as much
> information as I can, including reproducible test cases where
> practicable; but, I have done so with commercial vendors to whom my
> clients have paid big money for support, and been very disappointed.
>
> With one commercial vendor we've routinely been told by first line
> support staff that the product was functioning as intended.  After days
> of effort, sometimes involving calls from top management, we've gotten
> through to someone who can actually understand the problem and
> acknowledge the bug; only to have it take months (sometimes over a year)
> to get a fix,
>
> With another open source vendor, from whom no support is available
> without a paid license and a paid support contract, we (after paying for
> a commercial license and a support contract) have been told that such
> things as using an OR predicate within the ON clause of a JOIN was an
> "unimplemented feature" (even though it worked in simple cases).  They
> said they might "add the feature" in the next major release, but that
> wouldn't be for at least a year, and no guarantees.
>
> It was unexpected and quite refreshing to provide the same level of
> detail in a post to a PostgreSQL list, and get a patch file fast enough
> to be running a fixed version within 24 hours of posting the problem.
> When we have been able to provide sufficient detail and / or a test
> case, this has usually been the result.  When we participated in the
> beta test phase, people were quite helpful in leading me through the use
> of unfamiliar tools to capture the information they needed to identify
> and fix problems before the official release.
>
> After decades of working as an independent consultant, I've recently
> (eight days ago) accepted employment with the Wisconsin Court System as
> a DBA, and I'm told that as a court employee I'm not allowed to endorse
> one product over another; but, I can speak of my experiences with
> products so long as I don't violate any constraints of the license
> agreements.  I have worked with quite a few database products in my
> career and can say unequivocally that the support I've seen provided for
> PostgreSQL is superior to that which I've seen provided for any other
> database product.
>
> I don't want to name any names, because I would undoubtedly forget
> several very helpful people here, but I have to admit that my personal
> favorite was when I posted information about a bug in the JDBC driver
> shortly before I left for the day, and while I was sleeping a user in
> Germany created a program to cause the race condition, tracked down the
> cause, and posted a patch with a suggested fix.  By the time I'd
> finished my coffee the next morning, the patch had been reviewed, scaled
> back to the minimum change required to effect a fix, applied to CVS, and
> a new jar file deployed for download.  Wow.
>
> I can't really accept congratulations for this successful deployment
> without offering it right back to the community for all the help you've
> provided, as well as the product itself.  Absolutely fantastic, all
> around!
>
> -Kevin

If we do not have a testimonilas page, then  this is the perfect example of 
why we should, and what should be on it.  As good as we are in features, text 
like this can be far more efective in getting a foot in the door with PHB.

(just my 2 bits worth)
 

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

-- 
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

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

[GENERAL] Indexes on array columns

2006-03-15 Thread Brendan Duddridge

Hi,Is it possible to put an index on an array column?Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]  ClickSpace Interactive Inc.  Suite L100, 239 - 10th Ave. SE  Calgary, AB  T2G 0V9 http://www.clickspace.com   

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] encoding aliases

2006-03-15 Thread Martijn van Oosterhout

On Wed, Mar 15, 2006 at 11:33:25AM -0500, Vivek Khera wrote:
> We're developing a DB that will be storing email messages.  The clear  
> winner for the DB encoding is UTF8.  However, I will need to set the  
> proper client encoding based on the encoding as defined in the email  
> message.

Given that the messages directly encode both the encoding and the text
encoded, wouldn't make sense to store the emails in SQL_ASCII, i.e.
don't interpret the data at all, just consider it a bunch-of-bytes.

However, it seems what you're asking is as if you actually want to
interpret the content (i.e. you don't want to get out exactly what you
put in) and preserve semantics for easy searching, yes? I'm not sure
how to do that easily, since different parts of email can be in
different encodings.

To be honest, rather than relying on postgres behaviour, why not build
up a table mapping email encodings to postgres encodings. It costs
little but could save some hassle later on.

BTW, SQL-ASCII really means unknown encoding more than anything else...

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

signature.asc
Description: Digital signature

[GENERAL] psqlODBC Unicode 8.01.02.00 Driver "Overflows" On MS-Access Dates

2006-03-15 Thread Randy Yates

Hi Folks,

I'm trying to export a table from my MS-Access database to my postgresql
database. I downloaded and installed the 8.01.02.00 driver on my Win2k
system, created the datasource. Then when I try to export my table to the
datasource, I get a single-world message "overflow" and the export aborts.

I traced the cause of the overflow to a column that is in MS-Access's 
date/time format. 

Is this a known bug? Is there a workaround?
-- 
%  Randy Yates  % "Bird, on the wing,
%% Fuquay-Varina, NC%   goes floating by
%%% 919-577-9882%   but there's a teardrop in his eye..."
 <[EMAIL PROTECTED]>   % 'One Summer Dream', *Face The Music*, ELO
http://home.earthlink.net/~yatescr

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

[GENERAL] PostgreSQL Knowledge Base

2006-03-15 Thread Stephen Slezak

In the interim period while a central PostgreSQL community knowledge base is 
being put together, Pervasive Software is opening up access to our PostgreSQL 
Knowledge Base ( http://www.pervasivepostgres.com/instantkb13/). Any interested 
person can submit a KB article on our web site ( 
http://www.pervasivepostgres.com/kb/kbarticle.asp) and after review to make 
sure all is on the up and up, we will post it in our Knowledge Base. Of course 
authors will receive credit.
 
When the Community version of the Knowledge Base is ready to go, any and all 
articles submitted will be made available to it.

Enjoy!

Stephen Slezak
Director Worldwide Marketing, Database Division
Pervasive Software
http://www.pervasive.co

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

Re: [GENERAL] Dumping rows into an array?

2006-03-15 Thread karly

On Wed, Mar 15, 2006 at 09:52:48AM -0500, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > I've been unable to come up with the counterpart to select the
> > keywords and populate an array that I can return..  
> 
> I think you want something along the lines of
> 
>   kwlist := array(select keyword from keywords where ...);
> 
>   regards, tom lane

Thanks for the reply.  

I thought I had tried this, and I had tried something similar, but
for some reason I was using the syntax

   ARRAY[(SELECT ...)]

which didn't work.  I'll have to go read Section 8.10 again.

Thanks for the help.  Now on to my next bug.  {-;

-karl

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

2006-03-15 Thread Martijn van Oosterhout

On Wed, Mar 15, 2006 at 07:25:16PM +0100, Stephen Slezak wrote:
> In the interim period while a central PostgreSQL community knowledge
> base is being put together, Pervasive Software is opening up access
> to our PostgreSQL Knowledge Base (
> http://www.pervasivepostgres.com/instantkb13/). Any interested person
> can submit a KB article on our web site (
> http://www.pervasivepostgres.com/kb/kbarticle.asp) and after review
> to make sure all is on the up and up, we will post it in our
> Knowledge Base. Of course authors will receive credit.

Good news. My only question is: are you limiting your target audience?
For example, do you intend to have it aimed at non-technical end-users
or are you also aiming at technical docs relating making external
modules and such?

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


signature.asc
Description: Digital signature

RES: [GENERAL] Creating a function that acept any data type

Thanks Michael and Tom.

Y try put anyelement and the function works perfectly.

Alejandro Michelin Salomon
Porto Alegre
Brasil


-Mensagem original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Em nome de Michael Fuhr
Enviada em: sexta-feira, 10 de março de 2006 17:43
Para: Alejandro Michelin Salomon ( Adinet )
Cc: Pgsql-General
Assunto: Re: [GENERAL] Creating a function that acept any data type


On Fri, Mar 10, 2006 at 05:12:53PM -0300, Alejandro Michelin Salomon (
Adinet ) wrote:
> I am working in a migration. Im am migrating systems based in mysql to 
> postgresql.
> 
> I am trying to create a function named IFNULL, to not migrate any 
> ocurrence of this mysql function in my code.
> 
> The IFNULL function is the same of COALESCE in postgresql.

Are you aware of the MySQL Compatibility Functions module?  It has IFNULL.

http://pgfoundry.org/projects/mysqlcompat/
http://software.newsforge.com/article.pl?sid=05/12/15/1611251&from=rss

> This code does not work.
> 
> CREATE OR REPLACE FUNCTION IFNULL( xValor ANY, xPadrao ANY ) RETURNS 
> ANY AS $$

Change ANY to ANYELEMENT and the code should work.  And for something this
simple you could use an SQL function:

CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement) RETURNS anyelement
AS $$
  SELECT COALESCE($1, $2);
$$ LANGUAGE sql IMMUTABLE;

You'll have to cast one of the arguments if their types can't be determined.

test=> SELECT ifnull('abc', 'xyz');
ERROR:  could not determine anyarray/anyelement type because input has type
"unknown"

test=> SELECT ifnull('abc', 'xyz'::text);
 ifnull 

 abc
(1 row)

test=> SELECT ifnull(NULL, 'xyz'::text);
 ifnull 

 xyz
(1 row)

-- 
Michael Fuhr

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


-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/3/2006


-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/3/2006
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.4/282 - Release Date: 15/3/2006
 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.4/282 - Release Date: 15/3/2006


---(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] tsearch is slow

2006-03-15 Thread Abbath

Hello Richard,

Wednesday, March 15, 2006, 3:35:26 PM, you wrote:

> Abbath wrote:
>> is slow for the first time (7-15 sec), but then using the same keyword
>> next time it is fast (10-100 ms). The reason is, as I read, first time
>> it is not cached at all, but next time the index pages are
>> cached so it is fast.
>> 
>> I think in a real word application, in this form, it is useless, because
>> the 15 sec search time is not allowable for any user.

> What, never? Even if this facility is only used once a year by one user
> and you have 1000 other users who need their queries to complete in 0.2
> secs at most? What you mean is that it's not useful for *your* 
> application - don't assume the same applies to all applications.

The search function will be a frequently used one so it shouldn't be
slow. Ok, maybe it is not suitable for me if I want a user friendly
search function.

 >> Is there a way
>> to initially cache the index or tune some postgres parameter? (I tried
>> to increase "shared_buffers", "effective_cache_size", "work_mem" but
>> had no effect on it) Or I should look for another search technology? (I
>> just want to provide a 2 sec maximum search time at 1 million records,
>> I think it is not a big expectation nowadays)

> If you want to force the data to be cached, just put a cron-job in to
> run a query for "abc" or whatever once a minute.

I can't guess what the user want to search.

> Of course, if it turns out that your users aren't running the query very
> often then you're wasting resources, and if they are running it often
> then again you're wasting resources. But - if you really need to, that's
> the solution.

It seems if I periodically use VACUUM ANALYZE, it helps to reduce the search
time...I will see how can I tune it more.

Thanks for the reply.

Abbath


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

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

Re: [GENERAL] tsearch is slow

2006-03-15 Thread Scott Marlowe

On Wed, 2006-03-15 at 13:36, Abbath wrote:
> Hello Richard,
> 
> Wednesday, March 15, 2006, 3:35:26 PM, you wrote:
> > If you want to force the data to be cached, just put a cron-job in to
> > run a query for "abc" or whatever once a minute.
> 
> I can't guess what the user want to search.

But that query will likely load up all the index info into memory.

> > Of course, if it turns out that your users aren't running the query very
> > often then you're wasting resources, and if they are running it often
> > then again you're wasting resources. But - if you really need to, that's
> > the solution.
> 
> It seems if I periodically use VACUUM ANALYZE, it helps to reduce the search
> time...I will see how can I tune it more.

That statement is pretty telling.  You're new to PostgreSQL I'll
assume.  You'll need to read up on the periodic maintenance section of
the docs.

Here ya go: 
http://www.postgresql.org/docs/8.1/interactive/maintenance.html

---(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] PostgreSQL Free Visual Manager

2006-03-15 Thread Ycrux


Hi Folks!

I'm looking for a free or open source PostgreSQL visual manager (Linux 
or Windows).


My aim is to quickly create and manage databases. The ability to export 
DB schema to

file will be really useful for me.

Thanks in advance
/youn



---(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 Free Visual Manager

2006-03-15 Thread Moises Alberto Lindo Gutarra

http://www.pgadmin.org/

2006/3/15, Ycrux <[EMAIL PROTECTED]>:
> Hi Folks!
>
> I'm looking for a free or open source PostgreSQL visual manager (Linux
> or Windows).
>
> My aim is to quickly create and manage databases. The ability to export
> DB schema to
> file will be really useful for me.
>
> Thanks in advance
> /youn
>
>
>
> ---(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
>


--
Atentamente,
 .~.
( 0 0 )  Moisés Alberto Lindo Gutarra
/  V  \   Asesor - Desarrollador Java / Open Source
   //   \\  TUMI Solutions S.A.C.
/((   _))\ Cel: 97366260  Trab: 3481104
   oo0 0oo MSN: [EMAIL PROTECTED]

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

2006-03-15 Thread Daniel Blaisdell

I am currently in a situation where I have a distributed application
between a few remote nodes all connecting to a central database. I have
been searching for a database replication or synchronization system
that will allow a disconnected node to operate independently of the
central database. The ultimate intention of this type of system is to
have an application operate off of data locally and have those changes
synced in the background. 

>From the research I've done, Slony doesn't support multi-master
writers. PGCluster and ExtenDB seem to require all nodes be next to
each other with the ultimate goal of local load balancing. 

Are there any solutions that come close to the requirements I'm after?

Thanks for any input,

-Daniel

Re: [GENERAL] PostgreSQL Free Visual Manager

2006-03-15 Thread Ycrux


Thanks guys, I'll give it a try

cheers
/youn

Moises Alberto Lindo Gutarra a écrit :

http://www.pgadmin.org/

2006/3/15, Ycrux <[EMAIL PROTECTED]>:
  

Hi Folks!

I'm looking for a free or open source PostgreSQL visual manager (Linux
or Windows).

My aim is to quickly create and manage databases. The ability to export
DB schema to
file will be really useful for me.

Thanks in advance
/youn



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





--
Atentamente,
 .~.
( 0 0 )  Moisés Alberto Lindo Gutarra
/  V  \   Asesor - Desarrollador Java / Open Source
   //   \\  TUMI Solutions S.A.C.
/((   _))\ Cel: 97366260  Trab: 3481104
   oo0 0oo MSN: [EMAIL PROTECTED]

  



---(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 Free Visual Manager

2006-03-15 Thread Tony Caduto



I'm looking for a free or open source PostgreSQL visual manager (Linux 
or Windows).


My aim is to quickly create and manage databases. The ability to 
export DB schema to

file will be really useful for me.

It's not free, but you can get PG Lightning Admin right now for 5 
dollars and 1 dollar goes to the postgresql project.
http://www.amsoftwaredesign.com or 
http://www.amsoftwaredesign.com/lightning_admin.php



You can print DDL, save as PDF, export and import to/from many sources, 
tabbed enterprise manager, tabbed MDI interface, grant manager plus lots 
more.
It's really nice and worth many times the 5 dollars it's going for right 
now.



Tony

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

[GENERAL] Can the PostgreSQL store the Multimedia files

2006-03-15 Thread senyorita . abeer

hallo, I am working with a group on a Gradute project and we use the
PostgreSQL database for build an information system for a school.
we have a small question:
- Can the PostgreSQL store the Multimedia files ( Images ,video
,audio)??and exactly how??
- IF not , can we make some change in the source code to make this
support for multimedia??

pleaze I want the answer very fast
cu


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

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

[GENERAL] ERROR: FULL JOIN is only supported with merge-joinable join conditions

2006-03-15 Thread Harco de Hilster


Hi all,

I am porting my application from Ingres to Postgres, and I have the 
following problem. I am not sure if this is a known limitation of 
Postgresql or a bug. My code works under Ingres but fails in Postgres 
with the following error:


ERROR:  FULL JOIN is only supported with merge-joinable join conditions

My table contain temporal data e.g.

Table A:
f1 | f2 | modtime | exptime
--
A | B | t0 | t2   <= historical record
A | C | t2 | t6<= historical record
A | D | t6 | NULL   <= live record

Table B:
f1 | f2 | modtime | exptime
--
F | G | t1 | t3 <= historical record
F | H | t3 | t5 <= historical record
F | I | t5 | NULL <= live record

All queries on live data are of the form: select * from a where f1 = xx 
and exptime is NULL


A full outer join on two tables with temporal data looks like this:

select *
from A
full outer join B on A.f1 = B.f1  and  ((A.ExpTime IS NULL AND B.ExpTime 
IS NULL) OR (A.ModTime <= B.ExpTime AND (B.ExpTime > A.ExpTime OR 
B.ExpTime IS NULL)))


The primary keys of A and B are (f1, exptime).


Postgres's problem is with the <=, > and is null conditions in the full 
outer join. These are probably not 'merge-joinable', so the query fails. 
Shouldn't it try a different method instead of failing??


I cannot move the conditions on exptime to the where clause, because 
that would introduce (outer join) extra records with historical data in 
B that are not in the lifetime span of records in A.


Any suggestions or is this a show stopper?

Thanks,

Harco



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

[GENERAL] Problems with Postgres and TCP/IP Protocol

2006-03-15 Thread Paulo

I installed recently the last version of Postgres, but I can't get to 
connect using any tcp/ip port. I tried many. Few time ago I used the 
NLite program and I remove the Secondary Logon service and the program 
always asks by this service. So, I installed no service. I tried no 
firewalls too and I had the same problem.


When I tries with pgadmin appear the hourglass and stay so. Thus I needs 
to close the window of program.


I tried through psql and not worked again. The cursor is blinking below 
of command and nothing more happens.


Thank you very much for any help.

Paulo.

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

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

[GENERAL] hi problem with installing postgresql8.1

2006-03-15 Thread VenuGopal Papasani

Hi all,    I am new to pgsql family.I had a problem with installing postgres8.1 i was getting the following error at the 4th step of installation the error is as follows:Data directory error:  The specified data directory is not empty
   If you have an existing database with teh same major version number,you do not need to initialise a database cluster.   If you have an existing database with the same major version you need to backup your old database and create a new one.
   why this warning actually arises.If  I install by unchecking the initialise database cluster then the next three steps of installation procedure is not shown.It goes directly installation and starts installation after some time a fatal error arises saying accont not matching
    and rolls back.i have tried it with lots of account names and lot of super user names.The steps i was talking about are as per the given link.  
http://pginstaller.projects.postgresql.org    Can any one give the solution to this problem.Please suggest solution to overcome this problem ASAP.Thanks in advance,Venu.

[GENERAL] Case Sensitive problem

2006-03-15 Thread Erik Ferencz




Hi,
I have got problems with case sensitive 
sort.
My Postgre DB is Case sensitive but I need case 
insensitive.
Could anyone help me,please.
I don't know where or how to set this 
option.
 
Thanks.
 
Erik ferencz

Re: [GENERAL] full text indexing

2006-03-15 Thread Ian Harding

On 3/15/06, chris smith <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> Just wondering which full text module is better & what the differences
> are between tsearch and fti ?

Having only used tsearch/tsearch2 all I can say that it works as
advertised and I am extremely happy with it.

- Ian

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

[GENERAL] PostgreSQL on Windows not starting

2006-03-15 Thread Chris Travers

I have a customer who is having issues starting PostgreSQL 8.1 on 
Windows.  It worked for a while and now doesn't appear to be running.  I 
thought it was probably a stale pidfile, but had him search and could 
not find it.  Is the pid information still in a pidfile or is it in the 
registry somewhere on Windows?  I am suggesting that he try to start the 
service manually and look for error messages.  But in the mean time I 
thought I would ask.


Best Wishes,
Chris Travers
Metatron Technology Consulting
begin:vcard
fn:Chris Travers
n:Travers;Chris
email;internet:[EMAIL PROTECTED]
tel;work:509-888-0220
tel;cell:509-630-9974
x-mozilla-html:FALSE
version:2.1
end:vcard


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

[GENERAL] catch SELECT statement return

hi all,
i am working on this postgresql statement. it picks up all non-null
values only. is there a way to pickup all hour values (if any hour
value not existing, still find them and assign their value to be 0).
coz my table does not contain all hour values, only ones which have a
non-zero value.

SELECT to_timestamp(to_char (last_edit_timestamp,'-MM-DD
HH24:00:00'), '-MM-DD HH24:00:00')AS edit_time, count(to_char
(last_edit_timestamp,'-MM-DD HH24:00:00'))
as edit_time_count FROM dbpt_containerlog GROUP BY to_char
(last_edit_timestamp,'-MM-DD HH24:00:00') ORDER BY to_char
(last_edit_timestamp,'-MM-DD HH24:00:00');

please help me, all help appreciated. thanks


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

[GENERAL] Hour records within a date range

hi all,
i am trying to take a datefrom and dateto from a user and insert all
hour records within that date range within the db. eg, if the user
selects 2006-03-14 as datefrom and 2006-03-14 as dateto, the system
would insert 24 rows within the table with their corresponding
timestamps (eg 2006-03-14 01:00:002006-03-14 23:00:00).
any suggestions on how to do this. thanks.


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

   http://archives.postgresql.org

[GENERAL] \copy combine with SELECT

2006-03-15 Thread jia ding

Hi all,I tried: select id, name  into table2   from table1; \copy table2 to filename.txt in order to export 2 columns from table1 to a file.But, I am thinking, if there is a command can combine these two command together?
Maybe, something like: \copy select id,name  from table  to filename.txt ?Nina

[GENERAL] Disability the trigger

2006-03-15 Thread Claudio Tognolo

I can disable the Trigger?-- Claudio Tognolo[EMAIL PROTECTED]

Re: [GENERAL] Create a new table

i just had this thought.
i should take both dates, subtract them and multiply the figure by 24.
then i should insert that many rows with their individual timestamps
into the table in a sequential order.
eg 2005-12-12 12:00:00
2005-12-13  01:00:00 and so on.

can somebody please guide me in this.
thanks.


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

2006-03-15 Thread Editores S.A.




 
Tengo una consulta 
acerca de manejo de concurrencia  en postgres con PHP
 
En un acceso 
de  50  usuarios simultaneamente como agilizar el bolqueo de 
tablas accesadas
y como puedo 
garantizar un refresco de pantalla en PHP, que la informacion 
desplegada  este actualizada.  evitar un desbordamiento de 
memoria por vistas sobre la BD

[GENERAL] URGENT!!! SELECT statement please help

hi all,

i have a web based java application with a postgres db.

now i am trying to generate a temp table which contains all hour
records for a selected date range. eg. if the user selects 2006-03-14
as from and 2006-03-14 as to, the system should insert 24 hour records
with their individual time stamps (eg 2006-03-14 12:00:00).

now currently, i have a select statement which searches another table,
finds data and then inserts them. but the problem is that if there is
no data for an hour, it does not insert anything.

now i can think of a couple of solutions,
1. insert additional records using a second statement, which compares
the table and inserts any missing records
2. insert data as usual and then using a second statement, compare the
content and insert any missing records.

my current implementation is:
" INSERT INTO temp_table (edit_time,edit_time_count) " +
" SELECT to_timestamp(to_char (last_edit_timestamp,'-MM-DD
HH24:00:00'),
   '-MM-DD HH24:00:00')AS edit_time, " +
" count(to_char (last_edit_timestamp,'-MM-DD HH24:00:00')) as
edit_time_count " +
" FROM " + tableName + " " + sqlWhereStr +
" GROUP BY to_char (last_edit_timestamp,'-MM-DD HH24:00:00') " +
" ORDER BY to_char (last_edit_timestamp,'-MM-DD HH24:00:00'); ";

the problem is that if the select statement does not return anything
for a particular hour record, that record is not inserted. now i would
like to have a table with all records including hours with 0 count.

can somebody please help me urgently. i would really appreciate it.
thanks.


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

   http://archives.postgresql.org

[GENERAL] Create a new table

hi all,
i am trying to create a postgres table from within my java code.
now the scenario is that the user selects a date range and clicks
submit from my HTML form. now i want to create a table, which contains
records for each hour within that date range as a timestamp and another
column for value which is 0 for each of them.
eg. if the user selects between 2006-03-13 and 2006-03-14, the system
should generate a table with 24 entries/records as type timestamp for
each hour within that date range with corresponding values of 0.

i hope i am able to get my thought across. i have attempted a few
approaches but am not able to get something working. hopefully some of
you geniuses will be able to help me out.

thanks.


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

[GENERAL] Project survey: Commercial Companies and the Open Source Community

2006-03-15 Thread imypraz

Hi,

I am a student studying Business IT Systems (MSc) at the University of
Strathclyde in Scotland.

For my masters dissertation I am investigating the relationship between
commercial companies and the open source community.

The project examines the issues, motivations and critical success
factors in this collaborative relationship.

If you have experience in this area ... i.e. have worked for a company
that has released code into the community... Or would simply like to
pass on your opinions... I would be grateful if you could participate
in my survey.

The survey can be found at ...
http://FreeOnlineSurveys.com/rendersurvey.asp?sid=v4qpja3izsf7w9f170254


Thnx for ur time.

Imran


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

Re: [GENERAL] Can the PostgreSQL store the Multimedia files

2006-03-15 Thread Devrim GUNDUZ

Hi,

On Fri, 2006-03-10 at 08:47 -0800, [EMAIL PROTECTED] wrote:

> - Can the PostgreSQL store the Multimedia files ( Images ,video
> ,audio)??and exactly how??

Yes. The keyword is "large object".

> pleaze I want the answer very fast

Was that fast enough?

-- 
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: PL/php, plPerlNG - http://www.commandprompt.com/


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

Re: [GENERAL] What's a good default encoding?

2006-03-15 Thread Michael Schmidt





Perhaps others can comment on encoding versus type of data.  I would 
add that the manner in which data are accessed may also be a 
consideration.  Specifically, UTF-8 is a good choice if one is going to use 
JDBC.
 
Michael Schmidt

Re: [GENERAL] Remote Sync

2006-03-15 Thread Douglas McNaught

"Daniel Blaisdell" <[EMAIL PROTECTED]> writes:

> I am currently in a situation where I have a distributed application
> between a few remote nodes all connecting to a central database. I
> have been searching for a database replication or synchronization
> system that will allow a disconnected node to operate independently
> of the central database. The ultimate intention of this type of
> system is to have an application operate off of data locally and
> have those changes synced in the background.

I don't think there are any.  The general problem is very hard to
solve, because "synchronization" means different things to different
apps, and approaches to conflict resolution differ.  The consensus
here seems to be that it's best handled at the application level.

-Doug

---(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] URGENT!!! SELECT statement please help

2006-03-15 Thread Ben

Have you considered using a stored proceedure? It seems like it might easily 
give you the logic you're after.


http://www.postgresql.org/docs/8.1/static/xplang.html

On Sun, 12 Mar 2006, [EMAIL PROTECTED] wrote:


hi all,

i have a web based java application with a postgres db.

now i am trying to generate a temp table which contains all hour
records for a selected date range. eg. if the user selects 2006-03-14
as from and 2006-03-14 as to, the system should insert 24 hour records
with their individual time stamps (eg 2006-03-14 12:00:00).

now currently, i have a select statement which searches another table,
finds data and then inserts them. but the problem is that if there is
no data for an hour, it does not insert anything.

now i can think of a couple of solutions,
1. insert additional records using a second statement, which compares
the table and inserts any missing records
2. insert data as usual and then using a second statement, compare the
content and insert any missing records.

my current implementation is:
" INSERT INTO temp_table (edit_time,edit_time_count) " +
" SELECT to_timestamp(to_char (last_edit_timestamp,'-MM-DD
HH24:00:00'),
  '-MM-DD HH24:00:00')AS edit_time, " +
" count(to_char (last_edit_timestamp,'-MM-DD HH24:00:00')) as
edit_time_count " +
" FROM " + tableName + " " + sqlWhereStr +
" GROUP BY to_char (last_edit_timestamp,'-MM-DD HH24:00:00') " +
" ORDER BY to_char (last_edit_timestamp,'-MM-DD HH24:00:00'); ";

the problem is that if the select statement does not return anything
for a particular hour record, that record is not inserted. now i would
like to have a table with all records including hours with 0 count.

can somebody please help me urgently. i would really appreciate it.
thanks.


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

  http://archives.postgresql.org



---(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] Case Sensitive problem

2006-03-15 Thread Douglas McNaught

"Erik Ferencz" <[EMAIL PROTECTED]> writes:

> Hi,
> I have got problems with case sensitive sort.
> My Postgre DB is Case sensitive but I need case insensitive.
> Could anyone help me,please.
> I don't know where or how to set this option.

It's not an option you can set.  Just do "ORDER BY upper(mycolumn)".

-Doug

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

Re: [GENERAL] Case Sensitive problem


Erik Ferencz wrote:

Hi,
I have got problems with case sensitive sort.
My Postgre DB is Case sensitive but I need case insensitive.
Could anyone help me,please.
I don't know where or how to set this option.


What output do you get and what do you expect to get?

It sounds like a locale issue, I'm not sure if you can change this on 
the fly though.


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

---(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] catch SELECT statement return


[EMAIL PROTECTED] wrote:

hi all,
i am working on this postgresql statement. it picks up all non-null
values only. is there a way to pickup all hour values (if any hour
value not existing, still find them and assign their value to be 0).
coz my table does not contain all hour values, only ones which have a
non-zero value.

SELECT to_timestamp(to_char (last_edit_timestamp,'-MM-DD
HH24:00:00'), '-MM-DD HH24:00:00')AS edit_time, count(to_char
(last_edit_timestamp,'-MM-DD HH24:00:00'))
as edit_time_count FROM dbpt_containerlog GROUP BY to_char
(last_edit_timestamp,'-MM-DD HH24:00:00') ORDER BY to_char
(last_edit_timestamp,'-MM-DD HH24:00:00');


I don't think it's possible. It won't come up with a left outer join 
because you aren't comparing to anything unless you have a second table 
with all of the times you want to compare.


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

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

  http://archives.postgresql.org

Re: [GENERAL] \copy combine with SELECT


jia ding wrote:

Hi all,

I tried:
select id, name  into table2   from table1;
 \copy table2 to filename.txt
in order to export 2 columns from table1 to a file.

But, I am thinking, if there is a command can combine these two command 
together?

Maybe, something like: \copy select id,name  from table  to filename.txt


Close.

copy tablename field1, field2 to 'filename';

http://www.postgresql.org/docs/8.1/static/sql-copy.html

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

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

Re: [GENERAL] Concurrencia

2006-03-15 Thread Jaime Casanova

redirecting to [EMAIL PROTECTED]

On 3/13/06, Editores S.A. <[EMAIL PROTECTED]> wrote:
>
>
> Tengo una consulta acerca de manejo de concurrencia  en postgres con PHP
>
> En un acceso de  50  usuarios simultaneamente como agilizar el bolqueo de
> tablas accesadas
> y como puedo garantizar un refresco de pantalla en PHP, que la informacion
> desplegada  este actualizada.  evitar un desbordamiento de memoria por
> vistas sobre la BD


--
Atentamente,
Jaime Casanova

"What they (MySQL) lose in usability, they gain back in benchmarks, and that's
all that matters: getting the wrong answer really fast."
   Randal L. Schwartz

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

Re: [GENERAL] URGENT!!! SELECT statement please help

2006-03-15 Thread Guy Rouillier

Why have you asked the same question 3 times in five minutes?
Additional responses below.

[EMAIL PROTECTED] wrote:
> hi all,
> 
> i have a web based java application with a postgres db.
> 
> now i am trying to generate a temp table which contains all hour
> records for a selected date range. eg. if the user selects 2006-03-14
> as from and 2006-03-14 as to, the system should insert 24 hour
> records with their individual time stamps (eg 2006-03-14 12:00:00).   
> 
> now currently, i have a select statement which searches another
> table, finds data and then inserts them. but the problem is that if
> there is no data for an hour, it does not insert anything.  
> 
> now i can think of a couple of solutions,
> 1. insert additional records using a second statement, which compares
> the table and inserts any missing records 2. insert data as usual and
> then using a second statement, compare the content and insert any
> missing records.   
> 
> my current implementation is:
> " INSERT INTO temp_table (edit_time,edit_time_count) " +
> " SELECT to_timestamp(to_char (last_edit_timestamp,'-MM-DD
>HH24:00:00'), '-MM-DD HH24:00:00')AS
> edit_time, " + " count(to_char (last_edit_timestamp,'-MM-DD
> HH24:00:00')) as edit_time_count " + " FROM " + tableName + " " +
> sqlWhereStr + " GROUP BY to_char (last_edit_timestamp,'-MM-DD
> HH24:00:00') " + " ORDER BY to_char (last_edit_timestamp,'-MM-DD
> HH24:00:00'); ";
> 
> the problem is that if the select statement does not return anything
> for a particular hour record, that record is not inserted. now i
> would like to have a table with all records including hours with 0
> count.   
> 
> can somebody please help me urgently. i would really appreciate it.
> thanks. 

Don't rely on your SELECT to provide the timestamps.  You know what
dates you are working with, and you know the hour range (1-24).  So
prefill the table with 24 rows for each day in the date range.  Then use
your select statement to update any rows for which you have data.

-- 
Guy Rouillier


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

Re: [GENERAL] tsearch is slow

2006-03-15 Thread Abbath

Hello Scott,

Wednesday, March 15, 2006, 8:49:00 PM, you wrote:

> On Wed, 2006-03-15 at 13:36, Abbath wrote:
>> Hello Richard,
>> 
>> Wednesday, March 15, 2006, 3:35:26 PM, you wrote:
>> > If you want to force the data to be cached, just put a cron-job in to
>> > run a query for "abc" or whatever once a minute.
>> 
>> I can't guess what the user want to search.

> But that query will likely load up all the index info into memory.

Misunderstanding: I experienced that if I run a search for a keyword
first time it is slow, then next time it is fast BUT for that keyword,
not for any keyword.

>> > Of course, if it turns out that your users aren't running the query very
>> > often then you're wasting resources, and if they are running it often
>> > then again you're wasting resources. But - if you really need to, that's
>> > the solution.
>> 
>> It seems if I periodically use VACUUM ANALYZE, it helps to reduce the search
>> time...I will see how can I tune it more.

> That statement is pretty telling.  You're new to PostgreSQL I'll
> assume.  You'll need to read up on the periodic maintenance section of
> the docs.

> Here ya go: 
> http://www.postgresql.org/docs/8.1/interactive/maintenance.html

Yes, I have just started to use postgres so I need further experience.
Thanks for the link.

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



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

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

Re: [GENERAL] question about postgresql time intervals

2006-03-15 Thread Michael Glaesemann



On Mar 15, 2006, at 23:39 , Linda wrote:


According to the SQL standard, shouldn't this work?

select '506:47:04'::interval day to second ;


No one has implemented this in PostgreSQL yet.


Is there a portable way to do this without using justify_hours()?


Not currently that I know of.

Michael Glaesemann
grzm myrealbox com




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

Re: [GENERAL] tsearch is slow

2006-03-15 Thread Scott Marlowe

On Wed, 2006-03-15 at 18:09, Abbath wrote:
> Hello Scott,
> 
> Wednesday, March 15, 2006, 8:49:00 PM, you wrote:
> >> 
> >> I can't guess what the user want to search.
> 
> > But that query will likely load up all the index info into memory.
> 
> Misunderstanding: I experienced that if I run a search for a keyword
> first time it is slow, then next time it is fast BUT for that keyword,
> not for any keyword.

I think you mean "ONLY for that keyword" there?  If everything else
becomes fast but the keyword becomes slow, then we've got a very
interesting (and possibly difficult) problem.

Full text search is the kind of problem you throw ONE database at on a
machine with LOTS of ram.  It doesn't need lots of CPU horsepower, or
even disk performance, as long as everything can fit into RAM.  Then,
set shared_buffers to 10-15% of the memory size, and let the OS do the
caching.  

One of the best performance tuning docs is here:

http://www.varlena.com/GeneralBits/Tidbits/perf.html

> > That statement is pretty telling.  You're new to PostgreSQL I'll
> > assume.  You'll need to read up on the periodic maintenance section of
> > the docs.
> 
> > Here ya go: 
> > http://www.postgresql.org/docs/8.1/interactive/maintenance.html
> 
> Yes, I have just started to use postgres so I need further experience.
> Thanks for the link.

We all started somewhere.  PostgreSQL is a pretty good place to start
learning databases.  

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

Re: [GENERAL] Remote Sync

2006-03-15 Thread Simon Riggs

On Wed, 2006-03-15 at 17:12 -0500, Daniel Blaisdell wrote:
> I am currently in a situation where I have a distributed application
> between a few remote nodes all connecting to a central database. I
> have been searching for a database replication or synchronization
> system that will allow a disconnected node to operate independently of
> the central database. The ultimate intention of this type of system is
> to have an application operate off of data locally and have those
> changes synced in the background. 
> 
> >From the research I've done, Slony doesn't support multi-master
> writers. PGCluster and ExtenDB seem to require all nodes be next to
> each other with the ultimate goal of local load balancing. 
> 
> Are there any solutions that come close to the requirements I'm after?
> 
> Thanks for any input,

Is this situation multi-master? If you partition your data at each
distributed node, then each is a single master to different data. You
can then make the central database the slave to multiple distributed
master databases.

Remote table1 -> central table1
Remote table2 -> central table2 etc

You can then link all the central tables together using:
- inheritance partitioning
- UNION ALL views

Best Regards, Simon Riggs


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

Re: [GENERAL] question about postgresql time intervals

Michael Glaesemann <[EMAIL PROTECTED]> writes:
> On Mar 15, 2006, at 23:39 , Linda wrote:
>> According to the SQL standard, shouldn't this work?
>> 
>> select '506:47:04'::interval day to second ;

> No one has implemented this in PostgreSQL yet.

It depends on what you define as "work".  8.1 says

regression=# select '506:47:04'::interval day to second ;
 interval
---
 506:47:04
(1 row)

8.0 and before say

regression=# select '506:47:04'::interval day to second ;
 interval
--
 21 days 02:47:04
(1 row)

because before 8.1 we didn't distinguish intervals of "1 day" and "24 hours"
as being different.  But the syntax has been accepted for a long time,
at least back to 7.0.

If there's some specific functionality you're after, you should say what
it is rather than expecting us to guess what you mean.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] What's a good default encoding?

2006-03-15 Thread Junaili Lie

I am wondering if somebody here can tell me the difference between
UTF-8 and SQL-ASCII, whether there are any benefits of converting
SQL-ASCII to UTF-8? If so, under what circumstances do we want to
convert to UTF-8?
Thanks,
On 3/15/06, Michael Schmidt <[EMAIL PROTECTED]> wrote:








Perhaps others can comment on encoding versus type of data.  I would 
add that the manner in which data are accessed may also be a 
consideration.  Specifically, UTF-8 is a good choice if one is going to use 
JDBC.
 
Michael Schmidt

[GENERAL] apparent loss of sys tables!! - help


Hi all,

Has anyone had this problem?

while in psql:

monashprotein=> \d alignment
ERROR:  column c2.reltablespace does not exist
monashprotein=> \d region
ERROR:  column c2.reltablespace does not exist
monashprotein=>
monashprotein=> \d
ERROR:  relation "pg_catalog.pg_roles" does not exist


Any pointers would be really appreciated!

Cheers
Noel
begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:[EMAIL PROTECTED]
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard


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

Re: [GENERAL] apparent loss of sys tables!! - help

2006-03-15 Thread Klint Gore

On Thu, 16 Mar 2006 12:33:26 +1100, Noel Faux <[EMAIL PROTECTED]> wrote:
> Has anyone had this problem?
> 
> while in psql:
> 
> monashprotein=> \d alignment
> ERROR:  column c2.reltablespace does not exist
> monashprotein=> \d region
> ERROR:  column c2.reltablespace does not exist
> monashprotein=>
> monashprotein=> \d
> ERROR:  relation "pg_catalog.pg_roles" does not exist
> 
> 
> Any pointers would be really appreciated!

are you using an 8.x psql against a 7.x database?

klint.

+---+-+
: Klint Gore: "Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless"   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+

---(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] apparent loss of sys tables!! - help





Klint Gore wrote:

  On Thu, 16 Mar 2006 12:33:26 +1100, Noel Faux <[EMAIL PROTECTED]> wrote:
  
  
Has anyone had this problem?

while in psql:

monashprotein=> \d alignment
ERROR:  column c2.reltablespace does not exist
monashprotein=> \d region
ERROR:  column c2.reltablespace does not exist
monashprotein=>
monashprotein=> \d
ERROR:  relation "pg_catalog.pg_roles" does not exist


Any pointers would be really appreciated!

  
  
are you using an 8.x psql against a 7.x database?
  

7.4.8

  
klint.

+---+-+
: Klint Gore: "Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless"   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+
  




begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:[EMAIL PROTECTED]
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard


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

Re: [GENERAL] Remote Sync

2006-03-15 Thread Daniel Blaisdell

This sounds like a good idea. I only see one potential problem. 

Say someone in the central office notices an error in a remote table,
they misentered a charge to be billed out. Using a replication system
such as Slony the table local to the worker in the central office will
be readonly. How would someone in billing, on a saturday with no one to
contact in the remote office, make such a change?

-Daniel
On 3/15/06, Simon Riggs <[EMAIL PROTECTED]> wrote:
On Wed, 2006-03-15 at 17:12 -0500, Daniel Blaisdell wrote:> I am currently in a situation where I have a distributed application> between a few remote nodes all connecting to a central database. I> have been searching for a database replication or synchronization
> system that will allow a disconnected node to operate independently of> the central database. The ultimate intention of this type of system is> to have an application operate off of data locally and have those
> changes synced in the background.>> >From the research I've done, Slony doesn't support multi-master> writers. PGCluster and ExtenDB seem to require all nodes be next to> each other with the ultimate goal of local load balancing.
>> Are there any solutions that come close to the requirements I'm after?>> Thanks for any input,Is this situation multi-master? If you partition your data at eachdistributed node, then each is a single master to different data. You
can then make the central database the slave to multiple distributedmaster databases.Remote table1 -> central table1Remote table2 -> central table2 etcYou can then link all the central tables together using:
- inheritance partitioning- UNION ALL viewsBest Regards, Simon Riggs

Re: [GENERAL] apparent loss of sys tables!! - help





On the client machine:
$ psql --version
psql (PostgreSQL) 8.1.3

At the host:
monashprotein=> select version();
 version
-
 PostgreSQL 7.4.8 on ia64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.3.5 (Debian 1:3.3.5-13)
(1 row)

Please reply to all, ensuring that the post is placed on the
pqsql-general mailing list.

Klint Gore wrote:

  On Thu, 16 Mar 2006 12:51:03 +1100, Noel Faux <[EMAIL PROTECTED]> wrote:
  
  
This is a multi-part message in MIME format.


Klint Gore wrote:


  On Thu, 16 Mar 2006 12:33:26 +1100, Noel Faux <[EMAIL PROTECTED]> wrote:
  
  
  
Has anyone had this problem?

while in psql:

monashprotein=> \d alignment
ERROR:  column c2.reltablespace does not exist
monashprotein=> \d region
ERROR:  column c2.reltablespace does not exist
monashprotein=>
monashprotein=> \d
ERROR:  relation "pg_catalog.pg_roles" does not exist


Any pointers would be really appreciated!


  
  are you using an 8.x psql against a 7.x database?
  
  

7.4.8

  
  
7.4.8 doesn't have tablespaces so the version of psql that you have must
be a 8.x?

if you run "psql --version", does it say 7.4.8 or 8.x.x?
if you execute the query "select version();" what does it say?

I've got 8.1 on my workstation with 7.4.7 on a development server (dev1)
and it says the following -

D:\>psql --version
psql (PostgreSQL) 8.1.0

D:\>psql -h dev1 template1
template1=# select version();
 version

-
 PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
(1 row)

template1=# \d
ERROR:  relation "pg_catalog.pg_roles" does not exist
template1=# \d pg_class
ERROR:  column c2.reltablespace does not exist
template1=#

klint.

+---+-+
: Klint Gore: "Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless"   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+
  




begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:[EMAIL PROTECTED]
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard


---(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] apparent loss of sys tables!! - help


Noel Faux wrote:

On the client machine:
$ psql --version
psql (PostgreSQL) 8.1.3

At the host:
monashprotein=> select version();
 version
-
 PostgreSQL 7.4.8 on ia64-unknown-linux-gnu, compiled by GCC gcc (GCC) 
3.3.5 (Debian 1:3.3.5-13)

(1 row)


That's the problem then - they need to be the same version (or at least 
the same major version - ie both 8.1.x or both 7.4.x).


You'll either need to downgrade the client or upgrade the server.

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

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

Re: [GENERAL] apparent loss of sys tables!! - help


Chris wrote:

Noel Faux wrote:

On the client machine:
$ psql --version
psql (PostgreSQL) 8.1.3

At the host:
monashprotein=> select version();
 version
- 

 PostgreSQL 7.4.8 on ia64-unknown-linux-gnu, compiled by GCC gcc 
(GCC) 3.3.5 (Debian 1:3.3.5-13)

(1 row)


That's the problem then - they need to be the same version (or at 
least the same major version - ie both 8.1.x or both 7.4.x).


You'll either need to downgrade the client or upgrade the server.


Ok, it's not a major issue! We're in the process of upgrading the server :)

Cheers
Noel
begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:[EMAIL PROTECTED]
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard


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

Re: [GENERAL] What's a good default encoding?

"Junaili Lie" <[EMAIL PROTECTED]> writes:
> I am wondering if somebody here can tell me the difference between
> UTF-8 and SQL-ASCII, whether there are any benefits of converting
> SQL-ASCII to UTF-8?

SQL_ASCII isn't really an encoding; it's more like a declaration of
ignorance.  If the encoding is set to SQL_ASCII, the backend will store
any high-bit-on data you send it, and return it without any sort of
conversion.

If you are dealing with data beyond the 7-bit ASCII set, it's probably a
really bad idea to be using the SQL_ASCII setting, because the database
won't give you any help at all in checking for bad data or converting
between the encodings wanted by different client programs.  There are a
few situations where this is what you want, but I think most people are
better off picking a specific encoding.

regards, tom lane

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

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

[GENERAL] Reprise of Oracle decode functionality...now with nifty plperlu (and two cupholders)

2006-03-15 Thread Mike Adams

I've been lurking on the pgsql-* lists for about a month now and have 
decided to quit being a "wall flower".


Looking through the achives, I've noticed a fair number of threads about 
Oracle compatibility functions: predominantly for decode().  I've even 
seen and (quickly) looked at one implemented in C as an extension.


I've decided to toss my perl hat into the ring.
I use multiple schema to hold my procs:
  schema usage
  -- -
  pl plperl,plperlu procedures
  java   pljava,pljavau procedures
  sqlpgSQL,SQL procedures

Comments? Suggestions? Testimonials?

Enjoy:

(OBLIGATORY BLURB)
Code is presented AS IS, with NO WARRANTY of fitness for purpose. May 
cause data loss, hair loss, may contain nuts.


/**/

CREATE OR REPLACE FUNCTION pl.decode(selector text, clauses text)
  RETURNS text AS
$BODY$

use Safe;

my $vault;

# get / setup a safe "vault"  from / in %_SHARED to reduce function
# start up time on a per session level

if ( exists( $_SHARED{pl_vault} ) && defined( $_SHARED{pl_vault} ) ){

$vault = $_SHARED{pl_vault};
#elog( NOTICE , "plperlu: Preloaded safety vault being used." );
}
else {
# setup a safe vault using the same parameters as the SAFE_OK
# macro in postgresql's
# http://developer.postgresql.org/cvsweb.cgi/
# pgsql/src/pl/plperl/plperl.c?rev=1.105

$vault = Safe->new;
$vault->permit_only( qw/ :default
 :base_math
!:base_io
 time
 sort
   / );

$_SHARED{pl_vault} = $vault;
#elog( NOTICE , "plperlu: Setting up session safety vault.");
}

my $selector   = $_[0];
my @in_clauses = split( /,,/ , $_[1] );

# reject @in_clauses argument if it doesn't contain an odd number of
# entries: ie -
#( '>10' , 'return#1' , 'final else' ) or
#( '>10' , 'return#1' , '<10' , 'return#2' , 'final else' )
#is OK
#( '>10' , 'return#1' ) is not

die(  "pl.decode(): invalid clause argument,"
. " the number of entries was not odd.\n")
unless ( scalar( @in_clauses ) % 2 );

my $final_else = pop @in_clauses;
my $retval = undef;
my $have_match = undef;

ITERATIONS:
while ( @in_clauses ){
my $match_clause = shift @in_clauses;
my $then_clause  = shift @in_clauses;
my $result   = $vault->reval( $selector . $match_clause );

if ( my $error = $@ ){
# safe reval error...clean up the error message then
# elog() and ignore it, then move on and try the next
# set of match/result clauses...

$error =~ s/ at line.+//;
$error =~ s/trapped.+/deemed unsafe/;
chomp $error;
elog( NOTICE , "pl.decode(): potentially dangerous "
 . "operation found, "
 . $error
 . ", skipping clause..."
 );
next ITERATIONS;
}
if ( $result ){
# we have the winner...set $retval and bail out...
# we only grab the first true result...

$retval = $then_clause;
$have_match = "yes";
last ITERATIONS;
}
# no $result? oh well try the next set...
}

if ( defined( $have_match ) ){
# last check to see if we've matched anything...
# and if so return it...accounting for the case where
# the wanted return is NULL...

if ( $retval =~ m/^ (?: null || undef ) \( \) $ /ix ) {
$retval=undef;
}
return $retval;
}

# if we get here we are returning the "default" result value
return $final_else;

$BODY$
  LANGUAGE 'plperlu' IMMUTABLE SECURITY DEFINER;
COMMENT ON FUNCTION pl.decode(selector text, clauses text) IS '
#
##   decode( selector text , clauses text )
##  plperlu rendition of Oracle''s decode() function.  Takes 2 args:
##  the item to check, and a double comma (,,) separated string
##  listing of items to match and items to return if the match is
##  successful. The last entry in the string is the final "else"
##  return value.  The match sections may include boolean
##  operations.
##
##  USE DOLLAR QUOTING to setup the test/result string, it WILL save
##  you much hair pulling.
##
##If you want a return item to be NULL for an option, use one of the
##   following (case INSENSITVE) return values:
## null()   or
## undef()
##
##   provides its own Safe.pm compartment for the reevalution of the
##   match clauses
##
###

Re: [GENERAL] Reprise of Oracle decode functionality...now with nifty

2006-03-15 Thread Mike Adams


Ack! Should at least pull the current version from the database:

This version also correctly checks the "default" return value for a 
possible return of a wanted NULL value.  (The part just ahead of "return 
$final_else;".


/***/

CREATE OR REPLACE FUNCTION pl.decode(selector text, clauses text)
  RETURNS text AS
$BODY$

use Safe;

my $vault;

# get / setup a safe "vault"  from / in %_SHARED to reduce function 
start up time on a per session level


if ( exists( $_SHARED{pl_vault} ) && defined( $_SHARED{pl_vault} ) ){

$vault = $_SHARED{pl_vault};
#elog( NOTICE , "plperlu: Preloaded safety vault being used." );
}
else {
	# setup a safe vault using the same parameters as the SAFE_OK macro in 
postgresql's
	# 
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/plperl.c?rev=1.105


$vault = Safe->new;
$vault->permit_only( qw/ :default :base_math !:base_io time sort / );

$_SHARED{pl_vault} = $vault;
#elog( NOTICE , "plperlu: Setting up session safety vault.");
}

my $selector   = $_[0];
my @in_clauses = split( /,,/ , $_[1] );

#reject @in_clauses argument if it doesn't contain an odd number of 
entries: ie -
#( '>10' , 'return#1' , 'final else' ) or ( '>10' , 
'return#1' , '<10' , 'return#2' , 'final else' )  is OK

#( '>10' , 'return#1' ) is not

die   "pl.decode(): invalid clause argument, the number of entries was 
not odd.\n"

unless ( scalar( @in_clauses ) % 2 );

my $final_else = pop @in_clauses;
my $retval = undef;
my $have_match = undef;

ITERATIONS:
while ( @in_clauses ){
my $match_clause = shift @in_clauses;
my $then_clause  = shift @in_clauses;
my $result   = $vault->reval( $selector . $match_clause );

if ( my $error = $@ ){
		# safe reval error...clean up the error message then elog() and ignore 
it, then move on and try the next set of match/result clauses...

$error =~ s/ at line.+//;
$error =~ s/trapped.+/deemed unsafe/;
chomp $error;
elog( NOTICE , "pl.decode(): potentially dangerous operation found, 
"
 . $error
 . ", skipping clause..."
 );
next ITERATIONS;
}
	if ( $result ){ # we have the winner...set $retval and bail out...we 
only grab the first true result...


$retval = $then_clause;
$have_match = "yes";
last ITERATIONS;
}
# no $result? oh well try the next set...
}

if ( defined( $have_match ) ){
# last check to see if we've matched anything...
# and if so return it...accounting for the case where
# the wanted return is NULL...
if ( $retval =~ m/^ (?: null || undef ) \( \) $ /ix ) {
$retval=undef;
}
return $retval;
}

# if we get here we are returning the "default" result value
# also accounting for the case where the wanted return is NULL...
if ( $final_else =~ m/^ (?: null || undef ) \( \) $ /ix ) {
   $final_else=undef;
}

return $final_else;

$BODY$
  LANGUAGE 'plperlu' IMMUTABLE SECURITY DEFINER;
COMMENT ON FUNCTION pl.decode(selector text, clauses text) IS '
#
##   decode( selector text , clauses text )
##   plperlu rendition of Oracle''s decode() 
function.  Takes 2 args:
##   the item to check, and a double comma (,,) 
separated string
##   listing of items to match and items to return 
if the match is successful.
##   The last entry in the string is the final 
"else" return value.  The match
##   sections may include boolean operations.  USE 
DOLLAR
##   QUOTING to setup the test/result string, it 
WILL save you much hair pulling.

##
##   If you want a return item to be NULL for an 
option, use one of the

##   following (case INSENSITVE) return values:
## null()   or
## undef()
##
##   provides its own Safe.pm compartment for the reevalution of the 
match clauses

##
#
';


---(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 on Windows not starting

2006-03-15 Thread Oisin Glynn


Chris Travers wrote:

I have a customer who is having issues starting PostgreSQL 8.1 on 
Windows.  It worked for a while and now doesn't appear to be running.  
I thought it was probably a stale pidfile, but had him search and 
could not find it.  Is the pid information still in a pidfile or is it 
in the registry somewhere on Windows?  I am suggesting that he try to 
start the service manually and look for error messages.  But in the 
mean time I thought I would ask.


Best Wishes,
Chris Travers
Metatron Technology Consulting




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

Possile reason I hit a while ago was the account that was created for 
the PostgreSQL service was set to need a PWD change at login and so the 
service would not start,  I was looking in all sorts of other areas 
before I spotted it, I think it was due to some domain policy as I had 
not seen it before or since. Or perhaps the account pwd is set to expire.


Oisin

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

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

Re: [GENERAL] Problems with Postgres and TCP/IP Protocol


Paulo wrote:
I installed recently the last version of Postgres, but I can't get to 
connect using any tcp/ip port. I tried many. Few time ago I used the 
NLite program and I remove the Secondary Logon service and the program 
always asks by this service. So, I installed no service. I tried no 
firewalls too and I had the same problem.


When I tries with pgadmin appear the hourglass and stay so. Thus I needs 
to close the window of program.


I tried through psql and not worked again. The cursor is blinking below 
of command and nothing more happens.


Does it work if you don't try it through tcp?

psql -l

instead of

psql -l -h ip

?

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

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

Re: [GENERAL] \copy combine with SELECT

2006-03-15 Thread Qingqing Zhou


""jia ding"" <[EMAIL PROTECTED]> wrote

> I tried:
> select id, name  into table2   from table1;
>  \copy table2 to filename.txt
> in order to export 2 columns from table1 to a file.
>
> But, I am thinking, if there is a command can combine these two
> command together?

Notice that COPY command can be used like this:
COPY  ['(' columnList ')'] FROM/TO [WITH options]

Regards,
Qingqing




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

Re: [GENERAL] hi problem with installing postgresql8.1

2006-03-15 Thread Luckys


Firstly, the existence of the Path would be there from previous installation, of which PG thinks data is already present.
Secondly you should have logged in as another user and not administrator, e.g postgres which can be even a part of domain.
 
Luckys
 
Data directory error:  The specified data directory is not empty  
   If you have an existing database with teh same major version number,you do not need to initialise a database cluster.   If you have an existing database with the same major version you need to backup your old database and create a new one. 
   why this warning actually arises.If  I install by unchecking the initialise database cluster then the next three steps of installation procedure is not shown.It goes directly installation and starts installation after some time a fatal error arises saying accont not matching 
    and rolls back.i have tried it with lots of account names and lot of super user names.The steps i was talking about are as per the given link.  
http://pginstaller.projects.postgresql.org    Can any one give the solution to this problem.Please suggest solution to overcome this problem ASAP.Thanks in advance,Venu.

[GENERAL] invalid UTF-8 byte sequence detected

Based on a couple of other posts, I think I'm out of luck, but I'm hoping
something might have changed recently.

I'm loading a very high volume of data with COPY using libpq - about 100+
million rows per day. The problem is that the COPY sometimes aborts with

  invalid UTF-8 byte sequence detected

I'd prefer not to add to the overhead by pre-validating every string, since
PostgreSQL validates it already.  Is there a way to get the server to
replace invalid characters with something like blank instead of generating a
fatal error?

Wes



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

Re: [GENERAL] invalid UTF-8 byte sequence detected

2006-03-15 Thread mike

If you don't care to store those characters then maybe you should change
the database character set to use SQL_ASCII instead of UTF-8.  I believe
ASCII will quietly discard those characters when converting from UTF-8.

Mike

 On Wed, 2006-03-15 at 23:20 -0600, Wes wrote:
> Based on a couple of other posts, I think I'm out of luck, but I'm hoping
> something might have changed recently.
> 
> I'm loading a very high volume of data with COPY using libpq - about 100+
> million rows per day. The problem is that the COPY sometimes aborts with
> 
>   invalid UTF-8 byte sequence detected
> 
> I'd prefer not to add to the overhead by pre-validating every string, since
> PostgreSQL validates it already.  Is there a way to get the server to
> replace invalid characters with something like blank instead of generating a
> fatal error?
> 
> Wes
> 
> 
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly

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

Re: [GENERAL] invalid UTF-8 byte sequence detected

On 3/15/06 11:42 PM, "mike" <[EMAIL PROTECTED]> wrote:

> If you don't care to store those characters then maybe you should change
> the database character set to use SQL_ASCII instead of UTF-8.  I believe
> ASCII will quietly discard those characters when converting from UTF-8.

I thought about that, but I do want to allow UTF-8 to be stored.  I just
want it to replace illegal characters with some valid character so that
invalid records will load.   Even if I preprocess the data, I can do no more
than that.

Wes

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

Re: [GENERAL] invalid UTF-8 byte sequence detected

2006-03-15 Thread Michael Glaesemann



On Mar 16, 2006, at 14:42 , mike wrote:

If you don't care to store those characters then maybe you should  
change
the database character set to use SQL_ASCII instead of UTF-8.  I  
believe
ASCII will quietly discard those characters when converting from  
UTF-8.


SQL_ASCII is *not* ASCII. See the "What's a good default encoding?"  
thread on this same list from today.


http://archives.postgresql.org/pgsql-general/2006-03/msg00685.php

I don't believe it will discard anything on import if the database is  
SQL_ASCII encoded.


Michael Glaesemann
grzm myrealbox com




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

Re: [GENERAL] Indexes on array columns

2006-03-15 Thread karly

On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote:
> Hi,
> 
> Is it possible to put an index on an array column?

Apparently yes (I just did it as a test).  However, consider the
following from the manual.

   Tip:  Arrays are not sets; searching for specific array elements
   may be a sign of database misdesign. Consider using a separate
   table with a row for each item that would be an array element. This
   will be easier to search, and is likely to scale up better to large
   numbers of elements.[1]

Arrays are attractive, but it seems they most often aren't the best
solution. FOr instance, I beleive I read somewhere that the index
will be on the whole array, and the individual elements are not
indexed, so 

   WHERE ary = {foo,bar} 

might benefit from your index, but 

   WHERE 'foo' = ANY(ary)

probably wouldn't.  At least that's the impression I got from
reading the archives of this list.  I haven't done any testing of
it.

-karl

1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491

> 
> Thanks,
> 
> 
> Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]
> 
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB  T2G 0V9
> 
> http://www.clickspace.com
> 

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

Re: [GENERAL] What's a good default encoding?

2006-03-15 Thread Harald Armin Massa

Good default encoding:does somebody NOT agree that UTF8 is quite a recommendation, at least for all the people without Korean, Japanese and Chinese Chars? I know, that's at maximum 2/3 of our potential user base, but better then nothing.
Maybe we could even "suggest" UTF8 in the "getting started" (i.e. the windows installer initdb screen, or other default installations) Sth. like "if you do not know better, take utf8"
Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-When I visit a mosque, I show my respect by taking off my shoes. I follow the customs, just as I do in a church, synagogue or other holy place. But if a believer demands that I, as a nonbeliever, observe his taboos in the public domain, he is not asking for my respect, but for my submission. And that is incompatible with a secular democracy.

Re: [GENERAL] Indexes on array columns

2006-03-15 Thread Oleg Bartunov


contrib/intarray might help you

Oleg
On Wed, 15 Mar 2006, [EMAIL PROTECTED] wrote:


On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote:

Hi,

Is it possible to put an index on an array column?


Apparently yes (I just did it as a test).  However, consider the
following from the manual.

  Tip:  Arrays are not sets; searching for specific array elements
  may be a sign of database misdesign. Consider using a separate
  table with a row for each item that would be an array element. This
  will be easier to search, and is likely to scale up better to large
  numbers of elements.[1]

Arrays are attractive, but it seems they most often aren't the best
solution. FOr instance, I beleive I read somewhere that the index
will be on the whole array, and the individual elements are not
indexed, so

  WHERE ary = {foo,bar}

might benefit from your index, but

  WHERE 'foo' = ANY(ary)

probably wouldn't.  At least that's the impression I got from
reading the archives of this list.  I haven't done any testing of
it.


-karl



1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491



Thanks,


Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com





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



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [GENERAL] invalid UTF-8 byte sequence detected

On 3/16/06 12:13 AM, "Michael Glaesemann" <[EMAIL PROTECTED]> wrote:

> SQL_ASCII is *not* ASCII. See the "What's a good default encoding?"
> thread on this same list from today.
> 
> http://archives.postgresql.org/pgsql-general/2006-03/msg00685.php
> 
> I don't believe it will discard anything on import if the database is
> SQL_ASCII encoded.

That might be worth a shot.  I don't really understand the ramifications,
though, especially given Tom's warning.  I guess as long as I don't care
about things like sort order for those fields, it may not matter much.

Wes

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

Re: [GENERAL] invalid UTF-8 byte sequence detected

2006-03-15 Thread Michael Glaesemann



On Mar 16, 2006, at 15:39 , Wes wrote:

That might be worth a shot.  I don't really understand the  
ramifications,
though, especially given Tom's warning.  I guess as long as I don't  
care

about things like sort order for those fields, it may not matter much.


I hope you didn't take my comments as a suggestion: I think you  
*should* preprocess your data and use UTF8 as the default encoding  
(or another encoding that includes all of the characters you hope to  
use) for your database.


Michael Glaesemann
grzm myrealbox com




---(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 on Windows not starting

2006-03-15 Thread Harald Armin Massa

Chris,i dove through all sorts of pain with windows security and PostgreSQL services while postgresql on win32 was in beta. Even deployed some big application to > 30 computers installing win32 PostgreSQL in beta. So, some areas where I had things to learn:
1.) group policies. On win32 computers within a domain, as a local administrator you are quite able to create (local) users with all sort of privileges; esp. if you do it via the api and not with GUI tools from MS.
Group Policies will NOT interfer with the creation of a user that is outside the policie! ... but it will silently roll his priviliges back to "normal", whatever "normal" is in that domain.
Most common error: the PostgreSQL service user needs the "logon as service" privilege. No normal user needs it, so most "normal user" policies (esp. the default by Microsoft) strips this privilege. On a "random time basis" - that is, not with every logon, but every 2 to 7 days. Have fun with bug hunting!
2.) Problems in the field of socketsPostgreSQL spawns (or forks?) a new process to deal with every connection. The master has to pass an open connection socket to this child. SOME - firewalls- voice over ip
- adult service USB tokens- viral scanners- computer telephony integration softwarescrew up the Windows tcp/ip stack. We spend some nights in repairing ( that is: me crying and testing, Magnus patching and compiling) the "passing of sockets even if the tcp/ip stack is screwed"; but maybe, even maybe your customer found a new way to destroy it?
3.) read the event log4.) read the log in data/pg_logBest wishesHarald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart
0173/9409607-PostreSQL - works as documented

Re: [GENERAL] invalid UTF-8 byte sequence detected