Re: [ADMIN] Specific questions about wraparound and vacuum

IL PROTECTED]> wrote: > On Wed, Aug 08, 2007 at 12:07:14PM -0400, Nick Fankhauser wrote: > > 2) If a regular (non-full) vacuum will not reset the XID. Will a > > dump/restore take care of wraparound? We have done this in the past for > > space reclamation because we seem to

Re: [ADMIN] Specific questions about wraparound and vacuum

On 8/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: > "Nick Fankhauser" <[EMAIL PROTECTED]> writes: > > One other question- when I'm vacuuming, I always get the warning: > > > WARNING: some databases have not been vacuumed in transactions > > HINT:

Re: [ADMIN] Specific questions about wraparound and vacuum

On 8/8/07, Nick Fankhauser <[EMAIL PROTECTED]> wrote: > the largest containing rows. Oops- I meant to say "...the largest containing 56 million rows". One other question- when I'm vacuuming, I always get the warning: WARNING: some databases have not been vacuum

[ADMIN] Specific questions about wraparound and vacuum

tions are occurring? If so, how to I deal with template0? Thanks. -Nick -- ------ Nick Fankhauser [EMAIL PROTECTED] http://www.doxpop.com 765.965.7363 765.962.9788 (Fax) Doxpop - Public Records at Your Fingertips.

[ADMIN] using disable-triggers in pg_dump

e confirm that this is indeed affecting the database for all sessions and if so, suggest a way to turn off the triggers just for the session doing the data copy? Thanks -Nick ----- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765

Re: [ADMIN] YOUR SITES SEARCH FEATURE DOES NOT WORK!

Can't help with the search engine, but the answer to your question is: psql . I'd recommend starting with the tutorial in the docs for questions like this. http://www.postgresql.org/docs/7.3/static/tutorial-start.html I can also confirm that the search engine in the docs area (http://www.postgres

Re: [ADMIN] Problem with n_distinct being consistently inaccurate.

cation and support. Thanks (I mean it!) -Nick ----- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ --

[ADMIN] PG version for n_distinct question.

Oops- forgot to give the version on that last question: I'm running version 7.3.2 on a Debian Linux platform. -NF - Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court recor

Re: [ADMIN] Problem with n_distinct being consistently inaccurate.

> Just out of curiosity, what happens if you make it bigger than 92k? > Does a value 10x or 100x reality change the plan? Neither one makes a change- perhaps something else is at work here- my understanding of the finer points of query plans is shaky- Here is the query and the plan I'm getting:

Re: [ADMIN] Problem with n_distinct being consistently inaccurate.

> It certainly should be the case. starelid matches to pg_class.oid and > staattnum matches to pg_attribute.attnum. My problem was that I was looking up "event_date_time" in pg_class.relname (and finding it), but the oid matched nothing. when I looked for 'event' in pg_class & 'event_date_time'

Re: [ADMIN] Problem with n_distinct being consistently inaccurate.

> AFAIK, estimating number of distinct values from a small sample is > inherently an ill-conditioned problem. If I had been getting estimates all over the map, I'd have been a bit more unconcerned, but what I'm seeing is a very consistent number that also increases and tends to be more consistent

[ADMIN] PG version for n_distinct question.

Oops- forgot to give the version on that last question: I'm running version 7.3.2 on a Debian Linux platform. -NF - Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court recor

[ADMIN] Problem with n_distinct being consistently inaccurate.

y missing what n-distinct is supposed to contain ? Thanks! -Nick ----- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ ---(end of broadcast)---

Re: [ADMIN] How to read a sequence without incrementing it?

Use currval() See: http://www.postgresql.org/docs/7.3/static/functions-sequence.html -Nick > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Pierre Couderc > Sent: Friday, August 29, 2003 11:27 AM > To: [EMAIL PROTECTED] > Subject: [ADMIN] How to read

Re: [ADMIN] changing field length

Jodi- Here's an example of the "hack" approach, which I've used without causing any problems for some time: update pg_attribute set atttypmod = 104 where attrelid = ( select oid from pg_class where relname = 'actor' and attname = 'actor_full_name' ); In your case, you'd substitute 254 for 104, y

Re: [ADMIN] Host configuration

In addition to making sure you do a reload to pick up the new values, make sure that the pg_hba.conf file you are editing is in fact the one being read by the postmaster. There have been a few situations where a symbolic link pointing from the data directory to a conf file living somewhere else ge

Re: [ADMIN] pg_restore problem!!!

ied to gzip one of the dump files: and got 0% savings! > > If this is a way to reduce the size of my nightly dumps I'm all for it! :) > > -- > David Olbersen > iGuard Engineer > St. Bernard Software > 11415 West Bernardo Court > San Diego, CA 92127 >

Re: [ADMIN] pg_restore problem!!!

> To nit-pick, this is a "useless use of cat". > > In UNIX-land, simple input redirection will work much better: > > psql [dbname and various options] < [filename] Good point... to elaborate further, the reason I was in a piping mindset is that with a large database, it also makes sense to comp

Re: [ADMIN] pg_restore problem!!!

Mago- pg_restore is used to restore a dump file created in one of the non-text formats such as tar format. To restore from a plain-text dump file, just pipe it into psql like so: cat [filename] | psql [dbname] -Nick - Nick

Re: [ADMIN] PG_dump fatal error (second post)

> prod.dump.tar is the result of pg_dump, not a command, as for > your text sample below. > "pg_dump -Ft prod > prod.dump.tar" would be better. Jean-Michel- I'm sorry- that was a typo in my original post that I should have corrected. The actual command that I'm using is in fact "pg_dump -Ft prod

Re: [ADMIN] common_fields: permission denied

Olivier- The pg_hba.conf file controls how users connect to the database, but if the user does not have grants on the specific table within the database, I think you'd be getting an error similar to the one you describe. Does the user you created either have "dba" privileges or select access on th

Re: [ADMIN] PG_dump fatal error (second post)

Hi Dani- The file is nowhere near 2GB, and a regular text dump running at the same time always completes successfuly, with a resulting file size about 4 times what the tar-format file was when it died. Also note that this worked on the same server using the same database using v7.2 of postgreSQL.

[ADMIN] PG_dump fatal error (second post)

ge blobs (mine has no blobs), and the suggestion was to upgrade to 7.3. I couldn't find a resolution in that thread, so I'm not sure if it ever got worked out. Any thoughts?? Thanks! -Nick - Nick Fankhauser

[ADMIN] Error message using pg_dump with tar format

ggestion was to upgrade to 7.3. I couldn't find a resolution in that thread, so I'm not sure if it ever got worked out. Any thoughts?? Thanks! -Nick ----- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.

Re: [ADMIN] Error message using pg_dump with tar format

> Does it stop at a filesize limit imposed by the OS or filesystem, such > as 2.0GB as commonly found on linux, or NFS? No, in this case, it is stopping at about 1.3 GB uncompressed. I usually pipe the pg_dump output into gzip but removed the gzip to simplify the situation while testing. Under

[ADMIN] Error message using pg_dump with tar format

ggestion was to upgrade to 7.3. I couldn't find a resolution in that thread, so I'm not sure if it ever got worked out. Any thoughts?? Thanks! -Nick ----- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.

Re: [ADMIN] Problem with tcp/ip connection, postgresql.conf

> While experimenting with this, I noted that the postmaster will not > complain if postgresql.conf is not found --- though it will complain if > it finds the file but can't read it (eg permission failures). It seems > to me this is a bug, or at any rate a bad idea. There should be at least > a w

[ADMIN] Problem with tcp/ip connection, postgresql.conf

distribution, so there may be something odd about the directory structure that I'm missing. Is there a SuSe user on the list that can help? Thanks -Nick --------- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765

[ADMIN] Problem with tcp/ip connection, postgresql.conf

distribution, so there may be something odd about the directory structure that I'm missing. Is there a SuSe user on the list that can help? Thanks -Nick --------- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765

Re: [ADMIN] Backup and Restore

Dave- I ran into this recently & made a similar inquiry of the list. Apparently the pg_restore dependency problems are known issues, but not fixed in 7.2. I haven't looked to see if it is fixed in 7.3 yet. In our case, space was not an issue and backups run quickly enough that I just do two backu

Re: [ADMIN] Insert Error

It means you need to give that error message to your system administrator, who will understand what it means. If you are the system administrator, use "df" to learn which filesystem is full & then spend some time learning about filesystem management so you can fix it. -Nick > -Original Mess

Re: [ADMIN] New User - Please Help

Michael- This document should get you started: http://www.postgresql.org/idocs/index.php?tutorial.html look at section 1.3 in particular. -Nick > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Cupp > Sent: Monday, January 27, 2003 11:33 AM

Re: [ADMIN] query problem

: "LOG: pq_recvbuf: recv() failed: Connection reset by peer" I feel that JDBC doesn't like query 2). because both query 1) & 2) works if queried in db directly. Many thanks for any clarification on this. Helen Nick Fankhauser <[EMAIL PROTECTED]> wrote: Helen- There is a separ

Re: [ADMIN] query problem

Helen- There is a separate JDBC list for related questions that I suggest you use in the future. A code sample is needed for a really good answer, but I'll make a guess. This message is probably telling you that you are either trying to set a value in a where clause that has a higher index than t

Re: [ADMIN] PKs for dictionary tables

The preferred method is to have a PK and store it in your big table. In addition to being more "normal" and probably saving a little space, this gives you the option of changing the corresponding values in one place. So for instance if your lookup table was "datatypes", and you had entered "Sting"

Re: [ADMIN] Connection problem (newbie question)

Paul- > Finally, I ran 'ps -ef' just after the postmaster successfully started (or > so it says), but there's no sign of any postgresql related processes: This is clearly your problem. See if you can locate the log file & find out why the processes are dying right away. Usually the location of th

Re: [ADMIN] proper db standard

Jodi- Given you two choices, I would go for #2, but consider this third option: Publication: pub_id other_stuff Keyword: keyword_id keyword_text Keyword_assignment: pub_id keyword_id Keyword only contains 6 records, but you can add new keywords as needed in the future. (Option #1 didn't give y

Re: [ADMIN] pg_restore error: function plpgsql_call_handler already exists with same argument types

> You could check this by running pg_restore with query logging > turned on, to see what commands it's actually issuing -- or just do > "pg_restore -s" into a text file and eyeball the generated script. I did this, and there is a view created before the table it refers to. > There are a lot of

Re: [ADMIN] pg_restore error: function plpgsql_call_handler already exists with same argument types

using that format. So... That's the whole story- Any thoughts on what I should try next? Thanks, -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ -

Re: [ADMIN] pg_restore error: function plpgsql_call_handleralready exists with same argument types

Oliver- Thanks for the idea. Unfortunately, it still won't go. We've never touched template1, but just to make sure, I tried using template0 to ensure an empty DB with the same results: nickf@morgai:~$ createdb -D PG_ALPHA -T template0 test CREATE DATABASE nickf@morgai:~$ pg_restore -dalpha tes

[ADMIN] pg_restore error: function plpgsql_call_handler already exists with same argument types

a plain text dump file, so I'm still mystified. Any thoughts? Thanks. -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulti

Re: [ADMIN] Troubles at Startup

> I'm operating in Debian Woody, with PostgreSQL 7.2. -- Hugh Hugh- Did you install from the Debian package or compile-your-own? We're running 7.2 on Debian 2.4, and the startup/shutdown script that Oliver Elphick created for the Debian package has worked flawlessly for us. If you did your own

typo RE: [ADMIN] FATAL 1: Sorry, too many clients already

Oops... > Nope, this is definitely a message from the postgresql backend. What I *meant* to type was: this is definitely a message from the postgresql backend referring to too many client connections. ---(end of broadcast)--- TIP 2: you can get o

Re: [ADMIN] FATAL 1: Sorry, too many clients already

>> If you aren't using pooled >> connections, maybe you just have more users on the web. > > But is there really that number of backends/connections present? Assuming there is no connection pooling going on, then yes, it is reasonable to assume that more users means more connections. (I don't kn

Re: [ADMIN] FATAL 1: Sorry, too many clients already

AM- The band-aid patch is to increase the number of connections available. Check out this link to the idocs: http://www.postgresql.org/idocs/index.php?runtime-config.html Maybe that will keep things running for you while you go over the code to learn why you're using more connections now. If you

Re: [ADMIN] USERS

Fred- I'm not familiar with phppgsql, so I can't help with the specific problem there, but perhaps someone else on the list can help with the next step. You do seem to be getting a connection at this point, but are having some sort of authorization problem in PHP. You may also want to try the pgs

Re: [ADMIN] USERS

Fred: Try following this link to the interactive Doc: http://www.postgresql.org/idocs/ This link will tell you how to allow tcp/ip access using the pg_hba.conf file: http://www.postgresql.org/idocs/index.php?client-authentication.html This link will tell you how to make sure the server is acc

Re: [ADMIN] hardware question

Here's a bit more anecdotal info on multi-processor systems- We've got one system with Tyan motherboard & 2 1.2 GHz Athlon processors, SCSI Raid, 1 GB RAM. We're running Debian Linux 2.4.17 & Postgres 7.2.1. We're happy with the performance, & both processors are being used by postgresql. We hav

Re: [ADMIN] logging queries

DEBUG_PRINT_PARSE (boolean) > DEBUG_PRINT_REWRITTEN (boolean) > DEBUG_PRINT_PLAN (boolean) > DEBUG_PRETTY_PRINT (boolean) > > Hope this helps > Phil > - Original Message - > From: "Nick Fankhauser" <[EMAIL PROTECTED]> > To: "pgsql-admi

[ADMIN] logging queries

if this is possible & if so, what runtime settings are needed? Thanks -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services ht

Re: [ADMIN] unsubscribe me for heavens sakes!!!!!!!!!

TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of ashwini sridhar > Sent: Tuesday, July 09, 2002 8:51 PM > To

Re: [ADMIN] PostgreSQL data -> Oracle

Back in the days when I used Oracle, there was something called SQL*Loader that allowed you to read a flat ASCII file into an Oracle table. If I were doing this, I think I'd do a pg_dump of the data, filter the dump file to remove the "copy" commands & then use SQL*Loader or it's newer equivalent

Re: [ADMIN] Oracle data -> PostgreSQL

http://techdocs.postgresql.org/ (Look about halfway down the page) -Nick > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Leonardo Camargo > Sent: Wednesday, July 03, 2002 1:57 AM > To: [EMAIL PROTECTED] > Subject: [ADMIN] Oracle data -> PostgreSQ

Re: [ADMIN] db connection fails

--Original Message- > From: Mark Tessier [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 02, 2002 1:27 PM > To: Nick Fankhauser > Cc: [EMAIL PROTECTED] > Subject: Re: [ADMIN] db connection fails > > > > > > Have you tried doing a network connection with your > "a

Re: [ADMIN] db connection fails

o, take a look at your pg_hba.conf file. Info on how to set it up can be found here: http://www.postgresql.org/idocs/index.php?client-authentication.html -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax

Re: [ADMIN] db connection fails

file locations may vary by OS flavor, but in Debian, you can find a log in /var/log/postresql.log. You may have to turn on logging in postgresql.conf. -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax

Re: [ADMIN] Change date format

You can set it either within the session, or set a default. For details, check this page in the docs: http://www.postgresql.org/idocs/index.php?sql-set.html (Look for DATESTYLE) Regards, -Nick -- Nick Fankhauser [EMAIL

Re: [ADMIN] Are statistics gathered on function indexes?

10:37 PM > To: [EMAIL PROTECTED] > Cc: pgsql-admin > Subject: Re: [ADMIN] Are statistics gathered on function indexes? > > > "Nick Fankhauser" <[EMAIL PROTECTED]> writes: > > [see subject] > > Nope, they ain't. I agree they should be. > > > Can s

[ADMIN] Are statistics gathered on function indexes?

usand rows really are returned indicates that the index would still be a good choice. Is there a way to make the planner favor index scans a bit more? (Other than the drastic set enable_seqscan to off.) Thanks -Nick -- Ni

Re: [ADMIN] Missing or Erroneous pg_hba.conf file

onf to you off-list, and you can use it as a starting point to edit again, (using a unix editor this time ). Or alternately, you can probably use a dos->unix filter on the old file. Regards, -Nick ------ Nick Fankhauser [EM

Re: [ADMIN] Importing Database

-Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[E

Re: [ADMIN] set permanent date style

> Cc: Martin Teoh; [EMAIL PROTECTED] > Subject: Re: [ADMIN] set permanent date style > > > "Nick Fankhauser" <[EMAIL PROTECTED]> writes: > > in postmaster.conf, add a line that looks something like this: > > PGDATESTYLE=ISO,European > > I do not beli

Re: [ADMIN] Divide Date with integer

.org/idocs/index.php?sql-expressions.html Regards, -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ -Or

Re: [ADMIN] Err sum number with Date ?

x.php?functions.html You may also want to learn how to create your own functions in case there is no equivalent: http://www.postgresql.org/idocs/index.php?sql-createfunction.html -Nick -- Nick Fankhauser [EMAIL PROTECTED]

Re: [ADMIN] What err ???

issues. (Like my silly questions!) -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---

Re: [ADMIN] What err ???

o move them to postgresql, or simply how to make the run better in access? Maybe if you restated the question you'd get some better responses. -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.

Re: [ADMIN] What err ???

li As Coluna FROM >Clientes WHERE RazaoSocial Like '%A%' Regards, Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/

Re: [ADMIN] LIKE operator and indexes

Marc- I've just gone through some similar query optimizing work, and I can confirm that LIKE can definitely use an index if the initial characters are supplied as in the example you sent. -Nick -- Nick Fankhauser [

Re: [ADMIN] how to install postgresql!!

Try: ls -al /tmp make sure the permissions on /tmp are: drwxrwxrwt >please answer in chinese That's a skill I don't have- I hope you can work with this... -Nick ------ Nick Fankhauser [EMAIL PROT

Re: [ADMIN] two databases

s on a different machine, you'd want to add the IP address(es) & mask & change local to host. -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Soft

Re: [ADMIN] cannot find attribute 1 of relation (second occurrence.)

Tom- No Problem- we'll do that. Is there a table that contains the mapping from the database object names to the actual filenames? -Nick > Next time it happens, would you shut down the > postmaster and make a copy of pg_attribute and its indexes (the physical > files) to send to me, before you

[ADMIN] cannot find attribute 1 of relation (second occurrence.)

time, the only coincidence I can think of was updating pg_attribute.atttypmod for several records last night. Could this have caused my problem? Any other ideas? Thanks everyone! -Nick ------ Nick Fankhauser [EMAIL PROT

Re: [ADMIN] Is it safe to increase pg_attribute.atttypmod ?

Tom, Joe: > Yup, that's the standard hack. Thanks very much! This saved us hours. -Nick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

[ADMIN] Is it safe to increase pg_attribute.atttypmod ?

at will come back to haunt us later? -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---(end of broadcast)--- TIP 5: Have you checke

Re: [ADMIN] [JDBC] Problem: upgrade from 7.1.3 to 7.2.1 ( database encode with ENC_TW)

Gordon- This looks like a subject for the PSQL-ADMIN list. I'll forward it over there, & you should also look there for the response. I know that one of the 7.1->7.2 issues is that unicode chars were not checked/rejected by 7.1 if there was no multibyte support in the compile, but you seem to hav

Re: [ADMIN] A couple of errors encountered in 7.1.3=>7.2.1-2 data migration

Tom- Thanks! your diagnosis was correct & the repair worked. -Nick & Ray > I'm beginning to think there is something seriously messed up about your > installation. The simplest theory is that the indexes on pg_attribute > are corrupted. ... > You should be able to recover using REINDEX

[ADMIN] A couple of errors encountered in 7.1.3=>7.2.1-2 data migration

are appreciated. Regards, -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---(end of broadcast)--- TIP 3: if posting/r

Re: [ADMIN] ALTER TABLE for field modify...

ints: http://www.postgresql.org/idocs/index.php?sql-createtable.html regards, -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http:/

Re: [ADMIN] Permission on tables

work either, > which I find strange. It would seem that life is MUCH easier if Apache and > Postgres are installed on the same host, but that's not the case... > > I'm reinstalling the apache machine now anyway, because I don't like the > way RH installed the rpm'

Re: [ADMIN] Permission on tables

on that the servers are separated. You could test the connectivity & authorization from X to Y by trying (from X) psql -hY regards, -Nick ----- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop -

[ADMIN] Avoiding transaction ID wrap

ry? -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgr

Re: [ADMIN] An Analyze question

> In the "after" case you are showing "18105XS" as the most common > actor_id, with a frequency of 11.2% of the entries. Where'd that > come from? Is it correct? I believe this is correct, and the reason I've not been getting poor performance on the old database is that the stats are not up to

Re: [ADMIN] An Analyze question

> select attname,attdispersion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'actor_case_assignment'; > > in each database? Here are the results: The "Before" database: attname | attd

Re: [ADMIN] An Analyze question

> Could we see the queries? (No, I do not remember your view definitions.) Sure! I'll append the details below. (I was hoping we had correctly guessed the cause & you wouldn't need details...) > Offhand I would think that 7.2 is smart enough to deal with this We're on 7.1.3. We're working to

[ADMIN] An Analyze question

48) -> Seq Scan on local_case_type (cost=0.00..12.22 rows=522 width=48) ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] Connection problem

pg_hba. -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -Original Message- > From: [EMAIL PROTECTED] > [mailto

Re: [ADMIN] JDBC and servlet...

-test the second issue would be to do an "su - www-data" while running your tests from the command line. Hope this helps! -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.97

Re: [ADMIN] Bad plan

Brian- I'm not sure if this will help the performance, but I believe this statement is equivalent: update v set nl=nl+1 where exists (select 'x' from l where l.sid = v.id and l.did = 123456); -Nick --

Re: [ADMIN] JDBC and servlet

osing connections really drags your performance down quickly! Regards, -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services

Re: [ADMIN] string PK vs. interger PK

much so they must be close. -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ -Original Message

Re: [ADMIN] A *short* planner question

re detailed stats and hopefully would make a better > estimate of the number of matches. 7.2 on our development box is on the middle-priority TODO list. (2-4 weeks.) I'll provide feedback when we get there. As always, thanks for the help! -Nick -------

Re: [ADMIN] A *short* planner question

d add a bit of code to my load process that will populate this field with upper(actor_full_name). It's a bit of a kludge, but should work until the day that you get to adding stats for function indexes. Thanks for the help. -Nick ------

[ADMIN] A *short* planner question

I know I'm about to become a pest, but I promise, this is a short one! Before doing the explain below, I specifically did a verbose analyze & noted that the results seemed in line with what I expected. I'm on v7.1.3 of PGSQL Here's the query that runs too slow: (It takes about 30 seconds on a 1.

[ADMIN] More question about plans & explain (long)

the field? I guess that's enough questions for now. If you've made it this far, thanks for reading all of this! I look forward to hearing your thoughts. -Nick -- Nick Fankhauser [EMAIL PRO

[ADMIN] Will an outer join on two indexed fields use the indexes?

ill be more efficient? Thanks! -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---(end of broadcast)--

Re: [ADMIN] New User - options and other "get going" questions.

le: /etc/init.d/postgresql restart 5)Initialize the database location: (su - postgres; initlocation PG_STAGING) 6)Create the database: (createdb staging -D PG_STAGING) Regards, -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone

Re: [ADMIN] A plan returned by explain doesn't make sense to me

Tom Lane wrote: > The only reason the planner should choose a single-column index over > using the first column of a multi-column index is that the latter index > is likely to be physically larger and thus require more I/O to access. > So, there's no penalty in the cost calculations other than the

Re: [ADMIN] A plan returned by explain doesn't make sense to me

> That seems strange to me also, particularly if the index column ordering > is indeed actor_id,case_id and not the other way round Tom- Actually, it *is* the other way around- I didn't realize that could make a difference. Here's the line that creates it: create unique index actor_case_assign

[ADMIN] error in my last post

d it will be correct. -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/

[ADMIN] A plan returned by explain doesn't make sense to me

arrange my query to help the planner come to the same conclusion, and most puzzling, why does the planner choose an index that involves actor_id? Many thanks to those of you who read through all of this! Any suggestions? -Nick -------

  1   2   >