Re: [GENERAL] question on trigger
On Saturday, February 11, 2012 09:22:44 AM mgo...@isstrucksoftware.net wrote: I will have several triggers which will make sure that the data in some columns in in uppercase format. For insert it is a no brainer, however for updates, it is better to check and see if the new values is the same and only update if different? We are not talking about a huge number of transactions. In one day we might have at the most 3000 transactions. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Michael: I am by no means an expert on database performance; however, it seems to me that you would waste more cycles checking if things changed than simply forcing upper case for all to which this criteria applies. The whole record is going to be written to the database anyway. Terry Lee Tucker Office: 336-372-6812
Re: [GENERAL] running out of oids
On Wednesday, August 03, 2011 04:24:32 PM Joshua D. Drake wrote: On 08/03/2011 12:41 PM, Geoffrey Myers wrote: Am I correct in assuming that the 'running out of oids' issue was resolved with a design change within Postgresql? Yes, many, many, many years ago. The only way to encounter the problem now is through user error, e.g; don't use WITH OIDS when creating a table. Joshua D. Drake Great! Geoff works with me and we don't use OIDs at all. -- The Tuckers Home: 336-372-5432
[GENERAL] Marking a Column for Special Use
Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? I'm using PostgreSQL 8.3 on Red Hat. TIA -- Terry Tucker Office: 336-372-6812
Re: [GENERAL] Marking a Column for Special Use
On Tuesday, July 12, 2011 11:47:33 AM Andrew Sullivan wrote: On Tue, Jul 12, 2011 at 11:25:24AM -0400, Terry Lee Tucker wrote: Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? Sounds like you want a view, I think. A Yes, that is a good idea. Thanks... -- The Tucker Family Home: 336-372-5432 Mobile: 336-404-6987
Re: [GENERAL] Marking a Column for Special Use
On Tuesday, July 12, 2011 11:33:34 AM David Johnston wrote: Can you make use of COMMENT ON .? From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Terry Lee Tucker Sent: Tuesday, July 12, 2011 11:25 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Marking a Column for Special Use Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? I'm using PostgreSQL 8.3 on Red Hat. TIA That's a good idea. Thanks... -- The Tucker Family Home: 336-372-5432 Mobile: 336-404-6987
[GENERAL] Views - Under the Hood
Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views - Under the Hood
On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote: On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker te...@chosen-ones.org wrote: Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... In pgsql a view is actually a rule that fires off the original query for you. So it's a simple wrapper, and is the same, for the most part, as simply typing in the original query again. So, it's pretty simple, and there's no real overhead to worry about. Thank you Scott! This is exactly what I needed to know... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] varchar lengths
On Tuesday, September 21, 2010 07:23:45 Massa, Harald Armin wrote: I recommend to use TEXT as type for that kind of columns. 99 out of 100 theories about this value will never be longer then xx characters fail in the long run. And text, limited only by PostgreSQLs limits, performs as good or better then varchar(length_limit) The time of we only can allow n chars for first name for performance reasons have gone by, together with walkmen and VHS. Harald Also, if you are absolutely set on a constraint on the length of the text, you can use a trigger for this and when the constraint changes, and it will, you simply modify the trigger. -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on trigger data visibility
On Monday, August 30, 2010 07:20:14 Maurice Gittens wrote: Hi, Assume tablex, tabley and tablez are correctly populated in my database. My purpose is to enforce referential integrity between a column in the tablex (the child) and a column in tablez (the parent). Since normal foreign keys do not give me this functionality, I decide to write a trigger. My trigger function looks something like: CREATE OR REPLACE FUNCTION trigger_on_tablex() RETURNS trigger AS $$ BEGIN PERFORM 1 FROM tablex AS tab_x INNER JOIN tabley AS tab_y ON tab_x.gp = tab_y.id INNER JOIN tablez AS tab_z ON tab_y.ml = tab_z.id WHERE tab_x.name = tab_z.name; IF NOT FOUND THEN RAISE EXCEPTION 'constraint violated '; END IF; END;$$ LANGUAGE plpgsql; CREATE TRIGGER mytrigger AFTER INSERT ON tablex FOR EACH STATEMENT EXECUTE PROCEDURE trigger_on_tablex(); My problem is that no matter what I insert into tablex, the exception is always raised. So, it seems that even though my trigger is defined as AFTER INSERT FOR EACH STATEMENT, the inserted row does not appear to be included in the join. So, now to my question: Should, as a matter of principle, statement level triggers not see rows recently inserted into the tablex? Thanks, Maurice They do see those rows. Are you sure that the inner join with tab_Y is not causing the problem? Just a guess... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locking Down a Database
Greetings: I occasionally find the need to perform some maintenance on one or more of thirteen different databases. Typically, due to the interaction between the databases, I need to lock down the databases for a short period of time so that no updates are being performed anywhere. I do not want to shut down the postmasters as it is a clustered environment and would rather just leave that alone. Also, since this is a 24 hour shop, it would be good if people could still query, but not change any data. All this work is done at times like 03:00 in the morning and the number of people working at that time is limited to five or six. Is there some easy way of doing this without having people log out of the application? TIA... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What does INSERT 0 1 mean?
On Wednesday 14 October 2009 11:05, [.::MDT::.] wrote: Hi, I can't find what does INSERT 0 1 mean. 1 stands for the number of the records added to the table, as far as I understood, but what about the 0? Thank you very much. -- View this message in context: http://www.nabble.com/What-does-%22INSERT-0-1%22-mean--tp25892901p25892901. html Sent from the PostgreSQL - general mailing list archive at Nabble.com. It represents the OID, which PostgreSQL, in earlier versions, generated by default. Later versions do not do this by default. The zero indicates that you are not generating OID's for that table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Configuration Question
Greetings: Is there a way to get hold of an environment variable such that it can be referenced in postgresql.conf? In particular, I'd like to be able to point dynamic_library_path to an environment variable defined at the system level as in dynamic_library_path = '$SOURCE:$libdir'. master=# select version(); version PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) TIA -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configuration Question
On Tuesday 18 August 2009 09:28, Martin Gainty wrote: v8.3 FAQ_Solaris To point it to the right location, set the LD_LIBRARY_PATH environment variable, e.g., LD_LIBRARY_PATH=/usr/sfw/lib:/opt/sfw/lib:/usr/local/lib export LD_LIBRARY_PATH and restart configure. You will also have to keep this setting whenever you run any of the installed PostgreSQL programs. Alternatively, set the environment variable LD_RUN_PATH. See the ld(1) man page for more information. either LD_LIBRARY_PATH or LD_RUN_PATH should contain binary folder Martin Gainty OK. Thanks for the help. I'll give this a try. From: te...@chosen-ones.org To: pgsql-general@postgresql.org Subject: [GENERAL] Configuration Question Date: Tue, 18 Aug 2009 06:32:25 -0400 Greetings: Is there a way to get hold of an environment variable such that it can be referenced in postgresql.conf? In particular, I'd like to be able to point dynamic_library_path to an environment variable defined at the system level as in dynamic_library_path = '$SOURCE:$libdir'. master=# select version(); version - --- PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) TIA -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows Live: Keep your friends up to date with what you do online. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:O N:WL:en-US:SI_SB_online:082009 -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C Function Question
On Tuesday 28 July 2009 03:22, Albe Laurenz wrote: Terry Lee Tucker wrote: Does anyone know if a function written in C and linked into the backend in a shared library with a statically declared structure, maintain that data for the life of the backend process such that, when the function is called again, the structure data is intact? Thanks for any insight anyone can give... Yes, that should work. Yours, Laurenz Albe Thanks for the reply Laurenze. I put together a small test case scenario and, indeed, it does work as I had hoped and as you indicated. Thanks for the help... -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C Function Question
Greetings: Does anyone know if a function written in C and linked into the backend in a shared library with a statically declared structure, maintain that data for the life of the backend process such that, when the function is called again, the structure data is intact? Thanks for any insight anyone can give... -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] warm standby with WAL shipping
On Wednesday 03 June 2009 15:26, Greg Smith wrote: On Wed, 3 Jun 2009, Geoffrey wrote: My assumption was that since pg_standby does not have the scp/rsync functionality, I would have to either modify it, change the way we do things, or 'reinvent' a little different wheel. There are three things to setup here: 1) archive_command on the master 2) Transport between master and standby(s) 3) recovery_command. pg_standby is the reference implementation here. You can combine (1) and (2) by putting some sort of network copy command into the archive_command, but better practice here (and probably required practice in your case) is to write a script that does that instead. That's the part you need to worry about. There is no need for you to reinvent (3) just because you have different requirements than most for (2). As you've noticed, pg_standby doesn't actually do the network transport part, and that also means that it's decoupled from what choices you make for that layer. Focus on writing scripts to atomically copy the files into the right destination on the standbys, and pg_standby will take care of applying the shipped log files to the database. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD Our circumstance here is that we will be feeding multiple warm stand-by servers; one local and the rest remote, that is, at least one in other state and possibly another in another city. We didn't want the WAL shipping process to fail because one of the nodes might be down. To circumvent that, we thought the best approach to take was to pump the WAL logs to a central machine on-site, and have the warm stand-by servers pick up their files from the central storage device. This is why we were thinking about changing pg_standby. Thanks for all the help... -- Work: 1-336-372-6812 Cell: 1-336-404-6987 email: te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] warm standby with WAL shipping
On Wednesday 03 June 2009 17:11, Joshua D. Drake wrote: On Wed, 2009-06-03 at 16:45 -0400, Terry Lee Tucker wrote: -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD Our circumstance here is that we will be feeding multiple warm stand-by servers; one local and the rest remote, that is, at least one in other state and possibly another in another city. We didn't want the WAL shipping process to fail because one of the nodes might be down. To circumvent that, we thought the best approach to take was to pump the WAL logs to a central machine on-site, and have the warm stand-by servers pick up their files from the central storage device. This is why we were thinking about changing pg_standby. PITRTools 1.2 has queuing which can deal with this problem. I don't know if walmgr does. We will look into PITRTools then. I'm nervous about re-inventing the wheel as has been pointed out. Thanks for the input... Joshua D. Drake Thanks for all the help... -- Work: 1-336-372-6812 Cell: 1-336-404-6987 email: te...@chosen-ones.org -- Work: 1-336-372-6812 Cell: 1-336-404-6987 email: te...@leetuckert.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Two Questions Re: Warm Backup
On Saturday 02 May 2009 13:08, Daniel Verite wrote: Terry Lee Tucker writes Q1: Can we set up a scenario where there is more that one warm standby? Yes. But you'll have to consider what you want to happen when one standby is correctly receiving the WAL files and another is not, because the archive_command has to either fail or succeed, it can't return half-done. You may need to provide your own layer that retry pushing local copies of WAL files to the remote nodes and deleting them only when they've been received by every standby server. Q2: Am I correct in assuming that ALL changes to any of the production schema will be written to the warm standby? For example, if I drop a constraint in production I assume the same will occur on the warm standby. If I create and drop a table in production, I assume it will occur on the warm standby. It will, all DDL is replicated. Best regards, Daniel: Thanks for the reply. I later read the answer to the first question in the docs. I expected the answer to number 2 to be affirmative, but just wanted to make sure. Thanks for the help... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of OHL 2251 Jesse Jewell Pkwy Gainesville, GA 30501 tel: (336) 372-6812 cell: (336) 404-6987 te...@turbocorp.com www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Two Questions Re: Warm Backup
Greetings: We are researching implementing a warm backup solution for our existing databases. We have a two node cluster running RH which are connected to a SAN. There is a total of 11 database clusters with the two node linux cluster balancing the load. At the moment, we are not doing any WAL archiving. exp=# select version(); version PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) (1 row) Q1: Can we set up a scenario where there is more that one warm standby? I want a warm standby locally and one that is hundreds of miles away connected with a T4 data circuit. Q2: Am I correct in assuming that ALL changes to any of the production schema will be written to the warm standby? For example, if I drop a constraint in production I assume the same will occur on the warm standby. If I create and drop a table in production, I assume it will occur on the warm standby. TIA -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] open transaction?
On Monday 29 December 2008 06:48, blackwater dev wrote: I just logged into postgres from the command line and did: begin: select blah; select blah; \q Without thinking I closed by connection before committing or rolling back my transaction. Did postgres handle this for me? How do I see if the transaction is still open? Thanks! If you don't commit, it is rolled back when you exit. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of OHL 2251 Jesse Jewell Pkwy Gainesville, GA 30501 tel: (336) 372-6821 cell: (336) 404-6987 te...@turbocorp.com www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger disable for table
On Thursday 02 October 2008 06:26, Frank Durstewitz wrote: Hi list. A fairly complex problem: - Table A has a before insert/update trigger, which look up table B and use field C from table B. - Table B has a after insert/update trigger, which update table A with field C. The update on table B triggers the trigger from table A, so the same thing is done twice. Can one avoid to fire the trigger on table A, when updates are made to table B, because i know all fields already and can build the update sql for table A, so no need to call the trigger on table A? My idea is to have it like ... IF NEW.published = TRUE THEN ALTER TABLE a DISABLE TRIGGER mytrigger USER; (do update here) ALTER TABLE a ENABLE TRIGGER mytrigger USER; ... Will a construct like this disable the trigger only inside the this function or is the trigger disabled outside (visiblility?) the function, too, which is unacceptable. (Hmm, sounds very confused, and so i am...) A helping hand on this topic is well accepted :-) Thanks, Frank This should work but, if I remember correctly, it will lock table A. If that is OK in your environment, then go for it. It is not in ours. We have a table that we called override and when we want to override the firing of a certain trigger, we put code in that trigger that checks the override table for the existence of a record matching the trigger name and some other criteria. If we find it, we simply return from the trigger at that point. The trigger on table B would be responsible for inserting the record into override and then deleting the record after the update is done. We've build wrapper functions to make the inserts and deletes to override easy. HTH... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ENABLE / DISABLE ALL TRIGGERS IN DATABASE
On Wednesday 27 August 2008 04:24, Teemu Juntunen wrote: Hi, I think this has been asked before, but I can't find the answer from arcive nor google. How to disable/enable all the riggers in a database? I have problem with disabled triggers after database restore. If there is no simple way, could this be made in a function where you find the table names and construct the needed commands in strings. If so, how to get all the tablenames from database? Best regards and thanks! Teemu Juntunen You can accomplish this by manipulating the run time parameter, session_replication_role. For example, from within your psql session: SET SESSION session_replication_role = replica; This will prevent all triggers from firing for the entire session except those defined as replica. We use this all the time. HTH... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dumping/Restoring with constraints?
On Wednesday 27 August 2008 09:19, Phoenix Kiula wrote: Hello. I have a database dump. With data and schema, which includes all the constraints and rules. But it seems the pg_dumpall command does not copy data in such a way that foreign keys are satisfied upon restoring. Because tables are inter-related, importing them keep giving errors and eventually no data is imported. Neither pg_dumpall nor pg_restore seems to have a without constraints or delay constraints check type command. What am I missing? Thanks for any advice. We have all sorts of constraints and foreign keys and we have never had any problem with pg_restore related to dumping such that foreign keys are satisfied. You must have data already in the database that violates the restraints. You can restore in two phases; that is, by restoring the schema, and then the data using --disable-triggers. I'm assuming you are doing a binary dump. See the man page for pg_restore. HTH -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dumping/Restoring with constraints?
On Wednesday 27 August 2008 09:36, Phoenix Kiula wrote: On Wed, Aug 27, 2008 at 9:29 PM, Terry Lee Tucker [EMAIL PROTECTED] wrote: We have all sorts of constraints and foreign keys and we have never had any problem with pg_restore related to dumping such that foreign keys are satisfied. You must have data already in the database that violates the restraints. You can restore in two phases; that is, by restoring the schema, and then the data using --disable-triggers. I'm assuming you are doing a binary dump. See the man page for pg_restore. Thanks for this. I don't have any foreign key violations in my existing database. I think the violation is happening because upon restoring the table that is being populated checks in another table that doesn't yet have data. I am not using pg_restore. I am just using psql --file=FILENAME syntax. Is that an issue? The errors you are having, then, must be related to your own trigger code. It sounds like you will need to prevent those triggers from firing and the only way I know how to accomplish that is to do a binary dump and then use pg_restore as I indicated earlier. There is no way to disable triggers in your method referenced above. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Script to export all privileges to csv or similar
On Friday 22 August 2008 05:15, Anton Melser wrote: Hi, Does anyone know of a script/tool that allows one to export all users with all privileges? I realise I could construct a query to do it but google turned up nothing and if someone else has done the good work... Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... Have you looked at pg_dumpall -g ? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Generate SQL Statements
Greetings: I was wondering if anyone knows of a third party product that will generate SQL statements for creating existing tables. We have to provide table definition statements for out parent company. Any ideas? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generate SQL Statements
On Tuesday 03 June 2008 20:10, Steve Crawford wrote: Terry Lee Tucker wrote: Greetings: I was wondering if anyone knows of a third party product that will generate SQL statements for creating existing tables. We have to provide table definition statements for out parent company. Any ideas? Why 3rd party? How about: pg_dump --schema-only -t table_name... ? Alternately, roll-your-own using the system tables. A good place to start is by running psql with the --echo-queries option to see the queries it runs behind the scenes. You can read the queries for things like \d+ tablename then modify them to suit. Cheers, Steve Now why didn't I think of that :o/ Thanks for the help... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deny creation of tables for a user
On Wednesday 23 April 2008 06:46, Pascal Cohen wrote: Hello I am playing with security in Postgres And I would like to have a database that can be managed by a given user that could do almost anything but I would also have a user that can just handle what is created. I mean she could insert, update delete rows but not create tables. I did not find a way to revoke such thing. Is it possible ? Thanks! Have you looked at GRANT? http://www.postgresql.org/docs/8.3/interactive/sql-grant.html -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup setup
On Wednesday 23 April 2008 11:14, Gabor Siklos wrote: I need to back up our database off-site for disaster recovery. If I just back up the entire database data directory (i.e. /var/lib/pgsql/data) will I be able to restore from there? Or should I instead just dump the data, using pg_dump, and back up the dump? The advantage of the first method would be that I would not have to wait for pg_dump (it takes quite long on our 60G+ database) and would just be able to configure the backup agent to monitor the data directory and do differential backups of the files there every hour or so. Your suggestions are much appreciated! -Gabor I would use pg_dump. It will ensure that you get a complete set of data and not something half written. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to retore a pg_dumpall dump?
On Wednesday 16 April 2008 13:38, wasenbr wrote: Hello, how can I restore a pg_dumpall dump? Cleiton http://www.postgresql.org/docs/8.3/interactive/backup.html -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Duplicate Symbols - Compiler Warnings
Greetings: We are converting from 7.4.19 to 8.3. While compiling our application using version 8.3, I have noticed the following warning from the compiler when compiling perlAPI.c: gcc -ggdb -Wall -Wmissing-prototypes -Wmissing-declarations -fforce-addr -DUNIX-DSTDERR_MSG -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe-I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -I/usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE -I/usr/X11R6/include -I/usr/local/pg83/include -I/usr/local/pg83/include/server -I/esc/logpro/4.0/pgrnd/prog -c utility/perlAPI.c In file included from /usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE/perl.h:37, from /esc/logpro/4.0/pgrnd/prog/utility/perlAPI.h:10, from utility/perlAPI.c:11: /usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE/config.h:2183:1: warning: _redefined In file included from /usr/local/pg83/include/postgresql/server/postgres.h:48, from /esc/logpro/4.0/pgrnd/prog/logpro.h:35, from /esc/logpro/4.0/pgrnd/prog/utility/perlAPI.h:8, from utility/perlAPI.c:11: /usr/local/pg83/include/postgresql/server/c.h:94:1: warning: this is the location of the previous definition In config.h, the symbol in question on line 2183 is: #define _(args) args In c.h (postgres) the offending symbol on line 94 is: #define _(x) gettext((x)) I expect this to cause random core dumps when putting the application into production and it concerns me greatly. So, my question is, do you see this as a serious problem and, if so, what shall I do to resolve it? By the way, this problem does not exist when compiling with 7.4.19. TIA -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Duplicate Symbols - Compiler Warnings
Didn't get any nibbles on this one. Can anybody provide any insight on this? Thanks... On Thursday 17 April 2008 10:03, Terry Lee Tucker wrote: Greetings: We are converting from 7.4.19 to 8.3. While compiling our application using version 8.3, I have noticed the following warning from the compiler when compiling perlAPI.c: gcc -ggdb -Wall -Wmissing-prototypes -Wmissing-declarations -fforce-addr -DUNIX-DSTDERR_MSG -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe-I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -I/usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE -I/usr/X11R6/include -I/usr/local/pg83/include -I/usr/local/pg83/include/server -I/esc/logpro/4.0/pgrnd/prog -c utility/perlAPI.c In file included from /usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE/perl.h:37, from /esc/logpro/4.0/pgrnd/prog/utility/perlAPI.h:10, from utility/perlAPI.c:11: /usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE/config.h:2183:1: warning: _redefined In file included from /usr/local/pg83/include/postgresql/server/postgres.h:48, from /esc/logpro/4.0/pgrnd/prog/logpro.h:35, from /esc/logpro/4.0/pgrnd/prog/utility/perlAPI.h:8, from utility/perlAPI.c:11: /usr/local/pg83/include/postgresql/server/c.h:94:1: warning: this is the location of the previous definition In config.h, the symbol in question on line 2183 is: #define _(args) args In c.h (postgres) the offending symbol on line 94 is: #define _(x) gettext((x)) I expect this to cause random core dumps when putting the application into production and it concerns me greatly. So, my question is, do you see this as a serious problem and, if so, what shall I do to resolve it? By the way, this problem does not exist when compiling with 7.4.19. TIA -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Duplicate Symbols - Compiler Warnings
On Thursday 17 April 2008 19:32, Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] writes: Didn't get any nibbles on this one. Can anybody provide any insight on this? You're complaining in the wrong place ... I dunno what perlAPI.c is, but it's not part of Postgres. FWIW, plperl.c seems to work around the issue this way: #include postgres.h /* Defined by Perl */ #undef _ // include Perl headers here I'm not sure that's entirely the right solution, because Perl's _ macro is 100% useless in any project that considers ANSI C a prerequisite, whereas ours could be useful ... but right now plperl doesn't have any localization so it doesn't need the _ macro anyway. regards, tom lane Tom, Thanks for the reply. I've implemented your suggestion and the warning goes away. perlAPI.c is our own source file of functions which allows access to the Perl interpreter which is linked in with the application. We use this to access various Perl hashes that configure and control the system. We'll do some serious testing to see if I've broken anything. As usual, thanks for your invaluable assistance... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] session_replication_role
Version 8.3.1: Is there a distinction between ORIGIN and LOCAL as related to session_replication_role, and if so, what is it? I am unable to understand from the documentation any distinction between the two settings. TIA -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] session_replication_role
On Tuesday 15 April 2008 14:26, Chris Browne wrote: [EMAIL PROTECTED] (Terry Lee Tucker) writes: Is there a distinction between ORIGIN and LOCAL as related to session_replication_role, and if so, what is it? I am unable to understand from the documentation any distinction between the two settings. The intent is that a system that is the origin for replication changes (e.g. - a database where you'll be collecting INSERT/UPDATE/DELETE information to replicate elsewhere) would run in the origin role, normally. The distinction from a practical perspective will take place when stored functions that implement replication stuff detect what role the system is in, and may behave differently. -- Thanks for the reply. I was unable to detect any change in trigger operation when setting the variable to origin or local. I understand that you are saying that the distinction only exists if my code is written to operate differently based on the setting. Thanks for the input... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 14:56, Greg Sabino Mullane wrote: I see the following in the documentation for pg_trigger related to tgenabled: Controls in which session_replication_role modes the trigger fires. O = trigger fires in origin and local modes, D = trigger is disabled, R = trigger fires in replica mode, A = trigger fires always. My question is: When tgenabled is set to D, how does that setting interact with session_replication_role and, is there a way to use tgenabled with a setting of D to prevent a particular trigger from firing. Using ALTER TABLE to disable the trigger won't work because the whole table is locked during the transaction and I only want the disabled trigger to apply to the current transaction in the current session. If you simply want to ignore all triggers, just use a 'replica' role. When done, switch it back to 'origin' (or your default, which should be origin). If you want to fire only a single trigger, set it to 'always' mode and switch to 'replica'. If you want to fire all triggers *except* a certain trigger, set that trigger to replica mode and leave the session_replication_mode unchanged (default/origin). You should be using ALTER TABLE and not worry about changing tgenabled yourself, in case it wasn't obvious. You should be able to make permanent changes and then just use session_replication_role to control how it acts in a particular transaction. Greg, Thanks for your help on this. I'll try to work out something along these lines. I'm inclined to update one of the system tables to accomplish this because that's the way we did it in version 7.4.x. In that case, we were setting reltriggers to 0 in pg_class to turn off all the triggers on a given table, and, in fact, I was doing that at Tom's suggestion for solving the problem in a post to the list long, long, ago, and far, far, away. Again, thanks for taking the time to help :o] Here's a quick example: SET client_min_messages = 'ERROR'; DROP SCHEMA IF EXISTS triggertest CASCADE; SET client_min_messages = 'NOTICE'; CREATE SCHEMA triggertest; SET SEARCH_PATH = triggertest; CREATE TABLE foo(a int); INSERT INTO foo VALUES (1); CREATE FUNCTION trig1() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN RAISE NOTICE 'I am trigger one'; RETURN NULL; END; $_$; CREATE FUNCTION trig2() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN RAISE NOTICE 'I am trigger two'; RETURN NULL; END; $_$; CREATE FUNCTION trig3() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN RAISE NOTICE 'I am trigger three'; RETURN NULL; END; $_$; CREATE TRIGGER t1 AFTER UPDATE on foo FOR EACH ROW EXECUTE PROCEDURE trig1(); CREATE TRIGGER t2 AFTER UPDATE on foo FOR EACH ROW EXECUTE PROCEDURE trig2(); CREATE TRIGGER t3 AFTER UPDATE on foo FOR EACH ROW EXECUTE PROCEDURE trig3(); UPDATE foo SET a=a; -- all three fire ALTER TABLE foo ENABLE ALWAYS TRIGGER t1; ALTER TABLE foo ENABLE REPLICA TRIGGER t2; UPDATE foo SET a=a; -- two does not fire SET session_replication_role TO 'replica'; UPDATE foo SET a=a; -- three does not fire SET session_replication_role TO DEFAULT; UPDATE foo SET a=a; -- two does not fire The output of the above yields: CREATE TRIGGER psql:trig.example:53: NOTICE: I am trigger one psql:trig.example:53: NOTICE: I am trigger two psql:trig.example:53: NOTICE: I am trigger three UPDATE 1 ALTER TABLE ALTER TABLE psql:trig.example:59: NOTICE: I am trigger one psql:trig.example:59: NOTICE: I am trigger three UPDATE 1 SET psql:trig.example:63: NOTICE: I am trigger one psql:trig.example:63: NOTICE: I am trigger two UPDATE 1 SET psql:trig.example:67: NOTICE: I am trigger one psql:trig.example:67: NOTICE: I am trigger three UPDATE 1 -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200804091452 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disable Triggers
Greetings:
I have a situation where an external process needs to disable the firing of
triggers on a table. I have two backend functions which handle this task:
disable_triggers ('table_name')
enable_triggers ('table_name')
These functions are called inside a transaction. The first, stores various
attributes of the triggers in a secondary table, then updates pg_trigger
setting tgenabled to 'D'. The second restores the value of tgenabled from the
secondary table and then deletes the records from the secondary table.
I can begin a transaction withing psql, issue: SELECT disable_triggers
('cust'), make an update to cust and the before and after triggers all fire
just like normal. I can do a \d cust inside the same transaction and the
triggers sections is listed with the heading: Disabled Triggers. I can look
at the pg_trigger records associated with the cust table, and tgenabled is
set to D, but the triggers still fire. session_replication_role is set to
origin. I thought this was supposed to be fixed in later versions of
Postgres (I'm converting from 7.4.19 to 8.3.1), so apparently I'm missing
something.
Does anyone have any insight?
TIA
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 11:00, Greg Sabino Mullane wrote: I have a situation where an external process needs to disable the firing of triggers on a table. ... session_replication_role is set to origin. I thought this was supposed to be fixed in later versions of Postgres (I'm converting from 7.4.19 to 8.3.1), so apparently I'm missing something. You want: SET session_replication_role to 'replica'; Thanks for the response Greg. Should the session_replication_role be restored to origin, when the process is complete? -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200804091058 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 11:00, Greg Sabino Mullane wrote: I have a situation where an external process needs to disable the firing of triggers on a table. ... session_replication_role is set to origin. I thought this was supposed to be fixed in later versions of Postgres (I'm converting from 7.4.19 to 8.3.1), so apparently I'm missing something. You want: SET session_replication_role to 'replica'; -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200804091058 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 I see the following in the documentation for pg_trigger related to tgenabled: Controls in which session_replication_role modes the trigger fires. O = trigger fires in origin and local modes, D = trigger is disabled, R = trigger fires in replica mode, A = trigger fires always. My question is: When tgenabled is set to D, how does that setting interact with session_replication_role and, is there a way to use tgenabled with a setting of D to prevent a particular trigger from firing. Using ALTER TABLE to disable the trigger won't work because the whole table is locked during the transaction and I only want the disabled trigger to apply to the current transaction in the current session. TIA -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 13:12, Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] writes: My question is: When tgenabled is set to D, how does that setting interact with session_replication_role and, is there a way to use tgenabled with a setting of D to prevent a particular trigger from firing. Using ALTER TABLE to disable the trigger won't work because the whole table is locked during the transaction and I only want the disabled trigger to apply to the current transaction in the current session. I'll bet you're missing a relcache flush operation. I don't think an update on pg_trigger will cause that by itself. regards, tom lane Thanks for the response Tom. I hate to be dense, but I really don't have a clue as to what you are saying. I can't find anything in the docs regarding relcache flush. I have to get this issue resolved as our system uses a Perl process to keep certain columns in certain tables in sync across several databases, so, if you can point me in the right direction, that would be great. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Saturday 05 April 2008 11:21, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: what type is new.ontime ?? timestamp or interval. I would expect it to be an interval. But intervals are either negative or positive, not ago unless that's something peculiar to 7.4 that I've long since forgotten. No, it's still around: regression=# select '-1 day'::interval; interval -- -1 days (1 row) regression=# set datestyle = postgres; SET regression=# select '-1 day'::interval; interval - @ 1 day ago (1 row) The most bletcherous aspect of Terry's original coding is that it fails entirely, and silently, if the DateStyle setting isn't what it's assuming... regards, tom lane Well, I didn't realize that ago was only applicable to a certain date style setting. I don't recall ever reading that anywhere but maybe I missed it. Now I know how to do it correctly. Thanks to all who responded... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Subtracting Two Intervals
In porting from 7.4.19 to 8.3.1 I have found the following: 7.4.19: mwr=# select interval '1 day 15 hours 30 minutes' - interval '29 hours'; ?column? @ 10 hours 30 mins (1 row) 8.3.1: mwr83=# select interval '1 day 15 hours 30 minutes' - interval '29 hours'; ?column? @ 1 day -13 hours -30 mins (1 row) Is that right? I mean if you take 1 day (24 hours) and add -13 hours and -30 minutes, you get 10 hours and 30 minutes, but is it supposed to display that way? TIA -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Friday 04 April 2008 16:36, Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] writes: I am converting our application from 7.4.19 to 8.3.1. In the old scheme of things, I was generating an interval between two timestamps and evaluating the interval string in another set of trigger code. I was doing the following: IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN With the new casting rules, this doesn't work. Well, you could force it to work by casting new.ontime to text explicitly, but this is a pretty horrid way of testing for a negative interval anyhow. I'd be inclined to do something like new.ontime '0 seconds' BTW, the IS NOT NULL test is redundant too, since the comparison can't succeed for a null. regards, tom lane Thanks Tom. That's what I'm looking for. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Conversion to 8.3
Greetings: I am converting our application from 7.4.19 to 8.3.1. In the old scheme of things, I was generating an interval between two timestamps and evaluating the interval string in another set of trigger code. I was doing the following: IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN . . END IF; With the new casting rules, this doesn't work. How can I determine if this on-time value is ago, that is, the shipment is late? Thanks for any help you can give... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Friday 04 April 2008 15:01, Craig Ringer wrote: Terry Lee Tucker wrote: Greetings: I am converting our application from 7.4.19 to 8.3.1. In the old scheme of things, I was generating an interval between two timestamps and evaluating the interval string in another set of trigger code. I was doing the following: IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN If I understand your question correctly, you're seeking to determine if new.ontime is in the past. If so, compare with current_timestamp / current_date as appropriate, eg: IF new.ontime IS NOT NULL AND new.ontime current_timestamp THEN (Note that current_timestamp and current_date are constant within a transaction, so they might not be suitable if you have really long running transactions). -- Craig Ringer Craig, Thanks for the response. I have failed to communicate the problem. The ontime value is calculated based on the arrival time versus the appointment time; therefore the current timestamp is not helpful. I don't have the appointment data in this trigger and thus I can't do a comparison there unless I go find the appointment data, which I could do, but was trying to prevent the overhead. My question is: Is there a way to look directly at the timestamp value and determine if it was ago, that is, negative? Thanks... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Friday 04 April 2008 16:36, Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] writes: I am converting our application from 7.4.19 to 8.3.1. In the old scheme of things, I was generating an interval between two timestamps and evaluating the interval string in another set of trigger code. I was doing the following: IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN With the new casting rules, this doesn't work. Well, you could force it to work by casting new.ontime to text explicitly, but this is a pretty horrid way of testing for a negative interval anyhow. I'd be inclined to do something like new.ontime '0 seconds' BTW, the IS NOT NULL test is redundant too, since the comparison can't succeed for a null. regards, tom lane Thanks Tom. This is what I needed. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Proper Way to Disable Triggers
Greetings: I am in the process of converting our 7.4.19 database to 8.3.1. We have various scenarios where we need to disable all triggers for a given table except for the Slony replication triggers. I'm looking for advice for the proper way to do this. I have a function, disable_triggers(table), which worked in 7.4.19 by retrieving the value of reltriggers from pg_trigger, saving the value in another table, and then setting reltriggers to zero. Once the work was done, we then call enable_triggers(table) which restores the value of reltriggers. Keep in mind, all of this is in a transaction, and thus, the action is only visible to the connection doing the work. Of course, this also disabled the Slony trigger, which is not what we want. Now that we are porting to 8.3.1, I'm looking for advice as to which is the best way to disable triggers for a given table. Should I simply alter disable_triggers to set tgenabled in pg_trigger to D for all the triggers I want to disable for a given operation, and then code enable_triggers to restore the value to O for said triggers? Thanks in advance for any insight you can give... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Escaping \n
Greetings: We are moving our application from 7.4.14 to 8.3.1. One giant step for mankind... Anyay, I have several triggers that update notes fields in certain tables and loading the trigger function is giving me the following error: psql:bill/bill_preupd_func.plsql:83: WARNING: nonstandard use of escape in a string literal LINE 1: ...OR REPLACE FUNCTION bill_preupd_func () RETURNS TRIGGER AS ' ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. The problem is a line like 'UPDATE bill SET notes = 'blah, blah, yea\nmore stuff'; How to I escape the newline embeded in the string? I've tried the advice from HINT, but have been unable to get it to work. Thanks... Thanks... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Escaping \n
On Friday 28 March 2008 17:21, Sam Mason wrote: On Fri, Mar 28, 2008 at 05:06:10PM -0400, Terry Lee Tucker wrote: HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. The problem is a line like 'UPDATE bill SET notes = 'blah, blah, yea\nmore stuff'; How to I escape the newline embeded in the string? I've tried the advice from HINT, but have been unable to get it to work. The statement would become: UPDATE bill SET notes = E'blah, blah, yea\nmore stuff'; Is this what you tried? I couldn't tell from your message. If you did, then maybe your database drivers are somehow mangling the statement somewhere between your code and the database. You could try running it locally from inside psql to find out. Sam Thanks Sam. No, that is not what I tried. I had tried: UPDATE bill SET notes = 'blah, blah, yeaE'\n'more stuff.' It didn't dawn on me that the E went in front of the whole string! Thanks for the help... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LIMIT Question
When one uses LIMIT, as in LIMIT 1, is the entire query executed on the server side, but only one record returned? PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) Thanks... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Disable Triggers
Greetings: We have been working diligently toward integrating Slony into our production databases. We've been having trouble with various tables, although being replicated perfectly in the initial replication stage, afterwards, getting out of sync. I have finally figured out what the problem is. We have a Perl process that continually updates certain columns across all databases. That Perl process calls a function we have written called disable_triggers which updates pg_class, setting reltriggers to 0 for the given table, and then later, after the work is complete, resetting reltriggers to the original value. Unfortunately, during this process, the Slony trigger is disabled as well which is causing our problem. My questions is this: how would I go about changing my function so that all the triggers EXCEPT the Slony trigger would be disabled? Any ideas? Version: PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Disable Triggers
Greetings: We have been working diligently toward integrating Slony into our production databases. We've been having trouble with various tables, although being replicated perfectly in the initial replication stage, afterwards, getting out of sync. I have finally figured out what the problem is. We have a Perl process that continually updates certain columns across all databases. That Perl process calls a function we have written called disable_triggers which updates pg_class, setting reltriggers to 0 for the given table, and then later, after the work is complete, resetting reltriggers to the original value. Unfortunately, during this process, the Slony trigger is disabled as well which is causing our problem. My questions is this: how would I go about changing my function so that all the triggers EXCEPT the Slony trigger would be disabled? Any ideas? Version: PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Disable Triggers
On Thursday 21 February 2008 11:26, A.M. wrote: On Feb 21, 2008, at 10:20 AM, Terry Lee Tucker wrote: Greetings: We have been working diligently toward integrating Slony into our production databases. We've been having trouble with various tables, although being replicated perfectly in the initial replication stage, afterwards, getting out of sync. I have finally figured out what the problem is. We have a Perl process that continually updates certain columns across all databases. That Perl process calls a function we have written called disable_triggers which updates pg_class, setting reltriggers to 0 for the given table, and then later, after the work is complete, resetting reltriggers to the original value. Unfortunately, during this process, the Slony trigger is disabled as well which is causing our problem. My questions is this: how would I go about changing my function so that all the triggers EXCEPT the Slony trigger would be disabled? Any ideas? Version: PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) Couldn't your triggers check some flag to determine if they should continue? Cheers, M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Thanks for the response. I do, in fact have a different function which can disable any trigger by trigger name which works by creating an entry in a table where, when the given trigger does fire, it checks for an entry in the table at the top of the trigger and takes the appropiate action. The problem is that the solution for disabling all triggers is used in several utility programs and I'm trying to avoid changing a bunch of code. I appreciate your input. Work: 1-336-372-6812 Cell: 1-336-404-6987 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Disable Triggers
On Thursday 21 February 2008 12:20, Andrew Sullivan wrote: Well, you could try rewriting the function to disable all but the Slony trigger. But there's something else wrong here. I seem to recall that we found some code path where reltriggers wasn't checked properly anyway, so disabling triggers wouldn't work exactly as you are doing it. This was part of the reason for the catalogue-breaking oid fiddling Slony does on replicated tables, IIRC. So I'm not even sure your current approach will work reliably as you think. Probably the right answer, I'm afraid, is to change your trigger functions to fire more selectively, then make the disable trigger function a no-op (so you don't have to change all your other code right now). A Thanks for the input. I've been using the reltriggers in pg_class for a long time and it does work; however, I did notice in the documentation on pg_trigger that tgenabled is not checked properly and using that will give inconsistant results. We have several valid reasons for disabling all triggers that I won't elaborate here. Unless I get a better idea, I'm going to change the disable_triggers function to duplicate all the records in pg_trigger belonging to a given table, delete the records except for the Slony trigger, update pg_class setting reltriggers to 1, do the work, and then restore everything with a call to enable_triggers. Does this sound reasonable to you? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disable Triggers
On Thursday 21 February 2008 12:56, Scott Marlowe wrote: On Thu, Feb 21, 2008 at 9:20 AM, Terry Lee Tucker [EMAIL PROTECTED] wrote: Greetings: We have been working diligently toward integrating Slony into our production databases. We've been having trouble with various tables, although being replicated perfectly in the initial replication stage, afterwards, getting out of sync. I have finally figured out what the problem is. We have a Perl process that continually updates certain columns across all databases. That Perl process calls a function we have written called disable_triggers which updates pg_class, setting reltriggers to 0 for the given table, and then later, after the work is complete, resetting reltriggers to the original value. Unfortunately, during this process, the Slony trigger is disabled as well which is causing our problem. Disabling all triggers is not something you do on a live, running database with users accessing and possibly changing it, it's something you do to a database during maintenance when no one else is connected. You'll have to go with the solution you talked about, i.e. disabling individual triggers by name, etc... I have failed to mention that we are disabling all the triggers on a given table only done during a transaction; thus, it affects no one else. Thanks for the input... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.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] Disable Triggers
On Thursday 21 February 2008 13:05, Andrew Sullivan wrote: Unless I get a better idea, I'm going to change the disable_triggers function to duplicate all the records in pg_trigger belonging to a given table, delete the records except for the Slony trigger, update pg_class setting reltriggers to 1, do the work, and then restore everything with a call to enable_triggers. Does this sound reasonable to you? I expect you're going to have to get everyone to disconnect after that, because the triggers oids will all have changed and you'll get errors to that effect. Also, are there these triggers on the slony replicas? You really need to be doing DROP TRIGGER/STORE TRIGGER operations if so. Otherwise, very surprising things may happen. Gee, I hadn't thought about that. Back to the drawing board... Thanks for the help. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Disable Triggers
On Thursday 21 February 2008 17:07, Andrew Sullivan wrote: On Thu, Feb 21, 2008 at 04:35:28PM -0500, Geoffrey wrote: How might we find out which release it was fixed in? Back patching 7.4.19 with the fix might be easier then trying to move up to the fixed version. According to HISTORY, there was a significant fix in this area in 8.1: * Add ALTER TABLE ENABLE/DISABLE TRIGGER to disable triggers (Satoshi Nagayasu) I think your chances of successfully back-porting something like that from 8.1 to 7.4.x are way lower than your chances of fixing your application to use a later database system. Also, if you get off 7.4, you get rid of the horrifying checkpoint storms in that version, and get a whack of other improvements and bugfixes. Yea, upgrading is slated to begin in April. We needed to get replication going now. You've been a big help. Thanks... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger Question
On Wednesday 14 March 2007 11:15, Jason Lee wrote: I'm trying to write a trigger that updates a date_changed field on a record anytime that record is updated. I have a function written, and the trigger created, but everytime I update the record, I get a recursion limit error. It appears that the action performed by my trigger is causing the trigger to fire. How do I avoid that. For the record, here's my function (modeled after a trigger that works on SQL Server. I'm pretty much a noob with plpgsql :) declare begin update unit_specification set date_changed = now() from unit_specification us where us.id = NEW.id; RETURN NEW; end; With the trigger created with CREATE TRIGGER update_changed_ts AFTER INSERT OR UPDATE ON unit_specification FOR EACH ROW EXECUTE PROCEDURE public.update_unit_spec_changed_date(); Thanks for the help. :) - Jason Lee, SCJP Senior Software Engineer http://www.iec-okc.com http://www.iec-okc.com/ Your trigger needs to be a BEFORE UPDATE trigger and you simply set the value of the field in the trigger as in: date_changed = current_date; -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] upgrading pl/pgsql triggers from 7.4 to 8.2
On Thursday 25 January 2007 10:02 am, Louis-David Mitterrand [EMAIL PROTECTED] thus communicated: Hello, We tried upgrading a 7.4 base to 8.2 and found many issues with the triggers. What are the main changes in the pl/pgsql syntax or contraints checking between these two version? Thanks, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster We have to do this as well so your question is of great interest to me. I hope you/we get lots of answers. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] printf-like format strings
On Monday 22 January 2007 12:59 pm, Alexander Presber [EMAIL PROTECTED] thus communicated: Hello, does somebody know of an extension for postgres that allows the use of printf-like format strings? PL/Perl comes to mind, but how could one take care of the variable argument count? Thanks for any advice! Sincerely Alexander Presber ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ If I understand the question correctly, couldn't you use pop @_ in the Perl function? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres Team: Thank You All
On Wednesday 20 September 2006 09:59 pm, Brian Maguire [EMAIL PROTECTED] thus communicated: -- To all involved in this project, -- -- I justed wanted to let you know how impressed and pleased I have been with postgres over the past 5 years . The timeliness and quality of the releases are always robust and stable. Every release has a very nice mix of admin, performance, platform, and feature adds. The support of the listserves is bar none to any commercial support I have ever purchased. I can't remember a time I did not get 3-5 answers to any question I have ever had. I must also note that the new website design and organization have added very nice polish to the project. -- -- Cheers and thank you all, -- -- Brian I agree wholeheartedly. -- -- -- -- -- -- -- ---(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 -- -- Terry Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] After Trigger
On Thursday 21 September 2006 12:44 pm, Bob Pawley [EMAIL PROTECTED] thus communicated: -- I am seeking wording for a procedure that will initiate a trigger only after another trigger has completed its function. In this case I want to drop a table that is created and used by the first trigger. -- -- Could someone point me to documentation? -- -- Bob Pawley According to the 7.4.6 docs: If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name. In the case of before triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any before trigger returns a NULL pointer, the operation is abandoned and subsequent triggers are not fired. So, name the last trigger you want to fire such that it falls aphabetically behind the rest. -- Terry Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Question Regarding a Temporary Table
Greetings:
I have have a plpgsql function that creates a temporary table to facilitate
some processing. Here is the code:
CREATE TEMP TABLE tmp (code VARCHAR,
booked INTEGER,
availINTEGER,
covered INTEGER,
profit NUMERIC (10,2),
billed NUMERIC (10,2))
WITHOUT OIDS ON COMMIT DROP;
Note the ON COMMIT DROP. I would expect this table to disapear after the
function completes, but it does not. Also, if I execute the the function
twice in a row from the psql interface, on the second try, I get the
following error:
sev=# select * from custSprtRpt('04/01/06', current_date);
NOTICE: custSprtRpt ()
ERROR: relation with OID 123654 does not exist
CONTEXT: PL/pgSQL function custsprtrpt line 39 at SQL statement
If have to reload the function with \i sqlfunc/custSprtRpt.plsql so that it
will work again. Why is this?
Also, this function does not perform any updates to a permanent database
table.
Anyone have any insight into this issue?
sev=# select version();
version
--
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
Thanks...
--
Terry Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719
[EMAIL PROTECTED]
www.turbocorp.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question Regarding a Temporary Table
Thanks for the reponse Jeff. See comments below.
On Wednesday 20 September 2006 05:09 pm, Jeff Davis [EMAIL PROTECTED] thus
communicated:
-- On Wed, 2006-09-20 at 16:51 -0400, Terry Lee Tucker wrote:
-- Greetings:
--
-- I have have a plpgsql function that creates a temporary table to
facilitate -- some processing. Here is the code:
-- CREATE TEMP TABLE tmp (code VARCHAR,
-- booked INTEGER,
-- availINTEGER,
-- covered INTEGER,
-- profit NUMERIC (10,2),
-- billed NUMERIC (10,2))
-- WITHOUT OIDS ON COMMIT DROP;
--
-- Note the ON COMMIT DROP. I would expect this table to disapear after
the -- function completes, but it does not. Also, if I execute the the
function --
-- In 8.1 it does drop for me on COMMIT, as you would expect. I don't know
-- about 7.4.
--
-- twice in a row from the psql interface, on the second try, I get the
-- following error:
-- sev=# select * from custSprtRpt('04/01/06', current_date);
-- NOTICE: custSprtRpt ()
-- ERROR: relation with OID 123654 does not exist
-- CONTEXT: PL/pgSQL function custsprtrpt line 39 at SQL statement
--
-- If have to reload the function with \i sqlfunc/custSprtRpt.plsql so
that it -- will work again. Why is this?
--
-- PL/pgSQL caches query plans. Unfortunately, there is currently no good
-- mechanism to invalidate the plans, and the function is using a stale
-- plan with an OID that no longer exists.
--
-- The workaround is to use EXECUTE in the function, and build the query
-- from a string. That prevents PL/pgSQL from caching the plan.
--
-- What confuses me is, if it didn't drop your table, why would it say the
-- oid doesn't exist?
Well, I was assuming that that the table wasn't being dropped and that was
what was causing the error. I can see from your comments, that I was wrong on
that asssumption. I can do this with and execute, but it's going to be a pain
to acomplish. I wonder what good a temporary table is if you can't use the
code which creates it twice in a row with reloading the function?
Anyway, thanks for the response...
--
-- Regards,
-- Jeff Davis
--
--
--
--
-- ---(end of broadcast)---
-- TIP 6: explain analyze is your friend
--
--
Terry Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719
[EMAIL PROTECTED]
www.turbocorp.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Sharing News
Greetings List: I just want to share with my Open Source commrads a success story. We have been working on converting a rather large and complex Logistics application from a Commercial database engine and 4GL supplied Progress Software Corp. We wrote the user interface in native X-Windows using C. This is a multi-database application with some rather complex interactions between databases in realtime mode. Most of that work is handled using Perl. Many of the reports are also written in Perl. On August 7, we went live with the application. We have over 200 connections at any given time. The application is very fast. Today, we just closed out our first month on the system. I just wanted to share this with everyone because many of you at different times have answered my various questions and I really appreciate it. This list and those associated with it are a fantastic resource. I hope, after I rest a little, to be able to contribute more to it, at least in answering some of the simple stuff ;o] PS: Another commercial database product bites the dust... -- Terry Tucker IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Newbie Copy From not working
On Thursday 17 August 2006 03:27 pm, Walter Vaughan [EMAIL PROTECTED] thus communicated: The problem is I need to load a field that is allowed to be null with nothing and it work The above statement is not the problem you are having. We dumped and loaded a 4 gig Progress database with copy and there were many instances of ||value||more|and more| in the dump file. There is something else wrong with the line of data. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL on Embeded Systems
On Tuesday 18 July 2006 05:50 am, Adem HUR [EMAIL PROTECTED] thus communicated: -- Hi, -- -- I am sory for my bad English :) -- -- -- We try to use PostgreSQL on an Embeded System. -- -- Plan to use this PC/104 Module -- http://www.icop.com.tw/products_detail.asp?ProductID=106 -- -- -- and this EmbedDisk -- http://www.icop.com.tw/products_detail.asp?ProductID=185 -- -- -- There is a Linux version (X-Linux) for the PC/104 Module. -- http://www.dmp.com.tw/tech/os-xlinux/ -- -- This Linux looks enough for our project. -- -- We instal X-Linux on PC/104 Module. -- -- Now, We want to install PostgreSQL -- -- but there are some problems -- -- 1 - Firstly, Can we install PostgreSQL on this X-Linux? -- -- 2 - Our EmbedDisk capacity is 128 Mb. -- X-Linux size approximately 32Mb. -- -- Our expectation, PostgreSQL operate standart query on basic tables -- and listen 5432 port. -- -- A program collect data from peripheral devices, insert this data -- into PostgreSQL database. Another program access this database by use 5432 -- port, read and delete data. -- -- We want to use PostgreSQL on PC/104 Module because we already use -- PostgreSQL for other part of the project. -- -- We have a capacity problem, so PostgreSQL installation and -- database tables must be configure to smaller size. -- -- How can we do this? suggestion,information, document... -- -- How much the PostgreSQL installation size? -- -- -- Thanks, -- -- -- -- ---(end of broadcast)--- -- TIP 3: Have you checked our extensive FAQ? -- --http://www.postgresql.org/docs/faq -- From the 7.4.6 documentation: Also check that you have sufficient disk space. You will need about 65 MB for the source tree during compilation and about 15 MB for the installation directory. An empty database cluster takes about 25 MB, databases take about five times the amount of space that a flat text file with the same data would take. If you are going to run the regression tests you will temporarily need up to an extra 90 MB. Use the df command to check for disk space. This includes space required for compiling the application. Looks like about 40 MB for the installation and an empty cluster. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Performance Question
Hello List: I've been told that an update to a record is equivalent to a delete and insert operation. We have a utility written in Perl that brings into sync certain elements of 50 thousand records on 8 structurally identical databases. We threw together the script and decided to just delete the record and re-insert it with the data that was brought into sync. Now the question: Is it just as fast to do it this way, or is there some hidden advantage to performing an update? Just curious. TIA master=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Performance Question
On Wednesday 14 June 2006 03:57 pm, Terry Lee Tucker [EMAIL PROTECTED] thus communicated: -- Hello List: -- -- I've been told that an update to a record is equivalent to a delete and insert -- operation. We have a utility written in Perl that brings into sync certain -- elements of 50 thousand records on 8 structurally identical databases. We -- threw together the script and decided to just delete the record and re-insert -- it with the data that was brought into sync. Now the question: Is it just as -- fast to do it this way, or is there some hidden advantage to performing an -- update? -- -- Just curious. -- -- TIA Thanks for the answers. This list is a BIG help to us all :o] ---(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] are there static variables in pgsql?
On Saturday 10 June 2006 06:31 pm, Yavuz Kavus [EMAIL PROTECTED] thus communicated: -- i am writing a recursive procedure in pl/pgsql. -- i need to check whether a condition is true in any step of recursive calls. -- -- if i get a true response in one call, i wont make other recursive calls -- anymore, because i get what i need. -- if no one gives me a true response, then uppest procedure will return false -- as a result. -- -- i think i may achieve this with a static variable(shared among all calls). -- -- is it possible? -- -- or another solution? -- -- thanks a lot for any help. -- I don't know of any static variable capability; however, I use various utility tables to accomplish this type of task. You can insert records into a table like this while inside a transaction and the current process will be the only process that can see it. When you are through with it, delete the record, all within the same transaction, and your purpose will be served. HTH... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] OID
On Friday 09 June 2006 06:34 am, Bert [EMAIL PROTECTED] thus
communicated:
-- Hi list
-- What is the comment to add OIDs to a already existing table, or i have
-- to recreate the table?
--
-- Thanks,
-- Clemens
--
--
Well, according on \h ALTER TABLE on version 7.4.6 all you can do is SET
WITHOUT OIDS.
master=# \h alter table
Command: ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ ONLY ] name [ * ]
ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
ALTER TABLE [ ONLY ] name [ * ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ALTER TABLE [ ONLY ] name [ * ]
SET WITHOUT OIDS
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name
ALTER TABLE [ ONLY ] name [ * ]
ADD table_constraint
ALTER TABLE [ ONLY ] name [ * ]
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER TABLE name
OWNER TO new_owner
ALTER TABLE name
CLUSTER ON index_name
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] How to do a CREATE DATABASE and then connect to it?
On Saturday 03 June 2006 04:07 am, Joseph Brenner [EMAIL PROTECTED]
thus communicated:
--
-- Joshua D. Drake [EMAIL PROTECTED] wrote:
--
-- Joseph Brenner wrote:
--
-- After you do a CREATE DATABASE, how do you programatically
-- connect to what you just created?
--
-- In the psql monitor, you'd use the \c command.
--
-- If the DATABASE already exists when you connect to postgresql,
-- you use the name when you connect (e.g. dbname=...).
--
-- I'm getting the impression I need to do this in multiple steps,
-- which is workable, but seems a little silly.
--
-- Have you tried reading the documentation?
--
-- Yup. Are you sure you've understood my question?
--
-- It's not a terribly major point, I'm just wondering if it's true that
-- there's no postgres SQL analog of the psql \c command.
--
-- For example, this certainly works in perl:
--
-- use DBI;
--
-- my $dbh_1 = DBI-connect(dbi:Pg:dbname=template1, $owner,
$db_password, -- { RaiseError = 1, AutoCommit =
1 }); --
-- $dbh_1-do(CREATE DATABASE new_test_db);
--
-- $dbh_1-disconnect();
--
-- my $dbh_2 = DBI-connect(dbi:Pg:dbname=new_test_db, $owner,
$db_password, -- { RaiseError = 1, AutoCommit =
1 }); --
--
-- $dbh_2-do(CREATE TABLE whocares (meaningless INTEGER, blather
TEXT)); --
--
-- But the need for those two DBI-connects seems inelegant to me.
--
-- Do you have any particular portion of the documentation in mind?
--
I don't see the point. There are two connects either way.
psql:
Connection 1: psql template1
Connection 2: \c new_test_db
Perl:
Connection 1: my $dbh_1 = DBI-connect(dbi:Pg:dbname=template1, blah, blah
Connection 2: DBI-connect(dbi:Pg:dbname=new_test_db, $owner, blah, blah
The only difference, at least from my point of view, is the method used and
the extra disconnect(). But even so, the extra disconnect() is not really
necessary.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] help me on a trigger
On Friday 02 June 2006 02:09 am, Pedro [EMAIL PROTECTED] thus communicated: -- I need to create a trigger that decreases on a stocks table the amount -- that have been inserted on the tooken_from_stock table. -- -- the trigger should be something like: -- -- CREATE TRIGGER triggerdecrease -- BEFORE INSERT ON tooken_from_stock -- EXECUTE PROCEDURE decrease(); -- -- -- then i need to create the procedure decrease that gets the inserted -- tooken_from_stock.amount and subtracts it on stocks.amount -- -- can anybody help me with this please? -- -- thanks in advance. -- See the documentation at the link below for a discussion on writting triggers. http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html ---(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] Problems posting messages in this mailing-list?
On Wednesday 31 May 2006 07:51 am, Tino Wildenhain [EMAIL PROTECTED] thus communicated: -- Philippe Lang schrieb: -- Hi, -- ... -- Can anyone read this message? -- -- No, sorry. Please repost. -- -- Thx -- Tino .-) -- :oD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Question Regarding DELETE FROM ONLY
Hello List:
Please observe the following example that reproduces my problem:
CREATE TABLE ptable (code VARCHAR) WITHOUT OIDS;
CREATE TABLE
CREATE TABLE ctable (code VARCHAR, name VARCHAR)
INHERITS (ptable) WITHOUT OIDS;
NOTICE: merging column code with inherited definition
CREATE TABLE
INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One');
rnd=# SELECT * FROM ptable;
code
--
code_one
(1 row)
rnd=# SELECT * FROM ctable;
code | name
---+--
code_one | Code One
(1 row)
DELETE FROM ONLY ctable WHERE code ~* 'code_one';
rnd=# SELECT * FROM ptable;
code
--
(0 rows)
The record in ctable AND the record in ptable are both deleted even though I
specified ONLY ctable in the delete phrase. Why is this happening? The two
tables in this example represent a greatly simplified version of what I'm
doing in developing an application and if ONLY doesn't work then I've got a
big problem. Have I misunderstood inheritance altogether?
TIA...
rnd=# select version();
version
--
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Question Regarding DELETE FROM ONLY
On Monday 29 May 2006 09:43 am, Michael Fuhr [EMAIL PROTECTED] thus
communicated:
-- On Mon, May 29, 2006 at 08:40:43AM -0400, Terry Lee Tucker wrote:
-- INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One');
-- rnd=# SELECT * FROM ptable;
-- code
-- --
-- code_one
-- (1 row)
--
-- rnd=# SELECT * FROM ctable;
-- code | name
-- ---+--
-- code_one | Code One
-- (1 row)
--
-- These aren't two distinct records -- they're the same record, the
-- one in ctable, as the following queries show:
--
-- SELECT tableoid::regclass, * FROM ptable;
-- SELECT tableoid::regclass, * FROM ctable;
--
-- You can use FROM ONLY to see that the record doesn't actually exist
-- in ptable:
--
-- SELECT tableoid::regclass, * FROM ONLY ptable;
--
-- DELETE FROM ONLY ctable WHERE code ~* 'code_one';
--
-- rnd=# SELECT * FROM ptable;
-- code
-- --
-- (0 rows)
--
-- The record in ctable AND the record in ptable are both deleted even
though I -- specified ONLY ctable in the delete phrase. Why is this
happening? --
-- Because there was only one record, the one in ctable, and you deleted
-- it. When you inserted the record into ctable that's the only place
-- it went. The query against ptable showed records in the parent
-- table (none) and records in its child tables (one). After you
-- delete the record from the child the subsequent query against the
-- parent returns zero rows because both tables are now empty (the
-- parent was always empty and the child had its one record deleted).
--
-- --
-- Michael Fuhr
--
Thanks for the response Michael. I'm beginning to see the light.
---(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] background triggers?
On Tuesday 23 May 2006 11:19 am, Sim Zacks [EMAIL PROTECTED] thus communicated: -- Is there any way to write a statement trigger that runs in the -- background? In my testing, when I have an After Insert For Each -- Statement trigger, the function call does not end until the trigger is -- finished processing. -- -- What I would like to do, and please let me know if there is a better way -- to do this, is to have an after event statement trigger run in a -- separate process so the function call ends when it ends and the -- statement trigger gets executed on its own time. -- -- The use that I have for this at the moment, and I can think of many -- other uses, is that I want to populate a statistics table each time that -- a table is updated. But the code to populate the table takes 10 seconds -- to run. I don't want the user to have to wait 10 seconds to add a record. -- -- ---(end of broadcast)--- -- TIP 6: explain analyze is your friend -- How about notice processing. See Asynchronous Notification in the docs. You can have your trigger make the notification and you can process execute the statistics function when the notice arrives and you could even do it in the background. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DB structure of PostGRE
On Wednesday 17 May 2006 12:19 pm, venu Vempati [EMAIL PROTECTED] thus communicated: -- Hi, -- my confession first..I have introduced myself this PostGRE db and I have a -- basic doubt? -- -- Where can I find a document about the structure of database/schema/users -- etc.. -- and how is the data actually stored, like the datafiles, tablespaces. -- by now you must have guessed I have some knowledge of Oracle..(oops can I -- use thisname here??!!) -- Thanks for your feedback.Just a link to a document should be OK to start -- with, if someone is taking pain to explain, most welcome :)) -- Regards.. -- --Venu -- http://www.postgresql.org/ has all sorts of documentation regarding the questions you ask. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pl/pgsql question
On Friday 12 May 2006 10:54 am, Rhys Stewart [EMAIL PROTECTED] thus communicated: -- hi all, -- are there any function in pl/pgsql to call a shell script? or like is -- there a pl/bash? -- You can use Perl in untrusted mode as a backend function and accomplish something like that. ---(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] Order of triggers and sequences...
On Saturday 29 April 2006 08:34 am, Alex Mayrhofer [EMAIL PROTECTED] thus communicated: -- Hi, -- -- i'm about to implement a trigger on a table with an id field populated by a -- sequence (the usual 'id SERIAL NOT NULL PRIMARY KEY'). -- -- Just a short (probably lame) question: Would a BEFORE INSERT row level -- trigger already see a populated 'id' column, or would that happen after all -- triggers on the actual insert? -- -- thanks, -- -- Alex -- In my experience, all the triggers see all the data all the time in varying combinations of the OLD and NEW buffers. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] file I/O in plpgsql
On Friday 28 April 2006 03:47 pm, Jessica M Salmon [EMAIL PROTECTED] thus communicated: -- -- I'm trying to write out query results to a text file from within a plpgsql -- function, but having trouble. Can anyone tell me if this is possible? I'm -- trying to perform \o filename, then select, but it squawks about no -- destination for the select results. Any pointers? -- -Meghan -- -- -- ---(end of broadcast)--- -- TIP 2: Don't 'kill -9' the postmaster -- You cannot do that with plpgsql. You can use Perl in trusted mode and pass the query results to the Perl function which can write the output to a ffile. ---(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] file I/O in plpgsql
On Saturday 29 April 2006 10:23 am, Terry Lee Tucker [EMAIL PROTECTED] thus communicated: -- You cannot do that with plpgsql. You can use Perl in trusted mode and pass -- the query results to the Perl function which can write the output to a ffile. -- Correcting typeo: s/trusted/untrusted/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] For vim users: Syntax highlighting for PostgreSQL
On Thursday 27 April 2006 07:43 pm, Devrim GUNDUZ [EMAIL PROTECTED] thus communicated: -- Hi, -- -- I just wrote a pgsql.vim file for vim users. It helps you to colorize -- the file which contains PostgreSQL SQL keywords: -- -- http://www.gunduz.org/postgresql/pgsql.vim -- -- In order to use this file, first edit filetype.vim file and add -- -- PgSQL -- au BufNewFile,BufRead *.pgsql setf pgsql -- -- to the relevant part. We will need to do this until this file becomes an -- official part of vim. -- -- Then put pgsql.vim to the syntax directory of vim -- (/usr/share/vim/vim64/syntax on my Fedora Core 5). After you rename your -- sql file as filename.pgsql, the syntax highlighting will be enabled. -- -- What I've added so far: -- -- - All the functions -- - All the data types -- - Many of the keywords (let us see if I've missed anything) -- - Comments -- -- I've used mysql.vim as a template. Thanks to \df, \dT and psql's -E for -- making this process easier. :) -- -- If you are vim user, please test it and send the possible -- errors/additions to me. I intend to send this file to vim developers, -- too, after the testing. -- -- Regards, -- -- -- 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: PL/php, plPerlNG - http://www.commandprompt.com/ -- -- -- ---(end of broadcast)--- -- TIP 6: explain analyze is your friend -- Thanks! I'll check this out ;o) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Triggers and Transactions
On Thursday 20 April 2006 12:25 pm, Chris Coleman [EMAIL PROTECTED] thus communicated: -- Hi, -- -- I have a question about how much of a trigger is in a transaction. -- I've read the docs and googled around but can't seem to find a -- concrete answer. -- -- I have two triggers that are designed to work together, one is a -- before trigger and one is an after. If the before trigger succeeds -- then it will have made some changes to one of my tables, however if -- the after one fails some how (elog(ERROR, )? then I would like to -- rollback the changes of the before one as well as any made by the -- after one too. -- -- Is this possible? -- -- Many thanks -- -- Chris Coleman. -- -- ---(end of broadcast)--- -- TIP 6: explain analyze is your friend -- The whole process is in a transaction and all of it will be rolled back. ---(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] Daylight Savings Time
Hello List:
I need to know if there is a convienient way of establishing whether DST is
active within a function dealing with adjusting timestamps to other time
zones. The problem is that if I have the following timestamp:
'04/21/2006 17:05 EDT'
and I use the timezone() function in the following manner:
return (timezone ('CST', '04/21/2006 17:05 EDT')
I get a two hour difference in time. Note that neither of the two arguments
are hard coded as this example. The CST value is stored in the customer
profile because that is their time zone and the timestamp is generated from
argeuments passed into the function.
This is:
rnd=# select version();
version
--
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
TIA
---(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] Daylight Savings Time
On Friday 21 April 2006 05:47 pm, Tom Lane [EMAIL PROTECTED] thus
communicated:
-- Terry Lee Tucker [EMAIL PROTECTED] writes:
-- I need to know if there is a convienient way of establishing whether
DST is -- active within a function dealing with adjusting timestamps to
other time -- zones. The problem is that if I have the following
timestamp:
-- '04/21/2006 17:05 EDT'
-- and I use the timezone() function in the following manner:
-- return (timezone ('CST', '04/21/2006 17:05 EDT')
-- I get a two hour difference in time.
--
-- Perhaps you should be using a DST-aware timezone specification? Since
-- 8.1 you could do
--
-- regression=# select timezone ('CST6CDT', '04/21/2006 17:05
EDT'::timestamptz); -- timezone
-- -
-- 2006-04-21 16:05:00
-- (1 row)
--
--
-- regards, tom lane
--
Thanks for the reply Tom. We will be upgrading to version 8.x hopefully in
August. I can implement a work around until then. So, when we can upgrade, we
will change the timezone specification in the customer profiles to the
DST-aware specification, and we will be set.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] trigger firing order
On Monday 10 April 2006 05:09 pm, Hugo saith: Hi, how can I tell in which order are triggered different triggers on the same table, let me explain , I have three triggers for table A, all of then are intended for before insert on the table, in ASA I can tell the Db in which order I want the triggers to fire, is there an equivalent for postgres ?? thanks for your help Postgres 8.0.4 Windows XP Triggers of the same type fire in alphabetical order; at least, that's how it works is 7.4.6. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Running Command Line From Trigger?
On Friday 07 April 2006 01:32 pm, Yudie Pg saith: Back to my original question where is it possible to run a command line from a function? From an earlier post: Note that plpgsql cannot run external programs on purpose. It's a security and safety issue. ---(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] Running Command Line From Trigger?
Reminds me of an old Star Trek - Next Generation episode. They were stuck in a time loop reliving the same series of events over and over. They had to leave themselves a clue so they could figure it out next time around. Maybe your post is it ;o) On Friday 07 April 2006 02:12 pm, Scott Marlowe saith: On Fri, 2006-04-07 at 12:38, Terry Lee Tucker wrote: On Friday 07 April 2006 01:32 pm, Yudie Pg saith: Back to my original question where is it possible to run a command line from a function? From an earlier post: Note that plpgsql cannot run external programs on purpose. It's a security and safety issue. Which is why I had advised using another language. Help! We're all trapped in a loop! ahhh! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Running Command Line From Trigger?
On Friday 07 April 2006 03:52 pm, Yudie Pg saith: Help! Try to install plperl ./createlang plperl mydb createlang: language installation failed: ERROR: could not access file $libdir/plperl: No such file or directory Do you have the Perl module on your machine? If not, you can get it here: http://search.cpan.org/search?query=Pgmode=all ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plgpsql and transactions
In the latter, you have expanded the scope of the transaction; which, sometimes you might want to do. Yes, I might. But, I'd like to understand it so I do know when I might or might not want to do it. Understanding is good. You need to read the documentation on transactions: http://www.postgresql.org/docs/7.4/interactive/tutorial-transactions.html and Concurrency control: http://www.postgresql.org/docs/7.4/interactive/mvcc.html Say I have a BEFORE INSERT trigger that does a table lock. When is that lock released? At the end of the trigger? Or after the INSERT has completed? At the end of the transaction. For example, say I want to set a column on the row I'm inserting based on what's already in the table. So I lock the table in the trigger and check the current status of the table and set the column based on that current status. I want to make sure that between the time the trigger completes and when the insert finally happens that another session can't also do an insert and see the same table state. Again, reading the docs on concurrency control and transactions will answer these questions. -- Bill Moseley [EMAIL PROTECTED] ---(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 5: don't forget to increase your free space map settings
Re: [GENERAL] plgpsql and transactions
On Thursday 06 April 2006 02:36 pm, Bill Moseley saith: In a BEFORE INSERT trigger, depending on input values, I need to lock a table and do a few selects. Of course, the lock table isn't much use if not currently in a transaction. So my question is this: can I tell if I'm inside a transaction or not and issue a BEGIN if not. And then also set a flag so that after the INSERT I can detect that I issued a BEGIN and do a COMMIT? Or, maybe better is to just throw an exception if not already inside a transaction. BTW -- it seems odd to me that you can issue a lock table outside of an explicit begin/commit and not get a warning. When would issuing a lock table outside an explicit transaction be of any use? -- Bill Moseley [EMAIL PROTECTED] ---(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 Bill, Triggers fire inside a transaction. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Running Command Line From Trigger?
On Thursday 06 April 2006 03:48 pm, Yudie Pg saith: I want to execute a command line from trigger function. The reason is I want to execute my perl script from stored procedure. any idea? Yudie Yudie, You might want to write your perl script as a trusted perl funcion and call from your trigger as you would any other backend function. Example of one we use: CREATE OR REPLACE FUNCTION fmtstr (TEXT, INTEGER) RETURNS TEXT AS ' my ($str, $len) = @_; return (pack (A$len, $str)); ' LANGUAGE 'plperlu'; ---(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] plgpsql and transactions
On Thursday 06 April 2006 03:27 pm, Bill Moseley saith: On Thu, Apr 06, 2006 at 01:44:57PM -0500, Terry Lee Tucker wrote: Triggers fire inside a transaction. Ah, thanks. Makes sense since each statement is in an implicit transaction. Granted, would help to see the trigger, but these are basically the same? -- fires a trigger that updates more than one table insert into semething (default); and: begin; -- fires a trigger that updates more than one table insert into somthing (default); commit; In the latter, you have expanded the scope of the transaction; which, sometimes you might want to do. ---(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] Updating a primary key
On Wednesday 22 March 2006 06:32 am, Paul Mackay saith: I was surprised to see that PostgreSQL doesn't execute a multiple row update as an atomic operation, but apparently one row at a time, with primary key uniqueness being checked after each row update. Actually, I think its done before the update, but I'm not sure and I'm certainly not a developer of Postgres. For example, let's say we have this table : CREATE TABLE mytable ( pos int PRIMARY KEY, t text ); into witch we insert two rows : INSERT INTO mytable (pos,t) VALUES (1,'test1'); INSERT INTO mytable (pos,t) VALUES (2,'test2'); Then, in order to insert a new record in position 1, we first try this update to bump any existing position number by 1 : UPDATE mytable SET pos = pos + 1; This actually raises the error ERROR: duplicate key violates unique constraint mytable_pkey. I'd be interested in any suggestions of workaround for this. Thanks, Paul We do things like this in plpgsql using a loop. We go backwards from the end making updates to the point where the new record is to be inserted. I'm sure others have more exotic methods. HTH ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] passing parameters to a trigger function
On Tuesday 21 March 2006 09:21 am, Larry White saith: I can't figure out how to pass parameters to a trigger function. I checked the documentation and saw that trigger functions don't take params in the usual fashion, but couldn't find an example of a pl-sql trigger function that used the original row data within the function. What I want is an on update trigger that creates an entry in a second table. The second (history) table has a subset of the columns in the first. Here's what I have so far: -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION CREATE OR REPLACE FUNCTION audit_task (param type declarations were here) RETURNS TRIGGER AS ' -- create an audit trail record BEGIN -- Perform the insert INSERT INTO TASK_h (id, updated_by, updated, name, description ) VALUES ($1, $2, $3, $4, $5); RETURN NULL; END; ' LANGUAGE plpgsql; -- THE TRIGGER CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE audit_task(); So the question is, how do I access the row from the original table so I can perform the insert? Thank you much. If I understand your question correctly, this documentation addresses your problem: 37.10. Trigger Procedures PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below. When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers. TG_NAME Data type name; variable that contains the name of the trigger actually fired. TG_WHEN Data type text; a string of either BEFORE or AFTER depending on the trigger's definition. TG_LEVEL Data type text; a string of either ROW or STATEMENT depending on the trigger's definition. TG_OP Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired. TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. TG_RELNAME Data type name; the name of the table that caused the trigger invocation. TG_NARGS Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement. TG_ARGV[] Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indices (less than 0 or greater than or equal to tg_nargs) result in a null value. A trigger function must return either null or a record/row value having exactly the structure of the table the trigger was fired for. Row-level triggers fired BEFORE may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated (but has no direct effect in the DELETE case). To alter the row to be stored, it is possible to replace single values directly in NEW and return the modified NEW, or to build a complete new record/row to return. The return value of a BEFORE or AFTER statement-level trigger or an AFTER row-level trigger is always ignored; it may as well be null. However, any of these types of triggers can still abort the entire operation by raising an error. Example 37-1 shows an example of a trigger procedure in PL/pgSQL. Example 37-1. A PL/pgSQL Trigger Procedure This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it checks that an employee's name is given and that the salary is a positive value. CREATE TABLE emp ( empname text, salary integer, last_date timestamp, last_user text ); CREATE FUNCTION emp_stamp() RETURNS trigger AS ' BEGIN -- Check that empname and salary are given IF NEW.empname IS NULL THEN RAISE EXCEPTION ''empname cannot be null''; END IF; IF NEW.salary IS NULL THEN RAISE EXCEPTION ''% cannot have null salary'', NEW.empname; END
[GENERAL] Order of Update - Second Try
Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I have a plpgsql function which makes updates to a set of records across two tables in a One to Many relationship hereinafter referred to as parent and child. The parent record contains two important sets of information that is relative to this process. The first is data that links the children to the parent. The second is data that links the parent to the to a third table that we will call totals. The update to the totals table is accomplished by the execution of a trigger that is fired as a result of updating the child records. The function updates the child records in a loop and then, at the bottom of the function, the parent table is updated, setting to null the values that link it to the totals table. The problem is that the update to the parent table is occurring first; then, the updates to the child records are occurring. This is causing the totals table NOT to be updated due to the fact that the information needed by the trigger to find that parent has already been removed. I have proved that the above is happening by placing various RAISE NOTICE messages in triggers on the tables involved. I can work around this by splitting the update into two transactions but I would like to understand why this is happening. I would like to recognize what circumstances might cause this to happen. In all my work with PostgreSQL, so far, I haven't seen this kind of behavior. I have tried to represent the table relationship below. Also, I point out that batch and chkno are the two elements of a UNIQUE index on the parent table. Is this the reason for the behavior? parent -- order_num, batch, chkno batch and chkno are set to null at the bottom of the function. child --- order_num, apply amount apply amount is what is updated on the child. Totals Table - batch, chkno, [...], [...] rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) TIA -- Quote: 65 A vote is like a rifle: its usefulness depends upon the character of the user. --Theodore Roosevelt Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Order of Update - Second Try
Thanks for the response. I'm working on a simplified example now. It will take a little time to set it up. I will post all the code for creating the much simplified tables and trigger. Again, thanks for the response... On Tuesday 21 March 2006 08:15 am, Martijn van Oosterhout saith: On Tue, Mar 21, 2006 at 06:56:23AM -0500, Terry Lee Tucker wrote: Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I think one of the reasons why you're not getting any responses is that the problem is complicated but you have not provided a complete example. You don't for example say how the trigger is defined (BEFORE|AFTER) (ROW|STATEMENT) DEFERRED yes/no? If you really want an answer you're going to need to provide a complete example people can run on their own systems. have a nice day, -- Quote: 39 Posterity -- you will never know how much it has cost my generation to preserve your freedom. I hope you will make good use of it. --John Quincy Adams Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Order of Update - Second Try
Ok, find attached a script called test.sql that will create three tables called parent, child, and totals. It will create a simple AFTER UPDATE trigger on child and a BEFORE trigger on parent simply to show that the values of batch and chkno are set to NULL right in the beginning. Just load the thing in with the \i command. There is a function created called myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you execute the function, you will find that parent.total is zero, child.apply_amt for each record is zero, but totals is still set to 1500. It should be 1000. Version info: rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Any insight is appreciated. TIA On Tuesday 21 March 2006 09:11 am, Terry Lee Tucker saith: Thanks for the response. I'm working on a simplified example now. It will take a little time to set it up. I will post all the code for creating the much simplified tables and trigger. Again, thanks for the response... On Tuesday 21 March 2006 08:15 am, Martijn van Oosterhout saith: On Tue, Mar 21, 2006 at 06:56:23AM -0500, Terry Lee Tucker wrote: Hello List: I posted a question over the weekend regarding this issue but I failed to communicate effectively what the problem is. I was weary :o[ Let me try again. I think one of the reasons why you're not getting any responses is that the problem is complicated but you have not provided a complete example. You don't for example say how the trigger is defined (BEFORE|AFTER) (ROW|STATEMENT) DEFERRED yes/no? If you really want an answer you're going to need to provide a complete example people can run on their own systems. have a nice day, -- Quote: 39 Posterity -- you will never know how much it has cost my generation to preserve your freedom. I hope you will make good use of it. --John Quincy Adams Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend CREATE TABLE parent ( order_num INT, batch INT, chkno VARCHAR, total INT ) WITHOUT OIDS; CREATE INDEX batch_chkno ON parent (batch, chkno); CREATE TABLE child ( recid INT, order_num INT, apply_amt INT ) WITHOUT OIDS; CREATE TABLE totals ( batch INT, chkno VARCHAR, amount INT, UNIQUE (batch, chkno) ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION parent_func () RETURNS TRIGGER AS ' DECLARE BEGIN IF TG_OP = ''UPDATE'' THEN RAISE NOTICE ''%: batch: % chkno: %'', TG_NAME, new.batch, new.chkno; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER parent_preupd BEFORE INSERT OR UPDATE ON parent FOR EACH ROW EXECUTE PROCEDURE parent_func (); CREATE OR REPLACE FUNCTION child_func () RETURNS TRIGGER AS ' DECLARE parentRec RECORD; -- parent record buffer BEGIN IF TG_OP = ''UPDATE'' THEN /* find the parent record. */ SELECT INTO parentRec batch, chkno FROM parent WHERE order_num = new.order_num; RAISE NOTICE ''%: parentRec.batch: % parentRec.chkno: %'', TG_NAME, parentRec.batch, parentRec.chkno; /* update the parent */ UPDATE parent SET total = total - old.apply_amt WHERE order_num = new.order_num; /* update totals */ UPDATE totals SET amount = amount - old.apply_amt WHERE batch = parentRec.batch AND chkno = parentRec.chkno; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER child_write AFTER INSERT OR UPDATE ON child FOR EACH ROW EXECUTE PROCEDURE child_func (); CREATE OR REPLACE FUNCTION myfunc (integer) RETURNS void AS ' DECLARE tripALIAS FOR $1; -- order_num childRecRECORD; BEGIN FOR childRec IN SELECT * FROM child WHERE order_num = trip LOOP UPDATE child SET apply_amt = 0 WHERE recid = childRec.recid; END LOOP; UPDATE parent SET batch = NULL, chkno = NULL WHERE order_num = trip; RETURN; END; ' LANGUAGE plpgsql; /* Insert data. */ INSERT INTO totals (batch, chkno, amount) VALUES (100, 'BR-549', 1500); INSERT INTO parent (order_num, batch, chkno, total) VALUES (99, 100, 'BR-549', 500); INSERT INTO child (recid, order_num, apply_amt) VALUES (1, 99, 100); INSERT INTO child (recid, order_num, apply_amt) VALUES (2, 99, 100); INSERT INTO child (recid, order_num, apply_amt) VALUES (3, 99, 100); INSERT INTO child (recid, order_num, apply_amt) VALUES (4, 99, 100); INSERT INTO child (recid, order_num
Re: [GENERAL] Order of Update - Second Try
Stephan, Thanks for the reply. We will be upgrading to version 8.x in the third quarter of the year. As I said, I have a workaround. I just wanted to know why it was behaving that way and you provided that. Thanks for the help. On Tuesday 21 March 2006 11:36 am, Stephan Szabo saith: Ok, find attached a script called test.sql that will create three tables called parent, child, and totals. It will create a simple AFTER UPDATE trigger on child and a BEFORE trigger on parent simply to show that the values of batch and chkno are set to NULL right in the beginning. Just load the thing in with the \i command. There is a function created called myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you execute the function, you will find that parent.total is zero, child.apply_amt for each record is zero, but totals is still set to 1500. It should be 1000. Version info: rnd=# select version(); version - - PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Any insight is appreciated. I think in 8.0 or later it'd do what you want, however IIRC in 7.4 the after triggers are delayed until after the full execution of the function myfunc (ie, at the end of the outer statement). ---(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 -- Quote: 71 When the Lord calls me home, whenever that may be, I will leave with the greatest love for this country of ours and eternal optimism for its future. I now begin the journey that will lead me into the sunset of my life. I know that for America there will always be a bright dawn ahead. --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
