Re: [PATCHES] psql backslash consistency
Tom Lane wrote: Greg Sabino Mullane [EMAIL PROTECTED] writes: Attached is my backslash consistency patch which basically makes all the backslash commands behave as \dt does: \d* shows non-system objects, and \d*S shows system objects. Could we have a way to turn this off? At least for functions and operators? For my usage, at least, this will be a serious step backwards in usefulness. I see hardly any use case for showing only user-defined functions or types by default. I think consistency is not necessarily desirable here. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] psql backslash consistency
On Fri, 2005-05-27 at 03:45, Peter Eisentraut wrote:
Tom Lane wrote:
Greg Sabino Mullane [EMAIL PROTECTED] writes:
Attached is my backslash consistency patch which basically makes
all the backslash commands behave as \dt does: \d* shows non-system
objects, and \d*S shows system objects.
Could we have a way to turn this off? At least for functions and
operators? For my usage, at least, this will be a serious step
backwards in usefulness.
Do you have an implementation in mind? I'm having trouble coming up with
a way to do it cleanly.
I see hardly any use case for showing only user-defined functions or
types by default. I think consistency is not necessarily desirable
here.
See the archives for previous discussion and/or use cases.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] psql backslash consistency
Robert Treat [EMAIL PROTECTED] writes: Do you have an implementation in mind? I'm having trouble coming up with a way to do it cleanly. A psql \set variable to choose the behavior seems like a reasonable compromise. Perhaps it could list the \d commands that should include system objects by default --- that would give considerable customization flexibility. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] psql backslash consistency
Peter Eisentraut wrote: Robert Treat wrote: I see hardly any use case for showing only user-defined functions or types by default. I think consistency is not necessarily desirable here. See the archives for previous discussion and/or use cases. I didn't find any. Nevertheless, while there are undoubtedly some uses for everything, making this the default behavior does not seem acceptable. I think the logical issue is that a database with no user tables is useless/empty, so showing only user tables makes sense, while a database with no user functions is still useful, and in fact I would think most databases have no user functions. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] psql backslash consistency
On Friday 27 May 2005 15:09, Peter Eisentraut wrote:
Robert Treat wrote:
I see hardly any use case for showing only user-defined functions
or types by default. I think consistency is not necessarily
desirable here.
See the archives for previous discussion and/or use cases.
I didn't find any. Nevertheless, while there are undoubtedly some uses
for everything, making this the default behavior does not seem
acceptable.
ISTM it is more acceptable than you're willing to admit.
http://archives.postgresql.org/pgsql-hackers/2005-04/msg9.php
http://archives.postgresql.org/pgsql-hackers/2005-04/msg00102.php
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00199.php
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] Fix PID file location?
I have generated the following patch that moves postmaster.pid into the configuration directory. pg_ctl only knows about the configuration directory because it can't read postgresql.conf, so it seems that is the right place to move it. I have tested it and it seems to work. I would like to backpatch this to 8.0.X because it is currently broken without it. This patch does require that the postgres unix user have write permission in the configuration directory to create the pid file on startup. --- Josh Berkus wrote: Hey, folks, I've noticed a problem with alternate PGDATA locations. Here's how to reproduce: On 8.0.2 on RHAS4: 1) Initdb a directory (on my system, /pgdata/pgdata) 2) Move the .conf files to an alternate location ( /etc/pgsql/) 3) Set $PGDATA to the alternate location ( /etc/pgsql ) 4) Edit postgresql.conf to support this file arrangement data_directory = '/pgdata/pgdata' 5) pg_ctl start PostgreSQL 6) pg_ctl stop PostgreSQL 7) Get an error: No PID file found. The problem seems to be that pg_ctl expects the PID file to be in $PGDATA, but the file actually gets written by the postmaster to the actual data directory. You can work around this by setting external_pid_file, but this then prevents you from using external_pid_file for another purpose. Seems like it should be a relatively easy fix, although I'm not sure whether the postmaster should write the PID to $PGDATA, or whether pg_ctl should be made to look in the right place. Probably the latter. --- More about this: due to the PID file not being in the right place, pg_ctl stop never reports success: waiting for postmaster to shut down... failed pg_ctl: postmaster does not shut down This appears to be because the duplicate PID in the conf directory is not removed on shutdown. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/runtime.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v retrieving revision 1.321 diff -c -c -r1.321 runtime.sgml *** doc/src/sgml/runtime.sgml 25 May 2005 02:56:15 - 1.321 --- doc/src/sgml/runtime.sgml 27 May 2005 20:46:42 - *** *** 309,318 para While the commandpostmaster/command is running, its acronymPID/acronym is stored in the file ! filenamepostmaster.pid/filename in the data directory. This is ! used to prevent multiple commandpostmaster/command processes ! running in the same data directory and can also be used for ! shutting down the commandpostmaster/command process. /para sect2 id=postmaster-start-failures --- 309,319 para While the commandpostmaster/command is running, its acronymPID/acronym is stored in the file ! filenamepostmaster.pid/filename in the data or configuration ! directory. This is used to prevent multiple ! commandpostmaster/command processes running in the same data ! directory and can also be used for shutting down the ! commandpostmaster/command process. /para sect2 id=postmaster-start-failures *** *** 4933,4940 Alternatively, you can send the signal directly using commandkill/. The acronymPID/ of the commandpostmaster/command process can be found using the commandps/command program, or from the file !filenamepostmaster.pid/filename in the data directory. For !example, to do a fast shutdown: screen $ userinputkill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`/userinput /screen --- 4934,4941 Alternatively, you can send the signal directly using commandkill/. The acronymPID/ of the commandpostmaster/command process can be found using the commandps/command program, or from the file !filenamepostmaster.pid/filename in the data or configuration directory. !For example, to do a fast shutdown: screen $ userinputkill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`/userinput /screen Index: src/backend/bootstrap/bootstrap.c === RCS file: /cvsroot/pgsql/src/backend/bootstrap/bootstrap.c,v retrieving revision 1.204 diff -c -c -r1.204 bootstrap.c *** src/backend/bootstrap/bootstrap.c 6 May 2005 17:24:52 - 1.204 --- src/backend/bootstrap/bootstrap.c 27 May 2005 20:46:43 - *** *** 367,373 /* If standalone, create lockfile for data directory */ if (!IsUnderPostmaster) !
Re: [PATCHES] modified farsi faq in html and txt version
FAQ's updated for 8.0.X and current. Thanks. --- Mahmoud Taghizadeh wrote: Please update site. With Regards, --taghi __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Fix PID file location?
Bruce Momjian wrote: I have generated the following patch that moves postmaster.pid into the configuration directory. pg_ctl only knows about the configuration directory because it can't read postgresql.conf, so it seems that is the right place to move it. Files that are not actually configuration files, to be edited by users, do not belong in the configuration directory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Fix PID file location?
Bruce Momjian said: I have generated the following patch that moves postmaster.pid into the configuration directory. pg_ctl only knows about the configuration directory because it can't read postgresql.conf, so it seems that is the right place to move it. this seems wrong ... wouldn't it be better to teach pg_ctl at least enough about the config file to enable it to find the data dir? cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Fix PID file location?
Bruce Momjian pgman@candle.pha.pa.us writes: I have generated the following patch that moves postmaster.pid into the configuration directory. pg_ctl only knows about the configuration directory because it can't read postgresql.conf, so it seems that is the right place to move it. Unfortunately, that is *absolutely* unsafe. If we do that it will break the safety property that the lock file is meant to enforce in the first place, namely only one postmaster running in a given data directory. It's not too hard to imagine people getting burnt by that, either: initdb, create new config files in another directory, forget to remove the original postgresql.conf in the data directory, and you have every ingredient needed for disaster. Just start two postmasters with both direct and indirect -D arguments, and kaboom. This patch does require that the postgres unix user have write permission in the configuration directory to create the pid file on startup. That assumption is unacceptable, too. One of the prime reasons for having config files somewhere else is that the somewhere else can be read-only, thus reducing your exposure in case of a security breach. (Otherwise, we could possibly fix this by generating a second postmaster.pid in the config directory.) I really think we have only two choices: teach pg_ctl how to dig the data directory location out of postgresql.conf, or revert the separate-config-file-location patch. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] psql backslash consistency
On Fri, May 27, 2005 at 04:16:15PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: How about a psql config option? It should default to show only non-system objects, as that is the most generally useful behavior. There seems to be a distinct lack of unanimity about that judgment ;-) Well, yes, _across Postgres hackers_. But if we were to ask pgsql-general I have a feeling we would measure more weight on one side. Now, with your suggestion of having a config entry which would allow to set the default independently for each kind of object, maybe we should consider setting different defaults for each: for example, user-only for tables and functions, but user-and-system for operators. -- Alvaro Herrera (alvherre[a]surnet.cl) Si quieres ser creativo, aprende el arte de perder el tiempo ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] psql backslash consistency
Robert Treat [EMAIL PROTECTED] writes: On Friday 27 May 2005 15:09, Peter Eisentraut wrote: I didn't find any. Nevertheless, while there are undoubtedly some uses for everything, making this the default behavior does not seem acceptable. ISTM it is more acceptable than you're willing to admit. http://archives.postgresql.org/pgsql-hackers/2005-04/msg9.php http://archives.postgresql.org/pgsql-hackers/2005-04/msg00102.php http://archives.postgresql.org/pgsql-hackers/2004-09/msg00199.php As near as I can tell, opinion is divided about fifty-fifty among those who have bothered to weigh in ... which is hardly a strong case for making an enforced change in long-standing behavior (since those who want a change have more of an incentive to say so than those who don't). A customization variable is definitely sounding like the way to go if we're going to do anything here. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Fix PID file location?
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, May 27, 2005 at 07:40:17PM -0400, Tom Lane wrote: I really think we have only two choices: teach pg_ctl how to dig the data directory location out of postgresql.conf, I don't think this is extremely hard, isn't it? One small problem is that I think the current definition allows the data directory to be specified relative to the original postmaster working directory. Of course, that's not different from -D . on the postmaster command line, so possibly the answer is if you want to use pg_ctl, don't do that. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] psql backslash consistency
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, May 27, 2005 at 04:16:15PM -0400, Tom Lane wrote: There seems to be a distinct lack of unanimity about that judgment ;-) Well, yes, _across Postgres hackers_. But if we were to ask pgsql-general I have a feeling we would measure more weight on one side. Yeah, but which side ;-) ? I think the pg-general population would have a very much higher fraction of people who have no user-defined functions and therefore would see no value in \df not showing system functions. If we put in a config variable, that at least lowers the stakes for the losing side in the argument about what the default should be. Without that, I think there will be some serious flamewars ahead... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Fix PID file location?
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, May 27, 2005 at 07:40:17PM -0400, Tom Lane wrote: I really think we have only two choices: teach pg_ctl how to dig the data directory location out of postgresql.conf, I don't think this is extremely hard, isn't it? One small problem is that I think the current definition allows the data directory to be specified relative to the original postmaster working directory. Of course, that's not different from -D . on the postmaster command line, so possibly the answer is if you want to use pg_ctl, don't do that. I don't see any way to accurately find the data directory location. Reading postgresql.conf is one way, but what if they set data_directory on the command line using postmaster -o? Is reading postgresql.conf from pg_ctl without a parser really accurate? Shame we can't attach and do SHOW data_directory on a backend. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] AllocSetReset improvement
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- a_ogawa wrote: Tom Lane [EMAIL PROTECTED] writes: a_ogawa [EMAIL PROTECTED] writes: It is a reasonable idea. However, the majority part of MemSet was not able to be avoided by this idea. Because the per-tuple contexts are used at the early stage of executor. Drat. Well, what about changing that? We could introduce additional contexts or change the startup behavior so that the ones that are frequently reset don't have any data in them unless you are working with pass-by-ref values inside the inner loop. That might be possible. However, I think that we should change only aset.c about this article. I thought further: We can check whether context was used from the last reset even when blocks list is not empty. Please see attached patch. The effect of the patch that I measured is as follows: o Execution time that executed the SQL ten times. (1)Linux(CPU: Pentium III, Compiler option: -O2) - original: 24.960s - patched : 23.114s (2)Linux(CPU: Pentium 4, Compiler option: -O2) - original: 8.730s - patched : 7.962s (3)Solaris(CPU: Ultra SPARC III, Compiler option: -O2) - original: 37.0s - patched : 33.7s regards, --- Atsushi Ogawa [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] hash index work
Neil, I have added these item to the TODO list. Do you plan on applying this? --- Neil Conway wrote: This patch implements two changes to hash indexes: - rather than storing the values of the index keys, we just store their hash value instead. The hash opclasses have been marked lossy so that the executor will recheck the scan qualifier to catch hash collisions. - within a page of a hash bucket, the entries are stored sorted by hash value. When doing a hash index scan, we can then binary search within a page rather than using a linear search. Unfortunately, I haven't been able to measure any performance improvement from either of these changes :-\ I've run tests on two types of tables: int4 keys and relatively short textual keys (I don't think there's much point testing longer keys: the patches should obviously be a win there, so I'm concerned about speeding up the common case). In both cases, the difference in index scan performance isn't significant: it's about the same with and without the patches. The indexes have been on tables with 1 million rows (of int4) and 400,000 rows (of text). I would test with larger tables, but creating hash indexes is so slow that even these sizes are painful enough. Since indexes of this size should be cached in memory the reduction in I/O for the text case isn't being measured (the index is about 30% smaller than it is when we store the full text value), but even so I would have expected the binary search to produce noticeable gains... Perhaps I've made a coding error that has pessimized the performance somehow (although nothing obvious shows up in profiles), or perhaps the linear scan of the pages of the hash bucket isn't a performance problem in the first place, at least for types with a cheap equality operator. Some oprofile data seems to support this -- for example, in the int4 case, we spend less than 0.5% of the time in _hash_next and children, and only 1.8% of the runtime in hashgetmulti and children (with the vanilla CVS HEAD code). -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] patches for items from TODO list
I have extracted the COPY \x part of your patch, and have attached it for later application to CVS for 8.1. --- Sergey Ten wrote: Hello all, Thank you to all who replied for suggestions and help. Enclosed please find code changes for the following items: - Allow COPY to understand \x as a hex byte, and - Add XML output to COPY The changes include implementation of the features as well as modification of the copy regression test. After a careful consideration we decided to - put XML implementation in the backend and - use XML format described below, with justification of our decision. The XML schema used by the COPY TO command was designed for ease of use and to avoid the problem of column names appearing in XML element names. XML doesn't allow spaces and punctuation in element names but Postgres does allow these characters in column names; therefore, a direct mapping would be problematic. The solution selected places the column names into attribute fields where any special characters they contain can be properly escaped using XML entities. An additional attribute is used to distinguish null fields from empty ones. The example below is taken from the test suite. It demonstrates some basic XML escaping in row 2. Row 3 demonstrates the difference between an empty string (in col2) and a null string (in col3). If a field is null it will always be empty but a field which is empty may or may not be null. Always check the value of the 'null' attribute to be sure when a field is truly null. ?xml version='1.0'? table row col name='col1' null='n'Jackson, Sam/col col name='col2' null='n'\h/col /row row col name='col1' null='n'It is quot;perfectquot;./col col name='col2' null='n'#09;/col /row row col name='col1' null='n'/col col name='col2' null='y'/col /row /table Please let us know if about any concerns, objections the proposed change may cause. Best regards, Jason Lucas, Sergey Ten SourceLabs -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 11, 2005 7:11 PM To: Sergey Ten Cc: pgsql-hackers@postgresql.org; [EMAIL PROTECTED] Subject: Re: [HACKERS] patches for items from TODO list Sergey Ten wrote: Hello all, We would like to contribute to the Postgresql community by implementing the following items from the TODO list (http://developer.postgresql.org/todo.php): . Allow COPY to understand \x as a hex byte . Allow COPY to optionally include column headings in the first line . Add XML output to COPY The changes are straightforward and include implementation of the features as well as modification of the regression tests and documentation. Before sending a diff file with the changes, we would like to know if these features have been already implemented. Please check the web site version. Someone has already implemented Allow COPY to optionally include column headings in the first line. As far as XML, there has been discussion on where that should be done? In the backend, libpq, or psql. It will need discussion on hackers. I assume you have read the developer's FAQ too. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/ref/copy.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.65 diff -c -c -r1.65 copy.sgml *** doc/src/sgml/ref/copy.sgml 7 May 2005 02:22:45 - 1.65 --- doc/src/sgml/ref/copy.sgml 28 May 2005 03:49:10 - *** *** 424,436 entryBackslash followed by one to three octal digits specifies the character with that numeric code/entry /row /tbody /tgroup /informaltable ! Presently, commandCOPY TO/command will never emit an octal-digits ! backslash sequence, but it does use the other sequences listed above ! for those control characters. /para para --- 424,441 entryBackslash followed by one to three octal digits specifies the
Re: [PATCHES] [HACKERS] Fix PID file location?
Bruce Momjian pgman@candle.pha.pa.us writes: Is reading postgresql.conf from pg_ctl without a parser really accurate? The brute-force solution is to duplicate guc-file.l. That seems pretty ugly but in the long run it might be the most maintainable solution. We eventually gave up trying to have a cut-rate SQL lexer in psql, and duplicated parser/scan.l. Might be best to just go for that solution up front in this case. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
