Re: [GENERAL] hardware failure - data recovery
Rick Gigger <[EMAIL PROTECTED]> writes: To make a long story short lets just say that I had a bit of a hardware failure recently. If I got an error like this when trying to dump a db from the mangled data directory is it safe to say it's totally hosed or is there some chance of recovery? Why don't you try dumping just the critical tables using pg_dump -t? There was only one table in the db. Also perhaps use psql and try looking up in pg_class for the damaged tables by OID. You may be able to drop just a few tables and then dump the DB normally. This assumes the damaged table(s) are non-critical... The table I needed was damaged. I dropped the indexes from it that were also damaged but then the table had to be repaired. I suggest you stop Pg first, take an FS backup of the entire cluster before this so you cahn try various approaches if needed. That was the first thing I did. Thanks everyone for the help. Luckily one of my developers was able to patch up the table and get 99% of the data out. All of the truly critical data was in another database and was backed up, so the 99% was enough to get through the crisis. Thanks, Rick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] hardware failure - data recovery
I could have my developer do this if it would be useful to someone else. But in general I think my time would be much better served fixing my backup situation and monitoring them so that this CAN'T happen again. It shouldn't have happened this time. On Oct 19, 2006, at 8:35 AM, Ray Stell wrote: On Thu, 19 Oct 2006 06:14:46 -0600, Rick Gigger wrote: I think we've got it figure out though. We were able to patch up the db enough to extract the data with some help from google and old postings from Tom. It would be really great if you put down the specifics of what you googled/old postings/Tom together. You might need it next time. I know I'd like to be ready. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] failing 8.1.4 autovacuum
On Monday October 23 2006 4:03 pm, Ed L. wrote: > I have an 8.1.4 autovac process running on HP-UX 11.23 IA64 > and repeatedly failing with the following error: > > ERROR: failed to re-find parent key in "audit_idx1" > > Will a reindex or drop index make this problem go away? BTW, it'd be nice if autovac didn't simply abort and restart with the prior tables that were successfully vacuumed, but rather was able to continue on after the error to the next table. As it is, it starves the others but autovac just keeps banging it's head against the wall, consuming lots of I/O and accomplishing little. Maybe that's part of the 8.2 fixes I haven't kept up with? Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] failing 8.1.4 autovacuum
"Ed L." <[EMAIL PROTECTED]> writes: > I have an 8.1.4 autovac process running on HP-UX 11.23 IA64 and > repeatedly failing with the following error: > ERROR: failed to re-find parent key in "audit_idx1" > Will a reindex or drop index make this problem go away? Probably. > Is there anything I can do to help identify the underlying issue? Can you send me a copy of the physical index file? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] missing FROM-clause entry
m == [EMAIL PROTECTED] writes: m> you didn't reference the table "replica"... this should work: m> UPDATE model_timemap m> SET map = updatemap( CASE WHEN replica = 32188 THEN 328787 WHEN replica = m> 32191 THEN 5739 ELSE -1 END, 1161642129, map) m> FROM replica m> WHERE replica.replica_id = model_timemap.replica AND replica.proxy = 32189 Ah. Thanks. -- Brandon ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] missing FROM-clause entry
you didn't reference the table "replica"... this should work: UPDATE model_timemap SET map = updatemap( CASE WHEN replica = 32188 THEN 328787 WHEN replica = 32191 THEN 5739 ELSE -1 END, 1161642129, map) FROM replica WHERE replica.replica_id = model_timemap.replica AND replica.proxy = 32189 - Original Message - From: "Brandon Metcalf" <[EMAIL PROTECTED]> To: Sent: Tuesday, October 24, 2006 12:35 AM Subject: [GENERAL] missing FROM-clause entry Just upgraded to 8.1.5 and the following UPDATE causes the "missing FROM-clause entry" error: UPDATE model_timemap SET map = updatemap( CASE WHEN replica = 32188 THEN 328787 WHEN replica = 32191 THEN 5739 ELSE -1 END, 1161642129, map) WHERE replica.replica_id = model_timemap.replica AND replica.proxy = 32189 From what I've read, this typically results from referring to a table instead of it's alias, but I don't see how that applies here. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] missing FROM-clause entry
Just upgraded to 8.1.5 and the following UPDATE causes the "missing FROM-clause entry" error: UPDATE model_timemap SET map = updatemap( CASE WHEN replica = 32188 THEN 328787 WHEN replica = 32191 THEN 5739 ELSE -1 END, 1161642129, map) WHERE replica.replica_id = model_timemap.replica AND replica.proxy = 32189 >From what I've read, this typically results from referring to a table instead of it's alias, but I don't see how that applies here. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] failing 8.1.4 autovacuum
I have an 8.1.4 autovac process running on HP-UX 11.23 IA64 and repeatedly failing with the following error: ERROR: failed to re-find parent key in "audit_idx1" Will a reindex or drop index make this problem go away? Is there anything I can do to help identify the underlying issue? (This is an intensely used production system, so I have very limited room to operate.) TIA. Ed ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Any documentation about cayenne
http://incubator.apache.org/cayenne/ Check googlethis is the first result for cayenne dfx wrote: Hi, somebody knows if it is possible to find on the internet documentation, articles or books (also in bookstore) about cayenne? Thank you Domenico ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Any documentation about cayenne
Hi, somebody knows if it is possible to find on the internet documentation, articles or books (also in bookstore) about cayenne? Thank you Domenico
Re: [GENERAL] performace review
On 10/22/06, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Ron Johnson wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 10/22/06 06:45, Thomas Hallgren wrote: >> Joshua D. Drake wrote: >>> Tomi NA wrote: I was just reading http://www.opencrx.org/faq.htm where RDBMS engines are one of the questions and see pgsql bashed sentence after sentence. Can anyone offer any insight as to weather it's fact or FUD? >>> It is 100% FUD. >>> >> What would be the incentive for OpenCRX spreading FUD about PostgreSQL? >> Does anyone know? > > That implies malice. The people at OpenCRX apparently really > believe what they wrote. I believe they probably do believe it and it was probably driven by a complete lack of understanding of PostgreSQL. It doesn't have to be malicious for it to be FUD though. maybe. the feeling i get reading their page is that they are saying: "mysql and postgresql, etc are made with tinker toys, you need to run us on the real stuff". they then try to upsell you to diamond studded titanium tinker toys, namely oracle rac. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] performace review
On Sun, 2006-10-22 at 08:12 -0700, Joshua D. Drake wrote: > Ron Johnson wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On 10/22/06 06:45, Thomas Hallgren wrote: > >> Joshua D. Drake wrote: > >>> Tomi NA wrote: > I was just reading http://www.opencrx.org/faq.htm where RDBMS engines > are one of the questions and see pgsql bashed sentence after sentence. > Can anyone offer any insight as to weather it's fact or FUD? > >>> It is 100% FUD. > >>> > >> What would be the incentive for OpenCRX spreading FUD about PostgreSQL? > >> Does anyone know? > > > > That implies malice. The people at OpenCRX apparently really > > believe what they wrote. > > I believe they probably do believe it and it was probably driven by a > complete lack of understanding of PostgreSQL. Actually, after reading the reply from Brandon Aiken, I believe it was driven by a complete lack of understanding of relational theory. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Fw: [ANNOUNCE] == PostgreSQL Weekly News - October 22 2006 ==
Bill Moran <[EMAIL PROTECTED]> writes: > I can't seem to find any information about the "security" release mentioned. > Are there any specific security issues that have been addressed? There are a couple of coredump conditions fixed --- they don't really amount to security issues unless you let untrustworthy people execute arbitrary SQL on your server, and even then we know of no way to exploit them to do anything except force a database restart. http://archives.postgresql.org/pgsql-committers/2006-10/msg00203.php http://archives.postgresql.org/pgsql-committers/2006-06/msg00300.php regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Fw: [ANNOUNCE] == PostgreSQL Weekly News - October 22 2006 ==
This message came across [EMAIL PROTECTED] I can't seem to find any information about the "security" release mentioned. Are there any specific security issues that have been addressed? Begin forwarded message: Date: Sun, 22 Oct 2006 18:15:27 -0700 From: David Fetter <[EMAIL PROTECTED]> To: PostgreSQL Announce Subject: [ANNOUNCE] == PostgreSQL Weekly News - October 22 2006 == == PostgreSQL Weekly News - October 22 2006 == Security Upgrade Releases 8.1.5, 8.0.9, 7.4.14 and 7.3.16 are out. Upgrade as soon as possible. http://www.postgresql.org/download There is an ongoing discussion about adopting a new CRC algorithm, slicing-by-8, which Intel has developed. Intel may have (and can still apply for) one or more patents on this algorithm. == PostgreSQL Product News == MacDent Pro 1.5 released. http://www.macdentpro.com/ ExtenDB 1.1 released. http://www.extendb.com/pr11.php Slony 1.2.0 released. http://pgfoundry.org/frs/?group_id=1000122 == PostgreSQL Jobs for October == http://archives.postgresql.org/pgsql-jobs/2006-10/threads.php == PostgreSQL Local == Everything this week was global. == PostgreSQL in the News == Planet PostgreSQL: http://www.planetpostgresql.org/ General Bits, Archives and occasional new articles: http://www.varlena.com/GeneralBits/ PostgreSQL Weekly News is brought to you this week by David Fetter and Volkan YAZICI. == Applied Patches == Tom Lane committed: - Various MSVC build fixes from Magnus; also remove stray Windows newlines. - Some desultory copy-editing. - Some marginal editorial improvements and updates in the tutorial. - Further release-note editorialization. - Editorial improvements for recent PL/Python doc updates. - In syntax.sgml, rewrote discussion of string constant syntax to bring it into line with the politically correct view that backslash escapes are deprecated. - Make index entry more specific in libpq.sgml. - Update release notes to current, and do a pass of editorial corrections. - Back-patch second version of AIX getaddrinfo fix. - Marginal code cleanups in pg_logdir_ls: use ReadDir not readdir, and avoid scribbling on its result (might be safe but why risk it) - Try to fix the AIX getaddrinfo mess in a way that works on all versions. - Clarify note about interaction of log_statement logging with errors. Remove obsolete note about logging of statements generated by PL/PgSQL function execution as that doesn't happen anymore. - Marginal improvement in logging: include the function name when logging a fastpath function call. - Rename our substitute qsort to pg_qsort at the link-symbol level (but provide a macro so code can still just say qsort). Avoids linker warnings on pickier platforms such as Darwin, and outright failure on MSVC. - Add externs for optarg/optind where apparently needed. Per Magnus. - In contrib/earthdistance, Handle missing M_PI the same way we've been doing in the core code, instead of inserting an MSVC dependency. - Further MSVC portability fixes from Magnus. - Improve port/qsort() to handle sorts with 50% unique and 50% duplicate values. This involves choosing better pivot points for the quicksort. - Fix a couple of places that were assuming debug_query_string couldn't be NULL, as this seems an unsafe assumption. - Make sure that debug_query_string contains the original query text, if available (which it usually should be), during processing of Bind and Execute protocol messages. This improves usefulness of log_min_error_statement logging for extended query protocol. - Clean up local re-declarations of variables with DLLIMPORT, per report from Magnus that MSVC complains about this. - Simplify contrib Makefiles by removing unnecessary SRCS macro, per Magnus. - Work around reported problem that AIX's getaddrinfo() doesn't seem to zero sin_port in the returned IP address struct when servname is NULL. This has been observed to cause failure to bind the stats collection socket, and could perhaps cause other issues too. Per reports from Brad Nicholson and Chris Browne. - Add some code to CREATE DATABASE to check for pre-existing subdirectories that conflict with the OID that we want to use for the new database. This avoids the risk of trying to remove files that maybe we shouldn't remove. Per gripe from Jon Lapham and subsequent discussion of 27-Sep. - Fix up timetz input so that a date is required only when the specified timezone actually has a daylight-savings rule. This avoids breaking cases that used to work because they went through the DecodePosixTimezone code path. Per contrib regression failures. Also document the already-applied change to allow GMT offsets up to 14 hours. - Fix array operator reference in release notes, per David Wheeler. - Fix up some problems in handling of zic-style time zone names in datetime input routines. Remove the former "DecodePosixTimezone" function in favor of letting the zic code
Re: [GENERAL] performace review
On 10/22/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes:> Ron Johnson wrote:>> That implies malice. The people at OpenCRX apparently really>> believe what they wrote. > I believe they probably do believe it and it was probably driven by a> complete lack of understanding of PostgreSQL.> It doesn't have to be malicious for it to be FUD though.The psychological reason seems clear enough: if they can dismiss postgres as not being worthy of their time, it saves them a lot ofwork in supporting another database. By this point I'd imagine thattheir code is sufficiently mysql-centric that trying to have realsupport for other databases would be a huge undertaking; ergo, there will be great resistance to the idea that they should take postgresseriously. It's a bit of a self-fulfilling prophecy, too, becauseonce the code is sufficiently tuned for mysql you can indeed show thatany other database sucks running it ... regards, tom lane---(end of broadcast)---TIP 5: don't forget to increase your free space map settingsI think you're right, except that they don't recommend MySQL for production either; they seem happiest with MS SQL Server and Oracle, which seems a little odd for an open source project. If they haven't figured out how to get their app to work well with either MySQL or PostgreSQL, it seems a little disingenuous to claim they support them on the site's front page. Oh well. (shrug) -- Wes Sheldahl[EMAIL PROTECTED]
Re: [GENERAL] Encoding problem
Albe Laurenz a écrit :
I am trying to remove accents from a string. I found a nice
solution for this on postgresqlfr, using the to_ascii()
function.
Now, the problem I have is :
mydb=# SELECT to_ascii(convert('abcdef', 'LATIN9'));
ERROR: encoding conversion from UTF8 to ASCII not supported
Why is the conversion to LATIN9 not working as expected ?
My database's encoding is UTF8.
Maybe you actually want to
test=> select to_ascii(convert('ábcdêf', 'LATIN9'), 'LATIN9');
to_ascii
--
abcdef
(1 row)
Indeed !!!
Thanks a lot !
I suppose that not giving the encoding to 'to_ascii'
defaulted to the database encoding ?
Sorry for this mistake, I did not realize that this setting
existed for the to_ascii function...
Thanks again !
--
Arnaud
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Encoding problem
> I am trying to remove accents from a string. I found a nice
> solution for this on postgresqlfr, using the to_ascii()
> function.
>
> Now, the problem I have is :
>
> mydb=# SELECT to_ascii(convert('abcdef', 'LATIN9'));
> ERROR: encoding conversion from UTF8 to ASCII not supported
>
> Why is the conversion to LATIN9 not working as expected ?
> My database's encoding is UTF8.
Maybe you actually want to
test=> select to_ascii(convert('ábcdêf', 'LATIN9'), 'LATIN9');
to_ascii
--
abcdef
(1 row)
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] postgresql-python-3.8.1-3PGDG.i686.rpm
Hello I've downladed postgresql-python-3.8.1-3PGDG.i686.rpm from the http://www.postgresql.org/ftp/binary/v8.1.5/linux/rpms/redhat/rhel-es-4/ to install on a CentOS 4.4 and get this error : # rpm -Uvh postgresql-python-3.8.1-3PGDG.i686.rpm error: Failed dependencies: python(abi) = 2.4 is needed by postgresql-python-3.8.1-3PGDG.i686 does Redhat es 4 has python 2.4 ? Thank you Leonel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Wordpress & PostgreSQL ...
Marc, We are working on making the port to PostgreSQL ... may be for the end of this month. We have wrote to the Wordpress team ... but without any answer for the moment ... :o( When it'll be ready from our part, I'll inform you. Best regards, Le dimanche 22 octobre 2006 00:58, Marc G. Fournier a écrit : > Does anyone know of any work being done to get wordpress ported to > PostgreSQL? My search on the web finds emails from March of this year > concerning some ppl more or less "looking into it", but I can't find > anything that indicates they've done much more then talk :( > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] > Yahoo . yscrappy Skype: hub.orgICQ . 7615664 > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Hervé Piedvache Elma Ingénierie Informatique Groupe Maximiles S.A. 3 rue d'Uzès F-75002 - Paris - France Pho. 33-144949901 Fax. 33-144882747 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Encoding problem
Hi List !
I am trying to remove accents from a string. I found a nice
solution for this on postgresqlfr, using the to_ascii()
function.
Now, the problem I have is :
mydb=# SELECT to_ascii(convert('abcdef', 'LATIN9'));
ERROR: encoding conversion from UTF8 to ASCII not supported
Why is the conversion to LATIN9 not working as expected ?
My database's encoding is UTF8.
Furthermore, in PgAdmin, running SELECT convert(somestring,
'LATIN9'); returns empty strings whenever somestring
contains special (accentued, in my case) characters.
SELECT Length(convert(somestring, 'LATIN9')); looks OK though !
I think my misunderstanding of encoding is driving me crazy...
Could anyone help me on this ?
Thanks !
--
Arnaud
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Fwd: [GENERAL] how to check SQLSTATE
Hi,Is there a way to check how many records where affected by a delete or update sentence in a trigger function ??thanksHugo On 10/6/06, Hugo <[EMAIL PROTECTED]> wrote: OK, thanks, I'm porting a lot of sybase storedprocedures, and there are lots of "if sqlstate.." statements that i need to translate to pgpsql, obviously I cannot use the SQLSTATE pseudo variable directly in my fuctions, according to the documentation i can be used in a exception block but couldn't find any examples, could you point me to some document or give some advice on how i could use sqlstate pseudo variable? thanks againHugoOn 10/6/06, Tom Lane < [EMAIL PROTECTED]> wrote: Hugo <[EMAIL PROTECTED]> writes:> Hi again, thanks for your guidance, this is the error I got trying to save > my fuction:> ERROR: unrecognized exception condition "no_data" > CONTEXT: compile of PL/pgSQL function "fn_verificar_aportes_socio" near> line 36NO_DATA isn't an error condition, only a warning, thus there is no casein which an exception block would trap it. So plpgsql doesn't bother to recognize it.regards, tom lane
Re: [GENERAL] strange encoding behavior
Jeff Davis wrote: > I have a UTF8 encoded database. I can do > > => SELECT '\xb9'::text; > > But that seems to be the only way to get an invalid utf8 byte sequence > into a text type. [...] > So, if I were to sum this up in a single question, why does cstring not > accept invalid utf8 sequences? And if it doesn't, why are they allowed > in any text type? I would say that it should be impossible to get invalid UTF-8 bytes into a text on an UTF-8 database, and my opinion is that it is a bug or oversight if a typecast allows you to do so. The program you are talking about that needs to be able to store arbitrary bytes in a text column should be changed - maybe it is enough to change the data type of the database column from 'text' to 'bytea'. Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to determine initdb parameters on old database?
Joost Kraaijeveld wrote: On Mon, 2006-10-23 at 02:11 -0400, brian wrote: pg_controldata - display control information of a PostgreSQL database cluster pg_controldata [ datadir ] I was hoping for the actual command but this suggests deduction ;-) That is it. given: PG_DIR=/usr/local/pgsql do: $PG_DIR/bin/pg_controldata $PG_DIR/data or: man pg_controldata brian ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Overload after some minutes, please help!
Peter Bauer wrote: Hi all, for further investigation we seperated the sub-SELECT from the DELETE statement and it looks like the SELECT is usually finished in some 100 milliseconds but after some minutes it suddenly takes some minutes. Explain analyzes before and after should give some insight into what's happening. It looks like the query plan changes after some minutes. What I think is happening (but w/o any proof I'm just guessing) is that the planner starts with statistics from a relatively empty table (or something similar statistics-wise) and never gets the opportunity to update its statistics with information about the newly inserted data. If that's the case, calling ANALYSE regularly should show improvement (I'm not sure if autovacuum also analyses - but if so, not frequently enough). It may also be that the statistics do not match the number of records and the data you have. Playing with the statistics size may show improvement. A quick question for the experts: Is the statistics size equivalent to a "sample" as known in statistical analysis? If so, there are mathematics to calculate the required sample size that should at least give people some idea what size to set it to. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SQL injection in a ~ or LIKE statement
On Oct 22 02:33, Uwe C. Schroeder wrote: > On Sunday 22 October 2006 12:32, Volkan YAZICI wrote: > > If I were you, I'd ask psycopg2 developers to implement parameters that > > are natively supported by PostgreSQL. With parameters, you won't mess up > > with any escaping or injection related issue. > > psycopg2 supports parameters which are escaped properly. You're wrong. psycopg uses Python style parameters and escapes the specified input before inserting into query string. See documentation of PQexecParams() for the "parameters" I mentioned. I think, you're confusing terms. I just checked psycopg2 source code and couldn't see any parameter implementation. Regards. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
