Re: [GENERAL] Core reported from vaccum function.
"Mavinakuli, Prasanna (STSD)" <[EMAIL PROTECTED]> writes: > Thanks Alvaro for your time and suggestions.Yes we do understand that we > are expected to use the higher versions.But as Bruce pointed out > already,the same is NOT recommended when there is NO imperative > reason. Say again? Bruce's point was the opposite: not upgrading is generally more risky than upgrading. It's certainly true that we have sometimes introduced new bugs into minor version updates, but the odds of being bitten by one of those are considerably lower than the odds of being bitten by one of the ones we did fix. This is particularly true when you are talking about a release as ancient as 7.4.2 is. Read the release notes sometime: http://developer.postgresql.org/pgdocs/postgres/release.html and ask yourself if you really want to continue risking exposure to all the known, fixed bugs in the 7.4 series. 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
Re: [GENERAL] Core reported from vaccum function.
Mavinakuli, Prasanna (STSD) wrote: > Hello Alvaro, > > Thanks Alvaro for your time and suggestions.Yes we do understand that we > are expected to use the higher versions.But as Bruce pointed out > already,the same is NOT recommended when there is NO imperative > reason.Hence we were just in the process of validating and justifying > the upgrade. You are mistaken. Bruce said exactly the opposite. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Auto Starting +/or Shutdown on OS X
On Jul 31, 2007, at 3:27 PM, Ralph Smith wrote:
How come shutdown isn't clearing things up?
Or is the problem in the startup?
There is a small package you can download from this page which will
install in StartupItems and handle things correctly:
http://www.entropy.ch/software/macosx/postgresql/
Direct link:
http://www2.entropy.ch/download/pgsql-startupitem-1.2.pkg.tar.gz
I have also included the script below.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
==
#!/bin/sh
. /etc/rc.common
##
# Start up the PostgreSQL database server on Mac OS X / Darwin
#
#
# History
# ---
#
# 2002-08-21 Marc Liyanage <[EMAIL PROTECTED]>
# Changed startup to use pg_ctl
#
# 2002-08-19 Ed Silva <[EMAIL PROTECTED]>
# Modified startup script to conform
# to new SystemStarter format for Mac OS X 10.2
#
# 2001-04-02 Marc Liyanage <[EMAIL PROTECTED]>
# First version
#
# 2001-12-02 Johan Henselmans <[EMAIL PROTECTED]>
# Enhanced after carefully studying the Frontbase
# startup sequence ;-)
# Now provides a stop procedure for a graceful shutdown
# and a hard kill if the clean shutdown doesn't work.
#
# 2001-12-02 Marc Liyanage <[EMAIL PROTECTED]>
# Added localized startup messages in 7 languages
# by adapting the resources of the Apple-supplied
# "Sendmail" startup script.
#
#
# License
# ---
#
# The PostgreSQL BSD-style license applies to this file
#
StartService ()
{
if [ "${POSTGRES:=-YES-}" = "-YES-" ]; then
ConsoleMessage "Starting PostgreSQL database server"
su - postgres -c '/usr/local/bin/pg_ctl start -D /usr/local/
pgsql/data -l /usr/local/pgsql/logfile -o -i'
fi
}
StopService()
{
ConsoleMessage "Stopping PostgreSQL database services"
/usr/local/bin/pg_ctl stop -D /usr/local/pgsql/data
x=`/bin/ps axc | /usr/bin/grep postgres`
if /bin/test "$x"
then
set $x
kill -9 $x
fi
}
RestartService ()
{
StopService
StartService
}
RunService "$1"
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Polymorphic functions' weird behavior
"Vyacheslav Kalinin" <[EMAIL PROTECTED]> writes: > Another little issue with that function. I cannot make it > accept NULL as an argument. You'd have to cast the NULL to some specific array type. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] array_to_set functions
On 8/1/07, Decibel! <[EMAIL PROTECTED]> wrote: > David Fetter and I just came up with these, perhaps others will find > them useful: > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF > anyelement LANGUAGE SQL AS $$ > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, > $2)) i > $$; > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement > LANGUAGE SQL AS $$ > SELECT array_to_set($1, 1) > $$; very nice, although IMO there is a strong justification for these functions to be in core and written in C for efficiency (along with array_accum, which I have hand burn from copying and pasting out of the documentation). merlin ---(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] array_to_set functions
David Fetter and I just came up with these, perhaps others will find them useful: CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i $$; CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ SELECT array_to_set($1, 1) $$; -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp6qc2KGTEjC.pgp Description: PGP signature
Re: [GENERAL] Auto Starting +/or Shutdown on OS X
Apologies for the huge post, but it's got everything relevant that I can think of. See below. Michael Glaesemann wrote: On Jul 31, 2007, at 14:27 , Ralph Smith wrote: === INITIAL POST I'm using scripts in /Library/StartupItems/PostgreSQL PostgreSQL starts manually just fine via /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile start What user are you starting the server as? Is this the same user you have specified in the StartupItem/launchd plist? If I execute the start command above just after a System start I get pg_ctl: another server may be running; trying to start server anyway server starting and then it starts and runs fine. I'm speculating that the StartupItem is trying to start the server but failing. I'm looking at getting that debugging to log where I can find it. Can someone help me config extended logging? log_destination, log_directory, log_filename I believe you should be able to set these at startup, though I haven't tried. /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -c redirect_stderr=YES -c log_min_message=DEBUG5 -c log_error_verbosity=VERBOSE Did you also change the user and group to what's appropriate for your setup? Michael Glaesemann grzm seespotcode net = Ralph here again. The command-line start is via U/G postgres/postgres. In /Library/LaunchDaemon/... I'm using the same names. I've removed all references to PG in /Library/StartupItems - - - - - - - - - - - - - - - - - - - - - - - - From tail of /var/log/system.log Jul 31 16:31:04 swampmac launchd: org.postgresql.postgres.v824: exited with exit code: 1 Jul 31 16:31:04 swampmac launchd: org.postgresql.postgres.v824: respawning too quickly! throttling Jul 31 16:31:04 swampmac launchd: org.postgresql.postgres.v824: 1 more failure without living at least 60 seconds will cause job removal Jul 31 16:31:04 swampmac launchd: org.postgresql.postgres.v824: will restart in 10 seconds Jul 31 16:31:14 swampmac launchd: org.postgresql.postgres.v824: exited with exit code: 1 Jul 31 16:31:14 swampmac launchd: org.postgresql.postgres.v824: respawning too quickly! throttling Jul 31 16:31:14 swampmac launchd: org.postgresql.postgres.v824: too many failures in succession swampmac:/pgsql_link postgres$ ll -R /Library/LaunchDaemons/ total 8 drwxr-xr-x3 root wheel 102 Jul 31 16:10 . drwxrwxr-t 47 root admin 1598 Jul 19 16:58 .. -rw-r--r--1 root wheel 3957 Jul 30 16:59 org.postgresql.postgres.v824.plist swampmac:/pgsql_link postgres$ cat /Library/LaunchDaemons/org.postgresql.postgres.v824.plist "http://www.apple.com/DTDs/PropertyList-1.0.dtd";> Label org.postgresql.postgres.v824 OnDemand Disabled ProgramArguments /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -c redirect_stderr=YES -c log_min_message=DEBUG5 -c log_error_verbosity=VERBOSE ServiceDescription PostgreSQL Server v8.2.4 UserName postgres GroupName postgres === === swampmac:/pgsql_link postgres$ more data/postgresql.conf # - # PostgreSQL configuration file # - # # COMMENTS REMOVED HERE #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory data_directory = '/usr/local/pgsql/data'# use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file hba_file = '/usr/local/pgsql/data/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '(none)' # write an extra PID file
[GENERAL] client encoding mismatch
Hello, I am getting the following error: client encoding mismatch State:22003,Native:214,Origin: Using the function "SQLDriverConnect" with the connection string "DSN=PostgreSQL;Servername=server;UID=user;Password=xx;Database=database ;" OS: Windows 2000 Postgresql Server 8.1.3 ODBC: 7.03.01.14 'PostgreSQL' driver (Want to retain, because I am also accessing a 7.x database) I have done the following: SET CLIENT_ENCODING TO 'SQL_ASCII'; but the encoding keeps coming back as "UTF8" or "WIN1251" Please help. Brian ---(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] Polymorphic functions' weird behavior
Tom Lane wrote: The fix is pretty easy if you need it now Thanks for the update, Tom. Another little issue with that function. I cannot make it accept NULL as an argument. It won't normally accept NULL reporting an appropriate error that it cannot determine argument type, nor will it accept it if I make the function STRICT, nor can I handle it inside the function since it isn't actually called. Thus I always get the error which is not desirable behavior. Any way around it? regards, Viatcheslav ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Auto Starting +/or Shutdown on OS X
On Jul 31, 2007, at 14:27 , Ralph Smith wrote: === INITIAL POST I'm using scripts in /Library/StartupItems/PostgreSQL PostgreSQL starts manually just fine via /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/ pgsql/logfile start What user are you starting the server as? Is this the same user you have specified in the StartupItem/launchd plist? If I execute the start command above just after a System start I get pg_ctl: another server may be running; trying to start server anyway server starting and then it starts and runs fine. I'm speculating that the StartupItem is trying to start the server but failing. I'm looking at getting that debugging to log where I can find it. Can someone help me config extended logging? log_destination, log_directory, log_filename I believe you should be able to set these at startup, though I haven't tried. /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -c redirect_stderr=YES -c log_min_message=DEBUG5 -c log_error_verbosity=VERBOSE Did you also change the user and group to what's appropriate for your setup? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plperl syntax highlighting for vi
> > http://www.gunduz.org/postgresql/pgsql.vim > > That's great for PL/PgSQL. Maybe there's a way to do it for PL/Perl, > too :) Can anyone point me to a link that shows which file vim conf file need to be configured and how to configure it to use this above .vim file? TIA, Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Auto Starting +/or Shutdown on OS X
=== INITIAL POST
I'm using scripts in /Library/StartupItems/PostgreSQL
PostgreSQL starts manually just fine via
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l
/usr/local/pgsql/logfile start
PostgreSQL will not start on System restart using files in
/Library/StartupItems/PostgreSQL (no longer below).
If I execute the start command above just after a System start I get
pg_ctl: another server may be running; trying to start server anyway
server starting
and then it starts and runs fine.
If I shut down PG using
pg_ctl stop (no users connected)
it stops and subsequently starts using code above just fine, w/o the
warning.
---
I added the command:
date > /Users/showmefile
to appropriate portions of /Library/StartupItems/PostgreSQL/PostgreSQL
as seen below, and the script portions ARE appropriately being executed.
How come shutdown isn't clearing things up?
Or is the problem in the startup?
Thanks!
=== FIRST REPLY ===
Michael G wrote:
On Jul 30, 2007, at 16:14 , Ralph Smith wrote:
I'm using scripts in /Library/StartupItems/PostgreSQL
I haven't used a StartupItem for PostgreSQL since launchd was released.
I haven't looked too closely at your configuration, but you might
consider using a launchd plist instead. Here's one of mine (from
/Library/LaunchDaemons/org.postgresql.postgres.v824.plist). Note that if
you use one of the plist launchd configuration editors out there you'll
probably lose the comments when you save.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
"http://www.apple.com/DTDs/PropertyList-1.0.dtd";>
Label
org.postgresql.postgres.v824
OnDemand
Disabled
ProgramArguments
/usr/local/pgsql/pgsql-8.2.4/bin/postmaster
-D
/usr/local/pgsql/pgsql-8.2.4/data
-c
redirect_stderr=YES
ServiceDescription
PostgreSQL Server v8.2.4
UserName
postgres
GroupName
postgres
=== COUNTER REPLY ===
Ralph Replies:
Thanks Michael.
But alas, auto-starting still doesn't work!
I renamed the file in the FORMERLY like-named DIR in
/Library/StartupItems/PostgreSQL to inactivate it.
Then I inserted your .plist text below into /Library/LaunchDaemons as
org.postgresql.postgres.v824.plist
Owner/Group root/wheel, 644 perms.
However... PostgreSQL still won't start at boot up!
In the system.log I get the errors you can see below.
Also there are my modifications to the .plist file -- to match my
install, and some debugging I added.
I'm looking at getting that debugging to log where I can find it.
Can someone help me config extended logging?
log_destination, log_directory, log_filename
Or is startup too soon for these to help me here?
Thanks,
Ralph
---
Jul 30 17:11:40 swampmac lookupd[90]: lookupd (version 369.5) starting -
Mon Jul 30 17:11:40 2007
Jul 30 17:11:40 swampmac configd[52]: posting notification
com.apple.system.config.network_change
Jul 30 17:11:40 swampmac lookupd[91]: lookupd (version 369.5) starting -
Mon Jul 30 17:11:40 2007
Jul 30 17:11:40 swampmac configd[52]: setting hostname to
"swampmac.eplt.washington.edu"
Jul 30 17:11:40 swampmac loginwindow[83]: Login Window Started Security
Agent
Jul 30 17:11:42 swampmac
/System/Library/CoreServices/mcxd.app/Contents/MacOS/mcxd: DSOpenNode():
dsOpenDirNode("/Active Directory/All Domains") == -14002
Jul 30 17:11:43 swampmac configd[52]: target=enable-network: disabled
Jul 30 17:11:43 swampmac launchd: org.postgresql.postgres.v824: exited
with exit code: 1
Jul 30 17:11:43 swampmac launchd: org.postgresql.postgres.v824:
respawning too quickly! throttling
Jul 30 17:11:43 swampmac launchd: org.postgresql.postgres.v824: 8 more
failures without living at least 60 seconds will cause job removal
Jul 30 17:11:43 swampmac launchd: org.postgresql.postgres.v824: will
restart in 10 seconds
Jul 30 17:11:44 swampmac VersionCueCS2Daemon[198]: warning:
VersionCueCS2Daemon not started by mach_init process (parent pid: 1)
Jul 30 17:11:45 swampmac configd[52]: AppleTalk startup complete
Jul 30 17:11:53 swampmac launchd: org.postgresql.postgres.v824: exited
with exit code: 1
Jul 30 17:11:53 swampmac launchd: org.postgresql.postgres.v824:
respawning too quickly! throttling
Jul 30 17:11:53 swampmac launchd: org.postgresql.postgres.v824: 7 more
failures without living at least 60 seconds will cause job removal
Jul 30 17:11:53 swampmac launchd: org.postgresql.postgres.v824: will
restart in 10 seconds
Modifications to the .plist file:
/usr/local/pgsql/bin/postmaster
-D
/usr/local/pgsql/data
-l
/usr/local/pgsql/logfile
-c
redirect_stderr=YES
-c
log_min_mes
Re: [GENERAL] FOREIGN KEY migration of syntax, help needed
Thank you for the quick response. If all my old constraints are NOT DEFERRABLE INITIALLY IMMEDIATE does that mean I don't have to worry about the deferrable keyword? mike On Tue, Jul 31, 2007 at 04:00:59PM -0400, Tom Lane wrote: > Mike Haberman <[EMAIL PROTECTED]> writes: > >Will the following take care of all three statements? > > >ALTER TABLE ONLY assettype > >ADD CONSTRAINT at_fk_1 FOREIGN KEY (pid) REFERENCES assettype(id) > >ON UPDATE CASCADE ON DELETE CASCADE; > > Yes, there are three or so triggers under the hood of any FOREIGN KEY > constraint. > > regards, tom lane -- --- Mike Haberman Senior Software/Network Research Engineer National Center for Supercomputing Applications 217.244.9370 --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] continue in plpgsql 7.4
"Gauthier, Dave" <[EMAIL PROTECTED]> writes: > Lacking "continue", what would you suggest? EXIT out of a labeled BEGIN block immediately within the loop. On the whole, though, if you're doing development for PG 7.4 my reaction is "why"? Your effort would be better invested in an update. 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
Re: [GENERAL] plperl syntax highlighting for vi
On Tue, Jul 31, 2007 at 01:19:06PM -0400, Bruce Momjian wrote: > David Fetter wrote: > > On Mon, Jul 30, 2007 at 07:58:14PM -0500, Decibel! wrote: > > > On Jul 30, 2007, at 3:44 PM, Geoffrey wrote: > > > >Has anyone taken a stab at adding plperl syntax highlighting for > > > >vi? > > > > > > Hrm, not likely. David Fetter might be able to point you at > > > something. > > > > > > If you come up with something please post it on pgFoundry so that > > > others can find it! :) > > > > I'm flattered, but I haven't seen anything like that. I suppose vim's > > extensibility might be able to help out. :) > > Here is a vim version: > > http://www.gunduz.org/postgresql/pgsql.vim That's great for PL/PgSQL. Maybe there's a way to do it for PL/Perl, too :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(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] continue in plpgsql 7.4
How does one effectively doa "continue" in plpgsql v7.4? I believe "continue" is supported in v8, but not 7. At any rate, "continue" gets flagged as a syntax error in the code I'm trying. Lacking "continue", what would you suggest? Thanks -dave
Re: [GENERAL] FOREIGN KEY migration of syntax, help needed
Mike Haberman <[EMAIL PROTECTED]> writes: >Will the following take care of all three statements? >ALTER TABLE ONLY assettype >ADD CONSTRAINT at_fk_1 FOREIGN KEY (pid) REFERENCES assettype(id) >ON UPDATE CASCADE ON DELETE CASCADE; Yes, there are three or so triggers under the hood of any FOREIGN KEY constraint. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need quick help with standalone mode
Thanks a lot! That was a life saver :-) Greetings Robert Merlin Moncure wrote: > On 7/31/07, RW <[EMAIL PROTECTED]> wrote: > >> Hi! >> >> I'm really in big trouble with a production database. It doesn't accept >> connections anymore: >> >> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be >> vacuumed within 999832 transactions >> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute >> a full-database VACUUM in "userbase". >> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be >> vacuumed within 999832 transactions >> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute >> a full-database VACUUM in "userbase"... >> >> So I tried this script (after shutting down the postmaster): >> >> #!/bin/bash >> >> /usr/local/pgsql81/bin/postgres -D /data/pgsql/data/lindau userbase << SQL >> VACUUM FULL VERBOSE ANALYZE >> SQL >> >> But all I get is: >> >> backend> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must >> be vacuumed within 999831 transactions >> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute >> a full-database VACUUM in "userbase". >> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be >> vacuumed within 999830 transactions >> ... >> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be >> vacuumed within 999809 transactions >> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute >> a full-database VACUUM in "userbase". >> 2007-07-31 19:27:33 CEST ERROR: could not access status of >> transaction 539227074 >> 2007-07-31 19:27:33 CEST DETAIL: could not open file "pg_clog/0202": >> No such file or directory >> >> I've provided the data path. So I don't understand the entry "could not >> open file...". The database version is 8.1.5 >> and it runs on Redhat Linux 4 AS x86_64. >> > > > First of all, update the postmater. This was a bug that was fixed in > the 8.1 series. > > There is a resolution to this problem. Here is Alvaro's notes on the subject: > > On 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > >> Merlin Moncure wrote: >> >> >>> ya, it doesn't seem to match, as this seems to be repeating quite >>> regularly. interesting that my 'clog' files start at 06B6 and count >>> up. 0207 is way off the charts. >>> >>> a lot of applications are hitting this database, and so far everything >>> seems to be running ok (i found this log msg by accident), but I am >>> now officially very nervous. >>> >> I don't think there's much cause for concern here. If my theory is >> correct, this is an autovacuum bug which was fixed in 8.1.7. >> >> What I'd do is create a 0207 clog file, fill it with 0x55 (which is >> "transactions committed" for all transactions in that interval), and do >> a VACUUM FREEZE on that database. You'll need to set >> pg_database.datallowconn=true beforehand. >> >> Of course, I'd copy the files somewhere else and experiment on a scratch >> postmaster, running on a different port, just to be sure ... >> > > > Good news is you haven't lost any data. update the binaries and > schedule a maintenance window if you have to. > > merlin > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] FOREIGN KEY migration of syntax, help needed
Hi,
Quick question:
My old database has the old-style FOREIGN KEY syntax:
CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON assettype
FROM assettype
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'assettype',
'assettype', 'UNSPECIFIED', 'pid', 'id');
CREATE CONSTRAINT TRIGGER ""
AFTER DELETE ON assettype
FROM assettype
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_cascade_del"('', 'assettype',
'assettype', 'UNSPECIFIED', 'pid', 'id');
CREATE CONSTRAINT TRIGGER ""
AFTER UPDATE ON assettype
FROM assettype
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_cascade_upd"('', 'assettype',
'assettype', 'UNSPECIFIED', 'pid', 'id');
I was wondering if I need to worry about the RI_FKey_check_ins statement.
Will the following take care of all three statements?
ALTER TABLE ONLY assettype
ADD CONSTRAINT at_fk_1 FOREIGN KEY (pid) REFERENCES assettype(id)
ON UPDATE CASCADE ON DELETE CASCADE;
Are there any other gottcha's when doing this type of migration?
(I need to use the FKEY syntax, so a schema visualizer will show the
foreign key relationships).
thanks a ton,
mike
---(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] Indexing Hostnames with tsearch2 and fti.c
We're currently using the old fti.c code for searching our database. It's indexing a mixed bag of hostnames, employee names, ticket subjects, and the like. We hacked it to make it work correctly for hostnames, ignoring whitespace and periods as word boundaries. Since were moving to 8.2, we noticed fti.c was no longer included in the distro. Our hacked version still compiles, bit it appears that everyone is switching to tsearch2, and now is a good time to update. Has anyone used tsearch2 for indexing/searching hostnames? What's involved? I believe it's possible with a custom dictionary, but don't have any experience with writing them. We currently index about 50 records. Will tsearch2 have any trouble sifting through that many records? Thanks, Rob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] upgrade to 8.2.? or implement Slony, which first?
On Jul 27, 2007, at 8:29 PM, Jim Nasby wrote: Double-check with the Slony guys, but ISTR that there's an issue going all the way from 7.4 to 8.2 in a single shot. I don't think that's a slony-specific issue. Moving from 7.4 to 8.0 introduces a fair number of incompatibilities one must address (mostly quoting and implied type casts as I recall from when we did the transition). ---(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] Need quick help with standalone mode
On 7/31/07, RW <[EMAIL PROTECTED]> wrote: > Hi! > > I'm really in big trouble with a production database. It doesn't accept > connections anymore: > > 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be > vacuumed within 999832 transactions > 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute > a full-database VACUUM in "userbase". > 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be > vacuumed within 999832 transactions > 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute > a full-database VACUUM in "userbase"... > > So I tried this script (after shutting down the postmaster): > > #!/bin/bash > > /usr/local/pgsql81/bin/postgres -D /data/pgsql/data/lindau userbase << SQL > VACUUM FULL VERBOSE ANALYZE > SQL > > But all I get is: > > backend> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must > be vacuumed within 999831 transactions > 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute > a full-database VACUUM in "userbase". > 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be > vacuumed within 999830 transactions > ... > 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be > vacuumed within 999809 transactions > 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute > a full-database VACUUM in "userbase". > 2007-07-31 19:27:33 CEST ERROR: could not access status of > transaction 539227074 > 2007-07-31 19:27:33 CEST DETAIL: could not open file "pg_clog/0202": > No such file or directory > > I've provided the data path. So I don't understand the entry "could not > open file...". The database version is 8.1.5 > and it runs on Redhat Linux 4 AS x86_64. First of all, update the postmater. This was a bug that was fixed in the 8.1 series. There is a resolution to this problem. Here is Alvaro's notes on the subject: On 2/6/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Merlin Moncure wrote: > > > ya, it doesn't seem to match, as this seems to be repeating quite > > regularly. interesting that my 'clog' files start at 06B6 and count > > up. 0207 is way off the charts. > > > > a lot of applications are hitting this database, and so far everything > > seems to be running ok (i found this log msg by accident), but I am > > now officially very nervous. > > I don't think there's much cause for concern here. If my theory is > correct, this is an autovacuum bug which was fixed in 8.1.7. > > What I'd do is create a 0207 clog file, fill it with 0x55 (which is > "transactions committed" for all transactions in that interval), and do > a VACUUM FREEZE on that database. You'll need to set > pg_database.datallowconn=true beforehand. > > Of course, I'd copy the files somewhere else and experiment on a scratch > postmaster, running on a different port, just to be sure ... Good news is you haven't lost any data. update the binaries and schedule a maintenance window if you have to. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] huge file in pg_xlog and base
Nicola Benaglia wrote: > Hi, > I have 6 little dbs, but I see that my base directory needs 213MB and > log are 114MB. > Here my folder structure and size: > > 3571./base/1 > 3487./base/10792 > 4691./base/10793 > 3707./base/16384 > 16618 ./base/16386 > 0 ./base/64673/pgsql_tmp > 143697 ./base/64673 > 0 ./base/86171/pgsql_tmp > 6133./base/86171 > 5790./base/89667 > 6401./base/106022 > 7926./base/106521 > 11597 ./base/120343 > 213615 ./base > 160 ./pg_subtrans > 0 ./pg_twophase > 8 ./pg_multixact/members > 8 ./pg_multixact/offsets > 16 ./pg_multixact > 865 ./global > 16 ./pg_clog > 0 ./pg_xlog/archive_status > 114800 ./pg_xlog > 0 ./pg_tblspc > 329484 . > > > Could you please suggest me how to reduce that space (if possible)? Have you been vacuuming? Check your vacuum/autovacuum settings and procedures. Try "vacuum full" and see if that helps. Reindexing can reduce the index disk-size as well. Assuming a typical 8k page size, you can run "select relpages*8 as kbytes, relname, relkind from pg_class order by 1 desc" to see what tables are to blame for the large disk-size. Try vacuum full (relkind='r') or reindex (relkind='i') on the large ones and see what happens. Queries that update all rows will cause rapid bloat since every updated row will be duplicated. One mistake I've seen is apps that have some sort of flag, perhaps a "processed" flag. Some process looks at the table then does an "update foo set processed = true" on a table where only a tiny fraction is not processed. Adding "where not processed" can reduce the bloat associated with this type of update. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Need quick help with standalone mode
Hi! I'm really in big trouble with a production database. It doesn't accept connections anymore: 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be vacuumed within 999832 transactions 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute a full-database VACUUM in "userbase". 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be vacuumed within 999832 transactions 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute a full-database VACUUM in "userbase"... So I tried this script (after shutting down the postmaster): #!/bin/bash /usr/local/pgsql81/bin/postgres -D /data/pgsql/data/lindau userbase << SQL VACUUM FULL VERBOSE ANALYZE SQL But all I get is: backend> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be vacuumed within 999831 transactions 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute a full-database VACUUM in "userbase". 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be vacuumed within 999830 transactions ... 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be vacuumed within 999809 transactions 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute a full-database VACUUM in "userbase". 2007-07-31 19:27:33 CEST ERROR: could not access status of transaction 539227074 2007-07-31 19:27:33 CEST DETAIL: could not open file "pg_clog/0202": No such file or directory I've provided the data path. So I don't understand the entry "could not open file...". The database version is 8.1.5 and it runs on Redhat Linux 4 AS x86_64. Any hints? Thanks! Robert ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error restarting postmaster
On Tue, 2007-07-31 at 06:53 -0700, Andrew Edson wrote: > Yesterday, one of the (replicated) remote databases I work with > somehow got corrupted, so I attempted to drop a new copy off of the > master (on a different box) and rebuild the database. Creation, > language install, schema reload, all appeared to go well. On the > actual data reload, I set the system aside and went on to something > else, as the db takes a while to load. I came back to discover that > the connection between my system and the one where the db was being > rebuilt had been severed. Opening a new remote connection, I went in > and attempted to rebuild the database, only to discover that neither > postgres nor the postmaster was running. And when I attempted to > restart the postmaster process, I received the following error: > > [EMAIL PROTECTED]:/usr/local/pgsql/bin> ./postmaster > -D /usr/local/pgsql/data/ > LOG: database system was interrupted while in recovery at 2007-07-31 > 08:17:22 CDT > HINT: This probably means that some data is corrupted and you will > have to use the last backup for recovery. > LOG: checkpoint record is at 3C/D7008078 > LOG: redo record is at 3C/D7008078; undo record is at 0/0; shutdown > FALSE > LOG: next transaction ID: 59170527; next OID: 532878 > LOG: next MultiXactId: 1; next MultiXactOffset: 0 > LOG: database system was not properly shut down; automatic recovery > in progress > LOG: redo starts at 3C/D70080BC > PANIC: block 39 unfound > WARNING: autovacuum not started because of misconfiguration > HINT: Enable options "stats_start_collector" and "stats_row_level". > LOG: startup process (PID 6403) was terminated by signal 6 > LOG: aborting startup due to startup process failure > > A google search on the Panic clause lead me to an old thread in the > [Hackers] list, which looks like it was a similar problem, but I can't > figure out from that thread how the problem was solved. Would someone > please help me figure out what I need to do to correct this and get my > database running again? You're running 8.1 with GIST indexes and you will prefer the way they work in 8.2. The changes were bug fixes but possibly considered extensive enough to not have been backpatched. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plperl syntax highlighting for vi
David Fetter wrote: > On Mon, Jul 30, 2007 at 07:58:14PM -0500, Decibel! wrote: > > On Jul 30, 2007, at 3:44 PM, Geoffrey wrote: > > >Has anyone taken a stab at adding plperl syntax highlighting for > > >vi? > > > > Hrm, not likely. David Fetter might be able to point you at > > something. > > > > If you come up with something please post it on pgFoundry so that > > others can find it! :) > > I'm flattered, but I haven't seen anything like that. I suppose vim's > extensibility might be able to help out. :) Here is a vim version: http://www.gunduz.org/postgresql/pgsql.vim -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] PG Admin
On 7/31/07, Bob Pawley <[EMAIL PROTECTED]> wrote: > > > Can anyone tell me why a table developed through the PG Admin interface > isn't found by SQL when accessing it through the SQL interface?? > > Bob Pawley likely issues: in the wrong schema in the wrong database in the wrong host :-) merlin ---(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] PG Admin
On Tuesday 31 July 2007 Bob Pawley's cat, walking on the keyboard, wrote: > Can anyone tell me why a table developed through the PG Admin interface > isn't found by SQL when accessing it through the SQL interface?? Maybe it is a problem of case-sensitive names? Check in the table definition of pgadmin is the table name is quoted (like in "myTable"). Nothing comes into my head now. Luca ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG Admin
On 31/07/2007 16:55, Bob Pawley wrote: Can anyone tell me why a table developed through the PG Admin interface isn't found by SQL when accessing it through the SQL interface?? Hi Bob, No reason that I can think ofcan you describe *IN DETAIL* the steps you followed and the result? Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] psql 8.2 client vs pg 8.1 server problem
Alban Hertroys <[EMAIL PROTECTED]> writes: > But wouldn't it suffice to issue the "old" versions of the command > queries on an "old" server? It shouldn't be that hard to keep backward > compatibility among minor versions of psql. It's enough of a pain in the neck that no one has bothered ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PG Admin
Can anyone tell me why a table developed through the PG Admin interface isn't found by SQL when accessing it through the SQL interface?? Bob Pawley
Re: [GENERAL] Polymorphic functions' weird behavior
Viatcheslav Kalinin <[EMAIL PROTECTED]> writes: > Now we change SECURITY INVOKER clause to SECURITY DEFINER and voila: >>> select * from array_to_set(array[1,2,3]); > ERROR: could not determine actual argument type for polymorphic > function "array_to_set" Wow, apparently you're the first person ever to try that, because it's never worked in any release since polymorphism was introduced :-(. Thanks for the report! The fix is pretty easy if you need it now: Index: fmgr.c === RCS file: /cvsroot/pgsql/src/backend/utils/fmgr/fmgr.c,v retrieving revision 1.102 diff -c -r1.102 fmgr.c *** fmgr.c 4 Oct 2006 00:30:01 - 1.102 --- fmgr.c 31 Jul 2007 15:39:29 - *** *** 793,798 --- 793,799 fmgr_info_cxt_security(fcinfo->flinfo->fn_oid, &fcache->flinfo, fcinfo->flinfo->fn_mcxt, true); + fcache->flinfo.fn_expr = fcinfo->flinfo->fn_expr; tuple = SearchSysCache(PROCOID, ObjectIdGetDatum(fcinfo->flinfo->fn_oid), 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
Re: [GENERAL] psql 8.2 client vs pg 8.1 server problem
Dave Page wrote: > Alban Hertroys wrote: >> We have psql 8.2 clients on our workstations, while we still have pg 8.1 >> on our development and production servers. This causes problems like the >> following: >> >> database> \d table >> ERROR: column i.indisvalid does not exist >> database> >> >> We can log into the server and use the local client of course, but this >> problem is causing some confusion among our new employees and doesn't >> exactly inspire them with the reliability we know PostgreSQL has... >> >> Is there a better workaround, or may this be a bug even? > > Install the 8.1 version of psql on your workstations (and symlink it to > psql81 or something if necessary). psql doesn't make any promises about > backward compatibility - for that, you'll need to use something like > pgAdmin (even that may need to be pointed at the older versions of > pg_dump etc if you're going to run backups from the workstations). But wouldn't it suffice to issue the "old" versions of the command queries on an "old" server? It shouldn't be that hard to keep backward compatibility among minor versions of psql. Even the server version is known... They're just queries, right? How hard can it be to keep various versions around so as to match the server version? I can understand that new psql client features wouldn't be backward compatible, but normal commands like \d should keep working. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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] Error restarting postmaster
Is somewhat old, 8.1.3. I'll try to upgrade it to the 8.1.9. The box is running on SuSE 9.2, if I recall correctly...which binary rpm should I snag for that? Tom Lane <[EMAIL PROTECTED]> wrote: Andrew Edson writes: > PANIC: block 39 unfound > LOG: startup process (PID 6403) was terminated by signal 6 > LOG: aborting startup due to startup process failure What PG version is this? (If your answer had a release date more than about a year ago, first update to the latest release in that branch and see if that fixes it.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
Re: [GENERAL] Core reported from vaccum function.
Alvaro Herrera wrote: > Mavinakuli, Prasanna (STSD) wrote: > > > > Thanks Alvaro,for your deliberate explanation.But few more > > clarifications are requested as we are too novice to postgreSQL. > > Let me give you this piece of advice, since you are too novice to > PostgreSQL: > > Do NOT continue to run 7.4.2. Upgrade to 7.4.17. Not doing so is just > a waste of your time and ours. Agreed. Not upgrading is more risky than upgrading. This is in the FAQ. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error restarting postmaster
Andrew Edson <[EMAIL PROTECTED]> writes: > PANIC: block 39 unfound > LOG: startup process (PID 6403) was terminated by signal 6 > LOG: aborting startup due to startup process failure What PG version is this? (If your answer had a release date more than about a year ago, first update to the latest release in that branch and see if that fixes it.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Porting MySQL data types to PostgreSQL
[EMAIL PROTECTED] (Patrick TJ McPhee) writes: > One problem with this idea is the treatment of implicit casts between > numeric types in TypeCategory(). For implicit casts to work, the type's > OID has to be listed in that function (i.e., it has to be a built-in type). That's not the case. There probably are some things that won't work nicely if TypeCategory() doesn't recognize the type as numeric category, but to claim that implicit casts won't work at all is wrong. 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] huge file in pg_xlog and base
Hi, I have 6 little dbs, but I see that my base directory needs 213MB and log are 114MB. Here my folder structure and size: 3571./base/1 3487./base/10792 4691./base/10793 3707./base/16384 16618 ./base/16386 0 ./base/64673/pgsql_tmp 143697 ./base/64673 0 ./base/86171/pgsql_tmp 6133./base/86171 5790./base/89667 6401./base/106022 7926./base/106521 11597 ./base/120343 213615 ./base 160 ./pg_subtrans 0 ./pg_twophase 8 ./pg_multixact/members 8 ./pg_multixact/offsets 16 ./pg_multixact 865 ./global 16 ./pg_clog 0 ./pg_xlog/archive_status 114800 ./pg_xlog 0 ./pg_tblspc 329484 . Could you please suggest me how to reduce that space (if possible)? Thank you, Nico ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance problem with large resultsets (byte array 2200)
"Victor Adolfsson" <[EMAIL PROTECTED]> writes: > I'm having a problem with bad performance when retrieving many rows where > each row has a 2200 long byte array (called template_compressed) and a 50 > character varying (called uniqueid) 2200 bytes is (just) long enough to trigger toasting of the entries, and I suppose it's the extra time to fetch a toasted value that's hurting. As long as the total row width is under 8K, you could disable toasting by ALTER TABLE ... SET STORAGE PLAIN on all the variable-width columns of the table. Note that this wouldn't in itself de-toast rows already stored; so you'd have to dump/truncate/reload the table before seeing any performance benefit. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tables not in list
> -Original Message- > From: Viatcheslav Kalinin [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 31, 2007 9:16 AM > To: Lee Keel > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] tables not in list > > I usually resolve this as: > -- this function lets you select from an array > CREATE OR REPLACE FUNCTION "array_to_set" (vaarray anyarray) RETURNS > SETOF anyelement AS > $body$ > BEGIN >FOR I IN COALESCE(ARRAY_LOWER(VAARRAY, 1), 1) .. > COALESCE(ARRAY_UPPER(VAARRAY, 1), 0) LOOP >RETURN NEXT VAARRAY[I]; >END LOOP; > END > $body$ > LANGUAGE 'plpgsql'; > > select table_name > from array_to_set(array['test', 'bar', 'foo']) as table_name > where table_name not in (select table_name from > information_schema.tables where table_catalog='postgres' and > table_type='BASE TABLE' and table_schema='public') > > regards THANKS! That was exactly what I needed! This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. ---(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] tables not in list
Lee Keel wrote: Is there no way to do this without doing an insert into another table? I usually resolve this as: -- this function lets you select from an array CREATE OR REPLACE FUNCTION "array_to_set" (vaarray anyarray) RETURNS SETOF anyelement AS $body$ BEGIN FOR I IN COALESCE(ARRAY_LOWER(VAARRAY, 1), 1) .. COALESCE(ARRAY_UPPER(VAARRAY, 1), 0) LOOP RETURN NEXT VAARRAY[I]; END LOOP; END $body$ LANGUAGE 'plpgsql'; select table_name from array_to_set(array['test', 'bar', 'foo']) as table_name where table_name not in (select table_name from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public') regards ---(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] tables not in list
Is there no way to do this without doing an insert into another table? _ From: paddy carroll [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 3:24 PM To: Lee Keel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] tables not in list put all your tables in a new table (public.tables) select table_name from public.tables where public.tables.table_name not in (select table_name from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public') On 30 Jul 2007, at 20:31, Lee Keel wrote: Hi List, I have a list of table names and I am trying to confirm that they are all in my postgres db. But what I want returned is a list/array of ones that are in my list but not in the db. So for example: CREATE TABLE test ( somecol integer ) WITHOUT OIDS; CREATE TABLE bar ( barcol integer ) WITHOUT OIDS; Now if I were to have a list of table names that included 'test', 'bar', and 'foo', then how do I get 'foo' to return. Here is what I have, but it returns the ones that are in the list and I want the opposite of that. select array( select table_name::text from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public' and table_name = any (array['test', 'bar', 'foo'])) Thanks in advance for any help on this. Lee This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
[GENERAL] Error restarting postmaster
Yesterday, one of the (replicated) remote databases I work with somehow got corrupted, so I attempted to drop a new copy off of the master (on a different box) and rebuild the database. Creation, language install, schema reload, all appeared to go well. On the actual data reload, I set the system aside and went on to something else, as the db takes a while to load. I came back to discover that the connection between my system and the one where the db was being rebuilt had been severed. Opening a new remote connection, I went in and attempted to rebuild the database, only to discover that neither postgres nor the postmaster was running. And when I attempted to restart the postmaster process, I received the following error: [EMAIL PROTECTED]:/usr/local/pgsql/bin> ./postmaster -D /usr/local/pgsql/data/ LOG: database system was interrupted while in recovery at 2007-07-31 08:17:22 CDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. LOG: checkpoint record is at 3C/D7008078 LOG: redo record is at 3C/D7008078; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 59170527; next OID: 532878 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 3C/D70080BC PANIC: block 39 unfound WARNING: autovacuum not started because of misconfiguration HINT: Enable options "stats_start_collector" and "stats_row_level". LOG: startup process (PID 6403) was terminated by signal 6 LOG: aborting startup due to startup process failure A google search on the Panic clause lead me to an old thread in the [Hackers] list, which looks like it was a similar problem, but I can't figure out from that thread how the problem was solved. Would someone please help me figure out what I need to do to correct this and get my database running again? - Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.
Re: [GENERAL] Core reported from vaccum function.
Mavinakuli, Prasanna (STSD) wrote: > > Thanks Alvaro,for your deliberate explanation.But few more > clarifications are requested as we are too novice to postgreSQL. Let me give you this piece of advice, since you are too novice to PostgreSQL: Do NOT continue to run 7.4.2. Upgrade to 7.4.17. Not doing so is just a waste of your time and ours. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] psql 8.2 client vs pg 8.1 server problem
Alban Hertroys wrote: We have psql 8.2 clients on our workstations, while we still have pg 8.1 on our development and production servers. This causes problems like the following: database> \d table ERROR: column i.indisvalid does not exist database> We can log into the server and use the local client of course, but this problem is causing some confusion among our new employees and doesn't exactly inspire them with the reliability we know PostgreSQL has... Is there a better workaround, or may this be a bug even? Install the 8.1 version of psql on your workstations (and symlink it to psql81 or something if necessary). psql doesn't make any promises about backward compatibility - for that, you'll need to use something like pgAdmin (even that may need to be pointed at the older versions of pg_dump etc if you're going to run backups from the workstations). Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] psql 8.2 client vs pg 8.1 server problem
We have psql 8.2 clients on our workstations, while we still have pg 8.1 on our development and production servers. This causes problems like the following: database> \d table ERROR: column i.indisvalid does not exist database> We can log into the server and use the local client of course, but this problem is causing some confusion among our new employees and doesn't exactly inspire them with the reliability we know PostgreSQL has... Is there a better workaround, or may this be a bug even? Versions: psql --version psql (PostgreSQL) 8.2.4 contains support for command-line editing database> select version(); version - PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) (1 row) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] query to match '\N'
On Jul 30, 3:27 am, [EMAIL PROTECTED] (Alban Hertroys) wrote: > Nis Jørgensen wrote: > > Alban Hertroys skrev: > > >> Presumably he wanted col2 like E'%N%'. > >> But doesn't \N mean NULL, or would the OP be looking for literal '\N' > >> strings in his data? Because if he's looking for NULLs it may be better > >> to query for col2 IS NULL. > > > My guess is that this string was used to signify NULL in the file > > originally imported into the db. > > Which is basically what I was pointing out ;) > It's all speculation anyway, we're guessing at what problem the OP tries > to solve. > > I think either he is looking for NULL column values that exist in his > input file as '\N' strings (strings cannot contain NULLs, so using > "like" is pointless), or he is looking for failed conversions of \N from > his input file that thus may have ended up as literal \N characters in > column data. > > In the latter case there shouldn't be any columns that match "like > '%N%'" but not "= 'N'". OTOH, we may be talking about an import > failure, in which case anything is possible. Fixing that would probably > be more difficult than fixing the cause of the failure and re-doing the > import. > > As I said, it's all speculation. Without input from the OP there's not > much point in continuing this discussion. > > Regards, > -- > Alban Hertroys > [EMAIL PROTECTED] > > magproductions b.v. > > T: ++31(0)534346874 > F: ++31(0)534346876 > M: > I:www.magproductions.nl > A: Postbus 416 >7500 AK Enschede > > // Integrate Your World // > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ oh yes! You all are exactly right.I exported a table and all nulls became \N and when i imported it \n remained \N and did not convert to NULL.So I updates all \N s with '' now. Thank you all for your input.I got to learn a lot from you. pc PS: Please refer to me as she :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Porting MySQL data types to PostgreSQL
In article <[EMAIL PROTECTED]>, Jim Nasby <[EMAIL PROTECTED]> wrote: % On Jul 26, 2007, at 11:06 AM, Jeff Davis wrote: % > If you really do need an unsigned type, this is a good use of % > postgresql's extensible type system. You can just create an unsigned % > type for yourself. % % If you do that please start a project on pgfoundry so others can % contribute and benefit. In fact, if you do start one let me know and % I'll try and help out. One problem with this idea is the treatment of implicit casts between numeric types in TypeCategory(). For implicit casts to work, the type's OID has to be listed in that function (i.e., it has to be a built-in type). -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(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] query to match '\N'
wow! works for me! Thank you !! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Core reported from vaccum function.
Thanks Alvaro,for your deliberate explanation.But few more clarifications are requested as we are too novice to postgreSQL. 1)When it is said "upgrade" it is NOT the upgrade of table rather it is the upgrade that does happen because of vacuum query execution?..is that understanding right?.(Because we got the problem during normal query execution and not in postgreSQL upgrade) 2)Again what we could gather is,there is *a* chance of data corruption during vaccum query which might lead to core problem as well. The back trace what we have is , gdb) bt #0 0x449c210:0 in HeapTupleSatisfiesNow+0xb0 () #1 0x40ec3f0:0 in heap_fetch+0x6f0 () #2 0x41c1940:0 in analyze_rel+0x1540 () #3 0x42351d0:0 in vacuum+0x370 () #4 0x436adb0:0 in ProcessUtility+0xb00 () #5 0x4367b50:0 in PortalRunUtility+0x1c0 () #6 0x4368600:0 in PortalRun+0x950 () #7 0x435eab0:0 in exec_simple_query+0x530 () #8 0x4364550:0 in PostgresMain+0x45a0 () #9 0x4301c50:0 in ServerLoop+0x15e0 () #10 0x4306050:0 in PostmasterMain+0x2050 () #11 0x42858c0:0 in main+0x470 () Is there any point to think that it is the result of that corruption?.As we can observe,the core happened during the execution of HeapTupleSatisfiesNow which had a fix in later version for the said problem.does it vindicate core happened only because of the corruption which was there in earlier version of postgres.(Unfortunately we are still using older version.7.4.2 which didn't have the fix for the same.) Again Thanks a lot, Thanks , Prasanna. -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 10:00 PM To: Mavinakuli, Prasanna (STSD) Cc: pgsql-general@postgresql.org; Tom Lane; Rao, Srikanth R (STSD); Racharla, Chakravarthy (STSD); Manchenahalli, Raghunandan (STSD); Hebbar, Raghavendra (STSD) Subject: Re: [GENERAL] Core reported from vaccum function. Mavinakuli, Prasanna (STSD) wrote: > > Hello Alvaro, > > Thanks for your reply. > > We could see > "Fix potential-data-corruption bug in how VACUUM FULL handles UPDATE > chains (Tom, Pavan Deolasee) " in 7.4.17 release notes. > > Could you please elaborate more on the above problem.Meaning what was > the actual problem and what fix has been done etc? Here is the CVS log entry: 2007-03-14 14:48 tgl * src/backend/commands/vacuum.c (1.263.2.3): Fix a longstanding bug in VACUUM FULL's handling of update chains. The code did not expect that a DEAD tuple could follow a RECENTLY_DEAD tuple in an update chain, but because the OldestXmin rule for determining deadness is a simplification of reality, it is possible for this situation to occur (implying that the RECENTLY_DEAD tuple is in fact dead to all observers, but this patch does not attempt to exploit that). The code would follow a chain forward all the way, but then stop before a DEAD tuple when backing up, meaning that not all of the chain got moved. This could lead to copying the chain multiple times (resulting in duplicate copies of the live tuple at its end), or leaving dangling index entries behind (which, aside from generating warnings from later vacuums, creates a risk of wrong query results or bogus duplicate-key errors once the heap slot the index entry points to is repopulated). The fix is to recheck HeapTupleSatisfiesVacuum while following a chain forward, and to stop if a DEAD tuple is reached. Each contiguous group of RECENTLY_DEAD tuples will therefore be copied as a separate chain. The patch also adds a couple of extra sanity checks to verify correct behavior. Per report and test case from Pavan Deolasee. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] create function error
Sorry, received information I wasn't aware of. My understanding was this was all running on the db server, but the script was actually being executed through another server (web) that only had a 7.4 client. This appears to have been the problem. Thanks. Tony Crisera Michael Glaesemann wrote: On Jul 26, 2007, at 13:22 , Tony Crisera wrote: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN NEW.mod_date := now();" at character 63 ERROR: syntax error at or near "RETURN" at character 9 WARNING: there is no transaction in progress ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 1 Note that this is *not* the script you provided below, as $$ does not appear in the script you provided. Please provide the full output of the psql session that shows the error (i.e., statements and error output). ERROR: function public.setproposalmoddate() does not exist This is irrelevant. It's just telling you the trigger can't be created because the function doesn't exist. Here is a script that produces the error- CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS $mod_date$ BEGIN NEW.mod_date := now(); RETURN NEW; END; $mod_date$ LANGUAGE plpgsql; Works fine for me: test=# CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS $mod_date$ test$#BEGIN test$#NEW.mod_date := now(); test$#RETURN NEW; test$# END; test$# $mod_date$ LANGUAGE plpgsql; CREATE FUNCTION test=# select version(); version -- PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367) (1 row) Michael Glaesemann grzm seespotcode net ---(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] Core reported from vaccum function.
Hello Alvaro, Thanks for your reply. We could see "Fix potential-data-corruption bug in how VACUUM FULL handles UPDATE chains (Tom, Pavan Deolasee) " in 7.4.17 release notes. Could you please elaborate more on the above problem.Meaning what was the actual problem and what fix has been done etc? Thanks a lot for all your help/time. Thanks, Prasanna. -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 19, 2007 8:44 AM To: Mavinakuli, Prasanna (STSD) Cc: Tom Lane; pgsql-general@postgresql.org; Rao, Srikanth R (STSD) Subject: Re: [GENERAL] Core reported from vaccum function. Mavinakuli, Prasanna (STSD) wrote: > Hello All, > > We are getting the following core more oftenly.But We don't have a > test case where it is guaranteed to dump this core.We are using 7.4.2 > version postgres and if any one of you aware about some bug fixes > happened around this problem.Please let us know. There are about 15 rounds of bug fixes released after that version. Get 7.4.17 and try again. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Polymorphic functions' weird behavior
Hello We've come across the following issue with Polymorphic functions: CREATE OR REPLACE FUNCTION "array_to_set" (vaarray anyarray) RETURNS SETOF anyelement AS $body$ BEGIN FOR I IN COALESCE(ARRAY_LOWER(VAARRAY, 1), 1) .. COALESCE(ARRAY_UPPER(VAARRAY, 1), 0) LOOP RETURN NEXT VAARRAY[I]; END LOOP; END $body$ LANGUAGE 'plpgsql' SECURITY INVOKER; > select * from array_to_set(array[1,2,3]); array_to_set -- 1 2 3 (3 rows) Now we change SECURITY INVOKER clause to SECURITY DEFINER and voila: > select * from array_to_set(array[1,2,3]); ERROR: could not determine actual argument type for polymorphic function "array_to_set" Though explainable this is absolutely strange since logically security rules and polymorphism are irrelevant. regards, Viatcheslav ---(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] Performance problem with large resultsets (byte array 2200)
Hi
I'm having a problem with bad performance when retrieving many rows where
each row has a 2200 long byte array (called template_compressed) and a 50
character varying (called uniqueid)
Is there a better datatype than bytea when it is important to fetch the
information from the database?
Would it be better to store a reference in the database and then store the
2200 big template in a separate file?
What is the suggested size threshold for when it is better to store in file
than in database?
What parts need to be configured and how should they be configured given the
environment described at the bottom of this email?
(short version: DualCore P4 2.8 GHz, 1 GB RAM, 2 x 80 GB SATA RAID1)
What is the expected performance when an application on the same server is
querying the database and doing data retrieval with this large recordset?
Are the use of cursors recommended when dealing with large recordsets?
(currently cursors are not used)? Would the performance differ and if so, in
what direction?
How can i decrease the difference in duration between the explain analyze
time and the duration when actually retrieving the data from postgresql to
the application?
When I do an explain analyze on the same query I get Total runtime:
3306.216 ms. (explain analyze output is available below)
This is the output from the postgresql-log file after enabling logging of
slow queries (1second).
LOG: duration: 202927.174 ms statement: SELECT p.uniqueid AS uid,
f.template_compressed AS ctemplate FROM person p INNER JOIN features f ON
p.id=f.person_id WHERE p.website_id = '11' AND p.uniqueid != 'extrababak'
AND birthdate < (NOW() - '13 YEAR'::interval) AND birthdate > (NOW() - '53
YEAR'::interval) AND f.datecreated > '1980-1-1'::date ORDER BY f.datecreatedDESC
I have created an index on all columns which may be part of the where
clause.
explain analyze SELECT p.uniqueid AS uid, f.template_compressed AS ctemplate
FROM person p INNER JOIN features f ON p.id=f.person_id WHERE p.website_id =
'11' AND p.uniqueid != 'extrababak' AND birthdate < (NOW() - '13
YEAR'::interval) AND birthdate > (NOW() - '53 YEAR'::interval) AND
f.datecreated > '1980-1-1'::date ORDER BY f.datecreated DESC;
QUERY PLAN
Nested Loop (cost=0.00..13420.71 rows=63686 width=55) (actual time=
36.335..3265.844 rows=63902 loops=1)
-> Index Scan Backward using idx_features_datecreated on features f
(cost=0.00..3380.64 rows=79249 width=44) (actual
time=1.090..1488.601rows=79264 loops=1)
Index Cond: (datecreated > '1980-01-01'::date)
-> Index Scan using person_pkey on person p (cost=0.00..0.11 rows=1
width=19) (actual time=0.019..0.020 rows=1 loops=79264)
Index Cond: ( p.id = f.person_id)
Filter: ((website_id = 11) AND ((uniqueid)::text <>
'extrababak'::text) AND (birthdate < (now() - '13 years'::interval)) AND
(birthdate > (now() - '53 years'::interval)))
Total runtime: 3306.216 ms
(7 rows)
explain analyze yet another time
QUERY PLAN
Nested Loop (cost=0.00..13420.71 rows=63686 width=55) (actual time=
0.355..1123.840 rows=63902 loops=1)
-> Index Scan Backward using idx_features_datecreated on features f
(cost=0.00..3380.64 rows=79249 width=44) (actual time=
0.072..97.846rows=79264 loops=1)
Index Cond: (datecreated > '1980-01-01'::date)
-> Index Scan using person_pkey on person p (cost=0.00..0.11 rows=1
width=19) (actual time=0.009..0.010 rows=1 loops=79264)
Index Cond: (p.id = f.person_id)
Filter: ((website_id = 11) AND ((uniqueid)::text <>
'extrababak'::text) AND (birthdate < (now() - '13 years'::interval)) AND
(birthdate > (now() - '53 years'::interval)))
Total runtime: 1163.758 ms
(7 rows)
\d person;
Table "public.person"
Column| Type |
Modifiers
--+--+-
id | integer | not null default
nextval('person_id_seq'::regclass)
uniqueid | character varying(50)|
website_id | integer |
datecreated | timestamp with time zone | default now()
gender | character varying(1) | default 'U'::character varying
birthdate| date |
category_id | integer |
city | character varying(100) |
zipcode | character varying(20)|
state| character varying(100) |
country_iso2 | character varying(2) |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"idx_person_birthdate" btree (birthdate)
"idx_person_category_id" btree (category_id)
"idx
Re: [GENERAL] alter table table add column
Ronald Rojas skrev: > Hi, > > Anybody knows how to add column with reference to BEFORE or AFTER any > given column? Let say here's my table structure: > > Column | Type| Modifiers > --+---+--- > surname | character varying | > lastname | character varying | > address | character varying | > > And, I want to add the field name age with type integer after lastname > OR before the address field. How to I do that? > > I would really appreciate your response. Not tested. ALTER TABLE foo ADD COLUMN age integer ALTER TABLE foo ADD COLUMN address2 character varying; UPDATE TABLE foo SET address2=address; ALTER TABLE foo DROP COLUMN address; ALTER TABLE foo RENAME COLUMN address2 TO address; Nis ---(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] [NOVICE] alter table table add column
On 31/07/07, Ronald Rojas <[EMAIL PROTECTED]> wrote: > > Oh yes you have a good point. But then I will still have to test insert > and update on views. > > Thanks a lot michael! > > On Tue, 2007-07-31 at 00:56 -0500, Michael Glaesemann wrote: > > On Jul 31, 2007, at 0:23 , Ronald Rojas wrote: > > Yes I know that procedure but I would like to insert in between > because > > I have at third party software that will call the said > schema and one of > > its dependencies with the mapping is it should > have the correct order > > with what the receiving end will be use for > the mapping. And in this > > case, I can't modify the receiving > structure (third-party) and the > > tables that I will be using is in > on production state. So would only > > mean that I have to schedule a > very fast maintenance, probably 5 to 10 > > secs just to re-initialize > the tables. > Another option would be to use views to change the column order, which would > work for selects. I believe you could create rules for insert and update as > well, if necessary. Perhaps this would be a solution to your problem. > Michael Glaesemanngrzm seespotcode net > > But really you should not be using select * from ... anyway, Always list your column names, That way you will aways get the columns in the order you want rather than in the order they are stored. This really belongs in a FAQ Peter.
Re: [GENERAL] Manual Vaccum very slow with Autovaccum enabled
"Andy Dale" <[EMAIL PROTECTED]> writes: > Hi, > > I am working with a 3 Postgresql databases, 1 is configured with autovaccum > enabled with the following settings: > > vacuum_cost_delay = 200 # 0-1000 milliseconds > vacuum_cost_limit = 100 # 0-1 credits ... > on the 2 machines with autovaccum not enabled this analyze takes only a few > minutes at most, but on the machine with it enabled it takes roughly 40 - 50 > mins (2882336 ms.), i do not know why ?? does the autovaccum slow down a > manually invoked vaccum ? These say to wait 200ms every few pages. That makes sense if you want to run vacuum without having it slow down the production system but, yes, it will make it take a lot longer than if you don't have these set so high. You can set these locally in your session before running vacuum if you want to run vacuum manually faster than the normal autovacuum times. http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Manual Vaccum very slow with Autovaccum enabled
Hi, I am working with a 3 Postgresql databases, 1 is configured with autovaccum enabled with the following settings: vacuum_cost_delay = 200 # 0-1000 milliseconds vacuum_cost_page_hit = 6# 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20 # 0-1 credits vacuum_cost_limit = 100 # 0-1 credits === autovacuum = on # enable autovacuum subprocess? autovacuum_naptime = 30# time between autovacuum runs, in secs autovacuum_vacuum_threshold = 400 # min # of tuple updates before vacuum autovacuum_analyze_threshold = 100 # min # of tuple updates before analyze autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit and the other 2 without it enabled (all the setting commented out in postgresql.conf). All 3 databases hold the same data (roughly 250,000 rows spread over 82 tables), this data was deleted via a java process (within an application server) using the EntityManager (not the quickest or best way, and it will never have to delete this much data usually). After the deletion has taken place all the estimated rows (via pgadmin) are still as if no delete has taken place !! but performing a count on the table reveals the true figure 0. I decided that a vaccum (analyze) might solve the problem of the estimated rows being way off, and on the 2 machines with autovaccum not enabled this analyze takes only a few minutes at most, but on the machine with it enabled it takes roughly 40 - 50 mins (2882336 ms.), i do not know why ?? does the autovaccum slow down a manually invoked vaccum ? And the analyze does not solve pgadmins problem with the estimated rows (but disconnecting and the reconnecting does :-)) Can anyone offer me some advice on why the vaccum takes so long on the autovaccum machine. Thanks in advance, Andy
