Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
Tom Lane wrote: (B (BIn the XLogInsert(), it makes two kinds of logs, "whole buffer(page) (Blog" and "partial buffer log", isn't it? Is it only "who buffer (Blog" (Bto generate a log with "hole"? (B (B (B Right. (B (BI see. (BI think, it is important to reduce the necessities to write whole pages (Bto WAL (as TODO list). (B# It seems difficult to do so... Compressing WAL is easier way. (B (B No, because the WAL code cannot assume that all pages follow the (B convention that pd_lower and pd_upper represent the boundaries of (B free space. (As a counterexample: index metapages don't always (B do that.) (B (BOh, I forget it. (BAnd I think it is good idea to modify XLogInsert API as CVS, too. (B (B-- (BJunji Teramoto (B (B---(end of broadcast)--- (BTIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] regexp_replace
David Fetter wrote: On Tue, Jun 07, 2005 at 09:35:56AM +0900, a_ogawa wrote: David Fetter wrote: We don't yet have this functionality, as the patch allows for using second and later regex matches () in the replacement pattern. The function is misnamed. It should be called regex_replace_all() or some such, as it violates the principle of least astonishment by replacing all instances by default. Every other regex replacement defaults to replace first, not replace all. Or maybe it should take a bool for replace all, or...? Anyhow, it's worth a discussion :) I think that the usage increases if replace all or replace first can be specified to this function. Ogawa-san, I think that this would be a case for function overloading: function regexp_replace( string text, pattern text, replacement text ) RETURNS TEXT; /* First only */ regexp_replace( string text, pattern text, replacement text, global bool ) RETURNS TEXT; /* Global if global is TRUE, first only otherwise */ What do you think of this idea? One trouble is that there are some other options. For example, one could add switches for all combinations of global, case insensitive, compile once, exclude whitespace, etc. as perl does. Do we want to go this route? My idea is opposite. I think that the regexp_replace() should make replace all a default. Because the replace() of pgsql replaces all string, and regexp_replace() of oracle10g is also similar. And I think that it is better to be able to specify the option with text. I think about this function specification: regexp_replace( string text, pattern text, replacement text ) RETURNS TEXT; /* Replace all */ regexp_replace( string text, pattern text, replacement text, options text ) RETURNS TEXT; /* Change operation by the option. */ The options can use the following values. f: Replace first only i: Case insensitive Any comments? regards, --- Atsushi Ogawa ---(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
[HACKERS] GiST concurrency and recovery for 8.1
Hi there, I want to inform that we began to work on concurrency and recovery support in GiST on our's own account and hope to be ready before 8.1 code freeze. There was some noise about possible sponsoring of our work, but we didn't get any offering yet, so we're looking for sponsorhip ! We recognize that current GiST doesn't conform to enterprize level and our people starting experience difficulties using our extensions in production environment. That's why we decided to meet the wishes of GiST users not waiting real sponsorship. Our plan is to implement recovery first because we'll use LSN as NSN (node sequence number) needed for concurrency. Fpr details see Concurrency and Recovery in Generalized Search Trees by M. Kornacker, C.Mohan, J. Hellerstein. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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
[HACKERS] RESET CONNECTION idea
Our current RESET CONNECTION TODO item is: * Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of temporary tables, removing any NOTIFYs, cursors, open transactions, prepared queries, currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. The difficult of this features is allowing RESET ALL to not affect changes made by the interface driver for its internal use. One idea is for this to be a protocol-only feature. Another approach is to notify the protocol when a RESET CONNECTION command is used. I know we have GUC variables that are passed automatically to the client. I assume varaible changes are also automatically sent to the client. What if we create a 'reset_connection' guc that is initially false, and is set to 'true' when someone resets a connection. Then, when it happens, the client finds out, reconfigures whatever it needs, then sets the value back to 'false'. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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] regexp_replace
On Tue, Jun 07, 2005 at 10:27:28PM +0900, Atsushi Ogawa wrote: David Fetter wrote: On Tue, Jun 07, 2005 at 09:35:56AM +0900, a_ogawa wrote: David Fetter wrote: We don't yet have this functionality, as the patch allows for using second and later regex matches () in the replacement pattern. The function is misnamed. It should be called regex_replace_all() or some such, as it violates the principle of least astonishment by replacing all instances by default. Every other regex replacement defaults to replace first, not replace all. Or maybe it should take a bool for replace all, or...? Anyhow, it's worth a discussion :) I think that the usage increases if replace all or replace first can be specified to this function. Ogawa-san, I think that this would be a case for function overloading: function regexp_replace( string text, pattern text, replacement text ) RETURNS TEXT; /* First only */ regexp_replace( string text, pattern text, replacement text, global bool ) RETURNS TEXT; /* Global if global is TRUE, first only otherwise */ What do you think of this idea? One trouble is that there are some other options. For example, one could add switches for all combinations of global, case insensitive, compile once, exclude whitespace, etc. as perl does. Do we want to go this route? My idea is opposite. I think that the regexp_replace() should make replace all a default. Because the replace() of pgsql replaces all string, and regexp_replace() of oracle10g is also similar. I respectfully disagree. Although Oracle does things this way, no other regular expression search and replace does. Historically, you can find that Oracle does it this way is not a reason why we would do it. Text editors, programming languages, etc., etc. do replace the first by default and replace globally only when told to. And I think that it is better to be able to specify the option with text. I think about this function specification: regexp_replace( string text, pattern text, replacement text ) RETURNS TEXT; /* Replace all */ regexp_replace( string text, pattern text, replacement text, options text ) RETURNS TEXT; /* Change operation by the option. */ The options can use the following values. f: Replace first only i: Case insensitive Any comments? I think that case insensitive is a good thing to add separately as a boolean :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] RESET CONNECTION idea
Bruce Momjian pgman@candle.pha.pa.us writes: What if we create a 'reset_connection' guc that is initially false, and is set to 'true' when someone resets a connection. Then, when it happens, the client finds out, reconfigures whatever it needs, then sets the value back to 'false'. It seems to me that one could trivially break any driver that depends on such a thing, just by issuing SET reset_connection = true; Then the driver will think the connection has been reset when it has not, and become completely confused about the actual connection state. You can't avoid this by making the variable protected, because then the driver itself would be unable to clear it. In short I don't think this can work. We'd need an actual protocol message specifically to report RESET CONNECTION. The loss of backwards compatibility is not necessarily a bad thing; arguably, you *want* any client library that doesn't recognize the message to fail, since otherwise it will be out of sync about the connection state. Alternatively, depending on what level of client software you think should be issuing such things, we could make the RESET request be a new protocol message rather than a SQL statement. Then it couldn't even be invoked by a non-updated client, and so the compatibility problem goes away. 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
[HACKERS] copying a bucket to a BufFile
hi,
I'm trying to copy the contents of a bucket of a hash table to a BufFile.
There is a memory context for each bucket. That is, there is an array
(#nbuckets) memory contexts.
thus the tree of mem cxts are
...
hashCxt
|
batchCxt
| |
| |
BucketCxt... BucketCxt ... (#nbuckets)
The server terminated abnormally here - (1) at the code below and I
can't understand why! Please if anyone has any idea what's wrong, i'd
appreciate any suggetion. Thanks is advance!! --martha
void ExecScanDPHashBucketCopyToFile(HashJoinTable hashTable,int noOfbucket)
{
HashJoinTuple hashTuple;
TupleTableSlot *slot;
// create temp files only if they already don't exist.
if(hashTable-outerBucketFile[noOfbucket] == NULL)
hashTable-BucketFile[noOfbucket] = BufFileCreateTemp(false);
hashTuple = hashTable-buckets[noOfbucket]; // first tuple in bucket
while(hashTuple != NULL)
{
HeapTuple heapTuple = hashTuple-htup;
ExecHashJoinSaveTuple(heapTuple,
HashTable-BucketFile[noOfbucket]);
- (1)
// print the tuple we copy
slot = ExecStoreTuple(heapTuple,slot,InvalidBuffer,false);
if(!TupIsNull(slot))
print_slot(slot);
hashTuple = hashTuple-next;
}
// the bucket has copied. Rewind file to read it later.
if(BufFileSeek(hashtable-BucketFile[noOfbucket], 0, 0L, SEK_SET))
ereport(ERROR,(errcode_for_file_access(),
errmsq(could not rewind hash join emp file:%m)));
if(hashTable-bucketCxt[noOfbucket] != NULL) //if there is a context
allocated for this bucket.
MemoryContextReset(*(hashTable-bucketCxt[noOfbucket]));
hashTable-flushedBucket[noOfbucket] = true; // consider it flushed
anyway
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] copying a bucket to a BufFile
[EMAIL PROTECTED] writes: I'm trying to copy the contents of a bucket of a hash table to a BufFile. There is a memory context for each bucket. That is, there is an array (#nbuckets) memory contexts. That's a lot of contexts... The server terminated abnormally here - (1) at the code below and I can't understand why! Probably you reset a context that contained data that was still needed. Exactly where did that slot get created, for instance? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Issue with adding ORDER BY to EXCEPT.
SELECT encounter.encounter_id, encounter_d.encounter_d_id FROM encounter JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id EXCEPT SELECT encounter.encounter_id, encounter_d.encounter_d_id FROM encounter JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id JOIN p_l_d ON p_l_d.patient_id = encounter.patient_mpi WHERE encounter_d.encounter_id = encounter.encounter_id AND ((p_l_d.start_date = encounter_d.from_date OR p_l_d.start_date IS NULL) AND (p_l_d.end_date = encounter_d.from_date OR p_l_d.end_date IS NULL)) ORDER BY encounter.encounter_id, encounter_d.encounter_d_id With the ORDER BY NOTICE: adding missing FROM-clause entry for table encounter NOTICE: adding missing FROM-clause entry for table encounter_d ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns I suppose this is because the columns in the except are the same that the ones in the main select and the order by get confused. i'm redirecting to hackers to know if this is a known bug or there is something wrong in the select? i don't see anything wrong!! -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] The Contrib Roundup (long)
Folks, I had a lot of time to kill on airplanes recently so I've gone digging through /contrib in an effort to sort out what's in there and try to apply some consistent rules to it. Before people read further, please understand that this is just an initial discussion on what will and won't be in contrib for 8.1; nobody has made any decisions yet. What Should Be In Contrib? --- Looking over what's in there most of the reasonable contrib options fall into 3 groups: extra data types, extra functions and backend utilities. These all seem reasonable things to put into contrib, with the addition of other code being tested for inclusion in the core. These categories also pretty much cover things that need to be inside the PostgreSQL source to build. What Shouldn't Be In Contrib? --- The things I think we should exclude from contrib are rather more varied. Based on examples: a) Code with major external dependencies other than a programming language. Partly this is because this means they are useful to less users; more importantly, this is because the external dependencies mean that the release cycle for these tools is likely to be determined by the external dependency and not by PostgreSQL's release cycle. Further, the external dependencies mean that it's less likely that the postgresql core programmers can maintain them in the event that the original developer goes away. The Mysql conversion scripts are a good example of this; I don't believe that my2pg even works with MySQL 4. b) Alpha-quality code and unfinished projects. Shipping something with the PostgreSQL source code implies a certain level of stability, completeness and quality. We shouldn't be including scripts which took 2 hours to write and have only been tested on one platform. This stuff can get developed on pgFoundry and moved to contrib when it's close to mature. c) Differently licensed code. I'm not an attorney: I won't pretend to know which licenses it's legal to bundle in our tarballs and which are not. But I do know that most users and redistributors aren't going to grep contrib looking for other licenses, and putting differently licensed stuff in there is bad pr at best, and a legal booby trap at worst. (Particularly, there are 3 contrib modules by Massimo del Zotto, which are GPL licensed. According to the FSF's licensing admin, installing any these contrib modules will instantly make that copy of PostgreSQL GPL.) d) Application code and example code. Contrib is *not* a good place for here's how you do this in an application kind of code. It's not visible enough to be documentation, and such examples aren't generally useful to the majority of users as code. Moving to PgFoundry is NOT Demotion I know that I'm going to get a lot of resistance for the idea of moving some project to pgFoundry, because authors feel that it's a demotion for their code not to be shipped with the PostgreSQL source. However, being on pgFoundry increases the visibility of your code and allows a wider array of people to contribute to it -- and even find it. And for items of particularly broad utility, stuff can always go from pgFoundry into the core when mature or when utility is demonstrated. Contrib Subdirectories? - I think it would also be helpful to users if we could create subdirectories to organize contrib into categories. This would help users and packagers find what they want. These directories would be: data_types/ functions/ utilities/ I've noted below which contrib code I think should go in those subdirs. Contrib Build Options? --- I'll point out that several people (including one of our RPM builders) spoke up in favor of the idea of adding ./contrib command line options for individual contrib items. Discussion was dropped without a decision being reached. That would work like: ./configure --with-perl --prefix=/usr/pgsql --with-tsearch2 --with-fuzzystrmatch Documentation -- As previously mentioned, all contrib modules need to have documentation in the main postgreSQL docs. Probably their own section, called Optional Modules. Contrib Item Listing What follows is my notes on individual contrib projects. Many contain questions because I don't know enough about the item. Please read through them an provide what feedback you can. Especially, provide feedback on the items I'm suggesting eliminating or moving out. I've noted the author contact info where I'm thinking of moving modules, and will be attempting to contact those authors if we decide to change status. adddepend: is this still needed, or would a proper dump-and-reload from 7.2 add the dependancy information anyway? array: placeholder for old array module; contains only a readme. Should probably be dropped for 8.2. btree_gist: data_types/ chkpass:
Re: [HACKERS] The Contrib Roundup (long)
a few comments scattered inline... On Tue, Jun 07, 2005 at 02:53:32PM -0300, Josh Berkus wrote: Folks, I had a lot of time to kill on airplanes recently so I've gone digging through /contrib in an effort to sort out what's in there and try to apply some consistent rules to it. Before people read further, please understand that this is just an initial discussion on what will and won't be in contrib for 8.1; nobody has made any decisions yet. What Should Be In Contrib? --- Looking over what's in there most of the reasonable contrib options fall into 3 groups: extra data types, extra functions and backend utilities. These all seem reasonable things to put into contrib, with the addition of other code being tested for inclusion in the core. These categories also pretty much cover things that need to be inside the PostgreSQL source to build. What Shouldn't Be In Contrib? --- The things I think we should exclude from contrib are rather more varied. Based on examples: a) Code with major external dependencies other than a programming language. Partly this is because this means they are useful to less users; more importantly, this is because the external dependencies mean that the release cycle for these tools is likely to be determined by the external dependency and not by PostgreSQL's release cycle. Further, the external dependencies mean that it's less likely that the postgresql core programmers can maintain them in the event that the original developer goes away. The Mysql conversion scripts are a good example of this; I don't believe that my2pg even works with MySQL 4. b) Alpha-quality code and unfinished projects. Shipping something with the PostgreSQL source code implies a certain level of stability, completeness and quality. We shouldn't be including scripts which took 2 hours to write and have only been tested on one platform. This stuff can get developed on pgFoundry and moved to contrib when it's close to mature. c) Differently licensed code. I'm not an attorney: I won't pretend to know which licenses it's legal to bundle in our tarballs and which are not. But I do know that most users and redistributors aren't going to grep contrib looking for other licenses, and putting differently licensed stuff in there is bad pr at best, and a legal booby trap at worst. (Particularly, there are 3 contrib modules by Massimo del Zotto, which are GPL licensed. According to the FSF's licensing admin, installing any these contrib modules will instantly make that copy of PostgreSQL GPL.) I agree that anything that is not BSD licensed should not go into contrib. d) Application code and example code. Contrib is *not* a good place for here's how you do this in an application kind of code. It's not visible enough to be documentation, and such examples aren't generally useful to the majority of users as code. Moving to PgFoundry is NOT Demotion I know that I'm going to get a lot of resistance for the idea of moving some project to pgFoundry, because authors feel that it's a demotion for their code not to be shipped with the PostgreSQL source. However, being on pgFoundry increases the visibility of your code and allows a wider array of people to contribute to it -- and even find it. And for items of particularly broad utility, stuff can always go from pgFoundry into the core when mature or when utility is demonstrated. Contrib Subdirectories? - I think it would also be helpful to users if we could create subdirectories to organize contrib into categories. This would help users and packagers find what they want. These directories would be: data_types/ functions/ utilities/ I've noted below which contrib code I think should go in those subdirs. These directories are misleading since all data types include functions. If we are paring down contrib, I see no reason to reorganize them. Contrib Build Options? --- I'll point out that several people (including one of our RPM builders) spoke up in favor of the idea of adding ./contrib command line options for individual contrib items. Discussion was dropped without a decision being reached. That would work like: ./configure --with-perl --prefix=/usr/pgsql --with-tsearch2 --with-fuzzystrmatch Documentation -- As previously mentioned, all contrib modules need to have documentation in the main postgreSQL docs. Probably their own section, called Optional Modules. Contrib Item Listing What follows is my notes on individual contrib projects. Many contain questions because I don't know enough about the item. Please read through them an provide what feedback you can. Especially, provide feedback on the items I'm suggesting eliminating or
[HACKERS] Daily DBT-3 (DSS) Results on CVS head
FYI, I have results being generated daily against CVS, in addition to dbt2: http://developer.osdl.org/markw/postgrescvs/ I've also added a link to instructions on how to submit patches to test against PostgreSQL on that page, if anyone's interested. Thanks, Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Suggestion: additional system views
Bruce Momjian wrote: Uh, I thought the shoot-down was for duplication of existing information in new system tables, not the addition of new system table information, e.g. we have pg_operator, but no list of error codes or keywords in the system tables. System tables are primarily useful if (a) the information is variable for a given installation, or (b) client programs may want to automatically process the information. Neither seems to apply here. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] libpq API incompatibility between 7.4 and 8.0
Bruce Momjian wrote: Is this a direction we want to explore --- using the SONAME as part of the translation domain? I think that's the way to go. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Issue with adding ORDER BY to EXCEPT.
On Tue, Jun 07, 2005 at 12:42:47PM -0500, Scott Marlowe wrote: On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote: I suppose this is because the columns in the except are the same that the ones in the main select and the order by get confused. i'm redirecting to hackers to know if this is a known bug or there is something wrong in the select? i don't see anything wrong!! No, it's because to the order by, the column names are the ones given by the part after the period of the first select. If you do a plain select UNION select with no order by, you'll see the title for the columns is taken from the first select list column names. So, the order by needs to be order by encounter_id, encounter_d_id Or even easier: order by 1, 2; Then you don't even need to know the column names... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpj93Gz0htfl.pgp Description: PGP signature
Re: [HACKERS] The Contrib Roundup (long)
On Tue, 2005-06-07 at 13:53, Josh Berkus wrote:
mysql: these utilities have been moved to project sites (such as
GBorg), and I believe that my2pg is broken with current versions
of MySQL. Can we remove this from contrib?
I believe this version now lives at
http://gborg.postgresql.org/project/mysql2psql/projdisplay.php, although
there are other versions. I agree it should be removed.
reindexdb: now obsolete per the REINDEX {database} command.
Remove from contrib.
actually I think part of the point of this was to give a command line
version of the reindex command, like we have for vaccum. If that still
matters, then it should probably stay. Actually it should probably be
converted to C and moved to /src/bin.
xml and xml2: both by John Gray ([EMAIL PROTECTED]). John, why
do we have two of these? Otherwise, data_types/.
istr that xml2 had some expanded capabilties at the expense of
additional security issues, but we should wait for the author to jump
in.
Josh, was this comprehensive? I don't see dblink, and was thinking there
was some others missing... soundex ?.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] The Contrib Roundup (long)
On 2005-06-07, Josh Berkus josh@agliodbs.com wrote: userlocks: another GPL script, with the problems that entails. Also problematic as it relies heavily on per-record OIDs, something we tell users not to do. Overall, should be removed. Author: Massimo. userlocks is just a very thin interface to functionality that's really in the backend. What's left in contrib/userlock probably isn't even copyrightable in any case. The best bet is probably to re-implement it in the backend directly. Removing it certainly isn't a good idea; the functionality is important. (It doesn't rely on per-record OIDs either.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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] The Contrib Roundup (long)
On Tue, Jun 07, 2005 at 02:53:32PM -0300, Josh Berkus wrote:
Moving to PgFoundry is NOT Demotion
Yeah, I agree. Lots of people understand search in pgfoundry.org much
easily than see contrib/adddepend. (I agree with most of the rest of
your comments as well.)
adddepend: is this still needed, or would a proper
dump-and-reload from 7.2 add the dependancy information anyway?
Yes, it's still needed: a normal dump/reload doesn't fix the problem.
findoidjoins: again, it's not clear what this module is for.
Bruce?
I don't think this should be a contrib at all. It's more like a
developer tool.
lo: another special data type. Is its functionality required
anymore? It appears to be a workaround to some limitations of
our large object interface which may no longer exist.
No, it's still needed I think. It's somewhat redundant with vacuumlo
apparently? The functionality of both should be incorporated into the
backend somehow, I'd think.
pg_dumplo: is this still required for pg large objects? If
so, can't we integrate it into the core? utilities/
I believe pg_dump has this functionality, with -O.
reindexdb: now obsolete per the REINDEX {database} command.
Remove from contrib.
No, this is different than REINDEX DATABASE.
--
Alvaro Herrera (alvherre[a]surnet.cl)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] The Contrib Roundup (long)
lo: another special data type. Is its functionality required anymore? It appears to be a workaround to some limitations of our large object interface which may no longer exist. I **think** the lo datatype is for ODBC binary access. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] The Contrib Roundup (long)
adddepend: is this still needed, or would a proper dump-and-reload from 7.2 add the dependancy information anyway? No, a 7.2 to 7.3 or later upgrade will not have full dependency information using pg_dump. That said, I would abandon the module anyway. I don't recall testing it for a 7.2 to 8.0 upgrade, let alone to 8.1. It's probably been broken in some way by now (table spaces?) -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] The Contrib Roundup (long)
Andrew, userlocks is just a very thin interface to functionality that's really in the backend. What's left in contrib/userlock probably isn't even copyrightable in any case. The best bet is probably to re-implement it in the backend directly. Removing it certainly isn't a good idea; the functionality is important. Hmm. It needs to be re-written from scratch then so that we can remove the GPL, or if you can get an attorney to say it's not copyrightable ... (It doesn't rely on per-record OIDs either.) Ah, I misread the code then. It still seems like application code to me, but I'll happily admit to not really understanding it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] adding new pages bulky way
* Tom Lane [EMAIL PROTECTED] [07.06.2005 07:59]: Why bother? Just write each page when you need to --- there's no law that says you must use P_NEW. This means 2 things: 1) I cannot mix P_NEW and exact-number ReadBuffer() calls; 2) thus, I have to track next-block-number myself. Is it so? BTW, are there any differences in buffer seeking speed, if buffer block-numbers are mixed and if they're not (i.e. P_NEW is used)? -- Victor Y. Yegorov ---(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] The Contrib Roundup (long)
Joshua D. Drake [EMAIL PROTECTED] writes: lo: another special data type. Is its functionality required anymore? It appears to be a workaround to some limitations of our large object interface which may no longer exist. I **think** the lo datatype is for ODBC binary access. Yes, ISTR needing to install it to use ODBC BLOBs. I wonder if it should be packaged with the ODBC driver instead of being in contrib/? -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] adding new pages bulky way
On Tue, Jun 07, 2005 at 07:52:57PM +0300, Victor Y. Yegorov wrote: * Tom Lane [EMAIL PROTECTED] [07.06.2005 07:59]: Why bother? Just write each page when you need to --- there's no law that says you must use P_NEW. This means 2 things: 1) I cannot mix P_NEW and exact-number ReadBuffer() calls; Huh, why? You need to grab the relation extension block (LockRelationForExtension in CVS tip). -- Alvaro Herrera (alvherre[a]surnet.cl) [PostgreSQL] is a great group; in my opinion it is THE best open source development communities in existence anywhere.(Lamar Owen) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Proposed toast info extraction function for disaster recovery
I was just helping out some Red Hat colleagues who were trying to deal with a corrupted database in which pg_dump was failing like so: pg_dump: Error message from server: ERROR: missing chunk number 3 for toast value 205026 Frequently the best recovery action for this involves finding and deleting the row that references the TOAST value ... but there is no good way to find that row. AFAIK you have to engage in a very tedious binary search to see where you get the error and where you don't. On a large table this is just not fun at all; especially if you have or think you may have multiple corrupted rows. It strikes me that it'd be worth defining a function that could extract the toast OID of any toasted datum, along the lines of function pg_get_toast_id(anyelement) returns oid When faced with such a problem, you could do select ctid from my_giant_table where pg_get_toast_id(a_toastable_column) = 205026 or pg_get_toast_id(another_toastable_column) = 205026 ...; This would involve a seqscan since there'd be no way to index it, but at least it'd be only one seqscan rather than repeated trials. I'd envision making the thing return NULL if given a varlena value that happened not to be toasted out-of-line. If given a non-varlena data type, it could either return NULL or raise an error, depending on whether you think that represents pilot error or not. Comments? Anyone else ever wished they had this? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] adding new pages bulky way
* Alvaro Herrera [EMAIL PROTECTED] [08.06.2005 00:39]: Huh, why? You need to grab the relation extension block (LockRelationForExtension in CVS tip). Really? Didn't knew that. Consider: 1) I add 2 pages to the newly-created relation using P_NEW as BlockNumber; 2) then I do LockRelationForExtension; ReadBuffer(135) and UnockRelationForExtension. What BlockNumber will be assigned to the buffer, if I call ReadBuffer(P_NEW) now? 136? BTW, is it OK to say BlockNumber is assigned to buffer? -- Victor Y. Yegorov ---(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] I am up-to-date
On Mon, 2005-06-06 at 22:09 -0400, Bruce Momjian wrote: After months of being behind, I have gone through my entire mailbox and addressed all the patches held over from 8.0beta. I have loaded up the patch queue and will apply them in a day or two. http://momjian.postgresql.org/cgi-bin/pgpatches Some of the patches lack documentation which I will add, and some have multiple versions in the queue because I need the descriptions from earlier versions. I believe that the Bgwriter behaviour patch from 1 Jan is no longer valid against cvstip, since it has been superceded by other patches. We're safe to remove it from the patch queue. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Visibility issue with pg_table_is_visible
A week ago, I had a discussion with AndrewSN (Andrew Sullivan, I
suppose) on #postgresql IRC channel about the following issue.
I have a function, ss_info(text, text) which stores/replaces given key
and value in ss_info temporary table; the table is created unless exists
yet. The function looked like this:
CREATE OR REPLACE FUNCTION ss_info(text, text) RETURNS text AS '
DECLARE
_x integer;
BEGIN
SELECT 1 INTO _x FROM pg_class
WHERE relname = ''ss_info''
AND relkind = ''r''
AND table_is_visible(oid);
IF NOT FOUND THEN
EXECUTE ''CREATE TEMP TABLE ss_info (var text, value text)
WITHOUT OIDS'';
ELSE
EXECUTE ''DELETE FROM ss_info WHERE var = ''||quote_literal($1);
END IF;
EXECUTE ''INSERT INTO ss_info VALUES ('' ||
quote_literal($1) || '', '' || coalesce(quote_literal($2),
''NULL'') ||
'')'';
RETURN $2;
END' LANGUAGE 'plPgSQL';
And a similar function ss_info(text) getting a value by key from that
table.
Sometimes, very infrequently (up to several times from nearly
10,000..20,000 executions a week), I beheld the following error
on 8.0.1-3 (not sure about 7.4.x):
ERROR: cache lookup failed for relation 1522203
CONTEXT: SQL statement SELECT 1 FROM pg_class WHERE relname = 'ss_info' AND
relkind = 'r' AND pg_table_is_visible(oid)
Here's the end of our discussion:
AndrewSN the problem is this:
AndrewSN that query on pg_class will first find the oid of _every_
ss_info table, including ones in other backends,
AndrewSN and then call pg_table_is_visible
AndrewSN _but_
AndrewSN if another backend exits or drops the table, its ss_info table
can be gone from SnapshotNow even though it's still visible in the query
snapshot
AndrewSN and pg_table_is_visible uses the syscache, which is always in
SnapshotNow
fduch-m AndrewSN: Much clearer now... Is there any workaround?
AndrewSN hm, there might be another way to form the query that doesn't
have the same risk
AndrewSN maybe check for has_schema_privilege(relnamespace,'USAGE')
rather than pg_table_is_visible
AndrewSN no, that's not enough in itself
AndrewSN how about: WHERE relname='ss_info' AND relkind='r' AND CASE
WHEN has_schema_privilege(relnamespace,'USAGE') THEN pg_table_is_visible(oid)
ELSE FALSE END;
AndrewSN that checks visibility only when we already know the namespace
is accessible, so temp schemata of other backends will already be excluded
(since we have no permissions on them)
AndrewSN (the CASE is needed to control evaluation order)
fduch-m AndrewSN: Won't has_schema_privilege have a similar effect when
other temp namespace is also dropped already?
AndrewSN temp namespaces aren't dropped, they're recycled instead
AndrewSN (you'll see them accumulate in pg_namespace if you look)
AndrewSN there's never more than max_connections of them, though,
because they're named by the backend slot number
AndrewSN fduch-m: btw, you should post this issue to the mailing lists,
for the benefit of those of the developers that don#t do irc
fduch-m AndrewSN: Thanks, I'll try it. But I'm not sure I can certainly
reproduce the same case...
After that I modified my functions as suggested, and never seen that
error anymore, so Andrew seems right. I'd like to thank him once again
and share this issue with other developers for solving/documenting/etc.
--
Fduch M. Pravking
---(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] Proposed toast info extraction function for disaster recovery
On Tue, Jun 07, 2005 at 05:48:31PM -0400, Tom Lane wrote: Comments? Anyone else ever wished they had this? Hmm. There's another problem which shows up when the server throws an error about an invalid allocation request. This also happens when a varlena field is corrupted, but you don't have the toast id because the error message doesn't say. I guess one way to solve it would be to add errcontext() calls within toast realms. Now that I think about it, maybe my problem is not related to TOAST at all, but to a corrupted varlena field. So if the corruption does not involve toasting, I'm in the same position as before, i.e. I haven't found out what is the corrupted tuple. -- Alvaro Herrera (alvherre[a]surnet.cl) Saca el libro que tu religión considere como el indicado para encontrar la oración que traiga paz a tu alma. Luego rebootea el computador y ve si funciona (Carlos Duclós) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Visibility issue with pg_table_is_visible
On 2005-06-07, Alexander M. Pravking [EMAIL PROTECTED] wrote: A week ago, I had a discussion with AndrewSN (Andrew Sullivan, I suppose) No, that was me. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Visibility issue with pg_table_is_visible
Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-06-07, Alexander M. Pravking [EMAIL PROTECTED] wrote: A week ago, I had a discussion with AndrewSN (Andrew Sullivan, I suppose) No, that was me. I know who Andrew Sullivan is, but who are *you* exactly? (Not to be too rude, but most of the people on this list use their full names.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] adding new pages bulky way
Victor Y. Yegorov [EMAIL PROTECTED] writes: * Alvaro Herrera [EMAIL PROTECTED] [08.06.2005 00:39]: Huh, why? You need to grab the relation extension block (LockRelationForExtension in CVS tip). Really? Didn't knew that. Consider: 1) I add 2 pages to the newly-created relation using P_NEW as BlockNumber; 2) then I do LockRelationForExtension; ReadBuffer(135) and UnockRelationForExtension. As things are set up at the moment, you really should not use P_NEW at all unless you hold the relation extension lock. (At least not for ordinary heap relations. An index access method could have its own rules about how to add blocks to the relation --- hash does for instance.) This is all pretty ugly in my view, and so I would not stand opposed to ideas about a cleaner design ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] linuxtag 2005
Are any PostgreSQL hackers planning to be at Linuxtag in Karlsruhe? -- ams ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
