Multi-threading on PostgreSQL?

I think you are missing something. Threads and processes, to a great
extent are 6 of one and half a dozen of the other. Postgres uses
multiple processes accessing a shared memory area. A multithreaded
database would use multiple threads accessing a shared memory area. In
most OSes, threads and processes are treated very much the same way.
Threads and processes don't necessarily have to "wait". Both threads and
processes use locks. A good multi-process implementation will beat a bad
threaded one every time.

A threaded implementation can be more efficient, specifically when
you've got hundreds of simultaneous connections, but we're not talking
about some giant leap here.

That's not to say Sybase isn't faster than PostgreSQL (or slower for
that matter. I wouldn't know). But I'd advise against putting any stock
into whether it's threaded or not. As always, do your own testing.

Tiago Hermans wrote:

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Hi,

I am really impressed with the features of PostgreSQL but am wondering if we
should use this product for our project. We have tried several databases
(MySQL, Sybase ASE, Sybase ASA) and we are now looking at PostgreSQL before
releasing our service on the Internet.

My concern comes from the fact that there's no multi-threading support in
PostgreSQL and since we expect many customers to be connected at the same
time on multiple connections, I am afraid the time to perform the SQL
statements will be much longer, since they'll all be queued one after the
other. Therefore, if one query takes a long time, all the other queries have
to wait behind before being executed.

Am I missing something or are my assumptions correct?
Also, does PostgreSQL supports international characters?

We certainly process multiple queries at the same time. We are not
multi-threaded in the sense we don't run multiple queries inside the
same process. Each connection gets its own process.

I am interested to hear why you thought we only do one query at a time.
Is there some information we published or someone else published that
lead you to that conclusion.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

My concern comes from the fact that there's no multi-threading

support in

PostgreSQL and since we expect many customers to be connected

at the same

time on multiple connections, I am afraid the time to perform the SQL
statements will be much longer, since they'll all be queued one

after the

other. Therefore, if one query takes a long time, all the other

queries have

to wait behind before being executed.

Am I missing something or are my assumptions correct?
Also, does PostgreSQL supports international characters?

We certainly process multiple queries at the same time. We are not
multi-threaded in the sense we don't run multiple queries inside the
same process. Each connection gets its own process.

I am interested to hear why you thought we only do one query at a time.
Is there some information we published or someone else published that
lead you to that conclusion.

In the URL: http://www.postgresql.org/doxlist.html
the "Multi-threaded" and "Parallel Query" bullets are not active. I guess
these words should be explained with a hyperlink to avoid confusion. You
have such a great product, if I didn't register on the mailing list and
posted this message, I would have been totally wrong in my assumptions, and
looking at the history of the list, I am not the only one.

Anyway, we are downloading PostgreSQL today and will port our SQL code to
try it out.

Thank you for your reply and clarification on this.

Tiago

How can I implement foreign Keys with postgres? thanks.

----------------
Sime� Reig
simeo@tinet.org
Barcelona (SPAIN)
-----------------

On Thu, 15 Jul 1999, [iso-8859-1] Sime��� wrote:

How can I implement foreign Keys with postgres? thanks.

pgsql/contrib/spi has refint.c. you'd want to compile that and execute
refint.sql. it also comes with documentation.

refit is, for now, the way postgres handles foreign keys. two functions
are involved, check_foreign_key() and check_primary_key().

---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."

El dia 16-Jul-99 en Howie va escriure :

refit is, for now, the way postgres handles foreign keys. two functions
are involved, check_foreign_key() and check_primary_key().

Hello Howie, thanks for your help.I have a problem I don't Have any refi*.c.
Where I can find it ?. Thanks again

.~. ----------------
< Sime� Reig
// \\ simeo@tinet.org
/( 0 )\ Tarragona (SPAIN)
^ ^ -----------------

Simeo,

Rules, Triggers + functions. A function is supplied - refint? in the
contrib directory or you can write your own as below:

CREATE TABLE Companies (
CompanyId int4,
Text1 varchar(50),
PRIMARY KEY (CompanyId)
);
CREATE TABLE Projects (
ProjectId int4,
DeveloperCompanyId int4,
SponsorCompanyId int4,
Text1 varchar(50),
Text2 varchar(50),
PRIMARY KEY (ProjectId),
FOREIGN KEY (DeveloperCompanyId)
REFERENCES Companies(CompanyId),
FOREIGN KEY (SponsorCompanyId)
REFERENCES Companies(CompanyId)
);

----------------------------------------------------------------------------
----
-- File: create_functions.sql
-- Description: Create functions on the database.
----------------------------------------------------------------------------
----
drop function fn_CompaniesProjects_XUXM1();

create function fn_CompaniesProjects_XUXM1() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_Flag integer;
BEGIN
l_Flag := 0;
-- Check to see if any participating fields have been modified
if new.CompanyId != old.CompanyId then
l_Flag := 1;
end if;
-- If participating fields have been updated then check to see if
-- any slave records exist
if l_Flag > 0 then
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text text;
BEGIN
SELECT * FROM Projects INTO l_s
WHERE
SponsorCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
return NULL;
else
return new;
end if;
END;

end if;
return new;
END;'
language 'plpgsql';

drop function fn_CompaniesProjects_XXDM1();

create function fn_CompaniesProjects_XXDM1() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER A DELETE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text TEXT;
BEGIN
SELECT * FROM Projects INTO l_s WHERE
SponsorCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return old;
END;'

language 'plpgsql';

drop function fn_CompaniesProjects_XXXS1();

create function fn_CompaniesProjects_XXXS1() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Projects
(Slave)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_m Companies%ROWTYPE;
l_err_text text;
l_Flag integer;
BEGIN
l_Flag := 0;
if new.SponsorCompanyId IS NOT NULL then
l_Flag := 1;
end if;
if l_Flag > 0 then
SELECT * FROM Companies INTO l_m
WHERE
CompanyId = new.SponsorCompanyId
;
if not found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return NULL;
else
return new;
end if;
END;'
language 'plpgsql';

drop function fn_CompaniesProjects_XUXM2();

create function fn_CompaniesProjects_XUXM2() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_Flag integer;
BEGIN
l_Flag := 0;
-- Check to see if any participating fields have been modified
if new.CompanyId != old.CompanyId then
l_Flag := 1;
end if;
-- If participating fields have been updated then check to see if
-- any slave records exist
if l_Flag > 0 then
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text text;
BEGIN
SELECT * FROM Projects INTO l_s
WHERE
DeveloperCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
return NULL;
else
return new;
end if;
END;

end if;
return new;
END;'
language 'plpgsql';

drop function fn_CompaniesProjects_XXDM2();

create function fn_CompaniesProjects_XXDM2() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER A DELETE ON TABLE Companies
(Master)
-- Generated by PgUpT 17/07/1999
--
--
-- Enforce relationship between Companies and Projects
--
DECLARE
l_s Projects%ROWTYPE;
l_err_text TEXT;
BEGIN
SELECT * FROM Projects INTO l_s WHERE
DeveloperCompanyId = old.CompanyId
;
if found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return old;
END;'

language 'plpgsql';

drop function fn_CompaniesProjects_XXXS2();

create function fn_CompaniesProjects_XXXS2() returns opaque as '
--
-- REFERENTIAL INTEGRITY CHECKING CODE BETWEEN Companies AND Projects
-- ENSURES THAT INTEGRITY IS MAINTAINED AFTER AN UPDATE ON TABLE Projects
(Slave)
-- Generated by PgUpT 17/07/1999
--
DECLARE
l_m Companies%ROWTYPE;
l_err_text text;
l_Flag integer;
BEGIN
l_Flag := 0;
if new.DeveloperCompanyId IS NOT NULL then
l_Flag := 1;
end if;
if l_Flag > 0 then
SELECT * FROM Companies INTO l_m
WHERE
CompanyId = new.DeveloperCompanyId
;
if not found then
l_err_text := f_Exception(3201, ''Companies,Projects'');
raise exception ''%'', l_err_text;
end if;
return NULL;
else
return new;
end if;
END;'
language 'plpgsql';

drop trigger tr_CompaniesProjects_AXUXM1 on Companies;
create trigger tr_CompaniesProjects_AXUXM1 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM1();

drop trigger tr_CompaniesProjects_BXXDM1 on Companies;
create trigger tr_CompaniesProjects_BXXDM1 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM1();

drop trigger tr_CompaniesProjects_AXUXS1 on Projects;
create trigger tr_CompaniesProjects_AXUXS1 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();

drop trigger tr_CompaniesProjects_AAXXS1 on Projects;
create trigger tr_CompaniesProjects_AAXXS1 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();

drop trigger tr_CompaniesProjects_AXUXM1 on Companies;
create trigger tr_CompaniesProjects_AXUXM1 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM1();

drop trigger tr_CompaniesProjects_BXXDM1 on Companies;
create trigger tr_CompaniesProjects_BXXDM1 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM1();

drop trigger tr_CompaniesProjects_AXUXS1 on Projects;
create trigger tr_CompaniesProjects_AXUXS1 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();

drop trigger tr_CompaniesProjects_AAXXS1 on Projects;
create trigger tr_CompaniesProjects_AAXXS1 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS1();
--
drop trigger tr_CompaniesProjects_AXUXM2 on Companies;
create trigger tr_CompaniesProjects_AXUXM2 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM2();

drop trigger tr_CompaniesProjects_BXXDM2 on Companies;
create trigger tr_CompaniesProjects_BXXDM2 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM2();

drop trigger tr_CompaniesProjects_AXUXS2 on Projects;
create trigger tr_CompaniesProjects_AXUXS2 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

drop trigger tr_CompaniesProjects_AAXXS2 on Projects;
create trigger tr_CompaniesProjects_AAXXS2 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

drop trigger tr_CompaniesProjects_AXUXM2 on Companies;
create trigger tr_CompaniesProjects_AXUXM2 after update on Companies
for each row execute procedure fn_CompaniesProjects_XUXM2();

drop trigger tr_CompaniesProjects_BXXDM2 on Companies;
create trigger tr_CompaniesProjects_BXXDM2 before delete on Companies
for each row execute procedure fn_CompaniesProjects_XXDM2();

drop trigger tr_CompaniesProjects_AXUXS2 on Projects;
create trigger tr_CompaniesProjects_AXUXS2 after update on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

drop trigger tr_CompaniesProjects_AAXXS2 on Projects;
create trigger tr_CompaniesProjects_AAXXS2 after insert on Projects
for each row execute procedure fn_CompaniesProjects_XXXS2();

-----Original Message-----
From: Sime� <simeo@nil.fut.es>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: 15 July 1999 13:24
Subject: [GENERAL] Foreign Keys in PostgreSQL

On Sat, 17 Jul 1999, [iso-8859-1] Sime��� wrote:

El dia 16-Jul-99 en Howie va escriure :

refit is, for now, the way postgres handles foreign keys. two functions
are involved, check_foreign_key() and check_primary_key().

Hello Howie, thanks for your help.I have a problem I don't Have any refi*.c.
Where I can find it ?. Thanks again

should be in pgsql_src_root/contrib/spi/. im not sure if it comes with
non-source distributions ( yet another reason to get source tarballs and
not rely on package management ).

---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."