[GENERAL] Is dropping pg_ts_* harmful?
I have a database running very happily in 8.2 (to be upgraded soon). The system was installed with tsearch2 enabled, however, we have yet to use it. I am going through an effort to reduce "cruft" in the database, which includes four tables: pg_ts_cfg, pg_ts_cfgmap, pg_ts_dict, pg_ts_parser. Are these tables safe to drop? Will the remnants of tsearch2 be anywhere else in the database if it was never used? Thanks folks -- -- Eric Brown / Director of IT / www.mediweightlossclinics.com
[GENERAL] reltuples < # of rows
I thought that the number of tuples in a table must be greater than the number of rows? For one of my tables, I have the following: # analyze t_stats; ANALYZE siteserverdb=# select reltuples, relpages from pg_class where relname = 't_stats'; reltuples | relpages ---+-- 1760 |21119 (1 row) siteserverdb=# select count(*) from t_stats; count --- 1861 (1 row) How is this possible? I'm running postgres 8.0 on a redhat ws3. Clearly I'm not vacuuming enough, but that seems to be a separate issue to me. Am I seeing data corruption? Thanks, Eric Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] How can I expand serialized BLOBs into pseudo columns
I have a table (quite a few of them actually) where python objects are
serialized into a column. So a table might look like:
CREATE TABLE mytable (id int, obj bytea);
When I'm trying to diagnose/debug things, I'd like to be able to expand
the 'obj' column into multiple columns in a view. I created a type and
wrote a plpgsql function that expands the object. i.e.:
CREATE TYPE myitem AS (val1 text, val2 text);
CREATE FUNCTION expandobj(bytea) returns myitem as '...' LANGUAGE
plpgsql;
Then I tried:
SELECT expandobj(obj), * from mytable;
I get:
ERROR: cannot display a value of type record
I think/hope I'm missing something pretty stupid, but I can't figure
out what it might be. Any help would be appreciated. There might even
be a quite better way.
Eric.
Here's a script to reproduce the problem:
CREATE TABLE mytable (id int, obj text);
INSERT INTO mytable VALUES (1, 'x,y');
CREATE TYPE myitem AS (val1 text, val2 text);
CREATE or REPLACE FUNCTION expandobj(text) returns myitem as '
DECLARE
items text[];
item myitem%rowtype;
BEGIN
items := string_to_array($1, '','');
item.val1 := items[1];
item.val2 := items[2];
return item;
END
' LANGUAGE 'plpgsql';
SELECT * from expandobj('a,b'); -- this works
SELECT expandobj(obj), * from mytable; -- this does not
-- I'd like to see four columns: val1, val2, id, obj
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Questions on stored-procedure best practices
I'm used to writing C/Java/python type stuff and am a bit new to stored procedures and looking for a few best practices. I'm using plpgsql (as opposed to plpython, etc.) because it offers the most flexibility in terms of data-types, etc. good editor: Usually emacs does a good job, but postgres puts a stored procedure between ' and ' and this turns off any font-lock highlighting for the entire stored procedure. Can I use something other than quotes or fix this somehow? Is there a better editor? preprocessor: I saw one reference to people running stuff through the C-preprocessor before importing into their DB. Is this common practice? I don't see a huge benefit for it yet unless I want to support multiple DBs. file-names: I know it doesn't matter, but mostly it seems to make sense to put stuff in .sql files. I've seen some reference to people putting stuff in .sp files. What works best for people in terms of organization? packages: I saw there was a patch to support oracle-style packages in postgres back in ~2001, but I saw nothing else. Is this planned? I imagine I could use 'schemas', but I don't think this lets me share variables and I think these are more for splitting up table name-spaces than for associating a group of functions. other: Any other suggestions? Thanks. Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] What's faster
Option 1: create table a (id serial, hosts text[]); OR Option 2: create table a (id serial); create table hosts (id int references a, host text); Table 'a' will have about 500,000 records. There will probably be about 20 reads for every write. Each id has approximately 1.1 hosts. If I use the array (option 1), I'll have to loop over the elements of the array to see if I have a match when querying a given id. This isn't hard, but it means that SELECT will always return 1 record when, in option 2, it might return 0 records and only have accessed the indexes. Given the indexes that will be built and disk pages used (cached or otherwise), which mechanism would be faster for searching. ---(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] Running functions that return void in psql
I've got quite a few plpgsql functions that insert, update or delete. They're all declared to return void. All other functions, I can just run 'select f(...);' from psql to test them. I don't understand how to test these ones that return void from psql. Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pgplsql SELECT INTO ... FOR UPDATE (transaction/locking question)
I'm trying to write a stored procedure in plpgsql that selects a row and possibly increments one of its fields. I thought I would do SELECT INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql automatically lock any rows I read until the stored procedure exits? I'm just not sure how to get the functionality I'm looking for and not have to concern myself with concurrency. Example: create table t_test (x int, y int); create or replace function f_test(int) returns void as ' declare r record; begin select into r *, oid from t_test -- FOR UPDATE where x = $1; if found then update t_test set y=y+1 where oid = r.oid; end if; return; end' language plpgsql; insert into t_test values (1,1); select f_test(1);
Re: [GENERAL] Running functions that return void in psql
Thanks. I forgot the 'return;' and the error message led me in the wrong direction. Thanks! On Dec 15, 2004, at 12:43 AM, Neil Conway wrote: Eric Brown wrote: I've got quite a few plpgsql functions that insert, update or delete. They're all declared to return void. All other functions, I can just run 'select f(...);' from psql to test them. I don't understand how to test these ones that return void from psql. neilc=# create function xyz() returns void as 'begin return; end;' language 'plpgsql'; CREATE FUNCTION neilc=# select xyz(); xyz - (1 row) -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Long-running performance (MVCC, Vacuum, etc.) - Any fix?
I'm building an appliance where I don't want my customers having to tune postgresql in any way from the configuration when I install it. I don't even want them to know it is there. Yet in my study of posgresql, it seems that even conscientious use of a vacuum daemon or cron job in vacuum's various forms leaves databases after a while much larger and lower performing than the actual space and performance after a dump/restore. It seems to me that with MVCC, an UPDATE (for example) is really a DELETE+INSERT and it takes VACUUM to mark the old DELETED row as free space again. So changing one boolean or increasing one counter in a row of 100 bytes will relocate this row in a different location. While this in itself isn't terrible as some DB page is dirty no matter what, unless I'm lucky enough to insert into the same page that changed, my index(es) for this table will have to be changed and this will dirty yet another page that wouldn't be dirty without MVCC. And this is not to mention the resources required by VACUUM. And the disk bloat over time would imply that postgresql doesn't always efficiently use free-space with records/rows of similar size and so performance degrades as there are fewer rows per page over time. My application is multi-threaded and I could care less about the concurrency afforded by MVCC. I'd rather just update the things in place and get less postgresql concurrency but more consistent long-running performance and disk space utilization. Is my interpretation correct? Is there a way to turn off MVCC? Do fixed sized rows help any? Is there anybody using this thing in an appliance type application? I'm quite far along with stored-procedures and whatnot, but if postgresql really isn't the right solution due to these reasons, I'm curious if anybody has alternate OpenSource suggestions? (I'm actually migrating from sleepycat bsddb at the moment because I didn't realize the licensing costs involved there. My needs really aren't that extensive really - a few associative indexes, cascading delete, etc.) My large data sets will have the following characteristics: 5,000,000 Rows x 50 bytes/row (could be fixed) w/1 multi-column index, 1 single-column timestamp index AT 1,000,000 index searches, 300,000 reads, 150,000 updates per day 720,000 Rows x 32 bytes/row fixed w/ 1 multi-column index, 1 single-column timestamp index AT 150,000 index search, 150,000 updates, 100s of reads per day 2,000,000 Rows x 4000 bytes avg/row AT 100,000 inserts per day, 150,000 reads per day Thanks, Eric Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] debug_print_plan (pg7.4) doesn't seem to do anything
I have the following settings: syslog = 2 client_min_messages = notice log_min_messages = debug5 (tried debug1 too) log_error_verbosity = default (tried verbose too, but still doesn't print plan) log_statement = true log_duration = true debug_print_plan = true debug_print_parse = true I thought setting debug_print_plan was supposed to explain every query in my log file? I don't see the plan print either. I'm running version 7.4.6 (from fink) on OS X 10.3.7. Thanks. Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] debug_print_plan (pg7.4) doesn't seem to do anything
On Dec 29, 2004, at 2:28 PM, Tom Lane wrote:
Eric Brown <[EMAIL PROTECTED]> writes:
I thought setting debug_print_plan was supposed to explain every query
in my log file? I don't see the plan print either.
No, it just prints the plan. With settings like yours I get
I don't get the the "DEBUG: parse tree:" or the DETAIL lines at all. I
restarted via pg_ctl. I assume that that's the same as reload.
Actually, if I set client_min_messages to 'debug1' via the SET command
in psql, then I get the detail information in my client. But I want the
information in my syslog, not my client. (My client's driver will barf
if I try to send the information there.) I wonder if their is too much
information and syslog being UDP never gets the detail line? (But then
it appears that postgresql is specifically breaking most longer
messages up into multiple lines, so I doubt this is it either.)
Thanks.
DEBUG: StartTransactionCommand
LOG: statement: select 2+2;
DEBUG: parse tree:
DETAIL: {QUERY :commandType 1 :querySource 0 :canSetTag true
:utilityStmt <>
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable
<>
:jointree {FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList
({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1
:resname
?column? :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk
false} :expr
{OPEXPR :opno 551 :opfuncid 0 :opresulttype 23 :opretset false :args
({CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [
0 0 0 2 ]} {CONST :consttype 23 :constlen 4 :constbyval true
:constisnull
false :constvalue 4 [ 0 0 0 2 ]})}}) :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()}
DEBUG: plan:
DETAIL: {RESULT :startup_cost 0.00 :total_cost 0.01 :plan_rows 1
:plan_width 0
:targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23
:restypmod -1
:resname ?column? :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk
false} :expr {CONST :consttype 23 :constlen 4 :constbyval true
:constisnull
false :constvalue 4 [ 0 0 0 4 ]}}) :qual <> :lefttree <> :righttree <>
:initPlan <> :extParam () :allParam () :nParamExec 0 :resconstantqual
<>}
DEBUG: PortalRun
DEBUG: CommitTransactionCommand
LOG: duration: 7.439 ms
Maybe you forgot "pg_ctl reload" after changing your config?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] debug_print_plan (pg7.4) doesn't seem to do anything
On Dec 29, 2004, at 3:16 PM, Tom Lane wrote: Eric Brown <[EMAIL PROTECTED]> writes: On Dec 29, 2004, at 2:28 PM, Tom Lane wrote: Eric Brown <[EMAIL PROTECTED]> writes: I thought setting debug_print_plan was supposed to explain every query in my log file? I don't see the plan print either. No, it just prints the plan. With settings like yours I get I don't get the the "DEBUG: parse tree:" or the DETAIL lines at all. [ scratches head... ] Are you sure debug_print_plan is really on? Check it with SHOW. It's hard to see why that debug output wouldn't get to the log if the rest of the debug messages do. The problem was syslog. Postgresql logs to facility local0 by default and I wasn't logging that at debug level. ---(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] Passing a ROWTYPE to a function
I'm trying to write a function that takes a %ROWTYPE as an argument. I'm just not sure how to call it from another function. This is what I tried: CREATE TABLE t1 (x int, y int); INSERT INTO t1 VALUES (1, 2); CREATE OR REPLACE FUNCTION g1(t1) RETURNS int LANGUAGE plpgsql AS ' BEGIN RETURN $1.y; END'; CREATE OR REPLACE FUNCTION g2(int) RETURNS int LANGUAGE plpgsql AS ' DECLARE item t1%ROWTYPE; BEGIN SELECT INTO item * FROM t1 WHERE x = $1; RETURN g1(item); END'; SELECT g2(1); This is what I got: CREATE TABLE INSERT 28089 1 CREATE FUNCTION CREATE FUNCTION psql:/tmp/test.sql:16: ERROR: column "item" does not exist CONTEXT: PL/pgSQL function "g2" line 4 at return I'm using posgresql 7.4.6. Thanks. Eric Brown 408-571-6341 www.propel.com
[GENERAL] Editor: Syntax highlighting and stored procedures surrounded by '
I use emacs and syntax highlighting is great -- except that because stored procedures are completely enclosed between two single quotes, all the coloring is off for that portion. Is there a way to not surround stored procedures by quotes or does anybody have a solution that works for them? Thanks. Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Editor: Syntax highlighting and stored procedures surrounded by '
On Jan 6, 2005, at 11:43 AM, Bruce Momjian wrote: Eric Brown wrote: I use emacs and syntax highlighting is great -- except that because stored procedures are completely enclosed between two single quotes, all the coloring is off for that portion. Is there a way to not surround stored procedures by quotes or does anybody have a solution that works for them? In 8.0 final when released there is special $$ quoting, but not earlier versions. Great! Well, I had to move to 8.0 anyway to get better support for passing around composite types. So where is the $$ quoting stuff documented? How do I use it? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Modifying search_path in a stored procedure
I know I can SET the schema search_path in a stored procedure, but is there any way to retrieve it first so that I can get the current value and manipulate that rather than just replace it? I've got two sets of data and two sets of functions in 4 respective schemas. I want to select one set of data and one set of functions. It is fairly straight forward. However, it would be nice if I want to swap the schemas with the functions, I don't have to remember what data schema I was using. Thanks. Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Running a void function in psql
I run 'psql -f mycreatedb.sql' to setup things for my program. It has to run a few stored procedures after they're created. However, I do this by 'SELECT f(...);'. When I do this though, it prints out a bunch of rubbish like: f -- (1 row) How do I get rid of this rubbish? I just want to see errors or NOTICE/INFO strings I intentionally log. Thanks. Eric Brown 408-571-6341 www.propel.com ---(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] log_min_duration_statement
I set this to 250 and the statements that take longer than this are logged... but my driver is creating all kinds of cursors, so it isn't logging anything useful. Yet, if I log all statement (log_statment='mod'), I get way more logging than is useful. Is there a happy medium? This is what I'm getting now(for example): Jan 26 02:26:15 abacagw postgres[28359]: [22-1] LOG: duration: 284.786 ms statement: FETCH 1 FROM "PgSQL_A8A3BE0C" Jan 26 02:26:15 abacagw postgres[27992]: [19-1] LOG: duration: 254.549 ms statement: FETCH 1 FROM "PgSQL_A8A2884C" Jan 26 02:26:15 abacagw postgres[28432]: [11-1] LOG: duration: 376.167 ms statement: FETCH 1 FROM "PgSQL_A8A49CAC" Jan 26 02:26:16 abacagw postgres[28359]: [23-1] LOG: duration: 373.027 ms statement: FETCH 1 FROM "PgSQL_A8A166AC" Jan 26 02:26:16 abacagw postgres[28539]: [10-1] LOG: duration: 278.023 ms statement: FETCH 1 FROM "PgSQL_A8A387EC" Jan 26 02:26:16 abacagw postgres[28361]: [24-1] LOG: duration: 499.291 ms statement: FETCH 1 FROM "PgSQL_A8A68F0C" Jan 26 02:26:17 abacagw postgres[28359]: [24-1] LOG: duration: 636.480 ms statement: FETCH 1 FROM "PgSQL_A8A166AC" Jan 26 02:26:17 abacagw postgres[28399]: [9-1] LOG: duration: 369.708 ms statement: FETCH 1 FROM "PgSQL_A89CB0AC" Jan 26 02:26:17 abacagw postgres[28432]: [12-1] LOG: duration: 304.065 ms statement: FETCH 1 FROM "PgSQL_A89E1CAC" Jan 26 02:26:17 abacagw postgres[28361]: [25-1] LOG: duration: 252.809 ms statement: FETCH 1 FROM "PgSQL_A8A3694C" Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Getting "timeout expired" error almost immediately (20-200ms)
I'm running 8.0.3 on Linux 2.6. Once my application starts to get a bit
of load, I start getting application exceptions with the "timeout
expired" string from postgresql. I think it is coming from
src/interfaces/libpq/fe-misc.c. There is an interesting comment in that
function (hasn't changed since 8.0.3 I believe):
/*
* pqWaitTimed: wait, but not past finish_time.
*
* If finish_time is exceeded then we return failure (EOF). This is
like
* the response for a kernel exception because we don't want the caller
* to try to read/write in that case.
*
* finish_time = ((time_t) -1) disables the wait limit.
*/
int
pqWaitTimed(int forRead, int forWrite, PGconn *conn, time_t
finish_time)
{
int result;
result = pqSocketCheck(conn, forRead, forWrite, finish_time);
if (result < 0)
return EOF; /* errorMessage is already set */
if (result == 0)
{
printfPQExpBuffer(&conn->errorMessage,
libpq_gettext("timeout expired\n"));
return EOF;
}
return 0;
}
This is an example log message from my application code: (timeout after
203ms in this case)
06-01 02:13:21 XCP 12 INFO dbcursor:452 EXEC 203ms (203ms)
SELECT uid,optout FROM f_create_user(%s, %s, %s, %s, %s) ['support', 1,
None, False, False]; OperationalError: timeout expired [dbpool.py:43
execute()]
I just don't know how to get past this when I'm under a bit of load. Is
it a postgresql thing? A linux thing? What should I try next?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
