Re: [GENERAL] ecpg problem
I just committed the attached small fix to CVS HEAD and the 8.3 branch. This should fix your problem. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! diff --exclude CVS -ru /home/postgres/pgsql-ecpg/preproc/type.c preproc/type.c --- /home/postgres/pgsql-ecpg/preproc/type.c 2007-12-21 15:33:20.0 +0100 +++ preproc/type.c 2008-03-02 11:49:11.0 +0100 @@ -259,7 +259,7 @@ ECPGdump_a_simple(o, name, type-u.element-type, - type-u.element-size, type-size, NULL, prefix, type-lineno); + type-u.element-size, type-size, NULL, prefix, type-u.element-lineno); if (ind_type != NULL) { ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Understanding ps -ef command column
Andrej Ricnik-Bay wrote: On 23/02/2008, David Jaquay [EMAIL PROTECTED] wrote: When I do a ps -ef, in the command column, I see: postgres: postgres dbname 10.170.1.60(57413) idle This doesn't resemble any ps -ef output I've ever seen. What OS is this on, what's the version of ps? I had forgotten we showed the remote port number for TCP connections, but I see it here: postgres 13651 8991 0 7:26AM ?? 0:00.01 postgres test 127.0.0.1(57352) idle (postmaster) and it seems we have been doing it for years. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Design suggestion of multiple databases vs multiple schemas within the database
I am building a web app with Postgres, that also uses Drupal with Postgres. I am new to all these frameworks. There is some data that I'll need to cross-reference between the two databases. Can I do a cross-schema/catalog join? Or is a cross-database join better? Are there any gotchas for the cross-schema/cross-database joins that I need to be aware of? Such as when using Shared hosting? Any suggestions/comments appreciated. Thanks
[GENERAL] 8.2.6 8.3 blows up
Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. Its not clear how I can have both versions running at once side-by-side; this is a production system and I can get a tee of the blowup and post it, but to do that I need to be able to start both versions at once. both ARE loaded on the system; is there a way to do that? Thanks in advance -- Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Design suggestion of multiple databases vs multiple schemas within the database
On Sun, Mar 2, 2008 at 1:54 PM, Swaminathan Saikumar [EMAIL PROTECTED] wrote: I am building a web app with Postgres, that also uses Drupal with Postgres. I am new to all these frameworks. There is some data that I'll need to cross-reference between the two databases. Can I do a cross-schema/catalog join? Or is a cross-database join better? Are there any gotchas for the cross-schema/cross-database joins that I need to be aware of? Such as when using Shared hosting? pgsql doesn't really support cross db queries, so you should use multiple schemas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.6 8.3 blows up
On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] wrote: Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. So what error message? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Unicode comment on Postgres vs Sql Server
I am familiar with MS Sql Server just started using Postgres. For storing Unicode, Sql Server uses nvarchar/char for unicode, and uses char/varchar for ASCII. Postgres has this encoding setting at the database level. I am using UTF8 Unicode for most of my data, but there is some data that I know for sure will be ASCII. However, this is also stored as UTF8, using up more space. At first sight, it looks like the the more granular level design is better. Any comments? If you agree, does it make sense to add this as a new datatype to Postgres? Thanks
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
On Sun, Mar 02, 2008 at 11:50:01AM -0800, Swaminathan Saikumar [EMAIL PROTECTED] wrote a message of 30 lines which said: Postgres has this encoding setting at the database level. Which is simpler, IMHO. One encoding to rule them all I am using UTF8 Unicode for most of my data, but there is some data that I know for sure will be ASCII. However, this is also stored as UTF8, using up more space. Excuse me, but this shows a serious ignorance of UTF-8. A character of the ASCII range, in UTF-8, is stored in one byte, exactly the same size as ASCII (any ASCII file is an UTF-8 file, that's an important property of UTF-8). ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.6 8.3 blows up
A whole host of them, mostly about bad data formats in some of the table data. I suspect the underlying problem is that something got mangled in the table creates. I'm setting up on a different box as my attempt to create a second instance failed horribly - compiling with a different prefix and port number appears to work but when you do an initdb it blows up with a complaint about not being able to create the semaphores and shared segment. It appears the port number is used for the SEMID and SHMID prefixes, and those for some reason are not getting reset (it may be that the change in configure requires a gmake clean; not sure) In any event I have another machine and will get something more detailed ASAP - I will also try the restore program and see if that works. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net Scott Marlowe wrote: On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] wrote: Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. So what error message? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly %SPAMBLOCK-SYS: Matched [hub.org+], message ok
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
Swaminathan Saikumar wrote: I didn't have proper knowledge about the UTF8 format, thanks. I originally meant nvarchar nchar, which is basically varchar char that supports Unicode regardless of the database encoding. Well, we don't need that when we have UTF8. There could be edge cases speed wise when you use UCS16 or UCS32 internally but I'm not sure how well this would justify a new datatype. The current problem isnt so much with encoding database wise, its more about collating database cluster wise - which is something not easily solved when you want to do it according to the SQL spec. You could work around that with a functional index. Regards Tino Wildenhain ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
Swaminathan Saikumar wrote: I am familiar with MS Sql Server just started using Postgres. For storing Unicode, Sql Server uses nvarchar/char for unicode, and uses char/varchar for ASCII. Postgres has this encoding setting at the database level. I am using UTF8 Unicode for most of my data, but there is some data that I know for sure will be ASCII. However, this is also stored as UTF8, using up more space. This is wrong - ASCII is a subset of UTF8 and therefore uses exactly one byte for every ASCII char. See http://en.wikipedia.org/wiki/UTF-8 for example. At first sight, it looks like the the more granular level design is better. Any comments? If you agree, does it make sense to add this as a new datatype to Postgres? Which new datatype? Regards Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
I didn't have proper knowledge about the UTF8 format, thanks. I originally meant nvarchar nchar, which is basically varchar char that supports Unicode regardless of the database encoding. On 3/2/08, Tino Wildenhain [EMAIL PROTECTED] wrote: Swaminathan Saikumar wrote: I am familiar with MS Sql Server just started using Postgres. For storing Unicode, Sql Server uses nvarchar/char for unicode, and uses char/varchar for ASCII. Postgres has this encoding setting at the database level. I am using UTF8 Unicode for most of my data, but there is some data that I know for sure will be ASCII. However, this is also stored as UTF8, using up more space. This is wrong - ASCII is a subset of UTF8 and therefore uses exactly one byte for every ASCII char. See http://en.wikipedia.org/wiki/UTF-8 for example. At first sight, it looks like the the more granular level design is better. Any comments? If you agree, does it make sense to add this as a new datatype to Postgres? Which new datatype? Regards Tino
Re: [GENERAL] 8.2.6 8.3 blows up
Scott Marlowe wrote: On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] wrote: Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. So what error message? It looks like the problem had to do with the tsearch2 module that I have in use in a number of my databases, and which had propagated into template1, which meant that new creates had it in there. If its in a database in a dump it trashes the restore for everything beyond that point when the restore is unable to find it in the new database. I went through the entire catalog and pulled the old contrib/tesearch2 module in for those databases where it was present before, and it now appears to be restoring ok. I'm not quite clear what I have to do in terms of if/when I can drop the old tsearch config stuff and for obvious reasons (like not running into this in the future) I'd like to. Can I just run the untsearch2 script against those databases or will that destroy the search functionality? Are there changes necessary in the SQL code (the documentation implies not unless I'm doing odd things) Thanks in advance... Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unicode comment on Postgres vs Sql Server
On Sunday 2. March 2008, Swaminathan Saikumar wrote: I am using UTF8 Unicode for most of my data, but there is some data that I know for sure will be ASCII. However, this is also stored as UTF8, using up more space. ASCII stored as UTF8 doesn't take up more space than plain ASCII, it's exactly the same thing. It's one byte per character unless the character number is above 127. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.6 8.3 blows up
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 02 Mar 2008 15:46:25 -0600 Karl Denninger [EMAIL PROTECTED] wrote: I'm not quite clear what I have to do in terms of if/when I can drop the old tsearch config stuff and for obvious reasons (like not running into this in the future) I'd like to. Can I just run the untsearch2 script against those databases or will that destroy the search functionality? Are there changes necessary in the SQL code (the documentation implies not unless I'm doing odd things) One of the goals for 8.3 and the integrated tsearch was to remove exactly this problem. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHyzUtATb/zqfZUUQRAuyAAJ4vTqNjBvuNkyx2ygX55V3KPHxO+QCgl8yb IAvNRUdlBg6G75KX9d95FiA= =cqc6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.6 8.3 blows up
Karl Denninger [EMAIL PROTECTED] writes: It looks like the problem had to do with the tsearch2 module that I have in use in a number of my databases, and which had propagated into template1, which meant that new creates had it in there. The old tsearch2 module isn't at all compatible with 8.3. I'd suggest removing it from the 8.2 source databases where possible, ie wherever you're not actually using it. Where you are using it, the cure is to install the 8.3 version of contrib/tsearch2 into the target database *before* restoring. See http://www.postgresql.org/docs/8.3/static/textsearch-migration.html http://www.postgresql.org/docs/8.3/static/tsearch2.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
Tom Lane wrote: Karl Denninger [EMAIL PROTECTED] writes: It looks like the problem had to do with the tsearch2 module that I have in use in a number of my databases, and which had propagated into template1, which meant that new creates had it in there. The old tsearch2 module isn't at all compatible with 8.3. I'd suggest removing it from the 8.2 source databases where possible, ie wherever you're not actually using it. Where you are using it, the cure is to install the 8.3 version of contrib/tsearch2 into the target database *before* restoring. See http://www.postgresql.org/docs/8.3/static/textsearch-migration.html http://www.postgresql.org/docs/8.3/static/tsearch2.html regards, tom lane %SPAMBLOCK-SYS: Matched [postgresql.org], message ok Yeah, I read that in the docs... But there are compatability problems with the tsearch2 contrib module in 8.3 and backwards constructs and code, with the most serious being that it simply doesn't work correctly for some of the older ways of formatting queries and storage. Specifically, if you store the ts_vector in the table via a different column and have a GIST index on it, this fails with the 8.3 tsearch2 module loaded as all updates or inserts return a complaint when the trigger fires - the claim is that there's a data type mismatch. The simplest fix is to rework the index to go directly off the underlying column and then query off that but that requires a change to both the query and the schema. Not a big deal to do once you figure out what's up, but it DOES require code changes. See my other message to the list on this; I have worked around it in the applications affected but this is likely to bite people with some authority until they get their arms around it. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.6 8.3 blows up
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 02 Mar 2008 15:46:25 -0600 Karl Denninger [EMAIL PROTECTED] wrote: I'm not quite clear what I have to do in terms of if/when I can drop the old tsearch config stuff and for obvious reasons (like not running into this in the future) I'd like to. Can I just run the untsearch2 script against those databases or will that destroy the search functionality? Are there changes necessary in the SQL code (the documentation implies not unless I'm doing odd things) One of the goals for 8.3 and the integrated tsearch was to remove exactly this problem. Sincerely, Joshua D. Drake Unfortunately there are code changes necessary on my end in one of my major applications; the attempt at compatability is somewhat less than successful from here. The problem is that I was holding the ts_vector in a column in the table with a GIST index on that column. This fails horribly under 8.3; it appears to be ok on the reload but as there is a trigger on updates any update or insert fails immediately with a data mistype complaint. The fix is to rejigger the query to go directly at the fields and build a gin or gist index directly on the underlying, which is not difficult but DOES require code and schema changes. I'll get through it but this is going to bite people with some authority if they have applications that were doing things the same way I was - there was nothing esoteric about the way I had coded it (although it could be argued it was somewhat wasteful of disk space.) -- Karl Denninger [EMAIL PROTECTED]
[GENERAL] PostgreSQL Docs - Community Documentation
Last month there was a discussion about how it would be nice to have a place people would write user-oriented documentation at with more flexibility than the current Techdocs site offers. I ran with that idea and there is now such a site available at http://www.postgresqldocs.org You will need to create an account in order to submit edits, but there's no approval process; you'll get in instantly. Submissions are accepted under the Creative Commons Attribution license. I put in an initial outline to organize things and filled in enough articles that there's already useful content there. What I plan to do is take all these archived e-mails I have from interesting list discussions and summarize a chunk of them every week onto articles there, like General Bits used to do (with the difference that as people notice issues with the suggestions it's easy to improve them). An example of that I'd suggest as a reasonable format to follow for that sort of thing is at http://www.postgresqldocs.org/index.php/Fixing_Sequences Thanks to Joshua Drake and Command Prompt for providing hosting space and even having an appropriate domain. To cut off one question I expect to pop up, yes it would be nice to have this integrated with the main postgresql.org site and its existing account structure. But since Joshua was the only person who answered my request for hosting space I used the server he volunteered. We'd be glad to move this to somewhere more official if that were available, I'm focused on creating the content and don't care where it lives at. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Docs - Community Documentation
Greg Smith wrote: Thanks to Joshua Drake and Command Prompt for providing hosting space and even having an appropriate domain. To cut off one question I expect to pop up, yes it would be nice to have this integrated with the main postgresql.org site and its existing account structure. But since Joshua was the only person who answered my request for hosting space I used the server he volunteered. We'd be glad to move this to somewhere more official if that were available, I'm focused on creating the content and don't care where it lives at. Greg et al, thanks very much for taking this idea and making it a reality. Since I was one of the ones who requested it, I'll sign up for an account right away (done). Regarding where it lives, what is the constraint that prevents it from residing on the main site? Space? Money? I see one addition that would be helpful to newcomers to this tool such as myself. The Main Page jumps right into the PG related material; Getting Started refers to getting started with PG, not with getting started contributing to the community documentation. I discovered that clicking the About PostreSQL Docs link on the bottom of the page produces a short intro to the purpose of the site. Could we also get added to this page a sentence or two about the software that runs the site, and a pointer to documentation on that software? I know, I know, this is user-contributed documentation, so I'm free to add that myself. Hopefully you appreciate my Catch-22 ;). About PostgreSQL Docs is an important topic, and should be elevated to the same list as Main Page. -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Docs - Community Documentation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 03 Mar 2008 00:47:09 -0500 Guy Rouillier [EMAIL PROTECTED] wrote: Greg et al, thanks very much for taking this idea and making it a reality. Since I was one of the ones who requested it, I'll sign up for an account right away (done). Regarding where it lives, what is the constraint that prevents it from residing on the main site? Space? Money? Logistics. That's it. It will happen when people have time or desire. The main site has more than enough hardware (but we can always use more). http://www.postgresql.org/about/servers Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFHy5oPATb/zqfZUUQRAjedAJUfRN21378FgWYEEIbxO79roQyaAJwKkxai y9mcvZYmXgqka/uZe0ansA== =4IeE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/