Re: [GENERAL] Restricting access to rows?
Benjamin Smith [EMAIL PROTECTED] writes: How can I set up a user so that Bob can update his records, without letting Bob update Jane's records? Is it possible, say with a view or some other intermediate data type? It's not hard to give them access to *view* their records using a view. You just create the view with WHERE customer_id = .. and then grant SELECT access to that view but not the underlying table. In theory that would be enough to give them update access as well. However Postgres doesn't yet support updateable views, at least not automatically. You would have to write rules for each view to implement updateable views which isn't hard but would get pretty tiresome if you're doing this for a lot of tables and a lot of clients. There was a project around where someone had implemented some scripts to do this automatically. You might be able to find it searching back through the lists. There are also people interested in working on it as a built-in feature for Postgres, but I don't think there's any time-line on though or even any preliminary results yet, so I wouldn't depend on it any time soon. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Restricting access to rows?
Hi, Are there any plans to make CREATE USER local to a database? (as opposed to CLUSTER scope, as it is today) So that in such cases as Benjamin's, the ISP could satisfy customer requests by createing and handing over the new database instance within the managed cluster? Even with the unrestricted CREATE USER privileges? -R On Fri, 2006-05-26 at 07:39 +0200, Andreas Kretschmer wrote: Benjamin Smith [EMAIL PROTECTED] schrieb: How can I set up a user so that Bob can update his records, without letting Bob update Jane's records? Is it possible, say with a view or some other intermediate data type? You can use a VIEW to select all rows for CURRENT_USER, and then create RULES for this view to do INSERT, UPDATE and DELETE. A nice framework for row-level access-control is 'veil': http://pgfoundry.org/projects/veil HTH, Andreas -- -R ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Best practice to grant all privileges on all bjects in database?
On 5/26/06, Jim Nasby [EMAIL PROTECTED] wrote:
Only helps if the OP is willing to run on HEAD; grant on sequence isnot in 8.1 (at least not according to the docs).you can grant on sequences using syntax for tables. works:([EMAIL PROTECTED]:5810) 08:59:21 [depesz]
# create sequence test;CREATE SEQUENCE([EMAIL PROTECTED]:5810) 08:59:27 [depesz]# \c - depeszYou are now connected as new user depesz.([EMAIL PROTECTED]:5810) 08:59:29 [depesz] select nextval('test');
ERROR: permission denied for sequence test([EMAIL PROTECTED]:5810) 08:59:34 [depesz] \c - pgdbaYou are now connected as new user pgdba.([EMAIL PROTECTED]:5810) 08:59:36 [depesz]# grant select, update on table test to depesz;
GRANT([EMAIL PROTECTED]:5810) 08:59:43 [depesz]# \c - depeszYou are now connected as new user depesz.([EMAIL PROTECTED]:5810) 08:59:46 [depesz] select nextval('test');nextval
- 1(1 row)though i can't find it anywhere in documentation :(depesz-- http://www.depesz.com/ - nowy, lepszy depesz
[GENERAL] latest release of 8.0.8
Title: Re: [GENERAL] postgreslog - panic message where can i download the rpms for the PostgreSQL version 8.0.8 till now we have been using 8.0.0. thanks, regards Surabhi
Vector type (Re: [GENERAL] challenging constraint situation - how do I make it)
Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: I can see some problems there, as both value and range matter; it'd be similar to determining the uniqueness of an area in a rectangle (though 1 dimensional only, of course). Except that what you want is to forbid overlap, not forbid equality. This is not possible with btree-based unique indexes, because btree will not like an opclass whose equality member is not transitive. With what I have in mind, both overlap and equality would violate the unique constraint. I don't quite see why someone'd want to forbid overlap but to allow equality; isn't not allowing equality the whole point of a unique constraint? Yesterday being a holiday, I had some time to look into this. I'm now in the design phase for a (more general) vector type. A timespan type is just a special case for timestamps, the vector type could expand that to integers, reals, points, dates, etc. Anything scalar, basically, with some distinctions in operators depending on the type being discrete or not (discrete types could have adjacency operators, for example). I haven't looked into what types of index would be suitable, I suppose that could cause difficulties. OTOH, if neither overlap nor equality are allowed on a unique index, I'm under the impression that a btree should just work. Knowing that I rarely have time to spend on things like this, is here an appropriate place to discuss design issues or recruiting people for help? If you look in the archives you'll find previous discussion of this point, including some blue-sky speculation about whether GiST indexes could be made to serve the purpose. That's a long way from being a solution you could use today, though. Looks like a good starting place, I should have a look. Any pointers as to what to look for? Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [SQL] (Ab)Using schemas and inheritance
Jorge Godoy wrote: Em Quarta 24 Maio 2006 13:06, Alban Hertroys escreveu: Jorge Godoy wrote: That's not what I showed above. What I meant was: CREATE TABLE base_schema.sample (); CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample; ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2004; CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample; ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2005; ... So I'm always inheriting from the same base and I'm renaming the inherited tables, not the parent table. Did you try this? It seems to be important. I've done what I said, not what you said... Does it look better this way? It does; only 1 level of inheritence for every table, AOT growing inheritence. There are some differences in behaviour too: - Your method shows only data from one year in each table, so you'll need to query a different table to get historical information (probably the base tables, but that also includes data from other companies). - The growing inheritence method would show old data in the newly created tables, unless you use the ONLY operator. Your method will have better performance for the general case (querying current data), I think. Considering this case with views, you could: CREATE INDEX sample_2004_idx ON sample (object_id) WHERE date BETWEEN '2004-01-01'::date AND '2004-12-31'::date; CREATE INDEX sample_2005_idx ON sample (object_id) WHERE date BETWEEN '2005-01-01'::date AND '2005-12-31'::date; etc. You could extend that to also take company_id into account and put the indices on the base tables instead. I figure that takes less maintenance. From what I did to this suggestion or from what you thought I was doing to this suggestion? Neither; That's about the updatable views case. For yearly archiving, dumping one of those inherited tables (containing only data for a specific year, after all) could work. Never tried that before. If I'm removing this from the database, then I can dump it. If I rename it data will still be available for processing in the parent table while new data is inserted in the new table. When the time that I need to keep those records there expire, I can dump the table to some backup and remove it from the database, clearing space and eliminating unneeded tables. Can't you just dump the client and year specific inherited table (client1_schema.sample_2004 for example)? That way you could just use pg_dump, provided it understands inheritence of course. Hmm... thinking about this, I realize that in your case selecting all data in a year could be faster. The planner would certainly choose a sequential scan plan, while with all data in a single table (with views over them) may trigger an index scan, which would probably be slower. Depends on whether you're likely to do that, of course. Probably not often, but if we have benefits here, I believe that those might also appear on small selects. Only if a sequential scan is desirable over an index scan, meaning you're selecting almost all rows in your table. The difference between an index scan over all data and an index scan over year-specific data should be pretty small. Well, the data is inside those tables, where it's not with views. If you accidentaly DROP a column in a table, the data is gone. With a view you just don't see it anymore, but it's still there. Indeed, but with inherited tables I have to DROP ... CASCADE;, what might trigger some advice to whoever is maintaining the database. But you're right on this. But I'm at the same risk dropping some column from the tables the views are derived from... True, but with updatable views you have only one set of tables to take into account, instead of a set for every customer company. You can automate that to some extent, but it is more error prone - it involves human beings, after all ;) One thing that comes to mind now is the timetravel contrib package. That's another thing I plan to look into some time, but it may suit your needs. I'll take a look into it. I confess I don't even know what it does, but I'll check :-) Thanks, Alban! You're welcome. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] foreign key violation
i have 2 tables.
the first table "TAB1", has a primary key "id",
which is the foreign key in the second table "TAB2"
i have 2 stored procedures,
1 stored procedure does
check if there is a row already present in TAB1
with col 2 = $1;
if not present
then
insert into TAB1(col2) values ($1
);
select into "myid" tab1_id from TAB1 where col2 =
$1;
return myid
end if
please note that tab1_id is a serial
bigint.
second stored procedure does
check if there is a row already present in TAB2
with col 2 = $2;
if not present
then
insert into TAB2 (col2, col3) values ($1 ,
$2);
select into "myid" tab2_id from TAB2 where col2 =
$1;
return myid
end if
please note that tab2_id is the seriesl bigint.
also the second argument $2, is the value for the field col3, which is THE
FOREIGN KEY.
in my transaction i do the following
begin
id = call stored procedure 1("abc");
id2 = call stored procedure 2 ("something", id);
commit
however i may have another transaction going in parallel, where i do
this
delete from TAB1 where tab1_id = 5; //5 is just some value
in such a case sometime a foreign key violation occurs when the stored
procedure 2 is called.
Is there any way i can avoid this problem?
thanks,
regards
Surabhi
Re: [GENERAL] latest release of 8.0.8
surabhi.ahuja wrote: where can i download the rpms for the PostgreSQL version 8.0.8 till now we have been using 8.0.0. thanks, regards Surabhi you can from here : http://www.postgresql.org/ftp/binary/v8.0.8/linux/ leonel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] XML Support
Hello, I'm trying to use XML with PG, and I a descriptioin of the XML support for PostgreSQL at http://www.throwingbeans.org/postgresql_and_xml_updated.html As I could not find the mentioned package in my Windows installation (8.1.3) I thought I'd give the package from that website a try even though it says it's for 8.0 I copied all the .dll from the archive to $libdir and after restarting PG I tried to create the necessary functions using the supplied SQL script. But when running the supplied script, I get an error message: ERROR: could not load library D:/Programme/Postgres/lib/libpgxml.dll: The library is definitely available at that location. I tried to copy the .dll files from the suggested bin directory into $libdir as well (to make sure they are found by the postmaster process). Adding bin or lib to the system path does not seem to make a difference either. To be precise: I'm talking about the following files that are part of the archive: bin/iconv.dll bin/libxml2.dll bin/libxslt.dll bin/zlib1.dll lib/libpgxml.dll So I am suspecting that this is a version conflict (which is somewhat expected, as the module is inteded for 8.0 not for 8.1) Does anybody know whether there is an update planned for this module or even built-in XML support in a later version? I tried to contact the author as suggested on the website, but have not received any answer so far. Thanks in advance Thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Incomplete dump?
Tom Lane wrote: =?ISO-8859-1?Q?Benno_P=FCtz?= [EMAIL PROTECTED] writes: When trying to dump a database for upgrading to the current PSQL version using pg_dump I observed the following: Which version of pg_dump were you using, exactly? The process seems to have finished without problems, but the resulting dump file does not end in -- -- PostgreSQL database dump complete -- but rather with a command line (complete, not truncated as might be the case when running out of disk space, which was plenty anyway) Is this an indication of an incomplete dump? If so how could one proceed? I don't remember which version of pg_dump started adding that trailer. If it's an old copy then maybe you're OK. If it should have a trailer and doesn't then you're right to be suspicious. This may well be the reason, The version in question is 7.4.8 (hence my wish to upgrade) while my backup script worked with a newer (8.0) DB and was copied over ... Does anybody know when the trailer was added? Could pg_dump have been operating under a file-size ulimit that stopped it early? regards, tom lane ulimit -areports: core file size(blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files(-n) 1024 pipe size (512 bytes, -p) 8 stack size(kbytes, -s) unlimited cpu time (seconds, -t) unlimited max user processes(-u) 8192 virtual memory(kbytes, -v) unlimited so I don't think this to be the problem. Thanks Benno -- Benno Pütz Statistische Genetik Max-Planck-Institut f. PsychiatrieTel.: +49-89-30622-222 Kraepelinstr. 10 Fax : +49-89-30622-601 80804 München, Germany ---(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] foreign key violation
surabhi.ahuja wrote: however i may have another transaction going in parallel, where i do this delete from TAB1 where tab1_id = 5; //5 is just some value in such a case sometime a foreign key violation occurs when the stored procedure 2 is called. Is there any way i can avoid this problem? It's not a problem, it's what's supposed to happen. You've just deleted the row it's trying to reference. One of the two operations needs to fail. There are two options: 1. Catch the error and retry whichever operation fails (the function-calls in this case). 2. Use locking to ensure the operation isn't disturbed: begin, lock table, call functions, commit This will prevent anyone else from modifying the locked table while your transaction is modifying it. You might be able to get away with less extensive locking in your particular case, but for full details see the chapter on Concurrency Control: http://www.postgresql.org/docs/8.1/static/mvcc.html HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Restricting access to rows?
Greg Stark wrote: There are also people interested in working on it as a built-in feature for Postgres, but I don't think there's any time-line on though or even any preliminary results yet, so I wouldn't depend on it any time soon. Actually, there is a patch which works for some cases. Not sure if it will make it into 8.2 though. -- Richard Huxton Archonet Ltd ---(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] Restricting access to rows?
Benjamin Smith wrote: How can I set up a user so that Bob can update his records, without letting Bob update Jane's records? Is it possible, say with a view or some other intermediate data type? I've done something similar using a separate control table where I set what accounts an user can see, then I wrote a psql that returns just the rows for that especific user, it could also be done with pure SQL joins tough. -- Sinceramente, Josué Maldonado. ... Si me engañas una vez, tuya es la culpa. Si me engañas dos, la culpa es mía. -- Anaxágoras. ---(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
[GENERAL] LDAP authentication
Hello, I saw many messages about ldap authentication but I´m still not sure if PostgreSQL can use it? Can I use it? Should I apply some patch before? Where can I find some doc about? Reimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Vector type (Re: [GENERAL] challenging constraint situation - how do I make it)
Alban Hertroys [EMAIL PROTECTED] writes: Tom Lane wrote: Except that what you want is to forbid overlap, not forbid equality. This is not possible with btree-based unique indexes, because btree will not like an opclass whose equality member is not transitive. With what I have in mind, both overlap and equality would violate the unique constraint. I don't quite see why someone'd want to forbid overlap but to allow equality; isn't not allowing equality the whole point of a unique constraint? You're missing the point. Letting ~ represent the operator that tests for interval-overlap, we can have A -- B -- C so that A ~ B and B ~ C but not A ~ C. This is too much unlike normal equality for a btree to work with ~ as the equality operator. regards, tom lane ---(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] Incomplete dump?
=?ISO-8859-1?Q?Benno_P=FCtz?= [EMAIL PROTECTED] writes: Does anybody know when the trailer was added? Try dumping an empty database (eg template1) with that same copy of pg_dump and see what it does. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] SELECT table_type FROM table;
By accident, a colleague came across something unexpected. Here is a simple example: create table testo(gonk integer,spoodle text); CREATE TABLE agentm=# insert into testo values(1,'hello'); INSERT 0 1 agentm=# insert into testo values(2,'text'); INSERT 0 1 agentm=# select testo from testo; testo --- (1,hello) (2,text) (2 rows) Obviously, this is intentional behavior but where is it documented? I am aware that testo is also a type and that a set is returned for each row but this must be a special case, no? Alternate types don't seem to apply. agentm=# create type nice as (gonk integer,spoodle text); CREATE TYPE agentm=# select nice from testo; ERROR: column nice does not exist agentm=# select *::nice from testo; ERROR: syntax error at or near :: at character 9 LINE 1: select *::nice from testo; ^ agentm=# select cast(* as nice) from testo; ERROR: syntax error at or near * at character 13 LINE 1: select cast(* as nice) from testo; ^ Also, how can I turn each set row into an array? -M ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LDAP authentication
[EMAIL PROTECTED] wrote: Hello, I saw many messages about ldap authentication but I´m still not sure if PostgreSQL can use it? Are you using windows or unix? On unix, postgresql can use pam (pluggable authentication modules) for authentication. There are pam modules for about any authentication schema you can image, including ldap. Can I use it? Should I apply some patch before? Where can I find some doc about? Just compile a version of postgres that includes pam support (Most binary packages already include that), add a line containing all all 0.0.0.0 0.0.0.0 pam to pg_hba.conf, and create a /etc/pam.d/postgres file. greetings, Florian Pflug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Restricting access to rows?
Josue E. Maldonado wrote: Benjamin Smith wrote: How can I set up a user so that Bob can update his records, without letting Bob update Jane's records? Is it possible, say with a view or some other intermediate data type? I've done something similar using a separate control table where I set what accounts an user can see, then I wrote a psql that returns just the rows for that especific user, it could also be done with pure SQL joins tough. You can put in a some triggers that do a few things, and I think a rule on SELECT will round it off. on Insert: populate a column with CURRENT_USER on Update and Delete: refuse unless CURRENT_USER matches the column on SELECT rules, apply a filter that column = CURRENT_USER You also may put in an override for all three that if the CURRENT_USER is in some particular group these filters will not apply. One level might be just for selects, a higher level for updates/deletes. Or you can do the reverse, and say that these filters only apply if the user is in a certain group. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Incomplete dump?
am 26.05.2006, um 14:33:37 +0200 mailte Benno Pütz folgendes: I don't remember which version of pg_dump started adding that trailer. If it's an old copy then maybe you're OK. If it should have a trailer and doesn't then you're right to be suspicious. This may well be the reason, The version in question is 7.4.8 (hence my wish to upgrade) while my backup script worked with a newer (8.0) DB and was copied over ... In this case, you should better use the pg_dump from the 8.0-Version. Btw.: 8.0 is 'out of date', we have 8.1.4... Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] delete is extremely slow
Hello, Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] delete is extremely slow
Emi Lu [EMAIL PROTECTED] wrote Hello, Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? Regards, Qingqing ---(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] 8.1.4: Who says PHP deprecated addslashes since 4.0?
ljb wrote: [EMAIL PROTECTED] wrote: ljb [EMAIL PROTECTED] writes: | addslashes() or magic_quotes. We note that these tools have been deprecated | by the PHP group since version 4.0. Can anyone provide a source for the statement? I'm not going to put words in Josh's mouth about where he got that from, but anyone who reads all of the comments at http://us3.php.net/manual/en/function.addslashes.php ought to come away suitably unimpressed with the security of that function. Yes, sorry, I did see those comments, although I don't think they are from the PHP group themselves. But I missed the statement on the pg_escape_string manual page saying use of this function is recommended instead of addslashes(). I still think since version 4.0 is wrong. Better yet, use PEAR::DB or some other db abstraction package that will handle all of this for you. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] make check fails over NFS or tmpfs
On Wed, May 24, 2006 at 12:16:13AM +0200, Florian G. Pflug wrote: BSD signal semantics (what postgres uses) make all system calls restart across signals. Thus, a system call can never return EINTR unless you have non-blocking I/O enabled. These programs would be confused by unexpected EINTRs. AFAIK, linux actually abort syscalls when an signal arrives, and it's just the libc that restarts them automatically. So, actually, doing All UNIX OS's do something similar. After all, if you define a signal handler, the kernel has to return to user space to execute your handler. All BSD did was always restart the syscall (your loop, though probably just by fiddling the instruction pointer)) whereas SysV never did. Nowadays you can choose which way you want it using sigaction(). I think the real lesson is that you can emulate BSD semantics if you have SysV semantics, but not vice-versa. Postgres doesn't check EINTR on all filesystem system call and thus would be susceptable to the above problem. Even if postgres checked for EINTR, what could it possibly do in that case? Just retrying wont have any advantage over simply mounting with nointr - it would still just hang when the nfs-server dies. Well, it could check whether statement_tineout has passed and return an error rather than hanging... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: Vector type (Re: [GENERAL] challenging constraint situation -
Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: With what I have in mind, both overlap and equality would violate the unique constraint. I don't quite see why someone'd want to forbid overlap but to allow equality; isn't not allowing equality the whole point of a unique constraint? You're missing the point. Letting ~ represent the operator that tests for interval-overlap, we can have A -- B -- I'd say unique constraint violation right here (provided there's a unique constraint on this column, of course). The order in which these are inserted/updated doesn't seem to matter either. I'm afraid I'm still missing the point... or maybe I'm not wrong??? C so that A ~ B and B ~ C but not A ~ C. This is too much unlike normal equality for a btree to work with ~ as the equality operator. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Vector type (Re: [GENERAL] challenging constraint situation - how do I make it)
Alban Hertroys [EMAIL PROTECTED] writes: I'd say unique constraint violation right here (provided there's a unique constraint on this column, of course). The order in which these are inserted/updated doesn't seem to matter either. I'm afraid I'm still missing the point... The point is that btree depends on a number of assumptions about the behavior of the operators in an operator class, and one of those assumptions is that equality is transitive. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Restricting access to rows?
Rafal Pietrak [EMAIL PROTECTED] writes: Are there any plans to make CREATE USER local to a database? No. There is the db_user_namespace configuration parameter, but it's a bit of an ugly kluge if you ask me ... regards, tom lane ---(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] delete is extremely slow
Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? No, I do not have indexes setup for those dependents. If I setup index for those columns, it will speed up the deletion, right? Thank you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.1.4: Who says PHP deprecated addslashes since
On Fri, 2006-05-26 at 08:58, Erik Jones wrote: ljb wrote: [EMAIL PROTECTED] wrote: ljb [EMAIL PROTECTED] writes: | addslashes() or magic_quotes. We note that these tools have been deprecated | by the PHP group since version 4.0. Can anyone provide a source for the statement? I'm not going to put words in Josh's mouth about where he got that from, but anyone who reads all of the comments at http://us3.php.net/manual/en/function.addslashes.php ought to come away suitably unimpressed with the security of that function. Yes, sorry, I did see those comments, although I don't think they are from the PHP group themselves. But I missed the statement on the pg_escape_string manual page saying use of this function is recommended instead of addslashes(). I still think since version 4.0 is wrong. Better yet, use PEAR::DB or some other db abstraction package that will handle all of this for you. Or, if you're going to use the native pgsql interface, you can always use prepared queries. http://www.php.net/manual/en/function.pg-prepare.php Actually, other than still not having error numbers (just the error messages, seems like priority inversion to me, btw) the pgsql interface in php is quite robust. You can even run async queries with it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
RES: [GENERAL] LDAP authentication
Are you using windows or unix? On unix, postgresql can use pam We are using a mix of windows and unix+linux and I think PAM can not be used with windows. Am I right? Is there another way? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] delete is extremely slow
Emi Lu wrote: Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? No, I do not have indexes setup for those dependents. If I setup index for those columns, it will speed up the deletion, right? Thank you. http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php If deletioin is 80% data, will index helps? Emi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How to SHOW custom variable classes?
How do you do SHOW plperl.* or whatever technique allows you to find out all custom parameters defined for a particular class? i.e. class.parameter -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] [SQL] (Ab)Using schemas and inheritance
to all: maybe I can be of some help here. I think schemas are definately the way to go. I've used them quite a bit, previously with an accounting application which used schemas to separate company. Right now I am in development of a major project which will use them even more extensively, following an evalution of other methods of isloating data for scalability reasons. The mammoth table approach (company id in every table) is very clean and the best approach from a academic perspective but unfortunately you can run into scalability problems if your tables are very large. An ideal SQL database would automagically do everything for you without having to worry about it (no such database exists AFAIK). The major downsides of using schemas in an application can be mitigated...for example clever use of dynamic sql functions and scripts can go a long way in helping with updates that have to run across mutiple schemas. The benifits of schemas are pretty nice, you get the performance advantages of partitioning and you can manipulate the search path for easy switching...just make sure to force a reconnect to get all those stored procedures to relearn all the tables they use if you keep your functions in a shared area (usually a good idea). Table partitioning and inheritance look good on the surface but there are a number of 'gotchas' that can hold you back from using them effectively. Foreign keys are a mess, and compelex queries can be problematic...the new constraint exclusion feature helps, but there is a way to go IMO. by the way, the \d query in psql maps to a fairly complex sql statement, you can beat it by going right to the pg catalogs if performance is an issue, or materialize your catalogs to an indexed table. If you have a lot of tables (1000s) you will definately want to prepare everything, meaning use views, procedures, and prepared statements liberally. I would also take careful note of Jim's FSM comments and keep a careful eye on that. Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] delete is extremely slow
On Fri, 2006-05-26 at 09:51, Emi Lu wrote: Emi Lu wrote: Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? No, I do not have indexes setup for those dependents. If I setup index for those columns, it will speed up the deletion, right? Thank you. http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php If deletioin is 80% data, will index helps? Yep. If your master table has 10,000 rows, and the dependent table has 100,000 rows, and you're deleting half of the master table (5,000 rows) then you'll have 5,000 sequential scans of the 100,000 row dependent table. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] delete is extremely slow
Scott Marlowe wrote: On Fri, 2006-05-26 at 09:51, Emi Lu wrote: Emi Lu wrote: Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? No, I do not have indexes setup for those dependents. If I setup index for those columns, it will speed up the deletion, right? Thank you. http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php If deletioin is 80% data, will index helps? Yep. If your master table has 10,000 rows, and the dependent table has 100,000 rows, and you're deleting half of the master table (5,000 rows) then you'll have 5,000 sequential scans of the 100,000 row dependent table. What about the reverse, 90,000 in the master table , while 500 records in each dependant tables? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] delete is extremely slow
On Fri, 2006-05-26 at 09:57, Emi Lu wrote: Scott Marlowe wrote: On Fri, 2006-05-26 at 09:51, Emi Lu wrote: Emi Lu wrote: Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? No, I do not have indexes setup for those dependents. If I setup index for those columns, it will speed up the deletion, right? Thank you. http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php If deletioin is 80% data, will index helps? Yep. If your master table has 10,000 rows, and the dependent table has 100,000 rows, and you're deleting half of the master table (5,000 rows) then you'll have 5,000 sequential scans of the 100,000 row dependent table. What about the reverse, 90,000 in the master table , while 500 records in each dependant tables? basically, for every row you delete in the master table, you'll have to sequentially scan every row in the dependent table(s) for each, if you don't have an index on the dependent table. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT table_type FROM table;
A.M. [EMAIL PROTECTED] writes: agentm=# select testo from testo; testo --- (1,hello) (2,text) (2 rows) Obviously, this is intentional behavior but where is it documented? Well, it's mentioned in passing in section 32.4.2 SQL Functions on Composite Types, http://www.postgresql.org/docs/8.1/static/xfunc-sql.html#AEN31648 where it says The table row can alternatively be referenced using just the table name. Personally I prefer the syntax table.*; the syntax without * is a holdover from PostQUEL IIRC. agentm=# select *::nice from testo; ERROR: syntax error at or near :: at character 9 The syntactically right thing would be regression=# select testo::nice from testo; ERROR: cannot cast type testo to nice or regression=# select (testo.*)::nice from testo; ERROR: cannot cast type testo to nice We don't have any automatic support for casts from one composite type to another, but you can add your own: regression=# create function nice(testo) returns nice language sql as $$ regression$# select $1.* $$ strict immutable; CREATE FUNCTION regression=# create cast(testo as nice) with function nice(testo); CREATE CAST regression=# select (testo.*)::nice from testo; testo --- (1,hello) (2,text) (2 rows) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: RES: [GENERAL] LDAP authentication
[EMAIL PROTECTED] wrote: Are you using windows or unix? On unix, postgresql can use pam We are using a mix of windows and unix+linux and I think PAM can not be used with windows. Am I right? Is there another way? Not that I would know... But try googling around for pam and windows. Theres are rather large change the someone already ported pam. I guess compiling pam using cygwin would be quite straight-forward - but since AFAIK postgres on win32 doesn't use cygwin, you'd have to figure out if a non-cygwin app can link to cygwin-based libs. greetings, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to SHOW custom variable classes?
Simon Riggs [EMAIL PROTECTED] writes: How do you do SHOW plperl.* or whatever technique allows you to find out all custom parameters defined for a particular class? Maybe select from pg_settings where name like 'plperl.%' regards, tom lane ---(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] LDAP authentication
May I suggest you take a look at www.openldap.org and the following: http://www.samse.fr/GPL/ldap_pg/HOWTO/ [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello, I saw many messages about ldap authentication but I´m still not sure if PostgreSQL can use it? Can I use it? Should I apply some patch before? Where can I find some doc about? Reimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] Restricting access to rows?
On Fri, 2006-05-26 at 10:25 -0400, Tom Lane wrote: There is the db_user_namespace configuration parameter, but it's a bit of an ugly kluge if you ask me ... Haven't noticed that. But a [EMAIL PROTECTED], still can create a [EMAIL PROTECTED] - so it's of no use for privilege separation. Pity. -- -R ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Vector type (Re: [GENERAL] challenging constraint situation -
Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: I'd say unique constraint violation right here (provided there's a unique constraint on this column, of course). The order in which these are inserted/updated doesn't seem to matter either. I'm afraid I'm still missing the point... The point is that btree depends on a number of assumptions about the behavior of the operators in an operator class, and one of those assumptions is that equality is transitive. So, referring back to your example you mean that though B overlaps A, and C overlaps B, C doesn't overlap A and thus they're not transitive. I see. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] LDAP authentication
On Fri, 2006-05-26 at 10:55, codeWarrior wrote: May I suggest you take a look at www.openldap.org and the following: http://www.samse.fr/GPL/ldap_pg/HOWTO/ Note that that's more about backing LDAP with PostgreSQL, not the other way around. still a good faq though ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Restricting access to rows?
You are apparently dealing with the downside of co-mingling your clients
data... maybe you should seriously consider revising your approach and
giving each client either separate databases or separate schema's within a
given database --
This is why co-mingling should be avoided...
I'd push for the former -- that way -- you can use a template database
instead of hoping that all of your filters, rules, views, etc are
accurate...
Benjamin Smith [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
We have a growing ASP-hosted application built on PHP/Postgres 8.1, and
are
getting requests from clients to manipulate the databases more directly.
However, the structure of our databases prevents this from happening
readily.
Assume I have two tables configured thusly:
create table customers (
id serial unique not null,
name varchar not null
);
create table widgets (
customers_id integer not null references customers(id),
name varchar not null,
value real not null default 0
);
insert into customers (name) values ('Bob');
insert into customers (name) values ('Jane');
insert into widgets (customers_id, name, value) VALUES (1, 'Foo', 100);
insert into widgets (customers_id, name, value) VALUES (1, 'Bar', 50);
insert into widgets (customers_id, name, value) VALUES (2, 'Bleeb', 500);
This leaves us with two customers, Bob who has two widgets worth $150, and
Jane with one widget worth $500.
How can I set up a user so that Bob can update his records, without
letting
Bob update Jane's records? Is it possible, say with a view or some other
intermediate data type?
Thanks,
-Ben
--
The best way to predict the future is to invent it.
- XEROX PARC slogan, circa 1978
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: RES: [GENERAL] LDAP authentication
On Fri, 2006-05-26 at 17:38 +0200, Florian G. Pflug wrote: [EMAIL PROTECTED] wrote: Are you using windows or unix? On unix, postgresql can use pam We are using a mix of windows and unix+linux and I think PAM can not be used with windows. Am I right? Is there another way? Not that I would know... I think there is pam-smb, that comes with SAMBA, and can authenticate agains NT-domain controller (or samba acting as one). SAMBA in turn can be configured to authenticate against LDAP. -- -R ---(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
[GENERAL] Syntax question
I have a select statement that goes as follows: SELECT * FROM product prod, prod_alias pa, category cat, company co WHERE prod.catid = cat.catid AND prod.coid = co.coid AND prod.prodid = pa.prodid; If possible, I want to change the statement so that I get output regardless of whether there's a match between prod.prodid and pa.prodid. IOW, if there's a match between prod.prodid and pa.prodid, I want the output from both the product table and the prod_alias table. if there's no match, I still want the output from product table. Can I do this in one select statement? Thanks, Don Ring'em or ping'em. Make PC-to-phone calls as low as 1¢/min with Yahoo! Messenger with Voice.
Re: [GENERAL] LDAP authentication
Em Sexta 26 Maio 2006 12:55, codeWarrior escreveu: May I suggest you take a look at www.openldap.org and the following: http://www.samse.fr/GPL/ldap_pg/HOWTO/ This looks like the opposite: the HOWTO above is for LDAP using PostgreSQL as backend not PostgreSQL using LDAP to authenticate. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Syntax question
On Fri, May 26, 2006 at 09:09:25 -0700, John Taylor [EMAIL PROTECTED] wrote: I have a select statement that goes as follows: SELECT * FROM product prod, prod_alias pa, category cat, company co WHERE prod.catid = cat.catid AND prod.coid = co.coid AND prod.prodid = pa.prodid; If possible, I want to change the statement so that I get output regardless of whether there's a match between prod.prodid and pa.prodid. IOW, if there's a match between prod.prodid and pa.prodid, I want the output from both the product table and the prod_alias table. if there's no match, I still want the output from product table. Can I do this in one select statement? This is what outer joins are used for. See http://developer.postgresql.org/docs/postgres/sql-select.html for the syntax and a very brief description of what they do. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Syntax question
am 26.05.2006, um 9:09:25 -0700 mailte John Taylor folgendes: I have a select statement that goes as follows: SELECT * FROM product prod, prod_alias pa, category cat, company co WHERE prod.catid = cat.catid AND prod.coid = co.coid AND prod.prodid = pa.prodid; If possible, I want to change the statement so that I get output regardless of whether there's a match between prod.prodid and pa.prodid. IOW, if there's a match between prod.prodid and pa.prodid, I want the output from both the product table and the prod_alias table. if there's no match, I still want the output from product table. Can I do this in one select statement? I think, you should read about 'left outer join'. http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-FROM HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Syntax question
On Fri, May 26, 2006 at 09:09:25AM -0700, John Taylor wrote: I have a select statement that goes as follows: SELECT * FROM product prod, prod_alias pa, category cat, company co WHERE prod.catid = cat.catid AND prod.coid = co.coid AND prod.prodid = pa.prodid; If possible, I want to change the statement so that I get output regardless of whether there's a match between prod.prodid and pa.prodid. IOW, if there's a match between prod.prodid and pa.prodid, I want the output from both the product table and the prod_alias table. if there's no match, I still want the output from product table. Can I do this in one select statement? Yes :) First, rewrite your query to make the JOINs explicit like this: /* Rewritten query #1 (Q1) */ SELECT prod.*, pa.*, cat.*, co.* -- *Slightly* better than * :P FROM product prod JOIN prod_alias pa ON (prod.prodid = pa.prodid) JOIN category cat ON (prod.catid = cat.catid) JOIN company co ON (prod.coid = co.coid) Then look up LEFT JOIN in the OUTER JOIN part of the manual others have pointed you to. What you'll come out with is something like this: /* Rewritten query #2 (Q2) */ SELECT prod.*, pa.*, cat.*, co.* -- *Slightly* better than * :P FROM product prod LEFT JOIN prod_alias pa ON (prod.prodid = pa.prodid) JOIN category cat ON (prod.catid = cat.catid) JOIN company co ON (prod.coid = co.coid) When you compart Q1 and Q2 side by side, you'll see that the only difference between Q1 and Q2 above is the word LEFT. HTH :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pg_dump on 7.4
Hi, We have a running postgresql 7.4 on our server (SuSE Linux 9), and at the moment there is no chance to upgrade. Through an unusual combination of constraints (spanning over schemas) the dump cannot be imported without errors anymore. Are there any statically compiled 8.x binaries (with which it works) available which I could use to backup the database? Thanks Jürgen -- SAG Energieversorgungslösungen GmbH Center für GeoInformationsTechnologie CeGIT Westfalendamm 100 44141 Dortmund E mailto:[EMAIL PROTECTED] I http://www.cegit.com http://www.sag-el.com T +49-231-725488-44 (Büro CeGIT) T +49-231-913003-20 (Büro WGI) F +49-231-725488-13 M +49-173-2745926 ---(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
[GENERAL] LDAP authentication
Hello, I saw many messages about ldap authentication but I´m still not sure if PostgreSQL can use it? Can I use it or should I apply some patch before? Reimer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] meaning of PQresultStatus types
Thanks for your reply, FATAL is probably a bad choice of words here; it basically means any error condition reported by the server that caused execution of your query to be abandoned. NONFATAL_ERROR actually means notice or warning message, and isn't a possible return code from PQexec anyway. Just for anyone else wondering about this in the future, I found that PGRES_FATAL_ERROR can be a simple situation such as your query having a syntax error, or as serious as an outright database shutdown (i.e. connection lost). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Temp Tables
When using a temp table in plpgsql functions that has columns comprised from many tables populated by joins, how do you specify a temp table return type? I get an error when I specify returns setof temp_table. Also when I specify a permanent table as the return type, I get extra blank columns in the result set which are the additional columns of the permanent table I don't need and the data is displayed in the wrong columns. I tried defining composite types, but get a runtime error that it isn't available. Any help would be greatly appreciated. Thanks, Brandon ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Insert into partition table hangs
I thought that since application continues running fine after I restart it, I would try creating a separate connection for each insert statement, and then disconnect when it's completed. However, this had the same problem. It would connect, insert, and disconnect several hundred times, and then at random point it would connect, and insert would get stuck again. I am running out of ideas on how to fix this issue. I had a suggestion to go back to non-partitioned tables with partitioned index, but I would prefer not to do this. Is there anything else I can do to fix this problem? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Making query without trigger
I have trigger on updating the table. Sometimes i need to make queries without calling that trigger. How can I solve this? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump on 7.4
Rose, Juergen [EMAIL PROTECTED] writes: We have a running postgresql 7.4 on our server (SuSE Linux 9), and at the moment there is no chance to upgrade. Through an unusual combination of constraints (spanning over schemas) the dump cannot be imported without errors anymore. Try using an 8.0 or 8.1 pg_dump with the 7.4 server. 7.4's pg_dump is not very bright about dumping things in the right order. If it still doesn't work with a modern pg_dump, please show details. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] move from 1 database to another
Thanks.it works well.Ragnar [EMAIL PROTECTED] wrote: On fim, 2006-05-25 at 11:52 -0700, P.M wrote: I would like to know how can i move from 1 database to another one ? basically i want to run a *.sql script in which i connect under a particular database (for example : template1 or postgres) and my *.sql script should create a new database (for example : test) and after execute several SQL command under test database.I guess you are asking how to connect to a new databasein the middleof a sql scriptAssuming the script will be run by psql, you can use\connect testgnari---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.
Re: [GENERAL] Making query without trigger
On Fri, May 26, 2006 at 12:02:44PM +0500, [EMAIL PROTECTED] wrote: I have trigger on updating the table. Sometimes i need to make queries without calling that trigger. How can I solve this? You need your function to decide whether the don't do anything conditions apply, and then not do anything. The trigger still fires, but it doesn't do anything. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(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
[GENERAL] convert row() to array
Is there a function to convert a row record into an array (discarding column info)? -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] drivers bugs prior 8.1.4
Hello : If the driver for postgresql hasn't been patched for the past sqlinjection bug as I understood the postgresql 8.1.4 engine will return an error so no sqlinjection can be done ? thanks Leonel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] XML Support
On Fri, 26 May 2006 13:25:34 +0200, Thomas Kellerer wrote: Hello, I'm trying to use XML with PG, and I a descriptioin of the XML support for PostgreSQL at http://www.throwingbeans.org/postgresql_and_xml_updated.html As I could not find the mentioned package in my Windows installation (8.1.3) I thought I'd give the package from that website a try even though it says it's for 8.0 It may not be built by default. The source for the package is part of the main distribution, but assuming you are using the installer for the Windows version, that is of limited use to you. I can't claim to have great Windows compilation skills, but another contributor posted a zipped dll some time ago: http://archives.postgresql.org/pgsql-novice/2005-11/msg00216.php That may help you out. I copied all the .dll from the archive to $libdir and after restarting PG I tried to create the necessary functions using the supplied SQL script. But when running the supplied script, I get an error message: ERROR: could not load library D:/Programme/Postgres/lib/libpgxml.dll: The problem is not likely to be an inability to find the file, it may be that symbols in it remain unreferenced i.e. it's either not compatible with the main postgres executable or with libxml2. Does anybody know whether there is an update planned for this module or even built-in XML support in a later version? The only issue is a Win32 binary version of it - the module itself is intended to (and I believe does!) work with 8.1. Have a look at the referenced link and see if that helps you - if you still have problems, post again and I'll think harder! Regards John (original developer of contrib/xml2 but now largely bereft of time and glad to see XML in the SoC proposals!) ---(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] XML Support
John Gray wrote on 26.05.2006 22:01: I can't claim to have great Windows compilation skills, but another contributor posted a zipped dll some time ago: http://archives.postgresql.org/pgsql-novice/2005-11/msg00216.php That may help you out. Thanks for the pointer, but I can't seem to get that installed as well. Best regards Thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Invoke diff from plpgsql?
Wondering how to invoke a application like diff from plpgsql? Thanks! Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
Re: [GENERAL] Invoke diff from plpgsql?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Wondering how to invoke a application like diff from plpgsql? Thanks! Pl/Pgsql cannot execute system commands. For that, you will need one of the other Pl languages, running in untrusted mode. Here are some from the manual: http://www.postgresql.org/docs/current/static/pltcl.html http://www.postgresql.org/docs/current/static/plperl.html http://www.postgresql.org/docs/current/static/plpython.html - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200605262300 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEd8DMvJuQZxSWSsgRAo5qAJwNa+Nvxy8b094l1pYEyUVKB89vHwCgywXO 7mpUYWPeQ0q8qXpM/f/8g2s= =0Evq -END PGP SIGNATURE- ---(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] Invoke diff from plpgsql?
Matthew Peter wrote: Wondering how to invoke a application like diff from plpgsql? Thanks! You can't. But you couuld from plPerl or plPython Blab-away for as little as 1¢/min. Make PC-to-Phone Calls http://us.rd.yahoo.com/mail_us/taglines/postman2/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com using Yahoo! Messenger with Voice. -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
