Re: [HACKERS] RIP: Buildfarm member Baiji ??
The extinction of a dolphin ... sign of things to come? I've got a dolphin too (somebody's doing this on purpose ;): http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=tucuxibr=HEAD A few hours ago I realized the vmware instance it's running in had been suspended in a snapshot for the last few days... 1 extinct, 1 in coma - not going too well for dolphins these days ;) Bye, Chris. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] no cascade triggers?
Hello, triggers in PostgreSQL are cascading by default. From section 34.1. of the 8.2 manual: If a trigger function executes SQL commands then these commands may fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to cause a recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that inserts an additional row into the same table, causing the INSERT trigger to be fired again. It is the trigger programmer's responsibility to avoid infinite recursion in such scenarios. On the italian list we're discussing a case were a user reportedly worked around this (i.e. got rid of unwanted cascading calls) by writing an on insert trigger procedure something on the lines of: ALTER TABLE tab DISABLE TRIGGER USER; -- do more inserts into the same table ALTER TABLE tab ENABLE TRIGGER USER; While this reporetedly worked well in 8.2.1 it does not in 8.2.4 resulting in an error: ERROR: relation distinta_base1 is being used by active queries in this session Stato SQL: 55006 Now -- while we agree that disabling a trigger from inside itself is a somewhat strange thing to do, we cannot see a good and easy solution to the problem (of avoiding cascading trigger calls). General question: would a no cascade clause for triggers be a todo item? Special question: any recomendations for our user? He has a somewhat large number of triggers that do the alter table trick - working around it by means of some context-based logic would be a lot of work... Bye :) Chris. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Stamp major release 8.3.0,
On Fri, 12 Jan 2007 10:09:34 +0100 Joachim Wieland [EMAIL PROTECTED] wrote: On Fri, Jan 12, 2007 at 09:29:36AM +0100, Stefan Kaltenbrunner wrote: ok, but then we have some hosts in the buildfarm that run the updated versions like zebra and spoonbill. In this case we can't decide on the OS version number and cannot provide alternative files. Any idea except for actively replacing the offending line via sed from the regression script? that is incorrect - both zebra(4.0) and spoonbill(3.9) are not affected by this bug - the libc issue in question only affects i386 and m68k with OpenBSD 4.0 and older. So neither Spoonbill (Sparc64) nor Zebra (amd64/x86_64) ever had that issue. Okay, so it also depends on the platform... In this case I suggest to add the special expected/ files only for guppy, i.e. only for i386-unknown-openbsd3.8. If we get another i386 or m68k host that runs one of the affected systems, we have to update the check. However, if guppy got upgraded to 4.0 we'd have the problem that both guppy and emu would return i386-unknown-openbsd4.0 (while emu is running 4.0-current and hence is not affected)... Attached patch enables the special expected files only for i386-unknown-openbsd3.8. Ok, I feel sorry, guppy is causing so much trouble :| I guess then I'm going to upgrade her only when 4.1-stable comes out (in May). (i keep having this idea that if we all run current/unstable versions of our OSes we might overlook other issues ...) Bye, Chris. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [COMMITTERS] pgsql: Stamp major release 8.3.0,
Attached is a patch to get guppy green again (hopefully). The two new files go into src/interfaces/ecpg/test/expected Hi, I just wanted to mention that the latest release of OpenBSD i386 (4.0) is still broken too. So the ecpg-check failure would apply to (at least) to 3.8, 4.0, and likely 3.9. Bye :) Chris. PS: OpenBSD 4.0 current is fixed, but I was reluctant to update to current... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] error compiling 8.2 in debian sarge
i'm using debian sarge and when trying to compile 8.2beta3 got this fail... Just checked: no problems here on a stock 3.1. What ./configure switches did you use? Bye, Chris. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] error compiling 8.2 in debian sarge
i'm using debian sarge and when trying to compile 8.2beta3 got this fail... Just checked: no problems here on a stock 3.1. What ./configure switches did you use? ./configure --prefix=/usr/local/pgsql/pgsql --enable-debug--enable-cassert --enable-depend Again, no problem here. Some version infos are below. Bye, Chris. [EMAIL PROTECTED]:~$ uname -a Linux guest05 2.4.27-3-386 #1 Mon May 29 23:50:41 UTC 2006 i686 GNU/Linux [EMAIL PROTECTED]:~$ gcc --version gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) Copyright (C) 2003 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. [EMAIL PROTECTED]:~$ ld --version GNU ld version 2.15 Copyright 2002 Free Software Foundation, Inc. This program is free software; you may redistribute it under the terms of the GNU General Public License. This program has absolutely no warranty. [EMAIL PROTECTED]:~$ dpkg -l | grep gcc ii gcc3.3.5-3The GNU C compiler ii gcc-3.33.3.5-13 The GNU C compiler ii gcc-3.3-base 3.3.5-13 The GNU Compiler Collection (base package) ii gcc-3.4-base 3.4.3-13sarge1 The GNU Compiler Collection (base package) ii libgcc13.4.3-13sarge1 GCC support library ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Query Failed, out of memory
FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. That's sort of what I was afraid off. I am trying to get 100 million records into a text file in a specific order. Sigh, I have to write a quick program to use a cursor. :-( Why don't you try the psql client from 8.2beta1 then? This way you don't have to write the program yourself and you're helping out with beta testing as well :-) See FETCH_COUNT in http://developer.postgresql.org/pgdocs/postgres/app-psql.html Bye, Chris. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please to technical check of upcoming release
http://pgfoundry.org/docman/view.php/147/233/release82.zip is a zip file of a draft of the PostgreSQL 8.2 release and accompanying press kit. Please check if the technical details are correct, and get back to me with any corrections by Thursday. Hi :) I still see Theo Scholossenagle there. I realize the quote is not ready anyway, but why not fix the name in the mean time: it should be Theo Schlossnagle. Bye, Chris. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign keys
First of all i'de like to apolagize cause my poor english. After this, i shuould say that i beleavee a year ago i brought this problem to the community but i donn't remember some answering about it. The problem is: Every time a users misses a external refrenced key the PGSql raises an exception. Well as far as i realise if we had 5 or 10 Foreign keys during an Insert/Update transaction only exception should be raised reporting all erros/messages after last external refrenced field missed at one time,not one by one. Well, in order to implement this idea we will need to desable the built-in refencial integrety and build it all by your self- all the validation (look-ups etc..) before insert/update If tg_op='insert' or tg_op='update' then as people do with non relational Databases - all hand-made. Well, this is very hard to beleave!!! I must be missing something. Please i'must be wrong can some one explain me what i'm missing? When there is a constraint violation, the current transaction is rolled back anyhow. What's the purpose of letting you insert 1000 records, then, at the end say: hah, all is rolled back becauase the 2nd record was invalid. PG justly throws the exception immediately to let you know it's futile inserting 998 more records. I don't see a problem here. Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ECPG regression failures on OpenBSD
After upgrading some of my buildfarm members to the latest version of the buildfarm script both OpenBSD boxes startet to fail the ECPG regression tests: ... complex/test2 gets a segmentation fault on both machines. Could you try running it under gdb to see where it segfaults? I will try to get a hand on an OpenBSD machine myself, too will try to get a backtrace soon - if you are trying to do your own testing keep in mind that both boxes of mine do run with special malloc-settings (as in FGJZ) as discussed in: http://archives.postgresql.org/pgsql-hackers/2005-06/msg00817.php while I'm not sure yet that those are causing the errors to show up it seems quite likely since they tend to catch hidden memory allocation errors. Hi, I've just upgraded guppy, the only other OpenBSD machine that builds head to the latest buildfarm version. I don't have any special malloc settings. When guppy finishes, we will see what happens. Bye, Chris. -- Chris Mair http://www.1006.org ---(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] ECPG regression failures on OpenBSD
will try to get a backtrace soon - if you are trying to do your own testing keep in mind that both boxes of mine do run with special malloc-settings (as in FGJZ) as discussed in: http://archives.postgresql.org/pgsql-hackers/2005-06/msg00817.php while I'm not sure yet that those are causing the errors to show up it seems quite likely since they tend to catch hidden memory allocation errors. Hi, I've just upgraded guppy, the only other OpenBSD machine that builds head to the latest buildfarm version. I don't have any special malloc settings. When guppy finishes, we will see what happens. Hi, after a spurious run due to a config mistake after upgrading (sorry), guppy just now completed a new run and also failed on make ecpg check: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=guppydt=2006-09-05% 2010:02:05 Michael, if you want shell access to guppy, just contact me privately. Warning: guppy too, is somewhat dated (1:10 hours for the make step) :/ Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] updated patch for selecting large results
On Tue, 2006-08-29 at 18:31 -0400, Tom Lane wrote: [EMAIL PROTECTED] writes: here comes the latest version (version 7) of the patch to handle large result sets with psql. As previously discussed, a cursor is used for SELECT queries when \set FETCH_COUNT some_value 0 Applied with revisions ... I didn't like the fact that the code was restricted to handle only unaligned output format, so I fixed print.c to be able to deal with emitting output in sections. This is not ideal for aligned output mode, because we compute column widths separately for each FETCH group, but all the other output modes work nicely. I also did a little hacking to make \timing and pager output work as expected. regards, tom lane Cool! I specially like that as a side effect of your work for applying this, psql is faster now. Thanks to all people that helped with this (lots...:) Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] updated patch for selecting large results sets in
On Mon, 2006-08-28 at 13:45 -0400, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: Wait a minute. What I thought we had agreed to was a patch to make commands sent with \g use a cursor. This patch changes SendQuery so that *every* command executed via psql is treated this way. That's what I remembered. I don't think we want to introduce a difference between ; and \g. Have we measured the performance impact, then? The last time I profiled psql, GetVariable was already a hotspot, and this introduces another call of it into the basic query loop whether you use the feature or not. regards, tom lane Hi, after agreeing on using a \set variable, I proposed to have it influence \g as well as ;, because I thought that would be the most expected behaviour. IMHO I'm with Peter, that introducing a difference between \g and ; would go against the principle of least surprise. Performance-wise I took for granted without checking that GetVariable's running time would be negligible. [looks at the code] I see it's it's basically two function calls with a loop over a linked list of values (in the order of 10) doing strcmps and one strtol. It is not quite clear to me what the impact of this is. I could imagine it would show up only if you perform lots of trivial queries through psql. I'm going to benchmark something now and report back. Anyway, regardless the benchmark, I feel it's somehow not clean to have a variable introduce a difference between \g and ;. [goes benchmarking...] Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] updated patch for selecting large results
Performance-wise I took for granted without checking that GetVariable's running time would be negligible. [looks at the code] I see it's it's basically two function calls with a loop over a linked list of values (in the order of 10) doing strcmps and one strtol. It is not quite clear to me what the impact of this is. I could imagine it would show up only if you perform lots of trivial queries through psql. I'm going to benchmark something now and report back. Anyway, regardless the benchmark, I feel it's somehow not clean to have a variable introduce a difference between \g and ;. [goes benchmarking...] Ok, so I ran a file containing 1 million lines of select 1; through psql (discarding the output). 5 runs each with the patch and with the patch removed (the if() in SendQuery commented). These are the results in seconds user time of psql on a Pentium M 2.0 GHz (real time was longer, since the postmaster process was on the same machine). patch | count | avg | stddev ---+---+-+--- f | 5 | 16.6722 | 0.359759919946455 t | 5 | 17.2762 | 0.259528803796329 The conclusion is that, yes, the overhead is measurable, albeit with a very synthetic benchmark (of the type MySQL wins ;). Basically I'm loosing 0.6 usec on each query line (when FETCH_COUNT is not there and therefore psql need to scan the whole variables list in GetVariable() for nothing). Not sure if that's acceptable (I'd say yes, but then again, I'm not a cycle counter type of programmer *cough* Java *cough* ;)... Opinions? Bye, Chris. -- Chris Mair http://www.1006.org ---(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] [PATCHES] updated patch for selecting large results
The conclusion is that, yes, the overhead is measurable, albeit with a very synthetic benchmark (of the type MySQL wins ;). OK, so about 3 or 4% overhead added to extremely short queries. More accurately, that 3 or 4% overhead is added to about all queries (we're talking *psql*-only running time). It's just that for anything but short queries, psql running time totally dwarfs regarding to postmaster running time anyway. That's not enough to kill this patch, but it's still annoying ... and as I mentioned, there are some existing calls of GetVariable that are executed often enough to be a problem too. It strikes me that having to do GetVariable *and* strtol and so on for these special variables is pretty silly; the work should be done during the infrequent times they are set, rather than the frequent times they are read. I propose that we add the equivalent of a GUC assign_hook to psql's variable facility, attach an assign hook function to each special variable, and have the assign hook transpose the value into an internal variable that can be read with no overhead. If we do that then the cost of the FETCH_COUNT patch will be unmeasurable, and I think we'll see a few percent savings overall in psql runtime from improving the existing hotspot uses of GetVariable. Barring objections, I'll hack on this this evening ... Might help. Take into account the strtol is not critical at all for FETCH_COUNT, since when it's actually set, we're supposed to retrieving big data where a strtol doesn't matter anyway. The overhead comes from scanning the linked list for nothing in the normal case (when it's not set). I don't know how the other uses factor in here, but I see it's called at least twice more on average calls to SendQuery. Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] updated patch for selecting large results sets in
I am confused. I assume \g and ; should be affected, like Peter says. Tom, what *every* command are you talking about? You mean \d? Like I said, I thought we were intending to modify \g's behavior only; that was certainly the implication of the discussion of \gc. At some point you OKed the \g and ; proposal. I admit I was quick adding the and ; part, but it seemed so natural once we agreed on using a variable. OK, got it. I just don't see the value to doing \g and not ;. I think the \gc case was a hack when he didn't have \set. Now that we have \set, we should be consistent. I agree with this statement. If we have a variable that switches just between two versions of \g, we could have gone with using \u (or whatever) in the first place. In the mean time I have been converted by the variable camp, and I think the variable should change \g and ; together, consistently. If we find we can't live with the performance overhead of that if(FETCH_COUNT), it is still not clear why we would be better off moving it into the \g code path only. Is it because presumably \g is used less often in existing psql scripts? Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
Hi,
thanks for reviewing this :)
attached is the new and fixed version of the patch for selecting
large result sets from psql using cursors.
The is_select_command bit is wrong because it doesn't allow for left
parentheses in front of the SELECT keyword (something entirely
reasonable when considering big union/intersect/except trees).
Also you'd need to allow for VALUES as the first keyword.
You're right, I improved is_select_command to take these into account.
(Btw, I didn't even know a command VALUES existed..)
But isn't the whole thing unnecessary? ISTM you could just ship the
query with the DECLARE CURSOR prepended, and see whether you get a
syntax error or not.
I find it neat that \u gives a good error message if someone
executes a non-select query. If I leave that out there is no way to tell
a real syntax error from one cause by executing non-selects...
Anyway, if we don't want the extra check, I can skip the
is_select_command call, of course.
Patch with fix against current CVS is attached.
At some point we ought to extend libpq enough to expose the V3-protocol
feature that allows partial fetches from portals; that would be a
cleaner way to implement this feature. However since nobody has yet
proposed a good API for this in libpq, I don't object to implementing
\u with DECLARE CURSOR for now.
BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
some other name we could use?
True :)
Since buffer commands all have a single char I wanted a single char one
too. The c for cursor was taken already, so i choose the u (second
char in cursor). If somebody has a better suggestion, let us know ;)
Bye, Chris.
PS: I'm traveling Fri 18th - Fri 25th and won't check mail often.
--
Chris Mair
http://www.1006.org
diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:54:41.0 +0200
***
*** 1201,1206
--- 1201,1231
/listitem
/varlistentry
+
+ varlistentry
+ termliteral\u/literal [ { replaceable class=parameterfilename/replaceable | literal|/literalreplaceable class=parametercommand/replaceable } ]/term
+
+ listitem
+ para
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in replaceable
+ class=parameterfilename/replaceable or pipes the output
+ into a separate Unix shell executing replaceable
+ class=parametercommand/replaceable.
+ Unlike literal\g/literal, literal\u/literal works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore literal\u/literal uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exeeds the client's memory resources.
+ literal\u/literal always gives unaligned output. It does, however
+ use the current field separator (see command\pset/command).
+ literal\u/literal gives an error when trying to execute something
+ that is not a SELECT statement.
+ /para
+ /listitem
+ /varlistentry
+
+
varlistentry
termliteral\help/literal (or literal\h/literal) literal[ replaceable class=parametercommand/replaceable ]/literal/term
listitem
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c 2006-08-17 16:55:25.0 +0200
***
*** 830,835
--- 830,866
}
}
+ /*
+ * \u executes the current query buffer using a cursor
+ */
+ else if (strcmp(cmd, u) == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+
+ if (query_buf query_buf-len == 0)
+ {
+ if (!quiet)
+ {
+ puts(_(Query buffer is empty.));
+ fflush(stdout);
+ }
+ }
+ else
+ {
+ status = PSQL_CMD_SEND_USING_CURSOR;
+ }
+ }
+
/* \unset */
else if (strcmp(cmd, unset) == 0)
{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h 2006-08-17 16:55:25.0 +0200
***
*** 16,21
--- 16,22
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_USING_CURSOR, /* query complete; send off using cursor */
PSQL_CMD_SKIP_LINE,/* keep building query */
PSQL_CMD_TERMINATE
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
Replying to myself... Patch with fix against current CVS is attached. Alvaro Herrera sent two fixes off-list: a typo and at the end of SendQueryUsingCursor I sould COMMIT, not ROLLBACK. So, one more version (6) that fixes these too is attached. Bye, Chris. PS: I'm keeping this on both lists now, hope it's ok. -- Chris Mair http://www.1006.org ---(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] [PATCHES] selecting large result sets in psql using
Patch with fix against current CVS is attached.
Forgot the attachment... soory.
--
Chris Mair
http://www.1006.org
diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 18:02:29.0 +0200
***
*** 1201,1206
--- 1201,1231
/listitem
/varlistentry
+
+ varlistentry
+ termliteral\u/literal [ { replaceable class=parameterfilename/replaceable | literal|/literalreplaceable class=parametercommand/replaceable } ]/term
+
+ listitem
+ para
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in replaceable
+ class=parameterfilename/replaceable or pipes the output
+ into a separate Unix shell executing replaceable
+ class=parametercommand/replaceable.
+ Unlike literal\g/literal, literal\u/literal works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore literal\u/literal uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exceeds the client's memory resources.
+ literal\u/literal always gives unaligned output. It does, however
+ use the current field separator (see command\pset/command).
+ literal\u/literal gives an error when trying to execute something
+ that is not a SELECT statement.
+ /para
+ /listitem
+ /varlistentry
+
+
varlistentry
termliteral\help/literal (or literal\h/literal) literal[ replaceable class=parametercommand/replaceable ]/literal/term
listitem
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c 2006-08-17 18:02:49.0 +0200
***
*** 830,835
--- 830,866
}
}
+ /*
+ * \u executes the current query buffer using a cursor
+ */
+ else if (strcmp(cmd, u) == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+
+ if (query_buf query_buf-len == 0)
+ {
+ if (!quiet)
+ {
+ puts(_(Query buffer is empty.));
+ fflush(stdout);
+ }
+ }
+ else
+ {
+ status = PSQL_CMD_SEND_USING_CURSOR;
+ }
+ }
+
/* \unset */
else if (strcmp(cmd, unset) == 0)
{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h 2006-08-17 16:55:25.0 +0200
***
*** 16,21
--- 16,22
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_USING_CURSOR, /* query complete; send off using cursor */
PSQL_CMD_SKIP_LINE,/* keep building query */
PSQL_CMD_TERMINATE,/* quit program */
PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/common.c 2006-08-17 18:40:51.0 +0200
***
*** 28,33
--- 28,34
#include command.h
#include copy.h
#include mb/pg_wchar.h
+ #include mbprint.h
/* Workarounds for Windows */
***
*** 52,58
((T)-millitm - (U)-millitm))
#endif
!
static bool command_no_begin(const char *query);
/*
--- 53,59
((T)-millitm - (U)-millitm))
#endif
! static bool is_select_command(const char *query);
static bool command_no_begin(const char *query);
/*
***
*** 952,957
--- 953,1146
/*
+ * SendQueryUsingCursor: send the (SELECT) query string to the backend
+ * using a cursor and print out results.
+ *
+ * Unlike with SendQuery(), single step mode, ON_ERROR_ROLLBACK mode,
+ * timing and format settings (except delimiters) are NOT honoured.
+ *
+ * Returns true if the query executed successfully, false otherwise.
+ */
+ bool
+ SendQueryUsingCursor(const char *query)
+ {
+ PGresult *results;
+ bool started_txn = false;
+ PQExpBufferData buf;
+ FILE *queryFout_copy = NULL;
+ bool queryFoutPipe_copy = false;
+ intntuples, nfields = -1;
+ inti, j;
+
+ if (!pset.db)
+ {
+ psql_error(You are currently not connected to a database.\n);
+ return false;
+ }
+
+ if (!is_select_command(query))
+ {
+ psql_error(Need a SELECT command to perform cursor fetch
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
BTW, \u seems not to have any mnemonic value whatsoever ... isn't there some other name we could use? True :) Since buffer commands all have a single char I wanted a single char one too. The c for cursor was taken already, so i choose the u (second char in cursor). If somebody has a better suggestion, let us know ;) I think a new backslash variable isn't the way to go. I would use a \pset variable to control what is happening. IMHO with \pset I'd have different places where I'd need to figure out whether to do the cursor thing and I was a bit reluctant to add stuff to existing code paths. Also the other \pset options are somewhat orthogonal to this one. Just my two EUR cents, of course... :) Bye, Chris. -- Chris Mair http://www.1006.org ---(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] [PATCHES] selecting large result sets in psql using
Since buffer commands all have a single char I wanted a single char one too. The c for cursor was taken already, so i choose the u (second char in cursor). If somebody has a better suggestion, let us know ;) I think a new backslash variable isn't the way to go. I would use a \pset variable to control what is happening. That seems like it would be very awkward to use: you'd have to type quite a bit to go from one mode to the other. Personally I think that insisting on a one-letter command name is not such a good idea if you can't pick a reasonably memorable name. I'd suggest \gc (\g with a Cursor) or \gb (\g for a Big query) or some such. \gc sounds like a good idea to me :) (I must admit gc reminds me about 'garbage collector', which in a weired way is related with what we're doing here... At least more related than 'Great Britain' ;) So add it as a modifyer to \g? Yea, that works, but it doesn't work for ';' as a 'go' command, of course, which is perhaps OK. Yes, it was intended to differentiate this command from ';'; Bye, Chris. -- Chris Mair http://www.1006.org ---(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] newbie patch-attempt: selecting large result sets in psql
Hi :) powered by the great talk for newbie hackers by Neil Conway Gavin Sherry [1] at the anniversary summit I sneaked into the Code Sprint and started working a bit on a Todo from Neil's Code Sprint Page: Add a psql option to have it submit SELECT queries via a cursor, to avoid excessive memory consumption when manipulating large result sets Gavin gave me a sketch of a patch that added a new command line option to psql that would have it use cursors for selects. One of the problems with this was that a user would expect psql to work as usual (including all format and output option stuff) and to do this properly most of the psql output code would need to be refactored. Thinking about it, we had the idea to just introduce a new \ operator that would output the results of a select (using cursors) in an indipendent code path. Who's selecting gigabytes of stuff into a HTML table anyway? So, I've introduced a new \u command that from a user perspective is identical to \g (it executes the query buffer), just that it uses cursors and honours only the field separator. For example you could just do select a, c, g from big \u bigout.dat The patch is here [2], is working, but needs some performance improvment and double checking. Which I won't do before getting some feedback that what I'm doing does make any sense at all ;) Bye, Chris [1] http://neilconway.org/talks/hacking/ [2] http://www.1006.org/tmp/psql_cursor-3.patch ---(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] buildfarm stats
but it is about 2Gb of data, so just putting a dump cleaned of personal data somewhere isn't really an option. I could arrange a dump without the diagnostics, in these 2 tables: system: name | operating_system | os_version | compiler | compiler_version | architecture build: name | snapshot | stage | branch | build_flags (stage in the latter table is OK on success or the name of the stage that failed otherwise). But what do you want it for? And do you want it one-off or continuously? Nothing important at all. I'd just thought about a few interesting stats, like failures vs OS (as the first poster said) or failures vs gcc version or timings vs. arch / RAM or gcc version, etc. For the timings I guess there are some timestamps embedded that might be extracted... But I didn't really think about it, before posting (classic mailing list syndrome ;) Bye, Chris. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] buildfarm stats
Thanks for the stats Andrew. Out of interest, can you easily tabulate the number of failures against OS? Or, more generally, even put a dump of the DB (without personal infos of course :) somewhere? Bye, Chris. PS: and don't say you're running it in MySQL ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO item: support triggers on columns
I'd like to start working on the following TODO item: Referential Integrity / Support triggers on columns Is somebody else already working on this? Sorry for not jumping in earlier. As Rob said, I am working on column-level support for triggers. I did not have my name addded to the TODO list as I wanted to at least get a proof of concept going first to make sure I could undertake it. I am partway there, but there are still a lot of rough edges. I guess at this point I should formally put my name on the TODO, and can bail out if it gets over my head? Ok! Keep us up to date. Bye, Chris :) ---(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] TODO item: support triggers on columns
I'd like to start working on the following TODO item: Referential Integrity / Support triggers on columns Is somebody else already working on this? No one, I think. Isn't this the REFERENCING clause? I think there was a partial patch submitted already for this. Those are two different things: http://www.postgresql.org/docs/8.0/static/sql-createtrigger.html#SQL-CREATETRIGGER-COMPATIBILITY Bye, Chris. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] TODO item: support triggers on columns
Hello, I'd like to start working on the following TODO item: Referential Integrity / Support triggers on columns Is somebody else already working on this? Bye :) Chris. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] PostgreSQL 8.0.0 Release Candidate 4
On Sun, 2005-01-09 at 17:45, Peter Eisentraut wrote:
Well, it does not readily explain that 'infinity' is rejected as invalid
input syntax. You should get an overflow or some large value, at
least.
The relevant code is in src/backend/utils/adt/float.c.
You're right.
I had a look at the code.
It seems strtod() is to blame.
This sample program
#include stdio.h
#include stdlib.h
#include errno.h
int main()
{
char *endptr;
double val;
char *num = Infinity;
errno = 0;
val = strtod(num, endptr);
printf(input string is '%s'\n, num);
printf(strtod returned val = %lf\n, val);
printf(errno = %d\n, errno);
printf(endptr points to '%s'\n, endptr);
}
on the PlayStation 2 returns:
input string is 'Infinity'
strtod returned val = inf
errno = 0
endptr points to 'inity'
float.c's conversion routine cannot deal with a strtod that just eats
the 'Inf' part of 'Infinity', so the remaining 'inity' is causing the
syntax error (from line 522 in float.c).
Consistently, this works with the so compiled version of PG:
postgres=# select 'Inf'::float8;
float8
--
Infinity
(1 row)
Would it be worth treating this case apart in float.c, or should we just
blame the PlayStation's 2 Linux (quite old version 2.2.2) Gnu C library
to be broken?
I wonder how many implementations of strtod behave this way?
On a few other boxes of mine I've seen versions that accept 'Infinity'
as a whole or reject it as a whole
Bye, Chris.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] 8.0 beta 1 on linux-mipsel R5900
I've compiled 8.0 beta 1 on a R5900 V3.1 (a playstation 2) running Linux (PS2 Linux 1) for portability testing. ... I'm wondering: would it be hard to fix the assembly spinlock code for the R5900? According to the previous port report from Red Hat, the PS2 chip simply doesn't have any user-space TAS instruction, so you're pretty much stuck. If you can find something that works, let us know. I've asked about this issue on the ps2 linux forums and somebody came up with this document: http://lc.linux.or.jp/lc2001/papers/tas-ps2-paper.pdf See also this releated thread on the linux-mips list: http://www.linux-mips.org/archives/linux-mips/2002-01/msg00278.html I'm not fluent in Japanese or MIPS assembly, so I won't comment on these. Just posting the links :) Bye, Chris. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
