Re: [GENERAL] Probably been asked a hundred times before.
--On Tuesday, June 24, 2008 10:30:14 AM -0400 David Siebert [EMAIL PROTECTED] wrote: Which disto is best for running a Postgres server? I run most of my postgres servers on Debian. I really love it, because once a new major version comes out you can very easy install it parallel to your current version and test and once you are done, migrate the data and just switch the port. I also have some external servers with RedHat ES and postgres. But when I upgrade there I use the RPMs provided by postgres. Major version upgrades are a bit more tricky here, but they work. [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] replication
Hi Take a look also on Londiste from SkyTools. It is easy to set up and manage. In addition SkyTools contains other useful scripts and tools. We use Londiste to replicate data ovwe WAN where walshipping would consume too much bandwidth. Asko On Tue, Jun 24, 2008 at 9:41 AM, Adrian Moisey [EMAIL PROTECTED] wrote: Hi We have a 100GB database (16GB dumped) running on 8.2. Since the bandwidth in South Africa isn't that freely available it is difficult for us to get a new copy of out DB in our office (our fastest link in the office is 4Mbps). What can you recommend for me to get an up to date copy of my DB in the office with as little transfer as possible. A few ideas I've been toying with are rsyncing a dump of the db, or replication. Any ideas? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote: Ow Mun Heng wrote: explain select * from d_trr where revision like '^B2.%.SX' --where ast_revision = 'B2.M.SX' Seq Scan on d_trr (cost=0.00..2268460.98 rows=1 width=16) Filter: ((revision)::text ~~ '^B2.%.SX'::text) show lc_collate; en_US.UTF-8 Is it that this is handled by tsearch2? Or I need to do the locale to C for this to function? See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. It tells you how to create an index that like might use in non-C locales. Just more information. This columns is created with the varchar type. original index is created using CREATE INDEX idx_d_ast ON xmms.d_trh USING btree (revision varchar_pattern_ops); CREATE INDEX idx_d_ast2 ON xmms.d_trh USING btree (revision); after creating it, seems like it is still doing the seq_scan. So what gives? Can I get more clues here? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full Text Search - i18n
Re-reading the documentation, and I have the answer. If adding a tsvector column, then for per row selection, I should also add a second column of type regconfig to specify the language that rows contents are in. Cheers, Andy Andrew wrote: Apologies if this question has been previously covered, but I was not able to find something similar in any of the mailing list archives. With full text search, if you need to support a table where the content of individual tuples/rows may be in different languages with the language of the content known, and the content of any individual row does _not_ contain a mixture of languages, what is the best way to design for this? Would you create a different GIN/GiST index of the tsvector for each language? Or if using columns to store the tsvector, would you have a different column per language, or the one column with the associated trigger using the appropriate language when building the tsvector for that row? Are there better alternatives than what I have listed? Thanks, Andy
[GENERAL] Full Text Search - i18n
Apologies if this question has been previously covered, but I was not able to find something similar in any of the mailing list archives. With full text search, if you need to support a table where the content of individual tuples/rows may be in different languages with the language of the content known, and the content of any individual row does _not_ contain a mixture of languages, what is the best way to design for this? Would you create a different GIN/GiST index of the tsvector for each language? Or if using columns to store the tsvector, would you have a different column per language, or the one column with the associated trigger using the appropriate language when building the tsvector for that row? Are there better alternatives than what I have listed? Thanks, Andy
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
Ow Mun Heng wrote: On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote: Ow Mun Heng wrote: explain select * from d_trr where revision like '^B2.%.SX' --where ast_revision = 'B2.M.SX' Seq Scan on d_trr (cost=0.00..2268460.98 rows=1 width=16) Filter: ((revision)::text ~~ '^B2.%.SX'::text) show lc_collate; en_US.UTF-8 Is it that this is handled by tsearch2? Or I need to do the locale to C for this to function? See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. It tells you how to create an index that like might use in non-C locales. Just more information. This columns is created with the varchar type. original index is created using CREATE INDEX idx_d_ast ON xmms.d_trh USING btree (revision varchar_pattern_ops); CREATE INDEX idx_d_ast2 ON xmms.d_trh USING btree (revision); after creating it, seems like it is still doing the seq_scan. So what gives? Can I get more clues here? Post your query and the explain analyze of it and how many rows are in the table. In my database, there's 7200 rows in items and I know that none of the identifiers for them start with 'xb'. As you can see below, the 1st query is sequential and the 2nd one is using the new index. (v8.3.0) postgres=# show lc_collate; lc_collate English_Australia.1252 (1 row) postgres=# explain analyse select * from items where identifier like 'xb%'; QUERY PLAN - Seq Scan on items (cost=0.00..160.18 rows=1 width=113) (actual time=4.966..4.966 rows=0 loops=1) Filter: ((identifier)::text ~~ 'xb%'::text) Total runtime: 5.029 ms (3 rows) postgres=# create index anindex on items(identifier varchar_pattern_ops); CREATE INDEX postgres=# explain analyse select * from items where identifier like 'xb%'; QUERY PLAN --- Index Scan using anindex on items (cost=0.00..8.27 rows=1 width=113) (actual time=0.165..0.165 rows=0 loops=1) Index Cond: (((identifier)::text ~=~ 'xb'::text) AND ((identifier)::text ~~ 'xc'::text)) Filter: ((identifier)::text ~~ 'xb%'::text) Total runtime: 0.255 ms (4 rows) -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [XP SP2/SP3] FATAL: could not reattach to shared memory
Hi, We have problems with PostgreSQL on Windows. PostgreSQL: 8.3.1 System: Windows XP (SP3) FS: NTFS Hardware: IBM x3650, 1x Xeon Quad, 2GB RAM The database is accessed from multiple windows apps. One of them has a connection pool, the others use single connection per app. Somethimes when I start PgAdmin and then our app, the second one freeze (not a PgAdmin bug, the same issue is with other apps). The log contains: FATAL: could not reattach to shared memory (key=1808, addr=01CC): 487 FATAL: could not reattach to shared memory (key=1808, addr=01CC): 487 2008-06-24 06:36:11 CEST WARNING: worker took too long to start; cancelled Errors appears on Windows XP SP2 too. On Windows 2000 we have never noticed memory errors like above - on very similar configuration (but with win2k) the PostgreSQL works perfectly. Anybody have any idea about this? Thanks, -- Łukasz Czerpak | PGP: 0xCBEAA46D [subkeys.pgp.net] Bankowość Elektroniczna SoftNet Sp. z o.o. http://www.softnet.pl/ begin:vcard fn;quoted-printable:=C5=81ukasz Czerpak n;quoted-printable:Czerpak;=C5=81ukasz org;quoted-printable:SoftNet Sp. z o.o.;Bankowo=C5=9B=C4=87 Elektroniczna adr;quoted-printable:;;Pana Tadeusza 8;Krak=C3=B3w;;30-727;Polska email;internet:[EMAIL PROTECTED] tel;work:+48 12 6837300 tel;fax:+48 12 6837311 note:PGP: 0xCBEAA46D [subkeys.pgp.net] x-mozilla-html:FALSE url:http://www.softnet.pl version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
On Wed, 2008-06-25 at 17:00 +1000, Klint Gore wrote: Ow Mun Heng wrote: On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote: Ow Mun Heng wrote: explain select * from d_trr where revision like '^B2.%.SX' --where ast_revision = 'B2.M.SX' Seq Scan on d_trr (cost=0.00..2268460.98 rows=1 width=16) Filter: ((revision)::text ~~ '^B2.%.SX'::text) show lc_collate; en_US.UTF-8 Is it that this is handled by tsearch2? Or I need to do the locale to C for this to function? See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html. It tells you how to create an index that like might use in non-C locales. Just more information. This columns is created with the varchar type. original index is created using CREATE INDEX idx_d_ast ON xmms.d_trh USING btree (revision varchar_pattern_ops); CREATE INDEX idx_d_ast2 ON xmms.d_trh USING btree (revision); after creating it, seems like it is still doing the seq_scan. So what gives? Can I get more clues here? Post your query and the explain analyze of it and how many rows are in the table. explain analyse select count(*) from d_trr_iw --where ast_revision like '^B2.%.SX' where ast_revision = 'B2.P.SX' QUERY PLAN Aggregate (cost=353955.35..353955.36 rows=1 width=0) (actual time=54.565..54.566 rows=1 loops=1) - Bitmap Heap Scan on d_trr_iw (cost=3150.63..353593.31 rows=144813 width=0) (actual time=54.557..54.557 rows=0 loops=1) Recheck Cond: ((ast_revision)::text = 'B2.P.SX'::text) - Bitmap Index Scan on idx_d_trr_iw_ast (cost=0.00..3114.42 rows=144813 width=0) (actual time=54.520..54.520 rows=0 loops=1) Index Cond: ((ast_revision)::text = 'B2.P.SX'::text) Total runtime: 54.662 ms In my database, there's 7200 rows in items and I know that none of the identifiers for them start with 'xb'. As you can see below, the 1st query is sequential and the 2nd one is using the new index. (v8.3.0) There's approx 29million rows in there and using the LIKE condition will churn it for a good 20-30min I suppose. (didn't try - live database) postgres=# show lc_collate; lc_collate English_Australia.1252 (1 row) postgres=# explain analyse select * from items where identifier like 'xb%'; QUERY PLAN - Seq Scan on items (cost=0.00..160.18 rows=1 width=113) (actual time=4.966..4.966 rows=0 loops=1) Filter: ((identifier)::text ~~ 'xb%'::text) Total runtime: 5.029 ms (3 rows) postgres=# create index anindex on items(identifier varchar_pattern_ops); CREATE INDEX postgres=# explain analyse select * from items where identifier like 'xb%'; QUERY PLAN --- Index Scan using anindex on items (cost=0.00..8.27 rows=1 width=113) (actual time=0.165..0.165 rows=0 loops=1) Index Cond: (((identifier)::text ~=~ 'xb'::text) AND ((identifier)::text ~~ 'xc'::text)) Filter: ((identifier)::text ~~ 'xb%'::text) Total runtime: 0.255 ms (4 rows) Could it be that it's not able to determine the B2.%.SX in there? explain select count(*) from d_trr_iw where ast_revision like 'B2.P.SX' even this will result in a seq_scan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [XP SP2/SP3] FATAL: could not reattach to shared memory
Łukasz Czerpak wrote: Hi, We have problems with PostgreSQL on Windows. PostgreSQL: 8.3.1 System: Windows XP (SP3) FS: NTFS Hardware: IBM x3650, 1x Xeon Quad, 2GB RAM The database is accessed from multiple windows apps. One of them has a connection pool, the others use single connection per app. Somethimes when I start PgAdmin and then our app, the second one freeze (not a PgAdmin bug, the same issue is with other apps). The log contains: FATAL: could not reattach to shared memory (key=1808, addr=01CC): 487 FATAL: could not reattach to shared memory (key=1808, addr=01CC): 487 2008-06-24 06:36:11 CEST WARNING: worker took too long to start; cancelled Errors appears on Windows XP SP2 too. On Windows 2000 we have never noticed memory errors like above - on very similar configuration (but with win2k) the PostgreSQL works perfectly. Anybody have any idea about this? 487 is Invalid address. Do you have any antivirus or similar software on the machine? If so, try uninstalling it and trying again (just disabling it is usually not enough). Have you changed any configuration parameters around the memory size such as shared_buffers? //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump estimation
On Tue, Jun 24, 2008 at 11:13:14PM -0500, Marcelo Martins wrote: is there a way to find out / calculate / estimate how big a pg_dump using plain text format for a DB will be ? How about simply doing: pg_dump | wc -c Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE not using indexes (due to locale issue?)
Ow Mun Heng wrote: Could it be that it's not able to determine the B2.%.SX in there? explain select count(*) from d_trr_iw where ast_revision like 'B2.P.SX' even this will result in a seq_scan. How many values have you got that start with 'B2.'? If it's more than about 5% then it's probably quicker to sequential scan anyway. Since the equals query says there's one row with a P in the middle, what does this say? explain select count(*) from d_trr_iw where ast_revision like 'B2.P%.SX' or pick a value that you know can't exist e.g. if ast_revision can never start with WW then explain select count(*) from d_trr_iw where ast_revision like 'WW.%.SX' klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Probably been asked a hundred times before.
On 2008-06-24 16:30, David Siebert wrote: Which disto is best for running a Postgres server? I'd go for CentOS 5.2 (or better RedHat Enterprise Linux 5.2, if you can afford it, as $349/year for basic support can save you several hours of problem solving). But by default CentOS5/RHEL5 have PostgreSQL 8.1, so I'd configure an official Postgresql 8.3 yum repository: http://yum.pgsqlrpms.org/howtoyum.php I'd get: - automatic OS bug fixes up until Sep 30, 2010; - automatic OS security updates and mission critical bug fixes up until Mar 31, 2014; - automatic PostgreSQL security and bug fixes while 8.3 branch is supported; - additional OS security protection from SElinux; - Tom Lane, the most active developer, works for RedHat ;-) It isn't that big of a hassle but I noticed that almost none of the big distros keep all that up to date with Postgres as far as what they have in their repositories. It is not possible to keep up for enterprise grade distros. A major PostgreSQL version is released every year, compared to 2-3 years between enterprise grade major distro release. A distro can not do a major upgrade of PostgreSQL as its major versions data storage formats are not compatible. I'd stay away from not enterprise grade distros like Fedora, as a 1 year support lifetime is much too low. It is good for your home computer but not for a server. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [XP SP2/SP3] FATAL: could not reattach to shared memory
Magnus Hagander wrote: 487 is Invalid address. Do you have any antivirus or similar software on the machine? If so, try uninstalling it and trying again (just disabling it is usually not enough). Yes - NOD32. Ok i will check it. Is it possible that PostgreSQL works improperly on WinXP + NOD32 and properly on Win2k + NOD32? Have you changed any configuration parameters around the memory size such as shared_buffers? My memory parameters: max_connections = 100 #-- # RESOURCE USAGE (except WAL) #-- # - Memory - shared_buffers = 128MB temp_buffers = 8MB #max_prepared_transactions = 5 work_mem = 8MB maintenance_work_mem = 32MB Thanks, -- Łukasz Czerpak | PGP: 0xCBEAA46D [subkeys.pgp.net] Bankowość Elektroniczna SoftNet Sp. z o.o. http://www.softnet.pl/ begin:vcard fn;quoted-printable:=C5=81ukasz Czerpak n;quoted-printable:Czerpak;=C5=81ukasz org;quoted-printable:SoftNet Sp. z o.o.;Bankowo=C5=9B=C4=87 Elektroniczna adr;quoted-printable:;;Pana Tadeusza 8;Krak=C3=B3w;;30-727;Polska email;internet:[EMAIL PROTECTED] tel;work:+48 12 6837300 tel;fax:+48 12 6837311 note:PGP: 0xCBEAA46D [subkeys.pgp.net] x-mozilla-html:FALSE url:http://www.softnet.pl version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [XP SP2/SP3] FATAL: could not reattach to shared memory
Łukasz Czerpak wrote: Magnus Hagander wrote: 487 is Invalid address. Do you have any antivirus or similar software on the machine? If so, try uninstalling it and trying again (just disabling it is usually not enough). Yes - NOD32. Ok i will check it. NOD32 is known to cause a lot of issues in general. Is it possible that PostgreSQL works improperly on WinXP + NOD32 and properly on Win2k + NOD32? Yes. shared_buffers = 128MB If you still have the problem after removing NOD32, try lowering this one to say 32MB just to see if the problem goes away then. //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what are rules for?
Tom Lane wrote: To expand on that: it's pretty hard to see how update or delete triggers on a view would work. Insert is easy, because if left to its own devices the system would in fact try to insert a tuple into the view relation, and that action could fire a trigger which could redirect the insertion someplace else. But updates and deletes require a pre-existing target tuple, and there just aren't any of those in a view relation. (Another way to say it is that update/delete require a CTID column, which a view hasn't got.) So view update/delete appear to require a transformational-rule kind of approach instead of an actions-on-physical-tuples kind of approach. If you've got a better idea we're all ears ... Would it be any easier to implement Oracle-style instead of triggers for views, instead of before and after triggers? Notionally this seems like a do instead select trigger_fn() rule, with the trigger function having complete responsibility for updating the underlying table(s). The difficultly I can see is what data to pass to the trigger function, since just passing the old and new values from the view may not be enough to work out which rows to update. But then, this is no worse than what Oracle currently does, and for many data models it is very useful. I've used rules to implement updateable views, and I would certainly have found triggers much easier to work with. In particular, certain things didn't seem to be possible at all with rules, such as before insert and after delete actions, because the where clause doesn't match anything at those points. With an instead of trigger you can obviously do whatever you want, in any order. Dean. _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] replication
Initially you may want to get the full dump (this applies also to the replication option) across to your other site. You may want to bzip2 the dump file, you may be surprised by the reduction of file size using bzip2. Try tar -cjf mydump.sql.tar.bz2 mydump.sql then transfer it using scp or place it on an ftp server in your local office, then login remotely (ssh) to a pc/server on your destination office then issue wget -c -t0 http://myftpsite/path/to/mydump.sql;. Allan. Asko Oja wrote: Hi Take a look also on Londiste from SkyTools. It is easy to set up and manage. In addition SkyTools contains other useful scripts and tools. We use Londiste to replicate data ovwe WAN where walshipping would consume too much bandwidth. Asko On Tue, Jun 24, 2008 at 9:41 AM, Adrian Moisey [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi We have a 100GB database (16GB dumped) running on 8.2. Since the bandwidth in South Africa isn't that freely available it is difficult for us to get a new copy of out DB in our office (our fastest link in the office is 4Mbps). What can you recommend for me to get an up to date copy of my DB in the office with as little transfer as possible. A few ideas I've been toying with are rsyncing a dump of the db, or replication. Any ideas? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za http://www.careerjunction.co.za | Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump estimation
Sam Mason wrote: On Tue, Jun 24, 2008 at 11:13:14PM -0500, Marcelo Martins wrote: is there a way to find out / calculate / estimate how big a pg_dump using plain text format for a DB will be ? How about simply doing: pg_dump | wc -c Sam Hi Marcelo, If (using Sam's suggestion) the reported size of bytes is larger than (or even close to) the current free space, you may want to plug in an external drive (USB or otherwise) and mount it, then do the dump to it. If you intend to upgrade postgreSQL to 8.3.3, I think it may be advisable to first install postgreSQL 8.3.3 hopefully from source as you can have more control on where the executable will be placed (the idea is to avoid at least initially overwriting the current executables for postgres you have already deployed) use the --prefix=/your/path option to facilitate isolated installation. Then use pg_dump (or pg_dumpall) of the new postgreSQL installation to take the dump. In simpler words, use 8.3.x pg_dump to make a dump of a running instance of postgreSQL 7.x for later restore on postgreSQL 8.3.x. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump estimation
In response to Allan Kamau [EMAIL PROTECTED]: Sam Mason wrote: On Tue, Jun 24, 2008 at 11:13:14PM -0500, Marcelo Martins wrote: is there a way to find out / calculate / estimate how big a pg_dump using plain text format for a DB will be ? How about simply doing: pg_dump | wc -c Sam Hi Marcelo, If (using Sam's suggestion) the reported size of bytes is larger than (or even close to) the current free space, you may want to plug in an external drive (USB or otherwise) and mount it, then do the dump to it. If you intend to upgrade postgreSQL to 8.3.3, I think it may be advisable to first install postgreSQL 8.3.3 hopefully from source as you can have more control on where the executable will be placed (the idea is to avoid at least initially overwriting the current executables for postgres you have already deployed) use the --prefix=/your/path option to facilitate isolated installation. Then use pg_dump (or pg_dumpall) of the new postgreSQL installation to take the dump. In simpler words, use 8.3.x pg_dump to make a dump of a running instance of postgreSQL 7.x for later restore on postgreSQL 8.3.x. Also, you can probably use CLUSTER to get the DB size down to something manageable. If you CLUSTER one table at a time, you can probably start with the smaller tables and free up enough room to CLUSTER the larger tables. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] replication
On Wed, Jun 25, 2008 at 01:52:00PM +0200, Allan Kamau wrote: Initially you may want to get the full dump (this applies also to the replication option) across to your other site. Most of the replication systems I've seen require that _they_ send the data to the remote site, so copying the data yourself is actually a waste of bandwidth, since it's all going to get copied anyway. (They have to do this because that's how they know that they're starting from a point in time for which they have consistency information.) You can indeed save on bandwith, however, by tunnelling your postgres connections through an ssh tunnel that's using compression. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] Reg : Data base deletion
In response to Pradeepa [EMAIL PROTECTED]: Could you please explain the scenario in detail. Not sure I understand what you mean ... the scenario is yours to explain. Here is the documentation on configuring PG logging, if that's what you mean: http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html log_statement, in particular, is something to read up on. You didn't mention which version of PG you're using, nor what GUI and version you're using to access it. I expect you'll get better answers if you provide that information. I also wanted to let you that the Db has been already deleted and we wanted to know when it has been deleted for backup and restoration. That then depends on whether or not you had DDL logging enabled before the event occurred. If not, then I'm not sure how to identify when the event occurred. If it was not enabled, then you may want to look at whether the GUI you're using logs actions like that, but that will depend on what GUI you're using. Since your question isn't a bug, I've redirected the thread to the pgsql-general@ mailing list. Thanks in advance. Pradeepa -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2008 5:36 PM To: Pradeepa Cc: [EMAIL PROTECTED] Subject: Re: [BUGS] Reg : Data base deletion In response to Pradeepa [EMAIL PROTECTED]: We want some information like, if any one deletes database from postgres, how to trace when it has been deleted. The easiest way that I know of is to enable logging of DDL statements in postgresql.conf. Make sure you have enough disk space to handle all the log data that results. The deletion is not from any command but by right clicking on db and deleting it. It doesn't matter. Whatever GUI you're using to do this will simply convert your clicking into SQL commands, so it's really the same thing from the server's perspective. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] a question about data corruption
Hello, My customer has experienced a serious data corruption. He was using a posgresql version 8.0.3 I know that it is an old version. Postgresql started to log could not access status of transaction messages. Since the transaction IDs were far away from what server was using I took a look at data files i pgsql/base/ and I found a total garbage there. In five tables (of about 100) I have found a one to four blocks of random data. In two places it was data from other table instead of random data. All these blocks have sizes which are multiplications of 512 (for example 1, 9, 26) and starts at offsets which are also a multiplication of 512. The database is small 1G and there are no deletes or updates to affected tables, just inserts and selects. The database was vacuumed about 3 months before the time the corruption has been noticed. The system is linux 2.4.31 filesystem XFS on RAID5 And there are no messages sugesting I/O error or something My question is: Is there any known bug in postgresql 8.0.3 that could lead to such a data corruption or is it rather a hardware problem? Best regards, Jacek Rembisz PS. Please cc me as I'm not a subscriber. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Probably been asked a hundred times before.
On Tue, 24 Jun 2008, David Siebert wrote: Which disto is best for running a Postgres server? You didn't define what best means for you. If you want to always want to stay current with new releases, the RedHat/Fedora packages available at http://www.postgresql.org/download are on average the most up to date. I personally avoid Fedora because the support lifetime is so short, and on non-production servers I'll use CentOS instead of the official RedHat. If you want something popular so that you will likely be able to find support help if you run into issues, again a RHEL/Fedora system is good for that, with Ubuntu being another increasingly mainstream choice. Should running multiple databases instances at once, having easy scripts to upgrade between versions, and being able to easily install additional software be important goals, a Debian or Ubuntu system has some nice features. The main thing to watch for is that the Ubuntu desktop system is optimized a bit oddly for database use. If you'd like a more stable system with powerful filesystem and OS-debugging tools, and don't mind having a less popular system with less open-source gadgets tacked on, consider Solaris or FreeBSD. If performance is your priority, what will work best really depends on what hardware you intend to deploy on. It's possible to get a good high-performance setup out of any of these, it's just a matter of matching the appropriate supported hardware. There are some weird issues with really recent Linux kernels and PostgreSQL so you need to be careful there. I've put some suggestions about what works well and badly for me at http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html you might find interesting. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: could not open relation with OID 2836
PgSQL is returning that error when I open pgdmin and when I run some queries related to pg_buffercache. Also pg_dump cannot dump the DB. PgSQL version is 8.3.3 and happened one day after loading the DB there. Anything that can be done? or I have to restore a backup and put current data again? Thanks Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Serialized Access
I'm working on an application that uses EJB3 entities in JBoss, with Hibernate and a PostgreSQL database. One of the entity tables needs consistent, synchronized updates to rows in an environment where telling the user that their operation failed and starting over is not an option. Because it's the default, I've used EJB3's optimistic locking with a strategy of catching EJBExceptions and retrying my updates. Since contention can be frequent, the overhead (and extra logic) for this seems like a waste. I would like to try pessimistic locking and compare the results, but here's where my problem arises. EJB documentation passes this off to the application server, which considers it a ORM problem. Hibernate says it doesn't add any lock features beyond what JDBC and the database provide In the end, I need Java code in a stateless bean that causes serialized access to database rows that are under the control of an EntityManager, but the approach to doing that is eluding me. So, I'm not entirely sure this is a PostgreSQL question, but if not perhaps someone can tell me which layer of this architecture *is* responsible. :-) Thanks in advance.
Re: [GENERAL] Probably been asked a hundred times before.
Well I am kind of stuck using OpenSuse. Not a bad distro and is the one we use in our office for production work. I like CentOS myself for database work and tend to use that for test systems here since I manage them myself. I was more wondering if someone had made a Postgres centric distro yet. Sort of FreeNAS, OpenFiler, or what ever the Asterisk distro is called these days. Seems like you could build a nice little distro that was database centric. Maybe use FreeBSD, Solaris, or Centos as the base. Sort of a plug and play solution. I do wonder just how well Solaris plus ZFS would work for a Postgres server. I am lucky that my database is only several hundred thousands records and only has a few dozen users hitting it. It ran for the longest time on just a 400 MHZ PII and is now running under CentOS on a whopping 600Mhz PIII with all of 256 mb of ram. It is going to finally move to a real server with dual Xeons and a gig of ram. That should keep it happy for a decade or two. Did I mention that I love the performance of Postgres and Linux? Greg Smith wrote: On Tue, 24 Jun 2008, David Siebert wrote: Which disto is best for running a Postgres server? You didn't define what best means for you. If you want to always want to stay current with new releases, the RedHat/Fedora packages available at http://www.postgresql.org/download are on average the most up to date. I personally avoid Fedora because the support lifetime is so short, and on non-production servers I'll use CentOS instead of the official RedHat. If you want something popular so that you will likely be able to find support help if you run into issues, again a RHEL/Fedora system is good for that, with Ubuntu being another increasingly mainstream choice. Should running multiple databases instances at once, having easy scripts to upgrade between versions, and being able to easily install additional software be important goals, a Debian or Ubuntu system has some nice features. The main thing to watch for is that the Ubuntu desktop system is optimized a bit oddly for database use. If you'd like a more stable system with powerful filesystem and OS-debugging tools, and don't mind having a less popular system with less open-source gadgets tacked on, consider Solaris or FreeBSD. If performance is your priority, what will work best really depends on what hardware you intend to deploy on. It's possible to get a good high-performance setup out of any of these, it's just a matter of matching the appropriate supported hardware. There are some weird issues with really recent Linux kernels and PostgreSQL so you need to be careful there. I've put some suggestions about what works well and badly for me at http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html you might find interesting. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what are rules for?
On Tue, Jun 24, 2008 at 11:08 AM, Tom Lane [EMAIL PROTECTED] wrote: To expand on that: it's pretty hard to see how update or delete triggers on a view would work. Insert is easy, because if left to its own devices the system would in fact try to insert a tuple into the view relation, and that action could fire a trigger which could redirect the insertion someplace else. But updates and deletes require a pre-existing target tuple, and there just aren't any of those in a view relation. (Another way to say it is that update/delete require a CTID column, which a view hasn't got.) But isn't the CTID column only required in order for the executor to actually *do* the update or delete? And since with a view, there is nothing to actually update or delete in the view itself, the trigger would be doing the only actual updating or deleting, so where would the CTID column be needed? Perhaps this is the same as the instead trigger suggestion -- I'm not familiar with Oracle. Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 0xc3 error Text Search Windows French
I have a feeling that an issue I'm running into is related to this:
http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL 8.3.0
or 8.3.3 DB, when attempting to do a:
select * from ts_debug('french', 'catalogue');
getting the following error:
ERROR: invalid byte sequence for encoding UTF8: 0xc3
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
CONTEXT: SQL function ts_debug statement 1
I have replaced the french.stop file with the one from the snowball web
site (http://snowball.tartarus.org/algorithms/french/stemmer.html) to
see if that would make any difference. But the same issue. I have also
attempted to load the French Hunspell dictionary from the Open Office
web site (http://wiki.services.openoffice.org/wiki/Dictionaries), using
the following command:
CREATE TEXT SEARCH DICTIONARY public.fr_ispell (
TEMPLATE = pg_catalog.ispell,
DictFile = fr_FR,
AffFile = fr_FR,
StopWords = french
);
But getting the same error. I have successfully loaded the English and
Arabic dictionaries and an Arabic stop file I sourced from elsewhere,
and they work fine with the various text search function calls, so it
appears to be specifically related to a French character occurring in
the stop file and the dictionaries. To use the French OO dictionaries,
I had to convert them from an ISO-8859-15 character set encoding to
UTF-8. As it still had the same result as with the packaged stop file
when converting on Windows, I downloaded them and converted the encoding
on a Linux machine before copying them across to windows to see if that
would help, but it didn't.
However, if I run the ts_debug('french', 'catalogue'); against a Linux
version of PostgreSQL 8.3.1, it works fine. I have not tried version
8.3.1 on Windows. While there are a lot more combinations to exhaust
before I can make a categorical statement, at this stage it appears to
be pointing towards an issue with the UTF-8 parser of PostgreSQL on Windows.
Is this an outstanding defect, or is there something that I'm doing
wrong in my environment? I have attempted to find anything related on
the Internet, but other than the introductory reference, I have not
found anything, which for what I would imagine to be, of the size of the
French user base surprises me. Hence, I'm thinking that perhaps it may
be something in my environment causing the issue. If others could also
reproduce the error on their XP machines, that would indicate that the
issue was not something specific just to me.
At this stage, it is not that important to me, as I'm just playing
around with text search for my own curiosity and French was just a
language I have randomly picked, along with Arabic (for which I'm
lacking a snowball stemmer). I don't actually read, much less speak
those languages. However, it would still be nice to have them working.
An additional related topic. OO have for some languages, thesaurus
files which are not in the same format as supported by Pg Full Text
Search. Are there any plans to support the OO thesaurus file formats?
They also have hyphenation files. Are there any plans to extend the
current dictionary files to include hyphenation rules as captured in the
OO hyphenation files? I'm not sure how, if at all hyphenation rules
would improve on indexing and searches, but I thought as the files
exist, I would pose the question.
Thanks,
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 0xc3 error Text Search Windows French
Sorry one last detail.
All of my databases are in utf-8 format. My Windows XP is en_AU and
defaults to ISO-8859-1 character sets. My postgresql.conf is set to the
default for the client_encoding setting, which should then default to
the database utf-8 format.
Andrew wrote:
One additional aspect. I just ran the create text search dictionary
command without the stopfile declaration using the OO dictionaries,
and it worked fine with the select ts_lexize('public.fr_ispell',
'catalogue'); command executing with no problems. However, after
creating an associated catalogue based on a copy of the
pg_catalog.french catalogue, calls to ts_debug against my custom
French config result in the 0xc3 error. So it is looking like the
problem is restricted to the parsing of the stop file.
I ran through the other out of the box supplied stemmers, which I have
not touched in anyway and it is also occurring with the portuguese
catalogue.
Cheers
Andy
Andrew wrote:
I have a feeling that an issue I'm running into is related to this:
http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL
8.3.0 or 8.3.3 DB, when attempting to do a:
select * from ts_debug('french', 'catalogue');
getting the following error:
ERROR: invalid byte sequence for encoding UTF8: 0xc3
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
CONTEXT: SQL function ts_debug statement 1
I have replaced the french.stop file with the one from the snowball
web site
(http://snowball.tartarus.org/algorithms/french/stemmer.html) to see
if that would make any difference. But the same issue. I have also
attempted to load the French Hunspell dictionary from the Open Office
web site (http://wiki.services.openoffice.org/wiki/Dictionaries),
using the following command:
CREATE TEXT SEARCH DICTIONARY public.fr_ispell (
TEMPLATE = pg_catalog.ispell,
DictFile = fr_FR,
AffFile = fr_FR,
StopWords = french
);
But getting the same error. I have successfully loaded the English
and Arabic dictionaries and an Arabic stop file I sourced from
elsewhere, and they work fine with the various text search function
calls, so it appears to be specifically related to a French character
occurring in the stop file and the dictionaries. To use the French
OO dictionaries, I had to convert them from an ISO-8859-15 character
set encoding to UTF-8. As it still had the same result as with the
packaged stop file when converting on Windows, I downloaded them and
converted the encoding on a Linux machine before copying them across
to windows to see if that would help, but it didn't.
However, if I run the ts_debug('french', 'catalogue'); against a
Linux version of PostgreSQL 8.3.1, it works fine. I have not tried
version 8.3.1 on Windows. While there are a lot more combinations to
exhaust before I can make a categorical statement, at this stage it
appears to be pointing towards an issue with the UTF-8 parser of
PostgreSQL on Windows.
Is this an outstanding defect, or is there something that I'm doing
wrong in my environment? I have attempted to find anything related
on the Internet, but other than the introductory reference, I have
not found anything, which for what I would imagine to be, of the size
of the French user base surprises me. Hence, I'm thinking that
perhaps it may be something in my environment causing the issue. If
others could also reproduce the error on their XP machines, that
would indicate that the issue was not something specific just to me.
At this stage, it is not that important to me, as I'm just playing
around with text search for my own curiosity and French was just a
language I have randomly picked, along with Arabic (for which I'm
lacking a snowball stemmer). I don't actually read, much less speak
those languages. However, it would still be nice to have them working.
An additional related topic. OO have for some languages, thesaurus
files which are not in the same format as supported by Pg Full Text
Search. Are there any plans to support the OO thesaurus file
formats? They also have hyphenation files. Are there any plans to
extend the current dictionary files to include hyphenation rules as
captured in the OO hyphenation files? I'm not sure how, if at all
hyphenation rules would improve on indexing and searches, but I
thought as the files exist, I would pose the question.
Thanks,
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Probably been asked a hundred times before.
[EMAIL PROTECTED] (David Siebert) writes:
Well I am kind of stuck using OpenSuse. Not a bad distro and is the one
we use in our office for production work.
I like CentOS myself for database work and tend to use that for test
systems here since I manage them myself.
I was more wondering if someone had made a Postgres centric distro yet.
Sort of FreeNAS, OpenFiler, or what ever the Asterisk distro is called
these days.
Seems like you could build a nice little distro that was database
centric. Maybe use FreeBSD, Solaris, or Centos as the base.
Sort of a plug and play solution.
A pretty minimalist approach would be...
- Install Debian base (~20MB of install)
- Figure out packages needed for PostgreSQL
PKGS=postgresql-client-8.3 postgresql-8.3
- Libraries, and such
PKGS=${PKGS} libpq5 libdbd-pg-perl
- Some tools
PKGS=${PKGS} pgadmin3 pgadmin3-data
- Some useful 3rd party bits
PKGS=${PKGS} cfengine2 ntp ssh vim
Then install that...
$ apt-get install ${PKGS}
That's going to draw in some dependancies, but is still quite, quite
minimal, moreso than anything that wasn't *expressly* customized for
the purpose. That will, for instance, be *way* smaller than Centos.
You could do much the same using ports/openpkg on FreeBSD.
--
output = reverse(gro.mca @ enworbbc)
http://cbbrowne.com/info/finances.html
Computers are like air conditioners: They stop working properly if
you open windows.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 0xc3 error Text Search Windows French
One additional aspect. I just ran the create text search dictionary
command without the stopfile declaration using the OO dictionaries, and
it worked fine with the select ts_lexize('public.fr_ispell',
'catalogue'); command executing with no problems. However, after
creating an associated catalogue based on a copy of the
pg_catalog.french catalogue, calls to ts_debug against my custom French
config result in the 0xc3 error. So it is looking like the problem is
restricted to the parsing of the stop file.
I ran through the other out of the box supplied stemmers, which I have
not touched in anyway and it is also occurring with the portuguese
catalogue.
Cheers
Andy
Andrew wrote:
I have a feeling that an issue I'm running into is related to this:
http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL
8.3.0 or 8.3.3 DB, when attempting to do a:
select * from ts_debug('french', 'catalogue');
getting the following error:
ERROR: invalid byte sequence for encoding UTF8: 0xc3
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
CONTEXT: SQL function ts_debug statement 1
I have replaced the french.stop file with the one from the snowball
web site (http://snowball.tartarus.org/algorithms/french/stemmer.html)
to see if that would make any difference. But the same issue. I have
also attempted to load the French Hunspell dictionary from the Open
Office web site
(http://wiki.services.openoffice.org/wiki/Dictionaries), using the
following command:
CREATE TEXT SEARCH DICTIONARY public.fr_ispell (
TEMPLATE = pg_catalog.ispell,
DictFile = fr_FR,
AffFile = fr_FR,
StopWords = french
);
But getting the same error. I have successfully loaded the English
and Arabic dictionaries and an Arabic stop file I sourced from
elsewhere, and they work fine with the various text search function
calls, so it appears to be specifically related to a French character
occurring in the stop file and the dictionaries. To use the French OO
dictionaries, I had to convert them from an ISO-8859-15 character set
encoding to UTF-8. As it still had the same result as with the
packaged stop file when converting on Windows, I downloaded them and
converted the encoding on a Linux machine before copying them across
to windows to see if that would help, but it didn't.
However, if I run the ts_debug('french', 'catalogue'); against a Linux
version of PostgreSQL 8.3.1, it works fine. I have not tried version
8.3.1 on Windows. While there are a lot more combinations to exhaust
before I can make a categorical statement, at this stage it appears to
be pointing towards an issue with the UTF-8 parser of PostgreSQL on
Windows.
Is this an outstanding defect, or is there something that I'm doing
wrong in my environment? I have attempted to find anything related on
the Internet, but other than the introductory reference, I have not
found anything, which for what I would imagine to be, of the size of
the French user base surprises me. Hence, I'm thinking that perhaps
it may be something in my environment causing the issue. If others
could also reproduce the error on their XP machines, that would
indicate that the issue was not something specific just to me.
At this stage, it is not that important to me, as I'm just playing
around with text search for my own curiosity and French was just a
language I have randomly picked, along with Arabic (for which I'm
lacking a snowball stemmer). I don't actually read, much less speak
those languages. However, it would still be nice to have them working.
An additional related topic. OO have for some languages, thesaurus
files which are not in the same format as supported by Pg Full Text
Search. Are there any plans to support the OO thesaurus file
formats? They also have hyphenation files. Are there any plans to
extend the current dictionary files to include hyphenation rules as
captured in the OO hyphenation files? I'm not sure how, if at all
hyphenation rules would improve on indexing and searches, but I
thought as the files exist, I would pose the question.
Thanks,
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] POSIX Escape
Hello All,
Apologies if this is a bit off-topic. Any hints as to what character
sequences need escaping in Postgres's regular expressions?
Let me show you the problem.
If I want to find all strings that start with 'postgres' I use the regex
select str ~ '^postgres'.
However if str contains reserved meta characters, e.g. +(-)[.]{} etc
then I need to escape that sequence somehow? e.g. to find a string
starting 'fish + chips' I need to escape for regex and then postgres.
select str ~ '^fish\\s\\+\\schips''
Has anyone got a definitive list of replacements that are required for
Postgres's version of Regular Expressions?
Howard.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 0xc3 error Text Search Windows French
Andrew wrote:
I have a feeling that an issue I'm running into is related to this:
http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL 8.3.0
or 8.3.3 DB, when attempting to do a:
select * from ts_debug('french', 'catalogue');
getting the following error:
ERROR: invalid byte sequence for encoding UTF8: 0xc3
This is probably a bug fixed after 8.3.3:
http://git.postgresql.org/?p=postgresql.git;a=commitdiff;h=6bcf5a85233f7a039648990d1037119efb61146d
(This is the HEAD version of the patch; 8.3 should be identically
patched.)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] IF ROW( NEW ) ROW( OLD )
Is it possible to cast a table type to a ROW so that Row-Wise comparison can be achieved? -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] a question about data corruption
Jacek Rembisz [EMAIL PROTECTED] writes: Postgresql started to log could not access status of transaction messages. Since the transaction IDs were far away from what server was using I took a look at data files i pgsql/base/ and I found a total garbage there. In five tables (of about 100) I have found a one to four blocks of random data. In two places it was data from other table instead of random data. All these blocks have sizes which are multiplications of 512 (for example 1, 9, 26) and starts at offsets which are also a multiplication of 512. Substituting sector-size blocks of one file for another could easily be a filesystem (kernel) bug ... The system is linux 2.4.31 filesystem XFS on RAID5 ... and XFS on such an old kernel version doesn't seem like a very good bet for stability. My question is: Is there any known bug in postgresql 8.0.3 that could lead to such a data corruption or is it rather a hardware problem? No, nothing like that has ever been reported in any released PG version. If the substituted blocks were from non-Postgres files then I think you could write off the idea of a PG bug entirely. It could still be a software issue though. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez wrote: PgSQL is returning that error when I open pgdmin and when I run some queries related to pg_buffercache. Also pg_dump cannot dump the DB. PgSQL version is 8.3.3 and happened one day after loading the DB there. What platform are you using? If Windows: - Which version of Windows? - do you have a virus scanner on the system? Which one? Version? Otherwise: - What OS and version are you using? - What filesystem type is the database on? - Are you using any sort of RAID / LVM? If so, what kind? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Serialized Access
Phillip Mills wrote: I'm working on an application that uses EJB3 entities in JBoss, with Hibernate and a PostgreSQL database. One of the entity tables needs consistent, synchronized updates to rows in an environment where telling the user that their operation failed and starting over is not an option. Because it's the default, I've used EJB3's optimistic locking with a strategy of catching EJBExceptions and retrying my updates. Since contention can be frequent, the overhead (and extra logic) for this seems like a waste. I would like to try pessimistic locking and compare the results, but here's where my problem arises. EJB documentation passes this off to the application server, which considers it a ORM problem. Hibernate says it doesn't add any lock features beyond what JDBC and the database provide In the end, I need Java code in a stateless bean that causes serialized access to database rows that are under the control of an EntityManager, but the approach to doing that is eluding me. You might want to look into advisory locking. If your locks don't need to be longer than the life of an active EntityManager session then you can probably just issue a native query through the EntityManager to acquire the lock before doing anything more. The main thing you need to be aware of is that advisory locks are not transaction scoped, they're connection scoped. They are held until explicitly released, or until connection close. You'll have to make sure to release any locks when you catch persistence exceptions that leave the connection usable. If the connection is broken you don't need to do anything special as Pg will release the locks for you. Advisory locking is specific to PostgreSQL and will not be portable to other databases. See: http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS I'm going to have a play and see if I can come up with a demo that uses advisory locking through Hibernate. So far I'm only using it with direct JDBC calls in some of the trickier bits of the app I'm working on, so I haven't had cause to combine it with Hibernate yet. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez [EMAIL PROTECTED] writes: PgSQL is returning that error when I open pgdmin and when I run some queries related to pg_buffercache. Also pg_dump cannot dump the DB. PgSQL version is 8.3.3 and happened one day after loading the DB there. That raises a lot of questions about the trustworthiness of the platform you're using (hardware, kernel, etc). In 8.3, 2836 is pg_proc's TOAST table and should certainly never get deleted. I think you have filesystem or hardware issues. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] IF ROW( NEW ) ROW( OLD )
On Wed, Jun 25, 2008 at 4:40 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: I have never tried it but you may be able to do it with a composite type. True. However, I was under the impression that table types were essentially composite types. So I thought that composite types would behave the same. I could manually expand the composite type OLD and NEW to enumerate each field and make a row wise comparison. However, I was hoping to avoid that. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Craig Ringer wrote: Rodrigo Gonzalez wrote: PgSQL is returning that error when I open pgdmin and when I run some queries related to pg_buffercache. Also pg_dump cannot dump the DB. PgSQL version is 8.3.3 and happened one day after loading the DB there. What platform are you using? If Windows: - Which version of Windows? - do you have a virus scanner on the system? Which one? Version? Otherwise: - What OS and version are you using? - What filesystem type is the database on? - Are you using any sort of RAID / LVM? If so, what kind? -- Craig Ringer It's running under CentOS 4.4 using ext3, no RAID or LVM. Server is quad xeon 64 bits 3 GHz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: Rodrigo Gonzalez [EMAIL PROTECTED] writes: PgSQL is returning that error when I open pgdmin and when I run some queries related to pg_buffercache. Also pg_dump cannot dump the DB. PgSQL version is 8.3.3 and happened one day after loading the DB there. That raises a lot of questions about the trustworthiness of the platform you're using (hardware, kernel, etc). In 8.3, 2836 is pg_proc's TOAST table and should certainly never get deleted. I think you have filesystem or hardware issues. regards, tom lane Can be related to oom killer? I noticed today that it was invoked twice. I am making other server to move the DB finally there, but would want to know if the problem is hardware or not to be prepared for future... Best regards Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Serialized Access
On Wed, Jun 25, 2008 at 9:21 AM, Phillip Mills [EMAIL PROTECTED] wrote: I'm working on an application that uses EJB3 entities in JBoss, with Hibernate and a PostgreSQL database. One of the entity tables needs consistent, synchronized updates to rows in an environment where telling the user that their operation failed and starting over is not an option. Because it's the default, I've used EJB3's optimistic locking with a strategy of catching EJBExceptions and retrying my updates. Since contention can be frequent, the overhead (and extra logic) for this seems like a waste. Until you benchmark it for your app you really don't know how inefficient it really is compared to pessimistic locking. And what extra logic is needed to retry the failed transaction, or do you mean the logic in the app / jdbc, and not in postgresql. Because the logic in pgsql should pretty much be to just run the whole transaction over again. Since there's usually only one db but multiple web servers, having the web servers work a little harder is scalable, while doing anything that slows down the db will cost everyone. But I'm not really sure what exactly you're trying to do, so I don't really know what to advise on this one. But definitely use the serializable transaction level with retry for a benchmark to see how it scales under your contention conditions. Just to have a baseline to compare to. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what are rules for?
Michael Shulman [EMAIL PROTECTED] writes: On Tue, Jun 24, 2008 at 11:08 AM, Tom Lane [EMAIL PROTECTED] wrote: ... But updates and deletes require a pre-existing target tuple, and there just aren't any of those in a view relation. (Another way to say it is that update/delete require a CTID column, which a view hasn't got.) But isn't the CTID column only required in order for the executor to actually *do* the update or delete? And since with a view, there is nothing to actually update or delete in the view itself, the trigger would be doing the only actual updating or deleting, so where would the CTID column be needed? Well, both the trigger call API and the underlying implementation deal in CTIDs, so just airily saying we don't need 'em doesn't obviously work. (Note I did not say obviously doesn't work. Whether this is feasible depends on much closer analysis than any of the hand-waving that we've done so far.) To my mind there are two really fundamental issues underlying this. One, which is what CTID fixes, is that a view doesn't have any primary key by which to identify which row you're talking about. (Even if there is a candidate key implicit in the view semantics, we don't have any way for the system to know what it is.) The other nasty little issue is that if the view involves any non-immutable functions, it's not necessarily the case that you can recompute the OLD row at all. Also, if the view involves expensive functions, you'd probably rather the system *didn't* recompute them unless absolutely needed, even if they're immutable. A transform-based approach can succeed at that, but a trigger-based approach really can't since it needs to see materialized OLD and NEW rows. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] IF ROW( NEW ) ROW( OLD )
Richard Broersma [EMAIL PROTECTED] writes: I could manually expand the composite type OLD and NEW to enumerate each field and make a row wise comparison. However, I was hoping to avoid that. I think IF ROW(NEW.*) ROW(OLD.*) will work in recent releases. Actually you'd better use IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) ... you really don't want to rely on as it will not give the behavior you want in the presence of null columns. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez [EMAIL PROTECTED] writes: Craig Ringer wrote: What platform are you using? It's running under CentOS 4.4 using ext3, no RAID or LVM. Server is quad xeon 64 bits 3 GHz Ugh, I'd have liked to think RHEL4/Centos4 would be more reliable than that :-(. Still, you might have an issue with trying to use hardware that's not supported by RHEL4, especially if it's not a very recent version of RHEL4. Did you check compatibility charts before purchasing? https://hardware.redhat.com/ regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez [EMAIL PROTECTED] writes: It had been working with pgsql 8.1 and 8.2 for 2 years without problems. Suspicious is that problems started next day I've upgraded to 8.3. Did you update anything else at the same time? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez [EMAIL PROTECTED] writes: Tom Lane wrote: Did you update anything else at the same time? No, just postgres was updated Well, that does start to sound like it could be a PG bug; but no one else is reporting anything like it. Can you put together a self-contained test case? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: Rodrigo Gonzalez [EMAIL PROTECTED] writes: It had been working with pgsql 8.1 and 8.2 for 2 years without problems. Suspicious is that problems started next day I've upgraded to 8.3. Did you update anything else at the same time? regards, tom lane No, just postgres was updated -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: Rodrigo Gonzalez [EMAIL PROTECTED] writes: Tom Lane wrote: Did you update anything else at the same time? No, just postgres was updated Well, that does start to sound like it could be a PG bug; but no one else is reporting anything like it. Can you put together a self-contained test case? regards, tom lane No idea what to do Basically I should reinstall again PG with the same configuration and wait 1 night. Any log you need or want? anything to do besides doing the same I did? Thanks Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: Rodrigo Gonzalez [EMAIL PROTECTED] writes: Craig Ringer wrote: What platform are you using? It's running under CentOS 4.4 using ext3, no RAID or LVM. Server is quad xeon 64 bits 3 GHz Ugh, I'd have liked to think RHEL4/Centos4 would be more reliable than that :-(. Still, you might have an issue with trying to use hardware that's not supported by RHEL4, especially if it's not a very recent version of RHEL4. Did you check compatibility charts before purchasing? https://hardware.redhat.com/ regards, tom lane It had been working with pgsql 8.1 and 8.2 for 2 years without problems. Suspicious is that problems started next day I've upgraded to 8.3. I've tried reinstalling 8.3 from scratch and again, next morning, oid 2836 is missing... Before you ask, nothing strange is running at nights, just a pg_dump from other machine to make a backup Best regards Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez [EMAIL PROTECTED] writes: Basically I should reinstall again PG with the same configuration and wait 1 night. Any log you need or want? anything to do besides doing the same I did? Umm ... if I reinstall PG and wait one night, I'm quite sure that nothing much will happen. You need to show me how to reproduce the problem you're having. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: Rodrigo Gonzalez [EMAIL PROTECTED] writes: Basically I should reinstall again PG with the same configuration and wait 1 night. Any log you need or want? anything to do besides doing the same I did? Umm ... if I reinstall PG and wait one night, I'm quite sure that nothing much will happen. You need to show me how to reproduce the problem you're having. regards, tom lane Dont know exactly what you mean, if you are talking about the moment that I receive the error... # pg_dump -U postgres db pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not open relation with OID 2836 pg_dump: The command was: SELECT proretset, prosrc, probin, proallargtypes, proargmodes, proargnames, provolatile, proisstrict, prosecdef, proconfig, procost, prorows, (SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname FROM pg_catalog.pg_proc WHERE oid = '17018'::pg_catalog.oid Anything else I can do or give you, just tell me and I will try... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez [EMAIL PROTECTED] writes: Dont know exactly what you mean, if you are talking about the moment that I receive the error... No, it's clear that things are already broken before pg_dump started. You need to show us how to get to this state from a fresh database. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
