[HACKERS] pg_dump / Unique constraints
I've been examining the pg_dump source and output, and I've come to the conclusion that I can modify it so that UNIQUE constraints appear as part of the CREATE TABLE statement, rather than as a separate CREATE INDEX. I know it is possible because phpPgAdmin does it! This change should also be in line with what we have been discussing earlier, and could be a precursor to getting FOREIGN KEY constraints appearing as part of CREATE TABLE as well... Is there any problem with me working on this? Chris -- Christopher Kings-Lynne Family Health Network (ACN 089 639 243)
RE: [HACKERS] Crash during WAL recovery?
> I think it's certain that the original poster didn't realize Vadim is not > a native English speaker, which is why I made my comment (to clue him in). > Vadim didn't take my comment as criticism, as his follow-on post > made clear > (he got the joke). I don't know from your post if you thought I > was adding > to the criticism or not, but I can say with certainty I wasn't. In my > previous life as the founder of a company specializing in optimizing > compilers for minicomputers, I employed Dutch (who spoke and wrote English > than I or anyone here), Polish, Vietmanese and other nationals who were > excellent hackers and who all spoke better English than I spoke their > language - or cooked their cuisine or even followed their table customs, > for that matter. Just for the record, I apologise for criticising Valim's grammar. I didn't realise that he was a non-native speaker - nor that it was even his code. I just thought I should point out that spelling error (propably) given that there was a thread going on about spelling in some error messages... Chris
Re: [HACKERS] query plan optimizer bug
"xuyifeng" <[EMAIL PROTECTED]> writes: > stock# create table a(i int2, j int); > stock# create unique index idx_a on a(i, j); > stock# explain select * from a where i=1 and j=0; > psql:test.sql:4: NOTICE: QUERY PLAN: > Seq Scan on a (cost=0.00..25.00 rows=1 width=6) The constant "1" is implicitly type int4, and our planner isn't presently very smart about optimizing cross-data-type comparisons into indexscans. You could make it work with something like select * from a where i = 1::int2 and j = 0; or just bite the bullet and declare column i as int4 (== "int"). Making i int2 isn't saving any storage space in the above example anyhow, because of alignment restrictions. To be smarter about this, the system needs to recognize that "1" could be typed as int2 instead of int4 in this case --- but not "0", else that part of the index wouldn't apply. That opens up a whole raft of numeric type hierarchy issues, which you can find discussed at length in the pghackers archives. We do intend to fix this, but doing it without breaking other useful cases is trickier than you might think... regards, tom lane
Re: [HACKERS] Crash during WAL recovery?
> I don't know from your post if you thought I was adding > to the criticism or not, but I can say with certainty I wasn't. No, I saw that you understood perfectly, I just wanted to add another two cents... > I'm not denigrating the current efforts, because PG documention's pretty > good all things considered. But some volunteers devoted to improving > the docs could accomplish a lot. Yup. Anyone out there with the time and interest? regards, tom lane
Re: [HACKERS] query plan optimizer bug
I did VACUUM ANALYZE, there is no effect. XuYifeng - Original Message - From: Don Baccus <[EMAIL PROTECTED]> To: xuyifeng <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 22, 2000 10:51 AM Subject: Re: [HACKERS] query plan optimizer bug > At 10:46 AM 11/22/00 +0800, xuyifeng wrote: > >Hi, > > > >it's obviously there is a query plan optimizer bug, if int2 type used in > fields, > >the plan generator just use sequence scan, it's stupid > > Have you checked this with real data after doing a VACUUM ANALYZE? > > > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net. >
Re: [HACKERS] Crash during WAL recovery?
At 12:29 AM 11/22/00 -0500, Tom Lane wrote: >>> Is there any particular reason the spelling and punctuation in the code >>> snippet below is so bad? > >> Vadim's Russian. This impacts his english but not his ability to implement >> complex features like MVCC and WAL :) > >As someone who can't speak anything but English worth a damn (even >though I was raised in Spanish-speaking countries, so you'd think >I'd have acquired at least one clue), I have long since learned not >to criticize the English of non-native speakers. I think it's certain that the original poster didn't realize Vadim is not a native English speaker, which is why I made my comment (to clue him in). Vadim didn't take my comment as criticism, as his follow-on post made clear (he got the joke). I don't know from your post if you thought I was adding to the criticism or not, but I can say with certainty I wasn't. In my previous life as the founder of a company specializing in optimizing compilers for minicomputers, I employed Dutch (who spoke and wrote English than I or anyone here), Polish, Vietmanese and other nationals who were excellent hackers and who all spoke better English than I spoke their language - or cooked their cuisine or even followed their table customs, for that matter. >More generally, a lot of the PG documentation could use the attention >of a professional copy editor --- and I'm sad to say that the parts >contributed by native English speakers aren't necessarily any cleaner >than the parts contributed by those who are not. If you have the >time and energy to submit corrections, please fall to! This is very much true. PG needs some good documentation volunteers. I'm not denigrating the current efforts, because PG documention's pretty good all things considered. But some volunteers devoted to improving the docs could accomplish a lot. - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Crash during WAL recovery?
> As someone who can't speak anything but English worth a damn (even > though I was raised in Spanish-speaking countries, so you'd think > I'd have acquired at least one clue), I have long since learned not > to criticize the English of non-native speakers. Many of the > participants in this project are doing far better than I would if > the tables were turned. So, I fix grammatical and spelling errors > if I have another reason to be editing some piece of documentation, > but I never hold it against the original author. > > More generally, a lot of the PG documentation could use the attention > of a professional copy editor --- and I'm sad to say that the parts > contributed by native English speakers aren't necessarily any cleaner > than the parts contributed by those who are not. If you have the > time and energy to submit corrections, please fall to! I did have AW's copyeditor go through the refence manual. Would be nice if they had done the other manuals too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Crash during WAL recovery?
>> Is there any particular reason the spelling and punctuation in the code >> snippet below is so bad? > Vadim's Russian. This impacts his english but not his ability to implement > complex features like MVCC and WAL :) As someone who can't speak anything but English worth a damn (even though I was raised in Spanish-speaking countries, so you'd think I'd have acquired at least one clue), I have long since learned not to criticize the English of non-native speakers. Many of the participants in this project are doing far better than I would if the tables were turned. So, I fix grammatical and spelling errors if I have another reason to be editing some piece of documentation, but I never hold it against the original author. More generally, a lot of the PG documentation could use the attention of a professional copy editor --- and I'm sad to say that the parts contributed by native English speakers aren't necessarily any cleaner than the parts contributed by those who are not. If you have the time and energy to submit corrections, please fall to! regards, tom lane
Re: [HACKERS] query plan optimizer bug
At 10:46 AM 11/22/00 +0800, xuyifeng wrote: >Hi, > >it's obviously there is a query plan optimizer bug, if int2 type used in fields, >the plan generator just use sequence scan, it's stupid Have you checked this with real data after doing a VACUUM ANALYZE? - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] query plan optimizer bug
Hi, it's obviously there is a query plan optimizer bug, if int2 type used in fields, the plan generator just use sequence scan, it's stupid, i am using PG7.03, this is my log file: - stock# drop table a; DROP stock# create table a(i int2, j int); CREATE stock# create unique index idx_a on a(i, j); CREATE stock# explain select * from a where i=1 and j=0; psql:test.sql:4: NOTICE: QUERY PLAN: Seq Scan on a (cost=0.00..25.00 rows=1 width=6) EXPLAIN stock# drop table a; create table a(i int, j int); CREATE stock# create unique index idx_a on a(i, j); CREATE stock# explain select * from a where i=1 and j=0; psql:test.sql:8: NOTICE: QUERY PLAN: Index Scan using idx_a on a (cost=0.00..2.02 rows=1 width=8) EXPLAIN ---
Re: [HACKERS] Crash during WAL recovery?
Just speaking Russian and English both (to any degree) is absolutely amazing, put that on top of MVCC and WAL and we have Vadim, the smartest person alive! *grin* -Mitch - Original Message - From: "Mikheev, Vadim" <[EMAIL PROTECTED]> To: "'Don Baccus'" <[EMAIL PROTECTED]>; "Christopher Kings-Lynne" <[EMAIL PROTECTED]>; "PostgreSQL Development" <[EMAIL PROTECTED]> Sent: Tuesday, November 21, 2000 5:37 PM Subject: RE: [HACKERS] Crash during WAL recovery? > > >Is there any particular reason the spelling and punctuation > > in the code > > >snippet below is so bad? > > > > Vadim's Russian. This impacts his english but not his > > ability to implement complex features like MVCC and WAL :) > > Yes, sorry guys. C lang is much easier -:)) > > Vadim >
RE: [HACKERS] Crash during WAL recovery?
> >Is there any particular reason the spelling and punctuation > in the code > >snippet below is so bad? > > Vadim's Russian. This impacts his english but not his > ability to implement complex features like MVCC and WAL :) Yes, sorry guys. C lang is much easier -:)) Vadim
Re: [HACKERS] Assert Failure with current CVS
FWIW, I can freely reproduce this from a database dump and a short script which defines indexes and does a vacuum. They total about 600k, if anyone wants me to send them...
At 22:38 20/11/00 -0500, Tom Lane wrote:
>Philip Warner <[EMAIL PROTECTED]> writes:
>> TRAP: Failed Assertion("!(((file) > 0 && (file) < (int) SizeVfdCache &&
>> VfdCache[file].fileName != ((void *)0))):", File: "fd.c", Line: 967)
>> !(((file) > 0 && (file) < (int) SizeVfdCache && VfdCache[file].fileName !=
>> ((void *)0))) (0)
>> Server process (pid 7187) exited with status 6 at Tue Nov 21 13:44:27 2000
>
>There should be a core file from this --- backtrace please?
>
#0 0x400da0d1 in __kill () at soinit.c:27
#1 0x400d9eff in raise (sig=6) at ../sysdeps/posix/raise.c:27
#2 0x400db19b in abort () at ../sysdeps/generic/abort.c:83
#3 0x8155068 in ExcAbort () at excabort.c:27
#4 0x8154fc7 in ExcUnCaught (excP=0x81c3d58, detail=0, data=0x0,
message=0x81a3fa0 "!(((file) > 0 && (file) < (int) SizeVfdCache && VfdCache[file].fileName != ((void *)0)))") at exc.c:178
#5 0x815501a in ExcRaise (excP=0x81c3d58, detail=0, data=0x0,
message=0x81a3fa0 "!(((file) > 0 && (file) < (int) SizeVfdCache && VfdCache[file].fileName != ((void *)0)))") at exc.c:195
#6 0x81540ef in ExceptionalCondition (
conditionName=0x81a3fa0 "!(((file) > 0 && (file) < (int) SizeVfdCache && VfdCache[file].fileName != ((void *)0)))",
exceptionP=0x81c3d58, detail=0x0, fileName=0x81a3e87 "fd.c", lineNumber=967) at assert.c:73
#7 0x810afeb in FileSync (file=31) at fd.c:967
#8 0x81136e0 in mdcommit () at md.c:818
#9 0x8114510 in smgrcommit () at smgr.c:519
#10 0x8107fed in BufmgrCommit () at xlog_bufmgr.c:1071
#11 0x808c7f6 in RecordTransactionCommit () at xact.c:688
#12 0x80bd245 in repair_frag (vacrelstats=0x8234d44, onerel=0x8210344, vacuum_pages=0xbfffeeac, fraged_pages=0xbfffee9c,
nindices=2, Irel=0x8234dbc) at vacuum.c:1790
#13 0x80ba26b in vacuum_rel (relid=1249, analyze=1, is_toastrel=0 '\000') at vacuum.c:477
#14 0x80b9cf3 in vac_vacuum (VacRelP=0x0, analyze=1 '\001', anal_cols2=0x0) at vacuum.c:245
#15 0x80b9c6c in vacuum (vacrel=0x0, verbose=0, analyze=1 '\001', anal_cols=0x0) at vacuum.c:163
#16 0x8117a25 in ProcessUtility (parsetree=0x824529c, dest=Remote) at utility.c:690
#17 0x8115775 in pg_exec_query_string (query_string=0x8244f50 "vacuum analyze;", dest=Remote, parse_context=0x81fbe58)
at postgres.c:786
#18 0x8116802 in PostgresMain (argc=4, argv=0xb148, real_argc=3, real_argv=0xba34, username=0x8208f99 "pjw")
at postgres.c:1826
#19 0x80fd6ef in DoBackend (port=0x8208d30) at postmaster.c:2060
#20 0x80fd28a in BackendStartup (port=0x8208d30) at postmaster.c:1837
#21 0x80fc556 in ServerLoop () at postmaster.c:1027
#22 0x80fbf3d in PostmasterMain (argc=3, argv=0xba34) at postmaster.c:700
#23 0x80dc095 in main (argc=3, argv=0xba34) at main.c:112
FWIW, this is quite reproducible.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au |/ \|
|----
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Re: UUNET socket-file-location patch
> Bruce Momjian <[EMAIL PROTECTED]> writes: > >> Peter Eisentraut <[EMAIL PROTECTED]> writes: > Should the parameter determine the directory or the full file name? I'd > go for the former, but it's not a strong case. > >> > >> Directory was what I had in mind too, but I'm not sure what Bruce > >> actually did ... > > > I did whatever the patch did. I believe it is the full path. I believe > > it is used here: > > > #define UNIXSOCK_PATH(sun,port,defpath) \ > > ((defpath && defpath[0] != '\0') ? (strncpy((sun).sun_path, > > defpath, sizeof((sun).sun_path)), > > (sun).sun_path[sizeof((sun).sun_path)-1] = '\0') : > > sprintf((sun).sun_path, "/tmp/.s.PGSQL.%d", (port))) > > Hmm. I think it would make more sense to make the parameter be just > the directory, not the full path including filename --- for one thing, > doing it like that renders the port-number parameter useless. Why not > > #define UNIXSOCK_PATH(sun,port,defpath) \ > snprintf((sun).sun_path, sizeof((sun).sun_path), "%s/.s.PGSQL.%d", \ > (((defpath) && *(defpath) != '\0') ? (defpath) : "/tmp"), \ > (port)) > > regards, tom lane > OK, here is the diff to make the socket file option specify just a directory, not a full path. Documentation changes were also made. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ? Makefile.custom ? GNUmakefile ? Makefile.global ? log ? crtags ? backend/postgres ? backend/catalog/global.description ? backend/catalog/global.bki ? backend/catalog/template1.bki ? backend/catalog/template1.description ? backend/port/Makefile ? bin/initdb/initdb ? bin/initlocation/initlocation ? bin/ipcclean/ipcclean ? bin/pg_config/pg_config ? bin/pg_ctl/pg_ctl ? bin/pg_dump/pg_dump ? bin/pg_dump/pg_restore ? bin/pg_dump/pg_dumpall ? bin/pg_id/pg_id ? bin/pg_passwd/pg_passwd ? bin/pgaccess/pgaccess ? bin/pgtclsh/Makefile.tkdefs ? bin/pgtclsh/Makefile.tcldefs ? bin/pgtclsh/pgtclsh ? bin/pgtclsh/pgtksh ? bin/psql/psql ? bin/scripts/createlang ? include/config.h ? include/stamp-h ? interfaces/ecpg/lib/libecpg.so.3.2.0 ? interfaces/ecpg/preproc/ecpg ? interfaces/libpgeasy/libpgeasy.so.2.1 ? interfaces/libpgtcl/libpgtcl.so.2.1 ? interfaces/libpq/libpq.so.2.1 ? interfaces/perl5/blib ? interfaces/perl5/Makefile ? interfaces/perl5/pm_to_blib ? interfaces/perl5/Pg.c ? interfaces/perl5/Pg.bs ? pl/plperl/blib ? pl/plperl/Makefile ? pl/plperl/pm_to_blib ? pl/plperl/SPI.c ? pl/plperl/plperl.bs ? pl/plpgsql/src/libplpgsql.so.1.0 ? pl/tcl/Makefile.tcldefs Index: include/libpq/pqcomm.h === RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/libpq/pqcomm.h,v retrieving revision 1.45 diff -c -r1.45 pqcomm.h *** include/libpq/pqcomm.h 2000/11/15 18:36:06 1.45 --- include/libpq/pqcomm.h 2000/11/22 01:33:54 *** *** 51,66 /* Configure the UNIX socket address for the well known port. */ #if defined(SUN_LEN) - #define UNIXSOCK_PATH(sun,port,defpath) \ - ((defpath && defpath[0] != '\0') ? (strncpy((sun).sun_path, defpath, sizeof((sun).sun_path)), (sun).sun_path[sizeof((sun).sun_path)-1] = '\0') : sprintf((sun).sun_path, "/tmp/.s.PGSQL.%d", (port))) #define UNIXSOCK_LEN(sun) \ (SUN_LEN(&(sun))) #else - #define UNIXSOCK_PATH(sun,port,defpath) \ - ((defpath && defpath[0] != '\0') ? (strncpy((sun).sun_path, defpath, sizeof((sun).sun_path)), (sun).sun_path[sizeof((sun).sun_path)-1] = '\0') : sprintf((sun).sun_path, "/tmp/.s.PGSQL.%d", (port))) #define UNIXSOCK_LEN(sun) \ (strlen((sun).sun_path)+ offsetof(struct sockaddr_un, sun_path)) #endif /* *We do this because sun_len is in BSD's struct, while others don't. --- 51,65 /* Configure the UNIX socket address for the well known port. */ #if defined(SUN_LEN) #define UNIXSOCK_LEN(sun) \ (SUN_LEN(&(sun))) #else #define UNIXSOCK_LEN(sun) \ (strlen((sun).sun_path)+ offsetof(struct sockaddr_un, sun_path)) #endif + + #define UNIXSOCK_PATH(sun,port,defpath) \ + (snprintf((sun).sun_path, UNIXSOCK_LEN(sun), "%s/.s.PGSQL.%d", (defpath && +*(defpath) != '\0') ? (defpath) : "/tmp", (port))) /* *We do this because sun_len is in BSD's struct, while others don't.
RE: [HACKERS] Crash during WAL recovery?
At 09:14 AM 11/22/00 +0800, Christopher Kings-Lynne wrote: >Is there any particular reason the spelling and punctuation in the code >snippet below is so bad? Vadim's Russian. This impacts his english but not his ability to implement complex features like MVCC and WAL :) - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
RE: [HACKERS] Crash during WAL recovery?
Is there any particular reason the spelling and punctuation in the code
snippet below is so bad?
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Eisentraut
> Sent: Wednesday, November 22, 2000 6:04 AM
> To: PostgreSQL Development
> Subject: [HACKERS] Crash during WAL recovery?
>
>
> This snippet in xlog.c makes we wonder...
>
> else if (ControlFile->state == DB_IN_RECOVERY)
> {
> elog(LOG, "Data Base System was interrupted being
> in recovery at %s\n"
>"\tThis propably means that some data
> blocks are corrupted\n"
>"\tAnd you will have to use last backup
> for recovery",
>str_time(ControlFile->time));
> }
>
> I thought this was going to be crash safe.
>
> --
> Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
>
[HACKERS] Re: [COMMITTERS] pgsql/contrib/pg_dumplo (README.pg_dumplo lo_export.c lo_import.c main.c pg_dumplo.h utils.c)
Peter Eisentraut <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] writes: >> Modified Files: >> README.pg_dumplo lo_export.c lo_import.c main.c pg_dumplo.h >> utils.c >> >> Code review: minor cleanups, make the world safe for unsigned OIDs. >> Improve documentation, too. > Doesn't pg_dump handle large objects these days? It does, so pg_dumplo is probably dead code --- for people running 7.1 or later. The reason I'm taking an interest in it is that Great Bridge wants to make it available to people running 6.5.* or 7.0.*, so that they can get their large objects into newer versions in the first place. Also, it's possible that someone using pg_dumplo would not want to change (though I'm not sure why not). So we probably oughta leave it in the distro for a version or three anyway. regards, tom lane
[HACKERS] Re: [COMMITTERS] pgsql/contrib/pg_dumplo (README.pg_dumplo lo_export.clo_import.c main.c pg_dumplo.h utils.c)
[EMAIL PROTECTED] writes: > Date: Tuesday, November 21, 2000 @ 19:00:55 > Author: tgl > > Update of /home/projects/pgsql/cvsroot/pgsql/contrib/pg_dumplo > from hub.org:/home/projects/pgsql/tmp/cvs-serv39905 > > Modified Files: > README.pg_dumplo lo_export.c lo_import.c main.c pg_dumplo.h > utils.c > > - Log Message - > > Code review: minor cleanups, make the world safe for unsigned OIDs. > Improve documentation, too. Doesn't pg_dump handle large objects these days? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
RE: [HACKERS] Crash during WAL recovery?
At 02:01 PM 11/21/00 -0800, Mikheev, Vadim wrote:
>> This snippet in xlog.c makes we wonder...
>>
>> else if (ControlFile->state == DB_IN_RECOVERY)
>> {
>> elog(LOG, "Data Base System was interrupted
>> being in recovery at %s\n"
>> "\tThis propably means that some data
>> blocks are corrupted\n"
>> "\tAnd you will have to use last
>> backup for recovery",
>> str_time(ControlFile->time));
>> }
>>
>> I thought this was going to be crash safe.
>
>WAL doesn't protect against disk block corruption what
>could be reason of crash (or elog(STOP)) during recovery
>in most cases. Apart from disk corruption recovery is
>(or should be -:)) crash safe.
Which is why we'll still need BAR tools later.
The WAL log can be used to recover from a crash if the database
itself isn't corrupted (disk corruption, whatever), but not
otherwise because it applies logged data to the database itself.
The WAL log doesn't include changes caused by renegade disk
controllers, etc :)
BAR tools will allow recovery via archives of WAL logs applied
to an archive of the database, to recreate the database in the
case where the existing database has been corrupted.
In Oracle parlance, "WAL" log == "REDO" log, and the BAR tool
builds "Archive" logs.
Uhhh...I think, anyway.
- Don Baccus, Portland OR <[EMAIL PROTECTED]>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
RE: [HACKERS] Crash during WAL recovery?
> This snippet in xlog.c makes we wonder...
>
> else if (ControlFile->state == DB_IN_RECOVERY)
> {
> elog(LOG, "Data Base System was interrupted
> being in recovery at %s\n"
>"\tThis propably means that some data
> blocks are corrupted\n"
>"\tAnd you will have to use last
> backup for recovery",
>str_time(ControlFile->time));
> }
>
> I thought this was going to be crash safe.
WAL doesn't protect against disk block corruption what
could be reason of crash (or elog(STOP)) during recovery
in most cases. Apart from disk corruption recovery is
(or should be -:)) crash safe.
Vadim
Re: [HACKERS] Questions on RI spec (poss. bugs)
Jan Wieck writes: > Stephan Szabo wrote: > > > >There's a message on -general about a possible > > problem in the deferred RI constraints. He was doing a > > sequence like: > > begin > > delete > > insert > > end > > and having it fail even though the deleted key was back in > > place at the end. > > Isn't that (delete and reinsert the same PK) what the > standard means with "triggered data change violation"? Triggered data change violations can only occur if the same attribute is changed twice during the same *statement*, not transaction. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] quick english patch
Larry Rosenman writes: > Want me to do a new patch, or will you fix mine? I'll fix all these things. I'm also somewhat annoyed that these messages show up during initdb now. Anyone know why exactly? I couldn't trace it down. > > LER > > * Peter Eisentraut <[EMAIL PROTECTED]> [001121 11:51]: > > Larry Rosenman writes: > > > > > --- 1426,1432 > > >ControlFile->catalog_version_no, CATALOG_VERSION_NO); > > > > > > if (ControlFile->state == DB_SHUTDOWNED) > > > ! elog(LOG, "Data Base System was shutdown at %s", > > > > shut down (two words) > > > > >str_time(ControlFile->time)); > > > else if (ControlFile->state == DB_SHUTDOWNING) > > > elog(LOG, "Data Base System was interrupted when shutting down >at %s", > > > -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Questions on RI spec (poss. bugs)
Stephan Szabo wrote: > >There's a message on -general about a possible > problem in the deferred RI constraints. He was doing a > sequence like: > begin > delete > insert > end > and having it fail even though the deleted key was back in > place at the end. Isn't that (delete and reinsert the same PK) what the standard means with "triggered data change violation"? It is a second touching of a unique matching PK. And in this case the standard doesn't define a behaviour, instead it says you cannot do so. In the case of reinserting a deleted PK, does the new PK row inherit the references to the old PK row? If so, an ON DELETE CASCADE must be suppressed - no? If I'm right that it should be a "triggered data change violation", the problem is just changing into one we have with delete/reinsert in the ON DELETE CASCADE case. Haven't tested, but the current implementation shouldn't detect it. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [HACKERS] quick english patch
Want me to do a new patch, or will you fix mine? LER * Peter Eisentraut <[EMAIL PROTECTED]> [001121 11:51]: > Larry Rosenman writes: > > > --- 1426,1432 > > ControlFile->catalog_version_no, CATALOG_VERSION_NO); > > > > if (ControlFile->state == DB_SHUTDOWNED) > > ! elog(LOG, "Data Base System was shutdown at %s", > > shut down (two words) > > > str_time(ControlFile->time)); > > else if (ControlFile->state == DB_SHUTDOWNING) > > elog(LOG, "Data Base System was interrupted when shutting down at %s", > > > > -- > Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
At 10:58 AM 11/21/00 -0500, Tom Lane wrote: >> The MySQL folk have always cherry-picked their benchmarks, long lied >> about features in PG, do their benchmarking using default values >> for PG's shared buffer etc WITHOUT TELLING PEOPLE while at the same >> time installing MySQL with larger-than-default memory usage limits (the >> group hired by GB used MySQL's default installation, but EXPLICITLY SAID >> SO in the report), etc. > >The revised results that are on GB's site now include curves for MySQL >*with* tuning per advice from the MySQL folk. That's good. Have the MySQL folk made any effort to reciprocate? - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] quick english patch
Larry Rosenman writes: > --- 1426,1432 >ControlFile->catalog_version_no, CATALOG_VERSION_NO); > > if (ControlFile->state == DB_SHUTDOWNED) > ! elog(LOG, "Data Base System was shutdown at %s", shut down (two words) >str_time(ControlFile->time)); > else if (ControlFile->state == DB_SHUTDOWNING) > elog(LOG, "Data Base System was interrupted when shutting down at %s", > -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] Still having problems with DIGEST
I've subscribed and un-subscribed to the HACKERS-DIGEST list several times now. Each time I seem to be getting EVERY message sent to the list rather than a DIGEST. Can someone tell me if it is still possible to get a DIGEST of the list? Is the list administrator aware of the problem? Thanks. -Tony
Re: [HACKERS] SET SESSION CHARACTERISTICS
> > >SET SESSION CHARACTERISTICS AS parameter value > > > is really a more SQL'ish form of the current > > >SET parameter =/TO value > > > Perhaps they should be made equivalent, in order to avoid too many subtly > > > different subversions of the 'SET' command. > > Hmm. What do you mean by "equivalent"? > That they have the same effect when invoked. OK. > > I assumed that the incredibly > > verbose SQL99 form is not particularly gratifying to type, and that we > > would be interested in a shorter version of the same thing. > Definitely. But it would also be nice if we didn't have too many SET > commands that have intersecting functionality but where it's not quite > clear which controls what. Given that our custom short SET variant does > effectively control "session characteristics" it only seemed logical to me > that we could map it to the more SQL'ish variant. Sure. > > So I kept the original syntax and just added the statements that SQL99 > > calls out explictly. > Then I don't know where you got the TRANSACTION COMMIT and TIME ZONE > clauses from. SQL 99 doesn't have the former anywhere, and the latter > only as 'SET TIME ZONE' which we have already. OK, so maybe my recollection is not very good... > > Also, our "SET" syntax has lots more keywords than specified in > > SQL99... > Hmm, is it your argument that we should keep our custom parameters in our > custom command in order to avoid conflicts with future standards? Maybe > so, but then we already lose. Well, no argument really ;) I put the SET SESSION CHARACTERISTICS in as a start at the SQL99-defined functionality. Now would be a good time to make it right. - Thomas
Re: [HACKERS] SET SESSION CHARACTERISTICS
Thomas Lockhart writes: > >SET SESSION CHARACTERISTICS AS parameter value > > is really a more SQL'ish form of the current > >SET parameter =/TO value > > Perhaps they should be made equivalent, in order to avoid too many subtly > > different subversions of the 'SET' command. > > Hmm. What do you mean by "equivalent"? That they have the same effect when invoked. > I assumed that the incredibly > verbose SQL99 form is not particularly gratifying to type, and that we > would be interested in a shorter version of the same thing. Definitely. But it would also be nice if we didn't have too many SET commands that have intersecting functionality but where it's not quite clear which controls what. Given that our custom short SET variant does effectively control "session characteristics" it only seemed logical to me that we could map it to the more SQL'ish variant. > So I kept the original syntax and just added the statements that SQL99 > calls out explictly. Then I don't know where you got the TRANSACTION COMMIT and TIME ZONE clauses from. SQL 99 doesn't have the former anywhere, and the latter only as 'SET TIME ZONE' which we have already. > Also, our "SET" syntax has lots more keywords than specified in > SQL99... Hmm, is it your argument that we should keep our custom parameters in our custom command in order to avoid conflicts with future standards? Maybe so, but then we already lose. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[GENERAL] Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
Don Baccus <[EMAIL PROTECTED]> writes: > Great Bridge didn't do the benchmarking, they hired a third party to > do so. And that third party didn't, AFAIK, cherry-pick tests in order > to "prove" PG's superiority. In fairness, the third party was Xperts Inc, who have long done a lot of programming-related work for Landmark Communications; so there's a pretty close working relationship, it's not exactly arms-length. I think what may be more worth noting is that that benchmarking project was started as part of Landmark's "due diligence" investigation while deciding whether they wanted to bet a company on Postgres. They didn't go into it with the notion of proving Postgres superior; they went into it to find out if they were betting on a dog. They were very pleasantly surprised (as was the core group, when we first saw the results!). Naturally, their marketing guys said "hey, let's clean this up and publish it". There's a certain amount of after-the-fact selection here, since you'd certainly never have seen the results if they hadn't been favorable to Postgres; but there was no attempt to skew the results in Postgres' favor. If anything, the opposite. > The MySQL folk have always cherry-picked their benchmarks, long lied > about features in PG, do their benchmarking using default values > for PG's shared buffer etc WITHOUT TELLING PEOPLE while at the same > time installing MySQL with larger-than-default memory usage limits (the > group hired by GB used MySQL's default installation, but EXPLICITLY SAID > SO in the report), etc. The revised results that are on GB's site now include curves for MySQL *with* tuning per advice from the MySQL folk. regards, tom lane
[GENERAL] Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
Pete Forman <[EMAIL PROTECTED]> writes: > I thought that Great Bridge's August benchmarks were rather skewed. > They only used one particular test from the AS3AP suite. AFAIK there was nothing particularly sinister about that --- they didn't have time to run a large number of different tests, so they chose ones that seemed most important. They certainly didn't try a bunch of tests and then publish only the most favorable; the two tests used were selected at the beginning of the project, before anyone knew what the results would look like. regards, tom lane
Re: [HACKERS] Table/Column Constraints
At 12:18 AM 11/21/00 -0500, Tom Lane wrote: >Don Baccus <[EMAIL PROTECTED]> writes: >> If this problem is attacked, should one stop at constraints or make certain >> that other elements like views are dumped properly, too? (or were views >> fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over >> the last few months") ... >Views do seem to be dumped as views by current sources. Good...definitely a step in the right direction! - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
At 10:19 AM 11/21/00 +, Pete Forman wrote: >Don Baccus writes: > > I also hope that the PG crew, and Great Bridge, never stoop so low > > as to ship benchmarks wired to "prove" PG's superiority. > >I thought that Great Bridge's August benchmarks were rather skewed. >They only used one particular test from the AS3AP suite. That was the >basis for their headline figure of 4-5 times the performance of the >competition. > >I was however impressed by the TPC-C results. MySQL and Interbase >were unable to complete them. PostgreSQL showed almost identical >performance over a range of loads to Proprietary 1 (version 8.1.5, on >Linux) and Proprietary 2 (version 7.0, on NT). Great Bridge didn't do the benchmarking, they hired a third party to do so. And that third party didn't, AFAIK, cherry-pick tests in order to "prove" PG's superiority. The report itself mentioned the testing group's surprise over MySQL's poor showing in the simple, non-TPC-C test. I'm sure it was tossed in so they could answer the question "how much does it cost you to use a transaction-based system rather than MySQL", since avoiding that overhead is the big argument that the MySQL makes in favor of their product. I'm sure the hope was there that the answer would be "not all that much", instead the answer was "gee, you're not that fast after all". Clearly the real target of the benchmark effort was Oracle. However inadequate the benchmarking effort might've been (they're all inadequate, after all) the fact is that Great Bridge at least did run a set of standard benchmarks. The MySQL folk have always cherry-picked their benchmarks, long lied about features in PG, do their benchmarking using default values for PG's shared buffer etc WITHOUT TELLING PEOPLE while at the same time installing MySQL with larger-than-default memory usage limits (the group hired by GB used MySQL's default installation, but EXPLICITLY SAID SO in the report), etc. The GB-financed benchmarks weren't perfect, but they weren't dishonest. The MySQL folks have done things over the years that have been out-and-out dishonest, IMO. - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
> I've wondered and am still wondering what a lot of these benchmark tests > are out to prove. In this case, the "benchmark test" was not out to prove anything. It was an good-faith result of a porting effort with a suprising (to the tester) result. > I'm not sure that any PostgreSQL advocate has ever said or > implied that PostgreSQL is faster than anything, much less MySQL. While I'm > sure it's faster than some, I've just never heard the argument for using > PostgreSQL as "It's faster than anything else". Very true. But it turns out that in at least some real-world tests, in this case a real application *built for MySQL*, PostgreSQL was substantially faster when the number of users climbed above a very small number. These results are consistant with and supported by GB's initial published benchmark results. Two separate styles of comparisons with consistant results might help someone choose the right solution for their application. No harm in that, eh? > I chose PostgreSQL by what > it could do, not how fast it can SELECT... No benchmark between MySQL and > PostgreSQL (or any other RDBMS ) is ever going to be truly accurate since > there are so many things MySQL simply can't to that PostgreSQL (and others) > can.. Well, that is another dimension to the evaluation/comparison. But the testing results stand on their own: you *can* choose PostgreSQL for its performance, and you *will* have made the right choice. This is especially gratifying for all of us who have contributed to PostgreSQL because we *didn't* benchmark it, and *assumed* that MySQL claims for superior speed under all circumstances were accurate. Turns out it may be true for single-user mode, but that we've built a darn fast RDBMS for real-world applications. One *very unfair* part of these benchmarks and comparisons is that both MySQL and PostgreSQL can be identified by name for the comparisons, so they tend to be talked about the most. But the GB benchmarks could lead one to conclude that if SourceForge had been built with another database product it would also have seen a performance improvement when tested with PostgreSQL. - Thomas
[HACKERS] quick english patch
Fix some english issues... I also note some "interesting" (from an English perspective) #define names that mayhaps need to be looked at. Index: xlog.c === RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.31 diff -c -r1.31 xlog.c *** xlog.c 2000/11/21 10:17:57 1.31 --- xlog.c 2000/11/21 13:12:49 *** *** 1426,1432 ControlFile->catalog_version_no, CATALOG_VERSION_NO); if (ControlFile->state == DB_SHUTDOWNED) ! elog(LOG, "Data Base System was shutted down at %s", str_time(ControlFile->time)); else if (ControlFile->state == DB_SHUTDOWNING) elog(LOG, "Data Base System was interrupted when shutting down at %s", --- 1426,1432 ControlFile->catalog_version_no, CATALOG_VERSION_NO); if (ControlFile->state == DB_SHUTDOWNED) ! elog(LOG, "Data Base System was shutdown at %s", str_time(ControlFile->time)); else if (ControlFile->state == DB_SHUTDOWNING) elog(LOG, "Data Base System was interrupted when shutting down at %s", -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
Don Baccus writes: > I also hope that the PG crew, and Great Bridge, never stoop so low > as to ship benchmarks wired to "prove" PG's superiority. I thought that Great Bridge's August benchmarks were rather skewed. They only used one particular test from the AS3AP suite. That was the basis for their headline figure of 4-5 times the performance of the competition. I was however impressed by the TPC-C results. MySQL and Interbase were unable to complete them. PostgreSQL showed almost identical performance over a range of loads to Proprietary 1 (version 8.1.5, on Linux) and Proprietary 2 (version 7.0, on NT). -- Pete Forman -./\.- Disclaimer: This post is originated Western Geophysical -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- the opinion of Baker Hughes or http://www.crosswinds.net/~petef -./\.- its divisions.
Re: [HACKERS] Re: [COMMITTERS] pgsql/src/backend/access/transam (xlog.c)
> > Nope. Still fails... > > > > You should've said that the OIDs are now just off-by-one from where they > > were before, instead of off by several thousand. That I'm willing to > > accept as an implementation change ;-) I've updated the expected file. ^^ I hope for it -:) > Actually, pg_shadow' oid for DBA inserted by initdb is 2 now - I'm fixing > this now... Fixed. Vadim
Re: [HACKERS] Re: [COMMITTERS] pgsql/src/backend/access/transam (xlog.c)
> > Nope. Still fails... > > > > You should've said that the OIDs are now just off-by-one from where they > > were before, instead of off by several thousand. That I'm willing to > > accept as an implementation change ;-) I've updated the expected file. ^^ I hope for it -:) > Actually, pg_shadow' oid for DBA inserted by initdb is 2 now - I'm fixing > this now... Fixed. Vadim
Re: [HACKERS] Re: [COMMITTERS] pgsql/src/backend/utils/adt (ri_triggers.c)
Tom Lane wrote: > [EMAIL PROTECTED] writes: > > Update of /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt > > Modified Files: > > ri_triggers.c > > keep relations open until they are no longer needed. > > Something that's been bothering me for a good while about ri_triggers > is that it opens the relations without any lock to begin with. > That can't possibly be safe, can it? Opening relations with no lock seems illegal to me. Though I have no evidence that it does wrong thing in ri_triggers.c,it seems that we had better acquire an AccessShareLock on trial. I sometimes see SEGV error around ri stuff and I've doubted opening relations with no lock. However the cause was different from it. Hiroshi Inoue
RE: [HACKERS] Table/Column Constraints
At 15:10 21/11/00 +0800, Christopher Kings-Lynne wrote: >> CREATE TABLE example >> ( example_id serial >> >> -- Must be a ZIP or Postal Code >> , regionvarchar(6) >> >> -- Descriptive text >> , description varchar(60) >> ); > >Actually - this is something I _could_ do. > >As the pg_dump is running, it shouldn't be too hard to select the comment >associated with each entity as it is being dumped. ie. In the example >above, the comments for each attribute would be retrieved from >pg_description (or whatever) and output as '-- ...' comments. I was actually more worried about making sure the constraints were dumped separately from the table, but maybe I missed the point of the original post. >Then, if the COMMENT ON statements are also still dumped at the bottom, you >get the ability to see comments conveniently in your dump, but with the >ability to still hand-edit them before restoring the dump... If I recall correctly, the comments are actually grabbed when each table is retrieved, so it is easy to do. But is it really a good idea? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
