Re: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
Uh, yea, this is going to require quite a bit of discussion in the group, and I am concerned how it will affect other apps using PostgreSQL. (The mode isn't going to be useful if it breaks plug-in extensions and stuff.) The hard part of this isn't turning off backslash quoting; the code changes to do that would be pretty trivial. The hard part is not breaking vast quantities of existing client code. After our experience with autocommit, no one is going to want to solve it with a GUC variable that can be flipped on and off at random. That would make the compatibility problems that autocommit caused look like a day at the beach :-( I don't actually know a way to solve this that wouldn't impose impossible amounts of pain on our existing users, and I'm afraid that I rank that consideration higher than acquiring new users who won't consider changing their own code. If you can show me a way to provide this behavior without risk of breaking existing code, I'm all ears. regards, tom lane I feel somewhat confident (very actually) that a config option that disabled the backslash behavior globally(*) would be acceptable, BUT leave the current backslash behavior turned on by default so that current users are not impacted at all. Only a conscientious decision by the db admin to turn it on could cause problems, but _only_ if he/she didn't warn all his/her users beforehand of the impending change and its consequences (rtm). (*Or if it's possible, provide the no-backslash config on a per-catalog basis perhaps? -or even per-user/group?, --that would allow individuals to use the legacy mode until they choose otherwise) I can say, that I for one would enable the no-backslash config option out of the box -globally -so that we can start using pg now without any more upper managerial concerns/excuses about language/interface compliance..I can also say that (what we already know) the longer we wait to provide the 'right' option, the *more* legacy apps (and interfaces) will be built around it and consequently suffer when the need for change eventually comes (almost wholly caused by interop concerns). And market gain is being hurt now by this incompatibility with commercial offerings; that's an unfortunate fact. Better to nip it in the bud sooner than later, imo. thoughts, ken ---(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: [GENERAL] Referencing created tables fails with message that they do not exist!
On Sun, Feb 27, 2005 at 06:50:50PM -0500, Tommy Svensson wrote: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Could you copy and paste the *exact* commands and error messages and send them to the list? That might help us see what's going on. The tables do actually get created. I can se them in DBVisualizer. If you run psql, what does \d show? (Again, please copy and paste the exact output.) Is it possible that you created the tables in mixed case and didn't quote their names when you tried to query them? If so, then you might want to read Identifiers and Key Words in the SQL Syntax chapter of the documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Reading from Mysql writting in PGsql
Dears,I need to read 1 field with select command from mysql.Then Write it to pgsql. Please guide me. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
[ Previous version removed.] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Ron Mayer wrote: On Sun, 27 Feb 2005, Simon Riggs wrote: On Fri, 2005-02-25 at 16:48 -0800, Ron Mayer wrote: Getting closer? For me, yes. [...] The not-warnings seem a little wordy for me, but they happen when and how I would hope for. So, for me, it looks like a polish of final wording and commit. Thanks for the feedback. How about I replace the grammatically poor: LOG: max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d), HINT: You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated. with this: LOG: max_fsm_relations(100) equals the number of relations checked HINT: You have = 100 relations. You should increase max_fsm_relations. and replace this: LOG: max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f), HINT: You may want to increase max_fsm_pages to be larger than %.0f with the slightly smaller LOG: the number of page slots needed (2832) exceeds max_fsm_pages (1601) HINT: You may want to increase max_fsm_pages to a value over 2832. These updated messages would fit on an 80-column display if the numbers aren't too big. Here's 80 characters for a quick reference. 01234567890123456789012345678901234567890123456789012345678901234567890123456789 The pages needed...underestimate in the first message was no longer useful anyway; since it's no longer logging fsm_pages stuff when the max_fsm_relations condition occurred anyway Ron The patch now looks like: % diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c --- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31 14:00:54.0 -0800 +++ postgresql-patched/src/backend/storage/freespace/freespace.c 2005-02-27 11:54:39.776546200 -0800 @@ -705,12 +705,25 @@ /* Convert stats to actual number of page slots needed */ needed = (sumRequests + numRels) * CHUNKPAGES; -ereport(elevel, -(errmsg(free space map: %d relations, %d pages stored; %.0f total pages needed, +ereport(INFO, +(errmsg(free space map: %d relations, %d pages stored; %.0f total pages used, numRels, storedPages, needed), - errdetail(Allocated FSM size: %d relations + %d pages = %.0f kB shared memory., + errdetail(FSM size: %d relations + %d pages = %.0f kB shared memory., MaxFSMRelations, MaxFSMPages, (double) FreeSpaceShmemSize() / 1024.0))); + +if (numRels == MaxFSMRelations) +ereport(LOG, +(errmsg(max_fsm_relations(%d) equals the number of relations checked, + MaxFSMRelations), + errhint(You have = %d relations. You should increase max_fsm_relations.,numRels))); +else +if (needed MaxFSMPages) +ereport(LOG, +(errmsg(the number of page slots needed (%.0f) exceeds max_fsm_pages (%d), + needed,MaxFSMPages), + errhint(You may want to increase max_fsm_pages to a value over %.0f.,needed))); + } /* % -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low.
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Ron Mayer wrote: On Fri, 25 Feb 2005, Bruce Momjian wrote: Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Should the relation overflow be a WARNING or a LOG? ... I'd go for making them both LOG, I think. More consistent. Can we also update this wording: INFO: free space map: 52 relations, 61 pages stored; 848 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared memory. The pages needed is confusing. In fact it is the total pages used or allocated. I looked in the code and got confused. It needs clarity. Any preference? To me, allocated has some risk of sounding like it refers to the total free space map (memory allocated for fsm) instead of just the used ones.Allocated is actually used for that other meaning on the next line. I guess it's confusing there too, so that line should be changed as well. How about if I go for used in that first line; and simply remove the word Allocated in the DETAIL line. So instead of: INFO: free space map: 52 relations, 61 pages stored; 848 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared memory. it'll say INFO: free space map: 52 relations, 61 pages stored; 848 total pages used DETAIL: FSM size: 1000 relations + 2 pages = 182 kB shared memory. With those changes, the patch now looks like this... == % diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c --- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31 14:00:54.0 -0800 +++ postgresql-patched/src/backend/storage/freespace/freespace.c 2005-02-25 16:45:26.773792440 -0800 @@ -705,12 +705,25 @@ /* Convert stats to actual number of page slots needed */ needed = (sumRequests + numRels) * CHUNKPAGES; -ereport(elevel, -(errmsg(free space map: %d relations, %d pages stored; %.0f total pages needed, +ereport(INFO, +(errmsg(free space map: %d relations, %d pages stored; %.0f total pages used, numRels, storedPages, needed), - errdetail(Allocated FSM size: %d relations + %d pages = %.0f kB shared memory., + errdetail(FSM size: %d relations + %d pages = %.0f kB shared memory., MaxFSMRelations, MaxFSMPages, (double) FreeSpaceShmemSize() / 1024.0))); + +if (numRels == MaxFSMRelations) +ereport(LOG, +(errmsg(max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d), + MaxFSMRelations, numRels), + errhint(You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated. ,numRels))); +else +if (needed MaxFSMPages) +ereport(LOG, +(errmsg(max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f), + MaxFSMPages, needed), + errhint(You may want to increase max_fsm_pages to be larger than %.0f,needed))); + } /* == Getting closer? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Disabling triggers in a transaction
If I disable INSERT and UPDATE triggers inside a transaction; by setting and resetting reltriggers in pg_class; am I correct in thinking that this will disable triggers globally for that table for the duration of that transaction? So an INSERT or UPDATE to this table, outside of the transaction and within that precise timeframe, would NOT fire the trigger? If so, would the 'serializable ' isolation level be required in order to ensure this doesn't happen? ---(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: [GENERAL] hosting - asking for advice
On Sun, Feb 27, 2005 at 21:48:00 +0100, Berényi Gábor [EMAIL PROTECTED] wrote: It's a Win98 machine. That makes it trickier. I think it is possible to get it to install on W98, but the FAT file system isn't safe. There were some messages discussing this within the last couple of months. For just playing around the file system problem shouldn't be a problem. Bruno Wolff III [EMAIL PROTECTED] írta: On Fri, Feb 25, 2005 at 21:19:34 +0100, Berényi Gábor [EMAIL PROTECTED] wrote: I have a dotgeek.org free account, but they are about to leave PostgreSQL for MySQL which is too bad. Can you suggest me a free/cheap PostgreSQL host where I can test an open-source program? No web hosting is needed, only a single database with TCP/IP access enabled, plus ability to load a C-language function to the server. There is also a list at http://techdocs.postgresql.org/hosting.php, but I have no time to study it, and want to hear your suggestion anyway. If you are just testing, why aren't you just using your desktop to host the server? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] hosting - asking for advice
It's a Win98 machine. Bruno Wolff III [EMAIL PROTECTED] rta: On Fri, Feb 25, 2005 at 21:19:34 +0100, Bernyi Gbor [EMAIL PROTECTED] wrote: I have a dotgeek.org free account, but they are about to leave PostgreSQL for MySQL which is too bad. Can you suggest me a free/cheap PostgreSQL host where I can test an open-source program? No web hosting is needed, only a single database with TCP/IP access enabled, plus ability to load a C-language function to the server. There is also a list at http://techdocs.postgresql.org/hosting.php, but I have no time to study it, and want to hear your suggestion anyway. If you are just testing, why aren't you just using your desktop to host the server? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] GUI
Hi All, My sincere apologies if this is not the right list. I want to build a GUI for postgres DB, is there any toolkit available which allows to whip up a GUI real fast? General features for the GUi are like select a file to upload, login for DB Thanks in advance. HD ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible to run the server with ANSI/ISO string
On Sun, 2005-02-27 at 18:25 -0700, Ken Johanson wrote: Uh, yea, this is going to require quite a bit of discussion in the group, and I am concerned how it will affect other apps using PostgreSQL. (The mode isn't going to be useful if it breaks plug-in extensions and stuff.) The hard part of this isn't turning off backslash quoting; the code changes to do that would be pretty trivial. The hard part is not breaking vast quantities of existing client code. After our experience with autocommit, no one is going to want to solve it with a GUC variable that can be flipped on and off at random. That would make the compatibility problems that autocommit caused look like a day at the beach :-( I don't actually know a way to solve this that wouldn't impose impossible amounts of pain on our existing users, and I'm afraid that I rank that consideration higher than acquiring new users who won't consider changing their own code. If you can show me a way to provide this behavior without risk of breaking existing code, I'm all ears. regards, tom lane I feel somewhat confident (very actually) that a config option that disabled the backslash behavior globally(*) would be acceptable, BUT leave the current backslash behavior turned on by default so that current users are not impacted at all. Only a conscientious decision by the db admin to turn it on could cause problems, but _only_ if he/she didn't warn all his/her users beforehand of the impending change and its consequences (rtm). I'm a little worried about PostgreSQL having the same problems as PHP. In PHP, every time you want to download an application, you never see This application works on php 4+. Instead, you see This application works on php4+ with the following config options set long list. Sometimes these applications have conflicting requirements. From an administrator's standpoint, it's a mess. In PostgreSQL I think it would actually be much worse. Right now many applications build a PostgreSQL layer, but will they build two? I think this would cause a divide in the application support (some for config option A some for config option B) in the already smaller-than-we'd-like set of software that supports PostgreSQL. Regards, Jeff Davis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
Martijn van Oosterhout wrote: On Sun, Feb 27, 2005 at 06:25:18PM -0700, Ken Johanson wrote: I feel somewhat confident (very actually) that a config option that disabled the backslash behavior globally(*) would be acceptable, BUT leave the current backslash behavior turned on by default so that current users are not impacted at all. Only a conscientious decision by the db admin to turn it on could cause problems, but _only_ if he/she didn't warn all his/her users beforehand of the impending change and its consequences (rtm). It's not just a question of warning the users, all interfaces to the database will instantly break. For example: JDBC, Perl DBI, PHP PEAR etc. They will continue to send queries with the backslashes embedded. These interfaces would need to be modified to handle both situations and detect which situation they're dealing with. All interfaces will NOT break IF the legacy db behavior stays its default. This means NONE of the current users would be hurt until they start experimenting with the new option. Yes, the built in prepared stmt components of those interfaces will still add the backslash by default and break queries for legacy drivers, but this is not an issue for the straight-through query/update exec(s) calls, and prepared stmt users can hack the Prepared stmts behavior until the same option is officially supported in the driver also (probably by auto-detecting what the DB expects its backslashes to look like). Like I said, users should be warned beforehand, that they need to get a hacked or official driver update, if the dbadmin decides to turn on the 'new' mode. Seems prudent to me. The thing is all these interfaces handle the quoting transparently for you, so the code is portable already. What you're complaining about is that you have your own query marshalling and it is not portable. As you say, the portability you describe REQUIRES the use of prepared stmts type queries - but one CANNOT issue a portable query, say, jdbc:stmt.execQuery() or execute update. Those "lower-level" calls need to be portable where string escaping is concerned, and they are not. They arguably break both the JDBC spec and SQL spec since some additional, nonstandard string preprocessing is REQUIRED for them to work. And not to repeat what you already know, but Prepared stmts are not suitable or available for certain query types (performance sometimes better w/o PS, dynamic query building, batch queries, etc) and drivers (small foot print ones). Incidently, if you disable the backslash quoting, how does one enter raw binary data including NUL (\0) characters? I'm not sure if you're talking about API interfaces or shell, or both. If shell, a piped sql-compliant escape processor would clearly be needed. As for APIs, I suspect the current design of each driver handles nulls and the like, stems from how the underlying db protocol was originally built --so they use either run length encoding (chunking), boundary-delimiting, or are null terminated (whichever PG uses natively, I don't know). In any case the very lowest level of the db interface can translate as necessary (doubling the backslashes if necessary), but queries should be able to issue any byte, including nulls, with the only requirement that apostrophes are the string-escape character, for themselves. In other words nulls should need no special treatment from the query interface layer. The only viable solution I can think of is that it is set at *connection* time (maybe extra parameters), and unchangable for the rest of the session. This means that unmodified client interfaces won't see a difference. Yes, sessions (connections) could work, and also perhaps per-user or group, wherein the db the escape handing is handled the old or new way on a per-user basis. I can say, that I for one would enable the no-backslash config option out of the box -globally -so that we can start using pg now without any more upper managerial concerns/excuses about language/interface compliance..I can also say that (what we already know) the longer we wait to provide the 'right' option, the *more* legacy apps (and interfaces) will be built around it and consequently suffer when the need for change eventually comes (almost wholly caused by interop concerns). And market gain is being hurt now by this incompatibility with commercial offerings; that's an unfortunate fact. Even if PostgreSQL implements this now, you will have to wait for new versions of any client libraries before it's usable. See the autocommit disaster for an example why people are not rushing into this... I fully agree. I can see waiting at LEAST 1-3 months before the db itself has changes committed for alpha testing, but that SURE BEATS procrastination --which means years worth of more apps and interfaces being built around the 'backslash' (again, not everyone uses prepared statement - its not required and
Re: [GENERAL] GUI
I prefer GTK, but your platform will be Windows or *NIXes? (If you choose Linux I will be very glad to help you) - Original Message - From: Hrishikesh Deshmukh [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Sunday, February 27, 2005 2:47 PM Subject: [GENERAL] GUI Hi All, My sincere apologies if this is not the right list. I want to build a GUI for postgres DB, is there any toolkit available which allows to whip up a GUI real fast? General features for the GUi are like select a file to upload, login for DB Thanks in advance. HD ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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: [GENERAL] basic trigger using OLD not working? THANKS!
Hey, thanks to everyone who replied to my questions: problem solved! I needed to: 1) do BEFORE DELETE to see the OLD variables, and 2) use a placeholder in my format string in the RAISE EXCEPTION/NOTICE statement. Open source newsgroups rock! --rick Rick Casey, Research Associate Institute for Behavioral Genetics [EMAIL PROTECTED] 303.735.3518 Sven Willenberger wrote: [EMAIL PROTECTED] presumably uttered the following on 02/25/05 19:14: Yes, thank you, I corrected my function from statement level to row level. This did get rid of the error message. However, I still get no output from an OLD variable that should contain data: see the test variable in the simple case below. How else can I test OLD variables? This is the simplest test case I can think of. Any suggestions would be appreciated! Thanks, Rick I think you have created a statement level trigger (If they existed in 7.4.7...) by not including FOR EACH ROW in your create statement. In statement level triggers, there is no OLD or NEW. Rick Casey [EMAIL PROTECTED] 02/24/05 1:22 PM Hello all, I am trying to a simple thing: create a log history of deletes, and updates; but which I am having trouble getting to work in PG 7.4.7 (under Debian Linux 2.6.8). I have reduced my code to the following trivial case: Here is the code that creates the delete trigger: create trigger PEDIGREES_hist_del_trig AFTER DELETE on PEDIGREES EXECUTE PROCEDURE logPedigreesDel(); Here is the trigger code: (famindid is an integer field in the Pedigrees table): CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS ' DECLARE test integer; begin test := OLD.famindid; RAISE EXCEPTION ''OLD.famindid = '', test; return OLD; end; ' LANGUAGE plpgsql; Need a place holder for your variable in your RAISE expression (like a printf syntax): RAISE EXCEPTION ''OLD.famindid = %'', test; btw, if you just want to see the variable without having your function bail on you, try RAISE NOTICE ''OLD.famindid = %'', test; Sven ---(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
[GENERAL] to_char bug?
Is there any reason why : SELECT char_length(to_char(1, '000')); Gives a result char_length - 4 (1 row) It seems that to_char(1, '000') gives a string 001 with a space in front. Is this a bug? Regards, Ben ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Lost rows/data corruption?
Yes, we compile our own kernel based on the stardardised stable release available at the time. Everything we need is compiled in. This is what I mean by standard Linus approved kernel release (as opposed to an AC/MM modified release etc.) - Original Message - From: Keith C. Perry [EMAIL PROTECTED] To: Andrew Hall [EMAIL PROTECTED] Cc: Alban Hertroys [EMAIL PROTECTED]; Marco Colombo [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Saturday, February 26, 2005 6:02 AM Subject: Re: [GENERAL] Lost rows/data corruption? Quoting Andrew Hall [EMAIL PROTECTED]: Do you happen to have the same type disks in all these systems? That could point to a disk cache problem (f.e. the disks lying about having written data from the cache to disk). Or do you use the same disk parameters on all these machines? Have you tried using the disks w/o write caching and/or in synchronous mode (contrary to async). It's all pretty common stuff, quite a few customers use standard IDE (various flavours of controller/disk), some now use SATA (again various brands) and the rest use SCSI. The kernel we use is the standard Linus approved kernel with the inbuilt drivers as part of the kernel. We don't supply any non-default parameters to the disk controllers. Thanks for your suggestion on write caching, I'll look into this, I'm also tempted to try a different journalling FS too. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) I'm a little late on this thread but in regards to the SATA support. 2.4.29 in my experience is really the first kernel that decent SATA support (i.e. much better data throughput). I think that would corresponse to 2.6.9 or .10 but even before you get into all that. I am curious to know what do you mean by standard Linus kernel. Do you not compile your own kernels for the hardware platform being used? -- Keith C. Perry, MS E.E. Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] basic trigger using OLD not working?
[EMAIL PROTECTED] (Rick Casey) writes: CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS ' begin RAISE EXCEPTION ''OLD.famindid = '', OLD.famindid; RAISE EXCEPTION ''OLD.famindid = %'', OLD.famindid; ^ return OLD; end; ' LANGUAGE plpgsql; -- Remove -42 for email ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] multicolumn GIST index question
Did anyone get multi-column GIST indexes working using both
the gist_btree and postgis modules?
It seems to work fine for me on small test cases (shown
at the bottom), but seems to crash my database for large
ones. Any advice is welcome - including pointers to better
lists to ask questions like this.
Output showing a crash (on a large database) and a
successful run on a tiny one follow.
Ron
fl=# -- sessionid is a text; the_geom is a GEOMETRY
fl=# create index testidx2 on user_point_features using gist
(sessionid,the_geom);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
fl=#
fl=#
fl=#
fl=# create table test3(a text, b text, c text);
CREATE TABLE
fl=# select addgeometrycolumn ('','test3','the_geom','-1','POINT',2);
addgeometrycolumn
--
public.test3.the_geom SRID:-1 TYPE:POINT DIMS:2
geometry_column fixed:0
(1 row)
fl=# create index idx_text_text on test3 using gist (a,b);
CREATE INDEX
fl=# create index idx_text_geom on test3 using gist (a,the_geom);
CREATE INDEX
fl=#
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Suggestion for parameterized queries
Hi, Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would even be better if the DECLARE CURSOR could use a PREPAREd statement, to declare a server-side cursor with a parameterized query, to use the benefits of DECLAREd cursors (no fetching of all the result set on the client, use the binary mode, use the scrollable option or the FOR UPDATE option). Something like: PREPARE s1 (type,..) AS SELECT DECLARE c1 (value,...) CURSOR FROM s1 FETCH c1 or have a new OPEN instruction to pass parameters: PREPARE s1 (type,..) AS SELECT DECLARE c1 CURSOR FROM s1 OPEN c1 USING (value,...) FETCH c1 That would be an incredible improvement to make PostgreSQL compete with other databases like Informix, Oracle, Db2 UDB, SQL Server (I wrote some drivers for these database servers). For now we cannot use DECLARE CURSOR in our PostgreSQL driver because of this limitation I could build the SQL statement with literal values, but since you have now parametrized queries I would prefer to use that... Thank you! Sebastien FLAESCH Database driver writer at www.4js.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Unicode support on Win32
Hi, I am using Windows as a development environment (and database design), and it seems that Unicode is not supported on Win32. Is there word on when Unicode support will be implemented on the Windows port of PostgreSQL? I was used to using mySQL and it supported Unicode on Windows properly.. But I suppose that the Windows port of PostgreSQL is still working in progress. On the other hand, could I pick a different charset for now, and when I need to copy this whole database to a production machine (that will use linux) choose the UTF-8 charset ? I also don't seem to understand why you need to define the whole database as unicode ? Don't you only need unicode for certain fields, and the rest can be Latin1? Thanks :) __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Newbie - Pointers on moving.
Hi I have to move a pgsql database which is running on a linux box to a Windows 2003 server, so just started to find out about pgsql Not sure what version of pgsql is running on the linux box. This a library application written in php?. I have just installed php v5.0.0 and postgresql v 8.0.1 on the Windows 2003 server and it is working. I can use phppgadmin v 3.5.2 to login to the gpsql on the windows box. I am also trying to use the PostgreSQL ODBC connector to connect. I am a MS SQL DBA, so know more on ms products. Any recommendations on how to do this migration/upgrade ? TIA RajiA ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] to_char bug?
Ben Trewern wrote: Is there any reason why : SELECT char_length(to_char(1, '000')); Gives a result char_length - 4 (1 row) It seems that to_char(1, '000') gives a string 001 with a space in front. Is this a bug? Regards, Ben Try formatting the result: SELECT char_length(to_char(1, 'fm000')); char_length - 3 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] row numbering
NTPT wrote: Having some sort of line numbering in result query would be nice... The query result has line numbering. How else are you accessing the individual rows? Is the issue really that you want psql to number the lines on display? That could be implemented. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Possible to run the server with ANSI/ISO string
I'm a little worried about PostgreSQL having the same problems as PHP. In PHP, every time you want to download an application, you never see This application works on php 4+. Instead, you see This application works on php4+ with the following config options set long list. Sometimes these applications have conflicting requirements. From an administrator's standpoint, it's a mess. In PostgreSQL I think it would actually be much worse. Right now many applications build a PostgreSQL layer, but will they build two? I think this would cause a divide in the application support (some for config option A some for config option B) in the already smaller-than-we'd-like set of software that supports PostgreSQL. Regards, Jeff Davis There's certainly two perspectives to this. The one you present is certainly valid, but consider the bigger picture... This application requires the following databases: Oracle versionX, MY SQL version X, Mysql version 5.2 with the no-backslashes option, UltraDB version x Notice the lack of PG - some apps - most notably commercial ones - will automatically shoot it down if it cant meet certain language requirements. The database itself could meet the latest SQL03 (or whatever we're up to) specs for Object Relational stuff, etc to the tee. The JDBC driver could meet the JDBC spec to the tee for transaction support, etc - but this one low level problem is a total show stopper, because it plainly breaks queries sent through various interfaces in various drivers. Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. Thoughts, ken ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] GUI
On Sun, 2005-02-27 at 15:47 -0500, Hrishikesh Deshmukh wrote: Hi All, My sincere apologies if this is not the right list. I want to build a GUI for postgres DB, is there any toolkit available which allows to whip up a GUI real fast? General features for the GUi are like select a file to upload, login for DB Thanks in advance. HD wxWidgets (formerly wxWindows) http://www.wxwidgets.org/ there is wxPython, wxRuby, wxEtc.. -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development * --- Now hosting Ruby on Rails Apps --- / ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] row numbering
If you insert the results of your query into a table with a serial column, the serial column will do what you want.. Plus add a huge overload... ? Having some sort of line numbering in result query would be nice... - Original Message - From: Mike Harding [EMAIL PROTECTED] To: Peter Eisentraut [EMAIL PROTECTED] Cc: josue [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Saturday, February 26, 2005 1:27 AM Subject: Re: [GENERAL] row numbering If you insert the results of your query into a table with a serial column, the serial column will do what you want.. On Sat, 2005-02-26 at 01:10 +0100, Peter Eisentraut wrote: josue wrote: is there a way return a column with the row number automatically generated according the way the rows were processed by the query. No, but you can easily keep a counter in the client. -- Mike Harding [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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: [GENERAL] row numbering
is there a way return a column with the row number automatically generated according the way the rows were processed by the query. No, but you can easily keep a counter in the client. How, then, do I do it if I need the row number in a view ? Keep the counter in the client as you process or display the result rows. Well, but I'd like to use those row numbers in a comparison right inside the view definition ... Eg.: There are 5 vaccinations in a given vaccination schedule. Patient had 3 shots. I want the view to show me that shot 4 and 5 are missing without having to enter the cardinality of the vaccination in the original data. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] PostgreSQL 8 compiling for Windows
Hello list I need to compile postgres 8 for Windows to set up the locale support. The distributive accessible from FTPs does not support it, and I cannot sort Russian letters - that's why.. I have MS Visual Studio 6, but I've never worked with it. So, how can I do everything? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Referencing created tables fails with message that
Thanks for all the replys, I got quite a lot of them, and haven't had time to read them all yet, but one common comment I get is You didn't supply enough information , be more specific, etc. You are of course right. From the answers I got however, it seems to be a very high propability that it is different case in table name between create and select, etc that causes the problem. It does make sense (now). I got the suggestion to quote the table names. I also switched tools after creating the first table and trying to create the next since the first tool was so simple it did not support foreign keys. That was probably a bad thing to do :-) I will drop the table in the tool that created it and then recreate them in the same tool and quote the names as suggested. I'm quite sure that will solve my problem. inMyDefense skip=OK - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience with these led me to beleive that SQL was case insensitive. In fact, I was so sure of it that a case problem just never occured to me. - Since I could create the table I assumed it was OK and there were no point in describing the table. /inMyDefense Regards, Tommy Svensson Tino Wildenhain wrote: Am Sonntag, den 27.02.2005, 18:50 -0500 schrieb Tommy Svensson: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. You should have provided an exact example of _how_ do you create your tables. I bet you created tables with mixed case (in a tool?) but dont use the quoting on these table names when you select. ---(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
[GENERAL] Problems with pgcrypto and special characters
Hello!
To get straight to the point, here's my problem:
mypgdb=# select bytea2text(decrypt(encrypt('Tübingen'::bytea,
'mypassphrase'::bytea,'bf'::text),'mypassphrase'::bytea,'bf'::text)) as foo;
foo
-
T\303\274bingen
(1 row)
I have compiled and installed pg_crypto and I'am using the following function
as workaround for a bytea-to-text-cast:
create or replace function bytea2text(bytea) returns text as '
begin
return $1;
end;
' language plpgsql;
The cluster was initialized with locale de_DE.UTF-8, pg_controldata confirms:
LC_COLLATE: de_DE.UTF-8
LC_CTYPE: de_DE.UTF-8
Database version is PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.2
I think I'm missing something very obvious here, so please give me a hint: How
can I use pgcrypto to encrypt and decrypt text which contains UTF-8 special
characters like german umlauts? I think that this simple bytea2text-function
probably needs a replacement, but I haven't got the faintest clue about how to
actually retrieve the original input after encryption. Any help would be
tremendously appreciated :)
Thanks in advance!
Kind regards
Markus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
On Sun, Feb 27, 2005 at 06:25:18PM -0700, Ken Johanson wrote: I feel somewhat confident (very actually) that a config option that disabled the backslash behavior globally(*) would be acceptable, BUT leave the current backslash behavior turned on by default so that current users are not impacted at all. Only a conscientious decision by the db admin to turn it on could cause problems, but _only_ if he/she didn't warn all his/her users beforehand of the impending change and its consequences (rtm). It's not just a question of warning the users, all interfaces to the database will instantly break. For example: JDBC, Perl DBI, PHP PEAR etc. They will continue to send queries with the backslashes embedded. These interfaces would need to be modified to handle both situations and detect which situation they're dealing with. The thing is all these interfaces handle the quoting transparently for you, so the code is portable already. What you're complaining about is that you have your own query marshalling and it is not portable. Incidently, if you disable the backslash quoting, how does one enter raw binary data including NUL (\0) characters? The only viable solution I can think of is that it is set at *connection* time (maybe extra parameters), and unchangable for the rest of the session. This means that unmodified client interfaces won't see a difference. I can say, that I for one would enable the no-backslash config option out of the box -globally -so that we can start using pg now without any more upper managerial concerns/excuses about language/interface compliance..I can also say that (what we already know) the longer we wait to provide the 'right' option, the *more* legacy apps (and interfaces) will be built around it and consequently suffer when the need for change eventually comes (almost wholly caused by interop concerns). And market gain is being hurt now by this incompatibility with commercial offerings; that's an unfortunate fact. Even if PostgreSQL implements this now, you will have to wait for new versions of any client libraries before it's usable. See the autocommit disaster for an example why people are not rushing into this... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpJKqL3PigNZ.pgp Description: PGP signature
[GENERAL] Suggestion for parameterized queries
Hi, Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would even be better if the DECLARE CURSOR could use a PREPAREd statement, to declare a server-side cursor with a parameterized query, to use the benefits of DECLAREd cursors (no fetching of all the result set on the client, use the binary mode, use the scrollable option or the FOR UPDATE option). Something like: PREPARE s1 (type,..) AS SELECT DECLARE c1 (value,...) CURSOR FROM s1 FETCH c1 or have a new OPEN instruction to pass parameters: PREPARE s1 (type,..) AS SELECT DECLARE c1 CURSOR FROM s1 OPEN c1 USING (value,...) FETCH c1 That would be an incredible improvement to make PostgreSQL compete with other databases like Informix, Oracle, Db2 UDB, SQL Server (I wrote some drivers for these database servers). For now we cannot use DECLARE CURSOR in our PostgreSQL driver because of this limitation I could build the SQL statement with literal values, but since you have now parametrized queries I would prefer to use that... Thank you! Sebastien FLAESCH Database driver writer at www.4js.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] row numbering
OT: You have other database issues: http://www.gnumed.org/ snip error insert into WebLog values(586,31,2005-02-28,ip addr removed) Duplicate entry '2005-02-28' for key 2 /snip On Sun, 27 Feb 2005 18:08:02 +0100, Karsten Hilbert [EMAIL PROTECTED] wrote: is there a way return a column with the row number automatically generated according the way the rows were processed by the query. No, but you can easily keep a counter in the client. How, then, do I do it if I need the row number in a view ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Referencing created tables fails with message that
Am Sonntag, den 27.02.2005, 18:50 -0500 schrieb Tommy Svensson: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. You should have provided an exact example of _how_ do you create your tables. I bet you created tables with mixed case (in a tool?) but dont use the quoting on these table names when you select. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Referencing created tables fails with message that
On Sun, 27 Feb 2005, Tommy Svensson wrote: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. You haven't given alot of information in the above, but this often happens if the table was created with double quotes (often implicitly by a tool) in mixed-case but is referenced without double quotes which causes case-folding. This also sometimes similarly happens with all uppercase names because PostgreSQL does folding to lowercase rather than the SQL folding to uppercase. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Referencing created tables fails with message that
On Sun, 2005-02-27 at 18:50 -0500, Tommy Svensson wrote: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. you do not give actual examples, nor do you say how you created the tables, but one possibility is that you ran into the case-folding feature. names are folded to lowercase unless quoted in doublequotes. if you (or the client you use) created your table with quoted upper-case or mixed case names, you must do the same with the selects. CREATE TABLE Foo (a text); SELECT a from Foo; -- works SELECT a from Foo; -- fails the same applies to other names, such as columns. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] exporting table for load into oracle
miguel angel rojas aquino wrote: hi everybody first, it is not that i'm migrating from postgresql to oracle, it's just that we have a payroll system (we are a software development company) that currently runs on postgresql, but the goal is that it runs on other databases too (for our internal needs it runs on postgresql, and two of our clients run in postgresql too), so now i need to test it on oracle i've done a dump of our data, but we are having troubles with the date fields, as pg_dump just dumps dates as '-mm-dd', but when loading into oracle, it just can't handle dates this way, it needs an explicit mask (to_date) when importing the sql dump so the question is, there is a way to instruct pg_dump to dump dates with the to_date function included? Not a direct answer, but how's about using CSV files instead of dumps? You can instruct SQL Loader about the format of incoming dates via the control file (ctl.) BTW, I just did a migration in the other direction using CSV files. I must say I never cease to be impressed by the robustness of PostgreSQL. Oracle exported dates like this: 9/14/2004 6:40:21 PM. PG took 'em right in with no special instructions, and produced the correct timestamp. Hats off to the PG developers! Thanks for all your work. -- Guy Rouillier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Referencing created tables fails with message that
Try putting quotes around the table name: select * from Table; ? Do you see it in the table list? \dt Tommy Svensson wrote: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. Best Regards, Tommy Svensson ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Regards, Chris Smith Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia Ph: +61 2 9517 2505 Fx: +61 2 9517 1915 email: [EMAIL PROTECTED] web: www.interspire.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Referencing created tables fails with message that
Tommy Svensson presumably uttered the following on 02/27/05 18:50: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. What database did you create the tables in? and what database are you connecting to when trying to issue your SELECT statements? Are you trying to SELECT from the same application you are using to CREATE TABLE? It sounds like you created the tables in database and perhaps are trying to SELECT * FROM while connected to template1. Sven ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] http://www.postgresql.org/community/lists/
On Saturday 26 February 2005 09:41, Tatsuo Ishii wrote:
Hi, I don't know here is an appropriate place but...
In http://www.postgresql.org/community/lists/ there's Regional
Lists. It would be nice if Japan PostgreSQL community's regional list
pgsql-jp is added to it. The list page is
http://www.postgresql.jp/PostgreSQL/pgsql-jp.html.
Added, thanks much.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Reading from Mysql writting in PGsql
You will have to export from mysql and then insert into postgresql. If you are using postgresql 8 or above, you can use dbi-link (http://pgfoundry.org/projects/dbi-link/) to set up a view in your postgresql for the table in mysql and select directly from it. Sean - Original Message - From: Mohsen Pahlevanzadeh [EMAIL PROTECTED] To: pgsql-general@postgresql.org Cc: mysql@lists.mysql.com Sent: Sunday, February 27, 2005 6:27 PM Subject: [GENERAL] Reading from Mysql writting in PGsql Dears,I need to read 1 field with select command from mysql.Then Write it to pgsql. Please guide me. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] pgpool fundamental questions
Pgpool is a real cool project, I really hope it keeps evolving. I have a couple basic/fundamental questions that I hoping to get some clarification on. Question 1: If pgpool is used in conjuntion with slony or a another replication it can be used only for its loadbalancing and failover. I am correct like this? What else needs to be done to set in failover? set the backends backend_host_name =master secondary_backend_host_name=slave set it to lb on and replication off... load_balance_mode=true replication_mode=false set the health checks for failover. health_check_timeout=20 health_check_period=20 health_check_user=user Question 2: In the case that the master goes down pgpool would failover to the slave/secondary. What would be the logical steps to bring it back to normal. Can this be down with no downtime with both? 1) Turn off health check or weight the weight_master =0 so when you bring the master back load does not go to it when you bring the db backup. or does pgpool leave it down automatically until you restart pgpool? 2) Shutdown replication on the master. Bring back the master backup. 3) rsynch the data from the slave to the master??? 4) turn replication? Question 3: Is there anyway to make pgpool loadbalance certain select queries to the slave such as queries to a particular table? Is that a future feature planned? Thanks! ---(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: [GENERAL] postgresql 8.0 advantages
Hi, Am Samstag, den 26.02.2005, 15:35 -0600 schrieb Jim C. Nasby: On Sat, Feb 26, 2005 at 01:27:55AM -0800, Jeff Davis wrote: On Fri, 2005-02-25 at 09:20 -0800, Si Chen wrote: Thanks! Is there any documentation on how to upgrade to 8.0? Is it possible to upgrade from 7.4 to 8.0 while keeping a production database running in place? Or should I install 8.0 in another directory/machine and then restore the database into it? Are there any incompatibilities/modifications to databases from the earlier veresion required? You might want to check out Slony-I http://www.slony.info for the purpose of upgrading. Slony is actually a replication engine, but it can be used to upgrade with little or no downtime. Otherwise, just dump/upgrade/restore like normal. Has anyone tried moving a database from one location to another on the HD? I'd like to use slony to minimize downtime, but I'd also like my data to end up in the same place it is right now when I'm done. I used a straight copy of the filesystem with running database (over the net in my case) and immediately after that, stop the db and rsync for the last changes. This took only 10 minutes (compared to 1.5h for the full filesystem copy) and I could start up the db in new location. this could work for you too. Regards Tino ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [Slony1-general] Nagios plugin to check slony replication
I've finally got around to writing the two nagios plugins which I am using to check our slony cluster (on our linux servers). I'm posting them in case anyone else wants them or to use them as a basis for something else. These are based on Christopher Browne's scripts that ship with slony. I have added these scripts and your notes to CVS. I'll be updating them soon to allow running from a remote host, as that's something we want to be able to do. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Suggestion for parameterized queries
Sebastien FLAESCH wrote: Hi, Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would even be better if the DECLARE CURSOR could use a PREPAREd statement, to declare a server-side cursor with a parameterized query, to use the benefits of DECLAREd cursors (no fetching of all the result set on the client, use the binary mode, use the scrollable option or the FOR UPDATE option). For now we cannot use DECLARE CURSOR in our PostgreSQL driver because of this limitation I could build the SQL statement with literal values, but since you have now parametrized queries I would prefer to use that... You can define a function that returns a cursor, if that is of any use to you. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] GUI
On 27 Feb 2005 at 15:47, Hrishikesh Deshmukh wrote: My sincere apologies if this is not the right list. I want to build a GUI for postgres DB, is there any toolkit available which allows to whip up a GUI real fast? General features for the GUi are like select a file to upload, login for DB Have you seen pgAdmin (www.pgadmin.org)? --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Possible to run the server with ANSI/ISO string
On Mon, Feb 28, 2005 at 10:13:00 -0700, Ken Johanson [EMAIL PROTECTED] wrote: Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. Shouldn't this data be being passed through some standard code that checks if escaping is needed? If so, is that the right place to handle whether or not backslashes need to be escaped in addition to single quotes? ---(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: [GENERAL] Possible to run the server with ANSI/ISO string
Bruno Wolff III wrote:
On Mon, Feb 28, 2005 at 10:13:00 -0700,
Ken Johanson [EMAIL PROTECTED] wrote:
Besides, the version-deprecation / version requirements you mention
exists in every piece of software I've even seen. Sometime they're okay
with a really old version, sometime only the newest will do. This is the
very argument for getting PG to offer an (use-optional) escape behavior
inline with the rest - to mitigate these version requirements down the road.
Shouldn't this data be being passed through some standard code that checks if
escaping is needed? If so, is that the right place to handle whether or not
backslashes need to be escaped in addition to single quotes?
Ideally yes, but its not a requirement in any driver's spec that I'm
familiar with. In fact the driver specs expect or 'claim' some (possibly
implicit) level of sql language compliance -- so that the same query
sent to a different database yields the same result.
insert into tbl (path) values ('c:\test')
The above query *could* and should* be sent through an escape
preprocessor (PreparedStatement interface) but it is *not* required.
It's also not fair to say that a user can *expect* the above to not work
with PG even though it does with another DB, imo. The user coming from
another DB *won't* expect it to be broken. (I know from experience :-)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low.
On Sun, 27 Feb 2005, Simon Riggs wrote: On Fri, 2005-02-25 at 16:48 -0800, Ron Mayer wrote: Getting closer? For me, yes. [...] The not-warnings seem a little wordy for me, but they happen when and how I would hope for. So, for me, it looks like a polish of final wording and commit. Thanks for the feedback. How about I replace the grammatically poor: LOG: max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d), HINT: You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated. with this: LOG: max_fsm_relations(100) equals the number of relations checked HINT: You have = 100 relations. You should increase max_fsm_relations. and replace this: LOG: max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f), HINT: You may want to increase max_fsm_pages to be larger than %.0f with the slightly smaller LOG: the number of page slots needed (2832) exceeds max_fsm_pages (1601) HINT: You may want to increase max_fsm_pages to a value over 2832. These updated messages would fit on an 80-column display if the numbers aren't too big. Here's 80 characters for a quick reference. 01234567890123456789012345678901234567890123456789012345678901234567890123456789 The pages needed...underestimate in the first message was no longer useful anyway; since it's no longer logging fsm_pages stuff when the max_fsm_relations condition occurred anyway Ron The patch now looks like: % diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c --- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31 14:00:54.0 -0800 +++ postgresql-patched/src/backend/storage/freespace/freespace.c2005-02-27 11:54:39.776546200 -0800 @@ -705,12 +705,25 @@ /* Convert stats to actual number of page slots needed */ needed = (sumRequests + numRels) * CHUNKPAGES; -ereport(elevel, -(errmsg(free space map: %d relations, %d pages stored; %.0f total pages needed, +ereport(INFO, +(errmsg(free space map: %d relations, %d pages stored; %.0f total pages used, numRels, storedPages, needed), - errdetail(Allocated FSM size: %d relations + %d pages = %.0f kB shared memory., + errdetail(FSM size: %d relations + %d pages = %.0f kB shared memory., MaxFSMRelations, MaxFSMPages, (double) FreeSpaceShmemSize() / 1024.0))); + +if (numRels == MaxFSMRelations) +ereport(LOG, +(errmsg(max_fsm_relations(%d) equals the number of relations checked, + MaxFSMRelations), + errhint(You have = %d relations. You should increase max_fsm_relations.,numRels))); +else +if (needed MaxFSMPages) +ereport(LOG, +(errmsg(the number of page slots needed (%.0f) exceeds max_fsm_pages (%d), + needed,MaxFSMPages), + errhint(You may want to increase max_fsm_pages to a value over %.0f.,needed))); + } /* % ---(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: [GENERAL] Newbie - Pointers on moving.
Envbop wrote: I have to move a pgsql database which is running on a linux box to a Windows 2003 server, so just started to find out about pgsql Not sure what version of pgsql is running on the linux box. This a library application written in php?. Any recommendations on how to do this migration/upgrade ? Run the new (v8.0) pg_dump on the Windows box and use it to dump the database on the linux box (with the -h host parameter). The latest version will probably be smarter than older versions of pg_dump. Then use pg_restore to restore the data. If your linux database is only a couple of years old there shouldn't be any problems. If you do have problems, try restoring the schema then the data - that way it's easier to identify where the problems are. Details on the various options for these tools are in the client applications section of the manuals. -- Richard Huxton Archonet Ltd ---(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: [GENERAL] row numbering
OT: You have other database issues: http://www.gnumed.org/ snip error insert into WebLog values(586,31,2005-02-28,ip addr removed) Duplicate entry '2005-02-28' for key 2 /snip Yes I do and no I don't. That database underlies a Wiki written by one of our contributors. Nothing directly related to GnuMed or it's schema itself. Thanks for pointing this out. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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: [GENERAL] row numbering
Karsten Hilbert wrote: There are 5 vaccinations in a given vaccination schedule. Patient had 3 shots. I want the view to show me that shot 4 and 5 are missing without having to enter the cardinality of the vaccination in the original data. That sounds like you are trying to abuse the data model, so I'm not surprised that it isn't easily possible. As the data stored in a table is inherently unordered, you can't really talk about order unless you impose it yourself by way of assigning ordinal numbers or some other sort key to your rows. Even if you could, say, assign a fixed order to tables or views or actually had some kind of automatic row number available, that would still make the semantics of your data dependent of the particularities of the queries that you use to access it, which doesn't sound like a good idea to me. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] row numbering
On Mon, Feb 28, 2005 at 17:46:43 +0100, Karsten Hilbert [EMAIL PROTECTED] wrote: There are 5 vaccinations in a given vaccination schedule. Patient had 3 shots. I want the view to show me that shot 4 and 5 are missing without having to enter the cardinality of the vaccination in the original data. For this kind of task you usually want to use a left (or right) join. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
Martijn van Oosterhout wrote: Incidently, if you disable the backslash quoting, how does one enter raw binary data including NUL (\0) characters? The bytea type has its own internal quoting/escaping mechanism (which overlaps with the lexer's mechanism in some ways), so entering binary data is not an issue. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Reading from Mysql writting in PGsql
On Sun, Feb 27, 2005 at 03:27:48PM -0800, Mohsen Pahlevanzadeh wrote: Dears,I need to read 1 field with select command from mysql. Then Write it to pgsql. Please guide me. If you need to do this directly, look into dbi-link. http://pgfoundry.org/projects/dbi-link/ There are conversion tools in contrib: my2pg.pl and mysql2pgsql, and of course you can use the database-independent access in your favorite scripting language (DBI.pm in perl, for example) to attach to both databases, then stream from one to the other. HTH :) 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Unicode support on Win32
Caleb wrote: Hi, I am using Windows as a development environment (and database design), and it seems that Unicode is not supported on Win32. Yes, the problem is that we only support UTF-8 and the Win32 collation routines only support UTF-16. You can actually use UTF-8 if you don't care about ordering of the character set, but pginstaller doesn't support those options --- you have to run initdb manually. Is there word on when Unicode support will be implemented on the Windows port of PostgreSQL? I was used to using mySQL and it supported Unicode on Windows properly.. But I suppose that the Windows port of PostgreSQL is still working in progress. It is not a work in progress as far as we are concerned. You have hit a limitation, but it is ready for serious use. On the other hand, could I pick a different charset for now, and when I need to copy this whole database to a production machine (that will use linux) choose the UTF-8 charset ? Yes. I also don't seem to understand why you need to define the whole database as unicode ? Don't you only need unicode for certain fields, and the rest can be Latin1? No, we don't support per-table or per-column encodings yet either. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems with pgcrypto and special characters
On Mon, 2005-02-28 at 18:32 +0100, Markus Wollny wrote:
To get straight to the point, here's my problem:
mypgdb=# select bytea2text(decrypt(encrypt('Tübingen'::bytea,
'mypassphrase'::bytea,'bf'::text),'mypassphrase'::bytea,'bf'::text)) as foo;
foo
-
T\303\274bingen
(1 row)
I have compiled and installed pg_crypto and I'am using the following function
as workaround for a bytea-to-text-cast:
are you sure your problem is with pg_crypto ?
what does this produce:
select bytea2text('Tübingen'::bytea) as foo;
?
have you tried to use encode()/decode() instead ?
untested:
select
decode(
decrypt(
encrypt(
encode('Tübingen','escape') ,
'mypassphrase'::bytea,
'bf'::text
),
'mypassphrase'::bytea,
'bf'::text
)
) as foo;
(sorry for the obsessive indentation)
gnari
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] indexing date_part
Hello all,
I am interested in setting up an index with a date_part as
part of it From what I have been able to find I need to write a function
that will return the date_part that I want I have tried
CREATE FUNCTION month_idxable(date) returns date AS 'SELECT date_part('month', date)
from mchistw' LANGUAGE 'SQL' WITH (iscachable);
And I get: ERROR: parser: parse error at or near
month at character 72
So what am I doing wrong and how would I include the function
into an index?
Thank you all for all of your help!
Re: [GENERAL] Problems with pgcrypto and special characters
Markus Wollny [EMAIL PROTECTED] writes: ... I'am using the following function as workaround for a bytea-to-text-cast: create or replace function bytea2text(bytea) returns text as ' begin return $1; end; ' language plpgsql; That looks like your problem right there. Possibly a binary cast (WITHOUT FUNCTION) would solve your problem, though I doubt it will work well on bytea values containing \0. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Clay Shirky observation regarding MySQL
Hello! Clay Shirky made a comment about MySQL that I thought the PostgreSQL community should be aware of: http://www.shirky.com/writings/situated_software.html It's the section (mostly toward the bottom) entitled, The Nature of Programming, and the Curious Case of MySQL. The whole article is, as normal, interesting and thought-provoking. [Please, Shirky wrote this, not me; however, if he's thinking it, we should know.] B-) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Fast major-version upgrade (was: [GENERAL] postgresql 8.0 advantages)
On Sun, Feb 27, 2005 at 09:27:41PM +0100, Tino Wildenhain wrote: Hi, Am Samstag, den 26.02.2005, 15:35 -0600 schrieb Jim C. Nasby: On Sat, Feb 26, 2005 at 01:27:55AM -0800, Jeff Davis wrote: On Fri, 2005-02-25 at 09:20 -0800, Si Chen wrote: Thanks! Is there any documentation on how to upgrade to 8.0? Is it possible to upgrade from 7.4 to 8.0 while keeping a production database running in place? Or should I install 8.0 in another directory/machine and then restore the database into it? Are there any incompatibilities/modifications to databases from the earlier veresion required? You might want to check out Slony-I http://www.slony.info for the purpose of upgrading. Slony is actually a replication engine, but it can be used to upgrade with little or no downtime. Otherwise, just dump/upgrade/restore like normal. Has anyone tried moving a database from one location to another on the HD? I'd like to use slony to minimize downtime, but I'd also like my data to end up in the same place it is right now when I'm done. I used a straight copy of the filesystem with running database (over the net in my case) and immediately after that, stop the db and rsync for the last changes. This took only 10 minutes (compared to 1.5h for the full filesystem copy) and I could start up the db in new location. this could work for you too. I hadn't thought about using rsync; that's a great idea! Is there somewhere this could be documented? In an FAQ maybe? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 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: [GENERAL] PostgreSQL 8 compiling for Windows
On Feb 28, 2005, at 7:11 AM, Konstantin Danilov wrote: I need to compile postgres 8 for Windows to set up the locale support. The distributive accessible from FTPs does not support it, and I cannot sort Russian letters - that's why.. I have MS Visual Studio 6, but I've never worked with it. So, how can I do everything? I don't think MS Visual Studio is supported. You need to use MingW: http://www.postgresql.org/files/documentation/faqs/FAQ_MINGW.html Once you have that installed, building is the same as on Unix: conigure, make, etc. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] indexing date_part
In this case you just have a syntax error in your function. You have
tried to use a single-quoted string inside of a single-quoted string.
Escape the single quotes by writing '' instead of ' inside the
function definition.
For example:
CREATE FUNCTION month_idxable(date) returns date AS 'SELECT
date_part(''month'', date) from mchistw' LANGUAGE 'SQL' WITH
(iscachable);
After creating the function, just create the functional index and you
should be on the right track.
Regards,
Jeff Davis
On Mon, 2005-02-28 at 14:35 -0500, DEV wrote:
Hello all,
I am interested in setting up an index with a date_part as part of it
From what I have been able to find I need to write a function that
will return the date_part that I want I have tried
CREATE FUNCTION month_idxable(date) returns date AS 'SELECT
date_part('month', date) from mchistw' LANGUAGE 'SQL' WITH
(iscachable);
And I get: ERROR: parser: parse error at or near month at character
72
So what am I doing wrong and how would I include the function into an
index?
Thank you all for all of your help!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Disabling triggers in a transaction
Jay Guerette [EMAIL PROTECTED] writes: If I disable INSERT and UPDATE triggers inside a transaction; by setting and resetting reltriggers in pg_class; am I correct in thinking that this will disable triggers globally for that table for the duration of that transaction? Not if you never commit the pg_class row in that state. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Fast major-version upgrade (was: [GENERAL] postgresql 8.0 advantages)
On Mon, Feb 28, 2005 at 01:36:59PM -0600, Jim C. Nasby wrote: I used a straight copy of the filesystem with running database (over the net in my case) and immediately after that, stop the db and rsync for the last changes. This took only 10 minutes (compared to 1.5h for the full filesystem copy) and I could start up the db in new location. this could work for you too. I hadn't thought about using rsync; that's a great idea! Is there somewhere this could be documented? In an FAQ maybe? It works only in the special case where the PostgreSQL version number is the same and you're running on the same platform. How often are you transferring databases like that. Even transferring from i386 to amd64 wouldn't work like this AFAIUI. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpmARQ1jqJFZ.pgp Description: PGP signature
Re: [GENERAL] Referencing created tables fails with message that
On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote: - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience with these led me to beleive that SQL was case insensitive. In fact, I was so sure of it that a case problem just never occured to me. Case isn't a problem if you don't quote identifiers because unquoted identifiers will be folded to lower case, both when you create them and then later when you reference them. For example, if you create a table with this command: CREATE TABLE XYZ (I INTEGER); then the system folds XYZ and I to lower case: \dt List of relations Schema | Name | Type | Owner +--+---+--- public | xyz | table | mfuhr \d xyz Table public.xyz Column | Type | Modifiers +-+--- i | integer | The following queries should all work (not an all-inclusive list): SELECT I FROM XYZ; SELECT i FROM xyz; SELECT I FROM Xyz; select i from xyz; sEleCt i fRoM xYz; But if you quote identifiers when you create them, then they'll be created with the exact case you specified and you'll need to quote them whenever you use them: CREATE TABLE XYZ (I INTEGER); \dt List of relations Schema | Name | Type | Owner +--+---+--- public | XYZ | table | mfuhr \d XYZ Table public.XYZ Column | Type | Modifiers +-+--- I | integer | SELECT I FROM XYZ; -- works SELECT I FROM XYZ; -- fails -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible to run the server with ANSI/ISO string
On Mon, 2005-02-28 at 10:13 -0700, Ken Johanson wrote: I'm a little worried about PostgreSQL having the same problems as PHP. In PHP, every time you want to download an application, you never see This application works on php 4+. Instead, you see This application works on php4+ with the following config options set long list. Sometimes these applications have conflicting requirements. From an administrator's standpoint, it's a mess. In PostgreSQL I think it would actually be much worse. Right now many applications build a PostgreSQL layer, but will they build two? I think this would cause a divide in the application support (some for config option A some for config option B) in the already smaller-than-we'd-like set of software that supports PostgreSQL. Regards, Jeff Davis There's certainly two perspectives to this. The one you present is certainly valid, but consider the bigger picture... This application requires the following databases: Oracle versionX, MY SQL version X, Mysql version 5.2 with the no-backslashes option, UltraDB version x Notice the lack of PG - [snip] A valid point: that's certainly the issue we're dealing with here. I think most people agree that being SQL compliant is good. The question is: is it worth the pain for existing users? A configurable option does not make the pain disappear. Admins are forced to choose one side (either sql compliant or c style) and exclude the other applications. Any app developer that wants to support pre-8.1 apps will have to have a c-style app available. So even if you nip it in the bud, it's not really gone yet because app developers want to support old versions of postgres. I know if we added the option and deprecated the old style, I would be forced to choose between using deprecated syntax that may not be supported for long, or doing a lot of work to convert and retest applications. Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. I think you may have misunderstood what I meant. I am not suggesting that we don't change the database at all between versions, my argument was showing the difficulties when one version has many different shapes due to many incompatible options. Regards, Jeff Davis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problems with pgcrypto and special characters
Hi!
-Original Message-
From: Ragnar Hafsta [mailto:[EMAIL PROTECTED]
are you sure your problem is with pg_crypto ?
what does this produce:
select bytea2text('Tbingen'::bytea) as foo;
?
Well I'm sure it's not WITH pgcrypto but with actually using pgcrypto in
conjunction with UTF-8 encoded text. This function doesn't do anything but
replace a bytea::text-cast.
have you tried to use encode()/decode() instead ?
untested:
select
decode(
decrypt(
encrypt(
encode('Tbingen','escape') ,
'mypassphrase'::bytea,
'bf'::text
),
'mypassphrase'::bytea,
'bf'::text
)
) as foo;
Yes, and that doesn't work either:
mypgdb=# select decode(encode('Tbingen'::text::bytea,'escape'),'escape');
decode
-
T\303\274bingen
(1 row)
But I just found the bugger - we both confused encode and decode :)
mypgdb=# select encode(decode('Tbingen','escape'),'escape');
encode
--
Tbingen
(1 row)
Now using pgcrypto works, too:
mypgdb=# select
encode(decrypt(encrypt(decode('Tbingen'::text,'escape'),'mypassphrase','bf'),'mypassphrase','bf'),'escape');
encode
--
Tbingen
(1 row)
Thanks nevertheless, this was exactly the push in the right direction that I
needed!
Kind regards
Markus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] indexing date_part
Jeff Davis [EMAIL PROTECTED] writes:
In this case you just have a syntax error in your function. You have
tried to use a single-quoted string inside of a single-quoted string.
Escape the single quotes by writing '' instead of ' inside the
function definition.
BTW, if you were working in a more recent version of Postgres, you
wouldn't need the wrapper function in the first place. This works
fine in 7.4 and later:
regression=# create table foo(f1 date);
CREATE TABLE
regression=# create index fooi on foo(date_part('month', f1));
CREATE INDEX
regression=#
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Problems with pgcrypto and special characters
Hi!
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Possibly a binary cast (WITHOUT FUNCTION) would solve your problem,
though I doubt it will work well on bytea values containing \0.
Thanks, I've been a bit thick here, but I just found the solution to my problem
- and that doesn't need this awkward function nor any type of extra WITHOUT
FUNCTION casts - just decode and encode, alas in exactly the opposite order
than I originally expected.
mypgdb=# select decode('Tbingen'::text,'escape');
decode
-
T\303\274bingen
(1 row)
mypgdbe=# select encode('T\303\274bingen','escape');
encode
--
Tbingen
(1 row)
I think this should be safe for any kind of bytea value.
Kind regards
Markus
---(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: [GENERAL] Unicode support on Win32
Bruce Momjian wrote: Caleb wrote: Hi, I am using Windows as a development environment (and database design), and it seems that Unicode is not supported on Win32. Yes, the problem is that we only support UTF-8 and the Win32 collation routines only support UTF-16. You can actually use UTF-8 if you don't care about ordering of the character set, but pginstaller doesn't support those options --- you have to run initdb manually. I know this is the wrong list for it, but here are some thoughts about solutions: 1. Convert to UTF-16 - Windows give you builtin functions to do that. 2. Use another library set (ICU?) Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] to_char bug?
Ben Trewern [EMAIL PROTECTED] writes: It seems that to_char(1, '000') gives a string 001 with a space in front. Is this a bug? No. Possibly you want 'FM000'. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Clay Shirky observation regarding MySQL
On Mon, Feb 28, 2005 at 01:46:16PM -0600, [EMAIL PROTECTED] wrote: Hello! Clay Shirky made a comment about MySQL that I thought the PostgreSQL community should be aware of: http://www.shirky.com/writings/situated_software.html It's the section (mostly toward the bottom) entitled, The Nature of Programming, and the Curious Case of MySQL. The whole article is, as normal, interesting and thought-provoking. Interesting article, but w.r.t. to the MySQL statement, I read: If you don't need any of the things that databases are good for (ACID, transactions, triggers, views) then MySQL is an acceptable choice. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp5NHmOFS5At.pgp Description: PGP signature
[GENERAL] PGconn
Dears,I need to connect to the PostgreSQL without choosing database in C. Please guide me.. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PGconn
On Mon, 2005-02-28 at 16:43, Mohsen Pahlevanzadeh wrote: Dears,I need to connect to the PostgreSQL without choosing database in C. Please guide me.. You have to choose a database. You can choose template1 which should always be there. Connections in libpq are to specific databases. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] vacuum_cost_delay VACUUM holding locks on GIST indexes
When you VACUUM a table with postgis indexes (perhaps GIST indexes
in general?) it seems a lock is held on the table. Setting
vacuum_cost_delay seems to make vacuum hold this lock much longer.
Is this true?
If so, I assume that's not desirable behavior, right? It makes
autovacuum harder to use on tables that have these indexes.
Any clever workarounds?
Ron
fli-lin1 /home/pg while (1)
while? echo explain analyze SELECT * from lines2 WHERE the_geom
setSRID('BOX3D(-84.31043 30.44341,-84.2954 30.45372)'::BOX3D, -1 ); | psql fli
fli | grep runtime
while? sleep 5
while? end
Total runtime: 23.355 ms
Total runtime: 32.276 ms [ vacuum verbose starts ]
Total runtime: 36.080 ms
Total runtime: 28.373 ms
Total runtime: 114679.281 ms [ bad but not horrible]
Total runtime: 30.823 ms
[...]
Total runtime: 22.867 ms [ set vacuum_cost_delay=20]
Total runtime: 22.808 ms
Total runtime: 23.288 ms [ vacuum vebose again ]
Total runtime: 23.366 ms
[ dozens of lines ]
Total runtime: 23.337 ms
Total runtime: 764133.163 ms [ YIPES ]
Total runtime: 23.722 ms
fli=# select * from pg_locks;
relation | database | transaction | pid | mode | granted
--+--+-+---+--+-
36677268 |17230 | | 29039 | AccessShareLock | t
36677268 |17230 | | 29039 | ShareUpdateExclusiveLock | t
36677268 |17230 | | 29039 | AccessExclusiveLock | t
33620188 |17230 | | 29039 | ShareUpdateExclusiveLock | t
| | 66414 | 30758 | ExclusiveLock| t
36677268 |17230 | | 30731 | AccessShareLock | f
33620188 |17230 | | 29039 | ShareUpdateExclusiveLock | t
36677269 |17230 | | 29039 | ShareUpdateExclusiveLock | t
16839 |17230 | | 30758 | AccessShareLock | t
| | 66412 | 30731 | ExclusiveLock| t
33620188 |17230 | | 30731 | AccessShareLock | t
| | 66372 | 29039 | ExclusiveLock| t
(12 rows)
fli=#
fli=# select * from pg_class where oid=36677268;
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
--+--+-+--+---+-+---+--++---+---+-+-+-+--+---+-+--+--+-+++-++
tmp_lines2__gist | 2758256 | 0 | 100 | 783 |36677268 |
0 |34623 | 6.1128e+06 | 0 | 0 | f
| f | i |1 | 0 | 0 |0 |
0 | 0 | f | f | f | f |
(1 row)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Vacuum time degrading
We've been doing a vacuum every night. With about 254 million rows, it was taking abut 0.8 hours. Now a few months later with about 456 million rows it is taking 4.8 hours. I did check the archives and didn't find anything like this. Why is the vacuum time not going up linearly? Since vacuum has to sequentially read the entire database, I would have expected a linear increase - about 1.5 hours now. There are currently no deletes or modifies to the database - only inserts. This is on PostgreSQL 7.4.5, RedHat ES 3.0. Wes ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Possible to run the server with ANSI/ISO string
[snip] I think most people agree that being SQL compliant is good. The question is: is it worth the pain for existing users? My guess is that it is worth it, if the users are given the discretion of treading that water.. and to save them future pain by encouraging them to migrate toward 'other-db' compatibility (or merely to migrate to PreparedStatement to eliminate worry and *insure* interop). But where things are right now, I *know* allot of apps specially coded for PG (or mysql) --- using functions like PHPs escapeCslashes()... so they are NOT compatible apps with other DBs. So making the change would at least raises author awareness to use PreparedStatements instead (half the battle is won then because when a PS admin turns on the new escape, their apps still works correctly), or stop using escapeCslashes in favor of a sql-escape function (yes, not 'old pg' compatible, but be able to claim interop with other dbs). A configurable option does not make the pain disappear. Admins are forced to choose one side (either sql compliant or c style) and exclude the other applications. Any app developer that wants to support pre-8.1 apps will have to have a c-style app available. So even if you nip it in the bud, it's not really gone yet because app developers want to support old versions of postgres. As was mentioned earlier, this may not be too much of an issue if the new drivers supported an option in the getConnection call that turned on the new escape, otherwise leaving old escape turned on by default. Sort of like the jdbc version/conformance level that jdbc drivers can report through function calls. In fact PG could forever use the old style escapes by default, except when a modern driver connected to it and they both agree to use the new style. I know if we added the option and deprecated the old style, I would be forced to choose between using deprecated syntax that may not be supported for long, or doing a lot of work to convert and retest applications. Yes - and your app would be inter operable with Oracle, Sybase, etc and have a wider audience (moot point if you use prepared statements obviously) especially in the enterprise... Very worthwhile, imo. Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. I think you may have misunderstood what I meant. I am not suggesting that we don't change the database at all between versions, my argument was showing the difficulties when one version has many different shapes due to many incompatible options. Sorry, I misunderstood. Your point is well taken, and I agree. Thank you, ken ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] invalid multibyte character for locale
L.S.
I have a database created on:
db=# select version();
version
-
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
The initdb was done using no-locale and unicode as default encoding, the
particular database itself is indeed encoded as UNICODE.
Due to a buggy glibc, the following patch was applied to this install in order
to avoid a crash on things like 'upper(string)':
--- oracle_compat.c_origMon Dec 6 22:14:11 2004
+++ oracle_compat.c Mon Dec 6 22:14:24 2004
@@ -43,7 +43,7 @@
* We assume if we have these two functions, we have their friends too, and
* can use the wide-character method.
*/
-#if defined(HAVE_WCSTOMBS) defined(HAVE_TOWLOWER)
+#if defined(HAVE_WCSTOMBS) defined(HAVE_TOWLOWER) FALSE
#define USE_WIDE_UPPER_LOWER
#endif
The database on this machine was dumped and then restored on another, which
has a more recent installation of Slack on it:
db=# select version();
version
PostgreSQL 8.0.1 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
Again, the initdb on this machine was done using no-locale and unicode as
default encoding, the particular database obviously is also encoded as
UNICODE.
On the second machine, I'm now getting the following:
db=# select 'JTERBOG';
?column?
--
JTERBOG
(1 row)
db=# select lower('JTERBOG');
ERROR: invalid multibyte character for locale
HINT: The server's LC_CTYPE locale is probably incompatible with the database
encoding.
As far as I can tell, this didn't happen with v8.0.0, but I'm afraid I can't
be totally sure about that. Obviously, the error doesn't occur on the first
machine due to the hack needed for the buggy glibc.
I'd appreciate a pointer as to what is causing this. It 'shouldn't' be the
hack nor the dump/restore cycle, but...?
TIA.
--
Best,
Frank.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL]
Dears,I need to create an pgsql user in C. Please guide me Yours,Mohsen. ---(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: [GENERAL] invalid multibyte character for locale
Apparently your hack does not kill #define USE_WIDE_UPPER_LOWER.
BTW, the current code for upper/lower etc. seems to be broken. The
exact problem you have are happening in Japanese encodings too(EUC_JP)
too. PostgreSQL should not use wide-character method if LC_CTYPE is C.
--
Tatsuo Ishii
L.S.
I have a database created on:
db=# select version();
version
-
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
The initdb was done using no-locale and unicode as default encoding, the
particular database itself is indeed encoded as UNICODE.
Due to a buggy glibc, the following patch was applied to this install in
order
to avoid a crash on things like 'upper(string)':
--- oracle_compat.c_origMon Dec 6 22:14:11 2004
+++ oracle_compat.c Mon Dec 6 22:14:24 2004
@@ -43,7 +43,7 @@
* We assume if we have these two functions, we have their friends too, and
* can use the wide-character method.
*/
-#if defined(HAVE_WCSTOMBS) defined(HAVE_TOWLOWER)
+#if defined(HAVE_WCSTOMBS) defined(HAVE_TOWLOWER) FALSE
#define USE_WIDE_UPPER_LOWER
#endif
The database on this machine was dumped and then restored on another, which
has a more recent installation of Slack on it:
db=# select version();
version
PostgreSQL 8.0.1 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
Again, the initdb on this machine was done using no-locale and unicode as
default encoding, the particular database obviously is also encoded as
UNICODE.
On the second machine, I'm now getting the following:
db=# select 'JÜTERBOG';
?column?
--
JÜTERBOG
(1 row)
db=# select lower('JÜTERBOG');
ERROR: invalid multibyte character for locale
HINT: The server's LC_CTYPE locale is probably incompatible with the
database
encoding.
As far as I can tell, this didn't happen with v8.0.0, but I'm afraid I can't
be totally sure about that. Obviously, the error doesn't occur on the first
machine due to the hack needed for the buggy glibc.
I'd appreciate a pointer as to what is causing this. It 'shouldn't' be the
hack nor the dump/restore cycle, but...?
TIA.
--
Best,
Frank.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL]
On Mon, Feb 28, 2005 at 03:28:24PM -0800, Mohsen Pahlevanzadeh wrote: Dears,I need to create an pgsql user in C. Perhaps you're looking for the libpq or ECPG documentation: http://www.postgresql.org/docs/8.0/interactive/libpq.html http://www.postgresql.org/docs/8.0/interactive/ecpg.html Please guide me A good way to learn about PostgreSQL is to study the documentation. Go through the Tutorial, then at least skim the rest so you'll know what capabilities PostgreSQL has and where you can learn more about them. http://www.postgresql.org/docs/8.0/interactive/tutorial.html http://www.postgresql.org/docs/ -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Vacuum time degrading
Wes [EMAIL PROTECTED] writes: Why is the vacuum time not going up linearly? I'm betting that the database is suffering from substantial bloat, requiring VACUUM to scan through lots of dead space that wasn't there before. Check your FSM settings (the tail end of the output from a full-database VACUUM VERBOSE command would give some info about what you need). If you are suffering bloat, the fastest route to a solution would probably be to CLUSTER your larger tables. Although VACUUM FULL would work, it's likely to be very slow. There are currently no deletes or modifies to the database - only inserts. You *certain* about that? It's hard to see how the vacuum time wouldn't be linear in table size if there's nothing to do and no dead space. Again, VACUUM VERBOSE info would be informative (it's sufficient to look at your larger tables for this). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] vacuum_cost_delay VACUUM holding locks on GIST indexes
Ron Mayer [EMAIL PROTECTED] writes: When you VACUUM a table with postgis indexes (perhaps GIST indexes in general?) it seems a lock is held on the table. GIST isn't concurrent-safe. Any clever workarounds? Fix GIST ;-) You could shorten the intervals for which the lock is held by reducing vacuum_mem, but this might be counterproductive overall. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] invalid multibyte character for locale
Tatsuo Ishii [EMAIL PROTECTED] writes: BTW, the current code for upper/lower etc. seems to be broken. The exact problem you have are happening in Japanese encodings too(EUC_JP) too. PostgreSQL should not use wide-character method if LC_CTYPE is C. Yeah, we came to that same conclusion a few days ago in another thread. I am planning to install the fix but didn't get to it yet. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pgpool fundamental questions
Pgpool is a real cool project, I really hope it keeps evolving. I have a couple basic/fundamental questions that I hoping to get some clarification on. Question 1: If pgpool is used in conjuntion with slony or a another replication it can be used only for its loadbalancing and failover. I am correct like this? What else needs to be done to set in failover? set the backends backend_host_name =master secondary_backend_host_name=slave set it to lb on and replication off... load_balance_mode=true replication_mode=false set the health checks for failover. health_check_timeout=20 health_check_period=20 health_check_user=user You need to set master_slave_mode to true. Question 2: In the case that the master goes down pgpool would failover to the slave/secondary. What would be the logical steps to bring it back to normal. Can this be down with no downtime with both? 1) Turn off health check or weight the weight_master =0 so when you bring the master back load does not go to it when you bring the db backup. or does pgpool leave it down automatically until you restart pgpool? Yes. 2) Shutdown replication on the master. Bring back the master backup. 3) rsynch the data from the slave to the master??? 4) turn replication? It seems these steps OK. But you are going to use Slony-I with pgpool, no? Then you might want to sync DBs using Slony-I? Question 3: Is there anyway to make pgpool loadbalance certain select queries to the slave such as queries to a particular table? Is that a future feature planned? Since pgpool does not parse queries, probably it would not happen in the future. Ideas are welcome how to know a query is accessing particular table without parsing it. -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] PGP / GnuPg signed MD5 and SHA1 checksums for PostgreSQL version 8.0.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Source code MD5 and SHA1 checksums for PostgreSQL 8.0.1 For instructions on how to use this file, please see: http://www.gtsm.com/postgres_sigs.html ## Created with md5sum: 075ac81c865b0af865459260bf1ca890 postgresql-8.0.1.tar.bz2 b8f8edce5b29bcf785251693c6225885 postgresql-base-8.0.1.tar.bz2 f51624e70b9fca4ebd6bb56ba8587b97 postgresql-docs-8.0.1.tar.bz2 130474770b88fd059c2dff457fb06b0a postgresql-opt-8.0.1.tar.bz2 ad7baaaf9cf417b9ddd1512f1dd7fb10 postgresql-test-8.0.1.tar.bz2 0af34d782e041438e68395e20c67fa08 postgresql-8.0.1.tar.gz e4e4db83b2bcc28e819f2c5869baed6b postgresql-base-8.0.1.tar.gz d15fd0653aab6b3d2dfa969bca593303 postgresql-docs-8.0.1.tar.gz 18eac40a898829ee74c711677523c6e4 postgresql-opt-8.0.1.tar.gz 5dd506a5e761b5d37749ba92c378ac7b postgresql-test-8.0.1.tar.gz ## Created with sha1sum: 42a16fe13a6271b1612fb0a9f41f7da0a2e307b6 postgresql-8.0.1.tar.bz2 651a488321074489e0682dadb66dfb2bf4120256 postgresql-base-8.0.1.tar.bz2 26393da55b40481ee9f541973b33bbaa76fce5ca postgresql-docs-8.0.1.tar.bz2 4c7356a8bcbb8bd35c1f7c9c062e07bcfaa03e09 postgresql-opt-8.0.1.tar.bz2 b072478ddb0b575ed9b8bf22aacb12b06c84d07c postgresql-test-8.0.1.tar.bz2 48794bffaf934f7eef2a781c9d3dbfe2f0f9245f postgresql-8.0.1.tar.gz 28887661e205bb625a8dc5403c2c852f9bc96d60 postgresql-base-8.0.1.tar.gz caa04c0cb54dbdc97802f5c5e195dc534b0ca099 postgresql-docs-8.0.1.tar.gz 9257f362f49f4ae82ea8542d9cd3085a1e34d6d2 postgresql-opt-8.0.1.tar.gz 94c560ffb22966805075daaf8051936ce1056d90 postgresql-test-8.0.1.tar.gz - -- Greg Sabino Mullane [EMAIL PROTECTED] http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCI9PevJuQZxSWSsgRAkOHAKDV9/qh/kFzC44/BEJIowG9iqJieACgwgjR b3Uj9Pp1/1e5imxb8vOFYZo= =XYzR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] PGP / GnuPg signed MD5 and SHA1 checksums for PostgreSQL version 8.0.0
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Source code MD5 and SHA1 checksums for PostgreSQL 8.0.0 For instructions on how to use this file, please see: http://www.gtsm.com/postgres_sigs.html ## Created with md5sum: 3fe6bb504a6457daa80bc32daf10122e postgresql-8.0.0.tar.bz2 57984b8b016501ee920e95af6edc7145 postgresql-base-8.0.0.tar.bz2 9deb6ac959e367cabe882d77287a6b31 postgresql-docs-8.0.0.tar.bz2 11f91f2bf1697d7c8f340151208e0c3e postgresql-opt-8.0.0.tar.bz2 0281020c9a1a3c342edaf2d9586ec07e postgresql-test-8.0.0.tar.bz2 8cea817bc92bcc5933fa3cc8e8361e1f postgresql-8.0.0.tar.gz 626632456934b706dab055d3b66a90e6 postgresql-base-8.0.0.tar.gz 8c4c81e589b6a0a230cc0b28a007c944 postgresql-docs-8.0.0.tar.gz 77dfd85336230566ad3cd982eb359946 postgresql-opt-8.0.0.tar.gz a2304efbd2bcddac5bb1ae7be7651eea postgresql-test-8.0.0.tar.gz ## Created with sha1sum: b3c0a557e431de7d2b15385eda222b0ba2cc213f postgresql-8.0.0.tar.bz2 27148c009961d5cc95a25f886619fbfd70b10b6f postgresql-base-8.0.0.tar.bz2 f7f058ca936ad13c8bd14725112ae0bb351ad8d9 postgresql-docs-8.0.0.tar.bz2 b7fdeb86e55eb30a8a8c1c6dbe473fd6a1b40e62 postgresql-opt-8.0.0.tar.bz2 6b5c6d8015d91391241b4f32c1fc4c16e5d45743 postgresql-test-8.0.0.tar.bz2 4037035b46ccd34044dd1258bd34a53acced9814 postgresql-8.0.0.tar.gz ab1f73b843a53ec1a0deaf2ce321d58658b31855 postgresql-base-8.0.0.tar.gz 10575010795a063b511bb76e8d38006c85722615 postgresql-docs-8.0.0.tar.gz ef5ad8caf58285f8d91ecdf9d1155331e2719798 postgresql-opt-8.0.0.tar.gz f9d0ddf0e76f5eaafc482f8173d51e1fbd3fd197 postgresql-test-8.0.0.tar.gz - -- Greg Sabino Mullane [EMAIL PROTECTED] http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCI9DlvJuQZxSWSsgRAlaTAKD+jv5zdf3XqFth/h6oV5qdXqHu8gCgwDK+ iIDHU05RdQXcf/LwMQ+b11A= =MNwY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] vacuum_cost_delay VACUUM holding locks on GIST
On Mon, 28 Feb 2005, Tom Lane wrote:
You could shorten the intervals for which the lock is held by reducing
vacuum_mem, but this might be counterproductive overall.
Does this work?
I just tried:
setting vacuum_mem=1024
setting vacuum_cost_delay=10
ran a while loop that repeatedly executes a simple
select statement that usually takes 0.03 seconds
and it still gave me a single extremely slow (7 minutes
long instead of 25 milliseconds) query that lasted until
after the ...GIST... line was shown on vacuum verbose output.
It feels like even with the minimal vacuum_mem it spent a very
long time (430 seconds) wihtout releasing the lock on the GIST
index for my pretty large (relpages=94371 pages) table.
Or do I have something else broken? Shown below is the output
of a while loop of a simple query using this index; and the
\d table output.
Ron
=
== Loop of small select()s
== with vacuum_mem=1024 and vacuum_cost_delay=10
=
%while (1)
while? echo explain analyze SELECT * from lines2 WHERE the_geom
setSRID('BOX3D(-84.31043 30.44341,-84.2954 30.45372)'::BOX3D, -1 ); | psql fli
fli | grep runtime
while? sleep 5
while? end
Total runtime: 24.375 ms /* set vacuum_mem=1024 */
Total runtime: 24.303 ms
Total runtime: 25.370 ms /* vacuum verbose */
Total runtime: 27.332 ms
Total runtime: 26.628 ms
Total runtime: 26.001 ms
[many more like this]
Total runtime: 27.437 ms
Total runtime: 24.679 ms
Total runtime: 26.628 ms
Total runtime: 431265.868 ms
Total runtime: 24.419 ms /* INFO: index tmp_lines2__gist ... */
Total runtime: 24.375 ms
Total runtime: 24.303 ms
Total runtime: 24.294 ms
Total runtime: 24.235 ms
=
== \d for the table.
=
fli=# \d lines2;
Table tmp.lines2
Column | Type | Modifiers
---+--+---
tigerfile | integer |
tlid | integer |
cfcc | character varying(3) |
name | text |
the_geom | geometry |
Indexes:
lines2__tlid btree (tlid)
tmp_lines2__gist2 gist (the_geom)
Check constraints:
enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'LINESTRING'::text OR the_geom IS NULL)
enforce_dims_the_geom CHECK (ndims(the_geom) = 2)
enforce_srid_the_geom CHECK (srid(the_geom) = -1)
fli=#
---(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
[GENERAL] Is any limitations in PostgreSQL?
Hi all, In the document of PostgreSQL, I can't find any restriction of database. I want to know something as following: 1. What's the restriction of database file? How big it can be(for exmaple, 10TB?). 2. Is there any restriction for the record and the document? What kind of record and document can be add to database. 3. What's the restriction for the table and the collection? Thanks for any replier Yu Jie [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Is any limitations in PostgreSQL?
On Mon, Feb 28, 2005 at 07:49:54PM -0800, Yu Jie wrote: 1. What's the restriction of database file? How big it can be(for exmaple, 10TB?). See What is the maximum size for a row, a table, and a database? in the FAQ: http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.4 2. Is there any restriction for the record and the document? What kind of record and document can be add to database. See the aforementioned FAQ and the Data Types chapter in the documentation: http://www.postgresql.org/docs/8.0/interactive/datatype.html In addition to the built-in types, users can create their own types. 3. What's the restriction for the table and the collection? See the aforementioned FAQ. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] vacuum_cost_delay VACUUM holding locks on GIST indexes
Ron Mayer [EMAIL PROTECTED] writes: On Mon, 28 Feb 2005, Tom Lane wrote: You could shorten the intervals for which the lock is held by reducing vacuum_mem, but this might be counterproductive overall. Does this work? [ thinks about it... ] No, probably not; sorry for the misinformation. Cutting vacuum_mem will reduce the number of index tuples that are to be deleted during any one scan of the index. But if the index is large, it's probably the scanning time and not the deletion time that is dominant. 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: [GENERAL] multicolumn GIST index question
Ron Mayer wrote: Did anyone get multi-column GIST indexes working using both the gist_btree and postgis modules? Not quite stable yet. It seems to work fine for me on small test cases (shown at the bottom), but seems to crash my database for large ones. Any advice is welcome - including pointers to better lists to ask questions like this. For me it seems to work only if the geom-column is the first column in a multicolumn-index. Haven't investigated further so far... HTH Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Referencing created tables fails with message that
OK, I see. I first used the Postgres admin tool in webmin (Linux/unix web admin tool) to create the first table. I guess that it quoted my Project table. Anyhow, I dropped the table from the same tool, and then went to DBVisualizer and recreated all my tables there, using uppercase for all table and field names, and then it worked fine. But as you explained below, I guess it would not have mattered even if i called my first table PrOjEcT since it would be changed to project. I also realize that it is much safer to actually write the SQL yourself than let a tool produce it for you! /Tommy Michael Fuhr wrote: On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote: - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience with these led me to beleive that SQL was case insensitive. In fact, I was so sure of it that a case problem just never occured to me. Case isn't a problem if you don't quote identifiers because unquoted identifiers will be folded to lower case, both when you create them and then later when you reference them. For example, if you create a table with this command: CREATE TABLE XYZ (I INTEGER); then the system folds XYZ and I to lower case: \dt List of relations Schema | Name | Type | Owner +--+---+--- public | xyz | table | mfuhr \d xyz Table public.xyz Column | Type | Modifiers +-+--- i | integer | The following queries should all work (not an all-inclusive list): SELECT I FROM XYZ; SELECT i FROM xyz; SELECT I FROM Xyz; select i from xyz; sEleCt i fRoM xYz; But if you quote identifiers when you create them, then they'll be created with the exact case you specified and you'll need to quote them whenever you use them: CREATE TABLE XYZ (I INTEGER); \dt List of relations Schema | Name | Type | Owner +--+---+--- public | XYZ | table | mfuhr \d XYZ Table public.XYZ Column | Type | Modifiers +-+--- I | integer | SELECT I FROM XYZ; -- works SELECT I FROM XYZ; -- fails ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
