Re: [GENERAL] Server-side hooks for user session start and session end

[Pavel Stehule]

>
>> > years ago I tried it, if I remember well. I had a problems with SPI
>> calls,
>> > because some caches was not initialized. I am not sure, and I didn't
>> test
>> > last time.
>>
>> You'd have to start your own transaction if you wanted one, and any
>> uncaught error would effectively be FATAL because it would terminate the
>> session, but otherwise I don't see why that wouldn't work.
>>
>
> Probably I didn't start transaction.
>
> I'll check it.
>

It is working. Patch attached

Regards

Pavel
diff --git a/contrib/session_exec/Makefile b/contrib/session_exec/Makefile
new file mode 100644
index ...fd21d87
*** a/contrib/session_exec/Makefile
--- b/contrib/session_exec/Makefile
***
*** 0 
--- 1,16 
+ # contrib/session_exec/Makefile
+ 
+ MODULE_big = session_exec
+ OBJS = session_exec.o $(WIN32RES)
+ PGFILEDESC = "session_exec - logging facility for execution plans"
+ 
+ ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS := $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/session_exec
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
diff --git a/contrib/session_exec/session_exec.c b/contrib/session_exec/session_exec.c
new file mode 100644
index ...3bdead5
*** a/contrib/session_exec/session_exec.c
--- b/contrib/session_exec/session_exec.c
***
*** 0 
--- 1,89 
+ /*-
+  *
+  * session_exec.c
+  *
+  *
+  * Copyright (c) 2008-2015, PostgreSQL Global Development Group
+  *
+  * IDENTIFICATION
+  *	  contrib/session_exec/session_exec.c
+  *
+  *-
+  */
+ #include "postgres.h"
+ #include "fmgr.h"
+ #include "access/xact.h"
+ #include "catalog/namespace.h"
+ #include "storage/ipc.h"
+ #include "utils/builtins.h"
+ #include "utils/guc.h"
+ 
+ PG_MODULE_MAGIC;
+ 
+ void		_PG_init(void);
+ void		_PG_fini(void);
+ 
+ char *session_login_function_name = NULL;
+ 
+ /*
+  * Execute function named funcname. This function
+  * has not to have any parameters. This routine will
+  * be used for execution login/logout functions.
+  */
+ static void
+ exec_function(char *funcname)
+ {
+ 	FuncCandidateList clist;
+ 	List *names;
+ 
+ 	names = stringToQualifiedNameList(funcname);
+ 	clist = FuncnameGetCandidates(names, 0, NIL, false, false, true);
+ 
+ 	if (clist == NULL)
+ 		elog(WARNING, "function \"%s()\" does not exist", funcname);
+ 	else
+ 	{
+ 		/* execute function */
+ 		OidFunctionCall0(clist->oid);
+ 	}
+ }
+ 
+ void
+ _PG_init(void)
+ {
+ 	DefineCustomStringVariable("session_exec.login_name",
+ 	   "Define function that will be executed on login",
+ 	   "It is undefined by default",
+ 	   &session_login_function_name,
+ 	   NULL,
+ 	   PGC_USERSET,
+ 	   0, NULL, NULL, NULL);
+ 
+ 	if (session_login_function_name != NULL && *session_login_function_name != '\0')
+ 	{
+ 		MemoryContext	oldCtx = CurrentMemoryContext;
+ 
+ 		PG_TRY();
+ 		{
+ 			SetCurrentStatementStartTimestamp();
+ 			StartTransactionCommand();
+ 
+ 			exec_function(session_login_function_name);
+ 
+ 			CommitTransactionCommand();
+ 		}
+ 		PG_CATCH();
+ 		{
+ 			ErrorData	*edata;
+ 
+ 			MemoryContextSwitchTo(oldCtx);
+ 			edata = CopyErrorData();
+ 			FlushErrorState();
+ 
+ 			elog(FATAL, "exception in login function \"%s\"",
+ edata->message);
+ 		}
+ 		PG_END_TRY();
+ 	}
+ }
+ 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server-side hooks for user session start and session end

[Nikolai Zhubr]

26.09.2015 20:59, Tom Lane wrote:
[...]

A session-end hook seems pretty problematic to me: you simply cannot
guarantee that it will run at all.  (Consider process crash or server
abort cases.)  So anything built on the assumption that it gets to run
at session end is going to be inherently unreliable.


Yes, I understand that.
For such cases (of e.g. abnormal shutdown) it would also be nice if a 
database could 'autorun' some specified function just after server has 
performed whetever startup/replay/cleanup procedures necessary and 
immediately before it is ready for normal operation. This would allow to 
perform some checks and cleanups to restore consistency. And actually, 
such 'autorun' function could be interesting regardless of this session 
begin/end thing.
And there are already quite some hooks in use for session-end cleanups 
at C-level, like e.g. for disposing temp tables, just none of them 
expose anything to SQL level. I'd guess this technique is available for 
use by extensions, so potentially SQL-level hook could also be 
implemented. Though I think it is a bit beyond my capability at the 
moment...


Regards,


Thank you,
Nikolai


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server-side hooks for user session start and session end

[David G. Johnston]

On Saturday, September 26, 2015, Tom Lane  wrote:

> Pavel Stehule > writes:
> > 2015-09-26 19:53 GMT+02:00 Nikolai Zhubr  >:
> >> And the events of session start and session end would seem quite generic
> >> and usefull anyway?
>
> > I don't know. I am pretty sceptical - from my experience this request was
> > related to unhappy designed projects.
>
> A session-end hook seems pretty problematic to me: you simply cannot
> guarantee that it will run at all.  (Consider process crash or server
> abort cases.)  So anything built on the assumption that it gets to run
> at session end is going to be inherently unreliable.
>
>
Or even "reset session" as is often used by connection poolers depending on
what semantics are trying to be captured.

David J.

Re: [GENERAL] Server-side hooks for user session start and session end

[Pavel Stehule]

2015-09-26 20:29 GMT+02:00 Tom Lane :

> Pavel Stehule  writes:
> > 2015-09-26 19:59 GMT+02:00 Tom Lane :
> >> A session-start hook is already possible at the C-code level, using
> >> session_preload_libraries.  It wouldn't be hard to write an extension
> >> that exposed that in some useful way to SQL code.
>
> > years ago I tried it, if I remember well. I had a problems with SPI
> calls,
> > because some caches was not initialized. I am not sure, and I didn't test
> > last time.
>
> You'd have to start your own transaction if you wanted one, and any
> uncaught error would effectively be FATAL because it would terminate the
> session, but otherwise I don't see why that wouldn't work.
>

Probably I didn't start transaction.

I'll check it.

Regards

Pavel


>
> regards, tom lane
>

Re: [GENERAL] Server-side hooks for user session start and session end

[Tom Lane]

Pavel Stehule  writes:
> 2015-09-26 19:59 GMT+02:00 Tom Lane :
>> A session-start hook is already possible at the C-code level, using
>> session_preload_libraries.  It wouldn't be hard to write an extension
>> that exposed that in some useful way to SQL code.

> years ago I tried it, if I remember well. I had a problems with SPI calls,
> because some caches was not initialized. I am not sure, and I didn't test
> last time.

You'd have to start your own transaction if you wanted one, and any
uncaught error would effectively be FATAL because it would terminate the
session, but otherwise I don't see why that wouldn't work.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server-side hooks for user session start and session end

[Pavel Stehule]

2015-09-26 19:59 GMT+02:00 Tom Lane :

> Pavel Stehule  writes:
> > 2015-09-26 19:53 GMT+02:00 Nikolai Zhubr :
> >> And the events of session start and session end would seem quite generic
> >> and usefull anyway?
>
> > I don't know. I am pretty sceptical - from my experience this request was
> > related to unhappy designed projects.
>
> A session-end hook seems pretty problematic to me: you simply cannot
> guarantee that it will run at all.  (Consider process crash or server
> abort cases.)  So anything built on the assumption that it gets to run
> at session end is going to be inherently unreliable.
>
> A session-start hook is already possible at the C-code level, using
> session_preload_libraries.  It wouldn't be hard to write an extension
> that exposed that in some useful way to SQL code.
>

years ago I tried it, if I remember well. I had a problems with SPI calls,
because some caches was not initialized. I am not sure, and I didn't test
last time.

Regards

Pavel

>
> regards, tom lane
>

Re: [GENERAL] Server-side hooks for user session start and session end

[Tom Lane]

Pavel Stehule  writes:
> 2015-09-26 19:53 GMT+02:00 Nikolai Zhubr :
>> And the events of session start and session end would seem quite generic
>> and usefull anyway?

> I don't know. I am pretty sceptical - from my experience this request was
> related to unhappy designed projects.

A session-end hook seems pretty problematic to me: you simply cannot
guarantee that it will run at all.  (Consider process crash or server
abort cases.)  So anything built on the assumption that it gets to run
at session end is going to be inherently unreliable.

A session-start hook is already possible at the C-code level, using 
session_preload_libraries.  It wouldn't be hard to write an extension
that exposed that in some useful way to SQL code.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server-side hooks for user session start and session end

[Pavel Stehule]

2015-09-26 19:53 GMT+02:00 Nikolai Zhubr :

> Hi Pavel,
> 26.09.2015 19:26, Pavel Stehule wrote:
> [...]
>
>> This cannot be solved without patching PostgreSQL source code :( . There
>> are not good hooks for custom extension. Patch is relative simple, but I
>> cannot to publish it.
>>
>
> Ok, I see. Creating such a patch might be not very hard actually.
> But living with custom-patched server could cause massive pain later...
>
> You have to modify main loop in src/backend/tcop/postgres.c file. Login
>> point can be immediately before loop. Logout point can be joined to
>> 'X',EOF message.
>>
>
> Thanks for the hint, I've noticed this loop while searching for some more
> 'regular' methods.
>
> Attention - there are some corner case, what are necessary to solve -
>> mainly how to handle exceptions in login/logout procedures.
>>
>
> Right. And I'd guess that might be the reason for not implementing such
> thing in upstream (yet) - the lack of proper context for exceptions and
> such.
>
> p.s. I understand so this hack is necessary for porting older
>> application from other databases, but I am not sure if it is a good idea
>> to use it. Not all tasks can be solved well in database.
>>
>
> No, the application in question has been running on top of postgres almost
> from its very start and for quite some years successfully now, it
> definitely does not contain anything foreign to postgres.
> And the events of session start and session end would seem quite generic
> and usefull anyway?


I don't know. I am pretty sceptical - from my experience this request was
related to unhappy designed projects.

Pavel



>
>
>
> Thank you,
> Nikolai
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Server-side hooks for user session start and session end

[Nikolai Zhubr]

Hi Pavel,
26.09.2015 19:26, Pavel Stehule wrote:
[...]

This cannot be solved without patching PostgreSQL source code :( . There
are not good hooks for custom extension. Patch is relative simple, but I
cannot to publish it.


Ok, I see. Creating such a patch might be not very hard actually.
But living with custom-patched server could cause massive pain later...


You have to modify main loop in src/backend/tcop/postgres.c file. Login
point can be immediately before loop. Logout point can be joined to
'X',EOF message.


Thanks for the hint, I've noticed this loop while searching for some 
more 'regular' methods.



Attention - there are some corner case, what are necessary to solve -
mainly how to handle exceptions in login/logout procedures.


Right. And I'd guess that might be the reason for not implementing such 
thing in upstream (yet) - the lack of proper context for exceptions and 
such.



p.s. I understand so this hack is necessary for porting older
application from other databases, but I am not sure if it is a good idea
to use it. Not all tasks can be solved well in database.


No, the application in question has been running on top of postgres 
almost from its very start and for quite some years successfully now, it 
definitely does not contain anything foreign to postgres.
And the events of session start and session end would seem quite generic 
and usefull anyway?



Thank you,
Nikolai


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server-side hooks for user session start and session end

[Pavel Stehule]

2015-09-26 18:17 GMT+02:00 Nikolai Zhubr :

> Hi all,
>
> I'm trying to find a soultion to automatically execute something
> (preferrably a function or at least some pure sql statements) at the
> beginning and at the end of a user session. As an example, imagine just
> storing of all login and logout timestamps (though the real task is a bit
> more complicated than that)
>
> I would not like it to be somehow explicitely client-side initiated (like
> e.g. trivially calling some certain do_at_sess_begin(), do_at_sess_end())
> because first, a malicious client could then mess it up easily, and
> furthermore, in the case that the session somehow ended abnormally (due to
> say temporary network fault) client-side finalizer function would not be
> called anyway.
>
> My first try was to create an event trigger for sql drop events, then
> create a temporary table and watch for its drop at the end of a session.
> However, it seems drop events are just not fired for temporary tables
> (although documentation does not state this anywhere, as far as I can see,
> maybe I'm wrong about that)
>
> It would seem like one of the simplest things ever, but I'm now totally
> stuck out of ideas.
>
> As a partial solution, handling just session _end_ would already be OK.


> Any thoughts?
>

This cannot be solved without patching PostgreSQL source code :( . There
are not good hooks for custom extension. Patch is relative simple, but I
cannot to publish it.

You have to modify main loop in src/backend/tcop/postgres.c file. Login
point can be immediately before loop. Logout point can be joined to
'X',EOF message.

Attention - there are some corner case, what are necessary to solve -
mainly how to handle exceptions in login/logout procedures.

Regards

Pavel

p.s. I understand so this hack is necessary for porting older application
from other databases, but I am not sure if it is a good idea to use it. Not
all tasks can be solved well in database.


>
>
> Thank you,
> Nikolai
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Server-side hooks for user session start and session end

[Nikolai Zhubr]

Hi all,

I'm trying to find a soultion to automatically execute something 
(preferrably a function or at least some pure sql statements) at the 
beginning and at the end of a user session. As an example, imagine just 
storing of all login and logout timestamps (though the real task is a 
bit more complicated than that)


I would not like it to be somehow explicitely client-side initiated 
(like e.g. trivially calling some certain do_at_sess_begin(), 
do_at_sess_end()) because first, a malicious client could then mess it 
up easily, and furthermore, in the case that the session somehow ended 
abnormally (due to say temporary network fault) client-side finalizer 
function would not be called anyway.


My first try was to create an event trigger for sql drop events, then 
create a temporary table and watch for its drop at the end of a session. 
However, it seems drop events are just not fired for temporary tables 
(although documentation does not state this anywhere, as far as I can 
see, maybe I'm wrong about that)


It would seem like one of the simplest things ever, but I'm now totally 
stuck out of ideas.


As a partial solution, handling just session _end_ would already be OK.

Any thoughts?


Thank you,
Nikolai


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to speed up delete where not in

[Melvin Davidson]

In addition to the previous recommendation, make sure you have an index on
dokumnr in table omrid.

EG:
CREATE INDEX omrid_dokumnr_fk ON omrid
  USING BTREE (dokumnr);



On Sat, Sep 26, 2015 at 7:33 AM, David Rowley 
wrote:

> On 26 September 2015 at 19:53, Andrus  wrote:
>
>> Hi!
>>
>>
>> I'm looking for a way to delete records which do not have child rows on
>> big tables where lot of rows needs to be deleted. Both tables have lot of
>> other foreign key references.
>>
>>
>> Document headers are in omdok table:
>>
>>create table omdok ( dokumnr serial primary key, ... );
>>
>> Document rows are in omrid table
>>
>>CREATE TABLE omrid
>>(
>>  id serial NOT NULL,
>>  reanr serial NOT NULL,
>>  dokumnr integer NOT NULL,
>>  CONSTRAINT omrid_pkey PRIMARY KEY (id),
>>  CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
>>  REFERENCES omdok (dokumnr) MATCH SIMPLE
>>  ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY
>> IMMEDIATE,
>>  
>>);
>>
>>
>> I tried
>>
>>delete from omdok where dokumnr not in  (select dokumnr from omrid)
>>
>> Query it is running currently 15 hours and is still running.
>> postgres.exe is using 50% CPU  all the time (this is 2 core CPU).
>>
>>explain delete from omdok where dokumnr not in  (select dokumnr
>> from omrid)
>>
>> returns:
>>
>>"Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
>>"  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815
>> width=6)"
>>"Filter: (NOT (SubPlan 1))"
>>"SubPlan 1"
>>"  ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
>>"->  Seq Scan on omrid  (cost=0.00..77858.61
>> rows=1897261 width=4)"
>>
>> - How to delete parents without child rows fast?
>>
>
> if you write the query as with a NOT EXISTS, rather than a NOT IN() it
> should complete much more quickly.
>
> It's important to know that the semantics of NOT IN are likely not at all
> what you think:
>
> For example, in the following query, would you expect it to return the row
> with a.a = 3 ?
>
> select * from (values(1),(2),(3)) a(a) WHERE a NOT IN(SELECT b FROM
> (VALUES(NULL),(1),(2)) b(b));
>
> The presence of NULL causes this to not behave the way you might think,
> yet it works exactly the way the SQL standard dictates.
>
> You could think of this as equivalent to writing:
>
> select * from (values(1),(2),(3)) a(a) WHERE a.a <> NULL AND a.a <> 1 AND
> a.a <> 2;
>
> And since a.a <> NULL is 'unknown', this causes the entire WHERE clause to
> be false, therefore nothing matches.
>
> For this reason PostgreSQL does not optimise NOT IN() the same way as it
> optimises NOT EXISTS().
>
> If you write the query as:
>
> delete from omdok where not exists(select 1 from omrid where omdok.dokumnr
> = omrid.dokumnr);
>
> then you might see it execute in a few seconds. Perhaps you should
> consider cancelling the current query, perhaps perform a VACUUM on omdoc
> after cancelling, and then run the NOT EXISTS version instead.
>
> I mocked up your tables locally, and populated them with the same number
> of records as your row estimates in the EXPLAIN you pasted and I got:
>
> test=# delete from omdok where not exists (select 1 from omrid where
> omdok.dokumn = omrid.dokumnr);
> DELETE 0
> Time: 1698.233 ms
>
> Whereas with the NOT IN() I cancelled it after 10 minutes.
>
> Regards
>
> David Rowley
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
> 
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to speed up delete where not in

[David Rowley]

On 26 September 2015 at 19:53, Andrus  wrote:

> Hi!
>
>
> I'm looking for a way to delete records which do not have child rows on
> big tables where lot of rows needs to be deleted. Both tables have lot of
> other foreign key references.
>
>
> Document headers are in omdok table:
>
>create table omdok ( dokumnr serial primary key, ... );
>
> Document rows are in omrid table
>
>CREATE TABLE omrid
>(
>  id serial NOT NULL,
>  reanr serial NOT NULL,
>  dokumnr integer NOT NULL,
>  CONSTRAINT omrid_pkey PRIMARY KEY (id),
>  CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
>  REFERENCES omdok (dokumnr) MATCH SIMPLE
>  ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY
> IMMEDIATE,
>  
>);
>
>
> I tried
>
>delete from omdok where dokumnr not in  (select dokumnr from omrid)
>
> Query it is running currently 15 hours and is still running.
> postgres.exe is using 50% CPU  all the time (this is 2 core CPU).
>
>explain delete from omdok where dokumnr not in  (select dokumnr
> from omrid)
>
> returns:
>
>"Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
>"  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815
> width=6)"
>"Filter: (NOT (SubPlan 1))"
>"SubPlan 1"
>"  ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
>"->  Seq Scan on omrid  (cost=0.00..77858.61
> rows=1897261 width=4)"
>
> - How to delete parents without child rows fast?
>

if you write the query as with a NOT EXISTS, rather than a NOT IN() it
should complete much more quickly.

It's important to know that the semantics of NOT IN are likely not at all
what you think:

For example, in the following query, would you expect it to return the row
with a.a = 3 ?

select * from (values(1),(2),(3)) a(a) WHERE a NOT IN(SELECT b FROM
(VALUES(NULL),(1),(2)) b(b));

The presence of NULL causes this to not behave the way you might think, yet
it works exactly the way the SQL standard dictates.

You could think of this as equivalent to writing:

select * from (values(1),(2),(3)) a(a) WHERE a.a <> NULL AND a.a <> 1 AND
a.a <> 2;

And since a.a <> NULL is 'unknown', this causes the entire WHERE clause to
be false, therefore nothing matches.

For this reason PostgreSQL does not optimise NOT IN() the same way as it
optimises NOT EXISTS().

If you write the query as:

delete from omdok where not exists(select 1 from omrid where omdok.dokumnr
= omrid.dokumnr);

then you might see it execute in a few seconds. Perhaps you should consider
cancelling the current query, perhaps perform a VACUUM on omdoc after
cancelling, and then run the NOT EXISTS version instead.

I mocked up your tables locally, and populated them with the same number of
records as your row estimates in the EXPLAIN you pasted and I got:

test=# delete from omdok where not exists (select 1 from omrid where
omdok.dokumn = omrid.dokumnr);
DELETE 0
Time: 1698.233 ms

Whereas with the NOT IN() I cancelled it after 10 minutes.

Regards

David Rowley

--
 David Rowley   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] How to speed up delete where not in

[Andrus]

Hi!


I'm looking for a way to delete records which do not have child rows on big 
tables where lot of rows needs to be deleted. Both tables have lot of other 
foreign key references.



Document headers are in omdok table:

   create table omdok ( dokumnr serial primary key, ... );

Document rows are in omrid table

   CREATE TABLE omrid
   (
 id serial NOT NULL,
 reanr serial NOT NULL,
 dokumnr integer NOT NULL,
 CONSTRAINT omrid_pkey PRIMARY KEY (id),
 CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
 REFERENCES omdok (dokumnr) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY 
IMMEDIATE,

 
   );


I tried

   delete from omdok where dokumnr not in  (select dokumnr from omrid)

Query it is running currently 15 hours and is still running.
postgres.exe is using 50% CPU  all the time (this is 2 core CPU).

   explain delete from omdok where dokumnr not in  (select dokumnr from 
omrid)


returns:

   "Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
   "  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815 
width=6)"

   "Filter: (NOT (SubPlan 1))"
   "SubPlan 1"
   "  ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
   "->  Seq Scan on omrid  (cost=0.00..77858.61 
rows=1897261 width=4)"


- How to delete parents without child rows fast?
- Will this command finish or is postgres hanging ?
- Currently it is running 15 hours. How many hours it takes to finish ?
How to speed up this query ?


Using

   PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit

   Windows 2003 x64 server with 4 GB RAM.


Posted also in

http://stackoverflow.com/questions/32794828/how-to-speed-up-deleting-documents-without-rows

Andrus. 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Listen/notify, equivalents on other DBs

[Thomas Kellerer]

Mark Morgan Lloyd schrieb am 25.09.2015 um 23:41:

I'm trying to get support for PostgreSQL's listen/notify into a
development environment, but since it supports multiple database
backends: can anybody comment on how many other servers have a
comparable facility?

Minimal research has allowed me to code something for
Firebird/Interbase, but I'm not in a position to investigate Oracle,
IBM, MS and the rest. I'd appreciate any general comments from
somebody who has broad SQL experience, I'm not asking for example
code.


For Oracle this would be the "change notification" feature which
goes beyond what Postgres can do if I'm not mistaken as you can
register a query and the client is notified if the result of that
query would change:

http://docs.oracle.com/database/121/JJDBC/dbchgnf.htm#JJDBC28815






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general