Re: [GENERAL] [Auth] ident method and LDAP user accounts
On Wed, Mar 02, 2005 at 02:16:29PM -0500, Tom Lane [EMAIL PROTECTED] wrote a message of 8 lines which said: It is of course very inconvenient to duplicate my LDAP database into pg_ident.conf. Is there a better way? Perhaps you can find a PAM plugin that talks to LDAP, and configure Postgres to use that. It already exists (I tried it) but it does not suit my needs since it asks for the password (and I'm already logged in, I don't want to type a password again). The real issue is Why PostgreSQL does not use getpwuid when getsockopt with SO_PEERCREED returns a numeric UID? This would give to PostgreSQL the real and correct user name :-( ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [Auth] ident method and LDAP user accounts
Stephane Bortzmeyer wrote: All the user accounts, including mine, are in a LDAP database. Thanks to NSS (Name Service Switch) all applications have access to the LDAP accounts (getpwuid(3) and getpwnam(3) use LDAP). But not PostgreSQL. I did similar setups and both gentoo and debian/sarge, and this was never a problem. Might it be that the postgres user is not allowed to read /etc/ldap.conf - or however your nss_ldap config file is called? I'd try su-ing to the postgres user, and check if everything (ls -l /home, ... - you get the idea) works as expected. When I connect locally (Linux as SO_PEERCRED so the ident daemon is not used) with the ident method, I get rejected. If I create an ident map to map the numeric UID to my name, it works: # MAPNAME IDENT-USERNAMEPG-USERNAME ldapuser 1000 bortzmeyer If all else fails, you could create this via a shellscript from your ldap database - but of course thats ugly... greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] [Auth] ident method and LDAP user accounts
Stephane Bortzmeyer [EMAIL PROTECTED] writes:
The real issue is Why PostgreSQL does not use getpwuid when
getsockopt with SO_PEERCREED returns a numeric UID?
Oh? I read in hba.c
if (getsockopt(sock, SOL_SOCKET, SO_PEERCRED, peercred, so_len) != 0 ||
so_len != sizeof(peercred))
{
/* We didn't get a valid credentials struct. */
ereport(LOG,
(errcode_for_socket_access(),
errmsg(could not get peer credentials: %m)));
return false;
}
pass = getpwuid(peercred.uid);
so it sure looks like we *are* using getpwuid.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] [Auth] ident method and LDAP user accounts
On Thu, Mar 03, 2005 at 10:04:32AM +0100, Florian G. Pflug [EMAIL PROTECTED] wrote a message of 114 lines which said: Might it be that the postgres user is not allowed to read /etc/ldap.conf - or however your nss_ldap config file is called? myriam:~ % ls -ld /etc/*ldap* drwxr-xr-x 2 root root 4096 Oct 18 17:17 /etc/ldap -rw--- 1 root root 13 Oct 18 17:19 /etc/ldap.secret -rw-r--r-- 1 root root 8442 Oct 18 17:27 /etc/libnss-ldap.conf -rw-r--r-- 1 root root 7070 Oct 18 17:19 /etc/pam_ldap.conf I'd try su-ing to the postgres user, and check if everything (ls -l /home, ... - you get the idea) works as expected. It does: myriam:~ % id uid=104(postgres) gid=108(postgres) groups=108(postgres) myriam:~ % ls -l /home/bortzmeyer total 68 drwxr-sr-x 3 bortzmeyer staff 4096 Nov 19 11:47 AFGNIC While bortzmeyer is not on /etc/passwd, only in LDAP. So, we still have a mystery :-( ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [Auth] ident method and LDAP user accounts
On Thu, Mar 03, 2005 at 04:03:25AM -0500, Tom Lane [EMAIL PROTECTED] wrote a message of 21 lines which said: pass = getpwuid(peercred.uid); so it sure looks like we *are* using getpwuid. You're right but I do not understand why it fails only with PostgreSQL. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [Auth] ident method and LDAP user accounts
On Thu, 3 Mar 2005, Stephane Bortzmeyer wrote: On Thu, Mar 03, 2005 at 10:04:32AM +0100, Florian G. Pflug [EMAIL PROTECTED] wrote a message of 114 lines which said: Might it be that the postgres user is not allowed to read /etc/ldap.conf - or however your nss_ldap config file is called? myriam:~ % ls -ld /etc/*ldap* drwxr-xr-x 2 root root 4096 Oct 18 17:17 /etc/ldap -rw--- 1 root root 13 Oct 18 17:19 /etc/ldap.secret -rw-r--r-- 1 root root 8442 Oct 18 17:27 /etc/libnss-ldap.conf -rw-r--r-- 1 root root 7070 Oct 18 17:19 /etc/pam_ldap.conf I'd try su-ing to the postgres user, and check if everything (ls -l /home, ... - you get the idea) works as expected. It does: myriam:~ % id uid=104(postgres) gid=108(postgres) groups=108(postgres) myriam:~ % ls -l /home/bortzmeyer total 68 drwxr-sr-x 3 bortzmeyer staff 4096 Nov 19 11:47 AFGNIC While bortzmeyer is not on /etc/passwd, only in LDAP. So, we still have a mystery :-( Does Debian include and activate SELinux? .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Compatible Dumps
Is there a way to make a postgresql 8 database dump work with 7.4? I.E I want to do a dump from 8.0 on one machine and put it into a database running on 7.4 on another? Thanks Jake ---(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] Index size
All you said are wright. But it 's not so difficult for postgresql to hold on a bit attribute attached to each table the information, whether there is done an insertion/deletion/update to a clustered table or not. And i guess, postgresql would already implement this simply alternative. Easy, if you CLUSTER a table, it's CLUSTERed then. But it doesn't stay that way. As soon as you insert a new row, or update an old one, it gets added to the end (the only place with space) and now it's not clustered anymore. It's almost clustered and from a caching point of view it's fine. But postgresql can't assume at any point a table will stay clustered, an insert could happen in the middle of your processing. Logically you can't magically add space in the middle of a file, you have to move everything else up. If you know an efficient way to keep a table clustered while handling arbitrary inserts and updates, I'd be curious to know... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Compatible Dumps
Jake Stride wrote: Is there a way to make a postgresql 8 database dump work with 7.4? I.E I want to do a dump from 8.0 on one machine and put it into a database running on 7.4 on another? Haven't tried this yet, but apart from turning off dollar-quoting for functions, it should just work. If you want to test it, try a schema-only dump+restore first. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [Auth] 'ident' method and LDAP user accounts
On Thu, March 3, 2005 12:00, Marco Colombo said: On Thu, 3 Mar 2005, Stephane Bortzmeyer wrote: On Thu, Mar 03, 2005 at 10:04:32AM +0100, Florian G. Pflug [EMAIL PROTECTED] wrote a message of 114 lines which said: Might it be that the postgres user is not allowed to read /etc/ldap.conf - or however your nss_ldap config file is called? myriam:~ % ls -ld /etc/*ldap* drwxr-xr-x 2 root root 4096 Oct 18 17:17 /etc/ldap -rw--- 1 root root 13 Oct 18 17:19 /etc/ldap.secret -rw-r--r-- 1 root root 8442 Oct 18 17:27 /etc/libnss-ldap.conf -rw-r--r-- 1 root root 7070 Oct 18 17:19 /etc/pam_ldap.conf I'd try su-ing to the postgres user, and check if everything (ls -l /home, ... - you get the idea) works as expected. It does: myriam:~ % id uid=104(postgres) gid=108(postgres) groups=108(postgres) myriam:~ % ls -l /home/bortzmeyer total 68 drwxr-sr-x 3 bortzmeyer staff 4096 Nov 19 11:47 AFGNIC While bortzmeyer is not on /etc/passwd, only in LDAP. So, we still have a mystery :-( Seems so.. you could try to start the postmaster via strace -f, and capture the log (strace -f postmaster -- postmaster-opts 21 /tmp/postmaster.strace) Then try to connect, and see what happens - you should see the postmaster open your pam_ldap.conf, and then try to connect to your ldap server. Maybe you find some hint in the strace log on whats going on... Maybe it's also worth trying to start the postmaster by hand - the init-script might set some different env-variables or paths than what you have set in an interactive shell... Does Debian include and activate SELinux? There are selinux-versions of debian, but in vanilla debian/sarge (and debian/woody, and debian/sid), there is no selinux support - at least, I never stumbled upon this, and I use quite a few debian machines). greetings, Florian Pflug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Postgresql driver
I am trying to develop an application to access postgresql DB using QT developer however when running the developer it cannot find the DB driver? How can I make the driver available to QT? Any Ideas would be helpful Philip ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [Auth] ident method and LDAP user accounts
On Thu, Mar 03, 2005 at 12:00:51PM +0100, Marco Colombo [EMAIL PROTECTED] wrote a message of 39 lines which said: Does Debian include and activate SELinux? Not at all. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Does IMMUTABLE have any effect on functions?
Hi, i wonder if the attribute IMMUTABLE has any effect on functions. Maybe its not implemented yet? I tried the following: CREATE TABLE foo (bar int4); CREATE FUNCTION foo(int4) RETURNS int4 AS ' INSERT INTO foo (bar) VALUES ($1); SELECT $1; ' LANGUAGE 'sql' IMMUTABLE; ...now without any transaction... select * from foo(1); foo - 1 (1 row) SELECT * FROM foo; bar - 1 (1 row) select * from foo(1); foo - 1 (1 row) SELECT * FROM foo; bar - 1 1 (1 row) In my expectations the 2nd function call should not have added a new row to table foo, beacause it was called with the same parameter and is immutable. The same happens when i try to call the function twice within a single transaction. Maybe the IMMUTABLE attribute is just some sort of comment? greets, Tom Schön ---(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] Does IMMUTABLE have any effect on functions?
Lookup the docs, but IMMUTABLE and other such tags are hints to the optimiser. If a function is immutable then the optimiser can optimise away any invocations. If your function isn't actually immutable you've just caused a problem. They don't affect the actual function at all... On Thu, Mar 03, 2005 at 02:17:17PM +0100, Thomas Schoen wrote: Hi, i wonder if the attribute IMMUTABLE has any effect on functions. Maybe its not implemented yet? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpB1ruTapV0M.pgp Description: PGP signature
Re: [GENERAL] Compatible Dumps
Richard Huxton wrote: Jake Stride wrote: Is there a way to make a postgresql 8 database dump work with 7.4? I.E I want to do a dump from 8.0 on one machine and put it into a database running on 7.4 on another? Haven't tried this yet, but apart from turning off dollar-quoting for functions, it should just work. If you want to test it, try a schema-only dump+restore first. That helps a bit, but I still have the following issue: ERROR: function pg_catalog.pg_get_serial_sequence(unknown, unknown) does not exist Any ideas how to get around this? Thanks Jake ---(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] Compatible Dumps
Jake Stride wrote: That helps a bit, but I still have the following issue: ERROR: function pg_catalog.pg_get_serial_sequence(unknown, unknown) does not exist Any ideas how to get around this? Hmm - before 8.0 there wasn't an easy way to figure out the name of a sequence attached to a column. This function is the easy way to do that, but is (of course) not present in 7.x Assuming your sequences all have default names, and are in the public schema, you could write a simple function: CREATE FUNCTION my_pg_get_serial_sequence(text,text) RETURNS text AS ' SELECT ''public.'' || $1 || ''_'' || $2 || ''_seq''; ' LANGUAGE SQL; Then a quick bit of sed/perl search replace-ing and you're away. This *will* break if the target table (1st param) is in a different schema than public, or if you have non-default sequence names. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [Auth] ident method and LDAP user accounts
Stephane Bortzmeyer [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote so it sure looks like we *are* using getpwuid. You're right but I do not understand why it fails only with PostgreSQL. Perhaps one of the pile of random libraries we include is supplying a broken version of getpwuid? I concur with the suggestion to try strace'ing to compare PG's behavior to something that works (such as ls). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does IMMUTABLE have any effect on functions?
Thomas Schoen [EMAIL PROTECTED] writes: In my expectations the 2nd function call should not have added a new row to table foo, beacause it was called with the same parameter and is immutable. IMMUTABLE is a promise from you to the database (a promise which you broke, in this case) ... not vice versa. There is no commitment to avoid duplicate evaluations in all cases, and certainly not to do so across multiple queries. BTW, PG 8.0 will flat out reject this function, because it is violating the requirement that immutable functions not have side-effects. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Disabling triggers in a transaction
This caught my eye the other day, but didn't take the time to examine it. I find that I am now very interested in it. Could you please elaborate on your method and how this works? TIA On Sunday 27 February 2005 05:37 pm, Jay Guerette saith: If I disable INSERT and UPDATE triggers inside a transaction; by setting and resetting reltriggers in pg_class; am I correct in thinking that this will disable triggers globally for that table for the duration of that transaction? So an INSERT or UPDATE to this table, outside of the transaction and within that precise timeframe, would NOT fire the trigger? If so, would the 'serializable ' isolation level be required in order to ensure this doesn't happen? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/Perl trusted throws error on example function
Frank Finner [EMAIL PROTECTED] writes: error from Perl function: trusted Perl functions disabled - please upgrade Perl Safe module to version 2.09 or later at (eval 4) line 1. What do you get from perl -e 'require Safe; print $Safe::VERSION\n' regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl trusted throws error on example function
Hi Tom, I found an error right between my ears, means, I had looked at the client__s version of Safe, not thinking about the fact that I was connected to the database on a server which actually had Safe installed only with version 2.07. I upgraded Safe now from CPAN on my server and everything works fine now. Thanks for your help, it opened my eyes. Regards, Frank On Thu, 03 Mar 2005 12:14:53 -0500 Tom Lane [EMAIL PROTECTED] thought long, then sat down and wrote: Frank Finner [EMAIL PROTECTED] writes: error from Perl function: trusted Perl functions disabled - please upgrade Perl Safe module to version 2.09 or later at (eval 4) line 1. What do you get from perl -e 'require Safe; print $Safe::VERSION\n' regards, tom lane -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606Mail: [EMAIL PROTECTED] Telefax: 0271 231 8608Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651 pgp29HO6ihqA3.pgp Description: PGP signature
[GENERAL] unexpected 'aggregates not allowed in where clause' error
Hello, I am getting a strange "aggregates not allowed in where clause" error when I try to execute the below query: SELECT n.note, pr.property_id AS PropertyID FROM property.note n INNER JOIN property.rating_note rn ON rn.note_id = n.note_id INNER JOIN property.propertyrating pr ON pr.property_rating_id = rn.property_rating_id WHERE pr.property_id IN ( SELECT p.property_id FROM property.vw_property_summary p INNER JOIN property.propertyscore ps ON p.property_id = ps.property_id WHERE (ps.score_type_id = $1 AND p.place_id = $2) OR (ps.score_type_id = $3 AND p.place_id = $4) ) AND pr.created_date = ( SELECT MIN(pr.created_date) FROM property.propertyrating pr2 WHERE pr2.property_rating_id = pr.property_rating_id ORDER BY pr.created_date DESC LIMIT 5 ); Please note that the aggregate is within a subquery of the where clause, and not in the where clause proper. Is this error message a bug or am I doing something wrong?
Re: [GENERAL] Compatible Dumps
Richard Huxton dev@archonet.com writes: This *will* break if the target table (1st param) is in a different schema than public, or if you have non-default sequence names. Including if you've renamed a serial column since creating it, or if your serial column has an extremely long name. In practice it works well enough though. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] hostname change and postgresql
Hi, Simple question: If I change a hostname (Linux FC2) do I need to do any changes to Postgresql configurations ? Thanks, Mark __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] unexpected 'aggregates not allowed in where clause' error
L. Fletcher [EMAIL PROTECTED] writes: I am getting a strange aggregates not allowed in where clause error = when I try to execute the below query: SELECT n.note, pr.property_id AS PropertyID FROM property.note n INNER JOIN property.rating_note rn ON rn.note_id =3D n.note_id INNER JOIN property.propertyrating pr ON pr.property_rating_id = =3D rn.property_rating_id WHERE pr.property_id IN ( SELECT p.property_id FROM property.vw_property_summary p INNER JOIN property.propertyscore ps ON p.property_id =3D = ps.property_id WHERE (ps.score_type_id =3D $1 AND p.place_id =3D $2) OR (ps.score_type_id =3D $3 AND p.place_id =3D $4) ) AND pr.created_date =3D ( SELECT MIN(pr.created_date) FROM property.propertyrating pr2 WHERE pr2.property_rating_id =3D pr.property_rating_id ORDER BY pr.created_date DESC LIMIT 5 ); The error message is correct, because the aggregate is associated with the outer query's pr table. Perhaps you meant to write MIN(pr2.created_date)? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Changing Run-time parameters at command line
Hi, another simple question: what's the correct command line syntax to change specific run-time parameters, e.g. 'client_min_messages'. I understand you can define/change them in the .conf file ... an example, or a pointer to RTFM would be great =) thanks, richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] hostname change and postgresql
On Thu, 3 Mar 2005 11:07:53 -0800 (PST), Mark [EMAIL PROTECTED] wrote: Hi, Simple question: If I change a hostname (Linux FC2) do I need to do any changes to Postgresql configurations ? Only if the IP address associated with it is changing. Although remote clients might need to have their configurations updated to find the new hostname. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] GUI
Hi All, A question about joins i have 17 tables in my postgres-DB how does one perform a join on these many tables!!! :( Thanks, H On Tue, 1 Mar 2005 08:42:57 -0600, James Thompson [EMAIL PROTECTED] wrote: On Tuesday 01 March 2005 08:07 am, Hrishikesh Deshmukh wrote: Hi All, I want to spend less time coding and more time running queries against the DB which i am building. What about QT from TrollTech!! Can that be used to whip up gui real fast! Dreamweaver? Hrishi You could use gnue-designer and gnue-forms from www.gnuenterprise.org. It lets you paint the input/query forms by drag and drop fields from the db. You can create multi table master/details forms from existing tables in seconds that give you insert, update, delete, query capabilities. You can assign triggers to forms to do custom processing. And if you need more customization you can write python scripts using the gnue-common library which is the core of our other tools. I've done that to create custom apps that tie our database access system to a ui built with pyqt and qt-designer. Several of the developers hang out in #gnuenterprise on irc.freenode.net if you'd like more info. Take Care, James ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Disabling triggers in a transaction
Tom, Do you feel this is a safe method for disabling triggers in the rare cases where one finds that it is prudent to do that? Do you think that the column, reltriggers, is permanent fixture in pg_class? What is your advice on this? TIA On Monday 28 February 2005 03:22 pm, Tom Lane saith: Jay Guerette [EMAIL PROTECTED] writes: If I disable INSERT and UPDATE triggers inside a transaction; by setting and resetting reltriggers in pg_class; am I correct in thinking that this will disable triggers globally for that table for the duration of that transaction? Not if you never commit the pg_class row in that state. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] GUI
Am Donnerstag, den 03.03.2005, 14:35 -0500 schrieb Hrishikesh Deshmukh: Hi All, A question about joins i have 17 tables in my postgres-DB how does one perform a join on these many tables!!! :( you just use 16 times the word JOIN ? HTH Tino ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Table inherits...
When a table inherit another, why it doesn't inherit their keys nor fkeys?... How do I change it?
Re: [GENERAL] GUI
On Mar 3, 2005, at 4:36 PM, Tino Wildenhain wrote: Am Donnerstag, den 03.03.2005, 14:35 -0500 schrieb Hrishikesh Deshmukh: Hi All, A question about joins i have 17 tables in my postgres-DB how does one perform a join on these many tables!!! :( What are you trying to do? You have 17 total tables and you want to know how to join in general on several of them, or you need to do a 17-table join? Sean ---(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] PostgreSQL and XML
On Thu, 03 Mar 2005 07:59:14 +, Mario Splivalo wrote: Can I use XPath queries in any form to retrive data from XML documents and 'transfer' them to table-like sets? Basically, yes - if you look at the README for contrib/xml2 in the PostgreSQL source distribution (v8.0.1) there is an example using the xpath_table function which allows you to run several XPath expressions in parallel against a set of rows and turn the result into a table. To use this functionality you will need to build the contrib/xml2 mocule - your machine will need libxml (http://xmlsoft.org/) Something as MSSQL's OPENXML keyword? In a way (from reading half a webpage) - I'm not a user of MSSQL so I can't really compare against how their functions work! Regards John ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Table inherits...
On Thu, 2005-03-03 at 15:53, Cristian Prieto wrote: When a table inherit another, why it doesn't inherit their keys nor fkeys?... How do I change it? There are basic architectural problems that need to be overcome before that would happen. The only way to change it right now is to check out the CVS source code and program it yourself. not a small job, or someone would have done it by now. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Table inherits...
Thanks! - Original Message - From: Scott Marlowe [EMAIL PROTECTED] To: Cristian Prieto [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Thursday, March 03, 2005 4:18 PM Subject: Re: [GENERAL] Table inherits... On Thu, 2005-03-03 at 15:53, Cristian Prieto wrote: When a table inherit another, why it doesn't inherit their keys nor fkeys?... How do I change it? There are basic architectural problems that need to be overcome before that would happen. The only way to change it right now is to check out the CVS source code and program it yourself. not a small job, or someone would have done it by now. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] GUI
Hi, I want to do a 17 table join! :( But a general idea will help. Thanks, H On Thu, 3 Mar 2005 16:56:13 -0500, Sean Davis [EMAIL PROTECTED] wrote: On Mar 3, 2005, at 4:36 PM, Tino Wildenhain wrote: Am Donnerstag, den 03.03.2005, 14:35 -0500 schrieb Hrishikesh Deshmukh: Hi All, A question about joins i have 17 tables in my postgres-DB how does one perform a join on these many tables!!! :( What are you trying to do? You have 17 total tables and you want to know how to join in general on several of them, or you need to do a 17-table join? Sean ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] preserving data after updates
Is there a canonical form that db schema designers use to save changes to the data in their databases? For example, given a table with rows of data, if I UPDATE a field in a row, the previous value is lost. If I wanted to track the changes to my data over time, it occurs to me that I could, 1) copy the whole row of data using the new value, thus leaving the old row intact in the db for fishing expeditions, posterity, c. -- awfully wasteful, especially with binary data 2) enter a new row that contains only new data fields, requiring building a full set of data through heavy lifting and multiple queries through 'n' number of old rows -- overly complex query design probably leading to errors 3) create a new table that tracks changes -- the table is either wide enough to mirror all columns in the working table, or uses generic columns and API tricks to parse token pair strings, ... 4) other? Thanks Scott ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] GUI
On Thu, 2005-03-03 at 18:00 -0500, Hrishikesh Deshmukh wrote: Hi, I want to do a 17 table join! :( But a general idea will help. Thanks, H Try here to start: http://www.postgresql.org/docs/current/static/tutorial-join.html SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); Cheers, Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development * --- Now hosting Ruby on Rails Apps --- / ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Disabling triggers in a transaction
Terry Lee Tucker wrote: Tom, Do you feel this is a safe method for disabling triggers in the rare cases where one finds that it is prudent to do that? Do you think that the column, reltriggers, is permanent fixture in pg_class? What is your advice on this? I'd be quite interested in this as well. Can one depend on this column in the future? Even if not, as long as one verifies it still exists, is this a viable option for trigger control within a transaction? (This guy Tucker comes up with some interesting stuff...) :) -- Until later, Geoffrey ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
