[ADMIN] Create Rule/trigger
Title: Create Rule/trigger Hi All, Can we create trigger/rule that specifies Do action ‘Y’ when inserts/updates to any table in the database ‘X’ occurs? Thx. In advance. Anagha
Re: [ADMIN] configure readline error
John McGloughlin writes: > >> config.log (sorry for the BIG file ... wasn't sure if you wanted the > whole thing or not Looks like you need to install (or make available) the termcap library. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Create Rule/trigger
Anagha Joshi writes: > Can we create trigger/rule that specifies > Do action 'Y' > when > inserts/updates to any table in the > database 'X' occurs? No, you need to create a trigger/rule for each table. If you just want to audit things, maybe you could crank up the log level to log all statements in the server log. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Plz help: PostgreSQL takes too much disk-space
Hello! We use the PostgreSQL Version 7.2.2 bundled with SuSE Linux 8.2 Professional. Our database server contains 20 databases, many smaller and some larger ones. 10 of these databases contain most of the data. These data need to be updated every day. We delete all rows of the each table using "delete from tab" and then we fill them again using the COPY-command. The data that we insert using the COPY command is converted from a text file we receive every evening. The complete operation takes about 1 hour. After we updated all of our tables, we do a "vacuumdb --all --full". Now the problem: We encountered that the database is growing far more rapidly than the data! Our database currently consumes 20GB (!) of disk space! The strange effect is: When we do a "pg_dumpall > db.dmp" and insert this dump into a clean DB-server environment, the consumed disk space is 2.1 GB!! So you see, that we do not have that much data to really fill anything around 20GB... When I do a "select sum(relpages) from pg_class where relname not like 'pg_%';" to get the used pages from all non-system tables the result is: 849034. As we did not change the block size, we get 849034 * 8k = 6792272 k = 6,5 GB Then for the system tables: "select sum(relpages) from pg_class where relname like 'pg_%';" The result is: 267 => 267 * 8k = 2 MB As you see, PostgreSQL thinks it is using 6,5 GB, but "du -sh" says 20GB ... Any help / hints / links / ideas are VERY appreciated! Thank you all in advance Stefan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Plz help: PostgreSQL takes too much disk-space
It sounds like you may have uncontrolled index growth. This happens when you update indexes by adding information to one end, and removing it from the other. The index becomes lop sided (they're btrees) and begins to grow. you can either reindex the indexes, drop and recreate them, or wait for 7.4 which has a fix for that in it. (Thanks to Tom.) On Wed, 18 Jun 2003, Gaffga, Stefan wrote: > Hello! > > We use the PostgreSQL Version 7.2.2 bundled with SuSE Linux 8.2 > Professional. > Our database server contains 20 databases, many smaller and some larger > ones. > > 10 of these databases contain most of the data. These data need to be > updated every day. We delete all rows of the each table using "delete from > tab" > and then we fill them again using the COPY-command. The data that we insert > using > the COPY command is converted from a text file we receive every evening. > > The complete operation takes about 1 hour. > > After we updated all of our tables, we do a "vacuumdb --all --full". > > Now the problem: We encountered that the database is growing far more > rapidly than > the data! > > Our database currently consumes 20GB (!) of disk space! > > The strange effect is: When we do a "pg_dumpall > db.dmp" and insert this > dump > into a clean DB-server environment, the consumed disk space is 2.1 GB!! > So you see, that we do not have that much data to really fill anything > around 20GB... > > When I do a "select sum(relpages) from pg_class where relname not like > 'pg_%';" to get > the used pages from all non-system tables the result is: 849034. > As we did not change the block size, we get 849034 * 8k = 6792272 k = 6,5 GB > > Then for the system tables: "select sum(relpages) from pg_class where > relname like 'pg_%';" > The result is: 267 => 267 * 8k = 2 MB > > As you see, PostgreSQL thinks it is using 6,5 GB, but "du -sh" says 20GB ... > > Any help / hints / links / ideas are VERY appreciated! > > Thank you all in advance > Stefan > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] psql sequence question
Title: so it sounds like I just need to tell my programmer friend to change his use of last value to currval instead? I just hate silly mistakes like this! Thanks for the help. Jodi Bruno Wolff III wrote: On Mon, Jun 16, 2003 at 11:24:28 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: If I'm using transactions (not autocommit), are sequences atomic? Yes. In other words, after inserting a record to a table that uses sequence A, am I guaranteed that select last_value on sequence A is atomic, and cannot be interfered with by other transactions using that same sequence? Sequence A is used byseveral tables. Probably not in the way you mean. Every transaction is going to seea consistant view of the sequence table. However transactions proceedingin parallel may seem the same value for the last value. To make thiswork you would need to use serializable mode to do any updates basedon the value of the last value or lock the table exclusively to preventconcurrent updates. This defeats the function of sequences providingunique values using light weight locking.The right way to use sequences is to use nextval to get new valuesand use currval to reuse the value you got from the latest call tonextval in the same session.---(end of broadcast)---TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[ADMIN] checking currval
Title: I cannot view the currval because it is not set in the session yet. Is there a way to set it without having to increment the value? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] checking currval
select last_value from your_sequence; Jodi Kanter wrote: I cannot view the currval because it is not set in the session yet. Is there a way to set it without having to increment the value? Thanks Jodi ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] checking currval
Title: I tried that and it did not work. It won't be a problem in the code since it appears that we are always doing a nextval prior to currval, but I was just wondering about how to do it while in a psql session. Weiping He wrote: select last_value from your_sequence; Jodi Kanter wrote: I cannot view the currval because it is not set in the session yet. Is there a way to set it without having to increment the value? Thanks Jodi ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] configure readline error
John, The trick on my RedHat 7.3 system was to install the termcap-devel, readline-devel, and zlib-devel RPMs from the installation CDs. -dylan- > -Original Message- > From: John McGloughlin [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 17, 2003 21:03 > To: [EMAIL PROTECTED] > Subject: [ADMIN] configure readline error > > > Hello, > > I'm having trouble with ./configure for Postgres 7.3.3 on a > RH7.3 i686 > system. > > checking for readline... no > readline library not found > if you have readline already installed . > > I've gone through config.log and it looks like it searches > the correct > locations. > > I have gnu readline 4.1 installed and can find libs in > /usr/local/lib/libreadline.a and headers in > /usr/local/lib/include/readline/*.h. > > I configured with ./configure > --with-includes=/usr/local/lib:/usr/local/lib/include/readline > --with-libraries=/usr/local/lib:/usr/local/lib/include/readline > --with-perl --with-openssl --with-syslog > > Unfortunately I keep getting the error. I've searched numerous lists, > groups, faqs, etc. but cannot seem to resolve the issue. Any > help would > be greatly appreciated. > > Thanx in advance! > > johnmac > > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] psql sequence question
On Wed, Jun 18, 2003 at 09:49:58 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: > so it sounds like I just need to tell my programmer friend to change his > use of last value to currval instead? This is most likely what you want to do. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] checking currval
On Wed, Jun 18, 2003 at 09:55:51 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: > I cannot view the currval because it is not set in the session yet. Is > there a way to set it without having to increment the value? What value do you expect to see and why do you want to see it? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] checking currval
What flavor Postgresql are you running? That works on both my 7.2.x boxes and my 7.3 boxes. If you're running anything older than 7.2.x, you might wanna upgrade before asking for help, since folks tend to assume you're running at least 7.2 around here. Note that this IS NOT TRANSACTION safe. If two processes do this at the same time and operate on that number, you can have a race condition that will cause you problems. Use the nextval,setval,currval functions for transaction safe sequence work. On Wed, 18 Jun 2003, Jodi Kanter wrote: > I tried that and it did not work. It won't be a problem in the code > since it appears that we are always doing a nextval prior to currval, > but I was just wondering about how to do it while in a psql session. > > Weiping He wrote: > > > select last_value from your_sequence; > > > > Jodi Kanter wrote: > > > >> I cannot view the currval because it is not set in the session yet. > >> Is there a way to set it without having to increment the value? > >> Thanks > >> Jodi > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > > ---(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
[ADMIN] PostgreSQL calibration
What's your thought on creating some type of calibration applet that will divine proper settings for the PostgreSQL performance related variables by taking into consideration the speed of the machine, amount of RAM, access time of the filesystem, and the speed of various queries on a standardized (perhaps non-standardized?) dataset under different conditions? Is this possible, do-able, or even wanted? Where would one begin constructing the tests? I imagine it would automate the tasks that every PostgreSQL administrator goes through to calibrate his or her installation with the best possible performance settings... CG __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] PostgreSQL calibration
Sure, it would be great if we could do it. --- Chris Gamache wrote: > What's your thought on creating some type of calibration applet that will > divine proper settings for the PostgreSQL performance related variables by > taking into consideration the speed of the machine, amount of RAM, access time > of the filesystem, and the speed of various queries on a standardized (perhaps > non-standardized?) dataset under different conditions? > > Is this possible, do-able, or even wanted? Where would one begin constructing > the tests? > > I imagine it would automate the tasks that every PostgreSQL administrator goes > through to calibrate his or her installation with the best possible performance > settings... > > CG > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] PostgreSQL calibration
Bruce Momjian <[EMAIL PROTECTED]> writes: > Sure, it would be great if we could do it. If the program actually derives reliable numbers, it would be great. It could easily do more harm than good if it gives bogus results. I think it will be very hard to get reliable rather than bogus results :-( ... but feel free to try. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Database Encryption
I'm interested in using a passwords table with my Access front end, but need to encrypt the passwords over the network. Can anyone help me get started on this kind of project? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] postgreSQL 7.3.3 crashing on server with Itanium processor...
Hi All , H/W Configuration - Dell 7150 Quad Itanium 1 ( 733 MHz) with 4G RAM OS- Red Hat 7.2 - Postgresql ver 7.3.3 compiled from raw hide red hat rpms. Problem : We were able to install postgreSQL 7.3.3 successfully on this enviornment , but server crashes randomly and throws following error : * LOG: server process (pid 11220) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing shared memory and semaphores LOG: database system was interrupted at 2003-06-17 10:29:08 PDT LOG: checkpoint record is at 0/83CEC8 LOG: redo record is at 0/83CEC8; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 480; next oid: 16976 LOG: database system was not properly shut down; automatic recovery in progress LOG: ReadRecord: record with zero length at 0/83CF10 LOG: redo is not required LOG: database system is ready FATAL: Database "template0" is not currently accepting connections FATAL: IDENT authentication failed for user "zippool1" LOG: fast shutdown request LOG: shutting down LOG: database system is shut down *** Any help would be highly appreciated ! Thanks in Advance ! Regards ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] Perl DBI question
Hi,
I'm trying to setup some scripts that will allow me to use Perl DBI to
INSERT into my table. However, I have not had any success at all. I'm
using perl CGI as well so I've granted ALL permissions on my table to
the apache user and I still can't INSERT. I can, however, UPDATE and
SELECT on the table, just not INSERT.
Here is a piece of my code:
#--Establish the DB connection
#--Assign the DB name
$dbName = 'checkbook';
#--Connect to the Pg DB using DBI
my $dbh = DBI->connect("dbi:Pg:dbname=$dbName");
$sth = $dbh->do("INSERT INTO transactions
(date,description,amount,confirmation,nameid,typeid) VALUES
('$datePaid','$description','$amount','$confirmation',$nameid,$typeid)")
;
Please HELP...
Thanks,
Mike
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[ADMIN] pgsql function doesn't work
Hi,
the function bellow is created successfully, but executing it forces the
following error message:
WARNING: Error occurred while executing PL/pgSQL function
drop_table_if_exists
WARNING: line 5 at select into variables
ERROR: parser: parse error at or near "$1" at character 34
CREATE OR REPLACE FUNCTION drop_table_if_exists(TEXT) RETURNS BOOLEAN AS '
DECLARE
rec RECORD;
BEGIN
SELECT INTO rec off_mitarbeiter_id FROM $1;
IF FOUND THEN
EXECUTE ''DROP TABLE'' || '' '' || $1;
RETURN true;
END IF;
RETURN false;
END;'
LANGUAGE 'plpgsql';
select drop_table_if_exists('off_jahres_abr_2003');
If I hardcode the table name istead of using $1, everything works fine.
Please help.
Thanx Erwin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] pgsql function doesn't work
On Wed, 18 Jun 2003, Erwin Ambrosch wrote: > Hi, > > the function bellow is created successfully, but executing it forces the > following error message: > > WARNING: Error occurred while executing PL/pgSQL function > drop_table_if_exists > WARNING: line 5 at select into variables > ERROR: parser: parse error at or near "$1" at character 34 > > > CREATE OR REPLACE FUNCTION drop_table_if_exists(TEXT) RETURNS BOOLEAN AS ' > DECLARE >rec RECORD; > BEGIN > >SELECT INTO rec off_mitarbeiter_id FROM $1; You can't do this in plpgsql without some form of EXECUTE. >IF FOUND THEN > EXECUTE ''DROP TABLE'' || '' '' || $1; > RETURN true; >END IF; > >RETURN false; > > END;' > LANGUAGE 'plpgsql'; However, AFAICS this will error if the table doesn't exist (selecting from a non-existant table) rather than do nothing which I'm guessing was the point since otherwise you could do the drop without the function. You might want to consider querying the system catalogs instead. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] postgreSQL 7.3.3 crashing on server with Itanium
Always check for bad memory when you see sig 11 first. There are bugs that can throw a sig11, but if you see them at random places, with random queries, it's usually bad memory. On Tue, 17 Jun 2003, Taranjit Singh Lamba wrote: > Hi All , > > H/W Configuration - Dell 7150 Quad Itanium 1 ( 733 MHz) with 4G RAM > > OS - Red Hat 7.2 > - Postgresql ver 7.3.3 compiled from raw hide red hat rpms. > > > Problem : We were able to install postgreSQL 7.3.3 successfully on this enviornment > , but server crashes randomly and throws following error : > * > LOG: server process (pid 11220) was terminated by signal 11 > LOG: terminating any other active server processes > LOG: all server processes terminated; reinitializing shared memory and > semaphores > LOG: database system was interrupted at 2003-06-17 10:29:08 PDT > LOG: checkpoint record is at 0/83CEC8 > LOG: redo record is at 0/83CEC8; undo record is at 0/0; shutdown TRUE > LOG: next transaction id: 480; next oid: 16976 > LOG: database system was not properly shut down; automatic recovery in progress > LOG: ReadRecord: record with zero length at 0/83CF10 > LOG: redo is not required > LOG: database system is ready > FATAL: Database "template0" is not currently accepting connections > FATAL: IDENT authentication failed for user "zippool1" > LOG: fast shutdown request > LOG: shutting down > LOG: database system is shut down > *** > > > Any help would be highly appreciated ! > > > Thanks in Advance ! > > Regards > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] postgreSQL 7.3.3 crashing on server with Itanium processor...
My guess is that we don't have spinlock code for the CPU yet. I think it may be in 7.4. --- Taranjit Singh Lamba wrote: > Hi All , > > H/W Configuration - Dell 7150 Quad Itanium 1 ( 733 MHz) with 4G RAM > > OS - Red Hat 7.2 > - Postgresql ver 7.3.3 compiled from raw hide red hat rpms. > > > Problem : We were able to install postgreSQL 7.3.3 successfully on this enviornment > , but server crashes randomly and throws following error : > * > LOG: server process (pid 11220) was terminated by signal 11 > LOG: terminating any other active server processes > LOG: all server processes terminated; reinitializing shared memory and > semaphores > LOG: database system was interrupted at 2003-06-17 10:29:08 PDT > LOG: checkpoint record is at 0/83CEC8 > LOG: redo record is at 0/83CEC8; undo record is at 0/0; shutdown TRUE > LOG: next transaction id: 480; next oid: 16976 > LOG: database system was not properly shut down; automatic recovery in progress > LOG: ReadRecord: record with zero length at 0/83CF10 > LOG: redo is not required > LOG: database system is ready > FATAL: Database "template0" is not currently accepting connections > FATAL: IDENT authentication failed for user "zippool1" > LOG: fast shutdown request > LOG: shutting down > LOG: database system is shut down > *** > > > Any help would be highly appreciated ! > > > Thanks in Advance ! > > Regards > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] PostgreSQL calibration
Are there really any performance settings of much interest beyond the shared and non-shared memory settings? Beyond those the interactions get so complex that automation is probably impossible anyway, and certain options like fsync = false should never be 'recommended'. On the other hand, a way of empirically deriving some 'correct' optimizer parameters for a given machine would be very nice :-) Matt On Wed, 2003-06-18 at 18:07, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Sure, it would be great if we could do it. > > If the program actually derives reliable numbers, it would be great. > It could easily do more harm than good if it gives bogus results. > I think it will be very hard to get reliable rather than bogus results > :-( ... but feel free to try. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Perl DBI question
On Tue, Jun 17, 2003 at 16:51:33 -0500,
"Kovalcik, Mike A [ITS]" <[EMAIL PROTECTED]> wrote:
>
> $sth = $dbh->do("INSERT INTO transactions
> (date,description,amount,confirmation,nameid,typeid) VALUES
> ('$datePaid','$description','$amount','$confirmation',$nameid,$typeid)")
> ;
Most likely you want single quotes around $nameid and $typeid.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [ADMIN] Perl DBI question
I'm trying to setup some scripts that will allow me to use Perl DBI to
INSERT into my table. However, I have not had any success at all. I'm
using perl CGI as well so I've granted ALL permissions on my table to
the apache user and I still can't INSERT. I can, however, UPDATE and
SELECT on the table, just not INSERT.
Here is a piece of my code:
#--Establish the DB connection
#--Assign the DB name
$dbName = 'checkbook';
#--Connect to the Pg DB using DBI
my $dbh = DBI->connect("dbi:Pg:dbname=$dbName");
$sth = $dbh->do("INSERT INTO transactions
(date,description,amount,confirmation,nameid,typeid) VALUES
('$datePaid','$description','$amount','$confirmation',$nameid,$typeid)"
)
;
What kind of error message are you getting?
With just a quick glance, I would say check your quoting. i.e., is
$amount supposed to be quoted?
You can do parameter binding on $dbh->do statements also, like this:
$dbh->do("INSERT INTO transactions
(date,description,amount,confirmation,nameid,typeid) VALUES
(?,?,?,?,?,?)", undef, ($datePaid, $description, $amount,
$confirmation, $nameid, $typeid));
That takes care of all your quoting so you don't have to worry about it.
Also, you probably want to post this to the INTERFACES list.
--Jeremy
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [ADMIN] PostgreSQL calibration
It would be nice to have something that could suggest settings for the different *cost* options. On 18 Jun 2003, matt wrote: > Are there really any performance settings of much interest beyond the > shared and non-shared memory settings? Beyond those the interactions > get so complex that automation is probably impossible anyway, and > certain options like fsync = false should never be 'recommended'. > > On the other hand, a way of empirically deriving some 'correct' > optimizer parameters for a given machine would be very nice :-) > > > Matt > > > On Wed, 2003-06-18 at 18:07, Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > Sure, it would be great if we could do it. > > > > If the program actually derives reliable numbers, it would be great. > > It could easily do more harm than good if it gives bogus results. > > I think it will be very hard to get reliable rather than bogus results > > :-( ... but feel free to try. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Perl DBI question
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
You should not be using the "do" method, but the prepare/execute model
instead. Using placeholders can not only be more efficient but allows
quoting to happen properly. Here is a rewrite:
#--Establish the DB connection
#--Assign the DB name
my $dbName = 'checkbook';
my $dbuser = "joe";
my $dbpass = "sixpak";
my $dbh = DBI->connect("dbi:Pg:dbname=$dbName", $dbuser, $dbpass, {AutoCommit=>1,
RaiseError=>1})
or die "Could not connect to the database: $DBI::errstr\n";
my $SQL = "
INSERT INTO transactions (date,description,amount,confirmation,nameid,typeid)
VALUES (? ,? ,? ,? ,? ,? )";
my $sth = $dbh->prepare($SQL);
my $count = $sth->execute($datePaid,$description,$amount,
$confirmation,$nameid,$typeid);
print "Insert count: $count\n";
It is hard to tell why your inserts are going wrong without more information, but
the RaiseError should at least help catch some obvious errors.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200306181337
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+8KUHvJuQZxSWSsgRAhWoAJ9/aw9AaCMa5vGvEpvujEEBm4iBXQCggSSw
jPaGcdMt9Qq9XeJqttvdX48=
=x1nu
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] configure readline error
That was it. ncurses-devel, termcap-devel, readline-devel fixed the problem. Thanx a bunch for all those that helped. johnmac Kuhn, Dylan K (4520500D) wrote: John, The trick on my RedHat 7.3 system was to install the termcap-devel, readline-devel, and zlib-devel RPMs from the installation CDs. -dylan- -Original Message- From: John McGloughlin [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 21:03 To: [EMAIL PROTECTED] Subject: [ADMIN] configure readline error Hello, I'm having trouble with ./configure for Postgres 7.3.3 on a RH7.3 i686 system. checking for readline... no readline library not found if you have readline already installed . I've gone through config.log and it looks like it searches the correct locations. I have gnu readline 4.1 installed and can find libs in /usr/local/lib/libreadline.a and headers in /usr/local/lib/include/readline/*.h. I configured with ./configure --with-includes=/usr/local/lib:/usr/local/lib/include/readline --with-libraries=/usr/local/lib:/usr/local/lib/include/readline --with-perl --with-openssl --with-syslog Unfortunately I keep getting the error. I've searched numerous lists, groups, faqs, etc. but cannot seem to resolve the issue. Any help would be greatly appreciated. Thanx in advance! johnmac ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] PostgreSQL calibration
Precisely. Any hints from the real gurus out there as to how that might be accomplished (or alternatively, reasons why it's hopeless)? On Wed, 2003-06-18 at 18:42, scott.marlowe wrote: > It would be nice to have something that could suggest settings for the > different *cost* options. > > On 18 Jun 2003, matt wrote: > > > Are there really any performance settings of much interest beyond the > > shared and non-shared memory settings? Beyond those the interactions > > get so complex that automation is probably impossible anyway, and > > certain options like fsync = false should never be 'recommended'. > > > > On the other hand, a way of empirically deriving some 'correct' > > optimizer parameters for a given machine would be very nice :-) > > > > > > Matt > > > > > > On Wed, 2003-06-18 at 18:07, Tom Lane wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > > Sure, it would be great if we could do it. > > > > > > If the program actually derives reliable numbers, it would be great. > > > It could easily do more harm than good if it gives bogus results. > > > I think it will be very hard to get reliable rather than bogus results > > > :-( ... but feel free to try. > > > > > > regards, tom lane > > > > > > ---(end of broadcast)--- > > > TIP 6: Have you searched our list archives? > > > > > >http://archives.postgresql.org > > > > > > > > > ---(end of broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Perl DBI question
setting RaiseError to true is the way to go in DBI.
it catches most of the error and prints in apache error log.
if RaiseError = 1 one does not have to do the explicit
die after the connect even i think
regds
mallah,
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
>
>
> You should not be using the "do" method, but the prepare/execute model instead.
> Using
> placeholders can not only be more efficient but allows quoting to happen properly.
> Here is a
> rewrite:
>
> #--Establish the DB connection
> #--Assign the DB name
> my $dbName = 'checkbook';
> my $dbuser = "joe";
> my $dbpass = "sixpak";
>
> my $dbh = DBI->connect("dbi:Pg:dbname=$dbName", $dbuser, $dbpass, {AutoCommit=>1,
> RaiseError=>1})
> or die "Could not connect to the database: $DBI::errstr\n";
>
> my $SQL = "
> INSERT INTO transactions (date,description,amount,confirmation,nameid,typeid) VALUES
> (? ,? ,? ,? ,? ,? )";
>
> my $sth = $dbh->prepare($SQL);
>
> my $count = $sth->execute($datePaid,$description,$amount,
> $confirmation,$nameid,$typeid);
>
> print "Insert count: $count\n";
>
>
> It is hard to tell why your inserts are going wrong without more information, but
> the
> RaiseError should at least help catch some obvious errors.
>
>
> - --
> Greg Sabino Mullane [EMAIL PROTECTED]
> PGP Key: 0x14964AC8 200306181337
>
> -BEGIN PGP SIGNATURE-
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+8KUHvJuQZxSWSsgRAhWoAJ9/aw9AaCMa5vGvEpvujEEBm4iBXQCggSSw
> jPaGcdMt9Qq9XeJqttvdX48=
> =x1nu
> -END PGP SIGNATURE-
>
>
>
> ---(end of broadcast)--- TIP 7:
> don't forget to
> increase your free space map settings
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [ADMIN] postgreSQL 7.3.3 crashing on server with Itanium processor...
"Taranjit Singh Lamba" <[EMAIL PROTECTED]> writes: > H/W Configuration - Dell 7150 Quad Itanium 1 ( 733 MHz) with 4G RAM > OS - Red Hat 7.2 > - Postgresql ver 7.3.3 compiled from raw hide red hat rpms. [raises eyebrow] Does RHL 7.2 claim to support Itanium? Especially SMP Itanium? That release was awhile back, I believe, and I'd not be surprised at kernel bugs that show up with such hardware. If you've got a fully up-to-date kernel and have eliminated the theory of memory problems, please send us debugger stack traces from a few of the crashes. (The traces will be more useful if you first rebuild with debugging symbols enabled, which I think is not the default with the RPMs.) 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])
Re: [ADMIN] postgreSQL 7.3.3 crashing on server with Itanium processor...
Bruce Momjian <[EMAIL PROTECTED]> writes: > My guess is that we don't have spinlock code for the CPU yet. Out-of-the-box it would probably build with SysV semas instead of spinlocks, but that's not going to cause crashes, only less-than-stellar performance. regards, tom lane ---(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
[ADMIN] Fwd PostgreSQL and OpenOffice
From: Joerg Budischewski <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Hi, I'd like to announce the availability of a native postgresql driver version 0.5.0 for OpenOffice.org database API. The driver's homepage can be found here: http://dba.openoffice.org/drivers/postgresql/index.html The postgresql SDBC Driver allows to use the postgresql database from OpenOffice.org without any other wrapper layer such as odbc or jdbc. The driver is in a alpha state, the main purpose of this version is to collect input from the community about the most needed missing features. Treat this driver with care, it is not thouroughly tested yet and might (in the worst case) destroy your data. If you just want a driver, that just works, you should wait some more time. On the other hand, you have the chance to have some influence on the current development, so that a final driver may support the features you need. The driver is aimed only at the 1.1 OpenOffice.org versions, it may be used without GUI support also in the 1.0.x version (though this is not tested yet). I The final aim is to have an easier to use, faster, more feature rich database driver than the jdbc-odbc solution. The current version does not meet all these aims yet. Please have a look at the open questions paragraph, if you are interested in how you can help. Have fun, Joerg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] notifications:Urgent help needed!!
Just before I received this mail, I got spam from something 'Anagha Joshi', I think all PostgreSQL ADMIN users got this mail, so probably have you... please look into it (forgot to check all e-mail headers, to look for forged from-fields and such, alergic as I am to something that looks spammy :P thank you! Michiel At 12:16 18-6-2003 +0530, Anagha Joshi wrote: Hi All, I m new to Postgresql and using version 7.2.4. I want to get notification from backend when a specific error message (like low space) comes to the front end. How should I specify Notify and Listen condition? Thx. In advance. Anagha
[ADMIN] huge table / bitmap indexes
Hello All is in postgres any possibility to create bitmap index ? I have big (~12 000 000 records) table where one field have limited count of values. Many queries use this field in where section. I thing that bitmap index will by better than BTree. How i can optimize select on this table ? Thanks - Cure -- The poor player plays the opponent's game for him. cure mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] PostgreSQL calibration
On June 18, 2003 12:54 pm, Chris Gamache wrote: > What's your thought on creating some type of calibration applet that > will divine proper settings for the PostgreSQL performance related Have you seen pg_autotune? It's aging... but might be something to play with. http://gborg.postgresql.org/project/pgautotune/projdisplay.php -- Tim Middleton | Cain Gang Ltd | But the trouble was that my hysterical fit [EMAIL PROTECTED] | www.Vex.Net | could not go on for ever. --Dost (NFTU) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Latest transcation
Title: Latest transcation Hi All, Is there any way to know programatically which is the latest insert/update occured to a particular table? What are the values which are inserted/updated to that table? Help appreciated. Thx., Anagha
