[GENERAL] Comment for column in view - legal or just working?
Using PostgreSQL 8.1.8 on Windows. I have one named listedi in schema cust; that view has one column name No I used comment on cust.listedi.name is 'MyDescriptive Name'; and the command suceeded. Using \d+ in psql also shows me that comment (also I find no option within pgadmin to view it :) ) My question is: - is this a legal usage of COMMENT, or could this vanish in later releases? (within COMMENTs documentation there is only mentionedcolumn tablename.columname) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] backend process terminates
On Tue, Aug 07, 2007 at 07:46:45AM -0400, Geoffrey wrote: I don't know all the idiosyncrasies of how this works, so bear with me on this. The developer at the vendor indicated that he's narrowed down the problem to a set of wrapper routines in their code. They are named OpenFile(), CloseFile() and ReadFile(); He inquired as to whether there might be routines in the Postgresql code with the same names that might be causing a conflict. Sure enough, I searched the Postgresql source code and found routines with the same names. I don't see how this could pose a problem though, as it is my understanding that the compiler will properly address this issue. Yes, this could cause a problem. In general, when loading a library, any external references are first resolved against the main executable, then already loaded libraries, then the library being loaded. It's all in the ELF standard, if you're interested. As for solutions: 1. In your third party library, have the library built in such a way that the symbols are explicitly bound to the internal library version. There are various methods for dealing with that, it all depends on the toolchain used to build it. I suppose this product is actually several libraries that call eachother? Namespace would help here. 2. Make sure that any externally visible symbols in libraries are always prefixed by a tag, like libpq does (almost all symbols are pq*). Running nm -D over the main postgres executable and your libraries should give you an idea of the scope of the problem. Hope this helps, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Comment for column in view - legal or just working?
Harald Armin Massa wrote: Using PostgreSQL 8.1.8 on Windows. I have one named listedi in schema cust; that view has one column name No I used comment on cust.listedi.name http://cust.listedi.name is 'MyDescriptive Name'; and the command suceeded. Using \d+ in psql also shows me that comment (also I find no option within pgadmin to view it :) ) You can in 1.8 - that will show a collection of columns under a view, and allow you to set the comment and default value for each. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Comment for column in view - legal or just working?
Thanks, Dave. reminds me to run allways newest pgadmin, even on customer sites :) having it used within pgadmin makes it legal enough for me to rely on it within my application Best wishes, Harald You can in 1.8 - that will show a collection of columns under a view, and allow you to set the comment and default value for each. -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
[GENERAL] Installation problem
Hi. I\'m trying to install PostgreSQL 8.2.3 on WindowsXP SP2 Media Center (2005 edition). I\'m administrator on this machine, but at the end of the installation process it fails reporting the following error: \Unable to start the service. You must have Administrator rights\. Then the installation ends. I\'ve already converted the file system from FAT32 to NTSC (solving another PostgreSQL problem). Any ideas to solve this problem? Thanks in advance. Luca -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Conto Arancio: 4,50% sino a marzo 2008 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6742d=20070808
Re: [GENERAL] import content of XLS file into PostgreSQL
Hello, One more way to do it with mouse clicking only is OpenOffice. Get OO and install PostgreSQL driver into OpenOffice Database application, then you'll be able to import/export spreadsheets to and from database tables and work with DB tables just like they are spreadsheets. Regards, Ivan On 8/6/07, Alain Roger [EMAIL PROTECTED] wrote: Hi, I would like to know what should i do to import the content (not all columns) of a XLS file into pgsql. is there something special to do ? thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.2.4 PHP 5.2.1 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL and cluster
Hi. Anybody know if is possible to install PostgreSQL in cluster mode on a cluster formed by two windows server 2003 cluster? Luca -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Vivi i MONDIALI di ATLETICA di TOKYO da protagonista. Compra on line i prodotti ufficiali della Nazionale Italiana FIDAL. Vestiti di azzurro Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6907d=20070808
Re: [GENERAL] PostgreSQL and cluster
On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote: Hi. Anybody know if is possible to install PostgreSQL in cluster mode on a cluster formed by two windows server 2003 cluster? Yes. Set it up as a generic service, and make sure you store the data directory (and any other tablespaces) on disks that the service depends on. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Data Mart with Postgres
Hello everybody, Im working with a small project to a client, using Postgres to store data in a dimensional model, fact-oriented, e.g., a Datamart. At this time, all I have is a populated database, with the "star schemma" common relations (PKs / FKs). Below is a list of the main goals of this project : 1. Front-end app (PHP5) 2. Transactional database for this app (Postgres) 3. Datamart in Postgres (described above) 4. ROLAP server that supports Postgres (Java - Mondrian) 5. Front-end app to manage querys to the ROLAP server (JSP - JPivot) Users will have web access to (1), and will be enable to create views using (5). It seems like Im trying to reinvent the wheel, but the point here is that the client cant afford to use proprietary BI solutions, nor proprietary OS. What I have read all across the internet is that Postgres does not support this kind of application, wich demands materialyzed views, built-in bitmap index, and so on. In the open-source world, I find those missing features with Mondrian/JPivot. Does anyone has ever used this structure before ? At least Mondrian and JPivot ? -- []s, Andr Volpato
[GENERAL] Modified FIFO queue and insert rule
I found an excellent description of how to implement a fifo que in PostgreSQL at Greg Mullane's blog: http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html I have used the 'rule' approach to implement a queue that generates a quick-list of last selected places. The only modification I need is that if an item already exists in the list, a new reference should be written to the top, and the old reference should be deleted. But it seems like I'm in over my head here: -- short FIFO list of recently selected places CREATE TABLE recent_places ( id SERIAL PRIMARY KEY, place_fk INTEGER REFERENCES places ON DELETE CASCADE ); CREATE RULE placelimit AS ON INSERT TO recent_places DO ALSO DELETE FROM recent_places WHERE -- this clause doesn't work -- (place_fk = NEW.place_fk AND id NEW.id) OR id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT 10); When I try to use the commented clause above, no records are written to the table at all! Why? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(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] Reordering columns, will this ever be simple?
On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote: novnov [EMAIL PROTECTED] writes: Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. Doubt it, patches to implement this have been submitted and rejected in the past. I don't see any reason why 8.4 would be any different. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] backend process terminates
Martijn van Oosterhout wrote: On Tue, Aug 07, 2007 at 07:46:45AM -0400, Geoffrey wrote: I don't know all the idiosyncrasies of how this works, so bear with me on this. The developer at the vendor indicated that he's narrowed down the problem to a set of wrapper routines in their code. They are named OpenFile(), CloseFile() and ReadFile(); He inquired as to whether there might be routines in the Postgresql code with the same names that might be causing a conflict. Sure enough, I searched the Postgresql source code and found routines with the same names. I don't see how this could pose a problem though, as it is my understanding that the compiler will properly address this issue. Yes, this could cause a problem. In general, when loading a library, any external references are first resolved against the main executable, then already loaded libraries, then the library being loaded. It's all in the ELF standard, if you're interested. I will be checking them out. My compiler knowledge is a bit rusty, circa SVR4... ;) As for solutions: 1. In your third party library, have the library built in such a way that the symbols are explicitly bound to the internal library version. There are various methods for dealing with that, it all depends on the toolchain used to build it. I suppose this product is actually several libraries that call eachother? Namespace would help here. Correct on both counts. Many of the routines are wrapper routines used to assist in code portability. 2. Make sure that any externally visible symbols in libraries are always prefixed by a tag, like libpq does (almost all symbols are pq*). Running nm -D over the main postgres executable and your libraries should give you an idea of the scope of the problem. Hope this helps, It appears that the common routine names were causing the problem. We are currently testing new versions of these libraries where they have renamed the common routines with unique names. Thanks for the insights. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] List tables in reverse dependancy order
Hello,
five years ago I used
CREATE OR REPLACE FUNCTION list_user_tables_sort_depend
(owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS '
DECLARE tabulky VARCHAR[]; i INTEGER; opakovat BOOLEAN = ''t'';
pom VARCHAR; exportovano VARCHAR[] = ''{}''; r RECORD;
mohu_exportovat BOOLEAN;
BEGIN SELECT ARRAY(SELECT tablename FROM pg_tables WHERE tableowner =
owner) INTO tabulky;
WHILE opakovat LOOP
opakovat := ''f'';
FOR i IN array_lower(tabulky,1) .. array_upper(tabulky,1) LOOP
IF tabulky[i] THEN
mohu_exportovat := ''t'';
FOR r IN SELECT t.relname AS z, x.relname AS nz FROM
pg_catalog.pg_constraint d
INNER JOIN pg_catalog.pg_class t on t.oid = d.conrelid
INNER JOIN pg_catalog.pg_class x on x.oid = d.confrelid
WHERE d.contype = ''f'' AND t.relname = tabulky[i] LOOP
IF NOT r.nz = ANY(exportovano) THEN
mohu_exportovat := ''f'';
END IF;
END LOOP;
IF mohu_exportovat THEN
pom := tabulky[i];
exportovano := exportovano || tabulky[i];
opakovat := ''t''; tabulky[i] := ;
END IF;
END IF;
END LOOP;
END LOOP;
IF revers THEN
FOR i IN REVERSE array_upper(exportovano,1) ..
array_lower(exportovano,1) LOOP
RETURN NEXT exportovano[i];
END LOOP;
ELSE
FOR i IN array_lower(exportovano,1) .. array_upper(exportovano,1) LOOP
RETURN NEXT exportovano[i];
END LOOP;
END IF;
RETURN;
END;
' LANGUAGE plpgsql;
I am sorry for czech variable names
Regards
Pavel Stehule
2007/8/2, Gregory Williamson [EMAIL PROTECTED]:
I am not sure if this is the appropriate list -- please point me at the
correct one if not.
I'm trying to create a procedure that would let me retrieve a list of
tables and views in a database that will be used to control the order in
which lookup data is created/loaded. So, much simplified, if table
references table B, which in turn references table A, we want output to list
table A, B and C in that order.
I'm sure that this exists -- the pg_dump command must use some similar
algorithm to decide in which order to load tables, but I can't see to puzzle
this out.
Can anyone provide me with some clues, appropriate RTFM references, etc. ?
TIA,
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information and must be protected in accordance with those
provisions. Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient, please contact the sender
by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Modified FIFO queue and insert rule
Leif B. Kristensen wrote: CREATE RULE placelimit AS ON INSERT TO recent_places DO ALSO DELETE FROM recent_places WHERE -- this clause doesn't work -- (place_fk = NEW.place_fk AND id NEW.id) OR id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT 10); When I try to use the commented clause above, no records are written to the table at all! Why? Do you use nextval() for that id? In that case I think you immediately delete the record after inserting it, as nextval gets called again in the delete statement, and thus id NEW.id. You should probably use a trigger (a before one maybe) instead of a rule. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Modified FIFO queue and insert rule
On Wednesday 8. August 2007 15:12, Alban Hertroys wrote: Leif B. Kristensen wrote: CREATE RULE placelimit AS ON INSERT TO recent_places DO ALSO DELETE FROM recent_places WHERE -- this clause doesn't work -- (place_fk = NEW.place_fk AND id NEW.id) OR id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT 10); When I try to use the commented clause above, no records are written to the table at all! Why? Do you use nextval() for that id? I use : INSERT INTO recent_places (place_fk) VALUES ($place); And that's the functional equivalent of using nextval() I guess. In that case I think you immediately delete the record after inserting it, as nextval gets called again in the delete statement, and thus id NEW.id. Yeah, that sounds reasonable. You should probably use a trigger (a before one maybe) instead of a rule. I might consider that. But I figured that it should be trivial to modify Greg's example rule. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dealing with tsvector in fuctions for data split
Francisco Reyes writes:
ERROR: operator does not exist: text || tsvector
Also, it'd be worth to show simplified version of your function, which
demonstrates your problem.
I did include that.
The function looks like:
AS $$
DECLARE
v_sql TEXT;
BEGIN
v_sql := 'INSERT INTO messagecatalog_'||
to_char(NEW.timestampfield,'')||
'(field1, field2) values ('
||New.field1||','||New.field2||')
')';
EXECUTE v_sql;
RETURN NULL;
END
$$;
In the code above field1 is text and field2 is tsvector.
Any suggestions?
Anyone else has dealt with tsvectors in a partition environment?
If sow how did you get the split function/rule to insert into the child
table?
---(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
[GENERAL] Interpreting statistics collector output
Can anyone provide a brief overview of how to go about interpreting the information generated by the statistics collector? I've looked around and can't find old mailing list messages or anything in the manual beyond the basics of how to query the statistics. Cache hit rates are easy to compute, but is this useful beyond knowing if Postgres has enough buffers? Can anything useful be gleaned from looking at idx_scan / idx_tup_read / idx_tup_fetch? -- Steve Madsen [EMAIL PROTECTED] Light Year Software, LLC http://lightyearsoftware.com ZingLists: Stay organized, and share lists online. http://zinglists.com ---(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
[GENERAL] Automation using postgres
Hi, I my system, I am using postgres triggers to launch some unix tools and thus postgres not only serves the purpose of data storage but also works as an engine to automate the whole system. (this is about my system, talking on a broader level ) I just wanted to know if there's any related work in this area so that I can compare my system with already existing systems related to it. Thanks, Jas
Re: [GENERAL] array_to_set functions
On Tue, 2007-08-07 at 17:46 -0500, Decibel! wrote:
On Sun, Aug 05, 2007 at 08:18:08PM +0530, Merlin Moncure wrote:
On 8/3/07, Guy Fraser [EMAIL PROTECTED] wrote:
On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
On 8/1/07, Decibel! [EMAIL PROTECTED] wrote:
David Fetter and I just came up with these, perhaps others will find
them useful:
CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF
anyelement LANGUAGE SQL AS $$
SELECT $1[i] from generate_series(array_lower($1, $2),
array_upper($1, $2)) i
$$;
CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF
anyelement LANGUAGE SQL AS $$
SELECT array_to_set($1, 1)
$$;
very nice, although IMO there is a strong justification for these
functions to be in core and written in C for efficiency (along with
array_accum, which I have hand burn from copying and pasting out of
the documentation).
merlin
Excellent timing guys. :^)
I was trying to build a function to list the items of an array, but
ran into problems and was going to post what I had been working on.
Your functions work great.
In case you don't have the function to generate an array from a set
here is one I have been using :
CREATE AGGREGATE array_accum (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
I think that's what just about everyone uses. Unfortunately the
reverse of the function (array_to_set above) AFAIK does not map
directly to the C array API.
Oh, cool, hadn't thought about using an aggregate to do this. That's
probably faster than what I came up with.
You are welcome. I am glad someone appreciated my gesture.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Take your postgresSql on the road, and live to tell of it.
On 8/7/07, Owen Hartnett [EMAIL PROTECTED] wrote: At 2:15 PM -0700 8/7/07, Ben wrote: How many users do you have? Have you considered giving each user a schema in which to make their changes? It sounds like you don't really have a multi-master replication issue, which makes things easier. Maybe I'm not understanding the strategy, but I don't see what this buys me, as I have to end up with a single database schema that has incorporated all the changes. If I can record all the SQL a user does from the checkpoint on, then I can psql it in to the main database. Once I've combined their data into the database that sits on the server, I don't need their database copies anymore. I'm not sure how you're planning to do this. PostgreSQL doesn't use SQL statements for Point in Time Recovery, it uses WAL logs applied against a database that's an exact physical copy of the database at a previous time. Are you going to write your own application that will let you save each SQL statement before it's applied to the user's local database? And if so, are you then going to have an individual database for each user? That might work. My way isn't all that much harder to do. It just lets you store all the data in one database and share it out with all the users. So it really depends on what you want in the end. With one database, it would be much easier to run a query across all your data at once. With individual databases you have very strong isolation between the data sets. Either way would work, each has its own advantages and disadvantages. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
On 8/8/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote: novnov [EMAIL PROTECTED] writes: Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. Doubt it, patches to implement this have been submitted and rejected in the past. I don't see any reason why 8.4 would be any different. If the danger of implementing this is some subtle bug that eats my data, I'd just as soon do without. It's not a feature I've ever felt the need for really. And there are lots of cool features I could think of I'd want before this. ---(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] import content of XLS file into PostgreSQL
Hello, On 8/8/07, Roberto Mello [EMAIL PROTECTED] wrote: On 8/8/07, Ivan Zolotukhin [EMAIL PROTECTED] wrote: Hello, One more way to do it with mouse clicking only is OpenOffice. Get OO and install PostgreSQL driver into OpenOffice Database application, then you'll be able to import/export spreadsheets to and from database tables and work with DB tables just like they are spreadsheets. That should be the easiest way if you're not programming-inclined. I don't know if others have suggested it, but you could export the XLS to csv (comma-separated values) and use a csv-parsing module in a programming language (Python has one) to grab only the values you want and import into PostgreSQL. Sure. But if you use XLS one can suspect that you're not programming-inclined :) But seriously, OO Database is a nice thing because you'd better move all this documents that many people prepare in spreadsheets right inside PostgreSQL. Your xls users won't even notice the change while their data will be being stored in the DB directly without any files. Regards, Ivan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL and cluster
Thanks, setting it up as generic service make the clustering procedure to work fine, but I'm still unable to relocate the data directory. We have copied the DATA directory in the right path, but which variables I must change? I've changed the variable about the path (now I'm not in the office and don't remember the name) in the file PostgreSQL.conf, but nothing is changed. Luca - Original Message Da: Magnus Hagander [EMAIL PROTECTED] To: Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] PostgreSQL and cluster Data: 08/08/07 15:41 On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote: Hi. Anybody know if is possible to install PostgreSQL in cluster mode on a cluster formed by two windows server 2003 cluster? Yes. Set it up as a generic service, and make sure you store the data directory (and any other tablespaces) on disks that the service depends on. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Non perderti nella giungla di facili promesse, Logos ti da credito sempre! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6559d=20070808 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Running a query from the OS CLI
If I have a DB called foo ...and... I want to run select name from table_a where name like 'john%' ...and... I want no table header NAME in the output ...and... I want to do this as a one-liner from the linux command line ...and... I don't want to have to deal with intermediate files or home-grown programs... Is this possible?
Re: [GENERAL] Dealing with tsvector in fuctions for data split
On Wed, 8 Aug 2007, Francisco Reyes wrote:
Francisco Reyes writes:
ERROR: operator does not exist: text || tsvector
what'd you expect from this operation ? In 8.2 you can cast tsvector
to text like this:
=# select textin( tsvector_out( strip( to_tsvector('1 b c'::text || 'some
text'::text;
?column?
--
'1' 'b' 'c'some text
You should think about removing positional information from tsvector
using strip() function.
In CVS HEAD standard CAST should works.
postgres=# select cast( 'a b c'::tsvector AS text);
text
-
'a' 'b' 'c'
Also, it'd be worth to show simplified version of your function, which
demonstrates your problem.
I did include that.
The function looks like:
AS $$
DECLARE
v_sql TEXT;
BEGIN
v_sql := 'INSERT INTO messagecatalog_'||
to_char(NEW.timestampfield,'')||
'(field1, field2) values ('
||New.field1||','||New.field2||')
')';
EXECUTE v_sql;
RETURN NULL;
END
$$;
In the code above field1 is text and field2 is tsvector.
Any suggestions?
Anyone else has dealt with tsvectors in a partition environment?
If sow how did you get the split function/rule to insert into the child
table?
---(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
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(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] Running a query from the OS CLI
See: http://www.postgresql.org/docs/8.2/interactive/app-psql.html From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gauthier, Dave Sent: Wednesday, August 08, 2007 11:14 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Running a query from the OS CLI If I have a DB called foo -d dbname --dbname dbname Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line. ...and... I want to run select name from table_a where name like 'john%' -c command --command command Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. command must be either a command string that is completely parsable by the server (i.e., it contains no psql specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, like this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.) If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard input. ...and... I want no table header NAME in the output -t --tuples-only Turn off printing of column names and result row count footers, etc. This is equivalent to the \t command. ...and... I want to do this as a one-liner from the linux command line ...and... I don't want to have to deal with intermediate files or home-grown programs... Is this possible? Read The Fine Manual.
Re: [GENERAL] Running a query from the OS CLI
On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote: If I have a DB called foo psql --dbname foo ...and... I want to run select name from table_a where name like 'john%' psql --dbname foo -c select name from table_a where name like 'john %' ...and... I want no table header NAME in the output psql --dbname foo -c select name from table_a where name like 'john %' -t ...and... I want to do this as a one-liner from the linux command line check. ...and... I don't want to have to deal with intermediate files or home-grown programs... Does that work? Michael Glaesemann grzm seespotcode net ---(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] Running a query from the OS CLI
On Aug 8, 2007, at 11:13 AM, Gauthier, Dave wrote: If I have a DB called “foo” ...and... I want to run “select name from table_a where name like ‘john%’” ...and... I want no table header “NAME” in the output ...and... I want to do this as a one-liner from the linux command line ...and... I don’t want to have to deal with intermediate files or home-grown programs... Something like this: psql -A -q -t -d foo -c “select name from table_a where name like ‘john%’” You may need to use -U to set a user, and there are a bunch of other useful flags to set the output format. There are also flags and environment variables you can set to set the host and port to connect to. Depending on how your access control permissions are setup you may need to get a password to psql, typically by using a ~/.pgpass file. Check the psql man page and the main postgresql docs for the gory details. Cheers, Steve
Re: [GENERAL] Running a query from the OS CLI
Yes, that works. The actual sql executes a stored function that returns a set of records. Output to the CLI looks great !!! Thanks to all !!! -dave -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 2:43 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Running a query from the OS CLI On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote: If I have a DB called foo psql --dbname foo ...and... I want to run select name from table_a where name like 'john%' psql --dbname foo -c select name from table_a where name like 'john %' ...and... I want no table header NAME in the output psql --dbname foo -c select name from table_a where name like 'john %' -t ...and... I want to do this as a one-liner from the linux command line check. ...and... I don't want to have to deal with intermediate files or home-grown programs... Does that work? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tsearch2: plainto_tsquery() with OR?
Does anyone know where I can request an OR-version of plainto_tsquery()? I don't understand why it doesn't exist already: In most cases, when using user entered keywords to search for, there should be returned some rows even though not ALL keywords are matched. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Dump all objects under a specific schema
Hello List, Is there a way that I can only dump all objects under a specific schema? I'd like to dump all tables, views' definition and data under a specific schema. Thank you! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Trigger not working as expected, first row gets a null value
I have a trigger function that updates a field in the update/insert table which uses a not so simple sql statement to find the value to update with. I am not getting the results I expect. I expect I'm falling into some classic trigger gotcha. The table being updated is for 'bids'; 'bids has a fkey to the parent 'item' table. The first time a 'bid' row is added for a particular 'item', the trigger returns null. Any subsequent 'bids' on an 'item' find that the trigger has been invoked and a correct calc applied for the new rows. If value the trigger should apply is hard coded, all table updates get the hard coded value. So there seems to be some kind of timing issue when the complex sql gets involved. But, why does it work after the first row is added for the 'item'? If I change the trigger to be invoked after insert/update, a '0' value is always inserted...I also don't understand that. -- View this message in context: http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12061101 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Dump all objects under a specific schema
Hi; Hello List, Is there a way that I can only dump all objects under a specific schema? I'd like to dump all tables, views' definition and data under a specific schema. pg_dump --help says that you're looking for the -n flag, so, pg_dump -n your_schema -f output.sql Hope that helps, Aurynn Shaw The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103 PostgreSQL Replication, Consulting, Custom Development, 24x7 support [EMAIL PROTECTED] ---(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] Dump all objects under a specific schema
On Aug 8, 2007, at 14:18 , Emi Lu wrote: Is there a way that I can only dump all objects under a specific schema? Have you checked the pg_dump documentation? http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html In particular, the -n flag might interest you. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL and cluster
Probably the easiest way is to unregister the server using pg_ctl unregister, and then re-register it with the other data directory. The other option is to edit the registry under Services and change the commandline used to start the PostgreSQL service. Note that you will have to reboot your server if you go with this method. Normally, you'd point out the cluster directory when you originally install PostgreSQL and the installer will take care of it. //Magnus [EMAIL PROTECTED] wrote: Thanks, setting it up as generic service make the clustering procedure to work fine, but I'm still unable to relocate the data directory. We have copied the DATA directory in the right path, but which variables I must change? I've changed the variable about the path (now I'm not in the office and don't remember the name) in the file PostgreSQL.conf, but nothing is changed. Luca - Original Message Da: Magnus Hagander [EMAIL PROTECTED] To: Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] PostgreSQL and cluster Data: 08/08/07 15:41 On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote: Hi. Anybody know if is possible to install PostgreSQL in cluster mode on a cluster formed by two windows server 2003 cluster? Yes. Set it up as a generic service, and make sure you store the data directory (and any other tablespaces) on disks that the service depends on. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Non perderti nella giungla di facili promesse, Logos ti da credito sempre! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6559d=20070808 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trigger not working as expected, first row gets a null value
On Aug 8, 3:20 pm, [EMAIL PROTECTED] (novnov) wrote: ... I also don't understand ... DDL + sample data, please... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Having trouble building 64-bit pgsql 7.4.17 on HPUX ia64
On Tue, Aug 07, 2007 at 06:50:14PM +0530, Rajaram J wrote: Hi I'm having trouble building 64-bit pgsql 7.4.17 on the latest release of HP-UX 11.23 on ia64. I don't believe that's supported. There was just discussion about this, but I can't find it in the archive right now... -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpD4hJirhf9J.pgp Description: PGP signature
Re: [GENERAL] Reordering columns, will this ever be simple?
On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote: Gregory Stark wrote: novnov [EMAIL PROTECTED] writes: Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. From a admin tool developers perspective the ability to reorder columns without manually copying to a new table and all that is pretty exiting :-) Patches welcome. :) BTW, this is much more likely to happen if we divorce presentation order from actual storage order, something that there is some desire to do because it would allow the engine to automagically store things in the optimal ordering from an alignment standpoint. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpEp5PM6K98S.pgp Description: PGP signature
Re: [GENERAL] Data Mart with Postgres
On Wed, Aug 08, 2007 at 08:56:47AM -0300, Andr? Volpato wrote: Hello everybody, I?m working with a small project to a client, using Postgres to store data in a dimensional model, fact-oriented, e.g., a Datamart. At this time, all I have is a populated database, with the star schemma common relations (PK?s / FK?s). Below is a list of the main goals of this project : 1. Front-end app (PHP5) 2. Transactional database for this app (Postgres) 3. Datamart in Postgres (described above) 4. ROLAP server that supports Postgres (Java - Mondrian) 5. Front-end app to manage querys to the ROLAP server (JSP - JPivot) Users will have web access to (1), and will be enable to create views using (5). It seems like I?m trying to reinvent the wheel, but the point here is that the client can?t afford to use proprietary BI solutions, nor proprietary OS. What I have read all across the internet is that Postgres does not support this kind of application, wich demands materialyzed views, built-in bitmap index, and so on. In the open-source world, I find those missing features with Mondrian/JPivot. Does anyone has ever used this structure before ? At least Mondrian and JPivot ? I haven't but it's certainly possible to build a datamart without bitmap indexes or mviews, it's just a question of performance. MViews you can build yourself easily enough; as for bitmap indexes, IIRC you can get those in Bizgres. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpiSVi0CuRMG.pgp Description: PGP signature
Re: [GENERAL] Interpreting statistics collector output
On Wed, Aug 08, 2007 at 11:01:13AM -0400, Steve Madsen wrote: Can anyone provide a brief overview of how to go about interpreting the information generated by the statistics collector? I've looked around and can't find old mailing list messages or anything in the manual beyond the basics of how to query the statistics. Cache hit rates are easy to compute, but is this useful beyond knowing if Postgres has enough buffers? Can anything useful be gleaned from looking at idx_scan / idx_tup_read / idx_tup_fetch? Yes, that will give you information about how often an index is being used. If you see indexes where idx_scan is a small number, that's an indication that that index isn't being used for queries and could potentially be dropped. Something else I like to look at is pg_stat_all_tables seq_scan and seq_tup_read. If seq_scan is a large number and seq_tup_read/seq_scan is also large, that indicates that you could use an index on that table. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp35f1Zcp38S.pgp Description: PGP signature
Re: [GENERAL] Automation using postgres
On Wed, Aug 08, 2007 at 11:48:28AM -0400, Jasbinder Singh Bali wrote: Hi, I my system, I am using postgres triggers to launch some unix tools and thus postgres not only serves the purpose of data storage but also works as an engine to automate the whole system. (this is about my system, talking on a broader level ) I just wanted to know if there's any related work in this area so that I can compare my system with already existing systems related to it. I think that's pretty rare. You should write something up about what you're doing and post it, it could be useful info. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp46sQLvSknV.pgp Description: PGP signature
Re: [GENERAL] Having trouble building 64-bit pgsql 7.4.17 on HPUX ia64
Why don't you use latest version of postgresql? On 8/8/07, Decibel! [EMAIL PROTECTED] wrote: On Tue, Aug 07, 2007 at 06:50:14PM +0530, Rajaram J wrote: Hi I'm having trouble building 64-bit pgsql 7.4.17 on the latest release of HP-UX 11.23 on ia64. I don't believe that's supported. There was just discussion about this, but I can't find it in the archive right now... -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Re: [GENERAL] Trigger not working as expected, first row gets a null value
I know that would be best but it'd be a major undertaking to try to repro this situation. I was hoping for some hints, 'it sounds like xyz', because I'm pretty sure I'm just tripping over a commonplace issue. Rodrigo De León-2 wrote: On Aug 8, 3:20 pm, [EMAIL PROTECTED] (novnov) wrote: ... I also don't understand ... DDL + sample data, please... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- View this message in context: http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12062974 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger not working as expected, first row gets a null value
On Wed, Aug 08, 2007 at 03:20:00PM -0700, novnov wrote: I know that would be best but it'd be a major undertaking to try to repro this situation. I was hoping for some hints, 'it sounds like xyz', because I'm pretty sure I'm just tripping over a commonplace issue. It doesn't have to be repoducable, but the definition of the tables involves + the code of the trigger would help. I read your description three times and still couldn't quite work out exactly what the problem was or what you were expecting to happen... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] backend process terminates
On Wed, Aug 08, 2007 at 08:50:41AM -0400, Geoffrey wrote: Correct on both counts. Many of the routines are wrapper routines used to assist in code portability. That ok in programs, but shared libraries need to be careful not to use names likely to be used by programs that use them. FWIW, this document has lots of information about ELF shared libraries. http://people.redhat.com/drepper/dsohowto.pdf There's a lot of technical stuff that you can skip, but there is a lot of info about scopes and how they are resolved, common problems and how to fix them. Have a nice, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Reordering columns, will this ever be simple?
On Wed, Aug 08, 2007 at 11:37:11AM -0500, Scott Marlowe wrote: If the danger of implementing this is some subtle bug that eats my data, I'd just as soon do without. It's not a feature I've ever felt the need for really. And there are lots of cool features I could think of I'd want before this. What I'm more interested in the possibility of rearranging the physical order of columns at the CREATE TABLE stage to optimise access to various fields (move fixed width fields to front) and to minimise cost of padding of said fields. I imagine this could provide useful savings on wide tables and multicolumn indexes, but you have to decouple logical and physical ordering to do it. But this is a thoroughly dead horse, lets not beat it up again. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Crosstab Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Hi All: I'm building an application along the lines of the questionnaire design on varlena.com [1], with slight modifications. In my design, there's a table called chartversionquestion that collects different questions into a bundle to create the questionnaire. Using a view [2] very similar to the one on varlena, I pull all the data from the value tables into one long list (cast to text). I would like to be able to create a crosstab from this view such that the short question name becomes a column header, with the related value underneath. Since the number of questions to be pivoted will vary per questionnaire the method should accept a number of columns parameter. The crosstab in the tablefunc contrib module expects the columns to be explicitly identified before hand. I suppose it could be possible to write a simple function to wrap the crosstab_n_cols functions to be able to return a variable number, but that seems hackish. Are there any other alternatives? David Blewett [1] http://www.varlena.com/GeneralBits/110.php [2] http://www.varlena.com/varlena/GeneralBits/Tidbits/QnA/views.sql -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGul6MZmlc6wNjtLYRCA5hAJ9fSHjNI5gQs/eqZ85bHl4ZrGWG3ACfbEnG zo1KFvZL8LraBESRySyVryg= =w1Z8 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Take your postgresSql on the road, and live to tell of it.
You can also have a trigger that records into a log table the id table of each record inserted/updated/deleted, and then it's a simple matter of merging changes from a certain point forward by searching that table and using the values of the current records. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Crosstab Question
On Aug 8, 2007, at 19:23 , David Blewett wrote: Since the number of questions to be pivoted will vary per questionnaire the method should accept a number of columns parameter. The crosstab in the tablefunc contrib module expects the columns to be explicitly identified before hand. I suppose it could be possible to write a simple function to wrap the crosstab_n_cols functions to be able to return a variable number, but that seems hackish. Are there any other alternatives? I've done just that in the past. It's pretty straightforward and has worked fine. Michael Glaesemann grzm seespotcode net PGP.sig Description: This is a digitally signed message part
Re: [GENERAL] tsearch2: plainto_tsquery() with OR?
On Wed, 8 Aug 2007, cluster wrote: Does anyone know where I can request an OR-version of plainto_tsquery()? plainto_tsquery expects plain text, use to_tsquery for boolean operators. I don't understand why it doesn't exist already: In most cases, when using user entered keywords to search for, there should be returned some rows even though not ALL keywords are matched. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] tsearch2: plainto_tsquery() with OR?
Oleg Bartunov [EMAIL PROTECTED] writes: On Wed, 8 Aug 2007, cluster wrote: Does anyone know where I can request an OR-version of plainto_tsquery()? plainto_tsquery expects plain text, use to_tsquery for boolean operators. Are either of these definitions really right? If I type foo bar baz into Google, for instance, it seems to produce some sort of weighted result, neither a strict AND nor a strict OR. Google didn't get where they are by misjudging what the simplest search behavior should be like. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2: plainto_tsquery() with OR?
On Thu, 9 Aug 2007, Tom Lane wrote: Oleg Bartunov [EMAIL PROTECTED] writes: On Wed, 8 Aug 2007, cluster wrote: Does anyone know where I can request an OR-version of plainto_tsquery()? plainto_tsquery expects plain text, use to_tsquery for boolean operators. Are either of these definitions really right? If I type foo bar baz into Google, for instance, it seems to produce some sort of weighted result, neither a strict AND nor a strict OR. Google didn't get where they are by misjudging what the simplest search behavior should be like. we provide strict basic query language via to_tsquery(), which could be a foundation for different ql. We need consensus here and we leave it for future. Someone could write google like ql, but I didn't see any description what exactly google does. Currently, people write their own search wrappers which implement google-like ql. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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
