[GENERAL] libpq.so.3 not found error while use Perl DBI
I'm using Postgresql 7.3.4, FreeBSD 4.7, Apache 2.0.47. I have a perl script which uses the DBI module and it works great from the command line as long as I have '/usr/local/pgsgl/lib' in LD_LIBRARY_PATH or else it would give me an 'libpq.so.3 not found' error. Now, I've turned that perl script into a cgi and from the apache error log I see the 'libpq.so.3 not found' message. My own user's LD_LIBRARY_PATH is set correctly of course but could it be because the apache user's LD_LIBRARY_PATH isn't set properly that this is occurring? I believe I've used the ldconfig and other tools to set the library path but I'm not getting anywhere. Can anyone help? - Jake ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PG tools
Hi, I am new to postgresql. I have done some small applications with MS Access. Are there some PG tools out there that can build applications like MS Access does out there? Thanks Gary ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Interfaces that support cursors
[EMAIL PROTECTED] (Doug McNaught) writes: Network Administrator [EMAIL PROTECTED] writes: Ok, I did see the autocommit flag setting in DBD:Pg when I starting reading up on the DBI/DBD interfacing methods so I guess I could recode for that. However, how do you maintain the current transaction open if your script is writing pages to the web. Even in mod_perl I think that there is a commit after the script ends, no? Oh, right--I didn't get that bit of your problem. I think the conventional wisdom on this is that keeping transactions open across web page deliveries is a Bad Idea. If you're just doing the standard show N records per page thing, you can use LIMIT and OFFSET on your SELECT call. This is going to be slower thn using a transaction (because you're re-executing the query for every page) but is fairly simple. If the set of data is pretty complex, this can Suck Really Badly. A developer recently came to me with a more or less pathological case where LIMIT/OFFSET on a particular query made it run for about 3000ms, whereas dropping the LIMIT dropped query time to 75ms. The problem was that the table was big, and the ORDER BY DATE caused the LIMIT to force an index scan on the DATE field, when it would have been preferable to use an index scan on customer ID, and sort the resulting result set. I haven't tried to punt that problem over to [PERFORM] because it's pretty clear that a CURSOR is a better idea, as you suggest next. If you really want to have a DB transaction that covers multiple page views, you need some kind of persistent application server rather than CGI scripts, so you can keep open connections and application state around. Right you are. The challenge, of course, is of how to properly expire these objects. -- (format nil [EMAIL PROTECTED] cbbrowne libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(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] Redhat RPMs
Oops! [EMAIL PROTECTED] (Nigel J. Andrews) was seen spray-painting on a wall:
I've not looked at many RPMs but I must say that the few I have have
never been relocatable. Can the postgresql RPMs not be made
relocatable?
Unfortunately, relocation would have to include the init scripts, and
that would be pretty hairy. The notion of relocatable RPMs came up
early in its design, but the only case where that will be particularly
usable is if the components are mostly binaries that only make
relative path references. That situation is unusual, to say the
least.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
http://www.ntlug.org/~cbbrowne/postgresql.html
MICROS~1 is not the answer.
MICROS~1 is the question.
NO (or Linux) is the answer.
---(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] Can SQL return a threaded-comment-view result set?
Hello all, I've been meaning to get back to you all but I just haven't had time. Okay, I've got a little bit of time now so here goes I received many useful answers from many of you including Tom Lane, Joe Conway, and Josh Berkus. Max Nachlinger in particular on October 5th (which was my birthday) sent me a large amount of threaded discussion forum code of his own. (Nice birthday present, Max. Thank you.) I will be investigating his solution when I have more time since his is almost certainly more efficient than my own. My own solution is a 20-line PL/pgSQL function I put together after reading the 7.3 docs at postgresql.org. It requires no modifications to my original example table definitions other than that I decided to use a 0 value instead of a NULL value for the in_reply_to column when a message isn't a reply, because that way my plpgsql function doesn't have to treat NULL as a special case. In particular, my solution doesn't require a message to keep pointers to its children. If a message is a reply, it simply points to its parent's id via in_reply_to. You can add as many messages as you want with just single simple INSERT statements; you don't have to do any tree-refactoring or updating to the parent. The downside is that while insert speed couldn't be any better and inserting couldn't be any easier, building the threaded view seems rather algorithmically inefficient, and in almost all applications optimising for obtaining the threaded view rather than insert speed is more important. One probably couldn't base even a moderate-load application on this solution, but if one wanted to anyways I suppose an in-memory tree representation could be maintained which allows new messages to be linked into the in-memory tree efficiently as they're inserted into the database, and then whenever the application is shutdown and reloaded it could rebuild that in-memory representation on startup. Or something. And until you run out of memory (Also, simply caching the results of queries could be effective if you have many identical queries producing identical results [which my application does] so this solution might not work too bad for me.) For the sake of googlers and like novices reading this, I've adapted my PL/pgSQL function so that it works with the original example I posted. (My real code uses more fields, different types, and has some other subtle differences because there's more than one type of table to consider and there are foreign key constraints.) After loading the below code, evaluating select * from threadview(0, 0); builds a table like the one I wanted in my original posting. ---Chris = -- This code originally due to Chris Barry, http://www.goodfig.org/feedback -- It's hereby placed in the public domain. These public domain licenses -- usually have some sort of warning about no guarantee of fitness for a particular -- purpose, etc. Well, the below code is DEFINITELY not fit for any purpose! So, -- use it at your own peril. Caveat emptor. -- drop database discussion; create database discussion; \c discussion -- The path to plpgsql.so may need to be edited for your system. create function plpgsql_call_handler() returns opaque as '/usr/local/pgsql/lib/plpgsql.so' language 'c'; create language 'plpgsql' handler plpgsql_call_handler lancompiler 'PL/pgSQL'; create table messages ( message_id integer, in_reply_to integer, created date, author varchar(20), title varchar(30), message varchar(256), primary key (message_id) ); -- A threadrow is the same thing as a row from the messages table -- except a nesting integer has been added so the client knows how -- much to indent the thread message. I'm not sure if there's a -- syntax that makes it unnecessary to duplicate the redundant -- information from the messages table (e.g inheritance). create type threadrow as ( message_id integer, in_reply_to integer, created date, author varchar(20), title varchar(30), message varchar(256), nesting integer ); create or replace function threadview(int, int) returns setof threadrow as ' declare p alias for $1; -- p is the parent i alias for $2; -- i is the indentation (nesting) c threadrow%rowtype; c2 threadrow%rowtype; begin for c in select *, 0 as nesting from messages where in_reply_to = p order by created asc loop c.nesting = i; return next c; for c2 in select * from threadview(c.message_id, i+1) loop return next c2; end loop; end loop; return; end; ' language 'plpgsql'; -- Load the table with some example data: insert into messages values (1,0, '2003-09-01', 'John', 'Favorite DB?',
[GENERAL] CREATE INDEX question (how to index on money field?)
QUICK VERSION: How do I create an index on a field of type MONEY? - LONG VERSION: I have a table with a field of type money. I very often need to access records by the purchase price so I thought I'd create an index to help out my selects: CREATE INDEX purchasepriceidx ON mytable (purchaseprice); This results in the following: ERROR: data type money has no default operator class for access method btree You must specify an operator class for the index or define a default operator class for the data type So if I understand this message correctly, it means that money has no comparison operators associated with it? I RTFM'd and found the 51 available operators (SELECT am.amname AS acc_method, opc.opcname AS ops_name FROM pg_am am, pg_opclass opc WHERE opc.opcamid = am.oid ORDER BY acc_method, ops_name), but I don't see anything here that seems to relate to money type fields. Am I correct about my assuptions? Anyone have suggestions they can share? Any simple examples posted anywhere? PostgreSQL 7.3.4 on i586-pc-linux-gnu, compiled by GCC 2.96 Thanks, Stephane Charette stephanecharette @@@ telus ... net ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] previous next buttons
hi all, im developing a site in perl with mason on a postgreSQL backend. i currently have a page that lists all items in a table, but would like to do the PREVIOUS 1 2 3 4 NEXT sort of thing - say LIMIT of 10 records to a page. can someone help me out. thanks PP ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to list which tables are available?
It seems so elementary, but how I get a list of which tables are available in a database. I can't find an SQL command for this, but there must be a way! Thanks. Bertwim ---(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] Question
Hi, I have question: why such condition: foofield not like '%bar%' where foofield is varchar returns false (or rather even ignore row) on record where foofield is null but returns true on records where foofield is '' (empty string) regards Robert ---(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] simple remote user access question - pg_hda.conf
I just discovered that SuSE provides the sample pg_hba.conf in /usr/share/pgsql but the real pg_hba.conf file is located in /var/lib/pgsql/data Fixed! ---(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] need for concrete info
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Andrew Sullivan) would write: On Sat, Oct 11, 2003 at 05:48:23PM -0700, Dennis Gearon wrote: projects.) I want to use as my main argument, the fact (at this time, only from my previous usage), that MySQL really doesn't have foreign keys or record locking, and Postgres does. Define really. Certainly, for some cases, it now does. correct with today's MySQL vs. PostgreSQL, right? I *really* want to use PostgreSQL for this project and not MySQL as I want to avoid growing pains trying to get MySQL to do the job of a bigger DB down the road. Why don't you make the growing pains argument instead? What are those arguments, anyway? ( I think I know, but maybe not.) I would think that the 'non-validation of domain information' part would be an even better argument. It's easy to explain, which is vital. You can give examples: If we try to insert such-and-such data, which happens to be wrong, MySQL will silently insert _different_ wrong information, and not complain at all. In contrast, they can put all sorts of extra validation tests on domains in PostgreSQL, and it can quietly _prevent_ application bugs from corrupting data. That doesn't mean that _every_ sort of corruption is prevented, but there can be some meaningful ones. For instance, if a particular column is required to be in lower case, then a domain constraint to that effect means that if someone makes an application mistake, the database will catch it. Sort of like wearing a belt _and_ suspenders. -- (format nil [EMAIL PROTECTED] aa454 freenet.carleton.ca) http://www3.sympatico.ca/cbbrowne/advocacy.html If at first you don't succeed, try duct tape. If duct tape doesn't work, give up. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] backend exit mystery
On Friday October 10 2003 4:46, Ed L. wrote:
I have libpq client program that repeatedly connects to a DB, queries,
and then disconnects. After a seemingly random number of such successful
sessions (sometimes 30, sometimes hundreds), the backend mysteriously
exits after the client calls PQsetdbLogin(), and the client hangs. Any
clues? Details below...
Client: C program linked with 7.2.1 libpq on HP-UX B.11.00 E 9000/803.
Server: PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96.
Still looking for clues as to the cause of this repeatable connection
failure. Passing an explicit connection timeout to PQconnectdb() escapes
from any long hangs, but the hanging is still an issue I'd like to
understand. Attached is a small C program that reliably reproduces this
problem on the setup above. I added an explicit timeout to PQconnectdb()
to wait only 30 seconds. I'm curious to know if anyone can easily repeat
the problem (careful, it will generate a bit of traffic, cpu load, and run
forever). My last example run showed 17 timeouts seemingly randomly
dispersed among 5000 consecutive connection attempts.
The server has plenty of available memory on a dual processor machine
running Linux 2.4.18-3smp. Tried to catch snapshot data from netstat on
Recv-Q and Send-Q sizes on the server during a hang... that's a little iffy
with the timing of grepping netstat output, but seems like the server's
Recv-Q's were always zero and the Send-Q's were occasional in the tens
(bytes?).
TIA for any help.
/*
* test program to demonstrate connection hangs
*/
#include stdio.h
#include libpq-fe.h
static int tries = 0;
static int successes = 0;
void logmsg(const char *msg) {
char timestamp[256];
time_t t;
t = time(NULL);
strcpy(timestamp, asctime(localtime(t)));
timestamp[strlen(timestamp)-1] = '\0';
fprintf(stderr,%s [%d] %s\n, timestamp, getpid(), msg);
fflush(stderr);
}
const char* get_dbhname(PGconn *conn, char *dbhname)
{
sprintf(dbhname, [EMAIL PROTECTED]:%s:%s:%d,
PQuser(conn), PQhost(conn), PQport(conn),
PQdb(conn), PQbackendPID(conn));
return dbhname;
}
main()
{
charmsg[1024];
chardbhname[1024];
PGconn *conn;
PGresult *res;
if ( ! (getenv(PGPORT) getenv(PGHOST)
getenv(PGUSER) getenv(PGDATABASE) ) ) {
logmsg(Please export PGPORT, PGUSER, PGHOST, and PGDATABASE for me.);
exit(-1);
}
sprintf(dbhname, [EMAIL PROTECTED]:%s:db=%s,
getenv(PGUSER), getenv(PGHOST),
getenv(PGPORT), getenv(PGDATABASE));
while ( 1 ) {
tries++;
sprintf(msg,[%d/%d] Connecting to %s, tries, successes, dbhname);
logmsg(msg);
conn = PQconnectdb(connect_timeout=30);
if (PQstatus(conn) == CONNECTION_BAD)
{
sprintf(msg, Connection to database '%s' failed.,
getenv(PGDATABASE));
logmsg(msg);
sprintf(msg, %s, PQerrorMessage(conn));
logmsg(msg);
PQfinish(conn);
return 0;
}
successes++;
res = PQexec(conn, SELECT version());
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
sprintf(msg, SELECT query failed);
logmsg(msg);
PQclear(res);
PQfinish(conn);
return 0;
}
PQclear(res);
sprintf(msg,[%d/%d] Closing connection to %s,
tries, successes, get_dbhname(conn,dbhname));
logmsg(msg);
PQfinish(conn);
}
return 0;
}
---(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] Transaction Queries!!!
Hi,
We are using postgresql as the underlying RDBMS for one of our
application.
When in a transaction if we execute an query that causes database to
return
a failure. The whole transaction gets roll backed.
Below are 2 examples
which illustrate this problem. We want the rollback to be in control of
the user. And that is why as i understand we have the postgresql rollback
command.
Examples of implicit rollback:
1
cpdb=# begin;
BEGIN
cpdb=# insert into abc ('80219');
ERROR: parser: parse error at or near '
cpdb=# insert into abc values ('80219');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
cpdb=#
2
cpdb=# begin;
BEGIN
cpdb=# insert into abc values ('-1');
ERROR: ExecAppend: rejected due to CHECK constraint abc_i
cpdb=# insert into abc values ('1');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
cpdb=#
A another problem is of executing update query thru ODBC API's. When we
run update query thru executeQuery() API, even though the update query may
not go thru as some column condition may not have met or the where clause
may have been wrong. In such case also the API returns success.
When the query is executed thru psql prompt it says zero rows updated.
Has this problem got to do with postgresql or ODBC API?
kindly help!!!
TIA
regards
vatsal
**Disclaimer
Information contained in this E-MAIL being proprietary to Wipro Limited is
'privileged' and 'confidential' and intended for use only by the individual
or entity to which it is addressed. You are notified that any use, copying
or dissemination of the information contained in the E-MAIL in any manner
whatsoever is strictly prohibited.
***
Re: [GENERAL] SET within a function?
Is the rewrite only for the literal 'X = NULL' or will it do a test against a value such as 'X = OLD.X' (and rewrite is OLD.X is NULL)? Is there any way to match NULLS to each other (as I am looking for a literal row, not using NULL as the UNKNOWN). I suppose I could put in a dummy value for the 'Not a valid value', but it seems to be quite awkward when I really do want the NULL. I ended up writing an equivalent function for the project I'm working on. It goes like this in plpgsql: IF $1 IS NULL THEN RETURN $2 IS NULL; ELSIF $2 IS NULL THEN -- We already know $1 is not null. RETURN FALSE; ELSE -- Both args are not null. RETURN $1 = $2; END IF; That's the basic idea. I put a wrapper around this to generate a copy of it for all the data types used in my database. ---(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] SET within a function?
The problem I would face is that this still needs to be a sequential scan in the table rather than an index lookup. Regards, Ed On Tue, 14 Oct 2003, Arthur Ward wrote: Is the rewrite only for the literal 'X = NULL' or will it do a test against a value such as 'X = OLD.X' (and rewrite is OLD.X is NULL)? Is there any way to match NULLS to each other (as I am looking for a literal row, not using NULL as the UNKNOWN). I suppose I could put in a dummy value for the 'Not a valid value', but it seems to be quite awkward when I really do want the NULL. I ended up writing an equivalent function for the project I'm working on. It goes like this in plpgsql: IF $1 IS NULL THEN RETURN $2 IS NULL; ELSIF $2 IS NULL THEN -- We already know $1 is not null. RETURN FALSE; ELSE -- Both args are not null. RETURN $1 = $2; END IF; That's the basic idea. I put a wrapper around this to generate a copy of it for all the data types used in my database. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] converting varchar date strings to date
Hello, How can I typecast a date generated from VARCHAR fields into a date field ie: UPDATE inventory SET date_field = vc_year||'-'||vc_month||'-'||vc_day; where the date string is built up from varchar fields? Thanks for any help. Peter ---(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] SET within a function?
Edmund Dengler wrote: The problem I would face is that this still needs to be a sequential scan in the table rather than an index lookup. IIRC, NULL values aren't indexed, only actual values, which is an implementation detail but yet-another reason why NULL-elimination through normalization is a good idea: http://www.hughdarwen.freeola.com/TheThirdManifesto.web/Missing-info-without-nulls.pdf Mike Mascari [EMAIL PROTECTED] ---(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] Redhat RPMs
On Friday 10 October 2003 08:52 pm, Christopher Browne wrote: Oops! [EMAIL PROTECTED] (Nigel J. Andrews) was seen spray-painting on a wall: I've not looked at many RPMs but I must say that the few I have have never been relocatable. Can the postgresql RPMs not be made relocatable? Unfortunately, relocation would have to include the init scripts, and that would be pretty hairy. The notion of relocatable RPMs came up early in its design, but the only case where that will be particularly usable is if the components are mostly binaries that only make relative path references. That situation is unusual, to say the least. I've been watching this discussion with interest (well, I _am_ the RPM maintainer, after all) and have to say that it has been thought of before. It wasn't at that time implemented due to political factors (read: the then Red Hat maintainer (@redhat.com) refused to include such support even if I had built it). But I did go through the design phase. If everyone can be patient, I'll try to go back into my archives and dig out the design doc I put together way back then. In the meantime, I'd like to hear people's ideas. As alternatives (debian-style) are fully supported in later Red Hat (and the new Fedora Core) releases, a scheme that uses alternatives would be ok. Be sure to post to the pgsql-ports list instead of pgsql-general, though. If the list server will accept it, reply-to has been set to pgsql-ports. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to list which tables are available?
On Tuesday 14 October 2003 10:53, B.W.H. van Beest wrote: It seems so elementary, but how I get a list of which tables are available in a database. I can't find an SQL command for this, but there must be a way! In psql use \d or \dt If you start psql with the -E flag it will show you how it does that. There are also a number of pg_xxx views that show this sort of thing (pg_tables, pg_indexes etc). I think these are covered in an appendix of the manuals. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] converting varchar date strings to date
On Tuesday 14 October 2003 17:54, pw wrote: Hello, How can I typecast a date generated from VARCHAR fields into a date field ie: UPDATE inventory SET date_field = vc_year||'-'||vc_month||'-'||vc_day; ... SET date_field = CAST(vc_year...vc_day AS date) or ... SET date_field = (vc_year...vc_day)::date The first is SQL-standard, the second less typing. If that gives you problems, cast to text first, then to date. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How to list which tables are available?
On Tue, 2003-10-14 at 04:53, B.W.H. van Beest wrote: It seems so elementary, but how I get a list of which tables are available in a database. I can't find an SQL command for this, but there must be a way! $ man psql, then search for the string list of all tables $ psql test1 Welcome to psql 7.3.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA Experience hath shewn, that even under the best forms [of government] those entrusted with power have, in time, and by slow operations, perverted it into tyranny. Thomas Jefferson ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PG tools
--- G Lam [EMAIL PROTECTED] wrote: Hi, I am new to postgresql. I have done some small applications with MS Access. Are there some PG tools out there that can build applications like MS Access does out there? MS Access works well with PostgreSQL via ODBC. Check here: http://gborg.postgresql.org/project/psqlodbc There are no free/open source products which can be said to do what MS Access does, although we hope that pgAccess might do it. A recent recommendation on this lists pointed to a commercial product: http://www.thekompany.com/products/rekall/ I haven't tried it so I can't say. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Excute comnands OS from plpgsql
On Tuesday 14 October 2003 17:15, Richard Huxton wrote: On Tuesday 14 October 2003 16:38, Edwin Quijada wrote: How can I use NOtify to do this? Please make sure you reply to the list too. In your daemon you do something like: LISTEN signal1, and in a trigger (say) you issue NOTIFY signal1. Your daemon will receive an asynchronoums message signal1 and you can then check the relevant tables for updates/deletions/whatever signal1 means. In practice, I usually don't care about responding that quickly, and just have a cron job start up a script every 5 mins to check a pending_jobs table. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] previous next buttons
I was just helped with this last week (see threat Interfaces that support cursors that started on 10/10 if its in the archives. The two ways to do it- either issuing a selects with limit/offset modifiers or with a cursor. On the above thread today, it was posted that large datasets are going to significantly slow the limit/offset style down but since it sounds like you are doing a web app, you are faced with implementing a persistent connection to the database on the backend (which was also my issue) Over the weekend, I did the limit/offset method since I do have a large set of data. Basically I have an image browser that display Z images per page where Z is caculated from X columns by Y rows (these can be changed in the script in case my client ever want to change the format). For example, each page display there is represented by this query: select id,name from files order by id limit $pagec offset $offset; $offset=($page * $pagec) - $pagec; $pagec = $cols * $rows; # the number items per page- in you case 10 I only use prev and next buttom but you could use that logic to calculate the full range of pages. To find the last page, you could use this: select ceil(count(*)::float/$pagec) from files; Don't forget to check for use sillyness like pages less that 1 or pages greater than your last page. BTW, to do this with cursors a starting point is to look at PersistentPerl or mod_perl and their respective documentation on how to do persistent database connections. The short answer is that you have to make database connection global so that it can be checked in subsequent runs of your script. The long answer is to dig through the documentation. Hope that help you! Thanks to Doug McNaught and Jonathan Bartlett for helping me last week!! You're comments made for an enjoyable programming weekend :) Quoting Psybar Phreak [EMAIL PROTECTED]: hi all, im developing a site in perl with mason on a postgreSQL backend. i currently have a page that lists all items in a table, but would like to do the PREVIOUS 1 2 3 4 NEXT sort of thing - say LIMIT of 10 records to a page. can someone help me out. thanks PP ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Keith C. Perry Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Question
Robert Partyka wrote: Hi, I have question: why such condition: foofield not like '%bar%' where foofield is varchar returns false (or rather even ignore row) on record where foofield is null but returns true on records where foofield is '' (empty string) SQL specifications. Empty string and NULL are two different thinks. Regards Gaetano Mendola ---(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] converting varchar date strings to date
pw writes: How can I typecast a date generated from VARCHAR fields into a date field Using CAST(). -- Peter Eisentraut [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
Re: [GENERAL] Question
On Mon, 13 Oct 2003, Robert Partyka wrote: why such condition: foofield not like '%bar%' where foofield is varchar returns false (or rather even ignore row) on record where foofield is null Actually, it probably returns unknown(NULL) on such records. NULL LIKE '%bar%' is unknown, so NULL NOT LIKE '%bar%' is also unknown. This is because NULL isn't the same as empty string, nor is it the absence of a value, but it's an unknown value. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to list which tables are available?
B.W.H. van Beest writes: It seems so elementary, but how I get a list of which tables are available in a database. I can't find an SQL command for this, but there must be a way! SELECT * FROM pg_tables; -- Peter Eisentraut [EMAIL PROTECTED] ---(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] converting varchar date strings to date
Hello, This has been resolved. As I told a previous poster, CAST() wasn't working. I have no idea why. I finally used: UPDATE inventory SET date_field=date(vc_year||'-'||vc_month||'-'||vc_day ); Peter pw writes: How can I typecast a date generated from VARCHAR fields into a date field Using CAST(). -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Question
If you are experienced in Oracle, this might be confusing since Oracle treats empty string and NULL as being the same. On Mon, 13 Oct 2003, Robert Partyka wrote: why such condition: foofield not like '%bar%' where foofield is varchar returns false (or rather even ignore row) on record where foofield is null Actually, it probably returns unknown(NULL) on such records. NULL LIKE '%bar%' is unknown, so NULL NOT LIKE '%bar%' is also unknown. This is because NULL isn't the same as empty string, nor is it the absence of a value, but it's an unknown value. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] order by a string
hi!
i am developing with version 7.3.2 under cygwin. my production
evironment is 7.3.3 on a suse machine.
language settings (lc_...) for the server are on developing and
production machine the same, 'C'.
now i encoutered the following behaviour. i use a union select to unify
a query result with an ' (all)' entry.
i used at home a space on first character to ensure this entry will be
on top if i am ordering by the column.
it works in developing environment but it doesnt in production. in
production i got a ordering result wich ignores special chars like my
whitespace and the '(' and the all entry so comes for example after
'akk' and before 'amm'.
i dont want to have it this way!
did i miss any setting option to change this behaviour?
or do i need something like a workaround (adding another column, for
axample adding values like 0 for my special entrys and 1 for all others
so i can sort by this solumn too, to get my result?)
thanx for your help
stefan
---(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] PLPERL function error - utf-8 to iso8859-1
Patrick Hatcher [EMAIL PROTECTED] writes: Trying to create a plperl function to strip non-friendly mainframe characters from a string. However, when I try to add the Trademark symbol (™) as a replace criteria, PG spits back an error: ERROR: Could not convert UTF-8 to ISO8859-1 AFAICT this means that the trademark symbol is not in the character set that you've specified to be used in the database; accordingly there's no need to try to prevent it from being stored... Perhaps you should have selected the database encoding to be the same as what you're using on the client side. 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
