Re: [HACKERS] Batch update of indexes on data loading
2008/2/29, Tom Lane [EMAIL PROTECTED]: ITAGAKI Takahiro [EMAIL PROTECTED] writes: BTW, why REINDEX requires access exclusive lock? Read-only queries are forbidden during the operation now, but I feel they are ok because REINDEX only reads existing tuples. Can we do REINDEX holding only shared lock on the index? No. When you commit the reindex, the old copy of the index will instantaneously disappear; it will not do for someone to be actively scanning that copy. Can a shared lock be taken at first, and when the new index is ready, in order to delete the old index, elevate that lock to an exclusive one? Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/ ---(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: [HACKERS] PostgreSQL 8.4 development plan
2008/2/8, Heikki Linnakangas [EMAIL PROTECTED]: Gregory Stark wrote: git or its ilk would impact the lives of submitters and reviewers most. Basically it would allow two non-committers to collaborate, something which we can't really do effectively now. Two git-using non-committers can do that already, regardless of the master repository. Maybe the existing SVN, git and other mirrors could just become more official and supported in the sense that users can rely on them to be updated often enough? At the moment what is there are some links on http://developer.postgresql.org/index.php/Working_with_CVS#Other_versions_of_the_PostgreSQL_Repositoryand no indication of how reliable these repositories are. I suppos that a lot of reason for discussion would disappear if these repositories were made official and supported. Markus
Re: [HACKERS] configurability of OOM killer
2008/2/8, Tom Lane [EMAIL PROTECTED]: Martijn van Oosterhout [EMAIL PROTECTED] writes: On Thu, Feb 07, 2008 at 08:22:42PM +0100, Dawid Kuroczko wrote: while we are at it -- one feature would be great for 8.4, an ability to shange shared buffers size on the fly. Shared memory segments can't be resized... There's not even a kernel API to do it. Even if there were, it seems unlikely that we could reallocate shared memory without stopping all active transactions, so it'd be barely less invasive than a postmaster restart anyhow. What about allowing shared_buffers to be only greater than it was at server start and allocating the extra shared_buffers in one or more additional shm segments? Markus
Re: [HACKERS] CLUSTER and synchronized scans and pg_dump et al
2008/1/28, Tom Lane [EMAIL PROTECTED]: Do we have nominations for a name? The first idea that comes to mind is synchronized_scanning (defaulting to ON). synchronized_sequential_scans is a bit long, but contains the keyword sequential scans, which will ring a bell with many, more so than synchronized_scanning. Markus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Seperate command-line histories for seperate databases
2006/3/17, Bruce Momjian pgman@candle.pha.pa.us: Peter Eisentraut wrote: Bruce Momjian wrote: The psql manual pages for 8.1 now has: \set HISTFILE ~/.psql_history- :DBNAME Any reason psql doesn't do this by default? It is clear that the database name does not unambiguously identify a database, but having a history for each database name is already an improvement over the current situation. Markus Bertheau ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] psql -p 5433; create database test; \c test failing
Hi, this is from HEAD of a few days ago, it looks suspicious to me. 8.2devel runs on port 5433. [EMAIL PROTECTED]:~/pgsql$ bin/psql -p 5433 template1 markus Welcome to psql 8.2devel, 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 template1=# create database test; CREATE DATABASE template1=# \c test could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Previous connection kept Why is it trying to use 5432? Am I missing something? Is that the expected behaviour? Michael Fuhr hinted that http://archives.postgresql.org/pgsql-committers/2006-02/msg00139.php may be the cause of that. Markus Bertheau ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg.conf re-reading in signal handler or at next return to main loop?
2006/3/6, Tom Lane [EMAIL PROTECTED]: The comment is referring to the control flow in a backend; you're looking at the postmaster's sighup handler, which is different. Then the following comment patch is appropriate, afaics. Markus Bertheau Index: src/include/utils/guc.h === RCS file: /projects/cvsroot/pgsql/src/include/utils/guc.h,v retrieving revision 1.64 diff -c -r1.64 guc.h *** src/include/utils/guc.h 5 Mar 2006 15:59:07 - 1.64 --- src/include/utils/guc.h 6 Mar 2006 17:58:59 - *** *** 31,40 * * SIGHUP options can only be set at postmaster startup or by changing * the configuration file and sending the HUP signal to the postmaster ! * or a backend process. (Notice that the signal receipt will not be ! * evaluated immediately. The postmaster and the backend check it at a ! * certain point in their main loop. It's safer to wait than to read a ! * file asynchronously.) * * BACKEND options can only be set at postmaster startup, from the * configuration file, or by client request in the connection startup --- 31,40 * * SIGHUP options can only be set at postmaster startup or by changing * the configuration file and sending the HUP signal to the postmaster ! * or a backend process. (Notice that the signal receipt will be ! * evaluated immediately only in the postmaster. The backend checks it ! * at a certain point in its main loop. It's safer to wait than to ! * read a file asynchronously.) * * BACKEND options can only be set at postmaster startup, from the * configuration file, or by client request in the connection startup ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Plan for resetting commented postgresql.conf vars at sighup
Hi, this is the plan: In ParseConfigFile, record the fact that the variable was set in response to SIG_HUP in the status field (GUC_SET_FROM_SIGHUP). After setting all variables in postgresql.conf, set all variables that can appear in postgresql.conf (GUC_DISALLOW_IN_FILE), don't have their built-in value still set (PGC_S_DEFAULT), may be set from postgresql.conf (context not INTERNAL or POSTMASTER) and weren't set from SIGHUP (GUC_SET_FROM_SIGHUP) to their built-in default value. One problem is that set_config_option takes the variable's new value as a string, and at the moment the built-in values are saved with their real type (int, bool or double), so I can't call set_config_option with them. So I want to save the boot_val in config_generic as a string instead of in config_/type/ as their real type and change InitializeGUCOptions to set the initial reset_val from the string in boot_val. Any flaws? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Status of TODO item remove postmaster -o options
Hi, what's the status of this todo item? The relevant thread from 2001 is here: http://archives.postgresql.org/pgsql-hackers/2001-10/thrd6.php#00011 I think we should leave the code alone, and instead document in 7.2 that -o is deprecated (and explain what to do instead), with the intention of removing it in 7.3. I didn't find such a notice in the 7.2 release notes. Markus Bertheau ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Status of TODO: postgresql.conf: reset to default when commented
Hi, What's the status of the TODO item Allow commenting of variables in postgresql.conf to restore them to defaults Currently, if a variable is commented out, it keeps the previous uncommented value until a server restarted. I take that to apply to the configuration re-read at SIGHUP? Markus Bertheau ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg.conf re-reading in signal handler or at next return to main loop?
Hi, src/backend/utils/misc/README says If SIGHUP is received, the GUC code rereads the postgresql.conf configuration file (this does not happen in the signal handler, but at next return to main loop; note that it can be executed while within a transaction). SIGHUP_handler() calls ProcessConfigFile() which calls ParseConfigFile() which reads postgresql.conf. For me that means that postgresql.conf in read in the signal handler, which contradicts the claim in the README. Where's my error? Markus Bertheau ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] quote_boolean() and friends missing
Hi, I'm writing an application with a lot of PL/pgSQL and am constructing SQL queries on the fly. The quote_literal() and quote_ident() functions proved very much needed. Similarly, I need functions that return the SQL representation of all the other datatypes, not just TEXT: quote_boolean (), quote_date() and so on. For the sake of completeness I think these functions should exist. While the use of to_char() resp. decode() for some types is possible, it does feel like a workaround to me. Opinions? Do these functions belong in the PostgreSQL core? -- Markus Bertheau ☭ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] quote_boolean() and friends missing
В Пнд, 20/06/2005 в 10:01 -0400, Tom Lane пишет:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:
I'm writing an application with a lot of PL/pgSQL and am constructing
SQL queries on the fly. The quote_literal() and quote_ident() functions
proved very much needed. Similarly, I need functions that return the SQL
representation of all the other datatypes, not just TEXT: quote_boolean
(), quote_date() and so on. For the sake of completeness I think these
functions should exist. While the use of to_char() resp. decode() for
some types is possible, it does feel like a workaround to me. Opinions?
Do these functions belong in the PostgreSQL core?
What exactly do you think they would do? There is no analogy to
inserting escape characters for those other datatypes.
They would return the SQL representation of a given value.
quote_boolean(TRUE) = 'TRUE'
quote_bytea('\377'::bytea) = '\\377' (literally \377)
Maybe quote_* is not the right name, but the functionality is needed.
I'm currently looking for a way to get the SQL representation of a bytea
value and it looks like I'm going to have to write a C function for
that.
Markus
--
Markus Bertheau ☭ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] quote_boolean() and friends missing
В Пнд, 20/06/2005 в 10:10 -0400, Tom Lane пишет: Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes: Maybe quote_* is not the right name, but the functionality is needed. I don't think so --- at least not in plpgsql, which can do this already. Just assign the value to a text variable and then use quote_literal. Isn't that a workaround? Or is that the way such things are done in plpgsql? Markus -- Markus Bertheau ☭ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
, 24/05/2005 00:06 -0400, Tom Lane :
Joe Conway [EMAIL PROTECTED] writes:
Markus Bertheau wrote:
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Why would you expect an empty array instead of a NULL?
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output column.
To me, that implies that a subquery result of no rows generates a
one-dimensional array of no elements, not a null array.
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
Markus
--
Markus Bertheau [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
, 06/06/2005 10:44 -0400, Tom Lane :
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array.
But array_upper(ARRAY(SELECT 1 WHERE FALSE), 1) should return 0,
and array_upper(ARRAY[], 1), when the ARRAY[] syntax is supported,
should return 0.
Do I get that right?
Markus
--
Markus Bertheau [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]
, 06/06/2005 08:58 -0700, Joe Conway :
Joe Conway wrote:
Actually, consistent with my last post, I think array_upper() on a
zero-element array should return NULL. A zero-element array has a
defined lower bound, but its upper bound is not zero -- it is really
undefined.
Just to clarify my response, this is what I propose:
regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
array_upper
-
2
(1 row)
regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
?column?
--
t
(1 row)
Hmm, this gets really complicated and inconsistent. Complicated means
unusable. What about modifying the dimension syntax such that the second
number means number of elements instead of upper bound? That particular
problem would go away then, and array_upper('[0:0]={}'::int[]) can
return the correct 0 then.
What I'm actually worrying about is that array_upper(array(select 1
where false)) returns 0.
An option would be to drop the possibility to let the array start at
another index than 0. I don't know why it was decided to do that in the
first place. It seems a rather odd feature to me.
Markus
--
Markus Bertheau [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Dnia 24-05-2005, wto o godzinie 00:06 -0400, Tom Lane napisa(a):
Joe Conway [EMAIL PROTECTED] writes:
Markus Bertheau wrote:
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Why would you expect an empty array instead of a NULL?
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output column.
To me, that implies that a subquery result of no rows generates a
one-dimensional array of no elements, not a null array.
The point Markus is complaining about seems like it should
be easily fixable.
Great. Does this belong on the TODO?
--
Markus Bertheau [EMAIL PROTECTED]
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] patches for items from TODO list
Dnia 13-05-2005, pi o godzinie 16:01 -0700, Sergey Ten napisa(a): ?xml version='1.0'? table row col name='col1' null='n'Jackson, Sam/col col name='col2' null='n'\h/col /row row col name='col1' null='n'It is quot;perfectquot;./col col name='col2' null='n'#09;/col /row row col name='col1' null='n'/col col name='col2' null='y'/col /row /table Why didn't you do something to the effect of ?xml version='1.0'? table cols col name='col1'/ col name='col2'/ /cols row col null='n'Jackson, Sam/col col null='n'\h/col /row row col null='n'It is quot;perfectquot;./col col null='n'#09;/col /row row col null='n'/col col null='y'/col /row /table This avoids repeating the column names in every row, which don't change over the rows anyway. By reducing redundant information it also makes structurally invalid XML less likely (whether that is relevant depends on what people do with the XML data). Also you could encode the XML output as UTF-8, which would make the files more readable for humans if the text data is not ASCII. Markus -- Markus Bertheau [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] CVS HEAD problem: psql: symbol lookup error:
See FC3 broken with HEAD. , 06/05/2005 23:54 +0400, Oleg Bartunov : Just got this problem. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Markus Bertheau [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] GUC variable for setting number of local buffers
, 19/03/2005 12:57 -0500, Tom Lane : It's already true that the individual buffers, as opposed to the buffer descriptors, are allocated only as needed; which makes the overhead of a large local_buffers setting pretty small if you don't actually do much with temp tables in a given session. So I was thinking about making the default value fairly robust, maybe 1000 (as compared to the historical value of 64...). Why does the dba need to set that variable at all then? -- Markus Bertheau [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] prev main developer page
, 2005-02-11 00:45 -0500, Jaime Casanova : Hi, --- a little off topic --- I want to do something so informative like the map that was in the prev main developer page. Can anyone point me about what tool to use? and maybe some guidance on this? http://www.gnome.org/~jdub/blog/2005/Feb/11 thanks, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Markus Bertheau [EMAIL PROTECTED] signature.asc Description: =?koi8-u?Q?=E3=C0?= =?koi8-u?Q?_=DE=C1=D3=D4=C9=CE=D5?= =?koi8-u?Q?_=D0=CF=D7=A6=C4=CF=CD=CC=C5=CE=CE?= =?koi8-u?Q?=D1?= =?koi8-u?Q?_=D0=A6=C4=D0=C9=D3=C1=CE=CF?= =?koi8-u?Q?_=C3=C9=C6=D2=CF=D7=C9=CD?= =?koi8-u?Q?_=D0=A6=C4=D0=C9=D3=CF=CD?=
Re: [HACKERS] Update TIP 9 please
Who maintains the tips? , 14/11/2004 11:31 +, Simon Riggs : When 8.0 is released, TIP 9 should change from TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match to TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match (upgrade to 8.0!) We could change this now... ...and add another TIP to encourage people to upgrade...? -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] CVS should die
, 05.11.2004, 21:40, Heikki Linnakangas : On Fri, 5 Nov 2004, Travis P wrote: Heikki Linnakangas wrote: Interestingly, the subversion repository is 585MB, and the CVS repository is only 260MB, BDB or FSFS back-end? FSFS seems to require less space. (The BDB backend tends to pre-allocate space though, so maybe there was a big jump, but then growth will slow markedly, so making a comparison for a repository that will continue to grow is difficult.) BDB. Here's what the subversion book has to say about that: http://svnbook.red-bean.com/svnbook-1.1/ch05.html#svn-ch-5-sect-1.2.A We use svn over ssh and recently switched to fsfs because of the umask problem and because read-only access to bdb causes writes to the database. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] array_to_column function
, 30.10.2004, 21:54, David Fetter : Kind people, CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY) You want to see that function distributed with PostgreSQL? It would probably have to be implemented in C then, because PL/pgSQL-support has to be explicitly enabled for every database. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [pgsql-advocacy] Not 7.5, but 8.0 ?
, 05.06.2004, 10:28, David Garamond : This probably has been discussed and is probably a very minor point, but consider how many more years we want to be able to use the single digit.single digit major release numbering. Assuming 1 year between major releases (7.3.0 - 7.4.0 = +- 1 year), then we have 7.5-9.9 = 26 years = up until +- jul 2030. if we skip to 8.0 now, then we have up until 2023. Also we have 1 more chance to skip major number: 8.x - 9.0. Imagine what features will there be in 9.0 that is ground-breaking enough. Because after that, we don't have any more major number to jump into without going into 2 digits. What's the problem with 7.10? -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Big problem
, 24.05.2004, 16:12, Tom Lane : Christopher Kings-Lynne [EMAIL PROTECTED] writes: A guy on the IRC channel managed to accidentally click the wrong thing in phpPgAdmin and managed to remove superuser privileges from his only superuser. No sweat; we've seen this one before. Stop postmaster and start a standalone backend. Now you are a superuser, and you can create a new superuser, or just go in and UPDATE pg_shadow to make your original user super again. Exit standalone backend, restart postmaster, have a beer. The question whether we should prevent this from happening stands; I think we should. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Rough draft for Unicode-aware
, 13.05.2004, 04:42, Tom Lane : But if you have a platform that has mbstowcs and friends, please try it and let me know about any portability gotchas you see. I can't test it because with a clean 7.4.2 with the patch applied I get [EMAIL PROTECTED] postgresql-7.4.2]$ LANG=C make install make -C doc install make[1]: Entering directory `/home/bert/src/postgresql-7.4.2/doc' mkdir /home/bertheau/pg742 mkdir /home/bertheau/pg742/doc mkdir /home/bertheau/pg742/doc/postgresql mkdir /home/bertheau/pg742/doc/postgresql/html make[1]: *** [installdirs] Error 1 make[1]: Leaving directory `/home/bert/src/postgresql-7.4.2/doc' make: *** [install] Error 2 [EMAIL PROTECTED] postgresql-7.4.2]$ make and make check worked ok. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] unicode error and problem
, 24.03.2004, 11:33, Paolo Supino : Hi I received a unicode CSV file from someone (the file was created on a windows system) and I'm trying to import it into postgresql. When it gets to a line that isn't ascii it prints the following error and aborts: ERROR: copy: line 33, Invalid UNICODE character sequence found (0xd956). Try to convert the file from UTF-16 (which might be the encoding of the file) to UTF-8 with iconv: iconv --from UTF-16 --to UTF-8 file file.UTF-8 Maybe the file is not in UTF-16 but in some other encoding - convert accordingly then. By the way, Unicode is just a number - glyph mapping, it doesn't say anything about the representation of that number in the byte stream. UTF-8 and UTF-16 are such representation specifications. The encoding name in PostgreSQL should be changed from UNICODE to UTF-8 because UNICODE really just isn't an encoding. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_autovacuum next steps
, 22.03.2004, 00:12, Tom Lane : Please repost in some less proprietary format. Plain text is generally considered the thing to use on this list. -- Markus Bertheau [EMAIL PROTECTED] pg_autovacuum Version 2 Design Document: Exec Summary: pg_autovacuum was initially released as a contrib module in PostgreSQL v7.4. The version in 7.4 is by design very simple. No configuration is required, and very little configuration is possible. Despite these limitations it was voted the most popular new feature of PostgreSQL v7.4 according to the survey held on postgresql.org (http://www.postgresql.org/survey.php?View=1SurveyID=23). Despite it's popularity there is much room for improvement. This document sets out to define the most important improvements that would help pg_autovacuum to become a truly powerful asset to the suite of tools that come with PostgreSQL. Current Problems Limitations: Based on user feedback from people using pg_autovacuum in the field, and my own observations, there are a number of problems and limitation with pg_autovacuum. They are: Inability to customize thresholds on a per table basis Inability to set default thresholds on a per database basis Inability to exclude specific databases / tables from pg_autovacuum monitoring Inability to schedule vacuums during off-peak times Lack of integration related to startup and shutdown Ignorance of VACUUM and ANALYZE operations performed outside pg_autovacuum (requires backend integration? or can listen / notify can be used?) Lack of logging options / syslog integration / log rotation options Create table fails because template1 is busy I'm not sure how to address all of these concerns, or that they all should be addressed right now. One of my big questions is backend integration. I am leaning towards leaving pg_autovacuum as a client application in contrib for one more release. During this time, I can continue to tweak and improve pg_autovacuum so that we will have a very good idea what the final product should be before we make it a standard backend process. For PostgreSQL 7.5, I plan to implement these new features: 1.Per database defaults and per table thresholds (including total exclusion) 2.Persistent data 3.Single-Pass Mode (external scheduling from cron etc...) 4.Off peak scheduling 1. Per Database defaults and Per table Thresholds: There are differing opinions as to the best way to providing these this feature. The primary debate is where to save the configuration data. I see three options: 1.Store config data inside a special pg_autovacuum table inside existing databases that wants custom settings. 2.Use a config file. This would require some additional coding to add the required parsing, but is possible. 3.Create a pg_autovacuum database inside any cluster that wants to customize their settings. Since many people do not like tools that clutter their databases by adding tables, I think option 1 (adding a pg_autovacuum table to existing databases) is right out. Using a config file would be Ok, but would require additional parsing code. My preference is option 3. Since pg_autovacuum will (hopefully) eventually become an integrated part of the backend, it will eventually be able to add required data to the system catalogs. Given these two premises, as long as pg_autovacuum remains a contrib module it could use it's own database to mimic having system tables. If this database exists, it will be used, if it does not exist, then pg_autovacuum will work just as it did in the 7.4 release with very limited options available to it. The user will be able to specify a non-default database. Table Structure for database specific defaults and table specific thresholds: databases_defaults: (will reference the pg_class system table) id serial primary key exclude_databaseboolean default_vacuum_scaling_factor float default_vacuum_base_value int default_analyze_scaling_factor float default_analyze_base_value int dboid oid references pg_database.oid table_thresholds id serial primary key exclude_table boolean (exclude this table) vacuum_scaling_factor float (equivalent to -v) vacuum_base_value int (equivalent to -V) vacuum_thresholdfloat (if 0, use this threshold) analyze_scaling_factor float (equivalent to -a) analyze_base_value int (equivalent to -A) analyze_threshold float (if 0 use this threshold) relid oid references pg_classs.relid 2.Persistent pg_autovacuum Data: Right now pg_autovacuum has no memory of what was going on the last
Re: [HACKERS] \xDD patch for 7.5devel
, 05.11.2003, 16:25, Tom Lane : +#define HEXVALUE(c) (((c)='a') ? ((c)-87) : (((c)='A') ? ((c)-55) : ((c)-'0'))) This seems excessively dependent on the assumption that the character set is ASCII. Why have you hard-coded numeric equivalents into this macro? What not ASCII compatible character sets are out there in use still today? -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] TODO item psql schema completion already implemented
Hi, It seems to me that the TODO item under Clients * -Allow psql to do table completion for SELECT * FROM schema_part and table completion for SELECT * FROM schema_name. Is already implemented. If not what does it mean? select * from scheTAB completes the schema, and select * from schema_a.tabTAB completes the table. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] [Fwd: [BUGS] pg_autovacuum segv]
Forwarding to -hackers as per README - - From: Markus Bertheau [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [BUGS] pg_autovacuum segv Date: 09 Oct 2003 17:30:48 +0200 (gdb) run Starting program: /root/src/postgresql-7.4beta4/contrib/pg_autovacuum/pg_autovacuum [2003-10-09 05:28:13 PM] Failed connection to database template1 with error: FATAL: user root does not exist . Program received signal SIGSEGV, Segmentation fault. 0x0804a132 in check_stats_enabled (dbi=0x804e2f0) at pg_autovacuum.c:739 739 strcmp(on, PQgetvalue(res, 0, PQfnumber(res, stats_row_level))); (gdb) bt #0 0x0804a132 in check_stats_enabled (dbi=0x804e2f0) at pg_autovacuum.c:739 #1 0x0804a9b3 in main (argc=1, argv=0xbfffdf64) at pg_autovacuum.c:1004 #2 0x40103657 in __libc_start_main (main=0x804a8e4 main, argc=1, ubp_av=0xbfffdf64, init=0x8048b30 _init, fini=0x804afd0 _fini, rtld_fini=0x4000dcd4 _dl_fini, stack_end=0xbfffdf5c) at ../sysdeps/generic/libc-start.c:129 (gdb) This is on a i386 Redhat 7.2 with pg7.3.4 installed. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] cvs build failure
Hi, I'm trying to build cvs, but it fails: bison -y -d preproc.y preproc.y:6214: fatal error: maximum table size (32767) exceeded make[4]: *** [preproc.h] Error 1 make[4]: Leaving directory `/home/bert/src/pgsql/src/interfaces/ecpg/preproc' What's the problem? -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cvs build failure
, 01.07.2003, 21:41, Larry Rosenman : what version of bison are you using? I believe we require 1.875 these days. 1.35. I'll upgrade. Thanks. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] make check fails: user postgres doesn't exist
Hi, make check fails, and I have absolutely no idea where to look: running on port 65432 with pid 631 == creating database regression == createdb: could not connect to database template1: FATAL: user postgres does not exist pg_regress: createdb failed make: *** [check] 2 -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] make check fails: user postgres doesn't exist
, 01.07.2003, 23:30, Tom Lane : Markus Bertheau [EMAIL PROTECTED] writes: make check fails, and I have absolutely no idea where to look: running on port 65432 with pid 631 == creating database regression == createdb: could not connect to database template1: FATAL: user postgres does not exist (scratches head...) make check should automatically adopt your current username as the postgres superuser name, AFAIK. Perhaps there is some conflict in your environment settings? Do you have PGUSER defined, and if so is it different from your login name? Exactly that was the case, thanks. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cvs build failure
, 02.07.2003, 00:42, Tom Lane : Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe make configure act as though bison is missing? Not sure. It seems like that could create unnecessary problems in other cases. One trick would be to set YACC to some special value like bison.too.old and test for that when YACC is actually called from the Makefile. I kinda like Alvaro's suggestion of an --ignore-bison-version option to configure to suppress checking the version, but otherwise error out if we think bison is too old. The advantage to that is that you could manually override the automatic check if you had reason to know it was wrong (eg, you could see it'd misparsed the bison version string, or something). Also, it'd make sense to include that option by default in RPM builds, where you'd know you had up-to-date bison output files already included in the SRPM. But it seems weird to require a switch for the normal case, i.e. a tarball build, and not require it for a cvs build. I don't see overriding as an advantage, too, because misparsing of the bison version string would just be a bug that had to be fixed, imo. You can as well modify configure in that case to make it compile, and send the patch to the bison version parser in afterwards. Maybe add a comment to the Makefile where bison is called that gives a hint to the user in case bison fails. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] cvs build failure
, 02.07.2003, 01:10, Alvaro Herrera : On Wed, Jul 02, 2003 at 12:56:11AM +0200, Markus Bertheau wrote: Maybe add a comment to the Makefile where bison is called that gives a hint to the user in case bison fails. Not too many people read Makefiles these days Sorry, I meant an echo statement to communicate that message to stdout. Another suggestion would be to capture bison's error code for the table-too-large error and give an informational message about bison version. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] UTF8 and KOI8 mini-howto
, 13.06.2003, 20:02, Oleg Bartunov : Hi there, I've managed to get postgresql working with UTF8 and KOI8. Here is some mini-howto: http://www.sai.msu.su/~megera/postgres/utf8.html The correct name for UTF-8 locales is xx_XX.UTF-8, not upper case and the minus. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UTF8 and KOI8 mini-howto
, 15.06.2003, 21:08, Markus Bertheau : The correct name for UTF-8 locales is xx_XX.UTF-8, not upper case and s/not/note/ the minus. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] UTF8 and KOI8 mini-howto
, 13.06.2003, 20:02, Oleg Bartunov :
Hi there,
I've managed to get postgresql working with UTF8 and KOI8.
Here is some mini-howto:
http://www.sai.msu.su/~megera/postgres/utf8.html
select lower(''); returns '' with this setup here, any
idea why this is the case?
--
Markus Bertheau.
Berlin, Berlin.
Germany.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] [HACKERS] UTF8 and KOI8 mini-howto
, 16.06.2003, 01:26, Oleg Bartunov : Markus, I suspect lower,upper will not works with Unicode. It doesn't work with KOI8-R here, too. -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PHP] WebDB Developers Wanted
On Mon, 2002-09-16 at 10:52, Christopher Kings-Lynne wrote: Developers mailing list: [EMAIL PROTECTED] Hmm, that list does not appear on the sourceforge Lists page. Why? -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
