[GENERAL] Re: With which user Apache accesses PostgreSQL
On Sun, Mar 04, 2001 at 08:07:20PM -0300, Paulo Parola wrote:
[ . . . ]
> >
>
> My question should be: with which user Apache accesses PostgreSQL? From the
> message issued by PostgreSQL it is user 'apache'.
On most systems it seems to be either wwwrun or nobody by default.
>
> Does that mean that *any* tables accessed through the Web should be owned by
> an user named 'apache' created with PostgreSQL program 'createuser'?
No, it just means that you need to grant this user permission to read
data from a table owned by someone else, or write to it if that's
necessary (think about whether to allow that or not; it might be a
security risk) with the grant command:
Command: GRANT
Description: Grants access privilege to a user, a group or all users
Syntax:
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
e.g.:
grant select on to apache;
Regards, Frank
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Two way encription in PG???
Peter Eisentraut wrote: > Boulat Khakimov writes: > >> How do I encrypt/decrypt something in PG? > > > Download 7.1 and use the contrib/pgcrypto module. > I looked at the pgcrypto README file and it seems that it only makes one-way hashes. None of the functions had a key argument of any kind. The encode/decode functions are for bin/ascii encoding/deconding, not cryptographic. Any more info about this? Thnaks, Jeff Davis ---(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] postgres locks...
Tom Lane wrote: > Jeff Davis <[EMAIL PROTECTED]> writes: > >> Basically, psql would sit there trying to connect, meanwhile all the >> attempted connections wouldn't die. > > > More details please? What do you mean exactly by the above statements? > >> I had to "kill -9" all the >> postgres/postmaster processes. > > > Killing individual backends with kill -9 is NOT NOT NOT a recommended > procedure. In theory you can get away with it but why take risks? > Use the documented shutdown procedures to give the thing some chance > of cleaning up after itself. > > regards, tom lane > > > I realize that it is not good to kill the backends, but I tried the documented SIGTERM to postmaster and it didn't work. SIGKILL was the only thing that would kill a hanging backend, and once those were down I could gracefully SIGTERM the postmaster with pg_ctl. I reasearched the problem a little more (I mentioned above in a reply to my own message). What would happen is this: 1) I try to make a standard connection to database "A" with psql 2) psql sits there doing nothing for a seemingly infinate amount of time 3) I Ctl-C psql to get back to the shell after several minutes 4) I look at the output of "ps ax| grep post" and get processes like: /usr/local/pgsql/bin/postgres [args about how I tried to connect] as well as the postmaster 5) I try to stop using pg_ctl (seems ok) 6) I try to start with pg_ctl (gives error about a /tmp/.s..PGSQLfile) 7) I delete the file, and try again (shared mem errors) 8) I run ipcclean and it seems to eventually work (BTW: I looked at the script and it seems to check for the output of a "ps ... | grep ..." command, which sometimes returns the grep process itself, and sometimes doesn't, so I had to run it until it didn't think the backend was running). 9) I start it successfully 10) same thing happens If I initdb another location it is fine. Appearently, the only bad database is database A, the rest can be connected to from my real location anyway. After I droped/recreated the database, it worked fine (but no more tables, obviously). The rest of the DBs were unaffected. I have a .tar of the "bad" DB, if that would help. I apologize if the is not 100% accurate, but it should be very close to what happened. I didn't want to take my backend down for another hour to recreate the problem a third time. Thanks for any more help, Jeff Davis ---(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] postgres locks... [more information]
Ok, I think I know what happened, kind of.. One of my users' databases was currupt. I actually had to delete the entire base/ folder. Then I went in and drop/created the DB again and it seems to work fine. Is there any was to know how this happened? I have a .tar of the currupt DB if someone is interested... I would hope that 7.1 fixes this issue, any thoughts? Thanks, Jeff Davis Jeff Davis wrote: > I think this is a bug, but I don't have really enough info to go on > for a report. I was running postgres on my server and everything > seemed OK. Eventually I figured out that some of my websites weren't > loading and connected it to postgres. > > Basically, psql would sit there trying to connect, meanwhile all the > attempted connections wouldn't die. I had to "kill -9" all the > postgres/postmaster processes. Then I ran pg_ctl start, and it seemed > OK. However, same problem. So, I killed again, then ran ipcclean > (there were shm errors), the started and it worked. I had to delete a > socket file in /tmp also in order to get it going again. > > During the time, I appearently lost several unimportant tables. I am > running 7.0.3/debian-woody. Is this a known problem? I really need to > have a reliable version of postgres running. > > Thanks for any advice, > Jeff Davis > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
With which user Apache accesses PostgreSQL (was Re: [GENERAL] How to automatically start postmastrer with TCP/IP support (-i option) ???)
- Original Message - From: Uro Gruber <[EMAIL PROTECTED]> To: Paulo Parola <[EMAIL PROTECTED]> Sent: Sunday, March 04, 2001 7:14 PM Subject: Re: [GENERAL] How to automatically start postmastrer with TCP/IP support (-i option) ??? > Hi, > > Sunday, March 04, 2001, 10:38:04 PM, you wrote: > > PP> Hi, > > PP> So my questions are: > > PP> 1) How should I make so that PostgreSQL is automatically started accepting TCP/IP connections like I did by hand with the command above ( > PP> nohup /usr/bin/postmaster -i -D /var/lib/pgsql/data >server.log 2>>1 &) ??? > Use command pg_ctl (read the manual) > > PP> Which file should I edit to add option '-i' for accepting TCP/IP connections? Apparently, in shell script '/etc/rc.d/init.d/postgresql' the line that starts the postmaster reads 'su -l postgres > PP> -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null', is that right? > You don't have to edit any data, you can tell this in with command > line i think option -o and then options switches. > It would not help me to issue the command from the command line, since I need TCP/IP connections set everytime I reboot my server. Since the shell script run by Linux to start the postmaster is '/etc/rc.d/init.d/postgresql' I finally came to the correct syntax of the line I should edit in this file: Original: su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null Changed to: su -l postgres -c "/usr/bin/pg_ctl -o '-i' -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null I then rebooted the server and it worked, as shown by the process running which has option '-i' set : postgres 836 0.0 2.0 5092 1288 ?S19:45 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data -i > PP> 2) With which user shall I connect to the database? User "apache" or maybe user "nobody" ? Should I create a user 'apache' in PostgreSQL? > Anyone you like. You just have to make user. First connect you have to > make with pgsql user which is admin for sesrver and then you create > another user. And again read the manual, everything is in it. > My question should be: with which user Apache accesses PostgreSQL? From the message issued by PostgreSQL it is user 'apache'. Does that mean that *any* tables accessed through the Web should be owned by an user named 'apache' created with PostgreSQL program 'createuser'? For example: database test is owned by user 'postgres'. I then issue the following commands: $ createuser apache Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER $ psql -Upostgres teste 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 teste=# \c teste You are now connected to database teste. teste=# \l List of databases Database | Owner | Encoding ---+--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII teste | postgres | SQL_ASCII (3 rows) teste=# \d List of relations Name | Type | Owner ---+---+ teste | table | postgres teste=# alter table teste owner to apache; ALTER teste=# \d List of relations Name | Type | Owner ---+---+ teste | table | apache (1 row) And now my PHP script can connect to my table teste (owned by user 'apache') in database teste (owned by user 'postgres'). Is this the correct way to do it? TIA, Paulo > > > -- > , > Urosmailto:[EMAIL PROTECTED] > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pgaccess on Win9x
Hi Y'all: Unzipped pgaccess to C:\. Have TclPro 1.4 at c:\Program Files\TclPro1.4 Executing C:\pgaccess\main.tcl I get: couldn't load library "libpgtcl.dll": this library or a dependent library could not be found in library path while executing "load libpgtcl[info sharedlibextension]" [procedure "main" line 3] invoked from within "main $argc $argv" [file "C:\PGACCESS\MAIN.TCL" line 249] Copied libpgtcl83.dll-7.0 to \windows\system as libpgtcl.dll. Appreciate your help. -bill __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(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] INSERT ... RETURNING as Oracle
Title: RE: [GENERAL] INSERT ... RETURNING as Oracle And using MAX is also horrifically slow once you start having any significant volumes of data. Why not write a function to add the info, which selects from a sequence, inserts the new row using the sequence number, and then returns the sequence number? I've done it a number of times, and it's been quite successful so far. Cheers... MikeA -Original Message- From: Peter Eisentraut To: Sipos Andras Cc: [EMAIL PROTECTED] Sent: 3-4-01 8:04 PM Subject: Re: [GENERAL] INSERT ... RETURNING as Oracle Sipos Andras writes: > create table basket ( > id serial NOT NULL PRIMARY KEY, > timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP > ); > > How can I make a one step insert into the table and get values of 'ID' ? > I am trying to find a similar solution as in the oracle's INSERT ... > RETURNING. We have this sort of thing on the wish list, but it doesn't exist yet. Some people feel it's better to calculate the default in the client program and insert it explicitly. This may be a reasonable alternative for some applications. > If I use at first the INSERT, and after SELECT MAX(ID), the result will be > uncertain. If you are worried about other transactions getting in the way then you should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
[GENERAL] How to automatically start postmastrer with TCP/IP support (-i option) ???
Hi, I am trying to configure PostgreSQL 7.1 over RedHat 7.0 (according to Lamar Owen the RPM's for 7.1 were built on RedHat 6.2 and not 7.0 - does that implies any possible problems?). I have PostgreSQL already starting automatically but with no support to TCP/IP connections. I can see this by the following: [root@atlas pparola]# ps -auxw | grep postgrespostgres 836 0.0 2.0 5092 1284 ? S 17:14 0:00 /usr/bin/postmaster -D /var/lib/pgsql/dataroot 982 0.0 0.9 1488 584 pts/0 S 17:19 0:00 grep postgres And when trying to connect to PostgreSQL from PHP I get the following message: Warning: Unable to connect to PostgresSQL server: PQconnectPoll() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port 5432? in /var/www/html/pgsql.php on line 3Could not connect -- So I started it by hand with the following commands: su - postgres cd /var/lib/pgsql/data nohup /usr/bin/postmaster -i -D /var/lib/pgsql/data >server.log 2>>1 & When accessing the same PHP script I got the following output: Warning: Unable to connect to PostgresSQL server: FATAL 1: user "apache" does not exist in /var/www/html/pgsql.php on line 3Could not connect - So my questions are: 1) How should I make so that PostgreSQL is automatically started accepting TCP/IP connections like I did by hand with the command above ( nohup /usr/bin/postmaster -i -D /var/lib/pgsql/data >server.log 2>>1 &) ??? Which file should I edit to add option '-i' for accepting TCP/IP connections? Apparently, in shell script '/etc/rc.d/init.d/postgresql' the line that starts the postmaster reads 'su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null', is that right? 2) With which user shall I connect to the database? User "apache" or maybe user "nobody" ? Should I create a user 'apache' in PostgreSQL? TIA, Paulo
[GENERAL] Re: Trigger sending an eMail
On Mon, Feb 26, 2001 at 06:09:58PM -0300, Tulio Oliveira wrote:
> How is the best form of a trigger send an email ?
>
> I'll need make the trigger in C or the plpgsql has any mail function ?
#if !defined(_PATH_SENDMAIL)
# define _PATH_SENDMAIL "/usr/lib/sendmail"
#endif /*SENDMAIL*/
#define MAILCMD _PATH_SENDMAIL
#define MAILARGS "%s -Fpostgres -oem -t"
/* send notification e-mail
*/
static int sendmail(char * mailto, char * subject, char * mess) {
register FILE *mail;
char * mailcmd;
asprintf(&mailcmd, MAILARGS, MAILCMD);
if (!(mail = popen(mailcmd, "w"))) {
elog(ERROR, "Couldn't run command %s", MAILCMD);
(void) _exit(ERROR_EXIT);
}
free(mailcmd);
fprintf(mail, "From: Apartia Auction Daemon <[EMAIL PROTECTED]>\n");
fprintf(mail, "To: [EMAIL PROTECTED]\n");
fprintf(mail, "Subject: %s\n", subject);
fprintf(mail, "User-Agent: to %s\n", mailto);
fprintf(mail, "\n");
fprintf(mail, mess);
fprintf(mail, "\n.\n");
return pclose(mail);
}
--
-= this .sig is not funny =-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: avoiding endless loop in an UPDATE trigger
On Mon, Feb 26, 2001 at 11:53:51AM -0800, Norman J. Clarke wrote: > Hello, > > I am attempting to write a trigger function or rule in pl/pgsql that runs > on UPDATE to a table named "nodes". From inside this trigger, I would like > to UPDATE the same "nodes" table. How can I do this without entering into > an endless loop? What I did is add a no_update bool to the table and test: if new.no_update = true then new.no_update = false; else new.no_update = true; endif -- Jesus is coming! Everyone look busy! ---(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] INSERT ... RETURNING as Oracle
"Sipos Andras" wrote: >create table basket (
> id serial NOT NULL PRIMARY KEY,
> timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
>);
>
>How can I make a one step insert into the table and get values of 'ID' ?
>I am trying to find a similar solution as in the oracle's INSERT ...
>RETURNING.
>
>If I use at first the INSERT, and after SELECT MAX(ID), the result will be
>uncertain.
The serial data type is actually an INT4 with a sequence, as you will have
seen when you created your table. Use currval after the insert to get the
latest value of the sequence in your current session.
junk=# create table basket (
junk(# id serial NOT NULL PRIMARY KEY,
junk(# timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
junk(# );
NOTICE: CREATE TABLE will create implicit sequence 'basket_id_seq' for SERIAL column
'basket.id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'basket_pkey' for table
'basket'
CREATE
junk=# insert into basket (timestamp) values (now());
INSERT 2091655 1
junk=# select currval('basket_id_seq');
currval
-
1
(1 row)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Give, and it will be given to you. A good measure,
pressed down, taken together and running over,
will be poured into your lap. For with the same
measure that you use, it will be measured to
you." Luke 6:38
---(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] INSERT ... RETURNING as Oracle
Sipos Andras writes: > create table basket ( > id serial NOT NULL PRIMARY KEY, > timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP > ); > > How can I make a one step insert into the table and get values of 'ID' ? > I am trying to find a similar solution as in the oracle's INSERT ... > RETURNING. We have this sort of thing on the wish list, but it doesn't exist yet. Some people feel it's better to calculate the default in the client program and insert it explicitly. This may be a reasonable alternative for some applications. > If I use at first the INSERT, and after SELECT MAX(ID), the result will be > uncertain. If you are worried about other transactions getting in the way then you should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] INSERT ... RETURNING as Oracle
Hi, Sample table: create table basket ( id serial NOT NULL PRIMARY KEY, timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); How can I make a one step insert into the table and get values of 'ID' ? I am trying to find a similar solution as in the oracle's INSERT ... RETURNING. If I use at first the INSERT, and after SELECT MAX(ID), the result will be uncertain. my system version: 7.0.2 Thx, Andras [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] can a trigger on insert -> update other tables?
On Sun, Mar 04, 2001 at 04:52:16AM -0500, Jan Wieck wrote:
> will trillich wrote:
> > [...]
> >
> > CREATE TRIGGER _rating_propagate
> >BEFORE INSERT ON _rating
> >FOR EACH ROW EXECUTE PROCEDURE _rating_propagate();
> >
> > ERROR: NEW used in non-rule function
> >
> > (this doesn't happen until an insert actually activates the
> > trigger, so that plpgsql finally 'sees' the code of the procedure
> > and tries to execute it.) so what else could i try?
> >
> > > And remember to do
> > > RETURN new
> > > somewhere.
> >
> > right. but now i have a different problem... :)
>
> Can you add the line
>
> #option dump
>
> at the very top of your function (before DECLARE) and send me
> the debug output from the Postmaster?
sure! if you like, i can post the source SQL for the ratings
table and its views and functions and triggers and rules...
-- begin LOGFILE --
Execution tree of successfully compiled PL/pgSQL function _rating_propagate:
Functions data area:
entry 0: REC new
entry 1: REC old
entry 2: VAR tg_name type name (typoid 19) atttypmod -1
entry 3: VAR tg_when type text (typoid 25) atttypmod -1
entry 4: VAR tg_level type text (typoid 25) atttypmod -1
entry 5: VAR tg_optype text (typoid 25) atttypmod -1
entry 6: VAR tg_relid type oid (typoid 26) atttypmod -1
entry 7: VAR tg_relname type name (typoid 19) atttypmod -1
entry 8: VAR tg_nargs type int4 (typoid 23) atttypmod -1
entry 9: VAR foundtype bool (typoid 16) atttypmod -1
entry 10: VAR opinion type bpchar (typoid 1042) atttypmod 5
entry 11: VAR courseID type int4 (typoid 23) atttypmod -1
entry 12: VAR topicID type int4 (typoid 23) atttypmod -1
entry 13: VAR eduIDtype int4 (typoid 23) atttypmod -1
entry 14: VAR profID type int4 (typoid 23) atttypmod -1
entry 15: RECFIELD rating of REC 0
entry 16: ROW *internal* fields courseID=var 11
entry 17: RECFIELD course of REC 0
entry 18: ROW *internal* fields topicID=var 12
entry 19: ROW *internal* fields eduID=var 13
entry 20: ROW *internal* fields profID=var 14
entry 21: RECFIELD who of REC 0
entry 22: RECFIELD student of REC 0
entry 23: RECFIELD who of REC 0
entry 24: RECFIELD student of REC 0
entry 25: RECFIELD who of REC 0
entry 26: RECFIELD student of REC 0
entry 27: RECFIELD who of REC 0
entry 28: RECFIELD student of REC 0
entry 29: RECFIELD who of REC 0
entry 30: RECFIELD rating of REC 0
entry 31: RECFIELD student of REC 0
entry 32: RECFIELD who of REC 0
entry 33: RECFIELD who of REC 0
entry 34: RECFIELD student of REC 0
Functions statements:
8:BLOCK <<*unnamed*>>
9: ASSIGN var 10 := 'SELECT upper(substring( $1 from 1 for 1)) {$1=15}'
10: SELECT 'SELECT _course.id WHERE _course.id = $1 {$1=17}'
target = 16 *internal*
12: SELECT 'SELECT _topic.id WHERE _topic.id = _course.topic AND _course.id = $1
{$1=11}'
target = 18 *internal*
14: SELECT 'SELECT _edu.id WHERE _edu.id = _topic.edu AND _topic.id = $1 {$1=12}'
target = 19 *internal*
17: SELECT 'SELECT _prof.who WHERE _prof.who = $1 AND _prof.course = $2 {$1=21,
$2=11}'
target = 20 *internal*
19: IF 'SELECT NOT $1 {$1=9}' THEN
20:RAISE ''Unable to match professor to class, for rating''
ELSE
ENDIF
22: IF 'SELECT $1 = 'A' {$1=10}' THEN
24:EXECSQL 'UPDATE _course SET a = a + 1 WHERE _course.id = $1 {$1=11}'
25:EXECSQL 'UPDATE _topic SET a = a + 1 WHERE _topic.id = $1 {$1=12}'
26:EXECSQL 'UPDATE _edu SET a = a + 1 WHERE _edu.id = $1 {$1=13}'
27:EXECSQL 'UPDATE _student SET a = a + 1 WHERE _student.who = $1 {$1=22}'
28:EXECSQL 'UPDATE _faculty SET a = a + 1 WHERE _faculty.who = $1 AND
_faculty.edu = $2 {$1=23, $2=13}'
29:EXECSQL 'UPDATE _prof SET a = a + 1 WHERE _prof.who = $1 AND _prof.course =
$2 {$1=14, $2=11}'
ELSE
32:IF 'SELECT $1 = 'C' {$1=10}' THEN
33: EXECSQL 'UPDATE _course SET c = c + 1 WHERE _course.id = $1 {$1=11}'
34: EXECSQL 'UPDATE _topic SET c = c + 1 WHERE _topic.id = $1 {$1=12}'
35: EXECSQL 'UPDATE _edu SET c = c + 1 WHERE _edu.id = $1 {$1=13}'
36: EXECSQL 'UPDATE _student SET c = c + 1 WHERE _student.who = $1 {$1=24}'
37: EXECSQL 'UPDATE _faculty SET c = c + 1 WHERE _faculty.who = $1 AND
_faculty.edu = $2 {$1=25, $2=13}'
38: EXECSQL 'UPDATE _prof SET c = c + 1 WHERE _prof.who = $1 AND _prof.course
= $2 {$1=14, $2=11}'
ELSE
41: IF 'SELECT $1 = 'D' {$1=10}' THEN
42:EXECSQL 'UPDATE _course SET d = d + 1 WHERE _course.id = $1 {$1=11}'
43:
Re: [GENERAL] CREATE INDEX function limitation
[EMAIL PROTECTED] (Bruce Richardson) writes: > In a CREATE INDEX statement, functions don't do type conversion and > can't be nested, meaning that the cast convertion function won't work. > So, > CREATE INDEX testidx on testtable (upper(CAST colname AS TEXT)); > fails. Is this a feature I should just work around? There's an oversight in the CREATE INDEX code in 7.0.* and before, which is that it rejects functions that are actually binary-compatible with the column datatype. This is fixed in 7.1, meaning that you can apply upper() to char(n) and varchar(n) columns not only text columns. Dunno if that's the only case you care about. In the general case you still can't ask for an arbitrary casting, because that would mean application of a conversion function, and we don't handle anything beyond one function call as the definition of a functional index. The standard workaround is to define your own function that encapsulates whatever computation you need to perform. This is kind of a pain in the neck though, so there's been talk of extending "functional indexes" into "expressional indexes" that would accept any scalar expression as their definition. Maybe someday ... regards, tom lane ---(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] NULL parameters abort functions
Bruce Richardson writes: > I discover that if a function is passed a NULL parameter then it simply > doesn't operate and a NULL value is returned. Is there *any* way round > that? It makes life incredibly complicated. Upgrade to 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Two way encription in PG???
Boulat Khakimov writes: > How do I encrypt/decrypt something in PG? Download 7.1 and use the contrib/pgcrypto module. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] triggers vs "NEW" pseudorecord
On Sun, Mar 04, 2001 at 07:07:24AM -0500, Jan Wieck wrote:
> will trillich wrote:
> > so altho the docs elsewhere say NOT to rely on access to the
> > pseudo table NEW within a trigger function, this part does work
> > like it should. but when i add SELECT or UPDATE it complains of
> > "NEW used in non-RULE query" -- what's the distinction?
>
> Can't reproduce such an error here - neither with 7.0.3 nor
> with 7.1. Could you please post a complete, reproduceable
> example of the failure.Tables, functions, trigger
> declarations, queries.
many thanks for your reply! and now ai have a new question (of course)--
i think i figured out the original snag -- i moved my assignments
OUT of the DECLARE section, and everything got much better:
declare
x char(1) := NEW.afield from 1 for 1; -- BOOM ("new in non-rule query")
now i do
declare
x char(1);
begin
x := NEW.afield from 1 for 1; -- no worries, mate
and it works like a charm. (maybe this is a feature. :)
--
but now that i have it working, it's horrendously slow and i can't
figure out how or where to optimize my results...
ratings table insertions propagate to
- course table (which also links to course)
- topic table (which also links to school)
- school table
- proftable (which also links to course and person)
- faculty table (which also links to school and person)
- student table
first i check to be sure each selector/seeker is legal, then i
update all six table, which seems like double work... any
suggestions are welcome:
SELECT school.id
INTO schoolID
WHERE school.code = NEW.schoolcode;
IF NOT FOUND THEN
RAISE EXCEPTION ...;
END IF;
SELECT topic.id
INTO topicID
WHERE topic.code = NEW.topiccode AND topic.school = schoolID;
IF NOT FOUND THEN
RAISE EXCEPTION ...;
END IF;
SELECT course.id
INTO courseID
WHERE course.code = NEW.coursecode AND course.topic = topicID;
IF NOT FOUND THEN
RAISE EXCEPTION ...;
END IF;
SELECT person.id
INTO facID -- all activity for this faculty member
WHERE faculty.login = NEW.faclogin AND faculty.id = person.id AND
prof.school = schoolID;
IF NOT FOUND THEN
RAISE EXCEPTION ...;
END IF;
SELECT person.id
INTO profID -- feedback for this person teaching this course
WHERE person.login = NEW.proflogin AND prof.id = person.id AND
prof.course = courseID;
IF NOT FOUND THEN
RAISE EXCEPTION ...;
END IF;
SELECT person.id
INTO stuID -- track feedback from this student
WHERE person.login = NEW.stulogin AND student.id = person.id;
IF NOT FOUND THEN
RAISE EXCEPTION ...;
END IF;
-- ---
-- and after all that, NOW we gotta UPDATE them all...
-- ---
IF feedback = ''A'' THEN
UPDATE student SET a = a + 1 WHERE id = stuID;
UPDATE faculty SET a = a + 1 WHERE id = facID AND school = schoolID;
UPDATE profSET a = a + 1 WHERE id = profID AND course = courseID;
UPDATE school SET a = a + 1 WHERE id = schoolID;
UPDATE topic SET a = a + 1 WHERE id = topicID AND school = schoolID;
UPDATE course SET a = a + 1 WHERE id = courseID AND topic = topicID;
//snip//for each grade type//
END IF
if any of y'all'uns have some ideas, i'd like to hear them--
--
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] NULL parameters abort functions
I discover that if a function is passed a NULL parameter then it simply doesn't operate and a NULL value is returned. Is there *any* way round that? It makes life incredibly complicated. -- Bruce ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Two way encription in PG???
Hi, Im porting my software from mySQL to PG. I need to encypt a field in a table using two way encription. In mySQL for those purposes I was using build-in functions ENCODE/DECODE, is there such an equivalent in PG? How do I encrypt/decrypt something in PG? Regards, Boulat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] How to Describe a field in the table???
Hi, Im porting my programs from mySQL to PG. mySQL supports a query "DESCRIBE tablename fieldname" , that provides a detailed description of a specific table field. Is there such an equivalent in PG? "\d tablename" gives description for all the fields in the table, however I need to find out a description for a specific field only. Regards, Boulat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
