Re: [HACKERS] Casting to money
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 09 October 2006 04:15 To: Dave Page Cc: PostgreSQL Hackers Subject: Re: [HACKERS] Casting to money Dave Page dpage@vale-housing.co.uk writes: select '$123.45'::money ERROR: invalid input syntax for type money: $123.45 select '£123.00'::money ERROR: invalid input syntax for type money: £123.00 So ... what locale are you trying this in? Oh, sorry - English_United Kingdom.28591, on 8.1.4/win32. Now I'm back at work I do see that select '$123.45'::money Works OK on 8.0.3/Slackware Linux in en_US. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Casting to money
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Page Sent: 09 October 2006 08:42 To: Tom Lane Cc: PostgreSQL Hackers Subject: Re: [HACKERS] Casting to money -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 09 October 2006 04:15 To: Dave Page Cc: PostgreSQL Hackers Subject: Re: [HACKERS] Casting to money Dave Page dpage@vale-housing.co.uk writes: select '$123.45'::money ERROR: invalid input syntax for type money: $123.45 select '£123.00'::money ERROR: invalid input syntax for type money: £123.00 So ... what locale are you trying this in? Oh, sorry - English_United Kingdom.28591, on 8.1.4/win32. Now I'm back at work I do see that select '$123.45'::money Works OK on 8.0.3/Slackware Linux in en_US. As does select '£123.44'::money In en_GB on the same box, so I guess this is a windows issue. /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Casting to money
Tom Lane wrote: Dave Page dpage@vale-housing.co.uk writes: select '$123.45'::money ERROR: invalid input syntax for type money: $123.45 select '£123.00'::money ERROR: invalid input syntax for type money: £123.00 So ... what locale are you trying this in? I get the following from 8.2beta1 - looks like it doesn't like the double quotes. postgres=# select '123.45'::money; money - $123.45 (1 row) postgres=# select '$123.45'::money; money - $123.45 (1 row) postgres=# select 123.45::money; ERROR: column 123.45 does not exist LINE 1: select 123.45::money; ^ postgres=# select $123.45::money; ERROR: column $123.45 does not exist LINE 1: select $123.45::money; ^ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Casting to money
postgres=# select 123.45::money; ERROR: column 123.45 does not exist LINE 1: select 123.45::money; ^ postgres=# select $123.45::money; ERROR: column $123.45 does not exist LINE 1: select $123.45::money; ^ You are on the wrong mailing list :^) Try pgsql-general. http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDE NTIFIERS Anything between double quotes is an identifier. Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump exclusion switches and functions/types
We are two months past feature freeze ... adding entirely new features to pg_dump is *not* on the table for 8.2. Ok, clear. The scenario I most care about is to be able to make a complete data base dump (including non-schema objects) while excluding only a few tables. Isn't this the same as Kris' complaint? Why do you need additional dependency analysis to do the above? Well, I obviously didn't understand well the complete feature as it is implemented. Now, is what I want (see above) possible with the new feature, or if I exclude some tables I implicitly loose some other things too from the dump which normally would be there ? This is my only concern... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Casting to money
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shane Ambler Sent: 09 October 2006 09:06 To: PostgreSQL Hackers Subject: Re: [HACKERS] Casting to money Tom Lane wrote: Dave Page dpage@vale-housing.co.uk writes: select '$123.45'::money ERROR: invalid input syntax for type money: $123.45 select '£123.00'::money ERROR: invalid input syntax for type money: £123.00 So ... what locale are you trying this in? I get the following from 8.2beta1 - looks like it doesn't like the double quotes. Double quotes are used for object names - MySchema.MyTable for example. I didn't even bother testing them. postgres=# select '123.45'::money; money - $123.45 (1 row) Now that's interesting - on the Windows server, pgAdmin gets a blank string back. On further investigation, this is looking like an encoding issue in which pgAdmin (well, wxWidgets) isn't converting the £ to utf-8 and back correctly. It does appear to be working correctly in psql. Sorry for the noise. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Fwd: pg_dump VS alter database ... set search_path ...
What is the reason to not include database settings (like search_path) to database dump created with pg_dump -C? For me, I've created tmp patch for pg_dump to make my system work (patch for CVS version is included). -- Forwarded message -- From: Nikolay Samokhvalov [EMAIL PROTECTED] Date: Oct 9, 2006 12:45 PM Subject: pg_dump VS alter database ... set search_path ... To: PostgreSQL-general pgsql-general@postgresql.org Hi, What is the best practice for following case: In my database I have a set (~10) of schemas; my database periodically is being backed up and restored at another machine. I have set up search_path via ALTER DATABASE ... SET search_path TO ... to make all needed schemas visible to any user who has appropriate rights. The problem is that I cannot use pg_dumpall and pg_dump DOES NOT dump this ALTER command, even being executed with -C option. Using additional restoration script with list of schemas seems not the best solution, because a set of schemas can be changed and I have not only one database. Search in mail archives gives me understanding that this issue is among not resolved ones (there are pros and cons for including such ALTER in pg_dump-ing). Is there any common practice for this [probably very frequent] issue? -- Best regards, Nikolay -- Best regards, Nikolay pg_dump_settings82b1.patch Description: Binary data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] OT: Is there a LinkedIn group for Postgresql?
Hi, Is there a LinkedIn group for Postgresql/Hackers list. If there is, how can i join? Thank you. -- Regards, tzahi. Itzhak Fadida M.Sc - Technion, Information Systems, IE Faculty Home Page: Http://tzahi.webhop.info BLOG: Http://tzahi.blogsite.org LinkedIn: http://www.linkedin.com/in/tzahi WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Backbranch releases and Win32 locking
Analyzing locking state, lock occurs when backend wants to send data to stat
collector. So state is:
backend waits FD_WRITE event, stat collector waits FD_READ.
I suspect follow sequence of events in backend:
0 Let us work only with one socket, and socket associated with statically
defined event object in pgwin32_waitforsinglesocket.
1. pgwin32_send:WSASend fails with WSAEWOULDBLOCK ( or its equivalent )
2. socket s becomes writable and Windows signals event defined statically
in pgwin32_waitforsinglesocket.
3. pgwin32_waitforsinglesocket(): ResetEvent resets event
4. pgwin32_waitforsinglesocket(): WaitForMultipleObjectsEx waits indefinitely...
If I'm right, it's needed to move ResetEvent after WaitForMultipleObjectsEx. But
comment in pgwin32_select() says that we should send something before test
socket for FD_WRITE. pgwin32_send calls WSASend before
pgwin32_waitforsinglesocket(), but there is a call of
pgwin32_waitforsinglesocket in libpq/be-secure.c. So, attached patch adds call
of WSASend with void buffer.
It's a pity, but locking problem occurs only on SMP box and requires several
hours to reproduce. So we are in testing now.
What are opinions?
PS Backtraces
backend:
ntdll.dll!KiFastSystemCallRet
postgres.exe!pgwin32_waitforsinglesocket+0x197
postgres.exe!pgwin32_send+0xaf
postgres.exe!pgstat_report_waiting+0x1bd
postgres.exe!pgstat_report_tabstat+0xda
postgres.exe!PostgresMain+0x1040
postgres.exe!ClosePostmasterPorts+0x1bce
postgres.exe!SubPostmasterMain+0x1be
postgres.exe!main+0x22b
postgres.exe+0x1237
postgres.exe+0x1288
kernel32.dll!RegisterWaitForInputIdle+0x49
logger:
ntdll.dll!KiFastSystemCallRet
kernel32.dll!WaitForSingleObject+0x12
postgres.exe!pg_usleep+0x54
postgres.exe!SysLoggerMain+0x422
postgres.exe!SubPostmasterMain+0x370
postgres.exe!main+0x22b
postgres.exe+0x1237
postgres.exe+0x1288
kernel32.dll!RegisterWaitForInputIdle+0x49
bgwriter:
ntdll.dll!KiFastSystemCallRet
kernel32.dll!WaitForSingleObject+0x12
postgres.exe!pg_usleep+0x54
postgres.exe!BackgroundWriterMain+0x63a
postgres.exe!BootstrapMain+0x61f
postgres.exe!SubPostmasterMain+0x22c
postgres.exe!main+0x22b
postgres.exe+0x1237
postgres.exe+0x1288
kernel32.dll!RegisterWaitForInputIdle+0x49
stat collector:
ntdll.dll!KiFastSystemCallRet
postgres.exe!pgwin32_select+0x4f3
postgres.exe!PgstatCollectorMain+0x32f
postgres.exe!SubPostmasterMain+0x32a
postgres.exe!main+0x22b
postgres.exe+0x1237
postgres.exe+0x1288
kernel32.dll!RegisterWaitForInputIdle+0x49
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
*** ./src/backend/port/win32/socket.c.orig Mon Oct 9 10:39:53 2006
--- ./src/backend/port/win32/socket.c Mon Oct 9 15:44:24 2006
***
*** 132,137
--- 132,159
current_socket = s;
+ /*
+* See comments about FD_WRITE and WSASelectEvent
+* in pgwin32_select()
+*/
+ if ( (what FD_WRITE) != 0 ) {
+ charc;
+ WSABUF buf;
+ DWORD sent;
+
+ buf.buf = c;
+ buf.len = 0;
+ r = WSASend(s, buf, 1, sent, 0, NULL, NULL);
+
+ if (r == 0) /* Completed - means things are fine! */
+ return 1;
+ else if ( WSAGetLastError() != WSAEWOULDBLOCK )
+ {
+ TranslateSocketError();
+ return 0;
+ }
+ }
+
if (WSAEventSelect(s, waitevent, what) == SOCKET_ERROR)
{
TranslateSocketError();
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2beta1 crash possibly in libpq
On Sun, 2006-10-08 at 17:53 +0200, Magnus Hagander wrote: AFAICT the backtrace and server log is indicating that the crash is happening somewhere in libpq. If someone can help me figure out how to load the libpq symbols into MingW's gdb then I can get a better backtrace if required as I can reproduce this 100% of the time. For reference, the source for the application in question can be found at http://svn.refractions.net/postgis/tags/1.1.4/loader/pgsql2shp.c. If you figure out how to make gdb actually work on mingw, let us know - not many has ever managed to get it wokring, and I don't know of anybody who can make it work repeatedly. That said, libpq builds with Visual C++. Could you try building your pgsql2shp with Visual C++ as well, and then use the Visual C++ debugger (or windbg, really). They should give working backtraces. //Magnus Hi Magnus, Getting closer I think. I managed to compile a MSVC libpq but it agreed with the MingW backtrace in that it was jumping into the middle of nowhere :( I think I may be getting closer though: I've just done a comparison build with PostgreSQL 8.1 and noticed that there is an error message is being emitted regarding PGntuples (which is where the crash is occuring): PG 8.1: [EMAIL PROTECTED] ~/postgis/pg81/postgis-1.1.4/loader $ make gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -c -o shpopen.o shpopen.c shpopen.c:176: warning: 'rcsid' defined but not used gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -c -o dbfopen.o dbfopen.c dbfopen.c:206: warning: 'rcsid' defined but not used gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -c -o getopt.o getopt.c gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -c -o shp2pgsql.o shp2pgsql.c shp2pgsql.c: In function `utf8': shp2pgsql.c:1686: warning: passing arg 2 of `libiconv' from incompatible pointer type gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 shpopen.o dbfopen.o getopt.o shp2pgsql.o -liconv -o shp2pgsql.exe gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -IC:/msys/1.0/home/mca/pg81/REL-81~1.4/include -c pgsql2shp.c gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -c -o PQunescapeBytea.o PQunescapeBytea.c gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 shpopen.o dbfopen.o getopt.o PQunescapeBytea.o pgsql2shp.o -liconv C:/msys/1.0/home/mca/pg81/REL-81~1.4/lib/libpq.dll -o pgsql2shp.exe PG 8.2: [EMAIL PROTECTED] ~/postgis/pg82/postgis-1.1.4/loader $ make gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 -c -o shpopen.o shpopen.c shpopen.c:176: warning: 'rcsid' defined but not used gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 -c -o dbfopen.o dbfopen.c dbfopen.c:206: warning: 'rcsid' defined but not used gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 -c -o getopt.o getopt.c gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 -c -o shp2pgsql.o shp2pgsql.c shp2pgsql.c: In function `utf8': shp2pgsql.c:1686: warning: passing arg 2 of `libiconv' from incompatible pointer type gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 shpopen.o dbfopen.o getopt.o shp2pgsql.o -liconv -o shp2pgsql.exe gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 -IC:/msys/1.0/home/mca/pg82/REL-8~1.2BE/include -c pgsql2shp.c gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 -c -o PQunescapeBytea.o PQunescapeBytea.c gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 shpopen.o dbfopen.o getopt.o PQunescapeBytea.o pgsql2shp.o -liconv C:/msys/1.0/home/mca/pg82/REL-8~1.2BE/lib/libpq.dll -o pgsql2shp.exe Info: resolving _PQntuples by linking to __imp__PQntuples (auto-import) I think the key part is this line: Info: resolving _PQntuples by linking to __imp__PQntuples (auto-import). Could it be that the linker cannot find a reference to PQntuples and hence is jumping into random code? I have verified that PQntuples does exist within libpq.dll using the Microsoft Dependency Walker though. Kind regards, Mark. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2beta1 crash possibly in libpq
AFAICT the backtrace and server log is indicating that the crash is happening somewhere in libpq. If someone can help me figure out how to load the libpq symbols into MingW's gdb then I can get a better backtrace if required as I can reproduce this 100% of the time. For reference, the source for the application in question can be found at http://svn.refractions.net/postgis/tags/1.1.4/loader/pgsql2shp.c. If you figure out how to make gdb actually work on mingw, let us know - not many has ever managed to get it wokring, and I don't know of anybody who can make it work repeatedly. That said, libpq builds with Visual C++. Could you try building your pgsql2shp with Visual C++ as well, and then use the Visual C++ debugger (or windbg, really). They should give working backtraces. //Magnus Hi Magnus, Getting closer I think. I managed to compile a MSVC libpq but it agreed with the MingW backtrace in that it was jumping into the middle of nowhere :( Oops. Sounds like a generic memory corruption then, overwriting the return stack so the backtrace doesn't work. I think I may be getting closer though: I've just done a comparison build with PostgreSQL 8.1 and noticed that there is an error message is being emitted regarding PGntuples (which is where the crash is occuring): [EMAIL PROTECTED] ~/postgis/pg81/postgis-1.1.4/loader $ make gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=81 -c -o shpopen.o shpopen.c A question based on that - are you using gettext? I know gettext, and possibly iconv, breaks if gettext is compiled with one version of VC++ and the program using it a different version. If you are building with it, try to disable it and see if that's where the problem is from. snip C:/msys/1.0/home/mca/pg82/REL-8~1.2BE/lib/libpq.dll -o pgsql2shp.exe Info: resolving _PQntuples by linking to __imp__PQntuples (auto- import) I think the key part is this line: Info: resolving _PQntuples by linking to __imp__PQntuples (auto-import). Could it be that the linker cannot find a reference to PQntuples and hence is jumping into random code? I have verified that PQntuples does exist within libpq.dll using the Microsoft Dependency Walker though. This is fairly normal, and it's just info - not even a warning. If it couldn't find the refenrence, you'd get one of those could not find entrypoint in DLL errorboxes when you tried to start the program. It absolutely will not just pick a random memory and jump to. You could possibly do that yourself if you were loading the DLL manually, but since you're not doing that... //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] The improvement for psql of 8.2beta1 not
On 10/8/06, Magnus Hagander [EMAIL PROTECTED] wrote: The test shows that it's OK under Linux (Slackware), but malfunctioned on Windows XP. Good point. We don't use readline on Win32, but rather the native command-line control, over which we have little control. Does libedit compile under mingw? No. At least, it didn't the last couple of times I've tried it. I compile psql with cygwin to get the readline version. Also the win32 version defatults to pagination with 'more' instead of 'less'. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Interface of the R-tree in order to work with postgresql
Hi everybody!I'm Jorge from Peru South America, and this is my first postI want to know how can I add a new spatial access method into the postgresql (I'm doing research on spatial access methods( reading a lot of papers and programming a lot too ) but also I want to know how can I add my new data structure( if someday i get it, of course =) ) in the postgresql, I mean where can i find the .h that describes the interface that a spatial access method, like the R-tree, must have in order to work with postgresql.I think that to understand how postgresql works with spatial access methods, like the R-tree, I need to find a .h or .c to begin reading the code, so can you tell me where to start?I hope you can help meregards Jorge
Re: [HACKERS] Interface of the R-tree in order to work with postgresql
On Mon, Oct 09, 2006 at 09:15:58AM -0500, jorge alberto wrote: Hi everybody! I'm Jorge from Peru South America, and this is my first post I want to know how can I add a new spatial access method into the postgresql (I'm doing research on spatial access methods( reading a lot of papers and programming a lot too ) but also I want to know how can I add my new data structure( if someday i get it, of course =) ) in the postgresql, I mean where can i find the .h that describes the interface that a spatial access method, like the R-tree, must have in order to work with postgresql. I would start by reading the documentation, specifically the part about GiST. Once you have understood the concept and code there, go to reading the rtree code in the backend. That should answer almost all your questions. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] pg_dump exclusion switches and functions/types
Csaba Nagy [EMAIL PROTECTED] writes: Isn't this the same as Kris' complaint? Why do you need additional dependency analysis to do the above? Well, I obviously didn't understand well the complete feature as it is implemented. Now, is what I want (see above) possible with the new feature, or if I exclude some tables I implicitly loose some other things too from the dump which normally would be there ? This is my only concern... I think we've agreed that if you use some exclusion switches, but not any inclusion switches, then only the specific objects matching your switches are excluded. CVS HEAD gets this wrong, but I'm going to work on it today. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump exclusion switches and functions/types
On Mon, 2006-10-09 at 16:24, Tom Lane wrote: I think we've agreed that if you use some exclusion switches, but not any inclusion switches, then only the specific objects matching your switches are excluded. CVS HEAD gets this wrong, but I'm going to work on it today. Cool, that makes it cover my use case and some more. Thanks, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Fwd: pg_dump VS alter database ... set search_path ...
Nikolay Samokhvalov [EMAIL PROTECTED] writes: What is the reason to not include database settings (like search_path) to database dump created with pg_dump -C? Duplication of code and functionality with pg_dumpall. I'd want to see some thought about how to resolve that, not just a quick copy-some-code- from-pg_dumpall-into-pg_dump. You also need to explain why this issue should be treated differently from users and groups ... a dump won't restore correctly without that supporting context either. I have no objection to rethinking the division of labor between the two programs, but let's end up with something that's cleaner not uglier. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Backbranch releases and Win32 locking
Analyzing locking state, lock occurs when backend wants to send data to stat collector. So state is: backend waits FD_WRITE event, stat collector waits FD_READ. I suspect follow sequence of events in backend: 0 Let us work only with one socket, and socket associated with statically defined event object in pgwin32_waitforsinglesocket. 1. pgwin32_send:WSASend fails with WSAEWOULDBLOCK ( or its equivalent ) 2. socket s becomes writable and Windows signals event defined statically in pgwin32_waitforsinglesocket. 3. pgwin32_waitforsinglesocket(): ResetEvent resets event 4. pgwin32_waitforsinglesocket(): WaitForMultipleObjectsEx waits indefinitely... If I'm right, it's needed to move ResetEvent after WaitForMultipleObjectsEx. But comment in pgwin32_select() says that we should send something before test socket for FD_WRITE. pgwin32_send calls WSASend before pgwin32_waitforsinglesocket(), but there is a call of pgwin32_waitforsinglesocket in libpq/be- secure.c. So, attached patch adds call of WSASend with void buffer. Hmm. Not entirely sure. These are all in the SSL codepath. Are you using SSL on the machine? Does the problem go away if you don't? (I was thinking SSL always attempts to write data first, but then fails, at which point this code is fine. You only need to attempt a send at it if you didn't try that before) The normal way is that pgwin32_waitforsinglesocket is called from pgwin32_send(), which will always have made the attempt to send data first. It's a pity, but locking problem occurs only on SMP box and requires several hours to reproduce. So we are in testing now. Yikes, that's definitely not nice :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interface of the R-tree in order to work with postgresql
jorge alberto [EMAIL PROTECTED] writes: where can i find the .h that describes the interface that a spatial access method, like the R-tree, must have in order to work with postgresql. There is no single .h file that will tell you everything you need to know. I'd suggest starting here: http://developer.postgresql.org/pgdocs/postgres/internals.html with particular attention to chapter 48 http://developer.postgresql.org/pgdocs/postgres/indexam.html After that the indexam README files might be educational: src/backend/access/nbtree/README src/backend/access/gin/README src/backend/access/gist/README src/backend/access/hash/README and then start looking at .h files. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Backbranch releases and Win32 locking
Hmm. Not entirely sure. These are all in the SSL codepath. Are you using SSL on the machine? Does the problem go away if you don't? (I was No, we don;t use SSL. The normal way is that pgwin32_waitforsinglesocket is called from pgwin32_send(), which will always have made the attempt to send data first. My doubt is: can ResetEvent resets signaled state of associated event object? Look, in any case pgwin32_waitforsinglesocket() resets event before WSAEventSelect(). pgwin32_send() calls WSASend and if it fails, call pgwin32_waitforsinglesocket(). It's a pity, but locking problem occurs only on SMP box and requires several hours to reproduce. So we are in testing now. Yikes, that's definitely not nice :-) //Magnus -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2beta1 crash possibly in libpq
Magnus Hagander [EMAIL PROTECTED] writes: C:/msys/1.0/home/mca/pg82/REL-8~1.2BE/lib/libpq.dll -o pgsql2shp.exe Info: resolving _PQntuples by linking to __imp__PQntuples (auto-import) This is fairly normal, and it's just info - not even a warning. It seems pretty odd that it would only be whinging about PQntuples and not any of the other libpq entry points, though. I think Mark should try to figure out why that is. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Prepared Statement Question
We have a question regarding prepared statements. The following code is located
in src/backend/tcop/postgres.c:
/* Get the parameter format codes */
numPFormats = pq_getmsgint(input_message, 2);
if (numPFormats 0)
{
int i;
pformats = (int16 *) palloc(numPFormats * sizeof(int16));
for (i = 0; i numPFormats; i++)
pformats[i] = pq_getmsgint(input_message, 2);
}
There is similar code for Parameter Lists (ParamListInfo) and Result Format
Codes (rformats). Unless we're missing something, a prepared statement would
probably never change once prepared.
Would there be any issue or benefit moving the allocation of these buffers to
the PreparedStatement structure so they stay with the prepared statement
throughout its life? There is probably the question of named versus unnamed
prepared statements, but is there anything else that we might be missing?
Along these lines, would it also be possible to keep an Executor State and
Expression Context with the statement and just reset key parts of them, rather
than rebuilding them from scratch each time a prepared statement is executed?
Thanks
David
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2beta1 crash possibly in libpq
Hi Magnus, I finally got to the bottom of this - it seems that the flags being passed to MingW's linker were incorrect, but instead of erroring out it decided to create a corrupt executable. Here is the command line that was being used to link the pgsql2shp.exe executable, along with the associated auto-import warning: gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 shpopen.o dbfopen.o getopt.o PQunescapeBytea.o pgsql2shp.o -liconv C:/msys/1.0/home/mca/pg82/REL-8~1.2BE/lib/libpq.dll -o pgsql2shp.exe Info: resolving _PQntuples by linking to __imp__PQntuples (auto-import) Note that libpq.dll is referenced directly with -l which I believe should be an invalid syntax. This produces a corrupt executable that crashes whenever PQntuples is accessed. On the other hand, a correct executable can be realised by linking like this: gcc -g -Wall -I.. -DUSE_ICONV -DUSE_VERSION=82 shpopen.o dbfopen.o getopt.o PQunescapeBytea.o pgsql2shp.o -liconv -LC:/msys/1.0/home/mca/pg82/REL-8~1.2BE/lib -lpq -o pgsql2shp.exe Note there is no auto-import warning, and the use of -L and -l is how I would expect. In actual fact, the incorrect link line was being produced by an error in the configure.in script, so this won't be a scenario that most people will experience. The executables linked using the second method now work properly without crashing during regression. The big mystery is that the command line used to link the executables has been like that for several versions now, and I have absolutely no idea why it only triggered this failure when being linked against 8.2beta1 when it works perfectly on 8.1 and 8.0, and also why only PQntuples was affected. Kind regards, Mark. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upgrading a database dump/restore
Mark Woodward [EMAIL PROTECTED] writes: Whenever someone actually writes a pg_upgrade, we'll institute a policy to restrict changes it can't handle. IMHO, *before* any such tool *can* be written, a set of rules must be enacted regulating catalog changes. That one is easy: there are no rules. We already know how to deal with catalog restructurings --- you do the equivalent of a pg_dump -s and reload. Any proposed pg_upgrade that can't cope with this will be rejected out of hand, because that technology was already proven five years ago. The issues that are actually interesting have to do with the contents of user tables and indexes, not catalogs. It is becomming virtually impossible to recreate databases. Data storage sizes are increasing faster than the transimssion speeds of the media on which they are stored or the systems by which they are connected. The world is looking at a terabyte as merely a very large database these days. tens of terabytes are not far from being common. Dumping out a database is bad enough, but that's only the data, and that can takes (mostly) only hours. Recreating a large database with complex indexes can take days or hours for the data, hours per index, it adds up. No one could expect that this could happen by 8.2, or the release after that, but as a direction for the project, the directors of the PostgreSQL project must realize that the dump/restore is becomming like the old locking vacuum problem. It is a *serious* issue for PostgreSQL adoption and arguably a real design flaw. If the barrier to upgrade it too high, people will not upgrade. If people do not upgrade, then older versions will have to be supported longer or users will have to be abandoned. If users are abandoned and there are critical bugs in previous versions of PostgreSQL, then user who eventually have to migrate their data, they will probably not use PostgreSQL in an attempt to avoid repeating this situation. While the economics of open source/ free software are different, there is still a penalty for losing customers, and word of mouth is a dangerous thing. Once or twice in the customers product usage history can you expect to get away with this sort of inconvenience, but if every new major version requres a HUGE process, then the TCO of PostgreSQL gets very high indeed. If it is a data format issue, maybe there should be a forum for a next gen version of the current data layout that is extensible without restructuring. This is not something that a couple people can go off and do and submit a patch, it is something that has to be supported and promoted from the core team, otherwise it won't happen. We all know that. The question is whether or not you all think it is worth doing. I've done consulting work for some very large companies that everyone has heard of. These sorts of things matter. ---(end of broadcast)--- TIP 1: 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] width_bucket function for timestamps
Sinte we already have width_bucket, I'd argue this should go in core. If
someone's feeling adventurous, there should probably be a double
precision version as well. Hrm... and maybe text...
Doesn't the backend already have something like this for calculating
histograms?
On Sun, Oct 08, 2006 at 10:30:47PM -0700, Jeremy Drake wrote:
I just came across this code I wrote about a year ago which implements a
function equivilant to width_bucket for timestamps.
I wrote this when I was trying to plot some data over time, and I had more
points than I needed. This function allowed me to create a pre-determined
number of bins to average the data inside of so that I could get a sane
number of points. Part of the problem was that there were so many data
points, that a sql implementation of the function (or plpgsql, I forget,
it was a year ago) was painfully slow. This C function provided much
better performance than any other means at my disposal.
I wanted to share this code since it may be useful for someone else, but I
don't know exactly what to do with it. So I am putting it out there, and
asking what the proper home for such a function might be. I believe it
would be generally useful for people, but it is so small that it hardly
seems like a reasonable pgFoundry project. Maybe there is a home for such
a thing in the core distribution in a future release?
The code can be found at
http://www.jdrake.com/postgresql/bintimestamp.tar.gz for a buildable PGXS
module, or I attached just the C code. There is no documentation, the
parameters work the same as the width_bucket function. The code is not
necessarily the most readable in the world, I was trying to get as much
speed out of it as possible, since I was calling it over a million times
as a group by value.
Thanks for any pointers...
--
Fortune's Office Door Sign of the Week:
Incorrigible punster -- Do not incorrige.
/*
* file:$RCSfile: bintimestamp.c,v $ $Revision: 1.1 $
* module: timestamp
* authors: jeremyd
* last mod:$Author: jeremyd $ at $Date: 2005/10/28 20:26:38 $
*
* created: Fri Oct 28 13:26:38 PDT 2005
*
*/
#include string.h
#include math.h
#include postgres.h
#include fmgr.h
#include libpq/pqformat.h
#include utils/builtins.h
#include funcapi.h
#include utils/timestamp.h
#ifndef JROUND
# define JROUND(x) (x)
#endif
Datum timestamp_get_bin_size(PG_FUNCTION_ARGS);
Datum timestamp_bin(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(timestamp_get_bin_size);
Datum
timestamp_get_bin_size(PG_FUNCTION_ARGS)
{
Timestamp start = PG_GETARG_TIMESTAMP(0);
Timestamp stop = PG_GETARG_TIMESTAMP(1);
int32 nbuckets = PG_GETARG_INT32(2);
Interval * retval = (Interval *)palloc (sizeof(Interval));
if (!retval)
{
ereport(ERROR, (errcode(ERRCODE_OUT_OF_MEMORY),
errmsg(insufficient memory for Interval allocation)));
PG_RETURN_NULL();
}
memset (retval, 0, sizeof(Interval));
retval-time = JROUND ((stop - start) / nbuckets);
PG_RETURN_INTERVAL_P(retval);
}
PG_FUNCTION_INFO_V1(timestamp_bin);
Datum
timestamp_bin(PG_FUNCTION_ARGS)
{
/*Timestamp op = PG_GETARG_TIMESTAMP(0);*/
Timestamp start = PG_GETARG_TIMESTAMP(1);
/*Timestamp stop = PG_GETARG_TIMESTAMP(2);*/
Timestamp binsz;
/*int32 nbuckets = PG_GETARG_INT32(3)*/;
binsz = (PG_GETARG_TIMESTAMP(2) - start) / PG_GETARG_INT32(3);
PG_RETURN_TIMESTAMP(JROUND((int)((PG_GETARG_TIMESTAMP(0) - start) /
binsz) * binsz + start));
}
---(end of broadcast)---
TIP 1: 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
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] width_bucket function for timestamps
Jim C. Nasby [EMAIL PROTECTED] writes: Sinte we already have width_bucket, I'd argue this should go in core. If someone's feeling adventurous, there should probably be a double precision version as well. Hrm... and maybe text... It's not clear to me why we have width_bucket operating on numeric and not float8 --- that seems like an oversight, if not outright misunderstanding of the type hierarchy. But if we had the float8 version, I think Jeremy's problem would be solved just by applying the float8 version to extract(epoch from timestamp). I don't really see the use-case for putting N versions of the function in there. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upgrading a database dump/restore
On Mon, Oct 09, 2006 at 11:50:10AM -0400, Mark Woodward wrote: That one is easy: there are no rules. We already know how to deal with catalog restructurings --- you do the equivalent of a pg_dump -s and reload. Any proposed pg_upgrade that can't cope with this will be rejected out of hand, because that technology was already proven five years ago. snip Dumping out a database is bad enough, but that's only the data, and that can takes (mostly) only hours. Recreating a large database with complex indexes can take days or hours for the data, hours per index, it adds up. I think you missed the point of the email you replied to. *catalog* changes are quick and (relativly) easy. Even with 10,000 tables, it would only take a few moments to rewrite the entire catalog to a new version. If it is a data format issue, maybe there should be a forum for a next gen version of the current data layout that is extensible without restructuring. This is not something that a couple people can go off and do and submit a patch, it is something that has to be supported and promoted from the core team, otherwise it won't happen. We all know that. Actually, the data format is not the issue either. The tuple structure hasn't changed that often. What has changed is the internal format of a few types, but postgresql could support both the old and the new types simultaneously. There has already been a statement from core-members that if someone comes up with a tool to handle the catalog upgrade, they'd be willing to keep code from older types around with the original oid so they'd be able to read the older version. The question is whether or not you all think it is worth doing. I've done consulting work for some very large companies that everyone has heard of. These sorts of things matter. People are working it, someone even got so far as dealing with most catalog upgrades. The hard part going to be making sure that even if the power fails halfway through an upgrade that your data will still be readable... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Prepared Statement Question
Strong, David [EMAIL PROTECTED] writes: There is similar code for Parameter Lists (ParamListInfo) and Result Format Codes (rformats). Unless we're missing something, a prepared statement would probably never change once prepared. I think you're missing something. Or are you just proposing that we could save one palloc per Bind operation? Trust me, that ain't worth worrying about. In any case the number of parameters appearing in Bind could be different from the number appearing in the statement --- the fact that that's an error doesn't allow you to not process the message before complaining. Along these lines, would it also be possible to keep an Executor State and Expression Context with the statement and just reset key parts of them, rather than rebuilding them from scratch each time a prepared statement is executed? Sounds highly bug-prone to me ... especially in the case where the preceding execution didn't complete normally ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prepared Statement Question
Tom, Thanks for the advice. Yes, we were looking at the possibility of saving the palloc(s) (malloc in some cases) on the statement. David From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Mon 10/9/2006 9:08 AM To: Strong, David Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Prepared Statement Question Strong, David [EMAIL PROTECTED] writes: There is similar code for Parameter Lists (ParamListInfo) and Result Format Codes (rformats). Unless we're missing something, a prepared statement would probably never change once prepared. I think you're missing something. Or are you just proposing that we could save one palloc per Bind operation? Trust me, that ain't worth worrying about. In any case the number of parameters appearing in Bind could be different from the number appearing in the statement --- the fact that that's an error doesn't allow you to not process the message before complaining. Along these lines, would it also be possible to keep an Executor State and Expression Context with the statement and just reset key parts of them, rather than rebuilding them from scratch each time a prepared statement is executed? Sounds highly bug-prone to me ... especially in the case where the preceding execution didn't complete normally ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upgrading a database dump/restore
On Mon, Oct 09, 2006 at 11:50:10AM -0400, Mark Woodward wrote: That one is easy: there are no rules. We already know how to deal with catalog restructurings --- you do the equivalent of a pg_dump -s and reload. Any proposed pg_upgrade that can't cope with this will be rejected out of hand, because that technology was already proven five years ago. snip Dumping out a database is bad enough, but that's only the data, and that can takes (mostly) only hours. Recreating a large database with complex indexes can take days or hours for the data, hours per index, it adds up. I think you missed the point of the email you replied to. *catalog* changes are quick and (relativly) easy. Even with 10,000 tables, it would only take a few moments to rewrite the entire catalog to a new version. If it is a data format issue, maybe there should be a forum for a next gen version of the current data layout that is extensible without restructuring. This is not something that a couple people can go off and do and submit a patch, it is something that has to be supported and promoted from the core team, otherwise it won't happen. We all know that. Actually, the data format is not the issue either. The tuple structure hasn't changed that often. What has changed is the internal format of a few types, but postgresql could support both the old and the new types simultaneously. There has already been a statement from core-members that if someone comes up with a tool to handle the catalog upgrade, they'd be willing to keep code from older types around with the original oid so they'd be able to read the older version. That's good to know. The question is whether or not you all think it is worth doing. I've done consulting work for some very large companies that everyone has heard of. These sorts of things matter. People are working it, someone even got so far as dealing with most catalog upgrades. The hard part going to be making sure that even if the power fails halfway through an upgrade that your data will still be readable... Well, I think that any *real* DBA understands and accepts that issues like power failure and hardware failure create situations where suboptimal conditions exist. :-) Stopping the database and copying the pg directory addresses this problem, upon failure, it is a simple mv bkdir pgdir, gets you started again. If you have a system on a good UPS and on reliable hardware, which is exactly the sort of deployment that would benefit most from an in place upgrade. There is no universal panacea where there is 0 risk, one can only mitigate risk. That being said, it should be the preferred method of upgrade with new versions not being released untill they can migrate cleanly. A dump/restore should be a last resort. Don't you think? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upgrading a database dump/restore
Martijn van Oosterhout kleptog@svana.org writes: The hard part going to be making sure that even if the power fails halfway through an upgrade that your data will still be readable... I think we had that problem solved too in principle: build the new catalogs in a new $PGDATA directory alongside the old one, and hard-link the old user table files into that directory as you go. Then pg_upgrade never needs to change the old directory tree at all. This gets a bit more complicated in the face of tablespaces but still seems doable. (I suppose it wouldn't work in Windows for lack of hard links, but anyone trying to run a terabyte database on Windows deserves to lose anyway.) The stuff that needed rethinking in the old pg_upgrade code, IIRC, had to do with management of transaction IDs and old WAL log. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Backbranch releases and Win32 locking
:(( Patch doesn't work. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 1: 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] Upgrading a database dump/restore
Mark, No one could expect that this could happen by 8.2, or the release after that, but as a direction for the project, the directors of the PostgreSQL project must realize that the dump/restore is becomming like the old locking vacuum problem. It is a *serious* issue for PostgreSQL adoption and arguably a real design flaw. directors? (looks around) Nobody here but us chickens, boss. If you're really interested in pg_upgrade, you're welcome to help out. Gavin Sherry, Zdenek, and Jonah Harris are working on it (the last separately, darn it). -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upgrading a database dump/restore
Josh Berkus wrote: Mark, No one could expect that this could happen by 8.2, or the release after that, but as a direction for the project, the directors of the PostgreSQL project must realize that the dump/restore is becomming like the old locking vacuum problem. It is a *serious* issue for PostgreSQL adoption and arguably a real design flaw. directors? (looks around) Nobody here but us chickens, boss. Action, Action!.. no wait, I mean CUT Mark, if you really want this, join one of the many teams who have tried to do it and help them. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Upgrading a database dump/restore
Mark, No one could expect that this could happen by 8.2, or the release after that, but as a direction for the project, the directors of the PostgreSQL project must realize that the dump/restore is becomming like the old locking vacuum problem. It is a *serious* issue for PostgreSQL adoption and arguably a real design flaw. directors? (looks around) Nobody here but us chickens, boss. If you're really interested in pg_upgrade, you're welcome to help out. Gavin Sherry, Zdenek, and Jonah Harris are working on it (the last separately, darn it). This is the most frustrating thing, I *wan't* to do these things, but I can't find any companies that are willing to pay me to do it, and having kids, I don't have the spare time to do it. I *have* a recommendations system already, but I can't even find the time to do the NetFlix Prize thing. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Upgrading a database dump/restore
Mark Woodward wrote: Mark, No one could expect that this could happen by 8.2, or the release after that, but as a direction for the project, the directors of the PostgreSQL project must realize that the dump/restore is becomming like the old locking vacuum problem. It is a *serious* issue for PostgreSQL adoption and arguably a real design flaw. directors? (looks around) Nobody here but us chickens, boss. If you're really interested in pg_upgrade, you're welcome to help out. Gavin Sherry, Zdenek, and Jonah Harris are working on it (the last separately, darn it). This is the most frustrating thing, I *wan't* to do these things, but I can't find any companies that are willing to pay me to do it, and having kids, I don't have the spare time to do it. Well that pretty much sums it up doesn't. If the people / users that want this feature, want it bad enough -- they will cough up the money to get it developed. If not then it likely won't happen because for most users in place upgrades really isn't a big deal. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump exclusion switches and functions/types
On Sat, Oct 07, 2006 at 05:29:03PM -0400, Tom Lane wrote: So the only regex patterns you can't write directly are dot, R* and R? for which you can use these locutions: . = ? R* = (R+|) R? = (R|) (Perhaps this should be documented somewhere...) Yes, please. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: 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] array_accum aggregate
* Tom Lane ([EMAIL PROTECTED]) wrote:
Stephen Frost [EMAIL PROTECTED] writes:
I was hoping to do that, but since it's an aggregate the ffunc format is
pre-defined to require accepting the 'internal state' and nothing else,
and to return 'anyelement' or 'anyarray' one of the inputs must be an
'anyelement' or 'anyarray', aiui.
Hmm ... I hadn't been thinking about what the state type would need to
be, but certainly bytea is a lie given what you're really doing.
Indeed. I've updated the functions quite a bit to clean things up,
including: Added many more comments, removed the unnecessary 'storage*'
pointer being used, created my own structure for tracking state
information, created a seperate memory context (tied to the AggContext),
correctly handle NULL values, and changed the ffunc to use
makeArrayResult.
I also tried just tried using polymorphic types for the functions and
for the aggregate and it appeared to just work:
create function aaccum_sfunc (anyarray, anyelement) returns anyarray
language 'C' AS 'aaccum.so', 'aaccum_sfunc'
;
create function aaccum_ffunc (anyarray) returns anyarray language
'C' AS '/data/sfrost/postgres/arrays/aaccum.so', 'aaccum_ffunc'
;
create aggregate aaccum (
sfunc = aaccum_sfunc,
basetype = anyelement,
stype = anyarray,
finalfunc = aaccum_ffunc
);
select aaccum(generate_series) from generate_series(1,5);
aaccum
-
{1,2,3,4,5}
(1 row)
(test is a table with one varchar column, abc)
select aaccum(abc) from test;
aaccum
-
{a,b,c}
(1 row)
(Added a column called 'hi', set to 'a', added b,b and c,b)
select hi,aaccum(abc) from test group by hi;
hi | aaccum
+-
b | {b,c}
a | {a,b,c}
(2 rows)
It makes some sense that it would work as an 'anyarray' is just a
variable-length type internally and so long as nothing else attempts to
make sense out of our 'fake array' everything should be fine.
The latest version also appears to be a bit faster than the prior
version. I'm going to be running a very large query shortly using
this aaccum and will report back how it goes. Please let me know if
there are any other improvments or changes I should make. I'd like to
submit this to -patches w/ the appropriate entries to have it be
included in the core distribution. Is it acceptable to reuse the
'array_accum' name even though it was used in the documentation as an
example? I'm thinking yes, but wanted to check.
Thanks!
Stephen
#include postgres.h
#include fmgr.h
#include utils/array.h
#include utils/memutils.h
#include nodes/execnodes.h
PG_MODULE_MAGIC;
/* Structure for storing our pointers to the
* ArrayBuildState for the array we are building
* and the MemoryContext in which it is being
* built. Note that this structure is
* considered a bytea externally and therefore
* must open with an int32 defining the length. */
typedef struct {
int32 vl_len;
ArrayBuildState *astate;
MemoryContext arrctx;
} aaccum_info;
/* The state-transistion function for our aggregate. */
PG_FUNCTION_INFO_V1(aaccum_sfunc);
Datum
aaccum_sfunc(PG_FUNCTION_ARGS)
{
aaccum_info *ainfo;
AggState *aggstate;
/* Make sure we are in an aggregate. */
if (!fcinfo-context || !IsA(fcinfo-context, AggState))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(Can not call aaccum_sfunc as a non-aggregate)));
aggstate = (AggState*) fcinfo-context;
/* Initial call passes NULL in for our state variable.
* Allocate memory and get set up. */
if (PG_ARGISNULL(0)) {
/* Allocate memory to hold the pointers to the ArrayBuildState
* and the MemoryContext where we are building the array. Note
* that we can do this in the CurrentMemoryContext because when
* we return the storage bytea will be copied into the AggState
* context by the caller and passed back to us on the next call. */
ainfo = (aaccum_info*) palloc(sizeof(aaccum_info));
ainfo-vl_len = sizeof(aaccum_info);
ainfo-astate = NULL;
/* New context created which will store our array accumulation.
* The parent is the AggContext for this query since it needs to
* persist for the same timeframe as the state value.
* The state value holds the pointers to the ArrayBuildState and this
* MemoryContext through the aaccum_info structure. */
ainfo-arrctx = AllocSetContextCreate(aggstate-aggcontext, ArrayAccumCtx,
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
} else {
/* Our state variable is non-null, therefore it must be an existing
* ainfo structure. */
ainfo = (aaccum_info*) PG_GETARG_BYTEA_P(0);
}
/* Pull the element to be added and pass it
Re: [HACKERS] pg_dump exclusion switches and functions/types
On Sat, Oct 07, 2006 at 06:22:19PM -0700, David Fetter wrote: On Fri, Oct 06, 2006 at 10:28:21PM -0400, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: The existing patch's behavior is that the rightmost switch wins, ie, if an object's name matches more than one pattern then it is included or excluded according to the rightmost switch it matches. This is, erm, poorly documented, but it seems like useful behavior so I don't have an objection myself. I don't know, it sounds like it's the source of the confusion you identify later. My first thought is that the rule should be to apply all the inclusion switches (implicitly including everything if there are none), then apply all the exclusion switches. +1 :) Order-dependent switches are a giant foot gun. They're also very powerful, as anyone who's ever used them in a non-trivial rsync (or rdiff-backup) scenareo can tell you. What if you want to exclude all of a schema except for a few objects (granted, right now we're limited to just tables...)? Here's a real example, from my rdiff-backup exclude files list: + /opt/local/var/db/dports + /opt/local/var/log + /opt/local/etc /opt Note that rdiffbackup applies the first option that matches a file/directory, not the last. So this says to exclude all of /opt, except for /opt/local/var/db/dports, etc. If this was done as 'last operator takes priority', you'd just reverse the order of the list: pg_dump --exclude-schema /opt --include-table opt.local_var_db_dports --include-table opt.local_var_log --include-table opt.local_etc If we processed all includes first, you lose this ability. For simple cases, it might not matter, but think about wanting to back up an entire database, except for schema opt, but you do want a few objects from within opt. Without following some kind of ordering on includes and excludes, that's next to impossible. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Interface of the R-tree in order to work with postgresql
On Oct 9, 2006, at 7:21 AM, Martijn van Oosterhout wrote: On Mon, Oct 09, 2006 at 09:15:58AM -0500, jorge alberto wrote: I want to know how can I add a new spatial access method into the postgresql (I'm doing research on spatial access methods( reading a lot of papers and programming a lot too ) but also I want to know how can I add my new data structure( if someday i get it, of course =) ) in the postgresql, I mean where can i find the .h that describes the interface that a spatial access method, like the R-tree, must have in order to work with postgresql. I would start by reading the documentation, specifically the part about GiST. Once you have understood the concept and code there, go to reading the rtree code in the backend. That should answer almost all your questions. Index Access Methods (Ch. 48) for a more general interface may also be useful, since he implies that R-Tree is not the only spatial access method he is interested in. While some spatial access methods, such as R-Tree, are well-suited for a GiST implementation, it is not a particularly appropriate access method (assumption mismatch) for some other interesting spatial index types that a researcher might be interested in implementing. Cheers, J. Andrew Rogers (who is also implementing new spatial indexes...) [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump exclusion switches and functions/types
On Mon, Oct 09, 2006 at 12:07:29PM -0500, Jim C. Nasby wrote: On Sat, Oct 07, 2006 at 06:22:19PM -0700, David Fetter wrote: On Fri, Oct 06, 2006 at 10:28:21PM -0400, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: The existing patch's behavior is that the rightmost switch wins, ie, if an object's name matches more than one pattern then it is included or excluded according to the rightmost switch it matches. This is, erm, poorly documented, but it seems like useful behavior so I don't have an objection myself. I don't know, it sounds like it's the source of the confusion you identify later. My first thought is that the rule should be to apply all the inclusion switches (implicitly including everything if there are none), then apply all the exclusion switches. +1 :) Order-dependent switches are a giant foot gun. They're also very powerful, as anyone who's ever used them in a non-trivial rsync (or rdiff-backup) scenareo can tell you. What if you want to exclude all of a schema except for a few objects (granted, right now we're limited to just tables...)? You make an important distinction here, and thanks for doing that. :) IMHO, order-dependent switches are appropriate for a configuration file and inappropriate for the command line. The pg_hba.conf file is a great example of a place where order dependence is a good idea. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] continuing daily testing of dbt2 against postgresql
On Sun, Oct 08, 2006 at 05:26:11PM -0700, Mark Wong wrote: I made another couple of gross mistakes of forgetting to compile PostgreSQL with --enable-thread-safe and enabling the user space irq balancing program in Linux. I've restarted the histories with 600 and What's the advantage of irq balancing in user space as opposed to the kernel (which is the default, right?) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] continuing daily testing of dbt2 against postgresql
Jim C. Nasby wrote: On Sun, Oct 08, 2006 at 05:26:11PM -0700, Mark Wong wrote: I made another couple of gross mistakes of forgetting to compile PostgreSQL with --enable-thread-safe and enabling the user space irq balancing program in Linux. I've restarted the histories with 600 and What's the advantage of irq balancing in user space as opposed to the kernel (which is the default, right?) Linux doesn't have irq balancing in the kernel. They've made the decision to leave that to a user space process. The irq balancing flag in the kernel is just to enable the hooks for a user space program. Mark ---(end of broadcast)--- TIP 1: 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] width_bucket function for timestamps
On Mon, Oct 09, 2006 at 12:02:12PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Sinte we already have width_bucket, I'd argue this should go in core. If someone's feeling adventurous, there should probably be a double precision version as well. Hrm... and maybe text... It's not clear to me why we have width_bucket operating on numeric and not float8 --- that seems like an oversight, if not outright misunderstanding of the type hierarchy. But if we had the float8 version, I think Jeremy's problem would be solved just by applying the float8 version to extract(epoch from timestamp). I don't really see the use-case for putting N versions of the function in there. Well, it would be nice to have a timestamp version so that users didn't have to keep typing extract(epoch from timestamp)... but yeah, I suspect that would work fine for timestamps. For intervals I suspect you could just convert to seconds (if we're going to add timestamps, it seems like we should add intervals as well). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] width_bucket function for timestamps
Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Oct 09, 2006 at 12:02:12PM -0400, Tom Lane wrote: ... I think Jeremy's problem would be solved just by applying the float8 version to extract(epoch from timestamp). Thinko there ... I meant to type extract(epoch from interval). Well, it would be nice to have a timestamp version so that users didn't have to keep typing extract(epoch from timestamp)... but yeah, I suspect that would work fine for timestamps. For intervals I suspect you could just convert to seconds (if we're going to add timestamps, it seems like we should add intervals as well). This is exactly the slippery slope I don't care to start down. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump exclusion switches and functions/types
Jim C. Nasby [EMAIL PROTECTED] writes: On Sat, Oct 07, 2006 at 06:22:19PM -0700, David Fetter wrote: On Fri, Oct 06, 2006 at 10:28:21PM -0400, Gregory Stark wrote: My first thought is that the rule should be to apply all the inclusion switches (implicitly including everything if there are none), then apply all the exclusion switches. +1 :) Order-dependent switches are a giant foot gun. They're also very powerful, as anyone who's ever used them in a non-trivial rsync (or rdiff-backup) scenareo can tell you. Sure, but the question is whether that incremental gain in capability is worth the extra logical complexity. I'm inclined to think that many more users would get burned by the complexity than would have use for it. Considering that we've gotten along this long with only the most primitive selection capabilities in pg_dump, it doesn't seem like there's an enormous demand for highly refined capabilities. (And I agree with David's comment that it might be better to reserve such behavior for a configuration file than to put it on the command line.) regards, tom lane ---(end of broadcast)--- TIP 1: 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] OT: Is there a LinkedIn group for Postgresql?
[EMAIL PROTECTED] (Tzahi Fadida) writes: Hi, Is there a LinkedIn group for Postgresql/Hackers list. If there is, how can i join? The usual way LinkedIn works is that if there are people you know that do PostgreSQL work, they may link to others doing the same. You should probably see about linking to people you know; you will likely then find relevant relationships. -- output = (cbbrowne @ linuxfinances.info) http://cbbrowne.com/info/nonrdbms.html If Miss Cleo is a psychic, how come I have to call her? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Fwd: pg_dump VS alter database ... set search_path ...
On 10/9/06, Tom Lane [EMAIL PROTECTED] wrote: Duplication of code and functionality with pg_dumpall. Well, then -C option of pg_dump can be considered as duplication of pg_dumpall's functionality too, right? I'd want to see some thought about how to resolve that, not just a quick copy-some-code- from-pg_dumpall-into-pg_dump. You also need to explain why this issue should be treated differently from users and groups ... a dump won't restore correctly without that supporting context either. I have no objection to rethinking the division of labor between the two programs, but let's end up with something that's cleaner not uglier. -C option is useful in cases like mine. Example: in a PG cluster of 100 databases there is one database containing 10 schemes; this database is being dumped every night and restored on 3 separate machines, where some operations are then being executed). pg_dumpall is not a solution in this case. Moreover, playing with ALTER USER ... SET search_path TO ... may not the best solution too - there may be different users sets on different hosts, and, what is more important, if I (developing my app) add new schema to that database, I should run number of hosts * number of roles ALTERs, this is not good. When I write ALTER DATABASE ... SET ... I expect that corresponding *database's* property will be modified. When I choose -C option of pg_dump I expect that CREATE DATABASE with all its properties (in ALTER stmts) will be printed. I think it's not a question of division of labor between the two programs. As for users and groups - I do not understand why you are mentioning it. I'm talking about -C option, and complain that it doesn't allow me to dump/restore the database with its properties. I suppose, users/roles shouldn't be involved in this discussion. Maybe my understanding is wrong - I'll be glad to hear why. -- Best regards, Nikolay ---(end of broadcast)--- TIP 1: 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] [PATCHES] Generic Monitoring Framework with DTrace patch
All, I'll be fixing this documentation issue now that I have full information. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] continuing daily testing of dbt3 against postgresql
Hi everyone, I have now resumed producing daily results of dbt-3 against PostgreSQL CVS code at the 10 GB scale factor with results here: http://dbt.osdl.org/dbt3.html I'm currently only running the load the power test because of the amount of time it takes to run through the power test. The load test finishes within an hour while a power test finished in about 14 hours. Additionally the refresh streams are not executed. The two longest running queries are currently Q9 (~ 7 hours) and Q21 (~ 3 hours). EXPLAIN output can be found for each individual query by following the Power Test then query plans links on the bottom of a test result's page. EXPLAIN ANALYZE output can be found on the query results link under the query plans link. I'm still working on generating a more concise iostat report but a description of the disk layout can be found on the url listed above. Regards, Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] width_bucket function for timestamps
On Mon, Oct 09, 2006 at 01:49:37PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Oct 09, 2006 at 12:02:12PM -0400, Tom Lane wrote: ... I think Jeremy's problem would be solved just by applying the float8 version to extract(epoch from timestamp). Thinko there ... I meant to type extract(epoch from interval). Except that the patch is for timestamp support, not intervals. Well, it would be nice to have a timestamp version so that users didn't have to keep typing extract(epoch from timestamp)... but yeah, I suspect that would work fine for timestamps. For intervals I suspect you could just convert to seconds (if we're going to add timestamps, it seems like we should add intervals as well). This is exactly the slippery slope I don't care to start down. I guess I'm confused as to how this is any different from other functions where we've provided multiple input arguments, such as the aggregate functions. It certainly doesn't seem like it'd take a lot of extra code to support this... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: 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] continuing daily testing of dbt3 against postgresql
Mark Wong wrote: Hi everyone, I have now resumed producing daily results of dbt-3 against PostgreSQL CVS code at the 10 GB scale factor with results here: http://dbt.osdl.org/dbt3.html I'm currently only running the load the power test because of the amount of time it takes to run through the power test. The load test finishes within an hour while a power test finished in about 14 hours. Additionally the refresh streams are not executed. The two longest running queries are currently Q9 (~ 7 hours) and Q21 (~ 3 hours). FWIW: that findings roughly match with the testing I did several weeks ago but the two fixes tom put in after that already improved the situation but there is still a lot to do there :-( Stefan ---(end of broadcast)--- TIP 1: 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] OT: Is there a LinkedIn group for Postgresql?
On Mon, Oct 09, 2006 at 05:56:41PM +, Chris Browne wrote: [EMAIL PROTECTED] (Tzahi Fadida) writes: Hi, Is there a LinkedIn group for Postgresql/Hackers list. If there is, how can i join? The usual way LinkedIn works is that if there are people you know that do PostgreSQL work, they may link to others doing the same. You should probably see about linking to people you know; you will likely then find relevant relationships. I believe there's also some kind of group functionality, though I don't know how to find it on the site. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] OT: Is there a LinkedIn group for Postgresql?
Groups are created by some kind of organization. For example, a mailing list of java professionals i am listed on. There is no need to advertise because if you are a part of that organization you can ask the organization leader to add you to the group. Seeing that no one has volunteered a group for PostgreSQL in the list suggests there is no such group. (P.s. if someone will open one in the future and find this message in the archive, please add me to the group. 10x.) On Monday 09 October 2006 21:22, Jim C. Nasby wrote: On Mon, Oct 09, 2006 at 05:56:41PM +, Chris Browne wrote: [EMAIL PROTECTED] (Tzahi Fadida) writes: Hi, Is there a LinkedIn group for Postgresql/Hackers list. If there is, how can i join? The usual way LinkedIn works is that if there are people you know that do PostgreSQL work, they may link to others doing the same. You should probably see about linking to people you know; you will likely then find relevant relationships. I believe there's also some kind of group functionality, though I don't know how to find it on the site. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump exclusion switches and functions/types
On Mon, Oct 09, 2006 at 01:59:18PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Sat, Oct 07, 2006 at 06:22:19PM -0700, David Fetter wrote: On Fri, Oct 06, 2006 at 10:28:21PM -0400, Gregory Stark wrote: My first thought is that the rule should be to apply all the inclusion switches (implicitly including everything if there are none), then apply all the exclusion switches. +1 :) Order-dependent switches are a giant foot gun. They're also very powerful, as anyone who's ever used them in a non-trivial rsync (or rdiff-backup) scenareo can tell you. Sure, but the question is whether that incremental gain in capability is worth the extra logical complexity. I'm inclined to think that many more users would get burned by the complexity than would have use for it. Considering that we've gotten along this long with only the most primitive selection capabilities in pg_dump, it doesn't seem like there's an enormous demand for highly refined capabilities. (And I agree with David's comment that it might be better to reserve such behavior for a configuration file than to put it on the command line.) I can certainly see the logic in putting the more advanced capability in a config file of some kind (though, I think a simple include/exclude file is best for this...) The question becomes: do we want incompatible behavior between the config file and the command line? And which over-rides what? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] continuing daily testing of dbt2 against postgresql
On Mon, Oct 09, 2006 at 10:37:32AM -0700, Mark Wong wrote: Jim C. Nasby wrote: On Sun, Oct 08, 2006 at 05:26:11PM -0700, Mark Wong wrote: I made another couple of gross mistakes of forgetting to compile PostgreSQL with --enable-thread-safe and enabling the user space irq balancing program in Linux. I've restarted the histories with 600 and What's the advantage of irq balancing in user space as opposed to the kernel (which is the default, right?) Linux doesn't have irq balancing in the kernel. They've made the decision to leave that to a user space process. The irq balancing flag in the kernel is just to enable the hooks for a user space program. Oooh, interesting... I wonder how many installs out there are running without IRQ balancing enabled. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump exclusion switches and functions/types
On Mon, Oct 09, 2006 at 02:34:09PM -0500, Jim C. Nasby wrote: On Mon, Oct 09, 2006 at 01:59:18PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Sat, Oct 07, 2006 at 06:22:19PM -0700, David Fetter wrote: On Fri, Oct 06, 2006 at 10:28:21PM -0400, Gregory Stark wrote: My first thought is that the rule should be to apply all the inclusion switches (implicitly including everything if there are none), then apply all the exclusion switches. +1 :) Order-dependent switches are a giant foot gun. They're also very powerful, as anyone who's ever used them in a non-trivial rsync (or rdiff-backup) scenareo can tell you. Sure, but the question is whether that incremental gain in capability is worth the extra logical complexity. I'm inclined to think that many more users would get burned by the complexity than would have use for it. Considering that we've gotten along this long with only the most primitive selection capabilities in pg_dump, it doesn't seem like there's an enormous demand for highly refined capabilities. (And I agree with David's comment that it might be better to reserve such behavior for a configuration file than to put it on the command line.) I can certainly see the logic in putting the more advanced capability in a config file of some kind (though, I think a simple include/exclude file is best for this...) The question becomes: do we want incompatible behavior between the config file and the command line? And which over-rides what? The way I've cut this Gordian knot in the past is simply to make command-line and file-based options for a given thing (e.g. exclusion/inclusion) mutually exclusive and throw an error if somebody attempts to mix them. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] width_bucket function for timestamps
Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Oct 09, 2006 at 01:49:37PM -0400, Tom Lane wrote: This is exactly the slippery slope I don't care to start down. I guess I'm confused as to how this is any different from other functions where we've provided multiple input arguments, such as the aggregate functions. The salient reason is that the spec only defines width_bucket for numeric input arguments, whereas stuff like max/min is defined *by the spec* for other data types. Since there's no spec-based argument for allowing width_bucket for other datatypes, and only an (IMHO) very weak use-case for it, I don't think we should add the clutter. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] continuing daily testing of dbt2 against postgresql
Luke Lonergan wrote: +1 Mark, can you quantify the impact of not running with IRQ balancing enabled? Yeah, I'll try to have that done within a couple of days. Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] continuing daily testing of dbt2 against
+1 Mark, can you quantify the impact of not running with IRQ balancing enabled? - Luke Msg is shrt cuz m on ma treo -Original Message- Original message contents unavailable ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On 10/5/06, Jim Nasby [EMAIL PROTECTED] wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote: regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01 09:30:41'::timestamp);?column? --14 days 14:28:19
(1 row) should be reporting '350:28:19' instead. This is a hack that was done to minimize the changes in the regression test expected outputs when we changed type interval from months/
seconds to months/days/seconds.But I wonder whether it wasn't a dumb idea. It is certainly inconsistent, as noted in the code comments. I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to the subtraction result for themselves.Not sure what the fallout would be, though.I suspect there's applications out there that are relying on that
being nicely formated for display purposes.I agree it should be removed, but we might need a form of backwardscompatibility for a version or two...I am personally of the opinion that display logic should never be put into the database. Applications that rely on the database formatting - that is tightly coupling your application to the database which does not follow good programming principles.
None-the-less, the feature would be nice and may be very valuable for reporting.== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
Re: [HACKERS] width_bucket function for timestamps
On Mon, 9 Oct 2006, Tom Lane wrote: It's not clear to me why we have width_bucket operating on numeric and not float8 --- that seems like an oversight, if not outright misunderstanding of the type hierarchy. Would that make the below a lot faster? But if we had the float8 version, I think Jeremy's problem would be solved just by applying the float8 version to extract(epoch from timestamp). I don't really see the use-case for putting N versions of the function in there. I found the function I used before I implemented the C version. It was significantly slower, which is why I wrote the C version. -- given a date range and a number of buckets, round the given date to one -- of the buckets such that any number of dates within the date range passed -- in to this function will only return up to the number of buckets unique -- values CREATE OR REPLACE FUNCTION date_width_bucket (tm TIMESTAMP WITHOUT TIME ZONE, low TIMESTAMP WITHOUT TIME ZONE, high TIMESTAMP WITHOUT TIME ZONE, nbuckets INTEGER ) RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$ SELECT ((EXTRACT(epoch FROM $3) - EXTRACT(epoch FROM $2)) / $4) * (width_bucket(EXTRACT(epoch FROM $1)::NUMERIC, EXTRACT(epoch FROM $2)::NUMERIC, EXTRACT(epoch FROM $3)::NUMERIC, $4) - 1) * '1 second'::INTERVAL + $2; $$ LANGUAGE sql IMMUTABLE STRICT; -- I don't think they could put him in a mental hospital. On the other hand, if he were already in, I don't think they'd let him out. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] width_bucket function for timestamps
On Mon, Oct 09, 2006 at 03:49:50PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Oct 09, 2006 at 01:49:37PM -0400, Tom Lane wrote: This is exactly the slippery slope I don't care to start down. I guess I'm confused as to how this is any different from other functions where we've provided multiple input arguments, such as the aggregate functions. The salient reason is that the spec only defines width_bucket for numeric input arguments, whereas stuff like max/min is defined *by the spec* for other data types. Since there's no spec-based argument for allowing width_bucket for other datatypes, and only an (IMHO) very weak use-case for it, I don't think we should add the clutter. Catalog or code clutter? ISTM that it wouldn't take much extra work at all to provide this for timestamps or intervals... In any case, having a faster version that used double certainly seems like it'd be useful. It'd probably allow the OP to go back to his original, simple version. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] width_bucket function for timestamps
Jeremy Drake [EMAIL PROTECTED] writes: I found the function I used before I implemented the C version. It was significantly slower, which is why I wrote the C version. I would imagine that most of the problem is the NUMERIC arithmetic that's doing. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] width_bucket function for timestamps
On Mon, 2006-10-09 at 12:02 -0400, Tom Lane wrote: It's not clear to me why we have width_bucket operating on numeric and not float8 I asked about this when I originally implemented width_bucket(), I recall[1]. At the time, there was scepticism about whether it was even worth implementing width_bucket(), let alone providing multiple implementations of it. I'd be happy to provide a float8 implementation (I may even have one lying around somewhere...) -Neil [1] http://archives.postgresql.org/pgsql-patches/2004-04/msg00259.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On Mon, Oct 09, 2006 at 02:57:28PM -0500, Aaron Bono wrote:
On 10/5/06, Jim Nasby [EMAIL PROTECTED] wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/
seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to
the
subtraction result for themselves. Not sure what the fallout would
be,
though.
I suspect there's applications out there that are relying on that
being nicely formated for display purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
I am personally of the opinion that display logic should never be put into
the database. Applications that rely on the database formatting - that is
tightly coupling your application to the database which does not follow good
programming principles.
None-the-less, the feature would be nice and may be very valuable for
reporting.
I agree in general, except most languages have terrible support for
time/date data, so I can see a much bigger case for the database being
able to do it (and it's not like we'll be removing justify_*). Be that
as it may, there are probably apps out there that will break if this is
just changed.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Jim, I agree in general, except most languages have terrible support for time/date data, so I can see a much bigger case for the database being able to do it (and it's not like we'll be removing justify_*). Be that as it may, there are probably apps out there that will break if this is just changed. Many. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Revise psql pattern-matching switches
Tom Lane wrote: Log Message: --- Revise psql pattern-matching switches as per discussion. The rule is now Uh, you mean pg_dump, right? --- to process all inclusion switches then all exclusion switches, so that the behavior is independent of switch ordering. Use of -T does not cause non-table objects to be suppressed. And the patterns are now interpreted the same way psql's \d commands do it, rather than as pure regex commands; this allows for example -t schema.tab to do what it should have been doing all along. Re-enable the --blobs switch to do something useful, ie, add back blobs into a dump they were otherwise suppressed from. Modified Files: -- pgsql/doc/src/sgml/ref: pg_dump.sgml (r1.89 - r1.90) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/pg_dump.sgml.diff?r1=1.89r2=1.90) pgsql/src/bin/pg_dump: common.c (r1.93 - r1.94) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/common.c.diff?r1=1.93r2=1.94) pg_dump.c (r1.452 - r1.453) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.c.diff?r1=1.452r2=1.453) pg_dump.h (r1.129 - r1.130) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.h.diff?r1=1.129r2=1.130) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] width_bucket function for timestamps
Neil Conway [EMAIL PROTECTED] writes: On Mon, 2006-10-09 at 12:02 -0400, Tom Lane wrote: It's not clear to me why we have width_bucket operating on numeric and not float8 I asked about this when I originally implemented width_bucket(), I recall[1]. At the time, there was scepticism about whether it was even worth implementing width_bucket(), let alone providing multiple implementations of it. I'd be happy to provide a float8 implementation (I may even have one lying around somewhere...) It's probably too late for 8.2, unless some other compelling reason to force an initdb surfaces. But if you can find the code, please stick it in when 8.3 devel starts. As it stands, one must do an explicit coercion to numeric to use width_bucket() with floats; which is tedious, slow, and nowhere required by the spec. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Revise psql pattern-matching switches as per discussion.
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Log Message: --- Revise psql pattern-matching switches as per discussion. The rule is now Uh, you mean pg_dump, right? Sheesh, and I did read that message over twice before committing :-(. Long day, time for a break. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump exclusion switches and functions/types
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sure, but the question is whether that incremental gain in capability is worth the extra logical complexity. I'm inclined to think that many more users would get burned by the complexity than would have use for it. Considering that we've gotten along this long with only the most primitive selection capabilities in pg_dump, it doesn't seem like there's an enormous demand for highly refined capabilities. I disagree - we lose a lot of flexibility by taking out the ordering, and, as was pointed out to me when I first started this patch a while ago, we might as well front-load all the complexity and changes now rather than adding them in release by release. I'm also not sure why the regex should be changed to something even more non-standard than the current POSIX ones. Finally, I'm surprised at the apparent willingness at this point to shatter backwards-compatibility with previous -t scripts, as this was an option I raised early on but met strong resistance, thus the current compromise of allowing existing scripts to run unaltered, while adding in the ability to do some regular expressions. The regex stuff was discussed in January, and the patch submitted in July, so it seems a little rushed to be changing the underlying behavior so quickly right now (that behavior being the ability to control which tables and schemas to dump). I think the original post about the request to exclude a single table and still dump other objects is a fair one, but I think we've morphed far beyond solving that problem. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200610092003 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFKuPFvJuQZxSWSsgRAjAxAJ9oY5HCM4KxmpLEU56eCMJauHBhFgCfcyDt R5yf5SKKBeBHJ2gdRlE1Pqs= =rIxZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] array_accum aggregate
* Stephen Frost ([EMAIL PROTECTED]) wrote: I'm going to be running a very large query shortly using this aaccum and will report back how it goes. It went *very* well, actually much better than I had originally expected. This query used to take over 12 hours to complete (about 11 of which was after the main table involved was sorted). With this new aaccum in place the whole query only took about an hour, most of which was the sort and join required by the query. The aggregation (aaccum) and r_hist() (R histogram function generating PNGs) took only a few minutes. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Backbranch releases
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Sure, and stamping. How far back do you want to go? We might as well go back to 7.3 --- I saw Teodor back-patched some of his contrib/ltree fixes that far. Back branches are ready for release. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] query optimization with UDFs
Hello Everybody, I have a question about optimization of queries which includes UDFs. Does anybody know what the Postgres does for optimizing the queries with UDFs? Does the Postgres query optimizer do anything special with UDFs? Thanks, Jungmin Shin
Re: [HACKERS] query optimization with UDFs
On Mon, 2006-10-09 at 22:49 -0400, jungmin shin wrote: Does anybody know what the Postgres does for optimizing the queries with UDFs? The optimizer considers function volatility to avoid reevaluating UDFs needlessly, and to use index scans on predicates involving a function. Also, functions defined in the SQL language will be inlined into the function call site, when possible. That's all that comes to mind at the moment... Notably, the optimizer doesn't have support for realistic costing of UDFs: it can't tell how expensive evaluating a UDF is going to be, nor the number and distribution of the rows that will be produced by a set-returning function. The Berkeley release of Postgres supported expensive function optimization (a.k.a xfunc), which was an implementation of Hellerstein and Stonebraker's work on Predicate Migration[1]. That code is no longer in the current Postgres source. BTW, I think it would make sense to implement a limited subset of the xfunc ideas: add options to CREATE FUNCTION to allow cost information to be specified, and then take advantage of this information instead of using the existing constant kludges. This would be a tangible improvement, and would have minimal impact on the planner. A further improvement would be predicate migration: the xfunc code modified the optimizer to consider pulling predicates up above joins in the query plan. The traditional assumption is that all predicates are cheap, so they are always pushed down in the hopes that this will reduce the size of intermediate result sets. Naturally, this is not always wise with expensive UDFs. After the basics have been done, it might be interesting to implement this, provided it can be done without increasing the planner's search space too much. -Neil [1] http://db.cs.berkeley.edu/jmh/miscpapers/sigmod93.pdf ---(end of broadcast)--- TIP 1: 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] array_accum aggregate
On 10/10/06, Stephen Frost [EMAIL PROTECTED] wrote: * Stephen Frost ([EMAIL PROTECTED]) wrote: I'm going to be running a very large query shortly using this aaccum and will report back how it goes. It went *very* well, actually much better than I had originally expected. This query used to take over 12 hours to complete (about 11 of which was after the main table involved was sorted). With this new aaccum in place the whole query only took about an hour, most of which was the sort and join required by the query. The aggregation (aaccum) and r_hist() (R histogram function generating PNGs) took only a few minutes. very cool, and definately useful imo. i use array_accum all the time, and have not always been happy with its performance. if your stuff passes muster, would be nice to see it move into core :-). merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
