Re: [GENERAL] psql command line editor
Bob Gobeille wrote: I use PSQL to set my editor to vi. This works as expected in psql, \e brings up vi and I edit away. Is there any way to set my psql command line editor to also use vi (just like I do with set -o vi in bash)? I can't find this in the docs. I have in my .inputrc this line: set editing-move vi and it works like a charm. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql command line editor
Bob Gobeille wrote: I have in my .inputrc this line: set editing-move vi and it works like a charm. I tried this and can't get it to work. 1) I tried putting that in my .bashrc script and sourcing the file (and also logging in again) 2) I did the set on the command line and then started psql No luck. Try reading my first sentence. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.4 rpm packaging problem?
Devrim GÜNDÜZ wrote: On Sun, 2009-08-16 at 14:59 -0600, Stuart McGraw wrote: Second, after getting the postgresql-docs package installed by just downloading and installing the right rpm, I notice the FAQ subdirectory contains the FAQ in many languages, but not english. Intentional? English FAQ moved to wiki -- it is not even in 8.4.0 tarball. That's why RPM's don't ship it. Huh, but the tarball does not contain the FAQs in other languages either. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.4 rpm packaging problem?
Devrim GÜNDÜZ wrote: On Sun, 2009-08-16 at 18:35 -0400, Alvaro Herrera wrote: Huh, but the tarball does not contain the FAQs in other languages either. See doc/src/FAQ directory in 8.4.0 tarball. Hmm, this is strange -- the directory is not there in CVS ... /me checks CVS history Oh, I see -- the files were removed after the release of 8.4.0 apparently :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generating random unique alphanumeric IDs
Ivan Sergio Borgonovo escribió: Sometimes ago Daniel Verite posted an implementation of a fiestel cipher in plpgsql. It's in the wiki, in the Snippets area. wiki.postgresql.org/wiki/Snippets (pseudo encrypt or something like that I think it's called) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] licensing/distribution of DLL's question
Jim Michaels wrote: I wrote a postgres embedded application that uses libpq and requires the postgres DLL's in the bin directory to run. I am curious if I am allowed to package my app with the DLL's without distributing the entire postgres database application, though I could include all of postgres. You can do whatever suits your fancy. It is BSD-licensed. Just don't sue us and we're all set. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] totally different plan when using partitions
Scara Maccai wrote: explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73_0610_1 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13_0610_1 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime anddata1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 explain analyze: http://explain-analyze.info/query_plans/3805-query-plan-2509 same query, but using postgresql's partition pruning (2): explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime anddata1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 Huh, clearly not the same query (you're using the partition directly in the first query) ... Doing two changes at once is not helping your case. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
Sim Zacks wrote: According to the documentation, you can pass multiple parameters into an aggregate function, but it only stores one value. What I am trying to do is sum a quantity field, but it also has units that need to be converted. Have you seen Martijn van Oosterhout's tagged types? http://svana.org/kleptog/pgsql/taggedtypes.html -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] totally different plan when using partitions
Scara Maccai wrote: Huh, clearly not the same query (you're using the partition directly in the first query) ... Doing two changes at once is not helping your case. Sorry, I don't understand... of course I used the partition directly in the first query... it's the difference between the two... what I don't like is that since the tables used are in fact the same, the plan shouldn't be that different. I misread your original email to say that you were changing the parameter. What version are you using? Also, please post the table definitions (preferably in pg_dump -s format) My conclusion is that the planner thinks there could be some data in the root partition, even if that will always be empty. What I would like is a way to tell Postgres hey, don't even look at the root table. That's just a placeholder for the partitions. It will never contain any data when I create the tables. Otherwise the planner might get fooled by an empty table index scan in a loop (which is what happens here), thinking that that will take time. I'm not sure I agree with your assessment of the problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trouble building pgbench on CentOS 5.3: /usr/bin/ld: cannot find -lpgport
Tom Lane escribió: Aleksey Tsalolikhin atsaloli.t...@gmail.com writes: Hi. I am trying to build pgbench on CentOS 5.3 x86_64. make complains that it cannot find -lpgport # cd contrib # make all You need to make the rest of the tree first. Or at least the src/port/ part. Sounds like a makefile bug to me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does PERFORM hold a lock?
Peter Headland wrote: If I do the following in a pl/pgsql function, will an update lock be held until the end of the transaction? PERFORM c FROM t FOR UPDATE; Yes. (PERFORM is translated to SELECT by the plpgsql engine, so to the SQL executor this is the same as SELECT FOR UPDATE). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sequence Not created with pg_dump
sw...@opspl.com wrote: Seems unlikely (IOW, if so, you've found a bug no one else has ever seen). Maybe they are being created implicitly by SERIAL column declarations? Yeah they are created by the Serial column . He means: are they output as SERIAL columns in the dump too? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] parameters in functions and overlap with names of columns
Ivan Sergio Borgonovo wrote: On Tue, 4 Aug 2009 16:01:58 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: 2009/8/4 Ivan Sergio Borgonovo m...@webthatworks.it: Is there another way other than just simply rename the variable? yes - the most common is an using of prefix '_' for local plpgsql variables. Other possibility is using qualified names. Just to be sure... by qualified names you mean schema qualified name or table qualified names in case of columns... right... In this case I think it also means you can qualify the variable names with the function name; and/or declare named blocks inside the function, and qualify the variables with the block name. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding UTF8 has no equivalent in LATIN2
Andreas Kalsch wrote: The function convert_to(string text, dest_encoding name) will throw an error and so break my application when not supported characters are included in the unicode string. So what can I do - to filter characters out which have no counterpart in the latin codesets - or to simple ignore wrong characters? Perhaps this is useful: http://wiki.postgresql.org/wiki/Strip_accents_from_strings -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding UTF8 has no equivalent in LATIN2
Andreas Kalsch wrote: My question again: Is there a native Postgres solution to simplify characters consistently? It means to completely remove all diacriticals from Unicode characters. There's a to_ascii() function but it supports a subset of charsets, and IIRC UTF8 is not one of them. Patches welcome. I will validate input data on the client side (PHP or Python) and send it to the server. Of course the only encoding I will use on any side is UTF8. I just wnated to use this Latin thing for simplification of characters. Hmm, seems you're using the wrong tool for that purpose. Changing to a different encoding does not remove any diacritical marks, only change the underlying byte encoding. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I have a look at your TuningWizard generated config file?
Jennifer Trey escribió: I think I got everything now. Most things where on by default it seems. #-- # RUNTIME STATISTICS #-- # - Query/Index Statistics Collector - track_activities = off *#I turned this off What for? #autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze Hmm, does the tuning wizard not touch these? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems compiling contribs in Open Solaris
Emanuel Calvo Franco escribió: HI all, I trying to compile several contribs in Osol. I had in result some problems to take them work. /opt/SUNWspro/bin/cc -Xa -xO3 -xarch=native -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff -xc99=none -xCC -KPIC -I. -I../../src/include -c -o xpath.o xpath.c Putting child 0x080a3290 (xpath.o) PID 1718 on the chain. Live child 0x080a3290 (xpath.o) PID 1718 xpath.c, line 18: cannot find include file: libxml/xpath.h xpath.c, line 19: cannot find include file: libxml/tree.h xpath.c, line 20: cannot find include file: libxml/xmlmemory.h xpath.c, line 21: cannot find include file: libxml/xmlerror.h xpath.c, line 22: cannot find include file: libxml/parserInternals.h You need to tell configure where to find libxml's headers (--with-includes). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Calculating the difference between timetz values
Tom Lane wrote: Alexey Klyukin al...@commandprompt.com writes: On Jul 27, 2009, at 6:52 PM, Michael Glaesemann wrote: I don't have a solution, but am curious what your use case is for timetz (as opposed to timestamptz). I'm writing a custom trigger function that has to compare values of time* types and make some actions depending on a result. It's still fairly unclear why you think that comparing timetz values is a useful activity. Is 23:32 earlier or later than 00:32? How can you tell whether it's the same day or different days? Adding timezones into that doesn't make it better. Our documentation deprecates timetz as a poorly-defined datatype, and I've never seen a reason to argue with that judgment. I'd suggest taking a very hard look at why you're not using timestamptz instead. Yeah, well, this is a customer problem, so we're providing a solution to the problem they presented us. The underlying problem is Ruby on Rails doing something silly updating timestamps more often than some small number of milliseconds (or something like that), so what we want is to prevent such an update from happening. The problem being presented is not 23:32 00:32 but rather 23:32:23.0001 23:32:23.00012. On the border condition that 23:59:59.9 00:00:00.0 (which is obviously ambiguous) we just avoid the question by doing the update always. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous_commit=off doesn't always return immediately
tomrevam wrote: The behavior of the system is the same with bg_writer_lru_maxpages = 0. Can you explain why transactions are sometimes synchronous even with the synchrounous_commit set to off? Asynchronous transactions must still be logged to the journal from time to time. There's a background process called the wal writer that's supposed to do that on the background, but if you have too much WAL traffic then some transactions may block if there's not enough space in the in-memory WAL buffer to store their changes, which causes them to block. This is why Tom was suggesting you to increase wal_buffers. Did you try that? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comparing NEW and OLD (any good this way?)
Thomas Kellerer wrote: Pavel Stehule, 23.07.2009 14:50: look on http://wiki.postgresql.org/wiki/Category:Snippets That page is not accessible from the Wiki's main page (at least I can't find an easy way to navigate there) I think there should be a prominent link right at the start page that links to that page and your excellent collection. Agreed, just added one. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying only incremental records to another DB..
Scott Ribe escribió: To be clearer: Do you mean that the folder you backed up is the folder with the file PG_VERSION in it, and all its contents? In my case, yes, because I can copy the config files as well. In general, I was thinking of the folder passed to postmaster via -D. But of course if you don't want to copy some config files, or you have some table spaces off on another volume, you may need to do something a little more involved. Actually it might be more complicated even if you don't have any tablespace, if your config file specifies a data_directory other than the default one. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du [PATCH] extended \du with [+] - was missing
Andreas Wenk wrote: here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? Not really. For example the spanish translation file has this: msgid \\du [PATTERN] list roles (users)\n msgstr \\du [PATRÓN] listar roles (usuarios)\n and it needs to read instead: msgid \\du[+] [PATTERN] list roles (users)\n msgstr \\du[+] [PATRÓN] listar roles (usuarios)\n -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du [PATCH] extended \du with [+] - was missing
Tom Lane wrote: We've never before expected patch submitters to patch the .po files, and in fact I would have thought it would be useless to do so. The masters are not in our CVS. Why is Andreas being told to worry about this? I must admit I don't know :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Documentation Improvement suggestions
Robert James escribió: Two small suggestions that might make it easier for newcomers to take advantage of the wonderful database: 1. Googling Postgres docs produces links for all different versions. This is because incoming links are to different versions. Besides being confusing, it pushes the pages lower in Google, and makes it harder to find them. Could the website offer a link to the 'current' version, whichever it is. Eg instead of just : http://www.postgresql.org/docs/8.1/static/creating-cluster.html Have: http://www.postgresql.org/docs/current/static/creating-cluster.html which would keep all incoming links pointed to the current page. Maybe we should offer a robots.txt file that told crawlers to only index the current version of the docs, not the version-specific ones. 2. The 'SQL' in 'PostgresSQL' is hard to say and type. Everyone drops it (even this list!). Why not change the official name? Again, it would make googling and naming things easier. This is a taboo topic which has created the largest holy wars I've seen in this project. Please don't raise it. If you're interested, search the archives. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Help needed for reading postgres log : RE: [GENERAL] Concurrency issue under very heay loads
Raji Sridar (raji) wrote: I would like some help in reading the postgres logs. Here is a snippet of the log. Auto commit seems to be set to false. But still the logs shows CommitTransactionCommand in debug mode. The same order number is given for multiple clients. Please see CommitTransactionCommand below for both select ...for update and update... SQLs and let me know if I am reading correctly that auto commit is actually effective. CommitTransactionCommand is an internal function that has nothing to do with a SQL-level COMMIT. If there were a true transaction commit you'd see a debug entry saying CommitTransaction. You seem to be barking up the wrong tree here. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Autovacuum and pg_stat_reset()
Rafael Martinez wrote: Does the use of pg_stat_reset() affects the statistics autovacuum uses to find out what to do and when this should be done? Yes. You should do a manual ANALYZE after resetting stats to keep autovacuum in sync with reality. (In principle ANALYZE is only concerned with updating the unrelated stats in pg_statistic, but it also sends dead/live tuple counts to pgstats which autovacuum relies on.) Can the use of pg_stat_reset() affect performance in any way? Hmm, not sure. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Incr/Decr Integer
William Scott Jordan wrote: Hi Andrew, That's a very good guess. We are in fact updating this table multiple times within the same triggered function, which is being called on an INSERT. Essentially, we're using this to keep a running total of the number of rows being held in another table. This is the worst way to go about keeping running totals; it would be far better to have a table holding a last aggregated value and deltas from that; to figure out the current value of the counter, add the last value plus/minus the deltas (I figure you'd normally have one +1 for each insert and one -1 for each delete; update is an exercise to the reader). You have another process that runs periodically and groups the deltas to generate an up-to-date last aggregated value, deleting the deltas. This way you should have little deadlock problems if any, because no transaction needs to wait for another one to update the counter. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
Rafael Martinez wrote: This is the Makefile we use: - --- PG_SRC=/usr/local/src PG_LIB=/usr/local/lib SERVER_INCLUDES += -I $(shell /local/opt/pgsql-8.4/bin/pg_config - --includedir) SERVER_INCLUDES += -I $(shell /local/opt/pgsql-8.4/bin/pg_config - --includedir-server) I suggest you rewrite your makefile to use PGXS. The problem might be a difference in CFLAGS. It would make the makefile a lot simpler too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automatic type conversion
CG wrote: I could add the explicit type casts, but I'd rather find out what the nature of the subtle (or not-so-subtle) difference I've stumbled upon is... It's an intentional change, so adding typecasts is the appropriate solution. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] savepoints in 8.3.7 or whatever...
John R Pierce wrote: we have an app thats doing massive amounts of inserts, batched in transactions, multiple concurrent connections (tuned for optimal throughput, usually around 1 thread per cpu core plus a couple more). occasionally a transaction gets duplicated, and that causes a constraint violation which causes the whole transaction to abort unless we wrap each insert in a savepoint. my developers are asking me if there are limits as to how many savepoints can be active, etc. they have run into various such limits in oracle. There's a hard limit of 2^32 savepoints in a transaction, but you'll probably run into limits before that due to memory constraints (I think each savepoint will use at least 8kB). Anyway I suggest you do RELEASE SAVEPOINT after each insert to ensure resources are released as best as possible. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ascending / Descending Indexes
Michael Gould wrote: In some SQL engines the engine doesn't need to define both Ascending and Descending indexes on the same column. Does Postgres need to have indexes defined for both Ascending and Descending sorts? We use quite a few of these types of sorts. A single btree index can be used for both cases. (Unless you want some columns ascending and other columns descending, in which case you need to work extra.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Best practices for moving UTF8 databases
Andres Freund wrote: On Tuesday 14 July 2009 11:36:57 Jasen Betts wrote: if you do an ascii dump and the dump starts out SET CLIENT ENCODING 'UTF8' or similar but you still get errors. Do you mean that a dump from SQL_ASCII can yield non-utf8 data? right. But According to the OP his 8.3 database is UTF8... So there should not be invalid data in there. I haven't followed this thread, but older PG versions had less strict checks on UTF8 data, which meant that some invalid data could creep in. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Custom Class variables
Michael Gould wrote:
I have created the following in my postgres.conf file
custom_variable_classes = 'iss'
In a SQL session I've tried
Set iss.one = '1'
set iss.two = '2'
Select * from iss;
How do I access the values from the custom class in sql code?
show iss.one;
select current_setting('iss.one');
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uuid_hash declaration
Dimitri Fontaine wrote: Mel Flynn mel.flynn+pg...@mailing.thruhere.net writes: Is there a way in the API to tell the backend that uuid_hash function is implemented by the foo__uuid_hash function so that backwards compatibility isn't broken? Yes. http://www.postgresql.org/docs/8.3/interactive/xfunc-c.html#XFUNC-C-PGXS For example: CREATE OR REPLACE FUNCTION uuid_hash(uuid) RETURNS bool AS 'MODULE_PATHNAME', my_uuid_hash LANGUAGE 'C' IMMUTABLE STRICT; I think the problem he is having is that we provide a C function named uuid_hash, which conflicts with the one in FreeBSD's libc. If that's the problem, my 2c is that uuid_hash is too generic a name to export and we should change ours. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.4 literal escaping
Andreas escribió: Hi, I've got a similar issue with a function that uses regular-expression-magic. I got it from the sql list and it works but I'm just about 75% aware of how. Still PG complains about those \\ everywhere. Replacing every \ by || E'\\' || would make it ... cough ... not looking cuter as it allready is. What would be the correct syntax here? Just prepend E to the whole string, i.e. instead of '\\(0\\)||\\(||\\)' use E'\\(0\\)||\\(||\\)' If you're list most people and hate having to double the \s use dollar-quoting, as shown below. BTW it seems the function could be written in the SQL language instead of plpgsql, which could make it faster. CREATE OR REPLACE FUNCTION cleanphonenr(text) RETURNS text AS $BODY$ BEGIN RETURN CASE WHEN regexp_replace($1, '[^0-9+]', '', 'g') ~ $$^(\+|00)49$$ THEN '0'|| regexp_replace( regexp_replace( regexp_replace($1, '[^0-9+()]', '', 'g') , $$\(0\)||\(||\)$$, '', 'g') , $$^(?:\+|00)49(.*)$$, $$\1$$) WHEN regexp_replace($1, '[^0-9+]', '', 'g') ~ $$^(\\+|00)$$ THEN '+'|| regexp_replace( regexp_replace( regexp_replace($1, '[^0-9+()]', '', 'g') , $$\(0\)||\(||\)$$, '', 'g') , $$^(?:\+||00)(.*)$$, $$\1$$) ELSE regexp_replace($1, E'[^0-9]', '', 'g') END; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UUID datatype question
Tom Lane wrote:
(I believe that 8.3 takes braces too ... but it's pickier about where
it allows dashes.)
This is what the 8.3 comment says:
/*
* We allow UUIDs in three input formats: 8x-4x-4x-4x-12x,
* {8x-4x-4x-4x-12x}, and 32x, where nx means n hexadecimal digits
* (only the first format is used for output). We convert the first
* two formats into the latter format before further processing.
*/
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how drop a role that owns stuff ?
Andreas wrote: Hi, for some cleaning up I'd like to drop a certain role. Now DROP ROLE yields an error and complains about 271 objects being owned by this role. Is there an easy way to switch ownership of those objects to another role? Yes, see REASSIGN OWNED (gives ownership to something else) and DROP OWNED (drops grants and removes objects owned). Normally you run both in that order. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BR/
James B. Byrne wrote: 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133) hll_theheart_db_admin : LOG: 0: duration: 0.782 ms statement: SELECT * FROM currencies The client program that receives this result reports that there are no rows returned. So where did they go? Is it using a different PG connection than the one doing the insert? In that case, it won't see the new row until the inserting transaction commits. BTW it seems necessary to clarify that LOCATION lines correspond to the LOG/NOTICE/WARNING/ERROR line immediately _above_ it, not the one below. So if you see this: LOG: foo bar LOCATION: somewhere line N ERROR: baz qux LOCATION: another line you know what to make of it, and it's not this: LOCATION: somewhere line N ERROR: baz qux -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is autovacuum recommended?
Willy-Bas Loos escribió: Hi, Whenever i start a big action, like inserting millions of recs or doing a large update, the autovacuum fires on top of that. It has some adverse effects on performance when i need it most. More than once a postgres service crashed on me because of it. Sure, it had too little memory, but it wouldn't have happened if it wasn't for the autovacuum. 1. the server should never crash. If it does you have another problem that perhaps is triggered by the high load. Investigate that. 2. what PG version are you running? What operating system? 3. You can turn autovacuum off for particular tables. This would allow you to have it process most tables, and manually vacuum only the specific tables that are causing you grief. Should ik keep autovacuum on, or is it better to run a script like: vacuumdb -az (daily except one day) vacuumdb -azf (once a week) 4. VACUUM FULL is (almost) never needed unless your server is not properly tuned. If you find a need for it, it's a symptom that you need to tweak something somewhere. Need more details about the problem to give you more advice. 2a. Upgrading to 8.4 may give you several benefits in this area. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance problem with low correlation data
m_li...@yahoo.it wrote: testinsert contains t values between '2009-08-01' and '2009-08-09', and ne_id from 1 to 2. But only 800 out of 2 ne_id have to be read; there's no need for a table scan! I guess this is a reflection of the poor correlation on ne_id; but, as I said, I don't really think ne_id is so bad correlated. In fact, doing a select ne_id, t from testinsert limit 10 I can see that data is laid out pretty much by ne_id, t, grouped by day (that is, same ne_id for one day, then next ne_id and so on until next day). How is the correlation calculated? Can someone explain to me why, after the procedure above,correlation is so low??? Did you run ANALYZE after the procedure above? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Overhead of union versus union all
Tim Keitt wrote: I am combining query results that I know are disjoint. I'm wondering how much overhead there is in calling union versus union all. (Just curious really; I can't see a reason not to use union all.) UNION needs to uniquify the output, for which it plasters an additional sort step, whereas UNION ALL does not need to uniquify its output and thus it can avoid the sort step. Using UNION ALL is recommended wherever possible. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] about pg_stat_get_db_xact_commit
abdelhak benmohamed wrote: Hellow I like to track the number of committed transaction for my database So I use the following command Select pg_stat_get_db_xact_commit(16384) (16384 is the oid of my database) It gives me 35 But if I execute the same command another time, it gives me more than 35. Between the first select and the second select I don’t execute transaction on my database Please, can any one help me to understanding the cause of the change? Maybe autovacuum running underneath committed some transactions. (Also, each time you call the function it starts and commit a new transaction). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] create a table inside a function
Alain Roger escribió: IF (outResult = 1) THEN return true; ELSE EXECUTE 'create table sw.tmp_import (id serial NOT NULL CONSTRAINT id_key PRIMARY KEY, software VARCHAR(1024), barcode VARCHAR(10), username VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512)) WITH (OIDS=FALSE);'; EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit'; return false; END IF; Just leave out the EXECUTE and quotes. This example should work without them. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG 8.3.7 initdb -E LATIN1 fails on Windows
Abraham, Danny wrote: Hi, Runnning: initdb -E LATIN1 -D . Error: encoding mismatch Right. Try using Win1252 instead of Latin1: initdb -E win1252 ... Or just leave -E out entirely, since it will be picked up by default from the locale setting anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] planned recovery from a certain transaction
Chris Spotts escribió: The transaction itself works flawlessly, but every once and awhile the data the it uploads from comes in flawed and we have to find a way to reset it. This reset involves restoring a backup that was taken right before the proc started. If we had the xid of the long running transaction, is there a better way to reset it right before that transaction happened? Restoring the backup is a lengthy process because several of the tables that are affected are rather large. You could mark it aborted in pg_clog, assuming none of the tuples it touched have been examined by anyone else after it finished. Since you likely want to crosscheck the data (thus examine it, which sets its hint bits), it's going to be very hard to do. Another idea would be to use PITR to restore to the time just before the transaction, but that's going to be painful too because restoring from a base backup is going to take long for your big tables. Lastly, you could use a filesystem snapshot taken just before the long procedure, to which to revert if you don't like how it went. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL and master multi-slave replication
Eduardo Morras wrote: Hi, I'm currently building a Pg multiserver and have a question about the possibility of working with WAL in a multislave environment. I have few master servers (write only) and multiple slave servers (read only). I want to write the WAL files from masters in a central postgres and that the multiple slaves reads them from time to time and auto update. Never heard of a multiserver. I assume you mean there's a bunch (zero or more) slaves for each master. there's a suite to handle this kind of thing using pg_standby, including cleanup of old logs; see https://projects.commandprompt.com/public/pitrtools Mind you, the WAL files are not stored in a database but in raw files. I have never seen anyone advocating the use of a database to store them. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL and master multi-slave replication
Eduardo Morras escribió: At 19:25 24/06/2009, you wrote: On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morrasemor...@s21sec.com wrote: Yes, there will be 3 masters recolleting data (doing updates, inserts and deletes) for now and 5 slaves where we will do the searches. The slaves must have all the data recollected by the 3 masters and the system must be easily upgradable, adding new masters and new slaves. You know you can't push WAL files from 1 server into a slave, right? No, i didn't know that. I guess you don't know either that you can't query a slave while it is on recovery (so it's only a warm standby, not hot). And if you bring it up you can't afterwards continue applying more segments later. What you can do is grab a filesystem snapshot before bringing it online, and then restoring that snapshot when you want to apply some more segments to bring it up to date (so from Postgres' point of view it seems like it was never brought up in the first place). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] example of aggregate function for product
Whit Armstrong escribió: Ansis ansis_no_ AT inbox.lv13 Jan 2006 16:41:05 An aggregate multiplication function, an analog of sum (the same should be defined also for other numeric types): CREATE OR REPLACE FUNCTION mul2(FLOAT,FLOAT) RETURNS FLOAT AS ' DECLARE a ALIAS FOR $1; b ALIAS FOR $2; [...] but that example looks pretty different than the ones found in the 8.3 manual (avg for instance): The main difference is that the 8.3 docs example piggybacks on C language functions that you must compile and install separately, whereas the comment function uses a plpgsql function and is self-contained. Other than that (and the fact that the second one is for averages not multiplication), both examples are technically identical ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use PQfn() in libpq library?
Bruce YUAN escribió: Hi Tom, Which interface will replace this? And where can get it's reference? Try reading beyond the comma in the quoted paragraph: This interface is somewhat obsolete, as one can achieve similar performance and greater functionality by setting up a prepared statement to define the function call. Then, executing the statement with binary transmission of parameters and results substitutes for a fast-path function call. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 10 TB database
Artur wrote: Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. Sounds a bit like what Truviso does ... -- Alvaro Herrera -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres auditing features
Greg Smith wrote: On Thu, 11 Jun 2009, SHARMILA JOTHIRAJAH wrote: Does postgresql have any build-in auditing features like in Oracle's total-recall or fine grained auditing There's nothing built-in, but it's not too difficult to build such facilities yourself. I have added this to the FAQ http://wiki.postgresql.org/wiki/FAQ#Is_there_a_way_to_leave_an_audit_trail_of_database_operations.3F ... he says, hoping that it'll help generate interest in getting the FAQ updated ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] type cast in index
Linos escribió: CREATE INDEX fecha_creacion_ordtrab ON orden_trabajo_cabecera USING btree ((time_stamp_creacion::date)); but in my postgresql 8.3 version i get this error: ERROR: functions in index expression must be marked IMMUTABLE You can make it work by adding an AT TIME ZONE 'UTC' specification, which will cause it to be turned into a plain timestamp (without tz). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] aliases for sequences and other DB objects?
Agoston Postgres wrote:
Hi!
Is it possible in Postgres to specify aliases for various DB objects, such as
tables, views, sequences, etc.? (For now I would like to create them for
sequences.)
What I mean is something like in Oracle, such as
create alias my_sequence_alias for my_sequence;
select nextval('my_sequence_alias');
No. What would this be used for?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lc_messages 8.3.7
pribram pribram wrote:
postgres=# SET lc_messages=cs;select to_char('2006/01/01'::timestamp,
'TMMonth');
SET
to_char
-
January
Hmm, it works fine for me (not Windows though):
alvherre=# SET lc_messages='C';select to_char('2006/01/01'::timestamp,
'TMMonth');
SET
to_char
-
January
(1 fila)
alvherre=# SET lc_messages='fr_CA';select to_char('2006/01/01'::timestamp,
'TMMonth');
SET
to_char
-
Janvier
(1 fila)
alvherre=# RESET lc_messages; select to_char('2006/01/01'::timestamp,
'TMMonth');
RESET
to_char
-
Enero
(1 fila)
alvherre=# select version();
version
--
PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian
4.3.3-3) 4.3.3
(1 fila)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I manually alter the statistics for a column?
Douglas Alan escribió: Hey, while I have you on the line, might you be so kind as to explain why this query is so slow? Shouldn't it just fetch the first row in the table? What could be faster than that? explain analyze select * from maindb_astobject limit 1; QUERY PLAN Limit (cost=0.00..0.04 rows=1 width=78) (actual time=8091.962..8091.965 rows=1 loops=1) - Seq Scan on maindb_astobject (cost=0.00..3358190.12 rows=75426912 width=78) (actual time=8091.955..8091.955 rows=1 loops=1) Total runtime: 8092.040 ms (3 rows) Maybe there's a bunch of pages with only dead tuples at the start of the table? Maybe a lot of empty pages at the start of the table (If this is 8.3 you have to consider sync_seqscan as well) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump table space
Tom Lane wrote: However, this is really just cosmetic, as the dump is set up like this: SET default_tablespace = whatever; CREATE TABLE whichever(...); If tablespace 'whatever' doesn't exist, you'll get an error on the SET but the CREATE will succeed anyway. (I guess this only works cleanly if the destination machine has *none* of the source's tablespaces, else things might get assigned to unexpected tablespaces. But it's definitely possible to restore a dump without having the same tablespaces.) Maybe it would be better if the dump has a RESET default_tablespace before the SET. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I can't drop a user if I don't drop his grants beforehand??????????????????
Nico Sabbi wrote: Alvaro Herrera ha scritto: I'm not speaking of object ownership, but of GRANTs. As Tom says, it's a known limitation. Did you try REASSIGN OWNED and/or DROP OWNED? No, I didn't because the tables weren't owned by the user I wanted to drop, but by another one. DROP OWNED drops GRANTs too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bloated Table
Brad Nicholson wrote: On Wed, 2009-05-27 at 11:15 -0400, Tom Lane wrote: =?iso-8859-1?Q?Alexander_Sch=F6cke?= a...@turtle-entertainment.de writes: I'm using a view (http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html) to display the bloat (unused disk space) of the tables in a PostgreSQL database. I wouldn't trust the calculations that view does in the least. You might look at contrib/pgstattuple if you want numbers that have some relationship to reality (and are correspondingly more expensive to get :-() Is the referenced query reliable for even estimating, or is it flat our wrong? Co-workers that were PGCon are saying that this is becoming a popular/accepted way to check for bloated tables. If ma is supposed to be maxalign, then this code is broken because it only reports mingw32 as 8, all others as 4, which is wrong. However I think the big problem is that it relies on pg_class.relpages and reltuples which are only accurate just after VACUUM, only a sample-based estimate just after ANALYZE, and wrong at any other time (assuming the table has any movement). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bloated Table
Tom Lane wrote: It's an interesting exercise in trying to estimate bloat without groveling through the whole relation, but I seriously doubt you could ever get numbers this way that are trustworthy enough to drive maintenance decisions. Well, pg_stattuple is way too expensive to be running every minute ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need beginning and ending date value for a particular week in the year
Alban Hertroys wrote: I recall using generate_series() and EXTRACT(week FROM ...) to populate the table in one pass for several years, but I don't have the exact incantation at hand now. I'd have to experiment a bit to get that back again, I don't have access to it anymore. There's something similar in the Wiki: http://wiki.postgresql.org/wiki/Date_and_Time_dimensions -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3: timestamp subtraction
Havasvölgyi Ottó escribió: I mean the Win32 distribution on the PgSql site. I always used that. If you want to find out whether a particular build used floating point or integer datetimes, issue SHOW integer_datetimes. If it says off, then it's floating point. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I can't drop a user if I don't drop his grants beforehand??????????????????
Nico Sabbi wrote: Hi, i can't believe my eyes. Why on earth I can't drop a user without previously revoking his privileges? This is really _crazy_ in my opinion. I'm not speaking of object ownership, but of GRANTs. As Tom says, it's a known limitation. Did you try REASSIGN OWNED and/or DROP OWNED? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Aggregate Function to return most common value for a column
arta...@comcast.net wrote: Excellent observation Dave. Sometimes I can't see outside of the box I'm in. And at the time I was focusing on text so statistics was in another box. I've update post with final functions for mode(), median() and range(). Hey, if you want to add your functions to http://wiki.postgresql.org/wiki/Snippets , that would be great. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] array/function question
Joshua Berry escribió: Please forgive the lack of grace. I'd love tips on how to improve this! Tip: follow Pavel's suggestion. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] array/function question
Pavel Stehule escribió: postgres=# create or replace function xx(anyarray, anyarray) returns bool[] as $$ select array(select (select x = any(select y from unnest($2) g2(y))) from unnest($1) g(x)) $$ language sql immutable; CREATE FUNCTION There ain't no unnest() function in 8.3 ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump and 2gb limit on windows and version 8.1.3
j...@email.cz wrote: Hello, I have problem with 2gb limit for pg_dump on windows that was fixed in version 8.2.4. I have a server with postgresql version 8.1.3, which unfortunately cannot be upgraded. Is there some way how to use safely pg_dump (and if needed pg_restore) version 8.2.4 or newer with the server (8.1.3). Yes -- pg_dump is backwards compatible; it will be able to dump the database just fine. Note, however, that the dump is not guaranteed to be restorable in the 8.1 server. I think you should upgrade to 8.1.17 which likely contains the fix as well. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] array/function question
Joshua Berry escribió:
Inputs:
A: an array of integers. for example: { 1, 2, 3, 4, 7 }
B: an array of integers. for example: { 1, 4, 8, 9 }
Returns
C: an array of bools the same dimensions as Array A. In this example: {
true, false, false, false, true, false }
Effectively, this function would use Array A as a set of boolean tests
to exercise on Array B. The result array will have the save number of
elements as array A.
I think this is much easier to write in PL/Perl than PL/pgSQL. Trivial
in fact. Your example is flawed though (three falses instead of two) ...
I think it looks like this:
create or replace function is_element_present(int[], int[]) returns bool[]
language plperl as $$
$a = shift;
$b = shift;
if ($a =~ /{(.*)}/) {
@a = split /,/, $1
}
if ($b =~ /{(.*)}/) {
@b = split /,/, $1
}
for my $k (@b) {
$h{$k} = 1;
}
@c = map { if (defined $h{$_}) { 1 } else { 0 } } @a;
return \...@c;
$$;
Hmm, well, the fact that PL/Perl passes arrays as string kinda sucks --
fixing that takes half the code of the function!
alvherre=# select is_element_present('{1,2,3,4,7}', '{1,4,8,9}');
is_element_present
{t,f,f,t,f}
(1 fila)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Commit visibility guarantees
Marsh Ray escribió: On Mon, May 18, 2009 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marsh Ray marsh5...@gmail.com writes: The central question: So if I successfully commit an update transaction on one connection, then instantaneously issue a select on another previously-opened connection, under what circumstances am I guaranteed that the select will see the effects of the update? If the select is using a snapshot taken later than the commit, it will see the effects of the update. Great! Just the kind of definitive answer I was looking for. Now I just need to find a comprehensive list of all the things that could cause an older snapshot to be retained, and ensure that none of them could possibly be occurring on this connection. On a serializable transaction all queries will use the same snapshot taken when the first query is executed. Otherwise (read committed), a new query always gets a fresh one. (Old snapshots are also used for stuff like cursors that remain open, but that's not the case here.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
Philipp Marek wrote: On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote: we're using postgresql 8.3 for some logging framework. There are several tables for each day (which are inherited from a common base), which - are filled during the day, - after midnight the indizes are changed to FILLFACTOR=100, and - the tables get CLUSTERed by the most important index. - Some time much later the tables that aren't needed anymore are DROPped. So far, so fine. Do say, do you have any long-running transactions, or idle transactions? Maybe someone opened a terminal somewhere and left it open for days? Have a look at pg_stat_activity. Yes, I have two terminal windows for different users/schemas in the same DB open - but they're set to auto-commit, and have no tables open or locked. Please close them and try again. Please, let me repeat myself: So, as summary: vacuum_freeze_min_age=0 seems to interfere with btree indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return space to the filesystem. Might the open connections make a difference? I see no reason at all for CLUSTER not to return space to the filesystem, unless it is copying all the tuples over including dead ones (which can only be explained if you have open transactions). I also see no reason for vacuum_freeze_min_age=0 to interfere with btree cleaning. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
Philipp Marek wrote: Hello everybody, we're using postgresql 8.3 for some logging framework. There are several tables for each day (which are inherited from a common base), which - are filled during the day, - after midnight the indizes are changed to FILLFACTOR=100, and - the tables get CLUSTERed by the most important index. - Some time much later the tables that aren't needed anymore are DROPped. So far, so fine. Do say, do you have any long-running transactions, or idle transactions? Maybe someone opened a terminal somewhere and left it open for days? Have a look at pg_stat_activity. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Could not open file pg_clog/....
Markus Wollny wrote:
magazine=# vacuum analyze pcaction.article;
PANIC: corrupted item pointer: 5
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: Failed.
This error comes from this piece of PageRepairFragmentation:
/* Need to compact the page the hard way */
itemidbase = (itemIdSort) palloc(sizeof(itemIdSortData) * nstorage);
itemidptr = itemidbase;
totallen = 0;
for (i = 0; i nline; i++)
{
lp = PageGetItemId(page, i + 1);
if (ItemIdHasStorage(lp))
{
itemidptr-offsetindex = i;
itemidptr-itemoff = ItemIdGetOffset(lp);
if (itemidptr-itemoff (int) pd_upper ||
itemidptr-itemoff = (int) pd_special)
ereport(ERROR,
(errcode(ERRCODE_DATA_CORRUPTED),
errmsg(corrupted item pointer: %u,
itemidptr-itemoff)));
itemidptr-alignedlen = MAXALIGN(ItemIdGetLength(lp));
totallen += itemidptr-alignedlen;
itemidptr++;
}
}
What it means, AFAICT is that the page is corrupted beyond recovery. If you
want to salvage the rest of the data on the table, you could zero out this
page, for which you'll obviously need to know what page it was. It's not a
trivial patch to add that info to the error message, because
PageRepairFragmentation does not receive it.
I think it is worth our trouble to report block numbers on this kind of errors;
and in fact I had started on a patch to add errcontext() to vacuum and analyze
calls, but never finished it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Could not open file pg_clog/....
Alvaro Herrera wrote: Markus Wollny wrote: magazine=# vacuum analyze pcaction.article; PANIC: corrupted item pointer: 5 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: Failed. This error comes from this piece of PageRepairFragmentation: if (itemidptr-itemoff (int) pd_upper || itemidptr-itemoff = (int) pd_special) ereport(ERROR, (errcode(ERRCODE_DATA_CORRUPTED), errmsg(corrupted item pointer: %u, itemidptr-itemoff))); I forgot to mention that this ERROR is promoted to PANIC by the critical section defined in lazy_vacuum_page(). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
Philipp Marek wrote: A few days before we found the machine much slower, because of the autovacuum processes that were started automatically [autovacuum: VACUUM ... (to prevent wraparound)]. After several days we killed that, and, as a quick workaround, changed autovacuum_freeze_max_age to 1G and restarted the server, which worked as before (and didn't ran the autovacuum processes). Several days? How large is your vacuum_cost_delay and autovacuum_vacuum_cost_delay parameters? As a next idea we changed the cluster/reindex script to set vacuum_freeze_min_age=0 before the CLUSTER call, hoping that this would solve our transaction ID wraparound problem. REINDEX? What are you doing REINDEX for? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
David Fetter escribió: On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: Hi all. I'll make this faster. I hace this table and this function: You should only ever assume that your SELECT's output will have a particular ordering when you include an ORDER BY clause that actually specifies the order well enough :) Yeah, we went over this on the spanish list, turned out that I couldn't remember about syncscan :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Power outage and funny chars in the logs
--- On Thu, 7/5/09, Massa, Harald Armin c...@ghum.de wrote: After reading the answers to the funny chars in the logs and no fsync on the logs: is there a fsync on postmaster.pid? Or is that file not considered important enough? I think this strongly suggests that postmaster.pid should be fsync'ed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PgUS 501c3 Public Charity
Dave Page wrote: On Thu, May 7, 2009 at 3:04 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Joshua D. Drake wrote: Hello, Yeah its not general technical discussion but this little bit of news warrants more widely read attention. PgUS (http://www.postgresql.us/) received its 501c3 public charity status today. You can view the determination letter here: https://www.postgresql.us/determination_letter Just curious: is PostgreSQL as a project withdrawing from SPI? No. Joshua is reporting the status of PgUS which is analogous to pgEU in function - that is, it is primarily supporting serving the regional users and user groups. The core project will remain in SPI to support global activities and the work of the developers/contributors etc. Isn't the majority of donations going to go to PgUS and pgEU anyway? What good will it to for SPI to attempt to support global activities if it doesn't have any money? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PgUS 501c3 Public Charity
Andrew Gould escribió: The public charity status lets those of us who pay income taxes to the US government claim donations to PostgreSQL as deductions on our income tax statements. It encourages donations. It makes donations more affordable. It does not limit where the money is used. That's it. That's all. I know that. But both pgUS and SPI have public charity status now. Which one would be a prospective US donator be more willing to donate to? Josh just confirmed money is currently flowing to PgUS. But PgUS charter is to help the activities within the US; so since SPI is going to have little money shortly, the global communities (meaning everything outside US and Europe) are going to find themselves without any means to fund getting people from there to here (Yes -- here to me means outside the US/EU). For it was SPI who used to fund US speakers to travel to places like Brazil. Do you think Brazil is in a position to get nearly as many funds as the US community? I know my country is likely to raise very little money (we hardly get enough money to handle a single yearly Linux conference; and that's only because we bunch all F/OSS stuff together. A single project like Pg is unlikely to fly very far.) I'll ask PgUS later to fund my possible flight to Cuba for a Pg summer school. Oh wait a minute ... Hey, but I forgot -- congratulations on the 501(c)3 status! -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to select temp table
Robert Gravsjö escribió: On 2009-05-06 14.34, liuzg4 liuzg4 wrote: ver 8.4 i create two table with same name named 'testtable' one is temp table i select * from testtable then this table is a public or a temp ??? Temp. To access public use select * from public.testtable. Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table. Note that you can refer to the temp table like this: select * from pg_temp.testtable; Also, you can make the non-temp table first in the search path by putting pg_temp later than the public schema (or wherever you have created the function): select * from testtable;-- refers to temp set search_path to 'public', 'pg_temp'; select * from testtable;-- refers to non-temp -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PgUS 501c3 Public Charity
Joshua D. Drake wrote: Hello, Yeah its not general technical discussion but this little bit of news warrants more widely read attention. PgUS (http://www.postgresql.us/) received its 501c3 public charity status today. You can view the determination letter here: https://www.postgresql.us/determination_letter Just curious: is PostgreSQL as a project withdrawing from SPI? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] recover corrupt DB?
Dan Armbrust escribió: My particular disk-full condition was on ext2. Nothing exotic. Also, the process that filled the disk was postgres - if that makes any difference - I had left a debug level turned up in the postgres config file, and it was logging every single db query. Since it wasn't set up to remove old log files - it filled the disk. Nothing else unusual occurred that I'm aware of - things went weird for the lab tester, he cleared some space, rebooted the system, and postgres didn't come back online. Did cleared some space include the pg_xlog directory or something in the vicinity? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tracking down a deadlock
Bill Moseley wrote: I seemed to have resolved this issue by a: LOCK TABLE account IN EXCLUSIVE MODE; at the very start of the transaction to prevent that transaction from running in parallel. The transaction has quite a few selects and inserts and a few updates. I was hoping to not lock at the start of the transaction which will effectively serialize that code. The update that was causing the deadlock was just about the last command in the transaction. Removing that update and the deadlocks go away. I think you should be able to do a SELECT FOR UPDATE with the same WHERE as the UPDATE as the first thing in your transaction. That way it is much less likely to deadlock with itself. (This assumes that the set of tuples to update doesn't change, which holds true everytime if your transaction has isolation level serializable). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] triggers and execute...
Scott Marlowe escribió: Oh man, it just gets worse. I really need a simple elegant solution here, because if I try to build the query by hand null inputs make life a nightmare. I had built something like this: q = 'insert into '||schem||'.page_access_'||part||' values ( '||new.paid||', '''||new.timestamp||''', '||new.total_time||', '''||new.http_host||''', '''||new.php_self||''', '''||new.query_string||''', '''||new.remote_addr||''', '''||new.logged_in||''', '||new.uid||', '''||new.http_user_agent||''', '''||new.server_addr||''', '''||new.notes||''' )'; execute q; But if any of the fields referenced are null, the whole query string is now null. So the next step is to use coalesce to build a query string? That get insane very quickly. There's got to be some quoting trick or something to let me use new.*, please someone see this and know what that trick is. Agreed, it is ugly. I don't think there's a better way to do it though. One thing you could try is getting the column names and types from the catalogs to build the insert statement. That way you don't have to list each column separately, and you don't need to fiddle with whether each value needs quotes or not. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SPI_ERROR_TRANSACTION [PostgreSQL 8.3]
Daroslav wrote: Hi As a newbie in PL/pgSQL I have faced an error SPI_ERROR_TRANSACTION which was raised always when I have tried to launch following function with uncommented keywords SAVEPOINT, SET TRANSACTION and COMMIT(when commented, function works fine): Yes, you cannot use those constructs in plpgsql (or any other PL for that matter; it's a limitation of the underlying layer). You need to use BEGIN/EXCEPTION/END blocks instead. Also, keep in mind that if you RAISE EXCEPTION, the current transaction is always rolled back, so the RETURN FALSE in your function would never be reached. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore Crashes Postgres
Christine Penner wrote: The crashes I get are the windows, this program has encountered a problem and must close, error. At that point I can't do anything with Postgres. I can't restart the service. I have to restart windows. The PostgreSQL logs should have more info regarding the crash. I think you really need to solve this crash; it should normally never happen. (Postgres can be configured so that the log ends up in the Windows event log, by setting log_destination=eventlog in postgresql.conf. If you haven't set that up, it's probably ending up in a file somewhere). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] find column OID types with information schema?
Whit Armstrong escribió: Is it possible to find out the OID types of the columns of a table using the information schema? No; information_schema is limited to stuff that's defined by the SQL standard. If you want OIDs, you need to extract that stuff from the pg_catalog.* catalogs. Type OIDs for attributes can be found in pg_attribute.atttypid. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] status of pl/php
Joshua D. Drake escribió: On Mon, 2009-04-27 at 13:33 -0600, Scott Marlowe wrote: It looks like pl/php is still on a beta release. Is the previous non-beta release preferred, or the beta1 tested against 8.3beta1? Better question for the pl/php list. Copying Alexey because he would know better. It is still considered beta, but I think it's reasonably stable. Some things need to be reworked, such as handling of arrays. Otherwise it should work. Most of the problems with it seem to come from having to compile PHP than PL/php specific. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Object-Oriented Database?
Robert Pepersack wrote: My agency has a contractor that created a PostgreSQL database that he calls object-oriented. I noticed that the contractor has more than one value in a column separated by commas. In the relational world, this obviously violates first normal form. When I asked about it, he said that it's because PostgreSQL is an object-oriented database. I'm very skeptical. What kind of comma-separated? If it's an array (i.e. perhaps a column declared as text[]), then maybe it's not that wrong, as has been said elsewhere on the thread (but I would agree with you that if there's no specific reason to do things that way, it's probably less right than your contractor wants to admit). If it is actually a single text column with comma separated values, and the splitting is done client-side, I'd hazard that you're probably right in placing blame in the contractor. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help for PostgreSQL consistency check mechanism
Chen, Dongdong (GE Healthcare) escribió: When the OS starts up, it wants to detect whether there is data loss in PostgreSQL from last shutdown, is there a method provided? Why would the OS want to do that? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help for PostgreSQL consistency check mechanism
Filip Rembiałkowski escribió: OTOH, there could be some consistency check method... If postgres had block- or row-level checksums, this could do. Row level: it would be very expensive to compute, store and keep up to date. And it doesn't protect you from corruption elsewhere in the block. Block level: there was some effort to implement it for 8.4, but it fell into some deadly traps. The best way I know is to do plain pg_dumpall. But this does not detect all data corruptions. I wrote some plpgsql code a month ago to scan a table and detoast all toastable attributes, reporting it when an exception was raised. It was a very effective way to detect corrupted toast entries, which is the most visible way in which data is corrupted. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't use any with SQL functions
BTW is there a reason the error messages say plpgsql functions cannot ... instead of PL/pgSQL functions cannot ...? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] delete duplicates takes too long
Miguel Miranda escribió: I cant use a unique index because i only want to check for duplicates where processed = 2, for simplicity i did not include that condition in the example. In that case you can create a partial unique index: create index foo on cdr_ama_stat (abonado_a, abonado_b, fecha_llamada, duracion) where processed = 2; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_lsclusters error after pg_dropcluster
Willy-Bas Loos escribió:
On debian lenny, upgraded from etch, after stopping and dropping a cluster
with (pg_ctlcluster 8.1 name stop + pg_dropcluster 8.1 name, no errors), the
cluster still appears in the pg_lsclusters and i get the error: Use of
uninitialized value $info{owneruid} in getpwuid at /usr/bin/pg_lsclusters
line 28
Please report to Debian -- they are the ones that write these programs.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help for PostgreSQL consistency check mechanism
Chen, Dongdong (GE Healthcare) wrote: We are now working on database check mechanism in our application. The situation is when server shutdown abnormally, the postmaster.pid file still exists when reboot, But the PostgreSQL database itself may not be damaged. We just do not want to restore from backups when the database is still good even if server shutdown abnormally. If the PostgreSQL database records are damaged for sure when server reboots, then the restoration should be executed automatically. Just start it up. If there is recovery to execute, it'll be done automatically. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] From 8.1 to 8.3
S Arvind escribió: Our company wants to move from 8,1 to 8.3 latest. In irc they told me to check realse notes for issues while upgrading. But there are lots of release notesss. Can anyone tell some most noticable change or place-of-error while upgrading? If you're too lazy to read them, we're too lazy to summarise them for you ... (Luckily for everybody, Bruce and Tom were NOT lazy enough to write them in the first place.) The meat of what you need to know is in the 8.2.0 and 8.3.0 notes, the incompatibilities section anyhow. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trouble with to_char('L')
Mikko escribió: On Wed, Apr 22, 2009 at 2:13 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Ouch ... I thought that was the way that Windows designated UTF8 locales, but maybe I am wrong. Ok, now I found out that Windows doesn't support locales with encoding using more than two bytes per character and initdb falls back to 1252. http://msdn.microsoft.com/en-us/library/x99tb11d.aspx Hmm. Does this imply that we shouldn't allow UTF8 database on Windows at all? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] This is my first template
Geoff Caplan wrote: where $2 is a well-formed ISO date. Query works as expected when there are records in the result set. When the result set is empty, PG throws an error: date/time field value out of range: 2011-4-31 Is April 31st really a well-formed ISO date? I wonder what jhey smoke there in ISO meetings. My guess is that you're doing date arithmetic wrong somewhere. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trouble with to_char('L')
Mikko escribió: psql (PostgreSQL) 8.3.7 server_version 8.3.7 server_encoding UTF8 client_encoding win1252 lc_numeric Finnish, Finland lc_monetary Finnish, Finland testdb=# SELECT to_char(3.1415::numeric(5,2), '999D99L'); ERROR: invalid byte sequence for encoding UTF8: 0x80 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. FWIW 0x80 is the Euro symbol in Win1252 according to http://en.wikipedia.org/wiki/Windows-1252 Maybe the problem here is that the chosen locales are not UTF8. Does it work if you set lc_numeric and lc_monetary to Finnish_Finland.65001 instead? Those should match the server_encoding. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Yet another drop table vs delete question
marek.patr...@gmail.com escribió: I was wondering if dropping a table is more efficient in PostgreSQL 8.x in comparison to deleting it's content ? 8.x is a meaningless version number in Postgres. Major versions (with new features, etc) are labeled by the first two elements, so 8.0, 8.1, and so on. To actually answer your question, The thing is, postgresql may leave some invalid content behind in both situations. The real question is - which of those two options leaves less garbage to be vaccumed ? At this point I don't relay care about cost based efficiency but cutting down pg background work. Try TRUNCATE. That leaves the less garbage behind and takes the less time. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Scott Marlowe escribió: On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs peterachi...@gmail.com wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. On the news blog page it mentioned switching to MonetDB. I saw nothing about pgsql there. Do they store it in pgsql for manipulation then export to MonetDB? That's the April 1st news though ... the real news is here http://wiki.openstreetmap.org/wiki/OSM_Protocol_Version_0.6#Database_improvements -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trouble with to_char('L')
Mikko escribió:
On Tue, Apr 21, 2009 at 8:13 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Maybe the problem here is that the chosen locales are not UTF8. Does it
work if you set lc_numeric and lc_monetary to Finnish_Finland.65001
instead? Those should match the server_encoding.
alter database testdb set lc_monetary(or numeric) to
'Finnish_Finland.65001' returns:
ERROR: invalid value for parameter lc_monetary: Finnish_Finland.65001
Ouch ... I thought that was the way that Windows designated UTF8
locales, but maybe I am wrong.
However, I noticed that both lc_collate and lc_ctype are set to
Finnish_Finland.1252 by the installer. Should I have just run initdb
with --locale fi_FI.UTF8 at the very start? The to_char('L') works
fine with a database with win1252 encoding.
Hmm, it should have disallowed the creation of an UTF8 database then.
Maybe that part is what is broken here.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
