Re: [GENERAL] Error Codes?
> Rob Arnold wrote: > > > Look at $db->errstr That has the text version of the error code. > > > > --rob Yes, but I want to know about the codes, not the text. If the codes exist, they are easier to work with than the text. -Fran ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Error Codes?
I'm a bit confused about the current state of error codes in Pg. The docs and the mailing list archives seem to indicate that this is a TODO item, however, at least when using Perl with DBD::Pg, when I call $db->err() or examine $DBI::err I get a number back. For instance, 7 if I try to insert too many columns into a row, and a 1 if the connection fails. What's the scoop, and if error codes really do exist, is there a list of such codes? Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] replication?
Does postgres support replication? I think this is the feature I need. I have a table in one db that I need to use from another db on another machine. The first db is behind two firewalls (for a good reason =) and the second needs to be hung out on the internet, so I can't really use the same db. But I would like to have the table on the internet Pg install to be updated whenever the master table behind the firewalls is updated. It doesn't need to be real-time, but maybe once every hour or half hour. I certainly have no problem cheating and just copying a file over or something if that will work. I looked through the docs but didn't see anything about replication in the Administrator manual, where I assume it'd be discussed. I did see a vague reference to a replication toolkit in the release notes, is this what I'm looking for? Thanks, Fran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] do i need a view or procedure?
Matt, Thank you for the feedback! > I, too, am not a guru. Indeed, I'm so far from guru-hood that I'm not > even clear on why it is that you need anything more complicated than a > SELECT. Well, this may be exactly what we need, since a view is basically just a SELECT statement. It's just getting quite complicated so it might be easier to do it in a procedure instead (though I'm growing more confident that a stored procedure can't return a result set since I've yet to see any examples). > Then you might use a SELECT like this: > > "SELECT p.rpm-data >FROM patches p, servers s >WHERE s.id = xxx > AND s.criteria-1 = p.criteria-1 > AND s.criteria-2 = p.criteria-2 > AND s.criteria-3 = p.criteria-3 > AND p.version > s.version > AND ... > " This is what we started out doing too. The problem is that to pass a criteria doesn't necessarily mean you have to equal it. Sometimes it's equal or greater, sometimes it's not applicable at all, and often it depends on the particular patch - which is why it's so hard to make a general rule that applies to all patches and all servers. Some of the exception cases are that often we'll be testing something new out and release a patch destined for one and only one server whether or not it meets the criteria (this is actually easy to handle, it's just an OR in the above select statement, but they do get harder). We've been developing a view whose SELECT statement grows ever more complex as we realize new rules that must be observed. I think we'd be capable of just growing the SELECT statement indefinitely, but its getting messy and hard to understand and maybe even inefficient. I'm trying a new approach this week, seeing if the names and numbers of the patches themselves can do a lot of the legwork as to who is and is not eligible for a patch. We'll see how that goes. > You can even make the logic more complex, but perhaps more efficient, by > creating another table, this one containing perhaps three fields: > > server-id, patch-id, patch-version In fact, we have this exact table, which we called 'installs'. However, it's only part of the puzzle - the last part. After we whittle down to all of the eligible patches for a particular host, we then use this table to say which of those they already have installed and remove those from the result set. So, it does work very nicely for that. Thanks for the input, if nothing else, it gets the brain thinking about it in different ways. Thanks, Fran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] do i need a view or procedure?
Hello, I am trying to get my head around a complex problem I've been tasked with... We have a set of servers spread throughout the country (100s to 1000s of them) and I need to build a patch-distributing postgresql database. The actual patches would be distributed via rpm's which we'd like to store in the database (we pretty much have that part figured out). However, there are various server configurations and only some patches are going to be applicable to any one server. The decision process from an out-in-the-field server's point of view is something like this: - Connect to the central patch database - What patches are available? - Of these, which are intended for my OS? - Of these, which are intended for the major version of the software which I am running? - Of these, which are not already installed here? So that whittles down the number of eligible patches quite a bit. But then there's more... - Am I authorized to install patches designated for beta site or limited release? - Is this patch authorized for installation in my geographic region? - Are there any patches that are targeted uniquely to me as a site-specific patch? - I'm running version 1.2 of the software. Is this patch numbered 1.0.7 intended specifically only for 1.0 installs or is it ok for 1.x installs (we have both cases)? etc... As you can see, it becomes quite the tangled web. The database has a few tables...a host table with an entry for each field server, what their privileges are (beta site, limited release site, etc...), what region they are in, what OS they are running, what version of the software, etc...there's also a patches table, which contain both the actual patch binary and all the various info about the patch...what OS it's for...what version of the software it's for...if it's for general use or for a specific host, etc... the idea is that the client will be as dumb as possible. In other words, we hope it can be as simple as "select * from patchview where serverid=xxx" or "select availablepatches(xxx)" where xxx is the serverid. So, my task is to figure out which host is asking for which patches are available, and using that piece of information, build a result set that includes only the patches that pass all the criteria for that host. I see two approaches. The first is a view...drawbacks of the view approach are that the view cannot tailor which rows it's presenting based on a variable (i.e. a view can not present a different view of the table depending on who connected can it?) I think I would need one view for each server which quickly becomes unwieldy. Plus, I think the view is going to need some crazy unions and where clauses to get the full set and nothing but the set of eligible patches. The best we could come up with is a view that has a 1-to-1 mapping of serverid to patchid. So if you have 10 patches, and 10 servers, this view would have as many as 100 entries, if all 10 patches were applicable to all 10 servers. This gets fairly large when you start looking at real world projections of how many patches and servers we're going to have (something like maybe 100 patches x 1 servers) and we're still not even sure if a view can encompass all of the logic we need. Another, more elegant possibility would be to use a procedure which could build the result set piecewise (i.e. first get all the ones that are marked specifically for this host, store them off to the side, then go and get all the other ones that meet criteria B, add them to the pile, then go get those that meet C and add them, etc) and at the end of the procedure, return a big result set to the client. However, I don't see anything where the procedural languages can return a result set to the client, i'm not even sure if they can always return even a single row or not. A third option is that neither of us tasked with this are database gurus - we're mostly comfortable with them but it's not our bread and butter. So maybe we're taking the wrong approach completely. So, I'm just having a lot of trouble figuring out how to even approach this, so any feedback at all would be greatly appreciated! Thanks for reading this far :-) Sincerely, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] "trigger"ing a procedure every X minutes
Some thoughts are: 1. It would be portable with the database. I don't have to go setting up crons on the new machine if I move the db. 2. The cron usually involves writing a shell/perl/whatever script to call the psql client and pass in SQL queries via STDIN or file redirect just to invoke the desired procedure. An internal mechanism would bypass all of that. 3. All of the code that maintains the database could be in one place internal to the db. It's certainly not a big deal to use the cron, I just thought maybe someone had experimented with an internal mechanism. I'd like to try it someday, when I know a lot more about Pg. My current project has an ever-growing number of cron jobs (up to 6 now) and was just thinking about various ways to tidy it up. Thanks for the info, Fran > Fran Fabrizio <[EMAIL PROTECTED]> writes: > > Is the preferred method of calling a procedure every X minutes to have a > > cronjob installed external to the database, > > Yes. > > I see no need for us to replicate the functionality of cron ... > > regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Calling external programs
Hello, I would like to call an external program from Pg. Specifically, I'd like to send an email every time a row with certain attribute values is inserted into a table. I already have a trigger that fires upon insertion into the table. Is the preferred method to have a trigger call a procedure written in say PLPERL? Is PLPERL capable of calling external programs or better yet forming the mail and calling sendmail itself? Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Trigger only firing once
> Kinda hard to believe.
I know it, but that's what I am seeing. (to recap, my trigger only
fires on the first insert per connection.)
This will be kind of long, it's a spliced-together version of my psql
client session and the server log. I drop and re-create the procedure
and trigger, then I'll connect and test, and disconnect and reconnect and
show you what's happening. Comments with * stars around them
are my running commentary to make following this session easier.
* First, I use the text file to drop and recreate the procedure and
trigger. *
postgres@rusty ~$ psql monitoring < log_trigger
DROP
DROP
CREATE
CREATE
postgres@rusty ~$
* Server log shows dropping and creating (long procedure, sorry)
*
2001-05-14 11:51:12 DEBUG: StartTransactionCommand
2001-05-14 11:51:12 DEBUG: query: /* trigger to update the host table
for incoming records
The trigger will update the update the date and timestamps
for the host, insert a newhost if one is not found */
drop function update_host_table();
2001-05-14 11:51:12 DEBUG: ProcessUtility: /* trigger to update the host
table for incoming records
The trigger will update the update the date and timestamps
for the host, insert a newhost if one is not found */
drop function update_host_table();
2001-05-14 11:51:12 DEBUG: CommitTransactionCommand
2001-05-14 11:51:12 DEBUG: StartTransactionCommand
2001-05-14 11:51:12 DEBUG: query: drop trigger log_trigger on log ;
2001-05-14 11:51:12 DEBUG: ProcessUtility: drop trigger log_trigger on
log ;
2001-05-14 11:51:12 DEBUG: CommitTransactionCommand
2001-05-14 11:51:12 DEBUG: StartTransactionCommand
2001-05-14 11:51:12 DEBUG: query: create function update_host_table()
returns opaque
as 'declare
site_recrecord;
host_recrecord;
status_rec record;
begin
new.tstamp := now() ;
/* check to see if we have see this site before */
select * into site_rec
from sites s
where s.fqdn = new.fqdn ;
/* -- if we have not found the machine name we are going to
insert a new record into the sites table and set the init_contact to now
*/
if not found
then
insert into sites
values
(nextval(''sites_site_id_seq''),new.fqdn,new.site,new.region,''f'',new.tstamp)
;
/* we also have to insert a new host if this is a new site */
insert into hosts
values
(nextval(''hosts_host_id_seq''),currval(''sites_site_id_seq''),new.hostname,new.tstamp)
;
/* now update the incoming record with the new host_id and
site_id */
new.site_id=currval(''sites_site_id_seq'');
new.host_id=currval(''hosts_host_id_seq'');
else
/* we have seen the site before, update the incoming records
site_id */
new.site_id = site_rec.site_id ;
/* if we have seen this site before we need to check and see if
we have ever seen this machine before */
select * into host_rec
from hosts h
where h.hostname = new.hostname and
h.site_id = site_rec.site_id ;
/* new host */
if not found
then
insert into hosts
values
(nextval(''hosts_host_id_seq''),site_rec.site_id,new.hostname,new.tstamp)
;
new.host_id = currval(''hosts_host_id_seq'');
else
new.host_id = host_rec.host_id ;
update hosts
set last_contact = new.tstamp
where hosts.host_id = new.host_id ;
end if ;
/* update sites
set last_contact = new.tstamp
where sites.fqdn = new.fqdn ; */
end if ;
/* now we are going to update the status table with the new record */
select * into status_rec
from status s where
s.site_id = new.site_id and
s.host_id = new.host_id and
s.product = new.product and
s.class = new.class and
s.subclass = new.subclass ;
/* new monitored process */
if not found
then
insert into status
values (new.site_id,
new.host_id,
new.product,
new.class,
new.subclass,
new.status,
new.msg,
new.tstamp);
else
update status
set status = new.status,
tstamp = new.tstamp
where site_id = new.site_id and
host_id = new.host_id and
product = new.product and
class = new.class and
subclass = new.subclass ;
end if ;
return new;
end ;'
language 'plpgsql';
2001-05-14 11:51:12 DEBUG: ProcessUtility: create function
update_host_table()
returns opaque
as 'declare
site_recrecord;
host_recrecord;
status_rec record;
begin
new.tstamp := now() ;
/* check to see if we have see this site before */
select * into site_rec
from sites s
where s.fqdn = new.fqdn ;
/* -- if we have not found the machine name we are going to
insert a new record into the sites table and set the
[GENERAL] Trigger only firing once
Anyone have any clues as to my question yesterday re: why my trigger only fires on the first insert per connection? After posting yesterday, I tried a few different things, but the only thing that works (and obviously not the most efficient thing to do) is to disconnect and reconnect after every insert. This trigger is: create trigger log_trigger before insert on log for each row execute procedure update_host_table(); The trigger runs fine, the procedure it calls runs fine, but it only executes the trigger once per connection. The odd thing is that I generated the trigger and the procedure from a text file of sql which hasn't changed in weeks. It worked perfectly in postgresql 7.0, but now that I think about it, I can't confirm it ever working correctly in 7.1 since I upgraded earlier in the week. Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] formatting a date
I'm looking all over the place in the Pg docs and Momjian book and having no luck finding any functions that would turn a timestamp such as 2001-05-08 23:59:59-04 into May 8, 2001. (i.e. do what date_format() was doing for me in MySQL.) Is there equivalent functionality in Pg? Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Trigger only firing once
What would cause this trigger: create trigger log_trigger before insert on log for each row execute procedure update_host_table(); to only fire on the first insert per connection, but none of the subsequent inserts? The trigger runs fine, the procedure it calls runs fine, but it only executes the trigger once per connection. Thanks, Fran ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] select off of a view going slowly
Hello all, I've finished upgrading to 7.1 and I'm still having trouble getting this view to run respectably. Does a view run its query every time I select from the view? I had been assuming that the view recreates itself when I modify the table to which the view is attached, but Tom mentioned that adding an index on tstamp (which i did) might help, which would imply that every time I select from the view it is querying the original table (since tstamp lives there but not in the view). I did add the index (and one on status) with no effects. It's still taking wa long to run the select against this view (see stats below). I've included some select queries and their stats, a \d of the error_log view, a \d of the log table from which it is derived, and an EXPLAIN on the typical-case query. I'm not real familiar with views so maybe I am not using them in the proper way.Any pointers at all would be greatly appreciated, as this seems to be the last hurdle I need to get over before I have a usable application. Thank you very much! (Also, I promise that once I get this application working and can get my boss off my back, I'll take some time and learn about views, and relational databases in general, more deeply. Then maybe I can help out around here some more! =) Thanks, Fran monitoring=# select * from error_log; count | site_id | host_id ---+-+- 8 | 34 | 88 8 | 34 | 110 (2 rows) The query statistics: 2001-05-07 16:31:57 DEBUG: query: select * from error_log; 2001-05-07 16:31:57 DEBUG: ProcessQuery 2001-05-07 16:32:02 DEBUG: CommitTransactionCommand QUERY STATISTICS ! system usage stats: ! 4.428527 elapsed 3.72 user 0.71 system sec monitoring=# select * from error_log where site_id=34 and host_id=88; count | site_id | host_id ---+-+- 8 | 34 | 88 (1 row) 2001-05-07 16:32:46 DEBUG: query: select * from error_log where site_id=34 and host_id=88; 2001-05-07 16:32:46 DEBUG: ProcessQuery 2001-05-07 16:32:48 DEBUG: CommitTransactionCommand QUERY STATISTICS ! system usage stats: ! 2.152403 elapsed 1.53 user 0.62 system sec monitoring=# \d error_log View "error_log" Attribute | Type | Modifier ---+-+-- count | integer | site_id | bigint | host_id | bigint | View definition: SELECT count(*) AS count, log.site_id, log.host_id FROM log WHE RE (((log.status = 'CRIT'::"varchar") OR (log.status = 'EMERG'::"varchar")) AND (log.tstamp > (now() - '1 day'::"interval"))) GROUP BY log.site_id, log.host_id; monitoring=# \d log Table "log" Attribute | Type | Modifier --+--+-- site_id | bigint | host_id | bigint | fqdn | character varying| not null site | character varying| not null region | character varying| not null hostname | character varying| not null product | character varying| not null class| character varying| not null subclass | character varying| not null status | character varying| not null msg | character varying| not null remote_stamp | timestamp with time zone | not null tstamp | timestamp with time zone | not null Indices: log_hostid_index, log_siteid_hostid_index, log_siteid_index, log_status_index, log_tstamp_index monitoring=# explain select * from error_log where site_id=34 and host_id=88; NOTICE: QUERY PLAN: Subquery Scan error_log (cost=33145.20..33145.21 rows=1 width=16) -> Aggregate (cost=33145.20..33145.21 rows=1 width=16) -> Group (cost=33145.20..33145.21 rows=1 width=16) -> Sort (cost=33145.20..33145.20 rows=1 width=16) -> Seq Scan on log (cost=0.00..33145.19 rows=1 width=16) EXPLAIN monitoring=# ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Stranger than fiction - EXPLAIN results
I'm sorry that I did not send the EXPLAIN results along with my original email, I had no idea this command existed (I'm a first-week rookie with postgres!) Also, I did not know about vacuum, and apparently, that was the culprit! After vacuum'ing, times went from .433 sec/query to .001. Holy moly! I read up on vacuum in the Postgres book we have, and I guess since I've been doing so much testing, I've been really updating a LOT of rows in the table, because here's what the vacuum results looked like: 010502.11:13:46.469 [3029] StartTransactionCommand 010502.11:13:46.469 [3029] query: vacuum status; 010502.11:13:46.469 [3029] ProcessUtility: vacuum status; 010502.11:13:46.470 [3029] DEBUG: --Relation status-- 010502.11:13:46.978 [3029] DEBUG: Pages 5700: Changed 0, reaped 5700, Empty 0, New 0; Tup 224: Vac 364719, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 100, MaxLen 208; Re-using: Free/Avail. Space 45161576/45149008; EndEmpty/Avail. Pages 1/5698. CPU 0.25s/0.25u sec. 010502.11:13:46.980 [3029] DEBUG: Index status_5_column_index: Pages 4; Tuples 224: Deleted 0. CPU 0.01s/0.00u sec. 010502.11:13:50.641 [3029] DEBUG: Index status_site_id_key: Pages 920; Tuples 224: Deleted 364719. CPU 0.24s/3.36u sec. 010502.11:13:54.335 [3029] DEBUG: Index status_host_id_key: Pages 930; Tuples 224: Deleted 364719. CPU 0.17s/3.46u sec. 010502.11:13:55.252 [3029] DEBUG: Rel status: Pages: 5700 --> 4; Tuple(s) moved: 224. CPU 0.78s/0.14u sec. 010502.11:13:55.256 [3029] DEBUG: Index status_5_column_index: Pages 6; Tuples 224: Deleted 224. CPU 0.00s/0.00u sec. 010502.11:13:55.307 [3029] DEBUG: Index status_site_id_key: Pages 920; Tuples 224: Deleted 224. CPU 0.05s/0.00u sec. 010502.11:13:55.358 [3029] DEBUG: Index status_host_id_key: Pages 930; Tuples 224: Deleted 224. CPU 0.05s/0.00u sec. 010502.11:13:55.385 [3029] CommitTransactionCommand Can someone explain to me these results? What does it mean when it says Deleted 364719 of Index status_site_id_key, for example? And then later on it goes back to that index and deletes another 224. What is going on internally? Also, after vacuuming the table, I reran my select query 010502.11:22:57.579 [3029] StartTransactionCommand 010502.11:22:57.579 [3029] query: select * from status s where s.site_id = 18 and s.host_id = 49 and s.product = 'BETA' and s.class = 'APPS' and s.subclass = 'MONITOR' ; 010502.11:22:57.581 [3029] ProcessQuery 010502.11:22:57.582 [3029] CommitTransactionCommand As you can see, .001s execution time! However - EXPLAIN still shows: 010502.11:24:05.001 [3029] StartTransactionCommand 010502.11:24:05.002 [3029] query: explain select * from status s where s.site_id = 18 and s.host_id = 49 and s.product = 'BETA' and s.class = 'APPS' and s.subclass = 'MONITOR' ; 010502.11:24:05.002 [3029] ProcessUtility: explain select * from status s where s.site_id = 18 and s.host_id = 49 and s.product = 'BETA' and s.class = 'APPS' and s.subclass = 'MONITOR' ; 010502.11:24:05.003 [3029] NOTICE: QUERY PLAN: Seq Scan on status s (cost=0.00..9.04 rows=1 width=84) 010502.11:24:05.003 [3029] CommitTransactionCommand Which is incorrect because it should be using my index, shouldn't it? Here is how I created my index: monitoring=# create index status_5_column_index on status (site_id, host_id, product, class, subclass); And a desc. of the table shows the index is there: monitoring=# \d status Table "status" Attribute | Type| Modifier ---+---+-- site_id | bigint| not null host_id | bigint| not null product | varchar() | not null class | varchar() | not null subclass | varchar() | not null status| varchar() | not null msg | varchar() | tstamp| timestamp | Indices: status_5_column_index, status_host_id_key, status_site_id_key monitoring=# So it's puzzling me why it's still doing a Seq. Scan vs. an Index Scan. Any ideas? Thanks, Fran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] DBI/AutoCommit/Postgres
Hello all,
I'm trying to speed up some insert statements. I have been tinkering
with the postmaster and DBI parameters I did some timings on my insert
and copy commands. Here is a sample insert query:
010430.18:31:18.199 [2604] query: insert into log values
(0,0,lower('blah.blah.mydomain.com'),lower('foo'),lower('bar'),lower('blah'),upper('Me'),
upper('Myself'), upper('I'), upper('INFO'), 'String Here', '20010430
16:00:00')
Pretty straightforward. Table log looks like:
Table "log"
Attribute | Type| Modifier
--+---+--
site_id | bigint|
host_id | bigint|
fqdn | varchar() | not null
site | varchar() | not null
region | varchar() | not null
hostname | varchar() | not null
product | varchar() | not null
class| varchar() | not null
subclass | varchar() | not null
status | varchar() | not null
msg | varchar() | not null
remote_stamp | timestamp | not null
tstamp | timestamp | not null
Here are my non-scientific timings:
with AutoCommit on, using DBI across TCP/IP: 1.3 INSERTS/second
with AutoCommit off, DBI, TCP/IP, committing after every 100: 1.6
INSERTS/second
using psql -h host -U user -c "copy log from stdin" dbname < datafile
1.73 rows/second
using COPY LOG FROM 'filename' on the db machine itself: 1.73
rows/second
Another crucial piece of information is that each insert kicks off a
trigger. I did not write the trigger, and do not know how to write
triggers, but I think that might be the contributing factor to the
slowness. Here is the text file used to create the trigger:
drop function update_host_table();
drop trigger incoming_trigger on incoming ;
create function update_host_table()
returns opaque
as 'declare
myrec record;
new_hostid int4;
begin
new.timestamp := now() ;
/* check to see if we have see this machine before */
select * into myrec
from knownhosts k
where k.fqdn = new.fqdn and
k.hostname = new.hostname ;
/* -- if we have not found the machine name we are going to
insert a new record into the knownhosts table and set the init_contact
to now
*/
if not found
then
insert into knownhosts
values (new.fqdn,new.hostname,new.timestamp,new.timestamp) ;
else
update knownhosts
set last_contact = new.timestamp
where knownhosts.fqdn = new.fqdn ;
end if ;
/* now we are going to update the status table with the new record */
select * into myrec
from status s where
s.fqdn = new.fqdn and s.hostname=new.hostname
and s.class=new.class and s.sub_class=new.sub_class ;
if not found
then
insert into status
values (new.fqdn,new.hostname,new.class,
new.sub_class,new.level,new.msg,new.timestamp) ;
else
update status
set level = new.level,
timestamp = new.timestamp
where fqdn=new.fqdn and hostname=new.hostname and
class = new.class and sub_class = new.sub_class ;
end if;
return new;
end ;'
language 'plpgsql';
create trigger incoming_trigger
before insert on incoming
for each row
execute procedure update_host_table();
1.73 INSERTS/second seems awfully slow, but maybe I have set my
expectations too high. Now that you all can see the table and the kind
of data I am trying to put into it, do you have any suggestions? The
hardware specs of the database machine are: Pentium III 733Mhz, 512
megs memory, 7 gigs free on the partition. Seems like I should be
getting a lot more horsepower. I really need to speed this up somehow.
Does anyone see anything in the trigger or otherwise that would cause
this to be so slow?
Thank you very much,
Fran
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] installing DBD::Pg without installing postgres
Michelle & John> Yes, that's exactly what I am trying to accomplish. I am setting up a network monitoring application for our support personnel, and I have multiple people all examining data that gets reported to a central database. So I didn't relish the idea of installing postgres on all of the support personnel workstations. Jeff> Thanks for the tip about DBI::Proxy. That seems to be the missing link, as I've also run into this problem with MySQL in addition to Postgres. MySQL does have a libs-only rpm that you can use for installing the MySQL DBD without having MySQL locally, but DBI::Proxy may be an even cleaner solution. Thanks everyone for the dialogue, it has been very useful! -Fran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] installing DBD::Pg without installing postgres
> What's the dependencies for the DBD::Pg RPM? Satisfy those > dependencies, and properly set up for client-server communications with > a postgresql server, and it _should_ just _work_. Well, if I had known what it took to satisfy the dependencies, I wouldn't have needed to post here. ;-) It was looking for a libpg-fe.h. This file does not appear to be in the libs rpm, which is the only thing I can install without needing to download the entire source. In the interest of a quicker resolution, I just went ahead and installed postgres. I had to install the libs rpm, then the postgres rpm itself, then the devel rpm in order to find the file. Since the devel depends on postgres itself, I did have to install postgres in order to install DBD Pg. Which seems wrong somehow. libpg-fe.h seems to be available from two places: in the source .tar.gz in the interfaces/ subdir, or in the devel rpm, which requires the source rpm. So either way, you have to grab the source in one form or another. Oh well. I just hoped that there was a libs rpm or .tar.gz that would allow me to build these other tools without requiring the eitire source of postgres itself. Maybe my hopes are misguided. =) Thanks, Fran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] installing DBD::Pg without installing postgres
Hello, It seems that there should be a way to install the DBD Pg module without having to install postgres on the local machine. I tried installing just the libs rpm, but that didn't seem to do the trick. I've done some usenet and mailing list archive searches, but all the info I'm turning up appears geared towards the assumption that you also want postgres installed locally. Am I looking in the wrong places? Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] getting the currval of a sequence
Hi all,
I need to do the equivalent of:
select my_sequence.CURRVAL from Sys.dual;
so I'm guessing its:
select currvar('my_sequence') from ???;
Can anyone fill in the Thanks!
-Fran
[GENERAL] designating a column as primary key after creation
Why does postgres choke on the following: alter table mytable add constraint mycolumn_pk primary key(mycolumn); is this possible in a postgres database? if not, what's an easy workaround, i really need to have this column as primary key. Thanks! -Fran
