pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbulk load performance question
2+ messages / 2 participants
[nested] [flat]
* bulk load performance question
@ 2010-10-12 21:16 Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 2+ messages in thread
From: Samuel Gendler @ 2010-10-12 21:16 UTC (permalink / raw)
To: pgsql-performance
I've got a few tables that periodically get entirely refreshed via a COPY. I
don't really have a good mechanism for detecting only rows which have
changed so even though the differences are small, a full copy is easiest.
However, the data includes a primary key column, so I can't simply load
into the existing table and then drop older rows. So we load into a table
with a different name and then, within a transaction, drop the old and
rename the new. However, while the transaction will cause a query against
that table to block until the transaction commits, when the transaction
commits, the blocked query will fail with an error message like: ERROR:
could not open relation with OID 17556
Is there some way to do the drop+rename in a manner which will preserve the
OID or otherwise allow blocked queries to execute correctly once they
unblock?
A secondary issue is that if permissions were granted to a role on the old
table, the new table does not acquire those permissions and they must be
granted again.
The biggest table that gets updated like this is a couple hundred thousand
rows, with maybe a few thousand rows actually changing or being added with
each load. Suggestions for alternative mechanisms for doing the loading are
welcome. I'd really rather avoid updating every row in a several hundred
thousand row table, especially without easy upsert functionality. The data
is small enough that selecting everything and then comparing in memory
before updating modified rows is doable, but sure seems like a lot of work
if it can be avoided.
Writing this caused me to think of a possible solution, which appears to
work correctly, but I'd like to confirm it with folks in the know:
Instead of this:
CREATE TABLE mytable_temp...;
COPY INTO mytable_temp...;
BEGIN;
DROP TABLE mytable;
ALTER TABLE mytable_temp RENAME TO mytable;
COMMIT;
Which will cause any overlapping queries to pick up the wrong OID for
mytable and then fail when the transaction commits, I tested this:
COPY INTO mytable_temp;
BEGIN;
ALTER TABLE mytable RENAME TO mytable_old;
ALTER TABLE mytable_temp RENAME TO mytable;
COMMIT;
DROP TABLE mytable_old;
It would appear that any query that uses mytable which overlaps with the
transaction will pick up the OID of the original mytable and then block
until the transaction commits. WHen the transaction commits, those queries
will successfully run against the original OID (no queries write to this
table except for the bulk load) and will complete, at which time, the table
drop will finally complete. Meanwhile, any queries which don't overlap (or
perhaps any queries which start after the rename from mytable_temp to
mytable has occurred) will successfully complete against the new table.
The net result appears to be that I will no longer suffer the missing OID
error, which seemed to periodically completely hose a db connection,
requiring that the connection be closed since no subequent queries would
ever succeed, whether they touched the table in question or not. I've only
seen that erroneous behaviour on 8.3 (so far - we only recently upgraded to
8.4.4), but it was fairly mysterious because I've never been able to
replicate it in testing. I could get a single missing OID error, but never
one that would break all subsequent queries.
Are my assumptions about this correct?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: bulk load performance question
@ 2010-10-12 21:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-10-12 21:22 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
Samuel Gendler <sgendler@ideasculptor.com> writes:
> Is there some way to do the drop+rename in a manner which will preserve the
> OID or otherwise allow blocked queries to execute correctly once they
> unblock?
No, but you could consider
begin;
truncate original_table;
insert into original_table select * from new_data;
commit;
> A secondary issue is that if permissions were granted to a role on the old
> table, the new table does not acquire those permissions and they must be
> granted again.
Not to mention foreign keys ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-12 21:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-12 21:16 bulk load performance question Samuel Gendler <sgendler@ideasculptor.com>
2010-10-12 21:22 ` Tom Lane <tgl@sss.pgh.pa.us>
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