Re: [GENERAL] return two elements
Title: RE: [GENERAL] return two elements For my it would be sufficient that I could return a basic type in OUT/INOUT parameters, if in addition I could return a set fantastic! -Mensaje original- De: Alvaro Herrera [mailto:[EMAIL PROTECTED]] Enviado el: martes 7 de junio de 2005 19:22 Para: Michael Fuhr CC: Rodríguez Rodríguez,Pere; pgsql-general@postgresql.org Asunto: Re: [GENERAL] return two elements On Tue, Jun 07, 2005 at 07:45:03AM -0600, Michael Fuhr wrote: > On Tue, Jun 07, 2005 at 10:08:27AM +0200, "Rodríguez Rodríguez, Pere" wrote: > > > > I suppose that IN/OUT declaration also will use with procedural language > > (PL/pgSQL), it's correct? > > Yes; INOUT is also supported. Hmm, be aware that you can't return a set if you have OUT/INOUT parameters. Apparently this is something people coming from Oracle/SQL Server expect to be able to do. -- Alvaro Herrera () "Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat)
Re: [GENERAL] blocking INSERTs
On Wed, 2005-06-08 at 05:31, Joseph Shraibman wrote: > I want to do the following: > > BEGIN; > SELECT ... FROM table WHERE a = 1 FOR UPDATE; > UPDATE table SET ... WHERE a = 1; > if that resturns zero then > INSERT INTO table (...) VALUES (...); > END; > > The problem is that I need to avoid race conditions. Sometimes I get > primary key exceptions on the INSERT. > > I think I need to lock the table in share mode to keep inserts from > happening, but that blocks vacuums, and blocked vacuums block other > things behind them. So how do I get around this? > I think there's no way to avoid the race condition, I got to this conclusion while following past discussions (which were many of them, look for "insert or update" for example). There is though a solution starting with 8.0, namely you should place a save point before the inserts which are susceptible to fail, and roll back to that save point on failure. Then you can update while still keeping the transaction running. The strategy would be then "insert first, update if failed". HTH, Csaba. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Where to find translation of Postgres error messages?
Hi, I'd like to use a Postgres 8 server from different locales (english, german, hungarian, etc.). I can implement gettext into my client application so the only thing i'd need (at least I think so) is a .po (or an .mo) file for each locale. I looked into Postgres installation directories and found several .mo files under the locale directory. But I wasn't able to find the string "no password supplied" for example in neither of them. Where can I obtain a message translation or at least a gettext template? Are there translations available for the above mentioned languages? Many thanks, -- Csaba Együd -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres 8.0.1 configure failed
Sorry I forgot to set " LD_LIBRARY_PATH" , after setting able to install. LD_LIBRARY_PATH=/usr/local/lib export LD_LIBRARY_PATH Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Neil Conway Sent: Wednesday, June 08, 2005 11:42 AM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres 8.0.1 configure failed Dinesh Pandey wrote: > ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql > --with-tclconfig=/usr/local/lib --with-tcl > > configure: error: > *** Could not execute a simple test program. This may be a problem > *** related to locating shared libraries. Check the file 'config.log' > *** for the exact reason. Sounds like good advice to me -- what does config.log say? -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Backup Compatibility between minor versions.
Hi, Should it be possible to create a compressed backup of a version 8.0.1 database running on linux and restore that backup on version 8.0.3 running on XP? I ask this because it does not seem to work for me. Many problems seem to arise to do with tsearch2 extensions to tables, even though tsearch2 is installed on the XP machine. The backup was created using pgAdminIII. Howard. www.selestial.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Where to find translation of Postgres error messages?
On Jun 8, 2005, at 4:47 AM, Együd Csaba wrote: I'd like to use a Postgres 8 server from different locales (english, german, hungarian, etc.). I can implement gettext into my client application so the only thing i'd need (at least I think so) is a .po (or an .mo) file for each locale. I looked into Postgres installation directories and found several .mo files under the locale directory. But I wasn't able to find the string "no password supplied" for example in neither of them. Where can I obtain a message translation or at least a gettext template? Are there translations available for the above mentioned languages? Download the source and look in the "po" directory for the program you are interested in. Each language has a .po file. For the backend you want src/backend/po There are translations for German and Hungarian. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Copying data from int column to array column
Hi, I am trying to copy the data from an integer column into an array column in the same table. Something like this CREATE TABLE test (field1 INT, field2 INT, field3 INT[]); INSERT INTO test VALUES(1); INSERT INTO test VALUES(2); INSERT INTO test VALUES(3); INSERT INTO test VALUES(4); INSERT INTO test VALUES(5); UPDATE test SET field2 = field1; UPDATE test SET field3[1] = field1; Why does the UPDATE of field2 work, but the UPDATE of field3 does not? Thanks for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Copying data from int column to array column
On Jun 8, 2005, at 8:21 AM, Adam Witney wrote:
Hi,
I am trying to copy the data from an integer column into an array
column in
the same table. Something like this
CREATE TABLE test (field1 INT, field2 INT, field3 INT[]);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(5);
UPDATE test SET field2 = field1;
UPDATE test SET field3[1] = field1;
Why does the UPDATE of field2 work, but the UPDATE of field3 does not?
Adam,
I'm not sure what you were expecting, but I tried things here and they
seemed to do what I expected:
Sean
CREATE TABLE test (field1 INT, field2 INT, field3 INT[]);
INSERT INTO test (field1) VALUES(1);
INSERT INTO test (field1) VALUES(2);
INSERT INTO test (field1) VALUES(3);
INSERT INTO test (field1) VALUES(4);
INSERT INTO test (field1) VALUES(5);
SELECT * FROM test;
UPDATE test SET field2 = field1;
SELECT * FROM test;
UPDATE test set field3[1] = field2;
SELECT * FROM test;
UPDATE test SET field3 = array((select field1 from test));
SELECT * FROM test;
- OUTPUT
CREATE TABLE test (field1 INT, field2 INT, field3 INT[]);
CREATE TABLE
INSERT INTO test (field1) VALUES(1);
INSERT 147690348 1
INSERT INTO test (field1) VALUES(2);
INSERT 147690350 1
INSERT INTO test (field1) VALUES(3);
INSERT 147690352 1
INSERT INTO test (field1) VALUES(4);
INSERT 147690353 1
INSERT INTO test (field1) VALUES(5);
INSERT 147690355 1
SELECT * FROM test;
field1 | field2 | field3
++
1 ||
2 ||
3 ||
4 ||
5 ||
(5 rows)
UPDATE test SET field2 = field1;
UPDATE 5
SELECT * FROM test;
field1 | field2 | field3
++
1 | 1 |
2 | 2 |
3 | 3 |
4 | 4 |
5 | 5 |
(5 rows)
UPDATE test set field3[1] = field2;
UPDATE 5
SELECT * FROM test;
field1 | field2 | field3
++
1 | 1 | {1}
2 | 2 | {2}
3 | 3 | {3}
4 | 4 | {4}
5 | 5 | {5}
(5 rows)
UPDATE test SET field3 = array((select field1 from test));
UPDATE 5
SELECT * FROM test;
field1 | field2 | field3
++-
1 | 1 | {1,2,3,4,5}
2 | 2 | {1,2,3,4,5}
3 | 3 | {1,2,3,4,5}
4 | 4 | {1,2,3,4,5}
5 | 5 | {1,2,3,4,5}
(5 rows)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Backup Compatibility between minor versions.
To improve compatibility, I created a backup on the linux system (8.0.1) using plain format, data only with triggers disabled. However, when I try to restore the plain sql on the windows machine (8.0.3) it fails because the first insert command fails a foreign key constraint. Can I disable foreign key checks for the duration of the restore? Howard www.selestial.com Howard Cole wrote: Hi, Should it be possible to create a compressed backup of a version 8.0.1 database running on linux and restore that backup on version 8.0.3 running on XP? I ask this because it does not seem to work for me. Many problems seem to arise to do with tsearch2 extensions to tables, even though tsearch2 is installed on the XP machine. The backup was created using pgAdminIII. Howard. www.selestial.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] vulnerability/SSL
Hi, I¡¯m using postgreSQL with SSL these days. The version I¡¯m using is 8.0.3. I found that it¡¯s impossible to use an encrypted key file. When you use a protected server.key file, you will be prompted to input your passphrase EVERYTIME IT¡¯S USED, not only when you start the server but also when a client makes a connection. So you have to leave the key file un-protected. I think it¡¯s a serious vulnerability since the security relies on the secrecy of the private key. Without encryption, the only thing we can use to protect the private key is the access control mechanism provided by the OS. Any comments on this issue? cheers, Changyu __ Discover Yahoo! Have fun online with music videos, cool games, IM and more. Check it out! http://discover.yahoo.com/online.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] To SPAM or not to SPAM...
I post to this list occasionally, and yet get maybe one spam email a month. YMMV. Don't tell anyone, but I got a line on a great deal in Nigeria. Rick [EMAIL PROTECTED] wrote on 06/07/2005 09:56:16 PM: > Wes wrote: > > It seems that for some time this list has been mirrored to Usenet without > > changing the originating email addresses. Since Usenet is spammers Nirvana, > > that one little act results in everyone that posts to the postgres lists > > becoming SPAM fodder. > > > > Can something not be changed in the mailinglist-Usnet gateway such that the > > originating email addresses are either removed or scrambled so that posting > > to the mailing list doesn't result in your email address being plastered all > > over Usenet? People that intentionally post to Usenet generally don't use a > > replyable email address. > > It's not going to save you anything. If you post to a list such as > this, there are archives of the posts that the spammers can get at just > as easily as Usenet. > > -- > Until later, Geoffrey > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Copying data from int column to array column
On Wed, Jun 08, 2005 at 01:21:19PM +0100, Adam Witney wrote:
>
> UPDATE test SET field3[1] = field1;
>
> Why does the UPDATE of field2 work, but the UPDATE of field3 does not?
What version of PostgreSQL are you using? The example should work
in 8.x. See the Release Notes:
http://www.postgresql.org/docs/8.0/static/release-8-0.html
"Updating an element or slice of a NULL array value now produces a
non-NULL array result, namely an array containing just the assigned-to
positions."
In previous versions you can get around the problem by first setting
the column to an empty array:
UPDATE test SET field3 = '{}' WHERE field3 IS NULL;
UPDATE test SET field3[1] = field1;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Where to find translation of Postgres error messages?
Hi John, thank you very much for your suggestion. I downloaded the file (hu.po) and tried to find the given error message in it (using a text editor) with no success. There was no e.g. "SQL Error: fe_sendauth: no password supplied" lines in the po file. A also searched over the de.po and no such lines. I also tried to search for portions of the message. No result. :( What should I do? Regards, -- Csaba Együd -Original Message- From: John DeSoi [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 1:45 PM To: Együd Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Where to find translation of Postgres error messages? On Jun 8, 2005, at 4:47 AM, Együd Csaba wrote: > I'd like to use a Postgres 8 server from different locales (english, > german, hungarian, etc.). I can implement gettext into my client > application so the only thing i'd need (at least I think so) is a .po > (or an .mo) file for each locale. > > I looked into Postgres installation directories and found several .mo > files under the locale directory. But I wasn't able to find the string > "no password supplied" for example in neither of them. Where can I > obtain a message translation or at least a gettext template? Are there > translations available for the above mentioned languages? > Download the source and look in the "po" directory for the program you are interested in. Each language has a .po file. For the backend you want src/backend/po There are translations for German and Hungarian. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Where to find translation of Postgres error messages?
On Jun 8, 2005, at 9:12 AM, Együd Csaba wrote: thank you very much for your suggestion. I downloaded the file (hu.po) and tried to find the given error message in it (using a text editor) with no success. There was no e.g. "SQL Error: fe_sendauth: no password supplied" lines in the po file. A also searched over the de.po and no such lines. I also tried to search for portions of the message. No result. :( Apparently that string is not localized. It is defined in libpq-fe.h: /* Define the string so all uses are consistent. */ #define PQnoPasswordSupplied"fe_sendauth: no password supplied\n" John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Backup Compatibility between minor versions.
There appears to be a problem with pgadminIII where the option to disable triggers does not actually do anything! If I switch to the pg_dump command line then the disable triggers works. Unfortunately I still cannot restore a database backed up from a linux machine running 8.0.1 to an windows machine running 8.0.3. My problem seems to be: 1. I use a archive backup, which contains tsearch2 tables causes conflict with existing tsearch2 tables created from template1. or... 2. I use plain backup and restore to a blank database created from template0 - and the restore fails because it can't find tsearch2 types. My head hurts. I shall try to manually hack the plain backup file to remove references to the tsearch2 tables. Will all of these problems disappear if I upgrade the linux machine to 8.0.3? Howard. www.selestial.com Howard Cole wrote: To improve compatibility, I created a backup on the linux system (8.0.1) using plain format, data only with triggers disabled. However, when I try to restore the plain sql on the windows machine (8.0.3) it fails because the first insert command fails a foreign key constraint. Can I disable foreign key checks for the duration of the restore? Howard www.selestial.com Howard Cole wrote: Hi, Should it be possible to create a compressed backup of a version 8.0.1 database running on linux and restore that backup on version 8.0.3 running on XP? I ask this because it does not seem to work for me. Many problems seem to arise to do with tsearch2 extensions to tables, even though tsearch2 is installed on the XP machine. The backup was created using pgAdminIII. Howard. www.selestial.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] vulnerability/SSL
> Hi, > I¡¯m using postgreSQL with SSL these days. The version I¡¯m > using is 8.0.3. I found that it¡¯s impossible to use an > encrypted key file. > When you use a protected server.key file, you will be > prompted to input your passphrase EVERYTIME IT¡¯S USED, not > only when you start the server but also when a client makes a > connection. So you have to leave the key file un-protected. I > think it¡¯s a serious vulnerability since the security relies > on the secrecy of the private key. Without encryption, the > only thing we can use to protect the private key is the > access control mechanism provided by the OS. > Any comments on this issue? If you don't trust the access control provided by the OS, why are you putting sensitive data on it? If one can break your access control in the OS they can read all your data anyway - they don't even need to sniff the wire and decrypt it using the key. Or they can just change the passwords of your users and connect - or *change* they key. If you're still concerned, it should be possible to mount is using a crypto loopback device which wuold require a password to get it in there (note! haven't tried it, but it should work). That would save you against someone rebooting your server on a separate OS and try to read it, but not against someone cracking the system while it's running. But again, your data would be just as vulnerable, so I don't really see the gain. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] return two elements
On Wed, Jun 08, 2005 at 01:28:56AM -0400, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Hmm, be aware that you can't return a set if you have OUT/INOUT > > parameters. > > ? News to me --- what are you worried about exactly? > > It's surely possible that our idea of what this means is different > from Oracle's, but we ought to take a close look before the semantics > get set in stone by a release ... I see the following in the development documentation -- are the semantics still under discussion? Should this thread be moved to pgsql-hackers? "If you declared the function with output parameters, write just RETURN NEXT with no expression. The current values of the output parameter variable(s) will be saved for eventual return. Note that you must declare the function as returning SETOF record when there are multiple output parameters, or SETOF sometype when there is just one output parameter of type sometype, in order to create a set-returning function with output parameters." http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING The following example works in HEAD: CREATE FUNCTION foo(INOUT y integer, OUT z integer) RETURNS SETOF record AS $$ BEGIN y := y + 1; z := y + 2; RETURN NEXT; y := y + 1; z := z + 3; RETURN NEXT; y := y + 1; z := z + 4; RETURN NEXT; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1); y | z ---+ 2 | 4 3 | 7 4 | 11 (3 rows) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Backup Compatibility between minor versions.
Howard Cole <[EMAIL PROTECTED]> writes: > To improve compatibility, I created a backup on the linux system > (8.0.1) using plain format, data only with triggers disabled. However, > when I try to restore the plain sql on the windows machine (8.0.3) it > fails because the first insert command fails a foreign key > constraint. Can I disable foreign key checks for the duration of the > restore? I would suggest upgrading to 8.0.3 on the linux machine, just to make sure you get all pg_dump bugfixes, then retrying the dump/restore. AFAIK there's no reason it shouldn't work. If you still have trouble, post the exact dump and restore commands you're using and the exact error message you get. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
FW: [GENERAL] Where to find translation of Postgres error messages?
-Original Message- From: Együd Csaba [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 4:31 PM To: 'Tom Lane' Subject: RE: [GENERAL] Where to find translation of Postgres error messages? Ah, I see. OK. So there are two ways of mine: 1. to translate myself the whole stuff in my application :-/, or 2. leaving the back end messaeges in english. At last in most cases the server messages hold information only for the developers. I think I'll do the second. Many thanks. -- Csaba Együd -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 3:52 PM To: Együd Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Where to find translation of Postgres error messages? =?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes: > I looked into Postgres installation directories and found several .mo > files under the locale directory. But I wasn't able to find the string > "no password supplied" for example in neither of them. I think we deliberately don't localize that one because it'd break various application-level code that relies on being able to recognize that string. (Which is pretty yucky, of course, but until someone gets around to implementing SQLSTATE error codes for libpq's internal error reports, there's not a lot of choice.) regards, tom lane -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 2005.06.07. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] To SPAM or not to SPAM...
--- [EMAIL PROTECTED] wrote: > I post to this list occasionally, and yet get maybe one spam email a > month. > YMMV. Don't tell anyone, but I got a line on a great deal in > Nigeria. > > Rick > I posted on this list with this yahoo.com address and now receive upwards of 30-40 spam emails per day. I was more than slightly irritated to see that whoever operates the list<->usenet gateway doesn't bother to obfuscate our email addresses. Fortunately, yahoo's spam filter correctly categorizes 99% of the incoming spam properly and places it in the bulk mail folder but this account used to only receive maybe 5 spam emails a month. Regards, Shelby Cain __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Where to find translation of Postgres error messages?
=?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes: > I looked into Postgres installation directories and found several .mo files > under the locale directory. But I wasn't able to find the string "no > password supplied" for example in neither of them. I think we deliberately don't localize that one because it'd break various application-level code that relies on being able to recognize that string. (Which is pretty yucky, of course, but until someone gets around to implementing SQLSTATE error codes for libpq's internal error reports, there's not a lot of choice.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Optimizer and inherited tables
(Sorry, wrong subject line got sent) Greetings! Does anybody know how well the optimizer works when dealing with inherited tables? I am currently using 8.0.1. I have a table called eventlog.record_main, and a number of inherited tables to partition the data (called eventlog_partition.___record_main). is the primary key (all tables have this indexed via the primary key). The reason for doing this is that a single table would be way too big (there are on average 6-7 million rows per table) so that vacuum and deletes would be inefficient. Inserting has been much more efficient using this mechanism. When I try the following query, I get sequential scans: explain select * from eventlog.record_main order by luid limit 5; QUERY PLAN -- Limit (cost=160800332.75..160800332.77 rows=5 width=92) -> Sort (cost=160800332.75..161874465.60 rows=429653138 width=92) Sort Key: eventlog.record_main.luid -> Result (cost=0.00..11138614.37 rows=429653138 width=92) -> Append (cost=0.00..11138614.37 rows=429653138 width=92) -> Seq Scan on record_main (cost=0.00..0.00 rows=1 width=92) -> Seq Scan on _20050401__record_main record_main (cost=0.00..223029.64 rows=8620164 width=92) -> Seq Scan on _20050402__record_main record_main (cost=0.00..201536.46 rows=7789446 width=92) -> Seq Scan on _20050403__record_main record_main (cost=0.00..211277.34 rows=8165934 width=92) -> Seq Scan on _20050404__record_main record_main (cost=0.00..219381.70 rows=8479170 width=92) -> Seq Scan on _20050405__record_main record_main (cost=0.00..226305.94 rows=8746794 width=92) (and so on) Yet, when I run the query on a single table, I get index usage: explain select * from eventlog_partition._20050601__record_main order by luid limit 5; QUERY PLAN Limit (cost=0.00..0.15 rows=5 width=92) -> Index Scan using _20050601__record_main_pkey on _20050601__record_main (cost=0.00..163375.23 rows=5460021 width=92) (2 rows) This means that any query that limits the rows will run extremely inefficiently. Given a limit of 5, at most only 5 rows need to be considered in each partition sub-table, so an optimal plan would run a sub-query in each table limited to 5 rows, and then merge the results. Any ideas/fixes/patches? Regards! Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] vulnerability/SSL (fwd)
Greetings! Does anybody know how well the optimizer works when dealing with inherited tables? I am currently using 8.0.1. I have a table called eventlog.record_main, and a number of inherited tables to partition the data (called eventlog_partition.___record_main). is the primary key (all tables have this indexed via the primary key). The reason for doing this is that a single table would be way too big (there are on average 6-7 million rows per table) so that vacuum and deletes would be inefficient. Inserting has been much more efficient using this mechanism. When I try the following query, I get sequential scans: explain select * from eventlog.record_main order by luid limit 5; QUERY PLAN -- Limit (cost=160800332.75..160800332.77 rows=5 width=92) -> Sort (cost=160800332.75..161874465.60 rows=429653138 width=92) Sort Key: eventlog.record_main.luid -> Result (cost=0.00..11138614.37 rows=429653138 width=92) -> Append (cost=0.00..11138614.37 rows=429653138 width=92) -> Seq Scan on record_main (cost=0.00..0.00 rows=1 width=92) -> Seq Scan on _20050401__record_main record_main (cost=0.00..223029.64 rows=8620164 width=92) -> Seq Scan on _20050402__record_main record_main (cost=0.00..201536.46 rows=7789446 width=92) -> Seq Scan on _20050403__record_main record_main (cost=0.00..211277.34 rows=8165934 width=92) -> Seq Scan on _20050404__record_main record_main (cost=0.00..219381.70 rows=8479170 width=92) -> Seq Scan on _20050405__record_main record_main (cost=0.00..226305.94 rows=8746794 width=92) (and so on) Yet, when I run the query on a single table, I get index usage: explain select * from eventlog_partition._20050601__record_main order by luid limit 5; QUERY PLAN Limit (cost=0.00..0.15 rows=5 width=92) -> Index Scan using _20050601__record_main_pkey on _20050601__record_main (cost=0.00..163375.23 rows=5460021 width=92) (2 rows) This means that any query that limits the rows will run extremely inefficiently. Given a limit of 5, at most only 5 rows need to be considered in each partition sub-table, so an optimal plan would run a sub-query in each table limited to 5 rows, and then merge the results. Any ideas/fixes/patches? Regards! Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] vulnerability/SSL
On Wed, 2005-06-08 at 16:08 +0200, Magnus Hagander wrote: > > Hi, > > I¡¯m using postgreSQL with SSL these days. The version I¡¯m > > using is 8.0.3. I found that it¡¯s impossible to use an > > encrypted key file. > > When you use a protected server.key file, you will be > > prompted to input your passphrase EVERYTIME IT¡¯S USED, not > > only when you start the server but also when a client makes a > > connection. So you have to leave the key file un-protected. I > > think it¡¯s a serious vulnerability since the security relies > > on the secrecy of the private key. Without encryption, the > > only thing we can use to protect the private key is the > > access control mechanism provided by the OS. > > Any comments on this issue? > > If you don't trust the access control provided by the OS, why are you putting > sensitive data on it? > If one can break your access control in the OS they can read all your data > anyway - they don't even need to sniff the wire and decrypt it using the key. > Or they can just change the passwords of your users and connect - or *change* > they key. Yes and no. They can't change the key. It's tied to the certificate, which is signed. They need to get a signed certificate from a trusted CA, and put the associated private key on your server after they cracked it. Which is much like leaving a big banner with "Yes, it was me!" signed by you on the crime scene. :-) But overall I agree. If they gained enough privilege to read the key file, it's possible they're able to access the data as well. They might be able to patch the server and have the password that protects the key logged somewhere next time you type it in. OTOH, I see no advantage in reading the key at connection time instead of startup time (like every other daemon does). Encrypted key has an interesting significance with backups. Someone may be able to steal one backup of yours. They'll get old data (maybe you don't care much about that), _and_ the key. You don't want them to be able to sign stuff or impersonate your servers with it. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Bug with view definition?
Hello all, why is the last definition of a view not working, although the documentation says all three are equal? Testcase: CREATE SCHEMA one; CREATE SCHEMA two; CREATE TABLE one.one ( id SERIAL PRIMARY KEY ); CREATE TABLE two.two ( id SERIAL PRIMARY KEY ); CREATE TABLE join1 ( id SERIAL PRIMARY KEY ); CREATE OR REPLACE VIEW working AS SELECT one.* FROM one.one JOIN two.two ON TRUE JOIN join1 ON join1.id = one.id; CREATE OR REPLACE VIEW also_working AS SELECT one.* FROM one.one CROSS JOIN two.two JOIN join1 ON join1.id = one.id; CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; Thanks in advance Sebastian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Foreign keys and slow insert
I read in documentation that primary key doesn't require additional indexes but I could find nothing about foreign keys.
Do I need to create additional indexes when I create foreign keys?
Example:
create table master
{
master_id INT4,
master_name VARCHAR(64),
CONSTRAINT master_pkey PRIMARY KEY (master_id)
}
create table slave
{
slave_id INT4,
slave_name VARCHAR(64),
master_id INT4,
CONSTRAINT slave_pkey PRIMARY KEY (slave_id),
CONSTRAINT slave_fkey_master_id FOREIGN KEY (master_id)
REFERENCES master (master_id) ON UPDATE CASCADE ON DELETE RESTRICT
}
Do I need to create index
CREATE INDEX my_index
ON slave
USING btree
(master_id);
?
Thanks
-- Verba volent, scripta manentMy ISP - http://www.netbynet.ru
[GENERAL] deadlocks in multiple-triggers environment
hi i have a stituation a situation where i have multiple tables, and multiple triggers on all of them. at least 1 or 2 triggers on at lease 4 different tables does updates to main cache table. now. i have tasks which involve simultaneously (from different machines even) modifying all of the "source" tables. and i get some deadlocks. what is the best way to fight deadlocks? how to find exactly what happened deadlock - which command, which trigger, which function? how to avoid them (deadlocks). i can't lock all tables for update, because they happen constantly. any clues? pointers? urls? depesz
Re: [GENERAL] Bug with view definition?
Sebastian Böck wrote: Hello all, why is the last definition of a view not working, although the documentation says all three are equal? CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; I think it's trying to join "two" to "join1" - try ...FROM two.two, one.one JOIN join1... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] return two elements
I dropped [EMAIL PROTECTED] from the Cc: because that account has serious issues. On Wed, Jun 08, 2005 at 08:16:32AM -0600, Michael Fuhr wrote: > On Wed, Jun 08, 2005 at 01:28:56AM -0400, Tom Lane wrote: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > Hmm, be aware that you can't return a set if you have OUT/INOUT > > > parameters. > > > > ? News to me --- what are you worried about exactly? > > > > It's surely possible that our idea of what this means is different > > from Oracle's, but we ought to take a close look before the semantics > > get set in stone by a release ... My point is that Oracle and others, you can have an OUT parameter to return, say a number, and additionally a set like those returned with RETURN NEXT. And both things are independent. > The following example works in HEAD: > > CREATE FUNCTION foo(INOUT y integer, OUT z integer) RETURNS SETOF record AS $$ > BEGIN > y := y + 1; z := y + 2; RETURN NEXT; > y := y + 1; z := z + 3; RETURN NEXT; > y := y + 1; z := z + 4; RETURN NEXT; > END; > $$ LANGUAGE plpgsql; > > SELECT * FROM foo(1); > y | z > ---+ > 2 | 4 > 3 | 7 > 4 | 11 > (3 rows) Yeah, but if you do that, you can't use the OUT parameter separately. My point is that something like this doesn't work: CREATE FUNCTION foo (OUT y) RETURNS SETOF int LANGUAGE plpgsql AS $$ DECLARE z INT; BEGIN y := 4; FOR z IN 1 .. 3 LOOP RETURN NEXT z; END LOOP; END; $$ Now, this approach has a problem, and it's where do you save the value of y? We have no "host variables." This is exactly the reason Tom punted and made it return OUT/INOUT params in the result set, at the same time prohibiting it from receiving further output. -- Alvaro Herrera () "Llegará una época en la que una investigación diligente y prolongada sacará a la luz cosas que hoy están ocultas" (Séneca, siglo I) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Bug with view definition?
Richard Huxton wrote: Sebastian Böck wrote: Hello all, why is the last definition of a view not working, although the documentation says all three are equal? CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; I think it's trying to join "two" to "join1" - try ...FROM two.two, one.one JOIN join1... Sure, but the problem still exists if you want to join with table one and table two. Forgot to say that this also applies for normal selects (of course!). Sebastian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Bug with view definition?
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: > why is the last definition of a view not working, although the > documentation says all three are equal? The documentation says no such thing... > CREATE OR REPLACE VIEW not_working AS > SELECT one.* > FROM one.one, two.two > JOIN join1 ON join1.id = one.id; JOIN binds tighter than comma in FROM-lists, so that means FROM one.one CROSS JOIN (two.two JOIN join1 ON join1.id = one.id); which of course is illegal because the JOIN/ON condition refers to something that's not within the current JOIN. Your preceding example parenthesizes as FROM (one.one CROSS JOIN two.two) JOIN join1 ON join1.id = one.id; which is OK. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Bug with view definition?
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: why is the last definition of a view not working, although the documentation says all three are equal? The documentation says no such thing... So I misinterpreted the following: http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html that says: FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2. It is also equivalent to FROM T1 INNER JOIN T2 ON TRUE CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; JOIN binds tighter than comma in FROM-lists, so that means FROM one.one CROSS JOIN (two.two JOIN join1 ON join1.id = one.id); which of course is illegal because the JOIN/ON condition refers to something that's not within the current JOIN. Your preceding example parenthesizes as FROM (one.one CROSS JOIN two.two) JOIN join1 ON join1.id = one.id; which is OK. Thanks for clarification Sebastian ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Foreign keys and slow insert
On Wed, 8 Jun 2005, Dan Black wrote:
> I read in documentation that primary key doesn't require additional indexes
> but I could find nothing about foreign keys.
> Do I need to create additional indexes when I create foreign keys?
> Example:
> create table master
> {
> master_id INT4,
> master_name VARCHAR(64),
> CONSTRAINT master_pkey PRIMARY KEY (master_id)
> }
> create table slave
> {
> slave_id INT4,
> slave_name VARCHAR(64),
> master_id INT4,
> CONSTRAINT slave_pkey PRIMARY KEY (slave_id),
> CONSTRAINT slave_fkey_master_id FOREIGN KEY (master_id) REFERENCES master
> (master_id) ON UPDATE CASCADE ON DELETE RESTRICT
> }
>
> Do I need to create index
>
> CREATE INDEX my_index
> ON slave
> USING btree
> (master_id);
>
> ?
Generally you want to do so to speed up lookups when master changes.
However, if master is basically write once, almost never update or delete,
then you may not need one.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Bug with view definition?
Sebastian Böck wrote: Richard Huxton wrote: Sebastian Böck wrote: Hello all, why is the last definition of a view not working, although the documentation says all three are equal? CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; I think it's trying to join "two" to "join1" - try ...FROM two.two, one.one JOIN join1... Sure, but the problem still exists if you want to join with table one and table two. Sorry - hadn't read the initial post carefully enough, and didn't see the unconstrained join on one,two. Since "JOIN" has a high precedence you'll want to force the issue with a subselect: SELECT * FROM ( SELECT one.* FROM one.one, two.two ) AS dummy JOIN join1 ON join1.id = dummy.id -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign keys and slow insert
Dan Black wrote: I read in documentation that primary key doesn't require additional indexes but I could find nothing about foreign keys. Do I need to create additional indexes when I create foreign keys? Example: create table master create table slave Do I need to create index CREATE INDEX my_index ON slave USING btree (master_id); Yes. The primary key uses a "UNIQUE INDEX" to enforce uniqueness, so you get the index for "free". The foreign-key has no such constraint of course. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Copying data from int column to array column
Adam Witney <[EMAIL PROTECTED]> writes: > UPDATE test SET field2 = field1; > UPDATE test SET field3[1] = field1; > Why does the UPDATE of field2 work, but the UPDATE of field3 does not? Works for me in 8.0 ;-). Before 8.0, if you tried to assign to just one value of an array that was initially NULL, the array stayed NULL. (That behavior made sense from the point of view of the implementation, which sees array element assignment as a binary operation taking an array and a new element value ... but it wasn't especially useful.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] vulnerability/SSL
Hi, A possible countermeasure on Windows platform, inspired by Magnus.Thanks ;) First we remove the passphrase from the key file, making it plain. Windows provides a feature "encrypted file system", provide transparent encryption/decryption. We can log on using the account we run Postgres with and encrypt the plaintext key file. Then we logon using another non-amin account, and start postgres using "runas" service. Therefore the file is encrypted, only the Postgres acount and the recovery agent(built-in administrator by default) can read/modify it. The file will remain encrypted when restored from backup. I've tested it on my computer and it works. cheers, Changyu --- dong changyu <[EMAIL PROTECTED]> wrote: > Hi, > I¡¯m using postgreSQL with SSL these days. The > version > I¡¯m using is 8.0.3. I found that it¡¯s impossible > to > use an encrypted key file. > When you use a protected server.key file, you will > be > prompted to input your passphrase EVERYTIME IT¡¯S > USED, not only when you start the server but also > when > a client makes a connection. So you have to leave > the > key file un-protected. I think it¡¯s a serious > vulnerability since the security relies on the > secrecy > of the private key. Without encryption, the only > thing > we can use to protect the private key is the access > control mechanism provided by the OS. > Any comments on this issue? > > cheers, > Changyu > > > > > __ > Discover Yahoo! > Have fun online with music videos, cool games, IM > and more. Check it out! > http://discover.yahoo.com/online.html > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > __ Discover Yahoo! Find restaurants, movies, travel and more fun for the weekend. Check it out! http://discover.yahoo.com/weekend.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Two updates problem
I have table: CREATE TABLE "public"."test" ( "id" INTEGER, "text1" VARCHAR(25), "text2" VARCHAR(25) ) WITH OIDS; INSERT INTO test VALUES (1, 'qwerty', '111'); INSERT INTO test VALUES (2, 'asdfgh', '222'); and function: CREATE OR REPLACE FUNCTION "public"."test1" () RETURNS integer AS $body$ BEGIN UPDATE test SET text1='qqq' WHERE id = 2; RETURN 2; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; When I do SELECT test1(); it works fine and return "2". But this thing don't work: UPDATE test SET text2='test' WHERE id = (SELECT test1()); (rows affected: 0) Why? There is two updates on the same row, but work only first update (in the function). Maybe it's bug? P.S.: I'm using PostgreSQL 8.0.1 on Win32. Sorry for my english. ___ Best regards, Yuri B. Lukyanov 7 июня 2005 г. 22:49:28 mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and slow insert
I've observed that inserts into slave table became slower when I use foreign key than without one. Can it be related to foreign key? And I am interested how much performance of database with foreign keys can be different from performance of database without foreign keys? In other words, how much performance decrease on using foreign keys? Thanks :-)-- Verba volent, scripta manentMy ISP - http://www.netbynet.ru
[GENERAL] So maybe SQLERRM? Sb knows how to check it?
Maybe I ask another way. In Oracle there is a variable SQLERRM, where error message is placed. How to get some message in PostgreSQL? I searched documentation 3 or 5 times, and Google, and wrote on forums topics and I have enough. -- Startuj z INTERIA.PL! >>> http://link.interia.pl/f186c ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Foreign keys and slow insert
On Wed, 2005-06-08 at 12:39, Dan Black wrote: > I've observed that inserts into slave table became slower when I use > foreign key than without one. > Can it be related to foreign key? > And I am interested how much performance of database with foreign > keys can be different from performance of database without foreign > keys? In other words, how much performance decrease on using foreign > keys? The problem you're seeing is usually caused by adding records to a table set that starts out empty, and the planner uses seq scans, and as it grows, should switch to random seeks, but doesn't know to, because no one has bothered to analyze said tables. Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so often to help that. On the other hand, foreign keys are never zero cost, so even the most efficient implementation is gonna be slower than not using them. Data coherency costs, either up front (i.e. in the database doing it) or in the back (i.e. hiring 20 summer interns to go through your data and find the parts that are bad...) :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] WinXP installation
I need some help please! I'm using PostgreSQL for a long time (about two yeas), but always in a Linux box. So, I always could install and use it with no major problems! Unfortunetly now I have to use it on WinXP. So, here is my problem: I intalled PostgreSQL 8.0.3 in WInXP (SP1) with administrator rights, as a service and had confiured the user postgres for the system and a user postgres for the database. So far, so good! I known that I connot start the service with administrator rights! But when I try to start the service with another user it give me this error: 2005-06-08 15:14:07 NOTICE: Unknown win32 socket error code: 10106 2005-06-08 15:14:07 LOG: could not create IPv4 socket: Invalid argument 2005-06-08 15:14:07 WARNING: could not create listen socket for "localhost" 2005-06-08 15:14:07 FATAL: no socket created for listening Please I really need some help! Any ideas? Thanks!! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] So maybe SQLERRM? Sb knows how to check it?
On Wed, Jun 08, 2005 at 08:03:40PM +0200, Gorodowienko Daniel wrote: > Maybe I ask another way. > In Oracle there is a variable SQLERRM, where error message is placed. > How to get some message in PostgreSQL? > I searched documentation 3 or 5 times, and Google, and wrote on forums > topics and I have enough. No, we don't have SQLERRM support yet. If you were asking about getting the messages from RAISE EXCEPTION, I'm afraid there's no way to get it in the EXCEPTION clause. If you want to contribute it, patches are welcome ... -- Alvaro Herrera () "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Foreign keys and slow insert
I think 21 interns will be enough :)2005/6/8, Scott Marlowe <[EMAIL PROTECTED]>: On Wed, 2005-06-08 at 12:39, Dan Black wrote:> I've observed that inserts into slave table became slower when I use> foreign key than without one.> Can it be related to foreign key?> And I am interested how much performance of database with foreign > keys can be different from performance of database without foreign> keys? In other words, how much performance decrease on using foreign> keys?The problem you're seeing is usually caused by adding records to a table set that starts out empty, and the planner uses seq scans, and as itgrows, should switch to random seeks, but doesn't know to, because noone has bothered to analyze said tables.Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so often to help that.On the other hand, foreign keys are never zero cost, so even the mostefficient implementation is gonna be slower than not using them. Datacoherency costs, either up front (i.e. in the database doing it) or in the back (i.e. hiring 20 summer interns to go through your data and findthe parts that are bad...) :)-- Verba volent, scripta manentMy ISP - http://www.netbynet.ru
Re: [GENERAL] IMPORTANT NOTIFICATION
> you will have to confirm your account by the following link Is this bogus? Clicking on the link goes to 62.193.220.183 which is not postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] IMPORTANT NOTIFICATION
Hi On Wed, 8 Jun 2005, Matt Miller wrote: you will have to confirm your account by the following link Is this bogus? Yes... -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] IMPORTANT NOTIFICATION
Matt Miller wrote: you will have to confirm your account by the following link Is this bogus? Clicking on the link goes to 62.193.220.183 which is not postgresql.org Quite. It is a phising scam probably. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] IMPORTANT NOTIFICATION
Hi, Phishing scam for sure. I thought it was hilarious in a way: "Why would the host of postgresql.org send himself a bill?". Kind of a silly paradox. Regards, Arthur On 6/8/05, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Matt Miller wrote: > >>you will have to confirm your account by the following link > > > > > > Is this bogus? > > > > Clicking on the link goes to 62.193.220.183 which is not postgresql.org > > Quite. It is a phising scam probably. > > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faq > > > -- > Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 > PostgreSQL Replication, Consulting, Custom Programming, 24x7 support > Managed Services, Shared and Dedicated Hosting > Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] postgres and ggobi/xgvis
Hi All, How easy or difficult is it to get ggobi/xgvis working with postgresql?! Is it possible to write a query and send the output straight to ggobi/xgvis without much work? Any pointers. Thanks for your help. Hrishi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] IMPORTANT NOTIFICATION
Matt Miller wrote: you will have to confirm your account by the following link Is this bogus? Yes, if you're look at the email as text rather then html, you'll see it's a phishing attempt. A very good reason to read your email in text. Clicking on the link goes to 62.193.220.183 which is not postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Until later, Geoffrey ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Deleting orphaned records to establish Ref Integrity
>> Another idea is to try an outer join: >> SELECT child_table.parentid INTO tmp_table >> FROM child_table LEFT JOIN parent_table >> ON (child_table.parentid = parent_table.parentid) >> WHERE parent_table.parentid IS NULL; > >There's also >DELETE >FROM child_table >WHERE NOT EXISTS (select 1 >from parent_table >where parent_id = child_table.parent_id >) > > Which won't use anything as efficient as a hash join or merge join but will beat > least capable of using index lookups for something basically equivalent toa > nested loop. Sorry for the delay, I only get to work on this system every now and then. I tried Greg's suggestion and it worked out great. The estimates from EXPLAIN were much larger than the actuals (e.g. 41 hours vs. 2 hours), so I probably have some tuning to do with this dataset. I decided not to try Tom's temp table method because I was afraid the generated table would be very large, so the subsequent DELETE .. WHERE NOT IN (...) would cause swapping again. Thanks all for your help, your insights saved me a lot of headache. Roman _ Consolidate your email! http://www.fusemail.com _ Check All Email Accounts Anywhere! Check your POP3 and webmail account from any PC. With no ads http://www.fusemail.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Possible to ignore transactions > n?
Is is possible to tell PostgreSQL to ignore transactions committed after some point? In particular I want to get it to "rollback" a faulty recovery. On a related note, how can I get the most recent transaction id from the WAL segment that I want to rollback to? TIA, John Barham ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] So maybe SQLERRM? Sb knows how to check it?
Alvaro Herrera wrote: No, we don't have SQLERRM support yet. If you were asking about getting the messages from RAISE EXCEPTION, I'm afraid there's no way to get it in the EXCEPTION clause. If you want to contribute it, patches are welcome ... Actually, Pavel Stehule sent in a patch for this a few days ago, which I'll be reviewing and apply to HEAD shortly -- it will be in 8.1. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Possible to ignore transactions > n?
On Tue, Jun 07, 2005 at 10:25:26 -0700, John Barham <[EMAIL PROTECTED]> wrote: > Is is possible to tell PostgreSQL to ignore transactions committed > after some point? In particular I want to get it to "rollback" a > faulty recovery. PITR will let you do this. You need to have a complete backup of the database cluster and WAL logs from the time the backup was started through the time the bad transaction was committed. I haven't tried to ever do this, but there is documentation for 8.0 on the procedure. The development docs might be a bit better and are probably worth looking at. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] So maybe SQLERRM? Sb knows how to check it?
On Thu, Jun 09, 2005 at 12:25:19PM +1000, Neil Conway wrote: > Alvaro Herrera wrote: > >No, we don't have SQLERRM support yet. If you were asking about getting > >the messages from RAISE EXCEPTION, I'm afraid there's no way to get it > >in the EXCEPTION clause. > > > >If you want to contribute it, patches are welcome ... > > Actually, Pavel Stehule sent in a patch for this a few days ago, which > I'll be reviewing and apply to HEAD shortly -- it will be in 8.1. Huh, I meant a patch for getting the error message from RAISE EXCEPTION. Does Pavel's patch address that too? -- Alvaro Herrera () "Endurecerse, pero jamás perder la ternura" (E. Guevara) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] So maybe SQLERRM? Sb knows how to check it?
Alvaro Herrera wrote: Huh, I meant a patch for getting the error message from RAISE EXCEPTION. Does Pavel's patch address that too? Yes. (I just posted a revised patch to -patches, I'll apply it later tonight.) -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] how do you set foriegn keys in pgaccess?
I am a bit of a newbie to postgres, but I managed to install 8.0.4 on my windows box and it mostly appears to be working fine; I can set a primary key constraint, but when i try to set the foreign key it requires a 'reference' - but there is nothing there to chose from. I also have another query: How do you export the database tables and relationships etc? I found a pg_dump command in help and I am guessing this I what i need but where do I enter this command in pgaccess? Also how do you input existing SQL script into postgres? Thanks for any help. Jeremy _ Become a fitness fanatic @ http://xtramsn.co.nz/health ---(end of broadcast)--- TIP 8: explain analyze is your friend
