[ADMIN] table name as parameter in pl/psql
hi all, i'm trying to do something like this: CREATE FUNCTION read_table(text) RETURNS int AS ' DECLARE table_name ALIAS FOR $1; res INTERGER; BEGIN SELECT INTO res COUNT(id) FROM table_name; RETURN res; END; ' LANGUAGE 'plpgsql'; using psql the creation return no errors, but the statement SELECT read_table( 'books' ) AS how_many; resuts in ERROR: parser: parse error at or near $1 and the same using SELECT INTO res COUNT(id) FROM $1; instead of SELECT INTO res COUNT(id) FROM table_name; while SELECT INTO res COUNT(id) FROM books ( the real name of the table ) works good there is a way to use a table name as parameter? where am i wrong? thx Ivan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Moving a database
Hi all, we recently upgraded from 6.53 to 7.1.2, and at the same time tried to move the database to a new filesystem. However, while the upgrade was 100% successful using pg_dumpall, we see that postgres is still reading some files from the old file system (though only updating the new files). An example is pg_shadow which is read on both file systems whenever someone seems to authenticate, but only updated on the new file system. Does anyone have any ideas? Is it possible to move the location of Postgres in this manner? Thanks, Ewan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Moving a database
Ewan Leith [EMAIL PROTECTED] writes: An example is pg_shadow which is read on both file systems whenever someone seems to authenticate, but only updated on the new file system. I don't believe it for a minute. Please describe the actual problem you're having, rather than jumping to conclusions about the mechanism. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Help! System catalogs vanished!
Hi there, I'm having a just wonderful Friday at work. Not. At around 2am this morning, probably in the middle of the nightly scheduled VACUUM ANALYZE, the UPS on my server failed. This morning, I have no databases. The details: PostgreSQL 7.0.3 on IRIX 6.5.11m. When I started the machine this morning, I couldn't start the postmaster with pg_ctl at all. Firstly it claimed there was another postmaster running (there wasn't) and then when I removed the postmaster.pid file, pg_ctl just said it couldn't execute postmaster. When I started postmaster by hand with: /usr/freeware/bin/postmaster -D /usr/data/pgsql -o -i -S it started BUT now watch this: jove bin$ psql stowe3 Welcome to psql, 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 stowe3= \d stowe3= \dS stowe3= select now(); stowe3= select version(); stowe3= This is, to my mind, extremely unhealthy. To my customers' minds too for that matter. The behaviour is the same no matter which of the 8 databases I connect to. Also, I don't know if it's relevant, but all of them have pg_table files of zero length. SO, I really need to sort this out pronto-urgently. If I can't do anything to restore them within the next few hours, I'll have to do a fresh initdb and start restoring and reconstructing. Frankly though, I'd much rather have a weekend. *sigh* Hal ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] What is libpq.so.2.1 ?
Hallo, I`´m newbie in postgreSQL. Please help me, if you can. Q: After start postgreSQL on my system (SuSE 7.0) with postgreSQL 7.0 an creat a new directory /var/lib/pgsql/data I want creat a new db with createdb Personal. The system tells me, that it culd not found libpq.so.2.1 That is so ! At my system is only libpq.so.1.1 and a link from libpq to libpq.so.1.1 Where can get this missing lib... ? Or please tell me an other way. Armin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] How to rename a database
Does anyone know how to rename a database on postgres 7.1? Thank you in advance. Ligia ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] pgdumpall_file is bigger than 2 Gigabyte
David M. Richter schrieb: Hello! Ive got a problem! My database has the size of almost 5 Gigabytes. So the dump will take at least 2 Gigs of harddisk. But my Kernel supports only 2 Gig Files! Any experiences with big dumpfiles? Thanks a lot DAvid Hello I guess split and gzip are your friends. You can pipe the pgdumpall to split with option to cut the file into pieces. Recommended is to use also gzip or similar to compress the files. Hope this helps -- Andreas Hödle (Systemadministration) Kühn Weyh Software GmbH Linnestr. 1-3 79110 Freiburg WWW.KWSOFT.DE ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Primary Key Problems
On Fri, 7 Dec 2001, Phill Kenoyer wrote: I took your advice and checked the string size using char_length() and octet_length(). They are the same. octet_length | stock| octet_length | inventory_type | client_id 5 | 10725 |4 | used |60 5 | 10725 |4 | used |60 I do a daily pg_dump of the database and the scheme I posted was from that. So the primary key is still active. I just dont understand how I can have dups in the primary keys, unless it is a bug. Hmm, what does explain show for your query? I'd wonder if the unique index got corrupted for some reason and so didn't see the first value when the second was inserted. If you search for a specific value and try to force an index scan (use set enable_seqscan=false) do you still get two rows (and does explain in that case show it using the index?) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] table name as parameter in pl/psql
On Mon, 10 Dec 2001, [utf-8] [EMAIL PROTECTED] wrote: hi all, i'm trying to do something like this: CREATE FUNCTION read_table(text) RETURNS int AS ' DECLARE table_name ALIAS FOR $1; res INTERGER; BEGIN SELECT INTO res COUNT(id) FROM table_name; RETURN res; END; ' LANGUAGE 'plpgsql'; using psql the creation return no errors, but the statement SELECT read_table( 'books' ) AS how_many; resuts in ERROR: parser: parse error at or near $1 and the same using SELECT INTO res COUNT(id) FROM $1; instead of SELECT INTO res COUNT(id) FROM table_name; while SELECT INTO res COUNT(id) FROM books ( the real name of the table ) works good You need to look into using EXECUTE if you want to specify tables on the fly, and you may need something like: create function read_table(text) returns int as ' declare table_name alias for $1; rec record; begin for rec in EXECUTE ''select count(*) from '' || table_name LOOP return rec.count; END LOOP; return 0; end;' language 'plpgsql'; ---(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
[ADMIN] problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7
Hello-- I have been having trouble getting subselect queries to complete on a Sun E-450 running Solaris 7 and PostgreSQL 7.1.3.Just about any subselect query I try to run will fail to complete execution---Postgres's serverlog indicates that the process associated with the query gets killed after a few minutes have gone by. I've tried running subselects from an interface to Postgres which uses a socket connection, and I've tried running them directly from the command line, always with the same result. The E-450 has two 400 MHz processors and 1.5 GB of RAM.Here's an example of the kind of query I've tried: SELECT count(*) FROM users WHERE id NOT IN ( SELECT users_id FROM users_demographics ); What's weird is that I can successfully run the same queries on a Solaris x86 box with a single 233MHz Pentium chip, and 96 MB of RAM, also running PostgreSQL 7.1.3 but with Solaris 8. The database contains about 650,000 records in the users table, and only slightly less than that in the users_demographics table. I dumped the database from the E-450 and re-imported it into the x86 box to be sure that I was working with the same volume of data. The x86 box completes the query in about 10 minutes, but the E-450 can't complete the query at all. The resources of the E-450 don't seem to be overly taxed by postgres (see results of top over a 15-20 minutes period included below), and yet the comparative performance of the x86 box makes it sound like there is some kind of system problem responsible. Is anyone aware ofany problems with doing subselects in PostgreSQL with Solaris 7? Might there be other system-related issues or Postgres-related issues responsible for this?I would appreciate any suggestions you might have! Thank you! Heather Johnson load averages: 0.31, 0.11, 0.1049 processes: 47 sleeping, 2 on cpuCPU states: 25.5% idle, 25.2% user, 4.0% kernel, 45.2% iowait, 0.0% swapMemory: 1536M real, 24M free, 1987M swap in use, 693M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND24283 postgres 1 21 0 14M 6768K cpu/1 0:37 23.01% postgres24352 postgres 1 0 0 2168K 1432K cpu/3 0:00 0.78% top4846 nobody 1 60 4 1782M 1006M sleep 380:20 0.21% impress12047 nobody 1 54 0 148M 145M sleep 0:04 0.08% httpd 239 root 1 58 0 0K 0K sleep 14:54 0.01% sysedge.sol27-s 258 root 6 58 0 14M 3192K sleep 91:41 0.00% mysqld 1 root 1 58 0 752K 152K sleep 9:00 0.00% init 201 root 15 59 0 3376K 808K sleep 8:08 0.00% syslogd28712 postgres 1 58 0 13M 664K sleep 0:30 0.00% postgres 210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron 192 root 5 58 0 3408K 840K sleep 0:17 0.00% automountd 221 root 1 58 0 1016K 240K sleep 0:08 0.00% utmpd3220 root 1 58 0 2528K 448K sleep 0:05 0.00% sshd 233 root 1 59 -12 2080K 456K sleep 0:05 0.00% xntpd 225 root 1 58 0 2376K 504K sleep 0:04 0.00% sendmail load averages: 0.41, 0.23, 0.1549 processes: 47 sleeping, 2 on cpuCPU states: 1.7% idle, 49.3% user, 2.0% kernel, 47.1% iowait, 0.0% swapMemory: 1536M real, 22M free, 1987M swap in use, 693M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND24283 postgres 1 0 0 14M 6728K cpu/1 1:59 32.91% postgres24358 postgres 1 0 0 2168K 1424K cpu/3 0:02 1.00% top4846 nobody 1 60 4 1782M 1005M sleep 380:21 0.18% impress24337 root 1 58 0 2488K 1688K sleep 0:00 0.02% sendmail 258 root 6 58 0 14M 3824K sleep 91:41 0.00% mysqld 239 root 1 58 0 0K 0K sleep 14:54 0.00% sysedge.sol27-s 1 root 1 58 0 752K 144K sleep 9:00 0.00% init 201 root 15 59 0 3376K 832K sleep 8:08 0.00% syslogd28712 postgres 1 58 0 13M 720K sleep 0:30 0.00% postgres 210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron 192 root 5 58 0 3408K 816K sleep 0:17 0.00% automountd 221 root 1 58 0 1016K 264K sleep 0:08 0.00% utmpd3220 root 1 58 0 2528K 448K sleep 0:05 0.00% sshd 233 root 1 59 -12 2080K 488K sleep 0:05 0.00% xntpd12047 nobody 1 58 0 148M 145M sleep 0:04 0.00% httpd load averages: 0.47, 0.26, 0.1649 processes: 47 sleeping, 2 on cpuCPU states: 20.1% idle, 24.1% user, 4.0% kernel, 51.8% iowait, 0.0% swapMemory: 1536M real, 23M free, 1987M swap in use, 693M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND24283 postgres 1 30 0 14M 6568K cpu/3 2:17 32.27% postgres24358 postgres 1 0 0 2168K 1424K cpu/1 0:02 0.99% top4846 nobody 1 60 4 1782M 1004M sleep 380:21 0.20% impress 239 root 1 58 0 0K 0K sleep 14:54 0.01% sysedge.sol27-s24337 root 1 58 0 2488K 1688K sleep 0:00 0.00% sendmail 258 root 6 58 0 14M 3816K sleep 91:41 0.00% mysqld 1 root 1 58 0 752K 136K sleep 9:00 0.00% init 201 root 15 59 0 3376K 832K sleep 8:08 0.00% syslogd28712 postgres 1 58 0 13M 696K sleep 0:30 0.00% postgres 210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron 192 root 5 58 0 3408K 840K sleep 0:17 0.00% automountd 221 root 1 58 0 1016K 264K sleep 0:08 0.00% utmpd3220 root 1 58 0 2528K 448K sleep 0:05 0.00% sshd 233 root 1 59 -12 2080K 480K sleep 0:05 0.00%
Re: [ADMIN] problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7
Heather Johnson [EMAIL PROTECTED] writes: I have been having trouble getting subselect queries to complete on a Sun E= -450 running Solaris 7 and PostgreSQL 7.1.3. Just about any subselect query= I try to run will fail to complete execution---Postgres's serverlog indica= tes that the process associated with the query gets killed after a few minu= tes have gone by. Could we see the exact text of the log entries? Is it possible that you have some kernel limit on CPU time, memory space, etc expended by any one backend process? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
