Re: [BUGS] BUG #4913: Row missing from primary key index
On Fri, Jul 10, 2009 at 2:02 AM, Alvaro Herrera wrote: > What do the INSERT lines look like? Is it a trigger, an insert called > directly by the application? How is the sequence involved -- lastval(), > nextval(), does the code just leave the column out for the default to fire? > > * This is the INSERT query, called from PHP/Apache INSERT INTO log_event (user_id, ip, action_id, object1_id, object2_id, event_timestamp) VALUES ($1, $2, $3, $4, $5, NOW()) So the timestamp is generated by NOW(), and the pkey is generated by using the default value (nextval). * There are no UPDATE queries. * There a few DELETE queries that are lauched daily (called from PHP/cron) DELETE FROM log_event WHERE action_id = $1 AND event_timestamp < NOW() - '1 month'::interval (some events are not worthy of archiving individually, we just keep totals) Regards, Mathieu
Re: [BUGS] BUG #4913: Row missing from primary key index
Mathieu De Zutter writes: > On Fri, Jul 10, 2009 at 2:02 AM, Alvaro Herrera > wrote: >> What do the INSERT lines look like? > * This is the INSERT query, called from PHP/Apache > INSERT INTO log_event (user_id, ip, action_id, object1_id, object2_id, > event_timestamp) > VALUES ($1, $2, $3, $4, $5, NOW()) > So the timestamp is generated by NOW(), and the pkey is generated by using > the default value (nextval). The timestamps are invariably generated by NOW()? That seems impossible given that Andrew's report shows different timestamps for the same insert XID. But actually, you have to believe about six impossible things in order to credit that Andrew's report represents the behavior of a normally-functioning Postgres system. What I am thinking is that we are looking at the results of multiple disconnected backends hacking on the same database files --- ie, there's more than one active shared memory block. That would explain the apparent re-use of XIDs (they're being handed out separately in each shmem block) as well as disappearance of index entries (a write from either shmem block would wipe out recent changes in that page from the other one). This could be checked by looking at the output of "ipcs -m" (run this as root to be sure you get everything). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4913: Row missing from primary key index
On Fri, Jul 10, 2009 at 5:03 PM, Tom Lane wrote: > This could be checked by looking at the output of "ipcs -m" > (run this as root to be sure you get everything). > $ sudo ipcs -m -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x0052e2c1 1081344postgres 60030384128 21 0x 425985 postgres 60018440 34 0x 458754 postgres 60016384 34 0x 491523 postgres 60076834 0x 524292 postgres 60012 34 Note that I stopped/started postgresql last night. Regards, Mathieu
Re: [BUGS] BUG #4913: Row missing from primary key index
Mathieu De Zutter writes: > $ sudo ipcs -m > -- Shared Memory Segments > keyshmid owner perms bytes nattch status > 0x0052e2c1 1081344postgres 60030384128 21 > 0x 425985 postgres 60018440 34 > 0x 458754 postgres 60016384 34 > 0x 491523 postgres 60076834 > 0x 524292 postgres 60012 34 Hmm, I wonder what those last four are? Surely they are too small to be Postgres shmem segments, but if they're something else like X11 segments then why do we only see ones belong to user postgres? I think it's probably irrelevant to the problem, but just wondering ... > Note that I stopped/started postgresql last night. The evidence may be gone then. I still think that disconnected backends are the most plausible explanation, but you might have killed everything off. As far as anyone knows, it's impossible to get into the disconnected-backends state unless (a) you manually remove the postmaster.pid file that provides the interlock against it, or (b) you're trying to run multiple copies of Postgres on different machines that're sharing an NFS or SAN mount of the same database files. I assume you'd have mentioned it if you were trying (b), but I wonder whether you have an init script that takes it on itself to remove the postmaster.pid file? It's generally considered irresponsible for an init script to do that, but I'm not sure every distro has gotten the word. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4914: uuid_generate_v4 not present in eithersource or yum/rpm
Dan Boeriu wrote: > Could you please tell me how you installed it? What exact version did you > download? Was it source or yum/rpm? > I tried both with source compiling with the flags for oops-uuid and still > didn't get the function uuid_generate_v4. > > If you installed from source please send me the configure flags you used. > If you installed from yum/rpm please tell me what version/platform. I compiled from source. Did you enter the contrib/uuid-ossp directory and ran make install in there? $ pg_config --configure '--enable-debug' '--enable-depend' '--enable-cassert' '--enable-nls' '--cache-file=/home/alvherre/tmp/pgconfig.84_rel.cache' '--enable-thread-safety' '--with-python' '--with-perl' '--with-tcl' '--with-openssl' '--with-libxml' '--with-ossp-uuid' '--prefix=/pgsql/install/84_rel' '--with-pgport=55516' -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 48' 55.3", W 73º 15' 24.7" Syntax error: function hell() needs an argument. Please choose what hell you want to involve. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4913: Row missing from primary key index
On Fri, Jul 10, 2009 at 6:24 PM, Tom Lane wrote: > As far as anyone knows, it's impossible to get into the > disconnected-backends state unless (a) you manually remove the > postmaster.pid file that provides the interlock against it, or > (b) you're trying to run multiple copies of Postgres on different > machines that're sharing an NFS or SAN mount of the same database files. > I assume you'd have mentioned it if you were trying (b), but I wonder > whether you have an init script that takes it on itself to remove the > postmaster.pid file? It's generally considered irresponsible for an > init script to do that, but I'm not sure every distro has gotten the word. > (a) I can't remember doing that, but who knows I got frustrated at some point in time and took a wrong shortcut. (b) I've only got local RAID storage. I've checked the debian init script, and the only thing related to the pid file I found was creating the underlying directory /var/run/postgresql Regards, Mathieu
[BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)
The following bug has been logged online: Bug reference: 4916 Logged by: Richard Neill Email address: rn...@cam.ac.uk PostgreSQL version: 8.4 Operating system: Linux Description:wish: more statistical functions (median, percentiles etc) Details: In addition to the existing aggregate functions (avg, stddev etc), it would be nice if postgres could return further information. For example, the quartiles, percentiles, and median. [mode would also be useful, as an explicit function, though we can get it easily enough using count(1) order by count desc]. According to google, this has been a wish since at least year 2000 for various people, but doesn't seem to be implemented. Thanks - Richard -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)
On Fri, Jul 10, 2009 at 11:37:46PM +, Richard Neill wrote: > In addition to the existing aggregate functions (avg, stddev etc), it would > be nice if postgres could return further information. For example, the > quartiles, percentiles, and median. > > [mode would also be useful, as an explicit function, though we can get it > easily enough using count(1) order by count desc]. > > According to google, this has been a wish since at least year 2000 for > various people, but doesn't seem to be implemented. That's because no one has yet taken the time. However, patches are welcome, if you'd like it enough to implement it. It's on my list of things that might be interesting to write, for example, but there are other things higher up on that list. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)
On Sat, Jul 11, 2009 at 02:12:22AM +0100, Richard Neill wrote: > Thanks for your reply. Sadly, I haven't the time (or expertise) to write > this myself. However, the feature would be really useful to have. I'd > certainly be willing to make a £200 payment or donation in return. That's very nice of you to make the offer. Pending someone taking you up on it, you might consider your ability to write the functions in some procedural language. They would probably be easier to write, and you'd only have to make them handle data types you're planning to use them with. For instance, there's an example of PL/Perl versions available embedded in the code here: http://tr.im/rPDA -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)
Dear Joshua, Thanks for your reply. Sadly, I haven't the time (or expertise) to write this myself. However, the feature would be really useful to have. I'd certainly be willing to make a £200 payment or donation in return. I'm aware that this number is problematic, as it undervalues developer time (£200 is the value, to me, of that feature; which is probably uncorrelated with the value of his time to anyone who might implement it). The offer is there if anyone wants to take it; please feel free to contact me off-list. Richard Joshua Tolley wrote: On Fri, Jul 10, 2009 at 11:37:46PM +, Richard Neill wrote: In addition to the existing aggregate functions (avg, stddev etc), it would be nice if postgres could return further information. For example, the quartiles, percentiles, and median. [mode would also be useful, as an explicit function, though we can get it easily enough using count(1) order by count desc]. According to google, this has been a wish since at least year 2000 for various people, but doesn't seem to be implemented. That's because no one has yet taken the time. However, patches are welcome, if you'd like it enough to implement it. It's on my list of things that might be interesting to write, for example, but there are other things higher up on that list. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)
2009/7/11 Richard Neill : > > The following bug has been logged online: > > Bug reference: 4916 > Logged by: Richard Neill > Email address: rn...@cam.ac.uk > PostgreSQL version: 8.4 > Operating system: Linux > Description: wish: more statistical functions (median, percentiles > etc) > Details: > > In addition to the existing aggregate functions (avg, stddev etc), it would > be nice if postgres could return further information. For example, the > quartiles, percentiles, and median. > > [mode would also be useful, as an explicit function, though we can get it > easily enough using count(1) order by count desc]. > > According to google, this has been a wish since at least year 2000 for > various people, but doesn't seem to be implemented. > > Thanks - Richard hello you can use following tricks: create or replace function nth_percentil(anyarray, int) returns anyelement as $$ select $1[$2/100.0 * array_upper($1,1) + 1]; $$ language sql immutable strict; pagila=# select nth_percentil(array(select length from film order by 1),90); nth_percentil --- 173 (1 row) pagila=# select count(case when length < 173 then 1 end)::float / count(*) * 100.0 from film; ?column? -- 89.6 (1 row) create or replace function median(anyarray) returns float as $$ select ($1[round(array_upper($1,1)/2.0)] + $1[array_upper($1,1) - round(array_upper($1,1)/2.0) + 1]) / 2.0::float; $$ language sql immutable strict; pagila=# select median(array[1,2]), median(array[1,2,3]), median(array[1,2,3,4]); median | median | median ++ 1.5 | 2 |2.5 (1 row) pagila=# select median(array(select length from film order by 1)); median 114 (1 row) tested on pagila database regards Pavel Stehule > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
