ous wrt spelling
>
> 3. there other good reasons, that I've forgotten for now :-(
> (but I remember having them!!!)
Oh, so you name your tables 'fish' and 'crow' instead of 'school' and
'murder' ? Such wasted opportunities :p
On Tuesday 09 December 2014 16:56:39 Tom Lane wrote:
> Vincent de Phily writes:
> > It reads about 8G of the table (often doing a similar number of writes,
> > but
> > not always), then starts reading the pkey index and the second index (only
> > 2 indexes on this t
On Tuesday 09 December 2014 01:58:11 Vincent de Phily wrote:
> On Monday 08 December 2014 10:17:37 Jeff Janes wrote:
> > You can `strace` for the lseek command to see which file handles it is
> > currently working on, and
> > use lsof to turn those into names. You want to l
On Monday 08 December 2014 10:17:37 Jeff Janes wrote:
> On Mon, Dec 8, 2014 at 4:54 AM, Vincent de Phily <
>
> > bloat, which I'd like to get back asap). Currently about 80% of the IO is
> > devoted to the vacuum process (on average throughout the day, as
> > e
ctions in that timespan (that'd need
890 transactions per second, we're well below 100). The pg_class.relfozenxid
on that table is at 680M, while most other tables are around 860M. Could it be
that routine vacuums haven't been able to update the relfrozenxid in a long
long time, o
e :/ The refactoring
had to do with taking status queries to a different connection, and changing
the timing of calling the problematic query and interruption by other threads,
to increase throughput.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Friday 23 May 2014 15:12:47 Chris Ernst wrote:
> On 05/23/2014 08:57 AM, Vincent de Phily wrote:
> > I need to reduce downtime to a minimum, so I can't afford to let "alter
> > table set tablespace" take an exclusive lock on the table for the 2h
> > it'
ackup, rsync, and catalog update (which, if it was that simple,
would be great to have in core as a 'set tablespace concurrently' option) ?
Thanks.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ly seeing your answer now, it helps if you cc me when
answering the list.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Friday 09 May 2014 16:53:49 Adrian Klaver wrote:
> On 05/09/2014 08:04 AM, Vincent de Phily wrote:
> > Thanks to all for taking an interest so far, this bug is... weird.
>
> Yes weird. I tried to replicate your query to see if I could see
> anything similar. Tried it on 9.
On Friday 09 May 2014 08:01:47 Adrian Klaver wrote:
> On 05/09/2014 01:45 AM, Vincent de Phily wrote:
> > Before you grow suspicious of that conf_getint, the config is loaded once
> > at program startup, and the overlarge results hapen together with normal
> > results without
On Friday 09 May 2014 07:01:32 Tom Lane wrote:
> Vincent de Phily writes:
> > In case it changes anything, this is the uncut (but still anonimized)
> >
> > function:
> > query = """UPDATE foo SET processing = 't' WHERE id IN
> >
On Thursday 08 May 2014 16:56:25 Adrian Klaver wrote:
> On 05/08/2014 03:11 PM, Vincent de Phily wrote:
> > On Thursday 08 May 2014 06:30:39 Adrian Klaver wrote:
> >> On 05/08/2014 04:09 AM, Vincent de Phily wrote:
> > The histogram shows a large amount of small values
On Thursday 08 May 2014 16:05:59 Sim Zacks wrote:
> On 05/08/2014 02:09 PM, Vincent de Phily wrote:
> The problem is that sometimes (once every few days at about 2-300K queries
> per day) I get many more rows than the max 5000 I asked for (I've seen up
> to 25k). And I'm gett
On Thursday 08 May 2014 06:30:39 Adrian Klaver wrote:
> On 05/08/2014 04:09 AM, Vincent de Phily wrote:
> > Hello,
> >
> > I'm processing records in batches using PG 9.1.12, python 2.7, and psycopg
>
> > 2.5.2 :
> Comments in the code below:
uts and other problems as a result.
The id column is your typical primary key integer with a unique index. I've
checked the problematic cases and there are no id gaps or duplicate rows.
There are multiple threads in the program, but only the main thread is running
enqueue_loop(). I'm not
7.
>
> Unfortunately, the complainant is running 9.1.2.
Thanks for the detailed info, great to see it's already fixed. As it happens,
we're in the process of moving to new servers, where we'll strive to get a
better update process going.
--
Vincent de Phily
--
Sent via
On Monday 17 February 2014 18:46:49 Vincent de Phily wrote:
> Hi List,
>
> I have droped a database a few hours ago to reclaim some badly-needed space,
> but that disk space has not been freed yet. Investigating further, there
> are lots of deleted but open files that seem to co
979716 (deleted)
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sunday 06 May 2012 10:29:17 Simon Riggs wrote:
> On 4 May 2012 14:55, Vincent de Phily
wrote:
> > It all seems to be working fine, except that when checking the data
> > (selecting latest primary key and sequence value for all tables) on
> > master and slave, some seque
On Friday 04 May 2012 09:47:16 Merlin Moncure wrote:
> On Fri, May 4, 2012 at 8:55 AM, Vincent de Phily
>
> wrote:
> > Hi list,
> >
> > we have two 9.1.2 servers on debian squeeze, and are setting up a simple
> > streaming replication between the two.
> >
d the base backup twice
So...
* any likely mistake on our side ?
* can it be fixed ?
* is this harmless and to be ignored ?
Thank you.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mail
-+---+--
ascii\xdeadbeef | text | \x6173636969deadbeef
I would have expected a result cast as bytea or an error message telling me
about incompatible types, but the result from the first column is a nasty
gotcha. Is it the intented behaviour ?
--
Vincent de Phily
On Wednesday 29 February 2012 14:14:19 Tom Lane wrote:
> "David Johnston" writes:
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vincent de Phily
> >
> >> [ this doesn't do anything: ]
> >> db_foo=# REVOKE CREATE ON SCHEMA public F
As an aside, is there any better way to check existing privileges ? I need to
call the has_*_privilege() function for each privilege type to get an exact
view, which is cumbersome. I was using pg_class.relacl before, but it doesn't
support all object types and is not an official interface. Some of the tables
in information_schema look ideal, but again not all object types are covered.
Thanks in advance.
--
Vincent de Phily
indexes however. Is
there a functional difference between a unique index and a primary key index
(knowing that my column is not null) ? Or is it just for documentation and ORM
purposes ?
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
On Thursday 06 October 2011 12:09:30 Andrew Sullivan wrote:
> On Thu, Oct 06, 2011 at 03:39:02PM +0200, Vincent de Phily wrote:
> > In the case of vacuuming however, I think there's a point to be made
> > about finishing fast when all vacuum workers are constantly busy : say
&
On Thursday 06 October 2011 07:00:20 Andrew Sullivan wrote:
> On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote:
> > Fearing that vacuuming might accumulate lateness and hoping to see the
> > system idle every now and then,
>
> Why is your goal to see the syst
centage of the table remains static between vacuums), but
we're still too busy for that right now (unless you tell me I'm going to see a
night-and-day difference regarding this particular issue).
Thanks.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresq
spoted that in the changelogs, and haven't upgraded yet. Great :)
Yet another reason to upgrade ASAP. Thanks.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
t reallity is 100 times
slower. Is there a way I can extend explain output to show the rest of the
work done behind the scene ? Fixing that performance issue is easy once you
see it, but most people will just look at the explain output and erroneously
conclude "it's as good as it gets"
7;) instead of using it directly ? Unless your
function does more than "select nextval('XXX')", you're just making your code
harder to read and slightly slower.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
INTO last_values (...) VALUES (NEW);
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
UPDATE last_values SET ... WHERE ...;
END;
END IF;
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER last_values_trigger AFTER INSERT ON values FOR EACH ROW EXECUTE
PROCEDURE insert_la
a look at
http://pgfoundry.org/projects/pg-comparator/
which can give a more nuanced view of db differences and tries to be smart
about performance. It looks a bit stale; I haven't used it in ages, but it
used to be a trusty part of our test suite.
--
Vincent de Phily
--
Sen
On Friday 19 August 2011 12:55:01 Tom Lane wrote:
> Vincent de Phily writes:
> > On Friday 19 August 2011 11:52:50 Tom Lane wrote:
> >> IIRC, there are fields of pg_constraint that are copied into the
> >> pg_trigger rows for the supporting triggers, so as to save one
On Friday 19 August 2011 11:52:50 Tom Lane wrote:
> Vincent de Phily writes:
> > Thanks for your answer. Experimenting a bit, those columns seem to have
> > only a cosmetic impact, meaning that "\d" will show the schema you
> > expect, but the behaviour remains u
On Thursday 18 August 2011 13:08:18 Jerry Sievers wrote:
> Vincent de Phily writes:
> > The thing is, I know there is no violation by existing data, because of
> > the existing fkey. So locking and scaning the table to add the
> > "duplicate" fkey is>
> >
ALTER CONSTRAINT foo_barid_fkey ON UPDATE RESTRICT;
I'm guessing/wishfull-thinking that some hackery with the system catalog could
emulate that ?
I'm currently using postgres 8.3 (we want to upgrade, but it's hard to
schedule).
Thanks in advance.
--
Vincent de Phily
--
Sent
dividual queries.
http://munin.projects.linpro.no/
http://pgfouine.projects.postgresql.org/
http://www.postgresql.org/docs/current/static/sql-explain.html
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://ww
ed around the initial problem by running the
delete in smaller batches.
Thanks.
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
k-around for you might be to break your deletes up
> into smaller chunks, say 100k or 1M rows at a time, eg:
>
> delete from t1 where t1id in (select t1id from t2 where foo=0 and bar
> < '20101101' limit 10);
Yes, that's what we ended up doing. We
.
We haven't tweaked effective cache size yet, it's on the TODO... like many
other things :/
--
Vincent de Phily
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
=0.00..52.38 rows=1
> >width=10)
> >
> > Index Cond: (t1.t1id = t2.t1id)
> >
> > (7 rows)
> >
> >
> > Note that the estimate of 30849 rows is way off : there should be
> > around 55M rows deleted from t1, and 2-3 times as
ut differences.
Sorry it's a vast subject, and what you would be surprised by depends on what
you expect. Start here :
http://www.postgresql.org/docs/9.0/static/xfunc.html
The postgres docs are well written; read them at your leisure like a book,
follow the links where curiosity leads you, i
o.
Stored procedures will remain. Note that "stored procedures" in postgres are a
bit different from what you may be used to in other dbs; while I assure you
it's for the better, you might want to RTFM to avoid surprises.
--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353
sed / timed out.
Another thing you could do (but I'm not sure it is a good idea) is to write a
proxy application that runs on the server machine and understands your session
requirements. Then connect your application to this proxy instead of the
database.
--
Vincent de Phily
Mobile Devices
,data from idmap left join realdata
Depending on your insert/export ratio this might be more efficient. And of
course you can't delete rows 6 months later, but you knew that :p
--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320
Warning
This message (and a
47 matches
Mail list logo
