Re: Defaults for GUC variables (was Re: [HACKERS] pg_ctl reports succes when start fails)
On Mon, 27 Oct 2003 10:22:32 -0500, Tom Lane [EMAIL PROTECTED] wrote: The low-tech solution to this would be to stop listing the default values as commented-out entries, but just make them ordinary uncommented entries. Please not. How should we ask a newbie seeking assistance on one of the support lists to show his non-default config settings? Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] An interisting conundrum where tables have a column called found
I am putting together a DB that records information about a set of web
sites and how they link to one another. As one site refers to another, I
monitor the first site and then record when I find the referred site.
[snip]
I also have a function called add_site that adds the newly found site.
So far so good.
To test my code I wrote the INSERT statement by hand:
insert into sa_site (site_id, found, host_uri) values
(nextval('sa_site_id_seq'), 'now', 'www.endoid.net');
and everything worked fine when called from psql.
Then I added the code to my add_site function and got the following
error:
ensa1.1= select add_site('www.endoid.net', 4, null );
WARNING: Error occurred while executing PL/pgSQL function add_site
WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near $1 at character 43
I looked and looked but couldn't find anything that could explain the
error. Then, being somewhat used to Oracle I tried renaming the found
column to found_on. Oracle occasionally has discrepencies in its rules
for the naming of objects, so I thought that something *similar* might
be happening with PG. Anyways this change did work in my PL/pgSQL
function.
Could you guys figure out where a general description of please don't
use keywords as column names even if you're allowed to at create time
because something somewhere will throw an unintellligable error should
live on the site?
There is a SQL Key Words section, and I remember when porting to
postgres I saw complaints about a column named 'offset'.
So I assume there is a key word checking function already in operation.
Maybe it simply needs an update.
Regards, Christoph
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Open items
On Tue, 2003-10-28 at 00:02, Bruce Momjian wrote:
Marc G. Fournier wrote:
On Mon, 27 Oct 2003, Bruce Momjian wrote:
Marc G. Fournier wrote:
On Mon, 27 Oct 2003, Bruce Momjian wrote:
Changes
---
Allow superuser (dba?) the ability to turn off foreign key checks/all
constraints/triggers, not settable from postgresql.conf?
feature, not bug fix, no?
It became important when everyone realized that 7.4 would be first major
upgrade with full foreign key checking --- prior to that we did CREATE
CONSTRAINT TRIGGER that didn't check data. Basically, that's how it got
on the open item list.
Altho important, it is still a feature, and as such, should not be
critical to holding up the release ...
That's all I need --- a consensus that is isn't significant enough to be
on this list.
Does this prevent me from recreating databases that might have improper
data in the foreign key fields? If i would have been able to upgrade
these database in all prior versions but will now be prevented from
upgrading, then this is really a bug fix imho.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Open items
On Mon, Oct 27, 2003 at 11:32:45PM -0500, Tom Lane wrote: Have gcc use -g, add --disable-debug, rename? Personally I don't like the idea of this behavior defaulting differently depending on which compiler you use. I can see the practical arguments for doing so, but it still rubs me the wrong way. Can anyone offer new arguments pro or con here? Not an argument.. I use gcc, and I configure --enable-debug --enable-cassert. I was surprised reading the discussion that the --enable-debug was superfluous and thought it didn't feel right.. Cheers, Patrick ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Open items
Bruce Momjian wrote: Have gcc use -g, add --disable-debug, rename? Personally I don't like the idea of this behavior defaulting differently depending on which compiler you use. I can see the practical arguments for doing so, but it still rubs me the wrong way. Can anyone offer new arguments pro or con here? You and I think don't like the inconsistency, while Jan likes the debug where ever possible (gcc). There were a few others who liked the debug for gcc by default. I think if folks are debugging, they probably should turn off optimization anyway to make sense of the output, and we are never going to ship without optimization. What might be nice would be for --enable-debug to turn off optimization as well so people can actually make sense of the code in the debugger. Basically, I don't like the debug because of: inconsistency with non-gcc binary bloat binary bloat encourages strip, which is really bad Usually function names are enough for us to take a guess on the cause. I think I have a compromise for --enable-debug: How about if --enable-debug removes optimization, adds -g (or -g3 for macro debugging symbols in gcc), and maybe even enables casserts. That way, --enable-debug gives us a super-debuggable binary that we would never ship by default. Also, I can add a section to the release notes that discourages people running strip. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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/faqs/FAQ.html
Re: [HACKERS] Open items
Bruce Momjian wrote: Joshua D. Drake wrote: Hello, Well the reason I brought it up was the rather interesting discussion that Jan had today about Vacuum. I was wondering if we were going to explore that before the 7.4 release? No, I am afraid we are way past time time for that kind of addition. Couln't agree more. We have absolutely no plan what kind of cache algorithm or strategy we want as a replacement, what granularity of tuning options it might need and what good defaults would be. This is the kind of stuff that looks simple but needs a full development cycle like TOAST did. 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Slightly inconsistent behaviour in regproc?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Basically, my question is why ::regproc alone always addes the catalogue qualification in this case? regproc adds the schema if the name would be ambiguous without it (or not visible at all). In these cases, the function name is still ambiguous with the schema :-( ... but there's nothing regproc can do about that, since it's not chartered to emit function arguments. Use regprocedure instead if you don't want to see schema names. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Open items
Tom Lane wrote: Document new --describe-config postgres option Go to it. OK, that attached patch completes this item. I did not document --describe-config at the top as an accepted arg, but there was already a --name=value line. I added it to the bottom of the SEMI-INTERNAL OPTIONS section. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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/postgres-ref.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/postgres-ref.sgml,v retrieving revision 1.38 diff -c -c -r1.38 postgres-ref.sgml *** doc/src/sgml/ref/postgres-ref.sgml 16 Oct 2003 17:38:01 - 1.38 --- doc/src/sgml/ref/postgres-ref.sgml 28 Oct 2003 14:49:24 - *** *** 335,340 --- 335,351 /listitem /varlistentry + varlistentry + termoption--describe-config/option/term + listitem +para + This option dumps out the server's internal configuration variables, + descriptions, and defaults in tab-delimited commandCOPY/ format. + It is designed primarily for use by administration tools. +/para + /listitem + /varlistentry + /variablelist /refsect2 /refsect1 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open items
Bruce Momjian [EMAIL PROTECTED] writes: I think I have a compromise for --enable-debug: How about if --enable-debug removes optimization, adds -g (or -g3 for macro debugging symbols in gcc), and maybe even enables casserts. This strikes me as a completely arbitrary set of changes in long-established behavior. People who want to turn off optimization already know how to do it, and people who want asserts already know how to do that. Eliminating the functional difference between these --enable options isn't a step forward. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Open items
On Tue, Oct 28, 2003 at 01:09:36AM -0500, Bruce Momjian wrote: I am grouping the above two items together --- I thought the idea was to give people a way to load 7.4 in a fairly rapid manner --- we now have the ability to do ALTER TABLE ADD CONSTRAINT, but it lacks ANALYZE statistics, so it is kind of slow --- perhaps nothing can be done about this. Should we try to gather some statistics before doing the ALTER TABLE ADD CONSTRAINT queries if no stats exist? I am not advocating it, but just asking. Should COPY update the row count? Would that help? Maybe this is something to point out in the upgrading documents since that way it seems it could be put off to the next release? It sure sounds like a feature, and one about which there still seems to be fair disagreement. It would indeed be nice, but it doesn't sound like a show stopper to me if the proposal doesn't have anyone turning up with the code to back it. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Open items
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think I have a compromise for --enable-debug: How about if --enable-debug removes optimization, adds -g (or -g3 for macro debugging symbols in gcc), and maybe even enables casserts. This strikes me as a completely arbitrary set of changes in long-established behavior. People who want to turn off optimization already know how to do it, and people who want asserts already know How do you do it? CFLAGS= configure? how to do that. Eliminating the functional difference between these --enable options isn't a step forward. I was looking for something that would be a middle ground, and I thought a super-debug binary might to it. I do think we should consider -g3 for gcc. I didn't know it existed, and it does seem nice. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Compilation of PostgreSQL on Irix
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 8 Oct 2003, Robert E. Bruccoleri wrote: Dear Devrim, I have been using Postgres on Irix for over 8 years, and I have only used the SGI provided compilers. GCC doesn't work well on Irix. In addition, you can build a 64 bit version of PostgreSQL. Here's the script we used for snip (http://archives.postgresql.org/pgsql-hackers/2003-10/msg00422.php) (Sorry for the late response, I just could find time to try your solution) No, PostgreSQL 7.4beta4/5 does not still compile on SGI Irix, running on rs1. :( I've also tried to compile 7.3.4 but I got some errors,too. I'm not familiar to Irix, I'm trying to use my logic on Linux. I'm pretty sure that I have a problem with GCC.: = bash-2.05b$ /usr/freeware/bin/gcc -v Reading specs from /usr/freeware/lib/gcc-lib/mips-sgi-irix6.5/3.2.1/specs Configured with: ../configure --prefix=/usr/freeware - --enable-version-specific-runtime-libs --disable-shared --enable-threads - --enable-haifa --disable-c99 Thread model: single gcc version 3.2.1 = bash-2.05b$ bison --version GNU Bison version 1.25 (I know the problem with bison, but freeware.sgi.com just provides bison 1.35, not 1.875) So, shall we exclude IRIX support from v7.4? :( Regards, - -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE/npE+tl86P3SPfQ4RAtgdAKCpKan/xGUO2ioD1TDIpS+vOez0fwCeI+a0 WYrNfWB5uB/UUHTZyLWzARE= =Sjon -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Compilation of PostgreSQL on Irix
Dear Devrim, You can build the latest release of bison from www.gnu.org without any trouble under Irix. PostgreSQL 7.4 builds cleanly on Irix, and so far, it's much faster than 7.3 for the one database I've tested. --Bob +-++ | Robert E. Bruccoleri, Ph.D. | email: [EMAIL PROTECTED]| | President, Congenomics Inc. | URL: http://www.congen.com/~bruc | | P.O. Box 314| Phone: 609 818 7251| | Pennington, NJ 08534|| +-++ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Open items
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: This strikes me as a completely arbitrary set of changes in long-established behavior. People who want to turn off optimization already know how to do it, and people who want asserts already know How do you do it? CFLAGS= configure? I'd do CFLAGS=-O0 configure, but the other might work too. I think at one point the autoconf code treated empty CFLAGS as being unset, but we might have fixed that. how to do that. Eliminating the functional difference between these --enable options isn't a step forward. I was looking for something that would be a middle ground, and I thought a super-debug binary might to it. I do think we should consider -g3 for gcc. I didn't know it existed, and it does seem nice. The argument in favor of adding -g by default for gcc is based in very large part on the assumption that it doesn't cost any performance. Changing --enable-debug so that it *does* cost performance (by suppressing optimization) isn't a middle ground; it turns the switch into something useful only for developers, and guarantees that no binary used in the field will ever have debug info. I don't think we want that. My experience is that debugging optimized code is not as hard as you make it out to be --- I normally build with -O1 or -O2, because -O0 code has awful performance on HPPA. Only rarely will I recompile -O0 because I can't follow what's happening in a particular section of code. I'm not sure about -g3; how much does it bloat the executable? Does it work in every version of gcc? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Open items
On Tue, 2003-10-28 at 10:01, Bruce Momjian wrote: OK, that attached patch completes this item. I did not document --describe-config at the top as an accepted arg, but there was already a --name=value line. Why does '--name=value' suffice as documentation for '--describe-config'? I think you should add '--describe-config' to the syntax description at the top. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Compilation of PostgreSQL on Irix
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 28 Oct 2003, Robert E. Bruccoleri wrote: You can build the latest release of bison from www.gnu.org without any trouble under Irix. Is it just bison that creates the problem? I'll try on Thursday (it's national holiday in here) and will report to you. Regards, - -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE/npwqtl86P3SPfQ4RAnQGAKC5kZRvPnZhW4fe03ypWmQTdZPJfwCfaSpe UTk70+WJn0fSs9Y8Em+eGeE= =4/oS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Open items
Neil Conway wrote: On Tue, 2003-10-28 at 10:01, Bruce Momjian wrote: OK, that attached patch completes this item. I did not document --describe-config at the top as an accepted arg, but there was already a --name=value line. Why does '--name=value' suffice as documentation for '--describe-config'? I think you should add '--describe-config' to the syntax description at the top. OK, but it is going to look kind of big up there and isn't of general usefulness. Still want it? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Compilation of PostgreSQL on Irix
On Tue, 2003-10-28 at 11:41, Devrim GUNDUZ wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Tue, 28 Oct 2003, Robert E. Bruccoleri wrote:
You can build the latest release of bison from www.gnu.org
without any trouble under Irix.
Is it just bison that creates the problem?
bison should only be causing troubles if your building from cvs, not
from the beta package...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] bug? Drop column and SQL functions
Someone showed me this simple example:
regression=# CREATE TABLE test (a TEXT, b TEXT);
CREATE TABLE
regression=# INSERT INTO test VALUES ('foo', 'bar');
INSERT 17145 1
regression=# CREATE FUNCTION foo() RETURNS SETOF test as 'SELECT * FROM test' LANGUAGE
sql;
CREATE FUNCTION
regression=# SELECT * FROM foo();
a | b
-+-
foo | bar
(1 registro)
regression=# ALTER TABLE test DROP COLUMN a;
ALTER TABLE
regression=# SELECT * FROM foo();
ERROR: query-specified return row and actual function return row do not match
(note that I didn't specify a return record -- SETOF test should only
consider non-dropped columns ...)
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
La virtud es el justo medio entre dos defectos (Aristóteles)
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Open items
Andrew Sullivan wrote: On Tue, Oct 28, 2003 at 01:09:36AM -0500, Bruce Momjian wrote: I am grouping the above two items together --- I thought the idea was to give people a way to load 7.4 in a fairly rapid manner --- we now have the ability to do ALTER TABLE ADD CONSTRAINT, but it lacks ANALYZE statistics, so it is kind of slow --- perhaps nothing can be done about this. Should we try to gather some statistics before doing the ALTER TABLE ADD CONSTRAINT queries if no stats exist? I am not advocating it, but just asking. Should COPY update the row count? Would that help? Maybe this is something to point out in the upgrading documents since that way it seems it could be put off to the next release? It sure sounds like a feature, and one about which there still seems to be fair disagreement. It would indeed be nice, but it doesn't sound like a show stopper to me if the proposal doesn't have anyone turning up with the code to back it. It has to be put into the docs either way, as there still IS sort of a possibility for the DBA to get the data in without being checked. Version 7.4 pg_dump still has the --disable-triggers option, which only works for data-only dumps. So if someone want's to upgrade without running fkey checks v74/bin/pg_dump -d $dbname $dbname.schema.sql v74/bin/pg_dump -a --disable-triggers $dbname $dbname.data.sql then install 7.4, initdb and let psql slurp it up. It will loose some performance because of building the indexes during data load instead of CREATE INDEX after it. But I think it's still better than combing through millions of fkey references. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Open items
On Tue, 2003-10-28 at 12:57, Bruce Momjian wrote: OK, but it is going to look kind of big up there and isn't of general usefulness. Still want it? Well, as a matter of principle, I think it belongs there: if it's a command-line option, it should be documented in the section that claims to document the syntax of the command-line options. That said, I'm not militant about it... -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open items
Neil Conway wrote: On Tue, 2003-10-28 at 12:57, Bruce Momjian wrote: OK, but it is going to look kind of big up there and isn't of general usefulness. Still want it? Well, as a matter of principle, I think it belongs there: if it's a command-line option, it should be documented in the section that claims to document the syntax of the command-line options. That said, I'm not militant about it... Added. Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: [HACKERS] Open items
OK, doesn't look like we are going to add the ability to turn off constraint checking for reload, nor add ANALYZE as part of ALTER TABLE ADD FOREIGN KEY, so we only have a few items left. I think we are nearing the conclusion that --enable-debug is OK now (no -g without it), so the only remaining big item is the renaming of check_function_bodies. --- P O S T G R E S Q L 7 . 4 O P E NI T E M S Current at ftp://momjian.postgresql.org/pub/postgresql/open_items. Changes --- Rename dump GUC variable to be more generic Have gcc use -g, add --disable-debug, rename? Documentation Changes - -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: [HACKERS] Dump error
Jochem van Dieten [EMAIL PROTECTED] writes: Perhaps the pg_dump bug with procedural language handlers which have been created in the pg_catalog schema: http://archives.postgresql.org/pgsql-general/2003-01/msg6.php Since no better solution has emerged since January, I've applied a patch along the lines we discussed then (make pg_dump simply ignore languages for which it doesn't find the handler in finfo[]). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] psql copy help
The psql help for copy (version=7.3.2 and several others) appears
incorrect (or perhaps the command parser is at fault - in any case
the help doesn't match reality):
steve=# \h copy
Command: COPY
Description: copy data between files and tables
Syntax:
COPY table [ ( column [, ...] ) ]
FROM { 'filename' | stdin }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
...
I interpret this as meaning that you can optionally specify a
delimiter, null etc. and if you do then you can optionally include
the with and the as for readability. While I can omit the as I
cannot omit the with:
Works with both:
steve=# \copy foo from 'footest' with delimiter as ','
\.
Works with with only:
steve=# \copy foo from 'footest' with delimiter ','
\.
Does not work without with
steve=# \copy foo from 'footest' delimiter ','
\copy: parse error at 'delimiter'
steve=# \copy foo from 'footest' delimiter as ','
\copy: parse error at 'delimiter'
As such it seems that the help should be:
COPY table [ ( column [, ...] ) ]
FROM { 'filename' | stdin }
[ WITH
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
...
Cheers,
Steve
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql copy help
Steve Crawford [EMAIL PROTECTED] writes: The psql help for copy (version=7.3.2 and several others) appears incorrect (or perhaps the command parser is at fault - in any case the help doesn't match reality): You seem to be confusing the SQL command COPY with the psql command \copy. They are not the same. Possibly we should try to converge \copy's syntax with COPY's a little better... I'm not sure they could be made exactly alike though. 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: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not
On Tue, 28 Oct 2003, Tom Lane wrote: Note that tables, indexes, views and sequences relations in the 'pg_catalog' namespace are excluded even though they are in the current search path. I found not doing this produced annoying behaviour when expanding names beginning with 'p'. People who work with system tables a lot may not like this though; I can look for another solution if necessary. Ian Barwick AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? I also would expect any relations I can see to be picked up by tab completion. Jon ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I'm esperiencing problem with the TAB autocomplete on postgres 7.4beta5: #select * from pg_lTABTAB and no suggestions out. This appears to have been a deliberate change: 2003-03-27 11:45 momjian * src/bin/psql/tab-complete.c: Attached are two patches for psql's tab-completion.c. [snip] Note that tables, indexes, views and sequences relations in the 'pg_catalog' namespace are excluded even though they are in the current search path. I found not doing this produced annoying behaviour when expanding names beginning with 'p'. People who work with system tables a lot may not like this though; I can look for another solution if necessary. Ian Barwick AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? Might be better to: 1. make it a settable option and/or 2. include catalog objects in expansion iff we are expanding pg_ + optional suffix (probably best of both worlds). I rarely use completion in this way so I could be an unrepresentative user, though :-) cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Andrew Dunstan [EMAIL PROTECTED] writes: 2. include catalog objects in expansion iff we are expanding pg_ + optional suffix (probably best of both worlds). Hmm, that might be an okay compromise. Not sure how hard it is to implement ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Greg Stark [EMAIL PROTECTED] writes: I think I'm missing something. Why are pg_catalog.* tables in my search path at all? My search path seems to be set to $user,public. is pg_catalog implicitly appended there? Yes. See TFM: http://developer.postgresql.org/docs/postgres/ddl-schemas.html particularly section 5.8.5. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposed structure for coexisting major versions
On Mon, 2003-10-27 at 10:05, Neil Conway wrote:
On Sun, 2003-10-26 at 17:24, Oliver Elphick wrote:
If it were possible to have two separate versions of the PostgreSQL
packages installed simultaneously, it would be simple to do database
upgrades by dumping from the old version and uploading to the new.
You'd need some mechanism to prevent concurrent modifications of the
source DB during the upgrade process, wouldn't you?
Yes. The existing Debian mechanism (upgrading with the same package
names) does it by shutting down the postmaster and restarting the old
postmaster on port 5431 while a dump is done.
An adaptation of that process will be used to do an upgrade of a
particular database cluster:
pg_version_upgrade
--
A new program which will replace postgresql-dump [a Debian-only
program].
It will be used to migrate a cluster from one major version to another.
Options:
-c {cluster} the name of the cluster
-v {version} the version to upgrade to (the default is the latest
version installed)
-p {clusterpath} the new clusterpath (default = old clusterpath)
-d {dump directory} the directory in which to put the dump of the old
cluster (default = old clusterpath parent)
-rrecover; continue upgrading from a previous failure
Procedure:
1. initdb a new cluster in {clusterpath}.new/data for
the new major version
2. start a postmaster for the new cluster on port 5430
3. stop the postmaster for the old cluster
4. set the status field in cluster_ports to upgrading
5. start a postmaster for the old cluster on port 5431
6. pg_dumpall the old cluster {clustername}.dumpall
7. load the dump in the new cluster {dbname}.upgrade 21
8. if there are no errors, stop the two postmasters, else exit and
set status to failed-upgrade
9. move the old cluster directory to {clusterpath}.old and move
{clusterpath}.new to {clusterpath}; in cluster_ports, set the
status field back to its original value
10. start the postmaster for the new cluster
11. (with administrator approval only) delete the old cluster and
the dump file
(All operations are done with the software version appropriate to the
cluster version.)
Changes to my original proposal:
1. it is not necessary to keep the major version number in
cluster_ports, since it can be read from the cluster's PG_VERSION file.
It seems sensible to avoid duplicating that datum. The pathname held in
that file will not be PGDATA but its parent, and PGDATA will always be
{clusterpath}/data.
2. the active field in cluster_ports is renamed status, with the
values active, inactive, upgrading or failed-upgrade.
The latest version of the proposal is to be found at
http://cvs.alioth.debian.org/cgi-bin/cvsweb.cgi/~checkout~/common/postgresql-client.html?rev=1.1content-type=text/htmlcvsroot=pkg-postgresql
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
Cast thy burden upon the LORD, and he shall sustain
thee; he shall never allow the righteous to fall.
Psalms 55:22
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Call for port reports
Johan Henselmans [EMAIL PROTECTED] writes: I had trouble compiling postgressrc/pgsql/src/interfaces/ecpg/ecpglib and compiling pgsql/src/interfaces/ecpg/compatlib. Reason was I had asked during configure to include krb5 support. After adding the -lkrb5 flag to the Makefile in these subdirectories, everyting went fine. Okay, fixed. 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: [HACKERS] pg_ctl reports succes when start fails
We can also try to come up with a better scheme for verifying that we have started properly - I will think about that. There have been previous suggestions for a pg_ping functionality, in which you could simply send a packet to the postmaster and it would answer back if it's open for business. You can approximate this by sending a deliberately invalid login packet, but it's not quite the same thing. I think there were some concerns about security though; check the archives. Um, I wrote pg_ping and sent it to -patches but got no comments. http://archives.postgresql.org/pgsql-patches/2003-07/msg00053.php pg_ping is actually the basis for the threaded benchmark program I've got sitting in my tree, but I don't think folks here would look kindly on a -lpthread dependency given how up in the air pg's thread support/testing is at the moment. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Call for port reports
Certainly other alpha gcc platforms must have problems with -O2? I am inclined to add something to configure.in for all alpha compiles that changes -O2 to -O. I'm not. It's one thing if FreeBSD thinks their compiler is broken. But before I accept that gcc is broken as a whole, I want to hear from the GCC folks. Well, I have no insite into the gcc camp, but, my understanding is that gcc 3.3 for the alpha isn't broken, but for gcc 2.X, it's pretty horked with any level of optimization. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Open items
OK, doesn't look like we are going to add the ability to turn off constraint checking for reload, nor add ANALYZE as part of ALTER TABLE ADD FOREIGN KEY, so we only have a few items left. Hey - what about if you just delete the pg_constraint entries for all your foreign keys, then won't they all be dumped as CREATE CONSTRAINT TRIGGERs? Then, after restore, you can just re-run contrib/adddepend? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_ctl reports succes when start fails
- Original Message - From: Sean Chittenden [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Andrew Dunstan [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 7:59 PM Subject: Re: [HACKERS] pg_ctl reports succes when start fails We can also try to come up with a better scheme for verifying that we have started properly - I will think about that. There have been previous suggestions for a pg_ping functionality, in which you could simply send a packet to the postmaster and it would answer back if it's open for business. You can approximate this by sending a deliberately invalid login packet, but it's not quite the same thing. I think there were some concerns about security though; check the archives. Um, I wrote pg_ping and sent it to -patches but got no comments. http://archives.postgresql.org/pgsql-patches/2003-07/msg00053.php pg_ping is actually the basis for the threaded benchmark program I've got sitting in my tree, but I don't think folks here would look kindly on a -lpthread dependency given how up in the air pg's thread support/testing is at the moment. -sc At least those parts of that patch that alter pg_ctl seem to be moot given that we are moving to a C version of pg_ctl (Joshua Drake tells me that CommandPrompt is willing to do that work). cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? I had noticed that, but I had gotten used to it. I'm not fussed particularly either way, really... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Alvaro Herrera Munoz [EMAIL PROTECTED] writes: I found it very irritating at first, but when I discovered that I could tab my way to syscatalogs by using pg_catalog. as prefix, I started feeling it was actually a nice behavior. Hm. Okay, Ian isn't completely alone then ;-) I tried out that approach just now, though, and found that I still had to type at least pg_c before I could get any tab completion help at all. Another odd thing was that after completing pg_catalog., it wouldn't go any further --- one must type p here, even though all the possible completions begin pg_. (Possibly that could be fixed, but I don't know readline's behavior well enough to be sure.) So that's five typed characters and two tabs before one starts getting into the system catalogs. That seems like a lot of typing. If Ian were willing to type one more character of his p-something table names before hitting tab (I assume he's not calling them pg-something), then he'd not have a problem with the availability of tab completion for system catalogs. I think saving one keystroke for p-something user tables is a poor return for requiring seven keystrokes for system catalogs. Anyway, it seems like we need a vote to see how many people prefer each choice. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Alvaro Herrera Munoz wrote: On Tue, Oct 28, 2003 at 04:48:59PM -0500, Tom Lane wrote: AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. I found it very irritating at first, but when I discovered that I could tab my way to syscatalogs by using pg_catalog. as prefix, I started feeling it was actually a nice behavior. I found it similarly irritating at first, and it continues to irritate me. But that may be because I use system tables more frequently than the average Joe ;-) I guess I'd be happy to see tab completion reinstated for system tables (except toast tables as Tom suggested nearby), but I don't feel particularly zealous about it. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
On Tuesday 28 October 2003 23:47, Tom Lane wrote: Alvaro Herrera Munoz [EMAIL PROTECTED] writes: I found it very irritating at first, but when I discovered that I could tab my way to syscatalogs by using pg_catalog. as prefix, I started feeling it was actually a nice behavior. Hm. Okay, Ian isn't completely alone then ;-) I tried out that approach just now, though, and found that I still had to type at least pg_c before I could get any tab completion help at all. Another odd thing was that after completing pg_catalog., it wouldn't go any further --- one must type p here, even though all the possible completions begin pg_. (Possibly that could be fixed, but I don't know readline's behavior well enough to be sure.) I'm not sure whether it's intended or not, but explicitly adding pg_catalog to the search path alleviates this. So that's five typed characters and two tabs before one starts getting into the system catalogs. That seems like a lot of typing. If Ian were willing to type one more character of his p-something table names before hitting tab (I assume he's not calling them pg-something), Err ;-) db= \d p page_template_cache pg_ts_cfg_pkey page_template_cache_ov_idxpg_ts_cfgmap page_template_cache_pkey pg_ts_cfgmap_pkey page_template_cache_template_idx pg_ts_dict pg_catalog. pg_ts_dict_pkey pg_temp_1.pg_ts_parser pg_toast. pg_ts_parser_pkey pg_ts_cfg public. (The pg_ts_% are all from tsearch2 BTW. Though, they`re all in a schema of their own so explicitly addressing them would work equally well too). then he'd not have a problem with the availability of tab completion for system catalogs. I'm sure I can live with that (one more character), at least until I finish the brainwave input extension ;-). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] [HACKERS] Autocomplete TAB on Postgres7.4beta5 not working?
Rod Taylor [EMAIL PROTECTED] writes: I say leave it the way it is. If you want system table tab completion, simply: ALTER USER ... SET search_path =3D pg_catalog,...; Unfortunately, that *does not* affect the tab-completion behavior; it will still not offer the system catalogs as completions unless you explicitly prefix pg_catalog.. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Gaetano Mendola [EMAIL PROTECTED] writes: I'm esperiencing problem with the TAB autocomplete on postgres 7.4beta5: #select * from pg_lTABTAB and no suggestions out. This appears to have been a deliberate change: 2003-03-27 11:45 momjian * src/bin/psql/tab-complete.c: Attached are two patches for psql's tab-completion.c. [snip] Note that tables, indexes, views and sequences relations in the 'pg_catalog' namespace are excluded even though they are in the current search path. I found not doing this produced annoying behaviour when expanding names beginning with 'p'. People who work with system tables a lot may not like this though; I can look for another solution if necessary. Ian Barwick AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? 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: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? No one commented on it so it was applied --- now that we have two people who don't like it, seems we should back it out. Not the whole patch, certainly, since it added a bunch of other good stuff. I just want to take out the discrimination against pg_catalog. (BTW, the code also discriminates against pg_toast, but that part I don't have a problem with.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
On Tuesday 28 October 2003 22:48, Tom Lane wrote: AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? Guilty as charged? ;-) Just to clarify, the patch enables tab completion for catalog relations as long as the schema name pg_catalog is prepended. E.g. \d pg_c[tab].[tab] will get expansion for everything in pg_catalog. ISTR I found it very irritating that without this restriction \d p[tab] produces a very large number of selections for normal database work. Looking at a current database, I have about a dozen of relations beginning with p in the search path which I access with tab expansion frequently and I'm sure it would annoy me intensely if I got all the system tables as well every time. Mind you that's only my personal preference, I thought it might be unpopular but as no one has commented since... I can submit a correction but not today or tomorrow. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] [HACKERS] Autocomplete TAB on Postgres7.4beta5 not
On Tue, 28 Oct 2003, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: I say leave it the way it is. If you want system table tab completion, simply: ALTER USER ... SET search_path =3D pg_catalog,...; Unfortunately, that *does not* affect the tab-completion behavior; it will still not offer the system catalogs as completions unless you explicitly prefix pg_catalog.. It seems a good compromise then would be that if pg_catalog is in your search path, then do the old fashioned completion, i.e. ptab will list everything in both pg_catalog and the other schemas in your search path. Afterall, the system catalog kind of hitchhikes along for a ride in your search path. Most users aren't interested in the system catalogs, so having them suddenly show up when you just wanted the phonebook table is kinda a bother for them. Is it possible to remove the implicit search path of pg_catalog from a psql session without it breaking lots of stuff? If not, then it's kind of ugly to the user to have no way to get the system from proffering pg_catalog tables when they have no interest in them. If so, then why make it part of the default? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [HACKERS] Autocomplete TAB on Postgres7.4beta5 not
On Tue, 2003-10-28 at 18:49, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: I say leave it the way it is. If you want system table tab completion, simply: ALTER USER ... SET search_path =3D pg_catalog,...; Unfortunately, that *does not* affect the tab-completion behavior; it will still not offer the system catalogs as completions unless you explicitly prefix pg_catalog.. Very well.. Would it be enough simply to fix it so it does work? Remove the S functionality and allow pg_catalog to work like a normal schema. signature.asc Description: This is a digitally signed message part
Re: [BUGS] [HACKERS] Autocomplete TAB on Postgres7.4beta5 not working?
scott.marlowe [EMAIL PROTECTED] writes: Is it possible to remove the implicit search path of pg_catalog from a psql session without it breaking lots of stuff? Do you consider +, count(), etc to be important stuff? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] [HACKERS] Autocomplete TAB on Postgres7.4beta5 not
On Tue, 28 Oct 2003, Tom Lane wrote: scott.marlowe [EMAIL PROTECTED] writes: Is it possible to remove the implicit search path of pg_catalog from a psql session without it breaking lots of stuff? Do you consider +, count(), etc to be important stuff? Me, hardly ever use them :-) So I can assume that removing the implicit pg_catalog from the search path is a bad thing. In that case, does my proposed solution of having to implicitly include pg_catalog in your search path to get it to be seen by tab completion as just another schema make sense? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [HACKERS] Autocomplete TAB on Postgres7.4beta5 not
On Tue, 2003-10-28 at 19:34, scott.marlowe wrote: On Tue, 28 Oct 2003, Tom Lane wrote: scott.marlowe [EMAIL PROTECTED] writes: Is it possible to remove the implicit search path of pg_catalog from a psql session without it breaking lots of stuff? Do you consider +, count(), etc to be important stuff? Me, hardly ever use them :-) So I can assume that removing the implicit pg_catalog from the search path is a bad thing. From the search_path certainly -- but we can we teach the difference between implicit and explicit to the *is_visible functions? signature.asc Description: This is a digitally signed message part
