Re: [GENERAL] restoring one table?
On Thu, Aug 7, 2008 at 4:34 PM, Brian Maguire [EMAIL PROTECTED] wrote: We need to restore one table from a backup. What is the proper way to do this? Our backup command looks like this: pg_dump -C -Fc -S postgresql mydatabase today.backup.sqlc I am using PostgreSQL 8.1. You have read this page, right: http://www.postgresql.org/docs/8.1/interactive/app-pgrestore.html -- 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] Initdb problem on debian mips cobalt: Bus error
Okay, so it is indeed the linker's fault. Now try plan (a) --- can you find a more up-to-date toolchain? Well I've tried looking in apt, and the latest package is the version I've got, (the toolchain is more than just gcc isn't it though?) is there another way to get a more up to date toolchain? Here's what I'm running ont he qube; [EMAIL PROTECTED]:~$ gcc -v Using built-in specs. Target: mipsel-linux-gnu Configured with: ../src/configure -v --enable-languages=c,c++,fortran,objc,obj-c++,treelang --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --program-suffix=-4.1 --enable-__cxa_atexit --enable-clocale=gnu --enable-libstdcxx-debug --enable-mpfr --disable-libssp --enable-checking=release mipsel-linux-gnu Thread model: posix gcc version 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) And heres what I have on the servers at work; Way5a:/pgsql/logs# gcc -v Using built-in specs. Target: x86_64-linux-gnu Configured with: ../src/configure -v --enable-languages=c,c++,fortran,objc,obj-c++,treelang --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --program-suffix=-4.1 --enable-__cxa_atexit --enable-clocale=gnu --enable-libstdcxx-debug --enable-mpfr --enable-checking=release x86_64-linux-gnu Thread model: posix gcc version 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) Same version but different architectures. __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] pg_restore fails on Windows
Tom Tom wrote: Hello, We have a very strange problem when restoring a database on Windows XP. The PG version is 8.1.10 The backup was made with the pg_dump on the same machine. pg_restore -F c -h localhost -p 5432 -U postgres -d configV3 -v c:\Share\POSTGRES.backup pg_restore: connecting to database for restore Password: pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating SEQUENCE hi_value pg_restore: executing SEQUENCE SET hi_value pg_restore: creating TABLE hibconfigelement pg_restore: creating TABLE hibrefconfigbase pg_restore: creating TABLE hibrefconfigreference pg_restore: creating TABLE hibtableattachment pg_restore: creating TABLE hibtableattachmentxmldata pg_restore: creating TABLE hibtableelementversion pg_restore: creating TABLE hibtableelementversionxmldata pg_restore: creating TABLE hibtablerootelement pg_restore: creating TABLE hibtablerootelementxmldata pg_restore: creating TABLE hibtableunversionedelement pg_restore: creating TABLE hibtableunversionedelementxmldata pg_restore: creating TABLE hibtableversionedelement pg_restore: creating TABLE hibtableversionedelementxmldata pg_restore: creating TABLE versionedelement_history pg_restore: creating TABLE versionedelement_refs pg_restore: restoring data for table hibconfigelement pg_restore: restoring data for table hibrefconfigbase pg_restore: restoring data for table hibrefconfigreference pg_restore: restoring data for table hibtableattachment pg_restore: restoring data for table hibtableattachmentxmldata pg_restore: [archiver (db)] could not execute query: no result from server pg_restore: *** aborted because of error The restore unexpectedly fails on hibtableattachmentxmldata table, which is as follows: CREATE TABLE hibtablerootelementxmldata ( xmldata_id varchar(255) NOT NULL, xmldata text ) WITHOUT OIDS; and contains thousands of rows with text field having even 40MB, encoded in UTF8. The database is created as follows: CREATE DATABASE configV3 WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; The really strange is that the db restore runs OK on linux (tested on RHEL4, PG version 8.1.9). The pg_restore output is _not_ very descriptive but I suspect some dependency on OS system libraries (encoding), or maybe it is also related to the size of the CLOB field. Anyway we are now effectively without any possibility to backup our database, which is VERY serious. Have you ever came across something similar to this? Check what you have in your server logs (pg_log directory) and the eventlog around this time. There is probably a better error message available there. //Magnus Thank you for your hint. The server logs does not display any errors, except for 2008-08-08 11:14:16 CEST LOG: checkpoints are occurring too frequently (14 seconds apart) 2008-08-08 11:14:16 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:14:38 CEST LOG: checkpoints are occurring too frequently (22 seconds apart) 2008-08-08 11:14:38 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:14:57 CEST LOG: checkpoints are occurring too frequently (19 seconds apart) 2008-08-08 11:14:57 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:15:14 CEST LOG: checkpoints are occurring too frequently (17 seconds apart) 2008-08-08 11:15:14 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:15:36 CEST LOG: checkpoints are occurring too frequently (22 seconds apart) 2008-08-08 11:15:36 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:15:56 CEST LOG: checkpoints are occurring too frequently (20 seconds apart) 2008-08-08 11:15:56 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:16:16 CEST LOG: checkpoints are occurring too frequently (20 seconds apart) 2008-08-08 11:16:16 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. The warnings disappeared when the checkpoint_segments value was increased to 10. The restore still failed however :( The Windows eventlogs show no errors, just informational messages about starting/stopping the pg service. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re:[GENERAL] restoring one table?
Brian Maguire wrote: We need to restore one table from a backup. What is the proper way to do this? Our backup command looks like this: pg_dump -C -Fc -S postgresql mydatabase today.backup.sqlc I am using PostgreSQL 8.1. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Use the switch --table=NAME of pg_restore, i.e. something like this: pg_restore -C -F c S postgresql -d mydatabase --table=your_table -v today.backup.sqlc Tomas -- 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] Initdb problem on debian mips cobalt: Bus error
Glyn Astill [EMAIL PROTECTED] writes: Okay, so it is indeed the linker's fault. Now try plan (a) --- can you find a more up-to-date toolchain? Well I've tried looking in apt, and the latest package is the version I've got, (the toolchain is more than just gcc isn't it though?) is there another way to get a more up to date toolchain? gcc wasn't the suspected candidate --- the netbsd guys thought it was as or ld. On my Fedora machine those seem to be part of the binutils package; dunno how Debian handles it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need info on installer
Hi,Greetings!!!We have developed a software which uses PostgreSQL as one of the component. We would like to delever PostgreSQL along with our software as a single package. So we have to create an installer which installs PostgreSQL with all pre-install configuration first and then our software. Could you help me how to create a single installer? could you send PostgreSQL install script.ThanksRam
[GENERAL] How to get many data at once?
Hi, if I have such a table:
t_ref_id t_data
1 'abc'
2 '321'
1 'ddd'
2 'xyz'
9 '777'
...
I want to get data with a special t_ref_id:
SELECT t_data FROM THETABLE WHERE t_ref_id = '1';
I must use a while loop to extract the data (I'm using PHP):
$rows = array();
while (($row = pgsql_fetch_assoc($result) !== false) {
$rows[] = $row;
}
And if there are many matched rows, such as many hundreds or thousands of
rows, I think such a loop maybe inefficient.
How to do this in a more efficient way?
Thank you!
--
ddh
[GENERAL] compiling Xpath functions in PostgreSQL 8.3.3
Hi, I was trying to install postgreSQL with XML data type support. My OS is ubuntu , kernel 2.6.15.. PostgresSQL version is 8.3.3 .I have libxml2 and libxslt installed. I configured using ./configure --with-libxml --with-libxslt configure went fine.. 'make all' went fine 'make install' went fine. when i check the functions available inside PostgreSQL using '\df x*' , I can only see xpath() function that is related to xpath. I was able to use xpath() function properly. However,No other functions like xpath_bool() ,are available. When i run ' \i /usr/local/pgsql/share/pgxml.sql' in postgre , I get error saying file or directory named 'MODULE_PATHNAME' doesn't exist. How to get this MODULE_PATHNAME exist?. what is the easiest way to get the XML functioanlity (mainly xpath part like xpath_bool etc..) in PostgreSQL in Linux. I mean which linux flavours, or postgreSQl versions are recommendable. I couldn't find the documentation online for getting XML working in postgreSQL (except that configure with libxml ). THanks in Advance SagAr. -- View this message in context: http://www.nabble.com/compiling-Xpath-functions-in-PostgreSQL-8.3.3-tp18858857p18858857.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem using a xpath function
hello everyone
I'm working on PostgreSQL XML Extension.
My system is windows xp professional sp2 and
PostgreSQL 8.3.3 and I am sure installed xml2 module.
I have a 2 colums table:
(frase di creazione tabella : create table ecc ecc)
I have a problem using a xpath function.
My query is:
SELECT id_autori , xpath ('/Authority/Nome', testo)
FROM autori
WHERE xpath_bool('/Authority[Nome=ABELARDO]', testo) ;
I get this error:
ERROR : the function xpath_bool(unknown , xml) do not exsist at character 69
HINT: no function matches the given name and argument types. You might need
to add explicit type casts
Any Idea ?
thanks
--
View this message in context:
http://www.nabble.com/problem-using-a-xpath-function-tp18853909p18853909.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] result of UPDATE ... RETURNING not usable / real SELECT required?
Hi, here's a short example, the problem is at the end: DROP TABLE IF EXISTS foo; CREATE TABLE foo ( id SERIAL PRIMARY KEY, c2 VARCHAR NOT NULL ); INSERT INTO foo VALUES ( DEFAULT, 'foo' ); INSERT INTO foo VALUES ( DEFAULT, 'bar' ); INSERT INTO foo VALUES ( DEFAULT, 'baz' ); DROP TABLE IF EXISTS bar; CREATE TABLE bar ( id SERIAL PRIMARY KEY, c2 VARCHAR NOT NULL ); INSERT INTO bar VALUES ( DEFAULT, 'bla' ); INSERT INTO bar VALUES ( 4, 'blubb' ); SELECT * FROM foo; SELECT * FROM bar; -- This works BEGIN; UPDATE foo SET c2=bar.c2 FROM bar WHERE foo.id=bar.id RETURNING bar.id; INSERT INTO foo ( SELECT * FROM bar WHERE id NOT IN ( SELECT id FROM foo ) ); COMMIT; -- This doesn't: INSERT INTO foo ( SELECT * FROM bar WHERE id NOT IN ( UPDATE foo SET c2=bar.c2 FROM bar WHERE foo.id=bar.id RETURNING bar.id ) ); Question: Why is the last command not accepted? (ERROR: syntax error at or near foo (the foo after UPATE)) Shouldn't the result structure of UPDATE ... RETURNING ... be the same as the result structure from SELECT id FROM foo (note: structure, not content in the example above)? Regards, Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Heikkki's Visibility Map patch for postgres 8.4 ?
Hi, Is Heikki's Visibility Map patch included for the Postgresql 8.4 version http://archives.postgresql.org/pgsql-hackers/2007-11/msg00142.php If not whats the status of that patch? Im especially interested in the index-only scan mentioned there!!! Thanks Sharmila
Re: [GENERAL] Vacuum Vs Vacuum Full
Its 8.1 and I'm doing a Vacuum using the vacuumdb program. Thanks Matt, might be time for an upgrade. Date: Tue, 5 Aug 2008 11:21:44 -0400 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [GENERAL] Vacuum Vs Vacuum Full Adrian Klaver wrote: On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote: WARNING: database mydb must be vacuumed within 177009986 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in mydb.Which is reason I ask the question, is full vacuum backup useful for anything other than reclaiming disk space. Actually its not asking for a VACUUM FULL but a VACUUM of the full database, instead of selected tables. See below for complete details http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND BTW, what version of PostgreSQL is this? Database-wide vacuum is no longer required for XID wraparound issues. I think this was an 8.3 change but might have happened in 8.2, I don't remember. Matt _ It's simple! Sell your car for just $40 at CarPoint.com.au http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT
Re: [GENERAL] Create Table Dinamic
On Thu, Aug 07, 2008 at 10:55:06AM -0300, Anderson dos Santos Donda wrote: Each client has a db, and each db has the same tables. I don't need to share datas with the clients ( and I can't do it ) , because each clients have differents datas in yours tables. My function is to help me to create a new db with the tables. You may want to look into the template parameter of CREATE DATABASE. Whenever a database is created it's actually just copied from an existing database. It normally comes from template1, which is a basically empty and clean database that it's initialized when the cluster is created (installed). If you have lots of databases that are basically the same and unchanging, you may want to create the tables in one database (say clienttemplate) and do: CREATE DATABASE client101 TEMPLATE 'clienttemplate'; and all the tables/views/stored procedures/other definitions in the template will be automatically copied into the new database. One caveat, is that this is a once only operation. Once the new database has been created, the link back to the template is lost so any subsequent changes in the template won't also happen in the new database. For more details have a look at [1]. Sam [1] http://www.postgresql.org/docs/current/static/sql-createdatabase.html -- 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 info on installer
ramamurthy kv wrote: Hi, Greetings!!! We have developed a software which uses PostgreSQL as one of the component. We would like to delever PostgreSQL along with our software as a single package. So we have to create an installer which installs PostgreSQL with all pre-install configuration first and then our software. Could you help me how to create a single installer? could you send PostgreSQL install script. I assume you are talking about the Windows Installer. If that is indeed the case, you can find the osurce for it at http://pgfoundry.org/projects/pginstaller. //Magnus -- 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 get many data at once?
In response to 窦德厚(ddh) [EMAIL PROTECTED]:
Hi, if I have such a table:
t_ref_id t_data
1 'abc'
2 '321'
1 'ddd'
2 'xyz'
9 '777'
...
I want to get data with a special t_ref_id:
SELECT t_data FROM THETABLE WHERE t_ref_id = '1';
I must use a while loop to extract the data (I'm using PHP):
$rows = array();
while (($row = pgsql_fetch_assoc($result) !== false) {
$rows[] = $row;
}
And if there are many matched rows, such as many hundreds or thousands of
rows, I think such a loop maybe inefficient.
You're wrong. It's pretty much the only way.
The only way you can improve on that is to process that row immediately
instead of copying it from $row to $rows[].
How to do this in a more efficient way?
Use a more efficient language, such as C, instead of PHP.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
[EMAIL PROTECTED]
Phone: 412-422-3463x4023
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psqlodbc on Vista Ultimate 64
Olá, estou com um problema, o Windows Vista não reconhece o odbc do PostgreSQL, alguma dica? Hello, I have a problem, Windows Vista does not recognize the odbc of PostgreSQL, any hint? -- Lucas Felix de Sousa Técnico em Informática [EMAIL PROTECTED] Se enxerguei mais longe que outros homens, foi porque me ergui em ombros de gigantes.
Re: [GENERAL] Postgres 8.3.x installation on Fedora 9 system
On Thu, 2008-08-07 at 17:56 -0400, Jack Orenstein wrote: Can't find them. They aren't on postgresql.org (e.g. http://www.postgresql.org/ftp/binary/v8.3.3/linux/rpms/fedora/fedora-9-i386/), and apparently not on pgdg-83-fedora.repo. pgdg-83-fedora.repo uses http://yum.pgsqlrpms.org as the source --not our FTP site and its mirrors. Anyway, I am currently uploading Fedora-9 packages to ftp.postgresql.org. It will appear in the FTP site in a few hours. Regards, -- Devrim GÜNDÜZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
[GENERAL] pgbench
Hi, I can't find pgbench on the package we installed. I built, contrib pkg and installed, but that still didn't generate pgbench anywhere. Any idea where to get pgbench? -- Heeman Lee
Re: [GENERAL] Postgres 8.3.x installation on Fedora 9 system
On Thu, 2008-08-07 at 17:24 -0400, Jack Orenstein wrote: Using pgdg-83-fedora.repo, yum finds two RPMs, postgresql-8.3.3-1PGDG.f9.i386 and postgresql-libs-8.3.3-1PGDG.f9.i386. Uh... Use yum search postgresql :) sudo yum search postgresql|wc -l 145 :) -- Devrim GÜNDÜZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] How to get many data at once?
Hi,
PHP has several functions related to postgresql - including a function to
fetch all the rows of the resultset at once into an array and then have a
loop that extracts one row at a time from that :
http://in.php.net/manual/en/function.pg-fetch-all.php
search for all functions with pg_ prefix --- they are PHP functions
relating to PostgreSQL. You need not fetch rows one by one from pg, instead
fetch all the result rows into a php array and then loop to read them one by
one from that array.
Regards,
Chandra ASGI
On Thu, Aug 7, 2008 at 7:03 AM, 窦德厚(ddh) [EMAIL PROTECTED] wrote:
Hi, if I have such a table:
t_ref_id t_data
1 'abc'
2 '321'
1 'ddd'
2 'xyz'
9 '777'
...
I want to get data with a special t_ref_id:
SELECT t_data FROM THETABLE WHERE t_ref_id = '1';
I must use a while loop to extract the data (I'm using PHP):
$rows = array();
while (($row = pgsql_fetch_assoc($result) !== false) {
$rows[] = $row;
}
And if there are many matched rows, such as many hundreds or thousands of
rows, I think such a loop maybe inefficient.
How to do this in a more efficient way?
Thank you!
--
ddh
Re: [GENERAL] bytea encode performance issues
Steve Atkins wrote: So, yeah, you're right. Generally, email is too complex to deal with in the database as anything other than an opaque bytea blob, along with some metadata Only because that's the choice made by dbmail. As an IMAP server, it doesn't _have_ to do more. The downside is that the database is not as useful as it could be. I happen to have developed my own OSS project on exactly this idea: to have a database of mail with contents in normalized form and ready-to-be-queried. An picture of the schema can be seen here: http://www.manitou-mail.org/articles/db-diagram.html the architecture being this: http://www.manitou-mail.org/schemas/schema1.png There's nothing particularly remarkable about the schema, except that there is no trace left of the initial encapsulation of the data inside an RFC822 message and its associated rules about structure and encoding. The next step has been to write a MUA that talks directly in SQL to the database, and the resulting speed and efficiency is much better than with traditional IMAP-based MUAs. As an example related to search, I have this 10Gb database containing 600k mails, and hundreds of results for a full-text search typically come back to the MUA in a couple of seconds, Gmail-like, on a low-grade server to which I'm remotely connected through an SSH tunnel. SQL is so much better without an IMAP layer on top of it... Now, my dedicated MUA isn't as feature-rich as other popular mailers, and it can't be used offline despite being a desktop app, and has other deficiencies, but other mailer/server combinations come with their own sets of problems and inadequacies, too :) Regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] psqlodbc on Vista Ultimate 64
Hi. Is this helpful? http://winpg.jp/~saito/psqlODBC/psqlODBC64/ as for AMD64. http://www.geocities.jp/inocchichichi/psqlodbc/index.html Regards, Hiroshi Saito - Original Message - From: Lucas Felix To: pgsql-general@postgresql.org Sent: Saturday, August 09, 2008 12:00 AM Subject: [GENERAL] psqlodbc on Vista Ultimate 64 Olá, estou com um problema, o Windows Vista não reconhece o odbc do PostgreSQL, alguma dica? Hello, I have a problem, Windows Vista does not recognize the odbc of PostgreSQL, any hint? -- Lucas Felix de Sousa Técnico em Informática [EMAIL PROTECTED] Se enxerguei mais longe que outros homens, foi porque me ergui em ombros de gigantes. -- 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] pgbench
Never mind. I found it. On Fri, 2008-08-08 at 12:16 -0400, Heeman Lee wrote: Hi, I can't find pgbench on the package we installed. I built, contrib pkg and installed, but that still didn't generate pgbench anywhere. Any idea where to get pgbench? -- Heeman Lee -- Heeman Lee
[GENERAL] Unlinked files in PGDATA/base following unclean shutdown
Our application is running Postgres 7.4, (working on conversion to 8.3 right now). Our testing involves various forms of violence, including shutting off power and kill -9 postmaster. Occasionally we observe a form of database corruption in which one of the files storing a table or index disappears. The logs will contain ERRORs that look like this: could not open relation some_table_name: No such file or directory When this happens, and I cross-reference the pg_class.oid with the expected file under PGDATA, the file is missing (and does not appear to be in lost+found). I have fsync set to true, and wal_sync_method set to fsync. A few questions about this: 1) Why is this happening? 2) To help investigate this problem, I've written a script to cross-reference pg_class and the files in PGDATA/base. (I know that I should use pg_class.relfilenode instead of pg_class.oid -- I'll fix that.) The question is how to check for consistency in the case of large tables, which are split into multiple segments, (e.g. 123456.1, 123456.2). I.e., how can I find out how many segments there should be? Any chance it's as simple as (pg_class.relpages + SUITABLE_CONSTANT - 1) / SUITABLE_CONSTANT? Jack -- 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 postgresql-jdbc rpm with Sun JDK
Tom Lane wrote: Kevin Murphy [EMAIL PROTECTED] writes: Speaking as a near-ignoramus, would a simple RPM that wraps the binary jar file make sense? Sure, if you want to do it that way. We did in fact do it that way up till about 8.0. We (or at least I) moved away from it because of Red Hat's policy that source RPMs should contain only, well, source. But if you roll your own you certainly need not be bound by that ideology. After looking in more detail at what the gcj packages install, I've decided it's no big deal to just use Devrim's postgresql-jdbc-8.3.603-1PGDG.rhel5.x86_64.rpm package as is. While the java-1.4.2-gcj-compat and libgcj RPMs are needlessly installed, they don't interfere with Rocks' Java metapackage (roll), as I had feared. Thanks for the responses, Kevin Murphy -- 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] Unlinked files in PGDATA/base following unclean shutdown
Jack Orenstein [EMAIL PROTECTED] writes: Our application is running Postgres 7.4, (working on conversion to 8.3 right now). Our testing involves various forms of violence, including shutting off power and kill -9 postmaster. Do you have reason to trust either your kernel or your disk drives under such abuse? In general I'd bet on the drives being at fault a lot sooner than any other part of the food chain. Complete disappearance of files that should be there is a fault at the filesystem level or lower, anyway. The question is how to check for consistency in the case of large tables, which are split into multiple segments, (e.g. 123456.1, 123456.2). I.e., how can I find out how many segments there should be? The kernel-defined EOF is the truth, the whole truth, and nothing but the truth. There is no other authority. All segments before the last one should be exactly 1GB, but the last one can be anything up to that. Consult the comments in md.c for more details. (I think 7.4 may treat some corner cases differently from 8.3 anyway.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ALTER ROLE role-name-with-hyphen
Hi all - This is probably a new bee question... but, how do I change the password of a role that has a hyphen in the name? == $ createuser --no-superuser --createdb --no-createrole foo-bar $ psql Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar'; ERROR: syntax error at or near - LINE 1: ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar'; ^ == So I can create the role but I can't figure out how to modify it. I've tried a variety of quoting and backslashing and such, but no luck yet any ideas? Thanks, Tom -- 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] ALTER ROLE role-name-with-hyphen
Tom Copeland [EMAIL PROTECTED] writes: This is probably a new bee question... but, how do I change the password of a role that has a hyphen in the name? You need double quotes, not single quotes. Read about SQL identifier syntax in the fine manual ... regards, tom lane -- 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] ALTER ROLE role-name-with-hyphen
On Aug 8, 2008, at 4:57 PM, Tom Lane wrote: Tom Copeland [EMAIL PROTECTED] writes: This is probably a new bee question... but, how do I change the password of a role that has a hyphen in the name? You need double quotes, not single quotes. Read about SQL identifier syntax in the fine manual ... Argh I could have sworn I tried that... awesome, that works great, thanks much! Yours, Tom -- 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] ALTER ROLE role-name-with-hyphen
use the double quotes () ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar' --- On Fri, 8/8/08, Tom Copeland [EMAIL PROTECTED] wrote: From: Tom Copeland [EMAIL PROTECTED] Subject: [GENERAL] ALTER ROLE role-name-with-hyphen To: pgsql-general@postgresql.org Date: Friday, August 8, 2008, 8:49 PM Hi all - This is probably a new bee question... but, how do I change the password of a role that has a hyphen in the name? == $ createuser --no-superuser --createdb --no-createrole foo-bar $ psql Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar'; ERROR: syntax error at or near - LINE 1: ALTER USER foo-bar WITH ENCRYPTED PASSWORD 'foo-bar'; ^ == So I can create the role but I can't figure out how to modify it. I've tried a variety of quoting and backslashing and such, but no luck yet any ideas? Thanks, Tom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Checkpoints writes
On Thu, 7 Aug 2008, Cyril SCETBON wrote: What's the way to count the read/write bytes of the checkpoint process before 8.3 (no pg_stat_bgwriter view :-[ ) I want to distinguish bytes written by checkpoints and others written by the background process The reason that view was added was because it's really hard to figure that out in earlier versions. Theoretically you could have some operating system level program that tracked I/O on a per-process basis, noting which one was the background writer process and counting those separately. I found it easier to work on adding the counters instead. It's not really complete, but I did have a functional prototype of a pg_stat_bgwriter implementation that worked against 8.2 if that helps you any: http://www.westnet.com/~gsmith/content/postgresql/perfmon82.htm -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
