Re: [GENERAL] LISTEN considered dangerous
Martijn van Oosterhout kleptog@svana.org writes: It's slightly surprising though. I havn't seen anyone else complain about this before though. The only way to fix this is to make the LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs also. There isn't anything very desirable about the table-based approach to NOTIFY :-( I've previously proposed switching to an implementation similar to sinval messaging, which would completely eliminate the need for notifiers to be aware of who is listening. However, I'm unconvinced that the OP's complaint is valid. I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Otherwise, what of BEGIN; LISTEN foo; ROLLBACK; ? If I get some events for foo after this I'd surely think it was broken. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN considered dangerous
Martijn van Oosterhout wrote: A: BEGIN A: SELECT * FROM foo and cache the result. A: LISTEN foochange B: BEGIN B: update foo B: NOTIFY foochange B: COMMIT A: COMMIT Eh? At the point the LISTEN is run, the NOTIFY hasn't committed, so a row is inserted. At the time the NOTIFY is committed, the LISTEN hasn't committed yet so won't be visible. Only LISTEN is stored, not NOTIFY so there's nothing wrong with the read-committed semantics. No, that's all well and good it behaves as you'd expect when you know how it's implemented, that doesn't make it more usable though. It's slightly surprising though. I havn't seen anyone else complain about this before though. I'm likely to do that:) The only way to fix this is to make the LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs also. Not at all. The listen should simply listen for events issued at the start of the transaction it's executed in. To do that without timetravel we'd need to store all events from all transactions, but that's not too bad if it's implemented right. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] LISTEN considered dangerous
Tom Lane wrote: However, I'm unconvinced that the OP's complaint is valid. I'm unconvinced that I've stated the problem clearly enough. I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Naturally, the listen should not do anything at all when followed by a rollback. However if you start listening in a transaction then you should get all events that have happened after the snapshot that the transaction represents (the start of the transaction). That means that events that happen after the start of the transaction have to be returned in the next transaction. -- Regards Flemming Frandsen - http://dion.swamp.dk - YAPH ---(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] prepare, execute oids
I use PHP.When I make juste a simple insert likeInsert Into my_table (my_field) values ('value')I get back the OID of the inserted line.But when i use the prepareInsert fonction, I get nothing back. That's a pprobleme becaus i need to take the primary key of the line inserted in order to make post-operations.
I can't use the curval of a sequence because it can be the value of another insert made by another user.I use a prepare function because it's really faster (I need to insert many entries, nearly 5000) and i look for any way to make it faster and get the primary key of all the line inserted.
2006/8/1, Michael Fuhr [EMAIL PROTECTED]:
On Tue, Aug 01, 2006 at 06:19:29PM +0200, phazon wrote: I juste want to get the OID of the line inserted. Does anyone know how to do it ?It depends on the client interface.What interface are you using?
OIDs are deprecated as row identifiers; the preferred method is touse a sequence (serial column).To get a sequence's value you canuse currval() or lastval() (the latter available in 8.1).--Michael Fuhr
[GENERAL] Strange error message
Hi everyone, can please somebody let me know what this error message is supposed to mean: ERROR: could not open relation 1663/16858/9469857: Permission denied Strange thing about it is that i do not have a relation with that name. This somehow seems to go along with this other strange error message: 2006-08-01 05:08:22 LOG: archived transaction log file 0001002000D2 2006-08-01 05:11:47 LOG: archived transaction log file 0001002000D3 2006-08-01 06:06:32 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:33 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 WARNING: transaction log file 0001002000D2 could not be archived: too many failures Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strange error message
it seems as if in /pg_xlog/archive_status the respective file 0001002000D2.ready was not changed to 0001002000D2.done. Can this be done manually? About the persmission denied error i still have absolutely no clue! Christian Rengstl [EMAIL PROTECTED] wrote on 08/02/06 9:27 am: Hi everyone, can please somebody let me know what this error message is supposed to mean: ERROR: could not open relation 1663/16858/9469857: Permission denied Strange thing about it is that i do not have a relation with that name. This somehow seems to go along with this other strange error message: 2006-08-01 05:08:22 LOG: archived transaction log file 0001002000D2 2006-08-01 05:11:47 LOG: archived transaction log file 0001002000D3 2006-08-01 06:06:32 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:33 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 WARNING: transaction log file 0001002000D2 could not be archived: too many failures Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] prepare, execute oids
On Wed, Aug 02, 2006 at 09:25:43AM +0200, phazon wrote:
When I make juste a simple insert like
Insert Into my_table (my_field) values ('value')
I get back the OID of the inserted line.
You really don't want to use OID, you want a sequence. (Hint: OIDs are
not really unique, not a primary key).
I can't use the curval of a sequence because it can be the value of another
insert made by another user.
currval() is the last value in *your current session*. It won't be
affected by other users.
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Strange error message
am 02.08.2006, um 9:27:22 +0200 mailte Christian Rengstl folgendes: Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! My guess: Access Denied on the filesystem for the postgres-User. PS.: Windows, right? HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strange error message
On Wed, Aug 02, 2006 at 09:34:25AM +0200, Christian Rengstl wrote: it seems as if in /pg_xlog/archive_status the respective file 0001002000D2.ready was not changed to 0001002000D2.done. Can this be done manually? About the persmission denied error i still have absolutely no clue! It's not done, so why should it be marked done? It says quite clearly in the logs that it failed (error 1). As for the permission denied, that file has to exist, just look more carefully. This is usually caused by broken anti-virus software, but it could really be a permission problem... Don't forget that /'s are path seperators too, so it's under the 1663 subdirectory. Hope this helps, Christian Rengstl [EMAIL PROTECTED] wrote on 08/02/06 9:27 am: Hi everyone, can please somebody let me know what this error message is supposed to mean: ERROR: could not open relation 1663/16858/9469857: Permission denied Strange thing about it is that i do not have a relation with that name. This somehow seems to go along with this other strange error message: 2006-08-01 05:08:22 LOG: archived transaction log file 0001002000D2 2006-08-01 05:11:47 LOG: archived transaction log file 0001002000D3 2006-08-01 06:06:32 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:33 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 WARNING: transaction log file 0001002000D2 could not be archived: too many failures Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(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 -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Strange error message
Unfortunately it's windows, but nevertheless it worked now for weeks and suddenly the postgre user should not be able to copy anymore? Besides, the file was copied and then disappeared from the pg_xlog directory, nevertheless postgre kept trying to copy the very same file. A. Kretschmer [EMAIL PROTECTED] wrote on 08/02/06 9:40 am: am 02.08.2006, um 9:27:22 +0200 mailte Christian Rengstl folgendes: Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! My guess: Access Denied on the filesystem for the postgres-User. PS.: Windows, right? HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strange error message
The thing is that the very same file was copied to the specified directory. Martijn van Oosterhout kleptog@svana.org wrote on 08/02/06 9:43 am: On Wed, Aug 02, 2006 at 09:34:25AM +0200, Christian Rengstl wrote: it seems as if in /pg_xlog/archive_status the respective file 0001002000D2.ready was not changed to 0001002000D2.done. Can this be done manually? About the persmission denied error i still have absolutely no clue! It's not done, so why should it be marked done? It says quite clearly in the logs that it failed (error 1). As for the permission denied, that file has to exist, just look more carefully. This is usually caused by broken anti-virus software, but it could really be a permission problem... Don't forget that /'s are path seperators too, so it's under the 1663 subdirectory. Hope this helps, Christian Rengstl [EMAIL PROTECTED] wrote on 08/02/06 9:27 am: Hi everyone, can please somebody let me know what this error message is supposed to mean: ERROR: could not open relation 1663/16858/9469857: Permission denied Strange thing about it is that i do not have a relation with that name. This somehow seems to go along with this other strange error message: 2006-08-01 05:08:22 LOG: archived transaction log file 0001002000D2 2006-08-01 05:11:47 LOG: archived transaction log file 0001002000D3 2006-08-01 06:06:32 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:33 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 LOG: archive command copy pg_xlog\0001002000D2 C:\Archiv\DBArchiv\0001002000D2 failed: return code 1 2006-08-01 06:06:34 WARNING: transaction log file 0001002000D2 could not be archived: too many failures Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(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 -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] prepare, execute oids
phazon wrote:
I use PHP.
When I make juste a simple insert like
Insert Into my_table (my_field) values ('value')
I get back the OID of the inserted line.
But when i use the prepareInsert fonction, I get nothing back. That's a
pprobleme becaus i need to take the primary key of the line inserted in
order to make post-operations.
I can't use the curval of a sequence because it can be the value of
another insert made by another user.
currval is transaction safe, it's not a problem you need to worry about.
http://www.postgresql.org/docs/8.1/static/functions-sequence.html
--
Postgresql php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Strange error message
Disk full or otherwise problems with the disk? I remember I saw somthing similiar some months ago and the reason was (AFAIR) something like a full partition or a partition set to readonly, or a broken mountpoint because of hardware errors. With Linux, not MSW. Regards, Frank On Wed, 02 Aug 2006 09:44:54 +0200 Christian Rengstl [EMAIL PROTECTED] thought long, then sat down and wrote: Unfortunately it's windows, but nevertheless it worked now for weeks and suddenly the postgre user should not be able to copy anymore? Besides, the file was copied and then disappeared from the pg_xlog directory, nevertheless postgre kept trying to copy the very same file. A. Kretschmer [EMAIL PROTECTED] wrote on 08/02/06 9:40 am: am 02.08.2006, um 9:27:22 +0200 mailte Christian Rengstl folgendes: Postgre copied the file 0001002000D2 to the specified directory and now it says it can't be copied...I really, really don't get it! My guess: Access Denied on the filesystem for the postgres-User. PS.: Windows, right? HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606Mail: [EMAIL PROTECTED] Telefax: 0271 231 8608Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651 pgpIipkeBc9SW.pgp Description: PGP signature
[GENERAL] Handling foreign_key_violation in plpgsql
Hi list,I have a table which is referenced with foreign keys from multiple other tables. Records in this table are deleted by on delete rules on those other tables. This means that if I want to drop one of the referencing tables, I first have to delete all records in that referencing table to ensure that no stale records are left in the referenced tables. (DROP will, of course, not fire the on delete rules.) However, due to another bug which I'm still investigating, deleting all records in a table and then dropping the table within one transaction block will simply fail. For this reason and for the reason of sometimes simply _forgetting_ to create (or fire) an on delete rule, I decided to write a simple plpgsql function to delete stale records in the referenced table. If I make a mock database with a few simple referencing tables and one referenced table, such a function works fine, but in the actual DB where I need the function, similar exception handling code will not be reached, even though I'm checking for the right error condition (foreign_key_violation). As such, the function will fail with the very error code that I'm catching. What is so confusing to me is that I've not been able to reproduce this with a simplified mock database.Hoping for a hit of the clue bat,Rowan-- Morality is usually taught by the immoral.
Re: [GENERAL] ECPG and COPY
On Mon, Mar 06, 2006 at 10:15:06AM -0500, Bruce Momjian wrote: Added for ecpg TODO: o Add COPY TO STDIN / STDOUT handling COPY TO STDOUT has been implemented, but I'm unsure whether COPY FROM STDIN really makes sense. Does anyone know a real life example where this would be needed and the work couldn't be done easier using psql? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ECPG and COPY
* Michael Meskes: COPY TO STDOUT has been implemented, but I'm unsure whether COPY FROM STDIN really makes sense. Does anyone know a real life example where this would be needed and the work couldn't be done easier using psql? COPY FROM STDIN saves lots of network round-trips. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ECPG and COPY
On Wed, Aug 02, 2006 at 12:03:45PM +0200, Florian Weimer wrote: * Michael Meskes: COPY TO STDOUT has been implemented, but I'm unsure whether COPY FROM STDIN really makes sense. Does anyone know a real life example where this would be needed and the work couldn't be done easier using psql? COPY FROM STDIN saves lots of network round-trips. So what? Could you please explain what this has to do with my original question? Somehow I don't get it, sorry. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pgstat.c: send/EINTR issue
Hello! I've came across recent change of postmaster/pgstat.c with comment regarding send/EINTR issue. Does it make sense to amend, for example, secure_write() in be_secure.c (part of libpq) in the same way? Am I right thinking it may fail during reloading configuration? Is it the only dangerous case? -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best Procedural Language?
Christopher Browne wrote: Martha Stewart called it a Good Thing when "Carlo Stonebanks" [EMAIL PROTECTED] wrote: I am interested in finding out a "non-religious" answer to which procedural language has the richest and most robust implementation for Postgres. C is at the bottom of my list because of how much damage runaway code can cause. I also would like a solution which is platorm-independent; we develop on Windows but may deploy on Linux. I would dearly love to see plPHP join the family as a real supported language, right in the distribution. Just thought I'd mention that while we're on the topic. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Performance/Issues with CMP and JBoss
Hi,I have performed some tests earlier on today, and i think the problem lies with Postgres and it's bad performance when being used with container managed persistence. I am covinced of it being an issue with postgres because it works really well with MySQL and Hypersonic, but not with Postgres. I have been reading around on the internet and it seems that Postgres does not work too well with container managed persistence, but surely all i have to do is just change some configuration settings, or can Postgres not be used with container managed persistence. Thanks,AndyOn 8/1/06, Douglas McNaught [EMAIL PROTECTED] wrote: Andy Dale [EMAIL PROTECTED] writes: The current problem we seem to have is that the data is persisted ok (or at least it seems to be in there with pgadmin), but cannot be read back out of the database all the time (in fact for about 90% of the time), the current behaviour of the application suggests it is trying to read it back out of the database (using EntityManager.find ()) before it has really been saved, and thus fails to find the data.Do i have to tweak some settings in the postgres.conf file ? i have tried turning off fsync (i do not want to do this, for reliability reasons) and it performed far better. Can anyone advise me on the changes i need to make to speed up the inserting of data, i know that turning autocommit off is supposed to increase performance.This is almost certainly a problem with your persistence layer rather than with Postgres.If you can see the data with PGAdmin then it's inthe database.It may be that the transaction that saves the object isnot committing quickly, and so other connections don't see the object until the commit happens.But that's not the fault of Postgres.-Doug
Re: [GENERAL] pgstat.c: send/EINTR issue
On Wed, Aug 02, 2006 at 02:57:59PM +0400, Ilja Golshtein wrote: Hello! I've came across recent change of postmaster/pgstat.c with comment regarding send/EINTR issue. Does it make sense to amend, for example, secure_write() in be_secure.c (part of libpq) in the same way? Am I right thinking it may fail during reloading configuration? Is it the only dangerous case? The point is that EINTR is not supposed to happen, at all, in the backend. It should only happen with non-blocking sockets (not used in backend) or interruption by a signal (disabled in the backend). My understanding of the EINTR change is that it's a windows issue, which doesn't totally follow the above rules. BTW, be_secure is used in the backend, fe-secure is used in libpq and does support non-blocking and EINTR. If it possible for the system to return EINTR in secure_read/secure_write, then we also need to worry about it during disk access and many other places. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Performance/Issues with CMP and JBoss
Andy Dale wrote: Hi, I have performed some tests earlier on today, and i think the problem lies with Postgres and it's bad performance when being used with container managed persistence. Is your problem performance or an error? It sounded like you were getting errors in your first post. I am covinced of it being an issue with postgres because it works really well with MySQL and Hypersonic, but not with Postgres. *What* works really well? Can you tell us what query/queries are giving you problems? I have been reading around on the internet and it seems that Postgres does not work too well with container managed persistence, but surely all i have to do is just change some configuration settings, or can Postgres not be used with container managed persistence. If you generate valid SQL then PostgreSQL can certainly process the queries. As to whether tuning will help, nobody can say because you haven't supplied details of: 1. Hardware 2. Operating System 3. Queries giving problems 4. Concurrency details 5. Current configuration settings 6. System activity (is CPU/RAM/IO maxed?) Without at least *some* of these facts nobody can say anything useful. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] when do pg_temp SCHEMAS get purged?
I create 2 temp tables on each connection to store session relevant variables.It works beautifully with code I robbed accross postgresql mailinglists:CREATE OR REPLACE FUNCTION set_quarant(mylvlquarant int4) RETURNS int4 AS$BODY$ BEGIN perform relname from pg_class where relname = 'quara_tmp' and case when has_schema_privilege(relnamespace, 'USAGE') then pg_table_is_visible(oid) else false end; if not found then create temporary table quara_tmp ( lvlquara integer ); else delete from quara_tmp; end if; insert into quara_tmp values (mylvlquarant); return 0; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;CREATE OR REPLACE FUNCTION get_quarant() RETURNS int4 AS$BODY$declareergebnis int4; BEGIN perform relname from pg_class where relname = 'quara_tmp' and case when has_schema_privilege(relnamespace, 'USAGE') then pg_table_is_visible(oid) else false end; if not found then return 0; else select lvlquara from quara_tmp into ergebnis; end if; if not found then ergebnis:=0; end if; RETURN ergebnis; END;$BODY$ LANGUAGE 'plpgsql' STABLE; Now I looked at system objects and detected, that schemas named PG_TEMPn, where n is a slowly growing integer,are created.Those schemas seem to get more and more and more. Is anyprocess taking care of purging the ones no longer needed? PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)(yes, autovacuum is set up, yes, I have vacuumed the database manually in addition)Harald Post-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] LISTEN considered dangerous
In article [EMAIL PROTECTED], Flemming Frandsen [EMAIL PROTECTED] writes: I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Naturally, the listen should not do anything at all when followed by a rollback. However if you start listening in a transaction then you should get all events that have happened after the snapshot that the transaction represents (the start of the transaction). Here you're contradicting yourself. In your second paragraph you state that LISTEN should get events unless later cancelled by a ROLLBACK. How should LISTEN know if its transaction will commit or abort? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] LISTEN considered dangerous
Flemming Frandsen [EMAIL PROTECTED] writes: The listen should simply listen for events issued at the start of the transaction it's executed in. BEGIN; SELECT sleep(10); LISTEN foo; No, I don't think so. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Strange error message
Christian Rengstl [EMAIL PROTECTED] writes: Unfortunately it's windows, but nevertheless it worked now for weeks and suddenly the postgre user should not be able to copy anymore? Broken anti-virus software. We've seen essentially identical reports before ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgstat.c: send/EINTR issue
Ilja Golshtein [EMAIL PROTECTED] writes: Does it make sense to amend, for example, secure_write() in be_secure.c (part of libpq) in the same way? It's already done, see the only caller of secure_write ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] when do pg_temp SCHEMAS get purged?
Harald Armin Massa [EMAIL PROTECTED] writes: Now I looked at system objects and detected, that schemas named PG_TEMPn, where n is a slowly growing integer, are created. Those schemas seem to get more and more and more. Is anyprocess taking care of purging the ones no longer needed? We don't bother. One row in pg_namespace is not worth removing, especially when it's likely to be needed again someday. (Now, if any of the *contents* of the temp namespaces don't go away at backend exit, that's another story...) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] when do pg_temp SCHEMAS get purged?
Tom, Those schemas seem to get more and more and more. Is anyprocess taking care of purging the ones no longer needed?We don't bother.One row in pg_namespace is not worth removing,especially when it's likely to be needed again someday.thanks for the information! Now I understand: every backend gets it's own pg_tempxx, that is why in production those number rose fairly quickly; and the pg_tempxx schemas gets recycled after the backend exits. Now if only I would sell my software after maximum simultanuos concurrend users, I would have a very good, free measurement :)Thank you very much,Harald-- GHUM Harald Massapersuadere et programmare Harald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] LISTEN considered dangerous
Tom Lane wrote: Flemming Frandsen [EMAIL PROTECTED] writes: The listen should simply listen for events issued at the start of the transaction it's executed in. BEGIN; SELECT sleep(10); LISTEN foo; No, I don't think so. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I was just about to say that if someone was to do the following they would get the previously commited state of foo after the sleep not how foo looked before the sleep otherwise every begin would need an entire DB snapshot to be taken? Seems like it should be no different. I have been following this thread as I use LISTEN but not in as intensive way as the op and for me its working fine... BEGIN select sleep(10); select * from foo; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where do Tcl questions go?
On 8/1/06, Carlo Stonebanks [EMAIL PROTECTED] wrote: I have been trying to figure out where to put my plTcl questions, and where the people most knowledgable about that topic may be – either on these mail lists or elsewhere. TCL is dead. Long live TCL. PLTCL was taken out of the core distribution, and is not in contrib. You have to download it from here http://gborg.postgresql.org/project/pgtclng/download/download.php There is documentation available as well as Win32 binaries. This list is as good a place as any to ask! - Ian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where do Tcl questions go?
I didn't see you answer on the mail list - so please forgive the direct reply. Thanks you very much for the response - do you develop in plTcl? I also see that this package is for pgtcl - I am looking for server-side support, i.e. plTcl. Where can I find that? More specifically, I am looking for the scripts pltcl_loadmod, pltcl_listmod and pltcl_delmod mentioned in the PostgreSQL documentation of the unknown command. Carlo -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ian Harding Sent: August 1, 2006 2:49 PM To: Carlo Stonebanks Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Where do Tcl questions go? On 8/1/06, Carlo Stonebanks [EMAIL PROTECTED] wrote: I have been trying to figure out where to put my plTcl questions, and where the people most knowledgable about that topic may be - either on these mail lists or elsewhere. TCL is dead. Long live TCL. PLTCL was taken out of the core distribution, and is not in contrib. You have to download it from here http://gborg.postgresql.org/project/pgtclng/download/download.php There is documentation available as well as Win32 binaries. This list is as good a place as any to ask! - Ian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Help with plpgsql - subst variable value
*Alexander Bodnar * Try ObjectSeqName TEXT := foobar; EXECUTE 'CREATE SEQUENCE ' || ObjectSeqName || ' start 1 increment 1 maxvalue 2147483647'; Thanks, Chris Steffen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ECPG and COPY
Michael Meskes wrote: On Mon, Mar 06, 2006 at 10:15:06AM -0500, Bruce Momjian wrote: Added for ecpg TODO: o Add COPY TO STDIN / STDOUT handling COPY TO STDOUT has been implemented, but I'm unsure whether COPY FROM STDIN really makes sense. Does anyone know a real life example where this would be needed and the work couldn't be done easier using psql? TODO updated: o Add COPY TO STDIN handling If you want the item removed, let me know. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] ECPG and COPY
* Michael Meskes: Could you please explain what this has to do with my original question? I assumed that ECPG did something special with TO STDOUT, like other interfaces do. This is not the case (that is, STDOUT is really standard output, so the functionality is not very useful. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ECPG and COPY
Florian Weimer wrote: * Michael Meskes: Could you please explain what this has to do with my original question? I assumed that ECPG did something special with TO STDOUT, like other interfaces do. This is not the case (that is, STDOUT is really standard output, so the functionality is not very useful. I am confused. STDOUT is already implemented. It was STDIN that we were asking if it was valuable. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plTcl - how to create proc/function libraries
True, I cannot find this in the current docs - that statement about the conditional compile was from some documentation I found from googling pltcl_modules pltcl_modfuncs I went back to the site i found it and snooped around and saw that it was a copy of docs from 1998. Sorry about the confusion, but I don't know whether the pltcl.dll (in other words, the Windows postgres installation) supports the unknown command, and pulling up this doc (in error, once again - sorry) came out of desperation for looking for more information on the creating and maintenace of those tables (pltcl_modules pltcl_modfuncs), and why I don't have the support scrtipts for them in my Windows installation. Carlo Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Carlo Stonebanks [EMAIL PROTECTED] writes: To enable this behavior, the PL/Tcl call handler must be compiled with -DPLTCL_UNKNOWN_SUPPORT set. Where are you reading that? There's no such sentence in the current docs, and no sign of any such conditional in the source code either. I'm not sure why it doesn't work for you, but it's not because it's been deliberately turned off... regards, tom lane ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LISTEN considered dangerous
Tom Lane [EMAIL PROTECTED] writes: I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Otherwise, what of BEGIN; LISTEN foo; ROLLBACK; ? If I get some events for foo after this I'd surely think it was broken. Well the rollback would roll back any consequences of the notify handler firing too. So the transactional nature would still be maintained. I'm not sure why you're imagining that the listen would still be in effect after the rollback. rollback would return the connection state to the same state it was in before the transaction. I Think of GUC variables like enable_* as a good analogy. If you fiddle with them their effects are felt immediately by your transaction. You don't have to commit to make those changes take effect. If you roll back your changes their original state is restored. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] LISTEN considered dangerous
On Wed, Aug 02, 2006 at 09:15:46AM -0400, Oisin Glynn wrote: I was just about to say that if someone was to do the following they would get the previously commited state of foo after the sleep not how foo looked before the sleep otherwise every begin would need an entire DB snapshot to be taken? Seems like it should be no different. I have been following this thread as I use LISTEN but not in as intensive way as the op and for me its working fine... BEGIN select sleep(10); select * from foo; Well, it depends on the transaction mode, read-committed and serialisable behave differently here. In the case of serializable, you will get what was before the sleep(), that's kind of the point. The whole point of MVCC is that taking a snapshot doesn't actually cost anything. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] ECPG and COPY
On Wed, Aug 02, 2006 at 09:49:40AM -0400, Bruce Momjian wrote: I assumed that ECPG did something special with TO STDOUT, like other interfaces do. This is not the case (that is, STDOUT is really standard output, so the functionality is not very useful. I am confused. STDOUT is already implemented. It was STDIN that we were asking if it was valuable. Yes, I was asking about FROM STDIN. TO STDOUT has been added lately. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] Best Procedural Language?
I would dearly love to see plPHP join the family as a real supported language, right in the distribution. You mean as opposed a real support language outside of the distribution? Being in core doesn't really gain us much except a prestige moniker. But to answer your question, there is some work that needs to be done first. Joshua D. Drake Just thought I'd mention that while we're on the topic. ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Autovacuum help..
Thanks again. I am wondering as to why the state changes to Transaction in idle when a query is executed. It'll be nice if that happens only when a real change is made (transaction starts) to the database and not when a select query occurs. This makes no sense. A select query is also a query affected by transactions. In the example above, if you're in a transaction started three hours ago, a SELECT will be looking at a version of the database as it was three hours ago. Also, select queries can change the database also. Consider nextval() for example. The real question is, why are you keeping the transactions open? If they don't need to be, just commit them when you go idle and everything can be cleaned up normally. I am not keeping transactions open. Anytime an insert/delete/update is performed, the change is immediately committed (or rolled back). It is when selects are done that is causing a problem. The flow may be as below: insert into table ; commit; idle (autovacuum can remove dead rows) some time elapses delete table ; commit; idle (autovacuum can remove dead rows) select * from ; read rows from result set Idle in transaction (autovacuum cannot remove dead rows) LONG time elapses (autovacuum cannot remove dead rows) . . The last select operation is the one of concern. I was just raising the point that select by itself (like the one here) probably shouldn't put the connection in Idle in transaction mode. Since my app does not do a commit (or rollback) after every select (and selects in my app don't modify the database), the connection is left in Idle in transaction state for several hours until a next insert/update/delete followed by commit takes it to idle state. And, autovacuum is unable to remove the dead rows until connection goes to idle state. Perhaps, the solution is that I should modify my app to do a rollback followed by every select. But that is a little awkward because selects don't really modify the database in my case. Thanks for your suggestions, sundar. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] prepare, execute oids
On Wed, Aug 02, 2006 at 09:25:43AM +0200, phazon wrote:
I use PHP.
When I make juste a simple insert like
Insert Into my_table (my_field) values ('value')
I get back the OID of the inserted line.
But when i use the prepareInsert fonction, I get nothing back. That's a
pprobleme becaus i need to take the primary key of the line inserted in
order to make post-operations.
The PHP source code has no prepareInsert function -- are you using
a third-party module? If so then try contacting that module's
maintainer. But as I mentioned previously and as Martijn pointed
out, OIDs aren't suitable as primary keys. See the documentation
and FAQ:
http://www.postgresql.org/docs/8.1/interactive/datatype-oid.html
http://www.postgresql.org/docs/faqs.FAQ.html#item4.12
I can't use the curval of a sequence because it can be the value of another
insert made by another user.
As Martijn and Chris mentioned, currval() is safe in this respect.
Its behavior is documented and is the subject of an FAQ item:
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.3
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Best Procedural Language?
plPHP is not as mature as plTcl (or is that plTclng). However it is very well developed and maintained. Heck, companies are even holding talks and training classes on it now. What is lacking in plPHP? To be honest, even though I am a Tcl developer I would rather develop in PHP, and I know next to NOTHING about PHP! The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - and I can't blame them. PHP looks and behaves like a normal programming language, so there's more likelyhood that other programmers will be able to maintain my code. (Imagine that - a developer worrying about how the NEXT developer will maintain his code! Think the idea will catch on?) I couldn't find a recent release of plPHP, and have no idea of its status. Carlo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best Procedural Language?
Carlo Stonebanks wrote: plPHP is not as mature as plTcl (or is that plTclng). However it is very well developed and maintained. Heck, companies are even holding talks and training classes on it now. What is lacking in plPHP? To be honest, even though I am a Tcl developer I would rather develop in PHP, and I know next to NOTHING about PHP! The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - and I can't blame them. PHP looks and behaves like a "normal" programming language, so there's more likelyhood that other programmers will be able to maintain my code. (Imagine that - a developer worrying about how the NEXT developer will maintain his code! Think the idea will catch on?) I couldn't find a recent release of plPHP, and have no idea of its status. ww.commandprompt.com/community/plphp/ Last release was 2005. This is the first release that is actually useful, IMHO, because it allows SQL commands buried in the code, prior releases did not. My own totally unscientific I-didn't-get-very-thorough result from a trial installation was that it was sloow, as in less than half the speed of some comparable code in plperl. But I never isolated what was causing the slowdown and so I can't really say much more. It was bad enough though that I abandoned it very quickly, sucked in my gut and coded some perl. Carlo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] LISTEN considered dangerous
On Aug 2, 2006, at 2:07 AM, Tom Lane wrote: that the OP's complaint is valid. I would still expect any reimplementation of notify messaging to honor the principle that a LISTEN doesn't take effect till you commit. Otherwise, what of Well, it would break our usage of LISTEN/NOTIFY if they did not honor transactions, so back-compatibility is in my book the most important reason not to change that behavior. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Performance/Issues with CMP and JBoss
Andy Dale wrote: Hi, I will explain in more details what the test (extremely simple) program is actually doing. A session bean receives some data (roughly 3K) and then persists this data to the database with EntityManager.persist() (using the EJB 3.0 Persistence API that comes with JBoss). Once the persist method returns a message is sent to a message driven bean where it tries to get the previously persisted data from the database using the EntityManager.find() method, this is where we run into problems with Postgres, it only seems to find the persisted object 5% of the time. I don't know if you class it as a performance problem or an error but seems as it works in other databases i am more inclined to classify this as an error. Well that's easy to decide. 1. Did the transaction that stored the object complete successfully? 2. Should the results of that transaction be visible from the reading transaction? If Yes Yes, but you can't find the object there's an error. When i say works really well, i mean it in the sense that a simple query that the EntityManager produces for the find operation is something as simple as select columns from table where primary key = ?, this is causing problems for Postgres, the query is in the form of a prepared statement so could this be causing any problems ? Prepared queries mean you can't do certain optimisations, but for a single-table fetch on columns with a unique index I'd expect an index to be used (assuming analyse has been run recently). As far as hardware goes my testing machine is: P4 3.0 GHz 1GB RAM 20GB of HD (IDE) But we intend to use a HP Prolient server with the following spec: Intel Xeon 3.3 GHz 2 GB RAM 146GB Ultra SCSI 320 The Operating System being run on both is Fedora Core 5 The server/machine also needs to run other programs and processes so we don't want the database to hog to much of the resources, about 10 - 20 % RAM (and how to configure it) and CPU, the current config as defined in the postgres.conf file is as so: Squeezing PostgreSQL and cache-space for its data into 256MB is going to depend on how large your DB is. Oh, and if you have a lot of updates then disk will probably be the limiting factor. # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # We don't really need to see all the comment lines #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 1000# min 16 or max_connections*2, 8KB each #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5# can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1024# min 64, size in KB #maintenance_work_mem = 16384# min 1024, size in KB #max_stack_depth = 2048# min 100, size in KB OK, so you haven't actually done any configuration. PG *will* crawl with the default settings, it's setup so you can install it on your 5-year old laptop without keeling over. I recommend you read the short article at the following URL and start from there. http://www.powerpostgresql.com/PerfList Oh, and if you don't know what vacuum, analyse and the autovacuum tool are you'll want to read the relevant parts of the manual. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] best ways to handle large matrix data
Hi, this maybe a more general question and not limited to postgresql.We are generating some matrix like data, e.g. one object will have a fixed number 10 values, and we have currently 300 objects. We can visualized the data as a 10 by 300 matrix, and each object/column has an identifier, each row has an identifier as well. The number of rows are fixed, but columns will increase by time. The data is type double. The queries that we encounter most often are that given a set of rowids, e.g. (1000, 2000, 2001, 3241), retrieve the slice of the data. (the set of rowids are usually generated from SQL)I know that NetCDF and HDF5 can be used to strore such information, but is there any way we can save the matrix in postgres database and have easy retrieval and addition without sacrificing too much space? If not, I guess I'll try to write some functions in postgres to do some retrieval from netcdf. Thanks.Wenjian
Re: [GENERAL] Autovacuum help..
On Wed, Aug 02, 2006 at 07:36:09AM -0700, Sundar Narayanaswamy wrote: select * from ; read rows from result set Idle in transaction (autovacuum cannot remove dead rows) LONG time elapses (autovacuum cannot remove dead rows) . . The last select operation is the one of concern. I was just raising the point that select by itself (like the one here) probably shouldn't put the connection in Idle in transaction mode. Since my app does not do a commit (or rollback) after every select (and selects in my app don't modify the database), the connection is left in Idle in transaction state for several hours until a next insert/update/delete followed by commit takes it to idle state. And, autovacuum is unable to remove the dead rows until connection goes to idle state. Sorry, selects still advance the transaction counter, create a snapshot, hold locks, can still fire triggers, update stats, call external functions, etc. Maybe in your case they don't but maybe someday you'll make a change to the database that will. Maybe modify your app so selects arn't run inside an explicit transaction. Then you don't need to commit or rollback anything. Perhaps, the solution is that I should modify my app to do a rollback followed by every select. But that is a little awkward because selects don't really modify the database in my case. I imagine commit is cheaper and safer than a rollback... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Best Procedural Language?
ww.commandprompt.com/community/plphp/ Last release was 2005. This is the first release that is actually useful, IMHO, because it allows SQL commands buried in the code, prior releases did not. That is actually wrong. Go here: http://projects.commandprompt.com/public/plphp There was a release just recently of a bunch of code that fixed a bunch of stuff but was never released. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best Procedural Language?
Carlo Stonebanks wrote: plPHP is not as mature as plTcl (or is that plTclng). However it is very well developed and maintained. Heck, companies are even holding talks and training classes on it now. What is lacking in plPHP? To be honest, even though I am a Tcl developer I would rather develop in PHP, and I know next to NOTHING about PHP! There are some known issues that cause it to crash, you can see the project site for an example. Also it requires the apache apxs stuff which doesn't make it the most portable. We are looking at ways to make it not have the apache requirement. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best Procedural Language?
Joshua D. Drake wrote: ww.commandprompt.com/community/plphp/ Last release was 2005. This is the first release that is actually useful, IMHO, because it allows SQL commands buried in the code, prior releases did not. That is actually wrong. Go here: Well I took it from your home page! :) begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] LISTEN considered dangerous
# [EMAIL PROTECTED] / 2006-08-02 07:57:55 +0200: I'm bothered by listen listening from the end of the transaction in stead of the start of the transaction. Sorry if this isn't what you're after, instead just a question: Why don't you issue the LISTEN in a separate transaction before going on? LISTEN foo; BEGIN; SELECT ... -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Where do Tcl questions go?
Ian Harding [EMAIL PROTECTED] writes: PLTCL was taken out of the core distribution, and is not in contrib. I think you confused pltcl with pgtcl. Totally different things (pltcl is server side, pgtcl client side). regards, tom lane ---(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] Where do Tcl questions go?
Carlo Stonebanks [EMAIL PROTECTED] writes: ... More specifically, I am looking for the scripts pltcl_loadmod, pltcl_listmod and pltcl_delmod mentioned in the PostgreSQL documentation of the unknown command. They should be in the same directory as the other postgres executables, if you have pltcl. $ ls ~/testversion/bin clusterdb* ecpg*pg_dumpall* postmaster@ createdb*initdb* pg_resetxlog*psql* createlang* ipcclean*pg_restore* reindexdb* createuser* pg_config* pltcl_delmod*vacuumdb* dropdb* pg_controldata* pltcl_listmod* droplang*pg_ctl* pltcl_loadmod* dropuser*pg_dump* postgres* $ If you don't see them, you should take it up with whoever created the package you're using. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Where do Tcl questions go?
Yes - this statement actually came from a helpful soul who had replied but did not realise I was talking about pltcl. I am looking into the unknown command because I believe there may be security issues with using spource or package commands to access proc libraries - and that the unknown command and its magic tables is the way to make those libs reside on the server and totally portable. Am I correct? Carlo Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Ian Harding [EMAIL PROTECTED] writes: PLTCL was taken out of the core distribution, and is not in contrib. I think you confused pltcl with pgtcl. Totally different things (pltcl is server side, pgtcl client side). regards, tom lane ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Where do Tcl questions go?
If you don't see them, you should take it up with whoever created the package you're using. I thought that's what I was doing - I downloaded postgresql-8.1.4-1.zip from http://www.postgresql.org/ftp/binary/v8.1.4/win32/ Is there somewhere else I should be directing my question? Carlo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] proper use of array datatype
On 8/1/06, Reece Hart [EMAIL PROTECTED] wrote: Eric Andrews wrote: I am not much of a schema designer and have a general questoin about the proper use of the array datatype. In my example, I have destinations, and destinations can have multiple boxes, and inside those boxes are a set of contents. what I want to do is search and basically mine data from the content sets. I would use arrays exclusively for data sets for which each datum is meaningless by itself (for example, a single coordinate in 3D, although there are better ways to handle points in postgresql). I would recommend against using arrays for any data you wish to mine, and instead recast these has-a relationships as many-to-one joins across at least two tables. For example, a row from the table destination has-a (joins to) rows from boxes, and a box has-a (joins to) contents. how would these tables look though? I cant have a table for each set of contents in a box... The same argument goes for a similar representation such as concatenated values in a text field. The fundamental principle is that it's relatively easy to turn join separate data into a set of values or concatenated list, but it's quite cumbersome to turn a set of values into easily searchable data (i.e., it's often expensive to peek inside the structure of the data for a single value). Furthermore, it's difficult or impossible to write check or foreign key constraints on data within such a structure. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] Performance/Issues with CMP and JBoss
Diagnosing JBossCMP issues is not for the faint of heart, in that one of its main raison d'etre's is to hide SQL knowledge away from the casual coder. Add into the mix the concurrency issues which naturally occur since an EJB container is multithreaded and the overly complicated JTA stuff and you've a real mess to diagnose when things don't work just so. When we ran CMP, we'd also run postgres in debugging mode so as to have it emit queries onto stdout so that we could watch exactly what the CMP was doing. Try starting up postgres ala: /usr/local/pgsql/bin/postmaster -d 2 -i -D /usr/local/pgsql/data 21 | grep LOG Your mileage may vary, as would your PGDATA dir etc. Read the docs on the postmaster. But the end result is the ability to watch each query fly by on your development machine -- letting you see the order of which updates, inserts, selects, and commits happen from the postgres backend's perspective. I suspect the original poster has code issues being tickled by java threading issues interacting poorly with their default transaction isolation level of READ COMMITTED -- their inserts are being done in one thread / JTA transaction, while the read is being done in another and is loosing the race -- the inserting thread has not committed yet. Running the backend in debugging mode should let you see the select happening _before_ the first thread has committed its transaction. Postgres is doing exactly what it is being told -- if the inserting transaction has not yet committed, and the reading transaction's isolation level is set to READ COMMITTED, then postgres _will_not_ return anything to the reading connection / thread which has not yet been committed. Good luck with reading and following all of the EJB and CMP specifications, the JBossCMP documentation, the JTA spec, and then swallowing all of postgres [ or any other SQL backend ]. If you don't have all of 'em fully understood yet, you will have to one day if you continue with all that fat tech which was supposed to make things easy for you. CMP is a very leaky overcomplicated abstraction. James Robinson Socialserve.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL on RAM Disk / tmpfs
I'm working on a postgres instance (8.1.2 running on Solaris 10) where the data directory (including WAL) is being mounted on tmpfs. Based on this, and with knowledge that fsync is disabled, I'm operating under the assumption that recoverability is not a precondition for optimized performance. With that assumption, I have several questions, some performance-related, others internals-related:1. Should there be any perceptible difference between using a RAM disk and tmpfs? Would the fact that the RAM disk were fixed-size make it at all faster?2. Would there be any benefit to having WAL on a separate RAM disk? I.e., would RAM access be contentious in anything resembling the way disk access is contentious? One possible setup would be a fixed-size RAM disk for data and WAL on tmpfs, for instance.3. In this scenario, is the strategy of keeping checkpoints far apart still a valid performance consideration? I would expect that there would be an optimal spot on the curve somewhere on the axes of checkpoint distance and available memory. During testing on the RAM disk, one problem encountered was that increasing checkpoint_segments caused the RAM disk to fill up, which suggests one potential benefit of pushing WAL off to tmpfs but possibly leaving data on the RAM disk. There would be no reason to push for aggressive checkpointing, would there?4. Considering that recoverability is not a precondition, is there an easy patch that could be applied to the 8.1.x series from 8.1.4 on that would allow disabling full_page_writes? For a database in RAM with high write volume, is this setting even likely to make a difference?5. Does wal_debug provide insight into whether LogInsert is being forced to write WAL buffers? This would seem to be a useful indicator as to whether wal_buffers was set high enough. (A side note: I couldn't find reference to LogInsert or LogFlush in the source; would it make sense to have the documentation refer to the actual functions?) Unfortunately, I don't have access to a system that can be easily recompiled for me to test this. A corollary question: if data and pg_xlog are both in RAM, does LogInsert still spill to the kernel cache, or would it spill to RAM? --Thomas F. O'ConnellSitening, LLChttp://www.sitening.com/3004B Poston AvenueNashville, TN 37203-1314615-469-5150 x802615-469-5151 (fax)
[GENERAL] logic/db question
hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. name parentIDID foo- 1 cat 1 2 dog 2 3 my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? i had done this awhile ago.. but can't recall how i did it.. thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best Procedural Language?
Carlo Stonebanks wrote: plPHP is not as mature as plTcl (or is that plTclng). However it is very well developed and maintained. Heck, companies are even holding talks and training classes on it now. What is lacking in plPHP? To be honest, even though I am a Tcl developer I would rather develop in PHP, and I know next to NOTHING about PHP! The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - and I can't blame them. PHP looks and behaves like a normal programming language, so there's more likelyhood that other programmers will be able to maintain my code. (Imagine that - a developer worrying about how the NEXT developer will maintain his code! Think the idea will catch on?) I couldn't find a recent release of plPHP, and have no idea of its status. It sort of works if your platform is not too deviated from the mainstream Linux stuff (i.e. you're not using threaded PHP for example), and you avoid the stuff that we know cause server crashes. Also, array handling is very suboptimal -- it works for the simplest cases but fails if you get too clever (where too clever is not really very clever). There are probably other buggy areas I forget. I would suggest you to report the bugs you find, of which there will be plenty, but on the other hand it will be a waste of your time because there aren't any development resources devoted to it currently. I haven't measured performance at all. Oh, and it's called PL/php, not plPHP. In short, I suggest you look at PL/Perl. It is also a normal programming language. Joshua Drake wrote: However it is very well developed and maintained. I disagree. And I was the maintainer last time I checked, so you'd say my opinion carries some weight. Heck, companies are even holding talks and training classes on it now. Companies are run merely to make money. The fact that some of them make money by training people to use broken products does not make the products any less broken. While I am happy that people use PL/php, I would be much happier if it wasn't broken. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Join Question
Question,What is the difference between left join, and left outer join?I know the difference between inner and outer joins, but I was thinking that left join == inner join. But from what I am now seeing, it appears that PG is equating left join to left outer join. Is this correct? Thanks,Chris
Re: [GENERAL] Join Question
On Wed, 2006-08-02 at 14:32, Chris Hoover wrote: Question, What is the difference between left join, and left outer join? I know the difference between inner and outer joins, but I was thinking that left join == inner join. But from what I am now seeing, it appears that PG is equating left join to left outer join. Is this correct? A left or right join IS an outer join, as is a full join. the outer is just syntactic sugar. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best Procedural Language?
I disagree. And I was the maintainer last time I checked, so you'd say my opinion carries some weight. Because it is not currently being worked does not mean it is not maintained. It means that it is not currently being worked on. It is by no means a dead project. Heck, companies are even holding talks and training classes on it now. Companies are run merely to make money. That is a farse. Companies exist at the benefit of the shareholders. The shareholders may deem that their main goal is making money in which case your argument is valid. However if CMD took that approach I would be far richer and doing a lot more then PostgreSQL. We could easily double our revenue just by offering MySQL + PostgreSQL support and development. The fact that some of them make money by training people to use broken products does not make the products any less broken. While I am happy that people use PL/php, I would be much happier if it wasn't broken. Well that would require not using PHP at all wouldn't it? ;) However, Alvaro is correct there are currently no resources dedicated to PL/php. There will be in the future but for now we are busy with other things. It is however, BSD licensed please feel free to actually contribute. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Tape backup, 2 versions, same database name, which is pg_dump backing up
Yesterday a consultant upgraded me from 7.3.4 to 8.1.4 on a RedHat 9 server. To make sure everything was okay we left the server so that both versions of Postgresql load. 7.3.4 loads on the default port of 5432 and 8.1.4 loads on port 55432 . My database was moved into the new version so both the old and new databases have the same name. I have a little .sh file that runs a tape backup using pg_dump and I am wondering if both versions are running how do I know which version of the pg_dump is running and which version of the database is being backed up? The backup command I use is pg_dump -U postgres -C -D -f /tmp/$(date+%F)owl.sql owl then I use tar to copy the file to tape. Please answer to me as well as the list, I am on digest. Thanks, *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message.
Re: [GENERAL] logic/db question
Typically, a temp table is used for this. Beware of circular references. Fred - Wilma - Betty - Barney - Fred -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of bruce Sent: Wednesday, August 02, 2006 11:55 AM To: 'PgSQL-General' Subject: [GENERAL] logic/db question hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. nameparentIDID foo - 1 cat 1 2 dog 2 3 my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? i had done this awhile ago.. but can't recall how i did it.. thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump backing up
You'll need to specify the non-default port explicitly in your pg_dump command in order to back up the postmaster running on 55432.E.g., pg_dump -p 55432 -U postgres -C -D -f /tmp/$(date+%F)owl.sql owlBy default, the postgres command-line utilities attempt to connect to 5432 (or $PGPORT or whatever is configured as the default port).In the meantime, you're still backing up the 7.3.4 postmaster with that script. --Thomas F. O'ConnellSitening, LLChttp://www.sitening.com/3004B Poston AvenueNashville, TN 37203-1314615-469-5150 x802615-469-5151 (fax) On Aug 2, 2006, at 3:47 PM, [EMAIL PROTECTED] wrote:Yesterday a consultant upgraded me from 7.3.4 to 8.1.4 on a RedHat 9 server. To make sure everything was okay we left the server so that both versions of Postgresql load. 7.3.4 loads on the default port of 5432 and 8.1.4 loads on port 55432 . My database was moved into the new version so both the old and new databases have the same name. I have a little .sh file that runs a tape backup using pg_dump and I am wondering if both versions are running how do I know which version of the pg_dump is running and which version of the database is being backed up? The backup command I use is pg_dump -U postgres -C -D -f /tmp/$(date+%F)owl.sql owl then I use tar to copy the file to tape. Please answer to me as well as the list, I am on digest. Thanks, *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message.
Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump
Thomas F. O'Connell [EMAIL PROTECTED] wrote on 08/02/2006 02:04:35 PM: You'll need to specify the non-default port explicitly in your pg_dump command in order to back up the postmaster running on 55432. E.g., pg_dump -p 55432 -U postgres -C -D -f /tmp/$(date+%F)owl.sql owl By default, the postgres command-line utilities attempt to connect to 5432 (or $PGPORT or whatever is configured as the default port). In the meantime, you're still backing up the 7.3.4 postmaster with that script. -- Thomas F. O'Connell Sitening, LLC To confirm: what you're saying it that by specifying the port in my command the system knows which database to backup and which version of Postgresql to use. Thank you for the assistance. Margaret Gillon
Re: [GENERAL] logic/db question
On 8/2/06, bruce [EMAIL PROTECTED] wrote: hi... i have a tbl fooTBL name parentID ID so a name can have might have a parentID, as well as an ID. 'name's are associated with other 'name's via the parentID. in other words, if a name's parentID == a name's ID, name1 is the parent of name2. name parentIDID foo- 1 cat 1 2 dog 2 3 my question, how can i come up with a sql query that will list all the children (and children's children...) of a top level item? i had done this awhile ago.. but can't recall how i did it.. you can try tablefunc contrib module, or recursive pl/pgsql http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Join Question
On 8/2/06, Chris Hoover [EMAIL PROTECTED] wrote: Question, What is the difference between left join, and left outer join? I know the difference between inner and outer joins, but I was thinking that left join == inner join. But from what I am now seeing, it appears that PG is equating left join to left outer join. Is this correct? Types of JOIN: * [ INNER ] JOIN * LEFT [ OUTER ] JOIN * RIGHT [ OUTER ] JOIN * FULL [ OUTER ] JOIN * CROSS JOIN As usual, [ .. ] means that that word can be omitted. left join == inner join is absolutely incorrect, I'm afraid you need to refresh you memory and read the manual (http://www.postgresql.org/docs/8.1/interactive/sql-select.html, find join_type). This part of Postgres conforms to standard, all major DBMSs follow this semantics too. -- Best regards, Nikolay ---(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] Tape backup, 2 versions, same database name, which is pg_dump backing up
Your confirmation is correct with one slight technical exception: specifying the port doesn't technically cause the system to know which version of postgres to use.In my previous post, I forgot to mention a related note: the version of pg_dump that you're using matters slightly. pg_dump can typically be used with older postgres installations, but not newer ones. So you should prefer using the 8.1.4 version of pg_dump to dump at least your 8.1.4 postmaster, but probably also your 7.3.4 postmaster.A 7.3.4 pg_dump client will probably not be able to run against an 8.1.4 postmaster.Otherwise, pg_dump doesn't really care about the version as much as it cares about being able to connect to a postmaster. That's what specifying the port helps it do. When you don't specify the port, it just tries port 5432. If it finds a postmaster listening there, great, it will try to dump it; otherwise, it will fail with a complaint that it couldn't connect to anything on 5432. --Thomas F. O'ConnellSitening, LLChttp://www.sitening.com/3004B Poston AvenueNashville, TN 37203-1314615-469-5150 x802615-469-5151 (fax) On Aug 2, 2006, at 4:10 PM, [EMAIL PROTECTED] wrote:"Thomas F. O'Connell" [EMAIL PROTECTED] wrote on 08/02/2006 02:04:35 PM: You'll need to specify the non-default port explicitly in your pg_dump command in order to back up the postmaster running on 55432.E.g., pg_dump -p 55432 -U postgres -C -D -f /tmp/$(date+%F)owl.sql owlBy default, the postgres command-line utilities attempt to connect to 5432 (or $PGPORT or whatever is configured as the default port).In the meantime, you're still backing up the 7.3.4 postmaster with that script.-- Thomas F. O'Connell Sitening, LLC To confirm: what you're saying it that by specifying the port in my command the system knows which database to backup and which version of Postgresql to use. Thank you for the assistance. Margaret Gillon
Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump
Your confirmation is correct with one slight technical exception: specifying the port doesn't technically cause the system to know which version of postgres to use. In my previous post, I forgot to mention a related note: the version of pg_dump that you're using matters slightly. pg_dump can typically be used with older postgres installations, but not newer ones. So you should prefer using the 8.1.4 version of pg_dump to dump at least your 8.1.4 postmaster, but probably also your 7.3.4 postmaster. A 7.3.4 pg_dump client will probably not be able to run against an 8.1.4 postmaster. Otherwise, pg_dump doesn't really care about the version as much as it cares about being able to connect to a postmaster. That's what specifying the port helps it do. When you don't specify the port, it just tries port 5432. If it finds a postmaster listening there, great, it will try to dump it; otherwise, it will fail with a complaint that it couldn't connect to anything on 5432. -- Thomas F. O'Connell Sitening, LLC http://www.sitening.com/ 3004B Poston Avenue Nashville, TN 37203-1314 615-469-5150 x802 615-469-5151 (fax) You're correct, I cannot use the pg_dump. I get a error message that the pg_dump is aborting because of a version mismatch, then it says to use the i opt. How do I call the pg_dump from the 8.1.4 version? *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message.
Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump backing up
On Aug 2, 2006, at 4:27 PM, [EMAIL PROTECTED] wrote:You're correct, I cannot use the pg_dump. I get a error message that the pg_dump is aborting because of a version mismatch, then it says to use the i opt. How do I call the pg_dump from the 8.1.4 version?You'll probably need to specify an absolute path. Are both the client and server applications running on the same server? If so, you might need to get your consultant to tell you where the client applications for the 8.1.4 installation were actually installed. By default, postgres installs both client and server applications into /usr/local/pgsql, but at least one of your installations must be in a different location; otherwise, you probably couldn't have two versions of the postmaster running concurrently.--Thomas F. O'ConnellSitening, LLChttp://www.sitening.com/3004B Poston AvenueNashville, TN 37203-1314615-469-5150 x802615-469-5151 (fax)
Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump
It needed /bin in the path. Works fine this way. /usr/local/pgsql/bin/pg_dump -p 55432 -U postgres -C -D -f /tmp/$(date+%F)owl.sql owl Thank you again for your help. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message.
Re: [GENERAL] LISTEN considered dangerous
On 2 Aug 2006, Harald Fuchs wrote: all events that have happened after the snapshot that the transaction represents (the start of the transaction). Here you're contradicting yourself. In your second paragraph you state that LISTEN should get events unless later cancelled by a ROLLBACK. How should LISTEN know if its transaction will commit or abort? No, I'm not contradicting myself. What I'm saying is that if you listen for an event then that event should be delivered in the next transaction, if it happens after the start of the transaction that you listen in. The reason for this is that while you are in a transaction you see the world as it existed at the time the transaction was started. So if you decide that you want to be told about something then that wish has to be in effect from the time of the start of the transaction, because otherwise the state of the delivered events is not consistent with the state of the rest of the data. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] LISTEN considered dangerous
On Wed, 2 Aug 2006, Tom Lane wrote: Flemming Frandsen [EMAIL PROTECTED] writes: The listen should simply listen for events issued at the start of the transaction it's executed in. BEGIN; SELECT sleep(10); LISTEN foo; No, I don't think so. And why would that be a problem? There is no reason to assume that there would be any overhead in storing a list of outstanding events for your connection compared to today. What would happen in your example is that all the other connections leave this slow transaction behind, but in stead of storing all the events for all the transactions you can simply merge them all into one lump of events that are waiting to be delivered to that slow client. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LISTEN considered dangerous
On Wed, 2 Aug 2006, Roman Neuhauser wrote: I'm bothered by listen listening from the end of the transaction in stead of the start of the transaction. Sorry if this isn't what you're after, instead just a question: Why don't you issue the LISTEN in a separate transaction before going on? LISTEN foo; BEGIN; SELECT ... Well that's exactly what I do, but in stead of doing LISTEN foo I do 155x LISTEN foo before committing. It's not too bad as it only has to happen once pr. connection, but it creates a huge amount of churn in the pg_listeners table and there is an extra delay, simply because of the 155 roundtrips and the extra commit. I think I might put the listen stuff in a stored procedure to cut down on the roundtrips, but it's still not ideal. Many of my connections don't need the full 155 listens, so what I'd really like to be able to do is: select * from foo; listen foochanges; and actually get events if foo is updated compared to the state that the select read. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SELinux + CREATE TABLESPACE = ?
Folks, This may have come up before, but I didn't see a specific answer in the archives. When I try to do: CREATE TABLESPACE foo LOCATION '/path/to/foo/which/is/not/under/$PGDATA'; I get: ERROR: could not set permissions on directory /path/to/foo/which/is/not/under/$PGDATA Apparently this is a SELinux problem. How do I set the policy to allow for this, or if that's not possible, how do I disable SELinux? Thanks in advance :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELinux + CREATE TABLESPACE = ?
On Wed, Aug 02, 2006 at 04:47:36PM -0700, David Fetter wrote: Folks, This may have come up before, but I didn't see a specific answer in the archives. When I try to do: CREATE TABLESPACE foo LOCATION '/path/to/foo/which/is/not/under/$PGDATA'; I get: ERROR: could not set permissions on directory /path/to/foo/which/is/not/under/$PGDATA Apparently this is a SELinux problem. How do I set the policy to allow for this, or if that's not possible, how do I disable SELinux? Thanks in advance :) Pardon my self-followup for the archives :) Thanks to Talha Khan, who said: setenforce 1; will disable SELINUX Thanks also to Clodoaldo Pinto, who said: Apparently this is a SELinux problem. Confirm it looking for a message in /var/log/messages. How do I set the policy to allow for this, This Fedora FAQ is good: http://fedora.redhat.com/docs/selinux-faq-fc5/#faq-div-controlling-selinux or if that's not possible, how do I disable SELinux? edit /ect/selinux/config Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
