Re: [GENERAL] Cannot start the postgres service
On Tue, Oct 13, 2009 at 12:14 AM, John R Pierce wrote: > Mitesh51 wrote: >> >> If I move any file from the \PostgreSQL\8.4\data\pg_xlog\ then postgres >> services are stopped & I cannot start them. I need to reinstall the >> postgres. >> > > > well, then don't move any file from pg_xlog while postgres is running! Yeah, a good general rule of thumb is to not mess about with things if you're not sure what they do. pg_xlog and pg_clog are definitely in that category. -- 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] contrib/plantuner - enable PostgreSQL planner hints
On Mon, Oct 12, 2009 at 9:20 AM, Hans-Juergen Schoenig -- PostgreSQL wrote: > if there was a vote whether this should be in contrib or in core: +999 from > me ... Well, contrib is a good place to start. It sets a clearly defined ownership / maintenance person, and if the core of pgsql changes, instead of the core hackers having to make this code happy, that job then falls to the original contributor or whoever is willing to keep it fed and working. I think I'm gonna grab a copy and play with it, it sure could help in the cases where the query planner just can't make the right decision (correlation of indexes etc) Thanks to Oleg for this a bunch. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "Large object (lo)" and PostgreSQL 8.4
Hello, I've got an installation problem with the contrib module "Large object (lo)" and PostgreSQL 8.4. I hope this is the correct mailing list for that. I'm sorry if not. I want to adapt the installation routines of my Windows application to PostgreSQL 8.4. In earlier versions of PostgreSQL I was able to click the contrib module "Large object (lo)" while installing the PostgreSQL server. In 8.4 this is not offered any more and I didn't find any information or download package in the internet. So at the moment I must restore an old database backup to get the large object functionalities. Now what is the correct way to install the contrib module "lo" in PostgreSQL 8.4? Best regards Gerhard Lutz MBtech GmbH, Sindefingen, Germany If you are not the intended addressee, please inform us immediately that you have received this e-mail in error, and delete it. We thank you for your cooperation.
Re: [GENERAL] Cannot start the postgres service
Yeah... My query is...Is it the reason y postgres stops working?? (Moving files from pg_xlog) John R Pierce wrote: > > Mitesh51 wrote: >> If I move any file from the \PostgreSQL\8.4\data\pg_xlog\ then postgres >> services are stopped & I cannot start them. I need to reinstall the >> postgres. >> > > > well, then don't move any file from pg_xlog while postgres is running! > > > (was there a question here?) > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25868045.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
[GENERAL] tar error while running basebackup
Hi! We're using two backup strategies to get consistent backups of our postgresql databases. First, we create a complete dump every night by running pg_dump, zipping the file and writing this backup on tape. Second, we create a basebackup every saturday. To create a basebackup, we run pg_start_backup. After that, we create a tar file of the complete database directory and stop the backup mode by running pg_stop_backup. Of course, all archived wal logs are also copied ;) Well, everything was fine for month. But from time to time, I get an error when running tar: tar: ./base/208106/209062: File shrank by 262144 bytes; padding with zeros tar: ./base/208106/210576: file changed as we read it tar: ./base/208106/210577: file changed as we read it tar: ./base/208106/210431: file changed as we read it How can this happen? I always thought, that, when in backup mode, nothing is able to change the database - so the database files shouldn't change. Can autovaccumdb cause the changes? I already read something, that this kind of errors can be ignored when creating a basebackup, but I can't believe that. Normally, the tar file have to be worthless, when an error occurs - or do I have an error in reasoning? best regards, Andre -- 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] tar error while running basebackup
> From: Andre Brandt > Subject: [GENERAL] tar error while running basebackup > To: pgsql-general@postgresql.org > Date: Tuesday, 13 October, 2009, 11:40 AM > Hi! > > We're using two backup strategies to get consistent backups > of our postgresql databases. First, we create a complete > dump every night by running pg_dump, zipping the file and > writing this backup on tape. > Second, we create a basebackup every saturday. To create a > basebackup, we run pg_start_backup. After that, we create a > tar file of the complete database directory and stop the > backup mode by running pg_stop_backup. Of course, all > archived wal logs are also copied ;) > > Well, everything was fine for month. But from time to time, > I get an error when running tar: > > tar: ./base/208106/209062: File shrank by 262144 bytes; > padding with zeros > tar: ./base/208106/210576: file changed as we read it > tar: ./base/208106/210577: file changed as we read it > tar: ./base/208106/210431: file changed as we read it > > How can this happen? I always thought, that, when in backup > mode, nothing is able to change the database - so the > database files shouldn't change. Can autovaccumdb cause the > changes? > I already read something, that this kind of errors can be > ignored when creating a basebackup, but I can't believe > that. Normally, the tar file have to be worthless, when an > error occurs - or do I have an error in reasoning? My understanding was that when you back up like this you are actually taring up inconsistant database files, but it doesn't matter as you have the wal logs to replay any changes, thus correcting the inconsistancies. -- 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] tar error while running basebackup
On Tue, Oct 13, 2009 at 12:40:37PM +0200, Andre Brandt wrote: > tar: ./base/208106/209062: File shrank by 262144 bytes; padding with zeros > tar: ./base/208106/210576: file changed as we read it > tar: ./base/208106/210577: file changed as we read it > tar: ./base/208106/210431: file changed as we read it > This is entirely normal. > How can this happen? I always thought, that, when in backup mode, > nothing is able to change the database - so the database files shouldn't > change. Can autovaccumdb cause the changes? pg_start_backup() doesn't tell the database to stop writing changes to disk; it essentially just says "perform a checkpoint", which means all changes as of that instant are written to the base data files. That ensures that you start your base backup in a consistent state. When you recover it, replaying the WAL files will fix any weirdness in your base backup, and you'll get a working database, current up to the last WAL file you recovered. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] tar error while running basebackup
How can this happen? I always thought, that, when in backup mode, nothing is able to change the database - so the database files shouldn't change. Can autovaccumdb cause the changes? pg_start_backup() doesn't tell the database to stop writing changes to disk; it essentially just says "perform a checkpoint", which means all changes as of that instant are written to the base data files. That ensures that you start your base backup in a consistent state. When you recover it, replaying the WAL files will fix any weirdness in your base backup, and you'll get a working database, current up to the last WAL file you recovered. I think, I will install a newer version of tar on my cluster. It seems, that redhat shipped a really old version (1.15.1) with RHEL5. In version 1.16, the return code was changed when an read problem occurs - so I can differ between an read error and an fatal error. Thx for your fast help =) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JMS question
Has anyone done anything to integrate (any) JMS client with Postgres? Google tells me nothing. If not, I see there are JMS servers that use PG as the backend. Any chance of piggybacking on that? Eg could use triggers as 'message listeners' and insert into JMS table could trigger sending a new message as well. This Q is probably better asked in respective JMS server group I guess. Peter
Re: [GENERAL] Best data type to use for sales tax percent
Christophe Pettus wrote: On Oct 10, 2009, at 3:33 AM, Jasen Betts wrote: CREATE DOMAN sales_tax_rate AS DECIMAL CHECK (VALUE >= 0 AND VALUE <=1); why the latter check ( VALUE <=1 )? Since this version has no scale on the DECIMAL, the second check keeps it from being larger than 1.0, since it's presumably a percentage from 0% to 99%. For general-purpose sales-tax...in the United States...for the time being. I believe the current tax on new vehicles in Israel is 70+% and seem to recall that it was well over 100% at one time. Sales taxes already vary by product (in California, food is 0% for example) as well as state, county, city. I can certainly imagine some locale pushing for taxes well over 100% on the sin-du-jour (alcohol, tobacco, sugar, fat..) Cheers, Steve -- 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 data type to use for sales tax percent
> For general-purpose sales-tax...in the United States...for the time being. > > I believe the current tax on new vehicles in Israel is 70+% and seem to > recall that it was well over 100% at one time. Sales taxes already vary by > product (in California, food is 0% for example) as well as state, county, > city. I can certainly imagine some locale pushing for taxes well over 100% > on the sin-du-jour (alcohol, tobacco, sugar, fat..) In the E.U., sin taxes are charged as excise duty. A manufacturer pays duty (a fixed amount per unit of ethanol or whatever), and sells it on to a retailer at a price that (presumably) factors in the cost to him of paying that duty, in addition to VAT - they charge tax on the tax. The retailer doesn't have to give sinful goods any special treatment. When selling on those goods they just charge VAT, in turn, at the standard rate. I imagine it's a similar situation in the U.S. This minimises tax evasion, carousel fraud, etc, and takes the burden/responsibility of collecting such massive taxes (effectively 80%+ of the cost of a pack of cigarettes) higher up the supply chain. Taxes on cars are covered by "vehicle registration tax" and road tax here in Ireland, in addition to charging VAT at the standard rate on top of the total, inclusive cost. Charging massive rates like 70% is avoided. Consumption taxes are supposed to be easy to collect. If you wonder why a manufacturer charges VAT to a retailer or non-end user, well, that's one of the distinctions between VAT and sales tax (they pay, but they claim it back later, whereas an individual cannot claim it back). Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Possible encoding issue (win7)
Hi Using a Delphi app with ZEOS components to connect to server (on localhost) and get this message: SQL Error: invalid encoding name in PGCLIENTENCODING: WIN1252 (Running Windows 7 (64x), postgres 8.3.7) Initially I installed with server and client encoding as WIN1252. But since I got the error I tried an re-installing with locale = C, encoding = LATIN1 (server and client). But this did not solve the problem. >From the little info I found on the web, I also tried "SET CLIENT_ENCODING TO 'LATIN1'" but also had no effect. Read a couple of times about *environment variable* PGCLIENTENCODING, but could find no such variable. Heard about colleagues having some problems installing on Windows7. Could this be one of them? Any help would be appreciated. gmbouwer -- 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] Possible encoding issue (win7)
gmb writes: > Using a Delphi app with ZEOS components to connect to server (on > localhost) and get this message: > SQL Error: invalid encoding name in PGCLIENTENCODING: WIN1252 > (Running Windows 7 (64x), postgres 8.3.7) If the message is spelled exactly that way then it's not coming from anything in the standard Postgres distribution --- we might say "invalid encoding name "something"" but there's noplace that would put a reference to PGCLIENTENCODING in the middle. I infer that the complaint is coming from something in Delphi or ZEOS. You might have better luck asking in support forums for those products. FWIW, we added support for WIN1252 encoding in Postgres 8.1. One possible theory is that this is coming from client-side code that thinks it knows all the encodings supported by Postgres but hasn't been updated in a long time. > Initially I installed with server and client encoding as WIN1252. But > since I got the error I tried an re-installing with > locale = C, encoding = LATIN1 (server and client). But this did not > solve the problem. I'm thinking that this is probably driven by the locale environment on the client side --- something is trying to set the client_encoding to match that, and failing for some reason. So changing server encoding wouldn't help. 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] Possible encoding issue (win7)
Thank for the quick response Tom > If the message is spelled exactly that way then it's not coming from > anything in the standard Postgres distribution --- we might say > "invalid encoding name "something"" but there's noplace that would > put a reference to PGCLIENTENCODING in the middle. I infer that the > complaint is coming from something in Delphi or ZEOS. You might have > better luck asking in support forums for those products. Will do > FWIW, we added support for WIN1252 encoding in Postgres 8.1. One > possible theory is that this is coming from client-side code that > thinks it knows all the encodings supported by Postgres but hasn't > been updated in a long time. I suspect this is the case - the version of Zeos may be outdated. > > Initially I installed with server and client encoding as WIN1252. > > I'm thinking that this is probably driven by the locale environment on > the client side --- something is trying to set the client_encoding to > match that, and failing for some reason. So changing server encoding > wouldn't help. Is it just coincidence that the message displays WIN1252 - the encoding of my first time install? (BTW this may have been an 8.4 installation, soon after I unintalled and installed 8.3.7). I was wondering (hoping) that there may be some reference (config/ registry) to WIN1252 as enocding which I can locate and remove. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 8.4 and pg_autovacuum functionality
Hello, Since pg_autovacuum no longer exits on PG 8.4 and it seems that one now needs to provide the storage parameters during CREATE TABLE or later on with an ALTER TABLE. Will that ALTER TABLE block anything going on that table until it's finished ? I assume not since no table data is actually being rewritten. Thank you, Marcelo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to deactivate FK's to reorder fields in table?
Hi, I need to reorder fields in one table. But the database don't let me do this action because the FK's pointing to the table. How can I deactivate the FK's? Best Regards, André.
Re: [GENERAL] How to deactivate FK's to reorder fields in table?
Andre Lopes wrote: > Hi, > > I need to reorder fields in one table. Not possible. Use a select-list instead of 'select *' to achieve this. > But the database don't let me do this > action because the FK's pointing to the table. How can I deactivate the FK's? You can't reorder the fields, use a 'SELECT field 1, field2, ..., fieldN FROM instead. It has hothing to do with the FK's field. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Possible encoding issue (win7)
Seems all that was necessary was a restart of the server :( ...wish I hadn't spend the whole day on this -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SFPUG: Video from "Statistics and Postgres — How the Planner Sees Your Data"
Hi, The video from "Statistics and Postgres — How the Planner Sees Your Data," the September 8, 2009 meeting of the SFPUG, is now available on- line: http://thebuild.com/blog/2009/10/13/sfpug-statistics-and-postgres/ It's uploading to Vimeo now, so it should be available there shortly as well. -- -- Christophe Pettus x...@thebuild.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] How to deactivate FK's to reorder fields in table?
On 13/10/2009 20:17, Andre Lopes wrote: > I need to reorder fields in one table. But the database don't let me do this > action because the FK's pointing to the table. How can I deactivate the > FK's? You can't reorder columns in PostgreSQL, so I'm guessing that you're trying to drop and re-creating the table, and begin prevented from doing so by FK dependencies from other tables - would that be correct? If so, you can just drop the FKs and recreate them afterwards. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query to find contiguous ranges on a column
Given a column of data resembling the following: col 2 3 4 5 11 12 13 14 15 16 17 18 19 23 32 33 34 35 36 37 I need a query to find the contiguous ranges within this column, in this case returning the result set: start, end 2, 5 11, 19 23, 23 32, 37 I have one solution that joins the table against itself and does (among other things) a subselect looking "not exists col +1" and "not exists col -1" on the two instances of the table to find the start and end. This is, as you might guess, is not very efficient (my actual data is some 6 million+ rows) and I'm guessing there has to be something more efficient with windowing or possibly grouping on min and max (though I can't see how to make sure they are part of a contiguous set). Anyone have any ideas? -- Peter Hunsberger -- 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] Query to find contiguous ranges on a column
Peter Hunsberger wrote:
> [...]
> I have one solution that joins the table against itself and does
> (among other things) a subselect looking "not exists col +1" and "not
> exists col -1" on the two instances of the table to find the start and
> end. This is, as you might guess, is not very efficient (my actual
> data is some 6 million+ rows) and I'm guessing there has to be
> something more efficient with windowing or possibly grouping on min
> and max (though I can't see how to make sure they are part of a
> contiguous set). Anyone have any ideas?
You can either use a PL/pgSQL function ("SETOF TEXT" just
for the convenience of the example):
| CREATE FUNCTION SummarizeRanges () RETURNS SETOF TEXT AS $$
| DECLARE
| CurrentFirst INT;
| CurrentLast INT;
| CurrentRecord RECORD;
| BEGIN
| FOR CurrentRecord IN SELECT col FROM t ORDER BY col LOOP
| IF CurrentFirst IS NULL THEN
| CurrentFirst := CurrentRecord.col;
| CurrentLast := CurrentRecord.col;
| ELSIF CurrentRecord.col = CurrentLast + 1 THEN
| CurrentLast := CurrentRecord.col;
| ELSE
| RETURN NEXT CurrentFirst || ', ' || CurrentLast;
| CurrentFirst := CurrentRecord.col;
| CurrentLast := CurrentRecord.col;
| END IF;
| END LOOP;
| IF CurrentFirst IS NOT NULL THEN
| RETURN NEXT CurrentFirst || ', ' || CurrentLast;
| END IF;
| RETURN;
| END;
| $$ LANGUAGE plpgsql;
or a recursive query (which I always find very hard to com-
prehend):
| WITH RECURSIVE RecCols (LeftBoundary, Value) AS
| (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t)
|UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE
c.col = p.Value + 1)
| SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols
| GROUP BY LeftBoundary
| ORDER BY LeftBoundary;
Could you run both against your data set and find out which
one is faster for your six million rows?
Tim
--
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] Query to find contiguous ranges on a column
Peter Hunsberger wrote on 13.10.2009 23:23: I need a query to find the contiguous ranges within this column, in this case returning the result set: start, end 2, 5 11, 19 23, 23 32, 37 I have one solution that joins the table against itself and does (among other things) a subselect looking "not exists col +1" and "not exists col -1" on the two instances of the table to find the start and end. This is, as you might guess, is not very efficient (my actual data is some 6 million+ rows) and I'm guessing there has to be something more efficient with windowing or possibly grouping on min and max (though I can't see how to make sure they are part of a contiguous set). Anyone have any ideas? This is the best I can put together right now. Not very nice, but currently I can't think of a better solution: select * from ( select soi as start_of_interval, case when soi is not null and eoi is null then lead(eoi) over() when soi is not null and eoi is not null then eoi else null end as end_of_interval from ( select case when col - (lag(col,1,col + 1) over (order by col)) - 1 <> 0 then col else null end as soi, case when col - (lead(col,1,col + 1) over (order by col)) + 1 <> 0 then col else null end as eoi from numbers ) t1 where t1.soi is not null or t1.eoi is not null ) t2 where t2.start_of_interval is not null and t2.end_of_interval is not null; The outer-most select is needed to get rid of the "empty" rows. I couldn't find a way to push that into one of the sub-queries. The execution plan doesn't look too bad (probably better than your plan with a self join and a subselect), but it does sort the whole table which might be a problem. Regards 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] Procedure for feature requests?
Sam Mason wrote: >> >> "generate_series(A, B, C)" can also >> >> be written as "A + generate_series(0, (C - B) / C) * C" >> > >> > If you can figure out the limit then it seems easy, >> > though I'm not sure how you'd do that. >> What limit? > Sorry, I was calling the second parameter to generate_series the "limit". > Calculating "(C - B) / C" isn't easy for timestamps, whereas it's easy > for dates. I believe this is why there's a specific version for the > former but not the latter. (I obviously meant "(B - A) / C" :-).) Is it? I would assume that you just have to convert A, B and C to seconds (since epoch) and then use a normal integer division. > [...] >> "generate_series(DATE, DATE)" would just be syntactic sugar, >> and I like sweets. > We all do, but in software it's got to be balanced against the overhead > of maintaining support for these functions. My knowledge of PostgreSQL's codebase is nonexistent, so I do not know how unstable it is. Tim -- 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] Current state of XML capabilities in PostgreSQL?
Grzegorz Jaśkiewicz wrote: >> why would you store data thats wrapped in two copies of its fieldname along >> with other punctuation? wouldn't it make more sense to decompose your XML >> source into proper tables so proper indexes and relational sql queries can >> be made? otherwise, every query turns into a massive sequential scan and >> parsing operation. > you can always have index on xpath() ... > and than use same expression in WHERE, and postgresql will use index. Interesting. I had thought that it was not possible to index on XML columns because no comparison operators were defined: | tim=# CREATE TEMPORARY TABLE tmpXML (x XML PRIMARY KEY); | FEHLER: Datentyp xml hat keine Standardoperatorklasse für Zugriffsmethode »btree« | HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp definieren. yet an array of XML works: | tim=# CREATE TEMPORARY TABLE tmpXML (x XML[] PRIMARY KEY); | HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »tmpxml_pkey« für Tabelle »tmpxml« | CREATE TABLE | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT 'Manual...'))); | INSERT 0 1 though only once: | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT 'Manual 2...'))); | FEHLER: konnte keine Vergleichsfunktion für Typ xml ermitteln So an index on xpath() should not be possible. Tim -- 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] Cannot start the postgres service
On 13/10/2009 2:59 PM, Mitesh51 wrote: > > Yeah... > > My query is...Is it the reason y postgres stops working?? (Moving files from > pg_xlog) pg_xlog contains transaction logs. These aren't "log files" in the sense of text logs designed for the administrator to use. They're part of the critical function of the database and they're what permits Pg to support transactional rollback, safe crash recovery, point-in-time recovery, and lots more. Deleting them or moving them is just as bad for your database as deleting or moving the files that store tables. It's a really, really bad idea. A better question might by "why on earth are you messing about with the data directory when you don't understand what it does and how it works?". -- 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
[GENERAL] How to create tsvector_update_trigger on Non-character type data
Hi,
How can we create tsvector update trigger on Non-character data type.
For example, i have created a ts vector trigger something like this.
*CREATE TRIGGER tr_doc_id_col
BEFORE INSERT OR UPDATE
ON document
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('tsv_doc_id',
'pg_catalog.english', doc_id');*
**
Here,
*tr_doc_id_col* -- Name of the trigger
*document* -- Name of the table
*tsv_doc_id* -- tsvector form of the doc_id
*doc_id --* Name of the column. It's data type is *integer*
This trigger should update the *tsv_doc_id*, when there is insert, delete or
update happens on *doc_id* column.
But, the trigger is throwing an error saying that *doc_id* is not of
character type (i.e it is not able to update based on non-character type
column).
I have tried creating same kind of triggers on columns like *title,
body*which are text data type. In this case it is working very well,
but in the
earlier case.
Can any of you tell me how to do in the case of non-character data type like
doc_id?
Thanks,
Gaini Rajeshwar*
*
Re: [GENERAL] Cannot start the postgres service
On Tue, Oct 13, 2009 at 11:24 PM, Craig Ringer wrote: > > A better question might by "why on earth are you messing about with the > data directory when you don't understand what it does and how it works?". Not that anyone wants to discourage exploring. It's just there are better ways to go about things than deleting / removing files if you're not sure what they do. There's a whole section on internals here: http://www.postgresql.org/docs/8.4/interactive/storage.html If we go here: http://www.postgresql.org/docs/8.4/interactive/storage-file-layout.html There's a nice section on what each file-type / directory does. -- 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] How to create tsvector_update_trigger on Non-character type data
On Oct 13, 2009, at 11:21 PM, Gaini Rajeshwar wrote: doc_id -- Name of the column. It's data type is integer The strict error message is correct: The full-text search feature of PostgreSQL can only index text strings, and doc_id (as an integer) is not a text string. What precisely are you attempting to do? Do you want to index the text version of the doc_id field (for example, if doc_id is 12345, you want to include the literal string "12345" in the index), or is doc_id a key into another table, and you want to include some text fields from that other table in the index? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
