[GENERAL] plpgsql and insert
Hi, Have a quick question is possible to record a primary from a insert stament eg xprimary := insert into schema.table(.,.,.,.) VALUES (); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] OS X Kernel settings
Is it possible to set shared memory settings on the fly in OS X like you can in Linux e.g. sysctl -w kernel.shmmax=134217728 I have tried sysctl -w kern.sysv.shmmax=134217728 with no luck, i know you can edit /etc/sysctl.conf file. But i would sooner set these settings as part of the postgresql control script. Jamie ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] OS X Kernel settings
Is this a safe setting for g5 1gig ram Os 10.4.9 sysctl -w kern.sysv.shmmax=167772160 sysctl -w kern.sysv.shmmin=1 sysctl -w kern.sysv.shmmni=32 sysctl -w kern.sysv.shmseg=8 sysctl -w kern.sysv.shmall=65536 ---(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] PostgreSql replication and load balancing ( is Slony-I a solution?)
Alexander Staubo wrote: On 5/1/07, Jan Bilek <[EMAIL PROTECTED]> wrote: Is Slony-I capable of load balancing and how to set it up? We searched the web and some people mentioned that Slony-I could do load balancing, but haven't found how to make Slony-I to do it. You might want to check pgcluster out http://pgcluster.projects.postgresql.org/ witch does both. ---(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] Time Zone Error
Hi, We currently running Windows 2003 issues with time zone set to (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have with Postgresql 8.1.5 is that time zone is being reported with a incorrect off set –4 not –5 and this is causing incorrect time to be set. Below is example out put Windows Time 2007-05-17 00:55:32.327-05 Postgresql Time 2007-05-17 01:55:32.327-04 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Time Zone Error
Richard Huxton wrote: Jamie Deppeler wrote: Hi, We currently running Windows 2003 issues with time zone set to (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have with Postgresql 8.1.5 is that time zone is being reported with a incorrect off set –4 not –5 and this is causing incorrect time to be set. Below is example out put Windows Time 2007-05-17 00:55:32.327-05 Postgresql Time 2007-05-17 01:55:32.327-04 1. These are the same absolute times. Yes 2. Do you have some sort of daylight-saving flag set somewhere? No it was the first thing i checked -- *Jamie Deppeler * Programmer *once:technologies pty ltd * 46 Roseneath Street North Geelong Victoria 3215 Australia Ph: +61 3 5278 6699 Fax: +61 3 5278 6166 Email: [EMAIL PROTECTED] Web: http://www.oncefabrik.com <http://www.oncefabrik.com/> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] update problem in triggers
I am having with this simple trigger i wrote, worked when i created it but now i get this error "Stack depth Limit Exceeded" If someone could tell me what i am doing wrong i would be greatfull Trigger CREATE TRIGGER "updateKeys" AFTER UPDATE ON "projects"."resource" FOR EACH ROW EXECUTE PROCEDURE "projects"."setParentKeysResourceUpdate"(); Function CREATE OR REPLACE FUNCTION "projects"."setParentKeysResourceUpdate" () RETURNS trigger AS $body$ declare projectcursor refCursor; prop RECORD; begin if new.fkproject is null then open projectcursor FOR SELECT * from projects.component WHERE projects.component."primary" = new.fkcomponent; FETCH projectcursor into prop; update projects.resource set fkproject = prop.fkproject, fkproposition = prop.fkproposition, fkjob = prop.fkjob where projects.resource."primary" = new."primary"; close projectcursor; end if; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---(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] Autogenerated backup of a password protected database
Hi, Since pg_dump doesnt support password is there a way that password can be supplied -w option. Currently writting a application to do backups in java as i need a solution that is cross platform. Any help would be greatfully recieved. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Updating within Triggers
Hi trying to wtite a trigger to update summary fields in a seperate table to do this i am planning on using trigger. problem i have at the moment the update trigger doesnt seem to be fireing but the insert works. Trigger CREATE TRIGGER "setSummary" AFTER INSERT OR UPDATE ON "parts" FOR EACH ROW EXECUTE PROCEDURE "material"(); function CREATE OR REPLACE FUNCTION "partSumm" () RETURNS trigger AS $body$ begin update project actualsummmatcost = (select sum(actualcost) from projects.material where material."fkproject" = new.fkproject;), actualsummmattotal = (select sum(actualcharge) from projects.material where material."fkprojet" = new.fkproject;), where project."primary" = new.fkproject; return new; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] getGeneratedKeys()
Hi to all, I have one problem with PostgreSQL and Java. I have a table with Primary key(serial) field, but after I insert a record i am unable to retrieve this value. I have tried getGeneratedKeys() and i get nothing returned. There is another method to retrieve this field? thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] converting to hex
Hi,
I am trying to write a function that will allow a postgres to convert a
text string into a hex value but there doesnt seem a function to do it,
only one i could find is to_hex(number) example of what i have done is
update table1
Set field2 = encode((select md5('field1')),'hex')
where new."primary" = "primary";
any help would be greatfully received
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] International Characters
Hi, We are having an issue importing international characters into postgresql 8. example Renée The error message we get is ERROR: invalid byte sequence for encoding "UNICODE": 0xe92044 Any help would be greatfully recieved Jamie ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Issue with OS X
Hi, I am trying to install postgresql 8 on a G5 OS X machine and cannot get past this error i have tried changing the shared buffer with no luck. Error message: /usr/local/pgsql/data postgres$ FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget(key=5432001, size=10338304, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 10338304 bytes), reduce PostgreSQL's shared_buffers parameter (currently 1000) and/or its max_connections parameter (currently 100). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql books
I would recomend Postgresql by Korry and Susan Douglas, I think most people use this as there resource :) I think a new version for postgres 8 is comming out soon ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Executing SQL Script
This is a simple question what is the best way to execute a SQL script, this script will create schemas and tables for a database i created. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] SET AUTOCOMMIT TO OFF is no longer supported
Hi, I am application that we have built using postgresql 8.0.3 and tomcat 5.0.28. In the java code i have put a state set autocomit to false which should be fine but comes up with the error SET AUTOCOMMIT TO OFF is no longer supported. I have tried using 2 drivers 7.4 and 8 jdbc driver stack trace java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported Stack trace:QueryExecutor.java :: org.postgresql.core.QueryExecutor.execute() <131> AbstractJdbc1Connection.java :: org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL() <482> AbstractJdbc1Connection.java :: org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL() <461> AbstractJdbc1Connection.java :: org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit() <957> DelegatingConnection.java :: org.apache.commons.dbcp.DelegatingConnection.setAutoCommit() <268> PoolingDriver.java :: org.apache.commons.dbcp.PoolingDriver$PoolGuardConnectionWrapper.setAutoCommit() <353> SQLDataSource.java :: com.once.server.data.source.sql.SQLDataSource.executeInsertCommand() <540> DataWrapper.java :: com.once.server.data.DataWrapper.executeQuery() <199> ActionSQL.java :: com.once.ActionSQL.doPost() <142> HttpServlet.java :: javax.servlet.http.HttpServlet.service() <760> HttpServlet.java :: javax.servlet.http.HttpServlet.service() <853> ApplicationFilterChain.java :: org.apache.catalina.core.ApplicationFilterChain.internalDoFilter() <237> ApplicationFilterChain.java :: org.apache.catalina.core.ApplicationFilterChain.doFilter() <157> StandardWrapperValve.java :: org.apache.catalina.core.StandardWrapperValve.invoke() <214> StandardValveContext.java :: org.apache.catalina.core.StandardValveContext.invokeNext() <104> StandardPipeline.java :: org.apache.catalina.core.StandardPipeline.invoke() <520> StandardContextValve.java :: org.apache.catalina.core.StandardContextValve.invokeInternal() <198> StandardContextValve.java :: org.apache.catalina.core.StandardContextValve.invoke() <152> StandardValveContext.java :: org.apache.catalina.core.StandardValveContext.invokeNext() <104> StandardPipeline.java :: org.apache.catalina.core.StandardPipeline.invoke() <520> StandardHostValve.java :: org.apache.catalina.core.StandardHostValve.invoke() <137> StandardValveContext.java :: org.apache.catalina.core.StandardValveContext.invokeNext() <104> ErrorReportValve.java :: org.apache.catalina.valves.ErrorReportValve.invoke() <118> StandardValveContext.java :: org.apache.catalina.core.StandardValveContext.invokeNext() <102> StandardPipeline.java :: org.apache.catalina.core.StandardPipeline.invoke() <520> StandardEngineValve.java :: org.apache.catalina.core.StandardEngineValve.invoke() <109> StandardValveContext.java :: org.apache.catalina.core.StandardValveContext.invokeNext() <104> StandardPipeline.java :: org.apache.catalina.core.StandardPipeline.invoke() <520> ContainerBase.java :: org.apache.catalina.core.ContainerBase.invoke() <929> CoyoteAdapter.java :: org.apache.coyote.tomcat5.CoyoteAdapter.service() <160> Http11Processor.java :: org.apache.coyote.http11.Http11Processor.process() <799> Http11Protocol.java :: org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection() <705> PoolTcpEndpoint.java :: org.apache.tomcat.util.net.TcpWorkerThread.runIt() <577> ThreadPool.java :: org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run() <683> Thread.java :: java.lang.Thread.run() <552> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Looking for a good ERD Tool
There are probability* *a few around, but i think most people just use a flowcharting software to created designs. I know for the work that i do with postgresql i use smartdraw as it can do anything i want then i apply these designs to postgresql using EMS Manager. Rob Brenart wrote: I'm looking for a good ERD tool that's designed to work with PostgreSQL... currently I use the one found here http://www.fabforce.net/dbdesigner4/index.php for MySQL, and I just create the actual tables and such by hand... kinda of a kludge, and I lose all the reverse engineering benefits... but cest'le'vie. Anyway, would like to find a similar tool specifically designed for PostgreSQL... does one exist or is it a futile search? ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] off topic
Sorry this question is off topic. Does anyone know a good resource for tomcat tuning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problems compiling Postgresql 8.0.3 on 10.4
Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -dynamiclib -install_name /usr/local/pgsql/lib/libpq.4.dylib -compatibility_version 4 -current_version 4.0 -multiply_defined suppress fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o -L../../../src/port -lresolv -o libpq.4.0.dylib /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) make[3]: *** [libpq.4.0.dylib] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 ---(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] No user being created in os X
Hi, Having an issue with 10.4.2 at the moment when i initialize the database no user is being created. I have setup Postgresql 8.0.3 on 10.3 without any issue, has anyone come accross this problem before? ---(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] [HACKERS] No user being created during initdb for OS X
I am doing it right /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data Thomas F. O'Connell wrote: Do you already have a postgres user on the system? And do you mean that initdb is not creating a postgres user in the database? Presumably, if run as the user that will own the server process, it should create that user in the database as well. http://www.postgresql.org/docs/8.0/static/app-initdb.html -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 20, 2005, at 8:35 PM, Jamie Deppeler wrote: Hi, Having an issue with 10.4.2 at the moment when i initialize the database no user is being created. I have setup Postgresql 8.0.3 on 10.3 without any issue, has anyone come accross this problem before? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems compiling Postgresql 8.0.3 on 10.4
Vivek Khera wrote: On Jul 20, 2005, at 3:37 AM, Jamie Deppeler wrote: Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wendif-labels -fno-strict-aliasing -dynamiclib -install_name /usr/ local/pgsql/lib/libpq.4.dylib -compatibility_version 4 - current_version 4.0 -multiply_defined suppress fe-auth.o fe- connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o -L../../../ src/port -lresolv -o libpq.4.0.dylib /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) by "10.4" we shall have to guess that means MacOS X 10.4 by the name of the dynamic libraries... It does work. I just installed Pg 8.0.3 on my workstation using darwin ports (which really only just sets some funky installation location paths and does a standard build). Vivek Khera, Ph.D. +1-301-869-4449 x806 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Install Xcode development tools i had the same issue ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] stack depth limit exceeded
At the moment i am trying to execute a very simple function but i am getting the following error stack depth limit exceeded function CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS trigger AS $body$ begin update contacts.person set "contact" = new.firstname where person."primary" = new."primary"; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] stack depth limit exceeded
What i am trying to do is update the field contact with field values in firstname and lastname Trigger CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE ON FOR EACH ROW EXECUTE PROCEDURE "contacts"."addContactField"(); Procedure CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS trigger AS $body$ begin update contacts.person set "contact" = new.firstname where person."primary" = new."primary"; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Tom Lane wrote: Jamie Deppeler <[EMAIL PROTECTED]> writes: At the moment i am trying to execute a very simple function but i am getting the following error stack depth limit exceeded You didn't really show the complete context, but seeing that this is a trigger and it's trying to do an "UPDATE person" internally, I'll bet a nickel that the trigger itself is on update events on person, and therefore that you've written an infinite recursion. Had you shown more context, I could have given some advice on a better way to do it. If you're trying to alter the row that's about to be stored, you just have to assign to field(s) of the NEW row within the trigger. If you want to do something else, you need to explain what. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] OID's
Hi, I am planning to use OID for referencing as instead PK -->> FK on this situation would require alot of tables, OID would seen to nice solution. My worry with OID's is when i do SQL dump and rebuild the Database will OID will change making referencing certain records impossible. -- Jamie Deppeler
[GENERAL] OID
Hi, Im planning on using OID for referencing in certain part of the system i am building as in this case using normal pk -> fk would be inefficient as i have to have once table reference multi tables, but i have concerns that if a Database is exported and reconstructed the OID's will change making referencing impossible. Any ideas would helpful -- Jamie Deppeler Database Administrator Cormoran Communication Do It Once!
[GENERAL] tableoid
Hi have a bit of a issue im planning on using tableoid to select the appropate table, but im not sure that you can in sql select statement? If not is there another approch i could be using? -- *Jamie Deppeler *Database Administrator ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] off Topic: Looking for a good sql query
Hi, This post is a bit off topic im looking a good sql book can someone give me a recommendation? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] rules
This may be a bit of stupid question but it is the first time i have played around with rules and i am trying to convert a sql statement into a rule sample sql querty INSERT INTO schema.table2 ( "field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", ) VALUES ( SELECT table1."name", table1.notes, table1.hrs, table1.days, table3.value, table31.value, table4.ratename, table4.maxhrs, table4.appliesafter, table4.rate, table5.value, table5."name" FROM (inner joins) Where primary = number ) which i would like to make into a rule if possible ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Triggers
Hi,
Just doing some testing at the moment and trying to make a trigger that
consists of a insert statement which as a result of an sql querty!
Is this possible? if so could someone give me some pointers as i am
having no luck trying to get this to work, have even tried this but
still doesnt work.
begin
begin
INSERT INTO wip.resourceplan
(
"name",
"notes",
"hrs",
"glcode1",
"glcode2",
"chargeratename",
"chargeratevalue",
"servicefeename",
"servicefeevalue")
('1','2','3','4','5','6','7','8','9')
return null;
end;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Trigger Problems
Hi , i have designed a trigger function called test2(Integer)
im trying to use it with a trigger but get errors that function cannot
be found
Trigger statement is
CREATE TRIGGER "new_trigger566" BEFORE INSERT
ON "customer" FOR EACH ROW
EXECUTE PROCEDURE test2("primary");
get the error test2() cannot be found
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Trigger problem 2
Hi Finally getting this trigger ro work have one last problem, im trying to use Trigger variables sample code UPDATE wip.resource set "name" = datarecord.borname where wip.resource."primary" = OLD."primary"; get the following error Error: record "old" is not yet assigned would be greatful for any help thx begin:vcard fn:Jamie Deppeler n:Deppeler;Jamie org:Once;Development adr:;;46 Roseneath Street;North Geelong;Vic;3215;Australia email;internet:[EMAIL PROTECTED] title:Database Admin tel;work:+61 3 52278 6699 url:http://www.doitonce.net.au version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Rules
HI, Planning on witting a rule for a view, and i was wondering if anyone could suggest a good Internet resource? thx begin:vcard fn:Jamie Deppeler n:Deppeler;Jamie org:Once;Development adr:;;46 Roseneath Street;North Geelong;Vic;3215;Australia email;internet:[EMAIL PROTECTED] title:Database Admin tel;work:+61 3 52278 6699 url:http://www.doitonce.net.au version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Function Problem
Problem I am trying to store value in a TEMPORARY table and I am getting the following error ERROR: relation with OID 51533 does not exist Trigger CREATE TRIGGER "createtemporytable" AFTER INSERT ON "component" FOR EACH ROW EXECUTE PROCEDURE "createtemp"(); Function begin CREATE temporary TABLE primarykey ( componentpk Integer, plannerpk Integer, materialplanpk Integer, resourceplanpk Integer ); INSERT INTO primarykey(componentpk) VALUES (new."primary"); UPDATE component set "notes" = 'Updated' where component."primary" = primarykey.componentpk; end ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Rules
Hi, What i am trying to do is i have a join table eg primarykey field1 field2 and based on SQL Select have the values of field1 inserted into a new table which will scroll which will launch a trigger but im having problems getting this to work? Any ideas anyone? sql select may return 1 or more results so i will 1 insert statements ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Detecting Temporary Tables
Is it possible to detect the instance of a Temporary through a function? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] More problems
Hi, Newbie question I have a result set returned through a function or rule, i know i will have process the result set through a for loop, my question is how can i get length of result set? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] MD5
Hi, I was just wondering is it possible to encrypt a filed in the database with md5? i know it is possible to do it with DB users ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] MD5
Tino Wildenhain wrote: Hi, Am Freitag, den 17.12.2004, 09:41 +1100 schrieb Jamie Deppeler: Hi, I was just wondering is it possible to encrypt a filed in the database with md5? i know it is possible to do it with DB users No. You cannot encrypt with md5 because you cant decrypt. md5 is a hash function. But you can use it any time to hash values you insert. Regards Tino Well basically i want to store and hashed value that will never be changed just compaired too hashed values also when i try to encrpyt a field i get this error encypt(text,"unknown","unknown")
[GENERAL] sorting problem
Problem i am having at the moment i cant get a true alpha sort to work as Order By is sorting A..Z then a..z where i need aA..zZ sort independant of case. SQL Query SELECT * FROM person WHERE (salutation LIKE '%To%') ORDER BY person.lastname Results Ahsteit Bloggs Cap Carrey Diver Duckula Goldsworthy Gruff Harmony Hassleberry-flop-flop Heyheagle Jahoosal Straindove Yorrick of Finchery ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Detecting Temporary Tables
What i want to be able to do is create a tempory table and when i need a value stored in the table i want to check that the table has been created if it has not do a create temp table or just do an update. Michael Fuhr wrote: On Tue, Dec 07, 2004 at 09:22:51AM +1100, Jamie Deppeler wrote: Is it possible to detect the instance of a Temporary through a function? What are you trying to do? Temporary tables are stored in the pg_temp_NNN schema (e.g., pg_temp_1). See the "System Catalogs" chapter in the PostgreSQL documentation for information on finding a table's schema. If you're using 7.4 or later then see also the "Information Schema" chapter -- one of its views has two fields that should be helpful. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] problems with date
Hi, having a small problem in a trigger i want to automatically update a date field but when i use for example update table set field = current_date where table.pk = new.pk or update table set field = now() where table.pk = new.pk this crashes think link between server and db, i have tried using these commands on a command line and they work ok thanks for any help again JD ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] replicator
Can anyone suggest good open source replicator system for pgsql Thanks JD ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL 8 on windows very slow
Daniel Schuchardt wrote: lol wrote: Hi, I'm currently testing several databases for an application written in Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000 SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT request. Have-you seen this problem ? May be some parameters should be adjusted. What should I check ? Thanks I think first you should use a newer RC. In the first RC there were some probs... With RC3 I don't have such probs. Sure If I compared Win and Linux PG and ma results where that LINUX is about 3 times faster under high access. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org I don't have any probs with RC3 either. I was wondering if the PostgreSQL community has any support for gay users? Does the Linux version have gay mode and is it faster? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] update in triggers
Hi, Think im doing something wrong here, cant seem to resolve the problem i have a trigger which is calling a update function and when it gets to a update it goes into a infinite loop code Trigger CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE ON "chargeratetest" FOR EACH ROW EXECUTE PROCEDURE "chargeratetest"(); function CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS' begin UPDATE chargeratetest set notes=''hello'' where new."primary" = chargeratetest."primary"; return null; end; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; would be greatful for any help thx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] update in triggers
What i am trying to do is to update a field based on a sql query set notes='hello' is just being used as a test but i can not seem to make this simple update work Michael Fuhr wrote: On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote: Think im doing something wrong here, cant seem to resolve the problem i have a trigger which is calling a update function and when it gets to a update it goes into a infinite loop recursion, noun. See recursion. CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE ON "chargeratetest" FOR EACH ROW EXECUTE PROCEDURE "chargeratetest"(); CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS' begin UPDATE chargeratetest set notes=''hello'' where new."primary" = chargeratetest."primary"; The trigger says to call the function after every insert or update on the table. Suppose you insert a record into the table. The trigger calls the function and the function executes UPDATE. The update causes the trigger to call the function, which executes UPDATE so the trigger calls the function, which executes UPDATE so the trigger calls the function, and so on. Infinite recursion. What are you trying to do? What's the trigger's purpose? return null; end; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; A function that has side effects like updating a table should be VOLATILE, not IMMUTABLE.
[GENERAL] Catching delete
Hi have a pretty simple question i know its possible to catch a delete statement through a trigger is it possible to prevent the delete from happing through the same trigger function? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] PIT Recovery
Does any one know if this can be applied to windows server as through the beta this only worked on linux based machines. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Deleting Problem
Here is my problem I have a level structure which is 5 levels deep with 6 tables, for this example i will call it table1,table2,table3,table4,table5,table6 (1)table1 (2)table2 (3)table3 (4)table4 (5)table5,table6 (6)table7,table8 table5 and table6 have fk keys pointing to table1,table2,table3,table4. table7 and table 8 have fk keys pointing to table5. Here is my problem i have delete triggers on table5 and table6 which update summary information on table1,table2,table3,table4 if table5 or 6 gets delete, problem i am faced with is when eg table1 record gets deleted i get the following error Error insert or update on table5 violates foreign key constraint "table4". They are all link with a cascade delete. Machine Windows 2003, Postgresql 8.1.4 Any help would be useful. Thank you. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgresql 8.3 Installer issue
Hi, I am trying to build a new installer application. I am in the process of upgrading postgresql 8.1 to 8.3.3 but i am having a issue which i can't seemed to resolve. Error output from rpmbuilder + su -c - user'$RPM_BUILD_ROOT/usr/local/app/pgsql/bin/postmaster -D $RPM_BUILD_ROOT/usr/local/app/pgsql/data -S ' /var/tmp/app-root/usr/local/app/pgsql/bin/postmaster: option requires an argument -- S Try "postmaster --help" for more information. error: Bad exit status from /var/tmp/rpm-tmp.12297 (%install) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need some help
trying to install Postgresql 8.3 and i keep getting these errors libodbc.so is needed libodbcinst.so is needed Hopefully someone can help me -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Making a RPM installer
Hi, Not sure if this the correct place to post this issue but hopefully someone can help. I am building a rpm for RHEL 5 The rpm contains a compiled version for postgresql-8.3.3 and tomcat 6 and java 1.6. When i try to install the rpm on blank test system i get the following dependence issues libodbc.so is needed libodbcinst.so is needed Postgresql has been compiled with the following line ./configure --without-readline --without-zlib Any help would be greatly received. -- 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] Making a RPM installer
There is a few reason why I am making this installer 1. Need to make a installer that just uses one rpm file 2. It needs to come with a prebuild database 3. Custom changes have been made to tomcat configuration Joshua D. Drake wrote: On Thu, 2008-07-10 at 10:28 +1000, Jamie Deppeler wrote: Hi, Not sure if this the correct place to post this issue but hopefully someone can help. Any reason why you just wouldn't use: http://www.pgsqlrpms.org and http://www.jpackage.org/ Sincerely, Joshua D. Drake I am building a rpm for RHEL 5 The rpm contains a compiled version for postgresql-8.3.3 and tomcat 6 and java 1.6. When i try to install the rpm on blank test system i get the following dependence issues libodbc.so is needed libodbcinst.so is needed Postgresql has been compiled with the following line ./configure --without-readline --without-zlib Any help would be greatly received. -- 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] Making a RPM installer
Tried to use "ldd" but i am getting not a dynamic executable Tom Lane wrote: Jamie Deppeler <[EMAIL PROTECTED]> writes: I am building a rpm for RHEL 5 The rpm contains a compiled version for postgresql-8.3.3 and tomcat 6 and java 1.6. When i try to install the rpm on blank test system i get the following dependence issues libodbc.so is needed libodbcinst.so is needed You must be including more than you said, because AFAIK none of those components should depend on libodbc. Try going through all the .so files included in the RPM with "ldd" to see which one(s) depend on these libraries. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Delete Problem
Just today i have noticed i have one certain table that i cannot delete any records from 1 have delete all Triggers, Vacuumed the tables removed all foreign keys that linked to this table. -- *Jamie Deppeler *Database Administrator *once:technologies pty ltd * *Do It Once!* 46 Roseneath Street North Geelong Victoria 3215 Australia Ph: +61 3 5278 6699 Fax: +61 3 5278 6166 Email: [EMAIL PROTECTED] Web: http://www.oncefabrik.com <http://www.oncefabrik.com/> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Delete Problem
Michael Fuhr wrote: [Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Thu, Jul 13, 2006 at 03:32:43PM +1000, Jamie Deppeler wrote: Michael Fuhr wrote: On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote: Just today i have noticed i have one certain table that i cannot delete any records from What happens when you try to delete? Do you get an error? Does the delete succeed but report zero rows deleted? Something else? What does "EXPLAIN ANALYZE DELETE ..." show? Is the delete done in a transaction that doesn't commit? Delete is performed without any errors or warnings but when i refresh the found set it is still in the table What do you mean by "refresh the found set"? What client interface are you using? Might the deleting transaction not be committing? What happens if you do the delete using psql? What version of PostgreSQL are you running? From psql db=# select "primary" from job where "primary" = 370; primary - 370 (1 row) db=# delete from job where "primary" = 370; DELETE 0 DB is Postgresql 8.0 OS is Debian ---(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] Delete Problem
db=# \d job
Table "job"
Column|Type
| Modifiers
-+-+
primary | integer | not null default
nextval('job_primary_seq'::regclass)
jobnumber | text|
jobname | text|
jobdetails | text|
Indexes:
"job_pkey" PRIMARY KEY, btree ("primary")
Michael Fuhr wrote:
On Thu, Jul 13, 2006 at 04:28:50PM +1000, Jamie Deppeler wrote:
From psql
db=# select "primary" from job where "primary" = 370;
primary
-
370
(1 row)
db=# delete from job where "primary" = 370;
DELETE 0
You said that you had deleted all triggers -- have you verified
that none remain? Does the table have any rules? What does
"\d job" show?
---(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] Delete Problem
Not sure if it disk space as i have around 10gig free surabhi.ahuja wrote: even i have seen this problem i am using postgres 8.0.0 i open psql and there i try to do delete from it seems that psql gets stuck. even after 5 mins or something, no deletion happens. generally this happens when the disk is nearing to full cant this be avoided, why does postgres hang. thanks, regards Surabhi *From:* [EMAIL PROTECTED] on behalf of Michael Fuhr *Sent:* Thu 7/13/2006 11:48 AM *To:* Jamie Deppeler *Cc:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] Delete Problem *** Your mail has been scanned by InterScan VirusWall. ***-*** [Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Thu, Jul 13, 2006 at 03:32:43PM +1000, Jamie Deppeler wrote: > Michael Fuhr wrote: > >On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote: > >>Just today i have noticed i have one certain table that i cannot delete > >>any records from > > > >What happens when you try to delete? Do you get an error? Does > >the delete succeed but report zero rows deleted? Something else? > >What does "EXPLAIN ANALYZE DELETE ..." show? Is the delete done > >in a transaction that doesn't commit? > > Delete is performed without any errors or warnings but when i refresh > the found set it is still in the table What do you mean by "refresh the found set"? What client interface are you using? Might the deleting transaction not be committing? What happens if you do the delete using psql? What version of PostgreSQL are you running? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 12/07/2006 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Delete Problem
PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3
20060104 (prerelease) (Debian 4.0.2-6)
Michael Fuhr wrote:
On Thu, Jul 13, 2006 at 04:58:26PM +1000, Jamie Deppeler wrote:
db=# \d job
Table "job"
Column|Type
| Modifiers
-+-+
primary | integer | not null default
nextval('job_primary_seq'::regclass)
jobnumber | text|
jobname | text|
jobdetails | text|
Indexes:
"job_pkey" PRIMARY KEY, btree ("primary")
You said you were running 8.0 but "nextval('job_primary_seq'::regclass)"
suggests 8.1. What does "SELECT version()" show?
What are the output of the following?
EXPLAIN ANALYZE SELECT "primary" FROM job WHERE "primary" = 370;
EXPLAIN ANALYZE DELETE FROM job WHERE "primary" = 370;
If these commands use index or bitmap index scans, do you get
different results if you execute the following commands and then
try the delete again?
SET enable_indexscan TO off;
SET enable_bitmapscan TO off;
--
*Jamie Deppeler
*Database Administrator
*once:technologies pty ltd
*
*Do It Once!*
46 Roseneath Street
North Geelong
Victoria 3215 Australia
Ph: +61 3 5278 6699
Fax: +61 3 5278 6166
Email: [EMAIL PROTECTED]
Web: http://www.oncefabrik.com <http://www.oncefabrik.com/>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Function Help
Hi, I am having a problem with a delete function. The delete function uses 3 tables (tablea,tableb,tablec) tablec stores actual values and tablea+b stores summaries when a entry in tablec is deleted i updated the values in tablea+b, The problem when i do a cascade delete on tablea tablec delete trigger generates a error saying it cannot find the record in tablea. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
