Re: [HACKERS] Again, sorry, caching.
Greg Copeland wrote: At this point in time, I think we've both pretty well beat this topic up. Obviously there are two primary ways of viewing the situation. I don't think anyone is saying it's a bad idea...I think everyone is saying that it's easier to address elsewhere and that overall, the net returns may be at the expense of some other work loads. So, unless there are new pearls to be shared and gleaned, I think the topics been fairly well addressed. Does more need to said? With a PREPARE/EXECUTE patch now out for approval, can I assume we will go with that first and see how far it gets us, and then revisit the idea of cached results. In this case, we are caching the query plan. The query still executes again in the executor, so the data is always fresh. In a sense, the buffer cache and disk are the caches, which don't need separate invalidation if some data changes in the table. The plan can get invalid if it calls a non-cachable function or the schema changes, or the constants used to generate the plan in the optimizer would generate a different plan from the constants used in a later query, or the analyze statistics changed. The MVCC ramifications of cached results and invalidation could be quite complex. The commit of a transaction could change tuple visibility rules even if the data modify statement was executed much earlier in the transaction. Also, on the NOTIFY/trigger idea, triggers are called on statement end, not transaction end, so if an UPDATE query is in a multi-statement transaction, another backend looking for the NOTIFY will receive it before the transaction commits, meaning it will not see the update. That seems like a problem. We do have deferrable constraints which will only do checking on transaction end, but I am not sure if that can be used for NOTIFY on transaction commit. Anyone? -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regexp character class locale awareness patch
Can someone who is multbyte-aware comment on this patch? Thanks.
---
Manuel Sugawara wrote:
Attached is a pacth against 7.2 which adds locale awareness to
the character classes of the regular expression engine. Please
consider including this feature to postgreSQL.
Regards, Manuel.
Content-Description: regexp character class locale awareness patch
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
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
*** src/backend/regex/regcomp.c.org Sun Mar 17 16:39:13 2002
--- src/backend/regex/regcomp.c Sun Mar 17 16:53:43 2002
***
*** 47,53
--- 47,64
#include regex/regex.h
#include regex/utils.h
#include regex/regex2.h
+ #ifdef USE_LOCALE
+ struct cclass
+ {
+ char *name;
+ char *chars;
+ char *multis;
+ };
+ static struct cclass* cclasses = NULL;
+ static struct cclass* cclass_init(void);
+ #else
#include regex/cclass.h
+ #endif /* USE_LOCALE */
#include regex/cname.h
/*
***
*** 174,179
--- 185,195
pg_wchar *wcp;
#endif
+ #ifdef USE_LOCALE
+ if ( cclasses == NULL )
+ cclasses = cclass_init();
+ #endif /* USE_LOCALE */
+
#ifdef REDEBUG
#define GOODFLAGS(f) (f)
#else
***
*** 884,890
struct cclass *cp;
size_t len;
char *u;
! charc;
while (MORE() pg_isalpha(PEEK()))
NEXT();
--- 900,906
struct cclass *cp;
size_t len;
char *u;
! unsigned char c;
while (MORE() pg_isalpha(PEEK()))
NEXT();
***
*** 905,911
u = cp-chars;
while ((c = *u++) != '\0')
! CHadd(cs, c);
for (u = cp-multis; *u != '\0'; u += strlen(u) + 1)
MCadd(p, cs, u);
}
--- 921,927
u = cp-chars;
while ((c = *u++) != '\0')
! CHadd(cs, c);
for (u = cp-multis; *u != '\0'; u += strlen(u) + 1)
MCadd(p, cs, u);
}
***
*** 1716,1718
--- 1732,1796
return (islower((unsigned char) c));
#endif
}
+
+ #ifdef USE_LOCALE
+ static struct cclass *
+ cclass_init(void)
+ {
+ struct cclass *cp = NULL;
+ struct cclass *classes = NULL;
+ struct cclass_factory
+ {
+ char *name;
+ int (*func)(int);
+ char *chars;
+ } cclass_factories [] =
+ {
+ { alnum, isalnum, NULL },
+ { alpha, isalpha, NULL },
+ { blank, NULL, \t },
+ { cntrl, iscntrl, NULL },
+ { digit, NULL, 0123456789 },
+ { graph, isgraph, NULL },
+ { lower, islower, NULL },
+ { print, isprint, NULL },
+ { punct, ispunct, NULL },
+ { space, NULL, \t\n\v\f\r },
+ { upper, isupper, NULL },
+ { xdigit, isxdigit, NULL },
+ { NULL, NULL, NULL }
+ };
+ struct cclass_factory *cf = NULL;
+
+ classes = malloc(sizeof(struct cclass) * (sizeof(cclass_factories) /
+sizeof(struct cclass_factory)));
+ if (classes == NULL)
+ elog(ERROR,cclass_init: out of memory);
+
+ cp = classes;
+ for(cf = cclass_factories; cf-name != NULL; cf++)
+ {
+ cp-name = strdup(cf-name);
+ if ( cf-chars )
+ cp-chars = strdup(cf-chars);
+ else
+ {
+ int x = 0, y = 0;
+ cp-chars = malloc(sizeof(char) * 256);
+ if (cp-chars == NULL)
+ elog(ERROR,cclass_init: out of memory);
+ for (x = 0; x 256; x++)
+ {
+ if((cf-func)(x))
+ *(cp-chars + y++) = x;
+ }
+ *(cp-chars + y) = '\0';
+ }
+ cp-multis = ;
+ cp++;
+ }
+ cp-name = cp-chars = NULL;
+ cp-multis = ;
+
+ return classes;
+ }
+ #endif /* USE_LOCALE */
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Again, sorry, caching.
Bruce Momjian [EMAIL PROTECTED] writes: Also, on the NOTIFY/trigger idea, triggers are called on statement end, not transaction end, so if an UPDATE query is in a multi-statement transaction, another backend looking for the NOTIFY will receive it before the transaction commits, meaning it will not see the update. No it won't. 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 with current sources? Re: cost of parse/plan/execute for one sample query
Barry Lind [EMAIL PROTECTED] writes: In testing Neil's PREPARE/EXECUTE patch on my test query, I found the parser complains that this query is not valid when using current sources. The error I get is: psql:testorig.sql:1: ERROR: JOIN/ON clause refers to xf2, which is not part of JOIN Hmm. I have an open bug with sub-SELECTs inside a JOIN, but this example doesn't look like it would trigger that. I think the sql is valid (at least it has worked in 7.1 and 7.2). Is this a bug? Dunno. Give me a test case (and no, I am *not* going to try to reverse-engineer table schemas from that SELECT). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Again, sorry, caching.
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Also, on the NOTIFY/trigger idea, triggers are called on statement end, not transaction end, so if an UPDATE query is in a multi-statement transaction, another backend looking for the NOTIFY will receive it before the transaction commits, meaning it will not see the update. No it won't. Is this because NOTIFY is held for transaction end or because the triggers are held until transaction commit? -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Again, sorry, caching.
Bruce Momjian [EMAIL PROTECTED] writes: No it won't. Is this because NOTIFY is held for transaction end or because the triggers are held until transaction commit? The former. 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] Again, sorry, caching.
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: No it won't. Is this because NOTIFY is held for transaction end or because the triggers are held until transaction commit? The former. Thanks. I see it in the NOTIFY manual page now. Very nice. -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] WITH DELIMITERS in COPY
On Sun, 14 Apr 2002, Bruce Momjian wrote: Gavin, I will do the legwork on this if you wish. I think we need to No matter. I intended to submit a patch to fix this. use DefElem to store the COPY params, rather than using specific fields in CopyStmt. DefElem would have required modification of code outside the parser (to keep utility.c and DoCopy() happy) or otherwise an even messier loop executed as a result of CopyStmt than I have given in the attached patch, plus other issues with Yacc. The patch attached maintains backward compatibility. The syntax is as follows: COPY [BINARY] relname [WITH OIDS] FROM/TO [USING DELIMITERS delimiter] [WITH [ DELIMITER delimiter | NULL AS char | OIDS ]] I was also going to allow BINARY in the WITH list, but there seems to be little point. Note that if you execute a query such as: COPY pg_class TO '/some/path/file/out' USING DELIMITERS tab WITH DELIMITER '|'; The code will give preference to WITH DELIMITER. If no one can find fault with this or my implementation, I'll follow up with documentation and psql patches (not sure that there is much point patching pg_dump). Gavin copy2.diff.gz Description: GNU Zip compressed data ---(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] [PATCHES] WITH DELIMITERS in COPY
Gavin Sherry writes: The patch attached maintains backward compatibility. The syntax is as follows: COPY [BINARY] relname [WITH OIDS] FROM/TO [USING DELIMITERS delimiter] [WITH [ DELIMITER delimiter | NULL AS char | OIDS ]] I think we should lose the WITH altogether. It's not any better than USING. But just saying OIDS is not very clear. In this case the WITH is necessary. Note that if you execute a query such as: COPY pg_class TO '/some/path/file/out' USING DELIMITERS tab WITH DELIMITER '|'; The code will give preference to WITH DELIMITER. That should be an error. If no one can find fault with this or my implementation, I'll follow up with documentation and psql patches (not sure that there is much point patching pg_dump). pg_dump should use the new syntax. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Redhat 7.2.93 performance (was:Re: [HACKERS] PostgreSQL 7.2.1-2PGDG RPMs available for RedHat-skipjack 7.2.93 and RedHat 6.2/SPARC)
[Trimmed CC list] On Sunday 14 April 2002 01:52 am, Hannu Krosing wrote: On Sun, 2002-04-14 at 08:48, Lamar Owen wrote: Incidentally, the 7.2.93 (skipjack) public beta is a serious improvement over RHL 7.2, and I personally recommend it, as KDE 3 is worth the upgrade, even to a beta. Is the 7.2.93 (skipjack) public beta an improvement in raw postgresql performance or just in added stuff like KDE ? Hmmm. Raw performance seems to be increased as well, due to an improved kernel (2.4.18 plus low-latency and preemptible patches, according to the kernel source RPM). Although I am a little overwhelmed by the increased performance of this new Athlon 1.2+512MB RAM versus my old Celeron 650+192MB RAM, 7.2.93 seems to be faster on the same hardware. Particularly during the regression tests. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Redhat 7.2.93 performance (was:Re: [HACKERS] PostgreSQL 7.2.1-2PGDG RPMs available for RedHat-skipjack 7.2.93 and RedHat 6.2/SPARC)
On Sunday 14 April 2002 03:00 pm, Luciano Miguel Ferreira Rocha wrote: On Sun, Apr 14, 2002 at 02:35:13PM -0400, Lamar Owen wrote: Raw performance seems to be increased as well, due to an improved kernel (2.4.18 plus low-latency and preemptible patches, according to the kernel source RPM). The low-latency and preemptible patches are not meant for performance gains, but for responsiveness, and are not designed to be used in servers, only in workstations/desktops. ISTM that improving interactive performance would also improve multiuser performance in a server, as low latency and kernel preemption can increase multiuser server responsiveness. Although I am a little overwhelmed by the increased performance of this new Athlon 1.2+512MB RAM versus my old Celeron 650+192MB RAM, 7.2.93 seems to be faster on the same hardware. 2.4.18 does come with a improved VM, what could justify the performance increase. As could an update on the compiler (I've being using gcc 3.1 in my redhat 7.2). The stock gcc on 7.2.93 is still the RedHat-branded 2.96, but with lots of fixes backported from higher versions. However, the improved VM may indeed be a large part of it. It sure feels faster. But I can't recomend the beta to anyone, we had problems with one dual pentium iii server, causing random corruption on /usr/include/*.h and a lock up. Did you happen to report it to Red Hat's Skipjack list, or to bugzilla.redhat.com/bugzilla? Helps make a better dist! I have had less problems thus far with 7.2.93 than I ever did with 7.2. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] [PATCHES] WITH DELIMITERS in COPY
Gavin, I see where you are going with the patch; creating a list in gram.y and stuffing CopyStmt directly there. However, I can't find any other instance of our stuffing things like that in gram.y. We do have cases using options like COPY in CREATE USER, and we do use DefElem. I realize it will require changes to other files like copy.c. However, it seems like the cleanest solution. I guess I am not excited about adding another way to handle WITH options into the code. Now, if you want to argue that CREATE USER shouldn't use DefElem either, we can discuss that, but I think we need to be consistent in how we handle COPY vs. the other commands that use parameters. See commands/user.c for an example of how it uses DefElem, and the tests done to make sure conflicting arguments are not used or in copy's case, specified twice. It just seems like that is the cleanest way to go. One idea I had for the code is to allow BINARY as $2, and WITH OIDS in its current place, and all options in the new WITH location, and concatentate them together into one DefElem list in gram.y, and pass that to copy.c. That way, you can allow BINARY and others at the end too and the list is in one central place. --- Gavin Sherry wrote: On Sun, 14 Apr 2002, Bruce Momjian wrote: Gavin, I will do the legwork on this if you wish. I think we need to No matter. I intended to submit a patch to fix this. use DefElem to store the COPY params, rather than using specific fields in CopyStmt. DefElem would have required modification of code outside the parser (to keep utility.c and DoCopy() happy) or otherwise an even messier loop executed as a result of CopyStmt than I have given in the attached patch, plus other issues with Yacc. The patch attached maintains backward compatibility. The syntax is as follows: COPY [BINARY] relname [WITH OIDS] FROM/TO [USING DELIMITERS delimiter] [WITH [ DELIMITER delimiter | NULL AS char | OIDS ]] I was also going to allow BINARY in the WITH list, but there seems to be little point. Note that if you execute a query such as: COPY pg_class TO '/some/path/file/out' USING DELIMITERS tab WITH DELIMITER '|'; The code will give preference to WITH DELIMITER. If no one can find fault with this or my implementation, I'll follow up with documentation and psql patches (not sure that there is much point patching pg_dump). Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Redhat 7.2.93 performance (was:Re: [HACKERS] PostgreSQL 7.2.1-2PGDG RPMs available for RedHat-skipjack 7.2.93 and RedHat 6.2/SPARC)
On Sun, Apr 14, 2002 at 03:15:39PM -0400, Lamar Owen wrote: ISTM that improving interactive performance would also improve multiuser performance in a server, as low latency and kernel preemption can increase multiuser server responsiveness. I doubt any performance will increase, either on a multiuser or on a singleuser system. Having faster response on mouse clicks or keyboard input doesn't translate on better overall performance, the user just has the felling that it's so. As an example, a part of those patches causes brakes in the middle of some loops (saving buffers to disk, etc). Then other applications that don't depend on disk activity can have change to run, so the system seems faster, it's more responsive. But it won't actually be faster, the system still has to lock again and continue saving the buffers. Actually, in this case there will be an overhead caused by checking if the kernel should brake. However, both projects review the Linux code, and may find, if they haven't already, some places were a finer locking may be used, giving a better performance in a SMP system. But it could also break some integrity. Those patches are not recomended for a server, and now I'm curious to check if the -enterprise configuration has them active. Did you happen to report it to Red Hat's Skipjack list, or to bugzilla.redhat.com/bugzilla? Helps make a better dist! Alas, a bug report saying: the system crashed, I can't login remotely, doesn't help a lot... Regards, Luciano Rocha -- Luciano Rocha, [EMAIL PROTECTED] The trouble with computers is that they do what you tell them, not what you want. -- D. Cohen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.3 schedule
On Fri, Apr 12, 2002 at 12:51:26PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Certainly a shared cache would be good for apps that connect to issue a single query frequently. In such cases, there would be no local cache to use. We have enough other problems with the single-query-per-connection scenario that I see no reason to believe that a shared plan cache will help materially. The correct answer for those folks will *always* be to find a way to reuse the connection. My query cache was write for 7.0. If some next release will use pre-forked backend and after a client disconnection the backend will still alives and waits for new client the shared cache is (maybe:-) not needful. The current backend fork model is killer of all possible caching. We have more caches. I hope persistent backend help will help to all and I'm sure that speed will grow up with persistent backend and persistent caches without shared memory usage. There I can agree with Tom :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] experimental pg_qcache patch
On Sat, Apr 13, 2002 at 06:47:32PM -0400, Neil Conway wrote: I've attached an updated version of Karel Zak's pg_qcache patch, which adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL statements). It should apply cleanly against CVS HEAD, and compile properly -- beyond that, cross your fingers :-) I will try it during this week. Please take a look at the code, play around with using PREPARE and EXECUTE, etc. Let me know if you have any suggestions for improvement Is needful use shared cache? This is right and cardinal question. (Is pre-forked backends expected in next release?) or if you run into any problems -- I've probably introduced some regressions when I ported the code from 7.0 to current sources. BTW, if you run the regression tests, I'd expect (only) the prepare test to fail: I've only written partial regression tests so far. If any other tests fail, please let me know. The basic syntax looks like: PREPARE plan_name AS query; EXECUTE plan_name USING parameters; DEALLOCATE PREPARE plan_name; To get a look at what's being stored in the cache, try: SELECT qcache_state(); For more information on the qCache code, see the README that Karel posted to the list a few days ago. There are still lots of things that need to be improved. Here's a short list: (the first 3 items are the most important, any help on those would be much appreciated) (1) It has a tendancy to core-dump when executing stored queries, particularly if the EXECUTE has an INTO clause -- it will work the first time, but subsequent attempts will either dump core or claim that they can't find the plan in the cache. I don't know this bug :-) (2) Sometimes executing a PREPARE gives this warning: nconway= prepare q1 as select * from pg_class; WARNING: AllocSetFree: detected write past chunk end in TransactionCommandContext 0x83087ac PREPARE Does anyone know what problem this indicates? The memory managment is diffrent between 7.0 and 7.2. There is needful port cache shared-memory managment. I will look at it. (3) Preparing queries with parameters doesn't work: nconway= PREPARE sel USING text AS SELECT * FROM pg_class WHERE relname ~~ $1; ERROR: Parameter '$1' is out of range My original syntax was: PREPARE sel AS SELECT * FROM pg_class WHERE relname ~~ $1 USING text; ... USING is behind query. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] command.c breakup
I'm not exactly sure what you're touching, but could it wait for the below pg_depend patch to be either accepted or rejected? It lightly fiddles with a number of files in the command and catalog directories. http://archives.postgresql.org/pgsql-patches/2002-04/msg00050.php That shouldn't be too much of a problem in the next couple of weeks - if we can decide on a specific day I'll book it into my diary (Any day but Wednesday next week would be fine for me). I will try to have no uncommitted changes over this weekend; that will give you a clear field Monday morning, or you can start on the weekend if you like. Sound good? ---(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] experimental pg_qcache patch
On Sun, Apr 14, 2002 at 10:13:17PM +0200, Karel Zak wrote:
(2) Sometimes executing a PREPARE gives this warning:
nconway= prepare q1 as select * from pg_class;
WARNING: AllocSetFree: detected write past chunk end in TransactionCommandContext
0x83087ac
PREPARE
Does anyone know what problem this indicates?
The memory managment is diffrent between 7.0 and 7.2. There is
needful port cache shared-memory managment. I will look at it.
Hmm, I probably found it be first look to patch file.
The WARNING message is from leak detection. I'm sure that you see
this message if you use SHARE cache type.
- PREPARE_KEY_PREFIX_SIZE is 4 not 3
- in the PrepareKey() is needful fix:
+ if (store == PREPARE_STORE_SHARE) { /* shared between same DB */
+ *flag |= QCF_SHARE_NOTREMOVEABLE;
+ key = (char *) palloc(strlen(name) + PREPARE_KEY_PREFIX_SIZE
+ + strlen(DatabaseName) +1);
^^^
must be 3
+ sprintf(key, %s_%s_, DatabaseName, PREPARE_KEY_PREFIX);
^^
the space for '_' is not allocated :-(
It's my bug probably, I good knew why we need leak detection :-)
Karel
PS. Sorry that I don't send a patch, but now I haven't my computer there.
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] command.c breakup
On Sun, 2002-04-14 at 21:30, Rod Taylor wrote: I'm not exactly sure what you're touching, but could it wait for the below pg_depend patch to be either accepted or rejected? It lightly fiddles with a number of files in the command and catalog directories. http://archives.postgresql.org/pgsql-patches/2002-04/msg00050.php Well, I'm working on it now and it's about 75% done. I hope to post the patch within the next few hours. I'm sorry that I wasn't aware of your patch -but commands/ is a busy place at present :). I've scanned your patch very briefly and the major impacts I can see are: 1) The ALTER TABLE code will be in tablecmds.c (but exactly the same code as at present) 2) The type support will be in typecmds.c (define.c and remove.c are essentially gone -the define and remove commands for foo are in general now together in foocmds.c I'm not touching anything in the catalog directory. Note that as I'm only shuffling code from one file to another, your patch shouldn't need much modification to get it working afterwards - although there is an intention to tidy up common code in the commands/ directory as a second phase, this will consist of more ordinary patches... Regards John ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] command.c breakup
Sounds fair. I'd have brought it up earlier but was away last week. The changes I made are very straight forward and easy enough to redo. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: John Gray [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; Christopher Kings-Lynne [EMAIL PROTECTED]; Hackers [EMAIL PROTECTED] Sent: Sunday, April 14, 2002 4:43 PM Subject: Re: [HACKERS] command.c breakup On Sun, 2002-04-14 at 21:30, Rod Taylor wrote: I'm not exactly sure what you're touching, but could it wait for the below pg_depend patch to be either accepted or rejected? It lightly fiddles with a number of files in the command and catalog directories. http://archives.postgresql.org/pgsql-patches/2002-04/msg00050.php Well, I'm working on it now and it's about 75% done. I hope to post the patch within the next few hours. I'm sorry that I wasn't aware of your patch -but commands/ is a busy place at present :). I've scanned your patch very briefly and the major impacts I can see are: 1) The ALTER TABLE code will be in tablecmds.c (but exactly the same code as at present) 2) The type support will be in typecmds.c (define.c and remove.c are essentially gone -the define and remove commands for foo are in general now together in foocmds.c I'm not touching anything in the catalog directory. Note that as I'm only shuffling code from one file to another, your patch shouldn't need much modification to get it working afterwards - although there is an intention to tidy up common code in the commands/ directory as a second phase, this will consist of more ordinary patches... Regards John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] experimental pg_qcache patch
On Sun, 14 Apr 2002 22:39:32 +0200
Karel Zak [EMAIL PROTECTED] wrote:
- PREPARE_KEY_PREFIX_SIZE is 4 not 3
- in the PrepareKey() is needful fix:
+ if (store == PREPARE_STORE_SHARE) { /* shared between same DB */
+ *flag |= QCF_SHARE_NOTREMOVEABLE;
+ key = (char *) palloc(strlen(name) + PREPARE_KEY_PREFIX_SIZE
+ + strlen(DatabaseName) +1);
^^^
must be 3
+ sprintf(key, %s_%s_, DatabaseName, PREPARE_KEY_PREFIX);
^^
the space for '_' is not allocated :-(
It's my bug probably, I good knew why we need leak detection :-)
Thanks Karel! I made the changes you suggest and the warning (and
the accompanying memory leak) have gone away.
Cheers,
Neil
--
Neil Conway [EMAIL PROTECTED]
PGP Key ID: DB3C29FC
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] command.c breakup
On Sun, 2002-04-14 at 21:58, Rod Taylor wrote: Sounds fair. I'd have brought it up earlier but was away last week. The changes I made are very straight forward and easy enough to redo. I've sent the patch to the -patches list -Please let me know if there are any queries -I will be able to deal with them after ~1700 UTC Monday. Regards John ---(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] [GENERAL] Notify argument?
Here is a patch that implements Tom's suggestion of mallocing the
relation name string as part of PQnotify and not depending on
NAMEDATALEN. Nice trick.
---
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
The breakage will come when we lengthen NAMEDATALEN, which I plan to
tackle for 7.3. We will need to re-order the NOTIFY structure and put
the NAMEDATALEN string at the end of the struct so differing namedatalen
backend/clients will work. If you want to break it, 7.3 would probably
be the time to do it. :-) Users will need a recompile pre-7.3 to use
notify for 7.3 and later anyway.
If we're going to change the structure anyway, let's fix it to be
independent of NAMEDATALEN. Instead of
charrelname[NAMEDATALEN];
int be_pid;
let's do
char *relname;
int be_pid;
This should require no source-level changes in calling C code, thanks
to C's equivalence between pointers and arrays. We can preserve the
fact that freeing a PQnotifies result takes only one free() with a
little hacking to make the string be allocated in the same malloc call:
newNotify = (PGnotify *) malloc(sizeof(PGnotify) + strlen(str) + 1);
newNotify-relname = (char *) newNotify + sizeof(PGnotify);
strcpy(newNotify-relname, str);
Thus, with one line of extra ugliness inside the library, we solve the
problem permanently.
regards, tom lane
--
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
Index: src/interfaces/libpq/fe-exec.c
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v
retrieving revision 1.118
diff -c -r1.118 fe-exec.c
*** src/interfaces/libpq/fe-exec.c 8 Apr 2002 03:48:10 - 1.118
--- src/interfaces/libpq/fe-exec.c 15 Apr 2002 00:15:29 -
***
*** 1510,1517
return EOF;
if (pqGets(conn-workBuffer, conn))
return EOF;
! newNotify = (PGnotify *) malloc(sizeof(PGnotify));
! strncpy(newNotify-relname, conn-workBuffer.data, NAMEDATALEN);
newNotify-be_pid = be_pid;
DLAddTail(conn-notifyList, DLNewElem(newNotify));
return 0;
--- 1510,1525
return EOF;
if (pqGets(conn-workBuffer, conn))
return EOF;
!
! /*
!* Store the relation name right after the PQnotify structure so it can
!* all be freed at once. We don't use NAMEDATALEN because we don't
!* want to tie this interface to a specific server name length.
!*/
! newNotify = (PGnotify *) malloc(sizeof(PGnotify) +
! strlen(conn-workBuffer.data) + 1);
! newNotify-relname = (char *)newNotify + sizeof(PGnotify);
! strcpy(newNotify-relname, conn-workBuffer.data);
newNotify-be_pid = be_pid;
DLAddTail(conn-notifyList, DLNewElem(newNotify));
return 0;
Index: src/interfaces/libpq/libpq-fe.h
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/libpq-fe.h,v
retrieving revision 1.83
diff -c -r1.83 libpq-fe.h
*** src/interfaces/libpq/libpq-fe.h 5 Mar 2002 06:07:26 - 1.83
--- src/interfaces/libpq/libpq-fe.h 15 Apr 2002 00:15:40 -
***
*** 105,112
*/
typedef struct pgNotify
{
! charrelname[NAMEDATALEN]; /* name of relation containing
!*
data */
int be_pid; /* process id of backend */
} PGnotify;
--- 105,111
*/
typedef struct pgNotify
{
! char*relname; /* name of relation containing data */
int be_pid; /* process id of backend */
} PGnotify;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3 schedule
On 13 Apr 2002, Hannu Krosing wrote: On Fri, 2002-04-12 at 03:04, Brian Bruns wrote: On 11 Apr 2002, Hannu Krosing wrote: IIRC someone started work on modularising the network-related parts with a goal of supporting DRDA (DB2 protocol) and others in future. That was me, although I've been bogged down lately, and haven't been able to get back to it. Has any of your modularisation work got into CVS yet ? No, Bruce didn't like the way I did certain things, and had some qualms about the value of supporting multiple wire protocols IIRC. Plus the patch was not really ready for primetime yet. I'm hoping to get back to it soon and sync it with the latest CVS, and clean up the odds and ends. DRDA, btw, is not just a DB2 protocol but an opengroup spec that hopefully will someday be *the* standard on the wire database protocol. DRDA handles prepare/execute and is completely binary in representation, among other advantages. What about extensibility - is there some predefined way of adding new types ? Not really, there is some ongoing standards activity adding some new features. The list of supported types is pretty impressive, anything in particular you are looking for? Also, does it handle NOTIFY ? I don't know the answer to this. The spec is pretty huge, so it may, but I haven't seen it. Even if it is supported as a secondary protocol, I believe there is alot of value in having a single database protocol standard. (why else would I be doing it!). I'm also looking into what it will take to do the same for MySQL and Firebird. Hopefully they will be receptive to the idea as well. Hannu Cheers, Brian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] status report
With my pg_hba.conf changes done, I am now focusing in the next few days on clearing out my email/patch application backlog. -- 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Security Issue..
I'm running into a minor issue with security in regards to users being able to see constructs that they have no access too. The solution? Information_Schema coupled with no direct access to pg_catalog. Internals can use pg_catalog, possibly super users, but regular users shouldn't be able to do any reads / writes to it directly -- as per spec with definition_schema. Anyway, I'd like to start working on the information_schema and converting psql, pg_dump and other tools over to use it. After a couple of releases I'd like to block pg_catalog usage -- perhaps a GUC option? Any thoughts or objections? Obviously the information schema needs (aside from the spec) enough information to allow pg_dump to run. My thought is that if I start now when a large rewrite of clientside applications is required for schema support that there won't be nearly as much backlash later. A number of pg_dump items will be moved into base functions. Trigger statement, type formatting (various view fields). Whats the radix of the numeric, int, etc. types anyway? As a bonus, this adds a layer between the actual system tables and the clients. Might allow changes to be done easier. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Security Issue..
Rod Taylor wrote: I'm running into a minor issue with security in regards to users being able to see constructs that they have no access too. The solution? Information_Schema coupled with no direct access to pg_catalog. Internals can use pg_catalog, possibly super users, but regular users shouldn't be able to do any reads / writes to it directly -- as per spec with definition_schema. Is the problem that people can see system catalog columns that should be more secure? -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ecpg/preproc/preproc.y now generates lots of warnings
Could this get cleaned up please? make[4]: Entering directory `/home/postgres/pgsql/src/interfaces/ecpg/preproc' bison -y -d preproc.y mv -f y.tab.c ./preproc.c mv -f y.tab.h ./preproc.h gcc -O1 -Wall -Wmissing-prototypes -Wmissing-declarations -g -Wno-error -I./../include -I. -I../../../../src/include -DMAJOR_VERSION=2 -DMINOR_VERSION=10 -DPATCHLEVEL=0 -DINCLUDE_PATH=\/home/postgres/testversion/include\ -c -o preproc.o preproc.c preproc.y: In function `yyparse': preproc.y:3844: warning: assignment makes integer from pointer without a cast preproc.y:3906: warning: assignment makes integer from pointer without a cast preproc.y:3914: warning: assignment makes integer from pointer without a cast preproc.y:3922: warning: assignment makes integer from pointer without a cast preproc.y:3930: warning: assignment makes integer from pointer without a cast preproc.y:3944: warning: assignment makes integer from pointer without a cast preproc.y:3952: warning: assignment makes integer from pointer without a cast preproc.y:3960: warning: assignment makes integer from pointer without a cast preproc.y:4100: warning: passing arg 3 of `ECPGmake_struct_type' makes pointer from integer without a cast preproc.y:4102: warning: passing arg 3 of `ECPGmake_struct_type' makes pointer from integer without a cast preproc.y:4449: warning: assignment makes integer from pointer without a cast preproc.y:4540: warning: passing arg 3 of `ECPGmake_struct_type' makes pointer from integer without a cast preproc.y:4542: warning: passing arg 3 of `ECPGmake_struct_type' makes pointer from integer without a cast regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Security Issue..
Rod Taylor writes: The solution? Information_Schema coupled with no direct access to pg_catalog. Internals can use pg_catalog, possibly super users, but regular users shouldn't be able to do any reads / writes to it directly -- as per spec with definition_schema. The catch on this is that privileges on views don't work quite perfectly yet. For instance, if you create a view CREATE VIEW bar AS SELECT * FROM foo; then the statement SELECT * FROM bar; needs privileges to read foo. The privileges would need to be changed to be checked at view creation time. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] Security Issue..
Yes. A number of people in the company have mentioned that our customers can see tables and structures which they shouldn't know exist. Not a severe issue, but it's a checkmark for those wanting to switch to Oracle. Revoking read access to system catalogs causes interesting things to occur :) -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Hackers List [EMAIL PROTECTED] Sent: Sunday, April 14, 2002 9:33 PM Subject: Re: [HACKERS] Security Issue.. Rod Taylor wrote: I'm running into a minor issue with security in regards to users being able to see constructs that they have no access too. The solution? Information_Schema coupled with no direct access to pg_catalog. Internals can use pg_catalog, possibly super users, but regular users shouldn't be able to do any reads / writes to it directly -- as per spec with definition_schema. Is the problem that people can see system catalog columns that should be more secure? -- 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 ---(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] [PATCHES] WITH DELIMITERS in COPY
On Sun, 14 Apr 2002, Bruce Momjian wrote: Gavin, I see where you are going with the patch; creating a list in gram.y and stuffing CopyStmt directly there. However, I can't find any other instance of our stuffing things like that in gram.y. We do have cases using options like COPY in CREATE USER, and we do use DefElem. CREATE DATABASE also fills out a list in the same fashion =). I will however have a look at revising this patch to use DefElem later today. Thanks, Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] WITH DELIMITERS in COPY
Gavin Sherry wrote: On Sun, 14 Apr 2002, Bruce Momjian wrote: Gavin, I see where you are going with the patch; creating a list in gram.y and stuffing CopyStmt directly there. However, I can't find any other instance of our stuffing things like that in gram.y. We do have cases using options like COPY in CREATE USER, and we do use DefElem. CREATE DATABASE also fills out a list in the same fashion =). I will however have a look at revising this patch to use DefElem later today. Oh, I see that now. Which method do people prefer. We should probably make them all use the same mechanism. -- 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 ---(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] Security Issue..
Yeah, I was planning on blocking queries to pg_catalog for all cases. Make it so that it can never be done by any user directly. It would have to be done in the parser before the view was evaluated, and no user created views would be allowed to access pg_catalog. The spec describes the definition schema as accessable only from the information schema. Long term goal of course. It would take a few releases to ensure that everything was setup to be done like that. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Hackers List [EMAIL PROTECTED] Sent: Sunday, April 14, 2002 9:45 PM Subject: Re: [HACKERS] Security Issue.. Rod Taylor writes: The solution? Information_Schema coupled with no direct access to pg_catalog. Internals can use pg_catalog, possibly super users, but regular users shouldn't be able to do any reads / writes to it directly -- as per spec with definition_schema. The catch on this is that privileges on views don't work quite perfectly yet. For instance, if you create a view CREATE VIEW bar AS SELECT * FROM foo; then the statement SELECT * FROM bar; needs privileges to read foo. The privileges would need to be changed to be checked at view creation time. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regexp character class locale awareness patch
Can someone who is multbyte-aware comment on this patch? Thanks. I thought the patch is not relevant to multibyte support? -- Tatsuo Ishii ---(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] Security Issue..
Peter Eisentraut [EMAIL PROTECTED] writes: For instance, if you create a view CREATE VIEW bar AS SELECT * FROM foo; then the statement SELECT * FROM bar; needs privileges to read foo. This works just fine, thank you: the privileges are checked against the owner of the view. The privileges would need to be changed to be checked at view creation time. That would be broken; privileges are and must be checked at query time not view creation time. But having said that, I do not foresee being able to replace direct pg_catalog access with INFORMATION_SCHEMA views anytime soon. There are too many clients out there that are used to doing it that way. Moreover, pg_dump will never be able to work off INFORMATION_SCHEMA, because it needs to get at Postgres-specific information that will not be available from a spec-compliant set of views. I'm fairly dubious about converting psql, even. Rod's welcome to work on developing a set of spec-compliant INFORMATION_SCHEMA views ... and maybe he can even turn off public read access to pg_catalog in his own installation ... but he should not expect us to accept a patch that makes that the default anytime in the foreseeable future. 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])
[HACKERS] That CREATE OPERATOR CLASS patch
If Bruce is thinking of applying outstanding patches - whatever happened with Bill Studenmund's CREATE OPERATOR CLASS patch? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] regexp character class locale awareness patch
Tatsuo Ishii wrote: Can someone who is multbyte-aware comment on this patch? Thanks. I thought the patch is not relevant to multibyte support? Sorry, yes, it is for locale. -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Security Issue..
Tom Lane wrote: But having said that, I do not foresee being able to replace direct pg_catalog access with INFORMATION_SCHEMA views anytime soon. There are too many clients out there that are used to doing it that way. Moreover, pg_dump will never be able to work off INFORMATION_SCHEMA, because it needs to get at Postgres-specific information that will not be available from a spec-compliant set of views. I'm fairly dubious about converting psql, even. Rod's welcome to work on developing a set of spec-compliant INFORMATION_SCHEMA views ... and maybe he can even turn off public read access to pg_catalog in his own installation ... but he should not expect us to accept a patch that makes that the default anytime in the foreseeable future. Yes, it would be nice to have spec-compliant stuff. However, things like psql really get into those catalogs and grab detailed information that is probably not covered the the spec. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Security Issue..
For the non-spec compliant stuff, I was going to add various pg_ views to accomodate it, but with the spirit of the spec. That is, users can only see catalog entries which they have access to, and can only view definitions of entries that they have ownership of. Anyway, I got the feedback I wanted so I'll start puttering away at it. Theres a number of minor things missing or slightly out of whack which I hope to add as well. Timestamps on trigger creation, access levels on data types, etc. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Peter Eisentraut [EMAIL PROTECTED]; Rod Taylor [EMAIL PROTECTED]; Hackers List [EMAIL PROTECTED] Sent: Sunday, April 14, 2002 10:38 PM Subject: Re: [HACKERS] Security Issue.. Tom Lane wrote: But having said that, I do not foresee being able to replace direct pg_catalog access with INFORMATION_SCHEMA views anytime soon. There are too many clients out there that are used to doing it that way. Moreover, pg_dump will never be able to work off INFORMATION_SCHEMA, because it needs to get at Postgres-specific information that will not be available from a spec-compliant set of views. I'm fairly dubious about converting psql, even. Rod's welcome to work on developing a set of spec-compliant INFORMATION_SCHEMA views ... and maybe he can even turn off public read access to pg_catalog in his own installation ... but he should not expect us to accept a patch that makes that the default anytime in the foreseeable future. Yes, it would be nice to have spec-compliant stuff. However, things like psql really get into those catalogs and grab detailed information that is probably not covered the the spec. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] That CREATE OPERATOR CLASS patch
Good question. I see the thread at: http://groups.google.com/groups?hl=enthreadm=Pine.LNX.4.30.0202262002040.685-10%40peter.localdomainrnum=2prev=/groups%3Fq%3Dcreate%2Boperator%2Bgroup:comp.databases.postgresql.*%26hl%3Den%26selm%3DPine.LNX.4.30.0202262002040.685-10%2540peter.localdomain%26rnum%3D2 I asked the author to resumit but did not see a reply. Perhaps someone else can take it over and make the requested changes. Thanks. --- Christopher Kings-Lynne wrote: If Bruce is thinking of applying outstanding patches - whatever happened with Bill Studenmund's CREATE OPERATOR CLASS patch? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- 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 ---(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] RFC: Restructuring pg_aggregate
Christopher Kings-Lynne wrote: Also, it seems to me that at some point we are forced to break client compatibility. It's not a users' consensus at all. I'm suspicious if DROP COLUMN is such a significant feature to break client compatibility at our ease. Either we add attisdropped field to pg_attribute, or we use Hiroshi's (-1 * attnum - offset) idea. Both Tom and Hiroshi have good reasons for each of these - would it be possible for you guys to post with your reasons for and against both the techniques. I don't object to adding attisdropped field. What I meant to say is that the differene is very small. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] ANSI Compliant Inserts
I submitted a patch which would make Postgresql ANSI compliant in
regards to INSERT with a provided column list. As Tom states below,
this is not full compliance.
CREATE TABLE tab(col1 text, col2 text);
INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced
by patch)
INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good
INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good
Currently in postgres all of the above are valid. I'd like to rule
out the first case (as enforced by the patch) as it's obvious the user
had intended to have two values. Especially useful when the user
misses a value and inserts bad data into the table as a result.
For the latter one, it could be argued that the user understands the
table in question and has inserted the values they require. New
columns are added at the end, and probably don't affect the operation
in question so why should it be changed to suit new columns? But,
automated code should always be written with the columns explicitly
listed, so this may be a user who has simply forgotten to add the
value -- easy to do on wide tables.
Thoughts?
--
Rod Taylor
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Bruce Momjian [EMAIL PROTECTED]
Cc: Rod Taylor [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, April 14, 2002 11:49 PM
Subject: Re: [PATCHES] ANSI Compliant Inserts
Bruce Momjian [EMAIL PROTECTED] writes:
Do you want to argue we should continue allowing it?
No; I'm objecting that there hasn't been adequate discussion about
this change of behavior.
BTW, if the rationale for the change is ANSI compliance then the
patch
is still wrong. SQL92 says:
3) No column name of T shall be identified more than
once. If the
insert column list is omitted, then an insert column
list
that identifies all columns of T in the ascending
sequence of
their ordinal positions within T is implicit.
5) Let QT be the table specified by the query expression.
The
degree of QT shall be equal to the number of column
names in
the insert column list.
The patch enforces equality only for the case of an explicit insert
column list --- which is the behavior I suggested in the original
comment, but the spec clearly requires an exact match for an
implicit
list too. How tight do we want to get?
In any case this discussion should be taking place someplace more
public
than -patches.
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] [PATCHES] ANSI Compliant Inserts
[ Discussion moved to hackers.] We are discussing TODO item: o Disallow missing columns in INSERT ... VALUES, per ANSI Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Do you want to argue we should continue allowing it? No; I'm objecting that there hasn't been adequate discussion about this change of behavior. So, you don't want to allow it, I don't want to allow it, the patch author doesn't want to allow it. The reason the item doesn't require much discussion is that I can't imagine anyone arguing we should allow it. If there is anyone out there that doesn't want the listed TODO item completed, please chime in now. BTW, if the rationale for the change is ANSI compliance then the patch is still wrong. SQL92 says: 3) No column name of T shall be identified more than once. If the insert column list is omitted, then an insert column list that identifies all columns of T in the ascending sequence of their ordinal positions within T is implicit. 5) Let QT be the table specified by the query expression. The degree of QT shall be equal to the number of column names in the insert column list. The patch enforces equality only for the case of an explicit insert column list --- which is the behavior I suggested in the original comment, but the spec clearly requires an exact match for an implicit list too. How tight do we want to get? Yes, I think we want both implicit and explicit column names to match the VALUES list. We do have DEFAULT for INSERT now, so that should make things somewhat easier for people wanting to insert DEFAULT values without specifying the column list. -- 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 ---(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] [PATCHES] ANSI Compliant Inserts
Tom Lane wrote:
Rod Taylor [EMAIL PROTECTED] writes:
CREATE TABLE tab(col1 text, col2 text);
INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced
by patch)
INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good
INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good
Currently in postgres all of the above are valid. I'd like to rule
out the first case (as enforced by the patch) as it's obvious the user
had intended to have two values.
Seems reasonable.
For the latter one, it could be argued that the user understands the
table in question and has inserted the values they require.
Ruling out this case would break a technique that I've used a lot in the
past, which is to put defaultable columns (eg, SERIAL columns) at the
end, so that they can simply be left out of quick manual inserts.
So I agree with this part too. (I wouldn't necessarily write
application code that way, but then I believe in the theory that robust
application code should always specify an explicit column list.)
Yes, I understand the tempation to put the columns needing default at
the end and skipping them on INSERT. However, our new DEFAULT insert
value seems to handle that nicely, certainly better than the old code
did, and I think the added robustness of now requiring full columns on
INSERT is worth it.
I realize this could break some apps, but with the new DEFAULT value, it
seems like a good time to reign in this error-prone capability.
--
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] ANSI Compliant Inserts
Rod Taylor wrote: For the latter one, it could be argued that the user understands the table in question and has inserted the values they require. New columns are added at the end, and probably don't affect the operation in question so why should it be changed to suit new columns? But, automated code should always be written with the columns explicitly listed, so this may be a user who has simply forgotten to add the value -- easy to do on wide tables. I think our new DEFAULT for insert allows people to properly match all columns, and I think it is too error prone to allow missing columns in any INSERT. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] regexp character class locale awareness patch
Whatever you do with this patch, remember that the USE_LOCALE symbol is gone. Bruce Momjian writes: Can someone who is multbyte-aware comment on this patch? Thanks. --- Manuel Sugawara wrote: Attached is a pacth against 7.2 which adds locale awareness to the character classes of the regular expression engine. Please consider including this feature to postgreSQL. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] 7.3 schedule
On Thu, 11 Apr 2002, Barry Lind wrote: I'm not sure that JDBC would use this feature directly. When a PreparableStatement is created in JDBC there is nothing that indicates how many times this statement is going to be used. Many (most IMHO) will be used only once. Well, the particular PreparedStatement instance may be used only once, yes. But it's quite likely that other, identical PreparedStatement objects would be used time and time again, so it's still good if you don't need to do much work on the second and subsequent preparations of that statement. If it only is used once, it will actually perform worse than without the feature (since you need to issue two sql statements to the backend to accomplish what you were doing in one before). I'm not sure that it would be much worse unless you need to wait for an acknowledgement from the back-end for the first statement. If you had a back-end command along the lines of prepare this statement and execute it with these parameters, it would have pretty much the same performance as giving the statement directly with the parameters already substituted in, right? Thus if someone wanted to use this functionality from jdbc they would need to do it manually, i.e. issue the prepare and execute statements manually instead of the jdbc driver doing it automatically for them. I'd say that this is awfully frequent, anyway. I use PreparedStatements for pretty much any non-constant input, because it's just not safe or portable to try to escape parameters yourself. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug with current sources? Re: cost of parse/plan/execute
Tom, OK here is a test case: create table test1 (t1a int); create table test2 (t2a int); create table test3 (t3a int); SELECT x2.t2a FROM ((test1 t1 LEFT JOIN test2 t2 ON (t1.t1a = t2.t2a)) AS x1 LEFT OUTER JOIN test3 t3 ON (x1.t2a = t3.t3a)) AS x2 WHERE x2.t2a = 1; The select works under 7.2, but gives the following error in 7.3: ERROR: JOIN/ON clause refers to x1, which is not part of JOIN thanks, --Barry Tom Lane wrote: Barry Lind [EMAIL PROTECTED] writes: In testing Neil's PREPARE/EXECUTE patch on my test query, I found the parser complains that this query is not valid when using current sources. The error I get is: psql:testorig.sql:1: ERROR: JOIN/ON clause refers to xf2, which is not part of JOIN Hmm. I have an open bug with sub-SELECTs inside a JOIN, but this example doesn't look like it would trigger that. I think the sql is valid (at least it has worked in 7.1 and 7.2). Is this a bug? Dunno. Give me a test case (and no, I am *not* going to try to reverse-engineer table schemas from that SELECT). 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] [PATCHES] ANSI Compliant Inserts
Peter Eisentraut wrote:
Rod Taylor writes:
I submitted a patch which would make Postgresql ANSI compliant in
regards to INSERT with a provided column list. As Tom states below,
this is not full compliance.
CREATE TABLE tab(col1 text, col2 text);
INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced
by patch)
INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good
INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good
I recall that this was the behavior we agreed we wanted. IMHO, it would
be conditional on the INSERT ... VALUES (DEFAULT) capability being
provided. I'm not sure if that is there yet.
Yes, it is key to have DEFAULT working before we change this, and it is
in CVS now, committed a week or two ago.
Peter, are you saying you don't want to require all columns to be
specified when INSERT doesn't list the columns?
--
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
---(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] [PATCHES] ANSI Compliant Inserts
Bruce Momjian writes: Peter, are you saying you don't want to require all columns to be specified when INSERT doesn't list the columns? Yes, that's what I'm saying. Too much breakage and annoyance potential in that change. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bug with current sources? Re: cost of parse/plan/execute for one sample query
Barry Lind [EMAIL PROTECTED] writes: OK here is a test case: Looks like a bug, all right --- I must have introduced this when I redid the handling of JOIN aliases a few weeks ago. Will fix. Thanks for the report. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] ANSI Compliant Inserts
Peter Eisentraut [EMAIL PROTECTED] writes: I recall that this was the behavior we agreed we wanted. IMHO, it would be conditional on the INSERT ... VALUES (DEFAULT) capability being provided. I'm not sure if that is there yet. That is there now. Do you recall when this was discussed before? I couldn't remember if there'd been any real discussion or not. 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] [PATCHES] ANSI Compliant Inserts
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Ruling out this case would break a technique that I've used a lot in the past, which is to put defaultable columns (eg, SERIAL columns) at the end, so that they can simply be left out of quick manual inserts. Yes, I understand the tempation to put the columns needing default at the end and skipping them on INSERT. However, our new DEFAULT insert value seems to handle that nicely, certainly better than the old code did, and I think the added robustness of now requiring full columns on INSERT is worth it. If I have two or three defaultable columns (say, a SERIAL primary key and an insertion timestamp), it's going to be a pain in the neck to have to write DEFAULT, DEFAULT, ... at the end of every insert. I feel that people who want error cross-checking on this will have used an explicit column list anyway. Therefore, Rod's patch tightens the case that should be tight, while still being appropriately loose for casual manual inserts. BTW, I do *not* agree with equating this case with COPY. COPY is mostly used for loading dumped data, and so it's reasonable to make different tradeoffs between error checking and friendliness for COPY and INSERT. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] ANSI Compliant Inserts
Rod Taylor [EMAIL PROTECTED] writes:
CREATE TABLE tab(col1 text, col2 text);
INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced
by patch)
INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good
INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good
Currently in postgres all of the above are valid. I'd like to rule
out the first case (as enforced by the patch) as it's obvious the user
had intended to have two values.
Seems reasonable.
For the latter one, it could be argued that the user understands the
table in question and has inserted the values they require.
Ruling out this case would break a technique that I've used a lot in the
past, which is to put defaultable columns (eg, SERIAL columns) at the
end, so that they can simply be left out of quick manual inserts.
So I agree with this part too. (I wouldn't necessarily write
application code that way, but then I believe in the theory that robust
application code should always specify an explicit column list.)
For the record --- I actually am in favor of this patch; but I wanted
to see the change discussed and defended in a more widely-read mailing
list than -patches. If there are no objections from the assembled
hackers, apply away ...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] 7.3 schedule
Curt Sampson wrote: On Thu, 11 Apr 2002, Barry Lind wrote: I'm not sure that JDBC would use this feature directly. When a PreparableStatement is created in JDBC there is nothing that indicates how many times this statement is going to be used. Many (most IMHO) will be used only once. Well, the particular PreparedStatement instance may be used only once, yes. But it's quite likely that other, identical PreparedStatement objects would be used time and time again, so it's still good if you don't need to do much work on the second and subsequent preparations of that statement. But since the syntax for prepare is: PREPARE name AS statement you can't easily reuse sql prepared by other PreparedStatement objects since you don't know if the sql you are about to execute has or has not yet been prepared or what name was used in that prepare. Thus you will always need to do a new prepare. (This only is true if the driver is trying to automatically use PREPARE/EXECUTE, which was the senario I was talking about). If it only is used once, it will actually perform worse than without the feature (since you need to issue two sql statements to the backend to accomplish what you were doing in one before). I'm not sure that it would be much worse unless you need to wait for an acknowledgement from the back-end for the first statement. If you had a back-end command along the lines of prepare this statement and execute it with these parameters, it would have pretty much the same performance as giving the statement directly with the parameters already substituted in, right? I didn't say it would be much worse, but it won't be faster than not using PREPARE. Thus if someone wanted to use this functionality from jdbc they would need to do it manually, i.e. issue the prepare and execute statements manually instead of the jdbc driver doing it automatically for them. I'd say that this is awfully frequent, anyway. I use PreparedStatements for pretty much any non-constant input, because it's just not safe or portable to try to escape parameters yourself. I agree this is useful, and you can write user code to take advantage of the functionality. I am just pointing out that I don't think the driver can behind the scenes use this capability automatically. --Barry ---(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] [PATCHES] ANSI Compliant Inserts
INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good
I recall that this was the behavior we agreed we wanted. IMHO, it
would
be conditional on the INSERT ... VALUES (DEFAULT) capability being
provided. I'm not sure if that is there yet.
My patch for that was applied a couple weeks ago.
---(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] [PATCHES] ANSI Compliant Inserts
Rod Taylor writes:
I submitted a patch which would make Postgresql ANSI compliant in
regards to INSERT with a provided column list. As Tom states below,
this is not full compliance.
CREATE TABLE tab(col1 text, col2 text);
INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced
by patch)
INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good
INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced)
INSERT INTO tab VALUES ('val1', 'val2'); -- good
I recall that this was the behavior we agreed we wanted. IMHO, it would
be conditional on the INSERT ... VALUES (DEFAULT) capability being
provided. I'm not sure if that is there yet.
--
Peter Eisentraut [EMAIL PROTECTED]
---(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] Security Issue..
Tom Lane writes: Peter Eisentraut [EMAIL PROTECTED] writes: For instance, if you create a view CREATE VIEW bar AS SELECT * FROM foo; then the statement SELECT * FROM bar; needs privileges to read foo. This works just fine, thank you: the privileges are checked against the owner of the view. OK, nevermind. The case I was referring to was that the CREATE VIEW statement succeeds and the privileges are checked when the view is queried. This is not in compliance with SQL, but it doesn't seem to matter that much. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.3 schedule
On Sun, 14 Apr 2002, Barry Lind wrote: But since the syntax for prepare is: PREPARE name AS statement you can't easily reuse sql prepared by other PreparedStatement objects since you don't know if the sql you are about to execute has or has not yet been prepared or what name was used in that prepare. Thus you will always need to do a new prepare. (This only is true if the driver is trying to automatically use PREPARE/EXECUTE, which was the senario I was talking about). Well, there are some ugly tricks you could build into the driver to allow it to effectively use a PREPAREd statement with multiple, identical PreparedStatement objects (basically, via the driver caching various things and identifying PreparedStatements created with the same SQL), but it's messy enough and has some problems hard enough to resolve that I can't actually see this being practical. I was actually just wanting to point out that this is where automatic caching on the server shines. If it only is used once, it will actually perform worse I didn't say it would be much worse, but it won't be faster than not using PREPARE. Well, if it's not faster, that's fine. If it's worse, that's not so fine, because as you point out there's really no way for the driver to know whether a PreparedStatement is being used just for speed (multiple queries with one instance) or security (on query, but with parameters). I am just pointing out that I don't think the driver can behind the scenes use this capability automatically. Well, if there's little or no performance impact, I would say that the driver should always use this capability with PreparedStatement objects. If there is a performance impact, perhaps a property could turn it on and off? cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bug with current sources? Re: cost of parse/plan/execute for one sample query
Barry Lind [EMAIL PROTECTED] writes: The select works under 7.2, but gives the following error in 7.3: ERROR: JOIN/ON clause refers to x1, which is not part of JOIN I've committed a fix for this. Thanks again. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] regexp character class locale awareness patch
Whatever you do with this patch, remember that the USE_LOCALE symbol is gone. I thought we have some way to tern off locale support at the configure time. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
