Re: [HACKERS] MERGE Specification
Simon Riggs wrote: On Fri, 2008-04-25 at 10:03 +0300, Hannu Krosing wrote: On Tue, 2008-04-22 at 00:24 +0100, Simon Riggs wrote: On Mon, 2008-04-21 at 16:38 -0400, A.M. wrote: MERGE will not invoke Rules. Does this imply that MERGE cannot be used on views or that the resulting INSERTs or UPDATEs do not work on views? Yes, that's right. Just like COPY. That seems fine to me because you're likely to be doing a MERGE immediately after a COPY anyway, so the restriction just continues. May be the bulk data merging variant of MERGE to be used after initial COPY should be a variant of COPY with special keyword(s) instead of MERGE ? That does sound like a good way of differentiating between the OLTP and bulk loading cases. I'll bear that in mind as we develop. To me, a simple user, it'd be important that MERGE implementation does not place any unpredictable restrictions. For example in Oracle you can break any MERGE statement by placing a full text index on the table. So I'd really expect a MERGE in PostgreSQL to be fine with views, rules, tsearch, etc. Just my 2 cent. -- Best regards, Hannes Dorbath -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 / 8.2.6 restore comparison
Tom Lane wrote: Greg Smith [EMAIL PROTECTED] writes: On Fri, 22 Feb 2008, Tom Lane wrote: Counts are useless here, we need to see the sequence of write locations to find out if there's a lot of nonconsecutive writes happening. How were you planning to analyze the strace output to quantify that? I didn't really have any preconceived ideas about that. I just want to see some raw data to see if something shows up. Isn't blktrace the tool to get that kind of information? Anyway, as the following threads point out the problems seems to be somewhere else.. -- Best regards, Hannes Dorbath ---(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: [HACKERS] Index trouble with 8.3b4
Tom Lane wrote: I've committed a patch to do that. Please test CVS HEAD and see if you still see problems. I'm happy to hear you found something and I will try CVS HEAD in a minute. In the meantime let me report that the cluster issue happens with GIST as well. I have load 5 million rows in that table and did: test=# CREATE INDEX CONCURRENTLY ts_test_tsv_gist ON public.test USING gist (tsv); CREATE INDEX test=# CLUSTER test USING ts_test_tsv_gist; ERROR: could not create unique index test_pkey DETAIL: Table contains duplicated values. test=# But as far as I understood this is already covered by your thesis. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Index trouble with 8.3b4
Tom Lane wrote: I wrote: I think it's okay for CREATE INDEX CONCURRENTLY to use bulk-read access strategy (that is, seqscan using a limited number of buffers), but it has to be able to force the scan to start at page zero. I've committed a patch to do that. Please test CVS HEAD and see if you still see problems. With some limited testing it seems both cases are indeed fixed. I was unable to reproduce either with current CVS HEAD. Though I'll run some further tests tomorrow to back that up. Thanks for your time and prompt responses. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index trouble with 8.3b4
Gregory Stark wrote: Gregory Stark [EMAIL PROTECTED] writes: On the other hand we can't just ignore all vacuums because someone could issue a manual vacuum inside a transaction (I think?). Doh, ignore this. sigh. I started from scratch to put up a test case. I cannot trigger ERROR: item pointer (0,1) already exists again as the deadlock issue reported by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry with that? Only good news is that I think I found the CLUSTER issue: It was no GIST index I created, I accidentally created a BTREE index: http://theendofthetunnel.de/cluster.txt If it does help anything, the data and setup: 8.3-b4 build from source ./configure --prefix=/usr/local --enable-thread-safety --with-perl --with-openssl --with-libxml --with-libxslt initdb line: initdb -D /data/pgsql --locale='de_DE.utf8' --lc-collate='C' Only listen_address and pg_hba.conf was touched. Please get the -Fc dump (37MB) from: http://theendofthetunnel.de/dump.bin http://theendofthetunnel.de/glob.sql -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index trouble with 8.3b4
Hannes Dorbath wrote: Guillaume Smet wrote: On Jan 13, 2008 7:50 PM, Hannes Dorbath [EMAIL PROTECTED] wrote: I started from scratch to put up a test case. I cannot trigger ERROR: item pointer (0,1) already exists again as the deadlock issue reported by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry with that? No, it's not fixed in RC1. You have to compile CVS HEAD to have it fixed. OK, the deadlock is gone. I can only provoke it when issuing the create index statement from 2 terminals at the same time. But I think this is intended. I keep trying to catch the gin error though. Well, or maybe not really intended that way. Both terminals error out with: ERROR: relation ts_test_tsv already exists But the index was created. ERROR: relation ts_test_tsv already exists test=# drop INDEX ts_test_tsv ; DROP INDEX -- Best regards, Hannes Dorbath ---(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: [HACKERS] Index trouble with 8.3b4
Guillaume Smet wrote: On Jan 13, 2008 7:50 PM, Hannes Dorbath [EMAIL PROTECTED] wrote: I started from scratch to put up a test case. I cannot trigger ERROR: item pointer (0,1) already exists again as the deadlock issue reported by Gregory Stark hit me every time. Is this fixed in RC1? Can I retry with that? No, it's not fixed in RC1. You have to compile CVS HEAD to have it fixed. OK, the deadlock is gone. I can only provoke it when issuing the create index statement from 2 terminals at the same time. But I think this is intended. I keep trying to catch the gin error though. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Index trouble with 8.3b4
Hannes Dorbath wrote: ERROR: relation ts_test_tsv already exists test=# drop INDEX ts_test_tsv ; DROP INDEX This is a general thing I'd like to ask. If the creation of an index fails, why is it nevertheless there? No matter if deadlock or my GIN error, why isn't the whole operation rolled back? And what state is it it leaves me on? Do I end up with a corrupt index on my table? -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index trouble with 8.3b4
Tom Lane wrote: Hannes Dorbath [EMAIL PROTECTED] writes: This is a general thing I'd like to ask. If the creation of an index fails, why is it nevertheless there? It's a rather ugly consequence of the fact that CREATE INDEX CONCURRENTLY requires more than one transaction. If the later ones fail, the invalid index is still there. It'd be nice to clean that up sometime, but don't hold your breath. OK, I have my GIN failure back with CSV-HEAD: test=# UPDATE test SET tsv = to_tsvector(text); UPDATE 753100 test=# CREATE INDEX CONCURRENTLY ts_test_tsv ON public.test USING gin (tsv); ERROR: item pointer (8,23) already exists test=# drop INDEX ts_test_tsv ; DROP INDEX test=# CREATE INDEX CONCURRENTLY ts_test_tsv ON public.test USING gin (tsv); CREATE INDEX test=# I have a hard time to pin it down. Currently all I can say is: It happens the first time after I bulk load data into that table. I cannot catch it with pg_dump -- after a restore it works. I can reproduce it here more or less reliable. Maybe I should just bzip $PGDATA and send it. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Index trouble with 8.3b4
Hannes Dorbath wrote: Currently all I can say is: It happens the first time after I bulk load data into that table. I have the bad feeling that I need to correct this into It happens when autovacuum is active on the table. Is it by any chance possible that CREATE INDEX CONCURRENTLY might read dirt while autovacuum is busy with the table? -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
