Re: [GENERAL] drop/rename table and transactions
Is it possible to achieve your goals by using things like "delete * from table1 where id!=stuffIwant" instead of dropping it? Yes, I think I better use delete statements instead of drop statements knowing PostgreSQL can't always handle drop/rename statements in transactions correctly. Jaco de Groot
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Mike Mascari wrote: From an otherwise EXTREMELY happy user :-) (full smile...), I see 3 scenarios: (1) Disallow DDL statements in transactions (2) Send NOTICE's asking for the user to not trigger the bug until the bugs can be fixed -or- (3) Have all DDL statements implicity commit any running transactions. I think 1 is the best solution as long as there are bugs concerning DDL statements in transactions. It will prevent people from getting in trouble. I've been in this trouble for months :-(. I'm using Java Servlets to connect to PostgreSQL and I'm having DDL statements whitin transactions wich sometimes cause an error. This error is hard to find and solve if you don't know PostgreSQL has problems with DDL statements in transactions. And if the error occures it doesn't simply crash 1 transaction or connection but it crashes all connections wich prevents my website from running correctly until I've manualy fixed the problem (mostly restarting PostgreSQL). To prevent others from getting in the same trouble I'd like to propose that the next release of PosgreSQL will dissalow DDL statements in transactions and notice the user this is a feature wich is currently in development. Jaco de Groot
[GENERAL] Quoting/Escaping
I'd like to be able to find a book title that contain C++ in the title select * from books where title ~* 'C++' doesn't work. I've tried all the basic methods of quoting the Plus (+) signs but none seem to work... Any hints would be most appreciated Thanks... Bill Sneed, Prospect, Maine
[GENERAL] BSDI shared mem
Hi, Perhaps this is useful for the FAQ as it was very difficult for me to discover... To increase shared mem and semaphores on BSDI (courtesy of Sin'ichiro Miyatani on bsdi-users list) In /usr/src/sys/sys/sem.h, #ifndef SEMMNI #define SEMMNI 10 /* # of semaphore identifiers */ #endif #ifndef SEMMNS #define SEMMNS 60 /* # of semaphores in system */ #endif So 10 and 60 are default values. To increase # of semaphore, add next line into kernel configuration file and recompile your kernel. options "SEMMNI=XX" options "SEMMNS=YY" XX and YY are number you need. And in /usr/src/sys/sys/shm.h: #define SHMMAXPGS 1024/* max hardware pages for shared memory */ It seems this value is hard coded and there is no kernel configuration option. John Henderson
Re: [GENERAL] Mail to DB.
On Sat, 27 Nov 1999, Jason C. Leach wrote: hi, I've read that a few of you are putting email into a postgres DB. I'd be interested in doing something similar. Would any of you care to share the secret that allows sendmail to deposit the email to a db, or how you get it from /var/spool/mail/mailbox into the table when new mail arrives? a friend and i were just talking about this... you could use procmail to spawn a program that inserts the data into pgsql. youd most likely want to use LO's for the email body, storing To:, From:, Cc:, and Subject: in a table ( for queries ). --- Howie [EMAIL PROTECTED] URL: http://www.toodarkpark.org "Tell a man that there are 400 billion stars and he'll believe you. Tell him a bench has wet paint and he has to touch it."
[GENERAL] memory
Hi,
I could really use some help understanding where exactly the limits are in
my use of memory and how postgres uses memory.
I am running PostgreSQL 6.4 on BSDI 3.0 with 64M ram and 262M virtmem.
table sessions is 74M and 371K records
isfiji= explain select user_name from sessions;
NOTICE: QUERY PLAN:
Seq Scan on sessions (cost=21330.73 size=371719 width=12)
EXPLAIN
This query (above) works without a hitch from psql
isfiji= explain select * from sessions;
NOTICE: QUERY PLAN:
Seq Scan on sessions (cost=21330.73 size=371719 width=138)
EXPLAIN
The query above can access over 250M of memory according to top but dies
with either a seg fault or the latest, something called
"calloc: Cannot allocate memory"
I have had to set datasize to 256M which seems to be unrealistic. This table
is only 74M, it has a 15M index which the explain does not think is used.
Even if the entire table has to be sucked into mem and then duplicated
elsewhere in mem as a result this only accounts for 148M. Besides, is it
reaonable to require more than 256M of ram to copy a table from disk to
psql?
Having built this mass of data I don't seem to be able to do any useful
queries with it. Actually, just having problems with the query I want which
is
select user_name,sess_time,start, stop
from sessions
where date_part('epoch',start) between '$t1' and '$t2';
By the way, the following query from a PHP script works great, and because
it uses an index, very fast.
select date_trunc('minutes',sum(sess_time)) from
sessions where
user_name='$FORM{username}' and date_part('epoch',start)'$t1' and
date_part('epoch',start)'$t2'
The latter query is asking for 1 out of 3000 summaries approx.
Here are the questions...
1) Can someone explain how postgreSQL uses memory so that I can understand
what I should be doing here.
BTW, I am running postgres with -B 884. Can someone also explain how
postgres uses shared mem so that I can have a clue what would be a
reasonable setting.
2) Can any BSDI folk give me any tuning tips. I am especially interested to
hear from those who claim "some might tell you that we run equally well on
FreeBSD" or "BSD is the One True Code", of course all help is gratefully
received.
Thanks,
John Henderson
RE: [GENERAL] Mail to DB.
I understand Oracle 8i does something like this (also acting as an FTP backend). You might want to scower their site for good ideas Darvin Zuch -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Howie Sent: Sunday, November 28, 1999 3:39 PM To: Jason C. Leach Cc: pgsql-list Subject: Re: [GENERAL] Mail to DB. On Sat, 27 Nov 1999, Jason C. Leach wrote: hi, I've read that a few of you are putting email into a postgres DB. I'd be interested in doing something similar. Would any of you care to share the secret that allows sendmail to deposit the email to a db, or how you get it from /var/spool/mail/mailbox into the table when new mail arrives? a friend and i were just talking about this... you could use procmail to spawn a program that inserts the data into pgsql. youd most likely want to use LO's for the email body, storing To:, From:, Cc:, and Subject: in a table ( for queries ). --- Howie [EMAIL PROTECTED] URL: http://www.toodarkpark.org "Tell a man that there are 400 billion stars and he'll believe you. Tell him a bench has wet paint and he has to touch it."
Re: [GENERAL] Quoting/Escaping
Bill Sneed wrote:
I'd like to be able to find a book title that contain C++ in the
title
select * from books where title ~* 'C++' doesn't work.
I've tried all the basic methods of quoting the Plus (+) signs but
none seem to work...
Any hints would be most appreciated
Thanks...
Bill Sneed, Prospect, Maine
I think the 'like' operator may do what you seek.
create table t (w varchar);
insert into t (w) values ('C++');
insert into t (w) values ('C');
select * from t where w like 'C++';
w
---
C++
(1 row)
Cheers.
Ed
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
if PostgreSQL could successfully rollback DDL statements sanely (and thus diverge from ORACLE). I guess I don't expect that to happen successfully until something the equivalent of TABLESPACES is implemented and there is a disassociation between table names, index names and their filesystem counterparts and to be able to "undo" filesystem operations. That, it seems to me, will be a major undertaking and not going to happen any time soon... Ingres has table names that don't match on-disk file names, and it is a pain to administer because you can't figure out what is going on at the file system level. Table files have names like AAAHFGE. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote: if PostgreSQL could successfully rollback DDL statements sanely (and thus diverge from ORACLE). I guess I don't expect that to happen successfully until something the equivalent of TABLESPACES is implemented and there is a disassociation between table names, index names and their filesystem counterparts and to be able to "undo" filesystem operations. That, it seems to me, will be a major undertaking and not going to happen any time soon... Ingres has table names that don't match on-disk file names, and it is a pain to administer because you can't figure out what is going on at the file system level. Table files have names like AAAHFGE. I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Vadim
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Vadim Mikheev wrote: Bruce Momjian wrote: if PostgreSQL could successfully rollback DDL statements sanely (and thus diverge from ORACLE). I guess I don't expect that to happen successfully until something the equivalent of TABLESPACES is implemented and there is a disassociation between table names, index names and their filesystem counterparts and to be able to "undo" filesystem operations. That, it seems to me, will be a major undertaking and not going to happen any time soon... Ingres has table names that don't match on-disk file names, and it is a pain to administer because you can't figure out what is going on at the file system level. Table files have names like AAAHFGE. I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Vadim Will that aid in fixing a problem such as this: session 1: CREATE TABLE example1(value int4); BEGIN; session 2: BEGIN; ALTER TABLE example1 RENAME TO example2; session 1: INSERT INTO example1 VALUES (1); END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind session 2: END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind Just curious, Mike (implicit commit) Mascari
Re: [GENERAL] memory
At 05:11 PM 11/29/99 +1200, John Henderson wrote: snippage Here are the questions... 1) Can someone explain how postgreSQL uses memory so that I can understand what I should be doing here. BTW, I am running postgres with -B 884. Can someone also explain how postgres uses shared mem so that I can have a clue what would be a reasonable setting. 2) Can any BSDI folk give me any tuning tips. I am especially interested to hear from those who claim "some might tell you that we run equally well on FreeBSD" or "BSD is the One True Code", of course all help is gratefully received. Thanks, John Henderson The FreeBSD port of postgres stipulates that you must compile kernel with options SYSVSHM, SYSVSEM, SYSVMSG. Don't know jack about BSDI or your kernel config, but this might be something worth investigating. Ciao--Ken http://www.y2know.org/safari/ FreeBSD- Viagra for your server ;^)
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Using oid's instead of names may give us some ability to fix some other bugs, though. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Mike Mascari wrote: Will that aid in fixing a problem such as this: session 1: CREATE TABLE example1(value int4); BEGIN; session 2: BEGIN; ALTER TABLE example1 RENAME TO example2; session 1: INSERT INTO example1 VALUES (1); END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind session 2: END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind Seems that oid file names will fix this... Currently, each shared buffer description structure has database/table names for the purposes of "blind" writes (when backend cache hasn't entry for relation and so bufmgr can't use cache to get names from oids). ALTER TABLE ... RENAME renames relation file(s) but doesn't change relation name inside shbuffers... Mike (implicit commit) Mascari -:))) Vadim
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote: I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Why it's so painful? We can write utility to construct database dir with table names symlinked to real table files -:) Actually, I don't understand for what would you need to know what is what, (c) -:) Using oid's instead of names may give us some ability to fix some other bugs, though. Yes. Vadim
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote: I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Why it's so painful? We can write utility to construct database dir with table names symlinked to real table files -:) Actually, I don't understand for what would you need to know what is what, (c) -:) With Ingres, you can't just look at a file and know the table name, and if you need to reload just one file from a tape, it is a royal pain to know which file to bring back. I have said Ingres make things 100 times harder for adminstrators by doing this. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
