[HACKERS] width_bucket function for timestamps

[Jeremy Drake]

I just came across this code I wrote about a year ago which implements a
function equivilant to width_bucket for timestamps.

I wrote this when I was trying to plot some data over time, and I had more
points than I needed.  This function allowed me to create a pre-determined
number of "bins" to average the data inside of so that I could get a sane
number of points.  Part of the problem was that there were so many data
points, that a sql implementation of the function (or plpgsql, I forget,
it was a year ago) was painfully slow.  This C function provided much
better performance than any other means at my disposal.

I wanted to share this code since it may be useful for someone else, but I
don't know exactly what to do with it.  So I am putting it out there, and
asking what the proper home for such a function might be.  I believe it
would be generally useful for people, but it is so small that it hardly
seems like a reasonable pgFoundry project.  Maybe there is a home for such
a thing in the core distribution in a future release?

The code can be found at
http://www.jdrake.com/postgresql/bintimestamp.tar.gz for a buildable PGXS
module, or I attached just the C code.  There is no documentation, the
parameters work the same as the width_bucket function.  The code is not
necessarily the most readable in the world, I was trying to get as much
speed out of it as possible, since I was calling it over a million times
as a group by value.

Thanks for any pointers...

-- 
Fortune's Office Door Sign of the Week:

Incorrigible punster -- Do not incorrige./*
 * file:$RCSfile: bintimestamp.c,v $ $Revision: 1.1 $
 * module:  timestamp
 * authors: jeremyd
 * last mod:$Author: jeremyd $ at $Date: 2005/10/28 20:26:38 $
 * 
 * created: Fri Oct 28 13:26:38 PDT 2005
 * 
 */

#include 
#include 
#include "postgres.h"

#include "fmgr.h"
#include "libpq/pqformat.h"
#include "utils/builtins.h"
#include "funcapi.h"
#include "utils/timestamp.h"

#ifndef JROUND
#   define JROUND(x) (x)
#endif

Datum timestamp_get_bin_size(PG_FUNCTION_ARGS);
Datum timestamp_bin(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(timestamp_get_bin_size);
Datum
timestamp_get_bin_size(PG_FUNCTION_ARGS)
{
Timestamp start = PG_GETARG_TIMESTAMP(0);
Timestamp stop = PG_GETARG_TIMESTAMP(1);
int32 nbuckets = PG_GETARG_INT32(2);
Interval * retval = (Interval *)palloc (sizeof(Interval));

if (!retval)
{
ereport(ERROR, (errcode(ERRCODE_OUT_OF_MEMORY), 
errmsg("insufficient memory for Interval allocation")));
PG_RETURN_NULL();
}

memset (retval, 0, sizeof(Interval));

retval->time = JROUND ((stop - start) / nbuckets);

PG_RETURN_INTERVAL_P(retval);
}

PG_FUNCTION_INFO_V1(timestamp_bin);
Datum
timestamp_bin(PG_FUNCTION_ARGS)
{
/*Timestamp op = PG_GETARG_TIMESTAMP(0);*/
Timestamp start = PG_GETARG_TIMESTAMP(1);
/*Timestamp stop = PG_GETARG_TIMESTAMP(2);*/
Timestamp binsz;
/*int32 nbuckets = PG_GETARG_INT32(3)*/;

binsz = (PG_GETARG_TIMESTAMP(2) - start) / PG_GETARG_INT32(3);

PG_RETURN_TIMESTAMP(JROUND((int)((PG_GETARG_TIMESTAMP(0) - start) / 
binsz) * binsz + start));
}

---(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] Upgrading a database dump/restore

[Tom Lane]

"Mark Woodward" <[EMAIL PROTECTED]> writes:
>> Whenever someone actually writes a pg_upgrade, we'll institute a policy
>> to restrict changes it can't handle.

> IMHO, *before* any such tool *can* be written, a set of rules must be
> enacted regulating catalog changes.

That one is easy: there are no rules.  We already know how to deal with
catalog restructurings --- you do the equivalent of a pg_dump -s and
reload.  Any proposed pg_upgrade that can't cope with this will be
rejected out of hand, because that technology was already proven five
years ago.

The issues that are actually interesting have to do with the contents
of user tables and indexes, not catalogs.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Upgrading a database dump/restore

[Mark Woodward]

> "Mark Woodward" <[EMAIL PROTECTED]> writes:
>> Not to cause any arguments, but this is sort a standard discussion that
>> gets brought up periodically and I was wondering if there has been any
>> "softening" of the attitudes against an "in place" upgrade, or movement
>> to
>> not having to dump and restore for upgrades.
>
> Whenever someone actually writes a pg_upgrade, we'll institute a policy
> to restrict changes it can't handle.  But until we have a credible
> upgrade tool it's pointless to make any such restriction.  ("Credible"
> means "able to handle system catalog restructurings", IMHO --- without
> that, you'd not have any improvement over the current rules for minor
> releases.)

IMHO, *before* any such tool *can* be written, a set of rules must be
enacted regulating catalog changes. If there are no rules and no process
by which changes get approved, requiring a "was is" conversion strategy,
then the tools has to change with every major version, which will, of
course, put it at risk of losing support in the long term.

Like I said, I understand the reluctance to do these things, it isn't an
easy thing to do. Designing and planning for the future is, however, the
hallmark of a good engineer.

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

Re: [HACKERS] Casting to money

[Tom Lane]

"Dave Page"  writes:
> select '$123.45'::money
> ERROR: invalid input syntax for type money: "$123.45"
> select '£123.00'::money
> ERROR: invalid input syntax for type money: "£123.00"

So ... what locale are you trying this in?

regards, tom lane

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

[HACKERS] Casting to money

[Dave Page]

A pgAdmin user has just pointed out that the data editor doesn't work with 
money columns, apparently because it casts data when inserting/updating it. The 
docs for money say:

The money type stores a currency amount with a fixed fractional precision; see 
Table 8-3. Input is accepted in a variety of formats, including integer and 
floating-point literals, as well as "typical" currency formatting, such as 
'$1,000.00'. Output is generally in the latter form but depends on the locale. 

Yet:

-- Executing query:
select 123.45::money

ERROR: cannot cast type numeric to money
SQL state: 42846

-- Executing query:
select $123.45::money

ERROR: syntax error at or near ".45"
SQL state: 42601
Character: 12

-- Executing query:
select '$123.45'::money

ERROR: invalid input syntax for type money: "$123.45"
SQL state: 22P02

-- Executing query:
select '£123.00'::money

ERROR: invalid input syntax for type money: "£123.00"
SQL state: 22P02

-- Executing query:
select '123.45'::money

Total query runtime: 0 ms.
[this one returns an empty value]


So my question is, how do we cast input to the money type? Or am I working too 
late again and being a plank?

Regards, Dave.

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

   http://archives.postgresql.org

Re: [HACKERS] [PATCHES] Use non-deprecated APIs for dynloader/darwin.c

[Tom Lane]

Chris Campbell <[EMAIL PROTECTED]> writes:
> On Oct 8, 2006, at 14:29, Tom Lane wrote:
>> Looks good, but I don't think we want to abandon OSX 10.2 support
>> just yet.  I'll revise this to use a configure probe for dlopen.

> Maybe we can abandon Mac OS X 10.2 in 8.3 and later? And not back- 
> port these patches to the 7.x, 8.0, and 8.1 branches?

I don't see any particular reason to abandon 10.2 at all, as long as
all it's costing us is a couple dozen lines in dynloader/darwin.c.
As for back-porting, I don't see a strong reason to do that either.
Apple will presumably maintain the compatibility APIs for a long
while yet.

> BTW, I think the configure probe (only on Darwin, correct?) should  
> test for the existence of .

I realized that configure is already probing for dlopen(), so I just
used HAVE_DLOPEN.  We can add a separate test for the header file if
it proves really necessary, but I bet it's not.

regards, tom lane

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

Re: [HACKERS] [COMMITTERS] pgsql: Add include needed for new getrusage() call.

[Peter Eisentraut]

Tom Lane wrote:
> There's still something pretty strange here, though, because AFAICS
> configure should have rejected sys/resource.h if it needs sys/time.h.

I think it only gives you a warning.

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

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

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

Re: [HACKERS] continuing daily testing of dbt2 against postgresql

[Mark Wong]

Michael Paesold wrote:
> [EMAIL PROTECTED] wrote:
>>> Mark Wong <[EMAIL PROTECTED]> writes:
 After over a year of problems (old site
 http://developer.osdl.org/markw/postgrescvs/)  I have resumed producing
 daily results of dbt-2 against PostgreSQL CVS code with results here:
 http://dbt.osdl.org/dbt2.html
>>> This is good to hear!  I am curious where we are now compared to where
>>> we were a year ago ... do you still have the old data, and is the test
>>> setup still comparable?
>>
>> The test setup is on completely different hardware.  I still have the old
>> data and it's accessible, but it'll take a little bit of work to
>> regenerate the links.  I'll try to work on that.
> 
> I think it would also help if you would create reference runs for the
> latest 8.0 and 8.1 releases on the new hardware.

Ok, I'll try to work those in within the next couple days.

Regards,
Mark

---(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] [COMMITTERS] pgsql: Add include needed for new

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > For me, 'struct timeval' is coming in via #include "libpq/libpq.h", but
> > of course that is _after_ the inclusion of resource.h.
> 
> Ah, that explains it.
> 
> > Not sure where
> > you see that sys/resource.h was always there.  Looking at the CVS diffs
> > I see it added only in the past day.
> 
> It was further down before.
> 
> There's still something pretty strange here, though, because AFAICS
> configure should have rejected sys/resource.h if it needs sys/time.h.

I did some research and it turns out the configure test for includes
uses the following source file, which includes , which
itself includes  on my BSD/OS machine, so that is why
configure has no problem finding resource.h usable.

---

/* confdefs.h.  */

#define PACKAGE_NAME "PostgreSQL"
#define PACKAGE_TARNAME "postgresql"
#define PACKAGE_VERSION "8.2beta1"
#define PACKAGE_STRING "PostgreSQL 8.2beta1"
#define PACKAGE_BUGREPORT "pgsql-bugs@postgresql.org"
#define PG_VERSION "8.2beta1"
#define ENABLE_NLS 1
#define DEF_PGPORT 5432
#define DEF_PGPORT_STR "5432"
#define PG_VERSION_STR "PostgreSQL 8.2beta1 on i386-pc-bsdi4.3.1, compiled by 
GCC 2.95.3"
#define ENABLE_THREAD_SAFETY 1
#define PG_KRB_SRVNAM "postgres"
#define USE_SSL 1
#define PG_VERSION_NUM 80200
#define HAVE_LIBREADLINE 1
#define HAVE_LIBZ 1
#define HAVE_SPINLOCKS 1
#define HAVE_LIBCRYPTO 1
#define HAVE_LIBSSL 1
#define STDC_HEADERS 1
#define HAVE_SYS_TYPES_H 1
#define HAVE_SYS_STAT_H 1
#define HAVE_STDLIB_H 1
#define HAVE_STRING_H 1
#define HAVE_MEMORY_H 1
#define HAVE_STRINGS_H 1
#define HAVE_UNISTD_H 1
#define HAVE_GETOPT_H 1
#define HAVE_IEEEFP_H 1
#define HAVE_PWD_H 1
#define HAVE_SYS_IPC_H 1
/* end confdefs.h.  */
#include 
#if HAVE_SYS_TYPES_H
# include 
#endif
#if HAVE_SYS_STAT_H
# include 
#endif
#if STDC_HEADERS
# include 
# include 
#else
# if HAVE_STDLIB_H
#  include 
# endif
#endif
#if HAVE_STRING_H
# if !STDC_HEADERS && HAVE_MEMORY_H
#  include 
# endif
# include 
#endif
#if HAVE_STRINGS_H
# include 
#endif
#if HAVE_INTTYPES_H
# include 
#else
# if HAVE_STDINT_H
#  include 
# endif
#endif
#if HAVE_UNISTD_H
# include 
#endif
#include 


-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

   http://archives.postgresql.org

Re: [HACKERS] continuing daily testing of dbt2 against postgresql

[Mark Wong]

Tom Lane wrote:
> Mark Wong <[EMAIL PROTECTED]> writes:
>> After over a year of problems (old site 
>> http://developer.osdl.org/markw/postgrescvs/)  I have resumed producing 
>> daily results of dbt-2 against PostgreSQL CVS code with results here:
>>  http://dbt.osdl.org/dbt2.html
> 
> This is good to hear!  I am curious where we are now compared to where
> we were a year ago ... do you still have the old data, and is the test
> setup still comparable?

I made another couple of gross mistakes of forgetting to compile
PostgreSQL with --enable-thread-safe and enabling the user space irq
balancing program in Linux.  I've restarted the histories with 600 and
800 warehouse runs where 600 should be below peak system performance and
800 pushing the system beyond it's peak performance.

Still working on getting pointers to the old data...

Regards,
Mark

---(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] The improvement for psql of 8.2beta1 not

[Magnus Hagander]

> >> The test shows that it's OK under Linux (Slackware),  but 
> >> malfunctioned on Windows XP.
> > 
> > Good point.  We don't use readline on Win32, but rather the native 
> > command-line control, over which we have little control.
> 
> Does libedit compile under mingw?

No. At least, it didn't the last couple of times I've tried it.

//Magnus

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

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

Re: [HACKERS] The improvement for psql of 8.2beta1 not

[Joshua D. Drake]

Bruce Momjian wrote:
> Yourfriend wrote:
>>  According to the release notes of 8.2, the following item should have been
>> implemented,
>>
>> E.1.3.11. 
>> psqlChanges
>>
>>-
>>
>>Save multi-line statements as a single entry, rather than one line at
>>a time (Sergey E. Koposov)
>>
>>This makes up-arrow recall of queries easier.
>>
>>
>> The test shows that it's OK under Linux (Slackware),  but malfunctioned on
>> Windows XP.
> 
> Good point.  We don't use readline on Win32, but rather the native
> command-line control, over which we have little control. 

Does libedit compile under mingw?


Joshua D. Drake

-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

   http://archives.postgresql.org

Re: [HACKERS] [PATCHES] Use non-deprecated APIs for dynloader/darwin.c

[Chris Campbell]

On Oct 8, 2006, at 14:29, Tom Lane wrote:


Looks good, but I don't think we want to abandon OSX 10.2 support
just yet.  I'll revise this to use a configure probe for dlopen.


Maybe we can abandon Mac OS X 10.2 in 8.3 and later? And not back- 
port these patches to the 7.x, 8.0, and 8.1 branches?


BTW, I think the configure probe (only on Darwin, correct?) should  
test for the existence of .



My inclination is to apply this one now, since it only affects OSX
and should be easily testable, but to hold off on your other patch
for portable Bonjour support until 8.3 devel starts.  The portability
implications of that one are unclear, and I don't know how to test it
either, so I think putting it in now is too much risk.


The Bonjour patch wasn't intended to be portable to other platforms  
just yet. As submitted, it has the same risks/advantages as this  
dlopen() patch -- it only works on 10.3 and later, but isn't  
deprecated in 10.4.


If we want to keep 10.2 support for Bonjour, we can test for both  
DNSServiceDiscovery.h and dns_sd.h in ./configure, and prefer  
dns_sd.h if it's found (which will be the case for 10.3 and 10.4) but  
use DNSServiceDiscovery.h if not (which will be the case for 10.2).


Thanks!

- Chris


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

Re: [HACKERS] [COMMITTERS] pgsql: Add include needed for new getrusage() call.

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> For me, 'struct timeval' is coming in via #include "libpq/libpq.h", but
> of course that is _after_ the inclusion of resource.h.

Ah, that explains it.

> Not sure where
> you see that sys/resource.h was always there.  Looking at the CVS diffs
> I see it added only in the past day.

It was further down before.

There's still something pretty strange here, though, because AFAICS
configure should have rejected sys/resource.h if it needs sys/time.h.

regards, tom lane

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

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

Re: [HACKERS] [PATCHES] Use non-deprecated APIs for dynloader/darwin.c

[Tom Lane]

Chris Campbell <[EMAIL PROTECTED]> writes:
> Mac OS X has included dlopen() and friends etc since Mac OS X 10.3.  
> The attached patch switches to using those APIs in src/backend/port/ 
> dynloader/darwin.c (and passes "make check").

Looks good, but I don't think we want to abandon OSX 10.2 support
just yet.  I'll revise this to use a configure probe for dlopen.

My inclination is to apply this one now, since it only affects OSX
and should be easily testable, but to hold off on your other patch
for portable Bonjour support until 8.3 devel starts.  The portability
implications of that one are unclear, and I don't know how to test it
either, so I think putting it in now is too much risk.

regards, tom lane

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

Re: [HACKERS] [COMMITTERS] pgsql: Add include needed for new

[Bruce Momjian]

Tom Lane wrote:
> [EMAIL PROTECTED] (Bruce Momjian) writes:
> > Add include needed for new getrusage() call.
> 
> If that's actually needed, how did the code build before?  It's always
> included sys/resource.h, except possibly on machines without getrusage
> ... are there any?  I was thinking rusagestub was dead code, myself.

Uh, all I know is that it started failing yesterday.  The failure I got
was:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-fno-strict-aliasing -O1 -Wall -Wmissing-prototypes
-Wmissing-declarations -Wpointer-arith -Wcast-align
-I../../../src/include -I/usr/local/include/readline
-I/usr/contrib/include -DWIN32_STACK_RLIMIT=4194304  -c -o postgres.o
postgres.c
In file included from postgres.c:31:
/usr/include/sys/resource.h:63: field `ru_utime' has incomplete type
/usr/include/sys/resource.h:64: field `ru_stime' has incomplete type
gmake: *** [postgres.o] Error 1

and resource.h has:

struct  rusage {
struct timeval ru_utime;/* user time used */
struct timeval ru_stime;/* system time used */

so it is timeval it wanted. But I have:

#define HAVE_GETRUSAGE 1

For me, 'struct timeval' is coming in via #include "libpq/libpq.h", but
of course that is _after_ the inclusion of resource.h.  Not sure where
you see that sys/resource.h was always there.  Looking at the CVS diffs
I see it added only in the past day.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

   http://archives.postgresql.org

Re: [HACKERS] The improvement for psql of 8.2beta1 not

[Bruce Momjian]

Yourfriend wrote:
>  According to the release notes of 8.2, the following item should have been
> implemented,
> 
> E.1.3.11. 
> psqlChanges
> 
>-
> 
>Save multi-line statements as a single entry, rather than one line at
>a time (Sergey E. Koposov)
> 
>This makes up-arrow recall of queries easier.
> 
> 
> The test shows that it's OK under Linux (Slackware),  but malfunctioned on
> Windows XP.

Good point.  We don't use readline on Win32, but rather the native
command-line control, over which we have little control.  Update release
item text is:

  
   
Save multi-line statements as a single entry, rather than
one line at a time (Sergey E. Koposov)
   

   
This makes up-arrow recall of queries easier.  (This is
not availble on Win32 because it typically uses the native
command-line editing present in the operating system.)
   
  

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] [COMMITTERS] pgsql: Add include needed for new getrusage() call.

[Tom Lane]

[EMAIL PROTECTED] (Bruce Momjian) writes:
> Add include needed for new getrusage() call.

If that's actually needed, how did the code build before?  It's always
included sys/resource.h, except possibly on machines without getrusage
... are there any?  I was thinking rusagestub was dead code, myself.

regards, tom lane

---(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] Checking max_stack_depth automatically

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
>>> For win32, we set the stacksize in src/backend/Makefile with 
>>> "-Wl,--stack=4194304". So we know at build time what it is, if that 
>>> helps you...
>> 
>> Well, I can just wire that value into get_stack_depth_rlimit, 
>> I suppose, but it sounds crocky. 

> If we do, we probably move it to a define (which could just be in the
> Makefile) so we don't accidenally change one without the other, no?

OK, I've committed changes along these lines, but I can't really test
it.  Next time you make a Windows build, would you please verify that
it still defaults to max_stack_depth = 2MB, and that it will let you set
max_stack_depth no higher than 3.5MB ?

regards, tom lane

---(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] Should there be a charcat?

[Jim C. Nasby]

On Fri, Oct 06, 2006 at 04:10:42PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > I would think that c || 'x' would result in 'xx',
> > but it doesn't
> 
> We did it that way up through 7.3, but changed because we concluded the
> behavior was inconsistent.  The behavior of char(N) these days is that
> the padding spaces are not semantically significant, which means that
> they effectively are not there for anything except display purposes.
 
Nah, I've just always thought of the spaces as being significant. Then
again, I tend to avoid char, so...

> If we didn't do this, we would have situations where, for example,
> A = B but (A || 'x') != (B || 'x').  Does that strike you as a good idea?
> 
> If you're interested you can probably find more discussion in the
> archives from mid-2003 or so.
> 
>   regards, tom lane
> 

-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(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] timestamptz alias

[Jim C. Nasby]

On Fri, Oct 06, 2006 at 02:35:55AM -0500, Andrew Dunstan wrote:
> Jim Nasby wrote:
> >
> > It would be nice to denote types/aliases that are and aren't ANSI. A
> > number are marked  in the docs, but it would be good to add the info
> > to that summary table.
> 
> Right under the table this sentence appears:
> 
> Compatibility:  The following types (or spellings thereof) are specified
> by SQL: bit, bit varying, boolean, char, character varying, character,
> varchar, date, double precision, integer, interval, numeric, decimal,
> real, smallint, time (with or without time zone), timestamp (with or
> without time zone).
> 
> What's unclear about that?

Oops. This is what happens when I answer emails on a plane. :(
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

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

Re: [HACKERS] Checking max_stack_depth automatically

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
>> Well, I can just wire that value into get_stack_depth_rlimit, 
>> I suppose, but it sounds crocky. 

> If we do, we probably move it to a define (which could just be in the
> Makefile) so we don't accidenally change one without the other, no?

I think we'd have to put the define in Makefile.global, which seems like
overkill ... but OTOH, seeing that it's textually in two places in
backend/Makefile already, maybe that's the best way.  The alternative is
to stick a comment in each place referencing the other(s) ...

>> There's no way to tell at runtime what value was set?

> None that I know of, and none that I can find in a quick search of the
> API docs. A google shows up a couple of references to other people
> saying it can't be done. (Other than opening your own EXE file and
> manually parsing the PE header, but that seems at least as bad..)

I agree, that's right out.  Hardwired it shall be.

regards, tom lane

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

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

Re: [HACKERS] Checking max_stack_depth automatically

[Magnus Hagander]

> > For win32, we set the stacksize in src/backend/Makefile with 
> > "-Wl,--stack=4194304". So we know at build time what it is, if that 
> > helps you...
> 
> Well, I can just wire that value into get_stack_depth_rlimit, 
> I suppose, but it sounds crocky. 

If we do, we probably move it to a define (which could just be in the
Makefile) so we don't accidenally change one without the other, no?


> There's no way to tell at runtime what value was set?

None that I know of, and none that I can find in a quick search of the
API docs. A google shows up a couple of references to other people
saying it can't be done. (Other than opening your own EXE file and
manually parsing the PE header, but that seems at least as bad..) For
example, see the discussion aobut stack attributes at
http://www.roguewave.com/support/docs/hppdocs///thr200pl/8-2.html.

//Magnus

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

Re: [HACKERS] Checking max_stack_depth automatically

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> For win32, we set the stacksize in src/backend/Makefile with
> "-Wl,--stack=4194304". So we know at build time what it is, if that
> helps you...

Well, I can just wire that value into get_stack_depth_rlimit, I suppose,
but it sounds crocky.  There's no way to tell at runtime what value was
set?

regards, tom lane

---(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] PL/pgSQL Todo, better information in errcontext from plpgsql

[Pavel Stehule]

Why do you need the OID to know exactly what function something  is?  
What's wrong with schema.function(args)?

--


oid is simply unique. I can take source code, args and all without  
parsing. It's only one difference. I unlike parsing.


decibel=# select 'pg_catalog.abstimelt 
(abstime,abstime)'::regprocedure::oid;

oid
-
253



hmm, it changes situation. With it OID isn't really necessery. i didn't know 
it before.


Regards
Pavel Stehule

_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/


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

Re: [HACKERS] Checking max_stack_depth automatically

[Magnus Hagander]

> > I have just realized that getrlimit(RLIMIT_STACK) is a 
> pretty widely 
> > available syscall --- it's specified by the Single Unix 
> Spec and the 
> > man pages claim it works on all the platforms I have handy to check.
> > I propose that we make use of this call where available to prevent 
> > people from setting max_stack_depth larger than, say, the current 
> > stack rlimit less half a megabyte.
> 
> I've committed changes along this line, and am now wondering 
> whether there isn't some equivalent to 
> getrlimit(RLIMIT_STACK) on Windows (I somehow doubt that the 
> syscall exists as such ;-)).  If someone can provide a patch 
> to postgres.c's new get_stack_depth_rlimit() function, please do.

It doesn't. It doesn't have the concept of RLIMIT, really. 
In "the old days", there was on way to limit how much memory a process
uses. In Windows 2000, "Job Objects" were added, which can limit them.
But they make no difference between stack and non-stack memory.

For win32, we set the stacksize in src/backend/Makefile with
"-Wl,--stack=4194304". So we know at build time what it is, if that
helps you...

//Magnus

---(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] 8.2beta1 crash possibly in libpq

[Magnus Hagander]

> AFAICT the backtrace and server log is indicating that the 
> crash is happening somewhere in libpq. If someone can help me 
> figure out how to load the libpq symbols into MingW's gdb 
> then I can get a better backtrace if required as I can 
> reproduce this 100% of the time. For reference, the source 
> for the application in question can be found at 
> http://svn.refractions.net/postgis/tags/1.1.4/loader/pgsql2shp.c.

If you figure out how to make gdb actually work on mingw, let us know -
not many has ever managed to get it wokring, and I don't know of anybody
who can make it work repeatedly.

That said, libpq builds with Visual C++. Could you try building your
pgsql2shp with Visual C++ as well, and then use the Visual C++ debugger
(or windbg, really). They should give working backtraces.

//Magnus

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

   http://archives.postgresql.org

[HACKERS] 8.2beta1 crash possibly in libpq

[Mark Cave-Ayland]

Hi everyone,

I'm in the process of generating the Windows installer for the latest
PostGIS 1.1.4 release and I'm getting a regression failure in one of
libpq applications - the application in question is generating a
segfault.

Testing so far shows that the regression tests pass without segfaulting
in the following scenarios:

PostgreSQL 8.2beta1 / PostGIS 1.1.4 / Linux
PostgreSQL 8.1 / PostGIS 1.1.4 / Win32

So it appears it is something to do with 8.2beta1 and Win32. I've
compiled the application with debugging symbols enabled and get the
following backtrace from gdb in MingW:


(gdb) set args -f /tmp/pgis_reg_4060/dumper postgis_reg loadedshp
(gdb) run
Starting program: C:\msys\1.0\home\mca\postgis\pg82\postgis-1.1.4
\regress/../loader/pgsql2shp.exe -f /tmp/pgis_reg_4060/dumper
postgis_reg loadedshp
Initializing... 
Program received signal SIGSEGV, Segmentation fault.
0x63512c1c in ?? ()
(gdb) bt
#0  0x63512c1c in ?? ()
#1  0x0040c69c in _fu8__PQntuples () at pgsql2shp.c:2502
#2  0x00408481 in main (ARGC=5, ARGV=0x3d2750) at pgsql2shp.c:243
(gdb) 


I also turned on the logging in the server and get the following in the
server log:


2006-10-08 12:01:15 LOG:  statement: BEGIN;
2006-10-08 12:01:15 LOG:  statement: CREATE TABLE "loadedshp" (gid
serial PRIMARY KEY);
2006-10-08 12:01:15 NOTICE:  CREATE TABLE will create implicit sequence
"loadedshp_gid_seq" for serial column "loadedshp.gid"
2006-10-08 12:01:15 NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "loadedshp_pkey" for table "loadedshp"
2006-10-08 12:01:15 LOG:  statement: SELECT
AddGeometryColumn('','loadedshp','the_geom','-1','POINT',2);
2006-10-08 12:01:17 LOG:  statement: INSERT INTO "loadedshp" (the_geom)
VALUES ('010100F03F');
2006-10-08 12:01:18 LOG:  statement: INSERT INTO "loadedshp" (the_geom)
VALUES ('0101002240F0BF');
2006-10-08 12:01:18 LOG:  statement: INSERT INTO "loadedshp" (the_geom)
VALUES ('0101002240F0BF');
2006-10-08 12:01:18 LOG:  statement: END;
2006-10-08 12:01:21 LOG:  statement: select asewkt(the_geom) from
loadedshp;
2006-10-08 12:01:36 LOG:  statement: DROP table loadedshp
2006-10-08 12:01:39 LOG:  statement: BEGIN;
2006-10-08 12:01:39 LOG:  statement: CREATE TABLE "loadedshp" (gid
serial PRIMARY KEY);
2006-10-08 12:01:39 NOTICE:  CREATE TABLE will create implicit sequence
"loadedshp_gid_seq" for serial column "loadedshp.gid"
2006-10-08 12:01:39 NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "loadedshp_pkey" for table "loadedshp"
2006-10-08 12:01:39 LOG:  statement: SELECT
AddGeometryColumn('','loadedshp','the_geom','-1','POINT',2);
2006-10-08 12:01:41 LOG:  statement: COPY "loadedshp" (the_geom) FROM
stdin;
2006-10-08 12:01:41 LOG:  statement: END;
2006-10-08 12:01:43 LOG:  statement: select asewkt(the_geom) from
loadedshp;
2006-10-08 12:02:34 LOG:  statement: SELECT postgis_version()
2006-10-08 12:02:34 LOG:  statement: SELECT a.attname, a.atttypid,
a.attlen, a.atttypmod FROM pg_attribute a, pg_class c WHERE a.attrelid =
c.oid and a.attnum > 0 AND a.atttypid != 0 AND c.relname = 'loadedshp'
2006-10-08 12:02:48 LOG:  could not receive data from client: No
connection could be made because the target machine actively refused
it.

2006-10-08 12:02:48 LOG:  unexpected EOF on client connection


AFAICT the backtrace and server log is indicating that the crash is
happening somewhere in libpq. If someone can help me figure out how to
load the libpq symbols into MingW's gdb then I can get a better
backtrace if required as I can reproduce this 100% of the time. For
reference, the source for the application in question can be found at
http://svn.refractions.net/postgis/tags/1.1.4/loader/pgsql2shp.c.


Many thanks,

Mark.



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