pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed7.4Beta
30+ messages / 9 participants
[nested] [flat]
* 7.4Beta
@ 2003-08-14 18:51 Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: Peter Childs @ 2003-08-14 18:51 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Just loaded up 7.4Beta on a test system, to try and work out what the major
changes are/improvements.... I also started up pg_vacuum as this is now
working very well on our semi-live system (semi-live because we have not
finished developing the system yet.....)
Anyway. After trying to throw our 7.3 config file at it and it crashed badly
I resorted to the defaults. some of the settings in the config file have
changed. (Can somone make the config files backward compatable so at least
they ignor depricated settings!)
I throw last nights backup at it. Data went in in about 1/2 an hour then the
constraints went in and they took at age. about 2 hours.....
Is there anyway to speed up the database constraint code? Because quite
frankly at the current speed your probably better off without the
constraints.... (Same problem with 7.3 come to think about it.)
Otherwise 7.4. seams fine.
Question, when is replication and rolling backup comming. I want to be able
to take the last backup and a list of all the updates since then and get back
to the current database quickly.
Peter Childs
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
@ 2003-08-14 22:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 30+ messages in thread
From: Bruce Momjian @ 2003-08-14 22:13 UTC (permalink / raw)
To: Peter Childs <Blue.Dragon@blueyonder.co.uk>; +Cc: pgsql-hackers
[ Beta discussion moved to hackers.]
Peter Childs wrote:
> Just loaded up 7.4Beta on a test system, to try and work out what the major
> changes are/improvements.... I also started up pg_vacuum as this is now
> working very well on our semi-live system (semi-live because we have not
> finished developing the system yet.....)
> Anyway. After trying to throw our 7.3 config file at it and it crashed badly
> I resorted to the defaults. some of the settings in the config file have
> changed. (Can somone make the config files backward compatable so at least
> they ignor depricated settings!)
There will be a lost of config name and other changes in a special
section soon.
> I throw last nights backup at it. Data went in in about 1/2 an hour then the
> constraints went in and they took at age. about 2 hours.....
> Is there anyway to speed up the database constraint code? Because quite
> frankly at the current speed your probably better off without the
> constraints.... (Same problem with 7.3 come to think about it.)
> Otherwise 7.4. seams fine.
> Question, when is replication and rolling backup comming. I want to be able
> to take the last backup and a list of all the updates since then and get back
> to the current database quickly.
Rolling backups should be in 7.5, I hope.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-15 02:14 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 30+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-15 02:14 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; +Cc: pgsql-hackers
> > I throw last nights backup at it. Data went in in about 1/2 an hour then
the
> > constraints went in and they took at age. about 2 hours.....
> > Is there anyway to speed up the database constraint code? Because quite
> > frankly at the current speed your probably better off without the
> > constraints.... (Same problem with 7.3 come to think about it.)
I can also attest to the horrendously long time it takes to restore the ADD
FOREIGN KEY section...
Chris
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-08-15 05:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 3 replies; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 05:20 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
>
> > > I throw last nights backup at it. Data went in in about 1/2 an hour then
> the
> > > constraints went in and they took at age. about 2 hours.....
> > > Is there anyway to speed up the database constraint code? Because quite
> > > frankly at the current speed your probably better off without the
> > > constraints.... (Same problem with 7.3 come to think about it.)
>
> I can also attest to the horrendously long time it takes to restore the ADD
> FOREIGN KEY section...
That really needs to be rewritten to do a single check over the table
rather than running the constraint for every row. I keep meaning to get
around to it and never actually do. :( I'm not sure that in practice
you'll get a better plan at restore time depending on what the default
statistics give you.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 05:46 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 1 reply; 30+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-15 05:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
> > I can also attest to the horrendously long time it takes to restore the
ADD
> > FOREIGN KEY section...
>
> That really needs to be rewritten to do a single check over the table
> rather than running the constraint for every row. I keep meaning to get
> around to it and never actually do. :( I'm not sure that in practice
> you'll get a better plan at restore time depending on what the default
> statistics give you.
Surely in the default case it would reduce to using the new hashed IN()
feature, so it'd be a lot faster?
Chris
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-08-15 06:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 06:12 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
> > > I can also attest to the horrendously long time it takes to restore the
> ADD
> > > FOREIGN KEY section...
> >
> > That really needs to be rewritten to do a single check over the table
> > rather than running the constraint for every row. I keep meaning to get
> > around to it and never actually do. :( I'm not sure that in practice
> > you'll get a better plan at restore time depending on what the default
> > statistics give you.
>
> Surely in the default case it would reduce to using the new hashed IN()
> feature, so it'd be a lot faster?
If we wrote the query using IN that'd be the hope (I've not played with it
enough to guarantee that)
However, on a simple test comparing
select * from fk where not exists(select * from pk where pk.key=fk.key)
and key is not null;
(doing seq scan/subplan doing index scan - which is probably close to the
current system)
and
select * from fk where key in (select key from pk) and key is not null
on a pk table with 100k rows and fk table with 1m rows gives me a
difference of about 2x on my machine.
But that's with a single column int4 key, I haven't tried multi-column
keys or larger key types.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 11:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 14:35 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:13 ` Re: [GENERAL] 7.4Beta Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 30+ messages in thread
From: Tom Lane @ 2003-08-15 11:57 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> select * from fk where not exists(select * from pk where pk.key=fk.key)
> and key is not null;
> (doing seq scan/subplan doing index scan - which is probably close to the
> current system)
Actually, even that would probably be noticeably better than the current
system. I haven't profiled it (someone should) but I suspect that
executor startup/shutdown time is a huge hit. Even though the trigger
is caching a plan, it has to instantiate that plan for each referencing
tuple --- and the executor is not designed for quick startup/shutdown.
(Of course, this would become less relevant if the triggers got
rewritten to not go through SPI ...)
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-15 14:35 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 14:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > select * from fk where not exists(select * from pk where pk.key=fk.key)
> > and key is not null;
> > (doing seq scan/subplan doing index scan - which is probably close to the
> > current system)
>
> Actually, even that would probably be noticeably better than the current
> system. I haven't profiled it (someone should) but I suspect that
> executor startup/shutdown time is a huge hit. Even though the trigger
> is caching a plan, it has to instantiate that plan for each referencing
> tuple --- and the executor is not designed for quick startup/shutdown.
Yeah, but it was pretty much the best I could do testing on the command
line. And it was still a fair bit more expensive than using IN (my tests
on various key types showed anywhere from 15% to 300% better speed on IN
over exists for this).
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-15 17:13 ` Jan Wieck <JanWieck@Yahoo.com>
2003-08-15 18:49 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 30+ messages in thread
From: Jan Wieck @ 2003-08-15 17:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>> select * from fk where not exists(select * from pk where pk.key=fk.key)
>> and key is not null;
>> (doing seq scan/subplan doing index scan - which is probably close to the
>> current system)
>
> Actually, even that would probably be noticeably better than the current
> system. I haven't profiled it (someone should) but I suspect that
> executor startup/shutdown time is a huge hit. Even though the trigger
> is caching a plan, it has to instantiate that plan for each referencing
> tuple --- and the executor is not designed for quick startup/shutdown.
>
> (Of course, this would become less relevant if the triggers got
> rewritten to not go through SPI ...)
One of the reasons why we used SPI to generate the plans was the ease of
use. I'm not 100% sure, but I think the standard doesn't require the
referencing and referenced column(s) to be identical, only compatible.
So for example a text type foreign key can reference a varchar() and an
int4 can reference int8. Not using SPI for that lookup does not reduce
to a simple index- or seq-scan (depending on index availability on the
foreign key attributes).
Even if the standard does require it, we did not for a couple releases
and breaking that backward compatibility is IMHO not an option.
I'm thinking instead of a way to "cache" entire executors for this. Each
SPI plan used during a transaction would need it's own executor, and I
don't know offhand what type and how much resources an executor requires
(I think it's only some memory that get's initialized and the VFD's
opened). If I also remember correctly, the executor holds the pointer to
the parameters in the execstate and the actual values stay just in the
caller provided array. All that can change for a given plan between
SPI_execp() calls is this parameter array and the maxtuple arg. If an
executor is comparably cheap resource wise, SPI_execp() should be able
to just manipulate the parameter array in the execstate (with
propagation into the scankeys I fear) and then let it do a rescan. At
transaction commit time we'd need to close all executors then, like we
do with cursors.
Does that all make any sense to you?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 17:13 ` Re: [GENERAL] 7.4Beta Jan Wieck <JanWieck@Yahoo.com>
@ 2003-08-15 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 22:17 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: Tom Lane @ 2003-08-15 18:49 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
Jan Wieck <JanWieck@Yahoo.com> writes:
> I'm thinking instead of a way to "cache" entire executors for this. Each
> SPI plan used during a transaction would need it's own executor, and I
> don't know offhand what type and how much resources an executor requires
> (I think it's only some memory that get's initialized and the VFD's
> opened).
Hmm. This is probably more feasible now than it would have been a year
ago, because I did some cleanup work to ensure that executor state is
localized into a specific memory context. I'm not certain about the
amount of overhead either, but it's surely worth a try.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 17:13 ` Re: [GENERAL] 7.4Beta Jan Wieck <JanWieck@Yahoo.com>
2003-08-15 18:49 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-15 22:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-16 04:27 ` Re: [GENERAL] 7.4Beta Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 30+ messages in thread
From: Bruce Momjian @ 2003-08-15 22:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
Is there a TODO here?
---------------------------------------------------------------------------
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> > I'm thinking instead of a way to "cache" entire executors for this. Each
> > SPI plan used during a transaction would need it's own executor, and I
> > don't know offhand what type and how much resources an executor requires
> > (I think it's only some memory that get's initialized and the VFD's
> > opened).
>
> Hmm. This is probably more feasible now than it would have been a year
> ago, because I did some cleanup work to ensure that executor state is
> localized into a specific memory context. I'm not certain about the
> amount of overhead either, but it's surely worth a try.
>
> regards, tom lane
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 17:13 ` Re: [GENERAL] 7.4Beta Jan Wieck <JanWieck@Yahoo.com>
2003-08-15 18:49 ` Re: [GENERAL] 7.4Beta Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 22:17 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-16 04:27 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 30+ messages in thread
From: Jan Wieck @ 2003-08-16 04:27 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
Bruce Momjian wrote:
> Is there a TODO here?
Maybe!? It's one of these premature things noone can tell by now. So the
TODO would be "investigation" for now.
Jan
>
> ---------------------------------------------------------------------------
>
> Tom Lane wrote:
>> Jan Wieck <JanWieck@Yahoo.com> writes:
>> > I'm thinking instead of a way to "cache" entire executors for this. Each
>> > SPI plan used during a transaction would need it's own executor, and I
>> > don't know offhand what type and how much resources an executor requires
>> > (I think it's only some memory that get's initialized and the VFD's
>> > opened).
>>
>> Hmm. This is probably more feasible now than it would have been a year
>> ago, because I did some cleanup work to ensure that executor state is
>> localized into a specific memory context. I'm not certain about the
>> amount of overhead either, but it's surely worth a try.
>>
>> regards, tom lane
>>
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 06:05 ` Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 1 reply; 30+ messages in thread
From: Gavin Sherry @ 2003-08-15 06:05 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Thu, 14 Aug 2003, Stephan Szabo wrote:
> That really needs to be rewritten to do a single check over the table
> rather than running the constraint for every row. I keep meaning to get
> around to it and never actually do. :( I'm not sure that in practice
> you'll get a better plan at restore time depending on what the default
> statistics give you.
Perhaps it would be easier to allow SKIP VALIDATION (or something) with
ALTER TABLE ADD .... which can set FkConstraint->skip_validation. If we're
just handling pg_dump output, then presumably the data is already
validated. On the other handle, it might encourage users to bypass FKs
when they feel like it...
Thanks,
Gavin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
@ 2003-08-15 06:16 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:43 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:44 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 06:16 UTC (permalink / raw)
To: Gavin Sherry <swm@linuxworld.com.au>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Gavin Sherry wrote:
> On Thu, 14 Aug 2003, Stephan Szabo wrote:
>
> > That really needs to be rewritten to do a single check over the table
> > rather than running the constraint for every row. I keep meaning to get
> > around to it and never actually do. :( I'm not sure that in practice
> > you'll get a better plan at restore time depending on what the default
> > statistics give you.
>
> Perhaps it would be easier to allow SKIP VALIDATION (or something) with
> ALTER TABLE ADD .... which can set FkConstraint->skip_validation. If we're
> just handling pg_dump output, then presumably the data is already
> validated. On the other handle, it might encourage users to bypass FKs
> when they feel like it...
We've talked about stuff like that in the past, but we seem to generally
get stuck about how to specify it. If we add it to the alter table add as
an option then we're generating statements that are almost like a standard
sql statement, but not quite, and some people didn't like that. A set
option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
but I'd be worried about making it apply to the normal checks as well
(which I believe was suggested last time this came up). In any case,
making the full checks faster for when you really do care isn't a bad
plan. :)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 06:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 30+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-15 06:43 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; Gavin Sherry <swm@linuxworld.com.au>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
> We've talked about stuff like that in the past, but we seem to generally
> get stuck about how to specify it. If we add it to the alter table add as
> an option then we're generating statements that are almost like a standard
> sql statement, but not quite, and some people didn't like that. A set
> option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
> but I'd be worried about making it apply to the normal checks as well
> (which I believe was suggested last time this came up). In any case,
> making the full checks faster for when you really do care isn't a bad
> plan. :)
How about having an 'i'm restoring' SET var:
SET data_restore = true;
Which means (among other things) that FK's aren't checked?
Chris
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 06:44 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:58 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 30+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-15 06:44 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; Gavin Sherry <swm@linuxworld.com.au>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
> We've talked about stuff like that in the past, but we seem to generally
> get stuck about how to specify it. If we add it to the alter table add as
> an option then we're generating statements that are almost like a standard
> sql statement, but not quite, and some people didn't like that. A set
> option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
> but I'd be worried about making it apply to the normal checks as well
> (which I believe was suggested last time this came up). In any case,
> making the full checks faster for when you really do care isn't a bad
> plan. :)
Or, this:
1. Add the FK to the table BEFORE COPYing data
2. Use the old update blah set reltriggers = 0 trick
3. restore the data
4. Undo step 2
Chris
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:44 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-08-15 06:58 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 07:17 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 06:58 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Gavin Sherry <swm@linuxworld.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
> > We've talked about stuff like that in the past, but we seem to generally
> > get stuck about how to specify it. If we add it to the alter table add as
> > an option then we're generating statements that are almost like a standard
> > sql statement, but not quite, and some people didn't like that. A set
> > option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
> > but I'd be worried about making it apply to the normal checks as well
> > (which I believe was suggested last time this came up). In any case,
> > making the full checks faster for when you really do care isn't a bad
> > plan. :)
>
> Or, this:
>
> 1. Add the FK to the table BEFORE COPYing data
> 2. Use the old update blah set reltriggers = 0 trick
> 3. restore the data
> 4. Undo step 2
The problem with that is that I think the reltriggers=0 trick only works
if you're superuser, I thought that's why the trigger disabling became
optional. A set that affected only atac would probably be reasonable in
the dump (and presumably easy to do). It'd also carry over to future
cases where we separate some check constraints (for examples ones
that refer directly or indirectly to the same table in a subselect).
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:44 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:58 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 07:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 07:33 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 30+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-15 07:17 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Gavin Sherry <swm@linuxworld.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
> > 1. Add the FK to the table BEFORE COPYing data
> > 2. Use the old update blah set reltriggers = 0 trick
> > 3. restore the data
> > 4. Undo step 2
>
> The problem with that is that I think the reltriggers=0 trick only works
> if you're superuser, I thought that's why the trigger disabling became
> optional. A set that affected only atac would probably be reasonable in
> the dump (and presumably easy to do). It'd also carry over to future
> cases where we separate some check constraints (for examples ones
> that refer directly or indirectly to the same table in a subselect).
Well yes, this would be a super-user only ability. Are you worried about
people restoring dumps as non-superuser?
Chris
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:05 ` Re: [GENERAL] 7.4Beta Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:44 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:58 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 07:17 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-08-15 07:33 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 07:33 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Gavin Sherry <swm@linuxworld.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
> > > 1. Add the FK to the table BEFORE COPYing data
> > > 2. Use the old update blah set reltriggers = 0 trick
> > > 3. restore the data
> > > 4. Undo step 2
> >
> > The problem with that is that I think the reltriggers=0 trick only works
> > if you're superuser, I thought that's why the trigger disabling became
> > optional. A set that affected only atac would probably be reasonable in
> > the dump (and presumably easy to do). It'd also carry over to future
> > cases where we separate some check constraints (for examples ones
> > that refer directly or indirectly to the same table in a subselect).
>
> Well yes, this would be a super-user only ability. Are you worried about
> people restoring dumps as non-superuser?
Basically, yes (you might dump only some tables that you own for example).
I think that's why the data only dumps no longer do the reltriggers thing
by default and you need an option to get at it. We could make a similar
option for this case, but it'd only work when restored by a superuser.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 09:17 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 1 reply; 30+ messages in thread
From: Andreas Pflug @ 2003-08-15 09:17 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
Stephan Szabo wrote:
>On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote:
>
>
>
>>>>I throw last nights backup at it. Data went in in about 1/2 an hour then
>>>>
>>>>
>>the
>>
>>
>>>>constraints went in and they took at age. about 2 hours.....
>>>>Is there anyway to speed up the database constraint code? Because quite
>>>>frankly at the current speed your probably better off without the
>>>>constraints.... (Same problem with 7.3 come to think about it.)
>>>>
>>>>
>>I can also attest to the horrendously long time it takes to restore the ADD
>>FOREIGN KEY section...
>>
>>
>
>That really needs to be rewritten to do a single check over the table
>rather than running the constraint for every row. I keep meaning to get
>around to it and never actually do. :( I'm not sure that in practice
>you'll get a better plan at restore time depending on what the default
>statistics give you.
>
This is clearly a case for a statement level trigger, as soon as
affected rows can be identified.
One remark on that enable/disable triggers stuff: from a user's
perspective, I wouldn't consider a constraint trigger as a trigger, so
if I'd disable all triggers on a table, I still would expect all
constraints to be checked.
Regards,
Andreas
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
@ 2003-08-15 14:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 15:33 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 1 reply; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 14:38 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Peter Childs <Blue.Dragon@blueyonder.co.uk>; pgsql-hackers
On Fri, 15 Aug 2003, Andreas Pflug wrote:
> Stephan Szabo wrote:
>
> >That really needs to be rewritten to do a single check over the table
> >rather than running the constraint for every row. I keep meaning to get
> >around to it and never actually do. :( I'm not sure that in practice
> >you'll get a better plan at restore time depending on what the default
> >statistics give you.
> >
> This is clearly a case for a statement level trigger, as soon as
> affected rows can be identified.
Well, I think single inserts might be more expensive (because the query is
more involved for the table joining case) using a statement level trigger,
so we'd probably want to profile the cases.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 15:33 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 16:53 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 30+ messages in thread
From: Andreas Pflug @ 2003-08-15 15:33 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-hackers
Stephan Szabo wrote:
>On Fri, 15 Aug 2003, Andreas Pflug wrote:
>
>
>
>>Stephan Szabo wrote:
>>
>>
>>
>>>That really needs to be rewritten to do a single check over the table
>>>rather than running the constraint for every row. I keep meaning to get
>>>around to it and never actually do. :( I'm not sure that in practice
>>>you'll get a better plan at restore time depending on what the default
>>>statistics give you.
>>>
>>>
>>>
>>This is clearly a case for a statement level trigger, as soon as
>>affected rows can be identified.
>>
>>
>
>Well, I think single inserts might be more expensive (because the query is
>more involved for the table joining case) using a statement level trigger,
>so we'd probably want to profile the cases.
>
>
This really depends. If a constraint is just a check on the
inserted/updated column, so no other row needs to be checked, there's no
faster way then the current row trigger. But FK constraints need to
execute a query to retrieve the referenced row, and every RDBMS prefers
to execute a single statement with many rows over many statements with a
single row, because the first will profit from optimization. And even if
only a single row is inserted or updated, there's still the need to
lookup the reference.
Regards,
Andreas
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 15:33 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
@ 2003-08-15 16:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:20 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 1 reply; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 16:53 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; +Cc: pgsql-hackers
On Fri, 15 Aug 2003, Andreas Pflug wrote:
> Stephan Szabo wrote:
>
> >On Fri, 15 Aug 2003, Andreas Pflug wrote:
> >
> >
> >
> >>Stephan Szabo wrote:
> >>
> >>
> >>
> >>>That really needs to be rewritten to do a single check over the table
> >>>rather than running the constraint for every row. I keep meaning to get
> >>>around to it and never actually do. :( I'm not sure that in practice
> >>>you'll get a better plan at restore time depending on what the default
> >>>statistics give you.
> >>>
> >>>
> >>>
> >>This is clearly a case for a statement level trigger, as soon as
> >>affected rows can be identified.
> >>
> >>
> >
> >Well, I think single inserts might be more expensive (because the query is
> >more involved for the table joining case) using a statement level trigger,
> >so we'd probably want to profile the cases.
> >
> >
> This really depends. If a constraint is just a check on the
> inserted/updated column, so no other row needs to be checked, there's no
> faster way then the current row trigger. But FK constraints need to
> execute a query to retrieve the referenced row, and every RDBMS prefers
> to execute a single statement with many rows over many statements with a
> single row, because the first will profit from optimization. And even if
> only a single row is inserted or updated, there's still the need to
> lookup the reference.
I don't think that addresses the issue I brought up. If you're doing a
bunch of single inserts:
begin;
insert into foo values (1);
insert into foo values (1);
insert into foo values (1);
insert into foo values (1);
insert into foo values (1);
end;
Each of those statement triggers is still only going to be dealing with a
single row. If you're in immediate mode there's not much you can do about
that since the constraint is checked between inserts. If you're in
deferred mode, right now it won't help because it's not going to batch
them, it's going to be 5 statement triggers AFAICT each with its own 1 row
affected table.
I believe that the more complicated join the old/new table with the pk
table and do the constraint check is going to be slightly slower than the
current row behavior for such cases because the trigger query is going to
be more complicated. What would be nice would be some way to choose
whether to use a single query per statement vs a simpler query per row
based on what's happening.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 15:33 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 16:53 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 17:20 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 17:31 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 30+ messages in thread
From: Andreas Pflug @ 2003-08-15 17:20 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-hackers
Stephan Szabo wrote:
>On Fri, 15 Aug 2003, Andreas Pflug wrote:
>
>
>
>>Stephan Szabo wrote:
>>
>>
>>
>>>On Fri, 15 Aug 2003, Andreas Pflug wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Stephan Szabo wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>That really needs to be rewritten to do a single check over the table
>>>>>rather than running the constraint for every row. I keep meaning to get
>>>>>around to it and never actually do. :( I'm not sure that in practice
>>>>>you'll get a better plan at restore time depending on what the default
>>>>>statistics give you.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>This is clearly a case for a statement level trigger, as soon as
>>>>affected rows can be identified.
>>>>
>>>>
>>>>
>>>>
>>>Well, I think single inserts might be more expensive (because the query is
>>>more involved for the table joining case) using a statement level trigger,
>>>so we'd probably want to profile the cases.
>>>
>>>
>>>
>>>
>>This really depends. If a constraint is just a check on the
>>inserted/updated column, so no other row needs to be checked, there's no
>>faster way then the current row trigger. But FK constraints need to
>>execute a query to retrieve the referenced row, and every RDBMS prefers
>>to execute a single statement with many rows over many statements with a
>>single row, because the first will profit from optimization. And even if
>>only a single row is inserted or updated, there's still the need to
>>lookup the reference.
>>
>>
>
>I don't think that addresses the issue I brought up. If you're doing a
>bunch of single inserts:
>begin;
>insert into foo values (1);
>insert into foo values (1);
>insert into foo values (1);
>insert into foo values (1);
>insert into foo values (1);
>end;
>
>Each of those statement triggers is still only going to be dealing with a
>single row. If you're in immediate mode there's not much you can do about
>that since the constraint is checked between inserts. If you're in
>deferred mode, right now it won't help because it's not going to batch
>them, it's going to be 5 statement triggers AFAICT each with its own 1 row
>affected table.
>
>I believe that the more complicated join the old/new table with the pk
>table and do the constraint check is going to be slightly slower than the
>current row behavior for such cases because the trigger query is going to
>be more complicated. What would be nice would be some way to choose
>whether to use a single query per statement vs a simpler query per row
>based on what's happening.
>
>
>
Deferring the constraint check would mean checking 5 single rows, right.
But I still can't see why you think that a row level trigger would be
cheaper in this case. I had a look at ri_triggers.c and what's coded
there looks just as I expected, doing a query on the referenced table.
the queries might look a bit different when checking multiple rows at
once, but carefully designed I doubt that there would be a performance
hit from this. In case it *is* significantly slower, single row updates
could be handled separately using the current triggers, and statement
triggers for multiple rows. This would cover both scenarios best. At the
moment, update/insert scales not too good.
Best thing in the situation above would certainly be if all 5 rows would
be checked in a single query, but that looks quite impossible because a
mixture of inserts/updates/deletes on different tables might be deferred.
Regards,
Andreas
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 15:33 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 16:53 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:20 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
@ 2003-08-15 17:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:48 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 1 reply; 30+ messages in thread
From: Stephan Szabo @ 2003-08-15 17:31 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; +Cc: pgsql-hackers
On Fri, 15 Aug 2003, Andreas Pflug wrote:
> Stephan Szabo wrote:
>
> >On Fri, 15 Aug 2003, Andreas Pflug wrote:
> >
> >>Stephan Szabo wrote:
> >>
> >>>Well, I think single inserts might be more expensive (because the query is
> >>>more involved for the table joining case) using a statement level trigger,
> >>>so we'd probably want to profile the cases.
> >>>
> >>>
> >>>
> >>>
> >>This really depends. If a constraint is just a check on the
> >>inserted/updated column, so no other row needs to be checked, there's no
> >>faster way then the current row trigger. But FK constraints need to
> >>execute a query to retrieve the referenced row, and every RDBMS prefers
> >>to execute a single statement with many rows over many statements with a
> >>single row, because the first will profit from optimization. And even if
> >>only a single row is inserted or updated, there's still the need to
> >>lookup the reference.
> >>
> >>
> >
> >I don't think that addresses the issue I brought up. If you're doing a
> >bunch of single inserts:
> >begin;
> >insert into foo values (1);
> >insert into foo values (1);
> >insert into foo values (1);
> >insert into foo values (1);
> >insert into foo values (1);
> >end;
> >
> >Each of those statement triggers is still only going to be dealing with a
> >single row. If you're in immediate mode there's not much you can do about
> >that since the constraint is checked between inserts. If you're in
> >deferred mode, right now it won't help because it's not going to batch
> >them, it's going to be 5 statement triggers AFAICT each with its own 1 row
> >affected table.
> >
> >I believe that the more complicated join the old/new table with the pk
> >table and do the constraint check is going to be slightly slower than the
> >current row behavior for such cases because the trigger query is going to
> >be more complicated. What would be nice would be some way to choose
> >whether to use a single query per statement vs a simpler query per row
> >based on what's happening.
> >
>
> Deferring the constraint check would mean checking 5 single rows, right.
> But I still can't see why you think that a row level trigger would be
> cheaper in this case. I had a look at ri_triggers.c and what's coded
> there looks just as I expected, doing a query on the referenced table.
> the queries might look a bit different when checking multiple rows at
> once, but carefully designed I doubt that there would be a performance
> hit from this. In case it *is* significantly slower, single row updates
I don't know if there will be or not, but in one case it's a single table
select with constant values, in the other it's probably some kind of scan
and subselect. I'm just not going to rule out the possibility, so we
should profile it in large transactions with say 100k single inserts and
see.
> could be handled separately using the current triggers, and statement
> triggers for multiple rows. This would cover both scenarios best. At the
Yep. I'd wish that it could do it without actually needing to queue up
both triggers, but I don't know how if that'd be possible without tying
some knowledge of the fk functions deeper down.
> Best thing in the situation above would certainly be if all 5 rows would
> be checked in a single query, but that looks quite impossible because a
> mixture of inserts/updates/deletes on different tables might be deferred.
Yeah, the 5 above are pretty easy to show that it's safe, but other cases
and referential action cases won't necessarily be so easy.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Re: [GENERAL] 7.4Beta Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Re: [GENERAL] 7.4Beta Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 15:33 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 16:53 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:20 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 17:31 ` Re: [GENERAL] 7.4Beta Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-15 17:48 ` Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 0 replies; 30+ messages in thread
From: Andreas Pflug @ 2003-08-15 17:48 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-hackers
Stephan Szabo wrote:
>I don't know if there will be or not, but in one case it's a single table
>select with constant values, in the other it's probably some kind of scan
>and subselect. I'm just not going to rule out the possibility, so we
>should profile it in large transactions with say 100k single inserts and
>see.
>
>
You're talking about bulk operations, that should be handled carefully
either. Usually loading all data into a temporary table, and making a
INSERT INTO xxx SELECT FROM tmptable should give a better performance if
indices and constraints are concerned. PostgreSQL shouldn't be
considered to accept the most abusive ways of operation, but it should
offer a reasonable set of tools enabling the jobs in a convenient way.
Best situation available is if many small random transactions are
performed good, for TPC like loads, as well as bulk operations. Nobody
should expect that a database will smootly convert a bunch of single
transactions into an optimized bulk one. That's the job of a programmer.
>Yeah, the 5 above are pretty easy to show that it's safe, but other cases
>and referential action cases won't necessarily be so easy.
>
So it's the programmers responsibility to offer mass data to the
backend, not separate inserts that by chance might be handled in a
similar way. A RDBMS is not a clairvoyant.
Regards,
Andreas
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
@ 2003-08-15 18:08 Dann Corbit <DCorbit@connx.com>
2003-08-15 18:35 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 1 reply; 30+ messages in thread
From: Dann Corbit @ 2003-08-15 18:08 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-hackers
> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: Friday, August 15, 2003 10:49 AM
> To: Stephan Szabo
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] [GENERAL] 7.4Beta
>
>
> Stephan Szabo wrote:
>
> >I don't know if there will be or not, but in one case it's a single
> >table select with constant values, in the other it's
> probably some kind
> >of scan and subselect. I'm just not going to rule out the
> possibility,
> >so we should profile it in large transactions with say 100k single
> >inserts and see.
> >
> >
> You're talking about bulk operations, that should be handled
> carefully
> either. Usually loading all data into a temporary table, and making a
> INSERT INTO xxx SELECT FROM tmptable should give a better
> performance if
> indices and constraints are concerned. PostgreSQL shouldn't be
> considered to accept the most abusive ways of operation, but
> it should
> offer a reasonable set of tools enabling the jobs in a
> convenient way.
> Best situation available is if many small random transactions are
> performed good, for TPC like loads, as well as bulk
> operations. Nobody
> should expect that a database will smootly convert a bunch of single
> transactions into an optimized bulk one. That's the job of a
> programmer.
>
> >Yeah, the 5 above are pretty easy to show that it's safe, but other
> >cases and referential action cases won't necessarily be so easy.
> >
> So it's the programmers responsibility to offer mass data to the
> backend, not separate inserts that by chance might be handled in a
> similar way. A RDBMS is not a clairvoyant.
Simplification of bulk operations can be very important for customers
(on the other hand). For the CONNX tool set, we offer an escape on
INSERT/SELECT that performs the operation in bulk mode.
There are serious downsides to bulk operations also (such as not being
logged and therefore existing outside of a transaction). Therefore,
they are useful really only in cases where a complete destruction and
repopulation is called for. If anything goes haywire, you can't simply
roll it back.
Yet the speed savings can be enormous (orders of magnitude).
Compared to iteration over a set of prepared inserts, a bulk insert
(such as using Microsoft's BCP API or Oracles Direct Path loading) can
be 100 times faster. If you are moving gigabytes of data and performing
a complete refresh, the method to use becomes obvious.
When we go outside of the language bounds, a curly braced escape
notation is used. For instance, an insert/select might look like this:
INSERT INTO <destination_table> SELECT <column_list> FROM <source_table>
{fn commitcount 1000} {bulkmode}
The commit count says to use batches of 1000 rows and bulkmode says to
use the fastest possible insert method.
Imagine (for instance) that PostgreSQL can use an ODBC {or similar} data
source as a table. Then, with a syntax such as the above (or SELECT
INTO etc.), you could very rapidly move data from one system into
another.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-15 18:08 Re: [GENERAL] 7.4Beta Dann Corbit <DCorbit@connx.com>
@ 2003-08-15 18:35 ` Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 0 replies; 30+ messages in thread
From: Andreas Pflug @ 2003-08-15 18:35 UTC (permalink / raw)
To: Dann Corbit <DCorbit@connx.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-hackers
Dann Corbit wrote:
>Simplification of bulk operations can be very important for customers
>(on the other hand). For the CONNX tool set, we offer an escape on
>INSERT/SELECT that performs the operation in bulk mode.
>
>There are serious downsides to bulk operations also (such as not being
>logged and therefore existing outside of a transaction). Therefore,
>they are useful really only in cases where a complete destruction and
>repopulation is called for. If anything goes haywire, you can't simply
>roll it back.
>
>Yet the speed savings can be enormous (orders of magnitude).
>
>Compared to iteration over a set of prepared inserts, a bulk insert
>(such as using Microsoft's BCP API or Oracles Direct Path loading) can
>be 100 times faster. If you are moving gigabytes of data and performing
>a complete refresh, the method to use becomes obvious.
>
>When we go outside of the language bounds, a curly braced escape
>notation is used. For instance, an insert/select might look like this:
>INSERT INTO <destination_table> SELECT <column_list> FROM <source_table>
>{fn commitcount 1000} {bulkmode}
>The commit count says to use batches of 1000 rows and bulkmode says to
>use the fastest possible insert method.
>
>Imagine (for instance) that PostgreSQL can use an ODBC {or similar} data
>source as a table. Then, with a syntax such as the above (or SELECT
>INTO etc.), you could very rapidly move data from one system into
>another.
>
>
When saying "bulk operation" I don't necessarily mean using bulk load or
stuff like that. What I mean is handling large amounts of similar data
at the same time. That doesn't say anything about transactions or
logging problems.
Imagine you have 100k or rows to load, each having FKs to (hopefully)
existing rows
- Step 1: load the rows into a temp table
- Step 2: identify duplicates, logging and deleting them
- Step 3: insert all data satisfying the FK constraints.
- Step 4: log all that didn't insert.
This are relatively few statements (not the simplest), which can be
handled in a safe manner.
Just an example, how a RDBMS can do the job in a mass oriented (and thus
optimizable) way.
Regards,
Andreas
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
@ 2003-08-15 18:53 Dann Corbit <DCorbit@connx.com>
2003-08-15 19:13 ` Re: [GENERAL] 7.4Beta Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 1 reply; 30+ messages in thread
From: Dann Corbit @ 2003-08-15 18:53 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-hackers
> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: Friday, August 15, 2003 11:36 AM
> To: Dann Corbit
> Cc: Stephan Szabo; PostgreSQL-development
> Subject: Re: [HACKERS] [GENERAL] 7.4Beta
>
> Dann Corbit wrote:
>
> >Simplification of bulk operations can be very important for
> customers
> >(on the other hand). For the CONNX tool set, we offer an escape on
> >INSERT/SELECT that performs the operation in bulk mode.
> >
> >There are serious downsides to bulk operations also (such as
> not being
> >logged and therefore existing outside of a transaction). Therefore,
> >they are useful really only in cases where a complete
> destruction and
> >repopulation is called for. If anything goes haywire, you
> can't simply
> >roll it back.
> >
> >Yet the speed savings can be enormous (orders of magnitude).
> >
> >Compared to iteration over a set of prepared inserts, a bulk insert
> >(such as using Microsoft's BCP API or Oracles Direct Path
> loading) can
> >be 100 times faster. If you are moving gigabytes of data and
> >performing a complete refresh, the method to use becomes obvious.
> >
> >When we go outside of the language bounds, a curly braced escape
> >notation is used. For instance, an insert/select might look
> like this:
> >INSERT INTO <destination_table> SELECT <column_list> FROM
> ><source_table> {fn commitcount 1000} {bulkmode} The commit
> count says
> >to use batches of 1000 rows and bulkmode says to use the fastest
> >possible insert method.
> >
> >Imagine (for instance) that PostgreSQL can use an ODBC {or similar}
> >data source as a table. Then, with a syntax such as the above (or
> >SELECT INTO etc.), you could very rapidly move data from one system
> >into another.
> >
> >
> When saying "bulk operation" I don't necessarily mean using
> bulk load or
> stuff like that. What I mean is handling large amounts of
> similar data
> at the same time. That doesn't say anything about transactions or
> logging problems.
> Imagine you have 100k or rows to load, each having FKs to (hopefully)
> existing rows
> - Step 1: load the rows into a temp table
> - Step 2: identify duplicates, logging and deleting them
> - Step 3: insert all data satisfying the FK constraints.
> - Step 4: log all that didn't insert.
>
> This are relatively few statements (not the simplest), which can be
> handled in a safe manner.
> Just an example, how a RDBMS can do the job in a mass
> oriented (and thus
> optimizable) way.
I really, really like SQL*Server's solution to the problem you have
stated above. A typical example is you have a pile of incoming words
and you want to create a dictionary. An example might be a library
database, where you have an abstract for each item. You parse the words
and look for new ones to add.
What SQL*Server allows is an index with an option called "IGNORE
DUPLICATES" that simply tosses out rows that are already in the table.
For applications like what I have described and what you have described
it is an incredibly useful extension. Once I got used to it, I found
myself using it all the time. Of course, you must be very careful to
ensure that the duplicates really are completely unimportant.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: [GENERAL] 7.4Beta
2003-08-15 18:53 Re: [GENERAL] 7.4Beta Dann Corbit <DCorbit@connx.com>
@ 2003-08-15 19:13 ` Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 0 replies; 30+ messages in thread
From: Andreas Pflug @ 2003-08-15 19:13 UTC (permalink / raw)
To: Dann Corbit <DCorbit@connx.com>; +Cc: pgsql-hackers
Dann Corbit wrote:
>>-----Original Message-----
>>From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
>>Sent: Friday, August 15, 2003 11:36 AM
>>To: Dann Corbit
>>Cc: Stephan Szabo; PostgreSQL-development
>>Subject: Re: [HACKERS] [GENERAL] 7.4Beta
>>
>>Dann Corbit wrote:
>>
>>
>>
>>>Simplification of bulk operations can be very important for
>>>
>>>
>>customers
>>
>>
>>>(on the other hand). For the CONNX tool set, we offer an escape on
>>>INSERT/SELECT that performs the operation in bulk mode.
>>>
>>>There are serious downsides to bulk operations also (such as
>>>
>>>
>>not being
>>
>>
>>>logged and therefore existing outside of a transaction). Therefore,
>>>they are useful really only in cases where a complete
>>>
>>>
>>destruction and
>>
>>
>>>repopulation is called for. If anything goes haywire, you
>>>
>>>
>>can't simply
>>
>>
>>>roll it back.
>>>
>>>Yet the speed savings can be enormous (orders of magnitude).
>>>
>>>Compared to iteration over a set of prepared inserts, a bulk insert
>>>(such as using Microsoft's BCP API or Oracles Direct Path
>>>
>>>
>>loading) can
>>
>>
>>>be 100 times faster. If you are moving gigabytes of data and
>>>performing a complete refresh, the method to use becomes obvious.
>>>
>>>When we go outside of the language bounds, a curly braced escape
>>>notation is used. For instance, an insert/select might look
>>>
>>>
>>like this:
>>
>>
>>>INSERT INTO <destination_table> SELECT <column_list> FROM
>>><source_table> {fn commitcount 1000} {bulkmode} The commit
>>>
>>>
>>count says
>>
>>
>>>to use batches of 1000 rows and bulkmode says to use the fastest
>>>possible insert method.
>>>
>>>Imagine (for instance) that PostgreSQL can use an ODBC {or similar}
>>>data source as a table. Then, with a syntax such as the above (or
>>>SELECT INTO etc.), you could very rapidly move data from one system
>>>into another.
>>>
>>>
>>>
>>>
>>When saying "bulk operation" I don't necessarily mean using
>>bulk load or
>>stuff like that. What I mean is handling large amounts of
>>similar data
>>at the same time. That doesn't say anything about transactions or
>>logging problems.
>>Imagine you have 100k or rows to load, each having FKs to (hopefully)
>>existing rows
>>- Step 1: load the rows into a temp table
>>- Step 2: identify duplicates, logging and deleting them
>>- Step 3: insert all data satisfying the FK constraints.
>>- Step 4: log all that didn't insert.
>>
>>This are relatively few statements (not the simplest), which can be
>>handled in a safe manner.
>>Just an example, how a RDBMS can do the job in a mass
>>oriented (and thus
>>optimizable) way.
>>
>>
>
>I really, really like SQL*Server's solution to the problem you have
>stated above. A typical example is you have a pile of incoming words
>and you want to create a dictionary. An example might be a library
>database, where you have an abstract for each item. You parse the words
>and look for new ones to add.
>
>What SQL*Server allows is an index with an option called "IGNORE
>DUPLICATES" that simply tosses out rows that are already in the table.
>For applications like what I have described and what you have described
>it is an incredibly useful extension. Once I got used to it, I found
>myself using it all the time. Of course, you must be very careful to
>ensure that the duplicates really are completely unimportant.
>
>
Hm, option is quite a quick hack. I like logging in the database much
better than some sql messages. And it's only about duplicates, not about
fk constraint violations.
Regards,
Andresa
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2003-08-16 04:27 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-14 18:51 7.4Beta Peter Childs <Blue.Dragon@blueyonder.co.uk>
2003-08-14 22:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-15 02:14 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 05:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 05:46 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 11:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 14:35 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:13 ` Jan Wieck <JanWieck@Yahoo.com>
2003-08-15 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-15 22:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-16 04:27 ` Jan Wieck <JanWieck@Yahoo.com>
2003-08-15 06:05 ` Gavin Sherry <swm@linuxworld.com.au>
2003-08-15 06:16 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 06:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:44 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 06:58 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 07:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-15 07:33 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 09:17 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 14:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 15:33 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 16:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:20 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 17:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 17:48 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 18:08 Re: [GENERAL] 7.4Beta Dann Corbit <DCorbit@connx.com>
2003-08-15 18:35 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-08-15 18:53 Re: [GENERAL] 7.4Beta Dann Corbit <DCorbit@connx.com>
2003-08-15 19:13 ` Andreas Pflug <pgadmin@pse-consulting.de>
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