Re: [GENERAL] Problem : Pound to Hash sign

[Doug McNaught]

[EMAIL PROTECTED] writes:

> Hi there,
> 
> This is Sanjay from Dublin, Ireland.  I have a query related to JDBC.
> 
> In the web based application we have designed we have used Oracle 8i to
> store the data and Java as a front end.
> 
> When a user enters some description against a field containing a '£' sign
> and commits the record; it gets stored as a '#' sign and not the '£'.  I
> think this problem may not be in Oracle's area as the same works fine when
> record is being inserted through SQL or Pl/sql.

This isn't an Oracle mailing list.  Call your Oracle support line.

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] kinda newbie - ish question

[Marko Kreen]

On Mon, Oct 08, 2001 at 07:31:36PM -0700, Mike Judkins wrote:
> Im trying to insert a record with a php script. I insert a NULL value
> to get my auto increment unique key to automatically populate as
> usual. Then I want to be able to insert another value in this same row
> which is a URL. This URL is basically a path plus a filename which I
> want to have the exact same name as the unique key that was just
> generated for the row. Is there a quick way is SQL to do this or will
> I have to handle it in my script?

update tbl set fn = fileprefix || currval('tbl_id_seq')
where id = currval('tbl_id_seq')

This must be in the same TX as first insert.

-- 
marko


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Where to count

[H. Wade Minter]

The COUNT tells me how many times a particular combination of source IP,
destination IP, and service appears in the logs.  The ORDER BY puts it in
decending order, and the LIMIT only shows me the top 25/50/etc. entries.

It works like I want it to - I'm just checking to see if this is the most
efficient way of doing things.  Like, should I make an index on something
to accomplish this goal.

--Wade


On Wed, 10 Oct 2001, Mitch Vincent wrote:

> If you use LIMIT, count(*) is going to return that limit even if there are
> more than the specified limit.
>
> Why are you going a LIMIT here if it's the count you're looking for?
>
>
> - Original Message -
> From: "H. Wade Minter" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, October 10, 2001 3:46 PM
> Subject: [GENERAL] Where to count
>
>
> > I'm running a DB query on a database of firewall log entries (right now
> > around 700k rows).  What I want to do is pull out some common entries, as
> > well as the number of times that they occur in the table.
> >
> > Right now, I'm doing a query like:
> >
> > select source,destination,service,count(*) FROM logs WHERE action='$type'
> > GROUP BY source,destination,service ORDER BY count DESC LIMIT $num;
> >
> > This is a little more advanced than I'm used to doing, so I'm wondering if
> > that query is the best way to get that data, or if there's another way of
> > doing it.
> >
> > Thanks,
> > Wade
> >
> > --
> > Do your part in the fight against injustice.
> > Free Dmitry Sklyarov!  http://www.freesklyarov.org/
> > Fight the DMCA!  http://www.anti-dmca.org/
> >
> >
> > ---(end of broadcast)---
> > TIP 3: 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 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

-- 
Do your part in the fight against injustice.
Free Dmitry Sklyarov!  http://www.freesklyarov.org/
Fight the DMCA!  http://www.anti-dmca.org/


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] kinda newbie - ish question

[Sykora, Dale]

Mike,   
Perhaps you could just insert the url into the record and then
use a string function to return "url"+"id" when needed.  This would
eliminate the extra insert and save space in the row.

Dale


> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 08, 2001 9:32 PM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] kinda newbie - ish question
> 
> 
> hi all,
> 
> Im trying to insert a record with a php script. I insert a NULL value
> to get my auto increment unique key to automatically populate as
> usual. Then I want to be able to insert another value in this same row
> which is a URL. This URL is basically a path plus a filename which I
> want to have the exact same name as the unique key that was just
> generated for the row. Is there a quick way is SQL to do this or will
> I have to handle it in my script?
> 
> Thanks for helping!
> 
> Mike
> 
> ---(end of 
> broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

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

Re: [GENERAL] database field "pointer"

[Doug McNaught]

Jeff Davis <[EMAIL PROTECTED]> writes:

> I was wondering is there is a good method to make a database field a 
> pointer, similar to C. Here is an example I made up of why this could be 
> useful:
> Suppose I have a table 'coworkers' with 2 email address fields: work_email 
> and home_email. It would be useful to have another field that was something 
> like 'preferered_email' that pointed to one or the other. Then, updates would
> only need to happen once, and it would be easy to change back and forth. 
> Tell me if there is some better, more sensible method to accomplish this 
> task.

The "SQLish" way to do this would be:

create table email (
id serial primary key,
user_id integer references my_user(id),
addr text not null
);

create table my_user (
id serial primary key,
name text not null,
preferred_email integer references email(id)
);

To get a user's preferred email:

select email.addr from email, my_user 
  where my_user.name = 'Joe Blow'
and email.id = my_user.preferred_email;

To get all emails for a user (so you can select a new preferred
email):

select email.id, email.addr from email, my_user
  where my_user.name = 'Joe Blow' 
and email.user_id = my_user.id;

Then,

update my_user set preferred_email =  
  where name = 'Joe Blow';
  
There are probably better ways to do this but this is the one that
sprang to mind.

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

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

Re: [GENERAL] kinda newbie - ish question

[Doug McNaught]

[EMAIL PROTECTED] (Mike Judkins) writes:


> to get my auto increment unique key to automatically populate as
> usual. Then I want to be able to insert another value in this same row
> which is a URL. This URL is basically a path plus a filename which I
> want to have the exact same name as the unique key that was just
> generated for the row. Is there a quick way is SQL to do this or will
> I have to handle it in my script?

Why not leave the unique ID part out of the stored URL and then
combine them when you fetch from the DB?  It would be something like

SELECT urlprefix || id::text FROM mytable WHERE 

(That's assuming the ID is non-text, like a SERIAL)

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Contents of greatbridge.com?

[Nick Fankhauser]

try this:

http://gborg.postgresql.org

-Nick

-
Nick Fankhauser 

Business: 
[EMAIL PROTECTED]  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop  - Court records at your fingertips - http://www.doxpop.com/
 
Personal: 
[EMAIL PROTECTED]   http://www.fankhausers.com 

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

Re: [GENERAL] triggers

[Thalis A. Kalfigopoulos]

> I started looking at the trigger support in PostgreSQL and found what I
> needed except I'm not sure how to write the "body" of the trigger.  In
> Oracle I write PL/SQL but it seems I may have to write C code on the
> PostgreSQL side.  Is this true?  Is there a PostgreSQL procedural language
> that I can write the body in?

Why? What's wrong with plpgsql?

--thalis


---(end of broadcast)---
TIP 3: 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] database field "pointer"

[Jeff Davis]

I was wondering is there is a good method to make a database field a 
pointer, similar to C. Here is an example I made up of why this could be 
useful:
Suppose I have a table 'coworkers' with 2 email address fields: work_email 
and home_email. It would be useful to have another field that was something 
like 'preferered_email' that pointed to one or the other. Then, updates would 
only need to happen once, and it would be easy to change back and forth. 
Tell me if there is some better, more sensible method to accomplish this 
task.

Thanks,
Jeff Davis

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Contents of greatbridge.com?

[Vince Vielhaber]

On Tue, 9 Oct 2001, Tommy wrote:

> >From what I understand they went out of business. I have not seen the site
> morrored anywhere.
>
> I wonder what this means for the future of PostgreSQL?

You must be new here 'cuze this has been covered many times in the
last few weeks.

PostgreSQL is not now, nor has it ever been a product or property of
Great Bridge.  PostgreSQL, Inc has been around longer than Great Bridge
and is still running just fine.  In fact there's currently nine companies
listed on the PostgreSQL website that do the exact same thing as GB,
among them is PostgreSQL, Inc, and RedHat.

The projects website is, or is about to be available at

   http://gborg.postgresql.org

So to answer your question about what the future is for PostgreSQL?  Just
as bright as ever.

Vince.

>
> Tommy
>
> "reywob" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]...
> > Hi,
> >
> > Does anyone know if the contents of Greatbridge.com is anywhere?
> > Someone told me that they had a good article comparing PostgreSQL to
> > other databases, which I would like to read - except that the site has
> > disappeared :-(
> >
> > Thanks,
>
>
>
> ---(end of broadcast)---
> TIP 3: 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
>

-- 
==
Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net
 56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directoryhttp://www.camping-usa.com
   Online Giftshop Superstorehttp://www.cloudninegifts.com
==




---(end of broadcast)---
TIP 3: 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] Conditional Adding to a Table

[Aasmund Midttun Godal]

just add a unique constraint on the things you want to be unique...
On Wed, 10 Oct 2001 14:37:09 -0600, Glenn Sullivan <[EMAIL PROTECTED]> 
wrote:
> Greetings,
> 
> I need to check a table to see if an item already exist in the table.
> It if does not, then I want to add it.  Unfortunately, multiple processes
> (Solaris system) can be running at the same time.  Sometimes, one process
> checks to see if an item exist and it does not. At the same time another
> process checks to see if the same item exist and it does not.  Then both,
> add the item to the table.  This is, of course, not what I want.
> 
> I cannot seem to figure out how to do a conditional test to do something
> like this.  If I could, then the whole operation could fit in one transaction
> with locking.
> 
> Any ideas?
> 
> Thanks,
> Glenn
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Aasmund Midttun Godal

[EMAIL PROTECTED] - http://www.godal.com/
+47 40 45 20 46

---(end of broadcast)---
TIP 3: 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] database field "pointer"

[Aasmund Midttun Godal]

CREATE TABLE coworkers (
id serial PRIMARY KEY,
name varchar(63)
);

CREATE TABLE email_types (
type VARCHAR(10) PRIMARY KEY
);

INSERT INTO email_types VALUES ('home');
INSERT INTO email_types VALUES ('work');

CREATE TABLE emails (
owner INTEGER REFERENCES coworkers,
type INTEGER REFERENCES email_types, --You could have a CHECK 
--instead offcourse.
email VARCHAR(127) PRIMARY KEY,
preferred BOOLEAN,
UNIQUE(owner, preferred),
UNIQUE(type, owner)
);

No guarantees, I have not run it, but I think it should work.


On Wed, 10 Oct 2001 13:39:31 -0700, Jeff Davis <[EMAIL PROTECTED]> wrote:
>   I was wondering is there is a good method to make a database field a 
> pointer, similar to C. Here is an example I made up of why this could be 
> useful:
>   Suppose I have a table 'coworkers' with 2 email address fields: work_email 
> and home_email. It would be useful to have another field that was something 
> like 'preferered_email' that pointed to one or the other. Then, updates would 
> only need to happen once, and it would be easy to change back and forth. 
>   Tell me if there is some better, more sensible method to accomplish this 
> task.
> 
> Thanks,
>   Jeff Davis
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Aasmund Midttun Godal

[EMAIL PROTECTED] - http://www.godal.com/
+47 40 45 20 46

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [GENERAL] ERROR: Unrecognized language specified ...

[Stephan Szabo]

You'll need to use createlang to add the handler.
IIRC, it's createlang  

On Tue, 9 Oct 2001, Kevin HaleBoyes wrote:

> I'm creating a function (for use in a trigger) from the example in the 
> documentation and encountered an error.  If I type the following at
> the psql prompt (database and user are opf):
> 
> opf=> create function opf_user_fillin() returns opaque as '
> opf'> begin
> opf'> new.opf_user_id := nextval(opf_user_sequence);
> opf'> return new;
> opf'> end;
> opf'> '
> opf-> language 'plpgsql'
> opf-> ;
> 
> I get the following error:
> 
> ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. 
> Recognized languages are sql, C, internal and the created procedural languages.
> 
> Can I enable the plpgsql language on my system?  If so, how?  If not,
> what are my options (ie, what do I have to do to get plpgsql support)?
> 
> I'm pretty new to PostgreSQL but no stranger to compiling systems from source
> (or writing the source for that matter).  I'm running a RedHat Linux 7.1 system
> with the PostgreSQL that comes with the OS.


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [GENERAL] Error Codes?

[Fran Fabrizio]

> Rob Arnold wrote:
>
> > Look at $db->errstr  That has the text version of the error code.
> >
> > --rob

Yes, but I want to know about the codes, not the text.  If the codes exist,
they are easier to work with than the text.

-Fran


---(end of broadcast)---
TIP 3: 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] Performance problem with 50,000,000 rows

[Feite Brekeveld]

David Link wrote:

Just, reading it and I always enter these kind of queries like:

select   i.isbn,
 t.vendor,
 i.store,
 i.qty
from bk_inv i,
 bk_title t
where
   t.vendor = '01672708' and
   i.isbn = t.isbn;

Don't know if it makes a difference but I can imagine that it could because
the t.vendor = '...' limits the possibilities in the first stage.

Regards,

Feite

> I'm new to PG but this just seems wrong.  Can someone take a look:
>
>   .---. .---.
>   | bk_inv| | bk_title  |
>   |---| |---|
>   | isbn  |<--->| isbn  |
>   | store | | vendor|
>   | qty   | |   |
>   | week  | `---'
>   |   |  2,000,000 recs
>   `---'
>50,000,000 recs
>
>   Actual record numbers:
> bk_inv  : 46,790,877
> bk_title:  2,311,710
>
> VENDOR REPORT
>
>   A list of Inventory items, for any one given vendor (e.q. 01672708)
>
> select   i.isbn,
>  t.vendor,
>  i.store,
>  i.qty
> from bk_inv i,
>  bk_title t
> wherei.isbn = t.isbn
> and  t.vendor = '01672708' ;
>
> This query should be instantaneous.  Granted that's 50 million rows, but
> I have created an index on the isbn column for both tables.
> After about 25 minutes (on 4 processor Del 6300 with 1GB Memory) it
> spits out:
>
> ERROR:  Write to hashjoin temp file failed
>
> tiger=# explain select * from bk_inv i, bk_title t where i.isbn = t.isbn
> and t.vendor  ='5029';
> NOTICE:  QUERY PLAN:
>
> Merge Join  (cost=0.00..11229637.06 rows=2172466 width=72)
>   ->  Index Scan using bk_title_isbn_idx on bk_title t
> (cost=0.00..390788.08 rows=107331 width=24)
>   ->  Index Scan using bk_inv_isbn_idx on bk_inv i
> (cost=0.00..10252621.38 rows=46790877 width=48)
>
> BIG COST!
>
> These explain queries show the existance of the indexes and give small
> costs:
>
> tiger=# explain select * from bk_title where isbn = '5029';
> NOTICE:  QUERY PLAN:
>
> Index Scan using bk_title_isbn_idx on bk_title  (cost=0.00..4.90 rows=1
> width=24)
>
> tiger=# explain select * from bk_inv where isbn = '0897474228';
> NOTICE:  QUERY PLAN:
>
> Index Scan using bk_inv_isbn_idx on bk_inv  (cost=0.00..225.53 rows=55
> width=48)
>
> Note.  Same tables, same query returns instantaneously with Oracle 8.1.
> What I am hoping to show is that Postgres can do our job too.
>
> Any help on this much obliged.  (Yes I ran vacuum analyze).
>
> David Link
> White Plains, NY
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Feite Brekeveld
[EMAIL PROTECTED]
http://www.osiris-it.nl




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

Re: [GENERAL] PG mailing list problems (was Re: Support issues)

[Jim Caley]

Has anyone heard any more about this problem?  I also haven't gotten anything
from pgsql-general since Oct. 1.  The other lists seem to be working fine for
me.

I tried re-subscribing, plus sending an e-mail to pgsql-general-admin.  The
re-subscribe seemed to go normally and successfully, but I still haven't
received any postings.  I haven't heard back from the admin yet.

I got the info below from the archives.  I'll have to check back there to even
see if this post made it through, I guess. :(

Thanks,
Jim
--

> Re: PG mailing list problems (was Re: Support issues)
> 
> 
> 
>  From: Tom Lane <[EMAIL PROTECTED]> 
>  To: Frank Joerdens <[EMAIL PROTECTED]> 
>  Subject: Re: PG mailing list problems (was Re: Support issues) 
>  Date: Sun, 07 Oct 2001 10:02:46 -0400 
> 
> 
> 
> Frank Joerdens <[EMAIL PROTECTED]> writes:
> > * Tom Lane ([EMAIL PROTECTED]) [011005 13:58]:
> >> Marc, I think there is something rotten in Denmark: certain mail lists
> >> are not getting sent out to certain addresses, looks like.
> 
> > For me it only affects pgsl-general at the moment. The last mail I
> > received from there is dated 30 September. Mails form pgsl-hackers, sql,
> > interfaces and php are OK. I've seen "bursty" behaviour before too.
> 
> > The only thing that is non-standard or queer (I don't really know
> > whether any mailers still pay attention to it) about our mail server is
> > that reverse lookup for it's IP address does not work at the moment
> 
> My RDNS is busted too, but I'm not having any trouble receiving PG mail.
> So the problem is probably not that...
> 
> regards, tom lane
>

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [GENERAL] VACUUM, 24/7 availability and 7.2

[Jeffrey W. Baker]

On Wed, 10 Oct 2001 [EMAIL PROTECTED] wrote:

> Just to keep things in perspective, how large are your current databases, and
> what do you or the company consider to be a signficant length of time?  Right
> now I have a development database with just a few thousand records of test data,
> and vacuum takes just a very few seconds a day.  I think I recall hearing on
> this list of it taking a minute or three for databases several gigabytes in
> size.  For some sites this would be tolerable, for others it wouldn't.

The runtime seems to be dependent on what hind of activity you do.  If you
frequently rollback inserts, vacuum seems to take longer.  Say, a database
with 1m records which endures 5m inserts + rollback is going to take a
while to vacuum.

Just my informal observations, not based on structured testing.

-jwb


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Conditional Adding to a Table

[Glenn Sullivan]

Greetings,

I need to check a table to see if an item already exist in the table.
It if does not, then I want to add it.  Unfortunately, multiple processes
(Solaris system) can be running at the same time.  Sometimes, one process
checks to see if an item exist and it does not. At the same time another
process checks to see if the same item exist and it does not.  Then both,
add the item to the table.  This is, of course, not what I want.

I cannot seem to figure out how to do a conditional test to do something
like this.  If I could, then the whole operation could fit in one transaction
with locking.

Any ideas?

Thanks,
Glenn

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] A tool for making console/text data enrty/display

[Rich Shepard]

On Wed, 10 Oct 2001, David. E. Goble wrote:

> I am lazy. I which to write some c console/text programs to interface
> with postgresql. Are there any tools/utilities, to help make the data
> entry/display screens...?

David,

  ncurses-5.2.

Rich

Dr. Richard B. Shepard, President

   Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | [EMAIL PROTECTED]
 http://www.appl-ecosys.com


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

http://archives.postgresql.org

Re: [GENERAL] PG mailing list problems (was Re: Support issues)

[Alvar Freude]

Hi,

> Has anyone heard any more about this problem?  I also haven't gotten
> anything from pgsql-general since Oct. 1.  The other lists seem to be
> working fine for me.

I got since about one hour mails to an old account, which I disabled months 
ago.
Perhaps someone took an old backup?


Ciao
  Alvar


-- 
Experiment about censorship: http://odem.org/insert_coin/imkp2001.html
 http://odem.org/insert_coin/
Blaster: http://a-blast.org/



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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [GENERAL] Session identifier

[Mihai Gheorghiu]

I want to design some (a significant number of) views able to select records
from various tables for, say, current_client_account_number, which I would
like to store/set in a table (not in a variable). Creating a temp table for
storing these current_client_account_number would not help, because any
pre-designed view will not see the temp table created by the user session.
The reason why I want to design these views is that I want to design rules
to deal with updates/inserts, which would be the only objects that users
will have access to.
I need the PG session ID and not the current user, because the same user may
have more than one session, from different machines or frontends.
Any suggestions?


-Original Message-
From: Brett W. McCoy <[EMAIL PROTECTED]>
To: Mihai Gheorghiu <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Wednesday, October 10, 2001 11:16 AM
Subject: Re: [GENERAL] Session identifier


>On Wed, 10 Oct 2001, Mihai Gheorghiu wrote:
>
>> Is there anything like a session ID? How can I read it?
>> I want to store it in a table together with some other data.
>
>Session ID for use with what?  This is something best handled by your
>client application (like with Apache::Session:DBI).
>
>-- Brett
>  http://www.chapelperilous.net/
>
>"Probably the best operating system in the world is the [operating system]
> made for the PDP-11 by Bell Laboratories." - Ted Nelson, October 1977
>
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


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

http://www.postgresql.org/users-lounge/docs/faq.html

[GENERAL] Problems install with Python...

[Eric D Nielsen]

Hello, we're installing PostGreSQL 7.1.3 on a RH 7.1 system and we need
the --with-python option. The ./configure step works fine, but gmake fails on
the first occurence of an #include .  We added a 
- --with-includes=/path/to/Python/includes, but doesn't seem to solve the 
problem.  What else should we do?

Eric Nielsen

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Performance problem with 50,000,000 rows

[David Link]

I'm new to PG but this just seems wrong.  Can someone take a look:

  .---. .---.
  | bk_inv| | bk_title  |
  |---| |---|
  | isbn  |<--->| isbn  |
  | store | | vendor|
  | qty   | |   |
  | week  | `---'
  |   |  2,000,000 recs
  `---'
   50,000,000 recs

  Actual record numbers:
bk_inv  : 46,790,877 
bk_title:  2,311,710 


VENDOR REPORT

  A list of Inventory items, for any one given vendor (e.q. 01672708)

select   i.isbn,
 t.vendor,
 i.store,
 i.qty
from bk_inv i,
 bk_title t
wherei.isbn = t.isbn
and  t.vendor = '01672708' ;

This query should be instantaneous.  Granted that's 50 million rows, but
I have created an index on the isbn column for both tables.
After about 25 minutes (on 4 processor Del 6300 with 1GB Memory) it
spits out:

ERROR:  Write to hashjoin temp file failed

tiger=# explain select * from bk_inv i, bk_title t where i.isbn = t.isbn
and t.vendor  ='5029';
NOTICE:  QUERY PLAN:

Merge Join  (cost=0.00..11229637.06 rows=2172466 width=72)
  ->  Index Scan using bk_title_isbn_idx on bk_title t 
(cost=0.00..390788.08 rows=107331 width=24)
  ->  Index Scan using bk_inv_isbn_idx on bk_inv i 
(cost=0.00..10252621.38 rows=46790877 width=48)

BIG COST!


These explain queries show the existance of the indexes and give small
costs:

tiger=# explain select * from bk_title where isbn = '5029';
NOTICE:  QUERY PLAN:

Index Scan using bk_title_isbn_idx on bk_title  (cost=0.00..4.90 rows=1
width=24)

tiger=# explain select * from bk_inv where isbn = '0897474228';
NOTICE:  QUERY PLAN:

Index Scan using bk_inv_isbn_idx on bk_inv  (cost=0.00..225.53 rows=55
width=48)


Note.  Same tables, same query returns instantaneously with Oracle 8.1. 
What I am hoping to show is that Postgres can do our job too.

Any help on this much obliged.  (Yes I ran vacuum analyze).

David Link
White Plains, NY

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

http://archives.postgresql.org

[GENERAL] Where to count

[H. Wade Minter]

I'm running a DB query on a database of firewall log entries (right now
around 700k rows).  What I want to do is pull out some common entries, as
well as the number of times that they occur in the table.

Right now, I'm doing a query like:

select source,destination,service,count(*) FROM logs WHERE action='$type'
GROUP BY source,destination,service ORDER BY count DESC LIMIT $num;

This is a little more advanced than I'm used to doing, so I'm wondering if
that query is the best way to get that data, or if there's another way of
doing it.

Thanks,
Wade

-- 
Do your part in the fight against injustice.
Free Dmitry Sklyarov!  http://www.freesklyarov.org/
Fight the DMCA!  http://www.anti-dmca.org/


---(end of broadcast)---
TIP 3: 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] Postgres --with-python problems..

[Eric D Nielsen]

Please ignore if this is a resend, but I got an error message on my mail that
was unreadable in my mail reader.

We are install PostGreSQL 7.1.3 on a RH 7.1 system with python.  ./configure 
works fine, but gmake fails on the first file that has a #include 
with a file not found error.  We've tried re-configuring with a
--with-includes=/path/to/python/include, but we still get the same error.
(Python hsa been installed and works.)  Any help would be appreciated.

Eric Nielsen
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: 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] Sqlstatement with !=-1 fails

[Dave Cramer]

Hello,

I get the following error

pl=# select * from person where id !=-1;
ERROR:  Unable to identify an operator '!=-' for types 'int8' and 'int4'
You will have to retype this query using an explicit cast

pl=# select * from person where id =-1;
 id | name | last_update_time 
+--+--
(0 rows)

However this works fine

pl=# select * from person where id != -1;

This looks like a parser error, note the space added in the select that
works ??

Dave


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

[GENERAL] Error Codes?

[Fran Fabrizio]

I'm a bit confused about the current state of error codes in Pg.  The
docs and
the mailing list archives seem to indicate that this is a TODO item,
however,
at least when using Perl with DBD::Pg, when I call $db->err() or examine

$DBI::err I get a number back.  For instance, 7 if I try to insert too
many
columns into a row, and a 1 if the connection fails.  What's the scoop,
and if
error codes really do exist, is there a list of such codes?

Thanks,
Fran


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

http://www.postgresql.org/users-lounge/docs/faq.html