Re: [GENERAL] problem with pg_restore?
Jim Michaels, 15.07.2009 01:47: most programmer's text editors can't handle more than 2000 characters per line. and I want to be able to edit my dumps. I don't know what you are using, but the following editors can certainly handle more than 2000 characters: OpenSource and free: http://www.pnotepad.org http://www.pspad.com http://notepad-plus.sourceforge.net Commercial (but not expensive) http://www.textpad.com I have edited files with more than 1 characters per line in PNotepad, PSPad and Textpad Thomas -- 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] cache lookup failed for function 72629
I'm not exactly sure what was wrong. But I tried making a change on another database on the same server and it was ok. So I thought deleting the problem database and restoring it again would do the trick and it did. So problem solved I guess. Although I'm still not sure what happened . . . LW . . . Lawrence From: lawrence...@hotmail.com To: t...@sss.pgh.pa.us CC: mmonc...@gmail.com; pgsql-general@postgresql.org Subject: RE: [GENERAL] cache lookup failed for function 72629 Date: Tue, 14 Jul 2009 14:33:19 + I guess I'll ask on the slony-i mailing list also. thanks for your hlep. LW . . . Lawrence > To: lawrence...@hotmail.com > CC: mmonc...@gmail.com; pgsql-general@postgresql.org > Subject: Re: [GENERAL] cache lookup failed for function 72629 > Date: Tue, 14 Jul 2009 10:18:10 -0400 > From: t...@sss.pgh.pa.us > > Lawrence Wong writes: > > I had been using Slony-I together with another database on a server on a > > different machine. I had been testing my replication constantly dropping > > and creating my Slony-I tables. This is not out of the ordinary though. I > > had been doing this for many days now. > > Hmm, try asking about it on the Slony mailing lists. Slony is known to > cause strange errors if you do something it's not expecting. I suspect > that the missing function is actually a Slony trigger function, in which > case you could probably clean up by dropping the trigger --- but get > some help first, or you may mess up Slony even further. > > regards, tom lane Internet Explorer 8 makes surfing easier. Get it now! _ Attention all humans. We are your photos. Free us. http://go.microsoft.com/?linkid=9666046
Re: [GENERAL] problem with pg_restore?
Jim Michaels wrote: I am having problems with pg_restore. pg_restore --file=c:\pg-jmichae3-7-13-2009.sql --verbose --host=localhost --port=5432 --username=postgres this just hangs. I am restoring from 8.3.7 to 8.4 - what did I do wrong? Well, I don't see a database name, was that just an accident when you cut + pasted the line? If so, do you see any activity at all? If you turn connection logging on at the server, does it see any connection attempts? could somebody rewrite pg_dumpall and pg_dump so that it makes editable dumps? most programmer's text editors can't handle more than 2000 characters per line. and I want to be able to edit my dumps. Might want to get a better editor. Just tried vi with 1000 character lines and it's perfectly happy. Can't imagine a proper editor complaining. Having said that, once your file gets into the gigabytes you'll want more specialised tools (either an on-disk editor or sed/perl). -- Richard Huxton Archonet Ltd -- 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] problem with pg_restore?
On Tue, Jul 14, 2009 at 7:47 PM, Jim Michaels wrote: > could somebody rewrite pg_dumpall and pg_dump so that it makes editable > dumps? > most programmer's text editors can't handle more than 2000 characters per > line. > and I want to be able to edit my dumps. You're going to have better luck finding a decent editor than finding someone to rewrite pg_dump and pg_dumpall just for you. -- - David T. Wilson david.t.wil...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with pg_restore?
I am having problems with pg_restore. pg_restore --file=c:\pg-jmichae3-7-13-2009.sql --verbose --host=localhost --port=5432 --username=postgres this just hangs. I am restoring from 8.3.7 to 8.4 - what did I do wrong? could somebody rewrite pg_dumpall and pg_dump so that it makes editable dumps? most programmer's text editors can't handle more than 2000 characters per line. and I want to be able to edit my dumps. Jim Michaels jmich...@yahoo.com http://JesusnJim.com
Re: [GENERAL] Custom Class variables
Michael Gould wrote:
> I have created the following in my postgres.conf file
>
> custom_variable_classes = 'iss'
>
> In a SQL session I've tried
>
> Set iss.one = '1'
> set iss.two = '2'
>
> Select * from iss;
>
> How do I access the values from the custom class in sql code?
show iss.one;
select current_setting('iss.one');
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Python debugging - line numbers
Hi, Debugging PL/Python isn't as easy as with PL/pgSQL, as it appears I can't see line numbers where errors occur. For example: ERROR: plpython: function "evaluate_something" failed DETAIL: : sequence index must be integer, not 'str' The location of this type of exception is difficult to spot out in 366 lines of code. Is there a way to report where the error occurred? I'm using 8.3.7 via apt-get on Ubuntu Hardy LTS. I see the same message on the server using pgsql and in pgAdmin III on a different machine. I've tried using such options as "set client_min_messages to debug;" but this doesn't change the details of the error. Thanks, -Mike -- 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] Request for features
On Tue, Jul 14, 2009 at 10:00:04AM +, Jasen Betts wrote: > On 2009-07-13, Michael Gould wrote: > > I would like to know what the formal method of requesting new features are > > for Postgres and how are decisions made which features are included in a > > future release or not. > > Formal? > > for open soure software in general, changes are are requested using > the bug reporting system. Not in PostgreSQL, generally. > Change requests accompanied with working source patches have an > advantage. Not always. It's much better to discuss the design of the feature on -hackers, come to some rough consensus, *then* write some code than to come up with some huge wonk of code which no one is interested in understanding and will languish. > Grants of cash to the developers tend to help too. There are several outfits that can help you organize your donations. Software in the Public Interest, an non-profit company in the US, has a way to earmark donations for the PostgreSQL project. Other ways to contribute resources include hardware and/or people available to the project. Being a pleasant, helpful part of the community will incline everyone else favorably toward setting your ideas at a higher priority, even if some of them don't fly :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Custom Class variables
I have created the following in my postgres.conf file custom_variable_classes = 'iss' In a SQL session I've tried Set iss.one = '1' set iss.two = '2' Select * from iss; How do I access the values from the custom class in sql code? Best Regards Michael Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] [Q] single image Table across multiple PG servers
On Tuesday 14 July 2009 14:16:33 you wrote: > This is great, > thank you > > I have to say, if this is a free-of-charge > add on to postgres that works and utilizes the hardware on each server > to perform > the join, > > -- why would anybody need to use mapreduce/hadoop/etc? > for database-like selects ? It's new - the grid project just moved out of beta i believe. However I know of at least one commercial company using it in production with no issues > > > > > > > > On Tue, 14 Jul 2009 09:45 -0600, "Kevin Kempter" > > wrote: > > > But do you know if supports viewes that combines tables > > > from different servers (this way > > > I can prefix a table on each server with server_id and then > > > just combine them in the view in a single Image table) > > > > no need for this, the system presents the clients (via the controller) a > > single table - even though its actually partitioned across nodes > > > > > > > > It's quite similar to the Informix XPS product if that helps > > -- > Vlad P > author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- 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] [Q] single image Table across multiple PG servers
On Tue, Jul 14, 2009 at 12:38 PM, V S P wrote: > Hi, > thank you for the links > > > I read through the presentation > > and they did not solve the issue for me -- which presenting a e table from > multiple > shards as one single table (at least for reads) for ODBC clients. > > > I also do not think that skypetools do that > > they have implemented essentially an API on top of their shards (separate db > servers) > that does the table querying. That means that I have to write a separate > API for every time > a user decides on a new query. Just build a view that encloses a plproxy function. -- 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] [Q] single image Table across multiple PG servers
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > The reason why I think the data will not fit into one database, > is because I just do not have money for servers (everything is coming > out of my small pocket) so I just want to deploy inexpensive computers > but add them as I get more data to serve. I think you might be suffering from premature optimization. Or just wildly optimistic on your hardware needs. Postgres scales vertically extremely well, so I'd try out a single server and add sharding complexity only as a last resort. For scaling, you can use pgbouncer, spread the tables and indexes across different tablespaces, and other tricks. If money is tight, you might look into using something like EC2. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200907141552 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkpc4ioACgkQvJuQZxSWSsga/gCfQUp+AHnX1myAO6hYpnmIMk+8 5ZIAoKAOsJepWnavWwVMkdb2h4eOfYt5 =En0J -END PGP SIGNATURE- -- 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] [Q] single image Table across multiple PG servers
On Tue, Jul 14, 2009 at 1:16 AM, V S P wrote: > Hello > > I am researching how to store the data for easy 'user-driven' > reporting (where I do not need to develop application for > every user request). > > The data will typically be number ranges and text strings with > unique Id for each row > > I hope there will be a lot of data :-). > > So in that anticipation I am looking for a way > to allow > SQL/ODBC access to the data > > but in a way that each table resides on more than one > PG server Take a look at open-source GridSQL:http://sourceforge.net/projects/gridsql/ It'll do what you want and provide parallel query across your nodes. --Scott
Re: [GENERAL] [Q] single image Table across multiple PG servers
On Tuesday 14 July 2009 13:35:23 you wrote: > yes -- thank you > > that's in the direction of what I am looking for > > > ODBC connectivity and joins across databases! > > > It looks like it cannot support > a single image table across databases It's effectively a single image across nodes - in that the grid allows you to partition a table across nodes (i.e. physical servers) so any sql see's it as a single table so long as you send the sql to the grid controller > > But do you know if supports viewes that combines tables > from different servers (this way > I can prefix a table on each server with server_id and then > just combine them in the view in a single Image table) > no need for this, the system presents the clients (via the controller) a single table - even though its actually partitioned across nodes It's quite similar to the Informix XPS product if that helps > > I am downloading it now and will try out on my windows dev > machine. > > > thanks > > > Maybe this is what you're looking for? > > > > http://www.enterprisedb.com/community/projects/gridsql.do > > > > > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > -- > Vlad P > author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- 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] [Q] single image Table across multiple PG servers
On Tuesday 14 July 2009 12:38:27 V S P wrote: > Hi, > thank you for the links > > > I read through the presentation > > and they did not solve the issue for me -- which presenting a e > table from multiple > shards as one single table (at least for reads) for ODBC clients. > > > I also do not think that skypetools do that > > they have implemented essentially an API on top of their shards > (separate db servers) > that does the table querying. That means that I have to write a > separate API for every time > a user decides on a new query. > > > May be I misunderstood the approaches, but none of them actually > figures out how to > utilize the computing power/memory of multiple servers to satisfy > requests that spawn across > servers. > > I think Oracle supports the ability to at least reference a table > in another server, I do not think > PG does that > > It is possible that I have to look into the free DB2 server > offering (as the free version is exactly meant > to run on underpowered computers) > > I just wanted to ask the list first. > > > thank you > > > On Tue, 14 Jul 2009 13:04 +0530, "Ransika de Silva" > wrote: > > Hello, > > > > We were also in search of having a table split across multiple > databases but then found out about skypetools and at the same > time the following > article; [1]http://www.jurriaanpersyn.com/archives/2009/02/12/dat > abase-sharding-at-netlog-with-mysql-and-php/, true that it's not > done with PG, but the same thing can be done with PG as well. > > > > Assume this will be helpful for you. > > > > Regards, > > Ransika > > On Tue, Jul 14, 2009 at 4:20 PM, Scott Marlowe > <[2]scott.marl...@gmail.com> wrote: > > On Mon, Jul 13, 2009 at 11:16 PM, V S P<[3]torea...@fastmail.fm> > > wrote: > > Hello > > > > I am researching how to store the data for easy 'user-driven' > > reporting (where I do not need to develop application for > > every user request). > > > > The data will typically be number ranges and text strings with > > unique Id for each row > > > > I hope there will be a lot of data :-). > > > > So in that anticipation I am looking for a way > > to allow > > SQL/ODBC access to the data > > > > but in a way that each table resides on more than one > > PG server > > > > for example: > > > > table 1 lives in 3 PG instances (I can partition the data by > > date range) > > > table 2 lives in the same 3 instances plus another one (because > > it's > > > bigger) > > > > > > and I would like users to be able to issue SQL from within ODBC > > that > > > joins them. > > I think that skype's skytools could be used to create such a > solution, > in particular pl/proxy. > > > -- > Sent via pgsql-general mailing list > ([4]pgsql-gene...@postgresql.org) > To make changes to your subscription: > [5]http://www.postgresql.org/mailpref/pgsql-general > > -- > Ransika De Silva > SCMAD 1.0, SCJP 1.4, > BSc.(Hons) Information Systems > > References > > 1. > http://www.jurriaanpersyn.com/archives/2009/02/12/database-sharding-at-netl >og-with-mysql-and-php/ 2. mailto:scott.marl...@gmail.com > 3. mailto:torea...@fastmail.fm > 4. mailto:pgsql-general@postgresql.org > 5. http://www.postgresql.org/mailpref/pgsql-general > -- > Vlad P > author of C++ ORM http://github.com/vladp/CppOrm/tree/master Maybe this is what you're looking for? http://www.enterprisedb.com/community/projects/gridsql.do -- 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] [Q] single image Table across multiple PG servers
Hi, thank you for the links I read through the presentation and they did not solve the issue for me -- which presenting a e table from multiple shards as one single table (at least for reads) for ODBC clients. I also do not think that skypetools do that they have implemented essentially an API on top of their shards (separate db servers) that does the table querying. That means that I have to write a separate API for every time a user decides on a new query. May be I misunderstood the approaches, but none of them actually figures out how to utilize the computing power/memory of multiple servers to satisfy requests that spawn across servers. I think Oracle supports the ability to at least reference a table in another server, I do not think PG does that It is possible that I have to look into the free DB2 server offering (as the free version is exactly meant to run on underpowered computers) I just wanted to ask the list first. thank you On Tue, 14 Jul 2009 13:04 +0530, "Ransika de Silva" wrote: Hello, We were also in search of having a table split across multiple databases but then found out about skypetools and at the same time the following article; [1]http://www.jurriaanpersyn.com/archives/2009/02/12/dat abase-sharding-at-netlog-with-mysql-and-php/, true that it's not done with PG, but the same thing can be done with PG as well. Assume this will be helpful for you. Regards, Ransika On Tue, Jul 14, 2009 at 4:20 PM, Scott Marlowe <[2]scott.marl...@gmail.com> wrote: On Mon, Jul 13, 2009 at 11:16 PM, V S P<[3]torea...@fastmail.fm> wrote: > Hello > > I am researching how to store the data for easy 'user-driven' > reporting (where I do not need to develop application for > every user request). > > The data will typically be number ranges and text strings with > unique Id for each row > > I hope there will be a lot of data :-). > > So in that anticipation I am looking for a way > to allow > SQL/ODBC access to the data > > but in a way that each table resides on more than one > PG server > > for example: > > table 1 lives in 3 PG instances (I can partition the data by date range) > table 2 lives in the same 3 instances plus another one (because it's > bigger) > > > and I would like users to be able to issue SQL from within ODBC that > joins them. I think that skype's skytools could be used to create such a solution, in particular pl/proxy. -- Sent via pgsql-general mailing list ([4]pgsql-gene...@postgresql.org) To make changes to your subscription: [5]http://www.postgresql.org/mailpref/pgsql-general -- Ransika De Silva SCMAD 1.0, SCJP 1.4, BSc.(Hons) Information Systems References 1. http://www.jurriaanpersyn.com/archives/2009/02/12/database-sharding-at-netlog-with-mysql-and-php/ 2. mailto:scott.marl...@gmail.com 3. mailto:torea...@fastmail.fm 4. mailto:pgsql-general@postgresql.org 5. http://www.postgresql.org/mailpref/pgsql-general -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - The professional email service
Re: [GENERAL] filter duplicates by priority
On Tue, Jul 14, 2009 at 10:04:12AM -0400, Clark Slater wrote: > SELECT DISTINCT ON (part_number) * FROM ( > SELECT part_number, priority FROM ... > UNION ALL > SELECT part_number, priority FROM ... > UNION ALL > SELECT part_number, priority FROM ... > ) AS filter_duplicates ORDER BY priority,part_number > > The above statement does not work because if I ORDER BY > priority,part_number then I have to DISTINCT ON (priority,part_number). > But DISTINCT ON (priority, part_number) does not remove the repeated rows > because the same part_number with a different priority becomes a distinct > tuple. I think you just want to swap the ORDER BY columns around; i.e: ORDER BY part_number, priority -- Sam http://samason.me.uk/ -- 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] filter duplicates by priority
On Tue, Jul 14, 2009 at 10:04:12AM -0400, Clark Slater wrote: > Hello- > > I am trying to use DISTINCT ON to filter out *potential* duplicate values > from a set of sub queries. There are certain cases where there can be > repetitive part numbers that are priced differently. I'm trying to start > with the full list, ordered by priority, and then remove any repeats that > have a lesser priority. I think what you need to do is order by part_number first, do the DISTINCT ON () and then do an ORDER BY priority around that. Hope this helps, -- 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] filter duplicates by priority
"Clark Slater" writes: > I am trying to use DISTINCT ON to filter out *potential* duplicate values > from a set of sub queries. There are certain cases where there can be > repetitive part numbers that are priced differently. I'm trying to start > with the full list, ordered by priority, and then remove any repeats that > have a lesser priority. > SELECT DISTINCT ON (part_number) * FROM ( > SELECT part_number, priority FROM ... > UNION ALL > SELECT part_number, priority FROM ... > UNION ALL > SELECT part_number, priority FROM ... > ) AS filter_duplicates ORDER BY priority,part_number > The above statement does not work because if I ORDER BY > priority,part_number then I have to DISTINCT ON (priority,part_number). > But DISTINCT ON (priority, part_number) does not remove the repeated rows > because the same part_number with a different priority becomes a distinct > tuple. AFAICS, changing it to ORDER BY part_number,priority would solve the stated problem. If you really need the final result in priority rather than part number order, put the whole thing in a sub-select and re-sort outside it. 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] filter duplicates by priority
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Clark Slater > Sent: Tuesday, July 14, 2009 10:04 AM > Subject: [GENERAL] filter duplicates by priority Maybe I'm missing something, but why not something like (incoming pseudo-SQL): Select part_number, max(priority) From( Select part_number, priority from TableAndCriteria Union all Select part_number, priority from TableAndCriteria Union all Select part_number, priority from TableAndCriteria ) as allTables Group by part_number Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital .now. -- 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] prepared statements and DBD::Pg
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> It's perfectly valid (from the DBI's point of view) for prepare() to
> return a prepared statement handle for an invalid statement.
>
> It's not the job of prepare() to validate the SQL. It's a bonus if it
> does, but the primary goal is "to prepare as much as possible" for
> future execution.
>
> There are *many* DBI drivers that can't/don't validate the SQL on
> prepare. DBD::Oracle, for example, can but doesn't by default.
> It defers the prepare until the first execute (or meta data is
> requested) in order to reduce the number of round-trips.
Just to set the record straight on this thread, DBD::Pg also defers
actual preparation until needed (e.g. the first execute) and thus
will accept all prepare statements, regardless of what is inside
of them. It does this not only to save trips, but because bind_param
may be called betwixt the prepare and the execute.
This works fine with DBD::Pg:
$sth = $dbh->prepare('foobar');
However, this will then fail:
$sth->execute();
Thus, as pointed out elsewhere, the original report was not correct:
the error comes when execute() is called, not when prepare() is.
> p.s. I'd be happy to see 'success with info' status returned if the
> prepare() has to unexpectly fallback to client-side (and perhaps a dbh
> counter incremeted). So users can tell when and how often it's happening
> if they want to.
For DBD::Pg, this is not possible as prepare always works. Even if it didn't,
I think the fallback idea is not a good one, as we'd be potentially creating
many errors to have to rollback to (and creating a savepoint for every execute
attempt), and silently encouraging SQL that will fail when fed to Postgres
through any other interface.
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200907141125
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAkpcpGMACgkQvJuQZxSWSsjr1ACgjLWM4EurT9V+wXLXO83A+7pE
BPYAoKfYCl+6ywnOoQW4OOlKEP1YID0D
=QmVQ
-END PGP SIGNATURE-
--
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 of a big table
On Mon, Jul 13, 2009 at 3:29 PM, Sam Mason wrote: > On Mon, Jul 13, 2009 at 06:57:43PM +, Nelson Correia wrote: > > Running pg_dump from another machine needs much space on the DB > > host? Or it just outputs the data as it goes? > > pg_dump should use very little space on the server, it just streams it > out to where ever you tell it. You could run pg_dump on another host, > or do something like: > > pg_dump mydb | gzip | ssh otherbox "cat > out.sql.gz" Yes, from the other machine, run: [u...@notmyhost ]$ pg_dump -h myhost -U mydbuser mydb > out.sql --Scott
Re: [GENERAL] Best practices for moving UTF8 databases
On Tuesday 14 July 2009 15:52:29 Alvaro Herrera wrote: > Andres Freund wrote: > > On Tuesday 14 July 2009 11:36:57 Jasen Betts wrote: > > > if you do an ascii dump and the dump starts out "SET CLIENT ENCODING > > > 'UTF8'" or similar but you still get errors. > > > > Do you mean that a dump from SQL_ASCII can yield non-utf8 data? right. > > But According to the OP his 8.3 database is UTF8... > > So there should not be invalid data in there. > I haven't followed this thread, but older PG versions had less strict > checks on UTF8 data, which meant that some invalid data could creep in. But that was from 8.2 -> 8.3 and not 8.3->8.4 I think? Andres -- 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] cache lookup failed for function 72629
I guess I'll ask on the slony-i mailing list also. thanks for your hlep. LW . . . Lawrence > To: lawrence...@hotmail.com > CC: mmonc...@gmail.com; pgsql-general@postgresql.org > Subject: Re: [GENERAL] cache lookup failed for function 72629 > Date: Tue, 14 Jul 2009 10:18:10 -0400 > From: t...@sss.pgh.pa.us > > Lawrence Wong writes: > > I had been using Slony-I together with another database on a server on a > > different machine. I had been testing my replication constantly dropping > > and creating my Slony-I tables. This is not out of the ordinary though. I > > had been doing this for many days now. > > Hmm, try asking about it on the Slony mailing lists. Slony is known to > cause strange errors if you do something it's not expecting. I suspect > that the missing function is actually a Slony trigger function, in which > case you could probably clean up by dropping the trigger --- but get > some help first, or you may mess up Slony even further. > > regards, tom lane _ Internet explorer 8 lets you browse the web faster. http://go.microsoft.com/?linkid=9655582
Re: [GENERAL] cache lookup failed for function 72629
This sounds like a problem I have observed, which I was able to fix by restarting the Slony daemon. - Dean 2009/7/14 Lawrence Wong : > I had been using Slony-I together with another database on a server on a > different machine. I had been testing my replication constantly dropping > and creating my Slony-I tables. This is not out of the ordinary though. I > had been doing this for many days now. > > The query, I had been doing is just an update or insert into any table in > this database. There are about 40 tables. Any change to the tables in the > database results in this error. Like I said, I had restarted the PostgreSQL > service several times as well as restarted my Server several times to no > avail. I am pretty stumped and am not sure what to do short of a complete > uninstall and reinstall of PostgreSQL. Although I am not sure that would > work at this point. Probably because I'm still not sure what this error > means. Once again, like I said, it is just a simple update or insert into > my tables. I had been doing this function just a couple minutes earlier to > this message and did not do anything out of the ordinary in the time > between. > >> Date: Tue, 14 Jul 2009 09:16:05 -0400 >> Subject: Re: [GENERAL] cache lookup failed for function 72629 >> From: mmonc...@gmail.com >> To: lawrence...@hotmail.com >> CC: pgsql-general@postgresql.org >> >> On Tue, Jul 14, 2009 at 9:12 AM, Lawrence Wong >> wrote: >> > I'm sorry. What does 're-applying the function source' mean? and how >> > would >> > I do it? >> > >> > For the 'change to the table' I was talking about, I just tried to do a >> > regular update on the table using pgAdmin III. >> > >> > LW . . . Lawrence >> > >> >> well, the idea is to find the function that is causing the problem, >> make a change, and save it back with pgadmin. still, this shouldn't >> be happening, and a database restart should certainly have fixed it. >> do we have any context for this error? do you know the query that is >> causing it? >> >> merlin >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > We are your photos. Share us now with Windows Live Photos. -- 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] cache lookup failed for function 72629
Lawrence Wong writes: > I had been using Slony-I together with another database on a server on a > different machine. I had been testing my replication constantly dropping and > creating my Slony-I tables. This is not out of the ordinary though. I had > been doing this for many days now. Hmm, try asking about it on the Slony mailing lists. Slony is known to cause strange errors if you do something it's not expecting. I suspect that the missing function is actually a Slony trigger function, in which case you could probably clean up by dropping the trigger --- but get some help first, or you may mess up Slony even further. 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] Checkpoint Tuning Question
> > Propose a DTrace probe immediately after the "goto begin" at line 740 of > xlog.c, so we can start tracing from the first backend following > checkpoint, and turn off tracing when all backends have completed a > transaction. > That's greek to me. But I'm happy to test things if you send me patches or custom code. 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
[GENERAL] filter duplicates by priority
Hello- I am trying to use DISTINCT ON to filter out *potential* duplicate values from a set of sub queries. There are certain cases where there can be repetitive part numbers that are priced differently. I'm trying to start with the full list, ordered by priority, and then remove any repeats that have a lesser priority. SELECT DISTINCT ON (part_number) * FROM ( SELECT part_number, priority FROM ... UNION ALL SELECT part_number, priority FROM ... UNION ALL SELECT part_number, priority FROM ... ) AS filter_duplicates ORDER BY priority,part_number The above statement does not work because if I ORDER BY priority,part_number then I have to DISTINCT ON (priority,part_number). But DISTINCT ON (priority, part_number) does not remove the repeated rows because the same part_number with a different priority becomes a distinct tuple. Any suggestions are appreciated. -- A more detailed explanation of my problem follows: I am working on an e-commerce system that has different lists of products which contain many of the same products, at different prices. When a user searches for a certain set of part numbers, I would like the resulting products (and prices) to come from one of the lists, according to the list's priority. Each user can have a different set of lists and priorities. Table: product_lists id | name | priority | user_id -+--+--+-- 5 | General List of Products| 2| 23 3 | Different List of Products | 3| 23 150 | Customer-Specific Products | 1| 23 Table: products product_list_id | part_number | price -+-+ 3| 92298A| 123.38 5| 92298A| 111.04 3| C39207| 78.38 150 | C39207| 67.93 Below is a simplified example of the structure of the query I am working with. I realize that in this case, I could re-factor all of this into one statement, but each sub-query in the real case has a more complex set of joins that determines the price. The pricing joins from one sub-query to the next vary, so a collection of sub-queries seemed to be a logical solution. Some part numbers are found in only one of the lists, while other part numbers are repeated across lists at different prices. This is what I would *like* to say: SELECT DISTINCT ON (part_number) * FROM ( SELECT product_list_id,part_number,price,priority FROM products, product_lists WHERE product_list_id=product_lists.id AND product_list_id=150 AND (part_number='92298A' OR part_number='C39207' OR part_number=...) UNION ALL SELECT product_list_id,part_number,price,priority FROM products, product_lists WHERE product_list_id= product_lists.id AND product_list_id=5 AND (part_number='92298A' OR part_number='C39207' OR part_number=...) UNION ALL SELECT product_list_id,part_number,price,priority FROM products, product_lists WHERE product_list_id= product_lists.id AND product_list_id=3 AND (part_number='92298A' OR part_number='C39207' OR part_number=...) ) AS filter_duplicates ORDER BY priority,part_number I need to ORDER BY priority so that, in the case of duplicates, the product from the desired list is returned first. Then the purpose of DISTINCT ON is to filter out any duplicate part numbers that have a lesser priority. But, the above statement fails because the DISTINCT ON expression must match the leftmost ORDER BY expression. However, inserting the priority into the DISTINCT ON expression means that all of the resulting tuples are unique, even though the part_number is the same. -- 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] Best practices for moving UTF8 databases
Andres Freund wrote: > On Tuesday 14 July 2009 11:36:57 Jasen Betts wrote: > > if you do an ascii dump and the dump starts out "SET CLIENT ENCODING > > 'UTF8'" or similar but you still get errors. > Do you mean that a dump from SQL_ASCII can yield non-utf8 data? right. But > According to the OP his 8.3 database is UTF8... > So there should not be invalid data in there. I haven't followed this thread, but older PG versions had less strict checks on UTF8 data, which meant that some invalid data could creep in. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Ascending / Descending Indexes
Michael Gould wrote: > In some SQL engines the engine doesn't need to define both Ascending and > Descending indexes on the same column. Does Postgres need to have indexes > defined for both Ascending and Descending sorts? We use quite a few of > these types of sorts. A single btree index can be used for both cases. (Unless you want some columns ascending and other columns descending, in which case you need to work extra.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ascending / Descending Indexes
In some SQL engines the engine doesn't need to define both Ascending and Descending indexes on the same column. Does Postgres need to have indexes defined for both Ascending and Descending sorts? We use quite a few of these types of sorts. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] cache lookup failed for function 72629
I had been using Slony-I together with another database on a server on a different machine. I had been testing my replication constantly dropping and creating my Slony-I tables. This is not out of the ordinary though. I had been doing this for many days now. The query, I had been doing is just an update or insert into any table in this database. There are about 40 tables. Any change to the tables in the database results in this error. Like I said, I had restarted the PostgreSQL service several times as well as restarted my Server several times to no avail. I am pretty stumped and am not sure what to do short of a complete uninstall and reinstall of PostgreSQL. Although I am not sure that would work at this point. Probably because I'm still not sure what this error means. Once again, like I said, it is just a simple update or insert into my tables. I had been doing this function just a couple minutes earlier to this message and did not do anything out of the ordinary in the time between. > Date: Tue, 14 Jul 2009 09:16:05 -0400 > Subject: Re: [GENERAL] cache lookup failed for function 72629 > From: mmonc...@gmail.com > To: lawrence...@hotmail.com > CC: pgsql-general@postgresql.org > > On Tue, Jul 14, 2009 at 9:12 AM, Lawrence Wong wrote: > > I'm sorry. What does 're-applying the function source' mean? and how would > > I do it? > > > > For the 'change to the table' I was talking about, I just tried to do a > > regular update on the table using pgAdmin III. > > > > LW . . . Lawrence > > > > well, the idea is to find the function that is causing the problem, > make a change, and save it back with pgadmin. still, this shouldn't > be happening, and a database restart should certainly have fixed it. > do we have any context for this error? do you know the query that is > causing it? > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ We are your photos. Share us now with Windows Live Photos. http://go.microsoft.com/?linkid=9666047
Re: [GENERAL] cache lookup failed for function 72629
On Tue, Jul 14, 2009 at 9:12 AM, Lawrence Wong wrote: > I'm sorry. What does 're-applying the function source' mean? and how would > I do it? > > For the 'change to the table' I was talking about, I just tried to do a > regular update on the table using pgAdmin III. > > LW . . . Lawrence > well, the idea is to find the function that is causing the problem, make a change, and save it back with pgadmin. still, this shouldn't be happening, and a database restart should certainly have fixed it. do we have any context for this error? do you know the query that is causing it? merlin -- 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] Flexibility of views and functions?
On Mon, Jul 13, 2009 at 8:54 PM, Andreas wrote: > Hi, > I need to do some reporting for projects that have some columns that stay > the same for every project and then every project brings along some project > specific stuff. > Now I've got a big view for everyone of those about 100 projects (and > growing) that is about 80% the same as every other view. > > I'd like to strip the constant part into a central view holding those common > columns like "tvw_big_thing" > then have for every project some > SELECT tvw_big_thing.*, c1, c2, ..., cn ... > where c1...cn would be project specific. > > I'dread the day when mr. boss comes along to tell me he likes tvw_big_thing > altered. > All dependend views would have to be dropped and recreated, am I right? > > I figured a function as cool, too like > fct_big_thing(project_id::integer) > it could do the filtering :) > > I'd be cool to have depending views show the "inherited" columns that get > delivered on call time. why can't you use a view for what you want to do with a function? you can nest views... if you have a situation where a view and a function are both appropriate...choose a view: *) views are more flexible...can be queried on any field, not just a fixed set of inputs *) views are easier to join with other tables/views *) the planner will often be able to better discern what is going on with a view vs a function *) views have stricter dependency tracking -- the database has higher probability of blocking a ddl change that would make your function error (although this can also be a nuisance) as a consequence of the last point, if you are tables that views depend on are changing a lot you need to be prepared to have a script (or a function!) that drops and regenerates your views on command. merlin -- 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] cache lookup failed for function 72629
I'm sorry. What does 're-applying the function source' mean? and how would I do it? For the 'change to the table' I was talking about, I just tried to do a regular update on the table using pgAdmin III. LW . . . Lawrence > Date: Tue, 14 Jul 2009 09:01:38 -0400 > Subject: Re: [GENERAL] cache lookup failed for function 72629 > From: mmonc...@gmail.com > To: lawrence...@hotmail.com > CC: pgsql-general@postgresql.org > > On Tue, Jul 14, 2009 at 8:23 AM, Lawrence Wong wrote: > > Hi, > > > > I had been using my database for a pretty long time now. It is postgres 8.3 > > on Windows 2003 Server. Today I tried to make a change to one of my tables > > and I got this error message: > > > > 'An error has occured: ERROR: cache lookup failed for function 72629' > > > > I tried stopping the service and starting it. I tried restarting the > > server. Neither worked. Has anyone seen something like this and knows a > > solution? I would really like to avoid having to reinstall Postgres as my > > tables are rather large and backing up and restoring would be time > > consuming. > > try re-applying the function source. > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Create a cool, new character for your Windows Live™ Messenger. http://go.microsoft.com/?linkid=9656621
Re: [GENERAL] cache lookup failed for function 72629
On Tue, Jul 14, 2009 at 8:23 AM, Lawrence Wong wrote: > Hi, > > I had been using my database for a pretty long time now. It is postgres 8.3 > on Windows 2003 Server. Today I tried to make a change to one of my tables > and I got this error message: > > 'An error has occured: ERROR: cache lookup failed for function 72629' > > I tried stopping the service and starting it. I tried restarting the > server. Neither worked. Has anyone seen something like this and knows a > solution? I would really like to avoid having to reinstall Postgres as my > tables are rather large and backing up and restoring would be time > consuming. try re-applying the function source. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cache lookup failed for function 72629
Hi, I had been using my database for a pretty long time now. It is postgres 8.3 on Windows 2003 Server. Today I tried to make a change to one of my tables and I got this error message: 'An error has occured: ERROR: cache lookup failed for function 72629' I tried stopping the service and starting it. I tried restarting the server. Neither worked. Has anyone seen something like this and knows a solution? I would really like to avoid having to reinstall Postgres as my tables are rather large and backing up and restoring would be time consuming. _ Internet explorer 8 lets you browse the web faster. http://go.microsoft.com/?linkid=9655582
[GENERAL] Connection pool/load balancer supporting ident authentication?
Hi. Is anyone aware of a connection pool or load balancer for PostgreSQL that supports ident based authentication? Neither pgpool-ii nor pgbouncer support this according to their docs, so I was wondering what else is out there. -- Stuart Bishop http://www.stuartbishop.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] SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
2009/7/13 Tom Lane : > No, you're misinterpreting the message. What that code likely means > is that something is trying to use SPI and finding plpgsql already > connected. In other words, plpgsql forgets to do a SPI_push() before > calling something that might try to use SPI re-entrantly. It should be > perfectly deterministic and it definitely doesn't have anything to do > with the states of other sessions. But we're going to need a test > case to fix it. > Tom, I'm not able to prepare a test case - the error is thrown exactly in two queries, but those queries can be executed without that error - there is no rule, when it will be thrown. When the error was thrown both queries cannot be executed (in the same connection of course, but in other connections they can be executed without problem). Before the error is thrown both queries were executed successfully (I'm logging all "mod" queries), but at some moment (unknown for me) the error is thrown and as I wrote those queries cannot be executed in current connection anymore - what is really strange is the fact that other queries (both selects and updates) can be executed in that connection and there some of those queries fires plpgsql triggers which updates db data (so SPI is used??). What can I do ? I don't want to do the downgrade :-( How can I track the moment, when plpgsql forgets to do SPI_push() ? I'm really appreciated for your help. ML -- 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] PostgreSQL-License
On Tue, Jul 14, 2009 at 03:02:14AM -0700, Nenad Milasinovic wrote: > > Hello, > > I am interested can PostgreSQL be used in commercial applications, > and what is licensing policy. I know that PostgreSQL is released > under BSD licence, but still i want to be sure are there any costs > when distributing PostgreSQL in closed, commercial applications (is > there any restrictions when database is bigger than 4G). The only monetary costs you incur when using PostgreSQL in proprietary products are those you incur yourself: developing it, marketing it, distributing, collecting funds, etc. The only way you would lose the rights to your license to use the code would be by violating the terms of that license. http://www.postgresql.org/about/licence.html Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PostgreSQL-License
On Tuesday 14 July 2009 13:02:14 Nenad Milasinovic wrote: > Hello, > > I am interested can PostgreSQL be used in commercial applications, and > what is licensing policy. I know that PostgreSQL is released under BSD > licence, > but still i want to be sure are there any costs when distributing > PostgreSQL in > closed, commercial applications (is there any restrictions when database is > bigger than 4G). The license says: Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. That means what it says: - "without fee" = no costs - "for any purpose" = also for commercial applications, for any database size -- 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] [Q] single image Table across multiple PG servers
Hello, We were also in search of having a table split across multiple databases but then found out about skypetools and at the same time the following article; http://www.jurriaanpersyn.com/archives/2009/02/12/database-sharding-at-netlog-with-mysql-and-php/, true that it's not done with PG, but the same thing can be done with PG as well. Assume this will be helpful for you. Regards, Ransika On Tue, Jul 14, 2009 at 4:20 PM, Scott Marlowe wrote: > On Mon, Jul 13, 2009 at 11:16 PM, V S P wrote: > > Hello > > > > I am researching how to store the data for easy 'user-driven' > > reporting (where I do not need to develop application for > > every user request). > > > > The data will typically be number ranges and text strings with > > unique Id for each row > > > > I hope there will be a lot of data :-). > > > > So in that anticipation I am looking for a way > > to allow > > SQL/ODBC access to the data > > > > but in a way that each table resides on more than one > > PG server > > > > for example: > > > > table 1 lives in 3 PG instances (I can partition the data by date range) > > table 2 lives in the same 3 instances plus another one (because it's > > bigger) > > > > > > and I would like users to be able to issue SQL from within ODBC that > > joins them. > > I think that skype's skytools could be used to create such a solution, > in particular pl/proxy. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Ransika De Silva SCMAD 1.0, SCJP 1.4, BSc.(Hons) Information Systems
[GENERAL] PostgreSQL-License
Hello, I am interested can PostgreSQL be used in commercial applications, and what is licensing policy. I know that PostgreSQL is released under BSD licence, but still i want to be sure are there any costs when distributing PostgreSQL in closed, commercial applications (is there any restrictions when database is bigger than 4G). Thanks. -- View this message in context: http://www.nabble.com/PostgreSQL-License-tp24476686p24476686.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Request for features
On 2009-07-13, Michael Gould wrote: > I would like to know what the formal method of requesting new features are > for Postgres and how are decisions made which features are included in a > future release or not. Formal? for open soure software in general, changes are are requested using the bug reporting system. Change requests accompanied with working source patches have an advantage. Grants of cash to the developers tend to help too. -- 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] [Q] single image Table across multiple PG servers
On Mon, Jul 13, 2009 at 11:16 PM, V S P wrote: > Hello > > I am researching how to store the data for easy 'user-driven' > reporting (where I do not need to develop application for > every user request). > > The data will typically be number ranges and text strings with > unique Id for each row > > I hope there will be a lot of data :-). > > So in that anticipation I am looking for a way > to allow > SQL/ODBC access to the data > > but in a way that each table resides on more than one > PG server > > for example: > > table 1 lives in 3 PG instances (I can partition the data by date range) > table 2 lives in the same 3 instances plus another one (because it's > bigger) > > > and I would like users to be able to issue SQL from within ODBC that > joins them. I think that skype's skytools could be used to create such a solution, in particular pl/proxy. -- 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] Best practices for moving UTF8 databases
On Tuesday 14 July 2009 11:36:57 Jasen Betts wrote: > On 2009-07-13, Andres Freund wrote: > > On Sunday 12 July 2009 13:19:50 Phoenix Kiula wrote: > >> Hi. I *always* get an error moving my current fully utf-8 database > >> data into a new DB. > >> > >> My server has the version 8.3 with a five year old DB. Everything, all > >> collation, LC_LOCALE etc are all utf8. > >> > >> When I install a new Postgresql 8.4 on my home Mac OSX machine (after > >> losing some hair) I set everything about a new database to be utf8. At > >> least anything I could see in PgAdmin. > >> > >> But when I pull in the data dump from the server I always see that > >> error from the utf8 mismatch and such. > >> > >> So, my question. What is a good way to make sure that error does NOT > >> occur? I simply wish to replicate the server database on another PG > >> installation. What should one do? > > > > What is the _exact_ error you get? During which statement? > if you do an ascii dump and the dump starts out "SET CLIENT ENCODING > 'UTF8'" or similar but you still get errors. Do you mean that a dump from SQL_ASCII can yield non-utf8 data? right. But According to the OP his 8.3 database is UTF8... So there should not be invalid data in there. Andres -- 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] Best practices for moving UTF8 databases
On 2009-07-13, Andres Freund wrote: > On Sunday 12 July 2009 13:19:50 Phoenix Kiula wrote: >> Hi. I *always* get an error moving my current fully utf-8 database >> data into a new DB. >> >> My server has the version 8.3 with a five year old DB. Everything, all >> collation, LC_LOCALE etc are all utf8. >> >> When I install a new Postgresql 8.4 on my home Mac OSX machine (after >> losing some hair) I set everything about a new database to be utf8. At >> least anything I could see in PgAdmin. >> >> But when I pull in the data dump from the server I always see that >> error from the utf8 mismatch and such. >> >> So, my question. What is a good way to make sure that error does NOT >> occur? I simply wish to replicate the server database on another PG >> installation. What should one do? > What is the _exact_ error you get? During which statement? > > Andres > if you do an ascii dump and the dump starts out "SET CLIENT ENCODING 'UTF8'" or similar but you still get errors. run it through run it through "iconv -f UFT8 -t UTF8//IGNORE" that'll drop any illegal symbols. In theory that's a reduction in data integrity. iconv doesn't seem to have an option to replace them with U+FFFD :( I had hoped that //TRASNSLIT would do that, but no. -- 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] Checkpoint Tuning Question
On Mon, 2009-07-13 at 15:53 -0500, Dan Armbrust wrote: > > So this thought leads to a couple of other things Dan could test. > > First, see if turning off full_page_writes makes the hiccup go away. > > If so, we know the problem is in this area (though still not exactly > > which reason); if not we need another idea. That's not a good permanent > > fix though, since it reduces crash safety. The other knobs to > > experiment with are synchronous_commit and wal_sync_method. If the > > stalls are due to commits waiting for additional xlog to get written, > > then async commit should stop them. I'm not sure if changing > > wal_sync_method can help, but it'd be worth experimenting with. > > > All of my testing to date has been done with synchronous_commit=off > > I just tried setting full_page_writes=off - and like magic, the entire > hiccup went away. OK, that seems clear. I mistakenly referred to the CRC calculation happening while the lock was held, which confused the discussion. The lock *is* held for longer when we have backup blocks and the lock does need to be acquired twice immediately after a checkpoint. Neither of the above two effects appear, on their own, sufficient to explain the delay. We should conjecture that a traffic jam exists and go looking for it. Propose a DTrace probe immediately after the "goto begin" at line 740 of xlog.c, so we can start tracing from the first backend following checkpoint, and turn off tracing when all backends have completed a transaction. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
