[BUGS] BUG #3853: Autovacuum not working
The following bug has been logged online: Bug reference: 3853 Logged by: Christopher Brian L. Jurado Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.2 Operating system: Windows XP/2003 Description:Autovacuum not working Details: I can see in the server logs that it has entries saying "autovacuum: processing database xxx". But it seems that pgAdmin still gives hints/suggestions that the tables need vacuuming. I don't know if this is a pgAdmin bug or not. But in my experience, pgAdmin seemed right and table statistics were not updated and query performance degrades as inserts/updates/deletes are done--because of outdated table statistics. Is this a bug? And if it is, was this fixed in 8.2? I can't find this in the release notes. ---(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: [BUGS] BUG #3853: Autovacuum not working
In response to "Christopher Brian L. Jurado" <[EMAIL PROTECTED]>: > > The following bug has been logged online: > > Bug reference: 3853 > Logged by: Christopher Brian L. Jurado > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.2 > Operating system: Windows XP/2003 > Description:Autovacuum not working > Details: > > I can see in the server logs that it has entries saying "autovacuum: > processing database xxx". But it seems that pgAdmin still gives > hints/suggestions that the tables need vacuuming. I don't know if this is a > pgAdmin bug or not. But in my experience, pgAdmin seemed right and table > statistics were not updated and query performance degrades as > inserts/updates/deletes are done--because of outdated table statistics. > Is this a bug? And if it is, was this fixed in 8.2? I can't find this in the > release notes. The autovacuum daemon has a lot of settings, it's possible that you simply don't have it configured aggressively enough. Please review the documentation and consider the fact that the default settings are very conservative: http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html Additionally, you're missing 8 bug fixes in your version, I highly recommend updating to 8.1.10 immediately. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(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: [BUGS] BUG #3852: Could not create complex aggregate
Tom Lane wrote: "Sokolov Yura" <[EMAIL PROTECTED]> writes: create or replace function add_group(grp anyarray, ad anyelement, size int4) returns anyarray language plpgsql ... create aggregate build_group(anyelement, int4) ( SFUNC= add_group, STYPE = anyarray ); ERROR: argument declared "anyarray" is not an array but type anyarray After chewing on this for awhile, it seems to me that pg_aggregate.c is using enforce_generic_type_consistency() in a rather fundamentally different way than it's being used anywhere else. [snip] I think we could make enforce_generic_type_consistency() clearer by adding an additional argument "bool allow_poly" which specifies whether polymorphic "actual" argument and result types are allowed. [snip] lookup_agg_function() should always invoke enforce_generic_type_consistency(), with this argument "true". This sounds like a reasonable plan to me. Although this problem really goes quite far back, I think it's probably not interesting to back-patch further than 8.2, because AFAICS the interesting cases involve aggregates with more than one argument. I agree, especially since this is the first time anyone has complained. Did you want me to work on this? I could probably put some time into it this coming weekend. Joe ---(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: [BUGS] BUG #3850: Incompatibility among pg_dump / pg_restore.
Diego Spano wrote: Stefan / List, these are the steps: 1- pg_dump sicoba|gzip>/home/backups/pg_backup/backup.pg 2- createdb sicoba6 3- psql -d sicoba6 < backup.pg And thats all. Errors appear when trying to add rows to first table, and so on... Find attached backup.pg. ok just took a look at that dump and the problem here is that you have CHECK constraints wrapped in a function that are doing lookups on other data than the current row. This is simply not supported (see the manual on that) and because postgresql does not now that there is some sort of hidden dependency on some data to exist it cannot actually infer that this might be a problem(might be worth to consider dumping CHECK constraints after loading the data though). If you think that through there might not even be a "correct" way to dump a database because depending on the complexity of the CHECK constraint there might not even be a way to load data in the "correct" way (think circular dependencies or dependencies on special values in multiple tables). In short you really need to look into converting the CHECK contraints on those two tables into triggers which will make this problem go away. regards Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3808: Connections stays open in stateCLOSE_WAIT
Tom Lane schrieb: "Dave Page" <[EMAIL PROTECTED]> writes: From: Bruce Momjian <[EMAIL PROTECTED]> Should we require the form to tell us their exact version number and throw an error if it isn't the current one? Past experience with other bug systems tells me we'll just end up with people selecting the lastest version to ensure the form is accepted, thus making it even harder for us to figure out what the problem really is. Agreed, that's a pretty awful idea. It's hard enough to get accurate version information, even without creating an incentive for people to lie. Moreover, of those that are too honest to do that, a lot would simply never file a report; thus keeping us from hearing about a problem that might well still exist. regards, tom lane The problem seems to be fixed in the current version. Btw. if there hasn't been a similar bug report before, it is still okey to post it, for all others who will have the same problem again and can read in the mailing list if a solution is the upgrade. You now, never touch a running system, especially if it's running a highly used production site. Btw. the update went without a glitch and was done in 5 Minutes, good work for that. But I didn't know that and asking before having to restore something for hours because of a failed update seems legitimate to me. With best regards, Daniel Migowski
[BUGS] BUG #3854: pg_dump dumps renamed primary key constraints by their old name
The following bug has been logged online: Bug reference: 3854 Logged by: Milen A. Radev Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.6 Operating system: Debian Etch Description:pg_dump dumps renamed primary key constraints by their old name Details: After a table and the implicit index related to its primary key are renamed, pg_dump still creates a statement for the primary key using its old name. Most of the time that's probably harmless but not when there are clustered tables. Steps to reproduce the problem: === dev:~# /usr/local/postgresql-8.2.6/bin/psql -U postgres -p 6543 Welcome to psql 8.2.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# create table x (x_id integer primary key, foo integer); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x" CREATE TABLE postgres=# \d x Table "public.x" Column | Type | Modifiers +-+--- x_id | integer | not null foo| integer | Indexes: "x_pkey" PRIMARY KEY, btree (x_id) postgres=# CLUSTER x_pkey ON x; CLUSTER postgres=# \d x Table "public.x" Column | Type | Modifiers +-+--- x_id | integer | not null foo| integer | Indexes: "x_pkey" PRIMARY KEY, btree (x_id) CLUSTER postgres=# alter table x rename to a; ALTER TABLE postgres=# alter index x_pkey rename to a_pkey; ALTER INDEX postgres=# \d a Table "public.a" Column | Type | Modifiers +-+--- x_id | integer | not null foo| integer | Indexes: "a_pkey" PRIMARY KEY, btree (x_id) CLUSTER postgres=# \q dev:~# /usr/local/postgresql-8.2.6/bin/pg_dump -U postgres -p 6543 postgres -- -- PostgreSQL database dump -- SET client_encoding = 'SQL_ASCII'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -- -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: a; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE a ( x_id integer NOT NULL, foo integer ); ALTER TABLE public.a OWNER TO postgres; -- -- Data for Name: a; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY a (x_id, foo) FROM stdin; \. -- -- Name: x_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: -- ALTER TABLE ONLY a ADD CONSTRAINT x_pkey PRIMARY KEY (x_id); ALTER TABLE a CLUSTER ON a_pkey; -- -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM postgres; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- dev:~# === The problematic statements are: ALTER TABLE ONLY a ADD CONSTRAINT x_pkey PRIMARY KEY (x_id); ALTER TABLE a CLUSTER ON a_pkey; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] ALTER INDEX/ALTER TABLE on indexes can cause unrestorable dumps
Andy just reported on IRC that renaming indexes can lead to unrestorable dumps under certain circumstances. A simple example(8.2 but at least 8.1 and 8.3 seem to behave exactly the same) for that is: test=# CREATE TABLE foo(bar int PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE test=# ALTER TABLE foo_pkey RENAME TO mynew_pkey; ALTER TABLE test=# CLUSTER mynew_pkey ON foo ; CLUSTER which - if dumped & restored leads to: ERROR: index "mynew_pkey" for table "foo" does not exist the reason for this seems to be that pg_dump is using the constraint name (which is not changed by ALTER TABLE/ALTER INDEX) and not the index name to dump this kind of information but I wonder if it would actually be more sensible (until we get ALTER TABLE .. ALTER CONSTRAINT) to simply forbid renaming indexes that are part of a constraint like that and hint towards ALTER TABLE ADD/DROP CONSTRAINT ? Stefan ---(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: [BUGS] ALTER INDEX/ALTER TABLE on indexes can cause unrestorable dumps
dup 3854 thanks Stefan Kaltenbrunner wrote: > the reason for this seems to be that pg_dump is using the constraint name > (which is not changed by ALTER TABLE/ALTER INDEX) and not the index name to > dump this kind of information but I wonder if it would actually be more > sensible (until we get ALTER TABLE .. ALTER CONSTRAINT) to simply forbid > renaming indexes that are part of a constraint like that and hint towards > ALTER TABLE ADD/DROP CONSTRAINT ? I think the fix is to make pg_dump emit ALTER TABLE/CLUSTER ON using the right index name ... (This makes me wonder whether we should be making pg_dump emit sorted output for tables with a clustering index.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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: [BUGS] BUG #3852: Could not create complex aggregate
Joe Conway <[EMAIL PROTECTED]> writes: > Did you want me to work on this? I could probably put some time into it > this coming weekend. I'll try to get to it before that --- if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, so it'd be nice to have this dealt with sooner than that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
