[GENERAL] Glacial deletes (fixed), now glacial lo vacuum
Thanks to all who suggested indexing; I added an index to the running database, and delete performance immediately increased by a factor or 10. The problem now is that when I vacuum verbose analyze, the pg_largeobject system table takes *forever* (tens of minutes). A new postmaster seems to be spawned. The current psql session seems to disconnect (no more messages), while a tail -f postmaster.log shows the vacuum proceeding to the other tables. select version(); PostgreSQL 7.1.2 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3 -- P. J. Josh Rovero Sonalysts, Inc. Email: [EMAIL PROTECTED]www.sonalysts.com215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 *** ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] data storage question
Hi everyone, I would like to take advantage of postgresql's ability to store arrays within records, but I wanted to make sure that I store them in the most efficient way so my performance is optimized. I want to store one-dimensional arrays of float values, each with about 6000 elements. I need to store about 6 of these in each row of the table--the table will eventually have about 10,000 rows. Will using the float8[] data type be fast, or should i create my own data type (I was thinking of packing the array in perl, maybe). Also, in using the float8[] data type, I have another question: how do i take a column of an existing postgresql table, and place it in a record of a new table with the data type float8[]? All of the examples involving arrays in the documentation only show INSERT VALUES statements, and thus I'm not sure of the syntax if I want to use select instead of values. thanks! Evan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Index usage question
What does your data look like? If you have a lot of duplicate id's, a sequential scan may be better than an index scan. If you are not sure if this is the case, try: SELECT id, count(*) AS count FROM test GROUP BY id ORDER BY count DESC LIMIT 50; This should show you the top 50 most duplicated records in your table. -r At 08:52 PM 9/4/01 +0200, Norbert Zoltan Toth wrote: I have experienced the following problem, maybe they are related: I create a table with create table test (id int, name char(10)); then I create an index on it with create index test_idx on test (id); After populating my table, the query select id from test where id='1'; uses index scan. However when I only create the index AFTER inserting rows into the table, the index is not being used for the evaluation, even if I run vacuum / vaccum analyze on test; I would also appreciate some help with this. Thank you, Norbert ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] CREATE USER vs. createuser
With createuser I managed to create users like 12345 and John Doe (with a space). It did not work with CREATE USER. Aren't they supposed to do the same? Thank you all, Mihai Gheorghiu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] recovering a control file from a shutdown database....
Somehow in upgrading from 7.1.2 to 7.1.3 (and with much operator error along the way), my database became corrupted. I believe it is only the pg_control file that was damaged. The database was completely shutdown at the time. Is there anyway to create a new pg_control file, based on the contents of the database itself? The symptoms are that the data/global/1262 file has a file size of 8192 bytes and a time stamp of last June. data/global/pg_control has a file size of 16384 bytes and a time stamp of today. When the postmaster starts up, it tells me that: database was initialized with BLCKSZ 16384, but the backend was compiled with BLCKSZ 8192. looks like you need an initdb. Bleh! I would prefer to not waste my db, and I assume initdb will do just that. Can anything be done to recover the pg_control file? Thanks, Jerry Jerry Asher [EMAIL PROTECTED] 1678 Shattuck Avenue Suite 161 Tel: (510) 549-2980 Berkeley, CA 94709 Fax: (877) 311-8688 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] SERIAL, too low a value
I want to create a sequence with a higher value than an int4. Please explain how to do this, if possible. If not, this is certainly an issue that has to be targeted real soon, or? ---Daniel Åkerud [ Don't underestimate the power of stupid people in large groups]
[GENERAL] FOREIGN KEY: MATCH FULL
Hi, What does it mean the MATCH FULL parameter in a foreign key specfication ? It seems it is only valid for a multi-column foreign key: What's a multi-column foreign key ? I've tried to search this in Bruce's book but I have a lot of problems to connect to it at http://www.postgresql.org/docs/awbook.html Thanks, Gabi :-) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Perl won't eval PgSQL boolean value
On Sun, 02 Sep 2001 20:01:27 -0400, Randall Perry wrote:
I've got an if statement that checks if a boolean value is true:
if ($cust_data-{'hold'} eq 't')
But perl will not evaluate the value. $cust_data-{'hold'} is taken from a
PgSQL boolean field (either t or f). If I use the construct above it tell
me that 'eq' is not defined thinking it's a string.
Assuming that you're using DBI and DBD::Pg, the problem is that DBD::Pg
returns 1 and 0 for booleans, rather than the expected 't' and 'f'. This
means that if you know you're always going to use Postgres (and that DBD::Pg
isn't going to change), you can simply say:
if($cust_data-{hold})
However, if you want cross-database portability (and protection from
DBD::Pg's behaviour changing), you have to do this:
if($cust_data-{hold}=~/[t1]/)
--
Peter Haworth [EMAIL PROTECTED]
perl -e '$|=s\rJust another Perl hacker,;s/\w/$\b#/g;
s/(${\chr(97+rand$=)}).#/$1/igsleep print while/#/;die$/'
-- Ilmari Karonen, Yanick and Abigail
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[GENERAL] connection failed in threaded application
I use PostgreSQL 7.1.3 on both Linux 2.4 and Solaris 8 with my multi threaded application. The application has 16 threads and each thread opens a connection to PostgreSQL. Under Linux, this works just fine. Under Solaris, 11 threads succeeds in connecting to the database, but the rest fails with: connectDBStart() -- connect() failed: No such file or directory Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port 5432? I am running postmaster with -i -N32 -B64 so there should be enough allowed connections for my application. I have set the systems SHMMAX to 16MB instead of the default 1MB. Has anyone seen this happen before, or know why it could happen? The fact that 11 threads succeeds sounds very strange to me. Greetings, Tomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] recovering a control file from a shutdown database....
Jerry Asher [EMAIL PROTECTED] writes: Can anything be done to recover the pg_control file? I've already been talking to Jerry off-list, and I trust he's managed to recover his data --- but for the archives, the answer to this is to hack up contrib/pg_resetxlog to force whatever data you need into the pg_control file. It is an *extremely* good idea to back up your whole $PGDATA directory tree (with tar or some such) before attempting such desperate measures, so that you can undo any self-inflicted damage if you get it wrong... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Indexes
On Wed, Aug 29, 2001 at 10:44:56AM +0200, Stan wrote: How can I change the sequence of characters in PostgreSQL character indexes create index some_ix on some_tbl ( substr(some_fld,3,1) || substr(some_other_fld,12,5) ); but if you look hard enough, you'll usually find a better model by restructuring your data. -- Hey, let's change the whole justice system. Everybody gets to kill one person -- if you do two, you go to jail. That should cut down on the abrasive personalities, don't you think? [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] FOREIGN KEY: MATCH FULL
On Wed, 5 Sep 2001, Gabriel Fernandez wrote: Hi, What does it mean the MATCH FULL parameter in a foreign key specfication ? It seems it is only valid for a multi-column foreign key: What's a multi-column foreign key ? It's only different on multi-column fks, it's valid but uninteresting on singles. A multi column foreign key is something like the one in the following: create table foo ( a int, b int, foreign key (a,b) references bar ); The key that's being checked is the combination of a and b. For the unspecified match type, if either is NULL, the constraint is passed. For MATCH FULL, either both must be non-NULL and match in the other table or both must be null for the constraint to pass. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PL/java?
* [EMAIL PROTECTED] (Randal L. Schwartz) wrote: | | If you'd just stop saying things that can't be backed up, I wouldn't | have to keep responding. ;-) | Where is your proof that mod_perl is slower than most any j2ee | application? I don't like to generalize either. I've been on two projects where we have replaced Perl applications with Java applications. The first one was a content management system and the second was an online dating application(like match.com). In both cases we ended up with Java applications that performed better than the Perl applications. The reasons ? - Maybe it was because our Java developers were better than our Perl developers. - Maybe we had learned something the second time implementing the application. Better architecture, system design, etc. - Maybe Java was better suited for the actual applications. In the end there is however no proof to claim that Java applications are faster than mod_perl applications. But having Java in the PgSQL backend would be nice for some, regardless of how well Java compares to Perl. -- Gunnar Rønning - [EMAIL PROTECTED] Senior Consultant, Polygnosis AS, http://www.polygnosis.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PL/java?
Gunnar == Gunnar Rønning [EMAIL PROTECTED] writes: Gunnar In the end there is however no proof to claim that Java applications Gunnar are faster than mod_perl applications. I'll settle for that. Most of the time I've seen benchmarks, it's been more the skillset of the programmers at stake rather than the languages. Gunnar But having Java in the PgSQL backend would be nice for some, Gunnar regardless of how well Java compares to Perl. Yes, I can support that. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index usage question - Norbert
(Sorry for reposting...) You said you enter some rows, but how many rows got returned by your query? You're right, it does make all the difference. With only a few rows, indexing is somehow used in the first case only (for my example), but with larger tables index scan is used in both ways. Many thanks, Norbert Though I'm not sure, PG might not treat that 1 as the integer 1, since it's in single quotes.. The index was on an integer field, perhaps that is part of the problem too... -Mitch I have experienced the following problem, maybe they are related: I create a table with create table test (id int, name char(10)); then I create an index on it with create index test_idx on test (id); After populating my table, the query select id from test where id='1'; uses index scan. However when I only create the index AFTER inserting rows into the table, the index is not being used for the evaluation, even if I run vacuum / vaccum analyze on test; I would also appreciate some help with this. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] many junction tables
Oh, Ok. Now I understand. It seems that there is no solution using SQL without using inheritance. I had some Ideas but postgres has weird sematics (not conforming SQL99) using inheritance and so it didn't work. But you should not - as you suggested - store the tablenames in a assocciation table. That is no clean database design. If I can get my ideas running, I'll post you. Arne. E Kolve wrote: The idea is this. Suppose I had say 40 or 50 tables like city or company (schools, purchases, etc) where I want to have a one to many relationship (One person can purchase many things, one person can attend many schools) I would have to create many junction tables (school_persons, purchase_persons, etc). What I am looking for is a solution to creating all these tables, if at all possible. --eric Arne Weiner wrote: I don't understand what exactly your problem is. What what makes me unsure is: why do you want to store the target table names in the association table? Eric Kolve wrote: I was wondering if anyone has a solution to the following problem. I have a few tables similar to the following. person_table city_table company_table If I want to associate a person with a one or more cities. I need to create a city_persons table that contains just the primary keys of both the city_table and person_table. If I want to associate a person to many companies, I have to do a similar thing. Now, if I wan to generalize these associaes I can create something like an associate table which will contain both id's plus two columns which indicate the source and target tables of the relaionship. Does anyone have suggestions along these lines on how to generalize such relationships? thanks, --eric ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] index naming conventions
playpen=# create table jm( playpen(# jid int NOT NULL, playpen(# mid int , playpen(# UNIQUE(jid, mid) playpen(# ); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'jm_jid_key' for table 'jm' CREATE Why isn't the index created called 'jm_jid_mid_key' ? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] jdbc driver with BigDecimal patch
hi, i'm having trouble with the BigDecimal bug in the jdbc driver using jbuilder 4 and wonder if some body has a compiled version of the jdbc with the patch applied and if i can download it thanks in advance and best regards. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
