Re: [HACKERS] CVS compile failure

[Tom Lane]

"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
>> I am seeing a compile failure in current CVS from strptime():
>> ./backend/access/transam/xlog.c:if (strptime(tok2, "%Y-%m-%d %H:%M:%S", &tm) == 
>> NULL)
>> BSD/OS does not have that function.

> Under FreeBSD:
> " The strptime() function does not correctly handle multibyte characters in
>   the format argument"
> Not sure how critical that is for what you are doing, mind you ...

Not at all, since in this call the format is the fixed constant
"%Y-%m-%d %H:%M:%S".  But it's odd that your BSD variant has strptime()
where Bruce's does not.

I suppose it doesn't much matter though: we have to recode without
strptime.  No big deal.  I'll fix it tomorrow if no one beats me to it.

regards, tom lane

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

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

Re: [HACKERS] CVS compile failure

[Marc G. Fournier]

On Mon, 19 Jul 2004, Tom Lane wrote:
Bruce Momjian <[EMAIL PROTECTED]> writes:
I am seeing a compile failure in current CVS from strptime():
  ./backend/access/transam/xlog.c:if (strptime(tok2, "%Y-%m-%d %H:%M:%S", &tm) == 
NULL)
BSD/OS does not have that function.
Hmph.  I was wondering if that was really portable or not :-(.
Any ideas about a quick-and-dirty replacement?
Maybe we could call abstimein or something like that.  We just want to
convert a human-readable string value to a time_t, and I can't say that
I thought the strptime behavior was all that friendly anyway...
Under FreeBSD:
" The strptime() function does not correctly handle multibyte characters in
 the format argument"
Not sure how critical that is for what you are doing, mind you ...

Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] CVS compile failure

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> I am seeing a compile failure in current CVS from strptime():
>   ./backend/access/transam/xlog.c:if (strptime(tok2, "%Y-%m-%d %H:%M:%S", &tm) 
> == NULL)
> BSD/OS does not have that function.

Hmph.  I was wondering if that was really portable or not :-(.
Any ideas about a quick-and-dirty replacement?

Maybe we could call abstimein or something like that.  We just want to
convert a human-readable string value to a time_t, and I can't say that
I thought the strptime behavior was all that friendly anyway...

regards, tom lane

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

Re: [HACKERS] pg_dump bug fixing

[Tom Lane]

Rod Taylor <[EMAIL PROTECTED]> writes:
> I think what we want is a clean template without all of the extras that
> template1 has.

Sounds like a job for ... template0 !

Seriously, this thread would be more convincing if anyone in it betrayed
any knowledge that pg_dump wants you to start from template0 rather than
template1.

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] Why we really need timelines *now* in PITR

[Tom Lane]

Simon Riggs <[EMAIL PROTECTED]> writes:
> The way you write this makes me think you might mean you would allow: we
> can start recovering in one timelines, then rollforward takes us through
> all the timeline nexus points required to get us to the target
> timeline.

Sure.  Let's draw a diagram:

0001.0014 - 0001.0015 - 0001.0016 - 0001.0017 - ...
  |
  + 0002.0016 - 0002.0017 - ...
  |
  + 0003.0017 - ...

If you decide you would like to recover to someplace in timeline 0002,
you need to take the 0002 log files where they exist, and the 0001
log files where there is no 0002, except you do not revert to 0001
once you have used an 0002 file (this restriction is needed in case
the 0001 timeline goes to higher segment numbers than 0002 has reached).
In no case do you use an 0003 file.

> I had imagined that recovery would only ever be allowed to start and end
> on the same timeline. I think you probably mean that?

Logically it's all one timeline, I suppose, but to implement it
physically that way would mean duplicating all past 0001 segments when
we want to create the 0002 timeline.  That's not practical and not
necessary.

> Another of the issues I was thinking through was what happens at the end
> of your scenario abobe
> - You're on timeline 1 and you need to perform recovery.
> - You perform recovery and timeline 2 is created.
> - You discover another error and decide to recover again.
> - You recover timeline 1 again: what do you name the new timeline
> created? 2 or 3?

You really want to call it 3.  To enforce this mechanically would
require having a counter that sits outside the $PGDATA directory and
is not subject to being reverted by a restore-from-backup.  I don't
see any very clean way to do that at the moment --- any thoughts?

In the absence of such a counter we could ask the DBA to specify a new
timeline number in recovery.conf, but this strikes me as one of those
easy-to-get-wrong things ...

One possibility is to extend the archiving API so that we can inquire
about the largest timeline number that exists anywhere in the archive.
If we take new timeline number = 1 + max(any in archive, any in pg_xlog)
then we are safe.  But I'm not really convinced that such a thing would
be any less error-prone than the manual way :-(, because for any
archival method that's more complicated than "cp them all into one
directory", it'd be hard to extract the max stored filename.

regards, tom lane

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

[HACKERS] CVS compile failure

[Bruce Momjian]

I am seeing a compile failure in current CVS from strptime():

  ./backend/access/transam/xlog.c:if (strptime(tok2, "%Y-%m-%d %H:%M:%S", &tm) == 
NULL)

BSD/OS does not have that function.

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

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

Re: [HACKERS] Escaping metacharacters

[Greg Stark]

DarkSamurai <[EMAIL PROTECTED]> writes:

> And suppose I use this :
> 
> > $cat = $GET["category"];
> > $query = " SELECT Id, Title, Abstract FROM News " . "Where Category=" . $cat;

>From a security point of view you're even better off using something like

$dbh->query("SELECT id, title, abstract FROM news WHERE category = ?", $cat);

Or whatever the equivalent syntax is in your driver. Avoiding mixing
user-provided data with the code entirely. The driver may still have to do the
mixing but it's probably better at it than you are. And in newer versions of
Postgres it doesn't even have to do it at all, and can ship the data to the
server separately.

-- 
greg


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

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

Re: [HACKERS] Toward better documentation

[Andrew Dunstan]

David Fetter wrote:
Kind people,
It's been pointed out to me that I tend to document by example
, e.g.
My personal opinion is that this is a good thing, and should happen
throughout the PostgreSQL documentation.  However, this is not my
decision to make.
Here's some pros & cons, as I see it, for including more examples in
standard docs.
Pros:
* Accomodates different learning styles
* Jump-starts development by providing working code
* Built-in tests for breakage of backward compatibility
Cons:
* Start-up costs re: actually writing & checking the examples
* Bigger document base to update & maintain
* Disk space
What do you all think?
 

I doubt you will find any objections to more examples. However I am sure 
there would be many objections to substituting examples for reference 
material. Rather, examples should illustrate and amplify the reference 
material.

Also, there does need to be a consistent style in the docs. I suggest 
one reasonable guideline, given the primary function of the docs as an 
authoritative reference, and following the style mainly used already, is 
'reference material first, examples following' in each relevant section.

If we were preparing docs intended primarily as teaching material, very 
different principles might apply, but personally I don't see that as the 
primary purpose of the docs.

cheers
andrew
---(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] Escaping metacharacters

[Christopher Kings-Lynne]

function SQLString($s) {
   $s = str_replace("'", "\\s", $s)'
   $s = str_replace("\\", "", $s);
   return "'" . $s . "'";

Have you looked at the function PQescapeString() in the libpq library? 
Using that would seem to be a simpler way of solving this problem.
If he's using PHP, he should be using the pg_escape_string() function.
Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] Toward better documentation

[Christopher Kings-Lynne]

Pros:
* Accomodates different learning styles
* Jump-starts development by providing working code
* Built-in tests for breakage of backward compatibility
Cons:
* Start-up costs re: actually writing & checking the examples
* Bigger document base to update & maintain
* Disk space
What do you all think?
Sounds really great :)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Toward better documentation

[Christopher Kings-Lynne]

I see I have gotten off to a bad start here, and I apologize for not
having made myself clearer.  I did not suggest removing the
"principles" items from the docs, nor did I suggest that anybody
thought examples are a bad thing.
What I am looking for is how the community feels about committing
resources, which are in short supply, to putting more examples in the
docs.  I think this is a worthwhile effort, as it can pull in a larger
group of people to do documentation, taking some load off people who
do coding. :)
If you are the resource, and you have supply, then go for it.  Try to 
assemble a team :)  I know some people in the IRC channel were wanting 
to help with docs.

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

Re: [HACKERS] pg_dump bug fixing

[Christopher Kings-Lynne]

No, because pg_dump itself dumps template1's contents...
Are you planning on extending this line of thought to other things in
template1 such as operators?
Ah, I see where you are going with this
I think that we should treat the public schema specially :)  That's 
because it is much more likely to be dropped and messed with than the 
other system objects, in fact you're encouraged to drop it in the docs.

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

[HACKERS] function return type

[Andrew Dunstan]

How can I get a TupleDesc for the return type of a non-SR function that 
returns a tuple? I'm sure it's there somewhere but I just can't see it.

thanks
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] Escaping metacharacters

[Oliver Elphick]

On Thu, 2004-07-15 at 23:02, DarkSamurai wrote:
> Hi,
> 
> To prevent SQL injections, I try to neutralize SQL metacharacters.
> 
> ex:
> 
> Code:
> 
> 
> > function SQLString($s) {
> > $s = str_replace("'", "\\s", $s)'
> > $s = str_replace("\\", "", $s);
> > return "'" . $s . "'";

Have you looked at the function PQescapeString() in the libpq library? 
Using that would seem to be a simpler way of solving this problem.

Libraries such as Perl DBI have similar functions built in.

-- 
Oliver Elphick  [EMAIL PROTECTED]
Isle of Wight  http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
 
 "For God so loved the world, that he gave his only 
  begotten Son, that whosoever believeth in him should 
  not perish, but have everlasting life." John 3:16 


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

   http://archives.postgresql.org

Re: [HACKERS] Why we really need timelines *now* in PITR

[Simon Riggs]

On Sat, 2004-07-17 at 21:36, Tom Lane wrote:
> If we do not add timeline numbers to WAL file names, we will be forced
> to destroy information during recovery.  Consider the following
> scenario:
> 
> 1. You have a WAL directory containing, say, WAL segments 0010 to 0020
> (for the purposes of this example I won't bother typing out realistic
> 16-digit filenames, but just use 4-digit names).
> 
> 2. You discover that your junior DBA messed up badly and you need to
> revert to yesterday evening's state.  Let's say the chosen recovery end
> time is in the middle of file 0014.
> 
> 3. You run the recovery process.  At its end, the WAL end pointer will
> be 0014 and some offset.
> 
> If we simply run forward from this situation, then we will be
> overwriting existing WAL records in the existing files 0014-0020.
> This is bad from the point of view of not wanting to discard information
> (what if we decide we should have recovered to a later time??), but
> there is an even more serious reason for not doing that.  Suppose we
> suffer a crash sometime after recovery.  On restart, the system will
> start replaying the logs, and *there will be nothing to keep it from
> replaying all the way to the end of file 0020*.  (The files will contain
> proper, in-sequence page headers, so the tests that normally detect
> recycled log segments won't think there is anything wrong.)  This will
> leave you with a thoroughly corrupt database.
> 
> One way to solve this would be to physically discard 0015-0020 as soon
> as we decide we're stopping short of the end of WAL.  I think that is
> unacceptable on don't-throw-away-information grounds.  I think it would
> be far better to invent the timeline concept.  Then, our old WAL files
> would be named say 0001.0010 through 0001.0020, and we would start
> logging into 0002.0014 after recovery.
> 
> A slightly tricky point is that we have to "sew together" the end of one
> timeline and the start of the next --- for instance, we only want the
> front part of 0001.0014, not the back part, to be part of the new
> timeline.  Patrick Macdonald told me about a pretty baroque scheme that
> DB2 uses for this, but I think it would be simplest if we just copied
> the appropriate amount of data from 0001.0014 into 0002.0014 and then
> ran forward from there.  Copying a max of 16MB of data doesn't sound
> very onerous.
> 

Well, yes - I completely agree that we need the timeline concept as one
of the highest priorities. I originally raised the problem timelines
solve because of the errors I had experienced re-running restores many
times with the same archive set. It's just too easy to overwrite log
files without the timeline concept.

IMHO you don't need to change the xlog format as a necessary step to
introduce timelines. Simply adding  to the logid is sufficient
(which lets face it takes a heck of long time before it gets to 1...)

[Also, as an extra detail on your analysis, when recovery is finished
you need to move both primary and secondary checkpoint markers forwards
to the new timeline, so that crash recovery can't go back to the old
timeline]

If you're going to change xlog filenames, then I would think that adding
the system identifier to the xlogs would be a very good addition. I
would simply have recommended keeping them in separate directories, but
putting it on the name would be best. PostgreSQL doesn't have a name
concept...which would be the thing to use if it did.

> During WAL replay or recovery, there would be a notion of the "target
> timeline" that you are trying to recover to a point within.  The rule
> for selecting which WAL segment file to read is "use the one with
> largest timeline number less than or equal to the target, and never less
> than the timeline number you used for the previous segment".  So for
> example if we realized we'd chosen the wrong recovery target time, we
> could backpedal and redo the same recovery process with target timeline
> 0001, ignoring any WAL segments that had been archived with timeline
> 0002.  Alternatively, if we were simply doing crash recovery in timeline
> 0002, we could stop at (say) segment 0002.0018, and we'd know that we
> should ignore 0001.0019 because it is not in our timeline.
> 

That sounds like the way it should work.

The way you write this makes me think you might mean you would allow: we
can start recovering in one timelines, then rollforward takes us through
all the timeline nexus points required to get us to the target timeline.

I had imagined that recovery would only ever be allowed to start and end
on the same timeline. I think you probably mean that?


Another of the issues I was thinking through was what happens at the end
of your scenario abobe
- You're on timeline 1 and you need to perform recovery.
- You perform recovery and timeline 2 is created.
- You discover another error and decide to recover again.
- You recover timeline 1 again: what do you name the new timeline
created? 2 or 3? If you call it 2 yo

Re: [HACKERS] Toward better documentation

[Marc G. Fournier]

On Sun, 18 Jul 2004, David Fetter wrote:
What I am looking for is how the community feels about committing 
resources, which are in short supply, to putting more examples in the 
docs.  I think this is a worthwhile effort, as it can pull in a larger 
group of people to do documentation, taking some load off people who do 
coding. :)
Depends on who you feel should/would be doing these examples?  Note that 
there are *alot* of ppl out there that are not coders wondering what they 
can do to contribute ... this is definitely one way.


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[HACKERS] Escaping metacharacters

[DarkSamurai]

Hi,
To prevent SQL injections, I try to neutralize SQL metacharacters.
ex:
Code:

function SQLString($s) {
$s = str_replace("'", "\\s", $s)'
$s = str_replace("\\", "", $s);
return "'" . $s . "'";

And suppose I use this :
$cat = $GET["category"];
$query = " SELECT Id, Title, Abstract FROM News " . "Where Category=" . $cat;


If a malicious user tries to input
1' UNION SELECT 1, Usr, Pass FROM Usr
it would just pass as plain text like 1 \' UNION SELECT 1, Usr, Pass 
FROM Usr

[edit]
And if he tried 1\' UNION (...) it would pass 1\\\' UNION (...) to the 
database server.
[/edit]

Is that safe from SQL injection?
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Release planning

[Christopher Browne]

A long time ago, in a galaxy far, far away, Gaetano Mendola <[EMAIL PROTECTED]> wrote:
>> I was thinking of something much simpler where Jan would create an
>> ARC patch against 7.4.X and have it either in /contrib for 7.4.X or
>> on our ftp servers, or on a web site.  I could create a mechanism
>> so SELECT version() would display Jan's add-on.
>
> :-(
>
> I was asking to add the vacuum delayed patch to 7.4 months ago and
> the response was: why introduce instability to a stable release ?  I
> hope the global consensus is a no way to procede also for ARC.

If, as you suggest, ARC is too immature to go in, then I presume that
would also imply that "global consensus" should also be that:

 a) PITR is much too immature to put in;
 b) Win32 is way too immature to put in;
 c) NT is way too immature, as well;
 d) Tablespaces are way too immature to be included.

Which eliminates all of the big, interesting reasons to upgrade to
7.5.

Or is ARC the only thing you regard as a misfeature?  Interesting that
it was put into 7.5 _early_ in the process, so that everyone that has
lately touched the betas would be getting bitten pretty heavily if it
was laden with bugs...
-- 
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/emacs.html
"There  is something in  the lecture  course which  may not  have been
visible so far, which is reality ..."  -- Arthur Norman

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

Re: [HACKERS] Toward better documentation

[David Fetter]

On Sun, Jul 18, 2004 at 10:14:23PM +0200, Peter Eisentraut wrote:
> David Fetter wrote:
> > It's been pointed out to me that I tend to document by example
> 
> Documenting by example is like proving by example -- it helps
> understanding, but it doesn't replace the actual thing.
> Nonetheless, no one ever claimed that more examples would be a bad
> thing.

I see I have gotten off to a bad start here, and I apologize for not
having made myself clearer.  I did not suggest removing the
"principles" items from the docs, nor did I suggest that anybody
thought examples are a bad thing.

What I am looking for is how the community feels about committing
resources, which are in short supply, to putting more examples in the
docs.  I think this is a worthwhile effort, as it can pull in a larger
group of people to do documentation, taking some load off people who
do coding. :)

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

[HACKERS] XLogWrite: write request 0/53A4000 is past end of log 0/53A4000

[lists]

this is 7.3.3 (PGDG rpm's) on red hat 7.2:
i had a lot of debugging enabled.  i went to create an index, and pgsql 
dumped on me.  i tried restarting several times - no luck.

starting postgresql gives
LOG:  database system shutdown was interrupted at 2004-07-16 01:11:56 EDT
LOG:  checkpoint record is at 0/53A3FC0
LOG:  redo record is at 0/53A3FC0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 44321; next oid: 111535
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  ReadRecord: unexpected pageaddr 0/33A4000 in log file 0, segment 
5, offset 3817472
LOG:  redo is not required
   [  OK  ]
# PANIC:  XLogWrite: write request 0/53A4000 is past end of log 0/53A4000
LOG:  startup process (pid 16068) was terminated by signal 6
LOG:  aborting startup due to startup process failure


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

Re: [HACKERS] Toward better documentation

[Peter Eisentraut]

David Fetter wrote:
> It's been pointed out to me that I tend to document by example

Documenting by example is like proving by example -- it helps 
understanding, but it doesn't replace the actual thing.  Nonetheless, 
no one ever claimed that more examples would be a bad thing.

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


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

Re: [HACKERS] Toward better documentation

[Marc G. Fournier]

On Sun, 18 Jul 2004, David Fetter wrote:
Kind people,
It's been pointed out to me that I tend to document by example
, e.g.
My personal opinion is that this is a good thing, and should happen
throughout the PostgreSQL documentation.  However, this is not my
decision to make.
Here's some pros & cons, as I see it, for including more examples in
standard docs.
Pros:
* Accomodates different learning styles
* Jump-starts development by providing working code
* Built-in tests for breakage of backward compatibility
Cons:
* Start-up costs re: actually writing & checking the examples
* Bigger document base to update & maintain
* Disk space
What do you all think?
Agreed, where applicable ... *but* ... there is nothing stopping anyone 
from submitting patches for such examples, is there?


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] Toward better documentation

[David Fetter]

Kind people,

It's been pointed out to me that I tend to document by example
, e.g.

My personal opinion is that this is a good thing, and should happen
throughout the PostgreSQL documentation.  However, this is not my
decision to make.

Here's some pros & cons, as I see it, for including more examples in
standard docs.

Pros:
* Accomodates different learning styles
* Jump-starts development by providing working code
* Built-in tests for breakage of backward compatibility

Cons:
* Start-up costs re: actually writing & checking the examples
* Bigger document base to update & maintain
* Disk space

What do you all think?

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 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Vacuum Cost Documentation?

[David Fetter]

On Sun, Jul 18, 2004 at 02:32:10AM -0400, Tom Lane wrote:
> "Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
> > Related to autovacuum work, I was looking into the new vacuum
> > delay functionality.  I might be missing something, but I can't
> > find anything on it in the developer docs.  Is that right?
> 
> You are not missing anything.  I already nagged Jan about this
> once...
> 
> Of course, this is hardly the only seriously-underdocumented thing
> in CVS tip ;-).  We'll need to make a major push on docs soon.

Do tell. :)

Cheers,
D(oc writer ordinaire)
-- 
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] NT + deadlock intended behaviour ?

[Alvaro Herrera]

On Sun, Jul 18, 2004 at 11:06:19AM +0200, Gaetano Mendola wrote:
> Alvaro Herrera wrote:
> 
> >Gaetano, please apply the latest savepoints patch (savepoint-5.patch)
> >and let me know how it goes ...
> 
> where is it ?

I just sent it by private mail to you (11kb).  I don't see it in the
archives ...

-- 
Alvaro Herrera ()
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)


---(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] NT + deadlock intended behaviour ?

[Alvaro Herrera]

On Sun, Jul 18, 2004 at 11:00:25AM +0200, Gaetano Mendola wrote:
> Alvaro Herrera wrote:

> >If I abort only the innermost transaction on session 2, the application
> >writer can have a retry loop on it, so it will issue the "begin" again
> >and the same update.  Since session 1 is still locked, session 2 will
> >see a deadlock again.  The user could cope with detecting a deadlock
> >condition and do something else, but frankly I don't think we can leave
> >this as is.
> 
> I understand your point but I don't like the solution of invalidate the 
> whole transaction tree ( I don't know the good one ).

FYI, this is all moot with savepoints because we don't allow to create a
savepoint in an aborted transaction block.

-- 
Alvaro Herrera ()
"El sudor es la mejor cura para un pensamiento enfermo" (Bardia)


---(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] pg_dump bug fixing

[Rod Taylor]

On Sun, 2004-07-18 at 07:42, Christopher Kings-Lynne wrote:
> > I am not sure that is really a bug. If someone really wants less than
> > what is in template1, they should be dropping stuff from template1
> > before recreating the database.
> 
> No, because pg_dump itself dumps template1's contents...

Hmm.

1. Add language handler (say plpgsql) to template1.
2. Create new database
3. Dump database
4. Restore dump, having pg_dump create a new database

Doh.. errors because language handlers are there twice.

You can play this game with tables, sequences, and all sorts of other
things that might be useful in the default template. pg_dump just skips
it (not necessarily bad unless they did an alter table afterward) but
pg_restore doesn't like this at all.


I think what we want is a clean template without all of the extras that
template1 has.

If we dump & restore the public schema and other items that we might be
interested in having, we can have pg_dump use template_clean as a
template for new databases. Template_clean contains none of the things
that pg_dump can dump/restore (like language handlers) but is NOT the
default template.

I've done this myself, removed lots of stuff from template1 after
creating a template_ that is used for creating new DBs
with. This works great when we remember to specify WITH TEMPLATE.



---(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] pg_dump bug fixing

[Bruno Wolff III]

On Sun, Jul 18, 2004 at 19:42:09 +0800,
  Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote:
> >I am not sure that is really a bug. If someone really wants less than
> >what is in template1, they should be dropping stuff from template1
> >before recreating the database.
> 
> No, because pg_dump itself dumps template1's contents...

Are you planning on extending this line of thought to other things in
template1 such as operators?

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

[HACKERS] SSL connection abort screws up logger pipe

[Andreas Pflug]

While testing the logger subprocess, I found that I get a
"could not read from system logger pipe: Too many open files" if I abort 
a SSL connection.

After that, the ereport(FATAL) will screw up the backend, semctl etc (To 
fix this, I'm using ereport(COMMERROR) and exit(1) now, logger will 
restart gracefully).

It appears that the SSL connection cleanup code does some harmful stuff 
to the logger pipe, while the normal cleanup code will leave the pipe 
workable.

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

Re: [HACKERS] pg_dump bug fixing

[Christopher Kings-Lynne]

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.
No, because pg_dump itself dumps template1's contents...
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_dump bug fixing

[Bruno Wolff III]

On Sun, Jul 18, 2004 at 14:33:09 +0800,
  Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote:
> 
> * If you drop your public schema, a drop command is not issued for it in 
> the dump, so when you restore your public schema is back

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.

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

Re: [HACKERS] NT + deadlock intended behaviour ?

[Gaetano Mendola]

Alvaro Herrera wrote:

Gaetano, please apply the latest savepoints patch (savepoint-5.patch)
and let me know how it goes ...
where is it ?

Regards
Gaetano Mendola



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

Re: [HACKERS] NT + deadlock intended behaviour ?

[Gaetano Mendola]

Alvaro Herrera wrote:
On Sun, Jul 18, 2004 at 01:06:39AM +0200, Gaetano Mendola wrote:

I'm doing some experiments with NT, I din't expect this behaviuor:

First of all, let me point that the behavior on deadlock has been agreed
to change.  Instead of only aborting the innermost transaction, it will
abort the whole transaction tree.
The reason is simple.  Consider this case:
create table foo (a int);
insert into test values (1);
insert into test values (2);
begin;
 update foo set a=20 where a=1;
begin;
 update foo set a=21 where a=2;
 begin;
  update foo set a=22 where a=2;
 begin;
   update foo set a=23 where a=1;
   
If I abort only the innermost transaction on session 2, the application
writer can have a retry loop on it, so it will issue the "begin" again
and the same update.  Since session 1 is still locked, session 2 will
see a deadlock again.  The user could cope with detecting a deadlock
condition and do something else, but frankly I don't think we can leave
this as is.
I understand your point but I don't like the solution of invalidate the whole
transaction tree ( I don't know the good one ).
See also my comment at the end of this reply.

SESSION 1;SESSION 2;
begin;  begin;
update test set a = 300 where a = 3;  update test set a = 40 where a = 4;
~  begin;
update test set a = 400 where a = 4;

~  update test set a = 30 where a = 3;
~  
~  commit;
<-- !?!?!
~  
why SESSION 1 was unblocked?

Because when you COMMIT a subtransaction that was in aborted state, the
parent is aborted too.  So when you COMMIT you are not really
committing, you are aborting.  That gives session 1 green light to
continue, because session 2 has released all locks.
So why the second commit on SESSION 2 works without complain about the fact
that there is no transaction active to commit ?
I think the first commit have to fail because the transaction is aborted
( I know this was discussed before ).

If I repeat again but I do an abort:
SESSION 1;SESSION 2;
begin;  begin;
update test set a = 300 where a = 3;  update test set a = 40 where a = 4;
~  begin;
update test set a = 400 where a = 4;

~  update test set a = 30 where a = 3;
~  
~  abort;


This is what you expected, wasn't it?  When you ABORTed the
subtransaction, the parent did not abort, so it held it locks.  So
session 1 does not have the lock it needs.
This is what I was expecting; here we are in the same situation of your example,
what happen if the application open another transaction and try to update the
same row ?

Regards
Gaetano Mendola








---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [HACKERS] PITR COPY Failure (was Point in Time Recovery)

[Mark Kirkwood]

fixed.
Mark Kirkwood wrote:
There are some silly bugs in the script:
- forgot to export PGDATA and PATH after changing them
- forgot to mention the need to edit test.sql (COPY line needs path to 
dump file)

Apologies - I will submit a fixed version a little later
regards
Mark
Mark Kirkwood wrote:
A script to run the whole business can be found here :
http://homepages.paradise.net.nz/markir/download/pitr-bug.tar.gz
(It will need a bit of editing for things like location of Pg, 
PGDATA, and you will need to make your own data file)


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