[HACKERS] Encoding issues
Receiving a request to add ISO 8859-15 and 16, I review the multibyte support code and found several errors in it. 1) There is a confusion between LATIN5 and ISO 8859-5. LATIN5 is not ISO 8859-5, but is actually ISO 8859-9. Should we rename LATIN5 to ISO8859-5 (or whatever) as the encoding name? I think we should. For your information, here are the correct mapping between ISO 8859-n and LATINn. ISO 8859-1 LATIN1 ISO 8859-2 LATIN2 ISO 8859-3 LATIN3 ISO 8859-4 LATIN4 ISO 8859-9 LATIN5 ISO 8859-10 LATIN6 2) The leading characters for some Cyrillic charsets are wrong. Currently they are defined as: #define LC_KOI8_R 0x8c/* Cyrillic KOI8-R */ #define LC_KOI8_U 0x8c/* Cyrillic KOI8-U */ #define LC_ISO8859_50x8d/* ISO8859 Cyrillic */ These should be: #define LC_KOI8_R 0x8b/* Cyrillic KOI8-R */ #define LC_KOI8_U 0x8b/* Cyrillic KOI8-U */ #define LC_ISO8859_50x8c/* ISO8859 Cyrillic */ The impact of correcting them would be for users who are storing their data into database using MULE internal code. I think they are quite few people using MULE internal code. So we could correct them for 7.2. Comments? BTW, should we support ISO 8859-6 and beyond for 7.2? There have been some requests to do that. Supporting them are actually trivial works, should be one day job. The harder part is writing conversion function between encodings. However, there is very few demands to do that, I guess. If so, we could ommit the conversion capability for 7.2. Comments? -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Connections, table locks, disk space
Hi, I've done some research on your request, but I could not find very much to help you. What I've found about 1) Connections http://www.postgresql.org/idocs/index.php?runtime-config.html enable LOG_CONNECTIONS (boolean), LOG_PID (boolean) to log database users 2) Table locks nothing 3) Consumed disk space of a specific database All database related files are located in $PGDATA/base/database-name So, by summing all file sizes within this directory, you should have it. As far as I know, the only limitation to a database is given by the total disk capacity. I hope this helps at least a bit. I've looked through the FAQ list too, but couldn't find anything which might help you. Still, I don't understand why nobody else is answering. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Setting Password
Hi List, Iam pretty new to this list as well as PostgreSQL. I hope to find some crucial info from here. Thnx in advance to all those who would contribute to it. Iam basically an Oracle Consultant. At first i would like to clarify how to enforce password for a user i have created. I use the psql client to access the database and unless and until the -U option (psql template1 -U user ) is used, iam not prompted to enter any password. Even thou i enter a wrong password iam still allowed to log in. Is there any property needs to be altered to enforce the same ? Looking forward for some favourable responses. Regards Balaji
Re: [HACKERS] TOAST and TEXT
Rod Taylor wrote: It should be noted that there is still a limit of about 1GB if I remember correctly. You're right, there is still a practical limit on the size of a text field. And it's usually much lower than 1GB. The problem is that first, the (encoded) data has to be put completely into the querystring, passed to the backend and buffered there entirely in memory. Then it get's parsed, and the data copied into a const node. After rewriting and planning, a heap tuple is build, containing the third, eventually fourth in memory copy of the data. After that, the toaster kicks in, allocates another chunk of that size to try to compress the data and finally slices it up for storage. So the limit depends on how much swapspace you have and where the per process virtual memory limit of your OS is. In practice, sizes of up to 10 MB are no problem. So storing typical MP3s works. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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: Daily snapshots hosed (was Re: [pgadmin-hackers] [HACKERS] What
okay, daily snapshots are now being generated on the new server ... right now, all the mirror sites are stale while Vince does some finishing touches on the mirroring scripts/cgi's ... once he gerts that done, then, from my perspective, we'll be ready for beta ... On Mon, 8 Oct 2001, Tom Lane wrote: Dave Page [EMAIL PROTECTED] writes: ... I can't find an up-to-date snapshot I tried postgresql.rmplc.co.uk and got one (apparently) dated 7 Oct, however CREATE OR REPLACE FUNCTION didn't seem to be there (it certainly doesn't work anyway - syntax error at OR). I then looked in the primary copy on mail.postgresql.org and found the copy there was dated 30 Sept from which I assumed that the 07/10/2001 date on rm's copy was actually a US date - that site has been seriously out of date before. I just downloaded ftp://ftp.us.postgresql.org/dev/postgresql-snapshot.tar.gz which has a date of yesterday in the FTP archives, but actually contains a snapshot from around 15 September as near as I can tell. Looks like something is hosed in the snapshot preparation process; Marc, could you take a look at it? and I don't know the magic that has to be worked on the PostgreSQL CVS version of the configure script in order to make it run without barfing. I always assumed that something is done when the tarballs are built as the work just fine on the same machine. No, the tarballs should be the same as what you get from a CVS pull of the same date (other than not having a lot of /CVS subdirectories). In fact, they're made basically by tar'ing up a CVS checkout. Please try diffing configure from a tarball against one from CVS to see if you can figure out what's getting munged during your CVS pull. The only odd thing I can think of is that my copy of the source is maintained on my PC using WinCVS and was zipped/ftp'd onto a test box. LF vs CR/LF newlines leap to mind as a likely source of trouble... though I'm not sure why that would manifest in just this way... 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])
[HACKERS] pg_dump oid problems
Apologies for posting to [Hackers], have already posted to [Patches] with no reply. When trying to pg_dump on 7.1.2 ( 7.1.3) I get the following error message: bash-2.04$ pg_dump dwh getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp. Explanation from backend: ERROR: dtoi4: integer out of range bash-2.04$ pg_dump -v dwh -- saving database definition -- last builtin oid is 18539 -- reading user-defined types -- reading user-defined functions -- reading user-defined aggregates -- reading user-defined operators -- reading user-defined tables getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp. Explanation from backend: ERROR: dtoi4: integer out of range I have already applied the patches described by Martin Weinberg and Tom Lane (see below), but this doesn't deem to have fixed my problem. - --- pg_dump.cThu Sep 6 21:18:21 2001 +++ pg_dump.c.origThu Sep 6 21:19:08 2001 @@ -2289,7 +2289,7 @@ resetPQExpBuffer(query); appendPQExpBuffer(query, - SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = '%s'::oid , + SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = %s , tblinfo[i].oid); res2 = PQexec(g_conn, query-data); if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK) @@ -3035,7 +3035,6 @@ query = createPQExpBuffer(); appendPQExpBuffer(query, SELECT description FROM pg_description WHERE objoid = ); appendPQExpBuffer(query, oid); -appendPQExpBuffer(query, ::oid); /*** Execute query ***/ Several of my tables have very large OIDs (over 4 billion in some cases ! don't know why) , these are obviously also causing dtoi4 error messages when entering table design in pgaccess, but one can carry on past the messages and continue working. I am also having problems in CodeCharge using the ODBC driver - Codecharge fails to get column names for tables with high OIDs. Tables with lower OIDs in the same database work fine :-) I've had no problems with any previous version of PostgreSQL much of the data in this database has been progressively migrated over the last couple of years from 6.2. My interest in pg_dump is to dump my database without OIDs (normally I dump with OIDs so I've been carrying these big numbers for some time), drop everything and rebuild (psql data.out) so that I hopefully get new smaller OIDs generated. Is this likely to work if I get round the pg_dump problems? Anyway, what's needed now is suggestions as to what else I must do to get pg_dump working with my large OIDs, any ideas?? Thanks, Steve ---(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] Encoding issues
Tatsuo Ishii writes: BTW, should we support ISO 8859-6 and beyond for 7.2? If possible we should. Otherwise people might spread the word that PostgreSQL is not ready for the Euro. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] extract(timezone_hour) funny business
peter=# select current_timestamp; timestamptz --- 2001-10-10 01:04:54.965162+02 (1 row) peter=# select extract(timezone_hour from current_timestamp); date_part --- -2 (1 row) Plus or minus? peter=# select extract(timezone_hour from timestamp '2001-10-10 01:04:54.965162+02'); date_part --- -2 (1 row) (Same problem) peter=# select extract(timezone_hour from timestamp '2001-10-10 01:04:54.965162+03'); ^^ date_part --- -2 (1 row) Big problem. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Mule internal code ?
* Tatsuo Ishii [EMAIL PROTECTED] [011010 18:20]: As said in another mail, I have tried to add iso-8859-15 (Latin 9) iso-8859-16 (Latin 10) to PostgreSQL, I think I have done mostly all that's necessary. But I miss two things : ISO-8859-15 and 16! I don't know anything beyond ISO-8859-10. Can you give me any pointer (URL) explaining what they are? http://www.evertype.com/sc2wg3.html It links to files describing iso-8859-14 to 16. 14 is gaelic support, which I've never seen used (of course, I don't speak irish, so that's probably why :) ), and it has nothing to do with the euro. 15 is a modernised version of iso-8859-1. It removes some not-so-widely used characters (currency place-holder, fraction characters), to replace them with the euro sign, the french oe, OE, and Y diaeresis, and the finnish/estonian s/S caron and z/Z caron. That's the official 8-bit charset for western europe now (btw, the other name is latin9, or latin0, as it's supposed to replace iso8859-1, which is now what should be called a legacy encoding). 16 is quite new. It's supposed to do the same as iso-8859-15, but for central europe countries. It had support for the euro sign, the romanian language (t comma below, s comma below), but I've read somewhere that it has lost support for two or three other central europe countries... go figure... - latin92mic/mic2latin9/latin102mic/mic2latin10 in conv.c - the leading character value in pg_wchar.h I don't know anything about MULE except that it's some Emacs standard (why they didn't go for Unicode is beyond my understanding, is off-topic on this list, and had probably some good argument at the time). Probably this is because Unicode is not perfect at all. For example, the concept encode everything in two-bytes is obviously broken down, some charsets (for example JIS X 0213) are not supported at all, etc. etc... Well, for the history iso-10646 was 32 bits from the beginning, and Unicode didn't say that it was only 16 bits, though, to be fair, the Unicode consortium said it didn't believe it would need more than 16 bits. BTW, now, there is a statement that they wouldn't go above 0x10, which gives a bit more than 1 million characters... I think it should be enough this time (but who knows !?). Regarding the *main* issue with Unicode, which is support of japanese kanji vs chinese (in the CJK unification), I must admit I don't know the details, but arguments of both sides seem to be valid. I must admit I would say add the japanese version of the characters, since it's not lack of space which is the problem now. But things like this will get solved with time, and it really seems like Unicode will achieve the so much needed charset unity it's been made for :) Can someone point me to where I should look for that ? is it as easy as iso-8859-2/3/4 support, or do I need to do something as iso-8859-5 ? Docs for MULE internal code come with XEmacs. For example, see: ftp://ftp.xemacs.org/pub/xemacs/docs/letter/internals-letter.pdf.gz http://www.lns.cornell.edu/public/COMP/info/xemacs/internals/internals_15.html#SEC83 Unfortunately, these explain the principles behind mule, not the way to encode them from/to another character set :/ Patrice -- Patrice Hédé email: patrice hede à islande org www : http://www.islande.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Encoding issues
* Tatsuo Ishii [EMAIL PROTECTED] [011010 18:21]: Receiving a request to add ISO 8859-15 and 16, I review the multibyte support code and found several errors in it. 1) There is a confusion between LATIN5 and ISO 8859-5. LATIN5 is not ISO 8859-5, but is actually ISO 8859-9. Should we rename LATIN5 to ISO8859-5 (or whatever) as the encoding name? I think we should. For your information, here are the correct mapping between ISO 8859-n and LATINn. ISO 8859-1 LATIN1 ISO 8859-2 LATIN2 ISO 8859-3 LATIN3 ISO 8859-4 LATIN4 ISO 8859-9 LATIN5 ISO 8859-10 LATIN6 ISO-8859-14 LATIN 8 ISO-8859-15 LATIN 9 or LATIN 0 ISO-8859-16 LATIN 10 :) 2) The leading characters for some Cyrillic charsets are wrong. Currently they are defined as: #define LC_KOI8_R 0x8c/* Cyrillic KOI8-R */ #define LC_KOI8_U 0x8c/* Cyrillic KOI8-U */ #define LC_ISO8859_5 0x8d/* ISO8859 Cyrillic */ These should be: #define LC_KOI8_R 0x8b/* Cyrillic KOI8-R */ #define LC_KOI8_U 0x8b/* Cyrillic KOI8-U */ #define LC_ISO8859_5 0x8c/* ISO8859 Cyrillic */ The impact of correcting them would be for users who are storing their data into database using MULE internal code. I think they are quite few people using MULE internal code. So we could correct them for 7.2. Comments? BTW, should we support ISO 8859-6 and beyond for 7.2? There have been some requests to do that. Supporting them are actually trivial works, should be one day job. The harder part is writing conversion function between encodings. However, there is very few demands to do that, I guess. If so, we could ommit the conversion capability for 7.2. Comments? I think iso-8859-15 and 16 are important, if only because they are the only two encodings which support the Euro (not speaking of unicode, of course !), and at least iso-8859-15 has some official status in western europe (on Unix systems at least... Windows users have their own table where the Euro sign is stored somewhere else, I think at 0x80). I have done the conversion for the mappings to and from unicode, but you could get the original tables at : http://www.unicode.org/Public/MAPPINGS/ISO8859/ (you can get iso-8859-10, 13 and 14 there as well ! 10 is supposed to be for greenlandic and sámi, 13 for the baltic rim, and 14 for gaelic) Just found on google the following link, where you can see quite a few charsets (it doesn't have -16, too new probably) : http://www.kostis.net/charsets/ Patrice -- Patrice Hédé email: patrice hede à islande org www : http://www.islande.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] iso-8859-15/16 to MULE
I've been looking a bit at the MULE encoding wrt to latin 9 and 10. It seems that there is no support for the Euro at all in it. e.g. when I tried to use recode, which does recognise iso-8859-15 and 16, and convert to MULE, whatever I do, I obtain EUR for the euro sign, OE, oe, s, S, z, Z, Y for the different characters which are specific to 15 for example, and that's even worse for 16. Should we NOT allow conversion to Mule, or restrict the support, for example by pretending iso-8859-15 is iso-8859-1 (resp. 16 is 2) for conversion from/to mule (i.e. use the 0x81 and 0x82 octet for these encodings) and be done with it ?? (and MENTION it in the docs ;) ). Anyway, I don't see somebody wanting support for the euro using Mule to store its strings... UTF-8 is much more important (and straightforward) to support in that case :) What do you think ? Patrice. -- Patrice Hédé email: patrice hede à islande org www : http://www.islande.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] How to add a new encoding support?
Hi, I need to use some Chinese characters in charset MS950(CP950) but not in Big5. Big5 and MS950 encoding are very much similiar but currently there is no support for MS950 and I will need to add it. I've read files in src/backend/utils/mb directory but still not sure what files to modify. Or can I just replace the Big5 mapping? Thanks for your help. -- Regards, Zhenbang Wei [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ALTER RENAME and indexes
Of course, in 7.1 foreign key constraints become rather confused when you rename columns on them. create table parent (id serial); create table child (id int4 references parent(id) on update cascade); alter table parent rename column id to anotherid; alter table child rename column id to junk; insert into child values (1); - ERROR: constraint unnamed: table child does now have an attribute id -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: Brent Verner [EMAIL PROTECTED] To: pgsql-hackers [EMAIL PROTECTED] Sent: Saturday, October 06, 2001 7:49 PM Subject: Re: [HACKERS] ALTER RENAME and indexes On 05 Oct 2001 at 10:18 (-0400), Brent Verner wrote: | On 05 Oct 2001 at 09:46 (-0400), Tom Lane wrote: | | Brent Verner [EMAIL PROTECTED] writes: | | 'ALTER TABLE tbl RENAME col1 TO col2' does not update any indices that | | reference the old column name. | | | | It doesn't need to; the indexes link to column numbers, not column | | names. ah, I think I see the problem... The pg_attribute.attname just needs updating, right? I suspect this after noticing that the pg_get_indexdef(Oid) function produced the correct(expected) results, while those using pg_attribute were wrong. If this is the _wrong_ answer for this, stop me before I make a big mess :-) working... b -- Develop your talent, man, and leave the world something. Records are really gifts from people. To think that an artist would love you enough to share his music with anyone is a beautiful thing. -- Duane Allman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Patch for OSX 10.1 and Postgresql 7.3.1
Hi, It apears that getting Postgres and OSX 10.1 to work is not just a case of some compiler flags. I have attached a patch, not sure who wrote this patch, but it seems to work for me! I am asuming that the author has submitted it to the pgsql team, but if not here it is. Have fun, Serge P.S. I give NO guarantees, like I said... I did not write this! cut diff -ru postgresql-7.1.3/src/Makefile.shlib postgresql-7.1.3-posix/src/Makefile.shlib --- postgresql-7.1.3/src/Makefile.shlib Sun Apr 15 05:25:07 2001 +++ postgresql-7.1.3-posix/src/Makefile.shlib Wed Sep 19 23:00:08 2001 @@ -113,7 +113,7 @@ ifeq ($(PORTNAME), darwin) shlib := lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) - LINK.shared= $(COMPILER) $(CFLAGS_SL) + LINK.shared= $(COMPILER) endif ifeq ($(PORTNAME), openbsd) diff -ru postgresql-7.1.3/src/backend/storage/ipc/ipc.c postgresql-7.1.3-posix/src/backend/storage/ipc/ipc.c --- postgresql-7.1.3/src/backend/storage/ipc/ipc.cFri Mar 23 05:49:54 2001 +++ postgresql-7.1.3-posix/src/backend/storage/ipc/ipc.c Wed Sep 19 23:09:06 2001 @@ -29,10 +29,20 @@ #include sys/types.h #include sys/file.h +#define POSIX_SHARED_MEMORY +#ifdef POSIX_SHARED_MEMORY +#include sys/stat.h +#include sys/mman.h +#endif #include errno.h #include signal.h #include unistd.h +#ifdef POSIX_SHARED_MEMORY +#define IpcMemoryId unsigned int +#define IpcMemoryKey unsigned int +#endif + #include storage/ipc.h #include storage/s_lock.h /* In Ultrix, sem.h and shm.h must be included AFTER ipc.h */ @@ -77,6 +87,13 @@ static void *PrivateMemoryCreate(uint32 size); static void PrivateMemoryDelete(int status, Datum memaddr); +#ifdef POSIX_SHARED_MEMORY +uint32 posix_shmget(uint32 key, uint32 size, int permissions); +void *posix_shmat(uint32 id); +uint32 posix_shm_count(uint32 id); +void decrement_posix_shm_count(void *address); +int posix_shmrm(uint32 id); +#endif /* * exit() handling stuff @@ -265,6 +282,9 @@ * print out an error and abort. Other types of errors are not recoverable. * */ +#ifdef POSIX_SHARED_MEMORY +#define shmget(a, b, c) posix_shmget(a,b,c) +#endif static IpcSemaphoreId InternalIpcSemaphoreCreate(IpcSemaphoreKey semKey, int numSems, int permission, @@ -620,7 +640,11 @@ on_shmem_exit(IpcMemoryDelete, Int32GetDatum(shmid)); /* OK, should be able to attach to the segment */ +#ifdef POSIX_SHARED_MEMORY + memAddress = posix_shmat(shmid); +#else memAddress = shmat(shmid, 0, 0); +#endif if (memAddress == (void *) -1) { @@ -646,10 +670,13 @@ static void IpcMemoryDetach(int status, Datum shmaddr) { +#ifndef POSIX_SHARED_MEMORY if (shmdt(DatumGetPointer(shmaddr)) 0) fprintf(stderr, IpcMemoryDetach: shmdt(%p) failed: %s\n, DatumGetPointer(shmaddr), strerror(errno)); - +#else + decrement_posix_shm_count(DatumGetPointer(shmaddr)); +#endif /* * We used to report a failure via elog(NOTICE), but that's pretty * pointless considering any client has long since disconnected ... @@ -663,10 +690,13 @@ static void IpcMemoryDelete(int status, Datum shmId) { +#ifdef POSIX_SHARED_MEMORY + if (posix_shmrm(DatumGetInt32(shmId)) == -1) +#else if (shmctl(DatumGetInt32(shmId), IPC_RMID, (struct shmid_ds *) NULL) 0) +#endif fprintf(stderr, IpcMemoryDelete: shmctl(%d, %d, 0) failed: %s\n, DatumGetInt32(shmId), IPC_RMID, strerror(errno)); - /* * We used to report a failure via elog(NOTICE), but that's pretty * pointless considering any client has long since disconnected ... @@ -679,8 +709,9 @@ bool SharedMemoryIsInUse(IpcMemoryKey shmKey, IpcMemoryId shmId) { +#ifndef POSIX_SHARED_MEMORY struct shmid_ds shmStat; - +#endif /* * We detect whether a shared memory segment is in use by seeing * whether it (a) exists and (b) has any processes are attached to it. @@ -689,6 +720,9 @@ * nonexistence of the segment (most likely, because it doesn't belong * to our userid), assume it is in use. */ +#ifdef POSIX_SHARED_MEMORY + return (posix_shm_count(DatumGetInt32(shmId)) != 0); +#else if (shmctl(shmId, IPC_STAT, shmStat) 0) { @@ -706,6 +740,7 @@ if (shmStat.shm_nattch != 0) return true; return false; +#endif } @@ -801,9 +836,17 @@ shmid = shmget(NextShmemSegID, sizeof(PGShmemHeader), 0); if (shmid 0)
Re: [pgadmin-hackers] [HACKERS] What about CREATE OR REPLACE FUNC
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 08 October 2001 15:13 To: Dave Page Cc: 'Jean-Michel POURE'; [EMAIL PROTECTED]; Bruce Momjian; [EMAIL PROTECTED] Subject: Re: [pgadmin-hackers] [HACKERS] What about CREATE OR REPLACE FUNC TION? Dave Page [EMAIL PROTECTED] writes: ... I can't find an up-to-date snapshot Where have you looked? I checked a couple of FTP mirrors at random and see up-to-date snapshots, eg at ftp://ftp.us.postgresql.org/dev/ ftp://postgresql.wavefire.com/pub/dev/ ftp://postgresql.rmplc.co.uk/pub/postgresql/dev/ all of which have snapshots dated Sun Oct 7 08:02:00 2001 as I write. I tried postgresql.rmplc.co.uk and got one (apparently) dated 7 Oct, however CREATE OR REPLACE FUNCTION didn't seem to be there (it certainly doesn't work anyway - syntax error at OR). I then looked in the primary copy on mail.postgresql.org and found the copy there was dated 30 Sept from which I assumed that the 07/10/2001 date on rm's copy was actually a US date - that site has been seriously out of date before. and I don't know the magic that has to be worked on the PostgreSQL CVS version of the configure script in order to make it run without barfing. News to me that it requires any magic at all; I use it almost daily without problems. Why doesn't it work for you? I've tried it a few times and I always get something like: root@tux1:/usr/local/src/pgsql# ./configure su: ./configure: bad interpreter: No such file or directory root@tux1:/usr/local/src/pgsql# sh ./configure : command not found : command not found : command not found : command not found : command not found '/configure: line 127: syntax error near unexpected token `do '/configure: line 127: `do root@tux1:/usr/local/src/pgsql# I always assumed that something is done when the tarballs are built as the work just fine on the same machine. The only odd thing I can think of is that my copy of the source is maintained on my PC using WinCVS and was zipped/ftp'd onto a test box. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Mule internal code ?
Hi, As said in another mail, I have tried to add iso-8859-15 (Latin 9) iso-8859-16 (Latin 10) to PostgreSQL, I think I have done mostly all that's necessary. But I miss two things : - latin92mic/mic2latin9/latin102mic/mic2latin10 in conv.c - the leading character value in pg_wchar.h I don't know anything about MULE except that it's some Emacs standard (why they didn't go for Unicode is beyond my understanding, is off-topic on this list, and had probably some good argument at the time). Can someone point me to where I should look for that ? is it as easy as iso-8859-2/3/4 support, or do I need to do something as iso-8859-5 ? Thank you :) Patrice. -- Patrice HÉDÉ --- patrice à islande org - -- Isn't it weird how scientists can imagine all the matter of the universe exploding out of a dot smaller than the head of a pin, but they can't come up with a more evocative name for it than The Big Bang ? -- What would _you_ call the creation of the universe ? -- The HORRENDOUS SPACE KABLOOIE ! - Calvin and Hobbes -- http://www.islande.org/ - ---(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: Temprary issue gripes(was:Re: [HACKERS] cvs problem)
On Monday 08 October 2001 09:37 pm, John Summerfield wrote: I don't see for whom the long name would be a problem; certainly if it has been that way for five years, it couldn't have been a serious problem. Ask Marc why he changed it. Correction: Marc Fournier controls the entire disk layout, as it's his server. It was his decision to change the layout. Is Marc part of the team? Reference the developers listing on developer.postgresql.org. yes or no? I don't have web access at present. He contributes to discussions, so I guess in at least some sense he is. He is one of the six core developers; maintains the postgresql.org server (which he donates); maintains the network bandwidth which we all enjoy; coordinates releases; runs the mailing list, ftp, web, CVS, CVSup, and nesgroup services; is President of PostgreSQL, Inc, who provides first-rate commercial support for PostgreSQL; is chief cook and bottlewasher; and anything else I may have left out. He is one of the first four who took the also-ran Postgres95 and turned it into the real database known as PostgreSQL. So, yes, I guess you could say he's part of the team... :-) Instead of telling me how to go on with my affairs, there ensured a discussion about the documentation being wrong, about the devlopers corner shouldn't really be there and so on. Because your report was a symptom of a larger problem -- that of the automatically generated pages not generating properly. Fix the cause, not the symptom. No reason at all to make people wait for thich incantation. Someone had the correct information. Probably a minute to find it and incorproate it in a response. Did you or did you not post the question to pgsql-hackers? This list isn't for just telling people how to solve problems -- that is what admin, general, ports, etc are for. The hackers list is the developers list, where the developers talk through development problems. So, directly answering your question wasn't the top priority -- fixing the larger problem was. However, I do need reasonable support from the developers, and I was only seeking a a modest amount of support. If you're going to run CVS or even beta versions, you had better be ready to do alot of your own support. I'm not trying to be mean or arrogant, either -- if a change in CVSROOT and a lack of docs is too upsetting, wait on the final release, or a release candidate, where things are much more polished. Bleeding edge sometimes cuts -- and I've been there. I don't ordinarily have web access. Archives are inconvenient. And, in my experience, somewhat hard to use. It can be hard to find a specific topic - too many synomyms - and often they're too voluminous, and unless you have a high-bandwidth service (I don't) slow. I can sympathize to an extent with that, but I again have to go back to what irked me -- you made an uninformed critical remark that had nothing to do with your question. Don't make critical remarks about a process or project of which workings you are ignorant. That isn't meant to be demeaning -- I try to follow that very same advice, as it was given to me long ago by none other than Jonathan Kamens. And it takes more than just a couple of weeks reading the list to get familiar with the workingsof a project this size. That's really all I have to say about that on-list. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] FAQ error
Our FAQ, item 4.16.2 has: $newSerialID = nextval('person_id_seq'); INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal'); Is this correct Perl? I don't see a nextval() function in Perl. Can you call SQL server-side functions natively from Perl? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FAQ error
On 10 Oct 2001 at 17:12 (-0400), Bruce Momjian wrote: | | Our FAQ, item 4.16.2 has: | | $newSerialID = nextval('person_id_seq'); | INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal'); | | Is this correct Perl? I don't see a nextval() function in Perl. Can | you call SQL server-side functions natively from Perl? no. The proper perl code would be more like... use DBI; my ($lastid,$nextid,$sql,$rv); my $dbh = DBI-connect(perldoc DBD::Pg); # to use the nextval $sql = SELECT nextval('person_id_seq'); $nextid = ($dbh-selectrow_array($sql))[0]; $sql = INSERT INTO person (id, name) VALUES ($nextid, 'Blaise Pascal'); $rv = $dbh-do($sql); # or to get the currval $sql = INSERT INTO person (name) VALUES ('Blaise Pascal'); $rv = $dbh-do($sql); $sql = SELECT currval('person_id_seq'); $lastid = ($dbh-selectrow_array($sql))[0]; | -- | Bruce Momjian| http://candle.pha.pa.us | [EMAIL PROTECTED] | (610) 853-3000 | + If your life is a hard drive, | 830 Blythe Avenue | + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 | | ---(end of broadcast)--- | TIP 6: Have you searched our list archives? | | http://archives.postgresql.org -- Develop your talent, man, and leave the world something. Records are really gifts from people. To think that an artist would love you enough to share his music with anyone is a beautiful thing. -- Duane Allman ---(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] Mule internal code ?
ISO-8859-15 and 16! I don't know anything beyond ISO-8859-10. Can you give me any pointer (URL) explaining what they are? http://www.evertype.com/sc2wg3.html It links to files describing iso-8859-14 to 16. [snip] Thanks for the info. Well, for the history iso-10646 was 32 bits from the beginning, and Unicode didn't say that it was only 16 bits, though, to be fair, the Unicode consortium said it didn't believe it would need more than 16 bits. BTW, now, there is a statement that they wouldn't go above 0x10, which gives a bit more than 1 million characters... I think it should be enough this time (but who knows !?). Regarding the *main* issue with Unicode, which is support of japanese kanji vs chinese (in the CJK unification), I must admit I don't know the details, but arguments of both sides seem to be valid. I must admit I would say add the japanese version of the characters, since it's not lack of space which is the problem now. But things like this will get solved with time, and it really seems like Unicode will achieve the so much needed charset unity it's been made for :) IMHO we should not rely on particular encodings/charsets, including Unicode (or ISO 10646), MULE internal code or whatever. My plan for supporting CREATE CHARCTER SET etc. stuffs would be truly *neutral* to any encodings/charsets. Can someone point me to where I should look for that ? is it as easy as iso-8859-2/3/4 support, or do I need to do something as iso-8859-5 ? Docs for MULE internal code come with XEmacs. For example, see: ftp://ftp.xemacs.org/pub/xemacs/docs/letter/internals-letter.pdf.gz http://www.lns.cornell.edu/public/COMP/info/xemacs/internals/internals_15.html#SEC83 Unfortunately, these explain the principles behind mule, not the way to encode them from/to another character set :/ Please take look at 15.3.1 Internal String Encoding. -- Tatsuo Ishii ---(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] iso-8859-15/16 to MULE
e.g. when I tried to use recode, which does recognise iso-8859-15 and 16, and convert to MULE, whatever I do, I obtain EUR for the euro sign, OE, oe, s, S, z, Z, Y for the different characters which are specific to 15 for example, and that's even worse for 16. Apparently MULE currently does not support beyond ISO 8859-10 at all. Should we NOT allow conversion to Mule, or restrict the support, for example by pretending iso-8859-15 is iso-8859-1 (resp. 16 is 2) for conversion from/to mule (i.e. use the 0x81 and 0x82 octet for these encodings) and be done with it ?? (and MENTION it in the docs ;) ). I think that we could negelect MULE encoding support for beyond ISO 8859-10, at least untill MULE officially support them. Anyway, I don't see somebody wanting support for the euro using Mule to store its strings... UTF-8 is much more important (and straightforward) to support in that case :) What do you think ? Well, the conversion to/from UTF-8 for ISO 8859-10 or later is pretty easy and should be supported, I think. Actually I already have generated mapping tables for these charsets. I will make patches against current and leave it for the core's decision, whether it should be included in 7.2 or not. -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TOAST and TEXT
Chris Bitmead [EMAIL PROTECTED] writes: ... I don't like the old large object implementation, I need to store very large numbers of objects and unless this implementation has changed in recent times it won't cut it. Have you looked at 7.1? AFAIK it has no particular problem with lots of LOs. Which is not to discourage you from going over to bytea fields instead, if that model happens to be more convenient for your application. But your premise above seems false. 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] pg_dump oid problems
steve [EMAIL PROTECTED] writes: When trying to pg_dump on 7.1.2 ( 7.1.3) I get the following error message: bash-2.04$ pg_dump dwh getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp. Explanation from backend: ERROR: dtoi4: integer out of range Several of my tables have very large OIDs (over 4 billion in some cases Hmm. Okay, I think I can see how over-2-gig OIDs might lead to that error message, but that doesn't really help in tracking down the specific location of the problem. Could you run pg_dump after doing export PGOPTIONS=-d2 so that its queries get sent to the postmaster log? Then looking at the log to see the last couple of queries before the failure should tell us. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] iso-8859-15/16 to MULE
Tatsuo Ishii [EMAIL PROTECTED] writes: Well, the conversion to/from UTF-8 for ISO 8859-10 or later is pretty easy and should be supported, I think. Actually I already have generated mapping tables for these charsets. I will make patches against current and leave it for the core's decision, whether it should be included in 7.2 or not. If you are comfortable with these patches then apply them. You know more about multibyte issues than any of the core committee... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Patch for OSX 10.1 and Postgresql 7.3.1
Serge Sozonoff [EMAIL PROTECTED] writes: I have attached a patch, not sure who wrote this patch, but it seems to work for me! I am asuming that the author has submitted it to the pgsql team, but if not here it is. It has not been submitted, and it certainly won't get accepted as-is (it appears to unconditionally insert Darwin-specific code into ipc.c, and even without that I'm leery of applying patches from unknown sources). Please find the author and ask him to contact us. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] row value constructor bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: In my understanding below row value constructors(I hope this term is correct) exaples should return true, but PostgreSQL does not. By my reading, a row value constructor is one of the things in parentheses, while the whole clause is a comparison predicate (per section 8.2 of SQL92). But I agree that we don't seem to have implemented the semantics correctly. The code currently responsible for this is makeRowExpr() in gram.y ... I tend to agree with the comment on it that says that the functionality should be pushed deeper ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Suitable Driver ?
HI I have to setup PERL to interact with PGSQL. I have taken the following steps. 1.Installation of perl_5.6.0 under Redhat Linux 7.0 2.Installation of POSTGRESQL under Redhat Linux7.0 Both are working perfectly as seperate modules. Now I need to interface perl with PGSQL. I need to what's the best possible soln. I have installed latest DBI from www.cpan.org Now i need to install DBD For PGSQL .Is this the driver i have to work on for pgsql ?. Or do I have any other option to connect to pgsql from perl . Indeed i've found out an other way to use Pg driver provided by PGSQL to interface perl with pgsql. I need to exactly know the difference between use Pg ; and use DBI ; Need to which one is proceeding towards correct direction under what circumstances. when I tried to install DBD-Pg-0.93.tar.gz under Linux i get Configuring Pg Remember to actually read the README file ! please set environment variables POSTGRES_INCLUDE and POSTGRES_LIB ! I need to know what these varibles POSTGRES_INCLUDE and POSTGRES_LIB should point to ... and when i tried to run perl test.pl, the program to test the installation of the module which comes with the tar. I get the error OS: linux install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC (@INC contains: /usr/l ib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/i3 86-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::Pg perl module hasn't been fully installed, or perhaps the capitalisation of 'Pg' isn't right. Available drivers: ADO, ExampleP, Multiplex, Proxy. at test.pl line 51 Any body who can clarify is most welcome with regards, Prassanna... ---(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] Encoding issues
On Wed, Oct 10, 2001 at 03:40:25PM +0900, Tatsuo Ishii wrote: Receiving a request to add ISO 8859-15 and 16, I review the multibyte support code and found several errors in it. 1) There is a confusion between LATIN5 and ISO 8859-5. LATIN5 is not ISO 8859-5, but is actually ISO 8859-9. Should we rename LATIN5 to ISO8859-5 (or whatever) as the encoding name? I think we should. For your information, here are the correct mapping between ISO 8859-n and LATINn. ISO 8859-1 LATIN1 ISO 8859-2 LATIN2 ISO 8859-3 LATIN3 ISO 8859-4 LATIN4 ISO 8859-9 LATIN5 ISO 8859-10LATIN6 You are right. Now I see some old version of PostgreSQL and there is this confusion in some headers and comments too. 2) The leading characters for some Cyrillic charsets are wrong. Currently they are defined as: #define LC_KOI8_R 0x8c/* Cyrillic KOI8-R */ #define LC_KOI8_U 0x8c/* Cyrillic KOI8-U */ #define LC_ISO8859_5 0x8d/* ISO8859 Cyrillic */ These should be: #define LC_KOI8_R 0x8b/* Cyrillic KOI8-R */ #define LC_KOI8_U 0x8b/* Cyrillic KOI8-U */ #define LC_ISO8859_5 0x8c/* ISO8859 Cyrillic */ Again, it's long time in sources too (interesting is that we don't understand some bugreport). The impact of correcting them would be for users who are storing their data into database using MULE internal code. I think they are quite few people using MULE internal code. So we could correct them for 7.2. Comments? I agree with you, make release with know bugs is dirty thing. BTW, should we support ISO 8859-6 and beyond for 7.2? There have been some requests to do that. Supporting them are actually trivial works, should be one day job. The harder part is writing conversion function between encodings. However, there is very few demands to do that, I guess. If so, we could ommit the conversion capability for 7.2. Comments? You will hear we are in the feature freeze state.. :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Setting Password
You need to change the pg_hba.conf file in your PostgreSQL installation so that password authentication is used. Check out: http://www.postgresql.org/idocs/index.php?client-authentication.html for details. Hope that helps, Mike Mascari [EMAIL PROTECTED] Balaji Venkatesan wrote: Hi List, Iam pretty new to this list as well as PostgreSQL. I hope to find some crucial info from here. Thnx in advance to all those who would contribute to it. Iam basically an Oracle Consultant. At first i would like to clarify how to enforce password for a user i have created. I use the psql client to access the database and unless and until the -U option (psql template1 -U user ) is used, iam not prompted to enter any password. Even thou i enter a wrong password iam still allowed to log in. Is there any property needs to be altered to enforce the same ? Looking forward for some favourable responses. Regards Balaji ---(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] Unhappiness with forced precision conversion
We use timestamps and intervals quite a bit in our applications. We also use several different databases. Unfortunately, the time/date/ interval area is one that is not at all consistent between databases. It makes life particularly difficult when trying to re-use application code. So far, as compared to many other databases, PostgreSQL, remains pretty close to the standard (at least for our projects). The only areas that we have had issues with is the default inclusion of the timezone information when retriving the timestamp information and the slightly non-standard interval literal notation (i.e., including the year-month or day-time interval information inside the single quotes with the literal string). My vote on all datetime questions is to stick strictly to the standard. Of course sticking to the standard is not always easy as the standard is not always clear or even consistent. (I'm only familiar with ANSI 92 not ANSI 99.) Time zones in particular seem to be problematic. In this case, I believe that it would be preferable to stick with the TIME(0) and TIMESTAMP(6) default precision. In our applications, we always specify the precision, so, the default precision is not a real concern for us, however, for portability, I still suggest sticking with the standard. Thanks, F Harvell On Thu, 04 Oct 2001 20:30:24 -, Thomas Lockhart wrote: The code asserts that SQL99 requires the default precision to be zero, but I do not agree with that reading. What I find is in 6.1: 30) If time precision is not specified, then 0 (zero) is implicit. If timestamp precision is not specified, then 6 is implicit. so at the very least you'd need two different settings for TIME and TIMESTAMP. But we don't enforce the spec's idea of default precision for char, varchar, or numeric, so why start doing so with timestamp? Sure, I'd forgotten about the 6 vs 0 differences. Easy to put back in. One of course might wonder why the spec *makes* them different. Why start doing so with timestamp?. SQL99 compliance for one thing ;) I'm not sure I'm comfortable with the spec behavior, but without a discussion I wasn't comfortable implementing it another way. Essentially, what I want is for gram.y to set typmod to -1 when it doesn't see a (N) decoration on TIME/TIMESTAMP. I think everything works correctly after that. ... works correctly... == ... works the way we'd like Right? This is the start of the discussion I suppose. And I *expected* a discussion like this, since SQL99 seems a bit ill-tempered on this precision business. We shouldn't settle on a solution with just two of us, and I guess I'd like to hear from folks who have applications (the larger the better) who would care about this. Even better if their app had been running on some *other* DBMS. Anyone? - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Unhappiness with forced precision conversion
On Fri, 05 Oct 2001 19:35:48 -, Thomas Lockhart wrote: ... Have you actually used ANSI SQL9x time zones? istm that one offset fits all is really ineffective in supporting real applications, but I'd like to hear about how other folks use it. Fortunately, most of our date/time information is self-referential. I.e., we are usually looking at intervals between an initial date/ timestamp and the current date/timestamp. This has effectively eliminated the need to deal with time zones. In this case, I believe that it would be preferable to stick with the TIME(0) and TIMESTAMP(6) default precision. In our applications, we always specify the precision, so, the default precision is not a real concern for us, however, for portability, I still suggest sticking with the standard. We are likely to use the 0/6 convention for the next release (though why TIME should default to zero decimal places and TIMESTAMP default to something else makes no sense). The only thing that I can think of is that originally, the DATE and TIME types were integer values and that when the new TIMESTAMP data type was created the interest was to increase the precision. I would guess, as you have also suggested, that the standards were based upon existing implementations (along with an interest in backwards compatibility). Thanks, F Harvell ---(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: [pgadmin-hackers] [HACKERS] What about CREATE OR REPLACE FUNC
-Original Message- From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] Sent: 08 October 2001 14:43 To: [EMAIL PROTECTED] Cc: Tom Lane; Bruce Momjian; [EMAIL PROTECTED] Subject: Re: [pgadmin-hackers] [HACKERS] What about CREATE OR REPLACE FUNCTION? Dear all, 1) CREATE OR REPLACE FUNCTION In pgAdmin II, we plan to use the CREATE OR REPLACE FUNCTION if the patch is applied. Do you know if there is any chance it be applied for beta time? We would very much appreciate this feature... It's already done in pgAdmin CVS (committed this morning) and I believe Bruce committed the patch to PostgreSQL on 2nd October. I just haven't tested it yet as I can't find an up-to-date snapshot and I don't know the magic that has to be worked on the PostgreSQL CVS version of the configure script in order to make it run without barfing. 2) PL/pgSQL default support It is sometimes tricky for Windows users to install a language remotely on a Linux box (no access to createlang and/or no knowledge of handlers). So why not enable PL/pgSQL by default? 2nd 'ed! Regards, Dave. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Problem in pg_dump 7.1.2 dump order
Hello, In dump file statement which grants permissions on view exists before statement which create view. For tables and sequences permissions dumped in correct order. --TOC Entry ID 124 (OID 150248) GRANT ALL on my_view to group sales; ... skipped --TOC Entry ID 123 (OID 194103) CREATE VIEW my_view ... Any comments? ---(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] Postgres server locks up, HELP!
Tom, Bruce, any suggestions? [EMAIL PROTECTED] (Ryan) wrote in message news:[EMAIL PROTECTED]... I changed the number of shared buffers to 3000 and my database locks on a simple query. I must kill the database with pg_ctl stop -m i. Neither smart nor fast stops appear to succeed. One CPU gets pinned. When I set the number of shared buffers to 64 everything is fine. No data appears to have been corrupted, but I haven't been able to do a thorough check. I really didn't have a chance to do much of a postmortem. I only have what I can get from my logs: - schema - query - query plan - vacuum results - postgres log If anybody wants more data I can reproduce the problem. It is very repeatable. Also, I prefer to discover what went wrong rather than simply upgrade to 7.1.3 and hope for the best. None of the fixes/enhancements listed in 7.1.3 seem relevant to this problem. I am running postgres 7.1.2 on Solaris 5.7 E450, 4 processors, 2 gig ram. I added the following lines to /etc/system: set shmsys:shminfo_shmmax=0x1000 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=256 set shmsys:shminfo_shmseg=256 set semsys:seminfo_semmap=256 set semsys:seminfo_semmni=512 set semsys:seminfo_semmsl=32 set semsys:seminfo_semmns=512 set rlim_fd_max=65535 set rlim_fd_cur=65535 Here is the query that locks the DB: select i8, i5, count(*), sum( float8mi(d2,d1) ), sum(i9)::int4 as doll from calls_1001548800 group by i5, i8; i8 has about 5 unique values. i5 has two unique values (boolean) plus NULL. The query was entered into psql by hand. Here is the table def: CREATE TABLE calls_1001548800 ( pk int4 primary key, t1 text NOT NULL, t2 text NOT NULL, i1 int4 NOT NULL, i2 int4 NOT NULL, d1 double precision NOT NULL, d2 double precision NOT NULL, d3 double precision NOT NULL, d4 double precision NOT NULL, i3 int4 NOT NULL, d5 real NOT NULL, i4 int4 NOT NULL, t3 text, i5 boolean, i6 int4 NOT NULL, i7 int4 NOT NULL, i8 int2 NOT NULL, i9 int4, t4 text ); CREATE INDEX calls_1001548800_d21 on calls_1001548800 ( float8mi(d2,d1) ); CREATE INDEX calls_1001548800_i3 on calls_1001548800 ( i3 ); CREATE INDEX calls_1001548800_i9 on calls_1001548800 ( i9 ); Vacuum of the table (before the query): 5681:DEBUG: --Relation calls_1001548800-- 5682:DEBUG: Pages 915: Changed 54, reaped 559, Empty 0, New 0; Tup 33842: Vac 1077, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 1 42, MaxLen 648; Re-using: Free/Avail. Space 279912/279912; EndEmpty/Avail. Pages 0/559. CPU 0.00s/0.11u sec. 5683:DEBUG: Index calls_1001548800_pkey: Pages 179; Tuples 33842: Deleted 1077. CPU 0.00s/0.66u sec. 5684:DEBUG: Index calls_1001548800_d21: Pages 95; Tuples 33842: Deleted 1077. CPU 0.00s/0.70u sec. 5685:DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES 5686:DEBUG: Index calls_1001548800_i3: Pages 98; Tuples 33842: Deleted 1077. CPU 0.39s/0.78u sec. 5687:DEBUG: Index calls_1001548800_i9: Pages 100; Tuples 33842: Deleted 1077. CPU 0.03s/0.66u sec. 5688:DEBUG: Rel calls_1001548800: Pages: 915 -- 886; Tuple(s) moved: 1122. CPU 0.00s/1.16u sec. 5689:DEBUG: Index calls_1001548800_pkey: Pages 179; Tuples 33842: Deleted 1122. CPU 0.00s/0.38u sec. 5690:DEBUG: Index calls_1001548800_d21: Pages 95; Tuples 33842: Deleted 1122. CPU 0.00s/0.40u sec. 5691:DEBUG: Index calls_1001548800_i3: Pages 102; Tuples 33842: Deleted 1122. CPU 0.01s/0.39u sec. 5692:DEBUG: Index calls_1001548800_i9: Pages 104; Tuples 33842: Deleted 1122. CPU 0.00s/0.40u sec. Here is a snippet of the log file showing the query plan and the events after the query. 7553:NOTICE: QUERY PLAN: 7554: 7555:Aggregate (cost=3770.44..4108.86 rows=3384 width=19) 7556: - Group (cost=3770.44..3939.65 rows=33842 width=19) 7557:- Sort (cost=3770.44..3770.44 rows=33842 width=19) 7558: - Seq Scan on calls_1001548800 (cost=0.00..1224.42 rows=33 842 width=19) 7559: 7560:Smart Shutdown request at Tue Oct 2 18:34:08 2001 7561:Fast Shutdown request at Tue Oct 2 18:35:03 2001 7562:Aborting any active transaction... 7563:FATAL 1: This connection has been terminated by the administrator. 7564:Immediate Shutdown request at Tue Oct 2 18:39:47 2001 7565:NOTICE: Message from PostgreSQL backend: 7566: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. 7567: I have rolled back the current transaction and am going to termina te your database system connection and exit. 7568: Please reconnect to the database system and repeat your query. 7569:DEBUG: database system was interrupted at 2001-10-02 18:11:06 PDT 7570:DEBUG: CheckPoint record at (39, 1811142680) 7571:DEBUG: Redo record at (39, 1811142680); Undo record at (0, 0); Shutdown FA LSE 7572:DEBUG:
Re: [HACKERS] TOAST and TEXT
Chris Bitmead [EMAIL PROTECTED] writes: ... I don't like the old large object implementation, I need to store very large numbers of objects and unless this implementation has changed in recent times it won't cut it. Have you looked at 7.1? AFAIK it has no particular problem with lots of LOs. Which is not to discourage you from going over to bytea fields instead, if that model happens to be more convenient for your application. But your premise above seems false. I'm storing emails, which as we know are usually small but occasionally huge. OK, I see in the release notes something like store all large objects in one table. and pg_dump of large objects. That sounds like maybe LOs are now ok, although for portability with Oracle blobs it would be nice if they could be embedded in any row or at least appear to be so from client interface side (Java client for what I'm doing). BTW, the postgres docs web pages says there is no limitation on row size. Someone should probably update that with the info given in the last few emails and probably integrate it in the regular doco as well. ---(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