RE: [SQL] psql problem
Does anyone know why when I am in a particular DB as user postgres and use the following statement, why I get this error?" This is the statement; SELECT * FROM some_file where ID = 1; [Volker Paul] If your ID field is really uppercase, try: SELECT * FROM some_file where "ID" = 1; Cheers, Volker
[SQL] SPEED UP.
I would like to know is there is a specific way to speed up my query to postgres. I have a DB-MACHINE that I use just and only for postgres witch runs linux RedHad 6.2 with 1Gb of RAM on ULTRA SCSI discs. I run vacuum every hour, but there is something else I can do (compile postgres in a special way ) to get the best from postgres ?? Thanks in advance Alex
Antw: [SQL] Insert with replace?
Wampler, Steve wrote: > ... > I've got a database that (logically) represents a set of > items, where each item has a primary key represented by two > columns (id and name). If that key isn't in the database, > I want to insert the item. If that key is in the database, I want > to update the item. I have no need to retain the previous > values. > You can solve your problem with a trigger and a view, that has the same attributes as your table. You insert the data into the view, not the table. The trigger first looks, if a row with that primary key is allready in the table and decides, if to use update or insert into the table. example: create table test_table ( id int4 primary key, data text); create view test_view as select id, data from test_table; create function insert_or_update() returns opaque as ' declare lid int4; begin select t.id into lid from test_table t where t.id = new.id; if found then update test_table set data = new.data where id = new.id; else insert into test_table values (new.id, new.data); end if; return null; end; ' language 'plpgsql'; create trigger insert_or_update_trigger before insert on test_view for each row execute procedure insert_or_update(); Of course this solution has some drawbacks. PostgreSQL always returns INSERT 0 0, because the insert into the view is canceled by returning null in the trigger. But it works. Test it with two inserts, that do insert insert into test_view values (1,'one'); insert into test_view values (2,'two'); select * from test_view; Now an insert that dose an update: insert into test_view values (1,'ONE'); select * from test_view; Gerhard
[SQL] pg_recvbuf : unexpected EOF on client
I have an application in Java I use to insert records into postgreSQL base. Java shows no errors, butrecords can't write into base.In pgsqrever.log I found entry :pg_recvbuf : unexpected EOF on client connection. Thanks for any help. Adam
Re: [SQL] SPEED UP.
Lets see your queries you're running and their plan, I'd bet there are ways to speed them up (that's always been the case with mine!).. fields - Mitch "The only real failure is quitting." - Original Message - From: Alessandro Rossi <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 02, 2000 2:49 AM Subject: [SQL] SPEED UP. > > > I would like to know is there is a specific way to speed up my query to > postgres. > > I have a DB-MACHINE that I use just and only for postgres witch runs > linux RedHad 6.2 with 1Gb of RAM on ULTRA SCSI discs. > > I run vacuum every hour, but there is something else I can do (compile > postgres in a special way ) to get the best from postgres ?? > > > Thanks in advance > > Alex > > >
Re: [SQL] SPEED UP.
> > > I would like to know is there is a specific way to speed up my query to > postgres. Please post the exact command line arguments you are giving when you start the postmaster daemon process. Cordially Patrick Giagnocavo [EMAIL PROTECTED]
[SQL] Deferral of primary key constraint
Is it possible to defer the check on a primary key constraint (or a check
constraint, for that matter). Here is an example that shows why it
would be nice to be able to do so. We have a real-life scenario that
is similar (but much more complex).
drop table btab;
create table btab (
base
varchar,
pos
int4,
cmt
varchar,
primary key (base, pos)
);
insert into btab (base,pos,cmt) values ('aa',1,'The');
insert into btab (base,pos,cmt) values ('aa',2,'quick');
insert into btab (base,pos,cmt) values ('aa',3,'grey');
insert into btab (base,pos,cmt) values ('aa',4,'fox');
insert into btab (base,pos,cmt) values ('bb',3,'dog');
insert into btab (base,pos,cmt) values ('bb',2,'brown');
insert into btab (base,pos,cmt) values ('bb',1,'The');
select * from btab order by base,pos;
begin;
delete from btab where base = 'aa' and pos = 2;
update btab set pos = pos - 1 where pos > 2 and base = 'aa';
commit;
select * from btab order by base,pos;
begin;
update btab set pos = pos + 1 where pos >= 2 and base = 'bb';
insert into btab (base,pos,cmt) values ('bb',2,'slow');
commit;
select * from btab order by base,pos;
The last transaction fails (on my box, anyway) because of the primary
key index.
We would like to be able to do inserts/deletes to a list of records
and still ensure that they are in contiguous sequential order so we want
to renumber higher records if a new record is inserted in the middle.
The sequence is part of the primary key and we want to ensure uniqueness.
Some renumbering will work (by chance) if the records happen to get adjusted
in the right order. But if one of the updates tries to rename to
an already existing record, it fails.
How hard is it to take the deferral mechanism you have for foreign key
references and apply it to the primary key too? It would also be
handy to be able to defer a check constraint.
begin:vcard
n:Bateman;Kyle
tel;fax:801-377-8096
tel;work:801-377-8033x101
x-mozilla-html:FALSE
url:www.actiontarget.com
org:Action Target Inc
adr:;;PO Box 636;Provo;UT;84603;US
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;-15520
fn:Kyle Bateman
end:vcard
[SQL] Benchmark
Is there anywhere I can find some benchmark test for postgres. The reason is that I want to use it for a site that could have several simultanous users querying the database and I want to be sure that it is stable (and fast) enough to handle that kind of (ab)use. Also which is fastest ? 1. A query (using index of course) for a text in the database. or 2. Reading from a file on the harddisk. If the answer is 1 will the answer still be 1 if I do it several times (100.000+ a day) Regards Nikolaj
[SQL] Re: Deferral of primary key constraint
Kyle Bateman wrote: > Is it possible to defer the check on a primary key constraint (or a > check constraint, for that matter). Here is an example that shows why > it would be nice to be able to do so. We have a real-life scenario that > is similar (but much more complex). Not yet. Only the trigger based foreign key checks are deferrable in 7.0. We discovered the need for deferrability of UNIQUE (what is what you want from the PK things) and other checks. Not sure up to now how we could do it, but a known problem. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Benchmark
Nikolaj Lundsgaard wrote: > Is there anywhere I can find some benchmark test for postgres. The reason is that I >want to use it for a site that could have several simultanous users querying the >database and I want to be sure that it is stable (and fast) enough to handle that >kind of (ab)use. There is actually no (DB-independant) benchmark suite publicly available. > > Also which is fastest ? > > 1. A query (using index of course) for a text in the database. > > or > > 2. Reading from a file on the harddisk. > > > If the answer is 1 will the answer still be 1 if I do it several times (100.000+ a >day) Since any DB access boils down somehow to file access, it could never be 1 (as long as your solution doesn't need to sequentially read big files to find small pieces of information). The question you have to ask is, what would happen in a concurrent environment? If there's at least one writing process, must the entire information be blocked from reading? Postgres uses MVCC (Multi Version Concurrency Control) for it. So no reader will ever be blocked by a writer - it simply sees a snapshot of the entire data as it was when he executed the first DML statement. If you have total static data, that never changes during uptime of your application, use flat files or some higher level file interface like GDB or MySQL. If you have data in motion, use a database. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] SQL'92 web resources
Bruce Momjian writes: > Shouldn't we have links to these on our web site? SQL92 is yesterday's news. Find your SQL99 documents at: ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ (Though I'm unsure about the legality of these.) -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [SQL] SQL'92 web resources
> Bruce Momjian writes: > > > Shouldn't we have links to these on our web site? > > SQL92 is yesterday's news. Find your SQL99 documents at: > > ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ Yes, all I am saying is that we need to get these onto our web page. -- 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
