[DOCS] Is timestamptz alias documented?

[Chris]

I have been unable to find where it is mentioned that timestamptz can be
used as shorthand for 'timestamp with time zone'. I discovered I could use
it by trying it out after I saw someone else use it. Is this a new alias, or
has it been around for some time? I am using 9.0, and have not tried it on
earlier versions.

I think it would be helpful to note this somewhere on the Date/Time Types
page.

Chris

Re: [DOCS] Is timestamptz alias documented?

[Chris]

On Tue, Dec 7, 2010 at 11:35 AM, Tom Lane  wrote:
> See table 8-1 here:
> http://www.postgresql.org/docs/9.0/static/datatype.html
>
> We don't emphasize it, since it's a nonstandard Postgres-ism.

Thanks. I guess I understand why you don't want to emphasize a
non-standard Postgres feature. But why doesn't that page show up when
I do a search?

http://search.postgresql.org/search?u=/docs/9.0/static/&q=timestamptz

Chris

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

[DOCS] Soundex

[chris]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.1/static/fuzzystrmatch.html
Description:

soundex/metaphone page:
https://www.postgresql.org/docs/current/static/fuzzystrmatch.html

It would be good to include that you need to install the fuzzyystrmatch
extension

and perhaps  also reference the pg_trgm extension as an alternative for
matching


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

Re: [DOCS] Do we need "Diagnostics" sections of SQL command reference pages?

[Chris M]

"Tom Lane" <[EMAIL PROTECTED]> Write news:[EMAIL PROTECTED]
> "Dan Langille" <[EMAIL PROTECTED]> writes:
> > On 26 Aug 2003 at 9:14, Tom Lane wrote:
> >> http://developer.postgresql.org/docs/postgres/sql-createdatabase.html
>
> > The value I see in those message is it gives the reader more
> > information about what can go wrong.  The above example shows that
> > you cannot use "create database" within a transaction.
>
> Sure, but that should have been stated in the command description.
>
> > Also, the information under "ERROR: Could not initialize database
> > directory." is pretty good.
>
> I chose this example deliberately, because it's one of very few pages
> where there's actually nontrivial content in the Diagnostics section.
> "could not initialize database directory" seems to me the only one
> of these messages that requires more info (the "could not create
> database directory" message now includes the kernel error code, so
> it's sufficiently improved IMHO).  What I'm inclined to do about it
> is add a DETAIL field showing the exact "cp" command that failed, and
> perhaps a HINT suggesting that people look in the postmaster's stderr
> log to see cp's complaint.  Not sure how to translate that to Windows,

Windows port may use "copy", "xcopy" to copy files.  In Windows, "copy"
is not so powerful as "xcopy". But I don't think using OS specific shell
commands
a good idea. If environment variable PATH is not set correctly, using these
commands
may cause a fail.

> but under Unix it should be sufficient no?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
>



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

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

Re: [DOCS] Changing the sequence owner

[Chris M]

I think it is a good idea.
Providing an example is better.

""Dan Langille"" <[EMAIL PROTECTED]>
news:[EMAIL PROTECTED]
> Should we make some reference to ALTER TABLE on the the sequence
> page?  A sequence owner is changed via:
>
>   alter table forums_auth_id_seq owner to phorum;
>
> That's not intuitive unless you know the innards.  We need to point
> the users there.
>
> There are references to sequences within the ALTER TABLE
> documentation ("This form changes the owner of the table, index,
> sequence or view to the specified user.") but you have to know about
> it in the first place.
> -- 
> Dan Langille : http://www.langille.org/
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>



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

Re: [DOCS] [HACKERS] Tutorial

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> David Fetter <[EMAIL PROTECTED]> writes:
>> On Fri, Jul 23, 2004 at 04:30:40PM -0400, Tom Lane wrote:
>>> If we're going to remove from the tutorial every feature for which
>>> any aspect is deemed by someone to be broken, the tutorial is liable
>>> to become quite short.
>
>> Are there other pieces that are broken?
>
> Between the locale behavior and the trailing-spaces behavior, one could
> make the case that the entire set of textual datatypes are broken.
> Other examples will occur to your thought if you follow pgsql-bugs.
>
> My point here is that one man's unusably broken feature may be another
> man's quite useful feature.  Postgres is a work in progress, and
> probably always will be.  I don't object to pointing out shortcomings,
> but removing all mention of a feature because it has some shortcomings
> seems not the best way.

Ah, but suggesting that people devote time to adding documentation for
less controversial features, so that we actually _do_ see some more
documentation, seems a good thing :-).
-- 
output = reverse("moc.enworbbc" "@" "enworbbc")
http://cbbrowne.com/info/multiplexor.html
Why isn't phonetic spelled the way it sounds?

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

[DOCS] Changing Defaults - NAMELEN...

[Chris Browne]

We're running into an issue with Slony-I documentation; I thought
there might be some wisdom possibly available here as the tools are
the same...

The Situation:

  We're using Rod Taylor's "autodoc" package to generate documentation
  for the database schema and the pl/pgsql functions

The Problem:

  Some of the object names (ID attributes) get pretty long,
  particularly when the function name was pretty long and there are a
  whole bunch of variables.

  On some systems, the DocBook preamble sets NAMELEN to 44.

  With the result that OpenJade refuses to process the resulting
  document because there are attributes longer than that size.

We could get the ./configure script to rummage around for DocBook 4.2
configuration, and yell at the user if it finds NAMELEN = 44.

But we'd sure like to have some better option, such as having a
replacement .dcl file that we could use to overlay the "distribution"
one.

This evidently plagues people running RHAT and SUSE distributions.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"


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

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

Re: [DOCS] [HACKERS] Replication documentation addition

[Chris Browne]

[EMAIL PROTECTED] (Bruce Momjian) writes:
> With no new additions submitted today, I have moved my text into our
> SGML documentation:
>
>   http://momjian.us/main/writings/pgsql/sgml/failover.html
>
> Please let me know what additional changes are needed.

It's looking a lot improved to me...

There are still numerous places where it needs s/Slony/Slony-I/g
because there is more than one thing out there called "Slony," only
one of which is the single-master-to-multiple-subscribers-asynchronous
replication system...



"This can be complex to set up because functions like random() and
CURRENT_TIMESTAMP will have different values on different servers, and
sequences should be consistent across servers."

It doesn't make sense to call this "complex to set up."  This problem
isn't about complexity of setup; it is about whether updates are
processed identically on different hosts.  

Perhaps better:

"Query broadcasting can break down such that servers fall out of sync
if the queries have nondeterministic behavior.  For instance,
functions like random(), CURRENT_TIMESTAMP, and
nextval('some_sequence') will take on different values on different
servers.  Care must be taken at the application level to make sure
that queries are all fully deterministic and that they either COMMIT
or ABORT on all servers."


"24.6. Clustering For Load Balancing

In clustering, each server can accept write requests, and these write
requests are broadcast from the original server to all other servers
before each transaction commits. Under heavy load, this can cause
excessive locking and performance degradation. It is implemented by
Oracle in their RAC product. PostgreSQL does not offer this type of
load balancing, though PostgreSQL two-phase commit can be used to
implement this in application code or middleware."

Something doesn't feel entirely right here...

How about...

"24.6. Multimaster Replication For Load Balancing

In this scenario, each server can accept write requests, which are
broadcast from the original server to all other servers before each
transaction commits in order to ensure consistency.  Unfortunately,
under heavy load, the cost of distributing locks across servers can
lead to substantial performance degradation. It is implemented by
Oracle in their RAC product. PostgreSQL does not offer this type of
load balancing, though PostgreSQL two-phase commit using  and  may be used to implement this in
application code or middleware.

The communications costs involved in distributing locks and writes
have the result that write operations are considerably more expensive
than they would be on a single server.  In general, the cost of
distributed locking means that this clustering approach is only usable
across a cluster of servers at a local site.  

There will only be a performance "win" if the cluster mostly processes
read-only traffic that the cluster can distribute across a larger
number of database servers.  Write performance generally degrades a
fair bit as compared to using a single database server.  Reliability
should be enhanced since the cluster should be able to continue work
even if some of the members of the cluster should fail."



"24.7. Clustering For Parallel Query Execution

This allows multiple servers to work on a single query. One possible
way this could work is for the data to be split among servers and for
each server to execute its part of the query and results sent to a
central server to be combined and returned to the user. There
currently is no PostgreSQL open source solution for this."

This seems a bit thin.

"24.7. Clustering For Parallel Query Execution

This allows multiple servers to work concurrently on a single query,
analagous to the way RAID permits multiple disk drives to respond
concurrently to disk I/O requests.

One way this could work is for the data to be partitioned across the
servers, where each server executes its part of the query, submitting
results to a central server to be combined and returned to the user.
There currently is no PostgreSQL open source solution for this."
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/advocacy.html
Why do we put suits in a garment bag, and put garments in a suitcase? 

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

   http://archives.postgresql.org

Re: [DOCS] Documentation and explanatory diagrams

[Chris Browne]

t...@sss.pgh.pa.us (Tom Lane) writes:
> Alvaro Herrera  writes:
>> Excerpts from Peter Eisentraut's message of jue jul 01 21:52:00 -0400 2010:
>>> Is there something that makes installing dia more challenging than the
>>> other documentation build tools?
>
>> Err, I dunno -- it's just an apt-get away for me, but what will Tom say
>> when it doesn't work on his ancient HP-UX 10.20 system?
>
> I don't try to build the docs on that box anyway --- it does have
> openjade but such an old version that they don't build.  In practice
> building the docs already takes much more modern infrastructure than
> compiling the source code; and besides there are many fewer people
> who care about doing it.
>
> A more interesting question is whether Marc can install a working
> version of dia on whatever he uses to wrap the tarballs.

Good news...  It's not terribly difficult to use command line usage to
get dia to export .png files.

{wrox}  dia --export=transport.png Transports.dia
Transports.dia --> transport.png
{wrox}  file Transports.dia transport.png
Transports.dia: gzip compressed data, from Unix
transport.png:  PNG image data, 753 x 774, 8-bit/color RGBA, non-interlaced

So the makefile rule is pretty much:
   %.png : %.dia; dia --export=$@ $<

I'll observe that while dia claims to be able to export in JPEG form,
it doesn't necessarily work:

{wrox} dia --export=transport.jpg Transports.dia

** (dia:946): CRITICAL **: dia error: do not know how to export into 
transport.jpg

If we have a preference for JPEG, then that's presumably an
ImageMagick run away...
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/languages.html
HEADLINE: Suicidal twin kills sister by mistake! 

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

Re: [DOCS] Documentation and explanatory diagrams

[Chris Browne]

br...@momjian.us (Bruce Momjian) writes:
> Marc G. Fournier wrote:
>> On Fri, 2 Jul 2010, Tom Lane wrote:
>> 
>> > Alvaro Herrera  writes:
>> >> Excerpts from Peter Eisentraut's message of jue jul 01 21:52:00 -0400 
>> >> 2010:
>> >>> Is there something that makes installing dia more challenging than the
>> >>> other documentation build tools?
>> >
>> >> Err, I dunno -- it's just an apt-get away for me, but what will Tom say
>> >> when it doesn't work on his ancient HP-UX 10.20 system?
>> >
>> > I don't try to build the docs on that box anyway --- it does have
>> > openjade but such an old version that they don't build.  In practice
>> > building the docs already takes much more modern infrastructure than
>> > compiling the source code; and besides there are many fewer people
>> > who care about doing it.
>> >
>> > A more interesting question is whether Marc can install a working
>> > version of dia on whatever he uses to wrap the tarballs.
>> 
>> that shouldn't be an issue ... Peter runs an update every 3 hours on that 
>> machine right now as it is ...
>
> OK, everyone seems to like requiring dia.  I wasn't sure how popular dia
> was.  One hack solution to allow builds without dia would be to create a
> Makefile rule that creates empty PNG files to match the dia files.
>
> Can someone provide the command-line to build the PNG files from the DIA
> files?

Here's a pretty suitable Makefile rule:
%.png : %.dia; dia --export=$@ $<
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/languages.html
HEADLINE: Suicidal twin kills sister by mistake! 

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

Re: [DOCS] Large SGML Cleanup

[Chris Browne]

pete...@gmx.net (Peter Eisentraut) writes:
> In general, I think the more efficient way to address this overall
> problem is to run the resulting HTML through tidy and be done with it.

+1.  I use similar toolchain for my own web site, and one of the steps
is to run tidy on the output, which rectifies a number of issues.  (I
haven't looked at just what they are in some years now :-))
-- 
http://www3.sympatico.ca/cbbrowne/slony.html
No lusers were harmed in  the creation of this  usenet article.  AND I
WANT TO KNOW WHY NOT!
-- glm...@twirl.mcc.ac.uk in alt.sysadmin.recovery

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

[DOCS] Documentation Navigation Feedback

[Chris Meller]

I jumped into #postgresql earlier to ask a couple of questions and we ended up 
talking about the documentation. agliodbs wanted me to mention the problems I 
ran into trying to find what I was looking for on the mailing list, so here we 
go.

I was looking at the documentation (which, btw, has always been of a very high 
quality, so props for that!) and trying to find out about character sets and 
collations. I didn't have much luck looking at the main TOC, which isn't a big 
deal or terribly unexpected, so I did a search for 'collation'. The second 
result is the CREATE DATABASE reference page, which is one of the main pages I 
was looking for, so that's great.

Once I'm there, though, I'm pretty much lost. I've got Prev and Next links (and 
Fast Backward and Fast Forward, which didn't seem to do anything different), 
but no indication of where I am or how to get somewhere else.

For a specific example: After reading the few pieces I needed to know about for 
CREATE DATABASE, I wanted to move on to CREATE TABLE. It looks like I'm in a 
function reference section, so I assume there must be a main TOC page listing 
them all, but I don't see a link to that anywhere. There's also no indication 
which chapter and section I'm in, so I can't go back to the main TOC and 
navigate down to it to find the chapter TOC. I ended up hitting 'Next' a dozen 
times to find CREATE TABLE in the alphabetical list of functions.

When I mentioned this out on IRC, peerce did point out that there's an 'Up' 
link... at the bottom. I had no idea it was there. I'd found the parameter I 
was looking for and had no reason to keep reading the rest of the lengthy 
explanation of other parameters and caveats to using them, so there was no 
reason for me to keep scrolling and I didn't expect the navigation link I was 
looking for to be at the bottom.

Once I was looking at the navigation at the bottom, it seemed like it should be 
the navigation at the top of the page instead. There's an 'up' link and the 
Prev and Next links include the title of the pages you'd be moving to, which is 
actually nice to know.

On other pages I saw that the chapter was shown under 'PostgreSQL x.y 
Documentation' in the navigation at the top, so I don't know why there wasn't a 
similar title on the function page.

Expanding the breadcrumbs at the top, which only show that you're in the 
PostgreSQL x.y documentation, to include the location in the documentation 
would pretty much eliminate my problem... So would using the save left-column 
navigation bar all the other pages seem to use.

Anyway, there's my feedback. Great documentation, but confusing navigation 
makes it tough to use. Carry on... :)

Thanks!

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

Re: [DOCS] Change to documentation headers

[Chris Meller]

On Feb 4, 2011, at 4:23 PM, Bruce Momjian wrote:

> I do like the chapter title there.  
> 
> Looking at "Home", we actually have two of them.  The "Home" at the top
> left of the page links to the PG homepage, while the "Home" at the
> bottom goes to the top of the 9.0 documentation.  That seems odd.  Maybe
> we need to remove the "Home" at the bottom, or rename it.
> 
> You could get away with changing "Fast Backward" to "Up" and removing
> "Fast Forward".

I like the title and chapter reference at the top. The "PostgreSQL x.y.z 
Documentation" title serves the same purpose as 'Home' at the bottom, so it 
should be fine as-is. Making the chapter a link to the same destination as 'Up' 
would make sense to me... You want to go up to the chapter TOC and that's what 
I would expect to get if I clicked on a chapter link (just as if I clicked on 
it in the main TOC).
-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

[DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

Hi all;

I would like to contribute a "What is an Object Relational database?"
section to the documentation for 9.3.  Where is the best place to start
tools and community-process-wise?

My thinking is that since people are often confused by this label, it would
be worth describing what it means, and describing in brief detail
object-relational features in PostgreSQL.

My thinking is to cover the following features briefly:

 * Table inheritance
 * Type Extensibility
 * Tuples as Types, casting tuples to various other types.

I am thinking of skipping over things that may be seen as misfeatures, such
as class.function syntax although this could be useful in the case of
simulating calculated fields.  What do people think there?  Is this a
feature or a misfeature?

Best Wishes,
Chris Travers

Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

On Mon, Aug 13, 2012 at 7:41 AM, Tom Lane  wrote:

> Chris Travers  writes:
> > I would like to contribute a "What is an Object Relational database?"
> > section to the documentation for 9.3.  Where is the best place to start
> > tools and community-process-wise?
>
> > My thinking is that since people are often confused by this label, it
> would
> > be worth describing what it means, and describing in brief detail
> > object-relational features in PostgreSQL.
>
> I think there's a discussion that has to happen before that one, which
> is whether we should continue pushing that term for Postgres.  It was
> originally applied by the Berkeley guys, well over twenty years ago, to
> code that didn't even speak the same language as now (PostQUEL vs SQL).
> So it's fair to ask whether the vision of the project is still the same
> as then.  Simon for one thinks differently:
>
> http://database-explorer.blogspot.com/2012/08/postgresql-multi-model-database-server.html


Agreed, and actually I came here after discussing this on -advocacy, and I
recognize that there is still some controversy but everyone seems to agree
that the way the term is currently used is confusing, and PostgreSQL
doesn't really resemble the wikipedia article on Object-Relational
databases.

However, it is hard to have a discussion regarding how to position
PostgreSQL if we don't have a bunch of good alternatives, so I think it
would still be worth offering even if the community ultimately decides to
move a different direction.  And of course these are not mutually exclusive
either so if nothing else we have the ability of community members to
position the database in other ways.

>
>
> > My thinking is to cover the following features briefly:
>
> >  * Table inheritance
> >  * Type Extensibility
> >  * Tuples as Types, casting tuples to various other types.
>
> I think PG's type extensibility features come out of the
> abstract-data-type culture more than than the object culture.



That's probably worth noting.


>  In
> particular, PG data types generally don't have any notion of "IsA"
> subclass relationships, though the rowtypes of inherited tables do have
> that.


So I noticed.  You can still do some sorts of inheritance, just like you
can do object-oriented programming in C


> (Well, I guess you could claim that a domain IsA subclass of its
> base type, but SQL's domain feature is so impoverished that any object
> hacker would laugh at you.)
>
> So really the argument for calling PG object-relational comes down to
> table inheritance and the IsA relationship between tuples of inherited
> tables.  Which is something I think few people even use anymore ...
> it definitely doesn't seem like a key selling point.
>

I was looking at it differently, namely that there are a bunch of features
that you can use together to build O-R systems.  The complex types may not
support inheritance, but with casts you can get some limited polymorphism.
 Moreover the fact that relations are classes means that you can create
casts of tuples to other types.  For example:

create table foo (
   bar text,
   baz int
); -- simple union type

insert into foo (bar, baz) values ('test', '1');

create function foo_to_int (foo) returns int as
$$ select $1.baz $$ language sql;

create cast (foo as int) with function foo_to_int(foo) as implicit;

 select foo + 1 as value from foo;

 value
---
 2
(1 row)

This is a trivial example and I would probably include it only by
description, but the point is that the combination of casts, functions, and
tables as classes allows you to create some degree of polymorphism.  For
example we could take an employee table and add a name function that
concatenates the first and last name together according to some logic.  We
could then index the output of that function for full text searching.

While you can't do inheritance easily with complex types, these can still
be used to create abstract interfaces and the use of explicit casts might
give you something like it though you'd have a fair bit of work to
implement such a system.

>
> > I am thinking of skipping over things that may be seen as misfeatures,
> such
> > as class.function syntax although this could be useful in the case of
> > simulating calculated fields.
>
> Agreed, that's not a major feature; it's just a notational detail that
> people have got varying opinions about.


Heck, I have varying opinions about it and my opinion on this feature is
rather fluid at any given poitn.  However, I am thinking that maybe
mentioning it up front would mean fewer people get taken by surprise by it.

Best Wishes,
Chris Travers

Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

As a note here, I think one of the fundamental difficulties in figuring out
how to position PostgreSQL (whether using Simon's multi-model idea or
Object-Relational, something else entirely, or some combination) is that
PostgreSQL is an extraordinarily competent and full-featured database
management system.  I have a very rough draft of how I'd explain it I will
send here for some feedback in terms of general message and accuracy before
I look at adapting it as a patch against the docs.

However, while I was going through this and asking "how would I build
something utilizing object-oriented approaches in PostgreSQL?" I realized
how few of the features of this sort I was currently using.  I have been
using PostgreSQL since 1999, and been seriously been trying to use advanced
features for six, and I realized I have barely begun to scratch the
surface.  It's really refreshing to look at this and realize that even
after 12-13 years of becoming familiar with a piece of software, a little
exercise like this provides all sorts of features that would simplify your
life.

The fact is that what PostgreSQL really is, inside the box, is a
transactional development environment where operations occur in a
relational-native way and this is largely how I am approaching it.
 Object-relational in terms of PostgreSQL seems to mean "relational along
with a bunch of tools useful for building object interfaces."  I think a
lot of the multi-model features that Simon talks about can be understood in
these terms as well.  If I was going to coin a term to call this, I would
call it a "Transactional/relational development environment."  Just as you
can do object-oriented programming in C, PostgreSQL lets you do this in SQL.

Also in my tests, I found that inherited relations do not inherit casts.
 Is this intentional?  Is there a reason I should be putting into the
documentation? Or is it just a gotcha that should be listed as a caveat?

Best Wishes,
Chris Travers

Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

So here is a very rough draft.  I would be interested in feedback as to
inaccuracies or omissions.  I would like to get the technical side right
before going into an editorial phase.

Any feedback on the technical side?

Best Wishes,
Chris Travers

How is PostgreSQL "Object-Relational?"

The term Object-Relational has been applied to databases which attempt to
bridge the relational and object-oriented worlds with varying degrees of
success.  Bridging this gap is typically seen as desirable because
object-oriented and relational models are very different paradigms and
programmers often do not want to switch between them.  There are, however,
fundamental differences that make this a very hard thing to do well.  The
best way to think of PostgreSQL in this way is as a relational database
management system with some object-oriented features.

PostgreSQL is a development platform in a box.  It supports stored
procedures written in entirely procedural languages like PL/PGSQL or Perl
without loaded modules, and more object-oriented languages like Python or
Java, often through third party modules.  To be sure you can't write a
graphical interface inside PostgreSQL, and it would not be a good idea to
write additional network servers, such as web servers, directly inside the
database.  However the environment allows you to create sophisticated
interfaces for managing and transforming your data. Because it is a
platform in a box the various components need to be understood as different
and yet interoperable.  In fact the primary concerns of object-oriented
programming are all supported by PostgreSQL, but this is done in a way that
is almost, but not quite, entirely unlike traditional object oriented
programming.  For this reason the "object-relational" label tends to be a
frequent source of confusion.

Data storage in PostgreSQL is entirely relational, although this can be
degraded using types which are not atomic, such as arrays, XML, JSON, and
hstore.  Before delving into object-oriented approaches, it is important to
master the relational model of databases.  For the novice, this section is
therefore entirely informational.  For the advanced developer, however, it
is hoped that it will prove inspirational.

In object-oriented terms, very relation is a class, but not every class is
a relation.  Operations are performed on sets of objects (an object being a
row), and new row structures can be created ad-hoc.  PostgreSQL is,
however, a strictly typed environment and so in many cases, polymorphism
requires some work.

Data Abstraction and Encapsulation in PostgreSQL

The relational model itself provides some tools for data abstraction and
encapsulation, and these features are taken to quite some length in
PostgreSQL.  Taken together these are very powerful tools and allow for
things like calculated fields to be simulated in relations and even indexed
for high performance.

Views are the primary tool here.  With views, you can create an API for
your data which is abstracted from the physical storage.  Using the rules
system, you can redirect inserts, updates, and deletes from the view into
underlying relations, preferably using user defined functions.  Being
relations, views are also classes.

A second important tool here is the ability to define what appear to be
calculated fields using stored procedures.  If I create a table called
"employee" with three fields (first_name, middle_name, last_name) among
others, and create a function called "name" which accepts a single employee
argument and concatenates these together as "last_name, first_name
middle_name" then if I submit a query which says:

select e.name from employee e;

it will transform this into:

select name(e) from employee e;

This gives you a way to do calculated fields in PostgreSQL without
resorting to views. Note that these can be done on views as well because
views are relations.  These are not real fields though.  Without the
relation reference, it will not do the transformation (so SELECT name from
employee will not have the same effect).

Messaging and Class API's in PostgreSQL

A relation is a class.  The class is accessed using SQL which defines a new
data structure in its output.  This data structure unless defined elsewhere
in a relation or a complex type cannot have methods attached to it and
therefore can not be used with the class.method syntax described above.
 There are exceptions to this rule, of course, but they are beyond the
scope of this introduction.  In general it is safest to assume that the
output of one query, particularly one with named output fields, cannot
safely be used as the input to another.

A second messaging aparatus in PostgreSQL is the LISTEN/NOTIFY framework
which can be used along with triggers to issue notifications to other
processes when a transaction commits.  This approach allows you to create
queue tables, use triggers to move data into these t

Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

On Fri, Aug 17, 2012 at 1:03 PM, Peter Eisentraut  wrote:

> On 8/15/12 5:33 AM, Chris Travers wrote:
>
>> So here is a very rough draft.  I would be interested in feedback as to
>> inaccuracies or omissions.  I would like to get the technical side right
>> before going into an editorial phase.
>>
>> Any feedback on the technical side?
>>
>
> [citation needed]
>
> Seriously, if we are trying to justify our use of seemingly standard
> academic terms, we should have some references to where those are defined
> or at least discussed.  Otherwise we are just begging the question:
> PostgreSQL is object-relational because we say so.
>

Good point.

I found two interesting resources quickly which seem on point:

 http://infolab.usc.edu/csci585/Spring2010/den_ar/ordb.pdf which appears to
be chapter 1 of
http://www.amazon.com/Object-Relational-Database-Development-Plumbers-CD-ROM/dp/0130194603

and

http://db.cs.berkeley.edu/papers/Informix/www.informix.com/informix/corpinfo/zines/whitpprs/illuswp/wave.htm

But this doesn't really get us beyond the "because we say so" given the
connection between Informix and PostgreSQL.

It really looks to me like Postges was given the name Object-Relational by
Stonebreaker as a way of saying "here's what I am trying to play around
with" and the databases which describe themselves in these terms seem
either inspired by or forks of Postgres ;-).

Best Wishes,
Chris Travers

Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

On Sat, Aug 18, 2012 at 12:12 PM, Jeff Davis  wrote:

> On Fri, 2012-08-17 at 16:03 -0400, Peter Eisentraut wrote:
> > On 8/15/12 5:33 AM, Chris Travers wrote:
> > > So here is a very rough draft.  I would be interested in feedback as to
> > > inaccuracies or omissions.  I would like to get the technical side
> right
> > > before going into an editorial phase.
> > >
> > > Any feedback on the technical side?
> >
> > [citation needed]
> >
> > Seriously, if we are trying to justify our use of seemingly standard
> > academic terms, we should have some references to where those are
> > defined or at least discussed.  Otherwise we are just begging the
> > question: PostgreSQL is object-relational because we say so.
>
> I feel like the bar is becoming pretty high for this document. It must:
>
> 1. Settle on an accepted criteria for ORDBMS
>

Probably Mike Stonebreaker's paper can be referred to here.  Also it looks
like Oracle used to have a document describing "object-relational" features
in Oracle 10.  Reading through other people's views, I think Oracle might
actually be ahead of us here, but... The problem here is relatively complex
and I am afraid if I go and re-iterate everything I will end up with
another book >:-D

Not that this would be a bad thing.  I did find Oracle's somewhat short
book (of 200 pages) on the subject at
http://docs.oracle.com/cd/B19306_01/appdev.102/b14260.pdf

However if I am doing a book by myself I am either going to publish it or
release it myself.  A document of that scope is a little wider-range than I
would like to just hand off to the community.

I think it will be worth pointing out that Oracle is an ORDBMS as well and
is really the major non-Pg-descended ORDBMS I can find on the market today.


> 2. Describe how postgres meets that criteria in a way that's:
> a. compelling to users
> b. connects with OOP so the users don't feel like it's a
>bait-and-switch or get confused by starting with the
>wrong expectation
>
> I feel like making #1 compatible with 2(a) requires some creativity; and
> #1 might be incompatible with 2(b) entirely.
>

The more I work with this and am trying to figure out how to apply these in
my own work the more I am convinced that this does connect with OOP just,
as I said, in a way that is almost but not entirely unlike normal OOP.

 The way I would describe it in simple terms is that a standard RDBMS
operates on sets of tuples.   An ORDBMS operates on sets of objects.  Those
objects may have methods, may be polymorphic, and may be encapsulated
behind interfaces.  As Stonebreaker said in his paper, this is a marriage
between the set-oriented relational database and the primitives of object
oriented programming.  Consequently the way to look at it is that you have
a relational database with object oriented features which makes this sort
of operation possible (and that is, as best as I can see, how Oracle
actually positions their product as well).

But more to the point, what do people think would be a valuable role for
this document?  I was thinking initially of a *brief* description of what
was meant so that people didn't get too confused.  Maybe it would be better
to save the brief description for later and write a longer document first
that could be incorporated into the brief document by reference?  Maybe a
book entitled "Object-Relational Programming in PostgreSQL" since this is
something I have started to delve deeply into for LedgerSMB.  Maybe by that
point we can figure out whether we are pushing Object-Relational features
as a subset of a multi-model approach or vice versa.  Indeed ontologically
speaking, I am not sure what the difference between multi-model and
object-relational is since Simon seems to think that object-relational is a
subset of multi-model and I think multi-model is a feature of
object-relational ;-).  This being said, of course there may be marketing
reasons to push one or the other as a primary term.

Best wishes,
Chris Travers

Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?

[Chris Travers]

Given the (generally helpful) feedback here what I have decided to do for
now is to run a blog series developing the use cases for O-R functionality
in PostgreSQL, and return after that with a new draft, possibly distilled
from that.

I will post again, perhaps, in a couple months.

Best Wishes,
Chris Travers

[DOCS] Thoughts on inheritance docs

[Chris Travers]

A few observations here.  Want to get feedback before considering
proposing a documentation update here.

The first is that the only example given for table inheritance is a
set/subset model, and the example is problematic for the reason that
the caveats section discusses.  Consequently it isn't immediately
apparent how table inheritance (and in particular multiple inheritance
which, unless I have missed something, seems to be unique to
PostgreSQL).

In general, I see table inheritance as useful for a large number of
tasks including:

  * Set/subset modelling (here borrowing techniques from table
partitioning is necessary-- the full set is partitioned according to
interesting subsets, but each subset may extend associated info)

  * Enforcement of consistent semantics of join syntax, including
functions that would allow you to automatically traverse joins.
Consider:  SELECT (bc.inventory_item).sku from barcodes bc where the
inventory_item method is inherited from inventory_item_ref.  Since
inventory_item_ref would add a column called inventory_item_id to the
table, we'd also know join syntax would generally be consistent.

  * Derivative information modelling of sets of columns which are
insufficient for relational modelling by themselves
 (perhaps because they lack necessary join conditions)

To my knowledge, the latter two are PostgreSQL-specific.  They make
very little sense unless multiple inheritance is permitted, and every
other ORDBMS I have looked at (including Informix, DB2, and Oracle)
has only supported single inheritance.  Restricting to single
inheritance makes sense if you are specializing on set/subset
modelling but multiple inheritance is a tremendously useful tool that
may be well beyond cutting edge.  it might be worth showing this off
in the documentation with real, working examples.

So my questions:

1)  Would it be worth coming up with more useful examples for table inheritance?
2)  Would it be worth adding mention of uses of multiple table inheritance?
3)  Would it be worth discussing how to position the limitations of
table inheritance?  I personally recognize the difficulties in
documenting these but I can't help thinking that a simple chart of
"this is/is not inherited" would be very useful as well.

What do people think?

Best Wishes,
Chris Travers


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

Re: [DOCS] Thoughts on inheritance docs

[Chris Travers]

On Thu, Aug 23, 2012 at 7:09 AM, Tom Lane  wrote:
> Chris Travers  writes:
>> 1)  Would it be worth coming up with more useful examples for table 
>> inheritance?
>> 2)  Would it be worth adding mention of uses of multiple table inheritance?
>> 3)  Would it be worth discussing how to position the limitations of
>> table inheritance?  I personally recognize the difficulties in
>> documenting these but I can't help thinking that a simple chart of
>> "this is/is not inherited" would be very useful as well.
>
> All of those sound sane to me at this level of detail, and in particular
> I agree that the docs are short on examples of multiple inheritance.
>
> The arguments will probably start once you get more concrete ...

I am sure.

In the early prototyping stage, then, maybe it is worth sending out a
brief example, asking for technical feedback before figuring out how
best to work it in.

This example demonstrates re-usable interfaces, the need to declare
foreign keys on child tables, and the ability to add functions to a
sets of tables which use these components.  Multiple inheritance then
gives us an ability to create re-usable interfaces for our data which
can be re-used throughout our database without creating interfaces
which particularly problematic for the use of most relational tools.

If the example looks technically solid then I will look at writing it
up in greater detail.

Suppose we have a country table:

CREATE TABLE country (
id serial not null unique,
name text primary key,
short_name varchar(2) not null unique
);

We may want to create a standardized join interface for this table:

CREATE TABLE country_ref (
country_id int
);

This table is relatively uninteresting itself and is in fact we will
never directly query it.  However it provides an interface for
defining foreign keys in other tables, and as we will show we can make
this simple interface very semantically rich if we want.

Suppose we also have a table for defining an interface for additional
notes fields on tables:

CREATE TABLE note_fields (
note_subject text,
note_content text
);

Typically we may want these to be nullable if we don't know how these
will be used down the inheritance tree.  If we want to we can create
inherited subtypes which enforce not null constraints on these fields.
 Descendant tables however cannot remove an inherited NOT NULL
constraint, and so constraints should be added only carefully.  In the
above two cases, other tables might want to allow null foreign keys to
fields, or may not want to require that notes be filled out.

Now we may wish to create a table which stores landmark information:

CREATE TABLE landmark (
id serial not null unique,
name text primary key,
nearest_city text not null,
foreign key (country_id) references country(id),
CHECK (country_id IS NOT NULL),
CHECK (note_content IS NOT NULL)
) INHERITS (note_fields, country_ref);

The inheritance adds the following fields to the table:  note_content,
note_subject, and country_id.  These can now be queried as you would
normally, but the table can be cast to note_fields or country_ref for
processing in functions.  This gives you some extremely powerful
features you can add.  For example we can:

CREATE FUNCTION note_tsvector(note_fields)
RETURNS tsvector IMMUTABLE
LANGUAGE SQL AS $BODY$

SELECT to_tsvector('english', coalesce($1.note_subject, '') || ' ' ||
coalesce($1.note_content, ''));

$BODY$;

This gives us a value which can be calculated on the fly which is an
english language tsvector for the notes.  This can be invoked either
using class.method syntax or function(input).  Note in the version
below we could use note_tsvector(l) instead.  Moreover this interface
will apply to every table in the database which inherits note_fields.
Moreover we can index this value like we would elsewhere (though
CREATE INDEX appears to require the note_tsvector(landmark) syntax.
See [section on functional indexes] for more information.  Keep in
mind that indexes are not inherited so each individual table must be
indexed separately.


or_examples=# select name, note_content from landmark l where
plainto_tsquery('english', 'bridge') @@ l.note_tsvector;
name|note_content
+
 Eiffel Tower   | Designed by a great bridge builder
 Golden Gate Bridge | Iconic suspension bridge
(2 rows)

Similarly we can add a method to "dereference" the country_id to every
table which inherits country_ref:

CREATE FUNCTION country(country_ref) RETURNS country
LANGUAGE SQL STABLE AS
$BODY$ SELECT * FROM country WHERE id = $1.country_id $BODY$;

If we want to know the names of the landmarks in the database and what
countries they are in we can then:

SELECT name, (l.country).

Re: [DOCS] A user report of misinterpretation of 'unsupported versions'

[Chris Travers]

As we move towards 9.4, would it be worthwhile suggesting that we break
this out into "Obsolete Versions" and "Forthcoming" or similar?


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

[DOCS] SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY

[chris+postgresql]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.1/static/sql-set-constraints.html
Description:

We found that normally, if you execute SET TRANSACTION READ ONLY, it
prevents COMMIT from happening if any data has been changed in the
transaction (and we have been relying on this for safety).

However, SET CONSTRAINTS ALL IMMEDIATE causes this not to apply to any
subsequent changes. So it appears that the READ ONLY nature of the
transaction is implemented like a constraint.

This fails as expected:

BEGIN;
UPDATE foo SET contact='{"asdas": "1235435343"}' 
WHERE foo.id = 1;
SET TRANSACTION READ ONLY;
COMMIT;

This passes unexpectedly:

BEGIN;
SET CONSTRAINTS ALL IMMEDIATE;
UPDATE foo SET contact='{"asdas": "1235435343"}' 
WHERE foo.id = 1;
SET TRANSACTION READ ONLY;
COMMIT;

This fails as expected:

BEGIN;
SET TRANSACTION READ ONLY;
SET CONSTRAINTS ALL IMMEDIATE;
UPDATE foo SET contact='{"asdas": "1235435343"}' 
WHERE foo.id = 1;
COMMIT;


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

Re: [DOCS] Site Administrators Gendered

[Chris Travers]

On Thu, Jul 13, 2017 at 11:27 PM, Alvaro Herrera 
wrote:

> Alvaro Herrera wrote:
> > case.roll...@gmail.com wrote:
> >
> > > "Possibly, your site administrator has already created a database
> for your
> > > use. He should have told you what the name of your database is."
> > >
> > > should be
> > >
> > > "Possibly, your site administrator has already created a database
> for your
> > > use. They should have told you what the name of your database is."
> > >
> > > I imagine this mistake occurs elsewhere in the docs. It would be great
> to
> > > see it fixed across the board.
> >
> > Sure.  This has been discussed before, I believe.  Feel free to submit a
> > patch.
>
> Actually, this has already been patched; see
> http://git.postgresql.org/pg/commitdiff/741ccd5015f82e31f80cdc5d2ae812
> 63ea92d794
> The problem is that you're reading an ancient version of the docs.
> A more recent version of the page does not have that problem:
> https://www.postgresql.org/docs/9.6/static/tutorial-createdb.html


We should fix the number agreement problem with the replacement.  Make site
administrator plural.

>
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more