Re: [HACKERS] ECPG regression failures on OpenBSD

[Chris Mair]

> >> After upgrading some of my buildfarm members to the latest version of
> >> the buildfarm script both OpenBSD boxes startet to fail the ECPG
> >> regression tests:
> >> ...
> > 
> > complex/test2 gets a segmentation fault on both machines. Could you try
> > running it under gdb to see where it segfaults? I will try to get a hand
> > on an OpenBSD machine myself, too
> 
> will try to get a backtrace soon - if you are trying to do your own 
> testing keep in mind that both boxes of mine do run with special 
> malloc-settings (as in FGJZ) as discussed in:
> 
> http://archives.postgresql.org/pgsql-hackers/2005-06/msg00817.php
> 
> while I'm not sure yet that those are causing the errors to show up it 
> seems quite likely since they tend to catch hidden memory allocation errors.

Hi,

I've just upgraded guppy, the only other OpenBSD machine that builds
head to the latest buildfarm version.

I don't have any special malloc settings. When guppy finishes, we will
see what happens.

Bye, Chris.


-- 

Chris Mair
http://www.1006.org


---(end of broadcast)---
TIP 1: 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] gBorg status?

[Chris Browne]

[EMAIL PROTECTED] ("Magnus Hagander") writes:
>> Also people trying to download slony have to do some hunting 
>> to find things.  The source only tar is not available on pgfoundry.
>
> All gborg *downloads* are available on:
> http://www.postgresql.org/ftp/projects/gborg/
>
> Seems Slony hasn't released files using the gborg file release system,
> perhaps? Because for some reason Slony stuff isn't there. But I figured
> it'd be a good idea t oget that pointer in for people looking for
> anything else off gborg that didn't know we mirrored those.

In the past, binaries got hosted on Jan Wieck's "downloads area" which
probably lives somewhere nearby there.  Apparently it wasn't terribly
convenient to add/drop files from the gBorg downloads area.

For 1.2, I have been putting release candidate files over at
pgFoundry, which is certainly still accessible.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/finances.html
"Recursion is the root of computation since it trades description for time."
-- Alan J. Perlis

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

   http://www.postgresql.org/docs/faq

[HACKERS] Slony-I 1.1.5 binaries at pgFoundry.org

[Chris Browne]

[EMAIL PROTECTED] (elein) writes:
> Also people trying to download slony have to do some
> hunting to find things.  The source only tar is not
> available on pgfoundry.

The source tarball for version 1.1.5 is now in place:
  

We may as well have at least that bit of backup.

I didn't bother putting up the documentation tarball; it is better to
grab a newer version of the docs.
-- 
"cbbrowne","@","ntlug.org"
http://cbbrowne.com/info/slony.html
You can lead a horse to water, but if you can get him to swim on his
back, you've got something.

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path changes.

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> Chris Browne <[EMAIL PROTECTED]> writes:
>> If I touch preproc.y and pgc.l, the .c files get regenerated, and all
>> is well.
>
>> If I don't, they get left alone, and I see compilation errors.
>
>> It seems to me you need to rebuild the C files and commit them.
>
> No, because those derived files are not in CVS at all.  What you
> are describing sounds to me like a clock skew problem.  Is your
> machine's system clock showing the correct date?

Odd, odd.  NOT a clock problem.  The .c files were sitting in my
buildfarm's CVS repository for HEAD.  And yes, indeed, the derived
files shouldn't have been there at all.  I'm not quite sure how they
got there in the first place.

At any rate, after comprehensively looking for yacc-derived files,
that clears this problem, as well as regression failures with last
night's commit of COPY (SELECT) TO, which is no bad thing.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/linux.html
Rules of the Evil Overlord #155. "If I know of any heroes in the land,
I will not under any circumstance kill their mentors, teachers, and/or
best friends."  <http://www.eviloverlord.com/>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[HACKERS] gBorg status?

[Chris Browne]

What's up there?  It has been down all week.

We're trying to get the Slony-I 1.2 release out, so we can then
migrate over to pgFoundry.  But that doesn't working terribly well
when gBorg's down...
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/emacs.html
"...Yet terrible as Unix addiction  is, there are worse fates. If Unix
is the heroin of operating systems, then VMS is barbiturate addiction,
the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your
sinuses  with  lucite and  letting  it set.)   You  owe  the Oracle  a
twelve-step program."  --The Usenet Oracle

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

Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path changes.

[Chris Browne]

[EMAIL PROTECTED] (Michael Meskes) writes:
> On Tue, Aug 29, 2006 at 03:35:14PM -0500, Jaime Casanova wrote:
>> >Ah.  So this would have caused a bunch of problems in compiling
>> >src/interfaces/ecpg/test/connect/test1.pgc???
>
> Not the compilation errors I would think.
>
>> i'm seeing this error when compiling HEAD (updated at ago 29 15:16)
>> ...
>
> This looks like you're using an old version of the parser. preproc.y was
> changed to handle empty database names and the the error you report is
> due to an empty db name.

I think the problem is that the latest version of preproc.c isn't
based on that version of preproc.y (or perhaps similarly with pgc.l/pgc.c).

If I touch preproc.y and pgc.l, the .c files get regenerated, and all
is well.

If I don't, they get left alone, and I see compilation errors.

It seems to me you need to rebuild the C files and commit them.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/lsf.html
When I die, I'd like to go peacefully in my sleep like my grandfather,
not screaming in terror like his passengers...

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] [PATCHES] updated patch for selecting large results

[Chris Mair]

On Tue, 2006-08-29 at 18:31 -0400, Tom Lane wrote:
> <[EMAIL PROTECTED]> writes:
> > here comes the latest version (version 7) of the patch to handle large
> > result sets with psql.  As previously discussed, a cursor is used
> > for SELECT queries when \set FETCH_COUNT some_value > 0
> 
> Applied with revisions ... I didn't like the fact that the code was
> restricted to handle only unaligned output format, so I fixed print.c
> to be able to deal with emitting output in sections.  This is not
> ideal for aligned output mode, because we compute column widths
> separately for each FETCH group, but all the other output modes work
> nicely.  I also did a little hacking to make \timing and pager output
> work as expected.
> 
>   regards, tom lane

Cool!
I specially like that as a side effect of your work for applying this,
psql is faster now.

Thanks to all people that helped with this (lots...:)

Bye, Chris.



-- 

Chris Mair
http://www.1006.org


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

Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path changes.

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> [EMAIL PROTECTED] (Michael Meskes) writes:
>> Second try committing the path changes.
>
> Ah, this looks better.  I get clean passes on both HPPA in-tree and
> Fedora x86_64 VPATH builds, so I think you've finally fixed all the
> issues.  Congrats!

Ah.  So this would have caused a bunch of problems in compiling
src/interfaces/ecpg/test/connect/test1.pgc???

I'm not sure I'm seeing it in CVS; perhaps this waits some time before
getting completely public?
-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/languages.html
"Unfortunately, because the wicked sorcerers of Silikonn' Vahlli hated
freedom, they devised  clever signs and wonders to  achieve the mighty
Captive User Interface, also known as the Prison for Idiot Minds."
-- Michael Peck <[EMAIL PROTECTED]>

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

Re: [HACKERS] [PATCHES] updated patch for selecting large results sets in

[Chris Mair]

> > > I am confused.  I assume \g and ; should be affected, like Peter says. 
> > > Tom, what *every* command are you talking about?  You mean \d?
> > 
> > Like I said, I thought we were intending to modify \g's behavior only;
> > that was certainly the implication of the discussion of "\gc".

At some point you OKed the "\g and ;" proposal.
I admit I was quick adding the "and ;" part, but it seemed so natural
once we agreed on using a variable.


> OK, got it.  I just don't see the value to doing \g and not ;. I think
> the \gc case was a hack when he didn't have \set.  Now that we have
> \set, we should be consistent.

I agree with this statement.

If we have a variable that switches just between two versions of \g,
we could have gone with using \u (or whatever) in the first place.

In the mean time I have been converted by the variable camp, and
I think the variable should change "\g" and ";" together, consistently.

If we find we can't live with the performance overhead of that
if(FETCH_COUNT), it is still not clear why we would be better
off moving it into the \g code path only.

Is it because presumably \g is used less often in existing psql scripts?

Bye, Chris.



-- 

Chris Mair
http://www.1006.org



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

Re: [HACKERS] [PATCHES] updated patch for selecting large results

[Chris Mair]

> > The conclusion is that, yes, the overhead is measurable, albeit with
> > a very synthetic benchmark (of the type MySQL wins ;).
> 
> OK, so about 3 or 4% overhead added to extremely short queries.

More accurately, that 3 or 4% overhead is added to about all queries
(we're talking *psql*-only running time).

It's just that for anything but short queries, psql running time
totally dwarfs regarding to postmaster running time anyway.

> That's not enough to kill this patch, but it's still annoying ...
> and as I mentioned, there are some existing calls of GetVariable
> that are executed often enough to be a problem too.
> 
> It strikes me that having to do GetVariable *and* strtol and so on
> for these special variables is pretty silly; the work should be done
> during the infrequent times they are set, rather than the frequent
> times they are read.  I propose that we add the equivalent of a GUC
> assign_hook to psql's variable facility, attach an assign hook function
> to each special variable, and have the assign hook transpose the
> value into an internal variable that can be read with no overhead.
> If we do that then the cost of the FETCH_COUNT patch will be
> unmeasurable, and I think we'll see a few percent savings overall in
> psql runtime from improving the existing hotspot uses of GetVariable.
> 
> Barring objections, I'll hack on this this evening ...

Might help.

Take into account the strtol is not critical at all for FETCH_COUNT,
since when it's actually set, we're supposed to retrieving big data
where a strtol doesn't matter anyway. The overhead comes from scanning
the linked list for nothing in the normal case (when it's not set).

I don't know how the other uses factor in here, but I see it's called
at least twice more on average calls to SendQuery.

Bye,
Chris.



-- 

Chris Mair
http://www.1006.org



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

Re: [HACKERS] [PATCHES] updated patch for selecting large results

[Chris Mair]

> Performance-wise I took for granted without checking that GetVariable's
> running time would be negligible.
> 
> [looks at the code]
> 
> I see it's it's basically two function calls with a loop over a linked
> list of values (in the order of 10) doing strcmps and one strtol.
> It is not quite clear to me what the impact of this is. I could
> imagine it would show up only if you perform lots of trivial queries
> through psql. I'm going to benchmark something now and report back.
> 
> Anyway, regardless the benchmark, I feel it's somehow not clean to have
> a variable introduce a difference between "\g" and ";".
> 
> [goes benchmarking...]

Ok,
so I ran a file containing 1 million lines of "select 1;" through
psql (discarding the output). 5 runs each with the patch and with the
patch removed (the if() in SendQuery commented).

These are the results in seconds user time of psql on a Pentium M 2.0
GHz (real time was longer, since the postmaster process was on the same
machine).

 patch | count |   avg   |  stddev
---+---+-+---
 f | 5 | 16.6722 | 0.359759919946455
 t | 5 | 17.2762 | 0.259528803796329

The conclusion is that, yes, the overhead is measurable, albeit with
a very synthetic benchmark (of the type MySQL wins ;).

Basically I'm loosing 0.6 usec on each query line (when FETCH_COUNT
is not there and therefore psql need to scan the whole variables list
in GetVariable() for nothing).

Not sure if that's acceptable (I'd say yes, but then again, I'm
not a cycle counter type of programmer *cough* Java *cough* ;)...

Opinions?

Bye, Chris.


-- 

Chris Mair
http://www.1006.org



---(end of broadcast)---
TIP 1: 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] [PATCHES] updated patch for selecting large results sets in

[Chris Mair]

On Mon, 2006-08-28 at 13:45 -0400, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> > > Wait a minute.  What I thought we had agreed to was a patch to make
> > > commands sent with \g use a cursor.  This patch changes SendQuery
> > > so that *every* command executed via psql is treated this way.
> 
> > That's what I remembered.  I don't think we want to introduce a 
> > difference between ; and \g.
> 
> Have we measured the performance impact, then?  The last time I profiled
> psql, GetVariable was already a hotspot, and this introduces another
> call of it into the basic query loop whether you use the feature or not.
> 
>   regards, tom lane

Hi,

after agreeing on using a \set variable, I proposed to have it influence
"\g" as well as ";", because I thought that would be the most expected
behaviour. IMHO I'm with Peter, that introducing a difference between
"\g" and ";" would go against the principle of least surprise.

Performance-wise I took for granted without checking that GetVariable's
running time would be negligible.

[looks at the code]

I see it's it's basically two function calls with a loop over a linked
list of values (in the order of 10) doing strcmps and one strtol.
It is not quite clear to me what the impact of this is. I could
imagine it would show up only if you perform lots of trivial queries
through psql. I'm going to benchmark something now and report back.

Anyway, regardless the benchmark, I feel it's somehow not clean to have
a variable introduce a difference between "\g" and ";".

[goes benchmarking...]

Bye, Chris.


-- 

Chris Mair
http://www.1006.org



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

Re: [HACKERS] Replication

[Chris Browne]

[EMAIL PROTECTED] (Jeff Davis) writes:
> On Wed, 2006-08-23 at 13:36 +0200, Markus Schiltknecht wrote:
>> Hannu Krosing wrote:
>> > But if you have very few writes, then there seems no reason to do sync
>> > anyway.
>> 
>> I think there is one: high-availability. A standby-server which can 
>> continue if your primary fails. Of course sync is only needed if you 
>> absolutely cannot effort loosing any committed transaction.
>> 
>
> I disagree about high-availability. In fact, I would say that sync
> replication is trading availability and performance for synchronization
> (which is a valid tradeoff, but costly). 
>
> If you have an async system, all nodes must go down for the system to go
> down.
>
> If you have a sync system, if any node goes down the system goes down.
> If you plan on doing failover, consider this: what if it's not obvious
> which system is still up? What if the network route between the two
> systems goes down (or just becomes too slow to replicate over), but
> clients can still connect to both servers? Then you have two systems
> that both think that the other system went down, and both start
> accepting transactions. Now you no longer have replication at all.

That is why for multimaster, there's a need for both automatic policy
as well as some human intervention.

- You need an automatic determination of "quorum", where, to be safe,
  it is only permissible for a set of $m$ servers to believe themselves
  to be active if they number more than 1/2 of the total of expected
  servers.

  Thus, if there are 13 servers in the cluster, then "quorum" is 7
  servers.

  If a set of 6 servers get cut off from the rest of the network, they
  don't number at least 7, and thus know that they can't represent a
  quorum.

- And if conditions change, a human may need to change the quorum
  number.

  If 4 new nodes get added, quorum moves up to 9.

  If 5 nodes get dropped, quorum moves down to 5.

Deciding when to throw a node out of the quorum because it is
responding too slowly is still not completely trivial, but having a
quorum policy does address your issue.
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/linux.html
"Be humble.   A lot happened before  you were born."   - Life's Little
Instruction Book

---(end of broadcast)---
TIP 1: 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] BugTracker

[Chris Browne]

[EMAIL PROTECTED] (Peter Eisentraut) writes:
> Am Mittwoch, 16. August 2006 14:10 schrieb Robert Treat:
>> I'm not sure I follow this, since currently anyone can email the bugs list
>> or use the bugs -> email form from the website.  Are you looking to
>> increase the barrier for bug reporting?
>
> Only a small fraction of the new posts on pgsql-bugs are actually
> bugs.  Most are confused or misdirected users.  I don't want to
> raise that barrier.  But I want a higher barrier before something is
> recorded in the bug tracking system.

Seems to me that for there to be a *bit* of a barrier might not be a
bad thing...

If "purported bugs" had to be acknowledged before going into the bug
tracker system, that wouldn't seem a bad thing.

That would mean that the frequent "I don't understand what I'm doing
and didn't read the documentation" reports could be quickly triaged
away, which strikes me as an important prerequisite for further
automating things.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/sap.html
FLORIDA: Relax, Retire, Re Vote.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] [PATCHES] selecting large result sets in psql using

[Chris Mair]

> > >> Since buffer commands all have a single char I wanted a single char one
> > >> too. The "c" for "cursor" was taken already, so i choose the "u" (second
> > >> char in "cursor"). If somebody has a better suggestion, let us know ;)
> > 
> > > I think a new backslash variable isn't the way to go.  I would use a
> > > \pset variable to control what is happening.
> > 
> > That seems like it would be very awkward to use: you'd have to type
> > quite a bit to go from one mode to the other.
> > 
> > Personally I think that insisting on a one-letter command name is not
> > such a good idea if you can't pick a reasonably memorable name.
> > I'd suggest "\gc" (\g with a Cursor) or "\gb" (\g for a Big query)
> > or some such.

\gc sounds like a good idea to me :)

(I must admit gc reminds me about 'garbage collector', which in a weired
way is related with what we're doing here... At least more related than
'Great Britain' ;)


> So add it as a modifyer to \g?  Yea, that works, but it doesn't work for
> ';' as a 'go' command, of course, which is perhaps OK.

Yes, it was intended to differentiate this command from ';';

Bye, Chris.


-- 

Chris Mair
http://www.1006.org



---(end of broadcast)---
TIP 1: 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] [PATCHES] selecting large result sets in psql using

[Chris Mair]

> > > BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
> > > some other name we could use?
> > 
> > True :)
> > Since buffer commands all have a single char I wanted a single char one
> > too. The "c" for "cursor" was taken already, so i choose the "u" (second
> > char in "cursor"). If somebody has a better suggestion, let us know ;)
> 
> I think a new backslash variable isn't the way to go.  I would use a
> \pset variable to control what is happening.

IMHO with \pset I'd have different places where I'd need to figure
out whether to do the cursor thing and I was a bit reluctant to add
stuff to existing code paths. Also the other \pset options are somewhat
orthogonal to this one. Just my two EUR cents, of course... :)


Bye, Chris.


-- 

Chris Mair
http://www.1006.org



---(end of broadcast)---
TIP 1: 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] [PATCHES] selecting large result sets in psql using

[Chris Mair]

> > Patch with fix against current CVS is attached.

Forgot the attachment... soory.


-- 

Chris Mair
http://www.1006.org

diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml	2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml	2006-08-17 18:02:29.0 +0200
***
*** 1201,1206 
--- 1201,1231 
  

  
+ 
+   
+ \u [ { filename | |command } ]
+ 
+ 
+ 
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in filename or pipes the output
+ into a separate Unix shell executing command.
+ Unlike \g, \u works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore \u uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exceeds the client's memory resources.
+ \u always gives unaligned output. It does, however
+ use the current field separator (see \pset).
+ \u gives an error when trying to execute something
+ that is not a SELECT statement.
+ 
+ 
+   
+ 
+ 

  \help (or \h) [ command ]
  
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c	2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c	2006-08-17 18:02:49.0 +0200
***
*** 830,835 
--- 830,866 
  		}
  	}
  
+ 	/*
+ 	 *  \u executes the current query buffer using a cursor
+ 	 */
+ 	else if (strcmp(cmd, "u") == 0)
+ 	{
+ 		char 	   *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+ 
+ 		if (!fname)
+ 			pset.gfname = NULL;
+ 		else
+ 		{
+ 			expand_tilde(&fname);
+ 			pset.gfname = pg_strdup(fname);
+ 		}
+ 		free(fname);
+ 
+ 		if (query_buf && query_buf->len == 0)
+ 		{
+ 			if (!quiet)
+ 			{
+ puts(_("Query buffer is empty."));
+ fflush(stdout);
+ 			}
+ 		}
+ 		else
+ 		{
+ 			status = PSQL_CMD_SEND_USING_CURSOR;
+ 		}
+ 	}
+ 
  	/* \unset */
  	else if (strcmp(cmd, "unset") == 0)
  	{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h	2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h	2006-08-17 16:55:25.0 +0200
***
*** 16,21 
--- 16,22 
  {
  	PSQL_CMD_UNKNOWN = 0,			/* not done parsing yet (internal only) */
  	PSQL_CMD_SEND,	/* query complete; send off */
+ 	PSQL_CMD_SEND_USING_CURSOR,		/* query complete; send off using cursor */
  	PSQL_CMD_SKIP_LINE,/* keep building query */
  	PSQL_CMD_TERMINATE,/* quit program */
  	PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c	2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/common.c	2006-08-17 18:40:51.0 +0200
***
*** 28,33 
--- 28,34 
  #include "command.h"
  #include "copy.h"
  #include "mb/pg_wchar.h"
+ #include "mbprint.h"
  
  
  /* Workarounds for Windows */
***
*** 52,58 
  	 ((T)->millitm - (U)->millitm))
  #endif
  
! 
  static bool command_no_begin(const char *query);
  
  /*
--- 53,59 
  	 ((T)->millitm - (U)->millitm))
  #endif
  
! static bool is_select_command(const char *query);
  static bool command_no_begin(const char *query);
  
  /*
***
*** 952,957 
--- 953,1146 
  
  
  /*
+  * SendQueryUsingCursor: send the (SELECT) query string to the backend
+  * using a cursor and print out results.
+  *
+  * Unlike with SendQuery(), single step mode, ON_ERROR_ROLLBACK mode,
+  * timing and format settings (except delimiters) are NOT honoured.
+  *
+  * Returns true if the query executed successfully, false otherwise.
+  */
+ bool
+ SendQueryUsingCursor(const char *query)
+ {
+ 	PGresult		*results;
+ 	bool			started_txn			= false;
+ 	PQExpBufferData	buf;
+ 	FILE			*queryFout_copy 	= NULL;
+ 	bool			queryFoutPipe_copy	= false;
+ 	intntuples, nfields = -1;
+ 	inti, j;
+ 
+ 	if (!pset.db)
+ 	{
+ 		psql_error("You are currently not connected to a database.\n");
+ 		return false;
+ 	}
+ 
+ 	if (!is_select_command(query))
+ 	{
+ 		psql_error("Need a SELECT command to perform cursor fetch.\n");
+ 		return false;
+ 	}
+ 
+ 	if (VariableEquals(pset.vars, "ECHO", "queries"))
+ 	{
+ 		puts(query);
+ 		fflush(stdout);
+ 	}
+ 
+ 	if (pset.logfile)
+ 	{
+ 		fprintf(pset.logfile,
+ _("* QUERY **\n"
+   "%s\n"
+   "

Re: [HACKERS] [PATCHES] selecting large result sets in psql using

[Chris Mair]

Replying to myself...

> Patch with fix against current CVS is attached.

Alvaro Herrera sent two fixes off-list: a typo and
at the end of SendQueryUsingCursor I sould COMMIT, not ROLLBACK.

So, one more version (6) that fixes these too is attached.

Bye, Chris.

PS: I'm keeping this on both lists now, hope it's ok.


-- 
Chris Mair
http://www.1006.org



---(end of broadcast)---
TIP 1: 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] [PATCHES] selecting large result sets in psql using cursors

[Chris Mair]

Hi,

thanks for reviewing this :)

> > attached is the new and fixed version of the patch for selecting
> > large result sets from psql using cursors.
> 
> The is_select_command bit is wrong because it doesn't allow for left
> parentheses in front of the SELECT keyword (something entirely
> reasonable when considering big union/intersect/except trees).
> Also you'd need to allow for VALUES as the first keyword.

You're right, I improved is_select_command to take these into account.
(Btw, I didn't even know a command VALUES existed..)


> But isn't the whole thing unnecessary?  ISTM you could just ship the
> query with the DECLARE CURSOR prepended, and see whether you get a
> syntax error or not.

I find it neat that \u gives a good error message if someone
executes a non-select query. If I leave that out there is no way to tell
a real syntax error from one cause by executing non-selects...

Anyway, if we don't want the extra check, I can skip the
is_select_command call, of course.

Patch with fix against current CVS is attached.


> At some point we ought to extend libpq enough to expose the V3-protocol
> feature that allows partial fetches from portals; that would be a
> cleaner way to implement this feature.  However since nobody has yet
> proposed a good API for this in libpq, I don't object to implementing
> \u with DECLARE CURSOR for now.
> 
> BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
> some other name we could use?

True :)
Since buffer commands all have a single char I wanted a single char one
too. The "c" for "cursor" was taken already, so i choose the "u" (second
char in "cursor"). If somebody has a better suggestion, let us know ;)

Bye, Chris.

PS: I'm traveling Fri 18th - Fri 25th and won't check mail often.


-- 

Chris Mair
http://www.1006.org

diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml	2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml	2006-08-17 16:54:41.0 +0200
***
*** 1201,1206 
--- 1201,1231 
  

  
+ 
+   
+ \u [ { filename | |command } ]
+ 
+ 
+ 
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in filename or pipes the output
+ into a separate Unix shell executing command.
+ Unlike \g, \u works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore \u uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exeeds the client's memory resources.
+ \u always gives unaligned output. It does, however
+ use the current field separator (see \pset).
+ \u gives an error when trying to execute something
+ that is not a SELECT statement.
+ 
+ 
+   
+ 
+ 

  \help (or \h) [ command ]
  
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c	2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c	2006-08-17 16:55:25.0 +0200
***
*** 830,835 
--- 830,866 
  		}
  	}
  
+ 	/*
+ 	 *  \u executes the current query buffer using a cursor
+ 	 */
+ 	else if (strcmp(cmd, "u") == 0)
+ 	{
+ 		char 	   *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+ 
+ 		if (!fname)
+ 			pset.gfname = NULL;
+ 		else
+ 		{
+ 			expand_tilde(&fname);
+ 			pset.gfname = pg_strdup(fname);
+ 		}
+ 		free(fname);
+ 
+ 		if (query_buf && query_buf->len == 0)
+ 		{
+ 			if (!quiet)
+ 			{
+ puts(_("Query buffer is empty."));
+ fflush(stdout);
+ 			}
+ 		}
+ 		else
+ 		{
+ 			status = PSQL_CMD_SEND_USING_CURSOR;
+ 		}
+ 	}
+ 
  	/* \unset */
  	else if (strcmp(cmd, "unset") == 0)
  	{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h	2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h	2006-08-17 16:55:25.0 +0200
***
*** 16,21 
--- 16,22 
  {
  	PSQL_CMD_UNKNOWN = 0,			/* not done parsing yet (internal only) */
  	PSQL_CMD_SEND,	/* query complete; send off */
+ 	PSQL_CMD_SEND_USING_CURSOR,		/* query complete; send off using cursor */
  	PSQL_CMD_SKIP_LINE,/* keep building query */
  	PSQL_CMD_TERMINATE,/* quit program */
  	PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c	2006-08-17 16:51:04.0 +0200

Re: [HACKERS] snapshot replication with pg_dump

[Chris Browne]

[EMAIL PROTECTED] (Paul Silveira) writes:
> Does anyone have any good examples of implementing "snapshot"
> replication. I know that PostgreSQL does not have snapshot
> replication and that Slony-I is the recomended replication senario
> but I've configured it and it seems rather advanced for a shop that
> is implementing PostgreSQL for the first time.  I have an
> application that will be mostly reads and snapshot replication would
> probably be simple enough and would work.  I was thinking about just
> using pg_dump to do the trick because the DB should not get very
> large.  Does anyone have any advanced examples of doing something
> like this? Also, does anyone have any comments they'd like to share
> about this...

If your database is small, and your needs simple, then using pg_dump
to generate "snapshots" is a perfectly reasonable idea.

I suppose the primary complication is whether or not you have multiple
databases around on the cluster...  If you don't, or if they all need
to be "snapshotted," you might consider using pg_dumpall, which also
creates users and databases.

If pg_dumpall is unsuitable, then you'll still need to grab user
information that isn't part of pg_dump output...
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/postgresql.html
"This .signature is  shareware.  Send in $20 for  the fully registered
version..."

---(end of broadcast)---
TIP 1: 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] 8.2 features status

[Chris Browne]

[EMAIL PROTECTED] (Bruce Momjian) writes:
> Christopher Browne wrote:
>> >> Make postmater and postgres options distinct so the postmaster -o
>> >> option is no longer needed | Alvaro | Confirmed | 09/20/06
>> >> 
>> >> Notice the sequence of events. I am not saying the specific statuses are 
>> >> the way to go but it would give a simple way to keep tabs on things 
>> >> without having to create a whole new ball of yarn.
>> >
>> > Interesting idea.  If people willing to state they will complete items
>> > for the next release, I can add this to the TODO list, and just remove
>> > it once the item is in CVS.
>> 
>> Is it forcibly necessary to have that commitment in order for this to
>> be of some use?
>> 
>> It seems to me that this would be a reasonably useful way of tracking
>> the progress of TODO items irrespective of any particular commitment
>> to completion in sync with a version.
>
> The problem comes with someone starting to work on something, then
> giving up, but if you record it, people think they are still working on
> it.

If there is some form of "last updated on" date, that seems to me to
be quite sufficient for the purpose.  If the person last working on it
hasn't reported any new news on the item in some substantial period of
time, that's a good implicit indication that something is stalled.

> What happens now is that someone says they want to work on X, and
> the community tells them that Y might be working on it, and Y gives
> us a status.

If what we see in the todo is...

Implement hierarchical queries using ANSI "WITH"/recursive query
system | Someone | Under way | [some date six months ago]

... then those that are interested in seeing this go in can probably
guess that the effort has stalled in that nothing has been worth
commenting on in six months.

This sort of thing is suggestive of having some sort of systematic way
to store structured information.  Perhaps one could implement some
sort of database for it...  :-)
-- 
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/sgml.html
Rules  of the  Evil  Overlord #21.  "I  will hire  a talented  fashion
designer  to create  original uniforms  for my  Legions of  Terror, as
opposed  to  some cheap  knock-offs  that  make  them look  like  Nazi
stormtroopers, Roman  footsoldiers, or savage Mongol  hordes. All were
eventually  defeated and  I want  my troops  to have  a  more positive
mind-set." 

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

Re: [HACKERS] 8.2 feature set

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>> Well if an initdb was not required, I think that would be a huge feature 
>> ;) (I know it may not work release over release)
>
> If someone had started working on pg_upgrade six months ago, we might
> have that for 8.2 ...

Someone brought the absence of that up in a LUG context this week, so
it's certainly the sort of feature that would be worth making
prominent...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/spreadsheets.html
"I think you ought to know I'm feeling very depressed"
-- Marvin the Paranoid Android

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[HACKERS] OSCON fallout - Compressed Annealing optimizer

[Chris Browne]

Robert Hansen did a talk at OSCON on a compressed annealing framework
called Djinni: 

It's a framework to use compressed annealing (a derivative of
simulated annealing) for finding approximate solutions to NP-complete
problems such as the TSP with time windows.  Note that while Djinni is
implemented in C++, it already supports embedding via SWIG, and has a
C wrapper and is accessible from other languages.  And it's
BSD-licensed...

This has the potential to be an alternative to the present use of GEQO
for query optimization for cases of large joins (e.g. - involving
large numbers of tables).

On the one hand, I'm somewhat suited to looking at this in that I have
the operations research background to know what they're talking about.
On the other hand, I'm not familiar with the optimizer, nor do I deal
with sorts of cases with so many joins that this would be worthwhile.

I've bounced a ToDo entry over to Bruce, and am making sure this is
documented here on pgsql.hackers so that a web search can readily find
it in the future...
-- 
"cbbrowne","@","cbbrowne.com"
http://www3.sympatico.ca/cbbrowne/
"It is far from complete, but it  should explain enough that you don't
just stare at your sendmail.cf file like a deer staring at an oncoming
truck."  -- David Charlap

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Postgres Process in Kernel Mode?

[Chris Browne]

[EMAIL PROTECTED] ("moises") writes:

>  xmlns:w="urn:schemas-microsoft-com:office:word" 
> xmlns="http://www.w3.org/TR/REC-html40";>
>
> 
> 
> 
> 
> 
> 
>
> 
>
> 
>
> 
>
> Hello,
>
>  
>
> I’m new in postgres SQL and I have some
> questions about the space where postgres process 
> run.
>
>  
>
>  
>
> 1-Can any body say me what libs use postgres for 
> make
> system calls, for example LIBC? 

You can easily determine this yourself using ldd.  The answer will
vary depending on what options you use when compiling it.

> 2-Can any body talk me if some postgres process can
> run in Linux kernel space? 

Yes, it doesn't.

> 3- Some body knows if exist some projects that 
> ports postgres
> process on Linux kernel space.
>
>  
>
>  
>
> I was read some source code of postgres, like, 
> 
>
> Postgres.C and 
> others…
>
>  
>
> I was found C instructions like Printf, 
> that’s prohibitive
> for Linux kernel applications, for example in kernel mode we use 
> printk.
>
>  
>
> I suppose that postgres are ported in a user space
> only. I’m Ok?

That's correct.

>  
>
> Thanks 
>
> Moises
>
>  
>
>  
>
>  
>
> - 
>
> 
>
> 
>
> 

You might want to consider using an email client that doesn't slobber:
   
around everywhere.

It's really irritating to have to read around that "deteriorata."
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/advocacy.html
A cool feature of OOP is that the simplest examples are 500 lines.  
-- Peter Sestoft

---(end of broadcast)---
TIP 1: 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] Connection limit and Superuser

[Chris Browne]

[EMAIL PROTECTED] (Andrew Dunstan) writes:
> Joshua D. Drake wrote:
>
>>
>>>
>>> As a protection against malice, yes. I think Rod was more
>>> interested in some protection against stupidity.
>>>
>>> Maybe the real answer is that Slony should connect as a
>>> non-superuser and call security definer functions for the
>>> privileged things it needs to do.
>>
>>
>> Wouldn't that break Slony's ability to connect to older postgresql
>> versions and replicate?
>>
>
> I don't know anything of Slony's internals, but I don't see why older
> versions should matter - Postgres has had security definer functions
> for every release that Slony supports. Maybe I'm missing something ...

Most of Slony-I's activities don't require superuser access.  The
usual thing that's running are SYNC events, and those merely require
write access to some internal Slony-I tables and write access to the
replicated tables on the subscribers.

The functions that do need superuser access are (basically)
 - subscribe set (needs to alter system tables)
 - execute script (ditto)

The trouble is that you in effect need to have that superuser up and
ready for action at any time in case it's needed, and it being that
needful, we basically use it all the time.

Perhaps it's worth looking at shoving the superuser stuff into
SECURITY DEFINER functions; that may be worth considering
post-1.2.0...
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/multiplexor.html
Wow!  Windows  now can do  everything using shared library  DLLs, just
like Multics  did back in  the 1960s!  Maybe someday  they'll discover
separate processes and pipes, which came out in the 1970s!

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] The vacuum-ignore-vacuum patch

[Chris Browne]

[EMAIL PROTECTED] ("Jim C. Nasby") writes:
> There are other transactions to consider: user transactions that will
> run a long time, but only hit a limited number of relations. These are
> as big a problem in an OLTP environment as vacuum is.
>
> Rather than coming up with machinery that will special-case vacuum or
> pg_dump, etc., I'd suggest thinking about a generic framework that would
> work for any long-runnnig transaction. One possibility:
>
> Transaction flags itself as 'long-running' and provides a list of
> exactly what relations it will be touching.
>
> That list is stored someplace a future vacuum can get at.
>
> The transaction runs, with additional checks that ensure it will not
> touch any relations that aren't in the list it provided. 

One thought that's a bit different...

How about we mark transactions that are in serializable mode?  That
would merely be a flag...

We would know that, for each such transaction, we could treat all
tuples "deadified" after those transactions as being dead and
cleanable.

That doesn't require any knowledge of relations that are
touched/locked...
-- 
"cbbrowne","@","cbbrowne.com"
http://www.ntlug.org/~cbbrowne/nonrdbms.html
To err is human, to moo bovine. 

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

Re: [HACKERS] Help! - Slony-I - saving/setting/restoring GUC

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> Chris Browne <[EMAIL PROTECTED]> writes:
>> Unfortunately (perhaps) standards_conforming_strings does not appear
>> to be exported, so I'm not sure how to do this otherwise.
>
> Perhaps your problem is one of spelling?  It's
> "standard_conforming_strings", and it's certainly a global variable.
>
> You still haven't explained why you need this, though.  There are
> no datatype output functions that examine this variable.

Apologies.  I was reading too much into the problem report.

After some discussion on IRC, I think we've "simplified" things back
to what the problem is.

Our logtrigger() function is already applying a "non-SCS" policy to
quote backslashes.  No need to declare anything there, as had been the
case with a recent DateStyle issue.

With DateStyle, we had to control the style in two places:
 1.  In logtrigger(), when PostgreSQL is asked to generate the
 string version of a datestamp.  We do so by temporarily
 setting DateStyle.
 2.  When loading data, we need to make sure the connection
 uses a consistent DateStyle.  We do so by setting the GUC
 variable using SET.

I was under the misapprehension that we needed something akin to that
step 1; apparently we only need worry about step 2.  To which Peter's
suggestion is entirely sufficient.

Sorry about the confusion.
-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/internet.html
"Who is General Failure and why is he reading my hard disk?" 
-- <[EMAIL PROTECTED]>, Felix von Leitner

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Help! - Slony-I - saving/setting/restoring GUC

[Chris Browne]

[EMAIL PROTECTED] (Peter Eisentraut) writes:
> Chris Browne wrote:
>> In support of PG 8.2, we need to have the log trigger function do the
>> following:
>>   - Save value of standards_conforming_string
>>   - Set value of standards_conforming_string to FALSE
>>   - proceed with saving data to sl_log_?
>>   - Recover value of standards_conforming_string
>
> Would SET LOCAL help you?

Not really.  The log trigger function is an SPI function, and I don't
think I want to be invoking an extra SQL request every time a tuple is
updated.

Consider our present handling of date localizations...  Slony-I
prefers to operate using ISO dates.  So the log trigger function must
force the datestyle correspondingly.

So our code looks like...  (eliding irrelevant code)

int OldDateStyle = DateStyle;
DateStyle = USE_ISO_DATES;

/* code that generates data to stow in sl_log_n */

DateStyle = OldDateStyle;  /* Retrieve user's local settings */

---

At one point, I thought that we'd modify this to:
int OldDateStyle = DateStyle;
int OldSCS = standards_conforming_strings;
DateStyle = USE_ISO_DATES;
standards_conforming_strings = FALSE; 

/* code that generates data to stow in sl_log_n */

DateStyle = OldDateStyle;  /* Retrieve user's local settings */
standards_conforming_strings = OldSCS;

Unfortunately (perhaps) standards_conforming_strings does not appear
to be exported, so I'm not sure how to do this otherwise.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/wp.html
Editing is a rewording activity.
-- Alan J. Perlis
[And EMACS a rewording editor.  Ed.]

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

   http://www.postgresql.org/docs/faq

[HACKERS] Help! - Slony-I - saving/setting/restoring GUC

[Chris Browne]

In support of PG 8.2, we need to have the log trigger function do the
following:
  - Save value of standards_conforming_string
  - Set value of standards_conforming_string to FALSE
  - proceed with saving data to sl_log_? 
  - Recover value of standards_conforming_string

The variable, standards_conforming_string, does not appear to be
widely exported, but rather seems pretty localized to guc.c

This is the prime thing holding us from RC2.

Tom, Peter, you have touched guc.c in the context of
standards_conforming_string; perahps you can suggest something?  Darcy
and I aren't seeing what to do...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/
Why do we drive on parkways and park on driveways?

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

   http://archives.postgresql.org

[HACKERS] "hot standby" system

[Chris Campbell]

The documentation [1] says this about On-line backup and point-in- 
time recovery:


If we continuously feed the series of WAL files to another machine  
that has been loaded with the same base backup file, we have a "hot  
standby" system: at any point we can bring up the second machine  
and it will have a nearly-current copy of the database.


Is this possible today in a stable and robust way? If so, can we  
document the procedure? If not, should we alter the documentation so  
it's not misleading? I've had several people ask me where to enable  
the "hot standby" feature, not realizing that PostgreSQL only has  
some of the raw materials that could be used to architect such a thing.


Thanks!

- Chris

[1] http://www.postgresql.org/docs/8.1/interactive/backup-online.html


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

  http://archives.postgresql.org

[HACKERS] gBorg misbehaviour

[Chris Browne]

Looks like gBorg has gone down...

The Slony-I project does plan to move to pgFoundry, once 1.2 is released...


But we need to get to that point (1.2) first.  Alas, gBorg being down
today doesn't help :-(.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/sap.html
"Success is something  I  will dress for   when I get  there,  and not
until."  -- Unknown

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

[HACKERS] Standards conforming strings

[Chris Browne]

I understand that we have an issue, with Slony-I, concerning the new
"standards_conforming_strings" option in 8.2.

Slony-I uses the "legacy" quoting conventions, which, such as it is,
is fine.

If a particular server is set to standards_conforming_strings=on, this
will presumably lead to certain bits of "breakage."

Is that a GUC variable that may be overridden in the fashion of
datestyles?  (e.g. - inside Slony-I, we set DateStyle = USE_ISO_DATES
in those places where it's needful)

Could that variable be escape_backslash?  (described in c.h...)

/*
 * Support macros for escaping strings.  escape_backslash should be TRUE
  * if generating a non-standard-conforming string.  Prefixing a string
   * with ESCAPE_STRING_SYNTAX guarantees it is non-standard-conforming.
* Beware of multiple evaluation of the "ch" argument!
 */

e.g. - we might force non-standard-conforming via a code segment like...

{
 int Oldescape_backslash;
 Oldescape_backslash = escape_backslash;
 escape_backslash = TRUE;
 do some stuff...

 escape_backslash = Oldescape_backslash;
}
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/x.html
"Moebius strippers only show you their back side." -- Unknown

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

   http://archives.postgresql.org

Re: [HACKERS] Three weeks left until feature freeze

[Chris Browne]

kleptog@svana.org (Martijn van Oosterhout) writes:
> On Thu, Jul 13, 2006 at 01:26:30PM -0400, Tom Lane wrote:
>> The right way to proceed is what was mentioned in another message:
>> work harder at educating packagers about which non-core projects
>> are worth including in their packages.  I have to confess
>> contributing to the problem, as I'm not currently including
>> eg. Slony in the Red Hat RPMs.  I certainly should be --- but
>> "fixing" that by pushing Slony into the core PG distro is not a
>> solution.
>
> Indeed. Distributors are not going to go through pgfoundary and
> package everything, there's just no point. I think it would be very
> useful to dedicate a portion of the website to add-ons that are
> considered worthwhile.

If there were enough chunks of it that were buildable using pgxs or
similar such that they could pretty readily script up...

for project in `echo $LIST`; do
   cd $DOWNLOADS
   wget http://downloads.pgfoundry.org/${project}/${project}-latest.tar.bz2
   cd $BUILDHOME
   mkdir $${project}
   cd $${project}
   tar xfvj $DOWNLOADS/${project}-latest.tar.bz2
   cd *
   ./configure --pgxs-options  --path=/usr  --rpm-deteriorata
   make install
   run-rpm-file-collector $${project}
done

The folks running Perl and Python repositories have gotten the
"toolage" together so that you can pull CPAN packages and very nearly
turn them into RPM packages.

If we have an interestingly large set of packages at pgFoundry that
are "that RPMable," then they *will* come.
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/oses.html
Rules of the Evil Overlord  #98. "If an attractive young couple enters
my realm,  I will carefully monitor  their activities. If  I find they
are  happy   and  affectionate,  I  will  ignore   them.   However  if
circumstance  have forced them  together against  their will  and they
spend  all their  time  bickering and  criticizing  each other  except
during the  intermittent occasions when  they are saving  each others'
lives  at  which point  there  are hints  of  sexual  tension, I  will
immediately order their execution." 

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

[HACKERS] Proper Method for using LockAcquire

[Chris Bowlby]

Hi All,

I've been working on a small module that I will be pluging into my 
local PostreSQL 8.x database and am in need of doing some table locking. 
At this time, I've used various other examples to no avail and was 
wondering what the proper method for aquiring a table lock within the 
module would be?


For example I am using an SPI based module:

static void mytest(void) {
 LOCKMETHODIDlocalLockTableId = INVALID_LOCKMETHOD;
 LOCKTAG localtag;

 memset(&localtag, 0, sizeof(localtag));
 localtag.relId = XactLockTableId;
 localtag.dbId = 1;
 localtag.objId.xid = InvalidTransactionId;

 if (!LockAcquire(LocalLockTableId, &localtag, 
GetCurrentTransactionId(), Sharelock, false)) {

  elog(ERROR, "mytest: did not acquire table lock");
 }

 

 if(!LockRelease(LocalLockTableId, &localtag, 
GetCurrentTransactionId(), Sharelock)) {

  elog(ERROR, "mytest: could not release lock");
 }
}

I know there is something I am missing and would appreciate any help. I 
believe I need to initialize the LocalLockTableId, but I have not been 
able to find any examples of that being done. Could someone look this 
over and point me in the right direction?




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

[HACKERS] newbie patch-attempt: selecting large result sets in psql

[Chris Mair]

Hi :)

powered by the great talk for newbie hackers by Neil Conway & Gavin
Sherry [1] at the anniversary summit I sneaked into the Code Sprint
and started working a bit on a Todo from Neil's Code Sprint Page:

  "Add a psql option to have it submit SELECT queries via a cursor,
  to avoid excessive memory consumption when manipulating large
  result sets"

Gavin gave me a sketch of a patch that added a new command line
option to psql that would have it use cursors for selects.

One of the problems with this was that a user would expect psql to
work as usual (including all format and output option stuff) and
to do this properly most of the psql output code would need to be
refactored.

Thinking about it, we had the idea to just introduce a new \ operator
that would output the results of a select (using cursors) in an
indipendent code path.  Who's selecting gigabytes of stuff into a
HTML table anyway?

So, I've introduced a new \u command that from a user perspective
is identical to \g (it executes the query buffer), just that it
uses cursors and honours only the field separator.

For example you could just do

select a, c, g
from big
\u bigout.dat

The patch is here [2], is working, but needs some performance
improvment and double checking. Which I won't do before getting
some feedback that what I'm doing does make any sense at all ;)

Bye, Chris


[1] http://neilconway.org/talks/hacking/
[2] http://www.1006.org/tmp/psql_cursor-3.patch




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] request for feature: psql 'DSN' option

[Chris Browne]

[EMAIL PROTECTED] (David Fetter) writes:
> On Fri, Jul 07, 2006 at 05:33:14AM -0500, Andrew Dunstan wrote:
>> Christopher Browne said:
>> >
>> > The notion:  Plenty of libraries out there like Pg, DBI::Pg, and such
>> > make you specify connections in the form:
>> >   "host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"
>> 
>> Why is this better than using the service file?
>
> What Andrew asked.

It's not *better* than PGSERVICE; it's just that this does reflect the
connection representation widely used by every sort of library out
there that uses libpq.

Let's do a quick "guess the language"...

Which language is this?
  conn = PGconn.open("host=my.db.host.example.org port=5678 dbname=dumb_db 
user=chewie");

How about this?
  $conn = Pg::connectdb("host=my.db.host.example.org port=5678 dbname=dumb_db 
user=chewie");

Hmm...  This one should be easy to guess...
  import psycopg2 
  dbcon = psycopg2.connect('host=my.db.host.example.org port=5678 
dbname=dumb_db user=chewie')

This one might conceivably be easy to mistake for another...
  $dbconn = pg_connect("host=my.db.host.example.org port=5678 dbname=dumb_db 
user=chewie")
 or die('Could not connect: ' . pg_last_error());

How about this?
  set conres [catch {set newdbc [pg_connect -conninfo 
  "host=my.db.host.example.org port=5678 dbname=dumb_db user=chewie"]} 
msg]

This isn't as obvious as it may seem...
  (setq P (pq-connectdb "host=my.db.host.example.org port=5678 dbname=dumb_db 
user=chewie"))

But this probably is...
  (setf conn (postgresql::PQconnectdb "host=my.db.host.example.org port=5678 
dbname=dumb_db user=chewie"))

Gavin would be extremely disappointed if I had left this one out :-)
  let main () =
let c = new connection ~conninfo:"host=my.db.host.example.org port=5678 
dbname=dumb_db user=chewie" () in
let _ = c#exec ~expect:[Copy_in] ("copy " ^ Sys.argv.(1) ^ " from stdin") in
c#copy_in_channel stdin;
c#finish

The common thing across all of these is that there's a single string
passed in to request the connection.  A string that libpq in effect
requires, but which psql, somewhat oddly, does not allow you to
directly specify.

We've had cases where we needed to store pgconn "connection
signatures", for reports, and then discovered we wanted to use them to
describe psql connections.

A "--pgconn" or "-D" option would allow doing this.

I agree that pg_service.conf is probably cleaner, but it seems
somewhat odd for The Poster Child libpq application, psql, *not* to
have a way of passing in a conninfo value.
-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/oses.html
If a mute swears, does his mother wash his hands with soap? 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] lastval exposes information that currval does not

[Chris Campbell]

On Jul 5, 2006, at 14:51, Phil Frost wrote:

test=# create function bump() returns bigint language sql security  
definer as $$ select nextval('private.seq'); $$;


SECURITY DEFINER means that the function runs with the permissions of  
the role used to create the function (ran the CREATE FUNCTION  
command). Due to your # prompt, I'm guessing that you were a  
superuser when you ran this command. Thus, bump() will be run with  
the superuser's permissions.


The superuser most definitely has permissions to access private.seq.

This has nothing to do with schema security or lastval() versus  
currval().


Check out the CREATE FUNCTION documentation:

   http://www.postgresql.org/docs/8.1/interactive/sql- 
createfunction.html


- Chris


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

Re: [HACKERS] buildfarm stats

[Chris Mair]

> but it is about 2Gb of data, so just putting a dump cleaned of personal 
> data somewhere isn't really an option.
> 
> I could arrange a dump without the diagnostics, in  these 2 tables:
> 
> system: < name | operating_system | os_version | compiler | 
> compiler_version | architecture >
> build: < name | snapshot | stage | branch | build_flags >
> 
> (stage in the latter table is OK on success or the name of the stage 
> that failed otherwise).
> 
> 
> But what do you want it for? And do you want it one-off or continuously?

Nothing important at all.
I'd just thought about a few interesting stats, like failures vs OS
(as the first poster said) or failures vs gcc version or timings vs.
arch / RAM or gcc version, etc. For the timings I guess there are
some timestamps embedded that might be extracted...

But I didn't really think about it, before posting (classic
mailing list syndrome ;)

Bye,
Chris.





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

Re: [HACKERS] buildfarm stats

[Chris Mair]

> Thanks for the stats Andrew. Out of interest, can you easily tabulate
> the number of failures against OS?

Or, more generally, even put a dump of the DB (without personal infos
of course :) somewhere?

Bye, Chris.

PS: and don't say you're running it in MySQL ;)

 


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

Re: [HACKERS] Index corruption

[Chris Browne]

[EMAIL PROTECTED] (Marc Munro) writes:
> As you see, slony is attempting to enter one tuple
> ('374520943','22007','0') two times.
>
> Each previous time we have had this problem, rebuilding the indexes on
> slony log table (sl_log_1) has fixed the problem.  I have not reindexed
> the table this time as I do not want to destroy any usable evidence.

We have seen this phenomenon on 7.4.8 several times; pulled dumps of
sl_log_1 and index files that Jan Wieck looked at, which alas hasn't
led to a fix.

He did, mind you, find some concurrency pattern that led, if memory
serves, to 7.4.12's release.  We had experienced cases where there was
some worse corruption that required that we rebuild replicas from
scratch :-(.
-- 
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/advocacy.html
"There  is no  psychiatrist in  the world  like a  puppy  licking your
face."  -- Ben Williams

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

Re: [HACKERS] vacuum, performance, and MVCC

[Chris Browne]

[EMAIL PROTECTED] (Csaba Nagy) writes:

>> > [...]
>> > There has to be a more linear way of handling this scenario.
>> 
>> So vacuum the table often.
>
> Good advice, except if the table is huge :-)

... Then the table shouldn't be designed to be huge.  That represents
a design error.

> Here we have for example some tables which are frequently updated but
> contain >100 million rows. Vacuuming that takes hours. And the dead row
> candidates are the ones which are updated again and again and looked up
> frequently...

This demonstrates that "archival" material and "active" data should be
kept separately.

They have different access patterns; kludging them into the same table
turns out badly.

> A good solution would be a new type of vacuum which does not need to
> do a full table scan but can clean the pending dead rows without
> that... I guess then I could vacuum really frequently those tables.

That's yet another feature that's on the ToDo list; the "Vacuum Space
Map."

The notion is to have lists of recently modified pages, and to
restrict VACUUM to those pages.  (Probably a special version of
VACUUM...)
-- 
output = reverse("moc.enworbbc" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lisp.html
"As  I've gained  more  experience with  Perl  it strikes  me that  it
resembles Lisp in many ways, albeit Lisp as channeled by an awk script
on acid."  -- Tim Moore (on comp.lang.lisp)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Generic Monitoring Framework Proposal

[Chris Browne]

[EMAIL PROTECTED] (Robert Lor) writes:
> For DTrace, probes can be enabled using a D script. When the probes
> are not enabled, there is absolutely no performance hit whatsoever.

That seems inconceivable.

In order to have a way of deciding whether or not the probes are
enabled, there has *got* to be at least one instruction executed, and
that can't be costless.
-- 
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/wp.html
"...while   I   know  many   people   who   emphatically  believe   in
reincarnation, I have  never met or read one  who could satisfactorily
explain population growth." -- Spider Robinson

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

   http://www.postgresql.org/docs/faq

[HACKERS] Preventing DELETE and UPDATE without a WHERE clause?

[Chris Campbell]

I heard an interesting feature request today: preventing the  
execution of a DELETE or UPDATE query that does not have a WHERE clause.


The user was worried about a typo leading to:

   DELETE FROM very_important_table

and deleting all the data. Or doing something similar with an UPDATE:

   UPDATE very_important_table SET important_column = 'Smith'

and all the rows now have their important_column set to Smith.

I was thinking that this could be accomplished with a GUC to cause  
the server to report an error if DELETE and UPDATE queries don't  
contain WHERE clauses. "allow_mod_queries_without_qualifier" or  
something (which would obviously default to true).


If this setting was activated (the GUC changed to false), the above  
queries could still be executed, but it would take a conscious effort  
by the user to add a WHERE clause:


   DELETE FROM very_important_table WHERE true;
   UPDATE very_important_table SET important_column = 'Smith' WHERE  
true;


Would such a patch ever be accepted?

Thanks!

- Chris


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

Re: [HACKERS] Fabian Pascal and RDBMS deficiencies in fully implementing

[Chris Browne]

kleptog@svana.org (Martijn van Oosterhout) writes:

> On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote:
>> > [3] 
>> > http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
>> 
>> The sample problem in [3] is one that shows pretty nicely a
>> significant SQL weakness; it's very painful to build SQL to do complex
>> things surrounding cumulative statistics.
>
> I havn't managed to wrap my brain around them yet, but this seems like
> something that SQL WINDOW functions would be able to do. For each row
> define the window frame to be all the preceding rows, do a SUM() and
> divide that over the total. Or perhaps the PERCENT_RANK() function does
> this already, not sure.
>
> Mind you, postgres doesn't support them yet, but it's interesting that
> it may be possible at all...

Yes, you are exactly right; I have seen a couple references to OVER
and PARTITION BY which look as though they are the relevant SQL
additions...

http://blogs.ittoolbox.com/database/technology/archives/olap-sql-part-5-windowing-aggregates-8373
http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://www.experts-exchange.com/Databases/Oracle/Q_21793507.html

I'm not sure the degree to which these are standardized, but they are
available in some form or another in late-breaking versions of Oracle,
DB2, and Microsoft SQL Server.

I'm not quite sure how to frame this so as to produce something that
should go on the TODO list, but it looks like there's a possible TODO
here...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/sap.html
"The newsreader abuse likely  stems from more fundamental, than merely
just the  UI, design disagreements. Requests from  Unix programmers to
replicate  Free Agent  rightfully so  should trigger  the  throwing of
sharp heavy objects at the requesting party."
-- [EMAIL PROTECTED] (jedi)

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Possible TODO item: copy to/from pipe

[Chris Browne]

[EMAIL PROTECTED] (Andreas Pflug) writes:
> Dave Page wrote:
>>
>>>-Original Message-
>>> From: [EMAIL PROTECTED]
>>> [mailto:[EMAIL PROTECTED] On Behalf Of Andreas
>>> Pflug
>>>Sent: 31 May 2006 16:41
>>>Cc: Tom Lane; pgsql-hackers@postgresql.org
>>>Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe
>>>
>>>Andreas Pflug wrote:
>>>
>>>
 Won't help too much, until gzip's output is piped back too, so a
 replacement for COPY .. TO STDOUT COMPRESSED  would be
 COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to
>>>
>>> receive the
>>>
reduced stuff.
>>>
>>>Forgot to mention:
>>> COPY COMPRESSED was also meant to introduce a portable format
>>> that's efficient for both text and binary data. Relying on some
>>> external XYZzip version seems not too portable to me.
>> It does have that advantage. Gzip and others are not particularly
>> Windows friendly for example.
>
> ... as most windows programs are pipe agnostic.

Shall we make PostgreSQL less powerful because of that?
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/advocacy.html
"Love is like a snowmobile flying over the frozen tundra that suddenly
flips, pinning you underneath.  At night, the ice weasels come."
-- Matt Groening

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[HACKERS] Schema Limitations ?

[Chris Broussard]

Hello Hackers,

I have the following questions, after reading this FAQ (http:// 
www.postgresql.org/docs/faqs.FAQ.html#item4.4) are there statistics  
around the max number of schemas in a database, max number of tables  
In a schema, and max number of tables in a database (number that  
spans schemas) ? Are the only limitations based on disk & ram/swap ?


Does anybody have a rough ballpark figures of the largest install  
base on those questions?


I'm curious about these stats, because I'm debating on how best to  
break up data, between schemas, physical separate databases, and the  
combination of the two.


Thanks In Advanced.

Chris

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

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

[Chris Browne]

[EMAIL PROTECTED] ("Mark Woodward") writes:
>> Jim C. Nasby wrote:
>>> Maybe a compatability layer isn't worth doing, but I certainly
>>> think it's very much worthwhile for the community to do everything
>>> possible to encourage migration from MySQL. We should be able to
>>> lay claim to most advanced and most popular OSS database.
>>>
>>
>> We'll do that by concentrating on spiffy features, not
>> compatibility layers. I want people to use PostgreSQL because it's
>> the best, not because it's just like something else.
>>
>
> While I do agree with the ideal, the reality may not be good
> enough. Even I, a PostgreSQL user for a decade, have to use MySQL
> right now because that is what the client uses.
>
> Again, there is so much code for MySQL, a MySQL emulation layer, MEL
> for short, could allow plug and play compatibility for open source,
> and closed source, applications that otherwise would force a
> PostgreSQL user to hold his or her nose and use MySQL.

But this is essentially what killed off OS/2 in the marketplace.

IBM created a "good enough" emulation layer that it ran [early]
Windows(tm) applications sufficiently well that nobody bothered
porting applications to *properly* work with OS/2.

Microsoft then played off that with exceeding success; they made sure
that future versions of Windows(tm) were sufficiently different that
OS/2 was left orphaned.

We *are* in a sufficiently comparable state here; MySQL AB is *NOT*
our friend; they want to successfully 'take over the world,' at least
as far as they can do so with their product line...
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable.  Gov. George W. Bush declared
Tarrant County a disaster area.  Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."

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

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

[Chris Browne]

[EMAIL PROTECTED] ("Marc G. Fournier") writes:
> To give someone a running chance at migrating it to PostgreSQL, a
> 'MySQL compatibility module' would allow them to just plug the
> existing DB in, and then work at improving sections of the code over
> time ...
>
> Hell, if done well, the module should be able to dump appropriately
> 'clean' PgSQL schemas ... as in your example elow about the domains ...

You can't have that because you essentially need to throw out four
aspects of fairly vital "data validation" functionality:

1.  Dates cease to be validatable.

2.  NULL and 0 and '' are all roughly equivalent, even though they
aren't.

3.  Foreign key constraints have to be ignored.

4.  You have to fabricate a locale offering a case-insensitive sort
order.

I suppose #4 isn't "vital data validation"...

But after you "gut" the PostgreSQL-based system of those four aspects
of data integrity, I'm not sure there's any remaining benefit to
having PostgreSQL in play...
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable.  Gov. George W. Bush declared
Tarrant County a disaster area.  Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."

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

Re: [HACKERS] autovacuum logging, part deux.

[Chris Browne]

[EMAIL PROTECTED] ("Larry Rosenman") writes:
> Gentlepeople,
> Now that the patch is out for keeping the last
> autovacuum/vacuum/analyze/autoanalyze
> timestamp in the stats system is pending, what's the consensus view on
> what, if any,
> logging changes are wanted for autovacuum?
>
> I have the time and inclination to cut code quickly for it.

It would be Really Nice if it could draw in the verbose stats as to
what the VACUUM did...

e.g. - to collect some portion (INFO?  DETAIL?  I'm easy :-)) of the
information that PostgreSQL generates at either INFO: or DETAIL:
levels.

/* [EMAIL PROTECTED]/dba2 vacdb=*/ vacuum verbose analyze vacuum_requests;
INFO:  vacuuming "public.vacuum_requests"
INFO:  index "vacuum_requests_pkey" now contains 2449 row versions in 64 pages
DETAIL:  3 index pages have been deleted, 3 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "vr_priority" now contains 0 row versions in 19 pages
DETAIL:  16 index pages have been deleted, 16 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "vacuum_requests": found 0 removable, 2449 nonremovable row versions in 
65 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 2809 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_95167460"
INFO:  index "pg_toast_95167460_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_95167460": found 0 removable, 0 nonremovable row versions in 0 
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.vacuum_requests"
INFO:  "vacuum_requests": 65 pages, 2449 rows sampled, 2449 estimated total rows
VACUUM

-- 
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/x.html
If you  stand in the middle  of a library and  shout "Argh" at
the top of your voice, everyone just stares at you. If you do the same
thing on an aeroplane, why does everyone join in?

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

Re: [HACKERS] Logging pg_autovacuum

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:

> "Larry Rosenman" <[EMAIL PROTECTED]> writes:
>> I'd like to see a more concrete definition of what we 
>> want Autovacuum to output and at what levels. 
>
> I would argue that what people typically want is
>
>   (0) nothing
>
>   (1) per-database log messages
>
> or
>
>   (2) per-table log messages (including per-database)
>
> The first problem is that (2) is only available at DEBUG2 or below,
> which is not good because that also clutters the log with a whole
> lot of implementer-level debugging info.
>
> The second problem is that we don't really want to use the global
> log_min_messages setting to determine this, because that constrains
> your decision about how much chatter you want from ordinary
> backends.
>
> I suggest that maybe the cleanest solution is to not use log level
> at all for this, but to invent a separate "autovacuum_verbosity"
> setting that controls how many messages autovac tries to log, using
> the above scale.  Anything it does try to log can just come out at
> LOG message setting.

At "level 2," it seems to me that it would be quite useful to have
some way of getting at the verbose output of VACUUM.

Consider when I vacuum a table, thus:

/* [EMAIL PROTECTED]/dba2 performance=*/ vacuum verbose analyze days;
INFO:  vacuuming "public.days"
INFO:  "days": found 0 removable, 1893 nonremovable row versions in 9 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  analyzing "public.days"
INFO:  "days": 9 pages, 1893 rows sampled, 1893 estimated total rows
VACUUM

The only thing that PostgreSQL will log generally about this is, if
the query runs for a while, that I requested "vacuum verbose analyze
days;", and that this took 4284ms to run.

It would be really nice if we could have some way of logging the
details, namely of numbers of row versions removed/nonremovable, and
of pages affected.

If we could regularly log that sort of information, that could be very
useful in figuring out some "more nearly optimal" schedule for
vacuuming.

One of our people wrote a Perl script that will take verbose VACUUM
output and essentially parses it so as to be able to generate a bunch
of SQL queries to try to collect how much time was spent, and what
sorts of changes got accomplished.

At present, getting anything out of that mandates that every VACUUM
request have stdout tied to this Perl script, which I'm not overly
keen on, for any number of reasons, notably:

- Any vacuums run separately aren't monitored at all

- Parsing not-forcibly-stable-across-versions file formats with Perl
  is a fragile thing

- Ideally, this would be nice to get into the PG "engine," somewhere,
  whether as part of standard logging, or as part of how pg_autovacuum
  works...

Having some ability to collect statistics about "we recovered 42 pages
from table foo at 12:45" would seem useful both from an immediate
temporal perspective where it could suggest whether specific tables
were being vacuumed too (seldom|often), and from a more
global/analytic perspective of perhaps suggesting better kinds of
vacuuming policies.  (In much the same way that I'd like to have some
way of moving towards an analytically better value for
default_statistics_target than 10...)

If people are interested, I could provide a copy of the "analyze
VACUUM stats" script...
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/sgml.html
"I would rather spend 10 hours reading someone else's source code than
10  minutes listening  to Musak  waiting for  technical  support which
isn't." -- Dr. Greg Wettstein, Roger Maris Cancer Center

---(end of broadcast)---
TIP 1: 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] About pg_hba.conf

[Chris Browne]

[EMAIL PROTECTED] ("Gevik Babakhani") writes:
> This may be a dumb question but please bear a moment with me.  About
> the TODO item %Allow pg_hba.conf settings to be controlled via
> SQL: If in the future we could configure the settings by SQL
> commands, assuming the settings are saved in an internal table, what
> would be the need for a pg_hba.conf file anymore. (except for the
> backward compatibility of cource)

It's a frequently asked question...

The trouble is, what if you accidentally lock all of the users out?
How do you fix that, if nobody can connect to submit SQL commands?
-- 
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/lsf.html
"Ahhh. A man with a sharp wit.  Someone ought to take it away from him
before he cuts himself." -- Peter da Silva

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

Re: [HACKERS] control pg_hba.conf via SQL

[Chris Browne]

[EMAIL PROTECTED] (Andrew Dunstan) writes:
> We don't have the luxury of being able just to throw out old stuff
> because we think it might be neater to do it another way.  The current
> rules for HBA are order dependent. The issue raised as I understood it
> was not to invent a new scheme but to be able to manage it from inside
> a postgres session.

If the need to support "legacy usage" mandates something like Svenne
Krap's suggestion of a control flag inside pg_hba.conf, or something
otherwise akin to Robert Treat's suggestions, then I think this *is*
designing something new/neater.

I think it would take a fair bit of work (and kludging of design) to
build something to slavishly emulate pg_hba.conf; it seems to me that
it is a much better thing to have an inside-the-database HBA scheme be
based on what is a good design inside-the-database.

> Of course, if we go for a new scheme that is not order dependent,
> then inventing a reasonable SQL syntax to support it becomes a heck
> of a lot easier. Something along the lines of GRANT/REVOKE CONNECT
> ... should do the trick.

Sure.  This would come as something of a 2 level attack on the problem:

 1.  Find a decent representation for the data;

 2.  Find a decent way to tell the system about the data...

> Maybe we could do something like this: if there is a pg_hba.conf
> file present, then use it as now and ignore the access rights table
> - if someone does GRANT/REVOKE CONNECT while under pg_hba.conf then
> process it but issue a warning. Maybe there could also be an initdb
> switch that gave users a choice.

initdb is a terrible choice for that; that means you have to
re-initialize the database to change the option.

I think a better approach is to control this in postgresql.conf...

An option like...

  host_based_authentication_file = "/etc/postgresql/pg_hba.conf"
  host_based_authentication = "internal"  [looks at table pg_catalog.pg_hba, 
let's say]
   # options here are "internal", "file", "file, internal", "internal, file"

That way, if a problem arises that is locking the administrator out,
it can be resolved by a "pg_ctl reload".
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"When  I was a  boy of  fourteen, my  father was  so ignorant  I could
hardly  stand to  have  the  old man  around.  But when  I  got to  be
twenty-one, I  was astonished at how  much the old man  had learned in
seven years." -- Mark Twain

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

   http://archives.postgresql.org

Re: [HACKERS] control pg_hba.conf via SQL

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> If your pg_hba.conf looks like
>   hostall all 0.0.0.0/32  md5
> there's not much call to update it dynamically ...

There's one case, where .pgpass got hosed, and you didn't have a
backup of it, and need to assign new passwords...

I once ran into a case like this, where nobody had bothered to record
the "postgres" user's password, and had to override md5 authentication
in order to get in and reset passwords...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"When  I was a  boy of  fourteen, my  father was  so ignorant  I could
hardly  stand to  have  the  old man  around.  But when  I  got to  be
twenty-one, I  was astonished at how  much the old man  had learned in
seven years." -- Mark Twain

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

Re: [HACKERS] How to put back??

[Chris Browne]

[EMAIL PROTECTED] (Dhanaraj M - Sun Microsystems) writes:
> Hi all,
>
>  I have recented joined and working on postgres. I fixed a bug that I
> saw in the mailing list. I ran the regression test that is available
> in postgres. It was successful and now I need the following details..
>
> 1) Test suits that i could get to test my code..

There is an existing test suite in the source code in src/test/regress.

You might check in there to see where the tests relevant to the
feature you modified reside, and see if you can add relevant tests
that break, in the unpatched system, and work fine, after the change.

> 2) How can I put back my fix into postgres src code.. (or) What is the
> procedure that I should follow at this stage??

Once you have a patch, there's a patches list where you can submit it...
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/spreadsheets.html
Rules of the Evil Overlord #32. "I will not fly into a rage and kill a
messenger who brings me bad news  just to illustrate how evil I really
am. Good messengers are hard to come by."


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

   http://archives.postgresql.org

Re: [HACKERS] Seperate command-line histories for seperate databases

[Chris Browne]

[EMAIL PROTECTED] ("Markus Bertheau") writes:
> 2006/3/17, Bruce Momjian :
>> Peter Eisentraut wrote:
>> > Bruce Momjian wrote:
>> > > The psql manual pages for 8.1 now has:
>> >
>> > >   \set HISTFILE ~/.psql_history- :DBNAME
>
> Any reason psql doesn't do this by default? It is clear that the
> database name does not unambiguously identify a database, but having a
> history for each database name is already an improvement over the
> current situation.

I fairly frequently find myself accessing different (but similar)
databases, and it's quite valuable to be able to submit the *same*
queries to them.

This change would make it troublesome to do that.  I'd not be all that
keen on the change...
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/rdbms.html
"On the  other hand, O'Reilly's book  about running W95 has  a toad as
the cover animal.  Makes sense; both have lots of  warts and croak all
the time."  --- Michael Kagalenko,

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

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

[Chris Browne]

[EMAIL PROTECTED] (Greg Stark) writes:
> Christopher Browne <[EMAIL PROTECTED]> writes:
>
>>   Letter of Invitation for Countries Whose Citizens Require a
>>   Temporary Resident Visa to Enter Canada
>
> I missed that this was happening up here in Canada. How exclusive is
> the guest list for this? Like, are you only expecting 50 top
> contributors by invitation only or is anyone who can make it
> welcome? What kind of costs are anticipated?

It's not intended to be punitively high priced, so as to keep it
exclusive, but the more expensive you find it to travel to Toronto,
the more you'll find it costs, naturally...  I'll probably grouse
about parking costs a bit, at some point, but I won't have a thousand
dollar plane ticket to pay for, to be sure... ;-)

I think there is some desire to have some amount of funding provided
for travel/accomodations based on what can be raised thru SPI; that's
certainly still a matter in flux.  The answers aren't clear yet...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/
Rules of the Evil Overlord #31. "All naive, busty tavern wenches in my
realm  will be replaced  with surly,  world-weary waitresses  who will
provide no  unexpected reinforcement  and/or romantic subplot  for the
hero or his sidekick." 

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

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions

[Chris Browne]

oleg@sai.msu.su (Oleg Bartunov) writes:
> I'd need an invitation to get a visa. Is't possible ?

"Certainty" is difficult to promise, but there is a reasonable
population of relevant people here such that invitations can be
arranged.

In view of the fact that it can take a fair bit of time to arrange
visas, this is something we should watch for pretty early...

Now is not too early to be arranging for passports and visas...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/internet.html
"Bonus!  The lack of multitasking is one of the most important reasons
why DOS destroyed Unix in the marketplace." -- Scott Nudds

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Patch Submission Guidelines

[Chris Browne]

[EMAIL PROTECTED] (Robert Treat) writes:

> On Tuesday 14 February 2006 16:00, Martijn van Oosterhout wrote:
>> > I would like to suggest that we increase substantially the FAQ entries
>> > relating to patch submission. By we, I actually mean please could the
>> > committers sit down and agree some clarified written guidelines?
>>
>> As I remember, there is a disinclination to increase the size of the
>> FAQ very much. This suggests maintaining it as a seperate document. Or
>> alternatively attach it as an appendix to the main documentation.
>>
>
> Huh?  The current developers FAQ is at least 1/2 the size of the main FAQ. I 
> think adding a submission on patch submission guidelines is a great idea. 
> I'll have a patch based on Simon's post to -patches ready in the next 24 
> hours unless someone is really going to object. 

If it were to be a new document, it would seem pretty sweet to call it
"The Hitchhiker's Guide To Getting Patches Accepted."

One of the "preface points" would be along the lines of...

 "Here are some guidelines as to what things to do to make it as easy
 as possible for proposed patches to be accepted with minimal change.
 To not follow them all does not forcibly guarantee rejection; it just
 increases the likelihood that the the amount of time and effort it
 takes to handle it increases..."
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/spiritual.html
"When campaigning, be swift as  the wind; in leisurely march, majestic
as the forest; in raiding and plundering, like fire; in standing, firm
as  the  mountains.   As  unfathomable  as the  clouds,  move  like  a
thunderbolt."  -- Sun Tzu, "The Art of War"

---(end of broadcast)---
TIP 1: 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] [PORTS] Failed install - libgen.so doesn't exist

[Chris Browne]

kleptog@svana.org (Martijn van Oosterhout) writes:
> On Sat, Feb 04, 2006 at 01:54:52AM +0100, Peter Eisentraut wrote:
>> I took a first swing at this and rearranged some of these calls.
>
>> ld -- On AIX at least this seems to be some magic library but doesn't 
>> have an obvious testable symbol.
>
> Indeed, appears to be AIX only.

Further, it appears to be AIX pre-4.3 only, when using it for dlopen()
replacement...

It would be an attractive idea to have configure detect not whether
it's open, but rather whether it is needed, and leave it out for AIX
4.3 and "better"...

> Apparently -lbsd is used on AIX but it contains pow() which is the
> wrong one (the comment wasn't clear). -lBSD was for hpux. Linux used to
> use it but not anymore.

Seneca didn't notice it being picked up; it may be that it is only
detected and used on old versions of AIX...

>> PW -- might be a compatibility library but none of the functions I found 
>> are used by PostgreSQL.
>
> Listed for old SCO. No idea what for though.

Apparently this is for compatibility with the AT&T Programmers
Workbench toolkit; probably not too relevant to anyone these days...

On AIX, it gets detected, but functions are never used.  I'll bet the
same is true on some other platforms (Solaris, HP/UX, and such).
-- 
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/nonrdbms.html
Smith's Test for Artificial Life:
When animal-rights activists and right-to-life protesters are marching
outside your laboratory, then you know you've definitely made progress
in your artificial life research.  -- Donald A. Smith

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Passing arguments to views

[Chris Campbell]

On Feb 3, 2006, at 11:21, Tom Lane wrote:


The SRF concept captures what you want a whole lot better.  If the
implementation isn't up to snuff, we should improve it, not warp other
pieces of the system.


Point taken. The rewriting concept is what I'm after; if that can be  
done pre-planning with SQL functions, I'm all for it. I just thought  
that since rules already do rewriting, that's the best thing to start  
building on.



Martijn mentioned the idea of inlining SQL functions that return sets
--- this is something I've toyed with too, but not got round to  
looking

at seriously.  AFAICS it would accomplish everything that you could do
with parameters in ON SELECT rules, considering the existing
restrictions on what can be in an ON SELECT rule.  And it wouldn't
require any new concepts at all, just a few(?) pages of code.


True, as long as there's a hook to do the inlining/rewriting before  
the query's planned. I guess we can see function calls at the parse  
stage, check to see if they're SQL functions or not, grab the prosrc,  
do the substitution, then re-parse?


I guess I can live without the dependancy tracking. I can always dump  
and reload my database to re-parse all the functions. Maybe we could  
have a RELOAD FUNCTION command that would just re-parse an existing  
function, so I don't have to dump and reload?


What about auto-creating a composite type for the function's return  
type based on the query definition? (Like how CREATE VIEW creates an  
appropriate table definition.) Do you see a way for CREATE FUNCTION  
to do that? The problem is that you have to specify a return type in  
CREATE FUNCTION.


Maybe an extension to CREATE FUNCTION as a shorthand for set- 
returning SQL functions? Like:


   CREATE SQL FUNCTION sales_figures(DATE) AS SELECT ... FROM ...  
WHERE sale_date <= $1;


It would (1) automatically create a composite type (newtype) for the  
return value, and (2) do a


   CREATE FUNCTION sales_figures(DATE) RETURNS SETOF newtype AS  
'...' LANGUAGE sql.


How much do I have to justify a patch for non-standard "RELOAD  
FUNCTION" and "CREATE SQL FUNCTION" commands (as described) in the  
grammar? :)


Thanks!

- Chris


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] Passing arguments to views

[Chris Campbell]

On Feb 3, 2006, at 12:27, Tom Lane wrote:


I guess I can live without the dependancy tracking. I can always dump
and reload my database to re-parse all the functions. Maybe we could
have a RELOAD FUNCTION command that would just re-parse an existing
function, so I don't have to dump and reload?


Hm?  I don't understand why you think this is needed.


Consider function foo() that references table bar. When you CREATE  
FUNCTION foo() ... AS 'SELECT ... FROM bar' you get an error message  
if bar doesn't exist. If it does exist, CREATE FUNCTION succeeds.


If you later DROP bar, you're not informed that function foo() was  
referencing it. You only find that out if you redefine foo() (using  
CREATE OR REPLACE FUNCTION and passing in the same definition, which  
fails) or if you try to run foo() (and the query fails).


If functions had true dependency tracking, then you couldn't DROP bar  
due to foo()'s dependency on it, unless you did a DROP CASCADE and  
were alerted that foo() was dropped as well.


I'm fine with those limitations. I can confirm that all of my  
functions are not referencing tables that don't exist by doing a  
CREATE OR REPLACE FUNCTION to reload each function. A pg_dump/ 
pg_restore would accomplish this, but it would be nice to have a  
"RELOAD FUNCTION" (or "REPARSE"? or "VERIFY"?) command that would  
just re-parse the function's source code (like CREATE FUNCTION does)  
and spit out errors if the function is referencing relations that  
don't exist. Just as a way to confirm that the table modification I  
just performed didn't break any functions. On-demand dependency  
checking, in a way.



Note that you can already do

regression=# create function fooey(int, out k1 int, out k2 int)  
returns setof record as
regression-# $$ select unique1, unique2 from tenk1 where thousand =  
$1 $$ language sql;

CREATE FUNCTION
regression=# select * from fooey(44);
  k1  |  k2
--+--
 7044 |  562
 5044 |  692
 1044 |  789
 4044 | 1875
 3044 | 3649
 2044 | 4063
 8044 | 6124
 6044 | 6451
 9044 | 6503
   44 | 7059
(10 rows)

regression=#


Learn something new every day. I'm still using 7.4 for most of my day  
job, and I can't do this without supplying a column definition list:


ERROR:  a column definition list is required for functions returning  
"record"


I hereby withdraw my proposal for "CREATE SQL FUNCTION."

Thanks!

- Chris



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] Multiple logical databases

[Chris Campbell]

On Feb 3, 2006, at 12:43, Rick Gigger wrote:

If he had multiple ips couldn't he just make them all listen only  
on one specific ip (instead of '*') and just use the default port?


Yeah, but the main idea here is that you could use ipfw to forward  
connections *to other hosts* if you wanted to. Basically working like  
a proxy.


- Chris


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] Multiple logical databases

[Chris Browne]

[EMAIL PROTECTED] ("Mark Woodward") writes:
> The "port" aspect is troubling, it isn't really self
> documenting. The application isn't psql, the applications are custom
> code written in PHP and C/C++.

Nonsense.  See /etc/services

> Using the "/etc/hosts" file or DNS to maintain host locations for is
> a fairly common and well known practice, but there is no such
> mechanism for "ports." The problem now becomes a code issue, not a
> system administration issue.

Nonsense.  See /etc/services

> If one writes the code to their website to use a generic host name,
> say, "dbserver," then one can easily test system changes locally and
> push the code to a live site. The only difference is the host
> name. When a port is involved, there is no systemic way to represent
> that to the operating system, and must therefor be part of the
> code. As part of the code, it must reside in a place where code has
> access, and must NOT be pushed with the rest of the site.
>
> Having some mechanism to deal with this would be cleaner IMHO.

I'm sure it would be, that's why there has been one, which has been in
use since the issuance of RFC 349 by Jon Postel back in May of 1972.
The mechanism is nearly 34 years old.

Note that RFCs are no longer used to issue port listings, as per RFC
3232, back in 2002.  Now, IANA manages a repository of standard port
numbers, commonly populated into /etc/services.

  

For customizations, see:

% man 5 services
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sgml.html
"Motto for a research laboratory: What we work on today, others will
first think of tomorrow." -- Alan J. Perlis

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Passing arguments to views

[Chris Campbell]

On Feb 3, 2006, at 10:25, Martijn van Oosterhout wrote:


Sure, for most procedural languages you can't do much. But we do do
syntax checking already and checking that the necessary functions  
exist

can be considered part of that. It' s not terribly important though.


Dependancy tracking needs to be more than a "best effort." If you  
can't do it accurately and completely, then I don't think it's worth  
doing at all.


But I agree with you that syntax checking is probably sufficient. We  
don't need true dependancy tracking for functions.


The only failure case (where syntax checking won't help you) is  
deleting (or renaming, or modifying) a table that a function was  
using. If you were to run or re-define the function, you'd learn  
about the missing (or renamed, or modified) table, whereas the  
dependancy-tracking system would prevent you from making the changes  
to the referenced table in the first place.



Ok, here's the deal. A view is nothing more than a RULE. Creating a
view does this automatically


Technically, it's a table and a rule, both of which are created by  
the CREATE VIEW command. We were talking about syntactic sugar, and  
CREATE VIEW is syntactic sugar for doing a CREATE TABLE and a CREATE  
RULE. That was my comparison. I'm aware of how views work. Here's the  
deal: I want to beef up rules versus beefing up functions. Maybe  
that's not the way to go; I'm enjoying this discussion and your  
insights.



CREATE RULE blah AS ON SELECT TO myview DO INSTEAD ;

Now, say you wanted to add parameters to this, would you restrict  
it to

SELECT rules, what about UPDATE or DELETE rules?


I don't see a huge use case for anything but SELECT rules, but I  
think it could be extended to any rule type. Maybe the CREATE RULE  
syntax could be something like:


CREATE RULE blah AS ON SELECT(INTEGER, INTEGER, DATE) TO myview  
DO INSTEAD SELECT * FROM sale WHERE sale_date = $3;



The other issue is that currently you can tell from looking at a
statement whether something is a function or a table (is it  
followed by

an open parenthesis?). With the above change you can't anymore, which
might mean you can't have functions and tables with the same names
because they'd be ambiguous.


Right. I said that my example syntax was deficient in this regard in  
the first message in this thread. And I solicited ideas for a better  
(unambiguous) syntax. I'm sure we would be able to come up with  
something. Maybe using square brackets instead of parentheses? Curly  
braces? "myview->(1, 2, 3)" notation? Since views are tables (when  
parsing the query, at least) we'd have to allow this syntax for any  
table reference, but throw an error (or silently discard the  
arguments) if the table didn't have a rule matching the argument types?


On the whole, I think allowing the server to inline SRFs would be a  
far

better way to go...


Maybe, but the highly-structured view definition syntax and  
everything that comes with it (dependancy tracking primarily) is so  
tasty. I think a little grammar hacking and a couple extra columns in  
pg_rewrite (nargs and argtypes) would get us most of the way there.


I would much rather put more stringent requirements on the programmer  
when defining his query (like a view), versus letting him submit any  
old string as a function (like a function). The database can do so  
much more when it's able to work with a better representation of the  
computation.


At the core, I want query rewriting with arguments. That sounds like  
a better fit for views/rules than functions, so that's why I keep  
coming back to it.


Thanks!

- Chris


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

Re: [HACKERS] Passing arguments to views

[Chris Campbell]

On Feb 3, 2006, at 08:50, Martijn van Oosterhout wrote:


Hmm, we actually do inline SQL functions under certain situations, but
only for "simple" queries (see inline_function in
optimizer/util/clauses.c). One approach would be to expand that
function to inline more complicated things.



* Better query execution performance because the planner can plan the
whole query (rewriting the original query to replace references to
the view with the view's definition -- this is how views work today)


Well, the inlining would acheive the same effect.


So you think approaching it from the "beefing up functions" side  
would be better than the "beefing up views" side?



* PostgreSQL-tracked dependancies: views create dependencies on the
relations they reference -- functions do not


Technically a bug. We should be tracking dependancies for functions
anyway.


Well, it's impossible to track dependencies for all functions, since  
they're just strings (or compiled code in shared libraries) until  
they're executed. But maybe SQL language functions could be special- 
cased? Do you think it would be easier to add dependancy-tracking for  
functions, or would it be easier to implement this functionality  
using the more-restrictive-language but better-dependency-tracking  
view system? When you add dependencies for things that didn't have  
dependencies before (like non-SQL functions), you create all sorts of  
backwards-compatibility problems due to the ordering that things need  
to be dumped and created, and circular dependancies.


For example, this works:

CREATE FUNCTION foo(INTEGER) RETURNS INTEGER AS 'BEGIN RETURN bar 
($1-1); END;' LANGUAGE plpgsql;


CREATE FUNCTION bar(INTEGER) RETURNS INTEGER AS 'BEGIN IF $1 < 0  
THEN RETURN $1; ELSE RETURN foo($1); END IF; END;' LANGUAGE plpgsql;


But it wouldn't work if PostgreSQL tracked and enforced dependancies.  
But it could probably be done with SQL-language functions only. I  
don't know if we'd want to add dependancy tracking for functions if  
it only works for SQL-language functions, though.


This is a good point. Though with syntactic sugar you could work  
around

this too...


Basically, how views do it? :) By auto-creating a table with the  
proper columns (for a function, that would be an auto-created type).


I'm looking for a function/view hybrid, taking features from each. It  
seems to me that views have most of the features I want (only missing  
the ability to pass arguments), so it's a shorter distance to the  
goal than by starting with functions.


Thanks!

- Chris


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

Re: [HACKERS] Multiple logical databases

[Chris Campbell]

On Feb 3, 2006, at 08:05, Mark Woodward wrote:


Using the "/etc/hosts" file or DNS to maintain host locations for is a
fairly common and well known practice, but there is no such  
mechanism for
"ports." The problem now becomes a code issue, not a system  
administration

issue.


What if you assigned multiple IPs to a machine, then used ipfw (or  
something) to forward connections to port 5432 for each IP to the  
proper IP and port?


You could use /etc/hosts or DNS to give each IP a host name, and use  
it in your code.


For example (this only does forwarding for clients on localhost, but  
you get the idea), you could set up:


Host  IP:port  Forwards to
  ---  -
db_one127.0.1.1:5432   192.168.1.5:5432
db_two127.0.1.2:5432   192.168.1.6:5432
db_three  127.0.1.3:5432   192.168.1.6:5433
fb_four   127.0.1.4:5432   16.51.209.8:8865

You could reconfigure the redirection by changing the ipfw  
configuration -- you wouldn't change your client code at all. It  
would continue to use a connection string of "... host=db_one", but  
you'd change 127.0.1.1:5432 to forward to the new IP/port.


Or use pgpool. :)

- Chris


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

  http://archives.postgresql.org

Re: [HACKERS] Passing arguments to views

[Chris Campbell]

On Feb 3, 2006, at 02:09, Tino Wildenhain wrote:


Well if the view does not suit your needs, why dont you use an
set returnung function instead? Inside it you can do all the magic
you want and still use it similar to a table or view.


That's what I'm currently doing (as explained in the first message in  
the thread). But the function is a "black box" to the planner when  
the query is executed -- I'd like the planner to be able to combine  
the query inside the function with the outer calling query and plan  
it as one big query. Like it does with views. Thus, "views with  
arguments."


We're certainly not deficient in this area (set-returning functions  
fill the need quite well), but a feature like this would go even  
further in terms of ease-of-use and performance.


Benefits of "views with arguments" versus functions:

* Better query execution performance because the planner can plan the  
whole query (rewriting the original query to replace references to  
the view with the view's definition -- this is how views work today)


* PostgreSQL-tracked dependancies: views create dependencies on the  
relations they reference -- functions do not


* Don't have to manually maintain a composite type for the return value

Basically, better performance and easier administration.

Thanks!

- Chris


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

Re: [HACKERS] Passing arguments to views

[Chris Campbell]

On Feb 2, 2006, at 23:33, Greg Stark wrote:

The "right" way to go about this in the original abstract set- 
theoretic
mindset of SQL is to code the view to retrieve all the rows and  
then apply

further WHERE clause restrictions to the results of the view.

So for example this:


CREATE VIEW sales_figures($1, $2) AS
SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;


Becomes:

CREATE VIEW sales_figures AS SELECT ... FROM ...

And then you query it with

SELECT * FROM sales_figures WHERE purchase_date BETWEEN $1 AND $2


That was a very simplistic example and didn't illustrate my point --  
I apologize. I was trying to think of something succinct and  
illustrative for a quick mailing list post but came up short.


Maybe a better example would be a situation where you want to do  
substitutions in places other than the WHERE clause? There's no way  
to "push" that out to the calling query. But even in this simple  
case, the easier-to-grok syntax of making a view look like a function  
(and codifying the options for restricting the results as arguments  
to the view) is a nice win in terms of readability and maintainability.


I was hoping that people would overlook my bad example because  
they've had the need for a "view with arguments" tool in their own  
work, and the conversation would just be about how it could be  
implemented. :)


I'll try to distill a better example from some of the projects I'm  
working on.


Thanks!

- Chris


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

[HACKERS] Passing arguments to views

[Chris Campbell]

I've written some complicated queries that I'd like to save inside  
the server and then call from my clients using a short name. For the  
queries that require no external information, views are perfect. For  
queries that *do* require external information (like a search date  
range), I've used functions (written in the SQL language, because I'm  
just writing plain SQL queries but using $1, $2 tokens for passed-in  
arguments).


When I use these functions, I'm typically joining the results of the  
function with other tables. Since much of the work is being done  
inside the function, the planner can't use both the inside-function  
and outside-function query information when generating a query plan.  
Instead, it has to do Function Scans (planning and executing the SQL  
inside the function at each execution, I'm assuming) and then  
manipulate the output.


Ideally, I'd like to be able to write queries that contain $n tokens  
that will be substituted at execution time, save them on the server,  
and let the query planner plan the whole query before it's executed.


Basically, writing views with arguments.

For example, a "sales_figures" view that requires start_date and  
end_date parameters could be used like this:


   CREATE VIEW sales_figures($1, $2) AS
   SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;

   SELECT * FROM sales_figures('2005-08-22', '2005-09-14') sf JOIN  
customers c ON (sf.customer_id = c.customer_id)


What do you think? Is this an interesting feature? Is this the right  
way to go about it, or should I try to get the planner to see through  
SQL function boundaries (e.g., enhance the function system instead of  
enhancing the view system)? Would this be a good project for a newbie  
to the code?


I can see that the syntax used above would be problematic: how would  
it distinguish that from a call to a sales_figures() function? Any  
comments about alternative syntax would be welcome, too!


Thanks!

- Chris



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] autovacuum

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>> Hmm, yeah, sounds useful.  There's one implementation issue to notice
>> however, and it's that the autovacuum process dies and restarts for each
>> iteration, so there's no way for it to remember previous state unless
>> it's saved somewhere permanent, as the stats info is.
>
> I think you'd really need to remember the previous oldest XID on a
> per-table basis to get full traction out of the idea.  But weren't
> we thinking of tracking something isomorphic to this for purposes of
> minimizing anti-wraparound VACUUMs?

I think I'd like that even better :-).

In the Slony-I case, the tables being vacuumed are ones where the
deletion is taking place within the same thread, so that having one
XID is plenty enough because the only thing that should be touching
the tables is the cleanup thread, which is invoked every 10 minutes.
One XID is enough "protection" for that, as least as a reasonable
approximation.

Tracking just the one eldest XID is still quite likely to be
*reasonably* useful with autovacuum, assuming there isn't a by-table
option.  By-table would be better, though.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/sgml.html
"Politics  is not a  bad  profession.  If  you succeed there  are many
rewards, if you disgrace yourself you can always write a book."
-- Ronald Reagan

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

Re: [HACKERS] autovacuum

[Chris Browne]

[EMAIL PROTECTED] (Alvaro Herrera) writes:

> Chris Browne wrote:
>
>> It strikes me as a slick idea for autovacuum to take on that
>> behaviour.  If the daily backup runs for 2h, then it is quite futile
>> to bother vacuuming a table multiple times during that 2h period when
>> none of the tuples obsoleted during the 2h period will be able to be
>> cleaned out until the end.
>
> Hmm, yeah, sounds useful.  There's one implementation issue to notice
> however, and it's that the autovacuum process dies and restarts for each
> iteration, so there's no way for it to remember previous state unless
> it's saved somewhere permanent, as the stats info is.

Hmm.  It restarts repeatedly???  Hmmm...

> However this seems at least slightly redundant with the "maintenance
> window" feature -- you could set a high barrier to vacuum during the
> daily backup period instead.  (Anybody up for doing this job?)

In effect, this would be an alternative to the "window" feature.  You
open the window by starting pg_dump; pg_autovacuum would automatically
notice that as the eldest XID, and stop work until the pg_dump
actually finished.

In a way, it strikes me as more elegant; it would automatically notice
"backup windows," noticing *exact* start and end times...
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://www.ntlug.org/~cbbrowne/linuxxian.html
"I'm sorry,  Mr.   Kipling, but you  just  don't know how to   use the
English Language."  -- Editor of the San Francisco Examiner, informing
Rudyard Kipling, who had one  article published in the newspaper, that
he needn't bother submitting a second, 1889

---(end of broadcast)---
TIP 1: 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] autovacuum

[Chris Browne]

matthew@zeut.net ("Matthew T. O'Connor") writes:
> Legit concern.  However one of the things that autovacuum is supposed to
> do is not vacuum tables that don't need it.  This can result in an overal
> reduction in vacuum overhead.  In addition, if you see that autovacuum is
> firing off vacuum commands during the day and they are impacting your
> response time, then you can play with the vacuum cost delay settings that
> are design to throttle down the IO impact vacuum commands can have.  In
> addition if you use 8.1, you can set per table thresholds, per table
> vacuum cost delay settings, and autovacuum will respect the work done by
> non-autovacuum vacuum commands.  Meaning that if you manually vacuum
> tables at night during a maintenance window, autovacuum will take that
> into account.  Contrib autovacuum couldn't do this.
>
> Hope that helps.  Real world feed-back is always welcome.

I have a question/suggestion...

Something we found useful with Slony-I was the notion of checking the
eldest XID on the system to see if there was any point at all in
bothering to vacuum.  I don't see anything analagous in autovacuum.c;
this might well be a useful addition.

In the Slony-I cleanup thread loop, we collect, in each iteration, the
current earliest XID.

In each iteration of this loop, we check to see if that XID has
changed.

- First time thru, it changes from 0 to 'some value' and so tries to do
  a vacuum.

- But supposing you have some long running transaction (say, a pg_dump
  that runs for 2h), it becomes pretty futile to bother trying to
  vacuum things for the duration of that transaction, because that
  long running transaction will, via MVCC, hold onto any old tuples.

It strikes me as a slick idea for autovacuum to take on that
behaviour.  If the daily backup runs for 2h, then it is quite futile
to bother vacuuming a table multiple times during that 2h period when
none of the tuples obsoleted during the 2h period will be able to be
cleaned out until the end.

Presumably this means that, during that 2h period, pg_autovacuum would
probably only issue ANALYZE statements...
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/languages.html
Rules of  the Evil Overlord #51.  "If one of my  dungeon guards begins
expressing  concern over  the  conditions in  the beautiful  princess'
cell,  I  will immediately  transfer  him  to  a less  people-oriented
position." 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Surrogate keys

[Chris Browne]

[EMAIL PROTECTED] writes:

> On Thu, Jan 19, 2006 at 09:37:12AM -0500, Pollard, Mike wrote:
>> Martijn van Oosterhout wrote:
>> > Please provides natural keys for any of the following:
>> > - A Person
>> > - A phone call: (from,to,date,time,duration) is not enough
>> > - A physical address
>> > - A phone line: (phone numbers arn't unique over time)
>> > - An internet account: (usernames not unique over time either)
>> Ahh, a challenge.  Hmm, not sure about all of them, but here goes:
>> A Person - well, you could use a bit map of their fingerprints, or maybe
>> their retinal scan.  Of course, that could change due to serious injury.
>> Maybe some kind of representation of their DNA?
>
> Yes. Representation of the DNA is probably best. But - that's a lot of
> data to use as a key in multiple tables. :-)

That is arguably about the best choice possible, for a human being, as
DNA isn't supposed to be able to change (much).  

Mind you, there do exist odd cases where a person might have two sets
of DNA in different parts of their body.  This commonly (well, it's
not really very common...) happens when non-identical twins share a
blood supply; that twins were involved may not be noticed if one does
not survive to birth...

>> A physical address - how about longitude/latitude/height from sea level?
>
> Planet? Solar system? Galaxy? Universe? :-)
>
> I agreed with what you had to say (the stuff I deleted). Just felt
> like being funny. Not sure if I'm successful. Hehe...

Well, that's useful for representing a key for a piece of real estate.

It's fairly interestingly useless for representing a human attribute,
at least in terms of being a primary key...
-- 
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/linux.html
"It's a pretty rare beginner who isn't clueless.  If beginners weren't
clueless, the infamous Unix learning cliff wouldn't be a problem."
-- david parsons

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

Re: [HACKERS] Automatic function replanning

[Chris Browne]

Lukas Smith <[EMAIL PROTECTED]> writes:
> Bruce Momjian wrote:
>
>>  * Flush cached query plans when the dependent objects change,
>>when the cardinality of parameters changes dramatically, or
>>when new ANALYZE statistics are available
>
> Wouldn't it also make sense to flush a cached query plan when after
> execution it is determined that one or more assumptions that the
> cached query plan was based on was found to be off? Like the query
> plan was based on the assumption that a particular table would only
> return a hand full of rows, but in reality it returned a few
> thousand.

There is some merit to that.

I could also see it being sensible to flush a cached plan any time the
query took more than some [arbitrary/GUC-chosen] interval.

Supposing it took 20s to execute the query, it would surely seem
surprising for re-evaluating the plan to be expected to make up a
material proportion of the cost of the *next* invocation.

If we flush every plan that took >10s to evaluate, that offers the
possibility for it to be done better next time...
-- 
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/internet.html
Points  are awarded for   getting   the last   word in.   Drawing  the
conversation out so long  that the original  message disappears due to
being indented off the right hand edge of the screen is  one way to do
this.  Another is to imply that  anyone replying further is a hopeless
cretin and is wasting everyone's valuable time.
-- from the Symbolics Guidelines for Sending Mail

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

Re: [HACKERS] Replication on the backend

[Chris Browne]

[EMAIL PROTECTED] (Gustavo Tonini) writes:
> But,  wouldn't the performance be better? And wouldn't asynchronous
> messages be better processed?

Why do you think performance would be materially affected by this?

The MAJOR performance bottleneck is normally the slow network
connection between servers.

When looked at in the perspective of that bottleneck, pretty much
everything else is just noise.  (Sometimes pretty loud noise, but
still noise :-).)
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/spreadsheets.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."  
-- Microsoft Word for Windows 2.0 User's Guide, p.35:

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

   http://archives.postgresql.org

Re: [HACKERS] Replication on the backend

[Chris Browne]

[EMAIL PROTECTED] (Gustavo Tonini) writes:
> What about replication or data distribution inside the backend.  This
> is a valid issue?

I'm not sure what your question is...
-- 
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/x.html
"Love is like a snowmobile flying over the frozen tundra that suddenly
flips, pinning you underneath.  At night, the ice weasels come."
-- Matt Groening

---(end of broadcast)---
TIP 1: 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] [WIN32] Quiet install and changing defaults

[Chris Gow]

Magnus Hagander wrote:

  
I am attempting to override the default installation 
directory that the postgres MSI uses. I'm not familiar with 
MSI, but from what I've read I can specify properties on the 
command line to change properties in the installer? I've 
tried a variety of properties from looking at the 
installation log and by examining the properties using the 
ORCA tool from Microsoft. All to no avail:

msiexec /i postgresql-8.1.msi SOURCEDIR=[my directory] 
msiexec /i postgresql-8.1.msi TARGETDIR=[my directory] 
msiexec /i postgresql-8.1.msi DEVDIR=[my directory]

In all cases the installer still presents me with the default 
directory (c:\Program Files\Postgres8.1) and not the one I 
specify on the command line. Should some combination of the 
above work?

My final goal is to be able to spawn the pgsql installer from 
my installer and override defaults for the user and password 
and installation directory. Is this feasible?

Hopefully this is the right list, if not if someone could 
direct me to the correct one, I'd appreciate it.

  
  
This is documented on
http://pginstaller.projects.postgresql.org/silent.html. THe property
you're looking for is BASEDIR. You'll also need to send it to
postgresql-8.1-int.msi - this is also documented on the page above.

//Magnus

  

Excellent!

Thanks for the info!

-- chris

[HACKERS] [WIN32] Quiet install and changing defaults

[Chris Gow]

Hello:


I am attempting to override the default installation directory that the 
postgres MSI uses. I'm not familiar with MSI, but from what I've read I 
can specify properties on the command line to change properties in the 
installer? I've tried a variety of properties from looking at the 
installation log and by examining the properties using the ORCA tool 
from Microsoft. All to no avail:


msiexec /i postgresql-8.1.msi SOURCEDIR=[my directory]
msiexec /i postgresql-8.1.msi TARGETDIR=[my directory]
msiexec /i postgresql-8.1.msi DEVDIR=[my directory]

In all cases the installer still presents me with the default directory 
(c:\Program Files\Postgres8.1) and not the one I specify on the command 
line. Should some combination of the above work?


My final goal is to be able to spawn the pgsql installer from my 
installer and override defaults for the user and password and 
installation directory. Is this feasible?


Hopefully this is the right list, if not if someone could direct me to 
the correct one, I'd appreciate it.


Please CC me as I am not currently subscribed to this list.


thanks

-- chris

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

  http://archives.postgresql.org

Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond

[Chris Browne]

[EMAIL PROTECTED] (Gavin Sherry) writes:

> Hi,
>
> On Tue, 14 Nov 2005 [EMAIL PROTECTED] wrote:
>
>> > Gavin Sherry:
>> > Grouping sets
>> > Recursive queries
>>
>> The recursive queries is a long-awaited feature. Does the fact that
>> the feature is listed for Gavin Sherry mean that Gavin is actually
>> working with the feature at the moment? Does anybody know the
>> current state of this feature or know when it will be public
>> available?
>
> I recall suggesting these features as being amongst those in
> demand. I don't remember saying that I'd actually do them...

Jonah Harris appears to be working on the Recursive Queries side of
it...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/sap.html
"I visited  a company  that was doing  programming in BASIC  in Panama
City and I asked them if they resented that the BASIC keywords were in
English.   The answer  was:  ``Do  you resent  that  the keywords  for
control of actions in music are in Italian?''"  -- Kent M Pitman

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] PG 8.1 supported platforms list

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> The 8.1 supported-platforms list is looking pretty good, I think -- we
> don't have updates for every single combination of OS and hardware,
> but we have updates for every OS and at least one instance of all
> supported CPU types.

Not to pester overly...

AIX 5.3 has had results reported, and entries for doc/FAQ_AIX
submitted; none of that is yet included :-(.
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/linux.html
"No, I'm not interested in developing a powerful brain.  All I'm after
is just  a mediocre  brain, something like  the president  of American
Telephone and Telegraph Company."  -- Alan Turing on the possibilities
of a thinking machine, 1943.

---(end of broadcast)---
TIP 1: 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] slru.c race condition

[Chris Browne]

[EMAIL PROTECTED] (Alvaro Herrera) writes:

> Chris Browne wrote:
>> [EMAIL PROTECTED] ("Jim C. Nasby") writes:
>> 
>> > On Tue, Nov 01, 2005 at 11:23:55AM -0300, Alvaro Herrera wrote:
>> >> Tom Lane wrote:
>> >> > "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
>> >> > > AFAIK they're not using subtransactions at all, but I'll check.
>> >> > 
>> >> > Well, yeah, they are ... else you'd never have seen this failure.
>> >> 
>> >> Maybe it's in plpgsql EXCEPTION clauses.
>> >
>> > Err, I forgot they're using Slony, which is probably using savepoints
>> > and/or exceptions.
>> 
>> Slony-I does use exceptions in pretty conventional ways; it does *not*
>> make any use of subtransactions, because it needs to run on PG 7.3 and
>> 7.4 that do not support subtransactions.
>
> Hmm, does it use the BEGIN/EXCEPTION/END construct at all?  Because if
> it does, it won't work on 7.4; and if it doesn't, then it isn't using
> savepoints in 8.0 either.

Ah, then I was misreading that.  

There are instances of RAISE EXCEPTION, which was what I had in mind,
but not of BEGIN/EXCEPTION/END.

There is some logic in 8.x to *detect* the nesting of transactions,
but that's quite another matter.
-- 
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/multiplexor.html
"If I  could find  a way to  get [Saddam  Hussein] out of  there, even
putting a  contract out on him,  if the CIA  still did that sort  of a
thing, assuming it ever did, I would be for it."  -- Richard M. Nixon

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

Re: [HACKERS] slru.c race condition

[Chris Browne]

[EMAIL PROTECTED] ("Jim C. Nasby") writes:
> AFAIK they're not using subtransactions at all, but I'll check.

Are they perchance using pl/PerlNG?

We discovered a problem with Slony-I's handling of subtransactions
which was exposed by pl/PerlNG, which evidently wraps its SPI calls
inside subtransactions.

For more details...


That is the only subtransaction issue I am aware of...
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/nonrdbms.html
:FATAL ERROR -- ERROR IN ERROR HANDLER

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] slru.c race condition

[Chris Browne]

[EMAIL PROTECTED] ("Jim C. Nasby") writes:

> On Tue, Nov 01, 2005 at 11:23:55AM -0300, Alvaro Herrera wrote:
>> Tom Lane wrote:
>> > "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
>> > > AFAIK they're not using subtransactions at all, but I'll check.
>> > 
>> > Well, yeah, they are ... else you'd never have seen this failure.
>> 
>> Maybe it's in plpgsql EXCEPTION clauses.
>
> Err, I forgot they're using Slony, which is probably using savepoints
> and/or exceptions.

Slony-I does use exceptions in pretty conventional ways; it does *not*
make any use of subtransactions, because it needs to run on PG 7.3 and
7.4 that do not support subtransactions.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"I can't believe my room doesn't have Ethernet!  Why wasn't it wired
when the house was built?"
"The house was built in 1576." 
-- Alex Kamilewicz on the Oxford breed of `conference American.'

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

   http://archives.postgresql.org

Re: [HACKERS] 8.1 Release Candidate 1 Coming ...

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes:
>> hmm well -HEAD(and 8.0.4 too!) is broken on AIX 5.3ML3:
>> http://archives.postgresql.org/pgsql-hackers/2005-10/msg01053.php
>
> [ shrug... ]  The reports of this problem have not given enough
> information to fix it, and since it's not a regression from 8.0,
> it's not going to hold up the 8.1 release.  When and if we receive
> enough info to fix it, we'll gladly do so, but ...

Well, we never had an AIX 5.3 system when 8.0 was released, so didn't
attempt a compile.  Seneca just tried out a build on 8.0.3 on AIX 5.3;
it appears to be experiencing the same problem with initdb, and a
slight modification of the previous "fix" appears to resolve the
issue.

Can you suggest what further we might provide that would help?

> (My guess is that the problem is a compiler or libc bug anyway,
> given that one report says that replacing a memcpy call with an
> equivalent loop makes the failure go away.)

It seems unlikely to be a compiler bug as the same issue has been
reported with both GCC and IBM XLC.  I could believe it being a libc
bug...

It would be terribly disappointing to have to report both internally
and externally that AIX 5.3 is not a usable platform for recent
releases of PostgreSQL...
-- 
"cbbrowne","@","ntlug.org"
http://cbbrowne.com/info/linuxdistributions.html
Never lend your car to anyone  to whom you have given birth to. 
--Erma Bombeck

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [Slony1-general] Slony1_funcs broken with 8.1

[Chris Browne]

[EMAIL PROTECTED] (Andreas Pflug) writes:
> Tom Lane wrote:
>> Andreas Pflug <[EMAIL PROTECTED]> writes:
>>
>>> So postmaster doesn't clean up pg_listener,
>> It never has.  If you're complaining about this patch
>> http://archives.postgresql.org/pgsql-committers/2005-10/msg00073.php
>> you ought to say so, rather than expecting us to guess it from an
>> out-of-context quote from another mailing list.
>
> Not complaining, just RFC.

It looks as though we can accomplish an equivalent pretty readily.

create table sl_nodelock (
  nl_node integer not null unique;
  nl_pid  integer not null;
);

The "interlock" could be accomplished via the following bit of sorta
pseudocode...

try {
  execute "insert into sl_nodelock (nl_node, nl_pid) values (getlocalnodeid, 
pg_backend_pid());";
} on error {
  execute "SELECT pg_stat_get_backend_pid(s.backendid) AS procpid  FROM (SELECT 
pg_stat_get_backend_idset() AS backendid) AS s where procpid = $OTHERPID;";
  if (not_found) {
 printf ("Found dead slon connection info - cleaning it out!\n");
 execute "delete from sl_nodelock;";
  } else {
printf ("Could not start node N - other slon still running!");
  }
  exit -1;
}

> But I wonder why postmaster doesn't truncate pg_listener at restart,
> since PIDs can't be valid any more (truncating would reduce bloating
> too). A redesign based on shmem or so wouldn't keep the data either.

Truncating things at restart just feels scary, even though it is, in
this case, pretty appropriate.  I'd rather see the shmem redisgn...

>> As near as I can tell, the technique Jan describes is an abuse of
>> pg_listener, and I won't feel any great sympathy when it does break
>> completely, which it will do before long when pg_listener goes away
>> in the planned rewrite of LISTEN/NOTIFY.
>
> Well slony uses LISTEN for its main purpose too. I'd guess there's
> always a demand to find out which backend is listening, so a
> pg_listener (probably a view wrapping a function) will be necessary.

There are two usages of LISTEN in Slony-I; the other one needs to get
improved, as well, as the event propagation system generates way too
many dead tuple entries at present.  I have a patch that easily cuts
it by about half; the other half seems pretty doable too...

> AFAICS a backend that notices loss of client connection will usually
> clean up its listener entries, so apparently slony doesn't need to
> take care of this, at least for 8.1 (with the postmaster crash
> exception).

Interesting.
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/rdbms.html
Rules  of the Evil  Overlord #53.  "If the  beautiful princess  that I
capture says "I'll never marry  you! Never, do you hear me, NEVER!!!",
I will say "Oh well" and kill her." 

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

   http://www.postgresql.org/docs/faq

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Chris Travers]

Dann Corbit wrote:


Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.
 

IIrc, varchar and bpchar are stored in a similar way, but are presented 
differently when retrieved.  I.e. storage is separate from presentation 
in this case.  I.e. the padding in bpchar occurs when it is presented 
and stripped when it is stored.


Again, I am happy "solving" this simply by documenting it since any 
questions of interpretation and implimentation of the standard would be 
answered.  So far what I (and I am sure others) have not heard is a 
strong case for changing the behavior, given that it is in line with a 
reasonable interpretation of the standards.



Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the same
length before performing the comparison.
 

Understood, but what gain do you have in a case like this that might 
justify the effort that would go into making it, say, an initdb option?  
How often does this behavior cause problems?


Best Wishes,
Chris Travers
Metatron Technology Consulting

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Chris Travers]

Josh Berkus wrote:


Dann,

 


I think that whatever is done ought to be whatever the standard says.
If I misinterpret the standard and PostgreSQL is doing it right, then
that is fine.  It is just that PostgreSQL is very counter-intuitive
compared to other database systems that I have used in this one
particular area.  When I read the standard, it looked to me like
PostgreSQL was not performing correctly.  It is not unlikely that I read
it wrong.
   



AFAIT, the standard says "implementation-specific".   So we're standard.

The main cost for comparing trimmed values is performance; factoring an 
rtrim into every comparison will add significant overhead to the already 
CPU-locked process of, for example, creating indexes.  We're looking for 
ways to make the comparison operators lighter-weight, not heavier.
 

If I understand the spec correctly, it seems to indicate that this is 
specific to the locale/character set.  Assuming that the standard 
doesn't have anything to do with any character sets, it should be 
possible to make this available for those who want it as an initdb 
option.  Whether or not this is important enough to offer or not is 
another matter.


Personally my questions are:

1)  How many people have been bitten by this badly?
2)  How many people have been bitten by joins that depend on padding?

Personally, unlike case folding, this seems to be an area where a bit of 
documentation (i.e. all collation sets have are assumed to have the NO 
PAD option in the SQL standard) would be sufficient to answer to 
questions of standards-compliance.


My general perspective on this is that if trailing blanks are a significant 
hazard for your application, then trim them on data input.  That requires 
a *lot* less peformance overhead than doing it every time you compare 
something.  
 

In general I agree.  But I am not willing to jump to the conclusion that 
it will never be warranted to add this as an initdb option.  I am more 
interested in what cases people see where this would be required.  But I 
agree that the bar is much higher than it is in many other cases.


Best Wishes,
Chris Travers
Metatron Technology Consulting

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

Re: [HACKERS] Vacuum and Transactions

[Chris Browne]

[EMAIL PROTECTED] (Hannu Krosing) writes:
> It also seems that Slony can be modified to not use LISTEN/NOTIFY in
> high load situations (akin to high performance network cards, which
> switch from interrupt driven mode to polling mode if number of packets
> per second reaches certain thresolds).

Yeah, I want to do some more testing of that; it should be easy to
improve the "abuse" of pg_listener a whole lot.

> Unfortunately Slony and Listen/Notify is not the only place where
> high- update rate tables start to suffer from vacuums inability to
> clean out dead tuples when working in parallel with other slower
> vacuums. In real life there are other database tasks which also need
> some tables to stay small, while others must be huge in order to
> work effectively. Putting small and big tables in different
> databases and using dblink-like functionality when accessing them is
> one solution for such cases, but it is rather ugly :(

That eliminates the ability to utilize transactions on things that
ought to be updated in a single transaction...
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/lsf.html
MS-Windows: Proof that P.T. Barnum was correct. 

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

Re: [HACKERS] State of support for back PG branches

[Chris Browne]

[EMAIL PROTECTED] ("Marc G. Fournier") writes:
> On Mon, 26 Sep 2005, Josh Berkus wrote:
>
>> Tom,
>>
>>> Or, as you say, we could take the viewpoint that there are commercial
>>> companies willing to take on the burden of supporting back releases, and
>>> the development community ought not spend its limited resources on doing
>>> that.  I'm hesitant to push that idea very hard myself, because it would
>>> look too much like I'm pushing the interests of my employer Red Hat
>>> ... but certainly there's a reasonable case to be made there.
>>
>> Well, I think you know my opinion on this.  Since there *are*
>> commercial companies available, I think we should use them to reduce
>> back-patching effort.   I suggest that our policy should be:  the
>> community will patch two old releases, and beyond that if it's
>> convenient, but no promises. In other words, when 8.1 comes out we'd
>> be telling 7.3 users "We'll be patching this only where we can apply
>> 7.4 patches.  Otherwise, better get a support contract."
>>
>> Of course, a lot of this is up to individual initiative; if someone
>> fixes a patch so it applies back to 7.2, there's no reason not to
>> make it available. However, there's no reason *you* should make it a
>> priority.
>
> Agreed ... "if its convient/easy to back patch, cool ... but don't go
> out of your way to do it" ...

We're looking at Slony-I the same way.

The earliest version it ever did support was 7.3.4.

Some effort has had to go into making sure it continues to support
7.3.x, and, as of today's check-ins, there is *some* functionality
which is lost if you aren't running at least 7.4.

At some point, it will make sense to drop 7.3 support, but since
Slony-I has, as a common use-case, assisting to upgrade to newer
versions, I'm loathe to drop it arbitrarily.

One happy part of that is that it doesn't mean that 7.3 becomes
*totally* unsupported, as major releases such as 1.0.5 and 1.1.0 *do*
support it, and I wouldn't feel horribly badly if direct support
ceased in 1.2 as long as this left people with old databases the
option of using Slony-I 1.1 to upgrade from PG 7.3 to 8.1, at which
point they could get Newer, Better Slony-I 1.3 stuff via upgrading
just on the 8.1 instances.

Of course, there hasn't been anything *SO* substantial changed that it
has become tempting enough to drop 7.3 support.  There have
occasionally been suggestions to add some 8.0-specific functionality;
when plenty of people are still using 7.4, that just doesn't tempt
:-).
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/linux.html
"There is   nothing in the world  more  helpless and irresponsible and
depraved than a man in the depths of an ether binge."
-- Dr. Hunter S. Thompson

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] State of support for back PG branches

[Chris Browne]

[EMAIL PROTECTED] (Steve Atkins) writes:
> We started our upgrade from 7.2 to 7.4 about 20 months ago and finished it
> about 10 months ago, skipping 7.3 entirely.

We did similar; there was only one system deployed in a timeframe
where 7.3 was relevant, and the "big systems" skipped over 7.3 much as
you did.

> We've only just today hit our first problem in 7.4, and it's fixed by
> upgrading to 7.4.current, rather than the 7.4.something we originally
> upgraded to from 7.2.something.

Ditto, to a degree; we hit a pretty funky index update thing that was
resolved in 7.4.8.

> We'll be skipping 8.0 completely and the next step will probably be to
> 8.1.something (or possibly 8.2.something, depending on how bizgres
> looks in 3 months time). We'd probably consider upgrading our
> customers more often, but a dump and restore is extremely painful.

We're strategizing somewhat similarly, save for bizgres not being on
our roadmap.

Dump and restore isn't forcibly necessary; we did the 7.2 to 7.4
upgrade via eRServer, and made sure that Slony-I was designed to
support upgrades.

I recently did an application upgrade (not a PG version change) using
Slony-I; replicated to a node that wasn't otherwise busy, and used
that node as the "base" where various tables were transformed into
their new forms.  Replicated the "new forms" on everywhere.  On the
"flag day," a MOVE SET shifted mastery, MERGE SET pasted things
together, and EXECUTE SCRIPT finished the transformation.

We're starting to look at 8.1, and would *certainly* use Slony-I to
perform that upgrade.  The "on the cheap" method would involve
replacing nodes one at a time with 8.1 versions, though we'd more
likely have a bunch of 7.4 nodes running parallel with a corresponding
set of 8.1 nodes, and, once done, drop all the 7.4 ones at once...

> Just a view from the pg-based-enterprise-application world.
>
> A nice pg_upgrade utility would make a big difference. Clearly an
> in-place upgrade is possible, but maintaining is hard. There are two
> broad ways of running a pg_upgrade project - one that is entirely
> independent of the main codebase and one that puts requirements on
> the main codebase developers ("if you change $foo you provide code
> to translate old $foo to new $foo"). Any feel for the relative
> difficulty of the two approaches? And how much push-back there'd be
> on the latter?

This strikes me as being only marginally easier than the proverbial
desires for tools to convert ext2 to XFS or ReiserFS.

The conversion tool would have to encode a lot of hairy details, and
would require that the likes of Tom Lane and Bruce Momjian spend a lot
of their time writing the conversion tool instead of working on new
features.

With filesystems, it seems easier and cheaper to buy an extra disk
drive (what, $200?) and use something like rsync/unison to relatively
efficiently replicate the filesystem.

Slony-I is the PostgreSQL equivalent to rsync/unison, in this case.

Or you could look at Mammoth Replicator, if you prefer...

The replication approach allows Tom and Bruce to work on sexy new
features instead of forcing them into the data conversion mould...
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/slony.html
It's always darkest just before it gets pitch black.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] R: feature proposal ...

[Chris Browne]

[EMAIL PROTECTED] ("Joshua D. Drake") writes:
> Hans-Jürgen Schönig wrote:
>> no because a new is not a heap ...
>
> Why not use a function with a temporary table?
>
> That way you can pass a table parameter that
> is the temporary table with a select statement
> that you can populate the temp table with.

That means having to instantiate the temp table on disk "twice," once
as temp table, and once as the output file.

It would sure be nice to do it just once; that should lead to there
only being data written out once, which saves a lot on I/O.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/spiritual.html
Rules  of  the Evil  Overlord  #59. "I  will  never  build a  sentient
computer smarter than I am." 

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] count(*) optimization

[Chris Browne]

[EMAIL PROTECTED] (huaxin zhang) writes:
> not sure where to put this. 
>
> I run two queries: 
>
> 1. select count(*) from table where indexed_column<10;
> 2. select * from table where indexed_column<10;
>
> the indexed column is not clustered at all. I saw from the trace
> that both query runs through index scans on that index and takes the
> same amount of buffer hits and disk read. 

> However, shouldn't the optimizer notice that the first query only
> needs to look at the indexes and possibly reduce the amount of
> buffer/disk visits?

No, it shouldn't, because that is NOT TRUE.

Indexes do not have MVCC visibility information stored in them, so
that a query cannot depend on the index to imply whether a particular
tuple is visible or not.  It must read the tuple itself as well.
-- 
output = ("cbbrowne" "@" "acm.org")
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
"I promise you a police car on every sidewalk."
-- M. Barry Mayor of Washington, DC

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] 4D Geometry

[Chris Traylor]

On Mon, 2005-09-05 at 20:40 -0400, Tom Lane wrote:


Chris Traylor <[EMAIL PROTECTED]> writes:
> On Mon, 2005-09-05 at 15:27 -0400, Tom Lane wrote:
>> I'd suggest keeping these as separate private types rather
>> than expecting that a patch to replace the 2D types will be accepted.

> What do you think about making it a configure option, i.e.
> --enable-4D-geometry (default false)?

Configure options are generally a pain in the neck,


Granted. Especially, if all the ifdefs start making the source hard to read, but they are a viable compile-time way to allow the user to make the decision for themselves.



 particularly if they
cause significant changes in user-visible behavior.
  What's wrong with
creating separate types instead of changing the behavior of the existing
ones?


I'd really rather not write a mirror version of every geometric function, in order to use a private type.




			regards, tom lane

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





Chris

--
Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -- Mark Twain

Re: [HACKERS] 4D Geometry

[Chris Traylor]

On Mon, 2005-09-05 at 15:27 -0400, Tom Lane wrote:


Chris Traylor <[EMAIL PROTECTED]> writes:
> 1.) Is anyone else currently working on this?

No, and AFAIR no one has ever even asked for it.  I'm a little dubious
about doubling the storage requirements for geometry data and likely
creating backwards-compatibility issues to implement a feature that only
you need.  I'd suggest keeping these as separate private types rather
than expecting that a patch to replace the 2D types will be accepted.



What do you think about making it a configure option, i.e. --enable-4D-geometry (default false)? This way people who don't want/need the extra overhead don't have to deal with it, and those who want to use postgres for scientific/engineering/animation/etc apps (where 2D doesn't quite cut the mustard) can have it available to them. I was thinking that it would allow a whole new set of applications to take advantage of the fact that postgres provides native geometric types. After all, you can use just about any db engine to handle geometric data with traditional sql and stored procedures. The point of the builtins is so you have a standard set of algorithms, and that you don't have to constantly reinvent the wheel. Like I said in my earlier message, I can patch the source for myself, and go about my merry way. The geometry portions really don't seem to change very frequently (the differences between 8.0.3, and 8.1beta were minimal), and except for the line stuff, the changes were trivial, so personal maintenance shouldn't be a problem. I just thought I'd share my work.:-)




			regards, tom lane

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

   http://archives.postgresql.org





Chris

--
Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -- Mark Twain

Re: [HACKERS] 4D Geometry

[Chris Traylor]

On Sun, 2005-09-04 at 21:45 -0700, Paul Ramsey wrote:


Chris,

PostGIS already has 4d geometry, though few functions that work with  
that fourth dimension (how far is 8am, in Paris from 4pm in  
London?).  Have you checked if there is some room to meet your needs  
with some PostGIS enhancements?



I haven't checked it out, but definitely will. However, from your brief description, it appears that it uses clock time. If so, that won't meet my needs, as I'm more interested in ordinal time (t=0, t=1, etc). Also, part of the reason for my interest is that my application may need to deal with >4D in the future (if all goes well), and as we all know, 2D is cute, and fairly straightforward, but it rests upon some assumptions/shortcuts that just don't hold, when you start moving to more complex analysis.




Paul

On 4-Sep-05, at 6:55 PM, Chris Traylor wrote:

> Please excuse any stupidity, as although I've used postgres for  
> quite some time, this is my first foray into developing for it. I'm  
> working on converting the geometry stuff in adt to support 4  
> dimensions. For my own use, I plan on patching 8.0.3 with the files  
> I edited in the 8.1beta source, right away. I really ownly need the  
> ability to store 4D (I do all the geometry functions outside of the  
> db), so for the public, I'd really rather do this properly and  
> completely. Since, optimistically, this won't see the light of day  
> until 8.1.X/8.2, there's plenty of time to discuss/debate things.  
> Any and all questions/comments/criticisms are welcomed and  
> encouraged. Here are my questions.
>
> 1.) Is anyone else currently working on this?
>
> 2.) 75% of the changes were trivial and most of the remaining 25%  
> are complications due to the way "line" is implemented.   
> Particularly, the fact that it uses the 2D specific Ax + By + C =  
> 0, and not a vector style storage. Obviously, I would have to  
> change the line functions in geo_ops.c, and its spec in pg_type.h,  
> but I've noticed that it only seems to be used internally, so other  
> than those, I can't see any other changes that would be necessary.  
> Can anyone, more familiar with the source, think of any good  
> reasons that would make them leery of me changing the structure to  
> reflect the parametric form, to say Point *A, Point *B, double p.  
> [Normally, the parameter would be "t", but I call the 4th  
> coordinate "t", so I figured "p" would be a little less confusing.  
> Also A & B should be a vectors, but I'll get to that in a later  
> question.]
>
> 3.) As it stands now, I added support for the extra dimensions to  
> pair_encode, pair_decode, and pair_count. Do you think that it  
> would be better to:
> a.) leave the original signatures, and use those routines to  
> work with the old style (x,y) coordinates, and setting (z,t) to  
> (0,0), when necessary.
> b.) create a new set of functions called quad_encode,  
> quad_decode, and quad_count to work with the new (x,y,z,t)  
> coordinates, and use them in the code. I'm more thinking of outside  
> stuff, (i.e. libpqxx, etc), that might use/depend on those  
> signatures. I'm not sure if anything does, that's why I'm asking.  
> Also, I'm trying to look ahead for when people that already use the  
> geo types go to upgrade.
>
> 4.) If changing the signatures for these routines presents  
> problems, will the fact that I changed other signatures to support  
> the additional coordinates, also present any problems?
>
> 5.) As it stands now, I'm just using the Point structure to denote  
> vectors in component form, and LSEG for stpt-endpt form. Does  
> anyone see any reason I shouldn't do this. I realize that having a  
> separate VECTOR structure would probably be more readable, and  
> probably more useful, but it would more than likely be more work  
> initially.
>
> 6.) Are there any objections to breaking up geo_ops.c into separate  
> sources?
>
> 7.) Can anyone think of any issues that I'm missing?
>
>
> Chris
>
> --
> Sometimes I wonder whether the world is being run by smart people  
> who are putting us on or by imbeciles who really mean it. -- Mark  
> Twain





Chris

--
Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -- Mark Twain

[HACKERS] 4D Geometry

[Chris Traylor]

Please excuse any stupidity, as although I've used postgres for quite some time, this is my first foray into developing for it. I'm working on converting the geometry stuff in adt to support 4 dimensions. For my own use, I plan on patching 8.0.3 with the files I edited in the 8.1beta source, right away. I really ownly need the ability to store 4D (I do all the geometry functions outside of the db), so for the public, I'd really rather do this properly and completely. Since, optimistically, this won't see the light of day until 8.1.X/8.2, there's plenty of time to discuss/debate things. Any and all questions/comments/criticisms are welcomed and encouraged. Here are my questions. 

1.) Is anyone else currently working on this?

2.) 75% of the changes were trivial and most of the remaining 25% are complications due to the way "line" is implemented.  Particularly, the fact that it uses the 2D specific Ax + By + C = 0, and not a vector style storage. Obviously, I would have to change the line functions in geo_ops.c, and its spec in pg_type.h, but I've noticed that it only seems to be used internally, so other than those, I can't see any other changes that would be necessary. Can anyone, more familiar with the source, think of any good reasons that would make them leery of me changing the structure to reflect the parametric form, to say Point *A, Point *B, double p. [Normally, the parameter would be "t", but I call the 4th coordinate "t", so I figured "p" would be a little less confusing. Also A & B should be a vectors, but I'll get to that in a later question.]

3.) As it stands now, I added support for the extra dimensions to pair_encode, pair_decode, and pair_count. Do you think that it would be better to:
    a.) leave the original signatures, and use those routines to work with the old style (x,y) coordinates, and setting (z,t) to (0,0), when necessary.
    b.) create a new set of functions called quad_encode, quad_decode, and quad_count to work with the new (x,y,z,t) coordinates, and use them in the code. I'm more thinking of outside stuff, (i.e. libpqxx, etc), that might use/depend on those signatures. I'm not sure if anything does, that's why I'm asking. Also, I'm trying to look ahead for when people that already use the geo types go to upgrade. 

4.) If changing the signatures for these routines presents problems, will the fact that I changed other signatures to support the additional coordinates, also present any problems?

5.) As it stands now, I'm just using the Point structure to denote vectors in component form, and LSEG for stpt-endpt form. Does anyone see any reason I shouldn't do this. I realize that having a separate VECTOR structure would probably be more readable, and probably more useful, but it would more than likely be more work initially.

6.) Are there any objections to breaking up geo_ops.c into separate sources?

7.) Can anyone think of any issues that I'm missing? 





Chris

--
Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -- Mark Twain

Re: [HACKERS] [Slony1-general] Re: dangling lock information?

[Chris Browne]

[EMAIL PROTECTED] (Alvaro Herrera) writes:
> On Tue, Aug 30, 2005 at 12:45:18PM -0400, Chris Browne wrote:
>> [EMAIL PROTECTED] ("David Parker") writes:
>> > The slony log trigger saves execution plans, so any given
>> > connection that has been used with a slony schema installed will
>> > have cached OIDs referring to the sl_log_1 table. When you drop
>> > the schema, those OIDs obviously go away. When you re-create the
>> > schema, and try to use the old connection, it still has the old
>> > plan cached in it, so the OIDs in the plan are out of sync with
>> > what actually exists in the database.
>> >
>> > This is the behavior I've observed in our environment,
>> > anyway. The problem always shows up when slony is RE-installed
>> > under an outstanding connection.
>> 
>> I have observed much the same behaviour...
>> 
>> It would be really useful to have some guidance as to how to
>> resolve this.
>> 
>> What is needed is to invalidate the cached execution plans.
>
> The simplest way to do that is to disconnect the client, and start a
> fresh session.

I'm keen on a "simplest way" that doesn't essentially involve having
to restart the application...

>> Unfortunately, it's not at all obvious how to accomplish that :-(.
>
> I don't think it can be easily done with the current code.  This is
> plpgsql code, right?  There are some ways to cause recompilation for
> those, at least on the 8.1 code I'm looking at.

No, the troublesome parts are in C/SPI code.

If it's something Neil Conway hasn't quite figured out how to handle
yet, I don't feel so bad that I can't imagine a way to do it...  :-)
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/spiritual.html
A cool feature of OOP is that the simplest examples are 500 lines.  
-- Peter Sestoft

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] [Slony1-general] Re: dangling lock information?

[Chris Browne]

[EMAIL PROTECTED] ("David Parker") writes:
> The slony log trigger saves execution plans, so any given connection
> that has been used with a slony schema installed will have cached OIDs
> referring to the sl_log_1 table. When you drop the schema, those OIDs
> obviously go away. When you re-create the schema, and try to use the old
> connection, it still has the old plan cached in it, so the OIDs in the
> plan are out of sync with what actually exists in the database.
>
> This is the behavior I've observed in our environment, anyway. The
> problem always shows up when slony is RE-installed under an outstanding
> connection.

I have observed much the same behaviour...

It would be really useful to have some guidance as to how to resolve
this.

What is needed is to invalidate the cached execution plans.

Unfortunately, it's not at all obvious how to accomplish that :-(.

Alas, any time I touch the SPI code in other than relatively trivial
ways, it falls over and croaks :-(.
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/linuxdistributions.html
One good turn gets most of the blankets. 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Call for 7.5 feature completion

[Chris Browne]

[EMAIL PROTECTED] (Alvaro Herrera) writes:
> Or, slightly different, what are people's most wanted features?

- Vacuum Space Map - Maintain a map of recently-expired rows

This allows vacuum to target specific pages for possible free
space without requiring a sequential scan.

- Deferrable unique constraint

- Probably trivially easy would be to add an index to pg_listener

- Tougher but better would be to have pg_listener be an in-memory
  structure rather than being physically represented as a table

- MERGE / UPSERT

- Config file "#includes" for postgresql.conf, pg_hba.conf

- Some better ability to terminate backends 

- Automatically updatable views (per SQL 99)
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/sap.html
I am not a number!
I am a free man!

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [pgsql-advocacy] Spikewatch testing

[Chris Travers]

Josh Berkus wrote:




Is it worth trying to promote this as a way to promote PostgreSQL? Also,
is it worth trying to improve our test coverage?
   



Actually, they'll be running a contest (with prizes up to $2500) for 
improved test coverage for OSS applications.   I've been trying to get 
someone to commit to helping me on the contest, so that PostgreSQL can 
participate.


 

What is involved in this?  Maybe if you give specifics one of us can 
commit to helping :-)


Best Wishes,
Chris Travers
Metatron Technology Consulting

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

  http://www.postgresql.org/docs/faq