Re: [GENERAL] Tuple is too big
Steve Wolfe wrote: After moving a database to a new machine, I tried a vaccum analyze, and get "ERROR: Tuple is too big: size 8180, max size 8140". I know that there's a limit on the tuple size and all of that - my question is: How do I fix it? Vaccum analyze barfs, as does trying "\d table name". I tried it on both machines, and the same thing. I suppose that I could write a parser to go through the pg_dump and find offending fields, but I'm hoping that there's a way for PostgreSQL to fix it. If there isn't a way for it to fix data that it's created, that's scary. : ) It is postgresql 6.5.3. Me too. It's been happening for the last weeks with a database that didn't have any problems before. By experimenting, I've observed that this behaviour disappeared when I removed a certain table that another co-worker created; the problem is that such table doesn't have any tuple bigger than the max. supported size. Looking at the data stored in it, I don't see anything bigger than 8000 bytes (more or less) either. We are using that table (and others) to store texts, in field defined with varchar(8000). I suppose that if somebody had tried to insert a text bigger than that, the database would have refused with an error... Just in case, is there any character that, when inserted, will make the tuple grow beyond the maximum size, while still taking technically just one byte? (Some of the inserted texts were Front Page-generated HTML, and had all kinds of tabs, return carriages and such). Paulo Jan. DDnet.
Re: [GENERAL] Dropping tables
Ron Peterson [EMAIL PROTECTED] writes: Huh? What is this? Oh, x_id_key somehow survived DROP TABLE. Bummer. Maybe I can use DROP INDEX to drop it? Close. Try DROP SEQUENCE. It works, thanks. Are there other things if I need to watch out for when dropping tables?
[GENERAL] DateStyle is Postgres with US (NonEuropean) conventions
hi list, i have start postgresql doingpostmaster -i and this is what i am getting in the shell. NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions and continuos. does anyone know how can start postgres to prevent from this? thanks. marcos [EMAIL PROTECTED]
Re: [GENERAL] DateStyle is Postgres with US (NonEuropean) conventions
i have start postgresql doingpostmaster -i and this is what i am getting in the shell. NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions NOTICE: DateStyle is Postgres with US (NonEuropean) conventions and continuos. does anyone know how can start postgres to prevent from this? postmaster -i -o -e CU, Denis.
[GENERAL] group by problem
Moin, i have an sql query which works perfect under PostgrSQL 6.0 but under 6.5.1 it brings: ERROR: Illegal use of aggregates or non-group column in target list. select T1.id, T1.name, T1.zusatz, T1.kontakt, T1.strasse, T1.land, T1.plz, T1.ort, T1.telefax from debitoren T1, auftrag T2 where T2.kunde=T1.id group by T1.id; Where is the problem? Thanks for any hints Bis dann Matthias
[GENERAL] NOTICE: DateStyle is Postgres with Eropean convention
hi list, i have restart postgresql with postmaster -i -o -e and every time the servlets (via web) are accesing to posgres database it shows, in the shell, this: NOTICE: DateStyle is Postgres with European conventions thanks marcos [EMAIL PROTECTED]
[GENERAL] count distinct
Hi people. I would like to make a query that tells me how many distinct values there are in a column. Standard count doesn't seems to support a count distinct option. select distint count(*) of course doens't work (distinti clause is applied after the result are calculated). I've tried to define a view, but it didn't worked ( create view distValues as select distinct ... but views doesn't support distinct clause)... Well, maybe I should create a new aggregate function, but before spending time on PGSQL guide I would like to know if someone can give me a fast tip... ;-) Thanks in advance Andrea PS: well, of course I can open a cursor on a "select distinct column from table" and then loop over the cursor couting how many values there are, but it seem a bit ugly...
RE: [GENERAL] count distinct
Hi people. I would like to make a query that tells me how many distinct values there are in a column. Standard count doesn't seems to support a count distinct option. select distint count(*) of course doens't work (distinti clause is applied after the result are calculated). I've tried to define a view, but it didn't worked ( create view distValues as select distinct ... but views doesn't support distinct clause)... SELECT COUNT(DISTINCT field_name) FROM table_name; Works for me in v7. Regards, Andrew Snow [EMAIL PROTECTED]
Re: [GENERAL] count distinct
Andrea Aime wrote: Hi people. I would like to make a query that tells me how many distinct values there are in a column. Standard count doesn't seems to support a count distinct option. select distint count(*) of course doens't work (distinti clause is applied after the result are calculated). I've tried to define a view, but it didn't worked ( create view distValues as select distinct ... but views doesn't support distinct clause)... Well, maybe I should create a new aggregate function, but before spending time on PGSQL guide I would like to know if someone can give me a fast tip... ;-) Thanks in advance Andrea PS: well, of course I can open a cursor on a "select distinct column from table" and then loop over the cursor couting how many values there are, but it seem a bit ugly... SELECT field, count(*) FROM table GROUP BY field With best regards, Max Vaschenko, Nizhny Novgorod Information Networks.
[GENERAL] Access Permission Denied
I am getting the following error Warning: PostgresSQL query failed: ERROR: userinfo: Permission denied. when I try to connect to a database over the web. "userinfo" is the name of the table I am trying to query. I have tried nearly everything in the documentation provided to connect and I am still unsuccessful. Can anyone spare some sample code that shows how to connect? Thanks p.s. Is there any new-user friendly documentation available?
[GENERAL] Q: regcomp failed with error invalid character range
I need to select records with description containing chars with highest bit set but select * from table where descr ~ '.*ATU[\0200-\0377].*'; fails with error ERROR: regcomp failed with error invalid character range Any idea how to work around it? Thanks. - Robert P.S. This is 7.0.2 from RPM on RH 6.1, locale is CZ, database is with encoding 'latin2' (8).
[GENERAL] [Fwd: Q: regcomp failed with error invalid character range]
With [\200-\377] instead of [\0200-\0377] it works. Sorry. - R. I need to select records with description containing chars with highest bit set but select * from table where descr ~ '.*ATU[\0200-\0377].*'; fails with error ERROR: regcomp failed with error invalid character range Any idea how to work around it? Thanks. - Robert P.S. This is 7.0.2 from RPM on RH 6.1, locale is CZ, database is with encoding 'latin2' (8).
[GENERAL]
Hello!! in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is an interesting statement "seek eval" ; it scans the index and for every position it evaluates a block of code/ function until the function return true . is in postgresql an internal mechanism or a way to use the index when selecting records with conditions like function(index_expression) = value ? Thanks!
Re: [GENERAL] Crosstab SQL Question
Is it possible to perform a crosstab query in postgres similar the functionality that MS Access provides? I tried building the query in Access (against postgre 6.5.3 using ODBC) and using the SQL created by Access, but it looks like very non-standard SQL code and postgre doesn't support it. ...[gratuitous sarcasm snipped]... you might want to describe what a crosstab query is... A crosstab is also known in MS Excel as a "pivot table", if that helps any. It facilitates drag-and-drop data analysis by creating arbitrary 2-D matrices aggregated from data columns. Very cool, and hard to adequately describe, as it makes some complex operations very simple, and I don't understand how it works underneath. IMO, it is one of the most powerful data analysis tools in existence. It would be neat to be able to do something similar in pgsql... Applix has something called TM1 that does this. They have a (ug) flash demo and some more documentation at http://www.applix.com/itm1 It used to be available for Linux, but with the big reorg, who knows. -- cary
Re: [GENERAL] NOTICE: DateStyle is Postgres with Eropean convention
and every time the servlets (via web) are accesing to posgres database it shows, in the shell, this: NOTICE: DateStyle is Postgres with European conventions Servlets? So are you using JDBC? Some of the interfaces (JDBC is one of them, afaik) explicitly set the date/time style because they are responsible for manipulating date/time strings coming back from the server, and need to ensure that these strings are in a known format. The notice comes from an explicit "SET DATESTYLE", and should be considered normal behavior when using some interfaces. Not all interfaces need to do this automatically, so you will have to be more specific on your exact scenerio to tell for sure whether you can safely supress this. - Thomas
Re: [GENERAL] Access Permission Denied
Warning: PostgresSQL query failed: ERROR: userinfo: Permission denied.
when I try to connect to a database over the web.
Your web interface is running as a specific user ("nobody"? Something
else??) and this user must be known to Postgres via the createuser
command.
p.s. Is there any new-user friendly documentation available?
We'd like to think that all of the documentation is "new user friendly",
but of course it is probably not, at least in some areas :/
We'd be happy to address any specific questions, comments, and
suggestions, and getting feedback from new users is the way to do it.
Once you aren't a "new user", then you will want something different
from the docs, and those of us who have been around Postgres for a while
probably do not understand very well what exactly is missing.
So, please send along specific suggestions, or general comments on what
you are having trouble finding or understanding, and we'll try to evolve
the docs to do better.
Regards.
- Thomas
Re: [GENERAL] Lock record
On Thu, Jun 15, 2000 at 12:45:52AM +0200, Jan Wieck wrote: Believe it or not, but holding pure DB locks over "interaction" in an interactive application isn't what you really want! The user might go for coffee, and such long time locks are not what the locking mechanism of databases is intended for - so it's not optimized for this kind of abuse! Allow me to echo the above sentiment. Our library automation system is built on a PICK back end (UniVerse), and the implementation locks any record that is in current use. A good thing, that, in so far as you don't want, say, two people writing to the same patron record at the same time. Problem is, patrons are frequently checking books out while someone else is checking in the items the patron had out before. Kablooey. What's supposed to happen, of course, is that the ckeck-in or check-out clerk gets a message, "patron file is locked -- wait or quit?" In practice, there are too many cases where lock contention is not handled properly, and both terminals get locked up. This is often a pain to resolve; I spend quite a bit of time just trying to figure out where the lock is coming from. And this doesn't even begin to touch the times where a staff member was helping a patron look at his/her holds (or whatever), and then just left the session logged in to that person's record (which is a problem of bad user behaviour, yes, but knowing that doesn't help when you're trying to break someone's 20 year bad habit). Use transactions. Much better than locking. -- Andrew Sullivan Computer Services [EMAIL PROTECTED]Burlington Public Library +1 905 639 3611 x158 2331 New Street Burlington, Ontario, Canada L7R 1J4
Re: [GENERAL] Help:How do you find that how much storage is taken by the database??
NEERAJ BANSAL wrote: Hi, How do you find that how much storage the data in the database or tables is taking in bytes or any other format??? Is there any command which tells you this??? ls -l /usr/local/pgsql/data/base As a user with access to this directory, of course. This is assuming PostgreSQL has been installed in the usual location. Ron Peterson [EMAIL PROTECTED]
[GENERAL] Performance for indexes on functions
Hi, I would like to use some indexes with functions like substr : CREATE INDEX IND1 ON T1 ( substr( col1, 1, 5 ) )... What are the performance of such an index compared to an index on col1... In which cases will this index be used in a query : Does the where clause has to match exactly the function used when creating the index : SELECT ... WHERE substr( col1, 1, 5 ) = '.'. Do you recommend or not to use this kind of indexes ? Excuse me it it's not really clear but if necessary, I will try to explain it better... Thanks a lot Patrick FICHE
Re: [GENERAL] trigger errors
Marc Britten wrote: hi again, i created a trigger on a table for insert or delete, and a function that gets created fine. however when i go to insert something into the table i get NOTICE: plpgsql: ERROR during compile of create_count_cache near line 2 ERROR: parse error at or near ";" Hi Marc, Try this function instead: CREATE FUNCTION create_count_cache() RETURNS opaque AS ' BEGIN DELETE FROM LangCount; INSERT INTO LangCount SELECT LangID as ID, COUNT(LangID) AS CNT FROM snippet GROUP BY LangID; DELETE FROM CatCount; INSERT INTO CatCount SELECT LangID as ID, COUNT(LangID) AS CNT FROM snippet GROUP BY LangID; RETURN NEW; END;' LANGUAGE 'plpgsql'; There are two changes from what you have. You cannot use BEGIN/COMMIT (i.e. transactions) within a function or trigger. In plpgsql, use BEGIN and END to delimit your function statements. There is more information about this in the PL/pgSQL portion of the user's guide. The function also needs to return something. A return type of 'opaque' doesn't mean the function returns nothing, it means the function doesn't return a defined SQL datatype. Ron Peterson [EMAIL PROTECTED]
[GENERAL] libpgtcl.so missing from pgaccess on FreeBSD4.0?
How do you locate/create the "libpgtcl.so" in order to run pgaccess under FreeBSD 4.0 - As far as I can tell, it does not appear to be part of the pgaccess distribution located under /contrib. This is called by main.tcl as a required shared library. Thanks. DLampe -
Re: [GENERAL]
Robert Deme wrote:
Hello!!
in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is
an interesting statement "seek eval" ; it scans the index and for every
position it evaluates a block of code/ function until the function
return true .
is in postgresql an internal mechanism or a way to use the index when
selecting records with conditions like function(index_expression) =
value ?
I'm not sure I understand the question. There are a large number of
functions built in to PostgreSQL that you can use in your queries. Plus
you can define your own functions in SQL, C, or other languages. If
you're talking about something else, maybe send an example.
DROP TABLE pie;
CREATE TABLE pie (
filling text,
slice float --size in degrees.
);
INSERT INTO pie
VALUES ('blackbird', 90);
INSERT INTO pie
VALUES ('blackbird', 45);
INSERT INTO pie
VALUES ('plum', 120);
SELECT filling, radians( slice ) AS size
FROM pie
WHERE radians( slice ) 1;
Ron Peterson
[EMAIL PROTECTED]
