Re: [GENERAL] Perspective: PostgreSQL usage boon after release of

2006-03-07 Thread Ken Johanson

Tony Caduto wrote:

Ken Johanson wrote:
Most of the corp folks I know who have tried using PG to augment or 
replacement a commercial offering just tend to silently pause and 
wait for this change.. that why this topic isn't really heard very 
often. It's like going to a car lot to buy a SUV, but they don't have 
any within sight.. the perspective buyer just moves on without saying 
anything.



I have converted databases from other DBs such as MS SQL server and 
never had a problem with string escaping, can you please post a 
example of what you mean?  Do you mean inside of functions?


Well for a simple (for brevity) example, when you compile a query (not 
via prepared stmts/argument based compilation) that takes user input, 
how do you handle both backslashes and single-quotes? In practice the 
way of doing this is quite different between pg and a iso-compliant db, 
otherwise you have either code injection, or superfluous backslashes..


"SELECT firstName FROM tbl WHERE lastName = '"+toSql(userInput)+"' "


smime.p7s
Description: S/MIME Cryptographic Signature


Re: [GENERAL] Perspective: PostgreSQL usage boon after release of

2006-03-07 Thread Tony Caduto

Ken Johanson wrote:
Most of the corp folks I know who have tried using PG to augment or 
replacement a commercial offering just tend to silently pause and wait 
for this change.. that why this topic isn't really heard very often. 
It's like going to a car lot to buy a SUV, but they don't have any 
within sight.. the perspective buyer just moves on without saying anything.



I have converted databases from other DBs such as MS SQL server and 
never had a problem with string escaping, can you please post a example 
of what you mean?  Do you mean inside of functions?


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

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


[GENERAL] reindexdb script required in 8.1?

2006-03-07 Thread David Wall
I've upgraded from 8.0 to 8.1 and want to be sure I'm making the changes 
that are required.


For my backups, I have removed the option --blobs because it says this 
is no longer needed.  I guess the backup automatically includes blobs now?


I have also been running the contributed 'reindexdb' script by Shaun 
Thomas in my backups.  Is that still necessary?  It does not appear to 
be part of the contrib area anymore.


Also, my backup scripts still run vacuumlo.  I rebuilt that in 8.1 and 
am using that.  Is that still required or is vacuumlo part of the new 
8.1 vacuum capabilities?


Lastly, do I still need to run vacuum analyze from time to time update 
my stats, or is that done automatically?  I read about how some vacuums 
are automatic, but it's not clear if this auto-vacuum is activated by 
default or not and I'm not sure how I specify that I want this to occur.


Thanks,
David


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


Re: [GENERAL] ALTER TABLE -- how to add ON DELETE CASCADE?

2006-03-07 Thread felix
On Tue, Mar 07, 2006 at 05:36:37PM -0500, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE
> 
> You're missing the specification of the foreign key, not to mention
> spelling the CASCADE clause backwards.  Try
> 
> ALTER TABLE A ADD FOREIGN KEY(AA) REFERENCES B(BB) ON DELETE CASCADE

Got it right in the Subject: and my many attempts, just not in the
body :-)

The column already had the foreign key, I never thought to add it
again.  I was only thinking of modifying the minimum necessary.

Thanks.

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED]
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

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


Re: [GENERAL] ALTER TABLE -- how to add ON DELETE CASCADE?

2006-03-07 Thread Tom Lane
[EMAIL PROTECTED] writes:
> ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE

You're missing the specification of the foreign key, not to mention
spelling the CASCADE clause backwards.  Try

ALTER TABLE A ADD FOREIGN KEY(AA) REFERENCES B(BB) ON DELETE CASCADE

> \h alter table seems to be missing any way to add a constraint to a
> column.

It's there, though you have to look to \h create table to see the
alternatives for "table_constraint".

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] ALTER TABLE -- how to add ON DELETE CASCADE?

2006-03-07 Thread felix
I have table A with a column AA which references table B's primary
key BB, and I want to alter column AA to delete on cascade.

ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE

is what I tried with a zillion variations, all reporting syntax
errors.

\h alter table seems to be missing any way to add a constraint to a
column.

Or maybe this isn't a constraint ... does seem like maybe the wrong
name, but I can't think what else it would be called, and the other
choices shown with \h seem even less likely.

Your assiatnce is much appreciated.  I would gladly send you some
Oreos or Ginger Nuts :-)

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED]
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

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


[GENERAL] Perspective: PostgreSQL usage boon after release of 8.2

2006-03-07 Thread Ken Johanson

Just a quick thought, and an possibly over-optimistic forecast:

I think PG will see a really significant increase in usage, especially 
'corporate' use, with the release of the version that has support for 
(what I consider to be) one of the biggest features in recent memory: 
standard iso/ansi string escaping.


In my experience this has been the biggest roadblock to adoption by 
companies running a commercial database... it has made migration 
difficult/daunting, especially for users who cannot rely on prepared 
statement style APIs (complex dynamic queries, etc).


Most of the corp folks I know who have tried using PG to augment or 
replacement a commercial offering just tend to silently pause and wait 
for this change.. that why this topic isn't really heard very often. 
It's like going to a car lot to buy a SUV, but they don't have any 
within sight.. the perspective buyer just moves on without saying anything.


I'm am SOOO looking forward to hearing the beta announcement for 8.2... 
hopefully that version's still on target for the standard string escape 
option.. This *one* roadblock will be gone.


k



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

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


Re: [GENERAL] how to setup default privileges

2006-03-07 Thread Emi Lu

When I first created an object, and did "\dp+ tableName"

I got blank under "Access privileges". However, if I tried to grant any 
permission to a group/user, I will see the access privileges for the 
object owner. 


Example:

1. create table test(id char(3));
2. \dp+ test;

  Schema| Name | Type  | Access privileges
-+--+---+---
abc | test | table |

3. grant select on test to group g1;

4. \dp+ test

5. After grant to g1, we see the default privileges for objectOwner now:

  Schema| Name | Type  |   Access privileges
-+--+---+---
abc | test | table | {objectOwner=arwdRxt/abc,"group g1=r/abc"}






Could somebody tell me that what are the default privileges for table t1 
please? Does it equal to

grant all on t1 to "user1", please?
   



Yes.  See the reference page for GRANT for details.

regards, tom lane
 




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


Re: [GENERAL] how to setup default privileges

2006-03-07 Thread Tom Lane
Emi Lu <[EMAIL PROTECTED]> writes:
> Could somebody tell me that what are the default privileges for table t1 
> please? Does it equal to
> grant all on t1 to "user1", please?

Yes.  See the reference page for GRANT for details.

regards, tom lane

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


Re: [GENERAL] User defined EXCEPTIONs

2006-03-07 Thread vishal saberwal
I have a work around, but it would really help if there was a way to define my own exceptions (for business logic),Here is the output, for those who would be browsing for it in future,create or replace function RowCount_Select(varchar) returns int as $$
DECLARE    res int;BEGIN    select into res reltuples from pg_class where relkind='r' and relname=$1;    if not found then        raise exception 'testing';    end if;    return res;END;
$$ language plpgsql strict;create or replace function test1() returns int as $$DECLARE    res int;BEGIN    select into res RowCount_Select('test');    return res;EXCEPTION    when raise_exception then
        raise exception 'test2 # %',SQLERRM;END;$$ language plpgsql strict;DB=# select * from test1();ERROR:  test2 # testingthis does solve my problem of catching my exceptions to a good extent.
On 3/7/06, vishal saberwal <[EMAIL PROTECTED]> wrote:
Using postgreSQL 8.1 on fedora.Below is a function RowCount_Select(Table_Name) defined that raises exception (test1) which i intend to catch in test1( ) and raise exception (test2).
Do we have a way to catch such user defined exceptions? Can someone direct me to the right resource pages?
create or replace function RowCount_Select(varchar) returns int as $$DECLARE    res int;BEGIN    select into res reltuples from pg_class where relkind='r' and relname=$1;    if not found then

        raise exception 'test1';    end if;    return res;END;$$ language plpgsql strict;-- There is no relation by name ' test 'create or replace function test1() returns int as $$DECLARE

    res int;BEGIN    select into res RowCount_Select('test');    return res;EXCEPTION    when test1 then        raise exception 'test2';END;$$ language plpgsql strict;ERROR:  unrecognized exception condition "test1"
CONTEXT:  compile of PL/pgSQL function "test1" near line 11thanks,vish




[GENERAL] User defined EXCEPTIONs

2006-03-07 Thread vishal saberwal
Using postgreSQL 8.1 on fedora.Below is a function RowCount_Select(Table_Name) defined that raises exception (test1) which i intend to catch in test1( ) and raise exception (test2).Do we have a way to catch such user defined exceptions? Can someone direct me to the right resource pages?
create or replace function RowCount_Select(varchar) returns int as $$DECLARE    res int;BEGIN    select into res reltuples from pg_class where relkind='r' and relname=$1;    if not found then
        raise exception 'test1';    end if;    return res;END;$$ language plpgsql strict;-- There is no relation by name ' test 'create or replace function test1() returns int as $$DECLARE
    res int;BEGIN    select into res RowCount_Select('test');    return res;EXCEPTION    when test1 then        raise exception 'test2';END;$$ language plpgsql strict;ERROR:  unrecognized exception condition "test1"
CONTEXT:  compile of PL/pgSQL function "test1" near line 11thanks,vish


[GENERAL] how to setup default privileges

2006-03-07 Thread Emi Lu

Hello,

When an object is created by the default user "user1", I ran:
\dp+ tableName , and got:


  Schema| Name | Type  | Access privileges
-+--+---+---
schemaName | t1   | table |   --- step 0

The Access privileges is blank. However, I am able to insert, update, 
delete , etc on the newly created t1.


Could somebody tell me that what are the default privileges for table t1 
please? Does it equal to

grant all on t1 to "user1", please?


After running "revoke all on t1 from user1", how to reset the table 
privileges back to step 0 please?



Thanks a lot,
Emi





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


Re: [GENERAL] About when we should setup index?

2006-03-07 Thread Michael Fuhr
On Tue, Mar 07, 2006 at 09:54:11AM -0500, Emi Lu wrote:
> Thank you very much Michael. Your inputs are very helpful for me. Just 
> have one small question, the example you gave is based on  postgresql 
> 8.1, does it apply to PostgreSQL 8.0.1 as well (i686-pc-linux-gnu, 
> compiled by GCC gcc 3.3.2)?

What example?  I only mentioned 8.1 in the context of queries against
low-cardinality columns (columns with only a few distinct values) but
I didn't show an example of that.

> >Yes.  When you issue the CREATE TABLE statement you should see a
> >notice like the following:
> >
> >CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for 
> >table "test"

Is this the example you meant?  Earlier versions of PostgreSQL work
the same way.

-- 
Michael Fuhr

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

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


Re: [GENERAL] per-statement, after, what's new?

2006-03-07 Thread Tom Lane
Kenneth Downs <[EMAIL PROTECTED]> writes:
> If I define a per-statement AFTER INSERT trigger, how are the new rows 
> exposed to the trigger? 

They aren't.

regards, tom lane

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


Re: [GENERAL] Question about index usage

2006-03-07 Thread Guido Neitzer

On 07.03.2006, at 16:04 Uhr, Tom Lane wrote:


Because IN means "=", which is a member of the index opclass for the
second index but not the first.

Why do you care?  Should be about the same result either way.


Only because I haven't set up the second index because I wasn't aware  
of this fact.


cug


--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature


Re: [GENERAL] Question about index usage

2006-03-07 Thread Tom Lane
Guido Neitzer <[EMAIL PROTECTED]> writes:
> Is there a reason why this query:
> select id from dga_dienstleister where plz in ('45257', '45259');
> doesn't use this index:
>  "dga_dienstleister_plz_index" btree (plz varchar_pattern_ops)
> but uses this index:
>  "dga_dienstleister_plz_index2" btree (plz)

Because IN means "=", which is a member of the index opclass for the
second index but not the first.

Why do you care?  Should be about the same result either way.

regards, tom lane

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

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


Re: [GENERAL] pg_dump error - filesystem full

2006-03-07 Thread Tom Lane
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Am Dienstag, 7. März 2006 11:13 schrieb Poul Møller Hansen:
>> Is there a way to make pg_dump use /var/tmp as a temporary filespace
>> instead ?

> Try export TMPDIR=/var/tmp.

Also, unless you have a really good reason to be using -Ft dump format,
I'd recommend -Fc format instead.  It's better tested and doesn't suffer
from various limitations of the tar format, notably the need for a temp
file.

regards, tom lane

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

   http://archives.postgresql.org


Re: [GENERAL] About when we should setup index?

2006-03-07 Thread Emi Lu
Thank you very much Michael. Your inputs are very helpful for me. Just 
have one small question, the example you gave is based on  postgresql 
8.1, does it apply to PostgreSQL 8.0.1 as well (i686-pc-linux-gnu, 
compiled by GCC gcc 3.3.2)?


- Emi



. id is the primary key, so a default unique index is generated 
automatically ?
   



Yes.  When you issue the CREATE TABLE statement you should see a
notice like the following:

CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table 
"test"

You can see the index if you look at the table's description, such
as when doing "\d test" in psql.

 


. if queries based on name are often, index should be setup for name?
if there are 30,000 records, 29,000 records' names are different, will 
the index for name still be useful?
   



If you have 29,000 unique names out of 30,000 rows then an index
should definitely speed up queries by name.

 


. possible values for sex are  F/M and null, should we setup index for sex?
   



Probably not, although 8.1 can make better use of indexes on
low-cardinality columns than previous versions could.  If you're
using 8.1 then try running typical queries with and without such
an index to see if it makes much difference.  EXPLAIN ANALYZE will
show whether the index is being used.  Unless you see a significant
improvement in query performance then don't bother indexing this
column.

 


. How about index for date and timestamp?
   



Probably, if you regularly query on those columns.

 

Basically, I'd like to know is there a percentage of the differences 
among data to decide whether index will help or not? For example,  among 
30,000 records, for a column, its value choices are less than A% and 
greater than B% so that we know index will help a lot?
   



There's no absolute rule; among other things physical order on disk
influences the planner's decision to use an index.  If a table is
clustered on a particular index then the planner might use that
index to fetch 80% of the table's rows, but if the data is randomly
scattered then the planner might prefer a sequential scan to fetch
only 3% of the rows.

If you regularly query a column that has more than a handful of
distinct values then queries will probably benefit from an index
on that column; in 8.1 even queries against low-cardinality columns
might benefit from an index.  If you perform a lot of queries for
values that are close together, or if queries for a certain value
tend to return a lot of rows, then consider clustering the table
on that column's index (and be sure to run ANALYZE afterwards to
update the table's statistics).  Indexes have costs, however: they
take up disk space and they can slow down other operations like
inserts and updates because each index has to be updated as well.
Create whatever indexes you need to realize a significant improvement
in query performance, but don't overdo it.

 




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

  http://archives.postgresql.org


Re: [GENERAL] real - integer type cast in prepared statements

2006-03-07 Thread Tom Lane
Andrei <[EMAIL PROTECTED]> writes:
> Why when I prepare statement by parsing such query: 'SELECT * FROM 
> "test" WHERE "ind" < $1 + 1' ("ind" is of type REAL) $1 is interpreted 
> by backend as INTEGER?

Because the context in which its type first has to be resolved is "$1 + 1",
and the "1" is INTEGER.

regards, tom lane

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


[GENERAL] per-statement, after, what's new?

2006-03-07 Thread Kenneth Downs

Hi folks,

I cannot find this in Google, Google groups, online docs, or the 
archive, so I hope somebody can help me.


If I define a per-statement AFTER INSERT trigger, how are the new rows 
exposed to the trigger? 

Put another way, what is the equivalent to the row-level variables NEW 
and OLD that exist in row-level triggers?


Thanks!


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 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [GENERAL] Logging seq scans

2006-03-07 Thread Richard Huxton

Guido Neitzer wrote:

Hi.

Is there a way to set up logging in a way that I can see queries which 
trigger seq scans? Or to log queries "taking longer than xx ms"?


The second is straightforward. See the "log_min_duration_statement" 
setting in the "logging" section of the manuals.


--
  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] Benchmark-Comparison PostGreSQL vs. SQL Server

2006-03-07 Thread Russ Brown
On Thu, 25 Nov 2004 11:36:33 +0100
"Robert Soeding" <[EMAIL PROTECTED]> wrote:

> Hi, this is my first question here, and also, it's somewhat delicate.
> So please be patient. 
> My question is, CAN PostGreSQL perform in the SQL Server area when it
> comes to speed? In other words, are there explanations for the
> results I found (see below)? 
> Thanks,
> Robert
>  
> -
> Background:
> 1. I read people were using PostGreSQL with TeraBytes of data
> sometimes, or thousands of users. These are things that could easily
> break SQL Server. - So I thought PostGreSQL might be similar fast to
> SQL Server. 2. I did some tests: Windows XP SP2
> Several GIGs free harddisk, ~400 MB free RAM
> Java 1.5 / JDBC
> PostGreSQL 8.0 beta (through Windows Installer), default
> configuration, default driver SQL Server 2000 SP3a, default
> configuration,

Here's your problem right here. You're never going to get a fair
comparison unless you tune the crap out of *both* contenders.
PostgreSQL's default configuration is extremely conservative to allow
it to be run on very limited resources.

> JDTS driver Tablespaces of both databases on the same
> partition Write-Test: Creating tables (slightly modified TCP-W
> benchmark) Read-Test: Simple SELECT statements on all tables,
> returning the first 1000 rows (cursor variants: read-only and
> non-locking, resp. updatable and locking) Results:
> Writing: SQL Server 25 times faster.
> Reading: SQL Server 100 times faster.


-- 

Russ

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

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


[GENERAL] real - integer type cast in prepared statements

2006-03-07 Thread Andrei

Hi!

Why when I prepare statement by parsing such query: 'SELECT * FROM 
"test" WHERE "ind" < $1 + 1' ("ind" is of type REAL) $1 is interpreted 
by backend as INTEGER? Parse completed successfully, but trying to bind 
parameter as '20.20' resulted in "ERROR C22P02 Minvalid input syntax for 
integer: "20.20" Fnumutils.c L98 Rpg_atoi"? I understand that it's 
possible to make query like 'SELECT * FROM "test" WHERE "ind" < $1::real 
+ 1', but at the query building time I can't know what type this column is!


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

  http://archives.postgresql.org


Re: [GENERAL] Is the "ACCESS EXCLUSIVE" lock for TRUNCATE really

2006-03-07 Thread Florian G. Pflug

Tom Lane wrote:

"Florian G. Pflug" <[EMAIL PROTECTED]> writes:

Tom Lane wrote:


Until when?  How would you synchronize the switchover?
 

Every snapshot would either contain the old, or the new version of
the corresponding pg_class tuple. The ones using the old version
couldn't possible be writer, only reader (TRUNCATE would still need
to acquire a lock that ensures that). New transactions started after
the commit of the truncate would see the new version, and use
the new datafile.


Wrong.  *All* transactions read the system catalogs with SnapshotNow.

Ah, well that clearly kills my idea... Too bad...

I was fooled by the fact that most ddl-statements can be rolled back,
and assumed that this follows from using "normal" mvcc semantics when
reading the catalog tables.

Thanks for your explanations!

greetings, Florian Pflug

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

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


Re: [GENERAL] pg_dump error - filesystem full

2006-03-07 Thread Peter Eisentraut
Am Dienstag, 7. März 2006 11:13 schrieb Poul Møller Hansen:
> Is there a way to make pg_dump use /var/tmp as a temporary filespace
> instead ?

Try export TMPDIR=/var/tmp.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(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] JSP pages don't work with database after postgres downgrade

2006-03-07 Thread Kenevel
Phill Edwards wrote:
> I can see these errors in /usr/local/tomcat/logs/catalina.out:
>
>Unable to instantiate DB connection pool.
>Technical error message:
>java.lang.NullPointerException
>A null connection was relinquished.
>
> Does that shed any more light on the matter?

Err, yes. As the message suggests, your Tomcat server cannot create the
connection pool which it makes available to the web applications it runs.

You need to check
$CATALINA_HOME/conf/Catalina/localhost/${your-app-name}.xml or the
deployment descriptor in the application codebase at /META-INF/server.xml
(both of these paths are from memory).

In there you should find a ... element with the
connection parameters, including username, password and the JDBC driver
class. You should check that the Driver classname is correct for the JDBC
jar you're using.

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

   http://archives.postgresql.org


Re: [GENERAL] Logging seq scans

2006-03-07 Thread Guido Neitzer

On 07.03.2006, at 12:11 Uhr, A. Kretschmer wrote:


Yes, of cource. You can define

log_min_duration_statement = 100

to log all queries taking longer 100 ms.


Thanks.

cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature


Re: [GENERAL] Question about index usage

2006-03-07 Thread Guido Neitzer

On 07.03.2006, at 12:09 Uhr, chris smith wrote:


Try without the quotes:

select id from dga_dienstleister where plz in (45257, 45259);


Same result, second index is used.


What is the table structure for dga_dienstleister ?


For the relevant column:

plz| character varying(256)  |  
not null


Thanks,
cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature


Re: REPOST: [GENERAL] How to determine the table a query or a views columns come from?

2006-03-07 Thread Martijn van Oosterhout
On Tue, Mar 07, 2006 at 10:38:29AM +, Frank Church wrote:
> Quoting Tom Lane <[EMAIL PROTECTED]>:
> 
> Can this info be obtained by querying the system tables,
> especially in the case of views? I am using 'scripting' languages and using C
> will be quite awkward.

Well, Tom suggests the PQftable() and PQftablecol() functions which are
exported by libpq and should be available in your favourite scripting
language whenever you execute the query. If not, you should probably
fix that.

If you really feel like digging through the catalogs you should
probably start with pg_rewrite. If you look carefully, under the
targetlist node there are a number of TARGETENTRY nodes. Within that
there are the fields "resorigtbl" and "resorigcol".

Note this is going straight through compatability and out the other
side. There is no guarentee that any of these fields exist in any
version, I'm just looking at 7.4.7 here.

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


signature.asc
Description: Digital signature


Re: [GENERAL] Logging seq scans

2006-03-07 Thread A. Kretschmer
am  07.03.2006, um 11:59:18 +0100 mailte Guido Neitzer folgendes:
> Hi.
> 
> Is there a way to set up logging in a way that I can see queries  which 
> trigger seq scans? Or to log queries "taking longer than xx ms"?

Yes, of cource. You can define

log_min_duration_statement = 100

to log all queries taking longer 100 ms.


Btw.: visit our new PostgreSQL User Group Deutschland - Homepage:
http://pgug.de



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

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


Re: [GENERAL] Question about index usage

2006-03-07 Thread chris smith
On 3/7/06, Guido Neitzer <[EMAIL PROTECTED]> wrote:
> Hi.
>
> Is there a reason why this query:
>
> select id from dga_dienstleister where plz in ('45257', '45259');
>
> doesn't use this index:
>
>  "dga_dienstleister_plz_index" btree (plz varchar_pattern_ops)
>
> but uses this index:
>
>  "dga_dienstleister_plz_index2" btree (plz)
>
> I had the first index setup for queries with "plz like '4525%'" but I
> never tested the "in" query until I saw in the logs that these
> queries where slow compared to the rest. Query plans at the end.
>
> cug
>
>
> DGADB=# explain analyse select id from dga_dienstleister where plz
> like
> '45257';   Q
> UERY PLAN
> 
> 
> Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21
> width=8) (actual time=13.489..14.211 rows=16 loops=1)
> Filter: ((plz)::text ~~ '45257'::text)
> ->  Bitmap Index Scan on dga_dienstleister_plz_index
> (cost=0.00..2.07 rows=21 width=0) (actual time=13.323..13.323 rows=16
> loops=1)
>   Index Cond: ((plz)::text ~=~ '45257'::character varying)
> Total runtime: 14.328 ms
> (5 rows)
>
>
> DGADB=# explain analyse select id from dga_dienstleister where plz =
> '45257';
>QUERY
> PLAN
> 
> ---
> Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21
> width=8) (actual time=0.486..0.663 rows=16 loops=1)
> Recheck Cond: ((plz)::text = '45257'::text)
> ->  Bitmap Index Scan on dga_dienstleister_plz_index2
> (cost=0.00..2.07 rows=21 width=0) (actual time=0.424..0.424 rows=16
> loops=1)
>   Index Cond: ((plz)::text = '45257'::text)
> Total runtime: 0.826 ms
> (5 rows)
>
>
>
>

Try without the quotes:

select id from dga_dienstleister where plz in (45257, 45259);

What is the table structure for dga_dienstleister ?

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

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

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


[GENERAL] Question about index usage

2006-03-07 Thread Guido Neitzer

Hi.

Is there a reason why this query:

select id from dga_dienstleister where plz in ('45257', '45259');

doesn't use this index:

"dga_dienstleister_plz_index" btree (plz varchar_pattern_ops)

but uses this index:

"dga_dienstleister_plz_index2" btree (plz)

I had the first index setup for queries with "plz like '4525%'" but I  
never tested the "in" query until I saw in the logs that these  
queries where slow compared to the rest. Query plans at the end.


cug


DGADB=# explain analyse select id from dga_dienstleister where plz  
like  
'45257';   Q 
UERY PLAN
 

Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21  
width=8) (actual time=13.489..14.211 rows=16 loops=1)

   Filter: ((plz)::text ~~ '45257'::text)
   ->  Bitmap Index Scan on dga_dienstleister_plz_index   
(cost=0.00..2.07 rows=21 width=0) (actual time=13.323..13.323 rows=16  
loops=1)

 Index Cond: ((plz)::text ~=~ '45257'::character varying)
Total runtime: 14.328 ms
(5 rows)


DGADB=# explain analyse select id from dga_dienstleister where plz =  
'45257';
  QUERY  
PLAN
 
---
Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21  
width=8) (actual time=0.486..0.663 rows=16 loops=1)

   Recheck Cond: ((plz)::text = '45257'::text)
   ->  Bitmap Index Scan on dga_dienstleister_plz_index2   
(cost=0.00..2.07 rows=21 width=0) (actual time=0.424..0.424 rows=16  
loops=1)

 Index Cond: ((plz)::text = '45257'::text)
Total runtime: 0.826 ms
(5 rows)



smime.p7s
Description: S/MIME cryptographic signature


[GENERAL] Logging seq scans

2006-03-07 Thread Guido Neitzer

Hi.

Is there a way to set up logging in a way that I can see queries  
which trigger seq scans? Or to log queries "taking longer than xx ms"?


Background is, that it is nearly impossible to tell, which queries  
are used in my applications as they are mostly generated by the  
frameworks. Yesterday I found, that one of the more often used  
queries has not used an existing index and I had to use another index  
for it. Nobody complained about the performance but nevertheless the  
query took about 1000ms and therefore slowed down the rest of the  
server ...


cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature


Re: REPOST: [GENERAL] How to determine the table a query or a views columns come from?

2006-03-07 Thread Frank Church
Quoting Tom Lane <[EMAIL PROTECTED]>:

Quoting Tom Lane <[EMAIL PROTECTED]>:

Can this info be obtained by querying the system tables,
especially in the case of views? I am using 'scripting' languages and using C
will be quite awkward.

> Frank Church <[EMAIL PROTECTED]> writes:
> > Is there way to determine the table a query or a view's columns come from?
>
> Yeah, there's some support for that in the protocol.  libpq exposes it
> as PQftable() and PQftablecol().
>
>   regards, tom lane
>




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


[GENERAL] pg_dump error - filesystem full

2006-03-07 Thread Poul Møller Hansen

/tmp: write failed, filesystem is full
pg_dump: [tar archiver] could not write to tar member (wrote 0, 
attempted 101)


Is there a way to make pg_dump use /var/tmp as a temporary filespace 
instead ?



Thanks, Poul



---(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] JSP pages don't work with database after postgres downgrade

2006-03-07 Thread Phill Edwards
> > Check your postgresql logs firstly.
>
> Nothing's showing up in the logs. I can see that postgres has
> successfully started in syslog when I restart it. I'm not getting
> anything at all being written to /var/log/postgresql even though
> debug_level = 4 in /var/lib/pgsql/data/postgresql.conf
>
> Does this mean that tomcat isn't even getting into the database?

I can see these errors in /usr/local/tomcat/logs/catalina.out:

   Unable to instantiate DB connection pool.
   Technical error message:
   java.lang.NullPointerException
   A null connection was relinquished.

Does that shed any more light on the matter?

Regards,
Phill

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