Re: [HACKERS] Problem with PITR recovery
On Sat, 2005-04-16 at 23:06 -0400, Bruce Momjian wrote: [about backup procedure with PITR documentation I see in the docs: To make use of this backup, you will need to keep around all the WAL segment files generated at or after the starting time of the backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need to have to make use of the backup. For example, if the starting WAL file is 0001123455CD the backup history file will be named something like 0001123455CD.007C9330.backup. (The second part of this file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the backup dump file, you can delete all archived WAL segments with names numerically preceding this one. I am not clear on what the backup dump file is? I assume it means 0001123455CD. It is called WAL segment file above. I will rename that phrase to match the above terminology. Patch attached and applied. Doesn't it refer to the backup file itself (the tar file of the data directory) ? You do not want to start deleting WAL segments until that one is safely archived. gnari ---(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] Problem with PITR recovery
Ragnar Hafstað wrote: On Sat, 2005-04-16 at 23:06 -0400, Bruce Momjian wrote: [about backup procedure with PITR documentation I see in the docs: To make use of this backup, you will need to keep around all the WAL segment files generated at or after the starting time of the backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need to have to make use of the backup. For example, if the starting WAL file is 0001123455CD the backup history file will be named something like 0001123455CD.007C9330.backup. (The second part of this file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the backup dump file, you can delete all archived WAL segments with names numerically preceding this one. I am not clear on what the backup dump file is? I assume it means 0001123455CD. It is called WAL segment file above. I will rename that phrase to match the above terminology. Patch attached and applied. Doesn't it refer to the backup file itself (the tar file of the data directory) ? No. That is what I thought it meant on first reading, but looking closer it is referring to the numbered file, and the tar file has no specific number. You do not want to start deleting WAL segments until that one is safely archived. Right, but the point of the paragraph is that you need the WAL file that goes with the backup history file number. -- 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] argtype_inherit() is dead code
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? Hello, From a people who call me perspective. I am never asked about inheritance. Most of the people don't even know it is there. The requests I get are: Replication (obviously solved) Multi-Master Replication (working on it ;)) Table Partitioning (No great solution) IN/OUT Parameteres (being working on) Good ODBC driver (Soon to be solved) I get others of course but those are the ones that seem to raise their head the most. Sincerely, Joshua D. Drake -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] argtype_inherit() is dead code
On Sun, Apr 17, 2005 at 06:56:01AM -0700, Joshua D. Drake wrote: From a people who call me perspective. I am never asked about inheritance. Most of the people don't even know it is there. The requests I get are: Just wondering, does anybody asks you about the excessive locking (and deadlocking) on foreign keys? The business about being able to drop users and then find out they were still owners of something? I guess I worry about things too low-level that nobody really cares too much about. -- Alvaro Herrera ([EMAIL PROTECTED]) Always assume the user will do much worse than the stupidest thing you can imagine.(Julien PUYDT) ---(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] argtype_inherit() is dead code
As the voice of someone who has a lot of experience with some of the original inheritance, I would prefer to have the select foo(t.*) from grandkid work for completeness. However, erroring out as ambiguous is not unreasonable since we have to cast the hell out of everything usually. I would suggest putting it on the bug list to fold into the replacement arguement marshalling code. That way we won't (ideally) forget the intended behaviour and may even fix it at some point. --elein On Sat, Apr 16, 2005 at 03:39:55PM -0400, Tom Lane wrote: [EMAIL PROTECTED] (elein) writes: Are you saying that the code was supposed unflatten the arguments of a function into a possible composite type taking into consideration the possible inheritance information of the composite type? No, it didn't do that. AFAICT the case it was supposed to handle was resolution of ambiguous function calls in what would amount to an object-oriented-programming usage style. Consider create table parent(...); create table child(...) inherits (parent); create table grandkid(...) inherits (child); create function foo(parent) returns ...; create function foo(child) returns ...; select foo(t.*) from parent t;-- uses foo(parent), of course select foo(t.*) from child t; -- uses foo(child), of course select foo(t.*) from grandkid t; -- ambiguous Our current code rejects the last with ambiguous function call, but an OOP user would expect it to choose foo(child) because that's the nearer parent supertype. It looks to me like Postgres once did choose foo(child), and argtype_inherit() was the code that made this happen. It's been broken for a long time though --- at least since 7.0, because 7.0 rejects this example. Digging in the CVS history, it appears that I may have broken it here: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parse_coerce.c.diff?r1=2.35;r2=2.36;f=h It's quite possible that it failed even before that however; I don't have a pre-7.0 server handy to check it on. In any case, given the lack of complaints since 7.0, it doesn't seem anyone is trying to do this kind of thing. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] inet increment w/ int8
Greetings,
I suggest function for inet increment w/ int8 (signed).
FUNCTION inet_inc(int, int8) RETURNS inet
Function, useful for making address pools (using also
existing inet compare functions to trap boundaries).
Notes:
This version lets address wrap around 0-*ff boundary.
Uses couple of non-POSIX functions - betoh64() and htobe64()
Tested on i386 with OpenBSD 3.7
PostgreSQL 8.0.2
-
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include postgres.h /* general Postgres declarations */
#include fmgr.h /* for argument/result macros */
#include utils/inet.h
Datum inet_inc(PG_FUNCTION_ARGS);
//-- stolen from backend/utils/adt/network.c
#define ip_family(inetptr) \
(((inet_struct *)VARDATA(inetptr))-family)
#define ip_bits(inetptr) \
(((inet_struct *)VARDATA(inetptr))-bits)
#define ip_type(inetptr) \
(((inet_struct *)VARDATA(inetptr))-type)
#define ip_addr(inetptr) \
(((inet_struct *)VARDATA(inetptr))-ipaddr)
#define ip_maxbits(inetptr) \
(ip_family(inetptr) == PGSQL_AF_INET ? 32 : 128)
static int
ip_addrsize(inet *inetptr)
{
switch (ip_family(inetptr))
{
case PGSQL_AF_INET:
return 4;
case PGSQL_AF_INET6:
return 16;
default:
return 0;
}
}
//---
PG_FUNCTION_INFO_V1(inet_inc);
Datum
inet_inc(PG_FUNCTION_ARGS)
{
inet*src = PG_GETARG_INET_P(0);
int64arg = PG_GETARG_INT64(1);
inet*dst;
uint64 wsp;
// allocate destination structure
dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
// copy to destination
*((inet_struct *)VARDATA(dst)) = *((inet_struct *)VARDATA(src));
if (ip_family(dst) == PGSQL_AF_INET)
{
// Increment v4 address w/ item truncated to 32 bits
*((uint32*)(ip_addr(dst))) =
htonl(ntohl(*((int32*)(ip_addr(dst + (int32)arg);
}
else
{
// Increment v6 address low qword (store to workspace)
wsp = htobe64(betoh64(*((int64*)(ip_addr(dst) + 8))) + arg);
*((uint64*)(ip_addr(dst) + 8)) = wsp;
// Carry/borrow high qword
if ( arg 0 wsp *((uint64*)(ip_addr(src) + 8)) )
{ *((int64*)(ip_addr(dst))) =
htobe64(betoh64(*((int64*)(ip_addr(dst + 1);
}
else
if ( arg 0 wsp *((uint64*)(ip_addr(src) + 8)) )
{ *((int64*)(ip_addr(dst))) =
htobe64(betoh64(*((int64*)(ip_addr(dst - 1);
}
}
// Return result
VARATT_SIZEP(dst) = VARHDRSZ
+ ((char *) ip_addr(dst) - (char *) VARDATA(dst))
+ ip_addrsize(dst);
PG_RETURN_INET_P(dst);
}
-
Thank you
Ilya A. Kovalenko (mailto:[EMAIL PROTECTED])
SpecialEQ SW section
JSC Oganer-Service
P.S. Treat as Public Domain
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] argtype_inherit() is dead code
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. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] argtype_inherit() is dead code
On Sun, 2005-04-17 at 14:04 -0400, Alvaro Herrera wrote: On Sun, Apr 17, 2005 at 06:56:01AM -0700, Joshua D. Drake wrote: From a people who call me perspective. I am never asked about inheritance. Most of the people don't even know it is there. The requests I get are: Just wondering, does anybody asks you about the excessive locking (and deadlocking) on foreign keys? The business about being able to drop users and then find out they were still owners of something? I guess I worry about things too low-level that nobody really cares too much about. I know of plenty of people impacted by foreign key locking that remove specific keys in production that they have in place for testing. -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] argtype_inherit() is dead code
On Sun, 2005-04-17 at 19:54 -0400, Robert Treat wrote: On Sunday 17 April 2005 19:30, Rod Taylor wrote: On Sun, 2005-04-17 at 14:04 -0400, Alvaro Herrera wrote: On Sun, Apr 17, 2005 at 06:56:01AM -0700, Joshua D. Drake wrote: From a people who call me perspective. I am never asked about inheritance. Most of the people don't even know it is there. The requests I get are: Just wondering, does anybody asks you about the excessive locking (and deadlocking) on foreign keys? The business about being able to drop users and then find out they were still owners of something? I guess I worry about things too low-level that nobody really cares too much about. I know of plenty of people impacted by foreign key locking that remove specific keys in production that they have in place for testing. That or put calls into try/catch mechanisms just in case it deadlocks even though it wouldn't with some less restrictive locking mechanism. Or come up with some type of serializing scheme to ensure deadlocks can't happen. Or Deadlocks weren't the issue, insert serialization by the FKey locks was the issue. several other bad schemes Alvaro, there are many pints waiting for you from a great many postgresql users if you can eliminate this problem with the work you're doing on shared row locks. Agreed. -- ---(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] argtype_inherit() is dead code
On Sunday 17 April 2005 19:30, Rod Taylor wrote:
On Sun, 2005-04-17 at 14:04 -0400, Alvaro Herrera wrote:
On Sun, Apr 17, 2005 at 06:56:01AM -0700, Joshua D. Drake wrote:
From a people who call me perspective. I am never asked about
inheritance. Most of the people don't even know it is there.
The requests I get are:
Just wondering, does anybody asks you about the excessive locking (and
deadlocking) on foreign keys? The business about being able to drop
users and then find out they were still owners of something? I guess I
worry about things too low-level that nobody really cares too much about.
I know of plenty of people impacted by foreign key locking that remove
specific keys in production that they have in place for testing.
That or put calls into try/catch mechanisms just in case it deadlocks even
though it wouldn't with some less restrictive locking mechanism. Or come up
with some type of serializing scheme to ensure deadlocks can't happen. Or
several other bad schemes Alvaro, there are many pints waiting for you
from a great many postgresql users if you can eliminate this problem with the
work you're doing on shared row locks.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] argtype_inherit() is dead code
From a people who call me perspective. I am never asked about inheritance. Most of the people don't even know it is there. The requests I get are: Just wondering, does anybody asks you about the excessive locking (and deadlocking) on foreign keys? The business about being able to drop users and then find out they were still owners of something? I guess I worry about things too low-level that nobody really cares too much about. I get regularly bitten by the former, and have been bitten by the latter :) Don't worry, I think your work is invaluable! They are subtle problems for people to notice, so they're not as prevalent. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with PITR recovery
Bruce Momjian wrote: I figured that part of the goal of PITR was that you could recover from just the tar backup and archived WAL files --- using the pg_xlog contents is nice, but not something we can require. I understood the last missing WAL log would cause missing information, but not that it would make the tar backup unusable. It would be nice if we could force a new WAL file on pg_stop_backup() and archive the WAL file needed to match the tar file. How hard would that be? I see in the docs: To make use of this backup, you will need to keep around all the WAL segment files generated at or after the starting time of the backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need to have to make use of the backup. For example, if the starting WAL file is 0001123455CD the backup history file will be named something like 0001123455CD.007C9330.backup. (The second part of this file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the backup dump file, you can delete all archived WAL segments with names numerically preceding this one. I am not clear on what the backup dump file is? I assume it means 0001123455CD. It is called WAL segment file above. I will rename that phrase to match the above terminology. Patch attached and applied. I found that the docs mention above are inaccurate because they state you only need the WAL segment used at the start of the file system backup, while you really need all the WAL segments used _during_ the backup before you can safely delete the older WAL segments. Here is updated text I have applied to HEAD and 8.0.X: Once you have safely archived the WAL segment files used during the file system backup (as specified in the backup history file), you can delete all archived WAL segments with names numerically less. 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. -- 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
oops - FUNCTION inet_inc(int, int8) RETURNS inet + FUNCTION inet_inc(inet, int8) RETURNS inet ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Problem with PITR recovery
pgman wrote: I figured that part of the goal of PITR was that you could recover from just the tar backup and archived WAL files --- using the pg_xlog contents is nice, but not something we can require. I understood the last missing WAL log would cause missing information, but not that it would make the tar backup unusable. It would be nice if we could force a new WAL file on pg_stop_backup() and archive the WAL file needed to match the tar file. How hard would that be? Added to TODO: * Force archiving of partially-full WAL files when pg_stop_backup() is called or the server is stopped -- 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] Problem with PITR recovery
I could still use a little clarification. It seems sort of like there is an extra step, like: (1) start archiving (2) pg_start_backup() (3) copy PGDATA directory with tar (4) pg_stop_backup() (5) ?? And the text you have at http://candle.pha.pa.us/main/writings/pgsql/sgml/backup-online.html says: To make use of this backup, you will need to keep around all the WAL segment files generated during and after the file system backup.. How long after? Wouldn't you be keeping the WAL segments afterward anyway by archiving? I've tested and been able to recover using PITR before, but I'd like a little clarification on the steps to make absolutely sure that the base backup I have is viable. Can you sort of run through the failure case again, and how to prevent it? Regards, Jeff Davis On Sun, 2005-04-17 at 21:38 -0400, Bruce Momjian wrote: Bruce Momjian wrote: I figured that part of the goal of PITR was that you could recover from just the tar backup and archived WAL files --- using the pg_xlog contents is nice, but not something we can require. I understood the last missing WAL log would cause missing information, but not that it would make the tar backup unusable. It would be nice if we could force a new WAL file on pg_stop_backup() and archive the WAL file needed to match the tar file. How hard would that be? I see in the docs: To make use of this backup, you will need to keep around all the WAL segment files generated at or after the starting time of the backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need to have to make use of the backup. For example, if the starting WAL file is 0001123455CD the backup history file will be named something like 0001123455CD.007C9330.backup. (The second part of this file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the backup dump file, you can delete all archived WAL segments with names numerically preceding this one. I am not clear on what the backup dump file is? I assume it means 0001123455CD. It is called WAL segment file above. I will rename that phrase to match the above terminology. Patch attached and applied. I found that the docs mention above are inaccurate because they state you only need the WAL segment used at the start of the file system backup, while you really need all the WAL segments used _during_ the backup before you can safely delete the older WAL segments. Here is updated text I have applied to HEAD and 8.0.X: Once you have safely archived the WAL segment files used during the file system backup (as specified in the backup history file), you can delete all archived WAL segments with names numerically less. 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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Problem with PITR recovery
Jeff Davis wrote: I could still use a little clarification. It seems sort of like there is an extra step, like: (1) start archiving (2) pg_start_backup() (3) copy PGDATA directory with tar (4) pg_stop_backup() (5) ?? And the text you have at http://candle.pha.pa.us/main/writings/pgsql/sgml/backup-online.html says: To make use of this backup, you will need to keep around all the WAL segment files generated during and after the file system backup.. How long after? Wouldn't you be keeping the WAL segments afterward anyway by archiving? I've tested and been able to recover using PITR before, but I'd like a little clarification on the steps to make absolutely sure that the base backup I have is viable. Can you sort of run through the failure case again, and how to prevent it? The failure case in the original docs is that you do your pg_stop_backup(), and then delete all the WAL file before the *.backup file that was just created. However, you do not have a valid tar backup until you have archived all the WAL files used from the *.backup WAL file up to the WAL file that was active at pg_stop_backup(), which is mentioned in the *.backup file. If you went and deleted your old WAL files anyway, without waiting for those other WAL files to be archived, and your disk drive crashed, you wouldn't have a tar backup you could use, and you had deleted the old WAL files you would have needed to recover your previous tar backup. Is there something in the current wording that needs clarification? -- 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
[HACKERS] Urgent
Hi, My postgresql was running successfully in my Redhat LInux 9. After 1 day when i try to login to the folder /usr/local/pgsql/data to update the ip address i am getting as [EMAIL PROTECTED] data]# ls Segmentation fault I am unable to see the files under /usr/local/pgsql/data/, I need to update the file pg_hba.conf. Plz help me. -- Warm Regards, S.ElayaRaja Mobile: (+91) 98450 59540 E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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
