Re: [GENERAL] can't start postgresql
On Tue, 14 Nov 2006 21:26:02 +0200, Igor Shevchenko wrote: I'd think starting a standalone backend and issuing a reindex database should be enough to get him started. Now, the problem is figuring _how_ the index got in that state; or even _what_ index is the problematic one. (I think it would be possible to find out by setting the log_error_verbosity parameter to verbose). The problem is, it won't start even in the standalone mode. It prints the same error message and dies. I was trying to start it connected to my main db, template0, template1 and postgres, no change. The index corruption (assuming this IS an index corruption) must've happened with system table(s). Is there any way to diagose that ? Maybe reindex a specific table without starting postmaster, or remove certain index files physically (e.g. for pg_class, etc) ? I think you want to use -P to ignore the indexes on startup: postgres -D /usr/local/pgsql/data -P my_database Does that help - then you might get the chance to do a REINDEX? Regards John ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] XPath and XML support
On Thu, 24 Aug 2006 13:56:12 -0700, Frank Cohen wrote: Thanks Nikolay: Seeing as xml2 hasn't been ported to Windows yet makes me wonder if this is going to be the best way to use XML in PostgreSQL in the long-term? Is there anything else on the boards? - Frank There's a lot of scope to do better than the current support, certainly. However, the not ported to Windows is slightly misleading - the code (like a lot of contrib code) compiles fine under Windows. Because of the dependency on libxml2, it might not often be distributed as a binary, but people are definitely using on Win32 (I'm not really a Win32 user but I have tried it and it did work). John ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgxml xpath_table
On Sat, 2006-06-10 at 14:06 -0400, Tom Lane wrote: John Gray [EMAIL PROTECTED] writes: This is the reason why xpath_table allows you to specify an identifying field (usually a primary key but doesn't have to be)- the solution to your question is to join an xpath_table that just fetches the document number against the primary key, e.g.: John, do you think anything could be done in the pgxml documentation to make this usage pattern clearer? Yes - I'll write a doc patch for this and include an example like this by way of demonstration - I notice now that this particular feature (which I went to some lengths to incorporate in the code!) is completely undocumented. I'll also consider whether another function with different behaviour in this situation is possible or helpful. Give me a few days... Regards John regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pgxml xpath_table
Hi,
On Fri, 09 Jun 2006 08:43:51 +0200, Philippe Lang wrote:
I'm playing with the contrib/pgxml library under PG 8.1.4, and I'm not sure
if what I found with pgxml is a feature of a bug:
[snip]
I get:
iddoc_num line_numval1val2val3
1 C1 L1 1 2 3
1 L2 11 22 33
I was expecting doc_num would receive twice the C1 value, just like with a
normal sql join.
The results from the XPath expressions should be seen as a plain list
representation of a multivalued answer rather than a join expression. i.e.
This is intended to be a feature. In order to deal with multivalued
results, the xpath_table function as many rows as the largest number of
result values from any of the XPath expressions. There is no sound way to
fill in the other columns if the result sets are of different lengths, so
they are left as null.
The assumption was that the XPath expressions would be used together - the code
doesn't know that /doc/@num only occurs once and that it is equally
applicable for all the rows.
This is the reason why xpath_table allows you to specify an
identifying field (usually a primary key but doesn't have to be)- the
solution to your question is to join an xpath_table that just fetches the
document number against the primary key, e.g.:
SELECT t.*,i.doc_num FROM
xpath_table('id','xml','test',
'/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c','1=1')
AS t(id int4, line_num varchar(10), val1 int4, val2 int4, val3 int4),
xpath_table('id','xml','test','/doc/@num','1=1')
AS i(id int4, doc_num varchar(10))
WHERE i.id=t.id and i.id=1
ORDER BY doc_num, line_num;
Giving
id | line_num | val1 | val2 | val3 | doc_num
+--+--+--+--+-
1 | L1 |1 |2 |3 | C1
1 | L2 | 11 | 22 | 33 | C1
(2 rows)
Hope this helps.
Regards
John
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] XML Support
On Fri, 26 May 2006 13:25:34 +0200, Thomas Kellerer wrote: Hello, I'm trying to use XML with PG, and I a descriptioin of the XML support for PostgreSQL at http://www.throwingbeans.org/postgresql_and_xml_updated.html As I could not find the mentioned package in my Windows installation (8.1.3) I thought I'd give the package from that website a try even though it says it's for 8.0 It may not be built by default. The source for the package is part of the main distribution, but assuming you are using the installer for the Windows version, that is of limited use to you. I can't claim to have great Windows compilation skills, but another contributor posted a zipped dll some time ago: http://archives.postgresql.org/pgsql-novice/2005-11/msg00216.php That may help you out. I copied all the .dll from the archive to $libdir and after restarting PG I tried to create the necessary functions using the supplied SQL script. But when running the supplied script, I get an error message: ERROR: could not load library D:/Programme/Postgres/lib/libpgxml.dll: The problem is not likely to be an inability to find the file, it may be that symbols in it remain unreferenced i.e. it's either not compatible with the main postgres executable or with libxml2. Does anybody know whether there is an update planned for this module or even built-in XML support in a later version? The only issue is a Win32 binary version of it - the module itself is intended to (and I believe does!) work with 8.1. Have a look at the referenced link and see if that helps you - if you still have problems, post again and I'll think harder! Regards John (original developer of contrib/xml2 but now largely bereft of time and glad to see XML in the SoC proposals!) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] xml_valid function
On Wed, 25 Jan 2006 17:11:04 -0800, George Pavlov wrote: Not sure what the correct forum for pgxml/xml2 questions is. I was wondering what is the definition of valid that the xml_valid(text) function that is part of that module uses? It seems different from the W3C definition of valid XML (is there an implicit DTD?) Maybe it is more akin to well-formed? It is indeed well-formed. That just seemed a long name for the function! Regards John ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] xml_valid function
On Fri, 2006-01-27 at 12:32 -0800, Roger Hand wrote: John Gray wrote on Friday, January 27, 2006 12:24 PM On Wed, 25 Jan 2006 17:11:04 -0800, George Pavlov wrote: Not sure what the correct forum for pgxml/xml2 questions is. I was wondering what is the definition of valid that the xml_valid(text) function that is part of that module uses? It seems different from the W3C definition of valid XML (is there an implicit DTD?) Maybe it is more akin to well-formed? It is indeed well-formed. That just seemed a long name for the function! John Valid means it's been checked against, and conforms to, a DTD. If it hasn't been then it can't be said to be valid. I know that - my point was just that when I was naming the functions, I (perhaps foolishly, in hindsight) decided that xml_wellformed seemed a longish name for a basic function. The README does in fact state that it checks well-formedness and not validity. It's easily changed in the SQL file if you'd rather have a different name for your installation. As for changing it in the distribution, I can see some backward-compatibility issues (I suspect it may be in production use under that name) - but if there were to be a version which validated a document against a DTD it would be a two parameter version which would therefore have a different signature for PG. Regards John ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Top 10 Wishlist
Robert, Others have covered some of your topics. On Thu, 12 Jan 2006 20:51:44 -0800, rlee0001 wrote: 7. An XML field type and associated XPath/DOM functions. Other exotic field types like Image might be nice for some people as well. But XML would be awesome. The contrib module xml2 (in the core source distribution and also built in some bianry versions) provides some support functions for XPath, where you store the documents in columns of type text. There is also a project, XpSQL that does decomposition of XML documents - see http://gborg.postgresql.org/project/xpsql/projdisplay.php Just some stupid ideas. It's always valuable to see what people are interested in - but there are sometimes reasons why it's not seen as practical or appropriate (standards compliance, namespace pollution etc.) to include a requested feature. Regards John ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OpenOffice 2 and Postgresql 8.1 How to?
On Mon, 12 Dec 2005 21:34:42 +0100, Tino Wildenhain wrote: Am Montag, den 12.12.2005, 15:26 -0500 schrieb Matthew T. O'Connor: Jerry LeVan wrote: Are there any (detailed) instructions available on how to connect OpenOffice 2 and Postgresql 8.1. I have the jdbc driver(s) from postgresql.org... I am running FC4 and have access to DB's on a Mac and the Linux box. This would be a helpful thing to have. I know there is a native OOo driver that was in development but hasn't been updated in quite a while. I would love to see that get included in the main OOo distribution. http://dba.openoffice.org/drivers/postgresql/ Anyone have any information on this? This one worked fine with OO.org 1.x but does not anymore with OO.org 2.x (It appears to be registered but just does not appear in the list of available database types) It worked for me (using version sdbc driver v0.6.2 and oO.o 2.0) - the biggest drawback is that it includes pg_catalog and information_schema in the table selection boxes - with public at the end! The instructions for the dba setup are either non-existent or incorrect, so you need to have a spirit of adventure about you (eg: the connection string to provide is just the host=somehost dbname=xyz bit - you don't need the sdbc:postgresql: prefix.) Regards John ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] XML inserts and updates in postgreSQL 8
On Thu, 13 Oct 2005 10:00:31 -0700, firechaser wrote: Hello all, [example snipped] I've got this working in sql server 2005 beta which implements a sub-set of XQuery on a native xml data type column, but I would prefer to use postgreSQL if at all possible. Although there is support for XPath and XSLT (see contrib/xml2) there's no XQuery support available. (Maybe I'll have to get coding again, but it's a biggish project :) Regards John ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL and XML support
On Tue, 06 Sep 2005 18:48:01 +0400, Andrey Fomichev wrote: Hello! I would like to raise a discussion about XML support in PostgreSQL. I'm a person who is quite experienced in XML data management and new to PostgreSQL. So, excuse me if I ask trivial questions about PostgreSQL... I tried to find something about support for XML in PostrgreSQL. As far as I understand, PosgreSQL does not have native support for XML. But I've found several works around. 1. XML databases build on top of PostgreSQL. They are XpSQL and XDB (sorry, if I missed something). Are they alive and functionable? 2. Contribution made by John Gray (xml and xml2). This is a tool that uses 'shredding' for storing XML in relational tables (another words, it decomposes XML document into nodes and places these nodes into tables in such a way that we can reconstruct this document back). What do you think about this tool? Actually, it doesn't do anything to the document - it just treats it as a text string (I believe XpSQL, for example, decomposes a document into constituent parts). The xml2 contrib module is concerned with providing convenient functions to use XPath queries against such documents. I'd love to do better indexing of them, and have looked at GiST, but I haven't got the understanding needed to accomplish that! In general, I have the following questions to PostgreSQL users. - Are there any of you who need to store and query XML data? - Do you already use PostgreSQL to store XML data or you are just thinking about it? - If you store XML data in PostgreSQL, how do you do it? What tool do you use? - Do you need some advanced query capabilities? Like XQuery, XPath 2.0 - Do you need some advanced update capabilities? Like node-level updates And I have the following question to PostgreSQL developers. Do you have any plans for native support for XML in PostgreSQL? By native support I understand persistent data structured specially developed for storing XML on disk and query/update it efficiently. As far as I know, native support for XML is a way other database vendors (Oracle, Microsoft, IBM) go. I think there's quite a bit of buzzword usage going on from database vendors - it depends on what your use case is what sort of approach you want to take. The advantage of decomposing documents is that you can more readily access the structure of them, but recomposing them is slower. Storing documents as text strings has the advantage that they are easily retrieved (and verified against digital signatures, which may be important for some applications) but are more difficult to represent as a DOM (though how that fits with a relational model at all is the question!) Updates would be interesting - but quite a large project, I suspect. I hope that helps. I am a developer more than a user and I can't speak for people's usage. Others may well do that. Regards John ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connecting to Postgres from LAN
On Mon, 30 May 2005 21:47:43 +0300, Andrus wrote: I installed Postgres 8 to Windows XP and added a line hostall all 168.179.0.1/32 trust I think you should probably make that 168.179.0.0/24 - the /32 means that only the IP address 168.179.0.1 is covered by that line, so the pattern does not match. Regards John Gray ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL and XML
On Thu, 03 Mar 2005 07:59:14 +, Mario Splivalo wrote: Can I use XPath queries in any form to retrive data from XML documents and 'transfer' them to table-like sets? Basically, yes - if you look at the README for contrib/xml2 in the PostgreSQL source distribution (v8.0.1) there is an example using the xpath_table function which allows you to run several XPath expressions in parallel against a set of rows and turn the result into a table. To use this functionality you will need to build the contrib/xml2 mocule - your machine will need libxml (http://xmlsoft.org/) Something as MSSQL's OPENXML keyword? In a way (from reading half a webpage) - I'm not a user of MSSQL so I can't really compare against how their functions work! Regards John ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] xpath_list() question for contrib/xml2
On Mon, 24 Jan 2005 16:53:47 -0800, Ron Mayer wrote: Short summary: I want something like xpath_list() that returns an array (instead of a delimited, concatenated string) when multiple nodes exist in an XML file. It feels to me like that'd be a better (cleaner) API. Yes. It's been at the back of my head that it would be a nice idea - when I first started on contrib/xml and /xml2 array support was rather primitive. Before I write one, does anyone already have such a patch? If not, would people be interested if I added xpath_array() that behaves like xpath_list() but returns an array instead of one big string? Or... is xpsql on gborg or some other postgresql-xml project a better place for me to be looking? Well, if you like the way that contrib/xml2 works, I would add it there, but I'm obviously biased. It could just be another wrapper around pgxml_xpath but use its own traversal of the nodeset instead of pgxmlnodesettotext. I can't speak for whether anyone else is doing anything similar, but I haven't heard anyone say so! Thanks for your interest John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ORM, XPath, and pgxml_dom segfaulting
On Tue, 20 Jan 2004 01:07:00 +, Simen Brekken wrote: Hi, I'm currently running 7.3.4 on Debian/testing, I've been trying to get pgxml_dom working but after doing some normal tests I find that it segfaults the postmaster whenever it hits an error, is this because the contrib module isn't maintained beyond alpha stage or doesn't it simply handle anything but completely well-formed documents without entities etc? Are there any alternatives to this module? There is a new version - which I hope to release within a few weeks. In answer to your questions - one of the bugs with the old version related to a memory leak when entities appeared. Also, although it shouldn't segfault, all the XPath support assumes that your documents are well-formed, so you'll just get NULL returned in many cases if the documents aren't well-formed. Regards John -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] uploading files
On Tue, 18 Nov 2003 21:22:56 +0100, Karsten Hilbert wrote: I think the field will still be competely loaded into memory on the server side though, while LOs are stored in chunks and can theoretically be streamed to the client. I'm not really a definitive authority, though... Ah ! Sounds about right ! Something new to learn every day :-) Actually, bytea and text are chunked behind the scenes (the technique known as TOAST). They are also compressed(LZ) by default. However if you anticipate substringing them a lot (or if they are not very compressible anyway), use ALTER TABLE xxx ALTER COLUMN yyy SET STORAGE EXTERNAL to turn off the automatc compression of values. This allows the substr operation to read the minimum number of chunks necessary of disk. Bear in mind of course, that the whole value will take up more disk space, so reading it in its entirety will be slower (IO is normally the limiting performance factor -CPU on a DB server is often cheap by comparison, so decompressing/compressing to save IO is a good idea). If however you always fetch small parts (e.g. you store large images and usually want to read the header info from them, EXTERNAL is a good bet (and depending on the image format, the compression might not compress them very much anyway). Finally, note that the substr optimisation for text only really buys you anything if the character-set is single-byte. Hope this helps John Gray (implementer of substr optimisation many moons ago!) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Changing xml in a database
On Tue, 11 Nov 2003 11:02:51 +, C G wrote: Dear All, I have installed the contrib xml package and have it working, but I would like some advice on the best way to use it. In particular, how should I alter xml files? For instance, if I have: There's no way at present. The support in contrib/xml is really intended for querying document stores in which each document is updated as a whole. There are other PG/XML solutions available which decompose XML documents into database tables which might be better for your application, eg. Xpsql (which I confess I've never had time to look at!): http://gborg.postgresql.org/project/xpsql/projdisplay.php In general, contrib/xml treats xml documents as blocks of text with XPath queries. It was really meant as a proof of concept (which happens to work in production :-) Regards John ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Installation problem
If I recall, it is the type of system, linux, bsd, x86, etc. Stuff like that. I think I encountered a problem like that when trying to compile Sendmail. For that there was a paramter where you could specify the host type in one of the config files. I think the problem derives from some sort of package that I didn't have installed, because I reinstalled the OS and tried again and it picked it up fine. For the purposes of configure, it is likely to be something like: i686-pc-linux-gnu (supposed to be cpu-vendor-os, apparently -this is from a RedHat 6.2 RPM version of 7.0) This is handled by the config.guess script; you could try looking in there and picking something that matches BUT if config.guess can't tell your system type then maybe you have some inconsistency in the configuration... I'm not an expert on autoconf host strings, though Regards John -- John Gray Senior Technician BEANS INDUSTRY (UK) Ltd 3 Brindley Place Birmingham B1 2JB Tel +44-121-698-8677 Fax +44-121-698-8624 mailto:[EMAIL PROTECTED] http://www.beansindustry.co.uk
