Re: [HACKERS] Further thoughts about warning for costly FK checks
On Thursday 18 March 2004 17:51, Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: > > How would you run pg_dump on a remote machine? > > Trivially. It's a client. Eh? I'm assuming we're talking at cross purposes here. *I* can run it trivially - ssh in and run it over there, or run it on my linux box here and tunnel the connection through. PGadmin etc. can't rely on pg_dump existing (not yet - once the windows port is ready though...) and it can't run it remotely. -- Richard Huxton Archonet Ltd ---(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] COPY formatting
Karel, Andrew, Fernando: > On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: > > Karel Zak <[EMAIL PROTECTED]> writes: > > > The formatting function API can be pretty simple: > > > text *my_copy_format(text *attrdata, int direction, > > > int nattrs, int attr, oid attrtype, oid relation) No offense, but isn't this whole thing more appropriate for a client program? Like the pg_import and pg_export projects on GBorg? Has anyone looked at those projects? I can see making a special provision for CSV in COPY, just because it's such a universal format. But I personally don't see that a complex, sophisticated import/export formatter belongs on the SQL command line. Particularly since most users will want a GUI to handle it. And, BTW, I deal with CSV *all the time* for my insurance clients, and I can tell you that that format hasn't changed in 20 years. We can hard-code it if it's easier. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] fsync method checking
Andrew Dunstan wrote: > > > Bruce Momjian wrote: > > >I have been poking around with our fsync default options to see if I can > >improve them. One issue is that we never default to O_SYNC, but default > >to O_DSYNC if it exists, which seems strange. > > > >What I did was to beef up my test program and get it into CVS for folks > >to run. What I found was that different operating systems have > >different optimal defaults. On BSD/OS and FreeBSD, fdatasync/fsync was > >better, but on Linux, O_DSYNC/O_SYNC was faster. > > > >[snip] > > > >Linux 2.4.9: > > > > > > This is a pretty old kernel (I am writing from a machine running 2.4.22) > > Maybe before we do this for Linux testing on a more modern kernel might > be wise. Sure, I am sure someone will post results. -- 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 8: explain analyze is your friend
[HACKERS] compile warning in CVS HEAD
I get the following warning compiling CVS HEAD: [neilc:/Users/neilc/pgsql]% make -C src/backend/utils/error all [ ... ] gcc -no-cpp-precomp -O0 -Winline -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -I/sw/include -c -o elog.o elog.c -MMD elog.c: In function `log_line_prefix': elog.c:1123: warning: passing arg 1 of `localtime' from incompatible pointer type This is on Mac OSX 10.3 w/ gcc 3.3 -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] compile warning in CVS HEAD
Neil Conway <[EMAIL PROTECTED]> writes: > I get the following warning compiling CVS HEAD: > [neilc:/Users/neilc/pgsql]% make -C src/backend/utils/error all > [ ... ] > gcc -no-cpp-precomp -O0 -Winline -fno-strict-aliasing -g -Wall > -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include > -I/sw/include -c -o elog.o elog.c -MMD > elog.c: In function `log_line_prefix': > elog.c:1123: warning: passing arg 1 of `localtime' from incompatible > pointer type Hm, looks like this code incorrectly assumes that the tv_sec field of struct timeval is necessarily the same datatype as time_t. I'd suggest assigning session_start into a local time_t variable. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] compile warning in CVS HEAD
Tom Lane wrote:
Neil Conway <[EMAIL PROTECTED]> writes:
I get the following warning compiling CVS HEAD:
[neilc:/Users/neilc/pgsql]% make -C src/backend/utils/error all
[ ... ]
gcc -no-cpp-precomp -O0 -Winline -fno-strict-aliasing -g -Wall
-Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-I/sw/include -c -o elog.o elog.c -MMD
elog.c: In function `log_line_prefix':
elog.c:1123: warning: passing arg 1 of `localtime' from incompatible
pointer type
Hm, looks like this code incorrectly assumes that the tv_sec field of
struct timeval is necessarily the same datatype as time_t. I'd suggest
assigning session_start into a local time_t variable.
*sigh*
my local (linux) man for gettimeofday says this:
struct timeval {
time_t tv_sec;/* seconds */
suseconds_ttv_usec; /* microseconds */
};
We could do what you say, or could we just cast it?
cheers
andrew
---(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] compile warning in CVS HEAD
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> *sigh*
> my local (linux) man for gettimeofday says this:
>struct timeval {
>time_t tv_sec;/* seconds */
>suseconds_ttv_usec; /* microseconds */
>};
Yeah, but mine (HPUX) says that tv_sec is "unsigned long". I suspect
that on Darwin the types disagree as to signedness.
> We could do what you say, or could we just cast it?
If they really were different types (as in different widths) then
casting the pointer would be a highly Wrong Thing. I think copying
to a local is safer, even if it does waste a cycle or two.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] UnixWare/CVS Tip/initdb.c needs to use threads flags...
I attempted(!) to compile up CVS Head, and if you --enable-thread-safety, you need to include the THREADS stuff to cc: gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/port' cc -O -Kinline initdb.o -L../../../src/interfaces/libpq -lpq -L../../../src/port -L/usr/local/lib -Wl,-R/usr/local/pgsql/lib -lz -lreadline -ltermcap -lresolv -lgen -lld -lsocket -lnsl -ldl -lm -lpgport -o initdb Undefined first referenced symbol in file pthread_getspecific libpq.so pthread_key_create libpq.so pthread_oncelibpq.so pthread_setspecific libpq.so UX:ld: ERROR: Symbol referencing errors. No output written to initdb gmake[3]: *** [initdb] Error 1 gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/bin/initdb' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/home/ler/pg-dev/pgsql/src/bin' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/home/ler/pg-dev/pgsql/src' gmake: *** [all] Error 2 $ Otherwise we don't pick up the threads library where the pthread_* routines are defined. (I sent the defines we need in src/template/unixware to Bruce already). -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
[HACKERS] Broken code in pquery.c
The if() statement at line 418 in pquery.c seems a bit bereft of
controlled statement; looks like a broken log_executor_stats patch.
if (portal->strategy != PORTAL_MULTI_QUERY)
{
ereport(DEBUG3,
(errmsg_internal("PortalRun")));
/* PORTAL_MULTI_QUERY logs its own stats per query */
if (log_executor_stats)
ResetUsage();
}
--->if (log_executor_stats && portal->strategy != PORTAL_MULTI_QUERY)
/*
* Check for improper portal use, and mark portal active.
*/
if (portal->portalDone)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("portal \"%s\" cannot be run anymore", portal->name)));
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
[HACKERS] Authentication drop-down?
Folks, Jeremy handed me an interesting feature proposal at last night's SFPUG meeting. PG authentication methods ought to have drop-downs to other authentication methods, in the same manner as SSH and PAM. The idea would be this, if you had the following in your pg_hba.conf: somedb jeremy 23.165.22.198 255.255.255.255 kerberos somedb jeremy 23.165.22.198 255.255.255.255 md5 Then, when jeremy tries to connect to somedb from 23.165.22.198, the system would first try kerberos authentication, and if that fails offer an md5 password login. Only when the system ran out of applicable lines in pg_hba.conf would the user be rejected. Any reason why this is a bad idea? It would improve the lives of a lot of kerberos and SSL users who have to deal with flaky authentication issues. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY formatting
And, BTW, I deal with CSV *all the time* for my insurance clients, and I can tell you that that format hasn't changed in 20 years. We can hard-code it if it's easier. Well many of my clients consider CSV "Character Separated Value" not Comma... Thus I get data like this: "Hello","Good Bye" Hello Good Bye Hello,Good Bye "This", "They're" ThisThey're "This""Is" "A" 1 Dealing with all of these different nuances is may or may not be beyond the scope of copy but it seems that it could be something that it can handle. Python has a csv module that allows you to assign dialects to any specific type of import you are performing. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Bug in CVS HEAD on bootparse.y???
gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I../../../src/include -D_GNU_SOURCE -c -o bootparse.o bootparse.c bootparse.y:26:26: access/strat.h: No such file or directory In file included from bootparse.y:340: bootscanner.l:24:26: access/strat.h: No such file or directory make[3]: *** [bootparse.o] Error 1 Am I missing something? I can build all the releases just fine. I've tried it with several ./configure option, the latest being without any ./configure options at all, and it still fails. -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Further thoughts about warning for costly FK checks
Dear Tom, I thought about it... how to solve the contradiction: - backend vs external tool? - new interface? new command? unix? windows? - compatibility with old/existing interfaces? - plugins, any one can contribute? - don't bother DBA's - communicate about it? My 2 pence idea of the day (for free): CREATE SCHEMA pg_advisor; CREATE VIEW pg_advisor.table_without_primary_keys AS SELECT ... FROM pg_catalog...; COMMENT ON VIEW pg_advisor.table_without_primary_keys IS 'hey man, it is considered better to have a primary key on tables...'; CREATE VIEW pg_advisor.costly_ri_checks AS ... CREATE VIEW pg_advisor.summary AS SELECT 'missing primary key declarations', COUNT(*) FROM pg_advisor.table_without_primary_keys UNION SELECT 'costly referencial integrity checks...', COUNT(*) FROM pg_advisor.costly_ri_checks UNION ... ; Then: - it is in the backend, somehow;-) - easy plugin: CREATE VIEW ...;;-) - anyone fluent in SQL and in pg_catalog can contribute! - ALL existing interfaces are already compatible;-) I can use psql;-);-) - no one has to look at the views if he does not want to. - you can communicate about it in the new release... - well, we're in a relationnal database, so let us stay there;-) Good night, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Authentication drop-down?
On Thu, 18 Mar 2004, Josh Berkus wrote: > Jeremy handed me an interesting feature proposal at last night's SFPUG > meeting. > > PG authentication methods ought to have drop-downs to other authentication > methods, in the same manner as SSH and PAM. > > The idea would be this, if you had the following in your pg_hba.conf: > > somedbjeremy 23.165.22.198 255.255.255.255 kerberos > somedbjeremy 23.165.22.198 255.255.255.255 md5 > > Then, when jeremy tries to connect to somedb from 23.165.22.198, the system > would first try kerberos authentication, and if that fails offer an md5 > password login. Only when the system ran out of applicable lines in > pg_hba.conf would the user be rejected. The case I ran into this morning where such an optional behavior would've been handy is when I want to allow the "postgres" OS user to connect as Pg user "postgres" without a password via ident checking, but allow anyone to connect as Pg user "postgres" with a password, e.g.: local all postgres ident sameuser local all all md5 hostall all 0.0.0.0 0.0.0.0 md5 What that makes easy is cron-driven vacuumdb or other maintenance calls (where I can't give a password), or letting the root user su to postgres and connect without needing to know the password, while still allowing others to connect with a password. Is there some other way to do what I'm looking for here without the authentication method fallthrough Josh proposes? Jon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Will auto-cluster be in 7.5?
Is anyone working on these two todo items? # CLUSTER * Automatically maintain clustering on a table * Add way to remove cluster specification on a table ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Will auto-cluster be in 7.5?
Joseph Shraibman wrote: > Is anyone working on these two todo items? > > # CLUSTER > > * Automatically maintain clustering on a table No, and we don't know how to do it. > * Add way to remove cluster specification on a table This patch is done and will be applied soon. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Broken code in pquery.c
Thanks. Fixed. Not sure how it happened.
---
Tom Lane wrote:
> The if() statement at line 418 in pquery.c seems a bit bereft of
> controlled statement; looks like a broken log_executor_stats patch.
>
> if (portal->strategy != PORTAL_MULTI_QUERY)
> {
> ereport(DEBUG3,
> (errmsg_internal("PortalRun")));
> /* PORTAL_MULTI_QUERY logs its own stats per query */
> if (log_executor_stats)
> ResetUsage();
> }
>
> ---> if (log_executor_stats && portal->strategy != PORTAL_MULTI_QUERY)
>
> /*
>* Check for improper portal use, and mark portal active.
>*/
> if (portal->portalDone)
> ereport(ERROR,
> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg("portal \"%s\" cannot be run anymore", portal->name)));
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>
--
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 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] UnixWare/CVS Tip/initdb.c needs to use threads flags...
On Thu, 18 Mar 2004, Larry Rosenman wrote: > I attempted(!) to compile up CVS Head, and if you --enable-thread-safety, > you need to include the THREADS stuff to cc: > > gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/port' > cc -O -Kinline initdb.o -L../../../src/interfaces/libpq -lpq > -L../../../src/port -L/usr/local/lib -Wl,-R/usr/local/pgsql/lib -lz > -lreadline -ltermcap -lresolv -lgen -lld -lsocket -lnsl -ldl -lm -lpgport > -o initdb > Undefined first referenced > symbol in file > pthread_getspecific libpq.so > pthread_key_create libpq.so > pthread_oncelibpq.so > pthread_setspecific libpq.so > UX:ld: ERROR: Symbol referencing errors. No output written to initdb > gmake[3]: *** [initdb] Error 1 I bring this up on PGHACKERS because unixware may not be the only place we have to use the threads flags. What is the concensus of the community? LER ---(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] Bug in CVS HEAD on bootparse.y???
Jonathan Gardner <[EMAIL PROTECTED]> writes: > gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes > -Wmissing-declarations -I. -I../../../src/include -D_GNU_SOURCE -c -o > bootparse.o bootparse.c > bootparse.y:26:26: access/strat.h: No such file or directory > In file included from bootparse.y:340: > bootscanner.l:24:26: access/strat.h: No such file or directory > make[3]: *** [bootparse.o] Error 1 > Am I missing something? access/strat.h has been gone for months. Perhaps you have a clock-skew problem that is preventing bootparse.c from being rebuilt from bootparse.y? Check the file dates. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UnixWare/CVS Tip/initdb.c needs to use threads
--On Thursday, March 18, 2004 19:39:56 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: Larry Rosenman <[EMAIL PROTECTED]> writes: What is the concensus of the community? AFAICS, initdb should not need to depend on libpq in the first place; it never makes a connection to a live postmaster. I think it would be cleaner to get rid of that dependency instead of propagating thread junk into initdb. That's why I asked :) LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] COPY formatting
Tom Lane <[EMAIL PROTECTED]> writes: > I could go with that too. The question here is do we have any popular > use-cases that aren't solved by that extension, but could be solved by > simple user-level data formatting functions? I'm not real eager to add > such a feature as an "if we build it they will come" speculation, but > if anyone can point to solid use-cases besides handling CSV, then it > probably is worth doing. (I can't believe I'm saying this, but) It seems like xml output would be the use case you're looking for. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] UnixWare/CVS Tip/initdb.c needs to use threads flags...
Larry Rosenman <[EMAIL PROTECTED]> writes: > What is the concensus of the community? AFAICS, initdb should not need to depend on libpq in the first place; it never makes a connection to a live postmaster. I think it would be cleaner to get rid of that dependency instead of propagating thread junk into initdb. regards, tom lane ---(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] Bug in CVS HEAD on bootparse.y???
On Thursday 18 March 2004 04:30 pm, Tom Lane wrote: > Jonathan Gardner <[EMAIL PROTECTED]> writes: > > gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes > > -Wmissing-declarations -I. -I../../../src/include -D_GNU_SOURCE -c -o > > bootparse.o bootparse.c > > bootparse.y:26:26: access/strat.h: No such file or directory > > In file included from bootparse.y:340: > > bootscanner.l:24:26: access/strat.h: No such file or directory > > make[3]: *** [bootparse.o] Error 1 > > > > Am I missing something? > > access/strat.h has been gone for months. Perhaps you have a clock-skew > problem that is preventing bootparse.c from being rebuilt from > bootparse.y? Check the file dates. > Yes, that was it. It wasn't updating bootparse.c because bootstrap_tokens.h was new enough. Here's the relevant sections from the Makefile. I doesn't look quite right, but I can't really suggest a way to fix it. $(srcdir)/bootparse.c: $(srcdir)/bootstrap_tokens.h ; $(srcdir)/bootstrap_tokens.h: bootparse.y Makefile ifdef YACC $(YACC) -d $(YFLAGS) $< $(sed-magic) < y.tab.c > $(srcdir)/bootparse.c $(sed-magic) < y.tab.h > $(srcdir)/bootstrap_tokens.h rm -f y.tab.c y.tab.h else @$(missing) bison $< $@ endif -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Further thoughts about warning for costly FK checks
though I'd be worried about the portability price paid to have one. Or are you concerned about whether a GUI could invoke it? I don't see why not --- the GUIs don't reimplement pg_dump, do they? Actually Tom, I think they do (where they have an export facility). How would you run pg_dump on a remote machine? (well, without building an RPC mechanism) In phpPgAdmin 2.x, such a re-implementation did exist. When we did the 3.2 rewrite, I wrote another one just for dumping tables. Then I had the much better idea of just allowing the person to specify the location of pg_dump on their server and now we stream raw pg_dump output back to the client browser. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Will auto-cluster be in 7.5?
# CLUSTER * Automatically maintain clustering on a table * Add way to remove cluster specification on a table I've done the latter - it's been sent to -patches. However, I need someone to look at the shift/reduce problem I'm getting... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Authentication drop-down?
On Thu, Mar 18, 2004 at 22:58:46 +, Jon Jensen <[EMAIL PROTECTED]> wrote: > > Is there some other way to do what I'm looking for here without the > authentication method fallthrough Josh proposes? Assuming people aren't sharing accounts, you could let any authorized postgres user connect using ident authentication as postgres. This should be usable any place you are willing to let the postgres user authenticate using ident. ---(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] Authentication drop-down?
On Thu, 18 Mar 2004, Bruno Wolff III wrote: > On Thu, Mar 18, 2004 at 22:58:46 +, Jon Jensen <[EMAIL PROTECTED]> wrote: > > > > Is there some other way to do what I'm looking for here without the > > authentication method fallthrough Josh proposes? > > Assuming people aren't sharing accounts, you could let any authorized > postgres user connect using ident authentication as postgres. This > should be usable any place you are willing to let the postgres user > authenticate using ident. That's true, but that doesn't satisfy the need. I want an automated process running as OS user "postgres" to authenticate with ident, but I'd also like to be able have, say, phpPgAdmin (running as user "apache") connect as Pg user "postgres" via the UNIX socket using an MD5 password. There's currently no way to do both AFAICT. I can create another superuser with a different name so each user has different pg_hba settings, but that's about it. Jon ---(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] Authentication drop-down?
On Fri, Mar 19, 2004 at 02:01:40 +, Jon Jensen <[EMAIL PROTECTED]> wrote: > > That's true, but that doesn't satisfy the need. I want an automated > process running as OS user "postgres" to authenticate with ident, but I'd > also like to be able have, say, phpPgAdmin (running as user "apache") > connect as Pg user "postgres" via the UNIX socket using an MD5 password. > There's currently no way to do both AFAICT. I can create another superuser > with a different name so each user has different pg_hba settings, but > that's about it. You can treat domain socket and internet socket connections differently. This is still a kludge but might solve your particular problem. ---(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] COPY formatting
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> I could go with that too. The question here is do we have any popular >> use-cases that aren't solved by that extension, but could be solved by >> simple user-level data formatting functions? > (I can't believe I'm saying this, but) It seems like xml output would be the > use case you're looking for. Does that fall into the category of stuff that could be supported by the kind of API we're talking about? I should think that XML would need a much more global view of the data, not just line-by-line reformatting. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Will auto-cluster be in 7.5?
Bruce Momjian wrote: Joseph Shraibman wrote: * Add way to remove cluster specification on a table This patch is done and will be applied soon. I'm a bit confused, why would you want to uncluster a table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UnixWare/CVS Tip/initdb.c needs to use threads flags...
Larry Rosenman wrote: > On Thu, 18 Mar 2004, Larry Rosenman wrote: > > > I attempted(!) to compile up CVS Head, and if you --enable-thread-safety, > > you need to include the THREADS stuff to cc: > > > > gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/port' > > cc -O -Kinline initdb.o -L../../../src/interfaces/libpq -lpq > > -L../../../src/port -L/usr/local/lib -Wl,-R/usr/local/pgsql/lib -lz > > -lreadline -ltermcap -lresolv -lgen -lld -lsocket -lnsl -ldl -lm -lpgport > > -o initdb > > Undefined first referenced > > symbol in file > > pthread_getspecific libpq.so > > pthread_key_create libpq.so > > pthread_oncelibpq.so > > pthread_setspecific libpq.so > > UX:ld: ERROR: Symbol referencing errors. No output written to initdb > > gmake[3]: *** [initdb] Error 1 > I bring this up on PGHACKERS because unixware may not be the only > place we have to use the threads flags. > > What is the concensus of the community? I tried removing the libpq link for initdb and got: (3) gmake gmake -C ../../../src/interfaces/libpq all gmake[1]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/interfaces/libpq' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/interfaces/libpq' gmake -C ../../../src/port all gmake[1]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -O1 -Wall -Wmissing-prototypes -Wmissing-declarations -Wpointer-arith -Wcast-align initdb.o -L../../../src/port -L/usr/local/lib -L/usr/contrib/lib -Wl,-rpath,/usr/local/pgsql/lib -O1 -Wall -Wmissing-prototypes -Wmissing-declarations -Wpointer-arith -Wcast-align -lssl -lcrypto -lz -lreadline -ltermcap -lgetopt -lcompat -lipc -ldl -lm -lutil -lpgport -o initdb initdb.o: In function `get_encoding_id': initdb.o(.text+0x739): undefined reference to `pg_char_to_encoding' initdb.o(.text+0x74b): undefined reference to `pg_valid_server_encoding' initdb.o: In function `trapsig': initdb.o(.text+0x2212): undefined reference to `pqsignal' initdb.o: In function `main': initdb.o(.text+0x2e69): undefined reference to `pqsignal' initdb.o(.text+0x2e7b): undefined reference to `pqsignal' initdb.o(.text+0x2e8a): undefined reference to `pqsignal' initdb.o(.text+0x2e9c): undefined reference to `pqsignal' initdb.o(.text+0x2ea8): more undefined references to `pqsignal' follow gmake: *** [initdb] Error 1 I thought that once you include libpthread in libpq, that you don't have to mention it again then you use libpq. Is your platform different somehow in this regard? I seem to remember this problem with libcrypt and libpq. Is this the same problem? I see that initdb is just the first of many /bin programs to be compiled, so if we have to add the thread lib, we will have to do it for all the bin programs. Yikes. Why wasn't this a problem for 7.4? -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Will auto-cluster be in 7.5?
This patch is done and will be applied soon. I'm a bit confused, why would you want to uncluster a table? You would want to remove the marker that says 'cluster this column in the future'. At the moment, there is no way of removing all markers from a table. 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] UnixWare/CVS Tip/initdb.c needs to use threads
--On Thursday, March 18, 2004 23:03:16 -0500 Bruce Momjian <[EMAIL PROTECTED]> wrote: Larry Rosenman wrote: On Thu, 18 Mar 2004, Larry Rosenman wrote: > I attempted(!) to compile up CVS Head, and if you > --enable-thread-safety, you need to include the THREADS stuff to cc: > > gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/port' > cc -O -Kinline initdb.o -L../../../src/interfaces/libpq -lpq > -L../../../src/port -L/usr/local/lib -Wl,-R/usr/local/pgsql/lib -lz > -lreadline -ltermcap -lresolv -lgen -lld -lsocket -lnsl -ldl -lm > -lpgport -o initdb > Undefined first referenced > symbol in file > pthread_getspecific libpq.so > pthread_key_create libpq.so > pthread_oncelibpq.so > pthread_setspecific libpq.so > UX:ld: ERROR: Symbol referencing errors. No output written to initdb > gmake[3]: *** [initdb] Error 1 I bring this up on PGHACKERS because unixware may not be the only place we have to use the threads flags. What is the concensus of the community? I tried removing the libpq link for initdb and got: (3) gmake gmake -C ../../../src/interfaces/libpq all gmake[1]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/interfaces/libpq' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/interfaces/libpq' gmake -C ../../../src/port all gmake[1]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -O1 -Wall -Wmissing-prototypes -Wmissing-declarations -Wpointer-arith -Wcast-align initdb.o -L../../../src/port -L/usr/local/lib -L/usr/contrib/lib -Wl,-rpath,/usr/local/pgsql/lib -O1 -Wall -Wmissing-prototypes -Wmissing-declarations -Wpointer-arith -Wcast-align -lssl -lcrypto -lz -lreadline -ltermcap -lgetopt -lcompat -lipc -ldl -lm -lutil -lpgport -o initdb initdb.o: In function `get_encoding_id': initdb.o(.text+0x739): undefined reference to `pg_char_to_encoding' initdb.o(.text+0x74b): undefined reference to `pg_valid_server_encoding' initdb.o: In function `trapsig': initdb.o(.text+0x2212): undefined reference to `pqsignal' initdb.o: In function `main': initdb.o(.text+0x2e69): undefined reference to `pqsignal' initdb.o(.text+0x2e7b): undefined reference to `pqsignal' initdb.o(.text+0x2e8a): undefined reference to `pqsignal' initdb.o(.text+0x2e9c): undefined reference to `pqsignal' initdb.o(.text+0x2ea8): more undefined references to `pqsignal' follow gmake: *** [initdb] Error 1 I thought that once you include libpthread in libpq, that you don't have to mention it again then you use libpq. Is your platform different somehow in this regard? I seem to remember this problem with libcrypt and libpq. Is this the same problem? I see that initdb is just the first of many /bin programs to be compiled, so if we have to add the thread lib, we will have to do it for all the bin programs. Yikes. Why wasn't this a problem for 7.4? 7.4 had initdb as a Shell Script. the 7.4.x libpq didn't have any pthread_* references in it, that I see on my box. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Will auto-cluster be in 7.5?
Bruce Momjian wrote: Because a CLUSTER with no argument clusters all previously clustered tables in the db. This turns it off for that table. My bad, I should have read the docs more closely. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UnixWare/CVS Tip/initdb.c needs to use threads
--On Thursday, March 18, 2004 22:47:39 -0600 Larry Rosenman <[EMAIL PROTECTED]> wrote: --On Thursday, March 18, 2004 23:03:16 -0500 Bruce Momjian <[EMAIL PROTECTED]> wrote: I see that initdb is just the first of many /bin programs to be compiled, so if we have to add the thread lib, we will have to do it for all the bin programs. Yikes. Why wasn't this a problem for 7.4? 7.4 had initdb as a Shell Script. the 7.4.x libpq didn't have any pthread_* references in it, that I see on my box. more info: /home/ler/pg-prod/postgresql-7.4.2/src/interfaces/libpq: $ grep pthread_ *.c thread.c: * strerror(). Other operating systems use pthread_setspecific() thread.c: * and pthread_getspecific() internally to allow standard library thread.c: * use pthread_setspecific/pthread_getspecific() also have *_r versions thread.c: * getpwuid() calls pthread_setspecific/pthread_getspecific() to return thread.c: static pthread_mutex_t strerror_lock = PTHREAD_MUTEX_INITIALIZER; thread.c: pthread_mutex_lock(&strerror_lock); thread.c: pthread_mutex_unlock(&strerror_lock); thread.c: static pthread_mutex_t getpwuid_lock = PTHREAD_MUTEX_INITIALIZER; thread.c: pthread_mutex_lock(&getpwuid_lock); thread.c: pthread_mutex_unlock(&getpwuid_lock); thread.c: static pthread_mutex_t gethostbyname_lock = PTHREAD_MUTEX_INITIALIZER; thread.c: pthread_mutex_lock(&gethostbyname_lock); thread.c: pthread_mutex_unlock(&gethostbyname_lock); $ nm libpq.so | grep pthread $ /home/ler/pg-dev/pgsql-server/src/interfaces/libpq: (7.5-dev): $ nm libpq.so | grep pthread_ [271] |0 |0 |NOTY |GLOB |0|UNDEF |pthread_getspecific [370] |0 |0 |NOTY |GLOB |0|UNDEF |pthread_key_create [421] |0 |0 |NOTY |GLOB |0|UNDEF |pthread_once [434] |0 |0 |NOTY |GLOB |0|UNDEF |pthread_setspecific $ grep pthread_ *.c fe-connect.c: static pthread_once_t check_sigpipe_once = PTHREAD_ONCE_INIT; fe-connect.c: pthread_once(&check_sigpipe_once, check_sigpipe_handler); fe-print.c: pthread_setspecific(thread_in_send, "t"); fe-print.c: pthread_setspecific(thread_in_send, "f"); fe-secure.c:pthread_key_t thread_in_send; fe-secure.c:pthread_setspecific(thread_in_send, "t"); fe-secure.c:pthread_setspecific(thread_in_send, "f"); fe-secure.c:pthread_key_create(&thread_in_send, NULL); fe-secure.c:return (pthread_getspecific(thread_in_send) /* has it been set? */ && fe-secure.c:*(char *)pthread_getspecific(thread_in_send) == 't') ? true : false; thread.c: * strerror(). Other operating systems use pthread_setspecific() thread.c: * and pthread_getspecific() internally to allow standard library thread.c: * use pthread_setspecific/pthread_getspecific() also have *_r versions thread.c: * getpwuid() calls pthread_setspecific/pthread_getspecific() to return $ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
[HACKERS] SET WITHOUT CLUSTER patch
Hi,
I have done a patch for turning off clustering on a table entirely.
Unforunately, of the three syntaxes I can think of, all cause
shift/reduce errors:
SET WITHOUT CLUSTER;
DROP CLUSTER
CLUSTER ON NONE;
This is the new grammar that I added:
/* ALTER TABLE SET WITHOUT CLUSTER */
| ALTER TABLE relation_expr SET WITHOUT CLUSTER
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->subtype = 'L';
n->relation = $3;
n->name = NULL;
$$ = (Node *)n;
}
Now, I have to change that relation_expr to qualified_name. However,
this causes shift/reduce errors. (Due to ALTER TABLE relation_expr SET
WITHOUT OIDS.)
Even changing the syntax to "qualified_name DROP CLUSTER" doesn't work
due to the existence of "relation_expr DROP ...".
What's the solution? I can't figure it out...
Chris
Index: doc/src/sgml/ref/alter_table.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.66
diff -c -r1.66 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 9 Mar 2004 16:57:47 - 1.66
--- doc/src/sgml/ref/alter_table.sgml 18 Mar 2004 03:51:41 -
***
*** 47,52
--- 47,54
OWNER TO new_owner
ALTER TABLE name
CLUSTER ON index_name
+ ALTER TABLE name
+ SET WITHOUT CLUSTER
***
*** 219,224
--- 221,235
+
+ SET WITHOUT CLUSTER
+
+
+ This form disables future on a table.
+
+
+
+
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.100
diff -c -r1.100 tablecmds.c
*** src/backend/commands/tablecmds.c13 Mar 2004 22:09:13 - 1.100
--- src/backend/commands/tablecmds.c18 Mar 2004 03:51:42 -
***
*** 3970,3999
rel = heap_open(relOid, AccessExclusiveLock);
- indexOid = get_relname_relid(indexName, rel->rd_rel->relnamespace);
-
- if (!OidIsValid(indexOid))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
-errmsg("index \"%s\" for table \"%s\" does not exist",
- indexName,
NameStr(rel->rd_rel->relname;
-
- indexTuple = SearchSysCache(INDEXRELID,
-
ObjectIdGetDatum(indexOid),
- 0, 0, 0);
- if (!HeapTupleIsValid(indexTuple))
- elog(ERROR, "cache lookup failed for index %u", indexOid);
- indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
-
/*
!* If this is the same index the relation was previously clustered on,
!* no need to do anything.
*/
! if (indexForm->indisclustered)
! {
! ReleaseSysCache(indexTuple);
! heap_close(rel, NoLock);
! return;
}
pg_index = heap_openr(IndexRelationName, RowExclusiveLock);
--- 3970,4010
rel = heap_open(relOid, AccessExclusiveLock);
/*
!* We only fetch the index if indexName is not null. A null index
! * name indicates that we're removing all clustering on this table.
*/
! if (indexName != NULL) {
! indexOid = get_relname_relid(indexName, rel->rd_rel->relnamespace);
!
! if (!OidIsValid(indexOid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
!errmsg("index \"%s\" for table \"%s\" does
not exist",
! indexName,
NameStr(rel->rd_rel->relname;
!
! indexTuple = SearchSysCache(INDEXRELID,
!
ObjectIdGetDatum(indexOid),
! 0, 0, 0);
! if (!HeapTupleIsValid(indexTuple))
! elog(ERROR, "cache lookup failed for index %u", indexOid);
! indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
!
! /*
!* If this is the same index the relation was previously clustered on,
!* no need to do anything.
!*/
! if (indexForm->indisclustered)
! {
! ReleaseSysCache(indexTuple);
! heap_close(rel, NoLock);
! return;
! }
! }
! else {
! /* Set to NULL to prevent compiler warnings */
! indexTuple = NULL;
!
Re: [HACKERS] SET WITHOUT CLUSTER patch
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > This is the new grammar that I added: > | ALTER TABLE relation_expr SET WITHOUT CLUSTER > Now, I have to change that relation_expr to qualified_name. However, > this causes shift/reduce errors. (Due to ALTER TABLE relation_expr SET > WITHOUT OIDS.) Well, seems like what you have to do is leave it as relation_expr as far as bison is concerned, but test in the C-code action and error out if "*" was specified. (Accepting ONLY seems alright to me.) You could possibly find a solution at the grammar level but it'd probably be a much worse kluge ... regards, tom lane ---(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] Authentication drop-down?
Josh Berkus <[EMAIL PROTECTED]> writes: > Any reason why this is a bad idea? It breaks client compatibility --- I don't think any existing clients are prepared to be challenged multiple times, and indeed the protocol spec specifically advises clients to drop the connection if they can't handle the first challenge method. We'd need a protocol extension by which a client could respond "I can't do that Dave". We could put it on the to-do list for the next protocol version bump, perhaps. I'm not in a hurry for one though... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Authentication drop-down?
On Friday 19 March 2004 02:01, Jon Jensen wrote: > On Thu, 18 Mar 2004, Bruno Wolff III wrote: > > On Thu, Mar 18, 2004 at 22:58:46 +, Jon Jensen <[EMAIL PROTECTED]> wrote: > > > Is there some other way to do what I'm looking for here without the > > > authentication method fallthrough Josh proposes? > > > > Assuming people aren't sharing accounts, you could let any authorized > > postgres user connect using ident authentication as postgres. This > > should be usable any place you are willing to let the postgres user > > authenticate using ident. > > That's true, but that doesn't satisfy the need. I want an automated > process running as OS user "postgres" to authenticate with ident, but I'd > also like to be able have, say, phpPgAdmin (running as user "apache") > connect as Pg user "postgres" via the UNIX socket using an MD5 password. > There's currently no way to do both AFAICT. I can create another superuser > with a different name so each user has different pg_hba settings, but > that's about it. How about a .pgpass file for OS-user postgres, and just set all logins for PG-user postgres to use password? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] fsync method checking
I have updated my program with your suggested changes and put in src/tools/fsync. Please see how you like it. --- Zeugswetter Andreas SB SD wrote: > > > 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 tried to modify the program to more closely resemble WAL > writes (all writes to WAL are 8k), the file is usually already open, > and test larger (16k) transactions. > > [EMAIL PROTECTED]:~> test_sync1 > write 0.000625 > write & fsync 0.016748 > write & fdatasync 0.006650 > write, close & fsync 0.017084 > write, close & fdatasync 0.006890 > open o_dsync, write0.015997 > open o_dsync, one write0.007128 > > For the last line xlog.c would need to be modified, but the measurements > seem to imply that it is only worth it on platforms that have O_DSYNC > but not fdatasync. > > Andreas Content-Description: test_sync1.c [ Attachment, skipping... ] -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] fsync method checking
I have been poking around with our fsync default options to see if I can improve them. One issue is that we never default to O_SYNC, but default to O_DSYNC if it exists, which seems strange. What I did was to beef up my test program and get it into CVS for folks to run. What I found was that different operating systems have different optimal defaults. On BSD/OS and FreeBSD, fdatasync/fsync was better, but on Linux, O_DSYNC/O_SYNC was faster. BSD/OS 4.3: Simple write timing: write 0.55 Compare fsync before and after write's close: write, fsync, close0.000707 write, close, fsync0.000808 Compare one o_sync write to two: one 16k o_sync write 0.009762 two 8k o_sync writes 0.008799 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 0.000658 (fdatasync unavailable) write, fsync, 0.000702 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 0.010402 (fdatasync unavailable) write, fsync, 0.001025 This shows terrible O_SYNC performance for 2 8k writes, but is faster for a single 8k write. Strange. FreeBSD 4.9: Simple write timing: write 0.83 Compare fsync before and after write's close: write, fsync, close0.000412 write, close, fsync0.000453 Compare one o_sync write to two: one 16k o_sync write 0.000409 two 8k o_sync writes 0.000993 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 0.000683 (fdatasync unavailable) write, fsync, 0.000405 Compare file sync methods with 2 8k writes: (o_dsync unavailable) open o_sync, write 0.000789 (fdatasync unavailable) write, fsync, 0.000414 This shows fsync to be fastest in both cases. Linux 2.4.9: Simple write timing: write 0.61 Compare fsync before and after write's close: write, fsync, close0.000398 write, close, fsync0.000407 Compare one o_sync write to two: one 16k o_sync write 0.000570 two 8k o_sync writes 0.000340 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 0.000166 write, fdatasync 0.000462 write, fsync, 0.000447 Compare file sync methods with 2 8k writes: (o_dsync unavailable) open o_sync, write 0.000334 write, fdatasync 0.000445 write, fsync, 0.000447 This shows O_SYNC to be fastest, even for 2 8k writes. This unapplied patch: ftp://candle.pha.pa.us/pub/postgresql/mypatches/fsync adds DEFAULT_OPEN_SYNC to the bsdi/freebsd/linux template files, which controls the default for those platforms. Platforms with no template default to fdatasync/fsync. Would other users run src/tools/fsync and report their findings so I can update the template files for their OS's? This is a process similar to our thread testing. Thanks. --- Bruce Momjian wrote: > Mark Kirkwood wrote: > > This is a well-worn thread title - apologies, but these results seemed > > interesting, and hopefully useful in the quest to get better performance > > on Solaris: > > > > I was curious to see if the rather uninspiring pgbench performance > > obtained from a Sun 280R (see General: ATA Disks and RAID controllers > > for database servers) could be improved if more time was spent > > tuning. > > > > With the help of a fellow workmate who is a bit of a Solaris guy, we > > decided to have a go. > > > > The major performance killer appeared to be mounting the filesystem with > > the logging option. The next most significant seemed to be the choice of > > sync_method for Pg - the default (open_datasync), which we initially > > thought should be the best - appears noticeably slower than fdatasync. > > I thought the default was fdatasync, but looking at the code it seems > the default is open_datasync if O_DSYNC is available. > > I assume the logic is that we usually do only one write() before > fsync(), so open_datasync should be faster. Why do we not use O_FSYNC > over f
Re: [HACKERS] fsync method checking
Bruce Momjian <[EMAIL PROTECTED]> writes: > I have been poking around with our fsync default options to see if I can > improve them. One issue is that we never default to O_SYNC, but default > to O_DSYNC if it exists, which seems strange. As I recall, that was based on testing on some different platforms. It's not particularly "strange": O_SYNC implies writing at least two places on the disk (file and inode). O_DSYNC or fdatasync should theoretically be the fastest alternatives, O_SYNC and fsync the worst. > Compare fsync before and after write's close: > write, fsync, close0.000707 > write, close, fsync0.000808 What does that mean? You can't fsync a closed file. > This shows terrible O_SYNC performance for 2 8k writes, but is faster > for a single 8k write. Strange. I'm not sure I believe these numbers at all... my experience is that getting trustworthy disk I/O numbers is *not* easy. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] fsync method checking
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I have been poking around with our fsync default options to see if I can > > improve them. One issue is that we never default to O_SYNC, but default > > to O_DSYNC if it exists, which seems strange. > > As I recall, that was based on testing on some different platforms. > It's not particularly "strange": O_SYNC implies writing at least two > places on the disk (file and inode). O_DSYNC or fdatasync should > theoretically be the fastest alternatives, O_SYNC and fsync the worst. But why perfer O_DSYNC over fdatasync if you don't prefer O_SYNC over fsync? > > > Compare fsync before and after write's close: > > write, fsync, close0.000707 > > write, close, fsync0.000808 > > What does that mean? You can't fsync a closed file. You reopen and fsync. > > This shows terrible O_SYNC performance for 2 8k writes, but is faster > > for a single 8k write. Strange. > > I'm not sure I believe these numbers at all... my experience is that > getting trustworthy disk I/O numbers is *not* easy. These numbers were reproducable on all the platforms I tested. -- 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
Re: [HACKERS] fsync method checking
On Thu, Mar 18, 2004 at 01:50:32PM -0500, Bruce Momjian wrote: > > I'm not sure I believe these numbers at all... my experience is that > > getting trustworthy disk I/O numbers is *not* easy. > > These numbers were reproducable on all the platforms I tested. It's not because they are reproducable that they mean anything in the real world. Kurt ---(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] fsync method checking
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> As I recall, that was based on testing on some different platforms. > But why perfer O_DSYNC over fdatasync if you don't prefer O_SYNC over > fsync? It's what tested out as the best bet. I think we were using pgbench as the test platform, which as you know I have doubts about, but at least it is testing one actual write/sync pattern Postgres can generate. The choice between the open flags and fdatasync/fsync depends a whole lot on your writing patterns (how much data you tend to write between fsync points), so I don't have a lot of faith in randomly-chosen test programs as a guide to what to use for Postgres. >> What does that mean? You can't fsync a closed file. > You reopen and fsync. Um. I just looked at that test program, and I think it needs a whole lot of work yet. * Some of the test cases count open()/close() overhead, some don't. This is bad, especially on platforms like Solaris where open() is notoriously expensive. * You really cannot put any faith in measuring a single write, especially on a machine that's not *completely* idle otherwise. I'd feel somewhat comfortable if you wrote, say, 1000 8K blocks and measured the time for that. (And you have to think about how far apart the fsyncs are in that sequence; you probably want to repeat the measurement with several different fsync spacings.) It would also be a good idea to compare writing 1000 successive blocks with rewriting the same block 1000 times --- if the latter does not happen roughly at the disk RPM rate, then we know the drive is lying and all the numbers should be discarded as meaningless. * The program is claimed to test whether you can write from one process and fsync from another, but it does no such thing AFAICS. BTW, rather than hard-wiring the test file name, why don't you let it be specified on the command line? That would make it lots easier for people to compare the performance of several disk drives, if they have 'em. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] fsync method checking
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> As I recall, that was based on testing on some different platforms. > > > But why perfer O_DSYNC over fdatasync if you don't prefer O_SYNC over > > fsync? > > It's what tested out as the best bet. I think we were using pgbench > as the test platform, which as you know I have doubts about, but at > least it is testing one actual write/sync pattern Postgres can generate. > The choice between the open flags and fdatasync/fsync depends a whole > lot on your writing patterns (how much data you tend to write between > fsync points), so I don't have a lot of faith in randomly-chosen test > programs as a guide to what to use for Postgres. I assume pgbench has so much variance that trying to see fsync changes in there would be hopeless. > >> What does that mean? You can't fsync a closed file. > > > You reopen and fsync. > > Um. I just looked at that test program, and I think it needs a whole > lot of work yet. > > * Some of the test cases count open()/close() overhead, some don't. > This is bad, especially on platforms like Solaris where open() is > notoriously expensive. The only one I saw that had an extra open() was the fsync after close test. I add a do-nothing open/close to the previous test so they are the same. > * You really cannot put any faith in measuring a single write, > especially on a machine that's not *completely* idle otherwise. > I'd feel somewhat comfortable if you wrote, say, 1000 8K blocks and > measured the time for that. (And you have to think about how far OK, it now measures a loop of 1000. > apart the fsyncs are in that sequence; you probably want to repeat the > measurement with several different fsync spacings.) It would also be > a good idea to compare writing 1000 successive blocks with rewriting > the same block 1000 times --- if the latter does not happen roughly > at the disk RPM rate, then we know the drive is lying and all the > numbers should be discarded as meaningless. > > * The program is claimed to test whether you can write from one process > and fsync from another, but it does no such thing AFAICS. It really just shows whether the fsync fater the close has similar timing to the one before the close. That was the best way I could think to test it. > BTW, rather than hard-wiring the test file name, why don't you let it be > specified on the command line? That would make it lots easier for > people to compare the performance of several disk drives, if they have > 'em. I have updated the test program in CVS. New BSD/OS results: Simple write timing: write0.034801 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 0.868831 write, close, fsync 0.717281 Compare one o_sync write to two: one 16k o_sync write10.121422 two 8k o_sync writes 4.405151 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 1.542213 (fdatasync unavailable) write, fsync,1.703689 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 4.498607 (fdatasync unavailable) write, fsync,2.473842 -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] fsync method checking
On Thu, Mar 18, 2004 at 02:22:10PM -0500, Bruce Momjian wrote: > > OK, what better test do you suggest? Right now, there has been no > testing of these. I suggest you start by doing atleast preallocating a 16 MB file and do the tests on that, to atleast be somewhat simular to what WAL does. I have no idea what the access pattern is for normal WAL operations or how many times it gets synched. Does it only do f(data)sync() at commit time, or for every block it writes? I think if you write more data you'll see more differences between O_(D)SYNC and f(data)sync(). I guess it can depend on if you have lots of small transactions, or more big ones. Atleast try to make something that covers different access patterns. Kurt ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] fsync method checking
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> It's what tested out as the best bet. I think we were using pgbench >> as the test platform, which as you know I have doubts about, but at >> least it is testing one actual write/sync pattern Postgres can generate. > I assume pgbench has so much variance that trying to see fsync changes > in there would be hopeless. The results were fairly reproducible, as I recall; else we'd have looked for another test method. You may want to go back and consult the pghackers archives. >> * Some of the test cases count open()/close() overhead, some don't. > The only one I saw that had an extra open() was the fsync after close > test. I add a do-nothing open/close to the previous test so they are > the same. Why is it sensible to include open/close overhead in the "simple write" case and not in the "o_sync write" cases, for instance? Doesn't seem like a fair comparison to me. Adding the open overhead to all cases might make it "fair", but it would also make it not what we want to measure. >> * The program is claimed to test whether you can write from one process >> and fsync from another, but it does no such thing AFAICS. > It really just shows whether the fsync fater the close has similar > timing to the one before the close. That was the best way I could think > to test it. Sure, but where's the "separate process" part? What this seems to test is whether a single process can sync its own writes through a different file descriptor; which is interesting but by no means the only thing we need to be sure of if we want to make the bgwriter handle syncing. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] fsync method checking
Kurt Roeckx wrote: > On Thu, Mar 18, 2004 at 02:22:10PM -0500, Bruce Momjian wrote: > > > > OK, what better test do you suggest? Right now, there has been no > > testing of these. > > I suggest you start by doing atleast preallocating a 16 MB file > and do the tests on that, to atleast be somewhat simular to what > WAL does. > > I have no idea what the access pattern is for normal WAL > operations or how many times it gets synched. Does it only do > f(data)sync() at commit time, or for every block it writes? > > I think if you write more data you'll see more differences > between O_(D)SYNC and f(data)sync(). > > I guess it can depend on if you have lots of small transactions, > or more big ones. > > Atleast try to make something that covers different access > patterns. OK, I preallocated 16mb. New results: Simple write timing: write0.037900 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 0.692942 write, close, fsync 0.762524 Compare one o_sync write to two: one 16k o_sync write 8.494621 two 8k o_sync writes 4.177680 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 1.836835 (fdatasync unavailable) write, fsync,1.780872 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 4.255614 (fdatasync unavailable) write, fsync,2.120843 -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] fsync method checking
Kurt Roeckx <[EMAIL PROTECTED]> writes: > I have no idea what the access pattern is for normal WAL > operations or how many times it gets synched. Does it only do > f(data)sync() at commit time, or for every block it writes? If we are using fsync/fdatasync, we issue those at commit time or when completing a WAL segment. If we are using the open flags, then of course there's no separate sync call. My previous point about checking different fsync spacings corresponds to different assumptions about average transaction size. I think a useful tool for determining wal_sync_method has got to be able to reflect that range of possibilities. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] fsync method checking
Here are my results on Linux 2.6.1 using cvs version 1.7. Those times with > 20 seconds, you really hear the disk go crazy. And I have the feeling something must be wrong. Those results are reproducible. Kurt Simple write timing: write0.139558 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 8.249364 write, close, fsync 8.356813 Compare one o_sync write to two: one 16k o_sync write28.487650 two 8k o_sync writes 2.310304 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 1.010688 write, fdatasync25.109604 write, fsync, 26.051218 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 2.212223 write, fdatasync27.439907 write, fsync, 27.772294 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] fsync method checking
Kurt Roeckx wrote: > Here are my results on Linux 2.6.1 using cvs version 1.7. > > Those times with > 20 seconds, you really hear the disk go crazy. > > And I have the feeling something must be wrong. Those results > are reproducible. > Wow, your O_SYNC times are great. Where can I buy some? :-) Anyway, we do need to find a way to test this because obviously there is huge platform variability. --- > > Kurt > > > Simple write timing: > write0.139558 > > Compare fsync times on write() and non-write() descriptor: > (If the times are similar, fsync() can sync data written > on a different descriptor.) > write, fsync, close 8.249364 > write, close, fsync 8.356813 > > Compare one o_sync write to two: > one 16k o_sync write28.487650 > two 8k o_sync writes 2.310304 > > Compare file sync methods with one 8k write: > (o_dsync unavailable) > open o_sync, write 1.010688 > write, fdatasync25.109604 > write, fsync, 26.051218 > > Compare file sync methods with 2 8k writes: > (The fastest should be used for wal_sync_method) > (o_dsync unavailable) > open o_sync, write 2.212223 > write, fdatasync27.439907 > write, fsync, 27.772294 > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [HACKERS] fsync method checking
Tom, Bruce, > My previous point about checking different fsync spacings corresponds to > different assumptions about average transaction size. I think a useful > tool for determining wal_sync_method has got to be able to reflect that > range of possibilities. Questions: 1) This is an OSS project. Why not just recruit a bunch of people on PERFORMANCE and GENERAL to test the 4 different synch methods using real databases? No test like reality, I say 2) Won't Jan's work on 7.5 memory and I/O management mean that we have to re-evaluate synching anyway? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [HACKERS] fsync method checking
Josh Berkus <[EMAIL PROTECTED]> writes: > 1) This is an OSS project. Why not just recruit a bunch of people on > PERFORMANCE and GENERAL to test the 4 different synch methods using real > databases? No test like reality, I say I agree --- that is likely to yield *far* more useful results than any standalone test program, for the purpose of finding out what wal_sync_method to use in real databases. However, there's a second issue here: we would like to move sync/checkpoint responsibility into the bgwriter, and that requires knowing whether it's valid to let one process fsync on behalf of writes that were done by other processes. That's got nothing to do with WAL sync performance. I think that it would be sensible to make a test program that focuses on this one specific question. (There has been some handwaving to the effect that everybody knows this is safe on Unixen, but I question whether the handwavers have seen the internals of HPUX or AIX for instance; and besides we need to worry about Windows now.) A third reason for having a simple test program is to confirm whether your drives are syncing at all (cf. hdparm discussion). > 2) Won't Jan's work on 7.5 memory and I/O management mean that we have to > re-evaluate synching anyway? So far nothing's been done that touches WAL writing. However, I am thinking about making the bgwriter process take some of the load of writing WAL buffers (right now it only writes data-file buffers). And you're right, after that happens we will need to re-measure. The open flags will probably become considerably more attractive than they are now, if the bgwriter handles most non-commit writes of WAL. (We might also think of letting the bgwriter use a different sync method than the backends do.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [HACKERS] fsync method checking
Bruce Momjian <[EMAIL PROTECTED]> writes: > Well, I wrote the program to allow testing. I don't see a complex test > as being that much better than simple one. We don't need accurate > numbers. We just need to know if fsync or O_SYNC is faster. Faster than what? The thing everyone is trying to point out here is that it depends on context, and we have little faith that this test program creates a context similar to a live Postgres database. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] fsync method checking
On Thu, Mar 18, 2004 at 03:34:21PM -0500, Bruce Momjian wrote: > Kurt Roeckx wrote: > > Here are my results on Linux 2.6.1 using cvs version 1.7. > > > > Those times with > 20 seconds, you really hear the disk go crazy. > > > > And I have the feeling something must be wrong. Those results > > are reproducible. > > > > Wow, your O_SYNC times are great. Where can I buy some? :-) > > Anyway, we do need to find a way to test this because obviously there is > huge platform variability. New results with version 1.8: Simple write timing: write0.150613 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 9.170472 write, close, fsync 8.851715 Compare one o_sync write to two: one 16k o_sync write 2.617860 two 8k o_sync writes 2.563437 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 1.031721 write, fdatasync25.599010 write, fsync, 26.192824 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 2.268718 write, fdatasync27.029396 write, fsync, 27.399243 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [HACKERS] fsync method checking
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Well, I wrote the program to allow testing. I don't see a complex test > > as being that much better than simple one. We don't need accurate > > numbers. We just need to know if fsync or O_SYNC is faster. > > Faster than what? The thing everyone is trying to point out here is > that it depends on context, and we have little faith that this test > program creates a context similar to a live Postgres database. Note, too, that the preferred method isn't likely to depend just on the operating system, it's likely to depend also on the filesystem type being used. Linux provides quite a few of them: ext2, ext3, jfs, xfs, and reiserfs, and that's just off the top of my head. I imagine the performance of the various syncing methods will vary significantly between them. It seems reasonable to me that decisions such as which sync method to use should initially be made at installation time: have the test program run on the target filesystem as part of the installation process, and build the initial postgresql.conf based on the results. You might even be able to do some additional testing such as measuring the difference between random block access and sequential access, and again feed the results into the postgresql.conf file. This is no substitute for experience with the platform, but I expect it's likely to get you closer to something optimal than doing nothing. The only question, of course, is whether or not it's worth going to the effort when it may or may not gain you a whole lot. Answering that is going to require some experimentation with such an automatic configuration system. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] fsync method checking
Tom Lane wrote: > > It really just shows whether the fsync fater the close has similar > > timing to the one before the close. That was the best way I could think > > to test it. > > Sure, but where's the "separate process" part? What this seems to test > is whether a single process can sync its own writes through a different > file descriptor; which is interesting but by no means the only thing we > need to be sure of if we want to make the bgwriter handle syncing. I am not sure how to easily test if a separate process can do the same. I am sure it can be done, but for me it was enough to see that it works in a single process. Unix isn't very process-centered for I/O, so I don't think it would make much of a difference. Now, Win32, that might be an issue. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] COPY formatting
On Thu, Mar 18, 2004 at 07:48:40AM +0100, Karel Zak wrote: > On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: > > Karel Zak <[EMAIL PROTECTED]> writes: > > > The formatting function API can be pretty simple: > > > text *my_copy_format(text *attrdata, int direction, > > > int nattrs, int attr, oid attrtype, oid relation) > > > > This seems like it could only reasonably be implemented as a C function. > > Why? I said it's pseudo code. It should use standard fmgr API like > every other PostgreSQL function or is it problem and I overlook > something? It must to support arbitrary programming language and not > C only. Well, I look over the COPY code and best will start with hardcoded version, but make it modular in code and if all will right we can think about some interface for others formats definition. OK? It's pity that main idea of current COPY is based on separated lines and it is not more common interface for streaming data between FE and BE. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] COPY formatting
To be honest this idea strikes me as overkill - over
engineering. While there is a clear need for proper CSV import
(i.e. just setting DELIMITER to ',' doesn't work due to ','s in
strings) I cannot see how this would prove useful, or who would use
it?
While i have done a lot of messing around reading/writing the binary
format (and been stung by changes in that format) if you are using
this format then you're 99% likely to be in control of the
incoming/outgoing data and thus able to format to your wishes outwith
COPY.
Something else in the TODO regarding COPY is XML import/export, and
for this to be supported in your proposed implementation the function
would need to be passed in a heap more information.
L.
Karel Zak writes:
>
> Hi,
>
> in TODO is item: "* Allow dump/load of CSV format". I don't think
> it's clean idea. Why CSV and why not something other? :-)
>
> A why not allow to users full control of the format by they own
> function. It means something like:
>
> COPY tablename [ ( column [, ...] ) ]
> TO { 'filename' | STDOUT }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ NULL [ AS ] 'null string' ]
> [ FORMAT funcname ] ]
>
>
> The formatting function API can be pretty simple:
>
> text *my_copy_format(text *attrdata, int direction,
> int nattrs, int attr, oid attrtype, oid relation)
>
> -- it's pseudocode of course, it should be use standard fmgr
> interface.
>
> It's probably interesting for non-binary COPY version.
>
> Comments?
>
> Karel
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Further thoughts about warning for costly FK checks
Dear Tom, On Wed, 17 Mar 2004, Tom Lane wrote: > If you want a GUI, it could be a GUI, I do not want a GUI, I'm not a GUI guy;-) I was just wondering how GUI could be adapted to deal with the tool if it is outside. > though I'd be worried about the portability price paid to have one. Or > are you concerned about whether a GUI could invoke it? I don't see why > not --- the GUIs don't reimplement pg_dump, do they? Yes, but pg_dump is more like a blackbox, the interface does not need to look at the generated output and interpret it, or in a very simple way to check whether it failed. > > Or separate only mean that it is a "separate" function of the backend that > > can be triggered by calling existing functions such as "EXPLAIN" or > > "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever. > > That still leaves us in the situation where only people who are capable > of doing backend programming can help. I hope that a standalone program > would be more understandable and could attract developers who wouldn't > touch the backend. Mmm. The tool would need support functions that should already exist in the backend, so they will be re-developed or somehow replicated. Moreover I'm among the ones asking for advices, and I'm not that afraid of the backend, as maybe I should be;-) Also, I would like to get the advices simply from psql, thus an added command (ADVISE) or even ANALYZE would be just fine. > Also, you'd still have to invent an interface for it --- and the > interface would be constrained by the limits of the FE/BE protocol. > It would have to look like a SQL command that returns a query result, > or possibly NOTICE messages, both of which are pretty confining. I think that such tool would generate "WARNING, NOTICE", HINT, CONTEXT just as the be does at the time, so I don't think that it is that confining. Also, some new fields could be added to improve reports, if they are really necessary, but I'm not even that sure that any is needed. Well, anyway if there is some place to put advices, that would be a good think, even if I'm not convinced about the design;-) Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] COPY formatting
On Thu, Mar 18, 2004 at 09:29:03AM +, Lee Kindness wrote: > To be honest this idea strikes me as overkill - over > engineering. It was suggestion, maybe you're right :-) > While i have done a lot of messing around reading/writing the binary > format (and been stung by changes in that format) if you are using > this format then you're 99% likely to be in control of the > incoming/outgoing data and thus able to format to your wishes outwith > COPY. I partly agree. But.. there is possible write directly final file by backend without data transfer to client. If we want to support this feature we need control output format by server... And.. I can image format that is use for BE/FE data transfer only and not for some final data presentation. For example compression of data stream from/to BE without PostgreSQL protocol change. > Something else in the TODO regarding COPY is XML import/export, and > for this to be supported in your proposed implementation the function > would need to be passed in a heap more information. Yes, very probably some struct with all COPY information and format specific stuff. Tom was right that in this case it will C functions only. As I said I will try implement it without user defined function call for format conversion, but I will do it modular and in future we can create some interface for user defined formats. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Problem on cluster initialization
On Wednesday 17 March 2004 21:23, Tom Lane wrote: > That is evidently a 7.1 database, not a 7.2 database. I'm surprised > that you don't get the other version check message first --- we must > have gotten the order of testing a mite confused ... anyway you need a > 7.1 server. Thank you for answering me I tried 7.1.3 but it gives to me the same message!! Any suggestion? P.S. However... it's strange but /var/lib/pgsql/data/PG_VERSION answers: 7.2 !!! Thank you, Silvio Mazzaro ---(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] COPY formatting
Lee Kindness wrote: To be honest this idea strikes me as overkill - over engineering. While there is a clear need for proper CSV import (i.e. just setting DELIMITER to ',' doesn't work due to ','s in strings) I cannot see how this would prove useful, or who would use it? I agree. My modest proposal for handling CSVs would be to extend the DELIMITER parameter to allow up to 3 characters - separator, quote and escape. Escape would default to the quote char and the quote char would default to unspecified. This would involve no grammar changes and fairly isolated and small code changes, I believe. In the most common CSV cases you would just use $$,"$$ or $$,'$$. :-) COPY is basically line/tuple oriented, and that alone would exclude many file formats (e.g. imagine wanting to import a spreadsheet where each worksheet was the table name and the first row on each worksheet was the field names - I have seen such beasts more than once). If we want a general facility for loading and exporting foreign file formats, I really believe that is the province of a utility program rather than a database engine function. The reason in my mind for making CSV a special case is that it is very easy to do and so often asked for. (I used to set parsing CSVs as a basic programming exercise - it is amazing how many way people find to get it wrong). cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] COPY formatting
Andrew Dunstan wrote: > Lee Kindness wrote: > > >To be honest this idea strikes me as overkill - over > >engineering. While there is a clear need for proper CSV import > >(i.e. just setting DELIMITER to ',' doesn't work due to ','s in > >strings) I cannot see how this would prove useful, or who would use > >it? > > > > > > > I agree. My modest proposal for handling CSVs would be to extend the > DELIMITER parameter to allow up to 3 characters - separator, quote and > escape. Escape would default to the quote char and the quote char would > default to unspecified. This would involve no grammar changes and fairly > isolated and small code changes, I believe. In the most common CSV cases > you would just use $$,"$$ or $$,'$$. :-) > > COPY is basically line/tuple oriented, and that alone would exclude many > file formats (e.g. imagine wanting to import a spreadsheet where each > worksheet was the table name and the first row on each worksheet was the > field names - I have seen such beasts more than once). If we want a > general facility for loading and exporting foreign file formats, I > really believe that is the province of a utility program rather than a > database engine function. > > The reason in my mind for making CSV a special case is that it is very > easy to do and so often asked for. > > (I used to set parsing CSVs as a basic programming exercise - it is > amazing how many way people find to get it wrong). I like the separator, quote, and escape idea. It allows variety without requiring folks to code in C. -- 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 8: explain analyze is your friend
Re: [HACKERS] COPY formatting
Karel Zak <[EMAIL PROTECTED]> writes: >> On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: >>> Karel Zak <[EMAIL PROTECTED]> writes: This seems like it could only reasonably be implemented as a C function. >> >> Why? I said it's pseudo code. It should use standard fmgr API like >> every other PostgreSQL function or is it problem and I overlook >> something? It must to support arbitrary programming language and not >> C only. Sure, but the question is whether the *stuff it has to do* can reasonably be coded in anything but C. Why are you passing in a relation OID, if not for lookups in relcache entries that are simply not accessible above the C level? (Don't tell me you want the function to do a bunch of actual SELECTs from system catalogs for every line of the copy...) Passing in a relation OID is probably a bad idea anyway, as it ties this API to the assumption that COPY is only for complete relations. There's been talk before of allowing a SELECT result to be presented via the COPY protocol, for instance. What might be a more usable API is COPY OUT: function formatter_out(text[]) returns text COPY IN: function formatter_in(text) returns text[] where the text array is either the results of or the input to the per-column datatype I/O routines. This makes it explicit that the formatter's job is solely to determine the column-level wrapping and unwrapping of the data. I'm assuming here that there is no good reason for the formatter to care about the specific datatypes involved; can you give a counterexample? > It's pity that main idea of current COPY is based on separated lines > and it is not more common interface for streaming data between FE and BE. Yeah, that was another concern I had. This API would let the formatter control line-level layout but it would not eliminate the hard-wired significance of newline. What's worse, there isn't any clean way to deal with reading quoted newlines --- the formatter can't really replace the default quoting rules if the low-level code is going to decide whether a newline is quoted or not. We could possibly solve that by specifying that the text output or input (respectively) is the complete line sent to or from the client, including newline or whatever other line-level formatting you are using. This still leaves the problem of how the low-level COPY IN code knows what is a complete line to pass off to the formatter_in routine. We could possibly fix this by adding a second input-control routine function formatter_linelength(text) returns integer which is defined to return -1 if the input isn't a complete line yet (i.e., read some more data, append to the buffer, and try again), or >= 0 to indicate that the first N bytes of the buffer represent a complete line to be passed off to formatter_in. I don't see a way to combine formatter_in and formatter_linelength into a single function without relying on "out" parameters, which would again confine the feature to format functions written in C. It's a tad annoying that we need two functions for input. One way that we could still keep the COPY option syntax to be just FORMAT csv is to create an arbitrary difference in the signatures of the input functions. Then we could have coexisting functions csv(text[]) returns text csv(text) returns text[] csv(text, ...) returns int that are referenced by "FORMAT csv". regards, tom lane ---(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] Problem on cluster initialization
Silvio Mazzaro <[EMAIL PROTECTED]> writes: > However... it's strange but /var/lib/pgsql/data/PG_VERSION answers: > 7.2 > !!! Yeah? Well, that explains something I was wondering about, which is why the PG_VERSION mismatch complaint didn't come out first. Where exactly did you get the server code you were running in this database? Is it possible that it was an early-7.2-development snapshot? AFAICS the only code that would have used catversion 200101061 with PG_VERSION 7.2 is 7.2 devel versions dated before 2001-05-05, which was the next catversion change. Anyway, the bottom line is you will need to resurrect the exact same server code you were using before in order to read this database. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY formatting
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Lee Kindness wrote: >> To be honest this idea strikes me as overkill - over >> engineering. >> > I agree. My modest proposal for handling CSVs would be to extend the > DELIMITER parameter to allow up to 3 characters - separator, quote and > escape. Escape would default to the quote char and the quote char would > default to unspecified. I could go with that too. The question here is do we have any popular use-cases that aren't solved by that extension, but could be solved by simple user-level data formatting functions? I'm not real eager to add such a feature as an "if we build it they will come" speculation, but if anyone can point to solid use-cases besides handling CSV, then it probably is worth doing. regards, tom lane ---(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] Further thoughts about warning for costly FK checks
On Thursday 18 March 2004 10:18, Fabien COELHO wrote:
> On Wed, 17 Mar 2004, Tom Lane wrote:
> > though I'd be worried about the portability price paid to have one. Or
> > are you concerned about whether a GUI could invoke it? I don't see why
> > not --- the GUIs don't reimplement pg_dump, do they?
Actually Tom, I think they do (where they have an export facility). How would
you run pg_dump on a remote machine? (well, without building an RPC
mechanism)
> Yes, but pg_dump is more like a blackbox, the interface does not need
> to look at the generated output and interpret it, or in a very simple
> way to check whether it failed.
>
> > > Or separate only mean that it is a "separate" function of the backend
> > > that can be triggered by calling existing functions such as "EXPLAIN"
> > > or "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever.
> >
> > That still leaves us in the situation where only people who are capable
> > of doing backend programming can help. I hope that a standalone program
> > would be more understandable and could attract developers who wouldn't
> > touch the backend.
Well - let's look at what info we might need for the ultimate pg_advisor:
1. schema details
2. stats info
3. query stats (correlated with existing stats, so we know what is causing
table-scans)
4. query plans
That's the same information as I need to make decisions, so it must be
sufficient. We already have 1+2, in the system tables/information_schema and
stats schema. The others could be generated if required.
We want two main features:
1. Add new tests without writing C
2. Allow a number of clients (pg_advisor/psql?/pgadmin/phppgadmin/RHdbadmin
etc) to run the tests/process results.
So - have something like:
Core => test domains => tests
Core is responsible for running the right tests (based on user parameters)
Test domains provide one or more views/tablefuncs that individual tests use to
query against.
Tests consist of a query, an assertion, message and keywords to match against
Results are returned as SQL - client formats it how they like.
Anyone can add tests by inserting rows into pg_advisor_tests (or whatever).
Most test-domains can be built using raw SQL/plpgsql (don't want to say all -
haven't thought it through yet).
So - a simple test might be defined as:
INSERT INTO pg_advisor_tests
(pat_id, pat_title,
pat_description,
pat_query,
pat_msg)
VALUES ('NAMES0001','Mixed-case column names',
'You appear to be using mixed-case column-names. See ADVISOR-HINT #32 for why
you need to be careful',
'SELECT schema_name,table_name,column_name FROM colname_test_domain_view
WHERE lower(column_name) <> column_name'
'Mixed-case column: %.%.%'
);
> I think that such tool would generate "WARNING, NOTICE", HINT, CONTEXT
> just as the be does at the time, so I don't think that it is that
> confining. Also, some new fields could be added to improve reports,
> if they are really necessary, but I'm not even that sure that any is
> needed.
Different levels of message sounds sensible to me, though I'm not sure what to
call them.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] COPY formatting
Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > Lee Kindness wrote: > >> To be honest this idea strikes me as overkill - over > >> engineering. > >> > > I agree. My modest proposal for handling CSVs would be to extend the > > DELIMITER parameter to allow up to 3 characters - separator, quote and > > escape. Escape would default to the quote char and the quote char would > > default to unspecified. > > I could go with that too. The question here is do we have any popular > use-cases that aren't solved by that extension, but could be solved by > simple user-level data formatting functions? I'm not real eager to add > such a feature as an "if we build it they will come" speculation, but > if anyone can point to solid use-cases besides handling CSV, then it > probably is worth doing. The thing I liked about Andrew's idea is that it even covers escape quoting for CVS, which might change from implementation to implentation, and it is flexible without requiring C coding. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Further thoughts about warning for costly FK checks
On Thu, 18 Mar 2004, Richard Huxton wrote: > On Thursday 18 March 2004 10:18, Fabien COELHO wrote: > > On Wed, 17 Mar 2004, Tom Lane wrote: > > > > though I'd be worried about the portability price paid to have one. Or > > > are you concerned about whether a GUI could invoke it? I don't see why > > > not --- the GUIs don't reimplement pg_dump, do they? > > Actually Tom, I think they do (where they have an export facility). How would > you run pg_dump on a remote machine? (well, without building an RPC > mechanism) Can't the GUI just do "pg_dump -h remotehost ..."? Or do you mean something else? I think the phpPgAdmin developers recommend using pg_dump exactly because they don't want to reinvent that formidable wheel. Jon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Further thoughts about warning for costly FK checks
Richard Huxton <[EMAIL PROTECTED]> writes: > How would you run pg_dump on a remote machine? Trivially. It's a client. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] fsync method checking
Bruce Momjian wrote: I have been poking around with our fsync default options to see if I can improve them. One issue is that we never default to O_SYNC, but default to O_DSYNC if it exists, which seems strange. What I did was to beef up my test program and get it into CVS for folks to run. What I found was that different operating systems have different optimal defaults. On BSD/OS and FreeBSD, fdatasync/fsync was better, but on Linux, O_DSYNC/O_SYNC was faster. [snip] Linux 2.4.9: This is a pretty old kernel (I am writing from a machine running 2.4.22) Maybe before we do this for Linux testing on a more modern kernel might be wise. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
