Re: [GENERAL] pg_dump is missing data

[Tom Lane]

"Robert Wille" <[EMAIL PROTECTED]> writes:
> I am running Postgres 7.3.3 on RedHat Linux 7.3. I have 14 tables, but
> when I run "pg_dump -c -F p -U username dbname", the output contains no
> data for three of them. Here is the relevant output for one of the
> tables:
 
> COPY message (recipient, subject, body, eventtime, delivertime) FROM
> stdin;
> \.

Sure looks to me like that table is empty.  Are you saying you get data
out of it when you do a manual COPY or SELECT?

regards, tom lane

---(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] Another date / time question

[Tom Lane]

"Vanole, Mike" <[EMAIL PROTECTED]> writes:
> I'm trying to query two timestamp(0) columns to determine elapsed time. I u=
> se the age() function which works fine, but I want to return the informatio=
> n in MINUTES.=20

Just subtract (giving an interval), use extract(epoch) to get the
interval in seconds, then divide by 60 and round (or truncate if you
prefer).

regards, tom lane

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

[GENERAL] prob with really simple insert

[marian]

When I do this,
INSERT INTO orgdata ( name, fax, contact, email, phone, address, 
preferred ) VALUES ( 'wally', '123456789', 'wally', '[EMAIL PROTECTED]', 
'987654321', 'wally way', 'f' )

I get
ERROR:  query rewritten 100 times, may contain cycles
: PGRES_FATAL_ERROR
What is going on here

---(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] Physical Database Configuration

[Guy Rouillier]

(Resurrecting a subject I found searching the archives...)

Newbie.  We're investigating replacing our Oracle databases with
PostgreSQL.  Our largest database is currently 25 GB and growing.  It
contains time sequenced data.  Under Oracle, we use a partitioned
tablespace for the busiest table, so that we can remove old partitions
after an aging period.  This one table is the majority of that 25 GB.

I saw in the referenced sequence of posts that PostgreSQL will close a
file for a table once it reaches 1 GB, and start a new file.  So I'm
concluding PostgreSQL has the ability to span multiple files for a
single table.  Is there any way for me to control that, so I can get
PostgreSQL to start a new file on the 1st of each month?  Or is this
planned for tablespaces, which I also saw discussed.

Thanks.

-- 
Guy Rouillier


---(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] Beginner Questions Please: Which To Go With ?

[Andrew Sullivan]

On Fri, Mar 19, 2004 at 11:34:30AM -0500, Robert11 wrote:
> I downloaded postgre, but I am a bit stumped on
> what to do next.
> The problem is I'm totally unfamiliar with ftp downloads.
> 
> There are a bunch of folders.
> 
> What do I open, or do next, please, to get started ?

What operating system are you using?  Windows?  If so, and you are
this new at it, I suspect you'll find PostgreSQL to be a little
tricky to use.  In any case, the instructions for installation are in
the PostgreSQL manual at http://www.postgresql.org.  If you can't
follow the instructions, or get stumped, you should come back with a
question about the point where you're stuck.

> Also: what are the major differences between all 3 packages ?

Access is not a real SQL database, but it has some SQL interface
glued onto it.

MySQL was historically very lightweight and missing quite a few
features.  It is much improved, but continues to have interfaces
which, while initially apparently convenient, are sufficiently
different from the standard way of doing things that you may
experience a lot of pain later.

PostgreSQL is a larger system intented for "industrial strength"
systems.  Having used all three of these, it is the only one among
them that I feel actually comfortable trusting data to.  This is a
prejudice I developed on older versions of MySQL, however, and it is
probably not well justified any more.  PostgreSQL has the steepest
learning curve: you will need to learn a fair amount about what you
are doing before you can do anything useful.  On the other hand, that
initial investment pays handsomely later.

> Which would be easiest to learn for a true database beginner ?

Probably Access.  It's also the least likely to teach you the best
database habits, so I'd urge you to consider the additional work for
Postgres, just because you'll get a good grounding in fundamentals
that way.  Postgres is the most rigid of the systems, in that it
usually has a smaller number of well-defined ways to do something.

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
--Dennis Ritchie

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] \s paging?

[Ron St-Pierre]

CSN wrote:

Is it possible to have "less"-type paging with psql's
\s command? Or other ways like display the last 50
commands backwards?
__
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
 

Since no one has had a go at this, I'll try. The docs say that it is no 
longer necessary to use the \s command because all commands are saved on 
program termination. However, if you want to read/cycle through previous 
commands while the app is still running you have to configure postres 
with readline. Then you can just use your up/down arrows on your 
keyboard to display previous commands. Also available in the few linux 
distros I've used is the shift-pgUp/shift-pgDwn functionality in a 
terminal to view input and output that's already scrolled off of the screen.

HTH
Ron
---(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] simple join query runs very slowly

[Roger Dahl]

Hello everyone,

I'm having trouble with a slow query and I wonder if anyone can help
me speed it up. I have read the PostgreSQL documentation and FAQ but
have been unable to find anything to help me out.

I have three tables: items, boxes and item_box_maps. The items table
holds ids and item names. The boxes table holds ids and box names. The
item_box_maps table holds box and item ids that "map" items to boxes.

To register a box as containing a specific item, I just add a record
in item_box_maps with the appropriate box name id and item name id. I
have used this approach on SQL Server many times and have always
gotten excellent performance.

To find out which boxes contain a specific item, I would write a query
like this:

select b.name,
from boxes b
inner join item_box_maps m on m.box_id = b.id
inner join items i on m.id = m.item_id
where i.name = 'hammer';

I've set up a PostgreSQL database with around 2000 boxes, 2000 items
and 80.000 mappings. The query above will typically return 40 records
(names of boxes) for a given item name. I'm used to this query running
"instantaneously" but on my PostgreSQL server this query takes over
one second.

I have btree indexes on everything and have vacuum analyze'ed 
everything. EXPLAIN gives me the following output on the query above:

Hash Join  (cost=5.00..3608.36 rows=14 width=114) (actual
time=572.76..1043.98 rows=7 loops=1)
   Hash Cond: ("outer".item_id = "inner".id)
   ->  Merge Join  (cost=0.00..3371.16 rows=30938 width=110) (actual
time=472.93..969.62 rows=27978 loops=1)
 Merge Cond: ("outer".id = "inner".box_id)
 ->  Index Scan using boxes_pkey on boxes b 
(cost=0.00..1621.40 rows=2076 width=102) (actual time=2.95..26.0\
8 rows=2076 loops=1)
 ->  Index Scan using item_box_maps_idx_box_id on
item_box_maps m  (cost=0.00..1233.31 rows=51384 wi\
dth=8) (actual time=24.17..580.09 rows=78544 loops=1)
   ->  Hash  (cost=5.00..5.00 rows=1 width=4) (actual time=0.38..0.38
rows=0 loops=1)
 ->  Index Scan using items_idx_item on items i 
(cost=0.00..5.00 rows=1 width=4) (actual time=0.35..0.37 rows=1 l\
oops=1)
   Index Cond: (item = 'hammer'::text)
 Total runtime: 1044.35 msec

I don't have any experience interpreting these, but to my untrained
eye, everything looks fine.

I then tried rewriting the query to use subqueries:

select b.name
from boxes b
where b.id in (
  select box_id
  from item_box_maps
  where item_id in (
select id
from items
where item = 'hammer'
  )
);

This one runs only slightly faster at around 800ms and gives the
following EXPLAIN output:

Seq Scan on boxes b  (cost=1.00..208235168085.56 rows=1038
width=98) (actual time=768.62..812.63 rows=7 loops=1)
   Filter: (subplan)
   SubPlan
 ->  Materialize  (cost=100257787.86..100257787.86 rows=25692
width=4) (actual time=0.35..0.36 rows=7 loops=2076)
   ->  Seq Scan on item_box_maps 
(cost=1.00..100257787.86 rows=25692 width=4) (actual
time=626.02..723\
.87 rows=7 loops=1)
 Filter: (subplan)
 SubPlan
   ->  Materialize  (cost=5.00..5.00 rows=1 width=4)
(actual time=0.00..0.00 rows=1 loops=78544)
 ->  Index Scan using items_idx_item on items 
(cost=0.00..5.00 rows=1 width=4) (actual time=0.32.\
.0.34 rows=1 loops=1)
   Index Cond: (item = 'hammer'::text)


What seems to be wrong with this one is that it's not using the
indexes. If I break up the query and run each part separate and
manually insert the results into the next part, I get the following
results:

select id
from items
where item = 'hammer';
id
---
10751
(1 row)

select box_id
from item_box_maps
where item_id in (10751);

box_id
-
12191
10537
10536
12169
12161
12151
11327
(7 rows)

select name
from boxes
where id in (12191, 10537, 10536, 12169, 12161, 12151, 11327);

name
-
box2910
box1223
box15
box433
box1233
box95
box1003
(7 rows)


Each of these queries return instantly and the output from EXPLAIN
indicates that they use the index. For instance:

Index Scan using boxes_pkey, boxes_pkey, boxes_pkey, boxes_pkey,
boxes_pkey, boxes_pkey, boxes_p\
key on boxes  (cost=0.00..37.69 rows=7 width=98) (actual
time=0.10..0.34 rows=7 loops=1)
   Index Cond: ((id = 12191) OR (id = 10537) OR (id = 10536) OR (id =
12169) OR (id = 12161) OR (id = 12151) OR (id = 11327))
 Total runtime: 0.51 msec

Some more information:

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE
relname LIKE 'items%';

relname | relkind | reltuples | relpages
+-+---+--
 items_id_seq   | S   | 1 |1
 items_pkey | i   |  2124 |   38
 items_type | c   | 0 |0
 items  | r   |  2124 |   81
 items_idx_item | i   |  2124 |9
(5 rows)

SELECT relname, relkind, reltuples, relpag

Re: [GENERAL] Questions on production rollout

[Andrew Sullivan]

On Sat, Mar 20, 2004 at 07:04:42PM -0800, Bill C. wrote:
> 1) A guy here had a compiled version of PostgreSQL for Solaris 8 
> that he gave me to install on my Solaris 8 system.  He said just 
> untar it and you're good to go.  I did, then I tried using the 
> psql command to connect his server that he already had up and 
> running on his system.  Got an error because I don't have a gcc
> library installed.  So here's an example of an install that worked
> on one machine but not the other.  Is it possible to build a
> version that one can be reasonably sure is going to run on all
> systems with a given OS?  I mean, if I know the client machine is
> Solaris 8, can I build a self-contained set of binaries that I 
> can confidently say will run on other Solaris 8 machines?

Yes, but if you're using gcc you'll need either to make sure they
have the relevant gcc libraries installed (see the sun freeware site
for more on this), or you'll have to put together a little package of
relevant libraries, install that with the postgres tree, and alter
the user's LD_LIBRARY_PATH.  I've used both these strategies, and
they both work.
 
> 2) I realize there are other configuration parameters involved.
> E.g. already encountered "SHMMAX value to small" message when 
> trying to start server on my system.  I changed it to something
> big, rebooted and it still didn't work.  I'm not too worried that
> I can't eventually figure it out but it still has me a little 
> spooked.  Wouldn't want to put a client thru hell trying to get 
> the server up and running.  Are there other system configuration 
> gotchas I should be aware of?

That's the biggest one.  You should have a look at Chapter 16 of the
docs (see e.g.
) for
more detail.  Solaris has just unbelievably low defaults for this, so
you'll for sure need to do some work there on every client's machine.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
--Brad Holland

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

   http://archives.postgresql.org

Re: [GENERAL] PHP or JSP? That is the question.

[Greg Stark]

Mike Mascari <[EMAIL PROTECTED]> writes:

> Bas Scheffers wrote:
>
> > Sarcasm: "A form of wit that is marked by the use of sarcastic language

Gee, a helpful dictionary there.

(I suppose that was sarcastic too)

-- 
greg


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

Re: [GENERAL] prob with really simple insert

[scott.marlowe]

On Mon, 22 Mar 2004, marian wrote:

> When I do this,
> INSERT INTO orgdata ( name, fax, contact, email, phone, address, 
> preferred ) VALUES ( 'wally', '123456789', 'wally', '[EMAIL PROTECTED]', 
> '987654321', 'wally way', 'f' )
> 
> I get
> ERROR:  query rewritten 100 times, may contain cycles
> : PGRES_FATAL_ERROR
> 
> What is going on here

Is there a trigger on that table?  I'd guess it's getting caught in a loop



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

Re: [GENERAL] To: Mr. Al Cohen: Your Reply To My Previous Post (which database to use/buy)

[Andrew Sullivan]

On Sat, Mar 20, 2004 at 07:00:40AM -0800, Bob wrote:
> with Access, but would certainly like to try mysql first.
> 
> Have downloaded it properly, I think, but can't get it to run.

If you're looking for MySQL help, you should probably try a MySQL
list.  This is for Postgres support.

> Why does it disappear ?  (running W98)

You won't be able to use Postgres on Win 98, I think -- I could be
wrong.  You'd need Cygwin, in any case.  There is a document to help
you:



It's for 7.3, but it likely works for later versions.  I have never
used Postgres on Windows (I can barely stand to use Word on Windows),
so I can't help you.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]

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

   http://archives.postgresql.org

Re: [GENERAL] PHP or JSP? That is the question.

[Richard Huxton]

On Friday 26 March 2004 00:43, Mike Mascari wrote:
> Bas Scheffers wrote:
> >>I'm confused, where was the sarcasm?
> >
> > Sarcasm: "A form of wit that is marked by the use of sarcastic language
> > and is intended to make its victim the butt of contempt or ridicule."
> >
> > The banks, an corporations that act just like them, being the "victim",
> > obviously...
>
> I think Greg should have written:
>
> 
> I'm confused, where was the sarcasm?
> 


Actually Mike, I'd suggest it probably should have been

I'm confused, where was the sarcasm?



-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] prob with really simple insert

[Stephan Szabo]

On Mon, 22 Mar 2004, marian wrote:

> When I do this,
> INSERT INTO orgdata ( name, fax, contact, email, phone, address,
> preferred ) VALUES ( 'wally', '123456789', 'wally', '[EMAIL PROTECTED]',
> '987654321', 'wally way', 'f' )
>
> I get
> ERROR:  query rewritten 100 times, may contain cycles
> : PGRES_FATAL_ERROR

Do you have any rules on orgdata?

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

Re: [GENERAL] Beginner Questions Please: Which To Go With ?

[scott.marlowe]

On Fri, 19 Mar 2004, Robert11 wrote:

> Hello:
> 
> Am about to sign up for an on-line database course (introductory)
> 
> Apparently, one must have on their PC for the course either
> 
> Access
> postgre
> mysql
> 
> Am really new at this.
> Access is a problem, as the package costs is $200.00  Wow !
> 
> I downloaded postgre, but I am a bit stumped on
> what to do next.
> The problem is I'm totally unfamiliar with ftp downloads.
> 
> There are a bunch of folders.

There should be one master folder named something like

postgresql-7.4.2

cd into that directory, and read the INSTALL file.  Basically, you'll need 
to do:

./configure
make
make install

to install postgresql, then you'll need to run initdb as the postgres 
super user and start the database.  It's not that hard, really, just take 
your time.

> I would like to evaluate postgre and mysql before the course actually
> starts.
> 
> Also: what are the major differences between all 3 packages ?

Access is a single user database system designed for simple databases.
MySQL is a very simple database server designed for speed with one or two 
users. 
Postgresql is a full featured relational database server in the same class 
as Oracle or db2 in terms of performance, scaling, and features.

> Which would be easiest to learn for a true database beginner ?

MySQL is easier to learn, but it teaches you bad habits.  Access is pretty 
easy, but also has some quirks.  Postgresql is likely the hardest to 
learn, but the most rewarding once you know it.  It is likely to be able 
to "take you further" than the other two.

> Should perhaps I go with Access despite the cost ?  Is it "easier", or are
> the screens and interface 'better" ?

Just easier for a beginner.  The basic concepts of database don't get 
easier to understand with any of the products, because the understanding 
is the hard part, not the UI of the database.

> Want to learn one database fairly well, as I think it's something I should
> know, and would also look good on a job resume.

Learn Postgresql, it will take you farther (Oracle, db2, etc... will come 
easier to you if you learn postgresql.)


---(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] Tables as function arguments

[Richard Huxton]

On Wednesday 24 March 2004 04:31, Jeremy Semeiks wrote:
> Hello,
>
> I use psql interactively for data exploration, and often I want to do
> the same types of generic set operations on data in many different
> tables. For example, to calculate a similarity metric between 2
> different tables I might type:
[snip]
> ... plpy.execute("select * from "+args[0]+" union select * from "+args[1])
> ...
>
> But calling this function on the results of subselects turns into a
> big syntactic mess. Is there a better way to do this kind of thing?

Not really - you might be able to use some sprintf() equivalent to make the 
syntax prettier (or different, anyway) but you'll need to build a dynamic 
query.

-- 
  Richard Huxton
  Archonet Ltd

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] CREATE TEMP TABLE

[Bruce Momjian]

See the FAQ entry about temp tables in function.  Use EXECUTE.

---

Juris Krumins wrote:
> 
> Hi everybody. I have a problem with my PostgreSQL DBMS.
> In serverl log file I periodicly have errors like:
> 
> 2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
> 2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
> 2004-03-24 11:43:18 ERROR:  Relation 'tmp_table1' already exists
> 2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
> 2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
> 
> I figure out that this is connected with query I use in one of the
> function:
> 
> CREATE TEMP TABLE tmp_table1 AS SELECT bla-bla-bla.
> 
> The problem is that when I had no so many users I have rigth now
> everything was ok, but now I have quite more users, so this problem
> appeared. Function , which use this query, is used all over my web site,
> which is base on PostgreSQL.
> 
> So anybody have any idea how to correct this or maybe I have to change my
> query. Any help would be appreciated.
> 
> I'm using PostgreSQL 7.3.4
> 
> Thanks a lot.
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] Where are these tables??

[Robert Treat]

On Fri, 2004-03-26 at 09:28, Patrick Hatcher wrote:
 
While trying to find the physical disk space size each table in our
database (by matching the file name in the base directory to an entry
in pg_class),  we found 4 files that constitute 5gigs that we cannot
match back to a entry in pg_class.
Is it safe to delete these?  

I feel like you're holding a gun asking if it's ok to shoot, but you
haven't told us which way it's pointing.  I'd be curious to know the
filenames in question and the query your running to look for these
files... might also suggest doing a vacuum full on your cluster to see
if their filesizes change at all...  but if you've got that cowboy
feeling going i'd say shutdown the database, backup the directory,
delete the files, and fire it back up... 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


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

Re: [GENERAL] initdb and login after

[Tom Lane]

[EMAIL PROTECTED] writes:
> Ive been having a weird problem recently. Ive been using initdb to 
> initialize a new postgres, but the as soon as i try and log in, i keep 
> on getting and "no PostgreSQL user name specified in startup packet" 
> error. Should this not be ok?

That's not an easy error to get --- most of our client-side programs try
hard to provide some user name.  What are you doing to "log in", exactly?
Do you perhaps have PGUSER set to an empty string in your environment?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Passing a row

[Tom Lane]

Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> Can anybody show me the error of my ways?

Seems like I just answered this yesterday ... ah, here we go:
http://archives.postgresql.org/pgsql-general/2004-03/msg00862.php

I don't recall if 7.3 has all the functionality of 7.4 in this regard.

regards, tom lane

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

Re: [GENERAL] simple join query runs very slowly

[Stephan Szabo]

On Thu, 25 Mar 2004, Roger Dahl wrote:

> Hello everyone,
>
> I'm having trouble with a slow query and I wonder if anyone can help
> me speed it up. I have read the PostgreSQL documentation and FAQ but
> have been unable to find anything to help me out.
>
> I have three tables: items, boxes and item_box_maps. The items table
> holds ids and item names. The boxes table holds ids and box names. The
> item_box_maps table holds box and item ids that "map" items to boxes.
>
> To register a box as containing a specific item, I just add a record
> in item_box_maps with the appropriate box name id and item name id. I
> have used this approach on SQL Server many times and have always
> gotten excellent performance.
>
> To find out which boxes contain a specific item, I would write a query
> like this:
>
> select b.name,
> from boxes b
> inner join item_box_maps m on m.box_id = b.id
> inner join items i on m.id = m.item_id
 -- is there a typo here?  I'd think it should be i.id=m.item_id
> where i.name = 'hammer';

Hmm, you didn't give a version.  On some versions the above is going
to force the boxes to item_box_maps join to be first (where it looks like
perhaps the item_box_maps to items join first would be better).  You could
try something like:

select b.name from boxes b inner join (item_box_maps m inner join items i
on i.id=m.item_id) on m.box_id=b.id where i.name='hammer'

And see what that gives you plan wise...


---(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] Native Win32 port - PLEASE!

[Andrew Mayo]

PLEASE  can we have the native Win32 port SOON. 

It will totally and utterly hurt Microsoft in a very important area to
have this port. Why?

Well, many of you will know that MS give away a cut-down version of
SQL Server (MSDE) and this is bundled with a *lot* of Win32 apps.

Now, MSDE is pretty good, though it is a nightmare to upgrade, for
various reasons - but there are fishhooks in the license agreement
that worry a lot of people - particularly, a clause about replication
that could be interpreted in ways that would suddenly require users to
pay MS large wads of cash.

And MSDE is deliberately crippled; five concurrent users, max 2G
database size.

Many vendors (including the one I work for) would be very very very
interested in looking at PostgreSQL as an alternative BUT it NEEDS to
run natively on Windows (not via Cygwin)

If it did, I can see real inroads being made into 'mindspace' here and
a huge increase in PostgreSQL usage - it would become ubiquitous as a
21st century database - and porting to Win32 therefore helps, not
hinders, the open source movement.

So can I quietly beg the Win32 group to expedite this port. I believe
you will be utterly astonished at the demand. Please.

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

Re: [GENERAL] CREATE TEMP TABLE

[Bill Moran]

Juris Krumins wrote:
Hi everybody. I have a problem with my PostgreSQL DBMS.
In serverl log file I periodicly have errors like:
2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
2004-03-24 11:43:18 ERROR:  Relation 'tmp_table1' already exists
2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
2004-03-24 11:43:18 ERROR:  Relation "tmp_table1" does not exist
I figure out that this is connected with query I use in one of the
function:
CREATE TEMP TABLE tmp_table1 AS SELECT bla-bla-bla.

The problem is that when I had no so many users I have rigth now
everything was ok, but now I have quite more users, so this problem
appeared. Function , which use this query, is used all over my web site,
which is base on PostgreSQL.
So anybody have any idea how to correct this or maybe I have to change my
query. Any help would be appreciated.
I'm using PostgreSQL 7.3.4
I'm not sure, but I suspect your web application is doing connection pooling
to connect to Postgres.  If that is the case, it's possible two different
threads of your application are trying to work with "tmp_table1" at the
same time over the same connection, which would cause the errors you
describe.
If this is the case, there are a number of choices:
1) Don't use a temporary table
2) Don't use connection pooling
3) Make you application choose a unique name for the temporary table, so
   there's no chance of clash.  Instead of always using tmp_table1, append
   a unique number to the name each time you create the temp table.
Hope this helps.

--
Bill Moran
Potential Technologies
http://www.potentialtech.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] PHP or JSP? That is the question.

[Guy Fraser]

scott.marlowe wrote:

On Thu, 25 Mar 2004, Bas Scheffers wrote:

 

Guy,

   

1) Fill out form to get data.
2) Server responds with java and set of records.
3) Java applet displays one of the records and with out further
 

I see what you mean now, a business application in a browsers. It's
usefull for that - my company has written various trading/market data apps
that way for banking clients - but for a public website, html and forms
are a better solution.
   

Note that if you want to write straight client-server apps, tcl/tk and 
php-gtk both work pretty well too.
 

I have never written a gui based app for Windows or Macs and since the
application would have to be cross platform compatable it would have to
run on Windows, Macs as well as Linux and other Unix variants. That is
too much work for me to do by myself. About the only alternative would
be to write the Client in Java {Not that MS cruft, but real Sun Java} then
anything woth a Java VM would work. I could then write the server in
what ever I wanted {probably C}.
This is way off the topic, though. The question was about PHP and JSP not
gtk client/server application efficiencies.
--
Guy Fraser




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

Re: [GENERAL] without hash functions

[Tom Lane]

"koju" <[EMAIL PROTECTED]> writes:
> With some reason, only btree functions are allowed to use in my project. I =
> modified all catalogs so that only bt- bp- related can be used. And it look=
> s like hash join and grouping are really depending on hash functions.

This is just about the silliest idea I've heard all week ...

regards, tom lane

---(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] FATAL: Can't read lock file /tmp/.s.PGSQL.5432.lock: Access denied

[Tom Lane]

"Carsten B. Jensen" <[EMAIL PROTECTED]> writes:
> what am I doing wrong here?

Is there already a lock file by that name?  It seems likely that you are
having a conflict against a pre-existing Postgres installation.

regards, tom lane

---(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] self referencing tables/ nested sets etc...

[Shawn Harrison]

Joe,

That's cool! Thanks for the tip. It looks like your "branch" is my
"ancestry". I'll look in contrib more often. I could use connectby() instead
of my homebrew plpgsql functions.

Shawn

- Original Message - 
From: "Joe Conway" <[EMAIL PROTECTED]>
To: "Shawn Harrison" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, March 24, 2004 11:37 PM
Subject: Re: [GENERAL] self referencing tables/ nested sets etc...


> Shawn Harrison wrote:
> > Forbes. But my own approach is to let the unique id of each object serve
as
> > its "key" in the nested set key-chain (which I call its "ancestry"), and
> > separate the ids with path separator, e.g.:
>
> See connectby() in contrib/tablefunc. It produces much the same output
> dynamically.
>
> Joe


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

[GENERAL] Compiling

[Nilton Yudiro Ikezire]

Hi,

I have PostgreSQL compiled and i realized that it has some diferences from
the packages instalation like the way i access a database, when i compile i
have  psql.bin that access the database and packages i have a link to
pg_wrapper
   How can i compile and execute the same way the packages instalation?

   Nilton.
--
Open WebMail Project (http://openwebmail.org)


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

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] pg_dump "what if?"

[Peter Lang]

Hi,

I'm running pg_dump 7.3.4.  I have a nightly process that dumps a database
via pg_dump, and then checks for the existence of the dumped file afterwards
to confirm that the dump actually occurred.  Just wondering, however, if a
corrupt disk prevented pg_dump from executing fully, would there still be an
output file up to the point where the corruption was encountered?  If so,
does anyone have any suggestions on another method to confirm whether or not
the db was successfully dumped?

BTW I'm running under Win2k using Cygwin.

Thanks in advance,
Peter


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

Re: [GENERAL] Native Win32 port - PLEASE!

[Rick Gigger]

So can I quietly beg the Win32 group to expedite this port. I believe
you will be utterly astonished at the demand. Please.
I'm sure quietly begging certain developers with your pocekt book 
probably wouldn't hurt your cause either.  :)

Actually though from what I read here on this list native win32 support 
is almost ceranily going to be included in the next major release of 
postgres with will probably come out sometime in the next 6-9 month.

Am I right on this?

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

[GENERAL] PostgreSQL7.3.4-8

[H A Prahalad]

Hello All,
I installed RHEL-3 Work Station and I found there is no postgreSQL
server and pl package along with the distribution. Is RHN not distributing
these packages. If not where can I download it from. Can anybody help me
with this.

regards,
Prahalad


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

[GENERAL] License on PostgreSQL

[Eric Yum]

Dear Sir

 

I am a developer of one commercial organization. We
are going to develop some applications with PostgreSQL
7.3.3. I would like to know that is it necessary to pay any license charge on
the usage of PostgreSQL as database server for
commercial purpose. If so, how much of it?? Does it
charge on server basis or client basis??

 

 

 

Best Regards, 

Eric Yum

CK Life Sciences Ltd.

Finance & Administration - IT
Team

Tel: 21261351

 

Re: [GENERAL] 7.4.2 on Solaris 9 - Error

[Wes Palmer]

On 3/25/04 4:13 PM, "Bruce Momjian" <[EMAIL PROTECTED]> wrote:

> Are you sure your change is correct?  We just added
> "-D_POSIX_PTHREAD_SEMANTICS" in post 7.4.2, and the submitters did not
> mention any problems with the -pthread line for gcc.
> 
> I just tried it on Sourceforge's Solaris 9 and I see:
> 
> bash-2.05$ gcc -pthread
> gcc: unrecognized option `-pthread'
> gcc: no input files
> 
> which seems to indicate you are right.  I am kind of surprised.  Some
> platforms to take -pthread, some -lpthread, so I am pretty sure it was
> specified by the original Solaris guy.

Don't know...  I assume it was just a typo that wouldn't show up if you were
using SUNWSPRO compiler instead of GCC.
 
> OK, new template/solaris version applied to 7.4.X and head:

[snip]

> THREAD_SUPPORT=yes
> NEED_REENTRANT_FUNCS=yes# 5.6 2003-09-13
> THREAD_CPPFLAGS="-D_POSIX_PTHREAD_SEMANTICS"
> if test "$GCC" != yes
> thenTHREAD_CPPFLAGS="$THREAD_CPPFLAGS -mt"
> fi
> THREAD_LIBS="-lpthread"

Shouldn't that be 

THREAD_CPPFLAGS="-D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS"

According to Jan, -D_REENTRANT must also be specified for libpq to be built
correctly.

Why isn't -D_THREAD_SAFE required also?

Wes


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

[GENERAL] Geodata type...

[Azeem M. Suleman]

Hi,

I have inserted some images as binary data in one of my postgresql database
now i need to query on that using Geofunctions.

Or

Is it possible to use binary data images directly in Geodata types

Thanks.



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

   http://archives.postgresql.org

Re: [GENERAL] License on PostgreSQL

[Bernard Clement]

There is no license fee either for commercial or non commercial usage as 
state: " Permission to use, copy, modify, and distribute this software and 
its documentation for any purpose, without fee, and without a written 
agreement is hereby granted, provided that the above copyright notice and 
this paragraph and the following two paragraphs appear in all copies.

HOWEVER, I AM NOT A LAWER, THEREFORE I COULD BE WRONG.

Regards,

Bernard

On Thursday 25 March 2004 01:19, Eric Yum wrote:
> Dear Sir
>
> I am a developer of one commercial organization. We are going to develop
> some applications with PostgreSQL 7.3.3. I would like to know that is it
> necessary to pay any license charge on the usage of PostgreSQL as
> database server for commercial purpose. If so, how much of it?? Does it
> charge on server basis or client basis??
>
>
>
> Best Regards,
> Eric Yum
> CK Life Sciences Ltd.
> Finance & Administration - IT Team
> Tel: 21261351


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

Re: [GENERAL] Physical Database Configuration

[scott.marlowe]

On Fri, 26 Mar 2004, Guy Rouillier wrote:

> (Resurrecting a subject I found searching the archives...)
> 
> Newbie.  We're investigating replacing our Oracle databases with
> PostgreSQL.  Our largest database is currently 25 GB and growing.  It
> contains time sequenced data.  Under Oracle, we use a partitioned
> tablespace for the busiest table, so that we can remove old partitions
> after an aging period.  This one table is the majority of that 25 GB.
> 
> I saw in the referenced sequence of posts that PostgreSQL will close a
> file for a table once it reaches 1 GB, and start a new file.  So I'm
> concluding PostgreSQL has the ability to span multiple files for a
> single table.  Is there any way for me to control that, so I can get
> PostgreSQL to start a new file on the 1st of each month?  Or is this
> planned for tablespaces, which I also saw discussed.

Not really, but there's more than one way to skin a cat.

What you can do is create a table for each month, then create a view on 
top of those tables.


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] pg_dump "what if?"

[Shelby Cain]

Glad I could help.  As Dustin pointed out, pg_dump
will return a non-zero value to indicate an error.

Regards,

Shelby Cain

--- Anony Mous <[EMAIL PROTECTED]> wrote:
> Thanks again.  I've got it working using the API
> calls described.  
> 
> pg_dump does return an exit code of 0 upon success. 
> Does anyone know off
> hand what is returned when the process fails?
> 
> -Pete
> 
> 

__
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

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

Re: [GENERAL] pg_dump "what if?"

[Steve Crawford]

On Wednesday 24 March 2004 10:59 am, Peter Lang wrote:
> I'm running pg_dump 7.3.4..does anyone
> have any suggestions on another method to confirm whether or not
> the db was successfully dumped?
>
> BTW I'm running under Win2k using Cygwin.

Normally you can check the exit status of the command. In bash you 
might use:
pg_dump.
if [ $? -ne 0 ]
then
  # report the error
fi

Don't know if there are any problems getting the exit status in 
cygwin, though.

Cheers,
Steve


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

Re: [GENERAL] Problem with inserting into a triggerable table;

[Stephan Szabo]

On Thu, 25 Mar 2004, Anoop Rajendra wrote:

> Hi, I was having some problems with inserting into a table with a trigger
> function attached.
>
> My code is shown below -
> CREATE OR REPLACE FUNCTION update_tv_prefs() RETURNS trigger AS '
> BEGIN
> SELECT * FROM tvprefs WHERE ch_number = NEW.id AND time_of_day =
> get_time() as X;

This "as X" is invalid I believe.

> Here, I dont have problems creating either the trigger or the function.
> Nor did I have any problems with the language before. I wrote another
> function with pretty much the same syntax ( not a trigger function though)
> and I didnt get any errors.
> But when I try to insert into the TV table, I get a error telling me -
>
> ERROR:  syntax error at or near "as" at character 76
> CONTEXT:  PL/pgSQL function "update_tv_prefs" line 2 at SQL statement

---(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] Native Win32 port - PLEASE!

[Andrew Sullivan]

On Fri, Mar 26, 2004 at 02:28:10AM -0800, Andrew Mayo wrote:
> PLEASE  can we have the native Win32 port SOON. 

I don't think anyone is dragging their heels on purpose.  If it's
that important to you, consider contributing either in code or in
paid developer time.  

A

-- 
Andrew Sullivan  | [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

Re: [GENERAL] Native Win32 port - PLEASE!

[Jeff Eckermann]

--- Rick Gigger <[EMAIL PROTECTED]> wrote:
> > So can I quietly beg the Win32 group to expedite
> this port. I believe
> > you will be utterly astonished at the demand.
> Please.
> 
> I'm sure quietly begging certain developers with
> your pocekt book 
> probably wouldn't hurt your cause either.  :)
> 
> Actually though from what I read here on this list
> native win32 support 
> is almost ceranily going to be included in the next
> major release of 
> postgres with will probably come out sometime in the
> next 6-9 month.

According to recent posts on the win32 hackers list,
current development code compiles and runs on Windows
now, albeit with a few magical incantations needed
along the way.  There are still issues to resolve, but
work is proceeding rapidly.

Now, as to the timing of the next release, I don't
believe that anyone has made an estimate yet.

After lurking on the win32 hackers list for a while, I
am very impressed by the energy and capability of the
people who are doing the work.  Anyone who wants to
test the development code, and report back on any new
issues encountered, would no doubt be doing the
developers a favour.  Instructions for compiling on
Windows are on the project page on Bruce Momjian's
site.  But I am feeling too lazy to look up the URL
right now ;-)

> 
> Am I right on this?
> 
> ---(end of
> broadcast)---
> TIP 4: Don't 'kill -9' the postmaster


__
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

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

Re: [GENERAL] Physical Database Configuration

[Guy Rouillier]

Bruno Wolff III wrote:
> On Fri, Mar 26, 2004 at 11:53:47 -0600,
>   Guy Rouillier <[EMAIL PROTECTED]> wrote:
>> 
>> I saw in the referenced sequence of posts that PostgreSQL will close
>> a file for a table once it reaches 1 GB, and start a new file.  So
>> I'm concluding PostgreSQL has the ability to span multiple files for
>> a single table.  Is there any way for me to control that, so I can
>> get PostgreSQL to start a new file on the 1st of each month?  Or is
>> this planned for tablespaces, which I also saw discussed.
> 
> Currently there is no way to force starting a new file.

Thanks for all replies ()  I'll follow the leads into the archives for
how to do this with views.

I actually sent this earlier directly to Tom by mistake.  I got tripped
up on replying.  This is the first list I've encountered where "Reply
To" is set to the individual poster rather than the list.  Is that on
purpose? (dumb question, I know - of course it is on purpose - but why?)

-- 
Guy Rouillier


---(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] Native Win32 port - PLEASE!

[Matthew T. O'Connor]

Andrew Mayo wrote:

PLEASE  can we have the native Win32 port SOON. 
 

All I can tell you is that a lot of energy and code is being thrown at 
this problem and a lot of progress has been make.  While it's not done 
yet, it certainly appears that 7.5 will include a native win32 port!  
But as is the way with open source, there are no promises. 

If you are really interested, join the win32 mailing list and contribute.

Matthew

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] pg_dump "what if?"

[Dustin Sallings]

On Mar 26, 2004, at 6:28, Anony Mous wrote:

Thanks again.  I've got it working using the API calls described.

pg_dump does return an exit code of 0 upon success.  Does anyone know 
off
hand what is returned when the process fails?
	Not 0.  You're looking for success, right?

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

Re: [GENERAL] 7.4.2 on Solaris 9 - Error

[Bruce Momjian]

[EMAIL PROTECTED] wrote:
> On 3/25/04 4:13 PM, "Bruce Momjian" <[EMAIL PROTECTED]> wrote:
> 
> > Are you sure your change is correct?  We just added
> > "-D_POSIX_PTHREAD_SEMANTICS" in post 7.4.2, and the submitters did not
> > mention any problems with the -pthread line for gcc.
> > 
> > I just tried it on Sourceforge's Solaris 9 and I see:
> > 
> > bash-2.05$ gcc -pthread
> > gcc: unrecognized option `-pthread'
> > gcc: no input files
> > 
> > which seems to indicate you are right.  I am kind of surprised.  Some
> > platforms to take -pthread, some -lpthread, so I am pretty sure it was
> > specified by the original Solaris guy.
> 
> Don't know...  I assume it was just a typo that wouldn't show up if you were
> using SUNWSPRO compiler instead of GCC.
>  
> > OK, new template/solaris version applied to 7.4.X and head:
> 
> [snip]
> 
> > THREAD_SUPPORT=yes
> > NEED_REENTRANT_FUNCS=yes# 5.6 2003-09-13
> > THREAD_CPPFLAGS="-D_POSIX_PTHREAD_SEMANTICS"
> > if test "$GCC" != yes
> > thenTHREAD_CPPFLAGS="$THREAD_CPPFLAGS -mt"
> > fi
> > THREAD_LIBS="-lpthread"
> 
> Shouldn't that be 
> 
> THREAD_CPPFLAGS="-D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS"
> 
> According to Jan, -D_REENTRANT must also be specified for libpq to be built
> correctly.

OK, I added -D_REENTRANT.

> Why isn't -D_THREAD_SAFE required also?

No idea?  Someone?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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] Passing a row

[Terry Lee Tucker]

Thanks for your response. I looked the posts you reference, but, to be quite 
frank, I didn't understand. I am confused regarding the sample code in the 
documentation I am looking at. Here it is:

CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS '
DECLARE
in_t ALIAS FOR $1;
BEGIN
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
' LANGUAGE 'plpgsql';

If this code is workable code, I would love to see an example of the code that 
would call such a function so I can determine how to pass the parameter. I 
think I did a poor job of expressing my ignorance ;o)

Thanks...

On Friday 26 March 2004 10:59 am, Tom Lane saith:
> Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> > Can anybody show me the error of my ways?
>
> Seems like I just answered this yesterday ... ah, here we go:
> http://archives.postgresql.org/pgsql-general/2004-03/msg00862.php
>
> I don't recall if 7.3 has all the functionality of 7.4 in this regard.
>
>   regards, tom lane
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend

-- 
Quote: 53
"A soul can never attain the knowledge of God unless God Himself in His
 condescension takes hold of it and raises it up to Himself. For the
 human intellect lacks the power to ascend and to participate in divine
 illumination, unless God Himself draws it up -- in so far as this is
 possible for the human intellect -- and illumines it with rays of divine
 light."

 --The Philokalia

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: [EMAIL PROTECTED]

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Physical Database Configuration

[Bruno Wolff III]

On Fri, Mar 26, 2004 at 11:53:47 -0600,
  Guy Rouillier <[EMAIL PROTECTED]> wrote:
> 
> I saw in the referenced sequence of posts that PostgreSQL will close a
> file for a table once it reaches 1 GB, and start a new file.  So I'm
> concluding PostgreSQL has the ability to span multiple files for a
> single table.  Is there any way for me to control that, so I can get
> PostgreSQL to start a new file on the 1st of each month?  Or is this
> planned for tablespaces, which I also saw discussed.

Currently there is no way to force starting a new file.

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

Re: [GENERAL] simple join query runs very slowly

[scott.marlowe]

On 25 Mar 2004, Roger Dahl wrote:

> To find out which boxes contain a specific item, I would write a query
> like this:
> 
> select b.name,
> from boxes b
> inner join item_box_maps m on m.box_id = b.id
> inner join items i on m.id = m.item_id
> where i.name = 'hammer';

Is this the actual query?  You're not joining items to anything.  I'm 
guessing it should really be:

inner join items i on i.id = m.item_id

Looking at the explain, I'm assuming it's just a typo, as it shows the
query using an index on i something or other.

> I've set up a PostgreSQL database with around 2000 boxes, 2000 items
> and 80.000 mappings. The query above will typically return 40 records
> (names of boxes) for a given item name. I'm used to this query running
> "instantaneously" but on my PostgreSQL server this query takes over
> one second.
> 
> I have btree indexes on everything and have vacuum analyze'ed 
> everything. EXPLAIN gives me the following output on the query above:
> 
> Hash Join  (cost=5.00..3608.36 rows=14 width=114) (actual
> time=572.76..1043.98 rows=7 loops=1)
>Hash Cond: ("outer".item_id = "inner".id)
>->  Merge Join  (cost=0.00..3371.16 rows=30938 width=110) (actual
> time=472.93..969.62 rows=27978 loops=1)
>  Merge Cond: ("outer".id = "inner".box_id)
>  ->  Index Scan using boxes_pkey on boxes b 
> (cost=0.00..1621.40 rows=2076 width=102) (actual time=2.95..26.0\
> 8 rows=2076 loops=1)
>  ->  Index Scan using item_box_maps_idx_box_id on
> item_box_maps m  (cost=0.00..1233.31 rows=51384 wi\
> dth=8) (actual time=24.17..580.09 rows=78544 loops=1)
>->  Hash  (cost=5.00..5.00 rows=1 width=4) (actual time=0.38..0.38
> rows=0 loops=1)
>  ->  Index Scan using items_idx_item on items i 
> (cost=0.00..5.00 rows=1 width=4) (actual time=0.35..0.37 rows=1 l\
> oops=1)
>Index Cond: (item = 'hammer'::text)
>  Total runtime: 1044.35 msec
> 
> I don't have any experience interpreting these, but to my untrained
> eye, everything looks fine.

No major issues with row count estimates bein way off.  But, maybe hash 
join isn't the best here.  You can try disabling it and see what you get:

set enable_hashjoin = off;

and try it again.

> I then tried rewriting the query to use subqueries:
> 
> select b.name
> from boxes b
> where b.id in (
>   select box_id
>   from item_box_maps
>   where item_id in (
> select id
> from items
> where item = 'hammer'
>   )
> );
> 
> This one runs only slightly faster at around 800ms and gives the
> following EXPLAIN output:

Now it's important which version you're running.  In clauses are much sped 
up in 7.4.

> Seq Scan on boxes b  (cost=1.00..208235168085.56 rows=1038

This tells me that set enable_seqscan=off was done.  Was it?

Forcing an index isn't always fastest.

> SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE
> relname LIKE 'items%';
> 
> relname | relkind | reltuples | relpages
> +-+---+--
>  items_id_seq   | S   | 1 |1
>  items_pkey | i   |  2124 |   38
>  items_type | c   | 0 |0
>  items  | r   |  2124 |   81
>  items_idx_item | i   |  2124 |9
> (5 rows)
> 
> SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE
> relname LIKE 'boxes%';
> 
> relname   | relkind | reltuples | relpages
> --+-+---+--
>  boxes_id_seq | S   | 1 |1
>  boxes_pkey   | i   |  2076 |   67
>  boxes_type   | c   | 0 |0
>  boxes| r   |  2076 |  456
>  boxes_idx_name   | i   |  2076 |   12
>  (5 rows)
> 
> SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE
> relname LIKE 'item_box_maps%';
> 
> relname   | relkind | reltuples | relpages
> --+-+---+--
>  item_box_maps_id_seq | S   | 1 |1
>  item_box_maps_pkey   | i   | 78544 |  246
>  item_box_maps| r   | 51384 |  435
>  item_box_maps_idx_item_id| i   | 78544 |  232
>  item_box_maps_idx_box_id | i   | 78544 |  248
>  item_box_maps_idx_item_id_box_id | i   | 78544 |  296
> (6 rows)
> 
> SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE
> tablename = 'item_box_maps';
> 
> attname   | n_distinct | most_common_vals
> --++--
>  id   | -1 |
>  item_id  |   2271 | {1,771,33,11104,81,677,4915,2,46,11853}
>  box_id   |   3448 |
> {10720,10600,10701,9329,9346,10611,10734,2960,5270,9332}
> (3 rows)
> 
> The on

Re: [GENERAL] Native Win32 port - PLEASE!

[Bruno Wolff III]

On Fri, Mar 26, 2004 at 02:28:10 -0800,
  Andrew Mayo <[EMAIL PROTECTED]> wrote:
> PLEASE  can we have the native Win32 port SOON. 
> 
> It will totally and utterly hurt Microsoft in a very important area to
> have this port. Why?

People are working on it and from what I have seen on the lists the port
is going well and will be available as part of the 7.5 release.

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