Re: [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: Michael Paesold wrote: 2) Implement a server-side function to get the savepoints from the server and query that before every release. I could not find a way to do this. Is there any interface to the list? Alvaro suggested to implement such a function. It is not there yet. I think you would have to access the sub xact stack, but I have not looked into that code for quite some time. http://archives.postgresql.org/pgsql-general/2004-10/msg00370.php First, I'm not of the opinion that it should automatically be turned off when running non-interactively. That's too much assuming of what the user wants, when this is a settable flag. However, it should be settable via a script to a definite state. So \reseterror will take an optional argument, "off" or "on", which sets it rather than toggles it. Discussion here last year showed some concern from people that this feature could bite people and is not really safe. Perhaps the best way would be to create three states: \reseterrors (on|off|auto) where auto means it's only active for interactive queries. (auto could be named interactive) The other problem is not stepping on other people's savepoints. The best solution we came up with was to check for savepoint commands ourselves, similar to the way psql already checks for transaction affecting commands, and handle things appropriately. Specifically, if someone issues a savepoint while in \reseterror mode, it switches off automatically*. Since the implementation of reseterror is pretty much a lazy shortcut to issuing savepoints yourself, it should be safe to say that you do not want to mix manual and automatic ones, and we'll back off (with a message) if you issue your own. Plus there will be a warning in the docs to be careful about mixing savepoints and the \reseterror method. * We could also switch it back on after rollback or release, but this would entail a little more tracking. Comments? I would prefer a solution, where the feature is not disabled as soon as I use my own savepoints. I like \reseterror because it prevents making typos from aborting my transaction. Explicit savepoints I rather use to try a whole bunch of statements and then decide if I want to commit so far. I can still make typos. If you don't want to, I can implement such a savepoint stack. I don't think it's that hard. The parsing, as you mentioned, should also not be too hard, because the infrastructure (removing white space) is already there. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] dbsize patch
If the C code for the prior dbsize patch is not acceptable for whatever
reason, here's a SQL-based patch to replace it. It's not a drop-in for
7.3/7.4 as I'd hoped, only an 8.1 patch. I believe it is functionally
equivalent to the C patch, but simpler, shorter, and probably a tad slower.
I also removed the README section on how to aggregate since it was
incomplete/incorrect (it didn't count toasted indices) and added a SQL
function that itemizes the size for a relation's table and index data
(helpful to us in identifying bloat, measuring performance, capacity
estimation, etc).
Ed
? contrib/dbsize/README.dbsize.aggregate_relation_components
? contrib/dbsize/dbsize.sql
? contrib/dbsize/dbsize.sql.in.aggregate_relation_components
? contrib/dbsize/dbsize.sql.in.using_pg_relation_size
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 28 Jan 2005 17:10:31 -
***
*** 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 on-disk size of a
! given database object in bytes:
***
*** 5,6
--- 5,10
int8 relation_size(text)
+ int8 indexes_size(text)
+ int8 aggregate_relation_size(text)
+
+ setof record relation_size_components(text)
***
*** 12,14
! The first two functions:
--- 16,20
! The first four functions take the name of the object (possibly
! schema-qualified for the latter three) and returns the size of the
! on-disk files in bytes.
***
*** 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:
--- 22,32
SELECT relation_size('pg_class');
+ SELECT indexes_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+
+ The next function, relation_size_components(), returns a set of rows
+ showing the sizes of the relations constituting the input relation.
+
+ SELECT relation_size_components('pg_class');
! These functions take object OIDs:
***
*** 24,49
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:
!
! SELECT *,
! pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
! FROM
! (SELECT pg_relation_size(cl.oid) AS tablesize,
! COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
! FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size(reltoastrelid)
! END AS toastsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
! WHERE ct.oid = cl.reltoastrelid))
! END AS toastindexsize
! FROM pg_class cl
! WHERE relname = 'foo') ss;
!
! This sample query utilizes the helper function pg_size_pretty(int8),
! which formats the number of bytes into a convenient string using KB, MB,
! GB. It is also contained in this module.
--- 36,47
+ The indexes_size() function returns the total size of the indices for a
+ relation, including any toasted indices.
+
+ The aggregate_relation_size() function returns the total size of the relation,
+ all its indices, and any toasted data.
+
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,
! use aggregate_relation_size().
Index: contrib/dbsize/dbsize.sql.in
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.sql.in,v
retrieving revision 1.4
diff -C1 -r1.4 dbsize.sql.in
*** contrib/dbsize/dbsize.sql.in 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/dbsize.sql.in 28 Jan 2005 17:10:32 -
***
*** 1,2
! CREATE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
--- 1,2
! CREATE OR REPLACE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
***
*** 4,10
! CREATE FUNCTION relation_size (text) RETURNS bigint
! AS 'MODULE_PATHNAME', 'relation_size'
! LANGUAGE C STRICT;
!
! CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_tablespace_size'
--- 4,6 --
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.
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);
+ PG_FUNCTION_INFO_V1(aggregate_relation_size);
+ PG_FUNCT
Re: [PATCHES] Continue transactions after errors in psql
On Sat, Jan 29, 2005 at 01:04:36PM +0100, Michael Paesold wrote: > Greg Sabino Mullane wrote: > > >Michael Paesold wrote: > >>2) Implement a server-side function to get the savepoints from the server > >>and query that before every release. > > > >I could not find a way to do this. Is there any interface to the list? > > Alvaro suggested to implement such a function. It is not there yet. I think > you would have to access the sub xact stack, but I have not looked into > that code for quite some time. > http://archives.postgresql.org/pgsql-general/2004-10/msg00370.php The only problem with this idea is that the function cannot be run when the transaction is in aborted state. Not sure if that is a problem or not. What happens if the user does SAVEPOINT foo; SLECT 1; ROLLBACK TO foo; all in one command in psql? I know psql sends that as three commands, so maybe it's not an issue. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Doing what he did amounts to sticking his fingers under the hood of the implementation; if he gets his fingers burnt, it's his problem." (Tom Lane) ---(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
