Re: [HACKERS] Weirdess when altering serial column type
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Should that sequence really stick around as an integer, numeric and text field??? What are you unhappy about exactly? We expended a fair amount of sweat to make it behave just like that ... 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] Weirdess when altering serial column type
Should that sequence really stick around as an integer, numeric and text field??? What are you unhappy about exactly? We expended a fair amount of sweat to make it behave just like that ... It's confused the odd IRC user (pgsql newbie). Seems like it breaks the 'serial type' illusion... I presume they have to drop the default, then drop the sequence to get rid of it. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Weirdess when altering serial column type
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I presume they have to drop the default, then drop the sequence to get rid of it. Hmm. Right at the moment I don't think you *can* get rid of it, short of dropping the column altogether. regression=# create table z(f1 serial); NOTICE: CREATE TABLE will create implicit sequence z_f1_seq for serial column z.f1 CREATE TABLE regression=# drop sequence z_f1_seq; ERROR: cannot drop sequence z_f1_seq because table z column f1 requires it HINT: You may drop table z column f1 instead. regression=# alter table z alter f1 drop default; ALTER TABLE regression=# drop sequence z_f1_seq; ERROR: cannot drop sequence z_f1_seq because table z column f1 requires it HINT: You may drop table z column f1 instead. Possibly it would be better if the implicit dependency led from the sequence to the column default rather than directly to the column ... but I'm too tired to think of the possible consequences. I think we may have considered and rejected that idea ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Problem with PITR recovery
On Mon, 2005-04-18 at 21:25 -0400, Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The wal file could be truncated after the log switch record, though I'd want to make sure that didn't cause other problems. Which it would: that would break WAL file recycling. Good point. I don't see non-full WAL archiving as a problem for the backup or shutdown, but I do see an issue with doing archives every X seconds. If someone sets that really low (and someone will) we could easily fill the disk. The disk would only fill if the archiver doesn't keep up with transmitting xlog files to the archive. The archive can fill up if it is not correctly sized, even now. Switching log files every N seconds would at least give a very predictable archive sizing calculation which should actually work against users sizing their archives poorly. However, rather than do it ourselves, maybe we should make it visible to administrators so they know exactly what is happening and can undo it in case they need to recover, something like: archive_command = 'gzip %p %f' so the compression is done in a way that is visible to the administrator. As long as we tell them there's more than one way to do it. Many tape drives offer hardware compression, for example, so there would be no gain in doing this twice. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with PITR recovery
On Tue, 2005-04-19 at 08:55 +0400, Oleg Bartunov wrote: On Mon, 18 Apr 2005, Simon Riggs wrote: but I'm not sure it's best practice to delete them at that point. I would recommend that users keep at least the last 3 backups. So, I'd prefer the wording ...all archived WAL segments with names numerically less will no longer be needed as part of that backup set. You may delete them at that point, though you should consider keeping more than one backup set to be absolutely certain that you are can recover your data. I see that clear and deterministic procedure of online backup as I imagined earlier becomes fuzzy and blurred :) The process is involved and requires strictly observed administration procedures, just as it does with other database systems. Each of them have difficulties that need to be surmounted and require much thought to implement. If PostgreSQL is the first DBMS on which you have attempted to implement transactional archive recovery then you will definitely find it hard, just as most Oracle and SQLServer DBAs don't understand how their log recovery systems work either. This is obviously not suited even for my notebook. Thats a pretty silly comment Oleg. Since most laptops require portability as the main objective and that usually requires or at least must frequently expect disconnection from networks and other peripheral devices such as tape units, then no, the PITR design isn't suitable in general for laptop use. If you use your notebook as a production system with online archiving then PITR is suitable. PITR was designed to offer data protection for major production systems. My experience was that these sites would have a reasonable stream of transactions coming through, making the time between log file switches somewhat predictable and usually every few minutes. The use case of a very low transaction rate system was not considered fully since it was felt that people in that situation would be less bothered to protect their data with a rigorous backup procedure, leaving the issue we have been discussing. If you want recoverability, use PITR. If you choose not to use PITR, thats fine. If you'd like to help make it better, that's fine too. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] inet increment w/ int8
BM Would you modify this so it can go in /contrib or pgfoundry? Is there BM general interest for this? Actually, I suggested to do such or similar function as internal. PostgreSQL has inet/cidr - excellent data type and good facilities to examine and compare inet values, but has no facilities to modify them (i.e. get-change-return operations). There is place for many useful operators and functions to do but they not invented enough yet (to supplement with existing facilites and each other). Only facility that has no doubt is increment/decrement inet address value with numeric value. It nicely supplements existing inet compare operators (that compares two addresses as numeric values). Also, it can be used to override + and - operators between inet and numeric values. I understand, that you have more important things to do, so I write this function, to save your time. Maybe, it is good idea, to implement such functions separatelly as /contrib ... for a start ... Live example for inet_inc() (as + operator) (PL/pgSQL) -- (try to) Peek address from group pool SELECT next INTO next_ip FROM peer_ranges WHERE group_id = (SELECT id FROM peer_groups WHERE name = $3) AND next last ORDER BY first LIMIT 1; -- Return NULL if pool is empty IF NOT FOUND THEN RAISE NOTICE ''Group address pool is empty''; RETURN NULL; END IF; -- Update pool UPDATE peer_ranges SET next = next_ip + 1 WHERE next = next_ip; RETURN next_ip; where, peer_ranges is: CREATE TABLE peer_ranges ( group_id bigint NOT NULL REFERENCES peer_groups (id), first inet NOT NULL UNIQUE, next inet NOT NULL UNIQUE, last inet NOT NULL UNIQUE ); Thank you, Ilya A. Kovalenko (mailto:[EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] inet increment w/ int8
On Mon, Apr 18, 2005 at 08:58:01PM -0400, Bruce Momjian wrote: Would you modify this so it can go in /contrib or pgfoundry? Is there general interest for this? I was about to sit down and write the same function yesterday, when as if by magic this appeared. In my case it is to loop over ip numbers in a class C looking for a free one to allocate. So, from here there is interest.. Cheers, Patrick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with PITR recovery
On Tue, 19 Apr 2005, Simon Riggs wrote: On Tue, 2005-04-19 at 08:55 +0400, Oleg Bartunov wrote: On Mon, 18 Apr 2005, Simon Riggs wrote: but I'm not sure it's best practice to delete them at that point. I would recommend that users keep at least the last 3 backups. So, I'd prefer the wording ...all archived WAL segments with names numerically less will no longer be needed as part of that backup set. You may delete them at that point, though you should consider keeping more than one backup set to be absolutely certain that you are can recover your data. I see that clear and deterministic procedure of online backup as I imagined earlier becomes fuzzy and blurred :) The process is involved and requires strictly observed administration procedures, just as it does with other database systems. Each of them have difficulties that need to be surmounted and require much thought to implement. If PostgreSQL is the first DBMS on which you have attempted to implement transactional archive recovery then you will definitely find it hard, just as most Oracle and SQLServer DBAs don't understand how their log recovery systems work either. This is not an argument ! It's shame we still don't understand do we really have reliable online backup or just hype with a lot of restriction and caution. I'm not experienced Oracle DBA but I don't want to be a blind user. I read seminal papers about recovery and I thought I understand how it should works in our system. I want to be 110% sure to claim we're ready to recommend it to our clients. I'm sure there are many experienced DBA's who also don't understand what we have right now, especially after this thread. This is obviously not suited even for my notebook. Thats a pretty silly comment Oleg. Don't be silly, Simon. It was just my reaction ! Since most laptops require portability as the main objective and that usually requires or at least must frequently expect disconnection from networks and other peripheral devices such as tape units, then no, the PITR design isn't suitable in general for laptop use. If you use your notebook as a production system with online archiving then PITR is suitable. PITR was designed to offer data protection for major production systems. My experience was that these sites would have a reasonable stream of transactions coming through, making the time between log file switches somewhat predictable and usually every few minutes. The use case of a very low transaction rate system was not considered fully since it was felt that people in that situation would be less bothered to protect their data with a rigorous backup procedure, leaving the issue we have been discussing. If you want recoverability, use PITR. If you choose not to use PITR, thats fine. If you'd like to help make it better, that's fine too. These sentences are not fair, Simon. I understand your point but I want to have postgresql applicable not just for major production systems. You forget that before production stage you have a lot of development and testing. I don't want something exotical and I'm a bit surprized about your reaction. I don't want to think about how difficult backup in Oracle and other major dbms you're so experienced ! I'm PostgreSQL user and PostgreSQL is rather transparent system and I'd like to have understandable recovery process. Now I see all limitations and cautions and waiting for improvements. Nobody attack you, I'm a bit dissapointed, but this is what we have. Best Regards, Simon Riggs 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] ORDER BY different locales for 8.0
Hello, the nls_string function that makes it possible to sort by arbitrary locale has been updated to reflect the changes in error handling in PostgreSQL 8.0, due to users using the nls_string sorting on 7.4 and requesting it for 8.0 as well. The distribution can be downloaded from http://www.fi.muni.cz/~adelton/l10n/ http://www.fi.muni.cz/~adelton/l10n/postgresql-nls-string/postgresql-nls-string-8.01.tar.gz I'll appreciate any comments. The README is as follows: - Why this function: PostgreSQL, at least until version 8.0, has rather weak support for various collating sequences -- what you get when you do select ... order by column. The sorting is closely tied to indexes used throughout the database cluster and is specified by locale settings at the initdb time. Yet, people asked for ways of specifying the collating rules at runtime, even if the sorting will not use indexes. Just take the records and sort them. It is reasonable request to want one select to order by using English rules, another one to run with German rules and yet another with Czech ones, without having to dump, initdb, restore. How it works: In this distribution you will find file nls_string.c. It contains the definition of function nls_string(text, text) which takes a string parameter and a locale name and returns string describing the ordering. So you can run select * from table order by nls_string(name, 'en_US.UTF-8') or select * from table order by nls_string(name, 'cs_CZ.UTF-8') or select * from table order by nls_string(name, 'C') and get what you expect -- the result is sorted the same way as it would be with LC_COLLATE=locate sort on the command line. Internally, the function sets the locale for LC_COLLATE category, runs strxfrm on the first parameter and encodes the result as octal values. Thus, it depends on your PostgreSQL collate setting (that which you did upon initdb, you can check it with show lc_collate) to sort numbers in the natural way. I believe this is reasonable assumption. Installation: Please check the INSTALL file. - Versions: This version of nls_string targets PostgreSQL server in version 8.0+. To use nls_string on version 7.4, download nls_string 0.53. - Bugs and ToDo: If your default collation settings does not sort numbers in the natural way (eg., 0123 is not sorted before 1234), the nls_string will not work. Nonetheless, the function does the work for me. Support, bug reports: This piece of software is provided as-is, in the hope that you will find it useful. However, no warranty is provided. I appreciate any bug reports, enhancement suggestions and patches. Please, _please_, use a meaningful Subject line and describe the situation in detail. Also make sure you've read and understood this README and the PostgreSQL documentation concerning C-language functions. I will not be helpful with installation problems if you did not read the documentation. --- If it works for you: If the function works for you, I'd appreciate a message from you. Just curious for what tasks people use the software. - Available: http://www.fi.muni.cz/~adelton/l10n/ -- Author: Copyright: (c) 2004--2005 Jan Pazdziora, [EMAIL PROTECTED] All rights reserved. Permission to use, distribute, modify, an copy this software and this documentation for any purpose is hereby granted. Contributors: Karel Zak [EMAIL PROTECTED] ftp://ftp2.zf.jcu.cz/users/zakkr/pg/ -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SETOF function call
Le Tuesday 19 April 2005 04:16, vous avez crit :
sizeof(Datum) == sizeof(long) - is that compatible with %d formatting (I'm
guessing something like vsprintf takes place in elog)? Wouldn't this need
%ld or %lu?
Sorry if this misses the point, I wasn't clear from original post if the
segfault was on elog or after it.
No, the elog works fine, and will be removed in final code, so the the mistake
between int and long int is not a real issue here.
The code segfault later, in the other function.
To be clean I am trying to in C code this:
CREATE FUNCTION rpmquery(rpmheader, TEXT) RETURNS SETOF TEXT AS $$
select * from rpmquery($1, rpmtag($2));
$$ LANGUAGE SQL;
Maybe it is easier to it as sql functions, but for my knowledge I like to
understand what I am doing wrong :)
Current buggy code is there, at the end:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgrpm/pgrpm/pgheader.c?rev=1.1content-type=text/x-cvsweb-markup
Of course patch/comments are welcome.
Regards, Philip.
On Tuesday 19 April 2005 11:53, Tom Lane wrote:
Olivier Thauvin [EMAIL PROTECTED] writes:
Datum
header_querytxt(PG_FUNCTION_ARGS)
{
elog(NOTICE, querytxt);
Datum tag;
/* converting TEXT value to integer one */
tag = DirectFunctionCall1(rpmtagvalue, PG_GETARG_TEXT_P(1));
elog(NOTICE, querytxt %d, tag);
/* header_query = function accepting integer instead text)
PG_RETURN_POINTER(DirectFunctionCall2(header_query,
PG_GETARG_BYTEA_P_COPY(0), tag));
}
If that's a cut-and-paste, then you seem to be short a */ on the second
comment ...
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
-
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.
-
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
pgpW6gKg3ZsHq.pgp
Description: PGP signature
Re: [HACKERS] Problem with PITR recovery
Simon Riggs wrote: On Mon, 2005-04-18 at 21:25 -0400, Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The wal file could be truncated after the log switch record, though I'd want to make sure that didn't cause other problems. Which it would: that would break WAL file recycling. Good point. I don't see non-full WAL archiving as a problem for the backup or shutdown, but I do see an issue with doing archives every X seconds. If someone sets that really low (and someone will) we could easily fill the disk. The disk would only fill if the archiver doesn't keep up with transmitting xlog files to the archive. The archive can fill up if it is not correctly sized, even now. Switching log files every N seconds would at least give a very predictable archive sizing calculation which should actually work against users sizing their archives poorly. I was thinking of the archiver filling because of lots of almost-empty 16mb files. If you archive every five seconds, it is 11 Gigs/hour, which is not too bad, I guess, but I would bet compression would save space and I/O load too. However, rather than do it ourselves, maybe we should make it visible to administrators so they know exactly what is happening and can undo it in case they need to recover, something like: archive_command = 'gzip %p %f' so the compression is done in a way that is visible to the administrator. As long as we tell them there's more than one way to do it. Many tape drives offer hardware compression, for example, so there would be no gain in doing this twice. Good point. I am thinking 'gzip --fast' would be the best option for copies to another file system. I see about 0.6 seconds to compress a 16mb WAL file here and I get 16x compression. -- 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] inet increment w/ int8
Ilya A. Kovalenko wrote: BM Would you modify this so it can go in /contrib or pgfoundry? Is there BM general interest for this? Actually, I suggested to do such or similar function as internal. PostgreSQL has inet/cidr - excellent data type and good facilities to examine and compare inet values, but has no facilities to modify them (i.e. get-change-return operations). There is place for many useful operators and functions to do but they not invented enough yet (to supplement with existing facilites and each other). Only facility that has no doubt is increment/decrement inet address value with numeric value. It nicely supplements existing inet compare operators (that compares two addresses as numeric values). Also, it can be used to override + and - operators between inet and numeric values. I understand, that you have more important things to do, so I write this function, to save your time. Agreed. Let's implement '+/-' for 'inet + int4' and put it in the backend as standard (I can help do the system table stuff if you give me the C functions). However, how do we handle cases where int4 255. I am thinking we should support only inet + inet, like this: SELECT '1.2.3.4'::inet + '0.0.1.2'::inet; But how do we do: SELECT '1.2.3.255'::inet + '0.0.0.1'::inet; I assume this becomes '1.2.4.0'. Inet +/- inet seems the most flexible because it allows you to add to any part of the mask, rather than just the lower-order bytes, or trying to make sense that 256 adds like '0.0.1.0'. -- 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] Problem with PITR recovery
Bruce Momjian pgman@candle.pha.pa.us writes: I was thinking of the archiver filling because of lots of almost-empty 16mb files. If you archive every five seconds, it is 11 Gigs/hour, which is not too bad, I guess, but I would bet compression would save space and I/O load too. If you wanted to archive every few seconds, it would be worth cutting the size of the segment files. At the moment I believe the segment size is a pg_config_manual.h configuration item. Not sure if it would be practical to make it run-time configurable, but in any case doing that would help a lot for people who want short archive cycles. But really, if that is the concern, I'd think you'd want Slony or some other near-real-time replication mechanism. PITR is designed for people for whom some-small-number-of-minutes is close enough. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] inet increment w/ int8
Bruce Momjian pgman@candle.pha.pa.us writes: am thinking we should support only inet + inet, like this: SELECT '1.2.3.4'::inet + '0.0.1.2'::inet; I don't think inet+inet makes any sense. I think inet+int4 should work by adding to the host address and overflowing if it exceeds the network mask. Ie, 10.0.0.0/24 + 1 = 10.0.0.1/24 10.0.0.255/24 + 1 = overflow Or 10.1/16 + 1 = 10.1.0.1/16 10.1/16 + 16384 = 10.1.64.0/16 10.1/16 + 65536 = overflow -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] inet increment w/ int8
Greg Stark wrote: Bruce Momjian pgman@candle.pha.pa.us writes: am thinking we should support only inet + inet, like this: SELECT '1.2.3.4'::inet + '0.0.1.2'::inet; I don't think inet+inet makes any sense. I think inet+int4 should work by adding to the host address and overflowing if it exceeds the network mask. Ie, 10.0.0.0/24 + 1 = 10.0.0.1/24 10.0.0.255/24 + 1 = overflow Or 10.1/16 + 1 = 10.1.0.1/16 10.1/16 + 16384 = 10.1.64.0/16 10.1/16 + 65536 = overflow So, do not overflow? We can do that. Another idea Tom had was creating a function that increments/decrements the address or the network portion of the address, and if you increment past the non-network portion that overflows too. -- 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] Problem with PITR recovery
On Tue, Apr 19, 2005 at 11:05:32AM -0400, Bruce Momjian wrote: Simon Riggs wrote: The disk would only fill if the archiver doesn't keep up with transmitting xlog files to the archive. The archive can fill up if it is not correctly sized, even now. Switching log files every N seconds would at least give a very predictable archive sizing calculation which should actually work against users sizing their archives poorly. I was thinking of the archiver filling because of lots of almost-empty 16mb files. If you archive every five seconds, it is 11 Gigs/hour, which is not too bad, I guess, but I would bet compression would save space and I/O load too. I suggested back then that some command to replace an archive could be provided. So some people could use rsync to update the older version of the XLog file to the new state. Non-rsync enabled people could use a temporary file to copy the new file, and then rename to the original XLog name, substituting the older version. And as a third way, maybe we can come up with a sort-of-xdelta that would only update the yet-unused portion of the old xlog file to the new content. (Maybe this could be made to work with tape.) Everyone here said that there was no need for such a thing because it would complicate matters. -- Alvaro Herrera ([EMAIL PROTECTED]) Hay quien adquiere la mala costumbre de ser infeliz (M. A. Evans) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Problem with PITR recovery
Alvaro Herrera wrote: On Tue, Apr 19, 2005 at 11:05:32AM -0400, Bruce Momjian wrote: Simon Riggs wrote: The disk would only fill if the archiver doesn't keep up with transmitting xlog files to the archive. The archive can fill up if it is not correctly sized, even now. Switching log files every N seconds would at least give a very predictable archive sizing calculation which should actually work against users sizing their archives poorly. I was thinking of the archiver filling because of lots of almost-empty 16mb files. If you archive every five seconds, it is 11 Gigs/hour, which is not too bad, I guess, but I would bet compression would save space and I/O load too. I suggested back then that some command to replace an archive could be provided. So some people could use rsync to update the older version of the XLog file to the new state. Non-rsync enabled people could use a temporary file to copy the new file, and then rename to the original XLog name, substituting the older version. And as a third way, maybe we can come up with a sort-of-xdelta that would only update the yet-unused portion of the old xlog file to the new content. (Maybe this could be made to work with tape.) Everyone here said that there was no need for such a thing because it would complicate matters. I do think we are going to need to go in that direction. I think the problem is that we didn't have enough time to come up with a clear solution to this problem so we delayed it for 8.1. I agree the idea of overwriting is a nice idea and works for everything but a tape drive, so it has to be optional in some way. -- 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] inet increment w/ int8
Bruce Momjian pgman@candle.pha.pa.us writes: Ie, 10.0.0.0/24 + 1 = 10.0.0.1/24 10.0.0.255/24 + 1 = overflow Or 10.1/16 + 1 = 10.1.0.1/16 10.1/16 + 16384 = 10.1.64.0/16 10.1/16 + 65536 = overflow So, do not overflow? You mean not doing modulus arithemtic? Yes. Overflow instead. I see a use case for of generating addresses based on a sequence or some primary key from the database. Something like CREATE SEQUENCE hosts_ip_seq MAXVALUE 65536; ALTER TABLE hosts ALTER ip SET DEFAULT '10.0.0.0/16'::inet + nextval(hosts_ip_seq') Using the primary key or some foreign key in the table would require a trigger which would take too much work to cons up an example for. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Problem with PITR recovery
On Tue, 2005-04-19 at 15:23 +0400, Oleg Bartunov wrote: This is not an argument ! It's shame we still don't understand do we really have reliable online backup or just hype with a lot of restriction and caution. I'm not experienced Oracle DBA but I don't want to be a blind user. I read seminal papers about recovery and I thought I understand how it should works in our system. I want to be 110% sure to claim we're ready to recommend it to our clients. I'm sure there are many experienced DBA's who also don't understand what we have right now, especially after this thread. Unless I misunderstand something, I think you're overreacting a bit. The failure case is that the machine on which the database resides vaporizes after you've done pg_stop_backup() but before the archiver archives the WAL segments used during the backup procedure. In practice, there are many reasons why that is not a major problem. For example, PITR base backups are often going to be taken when the archiver is already archiving WAL segments, and you already have a previous, working bask backup. You'd still be able to use that old base backup and the newly archived WAL segments. In general, it's just not realistic that you take a machine from having no backups of any kind to running mission-critical transactions and depending solely on the PITR backup, and then watch the server vaporize, all in less time than it takes to archive a few WAL segments. In almost all cases, the loss in data would be comparable to the loss experienced by not having the last few WAL segments shipped, and PITR never made a promise of keeping the transactions that never got archived. PITR works, and the developers are: (1) Improving the current docs to make it absolutely clear how to make 100% assured backups. (2) Making PITR easier to administer, probably for 8.1. (3) Adding features to PITR, probably for 8.1. If what I said above is incorrect, please correct me, because that means that I'm one of the lost DBAs that Oleg is talking about. Regards, Jeff Davis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problem with PITR recovery
Jeff Davis wrote: Unless I misunderstand something, I think you're overreacting a bit. The failure case is that the machine on which the database resides vaporizes after you've done pg_stop_backup() but before the archiver archives the WAL segments used during the backup procedure. In practice, there are many reasons why that is not a major problem. For example, PITR base backups are often going to be taken when the archiver is already archiving WAL segments, and you already have a previous, working bask backup. You'd still be able to use that old base backup and the newly archived WAL segments. In general, it's just not realistic that you take a machine from having no backups of any kind to running mission-critical transactions and depending solely on the PITR backup, and then watch the server vaporize, all in less time than it takes to archive a few WAL segments. In almost all cases, the loss in data would be comparable to the loss experienced by not having the last few WAL segments shipped, and PITR never made a promise of keeping the transactions that never got archived. PITR works, and the developers are: (1) Improving the current docs to make it absolutely clear how to make 100% assured backups. (2) Making PITR easier to administer, probably for 8.1. (3) Adding features to PITR, probably for 8.1. You are right. The problem we really had was that the documentation didn't mention the restrictions, and it said you could remove the old archived WAL files once you did pg_stop_backup(). That has been corrected and the new documentation will be in 8.0.3. I will mention the PITR documentation clarification in the release notes for 8.0.3. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problem with PITR recovery
On Tue, 19 Apr 2005, Jeff Davis wrote: Unless I misunderstand something, I think you're overreacting a bit. The Y're right. It's all emotions :) 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Comparing Datum's at aminsert() stage
* Tom Lane [EMAIL PROTECTED] [19.04.2005 19:48]: That's probably what you *have* to use, since the normal deconstructors assume they are working with heap tuples, which are different. But I don't understand why you are waiting till after the index tuple is formed. The aminsert function gets an array of Datums to start with. Why not do it there? Well, I need that exactly in aminsert. Each value is stored only once in the index (along with it's own series-of-bits). Thus, I need to compare each Datum from aminsert()'s array with the existing ones. Also, I cannot form tuple the ordinary way (I need all values separated), so I copy each TuplDesc-attrs[i] into temporary TupleDesc (1 attribute big) and call heap_fill_tuple(). Actually, I'm not sure this is the right way... I think, storing some kind of hash-value from the Datum is a good idea, but it's need to be unique. Is it possible with any existing API? -- 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] inet increment w/ int8
On Tue, Apr 19, 2005 at 12:03:27 -0400, Bruce Momjian pgman@candle.pha.pa.us wrote: Agreed. Let's implement '+/-' for 'inet + int4' and put it in the backend as standard (I can help do the system table stuff if you give me the C functions). However, how do we handle cases where int4 255. I am thinking we should support only inet + inet, like this: SELECT '1.2.3.4'::inet + '0.0.1.2'::inet; I don't think this operation makes much sense. Adding an integer makes some sense, but I think the original problem would be better solved by having a set returning function generate the possible network addresses to be allocated and store that set in a table. I don't think assuming everthing is a /24 is a good idea. If wrapping is to be done, there should be some mask specified. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PLM pulling from CVS nightly for testing in STP
I have dbt-2 tests automatically running against each pull from CVS and have started to automatically compile results here: http://developer.osdl.org/markw/postgrescvs/ I did start with a bit of a minimalistic approach, so I'm open for any comments, feedback, etc. Mark ---(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] argtype_inherit() is dead code
On Sun, Apr 17, 2005 at 07:01:41PM -0400, Christopher Browne wrote: The world rejoiced as [EMAIL PROTECTED] (Jim C. Nasby) wrote: On Sun, Apr 17, 2005 at 06:56:01AM -0700, Joshua D. Drake wrote: Is it really an important area to improve, or are there other priorities? I know some people wished we had better support for inheritance, but how strong is that wish? FWIW, I think people might be more likely to use the OO features that PostgreSQL already has if there was better OO support in one or more of the languages. Oracle has some support along these lines and it was nice being able to make use of it the last time I used Oracle. I don't remember the exact details, and I don't think they're necessarily the way you'd want to do it in PostgreSQL anyway, but it was nice being able to do things like expose a type/class that knew how to pull info from the database as well as store it there. What is there, really, to add? Object Orientation is all about the notion of having data that is aware of its type, and where there can be a dispatching of methods against those types. There is already a perfectly functional ability to dispatch based on argument types. These essentials are there. Yes, but they're only there when it comes to storing data. There's nothing allowing you to cohesively combine code and data. An object should be able to have methods attached to it, for example. And that functionality is essentially missing. There's no way to present a combined set data and code that operates on that data. It doesn't really matter why this kind of functionality is missing; the fact that it is missing means it's much less likely that any of the OO stuff will be used. I think the current limitations (foreign keys, and cross-table constraints) are issues as well. It might also help if the docs had some info about how inherited tables worked 'under the covers', so people knew what kind of overhead they implied. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Idea for the statistics collector
Added to TODO: * Add tool to query pg_stat_* tables and report indexes that aren't needed or tables that might need indexes --- Christopher Kings-Lynne wrote: I was thinking of writing a command line tool like 'pgtune' that looks at the stats views and will generate SQL code for, or do automatically the following: * Dropping indices that are never used * Creating appropriate indices to avoid large, expensive sequential scans. This would put us in the 'mysql makes my indices for me by magic' league - but would be far more powerful and flexible. How to do multikey indices is beyond me tho. *sigh* I'm recovering from a septoplasty on my nose atm, so I might have some time to do some coding! Chris - Original Message - From: Bruce Momjian pgman@candle.pha.pa.us To: Martijn van Oosterhout kleptog@svana.org Cc: PostgreSQL-development pgsql-hackers@postgresql.org Sent: Friday, June 21, 2002 10:50 AM Subject: Re: [HACKERS] [GENERAL] Idea for the statistics collector Martijn van Oosterhout wrote: Since it's currently all for collecting statistics on tables, why can't it collect another type of statistic, like: - How often the estimator gets it wrong? At the end of an index scan, the executor could compare the number of rows returned against what was estimated, and if it falls outside a certain range, flag it. Also, the average ratio of rows coming out of a distinct node vs the number going in. For a join clause, the amount of correlation between two columns (hard). etc Ideally, the planner could then use this info to make better plans. Eventually, the whole system could become somewhat self-tuning. Does anyone see any problems with this? [ Discussion moved to hackers.] I have thought that some type of feedback from the executor back into the optimizer would be a good feature. Not sure how to do it, but your idea makes sense. It certainly could update the table statistics after a sequential scan. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] [GENERAL] Idea for the statistics collector
Good god - how old was that email? 2002??? Chris Bruce Momjian wrote: Added to TODO: * Add tool to query pg_stat_* tables and report indexes that aren't needed or tables that might need indexes --- Christopher Kings-Lynne wrote: I was thinking of writing a command line tool like 'pgtune' that looks at the stats views and will generate SQL code for, or do automatically the following: * Dropping indices that are never used * Creating appropriate indices to avoid large, expensive sequential scans. This would put us in the 'mysql makes my indices for me by magic' league - but would be far more powerful and flexible. How to do multikey indices is beyond me tho. *sigh* I'm recovering from a septoplasty on my nose atm, so I might have some time to do some coding! Chris - Original Message - From: Bruce Momjian pgman@candle.pha.pa.us To: Martijn van Oosterhout kleptog@svana.org Cc: PostgreSQL-development pgsql-hackers@postgresql.org Sent: Friday, June 21, 2002 10:50 AM Subject: Re: [HACKERS] [GENERAL] Idea for the statistics collector Martijn van Oosterhout wrote: Since it's currently all for collecting statistics on tables, why can't it collect another type of statistic, like: - How often the estimator gets it wrong? At the end of an index scan, the executor could compare the number of rows returned against what was estimated, and if it falls outside a certain range, flag it. Also, the average ratio of rows coming out of a distinct node vs the number going in. For a join clause, the amount of correlation between two columns (hard). etc Ideally, the planner could then use this info to make better plans. Eventually, the whole system could become somewhat self-tuning. Does anyone see any problems with this? [ Discussion moved to hackers.] I have thought that some type of feedback from the executor back into the optimizer would be a good feature. Not sure how to do it, but your idea makes sense. It certainly could update the table statistics after a sequential scan. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] [GENERAL] Idea for the statistics collector
Christopher Kings-Lynne wrote: Good god - how old was that email? 2002??? Yep, and been in my mailbox since then, waiting for me to process it into a TODO entry. --- Chris Bruce Momjian wrote: Added to TODO: * Add tool to query pg_stat_* tables and report indexes that aren't needed or tables that might need indexes --- Christopher Kings-Lynne wrote: I was thinking of writing a command line tool like 'pgtune' that looks at the stats views and will generate SQL code for, or do automatically the following: * Dropping indices that are never used * Creating appropriate indices to avoid large, expensive sequential scans. This would put us in the 'mysql makes my indices for me by magic' league - but would be far more powerful and flexible. How to do multikey indices is beyond me tho. *sigh* I'm recovering from a septoplasty on my nose atm, so I might have some time to do some coding! Chris - Original Message - From: Bruce Momjian pgman@candle.pha.pa.us To: Martijn van Oosterhout kleptog@svana.org Cc: PostgreSQL-development pgsql-hackers@postgresql.org Sent: Friday, June 21, 2002 10:50 AM Subject: Re: [HACKERS] [GENERAL] Idea for the statistics collector Martijn van Oosterhout wrote: Since it's currently all for collecting statistics on tables, why can't it collect another type of statistic, like: - How often the estimator gets it wrong? At the end of an index scan, the executor could compare the number of rows returned against what was estimated, and if it falls outside a certain range, flag it. Also, the average ratio of rows coming out of a distinct node vs the number going in. For a join clause, the amount of correlation between two columns (hard). etc Ideally, the planner could then use this info to make better plans. Eventually, the whole system could become somewhat self-tuning. Does anyone see any problems with this? [ Discussion moved to hackers.] I have thought that some type of feedback from the executor back into the optimizer would be a good feature. Not sure how to do it, but your idea makes sense. It certainly could update the table statistics after a sequential scan. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] inet increment w/ int8
BM Greg Stark wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
am thinking we should support only inet + inet, like this:
SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;
I don't think inet+inet makes any sense.
I think inet+int4 should work by adding to the host address and overflowing
if
it exceeds the network mask.
Ie,
10.0.0.0/24 + 1 = 10.0.0.1/24
10.0.0.255/24 + 1 = overflow
Or
10.1/16 + 1 = 10.1.0.1/16
10.1/16 + 16384 = 10.1.64.0/16
10.1/16 + 65536 = overflow
BM So, do not overflow? We can do that. Another idea Tom had was creating
BM a function that increments/decrements the address or the network portion
BM of the address, and if you increment past the non-network portion that
BM overflows too.
Hmm, actually, you can do several functions to increase/decrease
network address with different overflow models (octet-overflow,
host part overflow, full address overflow, or without overflow
as special case), for flexibility.
Another question, what model choose for '+/-' ...
BTW, why 'inet + int4' (not int8), what about v6 ?
Few words for 'inet + inet'. It's can be useful for IPv6 addresses
(because you don't have 128-bit numeric type, except, maybe, 'numeric'
one).
But, there is another way to reach higher octets - use existing
inet_{send|receive} functions.
disclaimer text='raw ideas and thoughts'
Or invent something new like this:
-- src index value
FUNCTION extract_octet(inet, integer) RETURNS integer
FUNCTION extract_word (inet, integer) RETURNS int2
FUNCTION extract_dword(inet, integer) RETURNS int4
FUNCTION extract_qword(inet, integer) RETURNS int8
--src index value
FUNCTION replace_octet(inet, integer, integer) RETURNS inet
FUNCTION replace_word (inet, integer, int2)RETURNS inet
FUNCTION replace_dword(inet, integer, int4)RETURNS inet
FUNCTION replace_qword(inet, integer, int8)RETURNS inet
(not established with signed 'int%')
/disclaimer
Ilya A. Kovalenko
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] inet increment w/ int8
GS I see a use case for of generating addresses based on a sequence or some GS primary key from the database. GS Something like GS CREATE SEQUENCE hosts_ip_seq MAXVALUE 65536; GS ALTER TABLE hosts ALTER ip SET DEFAULT '10.0.0.0/16'::inet + nextval(hosts_ip_seq') hmm, not quite good idea - SEQUENCEs, by design, does not rollback next value on transation rollback, so you'll have holes on address range when other values will break some constraints or concurrent sessions appears. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
