Re: [HACKERS] Retiring from the Core Team

[Steve Crawford]

Thanks, Josh, for everything. I especially enjoyed your monthly updates at
SFPUG.

Cheers,
Steve

On Thu, Jan 12, 2017 at 1:59 PM, Merlin Moncure  wrote:

> On Wed, Jan 11, 2017 at 6:29 PM, Josh Berkus  wrote:
> > Hackers:
> >
> > You will have noticed that I haven't been very active for the past year.
> >  My new work on Linux containers and Kubernetes has been even more
> > absorbing than I anticipated, and I just haven't had a lot of time for
> > PostgreSQL work.
> >
> > For that reason, as of today, I am stepping down from the PostgreSQL
> > Core Team.
>
> Thanks for all your hard work.  FWIW, your blog posts, 'Primary
> Keyvil' are some of my favorite of all time!
>
> merlin
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Is it time to kill support for very old servers?

[Steve Crawford]

This thread gets me thinking about the definition of "support." While
support in practice seems to primarily relate to fixes/updates to the
supported version itself it could just as well apply to interoperability
support by newer versions.

Given that the standard PostgreSQL upgrade process involves upgrading
clients first and using pg_dump from the newer version, it is reasonable to
assume that the clients/utilities for a given version would support
interacting with any prior version that was not EOL at the time the new
major version is released.

In other words, 9.6 was released last month, the same month that 9.1 was
EOL, so 9.6 clients should work with 9.1 through 9.6 servers but from my
perspective there is no need to *guarantee* that 10 would do so. The
standard caveats apply. A new version *might* work for an unsupported older
version but no assurance is offered.

This is effectively a 5-year upgrade "grace period" *after* the EOL date of
a given version which seems plenty generous.

Defining the term of backward compatibility support might be useful in the
future when these types of questions arise.

Cheers,
Steve






On Fri, Oct 7, 2016 at 9:06 AM, Tom Lane  wrote:

> Robert Haas  writes:
> > On Fri, Oct 7, 2016 at 11:34 AM, Tom Lane  wrote:
> >> Greg Stark  writes:
> >>> For another there may be binary-only applications or drivers out there
> >>> that are using V2 for whatever reason.
>
> >> The problem with letting it just sit there is that we're not, in fact,
> >> testing it.  If we take the above argument seriously then we should
> >> provide some way to configure libpq to prefer V2 and run regression
> >> tests in that mode.  Otherwise, if/when we break it, we'll never know it
> >> till we get field reports.
>
> > I agree with that.  I think it would be fine to keep V2 support if
> > somebody wants to do the work to let us have adequate test coverage,
> > but if nobody volunteers I think we might as well rip it out.  I don't
> > particularly enjoy committing things only to be told that they've
> > broken something I can't test without unreasonable effort.
>
> When I wrote the above I was thinking of an essentially user-facing
> libpq feature, similar to the one JDBC has, to force use of V2 protocol.
> But actually, for testing purposes, I don't think that's what we want.
> Any such feature would fail to exercise libpq's logic for falling back
> from V3 to V2 when it connects to an old server, which is surely something
> we'd like to test without actually having a pre-7.4 server at hand.
>
> So what I'm thinking is it'd be sufficient to do something like
> this in pqcomm.h:
>
> +#ifndef FORCE_OLD_PROTOCOL
>  #define PG_PROTOCOL_LATEST PG_PROTOCOL(3,0)
> +#else /* make like a pre-7.4 server for testing purposes */
> +#define PG_PROTOCOL_LATEST PG_PROTOCOL(2,0)
> +#endif
>
> which would cause the server to reject 3.0 requests just as if it were
> ancient.  Then we could test with that #define, maybe have a buildfarm
> critter doing it.  (This might break pqmq.c though, so we might need
> to work slightly harder than this.)
>
> Also, I realized while perusing this that the server still has support
> for protocol 1.0 (!).  That's *definitely* dead code.  There's not much
> of it, but still, I'd rather rip it out than continue to pretend it's
> supported.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Bug in to_timestamp().

[Steve Crawford]

On Fri, Jun 24, 2016 at 3:43 PM, Joshua D. Drake <j...@commandprompt.com>
wrote:

> On 06/24/2016 02:16 PM, Tom Lane wrote:
>
>> Robert Haas <robertmh...@gmail.com> writes:
>>
>>> On Fri, Jun 24, 2016 at 12:26 PM, Steve Crawford
>>> <scrawf...@pinpointresearch.com> wrote:
>>>
>>>> To me, 2016-02-30 is an invalid date that should generate an error.
>>>>
>>>
>> I don't particularly disagree with that, but on the other hand, as
>>> mentioned earlier, to_timestamp() is here for Oracle compatibility,
>>> and if it doesn't do what Oracle's function does, then (1) it's not
>>> useful for people migrating from Oracle and (2) we're making up the
>>> behavior out of whole cloth.  I think things that we invent ourselves
>>> should reject stuff like this, but in a compatibility function we
>>> might want to, say, have compatibility.
>>>
>>
>> Agreed, mostly, but ... how far are we prepared to go on that?
>>
>
> We don't at all. Our goal has never been Oracle compatibility. Yes, we
> have "made allowances" but we aren't in a position that requires that
> anymore.
>
> Let's just do it right.
>
> Sincerely,
>
> JD
>
> /me speaking as someone who handles many, many migrations, none of which
> have ever said, "do we have Oracle compatibility available".
>
>
Tongue (partlyish) in cheek:

Developer: I need a database to support my project. Based on my research
this PostgreSQL thing is awesome so we will use it.

PostgreSQL: Welcome to our community!

Developer: I need to convert a string to a timestamp. This to_timestamp()
function I tried does not operate as I expect based on the documentation.

PostgreSQL: Ah, yes, grasshopper. You are young and do not understand the
Things That Must Not Be Documented . In time you will grow a gray ponytail
and/or white beard and learn the history and ways of every database that
came before. Only then will you come to understand how The Functions
*truly* behave.

Developer: Are you #@%!$ kidding me?

I will allow that there may be selected cases where a good argument could
be made for intentionally overly permissive behavior in the pursuit of
compatibility. But in those cases the documentation should specify clearly
and in detail the deviant behavior and reason for its existence.

As one who selected PostgreSQL from the start, I am more interested in the
functions working correctly.

Cheers,
Steve

Re: [HACKERS] Bug in to_timestamp().

[Steve Crawford]

My observation has been that the PostgreSQL development group aims for
correctness and the elimination of surprising results. This was part of the
reason to eliminate a number of automatic casts to dates in earlier
versions.

To me, 2016-02-30 is an invalid date that should generate an error.
Automatically and silently changing it to be 2016-03-01 strikes me as a
behavior I'd expect from a certain other open-source database, not
PostgreSQL.

Cheers,
Steve

On Fri, Jun 24, 2016 at 8:52 AM, Alex Ignatov 
wrote:

>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
> On 20.06.2016 17:09, Albe Laurenz wrote:
>
>> Tom Lane wrote:
>>
>>> I don't necessarily have an opinion yet.  I would like to see more than
>>> just an unsupported assertion about what Oracle's behavior is.  Also,
>>> how should FM mode affect this?
>>>
>> I can supply what Oracle 12.1 does:
>>
>> SQL> SELECT to_timestamp('2016-06-13 15:43:36', ' /MM/DD HH24:MI:SS')
>> AS ts FROM dual;
>>
>> TS
>> 
>> 2016-06-13 15:43:36.0 AD
>>
>> SQL> SELECT to_timestamp('2016-06-13 15:43:36', '/MM/DD  HH24:MI:SS')
>> AS ts FROM dual;
>>
>> TS
>> 
>> 2016-06-13 15:43:36.0 AD
>>
>> SQL> SELECT to_timestamp('2016-06-1315:43:36', '/MM/DD
>> HH24:MI:SS') AS ts FROM dual;
>>
>> TS
>> 
>> 2016-06-13 15:43:36.0 AD
>>
>> (to_timestamp_tz behaves the same way.)
>>
>> So Oracle seems to make no difference between one or more spaces.
>>
>> Yours,
>> Laurenz Albe
>>
>> Guys, do we need to change this behavior or may be you can tell me that
> is normal because this and this:
>
> postgres=# SELECT TO_TIMESTAMP('2016-02-30 15:43:36', '-MM-DD
> HH24:MI:SS');
>   to_timestamp
> 
>  2016-03-01 15:43:36+03
> (1 row)
>
> but on the other side we have :
>
> postgres=# select '2016-02-30 15:43:36'::timestamp;
> ERROR:  date/time field value out of range: "2016-02-30 15:43:36"
> LINE 1: select '2016-02-30 15:43:36'::timestamp;
>
> Another bug in to_timestamp/date()?
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Feature request: make cluster_name GUC useful for psql prompts

[Steve Crawford]

Although this is getting slightly off the original topic, rereading .psqlrc
is a potential can of worms. What triggers a reread? What portions of
.psqlrc are re-read?

For example, say I have just set tuples-only, extended-display, or output
file. Would they all get reset just because I changed connections?

You can use variables to approximate the behavior of aliases so you can
hack an alias that includes the reconnect and re-read. Or just \i ~/.psqlrc
as you deem necessary.

Cheers,
Steve




On Fri, May 6, 2016 at 12:50 PM, Jerry Sievers <gsiever...@comcast.net>
wrote:

> Steve Crawford <scrawf...@pinpointresearch.com> writes:
>
> > That is almost identical to the solution I suggested a week or two ago
> to someone tackling the issue and the hack works on initial connection.
> >
> > Connect to a different cluster with "\c", however, and it will leave the
> prompt showing you connected to the original database which is not good.
>
> True and I've always thought of it as a possible misfeature of psql that
> it scans .psqlrc only once.
>
> > Cheers,
> > Steve
> >
> > On Fri, May 6, 2016 at 11:42 AM, Jerry Sievers <gsiever...@comcast.net>
> wrote:
> >
> >     Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:
> >
> > > On 5/5/16 9:21 PM, Steve Crawford wrote:
> > >
> > >> Adding an escape sequence that references cluster_name would
> enable
> > >> prompts to identify the cluster in a manner that is both
> consistent and
> > >> distinct regardless of access path.
> > >
> > > I think that would be a good idea.  You could probably design it so
> > > that any server parameter reported to the client can be put in a
> psql
> > > prompt.
> >
> > The OP can easily work around that lack of support with something
> such as follow...
> >
> > Add this to ~/.psqlrc[-optional version stuff]
> >
> > select setting as cluster_name from pg_settings where name =
> 'cluster_name'  -- do not simicolon terminate this line
> > \gset
> >
> > \set PROMPT1 :cluster_name ': how cool is this:'
> >
> > >
> > >> Potential issues/improvements:
> > >>
> > >> What should the escape-sequence display if cluster_name is not
> set or
> > >> the cluster is a pre-9.5 version. %M? %m?
> > >>
> > >> In future server versions should there be a default for
> cluster_name if
> > >> it is not set? If so, what should it be? Would the server
> canonical
> > >> hostname + listen-port be reasonable?
> > >
> > > Those are good questions.  I don't really like the proposed
> answers,
> > > because that could cause confusion in practical use.
> > >
> > > --
> > > Peter Eisentraut  http://www.2ndQuadrant.com/
> > > PostgreSQL Development, 24x7 Support, Remote DBA, Training &
> Services
> >
> > --
> > Jerry Sievers
> > Postgres DBA/Development Consulting
> > e: postgres.consult...@comcast.net
> > p: 312.241.7800
> >
>
> --
> Jerry Sievers
> e: jerry.siev...@comcast.net
> p: 312.241.7800
>

Re: [HACKERS] Feature request: make cluster_name GUC useful for psql prompts

[Steve Crawford]

That is almost identical to the solution I suggested a week or two ago to
someone tackling the issue and the hack works on initial connection.

Connect to a different cluster with "\c", however, and it will leave the
prompt showing you connected to the original database which is not good.

Cheers,
Steve

On Fri, May 6, 2016 at 11:42 AM, Jerry Sievers <gsiever...@comcast.net>
wrote:

> Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:
>
> > On 5/5/16 9:21 PM, Steve Crawford wrote:
> >
> >> Adding an escape sequence that references cluster_name would enable
> >> prompts to identify the cluster in a manner that is both consistent and
> >> distinct regardless of access path.
> >
> > I think that would be a good idea.  You could probably design it so
> > that any server parameter reported to the client can be put in a psql
> > prompt.
>
> The OP can easily work around that lack of support with something such as
> follow...
>
> Add this to ~/.psqlrc[-optional version stuff]
>
> select setting as cluster_name from pg_settings where name =
> 'cluster_name'  -- do not simicolon terminate this line
> \gset
>
> \set PROMPT1 :cluster_name ': how cool is this:'
>
> >
> >> Potential issues/improvements:
> >>
> >> What should the escape-sequence display if cluster_name is not set or
> >> the cluster is a pre-9.5 version. %M? %m?
> >>
> >> In future server versions should there be a default for cluster_name if
> >> it is not set? If so, what should it be? Would the server canonical
> >> hostname + listen-port be reasonable?
> >
> > Those are good questions.  I don't really like the proposed answers,
> > because that could cause confusion in practical use.
> >
> > --
> > Peter Eisentraut  http://www.2ndQuadrant.com/
> > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>

[HACKERS] Feature request: make cluster_name GUC useful for psql prompts

[Steve Crawford]

It's great that 9.5 has the new cluster_name variable as an available GUC.

It would be even better to make that GUC available for use in psql
prompting escape sequences.

Prompting via sequences utilizing %M, %m and %> means the same cluster
could be identified numerous ways (local, 127.0.0.1, 10.1.2.3, localhost,
myserver.example.com, myserver, etc.) which is further exacerbated when
pooling or port-forwarding is in play.

In the inverse case, when logging into a multiple servers and running psql,
all the prompts might just say "local" despite all being different clusters.

Adding an escape sequence that references cluster_name would enable prompts
to identify the cluster in a manner that is both consistent and distinct
regardless of access path.

Potential issues/improvements:

What should the escape-sequence display if cluster_name is not set or the
cluster is a pre-9.5 version. %M? %m?

In future server versions should there be a default for cluster_name if it
is not set? If so, what should it be? Would the server canonical hostname +
listen-port be reasonable?

Cheers,
Steve

Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]

[Steve Crawford]

I was unaware that we had +- infinity for numeric.

select pg_typeof(extract(epoch from current_date));
   pg_typeof
--
double precision

Given that null is a "special value that is used to indicate the absence of
any data value" and that attributes like month or day-of-week will have no
value for a date of infinity I'd be OK with returning null.

I suppose the real question is what return value will cause the smallest
amount of breakage and surprising results. Throwing an error will
definitely break legit queries.

Cheers,
Steve


On Mon, Nov 9, 2015 at 8:22 AM, Kevin Grittner  wrote:

> On Monday, November 9, 2015 9:37 AM, Robert Haas 
> wrote:
> > On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy 
> wrote:
>
> >> I'd like to raise a topic about extracting fields from infinite
> >> timestamps, so much more that it is mentioned in the TODO list:
> >> "Determine how to represent date/time field extraction on infinite
> >> timestamps".
> >>
> >> Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
> >> result "0" as a mark it has "special" input value.
> >>
> >> The most confusing case is 'epoch' field: returning "0" from
> >> "infinity" means the same thing as returning "0" from "1970-01-01+00".
> >>
> >> Returning zero in most other cases is only slightly less confusing
> >> (may be because for me they are less often used).
> >> For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
> >> 'Infinity')" with result 0, as if it is Sunday?
> >> The same thing with fields: decade, hour, minute, seconds,
> >> microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
> >> Also for "millennium" and "year" (with the note "Keep in mind there is
> >> no 0 AD") current returning value is _between_ allowed values, but
> >> disallowed.
>
> > We're definitely not going to back-patch this.  Let's tally up the
> > votes on that other thread:
> >
> > Danielle Varrazzo: infinity
> > Bruce Momjian: infinity
> > Robert Haas: not sure we want to change anything, but if so let's
> > definitely NOT throw an error
> > Alvaro Herrera: infinity for epoch, but what about other things?
> > Brendan Jurd: infinity for epoch, error for other things
> > Tom Lane: infinity for epoch, error or NaN for other things
> > Josh Berkus: definitely change something, current behavior sucks
> >
> > That doesn't seem like enough consensus to commit this patch, which
> > would change everything to +/-infinity.  That particular choice
> > wouldn't bother me much, but it sounds like other people aren't sold.
> > I think we need to try to hash that out a little more rather than
> > rushing into a backward-incompatible change.
>
> I agree that none of this should be back-patched.
>
> I agree that a timestamp[tz] of infinity should yield infinity for
> epoch.
>
> My first choice for other things would be NaN, but throwing an
> error instead would be OK.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] No Issue Tracker - Say it Ain't So!

[Steve Crawford]

On Mon, Sep 28, 2015 at 4:41 PM, Jim Nasby  wrote:

> Note that since they also offer a hosted solution we should use that to
> play with instead of trying to install it at this point.
>
> Integrating the issue tracker looks like it's just a call to this API:
> http://doc.gitlab.com/ce/api/issues.html#new-issue. I don't normally do
> web development myself so I'd rather not figuring out how to setup a copy
> of the website to hack on, but if no one else wants to try it I can take a
> stab at it.
>
> Presumably mirroring our git repository would work the same as it does for
> mirroring to GitHub. My guess is that would be enough to get the basic
> git/issue tracker integration working.
>
> Commitfest could be tied in as well. Presumably each commitfest would be a
> milestone (http://doc.gitlab.com/ce/api/milestones.html) and each
> submission an issue.
>
>
One of the issues identified with  Github is that it is closed and
commercial which goes against the expressed desires of the PostgreSQL
community. As such, it's important to note that Gitlab seems to be in the
"freemium" model with a "community" and an "enterprise" version so for
comparison purposes we should only look at the features in the open-source
community version:
https://about.gitlab.com/features/#compare

Cheers,
Steve

Re: [HACKERS] No Issue Tracker - Say it Ain't So!

[Steve Crawford]

On Tue, Sep 29, 2015 at 7:16 AM, David Fetter  wrote:

> ...What we're not fine with is depending on a proprietary system, no
> matter what type of license, as infrastructure...
>
>
Exactly. Which is why I was warning about latching onto features only
available in the closed enterprise version.

Cheers,
Steve

Re: [HACKERS] What does RIR as in fireRIRrules stand for?

[Steve Crawford]

Candidate for Appendix K?

Cheers,
Steve

On Thu, Aug 27, 2015 at 6:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Andres Freund and...@anarazel.de writes:
  On 2015-08-27 09:43:09 -0400, Tom Lane wrote:
  http://www.postgresql.org/message-id/3e887762.5b68f...@yahoo.com

  Oops. I saw that message but thought, based on the subject, it'd a scam
  mail...

  Can we either add a comment to the effect of Jan's message, or just
  renamoe the functions/variables?

 Let's add a comment.

 regards, tom lane


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?

[Steve Crawford]

On 08/28/2014 01:51 AM, rohtodeveloper wrote:

Hi,all

I have a question about data type timestamp with time zone.
Why data of timestamptz does not store value of timezone passed to it?

Considering the following example.

postgres=# select '2014-08-28 14:30:30.423602+02'::timestamp with time 
zone;

  timestamptz
---
 2014-08-28 20:30:30.423602+08
(1 row)

The timezone of output(+08) is different with the original input 
value(+02).
It seems not to be good behavior.But the behavior of date type time 
with time zone is correct.


postgres=# select '14:30:30.423602+02'::time with time zone;
   timetz

 14:30:30.423602+02
(1 row)

If the corrent behavior of timestamptz is not suitable,is there any 
plan to correct the behavior of timestamptz or create a new data type 
which can store timestamp with timezone?



*）manual--8.5.1.3. Time Stamps
-
For timestamp with time zone, the internally stored value is always in 
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean 
Time, GMT). An input value that has an explicit time zone specified is 
converted to UTC using the appropriate offset for that time zone. If 
no time zone is stated in the input string, then it is assumed to be 
in the time zone indicated by the system's TimeZone parameter, and is 
converted to UTC using the offset for the timezone zone.

-


This is actually more appropriate for the General mailing list. But...

I have always considered timestamp with time zone to be a bad 
description of that data type but it appears to be a carryover from the 
specs. It is really a point in time with 2014-08-28 
14:30:30.423602+02 and 2014-08-28 20:30:30.423602+08 merely being 
different representations of that same point in time. Time with time 
zone is a similarly bad name as it is really a time with offset from GMT.


It should be noted that -08, +02 etc. are actually *offsets* from GMT 
and are not, technically, time-zones. A time-zone includes additional 
information about the dates on which that offset changes due to daylight 
saving schedules and politically imposed changes thereto.


As the manual states, The type time with time zone is defined by the 
SQL standard, but the definition exhibits properties which lead to 
questionable usefulness. From the above, you can infer that one of 
those issues is that the offset changes based on the date but there is 
no date in a time with time zone field. Among the things you will 
discover is that '12:34:56-04' is legal input for time with time zone 
but '12:34:56 America/New_York' is not because you can't determine the 
offset without a date. Adding a date like '2014-08-28 12:34:56 
America/New_York' will give you a time with offset or what the spec 
calls time with time zone (12:45:31.899075-04) though it really 
doesn't have any information about America/New_York.


That the internal representation is in GMT is a curiosity but ultimately 
irrelevant as is it up to PostgreSQL to appropriately convert/display 
whatever it stores internally to the input and output format specified 
by the user.


The varying values of things like day, month and year combined with 
constantly shifting definitions of time-zones make date and time 
handling, *um* interesting. Is the interval 1-day shorthand for 
24-hours or the same time of day the following day (i.e. when crossing 
DST boundaries). What is the appropriate value of March 31 minus one 
month? February 29 plus one year?


Read and experiment to understand the quirks and the design-decisions 
implemented in PostgreSQL (or other program).


Cheers,
Steve

Re: [HACKERS] Hokey wrong versions of libpq in apt.postgresql.org

[Steve Crawford]

On 08/07/2014 04:30 PM, Joshua D. Drake wrote:


Hello,

I know this has been brought up before:

http://www.postgresql.org/message-id/20140724080902.ga28...@msg.df7cb.de


For reference, libpq and packaging issues discussed here as well:
http://www.postgresql.org/message-id/53a304bc.40...@pinpointresearch.com
http://www.postgresql.org/message-id/53989c91.6050...@pinpointresearch.com



But this is just plain wrong. I don't care that the FAQ (on the wiki) 
says we are doing it wrong for good reasons. When I (or anyone else) 
pulls postgresql-$version-dev, I want the libpq for my version. I do 
not want 9.3.


Yes, it should (because of protocol compatibility) work but it 
doesn't always (as stated in that email and in a similar problem we 
just ran into).


There can be unintended circumstances on machines when you mix and 
match like that. Can we please do some proper packaging on this?


+1

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] I thought we were changing the name of recvlogical?

[Steve Crawford]

On 05/20/2014 08:48 AM, Josh Berkus wrote:

I can't find the thread now, but I'm pretty sure that we decided to
change the name of pg_recvlogical, because its inconsistent with other
client utils?  No?



This thread, perhaps??

http://www.postgresql.org/message-id/20130923084634.ga15...@awork2.anarazel.de

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: iff - if

[Steve Crawford]

On 04/15/2014 05:36 PM, Andrew Dunstan wrote:


On 04/15/2014 06:26 PM, Thom Brown wrote:
On 15 April 2014 23:19, Andreas 'ads' Scherbaum 
adsm...@wars-nicht.de wrote:

Hi,

stumbled over a number of iff in the source where if is meant - 
not sure
what the real story behind this is, but attached is a patch to fix 
the about

80 occurrences.

This only appears in comments, not in any code path.
Yeah, apparently those are intentional, and mean if and only if 
(i.e. =)




This is a reasonably common idiom, or used to be.


If it has fallen into disuse the news has failed to reach me:

http://en.wikipedia.org/wiki/If_and_only_if
http://www.mathwords.com/i/if_and_only_if.htm
http://mathworld.wolfram.com/Iff.html
...

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Documentation patch for to_date and to_timestamp

[Steve Crawford]

The attached patch is in response to ongoing mailing-list questions 
regarding perceived weirdness in to_timestamp and to_date.


The patch modifies doc/src/sgml/func.sgml to add (see usage notes) in 
the description column for to_date and to_timestamp in the Formatting 
Functions table and adds the following two list items to the start of 
the usage notes for date/time conversion:


The to_date and to_timestamp functions exist to parse unusual input 
formats that cannot be handled by casting. These functions interpret 
input liberally and with minimal error checking so the conversion has 
the potential to yield unexpected results. Read the following notes and 
test carefully before use. Casting is the preferred method of conversion 
wherever possible.


Input to to_date and to_timestamp is not restricted to normal ranges 
thus to_date('20096040','MMDD') returns 2014-01-17 rather than 
generating an error.


Cheers,
Steve

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76d357..19197ce 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5426,7 +5426,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
  literalfunctionto_date(typetext/type, typetext/type)/function/literal
 /entry
 entrytypedate/type/entry
-entryconvert string to date/entry
+entryconvert string to date (see usage notes)/entry
 entryliteralto_date('05nbsp;Decnbsp;2000', 'DDnbsp;Monnbsp;')/literal/entry
/row
row
@@ -5448,7 +5448,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
  literalfunctionto_timestamp(typetext/type, typetext/type)/function/literal
 /entry
 entrytypetimestamp with time zone/type/entry
-entryconvert string to time stamp/entry
+entryconvert string to time stamp (see usage notes)/entry
 entryliteralto_timestamp('05nbsp;Decnbsp;2000', 'DDnbsp;Monnbsp;')/literal/entry
/row
row
@@ -5750,10 +5750,32 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
 
para
 Usage notes for date/time formatting:
+   /para
 
+   para
 itemizedlist
  listitem
   para
+   The functionto_date/function and functionto_timestamp/function
+   functions exist to parse unusual input formats that cannot be handled
+   by casting.  These functions interpret input liberally and with minimal
+   error checking so the conversion has the potential to yield unexpected
+   results.  Read the following notes and test carefully before use.
+   Casting is the preferred method of conversion wherever possible.
+  /para
+ /listitem
+
+ listitem
+  para
+   Input to functionto_date/function and
+   functionto_timestamp/function is not restricted to normal ranges
+   thus literalto_date('20096040','MMDD')/literal returns
+   literal2014-01-17/literal rather than generating an error.
+  /para
+ /listitem
+
+ listitem
+  para
literalFM/literal suppresses leading zeroes and trailing blanks
that would otherwise be added to make the output of a pattern be
fixed-width.  In productnamePostgreSQL/productname,

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Documentation patch for date/time formatting functions

[Steve Crawford]

Due to a variety of messages over time regarding perceived weirdness in 
to_timestamp and to_date, this patch adds (see notes) in the 
description column for to_date and to_timestamp in the Formatting 
Functions table and adds the following text to the opening of the usage 
notes for date/time conversion:


The to_date and to_timestamp functions exist to handle unusual input 
formats that cannot be converted by simple casting.  These functions 
interpret input liberally and with minimal error checking and while they 
will produce valid output, the conversion has the potential to yield 
unexpected results.  Read the following notes and test carefully before 
use.  Casting is the preferred method of conversion wherever possible.


It also adds the following usage note:

Input to to_date and to_timestamp is not restricted to normal ranges 
thus to_date('20096040','MMDD') returns 2014-01-17 rather than 
causing an error.


This is the first patch I have submitted directly. Please advise if I 
have made any errors in method, style, etc.


Cheers,
Steve

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a1d3aee..6f5eee0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5426,7 +5426,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
literalfunctionto_date(typetext/type, 
typetext/type)/function/literal

 /entry
 entrytypedate/type/entry
-entryconvert string to date/entry
+entryconvert string to date (see notes)/entry
entryliteralto_date('05nbsp;Decnbsp;2000', 
'DDnbsp;Monnbsp;')/literal/entry

/row
row
@@ -5448,7 +5448,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
literalfunctionto_timestamp(typetext/type, 
typetext/type)/function/literal

 /entry
 entrytypetimestamp with time zone/type/entry
-entryconvert string to time stamp/entry
+entryconvert string to time stamp (see notes)/entry
entryliteralto_timestamp('05nbsp;Decnbsp;2000', 
'DDnbsp;Monnbsp;')/literal/entry

/row
row
@@ -5750,10 +5750,27 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');

para
 Usage notes for date/time formatting:
-
+   /para
+   para
+The functionto_date/function and functionto_timestampfunction
+functions exist to handle unusual input formats that cannot be
+converted by simple casting.  These functions interpret input 
liberally
+and with minimal error checking and while they will produce valid 
output,

+the conversion has the potential to yield unexpected results. Read the
+following notes and test carefully before use.  Casting is the
+preferred method of conversion wherever possible.
 itemizedlist
  listitem
   para
+   Input to functionto_date/function and
+   functionto_timestampfunction is not restricted to normal ranges
+   thus literalto_date('20096040','MMDD')/literal returns
+   literal2014-01-17/literal rather than causing an error.
+  /para
+ /listitem
+
+ listitem
+  para
literalFM/literal suppresses leading zeroes and trailing 
blanks

that would otherwise be added to make the output of a pattern be
fixed-width.  In productnamePostgreSQL/productname,


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Personal note: taking some vacation time in Sep/Oct

[Steve Crawford]

On 08/19/2013 11:55 PM, Gavin Flower wrote:

On 20/08/13 15:26, Tom Lane wrote:

I will be taking a long (and long-overdue) vacation...
but, But, BUT, you're not human - you can't possibly take leave, the 
sky will fall  all manners of divers calamities will come to pass!!!


As if on cue: 
http://www.nasa.gov/content/goddard/the-suns-magnetic-field-is-about-to-flip/


Cheers,
Steve

Re: [HACKERS] Deprecating RULES

[Steve Crawford]

On 10/17/2012 04:25 PM, Tom Lane wrote:
...Now having said that, I would definitely like to see rules in their 
current form go away eventually. But not without a substitute. 
Triggers are not a complete replacement, and no amount of wishful 
thinking makes them so.

...
Perhaps it would be more profitable to try to identify the pain points 
that make people so eager to get rid of rules, and then see if we 
could alleviate them.


Alternately/additionally identify the deficiencies in triggers that 
drive users to prefer rules. For example, a common need is to update a 
log table whenever updates are made to a main table.


Using rules to accomplish this is very easy to understand and write, 
even for most beginners. (Understand properly including limitations and 
dangers is another issue, of course.) It is also easy to maintain. If 
you drop the table, the rule is cleaned up as well.


With triggers you need to select from a variety of available languages, 
write a function in that language and write a trigger that calls that 
function. Dropping the function will remove the trigger but the user 
must remember to delete the function as well, if desired. Nothing 
insurmountable but inconvenient compared to the use of a rule.


Per the documentation PostgreSQL only allows the execution of a 
user-defined function for the triggered action. The standard allows the 
execution of a number of other SQL commands...


There may be valid reasons why implementing that part of the SQL 
standard in PostgreSQL is difficult or unwise but removing that 
limitation on triggers would eliminate one annoyance that pushes users 
toward rules.


Cheers,
Steve



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 9.2 bug? variable not found in subplan target list

[Steve Crawford]

On 09/12/2012 07:36 AM, Louis-David Mitterrand wrote:

See test case at: http://titus.apartia.fr/stuff/pg_92_error_sql.txt

Works fine on 9.1


I cannot absolutely say it is a bug as I haven't yet reviewed the 
relevant release notes but I can confirm that I also see your test-case 
working on 9.1 and failing on 9.2.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pgstat wait timeout

[Steve Crawford]

On 12/28/2011 05:05 AM, Alvaro Herrera wrote:

Excerpts from Steve Crawford's message of mar dic 27 22:51:06 -0300 2011:

I have a system (9.0.4 on Ubuntu Server 10.04 LTS x86_64) that is
currently in test/dev mode. I'm currently seeing the following messages
occurring every few seconds:

...
Dec 27 17:43:22 foo postgres[23693]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:43:27 foo postgres[27324]: [71400-1] : WARNING:  pgstat wait
timeout
Dec 27 17:43:33 foo postgres[23695]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:43:54 foo postgres[27324]: [71401-1] : WARNING:  pgstat wait
timeout

Hm, so can you strace the stats collector to see what it's doing?  Maybe
grab a backtrace with GDB from it before anything else.

My guess is 27324 is the autovac launcher and the others are autovac
workers just as they die.

You are correct. 27324 is the launcher and the others are autovac 
workers. Here's the strace of the stats collector process:


getppid()   = 27320
poll([{fd=8, events=POLLIN|POLLERR}], 1, 2000) = 0 (Timeout)
getppid()   = 27320
poll([{fd=8, events=POLLIN|POLLERR}], 1, 2000) = 0 (Timeout)
getppid()   = 27320
poll([{fd=8, events=POLLIN|POLLERR}], 1, 2000) = 0 (Timeout)
rinse...lather...repeat...ad nauseum...

And the backtrace:

#0  0x7ff4d2e80f58 in poll () from /lib/libc.so.6
#1  0x7ff4d4e6f465 in ?? ()
#2  0x7ff4d4e6fd83 in pgstat_start ()
#3  0x7ff4d4e73475 in ?? ()
#4 signal handler called
#5  0x7ff4d2e85fd3 in select () from /lib/libc.so.6
#6  0x7ff4d4e71b93 in ?? ()
#7  0x7ff4d4e74b01 in PostmasterMain ()
#8  0x7ff4d4e193b3 in main ()

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pgstat wait timeout

[Steve Crawford]

On 12/28/2011 09:34 AM, Alvaro Herrera wrote:

Excerpts from Steve Crawford's message of mié dic 28 13:24:37 -0300 2011:

On 12/28/2011 05:05 AM, Alvaro Herrera wrote:

Excerpts from Steve Crawford's message of mar dic 27 22:51:06 -0300 2011:

I have a system (9.0.4 on Ubuntu Server 10.04 LTS x86_64) that is
currently in test/dev mode. I'm currently seeing the following messages
occurring every few seconds:

...
Dec 27 17:43:22 foo postgres[23693]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:43:27 foo postgres[27324]: [71400-1] : WARNING:  pgstat wait
timeout
Dec 27 17:43:33 foo postgres[23695]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:43:54 foo postgres[27324]: [71401-1] : WARNING:  pgstat wait
timeout

Hm, so can you strace the stats collector to see what it's doing?  Maybe
grab a backtrace with GDB from it before anything else.

My guess is 27324 is the autovac launcher and the others are autovac
workers just as they die.


You are correct. 27324 is the launcher and the others are autovac
workers. Here's the strace of the stats collector process:

getppid()   = 27320
poll([{fd=8, events=POLLIN|POLLERR}], 1, 2000) = 0 (Timeout)
getppid()   = 27320
poll([{fd=8, events=POLLIN|POLLERR}], 1, 2000) = 0 (Timeout)
getppid()   = 27320
poll([{fd=8, events=POLLIN|POLLERR}], 1, 2000) = 0 (Timeout)
rinse...lather...repeat...ad nauseum...

Weird ... even across more pgstat wait timeout messages?  It's like
it's not getting the inquiry messages that would tell it to write the
file ... something wrong with the UDP socket perhaps?


Bingo!

postgres  27325 postgres8u *IPv6*5379428   
0t0UDP localhost:47204-localhost:47204


In working on diagnosing a network timeout issue over an IPv4 to IPv4 
VPN I disabled IPv6 via sysctl on this machine and pretty much forgot 
about it since we are still IPv4 internally. But PostgreSQL had already 
established a (now non-functional) IPv6 local connection. Re-enabling 
IPv6, as it was not related to the VPN timeouts, corrected the pgstat 
wait timeout issue.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pgstat wait timeout

[Steve Crawford]

I have a system (9.0.4 on Ubuntu Server 10.04 LTS x86_64) that is 
currently in test/dev mode. I'm currently seeing the following messages 
occurring every few seconds:


...
Dec 27 17:43:22 foo postgres[23693]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:43:27 foo postgres[27324]: [71400-1] : WARNING:  pgstat wait 
timeout

Dec 27 17:43:33 foo postgres[23695]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:43:54 foo postgres[27324]: [71401-1] : WARNING:  pgstat wait 
timeout

Dec 27 17:43:59 foo postgres[23697]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:44:04 foo postgres[27324]: [71402-1] : WARNING:  pgstat wait 
timeout

Dec 27 17:44:09 foo postgres[23715]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:44:17 foo postgres[27324]: [71403-1] : WARNING:  pgstat wait 
timeout

Dec 27 17:44:22 foo postgres[23716]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:44:27 foo postgres[27324]: [71404-1] : WARNING:  pgstat wait 
timeout

Dec 27 17:44:33 foo postgres[23718]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:44:54 foo postgres[27324]: [71405-1] : WARNING:  pgstat wait 
timeout

Dec 27 17:44:59 foo postgres[23824]: [6-1] : WARNING:  pgstat wait timeout
Dec 27 17:45:04 foo postgres[27324]: [71406-1] : WARNING:  pgstat wait 
timeout


I can't correlate events exactly, but the messages seem to have started 
shortly after I dropped a pgbench user and database. My Googling turned 
up various requests for debugging info on hackers. Since the system 
isn't live, I haven't touched it in case anyone wants me to collect 
debugging info.


Otherwise, I plan on just blowing the install away and replacing it with 9.1

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade - add config directory setting

[Steve Crawford]

On 09/29/2011 08:20 AM, Bruce Momjian wrote:

...
1  document the limitation and require users to use symlinks
2  add a --old/new-configdir parameter to pg_upgrade
3  have pg_upgrade find the real data dir by starting the server
4  add a flag to some tool to return the real data dir, and backpatch
that
5. (really 3a). Have pg_upgrade itself check the specified --XXX-datadir 
for postgresql.conf and use the data_directory setting therein using the 
same rules as followed by the server.


This would mean that there are no new options to pg_upgrade and that 
pg_upgrade operation would not change when postgresql.conf is in the 
data-directory. This would also make it consistent with PostgreSQL's 
notion of file-locations:


If you wish to keep the configuration files elsewhere than the data 
directory, the postgres -D command-line option or PGDATA environment 
variable must point to the directory containing the configuration files, 
and the data_directory parameter must be set in postgresql.conf...


So for backporting, it could just be considered a bug fix that aligns 
pg_upgrade's interpretation of datadir to that of the server.


Cheers,
Steve

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade - add config directory setting

[Steve Crawford]

On 09/28/2011 12:49 AM, Peter Eisentraut wrote:

On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:

It would perhaps be useful to add optional --old-confdir and
--new-confdir parameters to pg_upgrade. If these parameters are absent
then pg_upgrade would work as it does now and assume that the config
files are in the datadir.

It should work the same way the postmaster itself works: If the given
directory is not a data directory, look for the postgresql.conf file and
look there for the location of the data directory.




That would make sense to me (I actually tried setting the datadirs based 
on that assumption). It would require adding that feature to pg_upgrade 
and tweaking the docs for --XXX-datadir but would not require any new 
parameters.


Cheers,
Steve

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pg_upgrade - add config directory setting

[Steve Crawford]

It would perhaps be useful to add optional --old-confdir and 
--new-confdir parameters to pg_upgrade. If these parameters are absent 
then pg_upgrade would work as it does now and assume that the config 
files are in the datadir.


The reason for this suggestion is that packages for Ubuntu (and I 
suppose Debian and possibly others) place the config files in a 
different directory than the data files.


The Ubuntu packaging, for example, puts all the configuration files in 
/etc/postgresql/VERSION/main/.


If I set the data-directories to /var/lib/postgresql/VERSION/main then 
pg_upgrade complains about missing config files.


If I set the data directories to /etc/postgresql/VERSION/main/ then 
pg_upgrade complains that the base subdirectory is missing.


Temporarily symlinking postgresql.conf and pg_hba.conf from the config 
directory to the data directory allowed the upgrade to run successfully 
but is a bit more kludgey and non-obvious.


Cheers,
Steve




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] generate_series() Interpretation

[Steve Crawford]

On 06/27/2011 10:49 AM, David E. Wheeler wrote:

Hackers,

I'm curious about behavior such as this:

bric=# select generate_series('2011-05-31'::timestamp , 
'2012-04-01'::timestamp, '1 month');
generate_series
-
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-30 00:00:00
  2011-08-30 00:00:00
  2011-09-30 00:00:00
  2011-10-30 00:00:00
  2011-11-30 00:00:00
  2011-12-30 00:00:00
  2012-01-30 00:00:00
  2012-02-29 00:00:00
  2012-03-29 00:00:00

It seems to me that this is subject to interpretation. If I was building a 
calendaring app, for example, I might rather that the results were:

generate_series
-
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-31 00:00:00
  2011-08-31 00:00:00
  2011-09-30 00:00:00
  2011-10-31 00:00:00
  2011-11-30 00:00:00
  2011-12-31 00:00:00
  2012-01-31 00:00:00
  2012-02-29 00:00:00
  2012-03-31 00:00:00

Is there some way to change the interpretation of interval calculation like 
this? Or would I just have to write my own function to do it the way I want?

Thanks,

David




That's just how intervals that represent varying periods of time work. 
You would need to write your own. But a series of end-of-month dates is 
pretty easy:
select generate_series('2011-06-01'::timestamp , 
'2012-04-01'::timestamp, '1 month') - '1 day'::interval;

  ?column?
-
 2011-05-31 00:00:00
 2011-06-30 00:00:00
 2011-07-31 00:00:00
 2011-08-31 00:00:00
 2011-09-30 00:00:00
 2011-10-31 00:00:00
 2011-11-30 00:00:00
 2011-12-31 00:00:00
 2012-01-31 00:00:00
 2012-02-29 00:00:00
 2012-03-31 00:00:00

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] generate_series() Interpretation

[Steve Crawford]

On 06/27/2011 10:56 AM, David E. Wheeler wrote:

On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:


That's just how intervals that represent varying periods of time work. You 
would need to write your own. But a series of end-of-month dates is pretty easy:
select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp, '1 
month') - '1 day'::interval;

Yeah, but it's trickier if you have a calendaring app and don't know that date 
a user has chosen for a monthly recurring event. They might have selected June 
30, in which case only February would ever need to be different than the 
default.

Best,

David



The query is marginally trickier. But the better calendaring apps give a 
variety of options when selecting repeat: A user who selects June 30, 
2011 and wants a monthly repeat might want:


30th of every month - skip months without a 30th
30th of every month - move to end-of-month if 30th doesn't exist
Last day of every month
Last Thursday of every month

Typical payday repeats are the 15th and last -day-of-month if a workday 
or the closest preceding workday if not, second and last Friday, 
every other Friday...


No matter how '1 month' is interpreted in generate_series, the 
application programmer will still need to write the queries required to 
handle whatever calendar-repeat features are deemed necessary.


Cheers,
Steve



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] generate_series() Interpretation

[Steve Crawford]

Yeah, which is why I said it was subject to interpretation. Of course there's 
no way to tell generate_series() which to use, which is what I figured.

Fortunately PostgreSQL uses the same interpretation for '1 month'  when 
used in generate_series that it does everywhere else - to do otherwise 
would be hella confusing. :)


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] storing TZ along timestamps

[Steve Crawford]

On 06/01/2011 05:18 PM, Alvaro Herrera wrote:

Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Yes

That eliminates many of my issues - I just didn't want the type changed 
underneath me. But some considerations remain - including some new that 
have crossed my mind:


1. How would the time-zone be defined in this composite? Offset from 
GMT? Timezone (well, link thereto) with all DST rules intact? Would 
extract need to be modified to include the ability to grab the timezone?


2. What would be the precedence for defining originating timezone? 
Default? Set timezone to? ...at time zone...? Based on the timestamp 
(2011-06-02 12:34:56-07)?


3. Would indexing/sorting include the originating zone? If so, how would 
time zones collate (base offset, actual offset based on the timestamp, 
name)?


4. What would be the corresponding type when used with 
Perl/PHP/Python/... applications - would they require special 
non-standard handling?


Since this isn't going to alter my current beloved timestamptz and I 
don't have a use-case I leave the decisions on the above to others. But 
in my imagined use-cases I still see the originating zone as a separate 
piece of information better handled as a different column - for example 
sorting by timestamp plus priority or selecting everything for a 
specific time zone.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] storing TZ along timestamps

[Steve Crawford]

On 05/28/2011 02:58 PM, Peter Eisentraut wrote:

On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:

And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time. When you
retrieve
it, it is shown in your timezone and we both pick up the phone at the
correct time. And if I know I'll be somewhere else at that time, I
just
ask for the data in that zone. Altering the data type gains nothing.

How about a recurring appointment that happens every Tuesday whenever it
is 9:00am in California, independent of DST (in California or where ever
the participant actually is).  I'm not sure how to solve that within the
SQL framework.  You might need to use time with time zone with a
placeholder timezone, and then a rule that date + time with time zone
creates a timestamp with time zone that resolves the time zone for that
particular day.



Interval math is pretty smart about that:

select '2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7 
days'::interval * generate_series(1,60));


 2011-06-07 09:00:00-07
 2011-06-14 09:00:00-07
 2011-06-21 09:00:00-07
 2011-06-28 09:00:00-07
 2011-07-05 09:00:00-07
 2011-07-12 09:00:00-07
 2011-07-19 09:00:00-07
 2011-07-26 09:00:00-07
 2011-08-02 09:00:00-07
 2011-08-09 09:00:00-07
 2011-08-16 09:00:00-07
 2011-08-23 09:00:00-07
 2011-08-30 09:00:00-07
 2011-09-06 09:00:00-07
 2011-09-13 09:00:00-07
 2011-09-20 09:00:00-07
 2011-09-27 09:00:00-07
 2011-10-04 09:00:00-07
 2011-10-11 09:00:00-07
 2011-10-18 09:00:00-07
 2011-10-25 09:00:00-07
 2011-11-01 09:00:00-07
 2011-11-08 09:00:00-08
 2011-11-15 09:00:00-08
 2011-11-22 09:00:00-08
 2011-11-29 09:00:00-08
 2011-12-06 09:00:00-08
 2011-12-13 09:00:00-08
 2011-12-20 09:00:00-08
 2011-12-27 09:00:00-08
 2012-01-03 09:00:00-08
 2012-01-10 09:00:00-08
 2012-01-17 09:00:00-08
 2012-01-24 09:00:00-08
 2012-01-31 09:00:00-08
 2012-02-07 09:00:00-08
 2012-02-14 09:00:00-08
 2012-02-21 09:00:00-08
 2012-02-28 09:00:00-08
 2012-03-06 09:00:00-08
 2012-03-13 09:00:00-07
 2012-03-20 09:00:00-07
 2012-03-27 09:00:00-07
 2012-04-03 09:00:00-07
 2012-04-10 09:00:00-07
 2012-04-17 09:00:00-07
 2012-04-24 09:00:00-07
 2012-05-01 09:00:00-07
 2012-05-08 09:00:00-07
 2012-05-15 09:00:00-07
 2012-05-22 09:00:00-07
 2012-05-29 09:00:00-07
...

Or if you have to call in from London (notice the blips between 4pm and 
5pm due to London and California switching to/from DST on different dates):


select ('2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7 
days'::interval * generate_series(1,60))) at time zone 'Europe/London';

-
 2011-06-07 17:00:00
 2011-06-14 17:00:00
 2011-06-21 17:00:00
 2011-06-28 17:00:00
 2011-07-05 17:00:00
 2011-07-12 17:00:00
 2011-07-19 17:00:00
 2011-07-26 17:00:00
 2011-08-02 17:00:00
 2011-08-09 17:00:00
 2011-08-16 17:00:00
 2011-08-23 17:00:00
 2011-08-30 17:00:00
 2011-09-06 17:00:00
 2011-09-13 17:00:00
 2011-09-20 17:00:00
 2011-09-27 17:00:00
 2011-10-04 17:00:00
 2011-10-11 17:00:00
 2011-10-18 17:00:00
 2011-10-25 17:00:00
 2011-11-01 16:00:00
 2011-11-08 17:00:00
 2011-11-15 17:00:00
 2011-11-22 17:00:00
 2011-11-29 17:00:00
 2011-12-06 17:00:00
 2011-12-13 17:00:00
 2011-12-20 17:00:00
 2011-12-27 17:00:00
 2012-01-03 17:00:00
 2012-01-10 17:00:00
 2012-01-17 17:00:00
 2012-01-24 17:00:00
 2012-01-31 17:00:00
 2012-02-07 17:00:00
 2012-02-14 17:00:00
 2012-02-21 17:00:00
 2012-02-28 17:00:00
 2012-03-06 17:00:00
 2012-03-13 16:00:00
 2012-03-20 16:00:00
 2012-03-27 17:00:00
 2012-04-03 17:00:00
 2012-04-10 17:00:00
 2012-04-17 17:00:00
 2012-04-24 17:00:00
 2012-05-01 17:00:00
 2012-05-08 17:00:00
...

Cheers,
Steve

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] storing TZ along timestamps

[Steve Crawford]

On 05/27/2011 01:43 PM, Alvaro Herrera wrote:

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.
I am very interested in the use-case for this (in part as I'm working on 
a PG related time talk). My experience thus far is that people who want 
this do not fully understand the nature of date-time calculations and 
variables in PG.

It is currently possible to store a TZ in a separate column, but this is
a bit wasteful and not very convenient anyway.





Are there objections to the general idea?  If not, I'll flesh a more
complete proposal.

I'm not crazy about it.

Although time-stamp-with-time-zone is, perhaps, a bad name for what is 
actually a point in time, a point-in-time is what timestamptz 
represents. I can enter it and allow my defaults to take over, specify 
abbreviations, explicit offsets or long names none of which change the 
actual point in time. Likewise, I can display said point-in-time in any 
of dozens of ways according to my needs.


steve=# select '2011-05-27 12:34'::timestamptz;
  timestamptz

 2011-05-27 12:34:00-07

steve=# select '2011-05-27 12:34-07'::timestamptz;
  timestamptz

 2011-05-27 12:34:00-07

steve=# select '2011-05-27 12:34 PDT'::timestamptz;
  timestamptz

 2011-05-27 12:34:00-07

steve=# select '2011-05-27 11:34 PST'::timestamptz;
  timestamptz

 2011-05-27 12:34:00-07

steve=# select '2011-05-27 15:34 US/Eastern'::timestamptz;
  timestamptz

 2011-05-27 12:34:00-07

select now() - '02:58:54.605041'::interval;
   ?column?
---
 2011-05-27 12:34:00.394959-07

Granted, I'm a random sample of 1, but I've never found anyone with a 
real need for this feature - especially since the capability already 
exists to achieve the requested result, and much more flexibly, by 
either a separate column or a user-defined type.


Questions:

What would be the storage impact (tables, indexes and backups) for those 
of use with tens-of-millions of pieces of timestamp data?


What type of timestamp would be stored? Abbreviated/offset (PST, -07), 
full (US/Eastern) or a mix? Is there an expectation that the stored time 
zone information would be used for any calculation purposes? If so, how 
would rules be applied? Would there be any form of error-checking? 
Currently PG accepts non-existent time zones but maps them to UTC:


steve=# select '2011-05-27 15:34'::timestamptz at time zone 'US/f00';
  timezone
-
 2011-05-27 15:34:00


Would there be any impact to existing queries?

How would dump/restore issues be handled - especially if the time-zone 
info changes in between?


More as I think of them.

Cheers,
Steve




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] storing TZ along timestamps

[Steve Crawford]

On 05/27/2011 04:29 PM, Greg Stark wrote:

On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
scrawf...@pinpointresearch.com  wrote:

I am very interested in the use-case for this (in part as I'm working on a
PG related time talk). My experience thus far is that people who want this
do not fully understand the nature of date-time calculations and variables
in PG.

The use cases I recall having been mentioned in the past were accurate
data retention and calendaring applications.

Accurate data retention for things like drug trials need to guarantee
they retain precisely what the user entered, not an equivalent value.
If you run a report on a drug trial you need to see that the event was
recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen
to run the report in London.

And calendaring apps want to know what timezone is attached to an
event, not only the point in time at which it occurs. If your plane
flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know
that to book your taxi at 2:30pm EST -- not 7:30pm GMT.

Both of these two cases can be handled differently. The former by
storing the raw text inputs and then storing the interpreted value as
a derived column separetly, and the latter by storing the local time
zone to use for display as an additional attribute along with the
local address and other attributes of the calendar event.

So the proposed change does not handle the first case as you need to 
capture the raw input.


And the second case is already well handled. In fact calendaring is a 
great example. I enter the time for the teleconference and PG nicely 
uses my default timezone to store the point-in-time. When you retrieve 
it, it is shown in your timezone and we both pick up the phone at the 
correct time. And if I know I'll be somewhere else at that time, I just 
ask for the data in that zone. Altering the data type gains nothing.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] Date conversion using day of week

[Steve Crawford]

On 03/31/2011 08:00 AM, Adrian Klaver wrote:

On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:

On 31 March 2011 03:15, Steve Crawfordscrawf...@pinpointresearch.com  wrote:

On 03/29/2011 04:24 PM, Adrian Klaver wrote:

...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
   to_date

  2011-03-28
...

You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.

My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:

The relevant paragraphs in the docs are:

--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:

 * Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
 * Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.

Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a month or day of month has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--

We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
  This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.

With the attached patch applied, this is what happens instead:

# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR:  invalid combination of date conventions
HINT:  Do not mix Gregorian and ISO week date conventions in a
formatting template.

If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.

Just to play Devils advocate here, but why not? The day name is the same either
way, it is the index that changes. I am not sure why that could not be context
specific?



A week day represented as an int is ambiguous - as you mention, the 
index is necessary to decode to the correct day. Sunday is unambiguous 
so we could do something reasonable. But from everything I've read 
(though I didn't actually shell out 130CHF for a full 33-page copy of 
ISO8601:2004), the ISO *week* date format does not represent day-of-week 
as other than a numeric value so it would not really be an ISO8601 
formatted date and I would be tempted to thrown an error. However...


This whole discussion opens a #10 sized can o' worms. Admittedly, I 
don't have good knowledge of any SQL-mandated interpretations of an ISO 
date - but based on my reading of ISO formatting I see the following issues:


1. What we describe in the documentation as an ISO date is actually an 
ISO *week* date - a special purpose format included within ISO8601. 
2011-03-31 is also an ISO date as are 20110331, 20110331T013212 and 
20110331T21.3344298. Fixing this is probably as simple as a 
clarification in the documentation.


2. The ISO week-date format is defined as having the week-number 
prefaced by a W as in 2011-W03-7. From the ISO8601 FAQ page: Week 
date is an alternative date representation used in many commercial and 
industrial applications. It is: -Www-D where  is the Year in the 
Gregorian calendar, ww is the week of the year between 01 (the first 
week) and 52 or 53 (the last week), and D is the day in the week between 
1 (Monday) and 7 (Sunday). Example: 2003-W14-2 represents the second day 
of the fourteenth week of 2003. However PostgreSQL does *not* accept 
that as input even as specified as an ISO date:


select to_date('2003-W14-2', 'IYYY-IW-ID');
ERROR:  invalid value W1 for IW
DETAIL:  Value must be an integer.

Fixing this would require both a coding change and a decision whether or 
not to throw an error on incorrectly formatted input.


3. ISO8601 requires zero-padding. PostgreSQL, however, does not complain 
if that padding is missing. The following should be 2011-04-2 
(actually, 2011-W04-2 as noted above) but PostgreSQL accepts:


select to_date('2011-4-2', 'IYYY-IW-ID');
  to_date

 2011-01-25

However in ISO dates the hyphens are supposed to only be for easier 
reading by humans. But if we just remove them:


select to_date('201142', 'IYYYIWID');
  to_date

 2011-10-17

(Monday of the 42nd week).

Fix it and throw an error (and suffer the howls of anguish when backward 
compatibility is shattered) or tiptoe quietly 

Re: [HACKERS] [GENERAL] Date conversion using day of week

[Steve Crawford]

On 03/31/2011 10:51 AM, Brendan Jurd wrote:


I agree with your summary of the ISO standards.  Unfortunately,
to_date and its cohorts are not targeting ISO.  They are targeting
quasi-compatibility with some Oracle functions of the same name, I
suppose to make life easier for folks who are migrating from Oracle to
Postgres.  Any proposed reform of these (admittedly weird and kludgy)
functions is viewed through that lens, and usually rejected on those
grounds.  I've been down that road before.  There's not much point
having compatibility functions if they aren't, well, compatible.

In the big picture, to_date isn't meant to be the general entry point
for parsing dates.  If you wanted to make ISO8601 work as a syntax for
inputting date type literals vis. SELECT date '2011-W14-01', you might
have a better shot at getting that off the ground.

Well, to return to the original issue, should we allow the day to be 
spelled out and fix it (as noted in this thread it is non-standard but 
also unambiguous and we already allow plenty of non-standard formats) or 
throw an error? For me personally, either would be fine. What isn't 
correct is the current behavior:


select to_date('2011-13-SUN', 'IYYY-IW-DY');
  to_date

 2011-03-28

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] Date conversion using day of week

[Steve Crawford]

On 03/29/2011 04:24 PM, Adrian Klaver wrote:

...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
   to_date

  2011-03-28

...
You specified Sunday as the day but the date returned is a Monday. I 
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last 
day of an ISO week, it should have returned 2011-04-03.


My first inclination without consulting source or morning coffee is that 
PostgreSQL is seeing Sunday as day zero. Note that while:

select to_date('2011-13-1', 'IYYY-IW-ID');
  to_date

 2011-03-28

So does:
steve=# select to_date('2011-13-0', 'IYYY-IW-ID');
  to_date

 2011-03-28

So something isn't right. All sorts of other stuff is allowed as well - 
I don't know if that's by design or not:


steve=# select to_date('2011-13--23', 'IYYY-IW-ID');
  to_date

 2011-03-04


steve=# select to_date('2011-13-56', 'IYYY-IW-ID');
  to_date

 2011-05-22



Agreed, maintaining ISO arguments across the board is the way to go:

Monday
select to_date('2011-13-1', 'IYYY-IW-ID');...
We have to distinguish Gregorian and ISO days when represented as an 
integer since they define the start-of-week differently. Same with year. 
I don't think I've ever seen and ISO-week-date written as 2011-13-SUN 
but it *does* define a distinct date (which is not Monday). And even if 
PostgreSQL were updated to throw an error on that mix of formats it 
still leaves the problem of ISO day-of-week equal to zero.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Determining period between 2 dates

[Steve Crawford]

On 02/16/2011 09:07 AM, Marti Raudsepp wrote:

On Wed, Feb 16, 2011 at 18:03, Thom Brownt...@linux.com  wrote:

For the number of fortnights, that becomes:

select extract(epoch from now() - '2010-01-01 11:45:13'::timestamp)/60/60/24/14;

You'd think with PostgreSQL having such a rich type system, it
wouldn't need to come to that.  It's just asking for the number of
intervals between 2 timestamps rather than the number of seconds and
dividing it to the point you get your answer.

I think a good generic solution would be an interval/interval operator
that returns numeric. Then the above becomes:

SELECT (now() - timestamp '2010-01-01 11:45:13') / interval '2 weeks';

However, looking at the code, it's not so obvious what to do if the
intervals contain months.

Regards,
Marti

Actually, what I would really like is an option in the to_char format 
that would display an interval using an arbitrary combination of units. 
For instance, right now I can display parts of an interval:


steve=# select to_char('10d 11h 21m 3s'::interval, 'DD');
 to_char
-
 10

steve=# select to_char('10d 11h 21m 3s'::interval, 'SS');
 to_char
-
 03

steve=# select to_char('10d 11h 21m 3s'::interval, 'MI');
 to_char
-
 21

But those formats extract portions of the interval. I would like to be 
able to display the *entire* interval filling the largest portions first 
and continuing to smaller units, say:


select to_char('10d 11h 21m 3s'::interval, 'XM SS');
to_char

904863

or

select to_char('10d 11h 21m 3s'::interval, 'XM MI:SS');
to_char

15081:03

And as long as I'm on the subject, decimal time display would be handy 
as well (especially decimal hours and minutes).


The use case is anything that accumulates time - especially for billing 
purposes: 2.4 hours for the attorney, 11434.8 minutes of long-distance 
this month, etc.


I can write these myself, of course, but built-in would be nice.

-Steve



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] unlogged tables

[Steve Crawford]

On 11/17/2010 12:48 PM, Andrew Dunstan wrote:


Maybe VOLATILE for UNSYNCED? Not sure about UNLOGGED.


UNSAFE and EXTREMELY_UNSAFE?? :)

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] unlogged tables

[Steve Crawford]

On 11/17/2010 11:44 AM, Tom Lane wrote:
...because a backend crash has to be assumed to have corrupted 
unlogged tables...
   
So in a typical use-case, say storing session data on a web-site, one 
crashed backend could wreck sessions for some or all of the site? Is 
there a mechanism in the proposal that would allow a client to determine 
the state of a table (good, truncated, wrecked, etc.)?


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] what is good solution for support NULL inside string_to_array function?

[Steve Crawford]

Tom Lane wrote:

Josh Berkus j...@agliodbs.com writes:
  

quietly removing NULL is maybe good for compatibility but is wrong for
functionality. 
  


  

I agree.  I wasn't aware of this little misfeature.



  

Default display for NULL should be a zero-length string.



That's just as broken as Pavel's suggestion.  Unless you have something
that is guaranteed distingishable from the output of any non-null value,
you really can't make a significant improvement here.

regards, tom lane

  
Is this, perhaps, a generalized case of this long-running discussion 
from last year?: 
http://archives.postgresql.org/pgsql-hackers/2009-03/msg01350.php


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Anyone know if Alvaro is OK?

[Steve Crawford]

Marc G. Fournier wrote:


Is there a higher then normal amount of earthquakes happening 
recently? haiti, japan just had one for 6.9, there was apparently one 
in illinos a few weeks back, one on the Russia/China/N.Korean border 
and now Chile? 


Random events come in bunches - something I always stop to remind myself 
of whenever there is a sudden bunch of quakes, celebrity deaths, plane 
crashes, etc. Especially with relatively unusual events like 
great-quakes and plane crashes, it can be tough to see if there is any 
signal in the noise - a job I have to leave to experienced statisticians.


The world averages one great (8+) earthquake/year which, of course, 
means some years like 2008 have none but 2007 had four. 7-7.9 like Haiti 
or our own Loma Prieta quake are far more common averaging ~17/year.


Haiti is a catastrophe not because the quake was of unusual size (it 
barely made it into the 7-7.9 category and released less that 1/15 the 
energy of the Chile quake) but because the hypocenter was both shallow 
and fairly close to Port-au-Prince combined with terrible construction 
standards and virtually non-existent emergency-response capabilities in 
Haiti.


Some general quake stats/facts are here:
http://earthquake.usgs.gov/earthquakes/eqarchives/year/eqstats.php

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] array_to_string bug?

[Steve Crawford]

Tom Lane wrote:

David Fetter da...@fetter.org writes:
  

The next one is just plain unexpected.



array_to_string ignores null elements.  What do you think it should do
with them?

regards, tom lane

  
This seems somewhat related to the long-running discussion from back in 
February-April regarding string_to_array with empty input which faded 
away somewhere around here: 
http://archives.postgresql.org/pgsql-hackers/2009-04/msg00363.php. At 
the time the decision was to defer any decision to after 8.4.


Perhaps there is a solution which can address both cases - ideally one 
which would, to the extent practical, allow string_to_array to be the 
inverse of array_to_string. This could be particularly useful when 
dealing with clients that don't know how to directly deal with 
PostgreSQL arrays but which can generally easily deal with strings.


Although it might cause a fair amount of backward-compatibility trouble, 
the string representation could either use NULL to represent a null 
element as is allowed in other contexts or require that empty-string 
elements be represented as  to differentiate ,, (empty-string 
element) from ,, (null element).


Cheers,
Steve

Re: [HACKERS] EOL for 7.4?

[Steve Crawford]

Many people still run [7.4], so why make them move?


Many people still run 7.3... We made them move..

A nitpick. Nobody made anyone move.

PHP 4 was EOL some time ago but is still in widespread use. We still see 
occasional postings regarding 7.3 and sometimes even earlier.


The software doesn't suddenly stop working when it hits EOL. It is just 
an expectations-setting statement to end-users that the release is no 
longer likely to receive attention from the core team.


Users are, of course, free to use/self-support the software as they see 
fit. It's open-source, after all.


Cheers,
Steve
(who is in favor of 7.4 EOL despite one remaining 7.4 server in my 
upgrade queue)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] EOL for 7.4?

[Steve Crawford]

Josh Berkus wrote:

...The main reason I'm in favor of this is that we have a lot of users
using 7.4 out of inertia, and they need a message that 7.4 is not
supported to get them to upgrade.
I'm not entirely sure that inertia is the culprit. From what I've seen, 
since 7.4 is a good, stable release, checking/fixing everything required 
for an upgrade (casting, time-calculation changes, administrative 
procedures, perhaps switching from C to UTF8, client-deployment planning 
and so on) combined with risks of the unknown and 24x7 availability 
requirements makes the required expenditure a tough sell - especially in 
a lean and mean economy. I suspect 7.4 will remain in somewhat 
widespread use for quite some time after EOL.


EOL _does_, however, give IT some powerful ammo to use to in persuading 
management to devote the required resources to an upgrade.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Maintenance Policy?

[Steve Crawford]

Tom Lane wrote:

Andrew Dunstan and...@dunslane.net writes:
  
I think we can avoid most of these problems by making a best effort 
policy rather than a hard promise.  But it can be moderately specific 
about what we will make best efforts towards. I agree that anyone who 
wants a hard promise should be getting commercial support.



I don't mind the idea of saying our intention is to support new
releases for about five years, or something equally squishy.
But a list of dates in black and white does not look reasonable,
especially not dates that are four or five years out for versions
that have zero track record.  We have no idea whatsoever what the
future will bring.
  
Would it be reasonable to have the squishy intention coupled with a 
more firm policy of ...EOL will be announced X months in 
advance...Users requiring firm long-term EOL commitments are advised to 
purchase commercial support...


Perhaps the postgresql.org home-page should be modified slightly. 
Instead of Latest Releases (which doesn't even list 7.4 when I just 
looked), it could be something like Current Releases. Then when EOL is 
announced, the release could be suffixed with the EOL date (i.e. 7.4.25 
EOL 2009-12-31 - maybe even with the EOL date in bold and/or red) which 
would link to the EOL announcement or general EOL statement page.


I think that a EOL Statement link to a page with the generic statement 
placed just below the oldest release could be helpful as well.


Cheers,
Steve

Re: [HACKERS] [GENERAL] string_to_array with empty input

[Steve Crawford]

Did I miss the exciting conclusion or did this drift silently off radar?

I seem to recall three options:

1. Leave as is. Arguments: least effort, no backward compatibility 
issues, since array_to_string evaluate both an array with single empty 
string and an array with no elements to an empty string, string_to_array 
on empty strings is ambiguous so we'll call it null. But: means that the 
result of null input and non-null empty-string both result in null 
output, requires everyone to explicitly handle empty strings (with the 
side effect that they really know what the result will be) instead of 
helping the majority of users. Requires: documentation change to 
accurately describe function's behavior.


2. Change function to return an array. Arguments: Distinguishes null 
from non-null input, easier coding for most cases, perhaps a less 
surprising result. But: not backward compatible, requires somewhat 
arbitrary decision on correct return value. Requires: code 
change/testing, documentation updates.


In scenario 2, there were two options:
2a. Return zero-element array.
2b. Return array with single empty-string element.

My impression was that among the change options, 2b had the most 
support (it is the most useful for the use-cases I've encountered so it 
gets my vote). If the consensus is to change the function, it may be too 
late for 8.4. But the documentation could be updated to reflect current 
and planned behavior.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

[Steve Crawford]

Tom Lane wrote:


I'm starting to vacillate again.  It's clear that for the purposes
of string_to_array, an empty input string is fundamentally ambiguous:
it could mean a list of no things, or a list of one empty thing.
  
Agreed. Of the two, a list of one empty thing makes string_to_array 
closer to an inverse of array_to_string.



Or we could stick to the current behavior and say use COALESCE() to
resolve the ambiguity, if you need to.

  
Currently string_to_array(null, ',') yields a null result - 
indistinguishable from string_to_array('',','). Wrapping in coalesce 
does not help distinguish true null input from empty-string input. I'm 
not sure at the moment what other cases exist where non-null input 
generates null output.


If the decision is to leave the behavior unchanged, it at least cries 
out for a documentation patch.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What's going on with pgfoundry?

[Steve Crawford]

Kris Jurka wrote:



On Wed, 26 Nov 2008, Dave Page wrote:



It's the same IP address - but try port 35 for ssh. Marc changed it
(temporarily) due to a vast number of malicious connection attempts.



Why wasn't this change communicated to anyone, not even gforge-admins? 
How temporary is temporary?


Kris Jurka

I can't speak to the administrative and communications aspects, but 
based on my experience, I can recommend communicating to the appropriate 
users and making the change permanent.


I have changed the external ssh port on all machines I administer. The 
result is the complete elimination of the previous hundreds to thousands 
of daily script-kiddie brute-force attempts I used to see.


Obscurity should not be your *only* line of defense, but camouflage 
helps as well. And even if it didn't, it still reduces server-load, 
bandwidth and heaps of logfile cruft.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What's going on with pgfoundry?

[Steve Crawford]

David Fetter wrote:



We should move to a port-knocking
http://dotancohen.com/howto/portknocking.html or other modern
strategy if we're going to move at all.

  
Yeah, but telling my firewall to move port 22 inside to port  
outside took less time than writing this email. Inside the firewall 
plain old ssh continues to work fine and I don't have to deal with 
issues of forwarding additional ports through the firewall, mucking with 
iptables rules, etc.


For my servers, moving outside access to a non-standard port has proven 
100% effective for over a year so additional complexity hasn't been 
warranted.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] What's going on with pgfoundry?

[Steve Crawford]

Joshua D. Drake wrote:

On Wed, 2008-11-26 at 18:06 -0400, Marc G. Fournier wrote:
  


Since were chatting :P. My vote would be to move everything back to port
22 and force key based auth only.
  
How does that work?  Does that kill the script kiddies in their tracks?  I'm 
guessing so, but had never thought to try it ...





Well they can still talk to the port of course but its irrelevant...

  


Not really. My servers don't allow remote root ssh access at all. But 
all the failed script-kiddie attempts really hose the log files to say 
nothing about wasting my bandwidth.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Monitoring postgres

[Steve Crawford]

Kellyton Campos Feitosa - GYN wrote:


Dears,

I need monitor a postgres database, but I don’t know which tool to use.

The tool need perform the below actions

   1. show transactions pendents
   2. show the statistics per session actives
   3. show the statistics per database
   4. show metrics per session and database
   5. show rows locks
   6. show slow activities statements (SELECT, INSERT, UPDATE, DELETE)
   7. to allow set a trace to a specific session, similar the tool
  dbms_system.SET_SQL_TRACE_IN_SESSION include in Oracle Database


This is better asked in the administration (or general) mailing list. I 
don't know of a single magic-bullet tool that does everything you list 
but you might try with the PostgreSQL docs starting with the monitoring 
and logging sections.

http://www.postgresql.org/docs/8.3/interactive/monitoring.html
http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html

Then Google postgresql monitoring tool and see what's available 
including commercial products from EnterpriseDB:
http://www.enterprisedb.com/products/postgres_plus_as.do (dig into the 
docs and see what their monitoring stuff does)

and monitoring tools such as Hyperic that have PostgreSQL plugins:
http://www.hyperic.com/products/managed/postgresql-management.htm

Cheers,
Steve

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Do we really need a 7.4.22 release now?

[Steve Crawford]

Tom Lane wrote:


Yeah.  What this is about is how long the *community* supports 7.4...
  
Perhaps the discussion should be more global (and ultimately save time 
on having this discussion again in the future). Decide on the policy, 
make official and make it obvious. The time I usually hear tossed around 
is 5 years. This is the same support period that Ubuntu uses for the 
long-term-support releases of their server version - the longest support 
period they offer. As a user, 5 years seems a reasonable support period 
for a core infrastructure component.


Whatever time-period is chosen, I would make it obvious in a variety of 
places:


The versioning policy (add something like Major releases are supported 
through minor-release updates for a period of five years following 
initial release. to http://www.postgresql.org/support/versioning).


The FAQ (add an end-of-life FAQ): 
http://www.postgresql.org/docs/faqs.FAQ.html


All release notes: I.e. for 7.4: Release date: 2003-11-17  End-of-life 
date: 2008-11-17, for 7.4.21: Release date: 2008-06-12 End-of-life 
date 2008-11-17


Perhaps even as a comment at the start of the installation sections of 
the manual: It is recommended to use the most recent release... Major 
releases are supported for...


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

OT: Re: [HACKERS] A new take on the foot-gun meme

[Steve Crawford]

Gregory Stark wrote:

Shane Ambler [EMAIL PROTECTED] writes:

  

Robert Treat wrote:



So is that a golf club gun?


Careful what you wish for
http://www.totallyabsurd.com/12gaugegolfclub.htm
  

I reckon they watched Caddyshack (I think that was the one) and thought they
could get the patent before someone actually tried selling them.



Surely a movie counts as published!?

  


No the term is prior art leaving the lawyers to bill $400/hour while 
they argue over whether or not Caddyshack is art. Though the movie 
might have inspired this:

http://www.rodenator.com/
http://video.google.com/videoplay?docid=2386436112453851581
http://www.youtube.com/watch?v=2umEFHeo6mw

Looks fun as long as you don't do this:
http://uk.reuters.com/article/oddlyEnoughNews/idUKN2432304520080326

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] SHA1 on postgres 8.3

[Steve Crawford]

David Fetter wrote:

On Wed, Apr 02, 2008 at 12:27:15PM -0400, Tom Lane wrote:
  

David Fetter [EMAIL PROTECTED] writes:


Just exactly which encryption legislation are we talking about
here?
  
I know there was some fuss about this issue back in the early

1990s, but that was many, many law changes and court cases ago,
world-wide.  It's far from clear to me that there's any reason
other than inertia not to roll the crypto stuff into the core
functionality and have done.
  

This seems a very USA-centric view of the problem.  It's true that
the US export regulations no longer pose much of an issue for us
(but who's to say they might not become tighter again in future?);
the problem is there are lots of places where the laws are still
strict.



Which places, and what laws?
  

http://rechten.uvt.nl/koops/cryptolaw/cls-sum.htm

(Info only - I have not spent time considering the issue at hand thus, 
counter to net tradition, offer no opinion.)


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TRUNCATE TABLE with IDENTITY

[Steve Crawford]

Simon Riggs wrote:

RESTART IDENTITY will reset the SERIAL sequences back to the original
start value.
  

Assuming this feature were to be added

In cases where the same sequence has been used across multiple tables, 
what will be the appropriate response when a user attempts to TRUNCATE 
one of those tables with RESTART IDENTITY?


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] CVS repository rsync

[Steve Crawford]

Magnus Hagander wrote:
 I've set up my laptop to sync down the full cvs repository using rsync
 (remember - windows = no cvsup). This works well, except every now and
 then (not every time, but definitly often enough to bother me) it
 resyncs the entire repository, and not just the files that have had
 commits to them.
 
 Anybody have a clue as to why this is happening, and what I can do about
 it?
 
 //Magnus
 
 
 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?
 
http://www.postgresql.org/docs/faq
 
 

This, perhaps?:

--modify-window
  When comparing two timestamps rsync  treats  the  timestamps  as
  being  equal if they are within the value of modify_window. This
  is normally zero, but you may find it useful to set  this  to  a
  larger  value  in some situations. In particular, when transfer-
  ring to Windows FAT filesystems  which  cannot  represent  times
  with a 1 second resolution --modify-window=1 is useful.

(from rsync man page)

Cheers,
Steve


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

[HACKERS] Feature request (was psql: absolutes and toggles)

[Steve Crawford]

I would like the ability to absolutely set parameters/settings in psql
so that our psql scripts could generate predictable output absent a
known or controllable initial state. Original discussion at bottom of
message.

One alternate and easier approach I've thought of is to simply add
something akin to a \factory-reset meta-command which would return all
settings to the state they would be in immediately after starting psql
with the --no-psqlrc option. This would at least provide one solution to
the problem and might be a handy meta-command even if absolute settings
were added.

If a factory reset meta-command were added I think that \o should be
exempted as it is already an absolute setting that can be predictably
used in scripts and, where output redirection isn't specified in the
script, we shouldn't interfere with the ability to save the output of a
script or scripts as the user desires.

Cheers,
Steve

Peter Eisentraut wrote:
 Steve Crawford wrote:
 We create psql scripts that can be used at various times by various
 users. I have been unable to find how to absolutely set various
options (timing, expanded, etc.) rather than toggle them.

 The --no-psqlrc option provides a partial workaround - as long as
 the user remembers to include it and as long as they are only
 running the one script. But if they forget or if they are already
 running a session there is no telling what settings have been
 toggled by previously run scripts or the users themselves.

 So...have I overlooked an interactive psql option that will let me
 reset all options to factory-defaults or a method of specifying an
 absolute setting to the various options?
 
 Probably not.
 
 If not, do psql users out there feel this is worth a feature request?
 
 I think so.
 


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

Re: [HACKERS] The Contrib Roundup (long)

[Steve Crawford]

On Friday 10 June 2005 10:54 am, Kaare Rasmussen wrote:
  actually I think part of the point of this was to give a command
  line version of the reindex command, like we have for vaccum. If
  that still matters, then it should probably stay.  Actually it
  should probably be converted to C and moved to /src/bin.

 Wouldn't something like

 echo 'REINDEX DATABASE {database};' | psql {database}

 be easier?

But not as easy as:
psql -c reindex database {database} {database}

Add connection options as desired.

Cheers,
Steve


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

Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables

[Steve Crawford]

On Thursday 31 March 2005 12:06 pm, Tom Lane wrote:
 I wrote:
  The light just went on ... system catalog updates don't generate
  statistics reports.  Hence, autovacuum doesn't know any work is
  needed.

 The above claim is too strong --- they do normally generate stats
 updates.  However, in a simple test I observed that
 pg_stat_all_tables.n_tup_del did not seem to increment for the
 deletes that occur when a temp table is dropped during backend
 exit.  (Most likely we aren't flushing out the final stats
 messages...)

 Steve, is your app in the habit of creating lots of temp tables
 that are not dropped explicitly?  That would explain why you are
 getting bit more than other people.

Yes, various processes create in total well over 100 temporary tables 
every hour. None of them are explicitly dropped.

Cheers,
Steve


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

Re: [HACKERS] Bug 1500

[Steve Crawford]

 So this bug actually brings the issue of interval to_char()
 formatting. Opinions?

In digging around I discovered that it appears a decision was made to 
remove to_char(interval) at the 8.1 release but I've been unable to 
find the replacement for this functionality. This alarms me.

Given the messages I've seen regarding to_char(interval), it's clearly 
a function that is used. As an example, in our telephony systems 
there is a column for start_time and for end_time. Billing involves a 
sum(end_time-start_time) for the appropriate project/client/period. 
Naturally, that interval needs to be displayed appropriately.

The most common request I've seen (and it would be very helpful for me 
as well) is the ability to fill the largest displayed time increment 
with all remaining time in the interval.

In other words when the total increment is 7 days, 7 hours, 28 
minutes, 12 seconds the desired output would be 10528 minutes 12 
seconds. Think phone-billing, race times, mission clocks, etc.

So...

1) Is there really a plan to eliminate to_char(interval)?

2) If so, what is the replacement?

3) If there isn't a replacement and it's just scheduled for 
elimination, what harm was to_char(interval) causing to require its 
removal and what's the best way to lobby for its retention and 
improvement?

Cheers,
Steve


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

   http://archives.postgresql.org

Re: [HACKERS] CVS should die (was: Possible make_oidjoins_check ...)

[Steve Crawford]

 This doesn't really answer the question of what tool Postgres might
 change to, but it seems that Subversion is a good tool one should
 consider. And by golly, CVS is bad. Just consider the cons  having
 to forbid renames in all but the most necessary cases  it just
 invites cruft into any project.

Interesting reading:
http://better-scm.berlios.de/comparison/comparison.html
http://zooko.com/revision_control_quick_ref.html

Cheers,
Steve


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

Re: [HACKERS] Time off

[Steve Crawford]

 Its also an unusual replication scheme in that, more often than
 not, the slaves control the masters.

As the slave of a replica with an 86 day 16 hour uptime I've also 
discovered that the new I/O functions take some adjustment as does 
working around the lack of sleep(3).

Cheers,
Steve

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

Re: [HACKERS] Email data type

[Steve Crawford]

On Monday 17 May 2004 8:45 am, Steve Atkins wrote:
 Also, [EMAIL PROTECTED] is a syntactically valid email address, in the
 .13 TLD. It does not deliver to 10.11.12.13, or anywhere else, as
 of today, unless the MTA or local recursive resolver is broken (a
 common case). [EMAIL PROTECTED] is a whole other thing. As is
 [EMAIL PROTECTED]::10.11.12.13] and various other IPv6 variants.

Along those lines [EMAIL PROTECTED] and [EMAIL PROTECTED] are valid but they 
don't necessarily refer to the same mailbox (depends on the mx for 
foo.bar.com).

 Parsing email addresses is a significant part of my day job, and
 email address validation is a lot harder than it looks at first
 sight.

Yes, indeed.

 Don't forget quoting, whitespace, escaping and nesting
 parenthetical comments

The just looking for an @ breaks pretty quickly, say with 
joe@some.dom or a myriad of other variations.

 In some contexts the empty string is a valid email address. In some
 contexts Postmaster is a valid email address.

As are postmaster and pOsTmaSTeR and POSTmaster and they are all the 
same address.

 I'm not entirely convinced that an email address is a simple and
 well-defined enough datatype to handle comprehensively within the
 DB. The validation decisions are complex and vary from application
 to application.

 (I use two text columns - localpart and domainpart, with an index
  on reverse(lower(domainpart)) and leave validation to the
 application, myself).

Indeed. A problem with the email address datatype is that it hinders 
normalization:

Joe User [EMAIL PROTECTED] is valid but a database designer would 
probably prefer columns for name and email, or if the addresses were 
all people, firstname, middlename, lastname, email.

As you mentioned, the email can be broken into localpart and 
domainpart but if the app requires it, the domainpart could be 
further rendered into toplevel (so you could find all the .gov or 
.edu), secondlevel (at least corresponds to a registrant) and 
subdomain(s) as necessary.

Cheers,
Steve


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

Re: [HACKERS] Log rotation

[Steve Crawford]

On Sunday 14 March 2004 1:00 pm, Tom Lane wrote:
...
 So it seems fairly likely that the fsync-by-default business is
 indeed a Linux-ism not shared by other Unixen.

Excerpt from the Postfix 2.0.8 README_FILES/LINUX_README file in case 
it proves interesting:
-
LINUX syslogd uses synchronous writes by default. Because of this,
syslogd can actually use more system resources than Postfix.  To
avoid such madness, disable synchronous mail logfile writes by
editing /etc/syslog.conf and by prepending a - to the logfile name:

mail.*  -/var/log/mail.log

Send a kill -HUP to the syslogd to make the change effective.
-

Cheers,
Steve


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

Re: [HACKERS] Sigh, 7.3.6 rewrap not right

[Steve Crawford]

On Thursday 04 March 2004 7:28 pm, Tom Lane wrote:
 Lamar Owen [EMAIL PROTECTED] writes:
  Please, don't call it 7.3.6.  Streamlining releases is terrible. 
  7.3.7 or 7.3.6.1 or SOMETHING other than 7.3.6, and just let
  7.3.6 be a brown paper bag release (like 6.4.1 was).

 There were no code-change differences in this rewrap, so I see no
 real need to change the version number.

I have to agree with Lamar et. al. The _code_ may not have changed but 
the product did and the version number should reflect that.

This issue was discussed in InfoWorld a couple years back. I don't 
recall reading a single comment from someone who felt this practice 
benefitted them but there were plenty of tales of pain an frustration 
caused by even seemingly small changes between versions.

Perhaps the fourth digit could represent non-code related updates such 
as documentation and packaging fixes.

Cheers,
Steve


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

[HACKERS] psql copy help

[Steve Crawford]

The psql help for copy (version=7.3.2 and several others) appears 
incorrect (or perhaps the command parser is at fault - in any case 
the help doesn't match reality):

steve=# \h copy
Command: COPY
Description: copy data between files and tables
Syntax:
COPY table [ ( column [, ...] ) ]
FROM { 'filename' | stdin }
[ [ WITH ]
  [ BINARY ]
  [ OIDS ]
  [ DELIMITER [ AS ] 'delimiter' ]
  [ NULL [ AS ] 'null string' ] ]
...

I interpret this as meaning that you can optionally specify a 
delimiter, null etc. and if you do then you can optionally include 
the with and the as for readability. While I can omit the as I 
cannot omit the with:

Works with both:
steve=# \copy foo from 'footest' with delimiter as ','
\.

Works with with only:
steve=# \copy foo from 'footest' with delimiter ','
\.

Does not work without with
steve=# \copy foo from 'footest' delimiter ','
\copy: parse error at 'delimiter'
steve=# \copy foo from 'footest' delimiter as ','
\copy: parse error at 'delimiter'

As such it seems that the help should be:
COPY table [ ( column [, ...] ) ]
FROM { 'filename' | stdin }
[ WITH 
  [ BINARY ]
  [ OIDS ]
  [ DELIMITER [ AS ] 'delimiter' ]
  [ NULL [ AS ] 'null string' ] ]
...

Cheers,
Steve


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

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

[HACKERS] pg_conf idea (was Re: [GENERAL] Postgres performance comments from a MySQL user)

[Steve Crawford]

On Wednesday 11 June 2003 2:37 pm, Matthew Nuzum wrote:
 The problem with this is that in troubleshooting there's no frame of
 reference.  Having a stock config file, or stock config file options allows
 a person to write to the list and say, hey, I'm using medium.conf and I
 have x ram...

 The alternative is, hey, see my attached .conf file... which takes a lot
 more effort.

The postfix mail transport agent has a command postconf which allows you to 
read or change entries in the config file. Like postgresql, postfix uses 
certain defaults when there is no corresponding configuration entry.

Running postconf displays all settings while postconf -n command displays 
only non-default configuration settings. The output of postconf -n is 
generally one of the first things requested when someone asks a question. 
(Being a mail server, most questions are ultimately configuration related - 
postgresql questions are more varied.)

You can also change a setting with postconf parameter=value.

Perhaps a similar command would be useful for postgresql. Just please don't 
call it postconf. :)

Note: this would also provide a nice core interface for all sorts of purposes 
like updating configuration entries based on automatic analysis of database 
size/memory/cpu or based on asking the admin questions about the database use 
profile and then using the command (say pg_conf) to update specified options, 
save current settings and restore everything to default for 
testing/troubleshooting then restore the original settings, etc.

Looking through the useful options for postconf, I think a useful base set of 
features for pg_conf would be

pg_conf -D datadir [-n] [-h] [-d] [parameter...]

Output (unless -h is specified) would look like:
foo = 8192
bar = 0
...

where:
-n = only output values that are different than the default

-h = show the value only (useful for extracting values into scripts, eg, 
pg_conf -h foo would return 8192 instead of foo = 8192. Note: I just 
borrowed -h from postconf - choose any logical letter.)

-d = show the default value for the parameter

The optional parameter list limits the display to that/those parameter(s).

I haven't checked the source code, yet, but I suspect that most of the 
necessary code for such a command is already in whatever module reads 
postgresql.conf.

Thoughts?

Cheers,
Steve


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

Re: [HACKERS] Error message style guide

[Steve Crawford]

One thing that would be great from a user's perspective (and which might 
reduce the volume of support questions as well) is to uniquely number all 
errors as in:
Error 1036: the foo could not faz the fleep

The advantages of this include:
Ease of documentation: a manual could containg a section discussing each 
message. Similarly an error number could be used to easily access a web page 
discussing the error in more detail than a simple message allows.

Ease of searching: google searches like postgresql error 1036 tend to yield 
lots of relevant information - I've found that including an error number 
where available in a google search yields far better results that searching 
with text alone.

Pinpointing trouble: unique IDs would mean that anyone looking into a 
specific problem would know exactly which line of code in PostgreSQL sent the 
error.

If one wants to get fancy the numbers could run in series depending on the 
category of error similar to http/smtp/ftp response codes.

Of course this would require appointing a keeper of the error codes who would 
dole them out as required to prevent dups.

Just a thought - now for a pint of Guinness.

Cheers,
Steve




On Friday 14 March 2003 4:43 pm, Peter Eisentraut wrote:
 Some people were mentioning an error message style guide.  Here's a start
 of one that I put together a while ago.  Feel free to consider it.


 Size of message
 ---

 The main part of a message should be at most 72 characters long.  For
 embedded format specifiers (%s, %d, etc.), a reasonable estimate of
 the expected string should be taken into account.  The rest should be
 distributed to the detail and the hint parts.

 RATIONALE: 72 characters is typically considered an appropriate line
 length on terminal-type displays. Consequently, this length is fair to
 psql users and readers of the server log.  Also, longer messages will
 tend to get chatty.


 Newlines, tabs
 --

 A message may not contain a newline or a tab.

 RATIONALE: Messages are not necessarily displayed on terminal-type
 displays.  In GUI displays or browsers these formatting intructions
 are at best ignored.

 QUESTION: I think formatting characters should be avoided in detail
 and hint messages as well, for the same reasons.


 Quotation marks
 ---

 English text should use double quotes when quoting is appropriate.
 Text in other languages should consistently use one kind of quotes
 that is consistent with publishing customs and computer output of
 other programs.

 RATIONALE: The choice of double quotes over single quotes is somewhat
 arbitrary, but tends to be the preferred use.  Do not distinguish the
 kind of quotes depending on the type of object in SQL terms (i.e.,
 strings single quoted, identifiers double quoted).  This is a
 language-internal technical issue that many users aren't even familiar
 with, it won't scale to all quoted terms, it doesn't translate to
 other languages, and it's pretty pointless, too.


 Use of quotes
 -

 Use quotes always to denote files, database objects, and other
 variables of a character-string nature.  Do not use them to mark up
 nonvariable items.

 RATIONALE: Objects can have names that create ambiguity when embedded
 in a message.  Be consistent about denoting where a plugged-in name
 starts and ends.

 NOTE: This format encourages embedding data items into the message in
 grammatical positions instead of the old style 'invalid value: bar'.


 Punctuation
 ---

 Do not end the message with a period.  Do not even think about ending
 a message with an exclamation point.

 RATIONALE: Avoiding punctuation makes it easier for client
 applications to embed the message into a variety of grammatical
 contexts.  Often, messages are not grammatically complete sentences
 anyway.  (And if they're long enough to be more than one sentence,
 split them up.)


 Upper case vs. lower case
 -

 Use lower case for message wording, including the first letter of the
 message.  Use upper case for SQL commands and key words if the message
 refers to the command string.

 RATIONALE: It's easier to make everything look more consistent this
 way, since some messages are complete sentences and some not.


 Grammar
 ---

 Use the active voice.  Use complete sentences when there is an acting
 subject (A could not do B).  Use telegram style without subject if
 the subject would be the program itself; do not use I for the
 program.

 RATIONALE: The program is not human.  Don't pretend otherwise.

 Instead of multiple sentences, consider using semicolons or commas.

 RATIONALE: This avoids peculiar punctuation if you follow the request
 to leave off the final period.


 Present vs past tense
 -

 There is a nontrivial semantic difference between sentences of the
 form

 | could not open file %s

 and

 | cannot open file %s

 The first one means that the attempt to open the file failed.  The
 message 

Re: [HACKERS] location of the configuration files

[Steve Crawford]

On Friday 14 February 2003 6:07 am, Martin Coxall wrote:
 On Thu, 2003-02-13 at 20:28, Steve Crawford wrote:
  I don't see why we can't keep everyone happy and let the users choose the
  setup they want. To wit, make the following, probably simple, changes:
 
  1) Have postgresql default to using /etc/postgresql.conf

 /etc/postgres/postgresql.conf, if we want to be proper FHS-bitches.

  2) Add a setting in postgresql.conf specifying the data directory
  3) Change the meaning of -D to mean use this config file
  4) In the absence of a specified data directory in postgresql.conf, use
  the location of the postgresql.conf file as the data directory

 Shouldn't it in that case default to, say /var/lib/postgres?

Idea 4 was just a way to preserve current behaviour for those who desire. 
Moving postgresql.conf requires adding the data directory info into 
postgresql.conf or specifying it in some other way. If, in the absence of any 
specification in postgresql.conf, postgres just looks in the same directory 
as postgresql.conf then it will be almost identical to the current setup.

Cheers,
Steve

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

Re: [HACKERS] location of the configuration files

[Steve Crawford]

I don't see why we can't keep everyone happy and let the users choose the 
setup they want. To wit, make the following, probably simple, changes:

1) Have postgresql default to using /etc/postgresql.conf
2) Add a setting in postgresql.conf specifying the data directory
3) Change the meaning of -D to mean use this config file
4) In the absence of a specified data directory in postgresql.conf, use the 
location of the postgresql.conf file as the data directory

I see several advantages:

1) Anyone who doesn't want to change doesn't have to - leaving the data 
directory spec out of postgresql.conf and starting with -D will be 
essentially identical to how things are now (except it would be -D 
/foo/bar/postgresql.conf instead of -D /foo/bar/ - even this could be 
overcome with a bit of bailing wire saying if -D specifies a directory, look 
for postgresql.conf in that directory).

2) Postgresql will be more familiar to those who expect or desire configs 
to be in /etc.

3) Adding a postgresql.conf line for data location sets the stage for being 
able to specify directories for all sorts of files (WAL, index, etc.) without 
the need for symlinks.

4) Multiple config files could be more easily managed for 
testing/benchmarking/etc.

Cheers,
Steve


On Wednesday 12 February 2003 10:14 pm, Peter Bierman wrote:
 At 12:31 AM -0500 2/13/03, mlw wrote:
 The idea that a, more or less, arbitrary data location determines
 the database configuration is wrong. It should be obvious to any
 administrator that a configuration file location which controls the
 server is the right way to do it.

 Isn't the database data itself a rather significant portion of the
 'configuration' of the database?

 What do you gain by having the postmaster config and the database
 data live in different locations?

 -pmb

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

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

Re: Changing the default configuration (was Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks)

[Steve Crawford]

A quick-'n'-dirty first step would be more comments in postgresql.conf. Most 
of the lines are commented out which would imply use the default but the 
default is not shown. (I realize this has the difficulty of defaults that 
change depending upon how PostgreSQL was configured/compiled but perhaps 
postgresql.conf could be built by the make process based on the configuration 
options.)

If postgresql.conf were commented with recommendations it would probably be 
all I need though perhaps a recommendation to edit that file should be 
displayed at the conclusion of make install.

Cheers,
Steve


On Tuesday 11 February 2003 8:20 am, Tom Lane wrote:
 Merlin Moncure [EMAIL PROTECTED] writes:
  May I make a suggestion that maybe it is time to start thinking about
  tuning the default config file, IMHO its just a little bit too
  conservative,

 It's a lot too conservative.  I've been thinking for awhile that we
 should adjust the defaults.

 The original motivation for setting shared_buffers = 64 was so that
 Postgres would start out-of-the-box on machines where SHMMAX is 1 meg
 (64 buffers = 1/2 meg, leaving 1/2 meg for our other shared data
 structures).  At one time SHMMAX=1M was a pretty common stock kernel
 setting.  But our other data structures blew past the 1/2 meg mark
 some time ago; at default settings the shmem request is now close to
 1.5 meg.  So people with SHMMAX=1M have already got to twiddle their
 postgresql.conf settings, or preferably learn how to increase SHMMAX.
 That means there is *no* defensible reason anymore for defaulting to
 64 buffers.

 We could retarget to try to stay under SHMMAX=4M, which I think is
 the next boundary that's significant in terms of real-world platforms
 (isn't that the default SHMMAX on some BSDen?).  That would allow us
 350 or so shared_buffers, which is better, but still not really a
 serious choice for production work.

 What I would really like to do is set the default shared_buffers to
 1000.  That would be 8 meg worth of shared buffer space.  Coupled with
 more-realistic settings for FSM size, we'd probably be talking a shared
 memory request approaching 16 meg.  This is not enough RAM to bother
 any modern machine from a performance standpoint, but there are probably
 quite a few platforms out there that would need an increase in their
 stock SHMMAX kernel setting before they'd take it.

 So what this comes down to is making it harder for people to get
 Postgres running for the first time, versus making it more likely that
 they'll see decent performance when they do get it running.

 It's worth noting that increasing SHMMAX is not nearly as painful as
 it was back when these decisions were taken.  Most people have moved
 to platforms where it doesn't even take a kernel rebuild, and we've
 acquired documentation that tells how to do it on all(?) our supported
 platforms.  So I think it might be okay to expect people to do it.

 The alternative approach is to leave the settings where they are, and
 to try to put more emphasis in the documentation on the fact that the
 factory-default settings produce a toy configuration that you *must*
 adjust upward for decent performance.  But we've not had a lot of
 success spreading that word, I think.  With SHMMMAX too small, you
 do at least get a pretty specific error message telling you so.

 Comments?

   regards, tom lane

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

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

http://archives.postgresql.org

Re: [HACKERS] PGP signing releases

[Steve Crawford]

Having just started working with GPG I shouldn't be considered an expert but 
it seems to me that each core developer should create a key and should 
cross-sign each others' keys to form a web of trust to verify the 
authenticity of those signatures. In any case, I think that if 
security-related projects like GnuPG and OpenSSH use the individual method 
then it wouldn't be a bad idea to follow their lead.

One hopes that situations like last week's ousting of one of the core 
FreeBSD developers 
(http://slashdot.org/article.pl?sid=03/02/03/239238mode=threadtid=122tid=156) 
are rare but if such a situation were to arise, a shared project key would be 
Very Bad (tm).

If I understand GPG correctly, one can create a detached signature of a 
document. As such, any or all of the core developers could create and post 
such a signature and a user could verify against as many signatures as 
desired to feel secure that the file is good.

Cheers,
Steve


On Tuesday 04 February 2003 9:15 am, [EMAIL PROTECTED] wrote:
 There are generally two ways to do it: have a project key, or have
 each developer use their own key. The advantage of the first way is
 that each release is signed by the same key, which is clearly
 associated with the project. The disadvantage is control, security,
 and accountablility. The second way pretty much reverses the
 arguments: each key is controlled by one person, but there is no
 obvious mapping between that person and the project. Individual keys
 also have a history associated with them, and are usually already
 integrated into the Web of Trust.

 Many projects use the individual method, including Apache, GnuPG, and
 OpenSSH. Some use the project method, such as sendmail and proftpd.
 Either is okay with me, but some questions need to be answered if
 using a project key:

 Who will actually hold the key? Where will it be physically kept?

 How many people will know the passphrase?

 Who will be responsible for signing the files? Is there a backup person?

 Will it be a signing-only key? What size? Should it expire?

 How is verification of the files before signing accomplished?


 I've got some ideas about most of those, especially the last two. This will
 not be that easy of a process, but on the other hand, new versions do not
 appear very frequently, and it is important to get this right the first
 time.

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

Re: [HACKERS] Linux.conf.au 2003 Report

[Steve Crawford]

What about cases where I only want one or the other? Would a simple method 
exist to limit input to v4 or v6 only?

Also, what are the implications to functions such as network_sub, 
network_cmp, etc. when given mixed v4/v6 inputs as could easily happen if the 
two are freely mixed in the same data type?

Cheers,
Steve

On Wednesday 29 January 2003 10:04 pm, Tom Lane wrote:
 Christopher Kings-Lynne [EMAIL PROTECTED] writes:
  Maybe we should create a new type 'inet6'???

 I'd lean towards allowing the existing inet and cidr types to store both
 v4 and v6 addresses, if at all possible.  Is there a good motivation for
 doing otherwise?

   regards, tom lane

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

 http://archives.postgresql.org

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

http://archives.postgresql.org

Re: [HACKERS] [GENERAL] RC2 Packaged in Preparation for a Wednesday Release ...

[Steve Crawford]

SuSE 7..3 (2.4.10-4GB)

Compiles and passes regression fine:
All 89 tests passed. 

Installing to dev server next.

Cheers,
Steve



On Monday 25 November 2002 8:19 am, you wrote:
 Morning all ...

On Sunday this weekend, we packaged up PostgreSQL v7.3rc2 for testing
 ... this release, if all goes well, will become the Final Release on
 Wednesday, unless anyone comes up with any outstanding issues.

   At this point, we need as many ppl as possible to try and break it, so
 that when we do release, its as solid as we can possibly make it.

   If all goes well, v7.3 will be released by December 1st.

   Downloads are available at all mirrors, or the main site:

   ftp://ftp.postgresql.org/pub/beta

   Bugs should be reported to [EMAIL PROTECTED]

 Thanks ...





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

 http://www.postgresql.org/users-lounge/docs/faq.html


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

http://www.postgresql.org/users-lounge/docs/faq.html