Re: [GENERAL] Config for fast huge cascaded updates

[Craig de Stigter]

Thanks everyone. Sorry for the late reply.


Do you have indexes on all the referencing columns?


I had thought so, but it turns out no, and this appears to be the main
cause of the slowness. After adding a couple of extra indexes in the bigger
tables, things are going much more smoothly.


write the whole thing into a new SQL schema


This is a really interesting approach I hadn't thought of! We can currently
afford a little bit of downtime, but it's helpful to keep this in mind if
we ever do this kind of thing again in future.

The two changes we've made are:

   - Add a few indexes so that the cascades operate more efficiently
   - Move some of the tables (whose ID values don't matter so much to our
   app) into a separate migration, which can be run before we take down the
   site. Then only the tables whose IDs matter to the app/user are done while
   the site is down.

With those changes it looks like we can fit the downtime into the window we
have. Thanks for all the advice, much appreciated!


On 28 June 2017 at 01:28, Andrew Sullivan  wrote:

> On Mon, Jun 26, 2017 at 07:26:08PM -0700, Joshua D. Drake wrote:
>
> > Alternatively, and ONLY do this if you take a backup right before hand,
> you
> > can set the table unlogged, make the changes and assuming success, make
> the
> > table logged again. That will great increase the write speed and reduce
> wal
> > segment churn.
>
> Note that this is not for just that table, but for all of the
> implicated ones because of the CASCADE statements.  It sounds like the
> OP is basically rewriting a significant chunk of the entire database,
> so nothing is going to be super fast: all those CASCADEs have to fire
> and all those other tables need to be updated too.
>
> > However, if that fails, the table is dead. You will have to reload it
> from
> > backup.
>
> Right, and that goes for all the affected tables.
>
> Best regards,
>
> A
>
> --
> Andrew Sullivan
> a...@crankycanuck.ca
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Regards,
Craig

Developer
Koordinates

+64 21 256 9488 <+64%2021%20256%209488> / koordinates.com / @koordinates

[GENERAL] Config for fast huge cascaded updates

[Craig de Stigter]

Hi folks

We're doing a large migration on our site which involves changing most of
the primary key values. We've noticed this is a *very* slow process.

Firstly we've set up all the foreign keys to use `on update cascade`. Then
we essentially do this on every table:

UPDATE TABLE users SET id = id + 100;


Since this cascades via about 40 foreign keys to most of the other tables
in the database, this update on our fairly small table takes about five
hours.

This is understandable (it's rewriting most of the database) but what
settings can we tweak to make this process faster?

So far we have experimented with the following:

   - checkpoint_timeout : 3600
   - autovacuum: 0
   - max_wal_size: 128 (2GB)
   - synchronous_commit: off

What other things would you recommend to improve performance of this sort
of thing?


-- 
Regards,
Craig

Developer
Koordinates

+64 21 256 9488 <+64%2021%20256%209488> / koordinates.com / @koordinates

[GENERAL] dump restore to different schema

[Craig de Stigter]

Hi list

We're writing a plugin for our website that loads single-table database
dumps created by untrusted users. My question is two-fold:

1. I'm assuming that the dump format can contain arbitrary sql commands, so
a pg_restore of this nature should be run under an untrusted account in its
own restricted schema. Can someone confirm that this is the case?

2. The dump is presumed to contain exactly one table, which I want to load
into my restricted schema. But it seems pg_restore has no option to set the
schema during restoration. It always loads into the original schema as
stored in the dump. How can I get around this?

I would prefer not to use s/^SET search_path.*$/SET search_path TO
untrusted_schema/g if I can avoid it ;)

Thanks
Craig de Stigter

-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

Re: [GENERAL] Corrupt indices on already-dropped table (could not open relation with OID ...)

[Craig de Stigter]

 What PG version is this exactly?  Do you have any idea how you got into
 this state?


Using PostgreSQL 8.3.7-0ubuntu8.10.1  from the Intrepid repository.
version() is PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.2-1ubuntu11) 4.3.2

No database crashes or system restarts were involved. We accidentally had
two transactions open which were writing new rows to the table and then
creating the same indices. One failed with a 'could not open relation with
OID X' error and the other continued but failed for some unrelated reason.
When we dropped the table and tried to recreate the table we noticed the
indices were still there.


I don't see a reason to beterribly concerned about the consistency of the
 entries about this index.


The only issue is that we do want to be able to create that table again...

Thanks a bunch
Craig de Stigter
-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

[GENERAL] Corrupt indices on already-dropped table (could not open relation with OID ...)

[Craig de Stigter]

Hi folks

Somehow we've ended up with a few corrupt indices in our database. We've
previously dropped the table they were on, but the indices are still there
(kind of):


Trying to drop the indices gives us:

drop index v_038e_GEOMETRY;

ERROR:  could not open relation with OID 9590980



Looking up that index in pg_class:

select oid, relname from pg_class where relname = 'v_038e_GEOMETRY';

   oid   |   relname

-+-

 9590993 | v_038e_GEOMETRY



And looking up those OIDs in pg_depend:

select * from pg_depend where objid = 9590993;

 classid |  objid  | objsubid | refclassid | refobjid | refobjsubid |
 deptype

-+-+--++--+-+-

1259 | 9590993 |0 |   1259 |  9590980 |   3 | a

1259 | 9590993 |0 |   2616 |20506 |   0 | n



But that table doesn't exist anymore (that's okay, we dropped it earlier):

select * from pg_class where oid = 9590980 or relname = 'v_038e';

(0 rows)



Restarting the database didn't help, unfortunately.

I'm a bit hesitant to try the fix mentioned at the following URL since it
involves deleting things from system tables:
http://javadave.blogspot.com/2005/06/could-not-open-relation-in-postgresql.html


Any suggestions for a nicer approach? Or can someone who knows tell me if
its okay to follow the instructions at that url, without breaking anything?

Thanks

Craig de Stigter

-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

Re: [GENERAL] pg_stats.avg_width differs by a factor of 4 on different machines

[Craig de Stigter]

This query was giving us good-enough results on our old system. The
estimates don't have to be absolutely accurate, just ballpark figures.
Also we are estimating the size of zipped shapefiles, not textual
geometries.

Our tests show that such sizes are quite accurate for medium/large
datasets when we multiply the avg_width by a precalculated constant
factor.

Does anyone have any idea why these numbers would be 4 times as big in
Postgres 8.3.7 ?

If not I may end up with a dirty hack along the lines of:
if POSTGRES_VERSION = '8.3':
size_estimate /= 4.0

;)

On 5/29/09, Tom Lane t...@sss.pgh.pa.us wrote:
 Craig de Stigter craig.destig...@koordinates.com writes:
 We are using the PostgreSQL pg_stats view to estimate file sizes for some
 geodata exports. However, the following query gives us totally different
 results on different servers:

 select avg_width from pg_stats where tablename='some_geodata' and attname
 =
 'GEOMETRY';

 I'm afraid that query is pretty much completely useless for what you
 want to do.  What it should be giving you is the average width of the
 field values on-disk, which is to say after compression and toasting.
 It would probably be all right for narrow columns but it's likely to be
 a huge underestimate of the external textual size for wide field values.

 Having said that, though, these numbers make no sense to me:

 PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
 (Ubuntu
 4.3.2-1ubuntu11) 4.3.2
 81803

 PostgreSQL 8.2.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
 (Ubuntu 4.1.2-0ubuntu4)
 20450

 It should be impossible to get a value larger than the block size, or
 even more than about a quarter of the block size because that's where
 TOAST will start doing its thing.  Are you running modified source code?

   regards, tom lane



-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

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

[GENERAL] pg_stats.avg_width differs by a factor of 4 on different machines

[Craig de Stigter]

Hi list

We are using the PostgreSQL pg_stats view to estimate file sizes for some
geodata exports. However, the following query gives us totally different
results on different servers:

select avg_width from pg_stats where tablename='some_geodata' and attname =
 'GEOMETRY';


PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu
4.3.2-1ubuntu11) 4.3.2

 81803


PostgreSQL 8.2.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)

 20450


Both tables have had VACUUM FULL ANALYZE run on them and have identical
data. Note that 81803 is almost exactly 4x20450, though I don't know what
significance this has. x64/i386 makes no difference.

I couldn't find anything in the 8.3 release notes that looked relevant. Any
help appreciated.

Regards
Craig de Stigter

-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com