[HACKERS] FW: indirect dereferencing a field in a record using plpgsql
Hi, I had a problem where I needed to indirectly dereference a field from a record. To illustrate: CREATE FUNCTION test2() returns bool as ' DECLARE myrec record; fld text; BEGIN select ''hello'' as a, ''world'' as b into myrec; fld = ''a''; -- the fieldname from myrec that we are interested in raise exception ''the result is % %'', myrec._fld, myrec.b; return true; END; ' language 'plpgsql2'; Typically, dereferencing is done by specifying the fieldname that was obtained either by a previous query as illustrated above, or in predefined structures such as in the cases of triggers with the NEW and OLD records. The problem I had was that the functionality described above was not available. Building up a query string and then using the 'EXECUTE' functionality also does not work as I think a new context is created and the 'NEW' and 'OLD' records are not available - or they are part of the plpgsql language and not available via the methods when calling EXECUTE. Requirements for such functionality is probably quite limited in general. Most applications you would know what columns you are dealing with, but the above requirement could add some flexability. I noticed this was possible to do under pltcl, but plpgsql did not support this. Would this be something worth adding to plpgsql? Anyways, I decided to hack the plpgsql language a little - for my testing I created a seperate language 'plpgsql2' which had my hack. The patch I made was on the pl_exec.c file on the postgresql 7.3.2 source - the diff of the original to my hack is listed below. I realized I still have a problem. Apparently there are issues with types with regards to the planner. I only discovered this when I tried accessing the NEW and OLD records in a trigger. The problem that arose was that the type that is being dereferenced is not necessarily what is expected when compared with the type that the planner planned. The problem I faced was in the function exec_stmt_execsql(): if (expr-plan_argtypes[i] != SPI_gettypeid(rec-tupdesc, fno)) elog(ERROR, type of %s.%s doesn't match that when preparing the plan, rec-refname, recfield-fieldname); I have to spend some more time understanding the exact ADT that is created by the parser. Possibly my quick hack may not work if the parser predetermines the type as the functionality required would only really be able to determine the type at time of execution. If anyone can give some insight to help make the functionality work - would be appreciated. I'll have to spend some more time figuring out the internals I guess. :) Thanks, Conrad Vermeulen [EMAIL PROTECTED] To briefly comment the extenstion I added: Normal field dereferencing is done by 'record.field' where 'field' is part of the 'record'. In cases where 'indirect dereferencing' is required, the 'field' is actually a variable which needs to be dereferenced first. In order to differenciate between a normal record field, and a global field, my extension requires a '_' before the field name to identify that this process is required. The use of the '_' was because the parser tokenized other tokens such as $ or % and I did not want to patch any other code. # diff pl_exec.orig.c pl_exec.c 1737a1738,1772 #define DYNAMDTYPE_RECFIELD \ if (fno == SPI_ERROR_NOATTRIBUTE) \ \ int j; \ char *fld; \ if (recfield-fieldname[0] == '_') \ \ fld= recfield-fieldname+1; \ for (j=0;jestate-ndatums; j++) \ \ var = (PLpgSQL_var *) (estate-datums[j]); \ if ((estate-datums[j]-dtype == PLPGSQL_DTYPE_VAR)\ (!strcmp(fld,var-refname))) \ \ if (var-isnull ) \ \ elog(ERROR, \%s\ found but not assigned for use by \%s\, fld, rec-refname); \ break; \ } \ fld=DatumGetCString(DirectFunctionCall1(textout,var-value)); \ fno = SPI_fnumber(rec-tupdesc, fld);\ pfree(fld); \ if (fno != SPI_ERROR_NOATTRIBUTE) \ break; \ } \ } \ } \ if (fno == SPI_ERROR_NOATTRIBUTE) \ elog(ERROR, record \%s\ has no field named \%s\, rec-refname, recfield-fieldname); \ } 1821,1822c1856 if (fno == SPI_ERROR_NOATTRIBUTE) elog(ERROR, record \%s\ has no field named \%s\, rec-refname,
[HACKERS] dump cache summary
As part of attempting to gain an understanding of how Postgres works, I wanted to see if I could display a summary of what relations were using pages in the cache. Having done that, I was all set to trash the code when I wondered if it might be useful in its own right... Here is a sample of the output after a pgbench run with shared_buffers=500: bench=dumpcache; CACHE DUMP - pg_type_oid_index 2 pg_proc 1 history 3 pg_class_relname_nsp_index5 pg_statistic_relid_att_index 2 tellers_pkey 9 invalid oid 4 accounts205 pg_am 1 pg_statistic 1 branches 2 accounts_pkey 215 pg_amproc_opc_procnum_index 2 branches_pkey 9 pg_operator_oid_index 3 tellers 4 pg_class_oid_index2 pg_type 2 pg_index_indexrelid_index 2 pg_trigger_tgrelid_tgname_index 2 pg_amop_opr_opc_index 2 pg_index_indrelid_index 2 pg_class 2 pg_trigger1 pg_proc_oid_index 1 pg_amop_opc_strategy_index2 pg_attribute_relid_attnum_index 4 pg_amop 1 pg_amproc 1 pg_index 2 pg_operator 3 pg_attribute 3 (32 rows) bench= Does this seem like a useful thing to be able to display ? I implemented this by adding a command (as I wanted to know how this was done), but I suspect it would make more sense to use a function a bit like the pg_stat* collection. I have included (most of) the code I used, so that interested parties can show me what I have done wrong :-) (Some things I wondered about were : should I be locking buffers before peeking at the corresponding descriptors?, does it make sense to call RelationIdGetRelation on a relNode? ) regards Mark dumpcache.tar.gz Description: application/macbinary ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] mcxt.c
Tom Lane [EMAIL PROTECTED] wrote:
Mendola Gaetano [EMAIL PROTECTED] writes:
A test for null string is missing here:
MemoryContextStrdup(MemoryContext context, const char *string)
{
char *nstr;
-
- if ( !string )
- {
- elog(ERROR, MemoryContextStrdup called with a NULL pointer);
- return NULL;
- }
This seems inappropriate to me. Are you going to suggest that every
routine that takes a pointer parameter needs to explicitly test for
null? We could bloat the code a great deal that way, and slow it down,
without gaining anything at all in debuggability (IMHO anyway).
Of course I'm not suggesting this, what I'm suggesting is put an
assert( ) if the test can slow down the performances and an if ( )
in places that are not going to touch the performances.
I think that is reasonable.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[HACKERS] on-disk format changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. Will there be any more on-disk format changes before 7.4 goes final which will require a dump-restore, or is that impossible to say? - -- Andreas Joseph Krogh [EMAIL PROTECTED] Managing Director, Senior Software Developer OfficeNet AS - - There are 10 kinds of people in the world, those that can do binary arithmetic and those that can't. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/XHn1UopImDh2gfQRAlu7AJ42E+UX3LcjaZXMHo0KSz+clDXEAACfTHPX OSoPmCxxhN3OOXWEAh1e3SQ= =cc5z -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] on-disk format changes
On Mon, Sep 08, 2003 at 14:45:41 +0200, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: Will there be any more on-disk format changes before 7.4 goes final which will require a dump-restore, or is that impossible to say? While it is impossible to say with 100% certainly, the developers generally try to avoid requiring initdbs after the first beta and especially after later betas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] on-disk format changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 08 September 2003 15:27, Bruno Wolff III wrote: On Mon, Sep 08, 2003 at 14:45:41 +0200, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: Will there be any more on-disk format changes before 7.4 goes final which will require a dump-restore, or is that impossible to say? While it is impossible to say with 100% certainly, the developers generally try to avoid requiring initdbs after the first beta and especially after later betas. That's what I thought. I remember from the 7.3 beta-period that it broke between beta2 and beta3 or so and am wondering if the developers are aware of any known issues which might require an initdb after 7.4beta2. Lets hope not:-) - -- Andreas Joseph Krogh [EMAIL PROTECTED] Managing Director, Senior Software Developer OfficeNet AS - - There are 10 kinds of people in the world, those that can do binary arithmetic and those that can't. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/XIM4UopImDh2gfQRAh6aAJ91T5oaU8X8Jac8Jq42qgyMv5NnCQCeORIa rr6pTlyA1HMNuEvdd+kzkzo= =93Bk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] on-disk format changes
On Mon, Sep 08, 2003 at 19:13:47 +0530, Shridhar Daithankar [EMAIL PROTECTED] wrote: On 8 Sep 2003 at 8:42, Bruno Wolff III wrote: If that was hash index, Tom suggested to bump version number of the indexes in later versions and throw an error and ask user to reindex that particular index. This was good enogh solution to avoid initdb IIRC. That was the one I was thinking of. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] mcxt.c
Gaetano Mendola [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: This seems inappropriate to me. Are you going to suggest that every routine that takes a pointer parameter needs to explicitly test for null? Of course I'm not suggesting this, what I'm suggesting is put an assert( ) if the test can slow down the performances and an if ( ) in places that are not going to touch the performances. I see no value at all in an assert. The code will dump core just fine with or without an assert ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] plpgsql debugging
The current implementation of plpgsql lacks some details that makes programming really hard: there's no language validator to check the code when creating the function, and there's support to debug the code. Before I start hacking on this, I'd like to share my thoughts. Looking at the code, I think that a validator could be added quite soon. The PLpgSQL_execstate struct could be extended by a validation_active bool flag, which changes the behaviour of all exec_stmt_XXX routines. The validator primarily executes the function, with that flag to TRUE, forcing all conditional statements to execute all execution paths exactly once, and sql statements being parsed. Debugging is much harder. There are two levels of debugging thinkable: full-blown stepping with breakpoints etc while running in the backend, and the small version having an intelligent console which simulates a backend understanding plpgsql language natively, so you can test the code by executing single blocks of code one after another (i.e not storing the function, but selectively executing parts of the function definition). Backend debugging seems not possible the way the code is structured now. The execution path is stored on the backend program stack, so it's virtually impossible to interrupt the execution at a point for later continuation. The backend's flow of execution is identical with plpgsql's, interrupting plpgsql means stopping the backend. Frontend debugging seems more feasible, by offering some functions, that enables a caller to initialize variables, call a statement block (that's compiled immediately, executed and discarded), and retrieve all vars. Any thoughts about this? More caveats I haven't seen? Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] plpgsql debugging
On Monday 08 September 2003 15:14, Andreas Pflug wrote: Looking at the code, I think that a validator could be added quite soon. The PLpgSQL_execstate struct could be extended by a validation_active bool flag, which changes the behaviour of all exec_stmt_XXX routines. The validator primarily executes the function, with that flag to TRUE, forcing all conditional statements to execute all execution paths exactly once, and sql statements being parsed. Sounds good. Debugging is much harder. There are two levels of debugging thinkable: full-blown stepping with breakpoints etc while running in the backend, and the small version having an intelligent console which simulates a backend understanding plpgsql language natively, so you can test the code by executing single blocks of code one after another (i.e not storing the function, but selectively executing parts of the function definition). Actually, a simple trace ability would be a huge step forward. It'd save me dotting RAISE statements around my functions while I write them. Even the ability to add DEBUG statements that checked some global flag before firing would be very useful (to me at least). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] plpgsql debugging
Richard Huxton wrote: Actually, a simple trace ability would be a huge step forward. It'd save me dotting RAISE statements around my functions while I write them. Sounds bloody familiar... :-( Even the ability to add DEBUG statements that checked some global flag before firing would be very useful (to me at least). I could imagine a DEBUG which works like RAISE NOTICE with checking for a set variable, and ASSERT which works like RAISE ERROR. Does anybody expect keyword conflicts from this? Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pgsql vc++|win32
Hi guys, I'm planning with some friends to develop a port of pgsql, to native win32 environment using vc++; We read about MingW choice of official dev team, and we comprise their worries but we think that best results could be obtained using some really native win32 libraries (for better or for worse =/) where can I access latest dev source code and dev docs in the/from CVS ? Thanks Regards Luke ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] row level lock and table level locks
HI, I found out that TupleTable stores per-tuple information(it stores HeapTupleData) and that also there are multiple TupleTables in the db at a time.Based on what are diffrent TupleTables created? thank you Jenny From: Larry Douzie [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [HACKERS] row level lock and table level locks Date: Sun, 7 Sep 2003 21:05:49 -0700 (PDT) Larry Douzie writes: Is there a array or some sort of datastructures that store all the HeapTupleDatas for all rows in the db? Er, wouldn't that be the database files? What i mean is, whats the pointer name to this list of HeapTupleDatas? Tom Lane [EMAIL PROTECTED] wrote: c regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software _ Express yourself with MSN Messenger 6.0 -- download now! http://www.msnmessenger-download.com/tracking/reach_general ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. I'd opt for 2. It's quite common that newer compilers will detect more bogus coding than older ones. There might be existing functions that break from this because they rely on the current feature, but there are probably others that will throw an exception, revealing bad coding (and delivering correct results just by chance, I've seen this more than once...) Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: 2. Throw an error if the expression doesn't return boolean. I'd opt for 2. It's quite common that newer compilers will detect more bogus coding than older ones. There might be existing functions that break from this because they rely on the current feature, but there are probably others that will throw an exception, revealing bad coding (and delivering correct results just by chance, I've seen this more than once...) I agree, and option 2 also makes sure that bad code will fail cleanly, rather than possibly changing behavior and causing data loss/corruption. I agree with another poster that deprecation in 7.4 and removal in 7.5 might make sense. -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Doug McNaught [EMAIL PROTECTED] writes: I agree with another poster that deprecation in 7.4 and removal in 7.5 might make sense. How would we deprecate it exactly? Throw a NOTICE? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] row level lock and table level locks
Jenny - [EMAIL PROTECTED] writes: I found out that TupleTable stores per-tuple information(it stores HeapTupleData) and that also there are multiple TupleTables in the db at a time.Based on what are diffrent TupleTables created? TupleTables are just temporary data structures to hold transiently created tuples during execution of a query. There's usually one for each plan node. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FreeBSD/i386 thread test
Jeroen Ruigrok/asmodai wrote: -On [20030908 06:32], Bruce Momjian ([EMAIL PROTECTED]) wrote: Your gethostbyname() is _not_ thread-safe Your getpwuid() is _not_ thread-safe Your functions are _not_ all thread-safe Interesting. Do you have all the *_r files listed in thread.c? I sure hope so. I assume you used the template thread compile flags for this test. Both gethostbyname() and getpwuid() have no _r equivalents on FreeBSD-STABLE, ergo no thread-safe functions of these. So you don't have all the *_r functions, and your non-*_r functions aren't thread-safe. Should we disable theading on FreeBSD? Seems so. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane [EMAIL PROTECTED] writes: Doug McNaught [EMAIL PROTECTED] writes: I agree with another poster that deprecation in 7.4 and removal in 7.5 might make sense. How would we deprecate it exactly? Throw a NOTICE? I was thinking of just a mention in the release notes that we've found this problem and intend to fix it after giving people a release cycle to adjust. Not that people read release notes... :( -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Doug McNaught [EMAIL PROTECTED] writes: I agree with another poster that deprecation in 7.4 and removal in 7.5 might make sense. How would we deprecate it exactly? Throw a NOTICE? Good question. A NOTICE just might be ignored, breaking everything surprisingly in 7.5. To speak a bit more general, how about some sort of deprecation checker setting, if set to true anything marked as deprecated will throw an error, if false only a notice will be generated. This would enable users to check their existing stuff for future compatibility before it's broken in the next release. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] mcxt.c
Date: Mon, 08 Sep 2003 09:57:30 -0400 From: Tom Lane [EMAIL PROTECTED] Gaetano Mendola [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: This seems inappropriate to me. Are you going to suggest that every routine that takes a pointer parameter needs to explicitly test for null? Of course I'm not suggesting this, what I'm suggesting is put an assert( ) if the test can slow down the performances and an if ( ) in places that are not going to touch the performances. I see no value at all in an assert. The code will dump core just fine with or without an assert ... What if define that if() as a macro? This would avoid the code bloat and allow the paranoid users have the check if they want to. In analogy to --cassert and --debug, one could add a --null-paranoid option :) that would make that macro defined. That would be no slowdown for non-paranoids and a friendly error reporting for paranoids. Though I'm not sure if it is worthwhile of maintenance effort and falling back onto core dump would always work. -s ---(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] row level lock and table level locks
TupleTables are just temporary data structures to hold transiently created tuples during execution of a query. There's usually one for each plan node. whats a 'plan node'? From: Tom Lane [EMAIL PROTECTED] To: Jenny - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [HACKERS] row level lock and table level locks Date: Mon, 08 Sep 2003 12:49:51 -0400 Jenny - [EMAIL PROTECTED] writes: I found out that TupleTable stores per-tuple information(it stores HeapTupleData) and that also there are multiple TupleTables in the db at a time.Based on what are diffrent TupleTables created? TupleTables are just temporary data structures to hold transiently created tuples during execution of a query. There's usually one for each plan node. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster _ Compare Cable, DSL or Satellite plans: As low as $29.95. https://broadband.msn.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] FreeBSD/i386 thread test
-On [20030908 18:52], Bruce Momjian ([EMAIL PROTECTED]) wrote: So you don't have all the *_r functions, and your non-*_r functions aren't thread-safe. Should we disable theading on FreeBSD? Seems so. Exactly. Most other threading works though. :) -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ The only source of knowledge is experience... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] row level lock and table level locks
On Mon, Sep 08, 2003 at 10:17:46AM -0700, Jenny - wrote: TupleTables are just temporary data structures to hold transiently created tuples during execution of a query. There's usually one for each plan node. whats a 'plan node'? Have you tried reading the documentation, source code and the slides of Tom and Bruce's presentations? They all are very valuable resources. I believe there's a good set of slides by Bruce in http://developer.postgresql.org that explain these things in a general manner. Armed with that you can try to read the source code. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) I dream about dreams about dreams, sang the nightingale under the pale moon (Sandman) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Doug McNaught [EMAIL PROTECTED] writes: I agree with another poster that deprecation in 7.4 and removal in 7.5 might make sense. How would we deprecate it exactly? Throw a NOTICE? Release notes, I guess. A NOTICE would be fine as long as it didn't result in a flood of them. If that happened once at parse time that should be fine, I think. What's the practice in deprecating other features? cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] constraint modification on todo list
Hi people, can someone add: Add an ALTER TABLE MODIFY CONSTRAINT item to the todo list? I am even willing to pick this one up in a while, after I finish some other outstanding tasks. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Pull me down again and guide me into pain... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pgsql vc++|win32
-Original Message- From: luke [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 9:16 AM To: Hackers Subject: [HACKERS] pgsql vc++|win32 Hi guys, I'm planning with some friends to develop a port of pgsql, to native win32 environment using vc++; We read about MingW choice of official dev team, and we comprise their worries but we think that best results could be obtained using some really native win32 libraries (for better or for worse =/) Mingw uses the native Win32 libraries. Porting from a Mingw port to VC++ will be trivial compared to what we have now. where can I access latest dev source code and dev docs in the/from CVS ? Maybe you want the Win32 page. There are some links to it in recent messages here. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraint modification on todo list
On Mon, 2003-09-08 at 14:32, Jeroen Ruigrok/asmodai wrote: Hi people, can someone add: Add an ALTER TABLE MODIFY CONSTRAINT item to the todo list? I am even willing to pick this one up in a while, after I finish some other outstanding tasks. This could be rather time consuming to actually write but having the ability to change foreign key on update / on delete modes without rechecking all of the data would be very useful. I think this is a more consistent syntax: ALTER TABLE .. ADD OR REPLACE table constraint signature.asc Description: This is a digitally signed message part
Re: [HACKERS] plpgsql debugging
On Monday 08 September 2003 17:14, Andreas Pflug wrote: Richard Huxton wrote: Actually, a simple trace ability would be a huge step forward. It'd save me dotting RAISE statements around my functions while I write them. Sounds bloody familiar... :-( Even the ability to add DEBUG statements that checked some global flag before firing would be very useful (to me at least). I could imagine a DEBUG which works like RAISE NOTICE with checking for a set variable, and ASSERT which works like RAISE ERROR. Does anybody expect keyword conflicts from this? How about a DEBUG block, ideally with a token? ... DEBUG ''foo'' RAISE NOTICE ''my loop counter is %'',i; -- any other valid statements here END DEBUG; ... = SET DEBUG_TOKEN='foo'; = SELECT my_function(); That would let you turn debugging on/off for various modules by token-name, and let you e.g. check whether there are the expected number of records in some target table. Would that impose a horrible performance cost, or would the whole block just be skipped? (I only ever took a quick look at the plpgsql code) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] constraint modification on todo list
-On [20030908 20:52], Rod Taylor ([EMAIL PROTECTED]) wrote: This could be rather time consuming to actually write but having the ability to change foreign key on update / on delete modes without rechecking all of the data would be very useful. I was more interested in this feature for CHECK constraints. :) I think this is a more consistent syntax: ALTER TABLE .. ADD OR REPLACE table constraint I was following Oracle's example here for sake of some consistency between some RDBMSes. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Gravitation can not be held responsible for people falling in love... ---(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] pgsql vc++|win32
Dann Corbit wrote: Mingw uses the native Win32 libraries. Porting from a Mingw port to VC++ will be trivial compared to what we have now. where can I access latest dev source code and dev docs in the/from CVS ? Maybe you want the Win32 page. There are some links to it in recent messages here. URL is: http://momjian.postgresql.org/main/writings/pgsql/win32.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: How would we deprecate it exactly? Throw a NOTICE? Release notes, I guess. A NOTICE would be fine as long as it didn't result in a flood of them. If that happened once at parse time that should be fine, I think. It would be relatively difficult to do that; given the way plpgsql is structured, a runtime message would be a lot easier. What's the practice in deprecating other features? We generally don't ;-). Certainly 7.4 contains bigger incompatible changes than this one, and so have most of our prior releases. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] constraint modification on todo list
Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: can someone add: Add an ALTER TABLE MODIFY CONSTRAINT item to the todo list? Why? For a constraint, it's not obvious what this would do for you that dropping and re-adding the constraint wouldn't do. In the places where we support CREATE OR REPLACE, it's because it's important to maintain continuity of the object's identity, but I don't see any need for that with respect to check constraints. BTW, getting something put on the todo list doesn't mean anyone's going to step up to the plate and do it ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pgsql in shared lib
hi, ist possible to compile postgres (after same small modification) to shared so, or dll , and usr it like normal postgres , but without any server and so on. Its whould be work like simple database (with all funciton in one lib), which dont need any others additionals like (network,other process etc) ---(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] pgsql in shared lib
ivan [EMAIL PROTECTED] writes: hi, ist possible to compile postgres (after same small modification) to shared so, or dll , and usr it like normal postgres , but without any server and so on. Not without very major code changes. -Doug ---(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] constraint modification on todo list
Tom Lane wrote: Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: can someone add: Add an ALTER TABLE MODIFY CONSTRAINT item to the todo list? Why? For a constraint, it's not obvious what this would do for you that dropping and re-adding the constraint wouldn't do. In the places where we support CREATE OR REPLACE, it's because it's important to maintain continuity of the object's identity, but I don't see any need for that with respect to check constraints. I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Of course, one idea would be to allow MODIFY to make changes that _don't_ require rechecking, but I have no idea what such changes would be. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pgsql vc++|win32
On Mon, 8 Sep 2003, Bruce Momjian wrote: Dann Corbit wrote: Mingw uses the native Win32 libraries. Porting from a Mingw port to VC++ will be trivial compared to what we have now. where can I access latest dev source code and dev docs in the/from CVS ? Maybe you want the Win32 page. There are some links to it in recent messages here. URL is: http://momjian.postgresql.org/main/writings/pgsql/win32.html Just a reminder to everyone looking at porting that you wanna check the licensing from the software from MS you might be linking in. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pgsql in shared lib
ivan wrote: hi, ist possible to compile postgres (after same small modification) to shared so, or dll , and usr it like normal postgres , but without any server and so on. Its whould be work like simple database (with all funciton in one lib), which dont need any others additionals like (network,other process etc) You apparently want an embedded database. That is really a different class of software. Maybe a good place to start looking is at the Berkeley DB stuff at http://www.sleepycat.com cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraint modification on todo list
-On [20030908 22:42], Bruce Momjian ([EMAIL PROTECTED]) wrote: I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Of course, one idea would be to allow MODIFY to make changes that _don't_ require rechecking, but I have no idea what such changes would be. I might have misread/misremembered something: Constraint State Modification You can use the MODIFY CONSTRAINT clause of the ALTER TABLE statement to change the following constraint states: * DEFERRABLE or NOT DEFERRABLE * INITIALLY DEFERRED or INITIALLY IMMEDIATE * RELY or NORELY * USING INDEX ... * ENABLE or DISABLE * VALIDATE or NOVALIDATE * EXCEPTIONS INTO ... http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2103836 I wonder if it is possible to have ALTER TABLE ... MODIFY CONSTRAINT ... CHECK ... Because what I can imagine, and please correct me if I miss something in my thought pattern, you have a small gap between dropping a constraint and adding the new one allowing the possibility of missing checks. Or would this be solved by adding a second constraint under another constraint name with the new constraint values and then dropping the older one? If so, then ALTER TABLE ... RENAME CONSTRAINT would come in handy. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Things should be made as simple as possible, but not any simpler... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] constraint modification on todo list
Bruce Momjian [EMAIL PROTECTED] writes: I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Yeah. Rod's point about altering foreign key action settings is a good one, but other than that I don't see a whole lot of scope for shortcuts. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] constraint modification on todo list
Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: Because what I can imagine, and please correct me if I miss something in my thought pattern, you have a small gap between dropping a constraint and adding the new one allowing the possibility of missing checks. If you're concerned about concurrent transactions, you should do the change like this: begin; alter table drop constraint ...; alter table add constraint ...; commit; which leaves no window for missed checks. (The first ALTER will take out an exclusive lock on the table, which will be held till end of transaction.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgsql in shared lib
Doug McNaught [EMAIL PROTECTED] writes: ivan [EMAIL PROTECTED] writes: ist possible to compile postgres (after same small modification) to shared so, or dll , and usr it like normal postgres , but without any server and so on. Not without very major code changes. ... which are unlikely to happen, given the development community's strong emphasis on reliability. An embedded database is inherently less reliable than a client/server one, since any application bug has the potential to corrupt the database. With client/server, at least we only have to worry about our own bugs ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: What's the practice in deprecating other features? We generally don't ;-). Certainly 7.4 contains bigger incompatible changes than this one, and so have most of our prior releases. I thought I had seen discussions along the lines of we'll give them one cycle to fix it and then they are shot. It does seem very late in this cycle to make such changes. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] constraint modification on todo list
Jeroen Ruigrok/asmodai [EMAIL PROTECTED] wrote: Because what I can imagine, and please correct me if I miss something in my thought pattern, you have a small gap between dropping a constraint and adding the new one allowing the possibility of missing checks. I think, someone correct me if I'm wrong, you can do it inside a transaction, so no time window without constraint. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraint modification on todo list
Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Yeah. Rod's point about altering foreign key action settings is a good one, but other than that I don't see a whole lot of scope for shortcuts. The only shortcuts that come to my mind is when you enlarge ( or relax ) a constraint, and I think that is not so easy detect it. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] mcxt.c
Neil Conway [EMAIL PROTECTED] writes: I think the percentage of deployments that enable assertions (which causes a runtime performance hit) but NOT debugging info (which does not) is pretty small. How big a penalty is it? If it's small, or if it could be made small by making a few assertions require an extra extra-assertions option, then perhaps it would make more sense to ship with it enabled? I know the number of times I received ORA-600 (oracle's way of spelling assertion failed) I sure wouldn't have wanted the database to continue processing based on invalid data. ISTM that checking for non-NULL pointers is pretty pointless: just because a pointer happens to be non-NULL doesn't mean it is any more valid, and dereferencing a NULL pointer is easy enough to track down in any case. That would depend a lot on the scenario. Often code doesn't crash right at that point but stores the data causes a crash elsewhere. Or perhaps even causes corrupted data on disk. Probably the most useful side-effect of checking for null pointers is that programmers get in the habit of checking all their arguments... -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FreeBSD/i386 thread test
Bruce Momjian writes: Both gethostbyname() and getpwuid() have no _r equivalents on FreeBSD-STABLE, ergo no thread-safe functions of these. So you don't have all the *_r functions, and your non-*_r functions aren't thread-safe. Should we disable theading on FreeBSD? Seems so. Why would FreeBSD have a library of thread-safe libc functions (libc_r) if the functions weren't thread-safe? I think the test is faulty. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] FreeBSD/i386 thread test
-On [20030908 23:52], Peter Eisentraut ([EMAIL PROTECTED]) wrote: Why would FreeBSD have a library of thread-safe libc functions (libc_r) if the functions weren't thread-safe? I think the test is faulty. Having libc_r is not a guarantee that all functions of libc are represented in that library as thread-safe functions. gethostbyname_r() is a notable reentrant function which is absent in FreeBSD. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Character is what you are in the dark... ---(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] FreeBSD/i386 thread test
Jeroen Ruigrok/asmodai wrote: -On [20030908 23:52], Peter Eisentraut ([EMAIL PROTECTED]) wrote: Why would FreeBSD have a library of thread-safe libc functions (libc_r) if the functions weren't thread-safe? I think the test is faulty. A thread-safe library has a per-thread errno value (i.e. errno is a #define to a function call), thread-safe io buffers for stdio, etc. Some of these changes cause a noticable overhead, thus a seperate library for those users who want to avoid that overhead. Reentrancy is independant from _r: If you look at the prototype of gethostbyname(), it's just not possible to make that thread safe with reasonable effort - the C library would have to keep one buffer per thread around. Having libc_r is not a guarantee that all functions of libc are represented in that library as thread-safe functions. gethostbyname_r() is a notable reentrant function which is absent in FreeBSD. Is there a thread-safe alternate to gethostbyname() for FreeBSD? -- Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] mcxt.c
Greg Stark [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: I think the percentage of deployments that enable assertions (which causes a runtime performance hit) but NOT debugging info (which does not) is pretty small. How big a penalty is it? If it's small, or if it could be made small by making a few assertions require an extra extra-assertions option, then perhaps it would make more sense to ship with it enabled? We generally don't recommend enabling assertions in production installations, because it's not clear that there is any net gain in stability from doing so. Per the manual: --enable-cassert Enables assertion checks in the server, which test for many can't happen conditions. This is invaluable for code development purposes, but the tests slow things down a little. Also, having the tests turned on won't necessarily enhance the stability of your server! The assertion checks are not categorized for severity, and so what might be a relatively harmless bug will still lead to server restarts if it triggers an assertion failure. Currently, this option is not recommended for production use, but you should have it on for development work or when running a beta version. Obviously this does not apply to cases where the assert is testing for something that will cause a core dump anyway, like an improperly NULL pointer. But there are many, many asserts for things that are probably not serious bugs (at worst they might deserve a FATAL exit, rather than a system-wide PANIC). Peter E. has speculated about improving the Assert facility to allow categorization along this line, but I dunno when it will happen. As far as your original question goes, I find that MEMORY_CONTEXT_CHECKING and CLOBBER_FREED_MEMORY are quite expensive, and presently --enable-cassert turns these on. But of course we could decouple that if we were going to encourage people to run with asserts enabled in production. I don't think asserts are hugely expensive otherwise (though that might change if we sprinkle them as liberally as Gaetano's proposal implies...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] constraint modification on todo list
Jeroen Ruigrok/asmodai wrote: -On [20030908 22:42], Bruce Momjian ([EMAIL PROTECTED]) wrote: I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Of course, one idea would be to allow MODIFY to make changes that _don't_ require rechecking, but I have no idea what such changes would be. I might have misread/misremembered something: Constraint State Modification You can use the MODIFY CONSTRAINT clause of the ALTER TABLE statement to change the following constraint states: * DEFERRABLE or NOT DEFERRABLE * INITIALLY DEFERRED or INITIALLY IMMEDIATE * RELY or NORELY * USING INDEX ... * ENABLE or DISABLE * VALIDATE or NOVALIDATE * EXCEPTIONS INTO ... http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2103836 I wonder if it is possible to have ALTER TABLE ... MODIFY CONSTRAINT ... CHECK ... Because what I can imagine, and please correct me if I miss something in my thought pattern, you have a small gap between dropping a constraint and adding the new one allowing the possibility of missing checks. Or would this be solved by adding a second constraint under another constraint name with the new constraint values and then dropping the older one? If so, then ALTER TABLE ... RENAME CONSTRAINT would come in handy. Oh, you bring up two important issues --- one is is the gap in time between the drop and the recreate. This case can be done by doing the query in a transaction --- the lock will exist until the transaction completes, and in fact, you can roll it back in case you don't like it. The second case is changing not the constraint but its behavior, like deferrability. Right now I don't see any way to control that except drop/recreate, and this is where MODIFY might make sense. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] FreeBSD/i386 thread test
Manfred Spraul wrote: Jeroen Ruigrok/asmodai wrote: -On [20030908 23:52], Peter Eisentraut ([EMAIL PROTECTED]) wrote: Why would FreeBSD have a library of thread-safe libc functions (libc_r) if the functions weren't thread-safe? I think the test is faulty. A thread-safe library has a per-thread errno value (i.e. errno is a #define to a function call), thread-safe io buffers for stdio, etc. Some of these changes cause a noticable overhead, thus a seperate library for those users who want to avoid that overhead. Reentrancy is independant from _r: If you look at the prototype of gethostbyname(), it's just not possible to make that thread safe with reasonable effort - the C library would have to keep one buffer per thread around. See the top of src/port/thread.c --- that's exactly what is does (keep one buffer per thread around). * Threading sometimes requires specially-named versions of functions * that return data in static buffers, like strerror_r() instead of * strerror(). Other operating systems use pthread_setspecific() * and pthread_getspecific() internally to allow standard library * functions to return static data to threaded applications. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] FreeBSD/i386 thread test
Bruce Momjian wrote: Manfred Spraul wrote: Jeroen Ruigrok/asmodai wrote: -On [20030908 23:52], Peter Eisentraut ([EMAIL PROTECTED]) wrote: Why would FreeBSD have a library of thread-safe libc functions (libc_r) if the functions weren't thread-safe? I think the test is faulty. A thread-safe library has a per-thread errno value (i.e. errno is a #define to a function call), thread-safe io buffers for stdio, etc. Some of these changes cause a noticable overhead, thus a seperate library for those users who want to avoid that overhead. Reentrancy is independant from _r: If you look at the prototype of gethostbyname(), it's just not possible to make that thread safe with reasonable effort - the C library would have to keep one buffer per thread around. See the top of src/port/thread.c --- that's exactly what is does (keep one buffer per thread around). * Threading sometimes requires specially-named versions of functions * that return data in static buffers, like strerror_r() instead of * strerror(). Other operating systems use pthread_setspecific() * and pthread_getspecific() internally to allow standard library * functions to return static data to threaded applications. And that's exactly what src/tools/test_thread_funcs.c tests for. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] constraint modification on todo list
Bruce Momjian [EMAIL PROTECTED] writes: Oh, you bring up two important issues --- one is is the gap in time between the drop and the recreate. This case can be done by doing the query in a transaction --- the lock will exist until the transaction completes, and in fact, you can roll it back in case you don't like it. IIRC, Oracle does not have rollback-able DDL. That might imply that the reason they have MODIFY CONSTRAINT is that in Oracle you can't use the above way to eliminate the window. Can you put ALTERs inside transactions at all in Oracle? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] constraint modification on todo list
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Yeah. Rod's point about altering foreign key action settings is a good one, but other than that I don't see a whole lot of scope for shortcuts. Agreed. Added to TODO: o Allow ALTER TABLE to change constraint deferrability and actions -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] ISO 8601 Time Intervals of the format with time-unit designators
Tom wrote... At this point it should move to pghackers, I think. Background for pghackers first, open issues below... Over on pgpatches we've been discussing ISO syntax for time intervals of the format with time-unit designators. http://archives.postgresql.org/pgsql-patches/2003-09/msg00103.php A short summary is that Ive submitted a patch that accepts intervals of this format.. Postgresql interval: ISO8601 Interval --- '1 year 6 months''P1Y6M' '3 hours 25 minutes 42 seconds' 'PT3H25M42S' The final draft is here ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF This patch was backward-compatable, but further improvements discussed on patches may break compatability so I wanted to discuss them here before implementing them. Ill also be submitting a new datestyle iso8601 to output these intervals. Open issues: 1. Postgresql supported a shorthand for intervals that had a similar, but not compatable syntax: IntervalISO Existing postgres 8601shorthand - '1 year 1 minute' 'P1YT1M' '1Y1M' '1 year 1 month''P1Y1M' N/A The current thinking of the thread in pgpatches is to remove the existing (undocumented) syntax. Removing this will break backward compatability if anyone used this feature. Let me know if you needed it. 2. Some of the parsing for intervals is inconsistant and confusing. For example, note that 0.01 years is less than 0.01 months. betadb=# select '0.01 month'::interval as hundredth_of_month, betadb-#'0.01 year'::interval as hundredth_of_year; hundredth_of_month | hundredth_of_year +--- 07:12:00 | 00:00:00 This occurs because the current interval parsing rounds fractional years to the month, but fractional months to the fraction of a second. The current thinking on the thread in patches is at the very least to make these consistant, but with some open-issues because months arent a fixed number of days, and days arent a fixed number of seconds. The easiest and most minimal change would be to assume that any fractional part automatically gets turned into seconds, assuming things like 30 seconds/month, 24 hrs/day. Since all units except years work that way today, itd would have the least impact on existing code. A probably better way that Tom recommended would remember fractional months and fractional days. This has the advantage that unlike today, .5 months::interval + .5 months::interval would then equal 1 month. So what should .5 years be? Today, its 6 mons. But I could just as easily argue that it should be 365.2425/2 days, or 4382.91 seconds. Each of these will be different (the last two are different durring daylight savings). 3. This all is based on the final draft standard of ISO 8601, but I havent seen the actual expensive standard. If anyone has it handy... Also, Im curious to know what if anything the SQL spec says about intervals and units. Any pointers. Ron Any other interval annoyances I should hit at the same time? ---(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] row level lock and table level locks
TupleTables are just temporary data structures to hold transiently created tuples during execution of a query. There's usually one for each plan node. So, if i have the following transaction: begin work; select * from students where a age=19 for update; lock table studens in share mode; commit; The TupleTable will exist for the query from the point the query is made untill the transaction is committed? or does the TupleTable go away as soon as query is finished executing? I would think the TupleTable for that query is held untill the transaction is committed since lock on the tuple is endtill the end of transaction Thanks From: Tom Lane [EMAIL PROTECTED] To: Jenny - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [HACKERS] row level lock and table level locks Date: Mon, 08 Sep 2003 12:49:51 -0400 Jenny - [EMAIL PROTECTED] writes: I found out that TupleTable stores per-tuple information(it stores HeapTupleData) and that also there are multiple TupleTables in the db at a time.Based on what are diffrent TupleTables created? cc regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster _ Express yourself with MSN Messenger 6.0 -- download now! http://www.msnmessenger-download.com/tracking/reach_general ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] row level lock and table level locks
Jenny - [EMAIL PROTECTED] writes: TupleTables are just temporary data structures to hold transiently created tuples during execution of a query. There's usually one for each plan node. The TupleTable will exist for the query from the point the query is made untill the transaction is committed? or does the TupleTable go away as soon as query is finished executing? It goes away as soon as the query finishes. My answer above was mistaken --- plan nodes usually allocate slots in a single TupleTable created (and destroyed) by execMain.c, rather than each having their own TupleTable. But it's still a query-lifetime data structure. I would think the TupleTable for that query is held untill the transaction is committed since lock on the tuple is endtill the end of transaction You keep looking for nonexistent locks on tuples ... The only resources represented by a TupleTable entry are memory for a transient tuple (if we rewrote the system today, we'd forget that function, since short-term memory contexts can do the job better) or a buffer pin for a tuple that's sitting in a shared disk buffer. There is no reason to hold a buffer pin beyond the time that the tuple might actually be referenced by the query plan. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Maximum table size
Is our maximum table size limited by the maximum block number? With our block number maximum of: #define MaxBlockNumber ((BlockNumber) 0xFFFE) 0xFFFE = 4294967294 would the max table size really be (4GB * 8k) or 32 TB, not 16TB, as listed in the FAQ: 4.5) What is the maximum size for a row, a table, and a database? ... Maximum size for a table?16 TB Is the 16TB number a hold-over from when we weren't sure block number was unsigned, though now we are pretty sure it is handled as unsigned consistenly? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] mcxt.c
On Mon, 2003-09-08 at 11:09, Gaetano Mendola wrote: Tom Lane [EMAIL PROTECTED] wrote: I see no value at all in an assert. The code will dump core just fine with or without an assert ... Right but an assert can display information about the file and line number without debug the application I think the percentage of deployments that enable assertions (which causes a runtime performance hit) but NOT debugging info (which does not) is pretty small. ISTM that checking for non-NULL pointers is pretty pointless: just because a pointer happens to be non-NULL doesn't mean it is any more valid, and dereferencing a NULL pointer is easy enough to track down in any case. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] mcxt.c
Tom Lane [EMAIL PROTECTED] wrote: Gaetano Mendola [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: This seems inappropriate to me. Are you going to suggest that every routine that takes a pointer parameter needs to explicitly test for null? Of course I'm not suggesting this, what I'm suggesting is put an assert( ) if the test can slow down the performances and an if ( ) in places that are not going to touch the performances. I see no value at all in an assert. The code will dump core just fine with or without an assert ... Right but an assert can display information about the file and line number without debug the application, without mention that reading the code with the assert is clear what are the precondictions for a call function. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index creation takes for ever
I don't think so, because the patch does nothing to keep the sort order once the index is initially created. As Tom mentioned, we might not want to keep the tid's in order after the index is created because he wants the most recent tid's first, so the expired ones migrate to the end. But on average this argument only holds true for unique indexes, no ? Is there any code that stops the heap lookup after the visible tuple is found ? At least in an index with more rows per key you will fetch all heaps after the first one anyway to get at the next row. This is better done in heap order, no ? And the bitmap approach will not work for large result sets. Summa summarum I would leave the TODO item (maybe add a comment (only for non-unique, evaluate performance)) Andreas ---(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] Index creation takes for ever
On Mon, 8 Sep 2003 11:31:05 +0200, Zeugswetter Andreas SB SD [EMAIL PROTECTED] wrote: As Tom mentioned, we might not want to keep the tid's in order after the index is created because he wants the most recent tid's first, so the expired ones migrate to the end. But on average this argument only holds true for unique indexes, no ? Is there any code that stops the heap lookup after the visible tuple is found ? At least in an index with more rows per key you will fetch all heaps after the first one anyway to get at the next row. This is better done in heap order, no ? Yes, yes, yes, and yes. Seems we all agree on that; the patch has been queued for 7.5. Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? 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] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? It won't bite me so maybe I don't have a right to express an opinion :-) plpgsql is not C - it appears to be in the Algol/Pascal/Ada family, which do tend to avoid implicit type conversion. On that basis, option 2 seems like it might be the right answer and also the one most likely to break lots of existing functions. Maybe the right thing would be to deprecate relying on implicit conversion to boolean for one release cycle and then make it an error. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
