Re: [HACKERS] pg_stats not getting updated....
hey tom and others
look at these
how is the sum of all tables != database
qe18=# SELECT
pg_stat_get_db_blocks_fetched(771773788),pg_stat_get_db_blocks_hit(771773788);
pg_stat_get_db_blocks_fetched |
pg_stat_get_db_blocks_hit
---+---
63787 |
61398
(1 row)
qe18=# SELECT sum(heap_blks_hit),sum(heap_blks_read)
from pg_statio_all_tables;
sum | sum
---+-
36200 | 942
(1 row)
as far as the code goes both are same
/*
* Process all table entries in the message.
*/
for (i = 0; i < msg->m_nentries; i++)
{
tabentry = (PgStat_StatTabEntry *)
hash_search(dbentry->tables,
(void *) &(tabmsg[i].t_id),
HASH_ENTER, &found);
if (!found)
{
/*
* If it's a new table entry, initialize counters
to the
* values we just got.
*/
tabentry->numscans = tabmsg[i].t_numscans;
tabentry->tuples_returned =
tabmsg[i].t_tuples_returned;
tabentry->tuples_fetched =
tabmsg[i].t_tuples_fetched;
tabentry->tuples_inserted =
tabmsg[i].t_tuples_inserted;
tabentry->tuples_updated =
tabmsg[i].t_tuples_updated;
tabentry->tuples_deleted =
tabmsg[i].t_tuples_deleted;
tabentry->blocks_fetched =
tabmsg[i].t_blocks_fetched;
tabentry->blocks_hit = tabmsg[i].t_blocks_hit;
tabentry->destroy = 0;
}
else
{
/*
* Otherwise add the values to the existing entry.
*/
tabentry->numscans += tabmsg[i].t_numscans;
tabentry->tuples_returned +=
tabmsg[i].t_tuples_returned;
tabentry->tuples_fetched +=
tabmsg[i].t_tuples_fetched;
tabentry->tuples_inserted +=
tabmsg[i].t_tuples_inserted;
tabentry->tuples_updated +=
tabmsg[i].t_tuples_updated;
tabentry->tuples_deleted +=
tabmsg[i].t_tuples_deleted;
tabentry->blocks_fetched +=
tabmsg[i].t_blocks_fetched;
tabentry->blocks_hit += tabmsg[i].t_blocks_hit;
}
/*
* And add the block IO to the database entry.
*/
dbentry->n_blocks_fetched +=
tabmsg[i].t_blocks_fetched;
dbentry->n_blocks_hit += tabmsg[i].t_blocks_hit;
}
///
any ideas why is this happening...
thx
Himanshu
__
Discover Yahoo!
Stay in touch with email, IM, photo sharing and more. Check it out!
http://discover.yahoo.com/stayintouch.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
Hello all, (B (BI am interested in how to "Compress WAL entries". (BThen, I study the source now, and read this discussion. (B (BThere are some questions. (B (B1. (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 log" (Bto generate a log with "hole"? (B (B2. (BTom Lane wrote: (B> The overhead needed is only 2 bytes to show the number of (B> bytes removed. (B (BIn "whole buffer log", there is a page header that includes offset of (B"hole" (lower and upper). If we use that information, we don't need (Bany overhead, do we? (B (B# Sorry for my bad english.. (B (B-- (BJunji Teramoto (B (B---(end of broadcast)--- (BTIP 8: explain analyze is your friend
Re: [HACKERS] Speeding up the Postgres lexer
On Wed, 2005-05-25 at 11:28 +1000, Neil Conway wrote: > Tom Lane wrote: > > Dunno. Depending on such a thing would require depending on a new flex > > version, and seeing that the flex guys haven't put out a new release > > since the badly broken 2.5.31 more than 2 years ago, I wouldn't hold > > my breath waiting for one we can use. > > It should be easy enough to check for the presence of the flag at > configure-time and only use it if flex implements it. Prior to reading > Simon's message I was actually thinking of hacking up the same thing :), > so I agree it is probably worth doing. Neil, I'll be doing this after the beta freeze is announced. If you wanted to do this before then, I wouldn't stop you. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 04 June 2005 16:46 > To: Mark Cave-Ayland (External) > Cc: pgsql-hackers@postgresql.org > Subject: Re: Quick-and-dirty compression for WAL backup blocks (cut) > I've completed a test run for this (it's essentially MySQL's > sql-bench done immediately after initdb). What I get is: > > CVS tip of 6/1: ending WAL offset = 0/A364A780 = 2741282688 > bytes written > > CVS tip of 6/2: ending WAL offset = 0/8BB091DC = 2343604700 > bytes written > > or about a 15% savings. This is with a checkpoint_segments > setting of 30. One can presume that the savings would be > larger at smaller checkpoint intervals and smaller at larger > intervals, but I didn't try more than one set of test conditions. > > I'd say that's an improvement worth having, especially > considering that it requires no net expenditure of CPU time. > But the table is certainly still open to discuss more > complicated approaches. Hi Tom, Thanks for the numbers. I've just been across to the OSDL STP website and it appears that the automatic nightly PostgreSQL CVS builds set up by Mark have been broken since the middle of April :( A brief look shows that compilation of the PostgreSQL sources is failing with lots of errors and warnings. I don't think I can justify the time at the moment to go and look at this myself, however I thought I'd post to -hackers as a heads up in case anyone else could pick this up. Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] thw rewriter and default values, again
Jaime Casanova wrote:
I know you're right, but -when dealing with updateable views- doing
that implies to add a lot of time altering views when base table
change, and of course we maybe don't want all views get that values.
Sorry for the late posting on this thread.
Might there be any way to have something like ...SET DEFAULT
pg_same_as('public','foo','col1').
Hmm - you'd need pg_same_as_int/text/etc. depending on the return-type -
still not ideal.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
В Втр, 24/05/2005 в 00:06 -0400, Tom Lane пишет:
> Joe Conway <[EMAIL PROTECTED]> writes:
> > Markus Bertheau wrote:
> >> why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
> >> ARRAY[] resp. '{}'?
>
> > Why would you expect an empty array instead of a NULL?
>
> I think he's got a good point, actually. We document the ARRAY-with-
> parens-around-a-SELECT syntax as
>
> The resulting one-dimensional array will have an element for
> each row in the subquery result, with an element type matching
> that of the subquery's output column.
>
> To me, that implies that a subquery result of no rows generates a
> one-dimensional array of no elements, not a null array.
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
Markus
--
Markus Bertheau ☭ <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
Junji TERAMOTO <[EMAIL PROTECTED]> writes: > In the XLogInsert(), it makes two kinds of logs, "whole buffer(page) > log" and "partial buffer log", isn't it? Is it only "who buffer log" > to generate a log with "hole"? Right. > Tom Lane wrote: >> The overhead needed is only 2 bytes to show the number of >> bytes removed. > In "whole buffer log", there is a page header that includes offset of > "hole" (lower and upper). If we use that information, we don't need > any overhead, do we? No, because the WAL code cannot assume that all pages follow the convention that pd_lower and pd_upper represent the boundaries of free space. (As a counterexample: index metapages don't always do that.) I think the transformation has to be guaranteed lossless, which means that at a minimum you'd need to check whether the data in between pd_lower and pd_upper really is zeroes. So the irreducible minimum overhead is 1 bit to tell whether you compressed or not. Considering alignment requirements, you might as well expend a couple of bytes and keep it simple. (In the patch as committed, I ended up using 4 bytes --- a uint16 hole start and a uint16 hole length --- because it kept the code simple. The alignment requirements mean the extra 2 bytes are usually free anyway.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] thw rewriter and default values, again
On 6/6/05, Richard Huxton wrote:
> Jaime Casanova wrote:
> > I know you're right, but -when dealing with updateable views- doing
> > that implies to add a lot of time altering views when base table
> > change, and of course we maybe don't want all views get that values.
>
> Sorry for the late posting on this thread.
>
> Might there be any way to have something like ...SET DEFAULT
> pg_same_as('public','foo','col1').
>
> Hmm - you'd need pg_same_as_int/text/etc. depending on the return-type -
> still not ideal.
>
Actually, i try to do that but i need the function to be polymorphic
and because polymorphic functions needs to receive at least one
polymorphic argument that teach about the return type of the function
i can't go ahead with this idea.
About the code i talk earler in this thread i solve the problem with
the update to serial columns.
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Solution proposal for TODO item "Clients: sequences"
I would suggest adding it to \ds or create a \ds+ so that it is in common with the rest of the commands. -Jonah Gevik babakhani wrote: So what would be your suggestion in order to add this functionality? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: Sunday, June 05, 2005 3:17 AM To: Gevik Babakhani Cc: [EMAIL PROTECTED] Org Subject: Re: [HACKERS] Solution proposal for TODO item "Clients: sequences" It would be nice to add the current sequence value to \ds, but the existing psql query would have to be overhauled to do that. Right now the same query does all the work for most \d commands. --- Gevik Babakhani wrote: Dear People, Hereby a proposal for the TODO item "Clients: Have psql show current values for a sequences". I have added a new slash command to psql client "\sq" for showing the last values of the existing sequences in the public schema. The code is only tested on rh9. The new files are sequence_info.c and sequence_info.h I would like to hear your comments. (Be gentle this is my first) The sources can be downloaded from http://www.truesoftware.net/psql/ Regards, Gevik SCREEN OUTPUT [EMAIL PROTECTED] psql]$ ./psql Welcome to psql 8.0.0beta5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit gevik=# \sq Current sequence values Sequence | Last value ---+ mytableid | 5 seq1 | 1 (2 rows) gevik=# ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PGDN source browser
Gevik, You still didn't answer my question as to why you're reinventing the wheel. Why not just work on updating techdocs instead? -Jonah Tom Flavel wrote: On 04/06/2005 22:59:19, Gevik babakhani wrote: Dear all, The PostgreSQL Developer Network's Source Browser (beta1) is ready. If you got the time to check it for a moment, please do not hesitate to send Firstly, good that you're asking for comments (I assume as an attempt to work with people), but starting an entirley seperatre "developer network" seems rather like reinventing the wheel. Why not invest your time in extending the existing rersources, rather than starting from scratch? Other than saving me from downloading the source (which I keep around anyway, mostly for ease of grepping for things the documentation cant be expected to cover), I see no advantage to using this in it's current form. To me, the point of a source browsing system is that it provides information which a directory structure can't provide. Off the top of my head: * CVS head. Without this, it's always going to be irrelevant to developers who commit to postgresql itself. * CVS history. If you're targeting this at developers, I'd think this is important. * Referencing between function calls and definitions (with ctags, perhaps. Doxygen-style browsers do this.) To me, this is the single only advantage of presenting source code in HTML: ease of navigation by hyperlinks. Apart from that, web pages are pretty inconvenient (to me, at least). * Is a tree really appropiate? To compare two files (which is something I might want to use this for), that'd require a lot of scrolling to see where I am in the structure. * diff. Meanwhile, some aesthetic things which spring to mind: The syntax hilighting is confusing for non-.c files; quite a few are parsed incorrectly around comments, and hilighting applied to strange things in plain-text files. There is extreneous whitespace in the at the top by the line numbers' gutters. There's a off-by-one error in your loop to pad out the numbers: an extra space appears every power of 10. The "number of views" is irrelevant, as is the "...Source Browser" title on each page, which is unneccessary. Hope that helps, ---(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] graphical representaion of the catalogue
Dear All, I am sure this is all asked before, so forgive me… Does anyone know (besides the documentation) if there is a graphical representation of the pg catalog? Regards, Gevik.
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
В Пнд, 06/06/2005 в 10:44 -0400, Tom Lane пишет:
> Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:
> > By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
> > NULL.
>
> No, that doesn't follow ... we've traditionally considered '{}' to
> denote a zero-dimensional array.
But array_upper(ARRAY(SELECT 1 WHERE FALSE), 1) should return 0,
and array_upper(ARRAY[], 1), when the ARRAY[] syntax is supported,
should return 0.
Do I get that right?
Markus
--
Markus Bertheau ☭ <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] need help
For this information, you can go through the code. I would suggest reading through the catalog, heap access methods, storage manager, and executor. Does anyone know of any good high-level docs on this? Nithin Sontineni wrote: Hi, i want to know how create relation works and how the populated relation is stored in the form of pages and also when a tuple is inserted how page concept works in postgress. S.Nithin. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] graphical representaion of the catalogue
Is this really relevant to -hackers? I was thinking more along the lines of -general. Gevik babakhani wrote: Dear All, I am sure this is all asked before, so forgive me… Does anyone know (besides the documentation) if there is a graphical representation of the pg catalog? Regards, Gevik. ---(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] PGDN source browser
Regarding to update the techdocs and not reinveting the wheel, I am having a mailng with Robert Treat of (webmaster pg) in order to see what can be done.. any help is welcome... :) Regards, Gevik. -Original Message- From: Jonah H. Harris [mailto:[EMAIL PROTECTED] Sent: Monday, June 06, 2005 4:50 PM To: Tom Flavel Cc: Gevik babakhani; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] PGDN source browser Gevik, You still didn't answer my question as to why you're reinventing the wheel. Why not just work on updating techdocs instead? -Jonah Tom Flavel wrote: >On 04/06/2005 22:59:19, Gevik babakhani wrote: > > >>Dear all, >> >>The PostgreSQL Developer Network's Source Browser (beta1) is ready. >> >>If you got the time to check it for a moment, please do not hesitate to send >> >> > >Firstly, good that you're asking for comments (I assume as an attempt to >work with people), but starting an entirley seperatre "developer network" >seems rather like reinventing the wheel. Why not invest your time in >extending the existing rersources, rather than starting from scratch? > > >Other than saving me from downloading the source (which I keep around >anyway, mostly for ease of grepping for things the documentation cant be >expected to cover), I see no advantage to using this in it's current >form. > >To me, the point of a source browsing system is that it provides >information which a directory structure can't provide. Off the top of my >head: > >* CVS head. Without this, it's always going to be irrelevant to > developers who commit to postgresql itself. >* CVS history. If you're targeting this at developers, I'd think this is > important. >* Referencing between function calls and definitions (with ctags, > perhaps. Doxygen-style browsers do this.) To me, this is the single > only advantage of presenting source code in HTML: ease of navigation > by hyperlinks. Apart from that, web pages are pretty inconvenient (to > me, at least). >* Is a tree really appropiate? To compare two files (which is something > I might want to use this for), that'd require a lot of scrolling to > see where I am in the structure. >* diff. > > >Meanwhile, some aesthetic things which spring to mind: > >The syntax hilighting is confusing for non-.c files; quite a few are >parsed incorrectly around comments, and hilighting applied to strange >things in plain-text files. > >There is extreneous whitespace in the at the top by the line >numbers' gutters. There's a off-by-one error in your loop to pad out the >numbers: an extra space appears every power of 10. > >The "number of views" is irrelevant, as is the "...Source Browser" title >on each page, which is unneccessary. > >Hope that helps, > > > ---(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] PGDN source browser
Thanks! Gevik babakhani wrote: Regarding to update the techdocs and not reinveting the wheel, I am having a mailng with Robert Treat of (webmaster pg) in order to see what can be done.. any help is welcome... :) Regards, Gevik. -Original Message- From: Jonah H. Harris [mailto:[EMAIL PROTECTED] Sent: Monday, June 06, 2005 4:50 PM To: Tom Flavel Cc: Gevik babakhani; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] PGDN source browser Gevik, You still didn't answer my question as to why you're reinventing the wheel. Why not just work on updating techdocs instead? -Jonah Tom Flavel wrote: On 04/06/2005 22:59:19, Gevik babakhani wrote: Dear all, The PostgreSQL Developer Network's Source Browser (beta1) is ready. If you got the time to check it for a moment, please do not hesitate to send Firstly, good that you're asking for comments (I assume as an attempt to work with people), but starting an entirley seperatre "developer network" seems rather like reinventing the wheel. Why not invest your time in extending the existing rersources, rather than starting from scratch? Other than saving me from downloading the source (which I keep around anyway, mostly for ease of grepping for things the documentation cant be expected to cover), I see no advantage to using this in it's current form. To me, the point of a source browsing system is that it provides information which a directory structure can't provide. Off the top of my head: * CVS head. Without this, it's always going to be irrelevant to developers who commit to postgresql itself. * CVS history. If you're targeting this at developers, I'd think this is important. * Referencing between function calls and definitions (with ctags, perhaps. Doxygen-style browsers do this.) To me, this is the single only advantage of presenting source code in HTML: ease of navigation by hyperlinks. Apart from that, web pages are pretty inconvenient (to me, at least). * Is a tree really appropiate? To compare two files (which is something I might want to use this for), that'd require a lot of scrolling to see where I am in the structure. * diff. Meanwhile, some aesthetic things which spring to mind: The syntax hilighting is confusing for non-.c files; quite a few are parsed incorrectly around comments, and hilighting applied to strange things in plain-text files. There is extreneous whitespace in the at the top by the line numbers' gutters. There's a off-by-one error in your loop to pad out the numbers: an extra space appears every power of 10. The "number of views" is irrelevant, as is the "...Source Browser" title on each page, which is unneccessary. Hope that helps, ---(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] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly truncate
Tom Lane wrote: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > >> No, I don't think so. It doesn't seem to be something that enough > >> people use to risk the change in behavior --- it might break something > >> that was working. But, if folks want it backported we can do it. It is > >> only a change to properly do modulus for numeric. > > > Well, from my point of view it's an absolute mathematical error - i'd > > backport it. I can't see anyone relying on it :) > > Doesn't this patch break the basic theorem that > > a = trunc(a / b) * b + (a mod b) > > ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test=> select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test=> select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:
> By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
> NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array. A 1-D array of no elements is
'[1:0]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:
regression=# select '[1:0]={}' :: int[];
ERROR: upper bound cannot be less than lower bound
I think this should be a legal boundary case. In general, it should be
possible to form zero-size arrays of any number of dimensions.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Tom Lane wrote:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array. A 1-D array of no elements is
'[1:0]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:
regression=# select '[1:0]={}' :: int[];
ERROR: upper bound cannot be less than lower bound
I think this should be a legal boundary case. In general, it should be
possible to form zero-size arrays of any number of dimensions.
I've been playing with exactly this over the weekend. Of course, as
usual, the devil is in the details. For instance, using the above
notation, how would I specify a zero-element 1D array starting at a
lower bound index of 0? The answer following the above pattern would be:
select '[0:-1]={}'::int[];
You could not use '[0:0]={}'::int[], because that would be a one-element
array. I propose the following instead:
regression=# select '[1:]={}' :: int[];
int4
--
{}
(1 row)
regression=# select array_dims('[1:]={}' :: int[]);
array_dims
[1:]
(1 row)
In other words, an missing upper bound indicates zero elements.
Now the next question; what does a 2D zero-element array look like? I
think this makes sense:
regression=# select '[1:2][1:]={{},{}}'::int[];
int4
--
{}
(1 row)
Except (I think) array_out() should probably output something closer to
the input literal. Any thoughts on this?
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Markus Bertheau ☭ wrote:
В Пнд, 06/06/2005 в 10:44 -0400, Tom Lane пишет:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array.
But array_upper(ARRAY(SELECT 1 WHERE FALSE), 1) should return 0,
and array_upper(ARRAY[], 1), when the ARRAY[] syntax is supported,
should return 0.
Actually, consistent with my last post, I think array_upper() on a
zero-element array should return NULL. A zero-element array has a
defined lower bound, but its upper bound is not zero -- it is really
undefined.
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp.
Joe Conway wrote:
Actually, consistent with my last post, I think array_upper() on a
zero-element array should return NULL. A zero-element array has a
defined lower bound, but its upper bound is not zero -- it is really
undefined.
Just to clarify my response, this is what I propose:
regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
array_upper
-
2
(1 row)
regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
?column?
--
t
(1 row)
Joe
---(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] [SQL] ARRAY() returning NULL instead of ARRAY[]
В Пнд, 06/06/2005 в 08:58 -0700, Joe Conway пишет:
> Joe Conway wrote:
> > Actually, consistent with my last post, I think array_upper() on a
> > zero-element array should return NULL. A zero-element array has a
> > defined lower bound, but its upper bound is not zero -- it is really
> > undefined.
>
> Just to clarify my response, this is what I propose:
>
> regression=# select array_upper('[2][1:]={{},{}}'::int[],1);
> array_upper
> -
> 2
> (1 row)
>
> regression=# select array_upper('[2][1:]={{},{}}'::int[],2) IS NULL;
> ?column?
> --
> t
> (1 row)
Hmm, this gets really complicated and inconsistent. Complicated means
unusable. What about modifying the dimension syntax such that the second
number means number of elements instead of upper bound? That particular
problem would go away then, and array_upper('[0:0]={}'::int[]) can
return the correct 0 then.
What I'm actually worrying about is that array_upper(array(select 1
where false)) returns 0.
An option would be to drop the possibility to let the array start at
another index than 0. I don't know why it was decided to do that in the
first place. It seems a rather odd feature to me.
Markus
--
Markus Bertheau ☭ <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
On Mon, 6 Jun 2005, Tom Lane wrote: Junji TERAMOTO <[EMAIL PROTECTED]> writes: In "whole buffer log", there is a page header that includes offset of "hole" (lower and upper). If we use that information, we don't need any overhead, do we? No, because the WAL code cannot assume that all pages follow the convention that pd_lower and pd_upper represent the boundaries of free space. (As a counterexample: index metapages don't always do that.) I think the transformation has to be guaranteed lossless, which means that at a minimum you'd need to check whether the data in between pd_lower and pd_upper really is zeroes. So the irreducible minimum overhead is 1 bit to tell whether you compressed or not. Vacuum doesn't zero out the free space between lower and upper, it's just marked as unused, so a lossless compression becomes less efficient on tables that have free space released by vacuum in them. How about adding a flag to XLogRecData to indicate if the space between pd_lower and pd_upper is meaningful or not? The XLogInsert caller probably knows that. That way you could completely skip over the free space if it's not meaningful, saving even more cycles. - Heikki ---(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] PGDN source browser
Am Samstag, den 04.06.2005, 22:59 +0200 schrieb Gevik babakhani: > Dear all, > > > > The PostgreSQL Developer Networkʼs Source Browser (beta1) is ready. > > If you got the time to check it for a moment, please do not hesitate > to send your opinion. Hm. wouldn't an install of trac/postgres (http://trac.edgewall.com ) do much better w/ regard to syntax highliting, diff, ... Still someone would have to adapt the templates to the postgres.org design. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Vacuum doesn't zero out the free space between lower and upper, It does now ;-) > How about adding a flag to XLogRecData to indicate if the space between > pd_lower and pd_upper is meaningful or not? The XLogInsert caller probably > knows that. That way you could completely skip over the free space if > it's not meaningful, saving even more cycles. Hmm ... that might not be a bad idea. As far as I can think offhand, all the XLogInsert callers know very well what type of page they are working with, so they would always be able to set such a flag correctly. Would this be institutionalizing a particular approach to data compression in the XLogInsert API, though? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] graphical representaion of the catalogue
Gevik babakhani wrote: > Dear All, > > > > I am sure this is all asked before, so forgive me. > > Does anyone know (besides the documentation) if there is a graphical > representation of the pg catalog? Slide 65 in this presentation: http://candle.pha.pa.us/main/writings/pgsql/internalpics.pdf Let me know if you want the source. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
On Mon, 6 Jun 2005, Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: Vacuum doesn't zero out the free space between lower and upper, It does now ;-) Oh :). Does it affect vacuum performance? How about adding a flag to XLogRecData to indicate if the space between pd_lower and pd_upper is meaningful or not? The XLogInsert caller probably knows that. That way you could completely skip over the free space if it's not meaningful, saving even more cycles. Hmm ... that might not be a bad idea. As far as I can think offhand, all the XLogInsert callers know very well what type of page they are working with, so they would always be able to set such a flag correctly. Would this be institutionalizing a particular approach to data compression in the XLogInsert API, though? The "skip the free space" optimization is still useful and worthwhile even if we have a more sophisticated compression method for the rest of the page. - Heikki ---(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] regexp_replace
On Mon, Jun 06, 2005 at 12:02:18PM -0400, Bruce Momjian wrote:
>
> Patch removed because we already have this functionality.
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
:)
Cheers,
D
>
> ---
>
> a_ogawa00 wrote:
> >
> > This patch provides a new function regexp_replace.
> > regexp_replace extends a replace function and enables text search
> > by the regular expression. And, a back reference can be used within
> > a replace string.
> > (This patch for PostgreSQL 7.4.3)
> >
> > Function: regexp_replace(str, pattern, replace_str)
> > Retuen Type: text
> > Description: Replace all matched string in str.
> > pattern is regular expression pattern.
> > replace_str is replace string that can use '\1' - '\9', and
> > '\&'.
> > '\1' - '\9' is back reference to the n'th subexpression.
> > '\&' is matched string.
> >
> > (example1)
> > select regexp_replace('ABC-DEF', '(\\w+)-(\\w+)', '\\2-\\1')
> > result: DEF-ABC
> >
> > (example2)
> > update tab1 set col1 = regexp_replace(col1, '[A-Z]', '');
> >
> > ---
> > Atsushi Ogawa
> > [EMAIL PROTECTED]
> >
> > --- cut here ---
> >
> > *** ./src/backend/regex/regexec.c.orig Tue Jul 20 08:45:39 2004
> > --- ./src/backend/regex/regexec.c Tue Jul 20 08:49:36 2004
> > ***
> > *** 110,115
> > --- 110,116
> > regmatch_t *pmatch;
> > rm_detail_t *details;
> > chr*start; /* start of string */
> > + chr*search_start; /* search start of string */
> > chr*stop; /* just past end of
> > string */
> > int err;/* error code if any (0
> > none) */
> > regoff_t *mem;/* memory vector for
> > backtracking */
> > ***
> > *** 168,173
> > --- 169,175
> > pg_regexec(regex_t *re,
> >const chr *string,
> >size_t len,
> > + size_t search_start,
> >rm_detail_t *details,
> >size_t nmatch,
> >regmatch_t pmatch[],
> > ***
> > *** 219,224
> > --- 221,227
> > v->pmatch = pmatch;
> > v->details = details;
> > v->start = (chr *) string;
> > + v->search_start = (chr *) string + search_start;
> > v->stop = (chr *) string + len;
> > v->err = 0;
> > if (backref)
> > ***
> > *** 288,294
> > NOERR();
> > MDEBUG(("\nsearch at %ld\n", LOFF(v->start)));
> > cold = NULL;
> > ! close = shortest(v, s, v->start, v->start, v->stop, &cold, (int *)
> > NULL);
> > freedfa(s);
> > NOERR();
> > if (v->g->cflags & REG_EXPECT)
> > --- 291,298
> > NOERR();
> > MDEBUG(("\nsearch at %ld\n", LOFF(v->start)));
> > cold = NULL;
> > ! close = shortest(v, s, v->search_start, v->search_start, v->stop,
> > !&cold, (int *) NULL);
> > freedfa(s);
> > NOERR();
> > if (v->g->cflags & REG_EXPECT)
> > ***
> > *** 415,421
> >
> > assert(d != NULL && s != NULL);
> > cold = NULL;
> > ! close = v->start;
> > do
> > {
> > MDEBUG(("\ncsearch at %ld\n", LOFF(close)));
> > --- 419,425
> >
> > assert(d != NULL && s != NULL);
> > cold = NULL;
> > ! close = v->search_start;
> > do
> > {
> > MDEBUG(("\ncsearch at %ld\n", LOFF(close)));
> > *** ./src/backend/utils/adt/regexp.c.orig Tue Jul 20 08:50:08 2004
> > --- ./src/backend/utils/adt/regexp.cTue Jul 20 09:00:05 2004
> > ***
> > *** 80,116
> >
> >
> > /*
> > ! * RE_compile_and_execute - compile and execute a RE, caching if possible
> >*
> > ! * Returns TRUE on match, FALSE on no match
> >*
> > ! *text_re --- the pattern, expressed as an *untoasted* TEXT object
> > ! *dat --- the data to match against (need not be null-terminated)
> > ! *dat_len --- the length of the data string
> > ! *cflags --- compile options for the pattern
> > ! *nmatch, pmatch --- optional return area for match details
> >*
> > ! * Both pattern and data are given in the database encoding. We
> > internally
> > ! * convert to array of pg_wchar which is what Spencer's regex package
> > wants.
> >*/
> > ! static bool
> > ! RE_compile_and_execute(text *text_re, unsigned char *dat, int dat_len,
> > !
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > On Mon, 6 Jun 2005, Tom Lane wrote: >> Heikki Linnakangas <[EMAIL PROTECTED]> writes: >>> Vacuum doesn't zero out the free space between lower and upper, >> >> It does now ;-) > Oh :). Does it affect vacuum performance? I haven't tried to measure it ... but certainly it's not totally free. I'd be happy to rip that change out again. >> Would this be institutionalizing a particular approach to data >> compression in the XLogInsert API, though? > The "skip the free space" optimization is still useful and worthwhile > even if we have a more sophisticated compression method for the > rest of the page. Good point. OK, I'm hacking XLOG stuff now anyway so I'll see about making that happen. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] regexp_replace
David Fetter wrote: > On Mon, Jun 06, 2005 at 12:02:18PM -0400, Bruce Momjian wrote: > > > > Patch removed because we already have this functionality. > > 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 > :) Does anyone want to argue that this additional functionality is significant and deserves its own function or an additional argument to the existing function? -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] adding new pages bulky way
I need your advice. For on-disk bitmap I run a list of TIDs. TIDs are stored in pages as an array, page's opaque data holds an array of bits, indicating whether corresponding TID has been deleted and should be skipped during the scan. Pages, that contain TIDs list, are organized in extents, each extent has 2^N pages, where N is extent's number (i.e. 2nd extent will occupy 4 pages). Given that I know number of TIDs, that fit into one page, and the TID's sequential number, I can easily calculate: - extent number TID belongs to; - page offset inside that extent, and; - TID place in the page. At the moment, I store BlockNumber of the extent's first page in the metapage and allocate all pages that belongs to that extent sequentially. I need to do so to minimize number of page reads when searching for the TID in the list; I'll need to read 1 page at most to find out TID at given position during the scan. I hope you understood the idea. This also means, that while extent's pages are being added this way, no other pages can be added to the index. And the higher is extent's number, the more time it'll take to allocate all pages. The question is: allocating pages this way is really ugly, I understand. Is there some API that would allow allocating N pages in the bulk way? Maybe this is a know problem, that has been already solved before? Any other ideas? Thanks in advance! -- Victor Y. Yegorov ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: additional system views
Bruce, > I do like this idea. Can you make a general patch? Do others like the > idea of system tables showing error codes and keywords? Yes. However, I think the idea of additional system views has already been shot down in flames. Unless people think that it's reasonable to have a system view for error codes and not one for, say, operators? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: additional system views
Josh Berkus wrote: > Bruce, > > > I do like this idea. Can you make a general patch? Do others like the > > idea of system tables showing error codes and keywords? > > Yes. However, I think the idea of additional system views has already been > shot down in flames. > Unless people think that it's reasonable to have a system view for error > codes > and not one for, say, operators? 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. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
Christopher Kings-Lynne wrote: What would be absolutely ideal is a reset connection command, plus some way of knowing via the protocol if it's needed or not. And a way of notifying the client that a reset has happened. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Suggestion: additional system views
On Monday 06 June 2005 17:35, Bruce Momjian wrote:
> Josh Berkus wrote:
> > Bruce,
> >
> > > I do like this idea. Can you make a general patch? Do others like the
> > > idea of system tables showing error codes and keywords?
> >
> > Yes. However, I think the idea of additional system views has already
> > been shot down in flames.
> >
> > Unless people think that it's reasonable to have a system view for error
> > codes and not one for, say, operators?
>
> 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.
Would this include a new view for showing user priviliges, rather than the
current method of incense and chanting that are needed to divine the meaning
of pg_class.relacl ?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] Suggestion: additional system views
Robert Treat wrote: > On Monday 06 June 2005 17:35, Bruce Momjian wrote: > > Josh Berkus wrote: > > > Bruce, > > > > > > > I do like this idea. Can you make a general patch? Do others like the > > > > idea of system tables showing error codes and keywords? > > > > > > Yes. However, I think the idea of additional system views has already > > > been shot down in flames. > > > > > > Unless people think that it's reasonable to have a system view for error > > > codes and not one for, say, operators? > > > > 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. > > Would this include a new view for showing user priviliges, rather than the > current method of incense and chanting that are needed to divine the meaning > of pg_class.relacl ? The new tables have to do with error messages and keywords. It has nothing to do with permissions. -- 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 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] Added rtree/box TODO item
I have added the unresolved issue referenced here to TODO: http://archives.postgresql.org/pgsql-bugs/2005-01/msg00306.php It relates to the use of rtree indexing: * Fix incorrect rtree results due to wrong assumptions about "over" operator semantics [rtree] -- 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 8: explain analyze is your friend
Re: [HACKERS] unicode upper/lower functions
Yes, Thank you! :) .. John > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 07, 2005 10:07 AM > To: John Hansen > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] unicode upper/lower functions > > > I think we have decided to use the ICU library to implement > multiple locales. > > -- > - > > John Hansen wrote: > > Hi list, > > > > > > Attached for your perusal, unicode versions of upper/lower, > that work > > independent of locale except for the following languages: > > > > Turkish, Azeri, and Lithuanian. > > There are 15 locale specific cases in total not covered. > > > > > > -- > > John Hansen <[EMAIL PROTECTED]> > > GeekNET > > [ Attachment, skipping... ] > > > > > ---(end of > > broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > -- > 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] unicode upper/lower functions
I think we have decided to use the ICU library to implement multiple locales. --- John Hansen wrote: > Hi list, > > > Attached for your perusal, unicode versions of upper/lower, that work > independent of locale except for the following languages: > > Turkish, Azeri, and Lithuanian. > There are 15 locale specific cases in total not covered. > > > -- > John Hansen <[EMAIL PROTECTED]> > GeekNET [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] unicode upper/lower functions
... Except,.. It was never decided if the 'C' locale optimisations was going to be removed if/when implementing ICU. Tho I think the conclusion was a postgresql.conf parameter to enable/disable the optimisations. Either way, this code is now obsolete. ... John > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 07, 2005 10:07 AM > To: John Hansen > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] unicode upper/lower functions > > > I think we have decided to use the ICU library to implement > multiple locales. > > -- > - > > John Hansen wrote: > > Hi list, > > > > > > Attached for your perusal, unicode versions of upper/lower, > that work > > independent of locale except for the following languages: > > > > Turkish, Azeri, and Lithuanian. > > There are 15 locale specific cases in total not covered. > > > > > > -- > > John Hansen <[EMAIL PROTECTED]> > > GeekNET > > [ Attachment, skipping... ] > > > > > ---(end of > > broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > -- > 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 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] unicode upper/lower functions
John Hansen wrote: > ... Except,.. It was never decided if the 'C' locale optimisations was > going to be removed if/when implementing ICU. Uh, why would we remove it? Oh, meaning if the locale is C we bypass locale lookups? I think we will have to see what performance we have with things. > Tho I think the conclusion was a postgresql.conf parameter to > enable/disable the optimisations. > Either way, this code is now obsolete. Thanks. -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] regexp_replace
Bruce Momjian wrote:
> David Fetter wrote:
> > On Mon, Jun 06, 2005 at 12:02:18PM -0400, Bruce Momjian wrote:
> > >
> > > Patch removed because we already have this functionality.
> >
> > 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
> > :)
>
> Does anyone want to argue that this additional functionality is
> significant and deserves its own function or an additional argument to
> the existing function?
Oracle10g has a similar functionality. The name is regexp_replace.
There is the following usages in this functionality.
- Format the ZIP code and the telephone number, etc.
Example: select regexp_replace('111222', '(\\d{3})(\\d{3})(\\d{4})',
'(\\1) \\2-\\3');
result: (111) 222-
- Delete an unnecessary white space.
Example: select regexp_replace('A B C', '\\s+', ' ');
result: A B C
I think that the usage increases if "replace all" or "replace first" can be
specified to this function.
regards,
---
Atsushi Ogawa
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] adding new pages bulky way
On Mon, Jun 06, 2005 at 10:59:04PM +0300, Victor Y. Yegorov wrote: > The question is: allocating pages this way is really ugly, I understand. Is > there some API that would allow allocating N pages in the bulk way? > Maybe this is a know problem, that has been already solved before? > Any other ideas? I don't understand your question. What's the problem with holding the extend lock for the index relation while you extend it? Certainly you want only a single process creating a new extent in the index, right? I guess the question is when are the extents created, and what concurrency do you expect from that operation. -- Alvaro Herrera () "La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] regexp_replace
On Tue, Jun 07, 2005 at 09:35:56AM +0900, a_ogawa wrote:
>
> Bruce Momjian wrote:
> > David Fetter wrote:
> > > On Mon, Jun 06, 2005 at 12:02:18PM -0400, Bruce Momjian wrote:
> > > >
> > > > Patch removed because we already have this functionality.
> > >
> > > 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 :)
> >
> > Does anyone want to argue that this additional functionality is
> > significant and deserves its own function or an additional
> > argument to the existing function?
>
> Oracle10g has a similar functionality. The name is regexp_replace.
> There is the following usages in this functionality.
> - Format the ZIP code and the telephone number, etc.
>Example: select regexp_replace('111222', '(\\d{3})(\\d{3})(\\d{4})',
> '(\\1) \\2-\\3');
> result: (111) 222-
> - Delete an unnecessary white space.
>Example: select regexp_replace('A B C', '\\s+', ' ');
> result: A B C
>
> 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?
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] unicode upper/lower functions
Bruce Momjian wrote: > John Hansen wrote: > > ... Except,.. It was never decided if the 'C' locale > optimisations was > > going to be removed if/when implementing ICU. > > Uh, why would we remove it? Oh, meaning if the locale is C > we bypass locale lookups? I think we will have to see what > performance we have with things. Uhh, not quite: If locale is 'C' the current assumption is 7-bit ASCII for upper/lower/initcap. ICU is capable of properky doing upper/lower/initcap except for the cases described in this (obsolete) patch. > > > Tho I think the conclusion was a postgresql.conf parameter to > > enable/disable the optimisations. > > Either way, this code is now obsolete. > > Thanks. > > -- > 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] [Feed-back] Installing PostgreSQL 8.0.0 on SPARC/Solaris.
I have waded through this email and the suggestion seems to be that on Solaris we should add rpathdir based on the LD_RUN_PATH value at the time of compile. While this might be common on Solaris, I am not excited about implementing it because it changes compile behavior based on an environment variable, and this is typically not done on other platforms. I have also not seen similar complains from other Solaris users. If that changes, we can revisit the issue. --- Rolf Sponsel wrote: > Okay, I've now succeeded to build with > default runtime paths built/linked in, > although not using the optimal solution > (see end of this message). > > Please see further comments in-lined below ... > > Rolf Sponsel, 2005-02-04 23:26 GMT +01:00, wrote: > > > > > Tom Lane, 2005-02-01 04:45 GMT +01:00, wrote: > > > >> Rolf Sponsel <[EMAIL PROTECTED]> writes: > >> > >>> From my understanding, the preferred way > >>> for Solaris is to only set LD_RUN_PATH, > >>> and avoid setting LD_LIBRARY_PATH, at > >>> link-time. This is what I usually do. > > > > And usually works very well. > > > >> No, the preferred thing is to set -rpath within the executable, > > When using the Sun linker the preferred way is to use the '-R' flag. > > >> which we do already (see Makefile.solaris). > > Yes, you do (via "rpath = -Wl,-R$(rpathdir)" > but you fail to set the proper library paths, > i.e. the value of rpathdirs (which indirectly > was confirmed by Tom Lane). > > > > > ... DELETED ... > > > > I you get the '-R' option right, for the Solaris Platform, > > You have gotten the '-R' flag right in Makefile.solaris, > but fail to feed it with the correct argument(s), as > mentioned above. > > > I guess(!?) the Sun linker takes care of LD_RUN_PATH etc. > > No need to guess anymore. Here is the answer, taken from > the Sun ld man page (for Solaris 7); an this is why the > build scripts fail to incorporate the default runtime > paths explicitly specified by me via LD_RUN_PATH: > > ... > LD_RUN_PATH > An alternative mechanism for specifying a runpath > to the link-editor (see -R option). If both > LD_RUN_PATH and the -R option are specified, -R > supersedes. > ... > > > > >>It's possible that you need to > >> modify rpathdir to include /usr/local/ssl/lib and /usr/local/lib, > > Correct! One needs to modify the "rpathdir", although I > don't know how to do this, and this should be taken care > of automagically by the build scripts. > > > Well, if nothing else, I'd take this "possibility" > > as an indication of a "broken" configure process. > > > > Sorry, never heard of rpathdir (on Solaris)!? > > Okay, I see, this is not a Solaris specific > parameter, it's a build related parameter. > > >> but I'd think that indicates fairly serious brain damage in Solaris' > >> runtime loader. > > > > > > I'd be interested in knowing from what > > point of view you make that conclusion? > > > > ... DELETED ... > > > > And I really hope I didn't just > > suffer from a "brain outage"!!? :-) > > Seems like I had a tiny one anyhow :-) > > * * * * * > > BUG 1: > > When building postgresql-8.0.0 on SPARC/Solaris 7, > compiling with gcc-3.2.2, and linking with the Sun > linker '/usr/ccs/bin/ld', no default runtime paths > (other than '/usr/local/pgsql/lib') are "hard-wired" > into the executables and shared libraries. > > This regardless of whether runtime paths have been > explicitly specified via LD_RUN_PATH, or not. > > FIX 1: (Preferred) > > Make sure to provide necessary runtime paths to the > linker, e.g. via the rpathdir macro/variable. > > In order to provide additional flexibility, and for > becoming backward compatible with comon procedures > on the Solaris platform, it would be even better to > derive the value for rpathdir from the environment > variable LD_RUN_PATH whenever it's been defined. > > * * * > > BUG 2: > > The current configure script doesn't automagically > detect an OpenSSL installation that resided in the > default location (i.e. '/usr/local/ssl', when built > from source with gcc on Solaris), without having to > explicitly specify the location of OpenSSL via the > configure options '--with-libraries=/usr/local/ssl/lib' > and '--with-includes='/usr/local/ssl/include' when > enabling SSL/TLS support via '--with-openssl'. > > FIX 2: (Preferred) > > To be considered "well-behaving" it should manage > to automagically detect OpenSSL installed in the > default location (and also supply a default runtime > path, to the OpenSSL libraries, to the Sun linker). > > For non-standard situations, when OpenSSL does not > reside in one of it's default locations, it would > be preferrable to have the possibility to specify > the location as an argument to the '--with-openssl' > option, e.g. like '--with-openssl=/non-std/loc/ssl'
Re: [HACKERS] [BUGS] BUG #1467: fe_connect doesn't handle EINTR right
Would someone comment on this bug report from February? I can confirm
the code is unchanged and is in function fe-connect.c::PQconnectPoll().
---
Florian Hars wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1467
> Logged by: Florian Hars
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.1
> Operating system: All
> Description:fe_connect doesn't handle EINTR right
> Details:
>
> The file pgsql/src/interfaces/libpq/fe-connect.c contains the code fragment
>
> retry_connect:
> if (connect(conn->sock, addr_cur->ai_addr,
> addr_cur->ai_addrlen) < 0)
> {
> if (SOCK_ERRNO == EINTR)
> /* Interrupted system call - just try again */
> goto retry_connect;
> }
>
> This is not in accordance with a strict legalistic reading of the POSIX
> spec, according to which connect is not restartable so that you have to use
> select or poll after connect returned with EINTR.
>
> See
> http://www.eleves.ens.fr:8080/home/madore/computers/connect-intr.html
> for the ugly details, your code should work on Linux, but not on Solaris or
> (Free|Open)BSD.
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
--
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 8: explain analyze is your friend
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[] resp.
Markus Bertheau ☭ wrote:
Hmm, this gets really complicated and inconsistent. Complicated means
unusable. What about modifying the dimension syntax such that the second
number means number of elements instead of upper bound? That particular
problem would go away then, and array_upper('[0:0]={}'::int[]) can
return the correct 0 then.
What I'm actually worrying about is that array_upper(array(select 1
where false)) returns 0.
An option would be to drop the possibility to let the array start at
another index than 0. I don't know why it was decided to do that in the
first place. It seems a rather odd feature to me.
Actually I like both of these ideas, and have advocated the second one
myself before. But it isn't backward compatible -- anyone else have an
opinion? SQL2003 actually specifies that an array *should* start at 1:
4.10.2 Arrays
An array is a collection A in which each element is associated with
exactly one ordinal position in A. If n is the cardinality of A, then
the ordinal position p of an element is an integer in the range 1 (one)
≤ p ≤ n.
Joe
---(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] libpq API incompatibility between 7.4 and 8.0
Is this a direction we want to explore --- using the SONAME as part of
the translation domain?
---
Martin Pitt wrote:
-- Start of PGP signed section.
> Hi!
>
> Bruce Momjian [2005-02-09 18:05 -0500]:
> > > However, I just stumbled across another problem: libpq3 and the new
> > > libpq4 use the same translation domain "libpq4", thus they cannot be
> > > installed in parallel. Can you please change the domain to "libpq4" as
> > > well? This should generally be done anyway, but at least we can live
> > > with one breakage of this rule (for libpq3), so that we don't need to
> > > mess with the old libpq3 package.
> >
> > Uh, what is the translation domain?
>
> In short, that is the unique name for a bunch of translations for a
> particular application or group of related applications. The key point
> is that the name of the file in
> /usr/share/locale//LC_MESSAGES/.mo and the call
>
> bindtextdomain ("", NULL)
>
> must use the same so that libintl can find the mo file.
> However, that means that all applications that are installed in
> parallel need a distinct domain. Since the whole point of SONAMes is
> to allow several different library API versions to be installed in
> parallel, every library API (i. e. SONAME) should have an unique
> domain, which can be achieved easiest by just appending the SONAME to
> the translation domain.
>
> In my test packages I used the following patch:
>
> diff -ruN postgresql-8.0.1-old/src/interfaces/libpq/fe-misc.c
> postgresql-8.0.1/src/interfaces/libpq/fe-misc.c
> --- postgresql-8.0.1-old/src/interfaces/libpq/fe-misc.c 2004-12-31
> 23:03:50.0 +0100
> +++ postgresql-8.0.1/src/interfaces/libpq/fe-misc.c 2005-02-07
> 22:55:13.177419296 +0100
> @@ -1133,7 +1133,7 @@
> {
> already_bound = 1;
> /* No relocatable lookup here because the binary could be
> anywhere */
> - bindtextdomain("libpq", getenv("PGLOCALEDIR") ?
> getenv("PGLOCALEDIR") : LOCALEDIR);
> + bindtextdomain("libpq4", getenv("PGLOCALEDIR") ?
> getenv("PGLOCALEDIR") : LOCALEDIR);
> }
>
> return dgettext("libpq", msgid);
> diff -ruN postgresql-8.0.1-old/src/interfaces/libpq/nls.mk
> postgresql-8.0.1/src/interfaces/libpq/nls.mk
> --- postgresql-8.0.1-old/src/interfaces/libpq/nls.mk2005-01-14
> 09:57:06.0 +0100
> +++ postgresql-8.0.1/src/interfaces/libpq/nls.mk2005-02-07
> 22:54:54.770217616 +0100
> @@ -1,5 +1,5 @@
> # $PostgreSQL: pgsql/src/interfaces/libpq/nls.mk,v 1.20 2005/01/14 08:57:06
> petere Exp $
> -CATALOG_NAME := libpq
> +CATALOG_NAME := libpq4
> AVAIL_LANGUAGES:= af cs de es fr hr it ko nb pl pt_BR ru sk sl sv tr
> zh_CN zh_TW
> GETTEXT_FILES := fe-auth.c fe-connect.c fe-exec.c fe-lobj.c fe-misc.c
> fe-protocol2.c fe-protocol3.c fe-secure.c
> GETTEXT_TRIGGERS:= libpq_gettext pqInternalNotice:2
>
> Compared to the SONAME, changing the translation domain is relatively
> uncritical, so if you don't want to change this upstream, I can
> maintain this patch for Debian/Ubuntu. However, I heard that some RPM
> guys plan a infrastructure similar to mine, and at that point they
> will have precisely the same problems :-)
>
> Thanks for considering and have a nice day!
>
> Martin
> --
> Martin Pitt http://www.piware.de
> Ubuntu Developerhttp://www.ubuntulinux.org
> Debian GNU/Linux Developer http://www.debian.org
-- End of PGP section, PGP failed!
--
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 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] [COMMITTERS] pgsql: Fix NUMERIC modulus to properly
Bruce Momjian wrote: Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: No, I don't think so. It doesn't seem to be something that enough people use to risk the change in behavior --- it might break something that was working. But, if folks want it backported we can do it. It is only a change to properly do modulus for numeric. Well, from my point of view it's an absolute mathematical error - i'd backport it. I can't see anyone relying on it :) Doesn't this patch break the basic theorem that a = trunc(a / b) * b + (a mod b) ? If division rounds and mod doesn't, you've got pretty serious issues. Well, this is a good question. In the equation above we assume '/' is an integer division. The problem with NUMERIC when used with zero-scale operands is that the result is already _rounded_ to the nearest hole number before it gets to trunc(), and that is why we used to get negative modulus values. I assume the big point is that we don't offer any way for users to get a NUMERIC division without rounding. With integers, we always round down to the nearest whole number on division; float doesn't offer a modulus operator, and C doesn't support it either. We round NUMERICs to the specific scale because we want to give the most accurate value: test=> select 1000::numeric(24,0) / 11::numeric(24,0); ?column? 9090909090909090909091 The actual values is: -- 9090909090909090909090.90 But the problem is that the equation at the top assumes the division is not rounded. Should we supply a NUMERIC division operator that doesn't round? integer doesn't need it, and float doesn't have the accurate precision needed for modulus operators. The user could supply some digits in the division: test=> select 1000::numeric(30,6) / 11::numeric(24,0); ?column? --- 9090909090909090909090.909091 (1 row) but there really is no _right_ value to prevent rounding (think 0.999). A non-rounding NUMERIC division would require duplicating numeric_div() but with a false for 'round', and adding operators. I would prefer that division didn't round, as with integers. You can always calculate your result to 1 more decimal place and then round, but there is no way to unround a rounded result. Tom had asked whether PG passed the regression tests if we change the round_var() to a trunc_var() at the end of the function div_var(). It does not pass, but I think that is because the regression test is expecting that division will round up. (Curiously, the regression test for "numeric" passes, but the regression test for aggregation--sum() I think--is the one that fails.) I have attached the diffs here if anyone is interested. Regards, Paul Tillotson *** ./expected/aggregates.out Sun May 29 19:58:43 2005 --- ./results/aggregates.outMon Jun 6 21:01:11 2005 *** *** 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100; avg_32 - ! 32.6667 (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. --- 10,16 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100; avg_32 - ! 32. (1 row) -- In 7.1, avg(float4) is computed using float8 arithmetic. == test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test comments ... ok test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test geometry ... ok test horology ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_fun
[HACKERS] RESET CONNECTION behavior
Can we get a list features we want for RESET CONNECTION? At this point, I see ideas but no consistent approach. Some say protocol only, others say an SQL command is fine, but the protocol has to find out it happened somehow. Is that a plan? -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] RESET CONNECTION behavior
Bruce Momjian wrote: > Can we get a list features we want for RESET CONNECTION? At this point, > I see ideas but no consistent approach. Some say protocol only, others > say an SQL command is fine, but the protocol has to find out it happened > somehow. Is that a plan? I have enhanced our TODO for this item: * 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. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] I am up-to-date
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. -- 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 8: explain analyze is your friend
[HACKERS] Should *.backup files ever be removed from pg_xlog?
When you do a pg_start_backup()/pg_stop_backup(), the *.backup files created in pg_xlog are never deleted. Is that intended? $ lf /u/pg/data/pg_xlog/ 0001.0034C96C.backup00010004 0001.0034C9AC.backup00010005 0001000200010006 00010003archive_status/ -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #1467: fe_connect doesn't handle EINTR right
Bruce Momjian writes: > Would someone comment on this bug report from February? The report scored about zero out of zero IMHO: neither an actual report of field trouble, nor a clear explanation of the supposed trouble, nor a specific proposal what to do about it. Without actual field trouble reports I am disinclined to touch the code anyway ... chasing after unproven portability issues is an unprofitable pursuit in my experience, since you can have no way to know if you've actually fixed whatever real bug may exist. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Should *.backup files ever be removed from pg_xlog?
Bruce Momjian writes: > When you do a pg_start_backup()/pg_stop_backup(), the *.backup files > created in pg_xlog are never deleted. Is that intended? Yes. See the documentation. DBAs can delete 'em if they feel like, but I don't see a strong argument for automatically removing 'em. They aren't actually large ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Should *.backup files ever be removed from pg_xlog?
Tom Lane wrote: > Bruce Momjian writes: > > When you do a pg_start_backup()/pg_stop_backup(), the *.backup files > > created in pg_xlog are never deleted. Is that intended? > > Yes. See the documentation. DBAs can delete 'em if they feel like, > but I don't see a strong argument for automatically removing 'em. > They aren't actually large ... I don't see anywhere in the documentation where we say you can get rid of them. I see this: For example, if the starting WAL file is 0001123455CD the backup history file will be named something like 0001123455CD.007C9330.backup. (The second number in the file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the file system backup and the WAL segment files used during the backup (as specified in the backup history file), all archived WAL segments with names numerically less are no longer needed to recover the file system backup and may be deleted. However, you should consider keeping several backup sets to be absolutely certain that you are can recover your data. Keep in mind that only completed WAL segment files are archived, so there will be delay between running pg_stop_backup and the archiving of all WAL segment files needed to make the file system backup consistent. The "all archived WAL segments with names numerically less are no longer needed" I assume is talking about files in the archive location, not pg_xlog. Does this need clarifying? -- 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 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
On Mon, Jun 06, 2005 at 09:23:21PM -0400, Bruce Momjian wrote:
>
> Is this a direction we want to explore --- using the SONAME as part of
> the translation domain?
Hm, interesting -- this could explain some weird problems I've had with
translated text on a machine where multiple versions are installed.
I'll investigate a bit and if it turns out to be solved by this
approach, it has my vote.
However I think we should stay away from using the version number as a
string inside the source. If we do tht, we will forget to increment it
in the future and we will have problems.
> > In my test packages I used the following patch:
> >
> > diff -ruN postgresql-8.0.1-old/src/interfaces/libpq/fe-misc.c
> > postgresql-8.0.1/src/interfaces/libpq/fe-misc.c
> > --- postgresql-8.0.1-old/src/interfaces/libpq/fe-misc.c 2004-12-31
> > 23:03:50.0 +0100
> > +++ postgresql-8.0.1/src/interfaces/libpq/fe-misc.c 2005-02-07
> > 22:55:13.177419296 +0100
> > @@ -1133,7 +1133,7 @@
> > {
> > already_bound = 1;
> > /* No relocatable lookup here because the binary could be
> > anywhere */
> > - bindtextdomain("libpq", getenv("PGLOCALEDIR") ?
> > getenv("PGLOCALEDIR") : LOCALEDIR);
> > + bindtextdomain("libpq4", getenv("PGLOCALEDIR") ?
> > getenv("PGLOCALEDIR") : LOCALEDIR);
> > }
--
Alvaro Herrera ()
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] lastval()
BTW, I noticed that the "permission denied" messages throughout the source don't quote the name of the identifier for which permission has been denied. This violates the error code conventions: "Use quotes always to delimit file names, user-supplied identifiers, and other variables that might contain words." Is there a reason for this? Request: can we _please_ have the actual permission that is denied, and the username it was denied to in the error messages? It's really a pain when reviewing logs to see such an error, then not know what it was for or who generated it... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] adding new pages bulky way
"Victor Y. Yegorov" <[EMAIL PROTECTED]> writes: > [ scheme involving a predetermined layout of index pages ] > The question is: allocating pages this way is really ugly, I understand. Is > there some API that would allow allocating N pages in the bulk way? Why bother? Just write each page when you need to --- there's no law that says you must use P_NEW. The hash index type does something pretty similar, IIRC. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] adding new pages bulky way
"Tom Lane" <[EMAIL PROTECTED]> writes > > Why bother? Just write each page when you need to --- there's no law > that says you must use P_NEW. The hash index type does something pretty > similar, IIRC. > Is there any performance benefits if we have a mdextend_several_pages() function in md.c? So the relation can be extended in a bulky way. In my understanding, if we write write(fd, buffer, BLCKSZ*10) instead of for (i=0; i<10; i++) write(fd, buffer, BLCKSZ); This will reduce some costs of file system logs for journal file systems. Of course, the cost we have to pay is the longer time of holding relation extension lock. Regards, Qingqing ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] adding new pages bulky way
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > Is there any performance benefits if we have a mdextend_several_pages() > function in md.c? I very seriously doubt that there would be *any* win, and I doubt even more that it could possibly be worth the klugery you'd have to do to make it happen. Bear in mind that index access methods are two API layers away from md.c --- how will you translate this into something that makes sense in the context of bufmgr's API? regards, tom lane ---(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]> writes
>
> I very seriously doubt that there would be *any* win, and I doubt even
> more that it could possibly be worth the klugery you'd have to do to
> make it happen. Bear in mind that index access methods are two API
> layers away from md.c --- how will you translate this into something
> that makes sense in the context of bufmgr's API?
>
Index access or heap access doesn't matter. The imaginary plan is like this:
-- change 1 --
/* md.c */
mdextend()
{
mdextend_several_pages();
add_pages_to_FSM();
}
-- change 2 --
/*
* Any places hold relation extension lock
*/
if (needLock)
LockPage(relation, 0, ExclusiveLock);
/* ADD: check again here */
if (InvalidBlockNumber != GetPageWithFreeSpace())
UnlockPage(relation, 0, ExclusiveLock);
/* I have to do the extension */
buffer = ReadBuffer(relation, P_NEW);
Above code is quite like how we handle xlogflush() currently.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
