Re: [HACKERS] Moving sequences to another schema
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I think this is done by AddRelationRawConstraints. You'd have to get > the parsetree of the default expression. I think you could get that by > applying raw_parser() to pg_attrdef.adsrc. Not adsrc --- that's not trustworthy. In practice I think you could just assume you know what the default expression ought to be, and store a new one without looking at the old. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problem with dblink regression test
I have no clue why the mailling list is eating my original messages, unless it's because I attached a diff to them... in any case, applying http://stats.distributed.net/~buildfarm/patch provides a listing of what all the binaries and libraries in a buildfarm install are linking against. I couldn't figure out a decent way to send that info to pgbuildfarm.org, but http://stats.distributed.net/~buildfarm/libcheck.log is that info for a run. Based on the logfile, it looks like Tom's guess is correct that psql (and other binaries) are linking to the buildfarm libraries, while dblink.so (and other libraries) are linking against the system postgresql libraries. If someone wants to point me in the right direction I'll try and fix this, since I'm guessing it's just a make issue (or maybe a buildfarm issue). Actually, looking at my config (http://stats.distributed.net/~buildfarm/build-farm.conf), could the --with-libraries=/usr/local/lib be the issue, and if so, what's the proper way to handle system libraries (like libintl) living in /usr/local/lib and not /usr/lib? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Questions on extending a relation
"Tom Lane" <[EMAIL PROTECTED]> writes > > Yes. That's intentional --- otherwise they'd all block each other. > So if I saw the last two pages on a disk relation are half full, that's nothing wrong? > > Why wouldn't we replay xlog? Note in particular that the bgwriter is > not allowed to push page B to disk until the xlog entry describing the > index change has been flushed to disk. Since that will come after the > xlog entry about the heap change, both changes are necessarily on-disk > in the xlog, and both will be remade during replay. > Yes, I made a mistake. We reply xlog in any ways (no matter the transaction commits or not). Thanks, Qingqing ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] ("Jonah H.
Harris") wrote:
> I don't recommend discussion for this in this thread, but it could
> also tie in with the packages support we've discussed and (although
> some may argue this), compiling the PL to bytecode and using that.
This makes me think of the old jwz quote...
"Some people, when confronted with a problem, think 'I know, I'll
use regular expressions.' Now they have two problems."
-- Jamie Zawinski, on comp.lang.emacs
There are essentially four choices:
1. Embed a JVM in PostgreSQL, and use that; the fact that there are
already multiple "pljava" implementations suggests that it may be
difficult to pick a strategy...
2. Embed some clone of CLR in PostgreSQL, let's say, MONO.
I don't think there's a suitable BSDL'ed option...
3. Embed Parrot (the Perl/Python thing) in PostgreSQL. (Not that
Parrot can be considered "done".)
4. Make up a PostgreSQL-specific bytecode interpreter.
I'm quite sure that this leads to adding to the problems...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/nonrdbms.html
Love the scientific sampling language, when any sample that is
selected from Usenet readers and additionally self-selected is about
as representative as a wombat is of European wildlife.
-- Madeleine Page
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Moving sequences to another schema
When altering a sequence created by a SERIAL column type (i do this by examining pg_depend to avoid moving any other sequences that are 'foreign'), i need to recreate the default expression for the SERIAL column (stored in pg_attrdef.adbin). Is there an API to do that, or do i have to recreate the executable expression tree from scratch? Or am i missing something completely... Does ALTER TABLE/RENAME code help you? You can rename sequences with that... Chris ---(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] GiST concurrency commited
I think the whole GiST limitations page can be removed now... http://developer.postgresql.org/docs/postgres/limitations.html Chris ---(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] Moving sequences to another schema
On Tue, Jun 28, 2005 at 01:43:27AM +0200, Bernd Helmle wrote: > When altering a sequence created by a SERIAL column type (i do this by > examining pg_depend to avoid moving any other sequences that are > 'foreign'), i need to recreate the default expression for the SERIAL column > (stored in pg_attrdef.adbin). Is there an API to do that, or do i have to > recreate the executable expression tree from scratch? Or am i missing > something completely... I think this is done by AddRelationRawConstraints. You'd have to get the parsetree of the default expression. I think you could get that by applying raw_parser() to pg_attrdef.adsrc. -- Alvaro Herrera () "The Postgresql hackers have what I call a "NASA space shot" mentality. Quite refreshing in a world of "weekend drag racer" developers." (Scott Marlowe) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] commit_delay, siblings
Josh Berkus wrote: > Hackers: > > I've been trying to get a test result for 8.1 that shows that we can > eliminate > commit_delay and commit_siblings, as I believe that these settings no longer > have any real effect on performance. However, the checkpointing performance > issues have so far prevented me from getting a good test result for this. > > Just a warning, because I might bring it up after feature freeze. If we yank them ( and I agree) I think we have to do it before feature freeze. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
On Tue, 2005-06-28 at 10:40 +1000, Neil Conway wrote: > Jan Wieck wrote: > > The whole parser is a hack that attempts to parse the procedural parts > > of the function but preserving the SQL parts as query strings while > > substituting variables with numbered parameters. That is anything but > > clean. It was the only way I saw at the time of implementation to build > > a parser that automatically supports future changes of the main Postgres > > query language. > > I agree the current parser is a hack, but it's difficult to see how else > it could be implemented. One possibility I've mentioned in the past is Could the reverse be done? Combine the PL/PgSQL and SQL grammar for the main parser (thus allowing procedural logic in standard SQL locations) and perhaps for the other PLs they can hook into a specific statement grammar which is a subset of the PL/PgSQL grammar by prefixing a keyword -- say EXECUTE to their strings. I would like to have some logic in psql, much as you can build simple loops and logic with shell on the command line on the fly. -- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] language handlers in public schema
This patch implements putting language handlers for the optional PLs
into pg_catalog rather than public, and supports dumping languages whose
handlers are found there. This will make it easier to drop the public
schema if desired.
Unlike the previous patch, the comments have been updated and I have
reformatted some code to meet Alvarro's request to stick to 80 cols. (I
actually aghree with this - it makes printing the code much nicer).
I think I did the right thing w.r.t versions earlier than 7.3, but I
have no real way of checking, so that should be checked by someone with
more/older knowledge than me ;-)
cheers
andrew
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.410
diff -c -r1.410 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 21 Jun 2005 20:45:44 - 1.410
--- src/bin/pg_dump/pg_dump.c 28 Jun 2005 00:22:34 -
***
*** 2146,2151
--- 2146,2152
int i_proargtypes;
int i_prorettype;
int i_proacl;
+ int i_is_pl_handler;
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
***
*** 2154,2168
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(query,
"SELECT tableoid, oid,
proname, prolang, "
"pronargs, proargtypes,
prorettype, proacl, "
"pronamespace, "
! "(select usename from pg_user
where proowner = usesysid) as usename "
"FROM pg_proc "
"WHERE NOT proisagg "
! "AND pronamespace != "
! "(select oid from pg_namespace where nspname =
'pg_catalog')");
}
else if (g_fout->remoteVersion >= 70100)
{
--- 2155,2190
if (g_fout->remoteVersion >= 70300)
{
+ /*
+* We now collect info on pg_catalog resident functions, but
+* only if they are language call handlers or validators, and
+* only for non-default languages (i.e. not internal/C/SQL).
+*/
appendPQExpBuffer(query,
"SELECT tableoid, oid,
proname, prolang, "
"pronargs, proargtypes,
prorettype, proacl, "
"pronamespace, "
! "(select usename from pg_user
"
! " where proowner = usesysid)
as usename, "
! "CASE WHEN oid IN "
! " (select lanplcallfoid from
pg_language "
! " where lanplcallfoid != 0)
THEN true "
! " WHEN oid IN "
! " (select lanvalidator from
pg_language "
! " where lanplcallfoid != 0)
THEN true "
! " ELSE false END AS
is_pl_handler "
"FROM pg_proc "
"WHERE NOT proisagg "
! "AND (pronamespace != "
! "(select oid from
pg_namespace "
! " where nspname =
'pg_catalog')"
! " OR oid IN "
! "(select lanplcallfoid
from pg_language "
! " where lanplcallfoid !=
0) "
! " OR oid IN "
! "(select lanvalidator
from pg_language "
! " where lanplcallfoid !=
0))"
! );
}
else if (g_fout->remoteVersion >= 70100)
{
***
*** 2171,2177
"pronargs, proargtypes,
prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user
where proowner = usesysid) as usename "
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
Jan Wieck wrote: The whole parser is a hack that attempts to parse the procedural parts of the function but preserving the SQL parts as query strings while substituting variables with numbered parameters. That is anything but clean. It was the only way I saw at the time of implementation to build a parser that automatically supports future changes of the main Postgres query language. I agree the current parser is a hack, but it's difficult to see how else it could be implemented. One possibility I've mentioned in the past is to rewrite the main SQL parser by hand (e.g. as a recursive descent parser), so that we could directly call into the main SQL parser from the PL/PgSQL parser. I believe that would let us embed SQL in PL/PgSQL without needing to teach the PL/PgSQL anything about the main SQL grammar. But of course this has the downside of needing to write and maintain a recursive descent parser. Any better ideas? -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
Jonah H. Harris wrote: I don't recommend discussion for this in this thread, but it could also tie in with the packages support we've discussed and (although some may argue this), compiling the PL to bytecode and using that. How would compilation to bytecode help? -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] For review: dbsize patch
Dave Page wrote: > The attached patch is an update of the dbsize integration patch > discussed last week. This version includes the following functions: > > pg_relation_size(text) - Get relation size by name/schema.name > pg_relation_size(oid)- Get relation size by OID > pg_tablespace_size(name) - Get tablespace size by name > pg_tablespace_size(oid) - Get tablespace size by OID > pg_database_size(name) - Get database size by name > pg_database_size(oid)- Get database size by OID > pg_size_pretty(int8) - Pretty print (and round) the byte size > specified (eg, 123456 = 121KB) > > The only remaining function that last week's brief discussion indicated > was required is a replacement for total_relation_size() (or > pg_table_size() as it might now be called). I didn't realise until a few > minutes ago that this function (which is actually broken because it > doesn't handle schemas) was only committed a couple of months ago (v1.5, > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsize/dbsize.s > ql.in) and has therefore never been in a release version. Uh, do any of these include the index size? TOAST size? > So should we include this new feature, and if so, how is it best added - > rewrite in C, or one long line in pg_proc? I would follow whatever we do in pg_proc now. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Moving sequences to another schema
I'm still playing around with the ALTER OBJECT SET SCHEMA stuff. I managed to alter indexes and constraints as well for tables, but with SERIAL sequences there is one little problem: When altering a sequence created by a SERIAL column type (i do this by examining pg_depend to avoid moving any other sequences that are 'foreign'), i need to recreate the default expression for the SERIAL column (stored in pg_attrdef.adbin). Is there an API to do that, or do i have to recreate the executable expression tree from scratch? Or am i missing something completely... TIA -- Bernd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tsearch2 vs core?
From: "Teodor Sigaev" <[EMAIL PROTECTED]> > > Now that we have both WAL logging and better concurrency for GiST > > indexes (great job btw, this will push at least one of my projects into > > using 8.1 the day it is released - or more likely, at RC stage), are > > there any plans to move tsearch2 from contrib to core? > > tsearch2 now doesn't support multibyte encoding and has problems with UTF :(. Japanese Mr. Junji TERAMOTO(NTT) is supporting it. http://www.oss.ecl.ntt.co.jp/tsearch2j/ However, It is EUC_JP. He will probably understand the problem. Though I don't understand the problem regards, Hiroshi Saito ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] For review: Server instrumentation patch
Dave Page wrote: As per Bruce's request, here's a copy of Andreas' server instrumentation patch for review. I've separated out the dbsize stuff and pg_terminate_backend is also not included. This version was generated against CVS today. As far as I can tell from review of comments made back to pre-8.0, all security and other concerns raised have been addressed. Regards, Dave. (Andreas, can you eyeball this to make sure I didn't miss anything or clobber anything I shouldn't have when I trimmed the dbsize stuff please) Seems fine. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Wierd panic with 7.4.7
Hello, Any thoughts on the below? Specifically the PANIC? A customer was performing a full vacuum when it happen. This is running 7.4.7 on ES 3.0. We run daily vacuums and analyzes as well. 2005-06-27 16:35:02 LOG: recycled transaction log file "004D006F" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0070" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0071" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0072" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0073" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0074" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0075" 2005-06-27 16:35:02 LOG: recycled transaction log file "004D0076" 2005-06-27 16:36:23 LOG: incomplete startup packet 2005-06-27 16:37:53 ERROR: could not send data to client: Broken pipe 2005-06-27 16:37:53 PANIC: cannot abort transaction 146017848, it was already committed 2005-06-27 16:37:53 LOG: server process (PID 6931) was terminated by signal 6 2005-06-27 16:37:53 LOG: terminating any other active server processes 2005-06-27 16:37:53 WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. 2005-06-27 16:37:53 WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] GCC pointer signedness (and other) warnings
I've noticed a lot of signedness warnings when compiling Postgres with GCC 4. They may have been there with GCC 3.3 as well, but I don't recall. Here's a example: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wold-style-definition -Wendif-labels - fno-strict-aliasing -g -I../../../src/include -I/usr/include/ -c -o pg_proc.o pg_proc.c pg_proc.c: In function 'match_prosrc_to_query': pg_proc.c:724: warning: pointer targets in passing argument 1 of 'pg_mbstrlen_with_len' differ in signedness pg_proc.c:738: warning: pointer targets in passing argument 1 of 'pg_mbstrlen_with_len' differ in signedness Here's another: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wold-style-definition -Wendif-labels - fno-strict-aliasing -g -I../../../../../../src/include -I/usr/ include/ -c -o utf8_and_ascii.o utf8_and_ascii.c utf8_and_ascii.c: In function 'ascii_to_utf8': utf8_and_ascii.c:38: warning: pointer targets in initialization differ in signedness utf8_and_ascii.c:39: warning: pointer targets in initialization differ in signedness I've also noticed some possible uninitialized variable warnings, such as this: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wold-style-definition -Wendif-labels - fno-strict-aliasing -g -I../../../../src/interfaces/ecpg/include - I../../../../src/include/utils -I../../../../src/interfaces/libpq - I../../../../src/include -I/usr/include/ -DFRONTEND -c -o datetime.o datetime.c datetime.c: In function 'PGTYPESdate_defmt_asc': datetime.c:335: warning: 'tm$tm_mday' may be used uninitialized in this function datetime.c:335: warning: 'tm$tm_mon' may be used uninitialized in this function datetime.c:335: warning: 'tm$tm_year' may be used uninitialized in this function Are these things we could clean up? (Yes, I'm volunteering if it's something I'm capable of helping out with, though probably not for 8.1.) Michael Glaesemann grzm myrealbox com laughter:~ glaesema$ gcc -v Reading specs from /usr/lib/gcc/powerpc-apple-darwin8/4.0.0/specs Configured with: /private/var/tmp/gcc/gcc-4061.obj~8/src/configure -- disable-checking --prefix=/usr --mandir=/share/man --enable- languages=c,objc,c++,obj-c++ --program-transform-name=/^[cg][^+.-]*$/ s/$/-4.0/ --with-gxx-include-dir=/include/gcc/darwin/4.0/c++ -- build=powerpc-apple-darwin8 --host=powerpc-apple-darwin8 -- target=powerpc-apple-darwin8 Thread model: posix gcc version 4.0.0 20041026 (Apple Computer, Inc. build 4061) ---(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] Problem with dblink regression test
On Mon, Jun 27, 2005 at 03:44:41PM -0400, Andrew Dunstan wrote: > > > Jim C. Nasby wrote: > > >On Mon, Jun 27, 2005 at 02:10:47PM -0500, Jim C. Nasby wrote: > > > > > >>http://stats.distributed.net/~buildfarm/libcheck.log. Note that Tom's > >>theory is correct: psql is linking against the buildfarm libpq while > >>dblink is linking against the system one. > >> > >> > > > >BTW, after looking through that logfile, it appears that all the > >libraries are linking against the system libraries instead of the > >buildfarm/*/inst libraries, so this isn't specifically a dblink or even > >contrib issue. > > > > > > I only saw problems with libecpg.so, libecpg_compat.so and dblink.so AFAICT those are the only libraries that link to libpq or any other postgresql library, so yes, they'd be the only ones with a problem. :) BTW, anyone have any idea why my other two emails haven't gone out to the list yet? Is it because of their size? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
I agree with Jan, As part of my own projects I had to deal with the PL/pgSQL parser. While it was a workable design at the beginning, it now makes some things harder with the quoting etc. Don't get me wrong, I've never really had any beef with PL/pgSQL, it has worked great for a long time but I think it could definitely use a rewrite. I don't recommend discussion for this in this thread, but it could also tie in with the packages support we've discussed and (although some may argue this), compiling the PL to bytecode and using that. -Jonah Jan Wieck wrote: On 6/26/2005 4:10 PM, Pavel Stehule wrote: On Sun, 26 Jun 2005, Tom Lane wrote: "Denis Lussier" <[EMAIL PROTECTED]> writes: > For various technical and backward compatibility reasons, I don't think > SQL/PSM should be a replacement for PL/pgSQL. Although I do think it > should heavily leverage the solid foundation afforded by the PL/pgSQL > code base. "Solid"? I've wanted for quite some time to throw away plpgsql and start over --- there are too many things that need rewritten in it, starting with the parser. This project would be a great place to do that. What is wrong on plpgsql code? I see some problems with processing SQL statements, with efectivity evaluation of expr, but parser is clean (in my opinion). The whole parser is a hack that attempts to parse the procedural parts of the function but preserving the SQL parts as query strings while substituting variables with numbered parameters. That is anything but clean. It was the only way I saw at the time of implementation to build a parser that automatically supports future changes of the main Postgres query language. But that doesn't mean that I like the implementation. Jan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Problem with dblink regression test
Jim C. Nasby wrote: On Mon, Jun 27, 2005 at 02:10:47PM -0500, Jim C. Nasby wrote: http://stats.distributed.net/~buildfarm/libcheck.log. Note that Tom's theory is correct: psql is linking against the buildfarm libpq while dblink is linking against the system one. BTW, after looking through that logfile, it appears that all the libraries are linking against the system libraries instead of the buildfarm/*/inst libraries, so this isn't specifically a dblink or even contrib issue. I only saw problems with libecpg.so, libecpg_compat.so and dblink.so cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with dblink regression test
On Mon, Jun 27, 2005 at 02:10:47PM -0500, Jim C. Nasby wrote: > http://stats.distributed.net/~buildfarm/libcheck.log. Note that Tom's > theory is correct: psql is linking against the buildfarm libpq while > dblink is linking against the system one. BTW, after looking through that logfile, it appears that all the libraries are linking against the system libraries instead of the buildfarm/*/inst libraries, so this isn't specifically a dblink or even contrib issue. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Implementing SQL/PSM for PG 8.2
On 6/26/2005 4:10 PM, Pavel Stehule wrote: On Sun, 26 Jun 2005, Tom Lane wrote: "Denis Lussier" <[EMAIL PROTECTED]> writes: > For various technical and backward compatibility reasons, I don't think > SQL/PSM should be a replacement for PL/pgSQL. Although I do think it > should heavily leverage the solid foundation afforded by the PL/pgSQL > code base. "Solid"? I've wanted for quite some time to throw away plpgsql and start over --- there are too many things that need rewritten in it, starting with the parser. This project would be a great place to do that. What is wrong on plpgsql code? I see some problems with processing SQL statements, with efectivity evaluation of expr, but parser is clean (in my opinion). The whole parser is a hack that attempts to parse the procedural parts of the function but preserving the SQL parts as query strings while substituting variables with numbered parameters. That is anything but clean. It was the only way I saw at the time of implementation to build a parser that automatically supports future changes of the main Postgres query language. But that doesn't mean that I like the implementation. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 vs core?
Magnus, we have pretty big TODO for tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/donate.shtml which we'd like to have implement once we get support. It's certainly not for 8.1. Oleg On Mon, 27 Jun 2005, Magnus Hagander wrote: Hi! Now that we have both WAL logging and better concurrency for GiST indexes (great job btw, this will push at least one of my projects into using 8.1 the day it is released - or more likely, at RC stage), are there any plans to move tsearch2 from contrib to core? I quite often hear from people who miss it out because it's in contrib and not in main pg. Probably mainly because there is nothing about it in our docs. (other than in the list of examples for GiST, which probably directs more people into thinking it's just an example and not a complete system). //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Cleaning up unreferenced table files
No, not for now. Maybe for 8.2. And maybe as a contrib tool at first after all. - Heikki On Mon, 27 Jun 2005, Bruce Momjian wrote: Heikki, do you have any interest in completing your file checking patch for inclusion in 8.1 by adding tablespace information and other fixes as requested by Tom below? The current patch version is at: ftp://candle.pha.pa.us/pub/postgresql/mypatches called checkfiles*. Anyone else want to complete it? --- Tom Lane wrote: Bruce Momjian writes: Applied. Now that I've had a chance to look at it, this patch is thoroughly broken. Problems observed in a quick review: 1. It doesn't work at all for non-default tablespaces: it will claim that every file in such a tablespace is stale. The fact that it does that rather than failing entirely is accidental. It tries to read the database's pg_class in the target tablespace whether it's there or not. Because the system is still in recovery mode, the low-level routines allow the access to the nonexistent pg_class table to pass --- in fact they think they should create the file, so after it runs there's a bogus empty "1259" file in each such tablespace (which of course it complains about, too). The code then proceeds to think that pg_class is empty so of course everything draws a warning. 2. It's not robust against stale subdirectories of a tablespace (ie, subdirs corresponding to a nonexistent database) --- again, it'll try to read a nonexistent pg_class. Then it'll produce a bunch of off-target complaint messages. 3. It's assuming that relfilenode is unique database-wide, when no such assumption is safe. We only have a guarantee that it's unique tablespace-wide. 4. It fails to examine table segment files (such as "nnn.1"). These should be complained of when the "nnn" doesn't match any hash entry. 5. It will load every relfilenode value in pg_class into the hashtable whether it's meaningful or not. There should be a check on relkind. 6. I don't think relying on strtol to decide if a filename is entirely numeric is very safe. Note all the extra defenses in pg_atoi against various platform-specific misbehaviors of strtol. Personally I'd use a strspn test instead. 7. There are no checks for readdir failure (compare any other readdir loop in the backend). See also Simon Riggs' complaints that the circumstances under which it's done are pretty randomly selected. (One particular thing that I think is a bad idea is to do this in a standalone backend. Any sort of corruption in any db's pg_class would render it impossible to start up.) To fix the first three problems, and also avoid the performance problem of multiply rescanning a database's pg_class for each of its tablespaces, I would suggest that the hashtable entries be widened to RelFileNode structs (ie, db oid, tablespace oid, relfilenode oid). Then there should be one iteration over pg_database to learn the OIDs and default tablespaces of each database; with that you can read pg_class from its correct location for each database and load all the entries into the hashtable. Then you iterate through the tablespaces looking for stuff not present in the hashtable. You might also want to build a list or hashtable of known database OIDs, so that you can recognize a stale subdirectory immediately and issue a direct complaint about it without even recursing into it. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - Heikki ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Sigh, another contrib/cube and contrib/seg problem
Andrew, > I'd consider replacing them with something clearer, perhaps @< and @> ? > (i.e. (a @< b) would mean "a is contained by b" and (a @> b) would mean > "a contains b") Ltree uses those operators in that way, I believe. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How two perform TPC-H test on postgresql-8.0.2
Inno, > Currently I want to take a TPC-H test on postgresql-8.0.2. I have > downloaded the DBGEN and QGEN from the homepage of TPC. But I encountered > many problems which forced me to request some help. 1. How to load the data > from flat file generated by dbgen tool? To the best of my knowledge, there > is a SQL Loader in Oracle 2. How to simulate the currency environment? > Where can I download a client which connects to DB server through ODBC? Get DBT3 from Sourceforge (search on "osdldbt"). This is OSDL's TPCH-like test. However, given your knowledge of PostgreSQL you're unlikely to get any kind of result you can use -- TPCH requires siginficant database tuning knowledge. How about you ask the questions you really want to know on PGSQL-PERFORMANCE mailing list? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tsearch2 vs core?
Magnus, > I quite often hear from people who miss it out because it's in contrib > and not in main pg. Probably mainly because there is nothing about it in > our docs. (other than in the list of examples for GiST, which probably > directs more people into thinking it's just an example and not a > complete system). Well, I think the answer to this is to fix the documentation. I proposed last month to general approval that contrib modules should have documentation in a special chapter in the docs. Now somebody needs to write it ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixing r-tree semantics
Bruno Wolff III <[EMAIL PROTECTED]> writes: > I seem to remember there being a problem if <, <=, > and >= operators > didn't exist and doing some operations (distinct or group by?) that > required sorting the data type. I am not sure that you are suggesting > that these operators be removed, No, I wasn't. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] For review: dbsize patch
Dave Page wrote: The only remaining function that last week's brief discussion indicated was required is a replacement for total_relation_size() (or pg_table_size() as it might now be called). I didn't realise until a few minutes ago that this function (which is actually broken because it doesn't handle schemas) was only committed a couple of months ago (v1.5, http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsize/dbsize.sql.in) > and has therefore never been in a release version. So should we include this new feature, and if so, how is it best added > - rewrite in C, or one long line in pg_proc? IIRC the initially submitted patch for this contained a function written in C. It was only afterwards converted to SQL because of a comment by someone else. I will have a look in the archives. What I would like to have is a function that returns the table size (+ toast) + indexes. If it would be called pg_table_size(), that would be ok. We should have one with oid and another with text. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch2 vs core?
> > Now that we have both WAL logging and better concurrency for GiST > > indexes (great job btw, this will push at least one of my projects > > into using 8.1 the day it is released - or more likely, at > RC stage), > > are there any plans to move tsearch2 from contrib to core? > > tsearch2 now doesn't support multibyte encoding and has > problems with UTF :(. Oops. Didn't know that. All my affected DBs are LATIN1 (or ASCII in one case). Any plans on fixing this? Don't want to rush you or anything considering you just made my day with this latest commit, but it'd be interesting to know if it's "on it's way" or for some reason "not going to happen"? //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 vs core?
Now that we have both WAL logging and better concurrency for GiST indexes (great job btw, this will push at least one of my projects into using 8.1 the day it is released - or more likely, at RC stage), are there any plans to move tsearch2 from contrib to core? tsearch2 now doesn't support multibyte encoding and has problems with UTF :(. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] For review: dbsize patch
The attached patch is an update of the dbsize integration patch discussed last week. This version includes the following functions: pg_relation_size(text) - Get relation size by name/schema.name pg_relation_size(oid)- Get relation size by OID pg_tablespace_size(name) - Get tablespace size by name pg_tablespace_size(oid) - Get tablespace size by OID pg_database_size(name) - Get database size by name pg_database_size(oid)- Get database size by OID pg_size_pretty(int8) - Pretty print (and round) the byte size specified (eg, 123456 = 121KB) The only remaining function that last week's brief discussion indicated was required is a replacement for total_relation_size() (or pg_table_size() as it might now be called). I didn't realise until a few minutes ago that this function (which is actually broken because it doesn't handle schemas) was only committed a couple of months ago (v1.5, http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsize/dbsize.s ql.in) and has therefore never been in a release version. So should we include this new feature, and if so, how is it best added - rewrite in C, or one long line in pg_proc? Regards, Dave dbsize.c Description: dbsize.c dbsize.patch Description: dbsize.patch ---(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] Fixing r-tree semantics
On Sun, Jun 26, 2005 at 09:52:03 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > Now that the module uses GIST instead of r-tree, there's no very strong > reason why it should provide these operators at all. I propose removing > all of << >> &< &> from contrib/cube, leaving only the four > n-dimensional indexing operators (&& ~= ~ @). > > Any objections? I seem to remember there being a problem if <, <=, > and >= operators didn't exist and doing some operations (distinct or group by?) that required sorting the data type. I am not sure that you are suggesting that these operators be removed, as you didn't list them in either the remove or keep list above. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] accessing postgres conf from stored procedure
On Mon, Jun 27, 2005 at 04:37:11PM +0200, strk wrote: > On Mon, Jun 27, 2005 at 08:55:50AM -0400, Dave Cramer wrote: > > you can use show xxx to show configuration values > > > > http://www.postgresql.org/docs/7.4/interactive/sql-show.html > > No direct interface for shared libs ? I don't know if it's fair game, but GetConfigOptionByName() (declared in utils/guc.h) appears to work. Can any of the developers comment on whether it's appropriate for user-defined code to call this function? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Questions on extending a relation
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > 1. When we want a new page, we will do something like this: > LockPage(relation, 0, ExclusiveLock); > blockNum = smgrnblocks(reln->rd_smgr); > /* Try to locate this blockNum in buffer pool, but definitely can't? */ > smgrextend(blockNum); > LockPage(relation, 0, ExclusiveLock); You should be using ReadBuffer with P_NEW, not calling smgr yourself. > So if I have concurrently 10 backends reach here, we will have 10 new pages? Yes. That's intentional --- otherwise they'd all block each other. > 2. Suppose an insert on a relation with index is performed in this sequence: > begin transation; > extend relation for a new page A; > insert a heap tuple T on page A; > insert an index tuple I on another page B; > page B get written out by bgwriter; > System crashed. > System recovered. > At this time, page A is empty since we won't replay xlog. Why wouldn't we replay xlog? Note in particular that the bgwriter is not allowed to push page B to disk until the xlog entry describing the index change has been flushed to disk. Since that will come after the xlog entry about the heap change, both changes are necessarily on-disk in the xlog, and both will be remade during replay. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] accessing postgres conf from stored procedure
On Mon, Jun 27, 2005 at 08:55:50AM -0400, Dave Cramer wrote: > you can use show xxx to show configuration values > > http://www.postgresql.org/docs/7.4/interactive/sql-show.html No direct interface for shared libs ? --strk; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GiST concurrency commited
Teodor Sigaev <[EMAIL PROTECTED]> writes:
> While I'm running test with concurrent
> select/insert/update/delete/vacuum/vacuum full I found, that sometimes
> postgres crashes in index_beginscan_internal on FunctionCall3, because
> structure 'procedure' becomes zeroed. As I understand, LockRelation
> can invalidate part of Relation structure. So, I moved
> GET_REL_PROCEDURE after LockRelation.
Oooh, good catch.
> It seems to me, this patch
> should be backpatched or it's needed another fixing.
No, it's not an issue in the back branches, because until recently
GET_REL_PROCEDURE only fetched the function OID.
> And there is one more problem: it caused approximatly one time per 2-4
> million
> statements, I got traps:
> TRAP: FailedAssertion("!((*curpage)->offsets_used == num_tuples)", File:
> "vacuum.c", Line: 2766)
> LOG: server process (PID 15847) was terminated by signal 6
Odd. Will look at it later (after feature freeze), if you don't find
the cause beforehand.
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] process crash when a plpython function returns unicode
On Sat, Jun 18, 2005 at 05:27:28PM +0200, Tino Wildenhain wrote: > Am Samstag, den 18.06.2005, 08:41 -0600 schrieb Michael Fuhr: > > > > I was going to submit a patch, but I don't know enough about the > > Python API or how Python and PostgreSQL handle Unicode to know > > whether adding that simple check is the appropriate solution (I was > > planning to raise an error if PyObject_Str() returned NULL). Can > > anybody think of a better fix? > > raise error would be a correct solution since this is what > python does in this case: I just submitted a patch that checks for NULL and raises an error via PLy_elog(). > also in this context it would be helpful > if sys.defaultencoding would be set to > the database encoding so strings get encoded > to utf-8 when postgres works in unicode mode > rather then the default encoding of ascii. > This could avoid most of the PyObject_Str() > exeptions in the first place. I haven't looked at doing that yet and probably won't before feature freeze. Gerrit van Dyk has expressed an interest in hacking on PL/Python (he recently submitted a SETOF patch) so maybe he'll work on it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] contrib/rtree_gist into core system?
"John Hansen" <[EMAIL PROTECTED]> writes: > No, but the _current_ implementation of the rtree operators are ver much > self explaining and need no howto. That reasoning no doubt explains why we don't have *any* rtree-like opclasses that got the left/overleft/right/overright semantics right the first time :-(. Greg Stark is right that the GIST API could probably be simpler --- in particular it would be interesting to see if we could offer a default picksplit function that most opclasses could use. But that doesn't mean that the rtree API is exactly trivial. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Questions on extending a relation
Hi Hackers, Here I have two questions related to extending a relation: 1. When we want a new page, we will do something like this: LockPage(relation, 0, ExclusiveLock); blockNum = smgrnblocks(reln->rd_smgr); /* Try to locate this blockNum in buffer pool, but definitely can't? */ smgrextend(blockNum); LockPage(relation, 0, ExclusiveLock); So if I have concurrently 10 backends reach here, we will have 10 new pages? Suppose they all insert one new tuple, commit, then quit. Next time when they connected again, they only reuse the last page, so we almost lost 9 pages? 2. Suppose an insert on a relation with index is performed in this sequence: begin transation; extend relation for a new page A; insert a heap tuple T on page A; insert an index tuple I on another page B; page B get written out by bgwriter; System crashed. System recovered. At this time, page A is empty since we won't replay xlog. Now we insert a heap tuple on page A again, which will use the same slot of the tuple T. So now the index tuple I points to T? Thanks a lot, Qingqing ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] tsearch2 vs core?
Hi! Now that we have both WAL logging and better concurrency for GiST indexes (great job btw, this will push at least one of my projects into using 8.1 the day it is released - or more likely, at RC stage), are there any plans to move tsearch2 from contrib to core? I quite often hear from people who miss it out because it's in contrib and not in main pg. Probably mainly because there is nothing about it in our docs. (other than in the list of examples for GiST, which probably directs more people into thinking it's just an example and not a complete system). //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] accessing postgres conf from stored procedure
you can use show xxx to show configuration values http://www.postgresql.org/docs/7.4/interactive/sql-show.html Dave On 27-Jun-05, at 6:56 AM, strk wrote: Is it possible to access postgres configuration from a C stored procedure ? I need to leverage memory usage and I'd use postgres configuration rather then a compile-time define. Is there such a configuration, if access is possible ? --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] GiST concurrency commited
Have we list named something like 'test focusing for 8.1'? If it exists then
GiST concurrency and recovery testing should be added to it. Especially,
recovery after crash. Of course, now Oleg and me going to begin a large test
program.
While I'm running test with concurrent select/insert/update/delete/vacuum/vacuum
full I found, that sometimes postgres crashes in index_beginscan_internal on
FunctionCall3, because structure 'procedure' becomes zeroed. As I understand,
LockRelation can invalidate part of Relation structure. So, I moved
GET_REL_PROCEDURE after LockRelation. It seems to me, this patch should be
backpatched or it's needed another fixing. This problem was 2-4 times per
million statements executing by 4 flows.
And there is one more problem: it caused approximatly one time per 2-4 million
statements, I got traps:
TRAP: FailedAssertion("!((*curpage)->offsets_used == num_tuples)", File:
"vacuum.c", Line: 2766)
LOG: server process (PID 15847) was terminated by signal 6
Sorry, but I couldn't debug this trap and my knowledge about this piece of code
is very limited. Postgres didn't create a core file. I don't believe this
problem is in touch with my GiST framework, becouse it is about heap pages. I
suspect trap occurs while concurrent vacuum, but I am not sure.
PS
My concurrency testing scripts:
http://www.sigaev.ru/gist/
concur.pl - generator of SQL statements
concur.sh - simple wrapper about concur.pl which reinit db, makes db and table.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(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] How two perform TPC-H test on postgresql-8.0.2
innodb wrote: > Currently I want to take a TPC-H test on postgresql-8.0.2. I have > downloaded the DBGEN and QGEN from the homepage of TPC. But I encountered > many problems which forced me to request some help. > 1. How to load the data from flat file generated by dbgen tool? To the > best of my knowledge, there is a SQL Loader in Oracle > 2. How to simulate the currency environment? Where can I download a > client which connects to DB server through ODBC? > > > > To be brutally frank, if you have to ask these questions you should not be running TPC-H benchmarks, IMNSHO. You results are very unlikely to be fair to you or to PostgreSQL. TPC-H requires mild modification (took me about 15 minutes) to produce postgres-ready output, which can be loaded via the COPY command, which is designed for bulk loading data. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib/rtree_gist into core system?
Tom Lane [mailto:[EMAIL PROTECTED] Wrote: > There's no HOWTO for rtree either. Again, my point is not > that one couldn't be written; it's that we would probably be > better off spending the effort on a HOWTO for gist. No, but the _current_ implementation of the rtree operators are ver much self explaining and need no howto. Union(x,y) = x + y Intersect(x,y) = the values that are present in both x and y, or _overlapping_region_ Size(x) = the size of the area/length of the line, number of elements, etc... Now, how simple is that compared to gist? I for one, is yet to produce a working example of something as simple as indexing an array of 2 elements [x y] represented by a custom type as '[x y]' in string format (returned by type_out) internally stored as a char[2], so that I can fetch all rows where [x y] = ':y' (:y meaning 2nd element in array, x: meaning first element in array. I chose this as something simple to play with, having no practical application for me, but to get an understanding of gist, For now,. I have put it in the too hard basket. I did however in about half a day implement rtree support for inet/cidr (ipv4 only) as you might recall. Kind Regards, John ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] accessing postgres conf from stored procedure
Is it possible to access postgres configuration from a C stored procedure ? I need to leverage memory usage and I'd use postgres configuration rather then a compile-time define. Is there such a configuration, if access is possible ? --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Cleaning up unreferenced table files
Heikki, do you have any interest in completing your file checking patch for inclusion in 8.1 by adding tablespace information and other fixes as requested by Tom below? The current patch version is at: ftp://candle.pha.pa.us/pub/postgresql/mypatches called checkfiles*. Anyone else want to complete it? --- Tom Lane wrote: > Bruce Momjian writes: > > Applied. > > Now that I've had a chance to look at it, this patch is thoroughly > broken. Problems observed in a quick review: > > 1. It doesn't work at all for non-default tablespaces: it will > claim that every file in such a tablespace is stale. The fact > that it does that rather than failing entirely is accidental. > It tries to read the database's pg_class in the target tablespace > whether it's there or not. Because the system is still in recovery > mode, the low-level routines allow the access to the nonexistent > pg_class table to pass --- in fact they think they should create > the file, so after it runs there's a bogus empty "1259" file in each > such tablespace (which of course it complains about, too). The code > then proceeds to think that pg_class is empty so of course everything > draws a warning. > > 2. It's not robust against stale subdirectories of a tablespace > (ie, subdirs corresponding to a nonexistent database) --- again, > it'll try to read a nonexistent pg_class. Then it'll produce a > bunch of off-target complaint messages. > > 3. It's assuming that relfilenode is unique database-wide, when no > such assumption is safe. We only have a guarantee that it's unique > tablespace-wide. > > 4. It fails to examine table segment files (such as "nnn.1"). These > should be complained of when the "nnn" doesn't match any hash entry. > > 5. It will load every relfilenode value in pg_class into the hashtable > whether it's meaningful or not. There should be a check on relkind. > > 6. I don't think relying on strtol to decide if a filename is entirely > numeric is very safe. Note all the extra defenses in pg_atoi against > various platform-specific misbehaviors of strtol. Personally I'd use a > strspn test instead. > > 7. There are no checks for readdir failure (compare any other readdir > loop in the backend). > > See also Simon Riggs' complaints that the circumstances under which it's > done are pretty randomly selected. (One particular thing that I think > is a bad idea is to do this in a standalone backend. Any sort of > corruption in any db's pg_class would render it impossible to start up.) > > To fix the first three problems, and also avoid the performance problem > of multiply rescanning a database's pg_class for each of its > tablespaces, I would suggest that the hashtable entries be widened to > RelFileNode structs (ie, db oid, tablespace oid, relfilenode oid). Then > there should be one iteration over pg_database to learn the OIDs and > default tablespaces of each database; with that you can read pg_class > from its correct location for each database and load all the entries > into the hashtable. Then you iterate through the tablespaces looking > for stuff not present in the hashtable. You might also want to build a > list or hashtable of known database OIDs, so that you can recognize a > stale subdirectory immediately and issue a direct complaint about it > without even recursing into it. > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Constraint Exclusion (Partitioning)
Recently submitted to -patches. Copied here for further discussion. On Mon, 2005-06-27 at 01:41 +0100, Simon Riggs wrote: > I enclose a fully working implementation of Constraint Exclusion, a very > basic form of Partitioning. Initial review is requested, to allow us all > to assess what further work is required on this prior to Beta freeze. > > Patch against current cvstip; passes make check and all special tests. > > The main purpose of this feature is to reduce access time against large > tables that have been split into partitions by using the PostgreSQL > inheritance facility. It has been written in a very generic way allowing > a whole range of applications. > > If > a) a table is part of an inheritance set > b) the table has check constraints defined upon it > c) enable_constraint_exclusion = true > > then the planner will attempt to use the definition of the Constraints > to see if that relation could ever have rows in it that the query might > see. *No* additional SQL DDL syntax is required to define this. > > Only query clauses of the form ATTR OP CONSTANT will be considered, in a > very similar way to the way partial indexes work already. > > The code changes effect only the planner, building upon the partial > index logic to allow refutation as well as implication. > > There are clearly many questions to be answered by me and I'm happy to > do so, so please fire away. My hope is to get a more polished form of > this functionality into 8.1. Further developments on Partitioning are > foreseen, though the feature submitted today is the main building block > for any further work/optimization in this area and so additional > features will be discussed at a later time. > > A full test suite has been specially written for this feature. This is > included here also, though no attempt has been made as yet to integrate > that with the main regression test suite (as yet). Required files are > included in a single tar file with this email. Extract these to the > PostgreSQL installation directory and run using ./testprange.sh > The test suite executes around 100 queries against 7 different database > designs, comparing results with/without the new enable option. Full and > pruned EXPLAINs are also derived during execution to allow easier > analysis of the success of the exclusion process (view the > testprange_t*e.out files). > > There are no cases where any of the test queries returns a logically > incorrect answer; hence fully working. There are a few cases where > queries have not been optimised as far as possible; in those cases > checks on my propositional logic are requested... This is extremely > complex and my expectation is that testers/reviewers will find at least > of couple of logic improvements. The most frequent queries are believed > to work optimally. > > Main questions: > 1. How should we handle the case where *all* inherited relations are > excluded? (This is not currently covered in the code). > 2. Should this feature be available for all queries or just inherited > relations? > 3. And should we extend RelOptInfo to include constraint information? > 4. Do we want to integrate the test suite also? > 5. Presumably a section under Performance tips would be appropriate to > document this feature? (As well as section in run-time parameters). > > Additional thoughts: > 1. We should be able to optimise the case where there is only a single > non-excluded relation by removing the Append node. > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] ARRAY() returning NULL instead of ARRAY[]
OK, what is the TODO item text? --- Joe Conway wrote: > Bruce Momjian wrote: > > Is this a TODO item? > > > > Probably. I posted some questions regarding whether or not to break > backward compatiblity, and received no replies. In the meanwhile, I've > been doing a major system integration in Korea for the last 2 weeks, and > won't get back to home, or to anything like a reasonably normal schedule > until after July 2. I doubt I'll have time to do much between now and > feature freeze. > > Joe > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] contrib/rtree_gist into core system?
FYI, compress and decompress methods may be trivial. For GiST you still need 7 support functions + the operator function, some of which aren't exactly simple to implement, the picksplit for instance. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How two perform TPC-H test on postgresql-8.0.2
innodb wrote: Currently I want to take a TPC-H test on postgresql-8.0.2. You might want to take a look at the TPC-H implementation here: http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-3/ -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] contrib/rtree_gist into core system?
I believe all the picksplit functions are based on (apparently via copy/paste) a single algorithm that depends on a single operator: a kind of "distance" function. Usually it's the same function underlying the penalty gist api You are wrong, at least now in contrib it used three basic picksplit algoritm 1 simple sorting for ordered domain( btree_gist, ltree ) 2 several variations of Guttmans algorithm (tsearch2, intarray, seg, cube) 3 linear picksplit for rtree_gist (http://www.sai.msu.su/~megera/postgres/gist/papers/nsplitLN.ps.gz). -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/rtree_gist into core system?
I think we still have a serious problem with multicolumn indexes. As they stand they're basically only indexes on the first column. The later columns are not used to determine page splits. It's not a fully truth, second keys can be used in split, if first columns has non-unique values and second, the later columns uses in gistchoose method (wrap for user-defined penalty methods). But I am agreed, that split in multicolumn GiST indexes isn't very optimal, the solution was suggested by Aoki, but it's require to change interface to user function. Look: "Generalizing ''Search'' in Generalized Search Trees", 1997, Paul M. Aoki, http://www.sai.msu.su/~megera/postgres/gist/papers/csd-97-950.pdf -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] How two perform TPC-H test on postgresql-8.0.2
Currently I want to take a TPC-H test on postgresql-8.0.2. I have downloaded the DBGEN and QGEN from the homepage of TPC. But I encountered many problems which forced me to request some help. 1. How to load the data from flat file generated by dbgen tool? To the best of my knowledge, there is a SQL Loader in Oracle 2. How to simulate the currency environment? Where can I download a client which connects to DB server through ODBC? Your sincerely! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] contrib/rtree_gist into core system?
We can make r-tree as contrib module and then we will have example of index in contrib... By integrating the opclasses needed to replace R-tree, we can start down the path to deprecating and eventually removing R-tree. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] Implementing SQL/PSM for PG 8.2
Title: Re: [HACKERS] Implementing SQL/PSM for PG 8.2 Hi Affan, Please read this SQL/PSM thread over and then address how EDB did it (and of course how you would recommend generalizing it for PG 8.2). Perhaps our SQL/PSM could be designed from the ground up with "debugability" :-) in mind. --Luss From: Alvaro Herrera [mailto:[EMAIL PROTECTED]Sent: Sun 6/26/2005 6:06 PMTo: Andrew DunstanCc: Denis Lussier; pgsql-hackers@postgresql.orgSubject: Re: [HACKERS] Implementing SQL/PSM for PG 8.2 On Sun, Jun 26, 2005 at 06:06 -05, Alvaro Herrera wrote: > I've seen some example code on the EnterpriseDB website using their SPL > language, and it doesn't seem to be handled like "just another PL". The > function body does not look at all like quoted strings, as in our > regular PLs. I don't know how they did it, but I don't think they added > support for the whole language to the main parser.
Re: [HACKERS] contrib/rtree_gist into core system?
On 2005-06-27, Greg Stark <[EMAIL PROTECTED]> wrote: > I believe all the picksplit functions are based on (apparently via > copy/paste) a single algorithm that depends on a single operator: a kind > of "distance" function. Usually it's the same function underlying the > penalty gist api function. That's not quite true. There are at least two quite different picksplit algorithms in those of the contrib/* modules that I've studied, and in general I do not think it is possible to provide a single generic picksplit that will work efficiently for _all_ data types. (And it is of course important not to constrain the types of data that are allowed...) It might be reasonable to implement a "default" picksplit based on a user-supplied metric function (_not_ the same metric as "penalty"). But I think there always needs to be scope for the user to provide their own split function. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Sigh, another contrib/cube and contrib/seg problem
On 2005-06-27, Tom Lane <[EMAIL PROTECTED]> wrote: > I just noticed that these two modules define operator @ as "contains" > and operator ~ as "contained by", which is opposite to the meanings used > by every other datatype. These operators are fundamentally confusing because they give no visual indication as to which operand is the "larger" one. I'd consider replacing them with something clearer, perhaps @< and @> ? (i.e. (a @< b) would mean "a is contained by b" and (a @> b) would mean "a contains b") -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
