Re: [HACKERS] pljava revisited

2003-12-12 Thread Thomas Hallgren
The sqlstandards.org is still down I think. Is this something new in the upcoming 200x spec? I could not see it mentioned in the SQL-99. I'm a great fan of standards. If there is one I'll make my pljava adhere to it. Any information on this topic is greatly appreciated. Thanks, - thomas Peter

Re: [HACKERS] fsync method checking

2003-12-12 Thread Zeugswetter Andreas SB SD
Running the attached test program shows on BSD/OS 4.3: write 0.000360 write fsync 0.001391 I think the write fsync pays for the previous write test (same filename). write, close fsync 0.001308 open o_fsync, write0.000924 I have

Re: [HACKERS] Use of 'cp -r' in CREATE DATABASE

2003-12-12 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: but my BSD/OS manual only documents 'cp -R' and mentions: I think we should switch to -R in our code. And break the code on who knows how many other systems? No thanks. If we want to do anything at all with this code, we should eliminate the use of

Re: [HACKERS] Use of 'cp -r' in CREATE DATABASE

2003-12-12 Thread Andrew Dunstan
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: but my BSD/OS manual only documents 'cp -R' and mentions: I think we should switch to -R in our code. And break the code on who knows how many other systems? No thanks. If we want to do anything at all with this code, we should

Re: [HACKERS] Use of 'cp -r' in CREATE DATABASE

2003-12-12 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes: IIRC we don't copy anything but plain files and directories - no special files, symlinks or fifos, so the -R/-r differences shouldn't affect us anyway, should they? Also, that should make the implementation of an internal recursive copy much simpler

[HACKERS] WITH clause

2003-12-12 Thread David Fetter
Kind people, I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details TBD. WITH res = match (x.foo, '([0-9]+)x([0-9]+)') SELECT * FROM x WHERE y = res[2] ORy

Re: [HACKERS] WITH clause

2003-12-12 Thread Peter Eisentraut
David Fetter wrote: I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details TBD. WITH res = match (x.foo, '([0-9]+)x([0-9]+)') SELECT * FROM x WHERE y =

Re: [HACKERS] WITH clause

2003-12-12 Thread Tom Lane
David Fetter [EMAIL PROTECTED] writes: I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details TBD. WITH res = match (x.foo, '([0-9]+)x([0-9]+)') SELECT *

Re: [HACKERS] WITH clause

2003-12-12 Thread David Fetter
On Fri, Dec 12, 2003 at 07:47:26PM +0100, Peter Eisentraut wrote: David Fetter wrote: I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details TBD. WITH

[HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Tom Lane
I am planning to solve the ancient problem of updating to a new major version without dump/reload, by means of writing a new, more bulletproof implementation of Bruce's old pg_upgrade script. Here are some design notes --- please comment. The upgrade scenario I envision

Re: [HACKERS] WITH clause

2003-12-12 Thread Alvaro Herrera Munoz
On Fri, Dec 12, 2003 at 10:13:56AM -0800, David Fetter wrote: I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details TBD. I think you could be rather looking

Re: [HACKERS] WITH clause

2003-12-12 Thread David Fetter
In article [EMAIL PROTECTED] you wrote: On Fri, Dec 12, 2003 at 10:13:56AM -0800, David Fetter wrote: I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Peter Eisentraut
Tom Lane wrote: I think it's important to be able to run pg_upgrade with the postmaster shut down. Otherwise there is too much risk that some other user will change the database while we are working. The original pg_upgrade script left it to the DBA to ensure this wouldn't happen, but that

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Andrew Dunstan
re Windows: pipes, yes, hard links, no (and no sane symlinks either) - also of course no (sane) shell - is this going to be a script or a C program? Maybe use an option which you would disable on Windows to copy the files instead of hardlinking them. Yes it would take lots more time and space,

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes: re Windows: pipes, yes, hard links, no (and no sane symlinks either) - also of course no (sane) shell - is this going to be a script or a C program? C, certainly. Maybe use an option which you would disable on Windows to copy the files instead of

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Matthew T. O'Connor
On Fri, 2003-12-12 at 14:51, Andrew Dunstan wrote: re Windows: pipes, yes, hard links, no (and no sane symlinks either) Actually, NTFS does support hard links, there is just no support for it in any MS file management GUI.

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: Instead, all operations should be done through a standalone backend. This would also be a nice solution for people who want a standalone, server-less database system. But for the purpose of pg_upgrade it seems like a lot of work

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Marc G. Fournier
On Fri, 12 Dec 2003, Peter Eisentraut wrote: Tom Lane wrote: I think it's important to be able to run pg_upgrade with the postmaster shut down. Otherwise there is too much risk that some other user will change the database while we are working. The original pg_upgrade script left it to

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Tom Lane
Marc G. Fournier [EMAIL PROTECTED] writes: Then again, in the case of pg_upgrade, wouldn't just disabling access from anywhere except localhost prevent others from getting in? Not if your normal operating mode includes connections from clients running locally. I really don't see any clean way

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Andrew Dunstan
Matthew T. O'Connor wrote: On Fri, 2003-12-12 at 14:51, Andrew Dunstan wrote: re Windows: pipes, yes, hard links, no (and no sane symlinks either) Actually, NTFS does support hard links, there is just no support for it in any MS file management GUI.

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Tom Lane
Matthew T. O'Connor [EMAIL PROTECTED] writes: On Fri, 2003-12-12 at 14:51, Andrew Dunstan wrote: Maybe use an option which you would disable on Windows to copy the files instead of hardlinking them. I think this would be a good feature even without hard link problems. If I am a paranoid

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Matthew T. O'Connor
On Fri, 2003-12-12 at 15:42, Tom Lane wrote: Alternative thought: just recommend that if possible, people take a filesystem dump of their old PGDATA directory after stopping the old postmaster. This would be sufficient for retreating to the prior version if needed. It might or might not be

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Thomas Swan
Matthew T. O'Connor wrote: On Fri, 2003-12-12 at 15:42, Tom Lane wrote: Alternative thought: just recommend that if possible, people take a filesystem dump of their old PGDATA directory after stopping the old postmaster. This would be sufficient for retreating to the prior version if needed.

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Dave Smith
Why not go the other way. 1) Dump the schemas. 2) Initdb with the new schemas in a tmp PGDATA 3) backup the schemas in the current PGDATA 4) move the new schemas from the new db into the current one. This means that doing an update you would only have to have space for the system catalogs not

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Tom Lane
Dave Smith [EMAIL PROTECTED] writes: Why not go the other way. 1) Dump the schemas. 2) Initdb with the new schemas in a tmp PGDATA 3) backup the schemas in the current PGDATA 4) move the new schemas from the new db into the current one. This seems like approximately the same thing except

[HACKERS] more dbt-2 results hyperthreading on linux-2.6.0-test11

2003-12-12 Thread markw
Hi Nick, Here are the results of the comparisons I said I would do. no-hyperthreading: http://developer.osdl.org/markw/dbt2-pgsql/282/ - metric 2288.43 - baseline hyperthreading: http://developer.osdl.org/markw/dbt2-pgsql/278/ - metric 1944.42 -

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Marc G. Fournier
On Fri, 12 Dec 2003, Tom Lane wrote: Alternative thought: just recommend that if possible, people take a filesystem dump of their old PGDATA directory after stopping the old postmaster. This would be sufficient for retreating to the prior version if needed. It might or might not be slower

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Thomas Swan
Tom Lane wrote: Dave Smith [EMAIL PROTECTED] writes: Why not go the other way. 1) Dump the schemas. 2) Initdb with the new schemas in a tmp PGDATA 3) backup the schemas in the current PGDATA 4) move the new schemas from the new db into the current one. This seems like approximately

Re: [HACKERS] Resurrecting pg_upgrade

2003-12-12 Thread Matthew T. O'Connor
On Fri, 2003-12-12 at 14:00, Tom Lane wrote: Currently the no-table-contents-changes restriction keeps us from upgrading from versions older than 7.4 anyway (since type NUMERIC had its on-disk representation changed in 7.4). We could possibly upgrade 7.3 databases that contain no NUMERIC

Re: [HACKERS] more dbt-2 results hyperthreading on linux-2.6.0-test11

2003-12-12 Thread markw
Hi Jun, DBT-2 is a fair use implementation of the TPC-C (OLTP), if you're familiar with that. I have 14 drives attached through 1 megaraid raid controller, and 52 drives connected through 4 channels on 2 mylex raid controllers, all in a raid-0 configuration. I am using LVM2 on both sets of

[HACKERS] ORDER BY and DISTINCT ON

2003-12-12 Thread Neil Conway
We reject the following query: nconway=# create table abc (a int, b int, c int); CREATE TABLE nconway=# select distinct on (a) a, b, c from abc order by b, c, a; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions This works fine, of course: nconway=# select distinct

[HACKERS] Walker/mutator prototype.

2003-12-12 Thread Kurt Roeckx
I'm trying to change all the walkers and mutators to have a more strict prototype. I had to do this with lots of casts. I don't really like the idea of having all those generic pointer types (Node * and void *), but currently see no better way to deal with it. I attached the patch. Kurt

Re: [HACKERS] fsync method checking

2003-12-12 Thread Manfred Spraul
Bruce Momjian wrote: write 0.000360 write fsync 0.001391 write, close fsync 0.001308 open o_fsync, write0.000924 That's 1 milliseconds vs. 1.3 milliseconds. Neither value is realistic - I guess the hw cache on and the os doesn't issue cache flush

Re: [HACKERS] fsync method checking

2003-12-12 Thread Tom Lane
Manfred Spraul [EMAIL PROTECTED] writes: One advantage of a seperate write and fsync call is better performance for the writes that are triggered within AdvanceXLInsertBuffer: I'm not sure how often that's necessary, but it's a write while holding both the WALWriteLock and WALInsertLock. If

Re: [HACKERS] 7.4 build problem on Linux Vserver

2003-12-12 Thread Roderick A. Anderson
On Thu, 11 Dec 2003, Dave Page wrote: Dave Page [EMAIL PROTECTED] writes: gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations prod -I../../src/include -D_GNU_SOURCE -I/usr/include -c -o path.o path.c gcc: cannot specify -o with -c or -S and multiple

Re: [HACKERS] WITH clause

2003-12-12 Thread Christopher Browne
[EMAIL PROTECTED] (David Fetter) wrote: I'm looking to the SQL WITH clause as a way to get better regex support in PostgreSQL. I've been chatting a little bit about this, and here's an idea for a behavior. Implementation details TBD. WITH res = match (x.foo, '([0-9]+)x([0-9]+)') SELECT *