Re: [GENERAL] Problem with planner choosing nested loop
Alban Hertroys wrote: > > On Apr 2, 2008, at 9:02 PM, Alex Solovey wrote: >> The reduced database example has the same problem in EXPLAIN ANALYZE >> as production one, here: >> >> Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual >> time=0.098..3.561 rows=24 loops=1) > > Hang on... You prefer sequential scans because indexes make your > database too slow, but you don't want a sequential scan now? What kind > of solution do you expect then? An oracle maybe? It sounds to me like the issue is with *multiple* sequential scans inside a nested loop instead of the single sequential scan expected. The quoted explain line reflects a claimed cost misestimation, rather than being a claim that sequential scans in general are not desired. > You will need an index if this query is too slow for you, or you will > have to live with the slowness of this query. Pick one ;) He's already noted that it's preferable to avoid adding indexes due to insert/update performance issues. -- Craig Ringer -- 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] Foreign keys causing conflicts leading toserialization failures
Tom Lane wrote: > >> This is what I am wondering. Whether it is done this way due to > >> expecation/standard, or as an implementation side effect. In the > >> latter case it is fixable. > > > I don't see how this could break a standard. > > Actually, I think it does, because we went to great lengths to cause > this case to error out. It would be much simpler, code-wise, if the > RI checks just always used a current snapshot and didn't worry about > whether serializability had been violated. > > (Albe's description of the implementation is largely fiction, but the > conclusion is accurate: we throw error if the referenced PK row has been > updated since the serializable transaction started. The exact nature > of the update is not considered.) I am aware that I know nothing of the implementation and only can describe the behaviour... Of course a serializable transaction cannot just use the current index entry for verifying referential integrity, because then it might not behave consistently with the transaction snapshot. What I mean is: if the serializable transaction went out of its way to check if the update it wants to make is both consistent with its snapshot and the current index row, it should not violate anything to allow that update. The index entry would not be changed in that case. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Array operator "sum array values" + matching dimensions
> create or replace function sum_elements(anyarray) > returns anyelement as $$ > select sum($1[i]) >from generate_series(array_lower($1,1), >array_upper($1,1)) g(i); > $$ language sql immutable; Thank you! Anyway what I was really asking was a "$" (or whatever other symbol) in place of ":" for sum, that is a notation like myarray_col[12$16] would give the sum of all values from myarray_col[12] to myarray_col[16] Anyway, I was reading that multidimensional arrays must have matching dimensions (that is, they can't be, for example, [5][9]). Is this right? Why is that? Thank you Inviato da Yahoo! Mail. La casella di posta intelligente. http://it.docs.yahoo.com/mail/overview/index.html -- 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 update queries take a long time - postgres 8.3.1
On Wednesday 2. April 2008, Tomasz Ostrowski wrote: >Go for it. Even 64 (I like round numbers) would not be too much. Geek test: Do you find the above statement odd? Yes: 0, No: +10. (Sorry for being massively off-topic :-)) -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is it helpful for the optimiser/planner to add LIMIT 1
I'm reviewing some function I wrote to add stable, immutable where needed and I'd like to take the chance to add further "cheap" optimisation if it helps. There are many places where I know a function or a statement will return just one row? Is it helpful to add LIMIT 1? eg. select a, b from myfunction(3,5) limit 1; select into a,b x,y from tablename where z=5 and u=7 limit 1; select a,b from from tablename where z=5 and u=7 limit 1; thx -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] (FAQ?) JOIN condition - 'WHERE NULL = NULL'
> > We are annotating nodes on a hierarchical structure > > where NULL implied an > > I don't mean to be rude, but yuck. Why provide a > record for data that isn't there? > No offence taken - I'm trying to improve an old (partially inherited) system, hence the original post. I have no idea if this model would work for you, but > maybe it will help. > That approach all sounds sensible, however going through the links that Richard Broersma sent over in a previous post - I'm currently leaning towards the flexibility and intuitive interface that ltree contrib module appears to offer. http://www.postgresql.org/docs/8.3/interactive/ltree.html Cheers, Ian
Re: [GENERAL] simple update queries take a long time - postgres 8.3.1
On Thu, Apr 3, 2008 at 9:18 AM, Leif B. Kristensen <[EMAIL PROTECTED]> wrote: > On Wednesday 2. April 2008, Tomasz Ostrowski wrote: > > >Go for it. Even 64 (I like round numbers) would not be too much. > > Geek test: Do you find the above statement odd? Sadly, no. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about complex type
hi, now i meet a question about the complex field. Firstly i create a complex
type (the sql is "CREATE TYPE test11 as (area numeric, address character
varying(30))")and then i create a table ,set a field named "tt" whose type is
test11. Now i want insert the data which select from another talble
yantian_2005 into the tt of test1. but i try some SQL and always failure. so i
wish you can tell me how to do .The following is my sql and their error.
(1)select (eid,address,area1,marea,shape_area,shape_len,the_geom,gid)into
table1 from yantian_2005 where gid=2
ERROR: column "row" has pseudo-type record
(2)insert into test1(tt)select row(yantian_2005.address, yantian_2005.area1
)from yantian_2005 where gid=3;
ERROR: cannot cast type record to test11
(3)select Row (yantian_2005.address,yantian_2005.area1)::{tt} into table1 from
yantian_2005 where gid=2
ERROR: syntax error at or near "{"
LINE 1: ...ct Row (yantian_2005.address,yantian_2005.area1)::{tt} into ...
please tell me how to do.
my postgresql is 8.2 and system is windows XP.
xiaochun wu
---
外企高薪职位急聘(
http://d1.sina.com.cn/sina/limeng3/mail_zhuiyu/2008/mail_zhuiyu_20080331.html )
---
注册新浪2G免费邮箱（http://mail.sina.com.cn/）
Re: [GENERAL] is it helpful for the optimiser/planner to add LIMIT 1
"Ivan Sergio Borgonovo" <[EMAIL PROTECTED]> writes: > I'm reviewing some function I wrote to add stable, immutable where > needed and I'd like to take the chance to add further "cheap" > optimisation if it helps. > > There are many places where I know a function or a statement will > return just one row? > > Is it helpful to add LIMIT 1? > > eg. > select a, b from myfunction(3,5) limit 1; > select into a,b x,y from tablename where z=5 and u=7 limit 1; > select a,b from from tablename where z=5 and u=7 limit 1; In such simple queries the limit 1 won't do anything. In more complex queries it could help correct any problems higher up in the query caused by bad planner estimations. For example select * from a join (select x from myfunction(3,5) limit 1) as b(i) using (i) would work better than without the limit because without it the planner would have no idea that myfunction is only going to return 1 record. You could fix that more cleanly with "ALTER FUNCTION myfunction ROWS 1" but only if that's always true, not just for myfunction(3,5). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- 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] is it helpful for the optimiser/planner to add LIMIT 1
Gregory Stark <[EMAIL PROTECTED]> writes: > You could fix that more cleanly with "ALTER FUNCTION myfunction ROWS 1" but > only if that's always true, not just for myfunction(3,5). Perhaps the function shouldn't be declared SETOF in the first place? regards, tom lane -- 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] is it helpful for the optimiser/planner to add LIMIT 1
On Thu, 03 Apr 2008 10:33:56 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > > You could fix that more cleanly with "ALTER FUNCTION myfunction > > ROWS 1" but only if that's always true, not just for > > myfunction(3,5). > > Perhaps the function shouldn't be declared SETOF in the first place? Does I have to take it as: if it is not declared as SETOF all optimisation and planning marvels will happen without I add LIMIT 1? What about queries? Is it an information that is used for anything else other than stopping earlier? Summarising it up: is it worth to add it here and there as an optimisation flag? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] deadlock
Hi, I've come across a strange deadlock that I need your help with. There are two copies of the same Perl daemon running on a 2 cpu box. The program is pretty simple (because I wrote it :)) so I can trace its pathway fairly well: in it, there's a single "LOCK table foo" occurring part way through a transaction that sometimes ends up as this: DETAIL: Process 91376 waits for AccessExclusiveLock on relation 16488 of database 16386; blocked by process 92387. Process 92387 waits for AccessExclusiveLock on relation 16488 of database 16386; blocked by process 91376. After the exclusive lock, there is also exactly one SELECT, and then one UPDATE query involving table foo, among others, doing their usual implicit locking on it. I've read in the manuals that it's okay to stack locks this way as long as the more restrictive locks precede less restrictive ones. Mind you, there may be many requests per second, and some of them can and will happen at the same wall clock time due to 2 cpus at work. Can locking break under these circumstances? I'd rather opt for an educated solution to this, than having to check and restart the query. PostgreSQL 8.3.1 FreeBSD 7.0 p5-DBI-1.60.1 p5-DBD-Pg-1.49 Thanks for any tips. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > Please see the attached patch. One change I made is to hold the SHARE lock > on the page while ANALYZE is reading tuples from it. I thought it would > be a right thing to do instead of repeatedly acquiring/releasing the lock. I've applied a modified/extended form of this patch for 8.3.2. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] choosing the right locking mode
Given this type query: UPDATE bw_pool SET user_id=? WHERE bw_id= (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) RETURNING bw_id The idea is to "single-threadedly" get at the next available empty slot, no matter how many such queries run in parallel. So far I've been semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it deadlocks sometimes. Maybe I could use some less restrictive locking mode and prevent possible collisions at the same time? Thanks. -- 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] deadlock
On Thu, Apr 3, 2008 at 10:29 AM, rihad <[EMAIL PROTECTED]> wrote: > Hi, > > I've come across a strange deadlock that I need your help with. There are > two copies of the same Perl daemon running on a 2 cpu box. The program is > pretty simple (because I wrote it :)) so I can trace its pathway fairly > well: in it, there's a single "LOCK table foo" occurring part way through a > transaction that sometimes ends up as this: For what reason is the table being locked? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Thu, Apr 3, 2008 at 10:02 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > I've applied a modified/extended form of this patch for 8.3.2. > Thanks. I had another concern about VACUUM not reporting DEAD line pointers (please see up thread). Any comments on that ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Secure "where in(a,b,c)" clause.
Hi All I hope this isn't a FAQ, but does anyone have any suggestions as to how to make a query that selects using: "where in()" secure from an sql injection point of view? I have grid of tiles I'm using to reference geographical points. These tiles are identical to the tiling system google maps uses. My google maps application works out the tiles it wants to display as a list of tile names, and sends this list to a php script. This works very well, however I'm currently directly concatenating a sql query: select st_collect(the_geom) from tiles where tilename in ()) Which leaves my application vulnerable to sql injection. As the length of the comma delimited list is highly variable I don't think I can use a prepared query to increase security. Thanks Will -- 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] choosing the right locking mode
On Thu, Apr 3, 2008 at 10:44 AM, rihad <[EMAIL PROTECTED]> wrote: > Given this type query: > > UPDATE bw_pool > SET user_id=? > WHERE bw_id= > (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) > RETURNING bw_id > > The idea is to "single-threadedly" get at the next available empty slot, no > matter how many such queries run in parallel. So far I've been > semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it > deadlocks sometimes. Maybe I could use some less restrictive locking mode > and prevent possible collisions at the same time? So, is there some reason a sequence won't work here? If you've got a requirement for a no-gap id field, there are other, less locky-ish ways to do it. Locking the table doesn't scale, and that's likely what problem you're seeing. -- 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] PG 8.3.x doesn't get build
Tom Lane wrote: > Andreas <[EMAIL PROTECTED]> writes: > > make[3]: *** No rule exists for Target �utf8_and_euc_jis_2004.o�, > > needed to create �libutf8_and_euc_jis_2004.so.0.0�. END. > > There are two or three reports like this in the archives. It appears to > be related to using an old version of "tar" that fails to extract such > long filenames correctly. Please install the latest tar you can get > your hands on, and see if it gets better... Also, do not use Midnight Commander to extract the files. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] deadlock
rihad wrote: > Hi, > > I've come across a strange deadlock that I need your help with. There > are two copies of the same Perl daemon running on a 2 cpu box. The > program is pretty simple (because I wrote it :)) so I can trace its > pathway fairly well: in it, there's a single "LOCK table foo" occurring > part way through a transaction that sometimes ends up as this: > > DETAIL: Process 91376 waits for AccessExclusiveLock on relation 16488 > of database 16386; blocked by process 92387. > Process 92387 waits for AccessExclusiveLock on relation 16488 of > database 16386; blocked by process 91376. If there are only two processes, and each is waiting for an ACCESS EXCLUSIVE lock on the same relation and being blocked by the other one, then presumably both have weaker locks that conflict with ACCESS EXCLUSIVE on that relation. Process 1 can't proceed with the ACCESS EXCLUSIVE lock because process 2 has a lesser lock on the table. Process 2 can't proceed with the ACCESS EXCLUSIVE lock because process 1 has a lesser lock on the table. Deadlock. I don't see any other way the situation could arise, but I'm *very* far from an expert. Note that many statements take out fairly weak locks on a table. See: http://www.postgresql.org/docs/8.3/static/explicit-locking.html In particular, even a basic SELECT takes out an ACCESS SHARE, which conflicts with ACCESS EXCLUSIVE. If you are going to lock the table with ACCESS EXCLUSIVE you need to either: - Take out the ACCESS EXCLUSIVE lock before doing anything else with the table; - Rewrite to avoid the need for the ACCESS EXCLUSIVE lock (say, by using appropriate SELECT ... FOR UPDATE/SHARE row level locking); or - Be prepared to retry transactions when deadlocks arise I'd prefer to avoid the exclusive lock entirely if possible, and failing that I'd want to take it out before doing anything else. > After the exclusive lock, there is also exactly one SELECT But what about BEFORE the LOCK statement? That's what matters. > I've read in the manuals that it's okay to stack > locks this way as long as the more restrictive locks precede less > restrictive ones. Yep, and since ACCESS EXCLUSIVE is the most restrictive lock you need to take it out before doing ANYTHING else if you can't prove through other means (say, knowledge about locking on other tables/records) that a deadlock won't arise. > Mind you, there may be many requests per second, and > some of them can and will happen at the same wall clock time due to 2 > cpus at work. Can locking break under these circumstances? I'd be VERY surprised. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > Thanks. I had another concern about VACUUM not reporting DEAD line > pointers (please see up thread). Any comments on that ? If you want to work on that, go ahead, but I wanted it separate because I didn't think it merited back-patching. It's strictly cosmetic in terms of being about what VACUUM VERBOSE prints, no? regards, tom lane -- 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] Secure "where in(a,b,c)" clause.
> I hope this isn't a FAQ, but does anyone have any > suggestions as to > how to make a query that selects using: > "where in()" > secure from an sql injection point of view? > > As the length of the comma delimited list is highly > variable I don't > think I can use a prepared query to increase > security. > Prepared query, no.. but you can still use parameter binding. Determine how many parameters you need, and create a query like this: where in ($1, $2, $3, $4, $5) and then bind each of those parameters. This works well enough for small numbesr of parameters. Somebody else will have to answer if there's a better way for larger quantities. -- 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] Secure "where in(a,b,c)" clause.
On Thu, Apr 3, 2008 at 11:50 AM, William Temperley
<[EMAIL PROTECTED]> wrote:
> This works very well, however I'm currently directly concatenating a sql
> query:
>
> select st_collect(the_geom) from tiles where tilename in
> ())
>
> Which leaves my application vulnerable to sql injection.
>
> As the length of the comma delimited list is highly variable I don't
> think I can use a prepared query to increase security.
Use a prepared query and ANY, e.g.:
select st_collect(the_geom) from tiles
where tilename = any('{foo,bar,baz}');
--
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] Secure "where in(a,b,c)" clause.
On Thu, Apr 3, 2008 at 9:50 AM, William Temperley <[EMAIL PROTECTED]> wrote: > Hi All > > I hope this isn't a FAQ, but does anyone have any suggestions as to > how to make a query that selects using: > "where in()" > secure from an sql injection point of view? I have an idea, but I can't comment if it is a good idea since I haven't tried it. Maybe you can create a temp table for each user, insert the values you want into the table, and lastly perform a join on your foo table with the user's temp table. This hopefully would leave anything open for injection. When you are done just drop the temp table. -- Regards, Richard Broersma Jr. -- 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] choosing the right locking mode
Scott Marlowe wrote: On Thu, Apr 3, 2008 at 10:44 AM, rihad <[EMAIL PROTECTED]> wrote: Given this type query: UPDATE bw_pool SET user_id=? WHERE bw_id= (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) RETURNING bw_id The idea is to "single-threadedly" get at the next available empty slot, no matter how many such queries run in parallel. So far I've been semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it deadlocks sometimes. Maybe I could use some less restrictive locking mode and prevent possible collisions at the same time? So, is there some reason a sequence won't work here? bw_pool is pre-filled with 10 thousand rows of increasing bw_id, each of which is either set (user_id IS NOT NULL) or empty (user_id IS NULL). The state of each can change any time. If you've got a requirement for a no-gap id field, there are other, less locky-ish ways to do it. Locking the table doesn't scale, and that's likely what problem you're seeing. There's a shared resource backed by bw_pool that I absolutely need single-threaded access to, despite multiple cpus, hence an all-exclusive lock (or?..) -- 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] Secure "where in(a,b,c)" clause.
On Apr 3, 2008, at 9:50 AM, William Temperley wrote:
Hi All
I hope this isn't a FAQ, but does anyone have any suggestions as to
how to make a query that selects using:
"where in()"
secure from an sql injection point of view?
I have grid of tiles I'm using to reference geographical points.
These tiles are identical to the tiling system google maps uses. My
google maps application works out the tiles it wants to display as a
list of tile names, and sends this list to a php script.
This works very well, however I'm currently directly concatenating a
sql query:
select st_collect(the_geom) from tiles where tilename in
())
Which leaves my application vulnerable to sql injection.
As the length of the comma delimited list is highly variable I don't
think I can use a prepared query to increase security.
I count the number of values that I want to put in the IN () clause,
then create a query string with the right number of bind variables
in the in clause, then bind the values.
So for {1, 3, 5} I'd use "select * from foo where bar in (?, ?, ?)"
and for
{1,5,7,9,11} I'd use "select * from foo where bar in (?, ?, ?, ?, ?)"
Then, in perl-speak, I prepare that string into a query, loop through
all my values and bind them one by one, then execute the query.
Cheers,
Steve
--
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] choosing the right locking mode
On Thu, Apr 3, 2008 at 11:32 AM, rihad <[EMAIL PROTECTED]> wrote: > Scott Marlowe wrote: > > > On Thu, Apr 3, 2008 at 10:44 AM, rihad <[EMAIL PROTECTED]> wrote: > > > > > Given this type query: > > > > > >UPDATE bw_pool > > >SET user_id=? > > >WHERE bw_id= > > >(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) > > >RETURNING bw_id > > > > > > The idea is to "single-threadedly" get at the next available empty > slot, no > > > matter how many such queries run in parallel. So far I've been > > > semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it > > > deadlocks sometimes. Maybe I could use some less restrictive locking > mode > > > and prevent possible collisions at the same time? > > > > > > > So, is there some reason a sequence won't work here? > > > > bw_pool is pre-filled with 10 thousand rows of increasing bw_id, each of > which is either set (user_id IS NOT NULL) or empty (user_id IS NULL). The > state of each can change any time. So, then ANY id would do, would it not, as long as it was null when you picked it? > > > > > If you've got a > > requirement for a no-gap id field, there are other, less locky-ish > > ways to do it. Locking the table doesn't scale, and that's likely > > what problem you're seeing. > > > > > There's a shared resource backed by bw_pool that I absolutely need > single-threaded access to, despite multiple cpus, hence an all-exclusive > lock (or?..) Well, my most basic question was if that shared resource is a design flaw in the way it's set up. I'm still not convinced it isn't, but I know how you can get stuck with things like this too. Building a solution that works around this limitation may be as much work to get done as fixing whatever basic design flaw underlies this. If it is a design flaw. > -- 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] choosing the right locking mode
rihad wrote: > Given this type query: > > UPDATE bw_pool > SET user_id=? > WHERE bw_id= > (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) > RETURNING bw_id Can you use a SERIALIZABLE transaction and avoid the explicit lock? If I'm not mistaken, using the SERIALIZABLE isolation level should ensure that the following cannot occur: UPDATE begins UPDATE begins Subquery finds free row id 1 Subquery finds free row id 1 Update completes Update completes, overwriting changes from the other update. You'd have to be prepared to retry failed updates, but I doubt that's a big deal in this situation. See: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html -- Craig Ringer -- 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] choosing the right locking mode
On Thu, Apr 3, 2008 at 11:42 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Thu, Apr 3, 2008 at 11:32 AM, rihad <[EMAIL PROTECTED]> wrote: > > Scott Marlowe wrote: > > > > > On Thu, Apr 3, 2008 at 10:44 AM, rihad <[EMAIL PROTECTED]> wrote: > > > > > > > Given this type query: > > > > > > > >UPDATE bw_pool > > > >SET user_id=? > > > >WHERE bw_id= > > > >(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) > > > >RETURNING bw_id > > > > > > > > The idea is to "single-threadedly" get at the next available empty > > slot, no > > > > matter how many such queries run in parallel. So far I've been > > > > semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it > > > > deadlocks sometimes. Maybe I could use some less restrictive locking > > mode > > > > and prevent possible collisions at the same time? > > > > > > > > > > So, is there some reason a sequence won't work here? > > > > > > > bw_pool is pre-filled with 10 thousand rows of increasing bw_id, each of > > which is either set (user_id IS NOT NULL) or empty (user_id IS NULL). The > > state of each can change any time. > > So, then ANY id would do, would it not, as long as it was null when > you picked it? If this is the case, you could use a sequence and just select using it for the id until you hit a row that was null and use it. since all access for this would use the sequence no one would hit the row at the same time, they'd be one ahead or behind you. Set it to cycle and you've got a self-maintaining system. -- 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] choosing the right locking mode
On Thu, Apr 03, 2008 at 09:44:55PM +0500, rihad wrote: > Given this type query: > > UPDATE bw_pool > SET user_id=? > WHERE bw_id= > (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) > RETURNING bw_id > > The idea is to "single-threadedly" get at the next available empty slot, > no matter how many such queries run in parallel. Do you "unblock" the pool slot by updating user_id to NULL in some later transaction? If so, how about using INSERTs to lock and DELETEs to unlock? You could have a table of locks: CREATE TABLE bw_locks ( bw_id INTEGER PRIMARY KEY REFERENCES bw_pool (bw_id), user_id INTEGER NOT NULL REFERENCES users ); and have a function to perform the actual slot acquisition: CREATE FUNCTION nextslot (INTEGER) RETURNS INTEGER LANGUAGE plpgsql AS $$ DECLARE id INTEGER; BEGIN LOOP BEGIN INSERT INTO bw_locks (bw_id,user_id) SELECT MIN(bw_id), $1 FROM bw_pool p LEFT JOIN bw_locks l USING (bw_id) WHERE l.bw_id IS NULL RETURNING (MIN(bw_id)) INTO id; IF FOUND THEN RETURN id; END IF; RAISE EXCEPTION 'no free slots---panic!'; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'nextslot() spinning'; END; END LOOP; END; $$; This will keep trying to find the smallest id, looping when somebody else uses it at the same time. I've not tested this code, nor written anything much like it before so test liberally. > So far I've been > semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it > deadlocks sometimes. Maybe I could use some less restrictive locking > mode and prevent possible collisions at the same time? This problem is always going to be awkward with a relational database though. The problem you want to solve is the opposite of their model. Sam -- 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] Secure "where in(a,b,c)" clause.
William Temperley wrote: Hi All I hope this isn't a FAQ, but does anyone have any suggestions as to how to make a query that selects using: "where in()" secure from an sql injection point of view? I have grid of tiles I'm using to reference geographical points. These tiles are identical to the tiling system google maps uses. My google maps application works out the tiles it wants to display as a list of tile names, and sends this list to a php script. This works very well, however I'm currently directly concatenating a sql query: select st_collect(the_geom) from tiles where tilename in ()) Which leaves my application vulnerable to sql injection. As the length of the comma delimited list is highly variable I don't think I can use a prepared query to increase security. Aside from using a prepared statement, your application code can simply ensure that each named tile follows whatever naming conventions you have in place. A very basic regex should do. b -- 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] choosing the right locking mode
Scott Marlowe wrote: > On Thu, Apr 3, 2008 at 11:45 AM, Craig Ringer > <[EMAIL PROTECTED]> wrote: >> rihad wrote: >> > Given this type query: >> > >> > UPDATE bw_pool >> > SET user_id=? >> > WHERE bw_id= >> > (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) >> > RETURNING bw_id >> >> Can you use a SERIALIZABLE transaction and avoid the explicit lock? > > I'm pretty sure serializable won't fix this. I'm far from sure myself, but if it won't I'd be very interested in knowing how it can go wrong. A quick test suggested that it did the job, and according to: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html it should work. Given the language: -- UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row may have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the serializable transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the serializable transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the serializable transaction will be rolled back with the message "ERROR: could not serialize access due to concurrent update" because a serializable transaction cannot modify or lock rows changed by other transactions after the serializable transaction began. - Say two updates are begun at the same time. Both run their subqueries and both pick the same free id. One then acquires a ROW EXCLUSIVE lock on the record being updated and the other blocks trying to acquire that lock. The update that successfully grabbed the lock makes its changes and the transaction commits successfully, releasing the lock. The second update is now free to continue, but because the row it was attempting to modify has just been changed under it it'll abort with a serialization error. It seems safe to me. -- Craig Ringer -- 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] deadlock
rihad wrote: Hi, I've come across a strange deadlock that I need your help with. There are two copies of the same Perl daemon running on a 2 cpu box. The program is pretty simple (because I wrote it :)) so I can trace its pathway fairly well: in it, there's a single "LOCK table foo" occurring part way through a transaction that sometimes ends up as this: DETAIL: Process 91376 waits for AccessExclusiveLock on relation 16488 of database 16386; blocked by process 92387. Process 92387 waits for AccessExclusiveLock on relation 16488 of database 16386; blocked by process 91376. If there are only two processes, and each is waiting for an ACCESS EXCLUSIVE lock on the same relation and being blocked by the other one, then presumably both have weaker locks that conflict with ACCESS EXCLUSIVE on that relation. Process 1 can't proceed with the ACCESS EXCLUSIVE lock because process 2 has a lesser lock on the table. Process 2 can't proceed with the ACCESS EXCLUSIVE lock because process 1 has a lesser lock on the table. Deadlock. I don't see any other way the situation could arise, but I'm *very* far from an expert. Indeed, there is one SELECT and, conditionally, one UPDATE before the exclusive LOCK, on the table. I've re-read the manual, particularly this line: "One should also ensure that the first lock acquired on an object in a transaction is the highest mode that will be needed for that object." Since SELECT & UPDATE come before LOCK on bw_pool, the bug is obvious. Sadly I can't use any other locking as I need exclusive access to OS's firewall after getting bw_id. Well, I thought I'd move LOCK further away inside the transaction to better mimic fine-grained locking. So one solution is to move it back to the beginning. Thank you! -- 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] deadlock
rihad wrote: Given this type query: UPDATE bw_pool SET user_id=? WHERE bw_id= (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) RETURNING bw_id Can you use a SERIALIZABLE transaction and avoid the explicit lock? Not really. Since LOCKing bw_pool backs up later firewall manipulation (of which there's one) I'm not really prepared to restart transactions due to deadlocks. It's easier for me to prevent deadlocks altogether by carefully stacking queries according to the level of lock restrictiveness, albeit at a price that the whole transaction will be single threaded, even parts of it that don't need it. I was indeed willing to exclusively lock only as little code as possible (fine-grained locking), but neglected the importance of the locking-type order. -- 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] choosing the right locking mode
On Thu, Apr 3, 2008 at 11:45 AM, Craig Ringer <[EMAIL PROTECTED]> wrote: > rihad wrote: > > Given this type query: > > > > UPDATE bw_pool > > SET user_id=? > > WHERE bw_id= > > (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL) > > RETURNING bw_id > > Can you use a SERIALIZABLE transaction and avoid the explicit lock? I'm pretty sure serializable won't fix this. -- 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] choosing the right locking mode
On Thu, Apr 3, 2008 at 12:58 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Scott Marlowe wrote: > > > Sure, but you have to trap that all the time. The solution using a > > cycling sequence keeps you from ever seeing that (unless you managed > > to check out all 9,999 other values while still getting the current > > one. No locking needed, dozens of updaters running concurrently and > > no need to track update errors. > > > > > Yep, that does sound like it'd be nicer, at least if locks are becoming > free at a reasonable rate (ie you don't have to step through most of the > table to find a free lock). I was working on the probably mistaken > assumption that the OP wanted the "next" / "first" available slot, not any > free slot. > > If there are very few free locks at any given time I have the feeling the > sequence approach could spend a lot of time just scanning through the table > looking for free entries. Then again, using an aggregate subquery is far > from free either, and it's a whole lot nicer to just repeat one statement > until it succeeds rather than retrying the whole transaction if it conflicts > with another (which will happen often if there's really high demand for > locks). > > In fact, both transactions trying to grab the lowest free lock is > practically a recipe for serialization failures, making it even less > attractive. With only two concurrent connections it'd work OK if one used > min() and the other used max() ... but add another couple and you're in > trouble. > > The serial based approach sounds a fair bit better. Add prepared select statements and you'd get get pretty fast performance. I'd hope it's a situation where most blocks ARE free. Note that I based my whole argument that if a number down lower could become free then gaps weren't a problem. If gaps somehow are a problem, then there's a whole other set of approaches for providing the illusion of gap free ids without all the cost. -- 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] choosing the right locking mode
Scott Marlowe wrote: Sure, but you have to trap that all the time. The solution using a cycling sequence keeps you from ever seeing that (unless you managed to check out all 9,999 other values while still getting the current one. No locking needed, dozens of updaters running concurrently and no need to track update errors. Yep, that does sound like it'd be nicer, at least if locks are becoming free at a reasonable rate (ie you don't have to step through most of the table to find a free lock). I was working on the probably mistaken assumption that the OP wanted the "next" / "first" available slot, not any free slot. If there are very few free locks at any given time I have the feeling the sequence approach could spend a lot of time just scanning through the table looking for free entries. Then again, using an aggregate subquery is far from free either, and it's a whole lot nicer to just repeat one statement until it succeeds rather than retrying the whole transaction if it conflicts with another (which will happen often if there's really high demand for locks). In fact, both transactions trying to grab the lowest free lock is practically a recipe for serialization failures, making it even less attractive. With only two concurrent connections it'd work OK if one used min() and the other used max() ... but add another couple and you're in trouble. The serial based approach sounds a fair bit better. -- Craig Ringer -- 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] deadlock
Scott Marlowe wrote: Sure, but you have to trap that all the time. The solution using a cycling sequence keeps you from ever seeing that (unless you managed to check out all 9,999 other values while still getting the current one. No locking needed, dozens of updaters running concurrently and no need to track update errors. Yep, that does sound like it'd be nicer, at least if locks are becoming free at a reasonable rate (ie you don't have to step through most of the table to find a free lock). I was working on the probably mistaken assumption that the OP wanted the "next" / "first" available slot, not any free slot. If there are very few free locks at any given time I have the feeling the sequence approach could spend a lot of time just scanning through the table looking for free entries. Then again, using an aggregate subquery is far from free either, and it's a whole lot nicer to just repeat one statement until it succeeds rather than retrying the whole transaction if it conflicts with another (which will happen often if there's really high demand for locks). In fact, both transactions trying to grab the lowest free lock is practically a recipe for serialization failures, making it even less attractive. With only two concurrent connections it'd work OK if one used min() and the other used max() ... but add another couple and you're in trouble. The serial based approach sounds a fair bit better. Serial access to the firewall is what I'm now emulating with "LOCK bw_pool" as the first statement in the transaction. AFAIK Postgres' serializable transactions give you decent parallelism at the price of expecting you to retry them due to serialization errors, and thus cannot be relied upon for doing actions on a shared external resource (like manipulating the firewall) after having LOCKed some table. It's a pity there's no way to let go of the lock as soon as possible, only implicitly at the end of the transaction. -- 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] choosing the right locking mode
Scott Marlowe wrote: The serial based approach sounds a fair bit better. Er, I meant "sequence". Add prepared select statements and you'd get get pretty fast performance. Yep, and if DB round trips are a problem it can always be wrapped up in a stored procedure. I'd be tempted to do that anyway just to simplify the client's job, guarantee query plan caching, etc. -- Craig Ringer -- 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] PG 8.3.x doesn't get build
Alvaro Herrera schrieb: Tom Lane wrote: Andreas writes: make[3]: *** No rule exists for Target �utf8_and_euc_jis_2004.o�, needed to create �libutf8_and_euc_jis_2004.so.0.0�. END. There are two or three reports like this in the archives. It appears to be related to using an old version of "tar" that fails to extract such long filenames correctly. Please install the latest tar you can get your hands on, and see if it gets better... Also, do not use Midnight Commander to extract the files. BINGO MC was messing up the stuff. A simple tar -xf ... did work though. Thanks :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autograph Annoucement (ERD Tool)
Hello everyone. There were a number of people asking about ERD tools here a while ago, so I decided to publish one that I've put together. It's called Autograph, and you can find it on the pg foundry: http://pgfoundry.org/projects/autograph/ Autograph is really just an XSL stylesheet, used in combination with graphviz, xsltproc, convert and the downloadXml.py program from Scott Kirkwood's xmltoddl package. The documentation page, including a couple of example diagrams, is here: http://pgfoundry.org/docman/view.php/1000360/4848/readme.html Enjoy! Regards, cf begin:vcard fn:Colin Fox n:Fox;Colin org:CF Consulting Inc. adr:;;#330-1152 Mainland St.;Vancouver;BC;V6B 4X2;Canada email;internet:[EMAIL PROTECTED] title:President tel;work:(604) 681 5282 tel;cell:(778) 838 7887 x-mozilla-html:TRUE url:http://cfconsulting.ca version:2.1 end:vcard -- 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] Autograph Annoucement (ERD Tool)
Colin Fox wrote: Hello everyone. There were a number of people asking about ERD tools here a while ago, so I decided to publish one that I've put together. It's called Autograph, and you can find it on the pg foundry: http://pgfoundry.org/projects/autograph/ Cool! Nice to have a new option available. I'll definitely check it out. Autograph is really just an XSL stylesheet, used in combination with graphviz, xsltproc, convert and the downloadXml.py program from Scott Kirkwood's xmltoddl package. The documentation page, including a couple of example diagrams, is here: http://pgfoundry.org/docman/view.php/1000360/4848/readme.html From the example page: "I also wanted to make sure that there were "no dead crows" -- having the crows-feet right side up makes the diagram a lot more readable in my humble opinion." Um ... those arrows look backwards to me :-( -- 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] Autograph Annoucement (ERD Tool)
Colin Fox wrote: There were a number of people asking about ERD tools here a while ago, so I decided to publish one that I've put together. It's called Autograph, and you can find it on the pg foundry: http://pgfoundry.org/projects/autograph/ Looks handy. I'd like to give it a try. Autograph is really just an XSL stylesheet, used in combination with graphviz, xsltproc, convert and the downloadXml.py program from Scott Kirkwood's xmltoddl package. I'm familiar with graphviz and xsltproc, but what is convert? Is this Imagemagick's convert? I hope not... Debian says I need 36 packages for that. Colin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] To many records returned
Have a select statement with a where clause using datestamp with timezone column compared to a date Select * from sometable where DateStampColumn > '2008-03-31' this returns records that are equal 2008-03-31 but when the query includes casting to date Select * from sometable where DateStampColumn::date > '2008-03-31' The result is correct What is causing the difference in the results My guess is '2008-03-31' is being cast into a datestamp value of 2008-03-31 00:00:00.00-00 which would explain why one query returns more records. -- 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] To many records returned
Justin escribió: Have a select statement with a where clause using datestamp with timezone column compared to a date Select * from sometable where DateStampColumn > '2008-03-31' this returns records that are equal 2008-03-31 but when the query includes casting to date Select * from sometable where DateStampColumn::date > '2008-03-31' The result is correct What is causing the difference in the results My guess is '2008-03-31' is being cast into a datestamp value of 2008-03-31 00:00:00.00-00 which would explain why one query returns more records. I am almost sure your problem is that casting is done to textso 2008-03-31 00:00:01 is greater than 2008-03-31 so that date is included Explicit casting is the right thing to do (your second query) smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Autograph Annoucement (ERD Tool)
Colin Wetherbee wrote: Colin Fox wrote: Autograph is really just an XSL stylesheet, used in combination with graphviz, xsltproc, convert and the downloadXml.py program from Scott Kirkwood's xmltoddl package. I'm familiar with graphviz and xsltproc, but what is convert? Is this Imagemagick's convert? NetPBM seems to work with it, if the convert line in diagrams/Makefile is changed to the following. @pstopnm -stdout $@ | pnmtopng > $(subst .ps,.png,$@) Don't forget the tab at the beginning. Right now, all I get are three 1175-byte PNG files with the words "Generated by Auto Graph" in them, though. I'll play around with it more later. Colin -- 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] Autograph Annoucement (ERD Tool)
Colin Wetherbee wrote: > Colin Wetherbee wrote: >> Colin Fox wrote: >>> Autograph is really just an XSL stylesheet, used in combination with >>> graphviz, xsltproc, convert and the downloadXml.py program from Scott >>> Kirkwood's xmltoddl package. >> >> I'm familiar with graphviz and xsltproc, but what is convert? Is >> this Imagemagick's convert? > > NetPBM seems to work with it, if the convert line in diagrams/Makefile > is changed to the following. > > @pstopnm -stdout $@ | pnmtopng > $(subst .ps,.png,$@) > > Don't forget the tab at the beginning. > > Right now, all I get are three 1175-byte PNG files with the words > "Generated by Auto Graph" in them, though. > > I'll play around with it more later. > > Colin I probably should change this from "requires convert" to "currently uses convert to convert from PS to PNG, but you can use whatever you want, or just leave them in PS if you like". I'm converting from PS, by the way, because although I love graphviz's diagramming capability, it's PNG output is "crappy" :). Regards, cf begin:vcard fn:Colin Fox n:Fox;Colin org:CF Consulting Inc. adr:;;#330-1152 Mainland St.;Vancouver;BC;V6B 4X2;Canada email;internet:[EMAIL PROTECTED] title:President tel;work:(604) 681 5282 tel;cell:(778) 838 7887 x-mozilla-html:TRUE url:http://cfconsulting.ca version:2.1 end:vcard -- 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] Autograph Annoucement (ERD Tool)
brian wrote: > Colin Fox wrote: >> Hello everyone. >> >> There were a number of people asking about ERD tools here a while ago, >> so I decided to publish one that I've put together. >> >> It's called Autograph, and you can find it on the pg foundry: >> >> http://pgfoundry.org/projects/autograph/ >> > > Cool! Nice to have a new option available. I'll definitely check it out. > >> Autograph is really just an XSL stylesheet, used in combination with >> graphviz, xsltproc, convert and the downloadXml.py program from Scott >> Kirkwood's xmltoddl package. >> >> The documentation page, including a couple of example diagrams, is here: >> >> http://pgfoundry.org/docman/view.php/1000360/4848/readme.html >> > > From the example page: > > "I also wanted to make sure that there were "no dead crows" -- having > the crows-feet right side up makes the diagram a lot more readable in > my humble opinion." > > Um ... those arrows look backwards to me :-( > Actually, crows feet are supposed to look like... the feet of crows -- ie a three-line triangle at the bottom and the line rising from it. All the connectors on that diagram follow this convention except for the mickey-mouse ear on the comments table. Regards, cf begin:vcard fn:Colin Fox n:Fox;Colin org:CF Consulting Inc. adr:;;#330-1152 Mainland St.;Vancouver;BC;V6B 4X2;Canada email;internet:[EMAIL PROTECTED] title:President tel;work:(604) 681 5282 tel;cell:(778) 838 7887 x-mozilla-html:TRUE url:http://cfconsulting.ca version:2.1 end:vcard -- 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] Autograph Annoucement (ERD Tool)
Colin Wetherbee wrote: > Colin Wetherbee wrote: >> Colin Fox wrote: >>> Autograph is really just an XSL stylesheet, used in combination with >>> graphviz, xsltproc, convert and the downloadXml.py program from Scott >>> Kirkwood's xmltoddl package. >> >> I'm familiar with graphviz and xsltproc, but what is convert? Is >> this Imagemagick's convert? > > NetPBM seems to work with it, if the convert line in diagrams/Makefile > is changed to the following. > > @pstopnm -stdout $@ | pnmtopng > $(subst .ps,.png,$@) > > Don't forget the tab at the beginning. > > Right now, all I get are three 1175-byte PNG files with the words > "Generated by Auto Graph" in them, though. > Can you look at the PS file, and make sure that you're at least getting a legitimate postscript diagram? > I'll play around with it more later. > > Colin begin:vcard fn:Colin Fox n:Fox;Colin org:CF Consulting Inc. adr:;;#330-1152 Mainland St.;Vancouver;BC;V6B 4X2;Canada email;internet:[EMAIL PROTECTED] title:President tel;work:(604) 681 5282 tel;cell:(778) 838 7887 x-mozilla-html:TRUE url:http://cfconsulting.ca version:2.1 end:vcard -- 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] To many records returned
Justin <[EMAIL PROTECTED]> writes: > Have a select statement with a where clause using datestamp with > timezone column compared to a date > Select * from sometable where DateStampColumn > '2008-03-31' > this returns records that are equal 2008-03-31 There's no such thing as "timestamp compared to date". The date promotes to a timestamp at midnight; what you wrote is equivalent to Select * from sometable where DateStampColumn > '2008-03-31 00:00:00' > but when the query includes casting to date > Select * from sometable where DateStampColumn::date > '2008-03-31' > The result is correct You threw away the intraday part of the timestamp... regards, tom lane -- 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] Autograph Annoucement (ERD Tool)
Colin Fox wrote: Colin Wetherbee wrote: Colin Wetherbee wrote: Colin Fox wrote: Autograph is really just an XSL stylesheet, used in combination with graphviz, xsltproc, convert and the downloadXml.py program from Scott Kirkwood's xmltoddl package. I'm familiar with graphviz and xsltproc, but what is convert? Is this Imagemagick's convert? NetPBM seems to work with it, if the convert line in diagrams/Makefile is changed to the following. @pstopnm -stdout $@ | pnmtopng > $(subst .ps,.png,$@) Don't forget the tab at the beginning. Right now, all I get are three 1175-byte PNG files with the words "Generated by Auto Graph" in them, though. Can you look at the PS file, and make sure that you're at least getting a legitimate postscript diagram? It turned out I had incorrectly added my schema information. Works now, with NetPBM. Cool tool! Thanks. Also, is there a way to forcibly remove tables and views from the output? In all.xml, I only list the tables I want, but in addition to those, I also get my PostGIS tables and all the views I've created within the schema. Colin -- 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 update queries take a long time - postgres 8.3.1
Leif B. Kristensen wrote: On Wednesday 2. April 2008, Tomasz Ostrowski wrote: Go for it. Even 64 (I like round numbers) would not be too much. Geek test: Do you find the above statement odd? Yes: 0, No: +10. (Sorry for being massively off-topic :-)) I had the same thought. ;) Colin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump ignoring without oids
I'm transferring a database from 7.4.7 to 7.4.13 using 8.1.11 utilities and pg_dump leaves out the "without oids" clause in the create table commands. I've confirmed the original tables are created without oids by using pgadmin3 v1.8.2. How do I get the table created without oids? -- Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095 www.flashdev.org.au Sydney Flash Platform Developers Group March AIR and SQLite Date: Wed 26th March 6pm for 6:30 start Details soon -- 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] pg_dump ignoring without oids
"Chris Velevitch" <[EMAIL PROTECTED]> writes: > I'm transferring a database from 7.4.7 to 7.4.13 using 8.1.11 > utilities Why in the world would you try that, rather than using 7.4's pg_dump? At no time has pg_dump version N claimed to produce output that was loadable into server versions < N. > and pg_dump leaves out the "without oids" clause in the > create table commands. 8.1 thinks it should use "SET default_with_oids = false" instead. (Should I point out that the current release in the 7.4 series is 7.4.19? 7.4.13 is nearly a year and a half obsolete, and is missing significant security and data-loss fixes.) regards, tom lane -- 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] Autograph Annoucement (ERD Tool)
Colin Fox wrote: brian wrote: From the example page: "I also wanted to make sure that there were "no dead crows" -- having the crows-feet right side up makes the diagram a lot more readable in my humble opinion." Um ... those arrows look backwards to me :-( Actually, crows feet are supposed to look like... the feet of crows -- ie a three-line triangle at the bottom and the line rising from it. All the connectors on that diagram follow this convention except for the mickey-mouse ear on the comments table. I was kidding. No joke: i screwed up my smiley! b -- 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] Autograph Annoucement (ERD Tool)
Colin Wetherbee wrote: Also, is there a way to forcibly remove tables and views from the output? In all.xml, I only list the tables I want, but in addition to those, I also get my PostGIS tables and all the views I've created within the schema. And... If I set the tablemode to "detailed", all the lines terminate at primary keys, instead of terminating at a primary key and a foreign key, which, I assume, is what should happen. An example... TABLE mytable id PRIMARY KEY foo_id REFERENCES foo(id) TABLE foo id PRIMARY KEY The output of your tool would show a link between mytable.id and foo.id, instead of a link between mytable.foo_id and foo.id. I don't think I'll use the detailed view very often, though; I just wanted to see what it did. :) Here's the sample "detailed" output from one of my databases: http://colinwetherbee.com/data/js-20080403.png Thanks again for this neat, lean tool. Colin -- 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] pg_dump ignoring without oids
Chris Velevitch wrote: I'm transferring a database from 7.4.7 to 7.4.13 using 8.1.11 utilities and pg_dump leaves out the "without oids" clause in the create table commands. I've confirmed the original tables are created without oids by using pgadmin3 v1.8.2. How do I get the table created without oids? If you're moving from 7.4.7 to 7.4.13, why don't you use the 7.4 utilities? The 8.1 utilities assume 8.1 defaults, which can be quite different from 7.4 defaults and, IIRC, are responsible for your OID issue. If you really must use 8.1 utilities, edit your dump file and append WITHOUT OIDS to your CREATE TABLE statements. Also, FWIW, 7.4.19 is the latest release of the 7.4 series, and you really should be using that, if you're sticking with the now-very-old 7.4 version. Sydney Flash Platform Developers Group March AIR and SQLite Date: Wed 26th March 6pm for 6:30 start Details soon That looks to be a bit out of date, too. :) Colin -- 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] pg_dump ignoring without oids
On Fri, Apr 4, 2008 at 1:45 PM, Colin Wetherbee <[EMAIL PROTECTED]> wrote: > If you're moving from 7.4.7 to 7.4.13, why don't you use the 7.4 utilities? > The 8.1 utilities assume 8.1 defaults, which can be quite different from 7.4 > defaults and, IIRC, are responsible for your OID issue. I've been using 8.1 to dump from 7.4 to restore into an 8.1 server for backup purposes. Now I've been told that I need to move my database to another server which 7.4.13. I see about getting it upgraded. In the mean time, which download has only the 7.4 utils and can that be installed without removing my 8.1 utils. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095 www.flashdev.org.au Sydney Flash Platform Developers Group April meeting: TBD Date: Mon 28th April 6pm for 6:30 start Details soon -- 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] pg_dump ignoring without oids
On Fri, Apr 4, 2008 at 2:50 PM, Chris Velevitch <[EMAIL PROTECTED]> wrote: > In the mean time, which download has only the 7.4 utils and can that > be installed without removing my 8.1 utils. I'm using Centos 5. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095 www.flashdev.org.au Sydney Flash Platform Developers Group April meeting: TBD Date: Mon 28th April 6pm for 6:30 start Details soon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] referencing column aliases in select list
Hi, I have a SELECT statement that is a bit involved in terms of calculations, so I wanted to set up some column aliases and refer to them further down the select list: ---<---cut here---start-->--- SELECT table1.col1 - table2.col1 AS diff1, table1.col2 + table2.col2 AS sum1, sum1 - diff1 FROM table1 INNER JOIN table2 ON (table1.id = table2.id) ---<---cut here---end>--- but this fails with the error message that sum1 column is not known. How can one get around this? The calculations are quite involved and would be difficult and error-prone to repeat them anywhere they're needed in the select list. Thanks in advance for any pointers. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Thu, Apr 3, 2008 at 10:39 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > > Thanks. I had another concern about VACUUM not reporting DEAD line > > pointers (please see up thread). Any comments on that ? > > If you want to work on that, go ahead Ok. I would do that. > but I wanted it separate because > I didn't think it merited back-patching. It's strictly cosmetic in > terms of being about what VACUUM VERBOSE prints, no? > Umm.. Whatever we decide on the fix, I think we should backpatch it to 8.3 because I am worried that someone way get completely confused with the current vacuum report, especially if the autovac is triggered just because of heap full of DEAD line pointers. The num of dead rows reported may awfully be low in that case. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] referencing column aliases in select list
Seb wrote: ---<---cut here---start-->--- SELECT table1.col1 - table2.col1 AS diff1, table1.col2 + table2.col2 AS sum1, sum1 - diff1 FROM table1 INNER JOIN table2 ON (table1.id = table2.id) ---<---cut here---end>--- but this fails with the error message that sum1 column is not known. How can one get around this? The calculations are quite involved and would be difficult and error-prone to repeat them anywhere they're needed in the select list. Thanks in advance for any pointers. The way I usually handle this is as follows. SELECT foo - bar AS baz FROM ( SELECT a.a + b.a AS foo, a.b + b.b AS bar FROM a JOIN b ON a.id = b.id ) AS subtable; Although, I'm not really sure that's The Right Way to do it. Colin -- 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] referencing column aliases in select list
On Fri, 04 Apr 2008 00:35:01 -0400, Colin Wetherbee <[EMAIL PROTECTED]> wrote: [...] > SELECT foo - bar AS baz FROM ( SELECT a.a + b.a AS foo, a.b + b.b AS > bar FROM a JOIN b ON a.id = b.id ) AS subtable; > Although, I'm not really sure that's The Right Way to do it. Thanks Colin, yes, I thought about that, but things get nasty when you have to do some other calculation with 'baz', with the output of that and so on (as I'm facing in my real tables). But this may really be the best solution anyway. Cheers, -- Seb -- 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 update queries take a long time - postgres 8.3.1
On Wed, 2 Apr 2008, mark wrote: with no clients connected to the database when I try to shutdown the database [to apply new settings], it says database cant be shutdown.. for a long time both in smart and normal mode... then i had to go to immediate mode to shut down.. but then when i start it again.. it goes into recovery mode and runs for a while..is this the long recover time you are talking about? Exactly; doing an immediate shutdown is one way (probably the safest way in fact) to force the database into recovery mode when it starts back up again to see how long a recovery will take. As you increase checkpoint_segments, the amount of time it will take to process all the segments before the database comes up will increase about linearly. Double checkpoint_segments and you will double the expected startup delay after crash (and a bad shutdown qualifies as a crash of sorts). You need to make sure you're not making that time unacceptable for your application before going too crazy increasing the segments. You should certainly look into how to get your database to shutdown cleanly though. There's three modes here pg_ctl will use: smart, fast, immediate. I consider smart just about useless because I always have somebody connected. Immediate has the problem you already noticed, recovery after startup. So fast is the most useful of the three modes. You said "smart and normal mode"...the default is smart, so if you meant "without specifying a mode" when you said normal then you just tried smart twice--that matches your comment that it took "a long time" in both cases (fast won't). If I'm right about that, try fast next time. If you couldn't shutdown using fast, that's something that deserves some investigation. That shouldn't happen unless there's a bad situation. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] pg_dump ignoring without oids
"Chris Velevitch" <[EMAIL PROTECTED]> writes: > On Fri, Apr 4, 2008 at 2:50 PM, Chris Velevitch > <[EMAIL PROTECTED]> wrote: >> In the mean time, which download has only the 7.4 utils and can that >> be installed without removing my 8.1 utils. > I'm using Centos 5. Um ... Red Hat, who are about as conservative as they come on this type of issue, shipped PG 8.1.x in RHEL 5. Which benighted PHB decreed that you should be using 7.4.x? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > On Thu, Apr 3, 2008 at 10:39 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> I didn't think it merited back-patching. It's strictly cosmetic in >> terms of being about what VACUUM VERBOSE prints, no? > Umm.. Whatever we decide on the fix, I think we should backpatch it to > 8.3 because I am worried that someone way get completely confused with > the current vacuum report, "Somebody might misread an optional report" doesn't seem to me to be on the same risk level as "we might destabilize a stable release". The policy of this project is that we only put nontrivial bug fixes into back branches, and I don't think this item qualifies ... regards, tom lane -- 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] pg_dump ignoring without oids
On Fri, Apr 4, 2008 at 4:33 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Chris Velevitch" <[EMAIL PROTECTED]> writes: > > On Fri, Apr 4, 2008 at 2:50 PM, Chris Velevitch > > <[EMAIL PROTECTED]> wrote: > >> In the mean time, which download has only the 7.4 utils and can that > >> be installed without removing my 8.1 utils. > > > I'm using Centos 5. > > Um ... Red Hat, who are about as conservative as they come on this type > of issue, shipped PG 8.1.x in RHEL 5. Which benighted PHB decreed that > you should be using 7.4.x? We're using a shared hosting database service. They were using 7.4 when we first started using them and they haven't upgraded unless we pay them to. We are their only pg customer. > > regards, tom lane > -- Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095 www.flashdev.org.au Sydney Flash Platform Developers Group April meeting: TBD Date: Mon 28th April 6pm for 6:30 start Details soon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Fri, Apr 4, 2008 at 11:10 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > The > policy of this project is that we only put nontrivial bug fixes into > back branches, and I don't think this item qualifies ... > Got it. I will submit a patch for HEAD. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] pg_dump ignoring without oids
"Chris Velevitch" <[EMAIL PROTECTED]> writes: > On Fri, Apr 4, 2008 at 4:33 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> "Chris Velevitch" <[EMAIL PROTECTED]> writes: >>> I'm using Centos 5. >> >> Um ... Red Hat, who are about as conservative as they come on this type >> of issue, shipped PG 8.1.x in RHEL 5. Which benighted PHB decreed that >> you should be using 7.4.x? > We're using a shared hosting database service. They were using 7.4 > when we first started using them and they haven't upgraded unless we > pay them to. We are their only pg customer. I'm confused. They updated you to RHEL5, but not to the application packages that Red Hat ships on RHEL5? (I'm not sure why you'd want to run a PG-based service on a provider who so obviously has got zero knowledge, competence, or interest in PG, but anyway.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
