Re: [HACKERS] Binary Cursors, and the COPY command
Oliver Jowett wrote: This is not true if you happen to be using Java on the client side, which has no idea (unless you grot around in the guts of the JVM) what the native byte order is. The method java.nio.ByteOrder.nativeOrder() will tell you what the native byte order is. This actually means that Java clients have the opposite problem -- it's a lot of work to try to use the 7.3-style binary formats. The commonly used java.io.DataInput will always use network order but it's easy enough to read/write little endian using the java.nio and java.nio.channel packages. Regards, Thomas Hallgren ---(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] Binary Cursors, and the COPY command
Thomas Hallgren wrote: Oliver Jowett wrote: This is not true if you happen to be using Java on the client side, which has no idea (unless you grot around in the guts of the JVM) what the native byte order is. The method java.nio.ByteOrder.nativeOrder() will tell you what the native byte order is. NIO is not present before JDK 1.4. The JDBC driver, at least, needs to support earlier JVMs. This actually means that Java clients have the opposite problem -- it's a lot of work to try to use the 7.3-style binary formats. The commonly used java.io.DataInput will always use network order but it's easy enough to read/write little endian using the java.nio and java.nio.channel packages. That's no use if you don't know the endianness of the data you're receiving (which is what happens under = 7.3 -- the data followed the server's byte ordering) The problem with using native byte orderings is not the byte ordering itself, but that the order is unpredictable -- at best, you have to implement code to handle both orders, and at worst you have to just take a guess and hope you were right.. -O ---(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
[HACKERS] FW: 'vacuum full' memory leak?
Hello, I've got some strange vacuum full behaviour. I've got a table: flows=# explain select * from data; QUERY PLAN --- Seq Scan on data (cost=0.00..5045547.76 rows=236174576 width=55) (1 row) That is sometimes loaded with bunch of data, and sometimes that data gets deleted. As I didn't delete data for a while, I had to reclaim space, that was allocated. So, the normal way to do it would be vacuum full. But it's leaks memory and exits with 'not enough memory'. So... PostgreSQL 7.4.3 FreeBSD 4.7 vacuum_mem = 32768 # min 1024, size in KB shared_buffers = 500# min 16, at least max_connections*2, 8KB each (decreased for this operation) All external queries stopped, so vacuum is the only operation on PG. The initial process starts like: PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 11383 pgsql 29 0 45456K 7860K CPU1 0 1:57 7.91% 7.91% postgres Later, rather fast (each line is taken in approximately two-five minute periods): 11383 pgsql -2 0 140M 130M getblk 1 0:21 8.45% 8.45% postgres 11383 pgsql -2 0 188M 180M getblk 1 0:29 7.57% 7.57% postgres 11383 pgsql -2 0 188M 180M getblk 1 0:29 7.57% 7.57% postgres 11383 pgsql -2 0 252M 240M getblk 0 0:40 8.20% 8.20% postgres 11383 pgsql -2 0 276M 268M RUN1 0:44 10.21% 10.21% postgres 11383 pgsql -2 0 396M 382M getblk 0 1:04 7.47% 7.47% postgres 11383 pgsql -2 0 468M 457M getblk 1 1:17 5.66% 5.66% postgres 11383 pgsql -2 0 516M 345M getblk 0 1:27 6.79% 6.79% postgres (you can see that at the list line the PG was swapped out, though, it didn't do too much of swap ins) And eventualy: INFO: vacuuming public.data ERROR: out of memory DETAIL: Failed on request of size 192. As PG was swapped out rather easily, I can expect there was some kind of memory leak. This table with indexes uses ~27G. After vacuum it should use around 15G. If it goes this way I'll sure do simple copy-out/copy-in (downtime wouldn't cost too much). But the interesting way would be solving this trouble. Cheers, Domas ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] why is postgres-R not in standard dev Path.
Postgres-R is a multi server (write anywhere) replication tool which is possibly important for any enterprise if they want to shift to postgres. Did you guys debate on merging it. As of now They are working on postgres 7.2 and developing postgres-R. They plan to do it for 7.4 as well, why not merge these things. Is the postgres team planing to come up with a similar tool themselves ? -- Stand for something, or you will fall for nothing. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] why is postgres-R not in standard dev Path.
See http://www.slony.org/ It's a master-multislave replication system that has a pretty robust development cycle. It just reached a 1.0 release. Whether any solution becomes a core part of the distribution remains, I think, to be seen. -tfo On Jul 27, 2004, at 4:03 AM, chinni wrote: Postgres-R is a multi server (write anywhere) replication tool which is possibly important for any enterprise if they want to shift to postgres. Did you guys debate on merging it. As of now They are working on postgres 7.2 and developing postgres-R. They plan to do it for 7.4 as well, why not merge these things. Is the postgres team planing to come up with a similar tool themselves ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Binary Cursors, and the COPY command
Oliver Jowett [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] NIO is not present before JDK 1.4. The JDBC driver, at least, needs to support earlier JVMs. Clients only capable of network order (such as a Java 1.3 based JDBC driver) must of course be supported still. No argument there. My objection was to your general statement that Java has no idea what the native byte order is. Another more philosophical question (more suitable on the jdbc list) is when the Java 1.3 support should be limited (or perhaps discontinued altogether) so that further development can exploit everything that 1.4 provides. After all, it's been around for more than 2 years now. AFAIK, the early bugs forcing you to cling on to the 1.3 have been fixed a long time ago. Doesn't the current 3.0 driver make use of features from the Java 1.4 version of java.sql already? The problem with using native byte orderings is not the byte ordering itself, but that the order is unpredictable -- at best, you have to implement code to handle both orders, and at worst you have to just take a guess and hope you were right.. Sure, but those problems are present regardless of implementation language. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Binary Cursors, and the COPY command
Thomas Hallgren wrote: Oliver Jowett [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] NIO is not present before JDK 1.4. The JDBC driver, at least, needs to support earlier JVMs. Clients only capable of network order (such as a Java 1.3 based JDBC driver) must of course be supported still. No argument there. My objection was to your general statement that Java has no idea what the native byte order is. I suppose so. I'd point out that the NIO byteorder info is really just an optimization hint -- the rest of the NIO API is byteorder agnostic, regardless of what the native byteorder is. The problem with using native byte orderings is not the byte ordering itself, but that the order is unpredictable -- at best, you have to implement code to handle both orders, and at worst you have to just take a guess and hope you were right.. Sure, but those problems are present regardless of implementation language. That was my real argument.. -O ---(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] Binary Cursors, and the COPY command
Oliver Jowett wrote: I suppose so. I'd point out that the NIO byteorder info is really just an optimization hint -- the rest of the NIO API is byteorder agnostic, regardless of what the native byteorder is. The rest of the NIO API is not agnostic. A java.nio.ByteBuffer is big endian by default and can be changed using the order method. This is the mechanism you'd use to implement I/O that can handle either endian type. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Binary Cursors, and the COPY command
On Mon, Jul 26, 2004 at 10:06:28PM -0400, [EMAIL PROTECTED] wrote: So what you are saying is that you should inconvenience 90% of your users to make sure they do something right? I would say that was pretty solid reasoning. Exposing 10% of users to a high data corruption risk just to get slightly better CPU performance on I/O bound operations (think Amdahl's Law!) does not sound all that sensible to me. Remember what happened to NT on the Alpha? Device driver writers failed to use the portability macros for byte or 16-bit memory-mapped hardware register accesses, exactly because it also worked without on x86. This was one of the main reasons why they had to add 8-bit and 16-bit loads and stores to the architecture! Similarly, lots of programs harbor annoying, hard-to-find endianness bugs because little-endian byte order happens to hide some typical pointer bugs. When you *really* think about it, by forcing a big endian byte order over machine dependent byte order, you are inconveniencing 90% of the users, but only helping the very small amount of people who run in mixed environments where the server is intel and the client is big endian. I don't see this holding in situations where the user, the programmer, and the systems manager are different (groups of) people. Users may want to connect to different servers. Systems managers may want to replace servers. In today's world even a non-programming user might recompile your application on a big-endian machine. Applications may be running under emulation, and people will prefer to run the database server natively. All those scenarios may break the client-side application. The chance that the mixed-endian scenario had never been tested would be close to those 90%. Few deployments will *ever* really have different endian-ness amongst their servers. 99% of all deployments will be the same or compatible hardware, and probably intel at that. I'd like to add 2 points here: 1. Server architectures and client architectures are two very different things. There are lots of SPARC, PA-RISC, MIPS, POWER, zSeries etc. servers out there. 2. Even if both platforms are AMD or AMD-clones (we'll have to get used to saying that as 64-bit becomes more popular), the number of situations where this is _guaranteed_ as a part of the project definition will be much lower. Remember, the programmer should generally support both the single-endian and the big-endian scenario anyway. We might as well make sure that the hardest is also the most widely tested. Jeroen ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Savepoints inside functions
If we get savepoints working inside functions, would they work if the function is not called from a multi-statement transaction? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] No mail?
I got no mail from any of the PostgreSQL lists this morning. That's not right. Did this mail get through? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] No mail?
all fixed ... had a kernel panic this morning, and fsck took a while to run ... On Wed, 28 Jul 2004, Christopher Kings-Lynne wrote: I got no mail from any of the PostgreSQL lists this morning. That's not right. Did this mail get through? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No mail?
On Wed, Jul 28, 2004 at 10:14:33AM +0800, Christopher Kings-Lynne wrote: I got no mail from any of the PostgreSQL lists this morning. That's not right. Did this mail get through? Apparently everyone is at OSCON or something ... the rest of the people was left out because the NNTP server was down, according to a poster ;-) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) ¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo (Mafalda) ---(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] Savepoints inside functions
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: If we get savepoints working inside functions, would they work if the function is not called from a multi-statement transaction? We'll make sure they do ;-) That will be a neat trick because it will become a multi-statement transaction when it wasn't before. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Savepoints inside functions
Bruce Momjian [EMAIL PROTECTED] writes: If we get savepoints working inside functions, would they work if the function is not called from a multi-statement transaction? We'll make sure they do ;-) That will be a neat trick because it will become a multi-statement transaction when it wasn't before. Not really. At most it might require relaxing a couple of asserts in the state machine in xact.c. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Savepoints inside functions
Bruce Momjian [EMAIL PROTECTED] writes: If we get savepoints working inside functions, would they work if the function is not called from a multi-statement transaction? We'll make sure they do ;-) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] Point in Time Recovery
On 26 Jul, To: [EMAIL PROTECTED] wrote: Sorry I wasn't clearer. I think I have a better idea about what's going on now. With the archiving enabled, it looks like the database is able to complete 1 transaction per database connection, but doesn't complete any subsequent transactions. I'm not sure how to see what's going on. Perhaps I should try a newer snapshot from CVS while I'm at it? I tried to do an strace on the postmaster (and child processes) to see if that might show something, but when the postmaster starts the database isn't accepting any connections. I have the feeling it's not really starting up. Trying to shut it down seems to agrees with that. My wild guess is that the database is sitting waiting for something when a stored function is called but I'm not sure how to verify that. Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] why is postgres-R not in standard dev Path.
chinni [EMAIL PROTECTED] writes: Did you guys debate on merging it. Yes. If it were actually synced with our current CVS and potentially mergeable, the debate might have been longer :-(. But in point of fact, postgres-R has never been less than two releases behind in the past five years, and so there was never a time when merging it was technically feasible. There are also political issues --- merging it into our core would be seen as blessing one particular replication solution to the exclusion of others. And last I checked, there were licensing issues, because postgres-R depends on a group-communication package that doesn't use a BSD-style license. I'd personally be willing to override the political objections if the other two categories of problems were surmounted. But I doubt they ever will be. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Interesting bug in tablespaces
There is a confusing bug in tablespaces. Here is examples: OK, let's create a table with the 3 possible tablespaces of indexes: test=# create table test(a int4) tablespace loc; CREATE TABLE test=# create unique index test_a_idx on test(a); CREATE INDEX test=# create unique index test_a_idx2 on test(a) tablespace loc; CREATE INDEX test=# create unique index test_a_idx3 on test(a) tablespace pg_default; CREATE INDEX test=# \d test Table public.test Column | Type | Modifiers +-+--- a | integer | Indexes: test_a_idx UNIQUE, btree (a) test_a_idx2 UNIQUE, btree (a) test_a_idx3 UNIQUE, btree (a) Tablespace: loc test=# select relname, reltablespace from pg_class where relname like 'test%'; relname | reltablespace -+--- test| 17229 test_a_idx | 17229 test_a_idx2 | 17229 test_a_idx3 | 0 (4 rows) Note that psql (and pg_dump) will (because of pg_get_indexdef()) think that test_a_idx3 is in tablespace 'loc', even though it's in tablespace 'pg_default'. Now, let's make it worse: test=# alter table test set tablespace loc2; ALTER TABLE test=# \d test Table public.test Column | Type | Modifiers +-+--- a | integer | Indexes: test_a_idx UNIQUE, btree (a) TABLESPACE loc test_a_idx2 UNIQUE, btree (a) TABLESPACE loc test_a_idx3 UNIQUE, btree (a) Tablespace: loc2 test=# select relname, reltablespace from pg_class where relname like 'test%'; relname | reltablespace -+--- test| 17279 test_a_idx | 17229 test_a_idx2 | 17229 test_a_idx3 | 0 (4 rows) Now, it thinks test_a_idx3 is in loc2. pg_dump will dump it like that as well, so when it's restored, test_a_idx3 will be recreated in loc2. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Create database and tablespaces
Hi all, We have the following: template1=# create tablespace blah location '/home/swm/tbl/tspc1/'; CREATE TABLESPACE template1=# create table foo (i int) tablespace blah; CREATE TABLE template1=# create database foo tablespace blah; ERROR: could not initialize database directory DETAIL: Directory /home/swm/tbl/data/pg_tblspc/17227/17230 already exists. The comment in dbcommands.c makes it clear that we don't want to allow situations where the template database has entries in the target database's default database but the error message is rather unhelpful. I think that this also needs to be documented. The question is, should we check if the template database has a non empty directory under the target database's default database rather than let it come through to this. Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
