Re: [GENERAL] PostgreSQL Developer Best Practices
On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: > Actually I would suggest standardising on singular names, not JUST > because that this the standard I prefer! :-) > > But (also) because: > > 1. Singular words tend to be shorter > > 2. plurals are more ambiguous 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 -- 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
Re: [GENERAL] Speeding up an in-progress wraparound-preventing vacuum
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 table), reading both of them fully (some writes as > > well, but not as many as for the table), which takes around 8h. > > > > And the cycle apparently repeats: process a few more GB of the table, then > > go reprocess both indexes fully. A rough estimate is that it spends ~6x > > more time (re)processing the indexes as it does processing the table > > (looking at data size alone the ratio would be 41x, but the indexes go > > faster). I'm probably lucky to only have two indexes on this table. > > > > Is that the expected behaviour ? > > Yes. It can only remember so many dead tuples at a time, and it has > to go clean the indexes when the dead-TIDs buffer fills up. Fair enough. And I guess it scans the whole index each time because the dead tuples are spread all over ? What happens when vacuum is killed before it had time to go though the index with its dead-TID buffer ? Surely the index isn't irreversibly bloated; and whatever is done then could be done in the normal case ? It still feels like a lot of wasted IO. > You could > increase maintenance_work_mem to increase the size of that buffer. Will do, 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
Re: [GENERAL] Speeding up an in-progress wraparound-preventing vacuum
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 look at where it is in the > > table files, not the index files. > > Neat trick, thanks. I'll try it in the morning when I'm fresh. Are the files > read in C-sorting order ? I did this (stracing the first 1000 read() and write() every 30s) and kept an eye on progress throughout the day. It follows a (to me) surprising pattern, which looks unnecessarily time-consuming : 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 table), reading both of them fully (some writes as well, but not as many as for the table), which takes around 8h. And the cycle apparently repeats: process a few more GB of the table, then go reprocess both indexes fully. A rough estimate is that it spends ~6x more time (re)processing the indexes as it does processing the table (looking at data size alone the ratio would be 41x, but the indexes go faster). I'm probably lucky to only have two indexes on this table. Is that the expected behaviour ? Why ? I can imagine that it skips some pages and needs to go back, but then it should only do this once at the end of the process, or it should only revisit a handfull of pages. Is that something that can be improved by throwing more maintenance_work_mem at it ? -- 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
Re: [GENERAL] Speeding up an in-progress wraparound-preventing vacuum
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
> > extrapolated
> > from atop output).
>
> Is that 80% of the actually occurring IO, or 80% of the maximum possible IO?
80% of max possible IO, although I'm not sure how atop determines what the max
is. It's a fairly reliable metric of "the drive is a busy as can be" in my
experience.
> Increasing maintenance_work_mem even further, at least temporarily and
> locally for this operation, might be a good idea.
Ok, I thought 512M was already overgenerous, but I'll try increasing that too.
> > My first question is: is there a way to speedup the currently-running
> > vacuum
> > process ? I guess killing it to let it be replaced by a more agressively-
> > configured one would work, but I'd hate to lose 2 weeks of processing (is
> > there a way to estimate how much more vacuuming work remains to be done
> > ?),
> > and I'm being a bit more cautious with the wraparound-preventing kind.
>
> I don't know of a way to speed it up gracefully. That has frustrated me a
> few times, and a solution would really be nice.
>
> If you kill it, the new process will have to re-read the entire table, but
> it will have much less work to do since the killed process already 'plowed
> a path' for it. In a sense, killing the process will throw away all of the
> sequential read work on the table that has already been done, but the index
> reads and the writing workload is not all lost, it will save the new
> process time on those.
Ah good point, even if it has to re-read all the pages, it'll only have to
write fozenxid for the pages that weren't processed before, that's good to
hear.
Isn'there also something about vacuum marking a page as "all empty", so that
it can be skiped by the next run ?
I don't get what index read is saved between vacuum runs ? I have 64G of RAM
on this box, so there's no hope of the index staying in memory.
> 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 look at where it is in the
> table files, not the index files.
Neat trick, thanks. I'll try it in the morning when I'm fresh. Are the files
read in C-sorting order ?
> > Second question is: how come we reached the wraparound threshold on this
> > table
> > at all ? We've only been keeping 28 days of data in this table, doing
> > incremental deletes every day (there are no updates at all). I find it
> > very
> > unlikely that we'd go through 2M transactions 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, or am I missing something else ?
>
> PostgreSQL doesn't know what your delete routine is like. It has to verify
> with its own eyes that there are no rows over a certain age.
My assumption was that routine vacuuming was able to update the table's
relfroxenxid, but I realize now that PG needs to do a full scan before
updating that.
> I don't think that routine vacuums even attempts to update relfrozenxid, or
> at least doesn't try very hard.
AFAIU it does (the 6th paragraph in 23.1.4 seems to imply it), and
vacuum_freeze_min_age controls when. Perhaps lowering that value would help
prepare a lot of the anti-wraparound work.
Pity there's no "frozen pages map" (or is there ?) to keep track of pages will
all-frozen tuples, it could speed up that anti-wraparound vacuum.
> Are you sure that routine vacuums have been running to completion on this
> table, as opposed to getting interrupted by something before finishing each
> time?
I checked pg_stat_user_tables.last_{auto_,}vacuum for this table and they are
both null. This is seriously worrying. I've seen autovacuum take a few days on
this table but always assumed that it finished properly. And I'm pretty sure
vacuuming does some work, otherwise my disk usage woul grow much faster. I
have no idea what could cause vacuuming to systematically stop before the end.
Maybe I'll find something in the logs.
Thanks for your answers.
--
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
[GENERAL] Speeding up an in-progress wraparound-preventing vacuum
Hi List, I have a "autovacuum: VACUUM ANALYZE public.some_big_table (to prevent wraparound)" that has been running for over 13 days. The process is consuming IO so I'm confident it isn't stuck, but it's still taking surprisingly long. PG 9.1.13 on Debian. The actual table is 584G on a SAN, plus 324G of indexes on local disk. The system was IO-starved until about 5 days ago, after offloading half the work to a different server and waiting for the purging of old data (went from keeping 4 weeks to 2) to catch up (so probably 2/3rd of the table is currently 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 extrapolated from atop output). I've tried raising autovacuum_vacuum_cost_limit from 500 to 5000, but apparently the already-running vacuum process didn't pick up the change (I did send a sighup, and new vacuum processes do run faster). I tried running a manual vacuum on that table (to benefit from the new settings and from the more aggressive behavior of manual vacuums), but it's apparently waiting for the wraparound vacuum to finish first. My currrent settings: autovacuum | on| default autovacuum_analyze_scale_factor | 0.1 | default autovacuum_analyze_threshold| 1 | configuration file autovacuum_freeze_max_age | 2 | default autovacuum_max_workers | 3 | default autovacuum_naptime | 60| default autovacuum_vacuum_cost_delay| 10| configuration file autovacuum_vacuum_cost_limit| 5000 | configuration file autovacuum_vacuum_scale_factor | 0.002 | configuration file autovacuum_vacuum_threshold | 1 | configuration file maintenance_work_mem| 524288| configuration file The vacuum_scale_factor is tweaked to trigger once a day for most tables, and should trigger every 3 days on the problem table. My first question is: is there a way to speedup the currently-running vacuum process ? I guess killing it to let it be replaced by a more agressively- configured one would work, but I'd hate to lose 2 weeks of processing (is there a way to estimate how much more vacuuming work remains to be done ?), and I'm being a bit more cautious with the wraparound-preventing kind. Second question is: how come we reached the wraparound threshold on this table at all ? We've only been keeping 28 days of data in this table, doing incremental deletes every day (there are no updates at all). I find it very unlikely that we'd go through 2M transactions 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, or am I missing something else ? 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
Re: [GENERAL] Receiving many more rows than expected
On Monday 12 May 2014 10:10:36 David G Johnston wrote: > Did you try rewriting the query to avoid using an IN expression? > > UPDATE foo SET processing = 't' > FROM ( > SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT 5000 FOR > UPDATE > ) src > WHERE foo.id = src.id; > > The workaround I mentioned above said that a CTE was needed but I'm thinking > that a simply FROM would be just as effective. Otherwise: > > UPDATE foo SET processing = 't' > FROM ( > WITH ids_to_update AS ( > SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT 5000 FOR > UPDATE > ) > SELECT id FROM ids_to_update > ) src > WHERE foo.id = src.id; As it happens, I've done a fair bit of refactoring in my code (but not the actual query), and now I cannot reproduce the bug anymore :/ 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
Re: [GENERAL] Downtime-free 'alter table set tablespace'
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'll take to copy the data. > > You might look at pg_repack (https://github.com/reorg/pg_repack). The > most recent version added the ability to repack a table (or just > indexes) to a new tablespace. It won't be fast as it will essentially > rebuild the entire table. But it only needs an exclusive lock for a > brief moment, so there's virtually zero down time and no data loss. > > - Chris That's pretty much what I was looking for, thanks. It's not perfect because it still requires a fair amount of temporary space on the origin tablespace, but it does the job in a cleaner way than what I was attempting. Thanks Jerry too for the slony suggestion, I didn't think slony (which I've used a bit) supported replicating to the same db in a different table name. It'd still be nice to get support in core for "set tablespace concurrently" because it has the potential to be much more efficient, but beggers can't be choosers :p -- 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
[GENERAL] Downtime-free 'alter table set tablespace'
Hi list, I'm in the process of moving some tables to a new tablespace ahead of disk space issues. I'm on PG 9.1, using streaming replication. 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'll take to copy the data. I've searched the docs and internet but found nothing very exciting. The most promissing was Josh's http://www.databasesoup.com/2013/11/moving-tablespaces.html but I'm on 9.1 and just want to move one object to a different tablespace, not move the whole existing tablespace to a different partition. My current attempt consists of renaming the old table, recreating it in the correct tablespace, then progressively inserting data from the old table into the new one (table is insert-only with periodic purge of old data; missing old data for a while is an acceptable service degradation), and then cleaning up after myself. It should work, but it's very error-prone (there are triggers and foreign keys to deal with), slow, and app-specific. Is there another low-level trick I missed ? Some combination of pg_start_backup, 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
Re: [GENERAL] Receiving many more rows than expected
On Friday 09 May 2014 08:36:04 David G Johnston wrote: > This seems to likely be the same, still open, bug reported previously: > > No Number Assigned: > http://www.postgresql.org/message-id/CANCipfpfzoYnOz5jj=UZ70_R=CwDHv36dqWSpw > si27vpm1z...@mail.gmail.com > > #8464 > http://www.postgresql.org/message-id/E1VN53g-0002Iy-Il@wrigleys.postgresql.o > rg > > #8470 is referenced in the first thread as well...though that is > specifically a performance issue and not a query bug. > > The recommended work-around is to move the sub-query using the "FOR UPDATE" > into a CTE. Thanks for those pointers, it certainly looks like the same issue (the only difference being the size of the limit) and there has been a good amount of discussion there. I'll try the CTE workaround, although that leaves a bad taste in my mouth. >From the discussions in the 1st thread, I wonder wether raising the isolation level to repeatable read would also fix the issue ? In any case, testing will take time because the bug triggers less than once a day and I haven't yet managed to reproduce it locally. ps: sorry I'm only 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
Re: [GENERAL] Receiving many more rows than expected
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.0.17 and 9.3.4 and could not get it to fail.
>
> Did hard coding the limit value change anything?
I didn't hardcode the formating, just the test to display more debug when the
bug happens:
> if rlen != curs.rowcount or rlen > 5000:
>LOG.error("Fetched strange number of rows %d %d %s", rlen, curs.rowcount,
> query)
And I got 2 instances of the bug during the weekend. Annoyingly, the debug
showed as expected that rlen == curs.rowcount and that the query does ask for
5000 records. So the querystring formating is apparently not at fault.
I also verified that none of the returned row ids overlapped (as expected).
So... Still not solved :(
I'm running out of ideas, but there are still two things I want to try:
* Logging all queries on the server side until I catch at least one instance
of the bug. Is there a way to log the number of rows affected by a query ?
* Create a new cursor for each execute(), in case there somehow is some
leftover state between two execute() calls. However I did a number of code
changes today; they should be unrelated but I'll let them run on their own
for a while to verify that.
--
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
Re: [GENERAL] Receiving many more rows than expected
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 having left the loop. Just in case I'm now logging the > > query string anyway; who knows... > > As proof of concept that the value can change in the loop: > > > In [17]: l = [1, 2, 3, 4, 5] > > In [18]: def getNumber(): > return random.choice(l) > : > > In [19]: ct = 0 > > In [20]: while ct < 5: > s = 'Lucky number is %d' % (getNumber(),) > ct += 1 > print s > : > Lucky number is 5 > Lucky number is 5 > Lucky number is 4 > Lucky number is 3 > Lucky number is 2 Sure, but that's not what I'm doing at all. My "str" % (somefunc(),) is executed outside the loop, not inside it. This is python, not lisp. The variable "query" is a plain string, not a reference to how that string was created. -- 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
Re: [GENERAL] Receiving many more rows than expected
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
> >
> >(SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC
> >LIMIT %d
> >
> > FOR UPDATE)
> >
> >RETURNING *""" % (conf_getint('DEFAULT', 'push_count', 5000),)
>
> Well, of course this view of things exposes a relevant failure mode
> you hadn't mentioned: maybe sometimes the conf_getint() call returns
> something other than 5000?
True. But I've commented already that I'd be very surprised (and wouldn't know
how to begin) if that value was faulty (even though it would explain things
nicely), because
* It is parsed once at program start (using python's ConfigParser library)
* It has the correct value of 5000 in most cases (as demonstrated by the
frequency of number of rows returned)
* There is no sign that I exited the loop (and therefore got the opportunity
to change the value of the query) before I start receiving overlong results.
Still, I agree it's suspicious, so I'm now logging the query string whenever I
get over 5000 results (hardcoded). We'll see next time it happens.
--
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
Re: [GENERAL] Receiving many more rows than expected
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, progressively becoming
> > rarer for bigger values, up to value 5000 which is very frequent again
> > (depending on the day, between 0.5 and 5% of queries return the maximum
> > number of rows), then a handfull freak values of ~9k, 11k, 15k, 33k, 46k,
> > 107k, etc which cause my problems.
>
> So just to be clear this the value for rlen and that value and log
> message only appear in the loop above?
Yes, and the problematic query is only executed in this loop.
For clarity's sake I had left out bits of the code concering the notification.
In case it changes anything, this is the uncut (but still anonimized)
function:
def enqueue_loop(q):
global DO_LOOP
poll = select.poll()
poll.register(DB_HANDLER.fileno(), select.POLLIN)
curs = DB_HANDLER.cursor()
query = """UPDATE foo SET processing = 't' WHERE id IN
(SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT %d
FOR UPDATE)
RETURNING *""" % (conf_getint('DEFAULT', 'push_count', 5000),)
while DO_LOOP:
# Fetch data and pop notifications.
curs.execute(query)
results = curs.fetchall()
rlen = len(results)
nlen = len(DB_HANDLER.notifies)
while DB_HANDLER.notifies:
DB_HANDLER.notifies.pop()
if rlen > 0 or nlen > 0:
LOG.debug("Fetched %d rows, poped %d notifs, %d batches in queue",
rlen, nlen, q.qsize())
# If we get nothing, wait for notifications. Try again anyway once a
# minute to make sure that connection is ok.
if rlen == 0:
LOG.debug("Waiting for a notification")
poll.poll(6)
continue
# Got a batch of rows, enqueue them.
results.sort() # Sort is needed here (compares the 1st element of the
# tuple) because we do not have the guarantee that
# "UPDATE ... (SELECT ORDER BY ..) RETURNING *" returns
# ordered rows.
q.put(results)
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 having left the loop. Just in case I'm now logging the query string
anyway; who knows...
> >>> if rlen == 0:
> >>> # [...] wait for notification...
> >>> continue
> >>>
> >>> # [...] Enqueue batch and let other threads process it.
> >>> # [...] Those threads will eventually delete the processed
> >>> rows
> >>> from
> >>> # the foo table.
> >>>
> >>> 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 getting timeouts and other problems as a result.
>
> And there is no instance of the UPDATE query that is unconstrained and a
> code path to that query?
>
> Or as Sim suggested another copy of this code without the LIMIT?
Again, no. I'm now logging the query string just in case, but I can't imagine
that the value of the limit would change at runtime unless I have somehow
managed to corrupt the memory of a pure python program.
> Also what happens in the rlen == 0 case?
See the unabridged code above. We wait up to 1 minute for a notification to
arrive, and then we restart the loop.
--
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
Re: [GENERAL] Receiving many more rows than expected
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 getting timeouts and other problems as a result.
> I would bet you have another copy of the code running without the limit.
> Maybe on a cron job. To prove this, modify the log statement slightly and
> see if this statement is really returning more then 5000 rows. LOG.debug("1
> - Fetched %d rows", rlen) or similar.
I assure you I don't :/ The program is only 350 lines including plenty of good
comments, it'd be hard to miss a rogue query.
Exhaustive list of queries:
- pg_try_advisory_lock to make sure that only one instance is running at a
time
- set processing='f' during startup
- delete from foo where id in (fully_processed_records)
- listen for the notification from an "on insert" trigger on table foo
- select highest and lowest record in foo for monitoring purpose
--
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
Re: [GENERAL] Receiving many more rows than expected
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:
> > def enqueue_loop(q):
> > curs = DB_HANDLER.cursor()
> > query = """UPDATE foo SET processing = 't' WHERE id IN
> >
> > (SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC
> > LIMIT
> >
> > 5000 FOR UPDATE) RETURNING *"""
>
> # Where is this query actually run?
Sorry, I edited my code too much, it's actually executed at the first line of
the loop, I added it below.
> > while DO_LOOP: #the whole program eventually stops if this is false
>
> # What cause DO_LOOP to go false?
Either when receiving a signal from the OS (registered with
"signal.signal(signal.SIGINT, stop_main)") or when the topmost try-catch-
reinitialize-retry loop has caught a quick sucession of exceptions.
DO_LOOP is tested in a few places where we can make a clean exit. A cronjob
will restart the process if it is not or badly running.
> > curs.execute(query)
> > results = curs.fetchall()
> > rlen = len(results)
> >
> > if rlen > 0:
> > LOG.debug("Fetched %d rows", rlen)
>
> # What do you see in LOG for rlen values?
The histogram shows a large amount of small values, progressively becoming
rarer for bigger values, up to value 5000 which is very frequent again
(depending on the day, between 0.5 and 5% of queries return the maximum number
of rows), then a handfull freak values of ~9k, 11k, 15k, 33k, 46k, 107k, etc
which cause my problems.
> > if rlen == 0:
> > # [...] wait for notification...
> > continue
> >
> > # [...] Enqueue batch and let other threads process it.
> > # [...] Those threads will eventually delete the processed rows
> > from
> > # the foo table.
> >
> > 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 getting timeouts 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 sure if this is a server or a driver
> > issue.
> >
> >
> > Any idea ? 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
[GENERAL] Receiving many more rows than expected
Hello,
I'm processing records in batches using PG 9.1.12, python 2.7, and psycopg
2.5.2 :
def enqueue_loop(q):
curs = DB_HANDLER.cursor()
query = """UPDATE foo SET processing = 't' WHERE id IN
(SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT
5000 FOR UPDATE) RETURNING *"""
while DO_LOOP: #the whole program eventually stops if this is false
results = curs.fetchall()
rlen = len(results)
if rlen > 0:
LOG.debug("Fetched %d rows", rlen)
if rlen == 0:
# [...] wait for notification...
continue
# [...] Enqueue batch and let other threads process it.
# [...] Those threads will eventually delete the processed rows from
# the foo table.
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 getting timeouts 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 sure if this is a server or a driver issue.
Any idea ? 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
Re: [GENERAL] Deleted files still open long after droping a database
On Monday 17 February 2014 21:14:35 Tom Lane wrote: > Kevin Grittner writes: > > Perhaps we should arrange for a DROP DATABASE command to somehow > > signal all backends to close files from that backend? > > See commit ff3f9c8de, which was back-patched into 9.1.x as of 9.1.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 pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deleted files still open long after droping a database
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 correspond to the droped > db, all open by some postgres process. > > A lot of process were db connections to databases other than the droped one > (I'm not using connection pooling). The other process is the autovacuum > launcher. I have reset the various connection processes, but I hesitate to > kill the autovacuum process. > > I'm not sure wether that's relevant, but I moved the database to another > server using slony1-2.2.2 before droping it on the old server. There were > already no more slon processes running when I droped the db. > > > 1) why does this happen at all (keeping files from a droped databse open, > even by processes that never connected to that specific db) ? > > 2) Can the autovacuum launcher process be safely killed (there are no long- >running vacuum queries) ? I solved that particular issue by disabling and reenabling autovacuum. So I finally got my disk space back, but I'm still interested in an answer to those questions. > 3) Is there a generally cleaner way to do all this ? -- 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
[GENERAL] Deleted files still open long after droping a database
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 correspond to the droped db, all open by some postgres process. A lot of process were db connections to databases other than the droped one (I'm not using connection pooling). The other process is the autovacuum launcher. I have reset the various connection processes, but I hesitate to kill the autovacuum process. I'm not sure wether that's relevant, but I moved the database to another server using slony1-2.2.2 before droping it on the old server. There were already no more slon processes running when I droped the db. 1) why does this happen at all (keeping files from a droped databse open, even by processes that never connected to that specific db) ? 2) Can the autovacuum launcher process be safely killed (there are no long- running vacuum queries) ? 3) Is there a generally cleaner way to do all this ? PostgreSQL 9.1.2 on debian linux squeeze $ ps aux|grep autovacuum postgres 5025 0.0 0.2 122992 39124 ?Ss Jan27 14:28 postgres: autovacuum launcher process $ sudo lsof +aL1 /path/to/pgsql/ postgres 5025 postgres9u REG8,74308992 0 14943966 /path/to/pgsql/base/35355/5979724 (deleted) postgres 5025 postgres 24u REG8,7 8192 0 14948153 /path/to/pgsql/base/35355/35460 (deleted) postgres 5025 postgres 35u REG8,7 286720 0 14947766 /path/to/pgsql/base/35355/5979918 (deleted) postgres 5025 postgres 63u REG8,7 1073741824 0 14947390 /path/to/pgsql/base/35355/5979501.1 (deleted) postgres 5025 postgres 73u REG8,7 319488 0 14947627 /path/to/pgsql/base/35355/5979758 (deleted) postgres 5025 postgres 79u REG8,7 1073741824 0 14947428 /path/to/pgsql/base/35355/5979507 (deleted) postgres 5025 postgres 84u REG8,7 1073741824 0 14947317 /path/to/pgsql/base/35355/5979501 (deleted) postgres 5025 postgres 113u REG8,7 8192 0 14948150 /path/to/pgsql/base/35355/35398 (deleted) postgres 5025 postgres 131u REG8,72326528 0 14943967 /path/to/pgsql/base/35355/5979727 (deleted) postgres 5025 postgres 141u REG8,7 16384 0 14947008 /path/to/pgsql/base/35355/5979746 (deleted) postgres 5025 postgres 142u REG8,7 28991488 0 14944726 /path/to/pgsql/base/35355/5979728 (deleted) postgres 5025 postgres 146u REG8,7 1073741824 0 14947442 /path/to/pgsql/base/35355/5979508 (deleted) postgres 5025 postgres 150u REG8,7 82337792 0 14947615 /path/to/pgsql/base/35355/5979710 (deleted) postgres 5025 postgres 169u REG8,7 37855232 0 14944962 /path/to/pgsql/base/35355/5979729 (deleted) postgres 5025 postgres 178u REG8,7 8192 0 14948156 /path/to/pgsql/base/35355/35723 (deleted) postgres 5025 postgres 179u REG8,7 1073741824 0 14947429 /path/to/pgsql/base/35355/5979509 (deleted) postgres 5025 postgres 181u REG8,7 450560 0 14947764 /path/to/pgsql/base/35355/5979914 (deleted) postgres 5025 postgres 185u REG8,7 95968 0 14946595 /path/to/pgsql/base/35355/5979508.1 (deleted) postgres 5025 postgres 200u REG8,7 224239616 0 14947343 /path/to/pgsql/base/35355/5979507.1 (deleted) postgres 5025 postgres 204u REG8,7 74473472 0 14947620 /path/to/pgsql/base/35355/5979713 (deleted) postgres 5025 postgres 208u REG8,7 106536960 0 14947623 /path/to/pgsql/base/35355/5979719 (deleted) postgres 5025 postgres 210u REG8,7 105611264 0 14947619 /path/to/pgsql/base/35355/5979712 (deleted) postgres 5025 postgres 212u REG8,7 446078976 0 14947611 /path/to/pgsql/base/35355/5979704 (deleted) postgres 5025 postgres 216u REG8,7 75988992 0 14947469 /path/to/pgsql/base/35355/5979509.1 (deleted) postgres 5025 postgres 222u REG8,7 513097728 0 14947263 /path/to/pgsql/base/35355/5979501.2 (deleted) postgres 5025 postgres 239u REG8,7 119832576 0 14947621 /path/to/pgsql/base/35355/5979716 (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
Re: [GENERAL] Streaming replication: sequences on slave seemingly ahead of sequences on master
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 sequence ids are higher on the slave than on the > > master. I could understand if they were lower, but this is weird. > > > > * The slave's sequences can be anywhere between 1 and 50 ids ahead. > > This is normal. The sequences are advanced in chunks of 100, so the > master's value will be the nextval() while the value on standby will > be the start of the next chunk, so as you say, slightly ahead of the > master. > > The same thing would also happen in case of a crash. Thanks for the explanation (Michael's too). Would be nice to see it added to the documentation (unless I just didn't find it ?), as it is quite surprising, and might lead to problems if people expect to be able to read sequence values from the slave. As a bonus question, I guess it would be the same if using synchroneous replication ? -- 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
Re: [GENERAL] Streaming replication: sequences on slave seemingly ahead of sequences on master
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. > > > > * wal_keep_segments is set high on the master > > * the slave's recovery.conf contains just standbay_mode=on and > > primary_conninfo=foo > > * we use a simple start_backup/rsync/stop_backup to create the base copy > > before starting the slave. > > > > > > 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 sequence ids are higher on the slave than on the > > master. I could understand if they were lower, but this is weird. > > > > * The slave's sequences can be anywhere between 1 and 50 ids ahead. > > how did you determine that exactly? Quick and dirty : SQL=$(psql -tA -h $MASTER $DB <<< "select E'select \''||table_name||E'\', '|| column_name||' from '||table_name||' order by '||column_name||' desc limit 1;' from information_schema.columns where table_schema='public' and ordinal_position=1 order by table_name;select E'select \''||sequence_name|| E'\', last_value from '||sequence_name||';' from information_schema.sequences where sequence_schema='public' order by sequence_name;") psql -tA -h $SLAVE $DB <<< "select pg_last_xlog_replay_location();$SQL" > $SLAVE.check psql -tA -h $MASTER $DB <<< "select pg_current_xlog_location();$SQL" > $MASTER.check if diff -u $MASTER.check $SLAVE.check; then cat $MASTER.check echo -e "\e[32msync ok\e[m" else echo -e "\e[31msync bad\e[m" fi > how do you know the transactions > are committing in sequence order? I dont, actually. But whichever order the transactions eventually commit in, I'd expect that order to be the same on the slave and the host ? And I wouldn't expect anything to finish on the slave before it finishes on the master ? -- Vincent de Phily Mobile Devices +33 (0) 142 119 325 +353 (0) 85 710 6320 Warning This message (and any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication: sequences on slave seemingly ahead of sequences on master
Hi list, we have two 9.1.2 servers on debian squeeze, and are setting up a simple streaming replication between the two. * wal_keep_segments is set high on the master * the slave's recovery.conf contains just standbay_mode=on and primary_conninfo=foo * we use a simple start_backup/rsync/stop_backup to create the base copy before starting the slave. 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 sequence ids are higher on the slave than on the master. I could understand if they were lower, but this is weird. * The slave's sequences can be anywhere between 1 and 50 ids ahead. * The actual table data is properly in sync. * We look at the slave before the master. * We ignore readings where pg_current_xlog_location() != pg_last_xlog_replay_location(). * It only happens on frequently-updated sequences. * During recovery, we have warnings of the form: 2012-05-04 10:32:08 CEST WARNING: xlog min recovery request 16A/2A03BDD0 is past current point 16A/1E72A880 2012-05-04 10:32:08 CEST CONTEXT: writing block 0 of relation base/35355/42224_vm xlog redo vacuum: rel 1663/1562168/1563037; blk 12122, lastBlockVacuumed 12070 2012-05-04 10:32:12 CEST WARNING: xlog min recovery request 16A/469F2120 is past current point 16A/1E9B6EB8 2012-05-04 10:32:12 CEST CONTEXT: writing block 0 of relation base/56308/57181_vm xlog redo vacuum: rel 1663/1562168/1563037; blk 21875, lastBlockVacuumed 21329 2012-05-04 10:32:17 CEST WARNING: xlog min recovery request 16A/22D497B8 is past current point 16A/1FF69258 * servers have near-identical hardware and software * monitoring via munin show at most 1-2 KB of replication lag * we retried 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/mailpref/pgsql-general
[GENERAL] concatenating text and bytea
Hi,
I got recently bitten by this :
# select 'ascii'::text || E'\\xdeadbeef'::bytea, pg_typeof('ascii'::text ||
'\xdeadbeef'::bytea), 'ascii'::bytea || E'\\xdeadbeef'::bytea;
?column? | pg_typeof | ?column?
-+---+--
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
Re: [GENERAL] Privilege on schema 'public' not revokable
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 FROM foouser; > > > > "foouser" is obtaining its permission to "CREATE" on the "public " schema > > via global/PUBLIC permissions (i.e., via inheritance). Revoking only > > removes an explicitly granted permission but does not institute a block > > for > > an inherited permission. You would need to revoke the global permission > > to > > CREATE on "public" and then only GRANT it back to those users/roles that > > you wish to have it - all others will then effectively lose that ability. > Correct. Note where it says in the GRANT manual page that a user's > effective rights are the sum of those granted to PUBLIC, those granted > directly to him, and those granted to roles he is a member of. Rights > granted to PUBLIC are available to everybody, full stop, and can't be > selectively blocked. > > regards, tom lane Ah thanks, that's what I had been thinking during the night, but you confirmed it. Leaves me wondering how I lost the public permission in the first place, but hey :p Also, how do I see the privileges granted to public on schema ? -- Vincent de Phily
[GENERAL] Privilege on schema 'public' not revokable
Hi,
after having been locked-out of the public schema by mistake (which I fixed
with "GRANT ALL ON SCHEMA public TO 'foobar'"), I wanted to update my
privilege-check script to take the schema into account, but I'm running into
the following behaviour :
> $ psql db_foo
> psql (9.1.2)
> Type "help" for help.
>
> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser;
> REVOKE
> db_foo=# SELECT has_schema_privilege('foouser', 'public', 'CREATE');
> has_schema_privilege
> --
> t
> (1 row)
>
> db_foo=# \q
> $ psql db_foo -U foouser
> psql (9.1.2)
> Type "help" for help.
>
> db_foo=> create table tokill2(a int);
> CREATE TABLE
> db_foo=>
This is observable in 9.1.2 and 8.3.18 (gentoo linux packages). Same issue
with USAGE privilege. 'foouser' is neither a superuser nor the owner of any
database object.
Either I misunderstood something, or something is going awry (I expected
has_schema_privilege() to return 'f', and not being allowed to create a table
as user 'foouser').
I'm also wondering how I managed to revoke the privilege (symptom: "no such
table 'foobar'" messages when logged-in as the problematic user) in the first
place. I was using slony 2.1 to migrate from PG8.3 to PG 9.1. Not all migrated
databases were affected by the problem.
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
Re: [GENERAL] does reindex need exclusive table access?
On Tuesday 01 November 2011 12:00:33 Craig Ringer wrote: > A workaround for reindexing while live is to begin a transaction, create > the new index with a new name, drop the old one, rename the new one to > the old one, and commit. This only requires an exclusive lock for the > period of the drop and rename. On more recent versions you can even use > this for indexes that implement primary key or unique constrants by > using the 'ADD CONSTRAINT ... USING INDEX ...' syntax; see: > > http://www.postgresql.org/docs/current/static/sql-altertable.html Been happily doing this (without worrying about transactions, as Tom suggested), but couldn't quite figure out how to do it with my primary key indexes on 8.3. Do I have to bite the bullet and take an exclusive lock (or upgrade to 9.1) ? The technique kinda works (with some changes) using unique 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 make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?
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 > > the vacuum daemon notices that there are 10 tables that need vacuuming > > now. It allocates 3 workers, but while they do their intentionally-slow > > work, the other 7 tables keep creating more vacuumable tuples, so > > it'll be more work overall because they're "late" in their "vacuum > > schedule". Does that make sense (I'm not sure id does) ? > > Yes, that's exactly the issue. You need to balance the resource > you're depriving the "real" database transactions (i.e. the user ones) > against the cost of waiting, which waiting will probably cost those > user transactions in performance. The reason there's no magic > solution is because much of this depends on your use patterns. Ok, I'm glad my reasoning wasn't completely flawed :) > > Anyway, my particular issue is solved for now : I realized those tables > > were terribly bloated (often more than 99% slack), so I vacuum-fulled > > them and now the autovacuums run very fast and the disk is 90% idle > > again. That slack probably appeared at table initialization time > > because the fsm was not big enough. I since raised the fsm, but I think > > it's big enough during normal (non-init) usage anyway. > > This is what you want to keep an eye on, then. Yup, watching that. > Why do you think it > came from "initialization time", though? VACUUM only has work to do > when dead tuples show up (e.g. from DELETE or UPDATE), and normally > when you first populate a table you do a COPY, which isn't going to > create dead tuples. Those tables are a hand-made trigger-maintained "materialized view" created about 2 months ago. Initializing them meant doing a full seqscan of the reference table and doing one insert and 1-2 updates for each row in the MV table. And the work was split in thousands of transactions with a load- dependent sleep between them, in order to not impact user queries. Those updates (and some inserts) still hapen during normal usage, but at a much slower pace which autovacuum should have no trouble keeping up with. > > I'm still interested in more opinions about my two questions : > > * When does it make sense to make autovacuum more aggressive on IO, > > and by> > >how much ? > > At bottom, you don't want your tables to get so bloated that they > exhibit the problem you just saw, but you also don't want vacuum to be > taking so much I/O that your other tasks can't get done. That's the > general principle; how it applies to your case depends rather on use > patters. For instance, if you know that there will be at most 10% > churn on every table every day, but all transactions happen between > 9:00 and 17:00 local time, then it's probably safe to allow that to > happen: as long as your FSM can keep track, it can all be recovered > every day after 17:00, so you might as well allow the work to build > up, & let the vacuums happen when they're not stealing any I/O from > user queries. If, on the other hand, you get 100% churn on 50% of the > tables every day between 09:00 and 11:00, and the rest of the day is > mostly read-only traffic, with read-only traffic during all 24 hours > (don't scoff -- I had exactly this problem once) then you want to be > quite aggressive with the autovacuum settings, because keeping that > 100% bloat down is going to pay off in a big way on the read-only > traffic. Interesting. Although if you have such well-defined churn times, it might be better to vacuum from cron instead of from autovacuum ? You also don't want to autovacuum now if you know your churn will be over in 15 min. Looks like it's going to be hard to extract general rules. One of my motivations to make autovaccum more aggresive was that my fsm was too small and I didn't want a PG restart to take the new value into account yet. So "finish this vacuum faster and get on to the next one" was a way to do that "next one" before the fsm overflowed. But I now realize it's a very bad kludge, and I should just have my fsm sized right (or sized automatically; have I already said that I long to upgrade ? :p) > > * Does vacuuming fill the OS's disk cache, and is it an issue if it > > does ? > Well, it _affects_ the OS's disk cache. Whether it fills it is > controlled by the cache algorithms and the amount of memory you have > devoted to cac
Re: [GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?
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 system idle every now and then? It's not > going to get tired if it keeps working, and if you have a lot of work > and can spin out that work so that the system always has a little bit > of work to do, then you use your resources more efficiently. > > Normally, one likes to see some idle time because it is evidence of > "headroom" -- that you have more capacity than you actually need. If > that's the reason you want to see the idle times, then surely you > don't want to tune the system with the goal of causing idleness. You > want to tune the system so that the work gets done in as smooth and > fast a way possible. So I would aim for maximum throughput (including > but not exclusively complete table maintenance) and then check whether > you're getting any idle time. Off the cuff, though, it sounds to me > like you need more capacity than you have. I agree idleness per se is not the goal, and whatever work needs to be done might as well be spread smoothly over time. Idleness *is* however a measure of the ressource headroom available, and that headroom was incomfortably small (for IO) in this case. I was just groping for more performance out of the system. 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 the vacuum daemon notices that there are 10 tables that need vacuuming now. It allocates 3 workers, but while they do their intentionally-slow work, the other 7 tables keep creating more vacuumable tuples, so it'll be more work overall because they're "late" in their "vacuum schedule". Does that make sense (I'm not sure id does) ? Anyway, my particular issue is solved for now : I realized those tables were terribly bloated (often more than 99% slack), so I vacuum-fulled them and now the autovacuums run very fast and the disk is 90% idle again. That slack probably appeared at table initialization time because the fsm was not big enough. I since raised the fsm, but I think it's big enough during normal (non-init) usage anyway. I'm still interested in more opinions about my two questions : * When does it make sense to make autovacuum more aggressive on IO, and by how much ? * Does vacuuming fill the OS's disk cache, and is it an issue if it does ? Cheers. -- 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
[GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?
Hi list, I've got a heavily-updated table, and about 30 customers on the same system each with his own version of the table. The 3 configured autovacuum workers take turns vacuuming the table in each customer db; autovacuum is never idle and takes a large part of the available IO. Fearing that vacuuming might accumulate lateness and hoping to see the system idle every now and then, I increased autovacuum_vacuum_cost_limit to 500 and decreased autovacuum_vacuum_cost_delay to 10. First question : is it an intelligent thing to do or am I better off ignoring the constant vacuuming and trusting that things will get done in time ? With the new settings, autovacuum is still constant (even though each one takes less time), but I'm wary of making autovacuum even less "io-nice". Second thing : the vacuumed tables+indexes taken together are bigger than the available OS disk cache. Does vacuuming them fill the cache, or is there some kind of O_DIRECT in use ? I have a feeling (very un-verified) that this is not the most usefull data I could have in my cache. This is all on PG 8.3. I know upgrading would improve things (particularly since a large percentage 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seeing foreign key lookups in explain output
On Tuesday 20 September 2011 16:32:50 Marti Raudsepp wrote: > On Tue, Sep 20, 2011 at 16:12, Marti Raudsepp wrote: > > +1 for a TODO on adding foreign key trigger time to EXPLAIN ANALYZE > > output. > Sorry, that was too hasty. We already have that now in 9.0 and 9.1 > (not sure when it was introduced) > > create table a as select generate_series(1,1) i; > create table b as select generate_series(1,1) i; > alter table a add primary key (i); > alter table b add foreign key (i) references a (i) on delete cascade; > explain analyze delete from a; > > QUERY PLAN > --- > Delete (cost=0.00..145.00 rows=1 > width=6) (actual > time=16.308..16.308 rows=0 loops=1) >-> Seq Scan on a (cost=0.00..145.00 rows=1 width=6) (actual > time=0.008..2.208 rows=1 loops=1) > Trigger for constraint b_i_fkey: time=6324.652 calls=1 > Total runtime: 6342.406 ms > > Notice the line "Trigger for constraint b_i_fkey" Ahhh I never 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
[GENERAL] Seeing foreign key lookups in explain output
Hi list, an explain analyze wish : create table t1(id serial primary key); create table t2(id serial primary key, ref integer references t1(id) on delete cascade); ...insert many rows in both tables... explain delete from t1 where id < 1; ... The explain output will tell me it's using the index on t1's id, but it tells me nothing about the seqscan that happens on t2 (because I forgot to add an index on t2.ref). It isn't the first time I get bitten by this, and I bet I'm not the only one. The explain tells me everything will work fast, but 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". -- 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
Re: [GENERAL] duplicate sequence, it is possible?
On Monday 19 September 2011 08:19:18 Alban Hertroys wrote:
> On 19 Sep 2011, at 2:57, Anibal David Acosta wrote:
> > Hi everyone.
> >
> > I have a table with a PK, this table has a lot of insert per second (100
> > ~ 150 insert /sec) Sometimes, a get a duplicate key error, but ID is
> > generated from a function (VOLATILE). This function has just one line
> > (select nextval('XXX'))
> >
> > Is possible that this function return same ID for two diff call?
> >
> > Postgres version is 9.0
> >
> > Thanks!
>
> Very unlikely.
> Are you certain that you're not just hitting pre-existing rows that have
> ID's higher than the starting ID of your sequence? Or that there isn't
> another process inserting rows without using the sequence?
Also, why wrap nextval('XXX') 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
Re: [GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.
On Monday 12 September 2011 22:51:54 Reid Thompson wrote: > test=# select distinct on (val1) val1, val2, val3 from (SELECT > max(val3) OVER (PARTITION BY val1), * FROM sampledata) as sq where val3 > = max order by val1; Other things I've tried (was limited to PG8.3 so no "OVER (PARTITION...)" support) : SELECT DISTINCT ON (val1), val2, val3 FROM table ORDER BY val1, val3; SELECT val1, val2, val3 FROM table WHERE id IN ( SELECT sq.i FROM ( SELECT val1, max(val3) FROM table GROUP by 1 ) AS sq (v,i)) My case was a bit different since I wanted the record for distinct(A,B) instead of just DISTINC(A), and since I had a primary key available on the table. But let it be food for thought. However, none of those queries are either efficient or beautiful, so I ended up populating a "last_values" table via a trigger, which is way more efficient if it fits your needs : CREATE TABLE last_values (PRIMARY KEY foo, bar integer, baz integer); CREATE OR REPLACE FUNCTION insert_last_value() RETURNS TRIGGER AS $$ BEGIN UPDATE last_values SET val1=NEW.val1... WHERE ...; IF NOT found THEN BEGIN INSERT 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_last_values(); -- 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
Re: [GENERAL] md5 of table
On Thursday 01 September 2011 11:47:24 Sim Zacks wrote: > Is there a way to get an md5 or other hash of an entire table? > > I want to be able to easily compare 2 tables in different databases. > > I thought about using dblink and the EXCEPT query, but then I need to > know the field list of each query result, which is a pain in the butt. > > If I could return an md5 of the entire table, then I could check if the > tables have the same hash and be confident enough that the tables were > identical. > > > Thanks > Sim You might also want to take 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] altering foreign key without a table scan
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 catalog > >> lookup at run time. If you diddle one manually, you'd better diddle > >> both. > > > > Some relid values are indeed duplicated in pg_constraint and pg_trigger, > > but it doesn't look like I need to fiddle with those ? > > > > I'm only touching pg_trigger.tgfoid and > > pg_constraint.confdeltype/confupdtype (which indeed seem to say the > > same thing in a different way). Do you know if there is something else > > I've missed ? > > Yeah, that seems to be it except for the deferrable/deferred fields, > which match up in the obvious way. I had been thinking the RI triggers > avoided doing a lookup in pg_constraint, but that was mistaken. (I > think we used to store all that info in tgargs, but we evidently don't > anymore.) Thanks, I'll look into applying those next week then. If I never get back to the list about it, it'll mean that it worked without issues :) -- 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
Re: [GENERAL] altering foreign key without a table scan
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 unchanged (even after restarting > > postgres). > > > Digging further however, I found that pg_triggers can be used for my means > > : > IIRC, there are fields of pg_constraint that are copied into the > pg_trigger rows for the supporting triggers, so as to save one catalog > lookup at run time. If you diddle one manually, you'd better diddle > both. Some relid values are indeed duplicated in pg_constraint and pg_trigger, but it doesn't look like I need to fiddle with those ? I'm only touching pg_trigger.tgfoid and pg_constraint.confdeltype/confupdtype (which indeed seem to say the same thing in a different way). Do you know if there is something else I've missed ? 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
Re: [GENERAL] altering foreign key without a table scan
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> > > not necessary. In a sense, I'm looking for : > >> ALTER TABLE foo 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). > > Two things first... > > 1. I assume this is same for 8.3 > 2. Someone from Hackers best to answer if this is safe on live system >or might require at least a restart. > > Your 2 catalog fields of interest are; > pg_constraint.(confupdtype|confdeltype) > > Changing those for the relevant FKs should satisfy your needs. I am > not aware of those field values being duplicated anywhere. 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 unchanged (even after restarting postgres). Digging further however, I found that pg_triggers can be used for my means : CREATE TABLE tref(id INTEGER PRIMARY KEY); CREATE TABLE t(id INTEGER PRIMARY KEY, refid INTEGER REFERENCES tref(id) ON DELETE RESTRICT); INSERT INTO tref(id) VALUES (1),(2),(3); INSERT INTO t(id, refid) VALUES (1,1),(2,NULL),(3,1),(4,2); -- Cosmetic part: fkey looks updated but behaves the same (DELETE will fail) UPDATE pg_constraint SET confdeltype ='c' WHERE conname='t_refid_fkey'; \d t DELETE FROM tref WHERE id=1; -- Functional part: DELETE will now work (after opening a new connection) UPDATE pg_trigger SET tgfoid=(SELECT oid FROM pg_proc WHERE proname ='RI_FKey_cascade_del') WHERE tgconstrname='t_refid_fkey' AND tgfoid=(SELECT oid FROM pg_proc WHERE proname ='RI_FKey_restrict_del'); \c DELETE FROM tref WHERE id=1; > Strongly suggest you approach this with caution, as is standard > advice regarding any manual catalog fiddling. Of course. The psql script above works in my tests, but I could easily have missed a side-effect that will comme back to bite me at the worst moment. Unless someone can confirm that there are no hidden gotcha with this method, I'll probably wait until our migration to PG9.0 to do those schema changes. BTW, if anybody picks up the "ALTER CONSTRAINT" feature (low hanging fruit ?) for the next postgres release, I'll be happy to ship them their $FAVORITE_REWARD_BEVERAGE in the post :) -- Vincent de Phily Mobile Devices +33 (0) 142 119 325 +353 (0) 85 710 6320 Warning This message (and any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] altering foreign key without a table scan
Hi list, as part of a db schema update, I'd like to alter the "on update" property of a fkey, for example going from : > ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) > REFERENCES bar(id) ON UPDATE CASCADE ON DELETE CASCADE; to : > ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) > REFERENCES bar(id) ON UPDATE RESTRICT ON DELETE CASCADE; I understand I can create the new fkey and drop the old one, but this requires a scan of the table (to check that no existing data violates the new fkey) which, on this large, heavily-updated, no-downtime table I can't really aford. 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 not necessary. In a sense, I'm looking for : > ALTER TABLE foo 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 via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance Monitoring of PostGRE
On Wednesday 06 July 2011 14:27:53 BangarRaju Vadapalli wrote: >We want to monitor the performance of PostGRE database. Could anyone > please suggest any tools tried/working successfully... Munin will graph some usefull postgres stats. It's easy enough to graph another datapoint by creating a new plugin if you need to (for example, in addition to the global stats we graph the size of some specific tables). Pgfouine will create an aggregated report of all your queries, provided you've setup postgre swith sufficient logging. Explain analyze is you friend for individual 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://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] DELETE taking too much memory
On Friday 08 July 2011 10:31:33 French, Martin wrote: > If the query planner thinks it has the default amount of memory (128MB) > and the stats are out of date, then it will by no means be able to plan > proper execution. > > I would recommend setting the effective_cache_size to an appropriate > value, running "analyze" on both tables with an appropriate stats > target, and then explaining the query again to see if it's more > accurate. Yes, I'll schedule those two to run during the night and repost an explain, for information. However, we worked 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
Re: [GENERAL] DELETE taking too much memory
On Friday 08 July 2011 10:05:47 Dean Rasheed wrote: > > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: > >> Hi, > >> > >> I have a delete query taking 7.2G of ram (and counting) but I do not > >> understant why so much memory is necessary. The server has 12G, and > >> I'm afraid it'll go into swap. Using postgres 8.3.14. > >> > >> I'm purging some old data from table t1, which should cascade-delete > >> referencing rows in t2. Here's an anonymized rundown : > >> > >> # explain delete from t1 where t1id in (select t1id from t2 where > >> foo=0 and bar < '20101101'); > > It looks as though you're hitting one of the known issues with > PostgreSQL and FKs. The FK constraint checks and CASCADE actions are > implemented using AFTER triggers, which are queued up during the query > to be executed at the end. For very large queries, this queue of > pending triggers can become very large, using up all available memory. > > There's a TODO item to try to fix this for a future version of > PostgreSQL (maybe I'll have another go at it for 9.2), but at the > moment all versions of PostgreSQL suffer from this problem. That's very interesting, and a more plausible not-optimized-yet item than my guesses so far, thanks. Drop me a mail if you work on this, and I'll find some time to test your code. I'm wondering though : this sounds like the behaviour of a "deferrable" fkey, which AFAICS is not the default and not my case ? I haven't explored that area of constraints yet, so there's certainly some detail that I'm missing. > The simplest work-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 canceled the query after 24h, shortly before the OOM killer would have, and started doing things in smaller batches. -- 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
Re: [GENERAL] [PERFORM] DELETE taking too much memory
On Thursday 07 July 2011 19:54:08 French, Martin wrote: > How up to date are the statistics for the tables in question? > > What value do you have for effective cache size? > > My guess would be that planner thinks the method it is using is right > either for its current row number estimations, or the amount of memory > it thinks it has to play with. Not very up to date I'm afraid (as shown by the low estimate of deleted rows). Table t2 has been insert-only since its re-creation (that's another story), while t1 is your classic insert-many, update-recent. 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
Re: [GENERAL] DELETE taking too much memory
On Thursday 07 July 2011 22:26:45 Guillaume Lelarge wrote:
> On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
> > Hi,
> >
> > I have a delete query taking 7.2G of ram (and counting) but I do not
> > understant why so much memory is necessary. The server has 12G, and
> > I'm afraid it'll go into swap. Using postgres 8.3.14.
> >
> > I'm purging some old data from table t1, which should cascade-delete
> > referencing rows in t2. Here's an anonymized rundown :
> >
> >
> > # \d t1
> >
> > Table
> > "public.t1"
> >
> > Column |Type | Modifiers
> >
> > ---+-+--
> > ---
> >
> > t1id | integer | not null default
> >
> > nextval('t1_t1id_seq'::regclass)
> > (...snip...)
> >
> > Indexes:
> > "message_pkey" PRIMARY KEY, btree (id)
> >
> > (...snip...)
> >
> > # \d t2
> >
> >Table
> >"public.t
> >2"
> >
> > Column |Type |Modifiers
> >
> > -+-+
> > -
> >
> > t2id| integer | not null default
> >
> > nextval('t2_t2id_seq'::regclass)
> >
> > t1id| integer | not null
> > foo | integer | not null
> > bar | timestamp without time zone | not null default now()
> >
> > Indexes:
> > "t2_pkey" PRIMARY KEY, btree (t2id)
> > "t2_bar_key" btree (bar)
> > "t2_t1id_key" btree (t1id)
> >
> > Foreign-key constraints:
> > "t2_t1id_fkey" FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
> >
> > RESTRICT ON DELETE CASCADE
> >
> > # explain delete from t1 where t1id in (select t1id from t2 where
> > foo=0 and bar < '20101101');
> >
> >QUERY PLAN
> >
> >
> > -
> >
> > Nested Loop (cost=5088742.39..6705282.32 rows=30849 width=6)
> >
> >-> HashAggregate (cost=5088742.39..5089050.88 rows=30849
> >width=4)
> >
> > -> Index Scan using t2_bar_key on t2
> > (cost=0.00..5035501.50
> >
> > rows=21296354 width=4)
> >
> >Index Cond: (bar < '2010-11-01
> >00:00:00'::timestamp
> >
> > without time zone)
> >
> >Filter: (foo = 0)
> >
> >-> Index Scan using t1_pkey on t1 (cost=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 much from t2.
> >
> > When looking at the plan, I can easily imagine that data gets
> > accumulated below the nestedloop (thus using all that memory), but why
> > isn't each entry freed once one row has been deleted from t1 ? That
> > entry isn't going to be found again in t1 or in t2, so why keep it
> > around ?
> >
> > Is there a better way to write this query ? Would postgres 8.4/9.0
> > handle things better ?
>
> Do you have any DELETE triggers in t1 and/or t2?
No, there are triggers on insert/update to t1 which both insert into t2, but
no delete trigger. Deletions do cascade from t1 to t2 because of the foreign
key.
--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320
Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When the Session ends in PGSQL?
On Monday 04 July 2011 13:50:46 Durumdara wrote: > > The running query will be rolled back when the session ends. Note that > > "when the session ends" must be understood from the server's point of > > view, not the client's. > > As I understand you then running Queries forced to abort on the end of > Session and no more Query running over the session's ending point (or > after TCP connection broken). Yes. > > 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. > > Please explain a little this (Just 1-2 sentence please). > Or suggest me some info, if possible... :-) (Prayer) :-) > I want to know about 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, it can actually be entertaining :) -- 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
Re: [GENERAL] When the Session ends in PGSQL?
On Monday 04 July 2011 12:49:24 Durumdara wrote: > What happens with running statements and stored procs at Session's end? > > They will be aborted and destroyed, or they can continue the running? > > For example: > User X starts a long Query/STP. > But he is lost "in deep space" (like in "Space Oddity": > http://www.youtube.com/watch?v=rKE3FSPJu-4&feature=related). > The TCP connection aborted, then the Session is set to dead. > But I don't know what happens with this Query? Is it aborted by Server > or Running may infinitively? The running query will be rolled back when the session ends. Note that "when the session ends" must be understood from the server's point of view, not the client's. Temporary tables, prepared statements, and cursors (not an exhaustive list) will be destroyed too. 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 (0) 85 710 6320 Warning This message (and any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When the Session ends in PGSQL?
On Monday 04 July 2011 10:48:48 Durumdara wrote: > 1.) DataBases need to close the resources kept by Sessions. > 2.) There is need some "border", or a rule to split - which Session is > considered as "finished". So far so good. > The FireBird is uses "DummyPacketInterval" to recognize dead sessions, > EDB uses "Session Timeout" value for it. > The EDB can still alive if network connection lost, if before Timeout > the client also send a sign to the server. > > To I can use PGSQL also, I need to know the limitations, and how to > handle the connections, how to manage them. > > I wondering to PGSQL handle this with living TCP connections, because > this is may out of our control. > If we have a timeout value, we can control, which time we have, and > what time is acceptable for a Session. > For example: some places we have that uses wifi connections are > sometimes broken for just a little period. This is enough to > disconnect, but because of higher "Session Timeout" variable our > DataBase connections still alive without close the applications. > > Another thing is sign (packet). We must do something periodically to > keep alive the connection. For example: every 1 minutes we do some > dummy thing one server, like "select date" or etc. AFAIK postgres doesn't distinguish between a "TCP session" and a "database session" like (if I understand you correctly) FireBird/EDB does. You cannot reconnect and say "hello it's me again from session FOOBAR, can I resume that session ?". I believe you'll have to solve this at the application layer : * Make transactions, locks, temp tables, etc as short-lived as possible (this is always a good thing to do anyway). * If that's still not enough, store your "current working state" in a purpose- built table and add logic in your client to reinitialize session state using that data, and to purge the data after it has been used / 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 +33 (0) 142 119 325 +353 (0) 85 710 6320 Warning This message (and any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?
On Sunday 03 July 2011 07:47:01 Chris Travers wrote: > On Sun, Jul 3, 2011 at 7:25 AM, Ireneusz Pluta wrote: > > You may use dense_rank() (or even rank()) window function to map your > > sequence-with-gaps to a no-gap-id which will be used for exports. > > The typical case where gapless numbering comes up is something like this: > > In Greece, you go get invoice paper from the tax office which is > numbered in sequence and the government gets a list of the invoice > forms you have purchased. You then print the invoices on those paper > forms, and must number the invoices sequentially and without gaps. In > the case of an audit, all paper forms obtained must be accounted for > as must all gaps in numbering. You MUST be able to connect each > sequential invoice number (internally generated) to each invoice form > (numbered at the tax office). > > In this case you really have no choice but to lock some records, > generate a new gapless id, and save/print it. Naturally this causes > the sorts of problems mentioned. So the problem with Ireneuz's solution is that the mapping isn't stable enough. Then how about creating this mapping on disk whenever an export is done (assuming exports are much less frequent than inserts) ? * create table idmap(realid integer references realdata(id), gaplessid integer); * insert into realdata with the usual sequence * whenever an export of new data is requested : * lock table idmap * select * from realdata where id > (select max(realid) from idmap) and realdata.timestamp < now() - 'safety_margin_for_inserts_likely_to_rollback'::interval order by id for update; * insert into idmap * unlock table idmap * select gaplessid,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 any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
