[GENERAL] 2 questions about types
1. i have a few funcions that depend on a type. i don't want to have to srop every function just so I can drop the type and recreat everything. Is there a better way to do this in Postgres? 2. The reason I had to create my own type was because record didn't ork for me when I was selecting data across multiple tables. I thought it should be dynamic but it only seems to work if i select all data in one table. I need 2-3 columns from multiple tables. Is there a better way to do this in Postgres? I am using Suse with Postgres 7.4.2 but am considering an upgrade to 8.0 Thank you, Jason Tesser ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 2 questions about types
OK here is an example of a function where I had to create a type called login. How could I have written this function without having to create a type. CREATE OR REPLACE FUNCTION "public"."loginbyindidget" (integer) RETURNS SETOF "public"."login" AS' declare iindid alias for $1; returnRec RECORD; begin for returnRec in select tblindividual.indid, tblindividual.title, tblindividual.firstname, tblindividual.middlename, tblindividual.lastname, tblindividual.suffix, tblloginname.loginname, tblloginname.loginnameid, tblloginname.ad,tblloginname.current, tblloginname.email, tblloginname.note from tblindividual inner join tblloginname on (tblindividual.indid = tblloginname.indlink) where tblloginname.indlink = iindid order by tblindividual.lastname, tblindividual.firstname, tblindividual.middlename, tblloginname.loginname loop return next returnRec; end loop; return; end; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; On Wed, 2005-03-16 at 13:51 +, Richard Huxton wrote: > Jason Tesser wrote: > > 1. i have a few funcions that depend on a type. i don't want to have to > > srop every function just so I can drop the type and recreat everything. > > Is there a better way to do this in Postgres? > > Not really - if you're redefining the type then the functions really > have to be recreated. I try to keep related objects in the same file, so > I can re-run them all together. > > > 2. The reason I had to create my own type was because record didn't ork > > for me when I was selecting data across multiple tables. > > I thought it should be dynamic but it only seems to work if i select all > > data in one table. I need 2-3 columns from multiple > > tables. > > Is there a better way to do this in Postgres? > > Could you give more details of what you're trying? RECORD variables in > functions should work fine. > > -- >Richard Huxton >Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 2 questions about types
>
> There's an example in the manuals - chapter "7.2.1.4. Table Functions"
>
> SELECT *
> FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
>AS t1(proname name, prosrc text)
> WHERE proname LIKE 'bytea%';
>
> So basically, you need to supply the type definitions in your SELECT if
> you aren't going to supply it in the function definition.
ok I tried to rewrite as follows but I get an error that says "a column
definition list is required fro functions returning record
here is my function and call for it now
CREATE OR REPLACE FUNCTION "public"."loginbyindidgettest" (integer)
RETURNS SETOF "pg_catalog"."record" AS'
declare
iindid alias for $1;
returnRec RECORD;
begin
for returnRec in select t1.indid, t1.title, t1.firstname, t1.middlename,
t1.lastname, t1.suffix, t1.loginname, t1.loginnameid, t1.ad,t1.current,
t1.email, t1.note
from tblindividual inner join tblloginname on (tblindividual.indid =
tblloginname.indlink) as t1
where tblloginname.indlink = iindid
order by tblindividual.lastname, tblindividual.firstname,
tblindividual.middlename, tblloginname.loginname
loop
return next returnRec;
end loop;
return;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from loginbyindidgettest(43650);
---(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] problems with types after update to 8.0
I just upgraded 7.4 to 8.0 and I have noticed that i am getting alot of error that are saying i have a type problem. For example it is saying big int expected but it was sent character varying. These same queries use to work in 7.4 I am using java and jboss. My queries are all dynamic sql in prepared statements. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] problems with types after update to 8.0
Hi Richard and thank you for your help. Here is the actual message from the pg log ERROR: 42804: column "datetimein" is of type timestamp without time zone but expression is of type character varying HINT: You will need to rewrite or cast the expression. I can aviod this by rewritting my queries and casting all the stuff though I don't want to have to do this. I am trying to figure out what is happening so I can find a proper solution, like I said this all worked with pg 7.4 Here is my prepared statment in my java class private static final String MANUALINSERT = "insert into pactime (datetimein, pacpayperiodlink, wslink, deptlink, commment, type) " + "values ?,?,?,?,?,'man') "; private static final String DATEOUTUPDATE = "update pactime set datetimeout = timestamp, commment = ?, type='man' where pactimeid =?"; and here si the way to fix but there are too many queires to have to change them all. private static final String MANUALINSERT = "insert into pactime (datetimein, pacpayperiodlink, wslink, deptlink, commment, type) " + "values (cast(? as timestamp),?,?,?,?,'man') "; private static final String DATEOUTUPDATE = "update pactime set datetimeout = cast(? as timestamp), commment = ?, type='man' where pactimeid =?"; here is where I am executing the statement in java stmt = con.prepareStatement(DATEOUTUPDATE); stmt.setString(1, dateout); stmt.setString(2, comment); stmt.setString(3, pactimeid); On Tue, 2005-06-21 at 09:07 +0100, Richard Huxton wrote: > Jason Tesser wrote: > > I just upgraded 7.4 to 8.0 and I have noticed that i am getting alot > > of error that are saying i have a type problem. For example it is > > saying big int expected but it was sent character varying. > > Is it right? Do you have an example you could give? > > > These > > same queries use to work in 7.4 I am using java and jboss. My > > queries are all dynamic sql in prepared statements. > > I'm guessing something is blindly quoting all values. Seems unlikely > that it's the jdbc driver. > > Examples, please. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] problems with types after update to 8.0
HI > > Are you sure the issue is the change from PG7.4=>PG8.0, or have you > upgraded your jdbc package at the same time? I have upgraded the driver to the version that matched pg 8.0 > > Here is my prepared statment in my java class > > > > private static final String MANUALINSERT = > > "insert into pactime (datetimein, pacpayperiodlink, wslink, > > deptlink, commment, type) " + > > "values ?,?,?,?,?,'man') "; > > > and here si the way to fix but there are too many queires to have to > > change them all. > > > > private static final String MANUALINSERT = > > "insert into pactime (datetimein, pacpayperiodlink, wslink, > > deptlink, commment, type) " + > > "values (cast(? as timestamp),?,?,?,?,'man') "; > > > here is where I am executing the statement in java > > > > stmt = con.prepareStatement(DATEOUTUPDATE); > > stmt.setString(1, dateout); > > stmt.setString(2, comment); > > stmt.setString(3, pactimeid); > > Hmm - should this not be something like: >stmt.setTimestamp(1,dateout) / stmt.setDateTime(1,dateout) That would be a better :-) way to do it but for now I am just trying to deal with the code that is there :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Possible move away from PG
I work for a college and we use PG currently as our main backend. We are currently developing with Java. We are considering moving away from postgres for the reasons I am going to list below. I would appreciate some thoughts from the Postgres community on way we should or shouldn't leave postgres. 1. Our dev plan involves alot of stored procedures to be used and we have found the way this is done in PG to be painful. (ie. To return multiple record from different tables you have to define a type. This is a pain to maintain because if you ever have to change what it returns it cannot be dropped because of dependencies etc.. In some other databases you can simpley write a stored proc to return whatever the query inside returns and this is handled dynamically) 2. Also with stored procs it is painful to return mulitple records. The syntax is more complicated than some other databases. (We are currently using PL/SQL) 3. The tools. PgAdmin does some things well but it is lacking the features of some of the other gui tools. This is not a big deal as we do also have PgManage which is acceptable except I personally don't like it cause it doesn't run in Linux and the Linux version is pretty bad. Thank you for any input and help, Jason Tesser ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] COnsidering a move away from Postgres
I work for a college and we use PG currently as our main backend. We are currently developing with Java. We are considering moving away from postgres for the reasons I am going to list below. I would appreciate some thoughts from the Postgres community on way we should or shouldn't leave postgres. 1. Our dev plan involves alot of stored procedures to be used and we have found the way this is done in PG to be painful. (ie. To return multiple record from different tables you have to define a type. This is a pain to maintain because if you ever have to change what it returns it cannot be dropped because of dependencies etc.. In some other databases you can simpley write a stored proc to return whatever the query inside returns and this is handled dynamically) 2. Also with stored procs it is painful to return mulitple records. The syntax is more complicated than some other databases. (We are currently using PL/SQL) 3. The tools. PgAdmin does some things well but it is lacking the features of some of the other gui tools. This is not a big deal as we do also have PgManage which is acceptable except I personally don't like it cause it doesn't run in Linux and the Linux version is pretty bad. Thank you for any input and help, -- Jason Tesser Developer for NMI [EMAIL PROTECTED] Eph 2:8-10 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Possible move away from PG
I am not familar with how to do this. Could you give me an example of how this could help? > > Have you considered returing refcursors instead of setof some type. > > Kris Jurka -- Jason Tesser Developer for NMI [EMAIL PROTECTED] Eph 2:8-10 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] COnsidering a move away from Postgres
HI On Thursday 30 June 2005 9:20 am, Tom Lane wrote: > Jason Tesser <[EMAIL PROTECTED]> writes: > > 1. Our dev plan involves alot of stored procedures to be used and we have > > found the way this is done in PG to be painful. (ie. To return multiple > > record from different tables you have to define a type. > > FWIW, this won't be essential any more in 8.1. See the examples in the > development documentation: > http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-P >ARAMETERS > http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLP >GSQL-DECLARATION-ALIASES > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.ht >ml#PLPGSQL-STATEMENTS-RETURNING I might be missing it but how does this help me. What I would like is to be able to return multiple records from a select statement that return multiple columns from different tables without having to create a type. This is why it is painful for us. The management of types is bad because as far as I know there is no alter type and the depencies become a nightmane if you ever need to change something. -- Jason Tesser Developer for NMI [EMAIL PROTECTED] Eph 2:8-10 ---(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] COnsidering a move away from Postgres
Can this return multiples? I thought when you dfined columns dynamically like your example it only returns one record and I need to be able to return a set. Can your example return a set? On Thursday 30 June 2005 10:58 am, Sven Willenberger wrote: > If I understand the new features correctly, rather than: > CREATE FUNCTION foo(i int) RETURNS custom_type AS > and custom_type is (int,text,text) > you will be able to do the following instead: > CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ... > > As far as hard coding the OUT datatypes, if I understand the docs > correctly you can even: > CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z > anyelement) AS ... > > No custom type needed .. you specify how the output format in the > argument section itself. > > Sven -- Jason Tesser Developer for NMI [EMAIL PROTECTED] Eph 2:8-10 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Possible move away from PG
Ok. This sounds good to me but I have one question> In the manual for 8.0 with comments someone commented at the end of the section on frecursors that they need to be closed which the manual didn’t seem to mention will this be a problem with doing this in java? From: Pablo Baena [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 12:30 PM To: Jason Tesser Cc: Pgsql Subject: Re: [GENERAL] Possible move away from PG If found this article of help: http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=2 On 6/30/05, Jason Tesser <[EMAIL PROTECTED]> wrote: I am not familar with how to do this. Could you give me an example of how this could help? > > Have you considered returing refcursors instead of setof some type. > > Kris Jurka -- Jason Tesser Developer for NMI [EMAIL PROTECTED] Eph 2:8-10 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- > There are a lot of us out there who both do and do not work for Sun Wow! Quantum programmers!
Re: [GENERAL] COnsidering a move away from Postgres
That is very similar to what I have been trying to do. I have 1 question and one problem though. Question: DO I have to define every column I am returning as an out going parameter? Problem I ran your test and I am getting as error see below test=# create function countum(lim int, out n int, out en text, out es text) returns setof record as $$ test$# declare r record; test$# begin test$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim loop test$# n := r.f1; test$# en := r.f2; test$# es := r.k2; test$# return next; test$# end loop; test$# end $$ language plpgsql; ERROR: CREATE FUNCTION / OUT parameters are not implemented -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 3:57 PM To: Jason Tesser Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COnsidering a move away from Postgres Jason Tesser <[EMAIL PROTECTED]> writes: > I might be missing it but how does this help me. What I would like is to be > able to return multiple records from a select statement that return multiple > columns from different tables without having to create a type. You mean like this? regression=# create table t1 (f1 int, f2 text); CREATE TABLE regression=# insert into t1 values(1, 'one'); INSERT 0 1 regression=# insert into t1 values(2, 'two'); INSERT 0 1 regression=# create table t2 (k1 int, k2 text); CREATE TABLE regression=# insert into t2 values(1, 'uno'); INSERT 0 1 regression=# insert into t2 values(2, 'dos'); INSERT 0 1 regression=# create function countem(lim int, out n int, out en text, regression(# out es text) returns setof record as $$ regression$# declare r record; regression$# begin regression$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim loop regression$# n := r.f1; regression$# en := r.f2; regression$# es := r.k2; regression$# return next; regression$# end loop; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# select * from countem(2); n | en | es ---+-+- 1 | one | uno 2 | two | dos (2 rows) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] COnsidering a move away from Postgres
OK I am an idiot you are running a cvs build I guess. Which at least answers the problem. ---(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] COnsidering a move away from Postgres
Yes I figured it out could I bug one last time about my question :-) Question: DO I have to define every column I am returning as an out going parameter? You have been helpful Tom and I really do appreciate it. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 5:20 PM To: Jason Tesser Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COnsidering a move away from Postgres "Jason Tesser" <[EMAIL PROTECTED]> writes: > Problem I ran your test and I am getting as error see below This is an 8.1 feature not something that exists in current releases. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] stored proc help
I have the following store dproc but when I run it I am getting the error ERROR: invalid input syntax for integer: "(1)" CONTEXT: PL/pgSQL function "irispermissionget" line 9 at return next What am I doing wrong? CREATE OR REPLACE FUNCTION "public"."irispermissionget" (username varchar, pass varchar) RETURNS SETOF integer AS $body$ declare returnRec Record; begin for returnRec in select cast(irisuserpermission.permissionid as integer) from irisuserpermission INNER JOIN public.irisuser ON (public.irisuserpermission.irisuserid = public.irisuser.id) where irisuser.user = username and irisuser.password = pass loop return next returnRec; end loop; return; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
[GENERAL] returning the primary key value
I have a stored proc in which I want to retur the primary key of an insert statement that the stored proc just ran. How can I do that? ---(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] monitoring tools
I am looking for a tool in postgres to monitor present and past activity. Foe example in SQLServer there is a tool that reports on all queries run in the past say 2 weeks and tells you how long they took etc.. I know I can use explain in postgres but I want to be able to track my queries in a real enviroment while the apps are using them. Is there a tool that can help me? ---(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] monitoring tools
But that will just st in a huge tet sticks all data in text filess. I need a tool that will take that data and give me meaningful reports. I want to be able to get reports on queries ove rthe last 2 weeks. Which ones were slow etc.. I dont want to have to read through 2 weeks worth of logs :-) -Original Message- From: Lonni J Friedman [mailto:[EMAIL PROTECTED] Sent: Thu 12/23/2004 8:11 AM To: Jason Tesser Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] monitoring tools Sure, in postgresql.conf uncomment log_min_duration_statement and set it to whatever value you want to log. This, of course, assumes that you're already logging for the DB. On Thu, 23 Dec 2004 07:27:22 -0600, Jason Tesser <[EMAIL PROTECTED]> wrote: > I am looking for a tool in postgres to monitor present and past activity. > Foe example in SQLServer there is a tool that reports on all queries run in > the past say 2 weeks and tells you how long they took etc.. I know I can use > explain in postgres but I want to be able to track my queries in a real > enviroment while the apps are using them. Is there a tool that can help me? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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] monitoring tools
Does anyone else know of any other tool that can do what I am talking about. I can get this one to work but I would like to see a tool that is a little more robust. maybe even a gui client or something? -Original Message- From: Lonni J Friedman [mailto:[EMAIL PROTECTED] Sent: Thu 12/23/2004 8:50 AM To: Jason Tesser Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] monitoring tools Checkout pqa On Thu, 23 Dec 2004 08:14:41 -0600, Jason Tesser <[EMAIL PROTECTED]> wrote: > But that will just st in a huge tet sticks all data in text filess. I need a > tool that will take that data and give me meaningful reports. I want to be > able to get reports on queries ove rthe last 2 weeks. Which ones were slow > etc.. I dont want to have to read through 2 weeks worth of logs :-) > > > -Original Message- > From: Lonni J Friedman [mailto:[EMAIL PROTECTED] > Sent: Thu 12/23/2004 8:11 AM > To: Jason Tesser > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] monitoring tools > > Sure, in postgresql.conf uncomment log_min_duration_statement and set > it to whatever value you want to log. This, of course, assumes that > you're already logging for the DB. > > On Thu, 23 Dec 2004 07:27:22 -0600, Jason Tesser <[EMAIL PROTECTED]> wrote: > > I am looking for a tool in postgres to monitor present and past activity. > > Foe example in SQLServer there is a tool that reports on all queries run in > > the past say 2 weeks and tells you how long they took etc.. I know I can > > use explain in postgres but I want to be able to track my queries > -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] monitoring tools
maybe We don't mind the command line but my college at work doesn't like to bring in other technologies where not needed and he feels like this woulr be doing that. it is ruby so if we need to modify it we need to learn ruby. I dont mind so much I was just looking to see if anyone else had some options. Our main platform is j2ee but we do flirt with php as well. all and all though I liked pqa. Can it be run from windows? -Original Message- From: Lonni J Friedman [mailto:[EMAIL PROTECTED] Sent: Thu 12/23/2004 10:39 AM To: Jason Tesser Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] monitoring tools pqa does exactly what your original question requested. Sounds like what you really want is a GUI for pqa. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] pgodbc error
In my postgresql.conf I set it to log the query durations and this seems to goof up odbc. I know this sounds stupid but I did a search and actually found taht others have had the problem though I found no solution. I am running 7.4 on a suse 9.2 box. I have noticed that it appears to work on Windows in 8.0. Can it be fixed on 7.4? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pgodbc error
has no one been able to solve this problem? -Original Message- From: [EMAIL PROTECTED] on behalf of Jason Tesser Sent: Thu 12/30/2004 9:30 PM To: pgsql-general@postgresql.org Subject: [GENERAL] pgodbc error In my postgresql.conf I set it to log the query durations and this seems to goof up odbc. I know this sounds stupid but I did a search and actually found taht others have had the problem though I found no solution. I am running 7.4 on a suse 9.2 box. I have noticed that it appears to work on Windows in 8.0. Can it be fixed on 7.4? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] help with a stored procedure
I am new to postgres stored procedures and would like a little help. My function basically takes 2 arguments and inserts data into a table from a select statement. I want it to return the number of records inserted. I am not sure what the best way to do this is. Here is my function CREATE OR REPLACE FUNCTION "public"."workstudyrollover" (INTEGER, INTEGER) RETURNS INTEGER AS $$ declare currentSemester alias for $1; oldSemester alias for $2; begin insert into tblworkstudy (transcriptlink, deptlink, payrate, current) Select distinct transcriptid, ws.deptlink, ws.payrate, ws.current from (SELECT DISTINCT public.tblworkstudy.transcriptlink, public.tblworkstudy.deptlink, public.tblindividual.indid, public.tblworkstudy.payrate, public.tblworkstudy.current FROM public.tblworkstudy INNER JOIN public.tbltranscript ON (public.tblworkstudy.transcriptlink = public.tbltranscript.transcriptid) INNER JOIN public.tblindividual ON (public.tbltranscript.indlink = public.tblindividual.indid) WHERE public.tbltranscript.semesterlink = oldSemester and tblworkstudy.deptlink is not null) as ws inner Join (Select DISTINCT tbltranscript.transcriptid, tbltranscript.indlink from tbltranscript where tbltranscript.semesterlink = currentSemester) as nws ON (ws.indid = nws.indlink); return 1; end; $$ LANGUAGE 'plpgsql' VOLATILE; And I call it with select * from workstudyrollover(94, 92); Thank you for any help given
[GENERAL] views in 8.0
Can you insert and update data in views in 8.0 I know you cannot in 7.4. I am asking because I want to make new structure for my backend but for legacy apps I need to keep the old sturcture also. I was hoping to use views to accomplish this by making the new structure and give the old apps view with the same name as the old tables. :-) I guess I can also do this by writing alot of rules but that would get messy quick I think. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Case sensitivity
I am converting data from Access into Postgres and ran into an issue with case sensitivity. Can I write queries in Access that will be case insensitive without rewriting the queries. So I would like to know if this be handled in Postgres or even if someone knows in Access. Thank you.
[GENERAL] help with query speed
I have the below query written. I have removed a lot from the select Statement for simplicity sake. The query takes way too long. I am moving from an Access backend to a Postgres back with Access in the front. The below query is taking like 14-20 seconds. Is their a better way I can write the joins. I would think that Postgres should be fast than Access. BTW I am writing these as Pass through queries so it is not a problem with Access. I have Even ran the query directly against Postgres. SELECT tblroster.transcriptlink FROM tblroster FULL OUTER JOIN testclass ON (tblroster.classlink = testclass.classid) FULL OUTER JOIN tblqualitypoint ON (tblroster.lettergrade = tblqualitypoint.letter) ORDER BY tblroster.transcriptlink ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] help with query speed
Hi > > I have the below query written. I have removed a lot from the select > > Statement for simplicity sake. The query takes way too long. > > I am moving from an Access backend to a Postgres back with > > Access in the front. The below query is taking like 14-20 seconds. > > Is their a better way I can write the joins. I would think that > > Postgres should be fast than Access. BTW I am writing these as > > Pass through queries so it is not a problem with Access. I > > have Even ran the query directly against Postgres. > > > > SELECT > > tblroster.transcriptlink > > FROM > > tblroster > > FULL OUTER JOIN testclass ON (tblroster.classlink = > > testclass.classid) > > FULL OUTER JOIN tblqualitypoint ON (tblroster.lettergrade = > > tblqualitypoint.letter) > > ORDER BY > > tblroster.transcriptlink > > Is there an index on: > tblroster.classlink > > Is there an index on: > testclass.classid > > Is there an index on: > tblroster.lettergrade > > Is there an index on: > tblqualitypoint.letter > > Is there an index on: > tblroster.transcriptlink I created the indexes and it speed up a little. Still a little slower than Access though. Which I think should not be. The testclass is a view not A query so I cannot make an index there. What is the best way to index this stuff? Should I make one index (b-tree) And add all fkeys and pkey from that table? > How many rows are in the tables? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] help with query speed
> You didn't answer my question about whether they are the same types. Are > they? Yes but testclass is a view. But the field it is referencing is of the same type. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Backup questions
I have 2 questions. 1. What is the best way to back up a production box running Postgres nightly? 2. I thought the best way was to create a script that would use pg_dump to create a dump every night and then use restore if I need to. I did this but I am having a problem. Apparently there were a few Primary key Big Serial fields that had the name changed on them after they were created. The backup works fine but during the restore it fails because it thinks a relation is missing. Before restoring the database with the changed fields works fine, I just cannot get it to restore. Is there something in the background that Postgres is hanging on to after I renamed the fields that is preventing me from restoring? How can I work around this? Thank you Jason Tesser Software Development NMI 715-324-6900 x5273 ---(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] Backup questions
I have 2 questions. 1. What is the best way to back up a production box running Postgres nightly? 2. I thought the best way was to create a script that would use pg_dump to create a dump every night and then use restore if I need to. I did this but I am having a problem. Apparently there were a few Primary key Big Serial fields that had the name changed on them after they were created. The backup works fine but during the restore it fails because it thinks a relation is missing. Before restoring the database with the changed fields works fine, I just cannot get it to restore. Is there something in the background that Postgres is hanging on to after I renamed the fields that is preventing me from restoring? How can I work around this? Thank you Jason Tesser Software Development NMI 715-324-6900 x5273 ---(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] Wal logs
This question may seem trivial but how do I rebuild a database with the wal logs? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wal logs
how -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Thu 7/29/2004 7:56 PM To: Jason Tesser Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Wal logs Jason Tesser wrote: > This question may seem trivial but how do I rebuild a database with the > wal logs? You can't, but with 7.5 you can archive those logs and use them and a recent backup for point-in-time recovery. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Creating an hourly backup
I am trying to get an hourly backup of postgres. I tried using the
script below
but when I try to restore off the dumped file it throws an error that
says it
cannot read the data [-1]. If I just type in pg_dump -Fc --file=***
username= database
it restores fine. So something is going on with the
except script, posted below. Can someone send me a working
cron or except script so I can get an hourly snapshot
of our databases?
#!/usr/bin/expect -f
###
###
# Script to backup PostgreSQL Database
# usage:
# ./dbbackup.exp [username] [password] [dbname] # # Use this script in a
cron job to do nightly # backups # Date format is MMHHDDMM
###
###
set username [lindex $argv 0]
set password [lindex $argv 1]
set database [lindex $argv 2]
set time [timestamp -format %M%H%d%m%Y]
spawn {pg_dump} -Fc --username=dbdevel
--file=/var/lib/pgsql/backups/$database.$time $database # expect
"Username:"
# send "$username\r"
expect "Password:"
send "$password\r"
expect eof
Jason Tesser
Web/Multimedia Programmer
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] converting database to unicode
I have a database in sql_ascii that I need to convert to Unicode. I tried using pg_dump -Fc .. but it fails on certain characters. like this one "è" How can I get the data transferred? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] converting database to unicode
Some help please?? :-) Anyone? -Original Message- From: [EMAIL PROTECTED] on behalf of Jason Tesser Sent: Wed 10/13/2004 10:00 AM To: [EMAIL PROTECTED] Cc: Subject:[GENERAL] converting database to unicode I have a database in sql_ascii that I need to convert to Unicode. I tried using pg_dump -Fc .. but it fails on certain characters. like this one "è" How can I get the data transferred? ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] converting database to unicode
Uhh no. Could you help me understand how to do that. I am currently using the following to dump and restore pg_dump -Fc --username=xxx --dbanme=xxx filename pg_restore -Fc --username=xxx --dbname filename -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Fri 10/15/2004 8:25 AM To: Jason Tesser Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] converting database to unicode On Fri, Oct 15, 2004 at 07:59:19AM -0500, Jason Tesser wrote: > Some help please?? :-) Anyone? Did you try recoding the plain-text dump using recode or iconv? > -Original Message- > From: [EMAIL PROTECTED] on behalf of Jason Tesser > Sent: Wed 10/13/2004 10:00 AM > To: [EMAIL PROTECTED] > Cc: > Subject: [GENERAL] converting database to unicode > I have a database in sql_ascii that I need to convert to Unicode. I tried using > pg_dump -Fc .. but it fails on certain characters. like this one "Ã" > How can I get the data transferred? -- Alvaro Herrera () "No es bueno caminar con un hombre muerto" ---(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] support
I am looking at PostgreSQL as a possible option for our backend database. I am also evaluating Oracle. What kind of paid support does PostgreSQL offer? Jason Tesser Web/Multimedia Programmer Northland Ministries Inc. (715)324-6900 x3050 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] linking postgre to AD
I am looking into using PostgreSQL on the backend here. I work for a Bible College. We are putting together a new software package. The database would be on a LInux box but our domain is M$ so we use active directory. I saw that there is a PAM module for Postgre. My question is with applications that will both be standalone and web what is teh best way for me to authenticate to active directory. C Is there a Postgre function that will allow me to do this? Does anyone have any other ideas that will work? Thank you in advance for any feedback. Jason Tesser Web/Multimedia Programmer Northland Ministries Inc. (715)324-6900 x3050 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] linking postgre to AD
Hi, On Fri, Nov 21, 2003 at 06:27:01AM -0600, Jason Tesser wrote: > I am looking into using PostgreSQL on the backend here. I work for a Bible College. > We are putting together a > new software package. The database would be on a LInux box but our domain is M$ so > we use active > directory. I saw that there is a PAM module for Postgre. My question is with > applications that will both be > standalone and web what is teh best way for me to authenticate to active directory. > C > Is there a Postgre function that will allow me to do this? > Does anyone have any other ideas that will work? > I'm not quite clear what you want to do. If you want to authenticate > against an Active Directory server, then you need to talk LDAP to it, > probably using the PAM LDAP module. There's a fair amount of > documentation on this out on the interweb. I want to make my standalone apps and my web apps authenticate through AD, but keep any access levels (groups) in Postgre > If you want to use AD for client authentication in Postgres, section > 6.2.5 of the Administrator's Guide details how to use PAM for Postgres > client authentication. If I did that could I use a table in Postgre as a groups table so I can control who can go where or who can see what in a web app? I was wondering if Postgre had some built in functions that could help me out here? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] pam authentication for postgres
I am trying to authenticate POstgres using Pam. How do I do this. I tried using webmin on RH9 and telling it to use Pam for authentication but it doesn't seem to work. Jason Tesser Web/Multimedia Programmer Northland Ministries Inc. (715)324-6900 x3050 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pam authentication for postgres
OK I am still trying to get pam working here is the messages I have is the log from trying to log in Nov 26 08:55:16 localhost postgresql(pam_unix)[22693]: authentication failure; logname= uid=26 euid=26 tty= ruser= rhost= user=cherring Nov 26 08:55:16 localhost pam_winbind[22693]: user 'cherring' granted acces as you can see winbind is actually granting access but fro some reason poasgres still denies it. weird. any ideas. the steps I have done are listed below ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pam authentication for postgres
sorry for teh double posting I forgot the steps at the end OK I am still trying to get pam working here is the messages I have is the log from trying to log in Nov 26 08:55:16 localhost postgresql(pam_unix)[22693]: authentication failure; logname= uid=26 euid=26 tty= ruser= rhost= user=cherring Nov 26 08:55:16 localhost pam_winbind[22693]: user 'cherring' granted acces as you can see winbind is actually granting access but fro some reason poasgres still denies it. weird. any ideas. the steps I have done are listed below > note: i'm no sysad, nor do i even pretend to understand pam, the linux kernel, > or postgresql, but this setup is a safe, working, postgresql/linux/pam setup. > > 0) configure postgresql for pam, for example > > [root ( at ) omega tmp]# grep pam /usr/local/pgsql/data/pg_hba.conf > hostall all 137.75.0.0255.255.0.0 pam > > 1) create a /etc/pam.d/postgresql entry, here's how i did mine > > [root ( at ) omega tmp]# cp /etc/pam.d/passwd /etc/pam.d/postgresql > > i don't know if it's the best setup, but it works! mine looks like this > > [root ( at ) omega tmp]# cat /etc/pam.d/postgresql > #%PAM-1.0 > auth required /lib/security/pam_stack.so service=system-auth > accountrequired /lib/security/pam_stack.so service=system-auth > password required /lib/security/pam_stack.so service=system-auth ---(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] disaster recovery
We are evaluating Postgres and would like some input about disaster recovery. I know in MsSQL they have a feature called transactional logs that would enable a database to be put back together based off those logs. Does Postgres do anything like this? I saw in the documentation transactional logging but I don't know if it is the same. Where can I find info about disaster recovery in Postgres. Thank you in advance for any info given. Jason Tesser Web/Multimedia Programmer Northland Ministries Inc. (715)324-6900 x3050 ---(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] pam authentication for postgres
> Please post a comprehensive description of what you're trying to do > together with the configuration files you use. I thought I did that sorry. I am trying to get Postgres to authenticate through Pam so I can authenticate to Active Directory on our network. All the steps I took are posted below. If you notice the messages I pasted from my logs you will see that winbind is authenticating ok but for some reason Postgres still denies access. > > here is the messages I have is the log from trying to log in > > Nov 26 08:55:16 localhost postgresql(pam_unix)[22693]: authentication failure; > logname= uid=26 euid=26 tty= ruser= rhost= user=cherring > Nov 26 08:55:16 localhost pam_winbind[22693]: user 'cherring' granted acces > > as you can see winbind is actually granting access but fro some reason poasgres > still denies it. > weird. any ideas. > postgresql/linux/pam setup. >> >> 0) configure postgresql for pam, for example >> >> [root ( at ) omega tmp]# grep pam /usr/local/pgsql/data/pg_hba.conf >> hostall all 137.75.0.0255.255.0.0 pam >> >> 1) create a /etc/pam.d/postgresql entry, here's how i did mine >> >> [root ( at ) omega tmp]# cp /etc/pam.d/passwd /etc/pam.d/postgresql >> >> i don't know if it's the best setup, but it works! mine looks like this >> >> [root ( at ) omega tmp]# cat /etc/pam.d/postgresql >> #%PAM-1.0 >> auth required /lib/security/pam_stack.so service=system-auth >> accountrequired /lib/security/pam_stack.so service=system-auth >> password required /lib/security/pam_stack.so service=system-auth > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
hi, > Maybe there's not such a need for the advanced features of PostgreSQL > amongst PHP programmers as you seem to believe. Most of the PHP stuff I've > seen is read-only content display stuff and that doesn't really require a > top-notch RDBMS; a more limited database should also be up to the job. For > complex transactional web applications, J2EE/Model II is a far superior > technology to scripts/Model I and that means a different target audience > for the apps where PostgreSQL can offer those essential extra features. > Whilst most J2EE developers will be using Oracle/DB2/MSSQL as their > back-end, the awareness of PostgreSQL seems quite high and, in the few > usenet groups I monitor, I don't recall anyone being flamed for > recommending PostgreSQL over MySQL. Maybe seasoned, professional > developers don't like being told that they're crap programmers just > because they ask for something as fundamental as referential integrity! I completely disagree. I do a lot of programming with PHP and the features of Postgres come in handy. Let me give you an example of just some basic things. Triggers! Why should I have to write insert and update triggers in the logic (PHP) if I can handle it at the database level. Sql is 10x as fast as the language. Better to handle what you can at the database level. Same with views and stored procedures. MySQL cannot even handle sub-queries yet. I also use Python for standalone interfaces to the data. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. MySQL may be more popular with (cheap) web hosting places but that doesn't mean it is the best or that Postgres wouldn't serve better even in this area. I am glad to see the article written for PHP mag as Postgres would help alot of PHP guys that are using MySQL. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] CMS with PostgreSQL
> Anyone know of an open source (BSD, GPL) Content Managment System written in > PHP that will use PostgreSQL, or at least use PEAR::DB or any other DB > abstraction layer API? I am currently writing one for use with Python and Postgres as I feel this is the best way to go. I am using mod_pyhton. The newer version has all needed features to work with Apache like sessions, cookie, authenticatio etc.. some of these were missing from version 2 of mod_python. So anyways I might gpl what I am doing to answer your question. But why not just write one to cater to your needs. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
