Re: [GENERAL] surppressing column names in COPY format
I have some code from my project that does unload to file select and load to file insert ... Which would allow you to change column names or even table names through the load process. It wasn't submitted as a patch as I didn't think it added significant functionality, but apparently there is a need. Dave On Thu, 2003-07-31 at 17:59, Robert Treat wrote: On Thu, 2003-07-31 at 17:21, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: On Thu, 2003-07-31 at 16:50, Tom Lane wrote: None; we just haven't gotten around to removing code that no longer pulls its weight. The no-column-name variant is just as dangerous as it was in the COPY case, IMHO. by dangerous you mean functional right? :-) By dangerous I mean might not restore the table correctly. There are scenarios involving child tables and ALTER TABLE ADD COLUMN where a column-name-less INSERT or COPY will dump the data in a different column order than pg_dump's CREATE TABLE command will create. Before 7.3 it was in fact not possible to dump and reload the regression-test database using COPY, because of this problem --- perhaps that causes me to overstate its importance, but there is a definite risk. You do overstate it's importance IMHO, because I'm not lobbying for permanent removal of the column names, nor making the default to not have column names, I'm simply stating that the option to not have them provides benefits and I'm willing to take the risks associated with them. At least the work arounds are simple... :-\ Robert Treat -- Dave Cramer [EMAIL PROTECTED] fastcrypt ---(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
[GENERAL] tsearch2 parser
Hi, I wonder if this is the mailing list to go regarding to tsearch2. If not, could somebody direct me to the proper mailing list please ? Thanks. I am looking for more in-depth documentation regarding to the default parser that comes with tsearch2. I already read the Tsearch2 Reference but the documentation there is quite sparce. I'd also like to know the meaning of the fields in pg_ts_parser and what to do if I want to configure my own parser into tsearch2. Thanks. Fred
Re: [GENERAL] Hardware selection
On Fri, 25 Jul 2003 [EMAIL PROTECTED] wrote: The data will be stored on an external raid, SCSI based 2.5TB with IDE disks. Configured as 1 large volume, RAID5. ( We already have this hardware) How come you did not go with SCSI disks? Specially 15K ones. Performance will be much better with RAID 1+0/0+1 than with RAID 5. Dell Poweredge 6650 16GB RAM Quad intel hyperthreaded arch As others mentioned you may be better off going with a 64bit architecture. I have no experience with that, but you consider a SUN machine or a HP Alpha. PERC3Q controller 36GB System HDD approx 20K UKP My advice would be get just a 2 CPU machine, get a better disk subsystem and get only 8GB of RAM (or get 64bit and 16GB RAM). Any comments on better alternatives or modification to this system would be very helpful! You REALLY need to think out your DB structures and your index schemes. That could mean all the difference. A good DB organization will help you probably more than just simply throwing hardware at the problem. Question 3 - what would be the best way to distribute tables/swap/ system disk space? Throw it all at your RAID 1+0 device. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] COPY and domains
I noticed that COPY is getting very slow, when importing to table, which makes use of domains as column datatypes. It seems like foreign key validation is especially slow. It takes ages to import table with 25000 rows, which has foreign key reference to another table with 25000 rows. I tried the same tables without domains and it was much faster. Is this known problem? Version: PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3. Tambet ---(end of broadcast)--- TIP 3: 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
[GENERAL] Errors from Sub-select Not Showing Up
Has anyone else seen cases where errors from subselects are not being raised? I just had it happen again but can't find a simple case that reproduces it. The query is like this: insert into ... select (a bunch of CASE statements specifying a subselect to use if the value of the attribute is null)... from...(a messy JOIN) in which one of the CASE contains a subselect which has an Attribute not found error when executed independently. However, the only way it manifests itself is through a NOT NULL constraint not being satisfied in the insert. Should I spend any time trying to reproduce this in a simple case, or is there some known stupid way of making this happen? Thanks! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Error from Sub-Select, Simple Example.
OK, here's the deal. It seems that in this particular situation, if you reference an UNQUALIFIED field name in a subselect, the parser thinks it is just fine as long as that field name exists SOMEWHERE in the referenced tables Then, the error that gets generated gets lost in the mail. create table one ( oneidint, onevalue text not null); create table two ( twoidint, twovalue text); insert into one (oneid, onevalue) select 1, case when two.twovalue is null then (select twovalue from one where oneid = 1) else two.twovalue end from one left outer join two on one.oneid = two.twoid; This causes the not null constraint to fire, but if you run the subselect by itself, you get select twovalue from one where oneid = 1; ERROR: Attribute twovalue not found Is this a known issue? I know I should qualify field names where confusion might occur, but where did my error go? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Correct Allocation of UNICODE string in C
Hello All,
I'm struggling with the correct allocation of a
UNICODE text in a C function for PostgreSQL.
The strings are sometimes truncated, sometimes garbage
bytes are added at the end.
Is there a code example, that takes a UNICODE (UTF-8) text
of unknown length, allocates the PostgreSQL structure and copies
the data correctly?
You find the function in question below,
the full sources are available from
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/dcmms/arabic/
The problem is that the arabic_reshape() function will return texts
that are longer or shorter than the original text. In the PostgreSQL
sources I just found examples, where texts are copied - no example
how to allocate a fresh UTF-8 string.
Best Regards,
Steffen Macke
text *
shape_arabic(text *t)
{
glong items_read;
glong items_written;
long len;
long i;
text *new_t;
text *utf8_t;
len = g_utf8_strlen(VARDATA(t), -1);
new_t = (text *) palloc(VARHDRSZ+(len*4)+4);
VARATT_SIZEP(new_t) = VARHDRSZ+(len*4)+4;
utf8_t = (text *) palloc(VARSIZE(t)+4);
VARATT_SIZEP(utf8_t) = VARSIZE(t)+4;
memset(VARDATA(new_t), 0, (len*4)+4);
memset(VARDATA(utf8_t), 0, VARSIZE(utf8_t)-VARHDRSZ);
len = len*2;
arabic_reshape(len, VARDATA(t), VARDATA(new_t), ar_unifont);
g_ucs4_to_utf8(VARDATA(new_t), VARDATA(utf8_t), -1, items_read,
items_written);
len = g_utf8_strlen(VARDATA(utf8_t), -1);
return utf8_t;
}
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] COPY and domains
I noticed that COPY is getting very slow, when importing to table, which makes use of domains as column datatypes. It seems like foreign key validation is especially slow. It takes ages to import table with 25000 rows, which has foreign key reference to another table with 25000 rows. I tried the same tables without domains and it was much faster. PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3. Tambet ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] extract and time zones
Um, and what about this? $ uname -a FreeBSD caneli 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Fri May 9 02:30:11 CEST 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/CANELI i386 $ psql --version psql (PostgreSQL) 7.2.2 contains support for: readline, history, multibyte Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996, Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. create table t1( d timestamp(0) without time zone ); CREATE insert into t1 values( current_timestamp ); INSERT 26251 1 select * from t1; d - 2003-08-01 13:30:38 (1 row) select extract( epoch from d ) from t1; date_part 1059744638 (1 row) select extract( epoch from d::timestamp ) from t1; date_part 1059737438 (1 row) d :1059744638 d::timestamp : 1059737438 ?? Does 'epoch from d' check time zone and 'epoch from d::timestamp' doesn't? -- One ping to rule them all, one ping to find them, one ping to bring them all and in the darkness bind them. (Lord of the windows) This e-mail message and any attached files are intended SOLELY for the addressee/s identified herein. It may contain CONFIDENTIAL and/or LEGALLY PRIVILEGED information and may not necessarily represent the opinion of this company. If you receive this message in ERROR, please immediately notify the sender and DELETE it since you ARE NOT AUTHORIZED to use, disclose, distribute, print or copy all or part of the contained information. Thank you. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Error from Sub-Select, Simple Example.
Ian Harding writes: create table one ( oneidint, onevalue text not null); create table two ( twoidint, twovalue text); insert into one (oneid, onevalue) select 1, case when two.twovalue is null then (select twovalue from one where oneid = 1) else two.twovalue end from one left outer join two on one.oneid = two.twoid; This causes the not null constraint to fire, but if you run the subselect by itself, you get select twovalue from one where oneid = 1; ERROR: Attribute twovalue not found Is this a known issue? It's certainly known, but it's not an issue. ;-) I know I should qualify field names where confusion might occur, but where did my error go? twovalue gets resolved as a reference to the respective column in table two, which appears in the FROM clause of your query. The name space available to a subquery includes the outer query. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] tsearch2 parser
Fred Fung wrote: Hi, I wonder if this is the mailing list to go regarding to tsearch2. If not, could somebody direct me to the proper mailing list please ? Thanks. List at http://openfts.sourceforge.net/ I am looking for more in-depth documentation regarding to the default parser that comes with tsearch2. I already read the Tsearch2 Reference but the documentation there is quite sparce. I'd also like to know the meaning of the fields in pg_ts_parser and what to do if I want to configure my own parser into tsearch2. Read docs at http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/, espessially http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_in_Brief (but it on bad english :( ) -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] COPY and domains
On Friday 01 August 2003 08:45, Tambet Matiisen wrote: I noticed that COPY is getting very slow, when importing to table, which makes use of domains as column datatypes. It seems like foreign key validation is especially slow. It takes ages to import table with 25000 rows, which has foreign key reference to another table with 25000 rows. I tried the same tables without domains and it was much faster. Is this known problem? Version: PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3. I've found a few issues with domains in 7.3.x (note 7.3.4 is out and you should probably upgrade as soon as is convenient). Occasionally, it seems PG has trouble identifying operators that are valid and figuring out it can use an index. This is a shame because defining domains for things like tel_num or email is great. I think some refinements have been put into 7.4 -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Bug in comment parsing? [was: Re: [GENERAL] Comments in .sql files]
/* Check to see if a person's code is correct */ My Postgres 7.3.2 install chokes on the ' when trying to load a function from psql \i. Rory On 31/07/03, Tom Lane ([EMAIL PROTECTED]) wrote: Chad N. Tindel [EMAIL PROTECTED] writes: 1. It obviously doesn't like the # notation for comments. What is the proper way to put comments in schema files? The SQL-standard comment syntaxes are -- comment to end of line /* C-like comment, possibly multiple lines */ -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 3: 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] Comments in .sql files
Richard Huxton [EMAIL PROTECTED] writes: Note that you can also do multi-line comments /* like this */ That's not SQL standard AFAIK, It is standard in SQL99, but I don't see it in SQL92. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Table Stats
On Thu, 2003-07-31 at 23:23, Dennis Gearon wrote: It will only be at a given point in time, since datasbases are by design, realtime/concurrent applications. do: SELECT COUNT(*) FROM table_name; Should give you what you want. It'll give him what he wants, but not quickly, if it's a 10/20/etc M row table. One hack is: CREATE TABLE T_CARDINALITY ( RELATION_NAMECHAR(31) PRIMARY KEY, CARDINALITY BIGINT ); Then put ON INSERT/ON DELETE triggers on each table you want to track that increment/decrement T_CARDINALITY.CARDINALITY at the appropriate moment. Jeff Davidson wrote: Hi! Is there any quick way to determine how many rows exist in a given table? -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!| |unknown | +-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] UPDATE sql question
On Mon, 2003-07-28 at 03:24, Andrei Verovski wrote: Hi, What exactly will happen if UPDATE sql statement instructs to update some columns with the same values as already in the database? Will Postgres update only different values or it will simply modify all columns listed in UPDATE sql? You mean this: template1=# create table foo (f1 integer); CREATE TABLE template1=# insert into foo values (1); INSERT 16992 1 template1=# insert into foo values (2); INSERT 16993 1 template1=# insert into foo values (1); INSERT 16994 1 template1=# insert into foo values (3); INSERT 16995 1 template1=# select * from foo; 1 2 1 3 template1=# update foo set f1 = 1 where f1 = 1; UPDATE 2 template1=# select * from foo; 2 3 1 1 Looks like it does what you tell it to do... -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!| |unknown | +-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PC color icon data?
Title: PC color icon data? Howdy: Running PostgreSQL 7.2.1 on RedHat Linux 7.2. I know this will sound stupid, but can someone tell me what this means: 'PC color icon data' ? Background: I copy a table out to a .txt file by doing this: [snip] psql -U postgres -d bcn -c \\copy measures to '/tmp/fiveyears.txt' using delimiters '\|' [/snip] and when I use it elsewhere or if I type 'file' to make sure it's a text file, I get this: [snip message] [EMAIL PROTECTED] test]$ file fiveyears.txt fiveyears.txt: PC color icon data [/snip message] I am looking at the owner of this data and I see a pattern, but I've never heard of something like this. So, my *real* question should be: How does PostgreSQL figure out what the difference when copying a file? I have things like ASCII text, ASCII C++ program text, Netpbm PBM image text, Non-ISO extended-ASCII English text, with LF, NEL line terminators, etc ... and can I convert them to just ASCII? Thanks! -X
Re: [GENERAL] Table Stats
On 1 Aug 2003 at 9:14, Ron Johnson wrote: On Thu, 2003-07-31 at 23:23, Dennis Gearon wrote: It will only be at a given point in time, since datasbases are by design, realtime/concurrent applications. do: SELECT COUNT(*) FROM table_name; Should give you what you want. It'll give him what he wants, but not quickly, if it's a 10/20/etc M row table. One hack is: CREATE TABLE T_CARDINALITY ( RELATION_NAMECHAR(31) PRIMARY KEY, CARDINALITY BIGINT ); How about select reltuples from pg_class where relname='mytablename'; It is as accurate as your last vacuum.. Bye Shridhar -- Strategy: A long-range plan whose merit cannot be evaluated until sometime after those creating it have left the organization. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to do?
On Wed, 2003-07-30 at 07:17, Robert Partyka wrote: Hi, Have question How to do such like this: I have: select column list form tables where where statement; how to make one column be row numbers in result? and second one: have select like above and I know that in result is record with e.g. uid='AC13A1'. How to reduce result to this record and one record before in result and one record after in result? Are this questions just silly problems? :-) No, but slightly ambiguous, at least for my old brain. -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!| |unknown | +-+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] How to do?
On 1 Aug 2003 at 9:47, Ron Johnson wrote: On Wed, 2003-07-30 at 07:17, Robert Partyka wrote: Hi, Have question How to do such like this: I have: select column list form tables where where statement; how to make one column be row numbers in result? select oid,name from a; It's oid. Of course you need to have them enabled since they are optional in recent versions.. and second one: have select like above and I know that in result is record with e.g. uid='AC13A1'. How to reduce result to this record and one record before in result and one record after in result? I didn't get that.. could you please elaborate? Bye Shridhar -- Not me, guy. I read the Bash man page each day like a Jehovah's Witness readsthe Bible. No wait, the Bash man page IS the bible. Excuse me...(More on confusing aliases, taken from comp.os.linux.misc) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] extract and time zones
Oscar Estevez Lopez [EMAIL PROTECTED] writes: d :1059744638 d::timestamp : 1059737438 ?? This is fixed as of PG 7.3.4 ... 2003-02-27 16:37 tgl * src/backend/utils/adt/timestamp.c (REL7_3_STABLE): Change EXTRACT(EPOCH FROM timestamp) so that a timestamp without time zone is assumed to be in local time, not GMT. This improves consistency with other operations, which all assume local timezone when it matters. Per bug #897. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PC color icon data?
How does PostgreSQL figure out what the difference when copying a file? It doesn't. 'file' does. The data you export just happens to look like the formats 'file' thinks it is in. And maybe it is. Have you tried actually *looking* at the data in question ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Bug in comment parsing? [was: Re: [GENERAL] Comments in .sql files]
Rory Campbell-Lange [EMAIL PROTECTED] writes: /* Check to see if a person's code is correct */ My Postgres 7.3.2 install chokes on the ' when trying to load a function from psql \i. Works fine for me. Could we see a complete example? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Domains (Was [PERFORM] Views With Unions)
On Fri, 2003-08-01 at 12:26, Francisco J Reyes wrote: On Fri, 1 Aug 2003, Tom Lane wrote: [snip] accros some tables. Currently I used inheritance to enforce the consitency since a good number of fields needed to be common among the tables AND the inheritted tables are basically a supperset of the data, so some times I would want to access the inheritted tables and other times the parent/main table. Isn't this when you'd really want child tables, instead? -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!| |unknown | +-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] last inserted raw (identity)
On Fri, 2003-08-01 at 18:25, b b wrote:
Is there an environment variable that returns the
primary key of the last inserted row. This is usefull
if you insert a rwo and need the primary key to insert
it into another table as a foreign key.
In MS-SQL that is equivalent to @@identity variable:
insert into organization ('org name', )
insert into contact (@@identity, 'contact name'
.)
Here Identity is the organization's ID that is needed
as a foreign key in contact table.
Why environment variable? Are you writing this in shell?
--
+-+
| Ron Johnson, Jr.Home: [EMAIL PROTECTED] |
| Jefferson, LA USA |
| |
| I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!|
|unknown |
+-+
---(end of broadcast)---
TIP 3: 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
[GENERAL] plPHP -- sort of an announcement.. but not commercial
Hello, As a recent flurry of activity has commenced within Command Prompt we have released upon this rather unround earth, plPHP. Yes it is trigger safe, yes you can write UDF's in PostgreSQL with PHP now. Find it here: http://www.commandprompt.com/entry.lxp?lxpe=260 Have a glorius weekend. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plPHP -- sort of an announcement.. but not commercial
On Fri, 2003-08-01 at 19:06, Joshua D. Drake wrote: Hello, As a recent flurry of activity has commenced within Command Prompt we have released upon this rather unround earth, plPHP. Yes it is trigger safe, yes you can write UDF's in PostgreSQL with PHP now. Find it here: http://www.commandprompt.com/entry.lxp?lxpe=260 Is there a possibility of getting this in v7.5? -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!| |unknown | +-+ ---(end of broadcast)--- TIP 3: 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] Apache - DBI - Postgresql: Cancelling queries
The solution I finally implemented seems to be pretty good, graying out the button after it's pushed with javascript. That means no more doubleclick problem, and no more hammering away at the same button. It does not preclude the reloading of the page (reactivating the button) or just going somewhere else and issuing another query. The real solutions involving cute little client side applets that phone home to the server to see if the query is still running and show a phony status bar seem like too much work and still don't prevent malicious wankers from issuing multiple queries. Good luck! Mat wrote: I am having trouble with users firing off a query from the web interface, and then getting bored of waiting and firing off a different query. As http is stateless apache, (and so the perl cgi script) only realises that the user has gone when it ties to send data back to the user and gets a broken pipe type response. This is usually too late as by this time the query has completed, using up valuable resources. Is there a tried and tested solution to this problem? If so please let me know! If not... I am working on a work around at the moment but have run into trouble! I have read the DBI man pages but there doesn't seem to be a cancel query function implemented, can anyone confirm or deny this? Can i send some control sequence using DBI to cancel the query? I had taken the approach of having two perl threads, one to run the query and one to poll the browser every second to see if the user is still there. Plan X was then to cancel the query if the user had ended the connection. The first problem was the lack of cancel query, second problem seems to be that the DBI database handles cannot be shared between thread - so i will have to pass a signal to the thread waiting for query to return to cancel it? anyone else tried this? any more gaping pitfalls that i should be aware of?! Thanks! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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
[GENERAL] Help with BEGIN/COMMIT within a transaction
I aplogoize in the first place, if this is a silly question. But as silly as it sounds it has been giving me a hard time. I need to use BEGIN/COMMIT within a stored procedure and almost all the syntax (e.g. BEGIN ... COMMIT, START ... COMMIT, BEGIN WORK ... COMMIT WORK etc.) gives me an error when I try to execute (not when I compile) the stored procedure. Attached is the script to reproduce the problem. Is there a compile time option or a server setting that I need to enable ? It does COMMIT when it exits from the stored procedure, but thats not I want. I want to commit from within a cursor loop so that the changes are visible in other sessions as soon as they are done. === create table script == create table employee ( id integer, name text ); === stored procedure = CREATE OR REPLACE FUNCTION sp_test() RETURNS TEXT AS ' BEGIN START INSERT INTO employee (id, name) values (1, ''postgres''); COMMIT; return ''OK''; END; ' LANGUAGE plpgsql; === invoking the stored procedure thru psql === test=# select sp_test() as status; WARNING: plpgsql: ERROR during compile of sp_test near line 3 ERROR: parse error at or near ; test=# __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] error while loading shared libraries: libpq.so.3
Hi all! I would like some suggestion on how to solve that: postgres$ createdb postgres/usr/local/postgresql-7.3.4/bin/psql: error while loading shared libraries: libpq.so.3: cannot open shared object file: No such file or directorycreatedb: database creation failedpostgres$postgres$ uname -aLinux PgServer.prodam 2.4.18-14 #1 Wed Sep 4 11:57:57 EDT 2002 i586 i586 i386 GNU/Linux thanks a lot! \\\!/ 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
Re: [GENERAL] error while loading shared libraries: libpq.so.3
On Friday 01 August 2003 12:54, Elielson Fontanezi wrote: Hi all! I would like some suggestion on how to solve that: postgres$ createdb postgres /usr/local/postgresql-7.3.4/bin/psql: error while loading shared libraries: libpq.so.3: cannot open shared object file: No such file or directory createdb: database creation failed postgres$ postgres$ uname -a Linux PgServer.prodam 2.4.18-14 #1 Wed Sep 4 11:57:57 EDT 2002 i586 i586 i386 GNU/Linux You probably need to add your library folder to /etc/ld.so.conf and then run ldconfig. Alternatively, you could symlink from /usr/local/lib to the library and do the same for the binaries - that would mean you could skip the leading path when running psql. You can find the correct directory with: find /usr/local -name 'libpg.so.3' There should be something on this in the installation instructions. In any case, googling for ldconfig should find you some help. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: 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] [SQL] ALTER TABLE ... DROP CONSTRAINT
Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) \\\!/ 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/ 7.3 supports the drop constraint. The only exception begin if you upgraded your database. It keeps the existing trigger like constraints if youve moved from 7.1 or 7.2. But these triggers can be dropped as well. HTHChad P.S. Great signature! :-)
Re: [GENERAL] [SQL] ALTER TABLE ... DROP CONSTRAINT
It works in 7.3.2. George - Original Message - From: Elielson Fontanezi To: pgsql-general ; pgsql-sql Sent: Wednesday, July 30, 2003 10:52 AM Subject: [SQL] ALTER TABLE ... DROP CONSTRAINT Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) \\\!/ 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
[GENERAL] OSDL DBT-2 for PostgreSQL
Hi everyone, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. v0.10 is available on SourceForge at: http://prdownloads.sourceforge.net/osdldbt/dbt2-v0.10.tar.gz?download We keep the source in BitKeeper at: bk://developer.osdl.org/dbt2 For anyone interested in more discussion on our workloads, we have a mailing list setup at: [EMAIL PROTECTED] -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503)-626-2455 x 32 (office) (503)-626-2436 (fax) http://www.osdl.org/archive/markw/ ---(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] [HACKERS] OSDL DBT-2 for PostgreSQL
Mark, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. I'm confused. Jenny Zhang just announced OSDL-DBT3 for Postgres; is this a different test or does one of you have the name wrong? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] OSDL DBT-2 for PostgreSQL
On Fri, Aug 01, 2003 at 05:05:18PM -0700, Josh Berkus wrote: Mark, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. I'm confused. Jenny Zhang just announced OSDL-DBT3 for Postgres; is this a different test or does one of you have the name wrong? Yeah, this is a different test. DBT-3 is based on the TPC-H and DBT-2 is based on the TPC-C. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OSDL DBT-2 for PostgreSQL
On Fri, 2003-08-01 at 20:32, Mark Wong wrote: On Fri, Aug 01, 2003 at 05:05:18PM -0700, Josh Berkus wrote: Mark, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. I'm confused. Jenny Zhang just announced OSDL-DBT3 for Postgres; is this a different test or does one of you have the name wrong? Yeah, this is a different test. DBT-3 is based on the TPC-H and DBT-2 is based on the TPC-C. Josh, All 3 (DBT-1 is another style of test) should be included into the benchmark kit. It should be noted that the DBT tests are fairly linux specific at the moment, though that doesn't take much to change. OSDL has been happy to take portability patches. signature.asc Description: This is a digitally signed message part
Re: [GENERAL] CREATE TABLE with a column of type {table name}
elein [EMAIL PROTECTED] writes: IMNSHO a type is a type is a type. An instance of anytype can go in a column. However, having worked in some of the code to make this recursive definition work correctly I understand your No! Never! response. I don't agree, with it, but I understand it :-) I didn't say No! Never!. I said this isn't a matter of fixing a small bug. For starters, the apparent support for the feature needs to be ripped out, because it has nothing in common with actual support. regards, tom lane ---(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] Monthly table partitioning for fast purges?
On Fri, Aug 01, 2003 at 01:46:54PM -0700, Roger Hand wrote: We are moving an application from Oracle 8i to Postgres and I've run into a problem attempting to duplicate a feature we currently use. In Oracle you can divide a table into partitions. We use this feature to break up the data by month. Each month we store several tens of millions of rows in a particular table, and each month we drop the partition that's a year old. In other words, we always keep the last 12 months of data (12 partitions). This is clean and fast. Since the partition is by a timestamp column, it also gives us a certain amount of automatic indexing. Postgres doesn't support table partitions (correct me if I'm wrong!) so the only option appears to be to dump everything into one big table. What I'm worried about is the purging of the data from 12 months ago ... I'm worried that this will be a slow and expensive operation. Does anyone have any advice for how best to handle this? I feel your pain! No, PortgreSQL doesn't support this. There were some proposals recently on -hackers but there didn't seem to be a great deal of interest. The best solution I've come up with is by creating base tables for each year by hand and using a view to combine them. You can create RULEs to automatically move new data to various tables. As long as you're not doing UPDATEs you can avoid a lot of the complexity. Similar effects can be acheived using inheritance. Good luck! -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ All that is needed for the forces of evil to triumph is for enough good men to do nothing. - Edmond Burke The penalty good people pay for not being interested in politics is to be governed by people worse than themselves. - Plato pgp0.pgp Description: PGP signature
[GENERAL] Using contrib/fulltext on multiple tables.
I am running into a problem with using contrib/fulltext on several tables. Here's the details... I have one sql script which drops everything and recreates the database structure, then populates it with some test data. In this script I create tables a,b and c and setup the triggers for fulltext indexing (as well as associated tables for the index data). Later in the script when inserting into these tables via stored procedures, the full text indexes get created for a and b but not c. It's just empty. If I just have a and c setup in the script, they both work. Same for a and b. It seems to be a problem with b and c. Something else that is odd is I can then call the Insert stored procedure for c and it's index doesn't get created. However, if I exit and reenter the psql client app, THEN run the Insert stored procedure, the index is created just fine. I've tried breaking it into two scripts (one for db structure and one for test data) but it still doesn't do the indexing on c. Any Ideas? I apologize for the length, Eric ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] last inserted raw (identity)
On Fri, Aug 01, 2003 at 04:25:05PM -0700, b b wrote:
Is there an environment variable that returns the
primary key of the last inserted row. This is usefull
if you insert a rwo and need the primary key to insert
it into another table as a foreign key.
In MS-SQL that is equivalent to @@identity variable:
insert into organization ('org name', )
insert into contact (@@identity, 'contact name'
.)
Here Identity is the organization's ID that is needed
as a foreign key in contact table.
See currval() and nextval().
--
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
All that is needed for the forces of evil to triumph is for enough good
men to do nothing. - Edmond Burke
The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves. - Plato
pgp0.pgp
Description: PGP signature
Re: [GENERAL] last inserted raw (identity)
On Fri, 2003-08-01 at 22:27, Martijn van Oosterhout wrote:
On Fri, Aug 01, 2003 at 04:25:05PM -0700, b b wrote:
Is there an environment variable that returns the
primary key of the last inserted row. This is usefull
if you insert a rwo and need the primary key to insert
it into another table as a foreign key.
In MS-SQL that is equivalent to @@identity variable:
insert into organization ('org name', )
insert into contact (@@identity, 'contact name'
.)
Here Identity is the organization's ID that is needed
as a foreign key in contact table.
See currval() and nextval().
What if his PK isn't a sequence?
--
+-+
| Ron Johnson, Jr.Home: [EMAIL PROTECTED] |
| Jefferson, LA USA |
| |
| I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!|
|unknown |
+-+
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] plPHP -- sort of an announcement.. but not commercial
On Fri, Aug 01, 2003 at 05:06:00PM -0700, Joshua D. Drake wrote: Hello, As a recent flurry of activity has commenced within Command Prompt we have released upon this rather unround earth, plPHP. Yes it is trigger safe, yes you can write UDF's in PostgreSQL with PHP now. Very nice. Is it in the public domain? I don't see any copyright nor license mention in the source code... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth. That's because in Europe they call me by name, and in the US by value! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Using contrib/fulltext on multiple tables.
Eric Johnson [EMAIL PROTECTED] writes: I am running into a problem with using contrib/fulltext on several tables. That sounds awfully weird. Could we see a complete, reproducible example? And what PG version are you using, anyway? (Might be better to continue this on pgsql-bugs than -general.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] last inserted raw (identity)
On Fri, Aug 01, 2003 at 10:43:03PM -0500, Ron Johnson wrote:
On Fri, 2003-08-01 at 22:27, Martijn van Oosterhout wrote:
On Fri, Aug 01, 2003 at 04:25:05PM -0700, b b wrote:
Is there an environment variable that returns the
primary key of the last inserted row. This is usefull
if you insert a rwo and need the primary key to insert
it into another table as a foreign key.
In MS-SQL that is equivalent to @@identity variable:
insert into organization ('org name', )
insert into contact (@@identity, 'contact name'
.)
Here Identity is the organization's ID that is needed
as a foreign key in contact table.
See currval() and nextval().
What if his PK isn't a sequence?
Hadn't thought of that. It would have been part of the insert then, in which
case it may be possible for the application to grab it internally.
--
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
All that is needed for the forces of evil to triumph is for enough good
men to do nothing. - Edmond Burke
The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves. - Plato
pgp0.pgp
Description: PGP signature
Re: [GENERAL] Using contrib/fulltext on multiple tables.
Sure thing, I'll get subscribed to -bugs and send it! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Friday, August 01, 2003 11:06 PM To: Eric Johnson Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Using contrib/fulltext on multiple tables. Eric Johnson [EMAIL PROTECTED] writes: I am running into a problem with using contrib/fulltext on several tables. That sounds awfully weird. Could we see a complete, reproducible example? And what PG version are you using, anyway? (Might be better to continue this on pgsql-bugs than -general.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] last inserted raw (identity)
See currval() and nextval(). What if his PK isn't a sequence? Moreover, currval() and nextval() won't guarantee that you always get the most recently inserted sequence value, either, because each connection can have a cache of sequence values to assign from. While the backend guarantees that each sequence value will be UNIQUE there is no guarantee that MY currval() or nextval() is actually based on the last sequence value that ANYONE used. In short, I think the answer to the original question is that there is no reliable way to find out what the last record inserted was. -- Mike Nolan ---(end of broadcast)--- TIP 3: 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] last inserted raw (identity)
On Fri, Aug 01, 2003 at 11:18:30PM -0500, [EMAIL PROTECTED] wrote: See currval() and nextval(). What if his PK isn't a sequence? Moreover, currval() and nextval() won't guarantee that you always get the most recently inserted sequence value, either, because each connection can have a cache of sequence values to assign from. While the backend guarantees that each sequence value will be UNIQUE there is no guarantee that MY currval() or nextval() is actually based on the last sequence value that ANYONE used. Rubbish. currval() is guarenteed to return the last value returned by nextval() *in this session*. So if you do a nextval() and sleep for three days with the session open, currval() will return that value even if a million records have been entered in the mean time. In short, I think the answer to the original question is that there is no reliable way to find out what the last record inserted was. It returns the last record *you* entered. If you want the last record entered by anyone (committed ofcourse), you'd use order by x desc limit 1. In general, currval() and nextval() do exactly what you need. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ All that is needed for the forces of evil to triumph is for enough good men to do nothing. - Edmond Burke The penalty good people pay for not being interested in politics is to be governed by people worse than themselves. - Plato pgp0.pgp Description: PGP signature
Re: [GENERAL] plPHP -- sort of an announcement.. but not commercial
Hello, Well PHP is GPL... thus we left it GPL... it belongs to the community. Enjoy :) J Alvaro Herrera wrote: On Fri, Aug 01, 2003 at 05:06:00PM -0700, Joshua D. Drake wrote: Hello, As a recent flurry of activity has commenced within Command Prompt we have released upon this rather unround earth, plPHP. Yes it is trigger safe, yes you can write UDF's in PostgreSQL with PHP now. Very nice. Is it in the public domain? I don't see any copyright nor license mention in the source code... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] plPHP -- sort of an announcement.. but not commercial
Hello, I am in final testing now, so feel free to download the static. It is a pain in the but to compile (static is Linux). By the end of the week it should be all pacakaged etc... J Gavin M. Roy wrote: This is very exciting, is this stable, production ready, etc? Gavin Alvaro Herrera wrote: On Fri, Aug 01, 2003 at 05:06:00PM -0700, Joshua D. Drake wrote: Hello, As a recent flurry of activity has commenced within Command Prompt we have released upon this rather unround earth, plPHP. Yes it is trigger safe, yes you can write UDF's in PostgreSQL with PHP now. Very nice. Is it in the public domain? I don't see any copyright nor license mention in the source code... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] last inserted raw (identity)
In short, I think the answer to the original question is that there is no reliable way to find out what the last record inserted was. It returns the last record *you* entered. If you want the last record entered by anyone (committed ofcourse), you'd use order by x desc limit 1. I agree that currval() would return the last record *I* inserted, but I don't understand how an order by clause would always return the last record that *ANYBODY* inserted. (And I thought that was the original question, but perhaps I am mis-remembering it at this point.) If another user has a cache of 10 sequence values, there's no way for me to know that. Thus when the other user inserts a record using one of those cached sequence values, it could be either higher or lower than the sequence value in MY most recently inserted record. (I think it could be higher only if I also have my own cache of sequence values, assuming that the sequence never wraps.) -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
