[GENERAL] comparing OLD and NEW in update trigger..
Hi there, i'm planning to use the following trigger function to update a timestamp of a row when it is UPDATEd: CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$ BEGIN NEW.modify_timestamp := now(); END; $$ LANGUAGE SQL; Since i like to use the same trigger procedure for various tables, i'm planning to keep it very generic. What i'd like to do now is to just update the modify_timestamp column if OLD and NEW are different. I'd LOOP over the row elements, and compare each column of OLD with NEW, and bailing out if there's a difference. I'd appreciate your help on the following two questions: - How can i get the column names of NEW/OLD? Is there a set returning function for this? - Is there a more efficient way to compare whole rows? thanks, Alex Mayrhofer --- http://nona.net/features/map/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql Segfault in 8.1
// FIXED // Tom, thank you so much for your help! Now running 8.1.2, the query now works quickly and properly. -Ben On Wednesday 25 January 2006 13:17, Benjamin Smith wrote: Version: postgresql-8.1.0-4.c4 I'll have to see about getting an update... Thanks a TON, -Ben On Wednesday 25 January 2006 13:11, you wrote: Benjamin Smith [EMAIL PROTECTED] writes: Aha, yep. Sorry: Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) bt #0 0x0043c82c in heap_modifytuple () #1 0x0043c8f5 in slot_getattr () #2 0x0047a50a in FormIndexDatum () #3 0x004ebee3 in ExecInsertIndexTuples () #4 0x004e5265 in ExecutorRun () #5 0x00564312 in FreeQueryDesc () #6 0x00565287 in PortalRun () #7 0x00560f8b in pg_parse_query () #8 0x00562e0e in PostgresMain () #9 0x0053d316 in ClosePostmasterPorts () #10 0x0053ea59 in PostmasterMain () #11 0x005033c3 in main () Oh, so this is happening during index entry creation? (The reference to heap_modifytuple is misleading, but in a debug-symbol-free backend it's not so surprising.) This suddenly looks a whole lot like a known bug: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01016.php Which version did you say you were using exactly? That bug is fixed in 8.1.1 ... regards, tom lane -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- I kept looking around for somebody to solve the problem. Then I realized I am somebody -Anonymous -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connected user in a triggerfunction
Anyone? Op vrijdag 20 januari 2006 07:56, schreef Dick Kniep: Hi list, We are using logging in a database based on triggers and plpgsql functions. This works OK. However, we want deletes to be recorded too, and there we want the user who connected to be recorded in the log. So, how can I get the connected user in a triggerfunction? Cheers, Dick Kniep ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] comparing OLD and NEW in update trigger..
Alex Mayrhofer wrote: Hi there, i'm planning to use the following trigger function to update a timestamp of a row when it is UPDATEd: CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$ BEGIN NEW.modify_timestamp := now(); END; $$ LANGUAGE SQL; I don't think you can write a trigger function in SQL - you'll want one of the procedural languages: plpgsql / plperl / pltcl etc. Since i like to use the same trigger procedure for various tables, i'm planning to keep it very generic. What i'd like to do now is to just update the modify_timestamp column if OLD and NEW are different. I'd LOOP over the row elements, and compare each column of OLD with NEW, and bailing out if there's a difference. I'd appreciate your help on the following two questions: - How can i get the column names of NEW/OLD? Is there a set returning function for this? You'll want one of the interpreted languages: pltcl / plperl / plphp etc. You'll find plpgsql can't cope with the sort of dynamic-typing required to do this easily. - Is there a more efficient way to compare whole rows? No. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 2 instances of postmaster with different data directories
surabhi.ahuja wrote: Hi, I am going to integrate my dtabase into a system. That system also has another database and uses postgres. However they have their own data directory and start postmaster by specifying that. I have seen that it is possible to run multiple postmasters on multiple ports by specifying diffrent data directories. But we are still to decide if we should go with the above approachor is it better to have just one data directory and one instance of postmaster on the default port. Two instances will be less efficient: - two separate areas of shared-memory - two caches - possible I/O competition (particularly with WAL I'd expect) - twice as many upgrades needed when patches are released but will allow: - two different versions of PostgreSQL - separate user lists Does that help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Connected user in a triggerfunction
Dick Kniep wrote: Anyone? I thought I saw an answer to this yesterday. Have you tried CURRENT_USER ? It's in the functions and operators section of the manuals (contrary to appearance, it is a function). Op vrijdag 20 januari 2006 07:56, schreef Dick Kniep: Hi list, We are using logging in a database based on triggers and plpgsql functions. This works OK. However, we want deletes to be recorded too, and there we want the user who connected to be recorded in the log. So, how can I get the connected user in a triggerfunction? Cheers, Dick Kniep -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Missing database entry in pg_database
Robert Korteweg robert ( at ) sambalbij ( dot ) nl writes: I have a problem with a database i'm maintaining. I first noticed the problem because i could not make a backup of the database i got the following error: pg_dump: missing pg_database entry for database xxx I verified this by selecting the pg_database. It was indeed gone. I did some more diggin and noticed that on doing a describe (\d table) of a table i could not see any or some of the columns in the table, and a few tables i also could just see the correct layout. It looks random. This sounds suspiciously like a transaction ID wraparound problem. Yes i read about that, but i thought this was not my problem because i vacuumed like i thought i should. The database is a very active database. It is running on Postgresql 7.3. The database is getting a VACUUM FULL ANALYZE every night. The *entire* database ... or are you just vacuuming the user tables and not the system catalogs? Daily vacuuming of the catalogs should have prevented any such problem (unless you are managing to exceed 1 billion transactions per day...) VACUUM FULL ANALYZE is the exact query i do on the database every night. I do not know if postgres will vacuum the systemtables as well with this command. And i do not believe the database will exceed the billion transactions a day. I also do not see the template databases, but i do not know if this is important. -- You can't reach second base, and keep your foot on first. Groeten, Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] filtering after join
andrew wrote: I want to use a UDF to filter tuples t that are generated after a join. More specifially, I have a UDF foo(record), which computes a value for a given tuple. I can do the filtering before the join. e.g.: select * from A, B where foo(A)2 and A.a=B.b; I suppose you mean where foo(A.a) 2? I've never seen SP's being applied to entire tables, especially inside a where clause. Next to that, the planner can't do much in this case (according to a thread here from this week), so you're likely to be stuck with sequential scans. But I want to apply foo() to the tuples generated by the join operation. How can I do that? You should be able to use something like (rewrote your join as well): select foo(some_column) from A left join b on (A.a=B.b) where A.a 2; If that's not what you mean to do, could you be a bit clearer as to what you're trying to achieve? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(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] user defined function
Thanks, Tom. It is done by modifying coerce_type() and can_coerce_type(). The reason I have to keep to verson 7.3 is I am working on a research prototype that is built over pgsql 7.3. I need the extra functions provided by that prototype. On 1/25/06, Tom Lane [EMAIL PROTECTED] wrote: andrew [EMAIL PROTECTED] writes: Sorry, I modified the parser code and forgot abt it. Now there is no problem in creating the function. But there is another problem. I create a function to accept record type parameter. But when I call it on a specific composite type, error is reported. The followings are what I have done: backend create function complete(record) returns int4 as '$libdir/qualityudf' language C QUERY: create function complete(record) returns int4 as '$libdir/qualityudf' language C backend select *, complete(Person) from Person QUERY: select *, complete(Person) from Person ERROR: Function complete(person) does not exist Hmm. Looking at parse_coerce.c, 8.1 is the first release that thinks named composite types can be coerced to RECORD. I think you may be forced to upgrade if you want this to work. Changing 7.3's coerce_type() to allow this case would be simple enough, but I think you are still going to be minus a lot of infrastructure that's required to make it actually do anything useful :-( regards, tom lane -- andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] many row updates
Hi! I have table with row named rank and some other rows. I have about 900.000 rows in table and I need to update all rows with the same value of rank, so I run update table set rank = 1; The only problem is speed. I'm waiting about 30 minutes and it's still running. My system is pg8.1 with one SATA HD and P43GHz I think there must be some configuration option or something to be update so slow. I also remove index from this row but doesn't help. Maybe somone have an idea what parameter to tune or what can I check to see why the update is so slow. regards Uros ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] many row updates
Uroš Gruber wrote: Hi! I have table with row named rank and some other rows. I have about 900.000 rows in table and I need to update all rows with the same value of rank, so I run update table set rank = 1; The only problem is speed. I'm waiting about 30 minutes and it's still running. My system is pg8.1 with one SATA HD and P43GHz And is your disk very busy? Has this table been updated a lot without being vacuumed regularly? -- Richard Huxton Archonet Ltd ---(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] Connected user in a triggerfunction
Oops, Sorry, didn't get the answers untill just now... Op donderdag 26 januari 2006 10:02, schreef Richard Huxton: Dick Kniep wrote: Anyone? I thought I saw an answer to this yesterday. Have you tried CURRENT_USER ? It's in the functions and operators section of the manuals (contrary to appearance, it is a function). Op vrijdag 20 januari 2006 07:56, schreef Dick Kniep: Hi list, We are using logging in a database based on triggers and plpgsql functions. This works OK. However, we want deletes to be recorded too, and there we want the user who connected to be recorded in the log. So, how can I get the connected user in a triggerfunction? Cheers, Dick Kniep ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgstattuple output?
Michael Crozier wrote: I think I see now, dead tuples are the tuples that have yet to be reclaimed by vacuum, not tuples that are ready to be used. I'm still rather confused, as this table is only modified via inserts. No deletes or update operations are ever performed. Logically (ie I don't really know the truth) this table should have no free tuples or free space except for the remainder of the last allocated page. I suspect that if a transaction rolls back, any inserts done in the interim turn into dead tuples. - John Burger MITRE ---(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] Trigger question: ROW or STATEMENT?
that answered my question. Thanks everyone Patrick Hatcher Development Manager Analytics/MIO Macys.com Michael Fuhr [EMAIL PROTECTED] To 01/25/06 07:52 PM Patrick Hatcher [EMAIL PROTECTED] cc Doug McNaught [EMAIL PROTECTED], pgsql-general@postgresql.org Subject Re: [GENERAL] Trigger question: ROW or STATEMENT? On Wed, Jan 25, 2006 at 02:47:45PM -0800, Patrick Hatcher wrote: Would I gain any advantage by changing to it to fire after the insert? If you're modifying the row then the trigger must fire before the insert. An after trigger can abort the operation by raising an error and it can perform actions like updating another table, but by the time an after trigger fires it's too late to change the current row (except via an UPDATE, and then you must beware of cascading triggers leading to infinite recursion). You might want to read Overview of Trigger Behavior in the documentation -- it describes the various kinds of triggers (row/statement and before/after) and when certain types are appropriate: http://www.postgresql.org/docs/8.1/interactive/triggers.html#TRIGGER-DEFINITION The documentation mentions that if you have no specific reason to use before or after, then before is more efficient. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] locale - polish, poland
Hi, Is there a possibility to use polish, Poland locale from windows version under linux? Locales = ISO8859-2 (pl_PL) has no collate order for signs like space, dot,... ? Filip ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] comparing OLD and NEW in update trigger..
Hi there, i'm using the following trigger function to update a timestamp of a row when it is UPDATEd: CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$ BEGIN NEW.modify_timestamp := now(); END; $$ LANGUAGE SQL; Since i like to use the same trigger for various tables, i'm planning to keep it very generic. What i'd like to do now is to just update the modify_timestamp column if OLD and NEW are different. I'd LOOP over the row elements, and compare each column of OLD with NEW, and bailing out if there's a difference. I'd appreciate your help on the following two questions: - How can i get the column names of NEW/OLD? Is there a set returning function for this? - Is there a more efficient way to compare whole rows? thanks, Alex Mayrhofer --- http://nona.net/features/map/ ---(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] Temporary table visibility
In article [EMAIL PROTECTED], James Croft [EMAIL PROTECTED] wrote: [given a bunch of temporary tables called session_data] % How can I determine if one of the above relations is a temporary % table in the current session (one of them, the first in ns 2200, is a % normal permanent table)? If there's data in the table, you could select tableoid from session_data limit 1, then check the namespace corresponding to that table. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(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] pgstattuple output?
On Thu, Jan 26, 2006 at 10:13:52AM -0500, John D. Burger wrote:
I suspect that if a transaction rolls back, any inserts done in the
interim turn into dead tuples.
Yep:
test= CREATE TABLE foo (x integer);
CREATE TABLE
test= BEGIN; INSERT INTO foo SELECT 1 FROM generate_series(1, 1); ROLLBACK;
BEGIN
INSERT 0 1
ROLLBACK
test= INSERT INTO foo SELECT 1 FROM generate_series(1, 2);
INSERT 0 2
test= \x
Expanded display is on.
test= SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]--+
table_len | 1089536
tuple_count| 2
tuple_len | 64
tuple_percent | 58.74
dead_tuple_count | 1
dead_tuple_len | 32
dead_tuple_percent | 29.37
free_space | 6872
free_percent | 0.63
test= VACUUM foo;
VACUUM
test= SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]--+
table_len | 1089536
tuple_count| 2
tuple_len | 64
tuple_percent | 58.74
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 326692
free_percent | 29.98
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Access Problem After Version Upgrade
Last week I upgraded from -7.4.3 to -8.1.2. I had some problems moving data because of both my ignorance of the proper syntax and the move from /usr/local/pgsql to /var/lib/pgsql. Now I cannot access the server: [EMAIL PROTECTED] ~]$ psql -l psql: could not connect to server: Permission denied Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? This also prevents me from logging in to SQL-Ledger and other applications. Now, I don't know that the server is accepting connections, but srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432= exists as a socket. What do I do to trace the source of this problem and fix it? TIA, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] Access Problem After Version Upgrade
On 1/26/06 11:53 AM, Rich Shepard [EMAIL PROTECTED] wrote: Last week I upgraded from -7.4.3 to -8.1.2. I had some problems moving data because of both my ignorance of the proper syntax and the move from /usr/local/pgsql to /var/lib/pgsql. Now I cannot access the server: [EMAIL PROTECTED] ~]$ psql -l psql: could not connect to server: Permission denied Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? This also prevents me from logging in to SQL-Ledger and other applications. Now, I don't know that the server is accepting connections, but srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432= exists as a socket. What do I do to trace the source of this problem and fix it? You did start the server? Did you fix the pg_hba.conf file? Does your postgresql.conf file allow tcp connections? Those are the places I would look. Sean ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Access Problem After Version Upgrade -- Update
On Thu, 26 Jan 2006, Rich Shepard wrote: psql: could not connect to server: Permission denied What do I do to trace the source of this problem and fix it? Some progress to report. A Google search found a reply from Tom Lane last month to someone reporting the same error. I changed permissions on the socket to make them world writable, and that fixed one problem: [EMAIL PROTECTED] ~]$ psql -l List of databases Name | Owner| Encoding -++--- aesi| sql-ledger | LATIN1 eiabusiness | rshepard | SQL_ASCII postgres| postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII test| postgres | SQL_ASCII webcollab | rshepard | SQL_ASCII (7 rows) So, now I can see the tables in the various databases, but SQL-Ledger still cannot: Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. How do I get this fixed, please? Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] EXCEPTION Function
hi all,I am using PostgreSQL 8.0.1 on Fedora core 2.My goal is to create a common Exception handling stored function that takes Error Constant (as defined in Error Codes document: AppendixA) and raises a customized exception.
The problem is:(a) How do i catch these Error Constants? I was unable to use SQLSTATE and SQLERRM, for somehow the database didnt understand them.(b) How do i catch these from OTHERS exception and pass it to the Common Exception Handling function?
-- In SP fucntion, error could be in Inserts, divide by zero pr updates.-- The errors could be because a table is locked, or some other reasons.CREATE or replace SP(int) returns int as $$DECLARE res int;
BEGIN res:=0; insert into tbl values ('a','b','c'); res:=2/$1; update tbl set colA='x' where colA='a'; return res;EXCEPTION WHEN OTHERS THEN Common_Exception_Handling_Function(Error_Constant);
END;$$ language plpgsql;-- This common function will be called from EXCEPTION blocks of all Stored functions (around 300).-- All error codes will be defined in this common function and will raise a customized Exception message.
CREATE or replace Common_Exception_Handling_Function(varchar) returns VOID as $$BEGINif $1='DIVISION_BY_ZERO' then RAISE EXCEPTION 'DIVISION BY ZERO';elsif $1='SYNTAX_ERROR' then RAISE EXCEPTION 'SYNTAX ERROR';
. . . . . . . . . end if;END;
$$ language plpgsql;
Re: [GENERAL] Access Problem After Version Upgrade
On Thu, 26 Jan 2006, Sean Davis wrote: You did start the server? Did you fix the pg_hba.conf file? Does your postgresql.conf file allow tcp connections? Those are the places I would look. Sean, Thank you. Yes, the server is running: 21839 ?S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data I don't know what needs fexing in /var/lib/pgsql/data/pg_hba.conf. It has: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: hostall all ::1/128 trust which should cover both socket and tcp connections, if I understand correctly. The header of postgresql.conf says that the commented options are defaults, and the connections section has: #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 100 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off Is this correct? Thanks again, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] Access Problem After Version Upgrade
Rich Shepard [EMAIL PROTECTED] writes: Now, I don't know that the server is accepting connections, but srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432= exists as a socket. What do I do to trace the source of this problem and fix it? That's got the wrong ownership (should be postgres not root) and the wrong permissions (should be world-writable). Did somebody do something silly like chown everything in /tmp? You could manually fix the socket file's ownership and permissions, or just remove it and restart the postmaster. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Access Problem After Version Upgrade -- Update
Rich Shepard [EMAIL PROTECTED] writes: So, now I can see the tables in the various databases, but SQL-Ledger still cannot: Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. How do I get this fixed, please? That's a webserver error usually meaning a CGI script crashed or gave bad output. Look in the webserver error log file to see what happened. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Hey!!!
Hey. I am new here. I’m from Poland:-) I have one question (I don’t now if this is the right group for this question and … if my English is enough good… :P): Are the developers going to implement some system trigger like in ORACLE i.e. “on login” trigger ?? -- Best regards LaroG ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Access Problem After Version Upgrade -- Update
On Thu, 26 Jan 2006, Doug McNaught wrote: That's a webserver error usually meaning a CGI script crashed or gave bad output. Look in the webserver error log file to see what happened. Doug, Sigh. All I see there are references to SQL-Ledger scripts not finding libraries (which are installed where SQL-Ledger is looking for them), and it's login.pl script failing to run. When I posted on that mail list, Dieter pulled the thread and wrote me an angry message that SQL-Ledger is not broken, but PostgreSQL is. The postmaster is running, and I (as an individual) can now see what databases are there. So, that permission error on the socket is fixed and it looks like postgres is doing just what it should be doing. Dieter Simander tells me there's nothing wrong with SL. Here's /var/log/apache/error_log: [Thu Jan 26 08:59:36 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.7/i486-linux/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.3: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.7/i486-linux/DynaLoader.pm line 230. at (eval 8) line 3 Compilation failed in require at (eval 8) line 3. Perhaps a required shared library or dll isn't installed where expected at SL/User.pm line 116 Compilation failed in require at /usr/local/sql-ledger/login.pl line 92. [Thu Jan 26 09:03:29 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl Now, when I point firefox at: http://eia.appl-ecosys.com/webcollab-1.70/index/php Up comes the Webcollab login page and I can log in. This suggests to me that there's nothing wrong with apache (httpd), either. Checkmate. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] incremental backups
I am looking into using WAL archiving for incremental backups. It all seems fairly straightforward except for one thing. So you set up the archiving of the WAL files. Then you set up cron or something to regularly do a physical backup of the data directory. But when you do the physical backup you don't have the last WAL file archived yet that you need to restore that physical backup. So you always need to keep at least two physical backups around so that you know that at least one of them has the WAL files needed for recovery. The question I have is: how do I know if I can use the latest one? That is if I first do physical backup A and then later do physical backup B and then I want to do a restore. How do I know when I've got the files I need to use B so that I don't have to go all the way back to A? My initial thoughts are that I could: a) just before or after calling pg_stop_backup check the file system to see what the last archived WAL file is on disk and make sure that that I get the next one before I try restoring from that backup. b) just before or after calling pg_stop_backup check postgres to see to see what the current active WAL file is and make sure it has been archived before I try to restore from that backup. c) Always just use backup A. No c seems the easiest but is that even fail safe? I realize it wouldn't really ever happen in an active production environment that was set up right but say you did backup A and backup B and during that whole time you had few writes in postgres that you never filled up a whole WAL file so both of the backups are invalid. Then you would have to always go to option a or b above to verify that a given backup was good so that any previous backups could be deleted. Wouldn't it make things a lot easier if the backup history file not only gave you the name of the first file that you need but also the last one? Then you could look at a given backup and say I need this start file and this end file. Then you could delete all archived WAL files before start file. And you could delete any old physical dumps because you know that your last physical dump was good. It would just save you the step in the backups process of figuring out what that file is. And it seems like pg_stop_backup could determine that on it's own. Does that make sense? Am I totally off base here? Rick ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Access Problem After Version Upgrade
On Thu, 26 Jan 2006, Tom Lane wrote: That's got the wrong ownership (should be postgres not root) Tom, I wondered about that. and the wrong permissions (should be world-writable). Did somebody do something silly like chown everything in /tmp? Not me ... intentionally. You could manually fix the socket file's ownership and permissions, or just remove it and restart the postmaster. I did the permissions before and just did the ownership. Still no happiness with SL. Thanks very much, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Access Problem After Version Upgrade -- Update
looks like perl/DBD::Pg is looking for libpq.so.3 but postgresql 8.1.X supplies libpq.so.4. You need to rebuild DBD::Pg to get this to work (atleast that is what I have been doing for my upgrades from 7.4.X to 8.1.2) Jim -- Original Message --- From: Rich Shepard [EMAIL PROTECTED] To: Doug McNaught [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Thu, 26 Jan 2006 09:30:05 -0800 (PST) Subject: Re: [GENERAL] Access Problem After Version Upgrade -- Update On Thu, 26 Jan 2006, Doug McNaught wrote: That's a webserver error usually meaning a CGI script crashed or gave bad output. Look in the webserver error log file to see what happened. Doug, Sigh. All I see there are references to SQL-Ledger scripts not finding libraries (which are installed where SQL-Ledger is looking for them), and it's login.pl script failing to run. When I posted on that mail list, Dieter pulled the thread and wrote me an angry message that SQL-Ledger is not broken, but PostgreSQL is. The postmaster is running, and I (as an individual) can now see what databases are there. So, that permission error on the socket is fixed and it looks like postgres is doing just what it should be doing. Dieter Simander tells me there's nothing wrong with SL. Here's /var/log/apache/error_log: [Thu Jan 26 08:59:36 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.7/i486-linux/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.3: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.7/i486- linux/DynaLoader.pm line 230. at (eval 8) line 3 Compilation failed in require at (eval 8) line 3. Perhaps a required shared library or dll isn't installed where expected at SL/User.pm line 116 Compilation failed in require at /usr/local/sql-ledger/login.pl line 92. [Thu Jan 26 09:03:29 2006] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl Now, when I point firefox at: http://eia.appl-ecosys.com/webcollab-1.70/index/php Up comes the Webcollab login page and I can log in. This suggests to me that there's nothing wrong with apache (httpd), either. Checkmate. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings --- End of Original Message --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo
I am not sure what to do on this. Right now we have a one-line test: AC_REPLACE_FUNCS([getaddrinfo]) To test for a macro we are going to need to add include netdb.h, and the LINK test below is overkill. I am thinking we should just hard-code in HAVE_GETADDRINFO for the True64 platform; anything more is going to be just a Tru64 hack anyway. --- R, Rajesh (STSD) wrote: sorry. It is a macro. so, would it be better to check for the macro as suggested by Tom or go with this patch $ diff -r configure.in configure.in.new 918a919 AC_MSG_CHECKING([for getaddrinfo]) 920c921,926 AC_REPLACE_FUNCS([getaddrinfo]) --- AC_TRY_LINK([#include netdb.h #include assert.h], [char (*f)();f=getaddrinfo;], ac_cv_func_getaddrinfo=yes, ac_cv_func_getaddrinfo=no) if test x$ac_cv_func_getaddrinfo = xyes; then AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the getaddrinfo function]) fi 923a930 AC_MSG_RESULT([$ac_cv_func_getaddrinfo]) I guess, instead of adding seperate code for macro checking as suggested by Tom, this might serve dual purpose. Thanks, Rajesh R -- This space intentionally left non-blank. -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 2:46 PM To: R, Rajesh (STSD) Cc: Tom Lane; pgsql-hackers@postgresql.org; pgsql-general@postgresql.org Subject: Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function. On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) wrote: Its not a macro. I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by configure.in for configure does not have #include netdb.h. Hence function is not detected(unresolved getaddrinfo). Hence I thought AC_TRY_LINK could give test program instead of AC_REPLACE_FUNCS taking one. But if it isn't a macro, why do you need the header file? In C it's perfectly legal to declare the symbol yourself and try to link and it should work *unless* it's normally a macro. We're still missing some necessary understanding here... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Access Problem After Version Upgrade -- Update
Rich Shepard [EMAIL PROTECTED] writes: install_driver(Pg) failed: Can't load '/usr/lib/perl5/site_perl/5.8.7/i486-linux/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.3: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.7/i486-linux/DynaLoader.pm line 230. This seems to be your problem right here. Is libpq.so.3 installed (as opposed to some other version number)? Is it where the dynamic loader will look for it? I'm guessing your upgrade replaced libpq.so.3 with libpq.so.4. Did you perhaps override RPM's complaints that there was a dependency on libpq.so.3? (If there wasn't one, there's something wrong with the packaging of DBD::Pg.) You probably need to either put back libpq.so.3, or update DBD::Pg. 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] incremental backups
I didn't read your mail very carefully, but I guess you want: - turn on WAL archiving, and archive all WAL logs; - take the file system backup at regular time points, optionally you can keep them also for point in time recovery; Then you always have all the WAL files you need to recover to any point in time you need. You can then supply all the WAL files which are needed by the last file system backup to recover after a crash, or you can supply all the WAL files up to the time point just before your student DBA deleted all your data. HTH, Csaba. On Thu, 2006-01-26 at 18:33, Rick Gigger wrote: I am looking into using WAL archiving for incremental backups. It all seems fairly straightforward except for one thing. So you set up the archiving of the WAL files. Then you set up cron or something to regularly do a physical backup of the data directory. But when you do the physical backup you don't have the last WAL file archived yet that you need to restore that physical backup. So you always need to keep at least two physical backups around so that you know that at least one of them has the WAL files needed for recovery. The question I have is: how do I know if I can use the latest one? That is if I first do physical backup A and then later do physical backup B and then I want to do a restore. How do I know when I've got the files I need to use B so that I don't have to go all the way back to A? My initial thoughts are that I could: a) just before or after calling pg_stop_backup check the file system to see what the last archived WAL file is on disk and make sure that that I get the next one before I try restoring from that backup. b) just before or after calling pg_stop_backup check postgres to see to see what the current active WAL file is and make sure it has been archived before I try to restore from that backup. c) Always just use backup A. No c seems the easiest but is that even fail safe? I realize it wouldn't really ever happen in an active production environment that was set up right but say you did backup A and backup B and during that whole time you had few writes in postgres that you never filled up a whole WAL file so both of the backups are invalid. Then you would have to always go to option a or b above to verify that a given backup was good so that any previous backups could be deleted. Wouldn't it make things a lot easier if the backup history file not only gave you the name of the first file that you need but also the last one? Then you could look at a given backup and say I need this start file and this end file. Then you could delete all archived WAL files before start file. And you could delete any old physical dumps because you know that your last physical dump was good. It would just save you the step in the backups process of figuring out what that file is. And it seems like pg_stop_backup could determine that on it's own. Does that make sense? Am I totally off base here? Rick ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] incremental backups
Um, no you didn't read my email at all. I am aware of all of that and it is clearly outlined in the docs. My email was about a specific detail in the process. Please read it if you want to know what my actual question was. Thanks, Rick On Jan 26, 2006, at 10:41 AM, Csaba Nagy wrote: I didn't read your mail very carefully, but I guess you want: - turn on WAL archiving, and archive all WAL logs; - take the file system backup at regular time points, optionally you can keep them also for point in time recovery; Then you always have all the WAL files you need to recover to any point in time you need. You can then supply all the WAL files which are needed by the last file system backup to recover after a crash, or you can supply all the WAL files up to the time point just before your student DBA deleted all your data. HTH, Csaba. On Thu, 2006-01-26 at 18:33, Rick Gigger wrote: I am looking into using WAL archiving for incremental backups. It all seems fairly straightforward except for one thing. So you set up the archiving of the WAL files. Then you set up cron or something to regularly do a physical backup of the data directory. But when you do the physical backup you don't have the last WAL file archived yet that you need to restore that physical backup. So you always need to keep at least two physical backups around so that you know that at least one of them has the WAL files needed for recovery. The question I have is: how do I know if I can use the latest one? That is if I first do physical backup A and then later do physical backup B and then I want to do a restore. How do I know when I've got the files I need to use B so that I don't have to go all the way back to A? My initial thoughts are that I could: a) just before or after calling pg_stop_backup check the file system to see what the last archived WAL file is on disk and make sure that that I get the next one before I try restoring from that backup. b) just before or after calling pg_stop_backup check postgres to see to see what the current active WAL file is and make sure it has been archived before I try to restore from that backup. c) Always just use backup A. No c seems the easiest but is that even fail safe? I realize it wouldn't really ever happen in an active production environment that was set up right but say you did backup A and backup B and during that whole time you had few writes in postgres that you never filled up a whole WAL file so both of the backups are invalid. Then you would have to always go to option a or b above to verify that a given backup was good so that any previous backups could be deleted. Wouldn't it make things a lot easier if the backup history file not only gave you the name of the first file that you need but also the last one? Then you could look at a given backup and say I need this start file and this end file. Then you could delete all archived WAL files before start file. And you could delete any old physical dumps because you know that your last physical dump was good. It would just save you the step in the backups process of figuring out what that file is. And it seems like pg_stop_backup could determine that on it's own. Does that make sense? Am I totally off base here? Rick ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Access Problem After Version Upgrade -- Update
On Thu, 26 Jan 2006, Jim Buttafuoco wrote: looks like perl/DBD::Pg is looking for libpq.so.3 but postgresql 8.1.X supplies libpq.so.4. You need to rebuild DBD::Pg to get this to work (atleast that is what I have been doing for my upgrades from 7.4.X to 8.1.2) Jim, I thought I had mentioned this, but that might have been in my message to the sql-ledger list. [EMAIL PROTECTED] ~]$ locate libpq.so.3 /usr/local/pgsql/lib/libpq.so.3.0 /usr/local/pgsql/lib/libpq.so.3.1 /usr/local/pgsql/lib/libpq.so.3 ... but that's not where SL is looking for them! When I searched for libpq.so.4, I discovered that in /usr/lib. As soon as I copied all the '3' versions to that directory, SL found its way. Thank you _very_ much, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Suggestions on storing and retrieving geocode data
I am looking for suggestions on storing and retrieving geocode information. My application currently stores 2 columns (lat, long) as numeric and I have a btree index on them. This works fine for the current set of data, but as it expands I know it will become an issue. I am looking at changing the index to an rtree, but I did not know if it is better to create a point column or if I could use the existing lat/long columns. The query will always be to select points inside a box. Thanks in advance, Woody iGLASS Networks 211-A S. Salem St Apex NC 27502 (919) 387-3550 x813 www.iglass.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Access Problem After Version Upgrade -- FIXED
On Thu, 26 Jan 2006, Rich Shepard wrote: What do I do to trace the source of this problem and fix it? Thanks to Jim I found that libpq.so.3 were in the old directory. As soon as I moved them to /usr/lib (with libpq.so.4), SQL-Ledger allowed me to log in. All of this reminds me of the time, about 8 years ago, when postgres would install in one directory, but Red Hat's rpms put it in another directory. That caused all sorts of problems until I started building from source and ignoring the packages. Here, it was the move from /usr/local/pgsql to /var/lib/pgsql that caused me all this grief. A huge thank you to all of you -- and especially Jim. Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Encoding errors when upgrading from 7.4 to 8.1
Hi, I am upgrading from 7.4.8 - 8.1.2 on Linux 2.6.14.3 #1 SMP I have installed 8.1.2 and created the database (with encoding 'UNICODE', as I had done in 7.4.8) and am trying to load a 7.4.8 dump file but I am getting a few errors like this: psql:bugasbase2-backup:45880: ERROR: invalid UTF-8 byte sequence detected near byte 0xb5 CONTEXT: COPY array_scheme, line 17560, column gene_identifier: [EMAIL PROTECTED] (0G11) This dump file will load error free into 7.4.8. Does anybody have any ideas why this is failing in 8.1.2? 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Suggestions on storing and retrieving geocode data
On Thu, Jan 26, 2006 at 12:55:46PM -0500, George Woodring wrote: I am looking for suggestions on storing and retrieving geocode information. Consider using PostGIS: http://www.postgis.org/ -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Encoding errors when upgrading from 7.4 to 8.1
Adam Witney wrote: psql:bugasbase2-backup:45880: ERROR: invalid UTF-8 byte sequence detected near byte 0xb5 CONTEXT: COPY array_scheme, line 17560, column gene_identifier: [EMAIL PROTECTED] (0G11) This dump file will load error free into 7.4.8. Does anybody have any ideas why this is failing in 8.1.2? 8.1 changed UTF-8 handling to be more strict about invalid sequences. You may want to read the 8.1.0 release notes. http://www.postgresql.org/docs/8.1/interactive/release-8-1.html -- Seneca Cunningham [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Arrays
I would like to make a table of 20 plus columns the
majority of columns being arrays.
The following test works. The array will hold up to
five characteristics of each parameter including the unit of measurement used.
Using traditional methods I would need six columns to accomplish the same end
(Min, Max, Norm plus a unit column for each).
The downside is that the number of brackets
required increases for each succeeding column for insert and update. The last
column would comprise 48 brackets, 24 before - 24 after.
Is there a work-around for this.
Bob Pawley
create table specifications (fluid_id
int4 ,Flow_Rate varchar array[5],Temperature varchar
array[5],Pressure_In varchar array[5] ,Pressure_Out varchar
array[5]);
insert into specifications values ('1', '{25,
50, 100, gpm}', '{{100, 250, 500, DegF}}', '{{{10, 40, 100, psi}}}',
'60, 120, 150, psi' );
Re: [GENERAL] Access Problem After Version Upgrade -- FIXED
Here, it was the move from /usr/local/pgsql to /var/lib/pgsql that caused me all this grief. A huge thank you to all of you -- and especially Jim. Although I am glad you were able to get up and running, typically you don't want to move libs like that. Instead update your /etc/ld.so.conf and run ldconfig. Sincerely, Joshua D. Drake Rich -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to implement nested transactions
I want to implement nest transactions like begin; CREATE temp table t2 (foo char(20) primary key); begin; CREATE temp table t1 (bar char(20) primary key); commit; rollback; I'm expecting that t1 and t2 tables are not created since last rollback rolls back its nested transaction. However, both tables are created. Any idea hot to force parent transaction rollback to roll back committed nested transactions ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Access Problem After Version Upgrade -- FIXED
On Thu, 26 Jan 2006, Joshua D. Drake wrote: Although I am glad you were able to get up and running, typically you don't want to move libs like that. Instead update your /etc/ld.so.conf and run ldconfig. True, Josh. What I'd prefer to do is remove /usr/local/pgsql/ once I know that nothing there is being used any more with the 8.x versions of postgres. I thought of making softlinks, but those would fail as soon as the directory tree was removed. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo
Bruce Momjian pgman@candle.pha.pa.us writes: I am not sure what to do on this. Right now we have a one-line test: AC_REPLACE_FUNCS([getaddrinfo]) To test for a macro we are going to need to add include netdb.h, and the LINK test below is overkill. I am thinking we should just hard-code in HAVE_GETADDRINFO for the True64 platform; anything more is going to be just a Tru64 hack anyway. I still want to understand why any change is needed at all. There must be something very peculiar about getaddrinfo on Tru64 if the original coding doesn't work. Why is it different from every other function we test for? 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] Access Problem After Version Upgrade -- FIXED
Rich Shepard [EMAIL PROTECTED] writes: On Thu, 26 Jan 2006, Joshua D. Drake wrote: Although I am glad you were able to get up and running, typically you don't want to move libs like that. Instead update your /etc/ld.so.conf and run ldconfig. True, Josh. What I'd prefer to do is remove /usr/local/pgsql/ once I know that nothing there is being used any more with the 8.x versions of postgres. I thought of making softlinks, but those would fail as soon as the directory tree was removed. FWIW, my practice when compiling PG from source (which I usually do) is as follows: * Configure each release with '--prefix=/usr/local/pgsql-8.1' (or whatever) * Create (as far as disk space permits) independent data directories for each version--e.g. '/var/lib/pgsql-8.1/data'. * Symlink whatever version I'm running to '/usr/local/pgsql'. Have the standard PATH contain '/usr/local/pgsql/bin'. This way, when I do an upgrade, I can test beforehand by setting PATH and LD_LIBRARY_PATH (if applicable) appropriately, migrating my data, running whatever programs I want to test, then switching the symlink. It's worked fairly well, and it's nice to have the old binaries and data directory sitting there to switch back to of something breaks horribly. The problem I've seen with RPM upgrades is that if something breaks in the data upgrade process, the old binaries are gone and it's a pain to get back to where you were. Putting locally-compiled software in /usr/bin or /usr/lib is a bad idea, generally, as you may confuse your package manager. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Arrays
On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote: I would like to make a table of 20 plus columns the majority of columns being arrays. The following test works. The array will hold up to five characteristics of each parameter including the unit of measurement used. Using traditional methods I would need six columns to accomplish the same end (Min, Max, Norm plus a unit column for each). And why would that be undesirable ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement nested transactions
Andrus [EMAIL PROTECTED] writes: I want to implement nest transactions like begin; CREATE temp table t2 (foo char(20) primary key); begin; CREATE temp table t1 (bar char(20) primary key); commit; rollback; That is not the correct syntax. Use SAVEPOINT, then ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Arrays
Bob Pawley [EMAIL PROTECTED] writes:
insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100, 250,
500, DegF}}',
'{{{10, 40, 100, psi}}}', '60, 120, 150, psi' );
Why are you putting in all those extra braces?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Arrays
Because it gives me an error otherwise.
I am following the rules layed out in the documentation as follows -
Bob
8.10.2. Array Value Input
Now we can show some INSERT statements.
INSERT INTO sal_emp
VALUES ('Bill',
'{1, 1, 1, 1}',
'{{meeting, lunch}, {meeting}}');
ERROR: multidimensional arrays must have array expressions with matching
dimensionsNote that multidimensional arrays must have matching extents for
each dimension. A mismatch causes an error report.
INSERT INTO sal_emp
VALUES ('Bill',
'{1, 1, 1, 1}',
'{{meeting, lunch}, {training, presentation}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{2, 25000, 25000, 25000}',
'{{breakfast, consulting}, {meeting, lunch}}');
A limitation of the present array implementation is that individual elements
of an array cannot be SQL null values. The entire array can be set to null,
but you can't have an array with some elements null and some not. (This is
likely to change in the future.)
The result of the previous two inserts looks like this:
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
---+---+---
Bill | {1,1,1,1} |
{{meeting,lunch},{training,presentation}}
Carol | {2,25000,25000,25000} |
{{breakfast,consulting},{meeting,lunch}}
(2 rows)
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Thursday, January 26, 2006 11:16 AM
Subject: Re: [GENERAL] Arrays
Bob Pawley [EMAIL PROTECTED] writes:
insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100,
250, 500, DegF}}',
'{{{10, 40, 100, psi}}}', '60, 120, 150, psi' );
Why are you putting in all those extra braces?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am not sure what to do on this. Right now we have a one-line test: AC_REPLACE_FUNCS([getaddrinfo]) To test for a macro we are going to need to add include netdb.h, and the LINK test below is overkill. I am thinking we should just hard-code in HAVE_GETADDRINFO for the True64 platform; anything more is going to be just a Tru64 hack anyway. I still want to understand why any change is needed at all. There must be something very peculiar about getaddrinfo on Tru64 if the original coding doesn't work. Why is it different from every other function we test for? I have the answer. Tru64 netdb.h has: #if defined (_SOCKADDR_LEN) || defined (_XOPEN_SOURCE_EXTENDED) #define getaddrinfo ngetaddrinfo #else #define getaddrinfo ogetaddrinfo #endif so it is a macro, and configure produces this line: #undef $ac_func meaning that even if we added #include netdb.h, our configure test still would not work. Perhaps we should just test for ngetaddrinfo on that platform, and define HAVE_GETADDRINFO. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo
Bruce Momjian pgman@candle.pha.pa.us writes: I have the answer. Tru64 netdb.h has: #if defined (_SOCKADDR_LEN) || defined (_XOPEN_SOURCE_EXTENDED) #define getaddrinfo ngetaddrinfo #else #define getaddrinfo ogetaddrinfo #endif Seems like the same method we use for testing finite() and other possible-macros would handle this, then. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Arrays
I second that, and I'd love to have someone clarify the appropriate time to use arrays vs. more columns or an referenced tabled. I've always found that confusing. Thanks, Eric Karsten Hilbert wrote: And why would that be undesirable ? On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote: I would like to make a table of 20 plus columns the majority of columns being arrays. The following test works. The array will hold up to five characteristics of each parameter including the unit of measurement used. Using traditional methods I would need six columns to accomplish the same end (Min, Max, Norm plus a unit column for each). And why would that be undesirable ? Karsten
Fw: [GENERAL] Arrays
- Original Message - From: Bob Pawley [EMAIL PROTECTED] To: Karsten Hilbert [EMAIL PROTECTED] Sent: Thursday, January 26, 2006 11:26 AM Subject: Re: [GENERAL] Arrays Because with arrays I can include other information such as pointers to conversion factors and hopefully implement systems more easily with all pertinent information tied to a single source (single column). Bob - Original Message - From: Karsten Hilbert [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Thursday, January 26, 2006 11:00 AM Subject: Re: [GENERAL] Arrays On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote: I would like to make a table of 20 plus columns the majority of columns being arrays. The following test works. The array will hold up to five characteristics of each parameter including the unit of measurement used. Using traditional methods I would need six columns to accomplish the same end (Min, Max, Norm plus a unit column for each). And why would that be undesirable ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Arrays
I second that, and I'd love to have someone clarify the appropriate time to use arrays vs. more columns or an referenced tabled. I've always found that confusing. Thanks, Eric Karsten Hilbert wrote: And why would that be undesirable ? On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote: I would like to make a table of 20 plus columns the majority of columns being arrays. The following test works. The array will hold up to five characteristics of each parameter including the unit of measurement used. Using traditional methods I would need six columns to accomplish the same end (Min, Max, Norm plus a unit column for each). And why would that be undesirable ? Karsten ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Arrays
On Thu, 26 Jan 2006, Bob Pawley wrote:
Because it gives me an error otherwise.
What error?
insert into specifications values ('1', '{25, 50, 100, gpm}',
'{100, 250, 500, DegF}',
'{10, 40, 100, psi}', '{60, 120, 150, psi}' );
seems to insert fine for me given the table definition you gave.
I am following the rules layed out in the documentation as follows -
Are you trying to do multidimensional arrays or just a set of single
dimensional ones?
---(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] My very first PL/pgSQL procedure...
why not just use setval(), see docs for arguments.
I think that setval('seq_name', xx) have the same effect than
SEQUENCE seq_name RESTART WITH xx (the instruction I
wanted to use in my first function).
But the problem is that in both cases, the sequence should be
locked in order to prevent problems with concurrent transactions.
For example, if I want to raise the sequence value to 1000, while
its current value is 998, I would call :
setval('seq_name', 1000);
But because the sequence could not be locked, some concurrent
transactions could have already raised it's current value in the
meantime to, say, 1002, before the effective execution of setval().
So, instead of raising the value to 1000, my function could have
done the opposite (from 1002 to 1000) ! And the two next INSERT
using this sequence would then break !!
The only solution I found to prevent this is with my loops !!
Best regards,
Philippe Ferreira.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] VACUUM Question
We have 2 tables we expect to grow byup to 50,000 rows per day eachdepending on the customer. In normal operation we will most likely never update or delete from these tables asthey arefor historical reporting. (Eventually we may but a limit on the amount of data and delete older than X months or such) We intend tocreate a number of indexes based upon the reporting search criteria. What would the best setup be for VACUUM, ANALYSE, REINDEX. Alot of the infor refers to data hanging around from deletes and updates which in normal course we will not do on these tables? Oisin
Re: [GENERAL] Arrays
ERROR: malformed array literal: {100, 250, 500, DegF)
I want to do single dimension arrays.
How did I turn it into multidmensional?
Bob
- Original Message -
From: Stephan Szabo [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Tom Lane [EMAIL PROTECTED]; Postgresql
pgsql-general@postgresql.org
Sent: Thursday, January 26, 2006 11:43 AM
Subject: Re: [GENERAL] Arrays
On Thu, 26 Jan 2006, Bob Pawley wrote:
Because it gives me an error otherwise.
What error?
insert into specifications values ('1', '{25, 50, 100, gpm}',
'{100, 250, 500, DegF}',
'{10, 40, 100, psi}', '{60, 120, 150, psi}' );
seems to insert fine for me given the table definition you gave.
I am following the rules layed out in the documentation as follows -
Are you trying to do multidimensional arrays or just a set of single
dimensional ones?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Arrays
ERROR: malformed array literal: {100, 250, 500, DegF)
I want to do single dimension arrays.
How did I turn it into multidmensional?
Bob
- Original Message -
From: Stephan Szabo [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Tom Lane [EMAIL PROTECTED]; Postgresql
pgsql-general@postgresql.org
Sent: Thursday, January 26, 2006 11:43 AM
Subject: Re: [GENERAL] Arrays
On Thu, 26 Jan 2006, Bob Pawley wrote:
Because it gives me an error otherwise.
What error?
insert into specifications values ('1', '{25, 50, 100, gpm}',
'{100, 250, 500, DegF}',
'{10, 40, 100, psi}', '{60, 120, 150, psi}' );
seems to insert fine for me given the table definition you gave.
I am following the rules layed out in the documentation as follows -
Are you trying to do multidimensional arrays or just a set of single
dimensional ones?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Arrays
Bob Pawley [EMAIL PROTECTED] writes:
ERROR: malformed array literal: {100, 250, 500, DegF)
You wrote a right paren, not a right brace ...
I want to do single dimension arrays.
How did I turn it into multidmensional?
The multiple levels of braces create a multidimensional array.
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] Arrays
Bob Pawley wrote:
ERROR: malformed array literal: {100, 250, 500, DegF)
Well you have a typo:
{100, 250, 500, DegF) is wrong...
{100, 250, 500, DegF} is correct...
Sincerely,
Joshua D. Drake
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] How to implement nested transactions
That is not the correct syntax. Use SAVEPOINT, then ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. postgres log file: 2006-01-26 21:45:59 LOG: statement: INSERT INTO dok . 2006-01-26 21:45:59 ERROR: insert or update on table dok violates foreign key constraint dok_klient_fkey 2006-01-26 21:45:59 DETAIL: Key (klient)=(ESTATEINVEST) is not present in table klient. 2006-01-26 21:45:59 STATEMENT: INSERT INTO dok 2006-01-26 21:45:59 LOG: statement: ROLLBACK-- this statement seems to be generated automatically by Microsoft Visual FoxPro or by Postgres ODBC driver 2006-01-26 21:45:59 LOG: statement: ROLLBACK TO savepoint copyone 2006-01-26 21:45:59 ERROR: ROLLBACK TO SAVEPOINT may only be used in transaction blocks My client application (Microsoft Visual FoxPro 9) seems to generate automatic ROLLBACK statement in case if one of its commands (APPEND FROM TABLE) fails I have'nt found a way to disable this ROLLBACK So I'm looking a way to force Postgres to ignore this ROLLBACK Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] VACUUM Question
If you really are just inserting, and never updating or deleting, then you will never need to vacuum the table, rather you will just need to ANALYSE the table. If you use autovacuum that is exactly what it will do. As for Reindex, I'm not entirely sure, I don't think you would benefit from reindex because you aren't updating or deleting. Can anyone comment on this? Is is possibile that a table with lots of inserts resulting in lots of page splits etc could ever benifit form REINDEX? Matt We have 2 tables we expect to grow by up to 50,000 rows per day each depending on the customer. In normal operation we will most likely never update or delete from these tables as they are for historical reporting. (Eventually we may but a limit on the amount of data and delete older than X months or such) We intend to create a number of indexes based upon the reporting search criteria. What would the best setup be for VACUUM, ANALYSE, REINDEX. Alot of the infor refers to data hanging around from deletes and updates which in normal course we will not do on these tables? Oisin ---(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] Arrays
Thank you - my eyes aren't what they used to be.
Bob
- Original Message -
From: Joshua D. Drake [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Stephan Szabo [EMAIL PROTECTED]; Tom Lane
[EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org
Sent: Thursday, January 26, 2006 12:20 PM
Subject: Re: [GENERAL] Arrays
Bob Pawley wrote:
ERROR: malformed array literal: {100, 250, 500, DegF)
Well you have a typo:
{100, 250, 500, DegF) is wrong...
{100, 250, 500, DegF} is correct...
Sincerely,
Joshua D. Drake
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Arrays
I missed that - thanks for the help.
Bob
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Stephan Szabo [EMAIL PROTECTED]; Postgresql
pgsql-general@postgresql.org
Sent: Thursday, January 26, 2006 12:12 PM
Subject: Re: [GENERAL] Arrays
Bob Pawley [EMAIL PROTECTED] writes:
ERROR: malformed array literal: {100, 250, 500, DegF)
You wrote a right paren, not a right brace ...
I want to do single dimension arrays.
How did I turn it into multidmensional?
The multiple levels of braces create a multidimensional array.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Arrays
Joshua D. Drake schrieb:
Bob Pawley wrote:
ERROR: malformed array literal: {100, 250, 500, DegF)
Well you have a typo:
{100, 250, 500, DegF) is wrong...
{100, 250, 500, DegF} is correct...
I'd say both are wrong ;)
'{100,250,500,DegF}' could work. But I'm not sure about that
DegF. Since array members are all of the same type - is degf
some integer constant?
Regards
Tino
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] VACUUM Question
On Thu, Jan 26, 2006 at 02:35:42PM -0500, Matthew T. O'Connor wrote: If you really are just inserting, and never updating or deleting, then you will never need to vacuum the table, rather you will just need to ANALYSE the table. That's not quite true; the table must still be vacuumed occasionally to prevent transaction ID wraparound failure, else you risk losing data. http://www.postgresql.org/docs/8.1/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Arrays
The order for the array is Min, Norm, Max, Unit.
I'll probably reorder it with the unit first as every value has a unit.
Bob
- Original Message -
From: Tino Wildenhain [EMAIL PROTECTED]
To: Joshua D. Drake [EMAIL PROTECTED]
Cc: Bob Pawley [EMAIL PROTECTED]; Stephan Szabo
[EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; Postgresql
pgsql-general@postgresql.org
Sent: Thursday, January 26, 2006 12:30 PM
Subject: Re: [GENERAL] Arrays
Joshua D. Drake schrieb:
Bob Pawley wrote:
ERROR: malformed array literal: {100, 250, 500, DegF)
Well you have a typo:
{100, 250, 500, DegF) is wrong...
{100, 250, 500, DegF} is correct...
I'd say both are wrong ;)
'{100,250,500,DegF}' could work. But I'm not sure about that
DegF. Since array members are all of the same type - is degf
some integer constant?
Regards
Tino
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(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] Arrays
Bob Pawley schrieb: The order for the array is Min, Norm, Max, Unit. I'll probably reorder it with the unit first as every value has a unit. I'd rather create/use a custom datatype for your needs. This array stuff seems overly hackish for me. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] xmin system column
Outside of VACUUM FREEZE, is there any way the xmin column in a relation can change, assuming of course the tuple is never updated again? I'm considering using this as a way to identify all tuples modified in the same transaction (in an effort to group them together), and am wondering if there's any way tuples from different transactions could end up with the same xmin value. I've tried both VACUUM and VACUUM FULL on specific tables and neither seem to have an impact, but I haven't done extensive testing against very large tables that have experienced lots of churn. Any input will be greatly appreciated! eric ---(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] VACUUM Question
Michael Fuhr [EMAIL PROTECTED] writes: On Thu, Jan 26, 2006 at 02:35:42PM -0500, Matthew T. O'Connor wrote: If you really are just inserting, and never updating or deleting, then you will never need to vacuum the table, rather you will just need to ANALYSE the table. That's not quite true; the table must still be vacuumed occasionally to prevent transaction ID wraparound failure, Also, somebody made a real good point about rolled-back insertions. Even if the only command you ever apply to the table is INSERT, you could still have dead rows in the table if some of those transactions occasionally roll back. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] xmin system column
Eric B. Ridge [EMAIL PROTECTED] writes: Outside of VACUUM FREEZE, is there any way the xmin column in a relation can change, assuming of course the tuple is never updated again? If the tuple lives long enough, VACUUM will change it to FrozenTransactionId eventually, even without the FREEZE option. I'm considering using this as a way to identify all tuples modified in the same transaction (in an effort to group them together), and am wondering if there's any way tuples from different transactions could end up with the same xmin value. This seems OK as long as the transaction was fairly recent. Note that you will need a fairly restrictive definition of same transaction (no subtransactions). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] xmin system column
On Thu, Jan 26, 2006 at 04:19:34PM -0500, Eric B. Ridge wrote: Outside of VACUUM FREEZE, is there any way the xmin column in a relation can change, assuming of course the tuple is never updated again? I'm considering using this as a way to identify all tuples modified in the same transaction (in an effort to group them together), and am wondering if there's any way tuples from different transactions could end up with the same xmin value. I don't know about tuples from different transactions having the same xmin (aside from 1/BootstrapXID and 2/FrozenXID), but tuples from the same outer transaction could have different xmin values due to savepoints. test= CREATE TABLE foo (x integer); test= BEGIN; test= INSERT INTO foo VALUES (1); test= SAVEPOINT s; test= INSERT INTO foo VALUES (2); test= RELEASE SAVEPOINT s; test= INSERT INTO foo VALUES (3); test= COMMIT; test= SELECT xmin, * FROM foo; xmin | x +--- 424584 | 1 424585 | 2 424584 | 3 (3 rows) Explicit savepoints aren't the only way to get this effect; you'll also see it if the savepoint is implicit, as when trapping errors in a function. -- Michael Fuhr ---(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] Arrays
Our application will be dispersed amongst many users. I want to keep the datbase as generic as possible. Bob - Original Message - From: Tino Wildenhain [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Joshua D. Drake [EMAIL PROTECTED]; Stephan Szabo [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Thursday, January 26, 2006 1:09 PM Subject: Re: [GENERAL] Arrays Bob Pawley schrieb: The order for the array is Min, Norm, Max, Unit. I'll probably reorder it with the unit first as every value has a unit. I'd rather create/use a custom datatype for your needs. This array stuff seems overly hackish for me. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] VACUUM Question
matthew@zeut.net (Matthew T. O'Connor) writes: If you really are just inserting, and never updating or deleting, then you will never need to vacuum the table, rather you will just need to ANALYSE the table. If you use autovacuum that is exactly what it will do. Never is a pretty long time... You need a VACUUM every 2^31 transactions, but since there needs to be such a vacuum for the whole database, that one will do... As for Reindex, I'm not entirely sure, I don't think you would benefit from reindex because you aren't updating or deleting. Can anyone comment on this? Is is possibile that a table with lots of inserts resulting in lots of page splits etc could ever benifit form REINDEX? I could imagine a CLUSTER doing some good, and if that's the case, REINDEX could have some favorable results. But you'd better have a real specific model as to why that would be... -- let name=cbbrowne and tld=ntlug.org in name ^ @ ^ tld;; http://cbbrowne.com/info/spreadsheets.html Oh, boy, virtual memory! Now I'm gonna make myself a really *big* RAMdisk! ---(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] xmin system column
On Jan 26, 2006, at 4:44 PM, Tom Lane wrote: Eric B. Ridge [EMAIL PROTECTED] writes: Outside of VACUUM FREEZE, is there any way the xmin column in a relation can change, assuming of course the tuple is never updated again? If the tuple lives long enough, VACUUM will change it to FrozenTransactionId eventually, even without the FREEZE option. That's what I was afraid of. I've pondering making a grouping column that gets set to xmin via an UPDATE trigger. At least I'd have a constant value that would survive database dumps and reloads. This seems OK as long as the transaction was fairly recent. Note that you will need a fairly restrictive definition of same transaction (no subtransactions). I really need a way to create a unique identifier at the start of a top-level transaction, and be able to use it via triggers and/or column default values in that or its subtransactions. Is there some kind of TopXID magic variable/function that I haven't found in the documentation? eric ---(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] xmin system column
On Jan 26, 2006, at 4:50 PM, Michael Fuhr wrote: test= SELECT xmin, * FROM foo; xmin | x +--- 424584 | 1 424585 | 2 424584 | 3 (3 rows) hmm. Is it possible to grab that first xmin value when the transaction first starts, then I can explicitly use when I need it? eric ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] xmin system column
Eric B. Ridge [EMAIL PROTECTED] writes: That's what I was afraid of. I've pondering making a grouping column that gets set to xmin via an UPDATE trigger. At least I'd have a constant value that would survive database dumps and reloads. That will most assuredly NOT work. You will have XID conflicts if you reload into a different instance of Postgres, or even within the same instance once it's been running long enough to wrap XIDs around. I really need a way to create a unique identifier at the start of a top-level transaction, and be able to use it via triggers and/or column default values in that or its subtransactions. The only thing I can see that would work for you is to nextval() some sequence object at the start of each transaction, and then store its currval() wherever you need it. As long as you store int8 not int4 or xid values, this would be reasonably proof against wraparound issues. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] xmin system column
On Thu, Jan 26, 2006 at 05:05:19PM -0500, Eric B. Ridge wrote: I really need a way to create a unique identifier at the start of a top-level transaction, and be able to use it via triggers and/or column default values in that or its subtransactions. I suppose a sequence is out of the question? Too easy to get it wrong? Is there some kind of TopXID magic variable/function that I haven't found in the documentation? Not in the standard installation, but I think a C function that returns GetTopTransactionId() should work. It's trivial to write and examples have been posted before; search the archives. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] xmin system column
On Thu, Jan 26, 2006 at 03:22:50PM -0700, Michael Fuhr wrote: On Thu, Jan 26, 2006 at 05:05:19PM -0500, Eric B. Ridge wrote: Is there some kind of TopXID magic variable/function that I haven't found in the documentation? Not in the standard installation, but I think a C function that returns GetTopTransactionId() should work. It's trivial to write and examples have been posted before; search the archives. Tom made a good point against using this value: it's not guaranteed to be unique, for example after a dump and reload. I suppose that's a strong reason why the developers haven't provided such easy access to it. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Arrays
I can't imagine test=# create type stat1 as (i1 int, i2 int, i3 int, t1 text); CREATE TYPE test=# create table stest(s1 stat1); CREATE TABLE test=# insert into stest values ((1,1,1,'t')); INSERT 0 1 test=# select * from stest; s1 --- (1,1,1,t) (1 row) being a big issue. You've got to create the tables, you can create the type while you're at it, right? On Thu, 2006-01-26 at 15:59, Bob Pawley wrote: Our application will be dispersed amongst many users. I want to keep the datbase as generic as possible. Bob - Original Message - From: Tino Wildenhain [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Joshua D. Drake [EMAIL PROTECTED]; Stephan Szabo [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Thursday, January 26, 2006 1:09 PM Subject: Re: [GENERAL] Arrays Bob Pawley schrieb: The order for the array is Min, Norm, Max, Unit. I'll probably reorder it with the unit first as every value has a unit. I'd rather create/use a custom datatype for your needs. This array stuff seems overly hackish for me. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] SYNTAX ERROR at or near SQLSTATE
hi,
I found the following function on http://archives.free.net.ph/message/20050613.063258.1a326e27.en.html.
When i run it on PostgreSQL8.0.1 (on fedora core 2), i get the error:
dbm=# select * from excpt_test();
ERROR: syntax error at or near sqlstate at character 133
QUERY:
begin
begin
raise exception 'user exception';
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
end;
CONTEXT: compile of PL/pgSQL function excpt_test near line 5
LINE 6: raise notice 'caught exception % %', sqlstate, sqlerrm;
^
dbm=#
Is there a some configuration parameter i need to set?
Function is as below:
create function excpt_test() returns void as $$
begin
begin
raise exception 'user exception';
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
end; $$ language plpgsql;
Any help will be highly appreciated,
thanks,
vish
On 1/26/06, vishal saberwal [EMAIL PROTECTED] wrote:
hi all,I am using PostgreSQL 8.0.1 on Fedora core 2.My
goal is to create a common Exception handling stored function that
takes Error Constant (as defined in Error Codes document: AppendixA)
and raises a customized exception.
The problem is:(a) How do i catch these Error Constants? I
was unable to use SQLSTATE and SQLERRM, for somehow the database didnt
understand them.(b) How do i catch these from OTHERS exception and pass it to the Common Exception Handling function?
-- In SP fucntion, error could be in Inserts, divide by zero pr updates.-- The errors could be because a table is locked, or some other reasons.CREATE or replace SP(int) returns int as $$DECLARE res int;
BEGIN res:=0; insert into tbl values ('a','b','c'); res:=2/$1; update tbl set colA='x' where colA='a'; return res;EXCEPTION WHEN OTHERS THEN Common_Exception_Handling_Function(Error_Constant);
END;$$ language plpgsql;-- This common function will be called from EXCEPTION blocks of all Stored functions (around 300).-- All error codes will be defined in this common function and will raise a customized Exception message.
CREATE or replace Common_Exception_Handling_Function(varchar) returns VOID as $$BEGINif $1='DIVISION_BY_ZERO' then RAISE EXCEPTION 'DIVISION BY ZERO';elsif $1='SYNTAX_ERROR' then RAISE EXCEPTION 'SYNTAX ERROR';
. . . . . . . . . end if;END;
$$ language plpgsql;
Re: [GENERAL] Arrays
On Jan 27, 2006, at 4:41 , Eric E wrote: I second that, and I'd love to have someone clarify the appropriate time to use arrays vs. more columns or an referenced tabled. I've always found that confusing. I would only use arrays if the natural data type of the data is an array, such as some math applications. In these situations, for the most part you are not going to be doing a lot of operations on elements of the array, but rather the array value as a whole. While PostgreSQL does have array support, PostgreSQL is a relational database and as such is designed to handle relational data and is best at handling data that is stored relationally, i.e., in tables and columns. Michael Glaesemann grzm myrealbox 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
Re: [GENERAL] SYNTAX ERROR at or near SQLSTATE
On Thu, Jan 26, 2006 at 03:02:46PM -0800, vishal saberwal wrote: When i run it on PostgreSQL8.0.1 (on fedora core 2), i get the error: dbm=# select * from excpt_test(); ERROR: syntax error at or near sqlstate at character 133 SQLSTATE and SQLERRM are new in 8.1; they're not available in earlier versions unless you've applied some patch. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SYNTAX ERROR at or near SQLSTATE
oh thanks,i didnt know that,highly appreciate your help and quick response ...vishOn 1/26/06, Michael Fuhr [EMAIL PROTECTED] wrote:On Thu, Jan 26, 2006 at 03:02:46PM -0800, vishal saberwal wrote: When i run it on PostgreSQL8.0.1 (on fedora core 2), i get the error: dbm=# select * from excpt_test(); ERROR:syntax error at or near sqlstate at character 133SQLSTATE and SQLERRM are new in 8.1; they're not available in earlierversions unless you've applied some patch.--Michael Fuhr
Re: [GENERAL] xmin system column
On Jan 26, 2006, at 5:22 PM, Michael Fuhr wrote: I suppose a sequence is out of the question? Too easy to get it wrong? Well, I just wanted to avoid embedding this idea into my application. Would rather Postgres take care of it for me. Not in the standard installation, but I think a C function that returns GetTopTransactionId() should work. It's trivial to write and examples have been posted before; search the archives. Hmm. I also see GetCurrentTransactionStartTimestamp() in xact.h. That could work as a mostly-unique identifier. Its value could survive dumps (assuming clock is set correctly!) and a little wrapper around it could be used by triggers or by default column values. Futher reading in xact.c says: /* * This is the value of now(), ie, the transaction start time. * This does not change as we enter and exit subtransactions, so we don't * keep it inside the TransactionState stack. */ static TimestampTz xactStartTimestamp; long pause hahaha, *blush*. I could just use now(), right? pg8.1 docs say that now()/CURRENT_TIMESTAMP return the start time of the current transaction; their values do not change during the transaction. I could use a composite of (now(), GetTopTransctionId()) to assume batch uniqueness. eric ---(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] Arrays
Thanks Scott - I'll give this a try. Bob - Original Message - From: Scott Marlowe [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Tino Wildenhain [EMAIL PROTECTED]; Joshua D. Drake [EMAIL PROTECTED]; Stephan Szabo [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Thursday, January 26, 2006 2:35 PM Subject: Re: [GENERAL] Arrays I can't imagine test=# create type stat1 as (i1 int, i2 int, i3 int, t1 text); CREATE TYPE test=# create table stest(s1 stat1); CREATE TABLE test=# insert into stest values ((1,1,1,'t')); INSERT 0 1 test=# select * from stest; s1 --- (1,1,1,t) (1 row) being a big issue. You've got to create the tables, you can create the type while you're at it, right? On Thu, 2006-01-26 at 15:59, Bob Pawley wrote: Our application will be dispersed amongst many users. I want to keep the datbase as generic as possible. Bob - Original Message - From: Tino Wildenhain [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Joshua D. Drake [EMAIL PROTECTED]; Stephan Szabo [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Thursday, January 26, 2006 1:09 PM Subject: Re: [GENERAL] Arrays Bob Pawley schrieb: The order for the array is Min, Norm, Max, Unit. I'll probably reorder it with the unit first as every value has a unit. I'd rather create/use a custom datatype for your needs. This array stuff seems overly hackish for me. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
[GENERAL] What Could Cause This Behavior?
Now that I have postgres-8.1.2 properly configured and running I wanted to create a new database for an application. Postgres would not let me -- as a user -- create the database, so I su'd to 'postgres'. As user 'postgres' I could invoke psql and tried to create the database with the command, 'create database contacts username=rshepard'. That failed with no error message. I exited from psql and user postgres and ran the command 'createdb contacts' as me. That worked. Also, I (as a listed superuser of postgres) cannot run 'psql' and get a prompt. I'm told, 'psql: FATAL: database rshepard does not exist'. But, as user postgres I can type 'psql' and get into the interactive mode. In earlier versions I did not have this dichotomy. I could do everything as a superuser and did not need to be logged in as user postgres. Any ideas what's going on? Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] What Could Cause This Behavior?
On Jan 27, 2006, at 10:54 , Rich Shepard wrote: As user 'postgres' I could invoke psql and tried to create the database with the command, 'create database contacts username=rshepard'. That failed with no error message. Could you provide a sample session, from login to psql to logout? The added information may give someone on the list more insight into what's going wrong. I exited from psql and user postgres and ran the command 'createdb contacts' as me. That worked. Also, I (as a listed superuser of postgres) cannot run 'psql' and get a prompt. I'm told, 'psql: FATAL: database rshepard does not exist'. But, as user postgres I can type 'psql' and get into the interactive mode. Without adding a database to connect to, psql defaults to connect to a database with the same name as the user. As of PostgreSQL 8.1, there is a default database named postgres. When use run psql as user postgres, you should be connecting to the postgres database. If you specify the database, such as the examples below, you should be able to connect, or at least giving you another error which will lead you to find other things that may need tweaking. psql postgres psql -d postgres You may also want to read the release notes (in the documentation or on the website) to see if there are other changes that may be unknowingly affecting you. Hope this helps. Michael Glaesemann grzm myrealbox com ---(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] What Could Cause This Behavior?
On Thu, 26 Jan 2006, Rich Shepard wrote: Now that I have postgres-8.1.2 properly configured and running I wanted to create a new database for an application. Postgres would not let me -- as a user -- create the database, so I su'd to 'postgres'. As user 'postgres' I could invoke psql and tried to create the database with the command, 'create database contacts username=rshepard'. That failed with no error message. I exited from psql and user postgres and ran the Did you put a semicolon at the end of the command? I get a ERROR: syntax error at or near username at character 26 LINE 1: create database contacts username=sszabo ^ from 8.2 devel. I would guess owner= is what you were looking for, but I'm not sure. Also, I (as a listed superuser of postgres) cannot run 'psql' and get a prompt. I'm told, 'psql: FATAL: database rshepard does not exist'. But, as user postgres I can type 'psql' and get into the interactive mode. psql defaults to connecting to a database of the same name as the user when no database name is given. Do you get the same if you explicitly try to connect to a database? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What Could Cause This Behavior?
On Fri, 27 Jan 2006, Michael Glaesemann wrote: Could you provide a sample session, from login to psql to logout? The added information may give someone on the list more insight into what's going wrong. Michael, Sure: [EMAIL PROTECTED] ~]$ su postgres Password: [EMAIL PROTECTED]:/home/rshepard$ psql Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# create database testcase postgres-# \q [EMAIL PROTECTED]:/home/rshepard$ psql -l List of databases Name | Owner| Encoding -++--- aesi| sql-ledger | LATIN1 contacts| rshepard | SQL_ASCII eiabusiness | rshepard | SQL_ASCII postgres| postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII test| postgres | SQL_ASCII webcollab | rshepard | SQL_ASCII (8 rows) [EMAIL PROTECTED]:/home/rshepard$ exit exit [EMAIL PROTECTED] ~]$ Without adding a database to connect to, psql defaults to connect to a database with the same name as the user. As of PostgreSQL 8.1, there is a default database named postgres. When use run psql as user postgres, you should be connecting to the postgres database. Ah! I see. You may also want to read the release notes (in the documentation or on the website) to see if there are other changes that may be unknowingly affecting you. I will certainly do this. I was aware of changes and that's why I bought the Douglas/Douglas book. Regardless, I'll read the release notes and my pdf copy of the manual. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] What Could Cause This Behavior?
On Thu, 26 Jan 2006, Stephan Szabo wrote: Did you put a semicolon at the end of the command? I get a ERROR: syntax error at or near username at character 26 LINE 1: create database contacts username=sszabo Stephan, No, I did not put a semicolon there. I also received no error message, just the prompt. See the sample session I just posted in response to Michael's message. psql defaults to connecting to a database of the same name as the user when no database name is given. I now understand this. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What Could Cause This Behavior?
On Jan 27, 2006, at 11:22 , Rich Shepard wrote: postgres=# create database testcase postgres-# \q I think Stephan's right: you need to end the statement with a semi- colon (or \g in psql). Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What Could Cause This Behavior?
On Thursday 26 January 2006 19:24, Rich Shepard wrote: On Thu, 26 Jan 2006, Stephan Szabo wrote: Did you put a semicolon at the end of the command? I get a ERROR: syntax error at or near username at character 26 LINE 1: create database contacts username=sszabo Stephan, No, I did not put a semicolon there. I also received no error message, just the prompt. See the sample session I just posted in response to Michael's message. postgres=# create database testcase postgres-# \q They are not the same prompt. Notice the - as opposed to the = symbol. It was waiting for you to finish your statement (because you can use several lines for involved SQL statements). \q then just ended your session. Andy ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] What Could Cause This Behavior?
On Thu, 26 Jan 2006, Rich Shepard wrote: On Thu, 26 Jan 2006, Stephan Szabo wrote: Did you put a semicolon at the end of the command? I get a ERROR: syntax error at or near username at character 26 LINE 1: create database contacts username=sszabo Stephan, No, I did not put a semicolon there. I also received no error message, just the prompt. See the sample session I just posted in response to Michael's message. Without a semicolon, you didn't tell psql that you were done with the command. The cursor changes when the command is incomplete: postgres=# create database testcase postgres-# And then when you quit, it quit without having run the incomplete statement. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What Could Cause This Behavior?
On Fri, 27 Jan 2006, Michael Glaesemann wrote: I think Stephan's right: you need to end the statement with a semi-colon (or \g in psql). Sigh. Of course. By the time I got to this point I had put in a rather full day and was not thinking clearly. Apologies all around, Rich -- Richard B. Shepard, Ph.D. | Author of Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] VACUUM Question
Also, somebody made a real good point about rolled-back insertions. Even if the only command you ever apply to the table is INSERT, you could still have dead rows in the table if some of those transactions occasionally roll back. hmm... That's true. I don't think autovacuum doesn't anything to account for the concept of rolledback inserts. I suppose in most real world situations that number is going to be small enough to be ignored, but not in all cases. Is there anyway for the stats system to report the information about rolledback inserts? In fact autovacuum probably has a similar deficiency for rolled back deletes but not a rolled back update. Anyone think this is enough of an issue that it needs more attention? Matt ---(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] Hey!!!
On Thu, Jan 26, 2006 at 18:24:36 +0100, LaroG [EMAIL PROTECTED] wrote: Hey. I am new here. I’m from Poland:-) I have one question (I don’t now if this is the right group for this question and … if my English is enough good… :P): Are the developers going to implement some system trigger like in ORACLE i.e. “on login” trigger ?? Postgres has triggers. There aren't triggers that fire on login or startup though. ---(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] Suggestions on storing and retrieving geocode data
On Thu, Jan 26, 2006 at 12:55:46 -0500, George Woodring [EMAIL PROTECTED] wrote: I am looking for suggestions on storing and retrieving geocode information. My application currently stores 2 columns (lat, long) as numeric and I have a btree index on them. This works fine for the current set of data, but as it expands I know it will become an issue. I am looking at changing the index to an rtree, but I did not know if it is better to create a point column or if I could use the existing lat/long columns. The query will always be to select points inside a box. If you don't want to use PostGIS, you could also use contrib/earthdistance. The 'earth' domain uses the cube data type to represent points on the surface of the earth (modelled as a perfect sphere). There is a GIST index available for cube, so searches should be reasonably fast. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
