[GENERAL] ERROR: tablespace "archive2" is not empty
Hi, I've run into an odd problem - I have what seems to be a "zombie" tablespace that PostgreSQL won't let me drop, but nothing inside it is active. % sudo -H -u postgres psql template1 -c "drop tablespace archive2;" ERROR: tablespace "archive2" is not empty % sudo find /media/ssd/archive2/ -type f -ls 36962439 393940 -rw--- 1 postgres postgres 403390464 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054 36962444 1048580 -rw--- 1 postgres postgres 1073741824 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.2 36962442 1048580 -rw--- 1 postgres postgres 1073741824 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056 36962446 808 -rw--- 1 postgres postgres 827392 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056_fsm 369624418 -rw--- 1 postgres postgres 8192 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054_vm 36962445 282384 -rw--- 1 postgres postgres 289161216 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.3 36962440 120 -rw--- 1 postgres postgres 122880 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054_fsm 36962443 1048580 -rw--- 1 postgres postgres 1073741824 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.1 % sudo -H -u postgres psql template1 -c "SELECT datname FROM pg_database WHERE oid = 117264;" datname -- mydb (1 row) % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);" oid | relname | relkind -+-+- (0 rows) % sudo -H -u postgres psql mydb -c "SELECT t.relname AS catalog, d.objid AS oid_dependent FROM pg_catalog.pg_class t JOIN pg_catalog.pg_depend d ON (t.oid = d.classid) WHERE refobjid in (7877054, 7877056);" catalog | oid_dependent -+--- (0 rows) % sudo -H -u postgres psql mydb -c "SELECT oid, datname FROM pg_database WHERE dattablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'archive2');" oid | datname -+- (0 rows) % sudo -H -u postgres psql giscloud -c "SELECT pg_tablespace_databases((SELECT oid FROM pg_tablespace WHERE spcname = 'archive2'));" pg_tablespace_databases - 117264 (1 row) So there are no actual references to these files, yet the relationship persists sufficiently for the DROP TABLESPACE to error out. I've reviewed what seems to be a similar example from 2008 on this list: http://grokbase.com/t/postgresql/pgsql-general/086g1yrpbq/error-when-trying-to-drop-a-tablespace but there was no ultimate solution then. I'm hoping there would be now :) Is it safe if I get rid of it by setting up a slave server to this machine, but then shut that postmaster down and manually remove the contents of this directory, bring it back up, let it get back in sync again, and then promote the slave to become the master, with all the accompanying arrangements on the side? TIA. -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > Hi, > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > > pg_catalog.pg_class where oid IN (7877054, 7877056);" > > oid | relname | relkind > > -+-+- > > (0 rows) > > That's the wrong query. The files on disk are relefilenodes not > oids. Try WHERE pg_relation_filenode(oid) IN ... Oh, okay, but still: % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);" pg_relation_filenode -- (1 row) % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877056);" pg_relation_filenode -- (1 row) -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 04:39:55AM -0500, Jim Nasby wrote: > On 10/19/15 4:14 AM, Josip Rodin wrote: > >On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > >>Hi, > >> > >>On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > >>>% sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > >>>pg_catalog.pg_class where oid IN (7877054, 7877056);" > >>> oid | relname | relkind > >>>-+-+- > >>>(0 rows) > >> > >>That's the wrong query. The files on disk are relefilenodes not > >>oids. Try WHERE pg_relation_filenode(oid) IN ... > > > >Oh, okay, but still: > > > >% sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);" > > pg_relation_filenode > >-- > > > >(1 row) > > > >% sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877056);" > > pg_relation_filenode > >-- > > pg_relation_filenode accepts the OID of a table. For what you're > trying to do you'd need pg_relation_filenode(tablespace oid, > relfilenode). That function, with two integer parameters, does not exist on this PostgreSQL (9.1). -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: > On 2015-10-19 11:14:33 +0200, Josip Rodin wrote: > > On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > > > Hi, > > > > > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > > > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > > > > pg_catalog.pg_class where oid IN (7877054, 7877056);" > > > > oid | relname | relkind > > > > -+-+- > > > > (0 rows) > > > > > > That's the wrong query. The files on disk are relefilenodes not > > > oids. Try WHERE pg_relation_filenode(oid) IN ... > > > > Oh, okay, but still: > > > > % sudo -H -u postgres psql mydb -c "SELECT pg_relation_filenode(7877054);" > > pg_relation_filenode > > -- > > > > (1 row) > > Please actually run the query I suggested > above. pg_relation_filenode(oid) returns the relfilenode of the table > with the passed in oid - which you then compare with the relfilenode you > saw on disk. Oh, sorry, but yet again, there's just nothing there: % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE pg_relation_filenode(oid) IN (7877054, 7877056);" oid | relname | relkind -+-+- (0 rows) -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: > What happens if you do?: > > select oid, * from pg_tablespace ; mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; oid | spcname | spcowner | spclocation | spcacl | spcoptions -+--+--+--++ 7849107 | archive2 | 10 | /media/archive2/psql || (1 row) > select * from pg_class where reltablespace = ; mydb=> select * from pg_class where reltablespace = 7849107; relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions -+--+-+---+--+---+-+---+--+---+---+---+-+-++-+--+---+++-+++--++ (0 rows) It's there, but it's not there-there :) -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: > On 10/19/2015 07:56 AM, Josip Rodin wrote: > >On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: > >>What happens if you do?: > >> > >>select oid, * from pg_tablespace ; > > > >mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; > >oid | spcname | spcowner | spclocation | spcacl | spcoptions > >-+--+--+--++ > > 7849107 | archive2 | 10 | /media/archive2/psql || > >(1 row) > > > >>select * from pg_class where reltablespace = ; > > > >mydb=> select * from pg_class where reltablespace = 7849107; > > relname | relnamespace | reltype | reloftype | relowner | relam | > > relfilenode | reltablespace | relpages | reltuples | reltoastrelid | > > reltoastidxid | relhasindex | relisshared | relpersistence | relkind | > > relnatts | relchecks | relhasoids | relhaspkey | relhasrules | > > relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions > >-+--+-+---+--+---+-+---+--+---+---+---+-+-++-+--+---+++-+++--++ > >(0 rows) > > > >It's there, but it's not there-there :) > > > > Should have added. > > What does a listing of pg_tblspc/ in your $PGDATA directory show? Ah, I forgot to mention. 7849107 is symlinked to /media/archive2/postgresql, so the symlink is referencing a real directory, and spclocation is broken because that doesn't exist. But that sounds like an -ENOENT and not -ENOPERM, no? -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 08:24:10AM -0700, Tom Lane wrote: > Josip Rodin writes: > > On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: > >>>> That's the wrong query. The files on disk are relefilenodes not > >>>> oids. Try WHERE pg_relation_filenode(oid) IN ... > > > Oh, sorry, but yet again, there's just nothing there: > > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > > pg_catalog.pg_class WHERE pg_relation_filenode(oid) IN (7877054, 7877056);" > > oid | relname | relkind > > -+-+- > > (0 rows) > > Seeing that those files are all of similar date, I wonder if they are > tables that got orphaned in a crash, ie, the pg_class rows were removed > but the backend crashed before physically unlinking the files. > > Anyway, if you've satisfied yourself that there are no pg_class entries > for these files, you could just manually remove the files. > > I concur with Adrian's nearby suggestion of checking for rows with > reltablespace matching the tablespace's OID before you do anything > drastic, though. OK, so given that that's all missing, too, I'm in the clear, then? How likely would it be for the server to, at some point in the future, to somehow deduce a new reference to that tablespace and do a FATAL when it finds nothing there? BTW how safe is the standby/failover idea in general? Changing pg_tblspc across promotions, so to speak. -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 11:15:08AM -0700, Adrian Klaver wrote: > >Sorry, that's another typo. It's the latter. The symlink is indeed pointing > >to the 5 GB of leftovers. > > This: > > % sudo find /media/ssd/archive2/ -type f -ls > 36962439 393940 -rw--- 1 postgres postgres 403390464 Jun 27 > 2014 /media/ssd/archive2 > > is a typo or is there another symlink involved? > > So, is there anything at /media/archive2/psql? This time I'll just paste command output to make sure it's verbatim :) % sudo ls -la /media/ssd/postgresql/9.1/main/pg_tblspc/7849107 lrwxrwxrwx 1 postgres postgres 30 Sep 30 2014 /media/ssd/postgresql/9.1/main/pg_tblspc/7849107 -> /media/ssd/archive2/postgresql % sudo ls -la $(sudo readlink -f /media/ssd/postgresql/9.1/main/pg_tblspc/7849107) total 12 drwx-- 3 postgres root 4096 Jun 25 2014 . drwxr-xr-x 3 root root 4096 Sep 30 2014 .. drwx-- 3 postgres postgres 4096 Jun 25 2014 PG_9.1_201105231 % sudo ls -la /media/archive2/psql ls: cannot access /media/archive2/psql: No such file or directory > Or more to the point how did spclocation and the symlink get to be > different? No idea, I inherited this machine. But like Tom said, spclocation being wrong is apparently harmless. -- 2. That which causes joy or happiness. -- 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] ERROR: tablespace "archive2" is not empty
On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver wrote: >On 10/19/2015 08:28 AM, Josip Rodin wrote: >> On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: >>> On 10/19/2015 07:56 AM, Josip Rodin wrote: >>>> On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: >>>>> What happens if you do?: >>>>> >>>>> select oid, * from pg_tablespace ; >>>> >>>> mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; >>>> oid | spcname | spcowner | spclocation | spcacl | >spcoptions >>>> >-+--+--+--++ >>>> 7849107 | archive2 | 10 | /media/archive2/psql || >>>> (1 row) >>>> >>>>> select * from pg_class where reltablespace = ; >>>> >>>> mydb=> select * from pg_class where reltablespace = 7849107; >>>> relname | relnamespace | reltype | reloftype | relowner | relam | >relfilenode | reltablespace | relpages | reltuples | reltoastrelid | >reltoastidxid | relhasindex | relisshared | relpersistence | relkind | >relnatts | relchecks | relhasoids | relhaspkey | relhasrules | >relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions >>>> >-+--+-+---+--+---+-+---+--+---+---+---+-+-++-+--+---+++-+++--++ >>>> (0 rows) >>>> >>>> It's there, but it's not there-there :) >>>> >>> >>> Should have added. >>> >>> What does a listing of pg_tblspc/ in your $PGDATA directory show? >> >> Ah, I forgot to mention. 7849107 is symlinked to >/media/archive2/postgresql, > >Except what you showed in you first post was: > >/media/ssd/archive2/postgresql > >Note the /media/ssd/* Sorry, that's another typo. It's the latter. The symlink is indeed pointing to the 5 GB of leftovers. >> so the symlink is referencing a real directory, and spclocation is >broken >> because that doesn't exist. But that sounds like an -ENOENT and not >> -ENOPERM, no? >> -- 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] ERROR: tablespace "archive2" is not empty
On Mon, Oct 19, 2015 at 12:02:17PM -0700, Adrian Klaver wrote: > >No idea, I inherited this machine. But like Tom said, spclocation being > >wrong is apparently harmless. > > Other then you cannot DROP the tablespace:) This will probably > needed to be resolved for the reasons that came up in this thread: > > http://www.postgresql.org/message-id/caaw2xfea1mpw4rw3-4lutw+y4ruqfpt5bx4c3dkub01f4ej...@mail.gmail.com > > it is a very long thread so you might to take a look at this: > > http://www.postgresql.org/message-id/7681.1389548...@sss.pgh.pa.us > > > What happens if you change spclocation to the correct location? The suggestion makes perfect sense, but sadly there's no difference: % sudo -H -u postgres psql template1 -c "update pg_tablespace set spclocation = '/media/ssd/archive2/postgresql' where spcname = 'archive2';" UPDATE 1 % sudo -H -u postgres psql template1 -c "drop tablespace archive2;" ERROR: tablespace "archive2" is not empty I guess UTSL would be my remaining recourse, but I was hoping someone who already knows that code path offhand could put me at ease :) -- 2. That which causes joy or happiness. -- 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] Where do I enter commands?
On Sun, Oct 25, 2015 at 07:57:48AM -0700, David Blomstrom wrote: > I can see PostgreSQL is going to have a learning curve - hopefully shorter > than the years it took me to learn MySQL - but it looks interesting. The > community seems painfully small compared to MySQL, and there are less > online resources. There might be a causal relation between your first and your second sentence ;) more stuff doesn't always mean more quality stuff. The community size discrepancy really isn't that bad these days. http://stackoverflow.com/questions/tagged/mysql - 350k http://stackoverflow.com/questions/tagged/postgresql - 45k Obviously the former number is larger, but it's not necessarily going to be relevant since the latter number is large enough to support the vast majority of typical questions that a newcomer is likely to have. And that's just for that one site (albeit a very popular one) - Google searches for typical PostgreSQL questions will often turn up fine answers from the archives of this mailing list. -- 2. That which causes joy or happiness. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simple functions, huge overhead, no cache
Hi, I went looking at why some of our queries using some custom functions were a bit sluggish. usercandoonobject(integer, integer, character, integer) does this: IF (isSuperuser(p_user_id)) THEN RETURN true; END IF; RETURN userCanDoOnObjectCheckGod($1, $2, $3, $4); issuperuser(integer) does: RETURN (SELECT userInGroup($1, 1000)); useringroup(integer, integer) does: IF ($2 = 1) THEN RETURN true; ELSE RETURN EXISTS( SELECT groups_users.users_id FROM groups_users WHERE groups_users.users_id = $1 AND groups_users.groups_id = $2 ); END IF; This is someone else's code, but it seems simple and clear enough to me... But on runtime, I get this: db=# explain analyze select issuperuser(id) from users; QUERY PLAN Seq Scan on users (cost=0.00..6624.00 rows=23000 width=4) (actual time=0.186..644.488 rows=23000 loops=1) Total runtime: 664.486 ms (2 rows) db=# explain analyze select userInGroup(id, 1000) from users; QUERY PLAN Seq Scan on users (cost=0.00..6624.00 rows=23000 width=4) (actual time=0.125..417.948 rows=23000 loops=1) Total runtime: 437.594 ms (2 rows) db=# explain analyze select case when users.id = 1 then true else exists(SELECT groups_users.users_id FROM groups_users WHERE groups_users.users_id = users.id AND groups_users.groups_id = '1000') end as issuperuser from users; QUERY PLAN -- Seq Scan on users (cost=0.00..191157.14 rows=23000 width=4) (actual time=0.053..94.756 rows=23000 loops=1) SubPlan -> Index Scan using groups_users_pkey on groups_users (cost=0.00..8.27 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=22999) Index Cond: ((groups_id = 1000) AND (users_id = $0)) Total runtime: 112.154 ms (5 rows) Why are the function calls four or six times slower than their own direct meaning? I gather from the documentation that there exists some function cache, which sounds good, and I could understand this overhead if writing to cache was its purpose, but even if I immediately just repeat the same query, I get exactly the same slow result (verified by using the actual queries and \timing in psql, not repeated 'explain analyze's). What am I missing? I tried to find an explanation in the documentation, to no avail. This is with PostgreSQL 8.3.11. (Please Cc: replies, I'm not subscribed. TIA.) -- 2. That which causes joy or happiness. -- 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] simple functions, huge overhead, no cache
Thank you both for the helpful explanations. On Sat, Jul 10, 2010 at 11:48:29AM -0400, Tom Lane wrote: > Oh, and one more thing: there is no "function cache". Reading http://www.postgresql.org/docs/8.3/static/plpgsql-implementation.html section 38.10.2. "Plan Caching" had put that idea in my head :) -- 2. That which causes joy or happiness. -- 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] simple functions, huge overhead, no cache
On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: > Meh, personally I'll stick to the good old profiling methods "is it fast > enough", "\timing", and "explain analyze". I agree. Some hint could be included in 'explain analyze' output, maybe just to separate the timings for things that are well covered by the query plan optimizer from those that aren't. I found this in a line like this: Filter: (approved AND (NOT archived) AND ((time_to > now()) OR (time_to IS NULL)) AND ((time_from < now()) OR (time_from IS NULL)) AND usercandoonobject(1, 1, 'news'::bpchar, news_id)) These other referenced columns in the filter were all insignificant (time-wise) compared to the single function call, but I had to find that out with a manual search. -- 2. That which causes joy or happiness. -- 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] simple functions, huge overhead, no cache
On Mon, Jul 12, 2010 at 04:38:48PM +0200, Pavel Stehule wrote: > 2010/7/12 Josip Rodin : > > On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: > >> Meh, personally I'll stick to the good old profiling methods "is it fast > >> enough", "\timing", and "explain analyze". > > > > I agree. Some hint could be included in 'explain analyze' output, maybe just > > to separate the timings for things that are well covered by the query plan > > optimizer from those that aren't. I found this in a line like this: > > it is useles for functions - explain doesn't show lines of executed > functions. Can you show some example of some more complex query. It doesn't have to show me any lines, but it could tell me which part of the query is actually being optimized, and OTOH which part is simply being executed N times unconditionally because it's a function that is marked as volatile. That alone would be a reasonable improvement. -- 2. That which causes joy or happiness. -- 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] simple functions, huge overhead, no cache
On Mon, Jul 12, 2010 at 07:46:30PM +0200, Pavel Stehule wrote: > 2010/7/12 Josip Rodin : > > On Mon, Jul 12, 2010 at 04:38:48PM +0200, Pavel Stehule wrote: > >> 2010/7/12 Josip Rodin : > >> > On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: > >> >> Meh, personally I'll stick to the good old profiling methods "is it fast > >> >> enough", "\timing", and "explain analyze". > >> > > >> > I agree. Some hint could be included in 'explain analyze' output, maybe > >> > just > >> > to separate the timings for things that are well covered by the query > >> > plan > >> > optimizer from those that aren't. I found this in a line like this: > >> > >> it is useles for functions - explain doesn't show lines of executed > >> functions. Can you show some example of some more complex query. > > > > It doesn't have to show me any lines, but it could tell me which part of > > the query is actually being optimized, and OTOH which part is simply being > > executed N times unconditionally because it's a function that is marked as > > volatile. That alone would be a reasonable improvement. > > this is different kinds of problems. You can have a very slow a > immutable function or very fast volatile function. And with wrong > function design your functions can be a 10 times slower. yeah - you > can multiply it via wrong or good design with wrong or good stability > flag. Well, it was demonstrated previously that the domain of very fast volatile plpgsql functions is inherently limited with their startup overhead, which makes them inherently slow with small data sets (and/or nesting). Sure, this can become relatively insignificant on very large data sets, but as long as there is a reasonable chance that this slows down a query by an order of magnitude, IMHO it would be better to note it than to ignore it. -- 2. That which causes joy or happiness. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] freeradius postgresql sql query glitch
Hi,
I've observed an SQL logging problem with FreeRADIUS (2.x) and PostgreSQL
(8.1), on several different installations I occasionally get these errors:
Mon Dec 7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
accounting STOP record - ERROR: invalid input syntax for integer: ""
sql trace log indicates that this is the offending query:
UPDATE radacct
SET AcctStopTime = ('2009-12-07 13:19:01'::timestamp - '6'::interval),
AcctSessionTime = CASE WHEN '' = '' THEN
(EXTRACT(EPOCH FROM ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME ZONE -
AcctStartTime::TIMESTAMP WITH TIME ZONE
- '6'::INTERVAL)))::BIGINT ELSE '' END,
AcctInputOctets = (('0'::bigint << 32) + '0'::bigint),
AcctOutputOctets = (('0'::bigint << 32) + '0'::bigint),
AcctTerminateCause = 'User-Request',
AcctStopDelay = 0,
FramedIPAddress = NULLIF('4.3.2.1', '')::inet,
ConnectInfo_stop = ''
WHERE AcctSessionId = '57fc9e4821466d86'
AND UserName = 'o...@user.name'
AND NASIPAddress = '1.2.3.4'
AND AcctStopTime IS NULL;
I'm using the default unchanged sql/postgresql/dialup.conf setting:
accounting_stop_query = "UPDATE ${acct_table2} \
SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
(EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
AcctStartTime::TIMESTAMP WITH TIME ZONE \
- '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct-Session-Time}'
END, \
AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) +
'%{%{Acct-Input-Octets}:-0}'::bigint), \
AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) +
'%{%{Acct-Output-Octets}:-0}'::bigint), \
AcctTerminateCause = '%{Acct-Terminate-Cause}', \
AcctStopDelay = 0, \
FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
ConnectInfo_stop = '%{Connect-Info}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{NAS-IP-Address}' \
AND AcctStopTime IS NULL"
Looks like the code wants to use CASE to check whether %{Acct-Session-Time}
exists among the internal FreeRADIUS variables, while the return value of
the whole SQL CASE construct is supposed to be a bigint.
This is a reduced failing case:
radiustmobile=# select CASE WHEN '' = '' THEN (EXTRACT(EPOCH FROM ('2009-12-07
13:19:01'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE -
'6'::INTERVAL)))::BIGINT ELSE '' END from radacct where AcctSessionId =
'57fc9e4821466d86';
ERROR: invalid input syntax for integer: ""
In the else case, this fallback return value comes into PostgreSQL as just
an empty string, which causes it to trip over - it sees that there's a
possibility to write an empty string into a bigint field, which provokes
the syntax error, even if the problem won't actually happen with this
particular setup of input data.
I'm not sure what to do... can the query be rewritten in a manner that would
allow for both use cases?
(Mailing list users, please Cc: responses for those of us who may not be
subscribed. TIA.)
--
2. That which causes joy or happiness.
--
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] freeradius postgresql sql query glitch
On Mon, Dec 07, 2009 at 10:02:39PM +, Adrian Klaver wrote:
> > Mon Dec 7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
> > accounting STOP record - ERROR: invalid input syntax for integer: ""
> >
> > accounting_stop_query = "UPDATE ${acct_table2} \
> > SET
> > AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
> > (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
> > AcctStartTime::TIMESTAMP WITH TIME ZONE \
> > - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE
> > '%{Acct-Session-Time}' END, \
> >
> > I'm not sure what to do... can the query be rewritten in a manner that
> > would
> > allow for both use cases?
>
> If I understand correctly the below may work. If %{Acct-Session-Time} is
> an empty string it will return NULL otherwise it will return
> %{Acct-Session-Time}.
>
> ELSE
> NULLIF('%{Acct-Session-Time}','') END,
Thanks, that should work, with a slight modification - explicit cast to
'bigint', because a nullif()'ed '' is still a 'text' by default.
The two cases then evaluate like this:
pgsql=# select CASE WHEN '' = '' THEN 1234::BIGINT ELSE NULLIF('', '')::BIGINT
END AS value;
value
---
1234
(1 row)
pgsql=# select CASE WHEN '13' = '' THEN 1234::BIGINT ELSE NULLIF('13',
'')::BIGINT END AS value;
value
---
13
(1 row)
--
2. That which causes joy or happiness.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] relacl parsing method?
Hi,
I want to find out whether a user has a select privilege on a particular
database. This is what I see when it does:
# select relacl from pg_class where relname = 'mydbtable';
relacl
--
{mydbname=arwdxt/mydbname,mydbuser=r/mydbname}
(1 row)
Is this the best way to parse that easily from within PostgreSQL:
# select 1 from pg_class where relname = 'mydbtable' and relacl ~
'mydbuser=r/mydbname';
?column?
--
1
(1 row)
# select 1 from pg_class where relname = 'mydbtable' and relacl ~
'mydbuser=w/mydbname';
?column?
--
(0 rows)
Where is this documented? I tried searching for 'relacl' and 'aclitem' in
the docs, but didn't come up with much.
I did find a Perl module at http://search.cpan.org/~dwheeler/Pg-Priv-0.10/
that seems to extract relacl and parse it on its own, which sounds like a
kludge.
(Please Cc: replies, I'm not subscribed. TIA.)
--
2. That which causes joy or happiness.
--
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] relacl parsing method?
On Tue, Feb 16, 2010 at 12:22:29PM +0100, joy wrote:
> I want to find out whether a user has a select privilege on a particular
> database. This is what I see when it does:
>
> # select relacl from pg_class where relname = 'mydbtable';
> relacl
> --
> {mydbname=arwdxt/mydbname,mydbuser=r/mydbname}
> (1 row)
>
> Is this the best way to parse that easily from within PostgreSQL:
>
> # select 1 from pg_class where relname = 'mydbtable' and relacl ~
> 'mydbuser=r/mydbname';
> ?column?
> --
> 1
> (1 row)
>
> # select 1 from pg_class where relname = 'mydbtable' and relacl ~
> 'mydbuser=w/mydbname';
> ?column?
> --
> (0 rows)
Hmm, sorry, it looks like the string after the slash (/) is grantor, rather
than database name. If I omit it, then it warns about defaulting grantor to
user ID 10. Is there any way to check for any grantor?
--
2. That which causes joy or happiness.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
