Re: [GENERAL] Difference between UNIQUE constraint vs index

2007-02-27 Thread Jim C. Nasby
rally a SELECT) with certain conditions, the planner can make use of the knowledge that a column or set of columns is guaranteed to be unique. PostgreSQL currently can't do that. > John > > On 2/27/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > >On Tue, Feb 27, 2007 at

Re: [GENERAL] [ADMIN] increasing of the shared memory does not solve the problem of "OUT of shared memory"

2007-05-12 Thread Jim C. Nasby
On Fri, May 11, 2007 at 04:58:28PM +0300, Sorin N. Ciolofan wrote: > I increased significantly the number of shared buffers from 3000 to 100 000 > (80Mb) BTW, 100,000 shared buffers is actually 800MB, not 80. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB

Re: [GENERAL] PITR and tar

2007-05-13 Thread Jim C. Nasby
Moving to -docs... Does anyone know what the history of the docs saying that GNU tar had issues with files changing underneath it? According to this report it's actually BSD tar that has the issue. On Wed, May 09, 2007 at 10:19:05AM -0700, Jeff Davis wrote: > On Wed, 2007-05-09 at 11:40 -0500, Ji

Re: [GENERAL] Internals of PostgreSQL - Documentation or presentations

2007-05-13 Thread Jim C. Nasby
On Sun, May 13, 2007 at 08:44:48PM +0200, Gerhard Wiesinger wrote: > Are there some presentations or documents of the internals of PostgreSQL > available? > > Especially I'm looking for the concepts and detailed internals of general > transaction handling, internals of commit log, transaction lo

Re: [GENERAL] How access table by tableoid

2007-05-13 Thread Jim C. Nasby
On Sun, May 13, 2007 at 09:25:37PM +0200, Felix Kater wrote: > can I use a given tableoid (instead of the tablename) to select > columns from that table somehow? > > SELECT * FROM ??tableoid?? > So, I worked around that by peforming two queries: The first to retrieve > the table's name from pg_c

Re: [GENERAL] Vacuum non-clustered tables only

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 03:40:27PM -0700, Glen Parker wrote: > I think I know the answer to this, but... > > Is there a semi-easy way vacuum all tables in a database *except* those > that are clustered? You could query for tables that aren't clustered and use that to build a list of VACUUM comm

Re: [GENERAL] Large Database Restore

2007-05-17 Thread Jim C. Nasby
On Thu, May 17, 2007 at 08:19:08AM -0500, Lee Keel wrote: > I am restoring a 51GB backup file that has been running for almost 26 hours. > There have been no errors and things are still working. I have turned fsync > off, but that still did not speed things up. Can anyone provide me with the > op

[GENERAL] Turning a subselect into an array

2004-10-28 Thread Jim C. Nasby
FROM table_b ; -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: &quo

Re: [GENERAL] Reasoning behind process instead of thread based

2004-10-28 Thread Jim C. Nasby
mory a real pain. Of course this could probably be solved without going to a 'mono process' model. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Li

Re: [GENERAL] Turning a subselect into an array

2004-10-29 Thread Jim C. Nasby
-0600, Michael Fuhr wrote: > On Thu, Oct 28, 2004 at 05:37:29PM -0500, Jim C. Nasby wrote: > > I'm sure this has been answered before, but the search seems to be down > > again. > > > > How can I convert the results of a subselect into an array? IE: > > > >

Re: [GENERAL] Turning a subselect into an array

2004-10-29 Thread Jim C. Nasby
Thanks again for the help. I did manage to find that after a bunch of searching in the mailing list. There should really be a portion of the docs dedicated to array handling. On Fri, Oct 29, 2004 at 10:40:57PM -0600, Michael Fuhr wrote: > On Fri, Oct 29, 2004 at 05:13:02PM -0500, Jim C. Na

[GENERAL] Age of statistics

2004-11-01 Thread Jim C. Nasby
Is there any way to determine the last time statistics were analyzed on a given table? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where

[GENERAL] Storing every scrabble board possible

2004-11-22 Thread Jim C. Nasby
om storage format. Also note the author's original brute-force method is 5x larger than an improved method using PostgreSQL. So, is anyone currently running a PostgreSQL database that's 6TB and 150B rows? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your compu

[GENERAL] Way to check for function existance

2004-11-23 Thread Jim C. Nasby
gtypes = ARRAY((SELECT oid FROM pg_type WHERE typname='oid'))::oidvector; ERROR: cannot cast type oid[] to oidvector -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you

Re: [GENERAL] Postgres Design

2004-11-30 Thread Jim C. Nasby
nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" L

Re: [GENERAL] Trigger Problems

2004-11-30 Thread Jim C. Nasby
-(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.dis

[GENERAL] pgFoundary?

2004-12-02 Thread Jim C. Nasby
I've seen references to pgFoundary on the mailling lists, but I can't seem to find it anywhere. Does it actually exist? Is it the predecessor to gborg? Speaking of gborg, how come it isn't accepting projects right now? -- Jim C. Nasby, Database Consultant [EMAIL P

Re: [GENERAL] pgFoundary?

2004-12-02 Thread Jim C. Nasby
t the moment. > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Dec 3, 2004, at 12:28 AM, Jim C. Nasb

Re: [GENERAL] Scheduler in Postgres

2004-12-15 Thread Jim C. Nasby
+0100, Egy?d Csaba wrote: > Thank you Ben, I already have known these tools. I was wonder if Postgres > has an integrated tool for scheduling sp's (like Oracle has!). OK it doesn't > have. No problem just tried to make world more simpler... :) > > Bye, > -- Csaba

Re: [GENERAL] Scheduler in Postgres

2004-12-18 Thread Jim C. Nasby
> > -- > Until later, Geoffrey > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net T

Re: [GENERAL] Scheduler in Postgres

2004-12-18 Thread Jim C. Nasby
Can you open-source your code? I'm sure others would benefit from it. On Thu, Dec 16, 2004 at 09:37:04AM -0800, Steve Atkins wrote: > On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote: > > On Wed, 15 Dec 2004, Jim C. Nasby wrote: > > > > >No, PostgreSQL

Re: [GENERAL] Best practice in postgres

2004-12-18 Thread Jim C. Nasby
ent is that it watches table activity and after a certain number of insert/update/deletes it starts a vacuum (or analyze) automatically. What is new in 8.0 is the ability to tell vacuum to sleep a period of time between each tuple, so that you don't bog-down the server when vacuum is running. -- Ji

Re: [GENERAL] Scheduler in Postgres

2004-12-18 Thread Jim C. Nasby
t's up to each distro to decide if they'll include cron (and gentoo doesn't include cron for example). I'm pretty sure it's part of the base install of every unix I've used, though. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer

Re: [GENERAL] Scheduler in Postgres

2004-12-18 Thread Jim C. Nasby
it's necessarily the right way to handle scheduling, but because it's a convenient example. Oracle's newer job system is even better. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: &

Re: [GENERAL] Scheduler in Postgres

2004-12-19 Thread Jim C. Nasby
On Sat, Dec 18, 2004 at 10:22:44PM -0500, Tom Lane wrote: > Christopher Browne <[EMAIL PROTECTED]> writes: > > Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Jim C. Nasby") > > would write: > >> In PostgreSQL, there's currently no wa

[GENERAL] pgjob registered on pgFoundary

2004-12-19 Thread Jim C. Nasby
support in the backend so that users will only need to enable this. I've setup a mailing list for development discussions. I think the first order of business is to decide on the user API, but anyone who's interested in this should join the list and put your $0.02 in. -- Jim C. N

Re: [GENERAL] Scheduler in Postgres

2004-12-21 Thread Jim C. Nasby
On Tue, Dec 21, 2004 at 03:42:22PM -0500, Greg Stark wrote: > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > On Fri, Dec 17, 2004 at 12:07:21PM -0500, Greg Stark wrote: > > > > > > Csaba Nagy <[EMAIL PROTECTED]> writes: >

Re: [GENERAL] Scheduler in Postgres

2004-12-21 Thread Jim C. Nasby
le on pgfoundary. :) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: &quo

[GENERAL] PostgreSQL training curriculum

2004-12-21 Thread Jim C. Nasby
eal mostly with installation, tuning, and troubleshooting. I am being paid for this training, so keep that in mind if you're a real GPL zealot. ;) I've seen that Bruce Momjian has curriculum posted on his website, but I haven't turned anything else up yet. -- Jim C. Nasby, Database C

Re: [GENERAL] PostgreSQL training curriculum

2004-12-21 Thread Jim C. Nasby
On Tue, Dec 21, 2004 at 04:13:46PM -0600, Doug Quale wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > I am being paid for this training, so keep that in mind if you're a real > > GPL zealot. ;) > > Why would a GPL zealot care if you are

Re: [GENERAL] tool for incrementally shrinking bloated tables

2004-12-21 Thread Jim C. Nasby
> '(, 0)' doesn't work as mentioned. AFAIK it should be easy to create < and > operators for tid's, though there's some hidden gotchas there with wraparound. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy!

Re: [GENERAL] Postgres 8 release

2005-01-09 Thread Jim C. Nasby
P 7: don't forget to increase your free space map settings > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do yo

[GENERAL] handing created and updated fields

2005-01-10 Thread Jim C. Nasby
NSTEAD UPDATE SET created = current_timestamp WHERE id = NEW.id, though again I'm not sure if the serial field (id) would be handled properly. Does anyone have an example of the best way to handle this scenario? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some br

Re: [GENERAL] handing created and updated fields

2005-01-10 Thread Jim C. Nasby
IF TG_OP = ''INSERT'' THEN > NEW.created := CURRENT_TIMESTAMP; >ELSE > NEW.created := OLD.created; >END IF; >RETURN NEW; > END; > ' LANGUAGE plpgsql; Excellent; any idea which would perform better (combined v. separate trigg

Re: [GENERAL] large dbII to postgresql migration

2005-01-10 Thread Jim C. Nasby
t; > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain c

[GENERAL] OID of current function

2005-01-12 Thread Jim C. Nasby
Is there an easy way to get the OID of the currently running function? (IE: the function you're in when you execute the code to see what function you're in, if that makes any sense). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some b

Re: [GENERAL] OID of current function

2005-01-12 Thread Jim C. Nasby
On Wed, Jan 12, 2005 at 04:08:28PM +0100, Thomas Hallgren wrote: > Jim C. Nasby wrote: > >Is there an easy way to get the OID of the currently running function? > >(IE: the function you're in when you execute the code to see what > >function you're in, if that

Re: [GENERAL] OID Usage

2005-01-14 Thread Jim C. Nasby
lf-join pg_class > to get to the index itself. Not sure if it all can be done in a single > query. If you do manage to write a function that will do this I hope you can share it with the community. IMHO PostgreSQL could do with more functions for querying the system catalogs. -- Jim C. Nasb

Re: [GENERAL] Open Source Database Opportunity

2005-01-14 Thread Jim C. Nasby
> Immeuble Acacia - bur. 427 > Tel : +33 01 71 01 47 59 - Fax : +33 01 71 01 62 17 > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linu

Re: [GENERAL] Index optimization ?

2005-01-15 Thread Jim C. Nasby
only return one value in a statement/transaction? So the first time it's called it remembers what currval for that sequence is and always returns the same value? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team

Re: [GENERAL] OID Usage

2005-01-15 Thread Jim C. Nasby
;t change. Out of curiosity, what clears out the old index tuples? Vacuum? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to g

Re: [GENERAL] Index optimization ?

2005-01-15 Thread Jim C. Nasby
On Sat, Jan 15, 2005 at 06:34:11PM -0300, Alvaro Herrera wrote: > On Sat, Jan 15, 2005 at 03:11:22PM -0600, Jim C. Nasby wrote: > > > Would it make sense to have a version of currval that will only return > > one value in a statement/transaction? So the first time it's

[GENERAL] PostgreSQL code for nested sets

2005-01-15 Thread Jim C. Nasby
I'm wondering if anyone has taken the code from http://www.dbazine.com/tropashko4.shtml and converted it to PostgreSQL? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you

Re: [GENERAL] Index optimization ?

2005-01-18 Thread Jim C. Nasby
second scan starting at 8 and stopping at >10). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you wan

Re: [GENERAL] Index optimization ?

2005-01-18 Thread Jim C. Nasby
On Tue, Jan 18, 2005 at 11:03:22PM -0300, Alvaro Herrera wrote: > On Tue, Jan 18, 2005 at 07:33:51PM -0600, Jim C. Nasby wrote: > > On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote: > > > You can, howevery, accelerate something like "where f in (1,2,3,4)&quo

[GENERAL] Ways to check the status of a long-running transaction

2005-01-19 Thread Jim C. Nasby
I recall this being discussed before, but I couldn't manage to find it in the archives. Is there any way to see how many rows a running transaction has written? vacuum analyze verbose only reports visible rows. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give

Re: [GENERAL] Calculating a moving average

2005-01-20 Thread Jim C. Nasby
> > Hi, > > > > I need to calculate a moving average and I would like to do it with SQL, > or a Pg function built for this purpose. I'm on Pg 7.4. Is this possible > in Pg without a bunch of self joins, or is there a funtion available? > > > > Thanks, &

Re: [GENERAL] Calculating a moving average

2005-01-20 Thread Jim C. Nasby
ir analytic functions and see if you can come up with something generic for PostgreSQL. Even if you only do a moving average function it would be a good start. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net

Re: [GENERAL] How are foreign key constraints built?

2005-01-23 Thread Jim C. Nasby
er database I've used can do index covering, which means index scans *are* faster. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where d

Re: [GENERAL] How are foreign key constraints built?

2005-01-23 Thread Jim C. Nasby
On Sun, Jan 23, 2005 at 06:45:36PM -0300, Alvaro Herrera wrote: > On Sun, Jan 23, 2005 at 03:19:10PM -0600, Jim C. Nasby wrote: > > > > People have this weird notion that an index-based plan is always faster > > > than anything else. If you like you can try

Re: [GENERAL] How are foreign key constraints built?

2005-01-24 Thread Jim C. Nasby
On Mon, Jan 24, 2005 at 09:24:49AM -0500, Greg Stark wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > Well, every other database I've used can do index covering, which means > > index scans *are* faster. > > Still not necessarily true.

Re: [GENERAL] Recursive queries

2005-01-24 Thread Jim C. Nasby
omeone to > work on it, that would doubtless increase its priority for 8.1 or > such. > > I'd love to see it; the syntax provides ways to make complex queries a > lot cleaner even if you're not recursing. Depending on what you're doing you could also use contrib/ltree i

Re: [GENERAL] How are foreign key constraints built?

2005-01-25 Thread Jim C. Nasby
'index covering', so even when it accesses a table via an index it still has to read the base table. This is why if you need to read the entire table it's faster to seqscan than index scan. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer som

Re: [GENERAL] difficult JOIN

2005-01-25 Thread Jim C. Nasby
record in the plan table, and correlate records in the work table using that ID, or correlate based on begin and end time? BTW, I've never seen the convention id_employee; people generally use employee_id. Is it more important to know that you're talking about an ID or that you're ta

Re: [GENERAL] self-join on subselect

2005-01-26 Thread Jim C. Nasby
ur > joining column's datatypes do not match > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Wher

Re: [GENERAL] difficult JOIN

2005-01-26 Thread Jim C. Nasby
yee; people generally use > > employee_id. Is it more important to know that you're talking about an > > ID or that you're talking about an employee? Just food for thought. > > I'm not the father of this strange naming convention :) I hate getting stuck with o

[GENERAL] Guess what database is MIA from LinuxWorld's Reader's Choice

2005-01-27 Thread Jim C. Nasby
7;t know who to contact about this. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" Fre

Re: [GENERAL] changing sort_mem on the fly?

2005-01-27 Thread Jim C. Nasby
t; As a side question, are the options that will take effect with a kill > -HUP documented somewhere? If you just want to change it for one connection, you can do 'set sort_mem=newvalue;'. You might need to be an admin to do it, though. -- Jim C. Nasby, Database Consultant

Re: [GENERAL] changing sort_mem on the fly?

2005-01-27 Thread Jim C. Nasby
On Thu, Jan 27, 2005 at 06:23:05PM -0700, Michael Fuhr wrote: > On Thu, Jan 27, 2005 at 06:23:21PM -0600, Jim C. Nasby wrote: > > > > If you just want to change it for one connection, you can do 'set > > sort_mem=newvalue;'. You might need to be an admin to do it,

Re: [GENERAL] changing sort_mem on the fly?

2005-01-27 Thread Jim C. Nasby
On Thu, Jan 27, 2005 at 05:52:41PM -0800, Lonni J Friedman wrote: > On Thu, 27 Jan 2005 18:23:21 -0600, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > On Thu, Jan 27, 2005 at 02:57:22PM -0800, Lonni J Friedman wrote: > > > Is there any way to increase sort_mem without

Re: [GENERAL] changing sort_mem on the fly?

2005-01-28 Thread Jim C. Nasby
27;s a bunch of bored developers sitting around wondering what to do, it might be nice to have the ability to apply some security to GUC settings, possibly just being able to restrict them to super-user, and having a SECURITY DEFINER function users can call instead that could apply permissions. -- J

Re: [GENERAL] changing sort_mem on the fly?

2005-01-30 Thread Jim C. Nasby
m is handled. It's currently impossible to set it in a way to support moderately large sorts (say, 5% of available memory) without either embedding 'set sort_mem = blah' in your code or running the risk that at some point your database server will start swapping. I wish there was some w

Re: [GENERAL] changing sort_mem on the fly?

2005-01-30 Thread Jim C. Nasby
On Sun, Jan 30, 2005 at 08:50:49PM +1100, Neil Conway wrote: > Jim C. Nasby wrote: > >I'd really like to see an improvement in how sort_mem/work_mem is > >handled. > > So would I :) (I think it's well known that the current system is not > optimal.) >

Re: [GENERAL] changing sort_mem on the fly?

2005-01-30 Thread Jim C. Nasby
On Sun, Jan 30, 2005 at 01:05:15PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Sun, Jan 30, 2005 at 08:50:49PM +1100, Neil Conway wrote: > >> Do you have any thoughts on how to improve it? > > > See http://archives.postgr

Re: [GENERAL] FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings

2005-01-30 Thread Jim C. Nasby
che the data instead of PostgreSQL. 8.0 has a much more advanced cache management algorithm, so it might now be better to let PostgreSQL be your primary cache, but AFAIK no testing has been done to show that's the case. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give yo

Re: [GENERAL] changing sort_mem on the fly?

2005-01-30 Thread Jim C. Nasby
On Sun, Jan 30, 2005 at 04:49:39PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Since the planner knows how many rows will > > be going into the sort and how wide they are, ISTM it should be able to > > estimate how much memory will b

Re: [GENERAL] PostgreSQL vs. MySQL vs. Oracle, 2005 report card

2005-02-15 Thread Jim C. Nasby
; TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?"

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

2005-11-28 Thread Jim C. Nasby
nce archives, but take a look at shared_buffers and work_memory at a minimum. -- 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

Re: [GENERAL] Slow pgdump

2005-11-28 Thread Jim C. Nasby
check for issues? > > TIA > Patrick Hatcher > Development Manager Analytics/MIO > Macys.com > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org &

Re: [GENERAL] Group By?

2005-11-28 Thread Jim C. Nasby
ve 1 > >>> > >>> > >>> Auto_control > >>> > >>> loop_id mon valve valve > >>> serial int4 int4 int4 > >>> 1 1 3 5 > >>> 2 2 4 > >>> > >>> > >>> >

Re: [GENERAL] "invalid page header in block 597621 of relation..."error

2005-11-28 Thread Jim C. Nasby
that this almost certainly isn't one of those cases. That means something other than PostgreSQL is dropping data. Since it's apparently multiples of 4k it's reasonable to suspect the kernel or the filesystem; it's pretty unlikely it's the drives. -- Jim C. Nasby, Sr. Engineer

Re: [GENERAL] dumping data to version control using pg_dump?

2005-11-28 Thread Jim C. Nasby
New England : are useless" : > : Armidale NSW 2351 Australia : L.J.J. : > : Fax : +61 2 6772 5376 : : > +---+-+ > > ---(end of broadcast)-

Re: [GENERAL] Set a blank password for a db user

2005-11-28 Thread Jim C. Nasby
e a manner to avoid this ? > > Thanks in advance, > Claudio > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that

Re: [GENERAL] Strange VACUUM behaviour

2005-11-28 Thread Jim C. Nasby
enyaccess('_gti') > > The table is part of a slony tableset, which is subscribed on this database. > > Is there a reason that this vacuum takes so long? Maybe some lock > contention because slony replicates into this table? > > greetings, Florian Pflug -- Jim C

Re: [GENERAL] Login limitation?

2005-11-28 Thread Jim C. Nasby
administrative stuff with config files via SQL, but I'm not sure if they'd cover this case. I can see where this could be a problem for people providing hosting; if enough other users request this functionality it might make it onto the TODO list. -- Jim C. Nasby, Sr. Engine

Re: [GENERAL] Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1

2005-11-28 Thread Jim C. Nasby
; Index Scan using stats_participant_pkey on stats_participant (cost=0.00..1486391.76 rows=436912 width=4) Filter: (id IS NOT NULL) (5 rows) stats=# -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512

Re: [GENERAL] problem with psql?

2005-11-28 Thread Jim C. Nasby
QL version 8.0.0 and > Mac OS X 10.3.9. Can anyone tell us what's going on? > > Thanks! > Tim > > > > > > ---(end of broadcast)------- > TIP 3: Have you checked our extensive FAQ? > > h

Re: [GENERAL] Group By?

2005-11-28 Thread Jim C. Nasby
the same row as their associated > mon. > > Bob > - Original Message ----- > From: "Jim C. Nasby" <[EMAIL PROTECTED]> > To: "Bob Pawley" <[EMAIL PROTECTED]> > Cc: "Guy Rouillier" <[EMAIL PROTECTED]>; "PostgreSQL General&

Re: [GENERAL] Login limitation?

2005-11-29 Thread Jim C. Nasby
all > user information and if a new user is added it modifies the pg_hba.conf and > reloads the server? > > thanks, > -- csaba > > -----Original Message- > From: Jim C. Nasby [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 29, 2005 12:46 AM > To: Egy?d Csaba >

Re: [GENERAL] Question

2005-11-29 Thread Jim C. Nasby
\d" report ? > > Thank you, > Peter Futaro > > > > - > Yahoo! Music Unlimited - Access over 1 million songs. Try it free. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwa

Re: [GENERAL] memory leak under heavy load?

2005-11-29 Thread Jim C. Nasby
;)"); > $dbh->commit(); > $q++; > last if $q eq 'fzzz'; > $i++; > if (0 == $i % 1000) { > system("ps uxf"); > } > } > print "one iteration done\n"; > <>; >

Re: [GENERAL] memory leak under heavy load?

2005-11-30 Thread Jim C. Nasby
] on behalf of hubert depesz lubaczewski > Sent: Wed 11/30/2005 12:59 PM > To: Jim C. Nasby > Cc: PostgreSQL General > Subject: Re: [GENERAL] memory leak under heavy load? > > > *** > Your mail has been scanned by InterScan VirusWall. > ***-*

Re: [GENERAL] Finding uniques across a big join

2005-11-30 Thread Jim C. Nasby
ticular combination of feature type, name and > container. > > Any help is appreciated! > > - John Burger > MITRE > > > ---(end of broadcast)------- > TIP 4: Have you searched our list archives? > >

Re: [GENERAL] What is the deal with mailing lists?

2005-11-30 Thread Jim C. Nasby
k on the machine you sent the email from appears to be 13 minutes fast. Anyway, next time you're seeing a delay take a look at the headers and see if you can pin down what the bottleneck is. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pe

Re: [GENERAL] How to check options PostgreSQL was started with

2005-11-30 Thread Jim C. Nasby
On Wed, Nov 30, 2005 at 06:18:01PM +, frank church wrote: > > How do you check the options a PosgreSQL service was started with? > > Frank Easiest way is to do show all; from psql. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwa

Re: [GENERAL] Finding uniques across a big join

2005-11-30 Thread Jim C. Nasby
On Wed, Nov 30, 2005 at 01:29:17PM -0500, John D. Burger wrote: > Jim C. Nasby wrote: > > >It will probably be a win to come up with a list of potential records > >from each table, instead of after doing the 3-way join. so something > >like: > > > >(SELEC

Re: [GENERAL] default_index_tablespace?

2005-12-02 Thread Jim C. Nasby
dex creation (which really > isn't that difficult). > > > Adding an INDEX_TABLESPACE option to CREATE DATABASE might be worthwhile if > what we have done is typical. Or if pg maintainers want to suggest this > usage as sensible. > > -K -- Jim C. Nasby, Sr.

Re: [GENERAL] alter table schema on 8.0.X

2005-12-02 Thread Jim C. Nasby
s for a production system. If you do code up something that takes all those tables into account it'd be good to share it with the community. If nothing else, one of the core coders might tell you how risky it actually is. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] P

Re: [GENERAL] default_index_tablespace?

2005-12-02 Thread Jim C. Nasby
On Fri, Dec 02, 2005 at 06:06:25PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > TODO? > > %Add default_index_tablespace GUC and database parameter. > > That was part of the original tablespace proposal and was rejected for > (what

Re: [GENERAL] 8.1, OID's and plpgsql

2005-12-02 Thread Jim C. Nasby
t, I don't think there's any way to get the ctid of a row you just inserted anyway... Maybe the docs should be changed to just say that you should never reuse a ctid outside of the transaction you obtained the ctid in? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pe

Re: [GENERAL] 8.1, OID's and plpgsql

2005-12-02 Thread Jim C. Nasby
On Fri, Dec 02, 2005 at 06:58:39PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Maybe the docs should be changed to just say that you should never reuse > > a ctid outside of the transaction you obtained the ctid in? > > That's

Re: [GENERAL] 8.1, OID's and plpgsql

2005-12-02 Thread Jim C. Nasby
On Fri, Dec 02, 2005 at 06:58:39PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Maybe the docs should be changed to just say that you should never reuse > > a ctid outside of the transaction you obtained the ctid in? > > That's

Re: [GENERAL] deadlock on the same relation

2005-12-06 Thread Jim C. Nasby
an't do one of > those and later ask for exclusive lock within the same transaction. > The general rule is "get the strongest lock you will need first". And better yet, don't grab an exclusive lock... I'm curious; what are you doing that requires one? -- Jim C. Nasby,

Re: [GENERAL] Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits

2005-12-12 Thread Jim C. Nasby
ERICs, I didn't care about stuff like > that. I think that if there are any esoteric cases where people are doing these kinds of things with numeric, they could probably be best answered by offering a completely different system anyway, using a different type name. The 5 people in the worl

Re: [GENERAL] pg_autovacuum

2005-12-12 Thread Jim C. Nasby
ile have > anything about it in them. Try -d 2; it should give enough info to tell if it's at least checking tables. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.n

Re: [GENERAL] Quick hack: permissions generator

2005-12-12 Thread Jim C. Nasby
couple of groups of tables > that have somewhat different access pattern for couple of user > groups. Small example: Seems neat. I suggest putting it on http://pgfoundry.org. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwo

Re: [GENERAL] Excessive vacuum times

2005-12-12 Thread Jim C. Nasby
at are known to have dead tuples, which should hopefully mean no more index-scans during vacuum as well. Hopefully this makes it into 8.2... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vc

Re: [GENERAL] Excessive vacuum times

2005-12-13 Thread Jim C. Nasby
On Mon, Dec 12, 2005 at 11:09:01PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Mon, Dec 12, 2005 at 06:26:37PM -0500, Tom Lane wrote: > >> No. Avoiding that would require a new approach to > >> vacuum-vs-ordinary-index

Re: [GENERAL] Memory Leakage Problem

2005-12-13 Thread Jim C. Nasby
On Mon, Dec 12, 2005 at 08:31:52PM -0800, Joshua D. Drake wrote: > > > >>It sure is. Gentoo with kernel version 2.6.12, built for x86_64. > >>Looks like we have a contender for the common factor. :) > >> > > > >Please tell me you're *not* running a production database on Gentoo. > > > > >

Re: [GENERAL] pg_autovacuum

2005-12-13 Thread Jim C. Nasby
I think if you up the verbosity one more level it'll give you information about the stats on the tables it's looking at. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: ht

<    1   2   3   4   5   6   7   >