[SQL] FATAL 2: PageIndexTupleDelete
salt=# vacuum full; FATAL 2: PageIndexTupleDelete: corrupted page pointers: lower = 12289, upper = 1, special = 60672 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. Why do I have this error message and how can I fix it?
[SQL] strange "not deferrable" behaviour
Hi I've got some tables defined as: create table xx ( some_column references master_table It means this column is defined by default: NOT DEFERRABLE, INITIALLY IMMEDIATE I tried replacing rows in this table by new ones as: set autocommit=off; begin; set constraints all deferred; delete from insert ... insert... commit; I get "integrity violation"... just after "delete" If I well understood manual, it works like it should. I'm not sure, because if I run this query in pgAdmin2, it works fine (why?) and replaces rows as needed. When I drop this foreign key constraint (not too easy without a constraint name) and recreate it as "DEFERRABLE", this query works fine also in psql. My question is: Why my query works fine when using pgAdmin, and it fails when using psql? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Addition and subtraction on BIT type
Thank you for your reply. select int4(b'1001')::bit(32); gives the same result as what you gave. select int4(b'1001')::bit(4); gives the upper four bits, which are all zeroes. How would I get the lower four bits? I building bitmaps using plpgsql, and therefore, I will be doing a lot bit manipulation. Thanks, Yasir On Sun, 16 Nov 2003, Stephan Szabo wrote: > Date: Sun, 16 Nov 2003 21:40:45 -0800 (PST) > From: Stephan Szabo <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Addition and subtraction on BIT type > > On Sun, 16 Nov 2003, Yasir Malik wrote: > > > I think I am almost at a solution to my last question. I can do > > select int4(a) from test; > > to convert to an integer. So now addition and > > subtraction can be done between bit types. But how do I convert back to > > BIT type? If I do > > select bit(int4(b'1001')); > > > > I get the following message: > > ERROR: parser: parse error at or near "int4" at character 12 > > > > Can anyone tell me why the bit function is not working? It's under the > > pg_catalog schema. > > It's also the name of a type that takes a precision in parentheses, so > you'd have to say "bit"(...) with the quotes. As a note, I think > that's going to effectively return you a bit(32), so > > sszabo=# select "bit"(int4(b'1001')); >bit > -- > 1001 > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] strange "not deferrable" behaviour
On Mon, 17 Nov 2003, Tomasz Myrta wrote: > I've got some tables defined as: > create table xx ( >some_column references master_table > > It means this column is defined by default: > NOT DEFERRABLE, INITIALLY IMMEDIATE > > I tried replacing rows in this table by new ones as: > set autocommit=off; > begin; > set constraints all deferred; > delete from > insert ... > insert... > commit; > I get "integrity violation"... just after "delete" > > If I well understood manual, it works like it should. I'm not sure, > because if I run this query in pgAdmin2, it works fine (why?) and > replaces rows as needed. > > When I drop this foreign key constraint (not too easy without a > constraint name) and recreate it as "DEFERRABLE", this query works fine > also in psql. > > My question is: Why my query works fine when using pgAdmin, and it fails > when using psql? Hmm, I'd suggest turning on query logging to make sure pgAdmin is doing what you expect. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Addition and subtraction on BIT type
On Mon, 17 Nov 2003, Yasir Malik wrote: > Thank you for your reply. > select int4(b'1001')::bit(32); gives the same result as what you gave. > select int4(b'1001')::bit(4); gives the upper four bits, which are all > zeroes. How would I get the lower four bits? I building bitmaps using > plpgsql, and therefore, I will be doing a lot bit manipulation. I think you need to use substring, probably something like: select substring(9::bit(32) from 29 for 4); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] strange "not deferrable" behaviour
Dnia 2003-11-17 16:16, Użytkownik Stephan Szabo napisał: Hmm, I'd suggest turning on query logging to make sure pgAdmin is doing what you expect. Well, I've already done this. I didn't check query log, but a result by selecting rows from modified table. Anyway I opened a pgAdmin log and didn't find anything special. I also found something interesting in pgAdmin work. When constraint is defined as "NOT DEFERRABLE", I get "OK" only when I execute whole query at once. PgAdmin probably executes this query using only one statement - it separates each sql command using semicolon delimiter. If I try to split my query and execute each command separately - I get the same "integrity violation" error as in psql. I tried also change constraint into "DEFERRABLE". Behaviours of psql and PgAdmin are the same - query executes fine, doesn't matter whether it is executed as single or several statements. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Multicolum index and primary key
I wonder if is useless to set some indexes for columns contained in a multifield primary key. Suppose by example that one have a table1 with a primary key over three field (a, b, c): ALTER TABLE public.table1 ADD CONSTRAINT table1_pkey PRIMARY KEY(a, b, c); are the indexes over (a) and (a, b) redundant (and so useless)? I.e. with other words the question is, if I have a primary key set for the a, b, c fields the queries SELECT 1 from table1 where a='a value' and SELECT 1 from table1 where a='a value' AND b='another value' use the implicit index created by the primary key or not? Thank you in advance for any advice. ciao, Michele p.s. I know, I'll have to begin to use the explain command ... I promise I'll do it ;-) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Multicolum index and primary key
Michele Bendazzoli writes: > ALTER TABLE public.table1 > ADD CONSTRAINT table1_pkey PRIMARY KEY(a, b, c); > > are the indexes over (a) and (a, b) redundant (and so useless)? Exactly. > p.s. I know, I'll have to begin to use the explain command ... > I promise I'll do it ;-) You could also have read the documentation about multicolumn indexes, because it contains exactly this example. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Multicolum index and primary key
Dnia 2003-11-17 18:00, Użytkownik Michele Bendazzoli napisał: p.s. I know, I'll have to begin to use the explain command ... I promise I'll do it ;-) Use the explain analyze command and then answer yourself ;-) Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Multicolum index and primary key
> Suppose by example that one have a table1 with a primary key over three > field (a, b, c): .. > are the indexes over (a) and (a, b) redundant (and so useless)? Yes, they are redundant not not necessarily useless. In short, an index with 3 keys will be larger than an index with 1 key, as such PostgreSQL may choose to use the single key index to reduce the number of pages it needs to pull off the disk. That said, if the 3 key index is hit regularly, it is likely to be in memory where the rarely hit single key index is not. This would make going through the 3 key data faster (although there is more of it) than retrieving the single key data from disk, then processing. To top it all off, managing 3 indexes takes significantly longer during INSERT and UPDATE than manging a single larger index does. So... Are they useless? The primary key is required, so it's index is required. Do a majority of the queries against that table only supply one or two pieces of information? If so, you may benefit, as these indexes will tend to be in memory. Is access on the table mostly read? Is the write penalty worth the increased speed of write? Is the additional storage space worth it? Indexes on thousands or million of tuples are not free. 3 indexes will probably consume as much diskspace as the original table did thus doubling your storage requirements. Finally, if everything is useful, I suggest you re-order some of the indexes. a, ab, abc all require a to be a part of the query. There is 0 benefit if b or c are supplied without a. If you have determined 3 indexes will be useful, you might try a, ba, cba. This way if b or c are supplied without a, they will receive some benefit of the index with negligible impact to the queries that do use a. NOTE: I have made an assumption that the distribution of a, b and c are equivalent. You will want the more selective field first in your index to reduce the number of disk accesses -- so couple that with the odds that b or c will be supplied without a. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Multicolum index and primary key
On Mon, 2003-11-17 at 17:15, Tomasz Myrta wrote: > Dnia 2003-11-17 18:00, Użytkownik Michele Bendazzoli napisał: > > p.s. I know, I'll have to begin to use the explain command ... > > I promise I'll do it ;-) > > Use the explain analyze command and then answer yourself ;-) I'm not in still in production and I have too few values ... Thank you anyway ciao, Michele ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Multicolum index and primary key
On Mon, 2003-11-17 at 17:14, Peter Eisentraut wrote: > Michele Bendazzoli writes: > > > ALTER TABLE public.table1 > > ADD CONSTRAINT table1_pkey PRIMARY KEY(a, b, c); > > > > are the indexes over (a) and (a, b) redundant (and so useless)? > > Exactly. > > p.s. I know, I'll have to begin to use the explain command ... > > I promise I'll do it ;-) > > You could also have read the documentation about multicolumn indexes, > because it contains exactly this example. Thank you. I have read the chapter but I wonder if the implicit index created by the primary key behaves as a normal indexes at all. ciao, Michele ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] COPY
Hi all I'm trying to optimise some sql inserts I have by using the COPY command instead. I'm connecting from Java using 7.4 beta 3. It works at the command line, but I can't get it to work from Java. Here's the pg log output. LOG: query: COPY bingo_cards (account_id, game_id, win_ball, card, current_call, state) FROM stdin with delimiter '|'; 34|50558|75|3,7,8,4,12,23,18,24,22,21,38,45,42,39,43,55,54,53,51,46,70,66,71,75,65|0|0 34|50558|68|4,8,2,13,11,26,23,30,19,18,39,35,31,36,43,55,60,48,59,58,72,68,65,71,66|0|0 34|50558|72|15,11,2,4,5,26,18,20,25,27,43,36,45,38,31,54,57,58,55,50,66,73,71,72,69|0|0 \. ERROR: syntax error at or near "34" at character 108 Thanks for any help Ritchie ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Quota query with decent performance?
On Tue, 11 Nov 2003 18:49:31 -0500, Chester Kustarz wrote: [... discussion of top-n query (where n=3) ...] > select * > from person > where age <= > (select age from person order by age limit 1 offset 2); It fails when the cardinality of person is less than 3 (returns empty set). My solution is this, which is not as beautiful any more: SELECT * FROM person WHERE age <= COALESCE ( ( SELECT age FROM person ORDER BY age ASC LIMIT 1 OFFSET 2 -- 2=n-1 ),( SELECT age FROM person ORDER BY age DESC -- note: opposite of ASC LIMIT 1 ) ); -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] FATAL 2: PageIndexTupleDelete
cristi wrote: salt=# vacuum full; FATAL 2: PageIndexTupleDelete: corrupted page pointers: lower = 12289, upper = 1, special = 60672 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. Why do I have this error message and how can I fix it? Wich version are you running, and what about give us informations on your platform ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Multicolum index and primary key
On Mon, 2003-11-17 at 17:23, Rod Taylor wrote: > > Suppose by example that one have a table1 with a primary key over three > > field (a, b, c): > .. > > are the indexes over (a) and (a, b) redundant (and so useless)? > > Yes, they are redundant not not necessarily useless. > > In short, an index with 3 keys will be larger than an index with 1 key, > as such PostgreSQL may choose to use the single key index to reduce the > number of pages it needs to pull off the disk. > > That said, if the 3 key index is hit regularly, it is likely to be in > memory where the rarely hit single key index is not. This would make > going through the 3 key data faster (although there is more of it) than > retrieving the single key data from disk, then processing. > > To top it all off, managing 3 indexes takes significantly longer during > INSERT and UPDATE than manging a single larger index does. > So... Are they useless? > > The primary key is required, so it's index is required. In fact ... > Do a majority of the queries against that table only supply one or two > pieces of information? If so, you may benefit, as these indexes will > tend to be in memory. > Is access on the table mostly read? Is the write penalty worth the > increased speed of write? > Is the additional storage space worth it? Indexes on thousands or > million of tuples are not free. 3 indexes will probably consume as much > diskspace as the original table did thus doubling your storage > requirements. > > > Finally, if everything is useful, I suggest you re-order some of the > indexes. a, ab, abc all require a to be a part of the query. There is 0 > benefit if b or c are supplied without a. a is always present in the queries ... and other that (a, ab, abc) i have only to query (ac): so I think I have to index separately only (ac). > If you have determined 3 indexes will be useful, you might try a, ba, > cba. This way if b or c are supplied without a, they will receive some > benefit of the index with negligible impact to the queries that do use > a. Uhm, good point ... I'll have to think carefully. > NOTE: I have made an assumption that the distribution of a, b and c are > equivalent. You will want the more selective field first in your index > to reduce the number of disk accesses -- so couple that with the odds > that b or c will be supplied without a. For now a is not selective at all because I have sell the service at only one client (a may represent a function of the client). Obviously I hope that in the future the number of distinct values on field 'a' grows exponentially :-)) Thank you very much for the in depth advice. ciao, Michele ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] multiple function declarations
Hi , I have a plpgsql function (MainFunc) that intends to reuse a a particular block of code. So, I intend to make that particulat block of code into another function. (say, HelperFunc) My question is 1. Can I declare a function (HelperFunc) inside the main function (MainFunc) 2. If yes then how. Thanks in advance -akas
Re: [SQL] multiple function declarations
Majhi, Akas writes: > 1. Can I declare a function (HelperFunc) inside the main function (MainFunc) > 2. If yes then how. You could do it if you create the CREATE FUNCTION command in a string variable and then use EXECUTE to run it, but the normal way would be to declare it outside. There is usually no reason not to do that. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
