Re: [GENERAL] subselect in CHECK constraint?
* Ian Turner [EMAIL PROTECTED] [000903 22:37] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 When I try to do this: CREATE TABLE test ( a Integer, b Integer, CHECK ((SELECT SUM(t.a) FROM test t WHERE t.b = b) 1000) ); INSERT INTO test (a, b) VALUES (100, 2); I get this error on the second query: ERROR: ExecEvalExpr: unknown expression type 108 I'm guessing this means I can't do subselects in CHECK statements. Two things: 1) i'm pretty sure this subselect can be rewritten as: SELECT SUM(t.a) 1000 FROM test t WHERE t.b = b to return a boolean. 2) you can probably get away with using a plpgsql function that has more logic in it. I'm not saying that subselects do or do not work, just offering some alternative advice. -Alfred
[GENERAL] Updating cursors
Hi, Ive a SELECT cursor which I want to update/delete but postgresql does not support these: UPDATE myTable SET myColumn = 'myValue' WHERE CURRENT OF myCursor DELETE myTable WHERE CURRENT OF myCursor Does there exist any workaround? Or is my syntax wrong? One workaround would be to get the row id and to be able to update it. Something like this: ... a row is selected and fetched ... int i = GetRowId( ); // C function that reads a unique row id. ExecuteSQL( "UPDATE myTable SET myColumn = 'myValue' WHERE @ROWID = %d", i ); ExecuteSQL( "DELETE myTable WHERE @ROWID = %d", i ); // ExecuteSQL(...) is a C function that parses and executes the string. // Jarmo PS. I hope Im sending this mail to the right address, if not Im sorry DS.
RE: [GENERAL] Updating cursors
-Original Message- From: Jarmo Paavilainen Hi, Ive a SELECT cursor which I want to update/delete but postgresql does not support these: UPDATE myTable SET myColumn = 'myValue' WHERE CURRENT OF myCursor DELETE myTable WHERE CURRENT OF myCursor Does there exist any workaround? Or is my syntax wrong? One workaround would be to get the row id and to be able to update it. Something like this: ... a row is selected and fetched ... You may be able to use CTID. You could get CTIDs by using SELECT statements like select CTID,* from myTable; and update using CTID update myTable set ..=.. where CTID=..; Note that CTIDs aren't of int type. Regards. Hiroshi Inoue
RE: [GENERAL] subselect in CHECK constraint?
-Original Message- From: Ian Turner -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 When I try to do this: CREATE TABLE test ( a Integer, b Integer, CHECK ((SELECT SUM(t.a) FROM test t WHERE t.b = b) 1000) ); INSERT INTO test (a, b) VALUES (100, 2); I get this error on the second query: ERROR: ExecEvalExpr: unknown expression type 108 I'm guessing this means I can't do subselects in CHECK statements. Yes. It would be very difficult to implement constraints other than column constraints. There seems to be 2 reasons at least. 1) We have to check the constraint not only for the row itself which is about to be insert/update/deleted but also for other related rows. As for your case,if b is updated the constraints not only for new b but also for old b should be checked. If the WHERE clause is more complicated what kind of check should we do ? 2) The implementation is very difficult without acquiring a table level locking. As for your case I couldn't think of any standard way to prevent the following other than acquiring a table level locking. When there's no row which satisfies b = 2,two backends insert values (500, 2) at the same time. Regards. Hiroshi Inoue
Re: [GENERAL] Updating cursors
Jarmo Paavilainen wrote: Hi, Ive a SELECT cursor which I want to update/delete but postgresql does not support these: UPDATE myTable SET myColumn = 'myValue' WHERE CURRENT OF myCursor DELETE myTable WHERE CURRENT OF myCursor PostgreSQL does not have the concept of updateable cursors. And that isn't planned either. Does there exist any workaround? Or is my syntax wrong? One workaround would be to get the row id and to be able to update it. Something like this: ... a row is selected and fetched ... int i = GetRowId( ); // C function that reads a unique row id. ExecuteSQL( "UPDATE myTable SET myColumn = 'myValue' WHERE @ROWID = %d", i ); ExecuteSQL( "DELETE myTable WHERE @ROWID = %d", i ); // ExecuteSQL(...) is a C function that parses and executes the string. Right. That'd work and the row ID you're looking for is it's OID. Up to now, all tables have a system attribute OID, that you can explicitly SELECT for such a purpose. Don't forget to create an INDEX on the OID if you go and do UPDATE yourTable SET yourColumn = 'yourValue' WHERE oid = oid_of_yourRow; Also don't forget that in a concurrent environment you probably want to SELECT ... FOR UPDATE the rows in the first place. // Jarmo PS. I hope Im sending this mail to the right address, if not Im sorry DS. pgsql-sql would've been. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[GENERAL] Instability in copying large quantities of data
Hi all... I've a big thorne in my side at the moment. I'developing a web app based essentially on a set of report. This reports are generated from queryes on my client's legacy system. For obviuos security reason, my app doesn't interacts directly with the main server, but is built around a Postgres DB on a separate machine (that is also the web server), and I set up a "poor man's replication" that batch transfer data from legacy server to pgsql server. In practice, the legacy server generates ASCII dumps of the data necessary for the reports and the zips'em and ftp'em to the web server. Then, a little process sheduled in cron get them up and COPY them in the pgsql system. I built this process using C and LibPQ (if necessary, I can post the code, but is a very simple thing and I assume you can figure up how it works). I used this schema many times for various web app, and I never encountered problems (I've got an app built eons ago, based on Slack 3.5 and PG 6.3.2, that's housed on a far-away provider and that never stopped a single second in all of this time. Wow!). Now I was trying it on a brand new RH 6.2 with PG 7.0.2, RPM version. The problem is that the COPY of the data, apparently, sometimes leaves a table in an inconsistent state. The command doesn't throw any error, but when I try to SELECT or VACUUM that table the backend dumps core. Apparently the only thing I can do is drop the table and recreate it. This is EXTREMELY unfortunate, since it all must be automated and if I can't catch any error condition during the update, than also the web app start crashing down... Sadly this happens in a very inconsistent way. However, it seems that the size of the data file is related to the frequency of the problem: and since some of the table dumps are more then 20 Meg, this is no good news. I have not got any log, cause the RPM versions doesn't create them, however, I'll try to fix this as soon as possible. In the meantime, anybody can share some hint on how to resolve this nightmare? /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
[GENERAL] Problem with drop table within a transaction
Hi, I have a problem when doing drop table within a transaction: It seems that if you execute a drop table sentence but after the transaction is aborted because an error occurs, the result is a corruption of the database: the drop table sentence deletes the physical files corresponding to the table, but as transaction is aborted the table is not deleted from the database definition. So then you cannot recreate the table (baclkend says it alredy exists), you cannot drop the table (it says it doesn't find the physical file), etc. (Anyway, it is not very critical : i'm not droping table each hour and i can do it in its own atomic transaction). Beyond that i would want to know if there is available information about the database definiton in postgresql: where the table is defined, how to 'fix' manually some problems (for instance, the one i have explained above). Thanks. Gabi :-)
Re: [GENERAL] How can I kow my permissions?
Waldemar Baraldi wrote:
I'm programming a database application that uses the API, but I have
some security issues that I have to manage, so I need to know my access
rights on a table and the "try" technique is not appropiate because if I
get a "Permission denied" then my transaction block is aborted and I
don't want that to happend.
The psql program can read it with the \z option so I know that it can be
done, so, how? from tha API, a query? which?.
Thanks in advance and sorry for my English, I speak Spanish.
H Waldemar,
you can query the system-table "pg_class". The filed "relacl" holds the acl for the
table.
The form is:
{RIGHT[,RIGHT]}
where RIGHT is in the form:
"[user]=[acl-for-user]"
acl-for-user is a combination of the following:
a - insert granted
r - select granted
w - update granted
R - rule granted
Sample Entry:
{"=","admin=arwR","simpleuser=r","updater=w"}
means:
PUBLIC: allowed nothing
admin: insert,select,update,rule
simpleuser: select
updater: update
Hope this works for you.
cu
Martin
--
Martin Neimeier
Ingenieur-Buero Neimeier
Schwarzach / Germany
mailto:[EMAIL PROTECTED] / http://www.ibn.de (under heavy reconstruction)
Tel:+49(6262)912344 / Fax:+49(6262)912347
Re: [GENERAL] PL/Perl compilation error
Hi,
I have take a look to the source code concerning PL/Perl, it seems that 2 variables
have a bad call : errgv and na.
If you replace them by their normal call (in 5.6.0) PL_errgv and PL_na you will get
success to compile the lib plperl.so.
Also in Perl documentation you will find the answer for backward compatibility :
The API function perl_get_sv("@",FALSE) should be used instead of directly accessing
perl globals as GvSV(errgv). The API call is backward compatible with existing perls
and
provides source compatibility with threading is enabled.
It seems to be easily repared. I have no time yet but I will take a look as soon as
possible.
Regards
Gilles
Alex Guryanow wrote:
Hi,
I have just installed Perl 5.6.0 and PostgreSQL 7.0.2. After successfull
installation of both these
programs I tried to make PL/Perl support. After running the commands from Postgres
manual I have
received the following errors
[GENERAL] Test
This is a test
[GENERAL] permissions foreign keys
Hi, I am having some problems setting up security on my database: I have a table 'feedback', with a foriegn key to a table 'feedback_type' (tables at end). I want to give the user all permissions on feedback, but to only be able to select the possible values from the feedback_type table. So, I have granted select, insert, update, delete on feedback, and only select on feedback_type. But... When I try to update feedback, (e.g. update feedback set fb_type = 'bug' where fb_id = 1011) I get ERROR: feedback_type: permission denied, and looking at the debug output, its doing: 'select oid from "feedback_type" where "fb_type" = $1 for update of "feedback_type". When I created the constraint, I just did: ALTER TABLE feedback ADD CONSTRAINT fk_feedback_type FOREIGN KEY (fb_type) REFERENCES feedback_type; I don't really see why it wants to update feedback_type? Can anyone tell me what I'm doing wrong, or will I just have to grant update on feedback_type (and all other tables referenced by FKs)? Thanks for any help, Tamsin The tables are (abbreviated): CREATE TABLE feedback ( fb_idINTEGER NOT NULL, usr_id INTEGER NOT NULL, fb_type VARCHAR(20) DEFAULT 'bug' NOT NULL, CONSTRAINT XPKfeedback PRIMARY KEY (fb_id) ); CREATE TABLE feedback_type ( fb_type VARCHAR(20) NOT NULL, CONSTRAINT XPKfeedback_type PRIMARY KEY (fb_type) );
[GENERAL] To: He Weiping Laser Henry
Thank you very much!!
Re: [GENERAL] permissions foreign keys
Tamsin wrote: I don't really see why it wants to update feedback_type? Can anyone tell me what I'm doing wrong, or will I just have to grant update on feedback_type (and all other tables referenced by FKs)? It doesn't want to update it. It just does the SELECT ... FOR UPDATE to lock the now referenced row. Doing it without a lock would make it possible, that just after your backend checked that the PK row exists but before you got a chance to commit, another backend could delete that PK without seeing your just inserted reference. End would be a violated FK constraint. The bug here is, that doing a SELECT ... FOR UPDATE already requires UPDATE permissions. The correct solution would be to require a REFERENCES privilege for the owner of the referencing table. But we don't have that up to now. Maybe I can do something about it for 7.1. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
RE: [GENERAL] permissions foreign keys
That's cleared that up, thanks! Tamsin -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: 04 September 2000 15:50 To: Tamsin Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] permissions foreign keys Tamsin wrote: I don't really see why it wants to update feedback_type? Can anyone tell me what I'm doing wrong, or will I just have to grant update on feedback_type (and all other tables referenced by FKs)? It doesn't want to update it. It just does the SELECT ... FOR UPDATE to lock the now referenced row. Doing it without a lock would make it possible, that just after your backend checked that the PK row exists but before you got a chance to commit, another backend could delete that PK without seeing your just inserted reference. End would be a violated FK constraint. The bug here is, that doing a SELECT ... FOR UPDATE already requires UPDATE permissions. The correct solution would be to require a REFERENCES privilege for the owner of the referencing table. But we don't have that up to now. Maybe I can do something about it for 7.1. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[GENERAL] test3
Scuse me, this is a test.
[GENERAL] Change to DatabaseMetaData.java for the jdbc2 driver (fwd)
I sent this to PATCHES last week, but it did not show up on the list.
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
-- Forwarded message --
Date: Thu, 31 Aug 2000 12:26:59 -0500 (EST)
From: Travis Bauer [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Change to DatabaseMetaData.java for the jdbc2 driver
This is a patch which lets the DatabaseMetaData return the object type
when getTables() is called. It does not really fix any bug, but it
fills in some functionality that should be there anyway. The diff
included here is off of the CVS as of just now :)
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
Index: DatabaseMetaData.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
retrieving revision 1.2
diff -u -r1.2 DatabaseMetaData.java
--- DatabaseMetaData.java 2000/07/20 15:30:05 1.2
+++ DatabaseMetaData.java 2000/08/31 17:24:03
@@ -1651,7 +1651,7 @@
f[4] = new Field(connection, new String("REMARKS"), iVarcharOid, 32);
// Now form the query
-StringBuffer sql = new StringBuffer("select relname,oid from pg_class where (");
+StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class
+where (");
boolean notFirst=false;
for(int i=0;itypes.length;i++) {
if(notFirst)
@@ -1687,10 +1687,25 @@
remarks = defaultRemarks;
dr.close();
+ String relKind;
+ switch (r.getBytes(3)[0]) {
+ case 'r':
+ relKind = "TABLE";
+ break;
+ case 'i':
+ relKind = "INDEX";
+ break;
+ case 'S':
+ relKind = "SEQUENCE";
+ break;
+ default:
+ relKind = null;
+ }
+
tuple[0] = null;// Catalog name
tuple[1] = null;// Schema name
- tuple[2] = r.getBytes(1); // Table name
- tuple[3] = null;// Table type
+ tuple[2] = r.getBytes(1); // Table name
+ tuple[3] = relKind.getBytes(); // Table type
tuple[4] = remarks; // Remarks
v.addElement(tuple);
}
[GENERAL] JDBC
HI,I'm having a strange problem connecting to postgres7.02 using thejdbc driver. After installing the driver in Jbuilder 3, I'm ableto connect to my database but can ONLY see the postgres system indexesin Jbuilder's JDBC Explorer GUI. I'm unable to see my other databaseobjects (tables, views, etc...) in the JDBC Explorer. Yet thedriver lets me manipulate these unseen objects when I execute sqlstatements against the database. Problem is, I'd like to see all mydatabase objects in the JDBC Explorer GUI, not just the system index.Has anyone experienced this who can suggest a fix? Does anyoneknow if this is due to Jbuilder, the postgresql.Driver, or perhaps abad configuration? Thanks. ENRICO
Re: [GENERAL] Instability in copying large quantities of data
[EMAIL PROTECTED] writes: version. The problem is that the COPY of the data, apparently, sometimes leaves a table in an inconsistent state. The command doesn't throw any error, but when I try to SELECT or VACUUM that table the backend dumps core. Backtrace from core file, please? (Compiling the backend with -g first would improve the usefulness of the trace, but it might tell us something even without.) regards, tom lane
Re: [GENERAL]
thank you very much! But I can tar the old database ,if there is another way to move the datebase , please tell me or please tell me about your way detailed,thank you ! - Original Message - From: He Weiping (Laser Henry) [EMAIL PROTECTED] To: JinMing Qiu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 04, 2000 10:46 AM Subject: Re: [GENERAL] JinMing Qiu wrote: Hello everyone! How to move a postgresql database to another directory? I think the procedure below would work: 1, create the new directory and assign the read write previlege to postgres superuser; 2, shut down all your postmaster using command like (assume you are using bash on linux): postgres@db$ killall -SIGTERM postmaster 3, move the totally data base to the new directory using command like: postgres@db$ tar cvf - /the_old_db_location | tar -C /the_new_db_location xvf - 4, remove the file in old location: (of course, make a backup is a good idea) postgres@db$ rm -r /the_old_db_location 5, make a symbol link to the new location, thus avoid the environment variable changes: postgres@db$ln -s /the_new_db_location /the_old_db_location 6, restart your postmaster: postgres@db$ (something you've used before) that's it
Re: [GENERAL] subselect in CHECK constraint?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 CHECK (testconstraint(a, b)) Uhhh. I get no errors, but it dosen't work, either. Consider: CREATE FUNCTION testconstraint(int,int) RETURNS bool AS ' BEGIN RETURN (select sum(a) FROM test WHERE b = $2) 1000; END; ' LANGUAGE 'plpgsql'; CREATE TABLE test (a int, b int, CHECK (testconstraint(a,b))); INSERT INTO test (a,b) VALUES (1100, 1); SELECT * FROM test; Yielding: a | b - --+--- 1100 | 1 (1 row) which clearly does not satisfy the constraint. Ian -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5s8/6fn9ub9ZE1xoRAuiRAKCHh/wWSl7uYzhJGWnc7kc0OxqZogCgpMCN MdTBSXm7w0C4R4Ghh77+8ok= =nik7 -END PGP SIGNATURE-
RE: [GENERAL] Postgres 7.0.2 and ODBC
Can I use ODBC driver for windows version 6.5 to connect to postgres 7.0.2? Yes, you must (there is no ODBC driver version 6.5 for win, I think)
Re: [GENERAL] subselect in CHECK constraint?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Also, as several other people already pointed out, a constraint involving a select could be violated in many ways including alteration or removal of tuples in other tables. We only evaluate check constraints when we insert/update tuples in the table they are attached to... OK. Is this something that could be accomplished with triggers? :o Also, is it possible to have a foreign key constraint across multiple columns? :o Ian -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5s9xxfn9ub9ZE1xoRAo9WAJ0blihjzEQFo+3clEGRsySjkUzrqgCdEIhe 8VumU6bICMN6jUHCdq0WSYM= =niuY -END PGP SIGNATURE-
Re: [GENERAL] Indexes not working (bug in 7.0.2?)
Tom Lane [EMAIL PROTECTED] writes: Zlatko Calusic [EMAIL PROTECTED] writes: It is now NOT using the index, and I don't understand why? Queries are practically the same, tables are practically the same, why is postgres using indexes in the first case and not in the second? Because it has substantially different ideas about the sizes of the two tables --- notice the different estimated row counts. If you haven't "vacuum analyzed" these tables recently, do so to bring the planner's statistics up-to-date, and then see what you get. You may also care to read the user's manual chapter about EXPLAIN, http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm Yes, thanks to all who helped. 'vacuum analyze' was the magical incantation that helped. I still have one uncertainty. Is it possible that after some time postgres once again decides not to use indices (assuming I haven't run 'vacuum analyze' again)? -- Zlatko
