[GENERAL] H2 database
Hello and best wishes for this new year. I have a question concerning the H2 DB. http://www.h2database.com/html/main.html I've read (on their site) that they got better perfs than PG or MySQL in any case (embedded in a Java application and even as a standalone server). Tests seem a bit "light" with a single thread benchmarking the DB but the results seem anyway interesting. However I would be happy to get experts opinion or advice. Thanks in advance Pascal -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
I took your cue, and have formulated this solution for 8.3.1 :
create or replace function unknown2text(unknown) returns text as
$$ begin return text($1::char); end $$ language plpgsql;
drop cast (unknown as text);
create cast (unknown as text) with function unknown2text( unknown ) as
implicit;
select '' union all select * from (select '' ) as s;
Thanks for your help Pavel.
Best regards,
PS: I was getting the same error as yours (stack depth) in EDB version
8.3.0.12, so I had to use the following code for unknown2text:
return charin( unknownout($1) );
It works for PG 8.3.1 too.
On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule wrote:
> 2009/1/6 Gurjeet Singh :
> > As I mentioned, we cannot change the query, so adding casts to the query
> is
> > not an option. I was looking for something external to the query, like a
> > CREATE CAST command that'd resolve the issue.
>
> I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
> (but I am have old 8.3)
> postgres=# create function unknown2text(unknown) returns text as
> $$select $1::text$$ language sql;
> CREATE FUNCTION
> postgres=# create cast(unknown as text) with function
> unknown2text(unknown) as implicit;
> CREATE CAST
> postgres=# select '' union all select * from (select '' ) as s;
> ERROR: stack depth limit exceeded
> HINT: Increase the configuration parameter "max_stack_depth", after
> ensuring the platform's stack depth limit is adequate.
> CONTEXT: SQL function "unknown2text" during startup
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
>
> It working on 8.4
>
> postgres=# create cast (unknown as text) with inout as implicit;
> CREATE CAST
> postgres=# select '' union all select * from (select '' ) as s;
> ?column?
> --
>
>
> (2 rows)
>
> regards
> Pavel Stehule
>
>
> >
> > Best regards,
>
>
> >
> > On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule
> > wrote:
> >>
> >> Hello
> >>
> >> 2009/1/6 Gurjeet Singh :
> >> > Q1: select '' union all select ''
> >> > Q2: select '' union all select * from (select '' ) as s
> >> >
> >> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
> >> >
> >> > Hi All,
> >> >
> >> > Q1 works just fine, but Q2 fails with:
> >> >
> >> > ERROR: failed to find conversion function from "unknown" to text
> >> >
> >> > Q2 is a generalization of a huge query we are facing, which we
> >> > cannot
> >> > modify. I don't think this is a 'removed-casts' problem generally
> faced
> >> > in
> >> > 8.3, but I may be wrong. Will adding some cast resolve this?
> >>
> >> yes
> >>
> >> postgres=# select '' union all select * from (select ''::text ) as s;
> >> ?column?
> >> --
> >>
> >>
> >> (2 rows)
> >>
> >> regards
> >> Pavel Stehule
> >>
> >> >
> >> > Best regards,
> >> > --
> >> > gurjeet[.sin...@enterprisedb.com
> >> > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
> >> >
> >> > EnterpriseDB http://www.enterprisedb.com
> >> >
> >> > Mail sent from my BlackLaptop device
> >> >
> >
> >
> >
> > --
> > gurjeet[.sin...@enterprisedb.com
> > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh wrote: > I took your cue, and have formulated this solution for 8.3.1 : Is there a good reason you're running against a db version with known bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a version missing over a year of updates is not a best practice. -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe wrote:
> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh
> wrote:
> > I took your cue, and have formulated this solution for 8.3.1 :
>
> Is there a good reason you're running against a db version with known
> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a
> version missing over a year of updates is not a best practice.
>
That's just a development instance that I have kept for long; actual issue
was on EDB 8.3.0.12, which the customer is using. As noted in the PS of
previous mail, the solution that worked for PG 8.3.1 didn't work on EDB
8.3.0.12, so had to come up with a different code for that!
Best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] H2 database
On Tue, Jan 6, 2009 at 1:16 AM, Pascal Cohen wrote: > Hello and best wishes for this new year. > I have a question concerning the H2 DB. > http://www.h2database.com/html/main.html > I've read (on their site) that they got better perfs than PG or MySQL in any > case (embedded in a Java application and even as a standalone server). > Tests seem a bit "light" with a single thread benchmarking the DB but the > results seem anyway interesting. So, in other words, it's not really that interesting. :) How well a db runs with a single thread really doesn't mean a lot unless you're only using it for single user embedded or batch processing. I'd like to see a simple pgbench style (i.e. mixed reads and writes with transactions) benchmark with 5, 10, 50, 100 users, etc... That would tell you something interesting. Since they haven't published a benchmark with > 1 user, I'm willing to bet that the performance with > 1 users is not so good, and gets worse as you add users. http://tweakers.net/reviews/649/8/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-8.html Now that is an interesting benchmark. Notice how MySQL is a good 20-30% faster with one user? More importantly see what it does with many users, and how it behaves as the number of users increases. -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh wrote: > On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe > wrote: >> >> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh >> wrote: >> > I took your cue, and have formulated this solution for 8.3.1 : >> >> Is there a good reason you're running against a db version with known >> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a >> version missing over a year of updates is not a best practice. > > That's just a development instance that I have kept for long; actual issue > was on EDB 8.3.0.12, which the customer is using. As noted in the PS of > previous mail, the solution that worked for PG 8.3.1 didn't work on EDB > 8.3.0.12, so had to come up with a different code for that! Ahh, ok. I was just worried you were ignoring updates. I don't know anything about the numbering scheme for EDB. What does 8.3.0.12 translate to in regular pgsql versions? -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
"Gurjeet Singh" writes: > create cast (unknown as text) with function unknown2text( unknown ) as > implicit; This is a horrendously bad idea; it will bite your *ss sooner or later, probably sooner. 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] Replication on windows
2009/1/6 Tuan Hoang Anh : > Is there any postgres replication support windows (not slony because i want > merge replication) ? I undestand that merge replication alows update in the suscribers. If this correct i think you will need something like bucardo, i think it must work on windows cause it is in perl... > Please help me because I must work with postgreSQL on Windows OS :-( > > Thanks in advance. > Sorry for my English. > > Tuan Hoang Anh > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane wrote:
> "Gurjeet Singh" writes:
> > create cast (unknown as text) with function unknown2text( unknown ) as
> > implicit;
>
> This is a horrendously bad idea; it will bite your *ss sooner or later,
> probably sooner.
>
>regards, tom lane
>
I guessed so, but couldn't figure out exactly how! That's why I have
suggested this as a temp solution until we confirmed this with someone more
knowledgeable.
Can you please let us know how this would be problematic? And can you
suggest a better solution?
Thanks and best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
"Gurjeet Singh" writes: >> This is a horrendously bad idea; it will bite your *ss sooner or later, >> probably sooner. > Can you please let us know how this would be problematic? The point is that it's going to have unknown, untested effects on the default coercion rules, possibly leading to silent changes in the behavior of queries that used to work. If you'd rather retest every one of your other queries than fix this one, then go ahead. 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
[GENERAL] Is there a way to do an exact-match search on this list?
Greetings! I just tried to do a search in the archives of this list for ".Net provider". The search returned results contained "provided" and "providing". Is there a way to make sure that my searches return only messages containing strings that exactly match what I'm looking for? Thank you very much. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Thesaurus for Postgre
Hi, I am looking for is a hierarchical thesaurus not a linguistic one. I found this open source project for mySQL http://tematres.r020.com.ar/index.en.html Does anyone know of sg similar for PostGre? thx Juergen
Re: [GENERAL] Is there a way to do an exact-match search on this list?
On Tue, Jan 06, 2009 at 09:06:43AM -0500, Rob Richardson wrote: > I just tried to do a search in the archives of this list for ".Net > provider". The search returned results contained "provided" and > "providing". Is there a way to make sure that my searches return only > messages containing strings that exactly match what I'm looking for? would google do what you want? http://www.google.com/search?q=".Net+provider"+site:archives.postgresql.org/pgsql-general a few useful variations: ``intext:".net provider" site:archives.postgresql.org'' ``intitle:".net provider" site:archives.postgresql.org'' gleaned from: http://www.googleguide.com/advanced_operators.html Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] autovacuum
Buenas tardes. Cargue el parametro autovacuum_npatime en 86400, o sea cada 24 hs. deberia ejecutarse, sin embargo me encuentro en el log el mensaje WARNING: autovacuum not started because of misconfiguration Alguien tiene idea? Gracias Gustavo -- 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] autovacuum
http://archives.postgresql.org/pgsql-es-ayuda/ OR http://archives.postgresql.org/pgsql-es-fomento/ 2009/1/6 Gustavo Rosso : > Buenas tardes. > Cargue el parametro autovacuum_npatime en 86400, o sea cada 24 hs. deberia > ejecutarse, sin embargo me encuentro en el log el mensaje > WARNING: autovacuum not started because of misconfiguration > Alguien tiene idea? > Gracias > Gustavo > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] remove log header
Hi there, On Windows, I run a script batch file with psql, and I get any log line with the following pattern: psql://:4: NOTICE: I'd like to remove the log header before NOTICE. What should I do ? Thanks, Sabin -- 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] Vacuum problems
Thanks for the advice Scott. I've taken out the vacuum fulls entirely. I've now got a nightly vacuum analyze as well as reindex. I'll probably drop both to every other night. BTW, the database shrunk by 2 gigs just from reindexing last night. I expect I'll see a performance gain from actually doing reindexing since this database has never been reindexed since it was put in production 6 months ago. I've got about 12 tables that get caught by the autoanalyze and about 6 that get caught by autovacuum on a daily basis. I'm not sure how often the autovacuum and autoanalyze runs on those tables. I probably need to up the logging to find out. I'm not worried about making it more aggressive yet. One other problem though... my database has a "-" in the name... when I try to run: psql -U postgres -d rms-prod -c "REINDEX SYSTEM rms-prod" I get this: ERROR: syntax error at or near "-" LINE 1: REINDEX SYSTEM rms-prod The user tables run fine. Should I reindex the system tables also? If so, how do I get around the dash in the db name? Thanks, Scot Kreienkamp La-Z-Boy Inc. skre...@la-z-boy.com 734-242-1444 ext 6379 -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Monday, January 05, 2009 1:37 PM To: Scot Kreienkamp Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Vacuum problems On Mon, Jan 5, 2009 at 10:15 AM, Scot Kreienkamp wrote: > Scott, > > Would the "alter user postgres set statement_timeout=0;" be a permanent > change? I ask because our application is using that for its login to > the database. (No lectures please, I inherited the system that way. I > already read the riot act to our developers about that.) If so I'll > have to set it back after the vacuum is done. Then you can just issue a "set statement_timeout=0" before you run vacuum / vacuum full. The update versus insert ratio isn't as important as how many rows are updated out of the total between each run of vacuum analyze. Vacuum full is definitely NOT a regular, recommended practice. I don't think the docs really say it is. But a few other people have seemed to get the same idea from the docs, so there must be some gray area I'm not seeing when I read them. Given the usage pattern you described earlier, I'd say vacuum full is definitely NOT called for, but regular vacuum should be plenty. The best thing to do is to examine how many dead tuples you've got to keep track of, and if that number keeps rising then figure out if fsm pages needs to be bumped up, and / or autovacuum needs more aggresive settings. Note that autovacuum is kind of hand cuffed on pg versions before 8.3 because it was single threaded, and one really big table could throw it behind on other more frequently updated tables getting bloated while the vacuum thread runs against that one large table. Use vacuum verbose to get an idea of how many dead tuples there are in the database, and see if they rise to a plateu, or just keep rising. For most usage patterns with autovacuum enabled, you'll see a steady rise to about 10-20% dead tuples then it should level off. > FYI, when I inherited the system it was doing nightly vacuum fulls. It > was that way for several months. If that causes bloated indexes, then > that's fairly likely a problem I have. Sounds like I should quit > running vacuum fulls altogether except maybe once or twice per year. A lot of times a pgsql doing nightly fulls is a sign of someone who started out with an old version that only supported full vacuum and applying the faulty knowledge they gained from there to the newer version which likely doesn't need it. If you do find one table that really needs full vacuums because of its usage pattern, it's best to cron up a single vacuum (regular) to run more often on it, or make autovacuum more aggresive, or, failing those two, to make a regular nightly vacuum full / cluster / reindex for that one relation. Usually cluster is a better choice, as it doesn't bloat indexes and puts the table into index order (on the index you clustered on). -- 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] remove log header
On Tue, Jan 6, 2009 at 3:32 PM, Sabin Coanda wrote: > Hi there, > > On Windows, I run a script batch file with psql, and I get any log line with > the following pattern: > >psql://:4: NOTICE: > > I'd like to remove the log header before NOTICE. What should I do ? either write simple program for that, or install gnu utils and treat it with "sed" -- GJ -- 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] H2 database
2009/1/6 Scott Marlowe : > On Tue, Jan 6, 2009 at 1:16 AM, Pascal Cohen wrote: >> Hello and best wishes for this new year. >> I have a question concerning the H2 DB. >> http://www.h2database.com/html/main.html >> I've read (on their site) that they got better perfs than PG or MySQL in any >> case (embedded in a Java application and even as a standalone server). >> Tests seem a bit "light" with a single thread benchmarking the DB but the >> results seem anyway interesting. > I see many benchs that say a *database is better than the most popular (velneo,h2, db4, etc). But if all of the benchs was 100% really the most popular was disapear too many year ago :) > So, in other words, it's not really that interesting. :) How well a > db runs with a single thread really doesn't mean a lot unless you're > only using it for single user embedded or batch processing. I'd like > to see a simple pgbench style (i.e. mixed reads and writes with > transactions) benchmark with 5, 10, 50, 100 users, etc... That would > tell you something interesting. Since they haven't published a > benchmark with > 1 user, I'm willing to bet that the performance with >> 1 users is not so good, and gets worse as you add users. > > http://tweakers.net/reviews/649/8/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-8.html It's really interesting link. In adding, not so only 1 user. What happends if these user make more complex works... i don't know what's the behavior that could get mysql in front postgres or others. > > Now that is an interesting benchmark. Notice how MySQL is a good > 20-30% faster with one user? More importantly see what it does with > many users, and how it behaves as the number of users increases. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- 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] Vacuum problems
you don't have to reindex too often - it locks exclusively whole table, just like vacuum full. Just do it every few months, depending on db growth. -- 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] remove log header
"Sabin Coanda" writes: > On Windows, I run a script batch file with psql, and I get any log line with > the following pattern: > psql://:4: NOTICE: > I'd like to remove the log header before NOTICE. What should I do ? If you don't want line numbers at all, I think you can do psql database http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum problems
- "Scot Kreienkamp" wrote: > Thanks for the advice Scott. I've taken out the vacuum fulls > entirely. > I've now got a nightly vacuum analyze as well as reindex. I'll > probably > drop both to every other night. > > BTW, the database shrunk by 2 gigs just from reindexing last night. > I > expect I'll see a performance gain from actually doing reindexing > since > this database has never been reindexed since it was put in production > 6 > months ago. > > I've got about 12 tables that get caught by the autoanalyze and about > 6 > that get caught by autovacuum on a daily basis. I'm not sure how > often > the autovacuum and autoanalyze runs on those tables. I probably need > to > up the logging to find out. I'm not worried about making it more > aggressive yet. > > One other problem though... my database has a "-" in the name... when > I > try to run: > > psql -U postgres -d rms-prod -c "REINDEX SYSTEM rms-prod" You need to quote the db name: psql -U postgres -d rms-prod -c 'REINDEX SYSTEM "rms-prod"' > > I get this: > > ERROR: syntax error at or near "-" > LINE 1: REINDEX SYSTEM rms-prod > > The user tables run fine. Should I reindex the system tables also? > If > so, how do I get around the dash in the db name? > > Thanks, > > Scot Kreienkamp > La-Z-Boy Inc. > skre...@la-z-boy.com > 734-242-1444 ext 6379 > Adrian Klaver akla...@comcast.net -- 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] Vacuum problems
Yep... dummy me. That works. I tried that before with the reindexdb command, that doesn't work. I didn't try it with the psql command. Thanks, Scot Kreienkamp La-Z-Boy Inc. skre...@la-z-boy.com -Original Message- From: Adrian Klaver [mailto:akla...@comcast.net] Sent: Tuesday, January 06, 2009 12:02 PM To: Scot Kreienkamp Cc: pgsql-general@postgresql.org; Scott Marlowe Subject: Re: [GENERAL] Vacuum problems - "Scot Kreienkamp" wrote: > Thanks for the advice Scott. I've taken out the vacuum fulls > entirely. > I've now got a nightly vacuum analyze as well as reindex. I'll > probably > drop both to every other night. > > BTW, the database shrunk by 2 gigs just from reindexing last night. > I > expect I'll see a performance gain from actually doing reindexing > since > this database has never been reindexed since it was put in production > 6 > months ago. > > I've got about 12 tables that get caught by the autoanalyze and about > 6 > that get caught by autovacuum on a daily basis. I'm not sure how > often > the autovacuum and autoanalyze runs on those tables. I probably need > to > up the logging to find out. I'm not worried about making it more > aggressive yet. > > One other problem though... my database has a "-" in the name... when > I > try to run: > > psql -U postgres -d rms-prod -c "REINDEX SYSTEM rms-prod" You need to quote the db name: psql -U postgres -d rms-prod -c 'REINDEX SYSTEM "rms-prod"' > > I get this: > > ERROR: syntax error at or near "-" > LINE 1: REINDEX SYSTEM rms-prod > > The user tables run fine. Should I reindex the system tables also? > If > so, how do I get around the dash in the db name? > > Thanks, > > Scot Kreienkamp > La-Z-Boy Inc. > skre...@la-z-boy.com > 734-242-1444 ext 6379 > Adrian Klaver akla...@comcast.net -- 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_restore question (-T and -t)
Martin Gainty wrote: > > Tony- > > pgdump version 8.3 will dump multiple tables (with multiple -t) > http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html > > but I dont see the same multiple table functionality with pgrestore > http://www.postgresql.org/docs/8.3/interactive/app-pgrestore.html > > you may have found a bug.. Added to TODO: Add support for multiple pg_restore -t options, like pg_dump -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY ... FROM Permission denied ...
Hi All, This is a bit embarassing ... but ... I have a partial set of data that I want to restore via COPY ... FROM command I have created a public folder for the effect and chown'ed both the folder and the file to be fed into COPY to a+rw ... I switched users with su - postgres and connected to the DB with the psql command All I'm getting is a Permission denied upon issuing the COPY command from within psql interactive terminal! :O So: a) The Christmas *spirits* killed a billion of my brain cells b) I need to go to postgresql kindergarden c) I'm missing something very basic Could someone please lend a hand? Thanks in advance, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Vacuum problems
On Tue, Jan 6, 2009 at 9:05 AM, Grzegorz Jaśkiewicz wrote: > you don't have to reindex too often - it locks exclusively whole > table, just like vacuum full. Just do it every few months, depending > on db growth. While I don't wholly disagree with periodic reindexing, I do recommend that one keeps track of bloat. It's easy enough to have an alarm that goes off if any index gets over 50% dead space, then go look at the database. Or go in every week and see what vacuum verbose looks like. Even if you just email yourself a copy of the last 10 or 15 lines every morning or something to see how bloated the db is in general, you'll catch most problems before they become problems. One or two rogue updates without where clauses on medium to large sized tables can blow you right out of the water. Best to have some way to keep track of them. -- 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] COPY ... FROM Permission denied ...
On Tue, Jan 6, 2009 at 11:41 AM, Pedro Doria Meunier wrote: > Hi All, > > This is a bit embarassing ... but ... > > I have a partial set of data that I want to restore via COPY ... FROM command > > I have created a public folder for the effect and chown'ed both the folder and > the file to be fed into COPY to a+rw ... > > I switched users with su - postgres and connected to the DB with the psql > command > > All I'm getting is a Permission denied upon issuing the COPY command from > within psql interactive terminal! :O What is the exact error you're getting? It's better to usually use copy from stdin which has none of these problems. It's the same syntax tat pg_dump uses when it creates a backup. For example: COPY b (a_i, b) FROM stdin; 1 moreabc \. -- 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] COPY ... FROM Permission denied ...
Pedro Doria Meunier writes: > All I'm getting is a Permission denied upon issuing the COPY command from > within psql interactive terminal! Since you didn't show what you did or what the error was, we're just guessing ... but I'm going to guess that you should use \copy not COPY. The server normally can't read files in your home directory. 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] COPY ... FROM Permission denied ...
Hi Scott Txs for replying. Anyway I've found the problem (silly me... (blush) ) It had to do (of course) with the "forest" perms in the folder tree ... As soon as I moved the file into the data/ folder and executed the COPY ... FROM feeding it the file from that location everything worked as expected. :] Thanks again. Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Tuesday 06 January 2009 06:48:47 pm Scott Marlowe wrote: > On Tue, Jan 6, 2009 at 11:41 AM, Pedro Doria Meunier > > wrote: > > Hi All, > > > > This is a bit embarassing ... but ... > > > > I have a partial set of data that I want to restore via COPY ... FROM > > command > > > > I have created a public folder for the effect and chown'ed both the > > folder and the file to be fed into COPY to a+rw ... > > > > I switched users with su - postgres and connected to the DB with the psql > > command > > > > All I'm getting is a Permission denied upon issuing the COPY command from > > within psql interactive terminal! :O > > What is the exact error you're getting? > > It's better to usually use copy from stdin which has none of these > problems. It's the same syntax tat pg_dump uses when it creates a > backup. > > For example: > > COPY b (a_i, b) FROM stdin; > 1 moreabc > \. signature.asc Description: This is a digitally signed message part.
[GENERAL] Installing the Npgsql provider for .Net
Greetings! I am trying to learn how to use ADO.Net to access a PostGRESQL database through C#, using MS Visual Studio 2008 on a Windows XP Pro box. At first, I was using the PgOldDb provider for .Net, but it seems that that provider is not complete. It did not work for me. I switched to ODBC and was able to perform the update operations I spent more than a day struggling with. I would still like to use OLE DB instead of ODBC, as I understand that it is faster and more compatible with the .Net architecture. So I tried the Npgsql provider. I was dismayed to see that there are no installation instructions on the PGFoundry download page, but a Google search turned up a user's manual. According to that, all I had to do was to put the npgsql.dll file into a known location, and if I wanted IntelliSense to work, I had to put into the GAC as well. So I did. But when I tried to run an application using it, an exception was thrown complaining that it could not find file or assembly Mono.Security. Do I have the wrong file? I loaded the .Net 2.0 version (or so I thought). Do I need another file? Should I use a different provider entirely? Thank you very much. RobR
Re: [GENERAL] Installing the Npgsql provider for .Net
On Tue, Jan 6, 2009 at 5:38 PM, Rob Richardson wrote: > Greetings! > Hi, Rob! You can find the manual in the download file which has a section about how to install and use Npgsql. Also, you can find the user manual online at: http://manual.npgsql.org You will need to add the file Mono.Security.dll into the GAC as Npgsql.dll depends on it. This file is also found in the downloaded file. Maybe you got one for Mono which already contains the Mono.Security.dll assembly. If you read the manual and still have doubts, please let us know. I hope it helps. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://fxjr.blogspot.com http://www.npgsql.org -- 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] Slow Vacuum was: vacuum output question
Here is an interesting new datapoint. Modern Ubuntu distro - PostgreSQL 8.1. SATA drive. No Raid. Cannot reproduce slow vacuum performance - vacuums take less than a second for the whole database. Reinstall OS - Fedora Core 6 - PostgreSQL 8.1. Push data through PostgreSQL for a couple hours (same as above) and now vacuum reports this: INFO: vacuuming "public.cpe" INFO: index "pk_cpe" now contains 50048 row versions in 2328 pages DETAIL: 415925 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.05s/0.33u sec elapsed 0.94 sec. INFO: index "ix_cpe_ispid" now contains 50090 row versions in 1338 pages DETAIL: 415925 index row versions were removed. 953 index pages have been deleted, 0 are currently reusable. CPU 0.27s/0.22u sec elapsed 8.93 sec. INFO: index "ix_cpe_enable" now contains 50676 row versions in 1637 pages DETAIL: 415925 index row versions were removed. 1161 index pages have been deleted, 0 are currently reusable. CPU 0.45s/0.31u sec elapsed 14.01 sec. INFO: "cpe": removed 415925 row versions in 10844 pages DETAIL: CPU 1.48s/0.25u sec elapsed 35.86 sec. INFO: "cpe": found 415925 removable, 50003 nonremovable row versions in 10849 pages DETAIL: 6 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 2.40s/1.18u sec elapsed 61.13 sec. It tooks 61 seconds to vacuum, and the number of index row versions removed was huge. We than issued a reindex command for the entire database - and now the vaccum times are back down under a second. What on earth could be going on between PostgreSQL 8.1 and Fedora 6 that is bloating and/or corrupting the indexes like this? Thanks, Dan -- 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] getting elapsed query times
On Sun, Jan 4, 2009 at 7:01 PM, Craig Ringer wrote: > Alternately, rather than doing everything within PL/PgSQL, just do it from > normal SQL, issued through psql. That way you can just use \timing . > > For simple one-liners, instead of: > > psql -d DB1 -c 'select execute_function_foo();' > > you can write: > > psql -d DB1 <<__END__ > \timing > select execute_function_foo(); > __END__ Or just add \timing to your .psqlrc file for simplicity eg. $ cat ~postgres/.psqlrc \timing -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane wrote:
> "Gurjeet Singh" writes:
> >> This is a horrendously bad idea; it will bite your *ss sooner or later,
> >> probably sooner.
>
> > Can you please let us know how this would be problematic?
>
> The point is that it's going to have unknown, untested effects on the
> default coercion rules, possibly leading to silent changes in the
> behavior of queries that used to work. If you'd rather retest every one
> of your other queries than fix this one, then go ahead.
>
>
Changing the query is an option not given to us. It is being migrated from a
BigDB.
I was working on these solutions assuming that these are workarounds to a
bug. But from your mails, it seems that it is an expected behaviour; is it?
If we consider the second branch of UNION ALL of both the queries above, if
"select '' " yields a text column, then so should a "select * from (select
'')".
Its not exactly a bug, but sure is a problem that we should try to resolve.
Thanks and best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] Slow Vacuum was: vacuum output question
Dan Armbrust escribió: > What on earth could be going on between PostgreSQL 8.1 and Fedora 6 > that is bloating and/or corrupting the indexes like this? Postgres 8.1 was slow to vacuum btree indexes. My guess is that your indexes are so bloated that it takes a lot of time to scan them. I think the solution here is to vacuum this table far more often. -- 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] Generic provider for .Net
My stumbling through the wilds of .Net, ADO.Net and PostGRESQL continues... I left out a critical requirement from my discussion of .Net providers: They must be compatible with some generic data type inside a .Net application. The most that is allowed to change is a connection string. If I use ODBC, I can access the database through OdbcConnection and its related classes. All I have to do to change from PostGRESQL to SQL Server is to change from a DSN that refers to a PostGRESQL database to a DSN that refers to a SQL Server database. When I was trying to get the PgOleDb provider to work, I was using the OleDbConnectioni class and its relatives with this connection string: "Provider=PostgreSQL OLE DB Provider;Data Source=localhost;location=Great_Lakes_10_09;User ID=caps;Password=asdlkjqp" Somewhere in the depths of the registry, "PostgreSQL OLE DB Provider" is associated with the PgOleDb provider. If I want to connect to a SQL Server database, I presume that I would be just change the name of the provider in this string to another name that is associated in the registry with a SQL Server provider. I need a PostGRESQL provider that can be used through an OleDbConnection object just by using a connection string like: "Provider=Some PostgreSQL Provider That Actually Works;Data Source=localhost;location=Great_Lakes_10_09;User ID=caps;Password=asdlkjqp" All of this is because the first thing a user sees when he starts our application is a dialog box in which he selects a database. An ini file contains the list of available names, and each name is associated with a connection string. When the user selects a name from a listbox, the connection string is read from the ini file. So the only thing I can change once the application is built is the connection string. The code has to work for PostGRESQL, SQL Server, and any other reasonably popular database system. We can't have one version of the code built for PostGRESQL and another for SQL Server. Therefore, unless there's some other way of setting it up, I cannot use Npgsql because I cannot use an NpgsqlConnection object. RobR
Re: [GENERAL] Slow Vacuum was: vacuum output question
On Tue, Jan 6, 2009 at 1:39 PM, Dan Armbrust wrote: > Here is an interesting new datapoint. > > Modern Ubuntu distro - PostgreSQL 8.1. SATA drive. No Raid. Cannot > reproduce slow vacuum performance - vacuums take less than a second > for the whole database. > > Reinstall OS - Fedora Core 6 - PostgreSQL 8.1. Push data through > PostgreSQL for a couple hours (same as above) and now vacuum reports > this: Are you pushing the same amount of data through the ubuntu server? if not, then the comparison is invalid, if so, then yeah, there's some kind of difference between the platforms. Note that Fedora Core 6 is quite old compared to ubuntu 8.04 or 8.10. Also it's more likely to be installed on older and / or slower equipment. -- 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] Slow Vacuum was: vacuum output question
On Tue, Jan 6, 2009 at 3:01 PM, Alvaro Herrera wrote: > Dan Armbrust escribió: > >> What on earth could be going on between PostgreSQL 8.1 and Fedora 6 >> that is bloating and/or corrupting the indexes like this? > > Postgres 8.1 was slow to vacuum btree indexes. My guess is that your > indexes are so bloated that it takes a lot of time to scan them. > > I think the solution here is to vacuum this table far more often. > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > Actually, the customer reported problem is that when they enable autovacuum, the performance basically tanks because vacuum runs so slow they can't bear to have it run frequently. Though, perhaps they had bloated indexes before they started autovacuum, and it never fixed them. Perhaps it will behave properly if we do a reindex, and then enable autovacuum. -- 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] Slow Vacuum was: vacuum output question
On Tuesday 06 January 2009, "Dan Armbrust" wrote: > What on earth could be going on between PostgreSQL 8.1 and Fedora 6 > that is bloating and/or corrupting the indexes like this? Obviously the choice of operating system has no impact on the contents of your index. A better question might be, what did your application or maintenance procedures do different in the different tests? -- Alan -- 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] Slow Vacuum was: vacuum output question
> On Tue, Jan 6, 2009 at 1:39 PM, Dan Armbrust > wrote: >> Here is an interesting new datapoint. >> >> Modern Ubuntu distro - PostgreSQL 8.1. SATA drive. No Raid. Cannot >> reproduce slow vacuum performance - vacuums take less than a second >> for the whole database. >> >> Reinstall OS - Fedora Core 6 - PostgreSQL 8.1. Push data through >> PostgreSQL for a couple hours (same as above) and now vacuum reports >> this: > > Are you pushing the same amount of data through the ubuntu server? if > not, then the comparison is invalid, if so, then yeah, there's some > kind of difference between the platforms. > > Note that Fedora Core 6 is quite old compared to ubuntu 8.04 or 8.10. > Also it's more likely to be installed on older and / or slower > equipment. > Yep - actually, we pushed much more data through the Ubuntu system and could never reproduce the problem. On the Fedora Core 6 system, the problem happened very quickly. In our testing here, the Ubuntu test was on the same hardware as the fedora core 6 system (not just identical, but the same actual box) It seems that there is some sort of bad interaction between some part of the older OS and PostgreSQL. We have also seen what appears to be the same issue on a Cent OS 4.4 system. Which is a rather similar package level to Fedora Core 6. -- 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] Slow Vacuum was: vacuum output question
> > Obviously the choice of operating system has no impact on the contents of > your index. > > A better question might be, what did your application or maintenance > procedures do different in the different tests? > > > -- > Alan Our problem for a long time has been assuming the "obvious". But we now have tests that show otherwise. I'm now thinking something along the lines of an obscure file system or kernel interaction bug now - that was perhaps corrected in newer releases of the OS. Now that we can finally reproduce the problem in house, we are still doing more tests to figure out specifics - does the problem go away with Postgres 8.3, ext2/ext3/reiserFS, etc. -- 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] Generic provider for .Net
On Tue, Jan 6, 2009 at 7:00 PM, Rob Richardson wrote: > My stumbling through the wilds of .Net, ADO.Net and PostGRESQL continues... > With this requirement, I would suggest you to use dbproviderfactory support of .Net This is a sample link with informations about it: http://msdn.microsoft.com/en-us/library/dd0w4a2z%28VS.80%29.aspx http://www.davidhayden.com/blog/dave/archive/2007/10/08/CreatingDataAccessLayerUsingDbProviderFactoriesDbProviderFactory.aspx A little googling can provide you with more examples. Also, on Npgsql manual there is the entry you have to make to register Npgsql as a dbproviderfactory. Also note that Npgsql supports sqlclient parameter style with the '@' prefix to easy porting sql code from sqlserver to postgresql. I hope it helps. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://fxjr.blogspot.com http://www.npgsql.org -- 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] Slow Vacuum was: vacuum output question
On Tue, Jan 6, 2009 at 2:07 PM, Dan Armbrust wrote: > > Actually, the customer reported problem is that when they enable > autovacuum, the performance basically tanks because vacuum runs so > slow they can't bear to have it run frequently. Actually this is kinda backwards. What's happening is that the vacuum uses up so much IO that nothing else can get through. The answer is to make it run slower, by use of autovacuum_vacuum_cost_delay, and setting it to 10 or 20 and seeing if they can then run autovacuum during the day without these issues. Note that vacuum was improved a fair bit from 8.1 to 8.2 and even moreso from 8.2 to 8.3. > Though, perhaps they had bloated indexes before they started > autovacuum, and it never fixed them. Perhaps it will behave properly > if we do a reindex, and then enable autovacuum. Definitely look at the cost_delay setting. Makes a huge difference. -- 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] Slow Vacuum was: vacuum output question
"Dan Armbrust" writes: > INFO: "cpe": found 415925 removable, 50003 nonremovable row versions > in 10849 pages > What on earth could be going on between PostgreSQL 8.1 and Fedora 6 > that is bloating and/or corrupting the indexes like this? You're focusing on the indexes when the problem is dead table rows. It's very hard to believe that there's any OS dependence as such involved in that. I wonder whether (a) the Ubuntu and Fedora packages you're using are the same 8.1.x point release; (b) if there's any interesting non-default behavior built into the Ubuntu packaging ... like running autovacuum automatically, for instance. 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] Slow Vacuum was: vacuum output question
On Tue, Jan 6, 2009 at 3:36 PM, Tom Lane wrote: > "Dan Armbrust" writes: >> INFO: "cpe": found 415925 removable, 50003 nonremovable row versions >> in 10849 pages > >> What on earth could be going on between PostgreSQL 8.1 and Fedora 6 >> that is bloating and/or corrupting the indexes like this? > > You're focusing on the indexes when the problem is dead table rows. > > It's very hard to believe that there's any OS dependence as such > involved in that. I wonder whether (a) the Ubuntu and Fedora packages > you're using are the same 8.1.x point release; (b) if there's any > interesting non-default behavior built into the Ubuntu packaging > ... like running autovacuum automatically, for instance. > >regards, tom lane > In our testing, Postgres 8.1 was build from source (PostgreSQL website source) on both systems. No Distro packages involved. Believe me, we are as baffled as you. We have been chasing this bug off and on for months on a couple of different customer sites now. Thanks, Dan -- 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] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 06, 2009 at 11:13:59PM +0530, Gurjeet Singh wrote: > If we consider the second branch of UNION ALL of both the queries above, if > "select '' " yields a text column, then so should a "select * from (select > '')". The problem is ofcourse that "select ''" doesn't produce a text column in postgres. This generally works fine, except in the case of UNION where none of the branches provide the necessary type info. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Vacuum problems
Scott Marlowe wrote: > While I don't wholly disagree with periodic reindexing, I do recommend > that one keeps track of bloat. It's easy enough to have an alarm that > goes off if any index gets over 50% dead space, then go look at the > database. Reading this list, I've noticed that: - Many admins don't understand vacuum vs vacuum full at all, and are unaware of the probable need for a reindex after vacuum full. They're often landing up with very bloated indexes from excessive use of vacuum full, or very bloated tables due to insufficient fsm space / infrequent vacuuming. - It's hard to spot table and (especially) index bloat. Pg doesn't warn about bloated tables or indexes in any way that people seem to notice, nor are there any built-in views or functions that might help the admin identify problem tables and indexes. - Most people have a lot of trouble understanding where and how their storage is being used. I'm wondering if it might be a good idea to adopt one of the handy views people have written for getting table/index bloat information as a standard part of Pg (probably as an SQL function rather than a view) so people can just "SELECT pg_bloat()" to get a useful summary of table/index status. The other thing I wonder about is having EXPLAIN and EXPLAIN ANALYZE report information on the amount of free space that a table seq scan or an index scan is having to traverse. That might bring problems to the attention of people who're investigating query performance issues without being aware that the underlying issue is actually bloated indexes, not bad plans. -- 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] COPY ... FROM Permission denied ...
Pedro Doria Meunier wrote:
> I have created a public folder for the effect and chown'ed both the folder
> and
> the file to be fed into COPY to a+rw ...
The server user (usually via the "group" or "other" permissions blocks)
must also have at least execute ('x') permissions on every directory
between the root directory (/) and the directory containing the files of
interest.
> All I'm getting is a Permission denied upon issuing the COPY command from
> within psql interactive terminal! :O
Do you have SELinux on your system? It may be denying the server access
even though the plain UNIX permissions would otherwise allow it, because
the server normally has no good reason to be accessing files from
wherever you've put them.
--
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
