Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Feb 19, 2008 at 04:38:16PM +0100, Csaba Nagy wrote: Are you suggesting we keep appending? So if I set the same parameter 100 times, it would show up on 100 rows? What about not touching the config file at all, but write to a separate file which is completely under the control of postgres and include that at the end of the config file ? +2 Not only that, but including in the middle would allow to flexibly state what may be overridden by the gui and what not. And it is a clean separation of mechanism (general include mechanism) and policy. More complex schemes (a top-level postgresql.conf and a directory of includable module-specific files postgresql.conf.d) would be imaginable (mimicking a bit Debian's way to deal with such things). On syntax: anything (current is fine by me :) but please not XML :-/ Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFHu97FBcgs9XrR2kYRAul8AJ0dbPgVUjMCroIfUB9k4p6n6NU1vwCdFkLK HIdGsrX+lWOFBAJKSEDW2Ms= =lxkZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 02:59:44PM -0800, Josh Berkus wrote: Magnus, That's basically include but with a different name, no? Yes. FWIW, I seem to be lagged about 3 hours on -hackers. Why do you need to split it in two columns, and what would go in what column? Current data: postgres=# select name, category from pg_settings; name | category -+--- allow_system_table_mods | Developer Options archive_command | Write-Ahead Log / Settings archive_mode| Write-Ahead Log / Settings archive_timeout | Write-Ahead Log / Settings How it should be: postgres=# select name, category, subcategory from pg_settings; name | category | subcategory -+ allow_system_table_mods | Developer Options | archive_command | Write-Ahead Log| Settings archive_mode| Write-Ahead Log| Settings archive_timeout | Write-Ahead Log| Settings this would then allow us to do this: select * from pg_settings_categories name order Developer Options 37 Write-Ahead Log 11 select * from pg_settings_subcategories name category allow_system_table_mods Developer Options archive_command Write-Ahead Log archive_mode Write-Ahead Log and then generate a file which looks like this: # == Write-Ahead Log == # Settings archive_command = '/bin/rsync' archive_mode = 'on' # Fsync fsync = on wal_buffers = 8mb ... etc. This would allow the automatically generated version to be readable and searchable, if not quite as narrative as the present postgresql.conf. Ok, now I see the point. But does this really work in a scenario when the user edits the config file himself? The order will likely be broken pretty quickly anyway in that case... 3) have command line config write to postgresql.auto.conf, dumping the whole of pg_settings organized with headings in categories order. Don't get what you mean here. You mean you want a commandline tool to generate a config file from pg_settings? I meant from the SQL command line. Oh, ok. Then I'm in agreement. Another question completely, but related, is if it's actually the right thing to use postgresql.conf to write documentation. The way it is now we basically add all new config options to postgresql.conf.sample along with a comment that is the documentation. A different approach would be to only include the very most common settings, or possibly even only those that initdb sets to something non-default, in postgresql.conf.sample, and have the rest only added when they're actually used. Documentation really belongs in the documentation, after all... Yeah, we've taken an Apache-like approach of including heavy comments on the settings in the settings file itself. Unfortunately, I think changing that practice at this point would alienate a bunch of users. AFAIK, Apache doesn't document all it's parameters there. Or maybe it does and the distributions generaelly cut it down? ;-) //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 11:27:47PM -0500, Robert Treat wrote: On Tuesday 19 February 2008 20:08, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: On Tuesday 19 February 2008 15:05, Bruce Momjian wrote: One idea would be to remove duplicate postgresql.conf appended entries on server start. I think anything which has us appending extra settings to the end of the file is a non-starter. We'd get I changed the setting, but nothing's happening error reports 8x hour on #postgresql. Yeah, I agree. Any proposal that makes it materially harder for people to maintain the config files with an editor is going to suffer so much push-back that it will ultimately fail. And adding extra copies of settings to an existing file does make it harder. +1 That is indeed what I'd prefer, but the other way would have less impact on those that prefer config files. As in they could easily get rid of it. What I would suggest is to write a function in contrib/adminpack that updates the config file by replacing the variable assignment in-place. (Yes, it will have to be smart enough to parse the config file, but that hardly requires a great deal of smarts.) If that implementation sees sufficient usage then we can migrate the functionality into core. Wel,l it would take some logic to deal with: log_destination = 'stderr'# This is where we're sending the log But more to deal wtih # # This is a long comment about why we moved to syslog because of # blah blah blah blah lah # log_destination = 'syslog' # Yeah, we really did go to syslog! Now, if you change log_destination, you really should change both the comments as well. An easy way would be to just say don't use combinations of the two ways and have the user deal with it, though. phppgadmin would certainly use said function if it existed in core, so I'd suggest if we go that route put it in 8.4 straight away. My guess is it would also be easier to maintain if it was built-in. As I said before, my goal is to get rid of the adminpack and merge the functionality into core. Let's not add new stuff there. I've seen several cases of people choosing the windows version over the unix one simply because the functionality provided by the adminpack is not available on Unix by default. Sure, a little research would show it's fairly easy to get it in there, but nevertheless it's *seen* as a deficiency. It was complained up-thread that some installations make the config files read-only to the postgres user, but I see no conflict there. Anyone who does that is saying that they don't *want* automatic changes to the configuration settings. Such folk will not consider it a feature for the database to make an end-run around that policy. Hmm I don't think I've ever seen one like this, but thinking about it I suppose I could see the argument and way to do it... but yes, I think you'd get an error that the file was read-only, so the behavior would be similar to trying to edit it on the box as postgres user. Right, I don't see any problem at all with this. The default as set by initdb is that you can edit it. If you want to lock it down by permissions, go right ahead, but don't expect the GUIs for configuration to work after that. Seems very straightforward. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
On Feb 19, 2008 10:31 PM, Josh Berkus [EMAIL PROTECTED] wrote: Magnus, All, This is something I've been thinking about too, just because my efforts to write auto-config scripts have gotten bogged down in the need to parse and write .conf files in a paltform-agnostic way and preserve comments. I agree with Magnus that it's something we need to address. Having the ability to update .conf through an api other than reading writing a file one line will make developing future autotuning tools significanly easier. I think that the idea of just appending extra lines to the bottom of the file in chronoligical (or random) order is so messy and hackish that it's simply not worthy of consideration for the PostgreSQL project. I don't like it either. I think there is a place of chronological list of changes made to the configuration -- it is the log file. When configuration is changed remotely it must be logged, and an extra comment message might be nice. Instead, here's my proposal: 1) add to the top of postgresql.conf another file switch, like this: # auto_config_file = 'ConfigDir/postgresql.auto.conf' # if set, the auto config file will be read by the system and override the settings in the rest of this postgresql.conf file, which will be ignored. # to disable automated and SQL command-line-based configuration # comment the above or set it to an empty string 2) split the category column in pg_settings into two columns, and add a categories lookup table, so it can be sorted properly 3) have command line config write to postgresql.auto.conf, dumping the whole of pg_settings organized with headings in categories order. I think an arrangement like that will work well with pg_settings based config, autotuning, while still allowing backwards-compatible manual control via postgresql.conf. I kind of like the idea of having two files -- one user-managed and one database-managed. But let me first write few issues of general matter. 1) changes that cannot be done to live server: SET PERMANENT shared_buffers = '1GB'; Now, this is a setting that cannot be changed live, but it should be changeable. And we need a command to query what's permanent and what's current. 2) '1GB' -- If we are modifying postgres.conf I _think_ the format should be preserved, so not changed into number of pages but written 'as-is'. 3) If we do have two configuration files (+1), I think PostgreSQL should issue a BIG FAT WARNING saying that its overriding user-managed postgres.conf wih postgres.auto, on a per-setting basis. This way nobody would be surprised why their setting is not working. And the rollback of all remote changes would be one unlink away. 4) Saving actual file. Sometimes it could be nice to be able set work_mem globally (as if by postgres.conf) but not permanent (so you don't see these settings on next start), though I am not convinced the feature is worth the risks of people mixing up things. 5) if we have a file that is 100% PostgreSQL controlled, we could some day use it as an alternative to pg_hba.conf and pg_ident.conf. Regards, Dawid ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] distibuted transactions, SQL+XPath+XTree
Тюрин Дмитрий wrote: Hi list, Hi Dmitry, nice to have you back again. I see the following business opportunity for Postgres: I) Simple man can't program middleware to connect XML-client and Postgres. Aha! still trying to push an XML command system and http server into the backend. II) Request into several databases does not exist. Well, there are middleware layers that'll do so, or various case-specific solutions requiring dblink. The key problem is what you do with cross-database dependencies. How were you thinking of dealing with this? III) Notebooks need several switching-on and switching-off during transaction. How are you dealing with the locking issues? IV) Distance between strings are not supported, that makes aproximate searching impossible. Would that be not supported in the sense of contrib/fuzzystrmatch? V) There is no possibility to hide some (not all) records of table, granted to other users, from these users Apart from views or the veil pgfoundry project of course. I ask you to implement these solutions, that Postgres get advantage before other DBMS-es. I have prepered several drawing http://sql50.euro.ru/sql5.11.3.ppt to explain ideas. More details are described below. Were you looking to hire developers, or do you have customers who are looking to hire developers? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] longest prefix match
Hello Anybody got any ideas/experiences/links for 'longest prefix match' solution in PostgreSQL ? Basically,put some telephone prefices in some kind of trie,and be able to perform fast lookups ? Sincerely Dragan Zubac ---(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] longest prefix match
Hi, Le mercredi 20 février 2008, Dragan Zubac a écrit : Anybody got any ideas/experiences/links for 'longest prefix match' solution in PostgreSQL ? Basically,put some telephone prefices in some kind of trie,and be able to perform fast lookups ? Glad you ask! I've been taught there are several ways to have a fast longest prefix match queries working, the best of the possible solutions being to write a dedicated GiST index support. This is what I've begun doing here: http://pgsql.tapoueh.org/site/html/prefix/index.html http://pgfoundry.org/projects/prefix http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/ This should work ok with 8.2 or 8.3 (I don't intend to support older releases atm). The current code will allow you to create an index and use it in your queries, as stated on the README.txt: CREATE INDEX idx_prefix ON prefixes USING GIST(prefix gist_prefix_ops); SELECT * FROM prefixes WHERE prefix @ '0218751234'; But it seems (from some comments I've got on IRC) that current implementation performances are much less than one would expect from indexing support, so we're about to implement some prefix-range datatype in order to come up with a better picksplit(). I hope to have some time again to share on this project pretty soon. Regards, -- dim signature.asc Description: This is a digitally signed message part.
[HACKERS] failed assertion in toasting code
Hello -hackers, In the process of converting a multi-Tb datadabe from 8.2 to 8.3, Postgres 8.3 died at the failed assertion: TRAP: FailedAssertion(!(((toast_pointer).va_extsize (toast_pointer).va_rawsize - ((int32) sizeof(int32, File: tuptoaster.c, Line: 1134) LOG: server process (PID 8874) was terminated by signal 6: Aborted LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-02-20 07:43:00 MSK LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 78/BA00E060 LOG: record with zero length at 78/BC7A5FA8 LOG: redo done at 78/BC7A5F78 LOG: last completed transaction was at log time 2008-02-20 07:43:03.292665+03 LOG: autovacuum launcher started LOG: database system is ready to accept connections Unfortunately I cannot tell much more right now (I don't have the exact name of the table even), although I suspect which column is that (because I have only one column which is subject to toasting). I was doing basically pg_dumpall_8.2 | psql_8.3 I can say that the tables are large ~ 100-500 Gb in size and contain the column with the image in a special type image, which is toasted. cas=# \d sdssdr5.frame Table sdssdr5.frame Column | Type | Modifiers -+--+--- fieldid | bigint | not null .. htmid | bigint | not null img | image| not null CREATE TYPE image ( INPUT = image_in, OUTPUT = image_out, INTERNALLENGTH = -1, STORAGE = external ); Although I may agree that it could be a problem occurring due to the bug in type_in, type_out functions, I really doubt that, since it worked perfectly with 8.1-8.2 and the code is quite simple. I'll try to get the postmortem core dump, although it may require another couple of days since the segfault occured after two days of dumping :( Does anyone have ideas what could be the reason for the bug ? Thanks in advance. Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
Le mardi 19 février 2008, Gregory Stark a écrit : Magnus Hagander [EMAIL PROTECTED] writes: Yeah, that may actually be a very good way to implement it. I don't like the idea of continously appending to an existing file, but if we did have a separate file with a tightly controlled format that would be doable. +1 Separating the automatically written configuration and the explicit user configuration is definitely the right approach. My experience comes from Debian where packages editing their own configuration files is verboten. Otherwise you run into problems reconciling user-made changes and automatic changes. The include file method is workable but isn't perfect. What happens if a user connects with pgadmin and changes a parameter but that parameter is overridden by a variable in the config file? The alternative is to have two files and read them both. Then if you change a variable which is overridden by the other source you can warn that the change is ineffective. Ok, here's another idea, which only merits could well be to be different :) What about having a postgresql.conf.d directory containing a file per setting, maybe with a subdir per section. If I take Josh Berkus example, we'd have either: $PGDATA/postgresql.conf.d/allow_system_table_mods $PGDATA/postgresql.conf.d/archive_command $PGDATA/postgresql.conf.d/archive_mode $PGDATA/postgresql.conf.d/archive_timeout or: $PGDATA/postgresql.conf.d/developer_options/allow_system_table_mods $PGDATA/postgresql.conf.d/wal/settings/archive_command $PGDATA/postgresql.conf.d/wal/settings/archive_mode $PGDATA/postgresql.conf.d/wal/settings/archive_timeout $PGDATA/postgresql.conf.d/wal/fsync/fsync $PGDATA/postgresql.conf.d/wal/fsync/wal_buffers Each file would then only contains the parameter value, with or without comments in it, e.g.: cat $PGDATA/postgresql.conf.d/log/where/log_destination 'syslog' # # This is a long comment about why we moved to syslog because of # blah blah blah blah lah # This would solve a part of the configuration file parsing issues and I think would ease much of the 'make it all automatic and writable by backends, and still editable by user without too much confusion in the file(s)', but does nothing about Magnus remarks about comments (mis-)organisation in the file. Or we could force the files format to have the value in the first line, no comments and values on the same line allowed, then anything on following lines. Of course you end up with a forest of files, and that would be a pain to manually edit, but provided the categorization in pg_settings, it seems easy enough to automatically transform postgresql.conf to the 'forest', so there could be some way for the DBA to say he wont ever want resort to automatic configuration handling. Then postgresql.conf is the edited file and reload will have PostgreSQL generate the forest before to use it. Hope this helps, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] failed assertion in toasting code
Sergey E. Koposov [EMAIL PROTECTED] writes: In the process of converting a multi-Tb datadabe from 8.2 to 8.3, Postgres 8.3 died at the failed assertion: TRAP: FailedAssertion(!(((toast_pointer).va_extsize (toast_pointer).va_rawsize - ((int32) sizeof(int32, File: tuptoaster.c, Line: 1134) ... Does anyone have ideas what could be the reason for the bug ? What the assert is saying is that the datum it's trying to toast is compressed but the compressed version is larger than the original -- which shouldn't ever happen because we don't store such data compressed. I haven't quite figured out where the error is yet though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] failed assertion in toasting code
Does anyone have ideas what could be the reason for the bug ? Compression of varlena's header, introduced in 8.3. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] failed assertion in toasting code
Sergey E. Koposov [EMAIL PROTECTED] writes: Hello -hackers, In the process of converting a multi-Tb datadabe from 8.2 to 8.3, Postgres 8.3 died at the failed assertion: TRAP: FailedAssertion(!(((toast_pointer).va_extsize (toast_pointer).va_rawsize - ((int32) sizeof(int32, File: tuptoaster.c, Line: 1134) LOG: server process (PID 8874) was terminated by signal 6: Aborted LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-02-20 07:43:00 MSK LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 78/BA00E060 LOG: record with zero length at 78/BC7A5FA8 LOG: redo done at 78/BC7A5F78 LOG: last completed transaction was at log time 2008-02-20 07:43:03.292665+03 LOG: autovacuum launcher started LOG: database system is ready to accept connections Unfortunately I cannot tell much more right now (I don't have the exact name of the table even), although I suspect which column is that (because I have only one column which is subject to toasting). I was doing basically pg_dumpall_8.2 | psql_8.3 I can say that the tables are large ~ 100-500 Gb in size and contain the column with the image in a special type image, which is toasted. cas=# \d sdssdr5.frame Table sdssdr5.frame Column | Type | Modifiers -+--+--- fieldid | bigint | not null .. htmid | bigint | not null img | image| not null CREATE TYPE image ( INPUT = image_in, OUTPUT = image_out, INTERNALLENGTH = -1, STORAGE = external ); You aren't doing anything funny in the image_in function to generate compressed varlenas manually are you? Assuming not then it must be a case where we're saving less than 4 bytes and that's appearing as a saving in one place but then not somewhere else once you take into account the headers. Except I've just gone through the code looking for that kind of error and didn't spot it. I'll keep looking (or someone else will probably spot it before I do anyways) but if these images are mostly incompressible data you would probably be better off marking the columns as storage external so Postgres just toasts them as-is instead of trying to compress them first with: ALTER column SET STORAGE EXTERNAL -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(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] Permanent settings
On Wed, Feb 20, 2008 at 11:20:29AM +0100, Dimitri Fontaine wrote: Le mardi 19 février 2008, Gregory Stark a écrit : Magnus Hagander [EMAIL PROTECTED] writes: Yeah, that may actually be a very good way to implement it. I don't like the idea of continously appending to an existing file, but if we did have a separate file with a tightly controlled format that would be doable. +1 Separating the automatically written configuration and the explicit user configuration is definitely the right approach. My experience comes from Debian where packages editing their own configuration files is verboten. Otherwise you run into problems reconciling user-made changes and automatic changes. The include file method is workable but isn't perfect. What happens if a user connects with pgadmin and changes a parameter but that parameter is overridden by a variable in the config file? The alternative is to have two files and read them both. Then if you change a variable which is overridden by the other source you can warn that the change is ineffective. Ok, here's another idea, which only merits could well be to be different :) What about having a postgresql.conf.d directory containing a file per setting, maybe with a subdir per section. If I take Josh Berkus example, we'd have snip IMHO, if we do that it really sucks for those who use manual configuration files, to the point of being completely unusable. It could be valid if we want to support config only through the API, but that's not what people are asking for. We need something that's low-impact for existing users, and this certainly isn't. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] failed assertion in toasting code
Gregory Stark [EMAIL PROTECTED] writes: CREATE TYPE image ( INPUT = image_in, OUTPUT = image_out, INTERNALLENGTH = -1, STORAGE = external ); ALTER column SET STORAGE EXTERNAL Hum. I just noticed that you had set STORAGE = external in your type declaration. That makes it pretty odd that it would be going through this code path at all. Could you send select * from pg_attribute where attrelid = 'sdssdr5.frame'::regclass and attname = 'img'; -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] failed assertion in toasting code
On Wed, 20 Feb 2008, Gregory Stark wrote:
You aren't doing anything funny in the image_in function to generate
compressed varlenas manually are you?
No, I don't. The only thing I do there is
unsigned char *in = PG_GETARG_CSTRING(0);//AABBCCDDEE1122;//
and return the pointer to the palloced constructed standard varlena
datum with
typedef struct
{
int4 length;
unsigned char data[1];
} image;
image *im = (image *) palloc(VARHDRSZ + out_len);
memset(im, 0, VARHDRSZ + out_len);
im-length = out_len + VARHDRSZ;
/* fill the im-data
...
*/
PG_RETURN_POINTER(im);
Regards,
Sergey
***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] generating non-unicode 8-bit text data in SQL_ASCII encoding
In trying to generate random incompressible data I ran into this. I thought we said char()/ascii() would be allowed to generate either unicode code points if your encoding was unicode or 8-bit ascii if it's SQL_ASCII. Is there any way to generate random 8-bit characters in SQL_ASCII encoding now? postgres=# \l List of databases Name| Owner | Encoding ---+---+--- postgres | stark | SQL_ASCII template0 | stark | SQL_ASCII template1 | stark | SQL_ASCII (3 rows) postgres=# select char((random()*255)::integer+1); char -- ' (1 row) postgres=# select char((random()*255)::integer+1); ERROR: char out of range postgres=# select char((random()*255)::integer+1); ERROR: char out of range -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] More char()/ascii()
Also, I thought we said this would be an error: postgres=# select length(char(0)); length 0 (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] distibuted transactions, SQL+XPath+XTree
Richard, I see the following business opportunity for Postgres: I) Simple man can't program middleware to connect XML-client and Postgres. RH Aha! still trying to push an XML command system and http server into the RH backend. Yes. II) Request into several databases does not exist. RH what you do with cross-database dependencies As i understand, you are saying about inter-database FK connect address www.default.bz; create nick db1 address www.site.com; create nick db2 address www.data.net; connect addr=“www.data.net” user=“Smith” pwd=“qxuwb”; create table b ( b1 xml, b2 number, b3 xml ); connect addr=“www.site.com” user=“Tomson” pwd=“ncwhif”; create table a ( a1 number references db1:b(@b1/k/m/@m1), a2 xml, foreign key (@a2/p/q/@q1) references db1:b(@b2), a3 xml, foreign key (@a3/p/q/@q1) references db1:b(@b3/k/m/@m1) ); e.g. slides #75-76 in http://sql50.euro.ru/sql5.11.3.ppt ? There are two case, which i'm naming 'set' ( a= b, slide #93-94) and 'relay-rece' ( a = b , slide #95-98). I propose to not try all second database, i.e. to not use reference from second database to first database (case of 'set'). Case 'relay-race' works clearly. Of course, i raise question about designation of interbase FK in modellers (slide #105). III) Notebooks need several switching-on and switching-off during transaction. RH How are you dealing with the locking issues? I propose to freeze transaction (#118): create user u identified by p waited 1.0/0; -- yy.mm.dd/hh.mm.ss; freeze; -- like disconnect - ?res code=0 frozen=7482 ? !-- from ‘default.edu’ -- ?res code=0 frozen=8726 ? !-- from ‘site.com’ -- ?res code=0 frozen=9278 ? !-- from ‘data.net’ -- ?res code=0 frozen=3825 ? !-- from ‘data.net’: second transaction -- ?res code=0 frozen=6384 ? !-- from ‘store.org’ -- ?res code=6 ? !-- from ‘place.ws’: database is broken -- unfreeze addr=site.com user=Tomson pwd=ncwhif safe=8726; -- like ‘connect’ unfreeze addr=data.net user=Smith pwd=qxuwb safe=9278; P.S. Of course, i propose possibility to freeze failed commit (#120). IV) Distance between strings are not supported, that makes aproximate searching impossible. RH Would that be not supported in the sense of contrib/fuzzystrmatch? I propose 1) to use Levenshtein distance recursively: for letters in words, for words in phrases (separated by marks of punctuation), for phrases (e.g. sentances) in string 2) to count convolutions (several words into abbreviation, and back) V) There is no possibility to hide some (not all) records of table, granted to other users, from these users RH Apart from views depending of user, which look in view RH or the veil pgfoundry project of course. I'm not understanding, clarify, please. I ask you to implement these solutions, that Postgres get advantage before other DBMS-es. I have prepered several drawing http://sql50.euro.ru/sql5.11.3.ppt to explain ideas. More details are described below. RH Were you looking to hire developers, or do you have customers who are RH looking to hire developers? Neither this, nor that. I'm looking for volunteers. P.S. I'm also seggesting to implement 1) restricted table for stream processing: create table a ( a1 number, a2 number ) size 5; create table aa (…); -- resize a to 10; -- resize a to infinity; -- redirect a to aa; create table a ( a1 number, a2 number ) size 5 direct aa; 2) timer: create timer TimerName start yy.mm.dd/hh.mm.ss end yy.mm.dd/hh.mm.ss schedule (yy.mm.dd/hh.mm.ss, yy.mm.dd/hh.mm.ss, …) per yy.mm.dd/hh.mm.ss as begin … end; create timer t1 schedule (01/0, 03/0, 10/0) per 01.00/0 as … ; -- 1-st, 3-rd, 10-th day of each month create timer t2 schedule (0/0.05, 0/0.10, 0/0.20) per 0/01 as … ; -- 5-, 10-, 20-th minutes of each hour create timer t3 schedule (0) per 1/0 as … ; -- each day Dmitry Turin SQL5 (5.11.3) http://sql50.euro.ru HTML6 (6. 5.4) http://html60.euro.ru Unicode7 (7. 2.1) http://unicode70.euro.ru Computer2 (2. 0.2) http://computer20.euro.ru ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
Dawid Kuroczko escribió: 1) changes that cannot be done to live server: SET PERMANENT shared_buffers = '1GB'; Now, this is a setting that cannot be changed live, but it should be changeable. And we need a command to query what's permanent and what's current. IMO restart-only settings should not be changeable via the new SQL command. It's just too messy to deal with that. Also, this SQL command should reject being used in a transaction block (BEGIN/COMMIT), because rolling it back seems fragile. -- 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: [HACKERS] failed assertion in toasting code
On Wed, 20 Feb 2008, Gregory Stark wrote: Could you send select * from pg_attribute where attrelid = 'sdssdr5.frame'::regclass and attname = 'img'; cas=# select * from pg_attribute where attrelid = 'sdssdr5.frame'::regclass and attname = 'img'; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount --+-+--+---+++--+-+---+--++--++---+--++- 16856 | img |16418 |-1 | -1 | 29 |0 | -1 |-1 | f| e | i| t | f | f| t | 0 (1 row) Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] failed assertion in toasting code
Sergey E. Koposov [EMAIL PROTECTED] writes:
typedef struct
{
int4 length;
unsigned char data[1];
} image;
image *im = (image *) palloc(VARHDRSZ + out_len);
memset(im, 0, VARHDRSZ + out_len);
im-length = out_len + VARHDRSZ;
Ah, that's not going to work in 8.3 any longer. You have to change this to:
SET_VARSIZE(im, out_len+VARHDRSZ)
And you have to access the length with VARSIZE_ANY_EXHDR() (or a few other
macros but that's the most convenient).
Phew. You had me scared there.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] More char()/ascii()
Gregory Stark wrote: Also, I thought we said this would be an error: postgres=# select length(char(0)); length 0 (1 row) IIRC, we said chr(0) would give you an error, and it does. I don't recall any discussion of char(0), but no doubt a few brain cells have died since then. Fixing it would be trivial, I'm sure, but is it really a problem? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 09:36:43AM -0300, Alvaro Herrera wrote: Dawid Kuroczko escribió: 1) changes that cannot be done to live server: SET PERMANENT shared_buffers = '1GB'; Now, this is a setting that cannot be changed live, but it should be changeable. And we need a command to query what's permanent and what's current. IMO restart-only settings should not be changeable via the new SQL command. It's just too messy to deal with that. I respectfully disagree. It should be settable. You need a restart, sure, and the GUI app should tell you that. But you shuld be able to change them. (for example, pgadmin can restart the server just fine for you if you're on Windows) Also, this SQL command should reject being used in a transaction block (BEGIN/COMMIT), because rolling it back seems fragile. That I can agree with. //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] failed assertion in toasting code
On Wed, 20 Feb 2008, Gregory Stark wrote: Ah, that's not going to work in 8.3 any longer. You have to change this to: SET_VARSIZE(im, out_len+VARHDRSZ) Phew. You had me scared there. Thank you. Sorry for scaring :) I hope that everything will work fine now. Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(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: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 10:20:55AM -0300, Alvaro Herrera wrote: Magnus Hagander escribió: On Wed, Feb 20, 2008 at 09:36:43AM -0300, Alvaro Herrera wrote: IMO restart-only settings should not be changeable via the new SQL command. It's just too messy to deal with that. I respectfully disagree. It should be settable. You need a restart, sure, and the GUI app should tell you that. But you shuld be able to change them. (for example, pgadmin can restart the server just fine for you if you're on Windows) What if the server doesn't come back up? Say, because you increased shared_buffers and now it doesn't fit on the kernel limits. If you haven't made arrangements to be able to edit the postgresql.conf file beforehand, you're hosed. Right. So a warning in the GUI program is important, but that's no reason to restrict the API. Doing DROP TABLE can also be very dangerous. Or DROP TYPE CASCADE. Yet we do support them, and rely on the user to think first, or the GUI pogram to show warnings. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] More char()/ascii()
Andrew Dunstan [EMAIL PROTECTED] writes: Gregory Stark wrote: Also, I thought we said this would be an error: postgres=# select length(char(0)); length 0 (1 row) IIRC, we said chr(0) would give you an error, and it does. I don't recall any discussion of char(0), but no doubt a few brain cells have died since then. Ah, that explains my confusion. I knew it seemed weird to have to quote it. Fixing it would be trivial, I'm sure, but is it really a problem? The char data type which I was mistakenly using is enough of a wart that it probably doesn't matter what we do with it. There aren't any security holes with the current behaviour (I don't think). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
Magnus Hagander escribió: On Wed, Feb 20, 2008 at 09:36:43AM -0300, Alvaro Herrera wrote: IMO restart-only settings should not be changeable via the new SQL command. It's just too messy to deal with that. I respectfully disagree. It should be settable. You need a restart, sure, and the GUI app should tell you that. But you shuld be able to change them. (for example, pgadmin can restart the server just fine for you if you're on Windows) What if the server doesn't come back up? Say, because you increased shared_buffers and now it doesn't fit on the kernel limits. If you haven't made arrangements to be able to edit the postgresql.conf file beforehand, you're hosed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
On Tue, Feb 19, 2008 at 08:37:51PM -0500, Andrew Dunstan wrote: The way I intended to do it would indeed allow it to be undone simply by executing 'drop language plpgsql' in template1. Why isn't it enough that administrators can do CREATE LANGUAGE plpgsql in template1? I think this is completely unneeded, given the ease with which this can be enabled. It seems to me the source distribution of the code ought to be minimalist. Moreover, given that the trend in daemons is to turn everything off by default, just in case, I'm puzzled why we want to do the opposite here. Note that packagers are in a different boat entirely; I see no reason why packages might not turn this on by default. But they have a narrower target of users. I'd be more persuaded by a convenience package of things to enable by default that ships with the code, and can be run by the installing party. We'd at least then have an argument to the security community that we require explicit administrator action to enable the features. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
Le mercredi 20 février 2008, Magnus Hagander a écrit : What about having a postgresql.conf.d directory containing a file per setting, maybe with a subdir per section. If I take Josh Berkus example, we'd have snip IMHO, if we do that it really sucks for those who use manual configuration files, to the point of being completely unusable. It could be valid if we want to support config only through the API, but that's not what people are asking for. We need something that's low-impact for existing users, and this certainly isn't. What about having PG still able to load postgresql.conf or the tree of config files, automatically, erroring when both mechanisms are in use at the same time. This would allow for manual config editing installations and SQL embedded configuration setting, just not in the same cluster at the same time. I see how the proposal fails to answer to people wanting to edit the same configuration both with a file editor and SQL commands, but maybe having either postgresql.conf or SQL interface for configuration could be a first step? -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Permanent settings
Dimitri Fontaine wrote: Le mercredi 20 février 2008, Magnus Hagander a écrit : What about having a postgresql.conf.d directory containing a file per setting, maybe with a subdir per section. If I take Josh Berkus example, we'd have snip IMHO, if we do that it really sucks for those who use manual configuration files, to the point of being completely unusable. It could be valid if we want to support config only through the API, but that's not what people are asking for. We need something that's low-impact for existing users, and this certainly isn't. What about having PG still able to load postgresql.conf or the tree of config files, automatically, erroring when both mechanisms are in use at the same time. This would allow for manual config editing installations and SQL embedded configuration setting, just not in the same cluster at the same time. I see how the proposal fails to answer to people wanting to edit the same configuration both with a file editor and SQL commands, but maybe having either postgresql.conf or SQL interface for configuration could be a first step? No. Seriously. We need to have reasonable manual editability preserved for all cases. The tree of files proposal just strikes me as a basic non-starter, and, frankly, a piece of bad design. If you need structure, then using the file system to provider it is just a bad move. All this discussion seems to me to be going off into the clouds, where every objection is met with some still more elaborate scheme. I think we need to look at simple, incremental, and if possible backwards compatible changes. cheers andrew ---(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] Permanent settings
Le mercredi 20 février 2008, Andrew Dunstan a écrit : No. Seriously. We need to have reasonable manual editability preserved for all cases. The tree of files proposal just strikes me as a basic non-starter, and, frankly, a piece of bad design. If you need structure, then using the file system to provider it is just a bad move. Ok. Just wanted to have the idea exposed, nothing more. The aim was not to structure the file (that was just a bonus), but to be able to very easily edit the settings from C-code... comments included. All this discussion seems to me to be going off into the clouds, where every objection is met with some still more elaborate scheme. I think we need to look at simple, incremental, and if possible backwards compatible changes. ISTM backward compatible could mean including automatic migration code, where PostgreSQL 8.4 (e.g.) would convert old postgresql.conf to new format all by itself, with either a add-on command line tool or at first start maybe... -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] minimal update trigger
On Tue, Feb 19, 2008 at 09:32:30PM -0500, Andrew Dunstan wrote:
As discussed a little while back, I would like to add a generic
trigger function which will force an update to skip if the new and
old tuples are identical.
This one has lots of use cases. Did the earlier discussion settle on
whether there should be a GUC and/or CREATE DATABASE and/or initdb
option for this?
Cheers,
David.
The guts of this is the following snippet of code:
|rettuple = newtuple = trigdata-tg_newtuple;
oldtuple = trigdata-tg_trigtuple;
if (newtuple-t_len == oldtuple-t_len
newtuple-t_data-t_hoff == oldtuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newtuple-t_data) ==
HeapTupleHeaderGetNatts(oldtuple-t_data)
(newtuple-t_data-t_infomask ~HEAP_XACT_MASK) ==
(oldtuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp(((char *)newtuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
((char *)oldtuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newtuple-t_len - offsetof(HeapTupleHeaderData, t_bits))
== 0)
{
rettuple = NULL;
}
return rettuple;
I propose to call the function pg_minimal_update.
Unless there is an objection I will put together a patch + docs for this
shortly. Not quite sure what section of the docs to put it in - maybe a new
subsection of the Functions chapter?
cheers
andrew
|
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] minimal update trigger
David Fetter wrote: On Tue, Feb 19, 2008 at 09:32:30PM -0500, Andrew Dunstan wrote: As discussed a little while back, I would like to add a generic trigger function which will force an update to skip if the new and old tuples are identical. This one has lots of use cases. Did the earlier discussion settle on whether there should be a GUC and/or CREATE DATABASE and/or initdb option for this? None of the above. All we will be providing is a trigger function. You would create the trigger as with any other trigger: | CREATE TRIGGER _min BEFORE UPDATE ON mytable FOR EACH ROW EXECUTE PROCEDURE pg_minimal_update(); cheers andrew | ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone view
Tom Lane wrote: Naz Gassiep [EMAIL PROTECTED] writes: I think that it would be great if the pg_timezone_names and pg_timezone_abbrevs included a boolean field indicating if that item is in the Olsen DB Huh? They're all in the Olsen DB Not true, the zone.tab file has 398 zones defined, and in my PG 8.2 running on Debian, there are 564 timezones listed in pg_timezone_names. The field I propose would indicate which 398 of those 564 are listed in zone.tab as those are the actual Olsen defined timezones, the rest are aliases. Regards, - Naz. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone view
Alvaro Herrera wrote: Naz Gassiep wrote: It may also be beneficial to add the ISO 3166 column into that view, the data is in zone.tab and I can't see a reason to not include it. We also have the country name in iso3166.tab and the geo coordinates. And there is also a comment field. Which ones make sense to add I'd say the country name and geo coordinates both make sense to add. Regards, - Naz. ---(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] Permanent settings
Andrew Dunstan [EMAIL PROTECTED] writes: All this discussion seems to me to be going off into the clouds, where every objection is met with some still more elaborate scheme. I think we need to look at simple, incremental, and if possible backwards compatible changes. +1. Let me propose the simplest possible scheme, namely The SQL-exposed function knows how to find and replace the definition of a variable (commented or otherwise) in the primary configuration file. It does not chase INCLUDEs. If it doesn't find the target variable anyplace in the primary file, it errors out. What this would mean is that for people using configuration include files (which is surely a tiny minority), anything tucked away in an include file is not editable from a SQL session. This gives them the flexibility to decide which things are editable and which aren't, and by removing items from the base config file and/or rearranging the ordering of includes, they can control which things can be overridden from SQL. In particular, this works conveniently for the case where the base config file is in $PGDATA and is postgres-writable, whereas the include file(s) are shared across database clusters and are not writable. I think that's the most obvious use-case for having an include file. 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: [HACKERS] ANALYZE to be ignored by VACUUM
On Feb 19, 2008 8:31 AM, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: In my workload, ANALYZE takes long time (1min at statistics_target = 10, and 5min at 100), but the updated table needs to be vacuumed every 30 seconds because seqscans run on the table repeatedly. There is something *seriously* wrong with that. If vacuum can complete in under 30 seconds, how can analyze take a minute? (I'm also wondering whether you'll still need such frantic vacuuming with HOT...) There are two tables here: [S] A small table, that is frequently updated and seqscan-ed [L] A large table, that takes a long time to be analyzed The table [S] should be vacuumed every 30 seconds, because dead tuples affects the performance of seqscan seriously. HOT and autovacuum are very useful here *unless* long transactions begins. Analyzing [L] takes 1-5 minutes and both HOT and vacuums don't work during it. I want to use statistics_target = 100 at heart for more accurate statistics, but I'm using 10 instead because of avoiding long transactions by analyze. I am sure the idea is not original, yet still I would like to know how hard would it be to support local (per table) oldest visible XIDs. I mean, when transaction start you need to keep all tuples with xmin = oldest_xid in all tables, because who knows what table will that transaction like to touch. But then again, there is relatively large list of cases when we don't need to hold vacuum on _all_ relations. These include: SELECTs in auto-commit mode -- provided the SELECT is not something fancy (not immutable PL-functions), we just need to keep a snapshot of affected tables. DMLs in auto-commit mode -- provided no PL-functions or triggers are in effect. WITH HOLD CURSORS. Yes, I know, WITH HOLD cursor on first COMMIT will create a copy of rows to be returned (which can take a looong time in some cases), but perhaps it could be possible to just lock the table from vacuuming and skip the temporary store. And lots of other, when done in auto-commit. Like ALTER TABLEs, CREATE TABLE AS SELECT, COPY, etc... I am sure that such an idea isn't original. What are the main obstacles in making it happen except timemoney? :) Regards, Dawid ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Permanent settings
Magnus, all: Other thoughts: 1) fix category display for pg_settings. 'nuff said. 2) allow *commenting* of pg_settings / SET PERMANENT. Thus: SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM'; SET SELECT name, comment FROM pg_settings WHERE name = 'work_mem'; work_mem| 16mb too high; OOM 3) We should also discuss potentially having changed_on, changedby data: SELECT name, changedon, changedat, changedby FROM pg_settings; work_mem | 2008-01-22 14:35:11 | postgres shared_buffers | 2008-01-20 13:11:11 | postgresql.conf file (for postgresql.conf file the changedon would always be the date of the last reboot) 4) We'll need a log setting for log SET, since I can see people wanting to log this kind of activity without necessarily logging all statements. --Josh Berkus ---(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: [HACKERS] Permanent settings
Tom, The SQL-exposed function knows how to find and replace the definition of a variable (commented or otherwise) in the primary configuration file. It does not chase INCLUDEs. If it doesn't find the target variable anyplace in the primary file, it errors out. Hmmm. I guess I'm just not good enough with conf file parsing. The problem I've constantly run into with parsing and modifying settings in a user-edited postgresql.conf file is that sometimes users do their own chronological documentation: #work_mem = 1mb #original setting #work_mem = 4mb #2008-01-05 not high enough #work_mem = 32mb#2008-01-11 too high, OOM killer work_mem = 16mb #current setting If the user then chooses to use the API to update work_mem to 12mb, how do we process the file? Does it become this? work_mem = 12mb work_mem = 12mb work_mem = 12mb work_mem = 12mb The above wouldn't seem such a problem, except that sometimes those individual setting lines could be widely separated in the file, depending the application's history of DBAs. Further, sometimes comments can look like this: # warning! never, ever, ever, set # work_mem to be more than 16mb, it will # cause OOM! Which then gets transformed to: # warning! never, ever, ever, set work_mem = 12mb # cause OOM! Obviously, these individual cases can be worked around, but as long as we're trying to preserve our historical human-readable-and-documented .conf format *and* allow DBAs to hand-edit and machine-edit the same file, I think we're going to end up writing more corner case code than core implementation. I think an include approach would be a lot cleaner and less prone to issues. --Josh ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] More char()/ascii()
Gregory Stark [EMAIL PROTECTED] writes: Andrew Dunstan [EMAIL PROTECTED] writes: Fixing it would be trivial, I'm sure, but is it really a problem? The char data type which I was mistakenly using is enough of a wart that it probably doesn't matter what we do with it. There aren't any security holes with the current behaviour (I don't think). The char type seems to be partly intended to serve as a poor man's int1 --- at one time it even had arithmetic operators, if memory serves. So we shouldn't disallow zero or mess with the fact that it's a signed rather than unsigned byte. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 01:27:25PM -0500, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: The problem I've constantly run into with parsing and modifying settings in a user-edited postgresql.conf file is that sometimes users do their own chronological documentation: [snip] Yeah, those are good examples. It would be fairly easy to deal with a postgresql.conf file that's in a pristine state, but I can see that distinguishing commented-out values from actual comments is likely to be AI-complete :-( Right, this is one of the reasons for the suggestion to use a separate include file where the user isn't supposed to be editing it manually at all. Obviously, these individual cases can be worked around, but as long as we're trying to preserve our historical human-readable-and-documented .conf format *and* allow DBAs to hand-edit and machine-edit the same file, I think we're going to end up writing more corner case code than core implementation. I think an include approach would be a lot cleaner and less prone to issues. I'm starting to wonder why any of this proposal is a good idea at all. We already have sufficient support for someone to suck out the postgresql.conf file, edit it remotely, and put it back, so the argument that this will enable remote administration that you can't do now is entirely bogus. I don't see what it will buy us that is worth the problems it will create. For the point-and-drool crowd that can't cope with editing a text file, perhaps the best avenue to having a GUI is to build it atop the just-mentioned facility, namely 1. suck out the current settings. 2. provide a GUI that manipulates the values. 3. write back an entirely new postgresql.conf that doesn't take any trouble to preserve what was there before. That's what we have now, and it basically forces each frontend to do the implementatino themselevs. E.g. pgadmin has one implementation, phppgadmin has another implementation, apparantly Greg has one implementation, there may be third party ones out there with their own implementation. The point is we need one implementatino that's in the server, because that takes away redundancy and it makes it easier to maintain. //Magnus ---(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: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 20 Feb 2008 13:27:25 -0500 Tom Lane [EMAIL PROTECTED] wrote: For the point-and-drool crowd that can't cope with editing a text *ahem* I am far form a point and drool person and I am telling you: SET PERMANENTLY work_mem TO 65MB ; Is a heck of a lot more sane than editing a text file. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvHUcATb/zqfZUUQRAmbYAJ9oZ6BZrAHNVdzk/Jf8feiB5NDdBQCdEXWs iJqE5FqoQuZ5NJdVpTT6a94= =pnaR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Feb 20, 2008 at 01:27:25PM -0500, Tom Lane wrote: For the point-and-drool crowd that can't cope with editing a text file, perhaps the best avenue to having a GUI is to build it atop the just-mentioned facility, namely 1. suck out the current settings. 2. provide a GUI that manipulates the values. 3. write back an entirely new postgresql.conf that doesn't take any trouble to preserve what was there before. That's what we have now, and it basically forces each frontend to do the implementatino themselevs. E.g. pgadmin has one implementation, phppgadmin has another implementation, apparantly Greg has one implementation, there may be third party ones out there with their own implementation. The point is we need one implementatino that's in the server, because that takes away redundancy and it makes it easier to maintain. The main part of that is the GUI, which is certainly not going to be in the server, so I fail to see exactly what you think you're really gaining. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
Josh Berkus [EMAIL PROTECTED] writes: The problem I've constantly run into with parsing and modifying settings in a user-edited postgresql.conf file is that sometimes users do their own chronological documentation: [snip] Yeah, those are good examples. It would be fairly easy to deal with a postgresql.conf file that's in a pristine state, but I can see that distinguishing commented-out values from actual comments is likely to be AI-complete :-( Obviously, these individual cases can be worked around, but as long as we're trying to preserve our historical human-readable-and-documented .conf format *and* allow DBAs to hand-edit and machine-edit the same file, I think we're going to end up writing more corner case code than core implementation. I think an include approach would be a lot cleaner and less prone to issues. I'm starting to wonder why any of this proposal is a good idea at all. We already have sufficient support for someone to suck out the postgresql.conf file, edit it remotely, and put it back, so the argument that this will enable remote administration that you can't do now is entirely bogus. I don't see what it will buy us that is worth the problems it will create. For the point-and-drool crowd that can't cope with editing a text file, perhaps the best avenue to having a GUI is to build it atop the just-mentioned facility, namely 1. suck out the current settings. 2. provide a GUI that manipulates the values. 3. write back an entirely new postgresql.conf that doesn't take any trouble to preserve what was there before. regards, tom lane ---(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] Permanent settings
Josh Berkus [EMAIL PROTECTED] writes: 2) allow *commenting* of pg_settings / SET PERMANENT. Thus: SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM'; Ugh :-( I think that putting this into SET is a pretty bad idea in any case. SET is, and always has been, a session-local operation. Providing a secondary option that transforms it into something completely different doesn't seem to me to be good design. If we do anything along this line it should be some other syntax --- and really a specialized function will serve the purpose just fine. The other stuff you suggest is even more lily-gilding, not to mention completely pointless unless we were to make this function the *only* way that the settings could be changed. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
* Joshua D. Drake [EMAIL PROTECTED] [080220 13:43]:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 20 Feb 2008 13:27:25 -0500
Tom Lane [EMAIL PROTECTED] wrote:
For the point-and-drool crowd that can't cope with editing a text
*ahem*
I am far form a point and drool person and I am telling you:
SET PERMANENTLY work_mem TO 65MB ;
Is a heck of a lot more sane than editing a text file.
I think the first step is really for some people to show code that
rewrites the config file changing a setting reliably and correctly.
Once we have people comfortable with it rewriting the file, the
bikeshedding can start as to how to use it through the SQL interface.
But, until there's code out there...
bikeshedding
But as Tom said, that's *really* changing what SET has tradionally
been.
Why is a function something like this not sufficient:
pg_save_setting('work_mem', '65MB', 'comment so I remeber')
or,
pg_save_setting('work_mem', '65MB')
or even
pg_save_setting('work_mem')
Since it's a function:
1) It's implementable by anybody, in any fashion
2) It's implemtation is easily replacable by anyone, in any fashion
3) It's easily backportable to adminpack/8.3/8.2/8.1 for those who want
it
/bikeshedding
a.
--
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED] command like a king,
http://www.highrise.ca/ work like a slave.
signature.asc
Description: Digital signature
Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 20 Feb 2008 13:55:05 -0500
Aidan Van Dyk [EMAIL PROTECTED] wrote:
Once we have people comfortable with it rewriting the file, the
bikeshedding can start as to how to use it through the SQL
interface.
But, until there's code out there...
bikeshedding
But as Tom said, that's *really* changing what SET has
tradionally been.
Why is a function something like this not sufficient:
pg_save_setting('work_mem', '65MB', 'comment so I
remeber') or,
pg_save_setting('work_mem', '65MB')
or even
pg_save_setting('work_mem')
Since it's a function:
1) It's implementable by anybody, in any fashion
2) It's implemtation is easily replacable by anyone, in any
fashion 3) It's easily backportable to adminpack/8.3/8.2/8.1 for
those who want it
/bikeshedding
I am not opposed to the above. I was trying to make a point about the
ignorance of the point and drool crowd statement.
Sincerely,
Joshua D. Drake
- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHvHkKATb/zqfZUUQRAo2TAJsEGcTKCSaM7klq4KFYtuFT035nAwCfeAIr
KfaC5FrE3jSZ0V10eX8LOME=
=BUPQ
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 01:55:05PM -0500, Aidan Van Dyk wrote:
* Joshua D. Drake [EMAIL PROTECTED] [080220 13:43]:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 20 Feb 2008 13:27:25 -0500
Tom Lane [EMAIL PROTECTED] wrote:
For the point-and-drool crowd that can't cope with editing a text
*ahem*
I am far form a point and drool person and I am telling you:
SET PERMANENTLY work_mem TO 65MB ;
Is a heck of a lot more sane than editing a text file.
I think the first step is really for some people to show code that
rewrites the config file changing a setting reliably and correctly.
But what we're donig now is discussing *how to do that*, no?
Once we have people comfortable with it rewriting the file, the
bikeshedding can start as to how to use it through the SQL interface.
But, until there's code out there...
bikeshedding
But as Tom said, that's *really* changing what SET has tradionally
been.
Why is a function something like this not sufficient:
pg_save_setting('work_mem', '65MB', 'comment so I remeber')
or,
pg_save_setting('work_mem', '65MB')
or even
pg_save_setting('work_mem')
Since it's a function:
1) It's implementable by anybody, in any fashion
2) It's implemtation is easily replacable by anyone, in any fashion
3) It's easily backportable to adminpack/8.3/8.2/8.1 for those who want
it
/bikeshedding
I for one am perfectly fine with a function instead of a parameter to SET.
Because it's less invasive, and because of your argumen 3 above.
//Magnus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 01:43:46PM -0500, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Feb 20, 2008 at 01:27:25PM -0500, Tom Lane wrote: For the point-and-drool crowd that can't cope with editing a text file, perhaps the best avenue to having a GUI is to build it atop the just-mentioned facility, namely 1. suck out the current settings. 2. provide a GUI that manipulates the values. 3. write back an entirely new postgresql.conf that doesn't take any trouble to preserve what was there before. That's what we have now, and it basically forces each frontend to do the implementatino themselevs. E.g. pgadmin has one implementation, phppgadmin has another implementation, apparantly Greg has one implementation, there may be third party ones out there with their own implementation. The point is we need one implementatino that's in the server, because that takes away redundancy and it makes it easier to maintain. The main part of that is the GUI, which is certainly not going to be in the server, so I fail to see exactly what you think you're really gaining. The way things are now, writing the GUI is *simple* compared to the fact that you have to write a config file parser. One for each tool. The gain is exactly what I said above: we only need one implementation, not one for each potential tool using it, and the maintenance is easier should we ever decide to change how the config files are handled. //Magnus ---(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: [HACKERS] Permanent settings
* Magnus Hagander [EMAIL PROTECTED] [080220 14:03]: I think the first step is really for some people to show code that rewrites the config file changing a setting reliably and correctly. But what we're donig now is discussing *how to do that*, no? Sort of, but of course, we're getting caught up in extra syntactic stuff.. If someone *is* writing this config-rewriter now, these are the types of quesitons I think they need to be asking, some of which have been touched on, some not. But I think a first cut could pick any answer for them, and still be easily adaptable... 1) What file to we rewrite? Main one, or some other specified one? 2) Do we follow includes to find our setting? 3) Which setting do we change, the 1st, or last found in the config file? 4) What do we do about comments *on the same line* as the setting we're changing (I'm assuming all other lines won't be touched) 5) How do we want to handle errors like ENOSPC, or EPERM (I'm assuming of course that the file rewrite will be a tmp+rename, not a trunc+write) 6) Do we want to distinguish between restart only settings, and reloadable settings, and if so, how? a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] CVS repository invalid revision
On Mon, Feb 18, 2008 at 03:13:24PM -0300, Alvaro Herrera wrote: Christian Robottom Reis wrote: I'm running a conversion of PostgreSQL's CVS repository, but I'm stuck on a revision that cscvs fails to parse. The hint that the error gives me is: Parser error: failed to parse revision data line (line: 'date: 2000/12/04 01:20:38; author: tgl; state: Exp; lines: ') That's the literal output -- I'm not sure what the linebreak after lines: means. Can someone help me out by finding and fixing the corrupted revision? Id be most grateful. There's no corrupted revision -- the text you see is part of a log message, not a real header line. So the cscvs tool would seem to need to be able to cope with that. The complete entry (this is from contrib/pgcrypto/md5.c, but there are several more files touched by this commit) is this: revision 1.3 date: 2001-01-09 13:07:13 -0300; author: momjian; state: Exp; lines: +16 -16; The KAME files md5.* and sha1.* have the following changelog entry: revision 1.2 date: 2000/12/04 01:20:38; author: tgl; state: Exp; lines: +18 -18 Eliminate some of the more blatant platform-dependencies ... it builds here now, anyway ... Wow, that's very interesting. Thanks for pointing it out; now that I have this in hand, I need to chase somebody interested in fixing a cscvs bug wink -- Christian Robottom Reis | http://async.com.br/~kiko/ | [+55 16] 3376 0125 ---(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
[HACKERS] Which MemoryContext?
Hi, I want to keep an array of localized strings in memory. This array is dynamically allocated and is going to be used between transactions (that are not necessarily nested). It must be cleaned/freed when postmaster exists. In which context should this array be initialized? TopMemoryContext perhaps? Any thoughts? Regards, Gevik. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CVS repository invalid revision
Christian Robottom Reis [EMAIL PROTECTED] writes: On Mon, Feb 18, 2008 at 03:13:24PM -0300, Alvaro Herrera wrote: There's no corrupted revision -- the text you see is part of a log message, not a real header line. So the cscvs tool would seem to need to be able to cope with that. Wow, that's very interesting. Thanks for pointing it out; now that I have this in hand, I need to chase somebody interested in fixing a cscvs bug wink I notice that cvsweb doesn't handle it very gracefully, either. It looks to me like it's just about impossible for anything that is parsing cvs log output to tell the difference between this and a genuine revision entry header, which means it's probably breaking most of the other conversion tools too. Perhaps it'd be worth hacking the CVS repository entries to modify these log entries a bit? Indenting the quoted revision entry a few spaces would probably do it. According to http://archives.postgresql.org/pgsql-committers/2001-01/msg00115.php the affected files are contrib/pgcrypto/ md5.c md5.h pgcrypto.c pgcrypto.h sha1.c sha1.h regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Feb 20, 2008 at 01:43:46PM -0500, Tom Lane wrote: The main part of that is the GUI, which is certainly not going to be in the server, so I fail to see exactly what you think you're really gaining. The way things are now, writing the GUI is *simple* compared to the fact that you have to write a config file parser. One for each tool. No you don't. All you need is the output of the pg_settings view. Or at least, if that's insufficient, let's discuss exactly how. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Feb 20, 2008 at 01:43:46PM -0500, Tom Lane wrote: The main part of that is the GUI, which is certainly not going to be in the server, so I fail to see exactly what you think you're really gaining. The way things are now, writing the GUI is *simple* compared to the fact that you have to write a config file parser. One for each tool. No you don't. All you need is the output of the pg_settings view. Or at least, if that's insufficient, let's discuss exactly how. I can read the settings. How do I write them, if the only interface to write them is to deal with the file as a complete unit? I was certainly planning to use the output of the pg_settings view to read the data. pgadmin today uses the config file, which is one reason it sucks :-) (because it basically presents the entire config file as a remote text-file editor to the user, and that's not what the user wants) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Permanent settings
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: No you don't. All you need is the output of the pg_settings view. Or at least, if that's insufficient, let's discuss exactly how. I can read the settings. How do I write them, if the only interface to write them is to deal with the file as a complete unit? You write the file as a unit --- what's the problem? We already agreed that the GUIs would not be trying to preserve comments in the file. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: No you don't. All you need is the output of the pg_settings view. Or at least, if that's insufficient, let's discuss exactly how. I can read the settings. How do I write them, if the only interface to write them is to deal with the file as a complete unit? You write the file as a unit --- what's the problem? We already agreed that the GUIs would not be trying to preserve comments in the file. Well, I have to parse the file, and figure out where to have the setting. And if there are multiple configuration files, I have to parse multiple configuration files. And phppgadmin has to implement the exact same parser. As will insert third party app here. And people like JD who want such a feature *even though they may not be using the GUI* are left with nothing. (no, he's not the only one) The point was exactly to move that parsing to the backend. If we're fine with GUIs messing up the comments, then we can just have those functions in the backend and be fine with them messing up the comments. //Magnus ---(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
[HACKERS] Why does pg_ctl.c have its own copy of PM_VERSIONSTR?
Why isn't pg_ctl.c using PG_VERSIONSTR from miscadmin.h? Seems like this is something that will have to be kept in sync manually (I see from the CVS logs that Peter already had to do that once). If the feeling is that miscadmin.h is a backend include file that shouldn't be used by pg_ctl (which is not totally unreasonable, though I think there's no harm in it given the current contents of that file), maybe we should move PG_VERSIONSTR someplace else? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Why does pg_ctl.c have its own copy of PM_VERSIONSTR?
Tom Lane wrote: Why isn't pg_ctl.c using PG_VERSIONSTR from miscadmin.h? Seems like this is something that will have to be kept in sync manually (I see from the CVS logs that Peter already had to do that once). If the feeling is that miscadmin.h is a backend include file that shouldn't be used by pg_ctl (which is not totally unreasonable, though I think there's no harm in it given the current contents of that file), maybe we should move PG_VERSIONSTR someplace else? Your opinion of my memory at nearly 4 years remove is way too high :-) I don't even know if that was my doing or Bruce's - probably mine though. It looks like I adapted it from initdb.c - that would make sense given the time frames. There is unlikely to be any deep reason. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Which MemoryContext?
Gevik Babakhani wrote: I want to keep an array of localized strings in memory. This array is dynamically allocated and is going to be used between transactions (that are not necessarily nested). It must be cleaned/freed when postmaster exists. In which context should this array be initialized? TopMemoryContext perhaps? TopMemoryContext sounds right. Be careful not to leak there. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 7:34 PM, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus [EMAIL PROTECTED] writes: 2) allow *commenting* of pg_settings / SET PERMANENT. Thus: SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM'; Ugh :-( I think that putting this into SET is a pretty bad idea in any case. SET is, and always has been, a session-local operation. Providing a secondary option that transforms it into something completely different I think that's valid argument. We already have ALTER USER foo SET bar = baz, so why not something like: ALTER CLUSTER SET shared_buffers TO '2GB'; ...perhaps with some other word than CLUSTER? Regards, Dawid ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Why does pg_ctl.c have its own copy of PM_VERSIONSTR?
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Why isn't pg_ctl.c using PG_VERSIONSTR from miscadmin.h? It looks like I adapted it from initdb.c - that would make sense given the time frames. Oh, I hadn't seen the one in initdb.c. That's just plain silly, seeing that it's including miscadmin.h already. (Which means that non-backend inclusion of this file works already...) There is unlikely to be any deep reason. OK, will fix. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CVS repository invalid revision
Being prodded by this, I decided to take another look at why cvsps doesn't work on PostgreSQL CVS repository... It happens that it's choking on a bunch of the various REL7_1 tags... And, the fromcvs converter, which works fine, just happens to *not* support importing tags - go figure ;-) So, my solution - strip them all out, and low-and-behold, cvsps works (and thus, so does git-cvsimport). So, for people having problems with the cvs repository, try and eliminate the REL7_1* tags, and see if the problems go away... Here's my ugly 30 second perl to remove the REL7_* tags... Of course, git-cvsimport (with tags stripped) takes hours to convert the repository, rather than the couple 10s of minutes for fromcvs, but at least it works... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Getting available options
On Tue, 2008-02-19 at 14:22 +0100, Magnus Hagander wrote: I'd like to add a way for a client (in this case, typicallyi pgadmin, phppgadmin or similar) to get the available options on the server for the GUC settings we have that take fixed options. This is to make it possible to generate pretty dropdown lists of the options. How will this work with custom variable classes? Seems fairly important not to diminish the extensibility of the server by making those options more difficult to set. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CVS repository invalid revision
Aidan Van Dyk wrote: And, the fromcvs converter, which works fine, just happens to *not* support importing tags - go figure ;-) Meaning it chokes on the $PostgreSQL: ...$ stuff? or what? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CVS repository invalid revision
On Wed, Feb 20, 2008 at 05:00:14PM -0500, Aidan Van Dyk wrote: So, for people having problems with the cvs repository, try and eliminate the REL7_1* tags, and see if the problems go away... I'm currently trying an import off Subversion. Is there a copy of the CVS repo somewhere I can rsync, if that doesn't work? -- Christian Robottom Reis | http://async.com.br/~kiko/ | [+55 16] 3376 0125 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CVS repository invalid revision
* Andrew Dunstan [EMAIL PROTECTED] [080220 17:11]: Aidan Van Dyk wrote: And, the fromcvs converter, which works fine, just happens to *not* support importing tags - go figure ;-) Meaning it chokes on the $PostgreSQL: ...$ stuff? or what? $something$ are keywords in CVS speak. Tags are cvs tag type operations that put human labels on a specific set of files (like REL7_1BETA2, etc). It's the cvs tags, not keywords that are causing cvsps the pain (and thus git cvsimport). fromcvs doesn't try and import tags at all, so it happens to avoid that that tag problem completely. I haven't tried to figure out exactly what tag it is, or what file(s) the tags are problematic on yet, because it takes repeated munging/cvsps runs to figure out, and I haven' had the time yet... But just blindkly removing all the REL7_1* tags worked for me... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] CVS repository invalid revision
Christian Robottom Reis wrote: On Wed, Feb 20, 2008 at 05:00:14PM -0500, Aidan Van Dyk wrote: So, for people having problems with the cvs repository, try and eliminate the REL7_1* tags, and see if the problems go away... I'm currently trying an import off Subversion. Is there a copy of the CVS repo somewhere I can rsync, if that doesn't work? rsync anoncvs.postgresql.org::pgsql-cvs /path/to/mirror cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] CVS repository invalid revision
Christian Robottom Reis wrote: I'm currently trying an import off Subversion. Is there a copy of the CVS repo somewhere I can rsync, if that doesn't work? Sure: http://www.postgresql.org/docs/8.3/interactive/rsync.html -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] CVS repository invalid revision
* Christian Robottom Reis [EMAIL PROTECTED] [080220 17:32]: On Wed, Feb 20, 2008 at 05:00:14PM -0500, Aidan Van Dyk wrote: So, for people having problems with the cvs repository, try and eliminate the REL7_1* tags, and see if the problems go away... I'm currently trying an import off Subversion. Is there a copy of the CVS repo somewhere I can rsync, if that doesn't work? anoncvs allows rsync: rsync -qavzCH --delete anoncvs.postgresql.org::pgsql-cvs PostgreSQL/ -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] CVS repository invalid revision
* Aidan Van Dyk [EMAIL PROTECTED] [080220 17:00]:
It happens that it's choking on a bunch of the various REL7_1 tags...
And, the fromcvs converter, which works fine, just happens to *not*
support importing tags - go figure ;-)
So, my solution - strip them all out, and low-and-behold, cvsps works
(and thus, so does git-cvsimport).
So, for people having problems with the cvs repository, try and
eliminate the REL7_1* tags, and see if the problems go away...
Here's my ugly 30 second perl to remove the REL7_* tags...
I see I forgot to include it.
I've found that it's only REL7_1_BETA[23] that cause the problem...
And it seems like both those tags are taging various files from various
points in time, and such a way that cvsps loops forever...
Here' my perl to fix the repository and allow cvsps to work:
It's ugly, and I make no guarantee that it doesn't corrupt some binary
file somewhere...
#!/usr/bin/perl
my @TAGS = qw(REL7_1_BETA2 REL7_1_BETA3);
for my $file (@ARGV)
{
my $i, $o;
my @sb = stat($file);
printf %s: %d\n, $file, $sb[7];
open(INPUT, :raw, $file) || die Couldn't open $file: $!\n;
$i = sysread INPUT, $buf, $sb[7];
close INPUT;
for my $tag (@TAGS)
{
$buf =~ s/ $tag:[.0-9]*\n//es;
$buf =~ s/\n$tag:[.0-9]*;\n/;\n/es;
}
open OUTPUT, :raw, $file.new;
$o = syswrite OUTPUT, $buf;
close OUTPUT;
printf IN: %d OUT: %d\n, $i, $o;
rename ($file.new, $file) || die Couldn't rename: $!\n;
}
Run it over the repository like:
find CVSROOT/pgsql -name \*,v | xargs perl parse.pl
YMMV...
a.
--
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED] command like a king,
http://www.highrise.ca/ work like a slave.
signature.asc
Description: Digital signature
Re: [HACKERS] Getting available options
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2008-02-19 at 14:22 +0100, Magnus Hagander wrote: I'd like to add a way for a client (in this case, typicallyi pgadmin, phppgadmin or similar) to get the available options on the server for the GUC settings we have that take fixed options. This is to make it possible to generate pretty dropdown lists of the options. How will this work with custom variable classes? Anything like this will involve an API change in the guc stuff, so that doesn't seem insurmountable. I like the idea that was mentioned upthread of creating a new enum category for GUC variables, instead of continuing to abuse the rather inefficient string category for the purpose. One reason is that we then would not be faced with making an incompatible change in the arguments of DefineCustomStringVariable. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Which MemoryContext?
TopMemoryContext sounds right. Be careful not to leak there. Thank you :) I have allocated memory using: MemoryContextAlloc(TopMemoryContext,n * sizeof(char*)); In pgsql/src/backend/utils/mmgr/README:142 is stated that memory allocated using above should be freed manually, Is this correct? Or does the system release everything allocated in TopMemoryContext automatically when exiting? I looked around and found examples where memory allocated using above is not freed! (datetime.c:3811, uhhh.. a bit confused here) Any thoughts? Regards, Gevik. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
All, I think we're failing to discuss the primary use-case for this, which is one reason why the solutions aren't obvious. And that use case is: multi-server management. PostgreSQL is *easy* to manage on one server. For a single server, the existing text file editor GUIs are clunky but good enough. However, imagine you're adminning 250 PostgreSQL servers backing a social networking application. You decide the application needs a higher default sort_mem for all new connections, on all 250 servers. How, exactly, do you deploy that? Worse, imagine you're an ISP and you have 250 *differently configured* PostgreSQL servers on vhosts, and you need to roll out a change in logging destination to all machines while leaving other settings untouched. We need a server-based tool for the manipulating postgresql.conf, and one which is network-accessable, allows updating individual settings, and can be plugged into 3rd-party server management tools. This goes for pg_hba.conf as well, for the same reasons. If we want to move PostgreSQL into larger enterprises (and I certainly do) we need to make it more manageable. Now, none of this requires managing the settings via the SQL command line. Since we need to make it network-accessable, though, that seems the easiest route. Otherwise, we'd have to set up a daemon running on a 2nd port. P.S. I don't care what the syntax is. Josh Berkus PostgreSQL @ Sun San Francisco 415-752-2500 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
Aidan Van Dyk [EMAIL PROTECTED] writes: * Josh Berkus [EMAIL PROTECTED] [080220 18:00]: We need a server-based tool for the manipulating postgresql.conf, and one which is network-accessable, allows updating individual settings, Do we need to develop our own set of remote management tools/systems, or possibly document some best practices using already available multi- server managment tools? Indeed. If Josh's argument were correct, why isn't every other daemon on the planet moving away from textual configuration files? IIRC, one of the arguments for the config include-file facility was to simplify management of multiple servers by letting them share part or all of their configuration data. One of the things that bothers me considerably about all the proposals made so far in this thread (including mine) is that they don't play very nicely with such a scenario. Putting a setting into one file that contradicts one made in some other file is a recipe for confusion and less admin-friendliness, not more. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
Aidan Van Dyk wrote: -- Start of PGP signed section. * Josh Berkus [EMAIL PROTECTED] [080220 18:00]: All, I think we're failing to discuss the primary use-case for this, which is one reason why the solutions aren't obvious. However, imagine you're adminning 250 PostgreSQL servers backing a social networking application. You decide the application needs a higher default sort_mem for all new connections, on all 250 servers. How, exactly, do you deploy that? Worse, imagine you're an ISP and you have 250 *differently configured* PostgreSQL servers on vhosts, and you need to roll out a change in logging destination to all machines while leaving other settings untouched. But, from my experience, those are pretty much solved, with things like rsync, SCM (pick your favourite) and tools like clusterssh, multixterm, rancid, wish, expect, etc. Agreed. Put postgresql.conf on an NFS server and restart the servers. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
* Josh Berkus [EMAIL PROTECTED] [080220 18:00]: All, I think we're failing to discuss the primary use-case for this, which is one reason why the solutions aren't obvious. However, imagine you're adminning 250 PostgreSQL servers backing a social networking application. You decide the application needs a higher default sort_mem for all new connections, on all 250 servers. How, exactly, do you deploy that? Worse, imagine you're an ISP and you have 250 *differently configured* PostgreSQL servers on vhosts, and you need to roll out a change in logging destination to all machines while leaving other settings untouched. But, from my experience, those are pretty much solved, with things like rsync, SCM (pick your favourite) and tools like clusterssh, multixterm, rancid, wish, expect, etc. I would have thought that any larger enterprise was familiar with these approaches, and are probably using them already to manage/configure there general unix environments We need a server-based tool for the manipulating postgresql.conf, and one which is network-accessable, allows updating individual settings, and can be plugged into 3rd-party server management tools. This goes for pg_hba.conf as well, for the same reasons. If we want to move PostgreSQL into larger enterprises (and I certainly do) we need to make it more manageable. Do we need to develop our own set of remote management tools/systems, or possibly document some best practices using already available multi- server managment tools? -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Which MemoryContext?
Gevik Babakhani wrote: I have allocated memory using: MemoryContextAlloc(TopMemoryContext,n * sizeof(char*)); In pgsql/src/backend/utils/mmgr/README:142 is stated that memory allocated using above should be freed manually, Is this correct? Or does the system release everything allocated in TopMemoryContext automatically when exiting? On backend exit, everything in TopMemoryContext, like all other non-shared memory, is automatically released. I looked around and found examples where memory allocated using above is not freed! (datetime.c:3811, uhhh.. a bit confused here) That palloc'd table in datetime.c is kept until backend exit, or until it's replaced with a new table. If it's replaced with a new table, the old one is explicitly pfree'd in that function: /* Now safe to replace existing table (if any) */ if (timezonetktbl) pfree(timezonetktbl); -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 20 Feb 2008 18:38:09 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Agreed. Put postgresql.conf on an NFS server and restart the servers. Bruce, that is insane. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvL0AATb/zqfZUUQRAvlIAKCXvceixsK18qN1xNDEzMuvFjVjggCfXZMd R7mCktmpp/RcNg4XPxOaPi4= =zVm4 -END PGP SIGNATURE- ---(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: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 20 Feb 2008 18:38:10 -0500 Tom Lane [EMAIL PROTECTED] wrote: Aidan Van Dyk [EMAIL PROTECTED] writes: * Josh Berkus [EMAIL PROTECTED] [080220 18:00]: We need a server-based tool for the manipulating postgresql.conf, and one which is network-accessable, allows updating individual settings, Do we need to develop our own set of remote management tools/systems, or possibly document some best practices using already available multi- server managment tools? Indeed. If Josh's argument were correct, why isn't every other daemon on the planet moving away from textual configuration files? I believe the more correct argument would be to look at how our competition is doing this, and perhaps learn from them. How does Oracle, MSSQL, and DB2 handle this? Yes I purposely left out the dolphin tamers. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvLzsATb/zqfZUUQRAr0WAJ4gkYww0pBzC7ZzwdZZI0E6oLEaqgCfc1gm MOpFjuKHJ9sX20rJLfrXNOQ= =hjk0 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Which MemoryContext?
On backend exit, everything in TopMemoryContext, like all other non-shared memory, is automatically released. So it is safe to not free the allocated memory in TopMemoryContext and leave it to be released on backend exit. Thank you for the help :) Regards, Gevik. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
Tom Lane [EMAIL PROTECTED] writes: Andrew Dunstan [EMAIL PROTECTED] writes: All this discussion seems to me to be going off into the clouds, where every objection is met with some still more elaborate scheme. I think we need to look at simple, incremental, and if possible backwards compatible changes. +1. Let me propose the simplest possible scheme, namely The SQL-exposed function knows how to find and replace the definition of a variable (commented or otherwise) in the primary configuration file. It does not chase INCLUDEs. If it doesn't find the target variable anyplace in the primary file, it errors out. I think there are a few problems with having the function edit the primary config file: 1) It requires parsing and dealing with arbitrary user data. There could be comments on the same line, the order or white-space might be important to the user, etc. 2) How would this interact with config files outside of the data directory? If you have multiple postgres clusters using the same config fie or if your config file is in read-only media (as /etc often is) or if you're a packager where editing user-maintained /etc files is a forbidden and an awful idea this all leads to problems. I think it's much cleaner to have a postgresql.conf.auto file in the data directory which has a limited syntax. No comments, no extra white-space, and no includes. The user is not expected to edit it, though he can. The functions edit it using simple algorithms which add and remove single lines. The default config file then includes this postgresql.conf.auto and the sysadmin can decide whether to keep or remove that include, change configuration options before or after the include, etc. Actually this is very similar to how a lot of other packages manage their automatically maintained data. Apache used to be done like this on Debian (now it's a bit more complex using a directory, but the same idea). Emacs's custom.el package can be set up in a similar way where custom.el edits a separate file which you include from your .emacs. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication 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: [HACKERS] Permanent settings
Tom Lane wrote: Aidan Van Dyk [EMAIL PROTECTED] writes: * Josh Berkus [EMAIL PROTECTED] [080220 18:00]: We need a server-based tool for the manipulating postgresql.conf, and one which is network-accessable, allows updating individual settings, Do we need to develop our own set of remote management tools/systems, or possibly document some best practices using already available multi- server managment tools? Indeed. If Josh's argument were correct, why isn't every other daemon on the planet moving away from textual configuration files? IIRC, one of the arguments for the config include-file facility was to simplify management of multiple servers by letting them share part or all of their configuration data. One of the things that bothers me considerably about all the proposals made so far in this thread (including mine) is that they don't play very nicely with such a scenario. Putting a setting into one file that contradicts one made in some other file is a recipe for confusion and less admin-friendliness, not more. If you're interested in comments from the peanut gallery, we run hundreds of instances of nearly equal numbers of oracle, sql server, postgres, mysql. IMHO oracle has the most polish here, with its pfile/spfile business (excluding listener management, which is still pretty primitive, esp compared to the equivalent of what pg_hba.conf offers). SQL Server is close, with the internal table sysconfigures, but some things do get stashed in the registry. You can programatically edit this across the network, so it's not so bad. For postgres and mysql, we make mass changes by being able to mass distribute a postgresql.conf or my.cnf patch script, which is usually sed/awk/perl-ish in nature, and then running this en masse from a host setup with a trusted ssh key that can look through the servers list and call the patch script on each host (and for each instance that might be on that host). The config files get auto-checked into a SCM from there. So it's very much as Aidan described. To date, this approach has worked without any problems. In our case, there is a very uniform layout for everything, which is what makes this work. postgresql.conf/my.cnf start from general templates, there are standard locations for everything, there are shell functions to fetch details about any instance from a master list, etc. So while some team members would be happy if Pg were more Oracle-esque, it's not a *major* issue for us. I can't imagine, though, a so-called enterprise setup where I would be willing to literally share the same config file across instances (via NFS or whatever). Seriously, that's just not done. Someone may do that for a few or even a dozen, but not on the scale Josh is talking about. Further, while every daemon may not be moving away from text config files, most every database is? Certainly if our environment grew out faster than we were able to consolidate it and develop a sensible, structured approach, I could imagine we would be in a world of hurt. The oracle approach seems to me close to the dba's ideal. I can generate a text representation from the running instance or binary representation, modify the running instance and/or binary representation from the text version, find the locations of these programmatically, and do all this via sqlplus or whatever I want, across the network. If we mass deploy a pile of servers, we're not likely to comment different settings for each instance, but rather document in our install docs why we are now setting A to x instead of y across the board. Regards, Paul ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
On Thu, Feb 21, 2008 at 12:02 AM, Josh Berkus [EMAIL PROTECTED] wrote: All, I think we're failing to discuss the primary use-case for this, which is one reason why the solutions aren't obvious. And that use case is: multi-server management. ...and third-party management solutions. PostgreSQL is *easy* to manage on one server. For a single server, the existing text file editor GUIs are clunky but good enough. [...] I tried to ask myself -- what other similar systems do I know and what do they give me. Well, I know Oracle does have a concept of database managed configuration (a SPFILE), and it is preferred set up. If you are using SPFILE, you can issue: ALTER SYSTEM SET foo = 'bar' [ COMMENT = 'comment' ] SCOPE=SPFILE (or MEMORY or BOTH). ...SPFILE means changes take place upon next restart, MEMORY -- they are temporary (though global for the system). At any moment you can switch from one form to the other (CREATE PFILE FROM SPFILE) or vice versa. The idea is that human can edit PFILE, and that SPFILE is database-only (and database can store some extra hints there, if it wishes). OK, so what does it give Oracle? The management solutions use it a lot. You can easily change parameters from them. Combined with monitoring this gives full service solutions, say a PostgreSQL could diisplay a bgwriter statistics, suggest changes to the current settings, and a one click away solution to try them out. Would I like PostgreSQL to have such an option? Yes, having used it on Oracle, I think such an ability is nothing but beneficial (if done right). Regards, Dawid PS: And I think postgres.conf as it is today is one of the nicest application-provided configuration files. :) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The way I intended to do it would indeed allow it to be undone simply by executing 'drop language plpgsql' in template1. Why isn't it enough that administrators can do CREATE LANGUAGE plpgsql in template1? Because people do not have the rights, or the knowledge, or both. I'm glad most packagers are choosing to enable it by default, because it can be a real pain for applications like MediaWiki, which has a point and click GUI installation that is made extraordinarily harder by having to explain: what plpgsql and tsearch2 are, how to install them, what a superuser is, what they should tell their hosting provider, etc. I'm not sure I understand the security implications of turning plpgsql on: has there been some security concerns in the past? Does having access to plpgsql really faciliate an attacker that much above what they might already be capable of without it? It seems quite trivial to write a function in sql that ties up resources just as effectively as plpgsql. +1 on installed by default, in case it wasn't clear from the above. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802202019 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAke80bUACgkQvJuQZxSWSsgH/ACcD2A/BjKqT3DHWsb7ybKWGL0H AEYAoMKcvd+tBhyB4NpFzOMi5nT7Y6zq =dP0/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 20 Feb 2008 09:42:02 -0500
Andrew Dunstan [EMAIL PROTECTED] wrote:
All this discussion seems to me to be going off into the clouds,
where every objection is met with some still more elaborate scheme. I
think we need to look at simple, incremental, and if possible
backwards compatible changes.
The simplest solution I can think of is:
Have a table pg_configuration (pg_settings?).
Allow that table to be inserted into but not updated or deleted from.
Provide functions to manipulate the table perhaps:
select update_settings('shared_memory','64M');
That table is used as the definitive source for building the
postgresql.conf.
The postgresql.conf is pushed to disk each time the system is reloaded
via:
refresh_settings();
Refresh_settings would be called as an initial startup function as
well. So if you did:
pg_ctl -D data start
It would actually do:
pg_ctl -D data start; select update_settings(); pg_ctl -D data
restart;
The reason we only insert is that the function refresh_settings() calls
the max(created) for the setting. That way we can know what previous
settings for the GUC.
Other things could be added such as:
select update_settings('shared_memory','64M','Used to be 16 but we
got more ram');
The one thing this does is make the postgresql.conf basically a
placeholder. It is not definitive anymore, in the sense that settings
will be overwritten on restart. That really isn't that uncommon anyway
in other applications.
Sincerely,
Joshua D. Drake
- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHvN6FATb/zqfZUUQRAqlKAJ0ZHMGSfOBBUVqFGDtsNAw9b04JUgCgiRa4
T4e2P3+NqtVtiFpwPYArdBA=
=Zto3
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: The problem I've constantly run into with parsing and modifying settings in a user-edited postgresql.conf file is that sometimes users do their own chronological documentation: [snip] Yeah, those are good examples. It would be fairly easy to deal with a postgresql.conf file that's in a pristine state, but I can see that distinguishing commented-out values from actual comments is likely to be AI-complete :-( How about if we provide for a magic value of 'default' for every setting? So the model config file would move from lines like this: #port = 5432# (change requires restart) to lines like this: port = default # 5432 (change requires restart) So we'd never uncomment a commented out line. That way we could preserve comments, which would be a Good Thing (tm) Then I think Tom's original proposal suitably modified would make sense. cheers andrew ---(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: [HACKERS] Permanent settings
* Joshua D. Drake [EMAIL PROTECTED] [080220 21:15]: The one thing this does is make the postgresql.conf basically a placeholder. It is not definitive anymore, in the sense that settings will be overwritten on restart. That really isn't that uncommon anyway in other applications. Man, I'ld screem *bloody murder* if the config file we just finished, after spending days (or weeks) of careful analisys and implementation discussion was overwritten by postmaster automatically on server startup... Of course, I'm not quite that dumb - the config file would be checked out of SCM, so it wouldn't be lost, but I certainly wouldn't be happy to have to puzzle why the config file I *just wrote* seems not to be affecting things the way I intended, only to find that the database overwrote it with the old settings it had been using (that were obviously the reason we needed to change the config)... But part of that might just be user education... I personally just can't imagine that education could be enough to let *all* users know that as of version S, postgresql.conf is blatantly ignored, no, more exactly *purposely overwritten* with the old settings... If postgresql.conf is *ever* going to be deprecated as a config file that in *controls* PostgreSQL, then absolutely do *not* leave it around, and screem loudly if postmaster notices that it exists... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Which MemoryContext?
Gevik Babakhani wrote: On backend exit, everything in TopMemoryContext, like all other non-shared memory, is automatically released. So it is safe to not free the allocated memory in TopMemoryContext and leave it to be released on backend exit. All local memory is safe to handle that way. The problem only arises when you have memory to release _earlier_ than that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 20 Feb 2008 23:02:34 -0500 Aidan Van Dyk [EMAIL PROTECTED] wrote: * Joshua D. Drake [EMAIL PROTECTED] [080220 21:15]: The one thing this does is make the postgresql.conf basically a placeholder. It is not definitive anymore, in the sense that settings will be overwritten on restart. That really isn't that uncommon anyway in other applications. Man, I'ld screem *bloody murder* if the config file we just finished, after spending days (or weeks) of careful analisys and implementation discussion was overwritten by postmaster automatically on server startup... And I of course would respond, read the docs :P But part of that might just be user education... I personally just can't imagine that education could be enough to let *all* users know that as of version S, postgresql.conf is blatantly ignored, no, more exactly *purposely overwritten* with the old settings... We could also make it optional. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvPkJATb/zqfZUUQRAqNlAJ972s1p0RvfWabRXOQKkzJvACkEYQCfXrsc IZ18stRvr6NONj0T3wUBpXE= =nWtF -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Batch update of indexes on data loading
This is a proposal of fast data loading using batch update of indexes for 8.4. It is a part of pg_bulkload (http://pgbulkload.projects.postgresql.org/) and I'd like to integrate it in order to cooperate with other parts of postgres. The basic concept is spooling new coming data, and merge the spool and the existing indexes into a new index at the end of data loading. It is 5-10 times faster than index insertion per-row, that is the way in 8.3. One of the problem is locking; Index building in bulkload is similar to REINDEX rather than INSERT, so we need ACCESS EXCLUSIVE LOCK during it. Bulkloading is not a upper compatible method, so I'm thinking about adding a new WITH LOCK option for COPY command. COPY tbl FROM 'datafile' WITH LOCK; If the LOCK option is specified, the behavior of COPY will be changed as follows: 1. Lock the target table in ACCESS EXCLUSIVE mode instead of ROW EXCLUSIVE. 2. Prepare spooler (BTSpool) for each indexes. 3. For each new row, put index entries into the spools (_bt_spool) instead of index_insert. 4. At the end of COPY, merge the spool and the existing indexes into a new index file. The relfilenode of the index is changed like REINDEX. However, there might be better interfaces for bulk index creation. For example, if we want to use it with pgloader, we might need bulkload mode for indexes. pgloader commits every 1 rows, so the index spooler must keep alive until end of the session over transactions. (or end of the transaction over sub-transactions) I'm working toward the simple COPY WITH LOCK approach for now, but if there are other better ideas, I want to use them. Advices and suggestions welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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] ANALYZE to be ignored by VACUUM
Dawid Kuroczko [EMAIL PROTECTED] wrote: I am sure the idea is not original, yet still I would like to know how hard would it be to support local (per table) oldest visible XIDs. I mean, when transaction start you need to keep all tuples with xmin = oldest_xid in all tables, because who knows what table will that transaction like to touch. Per-table oldest XID management sounds good! You mean transactions that touch no tables does not affect vacuums at all, right? If so, the solution can resolve pg_start_backup problem, too. I feel it is enough for standard maintenance commands. Another solution might need for user defined long transactions, though. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
Greg Sabino Mullane [EMAIL PROTECTED] writes: I'm not sure I understand the security implications of turning plpgsql on: has there been some security concerns in the past? Does having access to plpgsql really faciliate an attacker that much above what they might already be capable of without it? It seems quite trivial to write a function in sql that ties up resources just as effectively as plpgsql. I grow weary of repeating this: it's not about resource consumption, nor about potential security holes in plpgsql itself. It's about handing attackers the capability to further exploit *other* security holes. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] VARATT_EXTERNAL_GET_POINTER is not quite there yet
I got around to testing PG 8.3 on HPUX on Itanium (feel free to play
along at www.testdrive.hp.com) ... and was dismayed to find that it
doesn't work. If compiled with HP's C compiler, the regression tests
dump core. Investigation shows that the problem occurs where
tuptoaster.c tries to copy a misaligned toast pointer datum into a
properly aligned local variable: it's using word-wide load and store
instructions to do that copying, which of course does not work on any
architecture that's picky about alignment.
We'd seen this before and tried to fix it by introducing a pointer cast
within VARATT_EXTERNAL_GET_POINTER(), but evidently that's not enough
for some non-gcc compilers.
After much experimentation I was able to get it to work by invoking
memcpy through a function pointer, which seems to be sufficient to
disable this particular compiler's built-in intelligence about memcpy.
I can't say that I find this a nice clean solution; but does anyone have
a better one?
The full patch that I'm thinking of applying is
*** src/backend/access/heap/tuptoaster.c.orig Tue Jan 1 14:53:12 2008
--- src/backend/access/heap/tuptoaster.cWed Feb 20 20:28:13 2008
***
*** 65,72
#define VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr) \
do { \
varattrib_1b_e *attre = (varattrib_1b_e *) (attr); \
! Assert(VARSIZE_ANY_EXHDR(attre) == sizeof(toast_pointer)); \
! memcpy((toast_pointer), VARDATA_EXTERNAL(attre),
sizeof(toast_pointer)); \
} while (0)
--- 65,74
#define VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr) \
do { \
varattrib_1b_e *attre = (varattrib_1b_e *) (attr); \
! void *(*mcopy) (void *dest, const void *src, size_t sz) = memcpy; \
! Assert(VARATT_IS_EXTERNAL(attre)); \
! Assert(VARSIZE_EXTERNAL(attre) ==
sizeof(toast_pointer)+VARHDRSZ_EXTERNAL); \
! mcopy((toast_pointer), VARDATA_EXTERNAL(attre),
sizeof(toast_pointer)); \
} while (0)
(The Assert changes aren't necessary for this particular problem, but
were added after realizing that the original Assert didn't adequately
protect the subsequent use of VARDATA_EXTERNAL. That macro assumes that
the datum has a 1-byte header. I had first thought that the cast to
varattrib_4b * that occurs within one branch of VARSIZE_ANY_EXHDR
might be giving the compiler license to think that the pointer is
word-aligned. After further experimentation I don't think that HP's
compiler thinks so; but some other compiler might, so it seems wise to
get rid of that.)
It's all pretty ugly, but I'm afraid that we're in for shenanigans like
this as compilers get more aggressive about optimization. Has anyone
got a better suggestion?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On 21/02/2008, Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 20 Feb 2008 23:02:34 -0500 Aidan Van Dyk [EMAIL PROTECTED] wrote: * Joshua D. Drake [EMAIL PROTECTED] [080220 21:15]: The one thing this does is make the postgresql.conf basically a placeholder. It is not definitive anymore, in the sense that settings will be overwritten on restart. That really isn't that uncommon anyway in other applications. Man, I'ld screem *bloody murder* if the config file we just finished, after spending days (or weeks) of careful analisys and implementation discussion was overwritten by postmaster automatically on server startup... And I of course would respond, read the docs :P But part of that might just be user education... I personally just can't imagine that education could be enough to let *all* users know that as of version S, postgresql.conf is blatantly ignored, no, more exactly *purposely overwritten* with the old settings... We could also make it optional. Silly point postgresql.conf has a bunch of settings that are needed by the server before it can actually read the database, Sure move out settings that are not needed early in startup but your going to get problems with others. I quite like the function based method its flexible. Allowing pg_settings to be update able does not seam to be a bad idea but then you could do that with triggers and rules that called the functions surly? set should be for temporary transaction and session based variables, not for change permanent things thats what the SQL constructs insert, update, alter, create, delete, drop etc are for. Regards Peter
