Re: [DOCS] Would like to contribute a section to docs for 9.3. Where to start?
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 tables (creating 'objects' in the process) and then issuing a notificat
Re: [DOCS] Foreign server version and type
On Mon, Aug 15, 2011 at 08:36:36PM +0300, Heikki Linnakangas wrote: > On 15.08.2011 19:58, Thom Brown wrote: > >I'm looking at the CREATE SERVER page on the documentation ( > >http://www.postgresql.org/docs/9.1/static/sql-createserver.html) and noticed > >that there's a server_version parameter which can be provided with the > >keyword VERSION and server_type specified after TYPE. > > > >However, there's zero information about what these are used for, whether > >they have any bearing on anything or why you'd want to use them. Are these > >parameters used for anything, or are they purely for future functionality? > > Whatever the case, could some kind of elaboration be added to justify the > >existence of these options? They have been in the docs since the CREATE > >SERVER syntax was introduced in 8.4. > > It's in the SQL spec. It isn't used anything by PostgreSQL itself, > but a wrapper could look at it. It would probably be good to say > that explicitly in the docs. I have applied the attached patch to document that these fields are only potentially useful to foreign data wrappers. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgml new file mode 100644 index be9be50..60744b2 *** a/doc/src/sgml/ref/create_server.sgml --- b/doc/src/sgml/ref/create_server.sgml *** CREATE SERVER server_type ! Optional server type. --- 69,75 server_type ! Optional server type, potentially useful to foreign-data wrappers. *** CREATE SERVER server_version ! Optional server version. --- 78,84 server_version ! Optional server version, potentially useful to foreign-data wrappers. -- 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] Foreign server version and type
On 15 August 2012 16:47, Bruce Momjian wrote: > On Mon, Aug 15, 2011 at 08:36:36PM +0300, Heikki Linnakangas wrote: >> On 15.08.2011 19:58, Thom Brown wrote: >> >I'm looking at the CREATE SERVER page on the documentation ( >> >http://www.postgresql.org/docs/9.1/static/sql-createserver.html) and noticed >> >that there's a server_version parameter which can be provided with the >> >keyword VERSION and server_type specified after TYPE. >> > >> >However, there's zero information about what these are used for, whether >> >they have any bearing on anything or why you'd want to use them. Are these >> >parameters used for anything, or are they purely for future functionality? >> > Whatever the case, could some kind of elaboration be added to justify the >> >existence of these options? They have been in the docs since the CREATE >> >SERVER syntax was introduced in 8.4. >> >> It's in the SQL spec. It isn't used anything by PostgreSQL itself, >> but a wrapper could look at it. It would probably be good to say >> that explicitly in the docs. > > I have applied the attached patch to document that these fields are only > potentially useful to foreign data wrappers. Thanks Bruce, and a very belated thanks to Heikki for the explanation. -- Thom -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
[DOCS] Re: [BUGS] documentation bug - behave of NEW a OLD in plpgsql's triggers
On Wed, Sep 7, 2011 at 03:40:19PM +0200, Pavel Stehule wrote: > 2011/9/7 Josh Kupershmidt : > > On Tue, Sep 6, 2011 at 10:54 PM, Bruce Momjian wrote: > >> Josh Kupershmidt wrote: > >>> How about a doc tweak like the attached? > >> > >> Perfect. Applied to 9.0, 9.1, and head. Thanks. Sorry for the delay. > > > > Err, as Tom's first comment in this thread explains, Pavel and I were > > both wrong: the variables in question are indeed NULL, not undefined. > > I think the docs were fine the way they were. > > There is maybe bug - these variables are defined, but they has not > assigned tupledesc, so there is not possible do any test > > postgres=# create table omega (a int, b int); > CREATE TABLE > postgres=# create or replace function foo_trig() > postgres-# returns trigger as $$ > postgres$# begin > postgres$# raise notice '%', new; > postgres$# return null; > postgres$# end; > postgres$# $$ language plpgsql; > CREATE FUNCTION > postgres=# create trigger xxx after delete on omega for each row > execute procedure foo_trig(); > CREATE TRIGGER > postgres=# insert into omega values(20); > INSERT 0 1 > postgres=# delete from omega; > ERROR: record "new" is not assigned yet > DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. > CONTEXT: PL/pgSQL function "foo_trig" line 3 at RAISE > > so current text in documentation is not correct too. I used your queries to test NEW/OLD on DELETE/INSERT, respectively, and for statement-level triggers, and you are right that they are unassigned, not NULL. The attached patch fixes our documentation for PG 9.3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml new file mode 100644 index ab40845..07fba57 *** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *** RAISE unique_violation USING MESSAGE = ' *** 3403,3409 Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level !triggers. This variable is NULL in statement-level triggers and for DELETE operations. --- 3403,3409 Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level !triggers. This variable is unassigned in statement-level triggers and for DELETE operations. *** RAISE unique_violation USING MESSAGE = ' *** 3415,3421 Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level !triggers. This variable is NULL in statement-level triggers and for INSERT operations. --- 3415,3421 Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level !triggers. This variable is unassigned in statement-level triggers and for INSERT operations. -- 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] create foreign table
On Thu, Sep 15, 2011 at 12:51:57PM -0400, Ray Stell wrote: > On Thu, Sep 15, 2011 at 06:43:59AM -0500, Robert Haas wrote: > >> On Mon, Sep 12, 2011 at 8:42 AM, Ray Stell wrote: > >> > http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html > >> > It would be helpful if "SERVER" was defined if the parameters list > >> > on this page. > >> > >> We don't usually document required keywords - they're just required. > >> We do document the meaning of server_name, which I think is probably > >> what you were after. > >> > >> -- > >> Robert Haas > >> EnterpriseDB: http://www.enterprisedb.com > >> The Enterprise PostgreSQL Company > > > > > >Thanks, Robert. > > > >Maybe if you just added a pointer to the "CREATE SERVER" command in > >your definition of server_name it would provide a bread crumb. > > > > Oh, I see my mistake. Maybe the inclusion of a link to "CREATE SERVER" > http://www.postgresql.org/docs/9.1/static/sql-createserver.html in the > server_name definition would be helpful, just like in the data_type line. > I was having trouble making the leap since the idea was a new one on me. I have done as you suggested with the attached patch which will appear in Postgres 9.3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml new file mode 100644 index 2113d00..f860ad0 *** a/doc/src/sgml/ref/create_foreign_table.sgml --- b/doc/src/sgml/ref/create_foreign_table.sgml *** CREATE FOREIGN TABLE [ IF NOT EXISTS ] < *** 135,141 The name of an existing server for the foreign table. ! --- 135,142 The name of an existing server for the foreign table. ! For details on defining a server, see . -- 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] somewhat wrong archive_command example
On Thu, Sep 22, 2011 at 02:15:30PM -0400, Josh Kupershmidt wrote:
> On Wed, Sep 21, 2011 at 8:59 PM, Euler Taveira de Oliveira
> wrote:
> > On 21-09-2011 20:02, Greg Smith wrote:
> >>
> >> Attached version fixes the late night brain fade errors. I think the right
> >> thing to do next is to package this up into a doc update that corrects the
> >> errors in that section too; just clean the whole thing up while I'm poking
> >> at
> >> it. I'll submit that over to the hackers list so that everyone can take a
> >> shot
> >> at correcting my shell code.
>
> First, a +1 on the impetus for this script.
>
> Few more suggestions/nitpicks:
> 1.) IMO it's more logical to put the test for whether the $ARCHIVE
> directory exists before the test whether ${ARCHIVE}/${FILE} exists.
> 2.) I think the error code reporting here is not sound:
>
> cp ${FULLPATH} ${ARCHIVE}/${FILE}
> if [ $? -ne 0 ] ; then
> echo $0 Archive copy of ${FILE} failed with error $? >&2
>
> at least on my OS X machine, that echo produces a message like
> "./local_backup_script.sh Archive copy of failed with error 0", I
> guess since $? gets reset to 0 after that if-statement. You can use a
> temporary variable like $ERRCODE=$? to get around this.
I have made all the suggestions posted and would like to add the
attached script to our documentation as a simple example.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
local_backup_script.sh
Description: Bourne shell script
--
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] PGDATA confusion
On Fri, Nov 4, 2011 at 12:32:13PM -0400, Bruce Momjian wrote: > Thom Brown wrote: > > > So if one set PGDATA to somewhere which had no database files at all, > > > but just postgresql.conf, it could still work (assuming it, in turn, > > > set data_directory correctly), but not vice versa. ?It would make more > > > sense to call it PGCONFIG, although I'm not proposing that, especially > > > since PGDATA makes sense when it comes to initdb. > > > > > > There are probably plenty of other places in the docs which also don't > > > adequately describe PGDATA or -D. > > > > > > Any disagreements? ?If not, should I write a patch (since someone will > > > probably accuse me of volunteering anyway) or would someone like to > > > commit some adjustments? > > > > No opinions on this? > > Yes. I had kept it to deal with later. Please work on a doc patch to > try to clean this up. pg_upgrade just went through this confusion and I > also was unhappy at how vague things are in this area. > > Things got very confusing with pg_upgrade when PGDATA pointed to the > configuration directory and the data_directory GUC pointed to the data > directory. I have applied the attached doc patch for PG 9.3 to clarify PGDATA. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/pg_ctl-ref.sgml b/doc/src/sgml/ref/pg_ctl-ref.sgml new file mode 100644 index 90725d9..7a4c2be *** a/doc/src/sgml/ref/pg_ctl-ref.sgml --- b/doc/src/sgml/ref/pg_ctl-ref.sgml *** PostgreSQL documentation *** 261,267 --pgdata datadir ! Specifies the file system location of the database files. If this is omitted, the environment variable PGDATA is used. --- 261,267 --pgdata datadir ! Specifies the file system location of the database configuration files. If this is omitted, the environment variable PGDATA is used. diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml new file mode 100644 index a1f36e1..41745fb *** a/doc/src/sgml/ref/postgres-ref.sgml --- b/doc/src/sgml/ref/postgres-ref.sgml *** PostgreSQL documentation *** 179,186 -D datadir ! Specifies the file system location of the data directory or ! configuration file(s). See for details. --- 179,186 -D datadir ! Specifies the file system location of the database ! configuration files. See for details. diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml new file mode 100644 index bd2dca3..e0a93c1 *** a/doc/src/sgml/storage.sgml --- b/doc/src/sgml/storage.sgml *** directories. *** 19,25 ! All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as PGDATA (after the name of the environment variable that can be used to define it). A common location for PGDATA is /var/lib/pgsql/data. Multiple clusters, --- 19,26 ! Traditionally, the configuration and data files used by a database ! cluster are stored together within the cluster's data directory, commonly referred to as PGDATA (after the name of the environment variable that can be used to define it). A common location for PGDATA is /var/lib/pgsql/data. Multiple clusters, *** these required items, the cluster config *** 33,39 postgresql.conf, pg_hba.conf, and pg_ident.conf are traditionally stored in PGDATA (although in PostgreSQL 8.0 and ! later, it is possible to keep them elsewhere). --- 34,40 postgresql.conf, pg_hba.conf, and pg_ident.conf are traditionally stored in PGDATA (although in PostgreSQL 8.0 and ! later, it is possible to place them elsewhere). -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
