Re: [SQL] Create Assertion -- Question from a newbie
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Well, it's on the SQL standard, so we should look forward to > implementing (some form of) it some day. Do *any* databases implement SQL standard Assertions? -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] query to select a linked list
"Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > Each message a unique id_forum and an id_parent pointing to the replied > post (empty if first post). > > How can I build an elegant query to select all messages in a thread? You would need recursive queries which Postgres doesn't support. There is a patch out there to add support but I don't think it's up-to-date with 8.2 and in any case the resulting queries can be quite intense. I would recommend you look into the contrib module named "ltree". It's easy to use and works well with the gist indexes. It does require changing your data model denormalizing it slightly which makes it hard to "reparent" children, but if that isn't an operation you have to support I think it makes most other operations you might want to do much easier to support. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] query to select a linked list
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > Are you sure the tablefunc functions, which include both connectby and > crosstab functions, aren't up to date with 8.2? They certainly are up > to 8.1, where I'm running them right now on my workstation. They built > for 8.2 and installed, but I haven't tried using them. > > I would think that connectby is at least worth looking into. Uhm, no, I guess I'm not sure. I didn't realize it was in the tablefunc module either. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] aggregate query
"Andrew Kroeger" <[EMAIL PROTECTED]> writes:
> Raj A wrote:
>> I have a table
>>
>> CREATE TABLE survey_load
>> (
>> meter_id character(5) NOT NULL,
>> number_of_bays integer NOT NULL,
>> bay_1_use integer,
>> bay_2_use integer,
>> bay_3_use integer,
>> bay_4_use integer,
>> bay_5_use integer,
>> date date NOT NULL,
>> inspection_id integer NOT NULL DEFAULT,
>> )
>>
>> How do i present an aggregate query
>>
>> inspection_id | meter_id | bay_use
>> 1 12345(value of bay_1_use)
>> 1 12345(value of bay_2_use)
>> 1 12345(value of bay_3_use)
>> 2 23456(value of bay_1_use)
>> 2 23456(value of bay_2_use)
>> 2 23456(value of bay_3_use)
>> 2 23456(value of bay_4_use)
>> 2 23456(value of bay_5_use)
>
> If I understand your issue correctly, it seems like the denormalized
> nature of your table is causing you some problems.
True. Normalizing the tables would make this query easier which is a good sign
that that's probably the right direction.
If for some reason you can't or won't change the table definition there are a
number of possible tricky answers given the current definition. Something like
this for example:
SELECT inspection_id, meter_id,
case when bay=1 then bay_1_use
when bay=2 then bay_2_use
when bay=3 then bay_3_use
when bay=4 then bay_4_use
when bay=5 then bay_5_use
else null
end AS bay_use
FROM (
SELECT *, generate_series(1,number_of_bays) AS bay
FROM survey_load
) as x
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Recursively traversing a partially ordered set
"Jason Grout" <[EMAIL PROTECTED]> writes: > 2. Is there a big difference in speed between using an array versus > using a SELECT in a WHERE condition? In other words, which is generally > going to be faster: > > SELECT * from table where field IN (some function returning a SETOF); > > or > > SELECT * from table where field = ANY(some function returning an array); In theory since these are equivalent (well nearly. To be equivalent the optimizer would need to know whether the array could possibly have NULLs in it) the optimizer ought to produce the same plan for each. In practice it's not clear where the optimizer would get the information to decide what plan to use for these two cases and whether it would have the same kinds of information available. In any case in practice the plans available in each of these cases are not the same so you'll have to try them and see which one works better for you. I think there are more plans available for the first case so it may work out better if you're returning quite large sets where those plans help. If you're returning quite small sets where you just need a simple bitmap index scan then the second will be less likely to pick (or actually I think it's incapable of picking) some other plan which works poorly. There was some discussion recently on what to do about exactly this type of case. I would be interested to hear about what plans you got from each and which plan ended up being best. > 3. Is there a strong reason I should strip out duplicates in either of > the two cases in question 2? Or is the performance about the same when > doing the queries whether or not the SETOF or arrays contain duplicates? The plans where it matters will remove the duplicates anyways, but I don't think the array version does if you're not using an bitmap index scan. > 4. Can you see any obvious optimizations to the above functions > (particularly the last one)? > > Thanks for your help. Thanks for the absolutely wonderful database and > solid documentation. I originally did this project in MySQL and had the > weirdest errors (the errors turned out to be due to the default > case-insensitive collation of MySQL!). That's when I decided to move to > postgresql when I updated the project. Well, unfortunately collocation support isn't exactly a strong point in Postgres either. Plenty of people get bitten by their database being initdb'd in a locale they didn't expect. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] JOIN
"Loredana Curugiu" <[EMAIL PROTECTED]> writes: > I am trying to say that sum column it is not calculated correctly. It looks like your counts are high. That would imply that your join clauses are matching more than one combination of rows. Ie, it's counting some rows multiple times because there are multiple join matches. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] trigger firing order
"chester c young" <[EMAIL PROTECTED]> writes: > tables A and B: a post row trigger on A cause updates on B which has > its own post row trigger. > > does the post trigger on A wait until post trigger on B is executed? - > this seems intuitive to me. How can it wait until the trigger on B is executed if the trigger on B doesn't actually get triggered until someone updates B and it's the trigger on A doing the update? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] percentages of a column
"Andreas" <[EMAIL PROTECTED]> writes: > Example: > > Fruit Count % > -- > Bananas 5 10% > Apples 15 30% > Oranges 30 60% select fruit_name, count(*), round(count(*)::numeric / (select count(*) from basket) * 100, 0)::text||'%' as "%" from basket group by fruit_name order by "%"; fruit_name | count | % +---+- Bananas| 5 | 10% Apples | 15 | 30% Oranges|30 | 60% (3 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Converting from MS Access field aliases
"Adam Tauno Williams" <[EMAIL PROTECTED]> writes: > "AS" works in Informix, and I believe, in DB2 as well. So it is at > least pretty common; I'm not saying it is correct. Since Informix > predates M$-SQL they at least didn't invent it. AS works in Postgres too. But the defined aliases are only in the resulting output columns, not in scope for the where clause. In fact the expressions used aren't even evaluated for rows which don't match the where clause which is pretty important if those expressions are subqueries or volatile functions. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Query Plan
"Radhika Sambamurti" <[EMAIL PROTECTED]> writes:
> When I run the query with combination of FirmClearingID & status the run
> times are approx 3700ms.
> But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
> run time becomes a horrendous 19631.958 ms.
I'm not really able to make heads or tails of your plans without the query.
But any where clause of the form date_trunc('...',col)='...' will always
generate crappy plans. And it looks like you already have expressions of that
form in the view even in the one which you label "without date_trunc".
Instead try to write a where clause that the database can understand the end
points of. Something like (col >= '20070703' and col < '20070704'). That's
something the database can apply a normal index to and also something it can
have a chance at guessing how many rows will fit.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] The nested view from hell - Restricting a subquerry
Nis Jørgensen <[EMAIL PROTECTED]> writes: > Well, the query can be satisfied by looking only at the rows with an > order_id matching the invoice_id given. The condition that this is the > largest invoice in the group then needs to be checked afterwards. > > I certainly did not expect the query planner to be able to deduce this, > though. No, that's not true. If you had two records in eg_order with the same order_id but different invoice_ids then the query would need both records to satisfy the query. The query planner can't deduce that this can't happen because it simply does not have that information. The more I look at this view the more I think it's just seriously broken. Why is it grouping by order_id at all if, I suspect, there will only be one record per order_id in eg_orders?? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] The nested view from hell - Restricting a subquerry
Nis Jørgensen <[EMAIL PROTECTED]> writes: >>> Well, the query can be satisfied by looking only at the rows with an >>> order_id matching the invoice_id given. The condition that this is the >>> largest invoice in the group then needs to be checked afterwards. >>> >>> I certainly did not expect the query planner to be able to deduce this, >>> though. >> >> No, that's not true. If you had two records in eg_order with the same >> order_id >> but different invoice_ids then the query would need both records to satisfy >> the query. > > I assume you mean "... then both records are necessary in order to > calculate the results of the query". This does not contradict what I wrote. Sorry I meant, "the query as written can not be satisfied by looking only at the rows with the specified invoice_id". > SELECT order_id, > max(order_view.invoice_id), > sum(order_view.mileage) > FROM(SELECT order_id,invoice_id, 0 as mileage FROM eg_order > UNION > SELECT order_id, 0, mileage FROM eg_order_line) > order_view GROUP BY order_view.order_id; > > This is then restricted on max(invoice_id) > > As far as I can tell, these steps produce the correct results (without > the later information about primary keys provided by Bryce) > > INPUT: my_invoice_id > > 1. Look up all order_ids for which (order_id,my_invoice_id) appear in > eg_orders > > 2. Find all rows (in both branches of the UNION) with these id_s > > 3. Group the rows, and calculate max(invoice_id) > > 4. Filter the result rows on max(invoice_id) = my_invoice_id. So here's a hypothetical data set for which this algorithm fails: order_idinvoice_id mileage 1 1 100 1 2 100 Your algorithm would produce order_id max(invoice_id) sum(mileage) 1 1 100 Whereas the correct output would be to output no records at all. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] The nested view from hell - Restricting a subquerry
"Gregory Stark" <[EMAIL PROTECTED]> writes: > Nis Jørgensen <[EMAIL PROTECTED]> writes: > >> 1. Look up all order_ids for which (order_id,my_invoice_id) appear in >> eg_orders >> >> 2. Find all rows (in both branches of the UNION) with these id_s Oh, did you mean look up the order_ids for which there was at least one record with the invoice_id specified, then look up all records with those order_ids regardless of invoice_id? That would work but as you say it would be hard to tell whether it will be any faster than just processing all the order_ids. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] is there a 'table' data type in pg?
"Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > Can I use a another column to store the type of the id_subject (ie: the > tabled it belongs to) ? Then I would be able to query that table for > additional info to print alongside the forum posts. There are ways to identifier tables in Postgres but there's no way to run a query against a table using them. I would strongly recommend you define your own list of "object_types", probably even have an object_type table with a primary key, a description column, and a table_name column. Then you can in your application construct the appropriate query depending on the object_type. One alternative you could do is have a set-returning plpgsql function which has a big if statement and performs the right kind of query. I think the records would have to all be the same -- they can't be different kinds of records depending on the type of object. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PG won't use index on ORDER BY
"Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > I create an index: > CREATE INDEX person_lowerfullname_idx ON > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) > varchar_pattern_ops); Why are you declaring it using the varchar_pattern_ops? The default operator set is the one you want for handling ordering. The pattern_ops operator set is for handling things like x LIKE 'foo%' -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PG won't use index on ORDER BY
"Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > Ooops, just fugured that out. But - it still doesn't use the index if I > remove > the "varchar_pattern_ops". I solved it by adding a function: Hm, well it does for me, you would have to post your explain analyze to see what's going on. > Another question then: Why doesn't "varchar_pattern_ops" handle ordering? > This > means I need 2 indexes on the columns I want to match with LIKE and ORDER BY. > Just doesn't seem right to need 2 "similar" indexes... If you initd in the C locale you only need one index. In other locales the collation order and the pattern order are different. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT syntax synopsis: column_definition?
"Michael Glaesemann" <[EMAIL PROTECTED]> writes: > ERROR: a column definition list is only allowed for functions returning > "record" > > So the *form* is right, but I don't know of an example that works. postgres=# create function testf() returns record as 'select 1' language sql; CREATE FUNCTION postgres=# select * from testf() as (i integer); i --- 1 (1 row) I haven't quite figured out how this is useful though. It probably makes more sense if you use plpgsql but I still don't quite see what the use case is. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Incomprehensible dogged sort in Merge Join
"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes:
> And that is its plan (attached one is the same, but with costs):
>
>>-<
> Merge Left Join
>Merge Cond: ("outer".name = "inner".name)
>-> Sort
> Sort Key: log.name
> -> Seq Scan on log_example_3 log
>-> Sort
> Sort Key: uh.name
> -> Subquery Scan uh
>-> Sort
> Sort Key: name
> -> Seq Scan on user_history
>>-<
What version are you running with?
Incidentally with CVS HEAD I see it working:
postgres=# explain select * from (select * from a order by i) as a right join b
on a.i = b.i ;
QUERY PLAN
-
Merge Right Join (cost=299.56..675.13 rows=22898 width=16)
Merge Cond: (public.a.i = b.i)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: public.a.i
-> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: b.i
-> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)
> With best regards to all of you,
> Aleksandr.
> ÿþ M e r g e L e f t J o i n
> ( c o s t = 2 0 7 6 4 6 . 0 0 . . 2 1 3 8 6 4 . 1 2 r o w s = 3 4 7 8 5 1
> w i d t h = 6 2 ) ( a c t u a l
> t i m e = 3 0 9 2 2 . 3 6 6 . . 3 2 1 6 6 . 5 1 8 r o w s = 1 3 9 0 8
> l o o p s = 1 )
> M e r g e C o n d : ( " o u t e r " . n a m e =
> " i n n e r " . n a m e )
> - > S o r t ( c o s t = 6 . 7 1 . . 7 . 0 3 r o w s = 1 2 7
> w i d t h = 3 0 ) ( a c t u a l t i m e = 1 . 1 1 7 . . 1 . 2 0 7
> r o w s = 1 0 0 l o o p s = 1 )
> S o r t K e y : l o g . n a m e
> - > S e q S c a n o n l o g _ e x a m p l e _ 3
> l o g ( c o s t = 0 . 0 0 . . 2 . 2 7 r o w s = 1 2 7
> w i d t h = 3 0 ) ( a c t u a l t i m e = 0 . 0 5 3 . . 0 . 1 7 7
> r o w s = 1 0 0 l o o p s = 1 )
> - > S o r t
> ( c o s t = 2 0 7 6 3 9 . 2 9 . . 2 0 9 0 0 8 . 7 8 r o w s = 5 4 7 7 9 6
> w i d t h = 3 2 ) ( a c t u a l
> t i m e = 3 0 9 2 1 . 1 7 1 . . 3 1 4 6 7 . 1 1 7 r o w s = 4 4 2 8 4 8
> l o o p s = 1 )
> S o r t K e y : u h . n a m e
> - > S u b q u e r y S c a n u h
> ( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 8 2 9 5 . 3 5 r o w s = 5 4 7 7 9 6
> w i d t h = 3 2 ) ( a c t u a l
> t i m e = 1 9 2 7 7 . 9 6 3 . . 2 1 5 9 5 . 8 7 4 r o w s = 5 4 7 7 9 6
> l o o p s = 1 )
> - > S o r t
> ( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 2 8 1 7 . 3 9 r o w s = 5 4 7 7 9 6
> w i d t h = 3 2 ) ( a c t u a l
> t i m e = 1 9 2 7 7 . 9 0 8 . . 2 0 1 0 4 . 5 6 8 r o w s = 5 4 7 7 9 6
> l o o p s = 1 )
> S o r t K e y : n a m e
> - > S e q S c a n o n
> u s e r _ h i s t o r y ( c o s t = 0 . 0 0 . . 2 2 1 0 3 . 9 6
> r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l
> t i m e = 0 . 0 5 1 . . 1 4 7 4 . 1 4 3 r o w s = 5 4 7 7 9 6
> l o o p s = 1 )
gesundheit.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Incomprehensible dogged sort in Merge Join
"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> >> What version are you running with? > > 8.0.1, sorry for missing. There have been 12 bug-fix releases since then on the 8.0 branch including updating timezones to reflect the new daylight saving time rules for North America, various crashes, data corruption, and security bugs. There is no reason not to upgrade to the current latest 8.0 branch release which is 8.0.13. Separately, you may not want to upgrade to 8.2 now for operational reasons but reporting "wishlist" bugs against a version that's two major releases old is almost certainly going to be pointless. Postgres is moving ahead so fast that the chances that your wish is granted already in more recent releases are very high. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Speeding up schema changes
"Stefan Arentz" <[EMAIL PROTECTED]> writes: > Is there a way to speed up simple schema changes like ... > > ALTER TABLE foo ADD COLUMN bar CHAR(64); > > ... where foo already contains millions of records? > > On a live database changes like this can take hours. Even when the > database is idle. Are you sure that's exactly like the change you're making? I think that should be instantaneous because it will add a new column which is null everywhere. It doesn't have to actually modify the table contents at all to do that, just the schema. It could be slow if the table is extremely busy and it has trouble getting the lock but I don't think it's easy to create that situation, at least not such that it will last more than a few seconds. If, on the other hand, you added a column with a default value then it would be an entirely different scenario. In that case it has to rewrite the whole table with the new values in every record. It also has to reindex every index for the new table contents and so on. So if you didn't have to initialise the contents you would avoid the wait. Also, incidentally do you have a good reason to use CHAR instead of varchar or text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar will take only as many bytes as the data you're storing (plus 4 bytes). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index usage in bitwise operation context
"W.Alphonse HAROUNY" <[EMAIL PROTECTED]> writes: > Question: > -- > I have an SQL request similar to: > > SELECT . FROM TBL1, TBL2 WHERE > AND > TBL1.CATEGORY & TBL2.CATEGORY <> 0 //-- where & is the AND bitwise > operator > > Qst: > 1/ IS the above SQL request will use the INDEX [categoryGroup] defined on > TBL1 and TBL2 ? No, & isn't an indexable operator for any of the standard indexing methods. You could create 32 partial indexes on some other key with clauses like WHERE tbl1.category & 0100... = 0100... But I don't think that would be useful for a join clause in any case. Second idea, you could create an expression index on tbl1 (category & 0100... = 0100..., category & 0010... = 0010..., category & 0001... = 0001..., ...) Again I don't see that it's going to be used for a join condition. Lastly, you could look for a GIST index method for varbit which would be superior to both of the above tactics. I'm still not sure it would be able to handle a join clause though, but maybe? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Extracting hostname from URI column
"Paul Lambert" <[EMAIL PROTECTED]> writes: > What would be the best way to do something like that? I assume it won't be a > simple regex like the above due to the country codes on the end of some > domains. My thought is look at the last portion of the domain, if it's 2 > characters long then assume it's a country code and grab the last three > sections, if it's not three characters long then assume it's an international > domain and grab the last two... but that sounds a bit dodgy. Not all countries break up their national tld space into sections like .co.uk or .com.au. Canadian domains can be bought directly under .ca like amazon.ca. I think you'll have to store a specific list of tlds and how deep you want to look. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Speeding up schema changes
"Decibel!" <[EMAIL PROTECTED]> writes: > On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: >> Also, incidentally do you have a good reason to use CHAR instead of varchar >> or >> text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you >> don't >> store anything more in it. text or varchar will take only as many bytes as >> the >> data you're storing (plus 4 bytes). > > Hrm, do we actually pad before storing? ISTM we should really do that the > other way around... Yes we do. And it isn't really fixable either. The problem is the familiar old problem that in Postgres the typmod is not really part of the type and not always available when we need it to interpret the datum. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ERROR: failed to re-find parent key in "pk_ep07"
"Tom Lane" <[EMAIL PROTECTED]> writes: > Otniel Michael <[EMAIL PROTECTED]> writes: >> When i was vacuum the database, the vacuum if failed. And I get this error. >> Any ideas an to fix this? >>ERROR: failed to re-find parent key in "pk_ep07" > > Update to a newer PG version, possibly? This symptom has been seen > before... It was fixed in these bug-fix releases: 7.4.15, 8.0.10, and 8.1.6 which were released on August 1st of this year. There have actually been 3 to 4 more bug-fix releases since those too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Returning the total number of rows as a separate column when using limit
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: >> On Monday 05 November 2007 15:18:22 Tom Lane wrote: >>> That's only an estimate. Since the query doesn't get executed to >>> completion thanks to the LIMIT, Postgres really has no idea whether >>> the estimate is accurate. > >> Ok. The query is ORDER-ed, but you're saying that it doesn't matter and PG >> still doesn't have to know the total numbers even if it has to sort the >> result? At a guess you're displaying pages of information and want to display something like "displaying 1-10 of 150" ? Postgres is kind of lacking a solution for this problem. The last time I needed to do this I bit the bullet and ran the query twice, once with a "select count(*) from (...)" around it and once with "select * from (...) order by x offset n limit m" around it. The second time runs relatively quickly since all the raw data is in cache. The "right" way to do this would probably be to have a temporary table which you populate in one step, perform the count(*) on in a second query, then select the page of data with the ordering in a third query. Then you can keep the data around for some limited amount of time in case the user accesses a second page. But this requires some infrastructure to keep track of the cached data and what query it corresponded to and determine when to replace it with new data or drop it. However Postgres temporary tables are fairly expensive and if you're creating them for every web access you're going to have to vacuum the system catalogs quite frequently. They're not really well suited for this task. Alternatively you could create a cursor and play with that. But I don't think that's a great solution either. (yet? I think cursors are getting more useful in Postgres, perhaps it will be eventually.) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Returning the total number of rows as a separate column when using limit
"Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > That's what I'm doing now. I run the query with "limit+1" as limit and if it > results in more than limit, I know there is more data and I run count(*) to > count them all. But count(*) cannot use indices in PG so it's limited in > speed anyway AFAICS. Well count(*) can use indexes the same as the query can. > I really hoped there was an "Oracle over()" equivalent way in PG. I > understand > that Oracle's LIMIT-hack with "3 subselects and rownum between 1 AND 20" is > rather expensive compared to PG's implementation of LIMIT. Oralce keeps > snapshot-info in the index, so counting only involves the index AFAIK. Well that's only going to be true if the index satisfies the whole query which is not going to be true for the simplest cases. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Returning the total number of rows as a separate column when using limit
"Gregory Stark" <[EMAIL PROTECTED]> writes: > "Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > >> That's what I'm doing now. I run the query with "limit+1" as limit and if it >> results in more than limit, I know there is more data and I run count(*) to >> count them all. But count(*) cannot use indices in PG so it's limited in >> speed anyway AFAICS. > > Well count(*) can use indexes the same as the query can. > >> I really hoped there was an "Oracle over()" equivalent way in PG. I >> understand >> that Oracle's LIMIT-hack with "3 subselects and rownum between 1 AND 20" is >> rather expensive compared to PG's implementation of LIMIT. Oralce keeps >> snapshot-info in the index, so counting only involves the index AFAIK. > > Well that's only going to be true if the index satisfies the whole query which > is not going to be true for the simplest cases. er, *except* for the simplest cases. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] functions are returns columns
"Michele Petrazzo - Unipex srl" <[EMAIL PROTECTED]> writes: > I try with: > CREATE FUNCTION getfoo (IN int, OUT int, OUT int) AS $$ >SELECT fooid, foosubid FROM foo WHERE fooid = $1; > $$ LANGUAGE SQL; > > but only one row returned... You're almost there: CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int) AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; The return type if present has to match the OUT (and BOTH) parameters. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] functions are returns columns
"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> You're almost there: > >> CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof >> record(int,int) AS $$ >> SELECT fooid, foosubid FROM foo WHERE fooid = $1; >> $$ LANGUAGE SQL; > > Not quite --- it's just "returns setof record". The output column types > are defined by the OUT parameters. The only reason you need the returns > clause is to have a place to stick the "setof" specification ... ok... I did test my example before posting it: postgres=# postgres=# CREATE or replace FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int)AS $$ SELECT 1,2 union all select 2,3; $$ LANGUAGE SQL; postgres$# postgres$# CREATE FUNCTION postgres=# postgres=# select * from getfoo(1); column1 | column2 -+- 1 | 2 2 | 3 (2 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] postgres bogged down beyond tolerance
"Tena Sakai" <[EMAIL PROTECTED]> writes: > Namely, I shutdown the database, issued two commands: > /sbin/sysctl -w kernel.shmmax=134217728 > /sbin/sysctl -w kernel.shmall=2097152 > and rebooted the computer. > > After it came up, I checked the shmmax and it is set > as 33554432. Which surprised me. Since I used -w > flag, I thought it should've written to /etc/sysctl.conf, > but there is no such entry at all and the data of this > file is from 2006. sysctl changes the values for the running kernel. /etc/sysctl is a file you edit manually to tell the boot scripts what values to store (using sysctl) so you don't have to run sysctl every time you reboot. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pg_clog (?) problem with VACUMM
"Gerardo Herzig" <[EMAIL PROTECTED]> writes: > Hi all. Well, im having this problem for the first time. > When executing a VACUUM, i have this msg: > gse_new_version=# VACUUM ; > ERROR: could not access status of transaction 118 > DETAIL: could not open file "pg_clog/": No existe el fichero o el > directorio This is the typical error you see if the data is corrupted by bad memory or disk. It's trying to check if the record was committed or aborted and finding a bogus transactionid. (Except in this case I'm not sure that the transaction id is necessarily bogus. It's awfully early though. Was the table in the template database created soon after running initdb? And then you're creating a copy of the database much later?) It's also possible it's one of the various bugs fixed in the bug-fix releases 8.1.3 through 8.1.10. I don't see any related to clog but there are some related to vacuum which could cause data corruption. But aside from upgrading immediately to 8.1.10 I would suggest you run memtest86 (assuming it's a x86 machine) or some other memory checker. Bad memory is quite common and could easily cause this. Also run some SMART tool to check the disks. > Ok, wtf, i will create the file. So touch pg_clog/ and VACUUM again > ERROR: could not access status of transaction 118 > DETAIL: could not read from file "pg_clog/" at offset 0: Conseguido That's almost the right idea for a last ditch attempt to extract what data you can from a corrupted table. You have to fill the file with nul bytes though. Something like dd if=/dev/zero of= bs=1k count=nnn where nnn is, uh, I'm not sure how large, it won't take much to cover transactionid 118 though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] marking tuples
"Ehab Galal" <[EMAIL PROTECTED]> writes: > In my algorithm, each NewJoin node may "mark" its ps_ResultTupleSlot with > either red/green. I need that mark to flow in the pipeline so that i can > collect them at the top aggregate. How will you tell about the marks from different NewJoins? I think you'll have to "project" the tuple and add a resjunk column to indicate that flag. I don't know how you'll identify that column from other such flag columns other NewJoin nodes have added. You could look at the setop code in prepunion.c like intersection to see other plans which add flag columns. In those cases though they are used by the direct parent of the node which added it, so the planner can just mark a field in the parent indicating which column it should look at for the flag. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] UTF8 encoding and non-text data types
"Joe" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Oh? Interesting. But even if we wanted to teach Postgres about that, >> wouldn't there be a pretty strong risk of getting confused by Arabic's >> right-to-left writing direction? Wouldn't be real helpful if the entry >> came out as 4321 when the user wanted 1234. Definitely seems like >> something that had better be left to the application side, where there's >> more context about what the string means. >> > The Arabic language is written right-to-left, except ... when it comes to > numbers. I don't think that matters anyways. Unicode strings are always in "logical" order, not display order. Displaying the string in the right order is up to the display engine in the Unicode world-view. I'm not sure what to think about this though. It may be that Arabic notation are close enough that it would be straightforward (IIRC decimal notation was invented in the Arabic world after all). But other writing systems have some pretty baroque notations which would be far more difficult to convert. If anything I would expect this kind of conversion to live in the same place as things like roman numerals or other more flexible formatting. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Sql ORDER BY and ASC/DESC question
"A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes: >> Hello everyone, >> >> I have following problem: am using pl/sql functions to trigger some >> sql code and i need to pass ORDER_BY column name and ASC/DESC sorting >> order as an input parameters into that function and order the result >> based on these input parameters. >> >> The problem is, that the only way is to create query as "string" and >> then execute it as "string". > > Right. >> >> Is there any other way around how to avoid that "string query"? If you're not concerned with the planner being able to find indexes to satisfy these orderings (ie, you don't mind always doing a sort) you could do something like: ORDER BY CASE ? WHEN 1 THEN name ASC WHEN 2 THEN name DESC WHEN 3 THEN height ASC WHEN 4 THEN height DESC ELSE id ASC END But to the planner this will be basically an opaque expression. It won't be able to use any indexes on these columns. Also, incidentally you might want to use text strings instead of integer labels. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sql ORDER BY and ASC/DESC question
"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: > >> ORDER BY >> CASE ? >> WHEN 1 THEN name ASC > > Uh, no, putting the ASC/DESC decoration inside a CASE like that is not > gonna work doh! I had a feeling something was wrong but couldn't put my finger on it before I hit send. Sigh. > For numerical sort keys you can cheat by using "-x" in place of > "x DESC", but I'm not aware of any equivalent hack for text keys. Yeah, you could do a really kludgy thing with a second sort expression where you null out one expression or the other depending on the parameter but it starts to look more and more spaghetti-like. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Check before INSERT INTO
"Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> writes: > Thanks for the reply Grogory. I am trying to do a INSERT INTO. > > Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key > (Serial not null) > id | n_gen | n_sheet | tot_n_sheet > --+---+---+- > a| 1| 1 | 1 > b| 2| 1 | 2 > x| 2| 2 | 2 > u| 3| 1 | 1 > r| 4| 1 | 3 > a| 4| 2 | 3 > s| 4| 3 | 3 > > > So there are 2 users inserting in to the db. In my ASP page i have a field > that > shows the value of n_gen +1. So when the 2 users both login at the same time, > with different sessions, they both see "7" in the n_gen field. But when they > click on the sumbit button only one record is inserted and the other is lost. > > I though it was possible to change the SQL string before it does the update.. > But i can't seem to find a solution for it.. Any idea ?? You'll have to explain what you want n_gen to contain. Then you'll have to decide whether you want to do this in ASP where you can certainly change the SQL all you like, or on the server where you can have triggers which change the values being stored or executing additional queries. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Check before INSERT INTO
"Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> writes:
> The user updates the DB via ASP. When 2 users click on the submit button at
> the
> same time, only 1 record is inserted. (ERROR: duplicate key violates unique
> constraint "my_shevi_pkey")
>
> For example they both send a string like below.
> strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
>
> I thought of adding a test before executing the insert into.
It's not clear to me what you're trying to do. If you're trying to update an
existing record then you might want something like example 37-1 on this page:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html
If you want to pick the first available n_gen then you're going to have to
repeatedly try inserting until you don't get that error. That will perform
quite poorly when you get to large values. You could do a "SELECT max(n_gen)
WHERE..." first but even that will be quite a lot of work for your database.
Perhaps you should rethink n_gen and use a serial column to generate your
primary key instead.
> Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET),
> upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND
> N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
For what it's worth your script is a security hole. Look into using query
parameters which in ASP will probably be represented by "?". The method above
will allow hackers to get direct access to your database and do nasty things.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] REFERENCES and INHERITS restrictions?
"Stefan Scheidegger" <[EMAIL PROTECTED]> writes: > Why is this not possible? It seems that this is about inheritance. When I > reference directly to tbl_child1, everything works just fine. I'm afraid inheritance doesn't work with foreign key references. You can have references to specific tables but they don't include any child tables. A related limitation is that you can't enforce unique constraints across tables and their children. Solving this doesn't seem to be high on anyone's priority list. If anything what interests more people is using inheritance for partitioning support. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Subqueries
"Pascal Tufenkji" <[EMAIL PROTECTED]> writes: > I understand the fact that "the inner query is executed before the outer > query and the inner query doesn't even know about the outer query." > > But why the following query can be executed, although the inner query is > using the outer query. > > Aren't we here using the same concept ? It's not that inner queries can't refer to outer queries. When they do it's called a "correlated subquery" and it has to be executed once for every row of the outer query. It's that queries on one side of a join can't refer to tables on the other side of the join. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] regexp_replace and UTF8
"Bart Degryse" writes: > Hi, > I have a text field with data like this: 'de patiënt niet' > Can anyone help me fix this or point me to a better approach. > By the way, changing the way data is put into the field is > unfortunately not an option. > Many thanks in advance. You could use a plperl function to use one of the many html parsing perl modules? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Funtion to clean up strings?
Andreas writes: > Hi > Thanks, that really works :) > > Now a last extension. > Some numbers were entered in a "110% perfect" way with an excessive (0). +49 > (0) 123 / 456 789 > I have to suspect the source liked to express that it's either +49 or 0 if > the +49 isn't applicable, but not both. This is the standard format for phone numbers. Parenthesized digits -- as you suspected -- represent digits which must only be dialled when using the number locally and must be omitted from outside. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
