Re: [GENERAL] Well, Pervasive is now out....

[Chris Browne]

[EMAIL PROTECTED] (Joe Audette) writes:
> My guess is what this really means is they weren't making money on
> it. Its a community friendly spin to suggest that community support
> is so good that not many companies will pony up for commercial
> support. My guess is that its fairly accurate though. A company is
> only going to use postgreSQL if their dba is behind it and for that
> to be the case the dba is probably pretty comfortable with their own
> knowledge backed by community support.  If its not the dba who is
> promoting pgsql in the company then it is likely an exec who sees it
> as a way to save money and likely doesn't want to pay for support
> for a free product.

Also, it's worth considering that there are other service
organizations out there.  With some of its "staff acquisitions,"
Command Prompt has more staff at a high technical level with
PostgreSQL than Pervasive did.

In effect, this suggests that CP (and others that are generally
smaller players) "beat out" Pervasive.

I also have heard vaguely that there may have been other politicking
taking place inside Pervasive.  Becoming a successful PG "shop" wasn't
necessarily something everyone there agreed they wanted to do.  I have
no special knowledge about such, but would strongly suspect that
there's more to the story than will ever meet our eyes.

I suppose it would be at least somewhat interesting to watch what they
do next; if it *isn't* to go in some clear new technical direction,
that would support the notion that what happened wasn't "about us."
-- 
"cbbrowne","@","acm.org"
http://www.ntlug.org/~cbbrowne/finances.html
"This must be Thursday.  I never could get the hang of Thursdays."
- Arthur Dent

---(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 database using 8.1 or 7.1

[Jaime Casanova]

On 8/3/06, Jonathan Vallar <[EMAIL PROTECTED]> wrote:

Hi!

 I have a corrupt database that runs on Postgres 7.1.2. I was able to repair
the database where I can now connect to the database and do queries.

 I have a migration plan to transfer my database to a Postgres 8.1. I
encountered problems when I was dumping the database. Postgres 7.1 reported:
getTables(): SELECT (funcname) for trigger trig_mis_t_ins returned 0 tuples.
Expected 1.

 The trig_mis_t_ins trigger is no longer found on the database but it is
still listed at the database. With this, I assume the database is corrupt.

 Would my problems go away if I dump it on postgres 8?

 Thanks.

 Regards,

 Jonathan



the better yu can do is try to dump the database with the 8.1's
pg_dump... but i don't know what problems can be (or if there is a
problem doing that)...

the better we can do is to wish you luck, your version is obsolete and
unsupported from some time ago...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
  Richard Cook

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

  http://archives.postgresql.org

Re: [GENERAL] Create function problem

[Michael Fuhr]

On Fri, Aug 04, 2006 at 06:16:41AM +0300, gustavo halperin wrote:
> *OK thank you, you right, but after write "public" I receive again an 
> empty row, Why??.
[...]
> mydb=> CREATE OR REPLACE FUNCTION f_describe_tables (v_tbl_scm text, 
> v_tbl_name text,
> mydb(> OUT  text, OUT text) as
> mydb-> $$ SELECT c.column_name, c.data_type
> mydb$> FROM information_schema.columns c
> mydb$> WHERE c.table_schema = 'v_tbl_schm' AND c.table_name = 'v_tbl_name'
> mydb$> $$ LANGUAGE SQL;

You've hardcoded the strings 'v_tbl_schm' and 'v_tbl_name' instead
of using the function's arguments.  I don't think SQL functions
support named arguments so you'll need to use $1 and $2.  You'll
also need to use "RETURNS SETOF record" if you want to return more
than one row.

-- 
Michael Fuhr

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

Re: [GENERAL] Create function problem

[gustavo halperin]

Ron St-Pierre wrote:

Check your spelling of public:
SELECT * FROM f_describe_tables('pubilc', 'mil_cien_diez');

Ron

*OK thank you, you right, but after write "public" I receive again an 
empty row, Why??.

By the way I wrote a short function:*

/mydb=> SELECT c.column_name, c.data_type
mydb-> FROM information_schema.columns c
mydb-> WHERE c.table_schema = 'public' AND c.table_name = 'mil_cien_diez';
column_name | data_type
-+---
miles   | smallint
cientos | smallint
decenas | smallint
(3 rows)

mydb=> CREATE OR REPLACE FUNCTION f_describe_tables (v_tbl_scm text, 
v_tbl_name text,

mydb(> OUT  text, OUT text) as
mydb-> $$ SELECT c.column_name, c.data_type
mydb$> FROM information_schema.columns c
mydb$> WHERE c.table_schema = 'v_tbl_schm' AND c.table_name = 'v_tbl_name'
mydb$> $$ LANGUAGE SQL;
CREATE FUNCTION
mydb=> SELECT * FROM f_describe_tables('public', 'mil_cien_diez');
column1 | column2
-+-
|
(1 row)
/

---(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] Create function problem

[Michael Fuhr]

On Fri, Aug 04, 2006 at 01:51:19AM +0300, gustavo halperin wrote:
> In order to know the names and data types of the table "mil_cien_diez" 
> from the schema "public" I run the next 'SELECT' but when I try to 
> create a SQL function and after it I run it, I receive an empty row. Can 
> you see the problem ??*
[...]
> mydb=> SELECT * FROM f_describe_tables('pubilc', 'mil_cien_diez');

'public' looks misspelled.  Does the query work if you change it?

-- 
Michael Fuhr

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

   http://archives.postgresql.org

Re: [GENERAL] Best Procedural Language?

[John Sidney-Woollett]

I'd say that the biggest benefit of pl/pgsql for postgres is that it is 
so close to Oracle's own procedural language. This makes the job of 
porting from Oracle to postgres *nearly* trivial.


Convincing a site to switch from Oracle to Postgres is therefroe easier 
and a major feather in postgres's cap.


Working with both Oracle 8,9,10 and postgres 7.4 and 8, I find switching 
between the two dbs fairly easy. Oracle is richer in terms of 
(programming) features but bang for buck and ease of 
administration/setup etc, you just can't beat postgres...


John

Merlin Moncure wrote:

On 8/1/06, Christopher Browne <[EMAIL PROTECTED]> wrote:

Martha Stewart called it a Good Thing when "Carlo Stonebanks" 
<[EMAIL PROTECTED]> wrote:

> I am interested in finding out a "non-religious" answer to which
> procedural language has the richest and most robust implementation
> for Postgres. C is at the bottom of my list because of how much
> damage runaway code can cause. I also would like a solution which is
> platorm-independent; we develop on Windows but may deploy on Linux.



my take:
C:
you can probably get by without doing any C. Most (but not quite all)
of things you would do via C is exposed in libraries.  One thing you
can do with C for example is invoke a function via its oid and
manually supplying parameters to make callbacks for proceures.  you
can also dump core on your backend. good luck!

pl/pgsql:
you do not know postgresql if you do not know pl/pgsql. period. ideal
for data processing and all sorts of things.  all queries are first
class in the code (except for dynamic sql), which in my estimation
cuts code size, defect rate, and development time about 75% for
typical database type stuff.  just be warned, after you learn it you
will never want to use another database ever again, i'm not kiddig.

pl/perl, etc:
not much to add beyond what chris browe said: great for text
processing or library support.

merlin

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

  http://archives.postgresql.org


---(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] Importance of re-index

[Tom Lane]

Scott Marlowe <[EMAIL PROTECTED]> writes:
> Reindex was originally
> designed to fix broken indexes, and, at least in earlier encarnations,
> should something stop it in the middle of reindexing I believe it is
> possible to be left with no index.

That was once true but these days reindex is perfectly crash-safe.  The
only case where it's not is where you want to reindex a shared catalog's
index (eg one of pg_database's), and we don't let you do that in
multiuser mode anyway.

regards, tom lane

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

Re: [GENERAL] Importance of re-index

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Scott Marlowe wrote:
> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
>> In addition to making sure databases are vacuumed regularly, it is worth 
>> running REINDEX on tables that see a lot of updates (or insert/deletes).
>>
>> Running REINDEX on a regular basis will keep the indexes compacted and 
>> can noticeably improve the database performance.
>>
>> The other benefit is that the disk space taken by your database can be 
>> significantly reduced.
>>
>> This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.
>>
>> FWIW, in my experience it is DEFINITELY worth reindexing regularly.
> 
> But note that reindex is one of those "invasive" commands that may cause
> problems for certain types of 24/7 operations, while vacuum is meant to
> run concurrently almost any time of day.  Reindex was originally
> designed to fix broken indexes, and, at least in earlier encarnations,
> should something stop it in the middle of reindexing I believe it is
> possible to be left with no index.
> 
> It's got its uses, but it's got its issues as well.

It certainly is simpler to write
REINDEX INDEX foo;
than to write
DROP INDEX foo;
CREATE INDEX foo BLAH BLAH BLAH.

- --
Ron Johnson, Jr.
Jefferson LA  USA

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

iD4DBQFE0n8hS9HxQb37XmcRAvHZAKCf/Orza4TboRxYiqys/VngHzpKCACY8i1t
suyTDb+FMnTN6jL3fi80bg==
=MGqB
-END PGP SIGNATURE-

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

[GENERAL] Create function problem

[gustavo halperin]

*Hello I have a little question

In order to know the names and data types of the table "mil_cien_diez" 
from the schema "public" I run the next 'SELECT' but when I try to 
create a SQL function and after it I run it, I receive an empty row. Can 
you see the problem ??*


/mydb=> SELECT c.column_name, c.data_type, e.data_type AS element_type
mydb-> FROM information_schema.columns c LEFT JOIN 
information_schema.element_types e
mydb->   ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', 
c.dtd_identifier)
mydb-> = (e.object_catalog, e.object_schema, e.object_name, 
e.object_type, e.array_type_identifier))

mydb->  WHERE c.table_schema = 'public' AND c.table_name = 'mil_cien_diez'
mydb-> ORDER BY c.ordinal_position;
column_name | data_type | element_type
-+---+--
miles   | smallint  |
cientos | smallint  |
decenas | smallint  |
(3 rows)

/
*The "CREATE FUNCTION" code is the next :*

/mydb=> CREATE OR REPLACE FUNCTION f_describe_tables (text, text
mydb(> ,OUT  text, OUT text, OUT text) as
mydb-> $$ SELECT c.column_name, c.data_type, e.data_type AS element_type
mydb$> FROM information_schema.columns c LEFT JOIN 
information_schema.element_types e
mydb$>   ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', 
c.dtd_identifier)
mydb$> = (e.object_catalog, e.object_schema, e.object_name, 
e.object_type, e.array_type_identifier))

mydb$> WHERE c.table_schema = $1 AND c.table_name = $2
mydb$> ORDER BY c.ordinal_position;
mydb$> $$ LANGUAGE SQL;
CREATE FUNCTION
mydb=> SELECT * FROM f_describe_tables('pubilc', 'mil_cien_diez');
column1 | column2 | column3
-+-+-
| |
(1 row)


/
*Thank you,
  Gustavo
*

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

  http://archives.postgresql.org

Re: [GENERAL] Importance of re-index

[Scott Marlowe]

On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
> In addition to making sure databases are vacuumed regularly, it is worth 
> running REINDEX on tables that see a lot of updates (or insert/deletes).
> 
> Running REINDEX on a regular basis will keep the indexes compacted and 
> can noticeably improve the database performance.
> 
> The other benefit is that the disk space taken by your database can be 
> significantly reduced.
> 
> This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.
> 
> FWIW, in my experience it is DEFINITELY worth reindexing regularly.

But note that reindex is one of those "invasive" commands that may cause
problems for certain types of 24/7 operations, while vacuum is meant to
run concurrently almost any time of day.  Reindex was originally
designed to fix broken indexes, and, at least in earlier encarnations,
should something stop it in the middle of reindexing I believe it is
possible to be left with no index.

It's got its uses, but it's got its issues as well.

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

   http://archives.postgresql.org

[GENERAL] Importance of re-index

[John Sidney-Woollett]

In addition to making sure databases are vacuumed regularly, it is worth 
running REINDEX on tables that see a lot of updates (or insert/deletes).


Running REINDEX on a regular basis will keep the indexes compacted and 
can noticeably improve the database performance.


The other benefit is that the disk space taken by your database can be 
significantly reduced.


This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.

FWIW, in my experience it is DEFINITELY worth reindexing regularly.

John

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

Re: [GENERAL] CREATE DATABASE

[Nikolay Samokhvalov]

Thanks. 'connect to' cannot be sent to server as plain text, surely.
I'm stupid :-(


On 8/4/06, Stephan Szabo <[EMAIL PROTECTED]> wrote:


On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote:

> On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:
> [...]
> > BTW, difference vanishes due to expression power of SQL -
> > it supports session comands in the same context as DDL commands and
> > data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
> > statements').
>
> Sorry, I already see my failure. It is in the fact that Postgres
> doesn't support that connection statements yet (but some connection
> things are supported - like changing the CURRENT_ROLE).
>
> So, my previous message is interesting only from theoretical point of
> view. I always try to thing in the manner of SQL standard or use
> knowledge from books/university ...
>
> But the logic is clear, isn't it? Connection is not client operation.

I'd read 4.39 differently which seems to imply that the SQL-client handles
the connection statements.




--
Best regards,
Nikolay

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

Re: [GENERAL] CREATE DATABASE

[Stephan Szabo]

On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote:

> On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:
> [...]
> > BTW, difference vanishes due to expression power of SQL -
> > it supports session comands in the same context as DDL commands and
> > data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
> > statements').
>
> Sorry, I already see my failure. It is in the fact that Postgres
> doesn't support that connection statements yet (but some connection
> things are supported - like changing the CURRENT_ROLE).
>
> So, my previous message is interesting only from theoretical point of
> view. I always try to thing in the manner of SQL standard or use
> knowledge from books/university ...
>
> But the logic is clear, isn't it? Connection is not client operation.

I'd read 4.39 differently which seems to imply that the SQL-client handles
the connection statements.

---(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] PITR Questions

[Matthew T. O'Connor]

I'm setting up PITR for a client and have a few questions.

I have done some googling for real world archive_command examples and 
haven't really found anything.  The example in the PGSQL Docs are 
qualified by (This is an example, not a recommendation, and may not work 
on all platforms.)


I have it set as follows:
archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f'

Any comments as to whether or not this is a *good* choice?


Also, I'm concerned that this clients website has extended periods of 
time where it's very low traffic, which will result in the same WAL file 
being used for long periods of time and not getting archived.  Does 
anyone have a tested script available for grabbing the most recent WAL 
file?  I can write one myself, but it seems this is information that 
should be posted somewhere.


Thanks,

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

  http://archives.postgresql.org

Re: [GENERAL] SAVEPOINT and FOR UPDATE

[Jeff Davis]

On Thu, 2006-08-03 at 12:43 -0500, Thomas F. O'Connell wrote:
> I'm curious to know more about the postgres implementation of
> subtransactions via SAVEPOINT.
> 

Locks are held until the end of the outer transaction, see:



in the first paragraph it reads:

"Once obtained, the lock is held for the remainder of the current
transaction. (There is no UNLOCK TABLE command; locks are always
released at transaction end.)"

If you want to release a lock before completing more tasks, you should
use two transactions. If a subtransaction could create and release locks
before the outer transaction finished, that would violate the ACID
properties of the outer transaction.

> postgres=# CREATE TABLE updateable1 ( id int primary key );
> postgres=# INSERT INTO updateable1 VALUES ( 1 );
> postgres=# START TRANSACTION;
> postgres=# SAVEPOINT u1;
> postgres=# SELECT id FROM updateable1 WHERE id = 1 FOR UPDATE;
> 

[ snip ]

> 
> postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;
> postgres=# RELEASE u1;
> 

This RELEASE makes it as though you never created a SAVEPOINT. 

[ snip ]

> I'd like a method for doing the following:
> 
> 
> START TRANSACTION;
> // do work
> // start subtransaction
> SELECT ... FOR UPDATE;
> UPDATE ...;
> // commit subtransaction
> // do more work
> COMMIT;
> 

What you are trying to do violates ACID because the work done while the
lock was held has not actually completed (because the outer transaction
has not committed). 

Regards,
Jeff Davis



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

Re: [GENERAL] Well, Pervasive is now out....

[Joe Audette]

My guess is what this really means is they weren't making money on it. Its a 
community friendly spin to suggest that community support is so good that not 
many companies will pony up for commercial support. My guess is that its fairly 
accurate though. A company is only going to use postgreSQL if their dba is 
behind it and for that to be the case the dba is probably pretty comfortable 
with their own knowledge backed by community support.
If its not the dba who is promoting pgsql in the company then it is likely an 
exec who sees it as a way to save money and likely doesn't want to pay for 
support for a free product.

Joe
 
 
joe_audette [at] yahoo dotcom 
http://www.joeaudette.com 
http://www.mojoportal.com

- Original Message 
From: Nikolay Samokhvalov <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Thursday, August 3, 2006 2:58:03 PM
Subject: Re: [GENERAL] Well, Pervasive is now out

On 8/3/06, Tony Caduto <[EMAIL PROTECTED]> wrote:
> http://news.zdnet.com/2100-3513_22-6100795.html

Let's believe that that was the real reason... :-)

"
...
In a letter to the PostgreSQL community of developers, Pervasive
Software President John Farr said last week that the company
"underestimated the high level of quality support and expertise
already available within the PostgreSQL community."
...
"

-- 
Best regards,
Nikolay

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





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

   http://archives.postgresql.org

Re: [GENERAL] CREATE DATABASE

[Nikolay Samokhvalov]

On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:
[...]

BTW, difference vanishes due to expression power of SQL -
it supports session comands in the same context as DDL commands and
data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
statements').


Sorry, I already see my failure. It is in the fact that Postgres
doesn't support that connection statements yet (but some connection
things are supported - like changing the CURRENT_ROLE).

So, my previous message is interesting only from theoretical point of
view. I always try to thing in the manner of SQL standard or use
knowledge from books/university ...

But the logic is clear, isn't it? Connection is not client operation.
I think that Rafal's proposal is quite interesting (I experience the
same difficulties every time. There was several wrong DROP DATABASE in
my career... :-) )

--
Best regards,
Nikolay

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

Re: [GENERAL] Well, Pervasive is now out....

[Nikolay Samokhvalov]

On 8/4/06, J B <[EMAIL PROTECTED]> wrote:

On 8/3/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:
> Let's believe that that was the real reason... :-)

If not, what was? Is this really a commentary on how many
"enterprise"-y types are using Postgres?



I'm afraid that many people read only the titles of abstract of news
(RSS-readers or diggers, etc) --> do not see that letter and that
words --> so they would think that this is a proof of that open-source
model is unworkable.

--
Best regards,
Nikolay

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

  http://archives.postgresql.org

Re: [GENERAL] Well, Pervasive is now out....

[J B]

On 8/3/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:

Let's believe that that was the real reason... :-)


If not, what was? Is this really a commentary on how many
"enterprise"-y types are using Postgres?

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

Re: [GENERAL] CREATE DATABASE

[Nikolay Samokhvalov]

On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote:

The reason neither of these are going to happen is that you're confusing
a server-side SQL command (viz CREATE DATABASE) with a client-side
operation (viz choosing to drop the server connection and make a new one
to a different database).


Hmm.. I always thought that creating connection is two-sided operation
(client sends requests, server accepts and sends that all is OK,
including that client sent proper user/pwd and wants to connect to
proper DB).

You're breaking my knowledge of client-server architecture.

What is the difference between the procedure of connection
establishing and database creation from user POV? Client asks server
and server does some work and then replies. What kind of work?
Permissions checking or creating of DB - yes, very different things,
but the difference is significant for backend! Notice, that for client
there is no such huge difference. The only difference is the order of
operations. BTW, difference vanishes due to expression power of SQL -
it supports session comands in the same context as DDL commands and
data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
statements').

If I'm wrong, I'd be very glad to see your contradiction.

--
Best regards,
Nikolay

---(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] SAVEPOINT and FOR UPDATE

[Tom Lane]

"Thomas F. O'Connell" <[EMAIL PROTECTED]> writes:
> I sort of expected the release of the savepoint to be tantamount to a  
> commit of the subtransaction, but it doesn't appear to have been.

But you still haven't committed the outer transaction: rolling it back
must undo the effects of the subtransaction, no?  So a "release" merely
destroys the separate identity of the subtransaction and assigns all its
effects (including locks) to the parent transaction.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Well, Pervasive is now out....

[Scott Marlowe]

I found this comment particularly telling:

Pervasive Software President John Farr said last week that the company
"underestimated the high level of quality support and expertise already
available within the PostgreSQL community."

I don't think I can add anything to that.

---(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] CREATE DATABASE

[Tom Lane]

Rafal Pietrak <[EMAIL PROTECTED]> writes:
> To put it the other way around: I don't remember me creating a database
> and *needing* to stay within the initiator (like template1) - that's why
> I've ventured the feature request. If not a 'CONNECT' option, may be z
> "SET parameter" for a session user?

The reason neither of these are going to happen is that you're confusing
a server-side SQL command (viz CREATE DATABASE) with a client-side
operation (viz choosing to drop the server connection and make a new one
to a different database).  The server cannot force the client to do
that, and we're unlikely to look favorably on adding syntax that the
server is supposed to ignore while the client starts parsing every
command to see if it's in there.

You could imagine inventing a psql operation like

\create_and_connect_to dbname ... other createdb parameters ...

but it still seems more like a kluge than a useful feature.

regards, tom lane

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

Re: [GENERAL] PITR timeline question

[Tom Lane]

Jim Nasby <[EMAIL PROTECTED]> writes:
> If you're using multiple timelines with PITR, do you have to re-copy  
> all the data files into the cluster every time you start a new  
> timeline? Or can you copy the data files out of the backup once, and  
> then perform multiple recoveries, each to different timelines?

The only way to "roll time backwards" is to replace the data directory
with a backup, so if I understand your question correctly, the answer
is no.

regards, tom lane

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

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

Re: [GENERAL] Well, Pervasive is now out....

[Nikolay Samokhvalov]

On 8/3/06, Tony Caduto <[EMAIL PROTECTED]> wrote:

http://news.zdnet.com/2100-3513_22-6100795.html


Let's believe that that was the real reason... :-)

"
...
In a letter to the PostgreSQL community of developers, Pervasive
Software President John Farr said last week that the company
"underestimated the high level of quality support and expertise
already available within the PostgreSQL community."
...
"

--
Best regards,
Nikolay

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

Re: [GENERAL] Well, Pervasive is now out....

[Joshua D. Drake]

Tony Caduto wrote:

http://news.zdnet.com/2100-3513_22-6100795.html



Week late bud :) that was announced during OSCON.

Joshua D. Drake


--

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



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

Re: [GENERAL] pg_xlog not cleaned up

[Tom Lane]

Wayne Conrad <[EMAIL PROTECTED]> writes:
> On Mon, Jul 31, 2006 at 10:18:31PM +0200, Christian Kastner wrote:
>> After the online backup script runs, all subsequent attempts to run
>> archive_command fail because the first thing it tries to archive away is
>> the still-existing *.backup file. This fails because a copy already
>> exists in the archive area and the script refuses to overwrite the
>> existing file.

> I'm seeing this too.  I thought I was doing something wrong.

I'm not entirely convinced by Simon's theory that this is already fixed,
because there are some points that you didn't mention.

First off, the thing isn't *supposed* to remove the latest .backup file;
that one, and its .done file, should stick around until your next
successful pg_stop_backup call.  pg_stop_backup removes all but the
just-created .backup file (if they have .done files, showing they've
been archived).  No other operation touches them at all.

Second, the bug Simon mentions consists in trying to create the .ready
file for the new .backup file twice during pg_stop_backup.  If the
archiver is fast enough to have pushed out the .backup file already,
this would result in there being both a .ready and a .done file for the
.backup file.  If that's what you see, then manually removing the .ready
file should clear the problem --- I wouldn't recommend manually removing
either .done or .backup.  If that's *not* what you see, then we have
another problem to deal with.  Please report back.

regards, tom lane

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

[GENERAL] Well, Pervasive is now out....

[Tony Caduto]

http://news.zdnet.com/2100-3513_22-6100795.html


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(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] CREATE DATABASE

[Rafal Pietrak]

On Thu, 2006-08-03 at 18:07 +0100, Richard Huxton wrote:
> Rafal Pietrak wrote:
> > When working on a new database application I quite frequently:
> > ---
> > test_xx# \c template1
> > template1# DROP DATABASE test_xx;
> > template1# CREATE DATABASE test_xx TEMPLATE = earlier_version;
> > template1# \c test_xx
> > template1# \i some_cooked_dataset.sql-dump
> > ---
> 
> The quickest solution is to put the whole thing in one script and 
> execute that with \i or -f from the command-prompt.

Well, the trick is, that when I refere back to previous CREATE DATABASE,
I quite often adjust it: name or owner, or template.

To put it the other way around: I don't remember me creating a database
and *needing* to stay within the initiator (like template1) - that's why
I've ventured the feature request. If not a 'CONNECT' option, may be z
"SET parameter" for a session user? This way pg_dumpall, which does
CREATE DATABASE and stays with the old one, since it is supposed to be
run as user postgres will continue to work correctly.

Frankly, when posting the original e-mail I feared to receive: "NO!!
that'll break a lot of scripts/applications". But if that's not the
case, I'd vote to put this request into (event very low priority)
to-be-implementes list. postgres is awesome to work with, part of the
quality of a 'product' is good set of defaults - having "CREATE DB"
connect to the new instance is GoodThing(tm) :).

just my 2c. not that I'm eager to elaborate this case any further.

Thenx.
-- 
-R

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

Re: [GENERAL] PostgreSQL on RAM Disk / tmpfs

[Merlin Moncure]

On 8/2/06, Thomas F. O'Connell <[EMAIL PROTECTED]> wrote:

I'm working on a postgres instance (8.1.2 running on Solaris 10) where the
data directory (including WAL) is being mounted on tmpfs. Based on this, and
with knowledge that fsync is disabled, I'm operating under the assumption
that recoverability is not a precondition for optimized performance. With
that assumption, I have several questions, some performance-related, others
internals-related:


to be honest, I think the best approach is to simply write to the
traditional filesystem and leave fsync off.  writing to a ramdisk
might be a bit faster, but you deprive the server memory from doing
other things like caching and sorting.  this might be more true for
some o/s than others though.  i'm just curious, what led you to do
ramdisk implementation  (most people who ask questions about ramdisk
have no idea what they are talking about, although you seem to).


4. Considering that recoverability is not a precondition, is there an easy
patch that could be applied to the 8.1.x series from 8.1.4 on that would
allow disabling full_page_writes? For a database in RAM with high write
volume, is this setting even likely to make a difference?


I would suggest pulling 8.2dev (shortly beta) if you want this and
experiment. it is perfectly stable.  looking at the todo list, 8.2
also gets the multiple insert syntax, which is nice.

if have super high write volumes, consider writing your insert call in
C. prepare your statement, and use the parameterized
versionExecPrepared(...).

merlin

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

[GENERAL] PITR timeline question

[Jim Nasby]

If you're using multiple timelines with PITR, do you have to re-copy  
all the data files into the cluster every time you start a new  
timeline? Or can you copy the data files out of the backup once, and  
then perform multiple recoveries, each to different timelines?


Ultimately, what I'd like to be able to do is periodically bring a  
'PITR slave' live. I know I can do this by first copying the base  
files and then recovering every time, but it would be nice if I  
didn't have to actually copy the base files first.

--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461



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

[GENERAL] SAVEPOINT and FOR UPDATE

[Thomas F. O'Connell]

I'm curious to know more about the postgres implementation of subtransactions via SAVEPOINT.If I wanted to set up a multi-statement transaction in which I needed multiple SELECT ... FOR UPDATE + UPDATE blocks, it would seem advantageous to be able to combine the SELECT ... FOR UPDATE clauses with the corresponding UPDATE clauses in a subtransaction in order to avoid locking rows for the duration of the entire outer transaction. In my experimentation, I'm not seeing this behavior, so I'm wondering if I'm misreading or overlooking something in the docs about how to use SAVEPOINT to create subtransactions.Here's what I set up as a basic test case in psql:postgres=# CREATE TABLE updateable1 ( id int primary key );postgres=# INSERT INTO updateable1 VALUES ( 1 );postgres=# START TRANSACTION;postgres=# SAVEPOINT u1;postgres=# SELECT id FROM updateable1 WHERE id = 1 FOR UPDATE;Then, in a separate session, I do this:postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;This, appropriately, waits.In the original session, I now do this:postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;postgres=# RELEASE u1;Unfortunately, the second session is still waiting and continues to do so until I commit the transaction started in the first session. I sort of expected the release of the savepoint to be tantamount to a commit of the subtransaction, but it doesn't appear to have been.I'd like a method for doing the following:START TRANSACTION;// do work// start subtransactionSELECT ... FOR UPDATE;UPDATE ...;// commit subtransaction// do more workCOMMIT;Is there any way to achieve the behavior I'm after? --Thomas F. O'ConnellSitening, LLChttp://www.sitening.com/3004B Poston AvenueNashville, TN 37203-1314615-469-5150 x802615-469-5151 (fax) 

[GENERAL] Pl/TCL: Is my Windows install missing scripts?

[Carlo Stonebanks]

The Windows-PosgreSQL 8.1.4 package I downloaded from the PostgreSQL 
download site (right here) does not seem to contain the scripts:
pltcl_delmod

pltcl_listmod

pltcl_loadmod

Have I missed something, or can anyone else confirm?

Carlo



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

Re: [GENERAL] Fulltime Opportunities in Silicon Valley!

[Richard Huxton]

Maryellen wrote:

Hi all,
I have a few clients that are looking for Database Developers (not
DBA's) with a focus on MS databases (SQL in particular). All are for 8+
year people. The need is high and I would like to help to make some
matches, if appropriate. I have a solid company, a midsized company and
a startup. Any interested party's can email me at
[EMAIL PROTECTED] for more info. or you can call me at
415-298-1926.
Thanks! And if this is not the proper forum to advise specialized
Database folks, please let me know, I do not mean to intrude or offend.
Maryellen O'Connell


Hi Maryellen - this is almost certainly the wrong forum. There are two 
important reasons why:


1. There is a pgsql-jobs list that is the place to post job advertisements.
2. The best place to look for people with "Microsoft" experience would 
probably be a "Microsoft" forum. Since this is a "PostgreSQL" mailing 
list you'll tend to find "PostgreSQL" people on it. You can find out 
more about the PostgreSQL RDBMS and community at the main website:

  http://www.postgresql.org/

Hope that helps, and best of luck in your search!

PS - The Internet covers many countries, you might want to make sure 
your phone numbers etc. are formatted appropriately.
PPS - I always think it looks more professional to use a company address 
in work-related emails. Also, consider a link to your website in your 
email. It makes it easier for people to forward contacts to you.


HTH
--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] xmin

[Richard Huxton]

Ilja Golshtein wrote:

Hello!

Is it Ok to use xmin column in application programm?

Purpose is obvios: I need to know the record was not changed since
a moment in the past and things like this. 
So every UPDATE should somehow change value of the column.


The choice it trigger or xmin.
Which is better (from perfomance, reliability, compartibility,
whatever, point of view)?


The only problem with using xmin is that it's not portable. You won't 
find it in Mysql/Oracle etc.


--
  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] Vacuum, Analyze, ReIndex from within VB.NET 2003

[Richard Huxton]

Rohit Prakash Khare wrote:

I want to use the following features of PostgreSQL from within VB.NET 2003:

Vacuum, Analyze, ReIndex.

Is there any way to write a VB.NET code to do the following tasks?


Is there some reason why you can't issue SQL with "VACCUM", "ANALYSE" 
and "REINDEX"?


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] CREATE DATABASE

[Richard Huxton]

Rafal Pietrak wrote:

Hi All,

I'd like to cast a small 'feature request' here for discussion/
evaluation. The case is the following:

When working on a new database application I quite frequently:
---
test_xx# \c template1
template1# DROP DATABASE test_xx;
template1# CREATE DATABASE test_xx TEMPLATE = earlier_version;
template1# \c test_xx
template1# \i some_cooked_dataset.sql-dump
---

All this is of cource issued from psql utility, and using psql_history
buffer.

Now, the thing is, that I have to be very carefull and focused when
going back in psql_history, as several times I've skiped the "\c
test_xx" stage  and ended up in a mass.


The quickest solution is to put the whole thing in one script and 
execute that with \i or -f from the command-prompt.


--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] xmin

[Ilja Golshtein]

Hello!

Is it Ok to use xmin column in application programm?

Purpose is obvios: I need to know the record was not changed since
a moment in the past and things like this. 
So every UPDATE should somehow change value of the column.

The choice it trigger or xmin.
Which is better (from perfomance, reliability, compartibility,
whatever, point of view)?

Thanks.

-- 
Best regards
Ilja Golshtein

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

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

[GENERAL] Vacuum, Analyze, ReIndex from within VB.NET 2003

[Rohit Prakash Khare]

I want to use the following features of PostgreSQL from within VB.NET 2003:

Vacuum, Analyze, ReIndex.

Is there any way to write a VB.NET code to do the following tasks?

Sign Up for your FREE eWallet at www.wallet365.com


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

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

[GENERAL] CREATE DATABASE

[Rafal Pietrak]

Hi All,

I'd like to cast a small 'feature request' here for discussion/
evaluation. The case is the following:

When working on a new database application I quite frequently:
---
test_xx# \c template1
template1# DROP DATABASE test_xx;
template1# CREATE DATABASE test_xx TEMPLATE = earlier_version;
template1# \c test_xx
template1# \i some_cooked_dataset.sql-dump
---

All this is of cource issued from psql utility, and using psql_history
buffer.

Now, the thing is, that I have to be very carefull and focused when
going back in psql_history, as several times I've skiped the "\c
test_xx" stage  and ended up in a mass.

Now, the solution looks simple: if only "CREATE DATABASE" had an
additional keyword, like CONNECT, meaning "\c " after successful
DB creation, the execution a similar sequence from history buffer, would
be much safer.

So I'd like to express here this 'feature request' - Regretably, I'm not
quite up to implementing such feature myself, but may be someone can?
Comments?

-- 
-R

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

   http://archives.postgresql.org

Re: [GENERAL] Best Procedural Language?

[Carlo Stonebanks]

Joshua, where can I find docs on how to return SETOF from Tcl?


""Joshua D. Drake"" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>
>>>  - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such
>>>requires having an extra level of function manipulations that
>>>won't be as natural as straight pl/pgsql.
>>
>> Another important distinguishing characteristic is whether it supports
>> set returning functions.  I think only plpgsql does right now.
>
> Actually no. Plperl, plphp, plruby and I believe even pl/Tcl support set 
> returning functions.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>>
>> ---(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
>>
>
>
> -- 
>
>=== The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>Providing the most comprehensive  PostgreSQL solutions since 1997
>  http://www.commandprompt.com/
>
>
>
> ---(end of broadcast)---
> TIP 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
> 



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

   http://archives.postgresql.org

Re: [GENERAL] Performance of the listen command

[Alvaro Herrera]

Flemming Frandsen wrote:
> Christopher Browne wrote:
> >There's a demerit:
> >c) If there are a LOT of events, that might not fit in memory nicely.
> 
> If you have that many events then the current implementation is going to 
> suck hard as well:)

The difference is that the current implementation *works* regardless of
the number of active listeners.  With the in-memory idea, you might have
to drop some listeners in order to make them fit in memory, which makes
that a non-starter, unless you find a solution to shave some stuff to
disk.  That's where the efficiency argument kicks in.

This is a bit worse than it sounds because the memory we are talking
about is shared memory, which cannot be grown after the server started
(like you can with the kind of memory served by malloc()).

Also, some people want the ability to stash messages with each NOTIFY.
This makes the whole idea a lot more complicated.

What this means is that nobody has tried *really hard* to make it work
(really hard meaning, enough so that it actually works).  Neil Conway
had some nice ideas but I don't think they were ever fully realized.

If you want to contribute, you're more than welcome.  You're far from
alone in wanting this thing "fixed."

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


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

Re: [GENERAL] pg_restore performance on solaris 10/6

[Thomas Burns]

On Aug 1, 2006, at 8:52 AM, Vivek Khera wrote:



On Jul 31, 2006, at 3:45 PM, Thomas Burns wrote:

our database literally takes 8 times longer on the x4200 as it  
does on
OS X (the x4200 hardware should be considerably faster -- it has  
better


for a restore like this, bump up the value of checkpoint_segments  
to some large value (I use 256 -- but I have a dedicated partition  
for the pg_xlog directory which gets big).


will give that a shot



also, the normal tuning of the shared memory settings apply.  how  
much RAM do you have and what kind of disks are you using?


I have 5 shared buffers and 1 temp buffers (the machine has  
16G ram).   The disks are new/fast SCSI drives







Thomas E. Burns
Co-Founder/CTO, Schoolloop.com
http://www.schoolloop.com
[EMAIL PROTECTED]
415.255.7285




---(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] LISTEN considered dangerous

[Ian Harding]

On 8/2/06, Flemming Frandsen <[EMAIL PROTECTED]> wrote:

Ian Harding wrote:
> NOTIFY interacts with SQL transactions in some important ways.
> Firstly, if a NOTIFY is executed inside a transaction, the notify
> events are not delivered until and unless the transaction is
> committed. This is appropriate, since if the transaction is aborted,
> all the commands within it have had no effect, including NOTIFY. But
> it can be disconcerting if one is expecting the notification events to
> be delivered immediately.

Yes, that's very nice, but it doesn't have *anything* to do with what I
posted about.



Quite true, but it does indicate, to me at least, the fact that this
is a SQL command and doesn't take effect until committed.


From what I read in the docs, I would expect the NOTIFY signals to be

like phone calls, if your phone's not plugged in (LISTEN not
committed) you miss the call.  That's the way it works apparently.


I'm bothered by listen listening from the end of the transaction in
stead of the start of the transaction.



What seems to be needed is an answering service that will record your
NOTIFY events, in case you decide to plug in the phone and retrieve
them.

- Ian

---(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] Fulltime Opportunities in Silicon Valley!

[Maryellen]

Hi all,
I have a few clients that are looking for Database Developers (not
DBA's) with a focus on MS databases (SQL in particular). All are for 8+
year people. The need is high and I would like to help to make some
matches, if appropriate. I have a solid company, a midsized company and
a startup. Any interested party's can email me at
[EMAIL PROTECTED] for more info. or you can call me at
415-298-1926.
Thanks! And if this is not the proper forum to advise specialized
Database folks, please let me know, I do not mean to intrude or offend.
Maryellen O'Connell


---(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] proper use of array datatype

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-08-02 10:49:01 -0700:
> On 8/1/06, Reece Hart <[EMAIL PROTECTED]> wrote:
> >
> > Eric Andrews wrote:
> >> I am not much of a schema designer and have a general questoin about
> >> the proper use of the array datatype. In my example, I have
> >> destinations, and destinations can have multiple boxes, and inside
> >> those boxes are a set of contents. what I want to do is search and
> >> basically "mine" data from the content sets.
> >
> >I would use arrays exclusively for data sets for which each datum is
> >meaningless by itself (for example, a single coordinate in 3D, although
> >there are better ways to handle points in postgresql). I would recommend
> >against using arrays for any data you wish to mine, and instead recast 
> >these
> >has-a relationships as many-to-one joins across at least two tables. For
> >example, a row from the table destination has-a (joins to) rows from boxes,
> >and a box has-a (joins to) contents.
> >
> 
> 
> how would these tables look though? I cant have a table for each set of
> contents in a box...

You need to rotate your brains 90 degrees. You cant have a distinct
set of columns (a table) for each set, but you can have have a
distinct set of rows (a set) for each, ummm, set. The language suggests 
it's a better model, and indeed it is:

CREATE TABLE destination (
  destid SERIAL PRIMARY KEY,
  destname VARCHAR
  -- ...
);
CREATE TABLE box (
  boxid SERIAL PRIMARY KEY,
  destid INT REFERENCES destination (destid)
  -- ...
);
CREATE TABLE box_contents (
  boxid SERIAL REFERENCES box (boxid),
  thing TEXT
  -- ...
);

SELECT * FROM box_contents
JOIN box USING (boxid)
JOIN destination USING (destid)
WHERE destination.destname = 'foo';

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

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

Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump

[MargaretGillon]

Great, Thanks!
*** *** *** *** *** *** *** *** ***
*** *** *** *** *** *** *** *** *** *** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

This e-mail message and any attachment(s) are for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential information
which may be privileged or otherwise protected from disclosure.  Any
unauthorized review, use, disclosure or distribution is prohibited.  If
you are not the intended recipient(s), please contact the sender by reply
email and destroy the original message and any copies of the message as
well as any attachment(s) to the original message.

Richard Huxton  wrote on 08/03/2006
01:45:41 AM:

> Thomas F. O'Connell wrote:
> > 
> > 
> > On Aug 2, 2006, at 4:27 PM, [EMAIL PROTECTED] wrote:
> > 
> >> You're correct, I cannot use the pg_dump. I get a error message
that 
> >> the pg_dump is aborting because of a version mismatch, then
it says to 
> >> use the i opt. How do I call the pg_dump from the 8.1.4 version?
> > 
> > You'll probably need to specify an absolute path. Are both the
client 
> > and server applications running on the same server? If so, you
might 
> > need to get your consultant to tell you where the client applications

> > for the 8.1.4 installation were actually installed. By default,
postgres 
> > installs both client and server applications into /usr/local/pgsql,
but 
> > at least one of your installations must be in a different location;

> > otherwise, you probably couldn't have two versions of the postmaster

> > running concurrently.
> 
> Oh, and two other commands you might find useful:
> which pg_dump
>    Will show you which version of pg_dump is being run by
default
> alias
>    Will let you set up pg_dump81 as an alias for the correct
binary you want.
> 
> Man pages cover the details.
> 
> -- 
>    Richard Huxton
>    Archonet Ltd

Re: [GENERAL] pg_restore performance on solaris 10/6

[Vivek Khera]

On Aug 2, 2006, at 8:05 PM, Thomas Burns wrote:

I have 5 shared buffers and 1 temp buffers (the machine has  
16G ram).   The disks are new/fast SCSI drives


what's their configuration? RAID? if so, what controller and setup?   
if not, what controller and setup?


without all the details of your setup, it is impossible to offer  
suggestions.




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] How to read log files

[Andy Dale]

I have tried messing with the logging settings in postgres.conf but it always displays $1, i have set every level to debug 5 and logging all statements, but i still have had no success.Andy
On 8/3/06, Thomas F. O'Connell <[EMAIL PROTECTED]> wrote:
On Aug 3, 2006, at 8:33 AM, Andy Dale wrote:> I am currently trying to debug an issue we are experiencing and i> have turned the server logging onto debug level 5 and filtering out> the LOG messages, i still need some advice on how to interpret
> them.  I an extract from the log is:[snip]> The main difficulty i am having at the moment is knowing which> commit belongs to which statement (especially when you have 2 or> more statements), does it work like a stack  structure where the
> last statement is committed first, or more like a queue where that> statements are committed in the order in which the were issued.> Also as you can see from the above log extract it has a lot of $1
> and $2 is there anyway to print these out in the log as well ?Have you tried running with log_statement enabled? I find thatincredibly useful for tracking application behavior from the postgreslogs.
http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT--Thomas F. O'ConnellSitening, LLC
http://www.sitening.com/3004B Poston AvenueNashville, TN 37203-1314615-469-5150 x802615-469-5151 (fax)

Re: [GENERAL] How to read log files

[Thomas F. O'Connell]

On Aug 3, 2006, at 8:33 AM, Andy Dale wrote:

I am currently trying to debug an issue we are experiencing and i  
have turned the server logging onto debug level 5 and filtering out  
the LOG messages, i still need some advice on how to interpret  
them.  I an extract from the log is:


[snip]

The main difficulty i am having at the moment is knowing which  
commit belongs to which statement (especially when you have 2 or  
more statements), does it work like a stack  structure where the  
last statement is committed first, or more like a queue where that  
statements are committed in the order in which the were issued.   
Also as you can see from the above log extract it has a lot of $1  
and $2 is there anyway to print these out in the log as well ?


Have you tried running with log_statement enabled? I find that  
incredibly useful for tracking application behavior from the postgres  
logs.


http://www.postgresql.org/docs/8.1/static/runtime-config- 
logging.html#RUNTIME-CONFIG-LOGGING-WHAT


--
Thomas F. O'Connell
Sitening, LLC

http://www.sitening.com/
3004B Poston Avenue
Nashville, TN 37203-1314
615-469-5150 x802
615-469-5151 (fax)

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

[GENERAL] How to read log files

[Andy Dale]

Hi,I am currently trying to debug an issue we are experiencing and i have turned the server logging onto debug level 5 and filtering out the LOG messages, i still need some advice on how to interpret them.  I an extract from the log is:
2006-08-03 09:37:21.643 CEST  LOG:  statement: PREPARE  AS SELECT 12006-08-03 09:37:21.644 CEST  LOG:  statement: 2006-08-03 09:37:21.644 CEST  LOG:  statement: EXECUTE   [PREPARE:  SELECT 1]
2006-08-03 09:37:21.647 CEST  LOG:  statement: PREPARE S_1 AS BEGIN2006-08-03 09:37:21.648 CEST  LOG:  statement: 2006-08-03 09:37:21.648 CEST  LOG:  statement: EXECUTE   [PREPARE:  BEGIN]
2006-08-03 09:37:21.648 CEST  LOG:  statement: PREPARE  AS select nextval ('hibernate_sequence')2006-08-03 09:37:21.649 CEST  LOG:  statement: 2006-08-03 09:37:21.649 CEST  LOG:  statement: EXECUTE   [PREPARE:  select nextval ('hibernate_sequence')] //get the number in sequence (1)
2006-08-03 09:37:21.746 CEST  LOG:  statement: PREPARE  AS insert into SampleData (mBuffer, mID) values ($1, $2)2006-08-03 09:37:21.747 CEST  LOG:  statement: 2006-08-03 09:37:21.747
 CEST  LOG:  statement: EXECUTE   [PREPARE:  insert into SampleData (mBuffer, mID) values ($1, $2)]2006-08-03 09:37:21.761 CEST  LOG:  statement: PREPARE  AS SELECT 12006-08-03 09:37:
21.762 CEST  LOG:  statement: 2006-08-03 09:37:21.762 CEST  LOG:  statement: EXECUTE   [PREPARE:  SELECT 1]2006-08-03 09:37:21.763 CEST  LOG:  statement: PREPARE S_1 AS BEGIN2006-08-03 09:37:
21.764 CEST  LOG:  statement: 2006-08-03 09:37:21.764 CEST  LOG:  statement: EXECUTE   [PREPARE:  BEGIN]2006-08-03 09:37:21.764 CEST  LOG:  statement: PREPARE  AS select sampledata0_.mID as mID0_0_, sampledata0_.mBuffer as mBuffer0_0_ from SampleData sampledata0_ where sampledata0_.mID=$1
2006-08-03 09:37:21.766 CEST  LOG:  statement: 2006-08-03 09:37:21.767 CEST  LOG:  statement: EXECUTE   [PREPARE:  select sampledata0_.mID as mID0_0_, sampledata0_.mBuffer as mBuffer0_0_ from SampleData sampledata0_ where sampledata0_.mID=$1]
2006-08-03 09:37:21.784 CEST  LOG:  statement: PREPARE S_2 AS COMMIT2006-08-03 09:37:21.785 CEST  LOG:  statement: 2006-08-03 09:37:21.785 CEST  LOG:  statement: EXECUTE   [PREPARE:  COMMIT]
2006-08-03 09:37:21.796 CEST  LOG:  statement: PREPARE S_2 AS COMMIT2006-08-03 09:37:21.796 CEST  LOG:  statement: 2006-08-03 09:37:21.796 CEST  LOG:  statement: EXECUTE   [PREPARE:  COMMIT]
2006-08-03 09:37:21.827 CEST  LOG:  statement: 2006-08-03 09:37:21.827 CEST  LOG:  statement: EXECUTE   [PREPARE:  BEGIN]2006-08-03 09:37:21.827 CEST  LOG:  statement: PREPARE  AS SELECT 1
2006-08-03 09:37:21.828 CEST  LOG:  statement: 2006-08-03 09:37:21.828 CEST  LOG:  statement: EXECUTE   [PREPARE:  SELECT 1]2006-08-03 09:37:21.829 CEST  LOG:  statement: PREPARE  AS select nextval ('hibernate_sequence')
2006-08-03 09:37:21.829 CEST  LOG:  statement: 2006-08-03 09:37:21.829 CEST  LOG:  statement: EXECUTE   [PREPARE:  select nextval ('hibernate_sequence')]//get the number in sequence (2)
2006-08-03 09:37:21.850 CEST  LOG:  statement: 2006-08-03 09:37:21.850 CEST  LOG:  statement: EXECUTE   [PREPARE:  BEGIN]2006-08-03 09:37:21.850 CEST  LOG:  statement: PREPARE  AS SELECT 1
2006-08-03 09:37:21.851 CEST  LOG:  statement: 2006-08-03 09:37:21.851 CEST  LOG:  statement: EXECUTE   [PREPARE:  SELECT 1]2006-08-03 09:37:21.852 CEST  LOG:  statement: PREPARE  AS select sampledata0_.mID as mID0_0_, sampledata0_.mBuffer as mBuffer0_0_ from SampleData sampledata0_ where sampledata0_.mID=$1
2006-08-03 09:37:21.852 CEST  LOG:  statement: 2006-08-03 09:37:21.853 CEST  LOG:  statement: EXECUTE   [PREPARE:  select sampledata0_.mID as mID0_0_, sampledata0_.mBuffer as mBuffer0_0_ from SampleData sampledata0_ where sampledata0_.mID=$1]
2006-08-03 09:37:21.856 CEST  LOG:  statement: 2006-08-03 09:37:21.856 CEST  LOG:  statement: EXECUTE   [PREPARE:  COMMIT]2006-08-03 09:37:21.864 CEST  LOG:  statement: PREPARE  AS insert into SampleData (mBuffer, mID) values ($1, $2)
2006-08-03 09:37:21.864 CEST  LOG:  statement: 2006-08-03 09:37:21.865 CEST  LOG:  statement: EXECUTE   [PREPARE:  insert into SampleData (mBuffer, mID) values ($1, $2)]2006-08-03 09:37:
21.865 CEST  LOG:  statement: 2006-08-03 09:37:21.865 CEST  LOG:  statement: EXECUTE   [PREPARE:  COMMIT]2006-08-03 09:37:21.868 CEST  LOG:  statement: 2006-08-03 09:37:21.868
 CEST  LOG:  statement: EXECUTE   [PREPARE:  BEGIN]2006-08-03 09:37:21.868 CEST  LOG:  statement: PREPARE  AS SELECT 12006-08-03 09:37:21.868 CEST  LOG:  statement: 2006-08-03 09:37:
21.868 CEST  LOG:  statement: EXECUTE   [PREPARE:  SELECT 1]2006-08-03 09:37:21.869 CEST  LOG:  statement: PREPARE  AS select nextval ('hibernate_sequence')2006-08-03 09:37:21.869
 CEST  LOG:  statement: 2006-08-03 09:37:21.870 CEST  LOG:  statement: EXECUTE   [PREPARE:  select nextval ('hibernate_sequence')]//get the number in sequence (3)2006-08-03 09:37:21.875 CEST  LOG:  statement: 
2006-08-03 09:37:21.875 CEST  LOG:  statement: EXECUTE   [PREPARE:  BEGIN]2006-08-03 09:37:21.875 CEST  LOG:  state

Re: [GENERAL] TSearch: Need debug help

[Hannes Dorbath]

hmm, I don't like this. Why not create synonym dictionary as written on 
http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes


Because I found some more words with the same problem, and I have no 
idea how much there are in total :/


you need to reindex when you change dictionaries. 


I just tested with ts_debug() in a new session (dict was reloaded)..

On 03.08.2006 13:22, Oleg Bartunov wrote:

On Thu, 3 Aug 2006, Hannes Dorbath wrote:


but does 'dursten' is a some form of 'durst' ?


Yes it is.

Hm, even when I remove `dursten' and `durst' all together from the 
dict I still get `sen'.


hmm, I don't like this. Why not create synonym dictionary as written on 
http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes





How can I update a tsvector column stripping the `sen' lexem?


you need to reindex when you change dictionaries.



Thanks!

On 03.08.2006 12:54, Oleg Bartunov wrote:

Hannes,

I don't know german, sorry, but does 'dursten' is a some form of 
'durst' ?

Probably, here we have false hit from compound word support. I'd suggest
to use exclusion dictionary (on the base of synonym dictionary) 
before ispell. It could be very simple:

durst : durst


Oleg

On Thu, 3 Aug 2006, Hannes Dorbath wrote:


SELECT ts_debug('durst');
(default_german,lword,"Latin word",durst,"{de_ispell,de}","'dur' 
'sen'")


SELECT ts_debug('h?chsten');
(default_german,word,Word,h?chsten,"{de_ispell,de}","'sen' 'h?ch' 
'h?chst' 'h?chsten'")


For some reason both produce the lexem 'sen'. That leads to strange 
results. Search for `durst' will highlight `h?chsten' with headline().


Server is PG 8.0.4,
german snowball stemmer,
dictionary used is http://hannes.imos.net/german_iso.med
(From OpenOffice)

What causes some words to result in `sen', though they don't contain 
that lexem?


Thanks!




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 3: Have you checked our extensive FAQ?

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







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


--
imos  Gesellschaft fuer Internet-Marketing und Online-Services mbH
Alfons-Feifel-Str. 9 // D-73037 Goeppingen  // Stauferpark Ost
Tel: 07161 93339-14 // Fax: 07161 93339-99 // Internet: www.imos.net

---(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] Row-level style access with sub tables

[Richard Broersma Jr]

> In  a nutshell, then, I'd like my users to be able to alter the data
> in the tables, but only the data that they can see in the views, and
> only in a way so it remains in their views after modification.
> Has anyone got any advice?

I've seen Veil suggested for problems like yours. It is a PostgreSQL addon.

http://pgfoundry.org/projects/veil/

Regards,

Richard Broersma Jr.

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

[GENERAL] Row-level style access with sub tables

[David Craigon]

This a row-level security style question.
 
Suppose I have a hypothetical warehouse database. In my warehouse
everything is stored in a crate. My customers are going to rent crates
from me. I'm going to provide a way that they can keep track of what's
in each crate. Because my customers are very security conscious, I
don't want them to know what's in each other's crates.  
 
For this example I'm going to try and implement this using postgres
security. I've created users fred, barney, wilma and betty.
 
So I'm going to create two tables crate and contents:
 
Crate:
 crate_id |   crate_location| owner
--+-+
1 | On a shelf  | fred
2 | Under the stairs| barney
3 | On the floor| wilma
3 | Outside in the rain | betty
 

Contents:
 item_number | crate_id |   item
-+--+---
   1 |1 | spare stone wheel for car
   1 |3 | lunch for dino
   1 |2 | bowling ball
   1 |4 | pelican washing machine
 

I'm not going to give them permissions on this table. Instead they can
all see these two views:
 
create view user_crate as select * from crate where owner=current_user;
 
create view user_contents as select * from contents where crate_id in
(select crate_id from crate where owner=current_user);
 
Great- now by using these views, all my customers can see what crates
they own and what's in them. (Don't worry, the point of this email is
coming!)
 
Now, I want to be able to let them alter what is in their crates. I'm
only going to let them use the user_contents view. I'm going to do
this doing rules.
 
Now I've cracked delete...
 
create rule delete_contents AS ON DELETE TO user_contents DO INSTEAD
DELETE FROM contents.records WHERE contents.item_number =
old.item_number AND contents.crate_id=old.crate_id
 
I tried this for update. This stops them from putting stuff in a crate
that doesn't belong to them, but alas it stops them moving stuff from
crate to crate.
 
create rule update_contents AS ON UPDATE TO user_contents DO INSTEAD
UPDATE contents.records SET contents.item_number=new.item_number 
 
I've tried various things for a rule for INSERT, but I've not come up
with anything that effectively stops people from putting stuff in
other's crates. The only solutions I've come up with involve triggers,
but since I can't put triggers on views, just the underlying
tables. As a superuser (and hypothetical warehouse owner) I want to
insert, delete etc. from the original tables. unencumbered.
 
In  a nutshell, then, I'd like my users to be able to alter the data
in the tables, but only the data that they can see in the views, and
only in a way so it remains in their views after modification.
 

Has anyone got any advice?
 
Thanks and congratulations if you've got to the end of this email :)
 
David


---(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] LISTEN considered dangerous

[Martijn van Oosterhout]

On Thu, Aug 03, 2006 at 12:43:47AM +0200, Flemming Frandsen wrote:
> On Wed, 2 Aug 2006, Tom Lane wrote:
> 
> > Flemming Frandsen <[EMAIL PROTECTED]> writes:
> > > The listen should simply listen for events issued at the start of the
> > > transaction it's executed in.
> >
> > BEGIN;
> > SELECT sleep(10);
> > LISTEN foo;
> >
> > No, I don't think so.
> 
> And why would that be a problem?
> 
> There is no reason to assume that there would be any overhead in storing a
> list of outstanding events for your connection compared to today.

Err, yes there would. Think about it: for that example to work, the
server would have to store every notify that happened until your
transaction completed. That could be thousands, considering you can
defer indefinitly. And at the end of your transaction it has to go
through the list and throw away 99% of them because they're not for
"foo" but for something else. Currently NOTIFY requires no storage at
all, so what you're suggesting is fairly expensive, since the cost
would be applied for every transaction, even ones that don't use
LISTEN.

The solution is to do the LISTEN first, outside the transaction. The
SELECT could be outside the transaction also, but you havn't told
enough to know if that's feasable.

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


signature.asc
Description: Digital signature

Re: [GENERAL] Best Procedural Language?

[Kenneth Downs]

Joshua D. Drake wrote:

However, Alvaro is correct there are currently no resources dedicated 
to PL/php.

There will be in the future but for now we are busy with other things.


Well consider me your biggest cheerleader, and when circumstances bring 
it to the front burner that will be great.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


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

Re: [GENERAL] TSearch: Need debug help

[Hannes Dorbath]

but does 'dursten' is a some form of 'durst' ?


Yes it is.

Hm, even when I remove `dursten' and `durst' all together from the dict 
I still get `sen'.


How can I update a tsvector column stripping the `sen' lexem?

Thanks!

On 03.08.2006 12:54, Oleg Bartunov wrote:

Hannes,

I don't know german, sorry, but does 'dursten' is a some form of 'durst' ?
Probably, here we have false hit from compound word support. I'd suggest
to use exclusion dictionary (on the base of synonym dictionary) before  
ispell. It could be very simple:

durst : durst


Oleg

On Thu, 3 Aug 2006, Hannes Dorbath wrote:


SELECT ts_debug('durst');
(default_german,lword,"Latin word",durst,"{de_ispell,de}","'dur' 'sen'")

SELECT ts_debug('h?chsten');
(default_german,word,Word,h?chsten,"{de_ispell,de}","'sen' 'h?ch' 
'h?chst' 'h?chsten'")


For some reason both produce the lexem 'sen'. That leads to strange 
results. Search for `durst' will highlight `h?chsten' with headline().


Server is PG 8.0.4,
german snowball stemmer,
dictionary used is http://hannes.imos.net/german_iso.med
(From OpenOffice)

What causes some words to result in `sen', though they don't contain 
that lexem?


Thanks!




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 3: Have you checked our extensive FAQ?

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




--
Regards,
Hannes Dorbath

---(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] TSearch: Need debug help

[Oleg Bartunov]

Hannes,

I don't know german, sorry, but does 'dursten' is a some form of 'durst' ?
Probably, here we have false hit from compound word support. I'd suggest
to use exclusion dictionary (on the base of synonym dictionary) 
before  ispell. It could be very simple:

durst : durst


Oleg

On Thu, 3 Aug 2006, Hannes Dorbath wrote:


SELECT ts_debug('durst');
(default_german,lword,"Latin word",durst,"{de_ispell,de}","'dur' 'sen'")

SELECT ts_debug('h?chsten');
(default_german,word,Word,h?chsten,"{de_ispell,de}","'sen' 'h?ch' 'h?chst' 
'h?chsten'")


For some reason both produce the lexem 'sen'. That leads to strange results. 
Search for `durst' will highlight `h?chsten' with headline().


Server is PG 8.0.4,
german snowball stemmer,
dictionary used is http://hannes.imos.net/german_iso.med
(From OpenOffice)

What causes some words to result in `sen', though they don't contain that 
lexem?


Thanks!




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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] Query performance

[Chris Mair]

> i have a table with around 57 million tuples, with the following columns: 
> pid(varchar), crit(varchar), val1(varchar), val2(varchar). Example:
> pidcritval1val2
> p1  c1  xy
> p1  c2  xz
> p1  c3  yx
> ...
> What i am doing is to query all val1 and val2 for one pid and all crit values:
> 
> select val1, val2, crit from mytable where pid='somepid' and crit in(select 
> crit from myCritTable);
> where myCritTable is a table that contains all crit values (around 42.000) 
> ordered by their insertion date.

In case myCritTable doesn't change a lot and this select by contrast is
executed a lot, have you considered precomputing whether a record from
your big table has a crit value from myCritTable?

Of course this info would be invalidated each time myCritTable is
updated, so you would trade fast selects on the big table vs. slow
updates on myCritTable. Don't know wether that makes sence for you...

Bye, Chris.



---(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] Query performance

[Christian Rengstl]

Hi,

here is the definition of the master table which is inherited by around 30 
tables based on the value of chr:
CREATE TABLE snp_master
(
  entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq'::regclass),
  pid varchar(15) NOT NULL,
  snp_id varchar(13) NOT NULL, 
  val1 varchar(1),
  val2 varchar(1),
  chr int2 NOT NULL,
  aendat text,
  aennam varchar(8),
  CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no),
  CONSTRAINT "UNIQUE_SNP_ALLEL_MASTER" UNIQUE (pid, entry_no, snp_id)
) 
WITHOUT OIDS;

The thing is that i load the data from txt files which themselves have more or 
less the same structure. So for every pid of 1500 there are up to 42000 
different snp_id values and for each of this combinations there are different 
val1 and val2 entries (all together this accounts for up to around 58 million 
tuples in the biggest table). MyCritTable then just contains the distinct 
snp_ids so that at least this query does not take very long any more.
CREATE TABLE snps_master
(
  snp_id varchar(13) NOT NULL,
  chr int2,
  aendat timestamp,
  CONSTRAINT pk_snp_master PRIMARY KEY (snp_id)
) 
WITHOUT OIDS;

Up to now there are no foreign key constraints, as once the data is loaded into 
the db there will be no update or delete operations at all. I only have to 
export the data to different file formats for which i need the query posted 
originally.

"Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 11:36 am:
> Hi,
> 
> maybe you should overthink your db-design, but thats a bit premature 
> whithout your complete 
> table-definitions(including table-names, datatypes, indexes, 
> foreign-key constraints,etc.)
> 
> If your are using pgadmin3 just cut'n paste the content of the window 
> on the bottom left for 
> the corresponding tables.
> 
> If you're using psql try \d yur-table-name.
> 
> Best regards
> 
> Hakan Kocaman
> Software-Development
> 
> digame.de GmbH
> Richard-Byrd-Str. 4-8
> 50829 Köln
> 
> Tel.: +49 (0) 221 59 68 88 31
> Fax: +49 (0) 221 59 68 88 98
> Email: [EMAIL PROTECTED] 
> 
> 
> 
>> -Original Message-
>> From: Christian Rengstl 
>> [mailto:[EMAIL PROTECTED] 
>> Sent: Thursday, August 03, 2006 11:18 AM
>> To: Richard Huxton; Hakan Kocaman
>> Cc: pgsql-general@postgresql.org 
>> Subject: Re: [GENERAL] Query performance
>> 
>> 
>> Hi,
>> 
>> i would rather compare int4 too, but the snp_id can be 
>> something like "abc123" unfortunately.
>> 
>> "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 11:08 am:
>> > Hi,
>> > 
>> > 
>> >> -Original Message-
>> >> From: Richard Huxton [mailto:[EMAIL PROTECTED] 
>> >> Sent: Thursday, August 03, 2006 11:00 AM
>> >> To: Christian Rengstl
>> >> Cc: Hakan Kocaman; pgsql-general@postgresql.org 
>> >> Subject: Re: [GENERAL] Query performance
>> >> 
>> >> 
>> >> Christian Rengstl wrote:
>> >> > Hi,
>> >> > 
>> >> > the complete query is the one i posted, but here comes the 
>> >> schema for mytable:
>> >> > entry_no int8 NOT NULL DEFAULT 
>> nextval('entry_no_seq''::regclass),
>> >> >   pid varchar(15) NOT NULL, 
>> >> >   crit varchar(13) NOT NULL,
>> >> >   val1 varchar(1),
>> >> >   val2 varchar(1),
>> >> >   aendat text,
>> >> >   aennam varchar(8),
>> >> >   CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)
>> >> > 
>> >> > myCritTable:
>> >> >   crit varchar(13) NOT NULL,
>> >> >   chr int2,
>> >> >   aendat timestamp,
>> >> >   CONSTRAINT pk_crit_master PRIMARY KEY (crit)
>> >> 
>> >> Still doesn't match the EXPLAIN output - where's snp_id? 
>> >> Where's table 
>> >> test2?
>> >> 
>> > 
>> > Yep, that bothered me too.
>> > 
>> >> > My server is 8.1.4. As a matter of fact, i have no idea 
>> >> where the text 
>> >>  > type comes from, because as you can see from above 
>> there are only
>> >>  > varchar with maximum 15 characters.
>> >> 
>> >> PG is casting it to text. There's no real difference between 
>> >> the types 
>> >> (other than the size limit) and it's not expensive.
>> > 
>> > But wouldn't a comparison between int4 be much cheaper.
>> > If i see smth like "id" (here snp_id) in a fieldname it should be a 
>> > int-type, i think.
>> > 
>> >> 
>> >> > "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 
>> >> 10:34 am:
>> >> >> Hi,
>> >> >>
>> >> >> can you post the complete query,schema- and 
>> >> >> table-definition,server-version etc. ?
>> >> >> This will help to identity the main problem.
>> >> >>
>> >> >> So at the moment i'm just guessing:
>> >> >>
>> >> >> Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
>> >> >> ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 
>> >>  rows=37120 
>> >> >> width=23) 
>> >> >>(actual time=291.600..356707.737 rows=37539 loops=1)
>> >> >> This part is very expensive, but i got no clue why.
>> >> 
>> >> Yep, it looks like the "Bitmap Heap Scan" is at the heart of 
>> >> this. You 
>> >> might want to increase work_mem, it could be that the bitmap 
>> >> is spilling 
>> >> to disk (which is much slower than keeping it all in RAM)
>> >> 
>> >> http://www.postgresql.org

[GENERAL] Dumping database using 8.1 or 7.1

[Jonathan Vallar]

Hi!

I have a corrupt database that runs on Postgres 7.1.2. I was able to
repair the database where I can now connect to the database and do
queries.

I have a migration plan to transfer my database to a Postgres 8.1. I
encountered problems when I was dumping the database. Postgres 7.1
reported: getTables(): SELECT (funcname) for trigger trig_mis_t_ins
returned 0 tuples. Expected 1. 

The trig_mis_t_ins trigger is no longer found on the database but it is
still listed at the database. With this, I assume the database is
corrupt. 

Would my problems go away if I dump it on postgres 8?

Thanks.

Regards,

Jonathan

Re: [GENERAL] Query performance

[Hakan Kocaman]

Hi,

maybe you should overthink your db-design, but thats a bit premature whithout 
your complete 
table-definitions(including table-names, datatypes, indexes, foreign-key 
constraints,etc.)

If your are using pgadmin3 just cut'n paste the content of the window on the 
bottom left for 
the corresponding tables.

If you're using psql try \d yur-table-name.

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]



> -Original Message-
> From: Christian Rengstl 
> [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, August 03, 2006 11:18 AM
> To: Richard Huxton; Hakan Kocaman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Query performance
> 
> 
> Hi,
> 
> i would rather compare int4 too, but the snp_id can be 
> something like "abc123" unfortunately.
> 
> "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 11:08 am:
> > Hi,
> > 
> > 
> >> -Original Message-
> >> From: Richard Huxton [mailto:[EMAIL PROTECTED] 
> >> Sent: Thursday, August 03, 2006 11:00 AM
> >> To: Christian Rengstl
> >> Cc: Hakan Kocaman; pgsql-general@postgresql.org 
> >> Subject: Re: [GENERAL] Query performance
> >> 
> >> 
> >> Christian Rengstl wrote:
> >> > Hi,
> >> > 
> >> > the complete query is the one i posted, but here comes the 
> >> schema for mytable:
> >> > entry_no int8 NOT NULL DEFAULT 
> nextval('entry_no_seq''::regclass),
> >> >   pid varchar(15) NOT NULL, 
> >> >   crit varchar(13) NOT NULL,
> >> >   val1 varchar(1),
> >> >   val2 varchar(1),
> >> >   aendat text,
> >> >   aennam varchar(8),
> >> >   CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)
> >> > 
> >> > myCritTable:
> >> >   crit varchar(13) NOT NULL,
> >> >   chr int2,
> >> >   aendat timestamp,
> >> >   CONSTRAINT pk_crit_master PRIMARY KEY (crit)
> >> 
> >> Still doesn't match the EXPLAIN output - where's snp_id? 
> >> Where's table 
> >> test2?
> >> 
> > 
> > Yep, that bothered me too.
> > 
> >> > My server is 8.1.4. As a matter of fact, i have no idea 
> >> where the text 
> >>  > type comes from, because as you can see from above 
> there are only
> >>  > varchar with maximum 15 characters.
> >> 
> >> PG is casting it to text. There's no real difference between 
> >> the types 
> >> (other than the size limit) and it's not expensive.
> > 
> > But wouldn't a comparison between int4 be much cheaper.
> > If i see smth like "id" (here snp_id) in a fieldname it should be a 
> > int-type, i think.
> > 
> >> 
> >> > "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 
> >> 10:34 am:
> >> >> Hi,
> >> >>
> >> >> can you post the complete query,schema- and 
> >> >> table-definition,server-version etc. ?
> >> >> This will help to identity the main problem.
> >> >>
> >> >> So at the moment i'm just guessing:
> >> >>
> >> >> Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
> >> >> ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 
> >>  rows=37120 
> >> >> width=23) 
> >> >> (actual time=291.600..356707.737 rows=37539 loops=1)
> >> >> This part is very expensive, but i got no clue why.
> >> 
> >> Yep, it looks like the "Bitmap Heap Scan" is at the heart of 
> >> this. You 
> >> might want to increase work_mem, it could be that the bitmap 
> >> is spilling 
> >> to disk (which is much slower than keeping it all in RAM)
> >> 
> >> http://www.postgresql.org/docs/8.1/static/runtime-config-resou 
> > rce.html#RUNTIME-CONFIG-RESOURCE-MEMORY
> > 
> > If it's still at 1MB (1024), try 5MB, 10MB, 50MB and see 
> what happens.
> > 
> > -- 
> >Richard Huxton
> >Archonet Ltd
> > 
> > 
> > 
> > Hakan Kocaman
> > Software-Development
> > 
> > digame.de GmbH
> > Richard-Byrd-Str. 4-8
> > 50829 Köln
> > 
> > Tel.: +49 (0) 221 59 68 88 31
> > Fax: +49 (0) 221 59 68 88 98
> > Email: [EMAIL PROTECTED] 
> > 
> > ---(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
> 
> 
> --
> Christian Rengstl M.A.
> Klinik und Poliklinik für Innere Medizin II
> Kardiologie - Forschung
> Universitätsklinikum Regensburg
> B3 1.388
> Franz-Josef-Strauss-Allee 11
> 93053 Regensburg
> Tel.: +49-941-944-7230
> 
> 

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

Re: [GENERAL] Query performance

[Christian Rengstl]

Hi,

i would rather compare int4 too, but the snp_id can be something like "abc123" 
unfortunately.

"Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 11:08 am:
> Hi,
> 
> 
>> -Original Message-
>> From: Richard Huxton [mailto:[EMAIL PROTECTED] 
>> Sent: Thursday, August 03, 2006 11:00 AM
>> To: Christian Rengstl
>> Cc: Hakan Kocaman; pgsql-general@postgresql.org 
>> Subject: Re: [GENERAL] Query performance
>> 
>> 
>> Christian Rengstl wrote:
>> > Hi,
>> > 
>> > the complete query is the one i posted, but here comes the 
>> schema for mytable:
>> > entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass),
>> >   pid varchar(15) NOT NULL, 
>> >   crit varchar(13) NOT NULL,
>> >   val1 varchar(1),
>> >   val2 varchar(1),
>> >   aendat text,
>> >   aennam varchar(8),
>> >   CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)
>> > 
>> > myCritTable:
>> >   crit varchar(13) NOT NULL,
>> >   chr int2,
>> >   aendat timestamp,
>> >   CONSTRAINT pk_crit_master PRIMARY KEY (crit)
>> 
>> Still doesn't match the EXPLAIN output - where's snp_id? 
>> Where's table 
>> test2?
>> 
> 
> Yep, that bothered me too.
> 
>> > My server is 8.1.4. As a matter of fact, i have no idea 
>> where the text 
>>  > type comes from, because as you can see from above there are only
>>  > varchar with maximum 15 characters.
>> 
>> PG is casting it to text. There's no real difference between 
>> the types 
>> (other than the size limit) and it's not expensive.
> 
> But wouldn't a comparison between int4 be much cheaper.
> If i see smth like "id" (here snp_id) in a fieldname it should be a 
> int-type, i think.
> 
>> 
>> > "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 
>> 10:34 am:
>> >> Hi,
>> >>
>> >> can you post the complete query,schema- and 
>> >> table-definition,server-version etc. ?
>> >> This will help to identity the main problem.
>> >>
>> >> So at the moment i'm just guessing:
>> >>
>> >> Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
>> >> ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 
>>  rows=37120 
>> >> width=23) 
>> >>   (actual time=291.600..356707.737 rows=37539 loops=1)
>> >> This part is very expensive, but i got no clue why.
>> 
>> Yep, it looks like the "Bitmap Heap Scan" is at the heart of 
>> this. You 
>> might want to increase work_mem, it could be that the bitmap 
>> is spilling 
>> to disk (which is much slower than keeping it all in RAM)
>> 
>> http://www.postgresql.org/docs/8.1/static/runtime-config-resou 
> rce.html#RUNTIME-CONFIG-RESOURCE-MEMORY
> 
> If it's still at 1MB (1024), try 5MB, 10MB, 50MB and see what happens.
> 
> -- 
>Richard Huxton
>Archonet Ltd
> 
> 
> 
> Hakan Kocaman
> Software-Development
> 
> digame.de GmbH
> Richard-Byrd-Str. 4-8
> 50829 Köln
> 
> Tel.: +49 (0) 221 59 68 88 31
> Fax: +49 (0) 221 59 68 88 98
> Email: [EMAIL PROTECTED] 
> 
> ---(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


--
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230


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

Re: [GENERAL] Query performance

[Hakan Kocaman]

Hi,


> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, August 03, 2006 11:00 AM
> To: Christian Rengstl
> Cc: Hakan Kocaman; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Query performance
> 
> 
> Christian Rengstl wrote:
> > Hi,
> > 
> > the complete query is the one i posted, but here comes the 
> schema for mytable:
> > entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass),
> >   pid varchar(15) NOT NULL, 
> >   crit varchar(13) NOT NULL,
> >   val1 varchar(1),
> >   val2 varchar(1),
> >   aendat text,
> >   aennam varchar(8),
> >   CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)
> > 
> > myCritTable:
> >   crit varchar(13) NOT NULL,
> >   chr int2,
> >   aendat timestamp,
> >   CONSTRAINT pk_crit_master PRIMARY KEY (crit)
> 
> Still doesn't match the EXPLAIN output - where's snp_id? 
> Where's table 
> test2?
> 

Yep, that bothered me too.

> > My server is 8.1.4. As a matter of fact, i have no idea 
> where the text 
>  > type comes from, because as you can see from above there are only
>  > varchar with maximum 15 characters.
> 
> PG is casting it to text. There's no real difference between 
> the types 
> (other than the size limit) and it's not expensive.

But wouldn't a comparison between int4 be much cheaper.
If i see smth like "id" (here snp_id) in a fieldname it should be a int-type, i 
think.

> 
> > "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 
> 10:34 am:
> >> Hi,
> >>
> >> can you post the complete query,schema- and 
> >> table-definition,server-version etc. ?
> >> This will help to identity the main problem.
> >>
> >> So at the moment i'm just guessing:
> >>
> >> Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
> >> ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 
>  rows=37120 
> >> width=23) 
> >>(actual time=291.600..356707.737 rows=37539 loops=1)
> >> This part is very expensive, but i got no clue why.
> 
> Yep, it looks like the "Bitmap Heap Scan" is at the heart of 
> this. You 
> might want to increase work_mem, it could be that the bitmap 
> is spilling 
> to disk (which is much slower than keeping it all in RAM)
> 
> http://www.postgresql.org/docs/8.1/static/runtime-config-resou
rce.html#RUNTIME-CONFIG-RESOURCE-MEMORY

If it's still at 1MB (1024), try 5MB, 10MB, 50MB and see what happens.

-- 
   Richard Huxton
   Archonet Ltd



Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]

---(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] logic/db question

[Marco Bizzarri]

If table reorganization is an option for you, you could consider using
integer ranges for describing parant/children relationships.

I have seen them in one of Joe Celko books:

http://www.amazon.com/gp/product/1558609202/sr=8-2/qid=1154595988/ref=pd_bbs_2/104-2243146-1376759?ie=UTF8

In that way, you can find all the children with just one query.

Regards
Marco

On 8/2/06, bruce <[EMAIL PROTECTED]> wrote:

hi...

i have a tbl
 fooTBL
   name
   parentID
   ID

so a name can have might have a parentID, as well as an ID. 'name's are
associated with other 'name's via the parentID. in other words, if a name's
parentID == a name's ID, name1 is the parent of name2.

  name  parentIDID
  foo-  1
  cat   1   2
  dog   2   3

my question, how can i come up with a sql query that will list all the
children (and children's children...) of a top level item?

i had done this awhile ago.. but can't recall how i did it..

thanks



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




--
Marco Bizzarri
http://notenotturne.blogspot.com/

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

Re: [GENERAL] Query performance

[Richard Huxton]

Christian Rengstl wrote:

Hi,

the complete query is the one i posted, but here comes the schema for mytable:
entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass),
  pid varchar(15) NOT NULL, 
  crit varchar(13) NOT NULL,

  val1 varchar(1),
  val2 varchar(1),
  aendat text,
  aennam varchar(8),
  CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)

myCritTable:
  crit varchar(13) NOT NULL,
  chr int2,
  aendat timestamp,
  CONSTRAINT pk_crit_master PRIMARY KEY (crit)


Still doesn't match the EXPLAIN output - where's snp_id? Where's table 
test2?


My server is 8.1.4. As a matter of fact, i have no idea where the text 

> type comes from, because as you can see from above there are only
> varchar with maximum 15 characters.

PG is casting it to text. There's no real difference between the types 
(other than the size limit) and it's not expensive.



"Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 10:34 am:

Hi,

can you post the complete query,schema- and 
table-definition,server-version etc. ?

This will help to identity the main problem.

So at the moment i'm just guessing:

Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
->  Bitmap Heap Scan on test2  (cost=232.92..132766.66  rows=37120 
width=23) 
	(actual time=291.600..356707.737 rows=37539 loops=1)

This part is very expensive, but i got no clue why.


Yep, it looks like the "Bitmap Heap Scan" is at the heart of this. You 
might want to increase work_mem, it could be that the bitmap is spilling 
to disk (which is much slower than keeping it all in RAM)


http://www.postgresql.org/docs/8.1/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

If it's still at 1MB (1024), try 5MB, 10MB, 50MB and see what happens.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Query performance

[Hakan Kocaman]

Hi,

can you post the complete query,schema- and table-definition,server-version 
etc. ?
This will help to identity the main problem.

So at the moment i'm just guessing:

Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
->  Bitmap Heap Scan on test2  (cost=232.92..132766.66  rows=37120 
width=23) 
 (actual time=291.600..356707.737 
rows=37539 loops=1)
This part is very expensive, but i got no clue why.
Maybe the text-type is not so ideal.

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]



> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Christian Rengstl
> Sent: Thursday, August 03, 2006 10:13 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Query performance
> 
> 
> Hi everyone,
> 
> i have a table with around 57 million tuples, with the 
> following columns: pid(varchar), crit(varchar), 
> val1(varchar), val2(varchar). Example:
> pidcritval1val2
> p1  c1  xy
> p1  c2  xz
> p1  c3  yx
> ...
> What i am doing is to query all val1 and val2 for one pid and 
> all crit values:
> 
> select val1, val2, crit from mytable where pid='somepid' and 
> crit in(select crit from myCritTable);
> where myCritTable is a table that contains all crit values 
> (around 42.000) ordered by their insertion date.
> 
> 
> QUERY PLAN
> 
> --
> --
> --
>  Hash IN Join  (cost=1033.67..134959.41 rows=37120 width=23) 
> (actual time=357.11
> 6..356984.535 rows=37539 loops=1)
>Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
>->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 
> rows=37120 width=23) (
> actual time=291.600..356707.737 rows=37539 loops=1)
>  Recheck Cond: ((pid)::text = '1'::text)
>  ->  Bitmap Index Scan on idx_test2_pid  
> (cost=0.00..232.92 rows=37120 w
> idth=0) (actual time=234.516..234.516 rows=37539 loops=1)
>Index Cond: ((pid)::text = '1'::text)
>->  Hash  (cost=700.20..700.20 rows=40220 width=13) 
> (actual time=65.055..65.0
> 55 rows=40220 loops=1)
>  ->  Seq Scan on snps_test  (cost=0.00..700.20 
> rows=40220 width=13) (act
> ual time=0.020..30.131 rows=40220 loops=1)
>  Total runtime: 357017.259 ms
> 
> Unfortunately the query takes pretty long for the big table, 
> so maybe one of you has a suggestion on how to make it faster.
> 
> --
> Christian Rengstl M.A.
> Klinik und Poliklinik für Innere Medizin II
> Kardiologie - Forschung
> Universitätsklinikum Regensburg
> B3 1.388
> Franz-Josef-Strauss-Allee 11
> 93053 Regensburg
> Tel.: +49-941-944-7230
> 
> 
> ---(end of 
> broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

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

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

Re: [GENERAL] Query performance

[Christian Rengstl]

Hi,

the complete query is the one i posted, but here comes the schema for mytable:
entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass),
  pid varchar(15) NOT NULL, 
  crit varchar(13) NOT NULL,
  val1 varchar(1),
  val2 varchar(1),
  aendat text,
  aennam varchar(8),
  CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)

myCritTable:
  crit varchar(13) NOT NULL,
  chr int2,
  aendat timestamp,
  CONSTRAINT pk_crit_master PRIMARY KEY (crit)

My server is 8.1.4. As a matter of fact, i have no idea where the text type 
comes from, because as you can see from above there are only varchar with 
maximum 15 characters.

"Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 10:34 am:
> Hi,
> 
> can you post the complete query,schema- and 
> table-definition,server-version etc. ?
> This will help to identity the main problem.
> 
> So at the moment i'm just guessing:
> 
> Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
> ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66  rows=37120 
> width=23) 
>(actual time=291.600..356707.737 
> rows=37539 loops=1)
> This part is very expensive, but i got no clue why.
> Maybe the text-type is not so ideal.
> 
> Best regards
> 
> Hakan Kocaman
> Software-Development
> 
> digame.de GmbH
> Richard-Byrd-Str. 4-8
> 50829 Köln
> 
> Tel.: +49 (0) 221 59 68 88 31
> Fax: +49 (0) 221 59 68 88 98
> Email: [EMAIL PROTECTED] 
> 
> 
> 
>> -Original Message-
>> From: [EMAIL PROTECTED] 
>> [mailto:[EMAIL PROTECTED] On Behalf Of 
>> Christian Rengstl
>> Sent: Thursday, August 03, 2006 10:13 AM
>> To: pgsql-general@postgresql.org 
>> Subject: [GENERAL] Query performance
>> 
>> 
>> Hi everyone,
>> 
>> i have a table with around 57 million tuples, with the 
>> following columns: pid(varchar), crit(varchar), 
>> val1(varchar), val2(varchar). Example:
>> pidcritval1val2
>> p1  c1  xy
>> p1  c2  xz
>> p1  c3  yx
>> ...
>> What i am doing is to query all val1 and val2 for one pid and 
>> all crit values:
>> 
>> select val1, val2, crit from mytable where pid='somepid' and 
>> crit in(select crit from myCritTable);
>> where myCritTable is a table that contains all crit values 
>> (around 42.000) ordered by their insertion date.
>> 
>> 
>> QUERY PLAN
>> 
>> --
>> --
>> --
>>  Hash IN Join  (cost=1033.67..134959.41 rows=37120 width=23) 
>> (actual time=357.11
>> 6..356984.535 rows=37539 loops=1)
>>Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
>>->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 
>> rows=37120 width=23) (
>> actual time=291.600..356707.737 rows=37539 loops=1)
>>  Recheck Cond: ((pid)::text = '1'::text)
>>  ->  Bitmap Index Scan on idx_test2_pid  
>> (cost=0.00..232.92 rows=37120 w
>> idth=0) (actual time=234.516..234.516 rows=37539 loops=1)
>>Index Cond: ((pid)::text = '1'::text)
>>->  Hash  (cost=700.20..700.20 rows=40220 width=13) 
>> (actual time=65.055..65.0
>> 55 rows=40220 loops=1)
>>  ->  Seq Scan on snps_test  (cost=0.00..700.20 
>> rows=40220 width=13) (act
>> ual time=0.020..30.131 rows=40220 loops=1)
>>  Total runtime: 357017.259 ms
>> 
>> Unfortunately the query takes pretty long for the big table, 
>> so maybe one of you has a suggestion on how to make it faster.
>> 
>> --
>> Christian Rengstl M.A.
>> Klinik und Poliklinik für Innere Medizin II
>> Kardiologie - Forschung
>> Universitätsklinikum Regensburg
>> B3 1.388
>> Franz-Josef-Strauss-Allee 11
>> 93053 Regensburg
>> Tel.: +49-941-944-7230
>> 
>> 
>> ---(end of 
>> broadcast)---
>> TIP 4: Have you searched our list archives?
>> 
>>http://archives.postgresql.org 
>> 


--
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230

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

   http://archives.postgresql.org

Re: [GENERAL] Tape backup, 2 versions, same database name, which

[Richard Huxton]

Thomas F. O'Connell wrote:



On Aug 2, 2006, at 4:27 PM, [EMAIL PROTECTED] wrote:

You're correct, I cannot use the pg_dump. I get a error message that 
the pg_dump is aborting because of a version mismatch, then it says to 
use the i opt. How do I call the pg_dump from the 8.1.4 version?


You'll probably need to specify an absolute path. Are both the client 
and server applications running on the same server? If so, you might 
need to get your consultant to tell you where the client applications 
for the 8.1.4 installation were actually installed. By default, postgres 
installs both client and server applications into /usr/local/pgsql, but 
at least one of your installations must be in a different location; 
otherwise, you probably couldn't have two versions of the postmaster 
running concurrently.


Oh, and two other commands you might find useful:
which pg_dump
Will show you which version of pg_dump is being run by default
alias
Will let you set up pg_dump81 as an alias for the correct binary you 
want.

Man pages cover the details.

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] TSearch: Need debug help

[Hannes Dorbath]

SELECT ts_debug('durst');
(default_german,lword,"Latin word",durst,"{de_ispell,de}","'dur' 'sen'")

SELECT ts_debug('höchsten');
(default_german,word,Word,höchsten,"{de_ispell,de}","'sen' 'höch' 
'höchst' 'höchsten'")


For some reason both produce the lexem 'sen'. That leads to strange 
results. Search for `durst' will highlight `höchsten' with headline().


Server is PG 8.0.4,
german snowball stemmer,
dictionary used is http://hannes.imos.net/german_iso.med
(From OpenOffice)

What causes some words to result in `sen', though they don't contain 
that lexem?


Thanks!

--
Regards,
Hannes Dorbath

---(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] Query performance

[Christian Rengstl]

Hi everyone,

i have a table with around 57 million tuples, with the following columns: 
pid(varchar), crit(varchar), val1(varchar), val2(varchar). Example:
pidcritval1val2
p1  c1  xy
p1  c2  xz
p1  c3  yx
...
What i am doing is to query all val1 and val2 for one pid and all crit values:

select val1, val2, crit from mytable where pid='somepid' and crit in(select 
crit from myCritTable);
where myCritTable is a table that contains all crit values (around 42.000) 
ordered by their insertion date.


QUERY PLAN


--
 Hash IN Join  (cost=1033.67..134959.41 rows=37120 width=23) (actual time=357.11
6..356984.535 rows=37539 loops=1)
   Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
   ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 rows=37120 width=23) (
actual time=291.600..356707.737 rows=37539 loops=1)
 Recheck Cond: ((pid)::text = '1'::text)
 ->  Bitmap Index Scan on idx_test2_pid  (cost=0.00..232.92 rows=37120 w
idth=0) (actual time=234.516..234.516 rows=37539 loops=1)
   Index Cond: ((pid)::text = '1'::text)
   ->  Hash  (cost=700.20..700.20 rows=40220 width=13) (actual time=65.055..65.0
55 rows=40220 loops=1)
 ->  Seq Scan on snps_test  (cost=0.00..700.20 rows=40220 width=13) (act
ual time=0.020..30.131 rows=40220 loops=1)
 Total runtime: 357017.259 ms

Unfortunately the query takes pretty long for the big table, so maybe one of 
you has a suggestion on how to make it faster.

--
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230


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

   http://archives.postgresql.org

Re: [GENERAL] Query performance

[Nikolay Samokhvalov]

On 8/3/06, Christian Rengstl <[EMAIL PROTECTED]> wrote:

...
Unfortunately the query takes pretty long for the big table, so maybe one of 
you has a suggestion on how to make it faster.



try smth like this:

select val1, val2, crit from mytable as a where pid='somepid' and
exists(select 1 from myCritTable as b where a.crit = b.crit);


--
Best regards,
Nikolay

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

Re: [GENERAL] SELinux + CREATE TABLESPACE = ?

[Just Someone]

If you rather keep SELinux on, you can still set the SELinux context
on the directory where you want the tablespaces to one postgres will
like.

To find what is the permissions you need, you can use ls -Z. It will
list the SELinux context. Check /var/lib/pgsql/data (or wherever
postgres data is pointing to), and then set this same permission on
the target dir using chcon.

For example, on my FC4 system all subdirectories on the data directory have:
root:object_r:postgresql_db_t or user_u:object_r:postgresql_db_t

So if you want to chage /path/to/foo/which/is/not/under/pgdata, run
(as root or sudo):

chcon root:object_r:postgresql_db_t /path/to/foo/which/is/not/under/pgdata

This way postgres can access it, and you get the SELinux security.

Bye,

Guy.

http://www.famundo.com
http://devblog.famundo.com
- Hide quoted text -

On 8/2/06, David Fetter <[EMAIL PROTECTED]> wrote:

On Wed, Aug 02, 2006 at 04:47:36PM -0700, David Fetter wrote:
> Folks,
>
> This may have come up before, but I didn't see a specific answer in
> the archives.
>
> When I try to do:
>
> CREATE TABLESPACE foo LOCATION '/path/to/foo/which/is/not/under/$PGDATA';
>
> I get:
>
> ERROR:  could not set permissions on directory 
"/path/to/foo/which/is/not/under/$PGDATA"
>
> Apparently this is a SELinux problem.  How do I set the policy to
> allow for this, or if that's not possible, how do I disable SELinux?
>
> Thanks in advance :)

Pardon my self-followup for the archives :)


Thanks to Talha Khan, who said:

>setenforce 1;
>
>will disable SELINUX

Thanks also to Clodoaldo Pinto, who said:

> >Apparently this is a SELinux problem.
>
> Confirm it looking for a message in /var/log/messages.
>
> >How do I set the policy to allow for this,
>
> This Fedora FAQ is good:
> http://fedora.redhat.com/docs/selinux-faq-fc5/#faq-div-controlling-selinux
>
> >or if that's not possible, how do I disable SELinux?
>
> edit /ect/selinux/config

Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

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



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