Re: [GENERAL] invalid UTF-8 byte sequence detected
Hi! -Ursprüngliche Nachricht- Von: Bruce Momjian [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. November 2005 19:46 An: Markus Wollny Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] invalid UTF-8 byte sequence detected I am also confused how invalid UTF8 sequences got into your database. It shouldn't have been possible. Our databases were originally encoded in ASCII, though they should have been LATIN9 - or UNICODE; this has been remedied a long time ago using iconv on the dumps; our PostgreSQL-version then was 7.4 and we converted databases and dumps to UTF-8. Maybe the invalid byte sequences have been entered sometimes during our migration from ODBC to JDBC while our encoding was still a mess - though I would have thought that this should have been resolved by dumpiconvrestore then. However, I do suspect that the cause of the issue was really more or less a bug in PostgreSQL 8.1, which accepted certain illegal byte sequences. I our case, I found that the re-import of the dump errored out on ISO-8859-1 encoded backticks (´) - certain mournfully misled individuals do use this degu-character instead of the apostrophe even tough it's more difficult to type on a german keyboard layout. And quite wrong, too. Anyway, I found some reference in the hackers-list that encoding-consistency for Unicode has been tightened down (see http://archives.postgresql.org/pgsql-hackers/2005-10/msg00972.php ). Both a solution and a suggestion have been posted in this thread; Christopher Kings-Lynne has suggested to include a reference to this issue in the 'upgrading/back compatibiliy' section for these release notes - I do strongly second his suggestion :) The suggested solution was to feed the plain dump again through iconv; however at least on my systems (Debian Sarge) iconv didn't like my 5GB sized dump files. So in order to successfully reimport the dumps, I had to split --line-bytes=650m the SQL-file, pass the parts through iconv -c -f UTF8 -t UTF8 and concatenate them back into one file again. There were no more errors on feeding the dump back into psql and I didn't come across any missing data during my tests, so this has definitely done the trick for me. As 8.1 has tightened down encoding-consistency for Unicode, I believe that the databases should be safe from any illegal byte-sequences in text-fields from now on. Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Bug with index-usage?
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
I get unpredictibale results selecting from a view depending on
index-usage.
It's not actually *using* the indexes, although presence of the indexes
does seem to be needed to trigger the bug:
regression=# explain SELECT * from test WHERE type = 'a';
QUERY PLAN
---
Nested Loop Left Join (cost=0.00..2.29 rows=1 width=40)
Join Filter: ((outer.type = 'c'::text) OR (outer.type = 'b'::text))
- Seq Scan on test2 (cost=0.00..1.16 rows=1 width=40)
Filter: ((type = 'a'::text) AND ((type = 'c'::text) OR (type =
'b'::text)))
- Seq Scan on test2 t2 (cost=0.00..1.11 rows=1 width=0)
Filter: (type = 'a'::text)
(6 rows)
regression=# drop index index_b;
DROP INDEX
regression=# explain SELECT * from test WHERE type = 'a';
QUERY PLAN
---
Nested Loop Left Join (cost=0.00..2.24 rows=1 width=40)
Join Filter: ((outer.type = 'c'::text) OR (outer.type = 'b'::text))
- Seq Scan on test2 (cost=0.00..1.11 rows=1 width=40)
Filter: (type = 'a'::text)
- Seq Scan on test2 t2 (cost=0.00..1.11 rows=1 width=0)
Filter: (type = 'a'::text)
(6 rows)
It looks like the problem is that the new 8.1 OR-index-qual code is
confused about when it can apply outer-join conditions. It shouldn't be
propagating the outer-join condition into the scan condition on test2,
but it is. Will fix.
regards, tom lane
Hi,
thanks for lookin into it.
I patched my 8.1 installation with the following changes:
http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461
The error described above doesn't exist any more, but it's still
buggy. Just create a view which is left-joining to an other table. The
joined columns don't show up in the view.
CREATE OR REPLACE VIEW test_ AS
SELECT test2.*, test1.name
FROM test2
LEFT JOIN test1 ON test1.id = test2.test1_id
LEFT JOIN test2 AS t2 ON
test2.type IN ('c','b') AND
t2.type = 'a';
In 8.0 I get:
SELECT * from test WHERE type = 'a';
id | type | test1_id | name
+--+--+-
1 | a|1 | test1_1
2 | a|2 | test1_2
3 | a|3 | test1_3
(3 rows)
In 8.1 (with or without your patches) I get:
SELECT * from test_ WHERE type = 'a';
id | type | test1_id | name
+--+--+--
1 | a|1 |
2 | a|2 |
3 | a|3 |
(3 rows)
Hope you could repeat the problem. Otherwise, please contact me.
Sebastian
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Tsearch2: casting text to tsquery
Hello,
I have a question that arose while using tsearch on a large database
of book information.
In order to inject unstemmed + stemmed versions of searchstrings into
a query we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works just
fine while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] shouldn't postgres know the numer of rows in a (sorted) result-set before returning the first row?
hi, i have some system where i show pages results on a web-page - the query that returns the paged result looks like this: (table has a few hundred thousand rows, result-set is ~3) a) select asset.asset_id, asset.found_time from asset.asset WHERE found_time 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS NULL order by found_time desc LIMIT 50 OFFSET 0 this query returns data in 0.064secs. if i now want to display the pure number of documents that this query would generate without the limit clase i would do: b) select count(asset.asset_id) from asset.asset WHERE found_time 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS NULL this query takes 6 seconds! i understand that postgres has to read every row from the heap to make sure that they are all still valid and count. but from my understanding query (a) would have something like an uncorrected count (somewhere internally) for the whole query as it has to performed an order by on the result-set before returning the first row. i would be interested in getting this uncorrected count after sort but before first row in query (a). so in a fresh DB with no updates/deletes this would be the correct count, and i could avoid the very expensive (b). i'd like to hack that feature into my local portgres, i'm not asking for inclusion in the official postgres, but could someone direct me if my idea is feasable and where to look in the code (8.1)? regards, thies ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tsearch2: casting text to tsquery
On Wed, 16 Nov 2005, Alexander Presber wrote:
Hello,
I have a question that arose while using tsearch on a large database of book
information.
In order to inject unstemmed + stemmed versions of searchstrings into a query
we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works just fine
while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
no workaround needed. to_tsquery provides everything you need. If you
want fancy-shmancy solution you could always write wrapper around tsquery, but
I doubt you enter queries by hand, so why do you bothering ?
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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: [GENERAL] shouldn't postgres know the numer of rows in a (sorted)
Thies C Arntzen wrote: i would be interested in getting this uncorrected count after sort but before first row in query (a). so in a fresh DB with no updates/deletes this would be the correct count, and i could avoid the very expensive (b). You don't say what applicaton language you are using, but most offer a pg_num_rows() interface which tells you how many results are in the recordset you have fetched. Your best bet to learn more is to read whatever documentation comes with your client library. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tsearch2: casting text to tsquery
Am 16.11.2005 um 13:52 schrieb Oleg Bartunov:
On Wed, 16 Nov 2005, Alexander Presber wrote:
Hello,
I have a question that arose while using tsearch on a large
database of book information.
In order to inject unstemmed + stemmed versions of searchstrings
into a query we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works
just fine while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
no workaround needed. to_tsquery provides everything you need. If
you want fancy-shmancy solution you could always write wrapper
around tsquery, but
I doubt you enter queries by hand, so why do you bothering ?
No. No fancy-shmancy solution needed, just one that works.
I do not see a way to include both a stemmed and unstemmed version of
a string into a tsquery.
Can you give an example, please?
Thanks
Alex
P.S. I have read the documentation (twice.)
Sincerely yours
Alexander Presber
---(end of
broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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: [GENERAL] shouldn't postgres know the numer of rows in a (sorted) result-set before returning the first row?
Am 16.11.2005 um 14:07 schrieb Richard Huxton:You don't say what applicaton language you are using, but most offer a pg_num_rows() interface which tells you how many results are in the recordset you have fetched. my query uses LIMIT and OFFSET - so pg_num_rows will return what i specify in LIMIT (or less). that's not the count i was asking for.re, thies
Re: [GENERAL] shouldn't postgres know the numer of rows in a (sorted)
Thies C. Arntzen wrote: Am 16.11.2005 um 14:07 schrieb Richard Huxton: You don't say what applicaton language you are using, but most offer a pg_num_rows() interface which tells you how many results are in the recordset you have fetched. my query uses LIMIT and OFFSET - so pg_num_rows will return what i specify in LIMIT (or less). that's not the count i was asking for. Ah - apologies, I didn't read your post closely enough. I think the answer then is no. In some cases PG can short-circuit the query and stop once 50 are fetched, which means it doesn't always know. With your query I'm not sure whether it can or not. Your timings however suggest that this is what is happening, otherwise both queries would take approximately the same amount of time. One thing I have noticed though, is that the sort-order of your query might not be well defined. select asset.asset_id, asset.found_time from asset.asset WHERE found_time 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS NULL order by found_time desc LIMIT 50 OFFSET 0 Unless found_time is unique then you might get different results on two queries (since asset_id ordering is undefined). -- Richard Huxton Archonet Ltd ---(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: [GENERAL] shouldn't postgres know the numer of rows in a (sorted) result-set before returning the first row?
On Wed, Nov 16, 2005 at 01:23:08PM +0100, Thies C Arntzen wrote: hi, i have some system where i show pages results on a web-page - the query that returns the paged result looks like this: (table has a few hundred thousand rows, result-set is ~3) a) select asset.asset_id, asset.found_time from asset.asset WHERE found_time 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS NULL order by found_time desc LIMIT 50 OFFSET 0 this query returns data in 0.064secs. if i now want to display the pure number of documents that this query would generate without the limit clase i would do: b) select count(asset.asset_id) from asset.asset WHERE found_time 1130926914 AND pool_id in (1,2,3,4) AND asset.status IS NULL this query takes 6 seconds! Umm, the first query doesn't calculate all the output nor does it even have an estimate of it. Why do you think it does? i understand that postgres has to read every row from the heap to make sure that they are all still valid and count. but from my understanding query (a) would have something like an uncorrected count (somewhere internally) for the whole query as it has to performed an order by on the result-set before returning the first row. Not if you have an index on found_time. In that case it can return the top 50 without even looking at most of the table. That's what indexes are for. The only estimate it has is the one in EXPLAIN, and it can find that without running the query at all. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpytWyjwwbjY.pgp Description: PGP signature
Re: [GENERAL] shouldn't postgres know the numer of rows in a (sorted) result-set before returning the first row?
Am 16.11.2005 um 14:49 schrieb Martijn van Oosterhout: i understand that postgres has to read every row from the heap to make sure that they are all still valid and count. but from my understanding query (a) would have something like an uncorrected count (somewhere internally) for the whole query as it has to performed an "order by" on the result-set before returning the first row. Not if you have an index on "found_time". In that case it can return the top 50 without even looking at most of the table. That's what indexes are for. The only estimate it has is the one in EXPLAIN, and it can find that without running the query at all. hey martijn,my question is more in the line ofhttp://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.phpwhereby my special case is all about beeing able to provide an [possible inaccuate] count for a query if possible: my understanding is that would be the case if the "where clase" and the "order by" clause have been satisfied from the indices and the only step left is to validate the records in the result by reading them from the heap. and -again- i'm not asking for a new feature but i'd like to play with it and am asking for hackers advice;-)what am i missing?re, thies
Re: [GENERAL] Wrong rows selected with view
Bill Moseley writes: PostgreSQL 7.4.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050816 (prerelease) (Debian 4.0.1-5) Hopefully this is something simple -- I assume it's a problem with my SQL. But it looks really weird to me at this late hour. I have some tables for managing workshops, and I have a view (below). I noticed for a given select it was returning the wrong number of rows. I expected seven rows back, but only got six. I rewrote the view with just the joins and it returned the correct number of rows. So I started the brute force method of removing one thing at a time in the view to see what would make it start returning the correct number of rows. That just confused me more. How does the query plan change when you make those changes? If it only occurs if a certain index is used, it might be corrupt (= REINDEX). regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] shouldn't postgres know the numer of rows in a (sorted) result-set before returning the first row?
On Wed, Nov 16, 2005 at 03:33:10PM +0100, Thies C. Arntzen wrote: my question is more in the line of http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php whereby my special case is all about beeing able to provide an [possible inaccuate] count for a query if possible: my understanding is that would be the case if the where clase and the order by clause have been satisfied from the indices and the only step left is to validate the records in the result by reading them from the heap. The problem is that in the index scan you indicated, it goes no further through the index than necessary to produce your answer. And it can't satisfy the where clause from the index (unless those columns are in your index, you don't say). The logic (very simplified) basically goes: 1. Get next entry in index 2. Does entry match, if not goto 1 3. Extract matching tuple from heap 4. Check visibility and where clause 5. If not match, goto 1 6. Return this tuple 7. Have we returned 50 rows yet, if not goto 1 8. finish As you can see, when you get to 8 you have no idea how much of the index you scanned and no idea how much of the table you scanned. You really have *no* idea how many more there might be. For example, say step 1 generated 300 tuples, step 2 passed 200 of them and step 4 passed 50 of those (which it returned). How many tuples will the query return in the end? It's your assumption that we actually examine more of the index than necessary that's wrong. and -again- i'm not asking for a new feature but i'd like to play with it and am asking for hackers advice;-) Get the result from EXPLAIN, it's about as good as any other estimate we can produce... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpKoCP51eRcy.pgp Description: PGP signature
Re: [GENERAL] installation(pg_depend) problem
HS [EMAIL PROTECTED] writes: creating template1 database in /usr/local/Robust/pgsql/data/base/1 ... ok initializing pg_shadow ... ok enabling unlimited row size for system tables ... ok initializing pg_depend ... cannot connect : Connection refused child process exited with exit code 1 That's completely bizarre. There isn't anything the pg_depend step does that's very different from stuff already done in the earlier steps. Also, I can't find the string cannot connect anywhere in the PG source code, so it's not apparent where the error is coming from ... somewhere down in glibc maybe? You might try running the initdb process under strace -f -o logfile to see if you can get more info. The output will probably be voluminous, so don't post it all; but the last few hundred lines might yield a clue as to what the code is doing when it fails. regards, tom lane ---(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: [GENERAL] Wrong rows selected with view
On Wed, Nov 16, 2005 at 03:42:19PM +0100, Andreas Seltenreich wrote: How does the query plan change when you make those changes? If it only occurs if a certain index is used, it might be corrupt (= REINDEX). I did a reindex database ws2; and no change. I'm not very good at reading the query plans. For one thing, they always send me off on some tangent wondering why it's doing a Seq Scan instead of a index scan. ;) The first plan below returns the correct number of rows, the second plan does not. These are after I did the reindex, btw. ws2= explain select id from bar where person_id = 84; Subquery Scan bar (cost=1225.81..1243.32 rows=6 width=4) Filter: (person_id = 84) - Unique (cost=1225.81..1230.82 rows=1000 width=334) - Sort (cost=1225.81..1228.31 rows=1003 width=334) Sort Key: class.id - Hash Join (cost=802.15..1175.81 rows=1003 width=334) Hash Cond: (outer.person = inner.id) - Hash Join (cost=67.50..203.81 rows=1003 width=315) Hash Cond: (outer.region = inner.id) - Hash Join (cost=45.00..163.77 rows=1002 width=279) Hash Cond: (outer.location = inner.id) - Hash Join (cost=22.50..118.74 rows=1001 width=141) Hash Cond: (outer.class = inner.id) - Seq Scan on instructors (cost=0.00..20.00 rows=1000 width=8) - Hash (cost=20.00..20.00 rows=1000 width=137) - Seq Scan on class (cost=0.00..20.00 rows=1000 width=137) - Hash (cost=20.00..20.00 rows=1000 width=142) - Seq Scan on location (cost=0.00..20.00 rows=1000 width=142) - Hash (cost=20.00..20.00 rows=1000 width=40) - Seq Scan on region (cost=0.00..20.00 rows=1000 width=40) - Hash (cost=649.12..649.12 rows=10212 width=23) - Seq Scan on person (cost=0.00..649.12 rows=10212 width=23) (22 rows) This returns one row less and the only change to the view is this commented out column: -- class.full_message AS full_message, -- this ws2= explain select id from bar where person_id = 84; Subquery Scan bar (cost=1222.54..1240.05 rows=6 width=4) Filter: (person_id = 84) - Unique (cost=1222.54..1227.55 rows=1000 width=366) - Sort (cost=1222.54..1225.05 rows=1003 width=366) Sort Key: class.id - Hash Join (cost=779.65..1172.54 rows=1003 width=366) Hash Cond: (outer.person = inner.id) - Hash Join (cost=45.00..204.14 rows=1003 width=347) Hash Cond: (outer.region = inner.id) - Hash Join (cost=22.50..164.10 rows=1002 width=311) Hash Cond: (outer.location = inner.id) - Merge Join (cost=0.00..119.06 rows=1001 width=173) Merge Cond: (outer.id = inner.class) - Index Scan using class_pkey on class (cost=0.00..52.00 rows=1000 width=169) - Index Scan using instructors_class_index on instructors (cost=0.00..52.00 rows=1000 width=8) - Hash (cost=20.00..20.00 rows=1000 width=142) - Seq Scan on location (cost=0.00..20.00 rows=1000 width=142) - Hash (cost=20.00..20.00 rows=1000 width=40) - Seq Scan on region (cost=0.00..20.00 rows=1000 width=40) - Hash (cost=649.12..649.12 rows=10212 width=23) - Seq Scan on person (cost=0.00..649.12 rows=10212 width=23) (21 rows) -- Bill Moseley [EMAIL PROTECTED] ---(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: [GENERAL] Wrong rows selected with view
Bill Moseley [EMAIL PROTECTED] writes: The first plan below returns the correct number of rows, the second plan does not. These are after I did the reindex, btw. Bizarre. What are the datatypes of the columns being joined on? If they're string types, what's your database locale and encoding? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PREPARE TRANSACTION and webapps
At 11:27 PM 11/15/2005 -0500, Tom Lane wrote: That said, it seems to me that the prepared-xacts infrastructure could possibly support a separate suspend transaction and resume transaction facility, if anyone wants to do the legwork to make it happen. What this would actually be useful for is a fair question though --- what's it do that you don't have now? MVCC-style transactions that are not limited by/to database connections. This could be useful if you want to have X pending database transactions and Y max concurrent database connections, where X is significantly greater than Y (magnitudes higher?). My assumption is that pending transactions (e.g. locks and other metainfo) will take much less memory than database backends. It'll be nice (but it might be difficult) to have an implementation that allowed migration of transactions to a different node in a cluster - so that one could bring down a database node server in the middle of a transactions without affecting database users/applications severely. A suitable protocol might allow a database client to automatically save its transaction, and then resume it on another node, without the database user/app noticing much (not sure if this is a good idea though). With respect to concerns about users leaving transactions open for long periods, this sort of thing already happens with the current implementation. As such, similar measures can be taken: rollback/commit the offending transactions. One needs a way of listing information about pending transactions, and some methods to manage them. My assumption is managing pending transactions would be easier than reimplementing MVCC and the other stuff. Especially if only controlled types of transactions are saved and resumed - one scenario might even put such transactions in a different database so as not to affect other transactions. But I could be wrong :). Sure one could create a tangled mess with thousands of transactions. But I don't think that's the fault of supplying X amounts of rope instead of Y amounts of rope, where X Y. Are there RDBMSes out there with this feature already? I'm not sure what keywords to search for. I suspect it might be very difficult to do on a database without an MVCC architecture. Regards, Link. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Wrong rows selected with view
On Wed, Nov 16, 2005 at 10:53:21AM -0500, Tom Lane wrote:
Bill Moseley [EMAIL PROTECTED] writes:
The first plan below returns the correct number of rows, the second plan
does
not. These are after I did the reindex, btw.
Bizarre. What are the datatypes of the columns being joined on? If
they're string types, what's your database locale and encoding?
The primary keys are all SERIAL, and the FKs are integer. Nothing too
odd.
The odd thing is the row that is not returned is basically a clone of
another row -- which is why I diff'ed them in my first posting.
BTW, this might be obvious, but the reason I'm doing DISTINCT ON
class.id is that the instructors table is a link table and a class can
have more than one instructor. I only want a list of classes, not one
per instructor (which could duplicate them).
I'm still a novice with Pg, so I assume this is what you are asking
(although none of my joins are on text fields).
ws2= SHOW LC_CTYPE;
-[ RECORD 1 ]---
lc_ctype | en_US
ws2= SHOW SERVER_ENCODING;
-[ RECORD 1 ]---+---
server_encoding | LATIN1
So my joins are:
WHERE class.location = location.id -- join with location
AND class.id = instructors.class -- join the
instructors
AND instructors.person = person.id -- join the person(s)
AND location.region = region.id;-- join the location to
a region
And the .id are all SERIAL integer and the FKs are all integer.
Trying to avoid sending too much unnecessary data to the list, but
here's a sample of the tables:
ws2= \d region
Table public.region
Column | Type | Modifiers
+-+
id | integer | not null default nextval('public.region_id_seq'::text)
active | boolean | not null default true
sort_order | integer | not null default 1
name | text| not null
Indexes:
region_pkey primary key, btree (id)
region_name_key unique, btree (nam
ws2= \d instructors
Table public.instructors
Column | Type | Modifiers
+-+---
person | integer | not null
class | integer | not null
Indexes:
instructors_pkey primary key, btree (person, class)
instructors_class_index btree (class)
instructors_person_index btree (person)
Foreign-key constraints:
$1 FOREIGN KEY (person) REFERENCES person(id)
$2 FOREIGN KEY (class) REFERENCES class(id)
ws2= \d class
Table public.class
Column |Type |
Modifiers
-+-+---
id | integer | not null default
nextval('public.class_id_seq'::text)
name| text| not null
old_id | integer |
location| integer | not null
workshop| integer | not null
class_time | timestamp(0) with time zone | not null
class_end_time | timestamp(0) with time zone | not null
class_size | integer | not null
begin_reg_time | timestamp(0) with time zone |
class_list_sent_time| timestamp(0) with time zone |
class_list_sent_email | text|
reminder_sent_time | timestamp(0) with time zone |
ride_list_sent_time | timestamp(0) with time zone |
html_description| text| not null
short_description | text|
special_instructions| text|
on_hold_message | text|
review_mode | boolean | not null default false
workshop_group | integer | not null
distance_ed | boolean | not null default false
contract_class | boolean | not null default false
online_evaluation | boolean | not null default true
price_scheme| integer | not null
duration| text|
register_cutoff_time| timestamp(0) with time zone | not null
cutoff_message | text|
full_message| text|
wait_list_size | integer |
wait_description| text|
wait_instructions | text|
wait_email_instructions | text|
cancel_late_hours | integer |
cancel_cutoff_hours | integer |
[GENERAL] Rebranding PostgreSQL
Guys, In our effort to distribute PostgreSQL to our customers, our higher ups would like to reduce the visibility that it is indeed PostgreSQL for a number of reasons at a few of our customer sites (particularly because these particular customers are very wary of open source). I know the license allows rebranding, but is there a document anywhere that specifies just what you have to do to do it? Is it as simple as regexing strings in the source, compiling, and renaming the exectuables? Or is it fraught with twisty little passages? Thanks, John Concerned about your privacy? Instantly send FREE secure email, no account required http://www.hushmail.com/send?l=480 Get the best prices on SSL certificates from Hushmail https://www.hushssl.com?l=485 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PREPARE TRANSACTION and webapps
Lincoln Yeoh lyeoh@pop.jaring.my writes: At 11:27 PM 11/15/2005 -0500, Tom Lane wrote: That said, it seems to me that the prepared-xacts infrastructure could possibly support a separate suspend transaction and resume transaction facility, if anyone wants to do the legwork to make it happen. What this would actually be useful for is a fair question though --- what's it do that you don't have now? MVCC-style transactions that are not limited by/to database connections. This could be useful if you want to have X pending database transactions and Y max concurrent database connections, where X is significantly greater than Y (magnitudes higher?). I don't think the prepared-xacts facility has the performance that would be needed to sustain that kind of usage. Suspend/resume would not be all that cheap, and a suspended transaction would still hold a lot of resources (locks mostly). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Rebranding PostgreSQL
[EMAIL PROTECTED] wrote: Guys, In our effort to distribute PostgreSQL to our customers, our higher ups would like to reduce the visibility that it is indeed PostgreSQL for a number of reasons at a few of our customer sites (particularly because these particular customers are very wary of open source). I know the license allows rebranding, but is there a document anywhere that specifies just what you have to do to do it? Is it as simple as regexing strings in the source, compiling, and renaming the exectuables? Or is it fraught with twisty little passages? Wow, we never got that question before. There is no legal requirement that people know they are running PostgreSQL, and some products do not use PostgreSQL in their name, so on that front you are fine. However, keep in mind that the changes you are suggesting will have a cost associated with them, in doing the changes, and finding all the place where the changes are required. PostgreSQL is pretty complex and even changing error messages can make things like internationalization or tests for specific messages in interface libraries fail. Basically, there isn't anything magic to the process except understanding all the applicable code well enough to know your changes are safe and thorough. Ultimately, you might end up reinforcing your users' bias, not because open source is unreliable, but because your version is. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Wrong rows selected with view
Bill Moseley [EMAIL PROTECTED] writes: On Wed, Nov 16, 2005 at 10:53:21AM -0500, Tom Lane wrote: Bizarre. What are the datatypes of the columns being joined on? If they're string types, what's your database locale and encoding? The primary keys are all SERIAL, and the FKs are integer. Nothing too odd. Well, that eliminates the theory I had, which was that string comparison was messing up because of incompatible locale/encoding choices. I think you may have found a PG bug. Can you duplicate the misbehavior if you dump the data and reload it into a fresh database? (Note you'll probably need to re-ANALYZE to get back to the same query plans.) If so, would you be willing to send me the dump off-list? If the data is not too sensitive, this'd probably be easier than trying to find a smaller test case. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PREPARE TRANSACTION and webapps
On Thu, Nov 17, 2005 at 12:29:25AM +0800, Lincoln Yeoh wrote: MVCC-style transactions that are not limited by/to database connections. This could be useful if you want to have X pending database transactions and Y max concurrent database connections, where X is significantly greater than Y (magnitudes higher?). My assumption is that pending transactions (e.g. locks and other metainfo) will take much less memory than database backends. They make take less memory but they take many more resources. Backend don't take locks by themselves, transactions do. What I don't understand about this whole discussion is that the concurrency control needed on a user level is of a completely different nature to what a programmer needs when writing programs. Let me give an example: User 1: Opens record 1 and begins to edit User 2: Opens record 1 and begins to edit Obviously these should both succeed. reading data doesn't block. Ten minutes later user 1 submits an update and goes to lunch without committing. User 2 then does an update but he has to wait. How long? Well, by your definition, forever. I doubt user 2 will be very happy with that. The way I would think about it would be to (a) let user 2 know straight away someone else is already looking at this record. This is useful info, maybe they talked to the same customer? and (b) when user 2 submits his edit he should be warned there are conflict and be asked to resolve them. If you abort either transaction you're going to have some annoyed users on your hands. Both of these fall outside MVCC. You can already check if the record was modified since you looked at it, no extra features needed there. Can you give an example of where MVCC for long running transactions makes sense? The example given where you have an ordering system for a limited number of widgets where the ordering process might take some time to enter is silly. The discussion about concurrency control is bypassing the fact that what you really want is a queue. You know, there are 3 widgets available but 5 people started their orders before you. If they cancel you get yours. Much better than waiting an hour for everyone else to finish. It'll be nice (but it might be difficult) to have an implementation that allowed migration of transactions to a different node in a cluster - so that one could bring down a database node server in the middle of a transactions without affecting database users/applications severely. A suitable protocol might allow a database client to automatically save its transaction, and then resume it on another node, without the database user/app noticing much (not sure if this is a good idea though). This is a completely different kettle of fish. I'm not sure what it would take to serialise a transaction, maybe most of that is done already. My assumption is managing pending transactions would be easier than reimplementing MVCC and the other stuff. Especially if only controlled types of transactions are saved and resumed - one scenario might even put such transactions in a different database so as not to affect other transactions. But I could be wrong :). This is silly. Any transaction that updates a row will block any other transaction using that row until he commits or aborts. Putting it on another server doesn't change the fact that the row is locked *for everybody*. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpeJgOJW8jhR.pgp Description: PGP signature
Re: [GENERAL] PREPARE TRANSACTION and webapps
Tom Lane [EMAIL PROTECTED] writes: This could be useful if you want to have X pending database transactions and Y max concurrent database connections, where X is significantly greater than Y (magnitudes higher?). I don't think the prepared-xacts facility has the performance that would be needed to sustain that kind of usage. Suspend/resume would not be all that cheap, and a suspended transaction would still hold a lot of resources (locks mostly). Well it'll be better than having to maintain a connection for each transaction. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Bug with index-usage?
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes: I patched my 8.1 installation with the following changes: http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461 The error described above doesn't exist any more, but it's still buggy. Yup, you're right :-(. Looks like we haven't been doing adequate testing with complex OUTER JOIN clauses ... Fix committed. Thanks for the report! regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tsearch2: casting text to tsquery
On Wed, 16 Nov 2005, Alexander Presber wrote:
Am 16.11.2005 um 13:52 schrieb Oleg Bartunov:
On Wed, 16 Nov 2005, Alexander Presber wrote:
Hello,
I have a question that arose while using tsearch on a large database of
book information.
In order to inject unstemmed + stemmed versions of searchstrings into a
query we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works just fine
while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
no workaround needed. to_tsquery provides everything you need. If you want
fancy-shmancy solution you could always write wrapper around tsquery, but
I doubt you enter queries by hand, so why do you bothering ?
No. No fancy-shmancy solution needed, just one that works.
I do not see a way to include both a stemmed and unstemmed version of a
string into a tsquery.
Can you give an example, please?
This is quite different question and Teodor has already answered you -
use different configurations. But I'm wondering why do you need that.
Thanks
Alex
P.S. I have read the documentation (twice.)
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Rebranding PostgreSQL
On Wed, 2005-11-16 at 11:05, Bruce Momjian wrote: [EMAIL PROTECTED] wrote: Guys, In our effort to distribute PostgreSQL to our customers, our higher ups would like to reduce the visibility that it is indeed PostgreSQL for a number of reasons at a few of our customer sites (particularly because these particular customers are very wary of open source). I know the license allows rebranding, but is there a document anywhere that specifies just what you have to do to do it? Is it as simple as regexing strings in the source, compiling, and renaming the exectuables? Or is it fraught with twisty little passages? Wow, we never got that question before. There is no legal requirement that people know they are running PostgreSQL, and some products do not use PostgreSQL in their name, so on that front you are fine. However, keep in mind that the changes you are suggesting will have a cost associated with them, in doing the changes, and finding all the place where the changes are required. PostgreSQL is pretty complex and even changing error messages can make things like internationalization or tests for specific messages in interface libraries fail. Basically, there isn't anything magic to the process except understanding all the applicable code well enough to know your changes are safe and thorough. Ultimately, you might end up reinforcing your users' bias, not because open source is unreliable, but because your version is. If they don't really want to get into the we produce our flavor of PostgreSQL called XYZ business, maybe they should resell some flavor from someone else then? Seems to me education of the people who are wary of open source is the answer here, but some people are harder to teach than others. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Rebranding PostgreSQL
[EMAIL PROTECTED] writes: In our effort to distribute PostgreSQL to our customers, our higher ups would like to reduce the visibility that it is indeed PostgreSQL for a number of reasons at a few of our customer sites (particularly because these particular customers are very wary of open source). I know the license allows rebranding, but is there a document anywhere that specifies just what you have to do to do it? Is it as simple as regexing strings in the source, compiling, and renaming the exectuables? Or is it fraught with twisty little passages? The license may allow it, but you really need a fair amount of chutzpah to expect that people help you with it ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PG 8.1 on Dual XEON with FreeBSD 6.0
Hi, The FreeBSD 6.0 SMP Kernel recognizes my two XEONS as 4 CPUs. A single postgresql query, as I could see in the top utility, can use a maximum of 25% of CPU time, since it runs on one single virtual CPU, which means for me half of a XEON. Is that correct? If yes, is there a way to change that, and accelerate long queries (which are all CPU-bound) in giving them more processing time, like a full XEON? Both XEONS? Should I disable Hyperthreading for that? Thanks, -- Philippe Lang Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76 GSM: +41 (79) 351 49 94 Email: [EMAIL PROTECTED] Skype: philippe.lang ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] locked backend
Hi all, I've had today a strange lockup on our postgres data base. Postgres 8.0.3 Debian GNU/Linux 3.1 autovacuum set up The situation (diagnosed via pg_stat_activity): one table was locked by an update, a VACUUM ANALYZE was running for the same table (triggered by autovacuum), and a handful of inserts were waiting on the same table. After some checking around (see below for details), I decided to kill the UPDATE (it was holding up the inserts which means lost data for us and danger of connection starvation), and did it with kill processid from the command line, but no success: the backend didn't respond. Then I killed the VACUUM which exited promptly, the UPDATE was still blocked. Then I remembered there is a pg_cancel_backend function (seen it recently on this list), and tried that too, without success - the update was still blocked, and blocking all inserts. In this situation I decided to kill -9 the blocked backend, knowingly that this will basically restart the data base server, which did happen. Only it took a vry long time to come up again (~15-20 minutes), which I also find very strange... Now my actual question is: what can cause a backend to block so that it won't answer to kill or pg_cancel_backend ? Is there any other solution than kill -9 in these situations ? I'm pretty sure the update would never have finished, the box was mostly idle when I did all this, so I can exclude that the update was busy working. I actually have seen this a few times before, and had to do the same kill -9 to get back on track. Some details of the circumstances this happened: I'm attaching a file with some diagnostic output, together with the SQLs used to get them. I also checked ps on the command line to make sure the pg_stat_activity is not reporting phantom SQLs (as I had some occasions it did). Unfortunately the attached diagnostic is not complete, as I forgot to save it from the beginning, and some of it rolled out of my terminal's buffer... particularly, the VACUUM is not visible anymore, I already killed it by then. As you see, the update was the oldest query running (and for more than 3 hours too !!!), and when I looked first there was a VACUUM also running, being the immediate oldest entry after the UPDATE. The inserts came after it, and checking their locks, they were waiting for a lock on the problem_table, I don't remember exactly what kind of lock. I have no clue as of what's happening here, so I would be grateful for any help in diagnosing this further... Maybe I should mention that with the same data base (but a different box !) I have already seen this behavior 1-2 times, and 2 times the system locked up completely (the machine was not answering even for ssh, but then after restart nothing was suspect, an extensive hardware test did not find anything either). TIA for any help. Cheers, Csaba. prepare ps as SELECT procpid, substring(current_query for 97), to_char((now()-query_start), 'HH24:MI:SS') as t FROM pg_stat_activity where current_query not like '%insufficient%' and current_query not like '%IDLE%' order by t desc; prepare locks(bigint) as select c.relname, l.* from pg_locks l left outer join pg_class c on c.oid=l.relation where pid=$1 union all select c.relname, l.* from pg_locks l left outer join pg_class c on c.oid=l.relation where l.pid = (select ml.pid from pg_locks ml, pg_locks cl where cl.pid=$1 and not cl.granted and cl.transaction = ml.transaction and ml.mode = 'ExclusiveLock'); dbprod=# execute ps; procpid |substring |t -+---+-- 5239 | UPDATE problem_table SET ... FROM ... | 03:40:54 ... the VACUUM should be here... 5248 | insert into problem_table( ...| 03:37:00 5251 | insert into problem_table( ...| 03:26:07 5235 | insert into problem_table( ...| 03:25:20 5331 | insert into problem_table( ...| 03:24:58 5266 | insert into problem_table( ...| 03:24:58 5262 | insert into problem_table( ...| 03:24:58 5271 | insert into problem_table( ...| 03:24:57 5282 | insert into problem_table( ...| 03:24:48 5328 | insert into problem_table( ...| 03:24:47 5304 | insert into problem_table( ...| 03:24:41 5354 | insert into problem_table( ...| 03:24:39 5243 | insert into problem_table( ...| 03:24:24 5358 | insert into problem_table( ...| 03:23:46 5260 | insert into problem_table( ...| 03:21:57 5273 | insert into problem_table( ...| 03:21:52 5256 | insert into problem_table( ...| 03:21:39 5496 | insert into problem_table( ...| 03:21:23 5238 | insert into problem_table( ...| 03:21:17 5321 | insert into problem_table( ...|
[GENERAL] PHP PDO functions
Hi all, After searching for a framework which work with PostgreSQL PHP I've found Framewerk (http://svn.framewerk.org/) which seem to fit my needs, the only problem that I've found is that it use the PDO functions. Now maybe I'm wrong but I understood that database abstraction layers like PEAR::DB got their disadvantages, does PDO got disadvantages also compared for using specific pg_ functions? I'll refer this question to PHP mailing list also. Thanks in advance, Yonatan Ben-Nes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PREPARE TRANSACTION and webapps
Martijn van Oosterhout kleptog@svana.org writes: They make take less memory but they take many more resources. Backend don't take locks by themselves, transactions do. And backends have transactions implicitly. The point here is that if you're going to suspend transactions by leaving idle backends around that's an added cost over just suspending the transaction. It's not a trivial cost either, processes consume memory, they consume kernel resources and cause extra context switching. What I don't understand about this whole discussion is that the concurrency control needed on a user level is of a completely different nature to what a programmer needs when writing programs. Let me give an example: User 1: Opens record 1 and begins to edit User 2: Opens record 1 and begins to edit Obviously these should both succeed. reading data doesn't block. Ten minutes later user 1 submits an update and goes to lunch without committing. User 2 then does an update but he has to wait. How long? Well, by your definition, forever. I doubt user 2 will be very happy with that. There's nothing stopping you from coding up a daemon that checks for suspended transactions older than some predetermined policy and rolling them back automatically. If you invent your own transaction semantics above Postgres's you'll have to do the same thing anyways. The way I would think about it would be to (a) let user 2 know straight away someone else is already looking at this record. This is useful info, maybe they talked to the same customer? and (b) when user 2 submits his edit he should be warned there are conflict and be asked to resolve them. If you abort either transaction you're going to have some annoyed users on your hands. It's not obvious that these should both succeed anyways. I would have expected you to do SELECT ... FOR UPDATE and lock the record. This would still allow other sessions to retrieve the data but not begin editing it. You would presumably want to use NOWAIT as well and handle the error if it's already locked. That would prevent two users from ever getting to the edit screen. You could give the second user the option of breaking the lock -- rolling back the other user's transaction. Both of these fall outside MVCC. You can already check if the record was modified since you looked at it, no extra features needed there. Can you give an example of where MVCC for long running transactions makes sense? You're assuming a simple case of a single record. What if the update screen covers a complete data structure represented by many records in many tables. And the update itself requires multiple stages on several different screens. Now you reach a conflict and want to roll back all the changes from all those screens. That requires a fairly large amount of machinery and all that machinery already exists in Postgres. If you really need all that complexity it makes sense to leverage the tool you have that implements it all. I agree with Tom Lane here and the conventional dogma that you can nearly always avoid this entire problem. And avoiding the problem nearly always leads to simpler cleaner systems than trying to present transactional semantics to the user. Your complaints all boil down to it being a bad idea to have such a complex interface. But if your business case requires it then you're going to have to bite the bullet and eat the added complexity and you may as well use the best tool available to do it. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Rebranding PostgreSQL
On Wed, 16 Nov 2005 09:20:26 -0800 Tom Lane [EMAIL PROTECTED] wrote: The license may allow it, but you really need a fair amount of chutzpah to expect that people help you with it ... Hah...I certainly agree. Keep in mind though...we're not actually selling them the database. It's not my intention to rebrand the database and *sell* it. We simply want to use PostgreSQL to leverage our application's capabilities. The app currently runs on the customer's supplied databases (Sybase, Oracle, and Access (gulp) in a few sites). We're not proposing to charge them for the database, and believe me, they wouldn't buy in. They already have to part of the game covered. I'm a long time user, and fan, and a open source advocate in general. I think PostgreSQL is the way of the future. I'm also the shot caller primarily on our app's technology, and I'd like to standardize as much as possible on developing solely for PostgreSQL. The multi db support has provided an endless supply of headaches. We can provide PostgreSQL for free to the customer, and most have been very open to the idea. We'll provide what administration is needed. There are a few obstinate anti-open source customers though, that prevent my plan from moving forward. They've bought into whatever hype they've read and just simply say no. Now, that said, they're fairly non-technical and probably had never heard of PostgreSQL before we presented our plan. So, is it a little shady to want to slide PostgreSQL in under the radar? I'm simply trying to downplay what it is...it's my take that what they don't know won't hurt them. Sounds like rebranding would be a significantly difficult task. Perhaps I'll just remove all menu entries and leave it at that. Any thoughts or suggestions are appreciated. And please, don't take offense to the question ;) Thanks, John Concerned about your privacy? Instantly send FREE secure email, no account required http://www.hushmail.com/send?l=480 Get the best prices on SSL certificates from Hushmail https://www.hushssl.com?l=485 ---(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
[GENERAL] PostgreSQL compatible with Real Time Linux ?
Title: Message Hi, I want to install PostgreSQL on a board running a Real Time embedded variant of Linux: TimeSys Linux Is PostgreSQL compatible with this version of Linux? Has anyone installed it on a real time- embedded platform before? Thanks in advance. Swati This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail.
[GENERAL] PostgreSQL compatible with Real Time Linux ?
Title: Message Hi, I want to install PostgreSQL on a board running a Real Time embedded variant of Linux: TimeSys Linux Is PostgreSQL compatible with this version of Linux? Has anyone installed it on a real time- embedded platform before? Thanks in advance. Swati This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail.
Re: [GENERAL] Rebranding PostgreSQL
Education is the best way to go. How are you going to slide in postgres? Hey guys, we can get rid of all your diverse databases and replace them with this black box I found here. Sooner or later your anti-OSS friends are going to find out you tricked them, and what happens then? If your clients have serious concerns with open source, address them. If they don't, call them on it. If they're in charge (and clients often are) and they insist on paying money for inferior software, show them what it will really cost them. If they still want to pay - hey, it's not your money that's being wasted. On Wed, 16 Nov 2005, [EMAIL PROTECTED] wrote: So, is it a little shady to want to slide PostgreSQL in under the radar? I'm simply trying to downplay what it is...it's my take that what they don't know won't hurt them. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] locked backend
Csaba Nagy [EMAIL PROTECTED] writes: The situation (diagnosed via pg_stat_activity): one table was locked by an update, a VACUUM ANALYZE was running for the same table (triggered by autovacuum), and a handful of inserts were waiting on the same table. Updates do not block inserts, and neither does vacuum, so there's something you're not telling us. In particular an UPDATE wouldn't take an ExclusiveLock on the table, so that lock must have come from some other operation in the same transaction. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] locked backend
Csaba Nagy [EMAIL PROTECTED] writes: The situation (diagnosed via pg_stat_activity): one table was locked by an update, a VACUUM ANALYZE was running for the same table (triggered by autovacuum), and a handful of inserts were waiting on the same table. After some checking around (see below for details), I decided to kill the UPDATE (it was holding up the inserts which means lost data for us and danger of connection starvation), and did it with kill processid from the command line, but no success: the backend didn't respond. Then I killed the VACUUM which exited promptly, the UPDATE was still blocked. Then I remembered there is a pg_cancel_backend function (seen it recently on this list), and tried that too, without success - the update was still blocked, and blocking all inserts. Are there any foreign key relationships involved here? Because barring foreign key constraints none of these should have blocked any of the others. The only thing that would block a plain VACUUM (as opposed to VACUUM FULL) would be if you had a transaction pending that had a schema change like ALTER TABLE. Or something that had done an explicit LOCK TABLE. And the only thing that would block the INSERTs are the above or if there's a foreign key relationship to another table and that record in the other is locked, from another INSERT that refers to it or from an UPDATE. The only way your explanation that the UPDATE was holding up the inserts makes sense is if the records you were UPDATEing were referred to by the records you were inserting in a foreign key reference. Even so it shouldn't have held up the VACUUM at all. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Call for trace_sort output/details
I'm interested in any performance reports for long running sort runs in 8.1, as background information to improving sort performance for 8.2. You'll need to enable the trace_sort parameter. If you could provide all of the following, that would be most helpful 1. the log output relating to the trace_sort 2. details of the SQL query invoking the sort 3. details of the table(s) involved so I can determine i) the columns and datatypes of the sort keys. ii) the total data volume being sorted 4. Any analysis you have of the data distributions, i.e. first column has 1 million unique values, 2nd sort column has 11 values etc.. 5. your work_mem and maintenance work_mem settings 6. server hardware details First few posts can be on-list, after that privately please - until I shout stop! Please only send me your worst sort case, not every case... Any help much appreciated. Best Regards, Simon Riggs ---(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: [GENERAL] Bug with index-usage?
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes: I patched my 8.1 installation with the following changes: http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461 The error described above doesn't exist any more, but it's still buggy. Yup, you're right :-(. Looks like we haven't been doing adequate testing with complex OUTER JOIN clauses ... Fix committed. Thanks for the report! Thanks for the quick fix, everything looks good now! Sebastian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL compatible with Real Time Linux ?
Title: Message I haven't run it on an embedded system but I have run it at real-time linux priority. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Murdia, Swati -GilSent: Wednesday, November 16, 2005 1:28 PMTo: pgsql-general@postgresql.orgSubject: [GENERAL] PostgreSQL compatible with Real Time Linux ? Hi, I want to install PostgreSQL on a board running a Real Time embedded variant of Linux: TimeSys Linux Is PostgreSQL compatible with this version of Linux? Has anyone installed it on a real time- embedded platform before? Thanks in advance. Swati This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail.
Re: [GENERAL] Rebranding PostgreSQL
On Nov 16, 2005, at 1:09 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: There are a few obstinate anti-open source customers though, that prevent my plan from moving forward. They've bought into whatever hype they've read and just simply say no. Now, that said, they're fairly non-technical and probably had never heard of PostgreSQL before we presented our plan. how would postgres be exposed to them anyhow? wouldn't it just sit behind the scenes of your front-end? if they're poking around the process table, just change the name of the postmaster executable and the socket it creates to johnsdb or some such. the real trick would have been to sell it in a better way. don't mention open source or antyhing -- just say we have our own in-house DB we can provide at reduced cost to supporting your pre-installed Oracle. given them too much information was a mistake, IMHO. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL compatible with Real Time Linux ?
On 11/16/05, Murdia, Swati -Gil [EMAIL PROTECTED] wrote: Hi, I want to install PostgreSQL on a board running a Real Time embedded variant of Linux: TimeSys Linux Is PostgreSQL compatible with this version of Linux? Has anyone installed it on a real time- embedded platform before? Thanks in advance. Swati test it and if you find it's pass regress tests, you can inform that -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PREPARE TRANSACTION and webapps
Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What this would actually be useful for is a fair question though --- what's it do that you don't have now? I think what they want to do is make the database concept of transactions match up 1-1 with their application's concept of transactions. Which may span multiple stateless http requests. [ itch... ] This seems to me to fly right in the face of the oft-repeated advice that you don't hold a transaction open while the user thinks about it, goes off to lunch, vacations in the Bahamas, etc. Sure, I said that was the answer people get when they ask about this. And it's clearly better approach if it's available. But what if the locking or MVCC semantcis *are* what you need? This problem is well understood and solutions are readily available. If you have a small amount of data, you cache it in the web server's session, then once the user confirms the transaction, you write it all at once to the DB. If you have a significant amount of information, you create a set of shopping cart tables and populate those as the end user progresses through the transaction. Once the user confirms the transaction, you read it from the shopping cart tables and write it all at once into the final tables. Having worked with web-based, transaction-oriented applications for almost 10 years now, I don't see any justification for holding an actual database transaction open between HTTP requests. As Tom correctly points out, there is no guarantee whatsoever that the end user will ever complete such a transaction. If you really do need to allow one user to edit the information and still present the existing information to others but not let them update it concurrently, etc. Reimplementing full ACID semantics is hard and easy to get wrong. We already have a tool that provides them properly. -- Guy Rouillier ---(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
[GENERAL] absolute or relative updates
I want to write a trigger that logs any changes that are made to a row. I have the old an new row values, but I am more interested in knowing if the update was absolute UPDATE table SET col = 3 or relative UPDATE table SET col = col + 2. Is the a way (any way) to access this type of information ? In any language available (C, PLPGSQL, etc.) Thanks Razvan Radu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cursors or Offset, Limit?
On Tue, Nov 15, 2005 at 01:44:32PM -0500, Jerry LeVan wrote: What are some of the tradeoffs between using a cursor and using the limit/offset method of selecting rows to display? OFFSET actually has to scan all the preceding rows every time (plus to get it consistently, you need to do an ORDER BY), so it's fast at first but possibly painfully slow in the last rows (especially on a large table). A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Rebranding PostgreSQL
[EMAIL PROTECTED] writes: There are a few obstinate anti-open source customers though, that prevent my plan from moving forward. They've bought into whatever hype they've read and just simply say no. Now, that said, they're fairly non-technical and probably had never heard of PostgreSQL before we presented our plan. So, is it a little shady to want to slide PostgreSQL in under the radar? I'm simply trying to downplay what it is...it's my take that what they don't know won't hurt them. Well, I have seen SAP AG deploy stuff like Ghostscript and Apache (under their various varying license) as components of their applications without anyone saying boo. In SAPGUI, the front end, they had parts of Ghostscript in there, complete with copyright messages and everything. But since all of this stuff was stowed in a subdirectory that they didn't really call attention to, nobody generally notices. I would imagine that if you simply stow components where you choose to stow them, and say, this is part of what we always install for all our customers, and never bring OSS up as an issue, they probably won't notice they were going to have an issue with it. For these people, you don't say, Oh yes, this is open source; you're agreeing to the BSDL. Instead, the story is more like: We have acquired proper licensing rights for all of the subcomponents that we use from their respective producers and vendors. -- cbbrowne,@,cbbrowne.com http://cbbrowne.com/info/spreadsheets.html I knew you weren't really interested. -- Marvin the Paranoid Android ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PREPARE TRANSACTION and webapps
Guy Rouillier [EMAIL PROTECTED] writes: Having worked with web-based, transaction-oriented applications for almost 10 years now, I don't see any justification for holding an actual database transaction open between HTTP requests. As Tom correctly points out, there is no guarantee whatsoever that the end user will ever complete such a transaction. Indeed I've never needed them either. But then I've never worked on a banking system or an airline ticket reservations system, or anything that would need anything but the simplest of transactions. So I've always found a way to finesse the issue and avoid entirely the entire field of having to deal with expiring sessions and conflict resolution. But the fact that these problems exist don't militate for either database transactions or an application level reimplementation of transactions. In either case you'll have to deal with expiring and rolling back old transactions and with resolving conflicts. I take it as a given that if suspended transactions were ever to appear people would expect a system table that let them list suspended transactions and how when they were suspended. Otherwise they just wouldn't be very manageable. -- greg ---(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: [GENERAL] Rebranding PostgreSQL
On Wed, Nov 16, 2005 at 14:19:28 -0500, Vivek Khera [EMAIL PROTECTED] wrote: if they're poking around the process table, just change the name of the postmaster executable and the socket it creates to johnsdb or some such. I think you need to be careful with that. The last time I checked, postmaster checked what name it was running under and behaved differently depending on what it was called. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Rebranding PostgreSQL
On Nov 16, 2005, at 4:17 PM, Bruno Wolff III wrote: On Wed, Nov 16, 2005 at 14:19:28 -0500, Vivek Khera [EMAIL PROTECTED] wrote: if they're poking around the process table, just change the name of the postmaster executable and the socket it creates to johnsdb or some such. I think you need to be careful with that. The last time I checked, postmaster checked what name it was running under and behaved differently depending on what it was called. well obviously you would have to take that into account when renaming... :-) in my head I had thought of it but just didn't type it out ---(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: [GENERAL] Rebranding PostgreSQL
On Wed, Nov 16, 2005 at 10:09:51AM -0800, [EMAIL PROTECTED] wrote: So, is it a little shady to want to slide PostgreSQL in under the radar? I'm simply trying to downplay what it is...it's my take that what they don't know won't hurt them. I appreciate what you're trying to do. At the same time, why do you think your customers will be more willing to go for John's Database than some community product called PostgreSQL? (And yes, I suspect there _are_ such people.) A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Rebranding PostgreSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I appreciate what you're trying to do. At the same time, why do you think your customers will be more willing to go for John's Database than some community product called PostgreSQL? (And yes, I suspect there _are_ such people.) Maybe he is going to call it Orakle? :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200511161737 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDe7TKvJuQZxSWSsgRAsHeAJ0eaiY58hbfU8CCehyNxTo5uGIu8QCgmyxL VDTXoeXupwD79/zYM+Zfgm8= =yYtZ -END PGP SIGNATURE- ---(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
[GENERAL] Incomplete Startup Packet
Some time ago, I accidentally did a kill -9 on the postmaster (yes, I know, I know), when trying to kill -9 one of the child processes (er, yeah, probably bad too). This turned out to be pretty bad for us. It put the database in a bad state. I had to run some kind of hacky command (I don't recall which one) to even get postgres to start up again. Since then, the log file is littered with: LOG: incomplete startup packet I am ok with the fact that the abrupt killing of the postmaster may have corrupted some data. It is not a mission critical data we're talking about. But I'm left with some questions - Is my database hosed? Does this necessitate a full reinstall of postgres? While not mission critical data, there is a lot of it, and many dbs in the cluster which would mean hours of data loading. (The database seems to be functioning just fine, but I seem to recall reading that a reinstall is recommended, though I forget why) - Mott As a side question, probably unrelated -- i understand that kill -9 postmaster is bad, but how bout killing a child process (a client)? I've noticed that if you kill a child process, it seems to kill all child processes and reboot (like a SIGUP?) [I was doing this in order to kill a hanging transaction.] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Rebranding PostgreSQL
I appreciate what you're trying to do. At the same time, why do you think your customers will be more willing to go for John's Database than some community product called PostgreSQL? (And yes, I suspect there _are_ such people.) That's easy. The same reason people used to buy Mammoth PostgreSQL (not the replicator version). There is an assumption that there is a throat to choke, or sue as the case may be. What most people fail to realize is that almost ALL software has a maximum of a 90 day warranty with a limitation of liability to the cost of the software. Sincerely, Joshua D. Drake A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Incomplete Startup Packet
Mott Leroy [EMAIL PROTECTED] writes: Some time ago, I accidentally did a kill -9 on the postmaster (yes, I know, I know), when trying to kill -9 one of the child processes (er, yeah, probably bad too). This turned out to be pretty bad for us. It put the database in a bad state. I had to run some kind of hacky command (I don't recall which one) to even get postgres to start up again. Since then, the log file is littered with: LOG: incomplete startup packet It's impossible to believe that that's even slightly related. Incomplete startup packet means that you've got broken client-side software, or perhaps that something is portscanning you. You sure you weren't seeing those beforehand? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Incomplete Startup Packet
Tom Lane wrote: It's impossible to believe that that's even slightly related. Incomplete startup packet means that you've got broken client-side software, or perhaps that something is portscanning you. You sure you weren't seeing those beforehand? I'm not certain I wasn't seeing those beforehand, no. I suppose I jumped to conclusions a bit. Should I be concerned about these incomplete startup packet errors? It's unlikely that its a portscan, since the db server is invisible to the outside world. How can I go about tracking down the source of these? My db clients are JDBC connections from web applications. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PREPARE TRANSACTION and webapps
[EMAIL PROTECTED] wrote: I take it as a given that if suspended transactions were ever to appear people would expect a system table that let them list suspended transactions and how when they were suspended. Otherwise they just wouldn't be very manageable. Regarding web applications, this turns out not to be too hard of a problem to solve. Using the two options I identified: if you are able to keep all your transaction data in the web server session, then this data just disappears when the session goes away. No clean up necessary. If you maintain state in a set of database tables, most implementations assume that if the transaction ages past some threshold value (e.g., one hour) without completion, the submitter decided not to complete. So you just run a cron job once an hour that sweeps through these tables and deletes anything older than the threshold. If you want to allow the submitter to return at a later date and resume, then you are right, this gets a little trickier, but not much. You still do the threshold checking in case the submitter never returns, but you up the threshold value to two weeks (or whatever.) And if the submitter does return, you force him/her to resume or delete the existing transaction before they can start a new one. -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wrong rows selected with view
Bill Moseley [EMAIL PROTECTED] writes: [ strange behavior ] Oh, duh, it's not a PG bug: the problem is that the view is underspecified. You have SELECT DISTINCT ON (class.id) ... a bunch of stuff ... FROM ... a bunch of tables ... ORDER BY class.id; The difficulty with this is that DISTINCT ON will take the first row in each group with the same class.id. And since you're only sorting by class.id, the first row is ill-defined. I'm not sure why qsort's behavior seems to depend on the width of the rows, but there's no doubt that it's sorting different rows to the front of each group depending on which view you use. To get stable results from this view, what you need to do is add enough ORDER BY conditions to make sure you are getting a consistent first row in each group. Adding the primary keys of each of the tables would be enough, though it might be overkill. It could also be that you don't want to be using DISTINCT ON at all; have you thought through exactly what this view ought to produce for each class.id? regards, tom lane ---(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: [GENERAL] Incomplete Startup Packet
Mott Leroy [EMAIL PROTECTED] writes: How can I go about tracking down the source of these? I think if you turn on log_connections, you'll at least get log entries showing what machine(s) the bad connection attempts are coming from. Not sure if that'll be enough for you. My db clients are JDBC connections from web applications. You might try asking about it on the pgsql-jdbc list; perhaps there's a known issue of this kind. regards, tom lane ---(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
[GENERAL] Does PG Support Unicode on Windows?
Is there any truth to what this guy is saying? On the other hand, Postgresql claims that Windows does not support Unicode and you can't have Unicode fields on postgresql on Windows. This is a big mistake. See: http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html What do you mean a big mistake? By Microsoft? Or PostgreSQL? The Big mistake is in the Postgresql FAQ in saying that Windows doesn't support Unicode. XP supports Unicode very well indeed. The FAQ I referenced prattles on about Slovenian code pages, of all things. Windows Code pages have been superseded by Unicode. If you go to http://msdn.microsoft.com and search for Unicode you get an eyeful. Having defended the undefendable, however, I'd like to know if postgresql really still doesn't support Unicode on windows, as I plan to develop on WindowsXP and deploy on Linux. Warren Seltzer Thread here: http://www.ruby-forum.com/topic/3690#new csn __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does PG Support Unicode on Windows?
On Nov 17, 2005, at 10:12 , CSN wrote: Is there any truth to what this guy is saying? [cut hard-to-read quotes regarding support for UTF8 in PostgreSQL on Windows] According to the release notes for 8.1: http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1 * Allow the UTF8 encoding to work on Windows (Magnus) This is done by mapping UTF8 to the Windows-native UTF16 implementation. Michael Glaesemann grzm myrealbox 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: [GENERAL] Wrong rows selected with view
On Wed, Nov 16, 2005 at 07:48:06PM -0500, Tom Lane wrote: Bill Moseley [EMAIL PROTECTED] writes: [ strange behavior ] Oh, duh, it's not a PG bug: the problem is that the view is underspecified. You have SELECT DISTINCT ON (class.id) ... a bunch of stuff ... FROM ... a bunch of tables ... ORDER BY class.id; The difficulty with this is that DISTINCT ON will take the first row in each group with the same class.id. And since you're only sorting by class.id, the first row is ill-defined. Sorry, but I fear I'm missing something. That ORDER BY is added by PG -- it's not part of my view when I define it. I assume PG adds that so it can do the DISTINCT ON. Still, I don't have any duplicate class.id rows in this select that I can see. class.id 1243 and 1244 are not the same, yet PG is (sometimes) throwing out one of them. Are you saying that somehow PG thinks they are the same class.id and is thus removing one? I'm asking for a list of all classes taught by instructor 84. ws2= select * from instructors where person = 84 order by class; person | class +--- 84 | 727 84 | 739 84 | 804 84 | 813 84 | 867 84 | 1243 84 | 1244 (7 rows) The reason I'm using DISTINCT ON is because the class_list view is suppose to just return a list of unique classes, and a class might have more than one instructor which would result in extra rows -- as shown here: ws2= select * from instructors where class in (select class from instructors where person = 84); person | class +--- 84 | 727 84 | 739 84 | 804 84 | 813 84 | 867 84 | 1243 26 | 1243 84 | 1244 26 | 1244 (9 rows) So when I don't want duplicates: ws2= select distinct on (class) * from instructors where class in (select class from instructors where person = 84); person | class +--- 84 | 727 84 | 739 84 | 804 84 | 813 84 | 867 84 | 1243 84 | 1244 (7 rows) I don't care in this case about the DISTINCT ON throwing out the duplicates -- I just care about distinct classes, not that all the instructors are included in this select. And even if I throw in all my other joins I get the same thing: ws2=SELECT DISTINCT ON (class.id) ws2- class.id AS id ws2- ws2- FROM class, location, region, person, instructors ws2- ws2- WHERE class.location = location.id -- join with location ws2-AND class.id = instructors.class -- join the instructors ws2-AND instructors.person = person.id -- join the person(s) ws2-AND location.region = region.id -- join the location to a region ws2-AND person.id = 84; id -- 727 739 804 813 867 1243 1244 (7 rows) I'm not sure why qsort's behavior seems to depend on the width of the rows, but there's no doubt that it's sorting different rows to the front of each group depending on which view you use. I just don't see what groups there are, though in this case. It could also be that you don't want to be using DISTINCT ON at all; have you thought through exactly what this view ought to produce for each class.id? Yes, I think so. A list of columns related to it, with the exception of when there's duplicate instructors I want one of those duplicates thrown out (and I don't care which one). When I do a query that generates duplicate class.id's such as when a class has more than one instructor: ws2= select class.id AS class_id, ws2- person.id AS person_id ws2- ws2- FROM class, location, region, person, instructors ws2- ws2- WHERE class.location = location.id -- join with location ws2-AND class.id = instructors.class -- join the instructors ws2-AND instructors.person = person.id -- join the person(s) ws2-AND location.region = region.id -- join the location to a region ws2-AND class_time now(); class_id | person_id --+--- 561 |95 614 |95 747 | 111 762 | 111 772 | 111 883 |13 924 |26 935 |26 945 |26 1243 |84 1243 |26 1244 |84 1244 |26 (13 rows) You can see some classes are listed twice, so using distinct on gets just my list of unique classes: ws2= SELECT DISTINCT ON (class.id) ws2- class.id AS class_id, ws2- person.id AS person_id ws2- ws2- FROM class, location, region, person, instructors ws2- ws2- WHERE class.location = location.id -- join with location ws2-AND class.id = instructors.class -- join the
Re: [GENERAL] Trouble with recursive trigger
On Wed, Nov 16, 2005 at 07:43:16AM +0100, Martijn van Oosterhout wrote: Just a thought, maybe it has something to do with the UPDATE updating a row where the trigger is running. So, think of the execution like this: # DELETE FROM post WHERE id = 3002; trigger DELETE FROM post WHERE parent = 3002; *recurses* trigger#2 DELETE FROM post WHERE parent = 3003; *recurses* ... trigger#5 DELETE FROM post where parent = 3005; *recurses* trigger#6 DELETE FROM post where parent = 3006;-- Does nothing trigger#6 UPDATE post SET replies = replies - 1 WHERE id = 3005; See this last line, it's updating the row while the delete trigger is running. I don't know the semantics but what's probably happening is that the original row the trigger ran on *was* deleted, but the UPDATE created a new one which hasn't been deleted. Yep I suspect it's something like this. I don't see why, as to me if the trigger hasn't completed yet then the row should still be there. And if that's not the case (the row is in some sort of half-deleted limbo state) then I'd expect some sort of sensible error, not a quiet failure of the subsequent completion of the trigger to actually delete the row. No ideas how to fix it though. Search the docs for a reference... Also, what if it's an AFTER DELETE trigger? The referential integrity means that if I delete a row in 'the middle' I need to delete the children myself first. If I let a cascade deal with that then I don't get the opportunity to update rows further up the tree to reflect the fact there are now less replies. I can't see any particular flaw in my method so I'd really like to get to the heart of why this doesn't work. - Justin -- Justin Hawkins | [EMAIL PROTECTED] | http://hawkins.id.au ---(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: [GENERAL] Wrong rows selected with view
Bill Moseley [EMAIL PROTECTED] writes: That ORDER BY is added by PG -- it's not part of my view when I define it. I assume PG adds that so it can do the DISTINCT ON. Well, then you're even further from following the protocol for DISTINCT ON. You *must* provide an ORDER BY to get reliable results from it. Still, I don't have any duplicate class.id rows in this select that I can see. class.id 1243 and 1244 are not the same, yet PG is (sometimes) throwing out one of them. Are you saying that somehow PG thinks they are the same class.id and is thus removing one? No, I'm saying that the underlying data (the join result before applying DISTINCT ON) looks like this: bill=# select class.id, person.id AS person_id bill-#FROM class, location, region, person, instructors bill-# WHERE class.location = location.id AND class.id = instructors.class bill-# AND instructors.person = person.id AND location.region = region.id bill-# ORDER BY class.id; id | person_id --+--- 1 |49 2 |27 3 |19 4 |82 5 |12 ... 1238 |61 1238 |60 1239 |40 1240 |67 1241 |11 1243 |26 1243 |84 1244 |26 1244 |84 (1311 rows) The DISTINCT ON will take just one of the two rows with id = 1243, and just one of the rows with id = 1244, and *it is effectively random which one gets picked*. So when you then select rows with person_id = 84, you may or may not see these rows in the end result. The reason I'm using DISTINCT ON is because the class_list view is suppose to just return a list of unique classes, and a class might have more than one instructor which would result in extra rows -- as shown here: Exactly. So your view is going to return the class id along with a randomly selected one of the instructor ids. It seems to me that filtering this result on instructor id is perhaps a bit ill-advised, even if you fix the view so that the chosen instructor id isn't so random (eg, you could fix it to display the lowest-numbered instructor id for the particular class). Even then, are you searching for the instructor id that the view happens to show for that class, or some other one? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] absolute or relative updates
am 16.11.2005, um 18:52:25 +0200 mailte [EMAIL PROTECTED] folgendes: I want to write a trigger that logs any changes that are made to a row. I You ivent the wheel the second time ;-) http://pgfoundry.org/projects/tablelog/ have the old an new row values, but I am more interested in knowing if the update was absolute UPDATE table SET col = 3 or relative UPDATE table SET col = col + 2. If you want restore a table and you have only relative log-entrys, the you must restore all steps from start to this point. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
