Re: [HACKERS] RFC: Security documentation
While I can understand your concern over security I simply do not know how you can protect against: On Sat, 7 Feb 2004, Alex J. Avriette wrote: ... or somebody may be passing in the de rigeur '; select * from sensitive_table; ...' attempts (this is very common, as you know, in CGI applications). Actually I can and it involves changing the backend to not permit multiple statements in one request. I can't imagine how that could sensibly be implemented, if at all, though. At some stage your interface code has to accept responsibility for preventing dangerous input from reaching libpq. Sure this means that if someone can bypass your that interface code then they can then inject the dangerous input but let's face it, if they're at that stage there's not a lot you can do to stop them submiting 'select * from sensitive_table' to the backend without all the leading/trailing crud to try and force that statement to execute in the middle of what should be a single statement. That immediately means that anything you've done to prevent multiple statements in one request is also bypassed. The program in question is a set of stored procedures which are called from Perl libraries (via DBD::Pg) I can't think of any way to ensure that malicious input is sanitized, from within plpgsql. From within perl, I can use DBI::quote, or I can come up with my own function using y///. The simplist way is to use place holders in a prepared statement and then execute the statement supplying the data for those placeholders. DBI escapes the data automatically. But when I began asking people what the final word was on the subject, if there was somebody who was willing to suggest a path to data security and stick by it, nobody could point you anywhere. Essentially, it boils down to this: I can't put in the documentation for my application well, some guy on IRC said that this was safe enough. I'd be fired if the application was compromised and the only checking I had done was by asking people on IRC. As such, I would like to see some documentation about securing the database at a data and application level. It would be nice to have some general guidelines, as well as being able to cite documentation when setting up a security policy for a database application. General guidlines for an application: Setup two db users, one is the owner of all the database objects, the other is granted select priviledges only on what it requires. If there is a exception that requires writing priviledges for the read-only side of the application, for example tracking pages a website visitor views, then create that interface function with owner execute flag. Oh, and did I mention, use functions, aka. stored procs, to do the work. Although that's a more contentious I think. That having been said, I would have submitted a patch with said documentation if I knew where to start. I have submitted this RFC -- a request for comments, nothing more serious than that -- because I'd like to know what we can do to get some documentation included in the next release. I don't feel that having zero documentation on this subject is acceptable. Are you saying here you _do_ have some documentation to contribute? -- Nigel J. Andrews ---(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] feature request... case sensitivity without double
On Thu, 15 Jan 2004, Pete wrote: Hi, I'm not sure if this is the correct place to make a feature request. If not hopefully I can be kindly pointed in that direction. I have several project that use MySQL and I would like to port them to PostgreSQL unfortunately they use a naming convention which uses upper case and lower case letters Example: SELECT AccountID FROM Account I am aware that if you enclose those table and column names with then postgresql will take the case into consideration. Only problem is most people who have current MySQL project have not written their statements with (MySQL parser uses no quotes of the ` back tick) and it would take considerable man power to convert each SQL statement. I've not tried this but if it were me I would try updating the names of the objects and columns in pg_class and pg_attribute so that they weren't mixed case. I wonder though, how did these get created in the db in mixed case in the first place? Your creation scripts must have created them using the double quotes around the names. That strikes me as an inconsitency which shouldn't have existed. However, as I say, I'd look at renaming things in the system tables to try and repair the situation. Don't forget to back everything up first. Unfortunately, the folding the lowercase isn't the best since names should be folded to upper case, however, I'm with the likes of Tom who prefer to see things in lowercase most of the time. I guess that's what FORTRAN does to one's brain after a while of trying to read it. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Use of 'cp -r' in CREATE DATABASE
On Thu, 11 Dec 2003, Alvaro Herrera wrote: On Thu, Dec 11, 2003 at 06:36:05PM -0500, Bruce Momjian wrote: Our dbcommands.c has for create database: snprintf(buf, sizeof(buf), cp -r '%s' '%s', src_loc, target_dir); [...] I think we should switch to -R in our code. But you will have to write special code for Win32, won't you? Maybe it would be better to avoid using system commands altogether and copy the whole thing using syscalls ... That was my immediate thought. Unfortunately that means reinventing the wheel; or grabbing it from BSD or somewhere and distributing it with postgresql. -- Nigel J. Andrews ---(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] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: Darcy Buskermolen [EMAIL PROTECTED] writes: On November 28, 2003 12:33 pm, Tom Lane wrote: Whoa. Try the following test program. Then send it in to your friendly local BSD hackers I've been running this code on a pair of FreeBSD (i386) boxen, for some time now, one of which is a 4.8-STABLE, the other is a 5.2-BETA. Could it be a hardware problem on Marc's box? Or specific to some other aspect of that installation (Marc, is pgsql74.hub.org multi-CPU, for example?) The failure is definitely quite repeatable on pgsql74.hub.org. I don't see it on svr1.postgresql.org, though, which seems to be running almost the same kernel. On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45, but they are all just a microsecond. On an Intel FreeBSD 3.3 I had one just after starting the program that was 2 secs behind. Then I stopped to restart with a leading under time and I've not seen any since. (4'50 it was running) .529 .2 user/sys time = 0.53 on the Linux system user/sys time = 0.2 on the FreeBSD one Make of that what you will. -- Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45, but they are all just a microsecond. What do you mean by just a microsecond? I mean it's always a out of order tv_usec... line and the difference is 1us. That is a.out gives: out of order tv_usec: 1070065862 374978, prev 1070065862 374979 out of order tv_usec: 1070065867 814300, prev 1070065867 814301 out of order tv_usec: 1070065868 794176, prev 1070065868 794177 out of order tv_usec: 1070065871 553831, prev 1070065871 553832 etc. Attached is a tightened-up test program that will only complain if the value of gettimeofday goes backward (at all) or forward by more than 10 seconds (adjustable as MAX_SKIP). This should be suitable to run on moderately loaded machines where the test program might occasionally not get dispatched for a few seconds. I don't think that would show any for me. I've only got two skips showing from a longer run on my FreeBSD 3.3 and those differ in the seconds counter by only 2. Time between the occurances is about 1100s. -- Nigel ---(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] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: On Fri, 28 Nov 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45, but they are all just a microsecond. What do you mean by just a microsecond? I mean it's always a out of order tv_usec... line and the difference is 1us. That is a.out gives: out of order tv_usec: 1070065862 374978, prev 1070065862 374979 out of order tv_usec: 1070065867 814300, prev 1070065867 814301 Fascinating. I'd call that a bug too, but evidently one with a different mechanism than the BSD issue we are chasing. FWIW, I have not seen any failures in a fair amount of runtime on a 2.4.18 (Red Hat 8.0) kernel here, running on a Dell P4. What is the hardware platform you're using? Ah, I have made a mistake. It's only a 2.2.18 kernal. Dual SMP P-III, perhaps that's the issue there. And on the FreeBSD system I've got this: $ time ./a.out 21 | tee a.txt out of order tv_sec: 1070066197 273140, prev 1070066195 721010 out of order tv_usec: 1070066197 273140, prev 1070066195 721010 out of order tv_sec: 1070067322 116061, prev 1070067320 440490 out of order tv_usec: 1070067322 116061, prev 1070067320 440490 out of order tv_sec: 1070067833 514969, prev 1070067831 755019 out of order tv_usec: 1070067833 514969, prev 1070067831 755019 ^C real38m53.026s user6m13.953s sys 32m6.589s So not very often there. -- Nigel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: $ time ./a.out 21 | tee a.txt out of order tv_sec: 1070066197 273140, prev 1070066195 721010 out of order tv_usec: 1070066197 273140, prev 1070066195 721010 out of order tv_sec: 1070067322 116061, prev 1070067320 440490 out of order tv_usec: 1070067322 116061, prev 1070067320 440490 out of order tv_sec: 1070067833 514969, prev 1070067831 755019 out of order tv_usec: 1070067833 514969, prev 1070067831 755019 ^C AFAICT the above is a legal trace, indicating only that the test program sometimes lost control for more than a second at a time. The revised version of the test will not complain about this. Doh! I was looking for time going backwards so that's what I saw, even though I already new the same complaint would be made if time skipped forward a second or so. One variable I didn't think to ask about is whether you are running NTP. In my experience an ntp daemon that has achieved lock will never step the clock back by even 1 usec (it's supposed to use much more subtle methods than that to manage the clock ;-)) but maybe under unstable conditions such things could happen. The machines I have tested here all run NTP. Yes, all NTPed. The FreeBSD system, the one that doesn't skip back *smacks forehead*, is the local time server but is generally lightly loaded. Not to say that there's not going to be the odd job it has to run which could make the system busy and time time away from this test. It's just difficult to say that every so often there's some task that makes the system unresponsive for a couple of seconds, which is what this is saying. Anyway, I like your suggestion as the timeslicing being a possible cause. Seems a little strange though, presumably all the numbers going into the timeofday calc are gathered by the one system call at a place past where the thread could have had it's time sliced away and before the next. However, I have no idea how the BSD kernel operates so can't do more than assume and guess. -- Nigel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Commercial binary support?
On Sun, 23 Nov 2003, Oleg Bartunov wrote: does tsearch2 in 7.4 still has the problem ? I apologies if we miss your patches but certainly we're interested in clear explanation of the problem. The problem was memory allocations made through malloc and family were not being checked for failure before attempts made to use the memory, i.e. null pointer dereference. Tom or Bruce applied the patch in time for 7.4 release. The only issue with this was noone knew how the version of tsearch2 for PostgeSQL 7.3 was being maintained. I think I posted the patch for that to at least one of the lists but as I am using tsearch2 on 7.3 I also threw this into my own CVS. In short, I don't think there's anything to worry about in relation to my patches and 7.4. Just to remind you though, the original fault reporter reported he was still getting the fault after applying what I assume was my patches. Which surprised me as I expected the fault location to be moved somewhere else. I think the real problem he was having was that of memory exhaustion but we never got more than basic information for this last report. Nigel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Commercial binary support?
On 19 Nov 2003, Robert Treat wrote: I don't think *we* thought it was a hot button issue.. at least I certainly didn't when I initially responded. There is no need for you to apologize, in fact, I'll apologize for the list, we sometimes get a little heated on -hackers. Hopefully you've not been to startled by this outburst :-) Some people have obviously lead a sheltered 'net existence :) -- Nigel Andrews ---(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] Commercial binary support?
However, I would love to see those patches. Sure. Should be in the archive. The version for 7.4 was submitted and applied pre-release but if you really do want the 7.3 runnable stuff I can send it. It was only the unchecked returns from malloc and family patch in the snowball directory. I think the original fault reporter still had problems afterwards though, shame he didn't seem interested in persuing it or providing decent help to find the cause. Nigel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Commercial binary support?
Oops, sorry folks. That was only meant to go to Joshua. On Sat, 22 Nov 2003, Nigel J. Andrews wrote: However, I would love to see those patches. Sure. Should be in the archive. The version for 7.4 was submitted and applied ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Commercial binary support?
On Wed, 19 Nov 2003, Bruce Momjian wrote: Marc G. Fournier wrote: On Wed, 19 Nov 2003, Michael Meskes wrote: On Tue, Nov 18, 2003 at 04:19:35PM -0600, Austin Gonyou wrote: I've been looking all over but I can't seem to see a company that is providing *up-to-date* postgresql support and provides their own supported binaries. Am I barking up the wrong tree entirely here? Why do you insist on their own binaries? I think there are several companies out there providing support for a given version of PostgreSQL and doubt they all ask for their own binaries. At least we do not. We don't either, nor do we worry about specific platforms ... And I know CommandPrompt doesn't care either. I don't even know what it means. If I were to build the 7.4 source, install it somewhere, tarball it up would that then count as providing our own supported binaries (assuming the support service is also offered of course)? Surely it's fairly common for someone to sell support and be happy to include the service of supplying the binaries so if requested, what's so special about it? Nigel Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] About the partial tarballs
On Tue, 11 Nov 2003, Marc G. Fournier wrote: ftp://ftp.postgresql.org/pub/README.dist-split to reduce the confusion, that would be great. I've just symlink'd it into the source directories as the .message, so that its displays when you enter the directory ... Does anyone actually read those messages? Similar sort of question to the 'Does anyone read any of those popup boxes produced by everything in Windows before hitting OK?' one. -- Nigel J. Andrews ---(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] Learning PostgreSQL
On Sun, 5 Oct 2003, Gevik Babakhani wrote: Dear PostgreSQL masters, I know this might look like a childish question and you probably might have a good laugh over this but I would like to learn how PostgreSQL works inside-out. Could anyone please give me some pointers of where to start in/from the source code? I am grateful for any help. As Bruce says, the developers website (http://developer.postgresql.org/ - I think). On the other hand I'd suggest a good read of the user documentation first. Get an idea of what it can do from the user perspective then start poking around inside. When it comes to poking around inside a decent start might be to take a look at one of the PLs and contrib items. -- Nigel ---(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] Thoughts on maintaining 7.3
On Fri, 3 Oct 2003, Andrew Sullivan wrote: On Thu, Oct 02, 2003 at 02:15:33PM -0500, Bruno Wolff III wrote: It might be better to split into two different trees. One just gets bug fixes, the other gets bug fixes plus enhancements that won't require an initdb. Yes, please. Please, please do not force all users to accept new features in stable trees. I wanted to say something similar earlier in this thread. To me the stable branches are not for feature introduction. If features are going to be introduced it is better to not have them applied in a manner which means a pure bug fix only version can't be obtained. Obviously this means having two branches if features are going to be introduced. I agree sometimes one looks at new developments and thinks how good it would be to have that feature, imagine what it'll be like when tablespaces are introduced and you're using the previous stable version, but those features need to be kept separate from the version that fixes that particularly nasty index corruption someone only provided a fix for 12 months after the version you have based your system around was released. One could argue that what is really needed is a collection of patches providing a pick and choose facility for features, with dependecies where unavoidable of course. The patches being applicable to the latest bug patched version of the stable branch. As an example take tsearch2. If that were core code, not optional, contrib material, and one was running a 7.3 series server but wanted the nifty features of tsearch2 instead of tsearch, would you expect all people upgrading within the stable 7.3 branch for bug fixes to be forced to use tsearch2 and not tsearch? -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
On Mon, 29 Sep 2003, Christopher Kings-Lynne wrote: So a db designer made a bloody mistake. The problem is there's no easy way to find out what's missing. I'd really like EXPLAIN to display all subsequent triggered queries also, to see the full scans caused by missing indexes. I'd sure second that! That's only partially determinable though. The trigger code could branch and run two different queries depending on the values supplied in the 'input' tuple of the trigger. Nigel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] getaddrinfo() for threading instead of gethostbyname()
On Sat, 27 Sep 2003, Bruce Momjian wrote: OK, the thread test program is read for platform testing, src/tools/thread_test. You will find the README, Makefile tests, and program output to be very clear and almost error-proof. Please run it on platforms we support and report back. Thanks. Debian linux kernel 2.2.x (- which was 2.0 or 2.2 of debian I think, can't remember) glibc 2.1.3 gcc 2.95.2 it looks like the thread stuff is enabled in ports/linux in cvs tip h... [EMAIL PROTECTED]:~/database/postgres/pgsql/src/tools/thread$ make gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I../../../src/include -D_GNU_SOURCE -c -o thread_test.o thread_test.c gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -L../../../src/port -Wl,-rpath,/usr/local/stow/postgresql-7.3.4/lib -lpthread thread_test.o -lpam -lz -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lpgport -o thread_test [EMAIL PROTECTED]:~/database/postgres/pgsql/src/tools/thread$ ./thread_test Make sure you have added any needed 'THREAD_CPPFLAGS' and 'THREAD_LIBS' defines to your template/$port file before compiling this program. Your gethostbyname() is _not_ thread-safe Your getpwuid() is _not_ thread-safe Not all non-*_r functions are thread-safe. Add this to your template/$port file: NEED_REENTRANT_FUNCS=yes [EMAIL PROTECTED]:~/database/postgres/pgsql/src/tools/thread$ Nigel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] feature request: show pgsql version when running initdb
On Fri, 26 Sep 2003, Christopher Kings-Lynne wrote: If you install many different versions in parallel, don't you give your installation paths some meaning that contain the version number? In any case, you can run initdb --version first if you're not sure about what is where. Yes I do, but sometimes as different users you don't know what the path is. I guess I can just go --version. Or just: which initdb which is a bit easier to interpret than: echo $path which is what you really want to know, i.e. what are the default paths applied in the search for an executable invoked without an explicit path. I think the problem here is the assumption that you don't need to explicitly state the path to the executable when invoking a command from multiple installations. If you've got specific requirements on which version to run never just assume which one will be picked up, always take steps to verify which one it is, explicitly state which one to use or accept that you may well end up running the wrong and have to start again (if you're lucky enough to be trying something that isn't going to permanently move you into a state where you can't start again). To take the normal sort of example in reverse: I have a script for reading manuals, I call it rm, it can take some switches lets say r and f, as well as the name of something to read about. I happen to be sitting in / and I look and start wondering why there's a /sbin. So I think I'll see if there's anything in the document store about it. So I type: rm -rf sbin (to do a recursive search of formated documents perhaps). I don't think about it, verify it or anything. Why should I? It always just works. Unfortunately, although I do know I'm doing this as root it doesn't occur to me there might be another command called rm installed somewhere on the system that I might pick up instead. Moral of the story, if it's in your path first then it's the default and you should therefore be happy with the results or be prepared to live with them, otherwise make sure what you're running. -- Nigel J. Andrews ---(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] pgsql procedures??
On Thu, 25 Sep 2003, Bruno Wolff III wrote: On Wed, Sep 24, 2003 at 20:21:20 -0300, Maurício Paiva [EMAIL PROTECTED] wrote: What we are trying to figure out is if postgresql supports returning multiple result sets from a stored procedure (psql function?) No, Postgres can't do that. But it can if you switch to one of the other languages like plpgsql, which isn't terribly complicated but does require the language to be installed in the database. -- Nigel J. Andrews ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] pgindented tsearch2 for 7.3.4
On Wed, 24 Sep 2003, Bruce Momjian wrote: Nigel J. Andrews wrote: I never knew running indent was so damn complicated. All three of my development systems can not manage it without throughing a fault ... There are about 6 files that can't be run through pgindent, and tsearch2 has one of them: $ pgindent *.c Hope you installed /src/tools/pgindent/indent.bsd.patch. gistidx.c [EMAIL PROTECTED]: Unbalanced parens [EMAIL PROTECTED]: Extra ) [EMAIL PROTECTED]: Unbalanced parens ... It was a far more basic problem than that! I'd have been pleased if I'd got as far as that. You developers of today don't know you're born. Back in my day we used to have to get up before noon just to turn the computer on... Actually, I had a problem in that I only had GNU indent and BSD indent that was core dumping but it was unpatched, plus I couldn't find the BSD indent source on the web. Eventually found it, in a linux distribution of all places, and then I got the above errors, by which time I was quite prepared to not count those as such. There are a few macro's defined that make me think of fortran programmers moving to C. Anyway Bruce, you'll see the patch eventually generated lower down you inbox. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] old pgindent change
There was a simple change commited in revision 1.47 of pgindent, listed as
being More updates for GNU indent.
The questions are: why? and surely I can't be the only one whose hit this
problem since November 2001?
On a debian (woody or potato, which ever one had a 2.2 series kernal) using
GNU bash 2.03.0 via /bin/sh (in the pgindent script) I get:
which indent = /usr/local/bin/indent
indent -version = Berkeley indent 5.17
status = 0
You do not appear to have 'indent' installed on your system.
By adding appropiate echo commands before and after the indent -version line in
the script, I've attached my slightly modified version for completeness. I can
never remember the way around the test of $? succeds or fails but the above
experiment plus a look at the near by tests of $? in pgindent seem to show that
there is a problem here.
A simple test of 2.05a.0 on a newer system using:
ls
if [ $? -eq 0 ]; then echo success aaa; fi
lls
if [ $? -eq 0 ]; then echo success bbb; fi
shows that the lls (non-existant executable) status fails the test as expected
while the plain ls status passes.
I'm obviously missing something very significant here or there's a very strange
oddity that's been there, and specifically placed there, for nearly 2 years.
--
Nigel J. Andrews
#!/bin/sh
echo
echo -n Path=
echo $PATH
echo
# Known bugs:
#
# Blank line is added after, seen as a function definition, no space
# after *:
# y = (int) x *y;
trap rm -f /tmp/$$ /tmp/$$a 0 1 2 3 15
entab /dev/null /dev/null
if [ $? -ne 0 ]
thenecho Go to the src/tools/entab directory and do a 'make' and 'make install'.
2
echo This will put the 'entab' command in your path. 2
echo Then run $0 again.
exit 1
fi
echo
a=`which indent`
echo which indent = $a
a=`indent -version 21`
echo indent -version = $a
echo
indent -version /dev/null /dev/null 21
echo status = $?
if [ $? -ne 1 ]
thenecho You do not appear to have 'indent' installed on your system. 2
exit 1
fi
indent -gnu /dev/null /dev/null 21
if [ $? -eq 0 ]
thenecho You appear to have GNU indent rather than BSD indent. 2
echo See the pgindent/README file for a description of its problems. 2
EXTRA_OPTS=-cdb -bli0 -npcs -cli4 -sc
elseecho Hope you installed /src/tools/pgindent/indent.bsd.patch. 2
EXTRA_OPTS=-cli1
fi
for FILE
do
cat $FILE |
# convert // comments to /* */
sed 's;^\([ ]*\)//\(.*\)$;\1/* \2 */;g' |
# Avoid bug that converts 'x =- 1' to 'x = -1'
sed 's;=- ;-= ;g' |
# mark some comments for special treatment later
sed 's;/\* *---;/*---X_X;g' |
# workaround for indent bug with 'else' handling
sed 's;\([} ]\)else[]*\(/\*.*\)$;\1else\
\2;g' |
detab -t4 -qc |
# work around bug where function that defines no local variables misindents
# switch() case lines and line after #else. Do not do for struct/enum.
awk ' BEGIN {line1 = ; line2 = }
{
line2 = $0;
if (NR = 2)
print line1;
if (NR = 2
line2 ~ ^{[]*$
line1 !~ ^struct
line1 !~ ^enum
line1 !~ ^typedef
line1 !~ ^extern[ ][ ]*\C\
line1 !~ =
line1 ~ ))
print int pgindent_func_no_var_fix;;
line1 = line2;
}
END {
if (NR = 1)
print line1;
}' |
# prevent indenting of code in 'extern C' blocks
awk ' BEGIN {line1 = ; line2 = ; skips = 0}
{
line2 = $0;
if (skips 0)
skips--;
if (line1 ~ ^#ifdef[ ]*__cplusplus
line2 ~ ^extern[ ]*\C\[]*$)
{
print line1;
print line2;
if (getline $0 ~ /^{[]*$/)
print /* Open extern \C\ */;
elseprint $0;
line2 = ;
skips = 2;
}
else if (line1 ~ ^#ifdef[ ]*__cplusplus
line2 ~ ^}[]*$)
{
print line1;
print /* Close extern \C\ */;
line2 = ;
skips = 2;
}
else
if (skips == 0 NR = 2)
print line1
Re: [HACKERS] old pgindent change
On Mon, 22 Sep 2003, Nigel J. Andrews wrote: There was a simple change commited in revision 1.47 of pgindent, listed as being More updates for GNU indent. The questions are: why? and surely I can't be the only one whose hit this problem since November 2001? ... I also had to apply the following change in order to avoid an awk compilation error: *** pgindentMon Sep 22 23:54:40 2003 --- pgindent.newMon Sep 22 23:54:27 2003 *** *** 65,71 line1 !~ ^typedef line1 !~ ^extern[ ][ ]*\C\ line1 !~ = ! line1 ~ )) print int pgindent_func_no_var_fix;; line1 = line2; } --- 65,71 line1 !~ ^typedef line1 !~ ^extern[ ][ ]*\C\ line1 !~ = ! line1 ~ \)) print int pgindent_func_no_var_fix;; line1 = line2; } The changed line was also added in November 2001, in revision 1.48 this time. awk -W version mawk 1.3.3 Nov 1996, Copyright (C) Michael D. Brennan and again with /bin/sh being a link to bash 2.03.0 -- Nigel J. Andrews ---(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] change of table name - any help
[I'm not convinced this is a -hackers issue so have cross posted to -general in the expectation followups will go there] I also didn't feel there was much I could cut from the earlier posts without losing relevent info, so I didn't. Sorry. On Fri, 19 Sep 2003, chakkara rangarajan wrote: Christoph, Thx for your response. We didn't change the search_parth variable. Moreover, I tried all combination of drop statements like Drop table table_name Drop table owner.table_name Drop table table_name Drop table owner.table_name ... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 2:37 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] We have a development server running OS - Linux development-server 2.4.20-openmosix-r4 #1 SMP Mon May 19 02:32:52 PDT 2003 i686 Intel(R) Xeon(TM) CPU 2.40GHz GenuineIntel GNU/Linux Database - PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.1 We have a table ctcert_name under postgres DB(postgres schema and postgres user is the owner). Suddenly, this object started missing from the DB (I am the only Person who connects to that server and did not drop/renamed it). When I tried to recreate the same table, the system threw me back an error, saying that postgres.ctcert_name already exists. I am neither able to drop or rename the table. checked the DB logs and there is no drop/rename table statement in that. I have the transaction logs, but not able to read, as they are not in the human readable format. How can I decipher from the txn logs, if it captures the change management. Can somebody please tell me, what cud have gone wrong and is the error is reproduceable? What is the solution for this kind of problem. Did you change the SEARCH_PATH variable? Did I get this right: You cannot DROP TABLE postgres.ctcert_name ; Mind, I left off the enclosing quotes. And you cannot CREATE TABLE postgres.ctcert_name( ... ) ; My suspicion is you are using these quotes and you shouldn't. This last would try a create a table called owner.table_name in the current schema. In fact given that I'm starting from the point of already having a table named test the following shows this as well as a few other points. test=# \dt List of relations Schema | Name | Type | Owner ++---+--- ... public | test | table | test (13 rows) test=# create table test (a int); ERROR: Relation 'test' already exists test=# create table public.test (a int); ERROR: Relation 'test' already exists test=# create table public.test (a int); CREATE TABLE test=# create table public.test (a int); ERROR: Relation 'public.test' already exists test=# \dt List of relations Schema | Name | Type | Owner ++---+--- ... public | public.test| table | test ... public | test | table | test (14 rows) test=# Also the all lower case variant shouldn't make any difference to the unquoted names since unquoted names get folded to lower case. What would make a difference is if there was quoted upper case characters used one time but not another. However, I suspect I'm telling you thing you already know. Presumably you've tried using \dt in psql and the table isn't listed but others in the same schema are? What about the query: select c.oid, c.relname, c.relnamespace from pg_class c where relname ilike '%ctcert%'; followed by: select oid,* from pg_namespace where oid = ? where the ? in the second is the relnamespace value in results from the first query. It's difficult to see why there would be a pg_class entry with the same name as you are trying and in the same schema but the relname, relnamespace combination must be unique. Trying to create a new table that would violate that unique constraint I imagine would give you that error message. Bearing in mind the lack of drop table statements logged you should also check for updates/delete from pg_class. Although because the system thinks there is a conflict when creating the new table I'd be slightly worried that something's gone horribly wrong somewhere but then I don't know what data in the system tables would, validly, make it look like the object existed at the same time as it didn't -- Nigel Andrews ---(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] Why select * from function doesn't work when function
On Thu, 24 Jul 2003, Francisco Figueiredo Jr. wrote: Nigel J. Andrews wrote: On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: How's this for an alternative if you really don't want any rows returned: create function fincF ( ) returns setof integer as ' begin delete from blah; return; end; ' language 'plpgsql'; This works, but what I really want is not to return any rows. I mean, the problem is not return null, but the error I get if I select * from voidfunction. I just wanted void functions behave like others when called as select * from voidfunction So I dont have to do select voidfunction. :) But that last does exactly that. Doesn't even return a null. Give it a quick go, skip the delete statement obviously, and see. You'll get something like: ? --- (0 rows) Uhmmm, I think I didn't make myself clear. What I mean by void function wasn't a function which just doesn't return anything. What I meant is a function created like this: create function voidfunction returns *void* as [...] I knew what you meant but why the insistence on the void return type? All it's saying is that there isn't any interpretation that can be applied to anything that may (or may not) be returned from it so what are you trying to gain by forcing the void type when you're forced into ignoring the result anyway? The problem to me is the void in the returns ;) If you create a function with the returns void above you'll see that if you do select * from voidfunction it gives you the error I said. But it works with select voidfunction. Well don't create the function as returning void :) I just wanted it to work with select * from voidfunction too. :) I think I did most of mine as returning integer type and the value 1 (just for something to return). Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tsearch2 for 7.3.X
On Tue, 22 Jul 2003, Tom Lane wrote: Oleg Bartunov [EMAIL PROTECTED] writes: OK. I'll suggest people to try new tsearch2 in README file of old tsearch. Okay, that works for me. Please patch the old tsearch README file in both HEAD and REL7_3_STABLE branch as soon as possible --- we are going to wrap a 7.3.4 release as soon as Bruce finds the time to make release notes, possibly today. I don't expect you wouldn't put in the information but just to highlight that it'd be a good idea to put the location the tsearch2 tarball can picked up from in the README. -- Nigel Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] suggestions to improve postgresql suitability for
On Tue, 22 Jul 2003, Fabien COELHO wrote: ... III) PL/pgSQL - Ok, if postgresql does not want to do it my way, let us make it do it. Thus I wrote some PL/pgSQL function for my purpose, something like: CREATE TEMPORARY TABLE tmp ( area INTEGER, type INTEGER, month INTEGER, amount INTEGER, count INTEGER, PRIMARY KEY(area, type, month) ); -- initialize tmp FOR i IN 0..99 LOOP FOR j IN 0..3 LOOP FOR k IN 0..11 LOOP INSERT INTO tmp VALUES(i,j,k,0,0); END all LOOPs; -- fill tmp FOR tuple IN SELECT area, type, month, amount FROM client, invoice WHERE id=client LOOP UPDATE tmp SET amount=amount+tuple.amount, count=count+1 WHERE area=tuple.area AND type=tuple.type AND month=tuple.month END LOOP; ... It is very SLOOOW... 10 to 100 times slower than the previous one. Exit PL/pgSQL. It will be, first you're doing the same join that generates the large result set you were complaining about in the plain SQL example and then you're looping over it generating a delete/insert for every tuple in that result set. IV) Basic client side (JDBC, DBI, libpq) Then I wrote the same stuff on the client side in java with JDBC, perl with DBI and C with libpq, by browsing the above SELECT in a simple loop and aggregating the data directly in the language. In all 3 cases, the process attempts to allocate the full result of the client and invoice join in memory... a **very** bad idea indeed! But what about doing that in the server? I checked that the postgres client-server protocol does not allow to chunk the result of a select, as only one response is sent for one query. I suggest that this behavior should be changed, as the ODBC/DBI/JDBC interfaces are designed to allow the client to process data as the come out of the database, even if the query is not finished yet. The library should do the chunking on its own automatically, either by doing a CURSOR/FETCH's manually in the library implementation on SELECT, or by changing the protocol so that results are sent by chunks when required. This is listed in the todo list of the JDBC interface, but there is nothing about the perl interface nor the libpq interface. V) Less basic client side (DBI, libpq) -- I've redone the previous stuff, but with an explicit CURSOR and a FETCH loop. It worked better, but it is still slow and still requires a lot of disk space. Indeed, the database seems to first generate the join in a temporary table on disk (I need twice as much disk space available as the original base), which is then sent back to the client. Thus I pay a read/write/read of the whole tables although I had hoped that reading the data only once would have been enough. I would suggest to make processing data on the fly be done really on the fly, not with an intermediate storage and providing just an on-the-fly interface without the real thing behind. I haven't seen any item in the todo list about this issue. I'm not sure it is really easy to implement. I thought it necessary for the result set to be generated before any data can be returned, in the general case and in your grouped by example specifically. The latter if only because if you're not using the hash aggregates then the sort is required and that of course requires all the result data to be known. Conclusion -- I have not succeeded in getting from postgresql the performances I was expecting for data-mining. I could get them if postgresql could be improved on some or all of the following items: (1) the execution engine may aggregate grouped data without a sort in some cases. As other's have said, this is in 7.4 (2) the PL/pgSQL interpreter would be a great deal faster. It did what you told it to do. (3) the client programming interfaces would provide a real on-the-fly (without intermediate storage) fetching mecanism. (4) Also, I noticed that temporary tables/indexes created by postgresql when processing a request are stored in the same partition as the database in use. What about /tmp or other partitions? Maybe a set of other directories could be designated for this purpose? Hope this help... at least to add new items to the postgresql todo list;-) Have a nice day, -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: Hi all, I would like to know why does calling a function with select * from function doesn't work when its return type is set to void. I'm asking this because I have a code which uses this syntax to add support for returning resultsets from functions. This way, regardless the function returns a resultset or a single value, I could do select * from function and it works very well. The problem appears when the function has its returns type to void. I get the following error message: npgsql_tests= select * from funcF(); ERROR: function funcf() in FROM has unsupported return type ERROR: function funcf() in FROM has unsupported return type where funcF is defined as: npgsql_tests= create function funcF() returns void as 'delete from tablea where field_serial 5' language 'sql'; CREATE FUNCTION But it does work if I call it as: select funcF(); I'd like to know if would be possible to change this behaviour to return an empty result set with a null value. This way, there would be consistency in calling all functions regardless of its return type with select * from function. Try returning an integer but returning a null for that integer...on the other hand I see you're using sql as the language and I don't know how that would work. Have you looked at plpgsql? Perhaps that is acceptable for you, in which case: create function funcF ( ) returns integer as ' begin delete from blah; return null; end; ' as language 'plpgsql'; select * from funcF(); I believe that would work but don't quote me :) -- Nigel J. Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: select * from funcF(); Yeap, it works, but you specified integer as the return type :) Yes, that's because I knew the void wouldn't work. :] I'd like to have the return type as void and be possible to call it with select * from funcF(); I don't believe it is possible. Makes sense since void doesn't really make sense in that position in the statment. How's this for an alternative if you really don't want any rows returned: create function fincF ( ) returns setof integer as ' begin delete from blah; return; end; ' language 'plpgsql'; -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why select * from function doesn't work when function
On Tue, 22 Jul 2003, Francisco Figueiredo Jr. wrote: How's this for an alternative if you really don't want any rows returned: create function fincF ( ) returns setof integer as ' begin delete from blah; return; end; ' language 'plpgsql'; This works, but what I really want is not to return any rows. I mean, the problem is not return null, but the error I get if I select * from voidfunction. I just wanted void functions behave like others when called as select * from voidfunction So I dont have to do select voidfunction. :) But that last does exactly that. Doesn't even return a null. Give it a quick go, skip the delete statement obviously, and see. You'll get something like: ? --- (0 rows) -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error messages --- now that we've got it, do you like
On Sun, 20 Jul 2003, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: VERBOSE doesn't seem like the right name for the \set parameter. VERBOSITY would be okay with me. Sounds meaningful. I often want to say 'verbosity level' when talking such things. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error messages --- now that we've got it, do you like
Oops, just remembered I was going to look at the new style error codes to answer one of your emails before. Just not had the time. On Thu, 3 Jul 2003, Tom Lane wrote: First fruits of all that work on error message rejiggering ... regression=# \set VERBOSE terse regression=# select 1!! ; ERROR: operator does not exist: integer !! I can see why 'terse' contains the least amount of information and that generally it should not therefore contain information not in the next higher level but I would have thought 'terse' would include the error number. Even _just_ the error number. I presume this setting is completely different from the one to determine the verbosity in the server log. In the server logs I would think it better to be able to include the error code in the error line without having any other detail lines. In fact in the server log would it not be the case that the LOCATION detail came before the HINT detail in the verbosity stakes, or is it viewed as being closer to a debug setting and so requires more verbosity? regression=# \set VERBOSE default regression=# select 1!! ; ERROR: operator does not exist: integer !! HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. regression=# \set VERBOSE verbose regression=# select 1!! ; ERROR: 42883: operator does not exist: integer !! HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. LOCATION: op_error, parse_oper.c:691 regression=# select 'z' 'q'; ERROR: 42725: operator is not unique: unknown unknown HINT: Unable to choose a best candidate operator. You may need to add explicit typecasts. LOCATION: op_error, parse_oper.c:684 Before we go too much further, does this look sane to people? Any adjustments you want to make around the edges? (BTW, if you're wondering where the 42xxx error codes came from, I borrowed them from DB2. The SQL99 spec seems happy to lump all sorts of conditions under 42000 syntax error or access violation ...) Looks good. Error codes are always handy to have and the extra details are just the ticket, I especially like the hint. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Two weeks to feature freeze
On Mon, 23 Jun 2003, Dann Corbit wrote: -Original Message- From: scott.marlowe [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 12:25 PM To: Dann Corbit Cc: Bruce Momjian; Tom Lane; Jason Earl; PostgreSQL-development Subject: Re: [HACKERS] Two weeks to feature freeze On Mon, 23 Jun 2003, Dann Corbit wrote: Vendor A: We think our tool is pretty solid and our end users hardly ever turn up any bugs. Vendor B: We think our tool is pretty solid and our 8500 tests currently show only 3 defects with the released version, and these are low impact issues. To view our current database of issues, log onto web form page. Which tool would you prefer to install? The one I've tested and found to meet my needs, both now and by providing fixes when I needed it. How about the one that doesn't run tests in order to show how much better it is than the competition but to actually test operation? In other words Vendor B has an interest in having the tests pass, what gives you the confidence it just hasn't listed the ones that fail and that the tests that do pass are not just testing something vendor B wants to show it can do? Real world example: We run Crystal Reports Enterprise edition where I work. It's tested thouroughly (supposedly) and has all kinds of QA. However, getting it to work right and stay up is a nightmare. It's taken them almost a year to get around to testing against the OpenLDAP LDAP server we use. The box said LDAP V3 compliant and they assured us that it was. Well, it doesn't work with our LDAP V3 compliant LDAP server at all, and the problem is something they can't fix for months because it doesn't fit into their test cycle. Real world example: Postgresql aggregates in subselects. Someone found a bug in subselects in Postgresql with inner references to outter aggregates. The postgresql team delivered a patch in less than a week. User tested it and it works. I'm not against testing and all, but as one of the many beta testers for Postgresql, I do feel a bit insulted by your attitude that only a cohesive, organized testing effort can result in a reliable product. Let me rephrase it: Only a cohesive, organized testing effort can result in a product that is proven reliable. Without such an effort, it is only an educated guess as to whether the product is reliable or not. The data is the most valuable software component in an organization. It is worth more than the hardware and it is worth more than the software. If you are going to trust one billion dollars worth of corporate data on a software system, you ought to ensure that the system has been carefully tested. I don't think that is just an opinion. It's simply common sense. So you've never worked on a project where the data is of high value, since in those circumstances the customer is always going to apply their own acceptance testing anyway. If you think that doesn't happen you try sitting through 2 solid days of Y2k testing on _one_ system and tell me customers never do their own testing. Therefore, I am going to stop harping on it. But there is no need to, as has been mentioned before, if the testing is not upto your level of testing submit something that makes it so. Having said that I do believe you mentioned that you didn't have the time to create something but you would be happy to test it, i.e. test the test. -- Nigel J. Andrews ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Two weeks to feature freeze
On Thu, 19 Jun 2003, The Hermit Hacker wrote: On Thu, 19 Jun 2003, Andrew Dunstan wrote: Maybe a better strategy would be to get a release out soon but not wait 6 months for another release which would contain the Win32 port and the PITR stuff (assuming those aren't done in time for this release). Just a thought. And definitely in agreement here ... I'd rather see a shortened dev cycle prompted by a big feature being added, then delaying a release because oh oh, I need another few weeks that draws out when something unexpected happens :( ... I'm not sure why another delay is being considered. There's been a delay of a week because of the server problems hasn't there and wasn't the original delay only acceptable on the basis that that was that and there wasn't going to be another extension? -- Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Pre-allocation of shared memory ...
On Fri, 13 Jun 2003, Lamar Owen wrote: On Friday 13 June 2003 11:55, Josh Berkus wrote: Regrettably, few of the GUI installers for Linux (SuSE or Red Hat, for example), include adequate swap space in their suggested disk formatting. Some versions of some distributions do not create a swap partition at all; others allocate only 130mb to this partition regardless of actual RAM. Incidentally, Red Hat as of about 7.0 began insisting on swap space at least as large as twice RAM size. In my case on my 512MB RAM notebook, that meant it wanted 1GB swap. If you upgrade your RAM you could get into trouble. In that case, you create a swap file on one of your other partitions that the kernel can use. I'm not sure I agree with this. To a large extent these days of cheap memory swap space is there to give you time to notice the excessive use of it and repair the system, since you'd normally be running everything in RAM. Using the old measure of twice physical memory for swap is excessive on a decent system imo. I certainly would not allocate 1GB of swap! Well, okay, I might if I've got a 16GB machine with the potential for an excessive but transitory workload, or say 4-8GB machine with a few very large memory usage processes that can be started as part of the normal work load. In short, imo these days swap is there to prevent valid processes dying for lack of system memory and not to provide normal workspace for them. Having said all that, I haven't read the start of this thread so I've probably missed the reason for the complaint about lack of swap space, like a problem on a small memory system. -- Nigel J. Andrews ---(end of broadcast)--- TIP 9: most folks find a random_page_cost between 1 or 2 is ideal
Re: [HACKERS] Proposal to Re-Order Postgresql.Conf, part II
On Mon, 9 Jun 2003, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Hey, I'm looking at the postgresql.conf.sample in CVS, and can't find the option that's supposed to let you turn off Inserting missing FROM clause for table ... Bruce hasn't applied that patch yet. I believe he's starting to catch up the patch backlog today, though. Are you sure about that? I seem to remember seeing the will be applied within 24 hours message a couple of weeks or so ago now. Is this a feature of the recent system problems and lost patches are having to be reapplied? As for it's name Josh, sorry, I don't have a record of my patch and the name used in the patch differs to that which I have in my source tree. -- Nigel Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] host and hostssl equivalence in pg_hba.conf
On Tue, 10 Jun 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: How do people feel about changing matching for host and hostssl to be such that a plain host line in pg_hba.conf does not allow a SSL connection but requires the hostssl specifier? Then there would be no way to have a host entry that allowed both --- which, aside from being a loss of functionality, would doubtless break existing setups. Well, what I was thinking of would have allowed it, just using two entries, a host one and a hostssl one. I'd hold still for a hostnossl keyword, I guess, but I don't entirely see the use for it. Well Jon Jenson's posted something else on this which I should read when I've got my mind more in tune with it. If your real gripe is that libpq insists on trying SSL connections first, the server is the wrong end to be patching that problem at. There should be a way to control libpq's allow_ssl_try state variable from the outside. A quick read makes me think that's what Jon's post is on about. -- Nigel Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] large objects
On Fri, 6 Jun 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Now, I did a little bit of testing and when doing a \lo_export oid filename in psql connected via localhost a SIGPIPE is generated in write() in libc and psql quit, without printing any message to the terminal. Perhaps interestingly the file that gets written is always 65536 bytes long. Hm. Are you using an SSL connection? There are some known bugs in the SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've not tried it myself. Damn, yes I am, I noticed the notice when connecting but then didn't think anything of it. Thanks Tom, I'll check that later when I do have time (shouldn't have wasted the precious minutes joining the NULL != war). -- Nigel Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] large objects
Repost just to add the information that this is [now] on 7.3.3, previously on 7.3.2. Sorry for the noise of the incomplete previous message, although the email lists seem to be very light this last week. Obviously the mail server is still feeling under the weather, I presume many posts have been lost in a hole somewhere much like another of my posts. On Sun, 8 Jun 2003, Nigel J. Andrews wrote: Note, primary list address changed to -general, I'd suggest any followups remove the -hackers, which I've left in just for 'closure'. On Fri, 6 Jun 2003, Nigel J. Andrews wrote: On Fri, 6 Jun 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Now, I did a little bit of testing and when doing a \lo_export oid filename in psql connected via localhost a SIGPIPE is generated in write() in libc and psql quit, without printing any message to the terminal. Perhaps interestingly the file that gets written is always 65536 bytes long. Hm. Are you using an SSL connection? There are some known bugs in the SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've not tried it myself. Damn, yes I am, I noticed the notice when connecting but then didn't think anything of it. Thanks Tom, I'll check that later when I do have time (shouldn't have wasted the precious minutes joining the NULL != war). Ok, I tried to try this but I can not get SSL to _not_ be used when connecting via any tcp connection, unless the client hasn't been built with ssl support of course. The pg_hba.conf has: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD local all all md5 host all all 127.0.0.1 255.255.255.255 md5 psql -U me -h localhost db prints: SSL connection (cipher: EDH-RSA-DES-CBC3-SHA, bits: 168) psql -U me db doesn't. Am I losing my mind? Should I need hostssl on that second line of the config before ssl is allowed? I did look at that code once a few weeks ago and vaguely remember something about host and hostssl handling but can't remember the details. Is this really a bug, even if only in the documentation, or have I got completely the wrong end of the stick? -- Nigel J. Andrews ---(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] large objects
Note, primary list address changed to -general, I'd suggest any followups remove the -hackers, which I've left in just for 'closure'. On Fri, 6 Jun 2003, Nigel J. Andrews wrote: On Fri, 6 Jun 2003, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Now, I did a little bit of testing and when doing a \lo_export oid filename in psql connected via localhost a SIGPIPE is generated in write() in libc and psql quit, without printing any message to the terminal. Perhaps interestingly the file that gets written is always 65536 bytes long. Hm. Are you using an SSL connection? There are some known bugs in the SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've not tried it myself. Damn, yes I am, I noticed the notice when connecting but then didn't think anything of it. Thanks Tom, I'll check that later when I do have time (shouldn't have wasted the precious minutes joining the NULL != war). Ok, I tried to try this but I can not get SSL to _not_ be used when connecting via any tcp connection, unless the client hasn't been built with ssl support of course. The pg_hba.conf has: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD local all all md5 host all all 127.0.0.1 255.255.255.255 md5 psql -U me -h localhost db prints: SSL connection (cipher: EDH-RSA-DES-CBC3-SHA, bits: 168) psql -U me db doesn't. Am I losing my mind? Should I need hostssl on that second line of the config before ssl is allowed? I did look at that code once a few weeks ago and vaguely remember something about host and hostssl handling but can't remember the details. Is this really a bug, even if only in the documentation, or have I got completely the wrong end of the stick? -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] large objects
I realise large objects are liked anymore and that the wire protocol is changing in 7.4 but I've just posted this message into the PHP documentation: -- begin Using php 4.3.0 and PostgreSQL 7.3.1 I can write a simple script in which pg_lo_write seems to always return 1 and not the number of bytes written, as evidenced by extracting the data through another means. Further more, I can make this pg_lo_write fail, or at least fail to write all the data it's pretty difficult to tell without the number of bytes written being returned, and not return the false value. In addition to this, the lo resource has been adjusted so that the oid it contains is 0. Unfortunately, I do not know what exactly the failure mode is, it does seem to be in the ip network communication side of PostgreSQL, which is odd since the unix domain comms works fine for this. However, it would have been useful to have the pg_lo_write() function return as advertised, it would have saved some of the 2 man hours me and the dev. team put into diagnosing this problem. -- end Now, I did a little bit of testing and when doing a \lo_export oid filename in psql connected via localhost a SIGPIPE is generated in write() in libc and psql quit, without printing any message to the terminal. Perhaps interestingly the file that gets written is always 65536 bytes long. The server log shows: 2003-06-05 14:24:02 LOG: query: select proname, oid from pg_proc where proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat'or proname = 'lo_unlink'or proname = 'lo_lseek'or proname = 'lo_tell' or proname = 'loread' or proname = 'lowrite' 2003-06-05 14:24:02 LOG: duration: 0.002924 sec 2003-06-05 14:24:03 LOG: pq_recvbuf: recv() failed: Success fwiw. The last 4 bytes saved and next 16 bytes that should follow are: 00fffc 74 f3 5f ff d0 d1 c6 b3 eb bb 01 26 d6 b3 51 a9 01000c 68 e5 70 54 Of course it could be way past there the failure point but I thought it worth including on the off chance. When I do the same except allowing psql to connect through the unix domain socket it works. The right number of bytes are returned and cmp shows no differences to the original. So, a) is this known? b) what is it? c) is it not going to happen in the new protocol? and d) does anyone care? -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] default locale considered harmful? (was Re: [GENERAL]
On Wed, 4 Jun 2003, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: That is one thing I liked about the initdb mention --- it clearly told them to watch out for something they might not have been looking for. Only if they read the message, though. People who are running RPM installations probably never get to see what initdb has to say ... so I can't put much faith in the usefulness of warnings emitted by initdb. Yes, I mentioned this when this thread was going a few weeks ago. I only caught the locale setting being wrong on a system before it went into production because I happened to install on another system and noticed the message. I then had to ask the hosting company's SA to first check and then re-initdb. I was even sat watching/directing what he was doing and missed it. He was using Redhat with RPMs I was doing it properly from source. Those RPMs are dangerous, they turn you mind off. I voted for setting 'C' by default. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgres config file: autocommit = off
On Sun, 1 Jun 2003, Rasmus Resen Amossen wrote: We have been there, done that, and decided it was a bad idea. I suggest you do a little reading in the mail list archives. I have searched the lists archives for the words commit, autocommit and transaction but couldn't find any discussion on wheter to give a database administrator the option to turn automatic commit off was is a good idea or not (not requirering the user to enter BEGIN; to start a transaction). Do you know the title of the thread or some more details on where to find the arguments? I look forward to read that discussion. :-) Personally I am managing a database for approx. 500 people which are all VERY dependant on the correctnes of the database. Sometimes, when we do manually fixes in the database, it takes a series of statements before our data mangeling leaves the databases in a consistent state again. Therefor it is a quite serious problem for us if we forget the BEGIN-word (which happens quite often :-( ), so we could really use the config file option mentioned. I can't remember the discussion very clearly but I seem to recall that it was some sort of issue with some, but not all, of the interfaces. However, ignoring that you can set autocommit to off by altering the user. For example if the user you do the fixing as as described above is called dba_1 then if you do: alter user dba_1 set autocommit to off; whenever you log in as the dba_1 user you will find that the autocommit is turned off. -- Nigel J. Andrews ---(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] Primary key and references
On Tue, 18 Mar 2003, Shridhar Daithankar[EMAIL PROTECTED] wrote: Hi, Today I discovered that if there is a compund primary key on a table, I can not create a reference from another table to one of the fields in the primary key.. Look at this.. phd=# create table tmp1(a integer,b integer,primary key(a,b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for table 'tmp1' CREATE TABLE phd=# create table tmp2(a integer references tmp1(a)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table tmp1 not found phd=# drop table tmp1; DROP TABLE phd=# create table tmp1(a integer unique,b integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for table 'tmp1' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'tmp1_a_key' for table 'tmp1' CREATE TABLE phd=# create table tmp2(a integer references tmp1(a)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE phd=# select version(); version - PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4 (1 row) That's right. The a,b combination is unique not the individual fields. Consider: Table: a | b --- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 3 | 1 Picking just a couple of examples from that a = 1 several times and b = 1 several times but there is no unique constraint violation because there isn't something like a = 1 and b = 1 as a combination appearing more than once. Note that I do not require unique check on tmp2. It is perfectly acceptable to have duplicate values in table tmp2. However no duplicates are allowed in table tmp1. I consider this as a bug but given my understanding of sql, I won't count on it. Any comments? If a is to be referenced in a foreign key it needs to be unique or how could it it be known which of the rows with a given value are being refered to. It follows that if a can be referenced in a foreign key then a uniquely identifies a row in the referenced table and therefore a primary key of (a,b) necessarily is unique based solely on a, i.e. the (a,b) combination seems unlikely to be the primary key for the table. The workaround shown here is acceptable as I don't really need a compound primary key. But If I need, I know it won't work.. I hope that helps. TIA.. Shridhar -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, Curt Sampson wrote: On Thu, 13 Feb 2003, Christopher Browne wrote: 1. It assumes that there is a location for the configuration files for /the single database instance./ No; it assumes that there's a location for the default instance. If you have more than one, you could have one default and one elsewhere, or just do what I often do, which is put in an empty config file except for a comment saying we have several instances of xxx on this machine; look in yyy for them. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. Right. It's dependent on the sysadmin to create /etc/postgres/ and make it writeable, or set up proper symlinks, or whatever. Fortunately, the files in /etc are only the defaults, to be used if they're not overridden on the command line. If you're in a situation like #2, you're basically stuck where we are now all the time: you have to just put it somewhere and hope that, if someone else needs to find it, they can. It doesn't follow this line of argument directly but it's to do with this thread... Is everyone forgetting that wherever the configuration file is stored and whether or not it needs a command line argument to specify it the database is not going to start up automatically unless at least part of the installation is done as root anyway? As I like to install software as a non root user normally anyway I am happy that the config file lives somewhere not requiring write access by the installer. However, I think having it in an etc directory is a good thing (tm). So, colour me an uncommited, fence sitter :) I'm not talking distribution/package installation here but just plain system administration. Being an untrusting soul I do _not_ want to type make install as root and find things installed outside of where I say I want things placed. That includes configuration files. Doing this as a normal user protects the system from bad software which assumes things about the host system. It also simplifies switching between versions of software, try doing that if your config is /etc/postgresql/postgres.conf. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Maximum Size for Large Object / TOASTed Object
On Tue, 11 Feb 2003, Paul Ramsey wrote: Hi All, What is the maximum size a large object can be in PostgreSQL? What is the maximum size a TOASTed object can be in PostgreSQL? The PostgreSQL Limitations page says the maximum size of a field is 1 Gb, but does a large object constitute a field? :) Thanks, Paul I don't know but large objects are stored in the filesystem so I presume any limit is going to apply there. A large object isn't a field, the large object id can, and very probably should, be stored in one though. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] Cannot connect to the database (PG 7.3)
On Tue, 28 Jan 2003, Tom Lane wrote: I wrote: Michiel Lange [EMAIL PROTECTED] writes: It is, somehow, not possible to connect as a user which name is completely numeric. I muttered nonsense! to myself, but darned if you're not right: regression=# create user 12345; CREATE USER regression=# \q $ psql -U 12345 regression psql: FATAL: SET SESSION AUTHORIZATION: permission denied Will look into it. After some looking, it appears the culprit is assign_session_authorization() in commands/variable.c, which is assuming that a numeric-looking parameter string should be taken as a numeric user sysid, rather than an actual user name. The reason this was done was to avoid the need to do catalog lookups when restoring a prior setting during error recovery. That's still a valid concern, so right offhand I don't see an easy fix. Any ideas? How about throwing an error if an all digit user name is given to create user as already alluded to? Seems that would be simple, not that I know anything about the parser, but does that break any standards? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Yaarrgh! CVS remote buffer overflow
On 21 Jan 2003, Doug McNaught wrote: It's all over Slashdot: http://security.e-matters.de/advisories/012003.html That bit about 'This does not apply to :pserver: only' (probably slightly paraphrased) is very confusing. I gather from later on in the page that it means that the flaw only applies to the pserver method. -- Nigel J. Andrews ---(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] Indexes
On Thu, 16 Jan 2003, Tom Lane wrote: mlw said: Does anyone think it would be a good idea, or is it even practical, to have a 'indx' subdirectory along side of the 'base' directory? I was thinking that, if it were an easy modification, that it could be an easy way to separate data and indexes to different hard disks. This and other quick hacks have been discussed before. I think the consensus has been to do nothing until someone gets around to writing a general-purpose tablespace implementation. Wasn't someone just about done with a tablespace implementation? I certainly remember some discussion on this subject a few months ago. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I feel the need for speed. What am I doing wrong?
Added -general list so that the next followup can remove -hackers and everyone there will have had notice. On Mon, 6 Jan 2003, Dann Corbit wrote: I have a query using two postgres tables. One is called CNX_DS_53_SIS_STU_OPT_FEE_TB and the other is called CNX_DS2_53_SIS_STU_OPT_FEE_TB. I am getting 3 times slower performance than Microsoft Access when performing a left outer join. ... Here is the query: select a.RT_REC_KEY, a.cnxarraycolumn, a.CRC from CNX_DS_53_SIS_STU_OPT_FEE_TB a left outer join CNX_DS2_53_SIS_STU_OPT_FEE_TB b on ( a.RT_REC_KEY = b.RT_REC_KEY and a.cnxarraycolumn = b.cnxarraycolumn) where b.oid is null ; Creating the following index had no effect on performance! create unique index i1 on CNX_DS2_53_SIS_STU_OPT_FEE_TB (RT_REC_KEY, cnxarraycolumn, CRC); Both tables had 6139062 rows of data. In this query ... all rows of data match perfectly, so no results are returned. I suspect you get no results because it's unlikely b.oid will be null. Are you sure the query is how it should be since you seem to be expecting no rows to be returned and yet your reason for that doesn't match the query as shown. Without the oid test I'd bet you get a result set of 6139062 rows. Is there a way to reformulate this query so that it will use the index? Given the above comment I'd say no since the entirety of both tables will be tested to make the result set. Alternatively, if the query is right try something along the lines of: SELECT a.blah, a.foo, FROM a, b WHERE a.blah = b.blah AND a.foo = b.foo AND b.oid IS NULL if that doesn't use a query try pushing the null test into a subselect like: SELECT a.blah, a.foo, FROM a, (SELECT * FROM b WHERE oid IS NULL) b WHERE a.blah = b.blah AND a.foo = b.foo After that let's hope I haven't embarrassed myself. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres Security Expert???
FWIW, a search on Google gives some hits for the name on the lists this year. First impressions are that it's not Sir Mondred (or whatever the spelling was). On Tue, 26 Nov 2002, Justin Clift wrote: Hi Chris, Just received this from them. Look like he was trying to claim stuff that wasn't true. :-/ Thanks for pointing this out Chris. :) Regards and best wishes, Justin Clift *** Original Message Subject: Re: Demande de renseignements Defi SYSDOOR Date: Tue, 26 Nov 2002 11:04:47 +0100 From: Vergoz Michael (SYSDOOR) [EMAIL PROTECTED] To: [EMAIL PROTECTED] References: 200211260205.gAQ25GTK009595@jenna Dear Clift, Justin Clift PostgreSQL Global Development Group demande des informations son adresse : Son e-mail : [EMAIL PROTECTED] Son téléphone : +61.393631313 Son message : Hi, Just noticed your website mentioning that Michael Vergoz is well known to created security patches for PostgreSQL: http://kernel.sysdoor.com/eng/ Can you please point us in their direction, as we don't know him by name. Right, it's true that i never make _security_ patches for PostGreSQL... As a side thought, would you please be able to correct the spelling of PostgreSQL on the same page. Presently it's spelt PostGreSQL, which is incorrect. Better way, i'v remove postgresql name in the site, as i think you want. Regards and best wishes, Justin Clift -- - Source IP : 203.173.161.124 (p378-tnt1.mel.ihug.com.au) Secure ID : [EMAIL PROTECTED] -- - Best Regards, Vergoz Michael SYSDOOR Founder *** ---(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] Need Quote for 7.3
On Tue, 19 Nov 2002, Josh Berkus wrote: Folks, We need a quote from a major code contributor to PostgreSQL about the upcoming 7.3 release -- something about how great the new release is, or some of the features in the release. We need this for the 7.3 press release, which will be drafted in 2 days. If you have something to say, please e-mail me, Marc ([EMAIL PROTECTED]) and Justin ([EMAIL PROTECTED]) off-list so we can quote you! I think it's great - but don't quote me on that. :) -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Debian build prob
On Thu, 14 Nov 2002, Patrick Welche wrote: Believe it or not, I'm trying to compile today's cvs pgsql on a Debian 2.2.19 system. Compilation dies while compiling pg_dump with ../../../src/interfaces/libpq/libpq.so: undefined reference to `atexit' In the mail archives there is a mention of upgrading libc to libc6-dev_2.2.5-3_i386.deb. As far as I can tell, that should read libc6_2.2.5-3_i386.deb, and again AFAICT this system already has libc6_2.2.5-6_i386.deb on it. I can see atexit is undefined in libpq, and it is defined in /usr/lib/libc.a. For some reason /lib/libc*.so are stripped, so it is hard to tell, but I assume it must be the same as for /usr/lib/libc.a. Have any of you managed to compile postgresql on an oldstable Debian system? The latest I've built was from somewhere like the beta 3 mark but yes, built it on a Debian 2.2 installation with no library upgrades or anything. Now of course one would need a new bison. -- Nigel J. Andrews ---(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] RC1?
On Tue, 12 Nov 2002, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian writes: Are we ready for RC1 yet? Questionable. We don't even have 50% confirmation coverage for the supported platforms yet. We can't just wait around indefinitely for port reports that may or may not ever appear. In any case, most of the 7.3 entries in the list seem to be various flavors of *BSD; I think it's unlikely we broke those ... FWIW, gmake check and gmake bigcheck pass on: FreeBSD 3.3-RELEASE #3: Thu Feb 3 23:48:56 GMT 2000 using: gcc -v gcc version 2.7.2.3 and ld -v GNU ld version 2.9.1 (with BFD 2.9.1) with: ./configure --prefix=/usr/local/pgsql-7.2.1 --enable-multibyte --with-perl --with-tcl --enable-odbc --with-pam --enable-syslog --with-tclconfig=/usr/local/lib/tcl8.0 --with-tkconfig=/usr/local/lib/tk8.0 --with-includes=/usr/local/include/tcl8.0:/usr/local/include/tk8.0 with the expection of: *** 214,220 SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 '0.0'; SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; ! ERROR: Bad float8 input format -- overflow SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; ERROR: pow() result is out of range SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; --- 214,220 SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 '0.0'; SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; ! ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by zero SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; ERROR: pow() result is out of range SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; in the float8 test. -- Nigel J. Andrews Logictree Systems Limited ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RC1?
On Wed, 13 Nov 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: FWIW, gmake check and gmake bigcheck pass on: FreeBSD 3.3-RELEASE #3: Thu Feb 3 23:48:56 GMT 2000 with the expection of: [snipped] in the float8 test. Okay, looks like we need to use float8-fp-exception.out on your platform. This is a bit surprising since resultmap presently shows float8/i.86-.*-freebsd=float8-small-is-zero How shall we distinguish your version of freebsd from the ones that need the other comparison file? regards, tom lane Is it necessary, I mean really necessary to distinguish this system? It's quite an old installation [that ain't broke so I ain't fixed it] and the difference is only the error message. I hadn't even looked to see if there was a better expected output file, just accepted it as a normal, acceptable variation in the regression tests. I don't know anything about how the tests are put together so I'd have to look into that before suggesting a way to differentiate my system. Having said that wouldn't the 3.3-RELEASE string be sufficient? -- Nigel J. Andrews ---(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] Memory leaks
On Tue, 22 Oct 2002, Tom Lane wrote: Greg Copeland [EMAIL PROTECTED] writes: Interesting. Having not looked at memory management schemes used in the pl implementations, can you enlighten me by what you mean by integrate the memory-context notion? Does that mean they are not using palloc/pfree stuff? Not everywhere. plpgsql is full of malloc's and I think the other PL modules are too --- and that's not to mention the allocation policies of the perl, tcl, etc, language interpreters... I was going to make the suggestion that malloc et al. could be replaced with palloc etc but then that raises too many complications without just shooving everything into a long lived context anyway. Also I think we've got to rely on, i.e. it is sensible to do so, the underlying language handling memory correctly. Hmmm...there do seem to be a few mallocs in plpython.c . I haven't looked very closely but nothing jumped out at me as being obviously wrong from the grep output. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Memory leaks
On 22 Oct 2002, Greg Copeland wrote: On Tue, 2002-10-22 at 17:09, Tom Lane wrote: plpgsql has some issues too, I suspect, but not as bad as pltcl etc. Possibly the best answer is to integrate the memory-context notion into those modules; if they did most of their work in a temp context that could be freed once per PL statement or so, the problems would pretty much go away. Interesting. Having not looked at memory management schemes used in the pl implementations, can you enlighten me by what you mean by integrate the memory-context notion? Does that mean they are not using palloc/pfree stuff? I saw use of a couple of malloc (or Python specific malloc) calls the other day but I also seem to recall that, after consideration, I decided the memory needed to survive for the duration of the backend. Should I have created a new child of the top context and changed these malloc calls? I was going to ask about thoughts on redirecting malloc etc to palloc etc and thereby intercepting memory allocation within the languages and automatically bringing them into the memory context realm. However, that would just be making life way too awkward, bearing in mind the above paragraph. Can't we get Sir Mongle (or whatever the name was) to test these things under the auspices of them being DoS attacks? -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Freeing plan memory
I notice there's a leak of memory in SPI_prepare().
The full fix is nontrival and I don't want to submit a half solution so I
thought I'd check whether people think it's worth worrying about.
The leak is that memory is grabbed in SPI_prepare() for a plan within whatever
context is current when it does the palloc(). It may be the caller's or it may
be the relevent SPI one. The plan is then copied out of this memory [and
context] into a child of the procedure's context and forgotten about, or just
plain forgotten. Obviously the intention is that this memory is freed when the
context is deleted and is probably not a problem unless someone does something
like:
i = 10;
while (i--)
{
plan = SPI_prepare(SELECT 1, 0, (Oid *)NULL);
SPI_freeplan(plan); /* SPI_freeplan() is not just for SPI_saveplan() */
}
Is this worth worrying about?
Any busy person can stop reading now as the above defines the problem while the
below only shows an easily reproducable example.
FWIW, I found it while testing something like, which is a little less daft
than the above example:
create function atest1 ( ) returns int as '
a = 0
while a 1:
plan = plpy.prepare(SELECT + repr(a))
a = a + 1
' language 'plpython';
Here the plpython code uses SPI_freeplan to release the context holding the
plan memory when each plan object returned by plpy.prepare() is garbage
collected. This seems sensibly to happen when the plan variable is
reassigned. However I was baffled why the process still had an obvious memory
leak so looked a little closer at SPI.
--
Nigel J. Andrews
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Freeing plan memory
On Sat, 19 Oct 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: The leak is that memory is grabbed in SPI_prepare() for a plan within whatever context is current when it does the palloc(). It may be the caller's or it may be the relevent SPI one. The plan is then copied out of this memory [and context] into a child of the procedure's context and forgotten about, or just plain forgotten. Au contraire: SPI_prepare builds the plan in its execCxt, which is reset before returning (look at _SPI_begin_call and _SPI_end_call). So I see no leak there. Ah, yes, I see that now. I'm not sure where the leak is in your plpython example, but I'd be inclined to look to plpython itself, perhaps even just the string concatenation expression in plan = plpy.prepare(SELECT + repr(a)) Well it's not that string operation. plpgsql used to have terrible intra-function memory leaks, and only by dint of much hard work has it been brought to the point where you can expect a long loop in a plpgsql function not to chew up memory. AFAIK, no one has yet done similar work for the other PL languages. Hmmm...my test case should boil down to a fairly small number of other calls in the SPI_prepare wrapper and a quick looks doesn't show anything interesting. Not sure I've got the time to dedicate to investigating this but I'll look at it as and when I can. I'm sending a patch for plpython.c to -patches which fixes a mistake I made in the previous patch. -- Nigel J. Andrews ---(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] Little note to php coders
On Tue, 8 Oct 2002, Sir Mordred The Traitor wrote: Check out this link, if you need something to laugh at: http://www.postgresql.org/idocs/index.php?1' Keeping in mind, that there are bunch of overflows in PostgreSQL(really?), it is very dangerous i guess. Right? I'm not sure what list this really fits onto so I've left as hackers. The old argument about data validation and whose job it is. However, is there a reason why all CGI parameters aren't scanned and rejected if they contain any punctuation. I was going to say if they contain anything non alphanumeric but then I'm not sure about internationalisation and that test. -- Nigel J. Andrews ---(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
[HACKERS] anoncvs and diff
I've been waiting to see how a patched file differs from my version. The patch was added to the to apply list last week I think (it wasn't mine btw) and I've been doing cvs diff to view the differences so I can tell when the patch has been applied. Additional information given by this is the revision number the comparison is against of course. This has stayed at 1.61 all the time I've been doing this cvs diff operation. Looking at the web interface to cvs I see the file has a revision number of 1.64. I use the anoncvs server for my operations. Am I being daft or is there a problem with the anoncvs archive? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] anoncvs and diff
On Thu, 3 Oct 2002, Bruce Momjian wrote: Nigel J. Andrews wrote: cvs diff -r HEAD pltcl.c gave me differences against revision 1.64 and cvs update pltcl.c said it was merging changes between 1.64 and 1.61 and a plain cvs diff now shows me differences against 1.64 I think this is probably just a short fall in my fairly basic knowledge of how cvs works. What does 'cvs log' say about the file, especially the top stuff? It gave me the log all the way up to the 1.64 revision with the REL7_3_STABLE label assigned to revision 1.64.0.2 Revision 1.64 apparently backing out my patch which made 1.63. I had a brain wave and did the cvs log command which was what lead me to try specifying revisions. As I say it looks like a lack of knowledge about how cvs works for these things. I always thought it worked like RCS and gave a diff against the latest checked in but obviously not. BTW, I've found Neil Conway's patch for this file, email dated 25th Sept., I can forward it or apply it and include the changes along with whatever I do for my next submission, which ever you'd prefer. I'd suggest it's easy to let me apply and submit it due to overlaps. -- Nigel J. Andrews ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Fwd: [HACKERS] int type problem in 7.3
On Wed, 2 Oct 2002, Mario Weilguni wrote: But oracle accepts this one: SQL select * from re_eintraege where id=''; no rows selected because oracle treats the empty string as NULL Oracle does that for string data, but it doesn't do it for numerics does it? In any case, that behavior is surely non-compliant with the SQL spec. No, oracle accepts this and works correctly with number() datatype. However I did not know that in postgres '' was treated as '0'. So what would I be selecting in Oracle if I did: SELECT * FROM mytable WHERE myfield = '' where myfield is of VARCHAR type? If you want to select on NULL, whether or not you think the database is more intelligent than you in determining what you really want, then write your query to select on NULL. The chances are your database is not actually a mind reader. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pltcl.so patch
On 25 Sep 2002, Neil Conway wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Yes, I do get the similar results. A quick investigation shows that the SPI_freetuptable at the end of pltcl_SPI_exec is trying to free a tuptable of value 0x82ebe64 (which looks sensible to me) but which has a memory context of 0x7f7f7f7f (the unallocated marker). Attached is a patch against CVS HEAD which fixes this, I believe. The problem appears to be the newly added free of the tuptable at the end of pltcl_SPI_exec(). I've added a comment to that effect: /* * Do *NOT* free the tuptable here. That's because if the loop * body executed any SQL statements, it will have already free'd * the tuptable itself, so freeing it twice is not wise. We could * get around this by making a copy of SPI_tuptable-vals and * feeding that to pltcl_set_tuple_values above, but that would * still leak memory (the palloc'ed copy would only be free'd on * context reset). */ That's certainly where the fault was happening. However, that's where the original memory leak problem was coming from (without the SPI_freetuptable call). It could be I got that fix wrong and the extra calls you've added are the right fix for that. I'll take a look to see what I can learn later. At least, I *think* that's the problem -- I've only been looking at the code for about 20 minutes, so I may be wrong. In any case, this makes both memleak() and memleak(1) work on my machine. Let me know if it works for you, and/or if someone knows of a better solution. I'll have to check later. I also added some SPI_freetuptable() calls in some places where Nigel didn't, and added some paranoia when dealing with statically sized buffers (snprintf() rather than sprintf(), and so on). I also didn't include Nigel's changes to some apparently unrelated PL/Python stuff -- this patch includes only the PL/Tcl changes. I dare say the plpython needs to be checked by someone who knows how to since I can well imagine the same nested call fault will exist there. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pltcl.so patch
Okay, I've looked again at spi_exec and I believe I can fix the bug I introduced and the memory leak. However, I have only looked quickly and not made these most recent changes to the execp version nor to the plpython code. Therefore I am not attaching a patch at the moment, just mentioning that I've straightened this out in my brain a bit more. On Wed, 25 Sep 2002, Nigel J. Andrews wrote: On 25 Sep 2002, Neil Conway wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Yes, I do get the similar results. A quick investigation shows that the SPI_freetuptable at the end of pltcl_SPI_exec is trying to free a tuptable of value 0x82ebe64 (which looks sensible to me) but which has a memory context of 0x7f7f7f7f (the unallocated marker). Attached is a patch against CVS HEAD which fixes this, I believe. The problem appears to be the newly added free of the tuptable at the end of pltcl_SPI_exec(). I've added a comment to that effect: /* * Do *NOT* free the tuptable here. That's because if the loop * body executed any SQL statements, it will have already free'd * the tuptable itself, so freeing it twice is not wise. We could * get around this by making a copy of SPI_tuptable-vals and * feeding that to pltcl_set_tuple_values above, but that would * still leak memory (the palloc'ed copy would only be free'd on * context reset). */ That's certainly where the fault was happening. However, that's where the original memory leak problem was coming from (without the SPI_freetuptable call). It could be I got that fix wrong and the extra calls you've added are the right fix for that. I'll take a look to see what I can learn later. At least, I *think* that's the problem -- I've only been looking at the code for about 20 minutes, so I may be wrong. In any case, this makes both memleak() and memleak(1) work on my machine. Let me know if it works for you, and/or if someone knows of a better solution. I'll have to check later. I also added some SPI_freetuptable() calls in some places where Nigel didn't, and added some paranoia when dealing with statically sized buffers (snprintf() rather than sprintf(), and so on). I also didn't include Nigel's changes to some apparently unrelated PL/Python stuff -- this patch includes only the PL/Tcl changes. I dare say the plpython needs to be checked by someone who knows how to since I can well imagine the same nested call fault will exist there. -- Nigel J. Andrews ---(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] pltcl.so patch
In answer to the question posed at the end of the message below:
Yes, I do get the similar results.
A quick investigation shows that the SPI_freetuptable at the end of
pltcl_SPI_exec is trying to free a tuptable of value 0x82ebe64 (which looks
sensible to me) but which has a memory context of 0x7f7f7f7f (the unallocated
marker).
Briefly following through to check this value shows that as long as I have
CLOBBER_FREED_MEMORY defined, which I presume I do having configured with
--debug, this value is also consistent with the tuptable having been freed
before this faulting invocation.
I haven't looked too closely yet but at a glance I can't see what could be
going wrong with the exception that the tuptable is freed even if zero rows are
returned by SPI_exec. That and I'm not sure what that $T(id) thing is doing in
the SQL submited to pltcl_SPI_exec. Oh 'eck, I've been reading that test
function wrong, it's got a level of nesting.
Unfortunately, I am currently trying to throw together a quick demo of
something at the moment so can't investigate too fully for the next day or so.
If someone wants to pick this up feel free otherwise I'll look into it later.
--
Nigel J. Andrews
On Tue, 24 Sep 2002, Ian Harding wrote to me:
First, thank you very much for working on this issue. Pltcl is extremely important
to me right now, and this memory leak is cramping my style a bit.
I applied the patch you sent to my pltcl.c (I am at version 7.2.1, but it seems to
apply fine...) It builds fine, psql starts fine, but my test function still blows up
dramatically.
Here is the script I am using:
drop function memleak();
create function memleak() returns int as '
for {set i 1} {$i 100} {incr i} {
set sql select ''foo''
spi_exec $sql
}
' language 'pltcl';
drop table testable;
create table testable (
id int,
data text);
insert into testable values (1, 'foobar');
insert into testable values (2, 'foobar');
insert into testable values (3, 'foobar');
insert into testable values (4, 'foobar');
insert into testable values (5, 'foobar');
insert into testable values (6, 'foobar');
drop function memleak(int);
create function memleak(int) returns int as '
set sql select * From testable
spi_exec -array T $sql {
for {set i 1} {$i 100} {incr i} {
set sql select * from testable where id = $T(id)
spi_exec $sql
}
}
' language 'pltcl';
Here is what happens:
bash-2.05# psql -U iharding test testfunction
DROP
CREATE
ERROR: table testable does not exist
CREATE
INSERT 118942676 1
INSERT 118942677 1
INSERT 118942678 1
INSERT 118942679 1
INSERT 118942680 1
INSERT 118942681 1
DROP
CREATE
bash-2.05# psql -U iharding test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# select memleak();
memleak
-
0
(1 row)
test=# select memleak(1);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
Here is the end of the log:
DEBUG: server process (pid 1992) was terminated by signal 11
DEBUG: terminating any other active server processes
DEBUG: all server processes terminated; reinitializing shared memory and semaphores
IpcMemoryCreate: shmget(key=5432001, size=29769728, 03600) failed: Cannot allocate
memory
This error usually means that PostgreSQL's request for a shared
...
Do you have similar results?
---(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] PGXLOG variable worthwhile?
On Sun, 22 Sep 2002, Tom Lane wrote: It was pretty clear that Thomas' original patch lost the vote, or would have lost if we'd bothered to hold a formal vote. Hasn't there just been a formal vote on this? I don't see anyone arguing against the notion of making XLOG location more easily configurable --- it was just the notion of making it depend on environment variables that scared people. And it's obvious it was centred on the use of an environment variable from the subject line, it's still got PGXLOG in capitals in it. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SCSI Error
On Fri, 20 Sep 2002, Ricardo Fogliati wrote: Hiya Lists Somebody could help me? I am with an error when the Postgresql makes Insert, Delete or Update kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938856 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938800 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938864 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938872 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938808 Version: postgresql-7.2.1-5 [deleted]... Kernel: Linux version 2.4.7-10custom (gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)) #9 Mon Sep 16 17:50:13 BRT 2002 Not sure what you're asking for. That's a hardware error. Back up immediately, if you haven't already got decent backups, and fix the disk/controller. Could possibly be a filesystem error but even if so it's still casting doubt on the hardware. On the other hand I do believe I saw a message recently saying that some of the 2.4 series kernels had file system bugs. I don't know which, someone else might be able to expand. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving speed of copy
On Fri, 20 Sep 2002, Shridhar Daithankar wrote: In select test where approx. 15 rows where reported with query on index field, mysql took 14 sec. and psotgresql took 17.5 sec. Not bad but other issues eclipse the result.. I don't know about anyone else but I find this aspect strange. That's 1 second (approx.) per row retrieved. That is pretty dire for an index scan. The data/index must be very non unique. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Memory Errors...
On 20 Sep 2002, Greg Copeland wrote: I'll try to have a look-see by the end of the weekend. Any code that can reproduce it or is it ANY code that uses SPI? Greg On Fri, 2002-09-20 at 11:39, Peter Eisentraut wrote: Tom Lane writes: On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. There's a note in the PL/Python documentation that it's leaking memory if SPI plans are used. Maybe that's related and someone could take a look at it. I've added the call to free the tuptable just as in the pltcl patch I submited earlier (which I can't remember if I've seen in the list so I may well resend). However, the comments in the code imply there might be another leak with prepared plans. I'm looking into that so I won't be sending this patch just yet. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Memory Errors...
Ok, below is the original email I sent, which I can not remember seeing come
across the patches list. Please do read the assumptions since they might throw
up problems with what I have done.
I have attached the pltcl patch again, just in case. For the sake of clarity
let's say this patch superscedes the previous one.
I have also attached a patch addressing the similar memory leak problem in
plpython. This includes a slight adjustment of the tests in the source
directory. The patch also includes a cosmetic change to remove a compiler
warning although I think the change makes the code look worse though.
Once again, please read my text below and also take a quick look at the comment
I've added in the plpython patch since it may well show that that
particular change is complete rubbish.
BTW, by my reckoning the memory leak would occur with prepared plans and
without. If that is not the case then I've been barking up the wrong tree.
Of further note, I have not tested for the memory leak in plpython but the
build passes the normal and big checks. However, I have tried testing using the
test.sh script in src/pl/plpython. This seems to be generating errors where
before there were warnings. Can anyone comment on the correctness of this?
Reversing my changes doesn't really help matters so I presume it is something
else that is causing the different behaviour.
--
Nigel J. Andrews
On Fri, 20 Sep 2002, Nigel J. Andrews wrote:
On Thu, 19 Sep 2002, Tom Lane wrote:
Ian Harding [EMAIL PROTECTED] writes:
It is pltcl [not plpgsql]
Ah. I don't think we've done much of any work on plugging leaks in
pltcl :-(.
It hurts when I do this:
drop function memleak();
create function memleak() returns int as '
for {set counter 1} {$counter 10} {incr counter} {
set sql select ''foo''
spi_exec $sql
}
' language 'pltcl';
select memleak();
Yeah, I see very quick memory exhaustion also :-(. Looks like the
spi_exec call is the culprit, but I'm not sure exactly why ...
anyone have time to look at this?
Attached is a patch that frees the SPI_tuptable in all post SPI_exec
non-elog paths in both pltcl_SPI_exec() and pltcl_SPI_execp().
The fault as triggered by the above code has been fixed by this patch but
please read my assumptions below to ensure they are correct.
I have assumed that Tom's comment about this only being required in non-elog
paths is correct, which seems a reasonable assumption to me.
I have also assumed, rather than verified, that freeing the tuptable does
indeed free the tuples as well. Tests with the above function show that the
process does not increase it's memory footprint during it's operation, although
if my assumption here is wrong this could be a feature of selecting
insignificantly sized tuples.
I have not worried about other uses of SPI_exec for selects in pltcl.c on the
basis that those are not under the control of the function writer and the
normal function management will release the storage.
Index: src/pl/plpython/feature.expected
===
RCS file: /projects/cvsroot/pgsql-server/src/pl/plpython/feature.expected,v
retrieving revision 1.4
diff -c -r1.4 feature.expected
*** src/pl/plpython/feature.expected2002/03/06 18:50:31 1.4
--- src/pl/plpython/feature.expected2002/09/20 22:12:36
***
*** 29,35
(1 row)
SELECT import_fail();
! WARNING: ('import socket failed -- untrusted dynamic module: _socket',)
import_fail
failed as expected
--- 29,35
(1 row)
SELECT import_fail();
! NOTICE: ('import socket failed -- untrusted dynamic module: _socket',)
import_fail
failed as expected
Index: src/pl/plpython/plpython.c
===
RCS file: /projects/cvsroot/pgsql-server/src/pl/plpython/plpython.c,v
retrieving revision 1.22
diff -c -r1.22 plpython.c
*** src/pl/plpython/plpython.c 2002/09/04 22:51:23 1.22
--- src/pl/plpython/plpython.c 2002/09/20 22:12:40
***
*** 408,414
--- 408,416
else
PLy_restart_in_progress += 1;
if (proc)
+ {
Py_DECREF(proc-me);
+ }
RERAISE_EXC();
}
***
*** 1841,1847
--- 1843,1856
*
* FIXME -- leaks saved plan on object destruction. can this be
* avoided?
+* I think so. A function prepares and then execp's a statement.
+* When we come to deallocate the 'statement' object we obviously
+* no long need the plan. Even if we did, without the object
+* we're never going to be able to use it again
Re: [HACKERS] [GENERAL] Memory Errors...
Ian Harding [EMAIL PROTECTED] writes: It is pltcl [not plpgsql] Quick, minor point, in the manner of a question: Why is the pltcl directory called tcl where all the other pls are pllanguage? That's in src/pl of course. Also in my anoncvs fetch which is a few weeks old now being from the day before beta freeze. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Memory Errors...
On Thu, 19 Sep 2002, Joe Conway wrote: Tom Lane wrote: I said: Yeah, I see very quick memory exhaustion also :-(. Looks like the spi_exec call is the culprit, but I'm not sure exactly why ... anyone have time to look at this? On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. pltcl_SPI_execp() has a similar problem, and there may be comparable bugs in other pltcl routines (not to mention other sources of memory leaks, but I think this is the problem for your example). I have no time to work on this right now; any volunteers out there? I can give it a shot, but probably not until the weekend. I haven't really followed this thread closely, and don't know tcl very well, so it would help if someone can send me a minimal tcl function which triggers the problem. I can probably take a look at this tomorrow, already started by looking at the pltcl_SPI_exec routine. I think a quick glance at ...init_unknown() also shows a lack of tuptable freeing. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PGXLOG variable worthwhile?
On Tue, 17 Sep 2002, Bruce Momjian wrote: Dave Page wrote: Which in this case is what puzzles me. We are only talking about a simple GUC variable after all - I don't know for sure, but I'm guessing it's not a huge effort to add one? Can we get agreement on that? A GUC for pg_xlog location? Much cleaner than -X, doesn't have the problems of possible accidental use, and does allow pg_xlog moving without symlinks, which some people don't like? If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. GUC instead of -X or PGXLOG : yes. However, how is that going to work if tablespaces are introduced in 7.4. Surely the same mechanism for tablespaces would be used for pg_xlog. As the tablespace mechanism hasn't been determined yet, as far as I know, wouldn't it be best to see what happens there before creating the TODO item for the log? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] SRF and pg_group
I realise that this has already been done, by Joe Conway I think. Indeed I was
looking at this just before beta1 when I happened to notice the post giving the
plpgsql function. However, as I had started work on it and I was interested in
seeing how things should be done I continued, only not in so much of a rush.
In the interests on finding out if I have approached this the right way, or the
way a more experienced backend programmer would, I'd appreciate any comments on
the attached .c file. In particular, I'm not sure what I'm doing with regard to
memory contexts, I think I may have one unnecessary switch in there, and in
general I seem to be doing a lot of work just to find out tidbits of
information.
I based this on, i.e. started by editing, Joe Conway's tablefunc.c but I think
there's very little of the original left in there.
I've also attached the .h, Makefile and .sql.in files to make this work if
anyone is interested in giving it a run. The .sql.in shows the usage. I did
this in a directory called pggrouping, for the sake of a better name, under the
contrib directory in my tree, so that's probably the best place to build it.
Thanks, and sorry for adding to people's email and work load.
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
/*
* Derived from tablefunc.c, a sample to demonstrate C functions which
* return setof scalar and setof composite by Joe Conway [EMAIL PROTECTED]
*
* Copyright 2002 by PostgreSQL Global Development Group
*
* Permission to use, copy, modify, and distribute this software and its
* documentation for any purpose, without fee, and without a written agreement
* is hereby granted, provided that the above copyright notice and this
* paragraph and the following two paragraphs appear in all copies.
*
* IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
* DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
* DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
* INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
* AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
* ON AN AS IS BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
* PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
*
*/
#include stdlib.h
#include math.h
#include postgres.h
#include fmgr.h
#include funcapi.h
#include executor/spi.h
#include utils/builtins.h
#include utils/guc.h
#include utils/lsyscache.h
#include pggrouping.h
typedef struct unpack_array_fctx
{
SPITupleTable *spi_tuptable; /* sql results from user query */
TupleDesc tupdesc; /* TupleDesc for results */
int unpack_attrnum; /* attribute number to be unpacked */
int lastcall_cntr; /* previous call_cntr, invlaid = -1 */
int lastindex; /* index of the last array item sent, invalid 1 */
} unpack_array_fctx;
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
static Datum expandArray_SRF(FunctionCallInfo info,
char *sql,
int unpackAttrNum);
static Datum expandArray_SRF_FirstCall(FunctionCallInfo fcinfo,
FuncCallContext *funcctx,
const char *sql,
const int unpackAttrNum);
static Datum expandArray_SRF_GetTuple(FunctionCallInfo fcinfo,
FuncCallContext *funcctx);
static TupleDesc makeUnpackedTupleDesc(TupleDesc src_tupdesc,
int unpack_attrnum);
static bool similarTupleDescs(TupleDesc ret_tupdesc,
TupleDesc sql_tupdesc);
/*
* pg_group_expandusers
*
* Return pg_group where each tuple has grolist attribute of int4[] type
* changed to be of type int4 and to hold only one user id.
*/
PG_FUNCTION_INFO_V1(pg_group_long);
Datum
pg_group_long(PG_FUNCTION_ARGS)
{
return expandArray_SRF(fcinfo,
select groname,grosysid,grolist from pg_group,
3);
}
/*
* expand_array_srf
*
* Return tuples such that the elements of an array attribute are
* extracted in turn and placed into the output instead of the array.
* Declared to fmgr as:
* CREATE FUNCTION the_name(text,integer) RETURNS SETOF RECORD ...
*
* where the text argument is the query string to obtain the source
* data and the integer argument gives the column number of the array
* to expand.
*
* Note, despite checking number of arguments this is in no way safe
* from some one creating a fmgr function which uses wrong argument types.
*/
PG_FUNCTION_INFO_V1(expand_array_srf);
Datum
expand_array_srf(PG_FUNCTION_ARGS)
{
if (fcinfo-nargs != 2)
elog(ERROR
[HACKERS] Memory management question
It's probably a pretty basic question explained in some document I haven't seen but...if I do something like a CreateTupleDescCopy() how do I know my memory context owns everything allocated without following the code all the way through until it returns to me? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Memory management question
On Tue, 3 Sep 2002, Karel Zak wrote: On Tue, Sep 03, 2002 at 12:28:37PM +0100, Nigel J. Andrews wrote: It's probably a pretty basic question explained in some document I haven't seen but...if I do something like a CreateTupleDescCopy() how do I know my memory context owns everything allocated without following the code all the way through until it returns to me? If some code doesn't call MemoryContextSwitchTo() all is allocated in current memory context. You can check if CurrentMemoryContext is same before and after call that is important for you - but this check say nothing, bacuse some code can switch to other context and after usage switch back to your context. IMHO is not common way how check it. (Ok, maybe check all contexts size before/after call...) Suggestion: add to memory managment counter that handle number of MemoryContextSwitchTo() calls. IMHO it can be compile only if MEMORY_CONTEXT_CHECKING is define. I quite like that idea. Only thing is it doesn't full address the issue of identifying if my context owns memory allocated by other functions I've used. For example: A called procedure could be doing (psuedo code obviously): SwitchContext() mem=palloc(anumber) /* use mem */ pfree(mem) SwitchContectBack() retmem=palloc(anothersize) There, net effect is that I do own retmem but the test on context switch counters would indicate that I may not. I think the problem is that I don't fully understand why [and when] is context switch is or should be done. But I think there is not to much places which switching between contexts and all are good commented (I hope, I wish :-) As someone pointed out my example wasn't very complex so checking the source wasn't onerous. Checking something like heap_modifytuple() is more time consuming. I was hoping there was some sort of 'rule of thumb'. In general I can't see how it could be sensibly known without such a rule and without tracing through the source. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Impending freeze
When is the beta freeze? I've just started looking at a ToDo list item and hope it won't take too long. However, I've got other things to do and this is the first I've looked in this area. An idea about time left to complete it would be good so I decide if I'm wasting my time and effort at the moment. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Impending freeze
On Mon, 2 Sep 2002, Gavin Sherry wrote: On Sun, 1 Sep 2002, Nigel J. Andrews wrote: When is the beta freeze? Today. Oops, my fault for being imprecise. I was wondering what time of day with timezone. Someone suggested end of today but that means different times to different people. -- Nigel Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Impending freeze
On Sun, 1 Sep 2002, Marc G. Fournier wrote: On Sun, 1 Sep 2002, Nigel J. Andrews wrote: On Mon, 2 Sep 2002, Gavin Sherry wrote: On Sun, 1 Sep 2002, Nigel J. Andrews wrote: When is the beta freeze? Today. Oops, my fault for being imprecise. I was wondering what time of day with timezone. Someone suggested end of today but that means different times to different people. 8:30am ADT on Tuesday morning is when I'm going to freeze everything ... so you effectively have all day on Monday to get it in ... Thanks all that replied. I haven't read the messages between Friday and when I posted yet so I didn't know about the Tuesday morning thing. I was not going to bother having realised the silliness of trying to rush a patch into place but this sounds more realistic. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fulltextindex
On Fri, 30 Aug 2002, Christopher Kings-Lynne wrote:
struct varlena *data;
char*word = john;
char*cur_pos= NULL;
int cur_pos_length = 0;
data = (struct varlena *) palloc(VARHDRSZ + column_length + 1);
word_length = strlen(word);
cur_pos = word[word_length - 2];
while(cur_pos word)
{
cur_pos_length = strlen(cur_pos);
/* Line below causes seg fault on SECOND iteration */
data-vl_len = cur_pos_length + sizeof(int32);
memcpy(VARDATA(data), cur_pos, cur_pos_length);
values[0] = PointerGetDatum(data);
values[1] = 0;
values[2] = oid;
ret = SPI_execp(*(plan-splan), values, NULL, 0);
if(ret != SPI_OK_INSERT)
elog(ERROR, Full Text Indexing: error executing plan in insert\n);
cur_pos--;
}
That would imply the SPI_execp call is trashing the value of data. Have you
confirmed that? (Sometimes it helps to confirm exactly where a pointer is
getting hammered.)
column_length is something sensible like word_length I presume.
That sizeof(int32) should really be VARHDRSZ imo, but I can't see how that's
breaking it.
Disclaimer: I have no idea what I'm doing here.
--
Nigel J. Andrews
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] source code indexer
On Fri, 30 Aug 2002, Laurette Cisneros wrote: HI all, Sorry to interrupt your busy list. I was wondering if you could recomend a good source code db/indexer that could be used to search through the postgresql code? I think I must be one of those 'old school' types. I use find somedir some spec. | xargs grep often followed by tags in Emacs. It isn't perfect but then I'm not either. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] TODO Done. Superuser backend slot reservations
On Mon, 26 Aug 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: + if (!superuser() MyBackendId MaxBackends - ReservedBackends) + elog(ERROR, Normal user limit exceeded); This coding is wrong on its face: the slot number you happen to find has no relationship to the number of slots remaining free, except as an existence proof that the number of slots free was 0 before you took one. Yes. I was taking the line that the last slots in the array are reserved. Those are not going to be taken by non su connections. Therefore, if MyBackendId is under the lower limit it doesn't matter if it's the only slot free since the 'safety' measure has already been used in restricting access to the last free slots and it just so happens that those sessions are still active. I take Neil's point about the order of the tests. That's my stupidity when rearranging stuff after noticing in tests that the user information wasn't available where I was [also stupidly] expecting it to be first time around. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TODO Done. Superuser backend slot reservations
On Mon, 26 Aug 2002, Bruce Momjian wrote: Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: I was taking the line that the last slots in the array are reserved. Those are not going to be taken by non su connections. But that doesn't do the job, does it? My view of the feature is that when there are at least MaxBackends - ReservedBackends slots in use (by either su or non-su connections) then no new non-su jobs should be let in. For example, if the system is full (with a mix of su and non-su jobs) and one non-su job quits, don't we want to hold that slot for a possible su connection? Your approach does have the advantage of being very cheap to test (I think my semantics would require counting the active backends), but I'm not sure that it really does what we want. Tom is right. If the last two slots are held by two long-running super-user backends, and the slots fill, there will be no reserved slots. The trick is that when the maximum number of backends is almost exceeded, only let the supuer-user in. Okay, it's not how I was thinking as you know but I've got nothing against it other than the backend slot scan time. I don't think that would be a significant drain of cpu time so I'll implement that scheme and resubmit. Got some other stuff to do first so it won't be done immediately but will in the next day or so; in time for beta assuming it doesn't fall foul of any patch review interval required. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] A configure.in patch check
Would someone apply the attached patch to the development source and let me know if the autoconf step fails or works. I've only got autoconf 2.13 available and the file needs 2.53 apparently. If it works could I also have a copy of the resulting configure script, or patch, please. For the record, this is related to reserving the last few backend slots for the superuser and I just need to test what I've done. TIA -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] A configure.in patch check (fwd)
Helps if I attach the patch... -- Forwarded message -- Date: Sun, 25 Aug 2002 14:36:19 +0100 (BST) From: Nigel J. Andrews [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: A configure.in patch check Would someone apply the attached patch to the development source and let me know if the autoconf step fails or works. I've only got autoconf 2.13 available and the file needs 2.53 apparently. If it works could I also have a copy of the resulting configure script, or patch, please. For the record, this is related to reserving the last few backend slots for the superuser and I just need to test what I've done. TIA -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants Index: configure.in === RCS file: /projects/cvsroot/pgsql-server/configure.in,v retrieving revision 1.197 diff -c -r1.197 configure.in *** configure.in2002/08/22 22:43:08 1.197 --- configure.in2002/08/25 13:31:31 *** *** 213,218 --- 213,230 # + # Number of connections reserved for superuser (--with-reservedbackends), default 2 + # + AC_MSG_CHECKING([for default superuser reserved number of connections]) + PGAC_ARG_REQ(with, reservedbackends, [ --with-reservedbackends=Nset default +superuser reserved number of connections [2]], + [], + [with_reservedbackends=2]) + AC_MSG_RESULT([$with_reservedbackends]) + AC_DEFINE_UNQUOTED([DEF_RESERVEDBACKENDS], [$with_reservedbackends], +[The default number of concurrent connection slots reserved for +superusers only]) + + + # # Option to disable shared libraries # PGAC_ARG_BOOL(enable, shared, yes, ---(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] A configure.in patch check (fwd)
On Sun, 25 Aug 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: + AC_MSG_CHECKING([for default superuser reserved number of connections]) + PGAC_ARG_REQ(with, reservedbackends, [ --with-reservedbackends=Nset default superuser reserved number of connections [2]], + [], + [with_reservedbackends=2]) This will be rejected anyway; what you want is to set up reserved_backends as a GUC parameter, not as something that has to be hard-wired at configure time. I can't see any reason to make it hard-wired... It is a GUC. It's exactly like max_backends. I took the easy route out and just followed where DEF_MAXBACKENDS was being set rather than hard wiring the value any where. Rather distressingly in order to get this new value into where it's needed I had to hit quite a few files, more than I would have expected. Again I just followed how MaxBackends was being sent to where it was needed but is there any particular reason why storage/ipc/sinvaladt.c:SIBackendInit() can't access MaxBackends and my new ReservedBackends directly? The are global variables afterall, I think #include miscadmin.h would need to be added but is that bad? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] Proposal: make opaque obsolete
On Wed, 21 Aug 2002, Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: ... Then you could actually do something interesting with a function taking anyarraytype. This sounds very cool. I'd vote for that. Um, am I hearing a volunteer to make it happen? I have other problems I need to deal with ... Tom, I saw something in the other thread suggesting that you might be working on this. Is that so? If not I have had a little poke around the cash type but I'm no where near up to speed on the internals. Your proposal is that cstring etc. get entries like record on pg_type? That presumably means we'd need in and out functions defined for these, which in the case of cstring would just be copying the input to output? (As you can see I may not be the best person to work on this if it is to be available for the beta) -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] @(#) Mordred Labs advisory 0x0001: Buffer overflow in
On Mon, 19 Aug 2002, Tom Lane wrote: Justin Clift [EMAIL PROTECTED] writes: From the info still around, this looks to mean that the cash_words() problem was fixed, but the cash_out() problem was harder to fix. Tom/Bruce, is that correct? The cash_out problem can't really be fixed until we do something about subdividing type opaque into multiple pseudo-types with more carefully defined meanings. cash_out is declared cash_out(opaque) which does not really mean that it accepts any input type ... but one of the several meanings of opaque is accepts any type, so the parser doesn't reject cash_out(2). I'd like to see something done about this fairly soon, but it's not happening for 7.3 ... Does anyone have an idea about what other functions are affected by this? As a stop gap measure to remove the *known* DoS issue how about changing the pg_proc entry to restrict input types, i.e. not cash_out(opaque)? cash_words is already listed as only taking the money type is cash_out really that different? On a related topic cash_out() is listed in pg_proc as returning an int4 but doesn't the code clearly show that is incorrect? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] @(#) Mordred Labs advisory 0x0001: Buffer overflow in
On Tue, 20 Aug 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: I'd like to see something done about this fairly soon, but it's not happening for 7.3 ... Does anyone have an idea about what other functions are affected by this? As a first approximation, every output function for a built-in pass-by-reference datatype will show this same behavior. cash_out is just getting picked on because it was the one mentioned in the first complaint. For that matter, every input function for any datatype has the same problem: regression=# select cash_in(2); server closed the connection unexpectedly ... But going back to the idea that it seems that the only problem being publicised in the 'outside world' is the cash_out(2) version can we not do the restriction on acceptable input type in order to claim that the fix? Obviously this is only a marketing ploy but on the basis that a real fix seems unlikely before beta in 11 days time (I'm still trying to work out what Tom's suggestion is) perhaps one worth implementing. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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 7.3 issues
On Fri, 16 Aug 2002, Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: Socket permissions - only install user can access db by default unix_socket_permissions in postgresql.conf This is dead. Removed, still on TODO. Daft question but isn't this an administrator's issue? Forcing a complete override of umask isn't usually considered a decent, friendly thing to do and the sys admin can change the permission bits. Besides, what was the proposed scheme? user/group writable world not I presume. I wouldn't even know how to port that to un unixy systems like Win32. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
On Wed, 14 Aug 2002, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I have no personal preference between period and @ or whatever. See if you can get some other votes for @ because most left @ when the ORDER BY idea came up from Marc. FWIW, I still lean to username@database, so I think we're roughly at a tie. It would be good to get more votes ... Seeing as this is rumbling on I'll throw in my fraction of a vote. I too like the user@database form, partly because it 'reads'. On the other hand I can see the the reasons to like database.user and it does match the style of database.schema.object. Unfortunately for this second form, as '.' is a valid character in a database name then I can see this causing problems, especially with the behind the scenes combination of the two names. I don't see this problem with the '@' form because I can't see that character being used in a 'unqualified' user name. Hmmm...not sure that makes a terribly good arguement for my vote for 'user@db', is there a third choice for us confused folks to go for? A compromise: database@username ? [BTW, I did check and '@' seems to be a valid character in database and user names.] -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] Linux Largefile Support In Postgresql RPMS
On Fri, 9 Aug 2002, Helge Bahmann wrote: As far as I can make out from the libc docs, largefile support is automatic if the macro _GNU_SOURCE is defined and the kernel supports large files. Is that a correct understanding? or do I actually need to do something special to ensure that pg_dump supports large files? in this case you still have to use large file functions in the code explicitly the easiest way to get large file support is to pass -D_FILE_OFFSET_BITS=64 to the preprocessor, and I think I remember doing this once for pg_dump see /usr/include/features.h There is some commentary on this in my /usr/doc/libc6/NOTES.gz, which I presume Oliver has already found since I found it after reading his posting. It gives a bit more detail that the header file for those who want to check this out. I for one was completely unaware of those 64 bit functions. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] Linux Largefile Support In Postgresql RPMS
Note, I'm not sure this belongs in -hackers so I've added -general but left -hackers in so that list can at least see that it's going to -general. On Thu, 8 Aug 2002, mark Kirkwood wrote: Hi all, I just spent some of the morning helping a customer build Pg 7.2.1 from source in order to get Linux largefile support in pg_dump etc. They possibly would have kept using the binary RPMs if they had this feature. This got me to wondering why the Redhat/Mandrake...etc binary RPMS are built without it. Would including default largefile support in Linux RPMs be a good idea ? (I am presuming that such RPMs are built by the Pg community and supplied to the various distros... apologies if I have this all wrong...) I must admit that I am fairly new to PostgreSQL but I have used it and read stuff about it and I'm not sure what you mean. Could you explain what you did? A quick scan of the source shows that there may be an issue in storage/file/buffile.c:BufFileSeek() is that the sort of thing you are talking about? Or maybe I've got it completely wrong and you're talking about adding code to pg_dump although I thought that could already handle large objects. Actually, I'm going to shut up now before I really do show my ignorance and let you answer. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] postgresql performance tuning document ?
Again, I'm not sure this doesn't belong on -general but have left the -hackers in the distribution list so it can be seen there. On Fri, 9 Aug 2002, Oleg Bartunov wrote: I see files truncated at 1Gb on my Linux server: -rw---1 postgres users855490560 Aug 6 20:53 795261707.2 -rw---1 postgres users943259648 Aug 8 23:34 823049708 -rw---1 postgres users1073741824 Aug 6 20:53 795261707.1 -rw---1 postgres users1073741824 Aug 6 20:53 795261707 I'm wondering if postgresql doesn't have LARGE_FILES support ? Those look like PostgreSQL's data files (from the context of the email) is that so? And if so, have you got a requirement for having large data files? It seems an odd requirement especially as I'd say you have approaching 3GB in one table so you're not too far off getting to 4GB and can the host system handle such files? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Trimming the Fat, Part Deux ...
Someone said earlier cvsup would have problems but the anonymous cvs would work fine. Well I've just had a weirdness reconfiguring and rebuilding my few weeks old 7.3dev tree and so deleted it and tried using the anoncvs to get pgsql. Running configure gives me the error: ./configure: ./src/template/linux: No such file or directory and all ./src contains is: total 44 drwxr-xr-x2 software software 4096 Aug 1 23:27 CVS -rw-r--r--1 software software 119 Jul 30 1999 DEVELOPERS -rw-r--r--1 software software 1039 Jul 30 18:47 Makefile -rw-r--r--1 software software13288 Jul 27 21:10 Makefile.global.in -rw-r--r--1 software software10853 Jul 27 21:10 Makefile.shlib drwxr-xr-x 23 software software 4096 Aug 1 23:27 backend -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PERFORM effects FOUND patch (Was: [GENERAL] I must be blind...)
On Fri, 14 Jun 2002, Alvaro Herrera wrote: Tom Lane dijo: Nigel J. Andrews [EMAIL PROTECTED] writes: However, because PERFORM discards the results of a query it is only useful for side effects of the query. Okay. I guess the next question is whether PERFORM *should* be setting FOUND. Seems like it might be a reasonable thing to do. Well, actually FOUND _is_ a side effect of PERFORM, IMHO. I also tried to do the very same thing, and also had to use the dummy variable, which seems like a waste to me. I do not know anything about Oracle's PERFORM, though a quick search on Google shows nothing relevant. I know nothing of Oracle's use of PERFORM either. Indeed I have looked in 4 Oracle books 'Oracle 8i The Complete Reference', 'Oracle8i DBA Bible', 'Oracle PL/SQL Language Pocket Reference' and one on PL/SQL Builtins (on the off chance), and couldn't find any reference to PERFORM. I even scanned, by eye, every page of the PL/SQL reference and saw nothing. On that basis I've included a patch that sets FOUND to true if a PERFORM query 'processes' a row. From looking at other routines in pl_exec.c I believe that I have used the correct test. As FOUND isn't testing as true after a PERFORM at the moment I also presume there is no need for an explicit set to false. I have tested this in 7.3dev with the regression tests and the case that caused me to come across this situation and no errors occured. The patch is at the bottom of this message. I don't know if this should be applied though. There seems to be one vote for it, at least, but there is a question over what other systems do in this situation. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants Index: src/pl/plpgsql/src/pl_exec.c === RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.55 diff -c -r1.55 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c2002/03/25 07:41:10 1.55 --- src/pl/plpgsql/src/pl_exec.c2002/06/15 15:10:38 *** *** 981,989 if (expr-plan == NULL) exec_prepare_plan(estate, expr); ! rc = exec_run_select(estate, expr, 0, NULL); if (rc != SPI_OK_SELECT) elog(ERROR, query \%s\ didn't return data, expr-query); exec_eval_cleanup(estate); } --- 981,992 if (expr-plan == NULL) exec_prepare_plan(estate, expr); ! rc = exec_run_select(estate, expr, 1, NULL); if (rc != SPI_OK_SELECT) elog(ERROR, query \%s\ didn't return data, expr-query); + + if (estate-eval_processed != 0) + exec_set_found(estate, true); exec_eval_cleanup(estate); } ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Redhat 7.3 time manipulation bug
On Fri, 24 May 2002, Peter Eisentraut wrote: Michael Meskes writes: Or does the -34 mean more than just the RedHat version number? The Debian version is correctly named 2.2.5-6 where the -6 means that this is the 6th release of glibc 2.2.5 for Debian, Just for general amusement: I run SuSE's glibc 2.2.5-38 which contains neither the questionable code in the original sources nor is there any reference to it in the patch set. Go figure. You've got to remember that you're talking about systems where, a long time ago now, certain groups felt it necessary to supply nonstandard versions of the core component (the kernel). Sure they helped development of the kernel but only through bastardisation of version numbers where 2.0.1 didn't really mean a Linux 2.0.1 kernel. Is it really surprising the system support stuff has been mangled beyond sense? Anyway, I've composed several and aborted all but this message on this subject and I'm not going to persue it. I have my own views on the right and wrongs off the change in glibc but they wouldn't have advanced anything so I'm keeping quiet on it, still. It seems there is a solution forming. Plus, I'd hate to side with the baddies from the first paragraph :) -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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] 2nd cut at SSL documentation
On Tue, 21 May 2002 14:27:00 -0600 (MDT) Bear Giles [EMAIL PROTECTED] wrote: A second cut at SSL documentation [snip] To set up a SSH/OpenSSH tunnel, a shell account for each user should be set up on the database server. It is acceptable for the shell program to be bogus (e.g., /bin/false), if the tunnel is set up in to avoid launching a remote shell. On each client system the $HOME/.ssh/config file should contain an additional line similiar to LocalForward psql.example.com:5432 I'm coming to this party a bit late in that this is the first I've read the documentation. I'm also a bit of a newbie when it comes to SSH and I've not investigated ssh3 at all yet. However, isn't this assuming ssh1 only? I know ssh2 will fallback to ssh1 compatibility but should there be something about configuring for the later versions? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [INTERFACES] libpgtcl - backend version information patch
[My apolgies if this turns up in the lists twice (now three times) but my mailer claims it's been in the queue for them too long. Not sure why it thinks that since it's only a few minutes since I sent it.] On Fri, 17 May 2002, Peter Eisentraut wrote: Nigel J. Andrews writes: I've attached a patch for libpgtcl which adds access to backend version numbers. This is via a new command: pg_version db channel major varname ?minor varname? ?patch varname? This doesn't truly reflect the way PostgreSQL version numbers are handled. Say for 7.2.1, the major is really 7.2 and the minor is 1. With the interface you proposed, the information major == 7 doesn't really convey any useful information. Ah, oops. I'll change it. I withdraw the patch submission I made yesterday (now two days back). I envisage this patch applied to 7.3 tip and to 7.2 for the 7.2.2 release mentioned a couple of days ago. The only problem with doing this for 7.2 that I can see is where people doing the 'package -exact require Pgtcl 1.x' thing, and how many of those are there? Even PgAccess doesn't use that. Normally we only put bug fixes in minor releases. PgAccess may get an exception, but bumping the version number of a library is stretching it a little. If you're intending to use the function for PgAccess, why not make it internal to PgAccess? That way you can tune the major/minor thing exactly how you need it. It did occur to me this morning that having it applied for 7.2.2 was perhaps silly as it was introducing a new feature and not a bug fix. This feature could be added to PgAccess but I felt it was general enough to be placed in the interface library. I think someone else suggested such a place a couple of weeks ago also. If there is a concensus that this should be done in the application layer I'll happily drop this patch completely. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] *new* libpgtcl - backend version information patch
This is similar to the same patch as I submitted Thursday, and hopefully withdrew in time after a response was made. I have repeated the description with appropiate changes for ease of reference. I've attached a patch for libpgtcl which adds access to backend version numbers. This is via a new command: pg_version db channel major varname ?minor varname? Using readonly variables rather than a command was my first choice but I decided that it was inappropiate for the library to start assigning global variable(s) when that's really the applications job and the command interface is consistent with the rest of the interface. Obviously, backend version numbers are specific to a particular connection. So I've created a new data structure, to keep the information as a distinct unit, and added an instance of the new structure to the Pg_ConnectionId type. The version information is retrieved from the given connection on first use of pg_version and cached in the new data structure for subsequent accesses. In addition to filling the named variables in the callers scope with version numbers/strings the command returns the complete string as returned by version(). It's not possible to turn this return off at the moment but I don't see it as a problem since normal methods of stopping unwanted values returned from procedures can be applied in the application if required. Perhaps the most significant change is that I've increased the package's version number from 1.3 to 1.4. This will adversly effect anyone using an application that requires a specific version of the package where their postgres installation is updated but their application has not been. I can't imagine there are many applications out there using the package management features of TCL though. This isn't a bug fix and is therefore for 7.3 not 7.2.2 -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
