[HACKERS] CREATE TYPE with two args
I want to create a data type that have tow arguments in the defenition, like the varchar type: create table mytmp(name varchar(10)); I like to do it like this: create table mytmp(name myvarchar(10,en_US)); how can it be done by CREATE TYPE I want to implement the internal functions (compare, ...). I faunded the documentation but I can't see any thing that help me to create a type same as varchar( with argument) Notice: I don't mean using CREATE TYPE myvarchar AS( lenght integer, collate char(10)) Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] CREATE TYPE with two args
Ameen - Etemady wrote: I want to create a data type that have tow arguments in the defenition, like the varchar type: This is not possible. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_arch.c call to sleep()
We have the following warning on Windows: pgarch.c:349: warning: implicit declaration of function `sleep' To fix it we could include the right header (which appears to be stdlib.h in the Windows/Mingw case), or we could replace the call by a call to pg_usleep(). stdlib.h is included automatically by c.h, so that surely won't fix it. I have some recollection that we invented pg_usleep in part because we wanted to not use sleep() at all in the backend, but I don't recall why (and the reasoning might not apply to the archiver process, anyway). win32 signal handling won't interrupt sleep(), just pg_usleep(). //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Memory Context problems...
On Sun, 2004-11-07 at 21:31, Tom Lane wrote: Katsaros Kwn/nos [EMAIL PROTECTED] writes: More precisely,when I call (my_)SPI_prepare the following message comes exactly at the point where (my_)SPI_end_call(true) is called: WARNING: problem in alloc set my_SPI Exec: detected write past chunk end in block 0x830b7e8, chunk 0x830c058 WARNING: problem in alloc set my_SPI Exec: detected write past chunk end in block 0x8307720, chunk 0x830b6cc Any ideas? I think it is something with the memory contexts but obviously this is not enough to solve the problem. I'd bet on buffer-overrun problems. Check your allocation request sizes against what's actually being used. I tried to increase the allocated memory using ALLOCSET_DEFAULT_*SIZE just to see what would happen, but nothing.What is returned is a Plan node and some (3 in my example) Query nodes. All inside a List. Are these too big to fit? When my user defined procedure (that calls my_SPI functions) returns, the following message also appears: WARNING: problem in alloc set my_SPI Results: detected write past chunk end in block 0x8313550, chunk 0x8315140 which is obviously about the same kind of problem. The thing is that I noticed that this latter message appears as many times as I palloc inside my_SPI_copy_results() function which is an extension of _SPI_copy_plan(). I cannot understand what the problem is since I do similar things with _SPI_copy_plan. I palloc() exactly at the same places with the original func. Is it that I use Lists as a mean of returning multiple objects to the user defined function or even that I call another function that pallocs before calling my_SPI_copy_results() (this other function produces the Query nodes)? Are there any other places in the source other than (my_)spi.c, (my_)spi.h and xact.c (AtEOXact_(my_)SPI() call) that I should make an entry regarding my code? Thanks for your interest! Ntinos ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: What do you think is broken about fragmented UDP packets? Fragmentation happens at the IP protocol level, the kernel is responsible for reassembly. There's nothing for the application level to handle. And, by the same token, on platforms where it is broken there is nothing we can do about it. Like what? If the OS can not handle UDP reassembly then we have some other problems around I think the OS breakage is a non issue here. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] NoMovementScanDirection
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Ah, okay. I'll remove gistscancache() then, as this seems to be dead code. Is there someone out there that can instrument the code with Rational Coverage in order to see how much dead code is still there ? Or at least see how much code is used during the make check. BTW: is there some free tool that do the Rational Coverage work ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cygwin build failure
Hi,
Related to the definition of __DLL_IMPORT below, the cygwin port of
glib/gmodule just does the following:
#define G_MODULE_IMPORT extern
#ifdef G_PLATFORM_WIN32
# define G_MODULE_EXPORT __declspec(dllexport)
#else /* !G_PLATFORM_WIN32 */
# define G_MODULE_EXPORT
#endif /* !G_PLATFORM_WIN32 */
Also, it doesn't make any distinction whether you are building a DLL or not.
The following example has been tested and it works (I've done the
example without gmodule, as we wouldn't want to use that within
postgresql anyways):
dll.h:
extern void foo();
dll.c:
#include dll.h
__declspec(dllexport) void foo() {
return;
}
main.c:
#include dll.h
int main(int argc, char **argv) {
foo();
return 0;
}
This is with recent GCC (3.3.3 on my system), but it probably also works
with older GCC versions.
Don't know if this information is useful in simplying things...
Maarten
Reini Urban wrote:
#ifdef BUILDING_DLL
# ifndef __GNUC__
# define __DLL_IMPORT __declspec(dllimport)
# else
# define __DLL_IMPORT __attribute__((dllimport)) extern
# endif
#else
# define __DLL_IMPORT
#endif
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [COMMITTERS] pgsql: Remove: * Allow database recovery where
Just curious, but in what sort of circumstance could this happen? Permissions problems, that sort of thing? On Sat, 6 Nov 2004, Bruce Momjian wrote: Log Message: --- Remove: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. Modified Files: -- pgsql/doc: TODO (r1.1385 - r1.1386) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1385r2=1.1386) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [HACKERS] [COMMITTERS] pgsql: Remove: * Allow database recovery
Marc G. Fournier wrote: Just curious, but in what sort of circumstance could this happen? Permissions problems, that sort of thing? Restoring a dump to another system that doesn't have the same directories to create the tablespaces. --- On Sat, 6 Nov 2004, Bruce Momjian wrote: Log Message: --- Remove: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. Modified Files: -- pgsql/doc: TODO (r1.1385 - r1.1386) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1385r2=1.1386) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-www] pg_autovacuum is nice ... but ...
On 11/4/2004 5:44 PM, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: Moved to -hackers where this belongs :) On Fri, 5 Nov 2004, Justin Clift wrote: Would making max_fsm_relations and max_fsm_pages dynamically update themselves whilst PostgreSQL runs be useful? Possibly, but it isn't happening in the foreseeable future, for the same reason that we don't auto-update shared_buffers and the other shared memory sizing parameters: we can't resize shared memory on the fly. I'm not sure if I like this one too much ... but it would be nice if something like this triggered a warning in the logs, maybe a feature of pg_autovacuum itself? autovacuum would probably be a reasonable place to put it. We don't currently have any good way for autovacuum to get at the information, but I suppose that an integrated autovacuum daemon could do so. Don't know why this must be an integrated autovacuum. Can't the info about the FSM usage be presented as system views? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [COMMITTERS] pgsql: Remove: * Allow database recovery
On Mon, 8 Nov 2004, Bruce Momjian wrote: Marc G. Fournier wrote: Just curious, but in what sort of circumstance could this happen? Permissions problems, that sort of thing? Restoring a dump to another system that doesn't have the same directories to create the tablespaces. 'k, that's what I thought, just wanted to clarify ... stupid question then ... if such a case happens, do we send a WARNING to let ppl know that the load didn't quite go as the dump went? --- On Sat, 6 Nov 2004, Bruce Momjian wrote: Log Message: --- Remove: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. Modified Files: -- pgsql/doc: TODO (r1.1385 - r1.1386) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1385r2=1.1386) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [HACKERS] [COMMITTERS] pgsql: Remove: * Allow database recovery
Marc G. Fournier wrote: On Mon, 8 Nov 2004, Bruce Momjian wrote: Marc G. Fournier wrote: Just curious, but in what sort of circumstance could this happen? Permissions problems, that sort of thing? Restoring a dump to another system that doesn't have the same directories to create the tablespaces. 'k, that's what I thought, just wanted to clarify ... stupid question then ... if such a case happens, do we send a WARNING to let ppl know that the load didn't quite go as the dump went? Yes, they get a warning because the tablespace create failed. When we had a TABLESPACE clause in create (before default_tablespace), all the CREATEs would fail leading to a useless restore. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] latest cygwin build failure
with CVS tip in contrib/spi: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -DREFINT_VERBOSE -I. -I../../src/include -c -o timetravel.o timetravel.c dlltool --export-all --output-def timetravel.def timetravel.o dllwrap -o timetravel.dll --def timetravel.def timetravel.o ../../src/utils/dllinit.o -L../../src/backend -lpostgres timetravel.o(.text+0x10cb):timetravel.c: undefined reference to `_pg_strcasecmp' collect2: ld returned 1 exit status dllwrap: gcc exited with status 1 make[1]: *** [timetravel.dll] Error 1 rm refint.o autoinc.o timetravel.o moddatetime.o insert_username.o make[1]: Leaving directory `/home/adunstan/pgbf/root/HEAD/pgsql.blurfl/contrib/spi' cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] View pg_stat_activity slow to get up to date
I checked the FAQ and docs but haven't found anything definitive. This is my SQL test script: SELECT pg_backend_pid(); SELECT * FROM pg_stat_activity order by procpid; When I run psql reading that I find that my backend procpid is not in the list. I know that I can see it if I can introduce a little sleep (1 second) between the connection and the reading of pg_stat_activity. My question is this. Is there a more reliable way to see if a particular backend is still alive? I am trying to do a locking system and this is necessary to make it work. I think that in actual sessions I will be OK but my unit test fails most of the time because of this. I will explain my scheme in case there is a better way to do what I want. Note that using transactions won't work in my application because I never know if I am already in one or not or if one will start while I am in this process. What I do is create a table that has, among other things, a serial, primary key and a pid_lock field. I add a rule like this: CREATE OR REPLACE RULE session_pid_lock AS ON UPDATE TO session WHERE EXISTS ( SELECT 1 FROM pg_stat_activity WHERE pg_stat_activity.procpid = old.pid_lock AND pg_stat_activity.procpid != pg_backend_pid()) DO INSTEAD NOTHING; Now all I do to grab the lock is update pid_lock with my own backend PID. I then test it to see if it has mine or someone else's. Now I know whether to fail, retry or whatever. As soon as the first DB connection drops I can grab the lock. Right now I am modifying my lock class (It's in Python) to add a 1 second sleep before trying to grab an existing session. That seems to work but I hope I can do better. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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: [HACKERS] latest cygwin build failure
Andrew Dunstan schrieb:
with CVS tip in contrib/spi:
ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels
-fno-strict-aliasing -DREFINT_VERBOSE -I. -I../../src/include -c -o
timetravel.o timetravel.c
dlltool --export-all --output-def timetravel.def timetravel.o
dllwrap -o timetravel.dll --def timetravel.def timetravel.o
../../src/utils/dllinit.o -L../../src/backend -lpostgres
timetravel.o(.text+0x10cb):timetravel.c: undefined reference to
`_pg_strcasecmp'
collect2: ld returned 1 exit status
dllwrap: gcc exited with status 1
make[1]: *** [timetravel.dll] Error 1
rm refint.o autoinc.o timetravel.o moddatetime.o insert_username.o
make[1]: Leaving directory
`/home/adunstan/pgbf/root/HEAD/pgsql.blurfl/contrib/spi'
This was addressed in my patch I sent.
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/
Index: contrib/spi/Makefile
===
RCS file: /projects/cvsroot/pgsql/contrib/spi/Makefile,v
retrieving revision 1.24
diff -u -b -r1.24 Makefile
--- contrib/spi/Makefile20 Aug 2004 20:13:08 - 1.24
+++ contrib/spi/Makefile8 Nov 2004 05:34:53 -
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/contrib/spi/Makefile,v 1.24 2004/08/20 20:13:08 momjian
Exp $
+# $PostgreSQL: pgsql-server/contrib/spi/Makefile,v 1.24 2004/08/20 20:13:08
momjian Exp $
MODULES = autoinc insert_username moddatetime refint timetravel
DATA_built = $(addsuffix .sql, $(MODULES))
@@ -17,3 +17,5 @@
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
+
+SHLIB_LINK += -L$(top_builddir)/src/port -lpgport
Index: src/bin/pg_ctl/pg_ctl.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_ctl/pg_ctl.c,v
retrieving revision 1.45
diff -u -b -r1.45 pg_ctl.c
--- src/bin/pg_ctl/pg_ctl.c 4 Nov 2004 22:25:12 - 1.45
+++ src/bin/pg_ctl/pg_ctl.c 8 Nov 2004 05:35:21 -
@@ -22,6 +22,7 @@
#include getopt_long.h
#if defined(__CYGWIN__)
+#include sys/cygwin.h
#include windows.h
/* Cygwin defines WIN32 in windows.h, but we don't want it. */
#undef WIN32
@@ -820,6 +821,9 @@
{
static char cmdLine[MAXPGPATH];
int ret;
+#ifdef __CYGWIN__
+ static char buf[MAXPGPATH];
+#endif
if (registration)
{
@@ -839,6 +843,11 @@
exit(1);
}
}
+#ifdef __CYGWIN__
+ /* need to convert to windows path */
+ cygwin_conv_to_full_win32_path (cmdLine, buf);
+ strcpy(cmdLine, buf);
+#endif
if (registration)
{
Index: src/interfaces/libpq/Makefile
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/Makefile,v
retrieving revision 1.120
diff -u -b -r1.120 Makefile
--- src/interfaces/libpq/Makefile 16 Oct 2004 22:52:49 - 1.120
+++ src/interfaces/libpq/Makefile 8 Nov 2004 05:35:28 -
@@ -31,6 +31,10 @@
md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o \
$(filter crypt.o getaddrinfo.o inet_aton.o open.o snprintf.o
strerror.o, $(LIBOBJS))
+ifeq ($(PORTNAME), cygwin)
+override shlib = cyg$(NAME)$(DLSUFFIX)
+endif
+
ifeq ($(PORTNAME), win32)
OBJS += win32.o libpqrc.o
libpqrc.o : libpq.rc
Index: src/template/cygwin
===
RCS file: /projects/cvsroot/pgsql/src/template/cygwin,v
retrieving revision 1.4
diff -u -b -r1.4 cygwin
--- src/template/cygwin 9 Oct 2003 14:40:36 - 1.4
+++ src/template/cygwin 8 Nov 2004 06:59:51 -
@@ -1 +1,6 @@
SRCH_LIB=/usr/local/lib
+# This is required to link pg_dump because it finds pg_toupper() in
+# libpq and pgport
+LDFLAGS=-Wl,--allow-multiple-definition -Wl,--enable-auto-import
+# --enable-auto-import gets rid of a diagnostics linker message
+LDFLAGS_SL=-Wl,--enable-auto-import
Index: src/include/port.h
===
RCS file: /projects/cvsroot/pgsql/src/include/port.h,v
retrieving revision 1.65
diff -u -b -r1.65 port.h
--- src/include/port.h 6 Nov 2004 01:16:14 - 1.65
+++ src/include/port.h 8 Nov 2004 07:00:42 -
@@ -72,12 +72,17 @@
extern int find_other_exec(const char *argv0, const char *target,
const char *versionstr, char *retpath);
-#if defined(WIN32) || defined(__CYGWIN__)
-#define EXE .exe
-#define DEVNULL nul
+#if defined(WIN32) !defined(__CYGWIN__)
+# define EXE .exe
+# define DEVNULL nul
#else
-#define EXE
-#define DEVNULL /dev/null
+# if defined(__CYGWIN__)
+# define EXE .exe
+# define DEVNULL /dev/null
+# else
+# define EXE
+# define DEVNULL /dev/null
+# endif
#endif
/*
@@ -89,13 +94,13 @@
* See the Notes section about quotes at:
* http://home.earthlink.net/~rlively/MANUALS/COMMANDS/C/CMD.HTM
*/
-#ifdef WIN32
+#if defined(WIN32)
Re: [HACKERS] View pg_stat_activity slow to get up to date
On 11/8/2004 12:03 PM, D'Arcy J.M. Cain wrote: I checked the FAQ and docs but haven't found anything definitive. This is my SQL test script: SELECT pg_backend_pid(); SELECT * FROM pg_stat_activity order by procpid; When I run psql reading that I find that my backend procpid is not in the list. I know that I can see it if I can introduce a little sleep (1 second) between the connection and the reading of pg_stat_activity. That is because the way your backend gets the information from the pgstat daemon process is by reading a file, which is at maximum written by that process every 500 msec. You will hardly ever see your own query. My question is this. Is there a more reliable way to see if a particular backend is still alive? I am trying to do a locking system and this is necessary to make it work. I think that in actual sessions I will be OK but my unit test fails most of the time because of this. All these statistics are unreliable by design, as explained numerous times on this list. In short, a backend should never be slowed down because the pgstat process can't swallow the stats fast enough. That is the reason for using UDP in the first place. So the kernel is allowed to drop stats packets, but not to block a backend. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-www] pg_autovacuum is nice ... but ...
Jan Wieck [EMAIL PROTECTED] writes: On 11/4/2004 5:44 PM, Tom Lane wrote: autovacuum would probably be a reasonable place to put it. We don't currently have any good way for autovacuum to get at the information, but I suppose that an integrated autovacuum daemon could do so. Don't know why this must be an integrated autovacuum. Can't the info about the FSM usage be presented as system views? No doubt, but that's not free either --- it'd still need supporting code in the backend. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] View pg_stat_activity slow to get up to date
On Mon, 08 Nov 2004 12:56:57 -0500 Jan Wieck [EMAIL PROTECTED] wrote: Hi Jan. On 11/8/2004 12:03 PM, D'Arcy J.M. Cain wrote: I checked the FAQ and docs but haven't found anything definitive. This is my SQL test script: SELECT pg_backend_pid(); SELECT * FROM pg_stat_activity order by procpid; When I run psql reading that I find that my backend procpid is not in the list. I know that I can see it if I can introduce a little sleep (1 second) between the connection and the reading of pg_stat_activity. That is because the way your backend gets the information from the pgstat daemon process is by reading a file, which is at maximum written by that process every 500 msec. You will hardly ever see your own query. OK, I thought that using stats might not be the most reliable method but I couldn't find any other. Is there a reliable way to get that info? Note that I don't care what the backend is doing, just whether it is still running or not. All these statistics are unreliable by design, as explained numerous times on this list. In short, a backend should never be slowed down because the pgstat process can't swallow the stats fast enough. That is the reason for using UDP in the first place. So the kernel is allowed to drop stats packets, but not to block a backend. So it is worse than I thought. I thought that it was slow but it looks like it may never even make it. Probably OK for my purposes but it would be nice to have a reliable method. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] View pg_stat_activity slow to get up to date
D'Arcy J.M. Cain [EMAIL PROTECTED] writes: I checked the FAQ and docs but haven't found anything definitive. This is my SQL test script: SELECT pg_backend_pid(); SELECT * FROM pg_stat_activity order by procpid; When I run psql reading that I find that my backend procpid is not in the list. I know that I can see it if I can introduce a little sleep (1 second) between the connection and the reading of pg_stat_activity. ISTM that what you have here is a bad substitute for using user locks (see contrib/userlock/). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] How to create/initialize/access an execution plan
Hi all, I need a help and an advice on playing around execution plans. I need a direct access to a 'plan structure' and change some fields. Even one step more, I need to create an execution plan directly w/o issuing an SQL statement (i.e. skipping parser and optimizer phases) and initialize (fill in the fields) it. I searched a lot to find a suitable documentation and example set, but I couldn't find any. If you give some advices on how to create a plan node/structure (directly w/o an sql statement or query tree assuming that I have my own plan in my mind) and how to play around it (change fields), I would really appreciate it. Thanks in advance, PS: I tried to access fields of a plan returned by SPI_prepare(), but I couldn't manage it either. This mail sent through www.mywaterloo.ca ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html This mail sent through www.mywaterloo.ca ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] ExclusiveLock
Recent runs of DBT-2 show very occasional ExclusiveLock (s) being held by transactions, sometimes waiting to be granted. On Sat, Nov 06, 2004 at 11:40:49AM +, Simon Riggs wrote: The lockstats just show there's all those Exclusive Locks on order_line, right?: http://www.osdl.org/projects/dbt2dev/results/dev4-010/191/db/lockstats.out The output is... relname| pid | mode | granted ---+---+--+- new_order | 21735 | AccessShareLock | t new_order | 21735 | RowExclusiveLock | t orders| 21715 | AccessShareLock | t orders| 21715 | RowExclusiveLock | t pg_class | 23254 | AccessShareLock | t order_line| 21715 | AccessShareLock | t order_line| 21715 | RowExclusiveLock | t order_line| 21735 | ExclusiveLock| f new_order | 21715 | AccessShareLock | t ... which shows a non-granted lock, waiting for a Table-level ExclusiveLock on order_line. This is unexpected (by me, that is...) According to the manual, Exclusive Lock is not normally held by SQL statements. There are no LOCK TABLE statements in DBT-2. My digging reveals that ExclusiveLock is held on user relations by _bt_getbuf() - when we extend a btree relation by one page I also find ExclusiveLock is held by - LISTEN/NOTIFY - XactLockTableInsert()/XactLockTableDelete() but those don't look like they lock user relations LockAcquire() says its locks show in lock tables, so is index extension the source of the ExclusiveLocks shown in the lock output? Presumably they would be short duration, so you wouldn't see them unless you caught it at just the right momentunless we start to queue up on the leadingedge of the index. I expect index extension to be a source of contention anyway, but are we actually *seeing* it? Or is it another issue, and is this an 8.0 problem? -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] View pg_stat_activity slow to get up to date
On Mon, 08 Nov 2004 13:07:34 -0500 Tom Lane [EMAIL PROTECTED] wrote: D'Arcy J.M. Cain [EMAIL PROTECTED] writes: I checked the FAQ and docs but haven't found anything definitive. This is my SQL test script: SELECT pg_backend_pid(); SELECT * FROM pg_stat_activity order by procpid; When I run psql reading that I find that my backend procpid is not in the list. I know that I can see it if I can introduce a little sleep (1 second) between the connection and the reading of pg_stat_activity. ISTM that what you have here is a bad substitute for using user locks (see contrib/userlock/). Perhaps. I assume that the lock is automatically released when the holder closes its connection to the database, right? If so then that's what I need. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: What do you think is broken about fragmented UDP packets? Fragmentation happens at the IP protocol level, the kernel is responsible for reassembly. There's nothing for the application level to handle. And, by the same token, on platforms where it is broken there is nothing we can do about it. Ok having read the source I think I see why we're talking past each other. The pgstat messages are indeed fixed size. So bumping the size up to 8k would mean *every* udp packet would be bumped up to 8k. However there's no good reason for that to be the case. recv(2) always returns exactly one packet and tells you how large it was. And in fact the PgStat_MsgHdr even has a redundant message size field that could serve the same purpose. So we actually have all the machinery needed twice over to avoid the fixed size messages. In fact looking over the code I think it would be simple to change this. I think it would be reasonable to make just the PgStat_MsgActivity variable sized. I'll look at it a bit more, I think it's well worth the slight code complexity, especially if it were only done for the one message type. This has potential to reduce the average size of these messages quite a lot. Potentially reducing the data being pumped through udp and the pipe buffer (?!) quite a lot. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] View pg_stat_activity slow to get up to date
D'Arcy J.M. Cain [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: ISTM that what you have here is a bad substitute for using user locks (see contrib/userlock/). Perhaps. I assume that the lock is automatically released when the holder closes its connection to the database, right? If so then that's what I need. Right, user locks will be dropped automatically at backend exit (otherwise their management is entirely in your hands). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Greg Stark [EMAIL PROTECTED] writes: The pgstat messages are indeed fixed size. No, there's a fixed maximum size. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ExclusiveLock
Simon Riggs [EMAIL PROTECTED] writes: Recent runs of DBT-2 show very occasional ExclusiveLock (s) being held by transactions, sometimes waiting to be granted. I think you are right that these reflect heap or btree-index extension operations. Those do not actually take locks on the *table* however, but locks on a single page within it (which are completely orthogonal to table locks and don't conflict). The pg_locks output leaves something to be desired, because you can't tell the difference between table and page locks. It's odd that your example does not appear to show someone else holding a conflicting lock. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: The pgstat messages are indeed fixed size. No, there's a fixed maximum size. Hm. *rereads source* It's true, pgstat_report_activity only sends the actual size of the query, not the full payload size. The only problem I see in raising the size of PGSTAT_MSG_PAYLOAD is that it also governs the size of PGSTAT_NUM_TABPURGE and PGSTAT_NUM_TABENTRIES. There's no need to grow those arrays and risk losing them. But these message sizes could just be left based on the 1k value while boosting the maximum size of PGSTAT_ACTIVITY_SIZE. That would have no downside and only benefits. The worst case is that a machine that didn't handle UDP fragment reassembly would drop the packets that postgres is currently dropping preemptively. Shorter queries and other packets would be unaffected. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Greg Stark [EMAIL PROTECTED] writes: The only problem I see in raising the size of PGSTAT_MSG_PAYLOAD is that it also governs the size of PGSTAT_NUM_TABPURGE and PGSTAT_NUM_TABENTRIES. There's no need to grow those arrays and risk losing them. But these message sizes could just be left based on the 1k value while boosting the maximum size of PGSTAT_ACTIVITY_SIZE. Just to be clear, I'm talking about something as simple as this: (Haven't finished compiling it yet) --- pgstat.h.~1.26.~2004-08-29 00:13:03.0 -0400 +++ pgstat.h2004-11-08 17:17:17.0 -0500 @@ -57,12 +57,13 @@ } PgStat_MsgHdr; /* -- - * Space available in a message. This will keep the UDP packets below 1K, - * which should fit unfragmented into the MTU of the lo interface on most - * platforms. Does anybody care for platforms where it doesn't? + * Space used by a message ideally and maximum space used. We try to not to go + * over 1k unless necessary to avoid UDP packets that don't fit into the MTU + * of the loopback interface on very old systems and need to be fragmented. * -- */ -#define PGSTAT_MSG_PAYLOAD (1000 - sizeof(PgStat_MsgHdr)) +#define PGSTAT_MSG_PAYLOAD_IDEAL (1000 - sizeof(PgStat_MsgHdr)) +#define PGSTAT_MSG_PAYLOAD (32740 - sizeof(PgStat_MsgHdr)) /* -- * PgStat_TableEntry Per-table info in a MsgTabstat @@ -131,7 +132,7 @@ * and buffer access statistics. * -- */ -#define PGSTAT_NUM_TABENTRIES ((PGSTAT_MSG_PAYLOAD - 3 * sizeof(int)) \ +#define PGSTAT_NUM_TABENTRIES ((PGSTAT_MSG_PAYLOAD_IDEAL - 3 * sizeof(int)) \ / sizeof(PgStat_TableEntry)) typedef struct PgStat_MsgTabstat @@ -148,7 +149,7 @@ * about dead tables. * -- */ -#define PGSTAT_NUM_TABPURGE((PGSTAT_MSG_PAYLOAD - sizeof(int)) \ +#define PGSTAT_NUM_TABPURGE((PGSTAT_MSG_PAYLOAD_IDEAL - sizeof(int)) \ / sizeof(Oid)) typedef struct PgStat_MsgTabpurge -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Greg Stark [EMAIL PROTECTED] writes: That would have no downside and only benefits. The worst case is that a machine that didn't handle UDP fragment reassembly would drop the packets that postgres is currently dropping preemptively. Huh? We're not dropping the query *entirely*, which is what I would expect to happen if the kernel doesn't want to deal with UDP packet fragmentation. However, after rereading the RFCs I think this discussion may be based on false premises. In a network stack designed per the RFCs, both TCP and UDP use the same IP-level fragmentation logic, and so it's unlikely that there would be no fragmentation support at all. It's really a performance issue: do you want to pay the penalty associated with reassembling messages that exceed the loopback MTU, and do you want to risk the possibility that the kernel will drop stuff on the floor rather than fragment or reassemble it? Remember that UDP is non-guaranteed delivery, and the cases you are most interested in are likely to be exactly the same cases where the kernel is under stress and may decide to shed load that way. BTW, although the transmitted packets might not be fixed-size, the per-backend entries written to the stats file are. Cranking PGSTAT_ACTIVITY_SIZE up to the moon without loss of performance will take more than just changing one #define. Another relevant question is why you are expecting to get this information through pgstats and not by looking in the postmaster log. I don't know about you, but I don't have any tools that are designed to cope nicely with looking at tables that have columns that might be many K wide. Looking in the log seems a much nicer way of examining the full text of extremely long queries. So I think it's actually a good thing that pgstats truncates the queries at some reasonable width. 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: [HACKERS] [JDBC] 8.0.0beta4: copy and client_encoding
Well, lets ask -hackers... When COPYing data from a file, the file encoding is taken from the client_encoding parameter. The JDBC driver always uses UNICODE as the client_encoding and wants to prevent people from changing it by monitoring ParameterStatus messages and erroring out if it's changed. This presents a problem when you want to COPY to or from a file with a different encoding. It seems reasonable to add an ENCODING specification to the COPY command instead of relying on the somewhat unrelated client_encoding setting. Oliver Jowett also noted that copying from a file with LATIN1 data into a table whose name contained UNICODE characters could not be done. Does this seem like a reasonable thing to do? Kris Jurka On Mon, 8 Nov 2004, Barry Lind wrote: I am assuming this will get addressed in the backend in 8.1 and that would be the upgrade path. (I agree if there isn't agreement on the server side that this is appropriate for the server, then this wouldn't be the correct parameter). --Barry ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Increasing the length of pg_stat_activity.current_query...
Tom Lane wrote: It's really a performance issue: do you want to pay the penalty associated with reassembling messages that exceed the loopback MTU [...] BTW, the loopback MTU here is quite large: [EMAIL PROTECTED]:~$ /sbin/ifconfig lo | grep MTU UP LOOPBACK RUNNING MTU:16436 Metric:1 [EMAIL PROTECTED]:~$ uname -a Linux flood 2.6.8.1-flood #1 Wed Sep 29 21:58:09 NZST 2004 i686 GNU/Linux so at least on Linux 2.6 it seems like the risk of fragmentation is minimal. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] unnest
On Nov 5, 2004, at 7:09 AM, John Hansen wrote:
Attached, array - rows iterator.
select * from unnest(array[1,2,3,4,5]);
This is really handy! But there is a problem...
The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return anyitem. If
anyone have a better way, please let me know.
Why do you need the switch statement at all? array-elements is already
an array of Datums. Won't simply returning
array-elements[array-i]
work?
The problem is:
test=# select * from unnest('{1,2,3,4,5}'::int8[]);
unnest
--
25314880
25314888
25314896
25314904
25314912
(5 rows)
Whereas simply returning the current Datum in array-elements returns
the correct result:
if (array-i array-num_elements)
SRF_RETURN_NEXT(funcctx,array-elements[array-i++]);
else
SRF_RETURN_DONE(funcctx);
test=# select * from unnest('{1,2,3,4,5}'::int8[]);
unnest
1
2
3
4
5
(5 rows)
Also works for the few other datatypes I checked.
Am I missing something obvious?
eric
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] unnest
On Fri, 5 Nov 2004, John Hansen wrote: Attached, array - rows iterator. select * from unnest(array[1,2,3,4,5]); Unnest --- 1 2 3 4 5 5 rows This mechanism is actually designed for the multiset data type in SQL. AFAICT, our elementary one dimensional array handling mimics SQL multisets. Is there any intention to bring this into line with the spec or would that be mere pedantism? Thanks, Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] unnest
The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return anyitem. If
anyone have a better way, please let me know.
Why do you need the switch statement at all? array-elements is already
an array of Datums. Won't simply returning
array-elements[array-i]
work?
yea,. sorry,. worked it out shortly after posting this, but forgot to
repost so here it is... attached.
The problem is:
test=# select * from unnest('{1,2,3,4,5}'::int8[]);
unnest
--
25314880
25314888
25314896
25314904
25314912
(5 rows)
# -
unnest :
gcc -I /usr/include/postgresql/server/ -I /usr/include/postgresql/ -shared -o unnest.so unnest.c
install :
install -s -m 755 unnest.so $(DESTDIR)/usr/lib/postgresql/lib/;
clean :
rm -f *.o *~ core *.so;
#include postgres.h
#include fmgr.h
#include funcapi.h
#include catalog/pg_type.h
#include utils/acl.h
#include utils/array.h
#include utils/date.h
#include utils/geo_decls.h
#include utils/inet.h
#include utils/nabstime.h
#include utils/numeric.h
#include utils/timestamp.h
#include utils/varbit.h
#include utils/lsyscache.h
typedef struct {
Datum *elements;
int num_elements;
Oid typelem;
bool typbyval;
int i;
} UNNEST;
PG_FUNCTION_INFO_V1(unnest);
Datum unnest(PG_FUNCTION_ARGS) {
FuncCallContext *funcctx;
UNNEST *array;
if(PG_ARGISNULL(0))
ereport(ERROR,(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),errmsg(null array elements not supported)));
if (SRF_IS_FIRSTCALL()) {
MemoryContext oldcontext;
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
array = (UNNEST *)palloc(sizeof(UNNEST));
ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
int nitems = ArrayGetNItems(ARR_NDIM(v), ARR_DIMS(v));
ArrayMetaState *my_extra;
my_extra = (ArrayMetaState *) fcinfo-flinfo-fn_extra;
if (my_extra == NULL)
{
fcinfo-flinfo-fn_extra = palloc(sizeof(ArrayMetaState));
my_extra = (ArrayMetaState *) fcinfo-flinfo-fn_extra;
my_extra-element_type = InvalidOid;
}
if (my_extra-element_type != ARR_ELEMTYPE(v))
{
get_typlenbyvalalign(ARR_ELEMTYPE(v), my_extra-typlen, my_extra-typbyval,my_extra-typalign);
my_extra-element_type = ARR_ELEMTYPE(v);
}
array-typelem = my_extra-element_type;
array-typbyval = my_extra-typbyval;
array-i = 0;
deconstruct_array(v,my_extra-element_type,my_extra-typlen,my_extra-typbyval,my_extra-typalign,array-elements,array-num_elements);
if(array-num_elements != nitems)
elog(WARNING,array unnests to %d elements but consists of %d items,array-num_elements,nitems);
funcctx-user_fctx = (void *)array;
MemoryContextSwitchTo(oldcontext);
}
funcctx = SRF_PERCALL_SETUP();
array = (UNNEST *)funcctx-user_fctx;
if (array-i array-num_elements) {
Datum retval = (Datum)array-elements[array-i];
array-i++;
SRF_RETURN_NEXT(funcctx,retval);
} else {
SRF_RETURN_DONE(funcctx);
}
}
CREATE FUNCTION unnest(anyarray)
RETURNS setof anyelement
AS 'unnest.so','unnest'
LANGUAGE 'C' IMMUTABLE;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers] Increasing the length of pg_stat_activity.current_query...
Tom, Another relevant question is why you are expecting to get this information through pgstats and not by looking in the postmaster log. I don't know about you, but I don't have any tools that are designed to cope nicely with looking at tables that have columns that might be many K wide. Looking in the log seems a much nicer way of examining the full text of extremely long queries. So I think it's actually a good thing that pgstats truncates the queries at some reasonable width. Because pg_stat_activity can be queried dynamically, and the log can't. I'm currently dealing with this at a clients site who is having elusive bad queries hammer the CPU. In order to find a bad query by PID, I have to: 1) turn on log_statement, log_timestamp and log_pid; 2) HUP the postmaster; 3) watch top and record the time and pid of the bad query; 4) cp the log off to a file; 5) turn back off log_statement and log_pid; 6) grep the log for the time/pid, using a regexp to deal with minor variations in timestamp. It's a big PITA to retrieve the text of one bad query. And that's assuming that the bad query re-occurs within a reasonable window of time from when I spotted it so that I don't end up watching top for the rest of the afternoon. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
