Re: [PATCHES] psql latex bugfixes
OK, it looks good. I don't have latex handy to build it, but it looks
fine to me...
Chris
Bruce Momjian wrote:
If you would like to review it I will apply it.
---
Christopher Kings-Lynne wrote:
Surely this is a really good bug fix and should be in 7.5?
Bruce Momjian wrote:
This has been saved for the 7.6 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---
Roger Leigh wrote:
I have noticed that the latex format in psql has some bugs:
? _ is not escaped, and causes TeX to abort, thinking it's a
subscript outside of maths mode. Most of my table and field names
use underscores, so this is a really nasty one.
? The column count is calculated using the contents of opt_align. But
opt_align has one extra element, and so it's always one too many. I
changed it to count the column headings, like all the other output
formats. There may be a bug in computing opt_align that this patch
does not address, but I'm not yet familiar enough with the psql
source to fix this as well.
? The line drawing rules for each border setting (0-3) and expanded
mode didn't always match the documented behaviour and what other
formats (e.g. aligned) did. I made it as conformant as possible,
and also tidied the alignment of the first line of the footer, which
was incorrectly indented.
I've attached some example output with this patch applied.
Regards,
Roger
Index: src/bin/psql/print.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/print.c,v
retrieving revision 1.48
diff -u -r1.48 print.c
--- src/bin/psql/print.c23 May 2004 22:20:10 - 1.48
+++ src/bin/psql/print.c1 Aug 2004 22:54:22 -
@@ -769,7 +769,7 @@
/*/
-/* LaTeX*/
+/* LaTeX*/
/*/
@@ -790,6 +790,9 @@
case '$':
fputs(\\$, fout);
break;
+ case '_':
+ fputs(\\_, fout);
+ break;
case '{':
fputs(\\{, fout);
break;
@@ -817,7 +820,6 @@
{
unsigned int col_count = 0;
unsigned int i;
- const char *cp;
const char *const * ptr;
@@ -829,42 +831,39 @@
fputs(\n\\end{center}\n\n, fout);
}
+ /* count columns */
+ for (ptr = headers; *ptr; ptr++)
+ col_count++;
+
/* begin environment and set alignments and borders */
fputs(\\begin{tabular}{, fout);
- if (opt_border == 0)
- fputs(opt_align, fout);
- else if (opt_border == 1)
- {
- for (cp = opt_align; *cp; cp++)
- {
- if (cp != opt_align)
- fputc('|', fout);
- fputc(*cp, fout);
- }
- }
- else if (opt_border == 2)
+
+ if (opt_border == 2)
+ fputs(| , fout);
+for (i = 0; i col_count; i++)
{
- for (cp = opt_align; *cp; cp++)
- {
- fputc('|', fout);
- fputc(*cp, fout);
- }
- fputc('|', fout);
+ fputc(*(opt_align + i), fout);
+ if (opt_border != 0 i col_count - 1)
+ fputs ( | , fout);
}
+ if (opt_border == 2)
+ fputs( |, fout);
+
fputs(}\n, fout);
if (!opt_barebones opt_border == 2)
fputs(\\hline\n, fout);
/* print headers and count columns */
- for (i = 0, ptr = headers; *ptr; i++, ptr++)
+ for (i = 0, ptr = headers; i col_count; i++, ptr++)
{
- col_count++;
if (!opt_barebones)
{
if (i != 0)
fputs( , fout);
+fputs(\\textit{, fout);
latex_escaped_print(*ptr, fout);
+fputc('}', fout);
}
}
@@ -888,7 +887,7 @@
if (opt_border == 2)
fputs(\\hline\n, fout);
- fputs(\\end{tabular}\n\n, fout);
+ fputs(\\end{tabular}\n\n\\noindent , fout);
/* print footers */
@@ -951,8 +950,12 @@
if (!opt_barebones)
{
if (opt_border == 2)
+ {
fputs(\\hline\n, fout);
- fprintf(fout, \\multicolumn{2}{c}{Record %d} \n,
record++);
+ fprintf(fout,
\\multicolumn{2}{|c|}{\\textit{Record %d}} \n, record++);
+
[PATCHES] Patch for Array min() / max()
Hi ! I would like to contribute a patch to the PostgreSQL. The patch is for the one of TODO items: *ARRAYS - Allow MIN()/MAX() on arrays This patch is created with the latest cvshead codes. regards koju --- Koju Iijima Software Engineer Fujitsu Australia Software Technology Address: 14 Rodborough Road, Frenchs Forest NSW 2086 Tel: +61 2 9452 9076 Fax: +61 2 9975 2899 Email: [EMAIL PROTECTED] Web site: www.fastware.com --- This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email [EMAIL PROTECTED] arrayMIN_MAX.patch Description: Binary data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] Patch for namespace.sql
Hi ! I would like to contribute a patch to the PostgreSQL. This patch aims to increase test coverage for namespace RT. Regards koju --- Koju Iijima Software Engineer Fujitsu Australia Software Technology Address: 14 Rodborough Road, Frenchs Forest NSW 2086 Tel: +61 2 9452 9076 Fax: +61 2 9975 2899 Email: [EMAIL PROTECTED] Web site: www.fastware.com --- This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email [EMAIL PROTECTED] namespace.patch Description: Binary data ---(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: [PATCHES] Win32 tablespace
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Pflug Sent: 05 August 2004 20:23 To: Bruce Momjian Cc: PostgreSQL Patches Subject: Re: [PATCHES] Win32 tablespace Bruce Momjian wrote: Andreas Pflug wrote: The attached patch implements a symlink for win32 using junctions, and uses that for win32 tablespaces. Wow, that's, uh, amazing! http://www.codeproject.com/w2k/junctionpoints.asp has a good description of the obfuscated REPARSE_DATA_BUFFER which is included in my vc6 installation, but not sufficiently documented. I concur with Bruce - nice work. This is obviously win2k+ only though - until now we've tried to support NT4 as well, although it seems that we can't get initdb to work as we'd like in the installer on that platform. Should we officially support 2K+ only? Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Win32 tablespace
Dave Page wrote: This is obviously win2k+ only though - There's a chance it might work on NT4 with =SP4, because AFAIR the updated NTFS driver already understands the W2K format. until now we've tried to support NT4 as well, although it seems that we can't get initdb to work as we'd like in the installer on that platform. Should we officially support 2K+ only? Since NT4 isn't supported by M$ for quite some time now, it seems reasonable to mark it as does run with issues, but not officially supported. The installer would have to skip initdb, and display a lengthy read-carefully-readme how to continue manually. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Win32 tablespace
-Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 11:07 To: Dave Page Cc: Bruce Momjian; PostgreSQL Patches Subject: Re: [PATCHES] Win32 tablespace Since NT4 isn't supported by M$ for quite some time now, it seems reasonable to mark it as does run with issues, but not officially supported. The installer would have to skip initdb, and display a lengthy read-carefully-readme how to continue manually. Ahh, one of those things none of us read :-) /D ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] dbsize modification to support tablespaces
Attached.
GavinIndex: contrib/dbsize/dbsize.c
===
RCS file: /usr/local/cvsroot/pgsql-server/contrib/dbsize/dbsize.c,v
retrieving revision 1.10
diff -2 -c -r1.10 dbsize.c
*** contrib/dbsize/dbsize.c 23 Feb 2004 23:03:10 - 1.10
--- contrib/dbsize/dbsize.c 6 Aug 2004 11:24:48 -
***
*** 7,11
--- 7,13
#include access/heapam.h
#include catalog/catalog.h
+ #include catalog/catname.h
#include catalog/namespace.h
+ #include catalog/pg_tablespace.h
#include commands/dbcommands.h
#include fmgr.h
***
*** 14,17
--- 16,22
+ static int64
+ get_tablespace_size(Oid dbid, Oid spcid, bool baddirOK);
+
static char *
psnprintf(size_t len, const char *fmt,...)
***
*** 45,52
Oid dbid;
- char *dbpath;
- DIR*dirdesc;
- struct dirent *direntry;
int64 totalsize;
dbid = get_database_oid(NameStr(*dbname));
--- 50,59
Oid dbid;
int64 totalsize;
+ #ifdef SYMLINK
+ Relationdbrel;
+ HeapScanDescscan;
+ HeapTuple tuple;
+ #endif
dbid = get_database_oid(NameStr(*dbname));
***
*** 56,67
errmsg(database \%s\ does not exist, NameStr(*dbname;
! dbpath = GetDatabasePath(dbid);
dirdesc = AllocateDir(dbpath);
if (!dirdesc)
! ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not open directory \%s\: %m,
dbpath)));
!
totalsize = 0;
for (;;)
--- 63,115
errmsg(database \%s\ does not exist, NameStr(*dbname;
! #ifdef SYMLINK
!
! dbrel = heap_openr(TableSpaceRelationName, AccessShareLock);
! scan = heap_beginscan(dbrel, SnapshotNow, 0, (ScanKey) NULL);
!
! totalsize = 0;
!
! while((tuple = heap_getnext(scan, ForwardScanDirection)))
! {
! Oid spcid = HeapTupleGetOid(tuple);
! if(spcid != GLOBALTABLESPACE_OID)
! totalsize += get_tablespace_size(dbid, spcid, true);
! }
! heap_endscan(scan);
! heap_close(dbrel, AccessShareLock);
! #else
! /* Same as always */
! totalsize = get_tablespace_size(dbid, DEFAULTTABLESPACE_OID, false);
! #endif
!
! /*
!* We need to keep in mind that we may not be called from the database
!* whose size we're reporting so, we need to look in every tablespace
!* to see if our database has data in there
!*/
!
! PG_RETURN_INT64(totalsize);
! }
!
! static int64
! get_tablespace_size(Oid dbid, Oid spcid, bool baddirOK)
! {
! char*dbpath;
! DIR *dirdesc;
! struct dirent *direntry;
! int64 totalsize;
!
! dbpath = GetDatabasePath(dbid, spcid);
dirdesc = AllocateDir(dbpath);
if (!dirdesc)
! {
! if(baddirOK)
! return 0;
! else
! ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not open directory \%s\: %m,
dbpath)));
! }
totalsize = 0;
for (;;)
***
*** 88,92
(errcode_for_file_access(),
errmsg(could not stat \%s\: %m,
fullname)));
-
totalsize += statbuf.st_size;
pfree(fullname);
--- 136,139
***
*** 94,102
FreeDir(dirdesc);
!
! PG_RETURN_INT64(totalsize);
}
-
-
/*
--- 141,146
FreeDir(dirdesc);
! return (totalsize);
}
/*
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] Minor savepoint doc addition
Included is an example of using savepoints in a non-trivial example.
Giving examples in the SQL command reference is hard because we don't have
conditionals at the SQL level.
Tom mentioned nested transactions being mentioned in places but I couldn't
find them (with grep). Suggestions?
GavinIndex: doc/src/sgml/ref/begin.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/begin.sgml,v
retrieving revision 1.31
diff -2 -c -r1.31 begin.sgml
*** doc/src/sgml/ref/begin.sgml 1 Aug 2004 17:32:13 - 1.31
--- doc/src/sgml/ref/begin.sgml 6 Aug 2004 11:35:03 -
***
*** 101,104
--- 101,107
Issuing commandBEGIN/ when already inside a transaction block will
provoke a warning message. The state of the transaction is not affected.
+To nest transactions within a transaction block, use savepoints
+(See xref linkend=sql-start-transaction endterm=sql-start-transaction-title
+for more information).
/para
/refsect1
Index: doc/src/sgml/ref/update.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.29
diff -2 -c -r1.29 update.sgml
*** doc/src/sgml/ref/update.sgml9 Jun 2004 19:08:13 - 1.29
--- doc/src/sgml/ref/update.sgml6 Aug 2004 11:54:39 -
***
*** 189,192
--- 189,205
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
/programlisting
+
+Attempt to insert a new stock item along with the quantity of stock. If
+the item exists, update the stock count of the existing item. To do this,
+use savepoints.
+ programlisting
+ BEGIN;
+ SAVEPOINT sp1;
+ INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
+ -- Check for unique violation on name
+ ROLLBACK TO sp1;
+ UPDATE wines SET stock = stock + 24 WHERE winename='Chateau Lafite 2003';
+ COMMIT;
+ /programlisting
/para
/refsect1
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [HACKERS] UNICODE characters above 0x10000
Attached, as promised, small patch removing the limitation, adding correct utf8 validation. Regards, John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Hansen Sent: Friday, August 06, 2004 2:20 PM To: 'Hackers' Subject: [HACKERS] UNICODE characters above 0x1 I've started work on a patch for this problem. Doing regression tests at present. I'll get back when done. Regards, John ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html wchar.c.patch Description: wchar.c.patch ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] Create Domain example
Kind people,
This patch adds an example to the CREATE DOMAIN docs.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: create_domain.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/create_domain.sgml,v
retrieving revision 1.17
diff -u -r1.17 create_domain.sgml
--- create_domain.sgml 29 Nov 2003 19:51:38 - 1.17
+++ create_domain.sgml 6 Aug 2004 18:06:30 -
@@ -160,11 +160,25 @@
titleExamples/title
para
- This example creates the typecountry_code/type data type and then uses the
- type in a table definition:
+ This example creates the typeus_postal_code/type data type and
+then uses the type in a table definition:
+
programlisting
-CREATE DOMAIN country_code char(2) NOT NULL;
-CREATE TABLE countrylist (id integer, country country_code);
+CREATE DOMAIN us_postal_code AS TEXT
+NOT NULL
+CHECK(
+ VALUE ~ $pc$^\d{5}$$pc$
+OR VALUE ~ $pc$^\d{5}-\d{4}$$pc$
+);
+
+CREATE TABLE us_snail_addy (
+ address_id SERIAL NOT NULL PRIMARY KEY
+, street1 TEXT NOT NULL
+, street2 TEXT
+, street3 TEXT
+, city TEXT NOT NULL
+, postal us_postal_code
+);
/programlisting
/para
/refsect1
---(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: [PATCHES] psql latex bugfixes
Patch applied for 8.0. Thanks.
---
Roger Leigh wrote:
I have noticed that the latex format in psql has some bugs:
? _ is not escaped, and causes TeX to abort, thinking it's a
subscript outside of maths mode. Most of my table and field names
use underscores, so this is a really nasty one.
? The column count is calculated using the contents of opt_align. But
opt_align has one extra element, and so it's always one too many. I
changed it to count the column headings, like all the other output
formats. There may be a bug in computing opt_align that this patch
does not address, but I'm not yet familiar enough with the psql
source to fix this as well.
? The line drawing rules for each border setting (0-3) and expanded
mode didn't always match the documented behaviour and what other
formats (e.g. aligned) did. I made it as conformant as possible,
and also tidied the alignment of the first line of the footer, which
was incorrectly indented.
I've attached some example output with this patch applied.
Regards,
Roger
Index: src/bin/psql/print.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/print.c,v
retrieving revision 1.48
diff -u -r1.48 print.c
--- src/bin/psql/print.c 23 May 2004 22:20:10 - 1.48
+++ src/bin/psql/print.c 1 Aug 2004 22:54:22 -
@@ -769,7 +769,7 @@
/*/
-/* LaTeX */
+/* LaTeX */
/*/
@@ -790,6 +790,9 @@
case '$':
fputs(\\$, fout);
break;
+ case '_':
+ fputs(\\_, fout);
+ break;
case '{':
fputs(\\{, fout);
break;
@@ -817,7 +820,6 @@
{
unsigned int col_count = 0;
unsigned int i;
- const char *cp;
const char *const * ptr;
@@ -829,42 +831,39 @@
fputs(\n\\end{center}\n\n, fout);
}
+ /* count columns */
+ for (ptr = headers; *ptr; ptr++)
+ col_count++;
+
/* begin environment and set alignments and borders */
fputs(\\begin{tabular}{, fout);
- if (opt_border == 0)
- fputs(opt_align, fout);
- else if (opt_border == 1)
- {
- for (cp = opt_align; *cp; cp++)
- {
- if (cp != opt_align)
- fputc('|', fout);
- fputc(*cp, fout);
- }
- }
- else if (opt_border == 2)
+
+ if (opt_border == 2)
+ fputs(| , fout);
+for (i = 0; i col_count; i++)
{
- for (cp = opt_align; *cp; cp++)
- {
- fputc('|', fout);
- fputc(*cp, fout);
- }
- fputc('|', fout);
+ fputc(*(opt_align + i), fout);
+ if (opt_border != 0 i col_count - 1)
+ fputs ( | , fout);
}
+ if (opt_border == 2)
+ fputs( |, fout);
+
fputs(}\n, fout);
if (!opt_barebones opt_border == 2)
fputs(\\hline\n, fout);
/* print headers and count columns */
- for (i = 0, ptr = headers; *ptr; i++, ptr++)
+ for (i = 0, ptr = headers; i col_count; i++, ptr++)
{
- col_count++;
if (!opt_barebones)
{
if (i != 0)
fputs( , fout);
+fputs(\\textit{, fout);
latex_escaped_print(*ptr, fout);
+fputc('}', fout);
}
}
@@ -888,7 +887,7 @@
if (opt_border == 2)
fputs(\\hline\n, fout);
- fputs(\\end{tabular}\n\n, fout);
+ fputs(\\end{tabular}\n\n\\noindent , fout);
/* print footers */
@@ -951,8 +950,12 @@
if (!opt_barebones)
{
if (opt_border == 2)
+ {
fputs(\\hline\n, fout);
- fprintf(fout, \\multicolumn{2}{c}{Record %d} \n,
record++);
+ fprintf(fout,
\\multicolumn{2}{|c|}{\\textit{Record %d}} \n, record++);
+ }
+ else
+ fprintf(fout,
\\multicolumn{2}{c}{\\textit{Record %d}} \n, record++);
}
if (opt_border = 1)
fputs(\\hline\n, fout);
@@ -967,7 +970,7 @@
if (opt_border == 2)
fputs(\\hline\n, fout);
[PATCHES] Tutorial patch
Folks,
This adds a caveat to the inheritance part of the tutorial.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: advanced.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/advanced.sgml,v
retrieving revision 1.42
diff -u -r1.42 advanced.sgml
--- advanced.sgml 1 Aug 2004 17:32:11 - 1.42
+++ advanced.sgml 6 Aug 2004 18:05:35 -
@@ -108,7 +108,7 @@
);
CREATE TABLE weather (
- city varchar(80) references cities,
+ city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
@@ -326,16 +326,97 @@
/indexterm
para
-Inheritance is a concept from object-oriented databases. It opens
-up interesting new possibilities of database design.
+Inheritance is a concept from object-oriented databases. Although
+it opens up interesting new possibilities of database design,
+this feature is currently unmaintained and known to have serious
+gotchas in its foreign key implementation, which you should take
+care to avoid. The fixes below are probably version-specific and may
+require updates in the future.
+ /para
+ para
+The example below illustrates the gotcha.
+ /para
+ para
+programlisting
+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;
+ parent_id | foo_id | parent_1_text
+---++---
+ 1 | 1 | parent text 1
+ 2 | 2 | parent text 2
+ 3 | 3 | parent text 3
+(3 rows)
+
+SELECT * FROM child_1;
+ parent_id | foo_id | parent_1_text | child_1_text
+---++---+
+ 1 | 1 | parent text 1 | child_1 text 1
+ 2 | 2 | parent text 2 | child_1 text 2
+(2 rows)
+ROLLBACK;
+/programlisting
+
+ /para
+ para
+ Oops!! None of parent, child or foo should have any rows with
+foo_id = 1 in them. Here is a way to fix the above tables.
+ /para
+
+ para
+To fix the gotcha, you must put foreign key constraints on each of
+the child tables, as they will not be automatically inherited as
+you might expect.
+ /para
+
+ para
+programlisting
+ALTER TABLE child_1 ADD CONSTRAINT cascade_foo
+FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;
+
+ALTER TABLE child_2 ADD CONSTRAINT cascade_foo
+FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;
+/programlisting
/para
para
-Let's create two tables: A table classnamecities/classname
-and a table classnamecapitals/classname. Naturally, capitals
-are also cities, so you want some way to show the capitals
-implicitly when you list all cities. If you're really clever you
-might invent some scheme like this:
+That caveat out of the way, let's create two tables: A table
+classnamecities/classname and a table
+classnamecapitals/classname. Naturally, capitals are also cities,
+so you want some way to show the capitals implicitly when you list all
+cities. If you're really clever you might invent some scheme like
+this:
programlisting
CREATE TABLE capitals (
@@ -358,7 +439,7 @@
/programlisting
This works OK as far as querying goes, but it gets ugly when you
-need to update several rows, to name one thing.
+need to update several rows, for one thing.
/para
para
Index: query.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/query.sgml,v
retrieving revision 1.35
diff -u -r1.35 query.sgml
--- query.sgml 29 Nov 2003 19:51:37 - 1.35
+++ query.sgml 6 Aug 2004 18:05:36 -
@@ -284,8 +284,10 @@
programlisting
SELECT * FROM weather;
/programlisting
-(here literal*/literal means quoteall columns/quote) and
-the output should be:
+(here literal*/literal means quoteall columns/quote.
+Note: While literalSELECT */literal is useful for
[PATCHES] Intro patch
Folks, This patch adds SQL2003 to the set of standards with which PostgreSQL is attempting to comply. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: intro.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/intro.sgml,v retrieving revision 1.24 diff -u -r1.24 intro.sgml --- intro.sgml 29 Nov 2003 19:51:37 - 1.24 +++ intro.sgml 6 Aug 2004 18:05:21 - @@ -98,8 +98,8 @@ para productnamePostgreSQL/productname is an open-source descendant - of this original Berkeley code. It supports SQL92 and SQL99 and - offers many modern features: + of this original Berkeley code. It supports SQL92, SQL99 and + SQL2003 and offers many modern features: itemizedlist spacing=compact listitem ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PATCHES] That dump-comments-on-composite-type-columns patch...
... doesn't work. $ pg_dump regression zzz.out pg_dump: SQL command failed pg_dump: Error message from server: ERROR: complex is a composite type pg_dump: The command was: COPY public.complex (r, i) TO stdout; $ I suspect it had more subtle problems too, because dumpTableComments would have attached the comments to the dumpid associated with the TableInfo entry, which isn't the object that will get dumped. So it seems moderately likely that there would have been a potential for misordering of the output. I think it's probably a fundamentally bad idea to be putting composite types into pg_dump's TableInfo array, because they just really aren't tables at all. If you want to try again, I'd suggest writing a variant of dumpTableComment that takes a TypeInfo and the attribute-names query data obtained by dumpCompositeType. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] UNICODE characters above 0x10000
John Hansen [EMAIL PROTECTED] writes: Attached, as promised, small patch removing the limitation, adding correct utf8 validation. Surely this is badly broken --- it will happily access data outside the bounds of the given string. Also, doesn't pg_mblen already know the length rules for UTF8? Why are you duplicating that knowledge? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] regexp_replace
This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---
a_ogawa00 wrote:
This patch provides a new function regexp_replace.
regexp_replace extends a replace function and enables text search
by the regular expression. And, a back reference can be used within
a replace string.
(This patch for PostgreSQL 7.4.3)
Function: regexp_replace(str, pattern, replace_str)
Retuen Type: text
Description: Replace all matched string in str.
pattern is regular expression pattern.
replace_str is replace string that can use '\1' - '\9', and
'\'.
'\1' - '\9' is back reference to the n'th subexpression.
'\' is matched string.
(example1)
select regexp_replace('ABC-DEF', '(\\w+)-(\\w+)', '\\2-\\1')
result: DEF-ABC
(example2)
update tab1 set col1 = regexp_replace(col1, '[A-Z]', '');
---
Atsushi Ogawa
[EMAIL PROTECTED]
--- cut here ---
*** ./src/backend/regex/regexec.c.origTue Jul 20 08:45:39 2004
--- ./src/backend/regex/regexec.c Tue Jul 20 08:49:36 2004
***
*** 110,115
--- 110,116
regmatch_t *pmatch;
rm_detail_t *details;
chr*start; /* start of string */
+ chr*search_start; /* search start of string */
chr*stop; /* just past end of string */
int err;/* error code if any (0 none)
*/
regoff_t *mem;/* memory vector for backtracking */
***
*** 168,173
--- 169,175
pg_regexec(regex_t *re,
const chr *string,
size_t len,
+size_t search_start,
rm_detail_t *details,
size_t nmatch,
regmatch_t pmatch[],
***
*** 219,224
--- 221,227
v-pmatch = pmatch;
v-details = details;
v-start = (chr *) string;
+ v-search_start = (chr *) string + search_start;
v-stop = (chr *) string + len;
v-err = 0;
if (backref)
***
*** 288,294
NOERR();
MDEBUG((\nsearch at %ld\n, LOFF(v-start)));
cold = NULL;
! close = shortest(v, s, v-start, v-start, v-stop, cold, (int *)
NULL);
freedfa(s);
NOERR();
if (v-g-cflags REG_EXPECT)
--- 291,298
NOERR();
MDEBUG((\nsearch at %ld\n, LOFF(v-start)));
cold = NULL;
! close = shortest(v, s, v-search_start, v-search_start, v-stop,
! cold, (int *) NULL);
freedfa(s);
NOERR();
if (v-g-cflags REG_EXPECT)
***
*** 415,421
assert(d != NULL s != NULL);
cold = NULL;
! close = v-start;
do
{
MDEBUG((\ncsearch at %ld\n, LOFF(close)));
--- 419,425
assert(d != NULL s != NULL);
cold = NULL;
! close = v-search_start;
do
{
MDEBUG((\ncsearch at %ld\n, LOFF(close)));
*** ./src/backend/utils/adt/regexp.c.orig Tue Jul 20 08:50:08 2004
--- ./src/backend/utils/adt/regexp.c Tue Jul 20 09:00:05 2004
***
*** 80,116
/*
! * RE_compile_and_execute - compile and execute a RE, caching if possible
*
! * Returns TRUE on match, FALSE on no match
*
! * text_re --- the pattern, expressed as an *untoasted* TEXT object
! * dat --- the data to match against (need not be null-terminated)
! * dat_len --- the length of the data string
! * cflags --- compile options for the pattern
! * nmatch, pmatch --- optional return area for match details
*
! * Both pattern and data are given in the database encoding. We
internally
! * convert to array of pg_wchar which is what Spencer's regex package
wants.
*/
! static bool
! RE_compile_and_execute(text *text_re, unsigned char *dat, int dat_len,
!int cflags, int nmatch, regmatch_t *pmatch)
{
int text_re_len = VARSIZE(text_re);
- pg_wchar *data;
- size_t data_len;
pg_wchar *pattern;
size_t pattern_len;
int i;
int regcomp_result;
- int regexec_result;
cached_re_str re_temp;
- /* Convert data string to wide characters */
- data = (pg_wchar *) palloc((dat_len + 1) * sizeof(pg_wchar));
- data_len = pg_mb2wchar_with_len(dat, data, dat_len);
-
/*
* Look for a match among previously compiled REs. Since the data
* structure is self-organizing with most-used entries at the front,
--- 80,105
/*
! * RE_compile_and_cache - compile a RE, caching if possible
*
! *
Re: [PATCHES] Autovacuum Integration Patch Take 5
Matthew, your reply was exactly the type of reply I would have made in your situation. Your arguments are clear and indisputable. Due to the many large patches the we had to process during this release, we serialized their review. However, I made promises to developers that their patches would get the same consideration if they were reviewed early or late. Obviously this wasn't true of your patch. We found more issues than we thought and didn't give you time to address them. Frankly we are lucky autovacuum was the only item that didn't make it because several features were in similar need of major work. Of course that is no consolation to you and people looking for autovacuum in 8.0. Not sure what I can do about it at this point. I am going to write up a whole documentation section on 3rd party tools and interfaces and pg_autovacuum would have a big mention there. There is the issue of Win32 and the need for pg_autovacuum to start easily. --- Matthew T. O'Connor wrote: Tom Lane wrote: You're headed in the right direction, but I'm afraid we're running out of time. The core committee has chewed this over and agreed that we can't postpone beta for the amount of time we think it will take to make this patch committable. So we're going to hold it over for the 8.1 release cycle. I have to make a personal apology to you for the fact that things worked out this way. I really should have looked at your patch much earlier and given you some feedback that might have allowed you to resolve the issues in time. I did not because (a) I felt that the other patches I was working on were more important features (a judgment I still stand by) and (b) I thought your patch was in good enough shape that we could apply it with little effort. That judgment was badly off, and again I must apologize for it. I hope you won't get discouraged, and will continue to work on an integrated autovacuum for 8.1. AGGGHH! This is very frustrating. I saw this coming weeks and weeks ago and tried to get people's attention so that this wouldn't happen. Aside from my personal frustration, I will say that autovacuum is a high priority for lots of users of autovacuum and there are already lots of users looking forward to it being in 8.0. FWIW, I tried to clean up as much stuff as I could the other night and submit and updated patch, I would guess that it wouldn't take you very long to clean up the shutdown issues. BTW, I choose to try to integrate it into the backend on the recomendation of several people on the hackers list despite my warnings that I would probably need help with the backend code issues. I could have instead put my time towards an improved version in contrib, now the end-users will have to go another release cycle without any of the feature improvements I had hoped for. FWIW, core has also agreed that we want to shoot for a much shorter release cycle for 8.1 than we have had in the past couple of releases. It seems likely that as the new 8.0 features are shaken out, 8.1 will be mostly a mop-up development cycle, and that we will want to push it out relatively soon (we're thinking of perhaps 3-4 months in development, with a total release cycle of 6-7 months). I think we have all heard this before ---(end of broadcast)--- TIP 8: 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] UNICODE characters above 0x10000
My apologies for not reading the code properly. Attached patch using pg_utf_mblen() instead of an indexed table. It now also do bounds checks. Regards, John Hansen -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, August 07, 2004 4:37 AM To: John Hansen Cc: Hackers; Patches Subject: Re: [HACKERS] UNICODE characters above 0x1 John Hansen [EMAIL PROTECTED] writes: Attached, as promised, small patch removing the limitation, adding correct utf8 validation. Surely this is badly broken --- it will happily access data outside the bounds of the given string. Also, doesn't pg_mblen already know the length rules for UTF8? Why are you duplicating that knowledge? regards, tom lane wchar.c.patch Description: wchar.c.patch ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Allow semicolons in psql \h strings
Cleaned up version applied.
---
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
[ PGP not available, raw data follows ]
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
A minor itch I finally got around to scratching:
allow semicolons at the end of help topics
(also bump copyright years (should really be done for all files))
--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200408042143
Index: help.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/help.c,v
retrieving revision 1.90
diff -c -r1.90 help.c
*** help.c 15 Jul 2004 03:56:06 - 1.90
--- help.c 4 Aug 2004 19:01:41 -
***
*** 1,7
/*
* psql - the PostgreSQL interactive terminal
*
! * Copyright (c) 2000-2003, PostgreSQL Global Development Group
*
* $PostgreSQL: pgsql-server/src/bin/psql/help.c,v 1.90 2004/07/15 03:56:06
momjian Exp $
*/
--- 1,7
/*
* psql - the PostgreSQL interactive terminal
*
! * Copyright (c) 2000-2004, PostgreSQL Global Development Group
*
* $PostgreSQL: pgsql-server/src/bin/psql/help.c,v 1.90 2004/07/15 03:56:06
momjian Exp $
*/
***
*** 310,318
int nl_count = 0;
char*ch;
! /* don't care about trailing spaces */
len = strlen(topic);
! while (topic[len - 1] == ' ')
len--;
/* Count newlines for pager */
--- 310,318
int nl_count = 0;
char*ch;
! /* don't care about trailing spaces or semicolons */
len = strlen(topic);
! while (' ' == topic[len - 1] || ';' == topic[len - 1])
len--;
/* Count newlines for pager */
***
*** 372,378
{
puts(
PostgreSQL Data Base Management System\n\n
!Portions Copyright (c) 1996-2003, PostgreSQL Global Development
Group\n\n
This software is based on Postgres95, formerly known as Postgres,
which\n
contains the following notice:\n\n
Portions Copyright(c) 1994, Regents of the University of
California\n\n
--- 372,378
{
puts(
PostgreSQL Data Base Management System\n\n
!Portions Copyright (c) 1996-2004, PostgreSQL Global Development
Group\n\n
This software is based on Postgres95, formerly known as Postgres,
which\n
contains the following notice:\n\n
Portions Copyright(c) 1994, Regents of the University of
California\n\n
-BEGIN PGP SIGNATURE-
iD8DBQFBEZFlvJuQZxSWSsgRAlLIAKD7GNhFDkbOa8DZNSKhU5Z8rDymtwCfQiWG
fRTYcra52IDgDbIeoChj3k0=
=hkdw
-END PGP SIGNATURE-
---(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
[ Decrypting message... End of raw data. ]
--
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
Index: src/bin/psql/help.c
===
RCS file: /cvsroot/pgsql-server/src/bin/psql/help.c,v
retrieving revision 1.90
diff -c -c -r1.90 help.c
*** src/bin/psql/help.c 15 Jul 2004 03:56:06 - 1.90
--- src/bin/psql/help.c 7 Aug 2004 03:30:48 -
***
*** 310,318
int nl_count = 0;
char*ch;
! /* don't care about trailing spaces */
len = strlen(topic);
! while (topic[len - 1] == ' ')
len--;
/* Count newlines for pager */
--- 310,318
int nl_count = 0;
char*ch;
! /* don't care about trailing spaces or semicolons */
len = strlen(topic);
! while (topic[len - 1] == ' ' || topic[len - 1] == ';')
len--;
/* Count newlines for pager */
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Minor BEFORE DELETE trigger fix
Can I get a context diff please? --- Gavin Sherry wrote: Attached is a minor patch to make BEFORE DELETE triggers honour tgenabled properly. I know that we cannot, currently, use this feature through a DDL command but just in case someone is updating the catalogs to do it and since it is necessary in order to implement disabling of triggers, I thought I'd send it in. Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- 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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Minor BEFORE DELETE trigger fix
Oops.
Attached in the usual format this time.
Gavin
On Fri, 6 Aug 2004, Bruce Momjian wrote:
Can I get a context diff please?
---
Gavin Sherry wrote:
Attached is a minor patch to make BEFORE DELETE triggers honour tgenabled
properly.
I know that we cannot, currently, use this feature through a DDL command
but just in case someone is updating the catalogs to do it and since it is
necessary in order to implement disabling of triggers, I thought I'd send
it in.
Gavin
Content-Description:
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
!DSPAM:41144fb520531574347913!
Index: src/backend/commands/trigger.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/trigger.c,v
retrieving revision 1.166
diff -2 -c -r1.166 trigger.c
*** src/backend/commands/trigger.c 1 Jul 2004 00:50:11 - 1.166
--- src/backend/commands/trigger.c 5 Aug 2004 01:25:46 -
***
*** 1350,1353
--- 1350,1354
TupleTableSlot *newSlot;
int i;
+ boolret;
trigtuple = GetTupleForTrigger(estate, relinfo, tupleid, cid, newSlot);
***
*** 1366,1369
--- 1367,1371
LocTriggerData.tg_relation = relinfo-ri_RelationDesc;
LocTriggerData.tg_newtuple = NULL;
+ ret = true;
for (i = 0; i ntrigs; i++)
{
***
*** 1378,1382
--- 1380,1387
GetPerTupleMemoryContext(estate));
if (newtuple == NULL)
+ {
+ ret = false;
break;
+ }
if (newtuple != trigtuple)
heap_freetuple(newtuple);
***
*** 1384,1388
heap_freetuple(trigtuple);
! return (newtuple == NULL) ? false : true;
}
--- 1389,1393
heap_freetuple(trigtuple);
! return (ret);
}
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] UNICODE characters above 0x10000
John Hansen [EMAIL PROTECTED] writes: My apologies for not reading the code properly. Attached patch using pg_utf_mblen() instead of an indexed table. It now also do bounds checks. I think you missed my point. If we don't need this limitation, the correct patch is simply to delete the whole check (ie, delete lines 827-836 of wchar.c, and for that matter we'd then not need the encoding local variable). What's really at stake here is whether anything else breaks if we do that. What else, if anything, assumes that UTF characters are not more than 2 bytes? Now it's entirely possible that the underlying support is a few bricks shy of a load --- for instance I see that pg_utf_mblen thinks there are no UTF8 codes longer than 3 bytes whereas your code goes to 4. I'm not an expert on this stuff, so I don't know what the UTF8 spec actually says. But I do think you are fixing the code at the wrong level. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
