Re: [HACKERS] not null partial index?
Tatsuo Ishii [EMAIL PROTECTED] writes: It seems partial indexes with not null condition do not work: What you created wasn't a partial index, it was a functional index. Try something like create index nonnullindex on accounts(bid) where bid is not null; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Charset/collate support and function parameters
For me that seems to be the right way. I'm not sure if two oids are the right solution but we need to store extra info in varlena structure to support charset/collation anyway. In my understanding TOAST has already done in similar way. Other than charset/collation we also need coercibility info to meet with the SQL standard. This could only be represented in each text, not by function parameters. Arn't we limiting ourself in how we can use charsets when we remove it from the type. ? That is a news to me. In my understanding nobody did remove charsets from the type. The charset info has not been in text type since PostgreSQL was born. The reason why I started to look at the function parameters is because in the standard one can do this: CREATE CHARACTER SET foo GET Latin1; Surely we want add this to PostgreSQL. and now we could add functions that work with this charset CREATE FUNCTION bar (x VARCHAR(255) CHARACTER SET foo) What we are saying is that we don't want to be able to do this? Not sure we want to add above. Is it something defined in the standard? Though I think we want to add something like: SELECT bar(x CHARACTER SET foo COLLATE buz) I just want to understand all the implications of simplifying the types. Same thing if the user wants to create new collations using CREATE COLLATION. How can we override functions for these new charsets and collations if all we can define are functions like foo(x VARCHAR)? Maybe one wants the information in both place. I think we could make our text type richer so that it could include charset, collate info and so on. At this point my plan to implement CREATE CHARSET/COLLATE includes: 1) add charset/collation info to pg_attribute 2) make shared tables such as pg_database and pg_shadow can handle multiple charsets. this is necessary because database names and user names could be repsented in different charsets 3) create new system catalogs to keep defined charsets and collations 4) make text type on memory representation richer so that it can keep chaset/collate/coercibility etc. info 5) enhance pg_am and friends 6) create our own locale database -- Tatsuo Ishii ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] not null partial index?
Tatsuo Ishii [EMAIL PROTECTED] writes: It seems partial indexes with not null condition do not work: What you created wasn't a partial index, it was a functional index. Try something like create index nonnullindex on accounts(bid) where bid is not null; Sorry for the confusing and foolish question. However still I wonder why my expression(functional) index does not work. -- Tatsuo Ishii ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: and now we could add functions that work with this charset CREATE FUNCTION bar (x VARCHAR(255) CHARACTER SET foo) What we are saying is that we don't want to be able to do this? Not sure we want to add above. Is it something defined in the standard? The syntax in sql2003 do allow it. The exact semantics is difficult to get a clear picture of from the spec (as always). I thought this question was the whole argument. We can't have something like the above with the pg overloading since then the resolving process will be too hard according to Tom. 2) make shared tables such as pg_database and pg_shadow can handle multiple charsets. this is necessary because database names and user names could be repsented in different charsets Shouldn't we just define the charset for user names and database names? Either one fixed or one that's set during initdb. You don't mean that we want different user numes to be defined using different charsets? The rest of the points looks good to me. The main problem is still what to do with the function definitions as above. Is it something we want or not? Is the charset something that makes two text types different or not? -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] not null partial index?
Tatsuo Ishii [EMAIL PROTECTED] writes: Sorry for the confusing and foolish question. However still I wonder why my expression(functional) index does not work. You could likely have gotten it to match to a query like SELECT ... WHERE (bid is not null) = true; which would have the proper form of (indexed value) = constant. Whether the planner would have picked an indexscan for that without coercion is another issue. IIRC 7.4 does not keep statistics for functional indexes and so it is unlikely to get the rowcount estimates right for a query expressed this way. (Looking back at your example, you don't seem to have run an ANALYZE anyway :-() A partial index is likely to work better for this problem on both counts: you can write just WHERE bid is not null, and the normal stats will (I think) be able to estimate that well. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: and now we could add functions that work with this charset CREATE FUNCTION bar (x VARCHAR(255) CHARACTER SET foo) What we are saying is that we don't want to be able to do this? Not sure we want to add above. Is it something defined in the standard? The syntax in sql2003 do allow it. The exact semantics is difficult to get a clear picture of from the spec (as always). I wonder what is the intention to allow such that syntax. It seems it's just useless since we could make a function bar() which accepts any charsets. I thought this question was the whole argument. We can't have something like the above with the pg overloading since then the resolving process will be too hard according to Tom. 2) make shared tables such as pg_database and pg_shadow can handle multiple charsets. this is necessary because database names and user names could be repsented in different charsets Shouldn't we just define the charset for user names and database names? Either one fixed or one that's set during initdb. You don't mean that we want different user numes to be defined using different charsets? What I have in my mind was: CREATE DATABASE foo CHARSET bar; If we allow this, we need 2). The rest of the points looks good to me. The main problem is still what to do with the function definitions as above. Is it something we want or not? Is the charset something that makes two text types different or not? I understand your point. If we are going to allow that, probably we have to regard each text type distinct which has different charset or collation each other. This will lead to number-of-types-explosion-problem which I really want to avoid... -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: I wonder what is the intention to allow such that syntax. It seems it's just useless since we could make a function bar() which accepts any charsets. One could override the behaviour of functions by adding a charset and a adding new definition of an old function name for that charset. Like adding a new collation and define a new cmp() function for that collation that works different then some old definitons of cmp(). The whole discussion came because I start to look at problems from what is in the specification and try to fit that into pg. Not everything will fit, it's just my starting point when discussing. Tom starts at the other end and then it looks like a big controversy. About the explosion of the number of functions needed. It's not obvious to me that there will be an explosion if one manage to allow both full types that include charset and more generic functions that work on any text type. It seems to me that there are not that many interesting combinations anyway. Most applications will use one charset and define functions that work with just that charset. Anyway, the only way to see what problems would arise is to try. I was hoping that the step A and B in the plan was something that we wanted no matter of how the locale problem was later solved. With those in place it would be easier to experiment. -- /Dennis Björklund ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] -HEAD regression failure on OpenBSD 3.6-current/x86
One of my boxes(emu) on the buildfarm fails to pass the float8 regressiontest: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=emudt=2004-10-31%2003:35:02 the interesting thing is that spoonbill (slightly older OpenBSD-current/Sparc64) passes this test(but fails contribcheck later on): http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=spoonbilldt=2004-10-30%2023:50:04 A wild guess is that the difference might be that OpenBSD/x86 is still using a 2.95.x compiler in the base system and Sparc64 already has 3.3.x... Yet it looks like that the float8 issue is not really fatal - (-0 vs 0) just annoying :-). Stefan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
On Sat, 30 Oct 2004 16:45:22 -0400, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Wed, Oct 27, 2004 at 09:29:21PM -0400, Tom Lane wrote: Wouldn't it be better to just stay in TBLOCK_STARTED state, as if the COMMIT were just some random utility command? It's the same thing, because CommitTransactionCommand acts identically either way. I changed it anyway because it seems simpler. Patch applied. Many thanks for this. I appreciate it's a fairly trivial issue, but seeing the word ROLLBACK when a commit, or at least a non-operation were expected, can do nasty things to one's blood pressure. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Charset/collate support and function parameters
I wonder what is the intention to allow such that syntax. It seems it's just useless since we could make a function bar() which accepts any charsets. One could override the behaviour of functions by adding a charset and a adding new definition of an old function name for that charset. Like adding a new collation and define a new cmp() function for that collation that works different then some old definitons of cmp(). How could that be usefull? For example, length() returns character length no matter what the charset/collation is. I hardly imagin a function which changes its behavior according to charsets. The whole discussion came because I start to look at problems from what is in the specification and try to fit that into pg. Not everything will fit, it's just my starting point when discussing. Tom starts at the other end and then it looks like a big controversy. About the explosion of the number of functions needed. It's not obvious to me that there will be an explosion if one manage to allow both full types that include charset and more generic functions that work on any text type. I don't understand your point. Today we already use one length() function for any charsets as Tom has already pointed out. It seems to me that there are not that many interesting combinations anyway. Most applications will use one charset and define functions that work with just that charset. Really? One of the objectives of i18n is an application can handle multiple charsets. I don't want to write two applications just for the charset difference, for example English and Japanese. Anyway, the only way to see what problems would arise is to try. I was hoping that the step A and B in the plan was something that we wanted no matter of how the locale problem was later solved. With those in place it would be easier to experiment. The question in your approach is how you could handle the coercibility property. It's a transient and on memory property thus will not fit into the function declaration. No? -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote:
I don't understand your point. Today we already use one length()
function for any charsets as Tom has already pointed out.
We have one length function that inside do different things depending on
the charset. If you want to add a charset and implement the length
function for that charset, how do you do that?
The length of a utf-8 string is not calculated the same way as the length
of a latin1 string. Each charset (encoding) have its own way of
calculating the length.
And by the way, today our databases just work with one charset at all and
what length do is decided by a global variable. The difference we talk
about here is the one between
length(latin1) ...
length(utf-8) ...
length(ascii) ...
and
length(x)
{
if charset(x) == latin1 then
,,,
else if charset(x) = utf-8 then
,,,
}
The question in your approach is how you could handle the coercibility
property. It's a transient and on memory property thus will not fit
into the function declaration. No?
No, it's not part of the function signature. Coercibility is a way to
decide what collation to use. Depending on where the value comes from it
can have different coercibility and when one do operations that involves
different collations the coercibility decide how ambiguities are resolved
(which value will be coerced).
If one would want function signatures with charsets in them and where the
charset information is stored, it doesn't have to be opposit of each
other.
I've currently been thinking that one can avoid storing the charset in the
value by handling types like that. I even though that there was no way
that anyone in the pg project would ever accept to enlarge the string
values, obviously a wrong assumption :-)
Even when one do store the charset in the value one might want to have
function overloading to depend on the charset of the string (when
specified).
That's the same opinion that if I declare a function
foo (x varchar(5))
begin
...
end
then I expect to get strings that are max 5 chars long. Why do we allow
the (5) if it's just droped? If I define a column as varchar(5) then the
column values are relly max 5 chars long, but it does not work for
functions like that.
Let us simply agree that we do store the charset/collation/... in the
(memory) values. On disk we don't want that since the column type do
decide it totally, do we agree on that?
--
/Dennis Björklund
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: I don't understand your point. Today we already use one length() function for any charsets as Tom has already pointed out. We have one length function that inside do different things depending on the charset. If you want to add a charset and implement the length function for that charset, how do you do that? That's exactly the job of CREATE CHARSET. It will define set of functions that handle various work including counting length of a string. One can find the char-length-counting function by looking up the charset system catalog. The question in your approach is how you could handle the coercibility property. It's a transient and on memory property thus will not fit into the function declaration. No? No, it's not part of the function signature. Coercibility is a way to decide what collation to use. Depending on where the value comes from it can have different coercibility and when one do operations that involves different collations the coercibility decide how ambiguities are resolved (which value will be coerced). I see. If one would want function signatures with charsets in them and where the charset information is stored, it doesn't have to be opposit of each other. I've currently been thinking that one can avoid storing the charset in the value by handling types like that. I even though that there was no way that anyone in the pg project would ever accept to enlarge the string values, obviously a wrong assumption :-) Even when one do store the charset in the value one might want to have function overloading to depend on the charset of the string (when specified). That's the same opinion that if I declare a function foo (x varchar(5)) begin ... end then I expect to get strings that are max 5 chars long. Why do we allow the (5) if it's just droped? If I define a column as varchar(5) then the column values are relly max 5 chars long, but it does not work for functions like that. Let us simply agree that we do store the charset/collation/... in the (memory) values. On disk we don't want that since the column type do decide it totally, do we agree on that? I agree except that shared system catalogs (and probably some non shared system catalogs such as pg_class) need charset on disk. I personaly don't see any value in using non English user names, database names, table names and so on though. However some users love to use them:-) -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] make check error on -HEAD
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I'm getting this error on two different servers, one if them is the server that I build PostgreSQL from CVS daily, the other one is a fresh one (used to double-check the error) Two servers have LANG=en_US.UTF-8. They are RHEL 3.0 Update 3 with gcc 3.2.3-42, and bison 1.875c: == == creating database regression == CREATE DATABASE pg_regress: could not set database default locales make[2]: *** [check] Error 2 rm regress.o make[2]: Leaving directory `/home/pgsql80/pgsql/src/test/regress' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/pgsql80/pgsql/src/test' make: *** [check] Error 2 == Any comments? Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBhMLwtl86P3SPfQ4RAibUAJ4h/gvrWr76FPjJvJRnGsF3TXEY/ACfWJ5d z+wKbSmMSgGaoBXCmeEX/gw= =3l/A -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] fsync, ext2 on Linux
The Linux fsync man page says: It does not necessarily ensure that the entry in the directory containing the file has also reached disk. For that an explicit fsync on the file descriptor of the directory is also needed. AFAIK, we don't care about it at the moment. The actual behaviour depends on the filesystem, reiserfs and other journaling filesystems probably don't need the explicit fsync on the parent directory, but at least ext2 does. I've experimented with a user-mode-linux installation, crashing it at specific points. It seems that on ext2, it's possible to get the database in non-consistent state. Especially: 1. start transaction 2. do a lot of updates, so that a new xlog file is created 3. commit 4. crash Sometimes the creation of the new xlog file is lost, losing the already committed transaction. I also got into this situation after one crash test: template1=# SELECT * FROM foo; ERROR: could not access status of transaction 1768515945 DETAIL: could not open file /home/hlinnaka/pgsql/data_broken/pg_clog/0696: No such file or directory I haven't tried to debug it more deeply. Should we fix this by fsyncing the parent directory of new files? We could also declare ext2 broken, but there could be others. - Heikki ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] float4/float8 regression failure on Alpha Linux
Hi all, I am getting a regression failure on float8 (and float4) when running on Debian Sarge on Alpha (gcc 3.3.4). Postgres is a HEAD checkout from yesterday. test=# select version(); version --- PostgreSQL 8.0.0beta4 on alpha-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-13) (1 row) --This test sould give an error SELECT 'Infinity'::float8 + 100.0; ?column? -- 0 Is this a problem with my system, Alpha's or postgres. Thanks Jim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] fsync, ext2 on Linux
Heikki Linnakangas wrote:
The Linux fsync man page says:
It does not necessarily ensure that the entry in the directory
containing the file has also reached disk. For that an explicit fsync on
the file descriptor of the directory is also needed.
AFAIK, we don't care about it at the moment. The actual behaviour
depends on the filesystem, reiserfs and other journaling filesystems
probably don't need the explicit fsync on the parent directory, but at
least ext2 does.
I've experimented with a user-mode-linux installation, crashing it at
specific points. It seems that on ext2, it's possible to get the
database in non-consistent state.
Have you experimented with mounting the filesystem with the dirsync
option ('-o dirsync') or marking the log directory as synchronous with
'chattr +D'? (no, it's not a real fix, just another data point..)
-O
---(end of broadcast)---
TIP 8: explain analyze is your friend
[HACKERS] Version defines
Greets, Would it be possible to get something along the lines of the attached patch in 8? (major,minor,patch,state version defines) (I tried making them shell vars and giving it to AC_INIT, but it seemed to want a literal, so...) Yes, I know there are other ways to get and define this information, but [something like] this is considerably more convenient, IMO. -- Regards, James William Pye Index: configure.in === RCS file: /projects/cvsroot/pgsql/configure.in,v retrieving revision 1.383 diff -c -r1.383 configure.in *** configure.in 25 Oct 2004 00:11:04 - 1.383 --- configure.in 29 Oct 2004 18:21:13 - *** *** 26,31 --- 26,35 AC_PREFIX_DEFAULT(/usr/local/pgsql) AC_SUBST(configure_args, [$ac_configure_args]) + AC_DEFINE_UNQUOTED(PG_VERSION_MAJOR, 8, [PostgreSQL major version]) + AC_DEFINE_UNQUOTED(PG_VERSION_MINOR, 0, [PostgreSQL minor version]) + AC_DEFINE_UNQUOTED(PG_VERSION_PATCH, 0, [PostgreSQL patch level]) + AC_DEFINE_UNQUOTED(PG_VERSION_STATE, beta4, [PostgreSQL version state]) AC_DEFINE_UNQUOTED(PG_VERSION, $PACKAGE_VERSION, [PostgreSQL version]) AC_CANONICAL_HOST Index: src/include/pg_config.h.in === RCS file: /projects/cvsroot/pgsql/src/include/pg_config.h.in,v retrieving revision 1.80 diff -c -r1.80 pg_config.h.in *** src/include/pg_config.h.in 6 Oct 2004 09:35:22 - 1.80 --- src/include/pg_config.h.in 29 Oct 2004 18:21:13 - *** *** 587,592 --- 587,604 /* Define to the address where bug reports for this package should be sent. */ #undef PACKAGE_BUGREPORT + /* Define to the major version */ + #undef PG_VERSION_MAJOR + + /* Define to the minor version */ + #undef PG_VERSION_MINOR + + /* Define to the patch level */ + #undef PG_VERSION_PATCH + + /* Define to version descriptor */ + #undef PG_VERSION_STATE + /* Define to the full name of this package. */ #undef PACKAGE_NAME signature.asc Description: This is a digitally signed message part
[HACKERS] Problems with pgxs
I have some problems when adjusting PL/Java to use PGXS in its makefiles. Aside from the Win32 problem I asked about earlier, I also have problems using the MODULE_big. When used, it brings in the Makefile.shlib and attempts to use the target all-lib. That target is defined as: all-lib: all-static-lib all-shared-lib all-static-lib: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h lib$(NAME).a all-shared-lib: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h $(shlib) but the $(top_builddir)/src/port directory is non existant in PostgreSQL installation. I'm not sure if I use pgxs in a completely wrong way or if pgxs needs some more work before I can rely on it. Are there any other projects out there that have their source completely separate from the PostgreSQL source that uses PGXS succesfully? If so, your input on this subject is greatly appreciated. Kind regards, Thomas Hallgren ---(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] Version defines
James William Pye [EMAIL PROTECTED] writes: Would it be possible to get something along the lines of the attached patch in 8? (major,minor,patch,state version defines) This has been proposed and rejected before, mainly on the grounds that it would encourage bad programming practices. At compile time, you should be checking the specific feature you care about, not a system version number (this is pretty much the entire point behind Autoconf). At run time, you need to be making a run-time test anyway; compiling against version x.y headers does not guarantee anything about what version you will be executing against at runtime. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] fsync, ext2 on Linux
Heikki Linnakangas [EMAIL PROTECTED] writes: The Linux [ext2] fsync man page says: It does not necessarily ensure that the entry in the directory containing the file has also reached disk. For that an explicit fsync on the file descriptor of the directory is also needed. This seems so broken as to defy belief. A process creating a file doesn't normally *have* a file descriptor for the parent directory, and I don't think the concept of an FD for a directory is even portable (opendir() certainly doesn't return an FD). One might also ask if we are expected to fsync everything up to the root in order to be sure that the file remains accessible, and how exactly we should do that on directories we don't have write access for. In general we expect the filesystem to take care of its own metadata. Run ext3 in journaling mode, or something like that. (It occurs to me that the admin guide really ought to have a few words about recommended and non-recommended filesystems ...) 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] fsync, ext2 on Linux
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: The Linux [ext2] fsync man page says: It does not necessarily ensure that the entry in the directory containing the file has also reached disk. For that an explicit fsync on the file descriptor of the directory is also needed. This seems so broken as to defy belief. A process creating a file doesn't normally *have* a file descriptor for the parent directory, and I don't think the concept of an FD for a directory is even portable (opendir() certainly doesn't return an FD). One might also ask if we are expected to fsync everything up to the root in order to be sure that the file remains accessible, and how exactly we should do that on directories we don't have write access for. The notes say this: When an ext2 file system is mounted with the sync option, directory entries are also implicitly synced by fsync. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Problems with pgxs
Thomas Hallgren [EMAIL PROTECTED] writes: [ Makefile.shlib contains ] all-lib: all-static-lib all-shared-lib all-static-lib: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h lib$(NAME).a all-shared-lib: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h $(shlib) Would someone explain to me what in the world these targets are doing demanding either of those as prerequisite? They certainly did not have those prereqs in 7.4. This seems an excessively klugy way of handling some Windows brokenness or other. 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] fsync, ext2 on Linux
In general we expect the filesystem to take care of its own metadata. Run ext3 in journaling mode, or something like that. (It occurs to me that the admin guide really ought to have a few words about recommended and non-recommended filesystems ...) Well I am not their admin, but I don't suggest any of the ext systems. Although ext3 is reasonably stable it is very slow. Stick with XFS, JFS or even Reiser. Sincerely, Joshua D. Drake 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]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] make check error on -HEAD
Devrim GUNDUZ [EMAIL PROTECTED] writes: pg_regress: could not set database default locales What shows up in the postmaster log file? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] make check error on -HEAD
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sun, 31 Oct 2004, Tom Lane wrote: pg_regress: could not set database default locales What shows up in the postmaster log file? You mean src/test/regress/log/postmaster.log, right? Actually not that much: LOG: database system was shut down at 2004-10-31 18:41:23 EET LOG: checkpoint record is at 0/A30FE8 LOG: redo record is at 0/A30FE8; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 492; next OID: 17230 LOG: database system is ready LOG: unexpected EOF on client connection LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBhRi8tl86P3SPfQ4RAjYeAKC75B6SPdZUR8SNZ1nOu5cXQZ1S1ACfbUhx rSL56HYBFmpltjTEF3s2SQQ= =vZ60 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] array_to_column function
On Sat, Oct 30, 2004 at 11:55:48PM +0200, Markus Bertheau wrote: В Сбт, 30.10.2004, в 21:54, David Fetter пишет: Kind people, CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY) You want to see that function distributed with PostgreSQL? It would probably have to be implemented in C then, because PL/pgSQL-support has to be explicitly enabled for every database. Yes, that would be good. It would be nice to have some symmetry with the ARRAY() function. Also, it would be a handy thing for doing column-like operations (aggregates, e.g.) on 1-D arrays :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] make check error on -HEAD
Devrim GUNDUZ [EMAIL PROTECTED] writes: pg_regress: could not set database default locales What shows up in the postmaster log file? You mean src/test/regress/log/postmaster.log, right? Actually not that much: LOG: unexpected EOF on client connection Hmm, that seems to be the only sign of trouble, which I guess means we have to conclude the problem is on the client side not the server side. The part of the pg_regress script that is failing is evidently $bindir/psql $psql_options -c \ checkpoint; alter database \$dbname\ set lc_messages to 'C'; alter database \$dbname\ set lc_monetary to 'C'; alter database \$dbname\ set lc_numeric to 'C'; alter database \$dbname\ set lc_time to 'C'; $dbname 2/dev/null if [ $? -ne 0 ]; then echo $me: could not set database default locales (exit 2); exit fi Try removing the 2/dev/null bit so that you can see if any error messages come out from psql. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] make check error on -HEAD
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sun, 31 Oct 2004, Tom Lane wrote: LOG: unexpected EOF on client connection Hmm, that seems to be the only sign of trouble, which I guess means we have to conclude the problem is on the client side not the server side. The part of the pg_regress script that is failing is evidently $bindir/psql $psql_options -c \ checkpoint; alter database \$dbname\ set lc_messages to 'C'; alter database \$dbname\ set lc_monetary to 'C'; alter database \$dbname\ set lc_numeric to 'C'; alter database \$dbname\ set lc_time to 'C'; $dbname 2/dev/null if [ $? -ne 0 ]; then echo $me: could not set database default locales (exit 2); exit fi Try removing the 2/dev/null bit so that you can see if any error messages come out from psql. Ok, we have a detailed error now: /home/pgsql80/pgsql/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/psql: relocation error: /home/pgsql80/pgsql/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/psql: undefined symbol: PQgetCancel pg_regress: could not set database default locales make[2]: *** [check] Error 2 make[2]: Leaving directory `/home/pgsql80/pgsql/src/test/regress' Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBhR0utl86P3SPfQ4RAjbWAJ9HsRqk7uW2yZTUbI/i4Ds+nMW6aQCfXlzx BI5pV37UYbKEjXgMxZSpfLY= =Ocrg -END PGP SIGNATURE- ---(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] Version defines
On Sun, 2004-10-31 at 08:02, Tom Lane wrote: This has been proposed and rejected before, mainly on the grounds that it would encourage bad programming practices. I admit that I am probably practicing this bad programming at few places in my source, and shame on me for it. I have hoped to tighten it up a bit later, but it is convenient for the time being. At compile time, you should be checking the specific feature you care about, Well, for one of my uses, it is not a feature check. My PL loads a Python extension module whose path is dependent on the major and minor version of the PostgreSQL installation that the PL was compiled against. So I construct the module path string based on the major and minor at compile time. If this is the stance that the group has, that is fine. For now, I will continue my shameful practice of parsing up pg_config --version and defining the components for use in my source. (; -- Regards, James William Pye signature.asc Description: This is a digitally signed message part
Re: [HACKERS] make check error on -HEAD
Devrim GUNDUZ [EMAIL PROTECTED] writes: Try removing the 2/dev/null bit so that you can see if any error messages come out from psql. Ok, we have a detailed error now: /home/pgsql80/pgsql/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/psql: relocation error: /home/pgsql80/pgsql/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/psql: undefined symbol: PQgetCancel pg_regress: could not set database default locales Oh, so you are using yesterday's libpq.so shared library ;-) I am not sure there is any way around that except to go ahead and install today's libpq. pg_regress can't do much more than set LD_LIBRARY_PATH, and evidently that's not enough to make the Linux dynamic loader take the version of libpq.so that's in the temp installation rather than the one you previously installed. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Version defines
James William Pye [EMAIL PROTECTED] writes: At compile time, you should be checking the specific feature you care about, Well, for one of my uses, it is not a feature check. My PL loads a Python extension module whose path is dependent on the major and minor version of the PostgreSQL installation that the PL was compiled against. So I construct the module path string based on the major and minor at compile time. Er ... can't you just keep it in pkglibdir and refer to it via $libdir? Given that 8.0 now supports relocatable installations, I'd think it best to avoid hardwiring any paths at compile time. 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] fsync, ext2 on Linux
On Mon, 1 Nov 2004, Oliver Jowett wrote:
Heikki Linnakangas wrote:
The Linux fsync man page says:
It does not necessarily ensure that the entry in the directory containing
the file has also reached disk. For that an explicit fsync on the file
descriptor of the directory is also needed.
AFAIK, we don't care about it at the moment. The actual behaviour depends
on the filesystem, reiserfs and other journaling filesystems probably don't
need the explicit fsync on the parent directory, but at least ext2 does.
I've experimented with a user-mode-linux installation, crashing it at
specific points. It seems that on ext2, it's possible to get the database
in non-consistent state.
Have you experimented with mounting the filesystem with the dirsync option
('-o dirsync') or marking the log directory as synchronous with 'chattr +D'?
(no, it's not a real fix, just another data point..)
Quick experiment shows that they seem to fix it as expected.
chattr +D might not be such a bad idea. A warning would be nice if you
start the postmaster on a filesystem that requires it. Few admins would
remember/know about it otherwise.
- Heikki
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] fsync, ext2 on Linux
On Sun, 31 Oct 2004, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: The Linux [ext2] fsync man page says: It does not necessarily ensure that the entry in the directory containing the file has also reached disk. For that an explicit fsync on the file descriptor of the directory is also needed. This seems so broken as to defy belief. A process creating a file doesn't normally *have* a file descriptor for the parent directory, and I don't think the concept of an FD for a directory is even portable (opendir() certainly doesn't return an FD). One might also ask if we are expected to fsync everything up to the root in order to be sure that the file remains accessible, and how exactly we should do that on directories we don't have write access for. I agree on the brokeness. Linux is the only OS that's broken that I know of. Therefore it doesn't really matter if the fix is portable or not, we would only do it on Linux anyway. Surely it's not necessary to crawl up to the root. Just fsync the parent of every new file and directory. In general we expect the filesystem to take care of its own metadata. Run ext3 in journaling mode, or something like that. I normally run reiserfs, I set up the ext2 filesystem just to test it. (It occurs to me that the admin guide really ought to have a few words about recommended and non-recommended filesystems ...) That's the least we can do. I wonder if we could check the filesystem at runtime and issue a warning if it's not in the list of recommended filesystems. - Heikki ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] make check error on -HEAD
Tom Lane wrote: Devrim GUNDUZ [EMAIL PROTECTED] writes: Try removing the 2/dev/null bit so that you can see if any error messages come out from psql. Ok, we have a detailed error now: /home/pgsql80/pgsql/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/psql: relocation error: /home/pgsql80/pgsql/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/psql: undefined symbol: PQgetCancel pg_regress: could not set database default locales Oh, so you are using yesterday's libpq.so shared library ;-) I am not sure there is any way around that except to go ahead and install today's libpq. pg_regress can't do much more than set LD_LIBRARY_PATH, and evidently that's not enough to make the Linux dynamic loader take the version of libpq.so that's in the temp installation rather than the one you previously installed. Yep, I saw the same thing here and make install fixed it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Problems with pgxs
Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: [ Makefile.shlib contains ] all-lib: all-static-lib all-shared-lib all-static-lib: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h lib$(NAME).a all-shared-lib: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h $(shlib) Would someone explain to me what in the world these targets are doing demanding either of those as prerequisite? They certainly did not have those prereqs in 7.4. This seems an excessively klugy way of handling some Windows brokenness or other. $(PTHREAD_H_WIN32) is a dependency of libpq and therefore we had to copy it into Makefile.shlib when we are compiling from that file rather than libpq/Makefile. libpq/Makefile has: all: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h def-files all-lib -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. Despite this, the default_tablespace GUC variable seems more attractive than what we have now. Last call for objections ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] make check error on -HEAD
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Oh, so you are using yesterday's libpq.so shared library ;-) I am not sure there is any way around that except to go ahead and install today's libpq. pg_regress can't do much more than set LD_LIBRARY_PATH, and evidently that's not enough to make the Linux dynamic loader take the version of libpq.so that's in the temp installation rather than the one you previously installed. Yep, I saw the same thing here and make install fixed it. I looked at this a bit more and found that on Linux, the dynamic loader is documented to search rpath before LD_LIBRARY_PATH; so had we not specified an rpath when building the psql executable, pg_regress would have worked as intended. Sounds like BSD is the same. Now, not specifying rpath seems like a sure loss for every context except make check with an uninstalled version. So I'm afraid we have to live with it. It might be worthwhile for build-farm builds to use configure --disable-rpath, if they want to make check without installing first. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Problems with pgxs
Bruce Momjian [EMAIL PROTECTED] writes: $(PTHREAD_H_WIN32) is a dependency of libpq and therefore we had to copy it into Makefile.shlib when we are compiling from that file rather than libpq/Makefile. libpq/Makefile has: all: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h def-files all-lib Say again? Why should libpq's dependencies be propagated to every shlib in the system? And when is libpq built without using libpq/Makefile? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] array_to_column function
David Fetter wrote: On Sat, Oct 30, 2004 at 11:55:48PM +0200, Markus Bertheau wrote: CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY) You want to see that function distributed with PostgreSQL? It would probably have to be implemented in C then, because PL/pgSQL-support has to be explicitly enabled for every database. Yes, that would be good. It would be nice to have some symmetry with the ARRAY() function. Also, it would be a handy thing for doing column-like operations (aggregates, e.g.) on 1-D arrays :) This has actually been discussed and rejected nearly two years ago -- see this thread: http://archives.postgresql.org/pgsql-hackers/2002-12/msg00453.php Later, Peter Eisentraut pointed out that there is actually a spec-compliant way (UNNEST) to achieve the same result -- see this thread: http://archives.postgresql.org/pgsql-hackers/2003-06/msg01167.php I have the beginnings (at least) of a C function to do this somewhere, but have obviously not been able to find the time to implement it (yet). If you want to pursue this functionality for 8.1, UNNEST support is the way to go. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] make check error on -HEAD
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Oh, so you are using yesterday's libpq.so shared library ;-) I am not sure there is any way around that except to go ahead and install today's libpq. pg_regress can't do much more than set LD_LIBRARY_PATH, and evidently that's not enough to make the Linux dynamic loader take the version of libpq.so that's in the temp installation rather than the one you previously installed. Yep, I saw the same thing here and make install fixed it. I looked at this a bit more and found that on Linux, the dynamic loader is documented to search rpath before LD_LIBRARY_PATH; so had we not specified an rpath when building the psql executable, pg_regress would have worked as intended. Sounds like BSD is the same. Now, not specifying rpath seems like a sure loss for every context except make check with an uninstalled version. So I'm afraid we have to live with it. It might be worthwhile for build-farm builds to use configure --disable-rpath, if they want to make check without installing first. The build-farm script removes the installation directory (we don't use the default, of course) after each run, so the library won't ever be found in the rpath during make check, regardless of this setting. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Version defines
James William Pye wrote: If this is the stance that the group has, that is fine. For now, I will continue my shameful practice of parsing up pg_config --version and defining the components for use in my source. (; FWIW, here's what I've been using in PL/R for a while now: /* working with postgres 7.3 compatible sources */ #if (CATALOG_VERSION_NO = 200211021) #define PG_VERSION_73_COMPAT #elif (CATALOG_VERSION_NO = 200310211) #define PG_VERSION_74_COMPAT #else #define PG_VERSION_75_COMPAT #endif Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] horology and DST changeover
It's time for the 6 monthly discussion of the failure of the horology tests during DST changeover. I'd like to find a way to avoid this, so we don't get spurious buildfarm failures for 2 or 3 days every six months. ISTM the first thing is to isolate the tests that are sensitive to it into a separate script. Then I'd like to have either some test that disables the sensitive tests around DST changeover, or allows some alternative output during that time. Detection of DST sensitivity should be a moderately simple perl one-liner, and we already require perl to build anyway. Thoughts? cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Fixing DetermineLocalTimeZone, this time for sure
I noticed today that we have once again managed to break PG's handling of ambiguous timestamps during a DST backwards transition. For example, in EST5EDT time zone, '2004-10-31 01:30:00' is ambiguous. The intention of the code for a long time has been to treat such times as being local standard time (so, EST in this case), but 7.4 and CVS tip get it wrong :-( (I coulda sworn I tested this case last time we touched DetermineLocalTimeZone; but anyway, what's done is done.) If you feel like weighing in on what the behavior *should* be, please add to the thread over in pgsql-general: http://archives.postgresql.org/pgsql-general/2004-10/msg01546.php What I want to talk about in this thread is implementation issues. We have spent enough effort fruitlessly trying to make this code work, first atop mktime() and then atop localtime(), to make me think it's time for a fresh approach. In particular, now that we have control of our own timezone library, we should think about offering a new API that would help out DetermineLocalTimeZone. What I'm toying with is a function to determine the next DST transition time, perhaps along the lines of extern int pg_next_dst_boundary(const pg_time_t *timep, long int *before_gmtoff, int *before_isdst, pg_time_t *boundary, long int *after_gmtoff, int *after_isdst); *timep is the input value, the other parameters are return values. When the return value is 1, *boundary is set to the time_t representation of the next DST transition time at or after *timep, *before_gmtoff and *before_isdst are set to the GMT offset and isdst state prevailing just before that boundary, and after_gmtoff and after_isdst are set to the state prevailing just after that boundary. When the return value is 0, there is no known DST transition at or after *timep, but *before_gmtoff and *before_isdst indicate the GMT offset and isdst state prevailing at *timep. (This would occur in DST-less time zones, for example.) A return value of -1 could indicate failure (this would not actually occur in our current implementation). The way DetermineLocalTimeZone would use this is to compute a GMT-zone time_t value from its input timestamp fields, subtract say 24 hours, and call pg_next_dst_boundary. Assuming no timezone has DST transitions less than two days apart, this is guaranteed to find the closest relevant DST transition. If the result is zero, we're done: before_gmtoff is the desired timezone offset. If the result is one, we can subtract before_gmtoff and after_gmtoff from the GMT-zone time_t to determine two candidate actual timestamp interpretations of the input. If these are both less than the boundary, the desired zone is before_gmtoff; if both greater, the desired zone is after_gmtoff; otherwise we have an impossible or ambiguous timestamp input, which we can resolve by choosing whichever gmtoff is standard time according to the isdst flags. (Or we can raise error, according to the suggestion made in the other thread, or perhaps do something else --- we now have enough information to implement any of several possible algorithms.) Aside from giving us the opportunity to get the right answer reliably, this should be substantially faster than our existing code, since pg_next_dst_boundary is essentially just the first step of pg_localtime() whereas the existing code requires several pg_localtime() calls plus additional arithmetic. Does this seem too ugly to anyone? Do you have an idea for a cleaner implementation? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] horology and DST changeover
Andrew Dunstan [EMAIL PROTECTED] writes: It's time for the 6 monthly discussion of the failure of the horology tests during DST changeover. This will go away whenever we fix the interval datatype to distinguish '1 day' from '24 hours'. In the meantime, it reminds us of the work we need to do there ;-). I don't think you should do any heavy lifting to hide the error. 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] horology and DST changeover
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: It's time for the 6 monthly discussion of the failure of the horology tests during DST changeover. This will go away whenever we fix the interval datatype to distinguish '1 day' from '24 hours'. In the meantime, it reminds us of the work we need to do there ;-). I don't think you should do any heavy lifting to hide the error. Ok. I hope this happens before the next changeover, then. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Problems with pgxs
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: $(PTHREAD_H_WIN32) is a dependency of libpq and therefore we had to copy it into Makefile.shlib when we are compiling from that file rather than libpq/Makefile. libpq/Makefile has: all: $(PTHREAD_H_WIN32) $(top_builddir)/src/port/pg_config_paths.h def-files all-lib Say again? Why should libpq's dependencies be propagated to every shlib in the system? And when is libpq built without using libpq/Makefile? No idea. I just know it fixed the problem. The problem report was that you could not build all-static-lib under MinGW. I confirmed that using my MinGW here and developed the following patch: *** *** 245,253 all-lib: all-static-lib all-shared-lib ! all-static-lib: lib$(NAME).a ! all-shared-lib: $(shlib) ifneq ($(PORTNAME), cygwin) ifneq ($(PORTNAME), win32) --- 245,253 all-lib: all-static-lib all-shared-lib ! all-static-lib: $(PTHREAD_H_WIN32) $(top_srcdir)/src/port/pg_config_paths.h lib$(NAME).a ! all-shared-lib: $(PTHREAD_H_WIN32) $(top_srcdir)/src/port/pg_config_paths.h $(shlib) ifneq ($(PORTNAME), cygwin) ifneq ($(PORTNAME), win32) The problem was that building libpq.a was not generating the include file dependencies. The cause I think is that we have a separate Win32 build rule for a static lib in Makefile.shlib: # win32 case $(shlib) lib$(NAME).a: $(OBJS) ifndef DLL_DEFFILE $(DLLTOOL) --export-all $(DLLTOOL_DEFFLAGS) --output-def $(NAME).def $(OBJS) $(DLLWRAP) $(LDFLAGS_SL) -o $(shlib) --dllname $(shlib) $(DLLWRAP_FLAGS) --def $(NAME).def $(OBJS) $(SHLIB_LINK) $(DLLTOOL) --dllname $(shlib) $(DLLTOOL_LIBFLAGS) --def $(NAME).def --output-lib lib$(NAME).a else $(DLLWRAP) $(LDFLAGS_SL) -o $(shlib) --dllname $(shlib) $(DLLWRAP_FLAGS) --def $(DLL_DEFFILE) $(OBJS) $(SHLIB_LINK) $(DLLTOOL) --dllname $(shlib) $(DLLTOOL_LIBFLAGS) --def $(DLL_DEFFILE) --output-lib lib$(NAME).a endif and that rule discusses only the OBJ files and doesn't know about the *.h files that are needed. Ideas? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. Right. I would say 99% of people are using template1 as the template for new databases, and if we clearly give an error message when they use a database not in the default tablespace (which we do now), it seems just fine. Let's see how many people complain and make adjustments in 8.1 if needed. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. Agreed. That is just too much work for so little gain. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Despite this, the default_tablespace GUC variable seems more attractive than what we have now. Last call for objections ... Sounds good. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Suggestion: additional system views
Josh Berkus wrote: Neil, pg_functions might be useful, but what would pg_users offer that pg_user does not already do? Show a list of groups that the user belongs to? Same thing with pg_groups; showing the list of users in the group. A pg_sequences view might also be handy. Yes. Anything else? So far I have: pg_users pg_groups pg_functions pg_sequences hmmm ... pg_schemas pg_tablespaces ... as well, just for completeness. This is obviously and 8.1 thing, so I'll put it on my task list for after 8.0 PR is done. I suggest to add on pg_functions and on pg_views too, the list of dependencies with other objects. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
On Sun, 31 Oct 2004, Bruce Momjian wrote: Tom Lane wrote: I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. Right. I would say 99% of people are using template1 as the template for new databases, and if we clearly give an error message when they use a database not in the default tablespace (which we do now), it seems just fine. Let's see how many people complain and make adjustments in 8.1 if needed. I agree. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. Agreed. That is just too much work for so little gain. I agree. Although, I think having a createdb() with transaction semantics and the ability to modify data on the fly would be useful -- not just for tablespace handling. As you say, it is a fair bit of work, however. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Neil has been talking to me about being able to set a tablespace for temporary tables at or after create database time. I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. Thanks, Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Gavin Sherry wrote: One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Neil has been talking to me about being able to set a tablespace for temporary tables at or after create database time. I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. I think we need to add temp_tablespace and toast_tablespace GUC variables to deal with this, perhaps for 8.1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Added to open items: * Add a GUC variable to control temporary and TOAST tablespace usage --- Gavin Sherry wrote: On Sun, 31 Oct 2004, Bruce Momjian wrote: Tom Lane wrote: I wrote: I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. After further thought it seems to me that using a default_tablespace GUC variable doesn't eliminate all the open issues. In particular it is no help for the problem of merging two different tablespaces during CREATE DATABASE, ie, creating a new DB with a dattablespace that is different from the template DB's default when the template DB already has some tables explicitly placed into that tablespace. In this situation we have the problem that the cloned DB would have pg_class rows with different references to the same tablespace (either zero for the database default, or the explicit OID of the tablespace). Among other things this would make it impossible to use the cloned DB again as a template for CREATE DATABASE. Right. I would say 99% of people are using template1 as the template for new databases, and if we clearly give an error message when they use a database not in the default tablespace (which we do now), it seems just fine. Let's see how many people complain and make adjustments in 8.1 if needed. I agree. AFAICS this problem stems ultimately from the choice to have a special representation (zero) in pg_class for the database's default tablespace. The only way to really get rid of it would be to eliminate that provision and say that pg_class.reltablespace is always the correct explicit OID. What that would mean in turn is that we could not copy a database and move its tables into a different tablespace, at least not without very major work on CREATE DATABASE to make it alter pg_class on-the-fly while copying. Agreed. That is just too much work for so little gain. I agree. Although, I think having a createdb() with transaction semantics and the ability to modify data on the fly would be useful -- not just for tablespace handling. As you say, it is a fair bit of work, however. We might want to think about doing that eventually, but for now I'd say that the restriction on merging tablespaces is just something we have to live with. It's less annoying than not being able to relocate a database, for sure. One downside that came up yesterday in a discussion is that once shemas don't have default tablespaces we can't easily have default tablespaces for toast and temporary table system schemas. Now we can't actually do that now anyway because they are created by the system but it might limit how we can control these in the future. I am just throwing this out as a point. Neil has been talking to me about being able to set a tablespace for temporary tables at or after create database time. I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. Thanks, Gavin -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] Suggestion: additional system views
On Mon, Nov 01, 2004 at 12:49:47AM +0100, Gaetano Mendola wrote: Josh Berkus wrote: Neil, pg_functions might be useful, but what would pg_users offer that pg_user does not already do? Show a list of groups that the user belongs to? Same thing with pg_groups; showing the list of users in the group. A pg_sequences view might also be handy. Yes. Anything else? So far I have: pg_users pg_groups pg_functions pg_sequences hmmm ... pg_schemas pg_tablespaces ... as well, just for completeness. This is obviously and 8.1 thing, so I'll put it on my task list for after 8.0 PR is done. I suggest to add on pg_functions and on pg_views too, the list of dependencies with other objects. pg_keywords pg_sqlstates Attached is a rough draft of the latter. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! BEGIN; CREATE TABLE sqlstate ( error_code CHAR(5) PRIMARY KEY , meaning TEXT ); COPY sqlstate FROM STDIN; 0 SUCCESSFUL COMPLETION 01000 WARNING 0100C WARNING DYNAMIC RESULT SETS RETURNED 01008 WARNING IMPLICIT ZERO BIT PADDING 01003 WARNING NULL VALUE ELIMINATED IN SET FUNCTION 01004 WARNING STRING DATA RIGHT TRUNCATION 02000 NO DATA 02001 NO ADDITIONAL DYNAMIC RESULT SETS RETURNED 03000 SQL STATEMENT NOT YET COMPLETE 08000 CONNECTION EXCEPTION 08003 CONNECTION DOES NOT EXIST 08006 CONNECTION FAILURE 08001 SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION 08004 SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION 08007 TRANSACTION RESOLUTION UNKNOWN 08P01 PROTOCOL VIOLATION 09000 TRIGGERED ACTION EXCEPTION 0A000 FEATURE NOT SUPPORTED 0B000 INVALID TRANSACTION INITIATION 0F000 LOCATOR EXCEPTION 0F001 INVALID SPECIFICATION 0L000 INVALID GRANTOR 0LP01 INVALID GRANT OPERATION 0P000 INVALID ROLE SPECIFICATION 21000 CARDINALITY VIOLATION 22000 DATA EXCEPTION 2202E ARRAY ELEMENT ERROR 22021 CHARACTER NOT IN REPERTOIRE 22008 DATETIME FIELD OVERFLOW 22012 DIVISION BY ZERO 22005 ERROR IN ASSIGNMENT 2200B ESCAPE CHARACTER CONFLICT 22022 INDICATOR OVERFLOW 22015 INTERVAL FIELD OVERFLOW 22018 INVALID CHARACTER VALUE FOR CAST 22007 INVALID DATETIME FORMAT 22019 INVALID ESCAPE CHARACTER 2200D INVALID ESCAPE OCTET 22025 INVALID ESCAPE SEQUENCE 22010 INVALID INDICATOR PARAMETER VALUE 22020 INVALID LIMIT VALUE 22023 INVALID PARAMETER VALUE 2201B INVALID REGULAR EXPRESSION 22009 INVALID TIME ZONE DISPLACEMENT VALUE 2200C INVALID USE OF ESCAPE CHARACTER 2200G MOST SPECIFIC TYPE MISMATCH 22004 NULL VALUE NOT ALLOWED 22002 NULL VALUE NO INDICATOR PARAMETER 22003 NUMERIC VALUE OUT OF RANGE 22026 STRING DATA LENGTH MISMATCH 22001 STRING DATA RIGHT TRUNCATION 22011 SUBSTRING ERROR 22027 TRIM ERROR 22024 UNTERMINATED C STRING 2200F ZERO LENGTH CHARACTER STRING 22P01 FLOATING POINT EXCEPTION 22P02 INVALID TEXT REPRESENTATION 22P03 INVALID BINARY REPRESENTATION 22P04 BAD COPY FILE FORMAT 22P05 UNTRANSLATABLE CHARACTER 23000 INTEGRITY CONSTRAINT VIOLATION 23001 RESTRICT VIOLATION 23502 NOT NULL VIOLATION 23503 FOREIGN KEY VIOLATION 23505 UNIQUE VIOLATION 23514 CHECK VIOLATION 24000 INVALID CURSOR STATE 25000 INVALID TRANSACTION STATE 25001 ACTIVE SQL TRANSACTION 25002 BRANCH TRANSACTION ALREADY ACTIVE 25008 HELD CURSOR REQUIRES SAME ISOLATION LEVEL 25003 INAPPROPRIATE ACCESS MODE FOR BRANCH TRANSACTION 25004 INAPPROPRIATE ISOLATION LEVEL FOR BRANCH TRANSACTION 25005 NO ACTIVE SQL TRANSACTION FOR BRANCH TRANSACTION 25006 READ ONLY SQL TRANSACTION 25007 SCHEMA AND DATA STATEMENT MIXING NOT SUPPORTED 25P01 NO ACTIVE SQL TRANSACTION 25P02 IN FAILED SQL TRANSACTION 26000 INVALID SQL STATEMENT NAME 27000 TRIGGERED DATA CHANGE VIOLATION 28000 INVALID AUTHORIZATION SPECIFICATION 2B000 DEPENDENT PRIVILEGE DESCRIPTORS STILL EXIST 2BP01 DEPENDENT OBJECTS STILL EXIST 2D000 INVALID TRANSACTION TERMINATION 2F000 SQL ROUTINE EXCEPTION 2F005 FUNCTION EXECUTED NO RETURN STATEMENT 2F002 MODIFYING SQL DATA NOT PERMITTED 2F003 PROHIBITED SQL STATEMENT ATTEMPTED 2F004 READING SQL DATA NOT PERMITTED 34000 INVALID CURSOR NAME 38000 EXTERNAL ROUTINE EXCEPTION 38001 CONTAINING SQL NOT PERMITTED 38002 MODIFYING SQL DATA NOT PERMITTED 38003 PROHIBITED SQL STATEMENT ATTEMPTED 38004 READING SQL DATA NOT PERMITTED 39000 EXTERNAL ROUTINE INVOCATION EXCEPTION 39001 INVALID SQLSTATE RETURNED 39004 NULL VALUE NOT ALLOWED 39P01 TRIGGER PROTOCOL VIOLATED 39P02 SRF PROTOCOL VIOLATED 3D000 INVALID CATALOG NAME 3F000 INVALID SCHEMA NAME 4 TRANSACTION ROLLBACK 40002 INTEGRITY CONSTRAINT VIOLATION 40001 SERIALIZATION FAILURE 40003 STATEMENT COMPLETION UNKNOWN 40P01 DEADLOCK DETECTED 42000 SYNTAX ERROR OR ACCESS RULE VIOLATION 42601 SYNTAX ERROR 42501 INSUFFICIENT PRIVILEGE 42846 CANNOT COERCE 42803 GROUPING ERROR 42830 INVALID FOREIGN KEY
[HACKERS] Win32 lost signals open item
We have this open item: Win32 o Handle lost signals on backend startup (eg. shutdown, config file changes, etc); signals are SIG_DFL on startup The problem here is that the postmaster might send signals to a child before the signal handlers are installed. We don't have this problem on unix because we fork and inherit the signal handlers. Win32 uses a special socket to receive signals and does not use the standard Unix signal mechanism. However, the socket doesn't exist on backend process start so there is possible loss of signal while the backend starts. The only solution I can think of for us is to set a PROC struct variable when you can't send the Win32 backend a signal and have the backend check this PROC variable after it starts listening for signals. However, there would still be a window where the signal could fail but the backend could check the variable before the postmaster sets it so we might just set the variable before a signal is sent and because it is only checked when we start listening for signals it should be OK. However, I don't think the postmaster reads/writes PROC so we would need some other way of flagging the backend. I bet there is some Win32 API that might help us. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Version defines
On Sun, 2004-10-31 at 10:49, Tom Lane wrote: Er ... can't you just keep it in pkglibdir and refer to it via $libdir? Given that 8.0 now supports relocatable installations, I'd think it best to avoid hardwiring any paths at compile time. Hmm.. I think it would be best to keep Python [extension] modules in Python's site-packages. AFA hardwiring is concerned, I will probably make it a GUC variable in 8.0 that will default to how I currently hardwire it. -- Regards, James William Pye signature.asc Description: This is a digitally signed message part
Re: [HACKERS] make check error on -HEAD
On Mon, 2004-11-01 at 07:51, Tom Lane wrote: I looked at this a bit more and found that on Linux, the dynamic loader is documented to search rpath before LD_LIBRARY_PATH; so had we not specified an rpath when building the psql executable, pg_regress would have worked as intended. Sounds like BSD is the same. Seems Solaris does it the other way: http://list-archive.xemacs.org/xemacs-beta/21/msg00328.html although Drepper says in the thread that searching rpath before LD_LIBRARY_PATH is per standard. Now, not specifying rpath seems like a sure loss for every context except make check with an uninstalled version. So I'm afraid we have to live with it. This is a kludge, but could we specify LD_PRELOAD? -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tablespaces for temporary files
On Sat, 2004-10-30 at 00:50, Tom Lane wrote: (1) What are the protection requirements for this variable? I think it can be USERSET -- most commands let the user specify a tablespace explicitly, and this is basically just another way of doing that. The user executing the query will need CREATE privileges on the tablespace they end up writing to. (2) I don't think that undefined is a particularly good concept for GUC variables. Particularly not ones that you are envisioning setting from multiple places. Hmm, ok. How about a token like $database that expands to the tablespace of the current database? (3) I don't like the idea that a catalog lookup will be necessary before we can create or access temp files. It would be quite unacceptable from a modularity standpoint to have the low-level routines that currently determine temp file paths do catalog accesses. I don't agree it is unacceptable, but it isn't ideal, granted. On the whole I'm unconvinced that this is worth the trouble. One of the reasons for allowing people to move databases around is to determine where their temp files go. I think this needlessly limits the flexibility of the system. Once you've created a database and added a bunch of tables to it (in the DB's tablespace), is there an easy way to change the tablespace used for temporary files? What if the DBA has placed the database in a relatively slow tablespace because that is suitable most of the time, but needs to quickly execute a large OLAP query that consumes a lot of temporary space? What if it makes sense at a particular installation for different users to use different tablespaces for their temporary files? I just think that always using the database's tablespace for temporary files needlessly conflates two distinct concepts. Also, it's always been possible for people to change the pgsql_tmp subdirectory into a symlink. This is a pain for the DBA, as you mention; it requires shutting down the database; and it is fragile to begin with because the pgsql_tmp directory is created on demand. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. I think we need to add temp_tablespace and toast_tablespace GUC variables to deal with this, perhaps for 8.1. A tablespace for temp tables is okay, but I'm fairly dubious about the idea of a toast tablespace. The current behavior is that a toast table is automatically placed into the same tablespace as its parent, and that seems exactly right to me. It's certainly the right thing from the point of view of users who do not understand TOAST and expect all of a table's data to get put where they said to put the table. 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] Using ALTER TABLESPACE in pg_dump
OK, TODO updated: * Add a GUC variable to control the tablespace for temporary objects --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I'm not sure about TOAST however. I considered the idea of adding something to CREATE TABLE like TOASTSPACE tablespace, such that all TOAST tables would be put in the 'toastspace'. But I think the syntax is ugly and would confuse many users who do not know what toast is. I think we need to add temp_tablespace and toast_tablespace GUC variables to deal with this, perhaps for 8.1. A tablespace for temp tables is okay, but I'm fairly dubious about the idea of a toast tablespace. The current behavior is that a toast table is automatically placed into the same tablespace as its parent, and that seems exactly right to me. It's certainly the right thing from the point of view of users who do not understand TOAST and expect all of a table's data to get put where they said to put the table. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers-win32] Win32 lost signals open item
Bruce Momjian [EMAIL PROTECTED] writes: We have this open item: Win32 o Handle lost signals on backend startup (eg. shutdown, config file changes, etc); signals are SIG_DFL on startup The problem here is that the postmaster might send signals to a child before the signal handlers are installed. We don't have this problem on unix because we fork and inherit the signal handlers. FWIW, I think the todo's description of the problem is completely inaccurate. The issue is not the lack of signal handler settings per se, it is that our pipe-based emulation of signals isn't ready to collect signal messages until some time after the child process starts. Could this be fixed by having the postmaster set up the pipe *before* it forks/execs the child? We'd probably need to pass down some additional info to inform the child where it has to hook into the pipe structure, but passing down more state is no problem. The only solution I can think of for us is to set a PROC struct variable when you can't send the Win32 backend a signal and have the backend check this PROC variable after it starts listening for signals. A backend does not create its PROC entry until *long* after it gets forked, so this does not sound like a path to a solution. Also, I'd prefer to be able to signal non-backend children such as pgstat. (I'm not sure if the current code actually needs that, but I can definitely believe that we'll need to do it some day.) Also, we do need to be able to signal the postmaster from backends, so we cannot tie the signal mechanism to the assumption that every signalable process has or will eventually have a PROC entry. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-hackers-win32] Win32 lost signals open item
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We have this open item: Win32 o Handle lost signals on backend startup (eg. shutdown, config file changes, etc); signals are SIG_DFL on startup The problem here is that the postmaster might send signals to a child before the signal handlers are installed. We don't have this problem on unix because we fork and inherit the signal handlers. FWIW, I think the todo's description of the problem is completely inaccurate. The issue is not the lack of signal handler settings per OK, updated: o Handle lost signals on backend startup (eg. shutdown, config file changes, etc); signals are not possible on startup The problem here is that the postmaster might send signals to a child before the Win32 pipe is created to accept signals. We don't have this problem on unix because we fork and inherit the signal handlers. se, it is that our pipe-based emulation of signals isn't ready to collect signal messages until some time after the child process starts. Could this be fixed by having the postmaster set up the pipe *before* it forks/execs the child? We'd probably need to pass down some additional info to inform the child where it has to hook into the pipe structure, but passing down more state is no problem. Not sure. Magnus? The only solution I can think of for us is to set a PROC struct variable when you can't send the Win32 backend a signal and have the backend check this PROC variable after it starts listening for signals. A backend does not create its PROC entry until *long* after it gets forked, so this does not sound like a path to a solution. Also, I'd prefer to be able to signal non-backend children such as pgstat. (I'm not sure if the current code actually needs that, but I can definitely believe that we'll need to do it some day.) Also, we do need to be able to signal the postmaster from backends, so we cannot tie the signal mechanism to the assumption that every signalable process has or will eventually have a PROC entry. OK. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Open Items
Where are we on this patch? --- Zeugswetter Andreas DAZ SD wrote: o fix shared memory on Win2k terminal server We might be able to just mark this as not supported. I have attached a patch that I think fixes this. The problem I saw and fixed is, that the shmem created in a terminal services client is not visible to the console (or services.msc). It was necessary to differenciate OS versions, this might be better put elsewhere. I think in addition the system global name sharemem.1 should be made more pg specific, like PostgreSQL.1. I have not done this since a new compile would not detect a running old beta. But now would be the time (or never). Andreas Content-Description: shmem.win32.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] charset/collation in values
I've looked into storing charset/collation in the string values. This means that we change varchar/text/BpChar to be structures that have a charset oid field and a collation oid field, the rest of the Datum is the string data. Coercability I think one don't need to put in the Datum and it can be stored in the Nodes. Charset/Collation need to be in the Datum since we send that into functions as arguments. Since we are changing what's stored in the Datum and the normal code saves that on disk then we will end up with charset/collation stored on disk for each value. If we want to avoid storing charset/collation both in the column type and in each row, we would need an extra layer that transforms the Datums before they are stored. As a first implementation it's easier to just store everything. For each type we need to have convertion functions to and from strings. Any suggestion of how to represent these as strings now when it's a string plus two oid's? This is a though one.. I have more comments/questions later on, but these are enough for one mail. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql and schemas
On Sun, 2004-10-31 at 05:32, Tom Lane wrote: The behaviors you mention were written at different times by different people, and mostly have nothing to do with schemas per se. I agree that some more consistency would probably be good. Do you have a specific proposal? Sure, I just thought I'd check if there was method to psql's madness before suggesting changes. Proposed new behavior: \dn non_existent_schema === No such schema ... (previously: empty list of schemas) \d non_existent_schema.* === No such schema ... (previously: Did not find any relation named non_existent_schema.*.) I'm not sure how we should handle \dn schema_name. (notice the period; assuming a schema with that name exists). The current behavior of listing all schemas is obviously wrong, but I'm not sure what the right behavior is. Perhaps we should reject the command? I think there needs to be a way to list all the objects in a schema. What do people think about making \dn schema behave like \dn+ schema currently does, and changing \dn+ schema to list the objects in the specified schema, like \d currently does for the objects in the search path? (BTW, I think a useful way to assess the usability of psql's schema slash commands is trying to use them to explore the information_schema. Perhaps I'm missing something, but with the current psql it seems almost impossible to do that effectively without adding information_schema to the search path.) -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
