Re: [GENERAL] Foreign Keys in PostgreSQL

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?

Thank you for any info this.

Tiago Hermans
thermans(at)hawaii(dot)rr(dot)com

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:
>
> 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?
>
> Thank you for any info this.
>
> Tiago Hermans
> thermans(at)hawaii(dot)rr(dot)com

[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(at)candle(dot)pha(dot)pa(dot)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(at)tinet(dot)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(at)toodarkpark(dot)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(at)tinet(dot)org
/( 0 )\ Tarragona (SPAIN)
^ ^ -----------------

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(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."

Vince, can you please make this change for me?

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > > 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
>
>

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