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

[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?)

> 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 ...

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 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've got a solution for 1.  I use a control file that contains the name
of the last file that was successfully copied over.  After the program
copies the file, it updates the control file with the new file's name.
The restore program looks in that file for what is the last safe file to
replay, and sleeps if the one it's been told to look for isn't safe yet.

Two is pretty easy, just special case out files ending in .history or
.backup.

Three is a problem I see happening once in a while, and will cause you
to have to recreate the backup database from a backup of master, it
could spell trouble, or at the very least a mad DBA.  A possible fix is
to check the error code returned from restore_command to see if it's
ENOENT before bringing the db online, instead of bringing the database
online at any error.  This might be better as an option though.



Still lots of bugs in my implementation, and my next step is to re-write
it from scratch.  I'm going to keep playing with this and see if I can
get something a little more solid working.

Here's a url to the code as it is right now, works on linux, no promises
with anything else.  http://www.bootseg.com/log_ship.c

For the archive command use:
/path_to_binary/log_ship -a /archive_directory/ %p %f

For the restore_command use:
/path_to_binary/log_ship -r /archive_directory/ %f %p

Any comments are Very appreciated

Thanks, 
Eric Kerin


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

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

[Tom Lane]

"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.

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

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

[Andrew Dunstan]

Tom Lane said:
> Andrew Dunstan <[EMAIL PROTECTED]> writes:
>> Should it be backported for the upcoming stable release(s)? Bruce and
>> I  were discussing this earlier.
>
> Probably a good idea, since we do support psql on Windows even in the
> older releases.
>
> My personal opinion is to back-port only as far as 7.4, but if you feel
> like doing and testing it for 7.3 then I'll apply the patch.  I need it
> tomorrow (Sat) though, as I'd like to wrap these releases Sunday.
>

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.

cheers

andrew



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

Re: [HACKERS] PITR on Windows?

[Bruce Momjian]

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.

-- 
  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 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

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> Would it be any better to allow
> SELECT blah(1,DEFAULT); 

Not a lot.  If there is more than one 2-parameter blah(), how do you
pick?  The DEFAULT gives you no clue at all about the type of the
second parameter...

I think if we wanted to do something like this, the right way would be
that "create function foo(f1 text, f2 int default 42)" implicitly
creates a second function "foo(f1 text)", and we make no change to the
matching rules.  But managing this seems mighty messy --- for instance,
we don't presently have any concept of hidden or second-class-citizen
entries in pg_proc, but we'd have to create one to keep the implicitly
created functions out of your face in pg_dump, psql \df, etc.  And
again, it's not really giving you anything you can't have today.

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

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Should it be backported for the upcoming stable release(s)? Bruce and I 
> were discussing this earlier.

Probably a good idea, since we do support psql on Windows even in the
older releases.

My personal opinion is to back-port only as far as 7.4, but if you feel
like doing and testing it for 7.3 then I'll apply the patch.  I need it
tomorrow (Sat) though, as I'd like to wrap these releases Sunday.

regards, tom lane

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

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

Re: [HACKERS] Development Schedule Page

[Tom Lane]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> I see this: there is no PUBLISHED schedule, there probably is no AGREED
> schedule, but everybody's working to some reasonable working assumptions
> which mean that there is in fact an IMPLICIT schedule. (Whether or not that
> changes over time)

Well, if you want a page that says "Release: maybe October-ish???" we
could do that, but I'm really dubious about the value.  ISTM that if you
follow -hackers you will have a feeling for how beta is going, and if
you don't then you won't.  No summary webpage is going to give you
insight that the people doing the work don't have themselves.

> We can put things to the nearest month, if thats all we know/decide we can
> hold ourselves to

Nearest month is far more specific than I care to be at the moment.
I would give good odds that 8.0 will be released before the end of the
year, but I'd not write down a month yet.  In another month, if beta
seems to be going well, I would be ready to get more specific.

regards, tom lane

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

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

Re: [HACKERS] Development Schedule Page

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> Not sure how much more useful this is, but generally the first RC goes out 
> once all the open items are dealt with.  Also doc freeze is generally 
> expected at RC1 time (though errors in the docs are still acceptable fixes 
> for subsequent RC's)

Actually I'm not sure that we generally have a docs freeze at all.
The reference pages may freeze a bit before release, because there
is some manual effort needed to produce the man-page formatted files
from them.  But that's an artifact of our current document generation
toolset rather than something that anyone finds desirable.  The SGML
docs can surely be worked on right up till release.

Of course it is better to get major docs changes in sooner rather than
later, if only so other people have a chance to look 'em over.

regards, tom lane

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

Re: [HACKERS] [ADMIN] Pseudo-Off-topic-survey: Opinions about future of Postgresql(MySQL)?

[Bruce Momjian]

Enrique Arizón wrote:
>  Now that CA has open sourced Ingres what future do
> you guess to Postgresql and MySQL?
> 
>  Don't missunderstand me, I have been using Postgresql
> for more than 3 years and developing apps against it
> and all I got is possitive impressions, but comparing
> the upcoming 8.0 (7.5) release with Ingres, it looks
> that Ingres is much more advanced (clustering,
> load-balancing, XML, ...) and the main advantage
> Postgresql had in its open source nature looks to be
> vanished. More one, CA looks really serious about
> Ingres that now is a core tool in more of 100
> derivates CA products, and it's said they had doubled
> the number of Ingres developers. Also the new version
> provides a great compatibility with Oracle and
> "easify" Oracle to Ingres port. Is there any OBJETIVE
> reason not to change to Ingres?

Good question.  We heard the same things when SapDB came out. SapDB was
moved into the MySQL AB company and renamed MaxDB.  I don't think there
is any more community development of it, just like MySQL.

Basically, what we have is quality code and a vibrant community.  Those
aren't easy to create.  In fact, that's what makes open source powerful.

-- 
  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 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] [PERFORM] Reiser4

[Bruce Momjian]

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) ?

I have been thinking about this.  Informix calls it buffered logging and
it would be a good feature.

Added to TODO:

* Allow buffered WAL writes and fsync

  Instead of guaranteeing recovery of all committed transactions, this
  would provide improved performance by delaying WAL writes and fsync
  so an abrupt operating system restart might lose a few seconds of
  committed transactions but still be consistent.  We could perhaps
  remove the 'fsync' parameter (which results in an an inconsistent
  database) in favor of this capability.

-- 
  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 7: don't forget to increase your free space map settings

Re: [HACKERS] Calling PL functions with named parameters

[Robert Treat]

Would it be any better to allow

SELECT blah(1,DEFAULT); 

?

Robert Treat

On Friday 13 August 2004 18:49, Jim C. Nasby wrote:
> I would personally find this useful, but I would suggest using Oracle's
> syntax of SELECT func(a=>2, b=>'b', ...);
>
> Having said that, having the concept of DEFAULT for parameters wolud be
> even more useful, ie:
>
> CREATE FUNCTION blah (
> a int
> , b int DEFAULT 0
> );
>
> SELECT blah(1,0);
> and
> SELECT blah(1);
> would do the same thing. (Yes, I know there's a work-around, but it's a
> bit of a pain if you've got 10 parameters that could be omitted).
>
> On Fri, Aug 13, 2004 at 02:41:48PM -0700, David Fetter wrote:
> > Kind people,
> >
> > I've brought this up before, and with Dennis Bjőrklund's help, would
> > like to bring it up again.  Here's the idea:
> >
> > 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');
> >
> > and have it Do The Right Thing.
> >
> > 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
> >
> > SELECT foo_func(name AS 'yet another name', 35);
> >
> > would be disallowed.
> >
> > A calling convention that names parameters makes it a lot easier to
> > track just exactly what parameter is set to which value, and lets
> > people not have to memorize what order those named parameters appear
> > in.  On a related note, it would also be nice to have default
> > parameters and some way to say to use them.
> >
> > Well, that's my thoughts so far.  What are yours?
> >
> > Cheers,
> > D
> > --
> > David Fetter [EMAIL PROTECTED] http://fetter.org/
> > phone: +1 510 893 6100   mobile: +1 415 235 3778
> >
> > Remember to vote!
> >
> > ---(end of broadcast)---
> > TIP 8: explain analyze is your friend

-- 
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

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

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

Re: [HACKERS] Development Schedule Page

[Robert Treat]

On Friday 13 August 2004 19:37, Tom Lane wrote:
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> > Please could we have a development schedule page on the web site...?
>
> You haven't been around here long, have you?  There is no schedule.
>
> The page would spend most of its time looking like
>
> Current Development Release: (Coordinator: Bruce Momjian)
>   8.0:Beta1, released 2 August 2004
>   Beta2, when it's ready
>   Doc Freeze, when it's ready
>   Release, when it's ready
>
> which isn't all that useful.

Not sure how much more useful this is, but generally the first RC goes out 
once all the open items are dealt with.  Also doc freeze is generally 
expected at RC1 time (though errors in the docs are still acceptable fixes 
for subsequent RC's)  We do RC's as needed until core feels they will produce 
no more bug reports in a timely fasion, and then release happens.  

-- 
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

---(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] PITR on Windows?

[Andrew Dunstan]

[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.

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

[Andrew Dunstan]

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

The attached patch appears to solve the problem. However, while it 
makes us conform to the first sentence below from the docs, it doesn't 
comply with the second. Not sure what to do about that. Maybe there's 
a better solution?
 

 

Attached patch seems much better, I think.
   

I think it is still not quite there.  Since as you noted the backend
will complain if line endings don't match, if we hit EOF then we have
to cons up a \. line with the correct ending.  (BTW, this is not
actually necessary when talking 3.0 protocol, but it is when talking
to an older server.)
I modified the patch a little more and applied the attached.  It seems
to work for me but could use more testing.
 

WorksForMe, and looks good. You're right, I had forgotten the EOF case.
Should it be backported for the upcoming stable release(s)? Bruce and I 
were discussing this earlier.

Pro: it's an ugly bug and hard to diagnose - things just seem to die for 
no apparent reason.
Con: there's a workaround - just make sure to run dos2unix on your file 
if necessary.

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

[HACKERS] PITR on Windows?

[[EMAIL PROTECTED]]

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?

[There isn't any way of telling, by default, since the log goes nowhere]

Is there a PostgreSQL approved way of saying "for Windows port, use
backslashes in pathnames"?

Best Regards, Simon Riggs


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

Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization

[[EMAIL PROTECTED]]

> Tom Lane
> 
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> > This is not a provably correct state machine
> 
> I think the discussion ends right there.  

Yes...

Negative results are worth documenting too, IMHO.

Best Regards, Simon Riggs

---(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] Development Schedule Page

[[EMAIL PROTECTED]]

> Tom Lane
>
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> > Please could we have a development schedule page on the web site...?
>
> You haven't been around here long, have you?

Gee, Tom, you noticed? What gave it away? :)

(I wasn't in a legal position to contribute before late 2003 - binding IP
clauses and all that).

> There is no schedule.

Hmm. I did think that at first.

But I don't go with the mystical stuff. (There IS a spoon, Matrix-fans,
IMHO.)

Now, I think the answer is close to "Tom and/or Bruce know, but maybe they
don't agree yet"

My observed group behaviour for what days is eventually agreed is something
like:
(Tom+Bruce+Marc)/2 in julian days

I see this: there is no PUBLISHED schedule, there probably is no AGREED
schedule, but everybody's working to some reasonable working assumptions
which mean that there is in fact an IMPLICIT schedule. (Whether or not that
changes over time)

I argued as strongly as anyone to NOT follow the agreed plan (just
recently). But I'm in favour of a published plan, so we all know. Just like
my recipe book says "90 mins for a cake", but we keep cooking it till it
looks good and don't try to flame Mrs.Beeton because it took 95 mins.

My interest is in allowing others to contribute. I have many other ideas in
this vein.

We can put things to the nearest month, if thats all we know/decide we can
hold ourselves to

Best Regards, Simon Riggs


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

Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization

[Tom Lane]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> This is not a provably correct state machine

I think the discussion ends right there.  You are assuming that the
commit is guaranteed to finish in X amount of time, when it is not
possible to make any such guarantee.  We are not putting in an
unreliable commit mechanism in order to save a small amount of lock
contention.  (As I tried to point out already, but it doesn't seem
to have sunk in: this newly-added lock is not likely to be that much
more contention added to the commit path, seeing that the path of
control it protects already involves taking at least two exclusive
LWLocks.  Those locks will likely each cause as much or more SMP
cache thrashing as this one.)

What we could use is a better way to build LWLocks in general.  I do not
know how to do that, though, in the face of SMP machines that seem to
fundamentally not have any cheap locking mechanisms...

regards, tom lane

---(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:
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,
and I think it would be OK to disallow this type mixing, so
SELECT foo_func(name AS 'yet another name', 35);
would be disallowed.

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

 >>> def f(a,b,c,d):
...   print a,b,c,d
...
 >>> f(1,2,3,4)
1 2 3 4
 >>> f(1,2,c=3,d=4)
1 2 3 4
 >>> f(1,2,d=4,c=3)
1 2 3 4
 >>> f(1,d=4,2,c=3)
SyntaxError: non-keyword arg after keyword arg
python don't have overloaded function...

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] Development Schedule Page

[Tom Lane]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> Please could we have a development schedule page on the web site...?

You haven't been around here long, have you?  There is no schedule.

The page would spend most of its time looking like

Current Development Release: (Coordinator: Bruce Momjian)
8.0:Beta1, released 2 August 2004
Beta2, when it's ready
Doc Freeze, when it's ready
Release, when it's ready

which isn't all that useful.

As for the historical aspect, we already have the past release notes
collected handily in the documentation, so I'm not sure what else we
need to do there.

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

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:

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.

-O
---(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

[Oliver Jowett]

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 ;)

How about just disallowing function signatures that cause ambiguity? 
i.e. make f(t1,t2,default t3,default t4,..) lay claim to f(t1,t2), 
f(t1,t2,t3), f(t1,t2,t3,t4) etc, and creation fails if any of those 
signatures are already claimed by another function.

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

Re: [HACKERS] hot spare / log shipping work on

[Gaetano Mendola]

Tom Lane wrote:
Gaetano Mendola <[EMAIL PROTECTED]> writes:
I'm facing however to the following problems:

1) Discovery the actual WAL file
I'm supposing is the last modified file inside the
pg_xlog directory. If this is not the good method
may I know how I can know it ?

While that theoretically will work, it leaves a bad taste in my mouth.
I have been thinking of proposing that we add a "pg_current_wal_file()"
function, or some such name, to return the name of the active WAL file.
Totally agree, this could help during the process.
Actually I detect the current wal file in this way:
ls -t1p $PGXLOGDIR | grep -v / | head 1
that is an almost "empirical" process, in the first phase
I can live with it but for sure a more robust way is a must.
Regards
Gaetano Mendola



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

Re: [HACKERS] Calling PL functions with named parameters

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> IOW, their function resolution code treats a(int, int default 0) as
> being equivalent to a(int) and a(int, int).

So you are willing to prohibit a(int) from existing in parallel with
a(int, int-with-a-default) ?

I'll be interested to see the unique-index scheme for pg_proc to enforce
that ;-)

However this does point up the fact that there already *is* a way to
accomplish the task, which is just to create some helper function(s) to
supply the default(s).  Perhaps we can leave it at that for the time
being, and concentrate on adding real new functionality.

regards, tom lane

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

[HACKERS] Development Schedule Page

[[EMAIL PROTECTED]]

Please could we have a development schedule page on the web site...?

The information it should contain would be like this (and others)

Current Development Release: (Coordinator: Bruce Momjian)
8.0:Beta1, released 2 August 2004
Beta2, deadline 2 September 2004
Doc Freeze, deadline 12 September 2004
Release, planned 2 October 2004

Stable Releases: (Coordinator: Bruce Momjian)
7.4 Stable: 7.4.3, released XYZ
Security advisory(s) exist for 7.4.1
7.3 Stable: 7.3.6, released XYZ
Security advisory(s) exist for 7.3.5 and below

Previous Releases: (No Coordinator)
7.2 Stable: 7.2.4, released XYZ
Advice: Upgrade now to 7.4 stable
7.1 Stable: 7.1.3, released XYZ
Advice: Upgrade now to 7.4 stable

Next Release: (Coordinator: Bruce Momjian)
8.1 Beta Freeze, deadline 1 June 2005


Once we have the page, we can start to discuss the details on it.

I remain mostly in the dark about how many beta phases there will be what
their deadlines are etc.

If we know this, its easy to write down, surely?

Best Regards, Simon Riggs


---(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 Windows]

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
>> The attached patch appears to solve the problem. However, while it 
>> makes us conform to the first sentence below from the docs, it doesn't 
>> comply with the second. Not sure what to do about that. Maybe there's 
>> a better solution?

> Attached patch seems much better, I think.

I think it is still not quite there.  Since as you noted the backend
will complain if line endings don't match, if we hit EOF then we have
to cons up a \. line with the correct ending.  (BTW, this is not
actually necessary when talking 3.0 protocol, but it is when talking
to an older server.)

I modified the patch a little more and applied the attached.  It seems
to work for me but could use more testing.

regards, tom lane

*** src/bin/psql/copy.c.origFri Aug 13 10:47:23 2004
--- src/bin/psql/copy.c Fri Aug 13 18:51:25 2004
***
*** 663,668 
--- 663,669 
boolcopydone = false;
boolfirstload;
boollinedone;
+   boolsaw_cr = false;
charcopybuf[COPYBUFSIZ];
char   *s;
int bufleft;
***
*** 695,724 
  
while (!linedone)
{   /* for each bufferload 
in line ... */
s = copybuf;
for (bufleft = COPYBUFSIZ - 1; bufleft > 0; bufleft--)
{
c = getc(copystream);
!   if (c == '\n' || c == EOF)
{
linedone = true;
break;
}
*s++ = c;
}
*s = '\0';
if (c == EOF && s == copybuf && firstload)
{
!   PQputline(conn, "\\.");
copydone = true;
if (pset.cur_cmd_interactive)
puts("\\.");
break;
}
PQputline(conn, copybuf);
if (firstload)
{
!   if (!strcmp(copybuf, "\\."))
{
copydone = true;
break;
--- 696,744 
  
while (!linedone)
{   /* for each bufferload 
in line ... */
+   /* Fetch string until \n, EOF, or buffer full */
s = copybuf;
for (bufleft = COPYBUFSIZ - 1; bufleft > 0; bufleft--)
{
c = getc(copystream);
!   if (c == EOF)
{
linedone = true;
break;
}
*s++ = c;
+   if (c == '\n')
+   {
+   linedone = true;
+   break;
+   }
+   if (c == '\r')
+   saw_cr = true;
}
*s = '\0';
+   /* EOF with empty line-so-far? */
if (c == EOF && s == copybuf && firstload)
{
!   /*
!* We are guessing a little bit as to the right 
line-ending
!* here...
!*/
!   if (saw_cr)
!   PQputline(conn, "\\.\r\n");
!   else
!   PQputline(conn, "\\.\n");
copydone = true;
if (pset.cur_cmd_interactive)
puts("\\.");
break;
}
+   /* No, so pass the data to the backend */
PQputline(conn, copybuf);
+   /* Check for line consisting only of \. */
if (firstload)
{
!   if (strcmp(copybuf, "\\.\n") == 0 ||
!   strcmp(copybuf, "\\.\r\n") == 0)
{
copydone = true;
break;
*

Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization

[[EMAIL PROTECTED]]

On Thu, Aug 12, 2004 at 01:13:46PM -0400, Tom Lane wrote:
> Kenneth Marshall <[EMAIL PROTECTED]> writes:
> > On Thu, Aug 12, 2004 at 09:58:56AM -0400, Tom Lane wrote:
> >> How would a read-only action work to block out the checkpoint?
>
> > The latch+version number is use by the checkpoint process. The
> > other processes can do a read of the latch to determine if it has
> > been set. This does not cause a cache invalidation hit. If the
> > latch is set, the competing processes read until it has been
> > cleared and the version updated. This makes the general case of
> > no checkpoint not incur a write and the consequent cache-line
> > invalidation and reload by all processors on an SMP system.
>
> Except that reading the latch and finding it clear offers no guarantee
> that a checkpoint isn't about to start.  The problem is that we are
> performing two separate actions (write a COMMIT xlog record and update
> transaction status in clog) and we have to prevent a checkpoint from
> starting in between those actions.  I don't see that there's any way to
> do that with a read-only latch.
>

...just caught up on this.

ISTM that more heavily loading the checkpoint process IS possible if the
checkpoint uses a two-phase lock. That would replace 1 write lock with 2
lock reads...which is likely to be beneficial for SMP, given I have faith
that the other two problems you mention will succumb to some solution in the
mid-term. The first lock is an "intent lock" followed by a second,
heavyweight lock just as you now have it.

Comitter:
1. prior to COMMIT: reads for an intent lock, if found then it attempts to
take heavyweight lock...if that is not possible, then the commit waits until
after the checkpoint, just as you currently suggest
2. prior to update clog: reads for an intent lock, if found then takes
heavyweight lock...if that is not possible, then report a server error

Checkpointer: (straight to step 4 for a shutdown checkpoint)
1. writes an intent lock (it always can)
2. wait for the group commit timeout
3. wait for 0.5 second more
4. begins to wait on an exclusive heavyweight lock, before starting
checkpoint proper

This is not a provably correct state machine, but the error message should
not occur under current "normal" situations. (It is possible that an intent
lock could be written by Checkpointer (step 1), after a Committer reads for
it (step 1), then a very long delay occurs before Committer's step 2), such
that Checkpointer step 4 begins before Committer step 2.) It is very likely
that this would be noticed by Comitter step 2 and reported upon, in the
unlikely event that it occurs.

Is a longer term solution for pg to use a background log writer? That would
make group commit much easier to perform automatically without the
false-delay model currently available.

Best Regards, Simon Riggs


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

   http://archives.postgresql.org

Re: [HACKERS] Calling PL functions with named parameters

[Jim C. Nasby]

On Fri, Aug 13, 2004 at 06:22:25PM -0400, 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.
 
Actually, it is possible because Oracle does it. Presumably they treat a
function with defaults as being the equivalent number of overloaded
functions when functions are created, to ensure it can always resolve
what function to call.

IOW, their function resolution code treats a(int, int default 0) as
being equivalent to a(int) and a(int, int).
-- 
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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Postgres development model

[Bruce Momjian]

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.

-- 
  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 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

[Jim C. Nasby]

I would personally find this useful, but I would suggest using Oracle's
syntax of SELECT func(a=>2, b=>'b', ...);

Having said that, having the concept of DEFAULT for parameters wolud be
even more useful, ie:

CREATE FUNCTION blah (
a int
, b int DEFAULT 0
);

SELECT blah(1,0);
and
SELECT blah(1);
would do the same thing. (Yes, I know there's a work-around, but it's a
bit of a pain if you've got 10 parameters that could be omitted).

On Fri, Aug 13, 2004 at 02:41:48PM -0700, David Fetter wrote:
> Kind people,
> 
> I've brought this up before, and with Dennis Bjőrklund's help, would
> like to bring it up again.  Here's the idea:
> 
> 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');
> 
> and have it Do The Right Thing.
> 
> 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
> 
> SELECT foo_func(name AS 'yet another name', 35);
> 
> would be disallowed.
> 
> A calling convention that names parameters makes it a lot easier to
> track just exactly what parameter is set to which value, and lets
> people not have to memorize what order those named parameters appear
> in.  On a related note, it would also be nice to have default
> parameters and some way to say to use them.
> 
> Well, that's my thoughts so far.  What are yours?
> 
> Cheers,
> D
> -- 
> David Fetter [EMAIL PROTECTED] http://fetter.org/
> phone: +1 510 893 6100   mobile: +1 415 235 3778
> 
> Remember to vote!
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 

-- 
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 5: Have you checked our extensive FAQ?

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

Re: [HACKERS] Calling PL functions with named parameters

[Oliver Jowett]

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,
and I think it would be OK to disallow this type mixing, so
SELECT foo_func(name AS 'yet another name', 35);
would be disallowed.
Python's equivalent syntax allows you to mix the two forms so long as 
all the by-position parameters come first:

>>> def f(a,b,c,d):
...   print a,b,c,d
...
>>> f(1,2,3,4)
1 2 3 4
>>> f(1,2,c=3,d=4)
1 2 3 4
>>> f(1,2,d=4,c=3)
1 2 3 4
>>> f(1,d=4,2,c=3)
SyntaxError: non-keyword arg after keyword arg
-O
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] Calling PL functions with named parameters

[Tom Lane]

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?

A bigger issue is how do you see this interacting with resolution of
ambiguous/overloaded function names.

> 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.

regards, tom lane

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

Re: [HACKERS] hot spare / log shipping work on

[Tom Lane]

Gaetano Mendola <[EMAIL PROTECTED]> writes:
> I'm facing however to the following problems:

> 1) Discovery the actual WAL file
>   I'm supposing is the last modified file inside the
>  pg_xlog directory. If this is not the good method
>  may I know how I can know it ?

While that theoretically will work, it leaves a bad taste in my mouth.
I have been thinking of proposing that we add a "pg_current_wal_file()"
function, or some such name, to return the name of the active WAL file.

> 2) During the recovery phase postmaster ask me for file that will never be
> there like:  0001.history.
>   Actualy if the file does not exist and contain the string history
>  instead to wait for him I exit with 1 exit code

I think you can reasonably assume that .history files won't show up
on-the-fly, since they are only created during a PITR recovery operation
on the master.  It's not clear how this whole thing should track such an
operation on the master anyway :-(

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

[HACKERS] Calling PL functions with named parameters

[David Fetter]

Kind people,

I've brought this up before, and with Dennis Bjőrklund's help, would
like to bring it up again.  Here's the idea:

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');

and have it Do The Right Thing.

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

SELECT foo_func(name AS 'yet another name', 35);

would be disallowed.

A calling convention that names parameters makes it a lot easier to
track just exactly what parameter is set to which value, and lets
people not have to memorize what order those named parameters appear
in.  On a related note, it would also be nice to have default
parameters and some way to say to use them.

Well, that's my thoughts so far.  What are yours?

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

Re: [HACKERS] PostgreSQL 8.0.0beta1 and diet libc

[Tom Lane]

Andreas Krennmair <[EMAIL PROTECTED]> writes:
> creating template1 database in data/base/1 ... FATAL:  XX000: failed to
> initialize lc_monetary to "C"
> LOCATION:  InitializeGUCOptions, guc.c:2337
> child process exited with exit code 1

> As an important note you have to know that diet libc's support for all
> the NLS and i18n/l10n-related stuff is largely incomplete and works so
> far that common software compiles and runs 

[ shrug ]  If it returns an error when someone tries to set the locale
to "C", that is *not* a good-enough implementation of the ANSI/ISO C
standard.  Fix the library.

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] Postgres development model

[Tom Lane]

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.

regards, tom lane

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

Re: [HACKERS] BEGIN EXCEPTION END - small bug?

[Tom Lane]

Daniel Schuchardt <[EMAIL PROTECTED]> writes:
> i tried a bit with errorhandling and found the following :
> (i want to ignore the dublicate key exception)

> ERROR:  SPI_prepare failed for "ROLLBACK": SPI_ERROR_TRANSACTION

You can't use ROLLBACK inside a plpgsql function.  I agree that this
error message leaves something to be desired, though.
[ ... sounds of hacking ... ]
Now it says

regression=# select test();
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function "test" line 5 at SQL statement
regression=#

That might still leave you a bit confused, since you *were* using
an EXCEPTION clause, but offhand it seems about the best we can do.

regards, tom lane

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

Re: [HACKERS] Referencing OLD/NEW Rows on Trigger Definition

[Tom Lane]

[EMAIL PROTECTED] (Henry) writes:
> As part of the project, we need to implement a new feature for the
> trigger creation in
> PostgreSQL, which defines aliases for the "old" and "new" data by
> adding
> the REFERENCING clause to the CREATE TRIGGER statement.  Based on my
> research, the following items need to be modified to support the
> REFERENCING option.

> - modify backend\parser\gram.y and keyword.c
> - modify CreateTrigStmt node struct
> - modify pg_trigger struct in pg_trigger.h, Trigger and TriggerDesc
> structs in rel.h, TriggerData struct in trigger.h
> - modify CreateTrigger(), RelationBuildTriggers(), InsertTrigger(),
> CopyTriggerDesc(), FreeTriggerDesc(), ExecBSInsertTriggers(),
> ExecASInsertTriggers(), ExecBRInsertTriggers(),
> ExecARInsertTriggers(), ExecBSDeleteTriggers(),
> ExecASDeleteTriggers(), ExecBRDeleteTriggers(),
> ExecARDeleteTriggers(), ExecBSUpdateTriggers(),
> ExecASUpdateTriggers(), ExecBRUpdateTriggers(), ExecBRUpdateTriggers()
> - to handle the new option, create new fuctions
> ExecBSOInsertTriggers(), ExecASOInsertTriggers(),
> ExecBROInsertTriggers(), ExecAROInsertTriggers(),
> ExecBSNInsertTriggers(), ExecASNInsertTriggers(),
> ExecBRNInsertTriggers(), ExecARNInsertTriggers(),
> ExecBSODeleteTriggers(), ExecASODeleteTriggers(),
> ExecBRODeleteTriggers(), ExecARODeleteTriggers(),
> ExecBSNDeleteTriggers(), ExecASNDeleteTriggers(),
> ExecBRNDeleteTriggers(), ExecARNDeleteTriggers(),
> ExecBSOUpdateTriggers(), ExecASOUpdateTriggers(),
> ExecBROUpdateTriggers(), ExecBROUpdateTriggers(),
> ExecBSNUpdateTriggers(), ExecASNUpdateTriggers(),
> ExecBRNUpdateTriggers(), ExecBRNUpdateTriggers()
> - add supports to ExecutePlan(), ExecInsert(), ExecDelete(),
> ExecUpdate() in \backend\executor\execMain.c
> - add supports to backend\node\equalfuncs.c and copyfuncs.c

Seems to me you are hacking on way more than you want to.  Take another
look at your design and see if there isn't a smaller design struggling
to get out.  In particular, why are you touching any of the executor
(which hardly deals in column names at all) rather than implementing the
aliasing in plpgsql?

regards, tom lane

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

Re: [HACKERS] Asserting existing key to be primary

[Tom Lane]

Chris Browne <[EMAIL PROTECTED]> writes:
> I have a table that has a candidate primary key (e.g. - unique, not
> null) that I wish to actually assert _is_ a primary key.

> Is there a way to do that without too much (mwahahhaha! fiddling with
> pg_class!!!) trickery?

Why don't you just ALTER TABLE ADD PRIMARY KEY and then drop the
existing unique constraint?

You could probably fool with pg_constraint and pg_index to manually
transform the unique constraint into a pkey constraint, but the odds
of messing it up seem to outweigh the possible time savings.

regards, tom lane

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

Re: [HACKERS] hot spare / log shipping work on

[Gaetano Mendola]

Manfred Spraul wrote:
Gaetano Mendola wrote:
a1) If exist check that is a 16MB file ( the request can
~arrive during the copy ),

I think this will fail under windows: "copy" first sets the file size 
and then transfers the data. I wouldn't rule out that some Unices use 
the same implementation.
I'm doing this work as "proof of concept", after made it working on
my platform we can even write them in C.

~a2) If the file not exist this mean that is not yet 
recycled and
~is a partial file present on the partial directory,
~check if the "alive" file is older then 2 minutes.
~   a21) If the file is older than 2 minutes I assume 
that
~the master is dead:

I'd concentrate on cold failover: the user (or the OS) must call a 
script to cause a fail-over. The tricky thing are the various partial 
connection losses between master and spare: perhaps the alive file is 
not updated anymore due to a net split, but the master is still alive. 
Unless you are really careful both master and spare could run.
I agree but as I said I'm doing it as "proof of concept", we can even change
later the way to discovery that master is dead.
I'm facing however to the following problems:
1) Discovery the actual WAL file
I'm supposing is the last modified file inside the
pg_xlog directory. If this is not the good method
may I know how I can know it ?
2) During the recovery phase postmaster ask me for file that will never be
   there like:  0001.history.
Actualy if the file does not exist and contain the string history
instead to wait for him I exit with 1 exit code
Is this right for you ?

Regards
Gaetano Mendola




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

Re: [HACKERS] to_char() and negative intervals

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Someone on IRC just reported this to_char() failure with negative
> intervals:

I think Karel wanted to remove to_char(interval) altogether.

regards, tom lane

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

Re: [HACKERS] pg_dump i18n - sk

[Peter Eisentraut]

Am Freitag, 13. August 2004 06:39 schrieb Zoltan Bartko:
> here's another bunch of message strings translated into sk_SK.

Installed.

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

---(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] i18n - sk

[Peter Eisentraut]

Am Freitag, 13. August 2004 14:31 schrieb Zoltan Bartko:
> attached you may find another bunch of translated strings.

Installed.

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

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

Re: [HACKERS] i18n - sk

[Peter Eisentraut]

Am Freitag, 13. August 2004 15:18 schrieb Zoltan Bartko:
> here goes the last missing bunch of strings translated to sk.

Installed.

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

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

Re: [HACKERS] hot spare / log shipping work on

[Manfred Spraul]

Gaetano Mendola wrote:
a1) If exist check that is a 16MB file ( the request can
~arrive during the copy ),
I think this will fail under windows: "copy" first sets the file size 
and then transfers the data. I wouldn't rule out that some Unices use 
the same implementation.

~a2) If the file not exist this mean that is not yet 
recycled and
~is a partial file present on the partial directory,
~check if the "alive" file is older then 2 minutes.
~   a21) If the file is older than 2 minutes I assume 
that
~the master is dead:
I'd concentrate on cold failover: the user (or the OS) must call a 
script to cause a fail-over. The tricky thing are the various partial 
connection losses between master and spare: perhaps the alive file is 
not updated anymore due to a net split, but the master is still alive. 
Unless you are really careful both master and spare could run.

I think SAP DB / MaxDB supports failover - perhaps it would be 
interesting to check their failover scripts.

--
   Manfred
---(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] Asserting existing key to be primary

[Merlin Moncure]

> Is there a way to do that without too much (mwahahhaha! fiddling with
> pg_class!!!) trickery?

esp=# alter table test add primary key(id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"test_pkey" for table "test"
ALTER TABLE

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

Re: Postgres development model (was Re: [HACKERS] CVS comment)

[Andrew Dunstan]

Peter Eisentraut wrote:
There is also a big difference between supporting some proprietary 
software and making proprietary software a de facto requirement for 
participating in the development effort.

 

Just to complete the information on this, I have it on good authority 
(i.e. from Larry McVoy) that they have a prototype BK<->CVS 
bidirectional read/write gateway. So nobody would be required to use 
anything.

My personal opinion (which I expressed to Larry) is that the pain is not 
great enough to warrant any change right now, but I guess the committers 
have a better view of that than I do.

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

Re: [HACKERS] Trigger function returning null

[Thomas Hallgren]

Tom Lane wrote:
Why not?
The real answer is "it's historical and I didn't see any need to
change it".  But one could argue that a function returning NULL
doesn't know it's supposed to be a trigger.
 

The reason I ask is that this behavior just bit me in PL/Java. Triggers 
returning null didn't work. I've fixed it at my end by simply forcing 
the isnull to false for triggers. I just wanted to know if there was 
some deeper thought behind this that could result in side effects.

Perhaps you should either change this behavior or make a note it in 
"Writing Trigger Functions in C"? Even if the documented example use a 
local isnull, it's not evident that you have to do that.

Regards,
Thomas Hallgren

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

Re: [HACKERS] We have got a serious problem with pg_clog/WAL synchronization

[Min Xu (Hsu)]

Tom Lane wrote:
Unfortunately, there isn't any pre-existing lock that will serve.
A transaction that is between XLogInsert'ing its COMMIT record and
updating the shared pg_clog data area does not hold any lock that
could be used to prevent a checkpoint from starting.  (Or it didn't
until yesterday's patch, anyway.)
I looked briefly at reorganizing the existing code so that we'd do the
COMMIT XLogInsert while we're holding lock on the shared pg_clog data,
which would solve the problem without adding any new lock acquisition.
But this seemed extremely messy to do.  Also it would be optimizing
transaction commit at the cost of pessimizing other uses of pg_clog,
which might have to wait longer to get at the shared data.  Adding the
new lock has the advantage that we can be sure it's not blocking
anything we don't want it to block.
Thanks for thinking about the problem though ...
 

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

[HACKERS] Asserting existing key to be primary

[Chris Browne]

I have a table that has a candidate primary key (e.g. - unique, not
null) that I wish to actually assert _is_ a primary key.

Is there a way to do that without too much (mwahahhaha! fiddling with
pg_class!!!) trickery?
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://cbbrowne.com/info/emacs.html
"What we need is either less corruption, or more chance to participate
in it."  -- Unknown

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

   http://archives.postgresql.org

Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization

[Kenneth Marshall]

On Thu, Aug 12, 2004 at 01:13:46PM -0400, Tom Lane wrote:
> Kenneth Marshall <[EMAIL PROTECTED]> writes:
> > On Thu, Aug 12, 2004 at 09:58:56AM -0400, Tom Lane wrote:
> >> How would a read-only action work to block out the checkpoint?
> 
> > The latch+version number is use by the checkpoint process. The
> > other processes can do a read of the latch to determine if it has
> > been set. This does not cause a cache invalidation hit. If the
> > latch is set, the competing processes read until it has been
> > cleared and the version updated. This makes the general case of
> > no checkpoint not incur a write and the consequent cache-line
> > invalidation and reload by all processors on an SMP system.
> 
> Except that reading the latch and finding it clear offers no guarantee
> that a checkpoint isn't about to start.  The problem is that we are
> performing two separate actions (write a COMMIT xlog record and update
> transaction status in clog) and we have to prevent a checkpoint from
> starting in between those actions.  I don't see that there's any way to
> do that with a read-only latch.
> 
>   regards, tom lane

Yes, you are correct. I missed that part of the previous thread. When
I saw "exclusive lock" I thought latch since that is what I am investigating
to solve other performance issues that I am addressing.

Ken

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

   http://archives.postgresql.org

[HACKERS] Referencing OLD/NEW Rows on Trigger Definition

[Henry]

I am currently working on a research project at University of Ottawa. 
As part of the project, we need to implement a new feature for the
trigger creation in
PostgreSQL, which defines aliases for the "old" and "new" data by
adding
the REFERENCING clause to the CREATE TRIGGER statement.  Based on my
research, the following items need to be modified to support the
REFERENCING option.

- modify backend\parser\gram.y and keyword.c
- modify CreateTrigStmt node struct
- modify pg_trigger struct in pg_trigger.h, Trigger and TriggerDesc
structs in rel.h, TriggerData struct in trigger.h
- modify CreateTrigger(), RelationBuildTriggers(), InsertTrigger(),
CopyTriggerDesc(), FreeTriggerDesc(), ExecBSInsertTriggers(),
ExecASInsertTriggers(), ExecBRInsertTriggers(),
ExecARInsertTriggers(), ExecBSDeleteTriggers(),
ExecASDeleteTriggers(), ExecBRDeleteTriggers(),
ExecARDeleteTriggers(), ExecBSUpdateTriggers(),
ExecASUpdateTriggers(), ExecBRUpdateTriggers(), ExecBRUpdateTriggers()
- to handle the new option, create new fuctions
ExecBSOInsertTriggers(), ExecASOInsertTriggers(),
ExecBROInsertTriggers(), ExecAROInsertTriggers(),
ExecBSNInsertTriggers(), ExecASNInsertTriggers(),
ExecBRNInsertTriggers(), ExecARNInsertTriggers(),
ExecBSODeleteTriggers(), ExecASODeleteTriggers(),
ExecBRODeleteTriggers(), ExecARODeleteTriggers(),
ExecBSNDeleteTriggers(), ExecASNDeleteTriggers(),
ExecBRNDeleteTriggers(), ExecARNDeleteTriggers(),
ExecBSOUpdateTriggers(), ExecASOUpdateTriggers(),
ExecBROUpdateTriggers(), ExecBROUpdateTriggers(),
ExecBSNUpdateTriggers(), ExecASNUpdateTriggers(),
ExecBRNUpdateTriggers(), ExecBRNUpdateTriggers()
- add supports to ExecutePlan(), ExecInsert(), ExecDelete(),
ExecUpdate() in \backend\executor\execMain.c
- add supports to backend\node\equalfuncs.c and copyfuncs.c

Do I miss anything?  Could anyone give me your advices on the
implementation?

Thanks.


Henry

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

   http://archives.postgresql.org

Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization

[Kenneth Marshall]

On Thu, Aug 12, 2004 at 09:58:56AM -0400, Tom Lane wrote:
> Kenneth Marshall <[EMAIL PROTECTED]> writes:
> > Would it be possible to use a latch + version number in
> > this case to minimize this problem by allowing all but the checkpoint to
> > perform a read-only action on the latch?
> 
> How would a read-only action work to block out the checkpoint?
> 
> More generally, though, this lock is hardly the one I'd be most
> concerned about in an SMP situation.  It's only taken once per
> transaction, while there are others that may be taken many times.
> (At least two of these, the WALInsertLock and the lock on shared
> pg_clog, will need to be taken again in the process of recording
> transaction commit.)
> 
> What I'd most like to find is a way to reduce contention for the
> BufMgrLock --- there are at least some behavioral patterns in which
> that is demonstrably a dominant cost.  See past discussions in the
> archives ("context swap storm" should find you some recent threads).
> 
>   regards, tom lane
> 

The latch+version number is use by the checkpoint process. The
other processes can do a read of the latch to determine if it has
been set. This does not cause a cache invalidation hit. If the
latch is set, the competing processes read until it has been
cleared and the version updated. This makes the general case of
no checkpoint not incur a write and the consequent cache-line
invalidation and reload by all processors on an SMP system.

Ken

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

Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization

[Kenneth Marshall]

> "Min Xu (Hsu)" <[EMAIL PROTECTED]> writes:
> > It seems to me this is an interesting phenomena of interactions between 
> > frequent events of transaction commits and infrequent events of system 
> > checkpoints. A potential alternative solution to adding a new shared 
> > lock to the frequent commit operation is to let the infrequent 
> > checkpoint operation take more overhead. I suppose acquiring/releasing 
> > an extra lock for each commit would incur extra performance overhead, 
> > even when the lock is not contented.  On the other hand, let the 
> > checkpoing operation acquire some existing locks (exclusively) to 
> > effectively disallowing committing transactions to interfere with the 
> > checkpoint process might be a better solution since it incur higher 
> > overhead only when necessary.
> 
> Unfortunately, there isn't any pre-existing lock that will serve.
> A transaction that is between XLogInsert'ing its COMMIT record and
> updating the shared pg_clog data area does not hold any lock that
> could be used to prevent a checkpoint from starting.  (Or it didn't
> until yesterday's patch, anyway.)
> 
> I looked briefly at reorganizing the existing code so that we'd do the
> COMMIT XLogInsert while we're holding lock on the shared pg_clog data,
> which would solve the problem without adding any new lock acquisition.
> But this seemed extremely messy to do.  Also it would be optimizing
> transaction commit at the cost of pessimizing other uses of pg_clog,
> which might have to wait longer to get at the shared data.  Adding the
> new lock has the advantage that we can be sure it's not blocking
> anything we don't want it to block.
> 
> Thanks for thinking about the problem though ...
> 
>regards, tom lane
> 

One problem with a high-traffic LWLock is that they require a write
to shared memory for both the shared lock and the exclusive lock. On
the increasingly prevalent SMP machines, this will cause the invalidation
of the cache-line containing the lock and the consequent reload and its
inherent delay. Would it be possible to use a latch + version number in
this case to minimize this problem by allowing all but the checkpoint to
perform a read-only action on the latch? This should eliminate the cache-line
shenanigans on SMP machines.

Ken Marshall 

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

[HACKERS] PostgreSQL 8.0.0beta1 and diet libc

[Andreas Krennmair]

Hello,

I'm trying to PostgreSQL 8.0.0beta1 together with diet libc
. diet libc is a small libc designed for
getting small, statically linked binaries. In the past, this has often
shown that programs were running faster. So, my current goal is to get
PostgreSQL running together with diet libc. So far, PostgreSQL compiles
and links when you configure PostgreSQL with the following parameters:

CC="/opt/diet/bin/diet -Os gcc -D_BSD_SOURCE -nostdinc" ./configure \
--without-readline --without-zlib --disable-shared

(you can remove --without-{readline,zlib} if you installed these two
libraries for diet libc).

One problem that I have with installation is the following thing:
./zic -d /usr/local/pgsql/share/timezone ./data/africa ./data/antarctica \
./data/asia ./data/australasia ./data/europe ./data/northamerica \
./data/southamerica ./data/pacificnew ./data/etcetera ./data/factory \
./data/backward ./data/systemv ./data/solar87 ./data/solar88 \
./data/solar89
"./data/europe", line 219: line too long
make[1]: *** [install] Error 1
make[1]: Leaving directory
`/usr/src/pgsql/postgresql-8.0.0beta1/src/timezone'
make: *** [install] Error 2

But that's fixed easily by deleting a comment that is too long (same
thing in line 1025, BTW). I then followed the steps described in 
http://www.postgresql.org/docs/7.4/static/installation.html.

And that's where I'm stuck right now:

[EMAIL PROTECTED]:/usr/local/pgsql$ bin/initdb -D data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory data ... ok
creating directory data/global ... ok
creating directory data/pg_xlog ... ok
creating directory data/pg_xlog/archive_status ... ok
creating directory data/pg_clog ... ok
creating directory data/pg_subtrans ... ok
creating directory data/base ... ok
creating directory data/base/1 ... ok
creating directory data/pg_tblspc ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 50
creating configuration files ... ok
creating template1 database in data/base/1 ... FATAL:  XX000: failed to
initialize lc_monetary to "C"
LOCATION:  InitializeGUCOptions, guc.c:2337
child process exited with exit code 1
initdb: failed
initdb: removing contents of data directory "data"
[EMAIL PROTECTED]:/usr/local/pgsql$

As an important note you have to know that diet libc's support for all
the NLS and i18n/l10n-related stuff is largely incomplete and works so
far that common software compiles and runs (this is also one of diet
libc's secondary design goals, to keep out a lot of complexity,
especially when it's about features you will most likely not need on
embdded Linux systems). Any hints on how to get PostgreSQL running
together with diet libc anyway?

Thanks in advance,
Andreas Krennmair


---(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] Trigger function returning null

[Tom Lane]

Thomas Hallgren <[EMAIL PROTECTED]> writes:
> Perhaps you should either change this behavior or make a note it in 
> "Writing Trigger Functions in C"?

Okay, I've changed the text thusly:

diff -r1.35 trigger.sgml
434,435c434,437
< A trigger function must return either NULL or a
< HeapTuple pointer.  Be careful to return either
---
> A trigger function must return either a
> HeapTuple pointer or a NULL pointer
> (not a SQL NULL, that is, do not set isNull true).
> Be careful to return either

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] Ready for Beta ... ?

[Jonah H. Harris]

Regression tests on PostgreSQL 8 Beta are successful.
[EMAIL PROTECTED] regress]$ cat /etc/redhat-release
Red Hat Enterprise Linux AS release 3 (Taroon)
[EMAIL PROTECTED] regress]$ uname -a
Linux lora01 2.4.21-9.0.1.ELsmp #1 SMP Mon Feb 9 22:26:51 EST 2004 i686 
i686 i386 GNU/Linux

Bruce Momjian wrote:
pgman wrote:
Marc G. Fournier wrote:
'k, saw Bruce's commit for the pgport stuff under Win32 ... is anyone 
sitting on that 'one last thing' that should be fixed/corrected before 
Beta proceeds, or am I clear for doing the bundle up tonight for an 
announce tomorrow?

Speak now, or forever hold your piece :)
The only open issue I see for beta1 is perhaps disabling vacuum delay. 
And I would like to have a good Win32 build report from Andreas.

OK, I think Jan agreed not to have vacuum delay enabled by default so I
backed out his patch to it is now disabled.
Patch attached and applied.
I think this means we are ready for beta1.


Index: src/backend/utils/misc/guc.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.227
retrieving revision 1.228
diff -c -c -r1.227 -r1.228
*** src/backend/utils/misc/guc.c	6 Aug 2004 04:15:09 -	1.227
--- src/backend/utils/misc/guc.c	7 Aug 2004 03:08:44 -	1.228
***
*** 1046,1052 
  			NULL
  		},
  		&VacuumCostDelay,
! 		0, 0, 1000, NULL, NULL
  	},
  
  	{
--- 1046,1052 
  			NULL
  		},
  		&VacuumCostDelay,
! 		50, 0, 1000, NULL, NULL
  	},
  
  	{
Index: src/backend/utils/misc/postgresql.conf.sample
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.121
retrieving revision 1.122
diff -c -c -r1.121 -r1.122
*** src/backend/utils/misc/postgresql.conf.sample	7 Aug 2004 01:04:50 -	1.121
--- src/backend/utils/misc/postgresql.conf.sample	7 Aug 2004 03:08:49 -	1.122
***
*** 74,80 
  #vacuum_cost_page_miss = 10	# 0-1 credits
  #vacuum_cost_page_dirty = 20	# 0-1 credits
  #vacuum_cost_limit = 200	# 0-1 credits
! #vacuum_cost_delay = 0		# 0-1000 milliseconds
  
  # - Background writer -
  #bgwriter_delay = 200		# 10-5000 milliseconds
--- 74,80 
  #vacuum_cost_page_miss = 10	# 0-1 credits
  #vacuum_cost_page_dirty = 20	# 0-1 credits
  #vacuum_cost_limit = 200	# 0-1 credits
! #vacuum_cost_delay = 50		# 0-1000 milliseconds
  
  # - Background writer -
  #bgwriter_delay = 200		# 10-5000 milliseconds


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI  | fax:   505.224.3014
525 Buena Vista SE   | [EMAIL PROTECTED]
Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/
"All great truths begin as blasphemies."
--  George Bernard Shaw
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] BEGIN EXCEPTION END - small bug?

[Daniel Schuchardt]

Hi list,
i tried a bit with errorhandling and found the following :
(i want to ignore the dublicate key exception)
CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS'
BEGIN
 BEGIN
  INSERT INTO table a dublicate key (primary);
 EXCEPTION
  WHEN OTHERS THEN ROLLBACK;
 END;
 RETURN ''test'';
END'LANGUAGE plpgsql;
will result in
ERROR:  SPI_prepare failed for "ROLLBACK": SPI_ERROR_TRANSACTION
I noticed the right syntax would be (works fine)
CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS'
BEGIN
 BEGIN
  INSERT INTO table a dublicate key (primary);
 EXCEPTION
  WHEN OTHERS THEN
 END;
 RETURN ''test'';
END'LANGUAGE plpgsql;
Just a hint
Regards Daniel
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Postgres development model

[Christopher Browne]

Oops! [EMAIL PROTECTED] (Reinoud van Leeuwen) was seen spray-painting on a wall:
> On Mon, Aug 09, 2004 at 09:30:09AM +0200, Peter Eisentraut wrote:
>> Tom Lane wrote:
>> > I haven't seen any particular reason why we should adopt another SCM.
>> > Perhaps BitKeeper or SubVersion would be better for our purposes than
>> > CVS, but are they enough better to justify the switchover costs?
>> 
>> BitKeeper ist not open source, so it's out of the question for most 
>> people.  
>
> Why? I understood that using BitKeeper for free for Open Source projects 
> is allowed. (but IANAL).
> It is available (on many platforms). It works great. Once you use 
> changesets you'll never want to go back to cvs.

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.

(d)  Notwithstanding any other terms in this License, this
 License is not available to You if You and/or your
 employer develop, produce, sell, and/or resell a
 product which contains substantially similar capabil-
 ities of the BitKeeper Software, or, in the reason-
 able opinion of BitMover, competes with the BitKeeper
 Software.

I don't think it would be "way out there" to consider that this means
that anyone that contributes to FreeBSD or to Debian Linux (two
systems that include packages for both SubVersion and Arch) is
forbidden to use BitKeeper.  

After all, they are involved in "developing/producing" a product that
contains substantially similar capabilities.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com').
http://www3.sympatico.ca/cbbrowne/oses.html
"Linux  is not  ready  for  the Enterprise.   There  is not a   single
voice-controlled app for any of the  mission critical functions of the
Enterprise. Conspicuously absent  are warp  core control, phaser  bank
activation,  interstellar  navigation,  transporter operation, and the
all-important  self-destruct  sequence. Until  these and  thousands of
other important apps are  written and deployed, Linux  will just be  a
toy in the Enterprise." -- Kevin Novak, Network Computing Magazine

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

[HACKERS] hot spare / log shipping work on

[Gaetano Mendola]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
I'm having some spare time and I'm testing what Tom Lane was
suggesting:
===
Tom Lane wrote:
1. You set up WAL archiving on the master, and arrange to ship copies of
completed segment files to the slave.
2. You take an on-line backup (ie, tar dump) on the master, and restore
it on the slave.
3. You set up a recover.conf file with the restore_command being some
kind of shell script that knows where to look for the shipped-over
segment files, and also has a provision for being signaled to stop
tracking the shipped-over segments and come alive.
4. You start the postmaster on the slave.  It will try to recover.  Each
time it asks the restore_command script for another segment file, the
script will sleep until that segment file is available, then return it.
5. When the master dies, you signal the restore_command script that it's
time to come alive.  It now returns "no such file" to the patiently
waiting postmaster, and within seconds you have a live database on the
slave.
===
How I'm expanding the point above:
1) This is the easy part and the task can be accomplished with a simple:
cp %p /mnt/server/archivedir/%f
2) Easy task
3+4) I already wrote the restore_command that do the trick, it take 3
~   parameters:   
~   The partial_directory will contain the partial_segment shipped each
~   minute, and a file "alive" that is "touch"ed periodically
~   The script when called perform these tasks:
a) Check if the file requested exist
a1) If exist check that is a 16MB file ( the request can
~arrive during the copy ), if is not 16MB sleep for
~1 second and retry. This is done for 20 try, after
~this time out the script exit with a nonzero return time.
~When the file reach a size of 16MB ( or is already a 16MB
~file then it's copied with:   cp  
~a2) If the file not exist this mean that is not yet recycled and
~is a partial file present on the partial directory,
~check if the "alive" file is older then 2 minutes.
~   a21) If the file is older than 2 minutes I assume that
~the master is dead: I move the partial WAL file
~present in the partial directory to the 
~directory, and I exit returning a 0 ( the asked file
~was the partial ). If the partial file do not exist
~this mean that in the previous call I already moved the
~partial file and then I have to exit with a nonzero value.
   a22) If the file is newer than 2 minutes I assume that
~the master is alive and I sleep for 5 seconds and I
~restart from the point a)
5) If the master dies the daemon ( a running shell script ) that is running on
~   the master will not touch the "alive" file.
~   If the master is alive the daemon copy the current WAL file in the  with the name .tmp and after the copy:
~   mv .tmp .partial

Do you see any pitfall on it ?
I think in an hour I'll test it and I let you know.
Regard
Gaetano Mendola





-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBHNW+7UpzwH2SGd4RAsMBAJ9diSsgG3y6rnueWbZLOvjzko07OwCdGaxE
f8mwC9A4sDJ8nN+XhcUKjP8=
=9SrG
-END PGP SIGNATURE-
---(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] to_char() and negative intervals

[Bruce Momjian]

Someone on IRC just reported this to_char() failure with negative
intervals:

test=> SELECT to_char('-00:17:56.886375'::interval, 'HH24:MI:SS');
 to_char
--
 00:-1:-5
(1 row)

Looks like a bug to me.  :-)

-- 
  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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Trigger function returning null

[Tom Lane]

Thomas Hallgren <[EMAIL PROTECTED]> writes:
> In backend/commands/trigger.c the following can be found:
>  /*
>   * Trigger protocol allows function to return a null pointer,
>   * but NOT to set the isnull result flag.
>   */
>  if (fcinfo.isnull)
>  ereport(ERROR,

> Why?

Why not?

The real answer is "it's historical and I didn't see any need to
change it".  But one could argue that a function returning NULL
doesn't know it's supposed to be a trigger.

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] Dump Version

[strk]

On Fri, Aug 13, 2004 at 11:12:50PM +1000, Philip Warner wrote:
> At 10:30 PM 13/08/2004, strk wrote:
> >>From PG73:
> >552; 2663984 CAST bytea (public.wkb)
> >>From PG74:
> >547; 1915318 CAST CAST (public.wkb AS bytea)
> >
> >Since these two dumps have same file format version I asked..
> 
> The file format (unfortunately for you) does not define the format of the 
> contents of the TOC entries.

Ok. This is what I wanted to know. Thank you.

--strk;

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

   http://archives.postgresql.org

Re: [HACKERS] Dump Version

[Philip Warner]

At 10:30 PM 13/08/2004, strk wrote:
>From PG73:
552; 2663984 CAST bytea (public.wkb)
>From PG74:
547; 1915318 CAST CAST (public.wkb AS bytea)
Since these two dumps have same file format version I asked..
The file format (unfortunately for you) does not define the format of the 
contents of the TOC entries. It may be messy, but you could use your source 
sql file to create an empty database in the same PG version, then do:

pg_dump -Fc tempdatabase | pg_restore -l
to get a compatible list. But you'll still have mismatched OIDs, and a temp 
database to create/cleanup.

I can't see another way that won't require constant updates & tweaking into 
the future.



Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 03 5330 3172  | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp.mit.edu:11371   |/ 

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

[HACKERS] i18n - sk

[Zoltan Bartko]

hackers,

here goes the last missing bunch of strings translated to sk.

Cheers

Zoltan


pg_resetxlog-sk.po.bz2
Description: BZip2 compressed data

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

[Andrew Dunstan]

Andrew Dunstan wrote:
The attached patch appears to solve the problem. However, while it 
makes us conform to the first sentence below from the docs, it doesn't 
comply with the second. Not sure what to do about that. Maybe there's 
a better solution?


Attached patch seems much better, I think.
cheers
andrew
Index: src/bin/psql/copy.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/copy.c,v
retrieving revision 1.50
diff -c -r1.50 copy.c
*** src/bin/psql/copy.c 11 Jul 2004 21:34:03 -  1.50
--- src/bin/psql/copy.c 13 Aug 2004 12:41:15 -
***
*** 717,723 
PQputline(conn, copybuf);
if (firstload)
{
!   if (!strcmp(copybuf, "\\."))
{
copydone = true;
break;
--- 717,724 
PQputline(conn, copybuf);
if (firstload)
{
!   if ((!strcmp(copybuf, "\\.")) || 
!   (c == '\n' && !strcmp(copybuf,"\\.\r")))
{
copydone = true;
break;

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

Re: [HACKERS] Dump Version

[strk]

Philip, I'm trying to make the process you describe automatic.
A script reads objects definitoin from an .sql file
and automatically deletes linkes from 'listfile' corresponding
to objects already found in the given .sql file.

As for my problem (detecting CASTS) I had to support 
lines in the form:

>From PG73:
552; 2663984 CAST bytea (public.wkb)
>From PG74:
547; 1915318 CAST CAST (public.wkb AS bytea)

Since these two dumps have same file format version I asked..

--strk;

On Fri, Aug 13, 2004 at 10:05:09PM +1000, Philip Warner wrote:
> At 08:53 PM 13/08/2004, strk wrote:
> >Commenting out lines from the dump corresponding to objects that
> >will be defined by another script.
> 
> I may have misunderstood, but try:
> 
> pg_restore -l dumpfile > listfile
> 
> then delete lines from listfile that you do not want, and do:
> 
> pg_restore -L listfile dumpfile
> 
> and it will only restore the items corresponding to lines in listfile.
> 
> 
> 
> 
> 
> 
> Philip Warner| __---_
> Albatross Consulting Pty. Ltd.   |/   -  \
> (A.B.N. 75 008 659 498)  |  /(@)   __---_
> Tel: (+61) 0500 83 82 81 | _  \
> Fax: (+61) 03 5330 3172  | ___ |
> Http://www.rhyme.com.au  |/   \|
>  |----
> PGP key available upon request,  |  /
> and from pgp.mit.edu:11371   |/ 

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

[HACKERS] i18n - sk

[Zoltan Bartko]

hackers,

attached you may find another bunch of translated strings.

Cheers

Zoltan


pgscripts-sk.po.bz2
Description: BZip2 compressed data

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

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

[Andrew Dunstan]

[redirecting]
I have abstracted this problem, and we definitely have a newline bug 
that has to be fixed, IMNSHO.

Attached are 2 scripts that are identical except that one has DOS-style 
line endings and one has Unix style line endings. The DOS-style just 
fails miserably with no warning. It's not related to Windows file-end 
munging - the problem is observable on Linux - psql doesn't correctly 
detect the end of copy input with "\." if it's followed by CRNL.

The attached patch appears to solve the problem. However, while it makes 
us conform to the first sentence below from the docs, it doesn't comply 
with the second. Not sure what to do about that. Maybe there's a better 
solution?

"COPY FROM can handle lines ending with newlines, carriage returns, or 
carriage return/newlines. To reduce the risk of error due to 
un-backslashed newlines or carriage returns that were meant as data, 
COPY FROM will complain if the line endings in the input are not all alike."

cheers
andrew
Tom Haddon wrote:
Here's what I can share, as there's some sensitive stuff in others.
This is just a dump from one of the databases. I was able to load it
using the \i switch, but it has only populated data from the first
table. Also, it kept prompting me to hit a key as the screen was
scrolling. This doesn't seem normal to me. And I tried running it as a
SQL statement from within PgAdmin3, and got:
"ERROR:  syntax error at or near "1" at character 15030"
The first "1" you see is where this error is.
COPY backup_data (id, lu, ub, tape, contents, last_writter,
times_written) FROM stdin;
1   2004-07-13 09:37:14.78254   thaddon DLT05   Full
Backup (including only 2004 filings) 6/9/04\r\n\r\nNow archived
2004-06-09  6
4   2004-07-13 09:40:57.876355  thaddon DLT09   Full
Backup 7/12/04  2004-07-12  4
3   2004-07-21 16:28:09.843069  thaddon DLT07   Full
Backup 7/21/04\r\n\r\nC:\\Perforce\\*.* /SUBDIR \r\nD:\\backup\\*.*
/SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE
\r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR \r\nYODA\\qa\\*.*
/SUBDIR \r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-21
14
2   2004-07-22 13:27:01.657706  thaddon DLT06   Full
Backup 7/22/04\r\n\r\nC:\\Perforce\\*.* /SUBDIR \r\nD:\\backup\\*.*
/SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE
\r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR \r\nYODA\\qa\\*.*
/SUBDIR \r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-22
10
5   2004-07-27 10:49:22.786997  thaddon DLT08
Incremental backup since last full 7/21/04 last updated
7/23/04\r\n\r\nWill include (as of 7/23/04):\r\n\r\nC:\\Perforce\\*.*
/SUBDIR \r\nD:\\backup\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE
\r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR \r\nYODA\\qa\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR
\r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-23  3
6   2004-07-27 10:50:11.802647  thaddon DLT10
Differential backup since 7/21/04\r\n\r\nC:\\Perforce\\*.* /SUBDIR
\r\nD:\\backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.*
/SUBDIR \r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR
\r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE
\r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR \r\nYODA\\qa\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR
\r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR
\r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-27  1
\.
Thanks, Tom
-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 12, 2004 8:40 AM
To: Tom Haddon
Cc: [EMAIL PROTECTED]
Subject: Re: [pgsql-hackers-win32] Import from Linux to Windows


Tom Haddon wrote:
 

I don't think so. Did a search for it in vi and nothing. It doesn't 
give me an error, just exits. Last statement is "CREATE TABLE".

Sorry, not very helpful...


   

I think you'll need to let us look at the dump file to make any progress
- otherwise we are just guessing in the dark.
thanks
andrew
 

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

CREATE TABLE bar (
c text,
d text
);
C

Re: [HACKERS] Dump Version

[Philip Warner]

At 08:53 PM 13/08/2004, strk wrote:
Commenting out lines from the dump corresponding to objects that
will be defined by another script.
I may have misunderstood, but try:
pg_restore -l dumpfile > listfile
then delete lines from listfile that you do not want, and do:
pg_restore -L listfile dumpfile
and it will only restore the items corresponding to lines in listfile.



Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 03 5330 3172  | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp.mit.edu:11371   |/ 

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

[HACKERS] Trigger function returning null

[Thomas Hallgren]

In backend/commands/trigger.c the following can be found:
/*
 * Trigger protocol allows function to return a null pointer,
 * but NOT to set the isnull result flag.
 */
if (fcinfo.isnull)
ereport(ERROR,
Why?
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Dump Version

[strk]

On Fri, Aug 13, 2004 at 08:42:44PM +1000, Philip Warner wrote:
> At 08:09 PM 13/08/2004, strk wrote:
> >What does 'Dump Version' refer to then ? Are these ToC output
> >changes encoded somehow ?
> 
> The file format version of pg_dump; the actual contents depend on pg_dump 
> version, and the source database since pg_dump asks the source (as much as 
> possible) to present it's data in a textual format.
> 
> What are you trying to do?

Commenting out lines from the dump corresponding to objects that
will be defined by another script. I also had problem with OPERATOR
as only the name is listed (I parsed the ascii version as a workaround).
I can parse the ascii version for CASTS as well... but I if everything
was possible just using the ToC that would be faster.

--strk;

> 
> 
> 
> Philip Warner| __---_
> Albatross Consulting Pty. Ltd.   |/   -  \
> (A.B.N. 75 008 659 498)  |  /(@)   __---_
> Tel: (+61) 0500 83 82 81 | _  \
> Fax: (+61) 03 5330 3172  | ___ |
> Http://www.rhyme.com.au  |/   \|
>  |----
> PGP key available upon request,  |  /
> and from pgp.mit.edu:11371   |/ 

---(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] Dump Version

[Philip Warner]

At 08:09 PM 13/08/2004, strk wrote:
What does 'Dump Version' refer to then ? Are these ToC output
changes encoded somehow ?
The file format version of pg_dump; the actual contents depend on pg_dump 
version, and the source database since pg_dump asks the source (as much as 
possible) to present it's data in a textual format.

What are you trying to do?

Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 03 5330 3172  | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp.mit.edu:11371   |/ 

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

[HACKERS] Dump Version

[strk]

Hello, dump problems again.
I'm working on a dump ToC parser for spatial database restore
(postgis).

I've noticed that pg_dump-7.3 and pg_dump-7.4 list CAST entries
in different ways. Still 'Dump Version' is the same (1.7-0).

What does 'Dump Version' refer to then ? Are these ToC output
changes encoded somehow ?

TIA

--strk;

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

   http://archives.postgresql.org