[HACKERS] Changing semantics of autovacuum_cost_limit
Hi all, As AUTOVACUUM is having multiple workers now, the semantics of autovacuum_cost_limit also need to be redefined. Currently, autovacuum_cost_limit is the accumulated cost that will cause one single worker vacuuming process to sleep. It is used to restrict the I/O consumption of a single vacuum worker. When there are N workers, the I/O consumption by autovacuum workers can be increased by N times. This autovacuum_cost_limit semantics produces unpredictable I/O consumption for multiple-autovacuum-workers. One simple idea is to set cost limit for every worker to: autovacuum_cost_limit / max_autovacuum_workers. But for scenarios which have fewer active workers, it is obvious unfair to active workers. So a better way is to set cost limit of every active worker to: autovacuum_cost_limit/autovacuum_active_workers. This ensures the I/O consumption of autovacuum is stable. Worker can be extended to have its own cost_limit on share memory. When a worker is brought up or a worker has finished its work, launcher recalculates: worker_cost_limit= (autovacuum_cost_limit/autovacuum_active_workers) and sets new value for each active workers. The above approach requires launcher can change cost delay setting of workers on-the-fly. This can be achieved by forcing VACUUM refers to the cost delay setting in its worker’s share memory every vacuum_delay_point. Any comments or suggestions? Best Regards Galy Lee [EMAIL PROTECTED] NTT Open Source Software Center ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Zoltan Boszormenyi írta: Zdenek Kotala írta: Zoltan Boszormenyi wrote: Hi, we have found that psql in PostgreSQL 8.2.3 has problems connecting to the server running on Solaris 10/Sun SPARC. $ uname -a SunOS dev-machine 5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-V440 It seems that somehow the system provided GCC 3.4.3 miscompiles timestamptz_send() and it segfaults. The default function looks like this: Can you send me how you compiled Postgres (configure switches, LDFLAGS ...) and is possible get core file? This was the configure line: ./configure --prefix=/export/local/postgresql/postgresql-8.2.3 --with-includes=/usr/local/include --with-libraries=/usr/local/lib/ I added --enable-debug --enable-depend --enable-cassert to get sensible gdb report after that. The problem was that the server had problems after psql connected with these commands: $ psql -l -h dev-machine -p 5477 -U user psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. $ psql -h dev-machine -p 5477 -U user template1 psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. If the user doesn't have permissions in e.g. pg_hba.conf then I get the correct permission denied error. If the user can connect then some statement inside psql causes segfault in the server. Compiled with debug info, I got this from gdb on the core file: $ gdb /.../pgsql/bin/postgres /.../data/core ... Program terminated with signal 11, Segmentation fault. #0 0x0021c8a0 in timestamptz_send (fcinfo=0x1) at timestamp.c:461 461 PG_RETURN_BYTEA_P(pq_endtypsend(buf)); (gdb) I described my experiments, compiling with --enable-integer-datetimes fixed the issue. We compiled GCC-4.1.2 on this machine, recompiled PostgreSQL with the new GCC without --enable-integer-datetimes and it fixed the problem we experienced. It seems that my suspicion was right: GCC-3.4.3 on Solaris 10/Sparc is buggy. -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Zoltan Boszormenyi írta: Zoltan Boszormenyi írta: Zdenek Kotala írta: Zoltan Boszormenyi wrote: Hi, we have found that psql in PostgreSQL 8.2.3 has problems connecting to the server running on Solaris 10/Sun SPARC. $ uname -a SunOS dev-machine 5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-V440 It seems that somehow the system provided GCC 3.4.3 miscompiles timestamptz_send() and it segfaults. The default function looks like this: Can you send me how you compiled Postgres (configure switches, LDFLAGS ...) and is possible get core file? This was the configure line: ./configure --prefix=/export/local/postgresql/postgresql-8.2.3 --with-includes=/usr/local/include --with-libraries=/usr/local/lib/ I added --enable-debug --enable-depend --enable-cassert to get sensible gdb report after that. The problem was that the server had problems after psql connected with these commands: $ psql -l -h dev-machine -p 5477 -U user psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. $ psql -h dev-machine -p 5477 -U user template1 psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. If the user doesn't have permissions in e.g. pg_hba.conf then I get the correct permission denied error. If the user can connect then some statement inside psql causes segfault in the server. Compiled with debug info, I got this from gdb on the core file: $ gdb /.../pgsql/bin/postgres /.../data/core ... Program terminated with signal 11, Segmentation fault. #0 0x0021c8a0 in timestamptz_send (fcinfo=0x1) at timestamp.c:461 461 PG_RETURN_BYTEA_P(pq_endtypsend(buf)); (gdb) I described my experiments, compiling with --enable-integer-datetimes fixed the issue. We compiled GCC-4.1.2 on this machine, recompiled PostgreSQL with the new GCC without --enable-integer-datetimes and it fixed the problem we experienced. It seems that my suspicion was right: GCC-3.4.3 on Solaris 10/Sparc is buggy. Oh, and the proof that I use the newly compiled version: $ psql -h reddb-dev-pgr -p 5477 test Welcome to psql 8.2.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# select version(); version PostgreSQL 8.2.3 on sparc-sun-solaris2.10, compiled by GCC gcc (GCC) 4.1.2 (1 row) test=# show integer_datetimes; integer_datetimes --- off (1 row) -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Server-side support of all encodings
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Backend encodings must have the property that all bytes of a multibyte character are = 128. But then, PG_JOHAB have already infringed it. Please see johab_to_utf8.map. Trailing bytes of JOHAB can be less than 128. In that case we must remove JOHAB from the list of allowed server encodings. Tatsuo, can you comment on whether this is correct? Sigh. From the first day when JOHAB was supported (back to 7.3 days), it should had not been in the server encodings. JOHAB's second byte definitely contain 0x41 and above. *johab*.map just reflect the fact. I think we should remove JOHAB from the server encodings list. I'm afraid users who have JOHAB encoded databases get angry, though. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 regression test failures
FWIW, it looks like it failed to reject stopwords. Is it possible you
Right.
I suppose the problem is with '\r\n'... Try attached patch.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
*** ./contrib/tsearch2/stopword.c.orig Mon Mar 26 14:25:16 2007
--- ./contrib/tsearch2/stopword.c Mon Mar 26 14:28:25 2007
***
*** 47,53
while (fgets(buf, sizeof(buf), hin))
{
! buf[strlen(buf) - 1] = '\0';
pg_verifymbstr(buf, strlen(buf), false);
if (*buf == '\0')
continue;
--- 47,57
while (fgets(buf, sizeof(buf), hin))
{
! pbuf = buf;
! while( !isspace( *pbuf ) )
! pbuf++;
! *pbuf = '\0';
!
pg_verifymbstr(buf, strlen(buf), false);
if (*buf == '\0')
continue;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Server-side support of all encodings
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Backend encodings must have the property that all bytes of a multibyte character are = 128. But then, PG_JOHAB have already infringed it. Please see johab_to_utf8.map. Trailing bytes of JOHAB can be less than 128. In that case we must remove JOHAB from the list of allowed server encodings. Tatsuo, can you comment on whether this is correct? Sigh. From the first day when JOHAB was supported (back to 7.3 days), it should had not been in the server encodings. JOHAB's second byte definitely contain 0x41 and above. *johab*.map just reflect the fact. I think we should remove JOHAB from the server encodings list. I'm afraid users who have JOHAB encoded databases get angry, though. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Group Commit
It's been known for years that commit_delay isn't very good at giving us group commit behavior. I did some experiments with this simple test case: BEGIN; INSERT INTO test VALUES (1); COMMIT;, with different numbers of concurrent clients and with and without commit_delay. Summary for the impatient: 1. Current behavior sucks. 2. commit_delay doesn't help with # of clients ~10. It does help with higher numbers, but it still sucks. 3. I'm working on a patch. I added logging to show how many commit records are flushed on each fsync. The output with otherwise unpatched PG head looks like this, with 5 clients: LOG: Flushed 4 out of 5 commits LOG: Flushed 1 out of 5 commits LOG: Flushed 4 out of 5 commits LOG: Flushed 1 out of 5 commits LOG: Flushed 4 out of 5 commits LOG: Flushed 1 out of 5 commits LOG: Flushed 4 out of 5 commits LOG: Flushed 1 out of 5 commits LOG: Flushed 3 out of 5 commits LOG: Flushed 2 out of 5 commits LOG: Flushed 3 out of 5 commits LOG: Flushed 2 out of 5 commits LOG: Flushed 3 out of 5 commits LOG: Flushed 2 out of 5 commits LOG: Flushed 3 out of 5 commits ... Here's what's happening: 1. Client 1 issues fsync (A) 2. Clients 2-5 write their commit record, and try to fsync, but they have to wait for fsync (A) to finish. 3. fsync (A) finishes, freeing client 1. 4. One of clients 2-5 starts the next fsync (B), which will flush commits of clients 2-5 to disk 5. Client 1 begins new transaction, inserts commit record and tries to fsync. Needs to wait for previous fsync (B) to finish 6. fsync B finishes, freeing clients 2-5 7. Client 1 issues fsync (C) 8. ... The 2-3-2-3 pattern can be explained with similar unfortunate resonance, but with two clients instead of client 1 in the above possibly running in separate cores (test was run on a dual-core laptop). I also draw a diagram illustrating the above, attached. I wrote a quick dirty patch for this that I'm going to refine further, but wanted to get the results out for others to look at first. I'm not posting the patch yet, but it basically adds some synchronization to the WAL flushes. It introduces a counter of inserted but not yet flushed commit records. Instead of the commit_delay, the counter is checked. If it's smaller than NBackends, the process waits until count reaches NBackends, or a timeout expires. There's two significant differences to commit_delay here: 1. Instead of waiting for commit_delay to expire, processes are woken and fsync is started immediately when we know there's no more commit records coming that we should wait for. Even though commit_delay is given in microseconds, the real granularity of the wait can be as high as 10 ms, which is in the same ball park as the fsync itself. 2. commit_delay is not used when there's less than commit_siblings non-idle backends in the system. With very short transactions, it's worthwhile to wait even if that's the case, because a client can begin and finish a transaction in much shorter time than it takes to fsync. This is what makes the commit_delay to not work at all in my test case with 2 clients. Here's a spreadsheet with the results of the tests I ran: http://community.enterprisedb.com/groupcommit-comparison.ods It contains a graph that shows that the patch works very well for this test case. It's not very good for real life as it is, though. An obvious flaw is that if you have a longer-running transaction, effect 1. goes away. Instead of waiting for NBackends commit records, we should try to guess the number of transactions that are likely to finish in a reasonably short time. I'm thinking of keeping a running average of commits per second, or # of transactions that finish while an fsync is taking place. Any thoughts? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] tsearch2 regression test failures
On Mon, Mar 26, 2007 at 02:32:26PM +0400, Teodor Sigaev wrote:
FWIW, it looks like it failed to reject stopwords. Is it possible you
Right.
I suppose the problem is with '\r\n'... Try attached patch.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW:
http://www.sigaev.ru/
*** ./contrib/tsearch2/stopword.c.origMon Mar 26 14:25:16 2007
--- ./contrib/tsearch2/stopword.c Mon Mar 26 14:28:25 2007
***
*** 47,53
while (fgets(buf, sizeof(buf), hin))
{
! buf[strlen(buf) - 1] = '\0';
pg_verifymbstr(buf, strlen(buf), false);
if (*buf == '\0')
continue;
--- 47,57
while (fgets(buf, sizeof(buf), hin))
{
! pbuf = buf;
! while( !isspace( *pbuf ) )
! pbuf++;
! *pbuf = '\0';
!
pg_verifymbstr(buf, strlen(buf), false);
if (*buf == '\0')
continue;
Yup, that solved the problem, thanks.
Wouldn't it be more efficiently written to walk the string backwards until
!isspace instead? Not sure that it matters at all, but then you'll
normallyi never step over more than two bytes...
//Magnus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch2 regression test failures
Yup, that solved the problem, thanks. I'll commit extended patch - there is one more place with the same bug. Wouldn't it be more efficiently written to walk the string backwards until !isspace instead? Not sure that it matters at all, but then you'll normallyi never step over more than two bytes... It doesn't significant matter - file reads once per backend lifetime. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] tsearch2 regression test failures
Yup, that solved the problem, thanks. I'll commit extended patch - there is one more place with the same bug. Ok, thanks. Wouldn't it be more efficiently written to walk the string backwards until !isspace instead? Not sure that it matters at all, but then you'll normallyi never step over more than two bytes... It doesn't significant matter - file reads once per backend lifetime. ok. /Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Proposal: integration plpgpsm into core PostgreSQL
Hello, I propose integration plpgpsm into core PostgreSQL. plpgpsm is SQL/PSM language implementation for PostgreSQL based on pl/pgsql runtime. Currently this modified runtime exists for PostgreSQL 8.1, 8.2 and CVS HEAD. Some information: * http://pgfoundry.org/projects/plpsm/ * http://www.pgsql.cz/index.php/SQL/PSM * http://www.pgsql.cz/index.php/SQL/PSM_Manual Sun's people translate it to en. * http://www.pgsql.cz/index.php/P%c5%99%c3%adru%c4%8dka_SQL/PSM original documentation in czech As far as I know, only one topic isn't well implemented - diagnostics fields, because PostgreSQL is very far to std. in this point. Over standard multi assignement is compatible with DB2 and MySQL, and SQLSTATE variable behave compatible with DB2. Statement PRINT is nonstandard too, but usefull if we have not debugger. known problems: * only few people know this language, minimum doc is available * it shares all problems with plpgsql - not well structured code and some strange constructs in code * code is duplicit for 50% (maybe more) with plpgsql what can be better solved: * I belive parser can be integrated into main parser (it's part of standard) * with hypoteticle PL runtime API it can share code with plpgsql whay I didn't it: * I was afraid to do big changes in plpgsql runtime together with development of less or more experimental (in this moment) runtime (language). And this year was rich for changes in plpgsql runtime. Current implementation has zero impact on plpgsql runtime or core files. .. I sent scrollable cursor support and table expression patches which aren't directly related to plpgpsm. why add it to core? * without additing into core plpgpsm will not have user base. It means nobody will check it, nobody will use it, nobody will correct regress tests and documentation. * implementation of sql/psm is more complete than in MySQL and I sucesfully tested portability of MySQL stored procedures to plpgpsm. next future steps? * real integration into core (parser, executor) .. it can carry annonymous SQL/PSM statements, true session variables, posibility of diagnostic of colission variables and attributes and more. It means strong reduction of plpgpsm runtime size. * steps to bigger conformance with ANSI philosophy (three levels of exceptions which depend on SQLSTATE, diagnostics fields, possibility of handling any warnings, ...) I belive plpgpsm is comparable with plpgsql and has more modern look and respect of standard, so it should be part of the core. I belive somebody will/should redesign plpgpsm and plpgsql in the (not far) future, and with large user base, fixed parser, completed regress tests this task will be easier. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(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] tsearch2 regression test failures
Teodor Sigaev [EMAIL PROTECTED] writes: ! pbuf = buf; ! while( !isspace( *pbuf ) ) ! pbuf++; ! *pbuf = '\0'; Surely the loop needs to look like while (*pbuf !isspace(*pbuf)) pbuf++; regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch2 regression test failures
Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: ! pbuf = buf; ! while( !isspace( *pbuf ) ) ! pbuf++; ! *pbuf = '\0'; Surely the loop needs to look like while (*pbuf !isspace(*pbuf)) pbuf++; Yes. But in any case, I am having difficulty in understanding why we are seeing a CR at all - the file should be opened in text mode, which should translate CR-LF in the file to a simple LF in the buffer. So regardless of the odd behavior of CVSNT, which presumabye caused this mess, it's rather strange. Can someone please explain? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Server-side support of all encodings
Tatsuo Ishii [EMAIL PROTECTED] writes: Sigh. From the first day when JOHAB was supported (back to 7.3 days), it should had not been in the server encodings. JOHAB's second byte definitely contain 0x41 and above. *johab*.map just reflect the fact. I think we should remove JOHAB from the server encodings list. I'm afraid users who have JOHAB encoded databases get angry, though. I think the best way to proceed is probably to fix this in HEAD but not back-patch it. During a dump and reload the encoding can be corrected to something safe. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Guarenteeing complex referencial integrity through custom triggers
At this time Postgresql is unable to guarentee that complex* referencial integrity (RI) constraints can be enforced using standard (read non-C) triggers or SQL. This is an unfortunate side-effect of the MVCC model, as it allows multiple transactions to modify a snapshot of the data at a certain point in time. Later these modifications are 'merged' together, resuling in a violation of a constraint that was not in the individual snapshots. (You might notice that the same happens with most version constrol software). Some previous discussion was in http://archives.postgresql.org/pgsql-general/2007-02/msg01234.php. As a real-world example where the constraint cannot be enforced in postgresql. For every tuple t in cartridge_change, there must exists a tuple t' in printers with t.id = t'.id, and a tuple t'' in cartridge_types with t.color = t''.color and t'.printertype = t''.printertype Under serializable isolation its impossible to enforce this constraint in all cases (without triggers written in C). For read committed its probably possible. The failure lies in scenarios where the MVCC rules hide newly inserted rows (by a transaction that starts later). This is also a concern in the referencial integrity (RI) constraints. Here the problem is solved by cross-checking against the 'latest' snapshot. However this functionality is not exposed to the user and can only be used from functions written in C. My intention is to expose the functionality to the outside world for general use. This provides means to ensure custom complex constraints can be enforced properly. I hope to push it into 8.3 if possible. Now lets get down to the details: The problem boils down to: * Newly inserted child (dependent on existance of others) tuples are not detected, while we should be aware of their presence. This happens under the following situations: * Checking the existance of child tuples. * Deleting the child tuples. * Updating the child tuples. We can exclude these situations: * Inserting a child, since the parent must exist for the current transaction. * Doing something to a parent, it's the 'propagation' that is a possible problem. One of the commercial competition, Oracle, which also has an implementation of the MVCC model, allows the FOR UPDATE clause to function as a way to ensure referencial integrity. See http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns _sqlproc.htm#sthref294 (and beyond) for details. It seems Oracle treats SELECT FOR UPDATE as something special. Unfortunally the manual is not too in-depth and slight confusing on the details, so I could be wrong. Oracle has choosen to allow constraint enforcement by locking on the parent tuple. In contrast postgres has chosen (historically, see RI triggers) to fail on detecting conflicting newly inserted rows (the cross-check). I can't debate which is better, since the situations where the problem can happen are restricted and transactions are not normally in serializable isolation, its doesn't seem to be a problem in practice. Nevertheless continuing as currently is probably the way to go, as the infrastructure is available and working properly. A few things remain: * Language extension? for referencial seems not too intrusive. * SPI_execute_snapshot interoperation, should give a failure. * Doing parallel tests? I think about hacking it in... Any opinions? - Joris Dobbelsteen ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch_core for inclusion
On Fri, 23 Mar 2007, Florian G. Pflug wrote: Teodor Sigaev wrote: For given schema and server's locale, it's possible to have several FTS configurations, but the only one (with special flag enabled) could be used as default. Current (active) FTS configuration contains in GUC variable tsearch_conf_name. If it's not defined, then FTS configuration is looked in search_path to match server's locale with default flag enabled. Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? I'm not sure I understand you correct (a bit complex :), but it's allowed to have only _one_ DEFAULT configuration per schema/per locale. So, visibility is defined by search_path for given locale. Having one DEFAULT configuration per schema per locale will necessarily cause confusion if search_path is not set carefully I think. That's what we're worry about and try to avoid possible confusions. greetings, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Copyrights on files
* Bruce Momjian: src/backend/port/dynloader/aix.c * This is an unpublished work copyright (c) 1992 HELIOS Software GmbH * 30159 Hannover, Germany The company still exists, it seems. Have you tried to contact them? See http://www.helios.de/about/email.phtml. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(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] notification payloads
This feature (ability to add a message payload to a NOTIFY) is on the TODO list and I had undertaken to implement it. However, pressure of other work has conspired to make that difficult, and Abhijit Menon-Sen recently very kindly offered to help out. Therer was some discussion of implementation late last year here: http://groups.google.com/group/pgsql.hackers/browse_frm/thread/e63a5ac43e2508ce/ce47016235bd5a62?tvc=1q=notify+payloadhl=en#ce47016235bd5a62 However, in various pieces of off-list discussion it appears that there is some opposition either to the design or to the feature itself. What is more, there will clearly be vigorous opposition to any implementation which does NOT remove the use of pg_listener (which I understand and I think largely agree with). So, before an investment of any more time is made by either Abhijit or myself, I would like to get confirmation that a) there is broad agreement on the desirability of the feature and b) that there is broad agreement on the general design (i.e. to use a circular buffer in shared memory, of configurable size, to hold the outstanding message queue). Please speak up or forever cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch_core for inclusion
Oleg Bartunov wrote: On Fri, 23 Mar 2007, Florian G. Pflug wrote: Teodor Sigaev wrote: For given schema and server's locale, it's possible to have several FTS configurations, but the only one (with special flag enabled) could be used as default. Current (active) FTS configuration contains in GUC variable tsearch_conf_name. If it's not defined, then FTS configuration is looked in search_path to match server's locale with default flag enabled. Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? I'm not sure I understand you correct (a bit complex :), but it's allowed to have only _one_ DEFAULT configuration per schema/per locale. So, visibility is defined by search_path for given locale. Yes, but why is that needed? Wouldn't one DEFAULT configuration per database be sufficient, and avoid the search_path problems? Sorry if I'm being stupid - I just can't see what having a different DEFAULT configuration per schema buys you. greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] notification payloads
Andrew Dunstan wrote: So, before an investment of any more time is made by either Abhijit or myself, I would like to get confirmation that a) there is broad agreement on the desirability of the feature Yes, absolutely desirable. and b) that there is broad agreement on the general design (i.e. to use a circular buffer in shared memory, of configurable size, to hold the outstanding message queue). Would it spill out to disk and expand (and shrink again) as required? Loss of notifications should not occur imho. Regards, Dave. ---(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] tsearch_core for inclusion
On Mon, 26 Mar 2007, Florian G. Pflug wrote: Oleg Bartunov wrote: On Fri, 23 Mar 2007, Florian G. Pflug wrote: Teodor Sigaev wrote: For given schema and server's locale, it's possible to have several FTS configurations, but the only one (with special flag enabled) could be used as default. Current (active) FTS configuration contains in GUC variable tsearch_conf_name. If it's not defined, then FTS configuration is looked in search_path to match server's locale with default flag enabled. Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? I'm not sure I understand you correct (a bit complex :), but it's allowed to have only _one_ DEFAULT configuration per schema/per locale. So, visibility is defined by search_path for given locale. Yes, but why is that needed? Wouldn't one DEFAULT configuration per database be sufficient, and avoid the search_path problems? Sorry if I'm being stupid - I just can't see what having a different DEFAULT configuration per schema buys you. It's what people asked for. Think about several sub-projects which share one database, for example. They all may need different configurations. It's not difficult to specify schema-qualified name of fts configuration, but the problem arises when using simple search, since there is no way to specify fts name in CREATE INDEX command. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Time to package 8.2.4
We have had several customers get bit by the 8.2.3 stats collector bug. It is also starting to get reported in areas such as IRC. The really bad thing about this bug is that you won't know what is wrong unless you know where to look, PostgreSQL will just appear slow and tying up resources. I reiterate the question. Can we please package 8.2.4. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] notification payloads
Dave Page wrote: Andrew Dunstan wrote: So, before an investment of any more time is made by either Abhijit or myself, I would like to get confirmation that a) there is broad agreement on the desirability of the feature Yes, absolutely desirable. good ;-) and b) that there is broad agreement on the general design (i.e. to use a circular buffer in shared memory, of configurable size, to hold the outstanding message queue). Would it spill out to disk and expand (and shrink again) as required? Loss of notifications should not occur imho. No loss, but, per previous discussion, it would block and try to get other backends to collect their outstanding notifications. Let's say we provide 100Kb for this (which is not a heck of a lot) , that the average notification might be, say, 40 bytes of name plus 60 bytes of message. Then we have room for about 1000 messages in the queue. This would get ugly only if backend presumably in the middle of some very long transaction, refused to pick up its messages despite prodding. But ISTM that means we just need to pick a few strategic spots that will call CHECK_FOR_NOTIFICATIONS() even in the middle of a transaction and store them locally. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] notification payloads
Andrew Dunstan wrote: Let's say we provide 100Kb for this (which is not a heck of a lot) , that the average notification might be, say, 40 bytes of name plus 60 bytes of message. Then we have room for about 1000 messages in the queue. This would get ugly only if backend presumably in the middle of some very long transaction, refused to pick up its messages despite prodding. But ISTM that means we just need to pick a few strategic spots that will call CHECK_FOR_NOTIFICATIONS() even in the middle of a transaction and store them locally. Why have the name on each message? Presumably names are going to be few compared to the total number of messages, so maybe store the names in a separate hash table and link them with a numeric identifier. That gives you room for a lot more messages. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Partitioned tables constraint_exclusion
I'm not sure if this is a bug, missing feature, misunderstanding on my part?
I checked the TODO list and couldn't find anything on it.
I currently have a 750 million row table, indexes are 10 GB, so trying
to partition it.
The basic -
constraint_exclusion + exact match = OK
constraint_exclusion + ( var + var )::case = Not OK
Weslee
I tried to break it down to a simple case -
(kid_200601 should never show up in the plan)
mytest=# create table master ( var_text text not null, var_ts timestamp
with time zone not null, unique ( var_ts ) );
NOTICE: CREATE TABLE / UNIQUE will create implicit index
master_var_ts_key for table master
CREATE TABLE
mytest=# create table kid_200601 ( check ( var_ts = '2006-01-01
00:00:00' AND var_ts '2006-02-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# create table kid_200602 ( check ( var_ts = '2006-02-01
00:00:00' AND var_ts '2006-03-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# create table kid_200603 ( check ( var_ts = '2006-03-01
00:00:00' AND var_ts '2006-04-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# explain select count(*) from master where var_ts '2006-02-22
00:00:00' ;
QUERY PLAN
---
Aggregate (cost=71.94..71.95 rows=1 width=0)
- Append (cost=7.09..69.18 rows=1101 width=0)
- Bitmap Heap Scan on master (cost=7.09..21.68 rows=367 width=0)
Recheck Cond: (var_ts '2006-02-22
00:00:00+00'::timestamp with time zone)
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.00 rows=367 width=0)
Index Cond: (var_ts '2006-02-22
00:00:00+00'::timestamp with time zone)
- Seq Scan on kid_200602 master (cost=0.00..23.75 rows=367
width=0)
Filter: (var_ts '2006-02-22 00:00:00+00'::timestamp
with time zone)
- Seq Scan on kid_200603 master (cost=0.00..23.75 rows=367
width=0)
Filter: (var_ts '2006-02-22 00:00:00+00'::timestamp
with time zone)
(10 rows)
mytest=# select now() ;
now
---
2007-03-26 16:02:29.360435+00
(1 row)
mytest=# explain select count(*) from master where var_ts ( now() - '1
month'::interval )::timestamptz ;
QUERY PLAN
--
Aggregate (cost=114.94..114.95 rows=1 width=0)
- Append (cost=7.10..111.27 rows=1468 width=0)
- Bitmap Heap Scan on master (cost=7.10..23.52 rows=367 width=0)
Recheck Cond: (var_ts (now() - '1 mon'::interval))
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts (now() - '1 mon'::interval))
- Seq Scan on kid_200601 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts (now() - '1 mon'::interval))
- Seq Scan on kid_200602 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts (now() - '1 mon'::interval))
- Seq Scan on kid_200603 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts (now() - '1 mon'::interval))
(12 rows)
mytest=# show constraint_exclusion ;
constraint_exclusion
--
on
(1 row)
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval
)::timestamptz ;
QUERY PLAN
Aggregate (cost=105.77..105.78 rows=1 width=0)
- Append (cost=7.10..102.10 rows=1468 width=0)
- Bitmap Heap Scan on master (cost=7.10..22.60 rows=367 width=0)
Recheck Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Seq Scan on kid_200601 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Seq Scan on kid_200602 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Seq Scan on kid_200603 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
(12 rows)
mytest=#
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an
Re: [HACKERS] notification payloads
Alvaro Herrera wrote: Andrew Dunstan wrote: Let's say we provide 100Kb for this (which is not a heck of a lot) , that the average notification might be, say, 40 bytes of name plus 60 bytes of message. Then we have room for about 1000 messages in the queue. This would get ugly only if backend presumably in the middle of some very long transaction, refused to pick up its messages despite prodding. But ISTM that means we just need to pick a few strategic spots that will call CHECK_FOR_NOTIFICATIONS() even in the middle of a transaction and store them locally. Why have the name on each message? Presumably names are going to be few compared to the total number of messages, so maybe store the names in a separate hash table and link them with a numeric identifier. That gives you room for a lot more messages. Maybe, but at the cost of some considerable complexity ISTM, especially as this all needs to be in shared memory. On any machine with significant workload a few Mb of memory would not be missed. How many messages do we reasonably expect to be in the queue? Judging by our usage here it would be a handful at most, but maybe others have far more intensive uses. Is anyone really doing notifies at a rate of many per second? 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] Time to package 8.2.4
Am Montag, 26. März 2007 18:07 schrieb Joshua D. Drake: I reiterate the question. Can we please package 8.2.4. Sure, go ahead. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Time to package 8.2.4
Peter Eisentraut wrote: Am Montag, 26. März 2007 18:07 schrieb Joshua D. Drake: I reiterate the question. Can we please package 8.2.4. Sure, go ahead. Funny :). What can I do to help get 8.2.4 branched? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] pg_index updates and SI invalidation
While experimenting with the proposed CREATE INDEX support with HOT, I realized that SI invalidation are not sent properly for pg_index updates. I noticed the following comment in relcache.c /* * RelationReloadClassinfo - reload the pg_class row (only) * * This function is used only for indexes. We currently allow only the * pg_class row of an existing index to change (to support changes of * owner, tablespace, or relfilenode), not its pg_index row or other * subsidiary index schema information. Therefore it's sufficient to do * this when we get an SI invalidation. Furthermore, there are cases * where it's necessary not to throw away the index information, especially * for nailed indexes which we are unable to rebuild on-the-fly. * * We can't necessarily reread the pg_class row right away; we might be * in a failed transaction when we receive the SI notification. If so, * RelationClearRelation just marks the entry as invalid by setting * rd_isvalid to false. This routine is called to fix the entry when it * is next needed. */ From the comment, its clear that we don't expect SI invalidation to work correctly for pg_index row updates. We are thinking of adding a new attribute to pg_index row to control the usability of the index in queries. Is it worth spending time to support SI invalidation for pg_index updates or should we rather add the attribute to pg_class though pg_index seems to the right place ? A side-effect of this limitation is that REINDEX does not make an index immediately available in the same transaction if REINDEX is used to fix an earlier failed CREATE INDEX CONCURRENTLY. Though we set indisvalid to 'true' at the end of REINDEX, the effect is not seen until the transaction completes because of lack of SI invalidation. Any suggestions how should I proceed with this ? Should I add a pg_class attribute or is it worth fixing pg_index SI invalidation ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] pg_index updates and SI invalidation
Pavan Deolasee [EMAIL PROTECTED] writes: While experimenting with the proposed CREATE INDEX support with HOT, I realized that SI invalidation are not sent properly for pg_index updates. Hmm ... actually, CREATE INDEX CONCURRENTLY gets this wrong already, no? I suspect that sessions existing at the time C.I.C is done will never see the new index as valid, unless something else happens to make them drop and rebuild their relcache entries for it. 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] Guarenteeing complex referencial integrity through custom triggers
Joris Dobbelsteen [EMAIL PROTECTED] writes: My intention is to expose the functionality to the outside world for general use. This provides means to ensure custom complex constraints can be enforced properly. I hope to push it into 8.3 if possible. You are at least a month too late for 8.3, even if you had a solid design now, which you clearly don't. Nor am I convinced that we really want/need to support what you are talking about at the SQL level. To me, the crosscheck stuff in the RI support is an extremely dirty hack that might or might not be 100% correct. Exposing it to the SQL level, and thereby committing to support it forever, seems the height of folly. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch_core for inclusion
Oleg Bartunov oleg@sai.msu.su writes: On Fri, 23 Mar 2007, Florian G. Pflug wrote: Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? I'm not sure I understand you correct (a bit complex :), but it's allowed to have only _one_ DEFAULT configuration per schema/per locale. So, visibility is defined by search_path for given locale. Not sure that that's a good idea at all. We used to have search-path-dependent rules for deciding which opclass was default, and found that that was not good. Also, I do not understand how the queries and the indexes are tied together --- but doesn't an index need to be built using the same rules that are later expected by the queries? If that varies on search_path it'll be too fragile. 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] pg_index updates and SI invalidation
On 3/26/07, Tom Lane [EMAIL PROTECTED] wrote: Hmm ... actually, CREATE INDEX CONCURRENTLY gets this wrong already, no? I suspect that sessions existing at the time C.I.C is done will never see the new index as valid, unless something else happens to make them drop and rebuild their relcache entries for it. Yes, C.I.C gets it wrong. I confirmed that new index is seen as invalid for existing sessions. Is it something we should fix ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] notification payloads
Alvaro Herrera [EMAIL PROTECTED] writes: Why have the name on each message? Presumably names are going to be few compared to the total number of messages, so maybe store the names in a separate hash table and link them with a numeric identifier. That gives you room for a lot more messages. That can be done by the application, if its notify payloads are such that that's a useful optimization. However it seems entirely possible to me that the payload strings might be nonrepeating and the overhead of a separate table completely wasted. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_index updates and SI invalidation
Pavan Deolasee [EMAIL PROTECTED] writes: On 3/26/07, Tom Lane [EMAIL PROTECTED] wrote: Hmm ... actually, CREATE INDEX CONCURRENTLY gets this wrong already, no? Yes, C.I.C gets it wrong. I confirmed that new index is seen as invalid for existing sessions. Is it something we should fix ? Certainly. It might be feasible to have RelationReloadClassinfo re-read the pg_index row and apply only the updates for specific known-changeable columns. The stuff it's worried about is the subsidiary data such as support function fmgr lookup records, but we don't need those to change on the fly. 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] GSoC's possible project
On Wednesday 21 March 2007 09:07, Alvaro Herrera wrote:
Germán Poó Caamaño escribió:
I'm a student and I'm planning to submit a project for Google Summer of
Code. I would like to receive feedback about to implement the vacumm
scheduling in order to allow maintenance's windows.
I have read the whole discussion about Autovacuum improvements[1]
taken in January. Alvaro Herrera suggested two improvements,
(probably following an idea of Matthew T. O'Connor), which were:
- Scheduling (a maintenance window)
- Process Handling.
As fas as I know, the process handling is a work in progress[2], while
the scheduling is defined (almost complete) but not implemented yet.
Just for the record, I support this project and I can mentor it.
Which is not clear to me (yet), if it would required collecting enough
information through implementing a sort of 'VACUUM SUMMARY'[3].
I would think this is a separate project from the maintenance window
change proper.
IF the OP want's the project to be considered, he needs to submit an
application TODAY. (Read that as I don't see an application in yet)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
As a real-world example where the constraint cannot be enforced in
postgresql.
For every tuple t in cartridge_change, there must exists a tuple t' in
printers with t.id = t'.id, and a tuple t'' in cartridge_types with
t.color = t''.color and t'.printertype = t''.printertype
While there may be unenforceable constraints, unless I am misreading your
example, I don't see a problem with enforcing this one. To restate
your problem, a company has a finite number of printers, and tracks when
a printer cartridge is changed. Each printer is of a certain type, and
each type has one or more types of cartridges that can go with it. Thus:
- -- Generic type of printer
CREATE TABLE printer (
idSERIAL NOT NULL PRIMARY KEY,
brand TEXT NOT NULL,
model TEXT NOT NULL
);
INSERT INTO printer (brand,model) VALUES ('epson','1200');
INSERT INTO printer (brand,model) VALUES ('hp','laserjet99');
- -- A printer can hold one or more cartridges, distinguished by 'color'
CREATE TABLE cartridge_types (
ptype INT NOT NULL,
CONSTRAINT cc_ptype FOREIGN KEY (ptype) REFERENCES printer(id) ON DELETE
RESTRICT,
color TEXT NOT NULL
);
CREATE UNIQUE INDEX ctype ON cartridge_types(ptype,color);
INSERT INTO cartridge_types VALUES (1,'black');
INSERT INTO cartridge_types VALUES (2,'black');
INSERT INTO cartridge_types VALUES (2,'color');
- -- Specific printers in the company
CREATE TABLE printers (
idSERIAL NOT NULL PRIMARY KEY,
ptype INTEGER NOT NULL,
CONSTRAINT ptype FOREIGN KEY (ptype) REFERENCES printer(id),
location TEXTNOT NULL
);
INSERT INTO printers(ptype,location) VALUES (1,'Room 234');
INSERT INTO printers(ptype,location) VALUES (2,'Break room #2');
INSERT INTO printers(ptype,location) VALUES (2,'NE corner of warehouse');
- -- Printers require lots of pesky hardware updates
CREATE TABLE cartridge_change (
printer_id INT NOT NULL,
CONSTRAINT change_printer FOREIGN KEY (printer_id) REFERENCES printers(id),
color TEXT NOT NULL,
whenchanged TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE cartridge_change ADD CONSTRAINT cc
FOREIGN KEY (printer_id, color)
REFERENCES cartridge_types (ptype,color);
INSERT INTO cartridge_change (printer_id, color) VALUES (1,'black');
INSERT INTO cartridge_change (printer_id, color) VALUES (1,'blue');
- -- Session 1:
- -- BEGIN;
- -- INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');
- -- Session 2:
- -- BEGIN;
- -- DELETE FROM cartridge_types WHERE ptype=2 AND color = 'color';
- -- blocks
- -- Session 1:
- -- COMMIT;
- -- Session 2:
- -- ERROR
- -- Session 1:
- -- BEGIN;
- -- DELETE FROM cartridge_types WHERE ptype=2 AND color = 'color';
- -- Session 2:
- -- BEGIN;
- -- INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');
- -- blocks
- -- Session 1:
- -- COMMIT;
- -- Session 2:
- -- ERROR
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200703261429
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFGCBJ/vJuQZxSWSsgRA16BAJ4hkfcY4ui+yLUGWNerHZf0FvRbPACg++X5
e4tmrrJ1BFcxjM3PCXyKP6Y=
=CDAM
-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] Time to package 8.2.4
Joshua D. Drake wrote: Funny :). What can I do to help get 8.2.4 branched? There is no branching involved, but you can look into src/tools/RELEASE_CHANGES and see what things you want to help with. Getting a release changes list would be a start. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] notification payloads
Andrew Dunstan wrote: No loss, but, per previous discussion, it would block and try to get other backends to collect their outstanding notifications. Let's say we provide 100Kb for this (which is not a heck of a lot) , that the average notification might be, say, 40 bytes of name plus 60 bytes of message. Then we have room for about 1000 messages in the queue. This would get ugly only if backend presumably in the middle of some very long transaction, refused to pick up its messages despite prodding. But ISTM that means we just need to pick a few strategic spots that will call CHECK_FOR_NOTIFICATIONS() even in the middle of a transaction and store them locally. Sounds good. Regards, Dave. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Time to package 8.2.4
Peter Eisentraut wrote: Joshua D. Drake wrote: Funny :). What can I do to help get 8.2.4 branched? There is no branching involved, but you can look into src/tools/RELEASE_CHANGES and see what things you want to help with. Getting a release changes list would be a start. We're just starting to get the process nice and streamlined with all the various binary builds, announcements and CVE embargos (when needed). Perhaps it's not such a good idea to start reassigning tasks to random people (no offence JD) without discussion and agreement from those normally responsible? Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Partitioned tables constraint_exclusion
On Mon, 2007-03-26 at 09:38 -0700, Weslee Bilodeau wrote: mytest=# explain select count(*) from master where var_ts ( '2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval )::timestamptz ; If you're able to supply a constant value, why not subtract 1 month before you submit the query? AFAIK timestamptz arithmetic depends upon the current timezone which is a STABLE value and so won't currently work with partitioning. Having partitioning work with STABLE functions should be a TODO item if it isn't already, but that requires some thought to implement and won't happen for 8.3. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Time to package 8.2.4
Dave Page wrote: Peter Eisentraut wrote: Joshua D. Drake wrote: Funny :). What can I do to help get 8.2.4 branched? There is no branching involved, but you can look into src/tools/RELEASE_CHANGES and see what things you want to help with. Getting a release changes list would be a start. We're just starting to get the process nice and streamlined with all the various binary builds, announcements and CVE embargos (when needed). Perhaps it's not such a good idea to start reassigning tasks to random people (no offence JD) without discussion and agreement from those normally responsible? No offense taken, I think Peter was saying... Hey this is something you (being me) could probably do, to help the continuation of that process. It is (i would guess) certainly something that people don't like doing in general and thus would help get the ball rolling. I don't mind either way, but 8.2.4 needs to be released. If my putting together a changes list will help with that. I will do so. Sincerely, Joshua D. Drake Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch_core for inclusion
On Mon, 26 Mar 2007, Tom Lane wrote: Oleg Bartunov oleg@sai.msu.su writes: On Fri, 23 Mar 2007, Florian G. Pflug wrote: Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? I'm not sure I understand you correct (a bit complex :), but it's allowed to have only _one_ DEFAULT configuration per schema/per locale. So, visibility is defined by search_path for given locale. Not sure that that's a good idea at all. We used to have search-path-dependent rules for deciding which opclass was default, and found that that was not good. Also, I do not understand how the queries and the indexes are tied together --- but doesn't an index need to be built using the same rules that are later expected by the queries? If that varies on search_path it'll be too fragile. fts is a very rich application and the rules for creating index and queries could be different. One index could be used for searching with/without taking into account stop-words, for example. But, in general, index and queries should be processed by the same parsers and dictionaries. It can be less fragile, if we store somehow fts information (fts configuration name) to display it, say, in \di command. I repeat, I see potential problem (confusion) only for simple fts index, which creates on TEXT/VARCHAR data, using CREATE INDEX command, since it's impossible explicitly specify which fts configuration to use. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Time to package 8.2.4
Joshua D. Drake wrote: Dave Page wrote: Peter Eisentraut wrote: Joshua D. Drake wrote: Funny :). What can I do to help get 8.2.4 branched? There is no branching involved, but you can look into src/tools/RELEASE_CHANGES and see what things you want to help with. Getting a release changes list would be a start. We're just starting to get the process nice and streamlined with all the various binary builds, announcements and CVE embargos (when needed). Perhaps it's not such a good idea to start reassigning tasks to random people (no offence JD) without discussion and agreement from those normally responsible? No offense taken, I think Peter was saying... Hey this is something you (being me) could probably do, to help the continuation of that process. It is (i would guess) certainly something that people don't like doing in general and thus would help get the ball rolling. I don't mind either way, but 8.2.4 needs to be released. If my putting together a changes list will help with that. I will do so. I would assume that would be a big help, especially given that Bruce is busy with the patch queue right now. There is another point to putting it out fairly soon - we're entering feature freeze, and most devs would likely want to focus on 8.3 at that time. Probably a good thing to get 8.2.4 out before that (which really means we should've started a couple of weeks back, but hey, we can't have everything) //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Time to package 8.2.4
Joshua D. Drake wrote: Dave Page wrote: Peter Eisentraut wrote: Joshua D. Drake wrote: Funny :). What can I do to help get 8.2.4 branched? There is no branching involved, but you can look into src/tools/RELEASE_CHANGES and see what things you want to help with. Getting a release changes list would be a start. We're just starting to get the process nice and streamlined with all the various binary builds, announcements and CVE embargos (when needed). Perhaps it's not such a good idea to start reassigning tasks to random people (no offence JD) without discussion and agreement from those normally responsible? No offense taken, I think Peter was saying... Hey this is something you (being me) could probably do, to help the continuation of that process. It is (i would guess) certainly something that people don't like doing in general and thus would help get the ball rolling. I don't mind either way, but 8.2.4 needs to be released. If my putting Yeah, I know - we should just get agreement on such procedural changes first imho. /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Partitioned tables constraint_exclusion
Simon Riggs wrote:
On Mon, 2007-03-26 at 09:38 -0700, Weslee Bilodeau wrote:
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval
)::timestamptz ;
If you're able to supply a constant value, why not subtract 1 month
before you submit the query?
AFAIK timestamptz arithmetic depends upon the current timezone which is
a STABLE value and so won't currently work with partitioning.
Having partitioning work with STABLE functions should be a TODO item if
it isn't already, but that requires some thought to implement and won't
happen for 8.3.
Mainly its because the value comes from a reporting system that has
minimal brains, it passes values it gets from the user directly into a
query.
IE, they enter '1 month', which I use to populate the interval value,
ts ( NOW() - $VALUE )
But, in the example I did a timestamp - interval, the exact date, not
NOW() - Still didn't work.
I'm guessing anything that has to think, math, etc is not valid for
constrain_exclusion?
Its not in the docs anywhere, so trying to isolate what can and can't be
done.
Weslee
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00'::timestamptz + '1 second'::interval
)::timestamptz ;
QUERY PLAN
---
Aggregate (cost=105.77..105.78 rows=1 width=0)
- Append (cost=7.10..102.10 rows=1468 width=0)
- Bitmap Heap Scan on master (cost=7.10..22.60 rows=367 width=0)
Recheck Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Seq Scan on kid_200601 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Seq Scan on kid_200602 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Seq Scan on kid_200603 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
(12 rows)
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00' ) ;
QUERY PLAN
--
Aggregate (cost=22.60..22.61 rows=1 width=0)
- Append (cost=7.09..21.68 rows=367 width=0)
- Bitmap Heap Scan on master (cost=7.09..21.68 rows=367 width=0)
Recheck Cond: (var_ts '2007-03-26
16:03:27.370627+00'::timestamp with time zone)
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.00 rows=367 width=0)
Index Cond: (var_ts '2007-03-26
16:03:27.370627+00'::timestamp with time zone)
(6 rows)
mytest=#
---(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] Time to package 8.2.4
Hello, Below is the change log as I see it. I gleaned it from here: http://projects.commandprompt.com/public/pgsql/log/branches/REL8_2_STABLE If I missed anything let me know. Sincerely, Joshua D. Drake * Fixed preload_shared_libraries on Windows (Bruce) * Rearrange use of plpgsql_add_initdatums() so that only the parsing of a DECLARE section needs to know about it. Fixes bug that caused a DECLARE within an exception handler tried to reinitialize SQLERRM. (Tom) * Fixed an ancient local varaiable (re)initializing error in plpgsql's exec_stmt_block. (Tom) * Fixed bug when localized to_char() day or month names were incorrectly transformed to lower or upper string. (Bruce) * Fix for early log messages getting lost on postmaster startup when running as a service on Win32. (Magnus) * Fixed backend crash with incorrect ts_query (tsearch2) parsing. (Teodor) * Fixed bug in JOIN logic for planning OUTER JOINS. (Tom) * Disallow commiting prepared transactions from a different database in which the transaction was initiated (Heikki) * Improve handling of psuedoconstants (Tom) * Resolved bug with clauseless JOINS and subqueries. (Tom) * Fixed problem in 8.2 changes that allowed one-time qual conditions to be checked at plan levels below the top. (Tom) * Adjust is_pushed_down to work correctly with INNER JOINS. (Tom) * Fixed old bug in portal management for FETCH queries. (Tom) * Allow Win32 pg_dump to create files larger than 2GB (Magnus) * Made markQueryForLocking() work with nested views. (Tom) * Fix miscalculation of stats collector's write delay. (Tom) * Fix inline_function() to work with binary compatible cases. (Tom) * Allow cube data type to be correctly toastable. (Teodor) * Fix vac_update_relstats to ensure it always sends a relcache inval message. (Tom) * Remove unsafe calling of WSAStartup and WSACleanup on Win32. (Magnus) * Fix race condition in pg_database_size and pg_tablespace_size. (Alvaro) * Allow POSIX-style timezones that don't exactly many any timezone database entry to be treated as having correct USA DST rules. (Tom) * Fix longstanding bug with VACUUM FULL handling of update chains. (Tom) * Fixed uninitialized value in pgstatindex that caused invalid values to be reported in some cases (Tatsuhito Kasahara) * Insure that read-only queries are truly read-only with SPI_cursor_open. (Tom) * Fixed 8.2 breakage of domains over array types. (Tom) * Fixed parser bug (tsearch2) with UTF8 and C locales on Win32. (Teodor) * Fix pg_wchar_table's maxmblen field of EUC_CN, EUC_TW, MULE_INTERNAL and GB18030. (ITAGAKI Takahiro). -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: maandag 26 maart 2007 19:52 To: Joris Dobbelsteen Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers Joris Dobbelsteen [EMAIL PROTECTED] writes: My intention is to expose the functionality to the outside world for general use. This provides means to ensure custom complex constraints can be enforced properly. I hope to push it into 8.3 if possible. You are at least a month too late for 8.3, even if you had a solid design now, which you clearly don't. Than its not possible, next try later on. I was messing up different dates it seemed. Nor am I convinced that we really want/need to support what you are talking about at the SQL level. To me, the crosscheck stuff in the RI support is an extremely dirty hack that might or might not be 100% correct. Exposing it to the SQL level, and thereby committing to support it forever, seems the height of folly. Debatable... Yet I see several options: 1) Extend the approach taken for the current RI triggers (i.e. 'cross-check hack'). 2) Build some general framework for constraint enforcement. 3) Invent something new. [Few more that aren't really proposable] At this point: 1) At least Tom's not in favor and there is little commerical motivation to do it right. 2) This is extremely huge project and needs to build on a primitive, with the current only a 'dirty hack' available. Probably it extends the CHECK syntax currently supported, and this is extremely involved. 3) Falling short of the innovative/sparkling idea. The case is that at this point consistency within a single modified snapshot of the database, does not imply all possible views (snapshots) are consistent too. So we need to ensure both are consistent. Yet there is no single _supported_ way to make that work. Its falling short on its commercial competitors (and my view of an 'enterprise dbms' unfortunally). I'm fully open to other suggestions... - Joris ---(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] Guarenteeing complex referencial integrity through custom triggers
[Resent: mailing list only] Tom, you mail server won't accept: The e-mail system was unable to deliver the message, but did not report a specific reason. Check the address and try again. If it still fails, contact your system administrator. orange.nl #5.0.0 X-SMTP-Server; host sss.pgh.pa.us[66.207.139.130] said: 5505.0.0 Go away, spammer (in reply to MAIL FROM command) [//] -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: maandag 26 maart 2007 19:52 To: Joris Dobbelsteen Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers Joris Dobbelsteen [EMAIL PROTECTED] writes: My intention is to expose the functionality to the outside world for general use. This provides means to ensure custom complex constraints can be enforced properly. I hope to push it into 8.3 if possible. You are at least a month too late for 8.3, even if you had a solid design now, which you clearly don't. Than its not possible, next try later on. I was messing up different dates it seemed. Nor am I convinced that we really want/need to support what you are talking about at the SQL level. To me, the crosscheck stuff in the RI support is an extremely dirty hack that might or might not be 100% correct. Exposing it to the SQL level, and thereby committing to support it forever, seems the height of folly. Debatable... Yet I see several options: 1) Extend the approach taken for the current RI triggers (i.e. 'cross-check hack'). 2) Build some general framework for constraint enforcement. 3) Invent something new. [Few more that aren't really proposable] At this point: 1) At least Tom's not in favor and there is little commerical motivation to do it right. 2) This is extremely huge project and needs to build on a primitive, with the current only a 'dirty hack' available. Probably it extends the CHECK syntax currently supported, and this is extremely involved. 3) Falling short of the innovative/sparkling idea. The case is that at this point consistency within a single modified snapshot of the database, does not imply all possible views (snapshots) are consistent too. So we need to ensure both are consistent. Yet there is no single _supported_ way to make that work. Its falling short on its commercial competitors (and my view of an 'enterprise dbms' unfortunally). I'm fully open to other suggestions... - Joris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Partitioned tables constraint_exclusion
Weslee Bilodeau wrote: Mainly its because the value comes from a reporting system that has minimal brains, it passes values it gets from the user directly into a query. IE, they enter '1 month', which I use to populate the interval value, ts ( NOW() - $VALUE ) But, in the example I did a timestamp - interval, the exact date, not NOW() - Still didn't work. I'm guessing anything that has to think, math, etc is not valid for constrain_exclusion? Its not in the docs anywhere, so trying to isolate what can and can't be done. This works - CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ; SELECT count(*) FROM master WHERE var_ts now_interval( '1 month' ); This doesn't work - SELECT count(*) FROM master WHERE var_ts ( NOW() - '1 month'::interval ); This works for me, as the reporting system I know doesn't change timezones, and function cache doesn't last longer then the current select? But, its basically the exact same logic in both cases? Weslee ---(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] BSD advertizing clause in some files
Neil Conway wrote: Bruce Momjian wrote: Someone has pointed out that the following files have the 4-part BSD copyright, which includes the advertising clause: src/backend/port/darwin/system.c src/backend/port/dynloader/freebsd.c src/backend/port/dynloader/openbsd.c src/backend/port/dynloader/netbsd.c src/backend/utils/mb/wstrcmp.c src/backend/utils/mb/wstrncmp.c src/port/strtoul.c src/port/getopt.c src/port/getopt_long.c src/port/inet_aton.c src/port/strtol.c src/port/snprintf.c contrib/pgcrypto/blf.c contrib/pgcrypto/blf.h Because Berkeley has said the advertising clause is to be ignored/removed, should we remove it from our files too? I don't think we *need* to remove it, but I agree we should remove it for the sake of clarity. Note that the UC declaration only applies to code that is copyright UC Berkeley -- which is most of the above files, but not all of them (e.g. blf.c and blf.h are copyright Niels Provos). Rather than removing the copyright clause per se, it might be better to just update to the latest versions of these files in an upstream source (e.g. NetBSD). They've already gone through their source tree and updated the Berkeley copyrights as appropriate. I removed the advertising clause from all the BSD-copyrighted files from Berkeley, namely all but */blf.*. I didn't update them from upsteam sources because some don't have clear upstream sources, and an update isn't a trivial operation --- if we need to update, it should be separate operation on all files, not just the ones with advertising clauses. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 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] Copyright question
FYI, I have received permission, below, to remove the Andrew Yu copyright. Thanks. --- Andrew Yu wrote: Hi Bruce, This header is originally part of a separate library package for dynamic loading on DECstation Ultrix. Ultrix did not support dynamic loading natively. (Is there really people still using this?) I'm fine with removing my copyright line on this file. cc'ing the mailing lists is also fine. -andrew --- Bruce Momjian [EMAIL PROTECTED] wrote: Andrew, long time, no talk. We have found that the PostgreSQL file src/backend/port/dynloader/ultrix4.h contains your personal copyright of: * Copyright (c) 1993 Andrew K. Yu, University of California at Berkeley * All rights reserved. ... BSD copyright text follows Can we remove that line and keep only the Berkely and PostgreSQL Global Development Group copyright lines? If you agree, can I CC a reply to you on the public PostgreSQL lists so there is an official record of this? Thanks. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Time to package 8.2.4
On 3/26/07, Joshua D. Drake [EMAIL PROTECTED] wrote: * Improve handling of psuedoconstants (Tom) s/psuedoconstants/pseudoconstants/ And +1 to have 8.2.4 released soon. I recommend our customers to not use 8.2 atm because of the stats problem. -- Guillaume ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] notification payloads
Andrew Dunstan [EMAIL PROTECTED] writes: and b) that there is broad agreement on the general design (i.e. to use a circular buffer in shared memory, of configurable size, to hold the outstanding message queue). Would it spill out to disk and expand (and shrink again) as required? Loss of notifications should not occur imho. No loss, but, per previous discussion, it would block and try to get other backends to collect their outstanding notifications. Let's say we provide 100Kb for this (which is not a heck of a lot) , that the average notification might be, say, 40 bytes of name plus 60 bytes of message. Then we have room for about 1000 messages in the queue. This would get ugly only if backend presumably in the middle of some very long transaction, refused to pick up its messages despite prodding. But ISTM that means we just need to pick a few strategic spots that will call CHECK_FOR_NOTIFICATIONS() even in the middle of a transaction and store them locally. Keep in mind that the usual place you run into problems with this type of buffering is where you have two processes talking to each other. Say a producer-consumer type of design. You want to be sure you never deadlock with each process waiting for the other to consume a notification. I don't think this is a problem in this case because it just means the state you enter when you're blocked waiting for your buffer to have free space MUST be amongst the times you call CHECK_FOR_NOTIFICATIONS(). If you didn't plan to have this local storage in the backend it would be difficult to guarantee that clients would handle this situation correctly. Perhaps that was obvious already. If so, sorry for worrying for nothing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Copyright question
Bruce Momjian wrote: FYI, I have received permission, below, to remove the Andrew Yu copyright. Thanks. Can't we just remove the file outright? The last release of Ultrix was in 1995. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Copyrights on files
Bruce Momjian wrote: Someone has pointed out to me that we have non-PostgreSQL/Berkeley copyrights on a number of files: src/port/rint.c * Copyright (c) 1999, repas AEG Automation GmbH src/backend/port/dynloader/aix.c * This is an unpublished work copyright (c) 1992 HELIOS Software GmbH * 30159 Hannover, Germany src/backend/utils/mb/Unicode/UCS_to_GB18030.pl # Copyright 2002 by Bill Huang The above copyright mentions have no actual BSD copyright text, so what do we do? If we assume they are BSD copyrights that match our own, we perhaps don't have a problem. If not, we do have a problem. The one that is a more serious issue is in /pgtop/contrib/pgcrypto/blf.*, where the BSD advertising clause is in the copyright, and the author has updated the copyright: *must display the following acknowledgement: * This product includes software developed by Niels Provos. I will try to contact all the authors to get permission to clean this up. --- * Blowfish block cipher for OpenBSD * Copyright 1997 Niels Provos [EMAIL PROTECTED] * All rights reserved. * * Implementation advice by David Mazieres [EMAIL PROTECTED]. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * 1. Redistributions of source code must retain the above copyright *notice, this list of conditions and the following disclaimer. * 2. Redistributions in binary form must reproduce the above copyright *notice, this list of conditions and the following disclaimer in the *documentation and/or other materials provided with the distribution. * 3. All advertising materials mentioning features or use of this software *must display the following acknowledgement: * This product includes software developed by Niels Provos. * 4. The name of the author may not be used to endorse or promote products *derived from this software without specific prior written permission. * -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Copyright question
Neil Conway wrote: Bruce Momjian wrote: FYI, I have received permission, below, to remove the Andrew Yu copyright. Thanks. Can't we just remove the file outright? The last release of Ultrix was in 1995. Yea, but that was the easy one because I already knew the author and had his recent email address. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Patch for pg_dump
Patch withdrawn by author, perhaps reworked in the future. --- Dany DeBontridder wrote: Sorry I forgot the attach :-) It is not perfect so bear with me, it is my first try. Regards, D. On 3/21/07, Bruce Momjian [EMAIL PROTECTED] wrote: And the patch is so small, it is invisible (missing). ;-) --- Dany DeBontridder wrote: Here is a (small) patch to give the ability to pg_dump to export only the functions (or only one), very useful when you often develop with psql ( postgresql.8.2.3) Usage: pg_dump -Q function_name DATABASE export function_name pg_dump -QDATABASE export all the functions This patch is distributed under the BSD licence Regards, D. PS: I hope it is the correct ml, otherwise excuse me, it is the first time I propose a patch for postgresql -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TOASTing smaller things
Luke Lonergan wrote: I advocate the following: - Enable specification of TOAST policy on a per column basis As a first step, then: - Enable vertical partitioning of tables using per-column specification of storage policy. How are these different from ALTER TABLE SET STORAGE? They need to be more specific. --- - Luke On 3/21/07 1:12 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Is this a TODO? --- Jan Wieck wrote: On 3/21/2007 2:05 PM, Tom Lane wrote: Chris Browne [EMAIL PROTECTED] writes: #define TOAST_DENOMINATOR 17 /* Use this as the divisor; current default behaviour falls from TOAST_DENOMINATOR = 4 */ #define TOAST_TUPLE_THRESHOLD^I\ ^IMAXALIGN_DOWN((BLCKSZ - \ ^I^I^I^I MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ ^I^I^I^I / TOAST_DENOMINATOR) Given that you are quoting code that was demonstrably broken since the original coding of TOAST up till a month or two back, it passes regression is not adequate proof of it's right. In fact I think it's not right; you have not got the roundoff condition straight. 4. A different mechanism would be to add a fifth storage column strategy (the present four are PLAIN, EXTENDED, EXTERNAL, MAIN), let's say, TOAST. FORCE_COMPRESSION, FORCE_EXTERNAL and FORCE_EXTERNAL_UNCOMPRESSED. Anything along this line would require invoking the toaster on every single tuple, since we'd always have to crawl through all the columns to see if toasting was supposed to happen. No thanks. Not necessarily. A flag in Relation telling if the table has any column marked like that could be set while constructing the relcache entry. Which of these sounds preferable? It's a bit late in the cycle to be proposing any of these for 8.3. Certainly. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug: Buffer cache is not scan resistant
Simon, is this patch ready to be added to the patch queue? I assume not. --- Simon Riggs wrote: On Mon, 2007-03-12 at 09:14 +, Simon Riggs wrote: On Mon, 2007-03-12 at 16:21 +0900, ITAGAKI Takahiro wrote: With the default value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in pool, just like existing sequential scans. Is this intended? Yes, but its not very useful for testing to have done that. I'll do another version within the hour that sets N=0 (only) back to current behaviour for VACUUM. New test version enclosed, where scan_recycle_buffers = 0 doesn't change existing VACUUM behaviour. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 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] Proposal: Adding JIS X 0213 support
Tatsuo, Related to this, when are we going to get the Japanese po files in the core distribution? --Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SCMS question
Andrew Dunstan wrote: Jeremy Drake wrote: rsync -avzCH --delete rsync.postgresql.org::pgsql-cvs cvsroot/ The buildfarm howto has somewhat more complete instructions (including how to adjust the various cvs config files if you need to). I set it up the other day - took me about 10 minutes. http://pgfoundry.org/docman/view.php/140/4/PGBuildFarm-HOWTO.txt - see point 12. I have updated our documenation to mention rsync as a method of pulling the cvs repositiry, and added your URL above for additional information. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/cvs.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/cvs.sgml,v retrieving revision 1.41 diff -c -c -r1.41 cvs.sgml *** doc/src/sgml/cvs.sgml 1 Feb 2007 00:28:16 - 1.41 --- doc/src/sgml/cvs.sgml 27 Mar 2007 01:30:14 - *** *** 27,34 /para para ! At least two methods, ! anonymous CVS and productnameCVSup/productname, are available to pull the productnameCVS/productname code tree from the productnamePostgreSQL/productname server to your local machine. /para --- 27,34 /para para ! At least three methods, anonymous CVS, productnamersync/productname, ! and productnameCVSup/productname, are available to pull the productnameCVS/productname code tree from the productnamePostgreSQL/productname server to your local machine. /para *** *** 270,280 /para /sect1 sect1 id=cvsup titleGetting The Source Via productnameCVSup/productname/title para !An alternative to using anonymous CVS for retrieving the productnamePostgreSQL/productname source tree is productnameCVSup/productname. productnameCVSup/productname was developed by --- 270,308 /para /sect1 + sect1 id=rsync + titleGetting The Source Via productnamersync/productname/title + + para +An alternative to using anonymous CVS for retrieving the +productnamePostgreSQL/productname source tree is +productnamersync/productname, an incremental file transfer tool. +A major advantage to using productnamersync/productname is that it +can reliably replicate the emphasisentire/emphasis CVS repository +on your local system, allowing fast local access to commandcvs/ +operations such as optionlog/option and optiondiff/option. +Other advantages include fast synchronization to the +productnamePostgreSQL/productname server due to an efficient +streaming transfer protocol which only sends the changes since the last +update. + /para + + para +You can download the CVS repository using this command: + programlisting + rsync -avzCH --delete rsync.postgresql.org::pgsql-cvs cvsroot/ + /programlisting +For full instructions, see the rsync section in the +ulink url=http://pgfoundry.org/docman/view.php/140/4/PGBuildFarm-HOWTO.txt; +pgbuildfarm instructions/ulink. + /para + /sect1 + sect1 id=cvsup titleGetting The Source Via productnameCVSup/productname/title para !Another alternative to using anonymous CVS for retrieving the productnamePostgreSQL/productname source tree is productnameCVSup/productname. productnameCVSup/productname was developed by *** *** 283,298 ulink url=http://www.freebsd.org;FreeBSD project/ulink. /para - para -A major advantage to using -productnameCVSup/productname is that it can reliably -replicate the emphasisentire/emphasis CVS repository on your local system, -allowing fast local access to commandcvs/ operations such as optionlog/option -and optiondiff/option. Other advantages include fast synchronization to -the productnamePostgreSQL/productname server due to an efficient -streaming transfer protocol which only sends the changes since the last update. - /para - sect2 titlePreparing A productnameCVSup/productname Client System/title --- 311,316 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Do we want to do anything about this for 8.3?
---
Jim C. Nasby wrote:
Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
return 25:00:00, not 1 day 1:00.
I agree with Tom that this should be changed; I'm just arguing that we
might well need a backwards-compatibility solution for a while. At the
very least we'd need to make this change very clear to users.
On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
One problem with removing justify_hours() is that this is going to
return '24:00:00', rather than '1 day:
test= select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
00:00:00'::timestamptz;
?column?
--
24:00:00
(1 row)
---
Jim Nasby wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/
seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to
the
subtraction result for themselves. Not sure what the fallout would
be,
though.
I suspect there's applications out there that are relying on that
being nicely formated for display purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(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
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Proposal: Adding JIS X 0213 support
Tatsuo, Related to this, when are we going to get the Japanese po files in the core distribution? No idea. In my understanding, current message translating system has serious problem if wrong locale and encoding is provided(has this issue been solved in 8.3?). AFAIK Hiroki Kataoka, chairman of JPUG has same impression. Japanese po files are managed by JPUG and it would be better to ask him or someone from JPUG who is responsible for Japanese po files. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(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] Proposal: Adding JIS X 0213 support
Tatsuo, Related to this, when are we going to get the Japanese po files in the core distribution? No idea. In my understanding, current message translating system has serious problem if wrong locale and encoding is provided(has this issue been solved in 8.3?). AFAIK Hiroki Kataoka, chairman of JPUG has same impression. Japanese po files are managed by JPUG and it would be better to ask him or someone from JPUG who is responsible for Japanese po files. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] --enable-xml instead of --with-libxml?
Where are we on this? Peter thought the consistency makes sense, but if we can provide functionality that doesn't require libxml, why not do it? --- Nikolay Samokhvalov wrote: On 2/21/07, Alvaro Herrera [EMAIL PROTECTED] wrote: I think it would be better that leaving --with-libxml out (i.e. compiling without libxml2 support) would only disable those parts in XML functionality that require libxml2 for their implementation; the rest of the stuff should be compiled in regardless of the setting. Is this not what is done currently? The thing is that some functions of XML support are based on libxml2, some are not. libxml2 contains useful routines to deal with XML data. Now we have: XMLELEMENT uses such routines and XMLPI doesn't. Actually, all SQL/XML publishing function could be implemented w/o libxml2 -- but it's more convenient to use those routines in some cases... And there is no guarantee that functions that don't currently use libxml2 will not use them in future. What I want to propose is just simplification -- consider all XML stuff as one package, including XML type, SQL/XML publishing, XPath funcs, additional publishing functions recently added by Peter (btw, who knows -- maybe libxml2 will help to improve them somehow in future?), etc. -- Best regards, Nikolay ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 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] Proposal: Adding JIS X 0213 support
Tatsuo Ishii [EMAIL PROTECTED] writes: Related to this, when are we going to get the Japanese po files in the core distribution? No idea. In my understanding, current message translating system has serious problem if wrong locale and encoding is provided(has this issue been solved in 8.3?). That's certainly true, and it's not solved. But how does keeping the Japanese po files out of the distribution improve the matter? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal: Adding JIS X 0213 support
Tatsuo Ishii [EMAIL PROTECTED] writes: Related to this, when are we going to get the Japanese po files in the core distribution? No idea. In my understanding, current message translating system has serious problem if wrong locale and encoding is provided(has this issue been solved in 8.3?). That's certainly true, and it's not solved. But how does keeping the Japanese po files out of the distribution improve the matter? Keeping out po files until the problem is solved is just my opinion. If JPUG (or Japanese po files maintainers/volunteers) decide to include them into PostgreSQL distribution, I have no right to prevent it. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Adding JIS X 0213 support
Hi. - Original Message - From: Tom Lane [EMAIL PROTECTED] Tatsuo Ishii [EMAIL PROTECTED] writes: Related to this, when are we going to get the Japanese po files in the core distribution? No idea. In my understanding, current message translating system has serious problem if wrong locale and encoding is provided(has this issue been solved in 8.3?). That's certainly true, and it's not solved. But how does keeping the Japanese po files out of the distribution improve the matter? We are doing the support including the trouble. It was thought that the place of JPUG was preferable for the reasons why they were problems too peculiar to Japan. Then, The system of the support of Honda-san who was the representative of the document team had functioned enough up to now. However, it is not the one to refuse to do the distribution with the main body. It should discuss it again in the document team for the reasons why the one that was the effort to match to the release schedule of the main body becomes stronger. Anyway, Please wait for the response from Honda-san for a while. Regards, Hiroshi Saito ---(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] tsearch_core for inclusion
Teodor Sigaev wrote: For given schema and server's locale, it's possible to have several FTS configurations, but the only one (with special flag enabled) could be used as default. Current (active) FTS configuration contains in GUC variable tsearch_conf_name. If it's not defined, then FTS configuration is looked in search_path to match server's locale with default flag enabled. Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? Having one DEFAULT configuration per schema per locale will necessarily cause confusion if search_path is not set carefully I think. greetings, Florian Pflug ---(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: [PERFORM] [HACKERS] EXISTS optimization
On 3/23/07, Kevin Grittner [EMAIL PROTECTED] wrote: [...] That's the good news. The bad news is that I operate under a management portability dictate which doesn't currently allow that syntax, since not all of the products they want to It doesn't really touch the substance, but I am curious: are you not even allowed to discriminate between products in your code like: if db is 'postresql' then ... else ... ? What would be the rationale for that? Thanks Peter cover support it. I tried something which seems equivalent, but it is running for a very long time. I'll show it with just the explain while I wait to see how long the explain analyze takes. [...] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Full page writes improvement, code update
Hi, Here's an update of a code to improve full page writes as proposed in http://archives.postgresql.org/pgsql-hackers/2007-01/msg01491.php and http://archives.postgresql.org/pgsql-patches/2007-01/msg00607.php Update includes some modification for error handling in archiver and restoration command. In the previous threads, I posted several evaluation and shown that we can keep all the full page writes needed for full XLOG crash recovery, while compressing archive log size considerably better than gzip, with less CPU consumption. I've found no further objection for this proposal but still would like to hear comments/opinions/advices. Regards; -- Koichi Suzuki pg_lesslog.tgz Description: Binary data ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [BUGS] Relation not found error but table exits.
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 This also ties into the discussions we've had off-and-on about making catalog lookups behave in an MVCC fashion instead of using SnapshotNow. I'm still pretty hesitant to go there, but maybe we could do something involving MVCC for unlocked lookups and then SnapshotNow for (re)reading a table's schema info once we've got lock on it. No ideas, but a strong +1 for making catalog lookups MVCC. Can this perhaps become a TODO so we don't forget about it and possibly entice people to give it a go? :) - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200703262326 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGCI8ZvJuQZxSWSsgRA71vAKCNHCRtQUhxVoYKiSmxUAohFSE6TgCeN5qt sdb4PWjhBn+6sepNPTWkArQ= =18qw -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] notification payloads
Andrew Dunstan [EMAIL PROTECTED] writes: ... But ISTM that means we just need to pick a few strategic spots that will call CHECK_FOR_NOTIFICATIONS() even in the middle of a transaction and store them locally. Minor comment --- I don't believe in having a separate sprinkle of notify-specific checks. It needs to be set up so that CHECK_FOR_INTERRUPTS will deal with the catch-up-please signal. We've already done (most of) the work of making sure CHECK_FOR_INTERRUPTS is called often enough, and AFAICS we'd end up needing CHECK_FOR_NOTIFICATIONS in exactly those same loops anyway. It definitely helps here that CHECK_FOR_NOTIFICATIONS need affect only localized state of a particular subsystem that nothing else depends on. I've been wishing we could handle SI inval at more places than we do now, but that seems a lot harder :-( 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] notification payloads
Ühel kenal päeval, E, 2007-03-26 kell 14:07, kirjutas Tom Lane: Alvaro Herrera [EMAIL PROTECTED] writes: Why have the name on each message? Presumably names are going to be few compared to the total number of messages, so maybe store the names in a separate hash table and link them with a numeric identifier. That gives you room for a lot more messages. That can be done by the application, if its notify payloads are such that that's a useful optimization. However it seems entirely possible to me that the payload strings might be nonrepeating and the overhead of a separate table completely wasted. What we could do is use one name for many messages/listeners/notifies, so that in case we have 10 backends listening to ACCOUNTS_CHANGE', then we can keep the ACCOUNTS_CHANGE part only once, and reuse it's id also for LISTENs. That would get the same storage savings as Alvaros proposed hash and only be live during the time whenthere are any listeners. So perhaps it Alvaros proposal can be rephrased thus: Why have the name on each message? The names are already stored in listen table, just reuse numeric identifier pointing to item in that table. That gives you room for a lot more messages. If there is no name in listen table, it means that nobody is interested and the message can be dropped right away. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(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
