Обсуждение: PostgreSQL questions

Dear PostgreSQL novice list,

I am starting development on an application that requires the use of a
Database. I am considering MySQL vs PostgreSQL and have done extensive
(perhaps incomplete) research on both of these.

I have 2 questions about PostgreSQL:
1.) Is there a difference in running PostgreSQL on Windows vs Linux. I
am a Windows user but have a Linux box. For the application I am
leaning more to Linux. Is there advantages or factors I should
consider when choosing the OS?

2.) This question is related to triggers. Can I use a trigger to
notify my external application about a change in the database. I am
looking at using QT for development because of its platform
independence. Thus the complete question is: Can I notify the QT
application that data was added to the database (using a trigger)
without having to query the database the whole time?

The specifications for the application are real-time to semi-real-time
thus I need an OS with semi-real-time characteristics (Windows is not
as far as I know, that is why Linux is a good option). And I want to
notifying the application of changes in the database as soon as they
happen, this will help with the real-time aspect.

Hope I can get this sorted soon as development should start asap.

Thank you,
Carel

This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.

Carel Combrink <s25291930@tuks.co.za> writes:
> I have 2 questions about PostgreSQL:
> 1.) Is there a difference in running PostgreSQL on Windows vs Linux. I
> am a Windows user but have a Linux box. For the application I am
> leaning more to Linux. Is there advantages or factors I should
> consider when choosing the OS?

The PG developers practically all prefer Unix over Windows, and view
running *any* kind of server on Windows with suspicion.  The Windows
port of PG is plenty good enough for development purposes, but if you're
after a high-reliability server you should lean towards the Unix camp.
Now whether you should prefer Linux or some other Unix flavor is another
question, for which you will get different answers from different people.

> The specifications for the application are real-time to semi-real-time
> thus I need an OS with semi-real-time characteristics (Windows is not
> as far as I know, that is why Linux is a good option). And I want to
> notifying the application of changes in the database as soon as they
> happen, this will help with the real-time aspect.

What do you consider to be "real time"?  Practically any SQL database is
going to give you fits if you have very short hard deadlines.  In
general I would say that PG is optimized with total throughput much more
in mind than guaranteed response to any one query, so tight deadlines
could be a killer for you.  (MySQL is unlikely to be much better on that
dimension, btw.)

            regards, tom lane

Gurjeet Singh <singh.gurjeet@gmail.com> writes:
> Till Postgres 8.4 the NOTIFY commands could get lost,

That is certainly false.

            regards, tom lane

On 2010-03-12, Carel Combrink <s25291930@tuks.co.za> wrote:
> Dear PostgreSQL novice list,
>
> I am starting development on an application that requires the use of a
> Database. I am considering MySQL vs PostgreSQL and have done extensive
> (perhaps incomplete) research on both of these.
>
> I have 2 questions about PostgreSQL:
> 1.) Is there a difference in running PostgreSQL on Windows vs Linux. I
> am a Windows user but have a Linux box. For the application I am
> leaning more to Linux. Is there advantages or factors I should
> consider when choosing the OS?

It works better on linux, not that it's flaky on windows,
it's designed from a posix standpoint, and just fits better with the
OS on linux.

> 2.) This question is related to triggers. Can I use a trigger to
> notify my external application about a change in the database.

Use notify for this.

Notify is a (postgres specific) SQL command that can be executed as a
query or by a trigger.  it will send an alert to any process that is
listening for that notify.

> I am
> looking at using QT for development because of its platform
> independence. Thus the complete question is: Can I notify the QT
> application that data was added to the database (using a trigger)
> without having to query the database the whole time?

notify can be listened for using [p]select() in combination with some
libpq calls, you can do that in a thread or poll periodically from
the application's idle loop.

> The specifications for the application are real-time to semi-real-time
> thus I need an OS with semi-real-time characteristics (Windows is not
> as far as I know, that is why Linux is a good option). And I want to
> notifying the application of changes in the database as soon as they
> happen, this will help with the real-time aspect.
>
> Hope I can get this sorted soon as development should start asap.

Carel Combrink schrieb:
[...]
> 2.) This question is related to triggers. Can I use a trigger to notify
> my external application about a change in the database. I am looking at
> using QT for development because of its platform independence. Thus the
> complete question is: Can I notify the QT application that data was
> added to the database (using a trigger) without having to query the
> database the whole time?

Have a look at the docu of Qt4.4:

http://doc.trolltech.com/4.4/qsqldriver.html#notification

Stefan

On Thu, Mar 18, 2010 at 5:55 AM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> Slide 9 says:
>
> Events can be lost!
> – If the same event occurs between two calls on
> collection by a backend, it will only see one of them
> – Because pg_listener has one row per (event,
> listener) pair.
>
> And I think these limitations are being remedied by the new implementation
> in 9.0.

Anything that describes notifications as "events" is on the wrong
track. This isn't a queueing system, it's a facility is analogous to
unix signals or hardware interrupts -- notifications are condition
variables. If your cache is invalidated twice you only need to know
that it was invalidated, not how many times.

Queueing systems are hard, you have to deal with large volumes of
short-lived data and deal with things like priorities and so on.
Interrupt mechanisms are easy, they're just a small set of flags that
need to be flipped and checked at the right time.


--
greg