[GENERAL] getting function argument names from psql?
Is there a way to get the names of the arguments to a function from psql? /df and /df+ return the parameter types, but not their names. Thanks, Tim ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] problem with psql?
(Oops...originally sent this to Jim, instead of the group) Thanks for the reply...Yes, we do have ARD installed on the machine, but I believe the postgres instance that we see running (through ps auxw | grep postgres) is our server instance (pointed at our data directory), not the ARD instance. We are still able to connect to our database using psql (and via JDBC). Does this still seem like it could be an ARD conflict? On Nov 28, 2005, at 5:56 PM, Jim C. Nasby wrote: I've heard from others that OS X's remote desktop equivalent uses an internal PosgreSQL database in an embedded install that uses the default PostgreSQL port. That could be the cause of your trouble... On Mon, Nov 28, 2005 at 01:24:37PM -0600, Timothy Perrigo wrote: We just ran an OS update on an Xserve which runs our PostgreSQL server, and now it seems that pg_ctl status doesn't report the correct status. ~ postgres$ pg_ctl status pg_ctl: neither postmaster nor postgres running ~ postgres$ ps auxw | grep postgres postgres 491 0.0 -0.133156 1160 ?? S 1:09PM 0:00.16 /usr/local/pgsql/bin/postmaster -D /Volumes/DBRAID/pgsql/data postgres 629 0.0 -0.031844216 ?? S 1:09PM 0:00.27 postgres: logger process postgres 631 0.0 -0.033140744 ?? S 1:09PM 0:00.06 postgres: writer process postgres 632 0.0 -0.031860276 ?? S 1:09PM 0:00.02 postgres: archiver process postgres 633 0.0 -0.032864164 ?? S 1:09PM 0:00.02 postgres: stats buffer process postgres 634 0.0 -0.031888284 ?? S 1:09PM 0:00.03 postgres: stats collector process We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? Thanks! Tim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] problem with psql?
On Nov 28, 2005, at 6:14 PM, Michael Glaesemann wrote: [Reordering top-posted reply] Conflicts with the ARD-installed server has been an issue for me on and off in the past, though I haven't been able to connect to the server using psql without specifying the ard database, user, and password. Also, I believe only the ARD server has the embedded PostgreSQL server (and an old one at that). Do you actually have the ARD server software installed on the Xserve? (My guess is you may be using the client software that is installed with the OS.) Also, could you check who owns those processes? The ARD-installed server will probably be owned by someone (such as daemon, though I don't recall off the top of my head) other than your normal postgres server. If you find it *is* a problem with ARD (which I am inclined to doubt), you can alter the port used by the ARD-installed PostgreSQL server by changing the port it uses in /var/db/RemoteManagement/RMDB/rmdb.data/postgresql.conf Hope this helps. Michael Glaesemann grzm myrealbox com Thanks for the reply Michael. As I just wrote in response to Jim's post, I don't think it is an ARD problem. The postmaster instance running on the machine is our instance, not the ARD instance, and is pointed at our data directory. We are able to connect to our database using psql and JDBC; it is just that pg_ctl seems to be lying to us! I have been suspect of ARD in the past (though at the moment I can't remember why), but in this case I don't think it is the source of the problem. We are going to try updating the machine to PostgreSQL 8.1 tonight; I will let you all know if that corrects the issue, but I'd really like to understand what is going on, so if anyone has any ideas, please let me know! Thanks, Tim ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] problem with psql?
Tom, Thanks for the reply...something did in fact remove the postmaster.pid file! We will have to look into this further to track down the culprit. Thank you everyone for your responses! Tim On Nov 28, 2005, at 3:36 PM, Tom Lane wrote: Timothy Perrigo [EMAIL PROTECTED] writes: We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? Sounds to me like you're running pg_ctl with a $PGDATA setting that doesn't match where the server actually lives. Try show data_directory; in psql to verify what the server thinks $PGDATA is. A less likely possibility is that some outside force removed the $PGDATA/postmaster.pid file. If that's the case, signal the postmaster to shut down using kill -TERM (equivalent of normal shutdown) or kill -INT (equivalent of fast shutdown) and then restart it to recreate the pid file. (pg_ctl stop is actually just a wrapper around these signal operations...) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] problem with psql?
We just ran an OS update on an Xserve which runs our PostgreSQL server, and now it seems that pg_ctl status doesn't report the correct status. ~ postgres$ pg_ctl status pg_ctl: neither postmaster nor postgres running ~ postgres$ ps auxw | grep postgres postgres 491 0.0 -0.133156 1160 ?? S 1:09PM 0:00.16 /usr/local/pgsql/bin/postmaster -D /Volumes/DBRAID/pgsql/data postgres 629 0.0 -0.031844216 ?? S 1:09PM 0:00.27 postgres: logger process postgres 631 0.0 -0.033140744 ?? S 1:09PM 0:00.06 postgres: writer process postgres 632 0.0 -0.031860276 ?? S 1:09PM 0:00.02 postgres: archiver process postgres 633 0.0 -0.032864164 ?? S 1:09PM 0:00.02 postgres: stats buffer process postgres 634 0.0 -0.031888284 ?? S 1:09PM 0:00.03 postgres: stats collector process We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? Thanks! Tim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_get_serial_sequence and table inheritence
Is there anything similar to pg_get_serial_sequence that will work with tables that have an inherited serial column? For example, if I have 2 tables: create table base ( idserial not null primary key ); and create table derived ( stufftext, constraint derived_pkey primary key(id) ) inherits (base); I'd like to be able to call pg_get_serial_sequence passing derived for the table and id for the sequence column (to get, in this case base_id_seq). If nothing like this currently exists, any suggestions on how I could write a plpgsql function to get this behavior? Thank you for any suggestions! Tim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Interval Question
Can you post the code for the function you are having trouble with? The following psql query works as expected (returns a negative interval): select '3 days 4 hours 17 mins'::interval - '3 days 6 hours 17 mins'::interval; ?column? --- -02:00:00 Inserting the difference into a table with an interval column also seems to work: azrael=# create temp table interval_test(dif interval); CREATE TABLE azrael=# insert into interval_test select '3 days 4 hours 17 mins'::interval - '3 days 6 hours 17 mins'::interval; INSERT 13615943 1 azrael=# select * from interval_test; dif --- -02:00:00 (1 row) On Jan 11, 2005, at 8:34 AM, Terry Lee Tucker wrote: Greetings: I am working on a function which returns an interval value. The work of the function is to calculate the difference between the appointment timestamp and and the current timestamp, represented as an interval, and the the time required to travel from point A to B, represented as an interval. Appoint time: 01/14/2004 15:30 Current time: 01/11/2004 10:43 Appt Interval: @ 3 days 4 hours 17 mins Travel Time: 78 hours 17 minutes Travel Interval: @ 3 days 6 hours 17 mins As you can see, this truck is going to be 2 hours late. The return value I'm looking for is the difference between Appt. Interval and Travel Interval, as in: return (appt_interval - travel_interval). This value will be stored in a column of type interval. I would like for late values to be shown as negative. @ -2 hours. I thought that subtracting the larger interval from the small would return this but it is always the absolute value. The documentation states: interval values can be written with the following syntax: [EMAIL PROTECTED] quantity unit [quantity unit...] [direction] Where: quantity is a number (possibly signed); Considering the above statement I believed that I could show this difference as a negative value but I haven't been able to figure out how to do it. Can I do this, and if so, how? Thanks... Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] OS X shared memory problems 8.0rc3
I just downloaded and installed RC3 on my OS X system (10.3.7), and I'm getting a shared memory error when trying to run initdb (error message listed below). I received a similar error a few weeks ago after upgrading my OS to 10.3.7, but I was able to get around that by reducing the shared_buffers setting in postgresql.conf. I'm not sure how to work around this one, though, since I'm starting from scratch and the error is coming from initdb. What is the best way to correct this situation? Any help would be appreciated! Thanks, Tim Error message from initdb: /usr/local/pgsql tperrigo$ sudo -u postgres initdb --encoding=UNICODE /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating directory /usr/local/pgsql/data/global ... ok creating directory /usr/local/pgsql/data/pg_xlog ... ok creating directory /usr/local/pgsql/data/pg_xlog/archive_status ... ok creating directory /usr/local/pgsql/data/pg_clog ... ok creating directory /usr/local/pgsql/data/pg_subtrans ... ok creating directory /usr/local/pgsql/data/base ... ok creating directory /usr/local/pgsql/data/base/1 ... ok creating directory /usr/local/pgsql/data/pg_tblspc ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 50 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1155072 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about shared memory configuration. ---(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] OS X shared memory problems 8.0rc3
On Jan 2, 2005, at 12:58 PM, Tom Lane wrote: Timothy Perrigo [EMAIL PROTECTED] writes: I just downloaded and installed RC3 on my OS X system (10.3.7), and I'm getting a shared memory error when trying to run initdb (error message listed below). I received a similar error a few weeks ago after upgrading my OS to 10.3.7, but I was able to get around that by reducing the shared_buffers setting in postgresql.conf. I think you probably are trying to run two postmasters at once. You really need to increase the OS X memory limits, instead. regards, tom lane No, I just ran pg_ctl status to check, and here was the output: pg_ctl: neither postmaster nor postgres running I then ran initdb, and got the error message I posted before. I only want to run 1 postmaster at a time...any ideas? Thanks, Tim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] syntax for inserting unicode character literal
What is the syntax for inserting a unicode character literal? I thought it would be something like '\u05D0', but that doesn't work. Any help would be appreciated! Thanks, Tim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?
On Dec 17, 2004, at 11:10 PM, Tom Lane wrote: Fascinating. As far as I can tell on my machine, 10.3.7 did not change the kernel IPC limits. So if it's not working for you guys that would suggest that 10.3.7 added some new background usage of IPC resources, which in combination with the PG postmaster exceeds the same-as-it-ever- was kernel limit. If ipcs worked then we'd have some chance of investigating this, but OS X doesn't provide ipcs. (Thank you Apple ... not) FWIW, my installation of PG on OS X defaults to max_connections = 50 shared_buffers = 300 because values higher than that exceed the default kernel limits. It looks like yours has 100/1000 --- did you hand-modify that? Or maybe you hand-modified the kernel limits? Another possible explanation is that the 10.3.7 update overwrote any local changes you'd made to the IPC limits. regards, tom lane I dropped the shared_buffers from 300 (the number determined by initdb) to 200 and I am now able to start the server. Tim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?
I'm getting the same error now too, although postgres was running without problem this morning (I updated the OS yesterday). OS X 10.3.7 Server, PostgreSQL 8.0RC1. Basically, I did a pg_dump, stopped the server and then tried to start it again. Any ideas? On Dec 17, 2004, at 2:41 PM, Jerry LeVan wrote: I *think* the 10.3.7 upgrade broke my postgresql implementation... I am getting this error at startup. FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5432001, size=10403840, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 10403840 bytes), reduce PostgreSQL's shared_buffers parameter (currently 1000) and/or its max_connections parameter (currently 100). The PostgreSQL documentation contains more information about shared memory configuration. The first time I noticed the problem I found that the shmmax was set low at a bit more than 4MB ( perhaps the upgrade replaced /etc/rc ). I increased the shmmax to about 64MB and still get the same error. Here are the current kernel settings kern.sysv.shmmax: 67108864 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 kern.sysv.semmni: 87381 kern.sysv.semmns: 87381 kern.sysv.semmnu: 87381 kern.sysv.semmsl: 87381 kern.sysv.semume: 10 Do I need to increase anything else? I am on the digest, if someone knows the answer please CC to me also... Jerry ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Regarding Postgres installation and administration on linux suse 9.0
On Dec 10, 2004, at 4:26 AM, Vikas Kumawat wrote: x-tad-biggerDear Sir /Madam,/x-tad-bigger x-tad-biggerWe are working on Linux suse 9.0 and we have installed Postgres 7.3 through yast with the privileges of /x-tad-biggerx-tad-biggerroot/x-tad-biggerx-tad-bigger user. But we are not able to start / configure the postgres database, Kindly help us in configuring and starting the same database and how can we create database and use it. We will be very thankful to you prompt help./x-tad-bigger x-tad-biggerThanks Regards/x-tad-bigger x-tad-biggerVikas Kumawat/x-tad-bigger The postgres users should own the data directory, but otherwise should not have administrative privileges. What is the specific error you are getting?
Re: [GENERAL] data integrity and inserts
The first way also makes it possible to put the constraint on multiple fields: create unique index uidx_abc on my_table(col_a, col_b, col_c); On Dec 2, 2004, at 1:51 PM, Ian Harding wrote: The second is shorthand for the first. you get to choose the index name in the first one. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 Scott Frankel [EMAIL PROTECTED] 12/01/04 10:48 AM 1. CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); CREATE UNIQUE INDEX uidx_thename ON names(the_name); vs. 2. CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE); Is the UNIQUE constraint in the second solution merely short-hand for the explicit index declaration of the first solution? Or is there a functional difference between them that I should choose between? Thanks again! Scott On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote: I want to ensure data integrity when inserting into a table, preventing multiple entries of identical rows of data. Does this call for using a trigger? How would triggers perform a query to test if data already exists in the table? (The doco outlines how triggers perform tests on NEW data inserted into a table; but I haven't found anything on data already extant.) Thanks in advance! Scott sample table: CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Table name as parameter in function
You'll need to use the EXECUTE command to build the SQL dynamically. See: http://www.postgresql.org/docs/7.4/interactive/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN On Nov 23, 2004, at 2:56 PM, Alexander Pucher wrote: Hi, struggling around with this for some time: How can I use a table name as a parameter in a PL/pgSQL function ?? I tried this but it didn't work... CREATE OR REPLACE FUNCTION my_row_count(text) RETURNS int4 AS ' DECLARE num_rows int4; BEGIN num_rows := (select count(*) from $1); RETURN num_rows; END; ' LANGUAGE plpgsql; Thnaks for any input! regards, alex. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Table name as parameter in function
Sorry for the brief response earlier; I was a bit rushed. After looking into it, it's a bit messier than I thought (at least, as far as I can tell...perhaps one of the gurus on this list can show us a better way). Ordinarily, when you write select statements (for example) in a plpgsql function, it will attempt to cache the execution plan. In your case, though, you want to be able to hit different tables each time your function is invoked, so you need a way to construct and execute your query dynamically. That's where the EXECUTE statement comes in. EXECUTE allows you to issue a command that is prepared every time it is run. In your case, though, things are a bit trickier. There's no way to get the results of a dynamically executed select statement within a plpgsql function (according to the docs, the results are discarded). In your example, you need to be able to run a dynamic sql statement and get a result back. I thought a temp table might work in this situation, so I tried something like this (using PostgreSQL 8.0 beta 4): create or replace function count_rows(table_name text) returns integer as $$ declare c integer; begin execute 'select count(*) into temp count_tbl from ' || quote_ident(table_name); select count into c from count_tbl; return c; end; $$ language 'plpgsql'; Unfortunately, you can't use EXECUTE to do a SELECT INTO. So, as if that wasn't ugly enough, I ended up having to do the following: create or replace function count_rows(table_name text) returns integer as $$ declare c integer; begin execute 'create temp table count_tbl(count integer)'; execute 'insert into count_tbl(count) select count(*) from ' || quote_ident(table_name); select count into c from count_tbl; return c; end; $$ language 'plpgsql'; That works, but it is definitely not very pretty (if you use it, you'll probably want to also add some code to drop the temp table...if you search through the recent messages on this list, there's question I asked about adding such a cleanup mechanism to a function that may be helpful). If anyone knows a cleaner way to solve Alexander's problem, I'd be really interested to hear it! Hope this helps, Tim On Nov 23, 2004, at 5:32 PM, Alexander Pucher wrote: Tim, I'm afraid, I didn't get the point. Could you give me an example code snippet of how to use the EXECUTE command in my case. Do I have to use the EXECUTE within my function? Thanks a lot, alex. Timothy Perrigo wrote: You'll need to use the EXECUTE command to build the SQL dynamically. See: http://www.postgresql.org/docs/7.4/interactive/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN On Nov 23, 2004, at 2:56 PM, Alexander Pucher wrote: Hi, struggling around with this for some time: How can I use a table name as a parameter in a PL/pgSQL function ?? I tried this but it didn't work... CREATE OR REPLACE FUNCTION my_row_count(text) RETURNS int4 AS ' DECLARE num_rows int4; BEGIN num_rows := (select count(*) from $1); RETURN num_rows; END; ' LANGUAGE plpgsql; Thnaks for any input! regards, alex. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Departement of Geography and Regional Research University of Vienna Cartography and GIS Virtual Map Forum: http://www.gis.univie.ac.at/vmf ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] How to make a good documentation of a database ?
Autodoc might be useful: http://www.rbt.ca/autodoc/ On Nov 18, 2004, at 8:31 AM, David Pradier wrote: Hi ! I've just inherited the responsibility of a postgresql database of roughly 480 tables and 6460 columns, mainly without constraints, not even foreign keys. I'd like to make it a little more orthodox (lots and lots of constraints, yeah !!), but I need a tool to make a documentation about every column, at least, as some column are really vicious (like, they are a foreign key to a table which depends on the type of another column...). The best idea I could come with to do that was to maintain an output of pgdump --shema-only, versioned with cvs, annotated with a patch, itself versioned with cvs. Not that bright, isn't it ? The problem is, I don't want to use a lot of time to maintain this documentation, and above all, I'd prefer not to insert the information twice (read: a new constraint in the database should automagically update the documentation). Does somebody know the right way to do this ? Best regards, David Pradier -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.45.20.17.98 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] error querying temp table in plpgsql function
(PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6)
I'm working on a function which creates and populates a temporary
table, then returns the number of records it has inserted. I'm getting
an error, though, after successive invocations of the function (I can
call it once successfully, but on the next call I get an error). I've
been able to reproduce the error with the following sample function:
create or replace function test() returns integer as
$$
declare result integer;
begin
-- drop temp table, if it exists (ignore exception if it doesn't)
begin
execute 'drop table test';
exception
when undefined_table then
null; -- do nothing
end;
-- create the vehicle route table
execute 'create temp table test ('
|| 'seq_num serial not null, '
|| 'foo text'
|| ')';
select count(*) into result from test;
return result;
end;
$$
language 'plpgsql';
Here is a clipping of a psql session which creates the function and
calls it twice, along with the error that results:
silo=# \i test.sql
CREATE FUNCTION
silo=# select test();
NOTICE: CREATE TABLE will create implicit sequence test_seq_num_seq
for serial column test.seq_num
CONTEXT: SQL statement create temp table test (seq_num serial not
null, foo text)
PL/pgSQL function test line 13 at execute statement
test
--
0
(1 row)
silo=# select test();
NOTICE: CREATE TABLE will create implicit sequence test_seq_num_seq
for serial column test.seq_num
CONTEXT: SQL statement create temp table test (seq_num serial not
null, foo text)
PL/pgSQL function test line 13 at execute statement
ERROR: relation with OID 524907 does not exist
CONTEXT: SQL statement SELECT count(*) from test
PL/pgSQL function test line 18 at select into variables
silo=#
If, instead of executing the select count(*) directly, I use the
EXECUTE command, then everything works. Is this expected behavior?
Thanks,
Tim
---(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 about temp table in function
I'm working on a plpgsql function that creates and populates a temporary table. I would like the function to first drop the temp table, if it already exists. I'm not sure how to accomplish this, though. My first inclination was to simply wrap the 'drop table' command in an exception handling block and ignore the exception if the table does not exist. I'm not sure what error condition to catch, though, so rather than specifying a WHEN condition, I just had something like the following: begin execute 'drop table my_temp'; exception -- do nothing end; That didn't work; apparently the WHEN condition is necessary. What condition should I be trapping for? Is there a better way to accomplish this? I thought about querying pg_tables, and seeing if a record exists. Would that be a better approach? Thanks, Tim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] question about temp table in function
On Nov 16, 2004, at 2:11 PM, Michael Fuhr wrote: \set VERBOSITY verbose SELECT foo(); ERROR: 42P01: table my_temp does not exist CONTEXT: SQL statement DROP TABLE my_temp PL/pgSQL function foo line 2 at SQL statement LOCATION: DropErrorMsgNonExistent, utility.c:144 The error code is 42P01, which Appendix A shows as UNDEFINED TABLE. The exception-handling block would therefore be: BEGIN DROP TABLE my_temp; EXCEPTION WHEN undefined_table THEN NULL; END; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ That's exactly what I needed. I didn't think to set the verbosity to get the error code. Thanks! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL on Linux PC vs MacOS X
I've been working with PostgreSQL on OS X (G4, G5 and dual G5 systems) for a few months now, and overall I've been really pleased; for us, it seems to be a good match. If you have both an OS X and a Linux or BSD system available, you could run pgbench against both and get a rough idea on how they compare. On Nov 4, 2004, at 1:33 PM, William Yu wrote: My guess is that you will get better performance from a similarly priced Dual Opteron for the following reasons: 1) OS-X is not 64-bit yet, 64-bit Linux/BSD OS's are available 2) GCC is far better tuned for x86 than PowerPC/Itanium/etc 3) Postgres *seems* to prefer Opteron's ondie memory controller architecture over shared bus -- especially in SMP configs Is it enough of a difference? If you needed to eek out every possible % performance because you have a critical production need, then the answer is yes. Otherwise, it's easier to stick with the OS you know. Jim Strickland wrote: Well, the whole reason I have asked this question is because my developer swears by OS X and PostgreSQL. However, I wanted opinions from other people who have possibly used a similar setup so I can make an informed decision. I will certainly keep your advice in mind. I guess the only reason I was asking about the version of OS X and the G5 processor, is because that is all my developer uses and he seems to think they make a great combination, but that seems to be at odds with your experience. Perhaps some others will weigh in with their experiences and I will be able to make a sound decision. Fortunately there is no great rush to decide. Thanks for your help. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Confusion about inheritance -- NEVER MIND
As you've discovered, primary key constraints (and other constraints) do not inherit, but perhaps something like the following will work for you: create table base (id serial primary key, (other fields to be inherited...)); create table derived (new_field1 varchar(10), (other new fields...), constraint derived_pkey primary key(id)) inherits base; Hope this helps, Tim On May 12, 2004, at 11:16 AM, [EMAIL PROTECTED] wrote: Well well, after I posted my request for help, I thought to search the mailing list archive, and lo! and behold! there was a discussion in December 2003 of the exact same problem, with the resolution that it is a design limitation, it may have been put on some request list, and it looks like I need to go back to individually defined tables or find some other workaround ... So unless someone has developed a patch for this for 7.4.2, I will now continue with my irregularly scheduled poking around and learning ... Thanks to everybody for PostgreSQL anyway :-) -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] questions on rules
It seems that triggers are not inherited, so to get the functionality I want I'll have to create a trigger for each table. If anyone knows another way, please let me know! After you pointed me in the right direction, I was able to create a trigger procedure which can be called from triggers on various tables and will log the operation (including the affected table's oid and name). The procedure is listed below. Thanks for the help! Tim create or replace function add_log_entry() returns TRIGGER as ' BEGIN insert into audit_log(table_oid, table_name, id, operation) values (TG_RELID, TG_RELNAME, NEW.id, TG_OP); return NEW; END; ' language 'plpgsql'; On Apr 27, 2004, at 8:18 AM, Richard Huxton wrote: On Tuesday 27 April 2004 13:40, Timothy Perrigo wrote: Thanks for the reply. Do you know if triggers defined on a base table fire for operations on inherited tables? (I.e., if I have an after insert trigger on table base, and a table derived that inherits from base, will inserts into derived cause the trigger on base to fire?) Hmm - don't know this I'm afraid. If so (this is the behavior I would like), is there a way to get the tableoid of the table which caused the trigger to fire? Here I can help. Check the plpgsql section of the manuals, and there you'll find a list of special variables available to trigger functions. These include table and trigger name. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
