[GENERAL] Replace NULL values
Hi there,is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace.I found that the COALESCE command does something like this, but I couldn't figure out how this works.And this solution which I found on the Net:= SELECT * FROM test; a--- 1 2 3= SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case---+--- 1 | one 2 | two 3 | otherdoesn't work either, cause the titles of my columns are very varied...Thanks for any advice,Stefan Schwarzer ___Stefan SchwarzerGIS Data ManagementUNEP/DEWA/GRID-EuropeChemin des Anemones 11CH - 1219 ChatelaineSwitzerlandTel: (+41) 22.917.83.49Fax: (+41) 22.917.80.29Internet: http://geodata.grid.unep.ch/___
[GENERAL] postgres and emacs on windows os
hi all, hasanybody experience using psql with emacs (M-x sql-postgres) on windows. if iexecute this command emacs prompts for user, database and host, but not for the password, so i think there is no response from psql. but if am looking at the emacs messages there is no error message (C:\Programme\Emacs22\emacs\bin\emacs.exe --load=C:/Programme/Emacs22/EmacsW32/bin/gnuservauto.el)Loading encoded-kb...doneAdding c:/Programme/Emacs22/EmacsW32/lisp/ to load-pathLoading kmacro...doneLoading c:/home/LöschTabEnde.txt...doneLoading cl-macs...doneLoading cl-seq...doneLoading easy-mmode...doneFor information about the GNU Project and its goals, type C-h C-p.Loading sql...doneLogin...done if try to execute sql-statements or some psql commands nothing happens, but thats not unexpected, because witout the specification of the password i can't be logged in. thanks and greetings from munich christian
Re: [GENERAL] Thought provoking piece on NetBSD
Shane Ambler wrote: On 2/9/2006 4:11, Scott Marlowe [EMAIL PROTECTED] wrote: I think that with either the GPL or BSD, code is returned under a type of coercion. Not necessarily a bad thing, understand. The coercion of the GPL is legalistic. If you distribute GPL stuff, you've got to give out the source code with it. So, you might as well give it to the community at large. With BSD, it's more that you'd be cutting yourself off from the community at large if you didn't return the code. So, the coercion is much more subtle. It's much easier to donate your code to the project and let other people maintain it then to try and maintain your own fork of the code and cross patch their changes into your own. The GPL *forces* you to release your source code where the BSD license gives you the option to choose what you want to do with your work. Free choice is a good way to get co-operation where forcing would normally get a negative response. That's just general human behaviour. Truly a theorie well proven by the GPL-ed Linux kernel and a few hundred other GPL licenced software packages, or is it? To me general human behaviour also includes not wanting to take advantage of other people / other people's work and not returning anything to the ones that give you something for free. Naturally, there will also always be vultures and thieves, so the GPL tries to act as an educational instrument. The fact is that most decent people have no problem with the stranglehold of the GPL, as it is clear to them that the GPL does not ask them to do anything which should be normal anyway. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tsearch2 Hebrew
Hello Oleg, Am 2006-09-04 20:52:02, schrieb Oleg Bartunov: You need to provide more details. Oleg - END OF REPLIED MESSAGE - One of my two programmers had coded last year stuff in php5 (UNICODE is now working) to search à la Google in my Database. I am collecting international stuff about wars, warcrime and violation of human rights. My database is text/plain UNICODE and has curently arround 380-390 GB which I have splited into tables of 10 years... It seems, there is a problem with BIDI searching. Russian and chinese is NO problem. Many texts are mixed like US-ASCII, arabic and hebrew. Now if I enter search strings it returns nothing. Even if I am in psql with multilingual terminal or in pgadmin. So it can not be a problem with PHP5. Oh yes, since I have switched to one table per 10 years, tsearch2 do not want to search my whole Database... but for tsearch2 I think, I am looking for a PHP5/PGSQL coder on www.getacoder.com since I am not the master of PHP5 and PGSQL. (I am more Sysadmin and soldier then programmer even if I can code stuff) Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problems with sequences
Arturo Perez wrote:
What happens is that if I do a select nextval('seq') I get a number
that's lower than the
max primary key id. This is inspite of my doing
SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
ALTER SEQUENCE seq RESTART WITH max + 1;
select pg_catalog.setval(seq, max+1, true);
This seems to be a bit over the top;
SELECT setval('seq', (SELECT MAX(seq_ID) FROM table)
should be enough. Even the +1 isn't necessary, as the first value the
sequence will return is already 1 higher than the value retrieved from MAX.
Are you sure you're using the correct sequence(s) to retrieve your
column values for the problematic table(s)? How do you set the values
for seqID?
Also note that a SERIAL type column is simply a macro for creating an
INT4 type column with a DEFAULT nextval('...'). You can easily change
your schema to include the defaults.
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Replace NULL values
On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Replace NULL values
On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density ---(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] Replace NULL values
On Thu, Sep 07, 2006 at 11:54:43AM +0200, Stefan Schwarzer wrote: As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density Ah, your data is denormalised. SQL doesn't handle that kind of data very well at all. A user-defined function should be able to handle that though (not pl/pgsql). Hope this helps, Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Replace NULL values
Aside from your database structure being problematic, what are you trying to accomplish? In other words, what do you want to replace the nulls with and in what circumstance? I imagine your table looks like this ID,country,1950,1951,1952,1953, 1 usa50 null 70 10 2 canada 10 45 null 4 Please mention what you would like to do with this? Stefan Schwarzer wrote: On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Asynchronous trigger
You want to use the Notify/Listen framework.
1) You need a rule on a table that says on Update (or insert or delete)
do also notify {key}
2) You need a daemon running against the server that calls listen {key}
3) when the table is updated (or inserted or deleted) then your
application will be able to check the table and see what needs to be done.
I just did this for a process that needs to update a denormalized
statistics table every time the data changes and it seems to be working
great. My query takes 2.5 seconds and the users didn't want to wait that
long each time they modified a record. They also refused to tolerate it
being updated every 10 minutes. They wanted it in as close to real time
as it could be (with good reason, i might add). So the daemon calls a
function and it works just fine.
Sim
Jean-Christophe Praud wrote:
Hi all,
I'm planning to convert an application to postgresql 8.1 (from mysql).
Currently we have some recursive procedures done on the application side
we would want to rewrite as stored pl/pgsql procedures called by
triggers...
Is it possible for these triggers to be asynchronous, in order not to
block the client application during the recursive process ?
Regards,
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Replace NULL values
I have an internet map server connected to my database. Until now, no data fields within the table were filled with a -, i.e. - equalled no data available. Now, for displaying a map with different classes (red for values from 0-100, green for values from 100-200) I need to build as well a class for no data (which is displayed in grey). Until now that worked perfectly well with the - values. But since I inserted a couple of new countries (which do not find any corresponding values in the tables, as they don't yet exist), I receive the usual - plus NULL values. Both should be considered as no data and thus displayed in grey. Unfortunately the mapserver can't deal with NULL values. So, I can't build a class saying if values = NULL do something but instead it only works with fake NULL values as - if values = - do something Stef Aside from your database structure being problematic, what are you trying to accomplish? In other words, what do you want to replace the nulls with and in what circumstance? I imagine your table looks like this ID,country,1950,1951,1952,1953, 1 usa50 null 70 10 2 canada 10 45 null 4 Please mention what you would like to do with this? Stefan Schwarzer wrote: On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 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
Re: [GENERAL] Replace NULL values
On Thu, Sep 07, 2006 at 12:58:17PM +0200, Stefan Schwarzer wrote: Unfortunately the mapserver can't deal with NULL values. So, I can't build a class saying if values = NULL do something but instead it only works with fake NULL values as - if values = - do something That's because nothing is equal to NULL. You say values IS NULL. And COALESCE(a,b) is pretty mutch: IF a IS NULL THEN b ELSE A Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Fwd: Help using user rights
Hello, I have a question regarding user rights. I have one user in postgres8.01. which connects from a webserver and inserts some data into a table. It only has INSERT access on that table. ON the other hand, on the same table a have an after-insert trigger which executes a function owned by a more powerful user. My problem is that when I try to insert some data with the webserver user, PostgreSQL wants that user to have access to all the relations used in the trigger function. Is there any way to avoid granting the light user with all those rigths? Thank you very much!
Re: [GENERAL] Problems with sequences
On Sep 7, 2006, at 5:35 AM, Alban Hertroys wrote:
Arturo Perez wrote:
What happens is that if I do a select nextval('seq') I get a number
that's lower than the
max primary key id. This is inspite of my doing
SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
ALTER SEQUENCE seq RESTART WITH max + 1;
select pg_catalog.setval(seq, max+1, true);
This seems to be a bit over the top;
SELECT setval('seq', (SELECT MAX(seq_ID) FROM table)
should be enough. Even the +1 isn't necessary, as the first value
the sequence will return is already 1 higher than the value
retrieved from MAX.
Note that all of the above was in an attempt to reset the sequence to
the proper value. I'm beginning to think that it's a library problem
as this morning I get:
iht= select max(article_id) from article;
max
--
4992
(1 row)
iht= select nextval('pk_article');
nextval
-
4986
(1 row)
Assuming the sequence number is being used correctly why would they
be 6 apart?
Are you sure you're using the correct sequence(s) to retrieve your
column values for the problematic table(s)? How do you set the
values for seqID?
I tried statement logging but I am not sure it reported anything
useful. When I get into work I'll send in those logs.
-arturo
---(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] Replace NULL values
Once again Martijn is correct, and you have to use is null not =null One thing you might want to consider is adding - as a default value in the table so that when new data is entered it with a null it automatically gets the correct value. Using coalesce with the value will probably be the simplest for you. Try select ...,coalesce(datafield,-) as datafield,... and that will change all nulls to - and give it the correct fieldname. Stefan Schwarzer wrote: I have an internet map server connected to my database. Until now, no data fields within the table were filled with a -, i.e. - equalled no data available. Now, for displaying a map with different classes (red for values from 0-100, green for values from 100-200) I need to build as well a class for no data (which is displayed in grey). Until now that worked perfectly well with the - values. But since I inserted a couple of new countries (which do not find any corresponding values in the tables, as they don't yet exist), I receive the usual - plus NULL values. Both should be considered as no data and thus displayed in grey. Unfortunately the mapserver can't deal with NULL values. So, I can't build a class saying if values = NULL do something but instead it only works with fake NULL values as - if values = - do something Stef Aside from your database structure being problematic, what are you trying to accomplish? In other words, what do you want to replace the nulls with and in what circumstance? I imagine your table looks like this ID,country,1950,1951,1952,1953, 1 usa50 null 70 10 2 canada 10 45 null 4 Please mention what you would like to do with this? Stefan Schwarzer wrote: On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replace NULL values
Thanks for your suggestions. You're right with the is versus = for NULL values. Unfortunately the coding for the mapserver does not allow an IS statement. Concerning the coalesce(datafield,-) it seems rather unusable for me, if I have to explicitly stated each column, as a) for many tables I have different column titles and b) there are up to 60 columns for each table. There is no way to say something like coalesce(table.*, -) I guess, no? Thanks a lot! Stefan Once again Martijn is correct, and you have to use is null not =null One thing you might want to consider is adding - as a default value in the table so that when new data is entered it with a null it automatically gets the correct value. Using coalesce with the value will probably be the simplest for you. Try select ...,coalesce(datafield,-) as datafield,... and that will change all nulls to - and give it the correct fieldname. Stefan Schwarzer wrote: I have an internet map server connected to my database. Until now, no data fields within the table were filled with a -, i.e. - equalled no data available. Now, for displaying a map with different classes (red for values from 0-100, green for values from 100-200) I need to build as well a class for no data (which is displayed in grey). Until now that worked perfectly well with the - values. But since I inserted a couple of new countries (which do not find any corresponding values in the tables, as they don't yet exist), I receive the usual - plus NULL values. Both should be considered as no data and thus displayed in grey. Unfortunately the mapserver can't deal with NULL values. So, I can't build a class saying if values = NULL do something but instead it only works with fake NULL values as - if values = - do something Stef Aside from your database structure being problematic, what are you trying to accomplish? In other words, what do you want to replace the nulls with and in what circumstance? I imagine your table looks like this ID,country,1950,1951,1952,1953, 1 usa50 null 70 10 2 canada 10 45 null 4 Please mention what you would like to do with this? Stefan Schwarzer wrote: On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Majordomo drops multi-line Subject:
Joshua D. Drake wrote: Marc G. Fournier wrote: On Wed, 23 Aug 2006, Alvaro Herrera wrote: In any case I don't see any reason to let the broken software continue to be broken. Surely there must be an updated version which corrects this bug? A patch at least? I mean, I can't be the only one complaining about it. Based on this thread, and the fact that you are the first to have ever noticed/commented about it ... ya, you are the only one complaining about it :) Honestly, it may be time we start looking at mailman. Please don't, unless Majordomo is really broken and unfixed (i.e. there isn't a newer version with the bug fixed). What version are we running? May I assume we are running a rather obsolete version? Like the version we are running of CVS, which doesn't support the usage of LocalKeyword=PostgreSQL=CVSHeader KeywordExpand=iPostgreSQL in CVSROOT/config which replaces the old, unsupported, broken hack of putting tag=PostgreSQL=CVSHeader in CVSROOT/options. I've been wanting to propose an upgrade to that as well. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Fwd: Help using user rights
On Thu, Sep 07, 2006 at 02:12:57PM +0300, Valentin Militaru wrote: I have one user in postgres8.01. which connects from a webserver and inserts some data into a table. It only has INSERT access on that table. ON the other hand, on the same table a have an after-insert trigger which executes a function owned by a more powerful user. My problem is that when I try to insert some data with the webserver user, PostgreSQL wants that user to have access to all the relations used in the trigger function. Is there any way to avoid granting the light user with all those rigths? You could declare the function SECURITY DEFINER, then it will always have the rights of the person who created the trigger. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] archives is not working was (Re: [NOVICE] Removing duplicate keys)
fixed now: * From: kynn ( at ) panix ( dot ) com * To: pgsql-novice ( at ) postgresql ( dot ) org * Subject: SQL for removing duplicates? * Date: Tue, 13 Jun 2006 12:11:41 -0400 (EDT) Hi. I'm stumped. I have a large table (about 8.5M records), let's call it t, whose columns include x and y. I want to remove records from this table so that any pair of values for these two fields appear only once. (This will get rid of about 15% of the records in t.) One simple solution would be something like CREATE TABLE tmp AS SELECT DISTINCT ON ( x, y ) * FROM t; DROP TABLE t; ALTER TABLE tmp RENAME TO t; This works, but it uses a lot of space. I would prefer to simply cull the unwanted records from t, but I just can't figure out the SQL for it. Any help with it would be *much* appreciated. Thanks! On Wed, Sep 06, 2006 at 08:00:54AM -0700, Richard Broersma Jr wrote: I have been trying to open the link and seems that postgresql archive site is down. http://archives.postgresql.org/pgsql-novice/2006-06/msg00092.php Sorry, the link didn't work. It worked yesterday when I found it. I will also forward this to the general list maybe someone there could take a look at it. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problems with sequences
On Thu, Sep 07, 2006 at 07:15:43AM -0400, Arturo Perez wrote:
Note that all of the above was in an attempt to reset the sequence to
the proper value. I'm beginning to think that it's a library problem
as this morning I get:
iht= select max(article_id) from article;
max
--
4992
(1 row)
iht= select nextval('pk_article');
nextval
-
4986
(1 row)
Assuming the sequence number is being used correctly why would they
be 6 apart?
The last four transactions could be rolled back, or not committed yet.
I'd suggest looking at your insert statements. If the actual inserts
are insterting actual numbers, look very very carefully where the numbers
are coming from.
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Problems with sequences
On 9/7/06, Arturo Perez [EMAIL PROTECTED] wrote: I tried statement logging but I am not sure it reported anything useful. When I get into work I'll send in those logs. More than likely they are large and should not be sent through the mailing list. contact me off list and I'll arrange it. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] On DNS for postgresql.org
On Wed, Sep 06, 2006 at 06:23:06PM -0700, Steve Atkins wrote: DNS clue might be relevant. We're not, though. Rather I'm saying that publicly criticizing people who volunteer services to a project, about things that are not related to the services they're providing is at best a little impolite. Actually, the real problem (as a couple people pointed out to me privately, for which I am thankful) is that I did it on the wrong list. But for the record: I wasn't trying to be critical; I was trying to solve a problem. If I appeared to be attacking anyone, I do apologise. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Majordomo drops multi-line Subject:
Alvaro Herrera wrote: Joshua D. Drake wrote: Marc G. Fournier wrote: On Wed, 23 Aug 2006, Alvaro Herrera wrote: In any case I don't see any reason to let the broken software continue to be broken. Surely there must be an updated version which corrects this bug? A patch at least? I mean, I can't be the only one complaining about it. Based on this thread, and the fact that you are the first to have ever noticed/commented about it ... ya, you are the only one complaining about it :) Honestly, it may be time we start looking at mailman. Please don't, unless Majordomo is really broken and unfixed (i.e. there isn't a newer version with the bug fixed). What version are we running? From what I can tell Majordomo isn't even supported any longer. Secondly we get some better management (not great but better) interfaces with mailman. Mailman is a supported, large, active, FOSS community project that is battle tested in the current field much more so then Majordomo. Holding on with a dying breaths to old software is silly. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.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] Replace NULL values
It seems like the mapserver is quite inflexible. Maybe there are options to loosen it up a little? (I've found that pouring beer into the keyboard after a long week does not help.) I would venture that your best option is to write a quick pgpsql function that goes over all the fields in the table, writes an alter statement that gives them a default value and an update statement that changes the null to -. Then any new fields that are created must be created with the default. All the field names are in the pg_attribute table and the table name is in the pg_class table. Spend a couple minutes figuring out how to tell the fields from the indexes and your code should be a walk in the park. Sim Stefan Schwarzer wrote: Thanks for your suggestions. You're right with the is versus = for NULL values. Unfortunately the coding for the mapserver does not allow an IS statement. Concerning the coalesce(datafield,-) it seems rather unusable for me, if I have to explicitly stated each column, as a) for many tables I have different column titles and b) there are up to 60 columns for each table. There is no way to say something like coalesce(table.*, -) I guess, no? Thanks a lot! Stefan Once again Martijn is correct, and you have to use is null not =null One thing you might want to consider is adding - as a default value in the table so that when new data is entered it with a null it automatically gets the correct value. Using coalesce with the value will probably be the simplest for you. Try select ...,coalesce(datafield,-) as datafield,... and that will change all nulls to - and give it the correct fieldname. Stefan Schwarzer wrote: I have an internet map server connected to my database. Until now, no data fields within the table were filled with a -, i.e. - equalled no data available. Now, for displaying a map with different classes (red for values from 0-100, green for values from 100-200) I need to build as well a class for no data (which is displayed in grey). Until now that worked perfectly well with the - values. But since I inserted a couple of new countries (which do not find any corresponding values in the tables, as they don't yet exist), I receive the usual - plus NULL values. Both should be considered as no data and thus displayed in grey. Unfortunately the mapserver can't deal with NULL values. So, I can't build a class saying if values = NULL do something but instead it only works with fake NULL values as - if values = - do something Stef Aside from your database structure being problematic, what are you trying to accomplish? In other words, what do you want to replace the nulls with and in what circumstance? I imagine your table looks like this ID,country,1950,1951,1952,1953, 1 usa50 null 70 10 2 canada 10 45 null 4 Please mention what you would like to do with this? Stefan Schwarzer wrote: On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote: Hi there, is there a simple way to replace NULL values in multiple columns within the SQL statement? I changed the underlaying country template of your database; so now there are a couple of NULL values when I join the stats-table with the country table. Unfortunately, my queries have always multiple (year) columns, so I can't do a kind of manual replace. I found that the COALESCE command does something like this, but I couldn't figure out how this works. Yes, COALESCE replaces NULLs, however your examples have neither NULLs nor use COALESCE, so I don't understand what your question is. Please repost with an actual example of your problem. As I said, I couldn't figure out how COALESCE would work on multiple columns (without naming them explicitly). So, say I have a table with columns for each year between 1970 and 2005. For specific countries the values might be NULL, depending if the statistical table has been updated recently (then they will have a value), or not (then they will be NULL). A sample query would thus be something like: SELECT * FROM pop_density ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched
Re: [GENERAL] Replace NULL values
On Thu, Sep 07, 2006 at 01:39:06PM +0200, Stefan Schwarzer wrote: You're right with the is versus = for NULL values. Unfortunately the coding for the mapserver does not allow an IS statement. There's a hack for this; you need to turn it on in the config file. I think it's called allow_null_equals. It's ugly, but it might solve this problem for you. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems with sequences
From: Alban Hertroys [mailto:[EMAIL PROTECTED]
Martijn van Oosterhout wrote:
On Thu, Sep 07, 2006 at 07:15:43AM -0400, Arturo Perez wrote:
iht= select max(article_id) from article;
max
--
4992
(1 row)
iht= select nextval('pk_article');
nextval
-
4986
(1 row)
Assuming the sequence number is being used correctly why would they
be 6 apart?
The last four transactions could be rolled back, or not committed yet.
b) the OP inserted numbers not coming from the sequence; he shot his
own feet.
I prefer to think that my feet were shot off by a library I'm using :-)
Some many layers, so little time (to debug).
In any case, at this point in time it's looking like Cayenne doesn't honor
the rules of the sequence. It appears to (and is documented as) internally
incrementing rather than fetching the sequence for each insert.
I would still like more debugging tips for this sort of thing. As I mentioned,
statement logging did not show the relevant details. What other things could
I have done?
-arturo
---(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] Majordomo drops multi-line Subject:
Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Marc G. Fournier wrote: On Wed, 23 Aug 2006, Alvaro Herrera wrote: In any case I don't see any reason to let the broken software continue to be broken. Surely there must be an updated version which corrects this bug? A patch at least? I mean, I can't be the only one complaining about it. Based on this thread, and the fact that you are the first to have ever noticed/commented about it ... ya, you are the only one complaining about it :) Honestly, it may be time we start looking at mailman. Please don't, unless Majordomo is really broken and unfixed (i.e. there isn't a newer version with the bug fixed). What version are we running? From what I can tell Majordomo isn't even supported any longer. Secondly we get some better management (not great but better) interfaces with mailman. Mailman is a supported, large, active, FOSS community project that is battle tested in the current field much more so then Majordomo. Holding on with a dying breaths to old software is silly. Can Mailman do moderation over email? If it can do that, then I'm all for it. If it can't, which was the case last time I checked (more than a year ago or two, I admit), then I repeat my plea that it's not done. Moderation over www is a PITA. My MUA allows me to accept/reject a message in a single keystroke. If I had to fetch a webpage any time I wanted to approve a post I'd abandon the job pretty quickly. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Majordomo drops multi-line Subject:
ever noticed/commented about it ... ya, you are the only one complaining about it :) Honestly, it may be time we start looking at mailman. Please don't, unless Majordomo is really broken and unfixed (i.e. there isn't a newer version with the bug fixed). What version are we running? From what I can tell Majordomo isn't even supported any longer. Secondly we get some better management (not great but better) interfaces with mailman. Seriously, I think that's the first time anybody said anything good about the mailman interfaces Just the stuff I have to do for the pgFoundry lists (of which I have only *two*) is just so much pain. (who came up with such a brilliant thing as different-password-for-everything-you-do? It's just god-awful if you have more than one list..) If we're changing anyway, I think we should seriously consider Sympa, IMHO. For one thing, it can store it's Config and userlists and stuf in a PostgreSQL database, so if you're not happy with the interface, it's fairly trivial to whack something else up. Or if you need to do batch changes or something... I'm not advocating a change though - I'll stand neutral on that - but *if* we're going to change... //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Majordomo drops multi-line Subject:
Mailman is a supported, large, active, FOSS community project that is battle tested in the current field much more so then Majordomo. Holding on with a dying breaths to old software is silly. Can Mailman do moderation over email? If it can do that, then I'm all for it. If it can't, which was the case last time I checked (more than a year ago or two, I admit), then I repeat my plea that it's not done. Moderation over www is a PITA. My MUA allows me to accept/reject a O.k. this is a little silly. This constant old school, we have to be able to administer things from email is counter-productive. Email is dead, long live www! ;) Seriously though, from a list administrator point of view, someone who has to manage many, many lists moderation over email is the PITA. It is much nicer to just view a nice long list in a web brower, select a couple that I want to keep -- and submit. The rest get thrown away. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problems with sequences
Martijn van Oosterhout wrote:
On Thu, Sep 07, 2006 at 07:15:43AM -0400, Arturo Perez wrote:
iht= select max(article_id) from article;
max
--
4992
(1 row)
iht= select nextval('pk_article');
nextval
-
4986
(1 row)
Assuming the sequence number is being used correctly why would they
be 6 apart?
The last four transactions could be rolled back, or not committed yet.
Could you elaborate on that? I'm confused...
AFAIK after the sequence was initialised at max(article_id),
nextval(article_id) could never return a number that's lower than
max(article_id).
Unless:
a) the OP managed to query max(article_id) and nextval(article_id)
from different transactions, the one querying nextval being older than
the one querying max.
b) the OP inserted numbers not coming from the sequence; he shot his
own feet.
c) the sequence wrapped around due to reaching numbers exceeding 2^32.
Or are you short on caffeine perhaps? ;)
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Majordomo drops multi-line Subject:
Seriously, I think that's the first time anybody said anything good about the mailman interfaces Just the stuff I have to do for the pgFoundry lists (of which I have only *two*) is just so much pain. (who came up with such a brilliant thing as different-password-for-everything-you-do? It's just god-awful if you have more than one list..) You don't have to have a different password for everything you do. Could you elaborate as to what you are talking about? If we're changing anyway, I think we should seriously consider Sympa, Well no one said we were changing, I just made the suggestion. I have never seen or even of heard of Sympa for that matter. Unless it has a very large, active, supported community -- I am not interested in the least. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems with sequences
Arturo Perez wrote: In any case, at this point in time it's looking like Cayenne doesn't honor the rules of the sequence. It appears to (and is documented as) internally incrementing rather than fetching the sequence for each insert. I have no experience with Cayenne, but reading http://cwiki.apache.org/CAYDOC/primary-key-generation.html it seems possible to use database sequences instead of Cayenne-generated ones: ... Generation mechanism depends on the DbAdapter used and can be customized by users by subclassing one of the included adapters. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Majordomo drops multi-line Subject:
Seriously, I think that's the first time anybody said anything good about the mailman interfaces Just the stuff I have to do for the pgFoundry lists (of which I have only *two*) is just so much pain. (who came up with such a brilliant thing as different-password-for-everything-you-do? It's just god-awful if you have more than one list..) You don't have to have a different password for everything you do. Could you elaborate as to what you are talking about? For example, to manage my pgFoundry lists, I have to log in with one password to manage pginstaller-devel and a different one to manage pginstaller-cvs (which has been discontinued, but keeps getting spams that notify me - at least it used to). Sure, I can set them to the same, but... Same goes as a user of mailman lists. What I want is to log in to lists.postgresql.org, and get an interface that wil show me everything about the lists i'm subscribed to (capability to change my flags etc) and everything about the ones I'm admin for (which I'm not for any on pgsql.org, but in principle - admin requests, moderation requests etc). It's possible this can be done in other versions of mailman than the ones I've been exposed to, in which case the point isn't valid given those would be the versions we'd talk about. If we're changing anyway, I think we should seriously consider Sympa, Well no one said we were changing, I just made the suggestion. Oh, I know that. I just wanted to get the suggestion into a possible discussion about it. I have never seen or even of heard of Sympa for that matter. Unless it has a very large, active, supported community -- I am not interested in the least. It has a pretty large community supporting it in France, IIRC. Certainly not as big as mailman, though. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Majordomo drops multi-line Subject:
Joshua D. Drake wrote: Mailman is a supported, large, active, FOSS community project that is battle tested in the current field much more so then Majordomo. Holding on with a dying breaths to old software is silly. Can Mailman do moderation over email? If it can do that, then I'm all for it. If it can't, which was the case last time I checked (more than a year ago or two, I admit), then I repeat my plea that it's not done. Moderation over www is a PITA. My MUA allows me to accept/reject a O.k. this is a little silly. This constant old school, we have to be able to administer things from email is counter-productive. Email is dead, long live www! ;) Seriously though, from a list administrator point of view, someone who has to manage many, many lists moderation over email is the PITA. It is much nicer to just view a nice long list in a web brower, select a couple that I want to keep -- and submit. The rest get thrown away. For each moderation request I get, I press a single key. Either A or R (yes, I wrote a script for this and set up a mutt macro. Does you MUA allow you to bind keys to macros?). And I get to see the message to check if it's actual spam or not. These condensed lists of yours are a mess because you can't readily tell without looking at the content, thus leading to opening new windows or tabs. Plus, I get the moderation requests on the regular inbox, so they get handled right away and discarded (the single key macro I wrote takes care of deleting the mail as well). I don't see how the web stuff can be any simpler. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Majordomo drops multi-line Subject:
Same goes as a user of mailman lists. What I want is to log in to lists.postgresql.org, and get an interface that wil show me everything about the lists i'm subscribed to (capability to change my flags etc) and everything about the ones I'm admin for (which I'm not for any on pgsql.org, but in principle - admin requests, moderation requests etc). O.k. from list to list yes you are correct. Hmmm... I wonder how they handle the token... (decides to take a look after the class he is teaching today). Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Majordomo drops multi-line Subject:
I don't see how the web stuff can be any simpler. I view a single page, select the items I wish to keep, hit a single button, I am done and I don't have to clutter my inbox. This discussion is all about how people work. Most people *DO NOT* work from email the way Alvaro and and some of the other hackers do. I do not use email for todo lists, sending commands to servers, moderating lists. I use it for email, which is to say I communicate with people with it. I receive attachments of contracts I need to sign and requests for work. When I open my email it is bad enough I have some spam to deal with, I don't want a bunch of emails that have nothing to do with my actual email cluttering my inbox. Administrative commands, for me -- belong in an interface that is separate from my business medium. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problems with sequences
From: Alban Hertroys [mailto:[EMAIL PROTECTED] Arturo Perez wrote: In any case, at this point in time it's looking like Cayenne doesn't honor the rules of the sequence. It appears to (and is documented as) internally incrementing rather than fetching the sequence for each insert. I have no experience with Cayenne, but reading http://cwiki.apache.org/CAYDOC/primary-key-generation.html it seems possible to use database sequences instead of Cayenne-generated ones: ... Generation mechanism depends on the DbAdapter used and can be customized by users by subclassing one of the included adapters. Yes. I think I am being bitten by a desire to minimize changes required when migrating from MySQL to PostgreSQL. Contrary to my belief, it appears that the pgSQL schema creation script was not created by Cayenne configured to work with pgSQL but rather was based on the script Cayenne used to create the MySQL database. Looks like I will be uncovering nits as we go for a bit yet. I did modify the sequences to increment by 20 as required by Cayenne. Hopefully, this particular issue will be laid to rest by that. thanks all, -arturo ---(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] Majordomo drops multi-line Subject:
-Original Message- From: Joshua D. Drake [EMAIL PROTECTED] To: Joshua D. Drake [EMAIL PROTECTED]; Marc G. Fournier [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; General pgsql-general@postgresql.org Sent: 07/09/06 14:27 Subject: Re: [GENERAL] Majordomo drops multi-line Subject: O.k. this is a little silly. This constant old school, we have to be able to administer things from email is counter-productive. Email is dead, long live www! ;) As someone who has been building webapps since the (very) early 90's I'm far from a www-luddite, but it'll be a cold day in hell when I willingly moderate any of the pg lists using mailman's interface (or mj's for that matter) - and yes, I do moderate a few of them. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgFoundry.org not working!
Working again now! Regards, Ben Ben Trewern [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I'm currently getting: PgFoundry Could Not Connect to Database when I try to visit http://pgfoundry.org Regards, Ben ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problems with upgrade Postgre 8.1.2 to 8.1.4
In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server from 8.1.2 to 8.1.4., by using upgrade.bat. Everything goes nice until end of installation when the following error apears: Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) could not be installed. Verify that you have sufficient priviliges to install system services . I am logged as administrator, Windows XP Pro SP2. What should I do? Zlatko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Database design and triggers...
Hi everybody. Hope I'm posting in the correct group. My question is part design and part implementation. Since we are creating an inventory system we need to have the clients pull up current inventory. Also, we need to have the past transactions stored for reference and billing. In our previous system in MS Access we accomplished this by adding up all of the transactions stored in two tables and generating a temporary table with the latest inventory count. The problem with this approach is that it is slow because the temporary table has to be created every time a user needs to see a report or work on a form. Even when instead of creating a temporary table we use a query it is still slow. With postgreSQL I found out about triggers and I figure that instead of calculating the current inventory count and storing it in a table every time a client needs it I could have a triggers maintain a table with the current count by incrementing or decreasing the amounts each time a transaction is stored in the transaction tables. My worry is that if for some reason a trigger were to somehow fail to execute correctly there would be an inconsistency between the transactions table and the current inventory count table and it would have to be calculated from scratch taking in to account all of the past transactions in the transactions table. Are trigger a very safe way to use in the way I describe? Or should I try using views or stick with the temporary table solution we already have? My second part of the question is if there is a tutorial for triggers and stored procedures and what is the difference between Procedures and Functions? Thanks Beforehand! ---(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] Help using user rights
Hello, I have a question regarding user rights. I have one user in postgres8.01. which connects from a webserver and inserts some data into a table. It only has INSERT access on that table. ON the other hand, on the same table a have an after-insert trigger which executes a function owned by a more powerful user. My problem is that when I try to insert some data with the webserver user, PostgreSQL wants that user to have access to all the relations used in the trigger function. Is there any way to avoid granting the light user with all those rigths? Thank you very much! -- Cu respect, Valentin Militaru Telcor Communications Tel: 0316.900.015 Tel.Mobil:0741.168.267 Fax: 0316.900.001 Prezentul mesaj si orice fisier atasat constituie informatie confidentiala si este proprietatea exclusiva a Telcor Communications SRL. Mesajul se adreseaza numai persoanei fizice sau juridice mentionata ca destinatara, precum si persoanelor autorizate sa-l primeasca. In cazul in care nu sunteti destinatarul vizat sau persoana autorizata sa primiti acest mesaj, va aducem la cunostinta ca dezvaluirea, copierea, distribuirea sau initierea unor actiuni pe baza prezentei informatii sunt strict interzise si atrag raspunderea dvs. civila si penala. Daca ati primit acest mesaj dintr-o eroare, va rugam sa ne anuntati imediat si sa-l stergeti apoi din sistemul dvs. Nu putem garanta ca transmisia acestui mesaj este securizata sau fara erori. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pglogger and utf-8
Hi, I discorver yesterday pgllogger and try to use it. OS: OpenBSD 3.8 DB: postgreSQL 8.1 I have a database with UNICODE encondings. Sometimes in the log of PostgreSQL I got the error: LOG: statement: PREPARE unnamed AS INSERT INTO apache.log (clientaddress,rfc1413,username,localtime,httprequest,statuscode,byt essenttoclient,referer,clientsoftware,timespent,canonname) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) LOG: statement: BIND ERROR: invalid UTF-8 byte sequence detected near byte 0x89 I try to add enconding settings in pglogger.c of the client to ASCII, but I got always the same error. I ttry two things: 1) PQexec(conn,SET CLIENT_ENCODING TO 'SQL_ASCII'); 2) PQsetClientEncoding(conn, SQL_ASCII); Any help is welcomed. Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] AYUDA POR FAVOR
hola...Cómo estas? ..Sabes cómo hacer en postgres plpgsql hacer varios join?..además,necesito hacer un select dentro de otro select y asignarle el resultado a una variable de tipo record, pero no me deja, y necesito tomar en el record una variable fecha y hora pero el datetime pareciera que es sólo para asignar fecha y hora actual... POR FAVOR AYUDA,.. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plz unsubscribe me
On Wed, Aug 30, 2006 at 02:11:11PM -0400, Tom Lane wrote: Actually, what I'd like to see done is to get majordomo to bounce list messages containing unsubscribe in the subject, with an explanatory message about the right way to unsubscribe. There's no reason the rest of us should be bothered. Or what about automatically unsubscribing at that point? (In spite of other ways it's awful, mailman does that rather well.) A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Two billion records ok?
Nick Bower wrote: We're considering using Postgresql for storing gridded metadata - each point of our grids has a variety of metadata attached to it (including lat/lon, measurements, etc) and would constitute a record in Postgresql+Postgis. Size-wise, grids are about 4000x700 and are collected twice daily over say 10 years. As mentioned, each record would have up to 50 metadata attributes (columns) including geom, floats, varchars etc. So given 4000x700x2x365x10 2 billion, is this going to be a problem if we will be wanting to query on datetimes, Postgis lat/lon, and integer-based metadata flags? H... 2 billion looks optimistic... I get 2.044e+10, which is 20 billion. I'm currently working with a table of over 200,000,000 records. With a clustered index underlying partitioning, response times are more than acceptable (a 25 wide self relation (left outer join) for 3 months data (records are around 40/minute) is about 2 minutes. Simple query with a where clause on timestamp is a few secs at most. This is on a 32 bit Intel system with only 2Gb memory mirrored 7200RPM SATA hard drives. I'd suggest partition on timestamp, maybe per year at least, use a clustered index on timestamp. It should be viable if your hardware is up to it. I'd also strongly recommend a suitable platform, 64 bit Linux on AMD 64 or Opteron with as much memory the fastest RAID setup you can afford. Make sure you use a fully 64 bit version of Postgres/Postgis on this platform as well. If the same grid is being resampled, then a separate table defining the grid, and a join on grid ID to the main (partitioned) table may improve performance ( reduce data volume). I wouldn't expect instantaneous answers from it, but don't know of any reason it won't work. Depends very much on what level of performance is considered acceptable. I'm also using Postgis grids with up to a few million cells and spatial joins to a millions or so tracklines to generate gridded models for analysis in R. You seem to be scaling this up from where I'm at, so I'd like to know how you get on.. Cheers, Brent Wood ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] uConnect Voip
Does this device allow connection to other phones besides Skype, like Xten Xlite? http://www.voipvoice.com/UConnect-2.html. Compatibility with standard voip is not mentioned on their website? ---(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] Foxpro + PostgreSQL
Hi Mike, I have been using Visual FoxPro with Postgresql for two years. Currently my front end is Visual Foxpro 9. I do not have the Postgresql on a Windows server, I am on a Linux server that is running RedHat 9. I upgraded my Postgresql two months ago and I am on version 8.1.4. My clients connect with the Postgresql ODBC driver for Windows . I haven't had any problems with this combination. I handle most of the access rights in the VFP application. To access the server data I am using SQLCONNECT and then SQLEXEC to send queries. The program I use to manage the databases is PGADMIN III on Windows. There are other VFP programmers on the pgsql-general maillist and some of them work with both Postgresql and MySql (Windows and LInux). They have helped me before when I hit snags. Many of them have been using this combo since VFP 6. There are programmers on the www.UniversalThread.com which use this combo too. Hope this helps. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message. Mike Copeland [EMAIL PROTECTED] 09/07/2006 12:27 AM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Foxpro + PostgreSQL Margaret, I found a post of yours on the PostgreSQL website. It didn't look like you had ever gotten a response so I am writing to ask if you have pursued the Foxpro+PostgreSQL combo, or not. I have years of Foxpro (currently on 9) experience, and began using MySQL as a backend a couple of years ago. Now that I'm getting close to rolling out an application I wrote (Foxpro frontend, MySQL backend) I'm getting nervous about the licensing issues with MySQL. So, I'm looking into switching from MySQL to PostgreSQL. All that to ask, did you have any success in your attempts to use this combo? I've been using the sqlstringconnect function in Foxpro 9, but is that possible with PostgreSQL? There seems to be a dearth of info available about using FP9 as a frontend to PostgreSQL...everyone seems to focus on which is better, blah blah blah. Thanks for any and all feedback! Mike Copeland Genesis Software Group
Re: [GENERAL] Majordomo drops multi-line Subject:
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 As someone who does a lot of moderation using both mailman and majordomo every single day, I can assure everyone that both of them suck in their own way. Majordomo is nice in that I can view a whole bunch of lists at once, and accept/reject en masse, across many lists. Plus, it has a gazillion options, some of which are useful and that mailman does not have yet. Majordomo stinks because it only shows the from on the main moderation page, and not the subject line, which would *really* speed up the moderating. Mailman is nice in that it shows the subject, but also gives a whole irritating box per message, which makes quick moderating a pain. I'd rather have a single line list. Plus, I get the moderation requests on the regular inbox, so they get handled right away and discarded (the single key macro I wrote takes care of deleting the mail as well). I don't see how the web stuff can be any simpler. You've got to be kidding: that sounds horrible. I don't want an email for every message that comes in. I want to handle them all at once, on /my time frame/. In addition, many of the lists I moderate are admin'ed by more than one person, so sending the message to all moderators is extremely inefficient. As much as I dislike majordomo, I'm not seeing a strong argument here for replacing it. As far as I can tell, there are no problems with our using it: the unsubscribe-in-subject thing was added to pgsql-general and seems to be working just fine. - -- Greg Sabino Mullane [EMAIL PROTECTED] [EMAIL PROTECTED] End Point Corporation 610-983-9073 PGP Key: 0x14964AC8 200609071154 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFAEFfvJuQZxSWSsgRAhYuAKDZ3qqAdeRvPm6rIxTW/Ccs4KN35QCg41e1 q1/f0B5O/j8brzQVt3qm4Xk= =XZ+l -END PGP SIGNATURE- ---(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] uConnect Voip
On Thu, 2006-09-07 at 10:38, Frank Church wrote: Does this device allow connection to other phones besides Skype, like Xten Xlite? http://www.voipvoice.com/UConnect-2.html. Compatibility with standard voip is not mentioned on their website? I think you sent this to the wrong list... :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] uConnect Voip
On Thursday 07 September 2006 11:38, Frank Church wrote: Does this device allow connection to other phones besides Skype, like Xten Xlite? http://www.voipvoice.com/UConnect-2.html. Compatibility with standard voip is not mentioned on their website? wrong list. ---(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 -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(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] Problems with sequences
On 9/6/06, Arturo Perez [EMAIL PROTECTED] wrote:
What happens is that if I do a select nextval('seq') I get a number
that's lower than the
max primary key id. This is inspite of my doing
SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
ALTER SEQUENCE seq RESTART WITH max + 1;
select pg_catalog.setval(seq, max+1, true);
Your sequence was probably created with the CACHE parameter. This
will cause each session to cache n values from the sequence.
Resetting the sequence from another session will not affect the others
until they've gone through all their cached values.
-K
---(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] AYUDA POR FAVOR
María Soledad Ibarra Díaz wrote: hola...Cómo estas? ..Sabes cómo hacer en postgres plpgsql hacer varios join?..además,necesito hacer un select dentro de otro select y asignarle el resultado a una variable de tipo record, pero no me deja, y necesito tomar en el record una variable fecha y hora pero el datetime pareciera que es sólo para asignar fecha y hora actual... El idioma de esta lista es el inglés. Para formular tus consultas en español, te sugiero suscribirte a la lista pgsql-es-ayuda en este mismo servidor. Te sugiero también usar un Subject: más descriptivo de las preguntas que están haciendo, y redactar las preguntas con más claridad. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgres and emacs on windows os
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 07, 2006 2:46 AM To: pgsql-general@postgresql.org Subject: [GENERAL] postgres and emacs on windows os hi all, has anybody experience using psql with emacs (M-x sql-postgres) on windows. if i execute this command emacs prompts for user, database and host, but not for the password, so i think there is no response from psql. but if am looking at the emacs messages there is no error message if try to execute sql-statements or some psql commands nothing happens, but thats not unexpected, because witout the specification of the password i can't be logged in. thanks and greetings from munich christian Christian, Are you sure that nothing is actually happening, or are you just seeing no output? Unfortunately, the native Win32 psql program falls into non-interactive mode whenever it is run from most shell programs, and thus although it is actually running and executing your commands, it shows no output. We see this behavior whenever we run psql remotely via SSH, and I believe we've also seen it when we run psql from emacs. There was a patch posted a while back that added a force interactive mode flag to psql, but this was never added in to the real psql code base. The alternative we chose instead was to run the version of psql from Cygwin -- that version works fine from inside shell programs, and since we were already using Cygwin to provide remote SSH services among other things, it was an easy switch. (All you need are psql.exe and pq.dll, although you may need to install the complete PostgreSQL server from the Cygwin setup to get just these two files.) - Bill ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] uConnect Voip
Sorry, I realized that. On 9/7/06, Jan de Visser [EMAIL PROTECTED] wrote: On Thursday 07 September 2006 11:38, Frank Church wrote: Does this device allow connection to other phones besides Skype, like Xten Xlite? http://www.voipvoice.com/UConnect-2.html. Compatibility with standard voip is not mentioned on their website? wrong list. ---(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 -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database design and triggers...
It's not clear to me how your data is organized or exactly what you're counting. If I understand you correctly, yes, you could use triggers to maintain a table in this manner. However, why can't you simply use a SELECT query using the SUM() or COUNT() aggregate functions? If the queries are slow, do some index tuning. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 2:05 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Database design and triggers... Hi everybody. Hope I'm posting in the correct group. My question is part design and part implementation. Since we are creating an inventory system we need to have the clients pull up current inventory. Also, we need to have the past transactions stored for reference and billing. In our previous system in MS Access we accomplished this by adding up all of the transactions stored in two tables and generating a temporary table with the latest inventory count. The problem with this approach is that it is slow because the temporary table has to be created every time a user needs to see a report or work on a form. Even when instead of creating a temporary table we use a query it is still slow. With postgreSQL I found out about triggers and I figure that instead of calculating the current inventory count and storing it in a table every time a client needs it I could have a triggers maintain a table with the current count by incrementing or decreasing the amounts each time a transaction is stored in the transaction tables. My worry is that if for some reason a trigger were to somehow fail to execute correctly there would be an inconsistency between the transactions table and the current inventory count table and it would have to be calculated from scratch taking in to account all of the past transactions in the transactions table. Are trigger a very safe way to use in the way I describe? Or should I try using views or stick with the temporary table solution we already have? My second part of the question is if there is a tutorial for triggers and stored procedures and what is the difference between Procedures and Functions? Thanks Beforehand! ---(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] Foxpro + PostgreSQL
I have not had problems with integers but I am using a VFP class for SQL server backends that was written by someone else. Best thing is to try it yourself. If you need support sign up for the psql-general list. They're a good resource and have subscribers from all platforms. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message. Mike Copeland [EMAIL PROTECTED] 09/07/2006 11:06 AM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Re: Foxpro + PostgreSQL Margaret, It helps a lot! Now all I have to do is figure out why another guy wrote me back saying that he had struggled with using PostgreSQL for 2 years and recently switched to MySQL (along with buying licenses for his customers) and, in his words, it was a breath of fresh air! >From the rest of what he wrote, he indicates that PostgreSQL is a pain to manage...as in adding users, etc. I think I'll try PGADMIN III as you recommended below. Do you recall running into any weird data type problems with the ODBC connection? With MySQL, there's a weird thing with whole Integers versus decimal place number field types...I don't recall the exact problem but I do remember it drove me up the wall last year before I figured it out. Turned out to be an ODBC bug that no one has any desire to fix since there's a workaround. Thanks again and thanks for any feedback. Mike [EMAIL PROTECTED] wrote: Hi Mike, I have been using Visual FoxPro with Postgresql for two years. Currently my front end is Visual Foxpro 9. I do not have the Postgresql on a Windows server, I am on a Linux server that is running RedHat 9. I upgraded my Postgresql two months ago and I am on version 8.1.4. My clients connect with the Postgresql ODBC driver for Windows . I haven't had any problems with this combination. I handle most of the access rights in the VFP application. To access the server data I am using SQLCONNECT and then SQLEXEC to send queries. The program I use to manage the databases is PGADMIN III on Windows. There are other VFP programmers on the pgsql-general maillist and some of them work with both Postgresql and MySql (Windows and LInux). They have helped me before when I hit snags. Many of them have been using this combo since VFP 6. There are programmers on the www.UniversalThread.com which use this combo too. Hope this helps. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message. Mike Copeland [EMAIL PROTECTED] 09/07/2006 12:27 AM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Foxpro + PostgreSQL Margaret, I found a post of yours on the PostgreSQL website. It didn't look like you had ever gotten a response so I am writing to ask if you have pursued the Foxpro+PostgreSQL combo, or not. I have years of Foxpro (currently on 9) experience, and began using MySQL as a backend a couple of years ago. Now that I'm getting close to rolling out an application I wrote (Foxpro frontend, MySQL backend) I'm getting nervous about the licensing issues with MySQL. So, I'm looking into switching from MySQL to PostgreSQL. All that to ask, did you have any success in your attempts to use this combo? I've been using the sqlstringconnect function in Foxpro 9, but is that possible with PostgreSQL? There seems to be a dearth of info available about using FP9 as a frontend to PostgreSQL...everyone seems to focus on which is better, blah blah blah. Thanks for any and all feedback! Mike Copeland Genesis Software Group
[GENERAL] pgpool on Opteron server running FreeBSD 6.0
I'm running postgres 8.1.3 on a 2-CPU dual-core Opteron server with 8-Gigs of RAM. The server's operating system is FreeBSD 6.0. We migrated our client applications from Informix and went through all of the standard steps to resolve differences between the two databases packages. However, there are a handful of applications that generate connection storms which we hoped could be mitigated by using pgpool. We installed pgpool (num_init_children = 32 and max_pool = 1)ran an application requiring only 14 pgpool children and observed the following: a) The system load is 20 b) WCPU values for the 14 active pgpool processes exceed 20% c) Context switching on the server jumps as high as 250,000 The application now takes 4 (with pgpool) rather than less than two hours (without pgpool) to complete (it took less than an hour with Informix). Strange! Any ideas? Thanks -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.noaa.gov ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Differences lang with or without u example:pltcl and pltclu
Hi all I wonderring what could be differences beetwen languages with and without u for example: pltcl - pltclu plperl - plperlu Is anybodu know and could point me suitable link or something? Kindly regards Sebastian ---(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] Foxpro + PostgreSQL
On 9/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have not had problems with integers but I am using a VFP class for SQL server backends that was written by someone else. Best thing is to try it yourself. If you need support sign up for the psql-general list. They're a good resource and have subscribers from all platforms. I used VFP for pg ages ago and found it to be an 'ok' combination. Honestly, if you want to do that type of development, Delphi is superior in every way. However, if you must do it that way, pg is fine. The performance will be largely driven by the vfp odbc pass though engine, not the backend server. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Query performance inconsistant.
Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: From what you described, I would not expect many locking problems. Are there any other types of queries you run that may cause a lock? Row locks (SELECT FOR UPDATE/SHARE) are a possible problem, particularly if this is a pre-8.1 Postgres where exclusive row locks were used for foreign key constraints. regards, tom lane Tom, I'm still having issues with this so lemme provide more information perhaps there is something obvious Here is the proc that has very inconsistent (anywhere from 25ms to 8000ms) performance: CREATE FUNCTION acctmessage(_accttype character varying, _username character varying, _ipaddress character varying, _nastimestamp timestamp with time zone, _sessionid character varying, _nassessionid character varying, _nasipaddress character varying, _input octets bigint, _outputoctets bigint, _inputgigawords integer, _outputgigawords integer, _sessionlength bigint, _termcause charact er varying, _nasidentifier character varying, _clientipaddress character varying, _nasport character varying, _framedprotocol cha racter varying, _servicetype character varying, _connectinfo character varying) RETURNS void AS $$ DECLARE session_rec RECORD; BEGIN IF _clientipaddress '127.0.0.1' THEN INSERT into accounting_tab ( acctType, userName, ipAddress, nasTimestamp, sessionId, nasSessionId, nasIpAddress, inputOctets, outputOctets, inputGigaWords, outputGigaWords, sessionLength, termCause, nasIdentifier, clientIpAddress, nasPort, framedProtocol, serviceType, connectInfo ) values ( _acctType, _userName, _ipAddress, _nasTimestamp, _sessionId, _nasSessionId, _nasIpAddress, _inputOctets, _outputOctets, _inputGigaWords, _outputGigaWords, _sessionLength, _termCause, _nasIdentifier, _clientIpAddress, _nasPort, _framedProtocol, _serviceType, _connectInfo ); END IF; SELECT INTO session_rec sessionId FROM radutmp_tab WHERE sessionId = _sessionId; IF session_rec.sessionId IS NULL AND _acctType = 'start' THEN INSERT into radutmp_tab ( lastAcctType, userName, ipAddress, nasStartTimestamp, sessionId, nasSessionId, nasIpAddress, inputOctets, outputOctets, inputGigaWords, outputGigaWords, sessionLength, termCause, nasIdentifier, clientIpAddress, nasPort, framedProtocol, serviceType ) values ( _acctType, _userName, _ipAddress, _nasTimestamp, _sessionId, _nasSessionId, _nasIpAddress, _inputOctets, _outputOctets, _inputGigaWords, _outputGigaWords, _sessionLength, _termCause, _nasIdentifier, _clientIpAddress, _nasPort, _framedProtocol, _serviceType ) ; ELSIF session_rec.sessionId IS NOT NULL AND _acctType = 'stop' THEN UPDATE radutmp_tab SET lastAcctType = _acctType, nasStopTimestamp = _nasTimestamp, ipAddress = _ipAddress, sessionlength = _sessionlength, inputOctets = _inputOctets, outputOctets = _outputOctets, inputgigawords = _inputgigawords, outputgigawords = _outputgigawords, nasSessionId = _nasSessionId, nasIPAddress = _nasIPAddress, clientIPAddress = _clientIPAddress, nasPort = _nasPort, framedProtocol = _framedProtocol, termCause = _termCause WHERE sessionId = _sessionId AND userName = _userName AND serviceType = _serviceType; END IF; END; $$ LANGUAGE plpgsql; It looks long, but it's really pretty simple, it inserts data into the accounting_tab and then updates or inserts into the radutmp_tab table based on whether the session ID is known or not. Here are the tables: Table public.accounting_tab Column | Type | Modifiers -+--+--- sessionid | character varying(32)| not null nassessionid| character varying(32)| not null accttype| character varying(6) | not null username| character varying(20)| not null nastimestamp| timestamp with time zone | nasipaddress| character varying(15)| not null nasidentifier | character varying(15)| clientipaddress | character varying(15)| not null servicetype | character varying(6) | not null sessionlength | bigint | default 0 inputoctets | bigint | default 0 outputoctets| bigint | default 0 inputgigawords | integer | default 0 outputgigawords | integer | default 0 nasport | character varying(32)| ipaddress | character varying(32)| framedprotocol | character varying(32)| termcause | character varying(32)| timestamp | timestamp with time zone | default now() connectinfo | character varying(100) | Indexes:
Re: [GENERAL] Foxpro + PostgreSQL
I think the ODBC pass through engine has been enchaned in newer versions of foxpro because many companies are moving to SQLserver backends. At recent VFP conferences there are sessions devoted entirely to this subject. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 I used VFP for pg ages ago and found it to be an 'ok' combination. Honestly, if you want to do that type of development, Delphi is superior in every way. However, if you must do it that way, pg is fine. The performance will be largely driven by the vfp odbc pass though engine, not the backend server. merlin
Re: [GENERAL] Differences lang with or without u example:pltcl
SebaM wrote: Hi all I wonderring what could be differences beetwen languages with and without u for example: pltcl - pltclu plperl - plperlu Is anybodu know and could point me suitable link or something? It's trusted versus untrusted languages. Trusted languages only work within the postgres environment. Untrusted languages can include things or do things outside the postgres environment. This page is about pl/perl but the same idea applies to all the languages: http://www.postgresql.org/docs/8.1/interactive/plperl-trusted.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Differences lang with or without u example:pltcl
On Thu, 2006-09-07 at 22:56 +0200, SebaM wrote: Hi all I wonderring what could be differences beetwen languages with and without u for example: pltcl - pltclu plperl - plperlu Languages with a u should be untrusted by PostgreSQL, because they are free to do anything the language can do. For instance, in plperlu, you can make socket connections, open files, and do all kinds of dangerous things. Languages without a u can be safely trusted by PostgreSQL, because they are resricted to using a safe subset of the language's functionality. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Database design and triggers...
Roman -- You can certainly use a trigger to track changes (as well as creating an audit trail and enforcing rules); performance on inserts,updates and deletes will suffer accordingly since there's extra operations involved, but it definitely be a winner on the other end in generating reports and quick totals. As long as data changes are properly rolled into a transaction I can't think of any obvious ways this setup would fail -- the trigger changes would also be committed or rolled back, but you do need to pay attention to when your trigger fires (before or after). See for instance http://www.postgresql.org/docs/8.1/interactive/triggers.html (section 33 of the 8.1.4 documentation) for examples and a discussion of the different types. And from the point of view of PostgreSQL function and procedure are used interchangably; its not like some languages in which procedures don't return values but functions always do. (Someone more knowledgable please correct me if I am wrong on this!). HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED] Sent: Wed 9/6/2006 11:05 AM To: pgsql-general@postgresql.org Cc: Subject:[GENERAL] Database design and triggers... Hi everybody. Hope I'm posting in the correct group. My question is part design and part implementation. Since we are creating an inventory system we need to have the clients pull up current inventory. Also, we need to have the past transactions stored for reference and billing. In our previous system in MS Access we accomplished this by adding up all of the transactions stored in two tables and generating a temporary table with the latest inventory count. The problem with this approach is that it is slow because the temporary table has to be created every time a user needs to see a report or work on a form. Even when instead of creating a temporary table we use a query it is still slow. With postgreSQL I found out about triggers and I figure that instead of calculating the current inventory count and storing it in a table every time a client needs it I could have a triggers maintain a table with the current count by incrementing or decreasing the amounts each time a transaction is stored in the transaction tables. My worry is that if for some reason a trigger were to somehow fail to execute correctly there would be an inconsistency between the transactions table and the current inventory count table and it would have to be calculated from scratch taking in to account all of the past transactions in the transactions table. Are trigger a very safe way to use in the way I describe? Or should I try using views or stick with the temporary table solution we already have? My second part of the question is if there is a tutorial for triggers and stored procedures and what is the difference between Procedures and Functions? Thanks Beforehand! ---(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 --- Click link below if it is SPAM [EMAIL PROTECTED] https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=450038a9268108992556831[EMAIL PROTECTED]retrain=spamtemplate=historyhistory_page=1 !DSPAM:450038a9268108992556831! --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
