Re: [PATCHES] add soundex difference function to contrib/fuzzystrmatch
Kris Jurka wrote: I've attached two new patches. One revising my original patch to make the function creations consistent and the other to just fix the problem in the existing code (which should be backported as far as people would like to). Full patch applied to HEAD, fix for STRICT applied to 8.0, 7.4, 7.3 and 7.2 Thanks for the patches. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PATCHES] Multi-table truncate
Hackers,
This is my multi-table truncate patch. Includes doc changes and couple
new regression tests.
Note the following excerpt from the temp regression test:
+ BEGIN;
+ CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS;
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"temptest3_pkey" for table "temptest3"
+ CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
+ COMMIT;
+ ERROR: invalid ON COMMIT and foreign key combination
+ DETAIL: Table "temptest4" references "temptest3", but they don't have the
same ON COMMIT setting
I haven't investigated whether we can reject the foreign-key/on-commit
combination at create table time, rather than commit time. Maybe it's
worth doing, but then apparently on-commit-actions is not used a lot
(nobody saw the bug I previously reported).
Please review and consider for application.
Thanks,
--
Alvaro Herrera ()
"Las mujeres son como hondas: mientras más resistencia tienen,
más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
Index: doc/src/sgml/ref/truncate.sgml
===
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/ref/truncate.sgml,v
retrieving revision 1.17
diff -c -r1.17 truncate.sgml
*** doc/src/sgml/ref/truncate.sgml 23 Mar 2004 13:21:41 - 1.17
--- doc/src/sgml/ref/truncate.sgml 6 Nov 2004 01:20:19 -
***
*** 11,17
TRUNCATE
! empty a table
--- 11,17
TRUNCATE
! empty a set of tables
***
*** 20,26
! TRUNCATE [ TABLE ] name
--- 20,26
! TRUNCATE [ TABLE ] name [, ...]
***
*** 28,37
Description
!TRUNCATE quickly removes all rows from a
!table. It has the same effect as an unqualified
!DELETE but since it does not actually scan the
!table it is faster. This is most useful on large tables.
--- 28,37
Description
!TRUNCATE quickly removes all rows from a set of
!tables. It has the same effect as an unqualified
!DELETE on each of them, but since it does not actually
!scan the tables it is faster. This is most useful on large tables.
***
*** 55,67
TRUNCATE cannot be used if there are foreign-key references
!to the table from other tables. Checking validity in such cases would
!require table scans, and the whole point is not to do one.
TRUNCATE will not run any user-defined ON
!DELETE triggers that might exist for the table.
--- 55,68
TRUNCATE cannot be used if there are foreign-key references
!to the table from other tables, unless all such tables are also truncated
!in the same command. Checking validity in such cases would require table
!scans, and the whole point is not to do one.
TRUNCATE will not run any user-defined ON
!DELETE triggers that might exist for the tables.
***
*** 69,78
Examples
!Truncate the table bigtable:
! TRUNCATE TABLE bigtable;
--- 70,79
Examples
!Truncate the tables bigtable and
fattable:
! TRUNCATE TABLE bigtable, fattable;
Index: src/backend/catalog/heap.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.279
diff -c -r1.279 heap.c
*** src/backend/catalog/heap.c 10 Jan 2005 20:02:19 - 1.279
--- src/backend/catalog/heap.c 20 Jan 2005 19:30:51 -
***
*** 1985,2083
/*
* heap_truncate
*
! * This routine deletes all data within the specified relation.
*
* This is not transaction-safe! There is another, transaction-safe
! * implementation in commands/cluster.c. We now use this only for
* ON COMMIT truncation of temporary tables, where it doesn't matter.
*/
void
! heap_truncate(Oid rid)
{
! Relationrel;
! Oid toastrelid;
!
! /* Open relation for processing, and grab exclusive access on it. */
! rel = heap_open(rid, AccessExclusiveLock);
!
! /* Don't allow truncate on tables that are referenced by foreign keys */
! heap_truncate_check_FKs(rel);
/*
!* Release any buffers associated with this relation. If they're
!* dirty, they're just dropped without bothering to flush to disk.
*/
! DropRelationBuffers(rel);
! /* Now truncate the actual data */
! RelationTruncate(rel, 0);
! /* If this relation has indexes, truncate the indexes too */
! RelationTruncateIndexes(rid);
! /* If it has a toast table, recursively truncate that too */
! toastrelid = rel->rd_rel->reltoastrelid;
! if (OidIsValid(toastrelid))
! heap_tr
[PATCHES] Win32 pg_autovacuum service retry patch
Apologies in advance as my original attempt to send this message may well get
through in a day or so now that Bob the Builder has finished digging up my
fibre and copper and the telco has set about joining them all back together and
burying them again.
Anyway... the attached patch to pg_autovacuum follows Tom's earlier suggestion
that it re-try the inital database connection every 30 seconds for 5 minutes
before aborting when starting as a Windows Service.
Any chance of getting this in before 8.0.1?
Cheers, Dave.Index: pg_autovacuum.c
===
RCS file: /projects/cvsroot/pgsql/contrib/pg_autovacuum/pg_autovacuum.c,v
retrieving revision 1.28
diff -u -r1.28 pg_autovacuum.c
--- pg_autovacuum.c 24 Jan 2005 00:13:38 - 1.28
+++ pg_autovacuum.c 25 Jan 2005 11:46:44 -
@@ -556,6 +556,7 @@
Dllist *db_list = DLNewList();
db_info*dbs = NULL;
PGresult *res = NULL;
+intj = 0, k = 0;
DLAddHead(db_list, DLNewElem(init_dbinfo((char *) "template1", 0, 0)));
if (DLGetHead(db_list) == NULL)
@@ -571,6 +572,28 @@
*/
dbs = ((db_info *) DLE_VAL(DLGetHead(db_list)));
dbs->conn = db_connect(dbs);
+
+#ifdef WIN32
+while (dbs->conn == NULL && !appMode && k < 10)
+{
+/* Pause for 30 seconds to allow the database to start up */
+log_entry("Pausing 30 seconds to allow the database to startup completely", LVL_INFO);
+fflush(LOGOUTPUT);
+ServiceStatus.dwWaitHint = 10;
+for (j=0; j<6; j++)
+{
+pg_usleep(500);
+ServiceStatus.dwCheckPoint++;
+SetServiceStatus(hStatus, &ServiceStatus);
+fflush(LOGOUTPUT);
+}
+
+/* now try again */
+log_entry("Attempting to connect again.", LVL_INFO);
+dbs->conn = db_connect(dbs);
+k++;
+}
+#endif
if (dbs->conn != NULL)
{
---(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] Win32 pg_autovacuum service retry patch
[EMAIL PROTECTED] writes: > Anyway... the attached patch to pg_autovacuum follows Tom's earlier suggestion > that it re-try the inital database connection every 30 seconds for 5 minutes > before aborting when starting as a Windows Service. Applied. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] dbsize patch
On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: > The attached dbsize patch: > > + makes relation_size(relname) include toast tables; > + adds aggregate_relation_size(relname) to count table data and indices; > + adds indices_size(relname) to report the size of indices for a > relation; > > I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu, > compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5). Barring any objections, I'll apply this to HEAD tomorrow. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Multi-table truncate
Alvaro Herrera <[EMAIL PROTECTED]> writes: > This is my multi-table truncate patch. Includes doc changes and couple > new regression tests. Applied with minor editorialization. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Increased error verbosity when querying row-returning
On Wed, 2005-01-12 at 17:34 +1100, Brendan Jurd wrote: > Thanks Alvaro, changes made and new patch attached. Applied to HEAD -- thanks for the patch. Another style tip: we don't accept code that produces compiler warnings (pretty much), so checking that before submitting patches would be nice. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Move get_grosysid() to utils/cache/lsyscache.c
On Wed, 2004-12-29 at 11:36 -0500, Stephen Frost wrote: > Small patch to move get_grosysid() from catalog/aclchk.c to > utils/cache/lsyscache.c where it can be used by other things. Also > cleans up both get_usesysid() and get_grosysid() a bit. This is in > preparation for 'Group Ownership' support. I'll apply this to HEAD tomorrow, barring any objections. -Neil ---(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] dbsize patch
Neil Conway wrote: On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5). Barring any objections, I'll apply this to HEAD tomorrow. Perhaps you could rename indices_size to indexes_size. A quick google search on "site:postgresql.org indices" and "site:postgresql.org indexes" shows that indices is used much less (7,080) than indexes (23,400). Top hits for indices are 7.1 docs, for indexes it's 7.3 and 7.4. It seems to me that indexes is the term more commonly used with postgresql. Best Regards, Michael ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] dbsize patch
On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote: > Perhaps you could rename indices_size to indexes_size. Yeah, sorry -- forgot to mention that. I believe we decided to standardize on "indexes" as the plural of "index" (at least in user-visible stuff) a few releases go. Good catch :) -Neil ---(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] dbsize patch
On Thursday January 27 2005 12:08, Neil Conway wrote:
> On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> > Perhaps you could rename indices_size to indexes_size.
>
> Yeah, sorry -- forgot to mention that. I believe we decided to
> standardize on "indexes" as the plural of "index" (at least in
> user-visible stuff) a few releases go.
Attached patch identical except for s/indices/indexes/g.
Ed
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -r1.4 README.dbsize
1,2c1,2
< This module contains several functions that report the size of a given
< database object:
---
> This module contains several functions that report the amount of diskspace
> occupied by a given database object according to the stat function:
5a6,7
> int8 aggregate_relation_size(text)
> int8 indexes_size(text)
13c15
< The first two functions:
---
> The first four functions:
16a19,20
> SELECT aggregate_relation_size('pg_class');
> SELECT indexes_size('pg_class');
18,19c22,23
< take the name of the object (possibly schema-qualified, for relation_size),
< while these functions take object OIDs:
---
> take the name of the object (possibly schema-qualified, for relation_size
> and aggregate_relation_size), while these functions take object OIDs:
25,28c29,64
< Please note that relation_size and pg_relation_size report only the size of
< the selected relation itself; any subsidiary indexes or toast tables are not
< counted. To obtain the total size of a table including all helper files
< you'd have to do something like:
---
> The function relation_size() returns the size of a relation including the
> size of any toast tables and toast indexes. It does not include the
> size of dependent indexes.
>
> The function aggregate_relation_size() returns the size of a relation
> including the size of any toast tables, toast indexes, and dependent
> indexes.
>
> The function indexes_size() returns the size of all user-defined indexes
> for the given relation. It does not include the size of the relation
> data nor does it include the size of any relation toast data.
>
> Here's an example with a table called 'fat' that illustrates
> the differences between relation_size and aggregate_relation_size:
>
> select indexes_size(n.nspname||'.'||c.relname) as idx,
>relation_size(n.nspname||'.'||c.relname) as rel,
>aggregate_relation_size(n.nspname||'.'||c.relname) as total,
>c.relname, c.relkind as kind, c.oid, c.relfilenode as node
> from pg_class c, pg_namespace n
> where c.relnamespace = n.oid
> and (c.relname like 'fat%' or c.relname like 'pg_toast%')
> order by total, c.relname
>
> (snipped)
>idx | rel | total | relname| kind | oid | node
> -+-+-+--+--+---+---
>0 | 32768 | 32768 | pg_toast_59383_index | i| 59388 | 59388
>32768 | 704512 | 737280 | pg_toast_59383 | t| 59386 | 59386
>0 | 1818624 | 1818624 | fat_idx | i| 59389 | 59389
> 1818624 | 761856 | 2580480 | fat | r| 59383 | 59383
>
> Please note that pg_relation_size reports only the size of the selected
> relation itself; any subsidiary indexes or toast tables are not counted.
> To obtain the total size of a table including all helper files you'd
> have to do something like:
45a82,83
> Alternatively, just use the aggregate_relation_size() function.
>
51a90,95
>
> Wishlist:
> - include size of serial sequence objects
> - make pg_* functions include toast, indexes, and sequences;
> - maybe other dependent objects as well? triggers, procs, etc
>
Index: contrib/dbsize/dbsize.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.16
diff -r1.16 dbsize.c
24a25
> #include "utils/relcache.h"
36a38,39
> Datum aggregate_relation_size(PG_FUNCTION_ARGS);
> Datum indexes_size(PG_FUNCTION_ARGS);
44a48,49
> PG_FUNCTION_INFO_V1(aggregate_relation_size);
> PG_FUNCTION_INFO_V1(indexes_size);
283a289,387
> /*
> * Compute on-disk size of files for 'relation' according to the stat function,
> * optionally including heap data, index data, and/or toast data.
> */
> static int64
> calculate_size(Relation relation,
>bool countData,
>bool countToast,
>bool countIndices)
> {
> Relationidxrelation;
> Relationtoastrelation;
> Oid relnodeOid;
> Oid tblspcOid;
> Oid toastOid;
> boolhasIndices;
> int64 size = 0;
> List*indexoidlist;
> ListCell*idx;
>
> tblspcOid = relation->rd_rel->reltablespace;
> relnodeOid = relation->rd_rel->relfilenode;
> toastOid = relation->rd_rel->reltoastrelid;
>
