Re: [GENERAL] image storing
But I think that hyperlinks would be a good addition to data types repertoire to pgsql. And how would the behavior of such a datatype differ from, for example, a text datatype? My interpretation would be that if the consumer of the data wants to display it as a hyperlink, fine, but that that is the job of the application not the database. Am I missing something? Cheers, Brook
Re: [GENERAL] Blown Index? Corrupted Table?
Adam Haberlach <[EMAIL PROTECTED]> writes: > I've been getting this on and off in my vaccums. Once it happens, the only way to > make it go away seems to be a dump/restore. Is this bad, Bad, or BAD? > NOTICE: FlushRelationBuffers(rawbebugs, 4400): block 287 is referenced (private 0, >global 1) > FATAL 1: VACUUM (vc_repair_frag): FlushRelationBuffers returned -2 It's just in the "annoyance" category: to clear the condition you need to stop and restart the postmaster. No dump/reload or anything like that, you just want it to reset shared memory. The most likely cause of this problem is a backend exiting from an open transaction without doing AbortTransaction processing. 7.0.* has a bug that a client disconnect without closing a transaction block will result in that happening. I posted a patch for it on the patches list a few weeks ago, or you could pull the current REL7_0_PATCHES branch (7.0.3-to-be) from the CVS server. regards, tom lane
Re: [GENERAL] image storing
Brook Milligan wrote: > >But I think that hyperlinks would be a good addition to data types >repertoire to pgsql. > > And how would the behavior of such a datatype differ from, for > example, a text datatype? My interpretation would be that if the > consumer of the data wants to display it as a hyperlink, fine, but > that that is the job of the application not the database. Am I > missing something? there are a bunch of data types like this that might be interesting to some people. some of the reasons that you might want a data type like this are verification that a URL is valid and the ability to search the URL's by hostname, protocol, etc. i can't say that i'd be that interested in it, but that shouldn't stop somebody from thinking about doing something like this. -- Jeff Hoffmann PropertyKey.com
Re: [GENERAL] Errors with pg_dump
>
> I would like to move some data from an older installation of PostgreSQL to
> a newer. When doing
> "pg_dump persondb > db.out" I get the following error message:
>
> "dumpSequence(person_sek): 0 (!=1) tuples returned by SELECT"
>
> The "person_sek" is a sequence in the database.
>
I believe sequences are implemented as a separate with one row that contains
the sequence parameters and state. It looks like somehow that one row has
been deleted and pg_dump expects it to be there.
Is this sequence being used by your program and is it functioning correctly?
Can you do a select nextval('person_sek')?
If you need the sequence and you know what its current value and other
parameters should be then I suggest droping and recreating the sequence.
Re: [GENERAL] Errors with pg_dump
[EMAIL PROTECTED] writes:
> I would like to move some data from an older installation of PostgreSQL to
> a newer. When doing
> "pg_dump persondb > db.out" I get the following error message:
> "dumpSequence(person_sek): 0 (!=1) tuples returned by SELECT"
> The "person_sek" is a sequence in the database.
> The version of PostgreSQL in question is 6.3.2,
Hmm. Does the sequence still work (can you do SELECT nextval('person_sek'))?
Not sure why the dump attempt would be failing, and 6.3.2 is far enough
back that digging for bugs in it isn't very appealing. I'd suggest just
looking for a work-around instead of a real solution.
You could probably just drop and recreate the sequence before running
pg_dump, being careful to set the new sequence's initial value to
whatever its current value is.
regards, tom lane
Re: [GENERAL] How does TOAST compare to other databases' mechanisms?
Frank Joerdens <[EMAIL PROTECTED]> writes: > Can I go around bragging to my SQL-minded friends about using this > really cool thing that no other database has, or should I keep my mouth > shut because it's actually not so cool? IMHO it's pretty cool. You get the benefits of BLOB storage without having to deal with weird declarations or access methods. I have no idea whether any other databases do it the same way, but simply removing the limit on physical tuple length wouldn't have been as nice. See, with a toasted column, you don't pay to suck the contents of the column into memory when you read the row for a query that doesn't actually touch that column. So, for example, you might have a table declared like CREATE TABLE foo (key1 text, moddate timestamp, big_horking_data text); and you can do things like SELECT big_horking_data FROM foo WHERE key1 = 'bar' AND moddate > 'yesterday'; Here the table is essentially acting as an index for the BLOB storage: the system won't bother to fetch the BLOB values for the rows that fail the WHERE check. You can't do that without lots of cruft in any non-TOAST-like scheme, AFAICS. regards, tom lane
[GENERAL] Re: URL Type
> My interpretation would be that if the > consumer of the data wants to display > it as a hyperlink, fine, but that that > is the job of the application not the > database But MS Access is _both_, remember? The question came up when migrating from Access talking to its own local Jet database to Access talking to pgsql via odbc. I'm not sure there is a way to tell Access to consider a particular text field in a remote ODBC data source to be a hyperlink... > Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
Re: [GENERAL] Newbie, Howto access Array-Slots in user defined functions?
Hi Len, Len Morgan schrieb: > > > > SELECT DISTINCT c FROM table; > > > >I get > > > >ERROR: There is no operator '<' for types '_bytea' and '_bytea' > >You will either have to retype this query using an explicit cast, > >or you will have to define the operator using CREATE OPERATOR > > > >To create an operator I need a function, so let's beginn with a > >compare function: > > > > CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS > > 'SELECT $1[1] < $[2];' LANGUAGE 'sql'; Sorry, this is a typo, my code example looks like this: CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS 'SELECT $1[1] < $2[1];' LANGUAGE 'sql'; Ok, this is not the complete code. To do a compare of all array elements I have to add more comparison operations. This example is the smallest code fragment to point out my problem. The question at this point is, how is the syntax to access an array element of a $n-Variable which refers to an array element? PostgreSQL does prefex the type with an underscore to make it an array reference, but how to access a dedicated elment, also called slot? Any ideas? > > > Shouldn't the above be: > SELECT $1[1] < $1[2] ? > > You probably need the field reference in both places. I have never used > arrays so I don't know if I'm way off base but it seems logical. > ... > > > > >I get > > > > ERROR: parser: parse error at or near "[" > > > >Any hint, how to overcome this? > ... -- Martin Jacobs * Windsbach * [EMAIL PROTECTED] und [EMAIL PROTECTED] Registered Linux User #87175
Re: [GENERAL] Re: URL Type
On Fri, 6 Oct 2000, K Parker wrote: > But MS Access is _both_, remember? The question > came up when migrating from Access talking to > its own local Jet database to Access talking > to pgsql via odbc. I'm not sure there is a > way to tell Access to consider a particular > text field in a remote ODBC data source to > be a hyperlink... I don't think there is... unless you create a saved query and have a calculated field that takes the ODBC data and typecasts it to the hypertext datatype, if such a thing is possible. Brett W. McCoy http://www.chapelperilous.net --- Men aren't attracted to me by my mind. They're attracted by what I don't mind... -- Gypsy Rose Lee
Re: [GENERAL] Newbie, Howto access Array-Slots in user defined functions?
[EMAIL PROTECTED] (Martin Jacobs) writes: > CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS > 'SELECT $1[1] < $2[1];' LANGUAGE 'sql'; > ERROR: Unable to identify an operator '<' for types 'bytea' and 'bytea' > You will have to retype this query using an explicit cast There is nothing wrong with your syntax --- you've declared a function that takes two arrays of bytea, selects the first element of each, and compares 'em. But bytea doesn't support comparison operators ... or much of anything, actually. There is a get_byte function, so you could conceivably build what you want starting with create function lessbyte(bytea, bytea) returns bool as 'select get_byte($1,0) < get_byte($2,0)' language 'sql'; However, I don't see any reasonable way to deal with variable-length inputs without a loop, and SQL functions don't have looping constructs. Given the lack of operators, type bytea isn't currently useful for much except plain storage and retrieval of raw byte sequences. Have you got a strong reason for using bytea, rather than some better-supported type like text? Heck, even array of char would work better: regression=# CREATE FUNCTION lessbyte(_char, _char) returns bool as regression-# 'SELECT $1[1] < $2[1];' LANGUAGE 'sql'; CREATE regards, tom lane
