Re: [GENERAL] Perspective: PostgreSQL usage boon after release of
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
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?
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?
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?
[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?
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
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
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
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
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
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
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?
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?
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
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
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
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?
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
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?
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
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
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
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
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
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
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
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
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?
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
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
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
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
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?
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
/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
> > 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
