Re: [HACKERS] stats collector dies in current

[Tom Lane]

Oliver Jowett <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> However, I would still like to know why 7.4 didn't show the same
>> misbehavior, when it isn't using this flag.

> It looks like the 7.4 code never unblocks signals in the collector 
> process, so that process never gets stopped by SIGTSTP.

Good catch --- that seems to explain all the facts.

Since the collector SIG_IGN's all the signals it'd be likely to get in
normal operation, it's not surprising we did not notice this.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] stats collector dies in current

[Oliver Jowett]

Tom Lane wrote:
Oliver Jowett <[EMAIL PROTECTED]> writes:
I think pqsignal should be passing SA_NOCLDSTOP in sa_flags,

With that patch applied, the problem is indeed gone on my system.
However, I would still like to know why 7.4 didn't show the same
misbehavior, when it isn't using this flag.
It looks like the 7.4 code never unblocks signals in the collector 
process, so that process never gets stopped by SIGTSTP.

On the 7.4.1 install I have to hand, from /proc//status, the buffer 
process reports:

SigPnd: 
ShdPnd: 
SigBlk: 
SigIgn: 01006a07
SigCgt: 0001
while the collector process has:
SigPnd: 
ShdPnd: 
SigBlk: 3ff9fa07
SigIgn: 01007a07
SigCgt: 
TSTP is signal 20 here which appears to be blocked (mask of 8) in 
the collector process.

A quick glance at the REL7_4_STABLE pgstat.c shows only one PG_SETMASK, 
executed in the buffer process only.

-O
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] [ADMIN] SRPM for 8.0.0 beta?

[Joe Conway]

Steve Bergman wrote:
Anyone have an SRPM of spec file for the beta?
(moved to HACKERS)
I just created and posted a source RPM for 8.0.0beta1. As I'm not the 
usual packager, and a pure hack when it comes to building my own RPMs, 
it would be a Good Thing(tm) if someone else could vet this package; 
available here:
  http://www.joeconway.com/postgresql-8.0.0beta1/
There are binaries for Fedora Core 2 there as well.

I think it would help us get wider beta testing if others could build 
binaries for other distributions.

Joe
---(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] stats collector dies in current

[Tom Lane]

Oliver Jowett <[EMAIL PROTECTED]> writes:
> I think pqsignal should be passing SA_NOCLDSTOP in sa_flags,

With that patch applied, the problem is indeed gone on my system.
However, I would still like to know why 7.4 didn't show the same
misbehavior, when it isn't using this flag.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] stats collector dies in current

[Tom Lane]

Oliver Jowett <[EMAIL PROTECTED]> writes:
> I think pqsignal should be passing SA_NOCLDSTOP in sa_flags,

Hmm, that does look like a good idea ... but it does not explain why 7.4
doesn't have the same problem.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] stats collector dies in current

[Oliver Jowett]

Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> writes:
In that context, is SIGTSTP similar to SIGSTOP in that it cannot be 
caught or ignored?

Possibly.  I've reproduced the problem here on an RHL 8 system
(2.4.18 kernel) and I think it's a kernel bug.  Points:
[...]
I can reproduce this on a 2.6.7 kernel.
I think pqsignal should be passing SA_NOCLDSTOP in sa_flags, or 
alternatively that the stats buffer process should check that its child 
really did die rather than receive a stop signal. The sigaction manpage 
says:

   sa_flags specifies a set of flags which modify the behaviour of the signal 
handling process. It is formed by the bit-
   wise OR of zero or more of the following:
  SA_NOCLDSTOP
 If signum is SIGCHLD, do not receive notification when child 
processes stop (i.e., when child processes
 receive one of SIGSTOP, SIGTSTP, SIGTTIN or SIGTTOU).
signal(7) says that SIGCHLD is generated when a child is stopped or 
terminated.

A bit of experimentation in the stats buffer process seems to confirm 
this -- while it is receiving a SIGCHLD, calling waitpid() with WNOHANG 
returns immediately with no dead processes.

-O
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] 8.0 beta status

[Gavin Sherry]

On Sat, 14 Aug 2004, Bruce Momjian wrote:

> Christopher Kings-Lynne wrote:
> > > Yeah, those are all bug fixes and okay for post-beta I think.  But which
> > > two tablespace failures are you thinking of exactly?  The last couple
> > > weeks have been a bit of a blur for me...
> >
> > http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.au&rnum=4
>
> I am confused.  You had:
>
>   template1=# create tablespace blah location '/home/swm/tbl/tspc1/';
>   CREATE TABLESPACE
>   template1=# create table foo (i int) tablespace blah;
>   CREATE TABLE
>   template1=# create database foo tablespace blah;
>   ERROR:  could not initialize database directory
>   DETAIL:  Directory "/home/swm/tbl/data/pg_tblspc/17227/17230" already
>   exists.
>
> but with CVS head I see:
>
>   $ psql -a testcreate tablespace blah location '/bjm/tmp';
>   CREATE TABLESPACE
>   create table foo (i int) tablespace blah;
>   CREATE TABLE
>   create database foo tablespace blah;
>   CREATE DATABASE

You need to create a condition where by the new database would have to
merge a table into its default database. If you did the above in the
template1 database, you would get the same effect.

I sent a (partial) fix for this, which included some documentation
improvements. However, I posed a question. *digs*

[PATCHES] CREATE DATABASE with tablespace fix

was the subject.

Gavin

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] stats collector dies in current

[Tom Lane]

Jan Wieck <[EMAIL PROTECTED]> writes:
> In that context, is SIGTSTP similar to SIGSTOP in that it cannot be 
> caught or ignored?

Possibly.  I've reproduced the problem here on an RHL 8 system
(2.4.18 kernel) and I think it's a kernel bug.  Points:

1. AFAICS, the only case where the stats buffer process will exit(1)
without logging a prior message is where it's gotten SIGCHLD.  So,
hypothesis: it is the collector process (grandchild process) that
is dying.

2. Experiment one: try to strace the collector process to see what
it's doing.  Result: failure goes away!!!

3. Experiment two: try to strace the buffer process.  Result: indeed
it's getting SIGCHLD (in fact it seems to get it before SIGTSTP
arrives).

So at the very least we've got a Heisenbug, but my opinion is we are
seeing broken kernel behavior.

The only difference in signal handling that I can see from 7.4 is that
the collector process explicitly executes pqsignal calls to re-establish
all the signal handlers it should have inherited from its parent.
I suspect (but haven't tested) that removing that supposedly redundant
code would make the failure go away again.

The handler re-establishment was put in because it is needed for the
EXEC_BACKEND case, but possibly we could make it #ifndef EXEC_BACKEND
to work around this problem.

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] stats collector dies in current

[Jan Wieck]

On 8/14/2004 11:38 PM, Tom Lane wrote:
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
I see stats collector processes die in current when I suspend
postmaster then put it in background from a terminal:

Is this normal?
Doesn't 7.4 behave the same?
It looks to me like 7.4 and current have the same signal handling.
I'm not sure why a tstp/cont sequence would create a problem on
your platform (which is what, btw?) but it ought to cause the same
problem in 7.4 ...
In that context, is SIGTSTP similar to SIGSTOP in that it cannot be 
caught or ignored?

Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [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] 7.4 backpatches

[Christopher Kings-Lynne]

Ooh, how about the 'there is no security checking on the cluster on 
index command' bug?

Chris
Christopher Kings-Lynne wrote:
Hi,
Should be backport the 'fix acls on owner to' on tables to 7.4?  The 
other acl objects did not have owner to commands in 7.4 IIRC, but the 
alter table is definitely broken.

Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html
---(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] stats collector dies in current

[Tatsuo Ishii]

> Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> > I see stats collector processes die in current when I suspend
> > postmaster then put it in background from a terminal:
> 
> > Is this normal?
> 
> Doesn't 7.4 behave the same?

No.

> It looks to me like 7.4 and current have the same signal handling.
> I'm not sure why a tstp/cont sequence would create a problem on
> your platform (which is what, btw?) but it ought to cause the same
> problem in 7.4 ...

This is a Linux box with kernel 2.4.22 (x86). I also noticed that the
background writer process does have almost same signal handling but it
is not killed.
--
Tatsuo Ishii

---(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] 8.0 beta status

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> However, I don't see any CVS commit that fixed this?  What am I missing?

The failure case is where the template database has a conflicting
table.  You didn't show us where you created that table, but it
evidently was not in template1.

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] stats collector dies in current

[Tom Lane]

Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> I see stats collector processes die in current when I suspend
> postmaster then put it in background from a terminal:

> Is this normal?

Doesn't 7.4 behave the same?

It looks to me like 7.4 and current have the same signal handling.
I'm not sure why a tstp/cont sequence would create a problem on
your platform (which is what, btw?) but it ought to cause the same
problem in 7.4 ...

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] 8.0 beta status

[Bruce Momjian]

Tom Lane wrote:
> > http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=4107211C.2050508%40familyhealth.com.au&rnum=5
> 
> I think the problem here is that we don't have a syntax for saying
> "my tablespace is the same as my database's default tablespace" or "my
> tablespace is the same as my schema's default tablespace", when there is
> an intermediate object (schema or table) that isn't using that
> tablespace. (Note that "TABLESPACE pg_default" does definitely not mean
> either of these.)
> 
> This is fixable with some special syntax but is it worth the trouble?

Seems we have to come up with some solution or dump/reload will not put
things back in the same place for certain unusual configurations.

Added to open items:

* fix ambiguity for objects using default tablespaces

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] 8.0 beta status

[Bruce Momjian]

Christopher Kings-Lynne wrote:
> > Yeah, those are all bug fixes and okay for post-beta I think.  But which
> > two tablespace failures are you thinking of exactly?  The last couple
> > weeks have been a bit of a blur for me...
> 
> http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.au&rnum=4

I am confused.  You had:

template1=# create tablespace blah location '/home/swm/tbl/tspc1/';
CREATE TABLESPACE
template1=# create table foo (i int) tablespace blah;
CREATE TABLE
template1=# create database foo tablespace blah;
ERROR:  could not initialize database directory
DETAIL:  Directory "/home/swm/tbl/data/pg_tblspc/17227/17230" already
exists.

but with CVS head I see:

$ psql -a test http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] stats collector dies in current

[Tatsuo Ishii]

I see stats collector processes die in current when I suspend
postmaster then put it in background from a terminal:

$ ps x
:
:
21638 pts/1S  0:00 /bin/bash -i
30525 pts/1S  0:00 postmaster
30527 pts/1S  0:00 postgres: writer process   
30528 pts/1S  0:00 postgres: stats buffer process   
30529 pts/1S  0:00 postgres: stats collector process   
30530 pts/1R  0:00 ps x
$ fg
postmaster

[1]+  Stopped postmaster
$ bg
[1]+ postmaster &
LOG:  statistics collector process (PID 30528) exited with exit code 1

Is this normal?
--
Tatsuo Ishii

---(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] DROP TABLESPACE causes panic during recovery

[Bruce Momjian]

Added to open items:

* fix recovery of DROP TABLESPACE after checkpoint


---

Tom Lane wrote:
> Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> > Maybe we could avoid removing it until the next checkpoint?  Or is that 
> > not enough.  Maybe it could stay there forever :/
> 
> Part of the problem here is that this code has to serve several
> purposes.  We have different scenarios to worry about:
> 
>   * crash recovery from the most recent checkpoint
> 
>   * PITR replay over a long interval (many checkpoints)
> 
>   * recovery in the face of a partially corrupt filesystem
> 
> It's the last one that is mostly bothering me at the moment.  I don't
> want us to throw away data simply because the filesystem forgot an
> inode.  Yeah, we might not have enough data in the WAL log to completely
> reconstruct a table, but we should push out what we do have, *not* toss
> it into the bit bucket.
> 
> In the first case (straight crash recovery) I think it is true that any
> reference to a missing file is a reference to a file that will get
> deleted before recovery finishes.  But I don't think that holds for PITR
> (we might be asked to stop short of where the table gets deleted) nor
> for the case where there's been filesystem damage.
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] Catching up

[Bruce Momjian]

I am catching up on my email.  I am OK reading the email, but am
back-logged in dealing with email that needs my attention.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] [Fwd: Re: [pgsql-hackers-win32] Import from Linux to

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> The patch is not platform-specific. It simply makes psql accept the same 
> line endings on COPY FROM that the backend will accept - in effect it 
> makes it line-end agnostic - this is a Good Thing (tm).

Strictly speaking it's not there yet --- psql still doesn't cope with
line endings that are \r only (original Mac OS style).  However, since
we do not and probably never will have a port to original Mac OS, I'm
satisfied with the code as-is.

> We are still months away from releasing 8.0, so I think doing this for 
> tomorrow's 7.4 bundle makes sense.

I agree (and did in fact just commit the back-patch a bit ago).

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] will PITR in 8.0 be usable for "hot spare"/"log

[Eric Kerin]

On Sat, 2004-08-14 at 01:11, Tom Lane wrote:
> Eric Kerin <[EMAIL PROTECTED]> writes:
> > The issues I've seen are:
> > 1. Knowing when the master has finished the file transfer transfer to
> > the backup.
> 
> The "standard" solution to this is you write to a temporary file name
> (generated off your process PID, or some other convenient reasonably-
> unique random name) and rename() into place only after you've finished
> the transfer.  
Yup, much easier this way.  Done.

> > 2. Handling the meta-files, (.history, .backup) (eg: not sleeping if
> > they don't exist)
> 
> Yeah, this is an area that needs more thought.  At the moment I believe
> both of these will only be asked for during the initial microseconds of
> slave-postmaster start.  If they are not there I don't think you need to
> wait for them.  It's only plain ol' WAL segments that you want to wait
> for.  (Anyone see a hole in that analysis?)
> 
Seems to be working fine this way, I'm now just returning ENOENT if they
don't exist.  

> > 3. Keeping the backup from coming online before the replay has fully
> > finished in the event of a failure to copy a file, or other strange
> > errors (out of memory, etc).
> 
> Right, also an area that needs thought.  Some other people opined that
> they want the switchover to occur only on manual command.  I'd go with
> that too if you have anything close to 24x7 availability of admins.
> If you *must* have automatic switchover, what's the safest criterion?
> Dunno, but let's think ...

I'm not even really talking about automatic startup on fail over.  Right
now, if the recovery_command returns anything but 0, the database will
finish recovery, and come online.  This would cause you to have to
re-build your backup system from a copy of master unnecessarily.  Sounds
kinda messy to me, especially if it's a false trigger (temporary io
error, out of memory)


What I think might be a better long term approach (but probably more of
an 8.1 thing).  Have the database go in to a read-only/replay mode,
accept only read-only commands from users.  A replay program opens a
connection to the backup system's postmaster, and tells it to replay a
given file when it becomes available. Once you want the system to come
online, the DBA will call a different function that will instruct the
system to come fully online, and start accepting updates from users.

This could be quite complex, but provides two things: proper log
shipping with status, (without the false fail->db online possibility)
and a read-only replicated backup system(s), which would also be good
for a reporting database.

Thoughts?


Anyway, here's a re-written program for my implementation of log
shipping:  http://www.bootseg.com/log_ship.c It operates mostly the
same, but most of the stupid bugs are fixed.  The old one was renamed to
http://www.bootseg.com/log_ship.c.ver1 if you really want it.

Thanks, 
Eric



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] [Fwd: Re: [pgsql-hackers-win32] Import from Linux to

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> Bruce Momjian wrote:
> 
> >One issue is that pre-8.0, psql files were opened in Win32 text mode, so
> >we wouldn't have seen this bug on Win32, but we would on Linux.  
> >Because we open them on Win32 now in binary mode so we see control-Z it
> >will show up on Win32 too.
> >
> >  
> >
> 
> true, *BUT*
> 
> The patch is not platform-specific. It simply makes psql accept the same 
> line endings on COPY FROM that the backend will accept - in effect it 
> makes it line-end agnostic - this is a Good Thing (tm).
> 
> We are still months away from releasing 8.0, so I think doing this for 
> tomorrow's 7.4 bundle makes sense.

Yes, totally agree.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Calling PL functions with named parameters

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Josh Berkus wrote:
>> b) Procedures are not automatically transactional; that is,
>> transactions within procedures must/can be explicit.  Among other
>> things, this would allow procedures to run maintainence tasks.

> I certainly want all my maintenance tasks to be transactional.  Being 
> nontransactional is a fuzzy idea anyway.  You can't really run anything 
> without a transaction in PostgreSQL.

Yeah, but the point is that a procedure in these terms could start and
end transactions.  Right now, any process you want to perform that has
to span more than one transaction has to be driven by client-side code.
It *would* be nice to be able to encapsulate such things totally on the
server side.  The actual work would of course all be inside
transactions, but if we could have (some limited amount of) control
logic outside it would be a nice addition.

I agree however with Andrew's nearby point that this is completely
unrelated to named parameters to functions/procedures, or to defaults
for parameters.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [Fwd: Re: [pgsql-hackers-win32] Import from Linux to

[Andrew Dunstan]

Bruce Momjian wrote:
One issue is that pre-8.0, psql files were opened in Win32 text mode, so
we wouldn't have seen this bug on Win32, but we would on Linux.  
Because we open them on Win32 now in binary mode so we see control-Z it
will show up on Win32 too.

 

true, *BUT*
The patch is not platform-specific. It simply makes psql accept the same 
line endings on COPY FROM that the backend will accept - in effect it 
makes it line-end agnostic - this is a Good Thing (tm).

We are still months away from releasing 8.0, so I think doing this for 
tomorrow's 7.4 bundle makes sense.

cheers
andrew
---(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] [Fwd: Re: [pgsql-hackers-win32] Import from Linux to

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> Tom Lane wrote:
> 
> >"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
> >  
> >
> >>No, I think 7.4 should do. 7.3 users will still have the dos2unix workaround
> >>available. Are you going to do the 7.4 patch, or do you need me to? I
> >>normally only keep a HEAD tree checked out. A quick look at the cvsweb diffs
> >>suggests the patch should apply cleanly but with different line offsets.
> >>
> >>
> >
> >If you're sure the code in that routine hasn't changed since 7.4, then I
> >can just apply the patch to that branch.
> >
> >
> >  
> >
> 
> It has - the prompt changes in version 1.37. But I don't think that 
> conflict with this patch. I'll have a look this morning.

One issue is that pre-8.0, psql files were opened in Win32 text mode, so
we wouldn't have seen this bug on Win32, but we would on Linux.  
Because we open them on Win32 now in binary mode so we see control-Z it
will show up on Win32 too.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Calling PL functions with named parameters

[Andrew Dunstan]

Peter Eisentraut wrote:
Josh Berkus wrote:
 

Dennis has pointed out that mixing the call-with-named-parameter
interface with call-by-order-of-parameters one would cause
confusion, and I think it would be OK to disallow this type mixing,
so
 

As we've discussed on IRC, this should be the difference between a
FUNCTION and a PROCEDURE.
   

Huh?  As far as I can tell, the difference between a function and a 
procedure is precisely that the latter doesn't return a value.  A 
consistent way to specify the parameters of either one would certainly 
be highly desirable.

 

b) Procedures are not automatically transactional; that is,
transactions within procedures must/can be explicit.  Among other
things, this would allow procedures to run maintainence tasks.
   

I certainly want all my maintenance tasks to be transactional.  Being 
nontransactional is a fuzzy idea anyway.  You can't really run anything 
without a transaction in PostgreSQL.

 

I think you're right on both counts.
ISTM we need a more strategic discussion of where we want to go with 
procedural code.

some other ideas to consider:
- it would be nice to be able to say PERFORM 'string with plcode' 
language plname;
- OUT / INOUT parameters

I think we need an idea of where we are going with all this stuff, 
rather than approaching the area piecemeal in a way that might preclude 
other decisions we might want to make later.

cheers
andrew
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Calling PL functions with named parameters

[Peter Eisentraut]

Josh Berkus wrote:
> > Dennis has pointed out that mixing the call-with-named-parameter
> > interface with call-by-order-of-parameters one would cause
> > confusion, and I think it would be OK to disallow this type mixing,
> > so
>
> As we've discussed on IRC, this should be the difference between a
> FUNCTION and a PROCEDURE.

Huh?  As far as I can tell, the difference between a function and a 
procedure is precisely that the latter doesn't return a value.  A 
consistent way to specify the parameters of either one would certainly 
be highly desirable.

> b) Procedures are not automatically transactional; that is,
> transactions within procedures must/can be explicit.  Among other
> things, this would allow procedures to run maintainence tasks.

I certainly want all my maintenance tasks to be transactional.  Being 
nontransactional is a fuzzy idea anyway.  You can't really run anything 
without a transaction in PostgreSQL.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Postgres development model

[Christopher Browne]

In the last exciting episode, [EMAIL PROTECTED] (Bruce Momjian) wrote:
> Tom Lane wrote:
>> Christopher Browne <[EMAIL PROTECTED]> writes:
>> > Oops! [EMAIL PROTECTED] (Reinoud van Leeuwen) was seen spray-painting on a wall:
>> >> Why? I understood that using BitKeeper for free for Open Source projects 
>> >> is allowed. (but IANAL).
>> 
>> > Ah, but there's a problem with BK _actually seen in production_ in
>> > that people that work on competing products are not permitted to use
>> > it.
>> 
>> In particular, I would have to resign from the project if we went over
>> to BK, as my employer (Red Hat) is affected by this restriction.  BK
>> does not meet the accepted definition of Open Source because of this
>> unfriendly license clause.
>
> How do the Linux kernel developer guys go from no revision system (just
> Linus's hard drive) to Bitkeeper and requring a signed authorization
> letter from each contributor?  They went from too little to too much,
> and never hit the happy medium.

What happened was that Linus Torvalds essentially said "what are the
features I need to better merge together contributions?" and Larry
McVoy responded with "We'll build what you need!"  (There was
certainly a lot of back and forth conversation, but that's the short
answer as to how it happened...)

It is pretty interesting how successful BK turned out to be for the
purpose.  But definitely "some rights were VERY MUCH reserved."
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/linux.html
"The social dynamics  of the net are a direct  consequence of the fact
that nobody  has yet developed  a Remote Strangulation  Protocol."  
-- Larry Wall

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Calling PL functions with named parameters

[Josh Berkus]

David,

> Dennis has pointed out that mixing the call-with-named-parameter
> interface with call-by-order-of-parameters one would cause confusion,
> and I think it would be OK to disallow this type mixing, so

As we've discussed on IRC, this should be the difference between a FUNCTION 
and a PROCEDURE.  It's about time we implemented a CREATE PROCEDURE statement 
anyway.  

As I see it, the critical difference between FUNCTIONS and PROCEDURES can be 
that:
a) Procedures support named parameter calling but do NOT allow being called in 
the context of a query, and
b) Procedures are not automatically transactional; that is, transactions 
within procedures must/can be explicit.  Among other things, this would allow 
procedures to run maintainence tasks.

This seems to me to be the obvious solution to the Named Parameters vs. 
Overloading conflict, and I'm really puzzled why people keep dismissing it as 
out-of-hand.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] 8.0beta1 with unixware

[ohp]

HI,

Compiling beta1 on Unixware 714 is OK, regression test are ok (with the
mp1 patch applied)

However make check-install fails if default_with_oids=false in
postgresql.conf (witch is normal regarding regression tests) but they
might be changed in a other version when you guys change this to false by
default.

Also, maakefiles/Makefile.unixware might need ajustement in the way -R is
used:

cc docs state the LD_RUN_PATH value is ignored if you use -R in the
linking phase. So I can't rely on that to add /usr/local/lib to the
harcoded library named.

seting LDFLAGS="-R/usr/local/lib" before configure / make is not an option
either because in case of multiple -R , only the last one is taken into
account.

I could'nt find with variable did old library path names to add it to
the rpath variable

Also, could anyone explain to me how to get localized messages appear?
I've installed bsd-gettext like said in the INSTALL file but couldn't get
fr messages anyway...

Anyway, my first test are very encouraging...

Many tanks for the great work

-- 
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou   +33-5-61-50-97-01 (Fax)
31190 AUTERIVE   +33-6-07-63-80-64 (GSM)
FRANCE  Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] 7.4 backpatches

[Tom Lane]

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> Should be backport the 'fix acls on owner to' on tables to 7.4?

I'm inclined not to, as (a) it's a moderately complex change that hasn't
yet been tested to my satisfaction, and (b) AFAIR we've not had any
complaints from the field about it, even though the issue has existed
in some form for a long time.

Given (b), I don't think it's a serious enough problem to take any risks
of introducing new bugs.

Also, concerning point (a), I think that the patch interacts with 8.0
changes that we cannot backport, such as the removal of explicit
representation of owner grant options in ACLs.  Any backport would be a
partial copying of 8.0 changes, and as such would have received no prior
testing whatsoever :-(

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] Calling PL functions with named parameters

[Andreas Pflug]

Tom Lane wrote:
David Fetter <[EMAIL PROTECTED]> writes:
CREATE OR REPLACE FUNCTION foo_func(name TEXT, val INTEGER) AS ...

SELECT foo_func(val AS 23, name AS 'Name goes here');

I don't think that syntax will work.  You could possibly do it the other
way round:
SELECT foo_func(23 AS val, 'Name goes here' AS name);
which would have some commonality with SELECT's column-labeling syntax
but otherwise seems to have little to recommend it.  Are there any other
vendors supporting such things in SQL, and if so how do they do it?
MSSQL's syntax for calling named parameters is like this:
CREATE PROCEDURE SampleProcedure @EmployeeIDParam INT,
@MaxQuantity INT OUTPUT AS ...
DECLARE @MaxQtyVariable INT
EXEC @rc = SampleProcedure @EmployeeIDParam = 9,
@MaxQuantity = @MaxQtyVariable OUTPUT
This is commonly used if a parameter should be left default (and I don't 
like it).

Regards,
Andreas
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] will PITR in 8.0 be usable for "hot spare"/"log shipping" type of replication

[Tom Lane]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>> Right, also an area that needs thought.  Some other people opined that
>> they want the switchover to occur only on manual command.  I'd go with
>> that too if you have anything close to 24x7 availability of admins.
>> If you *must* have automatic switchover, what's the safest criterion?
>> Dunno, but let's think ...

> That's fairly straightforward.

> You use a recovery_command that sleeps when it discovers a full log file
> isn't available - i.e. it has requested the "last" or master-current WAL
> file. The program wakes when the decision/operator command to switchover is
> taken.

But you're glossing over the hard part, which is how to take that
decision (assuming that for some reason you can't afford to wait for
a human to make it).

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] PITR on Windows?

[Andrew Dunstan]

Gaetano Mendola wrote:
BTW other projects are carrying also a build version in order to 
distinguish
between two 8.0beta1 versions.


Except that we don't officially publish builds, only source sets.
Actually, this might be a reson to chenge to a different SCC system - 
svn at least has a unique tree number for every set (it gets bumped 
after every commit), and I could see some value in exposing that 
information. Then if people report bugs against dev/beta versions, we 
could ask then for the tree number, which would be more helpful than the 
label. Not sure if other systems have this feature - I do know I 
recently looked at svn and decided it wasn't quite stable enough for my 
company's needs - they had a file format change earlier this year, which 
worried me.

cheers
andrew
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Calling PL functions with named parameters

[Gaetano Mendola]

Oliver Jowett wrote:
Gaetano Mendola wrote:
  Oliver Jowett wrote:
 
 > David Fetter wrote:
 >
 >> Dennis has pointed out that mixing the call-with-named-parameter
 >> interface with call-by-order-of-parameters one would cause 
confusion,
 >
 > Python's equivalent syntax allows you to mix the two forms so long
 > as all the by-position parameters come first:

I'm worried about:
(1) foo( integer, float);
(2) foo( integer, integer, float a = 3 );
which one is called with:   foo( 2, 2 )?

This is a separate issue to the one I was discussing above. I am not 
talking about default arguments at all here; I am talking about mixing 
positional parameter syntax with named parameter syntax.
I see.
Regards
Gaetano Mendola

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] PITR on Windows?

[Gaetano Mendola]

Andrew Dunstan wrote:

Gaetano Mendola wrote:
Bruce Momjian wrote:
>
> Yes, the fix will be in beta2 or the next pginstaller release.
Do you mean that the next pginstaller release will carry a 8.0beta1
with different behaviour that actual 8.0beta1 ?
I hope I mistake you.

What are you talking about? The installer project doesn't have to sit 
around waiting for the next beta before they include patches that have 
occurred since then. They can package whatever they like, in fact (and 
they have been doing a fantastic job, IMNSHO). After all, their releases 
are beta too. You can run against cvs HEAD and it will be marked 8.0beta1.
So? I don't like it neither. Am I criticizing their job ?
I think is better have a 8.0beta1 and a 8.0beta1devel, this just in order to
understand which version the users are testing. But of course I'm the last
in this list that can decide about it.
BTW other projects are carrying also a build version in order to distinguish
between two 8.0beta1 versions.
Regards
Gaetano Mendola


---(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] Calling PL functions with named parameters

[Gaetano Mendola]

Oliver Jowett wrote:
> Gaetano Mendola wrote:
>
>> Oliver Jowett wrote:
>>
>>> Gaetano Mendola wrote:
>>>
 Oliver Jowett wrote:

> David Fetter wrote:
>
>> Dennis has pointed out that mixing the call-with-named-parameter
>> interface with call-by-order-of-parameters one would cause confusion,
>
>
>
>
> Python's equivalent syntax allows you to mix the two forms so long
> as all the by-position parameters come first:
>
 python don't have overloaded functions...
>>>
>>>
>>> It doesn't change how you'd handle overloaded functions; you still
>>> have a type for every parameter available.
>>
>>
>>
>> I think will be a mess that will break the "minor surprise" principle,
>> even the bad C++ stays away from this field ( se explicit constructors,
>> and automatic cast limited to only one level ).
>
>
> I don't understand your argument. What is the surprising behaviour you
> are worried about?
I'm worried about:
(1) foo( integer, float);
(2) foo( integer, integer, float a = 3 );
which one is called with:   foo( 2, 2 )?
the first one because have two parameters or the second one
that better match the arguments ?
Whatever policy we adopt someone could argue that the (2) have
a signature with 3 parameters so the (1) shall be called, and
someone can argue that (2) is equivalent to:
(2a) foo(integer, integer)
(2b) foo(integer, integer, float);
so the (2) have to be called.
BTW C++ adopt the latter.

Regards
Gaetano Mendola







---(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] Calling PL functions with named parameters

[Gaetano Mendola]

Harald Fuchs wrote:
In article <[EMAIL PROTECTED]>,
Oliver Jowett <[EMAIL PROTECTED]> writes:

I think you just made my point for me. C++ allows default parameters
and resolves the ambiguity by disallowing ambiguous calls when they
happen.


I'm not sure why C++ doesn't disallow it at declaration time off the
top of my head -- perhaps because you'd get inconsistent behaviour if
the candidates were split across compilation units.

IIRC this was due to multiple unheritance.  You could inherit methods
with the same name and parameter list from two different base classes.
Disallowing that at declaration time would mean disallowing
inheritance (even indirectly) from these two base classes, even though
the derived class didn't use the ambiguous methods.
You get the point, and with a linear hierarchy the last function hide
the previous one:
struct A { void foo(int) {  } };
struct B : A { void foo(int, int a = 3) {  } };
B b;
b.foo(3);
will call the B::foo.

Regards
Gaetano Mendola
---(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] Calling PL functions with named parameters

[Oliver Jowett]

Gaetano Mendola wrote:
  Oliver Jowett wrote:
 
 > David Fetter wrote:
 >
 >> Dennis has pointed out that mixing the call-with-named-parameter
 >> interface with call-by-order-of-parameters one would cause 
confusion,
 >
 > Python's equivalent syntax allows you to mix the two forms so long
 > as all the by-position parameters come first:

I'm worried about:
(1) foo( integer, float);
(2) foo( integer, integer, float a = 3 );
which one is called with:   foo( 2, 2 )?
This is a separate issue to the one I was discussing above. I am not 
talking about default arguments at all here; I am talking about mixing 
positional parameter syntax with named parameter syntax.

-O
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] PITR on Windows?

[Andrew Dunstan]

Gaetano Mendola wrote:
Bruce Momjian wrote:
>
> Yes, the fix will be in beta2 or the next pginstaller release.
Do you mean that the next pginstaller release will carry a 8.0beta1
with different behaviour that actual 8.0beta1 ?
I hope I mistake you.

What are you talking about? The installer project doesn't have to sit 
around waiting for the next beta before they include patches that have 
occurred since then. They can package whatever they like, in fact (and 
they have been doing a fantastic job, IMNSHO). After all, their releases 
are beta too. You can run against cvs HEAD and it will be marked 8.0beta1.

cheers
andrew
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Calling PL functions with named parameters

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Oliver Jowett <[EMAIL PROTECTED]> writes:

> I think you just made my point for me. C++ allows default parameters
> and resolves the ambiguity by disallowing ambiguous calls when they
> happen.


> I'm not sure why C++ doesn't disallow it at declaration time off the
> top of my head -- perhaps because you'd get inconsistent behaviour if
> the candidates were split across compilation units.

IIRC this was due to multiple unheritance.  You could inherit methods
with the same name and parameter list from two different base classes.
Disallowing that at declaration time would mean disallowing
inheritance (even indirectly) from these two base classes, even though
the derived class didn't use the ambiguous methods.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [Fwd: Re: [pgsql-hackers-win32] Import from Linux to

[Andrew Dunstan]

Tom Lane wrote:
"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
 

No, I think 7.4 should do. 7.3 users will still have the dos2unix workaround
available. Are you going to do the 7.4 patch, or do you need me to? I
normally only keep a HEAD tree checked out. A quick look at the cvsweb diffs
suggests the patch should apply cleanly but with different line offsets.
   

If you're sure the code in that routine hasn't changed since 7.4, then I
can just apply the patch to that branch.
 

It has - the prompt changes in version 1.37. But I don't think that 
conflict with this patch. I'll have a look this morning.

cheers
andrew
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] will PITR in 8.0 be usable for "hot spare"/"log shipping" type of replication

[[EMAIL PROTECTED]]

> Tom Lane
> Eric Kerin <[EMAIL PROTECTED]> writes:
> > The issues I've seen are:
> > 1. Knowing when the master has finished the file transfer transfer to
> > the backup.
>
> The "standard" solution to this is you write to a temporary file name
> (generated off your process PID, or some other convenient reasonably-
> unique random name) and rename() into place only after you've finished
> the transfer.  If you are paranoid you can try to fsync the file before
> renaming, too.  File rename is a reasonably atomic process on all modern
> OSes.
>
> > 2. Handling the meta-files, (.history, .backup) (eg: not sleeping if
> > they don't exist)
>
> Yeah, this is an area that needs more thought.  At the moment I believe
> both of these will only be asked for during the initial microseconds of
> slave-postmaster start.  If they are not there I don't think you need to
> wait for them.  It's only plain ol' WAL segments that you want to wait
> for.  (Anyone see a hole in that analysis?)

Agreed.

> > 3. Keeping the backup from coming online before the replay has fully
> > finished in the event of a failure to copy a file, or other strange
> > errors (out of memory, etc).
>
> Right, also an area that needs thought.  Some other people opined that
> they want the switchover to occur only on manual command.  I'd go with
> that too if you have anything close to 24x7 availability of admins.
> If you *must* have automatic switchover, what's the safest criterion?
> Dunno, but let's think ...
>

That's fairly straightforward.

You use a recovery_command that sleeps when it discovers a full log file
isn't available - i.e. it has requested the "last" or master-current WAL
file. The program wakes when the decision/operator command to switchover is
taken.

That way, when switchover occurs, you're straight up. No code changes...

This is important because it will allow us to test recovery for many systems
by creating a continuously rolling copy. Implementing this will be the best
way to stress-test the recovery code.

I'm not hugely in favour of copying partially filled log files, but if
that's what people want...as long as we don't change the basic code to
implement it, because then we'll have just created another code path that
will leave PITR untested for most people.

[I discussed all of this before as Automatic Standby Database functionality]

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Calling PL functions with named parameters

[Oliver Jowett]

Gaetano Mendola wrote:
Oliver Jowett wrote:
Gaetano Mendola wrote:
Oliver Jowett wrote:
David Fetter wrote:
Dennis has pointed out that mixing the call-with-named-parameter
interface with call-by-order-of-parameters one would cause confusion,

Python's equivalent syntax allows you to mix the two forms so long 
as all the by-position parameters come first:

python don't have overloaded functions...
It doesn't change how you'd handle overloaded functions; you still 
have a type for every parameter available.

I think will be a mess that will break the "minor surprise" principle,
even the bad C++ stays away from this field ( se explicit constructors,
and automatic cast limited to only one level ).
I don't understand your argument. What is the surprising behaviour you 
are worried about?

-O
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Calling PL functions with named parameters

[Oliver Jowett]

Gaetano Mendola wrote:
C++ manages to solve this problem, although I can't remember the exact 
mechanics (and C++ is usually not a good example to follow anyway ;)
Your're wrong:
try to compile this: [...]

a.cpp:6: error: call of overloaded `foo(int, double)' is ambiguous
a.cpp:1: error: candidates are: void foo(int, float)
a.cpp:2: error: void foo(int, float, int)
usualy C++ is not a good example as SQL is not  :-)
I think you just made my point for me. C++ allows default parameters and 
resolves the ambiguity by disallowing ambiguous calls when they happen.

I'm not sure why C++ doesn't disallow it at declaration time off the top 
of my head -- perhaps because you'd get inconsistent behaviour if the 
candidates were split across compilation units. Since we don't have that 
problem in the SQL function case, we can disallow ambiguity at the time 
of creating the function.

-O
---(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] Calling PL functions with named parameters

[Peter Eisentraut]

David Fetter wrote:
> I'd like to be able to create functions with named parameters that
> could be called with the names in any order.  For example,
>
> CREATE OR REPLACE FUNCTION foo_func(name TEXT, val INTEGER) AS ...
>
> SELECT foo_func(val AS 23, name AS 'Name goes here');

When that was brought up last time, I think the hard part was what 
syntax to use.  You can't use AS because SQL uses that for something 
different.  => might be OK, but then we'd need to disallow that as 
operator name.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(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] 7.4 backpatches

[Christopher Kings-Lynne]

Hi,
Should be backport the 'fix acls on owner to' on tables to 7.4?  The 
other acl objects did not have owner to commands in 7.4 IIRC, but the 
alter table is definitely broken.

Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] [PERFORM] Reiser4

[Peter Eisentraut]

Bruce Momjian wrote:
> Pierre-Frédéric Caillaud wrote:
> > Is there also a possibility to tell Postgres : "I don't care if I
> > lose 30 seconds of transactions on this table if the power goes
> > out, I just want to be sure it's still ACID et al. compliant but
> > you can fsync less often and thus be faster" (with a possibility of
> > setting that on a per-table basis) ?

Then it would be "ACI" compliant.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] 8.0beta1 hot spare how to

[Gaetano Mendola]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
I seen that Eric Kerin did the work suggested by Tom about
how to use the PITR in order to have an hot spare postgres,
writing a C program.
I did the same writing 2 shell scripts, one of them perform
the restore the other one deliver the partial filled wal and
check if the postmaster is alive ( check if the pid process
still exist ).
With these two scripts I'm able to have an hot spare installation,
and the spare one go alive when the first postmaster dies.
How test it:
1) Master node:
modify postgresql.conf using:
~archive_command = 'cp %p /mnt/server/archivedir/%f'
~launch postgres and perform a backup as doc
~http://developer.postgresql.org/docs/postgres/backup-online.html
suggest to do
launch the script:
partial_wal_deliver.sh  /mnt/server/partialdir 
~this script will delivery each 10 seconds the "current" wal file,
~and touch the "alive" file in order to notify the spare node that
~the master node is up and running
2) Spare node:
create a recovery.conf with the line:
~restore_command = 'restore.sh /mnt/server/archivedir/%f %p 
/mnt/server/partialdir'
~replace the content of data directory with the backup performed at point 1,
~remove any file present in the pg_xlog directory ( leaving there the 
archive_status
~directory ) and remove the postmaster.pid file ( this is necessary if you are 
running
~the spare postgres on the same hw ).
~launch the postmaster, the restore will continue till the "alive" file 
present in the
~/mnt/server/partialdir directory is not updated for 60 seconds ( you can 
modify this
~values inside the restore.sh script ).
Be sure that restore.sh and all directories involved are accessible
Let me know.
This is a first step, of course, as Eric Kerin did, is better port these script
in C and make it more robust.
Postgres can help this process, as suggested by Tom creating a pg_current_wal()
or even better having two new GUC parameters: archive_current_wal_command and
archive_current_wal_delay.
I problem I discover during the tests is that if you shut down the spare node
and the restore_command is still waiting for a file then the postmaster will never
exit :-(


Regards
Gaetano Mendola




-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBHe+S7UpzwH2SGd4RAss1AJ0ZfnhSZWWvbRjvbgAlpLu0dtxKIwCg22B+
6bFDyutTVow6+mcTNfD3Cuc=
=Ot+H
-END PGP SIGNATURE-
#!/bin/bash

PID=$1
PARTIAL=$2
PGXLOG=$3

function copy_last_wal
{
   FILE=$( ls -t1p $PGXLOG | grep -v / | head -1 )

   echo "Last Wal> " $FILE

   cp ${PGXLOG}/${FILE} ${PARTIAL}/${FILE}.tmp
   mv ${PARTIAL}/${FILE}.tmp ${PARTIAL}/${FILE}.partial
   find ${PARTIAL} -name *.partial | grep -v ${FILE} | xargs -i rm -fr {}
}


while [ 1 ]
do 
   ps --pid $PID > /dev/null 2>&1
   ALIVE=$?
   
   if [ "${ALIVE}" == "1" ]
   then
   #The process is dead
   echo "Process dead"
   copy_last_wal 
   exit 1
   fi

   #The process still exist
   touch ${PARTIAL}/alive
   copy_last_wal 

   sleep 10
done
#!/bin/bash


SOURCE=$1
TARGET=$2
PARTIAL=$3

SIZE_EXPECTED=16777216  #bytes 16 MB
DIED_TIME=60#seconds

function test_existence
{
if [ -f ${SOURCE}   ]
then
   COUNTER=0

   #I have to check if the file is begin copied
   #I assume that it will reach the right
   #size in a few seconds

   while [ $(stat -c '%s' ${SOURCE} ) -lt $SIZE_EXPECTED ]
   do
  sleep 1
  let COUNTER+=1
  if [ 20 -lt $COUNTER ]
  then
 exit 1# BAILING OUT
  fi
   done

   cp $SOURCE $TARGET
   exit 0
fi
echo ${SOURCE}"> not found"

#if is looking for a history file and not exist 
#I have suddenly exit
echo $SOURCE | grep history > /dev/null 2>&1 && exit 1
}


while [ 1 ]
do 

   test_existence

   #CHECK IF THE MASTER IS ALIVE
   DELTA_TIME=$(( $( date +'%s' ) - $( stat -c '%Z' ${PARTIAL}/alive ) ))
   if [ $DIED_TIME -lt $DELTA_TIME ]
   then
   echo "Master is dead..."
   # Master is dead
   CURRENT_WAL=$( basename $SOURCE )
   echo "Partial: " ${PARTIAL}
   echo "Current wal: " ${CURRENT_WAL}
   echo "Target: " ${TARGET}
   cp ${PARTIAL}/${CURRENT_WAL}.partial ${TARGET}  > /dev/null 2>&1 && exit 0
   exit 1
   fi

   sleep 1

done

---(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] NOT LOGGED options (was Point in Time Recovery )

[[EMAIL PROTECTED]]

> Tom Lane wrote
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> > It would be my intention (in 8.1) to make those available via
> switches e.g.
> > NOT LOGGED options on CREATE INDEX and COPY, to allow users to take
> > advantage of the no logging optimization without turning off PITR system
> > wide. (Just as this is possible in Oracle and Teradata).
>
> Isn't this in direct conflict with your opinion above?  And I cannot say
> that I think this one is a good idea.  We do not have support for
> selective catalog xlogging; if you do something like this then you
> *will* have a broken database after recovery, because it will contain
> those indexes but with invalid contents.

No, its not in direct conflict. Turning OFF archive_mode would have a system
wide effect. The options described allow individual applications to make a
choice about whether certain very large operations are recoverable, or not.
I don't ever personally want to turn off system wide PITR, but there will be
times when I choose to avoid overhead on individual ops when the situation
dictates. This goes with your oft-mentioned dislike of systems that think
they know better than you do...

The first two optimizations have been included in 8.0 when archive_mode is
off. If there is a problem, then it will effect crash recovery of those
systems also. I suggest using exactly this optimisation, though under user
(application) control, rather than sysadmin control.

The challenges you mention have a solution. I wanted to add these to TODO,
not yet to discuss detailed implementation.

> > I would also aim to make the first Insert Select into an empty table not
> > logged (optionally). This is an important optimization for
> Oracle, teradata
> > and DB2 (which uses NOT LOGGED INITIALLY).
>
> This is even worse: not only do you have a broken database, but you have
> no way to recover.  (At least with an unlogged index you could fix it by
> REINDEX.)  If you don't care about longevity of the table, then make it
> a temp table.
>

It is frequently possible to use that route, though the option remains in
frequent use in other situations.

> The fact that Oracle does it does not automatically make it a good idea.
>

Amen to that. You will note that unless compatability has been a
requirement, there have been times I have not followed the Oracle path, e.g.
PITR design.

I admit it must seem strange that I tried so hard to put PITR in place, only
to suggest removing it, optionally...

Overall, the options I describe here have been in production use in major
enterprise Data Warehouse systems for almost 15 years now. Oracle and DB2
copied the original Teradata implementation; slowly because, they too,
didn't quickly or easily accept the wisdom. There is abosultely no doubt of
the true value of these optimisations - the TPC-H tests for all vendors make
use of those (hidden in the details of which load utility options are used,
or simply the default behaviour).

Logging only has value when the mean time to recover is low enough to make
recovery worthwhile. This can catch you in a bind because you have to decide
whether to reduce MTTR at the expense of 100% data recovery. For some big
systems, recovery is only an option if you exclude the biggest table(s). In
a Data Warehouse, where data is loaded in large volumes, it may only be
feasible to load it when you have this optimisation. In a recovery
situation, re-loading the largest fact tables from their original source
data files is more likely to be the best option, or in some cases, skipped
entirely in favour of loading new data.

I don't claim that everybody would want this, only that it is an extremely
beneficial optimisation for many very large databases - which is much of my
focus.

You've pointed out that I'm new "round here", which is certainly true - but
I have been many places... There are and will be many differences in
thinking that emerge from this; I regard all of this as synergy, not
argument.

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] will PITR in 8.0 be usable for "hot spare"/"log

[Gaetano Mendola]

Eric Kerin wrote:
On Wed, 2004-08-11 at 16:43, Tom Lane wrote:
Gaetano Mendola <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
It should work; dunno if anyone has tried it yet.

I was thinking about it but I soon realized that actually is
impossible to do, postgres replay the log only if during the
start the file recover.conf is present in $DATA directory :-(

Somebody should hack this together and try it during beta.  I don't
have time myself.
			regards, tom lane

I've wrote up a very quick, and insanely dirty hack to do log shipping. 
Actually, it's so poorly written I kinda feel ashamed to post the code.

But so far the process looks very promising, with a few caveats. 

The issues I've seen are:
1. Knowing when the master has finished the file transfer transfer to
the backup.
2. Handling the meta-files, (.history, .backup) (eg: not sleeping if
they don't exist)
3. Keeping the backup from coming online before the replay has fully
finished in the event of a failure to copy a file, or other strange
errors (out of memory, etc).
I did the same work and I had the same problems solved in the exact way
you did, however my version was shell based ( wasted time )   :-(
I guess is better mantain the C version, I will take a look at it and
I will modify it if something doesn't work.
Good work.
Regards
Gaetano Mendola


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] Why is WIN 1250 client only?

[Peter Eisentraut]

Peter Eisentraut wrote:
> Why is the encoding WIN 1250 only for the client side?  It seems that
> with the new Windows port, folks will be interested in using it on
> the server side.

Last call or I will move WIN 1250 to the server side encoding list. The 
bugs are already coming in.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Calling PL functions with named parameters

[Gaetano Mendola]

Oliver Jowett wrote:
Tom Lane wrote:
On a related note, it would also be nice to have default
parameters and some way to say to use them.

That is fundamentally not ever going to happen, because it blows
overloaded-function resolution out of the water: there is no way to
choose whether "foo(42, 2.5)" matches foo(int, float) or
foo(int, float, something-with-a-default).  Let's try to limit our
attention to something that might actually work.

C++ manages to solve this problem, although I can't remember the exact 
mechanics (and C++ is usually not a good example to follow anyway ;)
Your're wrong:
try to compile this:
void foo( int a, float b ) { }
void foo( int a, float b, int c=0 ) { }
int main(char argc, char **argv)
{
 foo( 42, 2.5 );
 return 0;
}
you'll get:
a.cpp:6: error: call of overloaded `foo(int, double)' is ambiguous
a.cpp:1: error: candidates are: void foo(int, float)
a.cpp:2: error: void foo(int, float, int)
usualy C++ is not a good example as SQL is not  :-)

Regards
Gaetano Mendola


---(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] Calling PL functions with named parameters

[Gaetano Mendola]

Oliver Jowett wrote:
Gaetano Mendola wrote:
Oliver Jowett wrote:
David Fetter wrote:
Dennis has pointed out that mixing the call-with-named-parameter
interface with call-by-order-of-parameters one would cause confusion,

Python's equivalent syntax allows you to mix the two forms so long as 
all the by-position parameters come first:

python don't have overloaded functions...

It doesn't change how you'd handle overloaded functions; you still have 
a type for every parameter available.
I think will be a mess that will break the "minor surprise" principle,
even the bad C++ stays away from this field ( se explicit constructors,
and automatic cast limited to only one level ).
I know I know the Koenig Lookup is there as a Damocle's sword...

Regards
Gaetano Mendola



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] PITR on Windows?

[Gaetano Mendola]

Bruce Momjian wrote:
> Andrew Dunstan wrote:
>
>>
>>[EMAIL PROTECTED] wrote:
>>
>>
>>>I notice that PITR doesn't function correctly on Windows. Has that been
>>>reported elsewhere?
>>>
>>>The archive_command parameter %p resolves to a full path containing slashes
>>>rather than backslashes. This is not a Windows file, so any attempt to copy
>>>it fails. There isn't any way to avoid that.
>>>
>>>I'm surely not the first to report that? Am I?
>>>
>>>
>>>
>>>
>>
>>This was fixed several days ago, after lengthy discussion on the -win32
>>list, and we have seen reported success, IIRC.
>
>
> Yes, the fix will be in beta2 or the next pginstaller release.
Do you mean that the next pginstaller release will carry a 8.0beta1
with different behaviour that actual 8.0beta1 ?
I hope I mistake you.
Regards
Gaetano Mendola


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings