pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Delay INSERT
12+ messages / 8 participants
[nested] [flat]

* Delay INSERT
@ 2005-03-23 11:46  ON.KG <skyer@on.kg>
  1 sibling, 2 replies; 12+ messages in thread

From: ON.KG @ 2005-03-23 11:46 UTC (permalink / raw)
  To: pgsql-php

Hi

Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
MySQL?

or any other way to delay inserting?

Thanx




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 13:33  Michael Fuhr <mike@fuhr.org>
  parent: ON.KG <skyer@on.kg>
  1 sibling, 0 replies; 12+ messages in thread

From: Michael Fuhr @ 2005-03-23 13:33 UTC (permalink / raw)
  To: ON.KG <skyer@on.kg>; +Cc: pgsql-php

On Wed, Mar 23, 2005 at 02:46:38PM +0300, ON.KG wrote:

> Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
> MySQL?
> 
> or any other way to delay inserting?

What problem are you trying to solve?  Are you aware that PostgreSQL
uses Multiversion Concurrency Control (MVCC) so readers and writers
don't block each other?

http://www.postgresql.org/docs/8.0/static/mvcc.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 14:40  Michael Fuhr <mike@fuhr.org>
  parent: ON.KG <skyer@on.kg>
  2 siblings, 0 replies; 12+ messages in thread

From: Michael Fuhr @ 2005-03-23 14:40 UTC (permalink / raw)
  To: ON.KG <skyer@on.kg>; +Cc: pgsql-general@postgresql.org

On Wed, Mar 23, 2005 at 07:31:22PM +0300, ON.KG wrote:

> Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
> MySQL?
> 
> or any other way to delay inserting?

What problem are you trying to solve?  Are you aware that PostgreSQL
uses Multiversion Concurrency Control (MVCC) so readers and writers
don't block each other?

http://www.postgresql.org/docs/8.0/static/mvcc.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 14:42  Christopher Browne <cbbrowne@acm.org>
  parent: ON.KG <skyer@on.kg>
  1 sibling, 0 replies; 12+ messages in thread

From: Christopher Browne @ 2005-03-23 14:42 UTC (permalink / raw)
  To: pgsql-php

After a long battle with technology, skyer@on.kg ("ON.KG"), an earthling, wrote:
> Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
> MySQL?
>
> or any other way to delay inserting?

What problem are you trying to solve by using "INSERT DELAYED"?

As far as I can tell, the problem solved using "INSERT DELAYED" is
that in MySQL AB's product, processes take out full table locks when
they modify tables.

That is completely irrelevant with PostgreSQL, as PostgreSQL does not
lock tables that way.

Unless your concern is something rather more abstruse, I think you're
trying to solve a problem that simply doesn't exist.  There is no
reason to delay inserts in PostgreSQL because there are no such locks.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/slony.html
"I would rather spend 10 hours reading someone else's source code than
10  minutes listening  to Musak  waiting for  technical  support which
isn't." -- Dr. Greg Wettstein, Roger Maris Cancer Center



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 14:50  Richard Huxton <dev@archonet.com>
  parent: ON.KG <skyer@on.kg>
  2 siblings, 1 reply; 12+ messages in thread

From: Richard Huxton @ 2005-03-23 14:50 UTC (permalink / raw)
  To: ON.KG <skyer@on.kg>; +Cc: pgsql-general@postgresql.org

ON.KG wrote:
> Hi
> 
> Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
> MySQL?
> 
> or any other way to delay inserting?

What precisely does this do?

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 15:50  Harald Fuchs <use_reply_to@protecting.net>
  parent: ON.KG <skyer@on.kg>
  2 siblings, 0 replies; 12+ messages in thread

From: Harald Fuchs @ 2005-03-23 15:50 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

In article <14435275218.20050323193122@on.kg>,
"ON.KG" <skyer@on.kg> writes:

> Hi
> Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
> MySQL?

> or any other way to delay inserting?

Every INSERT in PostgreSQL is delayed in some sense: firstly, it is
not visible to anyone else until you commit, and secondly, it is
written first to the (supposedly fast) write-ahead log and only later
to the table files.

If you have problems with INSERT speed, describe hardware,
configuration, and table structure.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Delay INSERT
@ 2005-03-23 16:31  ON.KG <skyer@on.kg>
  1 sibling, 3 replies; 12+ messages in thread

From: ON.KG @ 2005-03-23 16:31 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

Hi

Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
MySQL?

or any other way to delay inserting?

Thanx




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 16:34  Dawid Kuroczko <qnex42@gmail.com>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 3 replies; 12+ messages in thread

From: Dawid Kuroczko @ 2005-03-23 16:34 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

On Wed, 23 Mar 2005 14:50:47 +0000, Richard Huxton <dev@archonet.com> wrote:
> ON.KG wrote:
> > Hi
> >
> > Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
> > MySQL?
> >
> > or any other way to delay inserting?
> 
> What precisely does this do?

It adds an insert into a 'do this' queue and returns.  From PostgreSQL-s
point of view it would be equivalent of issuing INSERT and not waiting
for the result.

The MySQL has this mainly because when other statement such as
SELECT or UPDATE is in progress, the INSERT would be blocked.

PostgreSQL doesn't have such issues with blocking, so only difference
between INSERT and INSERT DELAYED from PostgreSQL's standpoint
would be waiting and not for the result...



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 16:49  Richard Huxton <dev@archonet.com>
  parent: Dawid Kuroczko <qnex42@gmail.com>
  2 siblings, 0 replies; 12+ messages in thread

From: Richard Huxton @ 2005-03-23 16:49 UTC (permalink / raw)
  To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: pgsql-general@postgresql.org

Dawid Kuroczko wrote:
> On Wed, 23 Mar 2005 14:50:47 +0000, Richard Huxton <dev@archonet.com> wrote:
> 
>>ON.KG wrote:
>>
>>>Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
>>>MySQL?
>>>or any other way to delay inserting?
>>
>>What precisely does this do?
> 
> It adds an insert into a 'do this' queue and returns.  From PostgreSQL-s
> point of view it would be equivalent of issuing INSERT and not waiting
> for the result.

OK - thanks.

> The MySQL has this mainly because when other statement such as
> SELECT or UPDATE is in progress, the INSERT would be blocked.
> 
> PostgreSQL doesn't have such issues with blocking, so only difference
> between INSERT and INSERT DELAYED from PostgreSQL's standpoint
> would be waiting and not for the result...

Well, if you don't actually care whether it got inserted or not, just 
throw the data away! That's got to be the quickest of all.

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 17:02  Greg Stark <gsstark@mit.edu>
  parent: Dawid Kuroczko <qnex42@gmail.com>
  2 siblings, 0 replies; 12+ messages in thread

From: Greg Stark @ 2005-03-23 17:02 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

Dawid Kuroczko <qnex42@gmail.com> writes:

> PostgreSQL doesn't have such issues with blocking, so only difference
> between INSERT and INSERT DELAYED from PostgreSQL's standpoint
> would be waiting and not for the result...

An insert can be blocked if there's a UNIQUE constraint and another
transaction has an insert or update pending for the same key. If the other
transaction commits you get a unique constraint violation, if it aborts your
insert succeeds.

-- 
greg




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-23 17:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dawid Kuroczko <qnex42@gmail.com>
  2 siblings, 1 reply; 12+ messages in thread

From: Tom Lane @ 2005-03-23 17:33 UTC (permalink / raw)
  To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: pgsql-general@postgresql.org

Dawid Kuroczko <qnex42@gmail.com> writes:
> PostgreSQL doesn't have such issues with blocking, so only difference
> between INSERT and INSERT DELAYED from PostgreSQL's standpoint
> would be waiting and not for the result...

With the right client-side code you can transmit multiple queries before
receiving the result from the first one.  I don't think libpq in its
current incarnation really supports this, but in principle it's doable.

The interesting questions have to do with error handling: if the
"delayed" insert fails, what happens and what is the impact on
subsequent queries?  I have no idea how MySQL defines that.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Delay INSERT
@ 2005-03-25 16:29  Dawid Kuroczko <qnex42@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 12+ messages in thread

From: Dawid Kuroczko @ 2005-03-25 16:29 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

On Wed, 23 Mar 2005 12:33:00 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dawid Kuroczko <qnex42@gmail.com> writes:
> > PostgreSQL doesn't have such issues with blocking, so only difference
> > between INSERT and INSERT DELAYED from PostgreSQL's standpoint
> > would be waiting and not for the result...

> With the right client-side code you can transmit multiple queries before
> receiving the result from the first one.  I don't think libpq in its
> current incarnation really supports this, but in principle it's doable.

...though I think it should be called asynchronic rather than
'delayed'.  I.e. issue a statement then select(2) in a spare
time to get the result, doing other work in meantime.

> The interesting questions have to do with error handling: if the
> "delayed" insert fails, what happens and what is the impact on
> subsequent queries?  I have no idea how MySQL defines that.

Well, looking at the on-line mysql docs -- it is not documented,
I did however try to issue them (on 3.x mysql installation).
I've created a table with a single int PK column, then inserted
delayed few values, some of which broke PK contraint and these
were silently discared.  There was no point checking how it would
work with transactions since ...delayed is MyISAM only and these
don't do transactions.

And as for PostgreSQL and asynchronic statements, I think it
should allow Pipelineing -- similar to NNTP or SMTP servers -- these would
send N commands to a server and then read them as-they-come-in.
Some usage example, imagine a web page which would:
 $q1 = execute_async("SELECT foo FROM a");
 $q2 = execute_async("SELECT * FROM polls LIMIT 5"); # whatever
 $q3 = execute_async("SELECT baz FROM bazzz");
  print some html;  print $q1->fetchall, some html $q2->fetchall,
  $q3->fetchall...

There is a question of synchronization and handling errors of course,
but I think it could be done and for some usage scenarios could be
quite benefitial.

Disclaimer: I never wrote a program using libpq, and I only looked
through libpq docs some time ago.  If I am wrong, please forgive me.

   Regards,
      Dawid



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-03-25 16:29 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-23 11:46 ` ON.KG <skyer@on.kg>
2005-03-23 13:33   ` Michael Fuhr <mike@fuhr.org>
2005-03-23 14:42   ` Christopher Browne <cbbrowne@acm.org>
2005-03-23 16:31 ` ON.KG <skyer@on.kg>
2005-03-23 14:40   ` Michael Fuhr <mike@fuhr.org>
2005-03-23 14:50   ` Richard Huxton <dev@archonet.com>
2005-03-23 16:34     ` Dawid Kuroczko <qnex42@gmail.com>
2005-03-23 16:49       ` Richard Huxton <dev@archonet.com>
2005-03-23 17:02       ` Greg Stark <gsstark@mit.edu>
2005-03-23 17:33       ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-25 16:29         ` Dawid Kuroczko <qnex42@gmail.com>
2005-03-23 15:50   ` Harald Fuchs <use_reply_to@protecting.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox