Re: [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: Finally had a chance to sit down at look at this afresh, and I'm pretty sure I've got all the kinks worked out this time. Apologies for not attaching, but my mail system is not working well enough at the moment. So, please try to break this patch: http://www.gtsm.com/pg/psql_error_recovery.diff Some suggestions in random order: * I think you should use PSQLexec instead of using PQexec directly. PSQLexec is used by all \-commands and prints out queries with -E, which is very helpful for debugging. -E display queries that internal commands generate * You do not check for the server version before activating \reseterror. - use PQserverVersion() to check for = 8 * Perhaps the name should be \reseterrors (plural)? Just my personal opinion though. * If I read the code correctly, you now don't destroy user savepoints anymore, but on the other hand, you do not release the psql savepoint after a user-defined savepoint is released. In other words, each time a user creates a savepoint, one psql savepoint is left on the subxact stack. I don't know if this is a real problem, though. * You have not yet implemented a way to savely put \reseterror in .psqlrc. I previously suggested an AUTO setting (additional to ON/OFF) that disables \reseterror when reading from a non-tty. So putting \reseterror AUTO in .psqlrc would be save. Otherwise, I could not find a way to break it. :-) Best Regards, Michael Paesold ---(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] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
Hello,
I used different format now. Documentation:
This patch is implementation of variables SQLERRM and SQLCODE for plpgsql
language. Variable SQLCODE contains five chars PostgreSQL Error Code,
SQLERRM contains relevant message last catched exception. All variables
are attached to plpgsql_block and have local scope. Default values are
'0' for SQLCODE and 'Sucessful completion' for SQLERRM. Some example
of using is in file test.sql.
Regards
Pavel Stehule
--
-- Test of built variables SQLERRM and SQLCODE
--
create or replace function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
raise notice '% %', SQLCODE, SQLERRM;
begin
raise exception 'last exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
return;
end; $_$ language plpgsql;
select trap_exceptions();
drop function trap_exceptions();
CREATE FUNCTION
NOTICE: P0001 first exception
NOTICE: 00 Sucessfull completation
NOTICE: P0001 last exception
trap_exceptions
-
(1 row)
DROP FUNCTION
diff -c -r src.old/gram.y src/gram.y
*** src.old/gram.y 2005-02-22 08:18:24.0 +0100
--- src/gram.y 2005-03-07 10:05:29.286336064 +0100
***
*** 80,85
--- 80,90
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ int sqlcode_varno;
+ int sqlerrm_varno;
+ } fict_vars;
List*list;
PLpgSQL_type*dtype;
PLpgSQL_datum *scalar;/* a VAR,
RECFIELD, or TRIGARG */
***
*** 95,101
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
!
%type declhdr decl_sect
%type varname decl_varname
%type str decl_renname
--- 100,106
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
! %type fict_vars fict_vars_sect
%type declhdr decl_sect
%type varname decl_varname
%type str decl_renname
***
*** 244,268
| ';'
;
! pl_block : decl_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;
new =
palloc0(sizeof(PLpgSQL_stmt_block));
new-cmd_type =
PLPGSQL_STMT_BLOCK;
! new-lineno = $3;
new-label =
$1.label;
new-n_initvars = $1.n_initvars;
new-initvarnos = $1.initvarnos;
! new-body = $4;
! new-exceptions = $5;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
;
decl_sect : opt_label
--- 249,288
| ';'
;
! pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect
exception_sect K_END
{
PLpgSQL_stmt_block *new;
new =
palloc0(sizeof(PLpgSQL_stmt_block));
new-cmd_type =
PLPGSQL_STMT_BLOCK;
! new-lineno = $4;
new-label =
$1.label;
new-n_initvars = $1.n_initvars;
new-initvarnos = $1.initvarnos;
! new-body = $5;
! new-exceptions = $6;
!
! new-sqlcode_varno =
$2.sqlcode_varno;
! new-sqlerrm_varno =
$2.sqlerrm_varno;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
Re: [PATCHES] Faster install-sh in C
Alvaro Herrera wrote: Well, apparently everyone says the system install is not portable and the discussion stops there. Well, who actually says that? I know that I was the one who actually coded up the current avoid-system-install-at-all-cost behavior, but only because we were too annoyed at the time to do more research. Now, as long as someone's spending time on it, it may be worth investigating what is wrong and provide an explanation for the benefit of the entire autotools-using community. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [INTERFACES] bcc32.mak for libpq broken? (distro 8.0.0)
Berényi Gábor wrote: The problem is something else. Gives the same message. Bruce Momjian pgman@candle.pha.pa.us ?rta: Ber?nyi G?bor wrote: Dear Bruce, Didn't work: Fatal bcc32.mak 169: No terminator specified for in-line file operator Look forward to hear of you again, Gabor OK, new bcc32.mak attached. It turns out the actions were indented with spaces instead of tabs. OK, I think I figured it out. Resource files are done differently in MS make and bcc make. I have attached a new file. Please test and let me know. If you can keep testing, we will eventually get this working. Thanks. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 # Makefile for Borland C++ 5.5 # Will build a Win32 static library libpq.lib #and a Win32 dynamic library libpq.dll with import library libpqdll.lib # Borland C++ base install directory goes here # BCB=d:\Borland\Bcc55 !MESSAGE Building the Win32 DLL and Static Library... !MESSAGE !IF $(CFG) == CFG=Release !MESSAGE No configuration specified. Defaulting to Release. !MESSAGE !ELSE !MESSAGE Configuration $(CFG) !MESSAGE !ENDIF !IF $(CFG) != Release $(CFG) != Debug !MESSAGE Invalid configuration $(CFG) specified. !MESSAGE You can specify a configuration when running MAKE !MESSAGE by defining the macro CFG on the command line. For example: !MESSAGE !MESSAGE make -DCFG=[Release | Debug] /f bcc32.mak !MESSAGE !MESSAGE Possible choices for configuration are: !MESSAGE !MESSAGE Release (Win32 Release DLL and Static Library) !MESSAGE Debug (Win32 Debug DLL and Static Library) !MESSAGE !ERROR An invalid configuration was specified. !ENDIF !IF $(OS) == Windows_NT NULL= !ELSE NULL=nul !ENDIF !IF $(CFG) == Debug DEBUG=1 OUTDIR=.\Debug INTDIR=.\Debug !ELSE OUTDIR=.\Release INTDIR=.\Release !ENDIF OUTFILENAME=blibpq USERDEFINES=FRONTEND;NDEBUG;WIN32;_WINDOWS;HAVE_VSNPRINTF;HAVE_STRDUP; CPP=bcc32.exe CPP_PROJ = -I$(BCB)\include;..\..\include -WD -c -D$(USERDEFINES) -tWM \ -a8 -X -w-use -w-par -w-pia -w-csu -w-aus -w-ccc !IFDEF DEBUG CPP_PROJ= $(CPP_PROJ) -Od -r- -k -v -y -vi- -D_DEBUG !else CPP_PROJ= $(CPP_PROJ) -O -Oi -OS -DNDEBUG !endif CLEAN : [EMAIL PROTECTED] $(INTDIR)\getaddrinfo.obj [EMAIL PROTECTED] $(INTDIR)\pgstrcasecmp.obj [EMAIL PROTECTED] $(INTDIR)\thread.obj [EMAIL PROTECTED] $(INTDIR)\inet_aton.obj [EMAIL PROTECTED] $(INTDIR)\crypt.obj [EMAIL PROTECTED] $(INTDIR)\noblock.obj [EMAIL PROTECTED] $(INTDIR)\md5.obj [EMAIL PROTECTED] $(INTDIR)\ip.obj [EMAIL PROTECTED] $(INTDIR)\fe-auth.obj [EMAIL PROTECTED] $(INTDIR)\fe-protocol2.obj [EMAIL PROTECTED] $(INTDIR)\fe-protocol3.obj [EMAIL PROTECTED] $(INTDIR)\fe-connect.obj [EMAIL PROTECTED] $(INTDIR)\fe-exec.obj [EMAIL PROTECTED] $(INTDIR)\fe-lobj.obj [EMAIL PROTECTED] $(INTDIR)\fe-misc.obj [EMAIL PROTECTED] $(INTDIR)\fe-print.obj [EMAIL PROTECTED] $(INTDIR)\fe-secure.obj [EMAIL PROTECTED] $(INTDIR)\pqexpbuffer.obj [EMAIL PROTECTED] $(INTDIR)\pqsignal.obj [EMAIL PROTECTED] $(OUTDIR)\libpqdll.obj [EMAIL PROTECTED] $(OUTDIR)\win32.obj [EMAIL PROTECTED] $(INTDIR)\wchar.obj [EMAIL PROTECTED] $(INTDIR)\encnames.obj [EMAIL PROTECTED] $(INTDIR)\pthread-win32.obj [EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME).lib [EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME)dll.lib [EMAIL PROTECTED] $(OUTDIR)\libpq.res [EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME).dll [EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME).tds [EMAIL PROTECTED] $(INTDIR)\pg_config_paths.h LIB32=tlib.exe LIB32_FLAGS= LIB32_OBJS= \ $(INTDIR)\win32.obj \ $(INTDIR)\getaddrinfo.obj \ $(INTDIR)\pgstrcasecmp.obj \ $(INTDIR)\thread.obj \ $(INTDIR)\inet_aton.obj \ $(INTDIR)\crypt.obj \ $(INTDIR)\noblock.obj \ $(INTDIR)\md5.obj \ $(INTDIR)\ip.obj \ $(INTDIR)\fe-auth.obj \ $(INTDIR)\fe-protocol2.obj \ $(INTDIR)\fe-protocol3.obj \ $(INTDIR)\fe-connect.obj \ $(INTDIR)\fe-exec.obj \ $(INTDIR)\fe-lobj.obj \ $(INTDIR)\fe-misc.obj \ $(INTDIR)\fe-print.obj \ $(INTDIR)\fe-secure.obj \ $(INTDIR)\pqexpbuffer.obj \ $(INTDIR)\pqsignal.obj \ $(INTDIR)\wchar.obj \ $(INTDIR)\encnames.obj \ $(INTDIR)\pthread-win32.obj RSC=brcc32.exe RSC_PROJ=-l 0x409 -i$(BCB)\include -fo$(INTDIR)\libpq.res LINK32=ilink32.exe LINK32_FLAGS = -Gn -L$(BCB)\lib;$(INTDIR); -x -Tpd -v LINK32_OBJS= $(INTDIR)\libpqdll.obj ALL: config $(OUTDIR) $(OUTDIR)\blibpq.dll $(OUTDIR)\blibpq.lib config: ..\..\include\pg_config.h pthread.h
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables
- You should write some regression tests for this functionality
- You should update the documentation
- Is there a reason why you've made the type of SQLCODE `text', rather
than integer?
Pavel Stehule wrote:
+ fict_vars_sect:
+ {
+ plpgsql_ns_setlocal(false);
+ PLpgSQL_variable *var;
+ var = plpgsql_build_variable(strdup(sqlcode), 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlcode_varno = var-dno;
+ var = plpgsql_build_variable(strdup(sqlerrm), 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
This shouldn't be strdup'ing its first argument (and even if it needed
to make a copy, it should use pstrdup). Also, my personal preference
would be to implement this without creating a new production (i.e. just
include it inline in the body of the pl_block production).
*** src.old/pl_exec.c 2005-02-24 02:11:40.0 +0100
--- src/pl_exec.c 2005-03-07 09:53:52.630243888 +0100
***
*** 809,814
--- 809,828
int i;
int n;
+ /* setup SQLCODE and SQLERRM */
+ PLpgSQL_var *var;
+
+ var = (PLpgSQL_var *) (estate-datums[block-sqlcode_varno]);
+ var-isnull = false;
+ var-freeval = false;
+ var-value = DirectFunctionCall1(textin, CStringGetDatum(0));
+
+ var = (PLpgSQL_var *) (estate-datums[block-sqlerrm_varno]);
+ var-isnull = false;
+ var-freeval = false;
+ var-value = DirectFunctionCall1(textin, CStringGetDatum(Sucessful completion));
`freeval' should be true, no? (Not sure it actually matters, but text is
certainly not pass-by-value).
***
*** 918,923
--- 932,957
[...]
+ var = (PLpgSQL_var *) (estate-datums[block-sqlcode_varno]);
+ var-value = DirectFunctionCall1(textin, CStringGetDatum(tbuf));
+
+ var = (PLpgSQL_var *) (estate-datums[block-sqlerrm_varno]);
+ var-value = DirectFunctionCall1(textin, CStringGetDatum(edata-message));
You should probably pfree() the old values before replacing them.
-Neil
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
Neil Conway [EMAIL PROTECTED] writes: - Is there a reason why you've made the type of SQLCODE `text', rather than integer? The value isn't an integer ... which gets back to my point that this is not compatible with Oracle's idea of SQLCODE and therefore we should *not* use that name for it. BTW: the patch has some memory-leak problems, I believe, because it is studiously not following the var-freeval protocol. Now that I look, it appears to be copied-and-pasted from some existing code that also gets this wrong :-( regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Continue transactions after errors in psql
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
* I think you should use PSQLexec instead of using PQexec directly. PSQLexec
is used by all \-commands and prints out queries with -E, which is very
helpful for debugging.
But these are not backslash commands, but almost directly analogous to the
BEGINs emitted by psql when in AutoCommit mode. On the other hand, it might
be neat to see all the savepoints psql will automagically create for you, so
I could go either way.
* You do not check for the server version before activating \reseterror.
- use PQserverVersion() to check for = 8
Thanks, it was in an earlier version, promise. This should be in command.c:
if (pset.sversion 8)
{
fprintf(stderr, _(The server version (%d) does not support savepoints.\n),
pset.sversion);
}
* Perhaps the name should be \reseterrors (plural)? Just my personal
opinion though.
Nah, less errors from people typing the wrong thing if we keep it shorter.
* You have not yet implemented a way to savely put \reseterror in .psqlrc. I
previously suggested an AUTO setting (additional to ON/OFF) that disables
\reseterror when reading from a non-tty. So putting \reseterror AUTO in
..psqlrc would be save.
Hmm...I suppose we could do that. Do we have anything else that does something
similar? I guess I'm not convinced that we need to change a switch's behavior
based on the tty status.
* If I read the code correctly, you now don't destroy user savepoints
anymore, but on the other hand, you do not release the psql savepoint after
a user-defined savepoint is released. In other words, each time a user
creates a savepoint, one psql savepoint is left on the subxact stack. I
don't know if this is a real problem, though.
Correct. More detail: we release our own temporary savepoint, unless the user
has successfully implemented their own savepoint. We need to do this so that we
do not clobber the user's savepoint. The larger problem is that our savepoints
and the user's savepoints tend to clobber each other. The normal flow of things
is to issue our savepoint, then the user's command, and then check to see if the
command succcessfully completed, and if we are still in a transaction. If we are
no longer in a transaction, we do nothing, as it means that our savepoint has
been
destroyed, so we don't need to worry about it. Otherwise, if the command failed,
we issue a rollback of our savepoint, which is guaranteed to be there because
the
user cannot have removed it, because their command did not succeed. Now the
tricky
part: If the transaction is still active, and the command succeeded, and the
command
was not SAVEPOINT, ROLLBACK TO, or RELEASE, we issue a release of our savepoint,
which is not strictly necessary, but is a good idea so we don't build up a large
chunk of old savepoints. Aside: we check if the command they issued was a
savepoint-
manipulating one by not parsing the SQL (yuck) but by simply checking the
cmdResult
string. Although there is no way to tell RELEASE from RELEASE TO from this
check,
we know it cannot be the former because we are still in a transaction. :) If it
was
one of those three commands, we do not issue a release. If they issued a
successful
release or rollback, then it just clobbered our savepoint, which now no longer
exists.
If it was a savepoint, we cannot release, or we will clobber their savepoint,
which
was created after ours. We could theoretically try and figure out beforehand if
they are issuing a savepoint command, but we must wrap it anyway in case it
fails so
we can rollback and not have it end the outer transaction. Thus, we create one
extra
savepoint every time the user issues a savepoint. Until they rollback or
release, of
course, in which case they also remove an equal number of our savepoints as
their
savepoints. So it doubles the number of savepoints a user currently has, but
this
is the price we pay for having the feature.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503070028
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCK+a6vJuQZxSWSsgRAsGRAJ99vJ0Mlzzl8MWBv262K//h0NasLwCgiBHZ
o2tgPvfwHR8zSJ1TAJ5/x30=
=itOf
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] fork() refactoring
Tom Lane wrote:
I'm worried about whether this doesn't break the EXEC_BACKEND case.
Most of the code you've moved out isn't applicable to Windows, but
the fflushes probably are
Right, which is why the patch adds fflushes to the Unix implementation
of internal_forkexec(). On reflection, it is probably more
straightforward to just invoke fork_process() from the Unix version of
internal_forkexec() -- attached is a revised patch that does this.
Please do not apply without some further portability testing.
I've checked EXEC_BACKEND on Unix, and it seems to work. I don't have
access to a BeOS box, so I can't test that (and I wouldn't be surprised
if the port was pretty bitrotted already). Is there anything else you
want me to test before committing?
I think it would be better to continue with your original thought of
passing a token into this code so that the EXEC_BACKEND case could be
handled too (the token would tell it which forkexec function to call).
The problem is that this was getting pretty complex; a lot of the
forkexec implementations want to pass data to the child process that is
private to the forkexec call site, for example. That means we still need
an #ifdef EXEC_BACKEND -- i.e. it's not such a win over the simpler
fork_process(). Since Magnus said he's thinking of refactoring this
anyway, I'm happy to leave it to him.
-Neil
Index: src/backend/port/beos/support.c
===
RCS file: /var/lib/cvs/pgsql/src/backend/port/beos/support.c,v
retrieving revision 1.11
diff -c -r1.11 support.c
*** src/backend/port/beos/support.c 25 Oct 2004 03:23:02 - 1.11
--- src/backend/port/beos/support.c 7 Mar 2005 23:36:03 -
***
*** 265,271
! /* The behavior of fork is borken on beos regarding shared memory. In fact
all shared memory areas are clones in copy on write mode in the new process.
We need to do a remapping of these areas. Just afer the fork we performe the
--- 265,271
! /* The behavior of fork is broken on beos regarding shared memory. In fact
all shared memory areas are clones in copy on write mode in the new process.
We need to do a remapping of these areas. Just afer the fork we performe the
Index: src/backend/postmaster/Makefile
===
RCS file: /var/lib/cvs/pgsql/src/backend/postmaster/Makefile,v
retrieving revision 1.19
diff -c -r1.19 Makefile
*** src/backend/postmaster/Makefile 5 Aug 2004 23:32:10 - 1.19
--- src/backend/postmaster/Makefile 7 Mar 2005 23:36:03 -
***
*** 12,18
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o syslogger.o
all: SUBSYS.o
--- 12,18
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = bgwriter.o fork_process.o pgarch.o pgstat.o postmaster.o syslogger.o
all: SUBSYS.o
Index: src/backend/postmaster/fork_process.c
===
RCS file: src/backend/postmaster/fork_process.c
diff -N src/backend/postmaster/fork_process.c
*** /dev/null 1 Jan 1970 00:00:00 -
--- src/backend/postmaster/fork_process.c 7 Mar 2005 23:36:03 -
***
*** 0
--- 1,80
+ /*
+ * fork_process.c
+ * A simple wrapper on top of fork(). This does not handle the
+ * EXEC_BACKEND case; it might be extended to do so, but it would be
+ * considerably more complex.
+ *
+ * Copyright (c) 1996-2005, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * $PostgreSQL$
+ */
+ #include postgres.h
+ #include postmaster/fork_process.h
+
+ #include unistd.h
+
+ /*
+ * Wrapper for fork(). Return values are the same as those for fork():
+ * -1 if the fork failed, 0 in the child process, and the PID of the
+ * child in the parent process.
+ */
+ pid_t
+ fork_process(void)
+ {
+ pid_t result;
+ #ifdef LINUX_PROFILE
+ struct itimerval prof_itimer;
+ #endif
+
+ /*
+ * Flush stdio channels just before fork, to avoid double-output
+ * problems. Ideally we'd use fflush(NULL) here, but there are still a
+ * few non-ANSI stdio libraries out there (like SunOS 4.1.x) that
+ * coredump if we do. Presently stdout and stderr are the only stdio
+ * output channels used by the postmaster, so fflush'ing them should
+ * be sufficient.
+ */
+ fflush(stdout);
+ fflush(stderr);
+
+ #ifdef LINUX_PROFILE
+ /*
+ * Linux's fork() resets the profiling timer in the child process. If
+ * we want to profile child processes then we need to save and restore
+ * the timer setting. This is a waste of time if not profiling,
+ * however, so only do it if commanded by specific -DLINUX_PROFILE
+ * switch.
+ */
+ getitimer(ITIMER_PROF, prof_itimer);
+ #endif
+
+ #ifdef __BEOS__
+ /* Specific beos actions before backend startup */
+ beos_before_backend_startup();
+ #endif
+
+
Re: [PATCHES] Display Pg buffer cache (WIP)
The latest iteration. I have added documentation and updated the expected output so that the regression tests pass. In addition, after looking at the various system view names, I decided that 'pg_cache_dump' does not fit in nicely - so chose an more Pg suitable name of 'pg_buffercache'. Some renaming of the backend functions happened too. Finally, since I was saving blocknum, it went into the view as well. Hopefully I am dealing with invalid buffer tags sensibly now. The per-buffer spin lock is still being held - altho it is obviously trivial to remove if not actually required. regards Mark P.s : remembered to use diff -c Mark Kirkwood wrote: Neil Conway wrote: Tom Lane wrote: It'd be possible to dispense with the per-buffer spinlocks so long as you look only at the tag (and perhaps the TAG_VALID flag bit). The tags can't be changing while you hold the BufMappingLock. That's what I had thought at first, but this comment in buf_internals.h dissuaded me: buf_hdr_lock must be held to examine or change the tag, flags, usage_count, refcount, or wait_backend_id fields. The comment already notes this isn't true if you've got the buffer pinned; it would be worth adding another exception for holding the BufMappingLock, IMHO. I'm dubious that there's any point in recording information as transient as the refcounts and dirtybits I think it's worth recording dirty bits -- it provides an indication of the effectiveness of the bgwriter, for example. Reference counts could be done away with, although I doubt it would have a significant effect on the time spent holding the lock. Let's suppose refcount is eliminated. I will then be examining the tag, flags and buf_id elements of the buffer. Holding the BufMappingLock prevents the tag changing, but what about the flags? In addition Tom pointed out that I am not examining the BM_TAG_VALID or BM_VALID flag bits (I am only checking if tag.blockNum equals InvalidBlockNumber). My initial thought is to handle !BM_TAG_VALID or !BM_VALID similarly to InvalidBlockNumber i.e all non buf_id fields set to NULL. Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) diff -Nacr pgsql.orig/doc/src/sgml/catalogs.sgml pgsql/doc/src/sgml/catalogs.sgml *** pgsql.orig/doc/src/sgml/catalogs.sgml Mon Mar 7 12:20:17 2005 --- pgsql/doc/src/sgml/catalogs.sgmlTue Mar 8 12:03:50 2005 *** *** 3875,3880 --- 3875,3885 tbody row + entrylink linkend=view-pg-buffercachestructnamepg_buffercache/structname/link/entry + entryshared buffer cache/entry + /row + + row entrylink linkend=view-pg-indexesstructnamepg_indexes/structname/link/entry entryindexes/entry /row *** *** 3917,3922 --- 3922,4021 /tbody /tgroup /table + /sect1 + + sect1 id=view-pg-buffercache + titlestructnamepg_buffercache/structname/title + + indexterm zone=view-pg-buffercache +primarypg_buffercache/primary + /indexterm + para +The view structnamepg_buffercache/structname provides access to +some information from the shared buffer cache. + /para + + para +There is one row for each buffer in the shared cache. Unused buffers are +shown with all fields null except structfieldbufferid/structfield. +Because the cache is shared by all the databases, there are pages from +relations not belonging to the current database. + /para + + + table +titlestructnamepg_buffercache/structname Columns/title + +tgroup cols=4 + thead + row + entryName/entry + entryType/entry + entryReferences/entry + entryDescription/entry + /row + /thead + tbody + row + entrybufferid/entry + entrytypeinteger/type/entry + entry/entry + entry +The buffer number. This is numbered 1 to varnameshared_buffers/varname. + /entry + /row + row + entryrelfilenode/entry + entrytypeoid/type/entry + entryliterallink linkend=catalog-pg-classstructnamepg_class/structname/link.relfilenode/literal/entry + entry + The on-disk file for the relation that this page came from. + /entry + /row + row + entryreltablespace/entry + entrytypeoid/type/entry + entry + literallink linkend=catalog-pg-tablespacestructnamepg_tablespace/structname/link.oid/literal + /entry + entryTablespace the corresponding relation is in./entry + /row + row + entryreldatabase/entry + entrytypeoid/type/entry + entryliterallink linkend=catalog-pg-databasestructnamepg_database/structname/link.oid/literal/entry + entry +Database the corresponding relation belongs to, or zero if the +relation is a globally-shared table/entry + /row
Re: [PATCHES] Display Pg buffer cache (WIP)
Mark Kirkwood wrote: + tupledesc = CreateTemplateTupleDesc(NUM_BUFFERCACHE_PAGES_ELEM, false); + TupleDescInitEntry(tupledesc, (AttrNumber) 1, bufferid, + INT4OID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 2, relfilenode, + OIDOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 3, reltablespace, + OIDOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 4, reldatabase, + OIDOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 5, relblockbumber, + NUMERICOID, -1, 0); I think this should be an int4, not numeric. Otherwise, looks good to me. Barring any objections, I'll apply this with a few stylistic tweaks and the numeric - int4 change tomorrow. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Display Pg buffer cache (WIP)
Neil Conway [EMAIL PROTECTED] writes: Mark Kirkwood wrote: +TupleDescInitEntry(tupledesc, (AttrNumber) 5, relblockbumber, + NUMERICOID, -1, 0); I think this should be an int4, not numeric. needs spell check too ;-) More generally, BlockNumber is unsigned and so int4 is not at all an accurate conversion. Perhaps OID would be a good choice even though it's horribly wrong on one level. Otherwise, looks good to me. Barring any objections, I'll apply this with a few stylistic tweaks and the numeric - int4 change tomorrow. I would rather see this as a contrib module. There has been *zero* consensus that we need this in core, nor any discussion about whether it might be a security hole. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: * You have not yet implemented a way to savely put \reseterror in .psqlrc. I previously suggested an AUTO setting (additional to ON/OFF) that disables \reseterror when reading from a non-tty. So putting \reseterror AUTO in ..psqlrc would be save. Hmm...I suppose we could do that. Do we have anything else that does something similar? I guess I'm not convinced that we need to change a switch's behavior based on the tty status. I do think so. In it's current state, would you yourself put \reseterror in your .psqlrc? Or even an /etc/psqlrc? It would break all my scripts that must either succeed or fail -- now they will produce garbage in my databases when something goes wrong! In my opinion, the behaviour should depend on tty in all settings, but I am o.k. with an AUTO setting, because so it's at least usable. I think without tty-detection, the patch just conflicts with PostgreSQL philosophy that the user should be kept save from unintended data-destruction. The SQL-Standard itself says that errors inside transactions should only rollback the last statement, if possible. So why is that not implemented in PostgreSQL? What I read from past discussions here, is because it's just unsave and will lead to data-garbage if you aren't very careful. * If I read the code correctly, you now don't destroy user savepoints anymore, but on the other hand, you do not release the psql savepoint after a user-defined savepoint is released. In other words, each time a user creates a savepoint, one psql savepoint is left on the subxact stack. I don't know if this is a real problem, though. Correct. More detail: we release our own temporary savepoint, unless the user has successfully implemented their own savepoint... The current way is ok for me at the moment. I still think there is a better way (parsing statements like it's already done for no-transaction-allowed-statements), but hey, as soon as your patch will be applied, I can myself propose another patch to improve this. ;-) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Display Pg buffer cache (WIP)
Tom Lane wrote: Perhaps OID would be a good choice even though it's horribly wrong on one level. Either that or add unsigned numeric types to PG :-) I would rather see this as a contrib module. I don't really have an opinion either way. Does anyone else have thoughts on this? There has been *zero* consensus that we need this in core, nor any discussion about whether it might be a security hole. ISTM if it can only be invoked by the superuser, it should be safe enough. -Neil ---(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] Continue transactions after errors in psql
Michael Paesold [EMAIL PROTECTED] writes: I do think so. In it's current state, would you yourself put \reseterror in your .psqlrc? Or even an /etc/psqlrc? It would break all my scripts that must either succeed or fail -- now they will produce garbage in my databases when something goes wrong! This is sounding a whole lot like the concerns that prompted us to reject server-side autocommit a little while ago. The problem with rejiggering error-handling behavior is that you *will* break existing code, on a rather fundamental level, and it's not even obvious that it's broken until after things have gone badly wrong. I don't have a good solution, but I do think that you need to set things up so that an application or script must invoke the new behavior explicitly. Hidden defaults that silently change such behavior look like land mines waiting to be stepped on. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
