[PATCHES] Clarify use of NOW() in pl/pgsql docs
Folks, This one from Ben Calvert. It uses the (imho clearer) NOW() rather than 'NOW' in a PL/PgSQL function example. 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/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.58 diff -c -r1.58 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 22 Jan 2005 22:56:36 - 1.58 --- doc/src/sgml/plpgsql.sgml 27 Jan 2005 10:24:42 - *** *** 2602,2608 END IF; -- Remember who changed the payroll when ! NEW.last_date := 'now'; NEW.last_user := current_user; RETURN NEW; END; --- 2602,2608 END IF; -- Remember who changed the payroll when ! NEW.last_date := NOW(); NEW.last_user := current_user; RETURN NEW; END; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 24 January 2005 23:58 To: Dave Page Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] pg_autovacuum Win32 Service startup delay Dave Page dpage@vale-housing.co.uk writes: When starting as a service at boot time on Windows, pg_autovacuum may fail to start because the PostgreSQL service is still starting up. This patch causes the service to attempt a second connection 30 seconds after the initial connection failure before giving up entirely. Hm. In event that the system crashed beforehand, it could require much more than 30 seconds to finish replaying the old WAL log. So the above doesn't seem super robust to me. Would it be reasonable to try every 30 seconds for five minutes, or some such? (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) OK, revised patch attached. This version tries every 30 seconds for 5 minutes then gives up. Regards, Dave. startup_delay.diff Description: startup_delay.diff ---(end of broadcast)--- TIP 8: explain analyze is your friend
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; Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Regards, Andreas ---(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
Andreas Pflug [EMAIL PROTECTED] writes: Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Your suggestion would be more compelling if the example were correct ;-). Consider more than one index on the same table. This does raise the question of whether the C implementations count the right things either --- I have not looked. Neil, I trust you're going to review this and not just apply it? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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.
Attached patch identical except for s/indices/indexes/g.
Attached is the same patch as context diff. (prior send from unregistered
email address)
Ed
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/README.dbsize 27 Jan 2005 08:49:25 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the amount of diskspace
! occupied by a given database object according to the stat function:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 aggregate_relation_size(text)
+ int8 indexes_size(text)
***
*** 12,14
! The first two functions:
--- 14,16
! The first four functions:
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 18,24
SELECT relation_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+ SELECT indexes_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size
! and aggregate_relation_size), while these functions take object OIDs:
***
*** 24,29
! 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:
--- 28,65
! 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:
***
*** 45,46
--- 81,84
+ Alternatively, just use the aggregate_relation_size() function.
+
This sample query utilizes the helper function pg_size_pretty(int8),
***
*** 51
--- 89,95
into any database using dbsize.sql.
+
+ 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 -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c 1 Jan 2005 05:43:05 - 1.16
--- contrib/dbsize/dbsize.c 27 Jan 2005 08:49:26 -
***
*** 24,25
--- 24,26
#include utils/syscache.h
+ #include utils/relcache.h
***
*** 36,37
--- 37,40
Datum relation_size(PG_FUNCTION_ARGS);
+ Datum aggregate_relation_size(PG_FUNCTION_ARGS);
+ Datum indexes_size(PG_FUNCTION_ARGS);
***
*** 44,45
--- 47,50
PG_FUNCTION_INFO_V1(relation_size);
+
Re: [PATCHES] Fix for SHGetSpecialFolderPath
Magnus Hagander [EMAIL PROTECTED] writes: Attached patch fixes the SHGetSpecialFolderPath issues on NT4. It does this by using SHGetFolderPath instead of SHGetSpecialFolderPath, and linking to shfolder.dll instead of shell32.dll. shfolder.dll exists as a redistributable from Microsoft in case it is needed on a system, and it's supported on all current windows platforms. Applied. 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])
Re: [PATCHES] [HACKERS] WAL: O_DIRECT and multipage-writer
Hi everyone, I gave this a try with DBT-2, but got a core dump on our ia64 system. I hope this isn't a random thing, like I ran into previously. Maybe I'll try again, but postgres dumped core. Binary and core here: http://developer.osdl.org/markw/pgsql/core/2morefiles.tar.bz2 #0 FunctionCall2 (flinfo=0x0, arg1=0, arg2=0) at fmgr.c:1141 1141result = FunctionCallInvoke(fcinfo); (gdb) bt #0 FunctionCall2 (flinfo=0x0, arg1=0, arg2=0) at fmgr.c:1141 #1 0x403bdb80 in FunctionCall2 (flinfo=Cannot access memory at address 0x0 ) at fmgr.c:1141 #2 0x403bdb80 in FunctionCall2 (flinfo=Cannot access memory at address 0x0 ) at fmgr.c:1141 Over and over again, so I'll keep the backtrace short. Mark ---(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 6:59, Andreas Pflug wrote: 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; Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Well, it seems quite a bit more complicated than that to me, but I'm going to rework the patch so it drops into 7.3 as well and resubmit shortly. Ed ---(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] WAL: O_DIRECT and multipage-writer
Hmm... I don't remember specifying a datatype. I suppose whatever the default one is. :) I'll be happy to test again, just let me know. Mark On Fri, Jan 28, 2005 at 06:28:32AM +0900, ITAGAKI Takahiro wrote: Thanks for testing, Mark! I gave this a try with DBT-2, but got a core dump on our ia64 system. I hope this isn't a random thing, like I ran into previously. Maybe I'll try again, but postgres dumped core. Sorry, this seems to be my patch's bug. Which datatype did you compile with? LP64, ILP64, or LLP64? If you used LLP64, I think the cause is buffer alignment routine because of sizeof(long) != sizeof(void*). I'll fix it soon... ITAGAKI Takahiro ---(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] WAL: O_DIRECT and multipage-writer
Thanks for testing, Mark! I gave this a try with DBT-2, but got a core dump on our ia64 system. I hope this isn't a random thing, like I ran into previously. Maybe I'll try again, but postgres dumped core. Sorry, this seems to be my patch's bug. Which datatype did you compile with? LP64, ILP64, or LLP64? If you used LLP64, I think the cause is buffer alignment routine because of sizeof(long) != sizeof(void*). I'll fix it soon... ITAGAKI Takahiro ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] heap_modifytuple
On Wed, 2004-09-08 at 16:31 -0400, Alvaro Herrera wrote: Here is a simple patch that changes heap_modifytuple to require a TupleDesc instead of a Relation (driven off a comment in the same function). Patch applied to HEAD. I went to the trouble of checking the call sites of heap_modifytuple() and using the TupleDesc of the relation if we have already fetched it, rather than calling RelationGetDescr() again. Thanks for the patch. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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. Applied to HEAD. Thanks for the patch. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] dbsize patch
On Thursday January 27 2005 2:12, Ed L. wrote: Well, it seems quite a bit more complicated than that to me, but I'm going to rework the patch so it drops into 7.3 as well and resubmit shortly. Too much trouble for now. Neil, if the latest patch is acceptable or useful for others as-is, great, please apply. Ed ---(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] Continue transactions after errors in psql
On Tuesday 25 January 2005 22:07, Greg Sabino Mullane wrote:
Attached is a patch that takes advantage of savepoints to enable
transactions to continue even after errors in psql. The name of it
is \reseterror, and it is off by default. It's backwards compatible,
and allows things like this to work on 8.0 and up servers:
\reseterror
BEGIN;
DELETE FROM foobar;
INSERT INTO foobar(a) VALUES(1);
ISNER INTO foobar(a) VALUES(2);
INSERT INTO foobar(a) VALUES(3);
COMMIT;
Doing a SELECT(a) FROM foobar will show two values, 1 and 3. This
is a great help for those of us that tend to type typos into our
psql session, and end up cursing as we have to restart our current
transaction. :)
I've been testing this patch and found the following bug:
test=# \reseterror
Reset error is on.
test=# begin;
BEGIN
test=# select * from t;
c
---
1
(1 row)
test=# delete from t;
DELETE 1
test=# select * from tt;
ERROR: relation tt does not exist
ERROR: relation tt does not exist
test=# select * from t;
c
---
(0 rows)
test=# commit;
COMMIT
ERROR: RELEASE SAVEPOINT may only be used in transaction blocks
ERROR: RELEASE SAVEPOINT may only be used in transaction blocks
I've attached a revised patch which fixes the problem, however I'm sure there
is a better way. Thanks to Neil for putting up with me on irc :-)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Index: command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.139
diff -c -r1.139 command.c
*** command.c 1 Jan 2005 05:43:08 - 1.139
--- command.c 28 Jan 2005 06:42:03 -
***
*** 646,651
--- 646,672
puts(gettext(Query buffer reset (cleared).));
}
+ /* \reseterror -- use savepoints to make transaction errors recoverable */
+ else if (strcmp(cmd, reseterror) == 0)
+ {
+ if (pset.sversion 8)
+ {
+ printf(gettext(The server version (%d) does not support savepoints.\n),
+ pset.sversion);
+ }
+ else
+ {
+ pset.reseterror = !pset.reseterror;
+ if (!quiet)
+ {
+ if (pset.reseterror)
+ puts(gettext(Reset error is on.));
+ else
+ puts(gettext(Reset error is off.));
+ }
+ }
+ }
+
/* \s save history in a file or show it on the screen */
else if (strcmp(cmd, s) == 0)
{
Index: common.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.95
diff -c -r1.95 common.c
*** common.c 1 Jan 2005 05:43:08 - 1.95
--- common.c 28 Jan 2005 06:42:03 -
***
*** 941,950
bool
SendQuery(const char *query)
{
! PGresult *results;
TimevalStruct before,
after;
bool OK;
if (!pset.db)
{
--- 941,951
bool
SendQuery(const char *query)
{
! PGresult *results, *res;
TimevalStruct before,
after;
bool OK;
+ PGTransactionStatusType tstatus;
if (!pset.db)
{
***
*** 973,979
SetCancelConn();
! if (PQtransactionStatus(pset.db) == PQTRANS_IDLE
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
--- 974,982
SetCancelConn();
! tstatus = PQtransactionStatus(pset.db);
!
! if (PQTRANS_IDLE == tstatus
!GetVariableBool(pset.vars, AUTOCOMMIT)
!command_no_begin(query))
{
***
*** 987,992
--- 990,1010
}
PQclear(results);
}
+ else {
+ /* If we are in error recovery mode and inside a transaction,
+ possibly issue a temporary savepoint */
+ if (PQTRANS_INTRANS==tstatus pset.reseterror) {
+ res = PQexec(pset.db, SAVEPOINT psql_savepoint);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ psql_error(%s, PQerrorMessage(pset.db));
+ PQclear(res);
+ ResetCancelConn();
+ return false;
+ }
+ PQclear(res);
+ }
+ }
if (pset.timing)
GETTIMEOFDAY(before);
***
*** 1001,1008
/* but printing results isn't: */
if (OK)
! OK = PrintQueryResults(results);
!
PQclear(results);
/* Possible microtiming output */
--- 1019,1049
/* but printing results isn't: */
if (OK)
! OK = PrintQueryResults(results);
!
! /* If in error recovery mode, release the savepoint */
!
! if (PQTRANS_INTRANS==tstatus pset.reseterror) {
! tstatus = PQtransactionStatus(pset.db);
!
! if (PQTRANS_INERROR==tstatus)
! res = PQexec(pset.db, ROLLBACK TO psql_savepoint);
! else if (PQTRANS_IDLE==tstatus)
! /* COMMITing leaves us in PQTRANS_IDLE so we can't release the save point here */
! res = PQexec(pset.db, SELECT 1);
! else
! res = PQexec(pset.db, RELEASE psql_savepoint);
!
! if (PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! psql_error(%s, PQerrorMessage(pset.db));
! PQclear(res);
! ResetCancelConn();
! return false;
! }
