[DOCS] functions-formatting.html
Was reading the docs and stumbled across what I believe is a mistake. There is a mistake in Table 9-25 the result of to_char(148.5,'999D999') should be ' 148.500', the docs list it as ' 148,500' http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html and http://developer.postgresql.org/pgdocs/postgres/functions-formatting.html -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
[DOCS] The Tutorial(TM)
Kind people, I was looking over the Tutorial section, and noticed that Foreign Keys is in the "Advanced" section. I believe this is a mistake, and would like to patch up the difference. What files are involved in the source tree apart from doc/src/sgml/query.sgml and doc/src/sgml/query.sgml ? TIA :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 Power over a man's subsistence is power over his will. Alexander Hamilton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [DOCS] The Tutorial(TM)
On Wed, Jan 07, 2004 at 09:13:47PM -0500, Bruce Momjian wrote: > David Fetter wrote: > > Kind people, > > > > I was looking over the Tutorial section, and noticed that Foreign > > Keys is in the "Advanced" section. I believe this is a mistake, > > and would like to patch up the difference. What files are > > involved in the source tree apart from doc/src/sgml/query.sgml and > > doc/src/sgml/query.sgml ? > > > > TIA :) > > I think you just move the section and submit a patch. Patching... BTW, src/tutorial/basics.source and src/tutorial/advanced.source also need patching for this. BTW2, as this is a pretty extensive rewrite, I'll put up a web page that shows how the patch would work :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [DOCS] The Tutorial(TM)
On Thu, Jan 08, 2004 at 08:52:29AM +0100, Jeroen Ruigrok/asmodai wrote: > -On [20040108 07:32], Peter Eisentraut ([EMAIL PROTECTED]) wrote: > >Why? > Because foreign keys are part of database design 101? Yes. Expanding on that, putting FK's in "Advanced Features" gives the (IME always wrong) impression that they're optional. Chris Browne, could you give me a pointer to your patch so we don't step on each other's feet? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [DOCS] The Tutorial(TM)
On Thu, Jan 08, 2004 at 06:07:13PM +0100, Peter Eisentraut wrote: > David Fetter wrote: > > Expanding on that, putting FK's in "Advanced Features" gives the > > (IME always wrong) impression that they're optional. > > They *are* optional. I disagree. > The first chapter is about getting the data in and out. That is not > optional for using a database. Transactions, views, foreign keys, > primary keys even, and everything else that some people think is > essential for a "real" database are in fact optional. > Remember that the tutorial is intended for people starting from > zero. No, they're not optional. If people see how it's done right, starting from zero, they will not then develop bad habits that have to be un-learned. > Let's not overwhelm them right away. I don't think this will be overwhelming. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] The Tutorial(TM)
Christopher Browne <[EMAIL PROTECTED]> wrote: > Oops! [EMAIL PROTECTED] (David Fetter) was seen spray-painting on a wall: >> On Thu, Jan 08, 2004 at 06:07:13PM +0100, Peter Eisentraut wrote: >>> David Fetter wrote: >>> > Expanding on that, putting FK's in "Advanced Features" gives the >>> > (IME always wrong) impression that they're optional. >>> >>> They *are* optional. >> >> I disagree. >> >>> The first chapter is about getting the data in and out. That is >>> not optional for using a database. Transactions, views, foreign >>> keys, primary keys even, and everything else that some people >>> think is essential for a "real" database are in fact optional. >>> Remember that the tutorial is intended for people starting from >>> zero. >> >> No, they're not optional. If people see how it's done right, >> starting from zero, they will not then develop bad habits that have >> to be un-learned. >> >>> Let's not overwhelm them right away. >> >> I don't think this will be overwhelming. > > When some Pointy-Haired Type has some ridiculously large list of > things that they say are all "top priorities," that implies that all > are of equally _LOW_ priority. Good point. How about this, then? For the table create scripts at the beginning, I'll put in FK's with a pointer to the DB design section, and just go with 'em from there. That way, people get used to seeing and doing things the right way, even if they don't understand them right off the bat. > I rather like Tom Lane's suggestion that it makes sense to split into > _three_ sections: > > 1. Rudimentary queries, where novices figure out the basics of > SELECT, INSERT, DELETE, UPDATE. Perhaps with simple joins. > > 2. Intermediate queries, where more complex joins, views, > transactions, subselects, some mention of vacuum/analyze/explain > and such, are presented. > > 3. The "advanced" part might instead get called "Designing > Databases," and present foreign keys, stored procedures, > triggers, and such. > Foreign keys would _naturally_ flow into the section that is on > design, as they are a "design" matter. Excellent :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 Liberty is inseparable from social justice, and those who dissociate them, sacrificing the first with the purpose of attaining the second more quickly, are the true barbarians of our time. Mario Vargas Llosa ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] [HACKERS] Tutorial
On Fri, Jul 23, 2004 at 09:03:30AM +0200, Peter Eisentraut wrote:
> Tom Lane wrote:
> > Robert Treat <[EMAIL PROTECTED]> writes:
> > > +1/2 (Since I don't like inheritence)
> > >
> > > IMHO we ought to try to keep the _tutorial_ free of things that
> > > are generally considered against relational design.
> >
> > Where is it written that inheritance is against relational design?
>
> I would venture that it is nowhere written that it is part of
> relational design. It is, however, unambiguously part of
> object-relational design, if that's what we're aiming for.
I see I have put my foot in it again. Please bear with me here.
Object-relational in general is not broken and is being worked on.
Custom data-types, custom aggregates, etc., etc. are working just
great, and lots of people use them.
What *is* broken is table inheritance, and the docs need to reflect
this.
If the parent table has a foreign key to another table foo, CASCADEing
DELETEs on foo leave ghost entries in the tables with inheritance.
Please find enclosed a repro, which demonstrates the problem on CVS
tip and 7.4.3.
Just an FYI, I first discovered this problem in a payment system.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
BEGIN;
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY
);
CREATE TABLE parent (
parent_id SERIAL PRIMARY KEY
, foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE CASCADE
, parent_1_text TEXT NOT NULL
);
CREATE TABLE child_1 (
child_1_text TEXT NOT NULL
) INHERITS(parent);
CREATE TABLE child_2 (
child_2_text TEXT NOT NULL
) INHERITS(parent);
INSERT INTO foo VALUES(DEFAULT);
INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)
VALUES (currval('public.foo_foo_id_seq'), 'parent text 1', 'child_1 text 1');
INSERT INTO foo VALUES(DEFAULT);
INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)
VALUES (currval('public.foo_foo_id_seq'), 'parent text 2', 'child_1 text 2');
INSERT INTO foo VALUES(DEFAULT);
INSERT INTO child_2 (foo_id, parent_1_text, child_2_text)
VALUES (currval('foo_foo_id_seq'), 'parent text 3', 'child_2 text 1');
DELETE FROM foo WHERE foo_id = 1;
SELECT * FROM parent;
SELECT * FROM child_1;
ROLLBACK;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] [HACKERS] Tutorial
On Fri, Jul 23, 2004 at 03:31:47PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > What *is* broken is table inheritance, and the docs need to reflect > > this. > > The combination of inheritance with certain other features is broken, > yes, and the docs do reflect that (see the bottom of > http://www.postgresql.org/docs/7.4/static/ddl-inherit.html > for example). > > I will grant you that this page is a near duplicate of the > tutorial's discussion of inheritance, which is surely bad --- either > they should be exact duplicates, or one or the other needs > rewriting. But I'm not really going to hold still for the docs on > inheritance being rewritten by someone who considers the entire > concept broken. Maybe we can get elein to do it ;-) I don't consider the concept broken. The implementation is, in fact, broken, and putting that broken piece in the tutorial is, imnsho, a bad mistake. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [DOCS] [HACKERS] Tutorial
On Fri, Jul 23, 2004 at 01:25:56PM -0700, elein wrote: > Perhaps after OSCON I can work with fetter on getting the > documentation clarified. OK? Sounds like fun. There are all kinds of object-relational concepts other than this broken piece. Which ones are good to highlight in that tutorial? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [DOCS] [HACKERS] Tutorial
On Fri, Jul 23, 2004 at 04:30:40PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > I don't consider the concept broken. The implementation is, in > > fact, broken, and putting that broken piece in the tutorial is, > > imnsho, a bad mistake. > > 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? As far as I know, the only documented feature in PostgreSQL that is is table inheritance. Anyhow, there are lots of ways to highlight the object-relational features that PostgreSQL provides. Table inheritance just isn't a good one. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [DOCS] [HACKERS] Tutorial
On Fri, Jul 23, 2004 at 04:58:55PM -0400, Tom Lane wrote: > 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. Fair enough. How about adding an explanation of the limits of table inheritance illustrated by that example (or other suitable one)? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] [HACKERS] 7.5 release notes
Bruce Momjian wrote: I have completed the 7.5 release notes. You can view them in HTML on the developer web page. I have marked a few items with question marks that need to be addressed. I am looking for improvements, even minor ones. Either send in a patch or committers can modify the file directly. In E.1.1 Overview: "... This release supports Windows NT 4 and all later releases. It does not support earlier releases like Windows 95, 98, or ME because ... " Win98 and WinME is released _after_ NT4. See http://www.computerhope.com/history/windows.htm Perhaps it's better to say: "We only support NT-based Windows such as NT4, Win2k/XP/2003/later. Old 9x-based Windows versions such as Win95/98/Me are not supported because ..." Btw, can 7.5 run on WinCE/XP Media Center/XP SP2/etc? -- dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[DOCS] Documenting NULLVALUE() and NONNULLVALUE()
Perl and PHP programmers (such as myself) are accustomed to using defined()/isset() and so when wanting to test null value they might look to the Functions chapter (9) instead of the SQL Syntax chapter (4). It took me some time before I found out about ISNULL, IS NULL, NOTNULL, IS NOT NULL. In MySQL I am accustomed to using ISNULL() function. Perhaps we can change section 9.1 from "Logical Operators" to "Logical Functions and Operators"? NULLVALUE() and NONNULLVALUE() can be documented there, along with IS, IS NULL, and IS NOT NULL (which also don't seem to have been covered yet in Chapter 9). Should I submit a patch? -- dave ---(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: [DOCS] Documenting NULLVALUE() and NONNULLVALUE()
Tom Lane wrote: Perhaps we can change section 9.1 from "Logical Operators" to "Logical Functions and Operators"? NULLVALUE() and NONNULLVALUE() can be documented there, along with IS, IS NULL, and IS NOT NULL (which also don't seem to have been covered yet in Chapter 9). I don't think we should document nullvalue() and nonnullvalue(), because they are not standard. > As for "not covered", IS NULL is documented in 9.2 Comparison Operators. Ah, it's not immediately visible so I missed that. Thanks. -- dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [DOCS] Suggestion on reorganizing functions
On Mon, Aug 09, 2004 at 11:20:33PM +0200, Peter Eisentraut wrote:
> Tom Lane wrote:
> > I am not sure if DocBook could handle generating an index covering
> > just functions, or if we'd have to merge it with the general
> > index.
>
> I think it's possible -- with a bit of programming work. I doubt,
> however, that it's going to be all that useful. We're already
> having trouble categorizing things like IS NULL (function?,
> operator?, special construct?).
For docs, redundancy is fine, at least at the output level :)
> A function index would be quite unreliable ("It's not in the
> function index, so it's not supported."). Feel free to add general
> index entries for all functions, though.
Where?
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---(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: [DOCS] Suggestion on reorganizing functions
On Mon, Aug 09, 2004 at 05:34:56PM -0400, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> > On Mon, Aug 09, 2004 at 11:20:33PM +0200, Peter Eisentraut wrote:
> >> A function index would be quite unreliable ("It's not in the
> >> function index, so it's not supported."). Feel free to add general
> >> index entries for all functions, though.
>
> > Where?
>
> In func.sgml. For example, this section seems adequately well indexed:
I think Jeff's original point was that he wanted some kind of index
for all functions, not just ones you already know how to classify.
Thanks for the pointer :)
>
> Sequence Manipulation Functions
>
>
>sequence
>
[snip]
> One thought though is that it's not clear when looking at the index that
> these entries are function names. Would it be useful to decorate them
> somehow, eg by adding "()" to the names or setting them in a fixed-width
> font?
Sure :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---(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
[DOCS] Array function
Folks,
Here's a little addition to the array functions & operators section of
the manual. It adds a function array() to the list.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.221
diff -u -r1.221 func.sgml
--- doc/src/sgml/func.sgml 26 Oct 2004 22:16:11 - 1.221
+++ doc/src/sgml/func.sgml 1 Nov 2004 00:33:22 -
@@ -6928,6 +6928,20 @@
string_to_array( 'xx~^~yy~^~zz', '~^~')
{xx,yy,zz}
+
+
+
+ array
+ (single-column SELECT)
+
+
+ anyarray
+
+ turns a single-column SELECT statement into an array
+
+array(SELECT foo FROM bar)
+{1,2,3}
+
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [DOCS] Array function
On Sun, Oct 31, 2004 at 10:40:47PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Here's a little addition to the array functions & operators section of > > the manual. It adds a function array() to the list. > > The ARRAY() construct isn't really a function; it is a special > syntax documented in Array Constructors, > http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS > > The addition you propose is misleading, because it would lead people > to expect to find array() in the output of \df, for example. It > might be reasonable to put in some kind of "see also" > cross-reference in the array functions section, but we shouldn't > list array() as though it were just like every other function in the > section. Good point. Please find attached a different diff :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.221 diff -u -r1.221 func.sgml --- doc/src/sgml/func.sgml 26 Oct 2004 22:16:11 - 1.221 +++ doc/src/sgml/func.sgml 1 Nov 2004 05:19:02 - @@ -6931,6 +6931,16 @@ + + NOTE: Although it is not strictly a function +and does not appear in \df in psql, ARRAY(), +mentioned in acts much +like a table function (equivalently, a +set-returning function or a +SRF--see ) +which takes anyarray and returns a set of +anyelement. + ---(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: [DOCS] Array function
On Mon, Nov 01, 2004 at 08:47:05PM +0100, Peter Eisentraut wrote: > David Fetter wrote: > > Please find attached a different diff :) > > If you want to write a note, use the element. Thanks for the tip :) Fixed, attaching a diff against CVS HEAD. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.221 diff -u -r1.221 func.sgml --- doc/src/sgml/func.sgml 26 Oct 2004 22:16:11 - 1.221 +++ doc/src/sgml/func.sgml 1 Nov 2004 20:21:02 - @@ -6931,6 +6931,18 @@ + + +Although it is not strictly a function and does not appear in \df +in psql, ARRAY(), mentioned in acts much like a +table function (equivalently, a +set-returning function or a +SRF--see ) +which takes anyarray and returns a set of +anyelement. + + ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [DOCS] Array function
On Mon, Nov 01, 2004 at 01:59:13PM -0600, Jim C. Nasby wrote: > On Sun, Oct 31, 2004 at 04:36:14PM -0800, David Fetter wrote: > > Folks, > > > > Here's a little addition to the array functions & operators > > section of the manual. It adds a function array() to the list. > > > > Cheers, D > > BTW, it seems it would be useful to dedicate a chapter to array > handling, since it's something that's not very common amongst > databases. I guess it depends what you mean. Oracle has "subtables," and other things have stuff like that. Also, it's in the SQL standard... Anyhow, where do you think it should go? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(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: [DOCS] Documentation of server configuration
On Sat, Nov 13, 2004 at 09:20:51PM +0100, Peter Eisentraut wrote: > I've just spent a day working through the current set of server > configuration parameters, and I think that the documentation at > <http://developer.postgresql.org/docs/postgres/runtime-config.html> has > reached its peak of unusability. I haven't been able to find a single > parameter all day except by using a text search over the file. My thoughts, hardly original, are: 1. Now is not the time to do massive re-arranging, 2. We should provide an alphabetized grand list with links in some obvious place. I am volunteering to do said list & linking :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[DOCS] Approximate count(*)
Folks, Please find enclosed a patch that shows how to get a quick approximation of count(*) on a table. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.241 diff -c -r1.241 func.sgml *** doc/src/sgml/func.sgml 14 Mar 2005 18:31:19 - 1.241 --- doc/src/sgml/func.sgml 24 Mar 2005 16:25:55 - *** *** 7330,7339 ! Unfortunately, there is no similarly trivial query that can be ! used to improve the performance of count() ! when applied to the entire table. --- 7330,7348 ! When the table has been VACUUMed recently, but ! only then, a good approximation of count(*) for an entire table ! can be obtained as follows: ! ! SELECT reltuples FROM pg_class WHERE relname = 'sometable'; ! ! ! ! ! Unfortunately, there is not yet a general trivial query that can ! be used to improve the performance of count(). + ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [DOCS] Approximate count(*)
On Thu, Mar 24, 2005 at 12:34:51PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Please find enclosed a patch that shows how to get a quick > > approximation of count(*) on a table. > > I'm not sure we should be encouraging people to look at reltuples... > for one thing, it's deliberately a moving average under 8.0. Should there be more caveats? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [DOCS] Missing doc on expression format for ALTER TABLE
On Sun, Mar 27, 2005 at 09:50:47PM -0800, Josh Berkus wrote: > Guys, > > > > Well, yes, actually. Where in the documentation is a type > > > conversion expression defined? Do we use NEW and OLD? Or the > > > name of the column? Or something else? > > > > I think the ALTER TABLE page is in dire need of a "See also" > > section, at least. And a reference to where is > > defined would be nice to have. > > What I'm pointing out here is that the USING clause can't be just > ANY expression, it needs to be a specific type of expression. And > the docs don't explain what kind of expression is usable here. > Even a single example would go a long way ... I sent a patch in awhile ago that added this: "To change an integer column containing UNIX timestamps to timestamp with time zone via a USING clause: ALTER TABLE foo ALTER COLUMN foo_timestamp TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'; " That is at least something, although I agree that more examples of the exact kind of expression would be a very good thing. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Storage requirements for NUMERIC
Folks, Please find a patch per IRC chat yesterday that clarifies how much space a NUMERIC takes. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/datatype.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/datatype.sgml,v retrieving revision 1.156 diff -c -r1.156 datatype.sgml *** doc/src/sgml/datatype.sgml 13 Mar 2005 09:36:30 - 1.156 --- doc/src/sgml/datatype.sgml 30 Apr 2005 23:57:51 - *** *** 503,511 Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column ! are maximums, not fixed allocations. (In this sense the numeric ! type is more akin to varchar(n) ! than to char(n).) --- 503,513 Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column ! are maximums, not fixed allocations, so each numeric needs eight ! bytes in headers and two bytes for each four decimal digits actually ! stored. (In this sense the numeric type is more akin to ! varchar(n) than to ! char(n).) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Add cross references for CREATE FUNCTION to PLs
Folks, Please find enclosed a patch which adds cross-references to the CREATE FUNCTION syntax in the PL/* docs. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/plperl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v retrieving revision 2.39 diff -c -r2.39 plperl.sgml *** doc/src/sgml/plperl.sgml9 Apr 2005 03:52:43 - 2.39 --- doc/src/sgml/plperl.sgml20 May 2005 00:02:47 - *** *** 44,50 PL/Perl Functions and Arguments !To create a function in the PL/Perl language, use the standard syntax: CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$ # PL/Perl function body --- 44,51 PL/Perl Functions and Arguments !To create a function in the PL/Perl language, use the standard ! syntax (): CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$ # PL/Perl function body Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.67 diff -c -r1.67 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 19 Apr 2005 03:55:43 - 1.67 --- doc/src/sgml/plpgsql.sgml 20 May 2005 00:02:50 - *** *** 53,61 Except for input/output conversion and calculation functions for user-defined types, anything that can be defined in C language functions can also be done with PL/pgSQL. ! For example, it is possible to ! create complex conditional computation functions and later use ! them to define operators or use them in index expressions. --- 53,61 Except for input/output conversion and calculation functions for user-defined types, anything that can be defined in C language functions can also be done with PL/pgSQL. ! For example, it is possible to create complex conditional ! computation functions () and ! later use them to define operators or use them in index expressions. Index: doc/src/sgml/plpython.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpython.sgml,v retrieving revision 1.28 diff -c -r1.28 plpython.sgml *** doc/src/sgml/plpython.sgml 9 Apr 2005 03:52:43 - 1.28 --- doc/src/sgml/plpython.sgml 20 May 2005 00:02:50 - *** *** 46,52 PL/Python Functions !Functions in PL/Python are declared in the usual way, for example CREATE FUNCTION myfunc(text) RETURNS text AS 'return args[0]' --- 46,53 PL/Python Functions !Functions in PL/Python are declared in the usual way ! (), for example CREATE FUNCTION myfunc(text) RETURNS text AS 'return args[0]' Index: doc/src/sgml/pltcl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/pltcl.sgml,v retrieving revision 2.37 diff -c -r2.37 pltcl.sgml *** doc/src/sgml/pltcl.sgml 9 Apr 2005 03:52:43 - 2.37 --- doc/src/sgml/pltcl.sgml 20 May 2005 00:02:51 - *** *** 75,81 PL/Tcl Functions and Arguments ! To create a function in the PL/Tcl language, use the standard syntax: CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$ --- 75,82 PL/Tcl Functions and Arguments ! To create a function in the PL/Tcl language, use ! the standard syntax (): CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Doc patch: New PL/Perl Features
Folks,
Please find enclosed document patches for PL/Perl features recently
introduced in CVS TIP. These include:
return_next
returning PostgreSQL arrays
spi_query/spi_fetchrow
use strict
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.41
diff -c -r2.41 plperl.sgml
*** doc/src/sgml/plperl.sgml5 Jun 2005 03:16:29 - 2.41
--- doc/src/sgml/plperl.sgml12 Jul 2005 19:18:05 -
***
*** 54,59
--- 54,86
The body of the function is ordinary Perl code.
+
+ As with ordinary Perl code, you should use the strict pragma,
+ which you can do one of two ways:
+
+
+
+
+ Globally, by turning on plperl (one of the you can use) and setting
+ plperl.strict to true in your postgresql.conf, or
+
+
+
+
+ One function at a time, by using PL/PerlU (you must be
+ database superuser to do this) and issuing a
+
+
+ use strict;
+
+
+ in the code.
+
+
+
+
The syntax of the CREATE FUNCTION command requires
***
*** 118,123
--- 145,165
+Perl can return PostgreSQL arrays as references to Perl arrays.
+ Here is an example:
+
+ CREATE OR REPLACE function returns_array()
+ RETURNS text[][]
+ LANGUAGE plperl
+ AS $$
+ return [['a"b','c,d'],['e\\f','g']];
+ $$;
+
+ select returns_array();
+
+
+
+
Composite-type arguments are passed to the function as references
to hashes. The keys of the hash are the attribute names of the
composite type. Here is an example:
***
*** 158,171
!PL/Perl functions can also return sets of either scalar or composite
!types. To do this, return a reference to an array that contains
!either scalars or references to hashes, respectively. Here are
!some simple examples:
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
! return [0..$_[0]];
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
--- 200,240
! PL/Perl functions can also return sets of either scalar or
! composite types. In general, you'll want to return rows one at a
! time both to speed up startup time and to keep from queueing up
! the entire result set in memory. You can do this with
! return_next as illustrated below:
!
! CREATE OR REPLACE FUNCTION perl_set_int(int)
! RETURNS SETOF INTEGER
! LANGUAGE plperl AS $$
! foreach (0..$_[0]) {
! return_next($_);
! }
! return;
! $$;
!
! SELECT * FROM perl_set_int(5);
!
! CREATE OR REPLACE FUNCTION perl_set()
! RETURNS SETOF testrowperl
! LANGUAGE plperl AS $$
! return_next({f1 => 1, f2 => 'Hello', f3 => 'World' });
! return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
! return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
! return;
! $$;
!
!
! For small result sets, you can return a reference to
! an array that contains either scalars or references to hashes,
! respectively. Here are some simple examples of returning the
! entire result set as a reference:
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
! return [0..$_[0]];
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
***
*** 182,192
SELECT * FROM perl_set();
-When you do this, Perl will have to build the entire array in memory;
-therefore the technique does not scale to very large result sets. You
-can instead call return_next for each element of
-the result set, passing it either a scalar or a reference to a hash,
-as appropriate to your function's return type.
--- 251,256
***
*** 217,223
!PL/Perl itself presently provides two additional Perl commands:
--- 281,287
!PL/Perl itself presently provides four additional Perl commands:
***
*** 228,233
--- 292,301
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(query)
+ spi_fetchrow(result of
spi_query)
+
+
Executes an SQL command. Here is an example of a query
***
*** 280,287
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
! my $res = [];
my $rv = spi_exec_query('select i, v from te
Re: [DOCS] [PATCHES] Doc patch: New PL/Perl Features
On Tue, Jul 12, 2005 at 12:21:29PM -0700, David Fetter wrote:
> Folks,
>
> Please find enclosed document patches for PL/Perl features recently
> introduced in CVS TIP. These include:
>
> return_next
> returning PostgreSQL arrays
> spi_query/spi_fetchrow
> use strict
>
> Cheers,
> D
Oops. Persuant to corrections and clarifications by Andrew Dunstan,
please find enclosed a better patch.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.41
diff -c -r2.41 plperl.sgml
*** doc/src/sgml/plperl.sgml5 Jun 2005 03:16:29 - 2.41
--- doc/src/sgml/plperl.sgml12 Jul 2005 19:59:40 -
***
*** 54,59
--- 54,86
The body of the function is ordinary Perl code.
+
+ As with ordinary Perl code, you should use the strict pragma,
+ which you can do one of two ways:
+
+
+
+
+ Globally, by turning on plperl (one of the you can use) and setting
+ plperl.use_strict to true in your postgresql.conf, or
+
+
+
+
+ One function at a time, by using PL/PerlU (you must be
+ database superuser to do this) and issuing a
+
+
+ use strict;
+
+
+ in the code.
+
+
+
+
The syntax of the CREATE FUNCTION command requires
***
*** 118,123
--- 145,165
+Perl can return PostgreSQL arrays as references to Perl arrays.
+ Here is an example:
+
+ CREATE OR REPLACE function returns_array()
+ RETURNS text[][]
+ LANGUAGE plperl
+ AS $$
+ return [['a"b','c,d'],['e\\f','g']];
+ $$;
+
+ select returns_array();
+
+
+
+
Composite-type arguments are passed to the function as references
to hashes. The keys of the hash are the attribute names of the
composite type. Here is an example:
***
*** 158,171
!PL/Perl functions can also return sets of either scalar or composite
!types. To do this, return a reference to an array that contains
!either scalars or references to hashes, respectively. Here are
!some simple examples:
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
! return [0..$_[0]];
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
--- 200,245
! PL/Perl functions can also return sets of either scalar or
! composite types. In general, you'll want to return rows one at a
! time both to speed up startup time and to keep from queueing up
! the entire result set in memory. You can do this with
! return_next as illustrated below. Note that
! after the last return_next, you must put
! either return; or (better) return
! undef;
!
!
! CREATE OR REPLACE FUNCTION perl_set_int(int)
! RETURNS SETOF INTEGER
! LANGUAGE plperl AS $$
! foreach (0..$_[0]) {
! return_next($_);
! }
! return undef;
! $$;
!
! SELECT * FROM perl_set_int(5);
!
! CREATE OR REPLACE FUNCTION perl_set()
! RETURNS SETOF testrowperl
! LANGUAGE plperl AS $$
! return_next({f1 => 1, f2 => 'Hello', f3 => 'World' });
! return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
! return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
! return undef;
! $$;
!
!
! For small result sets, you can return a reference to an array that
! contains either scalars, references to arrays, or references to
! hashes for simple types, array types, and composite types,
! respectively. Here are some simple examples of returning the entire
! result set as a reference:
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
! return [0..$_[0]];
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
***
*** 182,192
SELECT * FROM perl_set();
-When you do this, Perl will have to build the entire array in memory;
-therefore the technique does not scale to very large result sets. You
-can instead call return_next for each element of
-the result set, passing it either a scalar or a reference to a hash,
-as appropriate to your function's return type.
--- 256,261
***
*** 217,223
!PL/Perl itself presently provides two additional Perl commands:
--- 286,292
!PL/Perl itself presently provides four additional Perl commands:
***
*** 228,233
--- 297,306
spi_exec_query(query [,
max-rows])
spi_exec_q
Re: [DOCS] [HACKERS] PL/Perl list value return causes segfault
On Fri, Jul 29, 2005 at 11:24:37PM -0400, Bruce Momjian wrote:
>
> Would someone who knows perl update plperl.sgml and send me a patch?
>
> Also, is this still true in 8.1:
>
> In the current implementation, if you are fetching or returning
> very large data sets, you should be aware that these will all go
> into memory.
That's no longer true. Please find enclosed a new patch :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.42
diff -c -r2.42 plperl.sgml
*** doc/src/sgml/plperl.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml30 Jul 2005 05:42:56 -
***
*** 46,52
To create a function in the PL/Perl language, use the standard
!syntax:
CREATE FUNCTION funcname
(argument-types) RETURNS
return-type AS $$
# PL/Perl function body
--- 46,57
To create a function in the PL/Perl language, use the standard
!syntax. A PL/Perl function must always return a scalar value. You
!can return more complex structures (arrays, records, and sets)
!in the appropriate context by returning a reference.
!Never return a list. Here follows an example of a PL/Perl
!function.
!
CREATE FUNCTION funcname
(argument-types) RETURNS
return-type AS $$
# PL/Perl function body
***
*** 282,288
!PL/Perl provides two additional Perl commands:
--- 287,293
!PL/Perl provides three additional Perl commands:
***
*** 293,303
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
!Executes an SQL command. Here is an example of a query
!(SELECT command) with the optional maximum
!number of rows:
$rv = spi_exec_query('SELECT * FROM my_table', 5);
--- 298,315
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(command)
+
spi_fetchrow(command)
+
!spi_exec_query executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. You should only use this command when you know
! that the result set will be relatively small. Here is an
! example of a query (SELECT command) with the
! optional maximum number of rows:
!
$rv = spi_exec_query('SELECT * FROM my_table', 5);
***
*** 345,351
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
--- 357,363
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
***
*** 360,366
SELECT * FROM test_munge();
!
--- 372,416
SELECT * FROM test_munge();
!
!
! spi_query and spi_fetchrow
! work together as a pair for rowsets which may be large, or for cases
! where you wish to return rows as they arrive.
! spi_fetchrow works only with
! spi_query. The following example illustrates how
! you use them together:
!
!
! CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
!
! CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
! use Digest::MD5 qw(md5_hex);
! my $file = '/usr/share/dict/words';
! my $t = localtime;
! elog(NOTICE, "opening file $file at $t" );
! open my $fh, '<', $file # ooh, it's a file access!
! or elog(ERROR, "Can't open $file for reading: $!");
! my @words = <$fh>;
! close $fh;
! $t = localtime;
! elog(NOTICE, "closed file $file at $t");
! chomp(@words);
! my $row;
! my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
! while (defined ($row = spi_fetchrow($sth))) {
! return_next({
! the_num => $row->{a},
! the_text => md5_hex($words[rand @words])
! });
! }
! return;
! $$ LANGUAGE plperlu;
!
! SELECT * from lotsa_md5(500);
!
!
!
***
*** 716,724
! In the current implementation, i
Re: [DOCS] [HACKERS] PL/Perl list value return causes segfault
On Sat, Jul 30, 2005 at 09:47:58AM -0400, Andrew Dunstan wrote:
>
>
> David Fetter wrote:
>
> You have rolled 2 problems into one - spi_query+spi_fetchrow does not
> address the issue of returning large data sets.
>
> Suggest instead:
[suggestion]
Revised patch attached. Thanks for catching this :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.42
diff -c -r2.42 plperl.sgml
*** doc/src/sgml/plperl.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml31 Jul 2005 00:33:00 -
***
*** 46,52
To create a function in the PL/Perl language, use the standard
!syntax:
CREATE FUNCTION funcname
(argument-types) RETURNS
return-type AS $$
# PL/Perl function body
--- 46,57
To create a function in the PL/Perl language, use the standard
!syntax. A PL/Perl function must always return a scalar value. You
!can return more complex structures (arrays, records, and sets)
!in the appropriate context by returning a reference.
!Never return a list. Here follows an example of a PL/Perl
!function.
!
CREATE FUNCTION funcname
(argument-types) RETURNS
return-type AS $$
# PL/Perl function body
***
*** 282,288
!PL/Perl provides two additional Perl commands:
--- 287,293
!PL/Perl provides three additional Perl commands:
***
*** 293,303
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
!Executes an SQL command. Here is an example of a query
!(SELECT command) with the optional maximum
!number of rows:
$rv = spi_exec_query('SELECT * FROM my_table', 5);
--- 298,315
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(command)
+
spi_fetchrow(command)
+
!spi_exec_query executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. You should only use this command when you know
! that the result set will be relatively small. Here is an
! example of a query (SELECT command) with the
! optional maximum number of rows:
!
$rv = spi_exec_query('SELECT * FROM my_table', 5);
***
*** 345,351
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
--- 357,363
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
***
*** 360,366
SELECT * FROM test_munge();
!
--- 372,416
SELECT * FROM test_munge();
!
!
! spi_query and spi_fetchrow
! work together as a pair for rowsets which may be large, or for cases
! where you wish to return rows as they arrive.
! spi_fetchrow works only with
! spi_query. The following example illustrates how
! you use them together:
!
!
! CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
!
! CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
! use Digest::MD5 qw(md5_hex);
! my $file = '/usr/share/dict/words';
! my $t = localtime;
! elog(NOTICE, "opening file $file at $t" );
! open my $fh, '<', $file # ooh, it's a file access!
! or elog(ERROR, "Can't open $file for reading: $!");
! my @words = <$fh>;
! close $fh;
! $t = localtime;
! elog(NOTICE, "closed file $file at $t");
! chomp(@words);
! my $row;
! my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
! while (defined ($row = spi_fetchrow($sth))) {
! return_next({
! the_num => $row->{a},
! the_text => md5_hex($words[rand @words])
! });
! }
! return;
! $$ LANGUAGE plperlu;
!
! SELECT * from lotsa_md5(500);
!
!
!
***
*** 716,725
! In the current implementation, if you are fetching or returning
! very large data sets, you should be aware that these will al
[DOCS] Caveat for Domains
Folks, I think this needs to be made explicit in the documentation. Better, of course, would be some kind of change to the PL infrastructure that Just Handles It, but until then Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/ref/create_domain.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v retrieving revision 1.22 diff -c -r1.22 create_domain.sgml *** doc/src/sgml/ref/create_domain.sgml 2 May 2005 01:52:50 - 1.22 --- doc/src/sgml/ref/create_domain.sgml 9 Sep 2005 21:21:39 - *** *** 48,57 !Domains are useful for abstracting common fields between tables into !a single location for maintenance. For example, an email address column may be used !in several tables, all with the same properties. Define a domain and !use that rather than setting up each table's constraints individually. --- 48,60 !Domains are useful for abstracting common fields between tables !into a single location for maintenance. For example, an email address !column may be used in several tables, all with the same properties. !Define a domain and use that rather than setting up each table's !constraints individually. Keep in mind also that declaring a !function result value as a domain is pretty dangerous, because none of !the PLs enforce domain constraints on their results. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Clarifying Autovacuum docs in the release notes
Folks, Please find attached a patch which tells about what the spiffy, integrated autovacuum now does :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ? autovacuum.diff ? d0m41n_fux0r3d.diff Index: doc/src/sgml/release.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/release.sgml,v retrieving revision 1.365 diff -c -r1.365 release.sgml *** doc/src/sgml/release.sgml 4 Sep 2005 13:40:24 - 1.365 --- doc/src/sgml/release.sgml 12 Sep 2005 03:15:12 - *** *** 173,179 Integrating autovacuum into the server allows it to be automatically started and stopped in sync with the database server, and allows autovacuum to be configured from ! postgresql.conf. --- 173,181 Integrating autovacuum into the server allows it to be automatically started and stopped in sync with the database server, and allows autovacuum to be configured from ! postgresql.conf. Autovacuum now checks to see if ! the database is in danger of xid wraparound and ! VACUUMs the whole database if needed. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[DOCS] COPY example for partial tables
Folks, Please find enclosed a patch (should work for 7.3 and up) that illustrates a workaround for using COPY on parts of tables using temporary tables. It's helped me, and it seems popular via a very brief and un-scientific poll. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/ref/copy.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.67 diff -c -r1.67 copy.sgml *** doc/src/sgml/ref/copy.sgml 5 Sep 2005 14:44:05 - 1.67 --- doc/src/sgml/ref/copy.sgml 11 Oct 2005 23:00:40 - *** *** 709,714 --- 709,730 +To copy just the countries whose names start with 'A' into a file +using a temporary table which goes away at the end of the +transaction. This workaround will probably not be +needed for PostgreSQL 8.2 and + later. + + BEGIN; + CREATE TEMP TABLE a_list_COUNTRIES AS + SELECT * FROM country WHERE country_name LIKE 'A%'; + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; + ROLLBACK; + VACUUM; + + + + Here is a sample of data suitable for copying into a table from STDIN: ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] COPY example for partial tables
On Tue, Oct 11, 2005 at 06:12:53PM -0500, Jim C. Nasby wrote: > Why the vacuum? Seems a bit sever to do a vacuum of an entire database > just because you created a temp table. Excess enthusiasm about reclaiming space. It doesn't really need to be there :) Cheers, D > > On Tue, Oct 11, 2005 at 04:02:17PM -0700, David Fetter wrote: > > Folks, > > > > Please find enclosed a patch (should work for 7.3 and up) that > > illustrates a workaround for using COPY on parts of tables using > > temporary tables. It's helped me, and it seems popular via a very > > brief and un-scientific poll. > > > > Cheers, > > D > > -- > > David Fetter [EMAIL PROTECTED] http://fetter.org/ > > phone: +1 510 893 6100 mobile: +1 415 235 3778 > > > > Remember to vote! > > > Index: doc/src/sgml/ref/copy.sgml > > === > > RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v > > retrieving revision 1.67 > > diff -c -r1.67 copy.sgml > > *** doc/src/sgml/ref/copy.sgml 5 Sep 2005 14:44:05 - 1.67 > > --- doc/src/sgml/ref/copy.sgml 11 Oct 2005 23:00:40 - > > *** > > *** 709,714 > > --- 709,730 > > > > > > > > +To copy just the countries whose names start with 'A' into a file > > +using a temporary table which goes away at the end of the > > +transaction. This workaround will probably not be > > +needed for PostgreSQL 8.2 and > > + later. > > + > > + BEGIN; > > + CREATE TEMP TABLE a_list_COUNTRIES AS > > + SELECT * FROM country WHERE country_name LIKE 'A%'; > > + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; > > + ROLLBACK; > > + VACUUM; > > + > > + > > + > > + > > Here is a sample of data suitable for copying into a table from > > STDIN: > > > > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > >http://archives.postgresql.org > > > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] COPY example for partial tables
On Tue, Oct 11, 2005 at 06:33:42PM -0500, Jim C. Nasby wrote: > On Tue, Oct 11, 2005 at 04:22:40PM -0700, David Fetter wrote: > > On Tue, Oct 11, 2005 at 06:12:53PM -0500, Jim C. Nasby wrote: > > > Why the vacuum? Seems a bit sever to do a vacuum of an entire > > > database just because you created a temp table. > > > > Excess enthusiasm about reclaiming space. It doesn't really need > > to be there :) > > I think it needs to be commented on, one way or another. Better to > explain that this will slowly bloat pg_class than have a mystery > vacuum that many people have no idea why it's there... Patch fixes always welcome :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] COPY example for partial tables
On Thu, Oct 13, 2005 at 12:08:51AM -0400, Neil Conway wrote: > On Wed, 2005-12-10 at 10:55 -0400, Bruce Momjian wrote: > > > > +To copy into a file just the countries whose names start with 'A' > > +using a temporary table which is automatically deleted: > > + > > + > > + BEGIN; > > + CREATE TEMP TABLE a_list_COUNTRIES AS > > + SELECT * FROM country WHERE country_name LIKE 'A%'; > > + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; > > + ROLLBACK; > > + > > + > > The capitalization of "a_list_countries" is inconsistent -- both > references should all be in lowercase, IMO. Good catch :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] [PATCHES] Caveat for Domains
On Thu, Sep 22, 2005 at 07:57:35PM -0400, Bruce Momjian wrote: > Patch applied. Thanks. Could you please put fixing domains for functions on the TODO list? Cheers, D > > --- > > > David Fetter wrote: > > Folks, > > > > I think this needs to be made explicit in the documentation. Better, > > of course, would be some kind of change to the PL infrastructure that > > Just Handles It, but until then > > > > Cheers, > > D > > -- > > David Fetter [EMAIL PROTECTED] http://fetter.org/ > > phone: +1 510 893 6100 mobile: +1 415 235 3778 > > > > Remember to vote! > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > -- > Bruce Momjian| http://candle.pha.pa.us > [email protected] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] [pgsql-www] 8.1 PDF Documentation.
On Thu, Nov 10, 2005 at 06:01:42PM -0500, Kris Jurka wrote: > > 8.1 pdfs are finished. These versions actually got the correct pdf > bookmarks and table of contents. I can probably rebuild the 7.4 and > 8.0 versions in this format if there is sufficient interest. Way cool. Is producing these PDFs something that could be distributed? What tools actually work for this? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] [pgsql-www] 8.1 PDF Documentation.
On Tue, Nov 15, 2005 at 12:13:01AM -0500, Bruce Momjian wrote: > Tom Lane wrote: > > Kris Jurka <[EMAIL PROTECTED]> writes: > > > It took about two days each (run serially) on a 2xOpteron 246 using > > > debian > > > unstable's: > > > openjade: 1.3.2-8 > > > opensp: 1.5.1/0-4 > > > docbook-dsssl: 1.79-3 > > > > BTW, the -US file sends my (rather old) copy of acroread into what > > may be an infinite loop ... I gave up waiting for it to redisplay > > after trying to scroll down the bookmarks window. Even just > > displaying successive pages is painfully slow. > > > > I suspect there's something wrong with the toolset you're using. > > I am using Acrobat 5.0 on XP and BSD/OS and it worked fine for me, > including bookmarks. It was very fast. Acrobat works beautifully for me, too. :) > I am still unclear why there is no more reliable SGML toolchain for > PDF output after all these years. Seems things have improved > because we now have bookmarks, but still, why is this functionality > not more mainstream. See, there are these tuits, and they're round, and they're a precious resource usually spent on writing and testing C code rather than on improving (and helping transition adoption of) the SGML toolchain. Maybe we can get some kind soul(s) to donate some tuits and/or pay somebody a wage for awhile to fix the toolchain etc. :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[DOCS] Example for UPDATE FROM with correllation
Folks, Please find enclosed a doc patch that adds an example of a correllated UPDATE. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/ref/update.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v retrieving revision 1.33 diff -c -r1.33 update.sgml *** doc/src/sgml/ref/update.sgml12 Oct 2005 23:19:22 - 1.33 --- doc/src/sgml/ref/update.sgml12 Jan 2006 22:04:40 - *** *** 213,218 --- 213,230 (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); +Now that all the papers are signed, update the most recently closed + deal of the travelling salesperson who closed the Rocket Powered + Skates deal with the Acme Corporation. + + UPDATE employees SET last_closed_deal = deal.id + FROM accounts JOIN deals ON (account.id = deal.account_id) + WHERE deal.employee_id = employees.id + AND deal.name = 'Rocket Powered Skates' + AND accounts.name = 'Acme Corporation' + ORDER BY deal.signed_date DESC LIMIT 1; + + Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. To do this without failing the entire transaction, use savepoints. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] Get docs for contrib on the web somewhere
On Wed, Mar 08, 2006 at 01:50:47PM -0600, Jim C. Nasby wrote: > It would be nice if there was an easy place to see the documentation > for the various contrib projects. Would this be hard to setup? I > think the plain text would be fine, just need to post the readme's > somewhere and link to them. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/ has at least the raw files. I'd be delighted to put up something more, tho :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Get docs for contrib on the web somewhere
On Fri, Mar 17, 2006 at 12:22:44PM -0600, Jim C. Nasby wrote: > On Wed, Mar 08, 2006 at 02:05:44PM -0800, David Fetter wrote: > > On Wed, Mar 08, 2006 at 01:50:47PM -0600, Jim C. Nasby wrote: > > > It would be nice if there was an easy place to see the documentation > > > for the various contrib projects. Would this be hard to setup? I > > > think the plain text would be fine, just need to post the readme's > > > somewhere and link to them. > > > > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/ has at least > > the raw files. I'd be delighted to put up something more, tho :) > > Yeah, but very few people would ever think to look in CVS for > documentation. I think it'd be much better if we could include the > README's in the documentation build process somehow... I'm all for it. Do you have an idea of how this might work? I suppose some kind of idiot conversion of READMEs to man pages might be appropriate. Dunno about what would happen on Windows. Um, help? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] [GENERAL] COPY command documentation
On Thu, Mar 23, 2006 at 07:00:13PM +0100, Jim Nasby wrote: > On Mar 23, 2006, at 4:08 PM, Oisin Glynn wrote: > > >I just discovered that the comments from 8.0 had the answer I was > >looking for but these comments are not in the 8.1 docs. Should the > >comments be rolled forward as new versions are created? Or if valid > >comments added to the docs themselves? > > > >http://www.postgresql.org/docs/8.1/interactive/sql-copy.html > > > >http://www.postgresql.org/docs/8.0/interactive/sql-copy.html > > No, comments don't roll forward. ...and it's unlikely that they will, now or later, without somebody whose whole job is to monitor those comments and make patches. I'd like to make a Modest Proposalâ¢: Let's take down the interactive documents and, in their place, put up a request that doc patches be sent to -docs. What say? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] [GENERAL] COPY command documentation
On Thu, Mar 23, 2006 at 04:46:02PM -0500, Robert Treat wrote: > > ...and it's unlikely that they will, now or later, without > > somebody whose whole job is to monitor those comments and make > > patches. > > Well, we do make some attempt at rolling comments into the docs > where appropriate, but we could certainly use more dedicated > contributors in that area. If we're going to get dedicated contributors, we could direct their efforts to things a *lot* more productive than this. Improving the formal docs, for example. > > I'd like to make a Modest Proposalâ¢: Let's take down the > > interactive documents and, in their place, put up a request that > > doc patches be sent to -docs. > > > > What say? > > I'd say you're anti-interactive comments :-) I'm not against them. I'm just *for* improving the existing docs, and those comments don't (and won't, very likely) have any pipeline into those. Are you personally volunteering for this task, Robert? > More importantly, people just aren't going to to write patches for > doc additions... the overhead is several orders of magnitudes > greater than filling at a web form... so getting rid of the comments > is sure to lose any gains that we receive. What gains? As I said, I'm not against it, but right now, those things just go down the memory hole to the benefit of nobody. The detriment, I'd say, because somebody has wasted their time. > What I have tried to garner support for in the past was to either > direct those submission to this group for approval/rejection, which > would make the folks generally interested in docs directly involved > in the process. Somebody has to vet this. Please feel free to step up :) > The other option would be to mail approved doc comments to this > group so that someone could work them up into doc patches if > applicable. That really is a factor, most of the comments would need > to be reworded to be added into the docs proper. > > In the past these ideas were rejected as either off-topic or that it > would turn this list into a high traffic list... if peoples opinions > have changed, it could be arranged. I'm voicing a rejection for 'em again on the same grounds. Until we have a person whose paid, full-time job is web-comment rassling, this is a non-starter. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] [DOCS] Values list-of-targetlists patch for comments (was Re:
On Wed, Aug 09, 2006 at 03:05:02PM +0200, Peter Eisentraut wrote: > Am Freitag, 4. August 2006 04:50 schrieb Tom Lane: > > I'd like to see us refactor the docs as necessary to reflect that > > idea. Peter is right that this needs some discussion in > > syntax.sgml as well as in the reference pages --- but I'm still > > not very clear on how the presentation should go. > > I'm beginning to think that VALUES might be a separate command after > all. What's below definitely bolsters that idea :) postgres=# VALUES(1); column1 - 1 (1 row) However, there are some oddities: postgres=# SELECT * FROM (VALUES (1,2)) AS foo(bar,baz); bar | baz -+- 1 | 2 (1 row) postgres=# (VALUES (1,2)) AS foo(bar,baz); ERROR: syntax error at or near "AS" LINE 1: (VALUES (1,2)) AS foo(bar,baz); Does the SQL standard have anything to say about assigning identifiers both to the entire VALUES() statement and to its columns when the VALUES() statement is by itself? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] [HACKERS] New XML section for documentation
On Fri, Aug 25, 2006 at 07:46:57PM -0400, Bruce Momjian wrote: > Here is an new XML section for our SGML documentation. It explains > the various XML capabilities, if we support them, and how to use > them. > > Comments? This looks hauntingly similar to Peter's presentation at the conference. :) I'd add a http://wiscorp.com/SQLStandards.html to the reference section. Speaking of other parts of the SQL:2003 standard, how about one section each that mentions them? There's Part 4: SQL/PSM (Persistent Stored Modules) Part 9: SQL/MED (Management of External Data) (my favorite) Part 10: SQL/OLB (Object Language Binding) Part 11: SQL/Schemata Part 13: SQL/JRT (Java Routines and Types) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] [HACKERS] New XML section for documentation
On Fri, Aug 25, 2006 at 08:37:19PM -0400, Bruce Momjian wrote: > David Fetter wrote: > > On Fri, Aug 25, 2006 at 07:46:57PM -0400, Bruce Momjian wrote: > > > Here is an new XML section for our SGML documentation. It > > > explains the various XML capabilities, if we support them, and > > > how to use them. > > > > > > Comments? > > > > This looks hauntingly similar to Peter's presentation at the > > I used the XML/SQL and validation part from his talk, but the rest > was from earlier email discussions. Reuse is good :) > > conference. :) I'd add a http://wiscorp.com/SQLStandards.html to the > > This seems to be the best URL, but it seems too detailed: > > > http://wiscorp.com/H2-2005-197-SC32N1293-WG3_Presentation_for_SC32_20050418.pdf I'd just put the http://wiscorp.com/SQLStandards.html URL in, as it contains several references in varying levels of detail. > > reference section. > > > > Speaking of other parts of the SQL:2003 standard, how about one > > section each that mentions them? There's > > > > Part 4: SQL/PSM (Persistent Stored Modules) > > Part 9: SQL/MED (Management of External Data) (my favorite) > > Part 10: SQL/OLB (Object Language Binding) > > Part 11: SQL/Schemata > > Part 13: SQL/JRT (Java Routines and Types) > > I don't know anything about them. We claim SQL standard compliance, so since those are part of SQL:2003, we probably ought to mention them. SQL/PSM is a programming language that lives inside the database, and DB2 and MySQL have it. SQL/MED lets people talk to other data stores. SQL/OLB appears to be derived from equel, which we have as ecpg. SQL/Schemata contains the information schema. SQL/JRT appears to bear some similarity to PL/Java and PL/J. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] [HACKERS] New XML section for documentation
On Sat, Aug 26, 2006 at 12:48:32PM -0400, Bruce Momjian wrote: > David Fetter wrote: > > On Fri, Aug 25, 2006 at 08:37:19PM -0400, Bruce Momjian wrote: > > > > Speaking of other parts of the SQL:2003 standard, how about one > > > > section each that mentions them? There's > > > > > > > > Part 4: SQL/PSM (Persistent Stored Modules) > > > > Part 9: SQL/MED (Management of External Data) (my favorite) > > > > Part 10: SQL/OLB (Object Language Binding) > > > > Part 11: SQL/Schemata > > > > Part 13: SQL/JRT (Java Routines and Types) > > > > > > I don't know anything about them. > > > > We claim SQL standard compliance, so since those are part of > > SQL:2003, we probably ought to mention them. SQL/PSM is a > > programming language that lives inside the database, and DB2 and > > MySQL have it. SQL/MED lets people talk to other data stores. > > SQL/OLB appears to be derived from equel, which we have as ecpg. > > SQL/Schemata contains the information schema. SQL/JRT appears to > > bear some similarity to PL/Java and PL/J. > > I think the big question is whether we are ever going to implement > these? I think we need to decide that before I mention them. The SQL/Schemata thing is already in. I think we should at least mention which features that we already have are from what part of the standard. As far as the rest of the standard goes, we might want to mention whether we've even considered any of each piece in the TODO list, and what sub-pieces, if any, are already included/scheduled/too silly to contemplate :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] [HACKERS] New XML section for documentation
On Sat, Aug 26, 2006 at 01:16:06PM -0400, Bruce Momjian wrote: > David Fetter wrote: > > On Sat, Aug 26, 2006 at 12:48:32PM -0400, Bruce Momjian wrote: > > > David Fetter wrote: > > > > On Fri, Aug 25, 2006 at 08:37:19PM -0400, Bruce Momjian wrote: > > > > > > > > Speaking of other parts of the SQL:2003 standard, how about one > > > > > > section each that mentions them? There's > > > > > > > > > > > > Part 4: SQL/PSM (Persistent Stored Modules) > > > > > > Part 9: SQL/MED (Management of External Data) (my favorite) > > > > > > Part 10: SQL/OLB (Object Language Binding) > > > > > > Part 11: SQL/Schemata > > > > > > Part 13: SQL/JRT (Java Routines and Types) > > > > > > > > > > I don't know anything about them. > > > > > > > > We claim SQL standard compliance, so since those are part of > > > > SQL:2003, we probably ought to mention them. SQL/PSM is a > > > > programming language that lives inside the database, and DB2 and > > > > MySQL have it. SQL/MED lets people talk to other data stores. > > > > SQL/OLB appears to be derived from equel, which we have as ecpg. > > > > SQL/Schemata contains the information schema. SQL/JRT appears to > > > > bear some similarity to PL/Java and PL/J. > > > > > > I think the big question is whether we are ever going to implement > > > these? I think we need to decide that before I mention them. > > > > The SQL/Schemata thing is already in. I think we should at least > > Uh, what is the SQL/Schemata? Are you sure it is in CVS? It contains the information schema, among other things. We've had the information schema for awhile. :) > > mention which features that we already have are from what part of > > the standard. As far as the rest of the standard goes, we might > > want to mention whether we've even considered any of each piece in > > the TODO list, and what sub-pieces, if any, are already > > included/scheduled/too silly to contemplate :) > > Well, this seems like something that belongs in our chapter on how > we support the SQL standard. I'm not too fussy about where it first goes in. Just *that* it goes in somewhere. I'll be happy to start the needed patches. :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] [HACKERS] New XML section for documentation
On Sat, Aug 26, 2006 at 08:38:43PM +0200, Peter Eisentraut wrote: > David Fetter wrote: > > We claim SQL standard compliance, > > No, we don't. And SQL conformance doesn't require you to implement > all parts anyway. Right. It'd be nice to be able to tell what level of conformance we have to which parts of the standard. > > so since those are part of SQL:2003, we probably ought to mention > > them. SQL/PSM is a programming language that lives inside the > > database, and DB2 and MySQL have it. SQL/MED lets people talk to > > other data stores. SQL/OLB appears to be derived from equel, > > which we have as ecpg. SQL/Schemata contains the information > > schema. SQL/JRT appears to bear some similarity to PL/Java and > > PL/J. > > It's pretty useless to talk about stuff that we don't have yet. I think it's useful to mention what's arriving, what's being worked on, and what's not even being contemplated in the long term. > The point of the XML section is that we have a number of things, and > users are having trouble (understandably) fitting them together. Similar troubles apply--on a smaller scale--to the information schema, SQL/OLB, SQL/JRT, etc. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[DOCS] Predicate Locking
Folks, This patch clarifies the 'predicate locking' section in the docs. Thanks to Harrison Fisk of MySQL AB for helping. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Index: doc/src/sgml/mvcc.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v retrieving revision 2.57 diff -c -r2.57 mvcc.sgml *** doc/src/sgml/mvcc.sgml 25 Aug 2006 04:06:45 - 2.57 --- doc/src/sgml/mvcc.sgml 1 Sep 2006 17:28:28 - *** *** 471,477 result in problems. (Certainly the example above is rather contrived and unlikely to represent real software.) Accordingly, PostgreSQL does not implement predicate ! locking, and so far as we are aware no other production DBMS does either. --- 471,480 result in problems. (Certainly the example above is rather contrived and unlikely to represent real software.) Accordingly, PostgreSQL does not implement predicate ! locking. No system based on next-key locking implements it ! either because next-key locking only helps when all your ! predicates are point or range searches against an available ! B-tree index. DB2, however, does implement predicate locking. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[DOCS] Building Documentation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All: I've been trying to build the documentation for PostgreSQL 8.1.4 for the past few days without much success. First of all, it seems openjade has some severe performance issues. I ran: sgml#gmake postgres.rtf and let it run all night, but nothing had been written to the file. The CPU had been pegged the entire time. On a different box, I let: sgml#gmake postgres.dvi run all night, and it got to 22MB. After seeing this lackluster performance, I tried using XSLT instead of DSSSL. I ran: sgml#gmake postgres.xml sgml#gmake testxml This ran through without error in about 10 minutes. I then added: XSLTPROCFLAGS = -o postgres.fo to the Makefile, so that xsltproc would dump the XSL:FO file for further processing. This generates the command line (watch the wrap): xsltproc -o postgres.fo --stringparam pg.version '8.1.4' stylesheet.xsl postgres.xml However, no .fo file is created. The process runs with a lot of "ID recommended on ...". It runs through the entire document, but no .fo file is created. After browsing through the -doc list, I see mentions of moving to the XML version of DocBook. After the escapades with openjade, I heartily agree with this decision. Meanwhile, can someone provide me with either postgres.rtf or postgres.fot from the 8.1 series? David Blewett The software versions I'm using are as follows: openjade:I: "openjade" version "1.3.2" openjade:I: "OpenSP" version "1.5.2" Using libxml 20626, libxslt 10117 and libexslt 813 xsltproc was compiled against libxml 20626, libxslt 10117 and libexslt 813 libxslt 10117 was compiled against libxml 20626 libexslt 813 was compiled against libxml 20626 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFJph6Zmlc6wNjtLYRAuKPAJ4iYTzGne4n287TCn7a3uTmY07DbwCgiIfW U3EH7oEDpsAu2dUTwvtySRk= =NFwW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Building Documentation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Eisentraut wrote: > > That's because the stylesheet you are using is an html stylesheet. > >> Meanwhile, can someone provide me with >> either postgres.rtf or postgres.fot from the 8.1 series? > > You can build a postgres.fot from the makefile, but maybe you mean > postgres.fo, which is not quite the same thing, and there is no readily > available support for the latter. > Heh, that goes to show how little I know about this whole process. I went ahead and restarted the build process and I'll let it run over the weekend. I also tried the route of using dblatex which will allow me to use latex utilities. So far, the sgml->tex process was quick but the pdflatex process is taking forever. It stopped spewing info to the screen, so it might have hit an infinite loop. If I am able to get something working well, I'll let you know. David -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFJsTtZmlc6wNjtLYRAo3HAJ0QOokK1Llqj+vmmIQN+/5oMbZ4YwCdFNLt +w16ga7eIDg9Gljng3KuiuM= =YiqV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[DOCS] 8.1.5 Release Notes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All: Just noticed that the release notes section for 8.1.5 has this heading in it: "E.1.1. Migration to version 8.1.4". David -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFPQH5Zmlc6wNjtLYRArYOAKCaAJ+y/QQRRmMPFpbcFC2joPj4kACeJ3Lf GIwu7eJKPIQLPFVn5bEIA9I= =fmUT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] [HACKERS] Replication documentation addition
On Wed, Oct 25, 2006 at 11:38:11AM +0200, Markus Schiltknecht wrote: > Can we name the chapter "Fail-over, Load-Balancing and Replication > Options"? That would fit everything and contain the necessary buzz words. ... > IMHO, it does not make sense to speak of a synchronous replication for a > 'Shared Disk Fail Over'. It's not replication, because there's no replica. As you point out, there is no replica of the data, but there is some protection against machine failure, which puts it firmly in the "Fail-over" part above. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Documentation update for PQexecParams
On Wed, Nov 08, 2006 at 08:07:19PM +0200, Theo Kramer wrote: > Hi > > I have been using PQprepare() and PQexecPrepared(). This has led me to > update libpq.sgml, in particular for PQexecParams(). > > The changes are to the formatting of the arguments for PQexecParams() > (as shared by PQexecPrepared()) which make it easier to read, as well as > creating a table giving a set of possible values for the various arguments > with > the associated SQL types. > > I would imagine the best place to submit the patch would be to > psql-patches, however, before I do this I am wondering if this is the right > forum for > having the changes vetted before submitting the patch. > > Please advise. Send it to both lists, and thanks for doing this :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [DOCS] [HACKERS] pg_dump -t broken for mixed case table names in beta3?
On Tue, Nov 28, 2006 at 02:43:17PM -0500, Tom Lane wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > Unless I'm missing something, pg_dump is not allowing selective dump of > > a table where the table name is mixed case. > > You do it like this: > > $ pg_dump -s -t '"DbTranLogRecord"' dtr > > A bit ugly but the conflict between shell and SQL quoting rules > sometimes forces us into compromises :-( > > Perhaps it's worth having an example for this in the pg_dump man page? Please find enclosed a patch which adds this :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Index: doc/src/sgml/ref/pg_dump.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.91 diff -c -r1.91 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 23 Oct 2006 18:10:32 - 1.91 --- doc/src/sgml/ref/pg_dump.sgml 28 Nov 2006 22:50:15 - *** *** 805,810 --- 805,818 +To dump a single table named camelCaseTable: + + + $ pg_dump -t '"camelCaseTable"' mydb > db.sql + + + + To dump all tables whose names start with emp in the detroit schema, except for the table named employee_log: ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[DOCS] Switching to XML
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All: I read a thread from July explaining the current status of moving to XML [1]. Peter, you mention that if someone has a clear need you would be open to switching. Let me explain the problems I've encountered with SGML. A few months ago, I split up the 8.1 pdf into 2 volumes so that I could get a hardbound copy from Lulu. You can get those here [2-3] (I'm not making any money, it's the pure cost of production). For 8.2, I wanted to go deeper and be able to distribute the final version to bookstores. In order to do that, each volume needs to be under 700 pages. I started work on modifying the SGML to use a set of 3 volumes, split at roughly 500 page intervals. I wanted to generate individual ToC's and indexes for each volume. I started to modify the SGML to include a "role" attribute for each indexterm, to tell what volume it was part of. This was done with a simple sed script. When I went to generate the actual indexes, I hit a brick wall. Apparently, the SGML toolchain cannot handle typed indexes as described here [4]. Only the XML toolchain currently handles them. I am working with Joshua Drake on creating a Lulu account for the fundraising group. We would then move the volumes I did for 8.1 to their account, and raise the price so that any profit went to them. The ability to order the 8.2 manual from bookstores could increase the visibility of the project as a whole. David Blewett 1. http://groups.google.com/group/pgsql.docs/browse_thread/thread/37ff3a011bb705d7 2. http://www.lulu.com/content/455020 3. http://www.lulu.com/content/464949 4. http://www.xml.com/pub/a/2004/07/14/dbndx.html?page=3 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFeXIOZmlc6wNjtLYRAo2yAKCZT1NbsklCd8djADdv48MuLELG7wCfYYG3 ypUw8LU3g++GaY6Dz2vUi+I= =lC+c -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Switching to XML
On Sat, Dec 09, 2006 at 09:21:12AM +0100, Guillaume Lelarge wrote: > Joshua D. Drake a écrit : > > On Fri, 2006-12-08 at 21:58 +0100, Peter Eisentraut wrote: > >> Joshua D. Drake wrote: > >>> You can create, edit, convert, save, and open docbook xml in > >>> OpenOfice.org. > >> Sure, there are more editing options with DocBook XML. No one disputes > >> that. But the question at hand was about processing the DocBook. > > > > Yes which is generated from our use of SGML which is the core of this > > problem and the core of the question as a whole. > > > > SGML is making working with the documentation *harder*. > > +1 Thanks to Peter and Tom for making the PDF build faster, but the more general problem, which is that the SGML does not actually do the same things that XML does, no matter how many times Peter so asserts, remains. In addition to the long-standing problem that there is no way to edit the SGML docs with any known GUI tool, we have a particular use case, namely producing a multi-volume set suitable for printing as books. > > We have people that *DO NOT* contribute because of this SGML > > requirement. They have what I consider extremely valid reasons, > > namely it is dumb to require a writer to use emacs or write tags > > explictly. Peter, if you have a working example of a GUI tool that can be used with the SGML source in its current form, the burden of proof is on you to demonstrate it. Another flat assertion from you of some kind of mathematical equivalence between SGML and XML will *not* do the trick. > > Hell, the only reason I have even bothered to contribute what > > little I have to the docs is because I wrote a book in SGML, thus > > it is a no brainer to me. Others aren't so tortured as to have > > done the same. > > I'm not so sure it will help you find more contributors. I'm part of > a project which aims to translate HOWTO from TLDP. They don't find > contributors and we too have really hard times to find contributors > despite the fact we try to only use DocBook XML (TLDP use DocBook > SGML, DocBook XML and LinuxDoc formats). > > Did you try to use OpenOffice.org with DocBook ? I tried once and it > was a complete disaster. But it was a long time ago. I will try > again this week-end. I gave it a try post-patches, and it's still a disaster 3284 pages of un-rendered XML. > > There is a long standing support within the community to move to XML > > including: > > > > Josh Berkus > > Josh Drake > > Robert Treat > > Andrew Dunslane > > David Blewett > > David Fetter > > Devrim Gunduz > > Darcy Buskermolen > > > > And that is just from #postgresql > > > > The french team also uses Docbook XML and they can generate a PDF > > in 30 minutes... it takes us DAYS because of the SGML. > > In fact, we need 15 minutes to build HTML files and 10 minutes to > build PDF file. To be completely honest, I don't seem to be able to > build PDF file for 8.2.0 release. I must have made a mistake (or > perhaps a lot of :) ). It'll be nice to have the document building cycle shorter, but the point here is that we need to enter the 21st century. That Tom found a need to fork a document tool, i.e. take ownership of a whole large piece of software, that being what forking means, is a neon sign that means, "we're stuck with broken tools." Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [DOCS] Switching to XML
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Peter Eisentraut wrote:
> Right. So using the XSL(T) stylesheet instead of the DSSSL stylesheet would
> be a step in the right direction. Actually the 8.2 branch currently only has
> an XSLT stylesheet for HTML output. In 8.3devel I've added one for XSL-FO
> output, which you can invoke by "make postgres-{A4|US}.fo". (You will
> probably want yet a different paper format for your book.)
Excuse my ignorance, but I thought XSLT stylesheets can only be used
on XML files?
David Blewett
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFFfZ8KZmlc6wNjtLYRCEsTAJ0YF/5/enV2w7ibvjSYENTdKjZs3QCgophl
tZVTZsrFceHD3f+KKn6Drqo=
=zhwv
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [DOCS] Switching to XML
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> Secondly the make postgres.xml would be a one time thing. > > Agreed, the cost of conversion is one-time ... but it's not small. > Aside from getting the files themselves converted, there's the effort > for people to find, install, and learn suitable tools, not to mention > come up to speed on the differences between xml and sgml. (I assume > there are some significant ones, else why are we having this discussion?) > > The real problem here is that you've still failed to establish any > sizable benefit from converting. As best I can tell at the moment, > the acceptable options for editing XML will be about the same as they > are for SGML: emacs, and not a lot else. I don't really see why I > should have to start spelling out every closing tag for a no-op like > that. > > regards, tom lane In regards to cost of conversion: The osx tool that the makefile uses to generate postgres.xml has more options available to tweak the output (not resolve external files into the main file, no newlines in tags, etc.) After adding a few of those options + htmltidy, the result is pretty close to the original SGML, barring a bare newline between 's for example. The benefit I'm talking about is the ability to do multiple indexes in one document, which is not possible with DSSSL. In order to do this, I would have to run the conversion *every time* I wanted to work on a new release of the manual to bookstores. David Blewett -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFfci5Zmlc6wNjtLYRCFYwAJ45TMs/YY0RoxHi+oMCB7shPGghlQCfa+oA hDRctNczvnrFweSV4yI2lG0= =2NoW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] Switching to XML
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Tom Lane wrote: > Yeah, but for purely downstream work like that, what's the objection to > just running the osx conversion? It seems to take just a few seconds. > > I'm still not seeing where we get return on our investment for > converting the master source files from one format to the other. > > regards, tom lane Does the designation of "downstream work" apply here? I was intending to contribute back any changes required, to enable anyone else to build the multiple volume version for printing. I wanted it to become an easily maintainable way for the project to have an up-to-date version of our manual available to bookstores, or directly available through lulu.com. I also would like to reiterate the fact that the localization efforts would appreciate not having to re-do their work for each release. Do we really want to continue considering this addition to the project as well as their work to be "second-class citizens" wrt the rest of the distribution? David Blewett -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFfgg/Zmlc6wNjtLYRCK9pAKCtN+UDuiYMprloV6Kx9XM4X3jCXQCgvhZ3 sRgVSsSAwUhHhQ2aMMC4ehA= =XA3B -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Authoring Tools WAS: Switching to XML
On Thu, Dec 14, 2006 at 04:34:06PM -0500, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > On Thu, 2006-12-14 at 14:58, Josh Berkus wrote: > >> It would be more accurate to say that we have not identified a > >> WYSWYG tool which does not mess up the source. There may be one, > >> it would just take a fair amount of testing to find it. > > > Is this strictly a question of indentation, or one of actually > > mangling tags and such? > > What we need is something that does not change regions of the file > that the user did not intend to modify. I think > horizonal-white-space-only changes could be worked around if the > user is careful to use diff --ignore-space-change when submitting > the patch, but I suspect that an editor that thinks it can mangle > whitespace will also figure that it can change line boundaries, and > then diff will never be able to extract any signal from that noise. Could post-processing with tidy clean this up? I'm pretty sure that tidy's output is deterministic, at least if the editor hasn't mangled tags... Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [DOCS] Switching to XML
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Tom Lane wrote: > However, I have no idea what it'll take to get this patch propagated > into the copies people actually use, so your fix sounds good for the > short term. > > regards, tom lane I submitted the patch to gentoo's bugzilla: http://bugs.gentoo.org/show_bug.cgi?id=158605 David Blewett -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFieRjZmlc6wNjtLYRCH2GAJ4rfMXrcPAceJoL5rTPjmpY9EAndACcCfes oxBVK5jqghkxKVXEbwRD8m8= =bR1k -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Switching to XML
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 David Blewett wrote: > Tom Lane wrote: >> However, I have no idea what it'll take to get this patch propagated >> into the copies people actually use, so your fix sounds good for the >> short term. > >> regards, tom lane > > > I submitted the patch to gentoo's bugzilla: > http://bugs.gentoo.org/show_bug.cgi?id=158605 > > David Blewett > Hi Tom: There's been a question posted to that bug. Would you mind replying to it? You don't have to create an account there. I can post your reply. David Quoted, [EMAIL PROTECTED]: "Very interesting. I'd of course love to add a patch like this, however, it seems not so easy to follow. Not that the patch itself is overly complicated, but openjade sources are, let's say, less than ideal. If you or the original author of the patch could comment the changes, specially those in style/primitive.cxx regarding to the protect and protect2 objects, it would help a lot. Also, for future reference, please create your patches as unified diffs when submitting them to resources such as this bugzilla (many other projects encourage this as well). Thanks." -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFi7W0Zmlc6wNjtLYRCBUPAJ0X2y99yxnFsike34Vo+JHckPcI6wCfVzdc HuCmC6It6UvVa6a/5Z1QuGU= =fJ3G -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Fix misleading references to columns in GRANT/REVOKE summaries
Folks,
Per a question Alexey Parshin asked in the IRC channel, I'm attaching
a patch to the GRANT and REVOKE syntax summaries which replaces the
misleading word "column" with "parameter." "Column" is misleading
because it could be read to imply a column-level GRANT/REVOKE, which
we don't have yet.
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.64
diff -c -r1.64 grant.sgml
*** doc/src/sgml/ref/grant.sgml 1 Feb 2007 00:28:19 - 1.64
--- doc/src/sgml/ref/grant.sgml 6 Apr 2007 23:39:20 -
***
*** 525,531
GRANT privileges
! ON table [ ( column [, ...] ) ] [, ...]
TO { PUBLIC | username [,
...] } [ WITH GRANT OPTION ]
--- 525,532
GRANT privileges
! ON table [ (
! parameter [, ...] ) ] [, ...]
TO { PUBLIC | username [,
...] } [ WITH GRANT OPTION ]
Index: doc/src/sgml/ref/revoke.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.42
diff -c -r1.42 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml31 Jan 2007 23:26:04 - 1.42
--- doc/src/sgml/ref/revoke.sgml6 Apr 2007 23:39:20 -
***
*** 235,241
REVOKE [ GRANT OPTION FOR ] privileges
! ON object [ ( column [, ...] ) ]
FROM { PUBLIC | username [,
...] }
{ RESTRICT | CASCADE }
--- 235,242
REVOKE [ GRANT OPTION FOR ] privileges
! ON object [ (
! parameter [, ...] ) ]
FROM { PUBLIC | username [,
...] }
{ RESTRICT | CASCADE }
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[DOCS] Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Hello docs hackers, In my opinion it would be nice to mention in SQL reference commands that constraint triggers created by CREATE CONSTRAINT TRIGGER are fired in creation order unlike regular triggers created by command CREATE TRIGGER. As I know, the firing order of the CONSTRAINT TRIGGER is determined by the X num at "RI_ConstraintTrigger_X" column in pg_trigger table. Regards, David Pufer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Michael Glaesemann wrote: > > On Apr 21, 2007, at 4:39 , David Pufer wrote: > >> In my opinion it would be nice to mention in SQL reference commands that >> constraint triggers created by CREATE CONSTRAINT TRIGGER are fired in >> creation order unlike regular triggers created by command CREATE >> TRIGGER. As I know, the firing order of the CONSTRAINT TRIGGER is >> determined by the X num at "RI_ConstraintTrigger_X" column in >> pg_trigger table. > > I believe this just falls out of the normal order of trigger firing as > explained in the CREATE TRIGGER documentation > (http://www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html) > >> If multiple triggers of the same kind are defined for the same event, >> they will be fired in alphabetical order by name. > > It might be nice to add a similar phrase to the CREATE CONSTRAINT > TRIGGER documentation, but as far as I know, the behavior is the same > for triggers created by either method. The only difference is that > constraint triggers are named by the system rather than by the user. > > Thoughts? > > Michael Glaesemann > grzm seespotcode net > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend You are right. Thanks for the note of system name that are assigned by the server by incrementing the num at "RI_ConstraintTrigger_NUM" at each constraint trigger creation. Regards, David Pufer ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Tom Lane wrote: > Michael Glaesemann <[EMAIL PROTECTED]> writes: > >> On Apr 21, 2007, at 4:39 , David Pufer wrote: >> >>> In my opinion it would be nice to mention in SQL reference commands that >>> constraint triggers created by CREATE CONSTRAINT TRIGGER are fired in >>> creation order unlike regular triggers created by command CREATE >>> TRIGGER. As I know, the firing order of the CONSTRAINT TRIGGER is >>> determined by the X num at "RI_ConstraintTrigger_X" column in >>> pg_trigger table. >>> > > >> I believe this just falls out of the normal order of trigger firing >> as explained in the CREATE TRIGGER documentation (http:// >> www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html) >> > > The proposed change is wrong anyway: AFAICS from looking at the code, > an RI_ConstraintTrigger_X name is only generated for triggers that > are created in support of FOREIGN KEY constraint syntax. If you use > CREATE CONSTRAINT TRIGGER then the trigger name is whatever you say it > is. I don't recall if it was always like that, but that's definitely > the behavior in CVS HEAD. > > It would be a mistake to assume that FK constraint triggers are fired in > creation order anyway, since the lexical sorting of strings isn't the > same as numeric order (not to mention OID wraparound issues). > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > I tested in in 8.2.3 stable. e.g. command CREATE CONSTRAINT TRIGGER my_constraint_trigger_name AFTER INSERT OR UPDATE OR DELETE ON my_test_table DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE my_test_procedure() created a row with tgname "RI_ConstraintTrigger_28329" and tgconstrname "my_constraint_trigger_name" in system catalog in table pg_trigger, while creation of normal trigger (not constraint trigger) puts its name into tgname column. As Tom notedm a while ago it might be different in CVS HEAD and consequently in 8.3. Thanks for helpful info David Pufer
[DOCS] OS/X startup scripts
Folks, The attached tarball should be unpacked in contrib/startup-scripts, and supplants PostgreSQL.darwin and StartupParameters.plist.darwin. Thanks to Mark Cotner of Reflectr <http://pgfoundry.org/projects/reflectr/> fame for this update :) I haven't included the customary diffs. This points me to some of the many deficiencies of CVS, namely that I would need write access in order to have it create a diff, and write access is boolean, which means that I can't get write access only to the parts of the tree that make sense for me to have write access to. What say we consider using Git, which includes a CVS interface, starting after 8.3 gets out the door? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate PostgreSQL_startup.tgz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Autovacuum and XID wraparound
Folks, Per Neil Conway, here's some doc patches re: the autovacuum daemon's behavior. Should this be back-patched to 8.2x? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.122 diff -c -r1.122 config.sgml *** doc/src/sgml/config.sgml20 Apr 2007 02:37:37 - 1.122 --- doc/src/sgml/config.sgml14 May 2007 01:16:02 - *** *** 3172,3177 --- 3172,3185 This parameter can only be set in the postgresql.conf file or on the server command line. + + + Even when this variable is set to off, the autovacuum daemon + will run periodically in order to prevent transaction_id + wraparound. See for + more information. + + Index: src/backend/utils/misc/postgresql.conf.sample === RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.215 diff -c -r1.215 postgresql.conf.sample *** src/backend/utils/misc/postgresql.conf.sample 18 Apr 2007 16:44:18 - 1.215 --- src/backend/utils/misc/postgresql.conf.sample 14 May 2007 01:16:02 - *** *** 372,377 --- 372,379 #--- # AUTOVACUUM PARAMETERS #--- + # Note: even when autovacuum is turned off, the autovacuum daemon will + # run in order to prevent transaction id wraparound. #autovacuum = on # enable autovacuum subprocess? # 'on' requires stats_start_collector ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [DOCS] OS/X startup scripts
On Sun, May 13, 2007 at 07:04:44PM -0400, Andrew Dunstan wrote: > > > Tom Lane wrote: > >David Fetter <[EMAIL PROTECTED]> writes: > > > >>I haven't included the customary diffs. This points me to some of > >>the many deficiencies of CVS, namely that I would need write > >>access in order to have it create a diff, > > > >Strange, it works fine for everyone else. > > Especially if you have cvsutils installed (can be found in many > places including fedora extras). I didn't know about those, so I'll prepare a patch to the developer and documenter docs that mentions this utility :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[DOCS] On patching without write access to CVS
Folks, Thanks to Andrew Dunstan for pointing me toward cvsutils. As not everybody knows about them, here's a small patch which lets people know at least in theory where they are. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate Index: doc/FAQ_DEV === RCS file: /projects/cvsroot/pgsql/doc/FAQ_DEV,v retrieving revision 1.135 diff -c -r1.135 FAQ_DEV *** doc/FAQ_DEV 5 May 2007 14:33:55 - 1.135 --- doc/FAQ_DEV 14 May 2007 01:56:11 - *** *** 108,114 work. Failure to do so might mean your patch is rejected. If your work is being sponsored by a company, read this article for tips on being more effective. ! A web site is maintained for patches awaiting review, http://momjian.postgresql.org/cgi-bin/pgpatches, and those that are being kept for the next release, --- 108,120 work. Failure to do so might mean your patch is rejected. If your work is being sponsored by a company, read this article for tips on being more effective. ! !To create patches which would otherwise require that you have write !access to the CVS repository, for example ones that add or remove !files, you can use cvsutils. The cvsutils toolchain is packaged !for many operating systems and available in source form at !http://www.red-bean.com/cvsutils/ ! A web site is maintained for patches awaiting review, http://momjian.postgresql.org/cgi-bin/pgpatches, and those that are being kept for the next release, Index: doc/src/sgml/cvs.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/cvs.sgml,v retrieving revision 1.42 diff -c -r1.42 cvs.sgml *** doc/src/sgml/cvs.sgml 27 Mar 2007 01:45:22 - 1.42 --- doc/src/sgml/cvs.sgml 14 May 2007 01:56:11 - *** *** 15,20 --- 15,24 Thomas Lockhart + + David + Fetter + 1999-05-20 *** *** 150,155 --- 154,166 comes with CVS, or see the online documentation at http://www.nongnu.org/cvs/";>. + +For those things which CVS does not do +by itself, such as letting you create patches without write access, +you can use cvsutils, which is packaged +for many operating systems, or available in source form at http://www.red-bean.com/cvsutils/";>. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] OS/X startup scripts
On Sun, May 13, 2007 at 09:51:53PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Sun, May 13, 2007 at 07:04:44PM -0400, Andrew Dunstan wrote: > >> Tom Lane wrote: > >>> Strange, it works fine for everyone else. > >> > >> Especially if you have cvsutils installed (can be found in many > >> places including fedora extras). > > > I didn't know about those, so I'll prepare a patch to the > > developer and documenter docs that mentions this utility :) > > I dunno what cvsutils is, but I do know that plain old "cvs diff" > works fine whether you have commit privs or not. Rather than > preparing a patch to our docs, perhaps you should spend some time > reading the CVS docs. cvs diff works just great until you want to add or remove a file without write permissions to the CVS repository, i.e. when you've checked out as anonymous. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Autovacuum and XID wraparound
On Sun, May 13, 2007 at 10:06:40PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Per Neil Conway, here's some doc patches re: the autovacuum > > daemon's behavior. Should this be back-patched to 8.2x? > > This fact is already documented in at least three places; do we > really need two more? Yes. > The proposed addition to postgresql.conf seems particularly > over-the-top, since there is no entry in that file that even > pretends to offer a complete description of the associated behavior. I think that a boolean that doesn't do what you expect booleans to do, i.e. turn the thing all the way off, is worth a mention. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [DOCS] OS/X startup scripts
On Mon, May 14, 2007 at 03:31:40PM +1200, Mark Kirkwood wrote: > David Fetter wrote: > >cvs diff works just great until you want to add or remove a file > >without write permissions to the CVS repository, i.e. when you've > >checked out as anonymous. > > > > I usually saved an untouched version of the tree to compare against, > so something like: > > $ cvs diff -Nacr pgsql.orig pgsql > > gives a complete patch including added/deleted files. It is a bit > primitive, but is pretty easy to do! It's great that we have all this knowledge of how to deal with CVS's deficiencies, but at the moment, it amounts to magical lore. Until we get something better to replace it, they should be part of the docs. :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] OS/X startup scripts
On Wed, May 16, 2007 at 09:12:23AM +0100, Heikki Linnakangas wrote: > Jim C. Nasby wrote: > >BTW, is there some trick to getting cvs diff to ignore files that > >aren't in the repo? > > Trick? That's what it does by default. I suspect he's talking about all the lines starting with '?' that diff produces. Lacking sophistication, I've been known to do: cvs diff [list of files here] |grep -v '^?' > the_file.diff Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] OS/X startup scripts
On Wed, May 16, 2007 at 03:53:22PM +0100, Gregory Stark wrote: > "David Fetter" <[EMAIL PROTECTED]> writes: > > > On Wed, May 16, 2007 at 09:12:23AM +0100, Heikki Linnakangas wrote: > >> Jim C. Nasby wrote: > >> >BTW, is there some trick to getting cvs diff to ignore files > >> >that aren't in the repo? > >> > >> Trick? That's what it does by default. > > > > I suspect he's talking about all the lines starting with '?' that > > diff produces. > > > > Lacking sophistication, I've been known to do: > > > > cvs diff [list of files here] |grep -v '^?' > the_file.diff > > Those lines go to stderr. Not when I do "cvs diff." Is there something I should (un)set in my .cvsrc? > If you do "cvs diff > file" it spits out all the cvs file statuses > to the terminal but dumps the diff to the file. > > It doesn't matter, diffs can contain arbitrary junk between the file > diffs. patch only looks at the things it recognizes. IMHO, the diffs also need to be human-readable, and 500 useless lines starting with "?" don't help with that. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] [pgsql-www] Users comments don't migrate to docs for new version?
On Wed, Jun 06, 2007 at 09:18:29PM +0200, Magnus Hagander wrote: > Alvaro Herrera wrote: > > Nikolay Samokhvalov wrote: > >> I've just found (via google...) useful (for me) comments: > >> http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html > >> -- I mean users comments under primary contents of the page. > >> > >> These comments were written for 8.0, but are useful for the > >> current versions too. What we have is the situation that reading > >> docs for 8.2 people don't see these comments -- so this > >> potentially useful information is lost. So, in this > >> implementation the idea of comments for docs becomes weak in > >> general. > > > > The thing to do in these cases is to move the useful info from the > > user comments into the main doc text. > > Yeah, that's the idea. Tom often goes through the comments and puts > stuff into the docs. But I'm sure there are a *lot* of other people > who could help with that as well - read comments, figure out of they > make sense, and submit a docs patch for the next version! It occurs to me that we could encourage this by emphasizing on the interactive docs that user comments are not carried forward automatically and pointing to a place where people can learn how to submit such patches if they so desire. Here is the wording I'd propose to replace the paragraph after "Add Comment": Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. These comments are not automatically carried into future versions of the documentation. If you believe your change should carry forward, please consider http://www.postgresql.org/docs/current/static/docguide.html";>patching the document source SGML and sending those patches to mailto:[email protected]";>psql-docs for inclusion in the official documentation. This is a way to participate more in the PostgreSQL project. This is not a support forum, and your IP address will be logged. If you have a question or need help, please see the http://www.postgresql.org/docs/faq/>FAQ, try a mailing http://www.postgresql.org/community/lists/";>list, or join us on IRC. Submissions containing URLs or other keywords commonly found in spam comments may be silently discarded. Please contact the mailto:[EMAIL PROTECTED]">webmaster if you think this is happening to you in error. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
[DOCS] proposed FAQ entry
Earlier today I was tempted to start storing usesysid's as foreign keys in one of my tables by doing: SELECT usesysid FROM pg_user WHERE pg_user.usename= user; My proposed FAQ entry is based on this mailing list post which convinced me not to: http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.php So the Question would be: Q) How do I keep track of edits to a table? OR Can I use store usesysid in my table to identify users? A) No because usesysid values are not guaranteed to remain the same after a restore, or upgrade. Instead store the user name as text, if needed create your own user table and associate the user names with integer keys. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] proposed FAQ entry
Well the motivation for the question, is to want to track edits to the database. To know who changed what/when, and possibly run a report against that information. I looked through the docs to find the user and session_user variables at: http://www.postgresql.org/docs/8.1/interactive/functions-info.html It just seemed natural to think there must be some kind of integer key associated with the user. Maybe this doesn't qualify as frequent. Magnus Hagander wrote: On Thu, Jun 28, 2007 at 09:06:20PM -0400, Bruce Momjian wrote: David Gardner wrote: Earlier today I was tempted to start storing usesysid's as foreign keys in one of my tables by doing: SELECT usesysid FROM pg_user WHERE pg_user.usename= user; My proposed FAQ entry is based on this mailing list post which convinced me not to: http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.php So the Question would be: Q) How do I keep track of edits to a table? OR Can I use store usesysid in my table to identify users? A) No because usesysid values are not guaranteed to remain the same after a restore, or upgrade. Instead store the user name as text, if needed create your own user table and associate the user names with integer keys. Uh, sorry, this is not a _frequent_ question/issue. If it's not frequent, perhaps it should at least go in the documentatino somewhere? (I confess I haven't checked if it's actually there already) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- David Gardner ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] rename of a view
On Thu, Jun 28, 2007 at 10:16:51AM -0400, Tom Lane wrote: > Susanne Ebrecht <[EMAIL PROTECTED]> writes: > > that works, but there is no hint at the documentation, that you > > can rename a view via alter table. > > It is mentioned someplace (don't remember where). Where would you > have expected to find it? I'd expect to find it in an ALTER VIEW document. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] rename of a view
On Fri, Jun 29, 2007 at 11:32:19AM -0700, David Fetter wrote:
> On Thu, Jun 28, 2007 at 10:16:51AM -0400, Tom Lane wrote:
> > Susanne Ebrecht <[EMAIL PROTECTED]> writes:
> > > that works, but there is no hint at the documentation, that you
> > > can rename a view via alter table.
> >
> > It is mentioned someplace (don't remember where). Where would you
> > have expected to find it?
>
> I'd expect to find it in an ALTER VIEW document.
>
> Cheers,
> D
The attached patch and file implement and document
ALTER [VIEW | SEQUENCE] RENAME TO
The file goes in doc/src/sgml/ref and the patch should just apply to
CVS HEAD.
Thanks to Neil Conway for all the help putting this together :)
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
ALTER VIEW
SQL - Language Statements
ALTER VIEW
change a view
ALTER VIEW
ALTER VIEW name RENAME TO newname
Description
ALTER VIEW changes the definition of a
view.
You must own the view to use ALTER VIEW.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have CREATE privilege on
the view's schema. (These restrictions enforce that altering the
owner doesn't do anything you couldn't do by dropping and recreating the
view. However, a superuser can alter ownership of any view
anyway.)
Parameters
name
The name (optionally schema-qualified) of an existing view.
newname
The new name of the view.
Examples
To rename the view foo to
bar:
ALTER VIEW foo RENAME TO bar;
Compatibility
There is no ALTER VIEW statement in the SQL
standard.
See Also
Index: doc/src/sgml/reference.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/reference.sgml,v
retrieving revision 1.63
diff -c -r1.63 reference.sgml
*** doc/src/sgml/reference.sgml 26 Apr 2007 16:13:08 - 1.63
--- doc/src/sgml/reference.sgml 30 Jun 2007 00:14:47 -
***
*** 53,58
--- 53,59
&alterTrigger;
&alterType;
&alterUser;
+&alterView;
&analyze;
&begin;
&checkpoint;
Index: doc/src/sgml/ref/allfiles.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v
retrieving revision 1.70
diff -c -r1.70 allfiles.sgml
*** doc/src/sgml/ref/allfiles.sgml 26 Apr 2007 16:13:08 - 1.70
--- doc/src/sgml/ref/allfiles.sgml 30 Jun 2007 00:14:47 -
***
*** 25,30
--- 25,31
+
Index: doc/src/sgml/ref/alter_sequence.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_sequence.sgml,v
retrieving revision 1.16
diff -c -r1.16 alter_sequence.sgml
*** doc/src/sgml/ref/alter_sequence.sgml31 Jan 2007 23:26:02 -
1.16
--- doc/src/sgml/ref/alter_sequence.sgml30 Jun 2007 00:14:47 -
***
*** 29,34
--- 29,35
[ RESTART [ WITH ] start ] [
CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name SET SCHEMA
new_schema
+ ALTER SEQUENCE name RENAME TO
new_name
***
*** 190,195
--- 191,207
+
+
+ RENAME TO new_name
+
+
+ The RENAME TO option causes the sequence to be
+ renamed.
+
+
+
+
Index: src/backend/commands/alter.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/alter.c,v
retrieving revision 1.23
diff -c -r1.23 alter.c
*** src/backend/commands/alter.c26 Mar 2007 16:58:38 - 1.23
--- src/backend/commands/alter.c30 Jun 2007 00:14:47 -
***
*** 83,88
--- 83,90
break;
case OBJECT_TABLE:
+ case OBJECT_SEQUENCE:
+ case OBJECT_VIEW:
case OBJECT_INDEX:
case OBJECT_COLUMN:
case OBJECT_TRIGGER:
***
*** 96,101
--- 98,105
switch (stmt->renameType)
{
case OBJECT_TABLE:
+ case OBJECT_SEQUENCE:
+ case OBJECT_VIEW:
case
Re: [DOCS] rename of a view
On Sat, Jun 30, 2007 at 01:36:22AM -0400, Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > On Sat, 2007-30-06 at 00:26 -0400, Tom Lane wrote: > >> There is exactly 0 chance of that happening, because it's always > >> worked historically. > > > Agreed, but I think the patch should disallow ALTER VIEW ... > > RENAME on a non-view, and ALTER SEQUENCE ... RENAME on a > > non-sequence. > > No objection to that; it'd square with our treatment of TYPE and > DOMAIN commands. What I'm wondering though is whether the whole > patch has a reason to live at all, as compared to documenting > someplace more prominent than now that ALTER TABLE works on views & > sequences. How could it be prominent short of documentation of the thing people would expect, which is ALTER [SEQUENCE | VIEW] RENAME TO ... ? I suppose we could document that they're actually done by ALTER TABLE, but that just seems like a huge POLA violation, along with assuming way too much knowledge of how sequences and views are implemented. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] Printed Doc
Could this get added to: http://www.postgresql.org/docs/books/ ? Guillaume Lelarge wrote: > Andrej Ricnik-Bay a écrit : > >> On 8/5/07, RPK <[EMAIL PROTECTED]> wrote: >> >>> Is PGSQL doc available as a book? >>> >> There was a thread about this not long ago ... not sure >> whether on this list or on general; but I don't think it is. >> >> > > They are available at lulu.com. In a quick search, I found this : > http://www.lulu.com/content/863723 > > Be careful, this is 8.2.1 manual, not 8.2.4. Ans this is volume 1 of 3. > > Regards. > > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] PostgreSQL vs. Postgres labeling inconsistency
On Fri, Oct 05, 2007 at 07:22:01PM +0200, Peter Eisentraut wrote: > Some of you may have noticed that there was a move proposed to use > "Postgres" alongside "PostgreSQL" as a product name in the > documentation and other written resources. A change along that line > has already been made in the FAQ. > > Many points have been made recently on the name of the project or > the product, but the fact is that it will always be one or the other > at any particular time. It's fine to have alternative names. But > keep in mind that the purpose of documentation is to convey > information, not to make subtle points about naming issues. If you > want to make points about naming issues, write a nonsubtle document > about it. +1 > Others have also made points that it is OK to use acronyms in place > of the full name, and "Postgres" could be that, or that it's like > Coke vs Coca-Cola. > > Nevertheless, any writing resource or technical editor will tell you > that you need to be consistent. If you want to use an acronym, you > introduce it once, and then you use it all the time. And if you > write an article about beverages, you will use either Coke or > Coca-Cola throughout, not both. If the terminology or the acronyms > are not clear, you explain it at the beginning, and readers will > look it up there. > > I believe both the FAQ and the documentation do explain the naming > issue near the beginning. But the rest of the document should use > one name consistently, or it will just look silly and confusing. > Also consider that many of our written resources are not read > linearly, so it becomes even more important to use consistent > terminology that does not require much context to understand. > > So I think what is being proposed is wrong and needs to be reverted. That, or (my preference) make the change larger. I think it's significant that the vast majority of compatible software has some variant of Postgres and *not* PostgreSQL in the name. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] PostgreSQL vs. Postgres labeling inconsistency
On Fri, Oct 05, 2007 at 12:17:40PM -0700, Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > David Fetter wrote: > > On Fri, Oct 05, 2007 at 11:43:45AM -0700, Joshua D. Drake wrote: > >> -BEGIN PGP SIGNED MESSAGE- > >> Hash: SHA1 > >> > >> David Fetter wrote: > >>> On Fri, Oct 05, 2007 at 07:22:01PM +0200, Peter Eisentraut wrote: > >>>> Some of you may have noticed that there was a move proposed to > >>>> use "Postgres" alongside "PostgreSQL" as a product name in the > >>>> documentation and other written resources. A change along that > >>>> line has already been made in the FAQ. > >>>> > >>>> So I think what is being proposed is wrong and needs to be > >>>> reverted. > >>> That, or (my preference) make the change larger. I think it's > >>> significant that the vast majority of compatible software has some > >>> variant of Postgres and *not* PostgreSQL in the name. > >> I have seen no evidence of this. > > > > For evidence, take a look at the "Products" section of the Postgres > > Weekly News. > > I just read through all products sections in the PostgreSQL weekly news > from 07-22-07 to 09-30-07. Nice job of careful cherry-picking, but I'm not buying it. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] PostgreSQL vs. Postgres labeling inconsistency
On Fri, Oct 05, 2007 at 11:43:45AM -0700, Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > David Fetter wrote: > > On Fri, Oct 05, 2007 at 07:22:01PM +0200, Peter Eisentraut wrote: > >> Some of you may have noticed that there was a move proposed to > >> use "Postgres" alongside "PostgreSQL" as a product name in the > >> documentation and other written resources. A change along that > >> line has already been made in the FAQ. > >> > > >> So I think what is being proposed is wrong and needs to be > >> reverted. > > > > That, or (my preference) make the change larger. I think it's > > significant that the vast majority of compatible software has some > > variant of Postgres and *not* PostgreSQL in the name. > > I have seen no evidence of this. For evidence, take a look at the "Products" section of the Postgres Weekly News. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[DOCS] Partition: use triggers instead of rules
Folks, Best practices for partitioning so far have shown that TRIGGERs are better than RULEs for most cases. Please find attached a patch which reflects this. Thanks to Robert Treat for help putting this together :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 28 Nov 2007 20:23:44 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex trigger function as shown ! below: ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! ELSIF ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) THEN ! ... ! ELSIF ( logdate >= DATE '2008-01-01' AND logdate < DATE '2006-02-01' ) THEN !
Re: [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Best practices for partitioning so far have shown that TRIGGERs > > are better than RULEs for most cases. Please find attached a > > patch which reflects this. > > Entirely removing the example of how to do it with rules doesn't > seem like a good idea. It does to me. I haven't found a case yet where rules worked even as well as triggers. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Wed, 28 Nov 2007 12:26:15 -0800 > David Fetter <[EMAIL PROTECTED]> wrote: > > > Folks, > > > > Best practices for partitioning so far have shown that TRIGGERs are > > better than RULEs for most cases. Please find attached a patch which > > reflects this. > > > > Thanks to Robert Treat for help putting this together :) > > > > Cheers, > > David. > > +1 > > Joshua D. Drake Per Robert, I've also dropped the UNION partitioning suggestion as it's pretty useless. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 28 Nov 2007 20:39:48 - *** *** 2571,2594 script that generates the required DDL automatically. - - Partitioning can also be arranged using a UNION ALL - view: - - - CREATE VIEW measurement AS - SELECT * FROM measurement_y2004m02 - UNION ALL SELECT * FROM measurement_y2004m03 - ... - UNION ALL SELECT * FROM measurement_y2005m11 - UNION ALL SELECT * FROM measurement_y2005m12 - UNION ALL SELECT * FROM measurement_y2006m01; - - - However, the need to - recreate the view adds an extra step to adding and dropping - individual partitions of the data set. - --- 2571,2576 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 12:41:20PM -0800, David Fetter wrote: > On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Wed, 28 Nov 2007 12:26:15 -0800 > > David Fetter <[EMAIL PROTECTED]> wrote: > > > > > Folks, > > > > > > Best practices for partitioning so far have shown that TRIGGERs are > > > better than RULEs for most cases. Please find attached a patch which > > > reflects this. > > > > > > Thanks to Robert Treat for help putting this together :) > > > > > > Cheers, > > > David. > > > > +1 > > > > Joshua D. Drake > > Per Robert, I've also dropped the UNION partitioning suggestion as > it's pretty useless. Oops. Patch including *both* changes attached this time. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 28 Nov 2007 20:44:57 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We !
Re: [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 05:17:38PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote: > >> Entirely removing the example of how to do it with rules doesn't > >> seem like a good idea. > > > It does to me. I haven't found a case yet where rules worked even > > as well as triggers. > > I don't have a problem with emphasizing triggers as the preferred > solution, but we should keep the older example, if only because > people are going to see DB schemas that use that approach, and they > won't understand what's going on (or realize they could convert) if > they've not seen an example. Greg Sabino Mullane managed to contrive an example where RULEs might conceivably be the least-bad way to do this, that being a machine where no PLs may be installed. Telling people how to do this is not *quite* as bad as describing how to do EAV, but it's pretty close. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 10:06:01PM -0300, Alvaro Herrera wrote: > David Fetter wrote: > > > Greg Sabino Mullane managed to contrive an example where RULEs > > might conceivably be the least-bad way to do this, that being a > > machine where no PLs may be installed. > > Perhaps this just means we should consider installing plpgsql by > default. I'm all for that :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote: > On Nov 28, 2007 3:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > Entirely removing the example of how to do it with rules doesn't > > seem like a good idea. > > Agreed. Do you have an example of one use case where using RULEs rather than TRIGGERs is a good idea? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Thu, Nov 29, 2007 at 12:55:53AM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote: > >> On Nov 28, 2007 3:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > >>> Entirely removing the example of how to do it with rules doesn't > >>> seem like a good idea. > >> > >> Agreed. > > > Do you have an example of one use case where using RULEs rather > > than TRIGGERs is a good idea? > > The argument I made for keeping the example around is not dependent > on the assumption that using a rule is a good idea. It's dependent > on the established fact that we have recommended that in prior > releases, and therefore people are going to be seeing that construct > in real databases. We've corrected mistakes in the manual before :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Thu, Nov 29, 2007 at 11:42:18AM -0500, Bruce Momjian wrote: > Joshua D. Drake wrote: > > Tom Lane wrote: > > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > >> Rules are extremely slow in comparisons and not anywhere near as > > >> flexible. As I said up post yesterday... they work well in the basic > > >> partitioning configuration but anything else they are extremely > > >> deficient. > > > > > > I think that the above claim is exceedingly narrow-minded. > > > > We are talking about partitioning. It is supposed to be narrow-minded. > > Sure, but look at all the confusion we have had just on this list about > it. We had better state why triggers should be used in place of rules > _for_ _partitioning_ or that confusion will continue. Please find enclosed a patch with use cases for each. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 29 Nov 2007 17:51:46 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Fri, Nov 30, 2007 at 12:34:05PM +0530, NikhilS wrote: > Hi, > > Another reason to go along with triggers is that "COPY" honors > triggers, but does not honor rules. While trying to do bulk inserts > into a parent of partitioned tables where rules are being employed, > the COPY operation will not be so straightforward. Folks, Does my latest patch attached address this well enough? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 1 Dec 2007 04:55:46 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex trigger function as shown ! below: ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! ELSIF ( logdate >= DATE
Re: [HACKERS] [DOCS] "distributed checkpoint"
On Thu, Dec 06, 2007 at 07:19:44PM -0800, Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Thu, 06 Dec 2007 20:44:49 -0500 > Tom Lane <[EMAIL PROTECTED]> wrote: > > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > Am I the only one who finds the phrase "distributed checkpointing" > > > a bit awkward? Would it be better if we used "time-distributed > > > checkpointing" instead? > > > > Yeah, "distributed" has a bunch of connotations that are wrong for > > this purpose. > > > > I spent a bit of time with a thesaurus but didn't come up with > > anything that seemed le mot juste. Best I could do was "spread > > checkpoint" or "time-extended checkpoint". Anybody have a better > > idea? > > balanced > gradual > extended (I see you mention time-extended but wouldn't time be implicit > based on the actual docs and thus we only need extended?) How about "smoothed?" Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[DOCS] FAQ on Embedding Postgres
Folks, I just got yet another question about embedding Postgres in a binary. What should be in the FAQ on this subject? Here's what I have so far: Q: How can I embed PostgreSQL in a binary? A: You can't. PostgreSQL is designed from the ground up to run as a separate set of processes on a server. If you really need to embed an SQL engine in a binary, consider the excellent http://www.sqlite.org/";>SQLite for the purpose. Comments? Criticisms? Rotten tomatoes? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-docs
