Re: [HACKERS] Memory leaks

2002-10-23 Thread Nigel J. Andrews
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

[HACKERS] 'epoch'::timestamp and Daylight Savings

2002-10-23 Thread Hosen, John
Hi there all PostgreSQL Hackers. We have just upgraded from 7.1.3 to 7.2.3 over the weekend, and have just noticed something weird with regards 'epoch'::timestamp. In 7.1.3, 'epoch'::timestamp always returned the value 1970-01-01 00:00:00+00, following the upgrade we now get:- timestamptz

Re: [HACKERS] [ADMIN] Security question : Database access control

2002-10-23 Thread dima
edit *pg_hba.conf * # Allow any user on the local system to connect to any # database under any username, but only via an IP connection: host all 127.0.0.1 255.255.255.255trust # The same, over Unix-socket connections: local

Re: [HACKERS] Memory leaks

2002-10-23 Thread Greg Copeland
On Tue, 2002-10-22 at 22:28, Tom Lane wrote: Greg Copeland [EMAIL PROTECTED] writes: So again, I'm not really sure it they are meaningful at this point. psql might well have some internal leaks; the backend memory-context design doesn't apply to it. Okay. Thanks. I'll probably take

Re: [HACKERS] Memory leaks

2002-10-23 Thread Tom Lane
Nigel J. Andrews [EMAIL PROTECTED] writes: On Tue, 22 Oct 2002, Tom Lane wrote: 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

Re: [HACKERS] Memory leaks

2002-10-23 Thread Tom Lane
Greg Copeland [EMAIL PROTECTED] writes: Okay. I've started looking at plpython to better understand it's memory needs. I'm seeing a mix of mallocs and PLy_malloc. The PLy version is basically malloc which also checks and reports on memory allocation errors. Anyone know if the cases where

[HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Hans-Jürgen Schönig
First of all PREPARE/EXECUTE is a wonderful thing to speed up things significantly. I wonder if there is a way to store a parsed/rewritten/planned query in a table so that it can be loaded again. This might be useful when it comes to VERY complex queries ( 10 tables). I many applications the

Re: [HACKERS] BTree free pages again

2002-10-23 Thread Manfred Koizar
Alvaro, some time ago I started to collect ideas for btree reorganization but never got beyond brainstorming. Maybe it helps if I add them to your ideas ... On Tue, 22 Oct 2002 00:12:30 -0300, Alvaro Herrera [EMAIL PROTECTED] wrote: I propose instead an hybrid approach: the metapage has an

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 01:02 AM 23/10/2002 -0400, Bruce Momjian wrote: OK, you are saying if we don't have fseeko(), there is no reason to use off_t, and we may as well use long. What limitations does that impose, and are the limitations clear to the user. What I'm saying is that if we

Re: [HACKERS] Memory leaks

2002-10-23 Thread Greg Copeland
On Wed, 2002-10-23 at 08:48, Tom Lane wrote: Greg Copeland [EMAIL PROTECTED] writes: Okay. I've started looking at plpython to better understand it's memory needs. I'm seeing a mix of mallocs and PLy_malloc. The PLy version is basically malloc which also checks and reports on memory

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Greg Copeland
If you were using them that frequently, couldn't you just keep a persistent connection? If it's not used that often, wouldn't the overhead of preparing the query following a new connection become noise? Greg On Wed, 2002-10-23 at 09:24, Hans-Jürgen Schönig wrote: First of all PREPARE/EXECUTE

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 01:02 AM 23/10/2002 -0400, Bruce Momjian wrote: OK, you are saying if we don't have fseeko(), there is no reason to use off_t, and we may as well use long. What limitations does that impose, and are the limitations clear to the user. What I'm saying is that if we

Re: [HACKERS] Memory leaks

2002-10-23 Thread Tom Lane
Greg Copeland [EMAIL PROTECTED] writes: Ya, I'm currently looking to see how the memory is being used and why. I'm trying to better understand it's life cycle. You implying that even the short term memory should be using the palloc stuff? What about long term? Blanket statement that pretty

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Tom Lane
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: I wonder if there is a way to store a parsed/rewritten/planned query in a table so that it can be loaded again. The original version of the PREPARE patch used a shared-across-backends cache for PREPAREd statements. We rejected

[HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5

2002-10-23 Thread Nicolae Mihalache
Hello! I'm tring to move a database and an application from linux to IRIX and I have some problems getting postgresql to work on IRIX. My problem is that postgres crashes is killed by the kernel because of (this is from /var/log/SYSLOG): unix: |$(0x6db)ALERT: Process [postgres] pid 105818

Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5

2002-10-23 Thread Luis Alberto Amigo Navarro
could send the output from: limit and systune | grep shm also, what kind of machine is regards - Original Message - From: Nicolae Mihalache [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 23, 2002 5:57 PM Subject: crashes with postgresql 7.2.1 on

Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5

2002-10-23 Thread Nicolae Mihalache
Luis Alberto Amigo Navarro wrote: could send the output from: limit and systune | grep shm also, what kind of machine is regards robustcassini limit cputime unlimited filesizeunlimited datasize2097152 kbytes stacksize 524288 kbytes coredumpsizeunlimited

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Bruno Wolff III
On Wed, Oct 23, 2002 at 18:04:01 +0200, Hans-Jürgen Schönig [EMAIL PROTECTED] wrote: An example: I have a join across 10 tables + 2 subselects across 4 tables on the machine I use for testing: planner: 12 seconds executor: 1 second The application will stay the same forever. I

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Tom Lane
Bruno Wolff III [EMAIL PROTECTED] writes: Hans-Jürgen Schönig [EMAIL PROTECTED] wrote: I have a join across 10 tables + 2 subselects across 4 tables on the machine I use for testing: planner: 12 seconds executor: 1 second One option you have is to explicitly give the join order. Yes,

Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5

2002-10-23 Thread Luis Alberto Amigo Navarro
System configuration looks fine to me, there is no upper limit, the only configuration problem a can see is that stacksize limit is set to 512Mb when u only have 256Mb ram, try limit stacksize 65536 (64Mb) a most serious size, if it does not help I can't be of any more help. If it helps u may

Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5

2002-10-23 Thread Nicolae Mihalache
Luis Alberto Amigo Navarro wrote: System configuration looks fine to me, there is no upper limit, the only configuration problem a can see is that stacksize limit is set to 512Mb when u only have 256Mb ram, try limit stacksize 65536 (64Mb) a most serious size, if it does not help I can't be of

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Hans-Jürgen Schönig
This is exactly what we do in case of complex stuff. I know that it can help to reduce the problem for the planner. However: If you have explicit joins across 10 tables the SQL statement is not that readable any more and it is still slower than a prepared execution plan. I guess it is worth

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Hans-Jürgen Schönig
The idea is not to have it accross multiple backends and having it in sync with the tables in the database. This is not the point. My problem is that I have seen many performance critical applications sending just a few complex queries to the server. The problem is: If you have many queries

Re: [HACKERS] 'epoch'::timestamp and Daylight Savings

2002-10-23 Thread Hosen, John
Thomas, Thanks for replying so quickly. Upon some further investigation, the conversion of the time to BST is OK on data inserted before the upgrade (the display was just a red herring), the problem is just on new data inserted into the table with a default value for the timestamp fields. The

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Greg Copeland
Could you use some form of connection proxy where the proxy is actually keeping persistent connections but your application is making transient connections to the proxy? I believe this would result in the desired performance boost and behavior. Now, the next obvious question...anyone know of any

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Hans-Jürgen Schönig
Greg Copeland wrote: Could you use some form of connection proxy where the proxy is actually keeping persistent connections but your application is making transient connections to the proxy? I believe this would result in the desired performance boost and behavior. Now, the next obvious

Re: [HACKERS] 'epoch'::timestamp and Daylight Savings

2002-10-23 Thread Tom Lane
Hosen, John [EMAIL PROTECTED] writes: e_app_print_date | timestamp with time zone | default timestamp('epoch'::text) Yeah, there's your problem. You are casting 'epoch' to type timestamp without time zone, and thence to timestamp with time zone. The first step gives midnight and the second

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Zeugswetter Andreas SB SD
The idea is not to have it accross multiple backends and having it in sync with the tables in the database. This is not the point. My problem is that I have seen many performance critical applications sending just a few complex queries to the server. The problem is: If you have many

Re: [HACKERS] PREPARE / EXECUTE

2002-10-23 Thread Neil Conway
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: The standard approach to such a scenario would imho be to write stored procedures for the complex queries (e.g. plpgsql) and use that from the client. Maybe even eliminate a few ping pongs between client and server. Since PL/PgSQL cached

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Peter Eisentraut
Bruce Momjian writes: I think you are right that we have to not use off_t and use long if we can't find a proper 64-bit seek function, but what are the failure modes of doing this? Exactly what happens for larger files? First we need to decide what we want to happen and after that think

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Peter Eisentraut wrote: Bruce Momjian writes: I think you are right that we have to not use off_t and use long if we can't find a proper 64-bit seek function, but what are the failure modes of doing this? Exactly what happens for larger files? First we need to decide what we want to

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes: First we need to decide what we want to happen and after that think about how to implement it. Given sizeof(off_t) sizeof(long) and no fseeko(), we have the following options: It seems obvious to me that there are no platforms that offer

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: How would we disable access to large files? I think configure should fail if it can't find a way to seek. Workaround for anyone in that situation is configure --disable-largefile. regards, tom lane ---(end

Re: [HACKERS] crashes with postgresql 7.2.1 on IRIX 6.5

2002-10-23 Thread mache
Hello again to everybody! I've just downloaded and installed a binary version of postgresql 7.1.1 from ftp.postgresql.org and I have the same bad results: crashes during regression tests. Can please someone confirm me that the binary version posted there is able to pass all the regression

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: First we need to decide what we want to happen and after that think about how to implement it. Given sizeof(off_t) sizeof(long) and no fseeko(), we have the following options: It seems obvious to me that there are no platforms

Re: [HACKERS] One 7.3 item left

2002-10-23 Thread Bruce Momjian
Peter Eisentraut wrote: Bruce Momjian writes: OK, we are down to one open item, related to pg_dumping on 64-bit off_t. We had discussion today on this so it should be completed shortly. I hate to spoil the fun, but we have at least the Linux + Perl 5.8.1 + _GNU_SOURCE and the AIX +

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Giles Lean
Bruce Momjian [EMAIL PROTECTED] writes: OK, well BSD/OS now works, but I wonder if there are any other quad off_t OS's out there without fseeko. NetBSD prior to 1.6, released September 14, 2002. (Source: CVS logs.) OpenBSD prior to 2.7, released June 15, 2000. (Source: release notes.)

[HACKERS] Standalone backend doesn't read postgresql.conf

2002-10-23 Thread Bruce Momjian
Does anyone remember why a standalone backend doesn't read postgresql.conf? For example, I just set 'autocommit' to off in postgresql.conf, and after starting the standalone backend, it doesn't have autocommit off. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 05:50 PM 23/10/2002 -0400, Bruce Momjian wrote: Looking at the pg_dump code, it seems the fseeks are optional in there anyway because it already has code to read the file sequentially rather But there are features that are not available if it can't seek: eg. it will not restore in a

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 05:50 PM 23/10/2002 -0400, Bruce Momjian wrote: Looking at the pg_dump code, it seems the fseeks are optional in there anyway because it already has code to read the file sequentially rather But there are features that are not available if it can't seek: eg. it will

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Giles Lean wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, well BSD/OS now works, but I wonder if there are any other quad off_t OS's out there without fseeko. NetBSD prior to 1.6, released September 14, 2002. (Source: CVS logs.) OK, does pre-1.6 NetBSD have fgetpos/fsetpos that is

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 10:42 AM 23/10/2002 -0400, Bruce Momjian wrote: What I am concerned about are cases that fail at runtime, specifically during a restore of a 2gig file. Please give an example that would still apply assuming we get a working seek/tell pair that works with whatever we use as an offset? If

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 10:42 AM 23/10/2002 -0400, Bruce Momjian wrote: What I am concerned about are cases that fail at runtime, specifically during a restore of a 2gig file. Please give an example that would still apply assuming we get a working seek/tell pair that works with whatever we

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 11:50 PM 23/10/2002 +0200, Peter Eisentraut wrote: 1. Disable access to large files. 2. Seek in some other way. This gets my vote, but I would like to see a clean implementation (not huge quantities if ifdefs every time we call fseek); either we write our own fseek as Bruce seems to be

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 11:50 PM 23/10/2002 +0200, Peter Eisentraut wrote: 1. Disable access to large files. 2. Seek in some other way. This gets my vote, but I would like to see a clean implementation (not huge quantities if ifdefs every time we call fseek); either we write our own

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 09:36 PM 23/10/2002 -0400, Bruce Momjian wrote: We are going to need to either get fseeko workarounds for those, or disable those features in a meaningful way. ? if we have not got a 64 bit seek function of any kind, then use a 32 bit seek - the features don't need to be disabled.

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 09:41 PM 23/10/2002 -0400, Bruce Momjian wrote: If we get this, everything is fine. I have done that for BSD/OS today. I may need to do the same for NetBSD/OpenBSD too. What did you do to achieve this? Philip Warner

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 09:41 PM 23/10/2002 -0400, Bruce Momjian wrote: If we get this, everything is fine. I have done that for BSD/OS today. I may need to do the same for NetBSD/OpenBSD too. What did you do to achieve this? See src/port/fseeko.c in current CVS, with some configure.in

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 09:45 PM 23/10/2002 -0400, Bruce Momjian wrote: We have to write another function because fsetpos doesn't do SEEK_CUR so you have to implement it with more complex code. It isn't a drop in place thing. The only code that uses SEEK_CUR is the code to check if seek is available - I am ver

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 11:55 AM 24/10/2002 +1000, Philip Warner wrote: The only code that uses SEEK_CUR is the code to check if seek is available - I am ver happy to change that to SEEK_SET - I can't even recall why I used SEEK_CUR. The code that does the real seeks uses SEEK_SET. Come to think of it:

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 09:45 PM 23/10/2002 -0400, Bruce Momjian wrote: We have to write another function because fsetpos doesn't do SEEK_CUR so you have to implement it with more complex code. It isn't a drop in place thing. The only code that uses SEEK_CUR is the code to check if seek is

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Well, that certainly changes the functionality of the code. I thought that fseeko test was done so that things that couldn't be seeked on were detected. Not sure what isn't seek-able, maybe named pipes. I thought it was testing that so I didn't touch that variable. This was my original

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 10:08 PM 23/10/2002 -0400, Bruce Momjian wrote: Well, that certainly changes the functionality of the code. I thought that fseeko test was done so that things that couldn't be seeked on were detected. You are quite correct. It should read: #ifdef HAVE_FSEEKO

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Philip Warner
At 10:03 PM 23/10/2002 -0400, Bruce Momjian wrote: It is much cleaner to just duplicate the entire API so you don't have any limitations or failure cases. We may still end up using macros in pg_dump to cope with cases where off_t fseeko are not defined - if there are any. I presume we would

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Philip Warner wrote: At 10:03 PM 23/10/2002 -0400, Bruce Momjian wrote: It is much cleaner to just duplicate the entire API so you don't have any limitations or failure cases. We may still end up using macros in pg_dump to cope with cases where off_t fseeko are not defined - if there are

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Giles Lean
OK, does pre-1.6 NetBSD have fgetpos/fsetpos that is off_t/quad? Yes: int fgetpos(FILE *stream, fpos_t *pos); int fsetpos(FILE *stream, const fpos_t *pos); Per comments in stdio.h fpos_t is the same format as off_t, and off_t and fpos_t have been 64 bit since 1994.

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
Looks like I have some more work to do. Thanks. --- Giles Lean wrote: OK, does pre-1.6 NetBSD have fgetpos/fsetpos that is off_t/quad? Yes: int fgetpos(FILE *stream, fpos_t *pos); int

Re: [HACKERS] One 7.3 item left

2002-10-23 Thread Bruce Momjian
Marc G. Fournier wrote: On Tue, 22 Oct 2002, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian writes: OK, we are down to one open item, related to pg_dumping on 64-bit off_t. We had discussion today on this so it should be completed shortly. I hate to

Re: [HACKERS] pg_dump and large files - is this a problem?

2002-10-23 Thread Bruce Momjian
OK, NetBSD added. Any other OS's need this? Is it safe for me to code something that assumes fpos_t and off_t are identical? I can't think of a good way to test if two data types are identical. I don't think sizeof is enough.

Re: [HACKERS] One 7.3 item left

2002-10-23 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: Marc G. Fournier wrote: Was just about to ask that ... Friday sound reasonable for beta3 then? Bruce, can you have all your files updated by then? Done. Are we going to back-merge CVS tip into the REL7_3_STABLE branch now? What about opening CVS tip

Re: [HACKERS] One 7.3 item left

2002-10-23 Thread Marc G. Fournier
On Wed, 23 Oct 2002, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Marc G. Fournier wrote: Was just about to ask that ... Friday sound reasonable for beta3 then? Bruce, can you have all your files updated by then? Done. Are we going to back-merge CVS tip into the